Ron möchte ein paar VLOOKUPs machen und die Ergebnisse zusammenfassen. Für dieses Problem gibt es eine Lösung mit einer einzigen Formel.
Ron fragt:
Wie können Sie alle VLOOKUPs summieren, ohne jede einzelne Suche durchzuführen?
Viele Menschen kennen:
=VLOOKUP(B4,Table,2,True)
Wenn Sie die ungefähre Übereinstimmungsversion von VLOOKUP ausführen (wobei Sie True als viertes Argument angeben), können Sie auch LOOKUP ausführen.
Die Suche ist seltsam, da sie die letzte Spalte in der Tabelle zurückgibt. Sie geben keine Spaltennummer an. Wenn Ihre Tabelle von E4 bis J8 ausgeführt wird und Sie das Ergebnis aus Spalte G erhalten möchten, geben Sie E4: G4 als Nachschlagetabelle an.
Ein weiterer Unterschied: Sie geben True / False nicht als viertes Argument an, wie Sie es in VLOOKUP tun würden: Die LOOKUP-Funktion führt immer die Approximate Match-Version von VLOOKUP aus.
Warum sich mit dieser alten Funktion beschäftigen? Weil Lookup einen besonderen Trick hat: Sie können alle Werte gleichzeitig suchen und es werden sie summiert. Anstatt einen einzelnen Wert wie B4 als erstes Argument zu übergeben, können Sie alle Ihre Werte angeben =LOOKUP(B4:B17,E4:F8). Da dies 14 verschiedene Werte zurückgeben würde, müssen Sie die Funktion in eine Wrapper-Funktion wie =SUM( LOOKUP(B4:B17,E4:F8))oder =COUNT(LOOKUP(B4:B17,E4:F8))oder einschließen =AVERAGE( LOOKUP(B4:B17,E4:F8)). Denken Sie daran, dass Sie eine Wrapper-Funktion benötigen, jedoch keine Rapper-Funktion. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))wird nicht funktionieren.
Es gibt einen wirklich häufigen Fehler, den Sie vermeiden möchten. Drücken Sie nach dem Eingeben oder Bearbeiten der Formel nicht die Eingabetaste! Führen Sie stattdessen diesen Drei-Finger-Tastaturtrick aus. Halten Sie Strg und Umschalt gedrückt. Halten Sie die Strg- und Umschalttaste gedrückt und drücken Sie die Eingabetaste. Sie können jetzt Strg und Umschalt loslassen. Wir nennen dies Strg + Umschalt + Eingabetaste, aber es muss wirklich richtig eingestellt sein: Halten Sie Strg + Umschalt gedrückt, drücken Sie die Eingabetaste, lassen Sie Strg + Umschalt los. Wenn Sie es richtig gemacht haben, wird die Formel in der Formelleiste angezeigt, die von geschweiften Klammern umgeben ist:(=SUM(LOOKUP(B4:B17,E4:F8)))
Randnotiz
LOOKUP kann auch das Äquivalent von HLOOKUP ausführen. Wenn Ihre Nachschlagetabelle breiter als hoch ist, wechselt LOOKUP zu HLOOKUP. Im Falle eines Unentschieden… bei einem Tisch mit einer Größe von 8 x 8 oder 10 x 10 behandelt LOOKUP den Tisch als vertikal.
Sehen Sie sich das Video unten an oder studieren Sie diesen Screenshot.
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2184: Summe aller Suchvorgänge.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage von Ron zur Verwendung des alten, alten LOOKUP-Programms. Und das ist aus meinem Buch Excel 2016 In Depth.
Nehmen wir an, wir hatten hier eine Reihe von Produkten und mussten eine Nachschlagetabelle verwenden. Und für jedes Produkt mussten wir den Wert aus der Nachschlagetabelle abrufen und summieren. Der typische Weg ist, dass wir für dieses Produkt in dieser Tabelle ein VLOOKUP-- = VLOOKUP verwenden. Ich drücke F4, sperre das ab und mit dem zweiten Wert. In diesem speziellen Fall ist es sicher, TRUE zu verwenden, da jeder einzelne Wert, den wir suchen, in der Tabelle enthalten ist und die Tabelle sortiert ist. Normalerweise würde ich niemals TRUE verwenden, aber in dieser Episode werden wir TRUE verwenden. Also bekomme ich all diese Werte und dann hier unten, Alt + =, bekommen wir insgesamt diese, oder? Aber was ist, wenn unser gesamtes Ziel nur darin besteht, den 1130 zu bekommen? Wir brauchen nicht alle diese Werte. Wir brauchen nur dieses Ergebnis.
Nun, okay, jetzt gibt es eine alte, alte Funktion, die es seit den Tagen von Visical gibt, LOOKUP. Nicht VLOOKUP. Nicht HLOOKUP, sondern LOOKUP. Zunächst scheint es ähnlich zu sein wie bei VLOOKUP - Sie geben an, welchen Wert Sie suchen, und die Nachschlagetabelle drücken F4, aber dann sind wir fertig. Wir müssen nicht angeben, welche Spalte verwendet werden soll, da LOOKUP nur zur letzten Spalte in der Tabelle wechselt. Wenn Sie eine Tabelle mit sieben Spalten hätten und den vierten Wert nachschlagen möchten, geben Sie einfach die Spalten eins bis vier an. In Ordung? Und was auch immer die letzte Spalte ist, das wird es nachschlagen. Und wir müssen nicht FALSE oder TRUE angeben, da immer TRUE verwendet wird. Es gibt keine FALSE-Version. In Ordung?
Sie müssen also verstehen, wenn Sie einen VLOOKUP machen, verwende ich am Ende immer FALSE, aber in diesem Fall ist es eine kurze Liste - wir wissen, dass alles in der Liste in der Tabelle enthalten ist. Es fehlt nichts und die Tabelle ist sortiert. In Ordung? Auf diese Weise erhalten wir genau das gleiche Ergebnis wie beim VLOOKUP.
Genial, ich möchte das hier runter kopieren: Alt + =. In Ordung. Das kauft uns aber nichts, denn wir müssen noch alle Formeln und dann die SUMME-Funktion eingeben. Das Schöne ist, dass LOOKUP einen Trick machen kann, den VLOOKUP nicht kann, okay? Und das heißt, alle Suchvorgänge auf einmal durchzuführen. Wohin sende ich dies an die SUMME-Funktion, wenn ich LOOKUP sage? Was ist das Suchelement? Wir wollen all diese Dinge nachschlagen, Komma, und dann ist hier die Tabelle - und wir müssen nicht F4 drücken, weil wir dies nirgendwo kopieren werden, es gibt nur eine Formel - schließen Sie den LOOKUP, schließen Sie die Summe.
Okay, jetzt ist hier der Ort, an dem die Dinge durcheinander geraten können: Wenn Sie hier einfach die Eingabetaste drücken, werden Sie 60, okay? Weil es nur den ersten machen wird. Was Sie tun müssen, ist, die magischen drei Tastenanschläge gedrückt zu halten, und dies ist Strg + Umschalt - Ich halte Strg + Umschalt mit meiner linken Hand gedrückt, ich halte diese gedrückt und drücke ENTER mit meiner rechten Hand und es wird die ganze Mathematik von VLOOKUP machen. Ist das nicht großartig? Beachten Sie, dass in der Formelleiste hier oben oder im Formeltext geschweifte Klammern darum stehen. Sie geben diese geschweiften Klammern nicht ein, Excel fügt diese geschweiften Klammern ein und sagt: "Hey, Sie haben dafür Strg + Umschalt + Eingabetaste gedrückt."
Nun, hey, dieses Thema und viele andere Themen sind in diesem Buch enthalten: Power Excel mit der Ausgabe 2017. Klicken Sie dort oben rechts auf das "Ich", um mehr über das Buch zu erfahren.
Heute Frage von Ron: Wie können Sie alle VLOOKUPs zusammenfassen? Jetzt kennen die meisten Leute das VLOOKUP, in dem Sie den Suchwert, die Tabelle, die Spalte und dann TRUE oder FALSE angeben. Und wenn Sie - wenn Sie sich qualifizieren - die WAHRE Version von VLOOKUP machen. dann kannst du auch diesen alten LOOKUP machen. Es ist seltsam, weil es die letzte Spalte der Tabelle zurückgibt, Sie keine Spaltennummer angeben und nicht TRUE oder FALSE sagen. Es ist immer WAHR. Warum sollten wir das benutzen? Weil es einen besonderen Trick hat: Sie können alle Suchwerte auf einmal ausführen und es werden sie summiert. Sie müssen Strg + Umschalt + Eingabetaste drücken, nachdem Sie diese Formel eingegeben haben, sonst funktioniert es nicht. Und dann zeige ich Ihnen im Outtake einen weiteren Trick für LOOKUP.
Nun, hey, ich möchte Ron dafür danken, dass er diese Frage eingeschickt hat, 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.
Okay, während wir hier über LOOKUP sprechen, kann LOOKUP auch Folgendes tun: Wissen Sie, wir haben VLOOKUP für einen vertikalen Tisch wie diesen oder HLOOKUP für einen horizontalen Tisch wie diesen. LOOKUP kann in beide Richtungen gehen. Wir können also sagen, hey wir wollen = LOOKUP diesen Wert D in dieser Tabelle, und da die Tabelle breiter als hoch ist, wechselt LOOKUP automatisch zur HLOOKUP-Version, oder? In diesem Fall wird HLOOKUP ausgeführt, da wir 3 Zeilen mal 5 Spalten angeben. Und weil die letzte Zeile hier die Zahlen sind, bringt es uns diese Zahl. Also haben wir D, Date, holt uns 60. Okay. Wenn ich eine Tabelle angeben würde, die nur in Zeile 12 angezeigt wird, erhalte ich stattdessen den Namen des Produkts. In Ordung? Es ist also eine interessante kleine Funktion. Ich glaube, die Excel-Hilfe sagte früher: "Hey, benutze diese Funktion nicht", aber da 'Zu bestimmten Zeiten können Sie diese Funktion verwenden.
Titelfoto: grandcanyonstate / pixabay
