Endlich mal etwas anderes als ein Webformular! In diesem Fall soll ein Bestellschein erstellt werden; mit einem pflegbaren Dropdown-Feld für die Artikel, einem von der Artikelauswahl abhängingen Größen-Dropdown und einer dynamischen Preisermittlung. Dass am Ende den pflegbaren Zellen die Sperrung entzogen und der Blattschutz aktiviert wird, sei dabei nur als Randnotiz erwähnt.
Die eigentliche Herausforderung liegt in den Listenfeldern. Als erstes wird aber eine entsprechende Matrix benötigt, in der Artikel und Preis sowie Größe notiert werden. Dies soll anschließend als Quelle für die weiteren Felder dienen. In Excel 2010 ist es möglich, diese Quelle auf einem separaten Arbeitsblatt zu notieren. In Excel 2003 erhielt ich eine Fehlermeldung, so dass hier die Daten „außerhalb“ des Bestellscheinbereichs auf dem gleichen Arbeitsblatt eingetragen und anschließend über die weiße Schriftfarbe quasi unsichtbar gezaubert wurden. So oder so ähnlich sollte die Quell-Matrix schließlich aussehen (hier auf einem separaten Arbeitsblatt „Artikel“; in den Zellen A20 bis J20 steht die Artikelbezeichnung, in den Zellen A21 bis J21 der Preis und darunter jeweils die möglichen Größen in den Zellen A22 bis J 29):
Das erste Dropdown-Feld mit der Auswahl möglicher Artikel ist denn auch schnell erstellt: Die entsprechende Zelle (B33) wird markiert und über das Register „Daten“ der Dialog „Datenüberprüfung“ aufgerufen. Bei den „Gültigkeitskriterien“ wird unter „Zulassen“ Liste ausgewählt und die entsprechende Quelle entweder manuell eingegeben oder per Maus ausgewählt:
=Artikel!$A$20:$J$20
Mit Klick auf „OK“ ist auch schon das erste Feld, dynamisch, erstellt und kann auf die anderen Zellen in Spalte B übertragen werden (mit Hilfe der Ausfüllfunktion). Jetzt kommen wir zu dem spannenderen Feld, der artikelabhängigen Größenangabe.
Auch für die Größe wird der Dialog „Datenüberprüfung“ genutzt, allerdings wird die dort hinterlegte Quelle etwas komplexer. Mit der INDEX-Funktion ist man gut beraten und zwar in Kombination mit VERGLEICH:
=INDEX(Artikel!$A$22:$J$29;;VERGLEICH(B33;Artikel!$A$20:$J$20;))
INDEX gibt den Wert einer angegebenen Zelle oder Matrix von Zellen zurück, also INDEX(Matrix;Zeile;Spalte). Da die Größenangaben der Artikel als Ergebnis geliefert werden soll, wird entsprechend dieser Bereich, A22 bis J29 angegeben. Eine Zeilenangabe wird nicht benötigt, wohl aber die Angabe der zurückzuliefernden Spalte. Um diesezu ermitteln wird VERGLEICH herangezogen. Suchkriterium ist der Auswahlwert aus B33, Suchmatrix ist die Zeile der Artikelbezeichnungen A20 bis J20. Erneut verteilt sich mit der Ausfüllfunktion unser Arbeitsschritt auf die übrigen Zellen der Spalte.
Kurze Verschnaufspause: Die Spalte „Anzahl“ ist ein einfaches Eingabefeld. Und dann geht es an die Ermittlung des Einzelpreises. Dieser schreit geradezu nach der WVERWEIS-Funktion:
=WVERWEIS(B33;Artikel!$A$20:$J$21;2;FALSCH)
Suchkriterium ist wieder einmal die Auswahl in Zelle B33. Als Matrixbereich wird diesmal A20 bis J21 ausgewählt, also der Bereich mit Artikelname und Preis. Die nächste Angabe ist der Zeilenindex, da der Preis zurückgegeben werden soll, steht hier die zweite Zeile der Matrix, also „2“. Die letzte optionale Angabe gibt an, ob nach einer genauen oder einer ungefähren Entsprechung des Suchkriteriums gesucht werden soll. Da ein genauer Vergleich wünschenswert ist, gibt man den Parameter „FALSCH“ an.
Einen kleinen Schönheitsmakel hat jedoch die Funktion: Ist nichts in Spalte B ausgewählt, wird eine Fehlermeldung in der Zelle angezeigt. Um dies zu verhindern, wird der WVERWEIS noch in eine schöne WENN-Funktion integriert:
=WENN(B33="";"";WVERWEIS(B33;Artikel!$A$20:$J$21;2;FALSCH))
Sprich: Wenn Zelle B33 leer und nichts ausgewählt ist, gib nichts aus, sonst führe den WVERWEIS durch. Auf die anderen Zellen der Spalte übertragen, fertig. Die letzten beiden Dinge, die getan werden müssen, sind nun beinahe Kinderkram: In der Spalte G soll der Gesamtpreis ermittelt werden, Also Anzahl multipliziert mit dem Einzelpreis. Auch hier schützt die WENN-Funktion vor unschönen Ausgaben:
=WENN(B33="";"";E33*F33)
Und schließlich die Gesamtsumme:
=SUMME(G33:G43)
Fertig ist der Bestellschein. Eine hübschere Ansicht gewährt man über „Anicht / Seitenlayout“ statt „Normal“. Bei den auszufüllenden Zellen ist nun nur noch die Sperre zu entfernen und anschließend der Blattschutz zu aktivieren.
Weitere Informationen zu den verwendeten Funktionen: