Zusammenfassung
Dieses Excel-Toolpraktikum vertieft die Anwendung fortgeschrittener Excel-Funktionen für betriebswirtschaftliche Berechnungen. Behandelt werden die dynamische Noten- und Notenstufenberechnung mittels
SVERWEIS und verschachtelten WENN-Bedingungen, die Automatisierung eines Rechnungserstellungssystems (Artikeldatenabfrage, Mengenrabattstaffelung und Rundungsfunktionen) sowie die Optimierung von Lagerprozessen (Bestellmengenberechnung bei Meldebestandsunterschreitung). Abschließend werden komplexe Artikellisten mittels bedingter Aggregationen (ZÄHLENWENN, ZÄHLENWENNS und SUMMEWENNS) ausgewertet.Schlüsselbegriffe
SVERWEIS
Eine Suchfunktion in Excel, die senkrecht nach einem Wert in der ersten Spalte einer Matrix sucht und den Wert einer definierten Zelle in derselben Zeile zurückgibt.
Verschachteltes WENN
Die Kombination mehrerer WENN-Funktionen ineinander, um mehr als zwei Entscheidungszweige (z. B. Notenstufen A, B, C, D) abzubilden.
Meldebestand
Der Lagerbestand, bei dessen Erreichen oder Unterschreiten eine neue Bestellung ausgelöst werden muss, um Lieferengpässe zu vermeiden.
Höchstbestand
Die maximale Menge eines Artikels, die im Lager gelagert werden darf bzw. soll (Obergrenze beim Auffüllen).
ZÄHLENWENNS
Zählt die Anzahl der Zellen in einem Bereich, die mehrere definierte Kriterien gleichzeitig erfüllen.
SUMMEWENNS
Summiert die Werte in einem Bereich, die mehrere definierte Kriterien gleichzeitig erfüllen.
Kernkonzepte
1. Aufgabe 4 – Notenliste und Notenstufen
Es soll eine Notenliste mit den Prüfungsergebnissen von Studierenden vervollständigt werden:
-
Schritt 1: Notenermittlung mit SVERWEIS: Die Note wird anhand der erreichten Punktzahl ermittelt. Der Suchwert ist die Punktzahl, die Suchmatrix ist die Tabelle
Notenskala(Spalten A bis C, absolut referenziertNotenskala!$A$4:$C$28).- Formel:
=SVERWEIS(C2; Notenskala!$A$4:$C$28; 3; WAHR) - Hinweis: Der vierte Parameter ist
WAHR, da eine ungefähre Übereinstimmung gesucht wird (Punktebereiche von-bis).
- Formel:
-
Schritt 2: Notenstufen mit verschachteltem WENN: Die Zuordnung der Notenstufe (z. B. "sehr gut", "gut", "befriedigend", "ausreichend", "nicht bestanden") erfolgt basierend auf der ermittelten Note in Spalte D.
- Formel:
=WENN(D2="n.b."; "nicht bestanden"; WENN(D2<=1,5; "sehr gut"; WENN(D2<=2,5; "gut"; WENN(D2<=3,5; "befriedigend"; "ausreichend"))))
- Formel:
2. Aufgabe 5 – Rechnungserstellung (BWLIS-GmbH)
Die automatische Erstellung von Rechnungen soll über die Verknüpfung vorhandener Stammdaten realisiert werden:
- Artikelbeschreibung & Preis ermitteln:
Suche nach der Artikelbeschreibung und dem Preis in der Tabelle
6 – Artikellisteanhand der Artikelnummer (Suchwert in Spalte A).- Artikelbeschreibung:
=SVERWEIS(A12; Artikelliste!$A$18:$L$38; 5; FALSCH)(Spaltenindex 5) - Preis:
=SVERWEIS(A12; Artikelliste!$A$18:$L$38; 7; FALSCH)(Spaltenindex 7, exakte Suche mitFALSCH)
- Artikelbeschreibung:
- Rabattstaffel berechnen:
Der Rabatt richtet sich nach der bestellten Menge. Die Rabattstaffel ist wie folgt definiert:
- 0 bis 4 Stück: $0%$
- 5 bis 9 Stück: $1,5%$
- 10 bis 14 Stück: $2,0%$
- 15 bis 19 Stück: $2,5%$
- 20 Stück und mehr: $3,0%$
- Rabatt-Formel:
=SVERWEIS(C12; Rabattstaffel!$A$5:$D$9; 4; WAHR)(Intervallsuche)
- Berechnung der Preise:
- Einzelpreis abzüglich Rabatt:
=RUNDEN(E12 * (1 - F12); 2)(gerundet auf 2 Dezimalstellen). - Gesamtpreis:
=D12 * G12(Menge $\cdot$ Einzelpreis abzüglich Rabatt). - Gesamtsummen:
- Summe (netto):
=SUMME(H12:H38) - Umsatzsteuer (19%):
=RUNDEN(Summe_Netto * 0,19; 2) - Summe (brutto):
=Summe_Netto + Umsatzsteuer
- Summe (netto):
- Einzelpreis abzüglich Rabatt:
3. Aufgabe 6 – Artikelliste & Bestellsteuerung
Ergänzung der Dispositionsdaten in der Artikelliste zur automatischen Bestellabwicklung:
- Bestellmenge (Spalte K):
Eine Bestellung wird ausgelöst, wenn der Lagerbestand kleiner oder gleich dem Meldebestand ist. Der Lagerbestand wird auf den Höchstbestand aufgefüllt. Andernfalls wird nichts bestellt.
- Formel:
=WENN(H18<=J18; I18 - H18; 0) - Logik:
WENN(Lagerbestand <= Meldebestand; Höchstbestand - Lagerbestand; 0)
- Formel:
- Bestellwert (Spalte L):
Errechnet sich aus der Bestellmenge multipliziert mit dem Preis.
- Formel:
=K18 * G18
- Formel:
- Produktgruppen-Auswertungen (Zusammenfassungstabelle):
- Anzahl gelisteter Artikel (Zelle E2):
=ANZAHL2(A18:A38)(da Artikelnummern alphanumerisch sein können). - Menge Artikel pro Gruppe (Spalte E):
=ZÄHLENWENN(Artikelliste!$B$18:$B$38; Produktgruppe) - Anzahl Bestellungen pro Produktgruppe (Spalte F):
Es sollen nur Bestellungen gezählt werden, die eine Bestellmenge $> 0$ aufweisen.
- Formel:
=ZÄHLENWENNS(Artikelliste!$B$18:$B$38; Produktgruppe; Artikelliste!$K$18:$K$38; ">0")
- Formel:
- Bestellwert pro Produktgruppe (Spalte G):
Es wird der kumulierte Bestellwert pro Gruppe ermittelt, sofern tatsächlich bestellt wurde.
- Formel:
=SUMMEWENNS(Artikelliste!$L$18:$L$38; Artikelliste!$B$18:$B$38; Produktgruppe; Artikelliste!$K$18:$K$38; ">0")
- Formel:
- Anzahl gelisteter Artikel (Zelle E2):
Lernkarten
Übungsfragen
Welche Formel ordnet eine Zelle D2 (Note) korrekt in eine Notenstufe ein, wenn Noten bis 1,5 "sehr gut", bis 2,5 "gut", bis 3,5 "befriedigend" und ansonsten "ausreichend" sind?
Sie möchten die Summe der Bestellwerte für die Produktgruppe "CPU" ermitteln, aber nur, wenn die Bestellmenge größer als 0 ist. Welcher Funktionstyp ist korrekt?
Ein Artikel hat einen Lagerbestand von 15, einen Meldebestand von 20 und einen Höchstbestand von 50. Wie hoch ist die Bestellmenge nach der Formel =WENN(Lagerbestand <= Meldebestand; Höchstbestand - Lagerbestand; 0)?