Verwenden der Funktion SVERWEIS in Excel 2007
In diesem Beispiel erstellen Sie drei berechnete Spalten die Funktion SVERWEIS in vielfältiger Weise verwendet. Es nutzt Informationen über Studenten an einer hypothetischen staatlichen Universität.
Öffnen und Untersuchen StudentInfo
Die Schüler enthält die wichtigsten Informationen über die registrierten Studenten. Dies ist das Arbeitsblatt, das Sie modifizieren wird.
GPA listet die Quartal Studenten und jährlich Notendurchschnitt gemittelt, auf einer Skala von 0 bis 4,0.
Noten enthält eine GPA-to-Buchstaben-Umwandlungstabelle. (Es enthält auch einige UI an der Unterseite, die in einem Verfahren zur Berechnung eines Grades von einem GPA-Wert verwendet werden kann. Dies in dem Abschnitt beschrieben wird unter Verwendung von VLOOKUP VBA).
Diese Arbeitsmappe stellt eine Probe einer tatsächlichen Arbeitsmappe, die möglicherweise Informationen über Tausende oder Zehntausende von Studenten enthalten könnten.
Hinzufügen einer Buddy Spalte E-Mail
Die Universität Paare alle eingehenden Freshman miteinander in einem „Buddy“ -System. Derzeit in der Students Arbeitsblatt listet die Klasse Buddy Spalte die ID des entsprechenden Schülers. Obwohl diese Informationen über eine manuelle Suche in derselben Tabelle verfügbar ist, einen leichteren Zugang zu ermöglichen, eine weitere Spalte mit der E-Mail-Kumpel hinzugefügt werden. Um dies zu erreichen, werden Sie die Funktion SVERWEIS verwenden in der gleichen Tabelle zu suchen, eine exakte Übereinstimmung mit.
Um die Buddy-E-Mail-Spalte zu erstellen
H2 ist die Zelle der Wert (391.885), die zum Nachschlagen.
A2: H23 ist die Tabelle Array zu durchsuchen. Beachten Sie die erste, linke Spalte, hier A (ID). stellt die Spalte für unseren Lookup-Wert zu suchen.
7 ist der Index der Spalte, deren Zelle enthält den Rückgabewert der Funktion, um eine Übereinstimmung unter der Annahme für den Lookup-Wert gefunden wird.
FALSCH gibt an, dass eine exakte Suche durchgeführt werden soll.
Bearbeiten der Formel I2, so dass die Tischanordnung verwendet absoluten Zellenverweis:
Dies sollte nicht den berechneten Wert in I2 ändern, ist aber für den nächsten Schritt erforderlich.
Kopieren Sie die Formel in Zelle I2 an Zellen I3 durch I23. Der einfachste Weg, dies zu tun ist diese Spalte automatisch zu füllen, indem Sie den unteren rechten Griff an I2 bis I23 ziehen. Der Rest der berechneten Buddy-E-Mail-Adressen sollte jetzt angezeigt werden soll. Klicken Sie auf einige dieser Zellen die darunter liegende NACHSCHLAG Formel zu untersuchen.
Hinzufügen einer Spalte zu Buddy Kompatibilität Bestätigen
Freunde sind in der Regel in der gleichen Abschlussklasse sein, erforderlich. Um dieses Verbot zu überwachen, wird eine neue Spalte angefordert Spur dieser Beziehung zu halten. Auch hier werden Sie SVERWEIS verwenden in der gleichen Tabelle zu suchen, eine exakte Übereinstimmung mit, aber die Ergebnisse werden dann in einer weiteren Berechnung verwendet werden.
die paarigen Klasse Validierung Spalte zu erstellen
Diese Formel vergleicht die Abschlussklasse des aktuellen Kursteilnehmers mit dem seinem Kumpel und gibt das Boolesche Ergebnis. Man beachte, daß dieses Mal, dass die Tabelle durch eine Reihe von Spalten beschrieben ist, A: H. Dies ist besser geeignet für Tabellen, in denen Zeilen dynamisch hinzugefügt und gelöscht werden.
Kopieren diese Formel mit dem Rest der Zellen in dieser Spalte Zellen J3 durch J23.
Beachten Sie, dass zwei gepaarte Studenten, Mr. White und Mr. Xie, sind in verschiedenen Studienklassen.
Hinzufügen eines Grade Säule
Schließlich wurde eine Anforderung empfängt dieses Arbeitsblatt mit dem aktuellen Student durchschnittlichen Gehalt zu verlängern. Um dies zu erreichen, führen Sie eine verschachtelte Lookup mit Hilfe von Tabellen auf den anderen Arbeitsblatt.
Eine Lookup ausgeführt, um das Ergebnis der vorhergehenden lookup Verwendung in dem GPA-Arbeitsblatt auf dem Tisch der Suche und der sechsten Spalte zurückkehrt. Beachten Sie die folgenden:
! B12: Der benannte Bereich GradesCnv statt Klasse A2 verwendet. Dies ist nur ein Kurz Komfort.
Der Suchtyp ist ungefähre (FALSCH) und damit die erste Spalte (GPA) in der Klasse Arbeitsblatt muss bestellt aufsteigend werden.
Kopieren diese Formel mit dem Rest der Zellen in dieser Spalte, K3 Zellen durch K23.
Die daraus resultierende Studenten Arbeitsblatt
Nachdem Sie diese drei neuen Spalten hinzugefügt haben, sollte das resultierende Students Arbeitsblatt wie in Abbildung erscheinen 1 (mit Ausnahme der in der Figur, Spalten D-G ausgeblendet wurden um Platz zu sparen).
Abbildung 1. Die Schüler-Arbeitsblatt nach Spalte Ergänzungen.
Diese Funktion kann verwendet werden, um Informationen in einem Arbeitsblatt zu finden oder, häufiger, einen bekannten Wert in einem Arbeitsblatt zu verwenden, für die zugehörigen Daten in einer Reihe von anderen, verwandter Arbeitsblatt zu suchen.
lookup_value ist der Wert für die in der ersten Spalte der Tabelle Array zu suchen. Dieser Parameter kann einen Wert oder einen Verweis halten.
Ein Text Spiel ist nicht case sensitiv, aber Leerzeichen, Nicht-Druck-und Sonderzeichen nicht berücksichtigt. Sie können die TRIM oder CLEAN-Funktionen verwenden, um diese Zeichen zu entfernen und EXACT Fall zu vergleichen.
Fehlerbedingungen
Die folgenden Bedingungen in Fehlerwerte führen wird zurückgegeben:
Wenn lookup_value kleiner als der kleinste Wert in der ersten Spalte von Matrix ist, gibt SVERWEIS # N / A.
Wenn Spaltenindex kleiner als 1 ist, dann #value! zurückgekehrt ist; wenn sie größer ist als die Anzahl der Spalten in Matrix. dann #REF! zurückgegeben.
Wenn keine Übereinstimmung gefunden wird, # N / A zurückgegeben.
Mit SVERWEIS von VBA
Hinweis: VBA-Code funktioniert nur in der Makro-fähigen Tabellen und Vorlagen. Wenn Sie diesen Code hinzufügen und testen möchten, speichern Sie die Begleiter StudentInfo.xlsx als Makro-fähige Tabelle namens StudentInfo.xlsm Datei.