ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 7218|回复: 40

[分享] 浅谈 lookup+subtotal+offset 按指定次数重复内容

[复制链接]
回帖奖励 55 财富 回复本帖可获得 5 财富奖励! 每人限 1 次

TA的精华主题

TA的得分主题

发表于 2021-5-18 19:48 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:SUBTOTAL
本帖最后由 魚。。 于 2021-5-19 17:48 编辑

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

image.png

      如上图需求,就是要按照给定数字次数,重复前边的名字。方法其实挺多的,比如辅助列啥的。(不是今天内容,我不知道)直接来说今天的事:
      首先,要把结果放在多行里边,列么还是那两列。行是要根据次数来定的,牵扯到序列的问题了,row又要登场了。然后利用lookup查找row生成的序列,返回对应行的内容。先看下思路吧:

image.jpg

      Lookup 第二参数每两个数之间的差值就是D列每次递增增加的次数,为什么要从0开始生成序列呢,主要是为了末尾问题,重复完最后一次之后,不能一直让lookup找最后那个人,所以多引用一个空白单元格,这样就可以让超过的部分匹配最后一个人下一行的空白内容。实现一个错行引用,具体可以结合分析内容理解一下。
      思路有了,现在关键的问题是,怎么才能构建出那6个sum呢?也就是sum的依次递增的区域。我能想到的就offset了,先简单说下


offset函数:
OFFSET(reference, rows, cols, [height], [width])
Offset(单元格区域,偏移行,偏移列,[扩展行],[扩展列])
5个参数,后两个可省略。不过今天可不能省,我要用的就是扩展行呢。简单看下offset:

image.png

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

image.png

      思路有了,可是我们现在脑海里想下这个画面: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第一参数的表格,有兴趣的小伙伴可以自己研究。

image.png

      回到我们今天要做的活,既然高手已到,连工作表自动筛选,还有隐藏行内容的问题都能识别,这点多维小意思啦。我们用subtotal去把offset出来的那个结果给揽下来:
=SUBTOTAL(9,OFFSET($D$1,,,{1,2,3,4,5,6}))
      这样我们就得到了我们需要用来作为lookup第二参数的数组:

image.png

那么,既然第二参数确定了,第三参数返回的值,我们可以直接用单元格引用就好了。
Lookup通过在第二参数中,查找row下拉产生的序列,然后返回对应的第三参数的内容,公式:
=LOOKUP(ROW()-2,SUBTOTAL(9,OFFSET($D$1,,,{1,2,3,4,5,6})),B$2:B$7)&""

image.png

      公式右拉时,B2:B7 也就变为C2:C7,然后再下拉,完成!至于最后的 &"" 就是为了避免当下拉超过结果内容时,lookup找到的第7行的空值显示为0。小技巧提一下就行了,天天见。
      问题解决了,好像说完了。可是偏偏又还没完,论坛原帖里数据是可能增加的。还得改造下自动扩展的功能。我们就三言两语道一遍:
      如下图,数据又多了,人多力量大嘛,虽然小罗老师最近比较忙,但是这一来帮忙,我就可以少值一天班了。无比开心啊:

image.png

      这个时候,内容增加了,我公式得改呀:
=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楼继续

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

24.71 KB, 下载次数: 233

评分

13

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-18 19:53 | 显示全部楼层
本帖最后由 魚。。 于 2021-5-18 21:32 编辑

接1楼内容:
      既然遇到了这个函数,也为了初入江湖的小伙伴着想,所以还是稍微讲讲这个函数基本用法:
INDIRECT(ref_text, [a1])
      两个参数,第一个参数对单元格引用的文本地址,第二个参数样式:参数为真或省略为A1样式,假或简写是R1C1样式。别理解的文字还是看图吧:

image.jpg

      我们重点看一下示例10,因为今天要用嘛,indirect可以把row里边的内容摘出来,然后改造。因为indirect第一参数是字符串,我们就可以通过字符串的改造连接,配合counta获取最大上限,来动态生成序列:
前边设想的这个Row(1:counta(b:b)) ,就可以开始改造了:
=Row(Indirect("1:" & Counta(b:b)))

      利用字符串连接的方法,用 & 把固定字符串和函数生成的上限值连起来组成需要的字符串。然后提供给offset,生成指定上限维度的多维数组。再用强大的subtotal来识别,最终提供给lookup查找匹配。
      这里还有个小问题,lookup第二参数动态了,那么第三参数自然也是动态的。但第三参数我们就不用再indirect一遍了。因为上期分享的lookup第三参数的帖子,就是专门解决这个问题的。为了让第三参数别去省略成一维数组的扩充,至少写2个单元格。综合上述内容,最终得到F2公式:

image.png

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

      其实Counta这部分,也可以直接写一个足够大的数,反正多余的也不影响,就可以不用套indirect了。
另外引用部分如果需要保留数值格式,就不要用最后的 &"" 了,至于空行会引用到的0,可以套if来判断解决。
      又是一个普通的应用,被我啰嗦了那么多的内容。到此,魚同学该去休息了,不完善的内容就留个可爱的小伙伴去补充、钻研了~
最后附个offset的番外篇:

image.png

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


评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2021-5-18 21:25 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2021-5-18 21:42 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
鱼老师又出新文章了,lookup+subtotal的用法非常常见也实用。文章通俗易懂,说的很实在,实在是不可多得的好文章呀。

TA的精华主题

TA的得分主题

发表于 2021-5-18 21:43 | 显示全部楼层

回帖奖励 +5 财富

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

TA的精华主题

TA的得分主题

发表于 2021-5-18 21:47 | 显示全部楼层

回帖奖励 +5 财富

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

TA的精华主题

TA的得分主题

发表于 2021-5-18 21:56 | 显示全部楼层

回帖奖励 +5 财富

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

TA的精华主题

TA的得分主题

发表于 2021-5-18 22:01 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2021-5-18 22:02 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
鱼老师可以继续发布内容,然后把自己的帖子置顶。这样就连续了

评分

1

查看全部评分

TA的精华主题

TA的得分主题

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

嘻嘻,感谢版主的建议。2楼还在小房子里
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 04:44 , Processed in 0.056239 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表