100개 이상의 숨겨진 피벗 테이블 트릭

“삽입” 탭에는 권장 피벗 테이블을 확인하는 옵션이 있습니다. “권장 피벗 테이블”을 클릭하면 보유한 데이터로 가능한 피벗 테이블 세트가 표시됩니다.

이 옵션은 사용 가능한 데이터에 대한 모든 가능성을 확인하려는 경우 매우 유용합니다.

Excel에는 데이터 범위를 선택할 때마다 나타나는 빠른 도구 모음처럼 보이는 “빠른 분석”이라는 도구가 있습니다.

또한 이 도구를 사용하면 피벗 테이블을 만들 수도 있습니다.

빠른 분석 도구 ➜ 테이블 ➜ 빈 피벗 테이블.

이것은 여러분이 지금 당장 사용해 보셨으면 하는 이 목록의 가장 유용한 피벗 테이블 팁 중 하나입니다.

다른 폴더에 있는 통합 문서에서 피벗하고 해당 통합 문서의 데이터를 현재 시트에 추가하고 싶지 않다고 가정해 보겠습니다.

현재 파일에 데이터를 추가하지 않고도 이 파일을 소스로 연결할 수 있습니다. 단계는 다음과 같습니다.

• 피벗 테이블 만들기 대화 상자에서 “외부 데이터 원본 사용”을 선택합니다.

• 그런 다음 연결 탭으로 이동하여 “더 찾아보기”를 클릭하세요.

• 사용하려는 파일을 찾아서 선택하십시오.
• 확인을 클릭하세요.
• 이제 데이터가 있는 시트를 선택하세요.

• 확인(두 번)을 클릭합니다.

이제 외부 소스 파일의 모든 필드 옵션이 포함된 피벗 테이블을 생성할 수 있습니다.

삽입 탭에서 피벗 테이블을 만드는 대신 “기본 피벗 테이블 및 피벗 차트 마법사”를 사용할 수도 있습니다.

클래식 마법사에서 제가 좋아하는 한 가지 점은 피벗 테이블을 만들기 전에 여러 워크시트에서 데이터를 추출 할 수 있는 옵션이 있다는 것입니다.

이 마법사를 여는 쉬운 방법은 키보드 단축키 Alt + D + P 를 사용하는 것입니다.

피벗 테이블 필드 설정에는 필드를 검색하는 옵션이 있습니다. 수백 개의 열이 있는 필드를 검색할 수 있습니다.

검색 상자에 입력을 시작하면 열 필터링이 시작됩니다.

“피벗 테이블 필드 창”의 스타일을 변경하는 데 사용할 수 있는 옵션이 있습니다. 오른쪽 상단의 톱니바퀴 아이콘을 클릭하고 적용하려는 스타일을 선택하세요.

대규모 데이터 세트가 있는 경우 필수 필드를 더 쉽게 찾을 수 있도록 필드 목록을 AZ 순서로 정렬할 수 있습니다.

오른쪽 상단의 톱니바퀴 아이콘을 클릭하고 ‘A부터 Z까지 정렬’을 선택하세요. 기본적으로 필드는 소스 데이터를 기준으로 정렬됩니다.

피벗 테이블을 생성하고 클릭하면 “필드 목록”이 오른쪽에 나타나고 피벗 테이블을 클릭할 때마다 이런 현상이 발생합니다.

그러나 비활성화할 수 있으며 이를 수행하려면 “피벗 테이블 분석” 탭에서 “필드 목록 단추”를 클릭하기만 하면 됩니다.

피벗 테이블을 만들고 나면 다음으로 해야 할 일은 피벗 테이블의 이름을 지정하는 것입니다.

이를 위해 분석 탭 ➜ 피벗 테이블 ➜ 피벗 테이블 옵션으로 이동한 다음 새 이름을 입력할 수 있습니다.

최근 Excel 온라인 애플리케이션에 피벗 테이블을 생성하는 옵션이 추가되었습니다(제한된 옵션).

Excel의 웹 애플리케이션에서 피벗을 만드는 것만큼 간단합니다.

삽입 탭의 테이블 그룹에서 ‘피벗 테이블’ 버튼을 클릭하세요.

…그런 다음 소스 데이터 범위를 선택합니다…

…삽입하려는 스프레드시트…

…그리고 마지막에 확인을 클릭합니다.

피벗 테이블 생성 프로세스를 자동화하려면 VBA 코드를 사용할 수 있습니다.

이 가이드에서는 매크로 코드를 사용하여 피벗 테이블을 만드는 간단한 단계별 프로세스를 언급했습니다.

Excel에는 단 한 번의 클릭으로 적용할 수 있는 피벗 테이블용으로 미리 정의된 여러 스타일이 있습니다.

디자인 탭에서 ‘피벗 테이블 스타일’을 찾을 수 있으며, ‘더보기’를 클릭하면 원하는 스타일을 간단히 선택할 수 있습니다.

새로운 사용자 정의 스타일을 만들 수도 있습니다. “새 피벗 테이블 스타일” 옵션을 사용하여 이 작업을 수행할 수 있습니다.

사용자 정의 스타일을 완료한 후에는 다음 번에 사용할 수 있도록 저장하면 그대로 유지됩니다.

피벗 테이블 옵션(피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 피벗 테이블 옵션으로 이동)으로 이동하여 “업데이트 시 셀 서식 유지” 확인란을 선택합니다.

이 옵션의 장점은 피벗 테이블을 업데이트할 때마다 기존 서식이 손실되지 않는다는 것입니다.

하나의 형식을 지정하는 것 외에도 이를 유지해야 하며 이는 “열 너비”입니다.

이렇게 하려면 “피벗 테이블 옵션”으로 이동하여 “업데이트 시 열 너비 자동 맞춤”을 선택 취소한 다음 확인을 클릭하세요.

피벗 테이블에서 여러 항목을 사용하는 경우 상위 항목에 대한 레이블을 반복하면 됩니다. 피벗 테이블의 구조를 쉽게 이해할 수 있습니다.

• 피벗 테이블을 선택하고 ‘디자인’ 탭으로 이동합니다.
• 디자인 탭에서 레이아웃 ➜ 보고서 레이아웃 ➜ 모든 항목 레이블 반복으로 이동합니다.

대부분의 경우 피벗 테이블을 생성한 후 값의 형식을 지정해야 합니다.

예를 들어 숫자의 소수점 이하 자릿수를 변경하려는 경우입니다. 값 열을 선택하고 “셀 서식” 옵션을 열기만 하면 됩니다.

그리고 이 옵션에서 십진수를 변경할 수 있습니다. “형식” 옵션에서 다른 옵션을 변경할 수도 있습니다.

서식 지정에 관해 제가 가장 좋아하는 점 중 하나는 피벗 테이블의 “글꼴 스타일”을 변경하는 것입니다.

형식 옵션을 사용할 수 있지만 가장 쉬운 방법은 홈 탭에서 수행하는 것입니다. 전체 피벗 테이블을 선택한 다음 글꼴 스타일을 선택하십시오.

둘 이상의 항목 필드가 있는 피벗 테이블을 추가하면 기본 필드에 대한 소계를 얻을 수 있습니다.

하지만 소계를 표시할 필요가 없는 경우도 있습니다. 이 경우 다음을 수행하여 숨길 수 있습니다.

• 피벗 테이블을 클릭하고 분석 탭으로 이동합니다.
• 분석 탭에서 레이아웃 ➜ 부분합계 ➜ 부분합계 표시 안 함을 선택하세요.

소계와 마찬가지로 총합계도 숨기고 표시할 수 있으며, 아래에는 이를 수행하는 간단한 단계가 나와 있습니다.

• 피벗 테이블을 클릭하고 분석 탭으로 이동합니다.
• 분석 탭에서 레이아웃 ➜ 총합계 ➜ 행 및 열 끄기로 이동합니다.

일반 피벗 테이블에서는 값 열에 값 형식이 하나만 있습니다.

그러나 아래와 같이 단일 피벗 테이블에서 다른 형식을 사용해야 하는 몇 가지 (드문) 상황이 있습니다. 이렇게 하려면 맞춤 형식을 사용해야 합니다.

Excel에는 사용할 수 있는 미리 정의된 색상 테마가 있습니다. 이러한 테마는 피벗 테이블에도 적용할 수 있습니다. ‘레이아웃’ 탭으로 이동하여 ‘테마’ 드롭다운 메뉴를 클릭하세요.

단 한 번의 클릭으로 적용할 수 있는 32개 이상의 테마가 있으며, 현재 서식 스타일을 테마로 저장할 수도 있습니다.

각 피벗 테이블에 대해 레이아웃을 선택할 수 있습니다.

Excel(2007 이상 버전을 사용하는 경우)에서는 세 가지 다른 레이아웃을 사용할 수 있습니다. 디자인 탭에서 레이아웃 보고서 ➜ 레이아웃으로 이동하여 적용하려는 레이아웃을 선택하세요.

피벗 테이블을 만들 때 가장 먼저 하는 일 중 하나는 “브랜드 행 및 열”을 적용하는 것입니다.

디자인 탭에서 적용 후 “Banded Column”과 “Banded Lines”을 확인하실 수 있습니다.

일반 필터와 마찬가지로 피벗 테이블에서도 필터를 활성화/비활성화할 수 있습니다. “분석 탭”에서 “필드 헤더” 버튼을 클릭하여 필터를 활성화하거나 비활성화할 수 있습니다.

피벗 테이블에서 하나 이상의 셀을 선택했고 해당 셀만 필터링하려는 경우 사용할 수 있는 옵션은 다음과 같습니다.

셀을 선택한 후 마우스 오른쪽 버튼을 클릭하고 ‘필터’로 이동한 다음 ‘선택한 항목만 유지’를 선택하세요.

선택한 셀을 필터링하는 것과 마찬가지로 숨길 수도 있습니다. 이렇게 하려면 “필터”로 이동한 후 “선택한 항목 숨기기”를 선택하세요.

일반 필터 외에도 레이블 필터와 값 필터를 사용하여 특정 값이나 기준으로 필터링합니다.

라벨 필터:

값 필터:

위의 팁에서 말했듯이 레이블 및 값 필드가 있을 수 있지만 이 두 필터 옵션을 함께 사용하려면 옵션을 활성화해야 합니다.

• 먼저 ‘피벗 테이블 옵션’을 열고 ‘전체 및 필터’ 탭으로 이동합니다.
• ‘합계 및 필터’ 탭에서 ‘필드당 여러 필터 허용’ 상자를 선택합니다 .
• 그런 다음 확인을 클릭하세요.

필터에서 제가 가장 좋아하는 옵션 중 하나는 “상위 10개 값”을 필터링하는 것입니다. 이 필터 옵션은 인스턴트 보고서를 생성할 때 유용합니다.

이렇게 하려면 “값 필터”로 이동하여 “상위 10″을 클릭한 다음 확인을 클릭해야 합니다.

피벗 테이블을 생성할 때 필터링하려면 “피벗 필드” 창에서 필터링할 수 있습니다.

열의 값을 필터링하려면 오른쪽에 있는 아래쪽 화살표를 클릭하고 필요에 따라 값을 필터링하면 됩니다.

피벗 테이블에서 데이터를 필터링할 때 제가 찾은 가장 좋은 방법 중 하나는 “슬라이서”를 사용하는 것입니다.

슬라이서를 삽입하려면 “분석 탭”으로 이동하여 “필터” 그룹에서 “슬라이서 삽입” 버튼을 클릭한 후 슬라이서를 삽입할 필드를 선택한 다음 확인을 클릭하면 됩니다.

관련 항목: Excel SLICER – 이를 사용하여 데이터를 필터링하는 방법에 대한 완전한 가이드

세그먼트를 삽입한 후에는 스타일과 형식을 변경할 수 있습니다.

• 슬라이서를 선택하고 옵션 탭으로 이동합니다.
• “슬라이서 스타일”에서 드롭다운 메뉴를 클릭하고 적용하려는 스타일을 선택합니다.

스타일 외에도 설정 창에서 설정을 변경할 수 있습니다. “슬라이서 설정” 버튼을 클릭하여 설정 창을 엽니다.

피벗 테이블이 여러 개인 경우 모든 테이블을 제어하기 어려울 때가 있습니다. 그러나 단일 세그먼트를 여러 피벗 테이블과 연결 하면 모든 피벗을 쉽게 제어할 수 있습니다.

• 먼저 슬라이서를 삽입합니다.
• 그런 다음 슬라이서를 마우스 오른쪽 버튼으로 클릭하고 “연결 보고”를 선택합니다.

• 대화 상자에서 모든 피벗을 선택하고 확인을 클릭합니다.

이제 단일 슬라이서를 사용하여 모든 피벗 테이블을 간단히 필터링할 수 있습니다.

세그먼트와 달리 타임라인은 날짜 필터링을 위한 특정 필터링 도구이며 일반 필터보다 훨씬 강력합니다.

슬라이서를 삽입하려면 “분석 탭”으로 이동하여 “필터” 그룹에서 ” 타임라인 삽입 ” 버튼을 클릭한 후 날짜 열을 선택하고 확인을 클릭하면 됩니다.

타임라인을 삽입한 후에는 스타일과 형식을 변경할 수 있습니다.

• 타임라인을 선택하고 옵션 탭으로 이동합니다.
• ‘타임라인 스타일’에서 드롭다운 메뉴를 클릭하고 적용하려는 스타일을 선택합니다.

스타일 외에도 설정을 변경할 수도 있습니다.

필터링할 값을 입력해야 하는 모든 필터 옵션에서 Excel 와일드카드를 사용할 수 있습니다. 문자 A로 시작하는 값을 필터링하기 위해 별표를 사용한 아래 예를 살펴보세요.

여러 필드에 필터를 적용한 경우 분석 탭 ➜ 작업 ➜ 지우기 ➜ 필터 지우기에서 이러한 필터를 모두 제거할 수 있습니다.

피벗 테이블은 동적이므로 새 데이터를 추가하거나 원본 데이터의 값을 업데이트할 때 피벗 테이블이 원본에서 새로 추가된 모든 값을 가져오도록 새로 고쳐야 합니다. 피벗 테이블을 새로 고치는 것은 간단합니다.

• 첫째, 피벗을 마우스 오른쪽 버튼으로 클릭하고 “새로 고침”을 선택합니다.
• 둘째, “분석” 탭으로 이동하여 “새로 고침” 버튼을 클릭하세요.

Excel에는 통합 문서를 열 때마다 피벗 테이블을 자동으로 새로 고치고 활성화할 수 있는 간단한 옵션이 있습니다. 이를 수행하기 위한 간단한 단계는 다음과 같습니다.

• 먼저 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 “피벗 테이블 옵션”으로 이동합니다.
• 그런 다음 “데이터” 탭으로 이동하여 “파일을 열 때 데이터 새로 고침” 상자를 선택하세요.

• 마지막에 확인을 클릭하세요.

이제 통합 문서를 열 때마다 이 피벗 테이블이 즉시 업데이트됩니다.

특정 간격 후에 피벗 테이블을 자동으로 업데이트하려는 경우 이 팁이 도움이 됩니다.

…방법은 다음과 같습니다.

• 먼저, 피벗 테이블을 생성할 때 “피벗 테이블 생성” 창에서 “이 데이터를 데이터 모델에 추가” 박스를 체크하세요.

• 그런 다음 피벗 테이블을 만든 후 셀 중 하나를 선택하고 “분석 탭”으로 이동합니다.
• 분석 탭에서 데이터 ➜ 데이터 소스 편집 ➜ 연결 속성을 선택하세요.

• 이제 “연결 속성”의 사용량 탭에서 “새로 고침 간격” 확인란을 선택하고 분을 입력하세요.

• 마지막에 확인을 클릭하세요.

이제 입력한 특정 기간이 지나면 피벗이 자동으로 새로 고쳐집니다.

때로는 소스 데이터에 오류가 있는 경우 피벗에 동일한 내용이 반영되는데 이는 전혀 좋은 일이 아닙니다.

가장 좋은 방법은 이러한 오류를 의미 있는 값으로 바꾸는 것입니다.

다음은 따라야 할 단계입니다.

• 먼저 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 피벗 테이블 옵션을 엽니다.
• 이제 “레이아웃 및 형식”에서 “오류 값 표시” 확인란을 선택하고 입력 상자에 값을 입력합니다.

• 마지막에 확인을 클릭하세요.

이제 모든 오류에 대해 지정한 값을 갖게 됩니다.

판매 데이터에 대한 피벗이 있고 일부 셀이 비어 있다고 가정해 보겠습니다.

왜 이 셀이 비어 있는지 모르는 사람이 물어볼 수 있기 때문입니다. 따라서 의미 있는 단어로 바꾸는 것이 좋습니다.

…이를 위해 따라야 할 간단한 단계입니다.

• 먼저 피벗 테이블을 “마우스 오른쪽 버튼으로 클릭”하고 피벗 테이블 옵션을 엽니다.
• 이제 “레이아웃 및 서식”에서 “빈 셀 표시” 확인란을 선택하고 입력 상자에 값을 입력합니다.

• 마지막에 확인을 클릭하세요.

이제 모든 빈 셀에 대해 지정한 값을 갖게 됩니다.

판매 데이터에 대한 피벗이 있고 일부 셀이 비어 있다고 가정해 보겠습니다.

왜 이 셀이 비어 있는지 모르는 사람이 물어볼 수 있기 때문입니다. 따라서 의미 있는 단어로 바꾸는 것이 좋습니다.

…이를 위해 따라야 할 간단한 단계입니다.

• 먼저 피벗 테이블을 “마우스 오른쪽 버튼으로 클릭”하고 피벗 테이블 옵션을 엽니다.
• 이제 “레이아웃 및 서식”에서 “빈 셀 표시” 확인란을 선택하고 입력 상자에 값을 입력합니다.

• 마지막에 확인을 클릭하세요.

이제 모든 빈 셀에 대해 지정한 값을 갖게 됩니다.

이제 여러 항목이 포함된 큰 피벗 테이블이 있다고 가정해 보겠습니다.

여기서는 피벗이 복잡해지지 않도록 각 요소 뒤에 빈 줄을 삽입할 수 있습니다.

수행하려면 다음 단계를 확인하세요.

• 피벗 테이블을 선택하고 디자인 탭으로 이동합니다.
• 디자인 탭에서 레이아웃 ➜ 빈 줄 ➜ 각 요소 뒤에 빈 줄 삽입으로 이동하세요.

이 옵션의 가장 좋은 점은 보고서를 더 명확하게 볼 수 있다는 것입니다.

피벗에 긴 항목 목록이 있는 경우 간단히 끌어서 놓기만 하면 해당 항목을 모두 사용자 정의 순서로 정렬할 수 있습니다.

월별 판매 데이터로 피벗 테이블을 만들고 제품을 보고서 필터로 사용했다고 가정해 보겠습니다.

“보고서 필터 페이지 표시” 옵션을 사용하면 각 제품에 대한 피벗 테이블이 있는 여러 스프레드시트를 만들 수 있습니다.

피벗 필터에 10개의 제품이 있는 경우 단 한 번의 클릭으로 10개의 서로 다른 스프레드시트를 만들 수 있다고 가정해 보겠습니다.

다음과 같이하세요:

• 피벗을 선택하고 분석 탭으로 이동합니다.
• 분석 탭에서 피벗 테이블 ➜ 옵션 ➜ 보고서 필터 페이지 표시로 이동합니다.

이제 4개의 별도 워크시트에 4개의 피벗 테이블이 있습니다.

값 필드에 값 열을 추가하면 SUM 또는 COUNT가 표시되는 경우가 있지만 여기에서 계산할 수 있는 다른 항목은 거의 없습니다.

“값 설정” 옵션을 열려면 값 열에서 셀을 선택하고 마우스 오른쪽 버튼을 클릭하세요.

컨텍스트 메뉴에서 “값 필드 설정”을 열고 다음을 클릭합니다.

‘값 요약 기준’ 필드에서 피벗에 표시하려는 계산 유형을 선택합니다.

한 달에 한 번 피벗 테이블 판매가 있다고 가정해 보겠습니다.

이제 피벗 테이블에 누계를 삽입하여 한 달 전체의 전체 매출 성장을 표시하려고 합니다.

단계는 다음과 같습니다.

• 마우스 오른쪽 버튼을 클릭하고 “값 필드 설정”을 클릭합니다.
• ‘값 표시 형식’ 드롭다운 목록에서 ‘누적 합계’를 선택합니다.

• 마지막에 확인을 클릭하세요.

피벗 테이블에 누계를 추가하는 방법에 대해 자세히 알아보세요 .

순위는 사물을 서로 비교할 수 있는 더 나은 방법을 제공합니다.

…피벗 테이블에 순위 열을 삽입하려면 다음 단계를 따르세요.

• 먼저 피벗에 동일한 데이터 필드를 두 번 삽입합니다.
• 그런 다음 두 번째 필드에 대해 마우스 오른쪽 버튼을 클릭하고 “값 필드 설정”을 엽니다.
• “값 표시 형식” 탭으로 이동하여 “가장 큰 것부터 작은 것까지 정렬”을 선택하세요.

• 마지막에 확인을 클릭하세요.

…피벗 테이블의 순위 지정에 대해 자세히 알아보려면 여기를 클릭하세요 .

제품 판매를 위한 피벗 테이블이 있다고 가정해 보세요.

이제 전체 매출에서 모든 제품이 차지하는 비율을 계산하려고 합니다.

사용 단계:

• 먼저 피벗에 동일한 데이터 필드를 두 번 삽입합니다.
• 그런 다음 두 번째 필드에 대해 마우스 오른쪽 버튼을 클릭하고 “값 필드 설정”을 엽니다.
• “값 표시 형식” 탭으로 이동하여 “총합계의 %”를 선택합니다.

• 마지막에 확인을 클릭하세요.

또한 빠른 보고서를 작성하기 위한 완벽한 옵션이기도 합니다.

피벗 테이블을 만들 때 Excel에서 피벗 테이블에 대한 새 워크시트를 추가하라는 메시지가 표시됩니다.

…하지만 기존 피벗 테이블을 새 워크시트로 이동할 수 있는 옵션도 있습니다.

• 이렇게 하려면 “분석 탭” ➜ 작업 ➜ 피벗 테이블 이동으로 이동하세요.

피벗에서 셀을 참조해야 하는 상황이 있습니다.

하지만 피벗에서 셀을 참조하면 Excel에서 자동으로 GetPivotData 함수를 참조용으로 사용하므로 문제가 발생할 수 있습니다.

가장 좋은 점은 이를 비활성화할 수 있다는 것이며 다음 단계는 다음과 같습니다.

• 파일 탭 ➜ 옵션으로 이동합니다.
• 옵션에서 수식 ➜ 수식 작업 ➜ “피벗 테이블 참조에 GetPivotData 함수 사용”을 선택 취소합니다.

이를 위해 VBA 코드를 사용할 수도 있습니다.

하위 비활성화GetPivotData()

Application.GenerateGetPivotData = 거짓

엔딩 자막

다음을 확인하세요 ➜ 유용한 Excel VBA 코드 상위 100개 + PDF 파일

월별 피벗 테이블을 만들고 싶지만 데이터에 날짜가 있다고 상상해 보세요.

이 상황에서는 몇 달 동안 추가 열을 추가해야 합니다.

그러나 가장 좋은 방법은 도우미 열을 추가할 필요가 없는 이 방법을 사용하여 피벗 테이블에서 날짜 그룹화 방법을 사용하여 만드는 것입니다.

아래 단계를 사용하십시오.

• 먼저 피벗 테이블에 날짜를 행 항목으로 삽입해야 합니다.

• 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 “그룹…”을 선택합니다.

• 기준 항목에서 ‘월’을 선택하고 확인을 클릭하세요.

모든 날짜를 월별로 그룹화하며 이 옵션에 대해 더 자세히 알고 싶다면 여기에서 전체 가이드를 확인하세요.

날짜와 마찬가지로 숫자 값도 그룹화할 수 있습니다.

단계는 간단합니다.

• 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 “그룹…”을 선택합니다.
• “기준” 상자에 그룹 범위를 생성할 값을 입력하고 확인을 클릭합니다.

…피벗 테이블 그룹화 옵션을 사용하여 Excel에서 히스토그램을 만드는 방법을 알아보려면 여기를 클릭하세요.

열을 행과 같이 그룹화하려면 행과 동일한 단계를 수행하면 됩니다. 하지만 그 전에 열 헤더를 선택해야 합니다.

피벗 테이블에 그룹이 필요하지 않은 경우 마우스 오른쪽 버튼을 클릭하고 “그룹 해제”를 선택하여 그룹을 해제하면 됩니다.

고급 피벗 테이블 사용자가 되려면 피벗 테이블에서 계산된 필드와 항목을 만드는 방법을 배워야 합니다.

아래 피벗 테이블에서 현재 데이터 필드에 10을 곱하여 필드별로 새 데이터를 생성해야 한다고 가정해 보겠습니다.

이 경우 피벗 테이블에 별도의 열을 만드는 대신 계산 항목을 삽입할 수 있습니다.

➜ 피벗 테이블에서 계산 항목 및 필드 생성에 대한 전체 가이드

피벗 테이블에 계산을 추가하거나 계산된 필드 또는 항목이 포함된 피벗 테이블이 있으면 사용된 수식 목록을 볼 수 있습니다.

이렇게 하려면 “분석 탭” ➜ 계산 ➜ 필드, 요소 및 집합 ➜ 수식 목록으로 이동하면 됩니다.

피벗 테이블에 사용된 수식 목록이 포함된 새 워크시트가 즉시 생성됩니다.

날짜에 중복된 값이 있는 경우 피벗 테이블을 사용하여 고유한 값 목록을 가져올 수 있습니다.

• 먼저 피벗 테이블을 삽입한 후 중복된 값이 있는 열을 행 필드로 추가해야 합니다.
• 그런 다음 피벗에서 이 행 필드를 복사하여 값으로 붙여넣습니다.
• 이제 값으로 가지고 있는 목록은 고유한 값의 목록입니다.

고유한 값을 확인하기 위해 피벗 테이블을 사용할 때 제가 좋아하는 점은 모든 경우에 적용할 수 있는 단일 설정이라는 것입니다.

반복해서 만들 필요가 없습니다.

소스 데이터에 값이 없거나 0인 항목이 있다고 가정해 보겠습니다.

필드에서 “데이터 없이 요소 표시” 옵션을 활성화할 수 있습니다.

• 먼저 필드를 마우스 오른쪽 버튼으로 클릭하고 “필드 설정”을 엽니다.
• 이제 “레이아웃 및 인쇄”로 이동하여 “데이터 없이 항목 표시”를 선택하고 확인을 클릭합니다.

팔! 데이터가 없는 항목은 피벗 테이블에 표시됩니다.

이것은 제가 가장 좋아하는 피벗 테이블 옵션 중 하나입니다.

이를 통해 이전 값과 비교하여 현재 값의 차이를 표시하는 열을 만들 수 있습니다.

월 값이 있는 피벗이 있다고 가정해 보겠습니다.

…그럼 이 옵션으로…

…아래와 같이 이전 달의 차이 값 열을 추가할 수 있습니다.

단계는 다음과 같습니다.

• 먼저 값 필드에 값이 있는 열을 두 번 추가해야 합니다.

• 그런 다음 두 번째 필드에 대해 “값 설정” 및 “값 표시 형식”을 엽니다.
• 이제 “다음으로 값 표시” 드롭다운 목록에서 “다음과의 차이”를 선택하고 “기본 항목”에서 “월”과 “(이전)”을 선택합니다.
• 마지막에 확인을 클릭하세요.

그러면 값 열이 이전 열과 다른 열로 즉시 변환됩니다.

피벗 테이블에서 값 셀을 두 번 클릭하면 해당 값의 기본 데이터가 표시됩니다.

이는 좋은 일이지만 이 일이 발생하는 데 항상 필요한 것은 아니므로 필요한 경우 끌 수 있습니다.

피벗 테이블 옵션을 열고 “데이터 탭”으로 이동하여 “표시 세부 정보 활성화”를 선택 취소하기만 하면 됩니다.

그런 다음 확인을 클릭합니다.

피벗 테이블을 PowerPoint 슬라이드에 붙여넣는 간단한 단계는 다음과 같습니다.

• 먼저 피벗 테이블을 선택하고 복사하세요.
• 그런 다음 PowerPoint 슬라이드로 이동하여 특수 붙여넣기 옵션을 엽니다.

• 이제 특수 붙여넣기 대화 상자에서 “Microsoft Excel 차트 개체”를 선택하고 확인을 클릭합니다.

이미지 삽입

피벗 테이블을 변경하려면 차트를 두 번 클릭해야 합니다.

Microsoft Word에서 피벗 테이블을 추가하려면 PowerPoint에서와 동일한 단계를 따라야 합니다.

행이나 열에 여러 차원 필드가 있는 경우 외부 필드를 확장하거나 축소할 수 있습니다.

펼치려면 + 버튼을 클릭하고 접으려면 – 버튼을 클릭해야 합니다.

…모든 그룹을 한 번에 확장하거나 축소하려면 마우스 오른쪽 버튼을 클릭하고 옵션을 선택하면 됩니다.

행이나 열에 여러 차원 필드가 있는 경우 외부 필드를 확장하거나 축소할 수 있습니다.

피벗 테이블에는 숫자 값으로 셀 수를 계산할 수 있는 옵션이 있습니다.

이를 위해 “값 옵션”을 열고 “요약 값 필드 기준”에서 “개수 번호”를 선택한 다음 확인을 클릭하기만 하면 됩니다.

예, 일치하는 값을 기준으로 정렬할 수 있습니다.

• 필터를 열고 “추가 정렬 옵션”을 선택하기만 하면 됩니다.

• 그런 다음 “액세스(A ~ Z) 기준:”을 선택하고 정렬할 열을 선택한 다음 확인을 클릭합니다.

메모:

값 열이 여러 개인 경우 정렬 순서에 하나의 열만 사용할 수 있습니다.

예, 피벗 테이블에 사용자 정의 정렬 순서를 사용할 수 있습니다.

• 이렇게 하려면 “추가 정렬 옵션”을 열 때 “추가 옵션”을 클릭하고 “보고서가 업데이트되면 자동으로 정렬” 상자를 선택 취소하세요.
• 그런 다음 정렬 순서를 선택하고 마지막에 확인을 클릭합니다.

새로운 사용자 정의 정렬 순서를 생성해야 하며, 파일 탭 ➜ 옵션 ➜ 고급 ➜ 일반 ➜ 사용자 정의 목록 편집에서 생성할 수 있습니다.

“레이아웃 지연 업데이트”를 활성화하고 나중에 영역 간에 필드를 끌어다 놓는 경우.

피벗 테이블 필드 모서리에 있는 업데이트 단추를 클릭하지 않으면 피벗 테이블이 업데이트되지 않습니다.

이렇게 하면 피벗 테이블을 더 쉽게 확인할 수 있습니다.

값 필드를 삽입하면 해당 필드에 대해 얻는 이름은 “금액 합계” 또는 “단위 수”와 같습니다.

그러나 때때로(음, 항상) 이 이름을 “Sum of” 또는 “Count of” 없이 이름으로 변경해야 합니다.

이렇게 하려면 셀에서 “개수” 또는 “합계”를 제거하고 이름 끝에 공백을 추가하기만 하면 됩니다.

그래 그거야.

전체 피벗 테이블을 한 번에 선택하려면 다음을 수행하세요.

피벗 테이블 셀 중 하나를 선택하고 키보드 단축키 Ctrl + A를 사용합니다.

또는…

분석 탭으로 이동 ➜ ➜ 전체 피벗 테이블을 선택합니다.

피벗 테이블을 값으로 변환하려면 전체 피벗 테이블을 선택한 후 다음을 수행하면 됩니다.

Ctrl + C를 사용하여 복사한 다음 선택하여 붙여넣기 ➜ 값을 입력하세요.

피벗 테이블이 포함된 워크시트를 보호할 때 다음 사항을 확인하세요.

“피벗 테이블과 피벗 차트를 사용하세요”

‘이 스프레드시트의 모든 사용자에게 다음을 허용합니다.’

특정 값 열에 대한 “값 설정”을 열고 싶다면…

…그래서…

…가장 좋은 방법은 열 머리글을 두 번 클릭하는 것입니다.

다음과 같이 생각한다면: 피벗 테이블을 처음부터 만들면 Excel에서 피벗 캐시가 생성됩니다.

따라서 처음부터 더 많은 피벗 테이블을 만들수록 Excel에서는 더 많은 피벗 캐시가 생성되고 파일은 더 많은 데이터를 저장해야 합니다.

그래서 요점은 무엇입니까?

데이터 소스에서 오는 모든 피벗 테이블에는 동일한 캐시가 있어야 합니다.

그런데 푸닛, 내가 어떻게 이럴 수가 있지?

간단합니다. 두 번째, 세 번째 또는 네 번째를 만들어야 할 때마다 첫 번째 항목을 복사하여 붙여넣고 변경하면 됩니다.

피벗 테이블을 다른 사람에게 보내기 전에 할 수 있는 또 다른 작업은 원본 데이터를 삭제하는 것입니다.

피벗 테이블은 계속해서 올바르게 작동합니다.

그리고 소스 데이터가 필요한 사람은 피벗 테이블의 총계를 클릭하여 얻을 수 있습니다.

피벗 테이블을 다른 사람과 공유하는 또 다른 방법은 웹 페이지를 만드는 것입니다.

예, 피벗 테이블이 포함된 간단한 HTML 파일입니다.

• 이렇게 하려면 통합 문서를 웹 페이지[html]로 저장하면 됩니다.

• 웹으로 게시 페이지에서 피벗 테이블을 선택하고 “게시”를 클릭합니다.

이제 이 HTML 웹 페이지를 누구에게나 보낼 수 있으며 휴대폰에서도 피벗 테이블(편집 불가능)을 볼 수 있습니다.

아래와 같이 Excel 파일에 대한 웹 링크가 있다고 가정해 보겠습니다.

 https://exceladvisor.org/book1.xlsx

이 통합 문서에는 데이터가 있으며 이 데이터를 사용하여 피벗 테이블을 만드는 데 필요합니다.

이를 위해서는 POWER QUERY가 핵심입니다.

이것을 확인하세요: 파워 쿼리 예제 + 팁과 요령

• 먼저 데이터 탭 ➜ 데이터 가져오기 및 변환 ➜ 웹에서로 이동합니다.

• 이제 “웹에서” 대화 상자에 통합 문서의 웹 주소를 입력하고 확인을 클릭합니다.

• 그런 다음 스프레드시트를 선택하고 ‘다음으로 로드’를 클릭하세요.

• 그런 다음 피벗 테이블 보고서를 선택하고 확인을 클릭합니다.

이제 입력한 웹 주소에서 통합 문서에 연결된 빈 피벗 테이블이 있습니다.

이제 원하는 대로 피벗 테이블을 만들 수 있습니다.

피벗 테이블과 함께 사용할 수 있는 모든 CF 옵션이 있습니다.

➜ 여기에 피벗 테이블에서 CF를 사용하는 다양한 방법을 배우는 데 도움이 되는 가이드가 있습니다.

필터링하는 대신 피벗 테이블에서 처음 10개 값을 강조표시할 수 있습니다.

이렇게 하려면 조건부 서식을 사용해야 합니다.

단계는 다음과 같습니다.

• 피벗 테이블의 값 열에 있는 셀 중 하나를 선택합니다.
• 홈 탭 ➜ 스타일 ➜ 조건부 서식으로 이동하세요.
• 이제 조건부 서식에서 상위/하위 규칙 ➜ 상위 10개 요소로 이동하세요.

• 가지고 있는 창에서 색상을 선택하세요.

• 그리고 마지막에 확인을 클릭하세요.

이 옵션은 피벗 테이블을 사용하여 빠른 보고서를 만들 때 매우 유용합니다.

아래 단계에 따라 피벗 테이블에서 조건부 서식을 간단히 제거할 수 있습니다.

• 먼저 피벗 테이블 셀 중 하나를 선택합니다.
• 그런 다음 홈 탭 ➜ 스타일 ➜ 조건부 서식 ➜ 규칙 지우기 ➜ “이 피벗 테이블에 대한 규칙 지우기”로 이동하세요.

피벗 테이블이 여러 개인 경우 CF를 하나씩 삭제해야 합니다.

피벗 테이블을 삽입하기 위한 간단한 키보드 단축키를 공유했지만 아래 단계도 사용할 수 있습니다.

• 피벗 테이블에서 셀을 선택하고 “분석 탭”으로 이동합니다 .
• ‘분석’ 탭에서 ‘피벗 차트’를 클릭하세요.

보유하고 있는 피벗 테이블에서 피벗 테이블이 즉시 생성됩니다.

피벗 테이블과 피벗 차트는 Excel에서 히스토그램을 만드는 데 제가 가장 좋아하는 방법입니다.

이에 대한 단계별 가이드는 다음과 같습니다.

새 피벗 테이블을 삽입하면 필터링할 수 있는 버튼이 표시되는데 때로는 별로 유용하지 않습니다.

그리고 그렇게 생각하면 전부 또는 일부를 숨길 수도 있습니다.

버튼을 마우스 오른쪽 버튼으로 클릭 하고 “차트에서 값 필드 버튼 숨기기”를 선택하여 선택한 버튼을 숨기거나 “차트에서 모든 필드 버튼 숨기기”를 클릭하여 모든 버튼을 숨깁니다.

피벗 테이블의 모든 버튼을 숨기면 차트 하단의 필터 버튼도 숨겨지지만 피벗 테이블, 슬라이서 또는 타임라인 필터를 사용하여 필터링할 수 있습니다 .

피벗 테이블을 PowerPoint 슬라이드에 붙여넣는 간단한 단계는 다음과 같습니다.

• 먼저 피벗 테이블을 선택하고 복사하세요.
• 그런 다음 PowerPoint 슬라이드로 이동하여 특수 붙여넣기 옵션 을 엽니다 .

• 이제 특수 붙여넣기 대화 상자에서 “Microsoft Excel 차트 개체”를 선택 하고 확인을 클릭합니다.

피벗 테이블을 변경하려면 해당 테이블을 두 번 클릭해야 합니다.

Alt + N + V

이 바로 가기 키를 사용하려면 소스 데이터를 선택했는지 또는 활성 셀이 소스 데이터에서 나온 것인지 확인하십시오.

Alt + Shift + 오른쪽 화살표

월이 포함된 피벗 테이블이 있고 처음 6개월 또는 마지막 6개월을 그룹화한다고 가정해 보겠습니다.

여러분이 해야 할 일은 이 6개의 셀을 선택하고 이 단축키를 사용하는 것뿐입니다.

Alt + Shift + 왼쪽 화살표

항목 그룹을 생성할 수 있는 것처럼 이 바로가기를 사용하면 해당 항목을 그룹에서 그룹 해제할 수 있습니다.

Ctrl + –

이 단축키는 선택한 셀을 숨길 뿐입니다.

실제로 셀을 숨기는 것이 아니라 필터링한 다음 필터 옵션에서 지울 수 있습니다.

Ctrl + –

이 단축키를 사용하려면 값 필드 열에서 셀을 선택해야 합니다.

그리고 이 단축키를 누르면 “ 계산된 필드 ” 창이 열립니다.

Alt + D 및 P

이 키보드 단축키에서는 나중에 키를 눌러야 합니다.

Alt + 아래쪽 화살표

이 키는 필드 목록을 엽니다.

Alt+F1

이 키보드 단축키를 사용하려면 피벗 테이블에서 셀을 선택해야 합니다. 이 키는 기존 시트에 피벗 테이블을 삽입합니다.

F11

그리고 새 워크시트에 피벗을 삽입하려면 위의 키만 사용하면 됩니다.