奔牛网>office>Excel应用>>判断货品尺码断码齐码情况的公式(OR+COUNTIF+OFFSET)
2017年06月16日

判断货品尺码断码齐码情况的公式(OR+COUNTIF+OFFSET)

我们在用EXCEL处理货品销售库存数据的时候,经常会有需要判断货品断码情况的时候,这个时候就需要用到以下的公式:
IF(OR(COUNTIF(OFFSET(A2,,{0,1,2,3,4},,3),">0")=3),"无","断码")
这里判断连续3个尺码不断才算这个货品无断码,否则判断为断码,也就是必须存在3个连续的尺码有货才会被判断为未断码。
详细参数的作用看下图:


现在我们讲解下这个公式的计算过程,将公式拆解

1、IF函数=if(条件,“正常","断码")
这个应该都知道是什么意思吧?
接下来解读IF中的第一参数“条件”

2、OR(公式)
这个OF函数的意思就是其里面的公式只要有一个满足条件,就返回为TRUE,也就是条件存在~

OR(公式) 这个OR函数的意思就是其里面的公式只要有一个满足条件,就返回为TRUE,也就是条件存在~

那么再来解读这个OR里面的公式,是什么意思呢?

我们的要求即为:连续三个码数不为0的话才为真,

那么,也就是从一组码数中,要一个一个去判断再来统计个数,我用一组码数解释给大家看就很容易理解了。如下图,


 
如下面这个组码数


这样的一组码数,得判断所有符合组别的情况,

就要分为多组来判断了,

是不是得一个一个组别找出来,且应该为连续的三个码数来判断

所以这组码数,划为3个连续的,且都大于0的组别共有5组。

基中满足的条件只要有1组就不为断码。

那么上图标红色的,就表示有1组了,返回结果就不为断码了。

接下来返回OR中公式再来理解一下。

 OR(COUNTIF(OFFSET(A2,,{0,1,2,3,4},,3),">0")=3)


上图从S码开始,3个为一组,一个码接着一个码下去。

再来理解一下,如果以4个码为一组的。

以4个码的,到最后一位4XL码,只要分解4组即可了,但这个之中的4组没有一个符合条件都大于0的值,所以这组如果以4个连续的码且不为0的话来说明,其为断码。
好,继续回过头来说那3组的,OR中的公式
 OR(COUNTIF(OFFSET(A2,,{0,1,2,3,4},,3),">0")=3)

3、COUNTIF公式
这个公式起什么作用呢?这里把公式继续详解,


COUNTIF(数值1,">0”)这个公式看得懂什么意思吗?
 
这个公式的意思是说,计算符合条件值大于0的个数
 
那么再回过头来,刚才我们说过。如下图:

这连续三个码组成的是不是5个小组呢?
所以COUNTIF(数值1,">0”)中的数值1就是这5组。
而5组只要有一组符合条件就会大于0


再结合OR就显示条件为真。


,,{0,1,2,3,4},,为什么是0,1,2,3,4啊,两个逗号怎么用


那么再套一个IF,结果就返回不是断码。


这个不急,接下来就讲这个数值1的运用


OR(COUNTIF(OFFSET(A2,,{0,1,2,3,4},,3),">0")=3)

上面的OR(COUNTIF())这个套用应该懂了吧。
 
接下来就COUNTIF(数值1,">0”)中的数值1怎么引用的。


我们的条件就是要连接三个,且就得用函数来表示,如下图,



 
也就是说,用一个什么样的函数把一组码数隐藏中划分为5个小组,且5个小组又是连接3个码一组的数值。所以就想到了OFFSET函数。这也就是我们经常所说的数组,

数组是用来构建的,眼睛看不到的,上面图解只是方便大家理解~

4、OFFSET(A2,,{0,1,2,3,4},,3)

这个函数理解很容易。OFFSET共有5个参数。

第一个参数为偏移的起始位置

如函数OFFSET(A2,,{0,1,2,3,4},,3)

表示从A2开始偏移。

偏移怎么移才可以移出如下效果图?
 
 
 也就是移出一个逐步递增的3个连续区域。
OFFSET(A2,,{0,1,2,3,4},,3)

这个OFFSET函数中的第二参数没写,表示向下移的单元格。
顶(0)
踩(0)
最新评论