偶爾有朋友咨詢我EXCEL公式問題，其實平日用得不多，就熟悉幾個簡單的函數(shù)。只是多數(shù)問題通過分解，是可以通過簡單的函數(shù)實現(xiàn)的。
實際問題
以有效投標(biāo)人平均價為基準(zhǔn)價，每超出基準(zhǔn)價1%扣0.5分，低于基準(zhǔn)價10%以后，每1%扣0.5分?？偡?5分，扣至零分為止，不計負分。超過最高投標(biāo)限價的為無效投標(biāo)。
舉例來說就是比如基準(zhǔn)價100萬，然后我報了110萬就每1%扣0.5 就是扣5分；如果報了80萬，就是超過10%的每1%扣0.5分，也是扣5分。
方法論
形成一個最終的公式可以分為四個步驟：
（1）問題轉(zhuǎn)換
問題轉(zhuǎn)換，就是重新描述一下，以便問題的計算過程更具體化。不同的轉(zhuǎn)換描述，會形成不同的公式表達。
（2）分解
將問題分解為一個個的中間項，就像計算一道數(shù)學(xué)題的中間步驟一樣。復(fù)雜的公式不便記憶又難于理解，有效的分解，是把任務(wù)交給簡單函數(shù)的前提。
（3）表達映射
分解的項，使用函數(shù)來進行表達。我把這稱為表達映射，往往一些問題就可以用某類特定函數(shù)來表達，只是往往我們的表述會影響函數(shù)的選擇。如后邊后用到的MIN函數(shù)，它本義是取集合中的最小值，但計算值不大于某個特定值時就可以用它來表達。
（4）整合
把分解項形成的小公式，整合到一起，形成一個顯得高大上的公式，就是需要的終極公式。
開始寫公式
（1）問題轉(zhuǎn)換
問題可理解為：根據(jù)投標(biāo)價與基準(zhǔn)價的百分比，100%以上的部分和 90%以下的部分每1個百分點扣0.5分，扣完（最多扣25分）為止。
（2）分解
a. 取百分比：投標(biāo)價與基準(zhǔn)價的百分比點數(shù)
b. 百分比高于100多少點
c. 百分比低于 90 多少點
d. 高的低的都要扣分（雖然不會同時發(fā)生，但數(shù)據(jù)的計算自然會體現(xiàn)這一點）
e. 扣分不超過 25 分
如下圖，我們通過分解的方式把每個值分開計算：
（3）表達映射
逐項選擇合適的函數(shù)形成每一項的小公式
a.取百分比：投標(biāo)價/基準(zhǔn)價*100，對于小數(shù)部分經(jīng)確認不到1% 的不算，那就要采用小學(xué)數(shù)學(xué)中的去尾法，只保留整數(shù)，使用INT取整函數(shù)。
此時公式為：
= INT( B2 / A2 * 100 )
b.高于100多少，用剛才計算的結(jié)果減掉100即可，但結(jié)果不能小于0?？勺鲆韵潞瘮?shù)映射：
不小于某數(shù) => 即跟某數(shù)比取其大(總是不會取到比某數(shù)小的) => MAX(計算值, 某數(shù))
則 b 的公式為：
= MAX( C2 - 100, 0 )
c.低于90多少，與b項同理，用90來減去計算的百分比即可，同樣結(jié)果不能小于0
則c的公式為：
=MAX( 90 - C2, 0 )
d.開始扣分，高的低的該扣分的百分比都計算出來了，0.5分1個百分點
則d的公式為：
= D2 * 0.5 + E2 * 0.5
e.最終扣分，不大于25分
不大于某數(shù) => 即跟某數(shù)比取其小(總是不會取到比某數(shù)大的) => MIN(計算值, 某數(shù))
則e的公式為：
= MIN( F2, 25 )
（4）整合
根據(jù)上述分解，我們得到以下分解公式
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 )
殊途同歸
顯然不同的問題轉(zhuǎn)換與分解，不同的表達映射會有不同的結(jié)果。
描述分解附一
1.a 取得百分比
公式為：
=INT( B2 / A2 * 100 )
1.b取得扣分百分比點數(shù)
如果百分比大于100，則減100，
否則，如果百分比小于90，則用90減百分比，其余則為0
函數(shù)映射 => IF(大于100嗎？, 是的:百分比-100, 不是的:屬于100以內(nèi)的情況)
100以內(nèi)的情況公式=> 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 統(tǒng)一扣分半徑
扣分與不扣分的中間段為 90 至 100，取其中點 95，這樣在中點兩邊，不扣分都占5個百分點，取百分比到這個中間點的距離，即（百分比– 95）的絕對值
公式為：
=ABS(INT(B2/A2*100) - 95)
2.b 得到要扣分的點數(shù)
用該絕對值再減去不扣分的5，即得到要扣分的點數(shù)。為負數(shù)則在不扣分的范圍內(nèi)，由下一步去處理。
公式為：
=ABS(INT(B2/A2*100) - 95) – 5
2.c 用2.b的計算值 * 0.5，但不小于0
先使用2.b的計算值 * 0.5，然后有如前面介紹的函數(shù)映射，使用MAX函數(shù)表達不小于0
=MAX((ABS(INT(B2/A2*100) - 95)-5) * 0.5, 0)
2.d 扣分不超過25分
有如前面介紹的函數(shù)映射，使用MIN函數(shù)表達不大于25
最終公式為：
=MIN(MAX((ABS(INT(B2/A2*100) - 95)-5) * 0.5, 0),25)
