Ron chce udělat spoustu VLOOKUPů a sečíst výsledky. Tento problém má řešení s jedinou formulí.
Ron se ptá:
Jak můžete sečíst všechny SVYHLEDÁNÍ bez provedení každého jednotlivého vyhledávání?
Mnoho lidí zná:
=VLOOKUP(B4,Table,2,True)
Pokud děláte verzi VLOOKUP s přibližnou shodou (kde jako čtvrtý argument zadáte True), můžete také provést LOOKUP.
Vyhledávání je zvláštní, protože vrací poslední sloupec v tabulce. Nezadáte číslo sloupce. Pokud vaše tabulka běží od E4 do J8 a chcete výsledek ze sloupce G, zadejte jako vyhledávací tabulku E4: G4.
Další rozdíl: nezadáte True / False jako čtvrtý argument, jako byste to udělali ve VLOOKUP: funkce LOOKUP vždy provede verzi VLOOKUP s přibližnou shodou.
Proč se obtěžovat s touto starodávnou funkcí? Protože Lookup má speciální trik: Můžete vyhledat všechny hodnoty najednou a sečte je. Místo toho, abyste jako první argument předali jedinou hodnotu, například B4, můžete zadat všechny své hodnoty =LOOKUP(B4:B17,E4:F8). Vzhledem k tomu, že by se vrátilo 14 různých hodnot, musíte funkci zabalit do funkce wrapper, jako je =SUM( LOOKUP(B4:B17,E4:F8))nebo =COUNT(LOOKUP(B4:B17,E4:F8))nebo =AVERAGE( LOOKUP(B4:B17,E4:F8)). Nezapomeňte, že potřebujete funkci Wrapper, ale ne funkci rapper. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))nebude fungovat.
Existuje opravdu častá chyba, které se budete chtít vyhnout. Po napsání nebo úpravě vzorce nestiskněte Enter! Místo toho proveďte tento trik s klávesnicí se třemi prsty. Podržte Ctrl a Shift. Podržte klávesy Ctrl a Shift a stiskněte klávesu Enter. Nyní můžete uvolnit Ctrl a Shift. Říkáme tomu Ctrl + Shift + Enter, ale musí to být načasováno správně: Stiskněte a podržte Ctrl + Shift, Stiskněte Enter, Uvolněte Ctrl + Shift. Pokud jste to udělali správně, vzorec se zobrazí na řádku vzorců obklopen složenými závorkami:(=SUM(LOOKUP(B4:B17,E4:F8)))
Boční poznámka
LOOKUP může také dělat ekvivalent HLOOKUP. Pokud je vaše vyhledávací tabulka širší než vysoká, LOOKUP se přepne na HLOOKUP. V případě nerozhodného výsledku … stolu o rozměrech 8x8 nebo 10x10 bude LOOKUP považovat stůl za svislý.
Podívejte se na video níže nebo si prostudujte tento snímek obrazovky.
Sledovat video
Přepis videa
Naučte se Excel z Podcastu, epizoda 2184: Sum All Lookups.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka od Rona o používání starého, starého programu LOOKUP. A toto je z mé knihy Excel 2016 In Depth.
Řekněme, že jsme tu měli spoustu produktů a museli jsme použít vyhledávací tabulku. A pro každý produkt jsme museli, víte, získat hodnotu z vyhledávací tabulky a sečíst to. Typickým způsobem je, že pro tento produkt v této tabulce používáme VLOOKUP-- = VLOOKUP. Stisknu F4, uzamknu to a druhou hodnotou. A v tomto konkrétním případě, protože každá jednotlivá hodnota, kterou vyhledáváme, je v tabulce a tabulka je tříděna, je bezpečné použít TRUE. Normálně bych nikdy nepoužil TRUE, ale v této epizodě použijeme TRUE. Takže mám všechny tyto hodnoty a pak tady dole, Alt + =, dostaneme celkem ty, že? Ale co když celý náš cíl je jen dostat 1130? Všechny tyto hodnoty nepotřebujeme. Tento výsledek prostě potřebujeme.
Dobře, dobře, nyní existuje stará stará funkce, která existuje už od dob Visical, zvaná LOOKUP. Není VLOOKUP. Ne HLOOKUP, jen HLEDAT. A zdá se, zpočátku, podobný VLOOKUP - určíte, jakou hodnotu hledáte, a vyhledávací tabulku, stiskněte klávesu F4, ale pak jsme hotovi. Nemusíme určovat, který sloupec, protože LOOKUP právě přejde na poslední sloupec v tabulce. Pokud jste měli tabulku se sedmi sloupci a chcete vyhledat čtvrtou hodnotu, zadali byste pouze sloupce jedna až čtyři. V pořádku? Ať už je tedy poslední sloupec jakýkoli, bude vyhledávat. A nemusíme specifikovat FALSE nebo TRUE, protože vždy používá TRUE; neexistuje žádná FALSE verze. V pořádku?
Takže musíte pochopit, že pokud děláte VLOOKUP, vždy používám na konci FALSE, ale v tomto případě je to krátký seznam - víme, že vše v seznamu je v tabulce. Nic vám nechybí a tabulka je seřazená. V pořádku? Získáme tedy přesně stejný výsledek, jaký máme pro SVYHLEDÁNÍ.
Úžasné, chci to zkopírovat dolů: Alt + =. V pořádku. Ale to nám nic nekoupí, protože stále musíme vkládat všechny vzorce a poté funkci SUMA. Krásná věc je, že LOOKUP dokáže trik, který VLOOKUP neumí, dobře? A to je provést všechna vyhledávání najednou. Takže, kam to pošlu funkci SUMA, když řeknu VYHLEDAT, Co je to vyhledávací položka? Chceme vyhledat všechny tyto věci, čárku, a pak je tady tabulka - a nemusíme stisknout klávesu F4, protože to nikde nebudeme kopírovat, existuje pouze jeden vzorec - zavřít VYHLEDAT, zavřít součet.
Dobře, tady je místo, kde se mohou věci pokazit: Pokud zde jednoduše stisknete Enter, dostanete 60, dobře? Protože to prostě půjde udělat první. Musíte jen držet tři magické stisknutí kláves, a to je Ctrl + Shift-- Držím Ctrl + shift levou rukou, držím je dole a stisknu ENTER pravou rukou a udělá to všechno z matematiky VLOOKUP. Není to úžasné? Všimněte si, že v řádku vzorců nahoře nebo v textu vzorce kolem něj jsou složené závorky. Tyto složené závorky nezadáváte, Excel tyto složené závorky vloží a řekne: „Hele, stiskli jste pro to Ctrl + Shift + Enter.“
Ahoj, toto téma a mnoho dalších témat obsahuje tato kniha: Power Excel with, vydání z roku 2017. Kliknutím na toto „já“ nahoře v pravém horním rohu zobrazíte další informace o knize.
Dnes otázka od Rona: Jak můžete shrnout všechny SVYHLEDÁNÍ? Nyní většina lidí zná VLOOKUP, kde zadáte vyhledávací hodnotu, tabulku, který sloupec a poté TRUE nebo FALSE. A pokud děláte - pokud máte nárok na - PRAVOU verzi VLOOKUP. pak můžete také udělat tento starý VZHLED. Je to zvláštní, protože vrací poslední sloupec tabulku, neurčíte číslo sloupce a neřeknete PRAVDA nebo NEPRAVDA. Vždy je to PRAVDA. Proč bychom to používali? Protože má speciální trik: Můžete provést všechny vyhledávací hodnoty najednou a sečte je. Po zadání tohoto vzorce musíte stisknout Ctrl + Shift + Enter, jinak to nebude fungovat. A pak vám v outtake ukážu další trik pro VYHLEDÁVÁNÍ.
No, hej, chci poděkovat Ronovi za zaslání té otázky a chci ti poděkovat, že jsi se zastavil. Uvidíme se příště na dalším netcastu z.
Dobře, když už mluvíme o LOOKUP, další věc, kterou LOOKUP může udělat: Víte, máme VLOOKUP pro takový vertikální stůl nebo HLOOKUP pro takový horizontální stůl; LOOKUP může jít oběma směry. takže můžeme říci, že chceme = LOOKUP tuto hodnotu, D, v této tabulce, a protože tabulka je širší než vysoká, LOOKUP se automaticky přepne na verzi HLOOKUP, že? V tomto případě, protože zadáváme 3 řádky o 5 sloupcích, provede HLOOKUP. A protože poslední řádek zde jsou čísla, toto číslo nám přinese. Takže máme D, Date, dostane nás 60. Dobře. Pokud bych určil tabulku, která šla pouze na řádek 12, pak místo toho dostanu název produktu. V pořádku? Je to tedy trochu zajímavá malá funkce. Myslím, že Excel Help říkal: „Hej, nepoužívej tuto funkci,“ ale tam 'v určitých obdobích, kdy můžete tuto funkci použít.
Název fotografie: grandcanyonstate / Dreamstime
