Lengva ieškoti vertės naudojant vieną unikalų raktą lentelėje. Mes galime tiesiog naudoti funkciją VLOOKUP. Bet kai neturite to unikalaus stulpelio savo duomenyse ir reikia ieškoti kelių stulpelių, kad jie atitiktų vertę, VLOOKUP nepadeda.
Taigi, norėdami ieškoti vertės lentelėje su keliais kriterijais, naudosime formulę INDEX-MATCH-INDEX.
Bendra daugelio kriterijų paieškos formulė
= INDEX (lookup_range, MATCH (1, INDEX ((kriterijus1 = diapazonas1)*(kriterijus2 = diapazonas2)*(kriterijusN = diapazonasN), 0,1), 0))
lookup_range: Tai diapazonas, iš kurio norite gauti vertę.
1 kriterijus, 2 kriterijus, N kriterijus: Tai yra kriterijai, kuriuos norite atitikti diapazone1, diapazone2 ir diapazone N. Galite turėti iki 270 kriterijų - diapazono porų.
Diapazonas1, diapazonas2, diapazonasN: Tai yra diapazonai, kuriuose atitiksite atitinkamus kriterijus.
Kaip tai veiks? Pažiūrėkime…
INDEX ir MATCH su kelių kriterijų pavyzdžiu
Čia aš turiu duomenų lentelę. Noriu ištraukti kliento vardą, naudojant užsakymo datą, statybininką ir sritį. Taigi čia aš turiu tris kriterijus ir vieną paieškos diapazoną.
Parašykite šią formulę I4 langelyje paspauskite „Enter“.
= INDEKSAS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Kaip tai veikia:
Mes jau žinome, kaip EXCEL veikia INDEX ir MATCH funkcijos, todėl čia neketinu to paaiškinti. Mes kalbėsime apie čia naudotą triuką.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Pagrindinė dalis yra tokia. Kiekviena šio teiginio dalis pateikia masyvą klaidingos klaidos.
Kai loginės vertės dauginamos, jos grąžina masyvą 0 ir 1. Daugyba veikia kaip AND operatorius. Sunkus, kai visos vertės yra teisingos, tik tada grąžina 1 kitą 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Visa tai grįš
{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; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Kuris bus išverstas į
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEKSAS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX grąžins tą patį masyvą ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) į MATCH funkciją kaip paieškos masyvas.
Rungtynės(1,INDEKSAS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija MATCH ieškos 1 masyve {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ir grąžins masyvo pirmojo 1 indekso numerį. Kas čia 8.
INDEKSAS(E2: E16,Rungtynės(1,INDEKSAS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Galiausiai, INDEX grąžins vertę iš nurodyto diapazono (E2: E16) indeksas (8).
Paprasta ????. Atsiprašome, paprasčiau to padaryti nepavyko.
Masyvo sprendimas
Jei galite paspausti CTRL + SHIFT + ENTER, galite pašalinti vidinę INDEX funkciją. Tiesiog parašykite šią formulę ir paspauskite CTRL + SHIFT + ENTER.
= INDEKSAS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Bendra masyvo formulė kelių kriterijų paieškai
= INDEX (lookup_range, MATCH (1, (kriterijus1 = diapazonas1)*(kriterijus2 = diapazonas2)*(kriterijusN = diapazonasN), 0))
Formulė veikia taip pat, kaip aprašyta aukščiau.
Aš stengiausi kuo paprasčiau paaiškinti. Bet jei man nebuvo pakankamai aišku, praneškite man apie tai žemiau esančiame komentarų skyriuje. Beje, norint vairuoti automobilį nereikia žinoti, kaip veikia variklis. Jums tiesiog reikia žinoti, kaip jį vairuoti. Ir jūs tai puikiai žinote.
Kaip ieškoti populiariausių 5 verčių naudojant pasikartojančias vertes naudojant „INDEX-MATCH“ programoje „Excel“
Kaip VLOOKUP kelias reikšmes „Excel“
Kaip VLOOKUP naudojant dinaminį kolonų indeksą „Excel“
Kaip naudoti „VLOOKUP“ iš dviejų ar daugiau „Excel“ peržiūros lentelių
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti
Kaip naudotis „VLOOKUP“ funkcija „Excel“
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“
Kaip naudotis „SUMIF“ funkcija „Excel“