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

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

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

簡單梳理一下：表中C列為當月每筆實際消費金額?，F在需要針對不同消費筆數計算每次的平均消費額。

主要難點在于如何去除指定個數的最高和最低消費。這個問題解決后我們就可以通過IF函數進行判斷返回關鍵數值X。

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

1.消費次數小于4的情況

消費筆數小于4的情況下則計算這幾次消費額的平均金額，這個條件還是比較簡單的。只需要通過COUNT、AVERAGE這兩個函數即可完成。

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

如下圖所示：

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

2.消費次數小于6的情況

如果消費次數小于6次去掉最高的一次消費后求剩余的消費金額平均值。

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

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

3.消費次數小于9的情況

如果消費次數小于9就要去掉兩個最高消費和一個最低的消費后求平均消費金額。

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

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

公式解析：

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

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

4.消費次數小于20的情況

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

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

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

5.消費次數超過20的情況

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

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

（2）對報價升序排列去掉10%最低的。

這個條件相比前面的難度又增加了，因為我們需要讓LAGRE函數的第二個參數根據消費的次數實時變化。

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

公式解析：

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

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

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

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

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

總結：看到最后的公式，估計很多人都要崩潰了：難道真的是愛心越大，公式越長嗎？

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

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

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

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

微信公眾號：exceljiaocheng