3 Verweisfunktionen von Excel, die du kennen solltest

Excel hat drei Funktionen, die das Wort „Verweis“ in sich haben. Den SVERWEIS kennt wohl fast jeder, der Excel nutzt. Aber was ist mit den anderen beiden Funktionen, nämlich WVERWEIS und VERWEIS?
In diesem Beitrag will ich zeigen, wie die 3 Funktionen arbeiten und worin sie sich unterscheiden.

1. Die Ausgangswerte

Im Beispiel will ich Daten eines Obst- und Gemüsehändlers verwenden. Da gibt es eine Preisliste, die alle gehandelten Sorten sowie die Verkaufspreise pro kg enthält.

Verweise1

Diese Liste kann sich durchaus ändern, das hängt vom Preisgebahren des Großhandels, aber auch von der Nachfrage der Kunden ab.

Da gibt es täglich eine weitere Liste mit den gehandelten Artikeln und den Verkaufsmengen.
Zum Abschluss des Tages will der Händler wissen, wie viel Geld er in seiner Kasse haben muss.

Dazu bedient er sich des Tabellenkalkulationsprogramms Excel. Er gibt die Artikel und die verkauften Mengen in eine Liste ein. Die Artikel sortiert er von A-Z (wie das geht, ist hier nachzulesen).

So könnte das dann aussehen:

Verweise7

 

2. Erlösberechnung mit SVERWEIS

Die Erlöse sollen so ausgerechnet werden:
Artikelpreis * Menge

Dazu braucht der Händler den Artikelpreis, die Menge steht schon in seiner Tabelle. Diesen will er sich mit der Funktion SVERWEIS holen und das geht so:

Die Syntax der Funktion lautet:

=SVERWEIS(Suchkriterium; Matrix;Spaltenindex;Bereich_Verweis)

Das „S“ im Funktionsnamen steht für Senkrecht.

Das Suchkriterium ist der in der ersten Spalte der Matrix zu suchende Wert. Der erste Wert dieser Spalte ist „Äpfel“.

Die Matrix ist der Bereich, in dem der Preis für Äpfel gefunden werden soll. Das ist die Preisliste.

Der Preis befindet sich in der zweiten Spalte der Matrix, Spaltenindex ist also 2.
Bei Bereich_Verweis hat der Händler nun die Wahl, ob das Suchkriterium ungefähr übereinstimmen soll, dann ist WAHR zu nehmen, oder ob es genau übereinstimmen soll, dann ist FALSCH zu nehmen. Natürlich will er eine genaue Übereinstimmung, denn es nützt nichts, wenn er den Apfelpreis sucht, aber den Birnenpreis bekommt. Er wählt somit FALSCH.

Damit lautet die Formel für die Zelle C24:

=SVERWEIS($A24;$A$7:$B$19;2;FALSCH)*B24

Mit dem Ausfüllkästchen zieht der Händler die Formel bis C36 durch.

In C37 schreibt er schließlich noch die Formel

=SUMME(C24:C36)

Damit sind die Erlöse je Artikel und die Erlössumme über alle Artikel berechnet.

Verweise2.png

 

3. Erlösberechnung mit WVERWEIS

Der Händler probiert gern einmal etwas Neues aus und will die Berechnung mit der Funktion WVERWEIS vornehmen.

Die Syntax dieser Funktion lautet:

=WVERWEIS(Suchkriterium;Matrix;Zeilenindex;Bereich_Verweis)

Das „W“ im Funktionsnamen steht für Waagerecht.

Das Suchkriterium ist der Wert, der in der ersten Zeile der Matrix gefunden werden soll. Im Beispiel ist das die Zeichenfolge „Preis/kg“ und die befindet sich in der Preisliste in Zelle B6.

Matrix ist wieder die Preisliste.

Jetzt braucht er den Zeilenindex. Um die Erlöse in C24 berechnen zu können, muss er die Zeile in der Preisliste finden, in der der erste Artikel seiner Erlösliste, nämlich „Äpfel“ steht. Er kann nun einfach die 1 als feste Zahl in die Formel eintragen. Dann lässt sich die Formel allerdings nicht bis C36 herunter ziehen, denn bei jedem weiteren Artikel müsste eine andere Zahl als Zeilenindex eingetragen werden.

Er setzt daher für den Zeilenindex die Funktion VERGLEICH ein.
Mit Bereich_Verweis wird wieder über WAHR oder FALSCH entschieden. Auch hier wird FALSCH, also die genaue Übereinstimmung gebraucht.

Er verwendet hier probehalber eine neue Tabelle.

Die Formel für die Zelle C44 lautet:

=WVERWEIS($B$6;$A$6:$B$19;VERGLEICH($A44;$A$7:$A$19;0)+1;FALSCH)*$B44

Das Ergebnis aus VERGLEICH wird um 1 erhöht, da die Vergleichsmatrix bereits in A6 beginnt, Äpfel dann aber erst in der zweiten Zeile stehen.
Mit dem Ausfüllkästchen zieht der Händler die Formel bis C56 durch.

In C57 schreibt er schließlich noch die Formel

=SUMME(C44:C56)

Damit sind die Erlöse je Artikel und die Erlössumme über alle Artikel berechnet.

Verweise3

 

4. Erlösberechnung mit VERWEIS

Der Händler ist immer auf der Suche nach der besten Lösung für seine Abrechnung. Deshalb will er auch noch die Funktion VERWEIS ausprobieren.
Nun stellt er aber fest, dass es die in zwei Versionen gibt, einmal als Verktorversion und einmal als Matrixversion.

Er beginnt mit der Vektorversion.

Die Syntax dieser Funktion lautet:

=VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

Das Suchkriterium ist, wie beim SVERWEIS, der in der ersten Spalte der Matrix zu suchende Wert. Der erste Wert dieser Spalte ist „Äpfel“.

Suchvektor ist die erste Spalte, Ergebnisvektor ist die zweite Spalte in der Preisliste. Aus dem Ergebnisvektor wird der Preis entnommen. Die Multiplikation mit der Menge erfolgt schließlich in der Formel.

Die Werte im Suchvektor müssen in aufsteigender Reihenfolge angeordnet sein, das hat der Händler wohlwissentlich schon ganz zu Beginn getan.

Damit liegen schon alle Argumente vor. Der Händler nimmt auch hier zunächst eine neue Tabelle und schreibt in Zelle C63 diese Formel:

=VERWEIS($A63;$A$7:$A$19;$B$7:$B$19)*$B63

Die Formel wird bis C75 heruntergezogen.
In C76 schreibt er schließlich noch die Formel

=SUMME(C63:C75)

Damit sind die Erlöse je Artikel und die Erlössumme über alle Artikel berechnet.

Verweise4

Nun soll die VERWEIS-Funktion in der Matrixversion verwendet werden.

Die Syntax dieser Funktion lautet:

=VERWEIS(Suchkriterium;Matrix)

Das Suchkriterium ist, wie beim SVERWEIS, der in der ersten Spalte der Matrix zu suchende Wert. Der erste Wert dieser Spalte ist „Äpfel“.

Mit Matrix werden Suchvektor und Ergebnisvektor aus der Vektorversion zusammengefasst.

Die Matrixversion hat ein paar Besonderheiten, die zu beachten sind:

  • VERWEIS wählt immer den Wert, der in der letzten Zeile bzw. Spalte steht
  • Sind in der Matrix mehr Spalten als Zeilen, nimmt VERWEIS einen Abgleich der Werte aus der ersten Zeile mit dem Suchkriterium vor.
  • Ist die Matrix quadratisch oder befinden sich in ihr mehr Zeilen als Spalten, durchsucht VERWEIS die erste Spalte.
  • Die erste Spalte muss aufsteigend sortiert sein.

Die Preisliste hat zwei Spalten und > 2 Zeilen. Das wird sich kaum ändern. Deshalb sucht die Funktion in der ersten Spalte.

Der Händler schreibt in C80 diese Formel und zieht sie anschließend bis C92 herunter:

=VERWEIS($A80;$A$7:$B$19)*$B80

In C93 schreibt er schließlich noch die Formel

=SUMME(C80:C92)

Damit sind auch hier die Erlöse je Artikel und die Erlössumme über alle Artikel berechnet.

Verweise5

Die Formel der Matrixversion erscheint dem Händler sehr einfach. Er gibt der Matrix den Namen „Preisliste“ und probiert es noch einmal mit dieser Formel:

=VERWEIS($A80;Preisliste)*$B80

Die Summe in C93 weist natürlich auch jetzt 290,50 € aus.

 

Fazit

Der Beitrag hat gezeigt, dass mit allen der drei Funktionen die Berechnungsergebnisse erzielt werden können.

SVERWEIS sucht ein Kriterium in der ersten Spalte einer Matrix und gibt den zugehörigen Wert aus einer anderen, mit einer Zahl benannten und rechts liegenden Spalte, zurück.

WVERWEIS sucht ein Kriterium in der ersten Zeile einer Matrix und gibt den zugehörigen Wert aus einer anderen, mit einer Zahl benannten und darunterliegenden Zeile, zurück.

VERWEIS in der Matrixversion, die sich in diesem Beispiel anbietet, arbeitet analog SVERWEIS, weil die Matrix mehr Zeilen als Spalten hat.

Der Händler entscheidet für sich, mit der VERWEIS-Funktion in der Matrixversion weiter zu machen.

Die Formel

=VERWEIS($A80;Preisliste)*$B80

ist kurz und übersichtlich. Sie bedeutet: Suche die gewünschte Obst- bzw. Gemüsesorte in der ersten Spalte der Preisliste und gebe den entsprechenden Wert aus der zweiten Spalte zurück.

Warum wird die Funktion VERWEIS eigentlich nicht mehr genutzt? In Beiträgen anderer Blogs oder auch in Foren ist dieses Thema kaum gegenwärtig.

Wie hältst du es damit?

Advertisements

2 Kommentare zu „3 Verweisfunktionen von Excel, die du kennen solltest“

  1. Hallo Gerhard.

    persönlich nutze ich auch lieber die Funktionen WVERWEIS und SVERWEIS. Der Vorteil beider Funktionen hat es in meinen Augen, dass schon anhand des Formelnamen direkt erkannt werden kann, wo die einzelnen Daten zu finden sind und ich so nicht groß drüber nachdenken muss, was ich damals eigentlich auswerten wollte.

    Immerhin einmalig hatte ich ebenfalls schon einen Anwendungsfall der nur mit der Verweisfunktion zu lösen war. Hier wollte ich prüfen in welcher über Intervalle definierte Gruppe ein Wert einzuordnen ist ( siehe http://www.andreas-unkelbach.de/blog/?go=show&id=540 ) wobei ich hier als Beispiel die Umwandlung von Punkten in Schulnoten genutzt hatte 🙂 (manchmal ist das Thema Schule gar nicht so weit von Excel entfernt).

    Dennoch ist dies vielleicht einfach meiner Bequemlichkeit geschuldet :-).

    Viele Grüße
    Andreas

    Gefällt mir

    1. Hallo Andreas,
      besten Dank für deinen Kommentar und für den Link auf deine Site. Ich habe schon mal reingeschaut, mir gefallen deine Beispiele aus der Praxis sehr gut. Ich muss aber noch intensiv lesen.
      Aber ich muss zugeben, dass ich mit den Verweisfunktionen selten arbeite, mehr mit INDEX oder BEREICH.VERSCHIEBEN.
      Nochmals danke und viele Grüße
      Gerhard

      Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s