<p><img src="data/attachment/forum/202601/09/095116xnkk43ysdlmsyymy.webp" alt="141d455481b03db.webp" title="PIVOTBY函数-Excel函数教程" /></p>6 Z4 a/ d0 K0 m2 y( C; x
<p><code>PIVOTBY</code> 是 <strong>Excel 365/2021 及后续版本</strong>新增的<strong>动态数组函数</strong>,用于快速创建<strong>动态透视表</strong>,无需手动插入传统数据透视表,直接通过公式生成可自动溢出的透视结果,支持灵活的行列分组、聚合计算与筛选。相比传统透视表,<code>PIVOTBY</code> 更轻量化,可与其他函数嵌套使用。</p>
. r# D7 k c8 _. ~ f# ?! }<h2>一、函数核心语法与参数说明</h2>, H; ]+ w& h1 u& V$ H5 c
<h3>1. 完整语法</h3>! r7 i: @6 T7 y7 a
<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>5 w% C/ E5 G+ H4 N6 k
<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>* H2 q% A4 f" F) w
<th>数据类型</th>
. R% S+ O7 f1 U; ?6 m5 O4 _$ o<th>核心作用</th>
* H- s2 y- m. X' x0 _* G8 Q<th>注意事项</th>$ z, i" e) U' Q' X9 d/ y0 D
</tr>+ q; X% ]! ^( M" R; f
</thead>
# ]; V3 a' [) o1 v. G7 S( v<tbody>1 u' D+ o8 ~, {
<tr>& E R# r5 c9 r. E' b$ N
<td><code>rows</code></td>8 t0 f7 }$ t# j; t/ J- t
<td>必选</td>
J) ?( E, J- X6 h<td>单元格区域/数组</td>
6 M9 U2 @+ S* }4 b# n5 a<td><strong>行标签数据源</strong>:用于分组的行维度字段(可单字段/多字段)</td>$ |* ~. v5 d/ f7 e
<td>多字段需用 <code>HSTACK</code> 合并,如 <code>HSTACK(部门, 季度)</code></td>
~) ~2 u% w$ l d0 }7 n/ K- R</tr>( Z3 H# O b. L+ A9 H
<tr>- ^3 ]- o: m; w& ]- O N
<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>""</code>)表示无列分组</td>; `' {' k) Z- N$ u) n6 l8 S1 H
</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>) ~' B6 s% ?, ^' @6 I
<td><strong>值区域数据源</strong>:需要聚合计算的数值字段</td>. S9 F2 ?+ E6 C) M! v
<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># F# p; W0 \5 S
<td>文本/函数</td>, p" U2 R! |7 I
<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>3 a- L6 k: f& l
</tr>2 k( k. {0 F3 u5 ]3 ~
<tr>( E+ q6 I* g. P, `
<td><code>[row_total]</code></td>
! h9 X, D: f. k1 j; `! K% v; n0 U5 X4 u<td>可选</td>+ {" j( O& @( S8 H
<td>逻辑值/文本</td>9 M3 N5 O! \. F4 @- h( t, R
<td><strong>行总计开关</strong>:是否添加行总计行</td>
+ R' h: w; t8 d7 }$ [<td><code>TRUE</code>=显示总计(默认名称“总计”);<code>"自定义名称"</code>=自定义总计标题;<code>FALSE</code>=不显示</td>& M0 I9 o- R& H
</tr>2 W; i8 U/ G. ^+ r2 a
<tr>4 J# y: X k! a8 Z6 g
<td><code>[col_total]</code></td>, [- G; M( }$ A- D, P
<td>可选</td>
2 N- J0 Y- p/ Z0 g<td>逻辑值/文本</td>2 o2 s+ N; X5 v7 b* ^+ ~
<td><strong>列总计开关</strong>:是否添加列总计列</td>
) d5 Q% ~/ ^% @2 d" V" [<td>规则同 <code>row_total</code></td>2 @9 s' q5 f7 x& q" c4 ]
</tr>
# M3 t' ^1 v) a<tr>: ^( d0 H- u, ?6 ~" A4 ^' K
<td><code>[filter_array]</code></td>5 E" e* Y2 g: I/ e2 w6 `: k. O E7 y5 h
<td>可选</td>: f6 H1 h7 a* U3 Y$ A8 j& }/ E9 c
<td>布尔数组</td># H+ \6 f4 o- q- p- p; F4 B. E, y
<td><strong>筛选条件</strong>:按行筛选数据源,仅保留 <code>TRUE</code> 对应的行</td>; c. _, t& H3 |. Q1 C8 y- k
<td>数组行数必须与数据源一致,可结合 <code>FILTER</code> 函数生成筛选条件</td>
}% m# m7 J- K- t, S* t# x</tr>- T# S% @( k5 G/ f# l
<tr>2 s d Y0 h# d4 ]
<td><code>[drop_empty]</code></td>! G; [ ~0 q6 b- z
<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>0 O8 }9 f% t f) _: N7 D
</table>
# c( Q+ c/ O k- u) B<h3>3. 关键特性</h3>: G" V5 p1 g# s/ T5 E% N
<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>- J' c' [" k4 B6 \: N$ p
<h2>二、数据源准备(通用示例数据)</h2>
$ d! P, A4 w& B8 N5 b<p>为统一示例,使用<strong>销售数据表</strong>作为基础数据源,共 6 行数据:</p>$ b# H+ |, h* x) ?: ?+ Y/ @# S
<table>% V+ a2 U# r! h/ q
<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>7 }& W, c/ s8 W9 C9 K* h
</thead>
$ \+ s7 U/ c, {0 Q6 H( z4 o i<tbody>
% M2 f/ N; N1 ]<tr>3 x! z$ c; w! D$ I1 c
<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>. Z0 R) ]$ c( m0 f& _3 w- T
<td>10000</td>$ d y; Z! n ^6 G
<td>2000</td>
p, V7 ~; \ I! Y</tr>" }' R4 P, F" K- T. V# ]
<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>! b( {/ \4 }) K( |
<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>) O- G5 _. [( S8 s5 j; @
<td>Q1</td>
6 T) J9 o- B0 n<td>8000</td>
R. |( B) n3 P/ ?1 w+ L<td>1500</td>& {. l$ m( M2 G& v/ ~( I+ H' M3 M
</tr>
# o) i* ^" I% t# V<tr>; b( l4 }- D& Q# |) I0 U* v! W
<td>4</td>1 S1 n5 ^) ^- C! ]) L- ]2 f! o, X
<td>技术部</td>
4 _! a( k: V+ i3 t5 {<td>Q2</td>
0 F5 v* F; Y. L4 E( p<td>9000</td>) X ~% j3 j' a) y( i
<td>1800</td>( q- U3 y0 F: ~* b8 e( |
</tr>& a/ p/ J4 g3 @ i$ F7 @/ z
<tr>& C; \8 ]- W# Z1 o9 y* m
<td>5</td>0 ` o C: L: ?* I0 ^- v ]0 W4 c
<td>市场部</td>( P6 t. `$ x/ ^& Y" C# ?5 r" C# e' K
<td>Q1</td>, z) `( w0 `% l1 p& j
<td>5000</td>
5 P. ]! f; {& K6 t<td>1000</td>% q1 _( [) }' j: y$ n
</tr>* f. E( v9 S9 _( p
<tr>9 k5 d0 T: }7 S# i- `, g
<td>6</td>
/ R( a7 n. i7 A( L<td>市场部</td>, m, f; [ P% d! f
<td>Q2</td>9 @8 `* Q) I% G
<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>- N' q7 ^8 }4 H; W* q
</table>* y* o/ ^) k0 {4 V
<p>后续所有示例均基于此表,数据区域记为 <code>A1:D6</code>。</p>
2 X6 z- ?4 Y: c. W<h2>三、初级用法:基础单维度透视</h2>: O4 }* [ w- e9 X4 M4 N: p
<h3>场景1:单一行标签 + 单一列标签 + 单值聚合</h3>
7 i+ D* t2 d& W( M- M+ B. O! ?<p><strong>需求</strong>:按「部门」分组(行),按「季度」分组(列),统计<strong>销售额总和</strong>。</p>% Q0 |' N7 y+ [/ R) a7 u
<h4>公式编写</h4>
2 \) w0 u' |) L<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, "SUM")
- m9 c0 w5 o% E a$ B</code></pre>
# \$ t$ o8 Z! _3 Q4 I1 j<h4>参数对应说明</h4>2 V/ @6 R7 }& a" k
<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> C" Y; |4 Y$ B
</tr>' Z: I. a4 a2 Q9 E: @( ^2 L
</thead>
* J- a4 X6 v; p<tbody>* x9 a n: R t9 h. W2 @: f
<tr>* [. _( t6 P/ Z: N8 A% N3 X* r
<td><code>rows</code></td>* n- A% J ~& G# ?8 [. V `
<td><code>A2:A6</code></td>5 F1 d* H1 x. i9 Q, d
<td>行标签:部门(销售部/技术部/市场部)</td>
" E& A: q8 E+ G. \3 w1 i: ?0 o6 e</tr>. |0 }! c$ A3 v/ l! u* i' `
<tr>8 D* P- y# z6 C: C) ^# \) S, e
<td><code>cols</code></td>9 H$ r% |) E. Z" K4 a, G2 D
<td><code>B2:B6</code></td>
) u# u2 r! r, \<td>列标签:季度(Q1/Q2)</td>
+ L& P8 m7 k7 v( r</tr>9 c/ k8 J: z f* u5 c; n& g% ~
<tr>) G2 ~$ c! x4 M
<td><code>values</code></td>' R2 ?1 |$ T3 g8 U. n
<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>' q) j; M6 j& v, u
<td><code>"SUM"</code></td>9 k; x# W$ N. a
<td>聚合方式:求和(默认可省略)</td>
8 A* v/ a- I' y$ O) g6 R</tr>
: { o. Y m" h+ ~/ U( _7 q</tbody>" N& }9 i+ C+ q$ D" ]& i
</table>+ S* ^- o9 P% H ?
<h4>溢出结果</h4>8 Y3 R1 X7 g4 H" o2 k
<table>
6 C9 T, }0 A7 ^/ R4 E<thead>* l* Q$ K2 O& ?& s+ |& ]! e' u5 a
<tr>
* q8 ]% o4 j( x3 f8 r8 l<th></th>; h3 ^& J: v/ @
<th>Q1</th>- d: ~. s+ O$ F! S4 T8 {
<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>! ^7 q3 D" {' W& a" Z) ]( J. C
<tr>$ p; I0 G$ ]9 R) T$ W2 k+ j3 J* ]
<td>销售部</td>3 a( M/ t7 E* i& X5 n
<td>10000</td>* h+ t5 {+ Z- F$ k& `4 Y m
<td>12000</td>
7 G7 i( D D7 \; y</tr>: K' G6 H# d" q9 s, C6 W
<tr>" m+ W3 c' Q- T E
<td>技术部</td>0 e5 b# z# q% }% R$ D
<td>8000</td>
0 P6 @- x" ]+ E8 v<td>9000</td>
% g/ c, e" j5 p</tr>2 S5 r* \& c: i6 o+ z2 _$ N1 o8 H
<tr>0 |2 J8 L7 S/ `' V1 {' i
<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>2 t$ C% m+ d g
</table>, W. }3 i6 |) s2 X
<h3>场景2:无列标签(仅行维度聚合)</h3>, }. N$ M/ T% ^) ~3 d
<p><strong>需求</strong>:按「部门」分组,统计<strong>总销售额</strong>,不按列拆分。</p>
: q8 a, d5 J1 F7 X<h4>公式编写</h4>' u/ v- @, E0 c+ o: r" c
<pre><code class="language-excel">=PIVOTBY(A2:A6, "", C2:C6)
( [& e0 j/ `. @( I2 W</code></pre>
& t. ?9 f i* S: `9 \' F<h4>关键参数:<code>cols=""</code></h4>
! s0 d% t" y& [) W( B4 q<p>表示不设置列标签,结果仅输出「行标签+聚合值」两列。</p>3 |% {/ S O4 ^' P- S6 i6 i9 M
<h4>溢出结果</h4>' O0 s( c& J6 t1 x% a9 e
<table>. y9 s) A2 ~1 M
<thead>) {9 y6 @. X5 ~- R) }+ r$ _& [; C
<tr>+ |& w5 v/ Y$ k; a- O
<th></th>3 D( e$ Z) b: {4 I0 H1 e$ i9 \
<th>销售额</th>
. x8 S( F, K: n4 _7 G</tr>1 T! O# j1 j4 r# b& m/ z/ i
</thead>
- Z) P9 O6 z2 t<tbody>' m" m0 h5 s: N9 V
<tr>$ v: t) p" H4 r$ K9 ?2 Y
<td>销售部</td>
# L9 ^' g8 f a9 q# o: X& V<td>22000</td>
: t% [7 b$ g, a: a# J! {8 ^% m</tr>3 Z' _3 }7 x0 p$ l' f+ o2 [
<tr>
# @ d, I# Y! H \( b! v/ Q& C$ U6 ^<td>技术部</td>5 |8 A6 H6 B) o: u1 H
<td>17000</td>
$ w6 O% B% A. J</tr>3 P* Q2 ]0 Z) h* m z
<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>( n5 j4 f9 C$ l1 }* x N
</tbody>
0 E' f M$ c7 W5 C# R: m</table>7 A5 d% v( }+ c( i5 a0 \5 y3 d
<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), "", HSTACK(C2:C6, D2:D6), "SUM")
# a. N+ J" g) I</code></pre>& V1 C- f7 }2 B9 m( ?/ \
<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>. s3 \) }6 h1 Q, J+ X9 n C
<li>聚合方式 <code>SUM</code> 同时作用于两个值字段。</li>) H B3 H! }: A9 ]8 ^( I
</ul>
1 q$ P0 u9 v; n- N9 p<h4>溢出结果</h4> R( T0 b) l8 U* u5 P g* n
<table>3 \' Z, j" y) _" |, u0 q& n
<thead>: r9 @" T) o7 L
<tr>4 h- F. d3 b z1 @( c8 n
<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>2 w- T' A; e' K8 s
</tr>
$ g/ p1 H: r( T( d& u) p</thead># c9 r1 ?! ]8 P4 i# o8 _1 [
<tbody>$ v% p! h4 C# P! B
<tr>) ]2 x! @8 F& e1 Q
<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>0 q+ B) L) ]7 T' D. v; J0 `
<td>2000</td>
4 B8 O" F% V% \% R! }</tr>7 ~1 C0 ]/ r9 M( e3 c/ L. m; E" w2 A
<tr>
; @- j8 F* r9 C<td>销售部</td>
! P3 v* U g$ Y: s( R2 d9 n<td>Q2</td>' i& p# F u0 V! x( ]
<td>12000</td>
" `1 M" h+ V# `& W( _<td>2500</td>& p2 V/ E% s+ s( `$ d/ @ v
</tr>4 n, }6 @( v6 i
<tr>. y% G1 Y/ U3 s& _8 C% ~
<td>技术部</td>% S" ?; j0 @/ i, v7 \4 |
<td>Q1</td>
" n6 J& ?" B7 ]<td>8000</td>
6 l3 o* i/ C& D5 w# i3 ^5 h<td>1500</td>3 u. K2 D' i, N9 j S* p
</tr>! a8 w4 e$ [3 t% V& ?4 r
<tr>
R4 {8 X- |8 L0 F<td>技术部</td>8 W/ @, z6 n h5 T* t. Q' A
<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>- z/ l( d( u+ A- a1 x) J
<td>Q1</td>' A! U7 p6 I& r: |( x
<td>5000</td>: N( W- f5 @ w2 c% O7 x# l/ T
<td>1000</td>
! b' q7 [) \0 t$ j7 D. g</tr>
, j* l, S2 d2 A0 k0 f E<tr>9 C/ t; H8 u1 {# c& E
<td>市场部</td>; B- H; D+ F# }0 B" X1 J8 D9 h
<td>Q2</td>
. e1 E& r0 m; c<td>2000</td>
0 X! J8 F+ H% r+ ^' ], n<td>300</td>% w2 b# f; D* u/ n$ L
</tr>
~" D9 F' L( _</tbody>
+ m" G# X/ |" {: i</table>, w+ |7 I8 E1 B3 S8 R# o' s' X
<h3>场景2:添加行/列总计</h3>% X" l$ u! Q/ Z w; P Q# R
<p><strong>需求</strong>:按「部门」行标签、「季度」列标签统计销售额,同时显示<strong>行总计</strong>和<strong>列总计</strong>,自定义总计名称为「合计」。</p>$ M% O% x% \2 E% R$ W0 H/ K
<h4>公式编写</h4>
/ h' q( s6 i, Y9 r<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, "SUM", "合计", "合计")4 X0 v1 S4 [/ O8 l! u
</code></pre>3 _& P* {. T/ S0 g
<h4>参数说明</h4>8 P8 ]7 V1 k9 E4 `* ~# n2 b. P
<ul>" `1 I& t O7 x5 z; Y9 S9 H- M# E
<li><code>row_total="合计"</code>:行总计标题为「合计」;</li># E5 z6 d) y0 ^1 h) x( ?+ _
<li><code>col_total="合计"</code>:列总计标题为「合计」。</li>5 X3 O% j& _$ }9 M* }) y
</ul>
& D! M# T4 H1 g/ K<h4>溢出结果</h4>2 k% r, L) Y* C. b$ w
<table>
2 Z; G7 Z5 c2 r0 d& ^<thead>0 H# K, c# b* ]* g( S
<tr>! r+ | N) V0 }2 o
<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>& F4 u) g6 H% t+ P5 a: c; M
<th>合计</th> L( p% W0 }% v; U
</tr>0 `1 ?& w! }8 F' R
</thead>8 Q( O( O7 C/ Y3 L+ y
<tbody>, Y) R u& }3 l; ?! P, |
<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>) ?; E8 h( F* P2 ]4 m( S; D
<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>" t- a. ]& {1 d B5 |
<td>17000</td>
) v6 { J) A$ a6 t8 D8 h</tr>5 _$ `: E# D) |- K: E- H
<tr>
0 S( t3 j; q8 e% T* }% r<td>市场部</td>& |4 }* a9 d+ P- h" Y! Y
<td>5000</td>+ u$ _( x! b6 J
<td>2000</td>
1 l. I, J& J1 @# f. ]<td>7000</td>
3 O, x8 W2 ?6 p</tr>2 J2 O$ S+ S2 p7 u6 O
<tr>% P: g* S6 Z: j" e, G/ S1 b" R
<td>合计</td>
^- z; u4 G! |& l<td>23000</td>( i: R. ]" m6 z( t& d, c& n' g2 [
<td>23000</td>' | R5 A, H- B j4 R5 ?
<td>46000</td>
, \* d3 y- M8 S W' i0 f) G0 _4 J9 o5 l</tr>
. ]( }, f1 ]# h4 ?</tbody>. H! ~% X( J; P0 f8 a2 ?3 y
</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>; V" B' R; S. W1 g
<h4>扩展数据源</h4>
' j( a" `- M! z q( t; o<p>在原数据后添加一行:<code>A7=市场部, B7=Q3, C7=""</code>(销售额为空)。</p>" c& G% C$ I5 [+ x3 n
<h4>公式编写</h4>6 w L8 i+ N' ~6 m/ |, T: E
<pre><code class="language-excel">=PIVOTBY(A2:A7, B2:B7, C2:C7, "SUM", , , , FALSE)8 v( R [1 _9 S" I, i* _) O+ Y
</code></pre>5 M. }5 G3 n' L
<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>. z% o9 q p" x) Y$ w: F* y
<tr>
0 x5 u' Y0 S" v& P, b# @<th></th>, @7 L+ y9 \8 l! [- B( B+ F$ D
<th>Q1</th>
" m2 j$ M$ X! _<th>Q2</th>" C6 y5 c) n2 O
<th>Q3</th>
2 r) I# ]' f* D7 @7 z/ i# ?# x( T</tr> z, q* Q% u; n8 \7 _" W
</thead>5 S! l, G/ O& ^
<tbody>& n: ?- ~6 x# Z3 U" l9 ~: Z5 b
<tr>9 B e1 {5 k0 k$ o3 r
<td>销售部</td>1 d. ~6 f9 a; ?/ v: w- B% h" I
<td>10000</td>
9 I( o1 y) o4 z' t: o3 s1 p# b<td>12000</td>1 @1 p' Z( P( c" }3 M! o
<td>0</td>
4 z/ Z* \+ W. t0 E) m0 ^1 \; e</tr>& g) C" d3 @& ^2 r: N, s$ R3 Y
<tr>
/ x/ m9 M6 {2 j2 S<td>技术部</td>, Q- N. ]. Y7 I, N
<td>8000</td>. F7 @0 {- p6 l
<td>9000</td>
0 y- O" J$ J7 u; Q1 Z<td>0</td>; }0 s7 y$ C8 U" K8 r K# ]
</tr>' D a) d- w" O/ C- }
<tr>/ g& N3 ]) B8 Z* r2 F2 R# z
<td>市场部</td>7 [1 x6 W8 S% q/ h3 a. s
<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>. U# n" i) |( v, O+ I0 B8 [5 l4 p
</table>
^" _3 }8 ~& H# {1 N2 s<h2>五、高级用法:筛选、自定义聚合与函数嵌套</h2>" p$ g0 r" y0 l! f3 k
<h3>场景1:结合筛选条件(<code>filter_array</code>)</h3>/ ^/ a" c' H4 J, [- Y
<p><strong>需求</strong>:仅统计<strong>销售额>6000</strong>的记录,按部门和季度透视销售额总和。</p>( t4 `6 ]9 G' N8 v
<h4>公式编写</h4>2 A& F2 y' m, l2 h: t; y. H4 W
<pre><code class="language-excel">=PIVOTBY(A2:A6, B2:B6, C2:C6, "SUM", , , C2:C6>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>6000</code></h4>
' m, b8 u, X/ K S9 w d<ul>2 z+ J' N* }4 k: e" m
<li>生成布尔数组:<code>{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}</code>;</li>
, g. n# g5 ]: \0 e* B( F0 k<li>仅保留数组中 <code>TRUE</code> 对应的行(即销售额>6000的记录)。</li>
% ~% G7 ~" F( u1 j</ul>
! E( }( G8 u- Z+ `" p" O5 F<h4>筛选后数据源(实际参与计算的行)</h4>; Q" h; p" w9 v: ?/ B
<table>
1 B+ r+ B% @0 q5 {3 u2 s" z l<thead>, B4 d9 {" B' i/ t
<tr>& t7 F+ N9 ?6 p8 V: o: P% L
<th>部门</th>
( ]9 D7 l* E$ A<th>季度</th>
' D' k5 }0 w4 P! O<th>销售额</th>
# z& s6 f+ F. }* Z</tr>. _8 o9 J O! z
</thead>- _! @) {. L; D) w) P
<tbody>& @3 }; b4 A# V# f# S. Z
<tr>
. x. \1 z7 e! ~7 ~<td>销售部</td>
1 }' D6 a8 e9 s/ S; l; F- W% p! J<td>Q1</td>: F l; O- Q$ F5 Z
<td>10000</td>* c! r& N" c! P4 P$ E' r
</tr>8 R& H7 e5 Y+ P+ r O8 \& [- X, [
<tr>1 V% M0 w8 w- N6 e2 t6 u
<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>; M1 C8 i6 C+ w0 y' ]
<tr>% E6 G' J+ c' v* B
<td>技术部</td>) @- Y0 T i1 z/ z6 z
<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>/ `- R: _7 ]+ u' n
</tbody>9 J' k3 T- E; S* l& i9 E
</table>
8 j. c& I& s& j; I1 u<h4>最终溢出结果</h4>
* o- s" W# p6 a" P! F. _: e" Q<table>+ U( L* |, _: J, P
<thead>, F) ], i0 l3 ]% p6 f
<tr>
, p! n, `% {; R- D8 Y' w G0 [<th></th>
8 C- ~2 a. l6 A$ b7 p<th>Q1</th>. x4 O2 i. S! x
<th>Q2</th>
% A/ `/ O9 K3 n</tr>1 K3 f7 `4 N0 f8 i0 k( G
</thead>9 g( E: d+ M( m/ }- u7 x/ Y
<tbody>0 W- h2 e: P2 O5 A! ?
<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>6 a: H& j! A; c7 q
<tr>
; N! o- k) y; s4 c. Y<td>技术部</td>! \3 j5 k8 O7 G# z; Z' X, N6 B
<td>8000</td>
3 |, Y3 u% p6 a; t, g9 L<td>9000</td>) v: K; L7 f8 v. x# W8 H$ V
</tr>
& D& {3 C$ ^% `* [, p( @7 V8 W3 {</tbody>7 W! W: y* m& O5 \) r [' Q
</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>0 `! n4 ]$ C% ]% R2 } C# T
<pre><code class="language-excel">=PIVOTBY(A2:A6, "", C2:C6, LAMBDA(x, STDEV.S(x)))
6 f( m, f' L* t& i8 Q/ x" _</code></pre>% t3 _" ?" I. {8 _6 O8 E1 c
<h4>参数核心:<code>agg=LAMBDA(x, STDEV.S(x))</code></h4>4 j+ ^" T; [- C5 I5 ^4 ^
<ul>1 Z# }, |9 Z+ p7 `9 V5 p7 [
<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>% V" g9 u# `- K
</ul>1 `4 c" Z* T- q
<h4>溢出结果</h4>
! z. J( C1 x0 x. [<table>
p; |3 H$ F" |" E- T% N7 p<thead>" r8 q# k) ?. J: |" Q q! E
<tr>
$ S# H/ F; D1 L7 j, V# A1 s2 L<th></th>3 Z2 z( a, T# |0 ?
<th>销售额标准差</th>9 b# F% g- C7 z2 X
</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>6 ]+ i1 J( l/ w( G% h0 R
<td>1414.21</td>* I4 U' J. U+ F
</tr>
0 {) T2 ]( O9 O @% }7 G<tr>% L/ z/ q+ w+ o# B
<td>技术部</td>8 c0 i* l, W6 E% I- M. b& I" l- X3 b$ r
<td>707.11</td>
. R+ C( j& ~# z/ `& X0 Y</tr>4 x$ r) `. _( e1 B8 p
<tr>' e2 P; w9 D" L2 u- r/ w' @& W
<td>市场部</td>
7 c' J! T6 K, x# B<td>2121.32</td>
% |) }! E) J' G5 n</tr>5 l) A( p( U4 H$ j
</tbody>
/ i" K8 q# B: Y) g( t</table>
* K: ?! W/ c9 ~# u6 [<h3>场景3:与 <code>SORT</code> 嵌套,对透视结果排序</h3>* h* k6 L8 \, B) b n+ l
<p><strong>需求</strong>:按部门透视总销售额,并按销售额<strong>降序排序</strong>。</p>( u" W# ^% _5 v8 {
<h4>公式编写</h4>" ]! s, w! P1 `' N$ Z
<pre><code class="language-excel">=SORT(PIVOTBY(A2:A6, "", C2:C6), 2, -1)
9 c5 Q4 ~( V! l o0 Q( Y# Y</code></pre>+ h) @, W+ F9 [/ [9 j
<h4>嵌套逻辑</h4>" n" e# v, w$ |
<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>2 N) X- x: p: V9 [+ J6 o. B) m
<table>- V' m! D3 d# k
<thead>- G0 O0 e5 ]9 l8 o: X I
<tr>
, K8 }! X. {* Z+ C% l. I; ?4 P<th></th>6 m# C) S% j- v3 j
<th>销售额</th>
: D* q+ q/ e! F* [7 K* A</tr>
6 y, R( H1 I& a! U</thead>& ^( G! n* b6 o/ `$ j: c
<tbody>
0 Z8 X1 @# q2 ^2 [$ ?, [<tr>
+ {; Z, `0 j! u9 p/ I5 K5 [* u4 N<td>销售部</td>5 b/ }! ?/ w8 Q$ \( {& M
<td>22000</td>$ O1 B Y, Z7 z* O" K8 w* O: j6 a' R
</tr>, }3 s2 e( ]5 q* F) P" y
<tr>
& L# q% O. J/ r0 @<td>技术部</td>
9 q* b1 _* c- W6 _7 `4 w* s! o, y<td>17000</td>) d; `. T7 y- r0 r
</tr>
! u+ D( z% ~$ P8 R- g ]<tr>5 L& s7 A) @8 A8 k7 u2 d% |6 S R$ Y
<td>市场部</td>- S. z% M" D( }
<td>7000</td>
* G" }4 r$ K3 R5 d/ A# b: ]</tr>
: I4 ]7 h6 |, z& p' E</tbody>8 @# N- Y" r5 Z
</table>
# w2 O/ Z; U1 B- V- C<h2>六、常见问题与注意事项</h2>: g" c, l, y% C2 G6 y
<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>& d3 u: L) F M/ {
<li>原因:结果区域被其他单元格内容阻挡;<code>rows</code>/<code>cols</code>/<code>values</code> 行数不一致。</li>
$ ?- v4 j+ u5 v0 l! d0 g<li>解决:清空结果区域的空单元格;检查数据源区域行数是否匹配。</li>& c0 t% y4 p$ s. n9 I' B% ^
</ul># }' D4 `7 D( e) e3 R
</li>6 E$ e) r4 S/ ]! o& m
<li>7 l# e4 F+ t! u+ L
<p><strong>聚合函数不生效</strong></p>
9 t7 N! u3 [+ C* \( e/ H+ n d. I<ul>' N: ~( F! D0 s% n
<li>原因:<code>agg</code> 参数拼写错误(如 <code>"Sum"</code> 写成 <code>"sum"</code> 不影响,但需避免错别字);自定义 <code>LAMBDA</code> 函数语法错误。</li>* ?& ]1 u8 Q% p; R8 {; h
<li>解决:核对内置聚合函数名称;调试 <code>LAMBDA</code> 函数逻辑。</li>
$ X5 Y3 ?- }1 w o</ul>' X8 t9 a. r& N4 H# b
</li>" j5 i5 o' p6 u4 z( M. H
<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>: |0 {% l* S" P' R7 F5 v8 B
</li>
1 _, @0 ?* ]/ E/ C<li>, d1 }. O. d9 v/ Y4 s, |. U3 E
<p><strong>性能优化</strong></p>0 S6 L' U( t* ~: v" c
<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>- |; F, J& K# r) [) M
</ul>& B/ j* m% f0 u6 ^- N0 G/ F
</li>* L4 E, ~! k9 e8 m
</ol>5 T* O! K T' N1 C* \/ ~
|
-
温馨提示:
本文《Excel动态透视表--PIVOTBY函数初级到高级用法》由: 打工日常 发表于 2026-1-9 09:51
原文链接:https://www.jiangmen.pro/thread-128-1-1.html
- 1、本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2、本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3、本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4、未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5、匠们网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6、下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7、本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
- 8、该内容可能包含由AI辅助创作,请仔细甄别。
|