[365함수] FILTER 함수가 없는 누군가에게

엑셀을 조금 배웠다 할때가 VLOOKUP으로 좀 까불던 때가 아닐까 한다.

하지만 VLOOKUP도 정확히 일치하는 값이 다수일때는 가장 첫번째 값만 가져오기에 이 또한 후 가공을 해줘야했다..

 

이러한 번거로움을 해결하기 위해 365에서는 FILTER함수라는 녀석을 가지고 나왔다.

예전에는 위와 같은 문제를 해결하기 위해서 FILTER를 걸어서 해결하였다면 최근에는 365에서 간단하게 처리할 수 있다.

 

=FILTER([출력할영역],[찾을값의 영역]=[찾을값])

 

위와같이 하면 [찾을값의 영역]에서 [찾을값]과 일치하는것을 [출력할영역]에 설정된 DATA를 모두 가져오는 것을 볼수 있다.

 

허나 엑셀 365가 아닌 유저들에게  복잡하지만, 한줄기 빛같은 유용한 수식을 공개하려고 한다.

 ㅁ데이터의 영역
=INDEX(B5:E18,
       SMALL(
             ㅁ찾을값과 같은 행번호를 반환한다.
             IF(B5:B18=I2,ROW(B5:B18)-4,""),
             
             ㅁ행번호를 SMALL함수를 통해서 정렬시킨다.
             ROW(A1  : INDEX(A:A,COUNTIF(B5:B18,I2)))
             
ㅁ정렬된 영역에서 각 열의 데이터를 INDEX 데이터 영역에서 출력한다.
),{1,2,3,4})

 

위의 코드는 365 함수인 LET으로 변수선언을 한게 아니기에 [ㅁ.....] 으로 표현된 주석을 모두 제거후 수식을 넣어야한다.

 

수식을 하나씩 정리해보면 큰틀에서 

=INDEX(B5:E18, [조건] , {1,2,3,4})

 

INDEX함수의 데이터 영역에서 조건에 맞는 행을 찾아서 배열의 형태로 데이터의 1열부터 4열까지 반환을 하겠다는 의미이다.

 

IF(B5:B18=I2,ROW(B5:B18)-4,"")

 

찾을값이 있는 열에서 찾을값과 같으면 해당 행번호를 반환하고 그렇지 않으면 빈값을 반환한다.  여기서 ROW(B5:B18)-4를 한 이유는 데이터가 B5부터 시작함으로 -4 를 함으로써 B5가 1번행으로 인식하게 된다.

 

ROW(A1  : INDEX(A:A,COUNTIF(B5:B18,I2)))

 

이건 솔직히 큰의미가 없다....ROW(A1:A10)또는 A1부터 A5까지를 의미하는 ROW(A1:A5)를 하게되면 해당셀부터 1,2,3,4,5 가 배열로 출력이 될것이다.

 

COUNTIF로 일치하는값이 몇개인지 확인한 후에 ROW(A1:A?) 의 형태로 만들어주기 위함이다.

 

이렇게 구해지면 필터링된 값중 SMALL 함수를 통해서 첫번째부터 마지막까지 순차적으로 작은 값들을 반환하게 된다.

SMALL(
             IF(B5:B18=I2,ROW(B5:B18)-4,""),
             ROW(A1  : INDEX(A:A,COUNTIF(B5:B18,I2)))
)

 

구해진 행에서 {1,2,3,4}의 열을 가져오면 우리가 원하는 필터링이 된 값을 가져올 수 있다.

=INDEX(B5:E18,
       SMALL(
             IF(B5:B18=I2,ROW(B5:B18)-4,""),
             ROW(A1  : INDEX(A:A,COUNTIF(B5:B18,I2)))
),{1,2,3,4})

 

365함수에서는 SEQUENCE 함수가 있어서 ROW 함수의 확장판처럼 사용할 수도 있지만, ROWS함수와 ROW함수도 아직도 많이 사용하기에 학습이 필요로 하다.

 

INDEX함수에서 좀더 나아가면 단순하게

INDEX(B5:E18,4,0)

 

이렇게 입력하면 B5:E18의 4번째행의 전체 데이터인

B

148

66

83

 

값을 가져오는것을 볼수 있다. 위에처럼 배열상수를 가져오기 위해서는 {1,2,3,4} 식으로 배열상수를 구성해야 하지만 특정값들을 가져오기 위해서는 INDEX함수의 열 상수값을 0 을 입력하면 전체 행의 값을 가져오는 것을 보게 될 것이다.

 

실무에 많이 응용하기 바란다.

 

오늘도 맛있는 코드 냠냠

 

필터링대체.xlsx

0.01MB