안녕하세요. 김대리입니다. 

이번에는 피벗테이블(PIVOT TABLE)에 대해서 얘기해보겠습니다. 

저는 피벗테이블이 엑셀에서 가장 유용한 기능이라고 생각합니다. 

피벗테이블이란, 

데이터가 나열되어있는 정보들을 활용하여,

필요한 정보를 표의 형태로 요약시킬 수 있는 기능입니다.

지난번에 SUMIFS 함수에 대해서 얘기하면서, 

위와 같은 데이터를 활용할 때에는 SUMIFS함수보다 피벗테이블이 유용하다고 적었었습니다. 

이번에는 똑같은 예제를 가지고 SUMIFS함수와 똑같은, 아래의 정보를 만들어보도록 하겠습니다. 

<SUMIFS함수로 만든 결과물>

피벗테이블을 만드는 것은 어렵지 않습니다. 

사실, 피벗테이블을 활용하기 위해서 자료를 맨 위의 예산표처럼 만드는 것과, 

피벗테이블을 만든 후 원하는 자료의 형태를 만드는것이 더 중요합니다. 

※예제 파일을 다운받아서 따라해주세요.

PIVOT table예제.xlsx

①먼저, 결과물(표)을 위치시킬 곳에 커서를 둔 뒤(저는 새 시트의 A1셀에 커서를 두었습니다.),

②상단 "삽입" 메뉴에서 가장 좌측에 있는 "피벗테이블"을 클릭합니다. 

(클릭 후 "피벗 테이블 만들기"라는 메뉴가 뜨면 두가지를 지정해야합니다.

<분석할 데이터>와 <피벗테이블보고서를 넣을 위치>가 그 두가지 입니다만, 

저와 똑같이 진행하셨다면 피벗테이블을 넣을 위치는 새시트 A1에 지정하였기때문에

<분석할 데이터>만 지정해주시면 됩니다.) 

③예산표를 드래그하여 지정해줍니다. (최상단의 헤더까지 포함시켜주셔야합니다.) 

(*A1:D23까지 지정해주셔도 됩니다만, 저는 그 아랫쪽까지 모두 포함하는 A:D로 지정하였습니다.)

③까지 완료하게 되면, 위와같은 피벗테이블이 생성됩니다. 

피벗테이블에 커서를 올리게 되면 우측에 피벗테이블필드가 생성되었을겁니다. 

이후에는 이 피벗테이블을 통해 원하는 정보를 만들면 되겠습니다. 

우리는 위와 같은 정보를 만들고자 하는 것이므로, 똑같은 모양을 만들어보겠습니다. 

만드는 방법은 간단합니다. 

상단에 있는 '필드' 들을 아래쪽에 있는 '필터', '열', '행', '값' 등에 드래그해서 옮겨주면 됩니다. 

위와같은 표의 모양을 만들기 위해 '회계연도'와 '기능'을 옮겨보겠습니다. 

이렇게 열과 행을 설정하게 되면, 좌측에 있는 피벗 테이블은 아래처럼 변하게 됩니다. 

이렇게 만들게 되면, 우리가 원하는 표의 모양이 완성되었습니다. 

이제 SUMIFS함수를 채워넣었듯 안쪽에 금액의 합계를 표시하기 위해, 

'피벗 테이블 필드'의 하단에 있는 '값'에 "금액"필드를 옮겨보겠습니다. 

이렇게 "금액"을 옮겨주게 되면

이와같이 우리가 원하는 데이터를 요약할 수 있습니다. 

SUMIFS함수를 설명하며, '이런 자료를 피벗테이블을 활용하는 것이 낫다.' 라고 말씀드렸던 이유는 아래와 같습니다.

회사에서 일을 하다보면, SUMIFS함수를 통해 자료를 만든 후에 정보이용자로부터 

Q. 예산항목별로 2017년 2018년 비교한 자료는 없나요? 

Q. 기능별 비교를 포함한 세부항목으로 볼 수는 없나요?

등의 새로운 요구를 받을 때가 많습니다. 

SUMIFS함수나 다른 함수를 활용할 경우 새로운 결과물을 짜기 위해서 함수도 알아야 하고, 

하나하나 오류가 생길 위험도 감수해야 합니다. 

하지만 피벗테이블은 '피벗 테이블 필드'에서 원하는 자료를 열과 행에 넣는것만으로 다양한 자료로 가공이 가능합니다. 

또한, 현재는 2017년과 2018년의 자료만 기재되어있지만, 2019년이 되고 2020년이 되었을때,

예산표의 하단에 2019년 자료를 추가만 해주신 후 피벗테이블을 새로고침하시면, 

추가된 자료까지 한번에 볼 수 있습니다. (이것이 피벗테이블 데이터영역을 A:D로 한 이유입니다.)

※주의할점 

A. 데이터를 추가한 뒤 '새로고침'을 누를 것.

B. 값 필드 설정에서 '값의 형태'를 잘 지정할 것(합계인지, 갯수인지)

C. 자료를 활용하다 행레이블, 열레이블에 누락되는 것이 있는지 확인.

.

안녕하세요! 김대리입니다.

이번에는 'MATCH'함수를 다뤄해보겠습니다.

이 함수는 지난번에 말씀드렸던과 같이 INDEX 함수와 함께 사용되는 경우가 많습니다.

(추가적으로 피벗테이블과 함께 사용할 수 있습니다.)

INDEX함수와 함께 사용하는 법은 따로 다루도록 하고,

이번에는 MATCH함수가 어떤 기능을 하는지부터 다뤄보겠습니다.

MATCH함수는 세개의 영역으로 구성됩니다.

MATCH 함수의 구성은 다음과 같습니다.

​

=MATCH[①, ②, ③]

                                          ① : 찾고싶은 값

                                          ② : 범위

                                          ③ : 매치타입(-1, 0, 1중에 선택)

​

​

함수의 의미는

"찾고싶은값(①)이 범위(②)에서 몇번째 셀에 있는지" 출력합니다.

예를들어,

왼쪽의 표를 가지고 있을 경우,

오른쪽의 데이터를 만들 수 있습니다.

이처럼 수식을 적어주면 됩니다.

이렇게 수식을 적어주었을때,

​

서울이 몇번째 셀에 위치하는지 E열에 출력되게 됩니다. 

아래에 수식을 채우면 해당 지역이 몇번째 셀에 위치하는지 출력됩니다.

MATCH함수는 위처럼 세로형으로만 사용될 수 있는것은 아닙니다.

가로양식에도 똑같이 적용될 수 있습니다.

이러한 가로양식이 있을경우

위처럼 수식을 적어주게 되면

이렇게 셀의 순번을 불러오게 되고

이러한 자료를 만들 수 있습니다.

이를 정리해보면 아래와 같이 사용할 수 있습니다. 

다음번에는 INDEX와 MATCH함수를 동시에 활용하는 법에 대하여 작성해보겠습니다. :)

안녕하세요! 김대리입니다.

이번에는 표에서 원하는 위치에 있는 값을 불러오는 'INDEX'함수를 다뤄해보겠습니다.

이 함수는 독립적으로 사용되기도 하지만, MATCH 함수와 함께 사용되는 경우가 많습니다.

(추가적으로 피벗테이블과 함께 사용할 수 있습니다.)

MATCH함수와 함께 사용하는 법은 다음번에 다루도록 하고,

이번에는 INDEX함수가 어떤 기능을 하는지부터 다뤄보겠습니다.

INDEX함수는 세개의 영역으로 구성됩니다.

INDEX 함수의 구성은 다음과 같습니다.

​

=INDEX[①, ②, ③]

                                                     ① : 표의 전체

                                                     ② : 열의 순서

                                                     ③ : 행의 순서

​

​

함수의 의미는

"표(①)에서 몇번째 열(②), 몇번째 행(③)에 있는 값을 불러온다."

입니다.

예를들어,

이러한 표가 있다고 가정하면

①영역은 이 표의 전체를 선택하여야 하고,

②영역은 열 순서를 적어야 합니다.(알파벳쪽이 아닌 숫자쪽이 먼저임에 유의해야합니다.)

③영역은 위에 적혀져 있는 행 순서를 적어야 합니다.

이 표의 3번째 열, 4번째 행에 있는 23이라는 숫자(2학년 3반의 학생수)를 불러오고자 한다면

이처럼 수식을 적어주면 됩니다.

이렇게 수식을 적어주었을때,

이러한 E11셀에 23이라는 숫자가 출력되는 것을 볼 수 있습니다.

​

​

다시 한 번 복습을 해보면 아래와 같습니다.

사실 index함수는 독립적으로 쓰일때보다,

match함수와 함께 쓰이면 효과가 좋고,

피벗테이블과 쓰여도 좋은 효과를 기대할 수 있습니다.

추가적으로 match함수, 피벗테이블과의 활용법에 대하여 작성해보겠습니다.