Selles artiklis õpime, kuidas otsida väärtusi 2d tabelist, kasutades Excelis funktsiooni INDEX-MATCH-MATCH.
Stsenaarium:
Oletame, et peate tegema mitu otsingut tabelist, milles on sadu veerge. Sellistel juhtudel võtab iga otsingu jaoks erinevate valemite kasutamine liiga palju aega. Kuidas oleks luua dünaamiline otsinguvalem, mille saate otsida pakutud päise järgi. Jah, me saame seda teha. Seda valemit nimetatakse INDEX MATCH MATCH valemiks või ütleme 2d otsinguvalemiks.
Kuidas probleemi lahendada?
Selleks, et valem mõistaks kõigepealt, peame järgmisi funktsioone veidi üle vaatama
- Funktsioon INDEX
- MATCH funktsioon
Funktsioon INDEX tagastab massiivi antud indeksi väärtuse.
Funktsioon MATCH tagastab massiivi (ühemõõtmelise massiivi) väärtuse esmakordse ilmumise indeksi.
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äljale otsinguväärtuse2 indeksi. Indeksinumbrid sisestatakse nüüd funktsiooni INDEX, et saada 2D tabeli andmetest otsinguväärtuse all olevad väärtused.
Ü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, mida otsida.
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 registrinumbri 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.
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 numbriväärtusi, kuna tekstiväärtustele see kuidagi ei kehti
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 ning rea_pealkiri, andmed ja veerupäis pealkirjaga nimega vahemikud, nagu ülaltoodud hetktõmmisel mainitud.
Nagu ülaltoodud hetktõmmist näete, on hind saadud kõrguse järgi (34) & Laius (21) nagu 53.10. 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 mõistsite, kuidas kasutada otsingu 2D tabelit Excelis funktsiooni INDEX & MATCH abil. Siit leiate rohkem Exceli otsinguväärtuse artikleid. Palun esitage oma küsimused kommentaarikasti allpool. Kindlasti aitame teid.
Väärtuse otsimiseks kasutage INDEX ja MATCH : Funktsioon INDEX & MATCH väärtuse otsimiseks vastavalt vajadusele.
SUM vahemik koos indeksiga Excelis : Kasutage funktsiooni INDEX, et leida soovitud väärtuste SUMM.
Funktsiooni SUM kasutamine Excelis : Leidke numbrite summa, kasutades näitega selgitatud funktsiooni SUM.
Funktsiooni INDEX kasutamine Excelis : Leidke massiivi INDEX näite abil selgitatud funktsiooni INDEX abil.
Funktsiooni MATCH kasutamine Excelis : Leidke massiivist MATCH, kasutades näite abil selgitatud funktsiooni MATCH sees olevat INDEX -väärtust.
Funktsiooni LOOKUP kasutamine Excelis : Otsige massiivist otsinguväärtus, kasutades näidisega selgitatud funktsiooni LOOKUP.
Funktsiooni VLOOKUP kasutamine Excelis : Otsige massiivist otsinguväärtus näite abil selgitatud funktsiooni VLOOKUP abil.
Funktsiooni HLOOKUP kasutamine Excelis : Otsige massiivist otsinguväärtus näite abil selgitatud funktsiooni HLOOKUP abil.
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks
Muutke ripploendit
Absoluutne viide Excelis
Kui tingimusvorminguga
Kui metamärkidega
Vlookup kuupäeva järgi
Liituge Excelis ees- ja perekonnanimega
