VLOOKUP с текст
Един от основните критерии за работа с VLOOKUP е стойността „lookup“ трябва да бъде еднаква в клетката с резултати и също в основната таблица с данни, но понякога, въпреки че стойността за справка изглежда еднакво и в двете клетки, все пак получаваме стойност на грешка като # N / A !. Това е така, защото форматът на стойността на vlookup трябва да е различен във всяка клетка. Така че в тази статия ще ви покажем как да работите с текстовия формат на стойността на Vlookup.
Пример за VLOOKUP за текст
По-долу е даден пример за Excel VLOOKUP за текст.
Можете да изтеглите този VLOOKUP за Шаблон за текстов Excel тук - VLOOKUP за Шаблон за текстов ExcelПонякога числата се съхраняват като текстови стойности и в такива случаи не можем да ги третираме като числа поради функционалността на Excel. Например погледнете данните по-долу.
В горните данни всичко изглежда като числа, но когато го сумираме, трябва да получим обща стойност от 3712054, но когато приложим функцията SUM excel, получаваме долното число.
Причината за това е, че някои числа се съхраняват като текстови стойности. И така, как да идентифицираме текстовите стойности?
Можем да идентифицираме текстови стойности, като използваме функцията ISNUMBER excel. Функцията ISNUMBER ще върне TRUE, ако избраната стойност на клетката е число, или ще върне FALSE.
Така че в клетка B5 и B6 получихме резултата като FALSE, което означава, че номерата на клетките A5 и A6 се съхраняват като текстови стойности.
VLOOKUP Изисква формат на точен номер
Например, погледнете данните по-долу, за да приложите функцията VLOOKUP.
От таблица 1 трябва да приложим функцията VLOOKUP, за да получим колоната с приходите в резултат на таблица 2.
Така че, приложете функцията VLOOKUP в таблица 2.
Получаваме следния резултат.
Резултатът от функцията VLOOKUP в клетки E6 и E7 показва # N / A !.
Нека да разгледаме справочните стойности.
Това обикновено се случва при броя на справочните стойности, като основната причина трябва да е форматът на числата в двете таблици да не е еднакъв. Така че в такива случаи трябва да определим в кои номера на таблици се съхраняват като текст.
Приложете функцията ISNUMBER за идентифициране на нечислови стойности.
Както можем да видим ISNUMBER идентифицира нечислови стойности в таблица 2.
Когато данните на основната таблица са правилни и номерата на таблиците с резултати се съхраняват като текст, първо трябва да преобразуваме форматираните в текст числа в числови стойности и след това да приложим VLOOKUP. Има няколко начина, по които можем да направим това, по-долу са дадени методи.
Метод 1: Преобразувайте форматирани в текст числа в числови стойности чрез Специално поставяне
Първо въведете номер 1 във всяка от клетките в работния лист и копирайте тази клетка.
Сега изберете стойности на ID на изхода в таблица 2 и отворете диалоговия прозорец Специално поставяне.
За да отворите поставяне на специален диалогов прозорец, натиснете ALT + E + S.
Получаваме следния диалогов прозорец.
В специалния прозорец за поставяне изберете „умножи“ като опция.
Натиснете OK, той ще преобразува всички форматирани в текст числа в числови стойности и VLOOKUP сега автоматично извлича данните от таблица 1.
Метод 2: Конвертиране чрез използване на функция VALUE
Функцията VALUE се използва за преобразуване на форматирани в текст числа в числови стойности. Както виждаме, нашият VLOOKUP не извлече данните поради формата на справочната стойност.
За да преодолеем този проблем, който прилага функцията за търсене, трябва да приложим функцията VALUE.
Вижте горната формула, приложих функцията за търсене с функция VALUE. Тъй като сме приложили функция VALUE във функцията VLOOKUP, тя първо ще преобразува нечисловите стойности първо в числови стойности, след което VLOOKUP ги третира само като числови стойности.
Метод 3: Ами ако числата се съхраняват като текст в основната таблица
Видяхме как да преобразуваме текстови стойности в числа в таблицата с резултати, но какво е число се съхраняват като текстови стойности в самата основна таблица.
Както можем в горното изображение в основната таблица (Таблица 1), стойностите се съхраняват като текст. В такива случаи трябва да приложим функцията TEXT за търсещата стойност във функцията VLOOKUP.
Проблемът тук е, че функцията TEX преобразува дори числовите стойности в текстови стойности и така някои от стойностите, които се съхраняват като числови стойности, няма да работят в тази функция. За това трябва да приложим условието IFERROR в Excel.
Състоянието IFERROR тества дали стойността на справка е числова или не, ако е числова, ще приложим нормално LOOKUP, или ще приложим функцията TEXT в Excel.
Така че по този начин се нуждаем от обширни познания за формулите, за да работим с VLOOKUP на напреднало ниво и при различни сценарии.
Неща за запомняне
- Функцията TRIM също преобразува текстовите стойности в числови стойности.
- Първо, трябва да тестваме в кои номера на таблици се съхраняват като текст.
- ISNUMBER връща TRUE, ако избраната стойност на клетката е число, или ще върне FALSE.