Excel: Ruden auf erste Stelle ungleich "0"
Hi Leute,
ich weiß leider nicht weiter. Meine Recherche hat leider nichts gebracht und so bin ich hier gelandet. Es geht um folgendes:
Ich hab ne große Anzahl von Werten und möchte diese runden, und zwar auf die erste Stelle in der Zahl nach dem Komma, die ungleich "0" ist.
Mit "RUNDEN(ZAHL;Nachkommastellen)" kann ich ja lediglich eine bestimmte Anzahl von Nachkommastellen festlegen, auf die gerundet werden soll. Ich möchte aber Excel sagen, es soll auf die erste Stelle runden, die ungleich "0" ist, also z. B. soll die Zahl 0,000343 auf 0,0003 und z. B. 0,0000256 auf 0,00003 gerundet werden. Ansonsten würde Excel, wenn ich beim runden z. B. lediglich 4 Nachkommastellen definiere, für 0,0000256 eine 0 ausgeben.
Danke für eure Hilfe!
Liebe Grüße,
Michl
4 Antworten
Ich habe das Ganze mal andersherum als DeeDee(DH!) "aufgezäumt" und komme auf diese "Monster"-Formel, auf die ich wirklich nicht stolz bin, die aber - erstmal nur für Zahlen <0 - funktionieren sollte, allerdings auch nur bis 10^(-15), weil Excel bei mehr als 14 Nullen nach dem Komma automatisch zur wissenschaftlichen Schreibweise wechselt:
=RUNDEN(A1;(MIN(WENNFEHLER(FINDEN("1";A1);9^9);WENNFEHLER(FINDEN("2";A1);9^9);WENNFEHLER(FINDEN("3";A1);9^9);WENNFEHLER(FINDEN("4";A1);9^9);WENNFEHLER(FINDEN("5";A1);9^9);WENNFEHLER(FINDEN("6";A1);9^9);WENNFEHLER(FINDEN("7";A1);9^9);WENNFEHLER(FINDEN("8";A1);9^9);WENNFEHLER(FINDEN("9";A1);9^9))-2))
Vielleicht kann ja auch jemand das hier als Denkanstoß nutzen, zusätzlich zu DeeDee Lösung die mindestens gleichwertig ist, und was Eleganteres daraus machen.
P.S.: Die Vorgehensweise bei Zahlen >0 hat DeeDee ja schon erklärt.
...fehlt mir der mathematische Hintergrund...
Bei Deinen vielen guten Antworten hätte ich das nie vermutet! Ein extra DH!
Hab zwar auch (leider) keine mathematische Ausbildung, aber Erfahrungen mit dem Logarithmus bekommt man als Chemiker zwangsläufig, weil viele Formeln logarithmisch besser zu fassen sind:
pH-Wert, pKS-Wert, Extinktion log(E/E₀), sowie ausserhalb der Chemie zB Dezibel
Es geht nur eine genaue Definition beim Runden. Wenn du 5 angibst, rundet er die 5te Stelle. Du kannst dann nicht beim nächsten sagen, er soll die 4te nehmen.
kann man doch (s. meine AW)!
Man muss nur immer ein mathematisch-logisches Kriterium finden, nach dem sich
det janze richten soll, und da bietet sich beim Dezimalsystem der dekadische Logarithmus an.
Manchmal hilft allerdings alle Kreativität nichts, wenn der geforderte Sachverhalt zu kompliziert zu definieren ist!
Was funktioniert ist eine (nicht unbedingt, in meinem Beispiel aber doch) verschachtelte Wenn-Formel, etwa
=WENN(A1<0,0000001;RUNDEN(A1;7);WENN(A1<0,00001;RUNDEN(A1;6);WENN(A1<0,0001;RUNDEN(A1;5)usw...)))
Vielleicht findet noch jemand eine schönere Lösung.
Ich habe - ohne hinreichenden Erfolg - noch mit ABS(LOG10;A1) als Anzahl der zu rundenden Stellen experimentiert.
Außerdem habe ich mit =SUMMENPRODUKT((TEIL(A1;SPALTE(1:1);1)="0")*1) (stibitzt von http://excelformeln.de/formeln.html?welcher=96 ) die Anzahl der Nullen ermittelt als n.te Nachkommatstelle. Das funktioniert aber nicht, wenn hinter der ersten geltenden Ziffer weitere Nullen sind, z.B. 0,004302.
Aber vielleicht fühlt sich ja jemand dadurch inspiriert :-)
Achso, wenn es auch Zahlen >1 gibt, z.B. 35,00243, und das auf 35,002 gerundet werden soll, dann verwende in der WENN-Abfrage
=WENN(A1-GANZZAHL(A1)<0,000001;.......
es geht viel kürzer:
=RUNDEN(A1;0-GANZZAHL(LOG(ABS(A1))))
zB.
0,000345 wird zu 0,0003
0,0000018 zu 0,000002
Mit der 0 steuerst Du die Zahl der angezeigten Zählstellen (0=>eine, 1=>2, 2=>3 usw),
Das geht auch bei negativen Zahlen und bei großen Zahlen:
22,7 zB wird zu 20,
-316,0271 zu -300
Für so eine Lösung fehlt mir der mathematische Hintergrund.
Ich hatte das zwar auch in der Schule, aber das ist schon viel zu lange her.
Hab diese Lösung mal für die Frage einer Arzthelferin ausgearbeitet,, die die Laborwerte auf drei Stellen genau festhalten wollte, das aber wieder verloren und für www.gutefrage.net/frage/3fache-wenn-funktion-in-excel
dann nochmals lange rumgetüftelt mit - und ABS(),
(Die AW blieb damals ohne Resonanz, wie so oft...)
Man kann sich die Ergebnis-Zahl auch im wissenschaftlichen Format anzeigen lassen:
-300 = -3E+02
Aber das ginge auch direkt, ohne Rundung (der ungerundete Wert bliebe dabei allerdings erhalten)
So eine relativ kurze und elegante Lösung hatte ich auch angestrebt. Nur war Logarithmus noch nie meine Stärke ;-)