Stellen Sie sich vor, Sie haben mehrere identische Blätter in einer Arbeitsmappe, die identische Tabellen enthält (z. B. Anwesenheitsaufzeichnungen jedes Monats in einem separaten Blatt). Jetzt möchten Sie ein Dashboard erstellen, das Ihnen die Gesamtanwesenheit für den Monat anzeigt. Jetzt ist es keine gute Option, die Daten jedes Monats gleichzeitig auf dem Dashboard zu haben. Wir möchten ein Dropdown-Menü, um den Monat auszuwählen. Wir benötigen eine SVERWEIS-Formel, die wir aus dem ausgewählten Monatsblatt betrachten können.
In einfachen Worten, wir brauchen eine Nachschlageformel, um aus variablen Blättern nachzuschlagen.
Wie das obige GIF zeigt, verwenden wir die SVERWEIS- und die INDIREKT-Funktion zusammen, um anhand ihrer Namen aus mehreren Blättern nachzuschlagen.
Allgemeine Formel zum Nachschlagen mehrerer Blätter
| =SVERWEIS(Lookup-Wert,INDIREKT(""&blatt_name_referenz&"!lookup_table"),col_index,0) |
Lookup-Wert: Dies ist der Wert, nach dem Sie in der Suche suchen Tisch.
Tabellenname_Referenz: Dies ist die Referenz der Zelle, die den Namen des Blatts enthält.
Nachschlagwerk: Dies ist die Tabellenreferenz, nach der Sie suchen möchten Lookup-Wert. Dies kann ein benannter Bereich, eine Tabelle oder eine absolute Referenz sein. Es sollte in allen Blättern gleich sein.
Col_Index: Dies ist die Spaltennummer in der Tabelle, aus der Sie den Wert abrufen möchten. Wenn Sie mit der SVERWEIS-Funktion vertraut sind, wissen Sie, was sie ist.
Lassen Sie uns also auf ein Beispiel springen.
Beispiel: Abrufen der Anwesenheit für den ausgewählten Monat
Wir haben also ein Arbeitsbuch, das die Anwesenheit meiner Excel-Studenten aufrechterhält. Die Daten jedes Monats werden separat in verschiedenen Blättern gespeichert. Der Name des Blattes wird auf den Namen von Monaten gesetzt. Im Moment habe ich Daten für drei Monate, aber es wird natürlich mehr geben.
Ich möchte einen Bericht erstellen, der die Anwesenheit des ausgewählten Monats anzeigt. Der Monat kann aus einer Dropdown-Liste ausgewählt werden. Die Formel sollte in der Lage sein, von diesem Blatt automatisch nachzuschlagen, auch wenn ein neues Blatt hinzugefügt wird.
Also bereiten wir die obige Tabelle vor. Die Zelle G3 haben wir eine Dropdown-Liste mit einer Dropdown-Liste erstellt.
Der Lookup-Wert ist in B4. Die sheet_name_reference ist in G3. Die Nachschlagetabelle in allen Blättern ist B3:AZ100. Wir möchten Wert aus 2 Spalten abrufen. Also schreiben wir diese Formel in C4 und ziehen sie nach unten. Ebenso ändern wir bei fehlenden Werten den Spaltenindex.
| =SVERWEIS(B4,INDIREKT(""&$G$3&"!$B$3:$Az$100"),2,0) |
Wie funktioniert es?
Die Formel wird von innen nach außen gelöst. Lassen Sie uns zuerst sehen, wie es Schritt für Schritt gelöst wird.
Angenommen, G3 enthält Jan.
| =SVERWEIS(B4,INDIREKT(""&$G$3&"!$B$3:$Az$100"),2,0) |
| =SVERWEIS(B4,INDIREKT("Jan!$B$3:$Az$100"),2,0) |
| =SVERWEIS(B4,Januar!$B$3:$Az$100,2,0) |
| =7 |
Also wird zuerst die Anweisung ""&$G$3&"!$B$3:$Az$100" in einen String aufgelöst "Jan!$B$3:$Az$100". Als nächstes wandelt die INDIRECT-Funktion diese Zeichenfolge in eine tatsächliche Referenz um. Und schließlich haben wir die Formel SVERWEIS(B4,Jan!$B$3:$Az$100,2,0). Und dies wird schließlich zu 7 aufgelöst. Wenn Sie nun den Blattnamen im G3 ändern, wird der Wertreferenztext geändert. Und so schauen Sie von variablen Blättern in Excel nach oben.
Ich hoffe, das war hilfreich für Sie. Wenn Sie Fragen haben oder eine andere Suche durchführen möchten, lassen Sie es mich im Kommentarbereich unten wissen. Ich helfe Ihnen gerne. Bis dahin bleiben Sie Excelling.
Verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen | Um aus mehreren Tabellen zu suchen, können wir einen IFERROR-Ansatz verwenden. Das Nachschlagen von mehreren Tabellen nimmt den Fehler als Wechsel zur nächsten Tabelle. Eine andere Methode kann ein If-Ansatz sein.
So führen Sie die Groß-/Kleinschreibung in Excel durch | Die SVERWEIS-Funktion von Excel unterscheidet nicht zwischen Groß- und Kleinschreibung und gibt den ersten übereinstimmenden Wert aus der Liste zurück. INDEX-MATCH ist keine Ausnahme, kann jedoch geändert werden, um die Groß-/Kleinschreibung zu berücksichtigen. Mal sehen wie…
Nachschlagen von häufig erscheinendem Text mit Kriterien in Excel | Die Suche erscheint am häufigsten im Text in einem Bereich, den wir mit der Funktion INDEX-MATCH mit MODE verwenden. Hier ist die Methode.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Tabellen nachzuschlagen.
So verwenden Sie Excel ZÄHLENWENN-Funktion| 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.