☰

如何找到最接近平均值的數字

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
如何找到最接近平均值的數字

我有一個這樣的樣本，我想找到最接近平均值的值

城市和重量是兩個獨立的列

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

我做了一個樞軸併計算了 A- 的平均權重，即 46.75

我需要找到 A 最接近的數字，在本例中為 45

我認為我需要使用索引和匹配，但是如果有 17,000 行具有重複的城市名稱和不同的權重值，我該怎麼做？

如有任何幫助，我將不勝感激

所以我正在尋找的答案是

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

大多數類似的答案都沒有使用這個集合，請幫我設定我嘗試過的這個公式：

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

但它著眼於 AC 的整個權重數組。我只希望它在比較 A 的平均值時查看 A 的值，

然後比較 B 的平均值時的 B 權重，

等等....

請告訴我我的公式有什麼問題？

先致謝

答案1

編輯：

抱歉，我沒有很好地閱讀你的問題，現在才意識到你明確表示你想找到最接近Weight平均值的值在城市的價值觀中計算平均值。所以我更新了下面的答案。

看起來你發現了XOR LX 的答案對於類似的問題，你已經非常接近正確了。

MATCH()XOR LX 使用了一個非常簡潔的小公式，可以克服搜尋無序資料時的限制。我將在下面解釋它是如何工作的。

在下面顯示的數據表中，我計算了平均值：

=AVERAGEIF(A$2:A$11,A14,B$2:B$11)（我得到的答案與您上面顯示的答案不同）。

最接近Weight平均值的是：

=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))

請注意，這是一個陣列公式，因此必須使用 來輸入CTRLShiftEnter，而不僅僅是Enter。

在此輸入影像描述 _____________________________________________________________________________

怎麼運作的：

ABS(B$2:B$11-B14)是平均值與清單中所有數字之間的差異的陣列Weight。 And是一個值(A$2:A$11=A14)數組，其中包含equals 。將這兩者相乘得到一個數組，其中包含與 、 和其他位置相對應的位置的差異。True/FalseTrueCityA14City = A140

接下來，我們想要找到這些差異的最小值，但我們必須建立一個稍微不同的數組，因為如果數組中有 任何差異，MIN()則會傳回。00's

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))檢查 where ，並傳回這些位置與其他位置City = A14之間的差異Weight和平均值。False

取該數組的最小值，MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))給出最小的差異僅適用於那些位置 City = A14。

現在，等式給出了當前 的最小差異位置的值(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))數組。 找到 , 的位置（最接近的數字的位置）並將其饋送到 an以返回實際值。True/FalseTrueCityMATCH()TrueINDEX()

我希望這個幫助能祝你好運。

相關內容