最近收到某粉絲求助，問題是如何統(tǒng)計(jì)老婆每次平均花費(fèi)。

下圖是這位粉絲提供的每次平均消費(fèi)計(jì)算規(guī)則——態(tài)度很端正，愛心滿滿，充滿了智慧??！某粉絲家庭成員每次消費(fèi)金額計(jì)算規(guī)則：

我將這個(gè)文件中的內(nèi)容提煉出來做了一個(gè)例表，如下圖所示。

簡(jiǎn)單梳理一下：表中C列為當(dāng)月每筆實(shí)際消費(fèi)金額?，F(xiàn)在需要針對(duì)不同消費(fèi)筆數(shù)計(jì)算每次的平均消費(fèi)額。

主要難點(diǎn)在于如何去除指定個(gè)數(shù)的最高和最低消費(fèi)。這個(gè)問題解決后我們就可以通過IF函數(shù)進(jìn)行判斷返回關(guān)鍵數(shù)值X。

下面我們將拆分所有判斷條件，依次跟大家分享一下解決過程。

1.消費(fèi)次數(shù)小于4的情況

消費(fèi)筆數(shù)小于4的情況下則計(jì)算這幾次消費(fèi)額的平均金額，這個(gè)條件還是比較簡(jiǎn)單的。只需要通過COUNT、AVERAGE這兩個(gè)函數(shù)即可完成。

函數(shù)公式：=IF(COUNT(C:C)

如下圖所示：

公式解析：通過COUNT（C:C）函數(shù)統(tǒng)計(jì)消費(fèi)次數(shù)。然后使用IF函數(shù)判斷是否滿足小于4這個(gè)條件，如果滿足條件則計(jì)算這幾筆消費(fèi)的平均金額，如果不滿足條件則返回文字說明“不滿足”。

2.消費(fèi)次數(shù)小于6的情況

如果消費(fèi)次數(shù)小于6次去掉最高的一次消費(fèi)后求剩余的消費(fèi)金額平均值。

函數(shù)公式：=IF(COUNT(C:C)

公式解析：通過COUNT(C:C)函數(shù)統(tǒng)計(jì)消費(fèi)次數(shù)。如果消費(fèi)次數(shù)小于6次則返回消費(fèi)總額減去最高一次消費(fèi)后求平均金額，如果不滿足條件則返回文字說明“不滿足”。

3.消費(fèi)次數(shù)小于9的情況

如果消費(fèi)次數(shù)小于9就要去掉兩個(gè)最高消費(fèi)和一個(gè)最低的消費(fèi)后求平均消費(fèi)金額。

這個(gè)條件相比前面兩個(gè)條件難度增加了，我們需要通過LARGE函數(shù)求最高的2次消費(fèi)金額之和。

函數(shù)公式：{=IF(COUNT(C:C)

公式解析：

（1）通過COUNT(C:C)函數(shù)統(tǒng)計(jì)消費(fèi)次數(shù)，然后使用IF函數(shù)判斷消費(fèi)次數(shù)是否小于9次。如果小于9次則去掉兩個(gè)最高消費(fèi)和一個(gè)最低的消費(fèi)后求平均消費(fèi)金額

（2）SUM(LARGE(C:C,{1,2}))數(shù)組公式含義為通過LARGE函數(shù)返回第一個(gè)最大值和第二個(gè)最大值，然后通過SUM對(duì)這兩個(gè)數(shù)據(jù)求和。(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3)表示所有消費(fèi)金額匯總后減去2個(gè)最高消費(fèi)以及一個(gè)最低消費(fèi)后的平均金額。

4.消費(fèi)次數(shù)小于20的情況

其實(shí)這條和第3條基本一致，主要的區(qū)別在于第3條是去掉兩個(gè)最高消費(fèi)金額，而這里是去掉3個(gè)最高消費(fèi)金額。

所以數(shù)組公式SUM(LARGE(C:C,{1,2}))需要改成SUM(LARGE(C:C,{1,2,3}))即可。

函數(shù)公式：{=IF(COUNT(C:C)

5.消費(fèi)次數(shù)超過20的情況

如果4個(gè)條件都不滿足那么就作為其他。這里則需要做兩個(gè)修正：

（1）消費(fèi)金額降序后取出最高的15%消費(fèi)金額，舉例如果消費(fèi)筆數(shù)是100家那么就要降序去掉前面15家;

（2）對(duì)報(bào)價(jià)升序排列去掉10%最低的。

這個(gè)條件相比前面的難度又增加了，因?yàn)槲覀冃枰孡AGRE函數(shù)的第二個(gè)參數(shù)根據(jù)消費(fèi)的次數(shù)實(shí)時(shí)變化。

函數(shù)公式：{=((SUM(C:C)-SUM(LARGE(C:C,ROW(INDIRECT("1:"

公式解析：

（1）首先通過COUNT(C:C)*15%來計(jì)算需要去除的最高的N筆消費(fèi)，這里需要通過ROUND函數(shù)進(jìn)行取整。最終函數(shù)公式：ROUND(COUNT(C4:C100)*15%,0)。同理我們通過ROUND(COUNT(C4:C100)*10%,0)來計(jì)算去掉最低的N筆消費(fèi)。

（2）根據(jù)第一步中計(jì)算的最高消費(fèi)筆數(shù)構(gòu)建SUM（LARGE(C1:C100,ROW(1:N)）這樣的數(shù)組公式，我們通過ROW函數(shù)來作為L(zhǎng)ARGE的第二個(gè)參數(shù)，這樣我們就能達(dá)到動(dòng)態(tài)求和的目的。其中N為第一步中計(jì)算的去掉最高消費(fèi)筆數(shù)，通過INDIRECT函數(shù)引用。最終通過SUM(LARGE(C:C,ROW(INDIRECT("1:"

（3）同理通過SMALL函數(shù)完成最低的N筆消費(fèi)匯總。函數(shù)公式：SUM(SMALL(C:C,ROW(INDIRECT("1:"

（4）最后用消費(fèi)總額減去（2）和（3）的計(jì)算結(jié)果求平均消費(fèi)金額即可。注意：求平均時(shí)要通過COUNT(C:C)減去最高的N筆消費(fèi)和最低的N筆消費(fèi)，不能直接除以所有消費(fèi)筆數(shù)。即(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))這部分函數(shù)公式。

最后我們將這幾個(gè)條件的函數(shù)公式完成合并嵌套。函數(shù)公式：=IF(COUNT(C:C)

總結(jié)：看到最后的公式，估計(jì)很多人都要崩潰了：難道真的是愛心越大，公式越長(zhǎng)嗎？

有沒有簡(jiǎn)易的公式？有，老婆消費(fèi)次數(shù)越少，公式越簡(jiǎn)單（希望粉絲的老婆不要看到這里啊~~）……最初的固定個(gè)數(shù)求和相對(duì)簡(jiǎn)單，但是后面消費(fèi)次數(shù)超過20后，要求去掉最高消費(fèi)和最低消費(fèi)為動(dòng)態(tài)數(shù)值時(shí)難度增大……

＊＊＊＊部落窩教育－excel統(tǒng)計(jì)日?；ㄤN＊＊＊＊

原創(chuàng)：龔春光/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號(hào)：exceljiaocheng