엑셀 구분기호로 분리된 숫자 합계 공식
엑셀 구분기호로 분리된 숫자 합계 목차 바로가기
함수 공식
{ =SUM(0+TRIM(MID(SUBSTITUTE("기호"&셀,"기호",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&LEN(셀)-LEN(SUBSTITUTE(셀,"기호",""))+1))*LEN(셀)+1,LEN(셀)+1))) }
'배열 수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter로 수식을 입력합니다.
'배열 수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter로 수식을 입력합니다.
오빠두Tip : 엑셀 M365 버전 사용자는 TEXTSPLIT 함수로 더욱 편리하게 계산할 수 있습니다. TEXTSPLIT 함수를 사용하는 방법은 아래 함수 공식 포스트를 확인하세요!
인수 설명
| 인수 | 설명 |
| 셀 | 구분기호로 나뉘어진 숫자가 입력된 셀 입니다. |
| 구분기호 | 각 숫자를 분리하는 구분기호입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 구분기호로 분리된 숫자 합계 계산예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 구분기호로 분리된 숫자 합계 공식 알아보기
공식 설명
이번 포스트에서 소개해드린 공식은 셀 안에 구분기호(예: 쉼표, 띄어쓰기)로 나뉘어 입력된 숫자의 합계를 구하는 공식입니다. 배열수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter로 입력합니다.
만약 셀 안에 입력된 숫자들이 쉼표로 구분되어 있을 경우, 아래 공식을 입력하면 쉼표로 나뉘어진 숫자의 합계를 계산합니다.
'A1 : 1,10,100,1000,1000
=SUM(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)+1,LEN(A1)+1))) }
=2111
'쉼표로 나뉘어진 숫자의 합계를 계산합니다.만약 구분기호가 2개 이상 사용된 값의 합계를 구해야 할 경우, 공식에 사용된 SUBSTITUTE 함수를 여러번 나누어 사용합니다. 예를 들어, 구분기호가 2개 있을 경우 아래 공식을 사용합니다.
{ =SUM(0+TRIM(MID(SUBSTITUTE("기호1"&SUBSTITUTE(셀,"기호2",REPT(" ",LEN(셀)+1)),"기호1",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&LEN(셀)-LEN(SUBSTITUTE(SUBSTITUTE(셀,"기호1",""),"기호2",""))+1))*LEN(셀)+1,LEN(셀)+1))) }
'여러 구분기호로 나뉘어진 숫자의 합계를 계산합니다.공식의 동작원리
- 문장안에 사용된 구분기호의 개수를 구한 뒤, 구분기호 개수만큼 확장된 범위를 생성합니다.
'A1 : 1,10,100,1000
=INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1)
=INDIRECT("A1:A"&4)
=INDIRECT("A1:A4") - 생성된 범위의 행 개수 만큼 연속된 순번을 갖는 배열을 생성합니다.
=ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1))
=ROW(INDIRECT("A1:A4")
={1,2,3,4} - SUBSTITUTE 함수로 기존 문장에 사용 된 구분기호를 문장 길이보다 긴 공백(" ")으로 바꿔쓰기합니다.
=SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1))
=" 1 10 100 1000" - MID 함수로 각 n번째 값을 추출합니다.
=MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1))*LEN(A1)+1,LEN(A1)+1)
=MID(" 1 10 100 1000",{1,2,3,4}*14,14)
=MID(" 1 10 100 1000",{14,28,42,56},14)
={"1 ","10 ","100 ","1000"} - TRIM 함수로 숫자 양 옆 공백을 제거합니다.
=TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1))*LEN(A1)+1,LEN(A1)+1))
={1,10,100,1000} - SUM 함수로 합계를 계산합니다.
=SUM(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1))*LEN(A1)+1,LEN(A1)+1)))
=SUM(0+{1,10,100,1000})
=2111
- 문장안에 사용된 구분기호의 개수를 구한 뒤, 구분기호 개수만큼 확장된 범위를 생성합니다.
