Ron wil een aantal VERT.ZOEKEN doen en de resultaten optellen. Er is een oplossing met één formule voor dit probleem.
Ron vraagt:
Hoe kunt u alle VERT.ZOEKEN bij elkaar optellen zonder elke afzonderlijke zoekopdracht uit te voeren?
Veel mensen zijn bekend met:
=VLOOKUP(B4,Table,2,True)
Als u de versie van VERT.ZOEKEN bij benadering gebruikt (waarbij u True opgeeft als het vierde argument), kunt u ook ZOEKEN uitvoeren.
Opzoeken is vreemd omdat het de laatste kolom in de tabel retourneert. U specificeert geen kolomnummer. Als uw tabel van E4 naar J8 loopt en u het resultaat van kolom G wilt, specificeert u E4: G4 als de opzoektabel.
Nog een verschil: je specificeert niet True / False als het vierde argument zoals je zou doen in VERT.ZOEKEN: de functie ZOEKEN doet altijd de geschatte overeenkomst-versie van VERT.ZOEKEN.
Waarom zou u zich druk maken over deze oude functie? Omdat Lookup een speciale truc heeft: u kunt alle waarden in één keer opzoeken en het zal ze optellen. In plaats van een enkele waarde, zoals B4, als eerste argument door te geven, kunt u al uw waarden specificeren =LOOKUP(B4:B17,E4:F8). Omdat dit 14 verschillende waarden zou retourneren, moet u de functie in een wrapper-functie zoals =SUM( LOOKUP(B4:B17,E4:F8))of =COUNT(LOOKUP(B4:B17,E4:F8))of verpakken =AVERAGE( LOOKUP(B4:B17,E4:F8)). Onthoud dat je een wrapper-functie nodig hebt, maar geen rapper-functie. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))zal niet werken.
Er is een veel voorkomende fout die u wilt vermijden. Druk niet op Enter na het typen of bewerken van de formule! Voer in plaats daarvan deze toetsenbordtruc met drie vingers uit. Houd Ctrl en Shift ingedrukt. Houd Ctrl en Shift ingedrukt en druk op Enter. U kunt nu Ctrl en Shift loslaten. We noemen dit Ctrl + Shift + Enter, maar het moet echt correct getimed zijn: houd Ctrl + Shift ingedrukt, druk op Enter, laat Ctrl + Shift los. Als je het goed hebt gedaan, verschijnt de formule in de formulebalk, omringd door accolades:(=SUM(LOOKUP(B4:B17,E4:F8)))
Kanttekening
LOOKUP kan ook het equivalent van HLOOKUP doen. Als uw opzoektabel breder is dan hoog, schakelt LOOKUP over naar HLOOKUP. In het geval van een gelijkspel… een tafel die 8x8 of 10x10 is, behandelt LOOKUP de tafel als verticaal.
Bekijk de onderstaande video of bekijk deze screenshot.
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2184: Som alle zoekopdrachten op.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag, van Ron, over het gebruik van het oude, oude LOOKUP-programma. En dit komt uit mijn boek, Excel 2016 In Depth.
Laten we zeggen dat we hier een heleboel producten hadden en dat we een opzoektabel moesten gebruiken. En voor elk product moesten we, weet je, de waarde uit de opzoektabel halen en die optellen. De typische manier is dat we een VERT.ZOEKEN-- = VERT.ZOEKEN gebruiken voor dit product in deze tabel. Ik druk op F4, vergrendel dat en met de tweede waarde. En in dit specifieke geval, omdat elke waarde die we opzoeken in de tabel staat en de tabel is gesorteerd, is het veilig om TRUE te gebruiken. Normaal gesproken zou ik nooit TRUE gebruiken, maar in deze aflevering gaan we TRUE gebruiken. Dus ik krijg al die waarden en dan hier, Alt + =, we krijgen een totaal van die, toch? Maar wat als ons hele doel is om de 1130 te krijgen? We hebben al deze waarden niet nodig. We hebben alleen dit resultaat nodig.
Nou, oké, nu is er een oude, oude functie die bestaat sinds de dagen van Visical, genaamd LOOKUP. Niet VERT.ZOEKEN. Geen HORIZ.ZOEKEN, alleen LOOKUP. En het lijkt in eerste instantie op VERT.ZOEKEN - u specificeert welke waarde u opzoekt en de opzoektabel, drukt op F4, maar dan zijn we klaar. We hoeven niet te specificeren welke kolom omdat LOOKUP gewoon naar de laatste kolom in de tabel gaat. Als u een tabel met zeven kolommen had en u de vierde waarde wilt opzoeken, geeft u de kolommen één tot en met vier op. Oké? En wat de laatste kolom ook is, dat is wat hij zal opzoeken. En we hoeven niet FALSE of TRUE te specificeren omdat het altijd TRUE gebruikt; er is geen, FALSE versie. Oké?
Dus je moet begrijpen dat als je een VERT.ZOEKEN doet, ik altijd FALSE aan het einde gebruik, maar in dit geval is het een korte lijst - we weten dat alles in de lijst in de tabel staat. Er ontbreekt niets en de tafel is gesorteerd. Oké? Dit geeft ons dus exact hetzelfde resultaat dat we hebben voor VERT.ZOEKEN.
Geweldig, ik wil dit naar beneden kopiëren: Alt + =. Oké. Maar dat koopt ons niets, want we moeten nog alle formules erin stoppen en dan de SOM-functie. Het mooie is dat LOOKUP een truc kan doen die VERT.ZOEKEN niet kan, oké? En dat is om alle zoekopdrachten tegelijk uit te voeren. Dus, waar ik dit naar de SOM-functie stuur, wanneer ik ZOEKEN zeg, wat is het opzoekitem? We willen al deze dingen opzoeken, komma, en dan is hier de tabel - en we hoeven niet op F4 te drukken, want we gaan dit nergens kopiëren, er is maar één formule - sluit de ZOEKEN, sluit de SUM.
Oké, nu, hier is de plek waar dingen kunnen worden verpest: als je gewoon hier op Enter drukt, krijg je 60, oké? Omdat het gewoon de eerste gaat doen. Wat je moet doen is de magische drie toetsaanslagen ingedrukt houden, en dit is Ctrl + Shift-- ik houd Ctrl + shift ingedrukt met mijn linkerhand, ik blijf die ingedrukt houden en ik druk op ENTER met mijn rechterhand, en het zal alle wiskunde van VERT.ZOEKEN doen. Is dat niet geweldig? Merk op dat in de formulebalk hierboven, of in de formuletekst, accolades eromheen worden geplaatst. Je typt die accolades niet, Excel zet die accolades erin om te zeggen: "Hé, je hebt hiervoor Ctrl + Shift + Enter ingedrukt."
Nu, hey, dit onderwerp en veel andere onderwerpen staan in dit boek: Power Excel met, de editie van 2017. Klik op die "ik" daar in de rechterbovenhoek om meer over het boek te lezen.
Vandaag de vraag van Ron: hoe kun je alle VERT.ZOEKEN bij elkaar optellen? Nu kennen de meeste mensen VERT.ZOEKEN waar u de opzoekwaarde specificeert, de tabel, welke kolom, en vervolgens TRUE of FALSE. En als je doet - als je in aanmerking komt voor - de TRUE-versie van VERT.ZOEKEN. dan kun je ook deze oude LOOKUP doen. Het is vreemd, omdat het de laatste kolom van de tabel retourneert, u geen kolomnummer opgeeft en u zegt niet WAAR of ONWAAR. Het is altijd WAAR. Waarom zouden we dit gebruiken? Omdat het een speciale truc heeft: je kunt alle opzoekwaarden tegelijk uitvoeren en het zal ze optellen. U moet op Ctrl + Shift + Enter drukken nadat u die formule hebt getypt, anders werkt deze niet. En dan zal ik je in de outtake nog een truc laten zien voor ZOEKEN.
Nou, hé, ik wil Ron bedanken voor het insturen van die vraag, en ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Oké, terwijl we het hier hebben over LOOKUP, het andere dat LOOKUP kan doen: Weet je, we hebben VERT.ZOEKEN voor een verticale tabel als deze of HLOOKUP voor een horizontale tabel als deze; LOOKUP kan beide kanten op. dus we kunnen zeggen hey we willen = deze waarde, D, in deze tabel ZOEKEN, en omdat de tafel breder is dan hoog, schakelt LOOKUP automatisch over naar de HLOOKUP-versie, toch? Dus in dit geval, omdat we 3 rijen bij 5 kolommen specificeren, wordt de HORIZ.ZOEKEN uitgevoerd. En omdat de laatste rij hier de cijfers zijn, zal het ons dat aantal geven. Dus we hebben D, Date, krijgt ons 60. Oké. Als ik een tabel zou specificeren die alleen naar rij 12 ging, dan krijg ik in plaats daarvan de naam van het product. Oké? Dus het is een soort interessante kleine functie. Ik denk dat Excel Help altijd zei: 'Hé, gebruik deze functie niet', maar er is 's bepaalde momenten waarop u deze functie kunt gebruiken.
Titelfoto: grandcanyonstate / pixabay
