Exceli valem: mitme kriteeriumi otsimine ja ülevõtmine -

Lang L: none (table-of-contents)

Üldine valem

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Kokkuvõte

Mitmekriteeriumilise otsingu teostamiseks ja tulemuste tabelisse üleviimiseks võite kasutada massiivivalemit, mis põhineb INDEXil ja MATCHil. Näidatud näites on valem G5-s järgmine:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Pange tähele, et see valem on massiivvalem ja see tuleb sisestada juhtklahviga + shift + enter.

Selles valemis kasutatakse ka kolme nimega vahemikku: asukoht = B5: B13, summa = D5: D13, kuupäev = C5: C13

Selgitus

Selle valemi tuum on INDEX, mis võtab väärtuse välja nimega vahemikust "summa" (B5: B13):

=INDEX(amount,row_num)

kus real_num töötatakse välja funktsiooni MATCH ja mõne tõeväärtuse loogikaga:

MATCH(1,($F5=location)*(G$4=date),0)

Selles jupis võrreldakse asukohta F5-s kõigi asukohtadega ja G4-s olevat kuupäeva kõigi kuupäevadega. Mõlemal juhul on tulemuseks TRUE ja FALSE väärtuste massiiv. Kui need massiivid korrutatakse kokku, sunnib matemaatika toiming TÕENE ja VÄÄR väärtused ühele ja nullile, nii et MATCH-i minev otsingumassiiv näeb välja selline:

(1;0;0;0;0;0;0;0;0)

MATCH on seadistatud vastama 1 täpse vastena ja tagastab positsiooni rea numbrina INDEX-ile. Number 1 töötab otsingu väärtuse jaoks, kuna massiiv sisaldab nüüd ainult 1-sid ja 0-sid, nagu eespool näidatud.

F5 ja G4 sisestatakse segaviidetena, nii et valemit saab muutmata tabelist kopeerida.