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>
. r# D7 k  c8 _. ~  f# ?! }<h2>一、函数核心语法与参数说明</h2>
<h3>1. 完整语法</h3>
<pre><code class="language-excel">PIVOTBY(rows, cols, values, [agg], [row_total], [col_total], [filter_array], [drop_empty])
* v- ]4 C5 w5 \7 |' i) S</code></pre>
' {9 y+ v% }7 w! w<h3>2. 参数逐义解析</h3>
/ e. R/ K2 d! J8 E* e  P2 z. D; F<table>
<thead>
  i0 |" p  d! F+ w* s0 v: g8 W5 ?<tr>
# |2 d  Z, z. n2 G/ l) ]<th>参数名</th>
/ i1 M. ?' j) y) i( d6 {<th>必选/可选</th>
<th>数据类型</th>
. R% S+ O7 f1 U; ?6 m5 O4 _$ o<th>核心作用</th>
* H- s2 y- m. X' x0 _* G8 Q<th>注意事项</th>
</tr>
</thead>
# ]; V3 a' [) o1 v. G7 S( v<tbody>
<tr>
<td><code>rows</code></td>
<td>必选</td>
  J) ?( E, J- X6 h<td>单元格区域/数组</td>
6 M9 U2 @+ S* }4 b# n5 a<td><strong>行标签数据源</strong>：用于分组的行维度字段（可单字段/多字段）</td>
<td>多字段需用 <code>HSTACK</code> 合并，如 <code>HSTACK(部门, 季度)</code></td>
  ~) ~2 u% w$ l  d0 }7 n/ K- R</tr>
<tr>
<td><code>cols</code></td>
9 k1 v1 S/ F8 O0 _3 r; `7 m( f<td>必选</td>
1 j# X" `$ D* k( I2 @<td>单元格区域/数组</td>
! S' z: s0 x! K% e* z/ Y<td><strong>列标签数据源</strong>：用于分组的列维度字段（可单字段/多字段）</td>
1 f: R6 Y9 {; _* o<td>多字段需用 <code>HSTACK</code> 合并，支持空值（<code>&quot;&quot;</code>）表示无列分组</td>
</tr>
: W3 q8 D% w; L, t& J<tr>
* o3 N. Q) n( P2 V# d2 m7 S! `<td><code>values</code></td>
# [$ Y8 L( J7 i# [<td>必选</td>
& F/ m; B1 l  u; A<td>单元格区域/数组</td>
<td><strong>值区域数据源</strong>：需要聚合计算的数值字段</td>
<td>区域行数必须与 <code>rows</code>/<code>cols</code> 完全一致</td>
, z! v/ |. Y1 Y- [/ j! K8 p</tr>
: K7 {. x3 A9 d5 ^<tr>
8 P: E/ x, q) W+ b7 w+ e9 V& C<td><code>[agg]</code></td>
+ s" T( l: g0 N; G4 `) I1 j! |$ Q- f<td>可选</td>
<td>文本/函数</td>
<td><strong>聚合方式</strong>：默认 <code>SUM</code>，支持内置函数或自定义 <code>LAMBDA</code> 函数</td>
5 f4 ^, A6 }' o; q% |" I- c7 S<td>内置值：<code>SUM/COUNT/COUNTA/AVERAGE/MIN/MAX/MEDIAN</code> 等</td>
</tr>
<tr>
<td><code>[row_total]</code></td>
! h9 X, D: f. k1 j; `! K% v; n0 U5 X4 u<td>可选</td>
<td>逻辑值/文本</td>
<td><strong>行总计开关</strong>：是否添加行总计行</td>
+ R' h: w; t8 d7 }$ [<td><code>TRUE</code>=显示总计（默认名称“总计”）；<code>&quot;自定义名称&quot;</code>=自定义总计标题；<code>FALSE</code>=不显示</td>
</tr>
<tr>
<td><code>[col_total]</code></td>
<td>可选</td>
2 N- J0 Y- p/ Z0 g<td>逻辑值/文本</td>
<td><strong>列总计开关</strong>：是否添加列总计列</td>
) d5 Q% ~/ ^% @2 d" V" [<td>规则同 <code>row_total</code></td>
</tr>
# M3 t' ^1 v) a<tr>
<td><code>[filter_array]</code></td>
<td>可选</td>
<td>布尔数组</td>
<td><strong>筛选条件</strong>：按行筛选数据源，仅保留 <code>TRUE</code> 对应的行</td>
<td>数组行数必须与数据源一致，可结合 <code>FILTER</code> 函数生成筛选条件</td>
  }% m# m7 J- K- t, S* t# x</tr>
<tr>
<td><code>[drop_empty]</code></td>
<td>可选</td>
1 f: q0 g0 C* E, {2 a: }<td>逻辑值</td>
( ]" Z* J/ ]7 Y5 H; @9 M8 ]2 _' f<td><strong>空值过滤</strong>：是否删除无数据的行/列</td>
& |7 Q6 K8 k% z7 i2 y3 m/ r<td><code>TRUE</code>=删除空行列（默认）；<code>FALSE</code>=保留空行列</td>
$ \# Z6 P- b- M0 z</tr>
* n6 \& o) [6 Z</tbody>
</table>
# c( Q+ c/ O  k- u) B<h3>3. 关键特性</h3>
<ul>
* _8 s. a' g! [. a/ D7 m. N: G* K<li><strong>动态溢出</strong>：公式输入后自动向下、向右溢出结果，无需手动拖拽填充。</li>
; y8 H; a9 H+ S$ J1 T1 g<li><strong>自动更新</strong>：数据源变化时，结果实时刷新（需开启 Excel 自动计算）。</li>
% Y8 [6 O3 ]3 _) V9 a7 }<li><strong>兼容性</strong>：仅支持 Excel 365/2021 及以上版本，低版本需用传统透视表替代。</li>
9 a. {' o& e% F</ul>
<h2>二、数据源准备（通用示例数据）</h2>
$ d! P, A4 w& B8 N5 b<p>为统一示例，使用<strong>销售数据表</strong>作为基础数据源，共 6 行数据：</p>
<table>
<thead>
" r$ |; q% C9 f<tr>
) H: t, T& D+ J2 @* M) J7 z3 z<th>单元格区域</th>
8 q. c* I! W/ a5 u+ x" f* S: }# E# \<th>A (部门)</th>
. w- \: F1 W2 t; j! m0 w2 @" O% h<th>B (季度)</th>
( }: `$ A1 a! c<th>C (销售额)</th>
  J* ?9 c; [7 Z<th>D (利润)</th>
! M/ s8 v: r, m* h5 o' M4 c</tr>
</thead>
$ \+ s7 U/ c, {0 Q6 H( z4 o  i<tbody>
% M2 f/ N; N1 ]<tr>
<td>1</td>
$ t5 X* Z% A6 P$ Q; A6 o% W1 p<td>销售部</td>
8 |' a! Z1 W+ Q) B2 e* r$ e<td>Q1</td>
<td>10000</td>
<td>2000</td>
  p, V7 ~; \  I! Y</tr>
<tr>
- O% }7 B! s! \/ A' I9 |5 e<td>2</td>
& }- y2 |1 o% }<td>销售部</td>
. ^2 V. k9 C0 j3 K- |. v<td>Q2</td>
<td>12000</td>
5 V7 c) h& b0 l( s- W<td>2500</td>
) N5 e3 \# c& w  H2 v</tr>
7 S% G* l, {: _! n/ T/ G9 y<tr>
5 u: Y0 ~- J# Y" e<td>3</td>
! ]1 y$ ]" q1 g5 l( G; J( g& B<td>技术部</td>
<td>Q1</td>
6 T) J9 o- B0 n<td>8000</td>
  R. |( B) n3 P/ ?1 w+ L<td>1500</td>
</tr>
# o) i* ^" I% t# V<tr>
<td>4</td>
<td>技术部</td>
4 _! a( k: V+ i3 t5 {<td>Q2</td>
0 F5 v* F; Y. L4 E( p<td>9000</td>
<td>1800</td>
</tr>
<tr>
<td>5</td>
<td>市场部</td>
<td>Q1</td>
<td>5000</td>
5 P. ]! f; {& K6 t<td>1000</td>
</tr>
<tr>
<td>6</td>
/ R( a7 n. i7 A( L<td>市场部</td>
<td>Q2</td>
<td>2000</td>
! V! V* N: i' v<td>300</td>
! j7 e8 x6 E- i" \2 f</tr>
% a* b* S; Q$ a& _' l</tbody>
</table>
<p>后续所有示例均基于此表，数据区域记为 <code>A1:D6</code>。</p>
2 X6 z- ?4 Y: c. W<h2>三、初级用法：基础单维度透视</h2>
<h3>场景1：单一行标签 + 单一列标签 + 单值聚合</h3>
7 i+ D* t2 d& W( M- M+ B. O! ?<p><strong>需求</strong>：按「部门」分组（行），按「季度」分组（列），统计<strong>销售额总和</strong>。</p>
<h4>公式编写</h4>
2 \) w0 u' |) L<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
- m9 c0 w5 o% E  a$ B</code></pre>
# \$ t$ o8 Z! _3 Q4 I1 j<h4>参数对应说明</h4>
<table>
7 s0 w# k* G( s' _<thead>
2 |$ r- G& M1 k7 x<tr>
" n5 L0 m6 A8 w; a0 I<th>参数</th>
! w7 }+ B( q' _  g7 g2 {. ~<th>取值</th>
/ e% p5 o4 d; J2 \, a<th>作用</th>
</tr>
</thead>
* J- a4 X6 v; p<tbody>
<tr>
<td><code>rows</code></td>
<td><code>A2:A6</code></td>
<td>行标签：部门（销售部/技术部/市场部）</td>
" E& A: q8 E+ G. \3 w1 i: ?0 o6 e</tr>
<tr>
<td><code>cols</code></td>
<td><code>B2:B6</code></td>
) u# u2 r! r, \<td>列标签：季度（Q1/Q2）</td>
+ L& P8 m7 k7 v( r</tr>
<tr>
<td><code>values</code></td>
<td><code>C2:C6</code></td>
: F4 w1 o& p# M7 t, _! A: @6 \2 e<td>值区域：销售额</td>
/ g  ^. T# z9 m# g9 R# p; I</tr>
; f: e8 ~) ~: ?& A- X8 R<tr>
0 G1 a  c; ?$ }9 C" j# S  v$ Y<td><code>[agg]</code></td>
<td><code>&quot;SUM&quot;</code></td>
<td>聚合方式：求和（默认可省略）</td>
8 A* v/ a- I' y$ O) g6 R</tr>
: {  o. Y  m" h+ ~/ U( _7 q</tbody>
</table>
<h4>溢出结果</h4>
<table>
6 C9 T, }0 A7 ^/ R4 E<thead>
<tr>
* q8 ]% o4 j( x3 f8 r8 l<th></th>
<th>Q1</th>
<th>Q2</th>
1 }" P# O/ c5 h9 X& Q) K% h0 H0 u</tr>
. R) S5 ~6 t3 y$ x0 @, Z! l</thead>
9 ^- X( z7 T& K0 k5 n! }/ N6 @<tbody>
<tr>
<td>销售部</td>
<td>10000</td>
<td>12000</td>
7 G7 i( D  D7 \; y</tr>
<tr>
<td>技术部</td>
<td>8000</td>
0 P6 @- x" ]+ E8 v<td>9000</td>
% g/ c, e" j5 p</tr>
<tr>
<td>市场部</td>
5 C7 f/ I4 H, L+ A& G<td>5000</td>
0 Z9 h& b( N7 `$ z<td>2000</td>
1 j0 |6 J% b# c& {( W. a</tr>
. o2 w* i9 o% N; y* B. \0 D</tbody>
</table>
<h3>场景2：无列标签（仅行维度聚合）</h3>
<p><strong>需求</strong>：按「部门」分组，统计<strong>总销售额</strong>，不按列拆分。</p>
: q8 a, d5 J1 F7 X<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6)
( [& e0 j/ `. @( I2 W</code></pre>
& t. ?9 f  i* S: `9 \' F<h4>关键参数：<code>cols=&quot;&quot;</code></h4>
! s0 d% t" y& [) W( B4 q<p>表示不设置列标签，结果仅输出「行标签+聚合值」两列。</p>
<h4>溢出结果</h4>
<table>
<thead>
<tr>
<th></th>
<th>销售额</th>
. x8 S( F, K: n4 _7 G</tr>
</thead>
- Z) P9 O6 z2 t<tbody>
<tr>
<td>销售部</td>
# L9 ^' g8 f  a9 q# o: X& V<td>22000</td>
: t% [7 b$ g, a: a# J! {8 ^% m</tr>
<tr>
# @  d, I# Y! H  \( b! v/ Q& C$ U6 ^<td>技术部</td>
<td>17000</td>
$ w6 O% B% A. J</tr>
<tr>
$ Q: a2 F. Y% y* n5 s  O<td>市场部</td>
9 m5 I2 v7 a7 z; y<td>7000</td>
; J4 O0 U! F$ E</tr>
</tbody>
0 E' f  M$ c7 W5 C# R: m</table>
<h2>四、中级用法：多维度分组与总计配置</h2>
; l- F$ ~" m3 N; S+ X5 f& R<h3>场景1：多行/多列标签 + 多值聚合</h3>
( b+ G/ y' j  G4 w: j8 E5 s<p><strong>需求</strong>：按「部门+季度」双行标签，无列标签，同时统计<strong>销售额总和</strong>和<strong>利润总和</strong>。</p>
- G' k: B3 w3 o3 b1 _' A8 ?8 ^+ w<h4>公式编写</h4>
& B5 O$ z& g7 O/ m' s! L<pre><code class="language-excel">=PIVOTBY(HSTACK(A2:A6, B2:B6), &quot;&quot;, HSTACK(C2:C6, D2:D6), &quot;SUM&quot;)
# a. N+ J" g) I</code></pre>
<h4>参数核心技巧</h4>
  L: ?1 ?8 L0 u<ul>
( m, ~& a' x3 v- x% m0 ?. o3 V<li><code>rows</code> 用 <code>HSTACK(部门, 季度)</code> 实现<strong>多行标签</strong>；</li>
! z% b% @! r  L6 D* X<li><code>values</code> 用 <code>HSTACK(销售额, 利润)</code> 实现<strong>多值聚合</strong>；</li>
<li>聚合方式 <code>SUM</code> 同时作用于两个值字段。</li>
</ul>
1 q$ P0 u9 v; n- N9 p<h4>溢出结果</h4>
<table>
<thead>
<tr>
<th></th>
8 m* j! U5 v7 _# ?, f7 c6 E1 r1 E7 l<th></th>
) B6 x: i; H; ?<th>销售额</th>
3 x3 k( e2 R9 ^2 K4 N- a2 Y<th>利润</th>
</tr>
$ g/ p1 H: r( T( d& u) p</thead>
<tbody>
<tr>
<td>销售部</td>
) c, b% a3 s$ I/ w  E<td>Q1</td>
8 v, Q( H8 Z" t2 L! [* R# j( u+ O<td>10000</td>
<td>2000</td>
4 B8 O" F% V% \% R! }</tr>
<tr>
; @- j8 F* r9 C<td>销售部</td>
! P3 v* U  g$ Y: s( R2 d9 n<td>Q2</td>
<td>12000</td>
" `1 M" h+ V# `& W( _<td>2500</td>
</tr>
<tr>
<td>技术部</td>
<td>Q1</td>
" n6 J& ?" B7 ]<td>8000</td>
6 l3 o* i/ C& D5 w# i3 ^5 h<td>1500</td>
</tr>
<tr>
  R4 {8 X- |8 L0 F<td>技术部</td>
<td>Q2</td>
0 ]8 P( S) ^2 z4 P, }6 ?<td>9000</td>
( m8 r* N8 T6 ?0 W4 l5 ?( z<td>1800</td>
4 {4 a$ K/ H0 K; ~' R" \- L  F</tr>
$ \) S& \, ?  G& ]8 W: ~( T% e<tr>
' M8 z$ J6 [2 c<td>市场部</td>
<td>Q1</td>
<td>5000</td>
<td>1000</td>
! b' q7 [) \0 t$ j7 D. g</tr>
, j* l, S2 d2 A0 k0 f  E<tr>
<td>市场部</td>
<td>Q2</td>
. e1 E& r0 m; c<td>2000</td>
0 X! J8 F+ H% r+ ^' ], n<td>300</td>
</tr>
  ~" D9 F' L( _</tbody>
+ m" G# X/ |" {: i</table>
<h3>场景2：添加行/列总计</h3>
<p><strong>需求</strong>：按「部门」行标签、「季度」列标签统计销售额，同时显示<strong>行总计</strong>和<strong>列总计</strong>，自定义总计名称为「合计」。</p>
<h4>公式编写</h4>
/ h' q( s6 i, Y9 r<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, &quot;合计&quot;, &quot;合计&quot;)
</code></pre>
<h4>参数说明</h4>
<ul>
<li><code>row_total=&quot;合计&quot;</code>：行总计标题为「合计」；</li>
<li><code>col_total=&quot;合计&quot;</code>：列总计标题为「合计」。</li>
</ul>
& D! M# T4 H1 g/ K<h4>溢出结果</h4>
<table>
2 Z; G7 Z5 c2 r0 d& ^<thead>
<tr>
<th></th>
: ~6 c, j" a. u+ ]- k! |7 D<th>Q1</th>
. z3 G6 E* p; w/ K* T. W+ J0 j2 Q<th>Q2</th>
<th>合计</th>
</tr>
</thead>
<tbody>
<tr>
8 y% z% J5 J9 V" e<td>销售部</td>
' }5 o1 A0 I$ R7 X  G( d& }4 l<td>10000</td>
7 f* i7 A& J9 o( Y5 r7 n! b. |<td>12000</td>
! T! O: K! O: O# M<td>22000</td>
' Z6 M, u& a0 y& q- x: o1 L5 U; l</tr>
<tr>
& c8 ^2 m% t" v: Q" U<td>技术部</td>
/ Z/ n2 u  f4 a$ U9 Y8 U  @<td>8000</td>
' O6 c% h' _% E8 I& \7 P<td>9000</td>
<td>17000</td>
) v6 {  J) A$ a6 t8 D8 h</tr>
<tr>
0 S( t3 j; q8 e% T* }% r<td>市场部</td>
<td>5000</td>
<td>2000</td>
1 l. I, J& J1 @# f. ]<td>7000</td>
3 O, x8 W2 ?6 p</tr>
<tr>
<td>合计</td>
  ^- z; u4 G! |& l<td>23000</td>
<td>23000</td>
<td>46000</td>
, \* d3 y- M8 S  W' i0 f) G0 _4 J9 o5 l</tr>
. ]( }, f1 ]# h4 ?</tbody>
</table>
2 v/ a6 O7 M7 @& Y1 f1 d. R<h3>场景3：保留空行列（<code>drop_empty=FALSE</code>）</h3>
. M- C* ^* b0 m<p><strong>需求</strong>：若数据源存在无数据的分组（如“市场部 Q3”），保留空行显示。</p>
<h4>扩展数据源</h4>
' j( a" `- M! z  q( t; o<p>在原数据后添加一行：<code>A7=市场部, B7=Q3, C7=&quot;&quot;</code>（销售额为空）。</p>
<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A7, B2:B7, C2:C7, &quot;SUM&quot;, , , , FALSE)
</code></pre>
<h4>关键参数：<code>drop_empty=FALSE</code></h4>
: v& [/ g* \/ {& g<p>强制保留无数据的行列，空值显示为 <code>0</code>。</p>
( l& E) F6 M, G8 C<h4>溢出结果（新增Q3列）</h4>
& R; H+ S) @; i: t# O<table>
7 ?" S: {: i; c4 a' V) e<thead>
<tr>
0 x5 u' Y0 S" v& P, b# @<th></th>
<th>Q1</th>
" m2 j$ M$ X! _<th>Q2</th>
<th>Q3</th>
2 r) I# ]' f* D7 @7 z/ i# ?# x( T</tr>
</thead>
<tbody>
<tr>
<td>销售部</td>
<td>10000</td>
9 I( o1 y) o4 z' t: o3 s1 p# b<td>12000</td>
<td>0</td>
4 z/ Z* \+ W. t0 E) m0 ^1 \; e</tr>
<tr>
/ x/ m9 M6 {2 j2 S<td>技术部</td>
<td>8000</td>
<td>9000</td>
0 y- O" J$ J7 u; Q1 Z<td>0</td>
</tr>
<tr>
<td>市场部</td>
<td>5000</td>
2 s) k1 Z; M4 Y4 t1 d) O! L% w7 {; a5 Y<td>2000</td>
+ |7 M/ k" N+ e/ h3 g( b; m; ~<td>0</td>
! X5 }% w2 U( K7 M' u- |</tr>
' |/ ^, P8 V" b  B</tbody>
</table>
  ^" _3 }8 ~& H# {1 N2 s<h2>五、高级用法：筛选、自定义聚合与函数嵌套</h2>
<h3>场景1：结合筛选条件（<code>filter_array</code>）</h3>
<p><strong>需求</strong>：仅统计<strong>销售额&gt;6000</strong>的记录，按部门和季度透视销售额总和。</p>
<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, , , C2:C6&gt;6000)
3 Q9 o2 _1 o4 n& P! G, C</code></pre>
, }/ ?3 l3 Y* ]8 L" {6 P8 [" W+ d<h4>参数核心：<code>filter_array=C2:C6&gt;6000</code></h4>
' m, b8 u, X/ K  S9 w  d<ul>
<li>生成布尔数组：<code>{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}</code>；</li>
, g. n# g5 ]: \0 e* B( F0 k<li>仅保留数组中 <code>TRUE</code> 对应的行（即销售额&gt;6000的记录）。</li>
% ~% G7 ~" F( u1 j</ul>
! E( }( G8 u- Z+ `" p" O5 F<h4>筛选后数据源（实际参与计算的行）</h4>
<table>
1 B+ r+ B% @0 q5 {3 u2 s" z  l<thead>
<tr>
<th>部门</th>
( ]9 D7 l* E$ A<th>季度</th>
' D' k5 }0 w4 P! O<th>销售额</th>
# z& s6 f+ F. }* Z</tr>
</thead>
<tbody>
<tr>
. x. \1 z7 e! ~7 ~<td>销售部</td>
1 }' D6 a8 e9 s/ S; l; F- W% p! J<td>Q1</td>
<td>10000</td>
</tr>
<tr>
<td>销售部</td>
: ?7 y: e% X3 I, c( U4 b, D<td>Q2</td>
; U2 O9 u! O/ M5 Z" w) F<td>12000</td>
, K3 i1 _# \. d7 f% L</tr>
<tr>
<td>技术部</td>
<td>Q1</td>
9 p* d: b5 A: k% k1 R<td>8000</td>
, m( L% I6 \" E</tr>
. F4 N8 R  v* \$ f" n$ A8 Y<tr>
' T1 c- R; d- u% d) I* ]0 Y4 j<td>技术部</td>
7 @8 d+ F; U. Q<td>Q2</td>
0 V" A1 J) B( H$ ~<td>9000</td>
6 |4 [! c! P# M2 `</tr>
</tbody>
</table>
8 j. c& I& s& j; I1 u<h4>最终溢出结果</h4>
* o- s" W# p6 a" P! F. _: e" Q<table>
<thead>
<tr>
, p! n, `% {; R- D8 Y' w  G0 [<th></th>
8 C- ~2 a. l6 A$ b7 p<th>Q1</th>
<th>Q2</th>
% A/ `/ O9 K3 n</tr>
</thead>
<tbody>
<tr>
" s: D9 u) x# H; H9 H3 b<td>销售部</td>
, m7 ~) W# t! t0 o+ ^6 U<td>10000</td>
/ t6 y+ O% L- Y& q<td>12000</td>
4 ^: {& d# K0 x; S! U6 N1 a* \- S</tr>
<tr>
; N! o- k) y; s4 c. Y<td>技术部</td>
<td>8000</td>
3 |, Y3 u% p6 a; t, g9 L<td>9000</td>
</tr>
& D& {3 C$ ^% `* [, p( @7 V8 W3 {</tbody>
</table>
4 \+ L+ A$ T0 R<h3>场景2：自定义聚合函数（<code>LAMBDA</code> 实现非内置计算）</h3>
0 E/ h$ s2 g' c$ {8 K0 |<p><strong>需求</strong>：按部门分组，计算<strong>销售额的标准差</strong>（内置聚合无此函数，需自定义）。</p>
/ z7 u* i- b8 L<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6, LAMBDA(x, STDEV.S(x)))
6 f( m, f' L* t& i8 Q/ x" _</code></pre>
<h4>参数核心：<code>agg=LAMBDA(x, STDEV.S(x))</code></h4>
<ul>
<li><code>x</code> 代表每个分组的数值集合（如销售部的 <code>{10000,12000}</code>）；</li>
. e# W& C( q) U2 @<li><code>STDEV.S(x)</code> 计算样本标准差，可替换为任意自定义逻辑（如 <code>MAX(x)-MIN(x)</code> 计算极差）。</li>
</ul>
<h4>溢出结果</h4>
! z. J( C1 x0 x. [<table>
  p; |3 H$ F" |" E- T% N7 p<thead>
<tr>
$ S# H/ F; D1 L7 j, V# A1 s2 L<th></th>
<th>销售额标准差</th>
</tr>
- v) s7 @. V2 ^! T' y# b) ?- m</thead>
9 _1 N$ Q' J& [* c" ?9 L( g6 d<tbody>
: R& z) \$ l) q2 A" R<tr>
2 q2 x+ y/ J3 a<td>销售部</td>
<td>1414.21</td>
</tr>
0 {) T2 ]( O9 O  @% }7 G<tr>
<td>技术部</td>
<td>707.11</td>
. R+ C( j& ~# z/ `& X0 Y</tr>
<tr>
<td>市场部</td>
7 c' J! T6 K, x# B<td>2121.32</td>
% |) }! E) J' G5 n</tr>
</tbody>
/ i" K8 q# B: Y) g( t</table>
* K: ?! W/ c9 ~# u6 [<h3>场景3：与 <code>SORT</code> 嵌套，对透视结果排序</h3>
<p><strong>需求</strong>：按部门透视总销售额，并按销售额<strong>降序排序</strong>。</p>
<h4>公式编写</h4>
<pre><code class="language-excel">=SORT(PIVOTBY(A2:A6, &quot;&quot;, C2:C6), 2, -1)
9 c5 Q4 ~( V! l  o0 Q( Y# Y</code></pre>
<h4>嵌套逻辑</h4>
<ol>
) @3 L8 o! \; Q2 e  k) M<li>先用 <code>PIVOTBY</code> 生成基础透视结果（2列：部门+销售额）；</li>
7 X# P0 D. {) |. @' ?<li>再用 <code>SORT</code> 函数按<strong>第2列（销售额）</strong> 降序（<code>-1</code>）排序。</li>
1 T+ B2 ~8 i8 p4 m5 E9 w& P. i</ol>
8 u- S: n9 e* W5 L$ O/ @<h4>排序后结果</h4>
<table>
<thead>
<tr>
, K8 }! X. {* Z+ C% l. I; ?4 P<th></th>
<th>销售额</th>
: D* q+ q/ e! F* [7 K* A</tr>
6 y, R( H1 I& a! U</thead>
<tbody>
0 Z8 X1 @# q2 ^2 [$ ?, [<tr>
+ {; Z, `0 j! u9 p/ I5 K5 [* u4 N<td>销售部</td>
<td>22000</td>
</tr>
<tr>
& L# q% O. J/ r0 @<td>技术部</td>
9 q* b1 _* c- W6 _7 `4 w* s! o, y<td>17000</td>
</tr>
! u+ D( z% ~$ P8 R- g  ]<tr>
<td>市场部</td>
<td>7000</td>
* G" }4 r$ K3 R5 d/ A# b: ]</tr>
: I4 ]7 h6 |, z& p' E</tbody>
</table>
# w2 O/ Z; U1 B- V- C<h2>六、常见问题与注意事项</h2>
<ol>
! @* Y7 y- t$ ~$ H0 X& Z<li>
# f( H3 s6 N' d9 f9 _<p><strong>溢出错误 <code>#SPILL!</code></strong></p>
" t+ Y4 X: K' h2 O: X! t<ul>
<li>原因：结果区域被其他单元格内容阻挡；<code>rows</code>/<code>cols</code>/<code>values</code> 行数不一致。</li>
$ ?- v4 j+ u5 v0 l! d0 g<li>解决：清空结果区域的空单元格；检查数据源区域行数是否匹配。</li>
</ul>
</li>
<li>
<p><strong>聚合函数不生效</strong></p>
9 t7 N! u3 [+ C* \( e/ H+ n  d. I<ul>
<li>原因：<code>agg</code> 参数拼写错误（如 <code>&quot;Sum&quot;</code> 写成 <code>&quot;sum&quot;</code> 不影响，但需避免错别字）；自定义 <code>LAMBDA</code> 函数语法错误。</li>
<li>解决：核对内置聚合函数名称；调试 <code>LAMBDA</code> 函数逻辑。</li>
$ X5 Y3 ?- }1 w  o</ul>
</li>
<li>
; B; u1 B) ^* s: [& j# H1 E& Q<p><strong>多字段分组顺序问题</strong></p>
8 a) g8 w2 f0 O9 _<ul>
! j$ ~1 N2 }% u<li>技巧：<code>HSTACK(字段1, 字段2)</code> 中，字段1是<strong>外层分组</strong>，字段2是<strong>内层分组</strong>，顺序决定透视结果的层级。</li>
$ V; B) ~$ c5 r</ul>
</li>
1 _, @0 ?* ]/ E/ C<li>
<p><strong>性能优化</strong></p>
<ul>
& `$ V, p6 X; J9 u<li>当数据源超过1万行时，避免使用复杂自定义聚合；</li>
- y% Y3 h7 n) }, z, A0 {/ y$ V<li>先用 <code>FILTER</code> 筛选目标数据，再传入 <code>PIVOTBY</code>，减少计算量。</li>
</ul>
</li>
</ol>

<p>在 Excel 中，<code>PIVOTBY</code> 函数的结果被称为 <strong>溢出结果</strong>，核心原因是它属于 <strong>动态数组函数</strong>，返回的不是单个值，而是一个<strong>多单元格的二维数组</strong>，会自动填充到公式单元格周围的空白单元格中——就像水从一个中心点“溢”向四周一样，因此得名。</p>
<h3>一、“溢出”的本质：动态数组 vs 传统函数</h3>
<h4>1. 传统函数的局限</h4>
- s8 p! y7 b  W5 A& [0 E9 @# U<p>传统 Excel 函数（如 <code>SUM</code>/<code>VLOOKUP</code>）<strong>只能返回单个值</strong>，输入公式后仅在当前单元格显示结果。<br />
6 U  P' i$ x6 J% z' T) w: `: X' r+ `如果公式逻辑需要返回多个值（比如同时统计多个部门的销售额），传统函数会直接报错。</p>
<h4>2. 动态数组函数的“溢出”能力</h4>
<p><code>PIVOTBY</code> 是 Excel 365 新增的<strong>动态数组函数</strong>，其核心特性是：</p>
/ ~4 _+ @# O& I+ C* c( h- X<ul>
<li>计算结果是一个 <strong>二维数组</strong>（对应透视表的行和列）；</li>
<li>公式仅需输入在<strong>单个单元格</strong>中，Excel 会自动将数组内容“溢出”到<strong>右侧和下方</strong>的空白单元格，形成完整的透视结果；</li>
<li>溢出区域的大小由数据源的分组数量决定（比如 3 个部门、2 个季度，就会溢出成 3 行 × 2 列的区域）。</li>
</ul>
) S9 U9 p. E: s3 s1 e. b+ g<h3>二、结合 PIVOTBY 实例理解溢出</h3>
<p>以之前的基础示例为例：</p>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
1 E( B+ l, o$ p, l</code></pre>
<p>假设公式输入在 <strong>E1 单元格</strong>：</p>
5 a7 W9 F5 Y( g<ol>
' r: O7 J2 K- k$ ^# j) B<li>函数计算后生成一个 <code>3行×2列</code> 的二维数组：
<pre><code>{10000, 12000; 8000, 9000; 5000, 2000}
</code></pre>
</li>
<li>数组会从 E1 开始<strong>向右、向下溢出</strong>，自动填充到 <code>E1:G3</code> 区域：<table>
! e# S  c5 s( B, d' L3 a' p<thead>
& u( o' x) H8 `6 ~<tr>
' L9 V+ y" `4 {! D1 m) z<th>单元格</th>
<th>内容</th>
<th>对应透视表位置</th>
</tr>
- U4 d. `+ ^4 f+ Q</thead>
* B8 I. I# z: V1 C9 o7 ]<tbody>
<tr>
5 F) b6 t6 R& O8 [! `<td>E1</td>
2 A# B2 J: P: f9 C* M  L2 ]6 z<td>10000</td>
<td>销售部-Q1</td>
</tr>
<tr>
<td>F1</td>
<td>12000</td>
1 L' e5 u) a6 X<td>销售部-Q2</td>
7 ?$ Q* ?5 E3 a  }</tr>
$ A2 i6 M: r  q& R2 Z<tr>
" e% m# L, |! L# j+ v<td>E2</td>
<td>8000</td>
7 S  ]. k/ r2 R) S) J7 n/ w<td>技术部-Q1</td>
/ [; r( l7 f& }( |7 ?* d( H</tr>
<tr>
% P8 b- i$ J/ j<td>F2</td>
<td>9000</td>
5 r& N, f6 |, c! `& N<td>技术部-Q2</td>
/ s) h, w8 [" e4 K8 H- E5 \8 u$ Q4 o</tr>
: u3 n0 W, U" |$ E# F9 |<tr>
' t4 ?6 N7 \/ o, I# ^' W) Z<td>E3</td>
+ a" |0 r* D1 W  v9 H<td>5000</td>
<td>市场部-Q1</td>
0 K  q- ~5 `+ q" W& I) \</tr>
! g+ `9 b, W1 V/ o<tr>
' \+ \! `$ V' B" [# V# C<td>F3</td>
: }/ ]! O8 {( R) l<td>2000</td>
<td>市场部-Q2</td>
</tr>
8 U* P3 ]( R) a9 \* g2 Y</tbody>
* u/ `2 p4 u" x+ V% C</table>
</li>
</ol>
1 ^- k" I7 c3 ]8 G- ^: z3 C9 ^<blockquote>
<p>补充：Excel 会自动为溢出区域添加<strong>边框标识</strong>（浅灰色虚线），直观区分溢出结果和其他单元格。</p>
</blockquote>
<h3>三、溢出结果的关键特性</h3>
<ol>
& ^) Q, P- \% r<li>
<p><strong>自动适配大小</strong><br />
当数据源的分组数量变化时（比如新增“Q3”季度、“行政部”），溢出区域会<strong>自动扩展/收缩</strong>，无需手动调整公式或单元格范围。</p>
</li>
<li>
1 g1 O$ g9 r- ]<p><strong>联动更新</strong><br />
: U; k' c( |6 ]4 o& d1 c! U% c  I数据源的值发生变化时，溢出区域的结果会<strong>实时自动更新</strong>（前提是 Excel 开启“自动计算”模式）。</p>
4 n0 B0 k  H! P5 T& }</li>
3 c. o. ?8 s+ g: P& H, }<li>
$ P" z' R8 o  Y$ b6 d8 o1 s<p><strong>不可手动修改</strong><br />
溢出区域的单元格是<strong>公式的一部分</strong>，无法单独编辑。如果手动修改其中某个单元格，会触发 <code>#SPILL!</code> 错误，提示“溢出区域被阻挡或修改”。</p>
</li>
( `8 F  [4 j( Z* w<li>
' M' u7 ~+ Y% D* n( z( `# k8 O6 @<p><strong>支持嵌套与引用</strong><br />
. H- d3 n: l& k: h- U( N可以将溢出结果作为其他函数的参数（比如用 <code>SORT</code> 排序、<code>INDEX</code> 提取特定值），例如：</p>
* i- N" v/ {# S<pre><code class="language-excel">=INDEX(PIVOTBY(A2:A6, B2:B6, C2:C6), 1, 2)
</code></pre>
<p>公式会直接提取“销售部-Q2”的销售额（结果为 12000）。</p>
</li>
</ol>
$ f2 z1 Q) K+ i' V4 Y; r% G<h3>因此</h3>
* [* ?# p, S* D+ a<p><code>PIVOTBY</code> 的“溢出结果”本质是<strong>动态数组的自动填充行为</strong>，它彻底摆脱了传统透视表需要手动插入、调整字段的繁琐操作，实现了“公式化创建动态透视表”的目标——这也是动态数组函数相比传统功能的核心优势。</p>
/ c  D4 M) Y' K, g( {, ^+ B