이 새로운 엑셀 기능을 찾은 후 요약을 위해 피벗 테이블을 만드는 것을 그만두었습니다

수년 동안, 빠른 분석이 필요할 때 나의 반사적인 방법은 항상 같았습니다. 피벗 테이블을 삽입하고, 필드를 올바른 박스로 끌어다 놓은 후, 숫자가 바뀔 때마다 새로 고침을 하는 것이었죠. 효과가 있긴 했지만, 질문이 요구하는 것보다 더 많은 클릭이 필요했습니다. PIVOTBY는 동일한 그룹화와 수식을 그리드 안에 하나의 수식으로 수행하며, 새로 고침이 전혀 필요 없습니다. 이는 비교적 최신 Microsoft 365 동적 배열 함수 중 하나이므로, 스프레드시트가 여전히 오래된 도구에 의존한다면, 클릭 대신 입력할 가치가 있는 이유가 여기에 있습니다.

PIVOTBY는 설정 없이 요약 표만 나에게 제공합니다

하나의 수식으로 그룹화와 집계를 수행합니다

피벗 테이블은 아무것도 보여주기 전에 일련의 동작을 요구합니다. 피벗 테이블을 삽입하고 필드 목록을 열고 한 필드를 행으로, 다른 필드를 열로, 세 번째 필드를 값으로 끌어다 놓은 다음 결과를 배치합니다. 그러나 PIVOTBY는 그 모든 단계를 건너뜁니다. 저는 하나의 수식을 입력하고 Enter를 누르면 그룹화된 요약이 옆 셀들로 흘러 나옵니다.

내 판매 시트에서 지역 및 제품 카테고리별 총 판매를 원한다고 말하세요. 공식은 다음과 같습니다:

=PIVOTBY(B2:B33, C2:C33, F2:F33, SUM)

지역은 행을 따라 내려가고, 제품 카테고리는 상단을 가로지르며, 판매 금액 열은 각 셀에 합산됩니다. 출력은 피벗 테이블이 제공할 결과를 그대로 반영합니다. 북부는 30,100달러, 남부는 28,090달러로 나타나며, 스포츠는 남부에서 가장 강한 카테고리로 9,240달러를 기록합니다. 총합계는 가장자리에 별도로 표시되며, 전체가 하나의 연속된 범위로 다시 계산됩니다.

공식은 보고서인데, 이는 내가 왼쪽 상단 셀을 클릭하기만 하면 그것이 어떻게 만들어졌는지 정확히 읽을 수 있다는 뜻입니다.

내 첫 번째 PIVOTBY를 작성하는 데는 대화 상자가 아니라 한 줄이면 충분했다

논거들은 제가 이미 하고 있는 질문들과 대응됩니다

패턴을 한 번 보면, 문법 구조가 당신이 묻고 있는 질문처럼 읽힙니다. 여기에 뼈대가 있습니다:

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], ...)

처음 세 개의 인수는 매번 사용할 것들입니다.

• row_fields: 열이 측면으로 그룹화된 것입니다.
• col_fields: 이것은 맨 위에 있는 열입니다.
• 값: 요약되고 있는 데이터입니다.
• 기능: 계산을 결정합니다.

SUM은 분명한 출발점이지만, AVERAGE, COUNT, MAX 등도 사용할 수 있습니다. 따라서 각 지역이 기록한 판매 수를 세는 방법은 다음과 같습니다:

=PIVOTBY(B2:B33, , E2:E33, COUNT)

컬럼-필드 슬롯을 비워두면 단일 컬럼 요약을 얻을 수 있습니다. 선택적 인수는 다듬기를 처리합니다: 데이터에 헤더가 있는지, 소계와 총계가 어느 정도까지 적용되는지, 각 축의 정렬 순서가 어떻게 되는지 등입니다. 저는 대부분을 잘 건드리지 않지만, 보고서에서 필요할 때 사용됩니다.

새로운 판매를 추가하는 순간 내 요약이 업데이트됩니다

잊을 새로 고침 단계가 남아 있지 않다

이 부분이 나를 설득한 부분입니다. 피벗 테이블은 스냅샷입니다. 원본 데이터에 행을 추가해도 새로 고침을 누르는 것을 기억할 때까지 요약은 변경되지 않습니다. 나는 그 단계를 건너뛰어서 오래된 합계가 포함된 보고서를 여러 번 배송한 적이 있습니다.

PIVOTBY는 수식이기 때문에 수식처럼 작동합니다. 참조 범위 안에 새 행을 추가하는 순간, 영향을 받는 모든 수치가 스스로 재계산됩니다. 시트에 새 동부 지역 전자제품 판매 내역을 입력하면, 내가 손가락 하나 까딱하지 않아도 동부 합계가 올라갑니다.

이를 완전히 밀폐되게 만들기 위해, 저는 소스를 행을 추가할 때 확장되는 Excel 표로 형식화하고, 수식을 고정된 범위 대신 표 열을 가리키도록 합니다:

=PIVOTBY(Sales[Region], Sales[Product Category], Sales[Sales Amount], SUM)

이제 행을 추가하면 범위가 자동으로 확장되고, 요약도 함께 증가합니다.

공정하게 말하면, 필드를 이동하거나 슬라이서를 추가하거나 데이터를 인터랙티브하게 탐색하고 싶을 때는 피벗 테이블이 여전히 우위에 있습니다. PIVOTBY는 그것을 대체하려는 것이 아닙니다. 다만 항상 최신 수치를 반영하는 빠른 요약을 원할 때는, 이 수식이 가장 먼저 도달합니다.

같은 뷰에서 평균을 쌓을 수 있습니다

집계 함수를 바꾸는 것이 핵심 요령이다

함수 인수는 PIVOTBY가 유용하게 쓰이는 부분입니다. 수식에서 하나의 슬롯만 수정하면 요약 보고서를 변경할 수 있기 때문에 전체를 다시 구축할 필요가 없습니다.

총합 대신 각 판매원의 평균 판매액을 원하나요? SUM을 AVERAGE로 바꾸세요:

=PIVOTBY(D2:D33, , F2:F33, AVERAGE)

그것은 데이비드 첸이 그의 다섯 건의 판매에서 건당 평균 약 4,122달러를 기록하고 있는 반면, 리사 브라운은 그녀의 판매에서 약 2,480달러 정도임을 알려줍니다. 개수, 최대값, 최소값도 같은 방식으로 작동합니다.

값 인수에 하나 이상의 열도 전달하여 동일한 흘러넘치는 테이블에서 총계와 평균을 나란히 표시할 수 있습니다. 결과는 한 줄의 텍스트로 만든 작은 대시보드처럼 읽히며, 모든 수치는 그 아래의 실시간 데이터와 연결된 상태를 유지합니다.

내가 PIVOTBY를 다음에 어디로 가져갈지

GROUPBY와 실시간 차트가 제 목록 다음입니다

나는 아직도 이 도구를 사용할 새로운 장소를 찾고 있어요. GROUPBY는 하나의 축만 필요한 요약에 다음으로 적용할 예정인데, 컬럼 차원이 없는 똑같은 아이디어이기 때문입니다. 그 다음에는 PIVOTBY 범위를 차트에 연결하고 새로운 판매가 발생할 때 시각적 요소가 다시 그려지는 것을 지켜보려 합니다. 수동으로 범위를 수정할 필요 없이요. 맞춤 LAMBDA 집계는 좀 더 먼 이야기이지만, 가능성은 열려 있습니다. 당분간 누군가 금요일까지 유효한 빠른 분석을 요청할 때, 한 가지 수식을 입력하는 것이 제 첫 번째 선택이 되었습니다.