Sledovat video – Jak používat funkci VLOOKUP s více kritérii

Funkce Excel VLOOKUP ve své základní podobě může vyhledat jednu vyhledávací hodnotu a vrátit odpovídající hodnotu ze zadaného řádku.

Často je však nutné použít Excel VLOOKUP 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 matematickým skóre (jak je uvedeno níže):

Použití funkce VLOOKUP k získání matematického skóre pro každého studenta pro jednotlivé úrovně zkoušek může být výzvou.

Lze argumentovat že lepší možností by byla restrukturalizace datové sady nebo použití kontingenční tabulky. Pokud to funguje pro vás, 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í dvěma způsoby můžete získat vyhledávací hodnotu pomocí VLOOKUP s více kritérii.

• Použití pomocného sloupce.
• Použití funkce CHOOSE.

VLOOKUP s více kritérii – použití pomocného sloupce

Jsem fanouškem pomocných sloupců v aplikaci Excel.

Zjistil jsem dvě významné výhody použití pomocných sloupců nad maticovými vzorci:

• Usnadňuje pochopení toho, co se v listu děje.
• Zrychluje to ve srovnání s funkcemi pole (patrné u velkých datových sad).

Nechápejte mě špatně. Nejsem proti vzorcům polí. Miluji úžasné věci, které lze udělat pomocí maticových vzorců. Je to jen to, že je ukládám pro zvláštní příležitosti, když všechny ostatní možnosti nepomohou.

Když se vrátím k otázce, je třeba vytvořit pomocný sloupec, aby se vytvořil jedinečný kvalifikátor. Tento jedinečný kvalifikátor lze poté 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:

Jak to funguje?

Pro každou instanci jména a zkoušky vytváříme jedinečné kvalifikátory. Ve zde použité funkci VLOOKUP byla upravena vyhledávací hodnota na $ F3 & ”|” & G $ 2, takže obě vyhledávací kritéria jsou kombinovány a používají se jako jedna vyhledávací hodnota. Například vyhledávací hodnota pro funkci VLOOKUP v G2 je Matt | Unit Test. Nyní se tato vyhledávací hodnota používá k získání skóre z C2: D19.

Upřesnění:

Existuje několik otázek, které vás pravděpodobně napadnou, takže jsem si myslel, že se pokusí odpovědět zde:

• Proč jsem použil | symbol při spojování dvou 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á se liší, ale při kombinaci budou mít stejný výsledek. Zde je velmi jednoduchý příklad (odpusťte mi zde můj nedostatek kreativity):

Všimněte si, že zatímco A2 a A3 jsou různé a B2 a B3 jsou různé, kombinace nakonec zůstávají stejné. Pokud ale použijete oddělovač, pak by i kombinace byla jiná (D2 a D3).

• Proč jsem vložil pomocný sloup mezi sloupce B a C a ne úplně vlevo? – Vložení sloupku pomocníka zcela vlevo není na škodu. Ve skutečnosti, pokud nechcete zmírnit původní data, měla by to být cesta. Udělal jsem to, protože mě to přimělo používat menší počet buněk ve funkci VLOOKUP. Místo toho, abych měl v poli tabulky 4 sloupce, mohl jsem zvládnout pouze 2 sloupce. Ale to jsem jen já.

Nyní neexistuje žádná velikost, která by vyhovovala všem. Někteří lidé možná upřednostňují nepoužívat žádný pomocný sloupec, když používají VLOOKUP s více kritérii.

Takže zde je metoda sloupců bez pomocníka.

Stáhněte si ukázkový soubor

VLOOKUP s více kritérii – pomocí funkce CHOOSE

Použití maticových vzorců místo pomocníka Sloupce vám ušetří nemovitost 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é datové sadě, jaká byla použita výše, je zde vzorec, který vám dá výsledek :

= VLOOKUP ($ E3 & ”|“ & F $ 2, VYBERTE ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Jelikož se jedná o maticový vzorec, použijte jej s klávesami Control + Shift + Enter, nikoli pouze s klávesou Enter.

Jak to funguje?

Vzorec také používá koncept pomocného sloupce. Rozdíl je v tom, že místo vložení sloupce pomocníka do listu jej považujte za virtuální pomocná data, která jsou součástí vzorce.

Dovolte mi ukázat vám, co mám na mysli pod virtuálními pomocnými daty.

Na výše uvedeném obrázku, když vyberu část VÝBĚR vzorce a stisknu klávesu F9, zobrazí se výsledek, že CHOOSE vzorec dá.

Výsledkem je {„Matt | Unit Test“, 91; „Bob | Unit Test“, 52; ……}

Je to pole, kde je čá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.

Nyní , když použijete funkci VLOOKUP, jednoduše vyhledá hodnotu v prvním sloupci (těchto dat virtuálního 2 sloupce) a vrátí odpovídající skóre.

Stáhněte si Příklad souboru

K vyhledání s více kritérii (například INDEX / MATCH nebo SUMPRODUCT) můžete použít i jiné vzorce.