Excel如何跨工作表跨列进行动态的多条件求和

Excel如何跨工作表跨列进行多条件求和？

在日常工作中，其实仅仅跨列求和，就有点超出一般的excel应用水平，因为大家更习惯于进行单列数据的求和。

而跨工作表，就更添加计算的难度了。

下面，作者就通过某外贸公司各品类产品的销售情况实例，用详细的操作步骤和计算方法，来解答这个问题。

如下图所示，指定了产品大类，再指定该大类下的产品子类，然后计算指定的时间段，产品销量的总和。

这里产品大类的内容是对应各工作表的名称，当我们设置了动态列表框之后，便可以直接点击选择要查询的工作表。

然后根据产品大类，再设置一个二级下拉列表，这样就能动态地选择需要查询的产品大类下各产品子类的销售数据。

我们先来看看各工作表的数据内容：

每个工作表的格式基本一致，列标题是月份，行标题是产品子类名称。

回到汇总表，如下图所示，我们的目的是可以即时查询到，任意产品大类下任意子类在任意时间段的销量总和。

那首先就来设置产品大类的动态列表框。但要设置动态列表，需要创建一个辅助列，来引用所有的工作表名称。

如何批量提取工作表名称？有一个函数可以做到。

点击公式工具栏下方的”定义名称“，在弹出的编辑窗口中，输入一个名称，并在引用位置输入一个宏表函数：=get.workbook(1)

workbook 表示的是工作簿，get.workbook(1)，是一个固定的宏表函数表达式，它的作用就是提取工作表名称，结果则可以是单个值，也可以是多个值，这取决于工作表的数量。

我们可以记住这个固定表达式，但需要注意两点，一是宏表函数不能直接在单元格使用，需要通过定义名称的方式来间接应用；二是宏表函数要在excel中正常运行，需要将文件保存为“.xlsm”后缀格式的excel文件，即启用宏的工作簿。

随后我们在单元格内输入一个引用公式：INDEX(工作表名称,ROW(A1))

但它的结果是包含了工作簿名称的完整名称，如图所示：

我们知道之前用宏表函数提取的工作表名称，其结果是一个数组值，这里嵌套了ROW函数，则在向下填充公式时，可以依次提取各工作表的名称。

之后我们再套用REPLACE函数，来提取需要显示的字符串，即不带工作簿名称的工作表名。

REPLACE函数实际是一个查找替换函数，它的作用是将文本中的指定字符串替换为另一字符串。

作者通过将不需要的字符串替换为空值，而得到需要的名称。

它的表达式为：=REPLACE（文本，需替换的字符串开始位置，替换的字符个数，要替换的字符串）

我们看下上图中输入的完整公式：=REPLACE(INDEX(工作表名称,ROW(A1)),1,FIND("]",INDEX(工作表名称,ROW(A1)),1),"")

四个参数的含义如下：

第一个参数文本是index函数引用的工作表名称；

第二个参数是1，即表示从第1个字符开始；

第三个参数是FIND("]",INDEX(工作表名称,ROW(A1)),1)，也是一个函数公式，表示查找“]”符号在文本中的位置，它的结果以数值表示，这里计算结果为13，也就是说从第1个字符开始，选择13个字符，而“]”符号之后，就是我们需要的工作表名；

第四个参数是""，表示的是空值，综合讲，便是将文本1-13个字符，替换成空值，剩下的字符串就是我们需要的工作表名。

得到了简洁准确的工作表名，即产品大类名称，我们便可以设置一个动态列表框。

如下图所示，在数据工具栏下点击”数据验证“，在“允许”框选择“序列”，在“来源”输入单元格引用区域。

便得到之前动图中的下拉列表显示效果。

接下来我们继续设置二级下拉列表，同样的操作，进入数据验证设置界面。

但在“来源”框中输入的不再是固定的单元格区域，而是一个通过offset函数引用的动态区域。

这个公式为：=OFFSET(INDIRECT(A2&"!a:a"),1,,COUNTA(INDIRECT(A2&"!a:a"))-1,)

offset是查找引用函数，indirect函数也是引用函数，但区别在于offset函数是给定参照值并设定偏移量来进行引用，结果可以是单个值也可以是一个数组；indirect函数是返回文本字符串所指向的单元格或单元格区域引用。

这里我们着重讲讲indirect函数的应用。

indirect函数的表达式为：=indirect（文本，引用样式）

两个参数，第一个参数是文本字符串，通常是单元格地址，如A1,"A1"等，这里不带引号的A1表示引用A1单元格内容所指向的数据，带双引号的"A1"则表示引用A1单元格的数据；

第二个参数引用样式，就是公式的两种引用样式，也可以说是单元格地址的显示样式，这个参数可以省略，默认为A1样式。

我们回到上面的公式中，INDIRECT(A2&"!a:a")的含义是什么？

我们又要来弄懂A2&"!a:a"所表达的含义……

A2单元格的内容是”装饰纪念品“，而装饰纪念品又是一个工作表的名称，那么它的完整结果就是“装饰纪念品！a:a”，是不是似曾相似呢！

它其实就是跨表引用的单元格区域显示形式，表示装饰纪念品工作表中的A列数据区域。

我们套上indirect函数，即表示引用装饰纪念品工作表中的A列数据，它的值是一个数组，包含A列所有的数据。

那么COUNTA(INDIRECT(A2&"!a:a"))-1，则是计算引用区域中非空单元格的个数，它的作用是为了引用该工作表中所有的产品子类名称。

通过offset函数的引用，能计算得出指定各工作表下的所有产品子类名称。这样便得到了一个可任意点击选择的二级下拉列表。

之后在一次设置开始时间和结束时间的动态下拉列表，方法步骤与上同。

最后，我们进入汇总统计的主题，仍然是通过一个组合嵌套的函数，来进行求和计算。

从上图编辑栏中的公式可以看出，它带有大括号，属于一个数组公式，嵌套了sum函数、offset函数、match函数、indirect函数，各函数各有其用。

indirect函数来引用产品大类的跨表单元格区域，match函数用来返回产品子类在跨表单元格区域中的位置，offset函数则可以引用设定偏移位置的数值计算区域，最后sum函数来求和计算区域的数值。

四者一组合，便得到了跨表的多条件求和结果。

我们来看看这个公式的各参数结果，如下图所示：

在编辑较长的公式时，要擅长使用F9快捷键逐个解析各部分嵌套函数公式的结果，这样有助于对整个公式的理解。

根据上面公式解析的结果来看看在对应工作表中，它的计算区域和计算结果，如下图所示：

offset函数以A1单元格”category“为参照，向下方偏移5行，向右偏移1列，即B6单元格，然后要引用的行数为1，列数为9，也就是B6:J6单元格区域。

这正是我们要求和计算的单元格区域！

通过下拉列表来设置多个动态条件，使用数据验证来制作下拉列表，在数据验证中编辑公式来设计二级下拉列表，通过定义名称和宏表函数来提取工作表名称，使用引用函数来跨表引用，通过多个函数嵌套的数组公式来汇总求和，等等，这篇文章涉及的内容还是比较繁杂的。

     以上就是excel自学教程为您提供Excel如何跨工作表跨列进行动态的多条件求和的解读，本文章链接： http://www.5enet.cn/Excel_gongzuobiao/72447.html 欢迎分享转载，更多相关资讯请前往工作表