Excel动态透视表--PIVOTBY函数初级到高级用法

<p><img src="data/attachment/forum/202601/09/095116xnkk43ysdlmsyymy.webp" alt="141d455481b03db.webp" title="PIVOTBY函数-Excel函数教程" /></p>
<p><code>PIVOTBY</code> 是 <strong>Excel 365/2021 及后续版本</strong>新增的<strong>动态数组函数</strong>，用于快速创建<strong>动态透视表</strong>，无需手动插入传统数据透视表，直接通过公式生成可自动溢出的透视结果，支持灵活的行列分组、聚合计算与筛选。相比传统透视表，<code>PIVOTBY</code> 更轻量化，可与其他函数嵌套使用。</p>
<h2>一、函数核心语法与参数说明</h2>
<h3>1. 完整语法</h3>
4 B+ B* d" N7 j<pre><code class="language-excel">PIVOTBY(rows, cols, values, [agg], [row_total], [col_total], [filter_array], [drop_empty])
! e9 Z. ?2 V& u( z; u/ d</code></pre>
) t9 y" |8 Z: \' L<h3>2. 参数逐义解析</h3>
<table>
<thead>
<tr>
<th>参数名</th>
9 |8 D5 X/ G$ `) W- z- h9 a<th>必选/可选</th>
<th>数据类型</th>
<th>核心作用</th>
2 j! _5 q2 f5 O<th>注意事项</th>
, i5 S; d7 W7 i) H# Q! T</tr>
</thead>
<tbody>
; i8 B# G- A$ \3 y<tr>
) Q9 b3 O8 j( l% Z<td><code>rows</code></td>
<td>必选</td>
<td>单元格区域/数组</td>
( Q+ o# ~6 {1 L/ d6 ?- ]<td><strong>行标签数据源</strong>：用于分组的行维度字段（可单字段/多字段）</td>
<td>多字段需用 <code>HSTACK</code> 合并，如 <code>HSTACK(部门, 季度)</code></td>
) E8 c' a( E3 S: n* e! @9 D2 h</tr>
% c' h' ?% n- D1 U- B1 `: j<tr>
<td><code>cols</code></td>
9 v$ {) _4 J! J+ n7 p<td>必选</td>
: ~. r+ n' u( I1 v3 K2 j7 |<td>单元格区域/数组</td>
- u0 P; S. j6 B! d+ p; m<td><strong>列标签数据源</strong>：用于分组的列维度字段（可单字段/多字段）</td>
+ R4 Y0 H# O. x1 {% _; K<td>多字段需用 <code>HSTACK</code> 合并，支持空值（<code>&quot;&quot;</code>）表示无列分组</td>
</tr>
8 {7 F* Z1 n( Z9 {+ N<tr>
# Q, z, z+ d6 h' l0 B) a<td><code>values</code></td>
<td>必选</td>
<td>单元格区域/数组</td>
! W( i+ @+ m" R<td><strong>值区域数据源</strong>：需要聚合计算的数值字段</td>
<td>区域行数必须与 <code>rows</code>/<code>cols</code> 完全一致</td>
3 ]8 A  J0 _/ V- }  ^# M</tr>
<tr>
<td><code>[agg]</code></td>
<td>可选</td>
) v: w/ v! a7 I0 N9 |* n; e9 z$ @<td>文本/函数</td>
7 Q, L: N2 b5 o. ?<td><strong>聚合方式</strong>：默认 <code>SUM</code>，支持内置函数或自定义 <code>LAMBDA</code> 函数</td>
<td>内置值：<code>SUM/COUNT/COUNTA/AVERAGE/MIN/MAX/MEDIAN</code> 等</td>
7 u- Z, d/ H" \0 v& t. A) A- I' @. }</tr>
, ~% h! O3 M9 [$ N<tr>
/ B: W" E; {, ^9 H6 m' J) w, E! A<td><code>[row_total]</code></td>
<td>可选</td>
$ E; r/ g2 ~$ D. X  X2 x1 I  d# z<td>逻辑值/文本</td>
3 v9 z  I( Z) E5 O) {" C<td><strong>行总计开关</strong>：是否添加行总计行</td>
5 r: R7 o! b# v% L  ^' h+ J: P& G<td><code>TRUE</code>=显示总计（默认名称“总计”）；<code>&quot;自定义名称&quot;</code>=自定义总计标题；<code>FALSE</code>=不显示</td>
</tr>
<tr>
<td><code>[col_total]</code></td>
<td>可选</td>
<td>逻辑值/文本</td>
  \$ L6 ]1 }, W  I1 V<td><strong>列总计开关</strong>：是否添加列总计列</td>
8 |+ b+ f+ c. M* t<td>规则同 <code>row_total</code></td>
</tr>
- @/ D0 ^8 }* y. U; B+ {<tr>
<td><code>[filter_array]</code></td>
<td>可选</td>
<td>布尔数组</td>
<td><strong>筛选条件</strong>：按行筛选数据源，仅保留 <code>TRUE</code> 对应的行</td>
9 F3 |6 T- [' R9 F7 Y6 P9 s& o& [<td>数组行数必须与数据源一致，可结合 <code>FILTER</code> 函数生成筛选条件</td>
' n* i' e5 X! |! |</tr>
8 r4 h  p" q4 r0 r<tr>
% b# c+ x# b: J$ x' R<td><code>[drop_empty]</code></td>
<td>可选</td>
4 P6 l) x' f; ?/ p. s4 y<td>逻辑值</td>
<td><strong>空值过滤</strong>：是否删除无数据的行/列</td>
4 {2 p3 v3 Z$ m& E9 h<td><code>TRUE</code>=删除空行列（默认）；<code>FALSE</code>=保留空行列</td>
</tr>
: U9 d- V) h% {7 s) v- d</tbody>
</table>
/ B& Z; R3 P  x9 F8 b2 @" l<h3>3. 关键特性</h3>
<ul>
<li><strong>动态溢出</strong>：公式输入后自动向下、向右溢出结果，无需手动拖拽填充。</li>
3 d, H; {7 `. G" u5 \<li><strong>自动更新</strong>：数据源变化时，结果实时刷新（需开启 Excel 自动计算）。</li>
; C1 c5 N# P: B6 _<li><strong>兼容性</strong>：仅支持 Excel 365/2021 及以上版本，低版本需用传统透视表替代。</li>
</ul>
<h2>二、数据源准备（通用示例数据）</h2>
<p>为统一示例，使用<strong>销售数据表</strong>作为基础数据源，共 6 行数据：</p>
$ S5 e3 h" M: k. o* L; _3 x<table>
<thead>
3 O: H* |( k$ v. w3 H<tr>
# k- F! o* i1 k8 b, o5 G# `4 E<th>单元格区域</th>
<th>A (部门)</th>
<th>B (季度)</th>
7 _6 `0 n7 G  i8 H" N<th>C (销售额)</th>
<th>D (利润)</th>
</tr>
</thead>
<tbody>
5 w* E9 @( p  L- c: d# ?: e& \' F<tr>
" F9 d/ W' X! y0 y! {<td>1</td>
<td>销售部</td>
<td>Q1</td>
& A( J+ ?: {' h' S$ s3 i<td>10000</td>
$ f2 h9 e/ ?6 o! U<td>2000</td>
, Z/ x  J! V! \0 _</tr>
<tr>
<td>2</td>
4 s- U, j% \$ q0 E<td>销售部</td>
) J# r5 S7 z& o* A<td>Q2</td>
<td>12000</td>
<td>2500</td>
</tr>
3 T, F+ ^+ s$ Q( e<tr>
<td>3</td>
. j2 x8 ~) g; c2 H) P! G<td>技术部</td>
( K' c  i$ E6 E<td>Q1</td>
<td>8000</td>
, r* X/ b3 i. D/ }! A5 Z4 h+ K; T<td>1500</td>
</tr>
<tr>
- l, P' ^1 r& T* q% Y2 f<td>4</td>
' z5 M7 U* F+ [4 I$ T) @- u<td>技术部</td>
: b6 ?+ @! ]% H<td>Q2</td>
<td>9000</td>
( |5 j7 R1 R# B+ S1 T. [<td>1800</td>
1 j. A/ U; W# P/ c: A. ^</tr>
<tr>
3 z2 v# V4 G5 v7 b" M/ t. F<td>5</td>
, x9 X5 z3 H5 }( `2 j<td>市场部</td>
<td>Q1</td>
2 _) H9 |4 x" |; Q" T+ {2 ]<td>5000</td>
<td>1000</td>
7 H% a9 P, @/ T$ \+ R</tr>
& ~; @0 H: V+ R6 Y<tr>
<td>6</td>
; V, V( S" _: T8 }) n$ b9 X( @<td>市场部</td>
<td>Q2</td>
) O6 l. x( @# Z6 C* F: Y1 \<td>2000</td>
<td>300</td>
% ?/ c: D# \0 P% g5 H: |</tr>
& V) f8 P2 J, T& ~* X</tbody>
</table>
<p>后续所有示例均基于此表，数据区域记为 <code>A1:D6</code>。</p>
* j+ N7 d7 O; u4 P) A. h$ M+ k<h2>三、初级用法：基础单维度透视</h2>
<h3>场景1：单一行标签 + 单一列标签 + 单值聚合</h3>
  c6 t! j! ~/ P2 u8 L<p><strong>需求</strong>：按「部门」分组（行），按「季度」分组（列），统计<strong>销售额总和</strong>。</p>
<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
</code></pre>
<h4>参数对应说明</h4>
<table>
0 _7 w8 g5 J% k7 ~+ p<thead>
: O  t) ?( f9 k9 k<tr>
/ N2 H: h4 q7 v! u- S# o& k<th>参数</th>
<th>取值</th>
7 O6 T. T- _% L2 F9 B" {$ m* ^& x* C<th>作用</th>
</tr>
: K8 ?, A' }# x, i</thead>
<tbody>
0 @; w" M1 P* ~$ ~: L" N+ u<tr>
6 u) X3 r7 i5 j6 l* s5 c6 [<td><code>rows</code></td>
<td><code>A2:A6</code></td>
$ M5 {) V. k) J  ]1 x<td>行标签：部门（销售部/技术部/市场部）</td>
</tr>
<tr>
<td><code>cols</code></td>
<td><code>B2:B6</code></td>
+ o9 P3 m9 I" Y' f4 Y<td>列标签：季度（Q1/Q2）</td>
. F4 _9 Z  ]& v) p& P) k</tr>
% n3 v! ^. W2 J* p; w4 ^<tr>
6 y4 R" a5 H& q6 i7 c; s$ G6 Z<td><code>values</code></td>
<td><code>C2:C6</code></td>
<td>值区域：销售额</td>
</tr>
<tr>
<td><code>[agg]</code></td>
<td><code>&quot;SUM&quot;</code></td>
<td>聚合方式：求和（默认可省略）</td>
4 s* h# W: N  z5 [/ r2 y+ Q) e% T$ l</tr>
# w$ x# l$ |( e$ ?1 t& R</tbody>
</table>
<h4>溢出结果</h4>
5 l+ E/ t9 l8 k) I<table>
<thead>
<tr>
<th></th>
<th>Q1</th>
<th>Q2</th>
9 a0 d* l% b) y% E; T</tr>
) F2 o) O0 R4 M8 m, g' {</thead>
<tbody>
* d; f8 R" t% i<tr>
<td>销售部</td>
<td>10000</td>
$ \; E% {6 {: G) L# i1 U! i$ E<td>12000</td>
, v. ?+ s2 U" {2 x</tr>
<tr>
<td>技术部</td>
3 r% A5 L6 m( X; g, q<td>8000</td>
<td>9000</td>
</tr>
<tr>
<td>市场部</td>
<td>5000</td>
<td>2000</td>
0 W5 ^. A. V* `. e" p</tr>
</tbody>
</table>
<h3>场景2：无列标签（仅行维度聚合）</h3>
<p><strong>需求</strong>：按「部门」分组，统计<strong>总销售额</strong>，不按列拆分。</p>
<h4>公式编写</h4>
* f" X+ {1 z7 e5 D<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6)
</code></pre>
<h4>关键参数：<code>cols=&quot;&quot;</code></h4>
<p>表示不设置列标签，结果仅输出「行标签+聚合值」两列。</p>
<h4>溢出结果</h4>
<table>
6 `, S, U3 m$ K" |2 U<thead>
0 s5 X1 W# j( v- C5 t0 s5 |<tr>
' b) s* B- A9 a& y3 m& C<th></th>
' G' P; U; X# v& I0 Z! Y2 L<th>销售额</th>
</tr>
</thead>
<tbody>
<tr>
, j# k, S! p$ L<td>销售部</td>
5 Z5 ]* ]$ r/ n  X9 `<td>22000</td>
- P1 A3 W9 N# `8 l2 U' D</tr>
% p1 c# N9 L: T* x" W: W4 f8 t<tr>
<td>技术部</td>
<td>17000</td>
. O- a2 Q; E2 @0 N9 `$ t# r! E/ |" I</tr>
6 a0 U6 I1 o- v5 V; b  L<tr>
<td>市场部</td>
<td>7000</td>
' V7 F! ?" m6 r7 f# K+ j0 X3 M</tr>
</tbody>
</table>
<h2>四、中级用法：多维度分组与总计配置</h2>
<h3>场景1：多行/多列标签 + 多值聚合</h3>
<p><strong>需求</strong>：按「部门+季度」双行标签，无列标签，同时统计<strong>销售额总和</strong>和<strong>利润总和</strong>。</p>
<h4>公式编写</h4>
. D8 T/ h" ^2 e+ ~. R, N( p) Z<pre><code class="language-excel">=PIVOTBY(HSTACK(A2:A6, B2:B6), &quot;&quot;, HSTACK(C2:C6, D2:D6), &quot;SUM&quot;)
</code></pre>
<h4>参数核心技巧</h4>
( i9 P! t7 H& f# D* x<ul>
/ C8 U" _7 b: e- e- l<li><code>rows</code> 用 <code>HSTACK(部门, 季度)</code> 实现<strong>多行标签</strong>；</li>
<li><code>values</code> 用 <code>HSTACK(销售额, 利润)</code> 实现<strong>多值聚合</strong>；</li>
0 V7 |# `1 F6 W' D* J. s7 x<li>聚合方式 <code>SUM</code> 同时作用于两个值字段。</li>
</ul>
/ x* n5 s8 ~. d<h4>溢出结果</h4>
6 I+ ]! s9 @- R4 \<table>
<thead>
$ _0 c- m* A7 X) M2 R% h<tr>
$ G3 U9 m9 v( A& u<th></th>
/ M2 M; f/ w: F5 K% ^# f' g<th></th>
<th>销售额</th>
6 w1 Q0 Z: C9 J7 _- d) Z<th>利润</th>
3 p" M( u9 ?4 `2 t/ J$ d8 c1 e  ^$ c  Y</tr>
# q8 x! c7 d/ O</thead>
<tbody>
% D- E0 D2 o. V6 V' B6 L<tr>
<td>销售部</td>
<td>Q1</td>
<td>10000</td>
6 f/ s+ v+ `" }' B: O! }, p# T% A<td>2000</td>
</tr>
. c+ m+ ?. t7 \" f5 x8 b. B<tr>
<td>销售部</td>
<td>Q2</td>
+ c' z/ Q, i3 Q<td>12000</td>
<td>2500</td>
4 ]0 v2 K9 A' z2 S</tr>
8 Q, v5 \, ~' V<tr>
& L4 ~+ k/ _1 D<td>技术部</td>
/ m) c# {3 r8 \$ O% g1 z5 W$ J; f<td>Q1</td>
, b: f3 k* J; R4 T6 C<td>8000</td>
<td>1500</td>
/ U' ^) c, l& W</tr>
<tr>
0 n% y8 D# A3 h! Y! ?<td>技术部</td>
# m5 U3 C& ?# T) |1 Z7 n<td>Q2</td>
<td>9000</td>
<td>1800</td>
</tr>
<tr>
: ^  x' r5 S9 W9 z3 U$ u) k<td>市场部</td>
<td>Q1</td>
<td>5000</td>
<td>1000</td>
% d; b# M. T7 H1 r4 f2 _</tr>
; d9 n2 p6 N* n0 E8 _<tr>
% e) M2 X7 L' B8 @4 e3 f<td>市场部</td>
<td>Q2</td>
<td>2000</td>
4 G0 C2 M( [# \/ e<td>300</td>
( _7 {/ S# y, |- a& T9 ~3 G. c</tr>
</tbody>
) H/ `6 j: j$ i: X! W  B3 a- ?</table>
& V- {5 o8 M9 j2 u% L<h3>场景2：添加行/列总计</h3>
5 @3 q: ?& ]& O) r3 o# ~* x<p><strong>需求</strong>：按「部门」行标签、「季度」列标签统计销售额，同时显示<strong>行总计</strong>和<strong>列总计</strong>，自定义总计名称为「合计」。</p>
% {: U( p6 A7 |2 i6 B, E6 [% @<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, &quot;合计&quot;, &quot;合计&quot;)
</code></pre>
4 G4 A8 r9 n: S3 f3 q. J. I<h4>参数说明</h4>
2 u% H9 w* n" Z, {$ \. R( p% l<ul>
: P. `1 A7 O2 z- _4 ?+ X2 n<li><code>row_total=&quot;合计&quot;</code>：行总计标题为「合计」；</li>
<li><code>col_total=&quot;合计&quot;</code>：列总计标题为「合计」。</li>
</ul>
. G* B! N: d* v<h4>溢出结果</h4>
) j8 l9 m0 c# b1 e<table>
# ^# w9 P; e! B/ g( x" @& U# F<thead>
& h6 ?: K) `: }3 p3 F3 d1 Z<tr>
<th></th>
* O1 Y+ a6 k5 A$ D- A4 @  w& g( D<th>Q1</th>
4 [9 W$ A9 }8 }, w2 v9 |: I<th>Q2</th>
<th>合计</th>
</tr>
</thead>
! t- x+ }7 `. |( Q8 a0 ^<tbody>
5 N8 j, i" e, T; B' `& V' ]<tr>
8 {5 e9 l8 M8 g" G" s: ^<td>销售部</td>
2 D# q  N$ \* U* a<td>10000</td>
! i- U/ r( ]' n, Q6 Z) Y- d. j<td>12000</td>
1 y8 V) h1 F# d& H4 ?5 U6 n3 |<td>22000</td>
7 \+ M" r% B( S  ?! {% g</tr>
<tr>
$ K% ~" ~7 l1 h1 L: j+ |# q8 v<td>技术部</td>
( a( E5 ^5 d0 X6 P+ R0 o9 e7 K<td>8000</td>
<td>9000</td>
, E! i) F. Y% f) E/ t<td>17000</td>
</tr>
<tr>
3 d* o, Z  {$ T<td>市场部</td>
' y$ @; Y; n7 ?: Z4 D. L<td>5000</td>
<td>2000</td>
  v0 R9 D! K5 H2 V" J/ D2 O<td>7000</td>
</tr>
<tr>
% Z: w8 G2 t! o5 H0 g<td>合计</td>
/ g; a, a( C* {6 l/ @/ C# ]9 W<td>23000</td>
' g! f/ z3 U' B8 Z<td>23000</td>
<td>46000</td>
0 _( n$ ~1 U1 }4 L</tr>
</tbody>
</table>
<h3>场景3：保留空行列（<code>drop_empty=FALSE</code>）</h3>
<p><strong>需求</strong>：若数据源存在无数据的分组（如“市场部 Q3”），保留空行显示。</p>
# G0 v) Q( c; I& u# O6 u! ^# _<h4>扩展数据源</h4>
<p>在原数据后添加一行：<code>A7=市场部, B7=Q3, C7=&quot;&quot;</code>（销售额为空）。</p>
3 ]6 T8 a1 L2 _/ L5 H0 ]<h4>公式编写</h4>
- Q6 h% k9 H, a<pre><code class="language-excel">=PIVOTBY(A2:A7, B2:B7, C2:C7, &quot;SUM&quot;, , , , FALSE)
</code></pre>
# G7 }- a7 A7 Q" J+ X: d<h4>关键参数：<code>drop_empty=FALSE</code></h4>
5 e- D, b1 s3 C4 A/ k1 @# ~<p>强制保留无数据的行列，空值显示为 <code>0</code>。</p>
* [- t  P! W2 q' v9 }9 ]<h4>溢出结果（新增Q3列）</h4>
: R5 y9 R! s! e6 d3 d( p<table>
9 ^7 D2 c( P5 p9 W<thead>
<tr>
& R! Z2 _% p7 N. Z' ?$ j4 Y<th></th>
2 f8 k7 T4 ^4 p7 a. @  F# r<th>Q1</th>
# {- |6 @& g! O  p) P<th>Q2</th>
* Z8 s% L& p: ^<th>Q3</th>
</tr>
</thead>
<tbody>
<tr>
<td>销售部</td>
) S/ S' |/ G( k) C8 R<td>10000</td>
" \, D$ S# R, q, N0 V  H4 M<td>12000</td>
<td>0</td>
5 j2 t" G( [2 [4 h</tr>
<tr>
  J6 i% A; y; L, O<td>技术部</td>
9 A8 y2 ]4 Q/ F! r" a1 P8 w; j2 [' }<td>8000</td>
  b$ A7 r' @2 @( ]<td>9000</td>
9 c$ M& t5 Q! z' y0 V( Z* ^* ^* |<td>0</td>
0 g) v2 E9 a+ v  _</tr>
5 e/ J. ^1 v, d, {$ M" g" E2 c" F<tr>
<td>市场部</td>
7 h# Y9 P2 l" n<td>5000</td>
<td>2000</td>
$ Q4 F8 U$ b: m- T<td>0</td>
, H' s! b, j# b# {</tr>
* A! q# h7 t  D, x3 x% g</tbody>
- S6 O% @* N+ O</table>
<h2>五、高级用法：筛选、自定义聚合与函数嵌套</h2>
* X  R* ?/ C! }* X8 c<h3>场景1：结合筛选条件（<code>filter_array</code>）</h3>
<p><strong>需求</strong>：仅统计<strong>销售额&gt;6000</strong>的记录，按部门和季度透视销售额总和。</p>
; w% J/ d2 \/ U6 d8 V" ~$ }  d<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, , , C2:C6&gt;6000)
& ]" X" O  H/ T; q9 @</code></pre>
<h4>参数核心：<code>filter_array=C2:C6&gt;6000</code></h4>
% S- `" _& f9 ~; i3 m9 E<ul>
<li>生成布尔数组：<code>{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}</code>；</li>
<li>仅保留数组中 <code>TRUE</code> 对应的行（即销售额&gt;6000的记录）。</li>
& T% `- V2 z8 h  r</ul>
<h4>筛选后数据源（实际参与计算的行）</h4>
<table>
<thead>
<tr>
/ Q# H0 R1 C/ v& k<th>部门</th>
<th>季度</th>
<th>销售额</th>
0 A% z2 z# e& ~& r! ~; I</tr>
</thead>
<tbody>
* N8 C0 ~7 O$ y<tr>
" Y6 W4 P! w9 B<td>销售部</td>
3 p5 ^3 {9 ^6 i, P8 D& {<td>Q1</td>
<td>10000</td>
& x- E- T% R( f- v8 y</tr>
' ~8 s! ?1 Y. _9 G# Y; ]$ l<tr>
) f% P2 z& Q$ }5 R8 g<td>销售部</td>
<td>Q2</td>
<td>12000</td>
</tr>
$ k. t% _7 c: o0 r<tr>
<td>技术部</td>
<td>Q1</td>
<td>8000</td>
</tr>
<tr>
<td>技术部</td>
2 e# q1 j8 i+ f8 d5 D3 H% r<td>Q2</td>
5 n. H3 B3 }, A# t5 H1 p: S<td>9000</td>
</tr>
, b* f# Z, K5 `7 @- K! D</tbody>
7 n4 A7 X/ i- C3 v5 A& z( x/ b, F( ]</table>
9 i7 x$ Y# X/ r, a' c<h4>最终溢出结果</h4>
<table>
5 Z! Y9 ?, B- x* R<thead>
  Y. i, V$ y3 A- N2 W( ]3 b<tr>
<th></th>
8 C  N. h8 n, _6 u" f<th>Q1</th>
<th>Q2</th>
</tr>
</thead>
<tbody>
% A& A! y/ \. x+ F8 D<tr>
<td>销售部</td>
<td>10000</td>
4 P9 N5 S3 v8 O* Z) Y<td>12000</td>
</tr>
<tr>
<td>技术部</td>
6 x7 m" t4 t. a( e" x" B" V% Y<td>8000</td>
5 p! q' x0 m' d+ y7 z+ {<td>9000</td>
, q5 O' O( S  O( l8 l/ Q. k) ]</tr>
& K! q/ k8 [" J7 ]# t) [7 U# D</tbody>
</table>
: Y% K  |! l0 I# W<h3>场景2：自定义聚合函数（<code>LAMBDA</code> 实现非内置计算）</h3>
) ~9 k9 b0 A' @$ h: e2 ?<p><strong>需求</strong>：按部门分组，计算<strong>销售额的标准差</strong>（内置聚合无此函数，需自定义）。</p>
<h4>公式编写</h4>
8 |+ t' z2 {" k) l<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6, LAMBDA(x, STDEV.S(x)))
</code></pre>
<h4>参数核心：<code>agg=LAMBDA(x, STDEV.S(x))</code></h4>
5 @2 ?/ C, w( g: S: `<ul>
<li><code>x</code> 代表每个分组的数值集合（如销售部的 <code>{10000,12000}</code>）；</li>
5 M( L6 G3 Z2 G) L<li><code>STDEV.S(x)</code> 计算样本标准差，可替换为任意自定义逻辑（如 <code>MAX(x)-MIN(x)</code> 计算极差）。</li>
1 I% s* m- q; O* _2 p* ?</ul>
<h4>溢出结果</h4>
) N0 P0 Q9 J5 T- p6 E( M, B4 R<table>
) E1 e( J. m  c% b) Z: \8 ^<thead>
<tr>
9 Q0 |  q3 t4 A3 U0 P<th></th>
4 w6 L$ P: r4 W5 X6 ~. v6 |<th>销售额标准差</th>
</tr>
</thead>
<tbody>
<tr>
. u% H% w9 H! t. K2 U<td>销售部</td>
) M" e  B( k# m: C: z<td>1414.21</td>
</tr>
/ T3 T. A$ j7 h' X. S: d<tr>
<td>技术部</td>
<td>707.11</td>
: j, M, U2 X- E; N) t* W& B* J# v</tr>
<tr>
8 f: ~- x1 l, d: f9 D1 B<td>市场部</td>
" ]. D0 @" S5 P+ K$ T# m; ?( I' w<td>2121.32</td>
0 x) C* u1 K8 R5 ]</tr>
3 s3 J) O; b* e$ p$ u; m7 h</tbody>
! J! s4 ~7 Q& c/ G/ J' u8 F- t</table>
1 S8 {3 Q, o/ Y0 }<h3>场景3：与 <code>SORT</code> 嵌套，对透视结果排序</h3>
<p><strong>需求</strong>：按部门透视总销售额，并按销售额<strong>降序排序</strong>。</p>
<h4>公式编写</h4>
5 B5 r( @6 u; |) f2 ?<pre><code class="language-excel">=SORT(PIVOTBY(A2:A6, &quot;&quot;, C2:C6), 2, -1)
</code></pre>
, N5 f7 \5 G8 J0 t  W7 @& i* l<h4>嵌套逻辑</h4>
  G0 _- a4 @3 ^, ~) ?) _<ol>
9 D# V1 H1 N4 [<li>先用 <code>PIVOTBY</code> 生成基础透视结果（2列：部门+销售额）；</li>
$ k  f- ~6 i/ V0 W5 j* W7 A2 [: M<li>再用 <code>SORT</code> 函数按<strong>第2列（销售额）</strong> 降序（<code>-1</code>）排序。</li>
3 c8 t' ~) B- n4 h8 A</ol>
<h4>排序后结果</h4>
7 w& G( M  C# ?9 l<table>
<thead>
<tr>
<th></th>
' M/ A% `9 o8 a6 O, p- _- ~* t<th>销售额</th>
4 n3 v: {5 {9 ^/ P</tr>
</thead>
<tbody>
<tr>
<td>销售部</td>
6 n2 L' @6 F9 y<td>22000</td>
</tr>
* \$ k5 a9 H) v8 a5 V<tr>
* K: \: P) \1 T* J3 }- d<td>技术部</td>
( b4 Q3 p0 m/ J& r6 _<td>17000</td>
- d$ k# m3 ]+ m1 i$ P; c- n</tr>
6 Q' N4 ^& x! b& Q<tr>
<td>市场部</td>
<td>7000</td>
8 M0 W$ [9 |: }% F" W# B0 @8 u; X+ ~, J</tr>
) C: s! f" {; t- i- U9 t</tbody>
</table>
9 H6 u' U5 |# o0 \<h2>六、常见问题与注意事项</h2>
' r2 J' T/ ?% S  `% s/ }) D9 @<ol>
0 X% ~& b5 J7 y" E  g5 T  H<li>
<p><strong>溢出错误 <code>#SPILL!</code></strong></p>
<ul>
- Q! v  C& ^3 @3 D3 ~<li>原因：结果区域被其他单元格内容阻挡；<code>rows</code>/<code>cols</code>/<code>values</code> 行数不一致。</li>
<li>解决：清空结果区域的空单元格；检查数据源区域行数是否匹配。</li>
" u! z6 {/ J1 |$ {# g7 f</ul>
) w5 ^' i3 N- J7 n( O, f. t</li>
<li>
* p1 b, x+ R& t# ^2 z) \<p><strong>聚合函数不生效</strong></p>
<ul>
<li>原因：<code>agg</code> 参数拼写错误（如 <code>&quot;Sum&quot;</code> 写成 <code>&quot;sum&quot;</code> 不影响，但需避免错别字）；自定义 <code>LAMBDA</code> 函数语法错误。</li>
7 J" K- e; }8 b- d* l* j- }<li>解决：核对内置聚合函数名称；调试 <code>LAMBDA</code> 函数逻辑。</li>
/ n! [! U7 g6 K</ul>
3 Z8 B0 o( v9 ~, o</li>
<li>
$ ^8 C# }2 p  F- M. Q<p><strong>多字段分组顺序问题</strong></p>
<ul>
<li>技巧：<code>HSTACK(字段1, 字段2)</code> 中，字段1是<strong>外层分组</strong>，字段2是<strong>内层分组</strong>，顺序决定透视结果的层级。</li>
</ul>
/ Q6 `7 Z! s6 E7 \& R</li>
<li>
' ]) h4 |& E. h: O- _4 X1 R1 G<p><strong>性能优化</strong></p>
<ul>
<li>当数据源超过1万行时，避免使用复杂自定义聚合；</li>
) r' t2 c* H, f* o9 Y* ^4 p- ?2 X! F<li>先用 <code>FILTER</code> 筛选目标数据，再传入 <code>PIVOTBY</code>，减少计算量。</li>
</ul>
</li>
</ol>
" V9 ]2 @. Q5 s  @7 @4 E

<p>在 Excel 中，<code>PIVOTBY</code> 函数的结果被称为 <strong>溢出结果</strong>，核心原因是它属于 <strong>动态数组函数</strong>，返回的不是单个值，而是一个<strong>多单元格的二维数组</strong>，会自动填充到公式单元格周围的空白单元格中——就像水从一个中心点“溢”向四周一样，因此得名。</p>
<h3>一、“溢出”的本质：动态数组 vs 传统函数</h3>
" K, |# L$ F/ j<h4>1. 传统函数的局限</h4>
' ?2 f% y! R- n8 d<p>传统 Excel 函数（如 <code>SUM</code>/<code>VLOOKUP</code>）<strong>只能返回单个值</strong>，输入公式后仅在当前单元格显示结果。<br />
如果公式逻辑需要返回多个值（比如同时统计多个部门的销售额），传统函数会直接报错。</p>
6 r3 ]0 K$ v- ?/ \# o" @<h4>2. 动态数组函数的“溢出”能力</h4>
<p><code>PIVOTBY</code> 是 Excel 365 新增的<strong>动态数组函数</strong>，其核心特性是：</p>
<ul>
/ k2 q0 F0 m1 E7 G  k<li>计算结果是一个 <strong>二维数组</strong>（对应透视表的行和列）；</li>
<li>公式仅需输入在<strong>单个单元格</strong>中，Excel 会自动将数组内容“溢出”到<strong>右侧和下方</strong>的空白单元格，形成完整的透视结果；</li>
<li>溢出区域的大小由数据源的分组数量决定（比如 3 个部门、2 个季度，就会溢出成 3 行 × 2 列的区域）。</li>
* {- c/ q, }/ `: o2 }# y1 w</ul>
<h3>二、结合 PIVOTBY 实例理解溢出</h3>
. [6 O' {/ J' j0 G* f<p>以之前的基础示例为例：</p>
( e: Y& P7 T( K2 }" ^2 r9 X& ~4 n<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
0 L& S4 Q; U& B' N" b& v/ U8 `</code></pre>
<p>假设公式输入在 <strong>E1 单元格</strong>：</p>
<ol>
<li>函数计算后生成一个 <code>3行×2列</code> 的二维数组：
<pre><code>{10000, 12000; 8000, 9000; 5000, 2000}
% F: \# U" }$ O* B</code></pre>
2 M0 O' z8 a. ]8 L7 g) _2 w3 n</li>
. o, Z0 U- I2 a7 l# p8 S  e<li>数组会从 E1 开始<strong>向右、向下溢出</strong>，自动填充到 <code>E1:G3</code> 区域：<table>
5 m# w) p) y, n; _<thead>
<tr>
, O  m1 K& P3 y4 [& v* I7 n<th>单元格</th>
<th>内容</th>
<th>对应透视表位置</th>
+ P$ [7 c7 R+ [</tr>
0 ^. z  _8 V1 p+ W/ S</thead>
<tbody>
; z9 }9 X6 T' x( b# [<tr>
<td>E1</td>
<td>10000</td>
<td>销售部-Q1</td>
5 T2 @. }) Q) X  N</tr>
<tr>
<td>F1</td>
<td>12000</td>
<td>销售部-Q2</td>
/ x0 r9 }( `  `, }</tr>
<tr>
<td>E2</td>
<td>8000</td>
<td>技术部-Q1</td>
# u+ Y+ L% L1 ~</tr>
<tr>
<td>F2</td>
6 ]3 a4 g# V& k5 d<td>9000</td>
6 [# p4 g& I( L- I. {<td>技术部-Q2</td>
5 ]2 C5 F: {0 v* M+ S4 D2 b/ S</tr>
<tr>
<td>E3</td>
<td>5000</td>
1 i# \  n, S7 `0 S+ Q" ^4 n<td>市场部-Q1</td>
1 a! F: J7 \: c* z2 V</tr>
<tr>
<td>F3</td>
2 J' ~. N9 m; o* i% x: ]1 k<td>2000</td>
/ L. I3 V9 t/ S2 n" M% Q/ k<td>市场部-Q2</td>
</tr>
2 H: e1 d+ A0 F7 @$ M3 _* U& ~</tbody>
</table>
2 i4 |) f3 s& `% C5 \</li>
</ol>
<blockquote>
<p>补充：Excel 会自动为溢出区域添加<strong>边框标识</strong>（浅灰色虚线），直观区分溢出结果和其他单元格。</p>
/ |. A8 R9 Z  f</blockquote>
6 n5 u* {! r" G  U/ n) p<h3>三、溢出结果的关键特性</h3>
<ol>
3 @1 M% G2 `9 {7 D<li>
<p><strong>自动适配大小</strong><br />
当数据源的分组数量变化时（比如新增“Q3”季度、“行政部”），溢出区域会<strong>自动扩展/收缩</strong>，无需手动调整公式或单元格范围。</p>
1 |$ R6 F: F. p; A4 a; d</li>
6 D5 k! r& ]9 V1 S/ i<li>
* R8 z, x( [" k<p><strong>联动更新</strong><br />
1 ?7 y+ x0 `3 |8 Z# `! B" o7 _/ P数据源的值发生变化时，溢出区域的结果会<strong>实时自动更新</strong>（前提是 Excel 开启“自动计算”模式）。</p>
4 t% K! G9 O0 M& x+ P</li>
/ {. b" u; y; T! [<li>
<p><strong>不可手动修改</strong><br />
# M! \2 ?& ?& l) G8 B  s( f+ d溢出区域的单元格是<strong>公式的一部分</strong>，无法单独编辑。如果手动修改其中某个单元格，会触发 <code>#SPILL!</code> 错误，提示“溢出区域被阻挡或修改”。</p>
</li>
<li>
<p><strong>支持嵌套与引用</strong><br />
6 ?' E0 Z6 B$ ~( a, S# k/ t可以将溢出结果作为其他函数的参数（比如用 <code>SORT</code> 排序、<code>INDEX</code> 提取特定值），例如：</p>
1 s* c. f; Z8 c8 c4 [2 A<pre><code class="language-excel">=INDEX(PIVOTBY(A2:A6, B2:B6, C2:C6), 1, 2)
</code></pre>
( A0 z) f1 f. F( L$ w1 M( J/ L<p>公式会直接提取“销售部-Q2”的销售额（结果为 12000）。</p>
</li>
/ a1 L& @8 u9 w, l" ]</ol>
<h3>因此</h3>
<p><code>PIVOTBY</code> 的“溢出结果”本质是<strong>动态数组的自动填充行为</strong>，它彻底摆脱了传统透视表需要手动插入、调整字段的繁琐操作，实现了“公式化创建动态透视表”的目标——这也是动态数组函数相比传统功能的核心优势。</p>