Verweise

   SVERWEIS    Verweise-Logo

 Bereichswerte zuordnen

Für die weitere Arbeit brauchst du die Datei Wenn und Sverweis.xlsx.

Provisionstabelle Gib danach nach diesem Muster die nebenstehenden Werte ab A13 ein.
Um die Euro-Beträge in 50.000-Schritten hinzubekommen, tippst du erst die 0 in A14 und dann 50000 in A15 ein.Eine Falle:  Excel übernimmt das Monats-format aus dem darüber stehenden Datenblock.Über das Symbol Währung kannst du das Format wechseln und anschließend mit  Kommastellen zwei Kommastellen entfernen.

Ergänze nun folgende Angaben: Gib nach diesem Muster die nebenstehenden Werte ein: Um die Euro-Beträge in 50.000-Schritten hinzubekommen, tippe erst die 0 in A14 und dann 50000 in A15 ein.

Markiere nun beide Felder und ziehesie über das gemeinsame AUTOAUSFÜLL-KREUZ bis zum Feld A21.

Die Prozentangaben tauchen zunächst mit zwei Nachkommastellen auf. Auch hier kann man die beiden Anfangswerte markieren, eine Dezimalstelle löschen und danach nach unten  AUTOAUSFÜLLEN.

Ergänze folgende Überschriften. Klicke nun das Feld E4 an. Excel soll jeweils in der Provisionstabelle prüfen, welchen Provisionssatz der einzelne Mitarbeiter bekommt. Dazu kann man die SVERWEIS-Funktion nutzen:

SVerweis1a

Sverweis2 SVERWEIS sucht einen Vergleichswert in einer senkrecht aufgebauten Tabelle (WVERWEIS sucht in einer waagerecht aufgebauten Tabelle) und kann einen Wert aus dieser Tabelle zurückgeben.In der Zelle E4 kannst du nun folgende Formel aufbauen:

 

Sverweis3

Damit diese Funktion funktioniert, muss folgendes beachtet werden:

  • Die Zuordnungstabelle muss aufsteigend sortiert sein.
  • Die untersten Werte müssen so gesetzt werden, dass alle möglichen Fälle erfasst werden (sonst erscheint eine Fehlermeldung #NV für Nicht Vorhandene Zuordnungen).
  • Die Suchtabelle muss komplett bis zu der Spalten angegeben werden, in der der zu übermittelnde Wert steht.

Excel sucht in der Vergleichstabelle (Matrix) in der ersten Spalte solange, bis ein höherer Vergleichswert gefunden wird. Danach wechselt  Excel in die vorherige Zeile und übergibt den Wert aus der Spalte, die man bei Spaltenindex eingegeben hat. Steht der Wert also von der Startspalte an in Spalte 4, gibt man bei Spaltenindex eine 4 ein.

Excel sucht in der Vergleichstabelle (Matrix) in der ersten Spalte solange, bis ein höherer Vergleichswert gefunden wird. Danach wechselt  Excel in die vorherige Zeile und übergibt den Wert aus der Spalte, die man bei Spaltenindex eingegeben hat. Steht der Wert also von der Startspalte an in Spalte 4, gibt man bei Spaltenindex eine 4 ein.

Um den Matrixbereich über einen Namen einzusetzen, markiert man   die Provisionstabelle (ohne Überschrift).

Sverweis4 Im Namenfeld übertippt man die Zelladresse mit dem gewünschten Bereichsnamen. Bestätige den Namen mit der Eingabe-Taste. Der Bereich wird automatisch festgesetzt.In der SVERWEIS-Formel kann man im Fenster bei Matrix den gewünschten Bereichsnamen über die Funktionstaste F3 einsetzen.

Sverweis5

Hinter Bereichsnamen verbergen sich immer festgesetzte Zellbereiche.

Eindeutige Werte zuordnen

Für diese Übung brauchst du wieder die Datei Personal:

SverweisB1

Nur für diese Mitarbeiter sollen aus dem ersten Blatt im ersten Schritt die Mitarbeiternamen übertragen werden:

SverweisB2

Steht bei Bereich_Verweis der Schalter FALSCH (oder kürzer eine die Ziffer Null), wird eine absolute Übereinstimmung der Mitarbeiternummer gesucht (Reihenfolge ist dabei beliebig).

Mit folgender WENN-Bedingung kann man prüfen, ob die Mitarbeiternummer vorhanden ist:

=WENN(ISTNV(SVERWEIS(A2;Personal!$A$1:$B$23;2;FALSCH)) = WAHR;
   “Mitarbeiter nicht gefunden”; SVERWEIS(A2;Personal!$A$1:$B$23;2;FALSCH)) 
 

Über eine WENN-Bedingung wird hier zunächst geprüft, ob es einen Mitarbeiter gibt. Liefert die ISTNV beim SVERWEIS das Ergebnis WAHR, gibt es die gesuchte Mitarbeiternummer nicht und der Text “Mitarbeiter nicht gefunden” wird ausgegeben. Gibt es die Nummer, liefert der SVERWEIS den Mitarbeiternamen.

Übertragen Sie jetzt in das Feld C2 das Mitarbei­tergehalt:

SverweisB3

Ordne nun ein Währungsformat zu. Danach tippe  hinter der Formel in der Eingabezeile ein + und wählen dann die zweite SVERWEIS-Funktion:

SverweisB4

Komplett sieht die Formel so aus:

SverweisB5