실무에서 가장 많이 사용되는 엑셀 - ⅷ

 

엑셀 실무에서 가장 많이 사용되는 함수와 기능: 여덟 번째 글

이번 글에서는 **고급 필터링(Advanced Filtering)**과 **피벗 테이블(Pivot Table)**을 활용하여 데이터를 효율적으로 분석하고 원하는 정보를 빠르게 추출하는 방법을 다룹니다. 이 두 기능은 대량의 데이터를 다룰 때 특히 유용하며, 실무에서 데이터 분석의 핵심 도구로 자리 잡고 있습니다. 예제와 함께 상세히 설명하겠습니다.

반응형

1. 고급 필터링(Advanced Filtering)

고급 필터링은 단순한 필터 기능보다 더 복잡한 조건을 설정하여 데이터를 필터링할 수 있는 기능입니다. 여러 조건을 동시에 적용하거나 특정 범위에 맞는 데이터를 추출할 때 유용합니다.

고급 필터링의 주요 특징

• 여러 조건을 AND 또는 OR 논리로 결합 가능.
• 필터링된 데이터를 원본 위치에 유지하거나 다른 위치로 복사 가능.
• 수식 기반 조건 설정 가능.

고급 필터링 사용법

1. 데이터를 정리하고, 각 열에 고유한 헤더를 설정합니다.
2. 조건 범위를 별도로 작성합니다(헤더 포함).
3. 상단 메뉴에서 데이터 > 정렬 및 필터 > 고급을 클릭합니다.
4. 목록 범위와 조건 범위를 선택하고, 필요 시 결과를 복사할 위치를 지정합니다.
5. 확인을 누르면 필터가 적용됩니다.

예시 1: 단일 조건 필터링

• 상황: 매출 데이터에서 특정 지역("서울")의 매출만 추출.
• 데이터:
  

  지역	매출액
  서울	100,000
  부산	120,000
  서울	90,000

   
• 조건 범위:
  

  지역

  서울

   
• 결과:
  

  지역	매출액
  서울	100,000
  서울	90,000

   

728x90

예시 2: 다중 조건(AND)

• 상황: "서울" 지역 중 매출액이 100,000 이상인 데이터만 추출.
• 조건 범위:
  

  지역	매츨액
  서울	>=100000

   
• 결과:
  

  지역	매출액
  서울	100,000

   

예시 3: 다중 조건(OR)

• 상황: "서울" 또는 "부산" 지역의 매출 데이터 추출.
• 조건 범위:
  

  지역

  서울

  부산

   
• 결과:
  

  지역	매출액
  서울	100,000
  부산	120,000
  서울	90,000

   

수식 기반 고급 필터링

수식을 활용하면 더욱 유연한 조건 설정이 가능합니다.

• 상황: 매출 데이터에서 "매출액이 평균 이상인 데이터"만 추출.
• 조건 범위:
  • 헤더: =B2>AVERAGE(B2:B5) (매출액 열 기준)
• 결과는 평균값 이상인 행만 표시됩니다.

2. 피벗 테이블(Pivot Table)

피벗 테이블은 대량의 데이터를 요약하고 분석하는 데 매우 강력한 도구입니다. 데이터를 다양한 관점에서 재구성하여 통계와 패턴을 빠르게 파악할 수 있습니다.

SMALL

피벗 테이블 생성 방법

1. 소스 데이터가 정리된 상태인지 확인합니다(헤더 포함).
2. 상단 메뉴에서 삽입 > 피벗 테이블을 클릭합니다.
3. 새 워크시트 또는 기존 워크시트에 피벗 테이블을 생성합니다.
4. 오른쪽에 나타나는 피벗 테이블 필드 창에서 열(Row), 값(Value), 열(Column), 필터(Filter)를 설정합니다.

예시: 매출 데이터 요약

• 데이터:
  

  제품명	지역	분기	매출액
  스마트폰	서울	Q1	100,000
  노트북	부산	Q1	120,000
  태블릿	서울	Q2	90,000

   

1. 피벗 테이블 생성 후 아래와 같이 설정:
   • 행(Row): 지역
   • 열(Column): 분기
   • 값(Value): 매출액
2. 결과:
3. 지역Q1Q2

   지역	Q1	Q2
   서울	100,000	90,000
   부산	120,000

    

피벗 테이블 고급 기능

1. 데이터 그룹화(Grouping):
   • 날짜 데이터를 연도별/월별로 그룹화 가능.
   • 예: 날짜 데이터를 "연도"와 "분기"로 그룹화하여 요약.
2. 슬라이서(Slicer) 추가:
   • 슬라이서를 사용해 특정 조건으로 데이터를 빠르게 필터링 가능.
   • 예: "지역" 슬라이서를 추가해 "서울"만 선택하여 분석.
3. 계산된 필드(Calculated Field) 추가:
   • 새로운 계산식 기반의 값을 피벗 테이블에 추가 가능.
   • 예: 매출액 * 수수료율 계산값 추가.