피벗 테이블은 모든 사람에게 유용합니다. 신속하게 계산하고 유연하게 구성할 수 있으며 필요한 경우 디자인을 우아하게 감쌀 수 있습니다. 그러나 연고에는 특히 값 영역에 숫자가 아니라 텍스트가 포함되어야 하는 요약을 만들 수 없다는 점도 있습니다.
이 한계를 극복하고 비슷한 상황에서 "목발 한 쌍"을 생각해 봅시다.
우리 회사가 우리 나라와 카자흐스탄의 여러 도시로 제품을 컨테이너로 운송한다고 가정합니다. 컨테이너는 한 달에 한 번 이상 보내지 않습니다. 각 컨테이너에는 영숫자 번호가 있습니다. 초기 데이터로 배송을 나열하는 표준 테이블이 있습니다. 이 테이블에서 각 도시와 월별로 보낸 컨테이너 수를 명확하게 보기 위해 일종의 요약을 작성해야 합니다.
편의를 위해 명령을 사용하여 초기 데이터가 있는 테이블을 미리 "스마트"하게 만들어 보겠습니다. 홈 – 테이블 형식 (홈 — 표 형식) 그리고 그녀에게 이름을 지어줘 배송 탭 건설자 (디자인). 앞으로 이것은 삶을 단순화 할 것입니다. 왜냐하면. 수식에서 직접 테이블 이름과 해당 열을 사용할 수 있습니다.
방법 1. 가장 쉬운 방법 - 파워 쿼리 사용
파워 쿼리는 Excel에서 데이터를 로드하고 변환하기 위한 매우 강력한 도구입니다. 이 추가 기능은 2016년부터 기본적으로 Excel에 내장되어 있습니다. Excel 2010 또는 2013이 있는 경우 별도로 다운로드하여 설치할 수 있습니다(완전 무료).
명확성을 위해 전체 프로세스를 다음 비디오에서 단계별로 분석했습니다.
파워 쿼리를 사용할 수 없는 경우 피벗 테이블이나 수식을 통해 다른 방법을 사용할 수 있습니다.
방법 2. 보조 요약
간단한 공식을 사용하여 테이블의 각 행 수를 계산하는 원래 테이블에 열을 하나 더 추가해 보겠습니다.
분명히 -1이 필요합니다. 테이블에 한 줄의 헤더가 있기 때문입니다. 테이블이 시트의 시작 부분에 없으면 현재 행과 테이블 머리글의 수의 차이를 계산하는 약간 더 복잡하지만 보편적인 공식을 사용할 수 있습니다.
이제 표준 방식으로 데이터를 기반으로 원하는 유형의 피벗 테이블을 작성하지만 값 필드에서 필드를 삭제합니다. 줄 번호 우리가 원하는 것 대신 컨테이너:
같은 달에 같은 도시에 여러 개의 컨테이너가 없기 때문에 요약에서는 실제로 금액이 아니라 필요한 컨테이너의 줄 번호를 제공합니다.
또한 탭에서 총계 및 소계를 끌 수 있습니다. 생성자 – 총계 и 부분합 (디자인 — 총계, 소계) 같은 위치에서 버튼을 사용하여 요약을 보다 편리한 표 레이아웃으로 전환합니다. 보고서 모형 (보고서 레이아웃).
따라서 우리는 이미 결과의 절반에 도달했습니다. 도시와 월의 교차점에 소스 테이블에 행 번호가 있고 필요한 컨테이너 코드가 있는 테이블이 있습니다.
이제 요약(동일한 시트 또는 다른 시트에)을 복사하여 값으로 붙여넣은 다음 수식을 값 영역에 입력합니다. 그러면 요약에서 찾은 줄 번호로 컨테이너 코드가 추출됩니다.
함수 IF (만약), 이 경우 요약의 다음 셀이 비어 있지 않은지 확인합니다. 비어 있으면 빈 텍스트 문자열 ""을 출력합니다. 즉, 셀을 비워 둡니다. 비어 있지 않으면 열에서 추출 컨테이너 소스 테이블 배송 함수를 사용하여 행 번호로 셀 내용 INDEX (인덱스).
아마도 여기에서 명확하지 않은 유일한 점은 이중 단어입니다. 컨테이너 수식에서. 그런 이상한 형태의 글쓰기:
용품[[컨테이너]:[컨테이너]]
... 열을 참조하는 데만 필요합니다. 컨테이너 (일반 "비스마트" 테이블에 대한 $ 기호 참조와 같이) 절대적이며 공식을 오른쪽으로 복사할 때 인접 열로 미끄러지지 않았습니다.
앞으로 소스 테이블의 데이터를 변경할 때 배송, 보조 요약을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 행 번호로 보조 요약을 업데이트하는 것을 기억해야 합니다. 업데이트 및 저장 (새로 고치다).
방법 3. 공식
이 방법은 중간 피벗 테이블 생성 및 수동 업데이트가 필요하지 않지만 Excel의 "무거운 무기" 기능을 사용합니다. 썸슬림 (수미프). 요약에서 행 번호를 찾는 대신 다음 공식을 사용하여 계산할 수 있습니다.
일부 외부 부피가 있는 경우 실제로 이것은 선택적 합산 기능의 표준 사용 사례입니다. 썸슬림주어진 도시와 월의 행 번호를 합산하는 A. 다시 말하지만, 같은 달에 같은 도시에 여러 개의 컨테이너가 없기 때문에 실제로 우리 함수는 금액이 아니라 줄 번호 자체를 제공합니다. 그런 다음 이전 방법에서 이미 친숙한 기능 INDEX 컨테이너 코드를 추출할 수도 있습니다.
물론 이 경우 요약 업데이트에 대해 더 이상 생각할 필요가 없지만 큰 테이블에서는 함수 수메슬리 눈에 띄게 느려질 수 있습니다. 그런 다음 공식의 자동 업데이트를 끄거나 첫 번째 방법인 피벗 테이블을 사용해야 합니다.
요약의 모양이 보고서에 적합하지 않은 경우 우리가 했던 것처럼 직접적으로가 아니라 함수를 사용하여 요약에서 행 번호를 최종 테이블로 추출할 수 있습니다. GET.피벗.테이블.데이터 (GET.피벗.데이터). 이 작업을 수행하는 방법은 여기에서 찾을 수 있습니다.
- 피벗 테이블을 사용하여 보고서를 만드는 방법
- 피벗 테이블에서 계산을 설정하는 방법
- SUMIFS, COUNTIFS 등을 사용한 선택적 계산