클릭하면 강조되는 표

실습 가이드

이번 강의에서는 온라인 의류 쇼핑몰에 옷을 납품하는 의류 업체의 2018년도 1분기 매출현황 자료를 예제로 살펴봅니다. 자료 분석을 위해 다음과 같이 두 개의 피벗테이블과 한 개의 피벗차트를 제작합니다.

A. 모든 지역 매출 요약표 만들기

데이터 범위를 선택한 후, 기존 '쇼핑몰매출' 워크시트 K1 셀에 다음과 같이 피벗테이블을 생성합니다.

생성된 피벗테이블을 선택한 뒤 [피벗테이블 분석] 탭으로 이동합니다. 리본 좌측에서 '피벗테이블 이름'을 'pvtvl요약'으로 변경한 후, 단축키를 사용하여 피벗테이블 레이아웃을 변경합니다.

표를 한눈에 보기 쉽도록 금액에 천단위 구분기호를 표시합니다. 값 필드의 '금액'을 클릭한 뒤 [값 필드설정]으로 이동합니다. 표시형식에서 '사용자 지정 서식'을 #,##0 으로 변경합니다.

피벗테이블을 선택한 후 상단의 [디자인] 탭에서 피벗테이블 스타일을 '회색'으로 변경합니다.

B. 선택지역 매장별 매출 상세현황 표 만들기

데이터 범위를 선택한 후, 기존 '쇼핑몰매출' 워크시트 K19 셀에 피벗테이블을 생성합니다.

피벗테이블을 선택한 뒤 [피벗테이블 분석] 탭으로 이동합니다. 리본 좌측의 '피벗테이블 이름'을 'pvtvl세부'로 변경한 후, 이전과 동일한 단축키로 피벗테이블 레이아웃을 변경합니다.

매출일 필드를 '월 단위'로 그룹화합니다. 매출일 필드를 우클릭하여 [그룹]으로 이동합니다. 새로 나타난 창에서 '월'만 선택한 뒤 확인을 눌러 창을 닫습니다.

이전과 동일한 방법으로 금액에 천단위 구분기호를 표시합니다. 값 필드의 '금액'을 클릭한 뒤 [값 필드설정]으로 이동합니다. 표시형식에서 '사용자 지정 서식'을 #,##0으로 변경합니다.
거래처 필드에 부분합을 표시합니다. 거래처 범위를 우클릭한 후 [거래처 부분합]을 클릭합니다.

위의 피벗테이블과 동일하게 피벗테이블 디자인을 변경합니다. 피벗테이블을 선택한 후 상단의 [디자인] 탭에서 피벗테이블 스타일을 '회색'으로 변경합니다.

이후 피벗테이블의 [요약] 범위를 선택합니다. 마우스 커서가 오른쪽 화살표(→) 모양이 되도록 이동한 뒤 전체 요약범위를 선택합니다. [홈] 탭에서 채우기 색상을 '회색'으로 변경합니다.

거래처 범위를 선택합니다. 이번에도 동일하게 마우스 커서가 아래 화살표(↓) 모양이 되도록 이동한 뒤 전체 거래처 범위를 선택합니다. [홈] 탭에서 채우기 색상을 '회색'으로 변경합니다. 이어서 단축키 Ctrl + 1 로 '셀 서식'을 열어 오른쪽에 진한 회색 테두리를 추가합니다.

매출일 범위도 동일한 방법으로 선택한 후 오른쪽에 진한 회색 테두리를 추가하여 선택지역의 매장별 상세현황 표를 마무리합니다.

데이터 범위를 선택한 후, 기존 '쇼핑몰매출' 워크시트 V1 셀에 피벗테이블을 생성합니다.

금액에 천단위 구분기호를 표시합니다. 값 필드의 '금액'을 클릭한 뒤 [값 필드설정]으로 이동합니다. 표시형식에서 '사용자 지정 서식'을 #,##0 으로 변경합니다.

이어서 피벗차트를 추가합니다. 피벗테이블을 선택한 뒤 단축키 Alt – N – R 로 '묶은 세로막대형 차트'를 삽입합니다.

차트 좌측 상단의 버튼을 우클릭한 후 [차트에서 모든 필드단추 숨기기]를 선택하여 피벗차트 위의 모든 단추를 숨김 처리합니다.

이후 차트 제목, 범례, 눈금선, 세로축을 모두 삭제합니다. 차트를 클릭한 뒤 더하기(+) 버튼을 눌러 '데이터 레이블'을 추가합니다.

차트 막대를 우클릭한 후 채우기 색상을 '진한 회색'으로 변경합니다. 또한 [데이터 계열 서식]을 선택하여 차트 막대의 [간격너비]를 120%로 조절합니다.

피벗테이블을 선택한 후 상단의 [삽입] 탭으로 이동한 뒤 [슬라이서]를 선택합니다. '시도'를 선택하여 슬라이서를 추가합니다.

삽입된 슬라이서를 선택한 후 상단의 [슬라이서] 탭으로 이동합니다. 기본으로 제공되는 슬라이서 디자인(검은색/회색 디자인)을 선택합니다.

슬라이서를 매장별 세부현황 피벗테이블에도 연동합니다. 슬라이서를 우클릭한 후 [보고서 연결]을 선택합니다. 방금 만든 'pvtbl세부'에 체크한 뒤 확인 버튼을 눌러 창을 닫습니다.

삽입한 슬라이서와 피벗차트를 보기 좋은 위치로 이동한 뒤 슬라이서 버튼을 클릭합니다. 슬라이서 버튼을 클릭하면 차트와 매장별 세부현황 피벗테이블이 동시에 변경되는 것을 확인할 수 있습니다.

이제 COUNTIF 함수와 조건부서식을 응용하여 슬라이서 버튼을 클릭하면 선택된 지역이 실시간으로 표에 강조되도록 만들어 보겠습니다.

데이터 범위를 선택한 후, 기존 '쇼핑몰매출' 워크시트 S1 셀에 피벗테이블을 생성합니다.

생성된 피벗테이블을 선택한 후 [피벗테이블 분석] 탭으로 이동합니다. 리본 좌측의 '피벗테이블 이름'을 'pvtvl시도'로 변경합니다.

이전에 추가한 슬라이서를 우클릭한 후 [보고서 연결]을 클릭합니다. 피벗테이블 목록에서 'pvtbl시도'를 선택하여 슬라이서와 연결합니다. 이후 슬라이서 버튼을 클릭하면 'pvtbl시도' 피벗테이블도 동시에 값이 변경되는 것을 확인할 수 있습니다.

R2 셀에 '시도Count'라고 머릿글을 입력합니다. 이후 R3 셀에 다음과 같이 COUNTIF 함수를 입력한 뒤 아래로 자동채우기합니다.

COUNTIF 함수를 통해 피벗테이블에 선택된 지역의 개수를 셉니다. 따라서 슬라이서로 선택한 지역의 행에만 함수의 결과값으로 1이 출력됩니다. 이 값을 참조하여 조건부서식으로 해당 행을 강조합니다.

K3:Q7 까지의 범위를 선택한 뒤 [홈] 탭의 [조건부서식]에서 [새 규칙]을 추가합니다.

'새 서식 규칙' 창이 나타나면 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다. 이후 다음 수식을 빈 칸에 입력합니다.

여기서 한 가지 주의할 점이 있습니다. 조건부서식에서 수식을 입력하며 셀을 참조하면 엑셀이 자동으로 '절대참조'로 입력합니다. 따라서 R3 셀을 선택하면 수식에는 '$R$3' 으로 입력됩니다.

이번 예제에서 참조할 셀은 '시도COUNT' 범위의 R3, R4, R5… 순으로 R열만 고정해야 하므로, '$R$3'에서 3 앞의 달러 표시($)는 제거하여 '$R3'으로 변경한 뒤 수식을 완성합니다.

이후 [서식] 버튼을 클릭하여 조건부서식 서식을 설정합니다. 채우기 색상은 진한 회색으로, 글꼴 색상은 흰색에 굵은 글꼴로 변경합니다. [확인] 버튼을 눌러 조건부서식을 완성합니다.

이제 슬라이서 버튼을 클릭하면 표에서 선택된 지역이 실시간으로 강조되는 것을 확인할 수 있습니다.

사용자 편의성을 위한 문제 해결

완성된 양식에는 두 가지 작은 문제가 남아 있습니다.

A. 매출이 없는 월도 포함하여 차트에 표시하기

차트가 연결된 피벗테이블로 이동합니다. '매출일' 열을 우클릭한 뒤 [필드 설정]으로 이동합니다.

이후 [레이아웃 및 인쇄]에서 [데이터가 없는 항목 표시]에 체크한 뒤 확인을 눌러 창을 닫습니다.

[데이터가 없는 항목 표시]에 체크하면 1월부터 12월까지 모든 월이 표시됩니다. 우리가 보고자 하는 기간은 1분기(1월~3월) 입니다. 따라서 매출일 필터에서 1월, 2월, 3월만 선택하여 매출일을 필터링합니다.

이제 매출이 비어 있는 월도 모두 차트에 표시됩니다.

B. 피벗테이블 자동 열 맞춤 설정 해제하기

매장별 상세 매출현황 피벗테이블(pvtbl세부)로 이동합니다. 피벗테이블을 우클릭한 후 [피벗테이블 옵션]으로 이동합니다.

[레이아웃 및 서식]에서 '업데이트 시 자동 열 맞춤'을 체크 해제한 뒤 확인을 눌러 창을 닫습니다. 이후 피벗테이블 범위의 열 너비를 변경하면, 슬라이서 버튼을 클릭해도 열 너비가 일정하게 고정됩니다.

이번에는 '회의용자료' 시트로 이동하겠습니다. 회의용자료 시트에는 Raw Data(원본자료) 형태로 데이터가 입력되어 있습니다. 따라서 중복된 데이터가 반복적으로 입력되어 있는데, 이를 그대로 활용하면 다른 사람이 보기에 좋지 않으므로 원본자료는 그대로 유지한 채 표를 가공한 뒤 조건부서식을 적용하겠습니다.

A. 임시로 사용할 열 추가하기

각 업체명/대분류 옆에 임시 열을 추가합니다. (단축키: Ctrl + Shift + +) 이후 임시 열에 머릿글을 추가합니다. 이번 강의에서는 각각 [@]업체명, [@]대분류로 머릿글을 입력하였습니다.

IF 함수를 사용하여 위에 있는 데이터와 동일할 경우 빈 칸을 출력하고, 그렇지 않을 경우 아래에 있는 데이터를 출력하도록 함수를 입력합니다. 예제 파일의 C2 셀에 다음 수식을 입력한 뒤 아래로 자동채우기합니다.

수식을 복사한 뒤 D3 셀에 붙여넣고, 동일하게 아래로 자동채우기합니다.

B. 피벗테이블 및 슬라이서 생성하기

시트 오른쪽에 마련해 둔 '대분류/소분류'가 나열된 표를 선택한 후 K2 셀에 피벗테이블을 생성합니다.

이후 단축키를 사용하여 피벗테이블 레이아웃을 변경합니다.

피벗테이블을 선택한 뒤 상단의 [삽입] – [슬라이서]를 클릭하여 '대분류', '소분류' 슬라이서를 생성합니다. 두 슬라이서를 시트 좌측의 보기 좋은 위치로 이동시킨 뒤 슬라이서 디자인을 변경합니다.

C. 선택된 값을 참조하기 위해 COUNTIF 함수 수식 입력하기

이전과 동일한 방법으로 특정 행의 항목이 피벗테이블에 존재하는지 개수를 세기 위한 계산 열을 추가합니다. [비고] 열 우측에 두 개의 열을 추가합니다. (단축키: Ctrl + Shift + +)

이후 추가된 열의 머릿글을 '대분류COUNT', '소분류COUNT'로 입력합니다.

각 대분류COUNT의 첫 번째 셀과 소분류COUNT의 첫 번째 셀에 다음 수식을 입력한 뒤 아래로 자동채우기합니다.

수식을 입력한 뒤 슬라이서 버튼을 클릭하면, 선택된 행의 COUNT 값만 1로 변경되는 것을 확인할 수 있습니다.

D. 조건부서식 적용하기

시트에 기존에 있던 '업체명(B열)'과 '대분류(D열)'을 숨깁니다. 이후 C3:C34 범위를 선택하고 채우기 색상을 옅은 노란색으로 변경합니다.

E3:E34 범위를 선택한 후 [홈] – [조건부서식] – [새 규칙] – '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 뒤 다음 수식을 입력합니다. 앞서 설명한 것처럼 조건부서식에서 셀을 참조할 때 '절대참조'로 입력되는 점에 주의합니다.

이후 [서식] 버튼을 클릭하여 채우기 색상을 '옅은 노란색'으로 선택한 뒤 확인을 눌러 대분류의 조건부서식을 완료합니다.

F3:J34 범위를 선택한 후, 이전과 동일하게 [홈] – [조건부서식] – [새 규칙] – '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 뒤 다음 수식을 입력합니다.

이후 [서식] 버튼을 클릭하여 채우기 색상을 '옅은 노란색'으로 선택한 뒤 확인을 눌러 소분류의 조건부서식을 완료하면, 다중 조건으로 실시간 강조되는 표 만들기가 완성됩니다.