[엑셀] filter 함수에서 in 쿼리 표현하기 (N개의 키워드 검색)

쿼리문에서 N 개의 문자열 포함 여부를 판단할 때 in 쿼리를 많이 사용한다.

그것과 유사하게 엑셀내에서, 필터함수를 사용해서 특정 시트의 값 조건에 따라 필터링한 결과를 출력해주는걸 하려면 어떻게 해야할까?

특정 시트열을 사용자가 입력하면 N개의 키워드를 in 절과 같이 검색해서 결과를 노출되게 하려면 어떻게 할까?

1. 쿼리문에서 in 절을 엑셀에서 표현하려면?

쉽게 말하면 SQL 에서 in 절을 엑셀에서 어떻게 표현할 수 있을까? 하는 의미이다.

SELECT 
  *
FROM
  직원
WHERE
  고향 in (서울, 부산, ...) -- 조건을 변경하면서 검색한다면?
;

검색할 갯수가 고정적이라면 조건문에 + 를 써서 OR 조건으로 표현하는게 수식이 매우 쉽다 (이전글 참조)

2023.08.14 - [데이터처리/구글시트-엑셀] - [엑셀] SQL의 where 를 엑셀로 하려면? filter 함수 활용하기 (2개이상조건)

 

하지만 이런 조건이 100개가 넘는다면? 이걸 수식으로 복사붙여넣기 하는게 더 번거로운일이 생길수 있다.

이런경우는 조금 수식이 어려울순 있지만, 방법이 존재하긴 한다.

2. IN 절을 엑셀에서 표현하는 방법

특정 시트범위의 데이터를 쿼리문의 IN 검색조건과 유사하게  사용할때 아래와 같은 형태로 표현하면 된다. (샘플기준)

=SORT(IFERROR(INDEX(직원!A1:E11,UNIQUE(TOCOL(IF(직원!D2:D11=TRANSPOSE(G2:G7),ROW(직원!D2:D11),""))),{1,2,3,4,5}),""), 1, -1)

사실 장황하게 표현되어있긴 한데, 여기서 핵심은 TRANSPOSE 와 TOCOL 그리고 INDEX 함수가 핵심이다.

수식이 복잡해서 풀어서 설명하면 다음과 같다.

 

TRANSPOSE + 검색조건

IN 검색범위의 시트범위를 TRANSPOSE 하면 세로 표현이 가로로 변경되고, 이때 검색조건을 넣게 되면 이에 따른 검색결과가 array 로 리턴된다. 즉, 동일한 열에 대한 일치데이터는 TRUE 값이 매칭되는데, 이 행에 대한 index 정보를 추출후 그값을 가져오는 방식이다.

ROW 함수와 TO_COL 을 조합하여 행위치를 유도

이때 true 인 케이스에 대한 ROW 위치 정보를 추출하고, 여기서 N개의 열로 존재하는 데이터를 to_col 로 하나의 열로 묶어낸후, unique 함수로 중복을 제거하면 부산과 제주가 고향인 데이터는 4,5,9,10,11 행의 데이터임을 알수 있다.

이 데이터는 index 함수를 이용하면 해당 데이터를 추출할수 있는데, 이런 원리를 이용해서 데이터를 유도할 수 있다.

비교결과를 row 번호로 변경하고 하나의 열로 묶어 내기

3. 마무리

사실 수식이 너무 복잡해서 이해하기가 어려울텐데, 아래 샘플 엑셀파일을 다운로드 받아서 테스트해보면 조금더 이해가 빠를것으로 기대된다. (사실 나도 SQL 문으로는 간단히 표현되는데 엑셀에서는 너무 장황해서 더 좋은방법은 없을까 고민이 되긴한다)

ngela_excel_sample_inquery.xlsx

0.01MB

이런 케이스가 아니라, 고정된 갯수의 조건이라면 기냥 filter 의 조건절에 + 로 수식을 묶는게 훨씬 간단하니 간단한건 그 방식을 이용하고, 여기와 같이 검색 대상의 키워드가 확장이 쉬운 구조라면 조금 복잡하더라도 이 수식을 쓰면 해결이 가능하다.