当前位置: > 教程 > 办公软件 >

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列
                        栏目分类:办公软件   发布日期:2020-03-24   浏览次数:

                        问题来源 Excel数据汇总中有这样一个问题:在很多个工作表中,同一项数据都位于同一个单元格,比如:每个月份的销量都位于每个月份工作表的B1单元格,而我们需要把每个月的销量汇总到一个总表中,在该总表中,各个月的销量分布为同一列。 这种汇总情况如下动

                        问题来源

                        Excel数据汇总中有这样一个问题:在很多个工作表中,同一项数据都位于同一个单元格,比如:每个月份的销量都位于每个月份工作表的B1单元格,而我们需要把每个月的销量汇总到一个总表中,在该总表中,各个月的销量分布为同一列。

                        这种汇总情况如下动图:

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        以上示例中,每个分工作表的命名是有规律的:从1到12月。但这种有规律的情况,只是个例,而普遍存在的是:每个分工作表的名称是无规律可寻的。

                        比如,以下动图,各个分表名称是超市名,而超市名是没有1——12等数字规律的:

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        上述两种情况,需要不同的汇总方法。

                        韩老师一一讲述:

                        分表名称有规律

                        公式实现

                        在B2单元格输入公式:

                        =IFERROR(INDIRECT(ROW(A1)&"月"&"!B1"),"")

                        公式向下填充,即得所有工作表B1单元格的数据。

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        公式解析

                        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))

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        GET.WORKBOOK(1)是宏表函数,取所有工作表的名称。

                        2、在A2单元格输入公式:

                        =IFERROR(BM,"")

                        向下填充,得到所有超市名称:

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        IFERROR函数是容错处理,如果没有超市名称,返回空值。

                        3、在B2单元格输入公式:

                        =IFERROR(INDIRECT(A2&"!B1"),"")

                        公式向下填充,即得所有超市工作表B1单元格的数值:

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        4、如果不喜欢上图中带工作簿名称的超市名,可以把公式改为:

                        =IFERROR(MID(BM,13,9),"")

                        Excel282 | INDIRECT函数——汇总多个工作表同一单元格值成一列

                        因为工作簿名称有12个字节,所有用公式MID(BM,13,9),从第13个字节开始提取超市名称。其中9是随意取的长度,根据超市名称字符数的多少,该数值可灵活改变。

                        ※特别注意:

                        工作表名称无规律的情况,因为引用了宏表函数,所以文件保存时要保存成“启用宏的工作簿.xlsm”。

                        Excel图表·公式·函数·数据分析从新手到高手(超值全彩版 附光盘) ¥40.1 购买

                        相关热词:

                        js特效 教程 资源 资讯
                        网络彩票