lookup真强大！按指定次数重复内容并添加序号

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天要分享的内容是按指定次数重复内容并添加序号。主要用到lookup函数，这个问题还是挺有实际意义的，学会可以用来多表合并。

下面来看下具体的效果：根据左表的姓名和次数得到右表的结果。名字重复指定的次数，并且后面添加序号。

这个效果用函数实现起来还是有点困难的，公式也相对较长。不过掌握后还是很有用处的，可以实现多表内容合并在一张表中。

在D3单元格输入下面的公式，向下填充，完成。

=TEXT(LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))),A$3:A6)&LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))))-ROW(A1),";")

这个公式看起来很长，其实分为两个部分，用&连接起来。第1部分是按次数重复姓名，第2部分是添加序号。而且这两部分有重复的，只要理解了一个部分，另一个部分就好理解了。

先来看第1部分LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))),A$3:A6)，是个lookup函数。

第1参数ROW(A1)-1是个用row函数构建的序号，从0开始，公式下拉会变为1，2，3...

第2参数SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5)))是一个累加求和，返回的结果为{0;2;5;9;10}。相当于下图C列的结果。用的是subtotal和offset的多维引用，可以免去辅助列。

第3参数A$3:A6是姓名的区域，没什么好说的。重要的是这3个参数放在一起，lookup会返回什么结果。如下图C列所示，得到了按次数重复的姓名。

其实这个公式的关键是找到它的分界点{1;3;6;10;11}，你可以手工推算一下。第1个姓名重复2次，那就是1，2。第2个姓名重复3次，那就是从3开始，分别是3，4，5。以此类推，得到D列的数字。

我们只取分界点{1;3;6;10;11}，用{1;3;6;10;11}-1就是{0;2;5;9;10}。这个关系搞清了，这个公式就没什么难度了。

再来看第2部分LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))))-ROW(A1)，还是个lookup函数，但是后面要减row(a1)。

其中它的第1，第2参数和第1部分的公式是完全一样的。所以LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))))返回的结果如下图C列所示，按次数重复分界点。

LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$5))))-ROW(A1)是用上一步的结果减去row(a1)，返回的结果如下图E列所示，刚好得到每个人的序号，只不过是负数。

把第1部分和第2部分连起来的效果如下图C列所示。我们发现当所有的姓名重复完后，公式再下拉会出现负数。为了将这些负数显示为空，所以最后用了个text函数。最后的结果就如下图E列所示。

链接：

https://pan.baidu.com/s/13LWfdgMY7iu7j01PcN7vRQ

提取码：a1ao