Dika Ops entdecken

Wie kann ich meinen Lagerbestand in Excel fehlerfrei führen?

Lagerbestand in Excel führen: Artikelliste und Bewegungsjournal anlegen, Formeln für Bestand und Meldebestand setzen und mit dem Lager abgleichen.

Aktualisiert 8 Min. Lesezeit Unternehmenssoftware
Laptop auf einem grauen Sofa mit einem bunten Analyse-Dashboard aus Diagrammen und einer Weltkarte

Um den Lagerbestand in Excel zu führen, brauchen Sie zwei Tabellenblätter. Das erste ist eine Artikelliste mit einer Zeile für jedes Produkt. Das zweite ist ein Bewegungsjournal mit einer Zeile für jeden Wareneingang, jeden Verkauf, jede Umlagerung und jede Korrektur. Eine Formel summiert das Journal je Artikel und ergibt den Bestand, eine zweite Formel markiert die Artikel, die den Meldebestand erreicht haben. Dieser Aufbau funktioniert für einige hundert Produkte und einige tausend Bewegungen im Jahr und kommt ohne Zusatzprogramm und ohne Datenbank aus.

Die größte Schwachstelle vieler Lagertabellen sind nicht die Formeln, sondern die Gewohnheit, eine alte Menge einfach zu überschreiben. Danach lässt sich nicht mehr sagen, woher eine Differenz stammt. Ein Bewegungsjournal verhindert das, weil jede Veränderung als Zeile mit Datum und Grund erhalten bleibt. Der folgende Leitfaden zeigt, wie Sie die Blätter aufbauen, welche Formeln Sie brauchen und wie Sie das Ergebnis prüfen. Den Meldebestand selbst berechnen Sie am besten mit unserem Meldebestand-Rechner, der aus Verbrauch und Wiederbeschaffungszeit einen Wert ermittelt.

Warum ein Bewegungsjournal besser ist als eine einzige Bestandsspalte

Eine einzige Spalte mit der Bezeichnung „Bestand“ wirkt einfacher, verbirgt aber die Vorgeschichte. Stimmt die Zahl nicht, lässt sich nicht erkennen, ob eine Lieferung nicht erfasst wurde, ein Verkauf doppelt eingetragen wurde oder ein beschädigter Artikel nie ausgebucht wurde. Ein Journal hält jedes Ereignis fest, sodass Sie eine Differenz bis zu der Zeile zurückverfolgen können, die sie verursacht hat.

Das Journal liefert außerdem bessere Auswertungen. Sie sehen, wie viele Einheiten im Vormonat das Lager verlassen haben, welche Artikel am schnellsten gehen und welche gar nicht bewegt wurden. Genau diese Informationen bilden die Grundlage für Bestellentscheidungen und für die Suche nach Ladenhütern.

Lagerbestand in Excel führen: der Aufbau in fünf Schritten

Die folgenden Schritte bauen die Tabelle von Grund auf auf. Schritt 1 und 2 legen die Daten an, Schritt 3 und 4 rechnen und markieren, Schritt 5 prüft das Ergebnis gegen das Lager.

Schritt 1: Die Artikelliste anlegen

Legen Sie ein Blatt mit dem Namen „Artikel“ an. Jedes Produkt bekommt genau eine Zeile und eine eindeutige Artikelnummer. Die Nummern verbinden beide Blätter miteinander. Halten Sie sie kurz, fest und vergeben Sie sie nie zweimal.

SpalteInhaltBeispiel
A: ArtikelnummerEindeutige Nummer, die sich nicht ändertKAB-200
B: ArtikelbezeichnungDer Name, den Ihr Team im Regal verwendetKabelsatz, 2 m
C: EinheitDie Einheit, in der Sie zählenStück
D: LagerplatzWo die Ware liegtRegal 4
E: MeldebestandBestand, bei dem nachbestellt werden sollte80
F: BestandFormel aus Schritt 3166
G: StatusFormel aus Schritt 4OK

Schritt 2: Jede Bewegung im Journal erfassen

Legen Sie ein zweites Blatt mit dem Namen „Bewegungen“ an. Jede Zeile hält genau einen Vorgang fest, und eine Zeile ändern Sie nach der Eingabe nie wieder. Ist ein Fehler passiert, tragen Sie eine Korrekturzeile mit Begründung in der Bemerkung ein. Für die meisten kleinen Betriebe reichen diese Spalten:

  1. Spalte A, Datum: Der Tag, an dem sich die Ware bewegt hat, nicht der Tag, an dem Sie die Zeile getippt haben.
  2. Spalte B, Artikelnummer: Eine Nummer aus dem Blatt „Artikel“. Eine Dropdown-Liste über die Datenüberprüfung verhindert, dass ein Tippfehler einen neuen Artikel erzeugt.
  3. Spalte C, Art: „Zugang“ für Wareneingänge, „Abgang“ für Warenausgänge, Schäden und Ausbuchungen.
  4. Spalte D, Menge: Immer eine positive Zahl. Die Art bestimmt die Richtung.
  5. Spalte E, Menge mit Vorzeichen: Die Formel =WENN(C2="Abgang";-D2;D2) macht aus jeder Zeile ein Plus oder ein Minus.
  6. Spalte F, Beleg und Bemerkung: Lieferschein, Auftragsnummer oder Grund, damit sich jede Zeile nachprüfen lässt.

Erfassen Sie Bewegungen am Tag des Vorgangs und nicht gesammelt am Ende der Woche. Eine Woche mit nicht erfassten Ausgängen ist der häufigste Grund dafür, dass Tabelle und Regal nicht übereinstimmen.

Schritt 3: Den Bestand berechnen

Im Blatt „Artikel“ summiert Zelle F2 alle vorzeichenbehafteten Mengen des Artikels aus Zeile 2. Kopieren Sie die Formel nach unten. Sie lautet:

=SUMMEWENNS(Bewegungen!$E:$E;Bewegungen!$B:$B;A2)

Die Formel addiert die Mengen aller Bewegungen des Artikels aus A2, Zugänge also mit Plus und Abgänge mit Minus. Beginnen Sie mit einer Anfangsinventur, dann tragen Sie diese als einen Zugang mit dem Datum des ersten Tages ein. So stammt jede Zahl aus dem Journal.

Schritt 4: Artikel zum Nachbestellen markieren

In Zelle G2 vergleicht eine Formel den Bestand mit dem Meldebestand:

=WENN(F2<=E2;"Nachbestellen";"OK")

Wenden Sie auf die Statusspalte eine bedingte Formatierung an, damit die Zeilen mit „Nachbestellen“ farbig erscheinen. Sortieren oder filtern Sie die Liste danach jeden Morgen. Der Meldebestand in Spalte E sollte sich aus Bedarf und Wiederbeschaffungszeit ergeben und nicht aus dem Bauchgefühl. Der Meldebestand-Rechner liefert einen Wert mit den Rechenschritten, den Sie in Spalte E eintragen können.

Schritt 5: Die Tabelle mit dem Lager abgleichen

Eine Formel kann nur addieren, was erfasst wurde. Zählen Sie deshalb in einem festen Rhythmus nach, um Abweichungen zwischen Tabelle und Regal zu finden. Zählen Sie besser jede Woche einige Artikel als einmal im Jahr das ganze Lager. Weicht die Zählung ab, tragen Sie eine Korrekturzeile mit dem gezählten Wert und einer Begründung ein und prüfen danach, wie die Differenz entstanden ist. Wie Sie die Zählung methodisch durchführen, beschreibt unser Beitrag Inventur durchführen. Welche Verfahren zur Einteilung der Artikel passen, erklärt Methoden der Bestandsführung.

ProblemWahrscheinliche UrsacheAbhilfe
Bestand ist negativEin Abgang wurde vor dem Zugang erfasst oder eine Artikelnummer ist falschDatumsreihenfolge und Artikelnummer prüfen, mit einer neuen Zeile korrigieren
Tabelle zeigt mehr, als im Regal liegtBeschädigte oder zurückgesendete Ware wurde nie als Abgang erfasstAusbuchung oder Rücksendung mit Grund eintragen und den Artikel nachzählen
Artikel verschwinden aus der ListeEine Nummer wurde geändert oder eine Zeile gelöschtNummern nie ändern oder löschen, Artikel stattdessen deaktivieren
Summen stimmen nach dem Sortieren nichtFormeln bezogen sich auf Zeilen, die verschoben wurdenGanze Spalten in SUMMEWENNS verwenden und die Artikelliste als Tabelle führen

Ein Rechenbeispiel

Nehmen wir den Artikel KAB-200 mit einem Anfangsbestand von 120 Stück am 1. September. Das Journal zeigt einen Zugang von 200 Stück am 3. September, einen Abgang von 150 Stück am 12. September und eine Ausbuchung von 4 beschädigten Stück am 20. September. Die Vorzeichen lauten also +120, +200, -150 und -4. Die Formel ergibt 166 Stück auf Lager. Liegt der Meldebestand bei 80, bleibt der Status bei „OK“. Wird später ein weiterer Abgang von 90 Stück erfasst, sinkt der Bestand auf 76, und der Status wechselt auf „Nachbestellen“.

Wann Excel für den Lagerbestand nicht mehr ausreicht

Eine gut geführte Arbeitsmappe reicht einem kleinen Team lange. Sie stößt an Grenzen, wenn mehrere Personen gleichzeitig ändern, wenn Ware in mehreren Gebäuden liegt oder wenn die Tabelle mit Kundenaufträgen, Rechnungen und Einkauf verbunden werden soll. Dann entstehen doppelte Erfassungen und verspätete Aktualisierungen als Dauerzustand. Außerdem muss der Lagerwert zu den Zahlen der Buchhaltung passen. Welche Software dafür infrage kommt, vergleicht unser Beitrag zur Bestandsverwaltungssoftware, und für ein größeres Lager lohnt ein Blick auf das Lagerverwaltungssystem (WMS).

Dika Ops führt Lager, Verkauf, Einkauf und Finanzen auf einem Datensatz. Ein Warenausgang aktualisiert deshalb Bestand und Konten gemeinsam. Die Barcode-Erfassung in Inventur und Lagerarbeit ersetzt das Abtippen durch einen Scan. Das Lagermodul in Dika Ops zeigt, wie das im Alltag aussieht. Wenn Sie an die Grenzen Ihrer Tabelle stoßen, prüfen wir den Ablauf gern gemeinsam mit Ihnen. Sprechen Sie mit uns über Ihren Betrieb. Wie sich vorhandene Daten anbinden lassen, zeigt unsere Arbeit an Integration und Automatisierung.

Häufig gestellte Fragen

Kann ich den Lagerbestand kostenlos in Excel führen?

Ja. Excel oder eine kostenlose Tabellen-App reicht für eine Artikelliste und ein Bewegungsjournal mit den hier gezeigten Formeln. Die eigentlichen Kosten sind die Zeit für die Erfassung und die Pflege, damit die Tabelle stimmt, und nicht die Software.

Wie viele Artikel verträgt eine Excel-Lagertabelle?

Eine feste kleine Grenze gibt es nicht. Die Leistung nimmt aber ab, wenn das Journal in die Zehntausende Zeilen wächst und viele Formeln gleichzeitig neu berechnet werden. Archivieren Sie ältere Jahre in einer eigenen Datei und halten Sie nur den laufenden Zeitraum in der Arbeitsdatei.

Soll ich eine Excel-Tabelle oder einen einfachen Bereich verwenden?

Für Artikelliste und Journal ist eine Excel-Tabelle sinnvoll, weil neue Zeilen die Formeln automatisch übernehmen. Ganze Spalten in SUMMEWENNS funktionieren ebenfalls gut. Vermeiden Sie aber Summen oder Notizen in den Spalten, die die Formeln auswerten.

Wie führe ich den Bestand an zwei Standorten?

Ergänzen Sie im Journal eine Spalte für den Standort und filtern Sie die Formel nach Artikelnummer und Standort. Die Artikelliste bleibt bei einer Zeile je Produkt, und die Auswertung je Standort erfolgt über einen Filter oder eine Pivot-Tabelle.

Wie oft sollte ich die Tabelle aktualisieren und prüfen?

Erfassen Sie jede Bewegung am Tag des Vorgangs. Zählen Sie wöchentlich eine Stichprobe und den gesamten Bestand mindestens einmal im Jahr, bei hohen Werten oder leicht verlierbaren Artikeln häufiger. Differenzen korrigieren Sie mit Korrekturzeilen, die einen Grund nennen.

Nächster Schritt

Beginnen Sie mit den zehn Artikeln, die am meisten Ärger machen. Legen Sie dafür die Artikelliste an, erfassen Sie eine Woche lang alle Bewegungen und vergleichen Sie das Ergebnis am Freitag mit einer Zählung. Den Meldebestand setzen Sie mit dem Meldebestand-Rechner. Mehr über unser Team erfahren Sie auf der Startseite von Dika Design. Für Ihren Lagerbestand, der über Excel hinauswächst, lesen Sie außerdem Wie Sie Lagerverwaltung für kleine Unternehmen aufbauen.

Sie haben ein Projekt im Kopf? Sprechen wir es durch.

Sagen Sie uns, was Sie verbessern möchten. Wir antworten mit einem klaren nächsten Schritt.