Excel–Formel Schritt für Schritt auswerten

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.

24.02.2022-001

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” klicken14.02.2022-009
  • 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 – A314.02.2022-010
  • 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:

14.02.2022-011

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:

14.02.2022-012

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:

14.02.2022-013

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.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s