Pomoću formule polja u programu Excel možemo stvoriti formulu za pretraživanje koja koristi više kriterija za pronalaženje informacija u bazi podataka ili tablici podataka.
Formula polja uključuje guranje funkcije MATCH unutar funkcije INDEX .
Ovaj vodič sadrži korak po korak primjer stvaranja formule za pretraživanje koja koristi više kriterija za pronalaženje dobavljača widgova titana u uzorku baze podataka.
Slijedeći korake u nastavnim temama u nastavku vas vodi kroz stvaranje i korištenje formule vidio na gornjoj slici.
01 od 09
Unos podataka o vodiču
Prvi korak u vodiču je unos podataka u radni list programa Excel .
Da biste slijedili korake u vodiču, unesite podatke prikazane na gornjoj slici u sljedeće ćelije .
- Unesite najviši raspon podataka u ćelije D1 do F2
- Unesite drugi raspon u ćelije D5 do F11
Redci 3 i 4 ostaju prazni kako bi se smjestila formula polja stvorena tijekom ovog vodiča.
Vodič ne uključuje oblikovanje na slici, ali to neće utjecati na funkcioniranje formule za pretraživanje.
Informacije o opcijama formatiranja slične onima koje su gore vidljive dostupne su u ovom priručniku za Osmišljeno oblikovanje formata Excel.
02 od 09
Pokretanje funkcije INDEX
Funkcija INDEX jedna je od rijetkih u Excelu koja ima više oblika. Funkcija ima oblik obrasca i referentni obrazac .
Oblik obrasca vraća stvarne podatke iz baze podataka ili tablice podataka, dok Referentni obrazac daje referencu ili lokaciju ćelije podataka u tablici.
U ovom udžbeniku upotrijebit ćemo obrazac obrasca budući da želimo znati ime dobavljača za widgete titana, a ne kao referencu ovog dobavljača u našoj bazi podataka.
Svaki oblik ima drugačiji popis argumenata koji se moraju odabrati prije početka funkcije.
Koraci
- Kliknite na ćeliju F3 da biste ga učinili aktivnom ćelijom . Ovdje ćemo upisati ugniježđenu funkciju.
- Kliknite karticu formule na traci izbornika.
- Odaberite traku i referencu iz vrpce da biste otvorili padajući popis funkcije.
- Kliknite na INDEX na popisu da biste otvorili dijaloški okvir Select Arguments .
- Odaberite dijaloški okvir array, row_num, col_num .
- Kliknite OK (U redu) da biste otvorili dijaloški okvir INDEX.
03 od 09
Unosom indeksa funkcije funkcije INDEX
Prvi argument potreban je argument Array. Ovaj argument određuje raspon ćelija za traženje željenih podataka.
Za ovaj vodič ovaj argument bit će naša baza podataka o uzorku.
Koraci
- U dijaloškom okviru funkcije INDEX kliknite na redak polja .
- Istaknite ćelije D6 do F11 u radnom listu kako biste unijeli raspon u dijaloški okvir.
04 od 09
Pokretanje Nested MATCH funkcije
Prilikom gniježđenja jedne funkcije unutar drugog, nije moguće otvoriti dijaloški okvir druge ili ugniježđene funkcije za unos potrebnih argumenata .
Ugniježđena funkcija mora biti upisana kao jedan od argumenata prve funkcije.
U ovom udžbeniku ugrađena funkcija MATCH i njegovi argumenti bit će uneseni u drugu liniju dijaloškog okvira funkcije INDEX - redak Row_num .
Važno je napomenuti da ručno unosom funkcija argumenti funkcije međusobno se odvajaju zarezom "," .
Unos argumenata Lookup_value funkcije MATCH
Prvi korak u unosu u ugrađenu funkciju MATCH jest unijeti argument Lookup_value .
Vrijednost " Lookup_value" bit će lokacija ili referentna stanica za pojam za pretraživanje koji želimo podudarati u bazi podataka.
Uobičajeno, Lookup_value prihvaća samo jedan kriterij ili pojam za pretraživanje. Da bismo tražili više kriterija, moramo proširiti vrijednost Lookup_value .
To se postiže spajanjem ili povezivanjem dviju ili više referentnih stanica zajedno pomoću simbola " & ".
Koraci
- U dijaloškom okviru INDEX funkcije kliknite na redak Row_num .
- Upišite podudarnost imena funkcija i slijedi otvoreni okrugli nosač " ( "
- Kliknite na ćeliju D3 da biste unijeli referencu ćelije u dijaloški okvir.
- Upišite oznaku " & " nakon referentne ćelije D3 da biste dodali drugu referencu ćelije.
- Kliknite na ćeliju E3 da biste unijeli drugu ćeliju u dijaloški okvir.
- Upišite zarez "," nakon referentne ćelije E3 da biste dovršili unos argumenta Lookup_value funkcije MATCH.
- Ostavite dijaloški okvir INDEX funkcije otvoren za sljedeći korak u vodiču.
U posljednjem koraku udžbenika Lookup_values će se unijeti u ćelije D3 i E3 radnog lista.
05 od 09
Dodavanje Lookup_array za MATCH funkciju
Ovaj korak obuhvaća dodavanje argumenata Lookup_array za ugniježđenu funkciju MATCH.
Lookup_array je raspon ćelija koje će funkcija MATCH pretražiti kako bi pronašla argument Lookup_value dodan u prethodnom koraku udžbenika.
Budući da smo identificirali dva polja za pretraživanje u argumentu Lookup_array , moramo učiniti isto za Lookup_array . Funkcija MATCH pretražuje samo jedan polje za svaki navedeni termin.
Da biste unijeli više polja, ponovno koristimo znakove " & " kako bismo zajedno spojili polja.
Koraci
Ove se korake unose nakon zareza unesenog u prethodnom koraku na retku Row_num u dijaloškom okviru INDEX.
- Kliknite zarez Row_num nakon zarezom kako biste stavili točku umetanja na kraj trenutnog unosa.
- Istaknite ćelije D6 do D11 u radnom listu da biste unijeli raspon. Ovo je prvi niz funkcije za pretraživanje.
- Upišite oznaku " & " nakon referenci za stanice D6: D11 jer želimo da funkcija pretražuje dva polja.
- Istaknite ćelije E6 do E11 u radnom listu kako biste ušli u raspon. Ovo je drugi niz funkcije za pretraživanje.
- Unesite zarez "," nakon referentne ćelije E3 da biste dovršili unos matrice Lookup_array funkcije MATCH.
- Ostavite dijaloški okvir INDEX funkcije otvoren za sljedeći korak u vodiču.
06 od 09
Dodavanje vrste podudaranja i Dovršavanje funkcije MATCH
Treći i konačni argument funkcije MATCH je argument Match_type.
Ovaj argument kaže Excelu kako uskladiti vrijednost Lookup_value s vrijednostima u Lookup_array. Mogućnosti su: 1, 0 ili -1.
Ovaj argument nije obavezan. Ako je izostavljena, funkcija koristi zadanu vrijednost od 1.
- ako je Match_type = 1 ili izostavljen: MATCH pronalazi najveću vrijednost koja je manja ili jednaka vrijednosti Lookup_value. Podaci Lookup_array moraju biti razvrstani po uzlaznoj poruci.
- ako match_type = 0: MATCH pronalazi prvu vrijednost koja je točno jednaka Lookup_value. Podaci Lookup_array mogu se razvrstati u bilo kojem redoslijedu.
- ako Match_type = -1: MATCH pronalazi najmanju vrijednost koja je veća ili jednaka Lookup_value. Podaci Lookup_array moraju biti razvrstani u silaznom redoslijedu.
Koraci
Ove se korake unose nakon zareza unesenog u prethodnom koraku na retku Row_num u dijaloškom okviru INDEX.
- Nakon zareza na retku Row_num , upišite nulu " 0 " jer želimo da ugniježđena funkcija vrati točno podudaranja s uvjetima koje unosimo u ćelijama D3 i E3.
- Upišite kružni nosač zatvaranja " ) kako biste dovršili funkciju MATCH.
- Ostavite dijaloški okvir INDEX funkcije otvoren za sljedeći korak u vodiču.
07 od 09
Natrag na funkciju INDEX
Sada kada je funkcija MATCH izvršena, premjestit ćemo se na treću liniju otvorenog dijaloškog okvira i unijeti zadnji argument za funkciju INDEX.
Ovaj treći i završni argument je argument Column_num koji govori Excel broj stupca u rasponu D6 do F11 gdje će pronaći informacije koje želimo vratiti funkcijom. U ovom slučaju, dobavljač za widgete titana .
Koraci
- Kliknite redak Column_num u dijaloškom okviru.
- Unesite broj tri " 3 " (bez navodnika) na ovom retku jer tražimo podatke u trećem stupcu dometa D6 do F11.
- Nemojte kliknuti U redu ili zatvoriti dijaloški okvir INDEX funkcije. Mora ostati otvorena za sljedeći korak u vodiču - stvaranje formule polja .
08 od 09
Izrada obrasca formule
Prije zatvaranja dijaloškog okvira moramo pretvoriti ugniježđenu funkciju u formulu polja .
Polje formule je ono što omogućuje da pretražuje više pojmova u tablici podataka. U ovom vodiču želimo podudarati dva termina: Widgeti iz stupca 1 i titan iz stupca 2.
Stvaranje formule polja u programu Excel se vrši pritiskanjem tipki CTRL , SHIFT i ENTER na tipkovnici istodobno.
Učinak pritiskanja tih tipki zajedno je da okružuje funkciju s kovrčanim zagradama: {}, što znači da je sada formula polja.
Koraci
- S dovršenim dijaloškim okvirom koji je još otvoren iz prethodnog koraka ovog vodiča, pritisnite i držite tipke CTRL i SHIFT na tipkovnici pa pritisnite i otpustite tipku ENTER .
- Ako se pravilno radi, dijaloški okvir će se zatvoriti i pojavit će se pogreška # N / A u ćeliji F3 - ćeliji u koju smo unijeli funkciju.
- Pogreška # N / A pojavljuje se u ćeliji F3 jer su stanice D3 i E3 prazne. D3 i E3 su stanice u kojima smo rekli da ova funkcija pronađe Lookup_values u 5. koraku udžbenika. Nakon što se podaci dodaju u ove dvije stanice, pogreška će biti zamijenjena informacijama iz baze podataka .
09 od 09
Dodavanje kriterija za pretraživanje
Posljednji korak u vodiču jest dodavanje pojmova za pretraživanje u naš radni list.
Kao što je spomenuto u prethodnom koraku, želimo uskladiti pojmove Widgeti iz stupca 1 i Titana iz stupca 2.
Ako i samo ako naša formula pronađe podudaranje oba izraza u odgovarajućim stupcima u bazi podataka, vratit će vrijednost iz trećeg stupca.
Koraci
- Kliknite na ćeliju D3.
- Upišite widgete i pritisnite tipku Enter na tipkovnici.
- Kliknite na ćeliju E3.
- Upišite Titanium i pritisnite tipku Enter na tipkovnici.
- Naziv dobavljača Widgets Inc. trebao bi se pojaviti u ćeliji F3 - lokaciji funkcije jer je jedini dobavljač na popisu koji prodaje Titanium widgete.
- Kada kliknete na ćeliju F3, sve funkcije
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)
pojavljuje se u traci s formulama iznad radnog lista .
Napomena: U našem primjeru postojalo je samo jedan dobavljač za widgete titana. Ako je postojalo više od jednog dobavljača, dobavljač koji je prvi naveden u bazi podataka vraća se tom funkcijom.