按條件算平均:AVERAGEIF / AVERAGEIFS

更新於 發佈於 閱讀時間約 8 分鐘

這是「按條件算OO」系列文的第二篇教學!今天會來聊聊 AVERAGEIFAVERAGEIFS

前些日子我們把「聚集函式御五家」都搜集在一起了,有 SUMAVERAGECOUNT / COUNTAMAXMIN。這些都是常見又好上手的新手函式,接下來我們要試著把它們和條件的 IFIFS 結合,讓你可以按照自己定義的條件來算總和、平均、數量、最大值與最小值喔!

歡迎來複製一份練習用的試算表,一起來試試吧。




AVERAGEIF

語法

=AVERAGEIF(搜尋條件範圍, 條件, [求平均的數字範圍])
  • 搜尋條件範圍:要做條件篩選的範圍。等等 AVERAGEIF 就會用你指定的條件,在這個範圍搜尋符合條件的儲存格。
  • 條件:要篩選的條件。
  • 求平均的數字範圍:選填,如果你想求的平均數字的範圍和第一個參數的範圍(搜尋條件範圍)不同,這邊就可以再指定一個你希望 AVERAGEIF 求平均的範圍在哪。


條件要怎麼寫?

官方文件給了一些範例:

  • 等於,用等號開頭或是直接指定:"=文字""=1""文字"1如果是直接指定文字的話,要記得在文字外加上一組雙引號,數字的話就不要(1)。
  • 大於:">1"
  • 大於或等於:">=1"
  • 小於:"<1"
  • 小於或等於:"<=1"
  • 不等於:"<>1""<>文字"

另外,如果你的條件有不確定的文字,可以搭配半形「?」或是半形「*」這兩個萬用字元來幫忙,做出模糊搜尋。記得:

  • ?」表示不確定的文字只有一個
  • *」表示不確定的文字有零個、或很多個

舉例來說,來看看不確定文字只有一個的「?」怎麼寫:

=MAXIFS(A:A, "蘋果?茶", B:B)

試算表會在 A 欄裡面搜尋符合「蘋果O茶」條件的文字後找最大值。

那麼「蘋果紅茶」、「蘋果青茶」、「蘋果綠茶」就會符合,但「蘋果茶」卻不會符合,「蘋果烏龍茶」、「蘋果普洱茶」也不符合。

再來看看不確定的文字有零個、或很多個的「*」:

=MAXIFS(A:A, "蘋果*茶", B:B)

在 A 欄裡面搜尋符合「蘋果*茶」的文字。所以「蘋果紅茶」、「蘋果青茶」、「蘋果綠茶」會符合條件,「蘋果茶」也會,「蘋果烏龍茶」、「蘋果普洱茶」也會。

你也可以把「?」跟「*」放一起,做複雜的條件搜尋。舉例來說:

=MAXIFS(A:A, "?蘋果*茶", B:B)

在 A 欄裡面搜尋符合「O蘋果*茶」的文字,「青蘋果紅茶」、「青蘋果青茶」、「紅蘋果烏龍茶」就會符合條件,但「蘋果茶」、「蘋果烏龍茶」、「蘋果普洱茶」就會不符。

如果你想找的關鍵字恰好是「?」或是「*」的話,可以在前面加個波浪號「~」:

=MAXIFS(A:A, "~?", B:B)

這樣就可以在 A 欄裡面尋找「?」這個文字。

=MAXIFS(A:A, "~*", B:B)

同理,這樣就可以在 A 欄找「*」這個文字。

不過,這類「按條件找 OO」的函式無法直接以函式結果作為條件使用,只能指定靜態的值、利用輔助欄或其他的替代方案來做,要小心一下。


練習

打開試算表,來看看叫做「AVERAGEIF」的工作表吧:

raw-image


我們來試著找看看「蘋果紅茶」平均售出數量是多少吧!在 D2 這邊寫:

=AVERAGEIF(A2:A14, "蘋果紅茶", B2:B14)

拆解一下:

  • 搜尋條件範圍A2A14
  • 條件"蘋果紅茶"。因為是文字,要加上雙引號。
  • 求平均的數字範圍B2B14

來看看結果:

raw-image


再來試試看另外兩個吧!條件寫好的話,一切都相當簡單。我們來找「蘋果O茶」的平均售出數量。

D5 這邊寫:

=AVERAGEIF(A2:A14, "蘋果?茶", B2:B14)
  • 搜尋條件範圍A2A14
  • 條件"蘋果?茶"。因為是文字,要加上雙引號。
  • 求平均的數字範圍B2B14

按下 Enter,結果就像這樣:

raw-image


最後,來試試看「任何蘋果*茶」吧,試著在 D8 寫:

=AVERAGEIF(A2:A14, "蘋果*茶", B2:B14)
  • 搜尋條件範圍A2A14
  • 條件"蘋果*茶"。因為是文字,所以要加上雙引號。
  • 求平均的數字範圍B2B14

結果如下:

raw-image




AVERAGEIFS

語法

=AVERAGEIFS(求平均的數字範圍, 第一組條件範圍, 第一組條件, [第二組條件範圍], [第二組條件], ...)

這個語法就稍稍長了一點,不過還是很好理解的!我們慢慢看:

  • 求平均的數字範圍:你想要求平均的範圍。
  • 第一組條件範圍:要 AVERAGEIFS 判斷的第一組條件範圍。
  • 第一組條件:要 AVERAGEIFS 判斷的第一組條件。
  • 第二組條件範圍:選填,要 AVERAGEIFS 判斷的第二組條件範圍。
  • 第二組條件:選填,要 AVERAGEIFS 判斷的第二組條件。

當然你還可以往後寫第三組、第四組、第 N 組,看你的條件有多少。

條件的寫法和剛剛 AVERAGEIF 的寫法一樣,文字要用雙引號、可以用大於、小於、等於,也可以用萬用字元(?*)。要記得條件範圍和條件必須要成雙成對地出現,不可以只寫其中一個喔!


練習

切換到「AVERAGEIFS」的工作表,來看看這邊的練習吧:

raw-image

這邊準備了三個題目,來一一拆解吧!


已售出的蘋果紅茶的平均銷量

我們已經知道銷量在 B 欄了,所以等等的第一個參數就寫 B2:B14 即可。

再來是兩個條件:

  • C 欄的「已售出」等於「是」
  • 第二個是 A 欄的「品項」等於「蘋果紅茶」

那到這邊,我們就可以試著把全部的線索串起來了!在 E2 寫:

=AVERAGEIFS(B2:B14, C2:C14, "是", A2:A14, "蘋果紅茶")
  • 求平均的數字範圍B2B14
  • 第一組條件範圍C2C14(已售出)。
  • 第一組條件"是"。因為是文字的關係,要用雙引號包起來。
  • 第二組條件範圍A2A14(品項)。
  • 第二組條件"蘋果紅茶"。因為是文字的關係,要用雙引號包起來。

來看看結果:

raw-image


未售出的蘋果綠茶的平均銷量

試試看第二題吧!跟第一題的做法其實很像,把條件稍稍更改一下即可。拆解一下題目的兩個條件:

  • C 欄為「否」。
  • A 欄為「蘋果綠茶」。

好,在 E5 這邊把它們組合在一起吧!你會得到:

=AVERAGEIFS(B2:B14, C2:C14, "否", A2:A14, "蘋果綠茶")
  • 求平均的數字範圍B2B14
  • 第一組條件範圍C2C14(已售出)。
  • 第一組條件"否"。因為是文字的關係,要用雙引號包起來。
  • 第二組條件範圍A2A14(品項)。
  • 第二組條件"蘋果綠茶"。因為是文字的關係,要用雙引號包起來。

來看看執行結果:

raw-image


未售出的各種蘋果茶的平均銷量

最後來試試看萬用字元「*」的應用方式。定義一下「各種蘋果茶」的意思,只要 A 欄符合「蘋果O茶」的條件就算,像是「蘋果綠茶」、「蘋果紅茶」、「蘋果烏龍茶」等都算!回頭看看,這邊的條件跟剛剛有點像:

  • C 欄為「否」。
  • A 欄為「蘋果O茶」。

要怎麼達成第二個「各種蘋果茶」的條件呢?寫「蘋果*茶」就可以囉!

把這些線索串起來,在 E8 這邊寫:

=AVERAGEIFS(B2:B14, C2:C14, "否", A2:A14, "蘋果*茶")

來看看結果,對答案吧!

raw-image


完成!到這邊你就學會了 AVERAGEIFAVERAGEIFS 了。歡迎你在你的試算表上面嘗試看看,如果有什麼問題,可以在下面留言告訴我唷!




如果你喜歡這次的文章,歡迎你透過這些方法支持我:

  • 按下愛心、按下儲存
  • 留言告訴我你的想法
  • 加入喜特先生的官方沙龍,即時看到我發布的教學
  • 付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
  • 追蹤喜特先生的 Facebook
  • 這邊小額贊助我的創作!

想要看更多文章的話,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
喜特先生 Mr. Sheet -avatar-img
發文者
2024/03/08
「按條件算OO」系列文,也上線囉!提及了這篇文章,趕快過去看看吧!
avatar-img
喜特先生官方沙龍
19.9K會員
153內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
看更多
你可能也想看
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
高中數學主題練習—指數律基本練習
Thumbnail
高中數學主題練習—指數律基本練習
Thumbnail
高中數學主題練習—標準化計算
Thumbnail
高中數學主題練習—標準化計算
Thumbnail
喜特先生特別為 Google 試算表新手撰寫了「聚集函式御五家」系列文,將介紹五種簡單又實用的函式:SUM、AVERAGE、COUNT 與 COUNTA、MAX、MIN。每篇文章都會詳細解釋函式的語法,並附上一個實例讓讀者可以跟著練習,輕鬆掌握 Google 試算表的資料分析技巧!
Thumbnail
喜特先生特別為 Google 試算表新手撰寫了「聚集函式御五家」系列文,將介紹五種簡單又實用的函式:SUM、AVERAGE、COUNT 與 COUNTA、MAX、MIN。每篇文章都會詳細解釋函式的語法,並附上一個實例讓讀者可以跟著練習,輕鬆掌握 Google 試算表的資料分析技巧!
Thumbnail
高中數學主題練習—平均數計算
Thumbnail
高中數學主題練習—平均數計算
Thumbnail
有招 LET,可以讓你的長算式更清晰好讀!
Thumbnail
有招 LET,可以讓你的長算式更清晰好讀!
Thumbnail
這是「按條件算OO」系列文的第五篇教學!今天會來聊聊 MINIFS。
Thumbnail
這是「按條件算OO」系列文的第五篇教學!今天會來聊聊 MINIFS。
Thumbnail
這是「按條件算OO」系列文的第四篇教學!今天會來聊聊 MAXIFS。
Thumbnail
這是「按條件算OO」系列文的第四篇教學!今天會來聊聊 MAXIFS。
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News