VLOOKUP je výkonná funkcia. Na jednom zo svojich seminárov v Power Exceli však často dostanem otázku od niekoho, kto chce vedieť, či program VLOOKUP dokáže vrátiť všetky zodpovedajúce hodnoty. Ako viete, VLOOKUP so štvrtým argumentom False vráti vždy prvú nájdenú zhodu. Na nasledujúcej snímke obrazovky bunka F2 vráti hodnotu 3623, pretože je to prvá zhoda nájdená pre úlohu J1199.
Otázka potom môže VLOOKUP vrátiť všetky zápasy?
VLOOKUP nebude. Ale iné funkcie môžu.
Ak chcete spočítať všetky náklady na prácu J1199, použili by ste =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Ak máte textové hodnoty a chcete spojiť všetky výsledky do jednej hodnoty, môžete použiť =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Tento vzorec funguje iba v Office 365 a Excel 2019.
Alebo možno budete musieť vrátiť všetky výsledky pre jednu úlohu do nového rozsahu hárka. =FILTER(B2:C53,A2:A53=K1,"None Found")Problém vyrieši úplne nová funkcia, ktorá príde na Office 365 v roku 2019:
Ľudia niekedy chcú vykonať všetky VYHĽADÁVANIA a zhrnúť ich. Ak je vaša vyhľadávacia tabuľka zoradená, môžete použiť =SUM(LOOKUP(B2:B53,M3:N5)).
Ak potrebujete zhrnúť všetky VLOOKUPY s verziou VLOOKUP s presnou zhodou, na to, aby ste ich mohli používať, musíte mať prístup k dynamickým poliam =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Ak sa chcete dozvedieť viac informácií o dynamických poliach, vyskúšajte Dynamické polia Excel priamo k bodu.
Pozeraj video
Prepis videa
Naučte sa Excel z podcastu Epizóda 2247: Môžete vrátiť všetky hodnoty VLookUp?
Ahoj. Vitajte späť na netcaste. Som Bill Jelen. Minulý týždeň sa na mojom seminári v Appletone vo Wisconsine objavili dve otázky, ktoré sa týkali oboch. Povedali: „Hej, ako vrátime všetky VLOOKUPY, dobre? V tomto prípade, ako napríklad J1199, má veľa zápasov a oni, ako viete, ich chcú vrátiť všetky, a moja prvá otázka, kedykoľvek sa ma niekto spýta, je: No, čo chcete robiť so zápasmi? Sú to čísla, ktoré chcete spočítať, alebo je to text, ktorý chcete spojiť? A je to sranda. Dve otázky na rovnakom seminári, jedna osoba ich chcela sčítať a druhá osoba chcela výsledky spojiť.
Poďme sa teda pozrieť na obe tieto možnosti. V popise služby YouTube si pozrite obsah, kde môžete prejsť na druhý, ak chcete vidieť výsledok textu.
Dobre, takže prvá vec, ak ich chceme všetky spočítať, nebudeme VLOOKUP vôbec používať. Použijeme funkciu s názvom SUMIF alebo SUMIFS, ktorá zhrnie všetko, čo sa zhoduje s touto položkou. Takže SUMIFY. Tu sú číselné hodnoty, ktoré chceme sčítať. Stlačením klávesu F4 to uzamknem. Týmto spôsobom, keď to budem kopírovať, bude to smerovať stále do toho istého rozsahu a potom chceme ísť skontrolovať a zistiť, či číslo JOB v stĺpci A, opäť tam F4, je = k hodnote vľavo od nás - v tomto prípade E2 - a keď to budeme kopírovať, uvidíme CELKOM pre každú položku. (SUMIFY ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Poďme si tu urobiť malú kontrolu. J1199. Spolu je to 25365. Dobre. Takže to funguje. Ak sú to čísla a chcete získať všetky čísla a spočítať ich, prepnite na SUMIF alebo SUMIFS, ale ak je to text, v poriadku, teraz je táto funkcia v Office 365 nová vo februári 2017. Takže ak máte Excel 2016 alebo Excel 2013 alebo Excel 2010 alebo niektorý z tých starších, túto funkciu nebudete mať. Je to funkcia s názvom TEXTJOIN. TEXTJOIN. Toto je ďalšia funkcia od (Joe McDade - 01:50), ktorý nám práve priniesol všetky tie skvelé vzorce dynamického poľa na Ignite v roku 2018, a Joe sa postaral o to, aby TEXTJOIN fungoval s poľami, čo je naozaj skvelé.
Takže oddeľovač tu bude, VESMÍR, PRÁZDNO určite ignorujte. Chceme tu ignorovať PRÁZDNE, pretože v tejto ďalšej časti, vyhlásení IF, vygenerujeme veľa prázdnych miest. AK je táto položka nad A2, F4, = k tomuto číslu ÚLOHY, potom chcem zodpovedajúcu položku zo stĺpca C, F4, inak chcem „“. Zatvorte toto vyhlásenie IF. Zatvorte TEXTJOIN. Musím stlačiť kombináciu klávesov CONTROL + SHIFT + ENTER? Nie, ja nie. Prináša mi všetky produkty, ktoré sa tak zhodujú, dobre? Takže vrátením všetkých SVYHLEDNUTÍ, ak ich chceme zhrnúť, áno, ak ich chceme zreťaziť, áno. (= TEXTJOIN („,“, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, „“))))
Dobre, teraz je tu ešte jedna možnosť, keď sa ma ľudia pýtajú, či môžu vrátiť všetky VLOOKUPY. Môže to byť problém, keď tu chceme vyhľadať každú z týchto nákladov a vypočítať NÁKLADY NA MANIPULÁCIU a potom ich všetky zhrnúť. Nechcem sem vkladať VLOOKUP a VLOOKUP sem a VLOOKUP sem a VLOOKUP sem. Chcem ich urobiť úplne a v takom prípade použijeme funkciu SUM a potom starú, starú funkciu LOOKUP. LOOKUP hovorí, že všetky tieto hodnoty vyhľadáme v stĺpci B. F4 tu nepotrebujem, pretože ho nikde nekopírujem. , Tu je naša vyhľadávacia tabuľka. ), zatvorte SUM a zhasne a urobí každý jednotlivý VLOOKUP a potom ich všetky takto sčíta. (= SUM (LOOKUP (B2: B53, K3: L5))))
No hej. Všetky tieto témy sú mojou knihou LIV: 54 najväčších rád všetkých čias. Kliknutím na toto písmeno v pravom hornom rohu získate ďalšie informácie.
Otázkou teda je, či môžete vrátiť všetky VLOOKUPY. Trochu, ale v skutočnosti nepoužíva VLOOKUP. Buď na riešenie použijeme SUMIF, TEXTJOIN alebo SUM alebo LOOKUP.
No hej. Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Viete, dobre, o týchto dynamických poliach hovorím už týždeň. Chcel som urobiť jedno video, kde som sa nedotkol dynamických polí, pretože viem, že ich ešte veľa ľudí nemá, ale sme tu. Je to dôsledok. Viete, nie sú to abecedné. Bolo by to oveľa lepšie, keby sme ich mohli triediť. Ak náhodou máte nové dynamické polia, môžete to poslať do funkcie SORT, takto SORT a stlačiť ENTER, a teraz sa výsledky zoradia takto.
Viete, že aj tento vzorec by sa mohol vylepšiť pomocou dynamických polí. Na vyhľadanie je potrebné použiť parameter TRUE. Čo keby ste chceli použiť a, FALSE? Mohli by sme to zmeniť na VLOOKUP, vyhľadať celý tento text v tejto tabuľke,. V tomto prípade použijem hodnotu TRUE, ale v inom prípade môžete použiť hodnotu FALSE. CONTROL + SHIFT + ENTER. Nie. Bude to fungovať, dobre? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True))))
Dynamické polia, ktoré vyjdú začiatkom roka 2019, vyriešia toľko problémov.
Ďakujeme, že ste sa zúčastnili programu outtake. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: can-you-return-all-vlookup-values.xlsx
Keď sa niekto spýta „Môže VLOOKUP vrátiť všetky zápasy, odpoveď je nie. Existuje však veľa ďalších funkcií, ktoré môžu robiť v podstate to isté.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Normalizujte svoje údaje tak, ako by to ostatní nechali pre vás.“
Kevin Lehrbass
