In diesem Artikel erfahren Sie, wie Sie den Wert zwischen zwei Zahlen in Excel nachschlagen.
Szenario:
Es ist einfach, einen Wert mit einem Kriterium in einer Tabelle nachzuschlagen. Wir können einfach SVERWEIS verwenden. Aber was könnten wir tun, wenn wir diese mehrspaltigen Kriterien in Ihren Daten haben und in mehreren Spalten suchen müssen, um einen Wert zu finden? Lassen Sie uns lernen, wie dieses Problem mit verschiedenen Lookup-Versionen von Excel gelöst werden kann
LOOKUP-Wert zwischen zwei Zahlen
Verwenden der SVERWEIS-Formel
Wenn bei SVERWEIS-Annäherungsübereinstimmung kein Wert gefunden wird, wird der letzte Wert, der kleiner als der Nachschlagewert ist, abgeglichen und der Wert aus dem angegebenen Spaltenindex zurückgegeben.
Generische Formel zum LOOKUP-Wert zwischen zwei Zahlen:
| = SVERWEIS (Wert, Tabelle, lookup_col , 1 ) |
Und noch eine Sache von Vlookup ist, dass es nach dem Wert in der Spalte sucht und wenn es den Wert im Spaltenarray nicht findet, dann passt es und gibt den Wert zurück, der kleiner als dieser Wert im Tabellenarray ist.
Hinweis: SVERWEIS führt standardmäßig eine ungefähre Übereinstimmung aus, wenn wir die Bereichssuchvariable weglassen. Eine ungefähre Übereinstimmung ist nützlich, wenn Sie eine ungefähre Übereinstimmung durchführen möchten und Ihr Tabellenarray in aufsteigender Reihenfolge sortiert ist.
Verwenden von INDEX- und MATCH-Formeln
Die INDEX- und MATCH-Formeln funktionieren wie oben, jedoch mit einer anderen Syntax. Aber wenn Sie nur diejenige wählen sollten, mit der Sie sich wohl fühlen
Generische Formel zum LOOKUP-Wert zwischen zwei Zahlen
| =INDEX(lookup_range,VERGLEICH(1,INDEX((Kriterien1=Bereich1)*(Kriterien2=Bereich2),0,1),0)) |
lookup_range: der Bereich, aus dem Sie den Wert abrufen möchten.
Kriterien1, Kriterien2, Kriterien N: Dies sind die Kriterien, die Sie in Bereich1, Bereich2 und Bereich N abgleichen möchten. Sie können bis zu 270 Kriterien - Bereichspaare haben.
Range1, range2, rangeN : Dies sind die Bereiche, in denen Sie Ihre jeweiligen Kriterien erfüllen.
Beispiel :
All dies kann verwirrend sein zu verstehen. Lassen Sie uns anhand eines Beispiels verstehen, wie die Funktion verwendet wird. Hier haben wir den Studentenausweis und ihre jeweiligen Noten in einem Test. Jetzt mussten wir die Noten für jeden Schüler durch Nachschlagen in der Notensystemtabelle abrufen.
Dazu verwenden wir ein Attribut der SVERWEIS-Funktion, das heißt, wenn die SVERWEIS-Funktion die genaue Übereinstimmung nicht findet, sucht sie nur nach der ungefähren Übereinstimmung in der Tabelle und nur, wenn das letzte Argument der Funktion entweder ist WAHR oder 1.
Die Noten / Punktetabelle muss aufsteigend sein
Verwenden Sie die Formel:
| = SVERWEIS ( B2 , Tabelle , 2 , 1 ) |
Wie funktioniert es?
Die Vlookup-Funktion sucht den Wert 40 in der Markierungsspalte und gibt bei Übereinstimmung den entsprechenden Wert zurück. Wenn sie nicht übereinstimmt, sucht die Funktion nach dem kleineren Wert als dem Nachschlagewert (d. h. 40) und gibt ihr Ergebnis zurück.
Hier heißt das table_array range als Tisch in Formel und B2 wird als Zellbezug angegeben.
Wie Sie sehen können, haben wir die Note für den ersten Schüler erhalten. Um nun alle anderen Grade zu erhalten, verwenden wir die Verknüpfung Strg + D oder verwenden Sie die Drag-Down-Zelloption in Excel.
Hier sind alle Noten der Klasse mit der SVERWEIS-Funktion.
LOOKUP-Wert zwischen zwei Zahlen in der Employee-Tabelle
Wir haben eine Tabelle, die die Details aller Mitarbeiter in einer Organisation auf einem separaten Blatt enthält. Die erste Spalte enthält die ID dieser Mitarbeiter. Ich habe diese Tabelle als emp_data benannt.
Jetzt muss mein Suchblatt die Informationen des Mitarbeiters abrufen, dessen ID in Zelle B3 geschrieben ist. Als ID habe ich B3 genannt.
Zum besseren Verständnis haben alle Spaltenüberschriften genau dieselbe Reihenfolge wie die Tabelle emp_data.
Schreiben Sie nun die folgende Formel in Zelle C3, um die Zone der in B3 geschriebenen Mitarbeiter-ID abzurufen.
| =SVERWEIS(ID,Emp_Data,2,0) |
Dadurch wird die Zone der Mitarbeiter-ID 1-1830456593 zurückgegeben, da Spalte 2 in der Datenbank die Zone der Mitarbeiter enthält.
Kopieren Sie diese Formel in die restlichen Zellen und ändern Sie die Spaltennummer in der Formel, um alle Informationen zu den Mitarbeitern zu erhalten.
Sie können alle Informationen zu der genannten ID in Zelle B3 sehen. Unabhängig davon, welche ID Sie in Zelle B3 schreiben, werden alle Informationen abgerufen, ohne die Formel zu ändern.
Wie funktioniert das?
Es ist nichts schwierig. Wir verwenden einfach die SVERWEIS-Funktion, um die ID zu suchen und dann die erwähnte Spalte abzurufen. Üben Sie SVERWEIS mit solchen Daten, um SVERWEIS besser zu verstehen.
Abrufen von Mitarbeiterdaten mithilfe von Überschriften
Im obigen Beispiel haben wir alle Spalten in der gleichen Reihenfolge organisiert, aber es kann vorkommen, dass Sie eine Datenbank mit Hunderten von Spalten haben. In solchen Fällen ist diese Methode zum Abrufen von Mitarbeiterinformationen nicht gut. Es ist besser, wenn die Formel die Spaltenüberschrift überprüfen und Daten aus dieser Spalte aus der Mitarbeitertabelle abrufen kann.
Um den Wert aus der Tabelle mithilfe der Spaltenüberschrift abzurufen, verwenden wir eine 2-Wege-Suchmethode oder sagen dynamische Spalte SVERWEIS.
Verwenden Sie diese Formel in Zelle C3 und kopieren Sie sie in die restlichen Zellen. Sie müssen nichts an der Formel ändern, alles wird aus thd emp_data geholt.
| =SVERWEIS(ID,Emp_Data,MATCH(C2,Emp_Data_Headers,0),0) |
Diese Formel ruft einfach alle Informationen aus übereinstimmenden Spalten ab. Sie können die Kopfzeilen im Bericht durcheinander bringen, dies macht keinen Unterschied. Unabhängig davon, welche Überschrift in die obige Zelle geschrieben wird, sind die entsprechenden Daten enthalten.
Wie funktioniert das?
Dies ist einfach ein dynamisches SVERWEIS. Sie können hier darüber lesen. Wenn ich es hier erkläre, wird es ein zu großer Artikel.
Abrufen der Mitarbeiter-ID mit teilweiser Übereinstimmung
Es kann vorkommen, dass Sie sich nicht mehr an die gesamte ID eines Mitarbeiters erinnern, aber dennoch die Informationen einer ID abrufen möchten. In solchen Fällen ist eine teilweise Übereinstimmung SVERWEIS die beste Lösung.
Zum Beispiel, wenn ich weiß, dass eine ID 2345 enthält, ich aber nicht die ganze ID kenne. Wenn ich diese Zahl in Zelle C3 eingebe, wird die Ausgabe wie folgt aussehen.
Wir bekommen nichts. Da stimmt nichts mit 2345 in der Tabelle überein. Modifizieren Sie die obige Formel wie folgt.
| =SVERWEIS("*"&ICH WÜRDE&"*",Emp_Daten,SPIEL(C2,Emp_Data_Headers,0),0) |
Kopieren Sie dies in die gesamte Zeile. Und jetzt haben Sie die Informationen des ersten Mitarbeiters, der diese Nummer enthält.
Bitte beachten Sie, dass wir die erste ID erhalten, die die übereinstimmende Nummer in der Spalte Emp-ID enthält. Wenn eine andere ID dieselbe Nummer enthält, ruft diese Formel die Informationen dieses Mitarbeiters nicht ab.
Wenn Sie alle Mitarbeiter-IDs abrufen möchten, die dieselbe Nummer enthalten, verwenden Sie die Formel, die alle übereinstimmenden Werte sucht.
Verwenden von INDEX- und MATCH-Formeln
Hier haben wir eine Datentabelle. Ich möchte den Namen des Kunden mit Datum der Buchung, Erbauer und Bereich abrufen. Hier habe ich also drei Kriterien und einen Nachschlagebereich.
Schreiben Sie diese Formel in Zelle I4 und drücken Sie die Eingabetaste.
| =INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Wie es funktioniert:
Wir wissen bereits, wie INDEX- und MATCH-Funktionen in EXCEL funktionieren, daher werde ich das hier nicht erklären. Wir werden über den Trick sprechen, den wir hier verwendet haben.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Der Hauptteil ist dieser. Jeder Teil dieser Anweisung gibt ein Array von true false zurück.
Wenn boolesche Werte multipliziert werden, geben sie ein Array von 0 und 1 zurück. Die Multiplikation funktioniert als UND-Operator. Hense, wenn alle Werte wahr sind, gibt es nur 1 zurück, sonst 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Dies wird insgesamt zurückkehren
| {FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}*
{FALSCH;FALSCH;FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR}* {FALSCH; FALSCH; FALSCH; WAHR; FALSCH; FALSCH; FALSCH; WAHR; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH} |
Was übersetzt in
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Funktion gibt das gleiche Array zurück ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) zur MATCH-Funktion als Lookup-Array.
MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): Die MATCH-Funktion sucht nach 1 im Array {0;0;0; 0;0;0;0;1;0;0;0;0;0;0;0}. Und gibt die Indexnummer der ersten 1 im Array zurück, die hier 8 ist.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Schließlich gibt INDEX zurück der Wert aus dem angegebenen Bereich (E2:E16) am gefundenen Index (8).
Hier sind alle Beobachtungsnotizen mit der Formel in Excel
Anmerkungen :
- Die Tabellenspalte Noten/Ergebnisse muss in aufsteigender Reihenfolge sein, sonst kann die Funktion ein falsches Ergebnis liefern.
- Die SVERWEIS-Funktion sucht nach dem Wert in der ersten Zeile von Table_array und extrahiert die entsprechenden Werte nur rechts vom lookup_range.
- Das letzte Argument der Funktion SVERWEIS der Funktion muss entweder auf TRUE oder 1 gesetzt werden, um die ungefähre Übereinstimmung zu erhalten.
- Die SVERWEIS-Funktion gibt einen Fehler zurück, wenn die Arbeitsmappenadresse ungültig oder falsch ist.
- Die SVERWEIS-Funktion gibt einen Fehler zurück, wenn der Wert nicht übereinstimmt.
Ich hoffe, dieser Artikel zum Nachschlagen von Werten zwischen zwei Zahlen in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
Möglichkeit zur Verwendung der Vlookup-Funktion in der Datenvalidierung : Beschränken Sie Benutzer, um Werte aus der Nachschlagetabelle zuzulassen, indem Sie das Feld Datenvalidierungsformel in Excel verwenden. Mit dem Formelfeld in der Datenvalidierung können Sie die Art der erforderlichen Einschränkung auswählen.
So rufen Sie den neuesten Preis in Excel ab : Es ist üblich, Preise in jedem Unternehmen zu aktualisieren und die Verwendung der neuesten Preise für jeden Kauf oder Verkauf ist ein Muss. Um den aktuellen Preis aus einer Liste in Excel abzurufen, verwenden wir die LOOKUP-Funktion. Die LOOKUP-Funktion ruft den neuesten Preis ab.
SVERWEIS-Funktion zum Berechnen der Note in Excel : Um die Noten zu berechnen, sind IF und IFS nicht die einzigen Funktionen, die Sie verwenden können. Der SVERWEIS ist für solche bedingten Berechnungen effizienter und dynamischer. Um Noten mit SVERWEIS zu berechnen, können wir diese Formel verwenden.
17 Dinge über Excel SVERWEIS : SVERWEIS wird am häufigsten zum Abrufen übereinstimmender Werte verwendet, aber SVERWEIS kann noch viel mehr. Hier sind 17 Dinge über SVERWEIS, die Sie wissen sollten, um sie effektiv zu nutzen.
LOOKUP den ersten Text aus einer Liste in Excel : Die SVERWEIS-Funktion funktioniert gut mit Platzhalterzeichen. Wir können dies verwenden, um den ersten Textwert aus einer bestimmten Liste in Excel zu extrahieren. Hier ist die generische Formel.
LOOKUP-Datum mit letztem Wert in der Liste : Um das Datum abzurufen, das den letzten Wert enthält, verwenden wir die LOOKUP-Funktion. Diese Funktion sucht nach der Zelle, die den letzten Wert in einem Vektor enthält, und verwendet dann diese Referenz, um das Datum zurückzugeben.
Populäre Artikel :
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgaben in Excel schneller. Diese Verknüpfungen helfen Ihnen, Ihre Arbeitseffizienz in Excel zu steigern.
So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen und Blättern verwendet wird.
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.