스프레드시트의 꽃: 피벗 테이블 만들기 3부 (다이내믹 필터링/조건부서식)
스프레드시트의 꽃: 피벗 테이블 만들기 1부에서는 데이터를 준비하고 피벗 테이블을 만드는 방법을 살펴보았습니다.
스프레드시트의 꽃: 피벗 테이블 만들기 2부에서는 피벗테이블에 행과 열과 값을 더 추가하는 방법을 살펴보았습니다.
3부에서는 다이내믹 필터링과 조건부 서식을 살펴보겠습니다.
다이내믹 필터링
2부에서 만든 피벗 테이블을 기준으로 시작합니다.
만약 담당자별로 값을 바꾸어 보고 싶다면 어떻게 해야할까요?
1부에서 살펴본 필터링 기능을 사용해서 담당자를 선택하고, 제외하고를 반복할 수 있지만 너무 번거롭죠.
셀에서 드롭 목록을 선택하면 그에 맞춰서 피벗 테이블이 바뀌도록 만들면 좋지 않을까요?
먼저 드롭 목록을 만듭니다. 만드는 방법에 대해서는 이전 포스팅에서 다루었으니 생략합니다.
피벗 테이블 위에 드롭 목록을 만들어두었습니다.
이제 이 목록에서 선택하는 값에 맞춰서 변화하는 피벗 테이블을 만들 것입니다.
피벗 테이블에서 필터를 보면 담당자 – 셀이 비어있지 않음으로 선택되어 있습니다.
이번에는 조건별 필터링에서 같음을 선택합니다.
그리고, 담당자 드롭 목록이 있는 M1 과 같다고 입력합니다.
총계가 보기 불편하기에 담당자에서 합계 표시 체크를 지워줍니다.
한 눈에 담당자별로 데이터를 바꾸어 가면서 볼 수 있게 되었습니다.
또는 구글 시트의 새로운 기능인 슬라이서를 사용할 수도 있지만, 슬라이서는 모바일에서는 동작하지 않는다는 점을 고려해야합니다.
또한 슬라이서를 추가하면 데이터의 범위를 열단위가 아니라 셀단위로 정하기 때문에, 만약 행을 추가하는 경우에는 슬라이서에서 수동으로 변경해줘야하는 번거로움도 있습니다.
어떻게 만드는지 살펴볼까요? 좀 더 쉽기는 합니다.
피벗 테이블에 커서를 클릭해둔 상태에서 데이터 > 슬라이서를 클릭하면 자동으로 슬라이서가 생성됩니다.
이제 적용할 열을 선택합니다. 담당자를 선택합니다.
그러면, 필터를 적용할 때와 동일한 내용을 표시하는 슬라이서가 생성됩니다. 사실 필터를 밖으로 꺼내온 것이죠.
이제 선택을 하면 피봇 테이블 값이 변경됩니다. (조건별 필터링을 이미 적용해두었다면 슬라이서의 값이 우선합니다)
슬라이서가 재밌는 점은 동일한 범위로 만든 피벗테이블에 동시에 적용된다는 점입니다.
여러 피벗 테이블을 한 번에 제어할 수 있다는 것이죠. 강력한 기능입니다.
동일한 기능이 데이터스튜디오에도 있는데, 만약 데이터스튜디오를 쓸 정도의 규모가 아니라면, 슬라이서로 대시보드를 구현할 수 있습니다.
조건부 서식
피봇 테이블에 왜 조건부 서식이 필요할까~ 생각하실 수 있는데요.
기본 색상이 회색인 것이 마음에 안들 수도 있고, 특정한 부분에 강조를 하고 싶을 때가 있을 수도 있기 때문입니다.
색을 칠해두면, 필터링이나 값의 변화에 따라 피벗 테이블이 변화할 때 따라갈 수가 없죠. 엉뚱한 데 색이 칠해지게 됩니다.
다음과 같은 목표를 달성해보겠습니다.
|
1 2 3 |
1. 레이블 행과 총계 행에는 파란색 배경에 흰색 폰트로 표시 2. 수량이 100개가 넘는 셀에는 빨간 배경에 흰색 폰트로 표시 3. 공급 총액 평균이 600만원이 넘는 행은 BOLD로 처리 |
1. 레이블 행과 총계 행에는 파란색 배경에 흰색 폰트로 표시
서식 메뉴에서 조건부 서식을 추가합니다.
범위는 피봇 테이블의 범위를 입력하는데, S2:X 식으로 입력하면 알아서 마지막 행까지 변환됩니다.
스타일은 파란 배경에 흰색 폰트로 바꿉니다.
이제 중요한 것이 형식 규칙인데요. 맞춤 수식을 선택하고 다음과 같이 입력합니다.
|
1 |
=$T2 = "담당자" |
의미를 해석하면 이렇습니다.
$T 이기 때문에 T는 고정하고, 2부터 3,4,5 늘려가면서 체크를 해서 값이 담당자이면 선택한 범위에 스타일을 적용할 것.
조건부 서식으로 한 행에 스타일을 적용할 때에는 항상 이 방식이 사용됩니다.
S열을 선택할 수도 있지만, 일자 그룹핑이 바뀔 수 있어서 담당자 열인 T를 사용했습니다.
비슷하게 총계도 적용해보실까요? 동일하게 진행하고, 맞춤 수식만 변경합니다.
참고로 조건부 서식을 편집하는 상태에서 + 다른 규칙 추가를 누르면 복사가 되어서 동일한 범위에 다양한 서식을 적용할 때 편합니다.
|
1 |
=$S2 = "총계" |
2. 수량이 100개가 넘는 셀에는 빨간 배경에 흰색 폰트로 표시
이번에는 수량에만 적용하기 때문에 범위를 U2:U 로 설정합니다.
그리고 기본 옵션에 있는 초과를 선택하고 100을 써주고, 스타일을 적용하면 완료~
3. 공급 총액 평균이 600만원이 넘는 행은 BOLD로 처리
다시 행 전체에 서식을 적용하기에 첫번째 했던 서식과 유사하게 맞춤 수식을 적용합니다.
이렇게 해서 조건부 서식 적용도 완료했습니다.
오늘 살펴본 내용을 샘플로 확인해보세요.
샘플 보기간단하지만 실무에서 필수인 피벗테이블, 생각보다 쉽죠?
알면 30초이지만, 모르면 동일한 표를 만들기 위해서 3시간을 사용해야할지 모릅니다!
연재기사를 처음부터 보고 싶으시다면~
