Zusätzliche Erklärungen zum Tabellenblatt WVERWEIS()
Das Tabellenblatt bzw. der Inhalt bedarf einiger erklärender Worte und Hinweise. Diese finden Sie hier:
- In Zeile 1 stehen die Monate des Jahres als Text. Auch wenn die kalendarische Reihenfolge aufsteigend ist, so ist die alphabetische Reihenfolge nicht gegeben. Februar ist wegen des F kleiner als Januar, welcher ja nun einmal mit J beginnt.
- In den Zeilen 2:7 sind in Spalte B die Produktnamen aufgeführt, in den Spalten C:N die Umsätze des jeweiligen Produkts.
So weit ist das eine ganz normale Tabelle. Die Aufgabe ist es nun, zu einem Monat und einem Produkt den entsprechenden Umsatz zu finden. – Zugegeben, es gibt andere Methoden, die hier vielleicht besser zum Ziel führen würden, aber für den WVERWEIS ist diese Anordnung einigermaßen übersichtlich und auch geeignet.
- In B11 wird der Monat, für den der Umsatz gesucht werden soll, als Text in genau der Form eingegeben, wie er in der ersten Zeile steht.
- In B12 und auch als Alternative in B13 geben Sie jene Zahl ein, die vor dem Produkt in runden Klammern steht. Obwohl die Ziffer in Spalte B ein Text ist, gehört in B12 einen numerischen Wert.
- In der Zelle C12 wird zur Kontrolle der komplette Inhalt jener Zelle zurück gegeben, in welcher der Produktname steht. Dazu wird die Funktion SVERWEIS (ja, mit S) verwendet. Folgende Formel führt hier zum Erfolg:
=SVERWEIS(„(„ & B12 & ”)*”; B$2:B$7; 1; FALSCH) - Zugegeben, etwas verwirrend. Darum „zerpflücke” ich die Formel einmal in ihre Einzelteile …
- SVERWEIS ist als Funktion hier beschrieben. Es wird senkrecht ein als Argument übergebener Wert gesucht.
- Im ersten Argument „(„ & B12 & ”)*” wird ja der SuchBegriff gefordert. Das ist hier ein zusammengesetzter Ausdruck. Warum zusammengesetzt? Nun, gegeben ist in B12 die Nummer des Produkts, also 1 .. 6. Aber es muss nach (1) .. (6) gesucht werden, also mit den runden Klammern drumherum. Und danach steht dann ja auch noch etwas, der eigentliche Name des Produkts. Von links nach rechts sieht das dann so aus: ( plus Inhalt der Zelle B12 plus ) plus *, also beliebige Anzahl von Zeichen beliebiger Art.
- Das zweite Argument ist ja der SuchBereich, hier B$2:B$7. Dort wird dann der zu findende Wert von oben nach unten in der einzigen Spalte gesucht.
- Mit 1 als drittem Argument wird die erste (ja, auch einzige) Spalte des SuchBereichs für die Rückgabe des Ergebnisses festgelegt.
- FALSCH ist das vierte Argument, welches hier zwingend erforderlich ist. Nur durch diesen Wert ist es möglich, dass das Sternchen * als Joker für beliebige Zeichen verwendet werden kann. Der Nebeneffekt, dass der SuchBereich nicht aufsteigend sortiert sein muss, ist hier nicht relevant.
- In Zelle D12 kommt nun auch der WVERWEIS() zum Einsatz. Mit der Formel =WVERWEIS($B$11; $C$1:$N$7; B12+1; FALSCH) wird dann der korrekte Umsatz zurück gegeben. Auch hier sollen die Einzelheiten der Formel betrachtet werden:
- WVERWEIS ist wohl klar, denn darum dreht es sich ja hier. 🙂
- In $B$11 steht der Monat als Text, für diesen Zeitraum sollen die Daten für das entsprechende Produkt gefunden werden. Das ist also das erste Argument, der SuchBegriff.
- Das zweite Argument ist die SuchMatrix, also der Bereich, wo gesucht werden soll. Das ist hier (natürlich) $C$1:$N$7. Die Suche muss in der obersten Zeile mit den Monatsnamen beginnen, da diese ja ein wichtiger, eigentlich der wichtigste Teil des WVERWEIS sind.
- Irritiert Sie die Berechnung im dritten Argument, dem ZeilenIndex? Na ja, in B12 steht ja die Nummer des Produkts. Aber die Zeilennummer ist hier immer genau um den Wert 1 höher. Darum gibt B12+1 die gewünschte Zeilennummer, genauer gesagt den ZeilenIndex (die nte Zeile) zurück.
- FALSCH ist richtig. Denn wegen des Jokerzeichens * im ersten Argument muss das so sein. Ein WAHR bzw. gar keine Angabe dieses Arguments würde zu fehlerhaften Ergebnissen führen.
- In Zeile 13 ist die prinzipielle Logik gleich. Nur C13 ist mit einem kleinen Trick so gestaltet, dass dort nur der reine Produktname erscheint, ohne die Hilfs-Nummerierung. Mit etwas mehr oder weniger Überlegung werden Sie gewiss herausfinden, wie das funktioniert. Zur Teil() – Funktion finden Sie hier Hilfe.
- Beginnend in Zeile 21 finden Sie noch einmal die obige Tabelle. Und obwohl auch die absoluten Bezüge in den Formeln angepasst worden sind, kommt es in D32 und D33 zur Fehlermeldung #NV. Irritierend …
Einen ersten Hinweis auf die Fehlerursache können Sie unter Umständen schon sehen: Die Monatsnamen sind rechtsbündig und nicht links ausgerichtet. Und wenn Sie beispielsweise in C21 Klicken, werden Sie gewiss rasch erkennen, wo „der Hund begraben liegt” 😀 :
Der Monatsname ist ein „echtes” Datum …
Statt eines Textes steht dort ein Datum, welches so formatiert ist, dass nur der Monat angezeigt wird. Sie als Anwender sehen zwar den Text, Excel „sieht” aber nur das Datum, und unter diesen Umständen ist zwar der 1.3.2013 aber nicht „März” durch die Funktion zu finden. – Ganz tückisch wäre es dann noch, wenn die Ausrichtung des so formatierten Datums linksbündig ist. Da wird die Fehlersuche schon zur Detektivarbeit.
Apropos Detektiv: In früheren Versionen des Excel hatte die Formelüberwachung den Namen Detektiv. Und das ist auch eine von mehreren Möglichkeiten, Fehlern auf die Schliche zu kommen. Markieren Sie dazu die Zelle mit der Fehlermeldung, hier beispielsweise D32. Dann im Menü Formeln bei der Formelüberwachung bei der Fehlerüberprüfung den Unterpunkt Spur zum Fehler auswählen:
Der erste Schritt zur Fehlersuche
Ruck zuck wird Ihnen zur betroffenen Zelle mit Pfeilen angezeigt, wo die Daten herkommen und wo wahrscheinlich die Fehlerursache liegt:
Hilfestellung in Sachen Fehlersuche
Der Ursprung des dicken Pfeils ist hier auf die Zelle C21, welche ja die Ankerzelle für den blau eingerahmten Bereich ist. Dort ist dann auch der Fehler zu suchen. Mehr dazu im Beitrag Fehlersuche, wo auch dieses Beispiel für die Fehleranalyse genutzt wird.
- [NachObenLetzte Verweis=„Fn: Hinweise zur Tabelle WVerweis()”]