Lagerverwaltung mit Excel: kostenlose Vorlage und ihre Grenzen

Eine Lagerverwaltung in Excel funktioniert gut, wenn sie auf einer Regel beruht: Den Lagerbestand tippt man nie ein, man berechnet ihn. Dieser Leitfaden baut die Datei Schritt für Schritt auf, mit den genauen Formeln. Er liefert auch die kostenlose Vorlage und sagt offen, wo Excel an seine Grenzen kommt.

Wie funktioniert eine Lagerverwaltung in Excel?

Legen Sie zwei Excel-Tabellen an. Die erste listet die Artikel: Artikelnummer, Bezeichnung, Einkaufspreis, Anfangsbestand und Meldebestand. Die zweite erfasst jede Bewegung in einer eigenen Zeile: Datum, Artikelnummer, Zugang oder Abgang, Menge. Der aktuelle Bestand jedes Artikels ergibt sich aus diesen Bewegungen, ganz ohne manuelle Eingabe.

Viele Lagerlisten beginnen mit einem einzigen Blatt und einer Spalte „Menge“, die bei jeder Lieferung korrigiert wird. Am ersten Tag geht das schnell. Nach einem Monat lässt sich eine falsche Zahl nicht mehr erklären: Niemand weiß, wer sie geändert hat, wann und warum.

Die Trennung von Artikeln und Bewegungen löst dieses Problem. Jeder Wareneingang, jeder Verkauf, jeder Bruch wird zu einer datierten Zeile mit Kommentar. Der Bestand eines Artikels ist dann kein fragiler Wert mehr, sondern eine Summe, die sich jederzeit nachrechnen lässt.

Das Prinzip passt in eine Formel:

Verwenden Sie echte Excel-Tabellen (Start, dann Als Tabelle formatieren), keine einfachen Zellbereiche. Eine Tabelle wächst automatisch mit, sobald Sie eine Zeile hinzufügen, und ihre Formeln wandern mit.

Was enthält die kostenlose Vorlage?

Die Excel-Vorlage für die Lagerverwaltung ist eine Arbeitsmappe mit zwei Blättern, jedes als Excel-Tabelle formatiert. Das Blatt „Artikel“ enthält fünfzehn Beispielartikel und alle Formeln. Das Blatt „Bewegungen“ enthält vierzig Zu- und Abgänge, damit Sie die Berechnungen in Aktion sehen. Die beiden Tabellen heißen Artikel und Bewegungen. Der Download funktioniert ohne Anmeldung.

Das Blatt „Artikel“ hat dreizehn Spalten:

Das Blatt „Bewegungen“ hat fünf Spalten: Datum, Artikelnummer, Art, Menge und Kommentar. Die Spalte Art bietet in einer Dropdown-Liste zwei Werte an, „Zugang“ oder „Abgang“. Im Blatt Artikel sind die nachzubestellenden Zeilen farbig markiert. Ersetzen Sie die Beispiele durch Ihre Artikel und erfassen Sie Ihre Bewegungen laufend.

Wie berechnen Sie den aktuellen Bestand mit SUMMEWENNS?

Die Funktion SUMMEWENNS addiert die Werte einer Spalte, die mehrere Bedingungen erfüllen. Hier summiert sie die Mengen im Blatt Bewegungen, deren Artikelnummer zum Artikel passt und deren Art „Zugang“ lautet. Dieselbe Formel mit „Abgang“ liefert die Abgänge. Daraus ergibt sich der aktuelle Bestand.

Die Syntax lautet SUMMEWENNS(Summe_Bereich; Kriterien_Bereich1; Kriterien1; [Kriterien_Bereich2; Kriterien2]; …), laut der Microsoft-Seite zu SUMMEWENNS. Zuerst kommt der Summenbereich, dann jeweils ein Paar aus Bereich und Kriterium.

In der Vorlage enthält die Spalte Zugänge (H) in Zeile 2:

=SUMMEWENNS(Bewegungen[Menge];Bewegungen[Artikelnummer];A2;Bewegungen[Art];"Zugang")

Die Spalte Abgänge (I) verwendet dieselbe Formel mit „Abgang“:

=SUMMEWENNS(Bewegungen[Menge];Bewegungen[Artikelnummer];A2;Bewegungen[Art];"Abgang")

Die Spalte Aktueller Bestand (J) addiert Anfangsbestand (G) und Zugänge (H) und zieht die Abgänge (I) ab:

=G2+H2-I2

Bewegungen[Menge] ist ein strukturierter Verweis: Er bezeichnet die gesamte Spalte Menge der Tabelle Bewegungen, auch wenn sie länger wird. A2 ist die Artikelnummer in derselben Zeile. Die Vorlage mischt beide Schreibweisen, damit die Formel lesbar bleibt.

Wenn Sie lieber durchgehend strukturierte Verweise verwenden, ersetzen Sie A2 durch [@Artikelnummer]. Das @ steht für die Zelle der aktuellen Zeile, wie die Dokumentation zu strukturierten Verweisen erklärt. Beide Varianten liefern dasselbe Ergebnis.

In einem deutsch eingestellten Excel trennt das Semikolon die Argumente. In einer Tabelle kopiert sich eine in Zeile 2 eingegebene Formel automatisch in die ganze Spalte.

Wie legen Sie Meldebestand und Bestellliste fest?

Der Meldebestand ist die Menge, bei deren Unterschreitung nachbestellt werden muss. Tragen Sie ihn für jeden Artikel ein und vergleichen Sie ihn mit der Funktion WENN mit dem aktuellen Bestand. Die Spalte „Nachbestellen“ zeigt „Ja“, sobald der Bestand den Meldebestand erreicht oder unterschreitet. Ein Filter auf „Ja“ ergibt die Bestellliste des Tages.

Die Funktion WENN lautet WENN(Wahrheitstest; Wert_wenn_wahr; [Wert_wenn_falsch]), laut der Microsoft-Seite zu WENN. In der Spalte Nachbestellen steht:

=WENN(J2<=K2;"Ja";"Nein")

J2 ist der aktuelle Bestand, K2 der Meldebestand. Durch das <= springt der Artikel schon auf „Ja“, wenn er seinen Meldebestand erreicht, nicht erst darunter.

Für die Höhe des Meldebestands reicht eine einfache Regel. Multiplizieren Sie den durchschnittlichen Tagesverbrauch mit der Lieferzeit in Tagen und rechnen Sie einen Puffer hinzu. Ein Artikel mit 4 Einheiten Abgang pro Tag und 5 Tagen Lieferzeit braucht beim Bestellen mindestens 20 Einheiten. Mit einem Puffer von 10 Einheiten liegt der Meldebestand bei 30.

Wie berechnen Sie den Lagerwert?

Der Lagerwert eines Artikels ist sein aktueller Bestand multipliziert mit dem Einkaufspreis netto. Der Gesamtwert ist die Summe dieser Spalte. Er zeigt, wie viel Geld in den Regalen gebunden ist. Außerdem ist er ein nützlicher Ausgangspunkt für die Inventur zum Geschäftsjahresende.

In der Vorlage multipliziert die Spalte Lagerwert (M) den aktuellen Bestand (J) mit dem Einkaufspreis netto (F):

=J2*F2

Die Datei enthält keine Gesamtsumme. Schreiben Sie dafür in eine Zelle außerhalb der Tabelle:

=SUMME(Artikel[Lagerwert])

Die Vorlage bewertet jeden Artikel zu einem einzigen Einkaufspreis, was für die Steuerung genügt. Schwanken Ihre Preise von Lieferung zu Lieferung stark, fragen Sie Ihre Steuerberatung, welches Bewertungsverfahren passt.

Um diesen Wert über die Zeit zu verfolgen, mit einem Diagramm nach Kategorie oder Lieferant, lesen Sie unseren Leitfaden zum Dashboard in Excel.

Eingaben absichern: Dropdown-Listen und bedingte Formatierung

Zwei Einstellungen verhindern die meisten Fehler, und die Vorlage enthält bereits beide. Die Datenüberprüfung bietet in der Spalte Art nur „Zugang“ oder „Abgang“ an. So entstehen keine Varianten wie „Eingang“ oder „Zugnag“, die SUMMEWENNS übersehen würde. Die bedingte Formatierung färbt die Artikel unter dem Meldebestand ein.

Die Dropdown-Liste für die Bewegungsart

Markieren Sie die Spalte Art der Tabelle Bewegungen. Wählen Sie auf der Registerkarte Daten in der Gruppe Datentools die Option Datenüberprüfung. Wählen Sie unter Zulassen den Eintrag Liste und geben Sie die beiden Werte im Feld Quelle ein, gemäß der Anleitung von Microsoft. In einem deutschen Excel trennen Sie die Werte mit Semikolon: Zugang;Abgang.

Dasselbe Verfahren eignet sich für die Spalte Artikelnummer. Eine Liste, die auf die Artikelnummern im Blatt Artikel verweist, verhindert Tippfehler. Eine Bewegung mit unbekannter Artikelnummer wird nirgends gezählt und verfälscht den Bestand ohne jede Meldung.

Artikel unter dem Meldebestand einfärben

Markieren Sie die Datenzeilen der Tabelle Artikel, ohne die Überschrift. Öffnen Sie auf der Registerkarte Start die Bedingte Formatierung und dann Neue Regel. Wählen Sie „Formel zur Ermittlung der zu formatierenden Zellen verwenden“, wie es die Microsoft-Seite zur bedingten Formatierung beschreibt.

In der Vorlage steht die Überschrift in Zeile 1 und die Spalte Nachbestellen in Spalte L. Die Regel verwendet daher die Formel:

=$L2="Ja"

Das Dollarzeichen fixiert die Spalte, nicht die Zeile: Jede Zeile prüft ihre eigene Zelle.

Wie führen Sie eine Inventur durch und behandeln Differenzen?

Bei der Inventur zählen Sie jeden Artikel körperlich und vergleichen das Ergebnis mit dem aktuellen Bestand der Datei. Der Unterschied ist eine Inventurdifferenz. Den Anfangsbestand korrigieren Sie dabei nicht. Sie erfassen die Differenz als Bewegung: als Zugang, wenn in der Datei Einheiten fehlen, als Abgang, wenn dort zu viele stehen.

Für Kaufleute ist die Inventur keine Kür. Nach § 240 HGB müssen sie zu Beginn ihres Handelsgewerbes und zum Schluss jedes Geschäftsjahrs ein Inventar aufstellen, mit Vermögensgegenständen, Schulden und deren Wert. Ein Geschäftsjahr darf zwölf Monate nicht überschreiten. Ihre Steuerberatung sagt Ihnen, was für Ihr Unternehmen gilt.

So gehen Sie mit der Vorlage vor:

1. Fügen Sie neben der Tabelle Artikel vorübergehend zwei Spalten hinzu: „Gezählt“ und „Differenz“.
2. Zählen Sie die Artikel und tragen Sie die Mengen ein, am besten außerhalb der Zeiten mit Warenbewegungen.
3. Berechnen Sie die Differenz: gezählte Menge minus aktueller Bestand.
4. Fügen Sie für jede Differenz eine Zeile in Bewegungen hinzu, mit dem Zähldatum und dem Kommentar „Inventur“.
5. Prüfen Sie, ob der aktuelle Bestand der Zählung entspricht, und löschen Sie dann die Hilfsspalten.

Die Inventurbewegungen bleiben sichtbar. Taucht beim selben Artikel immer wieder eine Differenz auf, steckt ein Problem dahinter: ein nicht erfasster Abgang, Bruch oder eine missverstandene Verpackungseinheit. Der Kommentar hilft, die Ursache zu finden.

Bewährte Regeln für eine verlässliche Datei

Eine Lagerliste bleibt korrekt, wenn Bewegungen noch am selben Tag erfasst werden, von einer Person oder nach einer klaren Regel. Vergeben Sie pro Artikel eine eindeutige Artikelnummer, löschen Sie nie eine Bewegungszeile und schützen Sie die berechneten Spalten. Ein paar feste Gewohnheiten bringen mehr als eine zusätzliche Formel.

• Eine Artikelnummer, ein Artikel. Keine Dubletten und keine Nummer, die für ein anderes Produkt wiederverwendet wird.
• Per Bewegung korrigieren. Ein Eingabefehler wird durch eine kommentierte Gegenbuchung korrigiert, nicht durch Löschen der Zeile.
• Am selben Tag erfassen. Ein Lieferschein, der erst am Ende der Woche eingetragen wird, bedeutet fünf Tage falschen Bestand.
• Formeln schützen. Alle Zellen sind standardmäßig gesperrt. Entsperren Sie die Eingabespalten und schützen Sie dann das Blatt (Registerkarte Überprüfen, Blatt schützen).
• Jährlich archivieren. Wird das Blatt Bewegungen zu lang, beginnen Sie eine neue Datei mit dem gezählten Bestand als Anfangsbestand.

Wo stößt eine Lagerliste in Excel an ihre Grenzen?

Excel eignet sich sehr gut für eine Person, die einige Dutzend oder einige Hundert Artikel vom Schreibtisch aus verwaltet. An Grenzen stößt es, wenn mehrere Personen Bewegungen erfassen oder die Eingabe im Lager am Smartphone erfolgt. Oder wenn ein Fehlbestand teuer wird, weil niemand auf die Spalte Nachbestellen geschaut hat.

Diese Punkte stammen von den Microsoft-Seiten zur gemeinsamen Dokumenterstellung, zum Anzeigen von Änderungen und zum Blattschutz. Microsoft empfiehlt zudem, jeder Person eigene Bereiche oder Blätter zuzuweisen, um Konflikte zu vermeiden. Das ist machbar, aber die Datei wird dann eher zur Organisationsfrage als zur Formelfrage.

Sind Sie an diesem Punkt, gibt es zwei Wege: eine spezialisierte Lagerverwaltungssoftware oder ein Tool, in dem Sie Ihre eigene Vorlage behalten. Unser Vergleich kostenloser Lagerverwaltungssoftware stellt die Optionen vor, Rangee eingeschlossen.

Wie übertragen Sie dieselbe Vorlage in ein gemeinsames Tool?

Importieren Sie die Arbeitsmappe in Rangee. Der intelligente Import erkennt eine Lagerdatei an ihren Spalten (Artikelnummer, Menge, Meldebestand, Preis) und schlägt die Vorlage „Lager und Inventur“ vor. Daraus entstehen eine Base und ein Interface: Startseite, Katalog, Bestellliste und eine Seite pro Artikel. Nichts wird ohne Ihre Zustimmung angelegt.

Die Startseite zeigt die Zahl der Artikel, den Lagerwert und die Artikel unter dem Meldebestand. Der Katalog listet alle Artikel auf. Die Seite „Nachbestellen“ zeigt nur Artikel, die ihren Meldebestand erreicht haben, und die Artikelseite bündelt alles zu einem Produkt. Jede vorgeschlagene Tabelle, Spalte und Seite können Sie übernehmen oder verwerfen. Zum Lesen Ihrer Datei erhält die KI nur deren Profil: Spaltennamen, Typen und höchstens zehn Beispielwerte pro Spalte, nie die ganze Datei.

Drei Ergänzungen schließen die oben genannten Lücken:

• Ein Formular für Bewegungen. Die Person im Lager wählt Artikel, Art und Menge am Smartphone. Auf Mobilgeräten werden die Interfaces einspaltig angezeigt.
• Eine Automatisierung für den Meldebestand. Sie startet, sobald ein Artikel die Bedingung „aktueller Bestand kleiner oder gleich Meldebestand“ erfüllt. Dann kann sie den Einkauf per E-Mail, in Slack oder in Teams benachrichtigen. Sie testen sie, bevor Sie sie aktivieren.
• Rechte und ein Verlauf. Der Versionsverlauf speichert jede Änderung mit ihrem Urheber. Leser und Kommentatoren sind kostenlos: Die Geschäftsführung sieht den Lagerwert ohne bezahlten Platz.

Ein ehrlicher Hinweis zu den Mengen: Jede Bewegung ist ein Datensatz. Der Free-Plan erlaubt 500 Datensätze pro Base, was für ein kleines Sortiment über einige Wochen oder Monate reicht. Darüber hinaus kostet der Pro-Plan 5 € pro Bearbeiter und Monat und erlaubt 100.000 Datensätze pro Base (zu den Preisen). Bei 80 % des Limits erhalten Sie einen Hinweis, und es gehen keine Daten verloren.

Laden Sie die Vorlage herunter, tragen Sie Ihre Artikel ein und nutzen Sie sie, solange sie Ihnen genügt. Sobald mehrere Personen darin schreiben müssen, importieren Sie sie in Rangee: Der Free-Plan läuft nicht ab, und aus Ihrer Datei wird in wenigen Minuten ein gemeinsames Tool.