偶尔有朋友咨询我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)
网友评论