In der SVERWEIS-Funktion definieren wir oft col_index_no static. Wir haben es innerhalb der SVERWEIS-Formel hartcodiert, wie SVERWEIS(id,data,3,0). Das Problem tritt auf, wenn wir eine Spalte innerhalb von Daten einfügen oder löschen. Wenn wir eine Spalte vor oder nach der 3. Spalte entfernen oder hinzufügen, verweist die 3. Spalte nicht mehr auf die beabsichtigte Spalte. Dies ist ein Problem. Andere sind, wenn Sie mehrere Spalten zum Nachschlagen haben. Sie müssen den Spaltenindex in jeder Formel bearbeiten. Einfaches Kopieren und Einfügen hilft nicht.
Aber wie wäre es, wenn Sie SVERWEIS anweisen können, sich die Überschriften anzusehen und nur übereinstimmende Überschriftenwerte zurückzugeben. Dies wird als bidirektionaler SVERWEIS bezeichnet.
Wenn ich beispielsweise eine SVERWEIS-Formel für dieMarken Spalte, dann sollte SVERWEIS nach suchen Marken Spalte in Daten und Rückgabewert aus dieser Spalte. Dies wird unser Problem lösen.
Hmm… Okay, wie machen wir das? Durch Verwendung der Match-Funktion innerhalb der SVERWEIS-Funktion.
Generische Formel
=SVERWEIS(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup-Wert: der Nachschlagewert in der ersten Spalte von table_array.
Tabellenarray: der Bereich, in dem Sie eine Suche durchführen möchten. ZB A2, D10.
Lookup_heading: die Überschrift, die Sie in den Überschriften von table_array suchen möchten.
Tabellenüberschriften: Referenz der Überschriften im Tabellenarray. Z.B. wenn die Tabelle A2, D10 und Überschriften oben in jeder Spalte ist, dann ist es A1: D1.
Jetzt wissen wir also, was wir für den dynamischen col_index brauchen, lassen Sie uns alles mit einem Beispiel klären.
Dynamisches SVERWEIS-Beispiel
Für dieses Beispiel haben wir diese Tabelle, die Daten von Schülern im Bereich A4:E16 enthält.
Mit Rollennummer und Überschrift möchte ich Daten aus dieser Tabelle abrufen. In diesem Fall möchte ich in Zelle H4 die Daten der Rolle Nr. in Zelle G4 und der Überschrift in H3 erhalten. Wenn ich die Überschrift ändere, sollen in Zelle H4 Daten aus dem jeweiligen Bereich abgerufen werden.
Schreiben Sie diese Formel in Zelle H4
=SVERWEIS(G4,B4:E16,VERGLEICH(H3,B3:E3,0),0)
Da unser Tabellen-Array B4:E16 ist, wird unser Überschriften-Array B3:E3.
Notiz: Wenn Ihre Daten gut strukturiert sind, haben die Spaltenüberschriften die gleiche Anzahl von Spalten und es ist die erste Zeile in der Tabelle.
Wie es funktioniert:
Der Hauptteil besteht also darin, die Spaltenindexnummer automatisch auszuwerten. Dazu haben wir die MATCH-Funktion verwendet.
SPIEL(H3,B3:E3,0): Da H3 „Student“ enthält, gibt MATCH 2 zurück. Wenn H3 „Grade“ hätte, hätte es 4 zurückgegeben und so weiter. Die SVERWEIS-Formel wird schließlich col_index_num haben.
=SVERWEIS(G4,B4:E16,2,0)
Wie wir wissen, gibt die MATCH-Funktion die Indexnummer eines gegebenen Wertes im angegebenen eindimensionalen Bereich zurück. Daher sucht MATCH nach jedem in H3 geschriebenen Wert im Bereich B3:E3 und gibt seine Indexnummer zurück.
Wenn Sie nun eine Überschrift in H3 ändern, wenn es sich um Überschriften handelt, gibt diese Formel einen Wert aus der entsprechenden Spalte zurück. Andernfalls erhalten Sie einen #N/A-Fehler.
SVERWEIS in mehreren Spalten schnell
Im obigen Beispiel benötigten wir die Antwort aus einem Spaltenwert. Aber was ist, wenn Sie mehrere Spalten gleichzeitig erhalten möchten. Wenn Sie die obige Formel kopieren, werden Fehler zurückgegeben. Wir müssen einige kleinere Änderungen daran vornehmen, um es portabel zu machen.
Verwenden von absoluten Referenzen mit SVERWEIS
Schreiben Sie die folgende Formel in Zelle H2.
=SVERWEIS($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Kopieren Sie nun H2 in alle Zellen im Bereich H2:J6, um sie mit Daten zu füllen.
Wie es funktioniert:
Hier habe ich gegeben absoluter Bezug jedes Bereichs außer Zeile im Nachschlagewert für SVERWEIS ($G2) und Spalte in lookup_value für MATCH (1 H$).
$G2: Dadurch kann sich die Zeile beim Kopieren nach unten auf den Nachschlagewert für die SVERWEIS-Funktion ändern, aber die Spalte auf die Änderung beim Kopieren nach rechts beschränken. Dadurch sucht SVERWEIS nur mit der relativen Zeile nach der ID aus der Spalte G.
Ähnlich, 1 H$ erlaubt, dass sich die Spalte ändert, wenn sie horizontal kopiert wird, und schränkt die Zeile ein, wenn sie nach unten kopiert wird.
Verwenden benannter Bereiche
Das obige Beispiel funktioniert gut, aber es wird schwierig, diese Formel zu lesen und zu schreiben. Und das ist überhaupt nicht tragbar. Dies kann vereinfacht werden mit benannte Bereiche.
Wir werden hier zuerst einige Namensgebungen vornehmen. Für dieses Beispiel habe ich genannt
$B$2:$E$14 : als Daten
$B$1:$E$1 : als Überschriften
1 H$: Benennen Sie es als Überschrift. Machen Sie die Spalten relativ. Wählen Sie dazu H1. Drücken Sie STRG+F3, klicken Sie auf neu, entfernen Sie im Abschnitt Verweist auf das '$' von der Vorderseite von H.
$G2: Benennen Sie es in ähnlicher Weise als RollNo. Diesmal macht die Zeile relativ, indem '$' vom Anfang von 2 entfernt wird.
Wenn Sie nun alle Namen auf dem Blatt haben, schreiben Sie diese Formel an eine beliebige Stelle in der Excel-Datei. Es wird immer die richtige Antwort erhalten.
=SVERWEIS(RollNo,Data, MATCH(Überschrift, Überschriften,0),0)
Sehen Sie, das kann jeder lesen und verstehen.
Mit diesen Methoden können Sie also col_index_num dynamisch machen. Lassen Sie mich wissen, ob dies im Kommentarbereich unten hilfreich war.
Wie benutzt manDie SVERWEIS-Funktion in Excel
Relativer und absoluter Bezug in Excel
Benannte Bereiche in Excel
Wie man SVERWEIS von verschiedenen Excel-Tabellen aus macht
SVERWEIS Mehrere Werte
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
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.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.