У вас когда-нибудь была большая электронная таблица с данными в Excel , и вам нужен простой способ фильтровать и извлекать из нее конкретную информацию? Если вы научитесь использовать функцию ВПР^(VLOOKUP) в Excel , вы сможете выполнять этот поиск с помощью всего одной мощной функции Excel^{(Excel function)} .

Функция ВПР^{(VLOOKUP function)} в Excel многих пугает, потому что у нее много параметров и есть несколько способов ее использования. В этой статье вы узнаете обо всех способах использования функции ВПР^(VLOOKUP) в Excel и о том, почему эта функция настолько эффективна.

Параметры ВПР в Excel^{(VLOOKUP Parameters In Excel)}

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

Давайте рассмотрим каждый из этих параметров и их значение.

• lookup_value : значение, которое вы ищете в электронной таблице.
• table_array : диапазон ячеек на листе, в котором вы хотите выполнить поиск.
• col_index_num : столбец, из которого вы хотите получить результат.
• [range_lookup] : режим соответствия^{(Match mode)} ( TRUE = approximate , FALSE = exact )

Эти четыре параметра позволяют выполнять множество различных полезных операций поиска данных в очень больших наборах данных.

Простой пример ВПР Excel^{(A Simple VLOOKUP Excel Example)}

ВПР не является^{(VLOOKUP isn)} одной из основных функций Excel , которые вы могли изучить, поэтому для начала рассмотрим простой пример.

В следующем примере мы будем использовать большую таблицу результатов SAT для^(SAT) школ в США ^(United) . ^(States)Эта таблица содержит более 450 школ, а также индивидуальные результаты SAT по чтению, математике и письму. Не стесняйтесь^(Feel) загружать, чтобы следовать. Существует внешнее соединение, которое извлекает данные, поэтому при открытии файла вы получите предупреждение, но это безопасно.

Поиск интересующей вас школы в таком большом наборе данных займет очень много времени.

Вместо этого вы можете создать простую форму в пустых ячейках сбоку от таблицы. Чтобы выполнить этот поиск, просто создайте одно поле для школы^(School) и три дополнительных поля для оценок по чтению, математике и письму.

Затем вам нужно будет использовать функцию ВПР^{(VLOOKUP function)} в Excel , чтобы эти три поля работали. В поле Чтение^(Reading) создайте функцию ВПР^{(VLOOKUP function)} следующим образом:

1. Введите =VLOOKUP(
2. Выберите поле Школа^{(School field)} , в данном примере это I2 . Введите запятую.
3. Выделите весь диапазон ячеек, содержащих данные, которые вы хотите найти. Введите запятую.

Когда вы выбираете диапазон, вы можете начать со столбца, который вы используете для поиска (в данном случае это столбец с названием школы^{(school name column)} ), а затем выбрать все остальные столбцы и строки, содержащие данные.

Примечание^(Note) . Функция ВПР^{(VLOOKUP function)} в Excel может выполнять поиск только в ячейках справа от столбца поиска^{(search column)} . В этом примере столбец с названием школы^{(school name column)} должен находиться слева от данных, которые вы ищете.

1. Затем, чтобы получить оценку по чтению^{(Reading score)} , вам нужно выбрать третий столбец из крайнего левого выбранного столбца. Итак, введите 3 , а затем введите еще одну запятую.
2. Наконец, введите FALSE для точного совпадения и закройте функцию с помощью ) .

Ваша окончательная функция ВПР^{(VLOOKUP function)} должна выглядеть примерно так:

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

Когда вы впервые нажмете Enter и завершите функцию, вы заметите, что поле Reading^{(Reading field)} будет содержать #N/A .

Это связано с тем, что поле «Школа» пустое и ^{(School field)}функция ВПР^{(VLOOKUP function)} ничего не может найти. Однако, если вы введете название любой средней школы, которую хотите найти, вы увидите правильные результаты из этой строки для оценки чтения^{(Reading score)} .

Что делать, если функция ВПР чувствительна к регистру^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Вы можете заметить, что если вы не введете название школы в том же регистре, что и в наборе данных, вы не увидите никаких результатов.

Это связано с тем, что функция ВПР^{(VLOOKUP function)} чувствительна к регистру. Это может раздражать, особенно для очень большого набора данных, где столбец, в котором вы просматриваете, не соответствует тому, как пишутся заглавные буквы.

Чтобы обойти это, вы можете заставить то, что вы ищете, переключиться на нижний регистр, прежде чем искать результаты. Для этого создайте новый столбец рядом с искомым столбцом. Введите функцию:

=TRIM(LOWER(B2))

Это сделает название школы^{(school name)} строчными и удалит все посторонние символы (пробелы), которые могут быть слева или справа от названия.

Удерживая нажатой клавишу Shift, поместите ^{(Shift key and place)}курсор мыши^{(mouse cursor)} в правый нижний угол первой ячейки, пока он не изменится на две горизонтальные линии. Дважды^(Double) щелкните мышью, чтобы автоматически заполнить весь столбец.

Наконец, поскольку функция ВПР^(VLOOKUP) попытается использовать формулу, а не текст в этих ячейках, вам нужно преобразовать их все только в значения. Для этого скопируйте весь столбец, щелкните правой кнопкой мыши первую ячейку и вставьте только значения.

Теперь, когда все ваши данные очищены в этом новом столбце, немного измените функцию ВПР^{(VLOOKUP function)} в Excel , чтобы использовать этот новый столбец вместо предыдущего, начав диапазон поиска^{(the lookup range)} с C2 вместо B2.

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

Теперь вы заметите, что если вы всегда вводите поиск строчными буквами, вы всегда будете получать хорошие результаты поиска^{(search result)} .

Это удобный совет по работе с Excel^{(handy Excel tip)} , позволяющий преодолеть тот факт, что функция ВПР^(VLOOKUP) чувствительна к регистру.

ВПР Приблизительное совпадение

В то время как пример LOOKUP^{(LOOKUP example)} с точным соответствием, описанный в первом разделе этой статьи, довольно прост, приблизительное совпадение немного сложнее.

Приблизительное совпадение лучше всего использовать для поиска в диапазоне номеров. Чтобы сделать это правильно, диапазон поиска^{(search range)} должен быть правильно отсортирован. Лучшим примером этого является функция ВПР^{(VLOOKUP function)} для поиска буквенной оценки^{(letter grade)} , соответствующей числовой оценке^{(number grade)} .

Если у учителя есть длинный список оценок домашних заданий учеников^{(student homework)} за весь год с итоговой средней колонкой^{(averaged column)} , было бы неплохо, чтобы буквенная оценка, соответствующая этой итоговой оценке, появлялась автоматически.

Это возможно с помощью функции ВПР^{(VLOOKUP function)} . Все, что требуется, — это справочная таблица^{(lookup table)} справа, которая содержит соответствующую буквенную оценку для каждого ^{(letter grade)}диапазона^{(score range)} числовых оценок .

Теперь, используя функцию ВПР^{(VLOOKUP function)} и приблизительное совпадение, вы можете найти правильную буквенную оценку, соответствующую правильному числовому диапазону.

В этой функции ВПР:

• lookup_value : F2, окончательная усредненная оценка
• table_array : I2: J8, диапазон поиска буквенной оценки^{(letter grade lookup range)}
• index_column : 2, второй столбец в таблице поиска^{(lookup table)}
• [range_lookup] : TRUE, приблизительное совпадение

После того, как вы закончите работу с функцией ВПР^{(VLOOKUP function)} в G2 и нажмете Enter^{(G2 and press Enter)} , вы сможете заполнить остальные ячейки, используя тот же подход, который описан в предыдущем разделе. Вы увидите, что все буквенные оценки заполнены правильно.

Обратите внимание, что функция ВПР^{(VLOOKUP function)} в Excel выполняет поиск от нижнего конца диапазона оценок^{(grade range)} с присвоенной буквенной оценкой^{(letter score)} до верхней части диапазона следующей буквенной оценки^{(letter score)} .

Таким образом, «C» должна быть буквой, назначенной нижнему диапазону (75), а B — нижней (минимальной) букве собственного диапазона^{(letter range)} . ВПР^(VLOOKUP) «найдет» результат для 60 (D) как ближайшее приблизительное значение для любого значения в диапазоне от 60 до 75.

ВПР^(VLOOKUP) в Excel — очень мощная функция, доступная уже давно. Это также полезно для поиска совпадающих значений в любом месте рабочей книги Excel^{(Excel workbook)} .

Однако имейте в виду, что пользователи Microsoft с ежемесячной подпиской на ^(Microsoft)Office 365 теперь имеют доступ к более новой функции XLOOKUP. Эта функция имеет больше параметров и дополнительную гибкость. Пользователям с полугодовой подпиской необходимо дождаться выпуска обновления в июле 2020 года^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you ever had a large spreadsheet with data in Excel and nееd an easy way to filtеr and extract specific informatіon from it? If you learn how to uѕe VLOOKUP in Excel, you can do thіs lookup with just a sіngle, pоwerful 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

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как сделать Histogram в Excel

• Как надежно Password Protect Excel File

• Как создать Flowchart в Word and Excel

• Как Create Labels в Word от Excel Spreadsheet

• Как сделать простой график или диаграмму в Excel

• 40 лучших сочетаний клавиш Microsoft Excel

• Используйте функции суммирования для суммирования данных в Excel

• Как создать Drop Down List в Excel

• Как записать Macro в Excel

• Связать ячейки между листами и книгами в Excel

• Как использовать функцию YEARFRAC в Excel

• Используйте Excel, чтобы определить эффективную процентную ставку по номинальной процентной ставке

• График ваших данных Excel

• Как использовать функции автоматического восстановления и резервного копирования Excel

• Форматировать ячейки с помощью условного форматирования в Excel

• Как разделить имя и фамилию в Excel

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

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

• Учебное пособие по основам Microsoft Excel — обучение работе с Excel