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

엑셀 조건부서식을 응용하여 버튼 클릭시 원하는 부분이 실시간으로 강조되는 표 제작방법을 단계별로 살펴봅니다.

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

엑셀 실시간으로 강조되는 표 만들기 | 실무기초 9강

실습/완성파일E-Book

관련 기초내용 포스트 | by 오빠두엑셀

엑셀 COUNTIFS 함수 썸네일_크기
엑셀 COUNTIFS 함수 사용법 및 실전예제 총정리 :: 통계함수
엑셀 COUNTIFS 함수 목차 바로가기 함수 요약 엑셀 COUNTIFS 함수 구문 인수 알아보기 COUNTIFS 함수 상세설명 COUNTIFS 함수 사용법 간단예제 ...
엑셀 COUNTIF 함수 예제 PNG
엑셀 COUNTIF 함수 사용법 및 실전예제 총정리 :: 통계함수
자주묻는 질문 COUNTIF 함수로 옳지않은 결과값이 계산됩니다 빈칸이 아닌 셀의 개수를 세고싶어요 다른 통합문서를 참조하면 #VALUE 오류가 반환됩니다. 설명 엑셀 ...

1. 자료 표현을 위한 피벗테이블 / 피벗차트 기본 틀 만들기

이번 강의에서는 온라인 의류 쇼핑몰에 옷을 납품하는 의류 공장의 2018년도 1분기 매출현황을 예제로 진행합니다.

따라서 아래 2개의 피벗테이블 및 피벗차트로 기본틀을 제작합니다.

  • 모든 지역 매출 요약표
  • 선택지역 매장별 매출 상세현황표
  • 선택지역 월별 매출현황차트

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

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

  • 행 필드 : 시도
  • 열 필드 : 대분류
  • 값 필드 : 금액[합계]
  • 테이블 명 : pvtbl요약

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

단축키 Alt – J – Y – P – T (피벗테이블을 테이블형식으로 표시) 및 Alt – J – Y – T – D (피벗테이블 부분합 표시 안함) 으로 피벗테이블의 표시형식을 변경합니다.

1 피벗 완성
전지역 매출현황 요약표의 기본틀을 완성합니다.

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

1b. 표시형식 변경
금액의 표시형식을 변경합니다.

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

1c. 디자인 변경
피벗테이블의 스타일을 회색으로 변경합니다.

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

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

  • 행 필드 : 거래처, 매출일
  • 열 필드 : 대분류
  • 값 필드 : 금액[합계]
  • 테이블 명 : pvtbl세부

피벗테이블을 선택한 후, [피벗테이블 분석] 탭으로 이동합니다. 이후 리본 좌측의 ‘피벗테이블 이름’을 ‘pvtvl세부’로 변경합니다.

이전과 동일하게 단축키 Alt – J – Y – P – T (피벗테이블을 테이블형식으로 표시) 및 Alt – J – Y – T – D (피벗테이블 부분합 표시 안함) 으로 피벗테이블의 표시형식을 변경합니다.

2a. 피벗테이블 1차 완성
선택한 지역의 매장별 세부매출현황 표의 기본틀을 완성합니다.

매출일 필드를 ‘월 단위’로 그룹화 합니다. 매출일 필드를 우클릭 한 뒤, [그룹]을 클릭합니다. 새로 나타난 창에서 ‘월’만 선택되도록 변경한 후, 확인을 눌러 창을 닫습니다.

2b. 월단위 그룹
매출일을 ‘월’ 단위로 그룹화합니다.

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

2c. 거래처 부분합 표시
‘거래처’ 부분합을 추가하여 표를 더욱 보기 쉽게 만듭니다.

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

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

2d. 요약열 채우기
피벗테이블의 요약범위를 선택한 후, 요약범위의 채우기색상을 회색으로 변경합니다.

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

2e. 테두리 넣기
‘거래처’ 및 ‘매출일’ 범위의 채우기색상과 테두리를 변경합니다.

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

2f. 지역 매장별 상세현황 완성
선택 지역의 매장별 매출 상세현황 표가 완성되었습니다.

C. 선택지역 월별 매출현황 차트 만들기

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

  • 행 필드 : 거래처, 매출일
  • 값 필드 : 금액[합계]

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

3a-1. 피벗테이블 추가
차트를 만들기 위한 피벗테이블을 생성합니다.

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

3a-2. 묶은 세로막대 차트 추가
피벗테이블을 선택한 후, ‘묶은 세로 막대형’ 차트를 추가합니다.

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

3c. 차트 모든 필드 단추 숨기기
피벗차트에 표시된 모든 단추를 숨김처리합니다.

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

3e. 차트 모든항목 제거 및 데이터레이블 추가
차트의 제목, 범례, 눈금선, 세로축을 삭제한 뒤 데이터레이블을 추가합니다.

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

3e-a. 데이터 계열 서식 간격 너비 조절
차트 막대의 채우기색상 및 간격 너비를 조절합니다.

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

3f. 슬라이서 추가
피벗테이블을 선택한 후, ‘시도’ 항목의 슬라이서를 추가합니다.

삽입된 슬라이서를 선택한 후, 상단의 [슬라이서] 탭으로 이동합니다. 기존에 만들어진 슬라이서 디자인 (검은색/회색 디자인)을 선택합니다.

3g. 슬라이서 디자인 변경
슬라이서의 디자인을 변경합니다.

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

3h. 보고서 연결
pvtbl세부를 슬라이서에 연동합니다.

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

3i. 틀 완성
슬라이서를 클릭하면 차트와 피벗테이블이 동시에 업데이트됩니다.

2. 실시간으로 엑셀 표를 강조하는 방법

슬라이서로 선택한 지역을 COUNTIF 함수와 조건부서식을 응용하여 실시간으로 표에 강조합니다.

COUNTIF 함수:: 통계함수 [상세설명 및 예제 보러가기]
설명 : 선택된 범위에서 조건을 만족하는 셀의 개수를 셉니다.
구문 : = COUNTIF ( 조건범위, 조건 )

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

  • 행 필드 : 시도
  • 테이블 명 : pvtbl시도

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

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

4 시도 피벗테이블 추가
새로운 피벗테이블을 추가한 뒤, 이전과 동일하게 슬라이서에 연동시켜줍니다.

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

=COUNTIF(S:S,K3)
4c COUNTIF 함수 입력
COUNTIF 함수를 사용한 수식을 선택한 범위에 입력합니다.

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

4d 선택된 시도 countif 숫자 변경
슬라이서로 선택된 지역이 피벗테이블에 업데이트 되면서, 해당 지역의 행만 값이 1로 변하게됩니다.

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

4e 조건부서식 추가
조건부서식에서 새규칙을 추가합니다.

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

=($R3>0)
4f 조건부서식 수식 입력
조건부서식을 적용할 수식을 입력합니다.

여기서 한가지 주의사항이 있습니다. 조건부서식의 수식을 입력하면서 셀을 참조할 시, 엑셀은 기본값으로 ‘절대참조’로 셀이 입력됩니다. 따라서 R3셀을 선택하게 될 경우 수식에는 ‘$R$3’ 으로 입력됩니다.

우리가 참조할 셀은 ‘시도COUNT’범위의 R3, R4, R5… 순으로 R만 고정을 하여 참조하게 됩니다. 따라서 ‘$R$3’에서 3 앞의 달러표시($)는 제거한 뒤 ‘$R3’으로 변경하여 수식을 완성합니다.

4g 절대참조로 입력됨
조건부서식으로 셀참조시 ‘절대참조’로 입력되는것에 주의합니다.

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

4h 조건부서식 서식 변경
조건부서식을 설정합니다.

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

4i 버튼 클릭하면 표 강조됨
슬라이서 버튼을 클릭하면 해당 지역만 표에서 강조되는 것을 볼 수 있습니다.

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

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

  1. 매출이 없는 월은 차트에 표시되지 않아, 차트의 매장별 간격이 일정하지 않음
  2. 피벗테이블이 갱신될 때마다 열너비가 자동으로 변하면서 표 넓이가 일정하지 않게 됨.

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

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

5a. 필드설정 이동
‘매출일’열을 우클릭 한 뒤, [필드설정]으로 이동합니다.
이후 [레이아웃 및 인쇄]에서 [데이터가 없는 항목 표시]에 체크한 뒤 확인을 눌러 창을 닫습니다.

5b 데이터가 없는 항목 표시
레이아웃 및 인쇄에서 ‘데이터가 없는 항목 표시’에 체크한 뒤 창을 닫습니다.

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

5c. 매출일 1분기 월만 선택
매출일 항목에서 1월,2월,3월만 선택하여 필터링합니다.

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

5d 비어있는 월도 모두 표시됨
매출이 없는 열도 차트에 모두 표시되는 것을 확인할 수 있습니다.

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

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

5e 피벗테이블 옵션
매장별 상세매출현황 피벗테이블을 우클릭한 뒤, [피벗테이블 옵션]을 선택합니다.
[레이아웃 및 서식] 에서 ‘업데이트 시 자동 열맞춤’을 체크해제 한 뒤 확인을 눌러 창을 닫아줍니다.

이후 피벗테이블 범위의 열 넓이를 변경한 뒤, 슬라이서 버튼을 클릭해도 열너비가 일정하게 고정됩니다.

5f 업데이트시 자동 열맞춤 해제
피벗테이블 설정에서 ‘업데이트 시 열 자동맞춤’을 체크해제합니다.

4. 2단계로 나눠 강조되는 표 만들기

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

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

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

6a. 임시 머릿글 추가
임시로 사용할 열을 추가한 뒤, 머릿글을 넣어줍니다.

IF 함수를 사용하여 위/아래에 있는 데이터가 동일할 경우 빈칸을 출력하고, 그렇지 않을 경우 아래에 있는 데이터를 출력하도록 함수를 입력합니다.

시트의 C3셀을 선택한 후 아래 수식을 입력한 뒤, 복사합니다. 그리고 나머지 다른 부분에 ‘수식’만 붙여넣기 합니다. (단축키 Ctrl + Alt + V)

=IF(B2=B3,””,B3)
6b. 반복된 데이터 제거
수식을 입력하면 반복된 데이터는 빈칸으로 정리됩니다.

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

시트 우측에 위치한 ‘대분류/소분류’가 나열된 데이터 범위를 선택한 후, K2셀에 피벗테이블을 생성합니다.

  • 행 필드 : 대분류, 소분류

이후 단축키 Alt – J – Y – P – T (피벗테이블을 테이블형식으로 표시) 및 Alt – J – Y – T – D (피벗테이블 부분합 표시 안함) 으로 피벗테이블의 표시형식을 변경합니다.

6c. 피벗테이블 추가
대분류/소분류를 표시할 피벗테이블을 생성합니다.

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

6d. 슬라이서 생성
대분류/소분류 항목의 슬라이서를 생성한 뒤, 보기 편안한 위치로 이동합니다.

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

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

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

6e. 카운트 임시 열 추가
해당 항목이 피벗테이블에 선택되었는지 개수를 세기위한 계산열을 추가합니다.

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

=COUNTIF(M:M,D3) ‘// 대분류COUNT 범위
=COUNTIF(N:N,F3) ‘// 소분류COUNT 범위

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

6f. countif 함수 입력
선택된 대분류/소분류 행의 값만 1로 변하는 것을 확인할 수 있습니다.

D. 조건부서식 적용하기

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

6g. 업체명 배경색 채우기
업체명 범위의 채우기 색상을 옅은 노란색으로 변경합니다.

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

=($K3>0)

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

6h. 조건부서식 적용 1차
‘대분류’ 항목의 조건부서식 설정을 완료합니다.

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

=($L3>0)

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

6i. 다중조건 강조 표 완성
이제 버튼을 클릭하면 실시간으로 각 조건에 따라 표가 강조되는 것을 확인할 수 있습니다.
5 10 votes
별점주기
현재 페이지 댓글알림 신청
알림 설정
guest
15 Comments
Inline Feedbacks
View all comments
호야
별점주기 :
     

많이 배우고 갑니다.

밍키색시
별점주기 :
     

간단한 것들만 하다가 이런 자료를 보니 신세계네요!! 정말 감사합니다. 강의 보면서 많이 배우고 있어요.

크리슈
별점주기 :
     

이런 방법이 있었네요.. 감사합니다!

에이사 버터필드
별점주기 :
     

저도 신기해서 공부를 해야하는데 진도가 잘 안 빠지네용. 그래도 고맙지요.

에이사 버터필드
별점주기 :
     

유투브 강의 아래로 설명 붙이신 자료를 보면서 실습하니 훨씬 더 쉽습니다. 오빠두 선생님 감사합니다.

날으는공

18분 영상을 1시간 이상 보네요 보고 또 보고 하나하나의 말씀을 청취합니다.

에이사 버터필드

대단 하십니다. 저도 인제 기초 7강인가 8강까지 했네요.

민찬이아빠
별점주기 :
     

이건 제품 매출보고용으로 딱이네요... 와...

에이사 버터필드
별점주기 :
     

오늘 실무기초 9강 완료!!! 엑셀이 대단하네요, 오빠두 선생님 덕에 엑셀이 좋아지고 있어요

엑린이
별점주기 :
     

감사합니다

호크777
별점주기 :
     

저도 익숙해질때까지 보고 하고 있어요..
여전히 어려운 엑셀입니다. 하지만 능력 업이 된다고 생각하니 너무나 좋습니다.
오빠두 엑셀 강사님에게 너무도 감사드립니다.

하나 여쭈어 볼게 있는데요

엑셀 2013 사용자이고.

슬라이서로 클릭하면 표에서 실시간으로 강조되지 않고 저장해야만 색깔이 변하는데 이 부분은 어떤 오류인가요?

김일권

감사합니다.
이제 작동하네요.
운영자님 말씀하신대로 수동을 자동으로 바꾸니 정상 작동합니다.

부드럽럽
별점주기 :
     

선생님 좋은 강의 감사합니다. 추가 작업을 하다가 막혀서 질문드립니다.
새로운 워크시트에 새로운 피벗테이블을 만들고 월 그룹을 연,분기를 추가하니 기존의 피벗테이블까지 변경이 됩니다. 새로운 피벗테이블을 기존과 별도로 연,분기 표시를 하고 싶은데 방법을 모르겠습니다. 조언 부탁드려요^^

15
0
여러분의 생각을 댓글로 남겨주세요.x