집 › 멀티미디어 › 목록 조건을 기준으로 데이터를 필터링합니다. 목록의 데이터를 필터링합니다. 데이터 양식을 사용하여 필터링

목록 조건을 기준으로 데이터를 필터링합니다. 목록의 데이터를 필터링합니다. 데이터 양식을 사용하여 필터링

작업의 목표:데이터 정렬 수행, 목록 항목 필터링 방법 익히기, 자동 필터링 및 데이터 양식 작업.

연습 1.

표 5.5의 데이터를 구매자 성의 알파벳순, 거래 금액의 내림차순, 거래 날짜의 오름차순, 특성(성, 날짜, 금액)의 조합에 따라 여러 번 정렬합니다.

작업 수행 방법

1. 새 통합 문서를 열고 작업 폴더에 "정렬"로 저장합니다. .

2. 그림 5.56과 같은 테이블을 생성한다.

그림 5.56 - 데이터가 포함된 초기 테이블

3. 테이블의 서식 옵션을 설정합니다.

글꼴 시간 새 로마자, 글꼴 크기 12pt, 제목, 굵은 스타일 및 가운데 정렬, 단어 줄 바꿈, 회색 채우기; 주요 부분의 경우. 참고로 서식 지정 명령은 리본에서 사용할 수 있습니다. 홈 Þ 셀 .

4. 구매자의 성 필드를 기준으로 정렬하려면 이 열의 아무 곳에나 커서를 놓고 명령을 실행합니다. 데이터 Þ 정렬 (그림 5.51) .

열리는 대화 상자의 필드에서 정렬 기준구매자의 성을 선택하세요. 오름차순.

5. 4단계의 모든 단계를 반복하고 "거래 금액"을 기준으로 내림차순으로 정렬을 설정합니다.

6. '거래 날짜' 필드를 기준으로 오름차순으로 다시 정렬합니다.

7. 테이블을 다음 위치에 복사합니다. 새로운 잎일련의 특성에 따라 분류합니다. 이렇게 하려면 다음 명령을 호출하세요. 데이터 Þ 정렬. 설치하다 정렬 기준성은 오름차순으로, 그런 다음날짜를 오름차순으로, 마지막으로,내림차순으로 금액입니다.

8. 명령 사용 이름 바꾸기 이 두 시트에 이름을 지정하십시오.

작업 2. 자동 필터 명령을 기반으로 목록에서 정보를 선택합니다.

작업 수행 방법.

1. 시트 4에 표를 만들고 표 5.5의 정보로 채웁니다.

2. Sheet4의 이름을 "AutoFilter #1"로 변경합니다.

3. 자동 필터링을 적용하려면 목록 영역에 커서를 놓고 명령을 실행합니다. 데이터 Þ필터. 테이블 열 이름 옆에 아래쪽 화살표가 나타나 가능한 값 목록이 표시됩니다. "Gender" 열에서 "M"을 선택하고 표를 시트 5에 복사하고 이름을 "Autofilter No. 2"로 바꿉니다.

4. "자동 필터 1번" 시트의 "성별" 열에서 필터링 목록을 열고 "모두"를 선택합니다. 그런 다음 "생년월일" 열의 필터링 목록에서 "조건"을 선택하고 조건을 설정합니다(그림 5.57).

표 5.5

성	이름	입사일	생일	바닥	샐러리	나이
파슈코프	이고르	16.05.74	15.03.49	중
안드레바	안나	16.01.93	19.10.66	그리고
에로킨	블라디미르	23.10.81	24.04.51	중
포포프	알렉세이	02.05.84	07.10.56	중
틴코프	블라디미르	03.11.88	19.07.41	중
노킨	유진	27.08.85	17.08.60	중
쿠브리나	마리나	20.04.93	26.06.61	그리고
구드코프	니키타	18.03.98	05.04.58	중
고르바토프	남자 이름	09.08.99	15.09.52	중
비스트로프	알렉세이	06.12.00	08.10.47	중
크릴로바	타티아나	28.12.93	22.03.68	그리고
베르셰바	올가	14.12.01	22.12.74	그리고
루사노바	희망	24.05.87	22.01.54	그리고

그림 5.57 - 필터링 조건 설정

5. 필터링된 테이블을 시트 6에 복사하고 이름을 "Autofilter No. 3"으로 바꿉니다. 자동 필터 1번 시트에서 선택을 취소합니다.

그림 5.58 - 사용자 정의 필터

6. “성” 열의 필터링 목록에서 “조건”을 선택하고, 성이 “B”로 시작하는 모든 사원을 선택하는 조건을 설정합니다(그림 5.58).

7. 필터링된 목록을 시트 7에 복사하고 이름을 "Autofilter No. 4"로 바꿉니다.

8. "성" 열의 "자동 필터 번호 1" 시트에서 "모두"를 설정하고 "급여" 열에서 "처음 10..."을 설정합니다. 여기서 대화 상자에 "가장 큰 5개 요소 표시"를 입력합니다. 목록의”.

9. 파일을 저장합니다.

작업 3.고급 필터 명령을 사용하여 목록에서 레코드를 선택합니다.

작업 수행 방법론.

1. 시트 8로 이동하여 이름을 "고급 필터"로 바꿉니다.

2. 이전 작업의 표(표 5.5)를 이 시트에 복사하여 7행부터 붙여넣습니다. 처음 6행은 조건 설정용으로 예약되어 있습니다.

3. 다양한 조건을 만들어 봅시다. 5,000루블 이상을 버는 직원의 이름을 선택해야 한다고 가정해 보겠습니다. 또는 50세를 초과한 사람. 그림 5.59와 같이 조건을 채워준다.

그림 5.59 - 고급 필터의 조건

4. 명령을 실행하십시오 데이터 Þ 추가 . 다음과 같이 대화 상자를 작성합니다(그림 5.60).

그림 5.60 – 고급 필터 매개변수 창

선정 결과를 봅니다. 한 줄에 조건을 쓰면 논리 AND가 구현되고, 다른 줄에 조건을 쓰면 논리 OR로 연결된 것으로 간주됩니다. 우리는 첫 번째 옵션을 고려했으며 이제 두 번째 옵션을 고려하겠습니다.

5. 성이 문자 A, G 또는 N으로 시작하는 직원만 표시해야 한다고 가정합니다. 조건 범위를 입력합니다(그림 5.61).

그림 5.61 - 고급 필터의 조건

6. 명령을 실행하십시오. 데이터Þ추가 대화 상자를 작성합니다(그림 5.62).

그림 5.62 - 고급 필터 매개변수 창

레코드 선택 결과를 봅니다.

1. 모든 직원을 나열하십시오. 값평균 이상입니다. 이 필터를 만들기 전에 H2 셀에 =AVERAGE(F8:F20) 수식을 입력하여 평균 급여를 계산하세요.

2. 그런 다음 셀 A2에 셀 H2를 참조하는 계산된 조건 =F8>$H$2를 입력합니다(그림 5.63 및 5.64).

그림 5.63 - 고급 필터의 조건

그림 5.64 - 고급 필터 매개변수

필터는 데이터의 하위 집합을 찾아 목록에서 작업하는 빠르고 쉬운 방법입니다. 필터링된 목록에는 기준을 충족하는 행만 표시됩니다. 정렬과 달리 필터는 목록의 항목 순서를 변경하지 않습니다. 필터링하면 표시하고 싶지 않은 행이 일시적으로 숨겨집니다.

필터링을 통해 선택된 행은 행 순서를 변경하거나 이동하지 않고도 편집, 서식 지정, 차트 생성, 인쇄가 가능합니다.

필터링은 필요한 데이터만 선택하고 나머지 데이터는 숨깁니다. 이렇게 하면 보고 싶은 것만 표시되며, 클릭 한 번으로 가능합니다.

필터링할 때 데이터는 어떤 방식으로도 변경되지 않습니다. 필터가 제거되면 모든 데이터는 필터가 적용되기 전과 동일한 형식으로 다시 나타납니다.

Excel에서는 목록 필터링에 사용할 수 있는 두 가지 명령이 있습니다.

자동필터, 선택 항목별 필터 포함 간단한 조건선택.
고급 필터이상 어려운 상황선택.

자동필터

사용하려면 자동필터표에서 셀을 선택한 다음 탭에서 선택해야 합니다. 데이터 그룹에서 정렬 그리고 필터 큰 버튼을 누르세요 :

그런 다음 각 열 제목 오른쪽에 있는 테이블 헤더에 아래쪽 화살표 버튼이 나타납니다.

화살표를 클릭하면 해당 열에 대한 목록 메뉴가 열립니다. 목록에는 열의 모든 요소가 알파벳순 또는 숫자순(데이터 유형에 따라 다름)으로 포함되어 있으므로 필요한 요소를 빠르게 찾을 수 있습니다.

하나의 열에만 필터가 필요한 경우 나머지 열에는 화살표 버튼을 표시할 필요가 없습니다. 이렇게 하려면 버튼을 누르기 전에 제목과 함께 원하는 열의 여러 셀을 선택합니다.

정확한 값으로 필터링

켜다 자동필터, 화살표 버튼을 클릭하고 드롭다운 목록에서 값을 선택합니다. 열의 모든 요소를 빠르게 선택하거나 모든 요소를 선택 취소하려면 해당 항목을 클릭하세요. (모두 선택) :

이 경우 필드에 선택한 값이 포함되지 않은 모든 행이 숨겨집니다.

함으로써 실험실 작업, 필터링 결과를 선택하고 시트의 다른 위치에 복사한 후 서명하세요.

끄려면 자동필터버튼을 다시 눌러야 해 .

필터링 모드를 종료하지 않고 필터 작업을 취소하려면 버튼을 클릭하고 드롭다운 목록에서 항목을 선택하세요. (모두 선택) . 이 경우 필터에 의해 숨겨진 테이블 행이 나타납니다.

데이터 필터링의 징후

필터는 데이터를 숨깁니다. 이것이 바로 그들이 설계된 것입니다. 그러나 데이터 필터링을 알 수 없는 경우 일부 데이터가 누락된 것처럼 보일 수 있습니다. 예를 들어, 다른 사람의 필터링된 시트를 열거나 이전에 자신이 필터를 적용했다는 사실을 잊어버릴 수도 있습니다. 따라서 시트에 필터가 있으면 다양한 시각적 단서와 메시지를 찾을 수 있습니다.

(창 왼쪽 하단에 위치) 초기 상태:

데이터를 필터링한 직후 상태 표시줄 왼쪽 하단에 필터 적용 결과가 표시됩니다. 예를 들어, " 발견된 기록: 11개 중 2개”:

줄 번호 . 점선 번호는 일부 선이 숨겨져 있음을 나타내며 숫자의 색상이 변경되었습니다. 보이는 선선택한 행이 필터 선택의 결과임을 나타냅니다.

화살표 종류 . 필터링된 열의 자동 필터 화살표가 로 변경되면 해당 열이 필터링되었음을 나타냅니다.

”

""는 숫자나 날짜가 있는 열에 적용할 수 있는 또 다른 범용 필터입니다.

""는 매우 일반적인 이름입니다. 실제로 이 필터의 기능은 훨씬 더 넓습니다. 이 필터를 사용하면 첫 번째 요소나 마지막 요소(가장 작거나 큰 숫자 또는 날짜)를 찾을 수 있습니다. 그리고, 필터 이름과 달리, 얻어지는 결과는 처음 10개 요소 또는 마지막 10개 요소로 제한되지 않습니다. 표시되는 항목 수는 1부터 500까지 선택할 수 있습니다.

”을 사용하면 열의 총 행 수에 대한 백분율을 기준으로 데이터를 필터링할 수도 있습니다. 열에 100개의 숫자가 포함되어 있고 가장 큰 15개 숫자를 보려면 15%를 선택합니다.

필터를 사용하여 가격이 가장 높거나 낮은 제품을 찾거나, 가장 최근에 고용된 직원 목록을 확인하거나, 성적이 최고 또는 최저인 학생 목록을 볼 수 있습니다. 데이터 열에 "" 필터를 적용하려면( 숫자나 날짜만!!!), 열의 화살표를 클릭하고 항목을 선택합니다. 숫자 필터 더 나아가 :

그런 다음 대화 상자가 열립니다. 씌우다 정황 에 의해 목록 :

대화 상자에서 선택 숫자(행 또는 백분율), 가장 큰또는 가장 작은, 목록 요소또는 요소 수의 %.

나만의 맞춤 필터 만들기

예를 들어 문자 '로 시작하는 위치의 행만 출력해야 합니다. 디'. 이렇게 하려면 첫 번째 열의 자동 필터 화살표를 클릭하고 텍스트 필터 , 다음을 가리킨다 시작하다… :

대화 상자가 나타납니다 (오른쪽에서 어떤 항목을 선택하든 동일한 대화 상자가 계속 나타납니다.):

현장에서 직위선택하다 - 시작하다 , 오른쪽에 우리가 들어갑니다 디:

창문에서 힌트가 있습니다:

물음표 " ? ”는 임의의 한 문자를 의미합니다.

징후 " * ”는 일련의 문자를 나타냅니다.

필터링을 사용하여, 즉 지정된 기준을 충족하는 행을 제외하고 목록의 모든 행을 숨겨 목록에서 필요한 데이터를 선택할 수 있습니다. 필터링 기능을 사용하려면 목록 헤더 셀 중 하나(이 테이블에서는 A1:U11 범위임)에 테이블 커서를 놓고 다음 명령을 호출해야 합니다. 데이터/필터/자동 필터. 활성화되면 드롭다운 화살표가 있는 작은 사각형이 각 헤더 셀의 오른쪽 하단에 나타납니다.

다음 예제를 사용하여 자동 필터를 사용하는 방법을 살펴보겠습니다. 기업에서 더 강한 성행위를 하는 대표자가 몇 명인지 알아봅시다. 성별이라는 제목이 있는 셀에 있는 필터 버튼을 클릭하고 열리는 목록에서 문자 M(남성)을 선택합니다. 필터: 선택 메시지가 상태 표시줄에 나타납니다(그림 4.20). 지정된 기준을 충족하지 않는 모든 행은 숨겨집니다. 목록 버튼의 화살표가 파란색으로 바뀌어 이 분야의자동 필터가 활성화되었습니다.

쌀. 4.20.자동 필터를 사용하여 "M"(남성)을 기준으로 레코드 선택

이들 중 상사가 몇 명인지 확인하려면 직책 셀에서 자동 필터 버튼을 클릭하고 해당 목록에서 Chief라는 단어를 선택하세요. 지정된 기준을 만족하는 행 수를 나타내는 메시지가 상태 표시줄에 나타납니다. 발견된 레코드: 10개 중 2개(즉, 답변이 즉시 제공됩니다). 결과는 그림 1에 나와 있습니다. 4.21.

특정 열을 기준으로 필터링을 취소하려면 해당 열에서 자동 필터 목록을 열고 모두를 선택하세요. 그러나 여러 컬럼에 필터링 기능을 설정한 경우에는 이 작업을 여러 번 반복해야 합니다. 이 경우 명령을 사용하는 것이 좋습니다 데이터/필터/모두 표시.

쌀. 4.21.'남자 상사' 기준으로 직원 목록을 필터링한 후의 워크시트

데이터를 입력할 때 주의를 기울이면 필터링 기능이 제대로 작동합니다. 특히, 텍스트 데이터의 시작과 끝 부분에 불필요한 공백이 없는지 확인해야 합니다. 화면에서는 눈에 띄지 않지만 잘못된 결과를 초래할 수 있고 이를 식별하는 데 많은 시간이 소요됩니다.

필터링은 주어진 기준을 정확히 충족하는 데이터를 선택합니다. 따라서 "Head"라는 단어 대신 "Head_"라는 단어가 열에 나타나는 경우, 즉 끝에 공백이 있으면 Excel에서는 이러한 값을 다른 값으로 처리합니다. 이런 종류의 불일치를 제거하려면 "Boss"라는 단어가 있는 셀을 클립보드에 복사하고 "Boss_"로 선택하는 필터를 활성화한 다음 잘못된 값을 버퍼의 내용으로 바꾸십시오.

Excel에서 데이터 필터링을 사용하여 하나/여러 매개변수에 대한 정보를 표시할 수 있습니다.

이 목적을 위한 두 가지 도구가 있습니다: 자동 필터와 고급 필터. 조건에 맞지 않는 데이터는 삭제되지 않지만 숨겨집니다. 자동 필터는 가장 간단한 작업을 수행합니다. 고급 필터에는 훨씬 더 많은 옵션이 있습니다.

Excel의 자동 필터 및 고급 필터

목록으로 형식이 지정되거나 선언되지 않은 간단한 테이블이 있습니다. 기본 메뉴를 통해 자동 필터를 활성화할 수 있습니다.

데이터 범위를 테이블 형식으로 지정하거나 목록으로 선언하면 자동 필터가 즉시 추가됩니다.

자동 필터를 사용하는 것은 간단합니다. 원하는 값이 있는 항목을 선택해야 합니다. 예를 들어, 4번 매장으로의 배송을 표시합니다. 해당 필터링 조건 옆에 확인 표시를 합니다.

결과는 즉시 확인됩니다.

도구의 특징:

자동 필터는 중단되지 않는 범위에서만 작동합니다. 동일한 시트에 있는 다른 테이블은 필터링되지 않습니다. 동일한 유형의 데이터가 있더라도 마찬가지입니다.
이 도구는 맨 위 줄을 열 제목으로 처리합니다. 이 값은 필터에 포함되지 않습니다.
한 번에 여러 필터링 조건을 적용하는 것이 허용됩니다. 그러나 각각의 이전 결과는 다음 필터에 필요한 레코드를 숨길 수 있습니다.

고급 필터에는 훨씬 더 많은 옵션이 있습니다.

필요한 만큼 필터링 조건을 설정할 수 있습니다.
데이터 선택 기준이 표시됩니다.
고급필터를 이용하면 사용자가 쉽게 찾을 수 있습니다. 고유한 값여러 줄 배열로.

Excel에서 고급 필터를 만드는 방법

미리 만들어진 예 - Excel에서 고급 필터를 사용하는 방법:

원래 테이블에는 "Moscow" 값이 포함된 행만 남아 있습니다. 필터링을 취소하려면 "정렬 및 필터" 섹션에서 "지우기" 버튼을 클릭해야 합니다.

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

Excel에서 고급 필터를 사용하여 "Moscow" 또는 "Ryazan"이라는 단어가 포함된 행을 선택해 보겠습니다. 필터링 조건은 동일한 열에 있어야 합니다. 우리의 예에서는 서로 아래에 있습니다.

고급 필터 메뉴 작성:

주어진 기준에 따라 선택된 행이 포함된 테이블을 얻습니다.

"Store" 열에 "No. 1" 값이 포함되고 Cost 열에 ">1,000,000 루블" 값이 포함된 행을 선택해 보겠습니다. 필터링 기준은 조건 테이블의 해당 열에 있어야 합니다. 한 줄에.

필터링 매개변수를 입력합니다. 확인을 클릭하세요.

"지역" 열에 "Ryazan"이라는 단어가 포함되어 있거나 "비용" 열에 ">10,000,000 루블" 값이 포함된 행만 테이블에 남겨 두겠습니다. 선택 기준은 서로 다른 열에 속하므로 해당 제목 아래의 서로 다른 줄에 배치합니다.

"고급 필터" 도구를 사용해 보겠습니다.

이 도구는 수식을 사용하여 사용자가 배열에서 값을 선택할 때 발생하는 거의 모든 문제를 해결할 수 있습니다.

기본 규칙:

수식의 결과가 선택 기준입니다.
작성된 수식은 TRUE 또는 FALSE를 반환합니다.
초기 범위는 절대 참조를 사용하여 지정되고 선택 기준(수식 형식)은 상대 참조를 사용하여 지정됩니다.
TRUE가 반환되면 필터가 적용된 후 행이 표시됩니다. 거짓 - 아니요.

평균 이상의 수량을 포함하는 행을 표시해 보겠습니다. 이렇게 하려면 기준이 있는 플레이트(I1 셀) 외에 "최대 수량"이라는 이름을 입력합니다. 아래는 공식입니다. AVERAGE 함수를 사용합니다.

소스 범위에서 셀을 선택하고 "고급 필터"를 호출하십시오. I1:I2를 선택 기준으로 표시합니다(상대 링크!).

"수량" 열의 값이 평균보다 높은 행만 테이블에 남아 있습니다.

테이블에 반복되지 않는 행만 남기려면 '고급 필터' 창에서 '고유한 레코드만' 옆의 확인란을 선택하세요.

확인을 클릭하세요. 중복된 줄은 숨겨집니다. 시트에는 고유한 항목만 남습니다.

목록의 데이터를 필터링하는 것은 주어진 기준에 따라 데이터를 선택하는 것입니다. 사용 가능한 데이터 중에서 필요한 데이터를 선택할 수 있는 작업입니다.

필터를 사용하면 특정 조건에 맞는 데이터만 표시하고 볼 수 있습니다. Excel에서는 "자동 필터링"이라는 간단한 명령을 사용하여 목록에서 필요한 데이터를 빠르고 편리하게 볼 수 있습니다. 더 복잡한 쿼리데이터베이스에 대한 필터는 "고급 필터" 명령을 사용하여 구현할 수 있습니다.

자동 필터링

자동 필터링을 수행하려면 처음에 "수식에 따른 데이터 계산" 시트에서 새 "자동 필터링" 시트로 소스 데이터베이스를 복사해야 합니다. 그런 다음 목록 영역에 커서를 놓고 "Data" - "Filter" - "Autofilter" 명령을 실행합니다. 이것에 의해서 엑셀팀드롭다운 목록을 목록 열 이름에 직접 넣습니다. 화살표를 클릭하면 가능한 선택 기준 목록을 볼 수 있습니다. 버튼을 사용하여 필터를 할당한 경우 화살표가 파란색으로 변합니다. 다음 기준 목록 옵션을 사용할 수 있습니다.

· "모두" - 모든 레코드가 선택됩니다.

· "상위 10" - "목록에 조건 적용" 대화 상자에서 표시하려는 목록의 가장 작거나 가장 큰 요소 중 특정 수를 선택합니다.

· "값" - 이 열에 지정된 값을 생성하는 레코드만 선택됩니다.

· "조건" - "사용자 정의 자동 필터" 대화 상자에서 사용자가 생성한 조건에 따라 레코드가 선택됩니다.

· "비어 있음" - 열에 데이터가 포함되지 않은 행이 표시됩니다.

· "비어 있지 않음" - 열에 비어 있지 않은 줄이 포함된 레코드만 표시됩니다.

이 경우 "자동 필터링" 작업을 위해 다음 조건을 생성해야 합니다. "혜택" 필드에 "재향군인 또는 장애인" 값을 설정하고 "가족 구성원 수" 필드에 값을 설정해야 합니다. "3보다 크거나 같음"이라는 조건을 설정해야 합니다. 두 개의 열에 동시에 필터가 설치된다는 점에 따라 동시에 두 가지 조건에 따라 기록 필터링이 수행됩니다. 즉, 결과적으로 보훈 및 장애인 수당이 선택되며, 가족 수는 그 중 3 이상입니다. 그 결과 위의 조건을 만족하는 임차인을 찾았습니다. 이 결과그림 표 4 "자동 필터링"에 나와 있습니다.