■ 목차
1. 엑셀 피벗 테이블이란? 피벗(Pivot) 뜻
2. 피벗 테이블 구성의 이해
3. 피벗 테이블 보고서 만들기
4. 피벗 요약(총합계/부분합) 없애기/보이기
5. 피벗 테이블 수정 후 새로 고침
6. 피벗 테이블 삭제 / 이동
7. 피벗 차트, 슬라이서
** 핵심 요약
1. 엑셀 피벗 테이블이란? 피벗(Pivot) 뜻
- 오늘은 초보자분들이 부담스러워하는 엑셀의 기능 중에 하나인 "피벗 테이블 (Pivot Table)"에 대해서 설명을 하도록 하겠습니다.
- 남들은 다 잘 사용을 하고 있고 엄청나게 좋은 기능이라고 얘기를 들었지만 막상 내가 사용을 하려고 하면 마음대로 되지 않은 "피벗 테이블"입니다.
- 사실 피벗 테이블은 너무나도 편하고 강력한 기능이기 때문에 결국 모두 잘 사용하게 됩니다. ^^ MS 엑셀에서 입에 침이 마르도록 자랑하는 막강한 기능 중에 하나입니다.
- 지금까지 배운 수 많은 함수며 기능을 그냥 묻어버리는 막강하고 많이 사용을 하는 기능이지만 이게 초보분들에게는 정말이지 초고급 기능처럼 멀게만 느껴지는 바다 건너 안드로메다 같은 존재입니다.
- 왜냐면 피벗 테이블을 잘 사용하고 위력을 공감하기 위해서는 기본적으로 데이터 관리가 잘 되어 있어야 합니다.
- 초보분들은 여기서부터 나도 모르게 어렵습니다. 데이터 관리가 잘 되어 있지 않으면 데이터 분석에서는 알게 모르게 오류가 발생이 되기 때문입니다.
- 표 형태로 데이터 작성의 기본이 잘 지켜고 그 데이터 수량도 어느정도 있어야만 그 기능을 온전히 몸으로 느끼며 감사한 사용을 체감할 수 있습니다.
- "피벗 테이블이란?" 열심히 입력한 엄청나게 많은 데이터를 몇 번의 마우스 클릭으로 요약하고 분석해주는 데이터 분석 기능입니다.
- 학생들의 성적표가 입력된 표에서 반별, 남녀별로 성적의 합계, 평균 등의 이런저런 분석을 마우스 몇 번 클릭으로 요약 테이블( = 피벗 테이블)로 만들어 볼 수 있습니다.
- 데이터만 열심히 잘 입력하면 엑셀은 단 몇 분만에 훌륭한 보고서를 만들 수 있다고 자랑하는 바로 그때 사용을 하는 기능이 "피벗 테이블"입니다.
- "피벗 (Pivot)"이란? 엑셀 도움말을 잘 보시면 "행을 열로 열을 행으로 이동("피벗")하여…"라고 얘기를 합니다.
- 피벗이라는 영어는 축, 돌리다라는 의미를 가지는데 회전문의 중심축이나 회전문처럼 축을 중심으로 돌아간다는 의미라고 합니다.
- 그래서 "피벗 테이블"은 표의 좌상단을 축으로 필드( = 분석하고자 하는 항목)들이 행에서 열로 열에서 행으로 회전(이동)한다는 의미로 자유자재로 필드을 행에서 열로, 열에서 행으로 이동해서 분석을 해준다는 의미입니다.
- 사실 "피벗 테이블"에서 "피벗"의 의미를 이해하시면 그래서 "피벗(Pivot)" 딱 맞는 이름이네라고 통하는 순간이 오게됩니다.
- 여러분도 이 과정을 끝에는 "피벗" 이름 잘지었네, 딱 맞는 이름이네라고 통하시면 좋을 듯합니다. ^^
2. 피벗 테이블 구성의 이해
- 원본 표의 제목에 해당하는 부분의 명칭이 "필드(=열)"의 이름입니다.
- 아래 표에서는 "반" 필드, "이름" 필드, "성별" 필드…. 가 존재하고 맨위의 제목은 "필드명"이 됩니다.
- 그리고 피벗 테이블에서는 바로 이 "필드"를 행 혹은 열 구역에 세팅해서 요약 테이블을 만들어 주는데
- "성별"의 필드을 세팅(=사용)하는 경우 이경우 필드의 데이터 구성이 "남", "여'로 되어 있어서 "남/여"별로 데이터를 집계해주게 됩니다.
- 여기서 각 필드의 데이터 종류를 잘 보시면 "반/이름/성별/시험구분"의 경우 "성별"의 "남/여"처럼
- 뭔가 데이터를 종류별로 묶어서(그룹핑)해서 집계할 수 있는 형태의 필드가 있고이고
- "국어/영어/수학/평균"의 필드는 숫자 데이터로 구성이 되어 있어서 "성별"의 "남/여"로 그룹핑한 기준으로
- 해당 그룹핑에 속하는 값으로 집계가 가능한 데이터가 있습니다.
- "남/여"별로(그룹핑 해서) "영어" 점수의 합계(숫자 계산)를 본다는 것은 의미가 있고 가능하지만
- "영어 점수"별로(숫자를) "평균의 합계" 등을(숫자 계산해서) 본다는 것은 의미가 없고 우리가 의도한 분석도 아닐 것입니다.
- 그래서 보통 "반/이름/성별/시험구분"처럼 뭔가를 그룹핑해서 보고 싶은 필드는 "행/열/필터 구역"에 세팅하게 되고
- 행/열 필드의 기준으로 합계 등 계산 하고 싶은 필드는 "값 구역"에 세팅을 하게됩니다.
- (다만 "값 구역"에 문자 데이터가 있는 필드가 들어가서 "개수"를 보는 경우는 있습니다.)
- 이거 몇 번 이렇게 저렇게 해보시면 바로 알 수 있습니다.
- 그래서 최종적으로 "반/성별"별로(그룹핑 해서) "평균"의 집계 값(계산 값)을 구하는 "피벗 테이블"을 만들 수 있게 됩니다.
3. 피벗 테이블 보고서 만들기
- 학급 성적표 데이터를 기준으로 아래와 같이 "반, 시험구분", "성별"별로 "평균" 성적을 집계해 보도록 하겠습니다.
1) 피벗 테이블 삽입
- 먼저 원본 "학급 성적표" 표의 임의의 위치를 마우스로 선택 후 "피벗 테이블" 삽입 메뉴를 선택합니다.
- 메뉴 패스 : (표의 임의의 셀 선택) > (리본 메뉴) "삽입" > "피벗 테이블"
2) 피벗 테이블 만들기
- "피벗 테이블" 삽입 메뉴를 클릭하시면 아래와 같이 "피벗 테이블 만들기" 메뉴가 보이게 됩니다.
- ① "범위 선택" - 데이터 원본의 범위 선택
- 이 경우 우리는 표에서 셀을 하나 선택하고 들어왔기 때문에 엑셀이 자동으로 전체표의 범위를 기본 값으로 보여주고 셀에서도 해당 영역이 점선으로 표시가 되어 보이게 됩니다.
- 원하는 범위가 맞다면 그대로 두시고 만약 원하는 범위가 아니라면 "표/범위"란을 마우스로 한 번 클릭하여 선택한 뒤에
- 원하는 시트로 이동하셔서 표의 범위를 마우스로 드래그해서 선택해 주시면 됩니다.
- ② "피벗 테이블 보고서" 삽입 위치
- "새 워크시트"는 새로운 워크시트하나를 자동으로 삽입하고 거기에 "피벗 테이블 보고서"를 만들게 되고
- "기존 워크시트"를 선택하게 되면 "위치" 필드가 활성화 되면서 삽입 위치를 사용자가 직접 지정해 줘야 합니다.
- 보통은 "새 워크시트"를 사용하게 되지만 보고서 크기가 작거나 원본 데이터를 보면서 피벗 테이블을 만들고 싶은 경우 "기존 워크시트"를 선택하기도 합니다.
- "기존 워크시트" 선택 시 주의 하실점은 보고서의 설정에 따라서 보고서 크기가 선택한 셀의 우측, 아래 측으로 확장이 되기도 하니깐
- 가능하면 보고서가 확장 되어도 표의 데이터나 다른 데이터값들과 겹치지 않는 위치에 지정(원본 표의 우측 빈 공간) 해줍니다.
- 금방 저희는 "피벗 테이블 만들기" 메뉴를 적용했습니다. 아직 피벗 테이블은 만들지도 않았는데. ㅠㅠ
- 피벗 테이블 보고서를 만들기 위한 원본 표을 지정하고 삽입할 위치를 설정하게 되면
- 원본 표에 있는 데이터들이 "피벗 테이블 캐시"라고 하는 곳으로 저장이되고 지금부터는 원본 데이터와는 별도로 "피벗 캐시"에 저장된 데이터를 사용해 빠르게 보고서가 작성이 됩니다.
- 그래서 원본의 데이터를 수정하여도 "데이터 업데이트 ( = 새로고침)"를 해줘야 "피벗 캐시"에 반영이 되고 최종적으로 "피벗 캐시"로 만들어진 "피벗 테이블 보고서"가 수정이 되게 되어 있습니다.
3) 피벗 테이블 필드 - "행" 필드 지정
- "피벗 테이블"의 행 위치에 "반" 필드와 "시험구분" 필드를 순서대로 적용을 하도록 하겠습니다.
- 적용 하는 방법은 상단의 필드 목록에서 "반", "시험구분" 필드 목록을 마우스로 선택하여 "행 구역"에 드래그&드롭으로 하시면 됩니다.
- 적용 순서는 "반" / "시험구분" 순서가 되도록 합니다. 순서를 반대로 하면( "시험구분/반") 다른 방식으로 보고서가 집계가 되니 순서를 맞춰 주도록 합니다.
- 이때 만약 순서가 "시험구분/반" 순서로 반대로 적용이 되었다면 "행 구역"에서 필드을 마우스로 선택해서 순서를 바꿔주시면 됩니다.
- "행 구역"에 "필드 이름" 오른쪽에 보면 "역삼각형 (▼)"의 메뉴가 있지만 그냥 마우스로 조정하는 것이 더 편하기 때문에 메뉴는 거의 사용하지 않습니다.
- 간혹 "피벗 테이블 필드" 설정하는 메뉴가 보이지 않는 경우가 있는데
- 이때는 "피벗 테이블 보고서" 영역을 마우스로 클릭 하거나
- "피벗 테이블 보고서" 선택 상태에서 마우스 우클릭 > "필드 목록 표시"를 선택해주시면 볼 수 있습니다.
4) 피벗 테이블 필드 - "열" 필드 지정
- 열 필드 지정도 동일하게 상단 "필드 목록"에서 "성별"필드를 마우스로 선택하여 "드래그 & 드롭"을 해줍니다.
5) 피벗 테이블 필드 - "값" 필드 지정
- "값" 필드 지정도 동일하게 상단 "필드 목록"에서 "평균"필드를 마우스로 선택하여 "드래그 & 드롭"을 해줍니다.
- 이렇게 "값 구역"에 "평균" 필드를 설정을 하게 되면
- "반/시험구분" 및 "성별"별로 "평균"값을 집계해주게 되는데 보통은 숫자 데이터들은 "합계"값을 자동으로 집계를 해줍니다.
- 만약 숫자 데이터가 아닌 다른 필드가 지정이되면 "합계"가 되지 않기 때문에 "개수"를 자동으로 지정하게 됩니다. (즉, "값 구역"에서 계산의 기본 값은 숫자는 합계, 문자는 개수 입니다.)
- 그리고 이렇게 "합계"로 집계된 데이터는 사용자가 원하면 "개수, 평균, 최댓값, 최솟값, …" 등 다양한 계산값으로 자유롭게 변경 할 수 있습니다.
- 우리도 "평균"필드를 "평균(Average)"으로 집계를 바꾸어 보도록 하겠습니다. (평균의 평균값을 집계 합니다.)
6) 피벗 테이블 필드 - "값" 필드 집계 함수 바꾸기
- "값 구역"의 "합계 : 평균"의 필드는 이름처럼 현재 "평균" 필드의 값으로 "합계"의 수식이 적용되어 있습니다.
- "값 구역)에서 "합계 : 평균" 필드을 마우스로 클릭 (우클릭이 아닙니다.)하여 "값 필드 설정"이라는 메뉴를 사용합니다.
- 메뉴 패스 : (값 필드) "합계 : 평균" 마우스로 선택 > "값 필드 설정" >"값 요약 기준" 탭 > "평균"
- "피벗 테이블 보고서"의 최종 형태가 완성 되었고 "합계 : 평균" → "평균 : 평균"으로 변형된 것도 확인이 됩니다.
- 완성된 "피벗 테이블 보고서"를 기준으로 다양한 분석을 시도해 보겠습니다.
- 현재 행/열 구역의 "반/시험구분", "성별"을 행에서 열로, 열에서 행으로 마우스로 선택해서 이동해 보시기 바랍니다.
- 바로 바로 변경된 다양한 분석을 하는 것이 가능합니다.
- 이렇게 행/열 그리고 필터 구역을 이동(Pivot)하면서 분석을 실시간으로 자유롭게 할 수 있는 기능이 바로 "피벗 테이블"입니다.
- 만약 피벗 기능 없이 함수 등으로 힘들게 결과 양식을 만들었는데 부장님이 아니 여기 말고 이렇게 볼 수 없느냐고 수정하라시면… ㅠㅠ
4. 피벗 요약(총합계/부분합) 없애기/보이기
- 피벗 테이블 보고서의 요약은 2가지가 존재합니다.
- 먼저 행/열의 "총합계"가 있고 "행/열"의 필드을 2개 이상 적용하는 경우 "부분합"이라는 게 존재하게 됩니다.
- 두 가지 모두 보고서에서 표시하거나 안 보이게 하는 것이 가능합니다.
- 아래 피벗 테이블 보고서의 "디자인" 메뉴는 "피벗 테이블 보고서"를 마우스로 선택한 경우에 (선택된 개체의 관련 메뉴로) 리본 메뉴에 자동으로 보이게 됩니다.
- "총합계" 표시/삭제 메뉴 패스 (리본 메뉴) : (피벗 테이블 보고서 마우스 선택) > "디자인" 탭 > "레이아웃" 그룹 > "총합계"
- "부분합" 표시/삭제 메뉴 패스 (리본 메뉴) : (피벗 테이블 보고서 마우스 선택) > "디자인" 탭 > "레이아웃" 그룹 > "부분합"
5. 피벗 테이블 수정 후 새로 고침
- 피벗 테이블 보고서는 빠른 실행을 위해서 원본 테이블 값을 "피벗 캐시"에 별도로 저장을 하고 이를 사용하여 "피벗 테이블 보고서"를 보여 줍니다.
- 그래서 "원본 테이블"의 데이터를 수정하여도 바로 "피벗 테이블 보고서"를 업데이트해주지 않습니다.
- 필요하면 수동으로 "새로 고침"이라는 것을 해줘야 합니다.
- 그리고 수정하는 방향/순서는 "원본 표의 데이터" → "피벗 캐시" → "피벗 테이블 보고서" 순으로 가능하며
- 역방향으로 "피벗 테이블 보고서"를 수정하여 원본을 변경할 수는 없습니다.
- 이게 "피벗 테이블 보고서"에 있는 데이터(숫자)는 여러 데이터 값들의 합계, 평균 등의 집계 데이터라서 수정을 해줘도 원본의 어느 데이터를 수정할지 엑셀로서는 알 수가 없기 때문입니다.
- 원본 테이블의 데이터가 수정되었다면 아래 메뉴 패스로 "새로 고침"을 해서 "피벗 테이블 보고서"에 반영해 주시기 바랍니다.
- 메뉴 패스 : (피벗 테이블 보고서에서 임의의 셀 선택) > (마우스 우클릭) > "새로 고침"
6. 피벗 테이블 삭제 / 이동
- "피벗 테이블 보고서"는 전체가 한 덩어리입니다. 조작은 가능하지만 부분적인 삭제는 불가능합니다.
- "이 부분을 변경할 수 없습니다"라고 메시지 표시가 될 것입니다.
- 그래서 삭제를 해야 한는 경우는 "피벗 테이블 보고서" 전체를 선택하여 "Delete" 키를 눌러서 통으로 삭제를 해줘야 합니다.
- 이렇게 피벗 테이블 보고서 전체, 통으로는 복사/붙여넣기/값 붙여 넣기, 마우스 드래그로 이동 모두 가능하니 한 번씩 사용해 보시기 바랍니다.
7. 피벗 차트, 슬라이서
- "피벗 차트"라고 하면 "피벗 테이블 보고서"로 만든 차트를 말합니다.
- 일부 종류의 차트는 "피벗 테이블"의 온전한 기능을 사용할 수 없지만 거의 대부분의 차트는 "필터"등의 기능을 사용하고
- 또한 "피벗 테이블 보고서"와 연동하여 사용하는 것이 가능합니다.
- 만드는 방법은 단순히 "피벗 테이블 보고서"의 데이터로 일반 차트를 만들어 주듯이 하시면 됩니다.
- 대신 생긴 모습이랑 기능이 일반 차트와 다르고 다양한 피벗 테이블의 필터 기능을 사용이 가능할 뿐입니다.
- 단순해 보이지만 굉장히 효율적인 기능이고 또한 "슬라이스"와 "시간 표시 막대"을 활용하여 실시간 필터링 효과의 다이내믹한 분석을 할 수 있습니다.
- 메뉴 패스 : (피벗 테이블 보고서 선택 상태) > "분석" 탭 > "필터" 그룹 > "슬라이서 삽입"
- 엑셀은 문서작성 프로그램이 아닙니다. 데이터를 분석하고 분석에서 정보를 얻기 위한 프로그램입니다.
- 셀 서식, 조건부 서식 등 모두 예쁜 데이터를 만들기 위한 것이 아니고 정확하고 명확한 데이터를 제공하기 위한 툴로 사용이 될 때 의미가 클 것 같습니다.
- 그러한 의미에서 "피벗 테이블 보고서", "피벗 차트"는 엄청난 기능일 수밖에 없습니다.
- 사실 "피벗 테이블 보고서"의 기능이라고 하면 굉장히 많은 부분들이 있지만
- 오늘은 간략하게 어떻게 피벗 테이블이 구성되고 만들어지는지 어떻게 해야 내가 원하는 데이터 분석이 가능한지에 대한 부분을 위주로 설명을 드렸습니다.
- 초보분들에게 다소 어려울 수도 있지만 아무조록 원하는 분석 툴로 잘 활용하시고 좋을 결과 만드시기를 기원드립니다.
** 핵심 요약 : 피벗 테이블이란? - 피벗 테이블 보고서, 차트 기본 개념과 원리 이해하기, 만들어 보기
1. 엑셀 피벗 테이블이란? 피벗(Pivot) 뜻
- "피벗 테이블이란?" 몇 번의 마우스 클릭으로 요약하고 분석해주는 데이터 분석 기능이다.
- "피벗 (Pivot)"이란? 영어로 축, 돌리다라는 의미로 자유자재로 필드를 행에서 열로, 열에서 행으로 이동해서 분석을 해준다는 의미이다.
2. 피벗 테이블 구성의 이해
- 피벗 테이블은 표의 머리글에 행당하는 "필드"이름을 기준으로 데이터 분석이 가능하다.
- 이 "필드"를 행 혹은 열에 세팅해서 요약 테이블을 만들어 준다.
- 필드는 뭔가 데이터를 종류별로 묶어서(그룹핑)해서 집계할 수 있는 형태의 필드가 있고
- 숫자 데이터로 구성이 되어 해당 그룹핑에 속하는 값으로 합계 등 집계가 가능한 데이터가 있다.
- 그룹핑해서 보고싶은 "필드"는 "피벗 테이블 필드"에서 "행/열/필터 구역"에 세팅하고
- 집계를 하고 싶은 필드는 "값 구역"에 세팅을 하게 된다.
3. 피벗 테이블 보고서 만들기
- 1) 피벗 테이블 삽입
- 2) 피벗 테이블 만들기
- 3) 피벗 테이블 필드 - "행" 필드 지정
- 4) 피벗 테이블 필드 - "열" 필드 지정
- 5) 피벗 테이블 필드 - "값" 필드 지정
- 6) 피벗 테이블 필드 - "값" 필드 집계 함수 바꾸기
4. 피벗 요약(총합계/부분합) 없애기/보이기
- 행/열의 "총합계"가 있고 "행/열"의 필드을 2개 이상 적용하는 경우 "부분합"이라는 게 존재하게 된다.
- "피벗 테이블 관련" 리본 메뉴 "디자인"에서 "총합계", "부분합" 메뉴로 삽입/삭제가 가능하다.
5. 피벗 테이블 수정 후 새로 고침
- 피벗 테이블 보고서는 빠른 실행을 위해 원본 데이터를 "피벗 캐시"에 별도로 저장하여 사용하고 있다.
- 그래서 원본 테이블의 데이터가 수정이 되면 수동으로 "새로 고침"을 해줘야 한다.
6. 피벗 테이블 삭제 / 이동
- "피벗 테이블 보고서" 전체가 한 덩어리이기 때문에 전체를 선택하여 삭제, 복사, 붙여넣기, 이동 등이 가능하다.
7. 피벗 차트, 슬라이서
- "피벗 테이블 보고서"로 만든 차트가 "피벗 차트"이고 서로 연동이 되어 사용하고 "필터"등의 기능이 있다.
- 피벗 테이블/차트 관련 리본메뉴의 "분석" > "슬라이서 삽입"으로 실시간 필터의 다이나믹한 분석 효과를 사용해 보자.
[엑셀 특강 (개념 잡기)] - 999. 엑셀 기초 특강 목차
999. 엑셀 기초 특강 목차
[엑셀 특강 (개념 잡기)] - 001. (엑셀 기초 특강) 엑셀 데이터란? (feat. 숫자, 문자, 날짜 무엇이 다른가?) [엑셀 특강 (개념 잡기)] - 002. (엑셀 기초 특강) 엑셀 함수란? (feat. 왜 그렇게 어렵고 많아야
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글