Excel用AverageIfs函數多條件求平均值,含同列雙條件的實例

在 Excel 中，如果要多條件求平均值，可以用AverageIfs函數，它最多可以有 127 個條件，每個條件對應一個區域，即可以組合 127 個條件範圍/條件對，並且一個條件範圍即同列可以組合多個條件。AverageIfs函數與AverageIf函數的區別除能組合多個條件外，還表現在AverageIfs函數要求求平均值範圍與條件範圍必須一致，而AverageIf函數則不要求一致。以下是Excel用AverageIfs函數多條件求平均值的具體實例，含同列雙條件等六個實例，實例操作所用版本均為 Excel 2016。

一、AverageIfs函數語法

1、表達式：AVERAGEIFS(Average_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], ...)

中文表達式：AVERAGEIFS(求平均值範圍, 條件範圍1, 條件1, [條件範圍2, 條件2], ...)

2、說明：

A、條件範圍1與條件1組成一個條件範圍/條件對，最多可以有 127 個條件範圍/條件對。

B、條件可以是單純的文字，例如「白色」；也可以用大小於和等於號，例如 ">=100" 或 ">="&100。另外，條件還可以使用通配符問號（?）和星號（*），問號表示一個字符，星號表示一個或多個字符，如果要查找問號或星號，需要在它們前面加轉義字符 ~，例如 ~*。

C、如果選定的單元格中有邏輯值 True 或 False 都將被忽視。

D、如果 Average_Range 為空值、文本值或無法轉換為數字的其它內容，將返回分母為 0 錯誤（即 #DIV/0! 錯誤）。

E、如果條件中包含空單元格，將被視為 0 值；如果選定的區域沒有滿足條件的單元格，將返回 #DIV/0!錯誤。

F、AverageIfs函數條件範圍必須與求平均值範圍一致，這點與AverageIf函數允許條件範圍與求平均值範圍不一致不同。

二、AverageIfs函數的使用方法及實例

(一）只有一個條件範圍/條件對的實例

1、假如要求價格大於等於 90 的服裝銷量的平均值。選中 E2 單元格，把公式 =AVERAGEIFS(D2:D10,C2:C10,">40") 複製到 E2，按回車，返回求平均值結果 624.25；操作過程步驟，如圖1所示：

圖1

2、說明：公式 =AVERAGEIFS(D2:D10,C2:C10,">40") 中求平均值範圍為 D2:D10，條件範圍為 C2:C10，條件為 ">40"。

(二）邏輯值 True 或 False 都被忽視的實例

1、選中 B1 單元格，把公式 =AVERAGEIFS(A1:A4,B1:B4,">=30") 複製到 B1，按回車，返回 14；雙擊 B1 單元格，把公式改為 =AVERAGEIFS(B1:B4,A1:A4,">=0")，按回車，返回 46；操作過程步驟，如圖2所示：

圖2

2、說明：

A、A1 至 A4 中分別有一個邏輯值 True 和 False，當 A1:A4 為求平均值範圍時，返回結果為 14，而條件是 B1:B4 中的數值必須大於等於 30，符合條件的有 A2、A3 和 A4，如果 True 被轉為 1，結果應該為 (14 + 1) / 2 = 7.5，由此可知邏輯值 True 和 False 都沒有計入求平均值。

B、當把 B1:B4 改為求平均值範圍時，返回結果為 31.5，而條件是 A1:A4 中的數值必須大於等於 0，假設 True 被轉為 1、False 被轉為 0，則 B1 至 B4 都符合條件，結果應該為 (17 + 53 + 46 + 81) / 4 = 49.25，而 (17 + 46) / 2 = 31.5，說明邏輯值 True 和 False 也都沒有被計入求平均值。

（三）選定單元格為空值、文本值或無法轉換為數字的其它內容的實例

1、把公式 =AVERAGEIFS(B1:B5,A1:A5,">=0") 複製到 B1 單元格，按回車，返回 #DIV/0! 錯誤，選中 B2，輸入 5，選中 B3，仍然返回 #DIV/0! 錯誤，輸入 6，選中 B4，返回 6；操作過程步驟，如圖3所示：

圖3

2、說明：

A、公式中 B1:B5 為求平均值範圍，條件為 A1 至 A5 中的數值大於等於 0，假設 A2 中的空值被轉為 0，則 B1 至 B5 都滿足條件，假設 B1 至 B5 中的所有空值也都被轉為 0，應該有這樣的算式 0 / 5 = 0，返回結果應該 0，但返回 #DIV/0! 錯誤，說明空單元格只是被視為 0，並沒被轉為數值型 0，從這個公式 =AVERAGEIFS(B1:B5,A1:A5,">="&A2) 可以得到進一步認證，如圖4所示：

圖4

B、選中公式 =AVERAGEIFS(B1:B5,A1:A5,">="&A2) 所在的單元格 C2，按住 Alt，按一次 M，按一次 V，打開「公式求值」窗口，單擊「求值」，A2 被轉換為 0，如圖5所示：

圖5

C、再點一次求值，0 不見了，如圖6所示：

D、當在 B2 中輸入 5 時，仍然返回 #DIV/0! 錯誤，也說明空單元格（A2）只被視為 0 而沒有被轉為數值 0。當在 B3 中輸入 6 時，立即返回 6，B3 對應的單元格為 A3，A3 滿足大於等於 0 的條件，因此有 6 / 1 = 6。

（四）兩個條件範圍/條件對的實例（條件中同時使用通配符問號 ? 和星號 * ）

1、假如求服裝表中「編號」以 WS 開頭、「產品名稱」由四個字組成的服裝銷量的平均值。把公式 =AVERAGEIFS(F2:F12,A2:A12,"WS*",B2:B12,"????") 複製到 G2 單元格，按回車，返回結果 667.5，操作過程步驟，如圖7所示：

圖7

2、公式說明：公式 =AVERAGEIFS(F2:F12,A2:A12,"WS*",B2:B12,"????") 第一個條件範圍/條件對為 A2:A12,"WS*"，表示在 A2 至 A12 中，選出所有以 WS 開頭的服裝；第二個條件範圍/條件對為 B2:B12,"????"，表示選出 B2 至 B12 中所有由四個字組成的服裝。

（五）同一區域既為求平均值範圍又為條件範圍的實例

1、假如要求分類為「襯衫」、銷售地區為「廣州」且銷量大於等於 500 的服裝銷量的平均值。把公式 =AVERAGEIFS(F2:F12,C2:C12,"襯衫",D2:D12,"廣州",F2:F12,">=500") 複製到 G2 單元格，按回車，返回 563；操作過程步驟，如圖8所示：

8

2、公式中 F2:F12 既是求平均值範圍又是第三個條件的條件範圍，說明同一區域既可為求平均值範圍又可為條件範圍。

（六）同列多個條件求平均值

1、假如要求銷售地區為「廣州」、價格大於等於 80 同時小於等於 90 的服裝銷量的平均值。把公式 =AVERAGEIFS(F2:F12,D2:D12,"廣州",E2:E12,">=80",E2:E12,"<=90") 複製到 G2 單元格，按回車，返回 625，操作過程步驟，如圖9所示：

圖10

2、公式中 E2:E12 有兩個條件，即 ">=80" 和 "<=90"，這種情況 AverageIfs函數能返回正確的結果；但同列是兩個純文字的條件，則會返回 #DIV/0! 錯誤；例如求銷售地區為「廣州和深圳」的服裝銷量的平均值，把公式 =AVERAGEIFS(F2:F12,D2:D12,"廣州",D2:D12,"深圳") 複製到 G2 單元格，按回車，返回 #DIV/0! 錯誤，演示如圖10所示：

圖11