Excel情报局

用案例讲Excel

探索挖掘分享Excel实用技能
SuperExcelMan
用1%的Excel基础搞定99%的职场难题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气
数万表格爱好者聚集地


问题求助SOS：如何自动生成家庭序号？

如下图所示：
A列和B列是某社区的户籍数据。想要根据B列家庭关系的标识，在C列加序号，即为每一户家庭自动生成一个连续的序号。

以B列各个“户主”所在的单元格作为每一户的开头，该户主及其家庭成员，也就是说直到下一户“户主”之前的所有人共享同一个序号。遇到新“户主”时，序号自动加1。

在C2单元格编写公式，实现C列的效果。



了解完提干后，我们的任务很明确。这是一个户籍整理添加序号的工作：
自动为每一户家庭生成一个唯一的、连续的序号。同一户家庭的所有成员拥有相同的序号。

我们既可以使用传统公式或旧式公式实现，也可尝试挑战使用不同的动态数组函数，比如SCAN、MAP等实现同样的需求。使用动态数组公式后能自动将结果填充至整个数据区域，无需手动拖动。


我们可以都学习一下并比较其优劣。


方案1

SCAN+LAMBDA


我们可以这样使用函数公式：

=SCAN(0,B2:B9,LAMBDA(a,b,IF(b=”户主”,a+1,a)))


SCAN函数会遍历B2:B9区域的每个单元格。
0表示初始累计值，a代表累计值，b代表当前单元格的值。
从初始值0开始，遇到”户主”就在累加器a的基础上+1，否则保持当前值a不变，结果会自动溢出到C2:C9区域。

这是入门学习SCAN函数的一个非常经典的实用方案。



方案2
COUNTIF函数（非动态数组法）

我们可以这样使用函数公式：
=COUNTIF(B$2:B2,”户主”)
混合引用B$2:B2是这种处理方案的精妙之处。


B$2：起始点固定在第2行（绝对引用）
B2：结束点随当前行变化（相对引用）

下拉填充时的动态效果：
在C2单元格：
COUNTIF(B$2:B2, "户主")
统计B2:B2范围内的”户主”数量为1

在C3单元格：
COUNTIF(B$2:B3, "户主")
统计B2:B3范围内的”户主”数量为1

在C4单元格：
COUNTIF(B$2:B4, "户主")
统计B2:B4范围内的”户主”数量为2

…以此类推至C9单元格



方案3

COUNTIF动态数组化升级版

MAP+LAMBDA+COUNTIF

户籍序号遇难题，函数妙招不用急

我们可以在方案2的基础上将COUNTIF动态数组化：

=MAP(B2:B9,LAMBDA(a,COUNTIF(B$2:a,”户主”)))


①逐行映射：MAP函数遍历B2:B9的每个单元格。
②动态范围：对每个单元格a，统计从B2到当前单元格a范围内的”户主”数量。
③累计计数：随着遍历向下，统计范围逐步扩大，实现户主数量的累计。

运算过程大致如下：

B2(“户主”) → 统计B2:B2 → 结果1

B3(“妻”)  → 统计B2:B3 → 结果1  

B4(“户主”)  → 统计B2:B4 → 结果2

B5(“女”) → 统计B2:B5 → 结果2

B6(“妻”) → 统计B2:B6 → 结果2

…以此类推至C9单元格

这种升级实现了一个公式自动溢出所有结果，兼具了COUNTIF的简洁和动态数组的便利。


方案4
SUM函数（非动态数组法）

我们可以这样设置SUM函数的参数：
=SUM(–(B$2:B2=”户主”))

B$2:B2=”户主”：判断是否为户主



–(B$2:B2=”户主”)
–()：将TRUE或FALSE转为1或0。



SUM(–(B$2:B2=”户主”))
SUM()：直接求和，得到累计户主数量。



方案5
SUM动态数组化升级版
MAP+LAMBDA+SUM

我们需要这样更改函数公式：
=MAP(B2:B9,LAMBDA(a,SUM(–(B$2:a=”户主”))))

①逐行映射：MAP遍历B2:B9区域，将每个单元格临时命名为a。
②动态累计：对每个a，统计从B2到当前a位置的”户主”数量。
③数组溢出：随着遍历向下，统计范围逐步扩大，实现户主数量的累计。

运算过程大致如下：
B2(“户主”) → 统计B2:B2 → 结果1
B3(“妻”)  → 统计B2:B3 → 结果1  
B4(“户主”)  → 统计B2:B4 → 结果2
B5(“女”) → 统计B2:B5 → 结果2
B6(“妻”) → 统计B2:B6 → 结果2

…以此类推至C9单元格

这种方案的核心思路大致与方案3类似（MAP+LAMBDA+COUNTIF）。而本例结合了SUM方案的直接明了，具备MAP函数的一次性溢出优势，一个公式解决整个区域，无需下拉填充公式。这是静态公式与动态数组的完美结合。


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

对单元格部分文字内容替换 格式更改←

合并工作表，用两个函数就搞定啦←

比vlookup还强大的动态随机查询←

根据起始序号与终止序号写入内容←

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

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

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

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

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

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

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