Dies ist ein weiteres Beispiel für die Geschwindigkeit von Formeln. Angenommen, Sie müssen 12 Spalten von VLOOKUP ausführen. Sie können es schneller machen, indem Sie eine MATCH- und 12 INDEX-Funktionen verwenden.
In der folgenden Abbildung müssen Sie für jede Kontonummer 12 VLOOKUP-Funktionen ausführen. VLOOKUP ist leistungsstark, aber die Berechnung nimmt viel Zeit in Anspruch.
Außerdem muss die Formel in jeder Zelle beim Kopieren bearbeitet werden. Das dritte Argument muss sich für Februar von 2 auf 3 ändern, dann für März auf 4 und so weiter.
Eine Problemumgehung besteht darin, eine Zeile mit den Spaltennummern hinzuzufügen. Dann kann das dritte Argument von VLOOKUP auf diese Zeile verweisen. Zumindest können Sie dieselbe Formel aus B4 kopieren und in C4: M4 einfügen, bevor Sie den gesamten Satz kopieren.
Aber hier ist ein viel schnellerer Ansatz. Fügen Sie eine neue Spalte B mit Wo? als Überschrift. Spalte B enthält eine MATCH-Funktion. Diese Funktion ist VLOOKUP sehr ähnlich: Sie suchen den Wert in A4 in der Spalte P4: P227. Die 0 am Ende ist wie die Falsche am Ende von VLOOKUP. Es gibt an, dass Sie eine genaue Übereinstimmung wünschen. Hier ist der große Unterschied: MATCH gibt zurück, wo der Wert gefunden wird. Die Antwort von 208 besagt, dass A308 die 208. Zelle im Bereich P4: P227 ist. Aus zeitlicher Sicht sind MATCH und VLOOKUP ungefähr gleich.
Ich kann hören, was du denkst. „Was nützt es zu wissen, wo sich etwas befindet? Ich habe noch nie einen Manager anrufen lassen und gefragt: "In welcher Zeile befindet sich diese Forderung?"
Während Menschen selten fragen, in welcher Zeile sich etwas befindet, kann die INDEX-Funktion diese Position verwenden. Die folgende Formel weist Excel an, das 208. Element aus Q4 zurückzugeben: Q227.
Während Sie diese Formel kopieren, bewegt sich das Wertearray über die Nachschlagetabelle. Für jede Zeile führen Sie eine MATCH- und 12 INDEX-Funktionen aus. Die INDEX-Funktion ist im Vergleich zu VLOOKUP unglaublich schnell. Der gesamte Satz von Formeln berechnet 85% schneller als 12 Spalten von VLOOKUP.
Schau Video
- Angenommen, Sie müssen 12 Spalten von VLOOKUP ausführen
- Verwenden Sie vorsichtig ein einzelnes Dollarzeichen vor der Spalte des Suchwerts
- Verwenden Sie vorsichtig vier Dollarzeichen für die Nachschlagetabelle
- Sie codieren das Argument der dritten Spalte immer noch hart.
- Eine übliche Lösung besteht darin, eine Reihe von Hilfszellen mit der Spaltennummer hinzuzufügen.
- Eine andere weniger effiziente Lösung ist die Verwendung von COLUMN (B2) innerhalb der VLOOKUP-Formel.
- Es ist jedoch sehr ineffizient, 12 VLOOKUP für jede Zeile auszuführen
- Fügen Sie stattdessen eine Hilfsspalte mit der Überschrift WHERE hinzu und führen Sie eine einzelne Übereinstimmung durch.
- Das MATCH dauert so lange wie das VLOOKUP für Januar.
- Sie können dann 12 INDEX-Funktionen verwenden. Diese sind im Vergleich zu VLOOKUP unglaublich schnell.
- Der INDEX zeigt auf eine einzelne Spalte mit Antworten mit $ vor den Zeilen.
- Der INDEX zeigt auf die Hilfsspalte mit einem $ vor der Spalte.
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2028 - Ersetzen Sie viele VLOOKUPs durch ein MATCH!
Klicken Sie auf das "i" in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen. Ich werde das gesamte Buch podcasten!
Hey, willkommen zurück im Netcast, ich bin Bill Jelen! Nun, es ist ein klassisches Problem, wir müssen VLOOKUP einmal im Monat machen, oder? Und Sie können hier unglaublich vorsichtig sein, wenn Sie dreimal F4 drücken, um dies für die Spalte zu sperren, und dann F4 drücken, sobald die gesamte Zeile gesperrt ist. Aber wenn Sie an diesem Punkt angelangt sind, ist die, 2, FALSE, dass 2 fest codiert ist, und wenn Sie diese kopieren, müssen Sie die 2 zu einer 3 bearbeiten, richtig? Ein ineffizienter Weg, dies zu tun, den ich nicht mag, ist die Verwendung der Spalte von B1. Spalte B1 ist natürlich 2, aber wenn Sie das kopieren, sehen Sie, dass es sich in die Spalte C1 ändert, die 3 ist, aber denken Sie darüber nach, dies wird ständig die Spaltennummer immer wieder herausfinden. Also, was ich sehe, was die Leute tun und warum, wissen Sie, mehr als die Spalten bevorzugen, ist, dass wir das bei gedrückter Strg-Taste ziehen,Legen Sie die Zahlen 2-13 dort oben in eine Hilfszelle, und wenn wir an diesem Punkt angelangt sind, gehen wir nach oben und geben diese Spaltennummer an. Drücken Sie zweimal F4, um die Zeile ,, FALSE usw. zu speichern. Aber selbst mit dieser Methode ist VLOOKUP unglaublich ineffizient, da es alle diese Elemente hier durchsuchen muss, bis es A308 findet, und das ist die Zahl B4. Wenn es dann zu C4 übergeht, vergisst es, dass es nur gegangen ist und geschaut hat, und es fängt wieder von vorne an, in Ordnung. Sie haben also eine der langsamsten Funktionen in ganz Excel: VLOOKUP, FALSE wird immer und immer wieder für dasselbe Element ausgeführt.weil es alle diese Elemente hier durchsuchen muss, bis es A308 findet und das ist die Zahl B4. Wenn es dann zu C4 übergeht, vergisst es, dass es nur gegangen ist und geschaut hat, und es fängt wieder von vorne an, in Ordnung. Sie haben also eine der langsamsten Funktionen in ganz Excel: VLOOKUP, FALSE wird immer und immer wieder für dasselbe Element ausgeführt.weil es alle diese Elemente hier durchsuchen muss, bis es A308 findet und das ist die Zahl B4. Wenn es dann zu C4 übergeht, vergisst es, dass es nur gegangen ist und geschaut hat, und es fängt wieder von vorne an, in Ordnung. Sie haben also eine der langsamsten Funktionen in ganz Excel: VLOOKUP, FALSE wird immer und immer wieder für dasselbe Element ausgeführt.
Hier ist also der viel, viel schnellere Weg, wir werden eine Hilfsspalte einfügen, und diese Hilfsspalte nenne ich Wo? Wie zum Teufel ist A308? Wir werden a = MATCH verwenden, in der ersten Zeile der Tabelle nach A308 suchen, dort F4 drücken, 0 für eine genaue Übereinstimmung, in Ordnung, es sagt uns: „Hey, sieh dir das an, es ist in Zeile 6, wie Super ist das? " Aber wenn wir nach unten kopieren, ist es die ganze Zeit an verschiedenen Orten. Okay, jetzt dauert dieses Match so lange wie der Januar-VLOOKUP, da sind sie sogar tot, aber hier ist das Erstaunliche. Von dort aus müssen wir für den Rest der Zeile nie mehr einen VLOOKUP machen, wir könnten einfach = INDEX machen, INDEX sagt "Hier ist eine Reihe von Antworten." Ich werde in die Januar-Zellen gehen, und ich werde hier sehr vorsichtig zweimal F4 drücken, damit ich es auf 4: 227 sperre.aber das Q darf sich ändern, wenn ich mich bewege. Komma, und dann will es wissen, welche Zeile, nun, das wird die Antwort in B4 sein. Ich drücke dreimal F4, um das $ vor dem B zu erhalten. Okay, kopiere das rüber.
Diese Formel, diese INDEX-Formeln, diese 12 werden in weniger als der Zeit auftreten, die für die Durchführung des Februar-VLOOKUP erforderlich wäre. Wenn wir den Charles Williams-Timer darauf setzen, berechnet diese ganze Sache ungefähr 14% der Zeit von 12 VLOOKUPs. Ihr Manager möchte das Wo nicht sehen? Gut, versteck einfach diese Spalte, alles funktioniert weiter, okay, dies ist eine schöne Möglichkeit, die 12 Monate oder 52 Wochen von VLOOKUPs zu beschleunigen. Okay, dieser Tipp und so viele weitere Tipps sind in diesem Buch enthalten. Klicken Sie dort oben rechts auf das „i“. Sie können das Buch kaufen, 10 US-Dollar E-Book, 25 US-Dollar für das gedruckte Buch.
Wir hatten heute ein Problem mit 12 Spalten von VLOOKUP. Sie können das $ sorgfältig eingeben, aber dann muss das dritte Argument noch fest codiert werden. Sie könnten Spalte (B2) verwenden, ich bin kein Fan davon, weil es Hunderte von Zeilen * 12 Spalten gibt, in denen das immer wieder berechnet wird. Verwenden Sie einfach eine Hilfszelle in einer Reihe, geben Sie die Zahlen 2-12 ein und zeigen Sie darauf. Es ist immer noch ineffizient, da VLOOKUP, nachdem es den Januar herausgefunden hat, Anfang Februar beginnen muss. Ich empfehle daher, eine Spalte mit der Überschrift "Wo?" und dort ein einziges MATCH machen. Dieses MATCH dauert so lange wie der VLOOKUP für Januar, aber dann benötigen die 12 INDEX-Funktionen weniger Zeit als der VLOOKUP für Februar, und Sie haben eine ganze Menge Zeit gekürzt. Seien Sie auch hier vorsichtig mit dem $ in der INDEX-Funktion an beiden Stellen, eine kurz vor den Zeilen.und der andere vor den Spalten, eine gemischte Referenz in beiden.
Hey, ich möchte mich bei dir für deinen Besuch bedanken, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2028.xlsx
