Excel dvoustranné vyhledávání pomocí VLOOKUP část 2

01 z 06

Spuštění funkce vnořeného MATCH

Vraťte se do části 1

Zadání funkce MATCH jako Argument indexu sloupcového čísla

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

V tomto příkladu máme však tři sloupce, o které chceme najít data, takže potřebujeme způsob, jak snadno změnit indexové číslo sloupce bez úpravy vzhledu vyhledávání.

Toto je místo, kde hraje funkce MATCH. Umožní nám to porovnat číslo sloupce s názvem pole - buď leden, únor nebo březen -, které zadáme do buňky E2 listu.

Funkce hnízdění

Funkce MATCH proto funguje jako argument sloupce indexu VLOOKUP .

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

Ruční zadání funkce MATCH

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

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

Při zadávání funkcí ručně musí být každá z argumentů funkce oddělena čárkou "," .

Tutorial Kroky

Zadání Argumentu funkce Lookup_value funkce MATCH

Prvním krokem při zadávání vnořené funkce MATCH je zadání argumentu Lookup_value .

Hodnota Lookup_value bude umístění nebo odkaz na buňku pro hledaný výraz, který chceme v databázi shodovat.

1. V dialogovém okně funkce VLOOKUP klepněte na řádek Col_index_num .
2. Zadejte název shody názvu a následnou otevřenou kulatou konzolu " ( "
3. Klepnutím na buňku E2 zadejte odkaz buňky do dialogového okna.
4. Zadejte čárku "," za odkazem na buňku E3, abyste dokončili zadání argumentu Lookup_value funkce MATCH.
5. Ponechte dialogové okno funkce VLOOKUP otevřené pro další krok v tutoriálu.

V posledním kroku tutoriálu budou hodnoty Lookup_values ​​zadány do buněk D2 a E2 listu .

02 ze dne 06

Přidání funkce Lookup_array pro funkci MATCH

Přidání funkce Lookup_array pro funkci MATCH

Tento krok zahrnuje přidání argumentu Lookup_array pro vnořenou funkci MATCH.

Lookup_array je rozsah buněk, které vyhledává vyhledávací argument vyhledávaný v předchozím kroku tutoriálu.

V tomto příkladu chceme, aby funkce MATCH prohledala buňky D5 až G5 pro shodu s názvem měsíce, který bude vložen do buňky E2.

Tutorial Kroky

Tyto kroky je třeba zadat za čárkou zadanou v předchozím kroku na řádku Col_index_num v dialogovém okně funkce VLOOKUP .

1. V případě potřeby klikněte na řádek Col_index_num za čárkou a umístěte kurzor na konec aktuální položky.
2. Zvýrazněte buňky D5 až G5 v pracovním listu a zadejte tyto odkazy na buňky jako rozsah, ve kterém je funkce vyhledávána.
3. Stisknutím klávesy F4 na klávesnici změňte tento rozsah na absolutní odkazy na buňky . Tímto způsobem umožníte zkopírovat vzorec dokončeného vyhledávání do jiných míst v pracovním listu v posledním kroku tutoriálu
4. Zadejte čárku "," za odkazem na buňku E3 dokončete položku argumentu Lookup_array funkce MATCH.

03 ze dne 06

Přidání typu shody a dokončení funkce MATCH

Přidání typu shody a dokončení funkce MATCH

Třetím a posledním argumentem funkce MATCH je argument Match_type.

Tento argument řekne aplikaci Excel, jak odpovídat hodnotám Lookup_value s hodnotami v Lookup_array. Možnosti jsou: -1, 0 nebo 1.

Tento argument je nepovinný. Pokud je funkce vynechána, použije výchozí hodnotu 1.

• pokud Match_type = 1 nebo je vynechán: MATCH najde nejvyšší hodnotu, která je menší nebo rovna Lookup_value. Pokud je tato hodnota vybrána, musí být data Lookup_array seřazeny ve vzestupném pořadí.
• pokud Match_type = 0: MATCH najde první hodnotu, která je přesně stejná jako hodnota Lookup_value. Data Lookup_array lze třídit v libovolném pořadí.
• pokud match_type = 1: MATCH najde nejmenší hodnotu, která je větší nebo rovna vyhledávací hodnotě. Pokud je tato hodnota vybrána, musí být data Lookup_array seřazeny v sestupném pořadí.

Tutorial Kroky

Tyto kroky se zadávají za čárkou zadanou v předchozím kroku na řádku Row_num v dialogovém okně funkce VLOOKUP .

1. Po druhé čárce na řádku Col_index_num zadejte nulu " 0 ", protože chceme, aby vnořená funkce vrátila přesnou shodu s měsícem v buňce E2.
2. Zadejte uzavírací kulatý držák " ) " pro dokončení funkce MATCH.
3. Ponechte dialogové okno funkce VLOOKUP otevřené pro další krok v tutoriálu.

04 z 06

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

Argument vyhledávání rozsahu

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 údaje o prodeji za konkrétní měsíc, 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 dvojrozměrné vzorec pro vzorec a zavřete dialogové okno
4. Vzhledem k tomu, že jsme ještě nezadali vyhledávací kritéria do buněk D2 a E2, bude v buňce F2 přítomna chyba # N / A
5. Tato chyba bude opravena v příštím kroku v tutoriálu, když přidáme vyhledávací kritéria v dalším kroku tutoriálu.

05 ze dne 06

Testování vzorce obousměrného vyhledávání

Testování vzorce obousměrného vyhledávání

Chcete-li použít vzorec obousměrného vyhledávání, abyste zjistili měsíční údaje o prodeji různých souborů cookie uvedených v poli tabulky, zadejte název souboru cookie do buňky D2, měsíc do buňky E2 a stiskněte klávesu ENTER na klávesnici.

Údaje o prodeji se zobrazí v buňce F2.

Tutorial Kroky

1. Klikněte na buňku D2 ve svém listu
2. Zadejte ovsenou kaši do buňky D2 a stiskněte klávesu ENTER na klávesnici
3. Klikněte na buňku E2
4. Zadejte únor do buňky E2 a stiskněte klávesu ENTER na klávesnici
5. Hodnota 1.345 dolarů - částka prodeje cookie Oatmeal v měsíci únor - by měla být zobrazena v buňce F2
6. V tomto okamžiku by se váš list měl shodovat s příkladem na stránce 1 tohoto tutoriálu
7. Vyzkoušejte vzorec vyhledávání dále zadáním jakékoliv kombinace typů souborů cookie a měsíců přítomných v table_array a hodnoty prodejů by měly být zobrazeny v buňce F2
8. Posledním krokem v tutoriálu je kopírování vzhledu vyhledávání pomocí funkce Fill Handle .

Pokud se zobrazí chybová zpráva jako #REF! se objeví v buňce F2, tento seznam chybových hlášení VLOOKUP vám může pomoci zjistit, kde je problém.

06 z 06

Kopírování vzorku dvourozměrného vyhledávání pomocí rukojeti naplnění

Kopírování vzorku dvourozměrného vyhledávání pomocí rukojeti naplnění

Chcete-li zjednodušit porovnávání údajů za různé měsíce nebo různé soubory cookie, vzorec vyhledávání může být zkopírován do jiných buněk, aby se mohly zobrazovat více částek najednou.

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

Vzhledem k tomu, že vzorec je zkopírován, aplikace Excel aktualizuje relativní odkazy na buňky tak, aby odrážely nové umístění vzorce. V tomto případě D2 se stává D3 a E2 se stává E3,

Rovněž Excel udržuje absolutní odkaz na buňku totéž, takže absolutní rozsah $ D $ 5: $ G $ 5 zůstává stejný, když 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í funkce Vyplnit rukojeť.

Tutorial Kroky

1. Klikněte na buňku D3 ve svém listu
2. Zadejte ovsenou kaši do buňky D3 a stiskněte klávesu ENTER na klávesnici
3. Klikněte na buňku E3
4. Zadejte příkaz March do buňky E3 a stiskněte klávesu ENTER na klávesnici
5. Klepnutím na buňku F2 ji vytvoříte jako aktivní buňku
6. Umístěte ukazatel myši nad černý čtverec do pravého dolního rohu. Ukazatel se změní na znaménko plus "+" - jedná se o rukojeť doplňování
7. Klepněte na levé tlačítko myši a přetáhněte rukojeť výplně dolů do buňky F3
8. Uvolněte tlačítko myši a buňka F3 by měla obsahovat vzorec dvourozměrného vyhledávání
9. Hodnota 1,287 dolarů - částka prodeje ořechových sušenek v měsíci březen - by měla být zobrazena v buňce F3