Selles artiklis õpime, kuidas kasutada funktsiooni INDEX ja MATCH Exceli väärtuse otsimiseks.
Funktsioon INDEX & MATCH asendab funktsiooni vlookup
Tavaliselt kasutame väärtuse leidmiseks funktsiooni VLOOKUP, teades rea indeksit ja veeru numbrit. Funktsiooni VLOOKUP jaoks peavad meil olema veeru number ja ridade sobitamise kriteeriumid ning kõige olulisem väärtus, et teada saada, kas see asub sobiva väärtuse paremal. Kuid andmed ei toeta seda alati. Mitu korda peame lihtsalt otsima väärtusi, ei tea rea või veeru indeksit, vaid lihtsalt peavad need väärtused vastama. Näiteks: töötaja ID -ga töötaja palga leidmine. Selles otsime reaindeksi töötaja ID -d ja veeruindeksi palka. Õpime, kuidas seda otsingut INDEX & MATCH abil teha.
Kuidas probleemi lahendada?
Selleks, et valem mõistaks kõigepealt, peame järgmisi funktsioone veidi üle vaatama
- Funktsioon INDEX
- MATCH funktsioon
Nüüd teeme ülaltoodud funktsioone kasutades valemi. Funktsioon Vaste tagastab rea päise väljale otsinguväärtuse indeksi1. Ja teine MATCH -funktsioon tagastab veerupäise väljalt otsinguväärtuse 2 indeksi. Indeksinumbrid sisestatakse nüüd funktsiooni INDEX, et tabeli andmetest otsinguväärtuse all olevad väärtused kätte saada.
Üldine valem:
| = INDEX (andmed, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
andmed : väärtuste massiiv tabelis ilma päisteta
lookup_value1 : väärtus, mida otsida rida_pealkirjast.
rea_pealkirjad : Ridaindeksi massiiv otsimiseks.
lookup_value1 : väärtus, mida otsida veerust_pealkiri.
veeru_pealkirjad : veerg Otsitav indeksmassiiv.
Näide:
Ülaltoodud väiteid võib olla keeruline mõista. Nii et mõistame seda näite valemi abil
Siin on nimekiri õpilaste aineainete nimekirjaga saadud tulemustest. Peame leidma konkreetse õpilase (Gary) ja aine (ühiskonnaõpetus) skoori, nagu on näidatud alloleval pildil.
Õpilase väärtus1 peab vastama massiivile Rida_pealkiri ja Teema väärtus2 massiivile Veerupäis.
Kasutage lahtris J6 valemit:
| = INDEX (tabel, MATCH (J5, rida, 0, MATCH (J4, veerg, 0))) |
Selgitus:
- Funktsioon MATCH sobitab õpilase väärtuse lahtris J4 rea päise massiiviga ja tagastab selle asukoha 3 numbrina.
- Funktsioon MATCH sobitab lahtri J5 väärtuse Veerg päise massiiviga ja tagastab selle asukoha 4 numbrina.
- Funktsioon INDEX võtab ridade ja veergude indeksi numbri ning otsib üles tabeli andmed ja tagastab sobitatud väärtuse.
- MATCH tüüpi argument on fikseeritud väärtusele 0. Kuna valem ekstraheerib täpse vaste.
Siin on valemi väärtused antud lahtriviidetena ja rea_pealkiri, tabel ja veerupealkiri nimega vahemikena.
Nagu ülaltoodud hetktõmmist näete, saime üliõpilase saadud tulemuse Gary teemas Ühiskonnaõpetus nagu 36. Ja see tõestab, et valem töötab hästi ja kahtluste saamiseks vaadake mõistmiseks allolevaid märkusi.
Nüüd kasutame numbrina ligikaudset vastet ridade päiste ja veerupäistega. Ligikaudne vaste võtab ainult arvväärtusi, kuna tekstiväärtustele ei saa seda kuidagi rakendada
Siin on väärtuste hind vastavalt toote kõrgusele ja laiusele. Peame leidma konkreetse kõrguse (34) ja laiuse (21) hinna, nagu on näidatud alloleval pildil.
Kõrgusväärtus1 peab vastama massiivile Row_header ja laius väärtus2 massiivile Column_header.
Kasutage lahtris K6 järgmist valemit:
| = INDEX (andmed, MATCH (K4, kõrgus, 1, MATCH (K5, laius, 1))) |
Selgitus:
- Funktsioon MATCH sobitab K4 lahtri kõrguse väärtuse rea päise massiiviga ja tagastab selle asukoha 3 numbrina.
- Funktsioon MATCH sobitab K5 lahtri laiuse väärtuse veerupäise massiiviga ja tagastab selle asukoha 2 numbrina.
- Funktsioon INDEX võtab ridade ja veergude indeksi numbri ning otsib üles tabeli andmed ja tagastab sobitatud väärtuse.
- MATCH tüüpi argument on fikseeritud väärtuseks 1. Kuna valem eraldab ligikaudse vaste.
Siin on valemi väärtused antud lahtriviidetena ja rea_pealkiri, andmed ja veeru_pealkiri nimega vahemikena, nagu ülaltoodud hetktõmmisel mainitud.
Täpseid vasteid saate teha ka Exceli funktsiooni INDEX ja MATCH abil. Lugege lisateavet selle kohta, kuidas teha Exceli rakenduses INDEX & MATCH funktsiooni tõstutundlik otsing. Osalisi vasteid saate otsida ka Exceli metamärkide abil.
Nagu ülaltoodud hetktõmmist näete, saime hinna pikkuse järgi (34) & Laius (21) nagu 53.10. Ja see tõestab, et valem töötab hästi ja kahtluste saamiseks vaadake mõistmiseks allolevaid märkusi.
Märkused:
- Funktsioon tagastab vea #NA, kui funktsiooni MATCH otsimismassiivi argument on 2D massiiv, mis on andmete päiseväli …
- Funktsioon vastab täpsele väärtusele, kuna vaste tüübi argument MATCH -funktsioonile on 0.
- Otsinguväärtused võib anda lahtriviidena või kasutada valemis argumentidena otse tsitaatsümbolit (").
Loodetavasti see artikkel selle kohta, kuidas kasutada Exceli väärtuse otsimiseks funktsiooni INDEX ja MATCH kasutamine on selgitav. Siit leiate rohkem artikleid väärtuste arvutamise ja sellega seotud Exceli valemite kohta. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Väärtuse otsimiseks kasutage INDEX ja MATCH : Valemit INDEX-MATCH kasutatakse antud tabeli väärtuse dünaamiliseks ja täpseks otsimiseks. See on alternatiiv funktsioonile VLOOKUP ja see kõrvaldab funktsiooni VLOOKUP puudused.
Kasutage kahe või enama otsingutabeli VLOOKUP -i : Mitmest tabelist otsimiseks saame kasutada IFERROR -lähenemist. Mitmest tabelist otsimiseks võtab viga järgmise tabeli lülitina. Teine meetod võib olla If -lähenemine.
Kuidas Excelis teha tõstutundlikku otsingut? : Exceli funktsioon VLOOKUP ei ole tõstutundlik ja tagastab loendist esimese sobitatud väärtuse. INDEX-MATCH pole erand, kuid seda saab muuta, et muuta see tõstutundlikuks. Vaatame, kuidas…
Otsige Excelis sageli esinevat teksti kriteeriumidega : Otsing ilmub kõige sagedamini tekstis vahemikus, mida kasutame funktsiooni INDEX-MATCH with MODE abil. Siin on meetod.
Populaarsed artiklid:
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.