|
多个工作表的数据合并成一张工作表，是日常工作中常见的表格问题。在之前，我们陆续给大家分享了Power Query、SQL、VBA和插件的解法套路，独独缺少了函数…… 这倒不是因为函数太老了，身体不行了，解决不了多表合并的问题。而是这家伙的解决方案，难度太高，效率却不高。虽说可以迎男而上，但非常容易进入男上加男、进退两男的尴尬局面。 我举个例子。
如上图所示，有3张工作表，名称分别为2018年/2019年/2020年，表的结构一致，现在需要将这3张工作表的数据合并成一张表。
如果使用函数执行多表合并，可以在汇总表的A1单元格输入以下数组公式，向下向右复制填充。
数组公式 ▼ =INDIRECT(LOOKUP(ROW(A1)-1,MMULT(N(ROW($1:$3)>COLUMN(A:C)),COUNTIF(INDIRECT({"2018年";"2019年";"2020年"}&"!a:a"),"<>")),{"2018年";"2019年";"2020年"})&"!R"&ROW(A1)-LOOKUP(ROW(A1)-1,MMULT(N(ROW($1:$3)>COLUMN(A:C)),COUNTIF(INDIRECT({"2018年";"2019年";"2020年"}&"!a:a"),"<>")))&"C",0)&""
泡一杯老坛酸菜方便面，你看这公式又长又复杂，武侠小说看流泪，从来不相信魔鬼，函数就是这么累……咳，摊手，解释一下这公式的意思。 公式的核心是： 核心部位 ▼ MMULT(N(ROW($1:$3)>COLUMN($A:$C)),COUNTIF(INDIRECT({"2018年";"2019年";"2020年"}&"!a:a"),"<>"))
COUNTIF部分统计每张工作表A列存在数据行的个数，然后使用MMULT函数进行累加，比如每张表存在数据的行数分别为7,6,7；则MMULT计算结果为{0,7,13}。
使用LOOKUP函数按区间计算行列数据，生成R1C1样式的单元格引用地址，最后再使用间接引用函数INDIRECT化地址为单元格引用，显示相关单元格的值。
就这么回事。 如果你所使用的Excel版本是365，可以使用Let函数，公式的层次看起来就清晰多了。 =LET(表名,{"2018年";"2019年";"2020年"},行数,MMULT(N(ROW($1:$3)>COLUMN($A:$C)),COUNTIF(INDIRECT(表名&"!a:a"),"<>")),INDIRECT(LOOKUP(ROW(A1)-1,行数,表名)&"!R"&ROW(A1)-LOOKUP(ROW(A1)-1,行数)&"C",0)&"")
这公式强大到没朋友，简直自绝于人类，所以——就很不适合用于工作，还是优先推荐大家使用Power Query。 ……
没了，今天和大家分享的内容就这些。有不明白的地方可以在会员群中提问交流，完全看不懂又十分感兴趣的话可以过一下以下两篇教程。
|
