Excel数据求和相信许多同学都有操作过,但是有一类数据求和操作,相信对于许多人来说那就是跨工作表数据求和。尤其是涉及相同条件多表求和与跨工作表条件求和,这样的操作都是非常实用的操作。 在跨工作表Sheet查询引用时,如果每次都重新去选取新的工作表Sheet话,操作起来就会很繁琐,步骤也会比较多。 案例: 下图为1~3月水果重量销售记录,每个月有一个单独的工作表,希望做一张汇总表,自动计算水果重量的合计 Excel 图1 那么,有没有什么办法,使用一个公式就从多个工作表Sheet中查询出我们想要的结果呢?下面用两种情况为大家解剖方法。 Excel 图2 这种求和方法很简单,只要在汇总表的C3单元格输入公式: =SUM('*'!B2) ,回车以后,公式自动变成: =SUM(汇总:Sheet3!B2,'1月销售数量:3月销售数量'!B2)然后下拉填充即可完成。 还有一种方式不需要使用函数,只需使用合并计算的功能。首选选中C3:C8 , 选择【数据】选项卡【合并计算】 Excel 图3 弹出【合并计算】窗体,选择求和,选择引用位置 Excel 图4 选择1月那个表,再选择B2:B7这个区域 Excel 图5 点击添加后,继续点击选择 Excel 图6 最后点击确定,完成 Excel 图7 以上方式针对顺序完全一致的详细方法,下面讲解顺序不一样,数量也不一致,但都在同样的列的情况 这种情况下求和稍有麻烦,我们一步步分析: 首先:我们需要借助INDIRECT函数生成对多个表区域的引用,即是:=INDIRECT(ROW($1:$3)&'月销售数量!A:A')以及 =INDIRECT(ROW($1:$3)&'月销售数量!B:B'),以上两组都使用了ROW($1:$3)产生月份序号的数组,合起来是生成1~3月销售数量工作表的A区域和B区域。然后,我们需要sumif函数进行条件求和sumif第一参数和第三参数都是区域,前面我们已经列举好了A区域和B区域的多表数组,下面我们直接套进来=SUMIF(INDIRECT(ROW($1:$3)&'月销售数量!A:A'),A3,INDIRECT(ROW($1:$3)&'月销售数量!B:B')) Excel 图8 但是结果为什么是0呢?其实这套函数返回的结果只是一个数组,也就是将三个表条件求和的结果生成了一个数组,我们按F9试试 Excel 图9 这样我们就需要用SUM进行求和了,我们需要在外面嵌套一层sum,输入完成后,需要按CTRL+SHIFT+回车(数组公式需要三键回车/也叫三键合并)最后下拉填充,完成。 Excel 图10 函数解析: 1、在每张工作表数据都不一样的情况下,进行跨工作表数据求和我们需要利用sumif条件求和函数和indirect工作表引用进行嵌套的运用; 2、INDIRECT函数在这里主要为引用1-3月3个工作表的A列和B列,然后结合sumif条件求和; 3、sumif函数在这种情况下求和出来的结果是每一个工作表水果对应的数据,结果会以数组的方式显示,所以最后还是需要用sum函数再进行一次求和。 注:这种情况下的跨表求和如果数据量较大的话,表格会变得卡顿。 现在你学会如何在不同情况下进行跨工作表数据求和了吗? |
|