같은 글자가 중복되는 경우 마지막 글자의 위치 찾기(2) - ByRow, Reduce

1. 문제

아래와 같이 동, 리가 있는 경우 지번을 제외한 법정동을 추출해 보겠습니다.

같은 글자의 마지막 위치 찾기(문제)2.xlsx

0.01MB

 

 

2. 해법 1 - 성공

가. Reduce 함수의 구문

=REDUCE([initial_value], array, lambda(accumulator, value, body))

로서

[initial_value]는 옵션으로서 초기값이며, 생략하면 0이 됩니다.

 

array는 처리할 대상이 되는 배열이고,

 

이 2개를 lambda함수에 전달하는데,

 

accumulator는 누계값으로서 초기값은 Reduce함수의 첫 번째 인수인 initial_value가 되며,

 

value는 현재 처리할 셀 또는 값으로서 current_value가 이해하기 쉽습니다. 예를 들어 Reduce 함수의 두 번째 인수인 array로 A2셀에서 A7셀을 입력하면 순서대로 A2셀부터 A7셀까지 하나씩 처리됩니다.

 

body는 몸체란 뜻인데, 계산식(calculation)이라고 하는 것이 이해하기 쉽습니다.

 

다시 말해 body를 accumulator + value라고 하면 

처음에는 0 + A2셀,

두 번째는 0 + A2셀 값에 A3셀 값을 더하는 식으로 진행됩니다.

 

나. Sequence 함수의 구문

=SEQUENCE(rows,[columns],[start],[step])

로서

 

rows는 행수, columns는 열 수, start는 시작 값, step은 간격입니다.

그런데, rows를 제외하고는 모두 선택값이며, 생략하면 1,1,1이 됩니다.

 

1부터 A2셀의 문자열의 길이만큼 반복하려면

=sequence(len(a2))라고 하면 1부터 15까지 반환하므로 1부터 15까지 반복하게 됩니다.

 

 

다. Reduce 함수를 이용한 수식

=REDUCE(,SEQUENCE(LEN(A2)),LAMBDA(a,c,IF(MID(A2,c,1)={"동","리"},c,a)))

 

위 수식에서 초기값을 lambda함수의 누계인 a로 받고,

SEQUENCE(LEN(A2))를 lambda함수의 배열인 c로 받는데,

위에서 살펴본 바와 같이 SEQUENCE(LEN(A2))가 1부터 문자열의 길이를 배열로 반환하며, 이것을 하나씩 c에 전달하는 것입니다.

 

IF(MID(A2,c,1)={"동","리"},c,a)는

처음에는 IF(MID(A2,1,1)={"동","리"},1,0)이 되므로

A2셀의 첫 번째부터 글자 1개가 "동" 또는 "리"라면 1을 반환하고, 아니면 0을 반환하는 것입니다.

 

이런 식으로 1부터 15까지 글자 하나씩을 가져와서 '동' 또는 '리'와 비교해서 그 위치를 반환하므로 13과 1이 반환되는데,

13은 '동'의 위치이고, 1은 '리'의 위치입니다.

'동'의 위치는 13 맞는데, '리'는 없으므로 0이 반환돼야 하는데, 1이 반환됩니다.

초기값이 0이 아니라 1로 처리되는 듯합니다.

 

현재는 초기값이 비어 있는데 0이라고 채우면 13과 0이 반환됩니다.

 

 

이제 B2셀의 채우기 핸들을 B7셀까지 끌면(더블 클릭 안됨)

 

'동'과 '리'의 위치가 모두 잘 표시됩니다.

 

3. 해법 2 - 실패

가. ByRow 함수의 구문

이번에는 A2셀에서 A7셀까지를 하나의 수식으로 처리할 수 있도록 ByRow함수를 이용해 보겠습니다.

 

ByRow 함수의 구문은

=BYROW(array, lambda(row))

라고 Microsoft 도움말 사이트에는 표시되는데,

 

=BYROW(array, lambda(row, body))라고 계산식을 포함해서 표시하는 것이 맞습니다.

다시 말해 array를 인수로 제공하면 lambda함수의 row로 제공하는데,

한 행씩 (열을 한꺼번에) 전달하는 것입니다.

 

그러면 body에 의해 row를 이용한 처리를 합니다.

E2셀부터 G3셀까지 1부터 6까지 입력하기 위해

E2셀에 =SEQUENCE(2,3)라고 입력하고,

H2셀에 =BYROW(E2:G3,LAMBDA(r,MAX(r)))라고 하면

 

2행의 최댓값 3과 3행의 최댓값 6을 한꺼번에 처리해서 동적 배열로 반환합니다.

 

나. ByRow함수와 Reduce함수의 결합

ByRow함수로 A2셀에서 A7셀까지를 한 셀로 반환하기 위해

=BYROW(A2:A7,를 앞에 입력한 다음

하나씩 전달해야 하므로

Lambda를 입력하고 첫 번째 인수를 r(Range를 생각해서 지정)로 전달하고,

Reduce함수의 A2를 r로 수정하면

=BYROW(A2:A7,LAMBDA(r,REDUCE(0,SEQUENCE(LEN(r)),LAMBDA(a,c,IF(MID(r,c,1)={"동","리"},c,a)))))

라는 수식이 됩니다.

 

그런데, 뭐가 잘못됐는지 #CALC!라고 계산 에러가 표시됩니다.

 

4. 해법 3 - 성공

MID(r,c,1)={"동","리"}에서 

{"동","리"} 를 "동"으로 수정하니 이번에는 #분산! 에러로 바뀝니다.

#분산! 에러는 계산값이 표시될 범위에 값이 있어서 그런 것이므로 

 

B3셀에서 B7셀까지를 지웁니다.

그러면 '동'의 위치가 모두 표시되고, B6셀은 '동'이 없어서 0으로 표시됩니다.

 

그렇다면 "동"과 "리"를 배열로 표시하지 않고,

Or를 이용하면 어떨까 하고,

OR(MID(r,c,1)="동",MID(r,c,1)="리")라고 바꿔서

=BYROW(A2:A7,LAMBDA(r,REDUCE(0,SEQUENCE(LEN(r)),LAMBDA(a,c,IF(OR(MID(r,c,1)="동",MID(r,c,1)="리"),c,a)))))라고 수정하니 '리'까지 위치가 잘 표시됩니다.

 

 

5. '동' 또는 '리'까지 가져오기

Left함수를 이용하는데,

이미 Lambda함수로 r이 전달됐으므로

r을 이용해서 Left(r,Reduce함수식)으로 수식을 작성할 수 있습니다.

 

완성된 수식은 

=BYROW(A2:A7,LAMBDA(r,LEFT(r,REDUCE(0,SEQUENCE(LEN(r)),LAMBDA(a,c,IF(OR(MID(r,c,1)="동",MID(r,c,1)="리"),c,a))))))

입니다.

 

 

같은 글자의 마지막 위치 찾기(완성)2.xlsx

0.01MB