Chapter 6. 函式 (一)：邏輯與運算 › 6.6 包含條件的統計值
6.6 包含條件的統計值
本節會介紹包含條件的統計資料相關的函式，又包括單一條件、多條件的函式，將先介紹各函式的用法，再使用範例說明：
- 計算空格數量 / 不重複的值的數量：
語法 | COUNTBLANK / COUNTUNIQUE (值_1, [值_2,…]) |
輸入 | 值：要計算的範圍，可以是多個儲存格，或是單一值都可以，例如 =COUNTBLANK(A1:A4) 或 =COUNTUNIQUE(A1:A4, 0, 1)。 |
輸出 | 值 中有多少個空白 / 不重複的值。 |
- 單一條件 — 計算符合條件的數量 / 比例：
語法 | |
輸入 |
若要實際比對 ? 或 *，可在其前面加上 ~，例如 ~?。 另外，若要在 條件 中使用儲存格的資料，可使用 & 連接儲存格，例如"<="&A1、"*"&A1&"*" 等。 |
輸出 | 範圍 符合 條件 的資料筆數 / 比例。 |
- 單一條件 — 計算符合條件的項目加總 / 平均：
語法 | |
輸入 |
|
輸出 | 條件範圍 符合 條件 的資料中，加總 / 平均範圍 的加總 / 平均。 |
- 多條件 — 計算符合所有條件的數量：
語法 | COUNTIFS (條件範圍_1, 條件_1, [條件範圍_2, 條件_2,...]) |
輸入 |
|
輸出 | 所有 條件範圍_n 皆符合對應的 條件_n 的資料筆數。 |
- 多條件 — 計算符合所有條件的不重複值數量 / 最小值 / 最大值 / 加總 / 平均：
語法 | COUNTUNIQUEIFS / MINIFS / MAXIFS / SUMIFS / AVERAGEIFS (範圍, 條件範圍_1, 條件_1, [條件範圍_2, 條件_2,…]) |
輸入 |
|
輸出 | 所有 條件範圍_n 皆符合對應的 條件_n 者，其 範圍 的不重複值數量 / 最小值 / 最大值 / 加總 / 平均。 |
以下使用第 1 章的交易記錄檔並搭配九個範例說明各函式的用法，為了方便說明在此已將所有交易記錄檔的各欄位使用已命名範圍命名成表頭名稱：
- 計算交易記錄檔有多少筆交易
公式
=COUNTUNIQUE(交易編號)-1
解說
因為一筆交易若購買多個貨品時會超過一列，因此交易筆數為計算不重複的交易編號，另外因為表頭也會算進去，因此要減掉 1。
- 計算有多少比例的資料店號是 105
公式
=PERCENTIF(店號,105)
解說
條件也可輸入 "=105"。
- 計算有多少筆的資料貨名是以「台灣」開頭
公式
=COUNTIF(貨名,"台灣*")
解說
在此使用萬用字元 *，即可將條件設為開頭為「台灣」。
- 計算所有 2023 年的訂單總金額
公式
=SUMIF(交易日期,">=2023/1/1",貨品總金額)
解說
- 訂單總金額為貨品總金額而非交易總金額的加總。
- 交易日期的條件可使用各種 Google Sheets 可辨識的日期寫法，例如">=2023-01-01"、">=2023/01/01"、">=23/1/1" 等皆可。
- 計算「MATSUSEI背心袋 10KG」在 2023 年有幾筆資料
公式
=COUNTIFS(交易日期,">=2023/1/1",貨名,"MATSUSEI背心袋 10KG")
解說
有兩個條件所以使用 COUNTIFS，條件的順序不限。
- 計算「會員卡號 8159」在 2022 年下半年消費總額
公式
=SUMIFS(貨品總金額,交易日期,">=2022/7/1",交易日期,"<=2022/12/31",會員卡號,8159)
解說
有三個條件所以使用 SUMIFS，前兩個為日期區間、第三個為會員卡號。
- 計算 2022 年下半年有多少筆訂單金額超過 500 元的訂單
公式
=COUNTUNIQUEIFS(交易編號,交易日期,">=2022/7/1",交易日期,"<=2022/12/31",交易總金額,">500")
解說
要計算有條件的不重複項目使用 COUNTUNIQUEIFS，有三個條件，前兩個為日期區間設定、第三個為訂單金額 (交易總金額) 超過 500 元。
- 將貨號的銷售記錄超過 10 筆的資料整列 (A:L 欄) 填滿顏色改為「淺藍色 3」
公式
套用範圍「A:L」，自訂公式「=COUNTIF($G:$G,$G1)>10」。
解說
- 在條件式格式設定中不適用已命名範圍，所以使用儲存格範圍。
- 若要判斷貨號是否超過 10 次，可以用 COUNTIF 計算每一列的貨號共出現幾次，再判斷是否 >10 即可。
- 將當日單店銷售金額超過 1,000 元的資料整列 (A:L 欄) 填滿顏色改為「淺綠色 3」
公式
套用範圍「A:L」，自訂公式
「=SUMIFS($K:$K,$C:$C,$C1,$D:$D,$D1)>1000」。解說
若要判斷當日單店銷售金額是否超過 1,000 元，可使用 SUMIFS 計算該列的店號、交易日期的貨品總金額加總，再判斷是否 >1000 即可。
本節的公式在工作上非常實用，本章最後一節也會進一步介紹如何使用這些公式來建立儀表板 (Dashboard)，絕對是文書處理時最重要的技能之一。