Für Controlling-Auswertungen sind in Excel vor allem Funktionen für bedingte Summen, Verknüpfungen, Fehlerprüfung und Zeiträume wichtig. Mit SUMMEWENNS, ZÄHLENWENNS, XVERWEIS, WENNFEHLER und PivotTables lassen sich Rohdaten in nachvollziehbare Kennzahlen umwandeln. Beginne jedoch nicht mit Formeln, sondern mit einer sauberen Datenstruktur: Jede Zeile sollte einen Vorgang enthalten, während die Spalten eindeutige Angaben wie Datum, Kostenstelle, Sachkonto, Betrag und Status aufnehmen.
Prüfe zuerst, ob Datumswerte tatsächlich als Datum erkannt werden, Beträge numerisch vorliegen und Bezeichnungen einheitlich geschrieben sind. Sonst liefern selbst korrekte Formeln scheinbar falsche Ergebnisse. Arbeite bei wichtigen Berichten außerdem mit einer Kopie der Quelldatei oder einer gespeicherten Version, bevor du Spalten umbenennst oder Daten bereinigst.
Die Datengrundlage für Controlling-Berichte vorbereiten
Markiere den Datenbereich und wandle ihn mit Strg+T in eine formatierte Tabelle um. Vergib im Register „Tabellenentwurf“ einen verständlichen Namen, zum Beispiel Umsaetze. Tabellen wachsen automatisch mit, und Formeln können lesbarer mit strukturierten Verweisen arbeiten. Eine Spalte „Betrag“ sollte nur Zahlen enthalten, nicht zusätzlich das Währungssymbol als Text.
Vermeide verbundene Zellen, Leerzeilen innerhalb der Liste und wechselnde Schreibweisen wie „Nord“, „Region Nord“ und „Norden“. Für Kategorien, Kostenstellen oder Produkte lohnt sich eine separate Stammdatenliste. Dort kannst du gültige Bezeichnungen pflegen und sie später per XVERWEIS in die Auswertung übernehmen.
Summen nach Kriterien mit SUMMEWENNS bilden
Die Funktion SUMMEWENNS addiert Werte, die mehrere Bedingungen erfüllen. Das ist nützlich, wenn du beispielsweise die Kosten einer bestimmten Kostenstelle in einem bestimmten Monat auswerten möchtest.
Eine einfache Struktur sieht so aus:
=SUMMEWENNS(Umsaetze[Betrag];Umsaetze[Kostenstelle];A2;Umsaetze[Status];“Offen“)
In diesem Beispiel werden nur Beträge summiert, deren Kostenstelle dem Inhalt von A2 entspricht und deren Status „Offen“ lautet. Die Summenspalte steht bei SUMMEWENNS zuerst, danach folgen Kriterienbereiche und Kriterien jeweils als Paar.
Für Zeiträume kannst du mit einem Start- und einem Enddatum arbeiten:
=SUMMEWENNS(Umsaetze[Betrag];Umsaetze[Datum];“>=“&B1;Umsaetze[Datum];“<„&C1)
Die obere Grenze wird hier als „kleiner als“ formuliert. Das verhindert Probleme bei Datumswerten, die zusätzlich eine Uhrzeit enthalten. Soll ein kompletter Monat ausgewertet werden, kann C1 der erste Tag des Folgemonats sein.
Anzahl und Erfüllungsquoten mit ZÄHLENWENNS ermitteln
Mit ZÄHLENWENNS zählst du Datensätze, die mehrere Bedingungen erfüllen. So lässt sich etwa feststellen, wie viele Aufträge noch offen sind oder wie viele Vorgänge außerhalb eines Zielwerts liegen.
=ZÄHLENWENNS(Umsaetze[Status];“Offen“;Umsaetze[Kostenstelle];A2)
Für eine Quote kombinierst du die Funktion mit einer Division. Die Fehlerbehandlung verhindert eine Division durch null:
=WENNFEHLER(ZÄHLENWENNS(Umsaetze[Status];“Erledigt“)/ANZAHL2(Umsaetze[Status]);0)
Formatiere das Ergebnis als Prozent. Achte darauf, dass die verwendete Gesamtzahl dieselbe Grundgesamtheit beschreibt wie der Zähler. Wenn beispielsweise nur Vorgänge eines Monats betrachtet werden, müssen beide Teile der Berechnung auf diesen Zeitraum begrenzt sein.
Planwerte und Stammdaten mit XVERWEIS ergänzen
Ein Controlling-Bericht enthält häufig Istwerte aus einer Buchungsliste und Planwerte aus einer separaten Tabelle. XVERWEIS kann passende Informationen anhand einer gemeinsamen Kennung holen, etwa anhand einer Kostenstelle.
=XVERWEIS(A2;Planwerte[Kostenstelle];Planwerte[Monatsbudget];“Nicht gefunden“)
Der vierte Parameter legt fest, was bei einem fehlenden Treffer angezeigt wird. Eine verständliche Meldung ist besser als ein unauffälliger leerer Wert, weil fehlende Stammdaten dadurch schneller auffallen.
Wenn XVERWEIS in deiner Excel-Version nicht verfügbar ist, kannst du SVERWEIS oder eine Kombination aus INDEX und VERGLEICH verwenden. SVERWEIS verlangt jedoch, dass die Suchspalte links von der Rückgabespalte liegt. Bei größeren Berichten ist es außerdem sinnvoll, die Schlüsselwerte auf zusätzliche Leerzeichen und unterschiedliche Schreibweisen zu prüfen.
Abweichungen zwischen Ist und Plan sichtbar machen
Die absolute Abweichung berechnest du mit einer einfachen Subtraktion:
=Istwert-Planwert
Die prozentuale Abweichung lautet:
=WENNFEHLER((Istwert-Planwert)/Planwert;0)
Eine negative Zahl kann je nach Kennzahl günstig oder ungünstig sein. Bei Kosten bedeutet ein negativer Istwert gegenüber dem Plan meist eine Unterschreitung, bei Umsatz dagegen möglicherweise ein Rückstand. Benenne die Kennzahl deshalb eindeutig und lege fest, welche Richtung als positiv bewertet wird.
Für eine automatische Bewertung kannst du eine WENN-Funktion nutzen:
=WENN(Abweichung>=0;“Im Plan“;“Unter Plan“)
Die Schwelle sollte bei Bedarf nicht fest in der Formel stehen. Lege sie in einer separaten Zelle ab, damit sich die Toleranz später ändern lässt, ohne mehrere Formeln anzupassen.
WENN, UND und ODER für Regeln einsetzen
WENN eignet sich für einfache Klassifizierungen, etwa die Einteilung von Abweichungen in Warnstufen. Mit UND müssen alle Bedingungen erfüllt sein, während ODER bereits bei einer erfüllten Bedingung reagiert.
=WENN(UND(Abweichung>0,1;Betrag>=5000);“Prüfung erforderlich“;“Keine Sonderprüfung“)
Die Formel kennzeichnet Fälle, bei denen die Abweichung über zehn Prozent liegt und der Betrag mindestens 5.000 beträgt. Bei mehreren Stufen sind verschachtelte WENN-Funktionen möglich. Übersichtlicher wird es oft mit einer separaten Schwellentabelle und einem passenden Verweis.
Fehler mit WENNFEHLER kontrolliert behandeln
WENNFEHLER blendet typische Meldungen wie #NV, #DIV/0! oder #WERT! aus und ersetzt sie durch einen selbst gewählten Wert. Das ist in übersichtlichen Berichten hilfreich, sollte aber nicht dazu dienen, fehlende oder fehlerhafte Daten dauerhaft zu verstecken.
=WENNFEHLER(XVERWEIS(A2;Planwerte[Kostenstelle];Planwerte[Monatsbudget]);“Prüfen“)
Verwende bei Kontrolllisten besser einen deutlichen Hinweis wie „Prüfen“ statt einer leeren Zeichenfolge. So bleibt sichtbar, dass eine Zuordnung fehlt. Wenn du Fehler bereits in der Datenaufbereitung erkennen möchtest, nutze zusätzlich Filter auf die Fehlerzellen.
Datumsfunktionen für Monats- und Jahresberichte
MONAT und JAHR helfen dabei, Zeiträume aus einem Datum abzuleiten. Eine zusätzliche Spalte mit Jahr und Monat kann PivotTables und Diagramme vereinfachen.
=JAHR([@Datum])
=TEXT([@Datum];“JJJJ-MM“)
Die TEXT-Variante erzeugt eine gut sortierbare Monatsbezeichnung. Für Berechnungen bleiben echte Datumsgrenzen jedoch meist zuverlässiger. Mit HEUTE kannst du dynamische Zeiträume steuern, etwa offene Vorgänge bis zum aktuellen Tag. Prüfe bei wiederkehrenden Berichten, ob die automatische Aktualisierung wirklich gewünscht ist, da sich das Ergebnis täglich verändert.
PivotTables für flexible Auswertungen nutzen
PivotTables eignen sich, wenn du viele Datensätze nach Kostenstelle, Monat, Projekt oder Status gruppieren möchtest, ohne für jede Perspektive eine neue Formel zu schreiben. Klicke in die Quelldaten, wähle „Einfügen“ und anschließend „PivotTable“. Ziehe beispielsweise das Datum in den Zeilenbereich, die Kostenstelle in den Filterbereich und den Betrag in den Wertebereich.
Excel fasst Zahlen im Wertebereich normalerweise als Summe zusammen. Bei Text oder uneinheitlichen Daten kann stattdessen eine Anzahl erscheinen. Öffne in diesem Fall die Wertfeldeinstellungen und prüfe sowohl das Zusammenfassungsverfahren als auch das Zahlenformat.
Nach Änderungen an den Quelldaten musst du die PivotTable aktualisieren. Bei einer als Tabelle formatierten Quelle werden neue Zeilen in der Regel beim Aktualisieren berücksichtigt. Filter, Datenschnitte und gruppierte Datumsfelder solltest du vor der Weitergabe des Berichts kontrollieren, damit keine eingeschränkte Ansicht als vollständige Auswertung missverstanden wird.
Ein Bericht, der sich zuverlässig prüfen lässt
Trenne Rohdaten, Berechnungen und Darstellung möglichst auf unterschiedliche Tabellenblätter. Verändere die Rohdaten nicht direkt in der Berichtsebene, sondern dokumentiere Bereinigungsschritte oder nutze Power Query, wenn Daten regelmäßig aus mehreren Quellen geladen werden.
- Prüfe Datumswerte mit einer Sortierung vom ältesten zum neuesten Eintrag.
- Vergleiche eine Stichprobensumme aus der Quelldatei mit der Summe im Bericht.
- Filtere leere Kostenstellen, fehlende Planwerte und ungewöhnliche Beträge.
- Kontrolliere, ob die PivotTable nach einer Datenänderung aktualisiert wurde.
- Verwende einheitliche Zahlen-, Prozent- und Datumsformate.
- Schütze fertige Formeln nur dann, wenn die zuständigen Bearbeiter weiterhin wissen, wo Eingaben erlaubt sind.
Bei komplexen Dateien lohnt sich außerdem die Prüfung unter „Formeln“ mit „Formelüberwachung“. Bezugsfehler, zirkuläre Verweise und versehentlich überschriebenen Formeln erkennst du damit schneller als durch eine reine Sichtkontrolle.
Häufige Fragen zu Excel-Auswertungen im Controlling
Welche Excel-Funktion eignet sich für Summen mit mehreren Bedingungen?
SUMMEWENNS ist dafür die passende Standardfunktion. Sie kann beispielsweise Kosten nach Kostenstelle, Status und Zeitraum gleichzeitig filtern und addieren.
Warum liefert SUMMEWENNS kein Ergebnis?
Häufig stimmen Datentypen oder Schreibweisen nicht überein. Prüfe, ob das Datum als Datum erkannt wird, der Betrag eine Zahl ist und Kriterien wie Kostenstelle oder Status ohne zusätzliche Leerzeichen vorliegen.
Was ist der Unterschied zwischen XVERWEIS und SVERWEIS?
XVERWEIS kann in beide Richtungen suchen und benötigt die Suchspalte nicht zwingend links von der Ergebnisspalte. SVERWEIS ist in älteren Excel-Versionen verbreitet, aber bei der Tabellenstruktur weniger flexibel.
Wie lassen sich leere oder fehlerhafte Planwerte behandeln?
Nutze WENNFEHLER mit einem sichtbaren Hinweis wie „Prüfen“. Eine leere Ausgabe kann den Bericht zwar ruhiger wirken lassen, erschwert aber die Suche nach fehlenden Stammdaten.
Wann ist eine PivotTable besser als eine lange Formel?
Eine PivotTable ist besonders praktisch, wenn du dieselben Daten regelmäßig nach wechselnden Dimensionen gruppieren möchtest. Für fest definierte Kennzahlen mit klaren Bedingungen sind Formeln oft transparenter.
Kann Excel Monatsabweichungen automatisch berechnen?
Ja, sofern Ist- und Planwerte über eindeutige Monats- oder Periodenschlüssel verknüpft sind. Für die Berechnung sollten echte Datumswerte oder einheitliche Periodenkennungen verwendet werden, nicht unterschiedlich formatierte Textangaben.
Wie verhindere ich, dass Formeln im Bericht überschrieben werden?
Trenne Eingabezellen und Berechnungsbereiche und kennzeichne sie nachvollziehbar. Bei gemeinsam genutzten Dateien kann der Blattschutz helfen, nachdem du geprüft hast, dass alle benötigten Eingabefelder weiterhin bearbeitbar sind.
Der sinnvolle nächste Schritt
Baue die Auswertung zunächst mit einer kleinen, geprüften Datenmenge auf. Wenn Summen, Verknüpfungen und Zeitfilter stimmen, ergänzt du PivotTables, Kennzeichnungsregeln und Diagramme. So bleibt nachvollziehbar, an welcher Stelle ein Fehler entsteht, und der Bericht wächst kontrolliert mit den Anforderungen.
