216、Excel动态汇总多工作表中指定的数据
- 2026-08-25 18:30:06
216、Excel动态汇总多工作表中指定的数据 本节介绍如何动态汇总多工作表中指定的数据，当工作表增加或减少时，数据相应的自动做出变化。如下视频所示，需要将工作簿中除“汇总表”外所有工作表的B列数据汇总到一起，并提取唯一值。当工作表增加或减少以及工作表中内容变化时，汇总数据都会动态的进行相应的变化。 视频所示的动态包含以下含义：1、动态获取工作表名称；2、动态更新数据变化；3、动态去重。具体步骤如下 Step1、动态获取工作表名称：在前边的章节“166、Excel宏表函数四---GET.WORKBOOK函数”中介绍了返回工作簿相关信息的获取，当函数参数为“1”时会返以“水平数组方式”回当前工作簿所有工作表的名称（含工作簿名称)。为此，可以使用该函数动态获取工作表名称。首先创建一个名称，名为“工作表名”，引用位置输入公式“=MID(TOCOL(GET.WORKBOOK(1)&T(NOW())),FIND("]",TOCOL(GET.WORKBOOK(1)&T(NOW())))+1,15)”，函数中“GET.WORKBOOK(1)&T(NOW())”部分首先以水平数组形式获取含工作簿名的所有工作表名（T(NOW())部分使得函数可以自动更新，而不用手动刷新，实现自动更新目的），然后使用TOCOL将其转换为垂直数组，使用mid函数提取工作表名，提取位数可以填大一点，避免因提取不完整而出错。如下图所示： 
创建名称后，当在单元格中输入“=工作表名”时，便会返回当前工作簿中所有工作表的名称，注意生成的工作表数组顺序与工作表中的排布是一致的，如下图所示： 
Step2、显然“汇总表”是我们放置汇总数据的工作表，这个表中的内容是不需要进行汇总的，所以需要将其去掉（注意：将汇总表放在最前边），将G11单元格公式改为“=DROP(工作表名,1)”，使用DROP函数将第一行的数据删掉，也就是去掉“汇总表”，如下图所示： 
Step3、为工作表名添加各工作表想要合并的数据信息，这里是在B列，为了防止遗漏数据，可以将单元格地址信息放大一点，比如B2：B20（B1一般是标题，这里直接去掉B1），G11单元格公式改为“=DROP(工作表名,1)&"!B2:B20"”，&"!B2:B20"部分相当于公式中单元格引用时单元格区域部分，但这这里还只是文本，并不是真正的单元格区域引用，如下图所示： 
Step4、将上一步得到的文本转换为单元格引用。使用INDIRECT函数可以将单元格中的文本转换为单元格引用，如下图所示，在K13单元格输入“=INDIRECT(K11)”时，返回了N11单元格的数据23，函数将K11单元格中的N11文本转换成了单元格引用。 
Step5、借鉴上一步的结论，那直接将G11单元格的公式改为“=INDIRECT(DROP(工作表名,1)&"!B2:B20")”会得到什么结果呢？如下图所示： 
显然返回了错误值#Value！“值中的错误"，但当只对其中一个单元格进行转换时，可以返回正确结果，比如“=INDIRECT(G11)”，返回结果如下图所示：那么问题就在于参数只能逐个传递了。 
Step6、为了将G11：G16单元格的数组逐个转换成相应的单元格区域引用，可以使用迭代函数。比如将G11单元格公式改为“=REDUCE("名称",DROP(工作表名,1)&"!B2:B20",LAMBDA(x,y,VSTACK(x,INDIRECT(y))))”，公式通过VSTACK将每次获取的X和Y垂直连接，也就是连接成1列。如下图所示： 
Step7、通过上一步返回结果可以看出有很多值为0的行，这些行是对应工作表中的空白行，显然这也是我们不需要的，接下来将这些空白行去掉。TOCOL函数可以过滤错误值和空白值，如下所示： 
将G11单元格公式改为“=TOCOL(REDUCE("名称",DROP(工作表名,1)&"!B2:B20",LAMBDA(x,y,VSTACK(x,INDIRECT(y)))),3)”，注意参数3忽略空白和错误，这样便获取了各工作表B列中的数据了。结果如下图所示： 
Step8、去除重复值：在上一步结果的最外层嵌套一个UNIQUE函数即可返回唯值了，公式为“ 
PS、如果只是想获取每个工作表的不重复值，而不是所有工作表的不重复复制，则可以将UNIQUE的嵌套位置放在INDIRECT(y)的前边，公式改为“=TOCOL(REDUCE("名称",DROP(工作表名,1)&"!B2:B20",LAMBDA(x,y,VSTACK(x,UNIQUE(INDIRECT(y))))),3)”，此时结果如下图所示： 
通过本节的内容，“215、匹配数据时，数据源分布在多个工作表如何提高效率”中数据源的获取就可以采用其动态获取了，有兴趣的朋友可以试试，用其建立一个模板，这样后续就可以方便的使用了。
已关注
关注
重播 分享 赞
=UNIQUE(TOCOL(REDUCE("名称",DROP(工作表名,1)&"!B2:B20",LAMBDA(x,y,VSTACK(x,INDIRECT(y)))),3))
”。结果如下图所示：
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
