So schreiben Sie eine Excel-Formel, mit der ein Wert auf einem anderen Blatt basierend auf dem ausgewählten Produkt gesucht wird. So ziehen Sie Daten für jedes Produkt aus einem anderen Arbeitsblatt.
Schau Video
- Rhonda aus Cincinnati: Wie kann man sowohl die Zeile als auch das Arbeitsblatt nachschlagen?
- Verwenden Sie die Spalte Datum, um herauszufinden, welches Blatt verwendet werden soll
- Schritt 1: Erstellen Sie einen regulären VLOOKUP und verwenden Sie FORMULATEXT, um zu sehen, wie die Referenz aussehen soll
- Schritt 2: Verwenden Sie Verkettung und die TEXT-Funktion, um eine Referenz zu erstellen, die der Tabellenarray-Referenz in der Formel ähnelt
- Schritt 3: Erstellen Sie Ihren VLOOKUP, verwenden Sie jedoch für das Tabellenarray INDIRECT (Ergebnisse aus Schritt 2).
- Schritt 4: Kopieren Sie die Formel aus Schritt 2 (ohne Gleichheitszeichen) und fügen Sie sie aus Schritt 3 in die Formel ein
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2173: Blatt und Zeile nachschlagen.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Ich war letzte Woche in Cincinnati und Rhonda in Cincinnati hatte diese großartige Frage. Rhonda muss dieses Produkt nachschlagen, aber die Nachschlagetabelle ist je nach Monat unterschiedlich. Wir haben hier verschiedene Nachschlagetabellen für Januar bis April und vermutlich auch für die anderen Monate. In Ordung.
Also werde ich INDIRECT verwenden, um dies zu lösen, aber bevor ich INDIRECT mache, finde ich es immer einfacher, nur einen direkten VLOOKUP zu machen. So können wir sehen, wie das Formular aussehen wird. Wir suchen also im Januar in dieser Tabelle nach A1 und möchten, dass die siebte Spalte, Komma FALSE, alle VLOOKUPs mit FALSE enden. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). In Ordung.
Und das ist die richtige Antwort. Was mich wirklich interessiert, ist, den Formeltext dafür zu bekommen. Es zeigt mir also, wie die Formel aussehen wird. Und der ganze Trick hier ist, ich versuche eine Hilfsspalte zu erstellen, die genau wie diese Referenz aussehen wird, oder? Also dieser Teil - genau dieser Teil genau dort. In Ordung. Diese Hilfsspalte muss also so aussehen, wie sie aussieht. Und das erste, was ich tun möchte, ist, dass wir die TEXT-Funktion des Datums verwenden - die TEXT-Funktion des Datums -, um MMM, Leerzeichen, JJJJ - also "MMM JJJJ" so zu erhalten. - welches für jede der Zellen zurückgeben sollte, welchen Monat wir suchen. Jetzt muss ich das in Apostrophe einwickeln. Wenn es dort keinen Raumnamen gegeben hätte, würde ich die Apostrophe nicht brauchen, aber ich tue es. Also werden wir uns vorne zusammensetzen,das Apostroph, also das ist Zitat - Apostroph, Zitat - kaufmännisches Und, und hier drüben ein weiteres Zitat, Apostroph und Ausrufezeichen, A1: G13, abschließendes Zitat, kaufmännisches Und dort.
In Ordung. Was wir hier in der Helper-Spalte erfolgreich gemacht haben, ist, dass wir etwas erstellt haben, das genau wie das Tabellenarray im VLOOKUP aussieht. In Ordung. Unsere Antwort lautet also = VLOOKUP dieser Zelle, A2, Komma, und wenn wir zum Tabellenarray gelangen, verwenden wir INDIRECT. INDIREKT ist diese coole Funktion, die sagt: "Hey, hier ist eine Zelle, die wie eine Zellreferenz aussieht, und ich möchte, dass Sie zu F2 gehen, das Ding nehmen, das wie eine Zellreferenz aussieht, und dann alles in dieser Zellreferenz als verwenden Die Antwort "Komma, 7, Komma, FALSCH" lautet wie folgt. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSCH)) Okay, jetzt wählen wir im laufenden Betrieb a Unterschiedliche Nachschlagetabelle und Rückgabe der Werte, je nachdem, ob es April ist oder was.
In Ordung. Nehmen wir also diesen 24.04., Ich werde ihn so auf den 17.02.2008 ändern, und wir sollten sehen, dass 403 auf 203 geändert wird - perfekt. Es funktioniert. In Ordung. Jetzt brauchen wir diese beiden Spalten hier natürlich nicht, und wenn Sie darüber nachdenken, brauchen wir wirklich nicht diese ganze Spalte. Wir könnten das Ganze nehmen, bis auf das Gleichheitszeichen Strg + C, um es zu kopieren, und dann, wo wir D2 haben, einfach so einfügen. Perfekt. Doppelklicken Sie, um das abzuschießen und es loszuwerden. Da ist unsere Antwort. Okay, wir werden unseren Formeltext hier verwenden, um uns diese endgültige Antwort anzusehen.
Ich muss Ihnen sagen, wenn ich diese Formel nur von Grund auf neu erstellen müsste, würde ich es nicht tun. Ich würde es nicht schaffen. Ich würde es auf jeden Fall vermasseln. Deshalb baue ich es immer in Schritten auf - ich finde heraus, wie die Formel aussehen wird, und konkretisiere dann die Hilfsspalte, die in INDIRECT verwendet wird, und setze schließlich, vielleicht hier am Ende, alles wieder zusammen.
Hey, viele Tipps wie dieser Tipp im Buch Power Excel mit. Dies ist die 2017 Edition mit 617 Excel Rätsel gelöst. Klicken Sie oben rechts auf "I", um weitere Informationen zu erhalten.
Okay, Abschluss dieser Episode: Rhonda aus Cincinnati - wie man sowohl die Zeile als auch das Arbeitsblatt nachschlägt. Ich benutze die Spalte Datum, um herauszufinden, welches Blatt verwendet werden soll. Also erstelle ich ein reguläres VLOOKUP und verwende Formeltext, um zu sehen, wie die Referenz aussehen soll. und dann etwas erstellen, das wie diese Referenz aussieht, indem Sie die Textfunktion verwenden, um das Datum in einen Monat und ein Jahr umzuwandeln; Verwenden Sie Concactenation, um etwas zu erstellen, das der Referenz ähnelt. Wenn Sie dann Ihr VLOOKUP für das zweite Argument, das Tabellenarray, erstellen, verwenden Sie INDIRECT. und dann auf die Ergebnisse aus Schritt 2 zeigen; und dann den optionalen vierten Schritt dort, kopieren Sie die Formel aus Schritt 2 ohne Gleichheitszeichen und fügen Sie sie in die Formel aus Schritt 3 ein, sodass Sie eine einzige Formel erhalten.
Nun, ich möchte Rhonda dafür danken, dass sie zu meinem Seminar in Cincinnati erschienen ist, und ich möchte Ihnen dafür danken, dass Sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2173.xlsm
