VLOOKUP je výkonná funkce. Na jednom z mých seminářů Power Excel ale často dostanu otázku od někoho, kdo chce vědět, jestli VLOOKUP dokáže vrátit všechny odpovídající hodnoty. Jak víte, VLOOKUP s Čtvrtým argumentem False vždy vrátí první nalezenou shodu. Na následujícím snímku obrazovky vrátí buňka F2 hodnotu 3623, protože se jedná o první shodu nalezenou pro úlohu J1199.
Otázka tedy, může VLOOKUP vrátit všechny zápasy?
VLOOKUP nebude. Ale jiné funkce mohou.
Pokud chcete sečíst všechny náklady z úlohy J1199, použili byste =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Pokud máte textové hodnoty a chcete spojit všechny výsledky do jedné hodnoty, můžete použít =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Tento vzorec funguje pouze v Office 365 a Excel 2019.
Nebo možná budete muset vrátit všechny výsledky pro jednu úlohu do nové řady listu. =FILTER(B2:C53,A2:A53=K1,"None Found")Problém vyřeší zcela nová funkce, která přichází na Office 365 v roce 2019:
Někdy lidé chtějí provést všechny SVYHLEDÁNÍ a sečíst je. Pokud je vaše vyhledávací tabulka seřazená, můžete použít =SUM(LOOKUP(B2:B53,M3:N5)).
Pokud potřebujete sečíst všechny VLOOKUPY s verzí VLOOKUPu s přesnou shodou, budete potřebovat přístup k dynamickým polím, abyste je mohli používat =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Chcete-li se dozvědět více o dynamických polích, podívejte se na dynamická pole Excel přímo k bodu.
Sledovat video
Přepis videa
Learn Excel from, Podcast Episode 2247: Can You Return All VLookUp Values?
Ahoj. Vítejte zpět na netcastu. Jsem Bill Jelen. Minulý týden se na mém semináři v Appletonu ve Wisconsinu objevily dvě otázky - obě se týkaly. Řekli: „Hej, jak vrátíme všechny VLOOKUPY, dobře? V tomto případě, jako J1199, má spoustu zápasů a oni, víte, je chtějí vrátit všechny, a moje první otázka, kdykoli se mě někdo zeptá, je, no, co chcete dělat se zápasy? Jsou to čísla, která chcete sečíst, nebo je to text, který chcete zřetězit? A je to legrační. Dvě otázky na stejném semináři, jedna osoba je chtěla sečíst a druhá osoba chtěla zřetězit výsledky.
Pojďme se tedy na oba podívat. V popisu YouTube zkontrolujte obsah, kde můžete přeskočit na druhý, chcete-li vidět výsledek textu.
Dobře, takže první věc, pokud je chceme sečíst všechny, nebudeme VLOOKUP vůbec používat. Budeme používat funkci nazvanou SUMIF nebo SUMIFS, která bude sumarizovat vše, co odpovídá této položce. Takže SUMIFY. Tady jsou číselné hodnoty, které chceme sečíst, a stisknu F4, abych to uzamkl. Tímto způsobem, jak to kopíruji, bude to stále ukazovat na stejný rozsah a pak chceme jít zkontrolovat a zjistit, zda číslo JOB ve sloupci A, opět F4, je = k hodnotě nalevo od nás - v tomto případě E2 - a jak to kopírujeme, uvidíme CELKEM pro každou položku. (SUMIFY ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Pojďme se zde trochu podívat. J1199. Celkem je to 25365. Dobře. Takže to funguje. Pokud jsou to čísla a chcete získat všechna čísla a sečíst je, přepněte na SUMIF nebo SUMIFS, ale pokud je to text, v pořádku, nyní je tato funkce v Office 365 nová v únoru 2017. Pokud tedy máte Excel 2016 nebo Excel 2013 nebo Excel 2010 nebo některý z těchto starších, tuto funkci nebudete mít. Je to funkce zvaná TEXTJOIN. TEXTJOIN. Toto je další funkce od (Joe McDade - 01:50), který nám právě přinesl všechny ty skvělé vzorce dynamického pole na Ignite v roce 2018, a Joe se ujistil, že TEXTJOIN bude pracovat s poli, což je opravdu skvělé.
Takže oddělovač zde bude, MEZERNÍK, určitě PRÁZDNĚ ignorujte. Chceme zde EMPTY ignorovat, protože v této další části, příkazu IF, vygenerujeme spoustu prázdných míst. POKUD tato položka nad A2, F4, je = k tomuto číslu ÚLOHY, pak chci odpovídající položku ze sloupce C, F4, jinak chci „“ takhle. Zavřete toto prohlášení IF. Zavřete TEXTJOIN. Musím stisknout CONTROL + SHIFT + ENTER? Ne, já ne. Přináší mi všechny produkty, které se tak shodují, dobře? Takže pokud vracíme všechny SVYHLEDÁNÍ, pokud je chceme sečíst, ano, pokud je chceme zřetězit, ano. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Dobře, teď je tu ještě jedna možnost, když se mě lidé zeptají, jestli mohou vrátit všechny VLOOKUPy. Může to být problém, kdy zde chceme vyhledat každou z těchto nákladů a zjistit NÁKLADY NA MANIPULACI a poté je všechny sečíst. Líbí se mi, že sem nechci VLOOKUP a VLOOKUP a VLOOKUP zde a VLOOKUP zde. Chci je udělat úplně a v takovém případě použijeme funkci SUM a poté starou, starou funkci LOOKUP. LOOKUP říká, že všechny tyto hodnoty vyhledáme ve sloupci B. Nepotřebuji zde F4, protože to nikde nekopíruji. , Tady je naše vyhledávací tabulka. ), zavřete SUM, a zhasne a provede každý jednotlivý VLOOKUP a potom je všechny sečte. (= SUM (LOOKUP (B2: B53, K3: L5))))
No, hej. Všechna tato témata jsou mojí knihou LIV: 54 největších tipů všech dob. Kliknutím na toto písmeno i v pravém horním rohu získáte další informace.
Otázkou tedy je, zda můžete vrátit všechny VLOOKUPy? Něco takového, ale ve skutečnosti nepoužívá VLOOKUP. Buď to vyřešíme pomocí SUMIF, TEXTJOIN nebo SUM nebo LOOKUP.
No, hej. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Víte, dobře, o těchto dynamických polích mluvím už týden. Chtěl jsem udělat jedno video, kde jsem se nedotkl dynamických polí, protože vím, že mnoho lidí je ještě nemá, ale tady jsme. Je to outtake. Víte, nejsou abecední. To by bylo mnohem lepší, kdybychom je mohli třídit, a pokud náhodou máte nová dynamická pole, můžete to poslat do funkce TŘÍDIT, TŘÍDIT takhle, a stisknout ENTER, a nyní budou výsledky takto seřazeny.
Víte, že i tento vzorec by se s dynamickými poli mohl zlepšit. Vyhledávání vyžaduje použití, PRAVDA. Co kdybyste chtěli použít a, FALSE? Mohli bychom to změnit na VLOOKUP, vyhledat celý tento text do této tabulky, 2,. V tomto případě použiji TRUE, ale v jiném případě můžete použít FALSE. CONTROL + SHIFT + ENTER. Ne. Bude to fungovat, jasný? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True))))
Dynamická pole vycházející na začátku roku 2019 vyřeší tolik problémů.
Děkujeme, že jste se zúčastnili outtake zde. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: can-you-return-all-vlookup-values.xlsx
Když se někdo zeptá „Může VLOOKUP vrátit všechny shody, odpověď je Ne. Existuje však mnoho dalších funkcí, které mohou dělat v podstatě totéž.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„Normalizujte svá data tak, jak by ostatní nechali normalizovat svá data za vás.“
Kevin Lehrbass
