【Excel函數說說之2】AVERAGE、AVERAGEA、AVERAGEIF、AVERAGEIFS函數的基本語法與用法

  1,AVERAGE函數,返回參數的平均值(算術平均值)。

其語法結構是:

AVERAGE(number1,number2,…)

Number1, number2, … 是要計算其平均值的 1 到 255 個數字參數。

舉例,

=AVERAGE(1,5,7,8)=5.25

=AVERAGE(1,5,7,TRUE)=3.5

=AVERAGE(1,5,7,”7”)=5

如果,A1:A4單元格區域裡分別是:

1

5

7

TRUE

那,=AVERAGE(A1:A4)=4.33

注意,為什麼同樣是對1,5,7,true求平均,為什麼一個結果是3.5,一個是4.33呢。那是因為average函數對於直接輸入到參數列表裡的邏輯值是參與計算的,TRUE當作1計算,FALSE當作0計算。

但是,如果是用單元格區域引用來作為average函數的參數,那麼單元格裡的TRUE或FALSE,都會被忽略的。

其實,A4單元格的TRUE換成文本格式的7,再用=AVERAGE(A1:A4)=4.33,你會發現對於單元格引用作為average函數的參數,文本的數字7也會被忽略掉不參與計算的。

這就是AVERAGE函數的一個需要注意的地方。

那麼,如果是上面這樣的特別情況,該用哪個函數才能把邏輯值或文本格式的數字在作單元格引用的時候也統計在內呢?

那就要提到AVERAGE函數的兄弟函數AVERAGEA函數啦。

2,AVERAGEA函數,返回參數的平均值(算術平均值)

這個函數的語法結構同上。

接上例,

=AVERAGEA(A1:A4)=3.25

無論A4單元格是文本,還是文本格式的數字,還是邏輯值,結果值都為3.25。

那麼3.25是怎麼來的呢,=(1+5+7+0)/4=13/4=3.25

3,AVERAGEIF函數,返回某個區域內滿足給定條件的所有單元格的平均值(算術平均值)。

Range是要計算平均值的一個或多個單元格,其中包括數字或包含數字的名稱、數組或引用。

Criteria是數字、表達式、單元格引用或文本形式的條件,用於定義要對哪些單元格計算平均值。例如,條件可以表示為 32、”32”、”>32”、”apples” 或 B4。

Average_range是要計算平均值的實際單元格集。如果忽略,則使用 range。

財產值A列 傭金B列

100,000 7,000

200,000 14,000

300,000 21,000

400,000 28,000

公式

說明(結果)

=AVERAGEIF(B2:B5,”<23000”) 求所有傭金小於 23,000 的平均值 (14,000)

=AVERAGEIF(A2:A5,”<95000”) 求所有財產值小於 95,000 的平均值 (#DIV/0!)

=AVERAGEIF(A2:A5,”>250000”,B2:B5) 求所有財產值大於 250,000 的傭金的平均值 (24,500)

4,AVERAGEIFS函數,

返回滿足多重條件的所有單元格的平均值(算術平均值)。

語法

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range是要計算平均值的一個或多個單元格,其中包括數字或包含數字的名稱、數組或引用。

Criteria_range1, criteria_range2, …是計算關聯條件的 1 至 127 個區域。

Criteria1, criteria2, …是數字、表達式、單元格引用或文本形式的 1 至 127 個條件,用於定義要對哪些單元格求平均值。例如,條件可以表示為 32、”32”、”>32”、”apples” 或 B4。

學生 第一次測驗成績 第二次測驗成績 最終測驗成績

Emilio 75 85 87

Julie 94 80 88

Hans 86 93 不及格

Frederique 不及格 75 75

公式 說明(結果)

=AVERAGEIFS(B2:B5,B2:B5,”>70”,B2:B5,”<90”) 求所有第一次測驗成績在 70 和 90 之間的所有學生的平均成績 (80.5)

=AVERAGEIFS(C2:C5,C2:C5,”>95”) 求所有第二次測試成績高於 95 的所有學生的平均成績,但是不存在 (#DIV/0!)

=AVERAGEIFS(D2:D5,D2:D5,”<>不及格”,D2:D5,”>80”) 求所有第一次測驗成績高於 80 而且未標記為「不及格」的所有學生的平均成績 (87.5)

上述這4個函數呢,AVERAGE函數呢是老牌函數了,AVERAGEA函數2003版Excel也有了。不過這些函數在工作中使用頻率真不算高。

後來2007的excel,又增加了AVERAGEIF函數和AVERAGEIFS函數,這2個函數都是用來按條件求平均值的,前者是單條件求平均值,後者是多條件求平均值,以後大家按條件求平均值多用用。不過這2個函數要正常使用和保存文件,並能讓其他版本的人能正常顯示結果和編輯,還是比較麻煩,因為這2個新函數存在的工作簿必須保存為後綴名為XLSX格式,因此2003版本的excel就看不了這個工作簿了。