Excel使用函数在A列提取最后一个星号后的数字并在B列显示
1657
2022-08-03
Excel用函数和公式瞬间实现把表格全部合并到一个表中去(excel表格多个表格合并成一个公式)
将多张工作表汇总到一张工作表,这是什么意思呢?可以理解为把表格全部合并到一个表中去,之前使用VBA代码实现,本文将教会大家一种快速的方法,用函数和公式瞬间实现多表合并。
例如:有N多个以月份命名的excel工作表这里为了演示方便以6个为例,如图1所示,每张表字段名相同,如图2所示,现需要把表格全部合并到一个表中去。这个合并或者是汇总,每个人对它的理解不一样,这里就不多说了。
工作表名:
图 1
每张表字段名:
图 2
操作步骤
Step1:在汇总表输入字段名,A2单元格手工输入第一张工作表名称1月,点击单元格右下角+往下拖动到A7,如图3。这里用到了一个比较快捷的方法,点击单元格右下角往下拖。
图 3
Step2:在B2单元格输入公式=INDIRECT($A2&"!"&ADDRESS(INT(ROW(A1)1)/6+2,COLUMN(A1))),向右拖动公式,再向下拖动公式,得到图4。这个公式用到了几个公式,若大家对公式不够理解,可以看看下面的公式说明。
图 4
公式说明
/6中数字6为要合并的工作表实际个数。
$A2是工作表名称所在列(本例是A列),
INT((ROW(A1)-1)/6)+2:目的是生成2,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,4序列,
ADDRESS 动态生成引用的单元格地址。例如:
=address(1,2)返回第一行第二列位置,也就是$B$1
如果想加上sheet的话,后面还有一个参数:
=ADDRESS(行标,列标,引用方式,[R1C1]格式,工作表名)
引用方式为:绝对引用、混合引用和相对引用;
R1C1格式表示是否以R1C1格式代替我们常用的格式,例如B5在R1C1格式中显示为R5C2。
INDIRECT函数功能是返回并显示指定引用的内容,可引用其他工作簿的名称、工作表名称和单元格引用。
INDIRECT函数是Microsoft Excel 中的公式,此函数立即对引用进行计算,并显示其内容。
此函数立即对引用进行计算,并显示其内容。当需要更改公式中单元格的引用,而不更改公式本身,请使用此函数,INDIRECT为间接引用。
在之前的制作二级联动菜单也用到这个函数。
Excel中怎样实现二级联动下拉菜单?
Step3:复制A:D列区域(如果有100张表就选取A2:D101),然后选取下面的空行粘贴即可完成全部数据提取。
如果工作表名称没规律怎么办?可以用宏表函数GET.WORKBOOK提取工作表名称。使用宏表函数GET.WORKBOOK可提取工作表名称,这在使用公式查询或汇总多工作表数据时经常会用到。以上便是为大家介绍的有关把表格全部合并到一个表中去的方法,感兴趣的朋友可以看看哦,以备不时之需。
发表评论
暂时没有评论,来抢沙发吧~