자동 필터, 고급 필터, 수식 기반 조건 필터링, 그리고 전통적/동적 정렬까지 엑셀 데이터 탐색 및 관리의 모든 핵심 기술을 총동원하여, 엑셀을 단순한 데이터 나열 도구가 아닌 강력한 분석 및 보고서 자동화 시스템으로 활용해야 합니다.
필터링 및 정렬 기능을 다른 엑셀 함수(SUBTOTAL, AGGREGATE, FILTER, UNIQUE)와 결합하여 더욱 강력하고 자동화된 보고서를 만드는 고급 응용 방법을 다룰 예정입니다. 또한, 피벗 테이블의 필터링 및 정렬 기능을 활용하여 데이터 분석의 효율성을 극대화하는 내용까지 다루며, 엑셀 데이터 탐색 및 관리 역량을 완성시킬 수 있습니다!
왜 필터링 및 정렬의 고급 응용을 알아야 할까요?
실무에서 데이터는 독립적으로 존재하는 것이 아니라, 끊임없이 변화하고 서로 연결되어 있습니다.
필터링과 정렬은 데이터를 이해하는 강력한 도구이지만, 이들을 다른 함수나 기능과 결합할 때 진정한 시너지를 발휘합니다.
- 동적 집계: 필터링된 데이터에 대해서만 자동으로 합계, 평균 등을 계산합니다.
- 실시간 보고서: 조건 변경 시 필터링과 정렬 결과가 즉시 업데이트되는 보고서를 만듭니다.
- 데이터 클리닝 및 요약 자동화: 중복을 제거하고 정렬된 고유 목록을 손쉽게 만듭니다.
- 강력한 시각화: 필터링된 데이터에만 조건부 서식을 적용하여 중요한 패턴을 강조합니다.
이 고급 응용 기술들을 통해 여러분은 엑셀을 활용한 데이터 분석 및 보고서 작성의 효율성과 정확성을 최고 수준으로 끌어올릴 수 있을 것입니다.
주요 고급 응용 기술들
다음 핵심 응용 기술들을 실무 예제와 함께 자세히 살펴보겠습니다.
- 필터링된 데이터에 대한 집계 (SUBTOTAL, AGGREGATE)
- FILTER 함수와 SORT 함수를 결합한 동적 필터링 및 정렬 보고서 (엑셀 365)
- UNIQUE 함수와 SORT 함수를 결합하여 고유하고 정렬된 목록 생성 (엑셀 365)
- 조건부 서식과 필터/정렬의 시너지
- 피벗 테이블의 필터링 및 정렬 기능 활용
실무 예제로 배우는 엑셀 필터링 및 정렬 마스터하기
각 응용 기술을 실제 비즈니스 시나리오에 적용하여 가장 복잡한 데이터 분석 및 보고서 자동화를 구현하는 방법을 보여드리겠습니다.
샘플 데이터는 아래 '판매 기록' 테이블을 사용하겠습니다.
| 주문 ID | 고객명 | 제품 카테고리 | 제품명 | 판매액 (천 원) | 주문일 | 지역 |
| ORD001 | 김철수 | 전자제품 | 노트북 | 1,200 | 2025-05-10 | 서울 |
| ORD002 | 이영희 | 의류 | 티셔츠 | 80 | 2025-05-12 | 부산 |
| ORD003 | 박지성 | 전자제품 | 스마트폰 | 950 | 2025-05-15 | 서울 |
| ORD004 | 최민수 | 식품 | 과일 | 50 | 2025-05-18 | 대구 |
| ORD005 | 김철수 | 의류 | 바지 | 150 | 2025-05-20 | 부산 |
| ORD006 | 이영희 | 전자제품 | 태블릿 | 800 | 2025-06-01 | 서울 |
| ORD007 | 박지성 | 식품 | 채소 | 40 | 2025-06-05 | 대구 |
| ORD008 | 최민수 | 의류 | 스커트 | 110 | 2025-06-08 | 부산 |
| ORD009 | 김철수 | 전자제품 | 노트북 | 1,100 | 2025-06-10 | 대전 |
| ORD010 | 이영희 | 의류 | 코트 | 300 | 2025-06-15 | 서울 |
1. 필터링된 데이터에 대한 집계 (SUBTOTAL, AGGREGATE)
자동 필터가 적용된 상태에서 SUM, AVERAGE 같은 일반 함수를 사용하면 숨겨진 행도 포함하여 계산됩니다.
하지만 SUBTOTAL이나 AGGREGATE 함수는 오직 필터링되어 화면에 보이는 데이터에 대해서만 계산을 수행합니다.
- SUBTOTAL 기본 형식: =SUBTOTAL(함수_번호, 범위)
- 함수_번호: 1(AVERAGE), 2(COUNT), 9(SUM) 등 다양한 함수를 숫자로 지정. 필터링된 행만 포함(1~11), 숨겨진 행 포함(101~111) 등 옵션 제공.
- AGGREGATE 기본 형식: =AGGREGATE(함수_번호, 옵션, 범위, [k])
- 함수_번호: 1(AVERAGE), 9(SUM), 14(LARGE), 15(SMALL) 등 다양한 함수.
- 옵션: 1(숨겨진 행/오류 값 무시), 3(숨겨진 행 무시) 등 다양한 옵션.
예제 목표: '제품 카테고리'를 '전자제품'으로 필터링한 후, 필터링된 '판매액 (천 원)'의 총 합계와 평균을 계산하세요.
해결 방법:
- 먼저, 원본 데이터에 자동 필터를 적용합니다 (데이터 탭 → 필터).
- 제품 카테고리 열의 필터 화살표를 클릭하고 '전자제품'만 선택합니다.
- 필터링된 데이터 아래 (E12 셀 등)에 다음과 같이 수식을 입력합니다.
- 총 합계: =SUBTOTAL(9, E2:E11) 또는 =AGGREGATE(9, 1, E2:E11)
- 평균: =SUBTOTAL(1, E2:E11) 또는 =AGGREGATE(1, 1, E2:E11)
결과: '전자제품'으로 필터링된 판매액(1,200, 950, 800, 1,100)의 합계 4,050 (천 원)과 평균 1,012.5 (천 원)가 표시됩니다. 필터 조건을 변경하면 이 값들도 자동으로 업데이트됩니다.
최상급 실무 활용: SUBTOTAL과 AGGREGATE는 동적인 요약 보고서, 대시보드에서 필터 컨트롤과 연동되는 실시간 집계에 필수적입니다. 사용자가 필터를 조작할 때마다 전체 합계를 다시 계산할 필요 없이, 화면에 보이는 데이터에 대한 정확한 요약 정보를 제공합니다. AGGREGATE는 오류 값 무시 등 더 다양한 옵션을 제공하여 유연성이 높습니다.
2. FILTER 함수와 SORT 함수를 결합한 동적 필터링 및 정렬 보고서 (엑셀 365)
엑셀 365의 FILTER 함수로 특정 조건에 맞는 데이터를 동적으로 추출하고, 이 결과를 SORT 함수로 정렬하여 실시간으로 업데이트되는 보고서를 만들 수 있습니다.
- 기본 아이디어: =SORT(FILTER(원본_배열, 조건_배열), [정렬_기준_인덱스], [정렬_순서])
예제 목표: '서울' 지역이면서 '전자제품' 카테고리인 주문만 필터링하여 표시하고, 이 결과를 '판매액 (천 원)' 기준으로 내림차순 정렬하세요.
해결 방법: I1 셀 (결과가 나타날 시작 셀)에 다음 수식을 입력합니다. =SORT(FILTER(A1:G11, (G1:G11="서울")*(C1:C11="전자제품")), 5, -1)
- FILTER(A1:G11, (G1:G11="서울")*(C1:C11="전자제품")): '서울' 지역 '전자제품'에 해당하는 데이터를 필터링합니다. (AND 조건)
- SORT(..., 5, -1): 필터링된 결과의 5번째 열('판매액')을 기준으로 내림차순 정렬합니다.
결과: I1 셀부터 '서울' 지역 '전자제품' 주문만 '판매액' 내림차순으로 정렬되어 동적으로 펼쳐집니다.
| 주문 ID | 고객명 | 제품 카테고리 | 제품명 | 판매액(천 원) | 주문일 | 지역 |
| ORD001 | 김철수 | 전자제품 | 노트북 | 1,200 | 2025-05-10 | 서울 |
| ORD003 | 박지성 | 전자제품 | 스마트폰 | 950 | 2025-05-15 | 서울 |
| ORD006 | 이영희 | 전자제품 | 태블릿 | 800 | 2025-06-01 | 서울 |
최상급 실무 활용: 이 조합은 동적인 보고서 테이블, 대시보드의 특정 패널, 사용자 선택에 따라 실시간으로 변화하는 데이터 리스트를 만들 때 가장 강력합니다. 원본 데이터를 직접 필터링/정렬할 필요 없이, 원하는 조건과 정렬 순서에 따라 항상 최신 결과가 자동으로 표시됩니다.
3. UNIQUE 함수와 SORT 함수를 결합하여 고유하고 정렬된 목록 생성 (엑셀 365)
UNIQUE 함수는 범위에서 중복되지 않는 고유한 값만 반환하고, SORT 함수로 이를 정렬하면 정제된 목록을 얻을 수 있습니다.
- 기본 아이디어: =SORT(UNIQUE(범위))
예제 목표: '제품 카테고리'의 중복을 제거한 후 알파벳 순으로 정렬된 고유 목록을 생성하세요.
해결 방법: I15 셀 (결과가 나타날 시작 셀)에 다음 수식을 입력합니다. =SORT(UNIQUE(C2:C11))
결과: I15 셀부터 전자제품, 의류, 식품 (정렬된 고유 목록)이 동적으로 펼쳐집니다.
최상급 실무 활용: 데이터 유효성 검사의 드롭다운 목록 원본, 피벗 테이블의 필터/그룹화 기준, 보고서의 범주 목록 등을 동적으로 생성할 때 매우 유용합니다. 데이터가 추가/삭제되어도 목록이 자동으로 업데이트되므로, 수동으로 목록을 관리하는 번거로움이 사라집니다.
4. 조건부 서식과 필터/정렬의 시너지
논리 함수 시리즈에서 다루었던 조건부 서식은 필터링 및 정렬과 결합될 때 그 효과가 극대화됩니다. 필터링된 데이터에만 조건부 서식을 적용하거나, 조건부 서식으로 강조된 셀을 기준으로 정렬하여 중요한 정보를 더욱 부각시킬 수 있습니다.
예제 목표: 판매액이 평균(AVERAGE) 미만인 주문을 빨간색으로 강조하고, 이 빨간색 셀이 가장 위쪽에 오도록 정렬하세요.
해결 방법:
- 조건부 서식 적용:
- 원본 데이터의 '판매액 (천 원)' 열(E2:E11)을 선택합니다.
- 홈 탭 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정.
- 수식 입력: =E2<AVERAGE($E$2:$E$11) (첫 번째 데이터 행 기준 상대 참조)
- 서식 버튼 클릭 → 채우기 탭에서 빨간색 선택 → 확인.
- 색상으로 정렬:
- 데이터 범위 내 아무 셀 클릭 (예: A1).
- 데이터 탭 → 정렬 버튼 클릭.
- 정렬 대화 상자에서:
- 정렬 기준 - 판매액 (천 원).
- 정렬 방식 - 셀 색을 선택합니다.
- 순서 - 드롭다운 목록에서 빨간색을 선택하고, 위에를 선택합니다.
- 확인을 클릭합니다.
결과: 판매액이 평균 미만인 주문(ORD002, ORD004, ORD005, ORD007, ORD008, ORD010)이 빨간색으로 강조되고, 이 빨간색 셀들이 테이블의 가장 위에 모여 표시됩니다.
최상급 실무 활용: KPI(핵심 성과 지표) 모니터링, 위험 관리, 비상 상황 알림, 불량률 추적 등 시각적인 경고와 우선순위 지정이 필요한 모든 보고서에 활용됩니다. 사용자가 즉시 어떤 데이터에 집중해야 할지 알 수 있도록 돕습니다.
5. 피벗 테이블의 필터링 및 정렬 기능 활용
피벗 테이블은 엑셀에서 데이터를 요약하고 분석하는 가장 강력한 도구입니다.
피벗 테이블 자체 내에 필터링 및 정렬 기능이 내장되어 있어, 복잡한 함수 없이도 다양한 기준으로 데이터를 조작할 수 있습니다.
예제 목표: '제품 카테고리'별 '판매액' 합계를 요약하고, '제품 카테고리' 필드를 '전자제품', '의류', '식품' 순서로 정렬하세요.
해결 방법:
- 원본 데이터(A1:G11)를 선택하고 삽입 탭 → 피벗 테이블 → 확인을 클릭합니다.
- 피벗 테이블 필드 창에서 제품 카테고리를 행 영역으로, 판매액 (천 원)을 값 영역으로 드래그합니다.
- 피벗 테이블 필터링:
- 행 레이블 옆의 드롭다운 화살표를 클릭합니다.
- 레이블 필터나 값 필터를 사용하여 특정 조건으로 필터링할 수 있습니다 (예: '전자제품'만 선택).
- 피벗 테이블 정렬:
- 행 레이블에서 마우스 오른쪽 버튼 클릭 → 정렬 → 추가 정렬 옵션.
- 정렬 대화 상자에서 수동(자동 정렬 순서를 끌어서 놓을 수 있음)을 선택하거나, 오름차순/내림차순을 선택합니다.
- 특정 순서로 정렬하려면, 피벗 테이블의 레이블을 직접 드래그 앤 드롭하여 순서를 바꿀 수 있습니다. (예: '의류'를 '전자제품' 위로 드래그) 또는 '추가 정렬 옵션'에서 '내림차순 (수동 정렬을 사용하여 사용자 지정 목록 사용)'을 선택하여 사용자 지정 목록을 만들 수도 있습니다.
결과: '제품 카테고리'별 '판매액' 합계가 요약된 피벗 테이블이 생성되며, 원하는 기준으로 필터링하고 정렬할 수 있습니다.
최상급 실무 활용: 피벗 테이블은 다차원적인 데이터 분석, 대시보드의 백본, 그리고 복잡한 요약 보고서를 만들 때 가장 강력합니다.
함수로 데이터를 준비한 후 피벗 테이블로 분석하고, 피벗 테이블 자체의 필터링/정렬 기능을 활용하면 유연하고 직관적인 대화형 보고서를 구축할 수 있습니다.
SUBTOTAL, AGGREGATE, FILTER, UNIQUE, SORT 등 다양한 함수들을 필터링 및 정렬 기능과 결합하여 동적인 보고서를 만들고, 데이터를 시각적으로 강조하며, 효율적으로 집계하고 요약하는 고급 응용 방법을 심층적으로 살펴보았습니다. 또한, 피벗 테이블의 강력한 필터링 및 정렬 기능까지 다루면서 엑셀 데이터 탐색 및 관리 역량을 완성시켜 드렸습니다.