Ghid complet pentru utilizarea funcțiilor de căutare și referință în Excel

Dacă ai lucrat vreodată cu Excel, probabil ați întâlnit nevoia de a căuta sau de a consulta date eficient și rapid. Deși mulți oameni se limitează la utilizarea funcțiilor de bază, funcții de căutare și referință În Excel, formulele sunt instrumente puternice care pot optimiza incredibil modul în care lucrați și analizați informațiile. O bună înțelegere a modului în care funcționează aceste formule vă va permite să economisiți timp, să evitați erorile și să luați decizii mult mai informate.

În acest articol, vom explora în adâncime toate funcții cheie de căutare și referință care există în Excel, descoperind cum sunt utilizate, caracteristicile lor specifice și trucuri practice care să vă ajute să profitați la maximum de ele. Pe lângă analiza exemplelor și a sintaxei, vom clarifica scopul fiecărei funcții și vă vom oferi sfaturi despre cum să le integrați în propriile foi de calcul, indiferent dacă sunteți începător sau aveți deja experiență, dar doriți să vă îmbunătățiți.

Ce sunt funcțiile de căutare și referință în Excel?

În universul funcțiilor Excel, grupul de căutare și referință Se remarcă prin faptul că ne permite să localizăm informații specifice într-un interval, tabel sau set de matrici, returnând rezultate pe baza potrivirilor sau pozițiilor relative. Aceste funcții sunt concepute pentru a automatiza recuperarea datelor corelate, facilitând analize complexe, referențierea încrucișată a informațiilor între tabele, generarea dinamică de rapoarte și multe altele.

Articol asociat:

Cum să stăpânești analiza datelor cu instrumente avansate Excel

Funcții principale de căutare și referință în Excel

Excel include o gamă largă de funcții de acest tip, fiecare cu propria utilizare specifică. Unele sunt cunoscute pe scară largă, cum ar fi CĂUTARE o CĂUTARE, în timp ce altele, cum ar fi CĂUTAREX o INDEX au câștigat importanță odată cu cele mai recente versiuni. Iată o descriere a celor mai importante caracteristici:

• CĂUTAREGăsește o valoare în prima coloană a unui tablou și returnează o valoare din același rând, într-o altă coloană specificată.
• CĂUTARESimilar cu VLOOKUP, dar efectuează căutarea în primul rând al unui tablou, returnând valori din celelalte rânduri.
• CăutareVă permite să căutați într-un singur rând sau într-o coloană și să găsiți o valoare în aceeași poziție într-un alt rând sau într-o altă coloană.
• CĂUTAREXVersiunea îmbunătățită a celor anterioare, care permite căutarea pe orizontală sau verticală, opțiuni avansate de potrivire și este mult mai flexibilă.
• INDEXReturnează valoarea unei anumite celule dintr-o matrice, dată fiind poziția sa pe rând și coloană.
• COINCIDEReturnează poziția relativă a unei valori într-un interval sau o matrice.
• INDIRECTCreează o referință pe baza unei valori textuale, utilă pentru referințele dinamice.
• DECALAJReturnează o distanță de referință față de o celulă inițială, foarte utilă pentru referințele variabile.
• ALEGEVă permite să selectați o valoare dintr-o listă de argumente pe baza indexului pe care îl definiți.
• ZONEReturnează numărul de zone dintr-o referință.
• COLOANĂ y FILAReturnează numărul coloanei sau al rândului unei referințe date.
• RÂNDURI y COLOANEReturnează numărul total de rânduri sau coloane dintr-o referință.
• HYPERLINKCreați un acces rapid la un alt document, site web sau locație în cadrul registrului de lucru.
• TRANSPUNE: Schimbați rândurile și coloanele într-o matrice.
• UnicosReturnează o listă de valori unice dintr-un array sau interval.
• FILTRUExtrage date dintr-un interval pe baza criteriilor definite de dvs.

Cum funcționează funcțiile de căutare populare

Să analizăm cele mai populare funcții de căutare, să le explicăm sintaxa și să vedem cum se comportă în situații tipice. Acest lucru vă va ajuta să înțelegeți diferențele și să le aplicați pe fiecare în parte după cum este necesar.

Funcția VLOOKUP

Funcția CĂUTARE (sau VLOOKUP în engleză) este probabil cea mai utilizată funcție atunci când vine vorba de căutarea informațiilor într-un tabel pe verticală.

Sintaxă: BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, )

• valoare_căutareDatele pe care doriți să le găsiți.
• căutare_matrice_înIntervalul de celule în care se efectuează căutarea, prima coloană conținând valorile căutate.
• coloane_indicatoareNumărul coloanei din care va fi preluată valoarea rezultată, începând de la stânga.
• curatOpțional. Adevărat (implicit) pentru potrivire aproximativă sau Fals pentru potrivire exactă.

De exemplu, dacă aveți un tabel de produse și doriți să căutați prețul unei anumite referințe, CĂUTARE poate returna automat acea valoare fără a fi nevoie să o căutați manual.

Funcția HLOOKUP

Sora celui precedent, CĂUTARE, caută în primul rând al unui interval și returnează date din coloana specificată.

Sintaxă: BUSCARH(valor_buscado, matriz_buscar_en, indicador_filas, )

Este ideal pentru mesele unde informațiile cheie sunt aranjate pe orizontală.

Funcția CĂUTARE

Funcția Căutare Este util atunci când vrei să cauți pe un singur rând sau pe o coloană și să returnezi o valoare aflată în aceeași poziție dintr-un alt interval. Poate fi utilizat în două moduri: ca vector sau ca matrice.

• Formă vectorialăVă permite să căutați într-un interval dintr-un singur rând sau coloană (vector) și să obțineți o valoare dintr-un alt vector aflat în aceeași poziție.
• Formă matricialăCaută în primul rând sau în prima coloană a unei matrice și returnează valoarea din ultimul rând sau coloană corespunzătoare.

Sintaxă (vector): BUSCAR(valor_buscado; vector_de_comparación; )

Sintaxă (matrice): BUSCAR(valor_buscado; matriz)

În ambele cazuri, dacă valoarea căutată nu este găsită exact, funcția returnează cea mai apropiată valoare care este mai mică sau egală cu valoarea căutată. Dacă valoarea căutată este mai mică decât orice valoare din matrice sau vector, funcția returnează #N/A.

FUNCȚIA BUSCARX

Una dintre cele mai recente adăugiri, CĂUTAREX (XLOOKUP) îmbunătățește și simplifică considerabil căutările. Vă permite să căutați atât pe orizontală, cât și pe verticală, acceptă potriviri aproximative și exacte, căutare inversă, setări implicite dacă nu se găsește nicio potrivire și multe altele. Pentru a afla mai multe despre beneficiile sale, consultați pagina noastră .

Această funcție este mai flexibilă și evită multe dintre limitările funcțiilor VLOOKUP și HLOOKUP; de exemplu, nu necesită ca valoarea de căutare să fie în prima coloană sau rând a matricei.

Funcțiile INDEX și MATCH

Combinația de INDEX y COINCIDE permite căutări avansate mult dincolo de ceea ce poate face VLOOKUP. INDEX vă permite să extrageți o valoare dintr-un array pe baza poziției sale. COINCIDE găsiți poziția unei valori într-un interval și, împreună, puteți căuta orice valoare doriți în orice rând sau coloană a unui tabel, indiferent de ordine.

Articol asociat:

Cum să gestionezi corect datele și orele în Excel: Un ghid complet pas cu pas

Sintaxa INDEX: ÍNDICE(matriz, núm_fila, )

Sintaxa MATCH: COINCIDIR(valor_buscado, rango_buscado, )

Împreună, acestea vă permit să căutați dinamic valori și să construiți căutări complexe.

Alte funcții de referință utile

• DECALAJ vă permite să obțineți un offset de interval față de o celulă inițială, cheie pentru crearea de intervale dinamice și aflați mai multe în Cum se creează analize condiționale în Excel.
• INDIRECT Este foarte util dacă trebuie să construiești referințe folosind text, ajutându-te să lucrezi cu formule variabile.
• COLOANĂ y FILA Acestea indică numărul coloanei sau al rândului unei referințe, foarte practic pentru calculele automate.
• TRANSPUNE vă permite să schimbați rândurile cu coloanele, în cazul în care tabelul este în direcția opusă față de ceea ce aveți nevoie.

Funcții moderne și avansate de căutare și referință

De-a lungul timpului, Excel a încorporat noi funcții care îi îmbunătățesc și mai mult capacitățile de analiză și automatizare:

• FILTRUVă permite să obțineți un tabel filtrat în funcție de o condiție sau de un set de condiții pe care le definiți.
• UnicosExtrage toate valorile unice dintr-un interval, eliminând duplicatele.
• ORDIN y FILTREAZĂ DUPĂAcestea facilitează ordonarea datelor în cadrul unei matrice sau pe baza unui alt interval.
• LUA, CĂDERE BRUSCA, TOCOL, LA RÂNDULDiverse funcții care vă permit să manipulați matrice, să returnați doar anumite rânduri sau coloane sau să transformați intervale în coloane/rânduri individuale.
• WRAPCOLS y WRAPWSReorganizați matricile prin împărțirea lor în funcție de numărul de elemente pe care le determinați.

Aceste funcții vă permit să creați tabele și analize mai dinamice, automatizând multe sarcini fără a fi nevoie de alte resurse externe.

Exemplu practic: CĂUTARE în vectori și matrici

Imaginează-ți că ai o coloană cu valori numerice și o alta cu culori asociate. Vrei să știi ce culoare corespunde valorii 4,19.

1. Puneți datele în două coloane, de exemplu, A și B, astfel:
   

   Frecvență	Culori
   4,14	roșu
   4,19	portocaliu
   5,17	amarillo
   5,77	verde
   6,39	albastru

2. Acum, într-o altă celulă puteți scrie formula: =BUSCAR(4,19; A2:A6; B2:B6)

Excel va căuta în coloana A valoarea 4,19 și va returna culoarea care se află pe același rând din coloana B: portocaliu.

Dacă căutați o valoare care nu se află în tabel, va fi returnată culoarea cu cel mai apropiat număr. Dacă valoarea căutată este mai mică decât minimul coloanei, va fi returnat #N/A.

Acest tip de căutare este esențial în analiza datelor, inventariere, controlul stocurilor și în orice situație în care trebuie să comparați informații.

Sfaturi cheie pentru stăpânirea funcțiilor de căutare și referință

• În versiunile mai noi de Excel, pariază pe SEARCHX ori de câte ori este posibil, deoarece abordează multe limitări ale VLOOKUP și HLOOKUP. Pentru a afla cum să îl utilizați, vă recomandăm să vizitați pagina noastră .
• Când lucrați cu matrice dinamice sau date care se modifică frecvent, combină INDEX și MATCH pentru rezultate robuste și flexibile.
• Nu uita să profiți de funcții precum FILTRU, Unicos și noi funcții matriceale pentru automatizarea rapoartelor.
• Dacă trebuie să faceți referire la intervale care se modifică în dimensiune, utilizați OFFSET sau INDIRECT pentru a vă menține formulele la zi.
• Pentru a face formulele mai ușor de citit, utilizați nume de intervale și descrieți clar ce face fiecare funcție, mai ales dacă partajați fișierul cu alte persoane.

Aplicații practice și exemple din viața reală

Funcțiile de căutare și referință sunt fundamentul automatizării sarcinilor zilnice în Excel, de la integrarea datelor din diferite foi de calcul până la generarea de rapoarte personalizate și crearea de tablouri de bord. Câteva aplicații practice includ:

• Încrucișați datele despre vânzări și clienți pentru a obține rapid informații relevante.
• Creați șabloane de inventar care se actualizează automat pe baza produselor introduse.
• Creați rapoarte dinamice care afișează valori pe baza selecțiilor personalizate.
• Comparați liste de angajați, studenți sau produse, detectând duplicate și diferențe.
• Filtrați volume mari de informații pentru a obține doar înregistrările care îndeplinesc anumite criterii.

Greșeli frecvente și cum să le evitați

Este obișnuit să faci anumite greșeli atunci când folosești aceste funcții, mai ales dacă nu acorzi atenție sintaxei și intervalelor:

• Erori de tip #N/AAcestea apar atunci când valoarea căutată nu este găsită sau când intervalul de căutare nu este adecvat.
• Probleme cu referințe relative și absoluteAveți grijă când trageți formule; folosiți simbolul $ pentru a ancora coloane sau rânduri.
• Nepotrivire în numărul de rânduri/coloaneCând utilizați LOOKUP sau INDEX/MATCH, asigurați-vă că intervalele au aceeași dimensiune.
• Căutări sensibile la majuscule/minusculeAceste funcții sunt în general insensibile la majuscule/minuscule, deci un „John” și un „john” vor fi echivalente.
• Amintiți-vă mereu Sortează datele dacă vei face căutări aproximative, întrucât lipsa de ordine poate da rezultate neașteptate.

Sfaturi pentru a vă îmbunătăți productivitatea cu aceste funcții

• Exersați cu tabele exemplificative, în acest fel veți învăța să gestionați erorile și să înțelegeți comportamentul fiecărei funcții.
• Utilizarea noilor experți pentru formule în Excel să construiască funcții complexe pas cu pas.
• Gândește-te întotdeauna cum poți automatiza procesele recurente utilizând formule de referință, economisind timp și evitând erorile umane.
• A profita de Ajutor Excel și documentația oficială pentru a învăța variante avansate și posibilitățile fiecărei funcții.

Articol asociat:

Cum să sortați și să filtrați datele ca un expert în Excel: Un ghid profesional și practic

Stăpânește funcții de căutare și referință Excel este o abilitate care face diferența între simpla completare a foilor de calcul și devenirea unui adevărat expert în analiza datelor. Dacă profiți de aceste instrumente și exersezi cu diferite exemple, îți vei automatiza sarcinile, vei reduce erorile și îți vei crește productivitatea, astfel încât să te poți concentra pe ceea ce contează cu adevărat: luarea unor decizii mai bune și ușurarea muncii zilnice. Îndrăznește să experimentezi cu ele și vei observa schimbarea în cel mai scurt timp! Distribuiți ghidul pentru ca mai mulți oameni să știe cum să o facă..