Office 365는 XLOOKUP, SORT 및 FILTER와 같은 몇 가지 훌륭한 기능을 제공합니다.
Excel에서 데이터를 필터링할 때 Office 365 이전 세계에서는 대부분 Excel 기본 제공 필터 또는 기껏해야 고급 필터 또는 복잡한 SUMPRODUCT 수식에 의존했습니다.데이터 세트의 일부를 필터링해야 하는 경우 이는 종종 복잡한 해결 방법입니다.
그러나 새로운 FILTER 기능을 사용하면 조건에 따라 데이터 세트의 일부를 빠르게 필터링하는 것이 정말 쉬워졌습니다.
이 튜토리얼에서는 새로운 FILTER 기능이 얼마나 멋진지, 그리고 이 기능으로 할 수 있는 몇 가지 유용한 기능을 보여 드리겠습니다.
그러나 예제에 들어가기 전에 FILTER 함수의 구문을 간단히 살펴보겠습니다.
Excel에서 이러한 새로운 기능을 사용하려면 다음을 수행할 수 있습니다.Office 365로 업그레이드(모든 기능/수식에 액세스하려면 내부 프로그램에 가입)
내용
Excel 필터 기능 – 구문
다음은 FILTER 함수의 구문입니다.
=FILTER(배열, 포함,[if_empty])
- 정렬 - 이것은 데이터가 있고 그 데이터에서 일부 데이터를 필터링하려는 셀 범위입니다.
- 포함 - 어떤 레코드를 필터링할지를 함수에 알려주는 조건입니다.
- [빈 경우] – FILTER 함수가 결과를 찾지 못한 경우 반환할 항목을 지정할 수 있는 선택적 매개변수입니다.기본적으로(지정되지 않은 경우) #CALC를 반환합니다!실수
이제 필터 기능이 없으면 매우 복잡했던 필터 기능의 놀라운 예를 살펴보겠습니다.
예 1: 하나의 조건(지역)을 기반으로 데이터 필터링
아래와 같은 데이터 세트가 있고 미국의 모든 레코드만 필터링하려고 한다고 가정합니다.
다음은 이를 수행하는 FILTER 공식입니다.
=필터($A$2:$C$11,$B$2:$B$11="미국")
위의 수식은 데이터 세트를 배열로 사용하며 조건은 $B$2:$B$11="US"입니다.
이 조건으로 인해 FILTER 함수는 B 열의 모든 셀(범위가 있는 셀)을 확인하고 이 조건과 일치하는 레코드만 필터링합니다.
또한 이 예에서는 원본 데이터와 필터링된 데이터를 같은 시트에 넣었지만 별도의 시트나 통합 문서에 넣을 수도 있습니다.
필터 함수는 결과의 동적 배열을 반환합니다(즉, 값을 반환하는 대신 다른 셀로 오버플로되는 배열을 반환함).
이를 위해서는 결과가 비어 있는 영역이 있어야 합니다.범위(이 예에서는 E2:G5)의 모든 셀에 이미 무언가가 있으며 이 함수는 #SPILL 오류를 제공합니다.
또한 동적 배열이므로 결과의 일부를 변경할 수 없습니다.결과 또는 셀 E2(공식이 입력된 위치)로 전체 범위를 삭제할 수 있습니다.둘 다 전체 결과 배열을 삭제합니다.그러나 단일 셀을 변경하거나 삭제할 수는 없습니다.
위 수식에서 범위 값을 하드코딩했지만 셀에 넣고 해당 셀을 범위 값으로 참조할 수도 있습니다.
예를 들어 아래 예에서는 셀 I2에 범위 값이 있고 이를 수식에서 참조합니다.
=FILTER($A$2:$C$11,$B$2:$B$11=I1)
이렇게 하면 수식이 훨씬 더 유용해집니다. 이제 셀 I2의 범위 값을 변경하기만 하면 필터가 자동으로 변경됩니다.
또한 셀 I2에 드롭다운이 있어 간단히 선택하면 필터링된 데이터가 즉시 업데이트됩니다.
예 2: 하나의 기준(크거나 작음)을 기반으로 데이터 필터링
필터 함수에서 비교 연산자를 사용하고 특정 값보다 크거나 작은 모든 레코드를 추출할 수도 있습니다.
예를 들어 아래에 표시된 데이터 세트가 있고 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))
FILTER 공식을 사용하여 더욱 창의적으로 작업할 수도 있습니다.예를 들어 매출을 기준으로 상위 XNUMX개 레코드를 필터링하려면 다음 수식을 사용할 수 있습니다.
=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))
위의 수식은 LARGE 함수를 사용하여 데이터 세트에서 세 번째로 큰 값을 가져옵니다.그런 다음 FILTER 함수 조건에서 해당 값을 사용하여 매출이 세 번째로 큰 값보다 크거나 같은 모든 레코드를 가져옵니다.
예 3: 여러 조건을 사용하여 데이터 필터링(AND)
다음 데이터 세트가 있고 판매 값이 10000보다 큰 미국의 모든 레코드를 필터링하려고 한다고 가정합니다.
이것은 AND 조건이므로 두 가지를 확인해야 합니다. 지역이 미국에 있어야 하고 매출이 10000 이상이어야 합니다.조건이 하나만 충족되면 결과가 필터링되지 않아야 합니다.
다음은 지역이 미국이고 판매량이 10000개 이상인 레코드를 필터링하는 필터 공식입니다.
=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))
請注意，標準（稱為包含參數）是 ($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")
여기에서는 일치하는 레코드를 찾을 수 없을 때 사용되는 세 번째 매개변수로 "찾을 수 없음"을 사용했습니다.
예 4: 여러 기준(OR)을 사용하여 데이터 필터링
FILTER 함수에서 "contains" 매개변수를 수정하여 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($A$2:$C$11,C2:C11>AVERAGE(C2:C11))
다시 말하지만 평균 미만의 경우 다음 공식을 사용할 수 있습니다.
=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))
예 6: 짝수 레코드(또는 홀수 레코드)만 필터링
짝수 또는 홀수 행의 모든 레코드를 빠르게 필터링하고 추출해야 하는 경우 FILTER 함수를 사용하여 수행할 수 있습니다.
이렇게 하려면 FILTER 함수에서 줄 번호를 확인하고 줄 번호 조건과 일치하는 줄 번호만 필터링해야 합니다.
아래와 같은 데이터 세트가 있고 이 데이터 세트에서 짝수 레코드만 추출하려고 한다고 가정합니다.
이를 수행하는 공식은 다음과 같습니다.
=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)
위의 수식은 MOD 함수를 사용하여 각 레코드의 행 번호(ROW 함수로 제공)를 확인합니다.
MOD(ROW(A2:A11)-1,2)=0 수식은 행 번호가 짝수이면 TRUE를 반환하고 홀수이면 FALSE를 반환합니다.첫 번째 레코드가 두 번째 행에 있기 때문에 ROW(A2:A11) 부분에서 1을 빼서 두 번째 행을 첫 번째 레코드로 처리하도록 행 번호를 조정합니다.
마찬가지로 다음 공식을 사용하여 모든 홀수 레코드를 필터링할 수 있습니다.
=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)
예 7: 수식을 사용하여 필터링된 데이터 정렬
FILTER 기능을 다른 기능과 함께 사용하면 더 많은 작업을 수행할 수 있습니다.
예를 들어 FILTER 함수를 사용하여 데이터 세트를 필터링하는 경우 SORT 함수를 사용하여 정렬된 결과를 얻을 수 있습니다.
아래와 같은 데이터 세트가 있고 10000개 이상의 판매가 있는 모든 레코드를 필터링하려고 한다고 가정합니다.SORT 함수를 이 함수와 함께 사용하여 결과 데이터가 매출에 따라 정렬되도록 할 수 있습니다.
다음 공식이 이 작업을 수행합니다.
=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)
위의 함수는 FILTER 함수를 사용하여 매출이 10000보다 큰 C 열의 데이터를 가져옵니다.그런 다음 SORT 함수의 FILTER 함수에서 반환된 배열을 사용하여 매출에 따라 데이터를 정렬합니다.
SORT 함수의 두 번째 매개변수는 세 번째 열에 따라 정렬하는 3입니다.네 번째 매개변수는 -1로 데이터를 내림차순으로 정렬합니다.
그래서 이것들은 Excel에서 FILTER 함수를 사용하는 7가지 예입니다.
이 튜토리얼이 유용했기를 바랍니다..
