메뉴 닫기

엑셀에서 금액 표시하기(견적서 자동작성 一金 0000整)

엑셀을 사용하여 견젹서를 작성할 때 숫자로 금액을 표시하는 경우도 있지만 문자로 표시할 경우도 종종 있다.
예를 들면 견적서에서 일금 00만원정 과 같이 표시하는 경우를 보았을 것이다.
엑셀에서 견적금액을 자동 계산해 놓고 금액을 한글로 따로 작성해야 하는 것은 좀 이상하다.
엑셀 견젹서를 자동으로 작성하는 방법을 알아보자!
엑셀을 이용한 견적서 작성
image
1. 견적명세(1): 일금 XX원정과 같이 표시되는 부분이다. 여기서 사용되는 함수를 알아보자
– 엑셀 문자열 합치기: =”문자” & “표현2” 와 같이 사용된다 연산 결과는 “문자표현2”의 결과를 출력한다
– 엑셀 숫자를 문자로 표현하는 NUMBERSTRING 함수: NUMBERSTRING(셀,옴션)은 숫자를 문자로 바꾸어 주는 함수 이다.
셀에는 연산결과가 기록된 셀 이며 옵션은 숫자를 표현하는 방법이다 1 이면 자리단위를 포함해서 “일천이백삼” 처럼 표현한다
옵션이 2 이면 한문으로 표현한다. 옵션이 3이민 “일영이영..”과 같이 2자리씩 끊어서 표현한다.
예)견적합산 결과 셀이 그림의 (3) 에 처럼 셀 AE20 에 있다고 가정하자 그리고 합산 결과가 1,090,000원이라고 가정하고 출력결과를 보자
그림에서(1)의 견적명세에는 =”一金 ” & NUMBERSTRING(AE20,1) &” 원整” 와 같이 사용되었다
NUMSTRING(AE20,1) 결과 =>”一金 일백구만 원整”으로 문자열을 합쳐서 자동으로 표현된다
NUMSTRING(AE20,2) 결과 =>”一金 壹百九萬 원整”으로 문자열을 합쳐서 자동으로 표현된다
NUMSTRING(AE20,3) 결과 =>”一金 일영구영영영영 원整”으로 문자열을 합쳐서 자동으로 표현된다
2. 엑셀에서 금액(원화단위)표시: 그림의(2)는 숫자를 금액으로(원화단위)로 표현하는 함수 “WON()”과 만원 이하 금액은 버리는 ‘절사’의 기능이 포함되어 있다.
절사의 기능을 구현하기 위해 ROUNDDOWN(셀,솟수자리수) 함수가 사용되었다.
전체 표현식은
“=WON(ROUNDDOWN(AE20/10000,0)*10000)” 과 같이 되어 있다.
ROUNDDOWN에서 10000을 나누어 10000이하의 솟수점을 버리게 만들고 다시 10000을 곱하여 단위값 절사 기능을 구현하고
WON 함수를 사용하여 통화 단위로 환산 시켰다.

3. 엑셀에서 셀 영역합산 SUM: =SUM(AF16:AK19) 을 사용했다
SUM 함수는 ,SUM(시작셀:마지막셀) 과 같은 형태로 사용한다.
quot.zip

39 Comments

  1. 1.179.184.26

    Good day very cool web site!! Man .. Excellent ..
    Wonderful .. I’ll bookmark your website and take the feeds additionally?
    I’m happy to seek out a lot of useful information here in the post, we want develop more strategies on this
    regard, thanks for sharing. . . . . .

  2. cbd oil that works 2020

    Its like you read my mind! You seem to grasp so much
    about this, such as you wrote the e book in it or something.
    I believe that you just can do with some percent to power the message house
    a little bit, however instead of that, that is excellent
    blog. A great read. I will certainly be back.

  3. Jamee Mega

    I simply want to tell you that I am very new to weblog and definitely savored your web blog. Almost certainly I’m want to bookmark your website . You surely have awesome stories. Thanks a bunch for sharing with us your blog site.

  4. can you buy viagra in canada

    There are some fascinating times in this write-up however I don?t understand if I see all of them center to heart. There is some credibility but I will hold opinion until I look into it additionally. Good post, thanks and we desire more! Added to FeedBurner as well

  5. John Deere Technical Manuals

    The particular Amish’s important faith centered idea of Demut “humility”, plainly demonstrates the particular weak spot related to individualism as well as sociable school therefore well-known concerning individuals civilizations and also there exists a matching sturdiness relating to Amish party id.

  6. can cbd oil help incontinent dogs

    Nice post. I discover something extra tough on different blog sites everyday. It will certainly always be stimulating to check out material from various other authors as well as practice a little something from their shop. I?d choose to use some with the material on my blog whether you don?t mind. Natually I?ll give you a web link on your internet blog site. Thanks for sharing.

  7. who benefits from cbd oil

    There are some fascinating points in this short article yet I don?t understand if I see every one of them facility to heart. There is some validity yet I will hold opinion until I explore it even more. Great post, thanks as well as we want more! Contributed to FeedBurner also

  8. cbd oil 10 mg capsules

    This is the best blog for anyone who intends to discover this topic. You understand a lot its virtually tough to say with you (not that I in fact would want?HaHa). You definitely placed a brand-new spin on a subject thats been discussed for years. Excellent things, simply terrific!

  9. Barrett Affelt

    There are some fascinating times in this post yet I don?t know if I see every one of them center to heart. There is some legitimacy but I will certainly hold viewpoint until I consider it additionally. Good short article, thanks as well as we desire more! Contributed to FeedBurner also

  10. click for source

    Can I simply claim what a relief to discover someone that in fact understands what theyre discussing on the internet. You most definitely know how to bring an issue to light and make it vital. More individuals need to read this and also comprehend this side of the tale. I angle believe youre not much more popular due to the fact that you certainly have the present.

  11. web hosting providers

    Magnificent goods from you, man. I’ve keep in mind your stuff previous to
    and you are simply extremely excellent. I really like what you’ve got here, certainly like what you are saying and the best way in which you say it.
    You’re making it entertaining and you still care for to keep it wise.
    I cant wait to learn much more from you. This is really
    a great website.

  12. discover more here

    Youre so cool! I don’t suppose Ive review anything like this before. So great to find someone with some original thoughts on this subject. realy thank you for starting this up. this site is something that is needed on the web, someone with a little originality. beneficial job for bringing something new to the internet!

  13. for beginners

    There are definitely a great deal of details like that to take into account. That is a fantastic point to raise. I supply the ideas above as general ideas but plainly there are concerns like the one you raise where the most important thing will be operating in honest good faith. I don?t recognize if best practices have actually emerged around things like that, however I make certain that your task is plainly identified as a fair game. Both young boys and girls feel the effect of simply a moment?s pleasure, for the remainder of their lives.

  14. Google

    Hi, I think your blog may be having browser compatibility problems. When I look at your web site in Safari, it looks fine however, when opening in I.E., it’s got some overlapping issues. I simply wanted to provide you with a quick heads up! Apart from that, excellent blog!

댓글 남기기

이메일은 공개되지 않습니다.