編按：

今天分享的Excel居家愛心公式大家一定要珍藏！對(duì)于把Excel“用數(shù)據(jù)說話”的觀念帶回家的男人們、女人們來說，這個(gè)公式能讓家庭更加美滿，支出更平衡，因?yàn)樗且环N智慧算法、愛心算法。絕不把眼睛盯在金額最高的那次、那幾次消費(fèi)上——這是愛心！也絕不把眼睛盯在金額最低的消費(fèi)上——這是智慧！回到工作上，這個(gè)公式還能幫助你搞定招標(biāo)價(jià)格關(guān)鍵值計(jì)算，復(fù)雜評(píng)分平均值統(tǒng)計(jì)，抽檢樣本平均值統(tǒng)計(jì)等等。

最近收到某粉絲求助，問題是如何統(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)<4,AVERAGE(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)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),"不滿足")

公式解析：通過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)<9,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3),"不滿足")}（此函數(shù)為數(shù)組公式，輸入完成后需要按ctrl+shift+enter三鍵完成）

公式解析：

（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)<20,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2,3})))/(COUNT(C:C)-4),"不滿足")} （此函數(shù)為數(shù)組公式，輸入完成后需要按ctrl+shift+enter三鍵完成）

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)槲覀冃枰?span>LAGRE函數(shù)的第二個(gè)參數(shù)根據(jù)消費(fèi)的次數(shù)實(shí)時(shí)變化。

函數(shù)公式：{=((SUM(C:C)-SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))-SUM(SMALL(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*10%,0)))))))/(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))}（此公式為數(shù)組公式，輸入完成后按ctrl+shift+enter三鍵完成）

公式解析：

（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ù)來作為LARGE的第二個(gè)參數(shù)，這樣我們就能達(dá)到動(dòng)態(tài)求和的目的。其中N為第一步中計(jì)算的去掉最高消費(fèi)筆數(shù)，通過INDIRECT函數(shù)引用。最終通過SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))函數(shù)公式完成最高的N筆消費(fèi)金額匯總。

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

（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)<4,AVERAGE(C:C),IF(COUNT(C:C)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),IF(COUNT(C:C)<9,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3),IF(COUNT(C:C)<20,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2,3})))/(COUNT(C:C)-4),((SUM(C:C)-SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))-SUM(SMALL(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*10%,0)))))))/(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))))))

【總結(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í)難度增大……

為愛心公式點(diǎn)贊超過200個(gè)，筆者將在下篇為大家奉獻(xiàn)更帶勁的愛心公式。

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

《Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》

《Excel教程：sumifs函數(shù)常量數(shù)組簡(jiǎn)化公式》

《SUM函數(shù)特殊應(yīng)用：序號(hào)填充、提成比例計(jì)算、打印標(biāo)簽制作》