Hinweise zur Tabelle SVerweis()

Die Tabelle bzw. der Inhalt bedarf einiger erklärender Worte und Hinweise. Diese finden Sie hier:

  • In Spalte A und Spalte B stehen die Noten in numerischer Form und als Zeugnis-Text (Zeugnisnote).
  • A4 ist für den Fall gedacht, dass einmal eine Note besser sein kann als 1. Es wird also dafür gesorgt, dass in jedem Fall eine Zahl gefunden wird, die kleiner ist als der gesuchte Wert.
  • In B4 könnte natürlich auch (nur) „Sehr gut“ stehen, denn eigentlich gibt es die hier gezeigte Benotung ja nicht in Zeugnissen. Hier dient es in erster Linie der besseren Unterscheidung und als Hinweisgeber.
  • Der Notenschnitt (Spalte F) ist hier per Hand eingegeben worden, es kann aber durchaus auch ein berechneter Wert sein.
  • G4: Hier wurde die Funktion exakt eingetragen: =SVERWEIS(F4; $A$4:$B$10; 2). Die Erklärung dazu:
    • Im ersten Argument F4 steht der Wert der Zeugnisnote, welcher dann in den Notentext (z.B. „Gut“) umgewandelt werden soll.
    • Das zweite Argument $A$4:$B$10 stellt die Matrix dar, wo in der ersten Spalte nach dem ersten Argument gesucht werden soll.
    • Im dritten Argument 2 wird festgelegt, dass der Wert aus der zweiten Spalte der SuchMatrix zurück gegeben werden soll.
  • In G5 wird einerseits bewiesen, dass die Excel-Hilfe nicht immer Recht hat: Die Matrix muss nicht mindestens 2 Spalten haben, es reicht auch eine  😛 . – Andererseits dient diese Spalte hier auch zu Kontrollzwecken, denn es werden schnell Fehler aufgezeigt; weiterhin kann der Fehlerwert auch einen Hinweis auf die Fehlerart geben.
  • F5 ist mit der Note 0,7 besser als 1. Technisch kaum möglich aber in anderen Situationen der Verwendung dieser Funktion denkbar. Auf jeden Fall kann auf die „Notlösung“ Null zurück gegriffen werden.
  • Die Formel in G5 ist wie auch die Formeln darunter einfach von G4 herunter kopiert worden. Das funktioniert auch, weil die SuchMatrix per $-Zeichen zu einem absoluten Bereich gemacht worden ist und beim Kopieren entsprechend nicht angepasst wurde.
  • Die Formel in H5 wurde ebenfalls von H4 herunter kopiert. Allerdings zeigt sich hier, dass eine nicht absolute (also eine relative) Adressierung hier zu Fehlern führt. – In der Formel steht (dank Anpassung): =SVERWEIS(F5; A5:A11; 1)… was in diesem Fall zum Fehlerwert #NV führt. Hier die Erklärung dafür:
    • Der Wert aus F5 (0,7) soll im Matrix-Bereich A5:A11 gesucht werden.
    • Der kleinste Wert dieser Matrix wäre zwar 0 (in A11, wo Excel wegen des Leerinhalts eine Null annimmt), aber da die Werte in aufsteigender Reihenfolge sortiert sein müssen, ist (im benannten Bereich) von oben aus gesehen die 1 der kleinste Wert. Und da 0,7 kleiner ist als der kleinste Wert, kommt es zu der Fehlermeldung.
    • Wenn Sie die Notenschnitte von Alexander und Beate tauschen, wird es naturgemäß keine Fehlermeldung geben, da die Formel in H4 (noch) korrekt ist und in H5 nun ja nach dem Wert 2,3 gesucht werden würde, welcher ja auch von oben nach unten gesehen an zweiter Position gefunden wird.
  • Cäsar hat mit einem Notenschnitt von 1,83 eigentlich eine glatte 2 verdient, aber in G6 steht Sehr gut. – Auch das ist der SVERWEIS-Logik geschuldet. Gesucht wird 1,83; der Wert wird nicht gefunden, also wird als Fundposition der nächst tiefere Wert angenommen, und der steht mit dem Wert 1 in Zeile 3. Und das entspricht nun einmal einem „Sehr gut“. (Die Lösung folgt weiter unten.)
  • H6 ist aus den gleichen Gründen wie H5 mit einem #NV gefüllt.
  • Diana ist bei einem Schnitt von 3,5 mit einem „Befriedigend“ aus den gleichen Gründen wie vor zu gut bedient. Aber in Spalte H steht hier erstaunlicherweise kein Fehlerwert sondern die zum Notentext passende numerische Note. Das liegt hier daran, dass 3,5 größer ist als die kleinste Zahl in der SuchMatrix Spalte A und somit ein kleinerer passender Wert gefunden wird. Schwierig, ich weiß …
  • Auch Erich hat einen Notenschnitt von 3,5 – hier ist aber alles korrekt. Das liegt in diesem Fall daran, dass die Formel in Spalte G
    =SVERWEIS(RUNDEN(F8; 0); $A$4:$B$10; 2)
    von mir geändert wurde. Der Wert aus Spalte F (hier: 3,5) wurde per RUNDEN() auf eine Zahl ohne Nachkommastellen  geändert, als Ergebnis kommt dann (natürlich) 4 heraus. Und das entspricht dann der korrekten Zeugnisnote Ausreichend.
  • In der Zelle H6 wurde dann auch die korrekte Adressierung verwendet, die absolute Form. Dadurch wird der gewünschte Matrix-Bereich durchsucht. – Obwohl es hier in der Demo-Mappe die Ausnahme ist, sollte es (natürlich) der Standard sein. Hier geht es ja in erster Linie darum, Fehlermöglichkeiten aufzuzeigen.
  • Franziska hat offensichtlich die ganze Zeit gefehlt und darum in F9 nur stehen. Das ist aber Text und der kann naturgemäß nicht in Spalte A gefunden werden. Darum in G9 der Fehlerwert #NV. – Der Fehlerwert in H9 sagt ja auch ganz deutlich, dass (für RUNDEN) ein numerischer Wert erwartet wird und kein Text.
  • Auf den ersten Blick rätselhaft scheint die Zeugnisnote für Günther zu sein. Steht doch in Spalte F nichts drin. Aber wie bereits weiter oben erwähnt, rechnet Excel eine Zelle ohne Inhalt mit Null, und dieser Wert wird (wegen der absoluten Adressierung) durchaus gefunden. Und das wird durch den numerischen Wert in H8 auch klar …
  • Bei Hilda scheint das Ergebnis verwirrend zu sein. Das ist doch gleich wie bei Günther, oder? Nein, denn F10 scheint nur leer zu sein. Aber genau wie bei Franziska steht ein Text in der Zelle, auch wenn die Zeichenkette die Länge null hat. Tückisch, so etwas. Schauen Sie sich im Zweifel also genau die Inhalte der auszuwertenden Zellen in der Eingabezeile/Editierzeile an, dann ist meist mehr Klarheit gegeben. Hier ist die Formel nämlich ="", also Text. Solch ein Konstrukt wird auch Leerstring genannt:

Leerstring als Fehler-Ursache

Leerstring als Fehler-Ursache

Ergänzend sei hier angemerkt, dass Sie einmal die Formel in H4 so anpassen sollten, dass mittels $-Zeichen eine absolute Adressierung gegeben ist. Diese Formel dann einfach nach unten kopieren. Dann sind die meisten Fehlermeldungen weg. 😉  

▲ nach oben …

Dieser Beitrag wurde unter Ohne Makro/VBA, Tabelle und Zelle, Verschiedenes veröffentlicht. Setze ein Lesezeichen auf den Permalink.