VLOOKUP na dvou nebo více sloupcích kritérií – Excel University
Pokud jste někdy zkoušeli použít funkci VLOOKUP se dvěma nebo více sloupci kritérií, rychle jste zjistili, že prostě to nebylo postaveno pro tento účel. Naštěstí existuje další funkce, která může fungovat jako alternativa k VLOOKUP v závislosti na tom, co chcete vrátit.
Cíl vyhledávání ve více sloupcích
Nejprve si náš cíl ověřte pohledem na ukázkový sešit. Exportovali jsme některé informace z našeho účetního systému a v zásadě shrnují součty transakcí za měsíc podle třídy a účtu. Níže je ukázka exportu:
Z těchto exportovaných dat bychom chtěli načíst vybrané částky na základě sloupce třídy a účtu. Chceme získat částky a umístit je do našeho malého přehledu, který je zobrazen níže:
Pokud znáte VLOOKUP funkce, je přirozené pokusit se sestavit sestavu pomocí této funkce, protože koneckonců jde o vyhledávací úlohu. A vyhledávací úkoly se nejlépe vyřeší tradičními vyhledávacími funkcemi … že? No, to záleží. Záleží na tom, co se pokoušíte načíst.
Podmíněné sčítání pro vyhledávání
Pokud se pokoušíte načíst číselnou hodnotu, například částku, nemusí tradiční vyhledávací funkce buď nejlepší. Zde je důvod. Počínaje aplikací Excel 2007 zahrnovala společnost Microsoft funkci podmíněného sčítání SUMIFS. Tato funkce sčítání více podmínek je navržena tak, aby sčítala sloupec čísel a zahrnovala pouze řádky, které splňují jednu nebo více podmínek. Začínají se tečky již spojovat?
Pokud použijeme tuto myšlenku na náš úkol, rychle si uvědomíme, že bychom mohli použít tuto funkci podmíněného sčítání k načtení hodnot našeho přehledu.
Prvním argumentem funkce SUMIFS je rozsah součtu, tj. Sloupec čísel, který se má přidat. V našem případě sloupec s hodnotou, kterou chceme vrátit. Zbývající argumenty přicházejí ve dvojicích: rozsah kritérií a hodnota kritérií.
Je užitečné přemýšlet o funkci v těchto termínech: sečtěte tento sloupec (argument 1), zahrňte pouze ty řádky, kde tento sloupec (argument 2) se rovná této hodnotě (argument 3) a kde se tento sloupec (argument 4) rovná této hodnotě (argument 5), a kde… a tak dále, až 127 párů.
Abychom vyplnili náš přehled, načteme hodnoty částky z exportu a porovnáme sloupce třídy a účtu, jak je uvedeno níže.
Pokud existuje více řádků se stejnou třídou a účty, funkce SUMIFS vrátí součet všech odpovídajících položek.
Jak vidíte, pokud hodnotu se snaží vrátit je číslo, pak funkce SUMIFS usnadňuje provádění vyhledávání ve více sloupcích. Ale co když hodnota, kterou se pokoušíte vrátit, není číslo? Pak budete muset použít tradiční vyhledávací funkci, jak je popsáno níže.
Použití VLOOKUP s metodou SUMIFS
Jednou z metod je použití VLOOKUP a SUMIFS v jednom vzorci. V zásadě používáte SUMIFS jako první argument VLOOKUP. Tato metoda je plně prozkoumána v tomto příspěvku na Excel University:
Použití VLOOKUP s metodou CONCATENATE
Pokud se snažíte vrátit textový řetězec místo čísla nebo používáte verzi Excel, který nemá SUMIFS, pak jste pravděpodobně uvízl pomocí tradiční vyhledávací funkce, jako je VLOOKUP, spolu s funkcí CONCATENATE k vygenerování jednoho jedinečného vyhledávacího sloupce. Tento přístup je poměrně dobře zdokumentován, ale základní myšlenka zní takto: nejprve vytvořte jeden vyhledávací sloupec a poté použijte VLOOKUP.
Naším příkladem bude seznam zaměstnanců, jak je znázorněno níže:
Potřebujeme načíst stav ze seznamu zaměstnanců pro náš malý přehled uvedený níže:
Protože hodnota, kterou se pokoušíme vrátit, stav, je textový řetězec a ne číslo, je nám zakázáno používat funkci SUMIFS. Budeme tedy muset jít do staré školy s VLOOKUP a CONCATENATE.
Začínáme vytvořením pomocného sloupce, který v zásadě vytváří kombinované vyhledávací hodnoty. Toho lze snadno dosáhnout pomocí funkce CONCATENATE nebo operátoru zřetězení (&). Tento nový vyhledávací sloupec je zobrazen ve sloupci B níže:
Nyní máme jeden vyhledávací sloupec, který lze použít s tradiční vyhledávací funkce, například VLOOKUP. Přehled lze naplnit vyhledáním kombinovaných názvů v novém rozsahu vyhledávání, jak je uvedeno níže:
Stejný přístup lze použít, když je třeba vzít v úvahu dva, tři nebo více vyhledávacích sloupců.
Závěr
Kromě možnosti provádět vyhledávání ve více sloupcích, když je návratová hodnota číselná, má funkce SUMIFS ve srovnání s tradičními vyhledávacími funkcemi další výhody. Například vrátí nulu, když není nalezena žádná odpovídající hodnota, vrátí součet všech shod, podporuje operátory porovnání a nerozbije se, když se mezi vyhledávací a návratové sloupce vloží nový sloupec.
Takže, když se chystáte vypustit funkci VLOOKUP za účelem vyhledání, zvažte místo toho použití SUMIFS. Věřte tomu nebo ne, funkce SUMIFS je skvělá vyhledávací funkce.
Pokud máte jiné preferované přístupy k vyhledávání ve více sloupcích, rádi bychom se dozvěděli více … prosím pošlete komentář níže.
Ukázkový soubor
Pokud si chcete hrát se sešitem použitým ke generování výše uvedených snímků obrazovky, stáhněte si ukázkový soubor:
MultColumnLookup