一个日常 Excel 公式引发的思考

偶尔有朋友咨询我EXCEL公式问题，其实平日用得不多，就熟悉几个简单的函数。只是多数问题通过分解，是可以通过简单的函数实现的。

实际问题

以有效投标人平均价为基准价，每超出基准价1%扣0.5分，低于基准价10%以后，每1%扣0.5分。总分25分，扣至零分为止，不计负分。超过最高投标限价的为无效投标。

举例来说就是比如基准价100万，然后我报了110万就每1%扣0.5 就是扣5分；如果报了80万，就是超过10%的每1%扣0.5分，也是扣5分。

方法论

形成一个最终的公式可以分为四个步骤：

（1）问题转换

问题转换，就是重新描述一下，以便问题的计算过程更具体化。不同的转换描述，会形成不同的公式表达。

（2）分解

将问题分解为一个个的中间项，就像计算一道数学题的中间步骤一样。复杂的公式不便记忆又难于理解，有效的分解，是把任务交给简单函数的前提。

（3）表达映射

分解的项，使用函数来进行表达。我把这称为表达映射，往往一些问题就可以用某类特定函数来表达，只是往往我们的表述会影响函数的选择。如后边后用到的MIN函数，它本义是取集合中的最小值，但计算值不大于某个特定值时就可以用它来表达。

（4）整合

把分解项形成的小公式，整合到一起，形成一个显得高大上的公式，就是需要的终极公式。

开始写公式

（1）问题转换

问题可理解为：根据投标价与基准价的百分比，100%以上的部分和 90%以下的部分每1个百分点扣0.5分，扣完（最多扣25分）为止。

（2）分解

• a. 取百分比：投标价与基准价的百分比点数

• b. 百分比高于100多少点

• c. 百分比低于 90 多少点

• d. 高的低的都要扣分（虽然不会同时发生，但数据的计算自然会体现这一点）

• e. 扣分不超过 25 分

如下图，我们通过分解的方式把每个值分开计算：

（3）表达映射

逐项选择合适的函数形成每一项的小公式

a.取百分比：投标价/基准价*100，对于小数部分经确认不到1% 的不算，那就要采用小学数学中的去尾法，只保留整数，使用INT取整函数。

此时公式为：

= INT( B2 / A2 * 100 )

b.高于100多少，用刚才计算的结果减掉100即可，但结果不能小于0。可做以下函数映射：

不小于某数 => 即跟某数比取其大(总是不会取到比某数小的) => MAX(计算值, 某数)

则 b 的公式为：

= MAX( C2 - 100, 0 )

c.低于90多少，与b项同理，用90来减去计算的百分比即可，同样结果不能小于0

则c的公式为：

=MAX( 90 - C2, 0 )

d.开始扣分，高的低的该扣分的百分比都计算出来了，0.5分1个百分点

则d的公式为：

= D2 * 0.5 + E2 * 0.5

e.最终扣分，不大于25分

不大于某数 => 即跟某数比取其小(总是不会取到比某数大的) => MIN(计算值, 某数)

则e的公式为：

= MIN( F2, 25 )

（4）整合

根据上述分解，我们得到以下分解公式

C2 = INT( B2 / A2 * 100 )
D2 = MAX( C2 - 100, 0 )
E2 = MAX( 90 - C2, 0 )
F2 = D2 * 0.5 + E2 * 0.5
G2 = MIN( F2, 25 )

使用代入法整合为一个公式：

C2 = INT( B2 / A2 * 100 )
D2 = MAX( INT( B2 / A2 * 100 ) - 100, 0 )
E2 = MAX ( 90 - INT( B2 / A2 * 100 ), 0 )
F2 = MAX( INT( B2 / A2 * 100 ) - 100, 0 ) * 0.5 + MAX( 90 - INT( B2 / A2 * 100 ), 0 ) * 0.5
G2 = MIN( MAX( INT( B2 / A2 * 100 ) - 100, 0 ) * 0.5 + MAX( 90 - INT( B2 / A2 * 100), 0 ) * 0.5, 25)

最终公式即为：
= MIN( MAX( INT( B2 / A2 * 100 ) - 100, 0 ) * 0.5 + MAX( 90 - INT( B2 / A2 * 100 ), 0 ) * 0.5, 25 )

殊途同归

显然不同的问题转换与分解，不同的表达映射会有不同的结果。

描述分解附一

1.a 取得百分比

公式为：

=INT( B2 / A2 * 100 )

1.b取得扣分百分比点数

如果百分比大于100，则减100，

否则，如果百分比小于90，则用90减百分比，其余则为0

函数映射 => IF(大于100吗？, 是的:百分比-100, 不是的:属于100以内的情况)

100以内的情况公式=> IF(小于90吗？, 是的:90-百分比, 不是的:0)

得：=IF(INT(B2/A2100)<90, 90-INT(B2/A2100), 0)

公式为：

=IF(INT(B2/A2*100)>100, INT(B2/A2*100)-100, IF(INT(B2/A2*100)<90, 90-INT(B2/A2*100), 0))

1.c 再乘0.5

公式为：

=IF(INT(B2/A2*100)>100, INT(B2/A2*100)-100, IF(INT(B2/A2*100)<90, 90-INT(B2/A2*100), 0)) * 0.5

1.d扣分不超过 25 分

最终公式为：

=MIN(25, IF(INT(B2/A2*100)>100, INT(B2/A2*100)-100, IF(INT(B2/A2*100)<90, 90-INT(B2/A2*100), 0)) * 0.5)

描述分解附二

2.a 统一扣分半径

扣分与不扣分的中间段为 90 至 100，取其中点 95，这样在中点两边，不扣分都占5个百分点，取百分比到这个中间点的距离，即（百分比– 95）的绝对值

公式为：

=ABS(INT(B2/A2*100) - 95)

2.b 得到要扣分的点数

用该绝对值再减去不扣分的5，即得到要扣分的点数。为负数则在不扣分的范围内，由下一步去处理。

公式为：

=ABS(INT(B2/A2*100) - 95) – 5

2.c 用2.b的计算值 * 0.5，但不小于0

先使用2.b的计算值 * 0.5，然后有如前面介绍的函数映射，使用MAX函数表达不小于0

=MAX((ABS(INT(B2/A2*100) - 95)-5) * 0.5, 0)

2.d 扣分不超过25分

有如前面介绍的函数映射，使用MIN函数表达不大于25

最终公式为：

=MIN(MAX((ABS(INT(B2/A2*100) - 95)-5) * 0.5, 0),25)