浅谈 lookup+subtotal+offset 按指定次数重复内容

魚。。

Good evening,everyone! I am Fish.

      看到帖子就知道，这只魚又要开始唠了。今天的分享还是源于上次论坛小伙伴的帖子，解决按照指定次数重复内容的事。上一回和大家一起学习了lookup第3参数根据第2参数补齐的问题。因上边问题的原帖楼主的疑惑，想知道一下公式怎么运作的，还有后边刘卓老师的支持，今天跟大家分享一下lookup+subtotal+offset解决此问题的思路。

先来说说具体是个什么样的活要做：

      如上图需求，就是要按照给定数字次数，重复前边的名字。方法其实挺多的，比如辅助列啥的。（不是今天内容，我不知道）直接来说今天的事：

      首先，要把结果放在多行里边，列么还是那两列。行是要根据次数来定的，牵扯到序列的问题了，row又要登场了。然后利用lookup查找row生成的序列，返回对应行的内容。先看下思路吧：

      Lookup 第二参数每两个数之间的差值就是D列每次递增增加的次数，为什么要从0开始生成序列呢，主要是为了末尾问题，重复完最后一次之后，不能一直让lookup找最后那个人，所以多引用一个空白单元格，这样就可以让超过的部分匹配最后一个人下一行的空白内容。实现一个错行引用，具体可以结合分析内容理解一下。

      思路有了，现在关键的问题是，怎么才能构建出那6个sum呢？也就是sum的依次递增的区域。我能想到的就offset了，先简单说下

offset函数：

OFFSET(reference, rows, cols, [height], [width])

Offset(单元格区域，偏移行，偏移列，[扩展行]，[扩展列])

5个参数，后两个可省略。不过今天可不能省，我要用的就是扩展行呢。简单看下offset：

Offset还有很多强大的应用，这里就不多说了。现在我们用offset去处理一下之前思路里产生的6个sum问题：

      思路有了，可是我们现在脑海里想下这个画面：offset里边扩展的行数，我们用了6次，第一次1行，第2次2行，第3次3行……，第1次引用了1个单元格，第2次引用了2个单元格……。生成了6组内容，而且每组内容的数量还不一样。不规则的多维数据呀，这个活sum可真的接手不了，一看就傻眼了。这个时候就得来个高手帮忙了——subtotal函数。

      Subtotal 函数，该说它是个啥函数呢，应该算分类汇总型的。为什么今天要找它帮忙，它是有背景的。我们在Excel中用自动筛选，筛选出需要的数据，经常还会做一个自动求和，公式自动生成了：=subtotal(9,b2:b520)，表示对b2:b520处于筛选结果的数据进行求和。它的第一参数还有一组多100的，比如：=subtotal(109,b2:b520)，区别呢就是：如果b2:b520区域除了自动筛选操作的结果外，如果有手动隐藏的行，那么参数9的话，是包含这个隐藏行的数据的，也是一并求和的。如果109的话，钱多任性嘛，多得可是一张老人头呢。我都看不到你还统计你干啥，所以手动隐藏了的数据，109也把它筛没了。

      另外除了求和外，subtotal还可以统计其他东西呢，计数，大小值，平均数啥的。具体就不说了，因为我也不会啊。另外还有很多高级用法，比如第一参数用数组：

=VLOOKUP(2,SUBTOTAL({2,9},OFFSET(C1,ROW(1:20),,20)),2,)

   

      这个太难了，我不会的我就不说了，我怎么可能会知道里边subototal把c1:c20的内容改造生成了一个20行2列的1个数组，第1列是计数，第2列是求和，然后用vlookup找到第一列计数到2对应的第2列求和内容呢。不知道，不知道。

这里再帖个subtotal第一参数的表格，有兴趣的小伙伴可以自己研究。

      回到我们今天要做的活，既然高手已到，连工作表自动筛选，还有隐藏行内容的问题都能识别，这点多维小意思啦。我们用subtotal去把offset出来的那个结果给揽下来：

=SUBTOTAL(9,OFFSET($D$1,,,{1,2,3,4,5,6}))

      这样我们就得到了我们需要用来作为lookup第二参数的数组：

那么，既然第二参数确定了，第三参数返回的值，我们可以直接用单元格引用就好了。

Lookup通过在第二参数中，查找row下拉产生的序列，然后返回对应的第三参数的内容，公式：

=LOOKUP(ROW()-2,SUBTOTAL(9,OFFSET($D$1,,,{1,2,3,4,5,6})),B$2:B$7)&""

      公式右拉时，B2:B7 也就变为C2:C7，然后再下拉，完成！至于最后的 &"" 就是为了避免当下拉超过结果内容时，lookup找到的第7行的空值显示为0。小技巧提一下就行了，天天见。

      问题解决了，好像说完了。可是偏偏又还没完，论坛原帖里数据是可能增加的。还得改造下自动扩展的功能。我们就三言两语道一遍：

      如下图，数据又多了，人多力量大嘛，虽然小罗老师最近比较忙，但是这一来帮忙，我就可以少值一天班了。无比开心啊：

      这个时候，内容增加了，我公式得改呀：

=LOOKUP(ROW()-2,SUBTOTAL(9,OFFSET($D$1,,,{1,2,3,4,5,6,7})),B$2:B$8)&""

      Offset变化的范围也多了，又多了一组，第三参数返回的内容也多了到B8单元格了。那后边万一 jivy1212 老师呀，陈老师呀，灰灰老师呀也来帮忙值班了，我也不能每次改呢。所以offset中间那个序列1,2,3,4……不就可以用row来生成了么，可是这个要一次性就生成，我可不知道最后会有多少人。那就只能是动态取一个了。计数嘛，学透视表老师也教过了，动态识别数据源行列，不就用counta嘛。（当然方法肯定也不只一个）

      所以长这个样子？：

      Row(1:counta(b:b))

      道理是这样，但是没见过这样写的啊，反正我没见过。这时候又得下套了——Indirect 函数

内容没有完，接2楼继续

魚。。

接1楼内容：

      既然遇到了这个函数，也为了初入江湖的小伙伴着想，所以还是稍微讲讲这个函数基本用法：

INDIRECT(ref_text, [a1])

      两个参数，第一个参数对单元格引用的文本地址，第二个参数样式：参数为真或省略为A1样式，假或简写是R1C1样式。别理解的文字还是看图吧：

      我们重点看一下示例10，因为今天要用嘛，indirect可以把row里边的内容摘出来，然后改造。因为indirect第一参数是字符串，我们就可以通过字符串的改造连接，配合counta获取最大上限，来动态生成序列：

前边设想的这个Row(1:counta(b:b)) ，就可以开始改造了：

=Row(Indirect("1:" & Counta(b:b)))

      利用字符串连接的方法，用 & 把固定字符串和函数生成的上限值连起来组成需要的字符串。然后提供给offset，生成指定上限维度的多维数组。再用强大的subtotal来识别，最终提供给lookup查找匹配。

      这里还有个小问题，lookup第二参数动态了，那么第三参数自然也是动态的。但第三参数我们就不用再indirect一遍了。因为上期分享的lookup第三参数的帖子，就是专门解决这个问题的。为了让第三参数别去省略成一维数组的扩充，至少写2个单元格。综合上述内容，最终得到F2公式：

=LOOKUP(ROW()-2,SUBTOTAL(9,OFFSET($D$1,,,ROW(INDIRECT("1:"&COUNTA($B:$B))))),B$2:B$3)&""

      其实Counta这部分，也可以直接写一个足够大的数，反正多余的也不影响，就可以不用套indirect了。

另外引用部分如果需要保留数值格式，就不要用最后的 &"" 了，至于空行会引用到的0，可以套if来判断解决。

      又是一个普通的应用，被我啰嗦了那么多的内容。到此，魚同学该去休息了，不完善的内容就留个可爱的小伙伴去补充、钻研了~

最后附个offset的番外篇：

又送了你一朵小花花，记得用心查收哟。

jians0529

感谢分享。。。向老师学习 ！yydys!!

梅州

鱼老师又出新文章了，lookup+subtotal的用法非常常见也实用。文章通俗易懂，说的很实在，实在是不可多得的好文章呀。

消灭零公里

感谢分享，但是看不懂啊。
如果碰到类似的问题，我会考虑VBA或者PQ，数组公式不是我的菜啊

梅州

鱼老师的作品通俗易懂，耐人寻味。这个lookup+subtotal还有一些深层次的用法，鱼老师也可以讲解讲解。
就是生成法法1，法法2，鱼1，鱼2，鱼3这种更进一步的数据~~再次期待大作。

shaowu459

2楼没有了，已经被占了。你可以在下面继续发，应该可以执行提升楼层的操作。

Excel表哥在此

谢谢分享，学习中，先点赞支持！

梅州

鱼老师可以继续发布内容，然后把自己的帖子置顶。这样就连续了

魚。。

shaowu459 发表于 2021-5-18 21:56
2楼没有了，已经被占了。你可以在下面继续发，应该可以执行提升楼层的操作。

嘻嘻，感谢版主的建议。2楼还在小房子里