Formula za pretraživanje Excel sa više kriterijuma

Korišćenjem formula formula u Excel-u možemo napraviti formulu za pretraživanje koja koristi više kriterijuma za pronalaženje informacija u bazi podataka ili tablicama podataka.

Formula polja uključuje urezivanje funkcije MATCH unutar funkcije INDEX .

Ovaj tutorijal uključuje korak po korak primer kreiranja formula za pretraživanje koji koristi više kriterijuma za pronalazak dobavljača titanijumskih vidžeta u bazi podataka uzoraka.

Prateći korake u tutorijalnim temama ispod, kreira i koristi formulu koja se vidi na slici iznad.

01 od 09

Unos podataka o tutorijalu

Prvi korak u tutorijalu je unos podataka u Excel radni list .

Da biste pratili korake u tutorijalu, unesite podatke prikazane na slici iznad u sledeće ćelije .

• Unesite najviše opsega podataka u ćelije D1 do F2
• Unesite drugi opseg u ćelije D5 do F11

Redovi 3 i 4 ostaju prazni da bi se prilagodila formulama nizova kreiranih tokom ovog tutorijala.

Ovaj tutorijal ne uključuje oblikovanje prikazano na slici, ali to neće uticati na način na koji formula za pretraživanje funkcioniše.

Informacije o opcijama za oblikovanje slične onima koje smo vidjeli su dostupne u ovom Basic Excel formatting tutorijalu.

02 od 09

Pokretanje INDEX funkcije

Funkcija INDEX je jedna od retkih u programu Excel koja ima više oblika. Funkcija ima obrazac Array i referentnu formu .

Formular Array vraća stvarne podatke iz baze podataka ili tablice podataka, dok Referentni obrazac daje referentu ćelije ili lokaciju podataka u tablici.

U ovom vodiču ćemo koristiti obrazac Array jer želimo da upoznamo ime dobavljača za titanijumske widgetove, a ne referentu ćelije ovom dobavljaču u našoj bazi podataka.

Svaki obrazac ima drugu listu argumenata koji se moraju izabrati prije početka funkcije.

Uputstvo Koraci

1. Kliknite na ćeliju F3 da biste postali aktivna ćelija . Ovde ćemo uneti ugneženu funkciju.
2. Kliknite na karticu Formule u meniju trake .
3. Odaberite traku i referencu sa trake da biste otvorili funkciju padajuće liste.
4. Kliknite na INDEX na listi da biste prikazali okvir za dijalog Select Arguments .
5. U dijaloškom okviru izaberite opciju array, row_num, col_num .
6. Kliknite na dugme OK da biste otvorili dijalog prozora INDEX funkcije.

03 od 09

Unošenje argumenta Array argumenta funkcije INDEX

Prvi argument je argument Array. Ovaj argument određuje raspon ćelija koje treba pretraživati ​​za željene podatke.

Za ovaj tutorijal ovaj argument će biti naša baza podataka uzoraka.

Uputstvo Koraci

1. U dijalogu INDEX funkcije kliknite na liniju Array .
2. Označite ćelije D6 do F11 na radnom listu kako biste ušli u opseg u dijaloški okvir.

04 od 09

Započinjanje funkcije ugrađene MATCH

Kada gnežite jednu funkciju u drugu, nije moguće otvoriti dijalog prozora druge ili ugnežene funkcije da unesete neophodne argumente .

Ugnežena funkcija mora biti uneta kao jedan od argumenata prve funkcije.

U ovom tutorijalu, ugnežena MATCH funkcija i njegovi argumenti će se uneti u drugu liniju dijaloga INDEX funkcije - linija Row_num .

Važno je napomenuti da prilikom unošenja funkcija ručno, argumenti funkcije su odvojeni jedni od drugih pomoću zareza "," .

Unošenje argumenta Lookup_value argumenta MATCH funkcije

Prvi korak u unosu ugnežene MATCH funkcije je da unesete argument Lookup_value .

Lookup_value će biti referenca lokacije ili ćelije za izraz za pretraživanje koji želimo da se podudara u bazi podataka.

Normalno, Lookup_value prihvata samo jedan kriterijum pretraživanja ili termin. Da bi potražili više kriterijuma, moramo produžiti Lookup_value .

Ovo se radi povezivanjem ili povezivanjem dve ili više referenci ćelija pomoću ampersand simbola " & ".

Uputstvo Koraci

1. U dijalogu INDEX funkcije kliknite na liniju Row_num .
2. Ukucajte poklapanje imena imena, a zatim otvoreni okrugli držač " ( "
3. Kliknite na ćeliju D3 da biste uneli referencu ćelije u dijaloški okvir.
4. Ukucajte ampersand " & " nakon ćelije D3 da biste dodali drugu referencu ćelije.
5. Kliknite na ćeliju E3 da biste uneli ovu drugu referencu ćelije u dijalog box.
6. Otkucajte zarez "," nakon reference ćelije E3 da dovršite unos funkcije Lookup_value MATCH funkcije.
7. Ostavite okvir za dijalog INDEX otvoren za sledeći korak u tutorijalu.

U poslednjem koraku tutoriala Lookup_values ​​će se uneti u ćelije D3 i E3 radnog lista.

05 od 09

Dodavanje Lookup_array funkcije MATCH

Ovaj korak pokriva dodavanje argumenta Lookup_array za ugneženu MATCH funkciju.

Lookup_array je opseg ćelija koje će MATCH funkcija pretraživati ​​da pronađe argument Lookup_value dodan u prethodnom koraku tutoriala.

Pošto smo identifikovali dva polja za pretragu u argumentu Lookup_array , moramo isto uraditi i za Lookup_array . Funkcija MATCH traži samo jedan niz za svaki navedeni izraz.

Da unosimo više polja, ponovo koristimo ampersand " & " za povezivanje nizova zajedno.

Uputstvo Koraci

Ovi koraci se unose nakon unosa zareze u prethodnom koraku na liniji Row_num u dijalogu INDEX funkcije.

1. Kliknite na liniju Row_num nakon zareza da biste postavili tačku ubacivanja na kraju tekućeg unosa.
2. Označite ćelije D6 do D11 na radnom listu da biste ušli u opseg. Ovo je prvi niz koji je funkcija za pretragu.
3. Unesite ampersand i " & " nakon ćelijske reference D6: D11 jer želimo da funkcija pretraži dva nizova.
4. Označite ćelije E6 do E11 na radnom listu da biste ušli u opseg. Ovo je drugi niz koji je funkcija za pretraživanje.
5. Upišite oznaku "," nakon reference ćelije E3 da završi unos funkcije Lookup_array MATCH funkcije.
6. Ostavite okvir za dijalog INDEX otvoren za sledeći korak u tutorijalu.

06 od 09

Dodavanje tipa utakmice i dovršavanje funkcije MATCH

Treći i završni argument funkcije MATCH je Match_type argument.

Ovaj argument govori Excelu kako da odgovori Lookup_value s vrijednostima u Lookup_array-u. Izbor je: 1, 0 ili -1.

Ovaj argument je neobavezan. Ako je izostavljeno, funkcija koristi podrazumevanu vrednost od 1.

• ako je Match_type = 1 ili je ispušten: MATCH pronalazi najveću vrijednost koja je manja ili jednaka Lookup_value. Podaci Lookup_array moraju biti sortirani u rastućem redosledu.
• ako match_type = 0: MATCH pronalazi prvu vrijednost koja je tačno jednaka Lookup_value. Podaci Lookup_array mogu biti sortirani po bilo kom redosledu.
• ako Match_type = -1: MATCH pronađe najmanju vrijednost koja je veća ili jednaka Lookup_value. Podaci Lookup_array moraju biti sortirani u opadajućem redosledu.

Uputstvo Koraci

Ovi koraci se unose nakon unosa zareze u prethodnom koraku na liniji Row_num u dijalogu INDEX funkcije.

1. Nakon zareza na liniji Row_num , unesite nulu " 0 " jer želimo da ugnežena funkcija vrati tačne poklapanje sa terminima koji unosimo u ćelije D3 i E3.
2. Ukucajte držač za zatvaranje " ) " da biste dovršili funkciju MATCH.
3. Ostavite okvir za dijalog INDEX otvoren za sledeći korak u tutorijalu.

07 od 09

Nazad na funkciju INDEX

Sada kada je završena funkcija MATCH, prelazimo na treću liniju otvorenog dijaloga i ukucati poslednji argument za INDEX funkciju.

Ovaj treći i poslednji argument je argument Column_num koji ukazuje Excelu broj kolone u opsegu D6 do F11 gdje će pronaći informacije koje želimo vratiti funkcijom. U ovom slučaju, dobavljač za titanijumske widgete .

Uputstvo Koraci

1. Kliknite na liniju Column_num u dijaloškom okviru.
2. Unesite broj tri " 3 " (bez citata) na ovoj liniji, jer mi tražimo podatke u trećoj koloni od opsega D6 do F11.
3. Ne kliknite OK ili zatvorite okvir za dijalog INDEX. Ona mora ostati otvorena za sledeći korak u tutorijalu - kreiranje formula formula .

08 od 09

Kreiranje Formule Array

Prije zatvaranja dijaloga potrebno je pretvoriti ugneženu funkciju u niz formula .

Formula nizova omogućava ono što traži više termina u tabeli podataka. U ovom vodiču želimo da podudaramo sa dva pojma: Widget iz kolone 1 i titana iz kolone 2.

Kreiranje niza formula u Excelu se vrši pritiskom tastera CTRL , SHIFT i ENTER na tastaturi istovremeno.

Efekat pritiskanja ovih tastera zajedno je da okružite funkciju skrivenim oklopima: {} pokazujući da je sada formula formula.

Uputstvo Koraci

1. Sa završenim dijalogom koji se i dalje otvara sa prethodnog koraka ovog tutorijala, pritisnite i zadržite tastere CTRL i SHIFT na tastaturi, a zatim pritisnite i pustite taster ENTER .
2. Ako se ispravno izvrši, dijalog se zatvori i # N / A greška će se pojaviti u ćeliji F3 - ćeliji u koju smo uneli funkciju.
3. # N / A greška se pojavljuje u ćeliji F3 jer su ćelije D3 i E3 prazne. D3 i E3 su ćelije u kojima smo rekli funkciji da pronađemo Lookup_values ​​u koraku 5 tutoriala. Kada se podaci dodaju u ove dve ćelije, greška će biti zamenjena informacijama iz baze podataka .

09 od 09

Dodavanje Kriterij pretrage

Poslednji korak u tutorijalu je dodavanje pojmova za pretragu na naš radni list.

Kao što je pomenuto u prethodnom koraku, tražimo da se podudaraju sa terminima Widgets iz kolone 1 i Titanium iz kolone 2.

Ako i samo ako naša formula pronađe podudaranje za oba termina u odgovarajućim stupcima u bazi podataka, da li će vraćati vrednost iz treće kolone.

Uputstvo Koraci

1. Kliknite na ćeliju D3.
2. Ukucajte Widget i pritisnite taster Enter na tastaturi.
3. Kliknite na ćeliju E3.
4. Ukucajte titan i pritisnite taster Enter na tastaturi.
5. Dobavljač name Widgets Inc. treba da se pojavi u ćeliji F3 - lokaciju funkcije, jer je jedini dobavitelj naveden koji prodaje Titanium Widgets.
6. Kada kliknete na ćeliju F3 kompletnu funkciju
   {= INDEX (D6: F11, MATCH (D3 i E3, D6: D11 i E6: E11, 0), 3)}
   pojavljuje se u traci formule iznad radnog lista .

Napomena: U našem primeru postoji samo jedan dobavljač za titanijumske widgete. Ako postoji više od jednog dobavljača, dobavljač koji je prvi naveden u bazi podataka vraća funkcija.