Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Microsoft365.com «Мой Office» для iPhone
Совет: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.
Находите данные по строкам в таблице или диапазоне с помощью функции ВПР. Например, можно найти цену автомобильной детали по ее номеру или найти имя сотрудника по его идентификатору.
Самая простая функция ВПР означает следующее:
=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).
Советы:
-
Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
-
Если вы являетесь microsoft Copilot подписчик Copilot может упростить вставку и использование функций VLookup или XLookup. См . статью Copilot, чтобы упростить поиск в Excel.
Используйте функцию ВПР для поиска значения в таблице.
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Например:
-
=ВПР(A2;A10:C20;2;ИСТИНА)
-
=ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)
-
=ВПР(A2;’Сведения о клиенте’! A:F,3,FALSE)
|
Имя аргумента |
Описание |
|---|---|
|
искомое_значение (обязательный) |
Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в аргументе таблица. Например, если таблица охватывает диапазон ячеек B2:D7, искомое_значение должно находиться в столбце B.
|
|
таблица (обязательный) |
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Вы можете использовать именованный диапазон или таблицу, а также имена в аргументе вместо ссылок на ячейки. Первый столбец в диапазоне ячеек должен содержать искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти. Узнайте, как выбирать диапазоны на листе . |
|
номер_столбца (обязательный) |
Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение. |
|
интервальный_просмотр (необязательный) |
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
|
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
-
Значение, которое вам нужно найти, то есть искомое значение.
-
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
-
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
-
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
|
Проблема |
Возможная причина |
|---|---|
|
Неправильное возвращаемое значение |
Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия. |
|
#Н/Д в ячейке |
Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР. |
|
#ССЫЛКА! в ячейке |
Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!. Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!. |
|
#ЗНАЧ! в ячейке |
Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!. Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР. |
|
#ИМЯ? в ячейке |
Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе. Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?. |
|
Ошибки #ПЕРЕНОС! в ячейке |
Эта конкретная ошибка #ПЕРЕНОС! обычно означает, что формула использует неявное пересечение для искомого значения и применяет весь столбец в качестве ссылки. Например, =ВПР(A:A;A:C;2;ЛОЖЬ). Вы можете устранить эту проблему, привязав ссылку подстановки с помощью оператора @, например: =ВПР(@A:A;A:C;2;ЛОЖЬ). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку вместо целого столбца: =ВПР(A2;A:C;2;ЛОЖЬ). |
|
Действие |
Примечания |
|---|---|
|
Используйте абсолютные ссылки в аргументе интервальный_просмотр |
Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки. |
|
Не сохраняйте числовые значения или значения дат как текст. |
При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение. |
|
Сортируйте первый столбец |
Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы. |
|
Используйте подстановочные знаки |
Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом. Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться. |
|
Убедитесь, что данные не содержат ошибочных символов. |
При поиске текстовых значений в первом столбце убедитесь, что данные в первом столбце не имеют начальных пробелов, конечных пробелов, несогласованного использования прямых ( или » ) и фигурных кавычек ( или «) или символов без печати. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ. |
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Функция ПРОСМОТРX
Краткий справочник: функция ВПР
Исправление ошибки #Н/Д в функции ВПР
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Функция ГПР
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
#Руководства
-
0
Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто — переносим цены.
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.
Разберёмся, зачем нужна функция и как её использовать. В конце материала расскажем, что делать, если нужен поиск данных сразу по двум параметрам.
11 полезных материалов от издательства «МИФ» — раздаём бесплатно в Telegram
Дарим книги для буста карьеры, 12 инструментов для повышения качества жизни, энергобокс, гайд для внезапных удалёнщиков, инфографику «Супермен по привычке» и много других полезных материалов.
Забрать бесплатно
Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.
Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.
Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.
ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.
Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.
Важно!
ВПР может не работать, если таблицы расположены в разных файлах. Тогда лучше собрать данные в одном файле, на разных листах.
ВПР работает по следующему принципу. Функция просматривает выбранный диапазон первой таблицы вертикально сверху вниз до искомого значения‑идентификатора. Когда видит его, забирает значение напротив него из нужного столбца и копирует во вторую таблицу.
Подробнее о том, как определить все эти значения, поговорим ниже. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.
Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.
В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец «Цена, руб.» в таблицу с клиентами и выберем ячейку напротив первого клиента.
Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».
Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.
Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».
Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.
Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.
Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.
Порядок действий, чтобы указать значение, выглядит так:
- Ставим курсор в окно «Искомое значение» в построителе формул.
- Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.
Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).
Таблица — это диапазон ячеек, из которого функция будет брать данные для искомого значения. В этот диапазон должны войти столбцы с искомым значением и со значением, которое нужно перенести в первую таблицу.
В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы «Марка, модель» (искомое значение) и «Цена, руб.» (переносимое значение).
Важно!
Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона. У нас искомое значение находится в ячейке A2, поэтому диапазон должен начинаться с A.
Порядок действий для указания диапазона:
- Ставим курсор в окно «Таблица» в построителе формул.
- Переходим в таблицу «Каталог авто».
- Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
- Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.
Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).
Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.
В нашем случае значение для переноса — цена — находится в пятом столбце слева.
Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).
Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:
- Если нужно точное совпадение при поиске ВПР, вводим 0.
- Если нужно приближённое соответствие при поиске ВПР, вводим 1.
В нашем случае нужно, чтобы функция подтянула точные значения цен авто, поэтому нам подходит первый вариант.
Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.
Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.
Дальше нужно протянуть это значение вниз до конца таблицы, чтобы функция нашла и перенесла цены всех оставшихся моделей. Для этого мы закрепляли интервал, когда заполняли аргументы.
Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.
В нашем примере модели автомобилей в таблицах не повторяются, поэтому мы ищем только по одному критерию — названию модели. Но бывают случаи, когда такой поиск не подходит.
Например, у нас несколько одинаковых моделей с разным цветом.
И по традиции есть таблица с клиентами, которые эти модели забронировали.
Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.
Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.
Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.
Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:
- ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
- B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.
Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.
Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).
Курс-тренажёр: «Excel + «Google Таблицы» с нуля до PRO»
Узнать о курсе
Что такое функция ВПР в Excel
Функция ВПР или Vlookup ― это функция для поиска в Excel. Она по запросу ищет ключевые слова в одной таблице и переносит нужную информацию в другую. Подходит для работы с большим массивом данных. Если правильно вбить формулу, то вам не придется часами переносить строку за строкой — достаточно будет нажать одну кнопку, чтобы найти фамилию сотрудника, номер договора или выручку по розничной точке.
Как работает ВПР
Принцип работы формулы такой: функция анализирует выбранную область и столбец, а когда находит нужное значение, копирует его в другую таблицу, из которой поступил запрос. Работать можно на нескольких листах. Например, когда исходные данные «лежат» на Листе-1, а перенести информацию нужно на Лист-3.
Общая формула ВПР выглядит так: =ВПР (искомое значение; область для поиска; номер столбца, в котором нужное значение; 0 (если ЛОЖЬ) или 1 (если ИСТИНА).
«ЛОЖЬ» ставят, когда допустимо точное значение, а «ИСТИНА» ― если нужны приблизительные данные. Это актуально только для функции ВПР. В остальных случаях «ИСТИНА» означает, что условие выполняется (например, 15>5), а «ЛОЖЬ», что не выполняется (например, 15<5).
Для чего можно использовать ВПР в Excel
Чаще всего функцию ВПР используют, когда нужно перенести данные из одного списка в другой, а вручную это сделать невозможно. К примеру, вам нужно посчитать зарплату 800 сотрудникам. У вас есть шаблон ведомости в Excel, где проставлены ФИО и табельные номера, а оклады находятся в другой таблице. Если открыть документы в двух окнах и перебивать вручную, то на это можно потратить несколько дней, а если правильно вбить формулу — от 5 до 10 минут.
Из исходной таблицы слева нужно найти зарплату сотрудников и перенести в таблицу с ведомостью
Функцию ВПР можно использовать не только для начисления зарплаты, а также если надо:
- Найти цену на товар.
- Узнать номер договора с конкретным предприятием.
- Вычленить из общего реестра единичные данные, например только ФИО и возраст каких-то сотрудников, или перенести в одну таблицу данные о работниках мужчинах, а в другую — о женщинах.
- Найти в реестре товары, которые не продавались в течение года, и перенести их в отдельную таблицу.
В каждой компании ВПР используют в разных целях. Функция пригодится тем, кто работает с маркетплейсами, бухгалтерам, экономистам и аналитикам. А если проанализировать бизнес с помощью Excel не получается, то сервис сквозной аналитики от Calltouch даст все необходимые данные для улучшения маркетинговой стратегии.
Сквозная аналитика
Оценивайте эффективность всех рекламных кампаний в одном окне от клика до ROI
Вкладывайте в ту рекламу, которая приводит клиентов
Подробнее
Как создать функцию ВПР в Excel
Данные из 10-20 ячеек можно посчитать и вручную. Обычно формулу применяют для 100 и более наименований. Чтобы было проще понять, как работает ВПР, разберем функцию на примере. У нас есть таблица в Excel, в которой указаны ФИО директоров, названия их фирм и номера договоров. Найдем номер договора, который заключен с Лысенко Анной Викторовной.
Таблица с выходными данными
В строке ниже нам нужно автоматически найти № договора
В поле В13 начинаем писать функцию следующим образом:
- Первое — «=ВПР», чтобы чтобы начать работу с формулой.
- А13 ― так как наше искомое значение — это «Лысенко Анна Викторовна», поэтому выбираем ячейку, где уже вбиты ФИО.
- Область для поиска в данной таблице — А2:С7. Название столбцов сюда не входят. Выделяем ту зону, по которой функция ВПР будет искать информацию.
- Вводим номер столбца, из которого нужно взять значение. В примере это столбец С. Он третий слева. Выбирать нужно из массива, который участвует в формуле. В случае, когда нам понадобится информация не по номеру договора, а по названию компании, в формуле нужно будет поменять всего одно значение: вместо 3 — 2.
Нужно поменять номер столбца, чтобы узнать другую информацию
- Конечный этап ― выбрать «ЛОЖЬ». Программа сама подскажет этот шаг, когда все значения будут выбраны. Не забудьте закрыть скобку, иначе формула ВПР не сработает. После того как формула внесена, мы получаем ответ на запрос.
Результат
Чтобы отработать навыки, можно потренироваться на маленьких таблицах.
Функция ВПР для нескольких условий
Бывают ситуации, когда нужно подтянуть в другую excel-таблицу конкретное значение, но по нескольким параметрам сразу. Например, к компании и номеру договора нужно подобрать ФИО директора. Но функция ВПР не ищет по нескольким столбцам. Выйти из этой ситуации можно за счет создания дополнительного столбца. Поместить информацию в ячейку можно с помощью формулы «СЦЕП».
Исходная таблица со вспомогательным столбцом
Теперь в соответствии и синтаксисом функции вбиваем формулу:
- Критерий поиска ― это 3 параметра (компания, номер договора и предмет договора). Исходя из этих значений, нужно найти ФИО директора.
- Область таблицы важно выбрать вместе со вспомогательным столбцом.
- Так как мы хотим узнать ФИО, стоит указать столбец «2» как искомое значение.
- Цель — найти точное значение. Поэтому выбираем «ЛОЖЬ».
Вбиваем формулу
Если проставить знак «$», то формулу можно протягивать. Этот способ подойдет, если искать цену продукта в конкретные дни или искать выручку магазина по его подразделению и городу. Чтобы в дальнейшем интерпретировать эту информацию с выгодой, побеспокойтесь о маркетинговых знаниях заранее. Тем более, что базовые навыки можно получить всего за 1,5 часа.
Знания про маркетинг, аналитику, диджитал, быстро и бесплатно
- Новый скилл всего за 1,5 часа
- Практические знания
- Интерактивные юниты
- Без оплат и встроенных покупок
Функция ВПР по частичному совпадению
Представим ситуацию. Задача — найти информацию по фирме Василия, но вы не знаете его фамилию. А в excel-таблице с информацией нет отдельного критерия «Имя». Тогда вам поможет функция ВПР по частичному совпадению. Выражение будет выглядеть так: =ВПР(«*имя*»;таблица;столбец со значением;ЛОЖЬ).
Ищем информацию по частичному совпадению
Эта функция удобна, если нужно найти информацию о товаре, но вы не помните его полного названия. Также она поможет быстро найти дату дня рождения сотрудника.
Функция ВПР для нескольких таблиц
ВПР для нескольких таблиц подойдет, если данные за период изменились, но их нужно сравнить. К примеру, в текущем месяце у некоторых сотрудников повысились оклады. Чтобы не проверять данные вручную, воспользуемся функцией ВПР.
Как сравнить данные в таблицах
В I3 вводим формулу: =ВПР(ФИО работника;диапазон из левой таблицы;столбец;ЛОЖЬ). Затем копируем формулу в строки ниже, как на примере.
Формула удобна для сравнения текущих и старых цен или начальной цены и скидки. Так можно мониторить, как меняются цены у поставщиков или продажи в зависимости от стоимости продуктов.
Аналоги ВПР
Функция ВПР довольно сложная, но ее можно заменить другими формулами в Excel:
- ПРОСМОТР (LOOKUP) доступна в новых Google-таблицах и помогает найти столбец или строку с нужным значением;
- ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) ― при одновременном использовании ищут положение запроса в графах и перемещают в другую ячейку;
- СУММЕСЛИ (SUMIF) ― суммирует выбранный диапазон. К примеру, если нужно просуммировать только те оклады, которые выше 36 000 руб.
Ошибки при использовании функции ВПР
Функция ВПР ― одна из самых сложных формул в Excel, поэтому в ней можно допустить ошибки. Одни неточности можно заметить сразу ― программа не сделает расчет. А бывает и так, что погрешность не видна ― результат есть, но неверный. Разберем типовые ошибки пользователя:
- #Н/Д ― распространенное значение, когда ссылка сделана неверно. Либо вы сослались на окно с неправильно заполненным значением, либо ячейка пустая, а в ссылке обязательно должны быть данные.
- Формула сработала, но когда протягиваешь ее, выдает неверные значения. Это самая опасная ошибка, так как в крупных аналитических excel-таблицах ее трудно заметить. Такое бывает, когда вы не закрепили ссылку или таблицу. Чтобы закрепить ссылки, проставьте знак «$», нажав клавишу F4.
- Функция ВПР дает результаты, но при проверке они неточные. Тогда проверьте, что стоит в последнем значении: 0 или 1 (ЛОЖЬ или ИСТИНА).
- #ЗНАЧ ― выбивает тогда, когда значение в строке слишком длинное.
ВПР, или вертикальный поиск, помогает быстро найти в таблице нужные значения и перенести их с одного листа на другой. Расскажем, как работает функция, в каких случаях она полезна и как исправить возможные ошибки.
Вместо Excel мы пользуемся сервисом Google Sheets, потому что он бесплатный и работает онлайн: можно пользоваться с компьютера и телефона. Если Excel привычнее, никаких проблем — в нем те же функции.
Как использовать ВПР в личных финансах
Как устроена функция. ВПР сканирует диапазон значений сверху вниз, пока не найдет нужное. Когда значение находится, функция копирует его в соседнюю таблицу. Затем ВПР продолжает поиск, а когда таблица заканчивается, останавливает работу.
| Функция в Sheets | Функция в Excel | Что делает | Синтаксис |
| ВПР или VLOOKUP | ВПР | Автоматически переносит данные из одной таблицы в другую | =ВПР(искомое значение; таблица; номер столбца; интервальный просмотр) |
Функция работает только в рамках одной таблицы (одного файла). Если таблиц несколько, сначала нужно вручную скопировать данные в одну из них.
Чем функция ВПР полезна в личных финансах. Например, часто учет расходов и доходов ведут на разных листах одной таблицы, чтобы ничего не путалось:
Но что, если хочется собрать расходы и доходы по месяцам на одном листе? Тут и поможет ВПР.
Как работает ВПР: пошаговый алгоритм
Шаг 1. Выделяем ячейку, в которую хотим перенести первое значение.
Шаг 2. Вводим функцию с таким синтаксисом:
| Аргумент | В нашем примере |
| Значение, которое нужно найти. Оно должно быть одинаковым на обоих листах | Расход за месяц: январь, февраль, март и так далее |
| Диапазон, по которому нужно выполнить поиск | Названия месяцев и суммы расходов на листе «Расход» |
| Индекс или номер столбика со значением, которое нужно взять из листа «Расход» | Сумма расходов. Этот столбик идет вторым по счету, поэтому укажем цифру 2 |
| Интервальный просмотр, который определяет, насколько точно работает ВПР. Если значение нужно только при полном совпадении, вводим 0, а при приблизительном — 1. Когда работаете с суммами, всегда нужно полное совпадение, иначе в данных будет путаница | Выберем полное совпадение и введем 0 |
Все аргументы разделяются точкой с запятой. Вот как это выглядит:
Шаг 3. Растянем формулу из заполненной ячейки на все остальные, чтобы в таблицу попали значения по всем месяцам.
Готово! Теперь суммы легко сравнить — сразу понятно, в какие месяцы расходы превышают доходы.
Данные в нашем примере можно было бы перенести вручную, но если вы ведете подробный финансовый учет, цифр может быть много. А еще они могут быть размещены в таблице бессистемно, поэтому вместо долгих поисков лучше использовать ВПР.
Почему ВПР не работает
Бывает, что функция не дает желаемый результат. Обычно это связано с ошибкой при вводе формулы, потому что в ней целых четыре аргумента — легко запутаться. Вот самые популярные проблемы и их решения:
| Проблема | Решение |
| Ошибка #Н/Д — появляется, когда функция не находит искомое значение в таблице | Проверить, нет ли в таблице скрытых ячеек, символов, лишних пробелов |
| Ошибка #ССЫЛ! — в диапазоне указано слишком много столбцов | В формуле нужно учитывать только столбцы диапазона, по которому ищете значения. Столбцы всей остальной таблицы не нужно учитывать |
| Ошибка #ЗНАЧ! — указан неправильный номер столбика в аргументе «индекс» | Нужно перепроверить аргумент и указать целое число больше нуля |
| Ошибка ИМЯ? — идет поиск по запросу, который содержит текст, а не число | Искомый текст нужно заключить в такие кавычки: “текст”. |
Что важно запомнить
- ВПР помогает быстро найти данные и перенести их с одного листа на другой.
- Функция полезна, если ведете подробный финансовый учет на нескольких листах.
- Функция работает только в пределах одной таблицы.
Excel для личных финансов
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
-
Выделить ячейку и вписать функцию.
-
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
В нашем случае получится такая формула:
=ВПР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
=ВПР(A2;
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
=ВПР(A2;$G$2:$H$11
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
-
Формула сканирует таблицу по вертикали.
-
Находит в самом левом столбце совпадение с искомым значением.
-
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
-
Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
=ВПР(A2;$G$2:$H$11;2
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
=ВПР(A2;$G$2:$H$11;2;0)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
-
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
-
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает
Итоги
-
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
-
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
-
Функцию можно вписать вручную или в специальном окне (Shift + F3).
-
Искомое значение – относительная ссылка, а таблица – абсолютная.
-
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
-
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
-
Порядок работы с функцией подходит для Гугл-таблиц.