Применяется кExcel для 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 и те, кто хочет улучшить свои навыки работы с таблицами
- офисные работники, которым требуется автоматизация рутинных задач
- люди, рассматривающие карьеру в аналитике данных или желающие повысить свою квалификацию
Excel часто вызывает настоящую панику у новичков, особенно когда дело касается функций вроде ВПР. Многие представляют себе сложные формулы и непонятные термины, а на деле ВПР — это просто умный поисковик внутри таблиц, который может сэкономить часы работы. Вместо того чтобы вручную искать данные между разными таблицами, эта функция сделает всё за считанные секунды. Сегодня мы разберём ВПР так, что даже самый отъявленный «чайник» в Excel скажет: «Боже, почему я раньше этого не знал?!» 🧮
Устали тратить часы на работу с таблицами? Курс «Excel для работы» с нуля от Skypro превратит ваши мучения в удовольствие! Вы не только освоите ВПР, но и другие мощные функции Excel, которые автоматизируют рутину и впечатлят руководство. Наши выпускники экономят до 3 часов ежедневно на обработке данных. Присоединяйтесь к тем, кто уже перестал бояться сложных таблиц!
Что такое ВПР и почему это важно для новичков в Excel
ВПР (вертикальный просмотр) – одна из самых полезных функций Excel, которая позволяет находить нужную информацию в таблицах данных. По сути, это автоматический поисковик, который избавляет от необходимости вручную просматривать большие массивы данных. 📊
Представьте, что у вас есть список из 1000 товаров с ценами, а в другой таблице — список заказов с наименованиями товаров. Вместо того, чтобы вручную искать цену каждого товара, можно использовать ВПР и получить результат за секунды.
Александр Петров, финансовый аналитик
Когда я только начинал работать с отчетами, каждый месяц мне приходилось сверять продажи по более чем 200 товарным позициям с их актуальными ценами. Это занимало почти два полных рабочих дня.
Однажды мой коллега заметил, как я вручную ищу каждую позицию, и показал мне функцию ВПР. Я был поражен — то, что раньше занимало 16 часов, теперь делалось за 15 минут! Это был настоящий переворот в моей работе.
Самое смешное, что руководитель даже заподозрил, что я перестал выполнять часть обязанностей, пока не увидел, что все отчеты готовы даже раньше срока и без единой ошибки.
Почему ВПР особенно ценен для новичков в Excel:
- Экономия времени — автоматизирует поиск данных
- Снижение количества ошибок — исключает человеческий фактор при копировании информации
- Работа с большими объемами данных — легко справляется с таблицами любого размера
- Основа для более сложных операций — понимание ВПР откроет двери к другим продвинутым функциям
Основы синтаксиса ВПР: пошаговая инструкция с картинками
Функция ВПР может показаться сложной на первый взгляд, но на самом деле она имеет четкую структуру. Давайте разберем её синтаксис по шагам:
Каждый параметр имеет свое предназначение:
- искомое_значение — что именно мы ищем (товар, ID, имя и т.д.)
- таблица_массив — где мы ищем (диапазон ячеек или таблица)
- номер_столбца — в каком столбце находится результат (считаем от начала таблицы)
- интервальный_просмотр — логическое значение: ЛОЖЬ (0) для точного совпадения или ИСТИНА (1) для приблизительного
Рассмотрим пример. У нас есть две таблицы: с сотрудниками и их зарплатами.
- Щелкаем в ячейку, где хотим получить результат
- Вводим знак равенства и функцию:
=ВПР( - Указываем искомое значение (например, ячейку с фамилией сотрудника):
=ВПР(A2; - Выбираем таблицу с данными о зарплатах (например, $G$2:$H$10):
=ВПР(A2;$G$2:$H$10; - Указываем номер столбца с зарплатой (в нашем примере 2):
=ВПР(A2;$G$2:$H$10;2; - Завершаем формулу, указывая точный поиск:
=ВПР(A2;$G$2:$H$10;2;0)и нажимаем Enter
🔑 Важно помнить: искомое значение всегда должно находиться в первом столбце таблицы, в которой вы ищете!
Простой пример ВПР в Excel: поиск данных в таблицах
Давайте разберем практический пример использования ВПР на реальной задаче, с которой может столкнуться любой офисный работник. 🛒
Представим ситуацию: у нас есть таблица с кодами товаров и заказанными количествами (Таблица 1) и отдельная таблица с кодами товаров, их наименованиями и ценами (Таблица 2). Нам нужно автоматически заполнить наименование и стоимость заказа.
Марина Соколова, бухгалтер
До того как я узнала про ВПР, каждый понедельник превращался в кошмар. Мне приходилось обрабатывать отчеты по продажам за неделю — сопоставлять коды товаров с их наименованиями и ценами из прайс-листа, затем вычислять общую стоимость.
В особенно загруженные дни это занимало до 5 часов, и я часто оставалась после работы. Однажды у меня случился нервный срыв из-за того, что в одной из 200 позиций я допустила опечатку, и вся бухгалтерия не сходилась.
Когда я научилась использовать ВПР, процесс сократился до 20 минут! Теперь я даже помогаю коллегам из других отделов автоматизировать их отчеты. А тот день, когда я плакала над таблицами, вспоминаю как страшный сон.
Шаг 1: Создаем таблицы
- Таблица 1 (Заказы): Столбец A — Код товара, Столбец B — Количество
- Таблица 2 (Справочник товаров): Столбец D — Код товара, Столбец E — Наименование, Столбец F — Цена за единицу
Шаг 2: Настраиваем формулу ВПР для получения наименования товара
В столбце C таблицы Заказы вводим формулу:
Эта формула говорит: «Найди значение из ячейки A2 в диапазоне D2:F20 и верни значение из второго столбца этого диапазона (то есть, наименование товара)».
Шаг 3: Настраиваем формулу для расчета стоимости
В столбце D таблицы Заказы вводим формулу для получения цены и расчета общей стоимости:
Эта формула находит цену за единицу и умножает её на количество.
Шаг 4: Копируем формулы вниз по столбцам
Выделяем ячейки с формулами и протягиваем вниз до последней строки с данными.
🎯 Результат: теперь наша таблица заказов автоматически заполняется наименованиями и стоимостями, извлекая данные из справочника товаров.
Преимущества данного подхода:
- Не нужно вручную искать и вводить наименования и цены
- При изменении цен в справочнике все расчеты автоматически обновляются
- Исключается вероятность опечаток и ошибок при вводе
- Можно мгновенно обрабатывать списки любого размера
Работа с ошибками #Н/Д при использовании ВПР в Excel
Ошибка #Н/Д (значение не доступно) — самая распространенная проблема при работе с функцией ВПР. Она возникает, когда Excel не может найти искомое значение. Давайте разберем основные причины появления этой ошибки и способы её устранения. 🔍
Основные причины появления ошибки #Н/Д:
- Искомое значение отсутствует в таблице — просто нет такого значения в первом столбце диапазона поиска
- Различия в форматах данных — например, «100» (текст) и 100 (число) для Excel — разные значения
- Лишние пробелы — невидимые пробелы до или после значений
- Неверный диапазон поиска — искомое значение должно быть в первом столбце указанного диапазона
- Неправильный параметр интервального поиска — используется 1 вместо 0 при необходимости точного совпадения
Варианты решения проблемы:
- Проверьте наличие значения в таблице поиска
- Унифицируйте форматы данных с помощью функций ТЕКСТ() или ЗНАЧЕН()
- Удалите лишние пробелы с помощью функции СЖПРОБЕЛЫ()
- Используйте функцию ЕСЛИОШИБКА для обработки возможных ошибок
Пример использования ЕСЛИОШИБКА для обработки ошибок ВПР:
Эта формула пытается найти значение с помощью ВПР, а в случае ошибки возвращает текст «Товар не найден» вместо #Н/Д.
Для более сложных сценариев можно использовать комбинацию функций:
Эта формула сначала проверяет, существует ли искомое значение в диапазоне поиска, и только потом выполняет ВПР, что может ускорить работу с большими массивами данных.
💡 Профессиональный совет: если вы часто работаете с данными из разных источников, создайте вспомогательную колонку, где значения будут приведены к единому формату с помощью функций СЖПРОБЕЛЫ() и ТЕКСТ() или ЗНАЧЕН(), а затем используйте эту колонку для поиска.
Не знаете, подойдет ли вам карьера в аналитике данных? Пройдите бесплатный Тест на профориентацию от Skypro! Узнайте, насколько ваши навыки работы с Excel и аналитическое мышление соответствуют требованиям современного рынка. Тест определит ваши сильные стороны и предложит оптимальный карьерный путь — возможно, вы уже обладаете талантом к анализу данных, который стоит развивать дальше. Результаты получите мгновенно!
Практические задачи с ВПР для закрепления навыков
Лучший способ освоить функцию ВПР — применить её на практике. Предлагаю выполнить несколько упражнений различной сложности, которые помогут закрепить полученные знания. 📝
Задача 1: Справочник контактов
Создайте две таблицы:
- Таблица 1: Список клиентов (ID, Имя, Email)
- Таблица 2: Список заказов (ID клиента, Дата заказа, Сумма)
Используя ВПР, дополните таблицу заказов именами и электронными адресами клиентов.
Задача 2: Расчет зарплат
Создайте таблицы:
- Таблица 1: Сотрудники (Табельный номер, ФИО, Должность)
- Таблица 2: Ставки (Должность, Базовая ставка, Премиальный %)
- Таблица 3: Рабочие часы (Табельный номер, Отработано часов, Выполнено планов)
С помощью ВПР создайте расчетную таблицу с полями: Табельный номер, ФИО, Должность, Базовая ставка, Отработано часов, Выполнено планов, Премия, Итого к выплате.
Задача 3: Анализ продаж с многоуровневой логикой
Усложним предыдущие примеры. Создайте следующие таблицы:
- Таблица товаров (Код, Наименование, Категория, Базовая цена)
- Таблица скидок (Категория, Минимальное количество, Скидка %)
- Таблица заказов (Номер заказа, Код товара, Количество)
Задача: с помощью ВПР и других функций создайте отчет, который будет показывать для каждой позиции заказа: - Наименование товара
- Категорию
- Базовую цену
- Размер скидки в зависимости от количества и категории
- Итоговую сумму с учетом скидки
Решение задачи 3 (формула для расчета итоговой цены с учетом скидки):
В этой формуле мы сначала находим категорию товара с помощью первого ВПР, затем используем эту категорию для поиска соответствующей скидки с помощью второго ВПР (с параметром 1 для интервального поиска), и наконец рассчитываем итоговую сумму с учетом скидки и количества.
🚀 Дополнительные идеи для практики:
- Создайте таблицу учета расходов с автоматической категоризацией трат
- Разработайте систему учета времени с автоматическим расчетом стоимости проекта
- Сделайте инструмент для анализа успеваемости студентов
- Автоматизируйте заполнение накладных на основе справочника товаров
Помните, что ВПР — это только начало. После его освоения вы можете переходить к более продвинутым функциям, таким как ИНДЕКС, ПОИСКПОЗ, СУММЕСЛИМН и другим, которые расширят ваши возможности в Excel. 🏆
Освоение ВПР — это как получение ключа от потайной двери в мире Excel. То, что казалось невозможным, становится элементарным. За этой дверью открываются безграничные возможности для автоматизации рутинных задач, анализа данных и принятия более обоснованных решений. Не останавливайтесь на достигнутом — продолжайте изучать новые функции и сочетания, и вы удивитесь, как быстро из «чайника» превратитесь в того, к кому коллеги будут обращаться за помощью.
ВПР, или вертикальный поиск, помогает быстро найти в таблице нужные значения и перенести их с одного листа на другой. Расскажем, как работает функция, в каких случаях она полезна и как исправить возможные ошибки.
Вместо Excel мы пользуемся сервисом Google Sheets, потому что он бесплатный и работает онлайн: можно пользоваться с компьютера и телефона. Если Excel привычнее, никаких проблем — в нем те же функции.
Как использовать ВПР в личных финансах
Как устроена функция. ВПР сканирует диапазон значений сверху вниз, пока не найдет нужное. Когда значение находится, функция копирует его в соседнюю таблицу. Затем ВПР продолжает поиск, а когда таблица заканчивается, останавливает работу.
| Функция в Sheets | Функция в Excel | Что делает | Синтаксис |
| ВПР или VLOOKUP | ВПР | Автоматически переносит данные из одной таблицы в другую | =ВПР(искомое значение; таблица; номер столбца; интервальный просмотр) |
Функция работает только в рамках одной таблицы (одного файла). Если таблиц несколько, сначала нужно вручную скопировать данные в одну из них.
Чем функция ВПР полезна в личных финансах. Например, часто учет расходов и доходов ведут на разных листах одной таблицы, чтобы ничего не путалось:
Но что, если хочется собрать расходы и доходы по месяцам на одном листе? Тут и поможет ВПР.
Как работает ВПР: пошаговый алгоритм
Шаг 1. Выделяем ячейку, в которую хотим перенести первое значение.
Шаг 2. Вводим функцию с таким синтаксисом:
| Аргумент | В нашем примере |
| Значение, которое нужно найти. Оно должно быть одинаковым на обоих листах | Расход за месяц: январь, февраль, март и так далее |
| Диапазон, по которому нужно выполнить поиск | Названия месяцев и суммы расходов на листе «Расход» |
| Индекс или номер столбика со значением, которое нужно взять из листа «Расход» | Сумма расходов. Этот столбик идет вторым по счету, поэтому укажем цифру 2 |
| Интервальный просмотр, который определяет, насколько точно работает ВПР. Если значение нужно только при полном совпадении, вводим 0, а при приблизительном — 1. Когда работаете с суммами, всегда нужно полное совпадение, иначе в данных будет путаница | Выберем полное совпадение и введем 0 |
Все аргументы разделяются точкой с запятой. Вот как это выглядит:
Шаг 3. Растянем формулу из заполненной ячейки на все остальные, чтобы в таблицу попали значения по всем месяцам.
Готово! Теперь суммы легко сравнить — сразу понятно, в какие месяцы расходы превышают доходы.
Данные в нашем примере можно было бы перенести вручную, но если вы ведете подробный финансовый учет, цифр может быть много. А еще они могут быть размещены в таблице бессистемно, поэтому вместо долгих поисков лучше использовать ВПР.
Почему ВПР не работает
Бывает, что функция не дает желаемый результат. Обычно это связано с ошибкой при вводе формулы, потому что в ней целых четыре аргумента — легко запутаться. Вот самые популярные проблемы и их решения:
| Проблема | Решение |
| Ошибка #Н/Д — появляется, когда функция не находит искомое значение в таблице | Проверить, нет ли в таблице скрытых ячеек, символов, лишних пробелов |
| Ошибка #ССЫЛ! — в диапазоне указано слишком много столбцов | В формуле нужно учитывать только столбцы диапазона, по которому ищете значения. Столбцы всей остальной таблицы не нужно учитывать |
| Ошибка #ЗНАЧ! — указан неправильный номер столбика в аргументе «индекс» | Нужно перепроверить аргумент и указать целое число больше нуля |
| Ошибка ИМЯ? — идет поиск по запросу, который содержит текст, а не число | Искомый текст нужно заключить в такие кавычки: “текст”. |
Что важно запомнить
- ВПР помогает быстро найти данные и перенести их с одного листа на другой.
- Функция полезна, если ведете подробный финансовый учет на нескольких листах.
- Функция работает только в пределах одной таблицы.
Excel для личных финансов
Что такое функция ВПР в 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 (ЛОЖЬ или ИСТИНА).
- #ЗНАЧ ― выбивает тогда, когда значение в строке слишком длинное.