Sie möchten ein Rechnungsformular in Excel erstellen?
Auf Basis einer Kundenliste sollen Rechnungen erstellt werden. Die Kunden haben alle eine eindeutige Kundennummer. Nach Eingabe dieser Kundennummer soll Excel automatisch die dazugehörigen Namen und Adressen auslesen. Dasselbe soll bei den Artikeln der Fall sein. Auch hier gibt es eine Artikelliste mit eindeutigen Artikelnummern. Um das Ganze nachvollziehen zu können, benutzen Sie am besten die nachstehenden zum Download verfügbaren Dateien.
Erweiterung der AufgabeDropdowns, Dynamischer Listenbereich durch Namen Suchbereich dynamisch gestalten zur Verfügung stehende Dateien:
Syntax des Sverweises:
=SVERWEIS(Suchkriterium;Suchbereich;Spaltenindex; Bereich_Verweis)
Suchkriterium ist der Wert, der in der ersten Spalte des Suchbereichs gefunden werden soll. In unserem Fall also die Kundennummer in Spalte A. Suchbereich ist der Bereich, in dem die Informationen zu dem jeweiligen Suchkriterium stehen. Achten Sie dabei immer darauf den Suchbereich absolut zu setzen (in Dollar-Zeichen) und mit der Spalte zu beginnen in der sich das Suchkriterium befindet. In unserem Fall also lautet der Suchbereich $A$1:$J$102 (wir werden später diesen Bereich noch dynamisch gestalten um neu hinzukommende Datensätze mit abgreifen zu können.) Spaltenindex ist die Spaltennummer im Suchbereich, aus der der entsprechende Wert zurückgegeben werden muss. Ein Spaltenindex von 1 gibt den Wert der ersten Spalte im Suchbereich zurück; ein Spaltenindex von 2 gibt den Wert der zweiten Spalte des Suchbereichs zurück usw. Wenn Spaltenindex kleiner 1 ist, gibt SVERWEIS den Fehler #WERT! zurück; wenn Spaltenindex größer als die Anzahl der Spalten im Suchbereich ist, gibt SVERWEIS den Fehlerwert #BEZUG! zurück. Statt Suchbereich finden Sie häufig auch den Begriff Matrix. Bereich_Verweis ist ein logischer Wert, der angibt, ob SVERWEIS eine genaue oder eine ungefähre Entsprechung suchen soll. Wenn dieser Parameter WAHR ist oder weggelassen wird, wird eine ungefähre Entsprechung zurückgegeben. Anders ausgedrückt, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist, wenn keine genaue Entsprechung gefunden wird. Wenn der Parameter FALSCH ist, sucht SVERWEIS eine genaue Entsprechung. Wird keine Entsprechung gefunden, wird der Fehlerwert #NV zurückgegeben. In unserem Fall arbeiten wir also mit dem Parameter FALSCH, da wir eine exakte Übereinstimmung (Kundennummer) benötigen. Um die nachstehenden Schritte besser nachvollziehen zu können ist es am besten, wenn Sie sich die Musterdatei herunterladen und die Schritte dort nachvollziehen. http://www.xlfaq.de/downloads/sverweis_rechnungsformular.zip
In dieser Musterdatei befinden sich die Tabellenblätter Kundenstamm, RG und RG Mit Namen. Im ersten Teil befassen wir uns nur mit dem Kundenstamm und der Tabelle RG (Rechnungsvordruck).
In der Tabelle RG sollen in der Zelle B11 über ein Dropdown (Daten-Gültigkeit-Liste) die Kundennummern gewählt werden können. Nach Auswahl der Kundennummer füllt Excel automatisch über SVERWEIS die Adressfelder auf Basis der Datenbank in der Tabelle Kundenstamm. Dazu erstellen wir nun also zunächst das Dropdown für Zelle B11.
 Da sich unsere Ursprungsdaten für das Dropdown nicht in derselben Tabelle befinden, müssen wir in diesem Fall Excel ein wenig „austricksen“ und mit der Vergabe von Namen arbeiten. Dazu markieren Sie in Ihrer Tabelle „Kundenstamm“ den Bereich von A2 bis A102 – wählen Sie nun in der Menüleiste „Namen definieren“ und geben diesem Bereich den Namen Kundenstamm. Nun wechseln Sie in Ihre Tabelle RG und wählen die Zelle B11 aus, die das Dropdown bekommen soll. Über „Daten - Gültigkeit – Liste“ geben Sie nun bei Quelle ein: =Kundenstamm
Schon haben Sie Ihr Dropdown zur Auswahl der Kundennummer erzeugt. Nun erstellen wir die Formeln für die Zellen, die auf Basis der Kundennummer (Suchkriterium) im Kundenstamm (Suchbereich) die jeweils benötigten Adressdaten ermitteln sollen. Wir beginnen in der Zelle B13 um die Anrede des Kunden zu ermitteln. Zuerst schauen Sie sich nochmals den Sverweis an, um dann entsprechend die einzelnen Operanden der Syntax einzugeben. =SVERWEIS(Suchkriterium;Suchbereich;Spaltenindex; Bereich_Verweis)
Zelle B13: =SVERWEIS($B$11;Kundenstamm!A:J;10;FALSCH) Unser Suchkriterium (die Kundennummer) steht in Zelle B13. Der Suchbereich sind die Spalten A bis J der Tabelle Kundenstamm. Da sich die Anrede in der 10. Spalte unseres Suchbereichs befindet ist unser Spaltenindex 10. Nun setzen wir „Bereich_Verweis“ noch auf FALSCH da wir einen eindeutigen Treffer zu unserem Suchkriterium benötigen. Damit ist die Formel auch schon fertig und nach Auswahl der Kundennummer erscheint die dazugehörige Anrede. Im Feld „Name“ wollen wir nun statt eines einfachen Sverweis Name und Vorname in einer Zelle verketten. Das erreichen wir in dem wir 2 Sverweise durch das kaufmännische &-Zeichen verketten. Außerdem fügen wir zwischen die beiden Namen ein Leerzeichen ein. In der Zelle B14 steht nun also nachstehende Formel: =SVERWEIS(B11;Kundenstamm!A:J;2;FALSCH)& " " &SVERWEIS(B11;Kundenstamm!A:J;3;FALSCH) Die Formeln für Straße, Postleitzahl und Ort können Sie nun sicherlich selbst erstellen. Noch nicht genug geübt ? -J Na denn, …. bauen Sie doch einfach Ihr Rechnungsformular dahingehend aus, daß Sie die Artikel ebenso über Dropdown auswählen können und Excel Ihnen über Sverweis und andere Funktionen das Rechnungsformular entsprechend füllt. Eine Tabelle mit Demoartikeldaten finden Sie bereits in der Demodatei.
Wie versprochen möchte ich Ihnen nachstehend nun noch einige zusätzliche Features aufzeigen, die Ihre Datei ein wenig komfortabler und dynamisch gestaltet. Im obigen Beispiel haben wir ein Dropdown für die Kundennummer erzeugt. Was aber, wenn Ihre Kundenliste (hoffentlich) anwächst und ständig neue Kunden hinzukommen? Um diesen Bereich automatisch zu erweitern, werden wir den Bereich für das Dropdown dynamisch halten und auch den Suchbereich (der ja ebenso grösser wird wenn neue Kunden hinzukommen) ebenfalls dynamisch halten. Beginnen wir also zunächst damit, das Dropdown in Zelle B11 unserer Tabelle „RG_mit_Namen“ dynamisch zu machen. Dazu arbeiten wir wiederum mit Namen. Wählen Sie dazu in der Menüleiste „Einfügen - Namen – definieren“ und vergeben den Namen “Kundenstamm“. Bei bezieht sich auf geben Sie nachstehende Formel ein: =INDIREKT("Kundenstamm!A2:A"&ANZAHL2(Kundenstamm!$A:$A))
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 Kunden in ein gesondertes Tabellenblatt „Kundenstamm“ 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 Kundenstammdaten befinden. Wir setzen also über nachstehende Funktion den Bereich zusammen. =INDIREKT("Kundenstamm!A2:A"&ANZAHL2(Kundenstamm!$A:$A)) Über „Anzahl2“ wird ermittelt, bis zu welcher Zeile der Spalte A im Tabellenblatt Artikelstamm die Einträge gehen. In unserem Fall liefert ANZAHL2(Kundenstamm!$A:$A) uns die Zahl 102. Bis zur Zeile 102 stehen Einträge in Spalte A. Über Kundenstamm!A2:A"& setzen wir den Startwert auf A2 (A1 enthält unsere Überschrift) und das &-Zeichen vor der Anzahl2-Funktion verkettet das ganze nun mit der 102. Dadurch liefert uns die Formel als Ergebnis Kundenstamm!A2:A102.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(Kundenstamm!A2:A102)
Wechseln Sie nun in das Tabellenblatt „RG_mit_Namen“. Im Feld B11 erzeugen Sie nun wieder über „Daten – Gültigkeit – Liste“ das Dropdown zur Auswahl der Kundennummern.
Um nachher in unseren Formeln statt mit Bezügen mit aussagefähigen Namen zu arbeiten, vergeben wir für die Zelle B11 der Tabelle „RG_mit_Namen“ den Namen KDNR für Kundennummer. Also auch hier wieder „Menüleiste – Einfügen – Namen – definieren“
Nun werden wir noch einen Namen für unseren Suchbereich vergeben und auch diesen dynamisch gestalten. Wechseln Sie in Ihr Tabellenblatt „Kundenstamm“ und vergeben einen Namen. Mit der Formel =BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1)) gestalten wir nun den Suchbereich sowohl in der Länge (Zeilenzahl) als auch in der Breite (Spaltenzahl) dynamisch.
Was wird mit dieser Formel erreicht? Mit ANZAHL2(A:$A) ermitteln wir die Anzahl gefüllter Zellen in der Spalte A der Ursprungsliste. Mit ANZAHL2($1:$1) ermitteln wir die Anzahl gefüllte Zellen in der Zeile 1 der Ursprungsliste. Mit BEREICH.VERSCHIEBEN($A$1;;;…;…) geben wir einen Bereich zurück, der bei A1 beginnt und so viele Zeilen/Spalten besitzt, wie wir als gefüllt ermittelt haben. Dadurch haben wir den Bereich so aufbereitet, dass Ihr Suchbereich neu hinzukommende Datensätze automatisch übernimmt und auch neue Feldüberschriften (z.B. Umsätze) erkennt. Nach diesen Vorarbeiten können wir nun die von uns vergebenen Namen in den Formeln benutzen. Unser Sverweis in Zelle B13 des Tabellenblattes RG_mit_Namen sieht nun folgendermaßen aus: =SVERWEIS(KDNR;Suchbereich;10;FALSCH) Dadurch erreichen wir, daß die Formel statt abstrakten Bezügen sprechende Namen erhalten hat. Selbstverständlich lässt sich dies auch auf Ihre Formeln für die Artikel erweitern.
|
