编按：

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

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

考题位于汇总表中，包括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是为了将逻辑值转化为数字，方便统计。

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

对SUMPRODUCT函数一知半解的朋友可能会感觉比较蒙圈，建议点下方链接去补补课。

SUMPRODUCT函数才是Excel的求和函数之王

加了*的 SUMPRODUCT函数无所不能

第三题

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

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

第四题

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

公式为：

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

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

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

经过这次测试，你认为自己是否算熟练应用Excel呢？

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

COUNTIF和AVERAGEIF函数的6种使用场景，简单又实用！

IF函数经典案例：判断一个单元格内是否包含指定关键词

Excel教程：如何制作带有层次和透视感的图表？

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

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