Zahlen mit Excel runden

Computer: Software & Programmierung Office Software
#1

Hallo Allerseids,

ich weiss, der Titel hoert sich einfach an, aber irgendwie treibt mich das zur verzweiflung. Ich suche nach einer Moeglichkeit Werte in einer Exceltabelle automatisch zu runden und zwar nach folgendem Schema.
Wenn der Wert zm Bsp. 0.95 ist soll er 0.95 bleiben, wenn er 0.2 ist soll er 0 werden, wenn er 0.7 ist soll er 0.5 werden, wenn er 0.8ist soll er 1 werden…also immer entweder zur…0 oder zur .5, jenachdem was naeher ist…fragt nicht warum, ich hab mir den mist nicht ausgedacht : )…

Gruss danny

#2

Hallo, Danny!

Wenn der Wert zm Bsp. 0.95 ist soll er 0.95 bleiben, wenn er

Wie passt das zum nächsten? Ich hätte das jetzt auf 1 gerundet…

0.2 ist soll er 0 werden, wenn er 0.7 ist soll er 0.5 werden,
wenn er 0.8ist soll er 1 werden…also immer entweder zur…0
oder zur .5, jenachdem was naeher ist…fragt nicht warum,
ich hab mir den mist nicht ausgedacht : )…

Der Trick ist, zuerst mit 2 zu multiplizieren, das zu runden und dann wieder durch 2 zu teilen. Also z. B.

=RUNDEN(2*A1;0)/2

Aber pass auf, wie RUNDEN rundet: Schau mal unter /t/runden-von-5-werten-klappt-nicht/3077731

Wenn Du z. B. mit obiger Formel alle Werte von 0,1 an in 0,05er Schritten rundest, so wird Dir aus obigem Grunde die 3,25 auch auf 3 gerundet, die 2,25 aber auf 2,5.

Gruß, Manfred

#3

Hallo Manfred,

bei der Tabelle handelt es sich um einen Pivottable, d.h. das Formeln nicht so wirklich gehen, zumindest nicht, wenn ich das bestehende Aussehen beibehalten moechte.
Am besten waere es also wenn man einfach ein costom cell format auswaehlen koennte (oder selbst schreiben.) achso ich hab hier ne englische office version, falls das unterschiede machen solte.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#4

Hallo Danny!

Mit folgender Formel kriegst du das hin: Wenn dein zu rundender Wert in Zelle B3 steht, dann schreib in die Zielzelle die Formel: =TRUNC(B3*2+0.5)/2

Die Funktion TRUNC wandelt die Zahl in Integer um. Sie rundet nicht, sondern schneidet die Kommastellen ab. Deshalb werden die Werte erst mit 2 multipliziert und 0.5 addiert. Die Addition braucht man deshalb, damit aus 2.6 nicht 2 wird. Addiert man vorher 0.5, dann werden alle Dezimalbrüche mit >= 0.5 hinter dem Komma zur nächsthöheren Integerzahl gewandelt.
Das Ergebnis der TRUNC-Funktion wird dann wieder durch 2 dividiert. Damit hast du ein gerundetes Ergebnis in 0.5er-Schritten.

Übrigens, ich arbeite auch mit einer US-EXCEL-Version. Bei dir müsste die Formel also auch funktionieren.

Liebe Grüße,

Thomas.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#5

Mit folgender Formel kriegst du das hin: Wenn dein zu
rundender Wert in Zelle B3 steht, dann schreib in die
Zielzelle die Formel: =TRUNC(B3*2+0.5)/2

Hallo Thomas,

du hast 0,95 übersehen. Also dann so:

=WENN(GANZZAHL(A1*10)=A1*10;KÜRZEN(A1*2+0,5)/2;A1)

bzw.

=IF(INT(A1*10)=A1*10;TRUNC(A1*2+0.5)/2;A1)

allerdings Danny will das ja nicht wegen seiner Pivottabelle und mit Pivots kenne ich mich nicht aus.

Gruß
Reinhard

#6

Hallo

Das funktioniert nicht wirklich
aus 3.04 wird 3 (soll aber 3.05) aus 20.14 wird 20 (soll aber 20.15) und aus 23.18 wird 23 (soll aber 23.20)

ist sicher nicht einfach das zu automatisieren, aber die Verwaltung hier will solch merkwuerdige Rundungen.

Hallo Allerseids,

ich weiss, der Titel hoert sich einfach an, aber irgendwie
treibt mich das zur verzweiflung. Ich suche nach einer
Moeglichkeit Werte in einer Exceltabelle automatisch zu runden
und zwar nach folgendem Schema.
Wenn der Wert zm Bsp. 0.95 ist soll er 0.95 bleiben, wenn er
0.2 ist soll er 0 werden, wenn er 0.7 ist soll er 0.5 werden,
wenn er 0.8ist soll er 1 werden…also immer entweder zur…0
oder zur .5, jenachdem was naeher ist…fragt nicht warum,
ich hab mir den mist nicht ausgedacht : )…

Gruss danny

Hallo Danny!

Mit folgender Formel kriegst du das hin: Wenn dein zu
rundender Wert in Zelle B3 steht, dann schreib in die
Zielzelle die Formel: =TRUNC(B3*2+0.5)/2

Die Funktion TRUNC wandelt die Zahl in Integer um. Sie rundet
nicht, sondern schneidet die Kommastellen ab. Deshalb werden
die Werte erst mit 2 multipliziert und 0.5 addiert. Die
Addition braucht man deshalb, damit aus 2.6 nicht 2 wird.
Addiert man vorher 0.5, dann werden alle Dezimalbrüche mit
>= 0.5 hinter dem Komma zur nächsthöheren Integerzahl
gewandelt.
Das Ergebnis der TRUNC-Funktion wird dann wieder durch 2
dividiert. Damit hast du ein gerundetes Ergebnis in
0.5er-Schritten.

Übrigens, ich arbeite auch mit einer US-EXCEL-Version. Bei dir
müsste die Formel also auch funktionieren.

Liebe Grüße,

Thomas.

#7

Hallo, Danny!

Das funktioniert nicht wirklich
aus 3.04 wird 3 (soll aber 3.05) aus 20.14 wird 20 (soll aber
20.15) und aus 23.18 wird 23 (soll aber 23.20)

Auf was willst Du nun runden? Auf 0,5 oder auf 0,05? Das war ja gerade meine erste Frage in meiner ersten Antwort. Wenn Du auf 0,05 runden willst, spielst Du eben mit der 20 statt der 2. Wenn’s mal so, mal so laufen soll, muss man das schon genauer definieren: Wann runde ich auf 0,05 und wann auf 0,5.

Was das Thema Pivot betrifft: Ich wüsste nicht, wie man ein entsprechendes „Anzeigeformat“ definieren kann. Als Abhilfe machst Du ein neues Sheet, in dem Du auf das Pivot-Sheet verweist und die Werte wie beschrieben berechnest. Da ggf. die Zeilenanzahl einer Pivottabelle variieren kann, greifst Du in einer in jedem Fall ausreichenden Zeilenanzahl auf die Pivottabelle zu und zeigst nur Daten an, WENN Daten im Pivot-Blatt vorhanden sind, z. B. so:

=WENN(‚Pivot-Blatt‘!A3"";‚Pivot-Blatt‘!A3;"")

Für die Werte analog, dann eben mit der besagten Berechnung auf Deine Nachkommastellen.

Gruß, Manfred

#8

Lösung
zumindest für deine bisherigen Beispielzahlen :smile:

Hi Danny,

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │
──┼───────┼────────┼─────────┤
1 │ Zahl │ Formel │ Vorgabe │
──┼───────┼────────┼─────────┤
2 │ 0,95 │ 0,95 │ 0,95 │
──┼───────┼────────┼─────────┤
3 │ 0,2 │ 0 │ 0 │
──┼───────┼────────┼─────────┤
4 │ 0,7 │ 0,5 │ 0,5 │
──┼───────┼────────┼─────────┤
5 │ 0,8 │ 1 │ 1 │
──┼───────┼────────┼─────────┤
6 │ 3,04 │ 3,05 │ 3,05 │
──┼───────┼────────┼─────────┤
7 │ 20,14 │ 20,15 │ 20,15 │
──┼───────┼────────┼─────────┤
8 │ 23,18 │ 23,2 │ 23,2 │
──┴───────┴────────┴─────────┘
Benutzte Formeln:
B2: =WENN(RECHTS(A2\*100;1)"0";KÜRZEN(A2\*20+0,5)/20;WENN(GANZZAHL(A2\*10)=A2\*10;KÜRZEN(A2\*2+0,5)/2;A2))
B3: =WENN(RECHTS(A3\*100;1)"0";KÜRZEN(A3\*20+0,5)/20;WENN(GANZZAHL(A3\*10)=A3\*10;KÜRZEN(A3\*2+0,5)/2;A3))
B4: =WENN(RECHTS(A4\*100;1)"0";KÜRZEN(A4\*20+0,5)/20;WENN(GANZZAHL(A4\*10)=A4\*10;KÜRZEN(A4\*2+0,5)/2;A4))
B5: =WENN(RECHTS(A5\*100;1)"0";KÜRZEN(A5\*20+0,5)/20;WENN(GANZZAHL(A5\*10)=A5\*10;KÜRZEN(A5\*2+0,5)/2;A5))
B6: =WENN(RECHTS(A6\*100;1)"0";KÜRZEN(A6\*20+0,5)/20;WENN(GANZZAHL(A6\*10)=A6\*10;KÜRZEN(A6\*2+0,5)/2;A6))
B7: =WENN(RECHTS(A7\*100;1)"0";KÜRZEN(A7\*20+0,5)/20;WENN(GANZZAHL(A7\*10)=A7\*10;KÜRZEN(A7\*2+0,5)/2;A7))
B8: =WENN(RECHTS(A8\*100;1)"0";KÜRZEN(A8\*20+0,5)/20;WENN(GANZZAHL(A8\*10)=A8\*10;KÜRZEN(A8\*2+0,5)/2;A8))

A1:C8
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#9

=WENN(RECHTS(B2*100;1)„0“;VRUNDEN(B2*100;5)/100;VRUNDEN(B2*10;5)/10)

Ja - Reinhard hat recht.
Man muss einfach die zwei in ihrer Logik nicht zusammenpassenden Bedingungen abfragen und dann die entsprechende Rundungsformel wählen.

Trotzdem ist die Aufgabestellung unlogisch, weil Zahlen unterschiedlich gewertet werden, was rechnerisch gesehen Quatsch ist.

Gruß Hermes