Сегодня мы поговорим о функции, которая у многих вызывает неподдельный страх. Многие думают, что она сложная и запутанная. В этом уроке мы научимся использовать эту полезную функцию.
Функция ВПР - это совершенно не заменимая функция, используемая при обработке табличных данных.
Что позволяет делать функция ВПР?
Принцип действия функции ВПР в Гугл таблицах, да и собственно в Excel, достаточно прост.
Эта функция позволяет найти исходное значение в столбце таблицы и взять соответствующее ему значение в любом другом, соседнем столбце (из этой же строки).
Посмотрите на рисунок:
На этом образце совсем не большая таблица, и мы, окинув взглядом, сразу можем увидеть, что, например, у Сидорова оклад равен 135000руб.
Но если таблица очень большая, то вместо того, чтобы листать бессчетное количество страниц, проще поручить это специальной функции.
Итак. Давайте для нашего примера посмотрим, как будет выглядеть написание функции. Нам нужно вывести в ячейку значение оклада для Сидорова
Сначала посмотрим на то, как вообще составляется эта функция:
=ВПР(запрос; диапазон; номер_столбца; [отсортировано])
ВПР - название функции
запрос - это именно то, что мы ищем в столбце. В нашем случае это - "Сидоров" (т.к. это текст, берём в кавычки)
диапазон - это таблица в которой мы рассматриваем данные. Обратите внимание, что функция ВПР будет искать "Сидоров именно в первом столбце". Наш диапазон будет равен всей нашей таблице (отбросим только строку с заголовками), а именно: B4:E8. Если у нас очень большая таблица, либо таблица пока не имеющая окончания, то можно указать: B4:E
Можно использовать $, чтобы "заморозить" диапазон, на случай, если мы будем его копировать, например так: $B$4:$E
номер_столбца - это номер столбца (по порядку, начиная с 1-го) из которого нужно взять данные. Обратите внимание, что нумерация в диапазоне своя (новая), которая распространяется только на указанный диапазон. Наш диапазон начинается со столбца B. Именно он и будет столбцом номер 1, для функции ВПР. Т.е ФИО - это столбец номер 1, а Оклад - это столбец номер 4. Именно нам он и нужен, соответственно для нашего примера номер столбца берем 4.
отсортировано - хоть этот параметр указан в квадратных скобках и не обязателен для указания, но мы укажем "0". Это признак того, что столбец у нас не отсортирован. Чуть ниже мы подробно разберем принцип работы этого параметра.
Итак, вот как будет выглядеть наша формула:
=ВПР("Сидоров";B3:E8;4;0)
Как можно использовать функцию?
Давайте разберем несколько примеров использования данной функции. В прошлом уроке мы изучили как делать выпадающие списки, посмотрите, если пропустили
Давайте в ячейке В12 сделаем выпадающий список
И сделаем так, чтобы в ячейки С12, D12 и E12 автоматически подтягивались данные, в зависимости от выбранной фамилии. В моем примере таблица с исходными данными и новая таблица с выпадающем списком находятся на одном листе, в практике они могут быть на разных листах.
Как будет выглядеть формула для подтягивания в ячейку C12 даты рождения выбранного человека:
=ВПР($B$12;$B$3:$E$8;2;0)
$B$12 - ячейка в которой указана фамилия человека (по которой ищем данные)
$B$3:$E$8 - диапазон таблицы в которой ищем данные
2 - номер столбца, из которого берем данные
0 - признак того, что данные не отсортированы. Если сомневаетесь, что ставить, всегда ставьте 0.
Как вы обратили внимание, я все диапазоны и ссылки "заморозил". Теперь, если я скопирую эту формулу в остальные ячейки (D12 и E12), то формула не "поплывет" и в этих ячейках тоже будет дата рождения.
Останется только поменять номер столбца:
2 - это дата рождения,
3 - это телефон
4 - это оклад.
Соответственно, для ячейки D12, получим формулу
=ВПР($B$12;$B$3:$E$8;3;0)
а для E12, получим:
=ВПР($B$12;$B$3:$E$8;4;0)
Что такое признак Отсортировано в ВПР?
отсортировано – необязательный аргумент, который может принимать одно из следующих значений:
- ЛОЖЬ ("0", ноль) – рекомендуемое значение, используемое для поиска точного соответствия.
- ИСТИНА ("1"– значение, используемое для поиска приблизительного соответствия. Оно задается по умолчанию, если значение аргумента отсортировано не задано.
Внимание! Прежде чем использовать функцию ВПР для поиска приблизительного соответствия, необходимо выполнить сортировку по столбцу запроса в порядке возрастания. В противном случае вы можете получить неверный результат.
Если в аргументе задать "1", то будет искаться первое похожее значение. Например если в столбце есть несколько похожих значений:
Иванов
Иванова
Ивановченко
и мы, скажем, ищем "Иванов" и столбец отсортирован, то мы получим данные для значения Иванов, а если столбец не отсортирован, то мы можем получить не совсем прогнозируемые данные. Для столбца:
Иванова
Иванов
Ивановченко
мы получим данные не для Иванова, а для Ивановой. Т.к. это первое, что попалось для похожего на Иванов.
Друзья, я попытался максимально доходчиво объяснить применение этих функций. Надеюсь мой урок был полезен. Поставьте лайк и/или напишите отзыв. Спасибо!
Все уроки по Гугл таблицам можно посмотреть здесь:
Вы можете читать обучающие уроки по Google Tabs на моем канале в boosty.to.
Для Вас доступен бесплатный тестовый период, по ссылке: