Jak načíst hodnotu z referenční mřížky podle hodnot indexu Uložit v aplikaci Excel

Anonim

V tomto článku se naučíme Jak načíst hodnotu z referenční mřížky podle hodnot indexu Uložit v Excelu

Scénář:

Mnohokrát při práci s dlouhými neuspořádanými daty musíme data nejprve vyčistit, než na nich budeme pracovat. Trvá to dlouho a chtěli jste extrahovat nějaké dotazy. K extrahování dat obvykle používáte funkci VLOOKUP. Ale když máme dlouhá data s mnoha sloupcovými poli, pak je to nepořádné, protože funkce VLOOKUP vyžaduje správný sloupcový index jako číslo, jinak vzorec vrátí chybu. Právě zde je vysvětlení vzorce pro tento druh problému.

Jak problém vyřešit?

Aby vzorec nejprve porozuměl, musíme si trochu zrevidovat následující funkce

  1. Funkce INDEX
  2. Funkce MATCH

Nyní vytvoříme vzorec pomocí výše uvedených funkcí. Funkce shody vrátí index vyhledávací hodnoty1 v poli záhlaví řádku. A další funkce MATCH vrátí index vyhledávací hodnoty 2 v poli záhlaví sloupce. Čísla indexů budou nyní vložena do funkce INDEX, aby se hodnoty získaly pod vyhledávací hodnotou z dat tabulky.

Obecný vzorec:

= INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

data : řada hodnot uvnitř tabulky bez záhlaví

lookup_value1 : hodnota k vyhledání v záhlaví řádku.

záhlaví řádků : Řádkové indexové pole k vyhledání.

lookup_value1 : hodnota k vyhledání v záhlaví sloupce.

hlavičky sloupců : sloupec Indexové pole k vyhledání.

Příklad:

Pochopit to všechno může být matoucí. Pojďme pochopit, jak používat funkci pomocí příkladu. Zde máme několik ukázkových dat objednávky a potřebujeme v tabulce najít chybějící data související s danou hodnotou.

Zde musíme najít podrobnosti objednávky, jako je množství, region a cena za objednávku zadanou 13-01-2019.

Použijte vzorec:

= INDEX (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F4, A1: D1, 0))

Vysvětlení:

  1. Funkce MATCH vrací index odpovídající hodnoty v daném 1D poli (horizontální nebo vertikální).
  2. MATCH (F4, A1: D1,0) vrací 3 jako index.
  3. MATCH (G3, A2: A11,0) vrací 5 jako index
  4. Funkce INDEX vrátí 5. prvek (od 2. řádku) ve 3. sloupci.

Jak vidíte, množství související s objednávkou zadanou 13-01-2019 vychází na 38. Nyní můžeme pomocí vzorce najít region, ze kterého byla objednávka zadána.

Použijte vzorec:

= INDEX (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F5, A1: D1, 0))

Jak vidíte, objednávka byla zadána z jižní oblasti. Nyní získejte cenu související s provedenou objednávkou

Použijte vzorec:

= INDEX (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F6, A1: D1, 0))

Jak vidíte, cena vychází na 82,84. Všimli jste si, že jsme extrahovali všechny detaily pomocí jednoho vzorce. Kombinace INDEX a MATCH je nejlepší k nalezení jedné hodnoty z tabulky.

Zde jsou všechny pozorovací poznámky týkající se používání vzorce.

Poznámky:

  1. Vzorec funguje pro text i čísla.
  2. Funkce vrací chybu #NA, pokud argument vyhledávacího pole funkce MATCH nemá stejnou délku pole tabulky.
  3. Vzorec vrací chybu, pokud lookup_value neodpovídá hodnotě v tabulce lookup_array.
  4. Funkce odpovídá přesné hodnotě, protože argument typu shody pro funkci MATCH je 0.
  5. Použijte argument -1 pro méně než, 0 pro přesnou shodu a 1 pro větší než shodu vyhledávání.
  6. Hodnoty vyhledávání lze zadat jako odkaz na buňku nebo přímo pomocí symbolu uvozovky (") ve vzorci jako argumentů.

Doufám, že jste pochopili Jak načíst hodnotu z referenční mřížky podle hodnot indexu Uložit v aplikaci Excel. Zde najdete další články o hodnotách vyhledávání v Excelu a funkcích Excelu 2019. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.

K vyhledání hodnoty použijte INDEX a MATCH : Funkce INDEX & MATCH k vyhledání hodnoty podle potřeby.

SUM rozsah s INDEX v Excelu : Pomocí funkce INDEX najděte součet hodnot podle potřeby.

Jak používat funkci INDEX v Excelu : Najděte INDEX pole pomocí funkce INDEX vysvětlené na příkladu.

Jak používat funkci MATCH v Excelu : Najděte MATCH v poli pomocí hodnoty INDEX uvnitř funkce MATCH vysvětlené na příkladu.

Jak používat funkci LOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce LOOKUP vysvětlené na příkladu.

Oblíbené články:

Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.

Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.

Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.

Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.