Excel数据汇总方法:SUMPRODUCT函数统计交易金额和笔数

Excel数据汇总方法:SUMPRODUCT函数统计交易金额和笔数
2024年02月03日 10:16 部落窝教育

编按:Excel中如何按支付类型汇总交易金额和交易笔数?这是一道经管人员的Excel面试题。从流水账中统计支付情况是经营管理人员,财务人员常要做的事。今天就来教大家用SUMPRODUCT函数来解决这些问题吧!

最近有幸见到这样一个招聘经营管理人员的Excel面试题,在此分享给各位读者朋友。

面试题提供了基础表,数据包含两部分:交易明细和支付类型对照表。

考题位于汇总表中,包括3个必答题和1个附加题。答题限制不能修改表格结构,不能用辅助列。

面试题的第一题要求汇总每小时的交易金额和金额占比。

第二题要求按支付类型汇总交易金额和交易笔数,以及它们的交易占比。

第三题按订单号统计每位收银员收银笔数及金额。

附加题要求统计每位收银员每小时内的收银笔数。

各位朋友可以先自测一下可以做到什么程度,然后继续看下方内容。

实际上只要精通SUMPRODUCT函数,再辅以一些基础函数,解决这套题还是绰绰有余的。

申明一下,每道题的解决方法都不唯一,以下给出的思路都是以SUMPRODUCT函数为主导的。

第一题

按小时统计,涉及到三组公式,交易金额、金额占比和合计。

A列提供的是一个时间区间而不是一个小时数,基础表中的时间也不是一个小时数。在不使用辅助列的情况下,用SUMPRODUCT函数非常合适。

公式为:=SUMPRODUCT((HOUR(基础表!$A$2:$A$406)=ROW(A10))*基础表!$E$2:$E$406)

公式中的HOUR(基础表!$A$2:$A$406)部分根据基础表的A列得到小时数,然后和ROW(A10)进行比较。下拉公式,通过ROW(A10)可得到10、11、12这样的小时数序列。也可以用LEFT(A7,2)*1,其中的差异大家可以自己体会一下。

金额占比使用公式=B7/$B$21下拉,合计使用公式=SUM(B7:B20)右拉,这两组公式非常基础,就不赘述了。

第二题

按支付类型统计交易金额和交易笔数。

在整套题中难度最大,涉及四组公式,交易金额、交易笔数、占比和合计。

汇总表里的支付类型是文字描述,而基础表中的支付类型是字母代码。必须通过基础表中的对照表做转换才能进行统计。

在不使用辅助列的情况下,可借助INDEX-MATCH组合帮助SUMPRODUCT完成统计。

交易金额的公式为:

=SUMPRODUCT((基础表!$D$2:$D$406=INDEX(基础表!$I$2:$I$7,MATCH(A25,基础表!$J$2:$J$7,)))*基础表!$E$2:$E$406)

交易笔数的公式:公式为:=SUMPRODUCT((基础表!$D$2:$D$406=INDEX(基础表!$I$2:$I$7,MATCH(A25,基础表!$J$2:$J$7,)))*1)

最后的*1是为了将逻辑值转化为数字,方便统计。

占比和合计没什么难度,不啰嗦了。

第三题

是整套题里最最容易的,一个单条件计数,一个单条件求和,使用COUNTIF和SUMIF就能搞定,留给大家自己练习吧。

有兴趣的朋友还是可以用SUMPRODUCT函数解决这一题的,欢迎留言分享你的公式。

第四题

前三题本质上都是单条件统计,只是条件的设置有不同的难度。附加题是唯一的一个多条件统计题,按照收银员代码和小时数两个条件统计收银笔数。

公式为:

=SUMPRODUCT((HOUR(基础表!$A$2:$A$406)=ROW(A10))*(基础表!$C$2:$C$406=B$47)*基础表!$E$2:$E$406)

如果对前两个题目彻底理解的话,这一题完全没难度。

这套Excel面试题,如果允许使用辅助列,或者可以随意修改表格结构的话,很多人都可以搞定。但是在不允许的情况下全部使用公式完成,还是很考验函数基本功的。

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。

财经自媒体联盟更多自媒体作者

新浪首页 语音播报 相关新闻 返回顶部