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”。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
隐藏Excel表格行和列单元格方法
![](https://img.aspxhome.com/file/2023/2/37242_0s.jpg)
升级win10后word打不开怎么办
word文档怎么打钩和叉
![](https://img.aspxhome.com/file/2023/8/34388_0s.gif)
解决Excel表格输入数字变成日期
![](https://img.aspxhome.com/file/2023/6/37256_0s.jpg)
Word2013快速知道表格有多少行和列的两种方法
![](https://img.aspxhome.com/file/2023/6/24516_0s.jpg)
在word文档中打"√"的几种方法介绍
![](https://img.aspxhome.com/file/2023/1/19411_0s.jpg)
打开Word文档弹出此文包含的链接该怎么办?
![](https://img.aspxhome.com/file/2023/8/28848_0s.jpg)
用Word模板功能建立自己的模板
Word2010中怎么快速选取部分文字
![](https://img.aspxhome.com/file/2023/6/19696_0s.jpg)
宏碁笔记本win10改装win7插上耳机没声音怎么解决?
![](https://img.aspxhome.com/file/2023/9/51559_0s.png)
WPS字体如何添加纹理?
![](https://img.aspxhome.com/file/2023/1/a163551_0s.gif)
iOS 14 中的耳机调节功能有什么用?如何使用耳机调节功能?
![](https://img.aspxhome.com/file/2023/4/a194614_0s.png)
excel2019表格中输入时间和日期的技巧
![](https://img.aspxhome.com/file/2023/9/a140269_0s.jpg)
打开excel提示"找不到必要的安装文件sku001.CAB"怎么办
2013word页眉线设置方法
Excel上线多人实时编辑功能:支持多人对一个表格文档同时编辑
![](https://img.aspxhome.com/file/2023/6/40656_0s.jpg)
word打开文件出错怎么解决
![](https://img.aspxhome.com/file/2023/4/17744_0s.png)
学会这些 Excel技巧,快速录入数据,提升80%的效率
![](https://img.aspxhome.com/file/2023/4/39254_0s.gif)
Win10打开显示设置屏幕自动变亮如何解决?
![](https://img.aspxhome.com/file/2023/5/51825_0s.jpg)