Es ist schwierig, bei sehr langen Formeln einen Fehler zu finden. Ich habe hier mal einen längeren Bandwurm produziert, an dem ich zeigen werde, wie man Schritt für Schritt eine Formel ausrechnet und das Ergebnis anzeigen lässt.
Es geht um die Zuschläge für die Endreinigung einer Ferienwohnung. Verwendet werden zwei SVERWEISE und mehrere WENN. Die Formel soll errechnen, welche Zuschläge fällig werden: sind mehr Personen in der Ferienwohnung als vorgesehen, gibt es genauso einen Zuschlag wie für das Mitbringen eines Hundes. Gab es eine Zwischenreinigung, wird dagegen abgezogen.
Die vorgegebene Personenzahl pro Wohnung steht in den Zellen O2:P5.
Die Berechnung in Spalte lautet:
=WENN(B2>SVERWEIS(A2;$O$2:$P$5;2;FALSCH);(B2-SVERWEIS(A2;$O$2:$P$5;2;FALSCH))*25;0)+WENN(E2=“ja“;25;0)-WENN(F2=“ja“;25;0)
Zuerst wird geprüft, ob die Zahl in B2 größer ist als die vorgegebene Anzahl der Gäste. Wenn ja wird die überzählige Anzahl mit 25 multipliziert.
Dann wird geprüft, ob in E2 „ja“ steht. Wenn das stimmt, wird 25 addiert.
Dann wird geprüft, ob in F2 „ja“ steht. Wenn das stimmt, werden 25 abgezogen
Das ist korrekt für die Zeilen 2, 4 und 5.
Aber in Zeile 3 wird als Zuschlag 0 Euro errechnet, obwohl es 25 Euro sein müssten:
- Es sind die vorgesehenen 4 Personen in der FeWo: 0 Euro
- Es wird ein Haustier mitgebracht: +25 Euro
- Es gab keine Zwischenreinigung: kein Abzug
Bleibt ein Zuschlag von “25 Euro”. Warum kommt Excel auf 0 Euro?
Die Formel ist für alle Zeilen identisch und rechnet an allen Stellen korrekt. Es muss also an irgendeiner der Eingaben liegen – aber an welcher? Hier hilft das Schritt für Schritt errechnen.
Die falsch berechnete Zelle (G3) wird markiert.
- Register “Formeln” und dort auf “Datenauswertung” klicken
- Die komplette Formel wird angezeigt und die erste Berechnung wird unterstrichen (B3 – ich habe es zusätzlich gelb markiert, das ist nicht im Original so!)
- Klicken Sie auf “Auswerten”, um den Wert für B3 einzusetzen – das ist eine “4”
- Die Unterstreichung wandert auf den nächsten Ausdruck, der berechnet wird – A3
- Wieder “Auswerten” anklicken
So gehen Sie langsam (!) Schritt für Schritt durch die Formel und beobachten jeweils, welche Werte eingetragen werden und was daraus errechnet wird.
Es werden beide SVERWEISe durchgerechnet und mit dem Ergebnis 0 zurückgegeben. Dann folgt die Abfrage, ob in E3 das Wort “ja” steht und das sieht so aus:
Schaut man genau hin, ist es schon etwas irritierende, dass das schließende Anführungszeichen hinter dem ersten “ja” soweit weg steht. “Auswerten” löst jetzt auf, ob in E3 “ja” steht oder nicht:
Excel kommt zu dem Schluss, dass in E3 NICHT “ja” steht – es wird FALSCH zurückgeliefert und somit 0 Euro berechnet. An der Stelle kann ich schon abbrechen mit Klick auf “Schließen”. Ich muss mir also E3 genauer anschauen und sehe, dass dort hinter dem “ja” noch ein Leerschritt steht!
Sichtbar machen kann die Leerschritte übrigens nicht, ich klicke in die Zelle und stelle fest, dass der Cursor mit Abstand vom “a” blinkt.
Nach einer Korrektur wird auch sofort korrekt berechnet:
Um solche ärgerlichen kleinen Fehler zu vermeiden, ist es sinnvoll, sich eine Datenüberprüfung für diese Eingabezellen zu erstellen. Dazu mache ich in der nächsten Zeit einen separaten Blogbeitrag.
Bork.Blog 