Excel双重条件计算的一个方式

某些情况下,会出现满足不同条件下进行不同计算的情形,这种计算有时候比较简单有时候又挺复杂,主要还是要看不同条件下涉及的计算方式。例如这里有一个情形是:根据客流量的不同,满意度达标的标准也不同,超过了达标标准后才能加分。然后根据客流量,满意度来计算得分。 具体的要求描述如下:

  合格的标准同时满足:客流量<2000,满意度50%; 2000<=客流量<4000,满意度35%; 4000<=客流量<6000,满意度25%; 6000<客流量,满意度15%; 合格则得分1分,此外满意度按超标5%加1分线性加分的标准累加,得分封顶5。然后要求根据客流量、满意度的情况进行统计得分。​

图1.计算的方式

一、概括计算方式:

由给出的题意可以看到,首先是需要判断当前的客流量的满意度的标准,是否达标,达标给1分,不达标为0分;达标后是否超出了达标标准线,并线性计算加分项,但加分后依然不能超出最大值5分。这里面就涉及到了一个多重判断与计算的情况。判断的话大家都知道可以使用IF函数来进行判断,IF函数前期也多次有用到的。但这里情况稍微要区分一下4种情况,根据客流量的多少,阶梯判断。这时候可以使用前期介绍过的AND与OR函数。因为客流量是一个连续的阶梯,所以必然落到某个区间,从而在此区间进行判断是否符合满意度的标准,符合的再往下计算得分,不符合的直接给出0分。

其次,对符合满意度标准的,直接赋予1分,再计算超出标准后的加分。加分为线性加分,只要计算分差/5%*1即可。但同时也需要注意4种不同的情况下,满意度标准不一,所以需要分别对不同满意度的阶梯进行计算。

二、公式的使用

首先建立辅助项,在表格空白处,或另外新建子表,将关键数据予以输入。这里列入的关键数据,是方便后续对评判进行修改的时候,无需修改公式,而只要修改下相应的单元格即可。

图2.加入协助项辅助

这里加入的是阶梯的分段2000、4000、6000,以及加分的量化5%、1分。

其次多重嵌套一下判断是否合格的公式,具体如下:

OR(AND(F2<$C$2,G2>=$B$2),AND(F2>$C$4,G2>=$B$5),AND(AND(F2<$C$3,F2>=$C$2),G2>=$B$3),AND(AND(F2<$C$4,F2>=$C$3),G2>=$B$4))

简单解释为:使用and函数来判断当前的客流量、满意度是否同时满足相应的标准。如AND(AND(F2<$C$3,F2>=$C$2),G2>=$B$3) 就是判断客流量是否大于等于2000小于4000,且满意度大于等于35%,如同时满足则输出为真。在嵌套OR函数,判断是否存在4种为真的情况中的一种,只要一种情况为真,说明在相应的客流量阶梯中,满意度是达标,则说明可以进行第二步的计算得分。如果4种情况都为假,则OR函数的结果也是为假。

再次,计算得分的公式如下:

$C$6+IF(AND(F2<$C$2,G2>=$B$2),(G2-$B$2)/$C$5,0)+IF(AND(F2>$C$4,G2>=$B$5),(G2-$B$5)/$C$5,0)+IF(AND(F2<$C$3,F2>=$C$2),(G2-$B$3)/$C$5,0)+IF(AND(F2<$C$4,F2>=$C$3),(G2-$B$4)/$C$5,0)

简单解释为:使用IF函数再次判断是否合格,合格后,计算加分项为当前满意度-满意度标准,再除以加分阶梯5%得到加分。不合格的直接赋予0分。因为4种满意度阶梯只会存在满足其中一种的情况,所以可以直接将四种情况下的计算项进行累加,其中一种情况为真的前提下,另外3种情况必然为假得到结果是0。

然后嵌套IF函数,进行条件的判断,符合标准的再进行计算得分,不符合的,直接为0分。

最后加上封顶值5分的限制,这里可以使用MIN函数来完成。MIN(计算得分,5),这样就限制了即使计算得分超出5分的情况下,输出值依然是5分,而低于5分的情况,得分为计算得分。

相应的公式嵌套起来完整的为:

=MIN(IF(OR(AND(F2<$C$2,G2>=$B$2),AND(F2>$C$4,G2>=$B$5),AND(AND(F2<$C$3,F2>=$C$2),G2>=$B$3),AND(AND(F2<$C$4,F2>=$C$3),G2>=$B$4)),$C$6+IF(AND(F2<$C$2,G2>=$B$2),(G2-$B$2)/$C$5,0)+IF(AND(F2>$C$4,G2>=$B$5),(G2-$B$5)/$C$5,0)+IF(AND(F2<$C$3,F2>=$C$2),(G2-$B$3)/$C$5,0)+IF(AND(F2<$C$4,F2>=$C$3),(G2-$B$4)/$C$5,0),0),5)

计算的结果如下,可见与手工计算一致。

图3.计算的结果

而此时,则可以试下改变关键项后的得数是否随之变化了。如把条件给为超过标准后10%再加1分:

图4.不同条件下的计算方式