Ron vil gjøre en haug med VLOOKUPs og oppsummere resultatene. Det er en løsning med én formel på dette problemet.
Ron spør:
Hvordan kan du oppsummere alle VLOOKUP uten å gjøre hvert enkelt oppslag?
Mange er kjent med:
=VLOOKUP(B4,Table,2,True)
Hvis du gjør den omtrentlige kampversjonen av VLOOKUP (der du angir True som det fjerde argumentet), kan du også gjøre LOOKUP.
Oppslag er rart fordi den returnerer den siste kolonnen i tabellen. Du angir ikke et kolonnenummer. Hvis tabellen din går fra E4 til J8 og du vil ha resultatet fra kolonne G, vil du spesifisere E4: G4 som oppslagstabell.
En annen forskjell: du spesifiserer ikke sant / usant som det fjerde argumentet slik du ville gjort i VLOOKUP: LOOKUP-funksjonen gjør alltid den omtrentlige matchversjonen av VLOOKUP.
Hvorfor bry seg med denne eldgamle funksjonen? Fordi Lookup har et spesielt triks: Du kan slå opp alle verdiene samtidig, og det vil oppsummere dem. I stedet for å sende en enkelt verdi som B4 som det første argumentet, kan du spesifisere alle verdiene dine =LOOKUP(B4:B17,E4:F8). Siden dette ville gi 14 forskjellige verdier, må du pakke funksjonen inn i en innpakningsfunksjon som =SUM( LOOKUP(B4:B17,E4:F8))eller =COUNT(LOOKUP(B4:B17,E4:F8))eller =AVERAGE( LOOKUP(B4:B17,E4:F8)). Husk at du trenger en Wrapper-funksjon, men ikke en rapper-funksjon. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))vil ikke fungere.
Det er en veldig vanlig feil som du vil unngå. Etter å ha skrevet eller redigert formelen, ikke trykk Enter! I stedet gjør dette trefingertastaturet. Hold nede Ctrl og Shift. Mens du holder nede Ctrl og Shift, trykker du Enter. Du kan nå slippe Ctrl og Shift. Vi kaller dette Ctrl + Shift + Enter, men det må virkelig tidsbestemmes riktig: Trykk og hold Ctrl + Shift, trykk Enter, slipp Ctrl + Shift. Hvis du gjorde det riktig, vil formelen vises i formellinjen omgitt av krøllete bukseseler:(=SUM(LOOKUP(B4:B17,E4:F8)))
Side Merknad
LOOKUP kan også gjøre det som tilsvarer HLOOKUP. Hvis oppslagstabellen din er bredere enn den er høy, bytter LOOKUP til HLOOKUP. I tilfelle av uavgjort … et bord som er 8x8 eller 10x10, vil LOOKUP behandle bordet som loddrett.
Se videoen nedenfor eller studer dette skjermbildet.
Se på video
Videoutskrift
Lær Excel fra Podcast, Episode 2184: Sum All Lookups.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål fra Ron om bruk av det gamle, gamle LOOKUP-programmet. Og dette er fra boka mi, Excel 2016 In Depth.
La oss si at vi hadde en haug med produkter her, og at vi måtte bruke et Lookup-bord. Og for hvert produkt måtte vi, vet du, hente verdien fra oppslagstabellen og summere den. Vel, den typiske måten er, vi bruker en VLOOKUP-- = VLOOKUP for dette produktet i denne tabellen. Jeg trykker på F4, låser den ned og med den andre verdien. Og i dette spesielle tilfellet, fordi hver enkelt verdi som vi ser opp, er i tabellen, og tabellen er sortert, er det trygt å bruke SANT. Normalt vil jeg aldri bruke SANT, men i denne episoden skal vi bruke SANT. Så jeg får alle disse verdiene, og så her nede, Alt + =, får vi totalt disse, ikke sant? Men hva om hele målet vårt er bare å få 1130? Vi trenger ikke alle disse verdiene. Vi trenger bare dette resultatet.
Vel, ok, det er en gammel, gammel funksjon som har eksistert siden Visicals dager, kalt LOOKUP. Ikke VLOOKUP. Ikke HLOOKUP, bare LOOKUP. Og det virker som om det ligner på VLOOKUP - du spesifiserer hvilken verdi du ser opp og oppslagstabellen, trykk på F4, men så er vi ferdige. Vi trenger ikke å spesifisere hvilken kolonne fordi LOOKUP bare går til den siste kolonnen i tabellen. Hvis du hadde en syv kolonnetabell og du vil slå opp den fjerde verdien, vil du bare spesifisere kolonnene en til fire. Ok? Og, uansett hva den siste kolonnen er, det er det den skal se opp. Og vi trenger ikke å spesifisere, FALSE eller, TRUE fordi den alltid bruker, TRUE; det er ingen, FALSE versjon. Ok?
Så du må forstå at hvis du gjør en VLOOKUP, bruker jeg alltid FALSE på slutten, men i dette tilfellet er det en kort liste - vi vet at alt i listen er i tabellen. Det mangler ingenting, og bordet er sortert. Ok? Så dette vil gi oss nøyaktig det samme resultatet som vi har for VLOOKUP.
Kjempebra, jeg vil kopiere dette ned: Alt + =. Ok. Men det kjøper oss ikke noe fordi vi fortsatt må legge alle formlene inn og deretter SUM-funksjonen. Det vakre er at LOOKUP kan gjøre et triks som VLOOKUP ikke kan gjøre, ok? Og det er å gjøre alle oppslagene samtidig. Så hvor jeg sender dette til SUM-funksjonen, når jeg sier LOOKUP, Hva er oppslagselementet? Vi vil slå opp alle disse tingene, komma, og så er det tabellen - og vi trenger ikke å trykke F4, for vi kommer ikke til å kopiere dette hvor som helst, det er bare en formel - lukk LOOKUP, lukk summen.
Greit, nå, her er stedet der ting kan bli skrudd opp: Hvis du bare trykker Enter her, får du 60, greit? Fordi det bare kommer til å gjøre den første. Det du må gjøre er å holde nede de magiske tre tastetrykkene, og dette er Ctrl + Shift - Jeg holder nede Ctrl + shift med venstre hånd, jeg holder dem nede, og jeg trykker ENTER med høyre hånd, og det vil gjøre all matematikken til VLOOKUP. Er det ikke kjempebra? Legg merke til i formellinjen her oppe, eller i formelteksten, den setter krøllete bukseseler rundt den. Du skriver ikke de krøllete bukkene, Excel setter de krøllete bukkene i for å si "Hei, du trykket på Ctrl + Shift + Enter for dette."
Nå, hei, dette emnet og mange andre emner er i denne boka: Power Excel med, 2017-utgaven. Klikk på "Jeg" der oppe i høyre hjørne for å lese mer om boka.
I dag, spørsmål fra Ron: Hvordan kan du oppsummere alle VLOOKUP-ene? Nå vet de fleste VLOOKUP der du spesifiserer oppslagsverdien, tabellen, hvilken kolonne, og deretter, SANT eller, FALSK. Og hvis du gjør - hvis du kvalifiserer deg for - den SANTE versjonen av VLOOKUP. så kan du også gjøre denne gamle LOOKUP. Det er rart, fordi det returnerer den siste kolonnen i tabellen, du angir ikke kolonnetall, og du sier ikke SANT eller FALSK. Det er alltid sant. Hvorfor skulle vi bruke dette? Fordi det har et spesielt triks: Du kan gjøre alle oppslagsverdiene samtidig, og det vil oppsummere dem. Du må trykke Ctrl + Shift + Enter etter at du har skrevet den formelen, ellers fungerer den ikke. Og så vil jeg vise deg et annet triks for LOOKUP i uttaket.
Vel, hei, jeg vil takke Ron for at du sendte det spørsmålet inn, og jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Greit, mens vi er her og snakker om LOOKUP, er den andre tingen som LOOKUP kan gjøre: Du vet, vi har VLOOKUP for et loddrett bord som dette eller HLOOKUP for et horisontalt bord som dette; LOOKUP kan gå begge veier. så vi kan si hei vi vil = LOOKUP denne verdien, D, i denne tabellen, og fordi bordet er bredere enn det er høyt, bytter LOOKUP automatisk til HLOOKUP-versjonen, ikke sant? Så i dette tilfellet fordi vi spesifiserer 3 rader med 5 kolonner, vil det gjøre HLOOKUP. Og fordi den siste raden her er tallene, vil det gi oss det tallet. Så vi har D, Date, får oss 60. Greit. Hvis jeg ville spesifisere en tabell som bare gikk til rad 12, får jeg navnet på produktet i stedet. Ok? Så det er litt av en interessant liten funksjon. Jeg tror at Excel Help pleide å si "Hei, ikke bruk denne funksjonen," men der 's bestemte tider hvor du kan bruke denne funksjonen.
Tittelfoto: grandcanyonstate / Pixabay
