Tutorials

Tutorial: Bestandsveränderungen in Excel verfolgen und analysieren

Melden

Dieses Tutorial führt dich Schritt für Schritt durch die Erstellung eines professionellen Bestandsverwaltungssystems in Excel, mit dem du tägliche und wöchentliche Veränderungen analysieren kannst.

Ein effektives Inventarsystem besteht aus drei Hauptkomponenten:

  1. Stammdaten: Was hast du?
  2. Transaktionsprotokoll: Was kommt rein, was geht raus?
  3. Analyse-Dashboard: Wie verändert sich der Bestand über die Zeit?

Schritt 1: Die Tabellenstruktur aufbauen

Wir nutzen die Funktion "Als Tabelle formatieren", damit sich Formeln automatisch erweitern.

Blatt 1: "Stammdaten"

Erstelle eine Tabelle mit folgenden Spalten:

  • Artikel-ID (z. B. 1001)
  • Artikelname
  • Anfangsbestand (Dein Lagerbestand zum Startzeitpunkt)
  • Meldebestand (Wann muss nachbestellt werden?)
  • Aktueller Bestand (Berechnetes Feld)

Blatt 2: "Transaktionen"

Hier trägst du jede Bewegung ein:

  • Datum
  • Artikel-ID
  • Typ (Eingang / Ausgang)
  • Menge

Schritt 2: Dynamik durch Formeln hinzufügen

Damit der "Aktuelle Bestand" in den Stammdaten automatisch berechnet wird, nutzen wir die SUMMEWENNS-Formel.

Gehe in das Blatt Stammdaten in die Spalte Aktueller Bestand und gib folgende Formel ein:

=Anfangsbestand + SUMMEWENNS(Transaktionen[Menge]; Transaktionen[Artikel-ID]; [@[Artikel-ID]]; Transaktionen[Typ]; "Eingang") - SUMMEWENNS(Transaktionen[Menge]; Transaktionen[Artikel-ID]; [@[Artikel-ID]]; Transaktionen[Typ]; "Ausgang")

Erklärung: Die Formel addiert alle Eingänge und subtrahiert alle Ausgänge für die jeweilige Artikel-ID.

Schritt 3: Analyse der täglichen und wöchentlichen Änderungen

Um Trends zu sehen (z. B. "Wie viel haben wir diese Woche verbraucht?"), nutzen wir Pivot-Tabellen.

Erstellung der Analyse:

  1. Markiere die Tabelle im Blatt Transaktionen.
  2. Gehe auf Einfügen > PivotTable.
  3. Ziehe Datum in den Bereich Zeilen.
  4. Ziehe Menge in den Bereich Werte.
  5. Ziehe Typ in den Bereich Spalten.

Zeitliche Gruppierung (Täglich/Wöchentlich):

Excel gruppiert Daten oft automatisch. Falls nicht:

  • Klicke mit der rechten Maustaste auf ein Datum in der Pivot-Tabelle.
  • Wähle Gruppieren.
  • Für tägliche Analyse: Wähle "Tage".
  • Für wöchentliche Analyse: Wähle "Tage" und stelle bei "Anzahl der Tage" 7 ein (achte darauf, dass das Startdatum ein Montag ist).

Schritt 4: Bestandsverlauf visualisieren

Um Bestandsveränderungen grafisch darzustellen, eignet sich ein Liniendiagramm.

  1. Erstelle eine Pivot-Tabelle, die das Datum in den Zeilen und die Summe der Menge (gefiltert nach Ausgängen) zeigt.
  2. Gehe auf PivotTable-Analyse > PivotChart.
  3. Wähle ein Liniendiagramm.
    • Tipp: Wenn du die Bestandsentwicklung sehen willst, erstelle eine Hilfsspalte in deinen Transaktionen, die den kumulierten Bestand berechnet.

Schritt 5: Bedingte Formatierung für Warnungen

Damit du sofort siehst, wenn ein Artikel zur Neige geht:

  1. Markiere die Spalte Aktueller Bestand in den Stammdaten.
  2. Gehe zu Start > Bedingte Formatierung > Neue Regel.
  3. Wähle: "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
  4. Gib ein: =[@[Aktueller Bestand]] <= [@[Meldebestand]]
  5. Wähle eine rote Füllung.

Profi-Tipps für die Analyse

  • Datenschnitte nutzen: Füge über PivotTable-Analyse > Datenschnitt einfügen einen Filter für "Artikelname" oder "Kategorie" hinzu. So kannst du das Dashboard mit einem Klick filtern.
  • Umschlagsgeschwindigkeit: Berechne, wie oft sich dein Lager leert, indem du den Gesamtverbrauch pro Monat durch den durchschnittlichen Lagerbestand teilst.
  • SVERWEIS/XVERWEIS: Nutze den XVERWEIS in der Transaktionsliste, um den Artikelnamen automatisch basierend auf der ID anzuzeigen. Das verhindert Tippfehler.

Zusammenfassung der Analyse-Ebenen:

  • Täglich: Identifikation von Spitzen (Wann gehen die meisten Waren raus?).
  • Wöchentlich: Planung von Nachbestellungen (Reicht der Bestand für nächste Woche?).
  • Monatlich: Strategische Planung (Welche Ladenhüter müssen raus?).
0