4 Excel funkcije pretraživanja za učinkovito pretraživanje proračunskih tablica

Većinu vremena pretraživanje Microsoftove proračunske tablice Excel prilično je jednostavno. Ako ne možete samo skenirati redove i stupce za to, pomoću njega možete koristiti Ctrl + F. Ako radite s stvarno velikom proračunskom tablicom, može uštedjeti puno vremena za korištenje jedne od ove četiri funkcije pretraživanja.

Jednom kada znate kako pretraživati ​​u Excelu pomoću pretraživanja, nije važno koliko su velike proračunske tablice, uvijek ćete moći pronaći nešto u programu Excel!

1. VLOOKUP funkcija

Ova funkcija omogućuje vam odrediti stupac i vrijednost i vratit ćete vrijednost iz odgovarajućeg retka drugog stupca (ako to nema smisla, to će postati jasno u trenu). Dva primjera gdje to možete učiniti potražite prezime zaposlenika prema njihovom broju zaposlenika ili pronađite telefonski broj uz navođenje prezimena.

Evo sintaksa funkcije:

= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])

• [Tražena_vrijednost] je podatak koji već posjedujete. Na primjer, ako trebate znati u kojem se gradu nalazi grad, to bi bilo njegovo ime.
• [Table_array] omogućuje vam da odredite stanice u kojima će funkcija tražiti vrijednosti pretraživanja i povratka. Kad odabirete raspon, budite sigurni da je prvi stupac uključen u vaš niz taj koji će sadržavati vašu vrijednost pretraživanja!
• [Col_index_num] je broj stupca koji sadrži povratnu vrijednost.
• [Range_lookup] je neobavezni argument i traje 1 ili 0. Ako unesete 1 ili izostavite ovaj argument, funkcija traži vrijednost koju ste unijeli ili sljedeći-najmanji broj. Dakle, na slici ispod, VLOOKUP koji traži SAT ocjenu 652 vratit će 646, jer je to najbliži broj na popisu koji je manji od 652, a [range_lookup] podrazumijeva 1.

Pogledajmo kako biste mogli ovo koristiti. Ova proračunska tablica sadrži ID brojeve, imena i prezimena, grad, državu i SAT rezultate. Recimo da želite pronaći SAT rezultat osobe s prezimenom “zime.” VLOOKUP to olakšava. Evo formule koju biste koristili:

= VLOOKUP ("Zime", C2: F101, 4, 0)

Budući da su SAT rezultati četvrti stupac u koloni s prezimenom, 4 je argument indeksa stupaca. Kada tražite tekst, dobra je postavka [range_lookup] na 0. Bez nje možete dobiti loše rezultate.

Evo rezultata:

Vratio je 651, SAT rezultat koji pripada studentu po imenu Kennedy Winters, koji je u retku 92 (prikazan u početku iznad). Proći bi traženje imena trebalo puno više vremena nego da se brzo upiše sintaksa!

Bilješke o VLOOKUP-u

Nekoliko je stvari dobro zapamtiti kada koristite VLOOKUP. Provjerite je li prvi stupac u vašem rasponu onaj koji uključuje vašu vrijednost pretraživanja. Ako nije u prvom stupcu, funkcija će vratiti pogrešne rezultate. Ako su vaši stupci dobro organizirani, to ne bi trebao biti problem.

Također, imajte na umu da će VLOOKUP ikada vratiti samo jednu vrijednost. Da ste je koristili “Gruzija” kao vrijednost pretraživanja, vratio bi ocjenu prvog studenta iz Gruzije, i bez davanja naznaka da u stvari postoje dva studenta iz Gruzije.

2. HLOOKUP funkcija

Tamo gdje VLOOKUP pronalazi odgovarajuće vrijednosti u drugom stupcu, HLOOKUP pronalazi odgovarajuće vrijednosti u drugom retku. Kako je obično najlakše skenirati kroz zaglavlja stupaca dok ne nađete pravu i pomoću filtra potražite ono što tražite, HLOOKUP se najbolje koristi kada imate stvarno velike proračunske tablice ili radite s vrijednostima koje organiziraju vrijeme.

Evo sintaksa funkcije:

= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])

• [Tražena_vrijednost] je vrijednost za koju znate i za koju želite pronaći odgovarajuću vrijednost.
• [Table_array] je ćelija u kojoj želite pretraživati.
• [Row_index_num] određuje redak iz kojeg će doći povratna vrijednost.
• [Range_lookup] isto je kao u VLOOKUP-u, ostavite ga prazno kako biste dobili najbližu vrijednost kada je to moguće ili unesite 0 samo da biste tražili točne podudarnosti.

Ova proračunska tablica sadrži redak za svaku državu, zajedno s ocjenom SAT u godinama 2000-2014. Pomoću HLOOKUP-a možete pronaći prosječni rezultat u Minnesoti u 2013. Evo kako ćemo to učiniti:

= HLOOKUP (2013, A1: P51, 24)

Kao što možete vidjeti na slici ispod, rezultat se vraća:

Minnesotani su u 2013. godini postigli prosjek od 1014. Napominjemo da 2013. nije u navodnicima jer je to broj, a ne niz. Također, 24 dolaze iz Minnesote koji su u 24. redu.

Bilješke o HLOOKUP-u

Kao i kod VLOOKUP-a, vrijednost pretraživanja treba biti u prvom redu vaše tablice. To je rijetko problem s HLOOKUP-om, jer za vrijednost pretraživanja obično koristite naslov stupca. HLOOKUP također vraća samo jednu vrijednost.

3-4. Funkcije INDEX i MATCH

INDEX i MATCH dvije su različite funkcije, ali kada se zajedno koriste mogu pretraživati ​​veliku proračunsku tablicu mnogo brže. Obje funkcije imaju nedostatke, ali kombinirajući ih nadogradit ćemo na snagama obje.

Prvo, sintaksa obje funkcije:

= INDEX ([niz], [red_broj], [stupac_broj])

• [Niz] je niz u kojem ćete pretraživati.
• [ROW_NUMBER] i [COLUMN_NUMBER] može se koristiti za sužavanje pretraživanja (malo ćemo ga pogledati u trenu).

= MATCH ([lookup_value], [lookup_array], [match_type])

• [Tražena_vrijednost] je pojam za pretraživanje koji može biti niz ili broj.
• [Polje_pregleda] je niz u kojem će Microsoft Excel tražiti pojam za pretraživanje.
• [MATCH_TYPE] je izborni argument koji može biti 1, 0 ili -1. 1 će vratiti najveću vrijednost koja je manja ili jednaka vašem pojmu za pretraživanje. 0 će vam vratiti samo točan pojam, a -1 će vratiti najmanju vrijednost koja je veća ili jednaka vašem pojmu za pretraživanje.

Možda nije jasno kako ćemo zajedno upotrebljavati ove dvije funkcije, pa ću ih ovdje iznijeti. MATCH uzima pojam za pretraživanje i vraća referencu na ćeliju. Na slici ispod možete vidjeti da u pretraživanju vrijednosti 646 u stupcu F MATCH vraća 4.

INDEX, s druge strane, radi suprotno: uzima referencu ćelije i u njoj vraća vrijednost. Ovdje možete vidjeti da, kada se kaže da vrati šestu ćeliju stupca City, INDEX se vraća “Sidrište,” vrijednost iz retka 6.

Ono što ćemo učiniti je kombinirati dvije, tako da MATCH vraća referencu ćelije, a INDEX koristi tu referencu za traženje vrijednosti u ćeliji. Recimo da se sjećate da je postojao student čije je prezime bilo Waters i želite vidjeti kakav je bio rezultat ovog studenta. Evo formule koju ćemo koristiti:

= INDEX (F: F, MATCH ("Vode", C: C, 0))

Primijetit ćete da je vrsta podudaranja ovdje postavljena na 0. Kad tražite niz, to želite koristiti. Evo što dobivamo kada pokrenemo tu funkciju:

Kao što vidite iz unosa, Owen Waters postigao je 1720, broj koji se pojavljuje kada pokrenemo funkciju. Ovo se možda ne čini toliko korisnim kada možete pogledati nekoliko stupaca, ali zamislite koliko biste vremena uštedjeli ako biste to morali napraviti 50 puta na velikoj proračunskoj tablici Excel Vs. Pristup - Može li proračunska tablica zamijeniti bazu podataka? Excel vs. Pristup - Može li proračunska tablica zamijeniti bazu podataka? Koji alat trebate koristiti za upravljanje podacima? Pristup i Excel imaju značajku filtriranja podataka, usporedbe i upita. Pokazat ćemo vam koji je najprikladniji za vaše potrebe. koji je sadržavao nekoliko stotina stupaca!

Neka Excel pretraživanja počnu

Microsoft Excel ima puno izuzetno moćnih funkcija za manipuliranje podacima, a četiri gore navedena mjesta samo ogrebotine po površini. Naučite ih koristiti znatno će vam olakšati život.

Ako zaista želite ovladati Microsoft Excel-om, zaista biste mogli imati koristi od čuvanja Essential Excel varalice, Essential Microsoft Excel formula i funkcije varalice, najvažnije Microsoftove formule i funkcije. najdraži svjetski program za proračunske tablice. blizu ruke!

Kreditna slika: Cico / Shutterstock