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