KHIZHNYAKOVBericht aus Exportdaten in Excel erstellen: von der Rohtabelle zur Übersicht
Universeller Bericht aus Exportdaten – Excel-Vorlage — Zur Vorlage →
Jeden Monat dasselbe: Aus dem Warenwirtschaftssystem, dem Online-Shop oder der Buchhaltung kommt ein Export, und daraus soll ein Bericht werden. Summen nach Monaten, die wichtigsten Kategorien, die größten Kunden, der Vergleich zum Vorjahr. Wer das jedes Mal von Grund auf neu baut, verliert Stunden, und jede Pivot-Tabelle sieht ein wenig anders aus.
Besser ist ein fester Aufbau: Die Rohdaten kommen auf ein Blatt, alles andere rechnet sich selbst. Dieser Artikel zeigt, wie Sie das einrichten, worauf Sie bei der Datenprüfung achten und mit welchen Formeln Sie die typischen Auswertungen erzeugen.
Der Grundaufbau
Trennen Sie drei Dinge: Rohdaten, Berechnung und Bericht. Auf dem Datenblatt liegt nur der Export, unverändert. Daneben stehen Hilfsspalten (Jahr, Monat), die per Formel entstehen. Der Bericht greift ausschließlich auf diese Spalten zu. Wenn der nächste Export kommt, löschen Sie die alten Daten, fügen die neuen ein, und der Bericht aktualisiert sich.
Voraussetzung ist, dass die Spalten Ihres Exports immer gleich heißen oder dass Sie die Namen an einer Stelle einstellen können. Typische Spalten: Datum, Kategorie, Geschäftspartner, Menge, Betrag.
Hilfsspalten für Jahr und Monat
Aus dem Datum in A2 leiten Sie ab:
=JAHR(A2) und =MONAT(A2)
Aufgepasst: Das funktioniert nur, wenn das Datum wirklich ein Datum ist und kein Text. Exporte liefern häufig Text im Format „2024-03-15“ oder „15.03.2024“, den Excel je nach Einstellung nicht erkennt. Mit =DATWERT(A2) wandeln Sie ihn um.
Summen nach Monaten und Kategorien
Mit SUMMEWENNS summieren Sie den Betrag für Jahr, Monat und Kategorie:
=SUMMEWENNS(Daten!E:E;Daten!G:G;2024;Daten!H:H;3;Daten!B:B;"Zubehör")
Für die Monatssumme lassen Sie die Kategorie weg. Das Wachstum zum Vormonat ist =B3/B2-1, zum gleichen Monat des Vorjahres teilen Sie durch die Summe derselben Spalte im Vorjahr. Beispiel: März 2024 Umsatz 48.000 EUR, März 2023 40.000 EUR: Wachstum +20 %. Denken Sie bei Vorjahresvergleichen an WENNFEHLER, damit eine Division durch 0 nicht den ganzen Bericht stört.
Ranglisten: Top-Kategorien und Top-Partner
Für die Top-Liste summieren Sie je Kategorie oder Partner und sortieren dann absteigend. Mit KGRÖSSTE holen Sie den n-größten Wert, mit INDEX und VERGLEICH den zugehörigen Namen:
=KGRÖSSTE(Summen;1) und =INDEX(Namen;VERGLEICH(KGRÖSSTE(Summen;1);Summen;0))
Bei gleichen Summen liefert VERGLEICH immer den ersten Treffer. Für eine robuste Rangliste fügen Sie der Summe einen winzigen Zufallswert oder die Zeilennummer hinzu (zum Beispiel 0,0000001 mal Zeilennummer), damit alle Werte eindeutig sind.
Datenprüfung: Fehler finden, bevor sie im Bericht landen
Ein Bericht ist nur so gut wie die Daten. Prüfen Sie vor jeder Auswertung:
| Prüfung | Formel (Beispiel) |
|---|---|
| Leere Datumsfelder | =ZÄHLENWENN(A2:A5001;"") |
| Text statt Zahl im Betrag | =SUMMENPRODUKT(--ISTTEXT(E2:E5001)) |
| Datum außerhalb des Zeitraums | =ZÄHLENWENN(A2:A5001;"<"&Beginn)+ZÄHLENWENN(A2:A5001;">"&Ende) |
| Dubletten | Hilfsspalte aus Datum, Partner und Betrag, dann ZÄHLENWENN |
Zeigt eine Prüfung einen Wert größer als 0, schauen Sie sich die Zeilen an, bevor Sie den Bericht weitergeben.
Häufige Fehler
- Daten im Export von Hand verändern, sodass der nächste Export nicht mehr passt.
- Ganze Spalten (
A:A) in schweren Formeln verwenden. Begrenzte Bereiche bleiben schneller. - Prozentwerte und Beträge im selben Diagramm mischen.
- Dubletten übersehen, wenn derselbe Zeitraum doppelt exportiert wurde.
Fertige Excel-Vorlage für Berichte aus Exportdaten
Wer das nicht jedes Mal selbst aufbauen möchte, nutzt die kostenpflichtige Vorlage „Universeller Bericht aus Exportdaten“. Auf dem Blatt „Parameter“ stellen Sie das Berichtsjahr und die Spaltennamen Ihres Exports ein. Das Blatt „Daten“ bietet eine Kopfzeile und 5000 Zeilen zum Einfügen, plus interne Spalten zur Normalisierung (Monat, Jahr, Schlüssel).
Die „Übersicht“ zeigt Summen nach Monaten, Wachstum zum Vormonat und zum gleichen Monat des Vorjahres, die Top 5 Kategorien, die Top 10 Geschäftspartner, eine Matrix „Kategorie × Monat“ sowie ein Säulen- und ein Kreisdiagramm. „Zeitraum“ liefert Betrag und Menge für einen beliebigen Zeitraum und ausgewählte Kategorien. Die „Datenprüfung“ meldet leere Datumsfelder, Text statt Zahlen, Daten außerhalb des Zeitraums und Dubletten mit Zählern und Erläuterungen.
Das spart Ihnen die Arbeit mit Hilfsspalten, Rankings und Prüfungen. Jede Tabelle mit Datum, Kategorien, Menge und Betrag lässt sich einfügen, auch aus einem ERP-System. Die Datei kommt ohne Makros und ohne neue Funktionen aus, läuft in Excel 2007 und neuer sowie LibreOffice und ist auf Deutsch. Grenzen: bis zu 5000 Zeilen, 30 Kategorien und 200 Geschäftspartner.
FAQ
Was mache ich, wenn mein Export mehr als 5000 Zeilen hat?
Fassen Sie die Daten vor dem Einfügen zusammen, zum Beispiel pro Tag, Kategorie und Partner, oder teilen Sie den Zeitraum auf. Für sehr große Datenmengen sind Pivot-Tabellen oder Power Query besser geeignet.
Wie importiere ich eine CSV-Datei richtig?
Öffnen Sie sie über „Daten > Aus Text/CSV“ und wählen Sie Trennzeichen und Zeichensatz bewusst. So erkennen Sie falsche Dezimaltrennzeichen und kaputte Umlaute, bevor sie in der Auswertung auftauchen.
Pivot-Tabelle oder Formeln?
Pivot-Tabellen sind schnell und flexibel, müssen aber nach neuen Daten aktualisiert werden. Formeln rechnen sofort und lassen sich in feste Berichtsformate einbauen. Für wiederkehrende Berichte sind Formeln meist bequemer.
Wie vergleiche ich zwei Zeiträume?
Berechnen Sie die Summen für beide Zeiträume mit SUMMEWENNS und dem Datum als Bedingung (">="&Beginn, "<="&Ende) und bilden Sie die Differenz in Prozent.
