엑셀 버튼 클릭으로 실시간 강조되는 표 만들기 | 조건부서식 응용

엑셀 버튼 클릭으로 실시간 강조되는 표 만들기 | 조건부서식 응용

엑셀 실시간 강조되는 표 목차 바로가기

영상강의

---

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

---

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

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’으로 변경하여 수식을 완성합니다.

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

이제 슬라이서 버튼을 클릭하면 표에서 선택된 지역이 강조되는 것을 볼 수 있습니다.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

시트의 오른쪽에 적어드린 ‘대분류/소분류’가 나열된 표를 선택한 후, K2셀에 피벗테이블을 생성합니다.

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

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

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

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

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

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

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

D. 조건부서식 적용하기

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

E3:E34 범위를 선택한 후, [홈] – [조건부서식] – [새규칙] – ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택한 뒤 아래 수식을 입력합니다. 이전과 마찬가지로 조건부서식으로 셀을 참조할 경우 ‘절대참조’로 수식에 입력되는 것에 주의합니다.

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

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

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