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č uključuje korak-po-korak primjer stvaranja formule za pretraživanje koja koristi više kriterija za pronalaženje dobavljačatitanski widgeti u uzorku baze podataka. Slijedite ih kako biste saznali kako je lako stvoriti vlastite prilagođene formate za pretraživanje.
Pripremite svoju Excel radnu knjigu s podacima
Da biste slijedili korake u ovom vodiču, morat ćete započeti unosom uzorka u sljedeće ćelije, kao što je prikazano na gornjoj slici. Redci 3 i 4 ostaju prazni kako bi se smjestila formula polja stvorena tijekom ovog vodiča.
- Unesite najviši raspon podataka u ćelije D1 do F2
- Unesite drugi raspon u ćelije D5 do F11
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 08Izradite funkciju INDEX u programu Excel
INDEKS funkcija je jedna od rijetkih u Excelu koja ima više oblika. Funkcija ima Oblik obrasca i a 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 jer želimo znati ime dobavljača za widgete titana, a ne kao referencu ovog dobavljača u našoj bazi podataka. Slijedite ove korake da biste izradili funkciju INDEX:
- Kliknite na ćeliju F3 da je postane aktivna ćelija - ovdje ćemo ući u ugniježđenu funkciju.
- Klikni na formule karticu izbornika vrpce.
- izabrati Potraživanje i referenca iz trake s trakom za otvaranje padajućeg izbornika.
- Kliknite na INDEKS na popisu da se pojavi Odaberite Argumenti dijaloški okvir.
- Odaberi niz, row_num, col_num u dijaloškom okviru.
- Klik u redu otvoriti dijaloški okvir INDEX funkcije; to će otvoriti Formula Builder u programu Excel.
- U Formula Builder, Klikni na red crta.
- Istaknite stanice D6 do F11klikom i povlačenjem, u radnom listu kako biste unijeli raspon u graditelj.
U starijim verzijama programa Excel Formula Builder zamijenit će se Argumentni argumenti prozor. Primijenite iste korake navedene u ovom vodiču u prozoru.
03 od 08Pokrenite ugniježđenu funkciju MATCH
Prilikom gniježđenja jedne funkcije unutar drugog, nije moguće otvoriti formulaciju formule druge ili ugniježđene funkcije o unesite potrebne argumente. Ugniježđena funkcija mora biti upisana kao jedan od razvrstanih u prvoj funkciji.
Kada ručno unosite funkcije, argumenti funkcije međusobno se odvajaju zarezom.
Prvi korak u unosu u ugrađenu funkciju MATCH je unijeti tražena_vrijednost argument. 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; tnjegov je učinak povezivanjem ili povezivanjem dvije ili više ćelijskih referencija zajedno pomoću simbola i znaka - &.
- U Formula Builder, Klikni na broj_retka crta.
- Upišite naziv funkcije MATCH nakon čega slijedi otvoreni okrugli nosač.
- Kliknite na ćeliju D3 za unos navedene ćelije u dijaloški okvir.
- Upišite znak i nakon referentne ćelije D3 kako bi se dodala referenca druge ćelije.
- Kliknite na ćeliju E3 za ulazak u ovu drugu referencu stanica.
- Unesite zarez nakon reference ćelije E3 da biste dovršili unos funkcije MATCH tražena_vrijednost argument.
U posljednjem koraku udžbenika, naš Lookup_values bit će uneseni u ćelije D3 i E3 radnog lista.
04 od 08Dovršite funkciju Nested MATCH
Ovaj korak pokriva dodavanje polje_pregleda argument za ugniježđene MATCH funkcija. 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 pojam, tako da unese više polja, ponovo upotrijebimo znak i spojimo polja zajedno.
Ovi se koraci unose nakon zareza unesenog u prethodnom koraku na broj_retka line u INDEKS funkcija.
- Klikni na broj_retka line nakon zareza da mjesto točke umetanja bude na kraju trenutnog unosa.
- Istaknite stanice D6 do D11 u radnom listu za ulazak u raspon - ovo je prvi niz funkcija za pretraživanje.
- Upišite apsorber i nakon ćelije D6 D11 jer želimo da funkcija pretražuje dva polja.
- Istaknite stanice E6 do E11 u radnom listu za ulazak u raspon - ovo je drugi niz funkcija je za pretraživanje.
- Unesite zarez nakon reference ćelije E3 za dovršetak unosa MATCH funkcija je polje_pregleda argument.
- Ostavi INDEKS otvorite dijaloški okvir za sljedeći korak u vodiču.
Dodajte Argument vrste MATCH
Treći i konačni argument funkcije MATCH je MATCH_TYPE argument; to govori Excel kako da odgovara tražena_vrijednost s vrijednostima u Lookup_array - dostupni su odabiri 1, 0 ili -1.
Ovaj argument nije obavezan. Ako je izostavljena, funkcija koristi zadanu vrijednost od 1.
- Ako je Match_type = 1 ili je izostavljen: MATCH pronalazi najveću vrijednost koja je manja ili jednaka vrijednosti Lookup_value. Podaci Lookup_array moraju biti razvrstani po uzlaznoj poruci.
- Ako je Match_type = 0: MATCH pronalazi prvu vrijednost koja je točno jednaka vrijednosti Lookup_value. Podaci Lookup_array mogu se razvrstati u bilo kojem redoslijedu.
- Ako je Match_type = -1: MATCH pronalazi najmanju vrijednost koja je veća ili jednaka vrijednosti Lookup_value. Podaci Lookup_array moraju biti razvrstani u silaznom redoslijedu.
Ovi se koraci unose nakon zareza unesenog u prethodnom koraku na broj_retka line u INDEKS funkcija.
- Nakon zareza na broj_retka line, upišite nulu - 0 - budući da želimo da ugniježđena funkcija vrati točno podudaranja s uvjetima koji smo unijeli u ćelije D3 i E3.
- Upišite kružni nosač zatvaranja - ) - dovršiti MATCH funkcija.
- Ostavi Formula Builder otvorite se za sljedeći korak u vodiču.
Završavanje funkcije INDEX
Sada kada je MATCH učinili smo da ćemo se preseliti na treću liniju Formula Buildera i unijeti posljednji argument za INDEKS funkcija. Ovaj treći i posljednji argument je broj_stupca argument koji kaže Excelu broj stupca u rasponu D6 do F11; ovo je mjesto gdje će pronaći informacije koje želimo vratiti funkcijom. U ovom slučaju dobavljač za titanski widgeti.
- Klikni na broj_stupca crta.
- Unesite broj tri - 3 - na toj liniji budući da tražimo podatke u trećem stupcu raspona D6 do F11.
- Opet, ostavite Formula Builder otvorite se za sljedeći korak u vodiču.
Izrada obrasca formule
Prije zatvaranja Formula Builder, moramo pretvoriti našu ugniježđenu funkciju u formulu polja; to je ono što našoj funkciji omogućuje traženje višestrukih pojmova u tablici podataka. U ovom vodiču želimo podudarati dva termina: Widgeti iz stupca 1 i titanijum iz stupca 2.
Stvaranje formule polja u programu Excel izvodi se pritiskom na CTRL, SHIFT, i UNESI tipke na tipkovnici u isto vrijeme. Jednom pritisnutom, funkcija će biti okružena kovrčanim noževima, što ukazuje da je funkcija sada niz.
- Zatvori Formula Builder klikom na gumb Gotovo dugme.
- Zatim odaberite ćeliju F4 i pritisnite Unesi ključ za prikaz formule.
- Da biste Formulu pretvorili u niz, istovremeno pritisnite CTRL + SHIFT + ENTER na tipkovnici.
- Ako je ispravno učinjeno a # N / A pogreška će se pojaviti u ćeliji F3 - ćeliji u kojoj smo ušli u funkciju.
- # N / A pojavljuje se pogreška u ćeliji F3 jer stanice D3 i E3 su prazne. D3 i E3 su stanice u kojima smo rekli da je funkcija pronađena Lookup_values, Nakon što se podaci dodaju u ove dvije stanice, pogreška će biti zamijenjena informacijama iz baze podataka.
Dodavanje kriterija za pretraživanje
Na kraju ćemo dodati pojmove za pretraživanje u naš radni list. Kao što je spomenuto u prethodnom koraku, želimo uskladiti uvjete Widgeti iz stupca 1 i titanijum 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.
- Kliknite na ćeliju D3.
- Tip Widgeti i pritisnite Unesi na tipkovnici.
- Kliknite na ćeliju E3.
- Tip titanijum i pritisnite Unesi na tipkovnici.
- Naziv dobavljača Widgeti 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, cjelovita funkcija pojavljuje se u traci s formulama iznad radnog lista.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)
U našem primjeru, postojao 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.
