Formula za iskanje Excel z več kriteriji

Z uporabo formule matrike v Excelu lahko ustvarimo formulo za iskanje, ki uporablja več kriterijev za iskanje podatkov v podatkovni zbirki ali tabeli podatkov.

Formula matrike vključuje gnezdenje funkcije MATCH znotraj funkcije INDEX .

Ta vadnica vsebuje korak za korakom pripravo iskalne formule, ki uporablja več kriterijev za iskanje dobavitelja dodatkov titana v vzorčni bazi podatkov.

Po korakih v spodnjih tutorijalnih temah vas poišče ustvarjanje in uporaba formule, prikazane na zgornji sliki.

01 od 09

Vnos podatkov o vadnicah

Prvi korak v vadbi je vnos podatkov v delovni list Excel.

Če želite slediti korakom v vadnici, vnesite podatke, prikazane na zgornji sliki, v naslednje celice .

• Vnesite najvišjo paleto podatkov v celice D1 do F2
• V drugem obsegu vnesite celice D5 do F11

Vrstice 3 in 4 ostanejo prazna, da se prilagodi formulo matrike, ustvarjena v tem vadnici.

Vadnica ne vključuje oblikovanja, ki je vidna na sliki, vendar to ne bo vplivalo na delovanje formule za iskanje.

Informacije o možnostih oblikovanja, podobne tistim, ki so prikazane zgoraj, so na voljo v tem priročniku Tutorial za oblikovanje v Excelu.

02 od 09

Zagon funkcije INDEX

Funkcija INDEX je ena od redkih v Excelu, ki ima več oblik. Funkcija ima obrazec Array in referenčno obliko .

Obrazec Array vrne dejanske podatke iz podatkovne baze ali tabele podatkov, medtem ko vam referenčna obrazec poda referenco celice ali lokacijo podatkov v tabeli.

V tem tutorialu bomo uporabili obrazec Array, saj želimo vedeti ime dobavitelja za pripomočke titana namesto sklic celice tega dobavitelja v naši podatkovni bazi.

Vsak obrazec ima drugačen seznam argumentov, ki jih je treba izbrati pred začetkom funkcije.

Vadbeni koraki

1. Kliknite na celico F3, da postane aktivna celica . Tukaj bomo vnesli ugnezdeno funkcijo.
2. Kliknite jeziček Formule v meniju traku .
3. Izberite trak in referenco iz traku, da odprete spustni seznam funkcij.
4. Na seznamu kliknite INDEX, da odprete pogovorno okno Select Arguments .
5. V pogovornem oknu izberite možnost array, row_num, col_num .
6. Kliknite OK, da odprete pogovorno okno funkcije INDEX.

03 od 09

Vnos indikacije Array funkcije INDEX

Prvi argument je argument Array. Ta argument določa obseg celic, ki jih želite iskati za želene podatke.

Za ta vadnica bo ta argument naša vzorčna baza podatkov .

Vadbeni koraki

1. V pogovornem oknu funkcije INDEX kliknite vrstico Array .
2. Označite celice D6 do F11 na delovnem listu, da vnesete obseg v pogovorno okno.

04 od 09

Začetek funkcije gesla MATCH

Če se ena funkcija znotraj gnezdi v drugo, ni mogoče odpreti pogovornega okna druge ali ugnezdene funkcije, da vnesete potrebne argumente .

Vgnezdena funkcija mora biti vnesena kot eden od argumentov prve funkcije.

V tej vaja se ugnezdena funkcija MATCH in njegovi argumenti vnesejo v drugo vrstico pogovornega okna funkcije INDEX - vrstica Row_num .

Pomembno je omeniti, da pri vnosu funkcij ročno, so argumenti funkcije ločeni drug od drugega z vejico "," .

Vnos argumenta Lookup_value argumenta MATCH

Prvi korak pri vnosu ugnežene funkcije MATCH je vnos argumenta Lookup_value .

Lookup_value bo referenca lokacije ali celice za iskalni izraz, ki ga želimo ujemati v podatkovni bazi.

Običajno Lookup_value sprejema samo eno iskalno merilo ali izraz. Če želite poiskati več kriterijev, moramo razširiti Lookup_value .

To se naredi s povezovanjem ali povezovanjem dveh ali več celičnih referenc skupaj z znakom ampersand " & ".

Vadbeni koraki

1. V pogovornem oknu funkcije INDEX kliknite vrstico Row_num .
2. Vnesite ujemanje imena funkcij, ki mu sledi odprt okrogel nosilec " ( "
3. Kliknite celico D3, da v pogovorno okno vnesete referenco celice.
4. Vnesite ampersand " & " za referenčno številko celice D3 , da dodate drugo referenco celice.
5. Kliknite na celico E3, da vnesete to drugo referenco celic v pogovorno okno.
6. Vnesite vejico "," po sklicu celice E3 za dokončanje vnosa argumenta Lookup_value funkcije MATCH.
7. Odprite pogovorno okno funkcije INDEX za naslednji korak v vadbi.

V zadnjem koraku vadnice se Lookup_values ​​vnesejo v celice D3 in E3 v delovnem listu.

05 od 09

Dodajanje Lookup_array za funkcijo MATCH

Ta korak pokriva dodajanje argumenta Lookup_array za ugnezdeno funkcijo MATCH.

Lookup_array je obseg celic, ki jih bo funkcija MATCH poiskala, da bi našli argument Lookup_value , dodan v prejšnjem koraku vadnice.

Ker smo v argumentu Lookup_array identificirali dve iskalni polji, moramo storiti enako za Lookup_array . Funkcija MATCH išče samo eno matriko za vsak naveden izraz.

Če želite vnesti več nizov, ponovno uporabimo ampersand " & ", da združimo matrike skupaj.

Vadbeni koraki

Te korake je treba vnesti po vejici v prejšnjem koraku na vrstici Row_num v pogovornem oknu funkcije INDEX.

1. Kliknite vrstico Row_num za vejico, da vstavite točko vstavljanja na konec trenutnega vnosa.
2. Označite celice D6 do D11 na delovnem listu, da vstopite v obseg. To je prva matrika, ki jo funkcija išče.
3. Vnesite ampersand " & " po referenci celic D6: D11, ker želimo, da funkcija poišče dve nizi.
4. Označite celice E6 do E11 na delovnem listu, da vstopite v obseg. To je druga vrsta, ki jo funkcija išče.
5. Vnesite vejico "," po sklicu celice E3 za dokončanje vnosa argumenta Lookup_array funkcije MATCH.
6. Odprite pogovorno okno funkcije INDEX za naslednji korak v vadbi.

06 od 09

Dodajanje vrste ujemanja in zaključek funkcije MATCH

Tretji in zadnji argument funkcije MATCH je argument Match_type.

Ta argument pove Excelu, kako naj ustreza Lookup_value z vrednostmi v Lookup_array. Izbira je: 1, 0 ali -1.

Ta argument je neobvezen. Če je izpuščena, funkcija uporabi privzeto vrednost 1.

• če je Match_type = 1 ali je izpuščen: MATCH najde največjo vrednost, ki je manjša ali enaka Lookup_value. Podatke Lookup_array je treba razvrščati po naraščajočem vrstnem redu.
• če match_type = 0: MATCH najde prvo vrednost, ki je točno enaka Lookup_value. Podatke Lookup_array lahko razvrstite v poljubnem vrstnem redu.
• če Match_type = -1: MATCH najde najmanjšo vrednost, ki je večja ali enaka vrednosti Lookup_value. Podatke Lookup_array je treba razvrščati po padajočem vrstnem redu.

Vadbeni koraki

Te korake je treba vnesti po vejici v prejšnjem koraku na vrstici Row_num v pogovornem oknu funkcije INDEX.

1. Po vejici v vrstici Row_num vnesite nič " 0 ", ker želimo, da ugnezdena funkcija vrne natančno ujemanje s pogoji, ki jih vnesemo v celice D3 in E3.
2. Vnesite zaključni oklepni nosilec » ) «, da dokončate funkcijo MATCH.
3. Odprite pogovorno okno funkcije INDEX za naslednji korak v vadbi.

07 od 09

Nazaj na funkcijo INDEX

Zdaj, ko je funkcija MATCH končana, se premaknemo na tretjo vrstico odprtega pogovornega okna in vnesemo zadnji argument za funkcijo INDEX.

Ta tretji in zadnji argument je argument Column_num , ki Excelu pove stolpno številko v območju D6 do F11, kjer bo našel informacije, ki jih želimo vrniti s funkcijo. V tem primeru je dobavitelj za pripomočke titana .

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico Column_num .
2. Na to vrstico vnesite številko tri " 3 " (brez narekovajev), ker iščemo podatke v tretjem stolpcu območja D6 do F11.
3. Ne kliknite V redu ali zaprite pogovorno okno funkcije INDEX. Ostati mora odprta za naslednji korak v vadnici - ustvarjanje matrike .

08 od 09

Ustvarjanje formule matrike

Pred zaprtjem pogovornega okna moramo svojo ugnezdeno funkcijo spremeniti v matrično formulo .

Formula matrike omogoča, da v tabeli podatkov išče več izrazov. V tem tutorialu želimo ujemati dva izraza: dodatki iz stolpca 1 in titana iz stolpca 2.

Ustvarjanje matrične formule v Excelu poteka s pritiskom na tipke CTRL , SHIFT in ENTER na tipkovnici istočasno.

Učinek pritiska teh tipk je, da obkrožite funkcijo z zavihanimi oporniki: {}, ki označuje, da je zdaj formula matrike.

Vadbeni koraki

1. Z dokončanim pogovornim oknom, ki se še vedno odprejo iz prejšnjega koraka te vadnice, pridržite tipke CTRL in SHIFT na tipkovnici, nato pritisnite in spustite tipko ENTER .
2. Če je pravilno storjeno, se bo pogovorno okno zaprlo in v celici F3 se bo pojavila napaka # N / A - celica, v katero smo vnesli funkcijo.
3. Napaka # N / A se pojavi v celici F3, ker so celice D3 in E3 prazne. D3 in E3 sta celice, kjer smo funkciji poiskali iskanje Lookup_values ​​v 5. koraku vadnice. Ko se podatki v teh dveh celicah doda, se napaka nadomesti z informacijami iz baze podatkov .

09 od 09

Dodajanje kriterijev iskanja

Zadnji korak v vadnici je dodati iskalne izraze na naš delovni list.

Kot smo omenili v prejšnjem koraku, iščemo, da se ujemajo izrazi Widgets iz stolpca 1 in Titana iz stolpca 2.

Če in le če naša formula v ustreznih stolpcih baze podatkov najde zadetek za oba izraza, bo vrednost vrnila iz tretjega stolpca.

Vadbeni koraki

1. Kliknite na celico D3.
2. Vpišite pripomočke in pritisnite tipko Enter na tipkovnici.
3. Kliknite na celico E3.
4. Vnesite Titanium in pritisnite tipko Enter na tipkovnici.
5. Dobaviteljevo ime Widgets Inc naj se prikaže v celici F3 - lokacijo funkcije, ker je edini dobavitelj naveden, kdo prodaja dodatke Titanium.
6. Ko kliknete celico F3, celotno funkcijo
   {= INDEX (D6: F11, MATCH (D3 in E3, D6: D11 in E6: E11, 0), 3)}
   se prikaže v vrstici s formulo nad delovnim listom .

Opomba: V našem primeru je bil samo en dobavitelj za pripomočke titana. Če je imela več kot en dobavitelj, se s temi funkcijami vrne dobavitelja, ki je prvi naveden v podatkovni zbirki.