☰

解決方案1

tag-icon • tag-icon microsoft-excel worksheet-function
解決方案1

我有一行值，想要找出前 3 個值的平均值，不計算空白儲存格。例子：

  ABCDEFG
 12 空白 8 空白 7 9 4

前 3 個非空白儲存格的平均值應為 9，但我找不到可以為我計算此值的公式。有人可以幫忙嗎？

答案1

在A2進入：

=IF(A1="",0,1)

並在B2進入：

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

並複製過來。然後在另一個儲存格中輸入：

=SUMPRODUCT((1:1)*(2:2))/3

在此輸入影像描述

答案2

您正在尋找平均的功能。根據公式文法和用法對於 Excel 的 AVERAGE 函數：

如果範圍或儲存格引用參數包含文字、邏輯值，或空白單元格，這些值被忽略；但是，包含值為零的儲存格。

公式=AVERAGE(A1:E1)回傳9：

在此輸入影像描述

答案3

解決方案1

蠻力：您想要的平均值可以透過以下方式計​​算：

=IF(計數($A1:$C1)=3, 平均值($A1:$C1),
 IF(計數($A1:$D1)=3, 平均值($A1:$D1),
 IF(計數($A1:$E1)=3, 平均($A1:$E1),
 IF(計數($A1:$F1)=3, 平均值($A1:$F1),
 IF(COUNT($A1:$G1)=3, 平均值($A1:$G1), "哎呀")))))

(單行版：)
 =IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1) ) , IF(計數($A1:$E1)=3, 平均值($A1:$E1), IF(計數($A1:$F1)=3, 平均值($A1:$F1), IF(COUNT ( $A1:$G1)=3, 平均值($A1:$G1),"哎呀")))))

它會尋找包含三個數字的行的最短初始子集，並對其求平均值。這不僅對於大量列來說變得尷尬，而且實際上可能很快就會失敗，因為（我相信）IF()具有相當低的嵌套限制。

解決方案1.9

技巧。

我稱之為“1.9”，因為它還沒有完成。表達方式

SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})

（用於陣列公式）找出第 1 行前三個數字的列號。

IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")

部分評估為

IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )

這減少到

{ 1, "", 3, "", 5, 6, 7 }

和SMALL(…, {1,2,3})以上產生{ 1, 3, 5 }.這應該是個簡單的說法

=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

或者

=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

但它們要麼傳回 (12) 的值A1，要麼傳回錯誤（#DIV/0!或#REF!）。我不知道如何完成這個。

相關內容