VLOOKUP ist großartig und meine Lieblingsfunktion
Diese Tabellen erleichtern nicht nur das Aktualisieren der Daten, sondern auch das Lesen von Formeln erheblich! Sie müssen lediglich Strg + T drücken, bevor Sie die Formel schreiben.
Kehren wir von oben zur VLOOKUP-Formel zurück. Konvertieren Sie diesmal Ihre Artikeltabelle und Ihre Einkaufstabelle von Anfang an mit Strg + T in eine Excel-Tabelle! Geben Sie jeder Tabelle auf der Registerkarte Tabellentools einen Anzeigenamen, um die Arbeit zu vereinfachen:
Geben Sie nun den VLOOKUP erneut ein, ohne etwas anderes zu tun als normalerweise. Ihre Formel in C2 lautet jetzt =VLOOKUP((@Item),Items,2,0)statt =VLOOKUP(B2,$E$5:$F$10,2,0)!
Selbst wenn sich die Elementtabelle in einem anderen Arbeitsblatt befindet, ist die Formel dieselbe und nicht die weniger lesbare =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
Das (@Item) in der Formel bezieht sich auf die Zelle in der Spalte Item dieser Tabelle (in derselben Zeile wie die Formel) und ist daher in der gesamten Spalte identisch. Und Elemente bezieht sich auf die gesamte Elementtabelle (ohne die Überschriften). Das Beste ist, dass Sie nichts davon eingeben müssen. Sobald dies eine Tabelle ist, fügt Excel diese Namen in Ihre Formel ein, wenn Sie die Zellen / Bereiche auswählen!
Gehen wir noch einen Schritt weiter. Fügen Sie der Verkaufstabelle eine weitere Spalte hinzu, um den Umsatz mit der Formel zu berechnen =(@Price)*(@Qty). Wenn Sie jetzt den Gesamtumsatz berechnen möchten, lautet die Formel =SUM(Sales(Revenue)): Das ist wirklich leicht zu verstehen, egal wo sich die Daten befinden oder wie viele Zeilen sie abdecken!
Schau Video
- VLOOKUP ist großartig und meine Lieblingsfunktion
- VLOOKUP-Hasser beklagen, dass es aufgrund des dritten Arguments fragil ist
- Wenn sich die Form Ihrer Nachschlagetabelle ändert, können sich die Antworten ändern
- Eine Problemumgehung besteht darin, das dritte Argument durch MATCH zu ersetzen
- Aber stellen Sie sich vor, Sie machen ein MATCH für 1000 Zeilen VLOOKUP
- Machen Sie Ihre Nachschlagetabelle zu einer Tabelle, bevor Sie den VLOOKUP ausführen
- Die strukturierte Tabellenreferenz wird behandelt, wenn sich die Tabellenform ändert
- Außerdem muss nicht immer wieder ein MATCH durchgeführt werden
- Peter Albert hat diesen Tipp eingereicht
Video-Transkript
Lernen Sie Excel für Podcast, Episode 2003 - Lesbare Referenzen
Vergessen Sie nicht, die XL-Wiedergabeliste zu abonnieren. Ich werde das ganze Buch podcasten.
Okay, der heutige Tipp von Peter Albert. Peter Albert. Lassen Sie uns jetzt über VLOOKUP sprechen. Ich bin ein großer VLOOKUP-Fan. Für mich ist VLOOKUP die Trennlinie. Wenn Sie VLOOKUPs ausführen können, wird Ihnen alles andere in Excel leicht fallen. Mit VLOOKUP können wir also den Preis von dieser Tabelle aus nachschlagen, und wir werden später mehr über VLOOKUPs sprechen.
Also kopiere das runter und alles funktioniert in Ordnung, aber ich muss es dir sagen. Ich habe sie gesehen. Ich habe mit ihnen gesprochen. Ich habe sie getroffen. Es gibt VLOOKUP-Hasser da draußen. Leute, die es hassen, wenn Sie nachschlagen und andere Beschwerden haben, dass es so fragil ist, dieses dritte Argument, bei dem wir sagten, wir wollen die dritte Spalte, dass wir, wenn jemand später entscheiden würde, dass wir hier ein neues Feld brauchen, vielleicht wie Größe . Okay, zunächst einmal scheint es eine Art Fehler zu geben, bei dem Excel das Ganze nicht neu berechnet. Lassen Sie mich rückgängig machen, rückgängig machen und dann wiederholen. Na, bitte. Das ist seltsam, ich muss das dem Excel-Team melden, aber Sie sehen, dass dort, wo wir den Preis bekommen haben, es jetzt Farbe bekommt, weil es schwer zu sagen war, dass sie die dritte Spalte wollen. Okay und was die Leute tun, um das zu umgehen, ist diese verrückte Sache mit = MATCH.Suchen Sie in der ersten Zeile der Tabelle nach dem Wort Preis, F4,0, und das sagt uns, dass der Preis an dieser Stelle die vierte Spalte ist. Also machen sie tatsächlich = VLOOKUP. Wir suchen in dieser Tabelle nach A104. F4 und dann, anstatt die Nummer vier fest zu codieren, machen sie ein MATCH und das MATCH wird auf den Preis festgelegt. Also F4, zweimal, um $ vor die 1 zu setzen, und es wird durch die erste Zeile der Tabelle schauen. Ups, F4 zweimal, Komma, hat das Komma verpasst. Okay, drücke hier F4 Komma 0 für eine genaue Übereinstimmung mit der Übereinstimmung und dann fällt Komma für eine genaue Übereinstimmung mit dem VLOOKUP. Ja und hey, das funktioniert großartig und hier habe ich nur sechs davon, also ist es keine große Sache.in dieser Tabelle. F4 und dann, anstatt die Nummer vier fest zu codieren, machen sie ein MATCH und das MATCH wird auf den Preis festgelegt. Also F4, zweimal, um $ vor die 1 zu setzen, und es wird durch die erste Zeile der Tabelle schauen. Ups, F4 zweimal, Komma, hat das Komma verpasst. Okay, drücke hier F4 Komma 0 für eine genaue Übereinstimmung mit der Übereinstimmung und dann fällt Komma für eine genaue Übereinstimmung mit dem VLOOKUP. Ja und hey, das funktioniert großartig und hier habe ich nur sechs davon, also ist es keine große Sache.in dieser Tabelle. F4 und dann, anstatt die Nummer vier fest zu codieren, machen sie ein MATCH und das MATCH wird auf den Preis festgelegt. Also F4, zweimal, um $ vor die 1 zu setzen, und es wird durch die erste Zeile der Tabelle schauen. Ups, F4 zweimal, Komma, hat das Komma verpasst. Okay, drücke hier F4 Komma 0 für eine genaue Übereinstimmung mit der Übereinstimmung und dann fällt Komma für eine genaue Übereinstimmung mit dem VLOOKUP. Ja und hey, das funktioniert großartig und hier habe ich nur sechs davon, also ist es keine große Sache.Okay, drücke hier F4 Komma 0 für eine genaue Übereinstimmung mit der Übereinstimmung und dann fällt Komma für eine genaue Übereinstimmung mit dem VLOOKUP. Ja und hey, das funktioniert großartig und hier habe ich nur sechs davon, also ist es keine große Sache.Okay, drücke hier F4 Komma 0 für eine genaue Übereinstimmung mit der Übereinstimmung und dann fällt Komma für eine genaue Übereinstimmung mit dem VLOOKUP. Ja und hey, das funktioniert großartig und hier habe ich nur sechs davon, also ist es keine große Sache.
Sehen Sie, ob ich einen neuen einfüge, der sich automatisch anpasst und den Preis weiterhin erhält. Stellen Sie sich jedoch vor, Sie hätten tausend VLOOKUPs und jeder einzelne VLOOKUP wird diese Übereinstimmung wiederholen, um die Preise in der fünften oder vierten Spalte herauszufinden. Es ist schrecklich. Tabellen lösen dieses Problem einfach. Also hier ist mein VLOOKUP-Tisch, sei es lange bevor ich etwas tue, ich werde hierher gehen und STRG T drücken, um daraus einen echten Tisch zu machen. Sie werden es Tabelle 1 nennen, aber ich werde es ProductTable nennen, alles ein Wort, keine Leerzeichen: ProductTable. Jetzt hat es einen Namen. Okay, jetzt haben wir eine Tabelle mit dem Namen ProductTable. Dann kommen wir hierher und sagen, wir machen = INDEX dieser Preise. Welchen Preis wollen wir? Wir wollen das Ergebnis aus der Übereinstimmung von A104 mit diesen Elementen. Genaue Übereinstimmung, schließen Sie die Klammern für den INDEX.Dies ist nur ein einziges Match. Es ist kein Match und kein VLOOKUP. Art, wird viel, viel schneller sein. Kopieren Sie das nach unten. In Ordnung und später, wenn wir die Größe einfügen, also Spalte einfügen, Größe funktioniert alles weiter, da nach der Spalte "Preis" gesucht wird. Wenn wir dies in "Listenpreis" ändern, wird diese Formel neu geschrieben. Richtig, so viel, viel sicherer, sicherer Weg.
Okay, so viele coole Tricks in Tischen. Schauen Sie sich dieses Buch von Kevin Jones und Zach Barresse in Excel-Tabellen an. Alle möglichen Tricks und alles, was wir im August und September podcasten, finden Sie in diesem vollgepackten Buch. Plus viel Spaß. Excel-Witze. Excel-Cocktails. Excel-Tweets. Excel-Abenteuer. Vollfarbig verpackt. Probieren Sie es aus, kaufen Sie dieses Buch. Ich würde es wirklich schätzen.
Okay, die heutige Folge. VLOOKUP ist fantastisch und meine Lieblingsfunktion, aber es gibt VLOOKUP-Hasser, die sich darüber beschweren, dass es aufgrund dieses dritten Arguments zerbrechlich ist. Wenn sich die Form Ihrer Tabelle ändert, ändern sich die Antworten. Eine Problemumgehung besteht darin, dieses dritte Argument durch ein MATCH zu ersetzen, aber meine Güte, stellen Sie sich vor, Sie machen ein MATCH für tausend Zeilen VLOOKUP. Machen Sie Ihren VLOOKUP zu einem Tisch, bevor Sie den VLOOKUP ausführen. Die Strukturtabellenreferenzen werden behandelt, wenn sich die Tabellenform ändert. Außerdem machst du kein VLOOKUP und kein Match. Nur eine einzige Übereinstimmung zusammen mit einem INDEX und einem INDEX ist blitzschnell.
Vielen Dank an Peter Robert für diesen Tipp und an Sie für Ihren Besuch. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2003.xlsx
