4 funcții de căutare Excel pentru a căuta eficient foile de calcul

De cele mai multe ori, găsirea unei celule într-o foaie de calcul Excel este destul de ușoară; dacă nu puteți doar să scanați rândurile și coloanele pentru aceasta, puteți utiliza CTRL + F pentru ao căuta. Dar dacă lucrați cu o foaie de calcul foarte mare Cum să împărțiți o foaie de calcul excelentă CSV Excel în fișiere separate Cum să împărțiți o foaie de calcul excelentă CSV Excel în fișiere separate Unul dintre deficiențele Microsoft Excel este dimensiunea limitată a unei foi de calcul. Dacă aveți nevoie să faceți fișierul dvs. Excel mai mic sau să împărțiți un fișier CSV mare, citiți mai departe! Citiți mai mult, poate economisi o mulțime de timp pentru a utiliza una din aceste patru funcții de căutare. Indiferent de dimensiunea documentului dvs. Microsoft Excel, acestea vor fi mult mai eficiente.

Funcția VLOOKUP

Această funcție vă permite să specificați o coloană și o valoare și veți întoarce o valoare din rândul corespunzător dintr-o coloană diferită (dacă aceasta nu are sens, va deveni clară într-un moment). Două exemple în care puteți face acest lucru caută numele de familie al unui angajat prin numărul angajatului sau găsirea unui număr de telefon prin specificarea unui nume de familie. Iată sintaxa funcției:

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

[Lookup_value] este informația pe care o aveți deja; de exemplu, dacă trebuie să știți în ce stare se află un oraș, ar fi numele orașului. [table_array] vă permite să specificați celulele în care funcția va căuta valorile de căutare și retur. Când selectați intervalul dvs., asigurați-vă că prima coloană inclusă în matrice este cea care va include valoarea dvs. de căutare! Acest lucru este crucial. [col_index_num] este numărul coloanei care conține valoarea returnată.

[range_lookup] este un argument opțional și durează 1 sau 0. Dacă introduceți 1 sau omiteți acest argument, funcția caută valoarea introdusă sau numărul următor cel mai mic. Deci, în imaginea de mai jos, un VLOOKUP care caută un scor SAT de 652 va reveni la 646, fiind cel mai apropiat număr din listă mai mic de 652 și [range_lookup] implicit la 1.

Să aruncăm o privire la modul în care putem folosi acest lucru. Așa cum am făcut în articolele mele despre operatorii booleeni în Microsoft Excel Mini Excel Tutorial: Utilizați logica booleană pentru a procesa date complexe Mini Tutorial Excel: Utilizați logica booleană pentru a procesa date complexe Operatorii logici IF, NOT, AND și OR, vă pot ajuta să obțineți Excel newbie la putere utilizator. Explicăm elementele de bază ale fiecărei funcții și demonstrăm cum le puteți folosi pentru rezultate maxime. Citirea mai multor și avansate de numărare și adăugarea de mini Excel Tutorial: Utilizarea avansate de numărare și adăugarea de funcții în Excel Tutorial Excel Excel: Utilizarea avansate de numărare și adăugarea de funcții în Excel Numărarea și adăugarea formule poate să apară luminoasă în comparație cu formule Excel mai avansate. Dar ele vă pot ajuta să economisiți mult timp când trebuie să colectați informații despre datele din foaia dvs. de calcul. Citiți mai multe, voi folosi o foaie de calcul pe care am generat-o cu generateata.com. Acesta conține numere de identificare, nume și prenume, scoruri oraș, stat și SAT. Să spunem că vreau să găsesc scorul SAT al unei persoane cu numele de familie “Winters.” VLOOKUP o ușurează. Iată formula pe care o vom folosi:

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

Deoarece scorurile SAT sunt cea de-a patra coloană din coloana cu numele de ultimă generație, am folosit 4 pentru argumentul indexului coloanei. Rețineți că atunci când căutați text, setarea domeniului [range_lookup] la 0 este o idee bună; fără ea, puteți obține rezultate proaste. Iată ce ne dă Microsoft Excel:

A revenit 651, scorul SAT aparținând elevului numit Kennedy Winters, care se află în rândul 92 (afișat în insetul de mai sus). Ar fi trebuit mult mai mult să navighezi prin căutarea numelui decât să faci rapid sintaxa!

VLOOKUP poate fi, de asemenea, destul de util dacă utilizați Microsoft Excel pentru a vă face impozitele Faceți-vă impozitele? 5 Formule Excel trebuie să știi că îți faci impozitele? 5 Formule Excel Trebuie să știți Este cu două zile înainte ca impozitele dvs. să fie datorate și nu doriți să plătiți o altă taxă de depunere târzie. Acesta este momentul să valorificăm puterea Excel pentru a face totul în ordine. Citeste mai mult .

Note despre VLOOKUP

Câteva lucruri sunt bine de reținut când folosiți VLOOKUP. În primul rând, după cum am menționat anterior, asigurați-vă că prima coloană din intervalul dvs. este cea care include valoarea dvs. de căutare. Dacă nu se află în prima coloană, funcția va afișa rezultate incorecte. Dacă coloanele dvs. sunt bine organizate, aceasta nu ar trebui să fie o problemă.

Cel de-al doilea lucru pe care trebuie să-l țineți cont este că VLOOKUP va întoarce vreodată o singură valoare. Dacă am fi folosit “Georgia” ca valoare de căutare, ar fi returnat scorul primului elev din Georgia și nu a dat nici un indiciu că există, de fapt, doi studenți din Georgia.

Funcția HLOOKUP

În cazul în care VLOOKUP găsește valori corespunzătoare într-o altă coloană, HLOOKUP găsește valori corespunzătoare într-un rând diferit. Deoarece este de obicei cea mai ușoară scanare prin rubricile coloanelor până când găsiți cea potrivită și folosiți un filtru pentru a găsi ceea ce căutați, HLOOKUP este cel mai bine folosit atunci când aveți într-adevăr foi de calcul mari sau lucrați cu valori organizate de timp. Iată sintaxa:

= HLOOKUP ([lookup_value], [table_array], [rând_index_num], [interval_lookup])

[lookup_value], din nou, este valoarea pe care o cunoașteți și doriți să găsiți o valoare corespunzătoare pentru. [table_array] sunt celulele în care doriți să căutați. [row_index_num] specifică rândul de la care va veni valoarea returnată. Și [range_lookup] este același ca mai sus; lasati-o goala pentru a obtine cea mai apropiata valoare atunci cand este posibil sau introduceti 0 pentru a cauta doar meciurile exacte.

Pentru acest exemplu, am creat o nouă foaie de calcul cu generata.com. Acesta conține un rând pentru fiecare stat, împreună cu un scor SAT (vom spune că este scorul mediu pentru stat) în anii 2000-2014. Vom folosi HLOOKUP pentru a găsi scorul mediu în Minnesota în 2013. Iată cum vom face acest lucru:

= HLOOKUP (2013, A1: P51, 24)

(Rețineți că 2013 nu este în ghilimele deoarece este un număr și nu un șir, de asemenea, 24 provine din Minnesota fiind în rândul 24). După cum puteți vedea în imaginea de mai jos, scorul este returnat:

Minnesotanii au obținut un scor mediu de 1014 în 2013.

Note despre HLOOKUP

Ca și în VLOOKUP, valoarea de căutare trebuie să fie în primul rând al matricei dvs. de masă; acest lucru este rar o problemă cu HLOOKUP, deoarece de obicei folosiți un titlu de coloană pentru o valoare de căutare. HLOOKUP returnează numai o singură valoare.

Funcțiile INDEX și MATCH

INDEX și MATCH sunt două funcții diferite, dar atunci când sunt utilizate împreună pot face cautarea unei foi de calcul mari mult mai repede. Ambele funcții au dezavantaje, dar prin combinarea lor vom construi pe punctele forte ale ambelor. În primul rând, însă, sintaxa:

= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])

În INDEX, [array] este matricea în care veți căuta. [numărul de rând] și [column_number] pot fi utilizate pentru a vă restrânge căutarea; vom arunca o privire la asta într-o clipă.

MATCH's [lookup_value] este un termen de căutare care poate fi un șir sau un număr; [lookup_array] este matricea în care Microsoft Excel va căuta termenul de căutare. [match_type] este un argument opțional care poate fi 1, 0 sau -1; 1 va returna cea mai mare valoare care este mai mică sau egală cu termenul dvs. de căutare; 0 va returna doar termenul exact; și -1 va reveni la cea mai mică valoare care este mai mare sau egală cu termenul dvs. de căutare.

S-ar putea să nu fie clar cum vom folosi împreună aceste două funcții, așa că o voi pune aici. MATCH ia un termen de căutare și returnează o referință de celule. În imaginea de mai jos, puteți vedea că într-o căutare pentru valoarea 646 din coloana F, MATCH returnează 4.

INDEX, pe de altă parte, face opusul: este nevoie de o referință de celule și returnează valoarea în ea. Puteți vedea aici că, atunci când este spus să se întoarcă coloana a șasea a coloanei orașului, INDEX se întoarce “Ancorare,” valoarea din rândul 6.

Ceea ce vom face este să combinăm cele două astfel încât MATCH returnează o referință de celule și INDEX folosește această referință pentru a căuta valoarea într-o celulă. Să presupunem că îți amintești că a fost un student al cărui nume de familie era Waters și vrei să vezi ce scor al acestui elev era. Iată formula pe care o vom folosi:

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

Veți observa că tipul de potrivire este setat la 0 aici; când căutați un șir, asta veți dori să utilizați. Iată ce obținem atunci când executăm această funcție:

După cum puteți vedea din inserție, Owen Waters a marcat 1720, numărul care apare atunci când executăm funcția. Acest lucru nu poate părea tot atât de util atunci când vă puteți uita doar câteva coloane, dar imaginați cât timp ați salva dacă ar fi trebuit să faceți acest lucru de 50 de ori pe o foaie de calcul mare de baze de date Excel Vs. Acces - Poate o foaie de calcul să înlocuiască o bază de date? Excel Vs. Acces - Poate o foaie de calcul să înlocuiască o bază de date? Ce instrument trebuie să utilizați pentru gestionarea datelor? Accesul și Excel oferă atât filtrarea, colaționarea și interogarea datelor. Vă vom arăta care dintre cele mai potrivite pentru nevoile dvs. Citiți mai multe care conțineau câteva sute de coloane!

Să înceapă căutarea

Microsoft Excel are o mulțime de funcții extrem de puternice 3 Formule Crazy Excel care fac lucruri uimitoare 3 Formule de nebun Excel care fac lucruri uimitoare Formule de formatare condiționată în Microsoft Excel pot face lucruri minunate. Iată câteva trucuri Excel de productivitate. Citiți mai multe, iar cele patru de mai sus doar zgâriați suprafața. Chiar și cu această prezentare sumară, ar trebui să puteți economisi o mulțime de timp atunci când lucrați cu foi de calcul mari. Dacă doriți să vedeți cât de mult puteți face cu INDEX, verificați “Indicele impunător” la eroul Microsoft Excel.

Cum executați căutările în foi de calcul mari? Cum ați găsit aceste funcții de căutare pentru a fi de ajutor? Împărtășește-ți gândurile și experiențele de mai jos!

Credite de imagine: lupă pe o foaie de calcul Prin Shutterstock

Explorați mai multe despre: Microsoft Excel, Foaie de calcul.