엑셀 필터기능 사용하는 방법 (FILTER함수)

엑셀에서 데이터 정렬을 위해 사용하는 FILTER 함수에 대해 알아보겠습니다.

 

FILTER함수의 구문은 다음과 같습니다.

=FILTER(array,include,[if_empty])

• array – 데이터가 있고 데이터에서 일부 데이터를 필터링하려는 셀 범위입니다
• include – 함수에 필터링할 레코드를 알려주는 조건입니다.
• [if_empty] – FILTER 함수에서 결과를 찾을 수 없는 경우 반환할 항목을 지정할 수 있는 선택적 인수입니다. 기본적으로(지정하지 않은 경우) #CALC! 오류

1. 동일값을 기준으로 데이터 필터링

아래와 같은 데이터 세트가 있고 US에 대한 모든 레코드만 필터링한다고 가정합니다.

 

 

 

 

아래는 이를 수행하는 FILTER 수식입니다.

 

=FILTER($A$2:$C$11,$B$2:$B$11="US")

 

 

 

 

위의 공식은 데이터 세트를 배열로 사용하고 조건은 $B$2:$B$11="US"입니다.

이 조건은 FILTER 함수가 B 열(영역이 있는 셀)의 모든 셀을 검사하고 이 기준과 일치하는 레코드만 필터링하도록 합니다.

또한 이 예에서는 원본 데이터와 필터링된 데이터가 동일한 시트에 있지만 별도의 시트나 통합 문서에도 있을 수 있습니다.

필터 함수는 동적 배열인 결과를 반환합니다(즉, 하나의 값을 반환하는 대신 다른 셀로 유출되는 배열을 반환함).

 

이것이 작동하려면 결과가 비어 있는 영역이 있어야 합니다. 이 영역의 셀(이 예에서는 E2:G5)에 이미 무언가가 있는 경우 함수는 #SPILL 오류를 표시합니다.

또한 이것은 동적 배열이므로 결과의 일부를 변경할 수 없습니다. 결과가 있는 전체 범위를 삭제하거나 E2 셀(수식이 입력된 위치)을 삭제할 수 있습니다. 이 두 가지 모두 전체 결과 배열을 삭제합니다. 그러나 개별 셀을 변경하거나 삭제할 수 없습니다.

위의 공식에서 region 값을 하드 코딩했지만 셀에 넣은 다음 region 값이있는 셀을 참조 할 수도 있습니다.

 

예를 들어, 아래 예에서 I2 셀에 지역 값이 있고 이는 수식에서 참조됩니다.

 

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

 

 

이렇게 하면 수식이 훨씬 더 유용해지며, 이제 I2 셀의 지역 값을 변경하기만 하면 필터가 자동으로 변경됩니다.

또한 I2 셀에 드롭다운을 사용하여 선택하기만 하면 필터링된 데이터가 즉시 업데이트됩니다.

 

Also read: Excel LAMBDA Function

 

2. 범위에 따라 데이터 필터링

또한 filter 함수 내에서 비교 연산자를 사용하여 특정 값보다 크거나 작은 모든 레코드를 추출할 수 있습니다.

예를 들어 아래와 같은 데이터 세트가 있고 판매 값이 10000보다 큰 모든 레코드를 필터링한다고 가정합니다.

 

 

 

 

아래 공식은 이를 수행할 수 있습니다.

 

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

 

 

 

 

 

배열 인수는 전체 데이터셋을 참조하며 이 경우 조건은 ($C$2:$C$11>10000)입니다.

수식은 각 레코드에서 C 열의 값을 확인합니다. 값이 10000보다 크면 필터링되고, 그렇지 않으면 무시됩니다.

 

10000 미만의 모든 레코드를 가져 오려면 아래 공식을 사용할 수 있습니다.

 

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

 

 

판매 값을 기준으로 상위 3 개 레코드를 필터링하려면 아래 공식을 사용할 수 있습니다.

 

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

 

 

위의 공식은 LARGE 함수를 사용하여 데이터 세트에서 세 번째로 큰 값을 가져옵니다. 그런 다음 이 값은 FILTER 함수 조건에 사용되어 판매 값이 세 번째로 큰 값보다 크거나 같은 모든 레코드를 가져옵니다.

 

3. 두개 조건을 데이터 필터링

아래 데이터 세트가 있고 판매 값이 10000을 초과하는 미국의 모든 레코드를 필터링한다고 가정합니다.

 

 

 

이것은 두 가지를 확인해야 하는 AND 조건입니다 – 지역은 미국이어야 하고 판매량은 10000개 이상이어야 합니다. 하나의 조건만 충족되는 경우 결과를 필터링해서는 안 됩니다.

 

다음은 미국을 지역으로 하고 10000개 이상의 판매량을 기록하는 FILTER 수식입니다.

 

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

 

 

 

 

기준(include 인수라고 함)은 ($B$2:$B$11="US")*($C$2:$C$11>10000)입니다.

두 가지 조건을 사용하고 있고 둘 다 참이어야 하기 때문에 곱셈 연산자를 사용하여 이 두 기준을 결합했습니다. 이것은 0과 1의 배열을 반환하며, 여기서 1은 두 조건이 모두 충족되는 경우에만 반환됩니다.

 

기준을 충족하는 레코드가 없는 경우 함수는 #CALC! 오류.

오류 대신 의미있는 것을 반환하려는 경우 아래와 같이 수식을 사용할 수 있습니다.

 

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

 

 

여기서는 "Not Found"를 세 번째 인수로 사용했는데, 이는 기준과 일치하는 레코드를 찾을 수 없을 때 사용됩니다.

 

Also read: TAKE Function in Excel

 

 

4. OR조건을 사용하여 데이터 필터링

FILTER 함수에서 'include' 인수를 수정하여 OR 기준(지정된 조건 중 하나가 참일 수 있음)을 확인할 수도 있습니다.

예를 들어 아래와 같은 데이터 집합이 있고 국가가 미국 또는 캐나다인 레코드를 필터링한다고 가정합니다.

 

 

 

 

아래는 이를 수행하는 공식입니다.

 

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

 

 

 

 

위의 공식에서는 덧셈 연산자를 사용하여 두 조건을 추가하기만 하면 됩니다. 이러한 각 조건은 TRUE와 FALSE의 배열을 반환하기 때문에 조건 중 하나가 충족되면 TRUE가 되는 결합된 배열을 얻기 위해 추가할 수 있습니다.

또 다른 예는 국가가 미국이거나 판매 값이 10000 이상인 모든 레코드를 필터링하려는 경우일 수 있습니다.

아래 공식은 이를 수행합니다.

 

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

 

FILTER 함수에서 AND 기준을 사용하는 경우 곱하기 연산자(*)를 사용하고 OR 조건을 사용하는 경우 더하기 연산자(+)를 사용합니다.

 

5. 수식을 사용하여 데이터 정렬

FILTER 함수를 다른 함수와 함께 사용하면 더 많은 작업을 수행할 수 있습니다.

예를 들어 FILTER 함수를 사용하여 데이터 집합을 필터링하는 경우 SORT 함수를 함께 사용하여 이미 정렬된 결과를 가져올 수 있습니다.

 

아래와 같은 데이터 세트가 있고 판매 값이 10000을 초과하는 모든 레코드를 필터링한다고 가정합니다. 함수와 함께 SORT 함수를 사용하여 결과 데이터가 판매 값을 기준으로 정렬되도록 할 수 있습니다.

 

 

 

 

아래 공식은 이를 수행합니다.

 

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

 

 

 

 

위의 함수는 FILTER 함수를 사용하여 C 열의 판매 값이 10000 이상인 데이터를 가져옵니다. FILTER 함수에서 반환된 이 배열은 SORT 함수 내에서 사용되어 판매 값을 기준으로 이 데이터를 정렬합니다.

SORT 함수의 두 번째 인수는 3이며, 세 번째 열을 기준으로 정렬합니다. 그리고 네 번째 인수는 -1이며, 이는 이 데이터를 내림차순으로 정렬하는 것입니다.