Ron vil lave en masse VLOOKUP'er og opsummere resultaterne. Der er en løsning med en enkelt formel til dette problem.
Ron spørger:
Hvordan kan du sammenfatte alle VLOOKUP'er uden at foretage hver enkelt opslag?
Mange mennesker er fortrolige med:
=VLOOKUP(B4,Table,2,True)
Hvis du laver den omtrentlige matchversion af VLOOKUP (hvor du angiver True som det fjerde argument), kan du også lave LOOKUP.
Opslag er ulige, fordi det returnerer den sidste kolonne i tabellen. Du angiver ikke et kolonnenummer. Hvis din tabel kører fra E4 til J8, og du vil have resultatet fra kolonne G, angiver du E4: G4 som opslagstabel.
En anden forskel: du angiver ikke sand / falsk som det fjerde argument, som du ville gøre i VLOOKUP: LOOKUP-funktionen gør altid den omtrentlige matchversion af VLOOKUP.
Hvorfor gider med denne gamle funktion? Fordi Lookup har et specielt trick: Du kan slå alle værdierne på én gang, og det summerer dem. I stedet for at videregive en enkelt værdi som B4 som det første argument, kan du angive alle dine værdier =LOOKUP(B4:B17,E4:F8). Da dette ville returnere 14 forskellige værdier, skal du indpakke funktionen i en indpakningsfunktion som =SUM( LOOKUP(B4:B17,E4:F8))eller =COUNT(LOOKUP(B4:B17,E4:F8))eller =AVERAGE( LOOKUP(B4:B17,E4:F8)). Husk, du har brug for en Wrapper-funktion, men ikke en rapper-funktion. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))fungerer ikke.
Der er en rigtig almindelig fejl, som du vil undgå. Efter at have skrevet eller redigeret formlen, skal du ikke trykke på Enter! I stedet gør dette tre-finger tastatur trick. Hold Ctrl og Shift nede. Mens du holder Ctrl og Shift nede, skal du trykke på Enter. Du kan nu frigive Ctrl og Shift. Vi kalder dette Ctrl + Shift + Enter, men det skal virkelig tages korrekt: Tryk og hold Ctrl + Shift, tryk Enter, slip Ctrl + Shift. Hvis du gjorde det korrekt, vises formlen i formellinjen omgivet af krøllede seler:(=SUM(LOOKUP(B4:B17,E4:F8)))
Side note
LOOKUP kan også svare til HLOOKUP. Hvis din opslagstabel er bredere, end den er høj, skifter LOOKUP til HLOOKUP. I tilfælde af uafgjort … et bord, der er 8x8 eller 10x10, behandler LOOKUP bordet som lodret.
Se videoen nedenfor, eller studer dette skærmbillede.
Se video
Videoudskrift
Lær Excel fra Podcast, Episode 2184: Sum All Lookups.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål fra Ron om brugen af det gamle, gamle LOOKUP-program. Og dette er fra min bog, Excel 2016 In Depth.
Lad os sige, at vi havde en masse produkter her, og at vi var nødt til at bruge en opslagstabel. Og for hvert produkt skulle vi, ved du, hente værdien fra opslagstabellen og summe den. Den typiske måde er, vi bruger en VLOOKUP-- = VLOOKUP til dette produkt i denne tabel. Jeg trykker på F4, låser det ned og med den anden værdi. Og i dette særlige tilfælde er det sikkert at bruge SAND, fordi hver værdi, vi ser op, er i tabellen, og tabellen er sorteret. Normalt ville jeg aldrig bruge SAND, men i denne episode skal vi bruge SAND. Så jeg får alle disse værdier og derefter hernede, Alt + =, vi får i alt disse, ikke? Men hvad hvis hele vores mål er bare at få 1130? Vi har ikke brug for alle disse værdier. Vi har bare brug for dette resultat.
Nå, okay, nu er der en gammel, gammel funktion, der har eksisteret siden Visicals dage, kaldet LOOKUP. Ikke VLOOKUP. Ikke HLOOKUP, bare LOOKUP. Og det ser ud til, at det i første omgang svarer til at være VLOOKUP - du angiver, hvilken værdi du ser op og opslagstabellen, tryk på F4, men så er vi færdige. Vi behøver ikke at specificere hvilken kolonne, fordi LOOKUP bare går til den sidste kolonne i tabellen. Hvis du havde en syv kolonnetabel, og du vil slå den fjerde værdi op, ville du bare angive kolonner en til fire. I orden? Og så uanset hvad den sidste kolonne er, det er det, den vil se op. Og vi behøver ikke at specificere, FALSE eller, TRUE, fordi det altid bruger, TRUE; der er ingen, FALSK version. I orden?
Så du er nødt til at forstå, hvis du laver en VLOOKUP, bruger jeg altid FALSE i slutningen, men i dette tilfælde er det en kort liste - vi ved, at alt på listen er i tabellen. Der mangler intet, og bordet er sorteret. I orden? Så dette får os nøjagtigt det samme resultat, som vi har for VLOOKUP.
Awesome, jeg vil kopiere dette ned: Alt + =. I orden. Men det køber os ikke noget, fordi vi stadig skal sætte alle formlerne i og derefter SUM-funktionen. Det smukke er, at LOOKUP kan gøre et trick, som VLOOKUP ikke kan gøre, okay? Og det er at gøre alle opslag på én gang. Så hvor jeg sender dette til SUM-funktionen, når jeg siger LOOKUP, Hvad er opslagsemnet? Vi vil slå alle disse ting op, komma, og så er her bordet - og vi behøver ikke at trykke på F4, for vi vil ikke kopiere dette overalt, der er kun en formel - luk OPSLAG, luk summen.
Okay, her er det sted, hvor tingene kan blive skruet op: Hvis du bare trykker på Enter her, får du 60, okay? Fordi det bare kommer til at gøre det første. Hvad du skal gøre er at holde de magiske tre tastetryk nede, og dette er Ctrl + Shift - Jeg holder Ctrl + shift nede med min venstre hånd, jeg holder dem nede og jeg trykker på ENTER med min højre hånd og det vil gøre al matematik i VLOOKUP. Er det ikke fantastisk? Bemærk i formellinjen heroppe eller i formelteksten, det lægger krøllede seler omkring den. Du skriver ikke disse krøllede seler, Excel placerer disse krøllede seler i for at sige "Hej, du trykkede på Ctrl + Skift + Enter for dette."
Nu hej, dette emne og mange andre emner findes i denne bog: Power Excel med, 2017-udgaven. Klik på det "jeg" deroppe i øverste højre hjørne for at læse mere om bogen.
I dag spørgsmål fra Ron: Hvordan kan du sammenfatte alle VLOOKUP'erne? Nu kender de fleste mennesker VLOOKUP, hvor du angiver opslagsværdien, tabellen, hvilken kolonne og derefter SAND eller FALSK. Og hvis du gør - hvis du kvalificerer dig til - den SANDE version af VLOOKUP. så kan du også gøre dette gamle LOOKUP. Det er underligt, fordi det returnerer den sidste kolonne i tabellen, du angiver ikke kolonnenummer, og du siger ikke SAND eller FALSK. Det er altid SAND. Hvorfor bruger vi dette? Fordi det har et specielt trick: Du kan udføre alle opslagsværdierne på én gang, og det summerer dem. Du skal trykke på Ctrl + Shift + Enter efter at have skrevet den formel, ellers fungerer den ikke. Og så viser jeg dig endnu et trick til LOOKUP i udtagningen.
Nå, hej, jeg vil gerne takke Ron for at sende det spørgsmål, og jeg vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Okay, mens vi her taler om LOOKUP, er den anden ting, som LOOKUP kan gøre: Du ved, vi har VLOOKUP for et lodret bord som dette eller HLOOKUP for et vandret bord som dette; OPSLAG kan gå begge veje. så vi kan sige hej, vi vil = LOOKUP denne værdi, D, i denne tabel, og fordi tabellen er bredere end den er høj, skifter LOOKUP automatisk til HLOOKUP-versionen, ikke? Så i dette tilfælde, fordi vi specificerer 3 rækker med 5 kolonner, vil det gøre HLOOKUP. Og fordi den sidste række her er numrene, vil det bringe os dette nummer. Så vi har D, Date, får os 60. Okay. Hvis jeg ville specificere en tabel, der kun gik til række 12, får jeg i stedet navnet på produktet. I orden? Så det er lidt af en interessant lille funktion. Jeg tror, Excel Hjælp plejede at sige "Hej, brug ikke denne funktion," men der 's bestemte tidspunkter, hvor du kan bruge denne funktion.
Titelfoto: grandcanyonstate / Pixabay
