MS Excel의 고급 필터. 여러 선택 조건의 공동 작업 "작업"을 위한 Excel 규칙의 고급 필터

Excel에서 데이터를 필터링하면 특정 순간에 사용자가 관심을 갖는 정보가 표시됩니다. 큰 테이블로 작업하는 프로세스를 크게 단순화합니다. 열에 표시될 데이터와 목록에서 제외되는 데이터를 모두 제어할 수 있습니다.

추가 방법

"삽입" - "표" 탭 또는 "홈" 탭을 통해 정보를 작성한 경우 - "표 형식", 필터링 옵션은 기본적으로 활성화됩니다. 원하는 버튼이 오른쪽 상단 셀에 위치한 화살표 형태로 표시됩니다.

블록을 데이터로 채운 다음 테이블 형식으로 서식을 지정한 경우 필터를 켜야 합니다. 이렇게 하려면 필요한 버튼이 맨 위 행에 추가될 것이기 때문에 제목이 있는 행을 포함하여 전체 셀 범위를 선택합니다. 그러나 데이터가 있는 셀에서 시작하는 블록을 선택하면 첫 번째 행은 필터링된 정보를 참조하지 않습니다. 그런 다음 데이터 탭으로 이동하여 필터 버튼을 클릭합니다.

예에서 화살표가 있는 버튼은 헤더에 있으며 이것이 정확합니다. 그 아래의 모든 데이터는 필터링됩니다.

Excel에서 표를 만드는 방법에 대한 질문에 관심이 있는 경우 링크를 따라 이 주제에 대한 기사를 읽으십시오.

어떻게 작동합니까

이제 Excel에서 필터가 어떻게 작동하는지 살펴보겠습니다. 다음 데이터를 예로 들어보겠습니다. 3개의 열이 있습니다. "제품명", "Category" 및 "Price"에 다양한 필터를 적용합니다.

원하는 열의 상단 셀에 있는 화살표를 클릭합니다. 여기에 이 열에 있는 모든 셀의 반복되지 않는 데이터 목록이 표시됩니다. 각 값 옆에 확인 표시가 있습니다. 목록에서 제외하려는 값의 확인란을 선택 취소합니다.

예를 들어 "카테고리"에 과일만 남겨두자. "야채" 상자의 선택을 취소하고 "확인"을 클릭합니다.

필터가 적용된 테이블의 해당 열에 대해 해당 아이콘이 상단 셀에 나타납니다.

삭제 방법

Excel에서 데이터 필터를 제거해야 하는 경우 셀을 클릭합니다. 동아이콘을 클릭하고 메뉴에서 선택 "(열 이름)에서 필터 제거".

Excel에서 정보를 필터링할 수 있습니다. 다른 방법들... 텍스트 필터와 숫자 필터를 구별하십시오. 열의 셀에 텍스트 또는 숫자가 쓰여지면 그에 따라 적용됩니다.

필터 사용

수치

"가격"열에 "숫자 ..."를 적용합시다. 상단 셀의 버튼을 클릭하고 메뉴에서 해당 항목을 선택합니다. 드롭다운 목록에서 데이터에 적용할 조건을 선택할 수 있습니다. 예를 들어 가격이 "25" 미만인 모든 제품을 표시합니다. 우리는 "덜"을 선택합니다.

해당 필드에 원하는 값을 입력합니다. 필터링의 경우 논리 "AND" 및 "OR"을 사용하여 여러 조건을 적용할 수 있습니다. "AND"를 사용할 때 - 두 조건이 모두 충족되어야 하고 "OR"을 사용할 때 - 지정된 조건 중 하나가 충족되어야 합니다. 예를 들어 "덜" - "25" - "AND" - "더" - "55"로 설정할 수 있습니다. 따라서 가격 범위가 25~55인 품목은 제외합니다.

예제에서는 이렇게 얻었습니다. "가격"이 25 미만인 모든 데이터가 여기에 표시됩니다.

텍스트

"텍스트 필터"예제 테이블에서 열에 적용할 수 있습니다. "제품명"... 상단에 화살표가 있는 버튼을 클릭하고 메뉴에서 같은 이름의 항목을 선택합니다. 예를 들어 열리는 드롭다운 목록에서 "start with"를 사용합니다.

"ka"로 시작하는 제품을 테이블에 남겨 두십시오. 다음 창에서 필드에 "ka *"를 씁니다. "확인"을 클릭합니다.

"*" 단어에서 일련의 문자를 바꿉니다. 예를 들어 "포함" - "s * l" 조건을 설정하면 테이블, 의자, 팔콘 등의 단어가 유지됩니다. "?" 모든 문자를 대체합니다. 예를 들어 "b? 톤" - 덩어리, 새싹, 콘크리트. 5글자로 이루어진 단어를 남겨야 한다면 "?????"라고 쓰세요. ...

그렇게 나는 내가 필요로 하는 것들을 남겼어 "제품명".

셀 색상별

필터는 텍스트 색상 또는 셀 색상으로 사용자 정의할 수 있습니다.

만들자 "색상으로 필터링"열 셀 "제품명"... 화살표가 있는 버튼을 클릭하고 메뉴에서 같은 이름의 항목을 선택합니다. 빨간색을 선택합시다.

결과적으로 빨간색 제품 만 남아 있거나 오히려 선택한 색상으로 채워진 모든 셀이 남았습니다.

텍스트 색상별

이제 사용된 예에서는 빨간색 과일만 표시됩니다.

모든 테이블 셀을 표시하려면 먼저 빨간색, 녹색, 파란색 등이 표시되도록 하려면 Excel에서 정렬을 사용합니다. 링크를 클릭하면 해당 주제에 대한 기사를 읽을 수 있습니다.

고급 필터는 자동 필터와 비교할 때 고급스럽고 진정으로 무한하고 고유한 기능을 가지고 있기 때문에 그렇게 불립니다. 이 기사에서는 데이터베이스 테이블 작업을 위한 MS Excel 도구에 대해 계속해서 알고 있습니다.

고급 필터가 어떻게 작동하는지 자세히 살펴보겠습니다. 자동 필터와 마찬가지로 Excel의 고급 필터는 나머지 정보를 쉽게 인식하거나 선택한 데이터베이스 레코드만 인쇄하기 위해 사용자의 보기에서 정보의 일부를 숨기도록 설계되었습니다.

고급 필터를 사용하면 테이블에 있는 모든 것을 필터링할 수 있습니다!!! 이 도구의 기능의 한계는 사용자의 상상일 뿐입니다!

당신은 데이터베이스 생성에 대한 일련의 기사 중 네 번째 게시물을 읽고 있습니다. MS 뛰어나다 그리고 조직 정보 처리.

사이클의 이전 기사에서 방법에 대해 읽을 수 있습니다.

필터 # 2 - 고급 필터!

시작하려면 데이터베이스 위에 다른 테이블을 만들어야 합니다. 여기에는 고급 필터를 선택하는 기준이 되는 데이터가 표시됩니다. (때때로 이러한 테이블을 새 시트에 배치하는 것이 더 편리합니다.)

샘플 데이터베이스를 생성할 때 테이블 위에 나중에 데이터를 분석할 때 필요하다고 몇 줄을 남겨두었던 것을 기억하십니까? 이 시간이 왔습니다.

1. MS Excel에서 파일을 엽니다.

2. 고급 필터 선택 기준 테이블을 만들 때 먼저 해결해야 할 작업에 대해 생각하지 않고 공식적으로 행동하는 것이 좋습니다. 복사 모두 A7 ... F7 셀에서 A1 ... F1 셀로의 열 필드 헤더 - 공백기준 테이블이 준비되었습니다! 본업을 시작할 수 있습니다.

필터의 안정적이고 오류 없는 작동을 위해서는 선택 기준 테이블과 데이터베이스 테이블 사이에 최소한 하나의 빈 줄이 있어야 합니다!

Excel의 고급 필터를 계속 연구하여 BD2 "섹션 2에 의한 금속 구조 생산" 데이터베이스 작업의 예를 사용하여 실제 문제를 해결합니다.

작업 번호 5:

전체 기간 동안의 모든 주문에 대한 빔 생산에 대한 정보를 표시합니다.

1. 필터링 매개 변수 - 상위 선택 기준 테이블의 "제품"열에 "빔"이라는 단어를 기록합니다.

2. 데이터베이스 테이블 내부의 모든 셀을 활성화("마우스로 세우기")합니다. 그러면 "고급 필터" 드롭다운 대화 상자에서 "초기 범위" 창을 자동으로 채울 수 있습니다.

3. 프로그램의 주 메뉴를 통해 Excel 2003에서 고급 필터를 활성화합니다. "데이터" - "필터" - "고급 필터"를 선택합니다.

4. 드롭다운 고급 필터 창에서 이 텍스트 아래의 스크린샷과 같이 창을 채우십시오.

고급 필터를 사용하면 목록을 제자리에서 필터링할 수 있지만 필터링 결과를 사용자가 지정한 다른 위치로 복사할 수도 있습니다.

5. 고급 필터의 결과는 다음 스크린샷에 있습니다. 고급 필터는 "항목" 열에 "빔"이라는 단어가 포함된 모든 데이터베이스 레코드를 표시했습니다. 작업이 완료되었습니다.

대소문자는 필터링 결과에 영향을 미치지 않습니다!

여러 선택 조건의 공동 "작업"규칙

Excel의 고급 필터를 사용하면 매우 복잡한 필터링 조건 조합으로 필터링할 수 있습니다. 여러 열에 대해 여러 조건을 설정하거나, 한 열에 여러 조건을 설정하거나, 수식으로 필터 매개변수를 설정할 수 있습니다. 다양한 옵션이 있습니다!

그러나 두 가지 주요 규칙이 있습니다! 다른 모든 경우는 이 두 규칙의 다른 조합입니다.

1. 선발 조건 게시됨 한 줄에기준 테이블의 하나 이상의 다른 열에서 동시에 실행되는 행을 표시하도록 고급 필터에 지시 예외 없이 모두이러한 조건. (선택 매개변수는 논리 연산자 "AND"로 연결됩니다.)

2. 선발 조건 게시됨 다른 라인에기준 테이블의 하나 이상의 열에서 고급 필터에 지시하여 다음과 같은 모든 행을 표시합니다. 적어도 하나이러한 조건에서. (선택 매개변수는 논리 OR 연산자로 연결됩니다.)

위 규칙의 동작을 예로 들어 설명하고 복잡한 문제를 해결할 때 Excel의 고급 필터가 어떻게 작동하는지 보여줍니다.

이전 예에서 고급 필터가 활성화된 경우의 사용자 동작 순서를 자세히 설명했으므로 아래에는 선택 조건과 최종 필터링 결과만 표시됩니다.

문제 번호 6:

전체 데이터베이스에서 0.1톤 미만의 플레이트에 대한 정보를 필터링합니다.

확장된 필터 작업의 결과로 질량이 0.1톤 미만인 모든 데이터베이스 플레이트가 표시됩니다(규칙 1번 그림).

문제 번호 7:

데이터베이스에서 사용 가능한 모든 플레이트와 1톤을 초과하는 기타 제품에 대한 정보를 표시합니다.

문제가 해결되었습니다. 데이터베이스의 모든 판과 1톤보다 무거운 모든 제품에 대한 기록이 표시됩니다(규칙 2번 그림).

확장 필터의 동작을 취소하려면 프로그램의 메인 메뉴 명령을 실행해야 합니다.뛰어나다"데이터" - "필터" - "모두 표시".

드롭다운 고급 필터 대화 상자에서 초기 데이터베이스 범위 및 조건 테이블 범위 지정의 정확성을 신중하게 제어해야 할 필요성에 주의를 기울입니다!

특히 마지막 예에서는 다음을 지정해야 합니다. "조건 범위: $ A $ 1: $ 에프$3 »!

조건 범위 또는 데이터베이스에 완전히 빈 줄이 있으면 고급 필터가 작동하지 않습니다!

결과.

검색어를 입력할 때 일반적인 와일드카드와 수학 기호를 사용할 수 있습니다.

* - 임의의 문자 수
? - 임의의 하나의 문자
= - 같음
< — меньше
> - 더
<= — меньше или равно
> = - 더 많거나 같음
<>- 같지 않다

Excel의 고급 필터는 유연하고 유용합니다. 선택 기준은 항상 선택 기준 테이블의 형태로 사용자의 눈앞에 있어 매우 편리합니다.

레코드가 많고 실제 데이터베이스에 수천 개의 고유 레코드가 있는 경우 드롭다운 목록에서 스크롤을 사용해야 하므로 자동 필터를 사용하여 필요한 레코드를 필터링하는 것이 문제가 됩니다. 확장 필터는 이러한 단점이 없습니다.

이 도구의 기능에 대해 "더 깊이 파고들기" - 알아낼 가치가 있습니다! 업무의 효율성이 높아지면 공부하는 시간이 몇 배나 겹칠 것입니다!

주제의 계속 대용량 정보의 저장 및 관리- 사이클의 다음 기사에서.

나는 구걸 존경하는 작가의 작품 구독하다 기사 발표를 위해 각 기사 끝의 상자 또는 페이지 상단의 상자에!

독자 여러분, 페이지 하단의 의견에 질문과 의견을 작성하십시오.

그리고 나서, 할 수 있도록 빠르게필요한 데이터만 선택하고, 숨김불필요한 문자열테이블. 따라서 필터는 삭제하지 않고 Excel 테이블 행은 일시적으로 숨깁니다.

필터에 의해 숨겨진 테이블의 행은 사라지지 않습니다. 높이가 0이 된다고 조건부로 상상할 수 있습니다(행의 높이와 열의 너비를 변경하는 것에 대해 이야기했습니다). 따라서 필터에 의해 숨겨지지 않은 나머지 선은 그대로 "함께 접착"됩니다. 결과는 필터링된 테이블입니다.

외부적으로 Excel의 필터가 있는 테이블은 다른 테이블과 동일하게 보이지만 각 열의 위쪽 부분에 특수 화살표가 나타납니다. 아래 그림에서 필터가 적용된 테이블의 예를 볼 수 있습니다.

이제 실제로 테이블에 필터를 추가하는 방법을 살펴보겠습니다.

Excel 스프레드시트를 필터링하는 방법

그건 매우 쉬워요! 헤더가 있는 테이블이 있다고 가정해 보겠습니다. Excel 표의 필터는 표의 특정 부분에 적용되기 때문에 바로 이 부분이 가장 먼저 표시되어야 합니다. 가장 밝은 부분... Excel 자체는 사용자가 원하는 것을 절대 추측하지 않으므로 아래 그림과 같이 표 머리글이 있는 행에서 필요한 셀을 선택합니다.

[사진을 클릭하시면 확대됩니다]

메모:

모든 행을 선택하면 Excel에서 자동으로 최소한 무언가를 포함하는 모든 열에 필터를 설정합니다. 열에 아무것도 없으면 필터가 적용되지 않습니다.

필요한 열을 선택한 후 실제로 필터를 설치해야 합니다. 가장 쉬운 방법은 리본의 "기본" / "필터 및 정렬" 탭으로 이동하여 필터를 클릭하는 것입니다. 다음은 Office 2010의 스크린샷입니다. 다른 Excel 버전에서는 필터가 동일한 방식으로 테이블에 적용됩니다.

[사진을 클릭하시면 확대됩니다]

이 버튼을 클릭하면 선택한 셀에 필터가 추가됩니다.

여러 셀을 선택하지 않고 하나를 클릭하면 필터가 마치 선택한 것처럼 전체 행에 추가됩니다.

내 예에서는 그림에 표시된 세 개의 열 중 두 개에만 필터가 추가되며 그 중 셀은 이전에 선택되었습니다. 아래 스크린샷에서 결과를 볼 수 있습니다.

[사진을 클릭하시면 확대됩니다]

표에 대해 필터를 활성화하는 표시된 예는 가장 간단한 예입니다. 어떻게 작동하는지 봅시다.

Excel에서 필터를 사용하는 방법

필터를 켠 직후 테이블은 변경되지 않습니다(열 머리글에 나타나는 화살표 제외). 필요한 데이터 중 일부를 필터링하려면 필터링 기준으로 사용할 열의 화살표를 클릭하십시오. 실제로는 아래 그림과 같습니다.

[사진을 클릭하시면 확대됩니다]

필터의 의미:

Excel은 THIS(구성된 필터 포함) 열에 선택한 값이 있는 셀이 포함된 테이블 행만 남겨둡니다. 다른 라인은 숨겨집니다.

필터링을 제거하려면(필터를 제거하지 않고!) 모든 확인란을 선택하기만 하면 됩니다. 필터가 완전히 제거되면 동일한 효과가 나타납니다. 테이블은 원래 형태로 돌아갑니다.

Excel 표에 여러 필터 적용

Excel 표의 필터를 결합할 수 있습니다. 논리적 "AND" 원칙에 따라 여러 필터가 적용됩니다. 이것이 의미하는 바는 예를 들어 보겠습니다.

테이블에 여러 열이 있는 경우 첫 번째 열과 같이 먼저 필터를 설정할 수 있습니다. 이 경우 필터 조건에 따라 선의 일부가 숨겨집니다. 그런 다음 다음 열에서 필터를 설정합니다. 이 경우 첫 번째 필터 후 남은라인은 추가적으로필터링되었습니다. 간단히 말해서, 첫 번째 열과 두 번째 열에서 한 번에 각 행에 2개의 필터가 적용됩니다.

각 후속 필터를 적용할 때 ALL 필터의 조건을 만족하는 테이블에 표시되는 행의 수가 감소합니다. 따라서 여러 필터를 순차적으로 적용하면 수천 개의 행으로 구성된 거대한 테이블에서 작은 항목을 빠르게 선택할 수 있습니다.

결과 샘플을 사용하여 추가 작업을 수행할 수 있습니다. 예를 들어, 이러한 행을 복사하여 다른 테이블로 전송할 수 있습니다. 또는 완전히 삭제하려는 행을 필터링할 수 있습니다! 그런 다음 필터를 삭제한 후 제거하면 불필요한 데이터가 제거된 테이블을 얻을 수 있습니다.

이제 15,000줄 이상의 가격표에서 데이터를 필터링하는 실제 예를 보여주는 비디오를 시청하십시오.

요약하자면

Excel에서 큰 표로 작업해야 하고 특정 데이터를 빠르게 선택할 수 있어야 하는 경우 필터를 사용하면 많은 시간을 절약할 수 있습니다. 표가 클수록 필터의 이점이 커집니다.

위의 예는 내 튜토리얼에서 가져온 것입니다. 마이크로 소프트 엑셀... 더 많은 필터 사용 어려운 조건내가 교실에서 고려하는 선택.

필터 작업을 고려한 예에서 파일을 다운로드할 수 있습니다.

Excel 데이터 필터링에는 자동 필터와 고급 필터의 두 가지 필터가 포함됩니다. 큰 데이터 세트가 있지만 전체 배열에서 특정 날짜, 특정 사람 등과 관련된 데이터를 보거나 선택해야 한다고 가정합니다. 이를 위한 필터가 있습니다. 이 도구를 처음 접하는 사람들을 위해 필터는 삭제하지 않지만 사용자가 지정한 필터링 조건을 충족하지 않는 레코드를 숨깁니다.

첫 번째는 가장 간단한 작업을 위해 설계된 자동 필터입니다. 특정 값이 있는 레코드 강조 표시(예: LeBron James와 관련된 레코드만 강조 표시), 특정 범위(또는 평균 또는 상위 10개 이상)에 있는 데이터 또는 셀 / 특정 색상의 글꼴(그런데 매우 편리함). 따라서 사용이 매우 쉽습니다. 필터링된 보려는 데이터를 선택하기만 하면 됩니다. 그런 다음 "데이터" / "필터" 명령. 목록 확인란은 상단 테이블의 각 상단 셀에 표시되며 각 명령을 이미 이해하기 쉽고 배우고 설명하기 쉽습니다. 더 이상 필요하지 않으며 자동 필터 사용의 뉘앙스만 있으면 됩니다.

1) 비파괴 범위에서만 작동합니다. 더 이상 한 시트에서 두 개의 서로 다른 목록을 필터링할 수 없습니다.

2) 테이블의 최상위 행은 자동으로 헤더로 지정되며 필터링에 참여하지 않습니다.

3) 다른 열에 모든 필터를 적용할 수 있지만 필터가 적용되는 순서에 따라 일부 조건이 적용되지 않을 수 있습니다. 이전 필터는 이미 필수 항목을 숨겼습니다. 여기에서는 문제가 없습니다. 이러한 항목은 어쨌든 숨겨지지만 여러 필터 세트를 사용하려는 경우 적용이 가장 적은 조건으로 시작하는 것이 좋습니다.

실제 적용: 예를 들어 오류를 찾거나 데이터를 확인하기 위해 이 목록에서 작업합니다. 자동 필터 적용 후 전체 테이블을 하나씩 살펴보고 이미 본 데이터를 순차적으로 표시할 수 있습니다. "지우기" 및 "재적용" 버튼은 조건이 적용된 후 테이블의 모양을 결정합니다. 그런 다음 표 작업을 마친 후 데이터 자체를 변경하지 않고 글꼴을 원래 형태로 되돌릴 수 있습니다. 그건 그렇고 어떤 조건을 적용하면 테이블의 모든 레코드가 사라지는 사실에 혼란스러워하는 사람들이 있습니다. 글쎄, 자세히 살펴보면 이러한 조건을 충족하는 레코드가 없는 조건을 설정했습니다. 테이블이 필터링된다는 사실은 테이블 행 번호가 파란색으로 강조 표시된다는 것입니다.

이제 고급 필터로 넘어 갑시다. 자동 필터와 더 미세하게 조정되지만 데이터를 필터링할 때 선택의 폭이 넓어집니다. 특히:

1) 필요한 만큼 조건을 설정합니다.

2) 고유한(반복되지 않는) 데이터가 있는 셀을 선택할 수 있습니다. 이것은 데이터로 작업할 때 종종 필요하며 이 옵션은 문제를 훌륭하게 처리합니다.

3) 필터 결과를 메인 어레이를 건드리지 않고 별도의 장소에 복사할 수 있습니다.

따라서 이 필터로 작업할 때의 주요 차이점은 먼저 조건 테이블을 준비해야 한다는 것입니다. 그것은 간단하게 이루어집니다. 기본 테이블의 제목이 복사되어 우리에게 편리한 위치에 삽입됩니다(기본 테이블 위에 제안함). 이 테이블에는 조건을 결정한 후 기본 테이블에 들어가지 않도록 너무 많은 줄이 있어야 합니다.

조건의 예:

1) 'L *' - L로 시작하는 셀

2) '> 5' - 5보다 큰 데이터

필터링된 테이블에서 행을 삭제하면 이웃을 데려가지 않고 삭제됩니다. 저것들. 테이블이 필터링되고 26-29 및 31-25 행이 표시되면 모든 행을 선택하고 삭제해도 30 행이 삭제되지 않습니다. 이것은 편리합니다. 개인적으로 매크로를 작성할 때 자주 사용합니다. 이것의 장점은 무엇입니까 - 우리는 종종 작업 형식으로 가져와야 하는 테이블을 얻습니다. 예를 들어 빈 줄을 삭제합니다. 우리가 하는 일: 테이블을 필터링하여 필요하지 않은 행만 표시한 다음 제목을 포함하여 전체 테이블을 삭제합니다. 불필요한 행과 제목이 제거되고 테이블에 공백이 없으며 단일 범위입니다. 미리 준비된 영역에서 간단한 복사 작업으로 헤더 행을 추가할 수 있습니다. 매크로를 작성할 때 이것이 왜 중요한가요? 원하지 않는 데이터가 어느 행에서 시작되는지 알 수 없고 어떤 행에서 삭제를 시작할지 명확하지 않으므로 전체 테이블을 삭제하면 이 문제를 빠르게 해결하는 데 도움이 됩니다.

Excel의 고급 필터를 사용하면 스프레드시트 데이터를 더 잘 제어할 수 있습니다. 설정하기가 더 어렵지만 작업에서 훨씬 더 효율적입니다.

표준 필터의 도움으로 Microsoft Excel 사용자는 모든 작업을 결코 해결할 수 없습니다. 적용된 필터링 조건이 시각적으로 표시되지 않습니다. 두 개 이상의 선택 기준을 적용할 수 없습니다. 고유한 레코드만 유지하기 위해 중복 값을 필터링할 수 없습니다. 그리고 기준 자체는 도식적이고 간단합니다. 고급 필터의 기능은 훨씬 더 풍부합니다. 그 능력을 좀 더 자세히 살펴보자.