Всички тайни на функцията VLOOKUP на Excel за намиране на данни в таблица и извличането им в друга

2024 Автор: Malcolm Clapton | [email protected]. Последно модифициран: 2023-12-17 03:47

След като прочетете статията, вие не само ще научите как да намерите данни в електронна таблица на Excel и да ги извлечете в друга, но и техники, които могат да се използват във връзка с функцията VLOOKUP.

Когато работите в Excel, много често има нужда да намерите данни в една таблица и да ги извлечете в друга. Ако все още не знаете как да направите това, след като прочетете статията, не само ще научите как да направите това, но и ще разберете при какви условия можете да изстискате максималната производителност от системата. Разглеждат се повечето от много ефективните техники, които трябва да се използват във връзка с функцията VLOOKUP.

Дори и да използвате функцията VLOOKUP от години, тогава с голяма степен на вероятност тази статия ще ви бъде полезна и няма да ви остави безразлични. Например, като ИТ специалист, а след това и ръководител в ИТ, използвам VLOOKUP от 15 години, но успях да се справя с всички нюанси едва сега, когато започнах да обучавам хората на Excel на професионална основа.

VLOOKUP е съкращение за v вертикална NS инспекция. По същия начин, VLOOKUP - Вертикално LOOKUP. Самото име на функцията ни подсказва, че тя търси в редовете на таблицата (вертикално - итериране по редовете и фиксиране на колоната), а не в колоните (хоризонтално - итериране по колоните и фиксиране на реда). Трябва да се отбележи, че VLOOKUP има сестра - грозно патенце, което никога няма да стане лебед - това е функцията HLOOKUP. HLOOKUP, за разлика от VLOOKUP, извършва хоризонтални търсения, но концепцията на Excel (и всъщност концепцията за организация на данните) предполага, че вашите таблици имат по-малко колони и много повече редове. Ето защо трябва да търсим по редове много пъти по-често, отколкото по колони. Ако използвате функцията HLO твърде често в Excel, тогава е вероятно да не сте разбрали нещо в този живот.

Синтаксис

Функцията VLOOKUP има четири параметъра:

= VLOOKUP (;; [;]), тук:

- желаната стойност (рядко) или препратка към клетка, съдържаща желаната стойност (в по-голямата част от случаите);

- препратка към диапазон от клетки (двуизмерен масив), в ПЪРВАТА (!) колона на които ще се търси стойността на параметъра;

- номер на колона в диапазона, от който ще бъде върната стойността;

- това е много важен параметър, който отговаря на въпроса дали първата колона от диапазона е сортирана във възходящ ред. Ако масивът е сортиран, ние задаваме стойността TRUE или 1, в противен случай - FALSE или 0. Ако този параметър е пропуснат, той е по подразбиране 1.

Обзалагам се, че много от тези, които познават функцията VLOOKUP като нестабилна, след като прочетат описанието на четвъртия параметър, може да се почувстват неудобно, тъй като са свикнали да го виждат в малко по-различна форма: обикновено те говорят за точно съвпадение, когато търсене (FALSE или 0) или сканиране на диапазон (TRUE или 1).

Сега трябва да се напрягате и да прочетете следващия параграф няколко пъти, докато усетите смисъла на казаното до края. Там всяка дума е важна. Примерите ще ви помогнат да го разберете.

Как точно работи формулата VLOOKUP?

• Формула тип I. Ако последният параметър е пропуснат или е посочен равен на 1, тогава VLOOKUP приема, че първата колона е сортирана във възходящ ред, така че търсенето спира на реда, който непосредствено предхожда реда, съдържащ стойността, по-голяма от желаната … Ако не бъде намерен такъв низ, се връща последният ред от диапазона.

  

• Формула II. Ако последният параметър е посочен като 0, тогава VLOOKUP сканира първата колона от масива последователно и незабавно спира търсенето, когато бъде намерено първото точно съвпадение с параметъра, в противен случай кодът за грешка # N / A (# N / A) се връща.

  

Работни процеси с формули

VPR тип I

VPR тип II

Следствия за формули от вида I

1. Формулите могат да се използват за разпределяне на стойности в диапазони.
2. Ако първата колона съдържа дублиращи се стойности и е сортирана правилно, тогава последният от редовете с дублиращи се стойности ще бъде върнат.
3. Ако търсите стойност, която очевидно е по-голяма от първата колона, която може да съдържа, тогава лесно можете да намерите последния ред на таблицата, който може да бъде доста ценен.
4. Този изглед ще върне грешката # N / A само ако не намери стойност, по-малка или равна на желаната.
5. Доста е трудно да се разбере, че формулата връща грешни стойности, ако вашият масив не е сортиран.

Следствия за формули от тип II

Ако желаната стойност се появи няколко пъти в първата колона на масива, тогава формулата ще избере първия ред за последващо извличане на данни.

производителност на VLOOKUP

Стигнахте до кулминацията на статията. Изглежда, добре, каква е разликата, ако задам нула или единица като последен параметър? По принцип всеки посочва, разбира се, нула, тъй като това е доста практично: не е нужно да се притеснявате за сортирането на първата колона от масива, можете веднага да видите дали стойността е намерена или не. Но ако имате няколко хиляди формули VLOOKUP на вашия лист, ще забележите, че VLOOKUP II е бавен. В същото време обикновено всеки започва да мисли:

• Имам нужда от по-мощен компютър;
• Имам нужда от по-бърза формула, например много хора знаят за INDEX + MATCH, която уж е по-бърза с мижавите 5-10%.

И малко хора мислят, че щом започнете да използвате VLOOKUP от тип I и гарантирате, че първата колона е сортирана по какъвто и да е начин, скоростта на VLOOKUP ще се увеличи 57 пъти. Пиша с думи - ПЕТДЕСЕТ И СЕДЕМ ПЪТИ! Не 57%, а 5700%. Проверих този факт доста надеждно.

Тайната на такава бърза работа се крие във факта, че върху сортиран масив може да се приложи изключително ефективен алгоритъм за търсене, който се нарича двоично търсене (метод на разполовяване, метод на дихотомия). Така че VLOOKUP от тип I го прилага, а VLOOKUP от тип II търси без никаква оптимизация. Същото важи и за функцията MATCH, която включва подобен параметър, и функцията LOOKUP, която работи само върху сортирани масиви и е включена в Excel за съвместимост с Lotus 1-2-3.

Недостатъци на формулата

Недостатъците на VLOOKUP са очевидни: първо, той търси само в първата колона на посочения масив и второ, само вдясно от тази колона. И както разбирате, може да се случи колоната, съдържаща необходимата информация, да бъде вляво от колоната, в която ще търсим. Вече споменатата комбинация от формули INDEX + MATCH е лишена от този недостатък, което я прави най-гъвкавото решение за извличане на данни от таблици в сравнение с VLOOKUP (VLOOKUP).

Някои аспекти на прилагането на формулата в реалния живот

Търсене на диапазон

Класическа илюстрация на търсене на диапазон е задачата за определяне на отстъпка по размер на поръчката.

Търсене на текстови низове

Разбира се, VLOOKUP търси не само числа, но и текст. Трябва да се има предвид, че формулата не прави разлика между регистрите на знаците. Ако използвате заместващи знаци, можете да организирате размито търсене. Има два заместващи знака: "?" - замества всеки един знак в текстов низ, "*" - заменя произволен брой всякакви знаци.

Бойни пространства

Често се повдига въпросът как да се реши проблемът с допълнителните пространства при търсене. Ако справочната таблица все още може да бъде изчистена от тях, тогава първият параметър на формулата VLOOKUP не винаги зависи от вас. Следователно, ако съществува риск от запушване на клетките с допълнителни интервали, тогава можете да използвате функцията TRIM, за да го изчистите.

Различен формат на данните

Ако първият параметър на функцията VLOOKUP се отнася до клетка, която съдържа число, но която се съхранява в клетката като текст, а първата колона на масива съдържа числа в правилния формат, тогава търсенето ще бъде неуспешно. Възможна е и обратната ситуация. Проблемът може лесно да бъде решен чрез превод на параметър 1 в необходимия формат:

= VLOOKUP (−− D7; Продукти! $ A $ 2: $ C $ 5; 3; 0) - ако D7 съдържа текст и таблицата съдържа числа;

= VLOOKUP (D7 & ""); Продукти!$ A $ 2: $ C $ 5; 3; 0) - и обратно.

Между другото, можете да преведете текст в число по няколко начина наведнъж, изберете:

• Двойно отрицание -D7.
• Умножение по едно D7 * 1.
• Нулево събиране D7 + 0.
• Повишаване на първа степен D7 ^ 1.

Преобразуването на число в текст се извършва чрез конкатенация с празен низ, което принуждава Excel да преобразува типа данни.

Как да потиснем # N / A

Това е много удобно да се направи с функцията IFERROR.

Например: = АКО ГРЕШКА (VLOOKUP (D7; Продукти! $ A $ 2: $ C $ 5; 3; 0); "").

Ако VLOOKUP върне кода за грешка # N / A, тогава IFERROR ще го прихване и ще замени параметър 2 (в този случай празен низ), а ако не възникне грешка, тогава тази функция ще се преструва, че изобщо не съществува, но има само VLOOKUP, който връща нормален резултат.

масив

Често забравят да направят препратката на масива абсолютна и при разтягане масивът "изплува". Не забравяйте да използвате $ A $ 2: $ C $ 5 вместо A2: C5.

Добра идея е да поставите референтния масив на отделен лист от работната книга. Не попада под краката и ще бъде по-безопасно.

Още по-добра идея би била да декларирате този масив като именуван диапазон.

Много потребители, когато посочват масив, използват конструкция като A: C, посочвайки цели колони. Този подход има право да съществува, тъй като сте спасени от необходимостта да следите факта, че вашият масив включва всички необходими низове. Ако добавите редове към листа с оригиналния масив, тогава диапазонът, определен като A: C, не трябва да се коригира. Разбира се, тази синтактична конструкция принуждава Excel да върши малко повече работа от точното уточняване на диапазона, но тези допълнителни разходи могат да бъдат пренебрегнати. Говорим за стотни от секундата.

Е, на ръба на гениалността - да подредите масива във формата.

Използване на функцията COLUMN, за да посочите колоната за извличане

Ако таблицата, в която извличате данни с помощта на VLOOKUP, има същата структура като таблицата за търсене, но просто съдържа по-малко редове, тогава можете да използвате функцията COLUMN () във VLOOKUP, за да изчислите автоматично броя на колоните, които трябва да бъдат извлечени. В този случай всички формули на VLOOKUP ще бъдат еднакви (коригирани за първия параметър, който се променя автоматично)! Имайте предвид, че първият параметър има абсолютна координата на колоната.

Създаване на съставен ключ с & "|" &

Ако се наложи търсене по няколко колони едновременно, тогава е необходимо да се направи съставен ключ за търсене. Ако връщаната стойност не беше текстова (както е в случая с полето "Код"), а числова, тогава по-удобната формула SUMIFS би била подходяща за това и съставният ключ на колоната изобщо не би се изисквал.

Това е първата ми статия за Lifehacker. Ако ви хареса, каня ви да го посетите, а също така с удоволствие прочетете в коментарите за вашите тайни за използване на функцията VLOOKUP и други подобни. Благодаря.:)