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

<p><img src="data/attachment/forum/202601/09/095116xnkk43ysdlmsyymy.webp" alt="141d455481b03db.webp" title="PIVOTBY函数-Excel函数教程" /></p>
  s4 N4 B: j# E  u. X; z* b! ^<p><code>PIVOTBY</code> 是 <strong>Excel 365/2021 及后续版本</strong>新增的<strong>动态数组函数</strong>，用于快速创建<strong>动态透视表</strong>，无需手动插入传统数据透视表，直接通过公式生成可自动溢出的透视结果，支持灵活的行列分组、聚合计算与筛选。相比传统透视表，<code>PIVOTBY</code> 更轻量化，可与其他函数嵌套使用。</p>
<h2>一、函数核心语法与参数说明</h2>
3 r& ~2 @" s6 e1 p<h3>1. 完整语法</h3>
+ z3 X8 ?1 s' T0 k2 |- ?<pre><code class="language-excel">PIVOTBY(rows, cols, values, [agg], [row_total], [col_total], [filter_array], [drop_empty])
</code></pre>
<h3>2. 参数逐义解析</h3>
+ A2 G4 D' G5 J% ^$ _<table>
% p1 d# p/ Y( P; T<thead>
<tr>
8 d" y+ v0 ]9 P0 W% l4 Z7 d( }<th>参数名</th>
<th>必选/可选</th>
<th>数据类型</th>
5 h4 ^- V* J. s2 {<th>核心作用</th>
5 s/ M" s0 y- m! g<th>注意事项</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>rows</code></td>
<td>必选</td>
<td>单元格区域/数组</td>
<td><strong>行标签数据源</strong>：用于分组的行维度字段（可单字段/多字段）</td>
) q( H$ J+ D( i( g, h" z<td>多字段需用 <code>HSTACK</code> 合并，如 <code>HSTACK(部门, 季度)</code></td>
</tr>
) t" a. ^. \* {6 ^5 Z<tr>
<td><code>cols</code></td>
<td>必选</td>
) u/ r, _1 ]# F% q: U3 c' A<td>单元格区域/数组</td>
: [( X1 {6 V4 d& C% d+ _# M<td><strong>列标签数据源</strong>：用于分组的列维度字段（可单字段/多字段）</td>
% z. d. Z, N7 V3 V  r: s  `<td>多字段需用 <code>HSTACK</code> 合并，支持空值（<code>&quot;&quot;</code>）表示无列分组</td>
* Z: f% f9 i* ?6 |3 _7 B3 i</tr>
4 B4 ]2 A' h7 q7 G) ^' E: p<tr>
<td><code>values</code></td>
<td>必选</td>
<td>单元格区域/数组</td>
/ V: h  A% o3 P5 w: [<td><strong>值区域数据源</strong>：需要聚合计算的数值字段</td>
6 N+ l' t: H) m( T( Z- }; x7 c<td>区域行数必须与 <code>rows</code>/<code>cols</code> 完全一致</td>
* X, P) p2 `, a" N) |& @( p</tr>
6 M: \. U) T+ y5 t<tr>
; ~3 x4 b+ _' K4 r0 H& F<td><code>[agg]</code></td>
<td>可选</td>
2 w7 I/ i% B; d) d; r2 z<td>文本/函数</td>
<td><strong>聚合方式</strong>：默认 <code>SUM</code>，支持内置函数或自定义 <code>LAMBDA</code> 函数</td>
<td>内置值：<code>SUM/COUNT/COUNTA/AVERAGE/MIN/MAX/MEDIAN</code> 等</td>
+ m8 C; Z& O6 n& S2 K8 G</tr>
+ _; o; k1 p+ B<tr>
8 f# l$ T% @& G/ G7 U) L<td><code>[row_total]</code></td>
- ?; O$ J7 e0 H<td>可选</td>
<td>逻辑值/文本</td>
" l& O- m7 [7 f- A<td><strong>行总计开关</strong>：是否添加行总计行</td>
# O# y+ h! U4 U: R<td><code>TRUE</code>=显示总计（默认名称“总计”）；<code>&quot;自定义名称&quot;</code>=自定义总计标题；<code>FALSE</code>=不显示</td>
( i6 @1 ~5 Y; r  _. o- u</tr>
<tr>
<td><code>[col_total]</code></td>
' s( N" o! k9 B' B" r8 a& v. y<td>可选</td>
: `2 v, G; e6 q: O% j4 Q<td>逻辑值/文本</td>
<td><strong>列总计开关</strong>：是否添加列总计列</td>
" C; _! g# Z* U2 c3 W<td>规则同 <code>row_total</code></td>
</tr>
<tr>
/ e9 o4 N# s5 n% ]<td><code>[filter_array]</code></td>
<td>可选</td>
<td>布尔数组</td>
<td><strong>筛选条件</strong>：按行筛选数据源，仅保留 <code>TRUE</code> 对应的行</td>
<td>数组行数必须与数据源一致，可结合 <code>FILTER</code> 函数生成筛选条件</td>
</tr>
  ^7 y2 z/ s- I2 f1 g<tr>
<td><code>[drop_empty]</code></td>
<td>可选</td>
  C- i( m% P$ d3 c<td>逻辑值</td>
, c# x3 p; }+ `. O. v4 m9 Y6 o<td><strong>空值过滤</strong>：是否删除无数据的行/列</td>
3 B1 M0 |+ l, `3 F<td><code>TRUE</code>=删除空行列（默认）；<code>FALSE</code>=保留空行列</td>
7 y# g! t( \1 m  n/ Q% R! _: R</tr>
</tbody>
$ ~" R' p7 R& g1 Y& A1 }</table>
<h3>3. 关键特性</h3>
: k! n' U0 j+ ?* D- H<ul>
<li><strong>动态溢出</strong>：公式输入后自动向下、向右溢出结果，无需手动拖拽填充。</li>
' u! K% W, G* ~! B4 f, s) A<li><strong>自动更新</strong>：数据源变化时，结果实时刷新（需开启 Excel 自动计算）。</li>
  C( d) N5 Q! w<li><strong>兼容性</strong>：仅支持 Excel 365/2021 及以上版本，低版本需用传统透视表替代。</li>
  `8 S+ i- A! Q, [% D% S% q</ul>
<h2>二、数据源准备（通用示例数据）</h2>
; S! `. S$ v7 i$ @& Z6 R0 {<p>为统一示例，使用<strong>销售数据表</strong>作为基础数据源，共 6 行数据：</p>
7 g2 G8 m1 e0 e: P2 ~; n9 m& Z+ d<table>
<thead>
<tr>
<th>单元格区域</th>
<th>A (部门)</th>
- R6 h3 S. U* ~9 T" Z3 B3 e<th>B (季度)</th>
<th>C (销售额)</th>
<th>D (利润)</th>
</tr>
: P) V) ^2 u& f0 d</thead>
<tbody>
* S% F! X- L1 s0 J( x2 V<tr>
<td>1</td>
4 A! }1 M4 ^+ N<td>销售部</td>
<td>Q1</td>
<td>10000</td>
& f8 _0 A' E- M8 N; T, k<td>2000</td>
</tr>
, A/ g' E- i% e- r; H- O$ x7 _<tr>
! q+ {- |* |, G1 a0 |; O<td>2</td>
" z7 e8 J$ s* Z1 N% ^1 S* y$ l: f<td>销售部</td>
<td>Q2</td>
<td>12000</td>
<td>2500</td>
2 E  m  p/ M  V( c% m" x4 t</tr>
- I: R9 h2 y$ v, z<tr>
. e: V' J( s( y! R5 p<td>3</td>
" j; Q( a/ I' y7 N<td>技术部</td>
: C! s$ O9 p  Q2 @: L- G, }<td>Q1</td>
: C% Q6 _) P) @4 {& r<td>8000</td>
<td>1500</td>
</tr>
1 X. W; g( [$ m2 p<tr>
<td>4</td>
<td>技术部</td>
8 P# Q' P; E% d' Z<td>Q2</td>
<td>9000</td>
<td>1800</td>
/ W$ `8 p3 D  W& m5 T$ X$ ^2 n( O</tr>
; B9 L& H' t7 }! m0 q+ g<tr>
<td>5</td>
<td>市场部</td>
<td>Q1</td>
4 E  L: h5 W) O- \<td>5000</td>
<td>1000</td>
) m" ]' m  C9 W0 Q+ i6 l</tr>
<tr>
. m5 \$ V7 K  F8 m* V( F<td>6</td>
<td>市场部</td>
# F3 J* b0 ]6 F1 S( l! C<td>Q2</td>
<td>2000</td>
<td>300</td>
</tr>
: N; ?& B% U" P: ~6 e* v3 P" g8 ~1 |</tbody>
& i% M2 c: y0 ~' _2 _; |</table>
<p>后续所有示例均基于此表，数据区域记为 <code>A1:D6</code>。</p>
<h2>三、初级用法：基础单维度透视</h2>
<h3>场景1：单一行标签 + 单一列标签 + 单值聚合</h3>
<p><strong>需求</strong>：按「部门」分组（行），按「季度」分组（列），统计<strong>销售额总和</strong>。</p>
9 l4 v  B: z! h" F& A<h4>公式编写</h4>
: ^* ~$ l1 o! k* z# s8 ]7 Z5 m1 t! `<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
</code></pre>
<h4>参数对应说明</h4>
<table>
<thead>
<tr>
<th>参数</th>
3 x- T- S7 N! p7 b/ B+ H<th>取值</th>
9 B$ F5 ]; E+ s<th>作用</th>
</tr>
</thead>
<tbody>
8 s  e9 H3 X% l<tr>
<td><code>rows</code></td>
; i( a9 A  M' i, B$ ^/ N; i; V<td><code>A2:A6</code></td>
<td>行标签：部门（销售部/技术部/市场部）</td>
</tr>
# X1 B& i* o- X$ e3 A. T2 c<tr>
; i: f" [5 v6 X; \5 ]2 G2 t8 p+ l+ o<td><code>cols</code></td>
<td><code>B2:B6</code></td>
<td>列标签：季度（Q1/Q2）</td>
</tr>
+ p* m7 m+ r! H! J( n# W<tr>
  J- A" ~3 r+ m# c<td><code>values</code></td>
<td><code>C2:C6</code></td>
<td>值区域：销售额</td>
2 f3 W  X: o1 y0 o+ w- K/ l3 L- ?</tr>
<tr>
<td><code>[agg]</code></td>
<td><code>&quot;SUM&quot;</code></td>
& E0 t5 `$ a* n4 z  Y' M8 ~* x$ u<td>聚合方式：求和（默认可省略）</td>
</tr>
</tbody>
. l, D5 v7 p$ @, z</table>
6 {- p8 W; t9 K* E<h4>溢出结果</h4>
<table>
6 O$ G$ W9 Q, B- K1 t3 F& \<thead>
<tr>
<th></th>
<th>Q1</th>
" Y, j9 d* b; V2 L<th>Q2</th>
8 Z1 C8 Y2 u5 O. d</tr>
</thead>
. {8 l  l) p! H<tbody>
<tr>
<td>销售部</td>
<td>10000</td>
- K* m7 t" P' U<td>12000</td>
</tr>
+ c! k% q/ b/ t: R+ y1 r! J. W3 X<tr>
<td>技术部</td>
<td>8000</td>
/ w$ S& B6 k) B4 o: U9 S6 t<td>9000</td>
; {7 C* Q" x# t& |! q</tr>
( _3 O) Y( v$ P% o: x' |3 `. V3 o<tr>
, ?3 X+ t# Q0 R: c, O9 a<td>市场部</td>
$ c8 j* b3 x4 Y* {<td>5000</td>
<td>2000</td>
</tr>
</tbody>
</table>
% ~; |$ P6 _( z- f" |( J<h3>场景2：无列标签（仅行维度聚合）</h3>
. o' |- K  q3 |) G4 l<p><strong>需求</strong>：按「部门」分组，统计<strong>总销售额</strong>，不按列拆分。</p>
0 C6 n5 v% k( O, f& `* R<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6)
</code></pre>
8 z. h  @8 |: A3 {7 D<h4>关键参数：<code>cols=&quot;&quot;</code></h4>
% G( N- ?1 ^( t/ z. V3 F6 S<p>表示不设置列标签，结果仅输出「行标签+聚合值」两列。</p>
1 h7 m8 e. R2 v' b7 k8 U: ~<h4>溢出结果</h4>
$ s8 a, V, q' I4 n<table>
* M1 k" c+ O, y3 @3 c) e1 x<thead>
: ]' ^; @2 b: G: f0 D6 w/ Z<tr>
. ~* @1 w2 Z- C1 F  W8 i4 Q<th></th>
<th>销售额</th>
</tr>
</thead>
<tbody>
, O9 t' u& @6 S* A) V7 T  G<tr>
<td>销售部</td>
<td>22000</td>
</tr>
<tr>
& S3 k8 u# C0 |<td>技术部</td>
/ p, p* ?2 ~; W8 [- X# _# `<td>17000</td>
</tr>
<tr>
. a( g. L) V8 e8 u2 o<td>市场部</td>
<td>7000</td>
2 U9 A/ v+ q# ^$ Q/ W</tr>
</tbody>
</table>
$ X, \5 P6 L$ X& C<h2>四、中级用法：多维度分组与总计配置</h2>
<h3>场景1：多行/多列标签 + 多值聚合</h3>
$ i' I! B/ @# I<p><strong>需求</strong>：按「部门+季度」双行标签，无列标签，同时统计<strong>销售额总和</strong>和<strong>利润总和</strong>。</p>
( a  d2 L" M1 U; @0 z. z<h4>公式编写</h4>
. p, Q# S4 k3 e# n<pre><code class="language-excel">=PIVOTBY(HSTACK(A2:A6, B2:B6), &quot;&quot;, HSTACK(C2:C6, D2:D6), &quot;SUM&quot;)
</code></pre>
, V; S# ]  S) y1 q" D<h4>参数核心技巧</h4>
<ul>
2 n% N2 F9 x: c6 b<li><code>rows</code> 用 <code>HSTACK(部门, 季度)</code> 实现<strong>多行标签</strong>；</li>
9 J/ J) U$ e( J. [0 M<li><code>values</code> 用 <code>HSTACK(销售额, 利润)</code> 实现<strong>多值聚合</strong>；</li>
8 `5 y0 ^9 I" x<li>聚合方式 <code>SUM</code> 同时作用于两个值字段。</li>
</ul>
. S' ], b( w" W$ O1 u' r8 G- ]6 t9 U<h4>溢出结果</h4>
' M( w9 o. M! c: S! G& F  r<table>
% K2 b. y% k  X<thead>
<tr>
<th></th>
<th></th>
<th>销售额</th>
! J- ]3 J* B- M( h9 C<th>利润</th>
</tr>
( W. K5 P! j2 e5 U* ^</thead>
- X5 C1 B4 A; [5 k<tbody>
<tr>
<td>销售部</td>
<td>Q1</td>
0 W6 l2 Q' f$ e0 ?) Z) D<td>10000</td>
* t5 o) E9 m/ U# X' F! o* v, m<td>2000</td>
</tr>
<tr>
<td>销售部</td>
5 @/ ?: ?7 \; s! h3 J. [<td>Q2</td>
<td>12000</td>
<td>2500</td>
1 F3 R7 G1 u" K# h/ s; B</tr>
<tr>
<td>技术部</td>
<td>Q1</td>
<td>8000</td>
<td>1500</td>
</tr>
# K3 p5 C! l2 e; r( P- E0 y<tr>
<td>技术部</td>
5 r. \4 }, s6 c<td>Q2</td>
- n/ M% r, A8 O, ]) F5 W<td>9000</td>
<td>1800</td>
</tr>
<tr>
<td>市场部</td>
( P3 u# G# V# W8 Y<td>Q1</td>
: l& P7 {/ K" V<td>5000</td>
<td>1000</td>
& o0 I* Y5 J$ |! Y/ {</tr>
/ z; r, J7 M$ u4 Z; O1 ~( T<tr>
+ M( ]* C3 ?" f& O1 T! T<td>市场部</td>
<td>Q2</td>
5 G$ z. U0 T+ h( X<td>2000</td>
! o+ Y. x/ X4 I$ `<td>300</td>
</tr>
9 z; S2 M9 ]# j2 k</tbody>
</table>
<h3>场景2：添加行/列总计</h3>
6 Q) b6 o7 s& U0 i8 x<p><strong>需求</strong>：按「部门」行标签、「季度」列标签统计销售额，同时显示<strong>行总计</strong>和<strong>列总计</strong>，自定义总计名称为「合计」。</p>
<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, &quot;合计&quot;, &quot;合计&quot;)
1 I7 d9 x. D2 O$ y+ W) m</code></pre>
; m# T: a: U: F7 W3 ?7 `<h4>参数说明</h4>
<ul>
: g  \& f3 ]+ H4 I" e7 N8 O<li><code>row_total=&quot;合计&quot;</code>：行总计标题为「合计」；</li>
4 g" R6 j! _1 M# j. D4 p* k* _<li><code>col_total=&quot;合计&quot;</code>：列总计标题为「合计」。</li>
, Z& w* d0 a) }0 }7 }" b  V# A# ~( _</ul>
<h4>溢出结果</h4>
+ U/ l8 ~2 n( B/ V8 K: `<table>
<thead>
<tr>
<th></th>
" N, ~8 a, U, W: {0 {- Y1 c9 R<th>Q1</th>
<th>Q2</th>
<th>合计</th>
7 g# N3 r+ E. ^7 r  c5 k! k</tr>
</thead>
<tbody>
* m. |1 }( B9 P! V) M' n<tr>
) L" H) Q6 t: @3 |7 L  ~) F<td>销售部</td>
0 v4 r8 r; h- G0 V- @6 ^8 ~<td>10000</td>
" o. q) \5 E7 A' h$ {! K  k<td>12000</td>
3 X( H: x* k2 T$ E0 Z$ F<td>22000</td>
</tr>
5 U- v, f1 ]) G7 Q0 @% C<tr>
<td>技术部</td>
, S& Q0 |' o. w7 H; I  c( n8 p<td>8000</td>
4 C2 ]. T. ^+ v" T. j7 @+ o; q<td>9000</td>
<td>17000</td>
</tr>
<tr>
: g4 E. \7 K6 f& V1 q. B0 R<td>市场部</td>
5 Z1 r( A$ m' O+ E6 q<td>5000</td>
. l: j( |$ f) n<td>2000</td>
<td>7000</td>
</tr>
<tr>
<td>合计</td>
5 ~' V4 \$ S- H8 e1 u* _<td>23000</td>
+ D! K- v  f) }+ Y* F<td>23000</td>
/ N/ G5 E7 R& o* E  R- [<td>46000</td>
9 T, j) V+ D, k( \* D6 E9 {</tr>
* p! \9 G0 ~" A7 n) G, W* ~</tbody>
9 ~( I" |8 `* a- B3 _</table>
. M& M0 F& z& x% U' g  C<h3>场景3：保留空行列（<code>drop_empty=FALSE</code>）</h3>
<p><strong>需求</strong>：若数据源存在无数据的分组（如“市场部 Q3”），保留空行显示。</p>
<h4>扩展数据源</h4>
8 s+ p+ k; x; j4 d<p>在原数据后添加一行：<code>A7=市场部, B7=Q3, C7=&quot;&quot;</code>（销售额为空）。</p>
<h4>公式编写</h4>
" @1 |- o, d: V  V$ z% I<pre><code class="language-excel">=PIVOTBY(A2:A7, B2:B7, C2:C7, &quot;SUM&quot;, , , , FALSE)
</code></pre>
- ?+ u: G% c) `& d9 B  s<h4>关键参数：<code>drop_empty=FALSE</code></h4>
7 t; J" l6 J$ s/ w<p>强制保留无数据的行列，空值显示为 <code>0</code>。</p>
& R+ x" l2 V2 s3 @& {* E<h4>溢出结果（新增Q3列）</h4>
! h  j. F8 O6 a2 m# V* h<table>
<thead>
# w3 _  u5 T! j& _+ V<tr>
<th></th>
9 g4 P8 p0 ^* C" b  A$ h8 x6 a/ X<th>Q1</th>
<th>Q2</th>
9 j% M& x! d0 \9 K. D3 h<th>Q3</th>
</tr>
</thead>
<tbody>
<tr>
<td>销售部</td>
<td>10000</td>
% D' A/ O% c% H: A0 _  q/ r<td>12000</td>
<td>0</td>
</tr>
# A; Y& {) _! V7 c<tr>
; ~: o4 Q" R9 L. S. @. h. r<td>技术部</td>
<td>8000</td>
<td>9000</td>
<td>0</td>
$ ~8 t; D( ~0 L</tr>
<tr>
9 F# m. b0 _3 P$ B" u<td>市场部</td>
  A" U- V, x0 U% a/ ]" B<td>5000</td>
1 e, h' b+ |/ x9 L5 t" K<td>2000</td>
<td>0</td>
</tr>
</tbody>
</table>
1 @$ H/ C, P/ x. p5 E# I<h2>五、高级用法：筛选、自定义聚合与函数嵌套</h2>
# h5 M& G# Q6 E+ I, a<h3>场景1：结合筛选条件（<code>filter_array</code>）</h3>
<p><strong>需求</strong>：仅统计<strong>销售额&gt;6000</strong>的记录，按部门和季度透视销售额总和。</p>
8 u& P% X( d  Z: `* r8 V& n<h4>公式编写</h4>
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;, , , C2:C6&gt;6000)
/ @+ ^, ?" a  n" I0 ~' }</code></pre>
<h4>参数核心：<code>filter_array=C2:C6&gt;6000</code></h4>
<ul>
<li>生成布尔数组：<code>{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}</code>；</li>
. m7 ?9 F# b5 `9 A, X- S" y<li>仅保留数组中 <code>TRUE</code> 对应的行（即销售额&gt;6000的记录）。</li>
</ul>
<h4>筛选后数据源（实际参与计算的行）</h4>
<table>
; M+ h( n. g* F<thead>
: {2 z4 y2 v4 e8 j5 {0 D5 ]* T<tr>
<th>部门</th>
<th>季度</th>
: Q( i7 Y; ~# a) ]0 Z<th>销售额</th>
% c% S/ O) r  W- C" n</tr>
, M; @! U& y- |" M. T, J* i</thead>
( H3 ^* g5 W& S& w; s) ~5 \<tbody>
1 @, Y; l5 j7 g7 E' Z  s<tr>
<td>销售部</td>
; j  {5 d8 p2 g9 @<td>Q1</td>
<td>10000</td>
</tr>
5 [- w, U6 g  B7 Q; g  a; I2 n: T8 \+ N<tr>
$ D4 b5 b* X& m7 O  }<td>销售部</td>
<td>Q2</td>
<td>12000</td>
</tr>
<tr>
<td>技术部</td>
<td>Q1</td>
7 [) n9 }, J$ h( [1 j* k<td>8000</td>
</tr>
<tr>
<td>技术部</td>
7 H7 T* V: j+ t<td>Q2</td>
' {& M  @  M' _+ u/ Z* l<td>9000</td>
</tr>
</tbody>
: r- K  s$ o: E- m3 }- Q+ v</table>
# I& j( e: B/ X; A$ B<h4>最终溢出结果</h4>
* F  |0 x" J3 _( k! W<table>
9 i% D, w" m0 \$ d( o' d0 F$ n<thead>
<tr>
<th></th>
<th>Q1</th>
<th>Q2</th>
</tr>
</thead>
<tbody>
8 O; t4 O1 o: s8 `2 _+ ~2 ~0 |<tr>
$ Y9 n' h& s3 f4 \1 L4 Z: l<td>销售部</td>
5 i3 T; u& |( f0 w$ O$ a" ?9 S<td>10000</td>
<td>12000</td>
</tr>
<tr>
0 \( s8 L/ @) g2 S7 N/ e9 z/ F<td>技术部</td>
0 W+ D0 D" l2 Y<td>8000</td>
<td>9000</td>
</tr>
% @0 i- J: d0 @</tbody>
</table>
<h3>场景2：自定义聚合函数（<code>LAMBDA</code> 实现非内置计算）</h3>
<p><strong>需求</strong>：按部门分组，计算<strong>销售额的标准差</strong>（内置聚合无此函数，需自定义）。</p>
<h4>公式编写</h4>
4 w1 ]* m0 m: f( s% _<pre><code class="language-excel">=PIVOTBY(A2:A6, &quot;&quot;, C2:C6, LAMBDA(x, STDEV.S(x)))
* [) Y; s+ @' C; }/ ]% J$ \5 `</code></pre>
<h4>参数核心：<code>agg=LAMBDA(x, STDEV.S(x))</code></h4>
1 L- ~1 S# F, k, |* U<ul>
<li><code>x</code> 代表每个分组的数值集合（如销售部的 <code>{10000,12000}</code>）；</li>
<li><code>STDEV.S(x)</code> 计算样本标准差，可替换为任意自定义逻辑（如 <code>MAX(x)-MIN(x)</code> 计算极差）。</li>
</ul>
( f( G  Z) t! h) _<h4>溢出结果</h4>
<table>
, b3 n$ U' t+ j7 d1 W<thead>
$ m' F) T( e- W3 U$ i: k' u: y<tr>
<th></th>
1 S. w* x0 H6 I8 D* J# y<th>销售额标准差</th>
</tr>
</thead>
<tbody>
+ A# {$ @4 e+ V- }<tr>
: V0 j+ k& G* k7 e/ L3 `" J<td>销售部</td>
<td>1414.21</td>
</tr>
( d) s& c( [# ?; \7 L1 i% H: D7 T" U<tr>
: c! h$ Y1 e: t8 Y+ N$ g4 ^+ {<td>技术部</td>
0 h6 Q, f4 [" P7 D2 L: F% G<td>707.11</td>
</tr>
<tr>
& ^, n* k- H/ J* C- g  N. h<td>市场部</td>
2 s) l0 \7 P" I; S<td>2121.32</td>
* X/ U0 Y8 s3 G2 t  n) @* @: W1 z( \</tr>
" W- }% Q# Z% ^& R! w5 V& o</tbody>
& n, C) r1 E  m* v# N; |</table>
<h3>场景3：与 <code>SORT</code> 嵌套，对透视结果排序</h3>
& B2 J" I8 I; T4 O, t<p><strong>需求</strong>：按部门透视总销售额，并按销售额<strong>降序排序</strong>。</p>
" T' b0 N$ I1 M* U<h4>公式编写</h4>
<pre><code class="language-excel">=SORT(PIVOTBY(A2:A6, &quot;&quot;, C2:C6), 2, -1)
</code></pre>
/ C% }* F+ ^3 x( w7 x<h4>嵌套逻辑</h4>
+ c" {/ o. n4 W5 i, i<ol>
9 Y+ f8 b2 d( N5 }<li>先用 <code>PIVOTBY</code> 生成基础透视结果（2列：部门+销售额）；</li>
8 a+ i% `. V3 f/ P<li>再用 <code>SORT</code> 函数按<strong>第2列（销售额）</strong> 降序（<code>-1</code>）排序。</li>
2 j/ J, i0 G- @5 ]# v</ol>
<h4>排序后结果</h4>
<table>
0 p" y4 t" W- ^( V) d$ v& N7 p. t<thead>
<tr>
( h0 s2 k5 _. U7 n& R<th></th>
' M/ E1 V7 ^* I; u# _<th>销售额</th>
</tr>
3 m  ~; ?. \  }0 X8 z</thead>
, ~% P/ c8 [4 Q0 a6 k<tbody>
<tr>
  D* @  R2 a6 k7 k5 X<td>销售部</td>
7 h% \* Q* B# y% W2 F<td>22000</td>
</tr>
# c1 M  t) L: Q3 u6 l" p9 H3 X<tr>
<td>技术部</td>
+ x  A6 g# W" t# a; T7 {1 C3 L<td>17000</td>
: v/ f9 W$ K5 ?, e5 D2 P1 J0 a</tr>
  u/ R, ?/ R- `$ v/ n<tr>
<td>市场部</td>
<td>7000</td>
! F) f6 Y! N' }) Q</tr>
; f9 m1 e. c0 I/ J. @% U' W' G</tbody>
</table>
<h2>六、常见问题与注意事项</h2>
. J' _3 V6 g3 {, \$ U6 M2 y<ol>
<li>
1 S! H! v. {( Z<p><strong>溢出错误 <code>#SPILL!</code></strong></p>
% Q0 ?; \/ e" L4 e<ul>
<li>原因：结果区域被其他单元格内容阻挡；<code>rows</code>/<code>cols</code>/<code>values</code> 行数不一致。</li>
7 Q7 V2 z. `5 d$ O% t% S<li>解决：清空结果区域的空单元格；检查数据源区域行数是否匹配。</li>
</ul>
</li>
<li>
<p><strong>聚合函数不生效</strong></p>
) f1 F& \4 C6 i* V2 h2 H<ul>
<li>原因：<code>agg</code> 参数拼写错误（如 <code>&quot;Sum&quot;</code> 写成 <code>&quot;sum&quot;</code> 不影响，但需避免错别字）；自定义 <code>LAMBDA</code> 函数语法错误。</li>
8 D2 B$ u- R; V8 v, U% a! Y# h0 R<li>解决：核对内置聚合函数名称；调试 <code>LAMBDA</code> 函数逻辑。</li>
</ul>
" {: [6 F0 G3 w) a</li>
7 q- Q. `" i/ c! y) m- h& c' h4 f<li>
4 m, m% O" @* K<p><strong>多字段分组顺序问题</strong></p>
7 [0 S% V$ {' Y4 ?$ l% ]  s<ul>
<li>技巧：<code>HSTACK(字段1, 字段2)</code> 中，字段1是<strong>外层分组</strong>，字段2是<strong>内层分组</strong>，顺序决定透视结果的层级。</li>
</ul>
</li>
<li>
( I" i+ g! H9 e<p><strong>性能优化</strong></p>
<ul>
<li>当数据源超过1万行时，避免使用复杂自定义聚合；</li>
' h2 m! ^- |6 s$ ?, c+ I<li>先用 <code>FILTER</code> 筛选目标数据，再传入 <code>PIVOTBY</code>，减少计算量。</li>
</ul>
! p& _8 {* D; E* a</li>
</ol>

<p>在 Excel 中，<code>PIVOTBY</code> 函数的结果被称为 <strong>溢出结果</strong>，核心原因是它属于 <strong>动态数组函数</strong>，返回的不是单个值，而是一个<strong>多单元格的二维数组</strong>，会自动填充到公式单元格周围的空白单元格中——就像水从一个中心点“溢”向四周一样，因此得名。</p>
<h3>一、“溢出”的本质：动态数组 vs 传统函数</h3>
<h4>1. 传统函数的局限</h4>
<p>传统 Excel 函数（如 <code>SUM</code>/<code>VLOOKUP</code>）<strong>只能返回单个值</strong>，输入公式后仅在当前单元格显示结果。<br />
如果公式逻辑需要返回多个值（比如同时统计多个部门的销售额），传统函数会直接报错。</p>
<h4>2. 动态数组函数的“溢出”能力</h4>
: @4 C" C0 [( |; i<p><code>PIVOTBY</code> 是 Excel 365 新增的<strong>动态数组函数</strong>，其核心特性是：</p>
% X; l/ E4 E+ L<ul>
! L0 _) C; Z* C6 ]) }<li>计算结果是一个 <strong>二维数组</strong>（对应透视表的行和列）；</li>
% _4 y- f( m% ?- x% X7 N6 w; s<li>公式仅需输入在<strong>单个单元格</strong>中，Excel 会自动将数组内容“溢出”到<strong>右侧和下方</strong>的空白单元格，形成完整的透视结果；</li>
: {% W: P: K4 P6 w( q. ^& P+ J<li>溢出区域的大小由数据源的分组数量决定（比如 3 个部门、2 个季度，就会溢出成 3 行 × 2 列的区域）。</li>
, Q- M0 z* a. n1 j; P% C" G</ul>
* A9 P6 A/ X8 A( r2 m7 E<h3>二、结合 PIVOTBY 实例理解溢出</h3>
; n' N; k, W' c( T& Z% i6 l<p>以之前的基础示例为例：</p>
1 q0 k3 s# b: v, C<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, &quot;SUM&quot;)
3 o" K/ c5 b& G6 F, F! x* H/ m</code></pre>
* B8 j4 I/ y* `5 V+ @<p>假设公式输入在 <strong>E1 单元格</strong>：</p>
* z5 |- G9 i; @1 Z! {2 X3 Y<ol>
<li>函数计算后生成一个 <code>3行×2列</code> 的二维数组：
; c6 L: _# Y7 t<pre><code>{10000, 12000; 8000, 9000; 5000, 2000}
2 s% [( B2 h) q& v</code></pre>
</li>
1 o; d* U1 l- j% T+ ^# R# N<li>数组会从 E1 开始<strong>向右、向下溢出</strong>，自动填充到 <code>E1:G3</code> 区域：<table>
<thead>
+ u1 C8 F# n" J/ n9 F* y<tr>
<th>单元格</th>
/ c3 h6 S- g3 j" _' m<th>内容</th>
<th>对应透视表位置</th>
0 X, o! A: Q( L* {</tr>
</thead>
8 w$ K5 [5 S9 ]% r/ A. a, e+ g$ w<tbody>
  p1 I, l2 l6 }<tr>
* g/ n8 t8 c* k. a  u% c<td>E1</td>
<td>10000</td>
% Q4 \; ?" m/ s) \% o* ~<td>销售部-Q1</td>
</tr>
2 a0 |& p0 m! Z" i# ?' P<tr>
2 y% n9 J& V( `" l5 f9 [) k2 n( y<td>F1</td>
: e' f' i6 n% s6 Z) E5 w<td>12000</td>
<td>销售部-Q2</td>
4 L* x6 ]. S- ^3 {9 y4 C</tr>
<tr>
+ ?% a! G& o6 z) R  _% l0 U<td>E2</td>
<td>8000</td>
" r% g3 Q$ o! X) \8 V. u<td>技术部-Q1</td>
( |- J6 t$ T- @& Z: [8 o</tr>
2 C: O! ?  j# h* V6 X; C<tr>
<td>F2</td>
0 M4 G& }- U( D8 b( a<td>9000</td>
<td>技术部-Q2</td>
/ j( O1 W2 K% |8 N) a</tr>
% P9 @. S1 ^7 e2 W<tr>
<td>E3</td>
<td>5000</td>
<td>市场部-Q1</td>
</tr>
<tr>
5 n' P; {' X3 |0 }/ |- d) Q" R. A<td>F3</td>
<td>2000</td>
3 `; r  F+ N4 M<td>市场部-Q2</td>
4 M! s* q6 a4 g0 i</tr>
  h! b: D8 v( ?- m% [& ~</tbody>
</table>
</li>
</ol>
- ^8 m8 ~8 v6 M( t<blockquote>
<p>补充：Excel 会自动为溢出区域添加<strong>边框标识</strong>（浅灰色虚线），直观区分溢出结果和其他单元格。</p>
4 t, }$ I9 i5 a# J- ?" I7 j</blockquote>
5 V, N1 H5 ^; T- H<h3>三、溢出结果的关键特性</h3>
<ol>
<li>
8 S. h0 e- V4 Q<p><strong>自动适配大小</strong><br />
当数据源的分组数量变化时（比如新增“Q3”季度、“行政部”），溢出区域会<strong>自动扩展/收缩</strong>，无需手动调整公式或单元格范围。</p>
/ o- e& _% E8 r; N4 Q</li>
: K3 l  a" o& C: `<li>
8 t) N& t5 \0 F+ X5 w- P<p><strong>联动更新</strong><br />
/ R  P$ ?: a9 E& }( h( J数据源的值发生变化时，溢出区域的结果会<strong>实时自动更新</strong>（前提是 Excel 开启“自动计算”模式）。</p>
</li>
<li>
" i2 ]- l! g5 u# C8 J3 ^<p><strong>不可手动修改</strong><br />
溢出区域的单元格是<strong>公式的一部分</strong>，无法单独编辑。如果手动修改其中某个单元格，会触发 <code>#SPILL!</code> 错误，提示“溢出区域被阻挡或修改”。</p>
' R& o/ R5 h) B</li>
<li>
<p><strong>支持嵌套与引用</strong><br />
可以将溢出结果作为其他函数的参数（比如用 <code>SORT</code> 排序、<code>INDEX</code> 提取特定值），例如：</p>
<pre><code class="language-excel">=INDEX(PIVOTBY(A2:A6, B2:B6, C2:C6), 1, 2)
* f5 R7 F' |- x6 B% \! ~</code></pre>
: l* F, s) I3 r$ Z<p>公式会直接提取“销售部-Q2”的销售额（结果为 12000）。</p>
" I7 K5 @) i) t$ C5 B( e</li>
1 a) |$ I+ s- ?& @% Z" q: X2 A</ol>
; a9 ?! H% z1 {9 Z0 I" ?<h3>因此</h3>
% t4 W& j9 R$ w7 ^1 }<p><code>PIVOTBY</code> 的“溢出结果”本质是<strong>动态数组的自动填充行为</strong>，它彻底摆脱了传统透视表需要手动插入、调整字段的繁琐操作，实现了“公式化创建动态透视表”的目标——这也是动态数组函数相比传统功能的核心优势。</p>