Inhalte
- Suchen Sie in Excel nach mehreren Kriterien
- Wir extrahieren den 2., 3. usw. Wert mit SVERWEIS
- Rufen Sie alle Wiederholungen des gewünschten Werts ab
- XNUMXD Suche nach bekannter Zeile und Spalte
- Verwenden mehrerer SVERWEISE in einer Formel
- Dynamische Substitution von Daten aus verschiedenen Tabellen mit SVERWEIS und INDIREKT
Im zweiten Teil unseres Tutorials zur Funktion VPR (VLOOKUP) in Excel analysieren wir einige Beispiele, die Ihnen helfen, die ganze Kraft zu lenken VPR um die ehrgeizigsten Excel-Aufgaben zu lösen. Bei den Beispielen wird davon ausgegangen, dass Sie bereits über grundlegende Kenntnisse zur Funktionsweise dieser Funktion verfügen. Wenn nicht, könnten Sie daran interessiert sein, mit dem ersten Teil dieses Tutorials zu beginnen, in dem die Syntax und die grundlegende Verwendung erklärt werden. VPR. Nun, fangen wir an.
Suchen Sie in Excel nach mehreren Kriterien
Funktion VPR in Excel ist ein wirklich leistungsfähiges Werkzeug zum Durchführen von Suchen nach einem bestimmten Wert in einer Datenbank. Es gibt jedoch eine erhebliche Einschränkung – seine Syntax ermöglicht es Ihnen, nur nach einem Wert zu suchen. Was ist, wenn Sie nach mehreren Bedingungen suchen möchten? Nachfolgend finden Sie die Lösung.
Beispiel 1: Suche nach 2 verschiedenen Kriterien
Angenommen, wir haben eine Liste mit Bestellungen, die wir finden möchten Warenmenge (Menge), basierend auf zwei Kriterien – Kundenname (Kunde) è Название продукта (Produkt). Die Sache wird durch die Tatsache kompliziert, dass jeder der Käufer mehrere Arten von Waren bestellt hat, wie aus der folgenden Tabelle ersichtlich ist:
normale Funktion VPR funktioniert in diesem Szenario nicht, da es den ersten gefundenen Wert zurückgibt, der mit dem angegebenen Nachschlagewert übereinstimmt. Zum Beispiel, wenn Sie die Menge eines Artikels wissen möchten Süßigkeiten'vom Käufer bestellt Jeremy Hill, schreiben Sie die folgende Formel:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– Diese Formel gibt das Ergebnis zurück 15dem Produkt entsprechend Äpfel, weil es der erste Wert ist, der übereinstimmt.
Es gibt eine einfache Problemumgehung – Erstellen Sie eine zusätzliche Spalte, in der Sie alle gewünschten Kriterien kombinieren können. In unserem Beispiel sind dies die Spalten Kundenname (Kunde) è Название продукта (Produkt). Vergessen Sie nicht, dass die zusammengeführte Spalte immer die Spalte ganz links im Suchbereich sein muss, da es die linke Spalte ist, die die Funktion enthält VPR schaut nach oben, wenn nach einem Wert gesucht wird.
Sie fügen der Tabelle also eine Hilfsspalte hinzu und kopieren die folgende Formel über alle ihre Zellen: =B2&C2. Wenn Sie möchten, dass die Zeichenfolge besser lesbar ist, können Sie die kombinierten Werte mit einem Leerzeichen trennen: =B2&» «&C2. Danach können Sie die folgende Formel verwenden:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Wo ist die Zelle B1 enthält den verketteten Wert des Arguments Lookup-Wert (Suchwert) und 4 - Streit col_index_num (column_number), dh die Nummer der Spalte, die die abzurufenden Daten enthält.
Beispiel 2: SVERWEIS nach zwei Kriterien, wobei die Tabelle auf einem anderen Blatt angezeigt wird
Wenn Sie die Haupttabelle (Haupttabelle) aktualisieren müssen, indem Sie Daten aus der zweiten Tabelle (Nachschlagetabelle) hinzufügen, die sich auf einem anderen Blatt oder in einer anderen Excel-Arbeitsmappe befindet, können Sie den gewünschten Wert direkt in der von Ihnen eingefügten Formel sammeln in die Haupttabelle.
Wie im vorherigen Beispiel benötigen Sie eine Hilfsspalte in der Nachschlagetabelle mit den kombinierten Werten. Diese Spalte muss die Spalte ganz links im Suchbereich sein.
Also die Formel mit VPR könnte so sein:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Hier enthalten die Spalten B und C Kundennamen bzw. Produktnamen und den Link Bestellungen!$A&$2:$D$2 definiert eine Tabelle, die in einem anderen Blatt nachgeschlagen werden soll.
Um die Formel besser lesbar zu machen, können Sie dem Ansichtsbereich einen Namen geben, dann sieht die Formel viel einfacher aus:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Damit die Formel funktioniert, müssen die Werte in der linken Spalte der Tabelle, die Sie gerade betrachten, genauso kombiniert werden wie in den Suchkriterien. In der obigen Abbildung haben wir die Werte u2bu2band kombiniert und ein Leerzeichen dazwischen gesetzt, genauso wie Sie es im ersten Argument der Funktion tun müssen (BXNUMX& „“ & CXNUMX).
Merken! Funktion VPR auf 255 Zeichen begrenzt ist, kann nicht nach Werten gesucht werden, die länger als 255 Zeichen sind. Beachten Sie dies und stellen Sie sicher, dass die Länge des gewünschten Werts diese Grenze nicht überschreitet.
Ich stimme zu, dass das Hinzufügen einer Hilfsspalte nicht die eleganteste und nicht immer akzeptable Lösung ist. Sie können dasselbe ohne die Hilfsspalte tun, aber das würde eine viel komplexere Formel mit einer Kombination von Funktionen erfordern INDEX (INDEX) und PASSEN ZU (MEHR AUSGESETZT).
Wir extrahieren den 2., 3. usw. Wert mit SVERWEIS
Du weißt das schon VPR kann nur einen übereinstimmenden Wert zurückgeben, genauer gesagt den ersten gefundenen. Aber was ist, wenn dieser Wert mehrmals im angezeigten Array wiederholt wird und Sie den 2. oder 3. davon extrahieren möchten? Was ist, wenn alle Werte? Das Problem scheint kompliziert, aber die Lösung existiert!
Angenommen, eine Spalte der Tabelle enthält die Namen der Kunden (Kundenname) und die andere Spalte die Produkte (Produkt), die sie gekauft haben. Versuchen wir, den 2., 3. und 4. Artikel zu finden, den ein bestimmter Kunde gekauft hat.
Am einfachsten ist es, vor der Spalte eine Hilfsspalte einzufügen Kundenname und füllen Sie es mit Kundennamen mit der Wiederholungsnummer jedes Namens, zum Beispiel, John Doe1, John Doe2 usw. Wir werden den Trick mit der Nummerierung mit der Funktion machen COUNTIF (COUNTIF), da die Kundennamen in Spalte B stehen:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Danach können Sie die normale Funktion verwenden VPRum die gewünschte Reihenfolge zu finden. Zum Beispiel:
- Finden Sie 2. Artikel vom Kunden bestellt Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Finden Sie 3. Artikel vom Kunden bestellt Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Tatsächlich können Sie anstelle von Text einen Zellbezug als Suchwert eingeben, wie in der folgenden Abbildung gezeigt:
Wenn Sie nur suchen 2-e Wiederholung können Sie auf die Hilfsspalte verzichten, indem Sie eine komplexere Formel erstellen:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
In dieser Formel:
- $ F $ 2 – eine Zelle mit dem Namen des Käufers (dieser bleibt unverändert, bitte beachten Sie – der Link ist absolut);
- $B$ - Säule Kundenname;
- Table4 – Ihr Tisch (dieser Ort kann auch ein regulärer Bereich sein);
- $ C16 – die Endzelle Ihrer Tabelle oder Ihres Bereichs.
Diese Formel findet nur den zweiten übereinstimmenden Wert. Wenn Sie die verbleibenden Wiederholungen extrahieren müssen, verwenden Sie die vorherige Lösung.
Wenn Sie eine Liste aller Übereinstimmungen benötigen – die Funktion VPR Dies ist kein Helfer, da es jeweils nur einen Wert zurückgibt – Zeitraum. Aber Excel hat eine Funktion INDEX (INDEX), der diese Aufgabe problemlos bewältigen kann. Wie eine solche Formel aussehen wird, erfahren Sie im folgenden Beispiel.
Rufen Sie alle Wiederholungen des gewünschten Werts ab
Wie oben erwähnt VPR kann nicht alle doppelten Werte aus dem gescannten Bereich extrahieren. Dazu benötigen Sie eine etwas komplexere Formel, die sich aus mehreren Excel-Funktionen zusammensetzt, wie z INDEX (INDEX), SMALL (KLEIN) und REIHE (LINIE)
Die folgende Formel findet beispielsweise alle Wiederholungen des Werts aus Zelle F2 im Bereich B2:B16 und gibt das Ergebnis aus denselben Zeilen in Spalte C zurück.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Geben Sie diese Matrixformel in mehrere benachbarte Zellen ein, z. B. die Zellen F4: F8wie in der Abbildung unten gezeigt. Die Anzahl der Zellen muss gleich oder größer als die maximal mögliche Anzahl von Wiederholungen des gesuchten Werts sein. Anklicken nicht vergessen Strg + Umschalt + Enterum die Matrixformel richtig einzugeben.
Wenn Sie wissen möchten, wie es funktioniert, lassen Sie uns ein wenig in die Details der Formel eintauchen:
Teil 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – Vergleichen Sie den Wert in Zelle F2 mit jedem der Werte im Bereich B2:B16. Wenn eine Übereinstimmung gefunden wird, wird der Ausdruck STRING(C2:C16)-1 liefert die Nummer der entsprechenden Zeile (value -1 ermöglicht es Ihnen, die Kopfzeile nicht einzuschließen). Wenn es keine Übereinstimmungen gibt, wird die Funktion IF (IF) gibt eine leere Zeichenfolge zurück.
Funktionsergebnis IF (IF) wird es ein solches horizontales Array geben: {1,"",3,"",5,"","","","","","",12,"","",""}
Teil 2:
ROW()-3
СТРОКА()-3
Hier die Funktion REIHE (LINE) fungiert als zusätzlicher Zähler. Da die Formel in die Zellen F4:F9 kopiert wird, subtrahieren wir die Zahl 3 vom Funktionsergebnis zum Abrufen des Werts 1 in der Zelle F4 (Zeile 4, subtrahiere 3), um zu erhalten 2 in der Zelle F5 (Zeile 5, subtrahiere 3) und so weiter.
Teil 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktion SMALL (KLEIN) kehrt zurück n-oh der kleinste Wert im Datenarray. In unserem Fall wird durch die Funktion bestimmt, welche Position (von der kleinsten) zurückzugeben ist REIHE (LINE) (siehe Teil 2). Also für eine Zelle F4 Funktion KLEIN({Array},1) Rückgabe 1. (kleinstes) Array-Element, dh 1. Für Zelle F5 Rückgabe 2. das kleinste Element im Array, das heißt 3, usw.
Teil 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktion INDEX (INDEX) gibt einfach den Wert einer bestimmten Zelle in einem Array zurück C2:C16. Für Zelle F4 Funktion INDEX($2$:$16) wird zurückkehren ÄpfelAussichten für F5 Funktion INDEX($2$:$16) wird zurückkehren Süßigkeiten' und so weiter.
Teil 5:
IFERROR()
ЕСЛИОШИБКА()
Schließlich fügen wir die Formel in die Funktion ein IFERROR (IFERROR), da Ihnen die Fehlermeldung wahrscheinlich nicht gefallen wird #at (#N/A), wenn die Anzahl der Zellen, in die die Formel kopiert wird, kleiner ist als die Anzahl der doppelten Werte im angezeigten Bereich.
XNUMXD Suche nach bekannter Zeile und Spalte
Das Durchführen einer XNUMXD-Suche in Excel umfasst die Suche nach einem Wert anhand einer bekannten Zeilen- und Spaltennummer. Mit anderen Worten, Sie extrahieren den Zellenwert am Schnittpunkt einer bestimmten Zeile und Spalte.
Wenden wir uns also unserer Tabelle zu und schreiben eine Formel mit einer Funktion VPR, das Informationen über die Kosten der im März verkauften Zitronen finden wird.
Es gibt mehrere Möglichkeiten, eine XNUMXD-Suche durchzuführen. Sehen Sie sich die Optionen an und wählen Sie diejenige, die am besten zu Ihnen passt.
VLOOKUP- und MATCH-Funktionen
Sie können eine Reihe von Funktionen verwenden VPR (VLOOKUP) und AUSGESETZTER (VERGLEICH), um den Wert am Schnittpunkt der Felder zu finden Название продукта (Zeichenfolge) und Monat (Spalte) des betreffenden Arrays:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Die obige Formel ist eine reguläre Funktion VPR, das in den Zellen A2 bis A9 nach einer genauen Übereinstimmung mit dem Wert „Zitronen“ sucht. Da Sie jedoch nicht wissen, in welcher Spalte sich die März-Verkäufe befinden, können Sie die Spaltennummer für das dritte Funktionsargument nicht festlegen. VPR. Stattdessen wird die Funktion verwendet AUSGESETZTERum diese Spalte zu definieren.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Übersetzt in die menschliche Sprache bedeutet diese Formel:
- Wir suchen nach den Zeichen „Mar“ – Argument Lookup-Wert (Lookup-Wert);
- Suchen in den Zellen von A1 bis I1 – Argument lookup_array (lookup_array);
- Genaue Übereinstimmung zurückgeben – Argument Übereinstimmungstyp (Übereinstimmungstyp).
Die richtigen 0 im dritten Argument sagen Sie Funktionen AUSGESETZTER Suchen Sie nach dem ersten Wert, der genau mit dem gesuchten Wert übereinstimmt. Dies entspricht dem Wert FALSCH (FALSCH) für das vierte Argument VPR.
So können Sie in Excel eine bidirektionale Suchformel erstellen, die auch als zweidimensionale Suche oder bidirektionale Suche bezeichnet wird.
SUMPRODUCT-Funktion
Funktion SUMMENPRODUKT (SUMPRODUCT) gibt die Summe der Produkte der ausgewählten Arrays zurück:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX- und MATCH-Funktionen
Im nächsten Artikel werde ich diese Funktionen im Detail erklären, also kannst du vorerst einfach diese Formel kopieren:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Benannte Bereiche und der Schnittpunktoperator
Wenn Sie all diese komplexen Excel-Formeln nicht mögen, gefällt Ihnen vielleicht diese visuelle und einprägsame Methode:
- Wählen Sie die Tabelle aus, öffnen Sie die Registerkarte Formeln (Formeln) und klicken Sie auf Aus Auswahl erstellen (Aus Auswahl erstellen).
- Markiere das Feld Oberste Reihe (auf der Zeile oben) und Linke Spalte (in der linken Spalte). Microsoft Excel weist den Bereichen aus den Werten in der oberen Zeile und der linken Spalte Ihrer Tabelle Namen zu. Jetzt können Sie direkt nach diesen Namen suchen, ohne Formeln zu erstellen.
- Schreiben Sie in eine beliebige leere Zelle =Zeilenname Spaltenname, zum Beispiel so:
=Zitronen Mär
… oder umgekehrt:
=Mar-Zitronen
Denken Sie daran, dass die Zeilen- und Spaltennamen durch ein Leerzeichen getrennt werden müssen, was in diesem Fall wie der Schnittmengenoperator funktioniert.
Wenn Sie einen Namen eingeben, zeigt Microsoft Excel eine QuickInfo mit einer Liste übereinstimmender Namen an, genau wie bei der Eingabe einer Formel.
- Öffentlichkeitsarbeit/Presse Enter und überprüfe das Ergebnis
Im Allgemeinen ist das Ergebnis einer zweidimensionalen Suche unabhängig davon, welche der oben genannten Methoden Sie wählen, dasselbe:
Verwenden mehrerer SVERWEISE in einer Formel
Es kommt vor, dass die Haupttabelle und die Nachschlagetabelle keine einzige Spalte gemeinsam haben, was Sie daran hindert, die übliche Funktion zu verwenden VPR. Es gibt jedoch eine andere Tabelle, die nicht die Informationen enthält, an denen wir interessiert sind, die jedoch eine gemeinsame Spalte mit der Haupttabelle und der Nachschlagetabelle hat.
Schauen wir uns das folgende Beispiel an. Wir haben eine Haupttabelle mit einer Spalte Artikelnummer (neu), wo Sie eine Spalte mit den entsprechenden Preisen aus einer anderen Tabelle hinzufügen möchten. Zusätzlich haben wir 2 Nachschlagetabellen. Die erste (Nachschlagetabelle 1) enthält aktualisierte Zahlen Artikelnummer (neu) und Produktnamen und die zweite (Nachschlagetabelle 2) – Produktnamen und alte Nummern Artikelnummer (alt).
Um Preise aus der zweiten Nachschlagetabelle zur Haupttabelle hinzuzufügen, müssen Sie eine Aktion ausführen, die als Verdoppeln bekannt ist VPR oder verschachtelt VPR.
- Schreiben Sie eine Funktion VPR, das den Produktnamen in der Tabelle findet Suchtabelle 1Verwendung von SKU, als Sollwert:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Hier Neue_SKU – benannter Bereich $A:$B in der Tabelle Suchtabelle 1, 2 – das ist Spalte B, die die Warennamen enthält (siehe Bild oben)
- Schreiben Sie eine Formel, um Preise aus einer Tabelle einzufügen Suchtabelle 2 basierend auf bekannten Produktnamen. Fügen Sie dazu die zuvor erstellte Formel als Nachschlagewert für die neue Funktion ein VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Hier PREISLISTE – benannter Bereich $ A: $ C. in der Tabelle Suchtabelle 2, 3 ist Spalte C, die Preise enthält.
Die folgende Abbildung zeigt das Ergebnis der von uns erstellten Formel:
Dynamische Substitution von Daten aus verschiedenen Tabellen mit SVERWEIS und INDIREKT
Lassen Sie uns zunächst klären, was wir mit dem Ausdruck „Dynamische Substitution von Daten aus verschiedenen Tabellen“ meinen, um sicherzustellen, dass wir uns richtig verstehen.
Es gibt Situationen, in denen es mehrere Blätter mit Daten im gleichen Format gibt und es notwendig ist, die erforderlichen Informationen aus einem bestimmten Blatt zu extrahieren, abhängig von dem Wert, der in eine bestimmte Zelle eingegeben wird. Ich denke, es ist einfacher, dies an einem Beispiel zu erklären.
Stellen Sie sich vor, Sie haben Verkaufsberichte für mehrere Regionen mit den gleichen Produkten und im gleichen Format. Sie möchten Verkaufszahlen für eine bestimmte Region finden:
Wenn Sie nur zwei solcher Berichte haben, können Sie eine schändlich einfache Formel mit Funktionen verwenden VPR и IF (IF), um den gewünschten Bericht für die Suche auszuwählen:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Wo:
- $D$2 ist eine Zelle, die den Namen des Produkts enthält. Beachten Sie, dass wir hier absolute Bezüge verwenden, um zu vermeiden, dass der Nachschlagewert geändert wird, wenn die Formel in andere Zellen kopiert wird.
- $ D3 ist eine Zelle mit dem Namen der Region. Wir verwenden eine absolute Spaltenreferenz und eine relative Zeilenreferenz, da wir die Formel in andere Zellen in derselben Spalte kopieren möchten.
- FL_Sales и CA_Sales – die Namen der Tabellen (oder benannten Bereiche), die die entsprechenden Verkaufsberichte enthalten. Sie können natürlich zum Beispiel die üblichen Blattnamen und Zellbereichsbezüge verwenden 'FL-Blatt'!$A$3:$B$10, aber benannte Bereiche sind viel bequemer.
Wenn es jedoch viele solcher Tabellen gibt, wird die Funktion IF ist nicht die beste Lösung. Stattdessen können Sie die Funktion verwenden INDIREKT (INDIREKT), um den gewünschten Suchbereich zurückzugeben.
Wie Sie wahrscheinlich wissen, ist die Funktion INDIREKT wird verwendet, um einen durch eine Textzeichenfolge gegebenen Link zurückzugeben, was genau das ist, was wir jetzt brauchen. Ersetzen Sie also in der obigen Formel mutig den Ausdruck durch die Funktion IF mit Funktion verknüpfen INDIREKT. Hier ist eine Kombination VPR и INDIREKT funktioniert gut mit:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Wo:
- $D$2 – dies ist eine Zelle mit dem Namen des Produkts, sie bleibt aufgrund der absoluten Verknüpfung unverändert.
- $ D3 ist die Zelle, die den ersten Teil des Regionsnamens enthält. In unserem Beispiel diese FL.
- _Verkauf – der gemeinsame Teil des Namens aller benannten Bereiche oder Tabellen. Wenn es mit dem Wert in Zelle D3 kombiniert wird, bildet es den vollständig qualifizierten Namen des erforderlichen Bereichs. Nachfolgend finden Sie einige Details für diejenigen, die neu in der Funktion sind INDIREKT.
Funktionsweise von INDIREKT und SVERWEIS
Zunächst möchte ich Sie an die Syntax der Funktion erinnern INDIREKT (INDIREKT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Das erste Argument kann ein Zellbezug (A1- oder R1C1-Stil), ein Bereichsname oder eine Textzeichenfolge sein. Das zweite Argument bestimmt, welcher Link-Stil im ersten Argument enthalten ist:
- A1wenn das Argument ist ECHTER CODE (WAHR) oder nicht angegeben;
- R1C1Wenn FAS E (FALSCH).
In unserem Fall hat der Link den Stil A1, sodass Sie das zweite Argument weglassen und sich auf das erste konzentrieren können.
Kommen wir also zurück zu unseren Verkaufsberichten. Wenn Sie sich erinnern, ist jeder Bericht eine separate Tabelle, die sich auf einem separaten Blatt befindet. Damit die Formel richtig funktioniert, müssen Sie Ihre Tabellen (oder Bereiche) benennen, und alle Namen müssen einen gemeinsamen Teil haben. Zum Beispiel so: CA_Sales, FL_Verkäufe, TX_Sales usw. Wie Sie sehen können, ist „_Sales“ in allen Namen vorhanden.
Funktion INDIREKT verbindet den Wert in Spalte D und die Textzeichenfolge „_Sales“ und sagt so etwas aus VPR in welcher Tabelle gesucht werden soll. Wenn Zelle D3 den Wert „FL“ enthält, durchsucht die Formel die Tabelle FL_Verkäufe, wenn „CA“ – in der Tabelle CA_Sales und so weiter.
Das Ergebnis der Funktionen VPR и INDIREKT wird folgendes sein:
Wenn sich die Daten in verschiedenen Excel-Büchern befinden, müssen Sie den Namen des Buchs vor dem benannten Bereich hinzufügen, zum Beispiel:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ist die Funktion INDIREKT auf eine andere Arbeitsmappe verweist, muss diese Arbeitsmappe geöffnet sein. Wenn es geschlossen ist, meldet die Funktion einen Fehler. BEZUG! (#SSYL!).