[Excel/ 엑셀] 특정 컬럼에서 특정 값을 만족하는 경우인 것들만 따로 모은 시트 만드는 함수

 

능동적인 필터 적용

Excel에서 필터는 엑셀의 알파이자 오메가이며, 이를 사용하여 간단한 데이터 분석도 가능하다, 문제는 기본적으로 필터기능은 "체크박스" 또는 와일드카드를 사용하고, 필터를 적용했다고 필터에 속하지 않은 데이터들이 없어지지 않고 이로인해 row number가 순서대로 있지 않다는 점이다.

Col_2 == 'Y'일때의 결과 : 좌측 열 번호가 부적절하다.

 

거기다, 만일 와일드카드에 속하지 않거나, 필터링되는 기준이 바뀐다면 새롭게 필터링을 해줘야 한다.

Col_2에 Not Set이라는 데이터가 추가되었을때, 필터를 변경해야 한다.

 

이런 수동적인 필터말고, 좀더 세련되고 자동으로 필터링해주는 능동 필터를 함수로 만들어보자.(Excel 구버전일 경우 해당 함수가 없을 수 도 있다.)

 

 

---

 

 

XMATCH, Filter

아래 Excel 테이블을 보자, 우리는 Col_2와 Filtering_Data를 사용하여, 값이 있는지 없는지를 체크하고 있을경우 해당 열의 값들을 가져올 것이다.

---

 

결과 예시

Filtering_Data와 Col_2를 비교하여 값이 있는 경우 I:K열에 해당 하는 값을 표시

'N'을 추가하면 별도 작업없이 자동으로 우측에 추가된다.

 

 

 

---

 

 

 

함수1(XMATCH)

XMATCH는 VLOOKUP과 같이 값의 존재 여부를 체크하여 있을경우 비교값의 행 번호를 돌려주는 함수이다. 아래 이미지를 보면 이해가 될것이다.

=XMATCH( 필터값이 있는 지 검사할 부분(B:B) , 필터링될 값 열 (F:F))

=XMATCH(표1[[#모두],[Col_2]],표2[[#모두],[Filtering_Data]])

XMATCH란?

만일 필터값이 없을경우 #N/A가 표시되고,

필터값이 있을경우 Filtering_Data의 해당 값의 열 번호가 표시된다.

 

 

---

 

 

함수2(ISNUMBER)

함수 ISNUMBER()는 값이 숫자인지 아닌지를 검증하는 논리함수이다, 해당 함수를 사용하여 XMATCH의 결과가 True인지를 추출하자.

=ISNUMBER(XMATCH(표1[[#모두],[Col_2]],표2[[#모두],[Filtering_Data]]))

ISNUMBER로 있는지 없는지를 검증하도록 하자.

 

 

 

---

 

 

 

함수3(FILTER)

이제 마지막이다, 위 절차로 만들어진 True, False를 사용해서 =FILTER함수를 사용하면 된다.

=FILTER(가져올 열, 조건문(True/FALSE))

Col_1에는 아래 값을 

=FILTER(표1[[#모두],[Col_1]],ISNUMBER(XMATCH(표1[[#모두],[Col_2]],표2[[#모두],[Filtering_Data]])))

 

Col_2에는 아래 값을 (맨 앞의 표1[[#모두]],[Col_2]] 만 바꿔준다.

=FILTER(표1[[#모두],[Col_2]],ISNUMBER(XMATCH(표1[[#모두],[Col_2]],표2[[#모두],[Filtering_Data]])))

 

결과는 아래와 같다.

Filter 결과

 

 

이제 Filtering Data에 원하는 필터링 값들을 입력만 해주면 되고, 이를 응용하여 or 또는 and 조건을 추가하여 원하는 형태의 자동 필터링 시트를 만들어 낼 수 있다.

 

끝.