Generische Formel
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Zusammenfassung
Wenn Sie eine Zahl auf eine bestimmte (variable) Anzahl angegebener Ziffern oder Zahlen runden müssen, können Sie dies mit einer eleganten Formel tun, die die Funktionen ROUND und LOG10 verwendet.
In dem gezeigten Beispiel lautet die Formel in D6 wie folgt:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Erläuterung
Dies kann eine einschüchternde Formel sein, wenn Sie keinen guten mathematischen Hintergrund haben, aber wir werden sie Schritt für Schritt durcharbeiten.
Erstens, wenn Sie eine Formel wie diese haben, in der eine Funktion (in diesem Fall RUND) alle anderen umschließt, ist es oft hilfreich, von außen nach innen zu arbeiten. Im Kern rundet diese Formel also den Wert in B6 mit der RUNDE Funktion:
=ROUND(B6,x)
Wobei x die Anzahl der erforderlichen signifikanten Stellen ist. Der schwierige Teil dieser Formel ist die Berechnung von x. Dies ist eine Variable, da sie sich abhängig von der gerundeten Zahl ändert. x wird mit diesem Bit berechnet:
C6-(1+INT(LOG10(ABS(B6))))
Dies scheint kompliziert zu sein. Schauen wir uns zunächst an, wie die Formel für die angegebenen Beispiele funktionieren muss. Denken Sie mit RUND daran, dass eine negative Anzahl von Ziffern auf der linken Seite der Dezimalstelle funktioniert. Um 1234567 auf eine zunehmende Anzahl von signifikanten Stellen zu runden, hätten wir:
=ROUND(1234567,-6) = 1000000 // 1 sig. digit =ROUND(1234567,-5) = 1200000 // 2 sig. digits =ROUND(1234567,-4) = 1230000 // 3 sig. digits =ROUND(1234567,-3) = 1235000 // 4 sig. digits
Das Hauptproblem ist also, wie man -6, -5, -4 usw. berechnet, abhängig von der Zahl, die wir runden.
Der Schlüssel ist zu verstehen, wie diese Zahlen mit Exponenten ausgedrückt werden können, wie in der wissenschaftlichen Notation:
=ROUND(1234567,-6) = 1000000 = 1.0*10^6 =ROUND(1234567,-5) = 1200000 = 1.2*10^6 =ROUND(1234567,-4) = 1230000 = 1.23*10^6 =ROUND(1234567,-3) = 1235000 = 1.235*10^6
Beachten Sie, dass der Exponent in allen Fällen 6 ist, was mit diesem Bit bestimmt wird:
INT(LOG10(ABS(B6)))
Der Rest der Formel verwendet also nur den berechneten Exponentenwert, um die richtige Zahl für RUND zu ermitteln, abhängig von der Anzahl der gewünschten signifikanten Stellen:
=ROUND(1234567,-6) // 1-(1+6) = -6 =ROUND(1234567,-5) // 2-(1+6) = -5 =ROUND(1234567,-4) // 3-(1+6) = -4 =ROUND(1234567,-3) // 4-(1+6) = -3
Also zusammenfassend:
- ABS wandelt den Wert in einen absoluten (positiven) Wert um
- LOG10 erhält den Exponenten, in diesem Fall 6 mit einem Dezimalwert
- INT schneidet den Dezimalteil des Exponenten ab
- Die Formel verwendet den Exponenten und die angegebenen signifikanten Stellen, um die richtige Anzahl von Stellen für ROUND zu ermitteln
- ROUND rundet die Zahl mit der Anzahl der angegebenen Ziffern
