Podívejte se na video - Jak používat funkci VLOOKUP s více kritérii
Funkce VLOOKUP aplikace Excel ve své základní podobě může vyhledat jednu vyhledávací hodnotu a vrátit odpovídající hodnotu ze zadaného řádku.
Často však existuje potřeba použít VLOOKUP aplikace Excel s více kritérii.
Jak používat VLOOKUP s více kritérii
Předpokládejme, že máte data se jménem studentů, typem zkoušky a skóre z matematiky (jak je uvedeno níže):
Použití funkce VLOOKUP k získání matematického skóre pro každého studenta pro příslušné úrovně zkoušky může být výzva.
Lze namítnout, že lepší možností by byla restrukturalizace datové sady nebo použití kontingenční tabulky. Pokud vám to funguje, nic takového. Ale v mnoha případech jste uvízli s daty, která máte, a kontingenční tabulka nemusí být volbou.
V takových případech je tento návod pro vás.
Nyní existují dva způsoby, jak můžete získat vyhledávací hodnotu pomocí VLOOKUP s více kritérii.
- Pomocí pomocného sloupce.
- Pomocí funkce CHOOSE.
VLOOKUP s více kritérii - pomocí pomocného sloupce
Jsem fanouškem pomocných sloupců v aplikaci Excel.
Zjistil jsem dvě významné výhody použití pomocných sloupců oproti vzorcům pole:
- Umožňuje snadno porozumět tomu, co se děje v listu.
- Ve srovnání s funkcemi pole je to rychlejší (patrné u velkých datových sad).
Nechápejte mě špatně. Nejsem proti maticovým vzorcům. Miluji úžasné věci, které lze dělat pomocí maticových vzorců. Jen je ukládám pro zvláštní příležitosti, kdy všechny ostatní možnosti nepomáhají.
Vrátíme -li se k bodu otázky, pomocný sloupec je potřebný k vytvoření jedinečného kvalifikátoru. Tento jedinečný kvalifikátor pak lze použít k vyhledání správné hodnoty. Například v datech jsou tři Matt, ale existuje pouze jedna kombinace Matt a Unit Test nebo Matt a Mid-Term.
Zde jsou kroky:
- Vložte sloupec pomocníka mezi sloupce B a C.
- Ve sloupci pomocníka použijte následující vzorec: = A2 & ”|” & B2
- To by vytvořilo jedinečné kvalifikátory pro každou instanci, jak je uvedeno níže.
- To by vytvořilo jedinečné kvalifikátory pro každou instanci, jak je uvedeno níže.
- Použijte následující vzorec v G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopírovat pro všechny buňky.
Jak to funguje?
Vytváříme jedinečné kvalifikátory pro každou instanci jména a zkoušky. Ve zde použité funkci VLOOKUP byla vyhledávací hodnota upravena na $ F3 & ”|” & G $ 2, takže obě vyhledávací kritéria jsou kombinována a jsou použita jako jedna vyhledávací hodnota. Například vyhledávací hodnota pro funkci VLOOKUP v G2 je Matt | Unit Test. Nyní tato vyhledávací hodnota slouží k získání skóre z C2: D19.
Objasnění:
Pravděpodobně vás napadne několik otázek, tak jsem si řekl, že se pokusím odpovědět zde:
- Proč jsem použil | symbol při spojování obou kritérií? - V některých výjimečně vzácných (ale možných) podmínkách můžete mít dvě kritéria, která jsou odlišná, ale při kombinaci nakonec dávají stejný výsledek. Zde je velmi jednoduchý příklad (promiňte mi tu moji nedostatek kreativity):
Všimněte si toho, že zatímco A2 a A3 jsou různé a B2 a B3 jsou různé, kombinace jsou nakonec stejné. Pokud ale použijete oddělovač, pak by i kombinace byla jiná (D2 a D3).
- Proč jsem vložil pomocný sloupec mezi sloupce B a C a ne úplně doleva? - Vložení pomocného sloupku úplně doleva není na škodu. Ve skutečnosti, pokud nechcete temperovat s původními daty, měla by to být správná cesta. Udělal jsem to, protože mě to přimělo použít menší počet buněk ve funkci VLOOKUP. Místo toho, abych měl v poli tabulky 4 sloupce, mohl jsem spravovat pouze 2 sloupce. Ale to jsem jen já.
Nyní neexistuje jedna velikost, která by vyhovovala všem. Někteří lidé mohou při používání funkce VLOOKUP s více kritérii raději nepoužívat žádný pomocný sloupec.
Zde je tedy pro vás metoda sloupce, která není pomocná.
Stáhněte si ukázkový soubor
VLOOKUP s více kritérii - pomocí funkce CHOOSE
Použití maticových vzorců namísto pomocných sloupců vám ušetří nemovitosti v listu a výkon může být stejně dobrý, pokud se v sešitu použije méněkrát.
Vzhledem ke stejné sadě dat, která byla použita výše, zde je vzorec, který vám poskytne výsledek:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Protože se jedná o maticový vzorec, použijte jej pouze pomocí Ctrl + Shift + Enter, nikoli pouze Enter.
Jak to funguje?
Vzorec také používá koncept pomocného sloupce. Rozdíl je v tom, že místo vložení pomocného sloupce do listu jej považujte za virtuální pomocná data, která jsou součástí vzorce.
Ukážu vám, co myslím daty virtuálních pomocníků.
Na výše uvedeném obrázku, když vyberu CHOOSE část vzorce a stisknu F9, ukazuje výsledek, který by dal CHOOSE vzorec.
Výsledkem je {„Matt | Unit Test“, 91; „Bob | Unit Test“, 52;…}
Je to pole, kde čárka představuje další buňku ve stejném řádku a středník znamená, že následující data jsou v dalším sloupci. Tento vzorec tedy vytváří 2 sloupce dat - jeden sloupec má jedinečný identifikátor a jeden má skóre.
Když teď použijete funkci VLOOKUP, jednoduše vyhledá hodnotu v prvním sloupci (těchto virtuálních 2 sloupcových dat) a vrátí odpovídající skóre.
Stáhněte si ukázkový soubor
K vyhledání s více kritérii můžete použít také jiné vzorce (například INDEX/MATCH nebo SUMPRODUCT).
Víte o tom jiným způsobem? Pokud ano, podělte se se mnou v sekci komentáře.
Také by se vám mohly líbit následující návody LOOKUP:
- VLOOKUP vs. INDEX/ZÁPAS
- Získejte více hodnot vyhledávání bez opakování v jedné buňce.
- Jak zajistit, aby VLOOKUP rozlišoval velká a malá písmena.
- Použijte IFERROR s VLOOKUP a zbavte se #N/A chyb.
