Suchfunktionen

Der S-Verweis

Diese Möglichkeit hilft beim automatischen Ausfüllen von Zelleninhalten: Ein Tabellenbereich kopiert sich mit Hilfe eines Suchwertes automatisch Inhalte von einem zweiten Tabellenbereich.

Beispiel

Bei einer Trainingseinheit können maximal 100 Punkte erreicht werden. Jedem Teilnehmer ist entsprechend seiner erreichten Punktzahl eine von vier Bemerkungen mitzuteilen:

Von 0-49 Punkte: Trainingseinheit wiederholen
Von 50-69 Punkte: Fehler korrigieren
Von 70-79 Punkte: Alles ok!
Ab 80 Punkte: Prima!

Die Funktion

Der Funktionsname lautet SVERWEIS (senkrechter Verweis: senkrecht in einer Tabelle nach einem Ergebnis suchen).

Der Text in Spalte C wird entsprechend der erreichten Punkte (Spalte B) aus dem Tabellenbereich der Spalten E und F ermittelt: Die Zahl 1 als letzte Zahl in der Klammer darf auch fehlen, sie sorgt dafür, dass ein ungefährer Wert zurückgegeben wird, wenn keine 100%ige Übereinstimmung vorliegt (dazu unten mehr).
Beachte: Der Suchbereich muss dann allerdings aufsteigend sortiert sein!

Das Verfahren

Für B2 (Zelleninhalt = 60) ist die entsprechende Bemerkung zu finden.

Calc prüft Zeile 1 der Suchmatrix und findet dort den Wert 0.
0 ist kleiner oder gleich 60, diese Zeile könnte theoretisch also in Frage kommen. Calk "merkt" sich diese Zeile.

Calc prüft Zeile 2 der Suchmatrix und findet dort den Wert 50.
50 ist kleiner oder gleich 60; diese Zeile könnte theoretisch also auch in Frage kommen.
Und der Wert liegt näher am Suchwert. Damit ist die vorherige Zeile "aus dem Rennen" und Calc merkt sich nun diese Zeile.

Calc prüft Zeile 3 der Suchmatrix und findet dort den Wert 70.
70 ist allerdings NICHT kleiner oder gleich 60, also wird die letzte theoretisch in Frage kommende Zeile, also Zeile 3 als die passende Zeile erkannt. Dort steht in der zweiten Spalte das Ergebnis: „Fehler korrigieren“. Diese Bemerkung erscheint nun in C2.

Aufgabe 1

Erkläre, warum der Suchbereich aufsteigend sortiert sein muss. Konstruiere ein Beispiel, bei dem das nicht der Fall ist und zu einem Fehler führt.

Aufgabe 2

Löse die Aufgabe mit dem Funktionsassistenten.

Der W-Verweis

Der Funktionsname lautet WVERWEIS (waagerechter Verweis: horizontal in einer Tabelle nach einem Ergebnis suchen).
Diese Suchfunktion findet seltener Anwendung, weil Tabelleninhalte meist senkrecht angelegt sind. Die Syntax entspricht der des S-verweises.

Aufgabe 3

Verändere die Tabelle der letzten Aufgabe so, dass eine Lösung mit dem W-Verweis möglich ist. Tipps findest du in der LibreOffice-Onlinhilfe.

Eine komplexere Aufgabenstellung

Das Computerhaus IT-SYS erstellt die Rechnungen an die Kunden mit einer Calc-Lösung:

Hinweise

Auf diesem Formular fehlen der Briefkopf mit dem Firmenlogo und die Inhalte von Zeile 2 sowie Zeile 22. Diese sind für Lösung nicht relevant.

Wie du am unteren Tabellenrand erkennen kannst, existier hier eine zweite Tabelle mit dem Namen "Artikel". Es ist sinnvoll, Stammdaten wie hier die Artikelbezeichnung und den Preis in einem gesonderten Tabellenblatt zu halten, so dass diese bei Bedarf dann automatisch in ein neues Blatt übernommen werden können. Du musst diese beiden Tabellen nicht anlegen, lade dir hier die Calc-Datei herunter. Die Tabelle "Rechnung" hat in den Zellen noch keine Inhalte.

Die Anforderungen an die Lösung

1. Die Anzahl der Rechnungspositionen muss variabel gehalten werden mit der maximalen Anzahl von 12 Positionen pro Blatt. Alle Formeln müssen also bis auf Zeile 15 heruntergezogen werden, auch wenn weniger Positionen vorhanden sind. Es dürfen keine 0-Werte erscheinen (s. Punkt 2)! Die A-Spalte wird automatisch ausgefüllt. Lediglich in der Zelle A4 steht die Zahl 1. Ab A5 wird hier hochgezählt, sobald eine neue Artikelnummer eingegeben wurde.
Hilfe: =WENN(B5="";"";???)
Die drei ??? stehen für die entsprechende Formel.

2. Zu Spalte C: Nur wenn eine Artikelnummer eingegeben wurde, darf nach der Bezeichnung gesucht werden. Die eingegeben Artikelnummer muss mit einer Artikelnummer aus der Artikeldatei 100% überweinstimmen (dann ist eine Sortierung nicht erforderlich). Um dies zu erreichen, steht die "0" an letzte Stelle der Funktionsargumente. Sie darf (im Gegensatz zur "1", s. oben) nicht fehlen, wenn diese 100%-ige Übereinstimmung gewünscht wird.
Hilfe: =WENN(B4="";"";SVERWEIS(B4;$Artikel.???;0))
Die drei ??? sind durch die zu durchsuchende Teiltabelle und die Rückgabespalte zu ersetzen.

3. Die Werte von Spalte D werden manuell erfasst, die sind ja bei jeder Rechnung anders, also einfach die Zahlen eintragen.

4. Der Einzelpreis (Spalte E) wird so aus der Tabelle Artikel übernommen, wie du es schon bei Punkt 2 erledigt hast.

5. Bei der Berechnung des Betrags achte bitte darauf, dass nur dann gerechnet werden darf, wenn auch ein Artikel vorhanden ist.

6. Die Berechnungen im Rechnungsfuß werden ohne Überprüfung durchgeführt.