Beispiel Bestandsführung über Summewenn

Aufgabenstellung

Für ein kleines Lager soll über Excel eine automatische Bestandsermittlung erfolgen. Auf Basis der Zugänge und der Abgänge soll der aktuelle Bestand ermittelt werden.
Es soll für Zugänge und Abgänge je ein Tabellenblatt geben, in dem die jeweiligen Zugänge und Abgänge der Artikel erfasst werden und im Blatt „Artikelstamm“ soll sofort jeweils der aktuelle Bestand des jeweiligen Artikels ersichtlich sein.

Erweiterung
Im Blatt Artikelstamm werden Mindestbestände geführt. Wird der Mindestbestand unterschritten wird über bedingte Formatierung die Zeile markiert und mit einem Vermerk „Bestellen“ versehen.
Weiterführende Auswertungen über Pivottabellen finden Sie auf www.excel-pivot.de
Dort finden Sie auch ausführliche Anleitungen und Beispieldateien zu Pivotauswertungen.

Zur Verfügung stehende Dateien
• Bestandsfuehrung.xls  
Download : http://www.xlfaq.de/downloads/Bestandsfuehrung.xls
• Loesungsvorschlag_Bestandsfuehrung.xls
Download : http://www.xlfaq.de/downloads/Loesungsvorschlag_Bestandsfuehrung.xls
• Skript_Bestandsführung.pdf
Download : http://www.xlfaq.de/downloads/Bestandsfuehrung.pdf
Diese Dateien stehen auch als komplette ZIP-Datei im Internet zum Download bereit.
http://www.xlfaq.de/downloads/bestandsfuehrung.zip

Verwendete Funktionen:
• Wenn
• Summewenn
• dynamischer Bereich über Namen
• Daten Gültigkeit Liste(Dropdown)
• bedingte Formatierung

Vorgehensweise

In den Tabellenblättern Zugang und Abgang wollen wir nur Eingaben für im Artikelstamm vorhandene Artikel über ein Dropdown erlauben. Da sich unser Artikelstamm verändern kann (es kommen eventuell Artikel hinzu) gestalten wir den Bereich für die Dropdownfelder dynamisch und vergeben dafür einen Namen.
Wählen Sie dazu in der Menüleiste Einfügen - Namen – definieren und vergeben den Namen “Artikelstamm“. Bei bezieht sich auf geben Sie nachstehende Formel ein:

=INDIREKT("Artikelstamm!B2:B"&ANZAHL2(Artikelstamm!$B:$B))

Dropdowns in Excel über Daten – Gültigkeit Liste lassen sich normalerweise nur erzeugen, wenn die Quelle sich im selben Tabellenblatt wie das Dropdown befindet. Dies können Sie aber umgehen, in dem Sie dem Listenbereich der die Werte beinhalten soll über Einfügen Namen einen Namen vergeben. Im Dropdown für Daten Gültigkeit Liste wählen Sie nun bei Quelle statt eines Bereich Ihren vergebenen Namen. Da Exelnamen Gültigkeit für die gesamte Arbeitsmappe haben, können Sie so diese Beschränkung umgehen und in unserem Fall die Artikel in ein gesondertes Tabellenblatt Artikelstamm auslagern.
Da unser Bereich jedoch dynamisch und nicht statisch sein soll nutzen wir die Funktionen Indirekt und Anzahl2 um zu ermitteln wie groß der Bereich ist, in dem sich die Artikelstammdaten befinden.
Wir setzen also über nachstehende Funktion den Bereich zusammen.
=INDIREKT("Artikelstamm!B2:B"&ANZAHL2(Artikelstamm!$B:$B))

Über Anzahl2 wird ermittelt bis zu welcher Zeile der Spalte B im Tabellenblatt Artikelstamm die Einträge gehen. In unserem Fall liefert ANZAHL2(Artikelstamm!$B:$B) uns die Zahl 21. Bis zur Zeile 21 stehen Einträge in Spalte B.
Über Artikelstamm!B2:B"& setzen wir den Startwert auf B2 (B1 enthält unsere Überschrift) und das &-Zeichen vor der Anzahl2 Funktion verkettet das Ganze nun mit der 21.
Dadurch liefert uns die Formel als Ergebnis Artikelstamm!B2:B21.Da dies so noch nicht ausgewertet werden kann, setzen wir um die Ganze Funktion noch Klammern und die Funktion Indirekt. Durch Indirekt wird es Excel nun möglich das Ergebnis auszuwerten und unser Bereich ist dynamisch und passt sich automatisch an, wenn im Tabellenblatt Artikelstamm ein neuer Artikel hinzukommt.

=Indirekt(Artikelstamm!B2:B21)

Wechseln Sie nun in das Tabellenblatt Zugang. Dort finden Sie die Überschriften Datum, Artikel und Menge. In Spalte B (Artikel) soll die Eingabe des jeweiligen Artikels nur über ein Dropdown möglich sein um eventuelle Fehleingaben (die zu Fehlern bei der Auswertung führen) zu vermeiden. Um dieses Dropdown zu erstellen markieren Sie den Bereich von B2 bis Bx (Bx können Sie soweit wie benötigt setzen – Sie können diesen Bereich jedoch jederzeit erweitern).
Über Menüleiste Daten – Gültigkeit – Liste erzeugen wir jetzt unsere Dropdownfelder.

Dieses Vorgehen wiederholen Sie nun für Ihr Tabellenblatt Abgang. Nun stehen Ihnen in den beiden Tabellenblättern Zugang und Abgang in der Spalte B die Dropdowns zur Verfügung.

Auswertung aktueller Bestand im Tabellenblatt Artikelstamm

Um den aktuellen Bestand zu ermitteln orientieren wir uns zunächst an der allgemein gültigen Formel:

Aktueller Bestand = Angangsbestand + Summe aller Zugänge – Summe aller Abgänge

Diese allgemeine Formel setzen wir nun in unserem Tabellenblatt Artikelstamm in Excel um.
In Ihrem Tabellenblatt Artikelstamm gehen Sie in die Zelle D2 (aktueller Bestand).

=C2+SUMMEWENN(Zugang!B:B;Artikelstamm!B2;Zugang!C:C)-SUMMEWENN(Abgang!B:B;Artikelstamm!B2;Abgang!C:C)

Diese Formel setzt nun unsere allgemein gültige Formel für den aktuellen Bestand in Excelbezüge um und Sie können diese nun soweit wie benötigt nach unten kopieren.
Ihr Tabellenblatt „Artikelstamm“ liefert Ihnen nun immer die aktuellen Bestände auf Basis der Zugänge und Abgänge.
Nun erweitern wir noch unsere Tabelle Artikelstamm um ein Feld „Mindestbestand“. und das Feld „Bestellen“. Wir geben in Spalte E Mindestbestände vor und die Spalte F „Bestellen“ setzt uns ein X wenn der Mindestbestand erreicht oder unterschritten wird. 
Dazu schreiben wir nachstehende Formel in F2

=WENN(D2<=E2;"X";"")

Diese Formel können wir nun soweit wie benötigt nach unten kopieren.
Wir können nun noch unsere Artikelstammliste mit einem Autofilter versehen und so bequem auch nach verschiedenen Kriterien filtern.

Unser abschliessender Schritt wird sein, daß wir die Artikelliste mit einer bedingten Formatierung versehen. Diese Formatierung soll erreichen, daß uns die Zeilen die in Spalte F ein X enthalten (Mindestbestand wurde erreicht oder unterschritten) farblich hervorgehoben werden.

Dazu markieren wir den Bereich von A2 bis F1000 (Bereich kann natürlich beliebig erweitert werden).

Wählen Sie in der Menüleiste Format – Bedingte Formatierung. Stellen Sie das Dropdown von Zellwert ist auf Formel ist um. Nun geben Sie nachstehende Formel ein und wählen über den Schalter „Format“ noch das gewünschte Muster aus.

=$F2=“X“

Bestätigen Sie das Ganze noch mit OK und schon werden in Ihrer Tabelle die entsprechenden Zeilen farbig hervorgehoben.

Selbstverständlich lässt sich dieses kleine Beispiel noch ausbauen und erweitern. Um detaillierte Auswertungen zu bekommen empfiehlt es sich auch, sich mit dem Thema Pivottabellen zu befassen. Zum Thema Pivottabellen finden Sie auf www.xlfaq.de und www.excel-pivot.de weitergehende Informationen, Tutorials und Beispieldateien.