VLOOKUP cu text
Unul dintre criteriile de bază pentru a lucra cu VLOOKUP este valoarea „căutare” ar trebui să fie aceeași în celula de rezultat și, de asemenea, în tabelul de date principal, dar uneori, chiar dacă valoarea de căutare arată la fel în oricare dintre celule, totuși ajungem să obținem valoarea de eroare ca # N / A !. Acest lucru se datorează faptului că formatul valorii vlookup trebuie să fie diferit în ambele celule. Deci, în acest articol, vă vom arăta cum să lucrați cu formatul text al valorii Vlookup.
Exemplu de VLOOKUP pentru text
Mai jos este un exemplu de Excel VLOOKUP pentru text.
Puteți descărca acest șablon VLOOKUP pentru text Excel aici - VLOOKUP pentru șablon Excel textUneori numerele sunt stocate ca valori text și, în astfel de cazuri, nu le putem trata ca numere datorită funcționalității excel. De exemplu, uitați-vă la datele de mai jos.
În datele de mai sus, totul arată ca numere, dar atunci când îl însumăm ar trebui să obținem o valoare totală de 3712054, dar când aplicăm funcția SUM excel obținem numărul de mai jos.
Motivul din spatele acestui fapt este că unele numere sunt stocate ca valori text. Deci, cum identificăm valorile textului?
Putem identifica valorile textului folosind funcția excel ISNUMBER. Funcția ISNUMBER va reveni ADEVĂRAT dacă valoarea celulei selectate este numărul sau altfel va reveni FALS.
Deci, în celula B5 și B6, am obținut rezultatul ca FALS, ceea ce înseamnă că numerele de celule A5 și A6 sunt stocate ca valori text.
VLOOKUP Necesită formatul numărului exact
De exemplu, uitați-vă la datele de mai jos pentru a aplica funcția VLOOKUP.
Din Tabelul 1 trebuie să aplicăm funcția VLOOKUP pentru a obține coloana de venituri care să rezulte în Tabelul 2.
Deci, aplicați funcția VLOOKUP din tabelul 2.
Obținem următorul rezultat.
Rezultatul funcției VLOOKUP în celulele E6 și E7 arată # N / A !.
Să ne uităm la valorile căutării.
Acest lucru se întâmplă de obicei în numărul de valori de căutare, motivul principal ar trebui să fie formatul numerelor din ambele tabele nu sunt aceleași. Deci, în astfel de cazuri, trebuie să identificăm în ce numere de tabel sunt stocate ca text.
Aplicați funcția ISNUMBER pentru a identifica valori nenumerice.
După cum putem vedea ISNUMBER a identificat valoarea nenumerică în Tabelul 2.
Când datele din tabelul principal sunt corecte și numerele tabelului de rezultate sunt stocate ca text, trebuie să convertim mai întâi numerele formatate în text la valori numerice și apoi să aplicăm VLOOKUP. Există mai multe moduri în care putem face acest lucru, mai jos sunt metode.
Metoda 1: convertiți numerele formatate de text în valori numerice prin Paste Special
Mai întâi, introduceți numărul 1 în oricare dintre celulele din foaia de lucru și copiați acea celulă.
Acum selectați valorile ID-ului de ieșire din tabelul 2 și deschideți caseta de dialog Paste Special.
Pentru a deschide lipiți caseta de dialog specială, apăsați ALT + E + S.
Primim următoarea casetă de dialog.
În fereastra specială de lipire selectați „multiplicați” ca opțiune.
Apăsați ok, va converti toate numerele formatate de text în valori numerice, iar VLOOKUP va prelua automat datele din Tabelul 1.
Metoda 2: Conversia utilizând funcția VALUE
Funcția VALUE este utilizată pentru a converti numerele formatate de text în valori numerice. După cum putem vedea, VLOOKUP-ul nostru nu a preluat datele din cauza formatului valorii de căutare.
Pentru a depăși această problemă, care aplică funcția de căutare, trebuie să includem funcția VALUE.
Uită-te la formula de mai sus, am inclus funcția de căutare cu funcția VALUE. Deoarece am aplicat funcția VALUE în funcția VLOOKUP, aceasta va converti mai întâi valorile nenumerice în valori numerice, apoi VLOOKUP le tratează doar ca valori numerice.
Metoda 3: Ce se întâmplă dacă numerele sunt stocate ca text în tabelul principal
Am văzut cum să convertim valorile textului în cifre în tabelul de rezultate, dar ceea ce este numerele sunt stocate ca valori text în tabelul principal.
După cum putem în imaginea de mai sus din tabelul principal (Tabelul 1), valorile sunt stocate ca text. În astfel de cazuri, trebuie să atașăm funcția TEXT pentru valoarea de căutare în funcția VLOOKUP.
Problema aici este că funcția TEX convertește chiar valorile numerice în valori text și astfel unele dintre valorile stocate ca valori numerice nu vor funcționa în această funcție. Pentru aceasta, trebuie să includem condiția IFERROR în excel.
Condiția IFERROR testează dacă valoarea de căutare este numerică sau nu, dacă este numerică vom aplica LOOKUP normal sau altfel vom aplica funcția TEXT în excel.
Astfel, avem nevoie de cunoștințe extinse despre formule pentru a lucra cu VLOOKUP la nivel avansat și în diferite scenarii.
Lucruri de amintit
- Funcția TRIM convertește, de asemenea, valorile textului în valori numerice.
- În primul rând, trebuie să testăm în ce numere de tabel sunt stocate ca text.
- ISNUMBER returnează TRUE dacă valoarea celulei selectate este numărul sau altfel va reveni FALSE.