거래처명에서 일치하는 고객명 찾기(2) - Scan 함수

거래처명에서 일치하는 고객명 찾기(1).xlsx

0.01MB

 

나. 365 버전

(1) 사용되는 함수에 대한 설명

 

(가) Filter 함수

Filter함수는 필터 명령을 함수로 구현한 것입니다.

구문은 Filter( sourcearray , match [, include ] [, compare ] )인데,

첫 번째 인수는 필터 처리할 배열(범위)로서 머리글은 포함하지 않습니다.,

두 번째 인수는 필터 조건으로 필드명=값 식으로 입력하는데,

참에 해당하는 조건식을 입력해도 됩니다.

 

(나) Sort 함수

데이터 정렬명령을 함수로 구현한 것으로

구문은 =SORT(array,[sort_index],[sort_order],[by_col])으로서

첫 번째 인수는 정렬할 배열(범위)이고,

두 번째 인수는 정렬기준인 열,

세 번째 인수는 내림차순 또는 오름차순을 지정하는 것으로 오름차순이 기본값입니다.

네 번째 인수는 열 방향으로 정렬할 것인지 지정하는 것으로 사용하는 경우는 희박합니다.

 

(다) ChooseCols 함수

데이터 범위에서 원하는 열만을 추출하는 기능입니다.

구문은 =CHOOSECOLS(array,col_num1,[col_num2],…)으로

첫 번째 인수는 배열(범위)이고,

두 번째부터는 열의 순번을 기재하면 됩니다.

 

이와 대비되는 것으로 행별로 원하는 데이터를 추출하는

ChooseRows 함수가 있습니다.

 

(라) HStack 함수

데이터를 열 방향으로 추가하는 함수로

구문은 =HSTACK(array1,[array2],...)입니다.

 

인수는 배열로서 옆으로 쌓을 데이터를 지정하면 됩니다.

 

(마) Let 함수

이름에 값을 할당하고,

이름을 이용해 계산식을 작성하고 그 값을 반환하는 것입니다.

 

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

인수는 이름1, 값1, 계산식 또는 이름2...식으로

이름에 값을 여러 개 할당하고, 최종적으로는 계산식으로 마무리합니다.

 

(바) Take 함수

배열(범위)에서 지정한 행과 열 수만큼을 가져오는 것이며, 행 또는 열 하나를 생략할 수 있습니다.

=TAKE(array, rows,[columns])

따라서, ChooseRows 또는 ChooseCols와 똑같은 역할을 합니다.

 

(2) 해법 1 - Filter, Index와 ChooseCols, HStack 함수

 

(가) 수식

=IFERROR(CHOOSECOLS(INDEX(SORT(HSTACK(FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B2))),LEN(FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B2))))),2,-1),1),1),"없음")

 

 

(나) 수식에 대한 설명

 

' 결괏값이 에러일 경우 "없음"이라고 표시

=IFERROR(

​

    '열 1개를 가져옴

    CHOOSECOLS(

​

        '배열에서 첫 번째 요소를 가져옴. 예를 들어 김영희,3;영희,2에서 김영희,3을 가져옴

        INDEX(

​

            '이름 길이순으로 내림 차순 정렬

            SORT(

​

                '일치하는 이름과 이름의 길이를 가로 방향으로 결합

                HSTACK(

                   '고객 시트에서 B2와 일치하는 고객 데이터를 가져옴

                    FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B2))),

 

                   ' 고객 시트에서 B2와 일치하는 고객명의 길이를 가져옴

                    LEN(FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B2))) )

                ),

            2,-1)

        ,1)

    ,1)

,"없음")

​

(3) 해법 2 - 위 함수에 Let 함수 추가

=LET(Filter_Name,FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B3))),IFERROR(CHOOSECOLS(INDEX(SORT(HSTACK(Filter_Name,LEN(Filter_Name)),2,-1),1),1),"없음"))

​

 

Filter_Name에 FILTER(고객!$A$2:$A$14,--ISNUMBER(FIND(고객!$A$2:$A$14,$B3)))을 할당하고,

이후에 Filter_Name을 이용해 수식을 작성해서 깔끔하게 만들었습니다.

 

(4) 해법 3 -  ChooseCols대신 Take 함수 사용

ChooseCols를 Take(array, rows [,columns])함수로 바꾸고, IsNumber앞의 --를 제거해도 됩니다.

=LET(Filter_Name,FILTER(고객!$A$2:$A$14,ISNUMBER(FIND(고객!$A$2:$A$14,$B2))),IFERROR(TAKE(INDEX(SORT(HSTACK(Filter_Name,LEN(Filter_Name)),2,-1),1),,1),"없음"))

 

 

Take 함수의 인수가 Rows와 Columns이기 때문에 Rows는 비워둔 것입니다.

 

E2셀에서 G2셀까지 선택한 후 채우기 핸들을 더블 클릭하면

오른쪽 수식 3개의 결괏값이 같은 것을 알 수 있습니다.

 

거래처명에서 일치하는 고객명 찾기(2).xlsx

0.01MB