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

Функцията VLOOKUP в Excel плаши много хора, защото има много параметри и има множество начини да се използва. В тази статия ще научите всички начини, по които можете да използвате VLOOKUP в Excel и защо функцията е толкова мощна.

Параметри на VLOOKUP в Excel^{(VLOOKUP Parameters In Excel)}

Когато започнете да пишете =VLOOKUP( в която и да е клетка в Excel , ще видите изскачащ прозорец, показващ всички налични параметри на функцията.

Нека да разгледаме всеки от тези параметри и какво означават те.

• lookup_value : Стойността, която търсите от електронната таблица
• table_array : Обхватът от клетки в листа, в който искате да търсите
• col_index_num : Колоната, от която искате да изтеглите резултата си
• [range_lookup] : Режим на съвпадение ( TRUE = приблизително, FALSE = точно)

Тези четири параметъра ви позволяват да правите много различни, полезни търсения на данни в много големи набори от данни.

Прост пример за VLOOKUP в Excel^{(A Simple VLOOKUP Excel Example)}

VLOOKUP не е една от основните функции на Excel,^{(the basic Excel functions)} които може да сте научили, така че нека разгледаме прост пример, за да започнете.

За следващия пример ще използваме голяма електронна таблица с резултати от SAT за училища в Съединените ^(United) щати^(States) . Тази електронна таблица съдържа над 450 училища, заедно с индивидуални SAT резултати за четене, математика и писане. Чувствайте^(Feel) се свободни да изтеглите, за да следвате. Има външна връзка, която изтегля данните, така че ще получите предупреждение при отваряне на файла, но е безопасно.

Би отнело много време да търсите в такъв голям набор от данни, за да намерите училището, което ви интересува.

Вместо това можете да създадете прост формуляр в празните клетки отстрани на таблицата. За да извършите това търсене, просто направете едно поле за училище^(School) и три допълнителни полета за резултати за четене, математика и писане.

След това ще трябва да използвате функцията VLOOKUP в Excel , за да накарате тези три полета да работят. В полето Reading създайте функцията VLOOKUP , както следва:

1. Тип =VLOOKUP(
2. Изберете полето Училище^(School) , което в този пример е I2 . Въведете запетая.
3. Изберете целия диапазон от клетки, които съдържат данните, които искате да търсите. Въведете запетая.

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

Забележка^(Note) : Функцията VLOOKUP в Excel може да търси само в клетки вдясно от колоната за търсене. В този пример колоната с име на училище трябва да е отляво на данните, които търсите.

1. След това, за да извлечете резултата от Reading , ще трябва да изберете 3-та колона от най-лявата избрана колона. Така че, въведете 3 и след това въведете друга запетая.
2. Накрая въведете FALSE за точно съвпадение и затворете функцията с a ) .

Вашата последна функция VLOOKUP трябва да изглежда така:

=VLOOKUP(I2,B2:G461,3,FALSE)

Когато за първи път натиснете Enter и завършите функцията, ще забележите, че полето за четене^(Reading) ще съдържа #N/A .

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

Как да се справяме с VLOOKUP, като е чувствителен към малките букви^{(How To Deal With VLOOKUP Being Case- Sensitive)}

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

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

За да заобиколите това, можете да принудите това, което търсите, да превключи на малки букви, преди да потърсите резултатите. За да направите това, създайте нова колона до колоната, която търсите. Въведете функцията:

=TRIM(LOWER(B2))

Това ще намали името на училището с малки букви и ще премахне всички външни знаци (интервали), които може да са от лявата или дясната страна на името.

Задръжте натиснат клавиша Shift^(Shift) и поставете курсора на мишката върху долния десен ъгъл на първата клетка, докато се промени на две хоризонтални линии. Щракнете двукратно^(Double) с мишката, за да попълните автоматично цялата колона.

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

Сега, когато всичките ви данни са изчистени в тази нова колона, леко променете функцията си VLOOKUP в Excel , за да използвате тази нова колона вместо предишната, като започнете диапазона за търсене^{(the lookup range)} в C2 вместо B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

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

Това е удобен съвет на Excel^{(handy Excel tip)} за преодоляване на факта, че VLOOKUP е чувствителен към малки и големи букви.

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

Докато примерът за точно съвпадение LOOKUP , описан в първия раздел на тази статия, е доста ясен, приблизителното съвпадение е малко по-сложно.

Приблизителното съвпадение се използва най-добре за търсене в диапазони от числа. За да направите това правилно, диапазонът на търсене трябва да бъде правилно сортиран. Най-добрият пример за това е функцията VLOOKUP за търсене на буквен клас, който съответства на степен на число.

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

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

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

В тази функция VLOOKUP:

• lookup_value : F2, крайната осреднена оценка
• table_array : I2:J8, Диапазонът за търсене на буквен клас
• index_column : 2, втората колона в справочната таблица
• [range_lookup] : TRUE, приблизително съвпадение

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

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

Така че „C“ трябва да бъде буквата, присвоена на долния диапазон (75), а B е присвоена на дъното (минимум) на собствения си буквен диапазон. VLOOKUP ще „намери“ резултата за 60 (D) като най-близката приблизителна стойност за всичко между 60 и 75.

VLOOKUP в Excel е много мощна функция, която е налична от дълго време. Също така е полезно за намиране на съответстващи стойности навсякъде в работна книга на Excel^{(finding matching values anywhere in an Excel workbook)} .

Имайте предвид обаче, че потребителите на Microsoft , които имат месечен абонамент за Office 365 , вече имат достъп до по-нова функция XLOOKUP. Тази функция има повече параметри и допълнителна гъвкавост. Потребителите с полугодишен абонамент ще трябва да изчакат пускането на актуализацията през юли 2020^{(July 2020)} г.

How to Use VLOOKUP in Excel

Have you ever hаd a large spreadsheet with data in Excel and need an easy way to filter and extract specific informatiоn from it? Іf you learn how to use VLOOKUP іn Excel, you can do this lookup with just a single, powеrful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Добавете линия на тренда на линейна регресия към диаграма на разсейване на Excel

• Как да направите хистограма в Excel

• Как да защитите сигурно с парола файл на Excel

• Как да създадете блок-схема в Word и Excel

• Как да създадете етикети в Word от електронна таблица на Excel

• Как да използвате COUNTIFS, SUMIFS, AVERAGEIFS в Excel

• Ръководство за всички разширения на файлове на Excel и какво означават те

• Как да проследявате промените в Excel

• Как да добавяте и отпечатвате фонови изображения на Excel

• Как да групирате работни листове в Excel

• Как да сравните два файла на Excel и да подчертаете разликите

• Използвайте Excel като инструмент за копиране на данни от мрежата

• Как да филтрирам данни в Excel

• Как да добавяте коментари към клетка на работен лист на Excel

• Как да скриете листове, клетки, колони и формули в Excel

• Свържете клетките между листове и работни книги в Excel

• Използвайте Excel, за да изчислите ефективен лихвен процент от номинален лихвен процент

• Как да използвате функцията YEARFRAC в Excel

• Как да извадите дати в Excel

• Как да разделите собствените и фамилните имена в Excel