셀 안에 구분기호로 나눠진 숫자 합계 구하기
목차 바로가기
셀 안에 구분기호로 나눠진 숫자 합계 구하기 (M365)
엑셀 M365 이후
=SUM(IFERROR(VALUE(TEXTSPLIT(셀,"구분기호")),0))
인수
설명
셀
구분기호로 나눠진 숫자가 작성된 셀 또는 문장을 직접 입력합니다.
구분기호
쉼표, 띄어쓰기 등 숫자를 나누는 구분기호입니다.
셀 안에 구분기호로 나눠진 숫자 합계 구하기 (엑셀 2021 이전)
엑셀 2010 이후
=SUM(IFERROR(VALUE(TRIM(MID(SUBSTITUTE("구분기호"&셀,"구분기호",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&(LEN(셀)-LEN(SUBSTITUTE(셀,"구분기호","")))/LEN("구분기호")+1))*LEN(셀)+1,LEN(셀)+1))),0))
- 본 수식은 배열수식이므로 Ctrl + Shift + Enter로 수식을 입력합니다.
- 공식에 자세한 설명 및 사용법은 아래 영상강의에서 꼼꼼히 정리했습니다.
인수
설명
셀
구분기호로 나눠진 숫자가 작성된 셀 또는 문장을 직접 입력합니다.
구분기호
쉼표, 띄어쓰기 등 숫자를 나누는 구분기호입니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀 공식] 구분기호로 나눠진 숫자 합계 구하기예제파일
구분기호로 나눠진 숫자 합계 구하기 공식 살펴보기
공식의 동작 원리
- TEXTSPLIT 함수로 셀 안에 입력된 문장을 구분기호 단위로 분리합니다.
=TEXTSPLIT("1,2,가,3,4,나,5,6",",")
={"1","2","가","3","4","나","5","6"} - VALUE 함수로 문자 형태의 숫자 값을 올바른 숫자 데이터로 변환합니다. 만약 구분기호로 나눈 값 중에 문자가 있을 경우, #VALUE! 오류를 반환합니다.
=VALUE(TEXTSPLIT("1,2,가,3,4,나,5,6",","))
=VALUE({"1","2","가","3","4","나","5","6"})
={1,2,#VALUE!,3,4,#VALUE!,5,6} - IFERROR 함수를 사용해 #VALUE! 오류를 0으로 변환합니다.
=IFERROR(VALUE(TEXTSPLIT("1,2,가,3,4,나,5,6",",")),0)
=IFERROR({1,2,#VALUE!,3,4,#VALUE!,5,6},0)
={1,2,0,3,4,0,5,6} - SUM 함수로 합계를 계산합니다.
=SUM(IFERROR(VALUE(TEXTSPLIT("1,2,가,3,4,나,5,6",",")),0))
=SUM({1,2,0,3,4,0,5,6})
=21
구분기호로 나눠진 숫자 합계 구하기 공식 활용 예제
엑셀로 자료를 관리하다 보면, 하나의 셀 안에 여러 개의 숫자(또는 문자)가 구분된 데이터를 접할 때가 있습니다. 엑셀 2021 이전 버전에서는 '텍스트 나누기' 기능으로 각 구분기호별 값을 나눠줘야 했지만, M365 최신 버전에서 제공하는 TEXTSPLIT 함수를 사용하면 구분기호로 나눠진 숫자의 합계를 편리하게 계산할 수 있습니다.
- 예제파일을 실행하면 각 영업팀의 분기별 실적이 쉼표(,)로 구분되어 한 셀안에 모두 작성되어 있습니다. 각 1~4분기 매출을 합한 연간 실적으로 계산해보겠습니다.
분기별 실적이 쉼표로 나누어 작성되어 있습니다. - 각 버전에 맞는 공식을 복사하여 D5셀에 붙여넣기한 후, '셀'과 '구분기호'를 알맞게 변경합니다.
공식을 복사/붙여넣기 후, "셀"과 "구분기호"를 각각 알맞게 변경합니다. 오빠두Tip : M365 이전 버전 공식에서 '셀'과 '구분기호' 값을 찾기 및 바꾸기로 편리하게 변경하는 방법은 아래 영상강의를 확인하세요!
- 작성한 수식을 자동채우기하면 구분기호로 나눠진 분기별 매출 합계가 계산됩니다.
구분기호로 나누어진 분기별 매출 합계가 올바르게 계산됩니다.
- TEXTSPLIT 함수로 셀 안에 입력된 문장을 구분기호 단위로 분리합니다.
=SUM(IFERROR(VALUE(TEXTSPLIT(TEXTJOIN("구분기호",,범위,범위,범위,...),"구분기호")),0))TEXTJOIN 함수로 여러 범위의 문장을 하나로 합친 후, 합계를 구하는 원리입니다. 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.