18. ВПР
Самая популярная функция в Экселе. Будете знать одну эту функцию –
можете заявлять, что являетесь уверенным пользователем Excel.
Давайте разбираться. Как вы помните из предыдущего урока – ПОИСКПОЗ возвращает номер ячейки с нужным содержимым в столбце (или строке), а ИНДЕКС использует эту информацию, чтобы найти содержимое ячейки, находящейся на пересечении этих строки и столбца.
ВПР (VLOOKUP) ищет совпадение по столбцу (название ВПР расшифровывается как Вертикальный Поиск Результата). Затем берёт найденную строку и отсчитывает заданное количество столбцов.
Честно признаюсь, когда я впервые столкнулся с изучением функции ВПР в университете – ничего не понял. Преподаватель не смог объяснить нам, как работают аргументы, что делает функция, как это всё работает. Поэтому здесь я хочу разобрать это как можно подробнее на простом примере из 1 пункта.
Синтаксис:
=ВПР(искомое_значение; диапазон;номер_столбца; приблизительный_поиск)
Пойдём по аргументам по порядку:
1. Искомое значение – Груша, указано в ячейке E2. Можно указать и словом «Груша», но это менее гибкий способ.
2. Следующий аргумент – диапазон, в котором ищется значение. Функция ВПР устроена так, что левый столбец этого диапазона должен содержать искомое значение. Ищем грушу, груша – это продукт, значит левый столбец – столбец B. Правый столбец не так важен, главное, что он должен включать в себя то значение, которое функция должна вернуть. Мы ищем цену, значит диапазон должен включать столбец D. Итого можем указать диапазон как B2:D5 (можно и как B:D – тут главное, чтобы ничего не мешалось).
3. Номер столбца, который содержит нужное нам значение. Считается от левого столбца диапазона. Здесь диапазон начинается со столбца B. Ищем цену, она в столбце D. Столбец D – третий по счёту, если отсчитывать от В. Итого – 3.
4. Приблизительный поиск – 0 для точного поиска, 1 – для приблизительного. В теории, приблизительный поиск должен выдавать корректное значение, если искомое слово немного отличается от того, что записано в столбце (например, «груща» вместо «груша»). Но часто это работает криво, поэтому предлагаю пока забыть об этом и пропускать этот аргумент.
Давайте разбираться. Как вы помните из предыдущего урока – ПОИСКПОЗ возвращает номер ячейки с нужным содержимым в столбце (или строке), а ИНДЕКС использует эту информацию, чтобы найти содержимое ячейки, находящейся на пересечении этих строки и столбца.
ВПР (VLOOKUP) ищет совпадение по столбцу (название ВПР расшифровывается как Вертикальный Поиск Результата). Затем берёт найденную строку и отсчитывает заданное количество столбцов.
Честно признаюсь, когда я впервые столкнулся с изучением функции ВПР в университете – ничего не понял. Преподаватель не смог объяснить нам, как работают аргументы, что делает функция, как это всё работает. Поэтому здесь я хочу разобрать это как можно подробнее на простом примере из 1 пункта.
Синтаксис:
=ВПР(искомое_значение; диапазон;номер_столбца; приблизительный_поиск)
Пойдём по аргументам по порядку:
1. Искомое значение – Груша, указано в ячейке E2. Можно указать и словом «Груша», но это менее гибкий способ.
2. Следующий аргумент – диапазон, в котором ищется значение. Функция ВПР устроена так, что левый столбец этого диапазона должен содержать искомое значение. Ищем грушу, груша – это продукт, значит левый столбец – столбец B. Правый столбец не так важен, главное, что он должен включать в себя то значение, которое функция должна вернуть. Мы ищем цену, значит диапазон должен включать столбец D. Итого можем указать диапазон как B2:D5 (можно и как B:D – тут главное, чтобы ничего не мешалось).
3. Номер столбца, который содержит нужное нам значение. Считается от левого столбца диапазона. Здесь диапазон начинается со столбца B. Ищем цену, она в столбце D. Столбец D – третий по счёту, если отсчитывать от В. Итого – 3.
4. Приблизительный поиск – 0 для точного поиска, 1 – для приблизительного. В теории, приблизительный поиск должен выдавать корректное значение, если искомое слово немного отличается от того, что записано в столбце (например, «груща» вместо «груша»). Но часто это работает криво, поэтому предлагаю пока забыть об этом и пропускать этот аргумент.
Задание 1: Введение
Выше мы уже разобрали, какие аргументы нужно использовать в 1
пункте. Хочу лишь обратить внимание, что для удобства работы с
горячими клавишами Ctrl + стрелки при вводе ссылок на
ячейки, диапазон удобно выделять со второй строки: B2:D5. В этом
уроке будет проверяться такой диапазон, но в жизни вы можете
использовать любой другой!
Итак, приступим!
Итак, приступим!
1. В ячейке F2 найдите цену груши. Можете ссылаться на ячейку F2 в
1 аргументе
2. В ячейке J2 найдите продукт, который производится в Эквадоре.
Не забудьте, что левый столбец диапазона должен поменяться!
3. В ячейке G5 найдите продукт чёрного цвета
Вы можете заметить, что это невозможно сделать с помощью функции ВПР, потому что искомый столбец должен быть левым в диапазоне! Попробуйте ввести ВПР с диапазоном B2:D5 и посмотрите, какой результат выдаст функция
Вы можете заметить, что это невозможно сделать с помощью функции ВПР, потому что искомый столбец должен быть левым в диапазоне! Попробуйте ввести ВПР с диапазоном B2:D5 и посмотрите, какой результат выдаст функция
Выполните шаги... (0/3)
Задание 2: ВПР (VLOOKUP)
Давайте разберёмся с тем, как это обычно работает в таблицах.
Обычно у нас есть исходная таблица и ещё одна, в которую нужно
подтянуть данные
Для того, чтобы это сделать и используется ВПР
Предположим, что в ячейках A1:D8 у нас есть таблица с характеристиками разных городов, а в новой таблице в столбце F нужно подтянуть часть этих характеристик
Для того, чтобы это сделать и используется ВПР
Предположим, что в ячейках A1:D8 у нас есть таблица с характеристиками разных городов, а в новой таблице в столбце F нужно подтянуть часть этих характеристик
1. В ячейке G2 найдите население Перми. После этого скопируйте
формулу в ячейки ниже. Не забудьте закрепить нужные ссылки
2. Теперь в H2 найдите климат Перми. Также скопируйте формулу вниз
3. Теперь в новой таблице в K2 найдите население города в России
В отличие от предыдущего задания, здесь диапазон может быть указан корректно, так как столбец со страной находится левее столбца с населением. Но проблема возникает с тем, что некоторые названия стран в первой таблице дублируются! ВПР возвращает только первое вхождение, поэтому будет работать некорректно с такими данными
В отличие от предыдущего задания, здесь диапазон может быть указан корректно, так как столбец со страной находится левее столбца с населением. Но проблема возникает с тем, что некоторые названия стран в первой таблице дублируются! ВПР возвращает только первое вхождение, поэтому будет работать некорректно с такими данными
Выполните шаги... (0/3)
Задание 3: Практика
A2:E11 – таблица с перечислением фильмов. Она будет являться
диапазоном для поиска в функции ВПР. При этом если ищем фильм, то
самый левый столбец в диапазоне поиска должен содержать фильмы.
Поэтому диапазон поиска – B2:E11
В столбце G перечислены фильмы. Они будут выступать в качестве искомых значений
В верхней строке (B1:E1) для облегчения работы с ВПР указаны номера столбцов
В столбце G перечислены фильмы. Они будут выступать в качестве искомых значений
В верхней строке (B1:E1) для облегчения работы с ВПР указаны номера столбцов
1. В ячейке H2 введите формулу, которая вернет год для фильма из
ячейки G2. Пойдем по порядку. =VLOOKUP( 1 аргумент –
всегда то, ЧТО ищется. Здесь ищется фильм из ячейки
G2. 2 аргумент – диапазон, в котором будет
производиться поиск. Помним, что левый столбец должен
содержать те данные, которые ищем. Ищем фильм, поэтому диапазон
будет начинаться со столбца B – B2:E11. Помним, что будем
протягивать формулу вниз, поэтому сразу же
закрепляем диапазон через f4. Так как в
H ищем Рейтинг фильма, то считаем, какой номер у
столбца с рейтингом в диапазоне B2:E11. Это 4 столбец ,
пишем 4. И наконец 0
2. В столбце I попробуйте сами найти год фильма. Подсказка:
поменяется только номер столбца
3. В столбце J найдите жанр фильма
Выполните шаги... (0/3)
