Lagerverwaltung mit Excel: was gut geht und wann es aufhört
Viele kleine Lager laufen mit Excel, und das ist kein Fehler. Eine Tabelle kostet nichts extra, jeder kann sie öffnen, und für ein paar hundert Artikel, die eine Person im Blick hat, ist sie ein ordentliches Werkzeug. Schwierig wird es nicht, weil Excel schlecht wäre, sondern weil das Lager wächst und die Tabelle Dinge können soll, für die sie nicht gemacht ist.
Diese Seite zeigt, wie Sie eine Lagerverwaltung in Excel so anlegen, dass sie stimmt, wie Sie die häufigsten Fehler vermeiden und woran Sie ehrlich merken, dass es Zeit für etwas anderes ist.
Was Excel im Lager gut kann
- Artikel auflisten. Nummer, Bezeichnung, Einheit, Lieferant, Lagerplatz: eine saubere Stammliste.
- Zu- und Abgänge festhalten. Jede Bewegung als eigene Zeile, mit Datum und Beleg.
- Den Bestand ausrechnen. Aus allen Bewegungen eines Artikels, ohne dass jemand die Menge von Hand ändert.
- Nachbestellung anzeigen. Eine Zeile wird farbig, sobald der Bestand eine Grenze erreicht.
- Inventur vorbereiten. Eine Zählliste ausdrucken und die Differenzen nachtragen.
- Auswerten. Filtern, sortieren, eine Pivot-Tabelle für Verbrauch je Monat.
Für ein Lager mit überschaubarem Sortiment und einer Person, die bucht, reicht das oft jahrelang.
So legen Sie die Tabelle an: drei Blätter
Viele Vorlagen im Netz führen den Bestand in einer einzigen Spalte, die bei jedem Zu- oder Abgang überschrieben wird. Das geht schnell, hat aber einen Haken: Wenn die Zahl nicht stimmt, wissen Sie nicht, warum. Besser sind drei getrennte Blätter.
Artikel. Eine Zeile je Artikel: Artikelnummer, Bezeichnung, Einheit, Lagerplatz, Lieferant und die Mengen, bei denen nachbestellt wird. Formatieren Sie den Bereich als Tabelle (Strg+T), dann wachsen Formeln und Dropdowns mit, wenn neue Artikel dazukommen.
Bewegungen. Eine Zeile je Bewegung: Datum, Artikelnummer, Art (Zugang, Abgang, Retoure, Korrektur), Menge, Beleg und wer gebucht hat. Zugänge positiv, Abgänge negativ. Diese Zeilen werden nie überschrieben, nur ergänzt. So entsteht ein Protokoll, in dem Sie jede Differenz zurückverfolgen können.
Bestand und Nachbestellung. Der Bestand je Artikel ist die Summe seiner Bewegungen. In Excel heißt die Formel dafür SUMMEWENNS: Sie summiert die Spalte Menge für alle Zeilen mit dieser Artikelnummer. Damit ist der Bestand immer aus den Buchungen berechnet und nie eingetippt.
Mindestbestand und Nachbestellung
Für jeden Artikel tragen Sie zwei Mengen ein. Die erste ist die Menge, bei der Sie bestellen, damit die Ware ankommt, bevor das Regal leer ist; sie richtet sich nach Ihrem Verbrauch und danach, wie lange der Lieferant braucht. Das nennt man Meldebestand. Die zweite ist eine Reserve, die nur angetastet wird, wenn etwas schiefgeht, etwa eine verspätete Lieferung. Das nennt man Mindestbestand oder Sicherheitsbestand.
Eine einfache WENN-Formel zeigt dann "nachbestellen", sobald der berechnete Bestand den Meldebestand erreicht, und eine bedingte Formatierung färbt die Zeile gelb oder rot. Filtern Sie auf diese Zeilen, haben Sie Ihre Bestellliste. Wie hoch der Meldebestand sein sollte, rechnen Sie mit dem Rechner Meldebestand berechnen aus.
Fehler vermeiden: Datenqualität in Excel
Eine Tabelle ist nur so gut wie das, was hineingetippt wird. Drei Einstellungen verhindern die meisten Fehler:
- Eindeutige Artikelnummern. Steht dieselbe Nummer zweimal in der Artikelliste, landen Bewegungen beim falschen Eintrag, und der Bestand stimmt für beide nicht. Markieren Sie die Spalte und wählen Sie unter Bedingte Formatierung die Regel für doppelte Werte; Dubletten werden sofort farbig.
- Auswahl statt Freitext. Unter Daten, Datenüberprüfung legen Sie fest, dass in der Spalte Artikelnummer der Bewegungen nur Nummern aus der Artikelliste erlaubt sind, als Dropdown. Dasselbe für die Art der Bewegung. Damit gibt es keine Tippfehler und keine Schreibweisen wie "Zugang", "zugang" und "ZG" nebeneinander.
- Gültige Mengen. Ebenfalls über die Datenüberprüfung: nur ganze Zahlen, nicht null. Die Bezeichnung zu einer Nummer holen Sie per XVERWEIS aus der Artikelliste (in älteren Versionen SVERWEIS), statt sie jedes Mal neu zu schreiben.
Dazu eine Gewohnheit: Korrekturen werden als eigene Bewegung gebucht, mit Grund, und nie durch Ändern einer alten Zeile.
Inventur mit Excel
Für die Inventur drucken Sie eine Liste mit Artikelnummer, Bezeichnung und Lagerplatz, aber ohne die erwartete Menge, damit niemand nur abschreibt. Nach dem Zählen tragen Sie die gezählte Menge ein; die Differenz zum berechneten Bestand buchen Sie als Bewegung der Art "Inventur". So bleibt nachvollziehbar, wann und warum sich der Bestand geändert hat.
Statt einmal im Jahr alles zu zählen, können Sie auch jede Woche einen Teil der Artikel zählen, die schnell drehenden öfter, die langsamen seltener. Das nennt man zyklische Inventur, und es funktioniert mit einer Excel-Liste genauso.
Automatisieren mit Makros: hilfreich, aber mit Haken
Mit Makros, in Excel VBA genannt, lassen sich Eingabemasken bauen, Buchungen per Knopfdruck übertragen oder Bestelllisten erzeugen. Das spart Klicks. Es hat aber drei Haken: Die Datei muss im Format XLSM gespeichert werden, Excel sperrt Makros in Dateien aus dem Internet standardmäßig, und meist versteht nur die Person, die das Makro geschrieben hat, was es tut. Fällt sie aus, steht die Lagerverwaltung. Bevor Sie Makros schreiben, prüfen Sie, ob Formeln, Tabellen und Datenüberprüfung nicht schon reichen.
Wenn mehrere Leute dieselbe Datei öffnen
Diese Frage stellen sich die meisten erst, wenn es passiert. Liegt die Datei auf einem Netzlaufwerk, kann nur eine Person sie bearbeiten; wer sie danach öffnet, bekommt eine schreibgeschützte Fassung oder speichert eine Kopie, und schon gibt es zwei Wahrheiten. Liegt die Datei in OneDrive oder SharePoint und nutzen alle eine aktuelle Excel-Version, können mehrere gleichzeitig darin arbeiten.
Das löst aber nur die Hälfte. Excel weiß nicht, was im Lager gerade passiert. Wenn zwei Mitarbeiter zur selben Zeit die letzte Einheit eines Artikels für zwei verschiedene Aufträge ausbuchen, nimmt die Tabelle beide Buchungen an, und der Bestand wird negativ. Niemand wird gewarnt, bevor die Ware fehlt. Und gebucht wird, wenn jemand am Rechner sitzt, nicht in dem Moment, in dem die Ware das Regal verlässt.
Wann Excel nicht mehr reicht
An diesen fünf Stellen:
- Mehrere Leute buchen gleichzeitig. Wie oben beschrieben: Die Tabelle nimmt jede Eingabe an und prüft nicht, ob die Ware wirklich noch da ist.
- Scanner und Barcodes. Ein USB-Scanner tippt den Barcode wie eine Tastatur in die gerade markierte Zelle. Am Schreibtisch funktioniert das. Am Regal, mit einem Handgerät, das prüft, ob der richtige Artikel vom richtigen Platz genommen wird, nicht.
- Lagerplätze. Eine Spalte Lagerplatz geht, solange jeder Artikel genau einen Platz hat. Liegt derselbe Artikel an drei Stellen in unterschiedlicher Menge, wird die Tabelle unübersichtlich und die Menge je Platz stimmt kaum noch.
- Chargen und Seriennummern. Müssen Sie wissen, welche Charge an welchen Kunden gegangen ist, oder jede Seriennummer einzeln verfolgen, braucht jede Bewegung diese Angabe, und ein Rückruf wird zur Suche in tausenden Zeilen.
- Aufträge aus mehreren Kanälen. Kommen Bestellungen aus dem Onlineshop, von einem Marktplatz und per E-Mail, muss jemand sie alle von Hand in die Tabelle übertragen, und der Shop zeigt einen Bestand, der längst nicht mehr stimmt.
Trifft einer dieser Punkte zu, kostet die Tabelle mehr Zeit, als sie spart. Die Gegenüberstellung Lagerverwaltungssoftware oder Tabelle zeigt die Unterschiede im Detail.
Der nächste Schritt
Was Lagerverwaltung insgesamt umfasst und welche Stufen es zwischen Papier und eigenem System gibt, steht auf der Seite Lagerverwaltung. Was eine Software für das Lager leisten muss und wann der Umstieg sinnvoll ist, lesen Sie unter Lagerverwaltungssoftware. Ein Lagerverwaltungssystem wie BizBloqs bucht jede Bewegung per Scanner in dem Moment, in dem sie passiert, und führt den Bestand je Lagerplatz, Charge und Seriennummer.
Die Vorlage zu dieser Seite
Zum Herunterladen gibt es eine Excel-Vorlage mit den drei Blättern Artikel, Bewegungen und Mindestbestand, ohne Makros und mit Datenüberprüfung und Formeln bereits eingerichtet, dazu eine PDF-Fassung zum Ausdrucken.
Zwei Fragen zu Ihrem eigenen Lager
Wann haben Sie zuletzt eine Menge in Ihrer Tabelle überschrieben, ohne festzuhalten, warum?
Wie viele Personen haben Ihre Lagerdatei in der letzten Woche geöffnet?
Häufige Fragen
- Kann ich mein Lager mit Excel verwalten?
- Ja, solange das Lager klein ist, wenige Artikel hat und eine Person die Bewegungen bucht. Führen Sie Artikel und Bewegungen in getrennten Blättern und lassen Sie Excel den Bestand berechnen.
- Gibt es eine kostenlose Excel-Vorlage für die Lagerverwaltung?
- Ja, Microsoft und viele Vorlagenseiten bieten welche an. Achten Sie darauf, dass Bewegungen einzeln erfasst werden und der Bestand daraus berechnet wird, statt dass jemand die Menge überschreibt.
- Können mehrere Mitarbeiter gleichzeitig in derselben Excel-Datei buchen?
- Nur wenn die Datei in OneDrive oder SharePoint liegt und alle eine aktuelle Excel-Version nutzen. Auf einem Netzlaufwerk bekommt die zweite Person nur eine schreibgeschützte Kopie. Und auch beim gemeinsamen Bearbeiten prüft Excel nicht, ob zwei Leute gerade dieselbe letzte Einheit ausbuchen.
- Wie lege ich einen Mindestbestand in Excel an?
- Tragen Sie für jeden Artikel eine Menge ein, bei der nachbestellt wird, und lassen Sie eine Formel anzeigen, wenn der berechnete Bestand diese Menge erreicht. Mit einer bedingten Formatierung wird die Zeile dann farbig.
- Wann reicht Excel nicht mehr?
- Wenn mehrere Leute gleichzeitig buchen, mit Scannern gearbeitet werden soll, Lagerplätze, Chargen oder Seriennummern geführt werden müssen oder Aufträge aus mehreren Kanälen eingehen.