V tomto článku sa naučíme Ako extrahovať stĺpcový index z tabuľky v programe Excel.
Scenár:
Pri práci s dlho rozptýlenými údajmi mnohokrát musíme ú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 problém vyriešiť?
Na to použijeme funkciu MATCH. Ak ste o tejto funkcii ešte nepočuli, zvyknite si. Je to funkcia vyhľadávania v Exceli, ktorá vracia Index vyhľadávanej hodnoty v poli. Tu musíme získať indexové číslo názvu stĺpca. Potom pristúpime k ďalšiemu kroku Ako vyhľadávať hodnoty v tabuľkách pomocou funkcie MATCH. Nasleduje generický vzorec
Všeobecný vzorec:
| = MATCH (názov stĺpca, záhlavie_tabulky, 0) |
názov stĺpca: vyhľadávacia hodnota
table_header: pole hlavičky tabuľky
0: vyhľadanie presnej zhody
Príklad:
Toto všetko môže byť mätúce na pochopenie. Pochopme vzorec s vysvetlením a príkladom. Tu máme tabuľku údajov v Hárku 1 ($ A $ 1: $ U $ 9995). Máme teda hlavičku tabuľky ako A1: U1 (prvý riadok tabuľky).
Pozrime sa na nasledujúci vzorec, ktorý je potrebné použiť v tabuľke.
Použite vzorec
| = MATCH (C4, Sheet1! $ A $ 1: $ U $ 1,0) |
Vysvetlenie:
- Funkcia MATCH vyhľadá hodnotu v bunke C4 „ID objednávky“
- List1! $ A $ 1: $ U $ 1 je argument vyhľadávacieho poľa.
- Pri vyhľadávaní presnej zhody sa zadáva argument 0.
Ako vidíte, stĺpcový index ID objednávky v tabuľke je 2. Je však veľmi dráždivé používať hlavičku tabuľky ako úplnú skratku, takže pre pole záhlavia tabuľky používame pomenovaný rozsah. Tu sa dozviete viac o pomenovaných rozsahoch. Pomenovaný rozsah použitý pre hlavičku tabuľky (List1! $ A $ 1: $ U $ 1) je „hlavička“.
Použite vzorec.
| = MATCH (C4, hlavička, 0) |
Tu vyššie uvedená fotografia vysvetľuje dve veci. Prvý je stĺpcový index stavu v tabuľke je 11 a druhý sa nazýva rozsah "hlavička" funguje dobre. Skopírujte vzorec pre zostávajúce názvy stĺpcov pomocou Ctrl + D alebo ťahaním nadol z pravého dolného okraja použitej bunky.
Tu máme všetky stĺpce Index. Teraz môžeme použiť ako vstup do funkcie VLOOKUP, ako je znázornené nižšie.
Register MATCH vo funkcii VLOOKUP:
Teraz máme riešenie pre to, ako získať stĺpcový index tabuľky. Vzorec môžeme použiť ako vstup do funkcie VLOOKUP. Potrebujeme napríklad názov produktu kúpený menom zákazníka „Pete Kriz“.
Použite vzorec:
| = VLOOKUP (D10, tabuľka, MATCH (E9, hlavička, 0), 0) |
Poznámka: uistite sa, že vyhľadávacia hodnota v D10 (Pete Kriz) musí byť v prvom stĺpci tabuľky.
Ako vidíte, vzorec vracia názov produktu z názvu zákazníka v tabuľke. Spravidla nepoužívame MATCH s funkciou VLOOKUP, pretože hodnota vyhľadávania musí byť v prvom stĺpci, čo sa sotva stane. Používame teda kombináciu funkcie INDEX a MATCH. Získajte viac informácií o tom, ako vyhľadávať hodnoty pomocou funkcií INDEX a MATCH.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Vzorec funguje pre text aj pre čísla.
- Funkcia vráti chybu #NA, ak argument vyhľadávacieho poľa pre funkciu MATCH nemá rovnakú dĺžku poľa tabuľky.
- Vzorec vráti chybu, ak sa hodnota lookup_value nezhoduje s hodnotou v tabuľke lookup_array.
- Funkcia zodpovedá presnej hodnote, pretože argument typu zhody pre funkciu MATCH je 0.
- Použite argument -1 pre menej ako, 0 pre presnú zhodu a 1 pre väčšiu ako pre vyhľadávanú zhodu.
- 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 extrahovať stĺpcový index z tabuľky 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 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.
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.