Formula za Excel pretraživanje s višestrukim kriterijima

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

Funkcija pretraživanja s višestrukim kriterijima Excel. © Ted French

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 .

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

Upotreba Excelove funkcije INDEX u formuli traženja. © Ted French

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

  1. Kliknite na ćeliju F3 da biste ga učinili aktivnom ćelijom . Ovdje ćemo upisati ugniježđenu funkciju.
  2. Kliknite karticu formule na traci izbornika.
  3. Odaberite traku i referencu iz vrpce da biste otvorili padajući popis funkcije.
  4. Kliknite na INDEX na popisu da biste otvorili dijaloški okvir Select Arguments .
  5. Odaberite dijaloški okvir array, row_num, col_num .
  6. Kliknite OK (U redu) da biste otvorili dijaloški okvir INDEX.

03 od 09

Unosom indeksa funkcije funkcije INDEX

Kliknite sliku da biste vidjeli punu veličinu. © Ted French

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

  1. U dijaloškom okviru funkcije INDEX kliknite na redak polja .
  2. Istaknite ćelije D6 do F11 u radnom listu kako biste unijeli raspon u dijaloški okvir.

04 od 09

Pokretanje Nested MATCH funkcije

Kliknite sliku da biste vidjeli punu veličinu. © Ted French

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

  1. U dijaloškom okviru INDEX funkcije kliknite na redak Row_num .
  2. Upišite podudarnost imena funkcija i slijedi otvoreni okrugli nosač " ( "
  3. Kliknite na ćeliju D3 da biste unijeli referencu ćelije u dijaloški okvir.
  4. Upišite oznaku " & " nakon referentne ćelije D3 da biste dodali drugu referencu ćelije.
  5. Kliknite na ćeliju E3 da biste unijeli drugu ćeliju u dijaloški okvir.
  6. Upišite zarez "," nakon referentne ćelije E3 da biste dovršili unos argumenta Lookup_value funkcije MATCH.
  7. 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

Kliknite sliku da biste vidjeli punu veličinu. © Ted French

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.

  1. Kliknite zarez Row_num nakon zarezom kako biste stavili točku umetanja na kraj trenutnog unosa.
  2. Istaknite ćelije D6 do D11 u radnom listu da biste unijeli raspon. Ovo je prvi niz funkcije za pretraživanje.
  3. Upišite oznaku " & " nakon referenci za stanice D6: D11 jer želimo da funkcija pretražuje dva polja.
  4. Istaknite ćelije E6 do E11 u radnom listu kako biste ušli u raspon. Ovo je drugi niz funkcije za pretraživanje.
  5. Unesite zarez "," nakon referentne ćelije E3 da biste dovršili unos matrice Lookup_array funkcije MATCH.
  6. 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

Kliknite sliku da biste vidjeli punu veličinu. © Ted French

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.

Koraci

Ove se korake unose nakon zareza unesenog u prethodnom koraku na retku Row_num u dijaloškom okviru INDEX.

  1. 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.
  2. Upišite kružni nosač zatvaranja " ) kako biste dovršili funkciju MATCH.
  3. Ostavite dijaloški okvir INDEX funkcije otvoren za sljedeći korak u vodiču.

07 od 09

Natrag na funkciju INDEX

Kliknite sliku da biste vidjeli punu veličinu. © Ted French

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

  1. Kliknite redak Column_num u dijaloškom okviru.
  2. Unesite broj tri " 3 " (bez navodnika) na ovom retku jer tražimo podatke u trećem stupcu dometa D6 do F11.
  3. 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

Formula za pretraživanje Excelova retka. © Ted French

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

  1. 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 .
  2. 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.
  3. 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

Pronalaženje podataka pomoću obrasca za pretraživanje obrasca za Excel. © Ted French

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

  1. Kliknite na ćeliju D3.
  2. Upišite widgete i pritisnite tipku Enter na tipkovnici.
  3. Kliknite na ćeliju E3.
  4. Upišite Titanium i pritisnite tipku Enter na tipkovnici.
  5. Naziv dobavljača Widgets Inc. trebao bi se pojaviti u ćeliji F3 - lokaciji funkcije jer je jedini dobavljač na popisu koji prodaje Titanium widgete.
  6. 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.