INDIRECT函数——汇总多个工作表同一单元格值成一列
发布时间:2023-02-19 10:42:27
Excel数据汇总中有这样一个问题:在很多个工作表中,同一项数据都位于同一个单元格,比如:每个月份的销量都位于每个月份工作表的B1单元格,而我们需要把每个月的销量汇总到一个总表中,在该总表中,各个月的销量分布为同一列。
这种汇总情况如下动图:
以上示例中,每个分工作表的命名是有规律的:从1到12月。但这种有规律的情况,只是个例,而普遍存在的是:每个分工作表的名称是无规律可寻的。
比如,以下动图,各个分表名称是超市名,而超市名是没有1——12等数字规律的:
上述两种情况,需要不同的汇总方法。
一一述:
分表名称有规律公式实现
在B2单元格输入公式:
=IFERROR(INDIRECT(ROW(A1)&”月”&”!B1″),””)
公式向下填充,即得所有工作表B1单元格的数据。
公式解析
ROW(A1)&”月”:
公式在B2单元格时,ROW(A1)返回1,即得工作表名称1月,公式向下填充到B3单元格时,该部分变为ROW(A2),即得工作表名称2月,再向下填充,得到其它月份工作表名称。
ROW(A1)&”月”&”!B1″:
连接工作表名称与单元格,得到:1月B1,2月B1,……12月B1.
INDIRECT(ROW(A1)&”月”&”!B1″):
引用1月B1,2月B1,……12月B1的值。
IFERROR(INDIRECT(ROW(A1)&”月”&”!B1″),””):
如果引用有结果,返回正确值,否则返回空值。
分表名称无规律
1、鼠标放在第一个超市名称的单元格A2,【公式】——【定义名称】:输入名称BM(此名称可任意取),引用位置处输入公式:
=INDEX(GET.WORKBOOK(1),ROW(A2))
GET.WORKBOOK(1)是宏表函数,取所有工作表的名称。
2、在A2单元格输入公式:
=IFERROR(BM,””)
向下填充,得到所有超市名称:
IFERROR函数是容错处理,如果没有超市名称,返回空值。
3、在B2单元格输入公式:
=IFERROR(INDIRECT(A2&”!B1″),””)
公式向下填充,即得所有超市工作表B1单元格的数值:
4、如果不喜欢上图中带工作簿名称的超市名,可以把公式改为:
=IFERROR(MID(BM,13,9),””)
因为工作簿名称有12个字节,所有用公式MID(BM,13,9),从第13个字节开始提取超市名称。其中9是随意取的长度,根据超市名称字符数的多少,该数值可灵活改变。
※特别注意:
工作表名称无规律的情况,因为引用了宏表函数,所以文件保存时要保存成“启用宏的工作簿.xlsm”。
猜你喜欢
- 步骤:1、首先打开需要设置大纲的文档,选定需要设置的内容,如图“叙述者” 2、右键选定内容,单击“段落”,在如图所
- 许多用户在使用Word的时候都需要在某些空白处画上下划线,例如毕业论文的封面、填空题等等,但是不少用户并不是非常了解Word怎么输入下划线,
- 用户在用word编辑完文字后,想要知道整个文档中文字的字数,有没有什么比较简单快捷的方法?查看word档的左下角,整个文档的页数和字数都有显
- 无论任何时候,任何人在使用Word的第一步骤就是新建文档了,新建文档后我们才可以在文档上添加资料进行文字处理。Word2003入门动画教程第
- 如何在excel中快速为多个数据设置一样的格式?相信很多朋友都不是很清楚吧,现在大家就为大家介绍一下,不会的朋友一起来学习吧在excel处理
- 快乐码字是一款专门为网络写手开发的一款功能强大,那么在快乐码字里如何写大纲呢?下面就带来了在快乐码字里写大纲的教程,希望对你们有所帮助哦快乐
- 1. 基于Wingdings 2字体的下拉列表(R,S):解析:数据有效性里面设定下拉列表内容为{R,S},然后设定字体为WingDings
- Word2013中怎么给整编文档添加漂亮的边框?Word2013中的文章想要添加漂亮的边框,该怎么添加呢?下面我们就来看看Word2013添
- 当越来越多的小伙伴使用win10系统时,一些人发现win10更新后,性能没有提高反而有所下降,这种情况该怎么办?现在就让小编来告诉你吧!wi
- 本文介绍如何利用Excel函数实现分栏打印,这个技巧适合打印列数比较少的Excel表格,避免出现大量空白。平时偶尔需要打印一些列数特少的表格
- 小技巧:双击在offcie Excel中妙用. 1.巧分窗口 如果仔
- 近期本站网站对微软最新的Windows 10X系统保持密切关注, 今天,国外媒体已经公开了一项新功能,即从Microsoft St
- 我们在使用Win10系统时遇到搜索不到工作组其他计算机,很多人都不知道该怎么办,面对这个问题,小编给大家带来了Win10搜索不到工作组计算机
- 一个excel工作薄默认情况下有3个excel工作表,如果我们要对excel工作表进行复制、粘贴或者删除的话,都要进行选择工作表,那么你知道
- 下面是我推荐的方法(以输入“阝”为例):在文档中输入一个包含该偏旁部首的汉字(如“陈”),并选中这个字符,word2003版本依次执行菜单栏
- 有时在打开他人的excel文件时需要输入密码才能打开,想去掉这个打开密码怎么取消呢?一起来了解一下吧在日常工作中经常会使用excel,有时在
- Office确实在商用市场占据比较大的比例,所以职场上的“白骨精”都是使用Ofifice的好手。虽然某软推出了以云为主要卖点的Office3
- 前面我们讲过了在Word2007文档中SmartArt图形位置的设置,而SmartArt图形与我们平时插入的图形一样,也是可以对SmartA
- “键入时自动套用格式”功能可以帮助用户在Word2010文档中输入字符时自动替换成最合适的字符,或自动应用最合适的格式。用户可以通过设置“键
- Designer2007怎么定位到背景图片的像素点?网页中的背景图片想要定位到指定的像素点,该怎么定位呢?下面我们就来分享三种教程,需要的朋