Šajā rakstā mēs uzzināsim, kā izmantot funkciju INDEX un MATCH, lai meklētu vērtību programmā Excel.
Funkcija INDEX & MATCH, lai aizstātu vlookup funkciju
Mēs parasti izmantojam funkciju VLOOKUP, lai atrastu vērtību, zinot rindas Indekss un kolonnas numuru. Lai izmantotu funkciju VLOOKUP, mums ir jābūt kolonnu numuram un rindu atbilstības kritērijiem un vissvarīgākajai vērtībai, lai noskaidrotu, vai tā atrodas atbilstošās vērtības labajā pusē. Bet dati ne vienmēr to atbalsta. Daudzas reizes mums vienkārši jāmeklē vērtības, nezinot rindu vai kolonnu indeksu, bet tikai jāatbilst vērtībām. Piemēram: atrast atalgojumu darbiniekam, kuram ir darbinieka ID. Šajā mēs meklēsim darbinieku ID rindu indeksam un algu kolonnu indeksam. Uzzināsim, kā mēs varam veikt šo uzmeklēšanu, izmantojot INDEX & MATCH.
Kā atrisināt problēmu?
Lai formula vispirms saprastu, mums nedaudz jāpārskata šādas funkcijas
- Funkcija INDEX
- MATCH funkcija
Tagad mēs izveidosim formulu, izmantojot iepriekš minētās funkcijas. Atbilstības funkcija rindas galvenes laukā atgriež uzmeklēšanas vērtības1 indeksu. Un cita MATCH funkcija atgriezīs uzmeklēšanas vērtības 2 indeksu no kolonnas galvenes lauka. Indeksa numuri tagad tiks ievadīti funkcijā INDEX, lai tabulas datos iegūtu vērtības zem uzmeklēšanas vērtības.
Vispārējā formula:
| = INDEKSS (dati, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
dati : vērtību masīvs tabulā bez galvenēm
lookup_value1 : vērtība, ko meklēt rindas_virsrakstā.
row_headers : Meklējamais rindu indeksa masīvs.
lookup_value1 : vērtība, ko meklēt kolonnā_virsraksts.
column_headers : kolonna Meklējamais rādītāju masīvs.
Piemērs:
Iepriekš minētos apgalvojumus var būt sarežģīti saprast. Tāpēc sapratīsim to, izmantojot formulā sniegto piemēru
Šeit ir saraksts ar rezultātiem, ko studenti ieguvuši ar mācību priekšmetu sarakstu. Mums ir jāatrod konkrēta studenta (Gerijs) un priekšmeta (sociālās studijas) rādītājs, kā parādīts zemāk esošajā momentuzņēmumā.
Studenta vērtībai1 ir jāatbilst masīvam Rinda_galva, bet 2. tēmai - masīvam Kolonna_galva.
Izmantojiet formulu šūnā J6:
| = INDEKSS (tabula, MATCH (J5, rinda, 0, MATCH (J4, kolonna, 0))) |
Paskaidrojums:
- Funkcija MATCH atbilst studenta vērtībai J4 šūnā ar rindu galvenes masīvu un atgriež savu pozīciju 3 kā skaitlis.
- Funkcija MATCH saskaņo Tēmas vērtību šūnā J5 ar kolonnu galvenes masīvu un atgriež tās pozīciju 4 kā skaitlis.
- Funkcija INDEX ņem rindu un kolonnu indeksa numuru un uzmeklē tabulas datus un atgriež atbilstošo vērtību.
- Arguments MATCH type ir fiksēts uz 0. Tā kā formula iegūs precīzu atbilstību.
Šeit formulas vērtības tiek norādītas kā šūnu atsauces, bet rindas virsraksts, tabula un kolonnu virsraksts - kā nosaukti diapazoni.
Kā redzat iepriekšējā momentuzņēmumā, mēs saņēmām studenta iegūto rezultātu Gerijs tēmā Sociālās studijas kā 36. Un tas pierāda, ka formula darbojas labi, un šaubām skatiet zemāk esošās piezīmes.
Tagad mēs izmantosim aptuveno atbilstību ar rindu virsrakstiem un kolonnu galvenēm kā skaitļus. Aptuvenā atbilstība ņem tikai skaitļu vērtības, jo nekādā veidā tā netiek piemērota teksta vērtībām
Šeit mums ir vērtību cena atbilstoši produkta augstumam un platumam. Mums ir jāatrod cena konkrētam augstumam (34) un platumam (21), kā parādīts zemāk esošajā momentuzņēmumā.
Augstuma vērtībai1 ir jāatbilst masīvam Rindas_galva, bet platuma vērtībai2 - masīvam Kolonna_galva.
Izmantojiet formulu šūnā K6:
| = INDEKSS (dati, MATCH (K4, augstums, 1, MATCH (K5, platums, 1))) |
Paskaidrojums:
- Funkcija MATCH atbilst augstuma vērtībai šūnā K4 ar rindu galvenes masīvu un atgriež savu pozīciju 3 kā skaitlis.
- Funkcija MATCH atbilst platuma vērtībai K5 šūnā ar kolonnu galvenes masīvu un atgriež savu pozīciju 2 kā skaitlis.
- Funkcija INDEX ņem rindu un kolonnu indeksa numuru un uzmeklē tabulas datus un atgriež atbilstošo vērtību.
- Arguments MATCH type ir fiksēts uz 1. Tā kā formula iegūs aptuveno atbilstību.
Šeit formulas vērtības tiek norādītas kā šūnu atsauces, un rindas_virsraksts, dati un kolonnas_virsraksts - kā nosaukti diapazoni, kā minēts iepriekš esošajā momentuzņēmumā.
Varat arī veikt uzmeklēšanas precīzās atbilstības, izmantojot funkciju INDEX un MATCH programmā Excel. Uzziniet vairāk par to, kā veikt reģistrjutīgu meklēšanu, izmantojot funkciju INDEX & MATCH programmā Excel. Daļējās atbilstības varat arī meklēt, izmantojot Excel aizstājējzīmes.
Kā redzat iepriekšējā momentuzņēmumā, mēs saņēmām cenu, kas iegūta pēc auguma (34) & Platums (21) kā 53.10. Un tas pierāda, ka formula darbojas labi, un šaubām skatiet zemāk esošās piezīmes.
Piezīmes:
- Funkcija atgriež kļūdu #NA, ja uzmeklēšanas masīva arguments MATCH funkcijai ir 2D masīvs, kas ir datu galvenes lauks …
- Funkcija atbilst precīzai vērtībai, jo atbilstības veida arguments ar funkciju MATCH ir 0.
- Uzmeklēšanas vērtības var norādīt kā šūnu atsauci vai tieši, izmantojot formulā pēdiņu simbolu (") kā argumentus.
Ceru, ka šis raksts par to, kā izmantot Izmantot funkciju INDEX un MATCH, lai meklētu vērtību programmā Excel, ir paskaidrojošs. Vairāk rakstu par vērtību un saistīto Excel formulu aprēķināšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Izmantojiet INDEX un MATCH, lai atrastu vērtību : Formulu INDEX-MATCH izmanto, lai dinamiski un precīzi meklētu vērtību noteiktā tabulā. Šī ir alternatīva funkcijai VLOOKUP, un tā novērš funkcijas VLOOKUP trūkumus.
Izmantojiet VLOOKUP no divām vai vairākām uzmeklēšanas tabulām : Lai meklētu no vairākām tabulām, mēs varam izmantot IFERROR pieeju. Lai meklētu no vairākām tabulām, kļūda tiek uzskatīta par nākamās tabulas slēdzi. Vēl viena metode var būt If pieeja.
Kā veikt reģistrjutīgu meklēšanu programmā Excel : Excel VLOOKUP funkcija nav reģistrjutīga, un tā atgriezīs pirmo atbilstošo vērtību no saraksta. INDEX-MATCH nav izņēmums, taču to var mainīt, lai padarītu to reģistrjutīgu. Redzēsim, kā…
Meklējiet bieži parādīto tekstu ar kritērijiem programmā Excel : Meklēšana visbiežāk parādās tekstā diapazonā, kurā izmantojam funkciju INDEX-MATCH with MODE. Lūk, metode.
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.