Zwei Listen, die eigentlich zusammenpassen sollten: offene Rechnungen und Zahlungseingänge, Bestellungen und Lieferungen, Teilnehmerliste und Anmeldungen. Die Frage ist immer dieselbe. Was steht in beiden Listen, was fehlt, und wo weichen die Werte ab?
Dieser Beitrag zeigt drei Wege, die sich ergänzen: einen zum Zählen, einen zum Beschriften und einen, der die Abweichungen farbig hervorhebt. Wie die dabei verwendete Verweisfunktion aufgebaut ist, steht im Beitrag zur SVERWEIS-Funktion.
Die Ausgangslage
Links stehen sieben offene Rechnungen, rechts die vier Zahlungseingänge aus dem Kontoauszug. Die Rechnungsnummer verbindet beide Listen.
Bevor Sie loslegen, prüfen Sie eine Kleinigkeit: Beide Listen müssen dieselbe Schreibweise verwenden. RE-4821 und RE 4821 sind für Excel zwei verschiedene Werte, ebenso eine Zahl gegen die gleiche Zahl als Text.
Weg 1: ZÄHLENWENN zählt die Treffer
ZÄHLENWENN durchsucht die zweite Liste und zählt, wie oft der Wert dort vorkommt. Das Ergebnis ist eine Zahl, meist 0 oder 1.
=ZÄHLENWENN($F$4:$F$7;A4)
Eine 0 bedeutet: Diese Rechnungsnummer steht nicht im Kontoauszug. Eine 1 bedeutet: Es gibt genau einen passenden Eintrag. Steht dort 2 oder mehr, kommt die Nummer mehrfach vor, etwa durch eine Teilzahlung, eine Korrektur oder eine Doppelbuchung.
Achten Sie auf die Formulierung: Ein Treffer belegt, dass die Nummer vorkommt, nicht dass der Betrag stimmt. Ob vollständig bezahlt wurde, klärt erst der Betragsvergleich weiter unten.
Dieser Weg hat einen Vorteil gegenüber SVERWEIS: Er meldet keinen Fehler, sondern liefert immer eine Zahl. Und er zeigt Dubletten, die SVERWEIS stillschweigend übergeht. Wie die Funktion im Detail arbeitet, steht unter ZÄHLENWENN.
Weg 2: Aus der Zahl einen Klartext machen
Eine Spalte voller Nullen und Einsen liest sich schlecht. Legen Sie WENN darum herum:
=WENN(ZÄHLENWENN($F$4:$F$7;A4)=0;"kein Eingang";"Eingang vorhanden")
Jetzt steht in jeder Zeile, ob ein Zahlungseintrag vorliegt. Nach dieser Spalte lässt sich sortieren und filtern. Beachten Sie die Formulierung: Ein Eingang heißt noch nicht, dass der Betrag stimmt. Das klärt erst der Vergleich weiter unten. Mehr zur Funktion steht unter WENN-Funktion.
Wenn Sie den Wert selbst brauchen
Soll nicht nur der Status, sondern auch das Zahlungsdatum übernommen werden, ist SVERWEIS das richtige Werkzeug:
=WENNFEHLER(SVERWEIS(A4;$F$4:$G$7;2;FALSCH);"offen")
ZÄHLENWENN beantwortet damit die Frage, ob ein Eintrag vorhanden ist. SVERWEIS beantwortet die Frage, welcher Wert dort steht.
Weg 3: Abweichungen farbig hervorheben
Bei längeren Listen ist das Durchsehen der Statusspalte mühsam. Eine bedingte Formatierung färbt die betroffenen Zeilen ein.
- Markieren Sie den Bereich A4:D10.
- Wählen Sie Start → Bedingte Formatierung → Neue Regel.
- Klicken Sie auf Formel zur Ermittlung der zu formatierenden Zellen verwenden.
- Geben Sie die Formel
=$D4="kein Eingang"ein. - Wählen Sie über Formatieren eine Füllfarbe und bestätigen Sie mit OK.
Excel färbt jetzt die gesamte Zeile, sobald in Spalte D kein Eingang steht. Entscheidend ist das Dollarzeichen vor dem D: Es sorgt dafür, dass für jede Zelle der Zeile dieselbe Spalte geprüft wird. Die Zeilennummer bleibt ohne Dollarzeichen, damit die Regel Zeile für Zeile weiterwandert.
Mehr Möglichkeiten stehen unter bedingte Formatierung.
In beide Richtungen prüfen
Ein Abgleich in eine Richtung beantwortet nur die halbe Frage. Er zeigt, welche Rechnungen unbezahlt sind. Er zeigt nicht, ob es Zahlungen gibt, zu denen keine Rechnung existiert.
Setzen Sie dafür dieselbe Formel spiegelbildlich neben die zweite Liste:
=WENN(ZÄHLENWENN($A$4:$A$10;F4)=0;"keine Rechnung";"zugeordnet")Erst beide Richtungen zusammen ergeben ein vollständiges Bild. Das gilt für jeden Abgleich, ob Lager gegen Inventur oder Teilnehmerliste gegen Anmeldungen.
Zahlen vergleichen statt nur vorhanden sein
Manchmal steht ein Wert in beiden Listen, aber mit unterschiedlichem Betrag. Dann brauchen Sie keinen Abgleich, sondern eine Differenz. Voraussetzung ist, dass die zweite Liste ebenfalls eine Betragsspalte enthält, hier angenommen in Spalte H:
=C4-SUMMEWENN($F$4:$F$7;A4;$H$4:$H$7)SUMMEWENN statt SVERWEIS ist hier wichtig: Gibt es zu einer Rechnung mehrere Eingänge, etwa zwei Teilzahlungen, addiert SUMMEWENN sie. SVERWEIS nähme nur den ersten und würde eine Teilzahlung als vollständig ausweisen.
Steht dort 0, stimmen beide Beträge überein. Jede andere Zahl ist eine Abweichung, die Sie klären sollten. Fehlt der Eingang ganz, erscheint der volle Rechnungsbetrag, denn SUMMEWENN liefert dann 0.
Bei Beträgen mit Nachkommastellen erzeugen Rundungsdifferenzen winzige Abweichungen wie 0,0000001. Prüfen Sie in diesem Fall auf gerundete Gleichheit:
=RUNDEN(C4-SUMMEWENN($F$4:$F$7;A4;$H$4:$H$7);2)=0Typische Stolpersteine
- Zahl gegen Text. Sieht gleich aus, ist es für Excel nicht. Prüfen Sie mit
=ISTZAHL(A4)und=ISTZAHL(F4). Steht einmal WAHR und einmal FALSCH, liegt hier die Ursache. - Leerzeichen am Ende. Prüfen Sie mit
=LÄNGE(A4), ob beide Werte gleich lang sind. - Platzhalterzeichen. ZÄHLENWENN behandelt
*und?als Platzhalter. Kommen sie in Ihren Daten vor, zählt die Funktion zu viel. Setzen Sie dann eine Tilde davor, also~*oder~?, damit das Zeichen als normales Zeichen gilt. - Groß- und Kleinschreibung spielt weder bei ZÄHLENWENN noch bei SVERWEIS eine Rolle.
Übungsdatei zum Herunterladen
Das Blatt Zahlungen in der Übungsmappe enthält beide Listen mit einer leeren Spalte für den Abgleich.
Was in der Übungsdatei steckt
- Bestellungen und Preisliste für die Grundlagen
- Zahlungen für den Abgleich zweier Listen
- Kundenpreise für die Suche mit zwei Kriterien
- Rabatte für die Umsatzstaffel
- Fehlersuche mit dem Klassiker Zahl gegen Text
Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.
Weitere Anleitungen zu Verweisfunktionen
- SVERWEIS Schritt für Schritt: Werte aus der zweiten Liste übernehmen statt nur prüfen.
- WENNFEHLER: die Fehlermeldung durch einen lesbaren Hinweis ersetzen.
- SVERWEIS funktioniert nicht: wenn der Abgleich Treffer übersieht, die es geben müsste.
- Alle Treffer ausgeben: wenn eine Rechnung mehrere Zahlungseingänge hat.