Tabelis on ainulaadse võtmega lihtne väärtust otsida. Saame lihtsalt kasutada funktsiooni VLOOKUP. Kui aga teie andmetes pole seda ainulaadset veergu ja teil on vaja otsida mitmest veerust, et see väärtus sobiks, ei aita VLOOKUP.
Niisiis, mitme väärtusega tabeli väärtuse otsimiseks kasutame INDEX-MATCH-INDEX valemit.
Üldvalem mitme kriteeriumi otsimiseks
= INDEX (otsinguvahemik, MATCH (1, INDEX ((kriteerium1 = vahemik1)*(kriteerium2 = vahemik2)*(kriteerium N = vahemikN), 0,1), 0))
lookup_range: See on vahemik, millest soovite väärtust hankida.
Kriteeriumid 1, kriteeriumid 2, kriteeriumid N: Need on kriteeriumid, mida soovite vahemikus 1, vahemikus 2 ja vahemikus N sobitada. Teil võib olla kuni 270 kriteeriumi - vahemiku paarid.
Vahemik1, vahemik2, vahemikN: Need on vahemikud, milles vastate oma vastavatele kriteeriumidele.
Kuidas see toimib? Vaatame…
INDEX ja MATCH mitme kriteeriumiga
Siin on mul andmetabel. Soovin tõmmata kliendi nime, kasutades broneerimise kuupäeva, ehitajat ja ala. Siin on mul kolm kriteeriumi ja üks otsinguvahemik.
Kirjutage see valem lahtrisse I4, vajutades sisestusklahvi.
= INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Kuidas see töötab:
Me juba teame, kuidas INDEX ja MATCH funktsioon EXCELis töötavad, nii et ma ei hakka seda siin selgitama. Me räägime trikkist, mida me siin kasutasime.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Peamine osa on see. Selle väite iga osa tagastab massiivi tõene vale.
Kui tõeväärtused korrutatakse, tagastavad nad massiivi 0 ja 1. Korrutamine toimib JA -operaatorina. Raske, kui kõik väärtused on tõesed, siis tagastab see 1 muu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) See kõik tuleb tagasi
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; false
Mis tõlgitakse
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funktsioon INDEX tagastab sama massiivi ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) funktsiooni MATCH otsimismassiivina.
MATCH(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funktsioon MATCH otsib massiivist {0; 0; 0; 0; 0; 0 1 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ja tagastab massiivist leitud esimese 1 indeksi numbri. Mis siin on 8.
INDEKS(E2: E16,MATCH(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Lõpuks tagastab INDEX leitud väärtuse antud vahemikust (E2: E16) indeks (8).
Lihtne ????. Kahjuks ei saa seda lihtsamaks muuta.
Massiivi lahendus
Kui saate sellest tulenevalt vajutada klahve CTRL + SHIFT + ENTER, saate sisemise INDEX -funktsiooni kõrvaldada. Lihtsalt kirjuta see valem ja vajuta CTRL + SHIFT + ENTER.
= INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Üldine massiivivalem mitme kriteeriumi otsimiseks
= INDEX (otsinguvahemik, MATCH (1, (kriteerium1 = vahemik1)*(kriteerium2 = vahemik2)*(kriteerium N = vahemikN), 0))
Valem töötab samamoodi nagu ülaltoodud selgitus.
Üritasin oma parima, et seda võimalikult lihtsalt seletada. Aga kui ma ei olnud piisavalt selge, andke mulle sellest teada allpool olevas kommentaaride jaotises. Muide, auto juhtimiseks ei pea te teadma, kuidas mootor töötab. Peate lihtsalt teadma, kuidas sellega sõita. Ja sa tead seda väga hästi.
Kuidas otsida top 5 väärtust topeltväärtustega, kasutades Excelis INDEX-MATCH-i
Kuidas Excelis mitu väärtust otsida
Kuidas VLOOKUP -i kasutada dünaamiliste värvide indeksiga Excelis
Kuidas kasutada VLOOKUPi kahest või enamast Exceli otsingu tabelist
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks
Funktsiooni VLOOKUP kasutamine Excelis
Funktsiooni COUNTIF kasutamine Excelis
Funktsiooni SUMIF kasutamine Excelis