Много хора се опитват да използват Excel като база данни. Въпреки че може да работи като база данни, някои от задачите, които биха били много лесни в програма за бази данни, са доста сложни в Excel. Една от тези задачи е съвпадение на два списъка въз основа на общо поле; това може лесно да се постигне с помощта на Excel VLOOKUP. Ще намерите функцията VLOOKUP за изключително полезна, така че вижте пример за това кога и как да използвате тази функция по-долу.
Кажете, че вашата туристическа агенция ви изпраща отчет за края на месеца за всички места, които вашите служители са пътували. Докладът използва летищни кодове вместо имена на градове. Би било полезно, ако можете лесно да въведете истинското име на града, вместо само кода.
В интернет намирате и импортирате списък, показващ името на града за всеки код на летището.
Но как да получите тази информация за всеки запис в отчета?
- Използвайте функцията VLOOKUP. VLOOKUP означава „Вертикално търсене“. Може да се използва по всяко време, когато имате списък с данни с ключовото поле в най-лявата колона.
- Започнете да въвеждате функцията
=VLOOKUP(,. Въведете Ctrl + A, за да получите помощ за функцията. - VLOOKUP се нуждае от четири параметъра. Първо е градският код в оригиналния доклад. В този пример това ще бъде клетка D4
- Следващият параметър е диапазонът с вашата справочна таблица. Маркирайте обхвата. Не забравяйте да използвате F4, за да направите обхвата абсолютен. (Абсолютната препратка има знак за долар както пред номера на колоната, така и пред номера на реда. Когато формулата се копира, препратката ще продължи да сочи към I3: J351.
- Третият параметър казва на Excel в коя колона се намира името на града. В диапазона от I3: J351 името на града е в колона 2. Въведете 2 за този параметър.
- Четвъртият параметър казва на Excel дали съвпадението „затваряне“ е наред. В този случай не е, така че въведете False.
- Щракнете върху OK, за да попълните формулата. Плъзнете дръжката за попълване, за да копирате формулата надолу.
- Тъй като сте въвели внимателно абсолютни формули, можете да копирате колона E в колона D, за да получите града на местоназначението. В този случай всички заминавания са от международното летище Пиърсън в Торонто.
Въпреки че този пример се получи перфектно, когато зрителите използват VLOOKUP, това обикновено означава, че те съвпадат в списъци, дошли от различни източници. Когато списъците идват от различни източници, винаги може да има фини разлики, които правят списъците трудно съвпадащи. Ето три примера за това какво може да се обърка и как да ги коригирате.
-
Единият списък има тирета, а другият няма. Използвайте
=SUBSTITUTE()функцията, за да премахнете тиретата. Първият път, когато опитате VLOOKUP, ще получите N / A грешки.За да премахнете тиретата с формула, използвайте формулата ЗАМЕСТИТЕЛ. Използвайте 3 аргумента. Първият аргумент е клетката, съдържаща стойността. Следващият аргумент е текстът, който искате да промените. Последният аргумент е заместващият текст. В този случай искате да промените тиретата на нищо, така че формулата е
=SUBSTITUTE(A4,"-","").Можете да увиете тази функция във VLOOKUP, за да получите описанието.
-
Това е фино, но много често. Един списък има празно празно място след записа. Използвайте = TRIM (), за да премахнете излишните интервали. Когато първоначално въведете формулата, ще откриете, че всички отговори са N / A грешки. Със сигурност знаете, че стойностите са в списъка и всичко изглежда добре с формулата.
Едно стандартно нещо, което трябва да проверите, е да се преместите в клетката със справочната стойност. Натиснете F2, за да поставите клетката в режим на редактиране. Веднъж в режим на редактиране, можете да видите, че курсорът се намира на едно разстояние от последната буква. Това показва, че в записа има задно пространство.
За да разрешите проблема, използвайте функцията TRIM.
=TRIM(D4)ще премахне водещите интервали, последващите интервали и ще замени всички вътрешни двойни интервали с единично интервал. В този случай TRIM работи перфектно за премахване на крайното пространство.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)е формулата. -
Споменах бонус съвет в бележките на шоуто: как да замените резултата # N / A за липсващи стойности с празно. Ако вашата справочна стойност не е в справочната таблица, VLOOKUP ще върне N / A грешка.
Тази формула използва
=ISNA()функцията, за да открие дали резултатът от формулата е грешка N / A. Ако получите грешката, 2-рият аргумент във функцията IF ще каже на Excel да постави текста, който желаете.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP ви позволява да спестите време при съвпадение на списъци с данни. Отделете време, за да научите основното използване и ще можете да правите далеч по-мощни задачи в Excel.
