今天要聊的問題很常見：三家門市各賣三種茶，單價不一樣，賣的杯數也不一樣，「平均單價」到底要怎麼算？直接 AVERAGE 單價欄，每一種茶不管賣了幾杯都只算一次，這個數字不太能拿去報告。這時候要的是加權平均，Google 試算表有現成的 AVERAGE.WEIGHTED。
另外，標題上的 AVERAGEIF、AVERAGEIFS、MAXIFS、MINIFS 在前面幾篇已經講過條件怎麼寫，這篇不重講，只整理「想知道的東西長怎樣，該挑哪一個」。
先看資料
九筆資料，放在 A1:D10，A 欄門市、B 欄品項、C 欄杯數、D 欄單價（元）：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 門市 | 品項 | 杯數 | 單價 |
| 2 | 信義 | 蘋果紅茶 | 34 | 45 |
| 3 | 信義 | 鮮奶茶 | 26 | 55 |
| 4 | 信義 | 青茶 | 9 | 40 |
| 5 | 板橋 | 蘋果紅茶 | 43 | 45 |
| 6 | 板橋 | 鮮奶茶 | 11 | 58 |
| 7 | 板橋 | 青茶 | 22 | 40 |
| 8 | 左營 | 蘋果紅茶 | 17 | 42 |
| 9 | 左營 | 鮮奶茶 | 47 | 52 |
| 10 | 左營 | 青茶 | 12 | 38 |
我想知道「信義這家店，客人平均一杯付多少錢」。先手算。單價直接平均的話，是 (45 + 55 + 40) ÷ 3 ≈ 46.67。但信義的青茶才賣 9 杯，蘋果紅茶賣了 34 杯，青茶憑什麼跟蘋果紅茶占一樣多？
依杯數加權才對得上實際收入：(34 × 45 + 26 × 55 + 9 × 40) ÷ (34 + 26 + 9) = 3320 ÷ 69 ≈ 48.12。這兩個數字差了 1 元多，三家店每一家都這樣算，排名甚至會變，後面會看到。
怎麼選
| 想知道 | 用這個 | 條件寫法在哪 |
|---|---|---|
| 符合一個條件的平均 | AVERAGEIF | SUMIF 與 COUNTIF 入門 |
| 符合多個條件的平均 | AVERAGEIFS | SUMIFS 進階陷阱 |
| 符合條件的最大、最小 | MAXIFS、MINIFS | SUMIFS 進階陷阱 |
| 依數量加權的平均 | AVERAGE.WEIGHTED | 下面 |
| 加權平均又有條件 | AVERAGE.WEIGHTED 搭配 FILTER | 下面 |
重點只有一個：AVERAGEIF 系列是「先篩再平均」，每一列的分量一樣；AVERAGE.WEIGHTED 是「每一列的分量不一樣」，兩個功能不能互相取代，要看你的題目有沒有「數量」這一欄。
條件平均與條件極值
先把這四個函式在這組資料上各跑一次，看清楚它們算的是「單價欄的簡單平均或極值」，不管杯數。
鮮奶茶三家店的平均單價，手算 (55 + 58 + 52) ÷ 3 = 55：
鮮奶茶的單價平均
=AVERAGEIF(B2:B10, "鮮奶茶", D2:D10)結果是 55。信義而且不是青茶的單價平均，(45 + 55) ÷ 2 = 50：
信義且排除青茶的單價平均
=AVERAGEIFS(D2:D10, A2:A10, "信義", B2:B10, "<>青茶")結果是 50。鮮奶茶最高的單價：
鮮奶茶最高單價
=MAXIFS(D2:D10, B2:B10, "鮮奶茶")結果是 58。左營最低的單價：
左營最低單價
=MINIFS(D2:D10, A2:A10, "左營")結果是 38。要注意 AVERAGEIF 的平均範圍放在最後（還可以省略），AVERAGEIFS 跟 MAXIFS 則是第一個參數就是要算的範圍，我自己常把這兩種順序搞反，寫完回頭看一眼。沒有任何一列符合條件的話會是什麼結果，在 條件彙總實戰 有整理。
加權平均
AVERAGE.WEIGHTED
依權重算平均，權重大的值對結果影響比較大。
語法：
- 數值：要平均的範圍。
- 權重：每個數值對應的權重，大小要跟數值一樣。這個例子就是杯數。
- 其他數值、其他權重：選填，可以成對一直往後加。
九筆全部的加權平均單價，先手算：(34 × 45 + 26 × 55 + 9 × 40 + 43 × 45 + 11 × 58 + 22 × 40 + 17 × 42 + 47 × 52 + 12 × 38) ÷ 221 = 10387 ÷ 221 = 47。
依杯數加權的平均單價
=AVERAGE.WEIGHTED(D2:D10, C2:C10)你應該會得到 47。同一批單價直接用 AVERAGE，是 46.11（四捨五入到小數第二位），比實際平均每杯收入低。
權重也不一定是杯數。算成績最常見。另開一張工作表，三次考試 78、64、91 分，比重是 0.25、0.35、0.4，A2:A4 放分數、B2:B4 放比重：
三次考試依比重加權
=AVERAGE.WEIGHTED(A2:A4, B2:B4)手算 78 × 0.25 + 64 × 0.35 + 91 × 0.4 = 78.3，因為比重加起來剛好是 1，結果就是 78.3。比重加起來不是 1 也可以，函式會自己除以權重的總和。
資料分在兩個地方的話，用後面兩個參數接上去。分數在 A2:A3、比重在 B2:B3，第三次考試在 A4、B4 單獨一格：
用其他數值、其他權重補上第三筆
=AVERAGE.WEIGHTED(A2:A3, B2:B3, A4, B4)結果同樣是 78.3。我個人還是傾向把資料排成連續兩欄，一個範圍搞定，比較不容易漏。
這個函式沒有條件參數；官方說明要求權重不可為負、至少有一個要大於 0，否則會出錯。下一節就是怎麼加條件。
如果你在 Excel 用，要注意 Excel 沒有 AVERAGE.WEIGHTED，加權平均要寫成 。下面「不靠 FILTER」那種 SUMPRODUCT 寫法，兩邊都能用。
每家門市的加權平均單價
我想要一張表：每家門市賣了幾杯、簡單平均單價、加權平均單價，放在 F1:I4，F 欄是門市名稱。
加權平均加條件，最短的寫法是用 FILTER 先把這家店的單價和杯數挑出來，再丟給 AVERAGE.WEIGHTED。信義是：
信義的加權平均單價，四捨五入到小數第二位
=ROUND(
AVERAGE.WEIGHTED(
FILTER($D$2:$D$10, $A$2:$A$10=F2),
FILTER($C$2:$C$10, $A$2:$A$10=F2)
),
2
)F2 是「信義」，結果 48.12，跟前面手算的 3320 ÷ 69 一樣。
另一種是不靠 FILTER，直接用 SUMPRODUCT 算分子、SUMIFS 算分母，兩個函式都是之前介紹過的：
用 SUMPRODUCT 與 SUMIFS 算信義的加權平均單價
=ROUND(
SUMPRODUCT(($A$2:$A$10=F2)*$C$2:$C$10*$D$2:$D$10) /
SUMIFS($C$2:$C$10, $A$2:$A$10, F2),
2
)一樣是 48.12。杯數那欄用 SUMIFS，簡單平均用 AVERAGEIFS，往下拖曳到板橋、左營：
| 門市 | 總杯數 | 簡單平均單價 | 加權平均單價 |
|---|---|---|---|
| 信義 | 69 | 46.67 | 48.12 |
| 板橋 | 76 | 47.67 | 45.43 |
| 左營 | 76 | 44 | 47.55 |
簡單平均來看，板橋比信義貴（47.67 對 46.67）；加權之後反過來，信義 48.12、板橋 45.43。原因是板橋的蘋果紅茶賣了 43 杯，單價只有 45，把整體拉低了。報表只看簡單平均的話，結論剛好相反。
兩種寫法我比較常用 SUMPRODUCT 加 SUMIFS，因為分母那個總杯數本來就想列在報表上。要一次算很多家店、不想看到一長串的話，FILTER 的寫法比較好讀，這就看個人了。
左營的加權平均單價
用 AVERAGE.WEIGHTED 搭配 FILTER，算出左營的加權平均單價，四捨五入到小數第二位。
算平均之前先看一眼，每一列的分量一不一樣，不一樣就用 AVERAGE.WEIGHTED。條件寫法如果還不熟，回頭看前面連結的幾篇。我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章SUMIF 與 COUNTIF 入門：用一個條件加總和計數
SUMIF、COUNTIF 的語法、條件怎麼寫（文字、比較符號、萬用字元與波浪號），把條件放在儲存格裡的 & 寫法，以及簡單提到的 AVERAGEIF。只談單一條件。
SUMIFS 進階陷阱：多條件、日期區間、萬用字元與 MAXIFS
整理 SUMIFS、COUNTIFS 多條件加總常踩的坑：參數順序和 SUMIF 相反、範圍大小不一致、日期區間與文字日期、OR 條件重複計算、萬用字元與 ~ 跳脫、空白條件，以及 MAXIFS、MINIFS、AVERAGEIFS 與 COUNTUNIQUEIFS。
月報表怎麼做：用 SUMIFS 做門市 × 月份彙總與達成率
拿一份茶飲銷售明細，用 SUMIFS 做出門市 × 月份的月報矩陣，再用 COUNTIFS、AVERAGEIFS、MAXIFS 算筆數、平均與單筆最高，日期區間放在儲存格，最後加上目標與達成率。
留言討論
共 0 則留言