엑셀 만 단위 제거 후 합계 구하기 공식
엑셀 만 단위 제거 후 합계 구하기 목차 바로가기
함수 공식
{ =SUM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(합계범위,"만","."),"천",""),"원","")," ","")*10000) }
'배열수식이므로 Ctrl + Shift + Enter로 입력합니다.
(오피스 365 사용자는 일반 수식과 동일하게 Enter로 입력해도 무방합니다.)
'배열수식이므로 Ctrl + Shift + Enter로 입력합니다.
(오피스 365 사용자는 일반 수식과 동일하게 Enter로 입력해도 무방합니다.)
인수 설명
| 인수 | 설명 |
| 합계범위 | 만원, 천원 단위 제거 후 합계를 구할 범위입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 만 단위 제거 후 합계 구하기 – 예제파일예제파일
텍스트의 단위가 복잡하게 입력되어 있을 경우, 아래 TextToNumber 사용자지정함수를 사용하면 보다 손쉽게 텍스트를 숫자로 변환할 수 있습니다.
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 만 단위 제거 후 합계 구하기 공식 알아보기
공식 설명
숫자에 텍스트 ("만원", "천원", 등..)의 텍스트가 섞여 있을 경우 단위를 제거하고 숫자의 합계를 계산하는 공식입니다. 단위가 연속으로 입력되었다는 가정하에, 엑셀 내장함수만으로 손쉽게 사용 할 수 있는 공식입니다.
만약, 단위가 연속되어 있지 않은 경우 (예: 2만 3백원, 2천 3십원 등..) 에는 해당 공식을 사용할 수 없으며, 아래 자주묻는질문 두번째 공식을 응용하거나 VBA를 사용하면 쉽게 해결할 수 있습니다.
공식의 동작원리
- SUBSTITUTE 함수를 사용하여 각 단위를 소숫점 또는 빈칸으로 변경합니다.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("4만 3천원","만","."),"천",""),"원","")," ","")
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("4. 3천원","천",""),"원","")," ","")
=SUBSTITUTE(SUBSTITUTE("4. 3원","원","")," ","")
=SUBSTITUTE("4 .3"," ","")
="4.3" - 단위가 제거된 값에 10000을 곱해 올바른 숫자값으로 변환합니다.
="4.3"*10000
=43000 - SUM 함수로 숫자로 변경된 값들의 합계를 계산합니다.
=SUM({43000, 32000, 18000})
=93000
자주묻는질문
자주묻는질문1. 억단위, 천만단위도 가능한가요?
물론 가능합니다. 공식을 아래와 같이 적절히 수정하면 억단위는 물론 천만단위, 백만단위도 손쉽게 계산할 수 있습니다.
{ =SUM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(합계범위,"억","."),"천만",""),"원","")," ","")*10000000) }자주묻는질문2. 중간에 단위가 누락된 경우는 어떻게 하나요?
중간에 단위가 누락된 경우에는 공식 하나로 해결할 수 없습니다. 아래 적어드린 공식을 사용하여 계산 가능한 수식으로 변경한 뒤 작업을 하거나 또는 VBA 사용자지정함수를 사용하여 쉽게 해결할 수 있습니다.
아래 공식을 사용하며 공식의 사용방법은 아래와 같습니다.
- 공식을 사용하면 아래와 같이 하나의 수식이 완성됩니다.
="="&LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(셀,"만","*10000+"),"천","*1000+"),"백","100+"),"십","*10+")," ",""),"+*","+1*"),"원",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(셀,"만","*10000+"),"천","*1000+"),"백","100+"),"십","*10+")," ",""),"+*","+1*"),"원",""))-1)
공식을 사용하면 단위가 제거된 하나의 수식이 완성됩니다. - 수식을 복사한 뒤 다른 셀에 '선택하여 붙여넣기'로 값을 붙여넣기 합니다.
완성된 수식을 복사하여 다른 셀에 값만 붙여넣기 합니다. - 붙여넣기 된 값을 다시 입력하면 수식이 계산되며 값이 계산됩니다.
만 단위, 천 단위가 제거 된 숫자가 계산됩니다.
- SUBSTITUTE 함수를 사용하여 각 단위를 소숫점 또는 빈칸으로 변경합니다.
혹시 "."로 바꿀 텍스트 외의 텍스트는 한꺼번에 substitute 함수로 바꿔줘도 괜찮을까요?
"천", "원" 각각이 아니라 "천원"으로 바꾸는 방식이요. 해보니까 결과값은 다르지 않는데 실제로 응용하는 경우 염두해야할 사항이 있을까요?
네 위의 공식은 하나의 예제입니다. "천", "원" 각각이 아닌 "천원"으로 변경해서 사용하셔도 무방합니다. 다만, 천 이하 단위의 숫자가 있을 경우 4"천원"32 이런식으로 옳지 않은 값이 반환될 수도 있을듯 하니, 이 점만 유의하시면 될 듯 합니다.
답변이 도움이 되셨길 바랍니다.