Excel에서 고급 필터를 사용하는 방법 - 공식이있는 기준 범위 예제

이 자습서는 Excel에서 고급 필터를 사용하는 방법을 보여주고 사소한 필터를 만들기 위해 여러 가지 사소한 기준 범위 예제를 제공하여 케이스에 민감한 필터를 만들고 두 열의 일치 및 차이점을 찾기, 더 작은 목록과 일치하는 레코드를 추출합니다.

이전 기사에서는 Excel Advanced 필터의 다양한 측면과이를 사용하여 행을 필터링하는 방법과 논리를 사용하는 방법에 대해 논의했습니다. 이제 기본 사항을 알았으므로 작업에 도움이 될 수있는보다 복잡한 기준 범위 예제를 살펴 보겠습니다.

• 공식 기반 기준 범위를 설정하는 방법
• 고급 필터 기준 범위 예제
  • 텍스트 값에 대한 대소 문자에 민감한 필터
  • 열에서 평균 위 또는 이하의 필터 값
  • 블랭크 / 비 냉방 셀로 줄을 필터링합니다
  • 상단 / 하단 N 레코드를 추출합니다
  • 필터 일치 및 두 열의 차이
  • 목록의 항목과 일치하거나 일치하지 않는 필터 행
  • 주말과 주중 필터

공식 기반 기준 범위 설정

이 튜토리얼에서 논의 된 대부분의 기준 범위에는 다양한 공식이 포함되므로 올바르게 설정하기위한 필수 규칙을 정의하는 것으로 시작하겠습니다. 저를 믿으십시오.이 작은 이론은 많은 시간을 절약하고 공식을 기반으로 여러 조건을 포함하는 복잡한 기준 범위를 문제 해결하는 두통을 절약 할 것입니다.

• 기준 범위에서 사용하는 공식은 true 또는 false 로 평가해야합니다.
• 기준 범위에는 최소 2 개의 세포 : 공식 셀 및 헤더 셀이 포함되어야합니다.
• 공식 기반 기준의 헤더 셀은 비어 있거나 테이블 (목록 범위) 제목과는 달라야합니다.
• 목록 범위의 각 행 에 대해 공식을 평가하려면 A1과 같은 상대 참조를 사용하여 데이터를 가진 상단 셀을 참조하십시오.
• 특정 세포 또는 세포의 범위 에 대해서만 공식을 평가하려면 $ 1와 같은 절대 참조를 사용하여 해당 셀 또는 범위를 참조하십시오.
• 공식의 목록 범위를 참조 할 때는 항상 절대 셀 참조를 사용하십시오.
• 여러 조건을 공급할 때 동일한 행의 모든 ​​기준을 입력하여 운영자 와 조인하고 각 기준을 별도의 행에 올려 OR 연산자와 조인하십시오.

Excel 고급 필터 기준 범위 예제

다음 예제는 일반 Excel 자동 필터를 사용하여 수행 할 수없는보다 복잡한 작업을 처리하기 위해 Excel에서 자신의 필터를 만드는 방법을 알려줍니다.

텍스트 값에 대한 대소 문자에 민감한 필터

Excel Autofilter뿐만 아니라 고급 필터 도구는 본질적으로 대소 문자를 사용하지 않으므로 텍스트 값을 필터링 할 때 대문자와 소문자를 구별하지 않음을 의미합니다. 그러나 고급 필터 기준에서 정확한 기능을 사용하여 사례에 민감한 검색을 쉽게 수행 할 수 있습니다.

예를 들어 바나나 와 바나나를 무시하고 바나나가 포함 된 행을 필터링하려면 기준 범위에 다음 공식을 입력하십시오.

=EXACT(B5, "Banana")

여기서 B는 항목 이름을 포함하는 열이고 5 행은 첫 번째 데이터 행입니다.

그런 다음 데이터 탭에서 고급 버튼을 클릭하여 Excel Advanced Filter를 적용하고 아래 스크린 샷에 표시된 것과 같은 목록 범위 및 기준 범위를 구성하십시오. 기준 범위 에는 헤더 셀 과 공식 셀의 2 개의 셀이 포함되어 있다는 점에주의하십시오.

메모. 위의 이미지 와이 튜토리얼의 추가 스크린 샷은 명확성을 위해서만 기준 범위 셀의 공식을 보여줍니다. 실제 워크 시트에서 공식 셀은 첫 번째 데이터 행이 기준과 일치하는지 여부에 따라 TRUE 또는 FALSE를 반환해야합니다.

열에서 평균 위 또는 이하의 필터 값

숫자 값을 필터링 할 때는 종종 열의 평균 값 위 또는 아래에있는 셀 만 표시 할 수 있습니다. 예를 들어:

평균 이상의 하위로 행을 필터링하려면 기준 범위에서 다음 공식을 사용하십시오.

=F5>AVERAGE($F$5:$F$50)

평균 미만의 하위로 행을 필터링하려면 다음 공식을 사용하십시오.

=F5<average></average>

우리는 상대적인 참조를 사용하여 데이터가있는 최상위 셀 (F5)을 참조하고 열 제목을 제외하고 평균을 계산하려는 전체 범위를 정의하는 절대 참조 ($ f $ 5 : $ f $ 50)에주의하십시오.

다음 스크린 샷은 위의 평균 공식을 보여줍니다.

Excel 번호 필터에 익숙한 사람들은 왜 내장 번호 필터가 이미 위의 평균 및 평균 이하의 옵션을 가지고있는 동안 누군가가 고급 필터를 사용하는 것을 귀찮게하는 이유는 무엇입니까? 맞습니다.하지만 내장 된 Excel 필터는 OR Logic과 함께 사용할 수 없습니다!

따라서이 예제를 더 취하기 위해 하위 토탈 (열 F) 또는 9 월 판매 (열 E)가 평균 이상인 행을 필터링하겠습니다. 이를 위해 별도의 행에 각 조건을 입력하여 OR 논리로 기준 범위를 설정하십시오. 결과적으로, 위의 평균 값이 e 또는 f에있는 항목 목록을 얻게됩니다.

공백 또는 맹장이없는 줄을 필터링합니다

모두가 알고 있듯이 Excel 필터에는 빈 셀을 필터링하기위한 내장 옵션이 있습니다. 자동 필터 메뉴에서 (공백) 확인란을 선택하거나 선택 해제하면 하나 이상의 열에 비어 있거나 비어 있지 않은 행이있는 행만 표시 할 수 있습니다. 문제는 빈 공란 용 Excel 필터가 및 논리에서만 작동 할 수 있다는 것입니다.

OR 논리와 함께 공백 또는 비 냉방 셀을 필터링하거나 다른 기준과 함께 빈 / 비 냉각 조건을 사용하려면 다음 공식 중 하나로 고급 필터 기준 범위를 설정하십시오.

블랭크 필터 :

top_cell = ""

비금장 필터 :

top_cell ""

OR 논리로 빈 셀을 필터링합니다

컬럼 A 또는 B에 빈 셀이있는 행을 필터로 또는 두 열에있는 경우 고급 필터 기준 범위를 구성하십시오.

• =A6=""
• =B6=""

여기서 6은 가장 큰 데이터 행입니다.

비정

Excel의 고급 필터가 여러 기준으로 어떻게 작동하는지에 대한 이해를 높이려면 다음 조건으로 샘플 테이블의 행을 필터링합시다.

• 영역 (열 A) 또는 항목 (열 B)은 맹장이 아니으며
• 서브 토탈 (열 C)은 900보다 큰 것입니다.

다르게 말하면 다음 조건을 충족하는 행을 표시하려고합니다.

( 서브 토탈 > 900 및 영역 = 비금장) 또는 ( 서브 토탈 > 900 및 항목 = 비-정액)

이미 알고 있듯이 Excel Advanced Filter 기준 범위에서, 조건 및 논리가 동일한 행에 입력해야하며, 다른 행으로 OR 논리와 연결된 조건 : 조건 :

이 예제의 한 기준은 공식 (비정형)으로 표현되고 다른 기준은 비교 연산자 (하위 토탈> 900)를 포함하기 때문에 다음을 상기시켜 드리겠습니다.

• 비교 연산자와 함께 형성된 기준은 위의 스크린 샷의 하위 기준과 같이 테이블 제목과 정확히 동일한 제목을 가져야합니다.
• 공식 기반 기준에는 빈 제목 셀 또는 위의 스크린 샷의 맹장이 아닌 기준과 같이 테이블 제목과 일치하지 않는 제목이 있어야합니다.

상단/하단 N 레코드를 추출하는 방법

아시다시피, 빌드 인 Excel 번호 필터에는 상위 10 개 또는 하단 10 항목을 표시하는 옵션이 있습니다. 그러나 상단 3 또는 하단 5 값을 필터링 해야하는 경우 어떻게해야합니까? 이 경우 다음 공식이있는 Excel Advanced Filter가 편리합니다.

상단 N 항목 추출 :

top_cell > = large ( 범위 , n)

하단 N 항목 추출 :

top_cell 범위 , n)

예를 들어, 상위 3 개의 하위 토탈을 필터링하려면이 공식으로 기준 범위를 만듭니다.

=F5>=LARGE($F$5:$F$50,3)

하단 3 소계를 추출하려면이 공식을 사용하십시오.

=F5>=SMALL($F$5:$F$50,3)

여기서 F5는 소계 열에 데이터가있는 최상위 셀입니다 (열 제목 제외).

다음 스크린 샷은 상위 3 가지 공식을 보여줍니다.

메모. 목록 범위에 상단/하단 N 목록에 해당하는 동일한 값이있는 몇 개의 행이있는 경우 아래 스크린 샷에 표시된 것처럼 모든 행이 표시됩니다.

일치 및 두 열의 차이에 대한 필터

이전 기사 중 하나는 Excel의 두 열을 비교하고 그 사이의 성냥과 차이점을 찾는 다양한 방법을 설명했습니다. Excel 공식, 조건부 서식 규칙 및 위의 튜토리얼에서 다루는 중복 리무버 도구 외에도 Excel의 고급 필터를 사용하여 두 개 이상의 열에서 동일하거나 다른 값을 가진 행을 추출 할 수도 있습니다. 이렇게하려면 기준 범위에 다음 간단한 공식 중 하나를 입력하십시오.

• 2 개의 열에서 일치하는 (복제) 필터 : =B5=C5
• 2 열의 차이 (고유 한 값)에 대한 필터 : =B5C5

여기서 B5와 C5는 비교하려는 두 열의 데이터가있는 가장 큰 셀입니다.

메모. 고급 필터 도구는 같은 행 에서 일치와 차이 만 검색 할 수 있습니다. 열 A에 있지만 B 열의 어느 곳에도없는 모든 값을 찾으려면이 공식을 사용하십시오.

목록에 일치하는 항목을 기반으로 한 줄을 필터링합니다

당신이 수백 또는 수천 행으로 큰 테이블을 가지고 있다고 가정하고, 주어진 순간에 관련된 항목 만 포함 된 짧은 목록을 받았습니다. 문제는 - 작은 목록에 있거나없는 테이블의 모든 항목을 어떻게 찾을 수 있습니까?

목록의 항목과 일치하는 필터 행

다음 countif 공식을 사용하여 작은 목록에있는 소스 테이블의 모든 항목을 찾으려면 다음과 같습니다.

countif ( list_to_match , top_data_cell)

더 작은 목록이 D2 : D7 범위에 있고 해당 목록과 비교할 테이블의 항목이 10 행부터 시작하는 열에 있다고 가정하면, 공식은 다음과 같습니다 (절대 및 상대 참조의 사용은 주목하십시오).

=COUNTIF($D$2:$D$7,B10)

물론, 당신은 단 하나의 기준으로 테이블을 필터링하는 데 국한되지 않습니다.

예를 들어, 목록과 일치하는 행을 필터링하려면 북쪽 지역 의 경우 동일한 행에 두 가지 기준을 입력하여 논리와 함께 작동합니다.

• 지역 : ="=North"
• 일치하는 항목 : =COUNTIF($D$2:$D$7,B10)

아래 스크린 샷에서 볼 수 있듯이 두 기준과 일치하는 두 가지 레코드 만 있습니다.

메모. 이 예에서는 텍스트 값 <em>="=North "</em> 대한 정확한 일치 기준을 사용하여 지정된 텍스트와 정확히 동일한 셀 만 찾기 위해. 동일한 부호와 이중 인용문없이 북쪽 으로 단순히 지역 기준을 입력하는 경우 Microsoft Excel은 지정된 텍스트 (예 : 북서 또는 북서쪽 으로 시작하는 모든 항목을 찾을 수 있습니다. 자세한 내용은 텍스트 값에 대해서는 Excel Advanced Filter를 참조하십시오.

목록의 항목과 일치하지 않는 필터 행

작은 목록에없는 테이블의 모든 항목을 찾으려면 Countif 공식의 결과가 0과 동일하는지 확인하십시오.

countif ( list_to_match , top_data_cell) = 0

예를 들어, 목록에 나타나는 테이블의 북쪽 지역 항목을 필터링하려면 다음 기준을 사용하십시오.

• 지역 : ="=North"
• 비 일치 품목 : =COUNTIF($D$2:$D$7,B10)=0

참고 :

• 일치하는 목록이 다른 워크 시트에 상주하는 경우, 공식에 시트 이름을 포함 시키십시오 (예 =COUNTIF(Sheet2!$A$2:$A$7,B10) .
• 다른 시트로 결과를 추출하려면 다른 워크 시트로 필터링 된 행을 추출하는 방법에 설명 된대로 대상 시트에서 고급 필터를 시작하십시오.

주말과 주중 필터

지금까지 고급 필터 기준 범위 예제는 대부분 숫자 및 텍스트 값을 다루었습니다. 이제 날짜에 일하는 사람들에게 단서를 제공 할 때입니다.

내장 된 Excel 날짜 필터는 많은 시나리오를 다루는 광범위한 옵션을 제공합니다. 많은 것은 아니지만 전부는 아닙니다! 예를 들어, 날짜 목록을 받고 평일과 주말을 필터링하라는 요청을 받았다면 어떻게 할 것입니까?

아시다시피, Microsoft Excel은 주어진 날짜에 해당하는 요일을 반환하는 특별 주중 기능을 제공합니다. 그리고 Excel Advanced Filter 기준 범위에서 사용할 것이이 기능입니다.

Excel에서 주말을 걸러내는 방법

평일 용어로 1은 일요일을 나타내고 6 개는 토요일에 나타나며 주말에 필터 주말에는 다음과 같습니다.

또는 (주중 ( 날짜 ) = 7, 주중 ( 날짜 ) = 1)

이 예에서는 5 행부터 시작하여 B 열에서 날짜를 필터링하고 있으므로 주말 공식은 다음과 같은 모양을 취합니다.

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

평일에 Excel에서 필터링하는 방법

주중을 필터링하려면 위의 공식을 수정하여 1 (일요일)과 7 (토요일)을 남기도록 수정하십시오.

및 (주중 ( 날짜 ) 7, 주중 ( 날짜 ) 1)

샘플 테이블의 경우 다음 공식이 처리됩니다.

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

또한 빈 셀을 걸러 내기 위해 하나의 조건을 추가 할 수 있습니다. =B5""

다른 방식으로 워크 시트의 날짜를 필터링하려면 관련 날짜 기능을 찾아서 주저하지 말고 고급 필터 기준 범위에서 사용하십시오.

글쎄, 이것은 복잡한 기준으로 Excel에서 고급 필터를 사용하는 방법입니다. 물론, 귀하의 옵션은이 튜토리얼에서 논의 된 예제에만 국한되지 않습니다. 우리의 목표는 단지 ​​당신에게 올바른 길을 가질 몇 가지 영감을주는 아이디어를 제공하는 것이 었습니다. 숙달의 길은 연습으로 포장되어 있다는 것을 기억하면 아래 링크를 사용하여 예제를 다운로드하고 더 나은 이해를 위해 확장 또는 리버스 엔지니어링을 원할 수 있습니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

연습 통합 문서

Excel 고급 필터 예제 (.xlsx 파일)

위 내용은 Excel에서 고급 필터를 사용하는 방법 - 공식이있는 기준 범위 예제의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!