利用Excel计算货品的齐码率

利用Excel计算货品的齐码率

使用场景：在快消行业的商品管理中，经常遇到这样的问题 即要针对一件货品的不同规格来判断是否齐码。
那么如何快速判断大量货品规格在各店库存中是否齐码 不仅便于更好了解门店货品的实际状况 更能检查商品管理人员的工作状况
同时 如果我们能对门店商品在不同生命周期中的合理齐码率有充足的估计 也能保证货品销售能更加合理的进行

接下来 以办公过程中常使用到的Excel来进行这样的工作 我将通过两种不同的方法来展示实现这一判断功能的思路。

例如：在Excel中货品的库存状态信息通常以以下方式呈现：
首先，我们确定齐码断码状态的具体描述，例如在这里，先简单的认为如果某货品最少4个规格连续存在库存，那么即认定该货品库存状态为齐码 如果货品连续存在库存的规格数低于4个，则认为货品为断码状态。但是这里要注意一点是 如果某一货品 在库存中所有的库存数据都为0，则是一种例外情况，应该在实际使用中进行避免。

同时，这里我们忽视了一种例外情况，即两头存在库存 但中间没有的情况 即以下这种：

这种情况是无法避免的 但是这种库存状态在实际情况中出现的较少，一般可以忽略不计。同时，如果出现可以使用IF方法在最外层加一层判断，单独对这种情况进行统计也是可行的。

那么现在开始说明第一种方法：该方法的核心在于使用countif函数判断所有连续存在库存的单元格

IF(COUNTIF(N2:Q2,">0")=4,“齐码”,其他) 这是我们按顺序来判断从规格1到规格4之间是否4个单元格都有大于0的数，如果这种情况存在，那么就已经满足了完成齐码的条件，如果不存在则转向执行“其他”

以上就是该思路的中心，实际上也很简单，就是利用if嵌套对货品齐码状态的每一种可能性都进行判断是否为真。那么接下来为了完善这个功能，只要我们在其他中写上判断从O2:R2的判断是否齐码 这样反复嵌套直到推到库存的最后一个规格为止。

另外一种方法，则是主要利用Excel中的FREQUENCY函数来对货品是否齐码进行判断，在这里我们可以意识到，判断货品是否齐码，即是判断是否有连续的单元格存在数，而我们要求的即是其中连续存在单元格的个数最多有多少个。简而言之，就是求最大连续数。

EXCEL中的FREQUENCY函数，主要功能即是按区间统计，简要功能如下，
例如：有以下数据以及我们想要划分的区间，分别为负无穷到50 50到60 60到70 70到正无穷

那么，依次统计的结果也就是如上图所示。
具体的用法，来看看FREQUENCY的官方使用帮助：
这里重点在于：以一列垂直数组返回，即意味着frequency返回的必定是一个垂直数组，数组是Excel中一种特殊的数据存放方式，数组会包含运算多个运算数据。如上图实例，如果我们有3个分段点，如50，60，70，那么就将数轴划分为了4个部分，也就是会返回四个数。
也就是说，返回的数据量将永远比分段点多一个。

同时帮助说明要使用数组返回，那么即要使用数组运算，需要在公式完成时，选中比区间多一个的单元格，同时按下Ctrl shift enter三键运算。这也是所有数组运算的特征。

FERQUENCY的基本用法就是以上，但是离解决我们的问题还需要一步，如果FREQUENCY的基础分段点是相同的会出现什么情况呢？
可以看到，新加了重复的分段点之后，重复的分段点之后会出现统计为0，这是为什么呢？原因在于，FREQUENCY的分段点区间的统计是大于前一个分段点的数，但小于或等于后一个分段点，在这里体现为大于50，但是又小于或等于50的数不存在 故中间统计结果为0，也必定为0。

那么接下来的事情，就是依次构造出需要的数列即可。如下图所示，


这里黄色列中的公式，就是使用if条件，当所在单元格中有大于0的数时，返回当前列的列号，那么其中绿色列的公式，就是当所在单元格中有小于0的数时，返回当前列的列号。

这里，还需要最后一步，就是带入FREQUENCY函数，我们以第一列为数据，第二列为分段点，按以下公式执行，
那么就返回的数是什么呢？

为什么这里都是0呢，这是因为FREQUENCY返回的必定是垂直数组，其中第一个分段点0到14之间为0，那么数组第一个数必定为0。如果想查看全部结果，我们可以使用TRANSPOSE函数将垂直数组转换为横向数组即可。这里由于我们只需要最大值，在外面再加上MAX函数，即得到我们所求的最大连续数。

到这里已经函数的基本逻辑已经完成了，接下只要完成外层的if 就是当最大连续数大于4时，返回齐码，当小于4时即为断码，但是这里有更好的解决办法，即使用TEXT函数，来进行判断，会让函数显得更加简洁，优雅。

这样我们构建的函数功能就已经完成了，其中最为关键的在于使用FREQUENCY对不同的数据进行划分。理解和构造公式的关键在于Excel对数组的运用。尤其是在使用FREQUENCY中这种仅支持数组运算的函数时，显得尤为重要。

谢谢。