선택값을 기준으로 필터하여 표시하기(2)

선택값 필터표시(완성)(1).xlsx

0.01MB

 

 

크게 배열 수식을 이용하는 방법과 동적 배열 함수인 Filter 함수를 이용하는 방법으로 나눠 살펴보겠습니다.

 

나. 배열 수식을 이용하는 방법

배열 수식을 이용하는 것은 조건에 맞는 값을 찾은 후 index 함수를 이용해 순번에 따른 값을 차례로 표시하면 되는데, 차례대로 표시할 때 Small 함수를 이용할 수도 있고, Aggregate 함수를 이용할 수도 있습니다.

 

배열이 레거시 배열과 동적 배열로 구분되므로 먼저 동적 배열을 지원하는 Microsoft 365에서 해보고, 레거시 배열인 2019에서 해보겠습니다.

 

(1) Microsoft 365의 경우

 

필터 시트는 아래와 같이 업체명이 업체4여야 한다는 조건과 결과 표시 영역이 있고,

 

데이터 시트에는 업체별 판매물품 등 데이터가 들어있으므로 여기서 업체명에 해당하는 데이터를 찾아 순서대로, 다시 말해 행의 순서대로 표시하면 됩니다.

 

(가) Index + Small 함수

조건 업체명이 업체4라는 것은 B6셀에

=INDEX(데이터!B$2:B$30,SMALL(IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100),ROW()-5))

라고 입력하면 B6셀에 업체4에 대한 판매물품 4가 구해집니다. 

 

수식에서 

$B$2=데이터!$A$2:$A$30

는 업체명과 데이터시트의 A2셀에서 A30셀까지의 값을 비교해서 True인지, False인지 판단하는 것이고,

 

IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100)

는 데이터 시트의 업체명이 필터 시트의 업체명과 일치하면 데이터 시트의 A2셀에서 A30셀의 Row, 다시 말해 행 수를 반환하고, 아니면 100을 반환하는 것입니다.

여기서 100이라고 한 것은 Small 함수를 적용할 것이기 때문에 작은 값에 해당하지 않도록 29개보다 큰 값인 100을 지정한 것입니다.

 

If 수식의 결괏값을 확인하기 위해 IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100)를 마우스로 끌어서 범위를 선택한 후 F9키를 누르면 계속 100이다가 맨 끝만 29라는 값이 반환됩니다.

수식을 원래대로 돌리기 위해 Esc키를 누릅니다.

 

SMALL(IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100),ROW()-5)

Small 함수는 배열에서 순번에 해당하는 값을 추출해 주는 함수로

위에서 확인한 결괏값에서 첫 번째부터 순서대로 데이터를 추출하면 되는데,

첫 번째는 현재 행 수가 6이므로 5를 뺀 것입니다.

첫 번째로 작은 값은 29가 됩니다.

 

INDEX(데이터!B$2:B$30,SMALL(IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100),ROW()-5))

는 index함수를 이용해 배열 또는 범위에서 Row(와 Column)에 해당하는 데이터를 추출하는 것입니다.

위 수식에서 Small 함수를 이용한 수식의 결괏값이 29이므로 index로 구한 데이터!B$2:B$30에서 29번째 행에 있는 값은 D가 되는 것입니다. 

데이터!B$2:B$30라고 혼합참조 형식으로 셀 주소를 입력한 것은 옆으로 수식을 복사할 때 B열이 자동으로 C, D, E열이 되도록 하기 위한 것입니다.

 

먼저 A6셀의 채우기 핸들을 A34셀까지  끌어서 A34셀까지 수식을 복사합니다.

그러면 A7셀부터는 100번째 행의 값을 찾는데 해당하는 값이 없으므로 #REF! 에러가 발생합니다.

 

따라서, IfError 함수로 감싸줘야 합니다. 에러일 때 값은 공백("")으로 지정합니다.

 

위와 같이 수식을 입력한 후 A6셀의 채우기 핸들을 더블 클릭하면 A34셀까지 수식이 복사됩니다.

 

이제 A34셀의 채우기 핸들을 D열까지 끕니다.

 

업체명을 업체2로 바꾸면 업체2에 해당하는 데이터 6개가 순서대로 표시됩니다.

 

 

(나) Index + Aggregate 함수

Aggregate 함수도 SubTotal 함수처럼 함수 안에 함수를 가지고 있는데,

Large, Small 등이 있으며

 

함수를 입력할 때마다 인수에 대한 설명이 표시되므로 입력하기 편합니다.

F6셀에 =agg까지 입력하고 탭키를 누르면 아래와 같이 첫 번째 인수가 표시됩니다.

Small 함수를 사용할 것이므로 Small 함수를 선택합니다.

 

그러면 다시 숨겨진 행, 오류 값 무시 등 옵션이 표시되는데, 오류 값 무시를 포함하고 있는 1, 2, 3, 6,7 중 아무거나 선택합니다. 6을 선택하겠습니다.

 

그다음은 array를 입력해야 하는데, 조건에 해당하는 IF($B$2=데이터!$A$2:$A$30,ROW(데이터!$A$2:$A$30)-1,100)을 입력하는데,

 

여러 가지 조건을 충족하는 배열 수식으로 만들기 위해 *로 조건을 연결하고, 에러를 발생시키기 위해 1/를 앞에 추가합니다. 그러면 수식은 1/($B$2=데이터!$A$2:$A$30)*(ROW(데이터!$A$2:$A$30)-1)이 됩니다. 조건에 맞는 행 수의 배열이 되는 것입니다.

 

그리고 순번에 해당하는 ,row()-5를 입력하고 (괄호를 입력한 다음) 엔터키를 누르면

=AGGREGATE(15,6,1/($B$2=데이터!$A$2:$A$30)*(ROW(데이터!$A$2:$A$30)-1),row()-5)

이 되는데, 값 20이 구해졌습니다.

 

이제 순번에 해당하는 값을 찾기 위해 index 함수를 앞에 추가하면

=INDEX(데이터!B$2:B$30,AGGREGATE(15,6,1/($B$2=데이터!$A$2:$A$30)*(ROW(데이터!$A$2:$A$30)-1),ROW()-5))

이 되고,

원하는 값 A가 구해졌습니다.

 

위 수식에서 1/($B$2=데이터!$A$2:$A$30)*(ROW(데이터!$A$2:$A$30)-1)의 값을 확인하기 위해 범위를 설정한 후 F9키를 누르면 #DIV/0!(0으로 나눌 수 없음) 에러가 계속되다가, 21부터 25까지만 숫자로 표시됩니다. 그런데 위에서 오류값을 무시하라고 했기 때문에 21에서 25에 해당하는 값만 표시되는 것입니다.

 

F6셀의 채우기 핸들을 F34셀까지 끌고, 다시 i열까지 끕니다.

마찬가지로 해당하는 숫자가 없을 경우 #NUM! 에러가 발생하므로 IfError 함수를 씌워주고, 에러일 때 값을 공백으로 합니다.

 

수식을 입력한 후 Ctrl + Enter 키를 누르니 모든 셀에 동일하게 수식이 적용됩니다.

 

2019 버전에서의 배열 수식부터는 다음 편에서 다루도록 하겠습니다.

 

선택값 필터표시(완성)(2).xlsx

0.01MB