超越sum, 告别繁琐累加! 用scan函数体会"收入支出"算"累计余额"的场景

Excel情报局

用案例讲Excel

探索挖掘分享Excel实用技能

SuperExcelMan

用1%的Excel基础搞定99%的职场难题
做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万表格爱好者聚集地

同样的收支数据: 左边是收入，右边是支出，但最后那栏"余额"总是让人头疼。有的小伙伴用10分钟手动计算累计余额，有的小伙伴用10秒自动生成结果。差别就在于是否掌握了正确的公式方法。

累计余额计算看似简单，实际上隐藏着引用技巧、函数组合和计算逻辑的奥秘。手动计算，容易出错；简单累加，不够智能。在Excel中只要一个动态数组公式，就能让累计余额自动生成、实时更新。掌握这项技能都能让我们的数据统计事半功倍。

下面我们通过从传统方法到动态数组公式方法的逐步过渡介绍，来体会不同方法的优点，并学习新的动态数组公式的变革性处理思路。

一)传统思路的第一种写法

假设列标题C1单元格"余额"处是0或空值单元格:

我们常见的写法是这样的, 这个公式比较直观:

`=C1+A2-B2`

得到第1天余额后向下下拉填充公式即可。

记住这个公式写法一定要建立在C1是0或空单元格基础上。每一天余额的运算过程给大家详细写一下, 可感受并理解:

第1天余额C2:

=0+A2-B2

=初始余额0+收入A2-支出B2

=0+500-100→400

第2天余额C3:

=C2+A3-B3

=上日余额400+A3收入-B3支出

=400+600-0→1000

第3天余额C4:

=C3+A4-B4

=上日余额1000+A4收入-B4支出

=1000+0-300→700

第4天余额C5:

=C4+A5-B5

=上日余额700+A5收入-B5支出

=700+500-200→1000

但要注意：

本例中和大多数的办公场景中，C1一般是列标题"余额"文本(或"期初余额"等表示)，用上面这个公式会出错，因为文本不能与数字进行四则运算。可以用N函数解决。

二)传统思路的第二种写法

N函数用于将非数值类型的值转换为数值形式。如果需要转换的值为数值, 则返回原数值本身；如果需要转换的值是文本, 则返回固定数值0。

所以我们这样设置公式即可：

=N(C1)+A2-B2

得到第1天余额后向下下拉填充公式即可。

这样即使C1是文本，N(C1)也会返回0，而不会报错。它的每一天余额的运算过程和上面的第一种公式的效果就一样了：

C2第1天余额：

N(C1)+A2-B2=0+500-100=400

C3第2天余额：

N(C2)+A3-B3=400+600-0=1000

C4第3天余额：

N(C3)+A4-B4=1000+0-300=700

C5第4天余额：

N(C4)+A5-B5=700+500-200=1000

三)传统思路的第三种写法

这是传统公式中计算累计余额的标准方法:

=SUM($A$2:A2)-SUM($B$2:B2)

得到第1天余额后向下下拉填充公式即可。

第1部分(累计收入): SUM($A$2:A2)

$A$2: 绝对引用, 起始单元格固定为A2

A2: 相对引用，结束单元格随公式所在行变化

当公式在C2时: SUM($A$2:A2)→ 计算A2:A2的和

当公式在C3时: SUM($A$2:A3)→ 计算A2:A3的和

当公式在C4时: SUM($A$2:A4)→ 计算A2:A4的和

当公式在C5时: SUM($A$2:A5)→ 计算A2:A5的和

第2部分(累计支出): SUM($B$2:B2)

$B$2: 绝对引用, 起始单元格固定为B2

B2: 相对引用，结束单元格随公式所在行变化

当公式在C2时: SUM($B$2:B2)→ 计算B2:B2的和

当公式在C3时: SUM($B$2:B3)→ 计算B2:B3的和

当公式在C4时: SUM($B$2:B4)→ 计算B2:B4的和

当公式在C5时VSUM($B$2:B5)→ 计算B2:B5的和

第1部分与第2部分: 相减

累计收入-累计支出=累计余额

第1天余额：

SUM($A$2:A2)-SUM($B$2:B2)

SUM(500)-SUM(100)=400

第2天余额：

SUM($A$2:A3)-SUM($B$2:B3)

SUM(500,600)-SUM(100,0)=1000

第3天余额：

SUM($A$2:A4)-SUM($B$2:B4)

SUM(500,600,0)-SUM(100,0,300)=700

第4天余额：

SUM($A$2:A5)-SUM($B$2:B5)

SUM(500,600,0,500)-SUM(100,0,300,200)=1000

大家有没有发现，以上的传统方法都是设置单个公式的方法，需要下拉填充，中间删除或新增行数据时，会影响余额的正确性。

下面我们就尝试学习用scan函数计算每日余额。

A2:A5-B2:B5:

这个区域差的计算会先得到每一天的净现金流: 即"收入-支出"。它会产生一个新数组：

第1天: 500-100=400

第2天: 600-0=600

第3天: 0-300=-300

第4天: 500-200=300

所以，scan将要循环遍历的数组就是每日净现金流:

{400; 600; -300; 300}

累加规则: lambda(x, y, x+y)

lambda用来设置一个匿名函数, 并定义变量及其运算规则。

x: 代表"累积值"。在本例中我们可以把它想象成一个每天更新的"余额总计"。

y: 代表"当前正在处理的这天的净现金流"。来自上面(A2:A5-B2:B5)生成的数组。

x+y: 这就是规则。将当天的净现金流y，加到当前的累计净现金流x上，得到新的累计余额。

所以scan函数最终可以这样写：

=SCAN(0,A2:A5-B2:B5,LAMBDA(x,y,x+y))

初始值: 0

这是累加的起点。从财务工作角度上看，我们可以理解为"初始余额"或"期初余额"。这里设置为0，表示计算从零开始。

要循环遍历的数组: A2:A5-B2:B5

A2:A5: 表示收入列, 值为{500; 600; 0; 500}

B2:B5: 表示支出列, 值为{100; 0; 300; 200}

A2:A5-B2:B5: 表示每天净现金流{400; 600; -300; 300}

执行逻辑lambda(x,y,x+y)

用每天的现金流y与上一天累积现金流x相加，得到每天的余额。

总结一下: scan函数就像一个不知疲倦的会计，从0开始，拿着你每天的"收支单"(A列-B列)，按照"老余额加新变动"(x+y)的规则，一行一行的为我们更新账本，并把每一步的余额都清楚的记录下来。

如果觉得抽象，小编整理了模拟计算过程：

第1天

初始余额x=0, 循环数组取第1个值y=400,

x+y=0+400=400, 则C2余额返回400

第2天

上一步结果x=400, 循环数组取第2个值y=600,

x+y=400+600=1000, 则C3余额返回1000

第3天

上一步结果x=1000, 循环数组取第3个值y=-300,

x+y=1000+(-300)=700, 则C4余额返回700

第4天

上一步结果x=700, 循环数组取第4个值y=300,

x+y=700+300=1000, 则C5余额返回1000

最终, C2:C5得到的结果就是:

{400; 1000; 700; 1000}

这个公式的巧妙之处在于: 用一条公式同时完成了两项工作：

①计算每日净额: A2:A5-B2:B5

②执行动态累计: scan(0, ... , lambda(x,y,x+y))

并且它的计算逻辑完全符合财务常识: 当日余额=上日余额+本日收入-本日支出。

与传统公式的对比: 如果没有scan函数，我们通常需要在C2单元格输入`=A2-B2`，然后在C3单元格输入`=C2+A3-B3`，再向下拖动填充。`scan`函数用一个公式就替代了这一系列步骤，而且当数据增减时，动态数组会自动调整结果范围，更加简洁高效。

学习Excel/如果你没有天赋/那就一直重复/当你快到本能反应的时候/你的重复就是别人眼中的天赋/冲破捆绑/展翅翱翔/回顾关键内容/善用图片表达/学会建立联系/拓展深度广度/浓缩关键概念/应用到行动中/善于归纳总结/尝试进行分享

map遍历 scan遍历 reduce迭代

pivotby降维byrow函数let函数

regexp正则提取：款式&尺码多组提取←

一个关于人口普查Excel户籍整理的问题←

一个关于人口普查Excel户籍整理的问题2←

regexp+vlookup根据简称查询全称←

regexp+countif+filter根据全称查找简称←

excel1秒批处理1000条混乱时间数据←

Regexp这个大哥级函数人狠话不多←

公式里常出现的"@"、"#"、"."是干啥的←