아래와 같이 날짜가 텍스트로 인식되는 경우 피벗 테이블을 만들어 보겠습니다.
1. 날짜가 텍스트로 인식되는 경우 피벗 테이블 만들기
가. 기존 워크시트에 피벗 테이블 만들기
A1셀부터 D16셀까지 범위를 지정할 필요 없이 표 안에 어디든, 예를 들어 B2셀에 커서를 넣고
삽입 > 피벗 테이블을 누릅니다.
이때 피벗 테이블 아래 꺾기 부분을 누르면 테이블/범위에서와 외부 데이터 원본에서 란 옵션이 표시되는데 테이블/범위에서를 선택할 것이기 때문에 아래 꺾기 부분을 누르지 말고 윗부분을 누르는 것이 단계를 축소하므로 좋습니다.
그러면 '표 또는 범위의 피벗 테이블'창이 열리는데, 표/범위가 A1에서 D16으로 자동 지정되고, 아래에 피벗 테이블을 배치할 위치를 묻는데 '새 워크시트'와 '기존 워크시트'가 있습니다. 또한 그 아래 '데이터 모델에 이 데이터 추가'란 옵션이 있습니다.
일반적으로는 데이터의 크기가 크기 때문에 '새 워크시트'를 선택하는데, 작으므로 '기존 워크시트'를 클릭하겠습니다.
그러면 위치를 묻는데, F1셀을 클릭하고 확인버튼을 누릅니다.
그러면 F1셀부터 범위가 정해지고, '보고서를 작성하려면 피벗 테이블 필드 목록에서 필드를 선택하세요'란 메시지가 보이고, 그 오른쪽에 '피벗 테이블 필드' 선택창이 보입니다.
피벗 테이블 필드 선택 창을 보면 윗부분에 표의 필드가 표시되고,
아래에는 필터, 열, 행, 값의 영역이 있고, 그 위에 '아래 영역 사이에 필드를 끌어 놓으십시오'라고 설명이 되어 있습니다.
그러나, 숫자인 경우는 값 영역으로 끌지 않아도 값 영역으로 배치되고, 텍스트 형식이라면 행에 자동으로 배치됩니다.
만약 원하는 영역이 아니라면 그때 필드를 끌어서 해당 영역에 놓으면 됩니다.
판매일과 지점명은 텍스트 형식이라 행 영역에, 판매금액은 숫자이기 때문에 값 영역에 배치됐고, 왼쪽에 피벗 테이블이 보입니다.
피벗 테이블을 보면 판매일별 지점명으로 집계가 되어 있습니다.
그런데 날짜인 경우는 연, 월별로 집계가 되어야 하는데 날짜가 텍스트 형식이기 때문에 연, 월별 그룹이 없습니다.
따라서, 날짜를 날짜 형식으로 바꿔야 합니다.
바꾸는 방법은 아래 글을 참고 바랍니다.
위 방법 중 선택하여 붙여넣기를 사용하는 방법을 적용해 보겠습니다.
E2셀에 1이라고 입력하고, Ctrl+C키를 눌러 복사한 다음,
B2셀부터 B16셀까지 범위를 선택하고 선택하여 붙여넣기를 누르고, 곱하기를 더블 클릭합니다.
그러면 날짜가 숫자로 바뀝니다.
나. 숫자를 간단한 날짜로 바꾸기
홈 > 표시형식 그룹에서 일반을 누른 다음 간단한 날짜로 바꿉니다.
그러면 아래와 같이 날짜로 바뀝니다.
홈 > 글꼴 > 모든 테두리를 선택해서 테두리를 넣을 수 있는데 여기서는 건너뛰겠습니다.
다. 선택 항목 그룹화
피벗 테이블 분석 > 데이터 그룹 > 새로 고침을 누르거나, 행 영역의 판매일을 마우스로 왼쪽으로 끌면 X 표시가 나오면 그때 마우스 버튼을 떼서 판매일을 지웁니다. 위 필드 목록에서 판매일 왼쪽의 체크를 눌러 지워도 됩니다.
그러나, 행 영역에 여전히 연, 월이 안 생깁니다.
이때는 날짜에 해당하는 F3셀 등을 클릭하고
'선택항목 그룹화'를 누릅니다.
※ 이때 아래와 같이 '선택 범위를 그룹으로 묶을 수 없습니다'라고 메시지가 나오면
피벗 테이블 분석 탭에서 새로 고침을 누릅니다. 그리고, 다시 '선택항목 그룹화'를 누르면
그룹화 창에 단위로 연, 분기, 월, 일이 표시됩니다.
월은 선택되어 있으므로 '연'을 클릭해서 2개를 선택하고 확인 버튼을 누릅니다.
그러면 피벗 테이블 필드 선택 창의 필드 목록과 행 영역에 '연'이 추가되고, 왼쪽 피벗 테이블에도 연, 월별로 집계가 추가됩니다.
지점별, 연월별로 집계되는데 연월별 지점별로 보고 싶으면 행 영역에서 지점명을 마우스로 끌어서 맨 아래로 내리면 됩니다. 그러면 아래와 같이 년월이 먼저 나오고 그 아래 지점명이 표시됩니다.
라. 숫자 표시형식에 쉼표 넣기
G열을 클릭한 후 홈 > 표시 형식 그룹 > 쉼표(,)를 클릭하면 쉼표가 들어가는데,
이렇게 하면 피벗 테이블 분석 > 새로 고침을 누르면 쉼표가 사라지므로
이 때는 판매금액 아무거나 클릭해서 선택한 후 마우스 오른쪽 버튼을 누른 후 '값 필드 설정' 메뉴를 누릅니다.
그러면 '값 필드 설정' 창이 열리면서 왼쪽 아래에 '표시 형식' 버튼이 표시되는데 이것을 누릅니다.
그러면 셀 서식 창이 열립니다. 숫자를 선택합니다.
그런 다음 '1000 단위 구분 기호(,) 사용 왼쪽에 체크합니다. 그리고 확인 버튼을 누릅니다. 그러면 값 필드 설정 창이 다시 표시되는데 확인 버튼을 누릅니다.
그러면 마찬가지로 , 표시가 들어가는데, 이 때는 새로 고침을 눌러도 ,가 없어지지 않습니다.
2. 날짜로 인식되는 경우의 피벗 만들기
가. 새 워크시트에 피벗 테이블 만들기
이 번에는 B2셀에 커서를 놓고 피벗 테이블을 새 워크시트에 만들어보겠습니다.
삽입 > 피벗 테이블을 클릭하면 A1셀부터 D16셀까지 자동으로 지정돼야 하는데 E2셀에 1이 들어 있어 범위를 자동으로 인식되지 못하고 표/범위에 B2셀만 표시됩니다.
따라서, 마우스로 A1셀을 클릭한 다음 끌어서 D16셀까지 끌어서 범위를 선택합니다. 그리고 마우스 버튼을 떼면
범위가 새롭게 지정됩니다. 아래와 같이 범위 선택 창이 떠 있다면 오른쪽 아래 화살표 키 부분을 누르면 됩니다.
범위가 맞으므로 확인 버튼을 누릅니다.
새 워크시트가 생기면서 오른쪽에 피벗 테이블 필드 창이 보입니다.
오른쪽 피벗 테이블 필드 창의 필드 목록에서 판매일, 지점명과 판매금액을 클릭하면 왼쪽 피벗 테이블이 연도별 합계로 표시됩니다.
날짜 형식이다 보니 '선택 항목 그룹화'를 할 필요 없이 오른쪽오른쪽 피벗 테이블 필드 창을 보면 연이 필드 목록과 행 영역에 자동으로 추가되었습니다.
나. 필드 확장, 필드 축소
연도 왼쪽의 + 를 누르면 해당되는 연도만 펼쳐지는데,
피벗 테이블 분석 탭의 '활성 필드' 그룹에 있는 '필드 확장' 또는 '필드 축소' 명령을 누르면 한꺼번에 펼쳐지고 닫힙니다.
아래는 필드 확장 명령을 눌러 연, 월별로 지점별 판매금액 합계액이 보이는 화면입니다.
다. 필드 설정
숫자에 쉼표를 넣으려면 피벗 테이블 분석 탭의 '활성 필드' 그룹에 있는 '필드 설정' 명령을 눌러도 됩니다.
'Excel' 카테고리의 다른 글
| Vlookup 함수(2) - 다른 시트, 표, 이름, iferror (2) | 2022.12.28 |
|---|---|
| Vlookup 함수(1) - 구문, 정확한 값, 유사값 찾기 (0) | 2022.12.27 |
| 수식이 텍스트로 입력될 때 (0) | 2022.12.22 |
| 숫자와 날짜가 텍스트로 처리될 때 (0) | 2022.12.21 |
| 데이터 도구 - 통합(2) (두 개 이상 시트의 통합) (0) | 2022.12.02 |