如下图,用函数公式把图1里的金额按月份总和然后填到图2里的相应月份里,不能用透视表,试了很多次,真解决不了了。。脑壳都快炸了。。找了很多方法,不是错误,就是各种数据问题。。请教大神。。
在WPS表格中,可以使用SUMIF、SUMIFS和 INDIRECT函数、TEXT函数和 COUNTIF 函数 来跨表查询并根据月份求和。
下面假设数据源分别为表格1、表格2,其中表格1中“日期”列为A列,“金额”列为B列;表格2中“日期”列为A列,“金额”列为B列。
1.用 SUMIF 和 INDIRECT 函数求和:
在第三个工作表中,首先要通过 INDIRECT 函数创建一个范围,以确定所需表格的名称, SUMIF 函数用于累加所有值,可以使用 TEXT 函数从日期列中提取月份,在一个相应的列中,然后使用 COUNTIF 函数枚举多少个月份与该月份相应的匹配,并通过 INDIRECT 函数来计算相应的表格名称,最后用 SUMIF 函数的结果就是月份对应的金额总和。公式如下,其中的A2是要查找的月份:
=SUMIF(INDIRECT("'"&TEXT($A$2,"mmm")&"'!A1:A100"),"<"&DATE(YEAR($A$2),MONTH($A$2)+1,1), INDIRECT("'"&TEXT($A$2,"mmm")&"'!B1:B100"))2.用 SUMIFS 函数的方法求和:
SUMIFS 函数基于给定条件返回一个或多个单元格的总和。在此,我们将使用 SUMIFS 函数来求和预先提取的月份数据,使用这些方法的优点是不需要创建验证单元格或使用 INDIRECT 函数,公式如下,其中的A2是要查找的月份:
=SUMIFS(Table1[$金额],Table1[$日期],"<"&DATE(YEAR($A$2),MONTH($A$2)+1,1),Table1[$日期],">="&DATE(YEAR($A$2),MONTH($A$2),1))+SUMIFS(Table2[$金额],Table2[$日期],"<"&DATE(YEAR($A$2),MONTH($A$2)+1,1),Table2[$日期],">="&DATE(YEAR($A$2),MONTH($A$2),1))
注意:在使用上述公式之前,需要将第一个表格命名为“Table1”,第二个表格命名为“Table2”,如果命名不一致,需要相应修改公式中的表格名称。
在第一个表格中,选择需要求和的月份所在的列,点击“插入”选项卡中的“函数”按钮,选择“SUMIF”函数。
在函数参数中,第一个参数为需要求和的范围,选择第二个表格中的月份列;第二个参数为需要匹配的条件,选择第一个表格中的月份列;第三个参数为需要求和的范围,选择第二个表格中的金额列。
点击“确定”按钮,就可以得到跨表求和的结果。
如果需要在第一个表格中显示求和结果,可以在需要显示的单元格中输入函数“=SUMIF(第二个表格中的月份列,需要匹配的条件,第二个表格中的金额列)”。