Verwenden von GEOMEAN mit einer großen Liste (Microsoft Excel)

Posted on: 2020年11月12日 2020年11月12日

Ken schrieb über ein Problem mit der GEOMEAN-Funktion. Wenn er versucht, die Funktion für eine große Anzahl von Werten (3.500 Datenzeilen) zu verwenden, wird ein # NUM-Fehlerwert zurückgegeben.

Mit der GEOMEAN-Funktion wird das geometrische Mittel einer Reihe von Werten zurückgegeben. Der GEOMEAN von n Zahlen ist die n-te Wurzel des Produkts der Zahlen. Wenn eine Reihe beispielsweise vier Werte enthält (A bis D), ist das Produkt dieser Zahlen A B C * D, und GEOMEAN ist die vierte Wurzel dieses Produkts.

Der Fehler #NUM wird zurückgegeben, wenn eine der drei Bedingungen erfüllt ist: Einer der Werte war gleich Null, einer der Werte war negativ oder die Grenzen von Excel wurden überschritten. Es ist wahrscheinlich, dass es diese letzte Bedingung ist, auf die Ken stößt, insbesondere wenn einer seiner 3.500 Werte groß ist.

Da GEOMEAN das Produkt der 3.500 Zahlen findet (sie alle miteinander multipliziert) und dann die n-te Wurzel zieht, ist das Produkt möglicherweise leicht zu groß für Excel. Die größte positive Zahl in Excel ist 9.9999999999999999 * 10 ^ 307 (in wissenschaftlicher Notation wird dies als 9.99999999999999E + 307 geschrieben). Wenn das Produkt größer als diese Zahl wird, wird ein # NUM-Fehler für die Funktion angezeigt.

Die Lösung besteht darin, Protokolle für die Berechnung zu verwenden. Dies ist am einfachsten zu verstehen, wenn Sie sich eine Transformation der GEOMEAN-Funktion ansehen:

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)))

Wenn Sie die obigen Schritte ausführen, sehen Sie, dass GEOMEAN dem Exponenten des Durchschnitts der Protokolle der Werte entspricht. Sie können das gewünschte Ergebnis berechnen, indem Sie anstelle der GEOMEAN-Funktion die folgende Array-Formel verwenden:

=EXP(AVERAGE(LN(A1:A3500)))

Dies setzt voraus, dass die gewünschten Werte im Bereich A1: A3500 liegen. Da es sich um eine Array-Formel handelt, müssen Sie sie mit Strg + Umschalt + Eingabetaste in eine Zelle eingeben.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (2580) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Verwenden von GEOMEAN mit einer großen Liste.