VLOOKUP z besedilom
Eno od osnovnih meril za delo z VLOOKUP-om je, da mora biti vrednost iskanja enaka v celici z rezultati in tudi v glavni podatkovni tabeli, včasih pa čeprav je vrednost iskanja enaka v obeh celicah, kljub temu dobimo vrednost napake kot # N / A !. To je zato, ker mora biti oblika vrednosti vlookup v obeh celicah drugačna. V tem članku vam bomo pokazali, kako delati z besedilno obliko vrednosti Vlookup.
Primer VLOOKUP-a za besedilo
Spodaj je primer excel VLOOKUP za besedilo.
To predlogo VLOOKUP for Text Excel lahko prenesete tukaj - VLOOKUP za besedilno predlogo ExcelVčasih so številke shranjene kot besedilne vrednosti in jih v takih primerih ne moremo obravnavati kot številke zaradi funkcije excela. Na primer, poglejte spodnje podatke.
V zgornjih podatkih je vse videti kot številke, ko pa seštejemo, bi morali dobiti skupno vrednost 3712054, ko pa uporabimo funkcijo SUM excel, dobimo spodnjo številko.
Razlog za to je, ker so nekatere številke shranjene kot besedilne vrednosti. Torej, kako prepoznamo besedilne vrednosti?
Besedilne vrednosti lahko prepoznamo s pomočjo funkcije ISNUMBER excel. Funkcija ISNUMBER bo vrnila TRUE, če je izbrana vrednost celice številka, ali pa vrne FALSE.
Torej smo v celicah B5 in B6 dobili rezultat FALSE, kar pomeni, da so številke celic A5 in A6 shranjene kot besedilne vrednosti.
VLOOKUP zahteva natančno obliko številke
Na primer, za uporabo funkcije VLOOKUP si oglejte spodnje podatke.
V tabeli 1 moramo uporabiti funkcijo VLOOKUP, da dobimo stolpec s prihodki v tabeli 2.
Torej uporabite funkcijo VLOOKUP v tabeli 2.
Dobili smo naslednji rezultat.
Rezultat funkcije VLOOKUP v celicah E6 in E7 kaže # N / A !.
Oglejmo si iskalne vrednosti.
To se običajno zgodi pri številu iskalnih vrednosti, glavni razlog pa mora biti oblika številk v obeh tabelah, ki niso enake. V takih primerih moramo torej določiti, v katerih številkah tabel so shranjene kot besedilo.
Uporabite funkcijo ISNUMBER za prepoznavanje neštevilskih vrednosti.
Kot lahko vidimo, da je v tabeli 2 ugotovljeno številčno vrednost ISNUMBER.
Ko so podatki glavne tabele pravilni in so številke tabel rezultatov shranjene kot besedilo, moramo najprej pretvoriti besedilno oblikovane številke v številčne vrednosti in nato uporabiti VLOOKUP. To lahko storimo na več načinov, spodaj so navedene metode.
1. način: s posebnim lepljenjem pretvorite številčno oblikovane številke v številčne vrednosti
Najprej v katero koli celico na delovnem listu vnesite številko 1 in jo kopirajte.
Zdaj izberite vrednosti ID vtičnice v tabeli 2 in odprite pogovorno okno Paste Special.
Če želite odpreti prilepite posebno pogovorno okno, pritisnite ALT + E + S.
Dobili smo naslednje pogovorno okno.
V posebnem oknu za lepljenje kot možnost izberite »pomnoži«.
Pritisnite ok, vse besedilno oblikovane številke bodo pretvorjene v številske vrednosti, VLOOKUP pa samodejno pridobi podatke iz tabele 1.
2. način: Pretvorite s funkcijo VALUE
Funkcija VALUE se uporablja za pretvorbo besedilno oblikovanih števil v številčne vrednosti. Kot lahko vidimo, da naš VLOOKUP ni prinesel podatkov zaradi oblike iskalne vrednosti.
Da bi rešili to težavo, ki uporablja funkcijo iskanja, moramo priložiti funkcijo VALUE.
Poglejte zgornjo formulo, funkcijo iskanja sem zaprl s funkcijo VALUE. Ker smo v funkciji VLOOKUP uporabili funkcijo VALUE, bo najprej numerične vrednosti najprej pretvoril v numerične vrednosti, nato pa jih VLOOKUP obravnava le kot številčne vrednosti.
3. način: Kaj, če so številke shranjene kot besedilo v glavni tabeli
Videli smo, kako pretvoriti besedilne vrednosti v številke v tabeli rezultatov, toda številke so shranjene kot besedilne vrednosti v glavni tabeli.
Kot lahko na zgornji sliki v glavni tabeli (tabela 1), se vrednosti shranijo kot besedilo. V takih primerih moramo funkciji TEXT priložiti funkcijo za iskanje v funkciji VLOOKUP.
Težava je v tem, da funkcija TEX pretvori celo številske vrednosti v besedilne vrednosti, zato nekatere vrednosti, ki so shranjene kot številčne vrednosti, v tej funkciji ne bodo delovale. Za to moramo pogoj IFERROR priložiti v excel.
Pogoj IFERROR preizkusi, ali je vrednost iskanja številčna ali ne, če je številčna, bomo uporabili običajni LOOKUP ali pa bomo uporabili funkcijo TEXT v excelu.
Takole tako potrebujemo obsežno znanje o formulah za delo z VLOOKUP-om na napredni ravni in v različnih scenarijih.
Stvari, ki si jih je treba zapomniti
- Funkcija TRIM pretvori tudi besedilne vrednosti v številčne vrednosti.
- Najprej moramo preizkusiti, v katerih številkah tabel so shranjene kot besedilo.
- ISNUMBER vrne TRUE, če je izbrana vrednost celice številka, sicer pa vrne FALSE.