Excel, Runden auf bestimmte Nachkommastellen?
Guten Tag alle zusammen,
ich habe folgendes Problem.
Ich habe in Excel diverse Werte, wie z.B. 9,78; 2,32; 4,54 und würde gerne eine Formel benutzen die diese Werte auf den jeweils nächsten Wert von -,99 ; -,75 und -,49 rundet.
Habt ihr da irgendeine Idee oder sogar Lösung? Wäre sehr dankbar & danke im Voraus!
7 Antworten
Ohne Makro geht's nicht ohne Hilfsspalten.
Anmerkung: um zu testen, ob die Formel nicht mit Zeilen und Spalten durcheinander kommt, habe ich einen 4. Centbetrag eingeführt. In der Formel müsste ggf. überall "$A$1:$A$4" durch "$A$1:$A$3" ersetzt werden. (Oder man arbeitet gleich mit einem benannten Bereich, womöglich in einer eigenen Tabelle.)
In A1 bis A4 habe ich folgende Werte eingetragen: 0,99; 0,75; 0,49; 0,29
Ich habe in Spalte C die Eingangswerte eingetragen, in Spalte D die Ausgangswerte und Spalten E bis G sind Hilfsspalten.
Hier gebe ich die Formeln für Zeile 1 an; für die folgenden Zeilen die Formeln kopieren oder "herunterziehen".
Formel für Spalten E bis G (Matrixformel -- Eingabe über Strg+Umschalt+Enter):
=MIN(INDEX(ABS(REST($C1;1)+{-1.0.1}-$A$1:$A$4);;{1.2.3}))
Formel für Spalte D (Matrixformel möglich, aber nicht erforderlich):
=WENN($C1="";"";WENN($C1<MIN($A$1:$A$4);MIN($A$1:$A$4);$C1-INDEX(REST($C1;1)+{-1.0.1}-$A$1:$A$4;VERGLEICH(MIN(ABS(REST($C1;1)+{-1.0.1}-$A$1:$A$4));INDEX(ABS(REST($C1;1)+{-1.0.1}-$A$1:$A$4);0;VERGLEICH(MIN(ABS(REST($C1;1)+{-1.0.1}-$A$1:$A$4));$E1:$G1;0));0);VERGLEICH(MIN(ABS(REST($C1;1)+{-1.0.1}-$A$1:$A$4));$E1:$G1;0))))
(die erste Wenn-Abfrage dient dazu, die Ausgabe hübscher zu machen, wenn nichts in Spalte C drinsteht, die zweite dazu, zu kleine Beträge auf den kleinsten Zielbetrag aufzurunden.)
Ich habe jetzt leider keine Zeit mehr, das weiter zu testen und ggf. zu optimieren, aber schau mal, ob DAS hier in etwa klappt:
=INDEX({0,99;0,75;0,49};VERGLEICH(A1-GANZZAHL(A1);{0,99;0,75;0,49};-1))+GANZZAHL(A1)
Falls nicht, beschreibe, was falsch ist. Ich schaue heute Nacht wieder rein.
Kann sein, dass es auch viel einfacher geht. Vlt. hat einer der Anderen Experten hier eine Idee?!
Sind, glaube ich, noch Fehler drin. Teste mal genau. Ich schau morgen nochmal nach.
SO, jetzt habe ich den Fehler - glaube ich - raus. Es gibt da in Excel eine Ungenauigkeit mit den Nachkommastellen, wenn man von der Zahl die Ganzzahl abzieht. Da tauchen an 13. bis 15. Stelle noch Ziffern auf, die da nicht hingehören.
Teste alo nochmal AUSFÜHRLICH diese verbesserte Formel:
=INDEX({0,99;0,75;0,49};VERGLEICH(RUNDEN(A1-GANZZAHL(A1);2);{0,99;0,75;0,49};-1))+GANZZAHL(A1)
Aber anscheinend interessierst Du Dich sowieso nicht mehr für Deine Frage.
Oder?
Liefert allerdings für 2,05 nicht 1,99 sondern 2,49
(Ich hab den Fragesteller so verstanden, dass immer auf den nächstliegenden "passenden" Wert auf- bzw. abgerundet werden soll.)
Wie ich schon bei @Dietwals angemerkt habe:
auf den jeweils nächsten Wert von -,99 ; -,75 und -,49 rundet.
DAS verstehe ich so, dass immer aufgerundet werden soll. Und dass das nicht gleichmäßig verteilt ist, z.B. die 0,24 oder 0,25 fehlt, ist für mich die Herausforderung.
Ist aber wohl wirklich Auslegungssache. Als Sinn sehe ich dahinter Verkaufspreise festzulegen, die "schön aussehen" und da würde ich dann eher aufrunden, um die Marge nicht geringer sondern größer zu machen.
Abrunden würde ich so:
=GANZZAHL(A1)+WENNNV(VERWEIS(REST(A1;1);{0,49;0,75;0,99});-0,01)
Aufrunden so:
=GANZZAHL(A1)+WAHL(VERGLEICH(REST(A1;1);{0,99;0,75;0,49};-1);0,99;0,75;0,49)
Das Problem beim Aufrunden ist, dass die Verweisfunktionen dafür den Parameter nicht vorgesehen haben. Deswegen sehe ich auf Anhieb nur die Möglichkeit, mit VERGLEICH auf die Suche zu gehen und dann den Fund rück zu transferieren.
sehr interessanter Ansatz!
Auch wenn Verweis keinen FALSCH-Parameter hat, könnte man aufrunden (zB 0,5), indem man alles negativ setzt:
=-VERWEIS(-0,5;{-0,99;-0,74;-0,49;-0,01})
Man muss dafür allerdings die Liste dann andersrum aufbauen. SVerweis hätte einen FALSCH-Parameter:
=-SVERWEIS(-0,5;{-0,99;-0,74;-0,49;-0,01};1;1)
das ändert aber nichts an der Sache, eher dann schon
=INDEX({0,24;0,49;0,74;0,99};VERGLEICH(0,5;{0,24;0,49;0,74;0,99};1)+1) oder
=INDEX({0,24;0,49;0,74;0,99};VERGLEICH(0,5;{0,24;0,49;0,74;0,99};1)+1)
nur so als Idee, müsste man alles erst noch ausfeilen, wie du es mit Ganzzahl und Rest vorgeführt hast.
Ich halte aber die 0,75 für einen Flüchtigkeitsfehler und das ganze dann für viel einfacher lösbar!
Das mit dem Negieren halte ich wiederum für eine brilliante Idee. Die besten Ideen sind immer so einfach, dass man sich fragt, weshalb man nicht selbst darauf gekommen ist.
Ich halte die Werte 0,49, 0,75 und 0,99 für sinnvoll, bzw. nicht unrealistisch. Offensichtlich sollen Preise kalkuliert werden und 0,74 ist wahrscheinlich für das Auge nicht so ästhetisch wie 0,75.
Etwas komischer Wunsch. Auf was soll z. B. -,87 gerundet werden? Gleich weit von -,75 und -,99 entfernt. Oder -,62? Oder willst Du vielleicht immer abrunden?
Auf -,99 rundet man, indem man erst 0,01 hinzuzählt, dann auf -,00 rundet und schließlich wieder 0,01 abzieht. Bei -,49 klappte es vielleicht mit +0,51, runden auf -,00 und -0,51. Aber 0,75?? Das fällt aus jedem Schema.
auf den jeweils nächsten Wert von -,99 ; -,75 und -,49 rundet.
DAS verstehe ich so, dass immer aufgerundet werden soll. Und dass das nicht gleichmäßig verteilt ist, z.B. die 0,24 oder 0,25 fehlt, ist für mich die Herausforderung.
Dazu brauchst Du nicht mal eine Formel.
Auf den entsprechenden Feldern: Rechte Maustaste -> Zellen formatieren -> Zahl -> Dezimalstellen: 0 einstellen.
Ansonsten hilft die Funktion RUNDEN (Funktion)
Näheres findest Du in der Hilfe, die du über F1 Taste erreichst. Suche einfach mal nach runden.
Das ist vollig falsch (die Formatänderung, ändert nur das Format nicht die Zahl in der Zelle).
Und zudem noch falsch, weil man der Funktion Runden nicht die hier gewünschten Rundungschritte beibringen kann.
Nimm den Klartext von Jackie nicht übel: Es gibt hier oft Angeber, die großspurig irgendwas behaupten, ohne im geringsten durchzublicken. Für so jemand hat Jaclie dich wohl gehalten und da wäre ihr Klartext auch richtig gewesen.
Und dann gibt es Leute, die einfach mal was missverstehen oder fehlinterpretieren, was jedem von uns mal oder auch öfter passiert. Scheint hier der Fall zu sein.
Hab was ohne Hilfsspalten gefunden -- muss allerdings als Matrixformel eingegeben werden, damit es zuverlässig funktioniert:
=WENN($C1="";"";WENN($C1<MIN($A$1:$A$4);MIN($A$1:$A$4);$C1-INDEX(REST($C1;1)-$A$1:$A$4-{-1.0.1};MAX(VERGLEICH($A$1:$A$4;$A$1:$A$4;0)*(ABS(REST($C1;1)-$A$1:$A$4-{-1.0.1})=MIN(ABS(REST($C1;1)-$A$1:$A$4-{-1.0.1}))));MAX({1.2.3}*(ABS(REST($C1;1)-$A$1:$A$4-{-1.0.1})=MIN(ABS(REST($C1;1)-$A$1:$A$4-{-1.0.1})))))))
(Wie oben steht in C1 der Ausgangswert und in $A$1:$A$4 die "erlaubten" Nachkommastellen. Die Liste $A$1:$A$4 ist ggf. anzupassen (verkürzen/verlängern) oder durch einen benannten Bereich zu ersetzen.)
-----
Zu blöd, dass man Funktionen, die man auf diese Weise durch eingebaute Funktionen darstellen kann, nicht durch Makro-Formeln übersichtlicher darstellen kann, ohne gleich Makros erlauben zu müssen.
D. h. ich wünsche mir, dass man z. B. einstellen kann, dass man nur Makro-Funktionen verwenden kann, die keine Schleifen und keinen Rückgriff auf Methoden, die keine Worksheet-Funktionen sind, enthalten, diese dann aber auch ohne Makros zu erlauben verwenden kann.
Also eine eigene Art von "garantiert ungefährlichen" Makros, die lediglich solche komplizierten Ausdrücke übersichtlicher machen, ohne dabei die Funktionalität zu erweitern.