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

Excel疑难解答

每个单元格有多个已销商品代码,如何统计代码数量计算销售金额?

 

作者:老菜鸟来源:部落窝教育发布时间:2020-08-19 11:59:11点击:3744

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

编按:

部落窝推出Excel疑难解答专栏,负责解答各学员实际工作中遇到的各类问题。今天是第一个。在一份销售记录表中,各单元格记录了每次销售的产品代码。因为一次销售可能是多个产品,所以同一单元格就记录了多个代码。如何直接根据代码计算出每个销售员当日的销售金额呢?教程提供两个方法给你参考。

今天要和大家分享学员小王求助的真实案例。

小王是一家快餐店的财务人员。受疫情影响公司开展了店外销售业务,所有销售采取手工记账的方式。为了简化销售人员的工作量,为每种商品指定了一个字母代码,营业员只需要记录每一单销售的商品代码即可。下面是根据手工记账登记的销售记录表:

右侧为实际销售记录,左侧是商品代码及价格对照表(实际上有很多品种)。

 

业务开展得很顺利,但是小王的麻烦事来了。以前都是直接在电脑上下账,通过现成的收银报表即可快速算出每个人每天的应收合计,现在根据上面的销售记录表进行计算就很让人头疼,麻烦不说,还容易出错。

为此小王向老菜鸟求助,想看看是否有什么公式可实现自动求和。

 

这个问题乍一看真的有点无从下手,但是经过一番摸索,还真的找到了求和公式。下面就和大家一起来梳理一下解决问题的过程。

问题的难点在于每笔销售中包含的商品代码不是唯一的。如果能统计出每种代码的出现次数,然后再来核算金额就比较容易了。

例如先统计焦玉芳一共销售了多少个A,我们可以使用COUNTIF函数来实现,J2单元格公式为:=COUNTIF(B2:H2,"*A*")

这是COUNTIF最基本的用法,"*A*"中的星号*是通配符,两边都加了星号就表示统计含有A的单元格个数。

注:如果有顾客买了两个相同的商品,记录成AA,则此法无法正确统计,因为AA也只能算是一个包含了A的记录。

统计出A的数量后,只需要乘以单价就得到焦玉芳销售的A商品的金额,公式为=COUNTIF(B2:H2,"*A*")*M2

按照这种思路,只需要分别统计出ABCD的金额再相加,就能得到每个人当日的销售金额合计。

但是这样做的话,如果商品品种很多,公式就会很长或者需要多个辅助列,很麻烦。我们可以借助数组的应用,实现起来就比较方便。

首先将COUNTIF中的"*A*"做一下变化,改成这样的=COUNTIF(B2:H2,"*"&K2:K5&"*")

注意这里的条件已经不是一个值了,而是一组单元格。直接回车得到的将仍然是第一个产品A的数量。不用疑惑,继续往下看。

该公式可以对单元格K2:K5内的数据分别进行统计,得到的结果就是每个代码的计数。选中公式按F9键可以看到四个数字:

这四个数字就表示ABCD的个数。

COUNTIF的后面乘以单价区域,就可以得到每个商品的售价,公式为:

=COUNTIF(B2:H2,"*"&K2:K5&"*")*M2:M5

最后,只要再对这四个数字求和就能完成某个人的销售金额计算。

如果使用SUM函数求和需要三键,也就是Ctrlshift和回车键;如果使用SUMPRODUCT函数求和则无需三键,可以直接得到结果。在I2单元格输入公式:

=SUMPRODUCT(COUNTIF(B2:H2,"*"&K$2:K$5&"*"),M$2:M$5)

公式中,考虑到下拉填充,商品代码和单价这两个区域使用绝对引用符号$

公式中的三个区域,B2:H2是销售记录区域,K$2:K$5是代码区域,M$2:M$5是单价区域,可以根据实际数据进行调整。

强调:

需要注意的是COUNTIF的第二参数如果使用通配符,而条件值又是单元格的话,要给星号加引号,同时需要用连接符&进行连接。这个原则同样适用于SUMIF函数。

更多解决办法:

对于这类问题,还有另一个常用的套路,就是ISNUMBERFIND组合,公式分享给大家,有兴趣的朋友可以自己试试分析这个思路:

=SUMPRODUCT(ISNUMBER(FIND($K$2:$K$5,B2:H2))*$M$2:$M$5)

思考题:

如何解决文章中提到的如果同一单元格中出现两个代码A或者B的计算?(答案请加群264539405下载)

 

如果你在实际工作中也遇到了一些麻烦而又不好百度的问题,不妨留言告诉我们。在我们帮你解决问题的同时,大家也能从中得到一些收获。

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

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

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

IMG_256

相关推荐:

同样是countifs函数,为什么同事却使得比你好?原因在这里!

countif函数的使用方法以及countif函数查重复等5个案例分享

加了*的 SUMPRODUCT函数无所不能

TEXT和SUMPRODUCT强强联合,只为解决一个“微不足道”的编号问题?