Ron chce urobiť veľa VLOOKUPOV a zhrnúť výsledky. Tento problém má jednoformulárne riešenie.
Ron sa pýta:
Ako môžete zhrnúť všetky VLOOKUPY bez vykonania každého jednotlivého vyhľadávania?
Mnoho ľudí pozná:
=VLOOKUP(B4,Table,2,True)
Ak robíte verziu VLOOKUP s približnou zhodou (kde ako štvrtý argument zadáte True), môžete tiež urobiť LOOKUP.
Vyhľadávanie je nepárne, pretože vracia posledný stĺpec v tabuľke. Nezadáte číslo stĺpca. Ak vaša tabuľka beží od E4 do J8 a chcete výsledok zo stĺpca G, ako vyhľadávaciu tabuľku by ste zadali E4: G4.
Ďalší rozdiel: Nezadáte True / False ako štvrtý argument, ako by ste to urobili vo VLOOKUP: funkcia LOOKUP vždy urobí verziu VLOOKUP s približnou zhodou.
Prečo sa trápiť s touto starodávnou funkciou? Pretože Lookup má špeciálny trik: Môžete vyhľadať všetky hodnoty naraz a zhrnie ich. Namiesto zadania jednej hodnoty, napríklad B4, ako prvého argumentu môžete určiť všetky svoje hodnoty =LOOKUP(B4:B17,E4:F8)
. Pretože by sa tým vrátilo 14 rôznych hodnôt, musíte funkciu zabaliť do funkcie wrapper, ako napríklad =SUM( LOOKUP(B4:B17,E4:F8))
alebo =COUNT(LOOKUP(B4:B17,E4:F8))
alebo =AVERAGE( LOOKUP(B4:B17,E4:F8))
. Pamätajte, že potrebujete funkciu obaľovača, ale nie funkciu rappera. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))
nebude fungovať.
Existuje naozaj častá chyba, ktorej sa budete chcieť vyhnúť. Po napísaní alebo úprave vzorca nestláčajte kláves Enter! Namiesto toho urobte tento trik s klávesnicou s tromi prstami. Podržte Ctrl a Shift. Podržte stlačený kláves Ctrl a Shift a stlačte kláves Enter. Teraz môžete uvoľniť klávesy Ctrl a Shift. Hovoríme tomu Ctrl + Shift + Enter, ale musí to byť naozaj správne načasované: Stlačte a podržte Ctrl + Shift, Stlačte Enter, Uvoľnite Ctrl + Shift. Ak ste to urobili správne, vzorec sa zobrazí na paneli vzorcov obklopený zloženými zátvorkami:(=SUM(LOOKUP(B4:B17,E4:F8)))
Poznámka
LOOKUP môže robiť aj ekvivalent HLOOKUPU. Ak je vaša vyhľadávacia tabuľka širšia ako vysoká, VYHĽADÁVANIE sa prepne na HLOOKUP. V prípade kravaty… stôl, ktorý má veľkosť 8x8 alebo 10x10, bude LOOKUP považovať stôl za zvislý.
Sledujte video nižšie alebo si preštudujte túto snímku obrazovky.
![](https://cdn.wiki-base.com/3457144/do_all_lookups_and_sum_the_results_-_excel_tips_2.jpg.webp)
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2184: Sčítanie všetkých vyhľadávaní.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešná otázka od Rona o používaní starého, starého programu LOOKUP. A toto je z mojej knihy Excel 2016 In Depth.
Povedzme, že sme tu mali kopu produktov a museli sme použiť vyhľadávaciu tabuľku. A pre každý produkt sme museli, viete, získať hodnotu z vyhľadávacej tabuľky a sčítať ju. Typickým spôsobom je, že v tejto tabuľke používame VLOOKUP-- = VLOOKUP pre tento produkt. Stlačím F4, uzamknem to a s druhou hodnotou. A v tomto konkrétnom prípade, pretože každá jednotlivá hodnota, ktorú hľadáme, je v tabuľke a táto tabuľka je zoradená, je bezpečné použiť TRUE. Normálne by som nikdy nepoužíval TRUE, ale v tejto epizóde použijeme TRUE. Takže dostanem všetky tieto hodnoty a potom tu dole, Alt + =, dostaneme ich celkom, však? Čo však v prípade, že celý náš cieľ je iba získať model 1130? Nepotrebujeme všetky tieto hodnoty. Potrebujeme iba tento výsledok.
Dobre, dobre, teraz existuje stará, stará funkcia, ktorá existuje už od čias spoločnosti Visical, a ktorá sa volá LOOKUP. Nie VLOOKUP. Nie HLOOKUP, iba HĽADAŤ. A zdá sa, že spočiatku je to podobné ako VLOOKUP - určíte, akú hodnotu hľadáte, a vyhľadávaciu tabuľku, stlačte kláves F4, ale potom sme hotoví. Nemusíme určovať, ktorý stĺpec, pretože LOOKUP iba prejde do posledného stĺpca v tabuľke. Ak by ste mali tabuľku so siedmimi stĺpcami a chcete vyhľadať štvrtú hodnotu, zadali by ste iba stĺpce jeden až štyri. Dobre? Nech je teda posledný stĺpec akýkoľvek, bude sa vyhľadávať práve tu. A nemusíme špecifikovať FALSE alebo TRUE, pretože vždy používa TRUE; verzia FALSE neexistuje. Dobre?
Takže musíte pochopiť, že ak robíte VLOOKUP, vždy použijem na konci FALSE, ale v tomto prípade je to krátky zoznam - vieme, že všetko v zozname je v tabuľke. Nechýba nič a tabuľka je zoradená. Dobre? Takto sa dostaneme k úplne rovnakému výsledku, aký máme pre VÝHĽAD.
Úžasné, chcem to skopírovať dole: Alt + =. Dobre. Ale to nám nič nekúpi, pretože musíme do toho vložiť všetky vzorce a potom funkciu SUMA. Krásna vec je, že LOOKUP dokáže urobiť trik, ktorý VLOOKUP nedokáže, dobre? A to je vykonať všetky vyhľadávania naraz. Takže, keď to pošlem do funkcie SUMA, keď poviem HĽADAŤ, Čo je položka vyhľadávania? Chceme vyhľadať všetky tieto veci, čiarku, a potom je tu tabuľka - a nemusíme stlačiť kláves F4, pretože to nebudeme nikam kopírovať, existuje iba jeden vzorec - zavrieť VYHĽADAŤ, zavrieť SUMA.
Dobre, tu je miesto, kde sa dá niečo pokaziť: Ak tu jednoducho kliknete na Enter, dostanete 60, dobre? Pretože to pôjde len urobiť prvý. Musíte len podržať tri magické stlačenia klávesov, a to je Ctrl + Shift - Ctrl + Shift držím ľavou rukou, stále ich držím stlačený a pravou rukou stláčam ENTER a bude to robiť všetku matematiku VLOOKUPU. Nie je to úžasné? Všimnite si, že na paneli vzorcov tu alebo v texte vzorca je okolo neho zložená zátvorka. Tieto kučeravé zátvorky nenapíšete, Excel tieto zložené zátvorky vloží a povie: „Hele, stlačili ste na to Ctrl + Shift + Enter.“
Ahoj, táto téma a veľa ďalších tém sa nachádza v tejto knihe: Power Excel with, vydanie z roku 2017. Kliknutím na toto „Ja“ tam hore v pravom hornom rohu si prečítate viac o knihe.
Dnes otázka od Rona: Ako môžete zhrnúť všetky VLOOKUPY? Väčšina ľudí teraz pozná VLOOKUP, v ktorom zadáte vyhľadávaciu hodnotu, tabuľku, ktorý stĺpec a potom hodnotu TRUE alebo FALSE. A ak robíte - ak máte nárok na - PRAVÚ verziu VLOOKUPU. potom môžete urobiť aj tento starý VZHĽAD. Je to zvláštne, pretože vráti posledný stĺpec tabuľky, neurčíte číslo stĺpca a nehovoríte PRAVDA alebo NEPRAVDA. Vždy je to PRAVDA. Prečo by sme to používali? Pretože má špeciálny trik: Môžete vyhľadať všetky vyhľadávané hodnoty naraz a zhrnie ich. Po zadaní tohto vzorca musíte stlačiť Ctrl + Shift + Enter, inak to nebude fungovať. A potom v outtake, ukážem vám ďalší trik pre LOOKUP.
No, hej, chcem sa poďakovať Ronovi za zaslanie tejto otázky a chcem sa ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce na ďalšom netcaste od.
Dobre, keď tu hovoríme o LOOKUPE, ďalšia vec, ktorú LOOKUP môže urobiť: Viete, máme VLOOKUP pre vertikálny stôl, ako je tento, alebo HLOOKUP pre horizontálny stôl, ako je tento; LOOKUP môže ísť ktoroukoľvek cestou. takže môžeme povedať, že hej chceme = VYHĽADAŤ túto hodnotu, D, v tejto tabuľke, a pretože je tabuľka širšia ako vysoká, LOOKUP automaticky prepne na verziu HLOOKUP, nie? Takže v tomto prípade, pretože zadávame 3 riadky po 5 stĺpcoch, urobí to HLOOKUP. A pretože posledný riadok tu sú čísla, toto číslo nám prinesie. Takže máme D, Date, dostane nás 60. V poriadku. Ak by som určil tabuľku, ktorá prešla iba na riadok 12, potom dostanem namiesto toho názov produktu. Dobre? Je to teda trochu zaujímavá malá funkcia. Myslím, že Excel Help hovorieval: „Hej, nepoužívaj túto funkciu,“ ale je tuv určitých obdobiach, kedy môžete túto funkciu použiť.
Názov fotografie: grandcanyonstate / Pixabay