|
编按：哈喽，大家好！最近小编收到一位群友的求助，他说自己被excel中的编号问题给难住了。这是这么回事呢？编号不就是1、2、3、4、5吗，直接下拉单元格就能搞定，这有何难？一起来看看下面这篇文章中excel数据处理技巧。 近日看到一个群友的求助，觉得比较有意思，想和大家分享一下。学习更多技巧，请收藏部落窝教育excel图文教程。 这是一个看似普通的编号问题，可竟然动用了TEXT和SUMPRODUCT两个重量级的函数共同出手才得以解决。 以往遇到的编号问题，大多数都是COUNTIF的拿手好戏，但是今天这个问题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图文教程。 ＊＊＊＊部落窝教育-excel 组合函数应用＊＊＊＊ 原创：老菜鸟/部落窝教育（未经同意，请勿转载） 更多教程：部落窝教育 |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
