Excel VLOOKUP е мощен, но няма да работи, когато имате конкретни ситуации. Днес погледнете как да отстранявате VLOOKUP.
VLOOKUP е любимата ми функция в Excel. Ако можете да направите VLOOKUP, вие сте в състояние да разрешите много проблеми в Excel. Но има неща, които могат да задействат VLOOKUP. Тази тема говори за няколко от тях.
Но първо, основите на VLOOKUP на обикновен английски език.
Данните в A: C идват от ИТ отдела. Поискахте продажби по артикул и дата. Дадоха ти номер на артикул. Трябва ви описание на артикула. Вместо да чакате ИТ отделът да стартира отново данните, ще намерите таблицата, показана в колона F: G.
Искате VLOOKUP да намери елемента в A2, докато търси в първата колона на таблицата в $ F $ 3: $ G $ 30. Когато VLOOKUP намери съвпадението във F7, искате VLOOKUP да върне описанието, намерено във втората колона на таблицата. Всеки VLOOKUP, който търси точно съвпадение, трябва да завърши с False (или нула, което е еквивалентно на False). Формулата по-долу е настроена правилно.
Забележете, че използвате F4, за да добавите знаци от четири долара към адреса за справочната таблица. Докато копирате формулата надолу в колона D, ви е необходим адресът за справочната таблица да остане постоянен. Има две често срещани алтернативи: Можете да посочите цели колони F: G като справочна таблица. Или можете да назовете F3: G30 с име като ItemTable. Ако използвате =VLOOKUP(A2,ItemTable,2,False), посоченият диапазон действа като абсолютна препратка.
Всеки път, когато правите куп VLOOKUP, трябва да сортирате колоната на VLOOKUP. Сортирайте ZA и всички грешки # N / A ще се появят на върха. В този случай има такъв. Елемент BG33-9 липсва в справочната таблица. Може би е печатна грешка. Може би това е чисто нов артикул. Ако е нов, вмъкнете нов ред някъде в средата на вашата справочна таблица и добавете новия елемент.
Съвсем нормално е да има няколко грешки # N / A. Но на фигурата по-долу точно същата формула не връща нищо друго освен # N / A. Когато това се случи, виждам дали мога да разреша първата VLOOKUP. Търсите BG33-8, намерен в A2. Започнете да пътувате надолу през първата колона на справочната таблица. Както можете да видите, съвпадащата стойност явно е във F10. Защо можете да видите това, но Excel не може да го види?
Отидете до всяка клетка и натиснете клавиша F2. Ето F10. Имайте предвид, че курсорът за вмъкване се появява веднага след 8.
Ето клетка А2 в режим Редактиране. Курсорът за вмъкване е на няколко интервала от 8. Това е знак, че в даден момент тези данни са били съхранени в стар набор от данни COBOL. Обратно в COBOL, ако полето Елемент е дефинирано като 10 знака и сте въвели само 6 знака, COBOL ще го запълни с 4 допълнителни интервала.
Решението? Вместо да търсите A2, потърсете TRIM(A2).
Функцията TRIM () премахва водещите и крайните интервали. Ако имате няколко интервала между думите, TRIM ще ги преобразува в едно интервал. На фигурата по-долу има интервали преди и след двете имена в А1. =TRIM(A1)премахва всички, освен едно пространство в A3.
Между другото, какво би станало, ако проблемът беше пропускане на интервали в колона F вместо колона A? Добавете колона от функции TRIM () към E, сочейки към колона F. Копирайте ги и ги поставете като стойности във F, за да накарате справките да започнат да работят отново.
Другата много често срещана причина, поради която VLOOKUP няма да работи, е показана тук. Колона F има реални числа. Колона А има текст, който прилича на числа.
Изберете цялата колона A. Натиснете alt = "" + D, E, F. Това прави текст по подразбиране в колони и ще преобразува всички текстови числа в реални числа. Търсене отново започва да работи.
Гледам видео
- VLOOKUP решава много проблеми
- Често срещани проблеми с VLOOKUP:
- Ако VLOOKUP започне да работи, но # N / A стане по-забележим: забравена $ в таблицата за търсене
- Няколко # N / A: елементи, които липсват в таблицата
- Нито една от работата на VLOOKUP не работи: проверете за крайни интервали
- Премахнете задните интервали с TRIM
- Числа и числа, съхранявани като текст
- Изберете двете колони и използвайте alt = "" + DEF
- Епизодът включва шега, която както на счетоводителите, така и на ИТ специалистите им е смешна, но по различни причини
Видео транскрипция
Научете Excel от подкаст, епизод 2027 - Отстраняване на неизправности VLOOKUP!
Добре, като подкастирате цялата тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
VLOOKUP е любимата ми функция в целия Excel и винаги разказвам тази шега в един от семинарите си и се разсмива дали сте ИТ или не ИТ човек. Винаги казвам „Ами вижте, ние имаме тези данни тук вляво и мога да ги разгледам и да кажа, че данните са дошли от ИТ отдела, защото това е точно това, което поисках, но не всъщност това, от което се нуждаех. Поисках дата и количество на артикула и те ми дадоха дата и количество на артикула, но не си направиха труда да ми дадат описание, нали? “ И счетоводителите винаги се смеят на това, защото това им се случва непрекъснато, а момчетата от ИТ са като „Е, дадох ви това, което поискахте, не съм виновен!“ Така че и двамата смятат, че е смешно по различни причини, така че, знаете ли, а ИТ човекът е зает, той не може да се върне към пренаписване на заявката,така че ние трябва да направим нещо, за да спасим това сами.
И така, тази малка таблица, която копирах от някъде другаде в компютъра си, на обикновен английски, това, което VLOOKUP прави е, казва „Хей, имаме номер на артикул W25-6“. Тук имаме таблица, искам да премина през първата колона на таблицата, докато намеря номера на елемента, и след това да върна нещо от този ред. Добре, в този случай това, което искам, е 2-рата колона от този ред. И тогава в края на всеки VLOOKUP трябва да поставим или FALSE, или 0. Сега точно тук, след като избера диапазона, ще натисна клавиша F4 и след това искам 2-ра колона и след това FALSE за точно съвпада. Никога не избирайте приблизително съвпадение, никога, никога! Това не е приблизително съвпадение, това е много специално нещо, не работи постоянно. Ако искате да преизчислявате номерата си в Комисията за ценни книжа и борси, не забравяйте да използвате,ИСТИНА или просто оставете, FALSE изключено. Но всеки VLOOKUP, който някога създавате, трябва да завършва с FALSE или 0, 0 е по-кратък от FALSE, трябва да поставите FALSE там, но 0 е същото като FALSE.
Добре сега, отстраняване на неизправности, първо нещо, когато направите цял куп VLOOKUPs, е съвсем нормално да имате няколко # N / As, нали? И аз винаги намирам # N / A, като отида Data, ZA, което води до # N / As на върха, точно там, BG33-9, или това е печатна грешка, или е чисто нов елемент, добре, и имаме за да го разбера. Така че тук вдясно имам новите данни, ще ги изрежа и след това Alt + IED, вмъкнете тези клетки в средата, не е задължително да са азбучни, тази таблица не трябва да се сортира. Когато използвате версията FALSE, таблицата не е - можете просто да я поставите където пожелаете, не поставих в края, защото не трябваше да пренаписвам формулата, просто искам формулата да работа. Добре, така че няколко N / A, изключително, изключително нормални, но вижте това.
Когато започнете с отговори, които работят, но след това # N / A започват да се появяват леко често и след това в крайна сметка се появяват докрай, това е сигурен знак, че не сте заключили препратката към таблицата. Добре, вижте, така че тук таблицата се движи, докато копирам формулата надолу, нали, и така имам късмет да ударя няколко, които бяха в края на списъка. Но в крайна сметка стигам до точката, в която се гледа тук в напълно празни клетки и разбира се нищо не се намира. Добре, така че това е първото нещо, получавате двойка, която работи, няколко # N / A, още няколко, които работят и след това всички # N / A са останалата част от пътя - сигурен знак, че не сте поставили $ ин.
Просто се върнете, F2 за режим Редактиране, изберете дебелото черво, натиснете F4, което поставя всички $, щракнете двукратно, за да го свалите и нещата започват да работят отново, добре. Следващата, точно същата формула, формулата е перфектна, BG33-8 вижте, не получаваме нищо от това правилно. Добре, аз отивам да гледам, BG33-8, хей, ето го, точно там е, в червено е, трябваше да го видя! Така че стигнах до този от дясната страна, натиснах F2 и наблюдавах мигащата точка на вмъкване и видях, че е точно след 8, така, и след това дойдох тук и натиснах F2, има някои последващи пространства там. Това е много, много често в дните на COBOL, те биха казали „Знаете ли, вижте, ще ви дадем 10 интервала за номер на артикул и ако не напишете всички 10 интервала, те ще запълнят пространствата . " И това е много често,и чудесното решение тук е да се отървете от тези водещи и крайни интервали с функцията TRIM. Вместо A2 използвайте TRIM (A2), щракнете двукратно, за да го свалите, добре, все още BG33-9 се връща в другата таблица.
Добре, само за да разберете как работи TRIM, затова написах куп интервали, Джон, куп интервали, Durran и куп интервали, а след това обединих * преди и след, за да можете да видите как изглежда . Когато поискам TRIM (P4), ние се отърваваме от всички водещи пространства, всички последващи пространства и след това множеството вътрешни пространства се намаляват до едно пространство. Добре, така че можете да видите, някак си визуализирайте, че те се отърват от водещите и задните пространства, всички удвоени пространства в средата се превръщат в едно такова пространство. Така че TRIM, страхотен, страхотен инструмент във вашия арсенал, добре, ето още един наистина често срещан.
Ако това не са крайните интервали, тогава най-често срещаното нещо, което съм виждал, е къде тук имаме истински числа и тук имаме числа, съхранявани като текст. И VLOOKUP няма да види това като съвпадение, въпреки че 4399, това е число, това е текст, не работи. Най-бързият начин да конвертирате колона от текст в цифри, изберете колоната, 3 букви в последователност, alt = "" DEF и изведнъж нашите VLOOKUP-и започват да работят отново, страхотен, страхотен съвет от преди около 1500 подкасти. Добре, така че това са най-често срещаните проблеми с VLOOKUP, или сте забравили $, или имате последващи интервали, или имате числа и числа, съхранени като текст. Всички тези съвети са в тази книга „MrExcel XL“, щракнете върху „i“ в горния десен ъгъл, можете да купите книгата.
Добре, бързо обобщение: VLOOKUP решава много проблеми, ако VLOOKUP започне да работи, но # N / A! става по-видно, сте забравили да сложите $ в таблицата за търсене. Ако има няколко # N / A, това са само елементи, които липсват в таблицата. Ако нито един от VLOOKUP не работи и това е текст, проверете за последващи интервали, можете да използвате функцията TRIM. Ако имате числа и числа, съхранявани като текст, изберете някоя от колоните, тази, която има текста, след това направете alt = "" DEF трябва да се връща към всички тези.
Добре добре, хей, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2027.xlsx
