엑셀 만 단위 합계 구하기 쉬운 방법 :: 함수공식

텍스트의 만단위, 천단위 제거 후 합계를 구하는 공식의 사용법 및 동작원리를 알아봅니다.

홈페이지 » 엑셀 만 단위 제거 후 합계 구하기 쉬운 방법 :: 함수 공식

엑셀 만 단위 제거 후 합계 구하기 공식

엑셀 만 단위 제거 후 합계 구하기 목차 바로가기
함수 공식
{ =SUM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(합계범위,"만","."),"천",""),"원","")," ","")*10000) }
'배열수식이므로 Ctrl + Shift + Enter로 입력합니다.
(오피스 365 사용자는 일반 수식과 동일하게 Enter로 입력해도 무방합니다.)
인수 설명
인수설명
합계범위만원, 천원 단위 제거 후 합계를 구할 범위입니다.

예제파일 다운로드

텍스트의 단위가 복잡하게 입력되어 있을 경우, 아래 TextToNumber 사용자지정함수를 사용하면 보다 손쉽게 텍스트를 숫자로 변환할 수 있습니다.

호환성
운영체제호환성
Windows 버전모든 엑셀 버전에서 사용 가능합니다.
Mac 버전모든 엑셀 버전에서 사용 가능합니다.
사용된 기초 함수
선택된 셀 또는 범위의 합계를 구합니다.
문자열에서 특정 문자를 찾아 다른 문자로 변경합니다.

엑셀 만 단위 제거 후 합계 구하기 공식 알아보기

공식 설명

숫자에 텍스트 ("만원", "천원", 등..)의 텍스트가 섞여 있을 경우 단위를 제거하고 숫자의 합계를 계산하는 공식입니다. 단위가 연속으로 입력되었다는 가정하에, 엑셀 내장함수만으로 손쉽게 사용 할 수 있는 공식입니다.

만약, 단위가 연속되어 있지 않은 경우 (예: 2만 3백원, 2천 3십원 등..) 에는 해당 공식을 사용할 수 없으며, 아래 자주묻는질문 두번째 공식을 응용하거나 VBA를 사용하면 쉽게 해결할 수 있습니다.

공식의 동작원리
  1. SUBSTITUTE 함수를 사용하여 각 단위를 소숫점 또는 빈칸으로 변경합니다.
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("4만 3천원","만","."),"천",""),"원","")," ","")
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("4. 3천원","천",""),"원","")," ","")
    =SUBSTITUTE(SUBSTITUTE("4. 3원","원","")," ","")
    =SUBSTITUTE("4 .3"," ","")
    ="4.3"
  2. 단위가 제거된 값에 10000을 곱해 올바른 숫자값으로 변환합니다.
    ="4.3"*10000
    =43000
  3. SUM 함수로 숫자로 변경된 값들의 합계를 계산합니다.
    =SUM({43000, 32000, 18000})
    =93000

자주묻는질문

자주묻는질문1. 억단위, 천만단위도 가능한가요?

물론 가능합니다. 공식을 아래와 같이 적절히 수정하면 억단위는 물론 천만단위, 백만단위도 손쉽게 계산할 수 있습니다.

{ =SUM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(합계범위,"억","."),"천만",""),"원","")," ","")*10000000) }
자주묻는질문2. 중간에 단위가 누락된 경우는 어떻게 하나요?

중간에 단위가 누락된 경우에는 공식 하나로 해결할 수 없습니다. 아래 적어드린 공식을 사용하여 계산 가능한 수식으로 변경한 뒤 작업을 하거나 또는 VBA 사용자지정함수를 사용하여 쉽게 해결할 수 있습니다.

아래 공식을 사용하며 공식의 사용방법은 아래와 같습니다.

  1. 공식을 사용하면 아래와 같이 하나의 수식이 완성됩니다.
    ="="&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)

    엑셀 만단위 합계 계산 단위 제거
    공식을 사용하면 단위가 제거된 하나의 수식이 완성됩니다.
  2. 수식을 복사한 뒤 다른 셀에 '선택하여 붙여넣기'로 값을 붙여넣기 합니다.

    값으로 붙여 넣기
    완성된 수식을 복사하여 다른 셀에 값만 붙여넣기 합니다.
  3. 붙여넣기 된 값을 다시 입력하면 수식이 계산되며 값이 계산됩니다.

    엑셀 만단위 제거 완료
    만 단위, 천 단위가 제거 된 숫자가 계산됩니다.
5 1 vote
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
2 Comments
Inline Feedbacks
View all comments
정도영
정도영
2020년 8월 4일 8:14 오전
게시글평점 :
     

유용한 정보 정말 감사합니다. 혹시 "."로 바꿀 텍스트 외의 텍스트는 한꺼번에 substitute 함수로 바꿔줘도 괜찮을까요? "천", "원" 각각이 아니라 "천원"으로 바꾸는 방식이요. 해보니까 결과값은 다르지 않는데 실제로 응용하는 경우 염두해야할… 더보기 »

Last edited 2 월 전 by 정도영
2
0
여러분의 생각을 댓글로 남겨주세요.x