VLOOKUP je jednou z najpoužívanejších funkcií v programe Excel. Vyhľadá hodnotu v rozsahu a vráti zodpovedajúcu hodnotu v zadanom čísle stĺpca.
Teraz som narazil na problém, keď som musel vyhľadať celý riadok a vrátiť hodnoty vo všetkých stĺpcoch z tohto riadka (namiesto vrátenia jednej hodnoty).
Tu je teda to, čo som musel urobiť. V nižšie uvedenom súbore údajov som mal názvy predajných zástupcov a predaje, ktoré vykonali za 4 štvrťroky v roku 2012. Mal som rozbaľovací zoznam s ich menami a chcel som v týchto štyroch štvrťrokoch extrahovať maximálny predaj pre tohto obchodného zástupcu.
Môžem prísť s 2 rôznymi spôsobmi, ako to urobiť - pomocou INDEX alebo VLOOKUP.
Vyhľadajte celý riadok / stĺpec pomocou vzorca INDEX
Tu je vzorec, ktorý som na to vytvoril pomocou indexu
= VEĽKÝ (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Ako to funguje:
Najprv sa pozrime na funkciu INDEX, ktorá je zabalená do funkcie LARGE.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Pozrime sa podrobne na argumenty funkcie INDEX:
- Pole - $ B $ 4: $ F $ 1
- Číslo riadku - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Číslo stĺpca - 0
Všimnite si toho, že som použil číslo stĺpca ako 0.
Ide o to, že keď použijete číslo stĺpca ako 0, vráti všetky hodnoty vo všetkých stĺpcoch. Ak teda v rozbaľovacom zozname vyberiem Johna, indexový vzorec by vrátil všetky 4 hodnoty predaja pre Jána {91064,71690,67574,25427}.
Teraz môžem pomocou funkcie Large extrahovať najväčšiu hodnotu
Pro tip - Na vrátenie všetkých hodnôt v stĺpcoch/riadkoch použite vo stĺpci Index číslo stĺpca/riadka ako 0.
Vyhľadajte celý riadok / stĺpec pomocou vzorca VLOOKUP
Aj keď je vzorec indexu úhľadný, čistý a robustný, spôsob VLOOKUP je trochu komplexný. Tiež to skončí tým, že bude funkcia volatilná. Existuje však úžasný trik, o ktorý by som sa v tejto časti podelil. Tu je vzorec:
= VEĽKÉ (VLOOKUP (H3, B4: F13, ROW (NEPRIAMY ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Ako to funguje
- ROW (NEPRIAMY („2:“ & COUNTA ($ B $ 4: $ F $ 4)))) - Tento vzorec vráti pole {2; 3; 4; 5}. Všimnite si toho, že pretože používa INDIRECT, robí tento vzorec nestálym.
- VLOOKUP (H3, B4: F13, ROW (NEPRIAMY („2:“ A COUNTA ($ B $ 4: $ F $ 4))), NEPRAVDA) - Tu je najlepšia časť. Keď ich spojíte, stane sa VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Teraz si všimnite, že namiesto jedného čísla stĺpca som mu dal pole čísel stĺpcov. A VLOOKUP poslušne vyhľadá hodnoty vo všetkých týchto stĺpcoch a vráti pole.
- Teraz stačí použiť funkciu VEĽKÁ na extrahovanie najväčšej hodnoty.
Na použitie tohto vzorca nezabudnite použiť Ctrl + Shift + Enter.
Pro tip - Ak použijete pole VLOOKUP namiesto jedného čísla stĺpca, ak použijete pole čísel stĺpcov, vráti pole vyhľadávacích hodnôt.
