엑셀에서 데이터를 정리하거나 분석할 때
가장 손쉽게 사용하는 피벗테이블에 대해 설명을 드리고자 합니다.
보통 실무에서는 많은 양의 데이터를 요약하고
필요한 요소별 총계나 평균 등의 집계 정보를 만들 때 많이 사용합니다.
예를 들어, 피벗테이블을 통해서
매출 데이터에서 지역별, 제품별, 시기별 총 매출액 등을 보여줄 수 있습니다.
특히 피벗테이블을 사용하면
특정 기준에 따라 데이터를 필터링하고 연산을 수행할 수 있습니다.
예를 들어, 날짜, 지역 등 다양한 요소를 기준으로 필터링할 수 있습니다.
또한 피벗테이블은 데이터를 시각적으로 표현하기 위한 다양한 그래프를 제공합니다.
예를 들어, 막대 그래프, 선 그래프, 파이 차트 등의 다양한 그래프를 제공하여 데이터를 쉽게 이해할 수 있도록 도와줍니다.
하지만 보통 피벗테이블을 시각화에는 많이 사용하지 않고 대부분 데이터 정리를 하는데 사용을 하게 됩니다.
그래프 등 시각화하는 경우 디테일한 설정을 해야하는데 아무래도 피벗테이블 할 수 있는 설정에서는 한계가 있기 때문입니다.
그래서 일차적으로 피벗테이블을 통해서 데이터를 요약 및 분석을 하고 해당 데이터를 가지고 그래프 등 시각화를 하는게 일반적입니다.
피벗테이블은 매우 직관적이고 사용하기 편리하지만 데이터가 추가, 삭제 등 변경이 될 때에는 새로고침을 별도로 진행을 해야하는 번거로움이 있습니다.
또한 피벗테이블을 통해 나온 데이터를 추가적으로(2차) 가공해야 하는 경우 이를 진행하기에 어렵기 때문에
피벗테이블 대신 SUMIF 또는 AVERAGEIF 함수 활용해서 진행하는 경우가 많이 있습니다.
이번 포스팅에서는 피벗테이블 기능에 대해 간단하게 소개를 하고 이를 엑셀함수를 사용해서
대체하는 방법을 예시를 통해 안내를 드리도록 하겠습니다.
#피벗테이블사용전체크사항
이번에도 코로나확진자 데이터를 활용해서 설명을 드리도록 하겠습니다.
* 코로나확진자 데이터 활용(https://ncov.kdca.go.kr/)
위와 같이 코로나확진자수가 일자별로 있습니다.
해당 데이터를 활용해서
년도별, 월별로 코로나확진자의 총합계와
년도별, 월별 일평균 확진자수를 알고 싶다고 가정해보겠습니다.
일반적으로 데이터는 수준(단계)가 일정해야합니다.
하지만 해당 데이터의 경우
6행에 “누적(명)” 데이터가 추가로 있습니다.
그래서 A열의 데이터에서 일자별 데이터와 수준이 일치하지 않아서 데이터를 분석하는데 약간의 어려움이 있습니다.
그래서 6행을 삭제후 피벗을 돌리는게 용이하겠지만
개인적으로 원본 데이터는 최대한 유지하는 것을 좋아하기 때문에 데이터를 추가하는 방향으로 하겠습니다.
목표가 년도별, 월별 확진자수를 확인하는 것으로
년도와 월별 데이터를 추가하도록 하겠습니다.
해당 데이터가 “날짜”데이터 이므로
year 함수, month 함수를 사용하면 됩니다.
* 일자데이터 1개를 임의로 선택후에 마우스 우측클릭후 “셀서식”을 선택하면 표시형식을 확인할 수 있습니다.
* 만약 서식이 날짜가 아닌 일반이라면 mid 함수를 사용하면 됩니다.
* 해당 함수 설명은 추후에 포스팅하도록 하겠습니다.
#피벗테이블삽입하기
피벗테이블을 삽입할 때는
사전에 데이터를 선택한 후에 삽입을 진행하면 편리합니다.
B5셀을 선택한 후 키보드로
Ctrl + Shift + 방향키(→)
Ctrl + Shift + 방향키(↓)
누르면 데이터 행 전체가 선택이 되고
그 후에 메뉴의 “삽입”, “피벗테이블”, “피벗테이블범위에서”를 선택하면
아래와 같은 페이지가 나옵니다.
그 후에 필요에 따라
“새 워크시트”나 “기존 워크시트”를 선택해서 피벗테이블을 만들면 됩니다.
저는 일반적으로 새로 워크시트를 만드는 것을 선호해서 “새 워크시트”에 선택이 된 채로 “확인”을 눌러주겠습니다.
그러면 아래와 같이 피벗테이블이 구성이 됩니다.
우측에서 피벗테이블 필드를 조절하면
좌측에서 해당 필드에 맞춰서 데이터 값을 보여지게 됩니다.
* 필드에 “월”과 “일”이 보이지 않는다면 데이터에서 년도와 월 열에 있는 #VALUE! 데이터를 삭제를 해주시고 새로고침을 하시면 보입니다.
* 데이터 자체에 에러(#VALUE!)가 있으면 필드에 변수가 보이지 않습니다.
* 새로고침을 하는 방법은 왼쪽의 피벗테이블 자리에서 임의선택후 우측클릭을 하면 해당 메뉴가 보입니다.
#피벗테이블 설정하기
년도별로 월별로 확진자수를 보고 싶은 것이기 때문에
값에는 “계(명)”, “국내발생(명)”, “해외유입(명)”, “사망(명)”을 넣고
행에는 “년도”, 월”을 넣으면 됩니다.
*필드에서 마우스로 선택후 드래그하면 됩니다.
해당 ∑값을 모두 합계로 변경을 해줘야합니다.
해당 변수를 마우스로 클릭하면
“값 필드 설정(N)”을 선택할 수 있습니다.
해당 설정을 선택한 후 “합계”로 변경을 해주시면 됩니다.
[참고]
해당 데이터의 하단을 보면 (비어 있음)이라고 해서
오류난 데이터가 있어서 맨 하단의 총합계가 안 맞는 경우가 생깁니다.
이런 경우 우측의 필드에 필터에 “일자”를 넣어서 해당 데이터를 제외하면 됩니다.
좌측의 (모두)의 아래화살표 버튼을 클릭하면
일자의 데이터를 선택할 수 있는데
하단의 “여러 항목 선택”을 클릭후
“누적(명)” 칸을 선택을 제외하면 깔끔하게 제외된 것을 볼 수 있습니다.
산출된 피벗데이터를 선택한 후
복사해서 붙여넣기를 하면
그대로 옆에 동일한 피벗데이터가 복사가 됩니다.
해당 ∑값을 모두 평균값로 변경을 하면
처음 목표를 했던 데이터를 산출할 수 있습니다.
#SUMIF와AVERAGEIF를사용해서대체하기
지금까지는 피벗을 이용해서 데이터를 분석을 하는 것을 알아봤습니다.
이렇게 피벗테이블을 통해서 데이터를 분석을 하는 방법 외에도
엑셀 함수를 사용해서 분석을 하는 방법이 있습니다.
피벗테이블 대신 엑셀 함수를 사용해서 분석을 진행을 하면
원본 데이터가 변경이 되었을 때(추가, 삭제 등) 피벗테이블 새로고침 없이 자동 함수 계산을 진행하면 됩니다.
만약 피벗테이블을 여러 개를 사용한 데이터 분석 엑셀파일이 있다면
원데이터가 변경이 되면 각각의 피벗테이블을 새로고침을 해줘야하지만
함수로 진행한 경우에는 전체 계산을 한번만 돌려주면 되므로 상대적으로 편리합니다.
또한 피벗테이블의 결과를 한번더 가공하는데 있어서도
엑셀 함수로 변경을 해서 사용하는게 훨씬 용이합니다.
그래서 실무에서도 피벗테이블 보다는 엑셀 함수를 사용해서
데이터를 정리하는 것을 더 선호합니다.
그리면 지금까지 피벗테이블과 동일한 결과를 나타내는 엑셀 함수를 안내해드리도록 하겠습니다.
위와 같이 피벗테이블에서 산출한 것과 동일하게
노란색의 합계표와 주황색의 평균표를 산출한다고 할 때
SUMIF 함수와 AVERAGEIF 함수를 통해 쉽게 산출을 할 수 있습니다.
두개의 함수의 구조는 동일하며
합계표에는 SUMIF 함수를
평균표에는 AVERAGEIF 함수를 사용하면 됩니다.
SUMIF의 구조는
SUMIF(조건이 있는 범위, 조건, 계산할 범위) 입니다.
그래서 K18 셀에 들어갈 함수는
=sumif($F:$F,$I18,B:B)입니다.
단, 여기서 주의해야할 점은 범위의 크기의 설정이 동일해야합니다.
위의 경우 열 기준(F, B)으로 세팅을 한거지만
만약 데이터가 A5:G1000 까지 구성이 되어 있다면
=sumif(($F$5:$F$1000,$I18,B$5:B$1000)
으로 F열과 B열의 크기의 구조가 같아야 합니다.
다시 이전으로 넘어와서
=sumif($F:$F,$I18,B:B)에서
I열과 F열은 고정 기호 “$”를 넣었고
B열을 고정 기호를 넣지 않는 이유는
계(명)에서 국내발생(명)으로 넘어가면
조건을 나타내는 값(I열과 F열)는 변경이 없지만
계산을 할 범위(B열)는 변경이 되기 때문에
고정 기호를 변경해서 넣는 것입니다.
그럼 K7:K17까지 들어갈 함수는
년도 조건 1개
월 조건 1개, 조건이 총 2개가 되기 때문에
SUMIFS 함수를 사용해야 합니다.
SUMIFS의 구조는
SUMIF(계산할 범위 , 조건1이 있는 범위, 조건1, 조건2이 있는 범위, 조건2…)
으로 다수의 조건이 있는 경우 계속해서 붙여 나갈 수 있습니다.
그래서 K7 셀에 들어갈 함수는
=sumifs(B:B,$F:$F,$I7,$G:$G,$J7)입니다.
K7열에 함수를 넣은 후에
복사후 K8:K17까지 붙여넣기를 하면
값이 잘 산출이 될 것 입니다.
그후 K7:K18까지 선택후 복사한후
L~N열까지 붙여넣기를 하면
합계 표를 완성할 수 있습니다.
일평균 표도 SUM를
AVERAGE로만 바꾸고
동일하게 진행을 하면 됩니다.
저만의 방식으로는
K7:K18 선택후 복사
R7:R18 붙여넣기를 한후
Ctrl+F 단축키를 사용해서 바꾸기로
들어가서 “sum”을 “average”로 변경을 진행한 후에
R7 셀 선택후 F2 키를 눌러서
지정된 값들을 마우스를 이용해서 변경을 하면 좀 더 빠르게 바꿀 수 있습니다.
그리고 동일하게 R열을 S에서 U열까지 복사하면 전체적으로 완료가 됩니다.
지금까지 피벗테이블의 기초적인 사용방법과 해당 피벗테이블 대신 사용할 수 있는 함수에 대해 알아봤습니다.