excel如何求和再求平均值（Excel從實例出發來談談多條件求平均值或求和的思路是什麼）

2023-04-16 02:27:55 1

童鞋們可能都知道多條件求平均值或求和的函數是averageifs和sumifs，因為這兩個函數就是excel專門用來進行多條件計算的。

但在今天這個示例中，當使用averageifs來求平均值，卻遇到了一些麻煩。

下面我們輸入一個averageifs函數公式：

=AVERAGEIFS(B2:B184,A2:A184,A169,B2:B184,">"&LARGE(B2:B184,11))

得到的結果與前面一張圖的結果並不一致，是錯誤的結果，這是因為在公式中，第2個條件」排名前10「，使用了運算符，大於LARGE(B2:B184,11)，含義是大於排名前10的分數。

但這句話有一個易錯點，即它是大於所有考生中排名前10的分數，重點是「所有」。

而我們的目的是計算B班的考生前10的平均分數。

因此從某種角度來講，「B班排名前10的平均分」，兩個條件具有一定的從屬性，即第2個條件是在第1個條件的基礎上來進行計算，而averageifs函數中的多個條件設置是並列的，並不存在所謂的從屬關係。

那麼，在這樣的情形下，我們要怎麼來求解平均值？

解題的思路是什麼？

分為兩個部分。

第一個部分是多條件的設置。

第二個部分則是求平均的函數設置。

我們先寫下完整的公式，結合公式來講解兩個部分的含義。

完整公式為：{=AVERAGE(LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10)))}

在這個公式中，第一部分條件設置是LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10))，第二部分是AVERAGE函數設置。

我們知道average是計算平均值的函數，但它並不是專用來計算指定條件下的平均值。

但有一點，average函數的參數，是一組數據，這個組數據可以是單元格區域也可以是一個數組。那麼，如果我們將符合條件的所有數據組合成一個數組，那是不是也能達到按條件求平均的目的！？

因此，現在有了思路的第一步，即第二部分的函數設置，之所以將函數設置設為第二部分內容，是因為多條件的設置才是關鍵，是整個公式中的精髓所在。

對於函數的選擇，其實都是比較淺易的，在數學和統計函數中，求平均值，則使用average函數，求和則使用sum函數，求最大值用max，計數用count等等。

確定了使用的函數，我們就要思考如何將多條件轉換為該函數的參數值。

在作者的《excel100個常見函數快速入門》中，第1節就介紹了邏輯函數的使用。

邏輯函數的作用是判斷一個表達式是否成立，成立則返回true，換算成數值則等於1，不成立會返回false，換算成數值等於0.

而條件是可以通過判斷得到一個true或者false的結果，也就是大家常說的條件判斷。

所以某校B班這個條件，很明顯可以設置為「A2:A184=A2」，A2:A184是條件的數據列，A2是條件的參照值，也就是等於A2時為true，不等於時為false。這裡之所以輸入A2，是因為A2就等於「B班」，所以直接引用了單元格地址，當然我們也可以手動輸入「B班」的文本。

因此它的結果是一串true和false組成的數組數據。

接下來是B班前10的分數。

這句話我們可以這樣來分解，首先計算B班所有考生的分數，在從中選擇前10的分數。

而計算B班所有考生的分數，我們可以寫下這個表達式：(A2:A184=A2)*B2:B184

從上面的說明我們知道A2:A184=A2的結果是true和false組成的一組邏輯值，用數值來表示就是1和0組成的一組數據，那麼1和0乘以另一組數據，會得到不同的結果，0乘以任意數據則等於0，而1乘以任意數值則會等於該數值。

所以實質上，這個表達式其實是條件的兩步走，第1步是等於B班的條件設置，第2步是返回得到分數值的條件轉換。

符合B班的考生的結果是1，乘以B2:B184單元格區域中的分數，則返回該分數值，由於符合B班的條件結果有多個true值1，因此與B列數據相乘便得到了多個分數，而組成一個數組。

得到了B班所有考生的分數，再使用large函數選取指定排名的數據。

表達式為：LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10))

我們忽略表達式中的N函數，即「LARGE((A2:A184=A2)*B2:B184,ROW($1:$10))」。

(A2:A184=A2)*B2:B184是數據區域，ROW($1:$10)是要計算第幾個最大值的數據。

這裡又使用了row函數來運算得到一個從1到10的數組，作為large函數的第2參數，表示計算第1大到第10大的值。

於是它的結果也會得到(A2:A184=A2)*B2:B184結果中的第1到第10的一個分數數組。

large函數具體的語法解釋和用法，可以參照作者在專欄中的講解。

那回到開頭所言：

但有一點，average函數的參數，是一組數據，這個組數據可以是單元格區域也可以是一個數組。那麼，如果我們將符合條件的所有數據組合成一個數組，那是不是也能達到按條件求平均的目的！？

現在我們已經得到了一個符合班級、分數和排名條件的數組，便可以直接使用average函數來求平均了。

但需要注意的一點是，當一個數組作為它的一個參數，是一個數組公式，因此需要三鍵結束，才能得到正確結果。

綜上所述，進行多條件求平均的思路是，首先確定當前情境需要使用的函數，求平均則average函數，然後進行多條件的設置，並將其轉換為一個可以進行運算的數組。同時也要考慮某個條件是否可以通過其他途徑來代替，比如這裡排名前10，並沒有設置成一個條件，而是使用了large函數來進行取值。

多條件的設置和轉換，在表達式中的表現就是一個判斷條件乘以另一列數據，這是一個比較固定的表達方式。

當我們添加一個條件，B班男生排名前10的平均分。

公式為：

{=AVERAGE(LARGE((A2:A184=A2)*(B2:B184="男")*C2:C184,ROW($1:$10)))}

條件多了一個「=男生」，我們直接在公式中再乘以一個條件「B2:B184="男"」，如果還需要添加一個條件，則可以再乘以一組條件，不能忘記的是記得最後乘以返回的數值區域，使條件得到轉換而獲得一個可運算的數組。