大きなリストでのGEOMEANの使用(Microsoft Excel)
ケンは、GEOMEAN関数で抱えていた問題について書いています。多数の値(3,500行のデータ)で関数を使用しようとすると、#NUMエラー値が返されます。
GEOMEAN関数は、一連の値の幾何平均を返すために使用されます。 n個の数の幾何平均は、数の積のn乗根です。たとえば、一連の値(AからD)に4つの値がある場合、それらの数値の積はA B C * Dであり、GEOMEANはその積の4番目のルートです。
次の3つの条件のいずれかが満たされた場合、#NUMエラーが返されます。
いずれかの値がゼロに等しいか、いずれかの値が負であるか、Excelの制限を超えています。特に彼の3,500の値のいずれかが大きい場合、ケンが遭遇するのはこの最後の状態である可能性があります。
GEOMEANは3,500の数値の積を見つけ(それらすべてを互いに乗算し)、次にn乗根を取るため、積はExcelには大きすぎる可能性があります。 Excelの最大の正の数は9.99999999999999 * 10 ^ 307です(科学的記数法では、これは9.99999999999999E + 307と表記されます)。製品がこの数値より大きくなると、関数に対して#NUMエラーが発生します。
解決策は、ログを使用して計算を行うことです。これは、GEOMEAN関数の変換を見ると最も簡単に理解できます。
GEOMEAN = (X1X2X3...Xn)^ (1/n) ln(GEOMEAN) = ln((X1X2X3...Xn)^ (1/n)) ln(GEOMEAN) = (1/n) ln(X1X2X3...*Xn) ln(GEOMEAN) = (1/n) * (ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)) ln(GEOMEAN) = average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)) GEOMEAN = exp(average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)))
上記を実行すると、GEOMEANは値のログの平均の指数に等しいことがわかります。 GEOMEAN関数の代わりに次の配列数式を使用して、目的の結果を計算できます。
=EXP(AVERAGE(LN(A1:A3500)))
これは、目的の値がA1:A3500の範囲にあることを前提としています。これは配列数式であるため、Ctrl + Shift + Enterを使用してセルに入力する必要があります。
_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。
このヒント(9328)は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice 365のExcelに適用されます。Excelの古いメニューインターフェイス用のこのヒントのバージョンは、次の場所にあります。
link大きなリストでのGEOMEANの使用。