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

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

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

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

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

ok5office教程网

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

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

我们先来看看各工作表的数据内容：ok5office教程网

ok5office教程网

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

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

ok5office教程网

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

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

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

ok5office教程网

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

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

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

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

ok5office教程网

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

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

ok5office教程网

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

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

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

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

四个参数的含义如下：ok5office教程网

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

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

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

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

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

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

ok5office教程网

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

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

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

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

ok5office教程网

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

这里我们着重讲讲indirect函数的应用。ok5office教程网

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

ok5office教程网

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

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

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

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

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

ok5office教程网

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

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

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

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

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

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

ok5office教程网

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

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

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

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

ok5office教程网

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

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

ok5office教程网

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

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

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