한 셀에서 일정한 간격으로 떨어진 숫자 합계 구하기

1. 문제

일정한 간격으로 된 숫자 더하기.xlsx

0.01MB

 

 

아래와 같이 일정한 길이의 숫자가 한 칸 공백으로 연결되어 있을 때 합계를 구하는 것을 알아보겠습니다.

 

Mid를 이용한 365 이전 버전과 TextSplit와 ByRow를 이용한 365 버전 2가지, 총 세 가지 방법에 대해 알아보겠습니다.

 

2. Mid 함수

가. Mid 함수의 구문

MID(text, start_num, num_chars)로서

문장(또는 문자열이 들어 있는 셀 주소)과 시작 위치, 가져올 문자의 개수 3개로 되어 있습니다.

 

나. 수식 작성

위 문제를 살펴보면 숫자는 7자리이고, 공백이 1개 있으므로 8개가 1묶음입니다.

 

따라서, Mid함수를 이용해 Mid(A2,1,7), 그다음은 Mid(A2,9,7)... 식으로 개별적으로 숫자를 발췌한 후 더할 수도 있으나 배열 수식을 이용해

=MID(A2,{1,9,18,25,33,42},7)라고 중괄호 사이에 시작 위치를 여러 개 묶어서 입력하면

숫자들이 분할돼서 6개 셀에 표시됩니다.

 

이것은 365 버전이라 그렇고, 365 이전 버전이라면 CSE(Ctrl + Shift + Enter) 키를 눌러 입력하는데 163,692만 보일 것이고,

6개 셀에 모두 입력된 값이 보이게 하려면 B2셀에서 G2셀까지 범위를 잡은 다음 수식을 입력하고, CSE키를 눌러야 할 겁니다.

 

어쨌든 숫자가 분리됐으니 Sum으로 더하면 되는데

=SUM(MID(A2,{1,9,18,25,33,42},7))의 결괏값이 0으로 표시됩니다.

 

이것은 Mid함수로 구한 값이 문자열이기 때문입니다.

Left나 Right로 분리한 숫자의 경우도 마찬가지로 문자열로 반환됩니다.

 

따라서, Value 함수 또는 --를 사용해서 문자열을 숫자로 바꿔줘야 합니다.

 

이제 B2셀의 채우기 핸들을 더블 클릭하면 7행까지의 합계가 구해집니다.

 

3. TextSplit 함수

가. 구문

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

인수가 매우 많은데 대괄호 사이에 있는 것은 옵션이므로 생략하고,

Text는 문자열 또는 문자가 들어있는 셀 주소이고, col_delimeter는 열로 나눌 구분자입니다.

 

나. 수식 작성

=TEXTSPLIT(A2," ")

라고 입력하면 위에서 Mid 함수를 이용해 숫자를 분리해 낸 것처럼 옆으로 숫자가 나열됩니다.

 

마찬가지로 이것도 문자열이므로 --를 붙인 후 Sum을 해야 합니다.

이렇게 하는 것의 장점은 일정한 간격으로 떨어져 있지 않더라도 쉽게 합계를 구할 수 있다는 것입니다.

 

 

 

4. ByRow 함수

가. 정의

행과 열로 된 자료를 행별로 처리하는 것이 ByRow함수입니다.

따라서, 3행 3열의 데이터일 경우라도 결과는 1열 3행으로 됩니다.

 

나. 구문

=BYROW(array, lambda(row))

로서 첫 번째 인수로 array를 넣고, array를 Lambda함수에 넣고 처리한 후 그 값을 번환 합니다.

 

Lambda 함수는 인수들을 받은 후 계산식을 적용해서 결괏값을 반환하는 역할을 합니다.

 

 

다. 수식

수식이

=BYROW(A2:A7,LAMBDA(a,SUM(--TEXTSPLIT(a," "))))

로 복잡합니다.

 

먼저 숫자의 결합으로 된 A2셀에서 A7셀까지를 ByRow 함수의 인수로 받고,

이것을 Lambda함수의 a라는 인수로 넘기고,

그다음 계산식은 Sum과 TextSplit을 이용한 3번의 수식을 입력하는 것입니다.

 

3번에서는 =SUM(--TEXTSPLIT(A2," "))이었는데,

A2 대신에 a를 넣어서

SUM(--TEXTSPLIT(a," "))라고 하는 점이 다른 점입니다.

 

이렇게 하면 한 줄씩 a에 대입돼서 합계를 구해줍니다.

 

결과적으로 A2셀에서 A7셀까지의 문자열을 한 줄씩 합계를 구한 후

한꺼번에 배열로 반환하므로,

수식이 복잡하기는 하지만 한 번에 모든 행을 계산할 수 있다는 장점이 있습니다.

 

D2셀부터 D7셀을 보면 값 주변에 파란색 실선이 둘러쳐있는 것을 알 수 있습니다.

일정한 간격으로 된 숫자 더하기(완성).xlsx

0.01MB