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

Spojením aplikace ExcelVLOOKUP funkce sSLOUPEC můžete vytvořit vzorec vyhledávání, který umožňuje vrátit více hodnot z jednoho řádku databáze nebo tabulky dat.

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 SLOUPEC funkce je vnořena uvnitř VLOOKUP. Nasazení funkce zahrnuje zadání druhé funkce jako jednoho z argumentů pro první funkci.

V tomto tutoriálu, SLOUPEC funkce bude zadána jako index sloupce číslo argument 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.

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 zobrazená na výše uvedeném obrázku do následujících buněk:

• Zadejte nejvyšší rozsah dat do buňky D1 na G1.
• Zadejte druhý rozsah do buňky D4 na G10.

Zobrazí se kritéria hledání a vzorec vyhledávání vytvořený během tohoto kurzu řádek 2 pracovního 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.

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ů.

Název rozsahu neobsahuje názvy nebo názvy polí pro data (řádek 4), ale pouze samotná data.

1. Zvýraznit buňky D5 na G10 v listu je vyberte.
2. Klikněte na tlačítko Název pole umístěné výše sloupec A.
3. Typ Stůl do Název pole.
4. zmáčkni Zadejte na klávesnici.
5. Buňky D5 na G10 nyní má název rozsahu Stůl. Budeme používat jméno pro VLOOKUP tabulkového pole 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 v tomto případě je použít VLOOKUP dialogové okno. 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ňka E2 pracovního listu - umístění, kde budou zobrazeny výsledky dvourozměrného vzorce vyhledávání.
2. Klikněte na tlačítko Karta Vzorce z stuha.
3. Klikněte na tlačítko Vyhledávání a reference v nabídce stuha k otevření rozevírací nabídky funkce.
4. Klikněte na VLOOKUP v seznamu otevřete dialogové okno funkce.

05 z 10

Zadání argumentu vyhledávací hodnoty

Normálně, vzhlédnout hodnota odpovídá pole dat v prvním sloupci datové tabulky. V našem příkladě vzhlédnout hodnota odkazuje na název hardwarové části, o které chceme najít informace. Přípustné typy dat pro vzhlédnout hodnota jsou textová data, logické hodnoty, čísla a odkazy na buňky.

Absolutní odkazy na buňky

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 buňky pro vzhlédnout hodnota, se změní, jakmile bude vzorec zkopírován, čímž dojde k chybě buňky F2 a G2.

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

Chcete-li zabránit chybám, konvertujeme odkaz na buňky D2 do absolutní reference buňky. Absolutní odkazy na buňky se vytvoří stisknutím tlačítka F4 na klávesnici. Přitom přidává dolarové značky kolem odkazu na buňky, jako je například $ D $ 2.

1. Klikněte na tlačítko lookup_value v dialogovém okně.
2. Klikněte na buňka D2 přidat tento článek odkaz na lookup_value line - to je buňka, kde budeme zadávat název dílu, o kterém vyhledáváme informace
3. Bez přesunu bodu vložení stiskněte tlačítko F4 klíč na klávesnici pro převod D2 do absolutní reference buňky $ D $ 2.
4. Opustit VLOOKUP dialogové okno funkce 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), zatímco druhý sloupec bude prohledáván 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.

1. Klikněte na tlačítko table_array v dialogovém okně.
2. Typ Stůl pro zadání názvu rozsahu tohoto argumentu.
3. Opustit VLOOKUP dialogové okno funkce pro další krok v tutoriálu.

07 z 10

Vložení funkce COLUMN

Normálně VLOOKUP pouze vrací data z jednoho sloupce datové tabulky a tento sloupec je nastaven pomocí indexové číslo sloupce argument.

V tomto příkladu však máme tři sloupce, ze kterých chceme data vrátit, 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 SLOUPEC funkce přichází.

Zadáním příkazu jako indexové číslo sloupce argument, bude se měnit, jak se zkopíruje vzorec vyhledávání buňka D2 na buňky E2 a F2 později v tutoriálu.

Funkce hnízdění

The SLOUPEC tedy funguje jako VLOOKUP's indexové číslo sloupce argument; to je dosaženo hnízdením SLOUPEC funkce uvnitř VLOOKUP v Col_index_num řádku dialogového okna.

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. The SLOUPEC funkce, proto musí být zadána ručně v Col_index_num řádek. The SLOUPEC funkce má pouze jeden argument - Odkaz argument, který je odkaz na buňku.

Výběr argumentu sloupce funkce COLUMN

The SLOUPEC Funkce je vrátit číslo sloupce daného jako Odkaz argument. Jinými slovy konvertuje písmeno sloupce na číslo s sloupec A jako první sloupec, sloupec B druhá a tak dále.

Od prvního pole dat, co chceme vrátit, je cena položky, která je ve sloupci 2 datové tabulky. Můžeme zvolit odkaz buňky pro libovolnou buňku sloupec B jako Odkaz Argument pro získání čísla 2 pro Col_index_num argument.

1. V VLOOKUP dialogové okno funkce, klepněte na tlačítko Col_index_num řádek.
2. Zadejte název funkce sloupec následovaný znakem otevřený kruhový držák.
3. Klikněte na buňka B1 v listu zadejte odkaz buňky jako Odkaz argument.
4. Zadejte a uzavření kulatého držáku k dokončení SLOUPEC funkce
5. Opustit VLOOKUP dialogové okno funkce pro další krok v tutoriálu.

08 z 10

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

VLOOKUP's Range_lookup argument je logická hodnota (SKUTEČNÝ nebo NEPRAVDIVÉ), která označuje, zda chcete VLOOKUP najít přesnou nebo přibližnou shodu s Vyhledávací_hodnota.

• Li SKUTEČNÝ nebo pokud je tento argument vynechán: VLOOKUP vrátí buď přesnou shodu s Vyhledávací_hodnotaNebo pokud není nalezena přesná shoda, VLOOKUP vrátí další největší hodnotu. Pro tento vzorec použijte údaje v prvním sloupci Table_array musí být roztříděny ve vzestupném pořadí.
• Li NEPRAVDIVÉ: VLOOKUP bude používat přesné shody pouze k Vyhledávací_hodnota. Pokud jsou v prvním sloupci hodnoty dvě nebo více hodnot Table_array které odpovídají hodnotě vyhledávání, použije se první nalezená hodnota. Pokud není nalezena přesná shoda, a # N / A chyba je vrácena.

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

1. Klikněte na tlačítko Range_lookup v dialogovém okně.
2. Zadejte slovo Nepravdivé v tomto řádku označujeme, že chceme VLOOKUP abychom vrátili přesnou shodu údajů, které hledáme.
3. Klikněte na tlačítko OK pro dokončení vzhledu vyhledávání a zavření dialogového okna.
4. Jelikož jsme ještě nezadali vyhledávací kritéria buňka D2 an # Chyba N / A bude přítomen v buňka E2.
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í vzorce vyhledávání

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, aby načítal data z sloupce 2, 3, a 4 datové tabulky - to je cena, číslo součásti a jméno dodavatele při zadávání částečného názvu jako Vyhledávací_hodnota.

Vzhledem k tomu, že data jsou v pracovním listu uspořádána pravidelně, můžeme zkopírovat vzorec vyhledávání v souboru buňka E2 na buňky F2 a G2.

Jak je vzorec zkopírován, aplikace Excel aktualizuje relativní odkaz buňky v souboru SLOUPEC funkce (buňka B1), aby odrážel nové umístění vzorce.

Také Excel nemění absolutní odkaz na buňku $ D $ 2 a pojmenovaný rozsah Stůl jak je vzorec zkopírován.

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

1. Klikněte na buňka E2, kde je vyhledávací vzorec umístěn, aby se stala aktivní buňkou.
2. Umístete ukazatel myši přes černý čtverec v pravém dolním rohu - ukazatel se změní na znaménko plus, které označuje popisovač plnění.
3. Klepněte na levé tlačítko myši a přetáhněte ovládací prvek napříč buňka G2.
4. Uvolnění tlačítko myši a buňka F3 by měl obsahovat dvourozměrný vzorec vyhledávání.
5. Pokud jste provedli správně, buňky F2 a G2 by nyní měl také obsahovat # N / A chyba, která je přítomna v buňka 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 příkaz název položky chcete načíst do Vyhledávací_hodnota buňka (D2) a stiskněte tlačítko Zadejte 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.

1. Klikněte na buňka D2 v listu.
2. Typ Widget do buňka D2 a stiskněte tlačítko Zadejte na klávesnici.
3. Následující informace by měly být zobrazeny v buňky E2 na G2.
   1. E2: 14,76 dolarů - cena widgetu
   2. F2: PN-98769 - číslo dílu widgetu
   3. G2: Widgets Inc. - jméno dodavatele widgetů
4. Otestujte VLOOKUP pole formuláře dále zadáním názvu ostatních částí buňka D2 a pozorování výsledků v buňky E2 na G2