[Excel 365] 피벗 테이블로 데이터 원하는 형태로 정리 쉽게 하기
엑셀 피벗 테이블 사용해보셨나요? 한번도 사용해보지 않은 사람은 많지만, 한번만 사용해본 사람은 없다는 그 피벗 테이블! 그만큼 유용하다는 뜻이겠죠? 피벗 테이블만 잘 활용하셔도 엑셀을 아주 능숙하게 다루는 것처럼 평가 받으실 수 있습니다.
2022년 시즌 타율 기준으로 top 20명 선수들의 기록입니다(네이버 스포츠). 이 기록을 오늘의 실습 데이터로 삼겠습니다.
문제1. 만약 소속팀별 선수 숫자를 구하려면?
해결책1. 각 팀 별로 한명씩 세어서 정리한다(노가다).
해결책2. unique, countif 함수를 사용한다.
해결책3. 피벗테이블을 사용한다.
1번은 시간과 건강한 신체가 있는 사람이라면 누구나 할 수 있는 방법이니 패스하겠습니다. 2번 방법으로 먼저 해결해보겠습니다.
해결책2: unique, countif 활용
먼저 빈 셀에 unique 함수를 사용하여 소속팀의 고유값 리스트를 구합니다.
=UNIQUE(E4:E23)
그러면 다음과 같이 소속팀의 고유값들을 얻을 수 있습니다.
이제 COUNTIF 함수를 사용하여 각 팀당 몇 명의 선수가 표에 있는지 세도록 하겠습니다.
=COUNTIF($E$4:$E$23, I4)
키움 선수의 수를 잘 세었습니다.
나머지 팀 선수들의 수도 세기 위해 2가 들어가 있는 셀을 클릭한 후 아래로 드래그하여 수식을 자동 적용합니다.
원하는 대로 소속팀별 선수 수를 잘 구했습니다.
해결책3: 피벗 테이블 활용
이번에는 피벗 테이블을 활용하여 동일한 문제를 해결해보겠습니다. 먼저 기록이 있는 데이터 영역을 드래그한 후 [삽입] 탭에서 [피벗 테이블] - [테이블/범위에서]를 클릭합니다.
그 다음에 뜨는 창에서 [기존 워크시트] 라디오 버튼을 클릭한 후에 피벗 테이블을 넣어줄 시작 셀을 클릭한 후 [확인] 버튼을 클릭합니다.
이제 우측에 다음과 같이 피벗 테이블 필드라는 것이 보여질 텐데, 소속을 행 영역으로 끌어 놓고, 선수명은 값 영역으로 끌어 놓으면 아주 쉽게 각 팀별 선수 수가 카운트되는 것을 확인하실 수 있습니다. UNIQUE, COUNTIF 함수를 사용했던 해결책2보다 훨씬 간단하죠?
문제2. 만약 포지션별 타율 평균, 홈런 평균을 구하려면?
해결책1. 포지션 별로 데이터를 정리한 후 타율 평균과 홈런 평균을 계산한다(노가다).
해결책2. unique, averageif 함수를 사용한다.
해결책3. 피벗테이블을 사용한다.
역시나 해결책1은 다 할 수 있는 방법이기 때문에 패스하고, 해결책2부터 살펴보겠습니다.
해결책2: unique, averageif 활용
먼저 UNIQUE 함수를 사용하여 어떤 포지션들이 있는지 고유값을 구합니다.
이제 AVERAGEIF 함수를 사용하여 포지션별 평균 타율을 구합니다.
=AVERAGEIF(찾을범위, 찾을값, 평균을구할값의범위)
0.3135가 들어가 있는 셀을 클릭한 후 아래로 드래그하면 내야수의 평균 타율도 바로 구할 수 있습니다.
이제 비슷한 방식으로 포지션별 평균 홈런 개수도 구해보겠습니다.
위와 같이 수식을 입력한 후 엔터를 누르면 외야수의 평균 홈런수가 구해지고, 아래로 드래그하면 마찬가지로 내야수의 평균 홈런수도 구해집니다.
타율 top20 에 속한 선수들 중에서는 외야수가 내야수에 비해 홈런을 더 잘 치는 군요.
해결책3: 피벗 테이블 활용
이제 마법의 피벗 테이블을 활용해서 조금 더 빠르게 문제를 해결해보겠습니다. 행 영역에 이번에는 포지션을 넣어줘야 합니다. 그리고 값 영역에 먼저 타율을 넣어주겠습니다.
보시는 것처럼 타율의 합계가 구해졌을 것입니다. 저희가 알고 싶은 것은 포지션별 타율 평균입니다. 당황하지 말고, [합계 : 타율]을 클릭한 후 가장 아래 있는 [값 필드 설정] 메뉴를 클릭합니다.
그러면 합계 대신에 평균을 구할 수 있도록 설정해줄 수 있습니다.
[확인] 버튼을 클릭하면, 평균 타율이 잘 구해진 것을 확인할 수 있습니다.
마찬가지로 평균 홈런도 추가해줄 수 있습니다. 홈런 필드를 값 영역에 넣어준 다음에 [값 필드 설정]에 들어가셔서 평균으로 바꿔주시면 됩니다.
정리하며
오늘 확인하신 것처럼 피벗 테이블을 잘 활용하시면 데이터를 훨씬 더 수월하게 원하는 형태로 정리할 수 있습니다. 아직 피벗 테이블을 한 번도 사용해보지 않으신 분들은 한 번 실무에 활용해보시기 바랍니다.