ITQ 엑셀 시험 (부분합/피벗 테이블)

1. 부분합 (Subtotal)

부분합은 데이터를 그룹화하여 각 그룹별로 합계, 평균 등 다양한 요약 값을 계산할 때 사용합니다. ITQ 시험에서는 주로 정렬 후 부분합 계산과 같은 문제가 자주 나옵니다.

사용법

1. 데이터를 정렬합니다. (부분합을 적용할 열을 기준으로 정렬해야 정확한 결과가 나옵니다.)
2. 데이터 탭 → 부분합 클릭
3. 부분합 대화 상자에서 다음 항목 설정:
   • 어느 필드를 기준으로 부분합을 구할지 선택 (예: 부서, 제품 등)
   • 사용할 함수 선택 (SUM, AVERAGE 등)
   • 부분합을 적용할 데이터 열 선택 (예: 판매량, 매출 등)
4. 옵션 선택:
   • 기존 부분합 대체: 기존 부분합을 지우고 새로 계산
   • 각 변경 항목 옆에 부분합 추가: 그룹별 부분합 표시
   • 데이터 사이에 페이지 나누기 삽입: 그룹이 끝날 때마다 페이지 나누기

예제

• 데이터: 제품별 판매 데이터

  제품명	지역	판매량
  A	서울	100
  A	부산	200
  B	서울	150

• 설정:
  • "제품명"으로 부분합
  • 합계(SUM) 함수 사용
  • 판매량에 부분합 적용

결과:
제품별로 판매량의 합계가 계산됩니다.

팁

• 정렬을 꼭 먼저 해야 올바른 결과가 나옵니다.
• 부분합을 여러 번 적용할 수도 있으므로, 기존 부분합을 덮어쓰는지 확인해야 합니다.

---

1. 부분합 (Subtotal) 예제

부분합은 데이터를 그룹별로 합계, 평균, 최대값 등을 계산할 때 사용합니다. 시험에서는 주로 특정 열을 기준으로 정렬 후 부분합 계산 문제가 출제됩니다.

예제

다음과 같은 제품별 판매 데이터에서 지역별 총 판매량과 평균 판매량을 계산합니다.

지역	제품명	판매량
서울	A	300
서울	B	500
부산	A	400
부산	B	600
대구	A	200

부분합 적용 방법

1. 정렬:
   • 데이터 범위를 선택하고 지역 열을 기준으로 오름차순 정렬합니다.
     • 데이터 탭 → 정렬 → 정렬 기준: 지역
2. 부분합 삽입:
   • 데이터 탭 → 부분합 클릭
   • 부분합 대화 상자에서 아래와 같이 설정합니다:
     • 어느 필드에서 부분합을 계산할지: 지역
     • 함수: 합계(SUM)
     • 부분합을 구할 열: 판매량
3. 추가 설정:
   • 기존 부분합 대체 체크
   • 각 변경 항목 옆에 부분합 추가 체크
   • 확인을 누르면 각 지역별 판매량 합계가 계산됩니다.

결과

서울 총합: 800

부산 총합: 1000

대구 총합: 200

 

 4. 평균 판매량 계산:

• 부분합 대화 상자를 다시 열고,
• 함수에서 **평균(AVERAGE)**을 선택한 뒤 적용합니다.

---

2. 피벗 테이블 (Pivot Table)

피벗 테이블은 복잡한 데이터를 요약 및 분석하는 강력한 도구입니다. ITQ 시험에서는 여러 필드(열)로 그룹화하고 요약하는 문제가 주로 나옵니다.

사용법

1. 데이터 범위 선택
2. 삽입 탭 → 피벗 테이블 클릭
3. 피벗 테이블 만들기 대화 상자에서 다음 설정:
   • 테이블이나 범위 선택
   • 새 워크시트 또는 기존 워크시트에 피벗 테이블 삽입
4. 피벗 테이블 필드 창에서 항목을 다음과 같이 드래그:
   • 행 영역: 그룹화할 필드 (예: 부서, 제품 등)
   • 열 영역: 열로 그룹화할 필드 (예: 월, 지역 등)
   • 값 영역: 합계를 구하거나 분석할 필드 (예: 판매량, 매출 등)
   • 필터 영역: 데이터의 특정 부분만 필터링

예제

• 데이터: 판매 데이터

  제품명	지역	판매량
  A	서울	100
  A	부산	200
  B	서울	150

• 설정:
  • 행: 제품명
  • 열: 지역
  • 값: 판매량 (SUM)

결과:
제품별, 지역별 판매량 합계가 표시됩니다.

               서울    부산

제품 A    100      200

제품 B    150        -

값 필드 설정 변경

• 값을 평균, 개수, 최대값 등으로 변경할 수 있습니다.
  • 설정 방법: 값 필드에서 마우스 오른쪽 클릭 → 값 필드 설정

팁

• 피벗 테이블에서는 데이터가 자동으로 그룹화되므로 원본 데이터가 정렬되지 않아도 상관없습니다.
• 피벗 차트와 결합하여 데이터를 시각적으로 표현하는 문제도 나올 수 있습니다.
• **슬라이서(Slicer)**를 추가해 피벗 테이블을 빠르게 필터링할 수 있습니다.

---

2. 피벗 테이블 (Pivot Table) 예제

피벗 테이블은 복잡한 데이터를 요약하고 다양한 방식으로 분석할 수 있는 강력한 도구입니다. 시험에서는 주어진 데이터를 필드별로 그룹화하고 요약하는 문제가 주로 출제됩니다.

예제

다음은 부서별, 월별 매출 데이터입니다. 피벗 테이블을 이용해 부서별 월 매출 총합을 계산해봅니다.

부서	월	매출액
영업	1월	1000
영업	2월	1500
인사	1월	800
인사	2월	1200
회계	1월	900
회계	2월	1000

피벗 테이블 작성 방법

1. 데이터 범위 선택:
   • A1범위를 선택합니다.
2. 피벗 테이블 삽입:
   • 삽입 탭 → 피벗 테이블 클릭
   • 새 워크시트에 테이블을 삽입합니다.
3. 필드 설정:
   • 행 필드: 부서
   • 열 필드: 월
   • 값 필드: 매출액 (SUM)

결과

 

           1월     2월      총합

영업  1000   1500    2500

인사    800   1200    2000

회계    900   1000    1900

 

4. 값 필드 설정 변경:

• 매출액 필드를 우클릭 → 값 필드 설정 → **평균(AVERAGE)**로 변경하면 부서별 월 평균 매출을 볼 수 있습니다.

피벗 테이블 슬라이서 추가 (선택)

• 삽입 탭 → 슬라이서 클릭 → 부서 선택
• 슬라이서를 사용하면 부서별 데이터 필터링이 가능합니다.

---

부분합과 피벗 테이블 비교

기능	부분합	피벗 테이블
데이터 정렬	정렬 필요	정렬 필요 없음
데이터 업데이트	새로 부분합 설정 필요	테이블 업데이트(새로고침) 가능
사용 용도	단순 그룹별 합계,평균 계산	복잡한 데이터 분석 및 요약
적용 대상	동일한 데이터 범위 내 계산	여러 필드와 값 조합 가능
시각화 기능	없음	슬라이서, 피벗 차트 추가 가능
복잡한 분석	어렵다	여러 필드와 조합된 분석 가능

---

정리 및 추가 팁

1. 부분합:
   • 반드시 정렬 후 부분합을 적용해야 합니다.
   • 여러 조건으로 부분합을 적용할 수 있으며, 페이지 나누기 옵션도 시험에 나올 수 있습니다.
   • 시험에서는 여러 번의 부분합 계산이 요구될 수 있으니, 기존 부분합을 덮어쓰지 않도록 주의하세요.
2. 피벗 테이블:
   • 피벗 테이블은 데이터 분석의 핵심 기능이므로 값 필드 설정과 필터 기능을 잘 이해해두세요.
   • 값 필드 설정 변경(SUM, AVERAGE 등)에 익숙해지세요.
   • 데이터가 추가되면 데이터 범위 확장 및 새로고침하는 방법도 알아두세요.
   • 슬라이서와 피벗 차트를 활용해 데이터를 시각적으로 분석하는 문제도 나올 수 있습니다.