二维码 购物车
部落窝在线教育欢迎您!

Excel中如何按支付类型汇总交易金额和交易笔数?

 

作者:老菜鸟来源:部落窝教育发布时间:2023-04-26 17:23:33点击:1291

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

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)可得到101112这样的小时数序列。也可以用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)

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

交易笔数的公式:

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

 


 

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

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

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

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

加了*的 SUMPRODUCT函数无所不能

 

第三题

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

 

 

有兴趣的朋友还是可以用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

IMG_256

相关推荐:

COUNTIFAVERAGEIF函数的6种使用场景,简单又实用!

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

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

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

版权申明:

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