Cine este prea leneș sau nu are timp să citească – urmăriți videoclipul. Detalii și nuanțe sunt în textul de mai jos.
Formularea problemei
Deci, avem două tabele - masa de comenzi и listă de prețuri:
Sarcina este să înlocuiți automat prețurile din lista de prețuri în tabelul de comenzi, concentrându-vă pe numele produsului, astfel încât ulterior să puteți calcula costul.
Soluţie
În setul de funcții Excel, sub categorie Referințe și matrice (Căutare și referință) exista o functie VPR (CĂUTARE V).Această funcție caută o valoare dată (în exemplul nostru, acesta este cuvântul „Mere”) în coloana din stânga a tabelului specificat (lista de prețuri) deplasându-se de sus în jos și, după ce a găsit-o, afișează conținutul celulei adiacente (23 ruble) .Schematic, funcționarea acestei funcții poate fi reprezentată Deci:
Pentru a ușura utilizarea ulterioară a funcției, faceți un singur lucru deodată - dați-vă numelui intervalului de celule din lista de prețuri. Pentru a face acest lucru, selectați toate celulele listei de prețuri, cu excepția „antetului” (G3: H19), selectați din meniu Inserare – Nume – Atribuire (Inserați — Nume — Definiți) sau apăsați CTRL + F3 și introduceți orice nume (fără spații) ca Preț… Acum, în viitor, puteți folosi acest nume pentru a vă conecta la lista de prețuri.
Acum folosim funcția VPR… Selectați celula în care va fi introdusă (D3) și deschideți fila Formule – Inserare funcție (Formulele — Funcția de inserare)… În categorie Referințe și matrice (Căutare și referință) găsiți funcția VPR (CĂUTARE V) și apăsați OK… Va apărea o fereastră pentru introducerea argumentelor pentru funcție:
Le completăm pe rând:
- Valoarea dorită (Valoare de căutare) – numele produsului pe care funcția ar trebui să-l găsească în coloana din stânga a listei de prețuri. În cazul nostru, cuvântul „Mere” din celula B3.
- Tabel (Matrice de masă) – un tabel din care sunt luate valorile dorite uXNUMXbuXNUMXbare, adică lista noastră de prețuri. Pentru referință, folosim propriul nostru nume „Preț”, dat mai devreme. Dacă nu ați dat un nume, puteți doar să selectați tabelul, dar nu uitați să apăsați butonul F4pentru a fixa legătura cu semnele dolarului, deoarece altfel, va aluneca în jos când copiați formula noastră în restul celulelor din coloana D3:D30.
- Număr_coloană (Numărul de index al coloanei) – numărul de serie (nu o literă!) al coloanei din lista de prețuri din care vom lua valorile prețurilor. Prima coloană a listei de prețuri cu numele este numerotată 1, prin urmare avem nevoie de prețul din coloana numerotată 2.
- interval_lookup (Căutare interval) – doar două valori pot fi introduse în acest câmp: FALSE sau TRUE:
- Dacă se introduce o valoare 0 or MINCIUT (FALS), atunci de fapt asta înseamnă că doar căutarea este permisă potrivire perfecta, adică dacă funcția nu găsește articolul nestandard specificat în tabelul de comandă în lista de prețuri (dacă este introdus „Nucă de cocos”, de exemplu), va genera eroarea #N/A (fără date).
- Dacă se introduce o valoare 1 or TRUE (ADEVĂRAT), atunci aceasta înseamnă că permiteți căutarea nu pentru exact, dar potrivire aproximativă, adică în cazul „nucă de cocos”, funcția va încerca să găsească un produs cu o denumire cât mai apropiată de „nucă de cocos” și să returneze prețul pentru acest nume. În cele mai multe cazuri, o astfel de înlocuire aproximativă poate juca un truc utilizatorului, înlocuind valoarea produsului greșit care a fost de fapt acolo! Deci, pentru majoritatea problemelor reale de afaceri, căutarea aproximativă este cel mai bine să nu permiteți. Excepția este atunci când căutăm numere și nu text – de exemplu, atunci când calculăm Reducerile de pași.
Tot! Rămâne de apăsat OK și copiați funcția introdusă în întreaga coloană.
# N/A erori și suprimarea lor
Funcţie VPR (CĂUTARE V) returnează eroare #N/A (#N / A) în cazul în care un:
- Căutarea exactă este activată (argument Vizualizare interval = 0) și numele dorit nu este în Tabel.
- Căutare grosieră inclusă (Vizualizare interval = 1), dar Tabel, în care are loc căutarea nu este sortat în ordine crescătoare a numelor.
- Formatul celulei din care provine valoarea cerută a numelui (de exemplu, B3 în cazul nostru) și formatul celulelor primei coloane (F3: F19) a tabelului sunt diferite (de exemplu, numeric și text). ). Acest caz este tipic mai ales atunci când se utilizează coduri numerice (numere de cont, identificatori, date etc.) în loc de nume text. În acest caz, puteți utiliza funcțiile Ч и TEXT pentru a converti formatele de date. Va arata cam asa:
=CĂUTAREV(TEXT(B3),preț,0)
Puteți citi mai multe despre asta aici.
- Funcția nu poate găsi valoarea necesară deoarece codul conține spații sau caractere invizibile care nu pot fi imprimate (întreruperi de linie etc.). În acest caz, puteți utiliza funcții text TUNDE (TUNDE) и PRINT(CURAT) pentru a le elimina:
=CĂUTARE V(SPAȚII DE CURAT(CURAT(B3)), preț,0)
=CĂUTAREV(TRIM(CURAT(B3));preț;0)
Pentru a suprima mesajul de eroare #N / A (#N / A) în cazurile în care funcția nu poate găsi o potrivire exactă, puteți utiliza funcția DACA EROARE (DACA EROARE)… Deci, de exemplu, această construcție interceptează orice erori generate de VLOOKUP și le înlocuiește cu zerouri:
= DACĂ EROARE (CĂUTARE V (B3, preț, 2, 0), 0)
= DACĂ EROARE (CĂUTARE V (B3; preț; 2; 0); 0)
PS
Dacă trebuie să extrageți nu o singură valoare, ci întregul set deodată (dacă există mai multe diferite), atunci va trebui să șamanizați cu formula matricei. sau utilizați noua caracteristică XLOOKUP din Office 365.
- O versiune îmbunătățită a funcției CĂUTARE V (VLOOKUP 2).
- Calcul rapid al reducerilor de trepte (interval) folosind funcția CĂUTARE V.
- Cum se face o „CĂUTARE V stanga” folosind funcțiile INDEX și MATCH
- Cum să utilizați funcția CĂUTARE V pentru a completa formularele cu date din listă
- Cum să scoți nu prima, ci toate valorile din tabel simultan
- Funcțiile VLOOKUP2 și VLOOKUP3 din suplimentul PLEX