Najděte více polí dat v aplikaci Excel VLOOKUP

Kombinací funkce VLOOKUP aplikace Excel s funkcí COLUMN můžeme vytvořit vzorec vyhledávání, který umožňuje vrátit více hodnot z jednoho řádku databáze nebo tabulky dat.

V příkladu uvedeném na výše uvedeném obrázku vyhledávací vzorec umožňuje snadné vrácení všech hodnot - například ceny, čísla dílů a dodavatele - souvisejících s různými kusy hardwaru.

01 z 10

Vrátit více hodnot pomocí aplikace Excel VLOOKUP

Následující kroky vytvoří vzorec vyhledávání, který je vidět na obrázku výše, a vrátí několik hodnot z jednoho záznamu dat.

Vzorec vyhledávání vyžaduje, aby funkce COLUMN byla vnořena uvnitř VLOOKUP.

Nasazení funkce zahrnuje zadání druhé funkce jako jednoho z argumentů pro první funkci.

V tomto výukovém programu bude funkce COLUMN zadána jako argument indexu sloupcového indexu pro VLOOKUP.

Posledním krokem v tutoriálu je kopírování vzhledu vyhledávání do dalších sloupců, aby se získaly další hodnoty pro vybranou část.

Obsah výuky

• Zadání výukového data
• Vytvoření pojmenovaného rozsahu pro tabulku dat
• Spuštění funkce VLOOKUP
• Zadání argumentu vyhledávací hodnoty pomocí referencí absolutních buněk
• Zadání argumentu tabulkového pole
• Zadání funkce Vnořené sloupce
• Dokončení funkce VLOOKUP
• Kopírování vzhledu vyhledávání s rukojetí výplně
• Získání dat pomocí vzorce vyhledávání

02 z 10

Zadejte výukové údaje

Prvním krokem v tutoriálu je zadání dat do listu aplikace Excel.

Chcete-li postupovat podle pokynů v tutoriálu, zadejte data uvedená na obrázku do následujících buněk .

• Zadejte nejvyšší rozsah dat do buněk D1 až G1
• Zadejte druhý rozsah do buněk D4 až G10

Kritéria vyhledávání a vzorec vyhledávání vytvořený během tohoto kurzu budou zadány do řádku 2 listu.

Výukový program neobsahuje formátování viděné v obraze, ale to neovlivní způsob, jakým funguje vyhledávací vzorec.

Informace o možnostech formátování podobné těm, které jste viděli výše, jsou k dispozici v tomto základním výukovém formátu Excel .

Tutorial Kroky

1. Zadejte data, jak je vidět na obrázku výše, do buněk D1 až G10

03 z 10

Vytvoření pojmenovaného rozsahu pro tabulku dat

Pojmenovaný rozsah je snadný způsob odkazu na rozsah dat ve vzorci. Místo zadání odkazů na buňky pro data můžete pouze zadat název rozsahu.

Druhou výhodou pro použití pojmenovaného rozsahu je to, že odkazy na buňky pro tento rozsah se nikdy nezmění, i když je vzorec zkopírován do jiných buněk v listu.

Názvy rozsahu jsou proto alternativou k použití absolutních odkazů na buňky, aby se předešlo chybám při kopírování vzorců.

Poznámka: Název rozsahu neobsahuje názvy nebo názvy polí pro data (řádek 4), ale pouze údaje samotné.

Tutorial Kroky

1. Zvýrazněte buňky D5 až G10 v listu a vyberte je
2. Klikněte na pole Název nad sloupcem A
3. Do pole Název zadejte "Tabulka" (bez uvozovek)
4. Stiskněte klávesu ENTER na klávesnici
5. Buňky D5 až G10 mají nyní název "Tabulka". Název argumentu tabulkového pole VLOOKUP použijeme později v tutoriálu

04 z 10

Otevření dialogového okna VLOOKUP

I když je možné prostě zadat vzorec vyhledávání přímo do buňky v pracovním listu, mnozí lidé považují za obtížné zachovat syntaxi rovně - zejména pro komplexní vzorec, jako je ten, který používáme v tomto výukovém programu.

Alternativou je v tomto případě použít dialogové okno VLOOKUP. Téměř všechny funkce aplikace Excel mají dialogové okno, které umožňuje zadat jednotlivé argumenty funkce na samostatném řádku.

Tutorial Kroky

1. Klikněte na buňku E2 listu - místo, kde se zobrazí výsledky dvourozměrného vzorce vyhledávání
2. Klikněte na kartu Vzorky karet
3. Klepnutím na možnost Vyhledat a odkaz na pásu karet otevřete rozbalovací seznam funkcí
4. Klepnutím na VLOOKUP v seznamu otevřete dialogové okno funkce

05 z 10

Zadání argumentu vyhledávací hodnoty pomocí referencí absolutních buněk

Obvykle se vyhledávací hodnota shoduje s datovým polem v prvním sloupci datové tabulky.

V našem příkladu hodnota vyhledávání odkazuje na název hardwarové části, o které chceme najít informace.

Povolené typy dat pro vyhledávací hodnotu jsou:

• textových dat
• logická hodnota (pouze TRUE nebo FALSE)
• číslo
• odkaz na buňku na hodnotu v listu

V tomto příkladu uvedeme odkaz na buňku tam, kde bude umístěno jméno - buňka D2.

Absolutní odkazy na buňky

V pozdějším kroku v tutoriálu zkopírujeme vzorec vyhledávání v buňce E2 do buněk F2 a G2.

Normálně, když jsou vzorky zkopírovány do aplikace Excel, odkazy na buňky se změní tak, aby odrážely jejich nové umístění.

Pokud k tomu dojde, D2 - odkaz na buňku pro vyhledávací hodnotu - se změní, jakmile je vzorec zkopírován, čímž se vytvoří chyby v buňkách F2 a G2.

Abychom zabránili chybám, převedeme odkaz na buňky D2 na absolutní referenci buňky .

Absolutní odkazy na buňky se při kopírování vzorců nemění.

Absolutní odkazy na buňky se vytvářejí stisknutím klávesy F4 na klávesnici. Tímto způsobem přidáte dolarové značky kolem odkazu na buňku, například $ D $ 2

Tutorial Kroky

1. Klikněte na řádek lookup_value v dialogovém okně
2. Klepnutím na buňku D2 přidejte tento článek na řádek lookup_value . Jedná se o buňku, kde zadáme název dílu, o kterém vyhledáváme informace
3. Bez přesunu bodu vložení stiskněte klávesu F4 na klávesnici pro převod D2 na absolutní odkaz buňky $ D $ 2
4. Ponechte dialogové okno funkce VLOOKUP otevřené pro další krok v tutoriálu

06 z 10

Zadání argumentu tabulkového pole

Tabulkový matice je tabulka dat, která vyhledávací vzorec vyhledává, aby našla požadované informace.

Soubor tabulky musí obsahovat alespoň dva sloupce dat .

• první sloupec obsahuje argument vyhledávací hodnoty (předchozí krok v tutoriálu)
• druhá a další sloupce budou prohledávány vzorem pro vyhledání informací, které specifikujeme.

Argument tabulkového pole musí být zadán buď jako rozsah obsahující odkazy na buňky pro datovou tabulku nebo jako název rozsahu .

V tomto příkladu použijeme název rozsahu vytvořený v kroku 3 tutoriálu.

Tutorial Kroky

1. Klepněte na řádek table_array v dialogovém okně
2. Zadejte "Tabulka" (bez uvozovek) pro zadání názvu rozsahu tohoto argumentu
3. Ponechte dialogové okno funkce VLOOKUP otevřené pro další krok v tutoriálu

07 z 10

Vložení funkce COLUMN

Normálně vrátí VLOOKUP pouze data z jednoho sloupce datové tabulky a tento sloupec je nastaven argumentem indexu sloupců .

V tomto příkladu však máme tři sloupce, ze kterých chceme vrátit data, takže potřebujeme způsob, jak snadno změnit indexové číslo sloupce bez úpravy našeho vzorce vyhledávání.

To je místo, kde funkce COLUMN přichází. Zadáním jako argument indexu sloupcového indexu se změní, jakmile bude vzorec vyhledávání zkopírován z buňky D2 do buněk E2 a F2 později v tutoriálu.

Funkce hnízdění

Funkce COLUMN se proto chová jako argument pro číslo sloupce VLOOKUP.

To je dosaženo vložením funkce COLUMN uvnitř VLOOKUP v řádku Col_index_num v dialogovém okně.

Ruční zadání funkce COLUMN

Při vkládání funkcí Excel neumožňuje otevřít dialogové okno druhé funkce a zadat její argumenty.

Proto musí být funkce COLUMN zadána ručně v řádku Col_index_num .

Funkce COLUMN má pouze jeden argument - referenční argument, který je odkazem na buňku.

Výběr argumentu sloupce funkce COLUMN

Úloha funkce COLUMN je vrátit číslo sloupce zadaného jako referenční argument.

Jinými slovy, konvertuje sloupec písmeno na číslo se sloupcem A je první sloupec, sloupec B druhý a tak dále.

Vzhledem k tomu, že první pole údajů, které chceme vrátit, je cena položky - což je ve sloupci 2 tabulky údajů - můžeme zvolit odkaz buňky pro libovolnou buňku ve sloupci B jako referenční argument, abychom získali číslo 2 pro argumentu Col_index_num .

Tutorial Kroky

1. V dialogovém okně funkce VLOOKUP klepněte na řádek Col_index_num
2. Zadejte název sloupce názvu a následně otevřený kruhový rámeček " ( "
3. Klepnutím na buňku B1 v listu zadejte odkaz buňky jako referenční argument
4. Zadejte uzavírací kruhový rámeček " ) " pro dokončení funkce COLUMN
5. Ponechte dialogové okno funkce VLOOKUP otevřené pro další krok v tutoriálu

08 z 10

Zadání Argumentu vyhledávání rozsahu VLOOKUP

Argument Range_lookup VLOOKUP je logická hodnota (TRUE nebo FALSE only), která označuje, zda chcete VLOOKUP najít přesnou nebo přibližnou shodu s Lookup_value.

• Pokud je TRUE nebo pokud je tento argument vynechán, VLOOKUP vrací buď přesnou shodu s hodnotou Lookup_value, nebo pokud není nalezena přesná shoda, VLOOKUP vrátí další největší hodnotu. Pro tento vzorec musí být data v prvním sloupci table_array seřazeny ve vzestupném pořadí .
• Pokud FALSE, VLOOKUP bude používat pouze přesnou shodu na Lookup_value. Pokud existují dvě nebo více hodnot v prvním sloupci table_array, které odpovídají hodnotě vyhledávání, použije se první nalezená hodnota. Pokud není nalezena přesná shoda, vrátí se chyba # N / A.

V tomto tutoriálu, protože hledáme konkrétní informace o konkrétní hardwarové položce, nastavíme Range_lookup na hodnotu False .

Tutorial Kroky

1. Klepněte na řádek Range_lookup v dialogovém okně
2. Zadejte v tomto řádku slovo False a označte, že chceme, aby VLOOKUP vrátil přesné shodu pro data, která hledáme
3. Klepnutím na tlačítko OK dokončete vzorec vyhledávání a zavřete dialogové okno
4. Vzhledem k tomu, že jsme ještě nezadali vyhledávací kritéria do buňky D2, bude v buňce E2 přítomna chyba # N / A
5. Tato chyba bude opravena, když přidáme vyhledávací kritéria v posledním kroku tutoriálu

09 z 10

Kopírování vzhledu vyhledávání s rukojetí výplně

Vzorec vyhledávání je určen k načítání dat z více sloupců datové tabulky najednou.

Chcete-li to provést, vzorec vyhledávání musí být umístěn ve všech polích, ze kterých chceme získat informace.

V tomto tutoriálu chceme získat data ze sloupců 2, 3 a 4 datové tabulky - to je cena, číslo součásti a jméno dodavatele při zadání názvu součásti jako Lookup_value.

Vzhledem k tomu, že data jsou v pracovním listu seřazeny pravidelně, můžeme kopírovat vzorec vyhledávání v buňce E2 do buněk F2 a G2.

Vzhledem k tomu, že vzorec je zkopírován, aplikace Excel aktualizuje relativní odkaz buňky ve funkci COLUMN (B1) tak, aby odrážel nové umístění vzorce.

Také Excel nezmění absolutní odkaz buňky $ D $ 2 a pojmenovaný rozsah tabulky jako vzorec je zkopírován.

Existuje více způsobů, jak kopírovat data v aplikaci Excel, ale pravděpodobně nejsnazší způsob je pomocí funkce Vyplnit rukojeť .

Tutorial Kroky

1. Klikněte na buňku E2 - kde se nachází vyhledávací vzorec - aby se stala aktivní buňkou
2. Umístěte ukazatel myši nad černý čtverec do pravého dolního rohu. Ukazatel se změní na znaménko plus " + " - to je popisovač výplně
3. Klepněte na levé tlačítko myši a přetáhněte rukojeť výplně do buňky G2
4. Uvolněte tlačítko myši a buňka F3 by měla obsahovat vzorec dvourozměrného vyhledávání
5. Pokud se provede správně, buňky F2 a G2 by nyní měly obsahovat také chybu # N / A, která je přítomna v buňce E2

10 z 10

Zadání vyhledávacích kritérií

Jakmile je vyhledávací vzorec zkopírován do požadovaných buněk , lze jej použít k načtení informací z tabulky dat.

Chcete-li tak učinit, zadejte název položky, kterou chcete načíst, do buňky Lookup_value (D2) a stiskněte klávesu ENTER na klávesnici.

Po dokončení by každá buňka obsahující vzorec vyhledávání měla obsahovat jiný údaj o hardwarové položce, kterou hledáte.

Tutorial Kroky

1. Klikněte na buňku D2 v listu
2. Zadejte Widget do buňky D2 a stiskněte klávesu ENTER na klávesnici
3. Následující informace by měly být zobrazeny v buňkách E2 až G2:
   • E2 - 14,76 dolarů - cena widgetu
   • F2 - PN-98769 - číslo dílu widgetu
   • G2 - Widgets Inc. - jméno dodavatele widgetů
4. Vyzkoušejte vzorec pole VLOOKUP dále zadáním názvu ostatních částí do buňky D2 a pozorováním výsledků v buňkách E2 až G2

Pokud se zobrazí chybová zpráva jako #REF! se zobrazí v buňkách E2, F2 nebo G2, tento seznam chybových hlášení VLOOKUP vám může pomoci zjistit, kde se problém nachází.