VLOOKUP je jednou z nejpoužívanějších funkcí v Excelu. Vyhledá hodnotu v rozsahu a vrátí odpovídající hodnotu v zadaném čísle sloupce.
Nyní jsem narazil na problém, kdy jsem musel vyhledat celý řádek a vrátit hodnoty ve všech sloupcích z tohoto řádku (namísto vrácení jediné hodnoty).
Tady je to, co jsem musel udělat. V níže uvedené datové sadě jsem měl názvy prodejních zástupců a tržby, které uskutečnili za 4 čtvrtletí v roce 2012. Měl jsem rozevírací seznam s jejich jmény a chtěl jsem v těchto čtyřech čtvrtletích extrahovat maximální prodeje pro tohoto obchodního zástupce.
Mohl bych přijít na 2 různé způsoby, jak to udělat - pomocí INDEX nebo VLOOKUP.
Vyhledejte celý řádek / sloupec pomocí vzorce INDEX
Zde je vzorec, který jsem k tomu vytvořil pomocí Indexu
= VELKÝ (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Jak to funguje:
Nejprve se podívejme na funkci INDEX, která je zabalena uvnitř funkce LARGE.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Pojďme podrobně analyzovat argumenty funkce INDEX:
- Pole - $ B $ 4: $ F $ 1
- Číslo řádku - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Číslo sloupce - 0
Všimněte si, že jsem použil číslo sloupce jako 0.
Jde o to, že když použijete číslo sloupce jako 0, vrátí všechny hodnoty ve všech sloupcích. Pokud tedy v rozevíracím seznamu vyberu Johna, vzorec indexu vrátí všechny 4 hodnoty prodeje pro Jana {91064,71690,67574,25427}.
Nyní mohu pomocí funkce Large extrahovat největší hodnotu
Pro tip - Chcete -li vrátit všechny hodnoty ve sloupcích/řádcích, použijte ve sloupci Index číslo sloupce/řádku jako 0.
Vyhledejte celý řádek / sloupec pomocí vzorce VLOOKUP
Zatímco vzorec indexu je čistý, čistý a robustní, způsob VLOOKUP je trochu složitý. To také skončí tím, že funkce bude volatilní. Existuje však úžasný trik, o který bych se v této sekci podělil. Zde je vzorec:
= VELKÉ (VLOOKUP (H3, B4: F13, ŘADA (NEPŘÍMÉ („2:“ & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Jak to funguje
- ŘÁDEK (NEPŘÍMÝ („2:“ & COUNTA ($ B $ 4: $ F $ 4)))) - Tento vzorec vrací pole {2; 3; 4; 5}. Všimněte si toho, že protože používá INDIRECT, činí tento vzorec nestálým.
- VLOOKUP (H3, B4: F13, ROW (NEPŘÍMÝ („2:“ & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Zde je nejlepší část. Když je dáte dohromady, stane se VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Nyní si všimněte, že místo jednoho čísla sloupce jsem mu dal řadu čísel sloupců. A VLOOKUP poslušně vyhledává hodnoty ve všech těchto sloupcích a vrací pole.
- Nyní stačí pomocí funkce LARGE extrahovat největší hodnotu.
Tento vzorec nezapomeňte použít Ctrl + Shift + Enter.
Pro Tip - Pokud použijete ve VLOOKUP místo jednoho čísla sloupce pole čísel sloupců, vrátí pole vyhledávacích hodnot.
