Tabulā ir viegli meklēt vērtību, izmantojot vienu unikālu atslēgu. Mēs varam vienkārši izmantot funkciju VLOOKUP. Bet, ja jūsu datos nav šīs unikālās slejas un jums ir jāmeklē vairākas kolonnas, lai tās atbilstu vērtībai, VLOOKUP nepalīdz.
Tātad, lai atrastu vērtību tabulā ar vairākiem kritērijiem, mēs izmantosim formulu INDEX-MATCH-INDEX.
Vispārīga formula vairāku kritēriju meklēšanai
= INDEKSS (uzmeklēšanas_diapazons, MATCH (1, INDEKSS ((kritērijs1 = diapazons1)*(kritērijs2 = diapazons2)*(kritērijsN = diapazonsN), 0,1), 0))
lookup_range: Tas ir diapazons, no kura vēlaties iegūt vērtību.
1. kritērijs, 2. kritērijs, N kritērijs: Šie ir kritēriji, kuriem vēlaties atbilst diapazonā1, diapazonā2 un diapazonā N. Jums var būt līdz 270 kritērijiem - diapazona pāriem.
Diapazons1, diapazons2, diapazonsN: Šie ir diapazoni, kuros jūs atbilstat saviem attiecīgajiem kritērijiem.
Kā tas darbosies? Paskatīsimies…
INDEX un MATCH ar vairāku kritēriju piemēru
Šeit man ir datu tabula. Es vēlos izvilkt klienta vārdu, izmantojot rezervācijas datumu, celtnieku un apgabalu. Tātad šeit man ir trīs kritēriji un viens meklēšanas diapazons.
Ierakstiet šo formulu šūnā I4, nospiediet taustiņu Enter.
= INDEKSS (E2: E16, MATCH (1, INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Kā tas strādā:
Mēs jau zinām, kā EXCEL darbojas INDEX un MATCH funkcija, tāpēc es to šeit neskaidrošu. Mēs šeit runāsim par triku, ko izmantojām.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Galvenā daļa ir šī. Katra šī paziņojuma daļa atgriež patiesu nepatiesu masīvu.
Reizinot Būla vērtības, tās atgriež masīvu 0 un 1. Reizināšana darbojas kā UN operators. Smagi, ja visas vērtības ir patiesas, tikai tad atgriež 1 citu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Tas viss atgriezīsies
{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}
Kas tiks tulkots
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEKSS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX atgriezīs to pašu masīvu ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) līdz MATCH funkcijai kā uzmeklēšanas masīvs.
MATCH(1,INDEKSS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH funkcija meklēs 1 masīvā {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Un atgriezīs masīvā atrasto pirmo 1 indeksa numuru. Kas šeit ir 8.
INDEKSS(E2: E16,MATCH(1,INDEKSS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Visbeidzot, INDEX atgriež vērtību no noteiktā diapazona (E2: E16) indekss (8).
Vienkārši ????. Atvainojiet, nevarētu padarīt vienkāršāku.
Masīva risinājums
Ja jūs attiecīgi varat nospiest taustiņu kombināciju CTRL + SHIFT + ENTER, varat izslēgt iekšējo INDEX funkciju. Vienkārši uzrakstiet šo formulu un nospiediet CTRL + SHIFT + ENTER.
= INDEKSS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Vispārīga masīva formula vairāku kritēriju meklēšanai
= INDEKSS (uzmeklēšanas_diapazons, MATCH (1, (kritērijs1 = diapazons1)*(kritērijs2 = diapazons2)*(kritērijsN = diapazonsN), 0))
Formula darbojas tāpat kā iepriekš.
Es centos visu iespējamo izskaidrot pēc iespējas vienkāršāk. Bet, ja man tas nebija pietiekami skaidrs, dariet man to zināmu zemāk esošajā komentāru sadaļā. Starp citu, jums nav jāzina, kā darbojas motors, lai vadītu automašīnu. Jums vienkārši jāzina, kā to vadīt. Un jūs to ļoti labi zināt.
Kā meklēt piecas vērtības ar dublētām vērtībām, izmantojot programmu INDEX-MATCH programmā Excel
Kā VLOOKUP vairākas vērtības programmā Excel
Kā VLOOKUP ar dinamisko kolu indeksu programmā Excel
Kā lietot VLOOKUP no divām vai vairākām uzmeklēšanas tabulām programmā Excel
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti
Kā lietot funkciju VLOOKUP programmā Excel
Kā programmā Excel izmantot funkciju COUNTIF
Kā lietot funkciju SUMIF programmā Excel