Üldine valem
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Kokkuvõte
INDEXi ja MATCH-iga väärtuste otsimiseks mitme kriteeriumi abil saate kasutada massiivivalemit. Näidatud näites on H8 valem:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Selgitus
See on täpsem valem. Põhitõdede saamiseks vaadake jaotist INDEKS ja MATCH.
Tavaliselt on INDEX MATCH valem konfigureeritud koos MATCH komplektiga, et vaadata läbi ühe veeru vahemik ja pakkuda vastet etteantud kriteeriumide alusel. Ilma väärtuste liitmiseta abistaja veerus või valemis endas pole võimalust esitada rohkem kui üks kriteerium.
See valem töötab selle piirangu ümber, kasutades boolean loogikat, et luua massiiv üksusi ja nulle, et tähistada ridu, mis vastavad kõigile 3 kriteeriumile, ja seejärel kasutada MATCH, et see vastaks esimesele leitud 1-le. Selle fragmentiga genereeritakse ajutine massiivid üks ja null.
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Siin võrreldakse üksust H5 kõigi üksustega, suurust H6-s kõigi suurustega ja värvi H7-s kõigi värvidega. Esialgne tulemus on kolm sellist TRUE / FALSE massiivi:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nõuanne: nende tulemuste nägemiseks kasutage klahvi F9. Valige lihtsalt valemiribalt avaldis ja vajutage F9.
Matemaatikaoperatsioon (korrutamine) muudab TRUE FALSE väärtused väärtuseks 1s ja 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Pärast korrutamist on meil üks selline massiiv:
(0;0;1;0;0;0;0)
mis sisestatakse funktsiooni MATCH otsingu massiivina otsingu väärtusega 1:
MATCH(1,(0;0;1;0;0;0;0))
Siinkohal on valemiks INDEX MATCH standardvalem. Funktsioon MATCH tagastab 3 väärtusele INDEX:
=INDEX(E5:E11,3)
ja INDEX tagastab lõpptulemuseks 17,00 dollarit.
Massiivi visualiseerimine
Eespool selgitatud massiive võib olla keeruline visualiseerida. Allolev pilt näitab põhiideed. Veerud B, C ja D vastavad näite andmetele. Veerg F luuakse, korrutades kolm veergu kokku. See on MATCHile üle antud massiiv.
Massiivivaba versioon
Sellele valemile on võimalik lisada veel üks INDEX, vältides vajadust sisestada massiivivalemina koos juhtimisega + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funktsioon INDEX saab massiive käsitleda loomulikult, nii et teine INDEX lisatakse ainult tõeväärtusega loogikaoperatsiooniga loodud massiivi "püüdmiseks" ja sama massiivi uuesti MATCH-i tagastamiseks. Selleks on INDEX konfigureeritud null rea ja ühe veeruga. Nullrea trikk paneb INDEXi massiivist tagastama veeru 1 (mis on niikuinii juba üks veerg).
Miks soovite mitte-massiivi versiooni? Mõnikord unustavad inimesed sisestada massiivi valemi juhtklahviga + tõstuklahv + sisestusklahv ja valem annab vale tulemuse. Niisiis, mittemassiivne valem on "kuulikindlam". Kompromiss on siiski keerulisem valem.
Märkus. Excel 365-s pole massiivivalemeid vaja erilisel viisil sisestada.
