- 背景:统计过去1年内小明的出差费用。
- 问题1:一个个算太麻烦,有没有公式可以快速完成?
-
出差统计表
A B C D E F G 1 Site 人员 出差时间 出差天数 差旅费用 工时费用 考察/调研? 2 武汉 小明 2023.1.9-2023.1.18 10 6000 16720 考察 3 深圳 小紫 2023.6.26~2023.6.30 5 3000 14000 调研 4 武汉 小明 2023.7.13~2023.7.22 10 6000 16720 考察 5 武汉 小紫 2023.8.16-2023.8.23 8 4800 13376 调研 6 武汉 小明 2023.8.16-2023.9.5 21 12600 35112 考察 7 武汉 小绿 2023.8.23-2023.9.2 10 5870.4 16720 调研 8 武汉 小绿 2023.9.5-2023.9.27 23 11392.4 38456 考察 9 深圳 小紫 2023.10.17~2023.10.28 12 7200 33600 考察 10 武汉 小明 2023.10.20-2023.11.01 13 7800 21736 考察 11 深圳 小紫 2023.10.30~2023.11.11 13 7800 36400 调研 12 武汉 小绿 2023.11.13-2023.11.27 15 8263.2 25080 调研 13 深圳 小紫 2023.11.21-2023.11.27 7 2722.53 19600 调研 14 武汉 小红 2023.12.16-2023.12.30 15 9000 25080 调研 - 解决方案1:SUMIF(range, criteria, [sum_range]),range:选择你要统计的人所在的范围:小明所在的列,范围:column:B列,范围:B2:B14,criteria:选择你要统计的人,中文用英文双引号括起来"小明",[sum_range]选择你要统计的才旅费所在的范围:E2:E14,所以最后的公式是:=SUMIF(B2:B14,"小明",E2:E14)。
- 问题2:加大难度,再加一个条件,如果要统计小明过去一年因为考察的差旅费汇总,如何统计?
- 解决方案2:=SUMIFS(sum_range,range1,criteria1,range2,criteria2),sum-range表示你要统计的所在范围,对应统计表中的E2:E14,range1对应你要统计的人(小明)所在列,criteria1对应“小明”,range2对应你要统计的条件2(考察)所在列,criteria2对应“考察”,所以最后的公式是:=SUMIFS(E2:E14,B2:B14,"小明",G2:G14,"考察")
- 问题3:难度再次提升,同样要统计小明过去一年因为考察的差旅费汇总,不使用SUMIFS如何统计?
B C D 16 人员 考察/调研? 差旅费用 17 小明 考察 ? - 解决方案3:使用SUMPRODUCT函数,=SUMPRODUCT((G2:G14=C17)*(B2:B14=B17)*E2:E14),G2:G14=C17的含义是在G列统计=C17单元格中规定等于"考察"的这些行,B2:B14=B17的含义是在B列统计=B17单元格中规定等于"小明"的这些行,这两个条件同时满足的情况下,再来对G2:G14的"出差费用"进行汇总,汇总后的结果就放在D17的单元格。