Toate secretele funcției Excel VLOOKUP pentru a găsi date într-un tabel și a le extrage în altul

2024 Autor: Malcolm Clapton | [email protected]. Modificat ultima dată: 2023-12-17 04:09

După ce ați citit articolul, veți învăța nu numai cum să găsiți date într-o foaie de calcul Excel și să le extrageți într-o alta, ci și tehnici care pot fi utilizate împreună cu funcția CĂUTARE V.

Când lucrați în Excel, de foarte multe ori este nevoie să găsiți date într-un tabel și să le extrageți în altul. Dacă încă nu știți cum să faceți acest lucru, atunci după ce ați citit articolul, nu numai că veți învăța cum să faceți acest lucru, ci și în ce condiții puteți strânge performanța maximă a sistemului. Sunt luate în considerare majoritatea tehnicilor foarte eficiente care ar trebui utilizate împreună cu funcția CĂUTARE V.

Chiar dacă folosești funcția VLOOKUP de ani de zile, atunci cu un grad ridicat de probabilitate acest articol îți va fi util și nu te va lăsa indiferent. De exemplu, fiind specialist IT, apoi șef în IT, folosesc VLOOKUP de 15 ani, dar am reușit să mă ocup de toate nuanțele abia acum, când am început să predau oamenii Excel pe o bază profesională.

CĂUTARE V este o abreviere pentru vvertical NSinspecţie. La fel, VLOOKUP - Vertical LOOKUP. Însuși numele funcției ne sugerează că caută în rândurile tabelului (vertical - iterând peste rânduri și fixând coloana), și nu în coloane (orizontal - iterând peste coloane și fixând rândul). Trebuie menționat că VLOOKUP are o soră - o rățușă urâtă care nu va deveni niciodată lebădă - aceasta este funcția HLOOKUP. HLOOKUP, spre deosebire de VLOOKUP, efectuează căutări orizontale, dar conceptul de Excel (și într-adevăr conceptul de organizare a datelor) implică faptul că tabelele dumneavoastră au mai puține coloane și mai multe rânduri. De aceea trebuie să căutăm după rânduri de multe ori mai des decât după coloane. Dacă folosiți prea des funcția HLO în Excel, atunci este posibil să nu ați înțeles ceva în această viață.

Sintaxă

Funcția VLOOKUP are patru parametri:

= CĂUTARE V (;; [;]), Aici:

- valoarea dorită (rar) sau o referire la o celulă care conține valoarea dorită (majoritatea mare a cazurilor);

- referire la un interval de celule (matrice bidimensională), în coloana FIRST (!) a cărei valoare va fi căutată;

- numărul coloanei din intervalul din care va fi returnată valoarea;

- acesta este un parametru foarte important care răspunde la întrebarea dacă prima coloană a intervalului este sortată în ordine crescătoare. Dacă matricea este sortată, specificăm valoarea TRUE sau 1, în caz contrar - FALSE sau 0. Dacă acest parametru este omis, acesta este implicit 1.

Pun pariu că mulți dintre cei care știu că VLOOKUP funcționează ca fulger, după ce au citit descrierea celui de-al patrulea parametru, s-ar putea să se simtă inconfortabil, deoarece sunt obișnuiți să-l vadă într-o formă ușor diferită: de obicei vorbesc despre o potrivire exactă atunci când căutarea (FALSE sau 0) sau o scanare a intervalului (TRUE sau 1).

Acum trebuie să vă încordați și să citiți următorul paragraf de mai multe ori până când simțiți sensul a ceea ce s-a spus până la sfârșit. Fiecare cuvânt este important acolo. Exemplele vă vor ajuta să vă dați seama.

Cum funcționează exact formula VLOOKUP?

• Tipul de formulă I. Dacă ultimul parametru este omis sau specificat egal cu 1, atunci CĂUTARE V presupune că prima coloană este sortată în ordine crescătoare, deci căutarea se oprește la rândul care precede imediat linia care conține valoarea mai mare decât cea dorită … Dacă nu se găsește un astfel de șir, se returnează ultimul rând al intervalului.

  

• Formula II. Dacă ultimul parametru este specificat ca 0, atunci VLOOKUP scanează prima coloană a matricei secvenţial și opreşte imediat căutarea atunci când este găsită prima potrivire exactă cu parametrul, altfel codul de eroare # N / A (# N / A) este returnat.

  

Fluxuri de lucru cu formule

VPR tip I

VPR tip II

Corolare pentru formulele de forma I

1. Formulele pot fi folosite pentru a distribui valorile în intervale.
2. Dacă prima coloană conține valori duplicate și este sortată corect, atunci va fi returnat ultimul dintre rândurile cu valori duplicate.
3. Dacă căutați o valoare care este evident mai mare decât poate conține prima coloană, atunci puteți găsi cu ușurință ultimul rând al tabelului, care poate fi destul de valoros.
4. Această vizualizare va returna eroarea # N/A numai dacă nu găsește o valoare mai mică sau egală cu cea dorită.
5. Este destul de dificil de înțeles că formula returnează valori greșite dacă matricea dvs. nu este sortată.

Corolare pentru formulele de tip II

Dacă valoarea dorită apare de mai multe ori în prima coloană a matricei, atunci formula va selecta primul rând pentru extragerea ulterioară a datelor.

Performanță CĂUTARE V

Ai ajuns la punctul culminant al articolului. S-ar părea, ei bine, care este diferența dacă specific zero sau unu ca ultimul parametru? Practic, toată lumea indică, desigur, zero, deoarece acest lucru este destul de practic: nu trebuie să vă faceți griji cu privire la sortarea primei coloane a matricei, puteți vedea imediat dacă valoarea a fost găsită sau nu. Dar dacă aveți câteva mii de formule VLOOKUP pe foaia dvs., veți observa că VLOOKUP II este lentă. În același timp, de obicei, toată lumea începe să se gândească:

• Am nevoie de un computer mai puternic;
• Am nevoie de o formulă mai rapidă, de exemplu, mulți oameni știu despre INDEX + MATCH, care se presupune că este mai rapidă cu 5-10%.

Și puțini oameni cred că, de îndată ce începeți să utilizați VLOOKUP de tip I și vă asigurați că prima coloană este sortată prin orice mijloace, viteza VLOOKUP va crește de 57 de ori. Scriu în cuvinte – DE CINCIzeci și șapte de ori! Nu 57%, ci 5.700%. Am verificat acest fapt destul de sigur.

Secretul unei astfel de lucrări rapide constă în faptul că pe o matrice sortată poate fi aplicat un algoritm de căutare extrem de eficient, care se numește căutare binară (metoda înjumătățirii, metoda dihotomiei). Deci VLOOKUP de tip I îl aplică, iar VLOOKUP de tip II caută fără nicio optimizare. Același lucru este valabil și pentru funcția MATCH, care include un parametru similar, și pentru funcția LOOKUP, care funcționează numai pe matrice sortate și este inclusă în Excel pentru compatibilitate cu Lotus 1-2-3.

Dezavantajele formulei

Dezavantajele VLOOKUP sunt evidente: în primul rând, caută numai în prima coloană a matricei specificate și, în al doilea rând, numai în dreapta acestei coloane. Și după cum înțelegeți, se poate întâmpla ca coloana care conține informațiile necesare să fie în stânga coloanei în care ne vom căuta. Combinația deja menționată de formule INDEX + MATCH este lipsită de acest dezavantaj, ceea ce o face cea mai flexibilă soluție pentru extragerea datelor din tabele în comparație cu VLOOKUP (VLOOKUP).

Câteva aspecte ale aplicării formulei în viața reală

Căutare interval

O ilustrare clasică a căutării unui interval este sarcina de a determina o reducere în funcție de dimensiunea comenzii.

Căutați șiruri de text

Desigur, VLOOKUP caută nu numai numere, ci și text. Trebuie avut în vedere că formula nu face distincție între cazul caracterelor. Dacă utilizați metacaractere, puteți organiza o căutare neclară. Există două metacaractere: "?" - înlocuiește orice caracter dintr-un șir de text, "*" - înlocuiește orice număr de caractere.

Spații de luptă

Se pune adesea întrebarea cum se rezolvă problema spațiilor suplimentare la căutare. Dacă tabelul de căutare poate fi în continuare șters de ele, atunci primul parametru al formulei VLOOKUP nu depinde întotdeauna de dvs. Prin urmare, dacă este prezent riscul de înfundare a celulelor cu spații suplimentare, atunci puteți folosi funcția TRIM pentru a o șterge.

Format de date diferit

Dacă primul parametru al funcției CĂUTARE V se referă la o celulă care conține un număr, dar care este stocată în celulă ca text, iar prima coloană a matricei conține numere în formatul corect, atunci căutarea va eșua. Este posibilă și situația inversă. Problema poate fi rezolvată cu ușurință prin traducerea parametrului 1 în formatul necesar:

= CĂUTARE V (−− D7; Produse! $ A $ 2: $ C $ 5; 3; 0) - dacă D7 conține text și tabelul conține numere;

= CĂUTARE V (D7 & ""); Produse! $ A $ 2: $ C $ 5; 3; 0) - și invers.

Apropo, puteți traduce textul într-un număr în mai multe moduri simultan, alegeți:

• Dublă negație -D7.
• Înmulțirea cu un D7 * 1.
• Adăugarea zero D7 + 0.
• Ridicarea la prima putere D7 ^ 1.

Convertirea unui număr în text se face prin concatenare cu un șir gol, ceea ce obligă Excel să convertească tipul de date.

Cum să suprimați # N / A

Acest lucru este foarte convenabil de făcut cu funcția IFERROR.

De exemplu: = IFEROARE (CĂUTARE V (D7; Produse! $ A $ 2: $ C $ 5; 3; 0); "").

Dacă VLOOKUP returnează codul de eroare # N / A, atunci IFERROR îl va intercepta și va înlocui parametrul 2 (în acest caz, un șir gol), iar dacă nu apare nicio eroare, atunci această funcție va pretinde că nu există deloc, dar există doar VLOOKUP care a returnat un rezultat normal.

Matrice

Adesea ei uită să facă referința matricei absolută, iar atunci când întinde matricea „plutește”. Nu uitați să utilizați $ A $ 2: $ C $ 5 în loc de A2: C5.

Este o idee bună să plasați matricea de referință pe o foaie separată a registrului de lucru. Nu intră sub picioare și va fi mai sigur.

O idee și mai bună ar fi să declarați această matrice ca un interval numit.

Mulți utilizatori, când specifică o matrice, folosesc o construcție ca A:C, specificând coloane întregi. Această abordare are dreptul de a exista, deoarece sunteți ferit de a fi nevoit să urmăriți faptul că matricea dvs. include toate șirurile necesare. Dacă adăugați rânduri la foaia cu matricea originală, atunci intervalul specificat ca A: C nu trebuie ajustat. Desigur, această construcție sintactică forțează Excel să lucreze puțin mai mult decât specificarea exactă a intervalului, dar acest lucru poate fi neglijat. Vorbim de sutimi de secundă.

Ei bine, în pragul geniului - pentru a aranja matricea în formă.

Folosind funcția COLUMN pentru a specifica coloana de extras

Dacă tabelul în care recuperați date folosind CUĂUTARE V are aceeași structură ca și tabelul de căutare, dar pur și simplu conține mai puține rânduri, atunci puteți utiliza funcția COLUMN () din CUȚIUNE V pentru a calcula automat numerele coloanelor care urmează să fie preluate. În acest caz, toate formulele VLOOKUP vor fi aceleași (ajustate pentru primul parametru, care se schimbă automat)! Rețineți că primul parametru are o coordonată absolută a coloanei.

Crearea unei chei compuse cu & „|” &

Dacă devine necesară căutarea după mai multe coloane în același timp, atunci este necesar să se facă o cheie compusă pentru căutare. Dacă valoarea returnată nu ar fi textuală (cum este cazul câmpului „Cod”), ci numerică, atunci formula SUMIFS mai convenabilă ar fi potrivită pentru aceasta și cheia de coloană compusă nu ar fi deloc necesară.

Acesta este primul meu articol pentru un Lifehacker. Dacă ți-a plăcut, te invit să vizitezi și, de asemenea, să citești cu plăcere în comentarii despre secretele tale de utilizare a funcției VLOOKUP și altele asemenea. Mulțumiri.:)