VLOOKUP s tekstom
Jedan od osnovnih kriterija za rad s VLOOKUP-om je vrijednost "traženja" koja bi trebala biti ista u ćeliji rezultata i u glavnoj tablici podataka, ali ponekad iako vrijednost pregledavanja izgleda jednako u bilo kojoj ćeliji, ipak na kraju dobijemo vrijednost pogreške kao # N / A !. To je zato što se format vrijednosti vlookup mora razlikovati u bilo kojoj ćeliji. U ovom ćemo vam članku pokazati kako raditi s tekstualnim formatom vrijednosti Vlookup.
Primjer VLOOKUP-a za tekst
Ispod je primjer excel VLOOKUP-a za tekst.
Ponekad se brojevi pohranjuju kao tekstualne vrijednosti i u takvim slučajevima ih ne možemo tretirati kao brojeve zbog funkcionalnosti excela. Na primjer, pogledajte podatke u nastavku.
U gornjim podacima sve izgleda kao brojevi, ali kada to zbrojimo, trebali bismo dobiti ukupnu vrijednost 3712054, ali kada primijenimo funkciju SUM excel, dobit ćemo donji broj.
Razlog tome je što su neki brojevi pohranjeni kao tekstualne vrijednosti. Pa, kako prepoznati tekstualne vrijednosti?
Tekstualne vrijednosti možemo prepoznati pomoću funkcije ISNUMBER excel. Funkcija ISNUMBER vratit će TRUE ako je odabrana vrijednost ćelije broj ili će vratiti FALSE.
Dakle, u ćelijama B5 i B6 dobili smo rezultat FALSE što znači da se brojevi ćelija A5 i A6 pohranjuju kao tekstualne vrijednosti.
VLOOKUP zahtijeva točan format broja
Na primjer, pogledajte podatke u nastavku kako biste primijenili funkciju VLOOKUP.
Iz tablice 1 moramo primijeniti funkciju VLOOKUP da bi stupac prihoda rezultirao tablicom 2.
Dakle, primijenite funkciju VLOOKUP u tablici 2.
Dobivamo sljedeći rezultat.
Rezultat funkcije VLOOKUP u stanicama E6 i E7 pokazuje # N / A !.
Pogledajmo vrijednosti pretraživanja.
To se obično događa u broju traženih vrijednosti. Glavni razlog trebao bi biti format brojeva u obje tablice koji nisu isti. Dakle, u takvim slučajevima moramo prepoznati u kojoj se tablici brojevi pohranjuju kao tekst.
Primijenite funkciju ISNUMBER da biste identificirali numeričke vrijednosti.
Kao što vidimo, ISNUMBER je identificirao nenumeričku vrijednost u tablici 2.
Kada su podaci glavne tablice točni i brojevi tablice rezultata pohranjeni kao tekst, tada prvo moramo pretvoriti brojeve oblikovane u tekst u numeričke vrijednosti, a zatim primijeniti VLOOKUP. Postoji nekoliko načina na koje to možemo učiniti, u nastavku su navedene metode.
Metoda 1: Pretvorite brojeve oblikovane tekstom u numeričke vrijednosti pomoću Posebnog lijepljenja
Prvo unesite broj 1 u bilo koju od ćelija na radnom listu i kopirajte tu ćeliju.
Sada odaberite vrijednosti ID-a utičnice u tablici 2 i otvorite dijaloški okvir Paste Special.
Da biste otvorili zalijepite poseban dijaloški okvir pritisnite alt = "" + E + S.
Dobivamo sljedeći dijaloški okvir.
U paste, posebnom prozoru, odaberite "pomnoži" kao opciju.
Pritisnite ok, pretvorit će sve brojeve oblikovane u tekst u numeričke vrijednosti, a VLOOKUP sada automatski preuzima podatke iz tablice 1.
2. metoda: Pretvorba pomoću funkcije VALUE
Funkcija VALUE koristi se za pretvaranje brojeva oblikovanih tekstom u numeričke vrijednosti. Kao što vidimo, naš VLOOKUP nije dohvatio podatke zbog formata vrijednosti pretraživanja.
Da bismo prevladali ovaj problem, koji primjenjuje funkciju pretraživanja, moramo priložiti funkciju VALUE.
Pogledajte gornju formulu. Priložio sam funkciju pretraživanja funkcijom VALUE. Budući da smo funkciju VALUE primijenili unutar funkcije VLOOKUP, prvo će pretvoriti nenumeričke vrijednosti u numeričke vrijednosti, a zatim ih VLOOKUP tretira samo kao numeričke vrijednosti.
3. metoda: Što ako su brojevi pohranjeni kao tekst u glavnoj tablici
Vidjeli smo kako pretvoriti tekstualne vrijednosti u brojeve u tablici rezultata, ali koji su njegovi brojevi pohranjeni kao tekstualne vrijednosti u samoj glavnoj tablici.
Kao što možemo na gornjoj slici u glavnoj tablici (Tablica 1), same vrijednosti su pohranjene kao tekst. U takvim slučajevima trebamo priložiti funkciju TEXT za vrijednost pretraživanja u funkciji VLOOKUP.
Ovdje je problem TEX funkcija pretvara čak i numeričke vrijednosti u tekstualne vrijednosti, pa neke vrijednosti pohranjene kao numeričke vrijednosti neće raditi u ovoj funkciji. Za to trebamo priložiti uvjet IFERROR u excel.
Uvjet IFERROR provjerava je li vrijednost pretraživanja numerička ili nije. Ako je numerički, primijenit ćemo uobičajeni LOOKUP ili ćemo primijeniti funkciju TEXT u excelu.
Dakle, ovako, treba nam opsežno znanje o formulama za rad s VLOOKUP-om na naprednoj razini i u različitim scenarijima.
Stvari koje treba zapamtiti
- Funkcija TRIM također pretvara tekstualne vrijednosti u numeričke vrijednosti.
- Prvo moramo testirati u kojoj se tablici brojevi pohranjuju kao tekst.
- ISNUMBER vraća TRUE ako je odabrana vrijednost ćelije broj ili će vratiti FALSE.
