Ako získať hodnotu z referenčnej mriežky podľa hodnôt indexu Uložiť v programe Excel

Anonim

V tomto článku sa naučíme Ako získať hodnotu z referenčnej mriežky podľa hodnôt indexu Uložiť v programe Excel

Scenár :

Pri práci s dlhými nevyrovnanými údajmi mnohokrát potrebujeme údaje najskôr vyčistiť, než na nich budeme pracovať. Trvá to dlho a chceli ste iba extrahovať niektoré otázky. Na extrahovanie údajov spravidla používate funkciu VLOOKUP. Ale keď máme dlhé údaje s mnohými poľami stĺpcov, začne to byť chaotické, pretože funkcia VLOOKUP vyžaduje ako číslo správny index stĺpca, inak vzorec vráti chybu. Hneď nižšie je vysvetlenie vzorca pre tento druh problému.

Ako vyriešiť problém?

Aby vzorec najskôr porozumel, musíme si trocha zrevidovať nasledujúce funkcie

  1. Funkcia INDEX
  2. Funkcia MATCH

Teraz vytvoríme vzorec pomocou vyššie uvedených funkcií. Funkcia zhody vráti index vyhľadávacej hodnoty1 v poli hlavičky riadka. A ďalšia funkcia MATCH vráti index vyhľadávanej hodnoty 2 do poľa hlavičky stĺpca. Indexové čísla budú teraz vložené do funkcie INDEX, aby sa hodnoty dostali pod vyhľadávaciu hodnotu z údajov tabuľky.

Generický vzorec:

= INDEX (údaje, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))))

údaje : pole hodnôt v tabuľke bez hlavičiek

lookup_value1 : hodnota, ktorá sa má vyhľadať v hlavičke riadka.

riadky_záhlavia : Riadok Indexové pole na vyhľadávanie.

lookup_value1 : hodnota, ktorá sa má vyhľadať v hlavičke stĺpca.

hlavičky stĺpcov : stĺpec Indexové pole na vyhľadávanie.

Príklad:

Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako používať funkciu na príklade. Tu máme niekoľko vzorových údajov o objednávke a musíme v tabuľke nájsť chýbajúce údaje súvisiace s danou hodnotou.

Tu musíme nájsť podrobnosti objednávky, ako je množstvo, región a cena za objednávku zadanú 13-01-2019.

Použite vzorec:

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

Vysvetlenie:

  1. Funkcia MATCH vracia index zodpovedajúcej hodnoty v danom 1D poli (horizontálne alebo vertikálne).
  2. MATCH (F4, A1: D1,0) vráti 3 ako index.
  3. MATCH (G3, A2: A11,0) vráti 5 ako index
  4. Funkcia INDEX vráti 5. prvok (od 2. riadka) v 3. stĺpci.

Ako vidíte, množstvo súvisiace s objednávkou zadanou 13-01-2019 vychádza na 38. Teraz pomocou vzorca nájdeme región, z ktorého bola objednávka zadaná.

Použite vzorec:

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

Ako vidíte, objednávka bola zadaná z južného regiónu. Teraz získajte cenu súvisiacu s uskutočnenou objednávkou

Použite vzorec:

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

Ako vidíte, cena vychádza na 82,84. Všimli ste si, že sme extrahovali všetky detaily pomocou jedného vzorca. Kombinácia INDEX a MATCH je najlepšia na nájdenie jednej hodnoty z tabuľky.

Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.

Poznámky:

  1. Vzorec funguje pre text aj pre čísla.
  2. Funkcia vráti chybu #NA, ak argument vyhľadávacieho poľa pre funkciu MATCH nemá rovnakú dĺžku poľa tabuľky.
  3. Vzorec vráti chybu, ak sa hodnota lookup_value nezhoduje s hodnotou v tabuľke lookup_array.
  4. Funkcia zodpovedá presnej hodnote, pretože argument typu zhody pre funkciu MATCH je 0.
  5. Použite argument -1 pre menej ako, 0 pre presnú zhodu a 1 pre väčšiu ako pre vyhľadávanú zhodu.
  6. Hodnoty vyhľadávania je možné zadať ako odkaz na bunku alebo priamo pomocou symbolu úvodzovky (") vo vzorci ako argumentov.

Dúfam, že ste pochopili Ako získať hodnotu z referenčnej mriežky podľa hodnôt indexu Uložiť v programe Excel. Tu nájdete ďalšie články o hodnotách vyhľadávania v Exceli a funkciách Excelu 2019. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.

Na vyhľadanie hodnoty použite INDEX a MATCH : Funkcia INDEX & MATCH na vyhľadanie hodnoty podľa potreby.

Rozsah SUM s INDEX v Exceli : Pomocou funkcie INDEX nájdite SÚČET hodnôt podľa potreby.

Ako používať funkciu INDEX v programe Excel : Nájdite INDEX poľa pomocou funkcie INDEX vysvetlenej na príklade.

Ako používať funkciu MATCH v programe Excel : Nájdite MATCH v poli pomocou hodnoty INDEX vo funkcii MATCH vysvetlenej na príklade.

Ako používať funkciu LOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie LOOKUP vysvetlenej na príklade.

Populárne články:

Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.

Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.

Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.

Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.