移动版 - 最新教程 - 热门教程 - 推荐教程 - 热门资源 分享高质量的原创Office教程、Word教程、Excel教程、PPT教程。

excel如何统计产品批号

发布时间:2020-05-22 来源:Office办公助手整理

大家在日常工作中经常会遇到需要编号的情况,这个时候掌握一个超实用的函数就至关重要啦,以往遇到这样的问题,可能都会选择countif函数,但是今天将教你一个全新的函数哦!

如图所示,C列的批号要求用公式生成,说是批号,其实就是一个编号的问题,为什么这么说,还得从这个批号的规律来解释。

大家仔细观察一下就不难发现,在这个六位的批号中,其实是由两部分组成的,左边四位是生产日期的年和月,右边两位就是该产品在同一个月内生产的次数,为了便于理解,我们对在同一个月中多次生产的商品用不同颜色标注出来,之后再看就清楚了。

以丹参为例,虽然一共出现了四次,但是在4月份只有三次,因此对应的批号分别为200401-200403,所以这个问题的本质还是编号。

搞清楚了这一点,我们再来分析问题该如何解决。

正如前面分析的,批号是由两部分组成的,第一部分很容易,可以直接用TEXT函数从生产日期中得到,公式为:TEXT(A2,"yymm")。

TEXT函数的教程之前分享过很多篇,不再细说了,公式中的"yymm"表示将日期按照两位年两位月的格式显示结果。

问题的难点在于第二部分,同一个月内出现的次数,如果有一个辅助列的话,COUNTIFS就可以轻松解决,公式为:=COUNTIFS($D$2:D2,D2,$B$2:B2,B2)

公式中有两个条件,日期(其实是年月)和品名,关于COUNTIFS的用法,可以参阅往期教程《同样是countifs函数,为什么同事却使得比你好?原因在这里!》,这里要重点说明的是条件区域的写法,$D$2:D2和$B$2:B2中,只对区域的起始单元格锁定,这样得到的就是累计多条件计数的结果。学习更多技巧,请收藏部落窝教育excel图文教程。

如果条件区域是整列的话,得到的次数就不是累计的,而是最终出现的总次数,通过上图中的结果很容易搞明白这一点。

现在的问题是,如果没有这个辅助列,还能用COUNTIFS吗?

答案是不行!

因为COUNTIFS的特点就是条件区域只能使用单元格区域,而不能使用其他公式。

如果要使用公式作为条件区域的话,会弹出一个提示框:

同样的情况在COUNTIF和SUMIF、SUMIFS中都是类似的,只能使用单元格区域,这一点很重要。

因此,如果要在不使用辅助列的情况下解决这个问题,就必须用到SUMPRODUCT函数。

公式看起来会稍微有点长,=SUMPRODUCT((TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm"))*($B$2:B2=B2))

这个公式就是在没有辅助列的情况下实现了多条件的累计计数,公式中的TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm")是对日期(年月)进行判断,$B$2:B2=B2是对品名进行判断,分别得到两组逻辑值,两组逻辑值相乘后由SUMPRODUCT函数相加就能得到计数结果。

这个函数的用法可以参考之前的教程《加了*的 SUMPRODUCT函数无所不能》。

至此,最终的公式也就呼之欲出了,第一部分由TEXT得到年月,第二部分如果直接使用SUMPRODUCT得到的计数结果还不行,因为计算结果必须是两位数,如果不足两位的要在前面补零,这就还得用到TEXT函数,这种用法的格式是TEXT(要处理的数字, "00"),有几个0就表示得到的结果是几位数,因此最终的公式就是:

=TEXT(A2,"yymm")&TEXT(SUMPRODUCT((TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm"))*($B$2:B2=B2)),"00")

结束语:今天讲解的对学习函数来说是非常有帮助的,公式中包含了很多重要的知识点,总结如下。

1.TEXT函数中日期代码y和m的用法;

2.TEXT函数中关于指定数字位数的用法;

3.SUMPRODUCT函数实现多条件累计计数的用法,重点是条件区域中$符号的用法;

4.COUNTIF(S)、SUMIF(S)等函数中对于区域的要求,这一点算是一个隐藏的知识点吧。
源自:阳阳

更多相关阅读

用最快的方法核对两个excel不同之处

使用VBA代码实现excel文档每天自动备份

使用Excel仿旋转按钮圆环图的方法

利用Excel的冻结首行功能实现标题栏始终可见的方法

快速删除数据表格中的空白列的方法

    相关教程
    专题教程推荐
    热门资源下载