Projekt Stufe 3: Auftragsabwicklung über vier Blätter

Du brauchst: SVERWEIS mit genauer und ungefährer Suche, INDEX und VERGLEICH, WENNFEHLER und RUNDEN. Alles davon hast du in Stufe 2 und 3 schon geübt.

Die Ausgangslage

Ein Händler für Büroartikel bekommt zwei neue Bestellungen. Die Mappe hat vier Blätter:

• Bestellungen: Bestellnummer, Kunden-Nr., Artikel-Nr. und Menge. Hier rechnest du, die gelben Zellen füllst du. In B5 steht der MwSt-Satz.
• Artikel: Artikel-Nr., Bezeichnung und Nettopreis, nicht sortiert.
• Kunden: Name, Ort und Kunden-Nr. Die Nummer steht ganz rechts, weil die Liste aus einem anderen Programm kommt.
• Rabatte: die Rabattstaffel. Ab einem Bestellwert von 250 gibt es 2 %, ab 500 gibt es 3 %, ab 1000 gibt es 5 %.

Das Projekt

Worauf es ankommt

Preise immer suchen, nie fest verknüpfen

Der Toner A-104 steht auf dem Blatt Artikel in Zeile 3. =Artikel!C3 liefert heute also den richtigen Preis. Sobald jemand die Artikelliste sortiert oder einen Artikel einfügt, zeigt die Formel auf einen anderen Artikel, und die Rechnung ist falsch, ohne dass eine Fehlermeldung kommt. =SVERWEIS(D2;Artikel!A2:C7;3;FALSCH) sucht die Nummer jedes Mal neu. Die Übung prüft deshalb auch mit einer umsortierten Artikelliste.

Der Name steht links von der Nummer

SVERWEIS sucht immer in der ersten Spalte des Bereichs und gibt etwas rechts davon zurück. Auf dem Blatt Kunden steht die Kunden-Nr. aber in Spalte C, der Name in Spalte A. Mit VERGLEICH findest du die Zeile der Nummer, INDEX holt aus dieser Zeile den Namen: =INDEX(Kunden!A2:A6;VERGLEICH(B2;Kunden!C2:C6;0)). Die 0 am Ende von VERGLEICH bedeutet genaue Suche.

Die Rabattstaffel mit ungefährer Suche

Ein Betrag von 774 steht nicht in der Staffel. Gesucht ist die letzte Stufe, deren Untergrenze nicht größer ist als der Betrag, hier 500. Genau das macht SVERWEIS mit WAHR als viertem Argument: =SVERWEIS(G2;Rabatte!A2:B5;2;WAHR). Dafür muss die erste Spalte der Staffel aufsteigend sortiert sein und mit 0 beginnen, sonst gibt es für kleine Beträge #NV.

Brutto in einem Schritt

Der Rabatt mindert den Nettobetrag, die Mehrwertsteuer kommt auf den reduzierten Betrag. G2*(1-H2) ist der Betrag nach Rabatt, *(1+B5) schlägt 19 % MwSt auf. RUNDEN auf 2 Stellen sorgt dafür, dass in der Zelle der Betrag in Cent steht, der auch auf der Rechnung erscheint, und nicht nur so formatiert ist. Den Satz holst du aus B5, damit eine Bestellung mit ermäßigtem Satz von 7 % nur eine geänderte Zelle braucht.

Fehlende Artikel sichtbar machen

Die Artikel-Nr. A-109 aus Bestellung B-1002 steht nicht in der Artikelliste. Ein einfacher SVERWEIS zeigt #NV, und jede Formel, die damit weiterrechnet, auch. WENNFEHLER fängt den Fehler ab und schreibt stattdessen „Artikel fehlt“. So sieht die Sachbearbeitung sofort, dass die Liste ergänzt werden muss. Wird der Artikel später angelegt, steht in F3 automatisch der Preis.

Zwischenrechnungen

Wie in Excel darfst du freie Zellen für eigene Zwischenschritte nutzen, etwa den Betrag nach Rabatt in eine Hilfszelle schreiben und in I2 darauf verweisen. Geprüft werden nur die Ergebnisse in den gelben Zellen.

Wenn es nicht klappt

• Die Meldung nennt die erste Zelle, die noch nicht stimmt. Alles davor ist richtig.
• Zeigt F2 einen falschen Preis, fehlt wahrscheinlich FALSCH am Ende von SVERWEIS. Die Artikelliste ist nicht sortiert, die ungefähre Suche findet dann irgendeinen Artikel.
• Zeigt H2 #NV, steht im Rabatt-SVERWEIS FALSCH statt WAHR. 774 kommt in der Staffel nicht vor.
• Weicht I2 nur hinter dem Komma ab, fehlt RUNDEN. Ist I2 deutlich zu hoch, wurde der Rabatt nicht abgezogen oder 1+B5 nicht in Klammern gesetzt.
• Heißt es bei F3, das Ergebnis stimme nur mit diesen Daten, hast du „Artikel fehlt“ fest eingetippt. Die Formel muss den Preis liefern, sobald der Artikel in der Liste steht.

Häufige Fragen