Содержание

Функция ВПР в Excel – примеры использования и советы

Найти значение в таблице поможет функция ВПР в Excel примеры которой описаны ниже в статье.

Во время работы с программой у пользователей часто появляется необходимость быстрого поиска информации в одной таблице и переносе её в другой объект листа.

Понимание принципа работы ВПР существенно упростит вашу работу в Excel и поможет быстрее выполнять задачи.

Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр. Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек. Чаще пользователи отдают предпочтение именно функции ВПР, ведь большинство таблиц имеют больше строк, нежели столбцов.

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций. Посмотреть правильный вид опции можно, открыв табличный процессор:

Используйте уже созданный документ, либо откройте новый пустой лист;
Кликните на клавишу «Формулы» , как показано на рисунке ниже;
В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
Настройте категорию «Полный перечень» ;
Кликните на «Найти» .

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула. За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых скобах. Общий вид описания для ВПР выглядит так:

Рассмотрим детальнее каждое из значений, которое описывается в скобках:

— первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
— тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
— здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
— этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР можно пропустить, и тогда его значение по умолчанию принимается как истина.

Как работает ВПР. Полезный пример

Чтобы лучше понять принцип работы VLOOKUP, перейдём к рассмотрению конкретных примеров. Возьмём простейшую таблицу с двумя колонками. Пусть она обозначает код и наименование товара.

После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP. Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.

Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.

Когда использовать ВПР?

Первая вариация VLOOKUP подойдет для следующих случаев:

Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

Первый вариант правописания VLOOKUP не может найти элемент, если не было найдено значения меньше искомого или равное ему. В ячейке для результата вернется только «Н/Д».

Второй вариант для ВПР (с указанием «0» для сортировки) применяется для больших таблиц, в которых встречаются одинаковые названия для нескольких ячеек. VLOOKUP позволит легко оперировать данными, ведь возвращает первую найденную строку.

В реальной жизни опция используется, когда нужно осуществить поиск по заданному диапазону – он не обязательно должен соответствовать всей величине таблицы. В объектах листа, в которых встречаются разные виды значений, ВПР помогает найти текстовые строки.

ВПР бывает полезна, когда нужно удалить много лишних пробелов. Функция быстро находит все наименования с пробелами, и вы сможете быстро удалить их. Пример:

Быстродействие VLOOKUP

Большинство пользователей предпочитать не вписывать параметр во время работы с функцией. Конечно же, ноль вписать проще, но игнорирование оператора замедляет поиск. При работе с большими массивами данных Эксель может работать слишком медленно. На старых устройствах табличный процессор иногда даже зависает из-за слишком медленного поиска с ВПР.

Если на одном листе вашего документа представлено сразу несколько тысяч формул, лучше позаботиться о сортировке первого столбца. Это позволяет увеличить общую производительность поиска на целых 400%-500%.

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

[expert_bq id=»1570″]Чтобы привести данные в требуемый вид, необходимо щёлкнуть по неправильной ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт Формат ячеек. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Назначение функции ВПР — найти и вернуть в ячейку любое значение из исходной таблицы на основании указанных данных. Особенно полезна эта опция Excel, когда необходимо извлечь информацию из огромной сводной таблицы, сопоставить несколько источников данных или осуществить быстрый поиск по нескольким параметрам — примеры решения каждой из перечисленных задач будут приведены ниже.

Функция ВПР в Excel: пошаговая инструкция с примерами для чайников |

— первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
— тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
— здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
— этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР можно пропустить, и тогда его значение по умолчанию принимается как истина.

Поскольку в Excel формула ВПР применяется обычно для обработки больших объёмов данных, то лучше сразу приучаться заполнять только одну формулу, а в остальные ячейки копировать данные. Ссылка на статью по особенностям копирования формул приводилась выше.

Функция ВПР в Excel – примеры использования и советы

Найти значение в таблице поможет функция ВПР в Excel примеры которой описаны ниже в статье.

Понимание принципа работы ВПР существенно упростит вашу работу в Excel и поможет быстрее выполнять задачи.

Сама аббревиатура ВПР означает «вертикальный просмотр».

Анализ данных и их поиск в таблице осуществляется с помощью постепенного перебора элементов от строки к строке в каждой колонке.

Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр.

Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек.

Чаще пользователи отдают предпочтение именно функции ВПР, ведь большинство таблиц имеют больше строк, нежели столбцов.

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.

Используйте уже созданный документ, либо откройте новый пустой лист;
Кликните на клавишу «Формулы» , как показано на рисунке ниже;
В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
Настройте категорию «Полный перечень» ;
Кликните на «Найти» .

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула.

За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых скобах.

Рассмотрим детальнее каждое из значений, которое описывается в скобках:

— первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
— тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
— здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
— этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР можно пропустить, и тогда его значение по умолчанию принимается как истина.

Как работает ВПР. Полезный пример

После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP.

Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.

На рисунке выше в цветных ячейках указывается значение для товара. Если вы не ввели значение для сортировки, то функция автоматически воспринимает это как единицу.

Далее программа «думает», что элементы первого столбика вашей таблицы идут по возрастанию сверху-вниз.

Благодаря этому, процедура поиска будет остановлена только когда будет достигнута строка со значением, номер которого уже превышает искомый объект.

Рассмотрим еще один пример использования функции, который часто встречается во время реальной работы с прайсами и листами наименований товара.

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

Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.

Когда использовать ВПР?

Первая вариация VLOOKUP подойдет для следующих случаев:

Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

Второй вариант для ВПР (с указанием «0» для сортировки) применяется для больших таблиц, в которых встречаются одинаковые названия для нескольких ячеек.

VLOOKUP позволит легко оперировать данными, ведь возвращает первую найденную строку.

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

В объектах листа, в которых встречаются разные виды значений, ВПР помогает найти текстовые строки.

[expert_bq id=»1570″]Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка Н Д, то диапазон таблицы не закреплен. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

В Чем Отличие Функций Впр и Гпр Используемых в Программе ms Excel • Функция впр инструкция

Впр и гпр в одной формуле. Функция ВПР, ГПР, пошаговая инструкция. Уроки Excel.

— первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
— тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
— здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
— этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР можно пропустить, и тогда его значение по умолчанию принимается как истина.

Функция ВПР в Excel

Функция ВПР в Excel часто требуется на практике при обработке больших массивов данных, но при этом с этой же функцией чаще всего возникают проблемы при её использовании. В статье описана пошаговая инструкция по функции ВПР «для чайников», а также приведены примеры и видеоурок.

В конце статьи Вы можете скачать два файла с примерами использования ВПР в Эксель, а также типовую справку Excel по данной функции.

Также рекомендуем потом прочитать про функцию ГПР, по смыслу очень похожую и не менее часто используемую.

Функция ВПР для чайников

Функция ВПР применяется для совмещения данных из разных таблиц.

На первый взгляд выглядит совсем непонятно. Но на самом деле всё очень просто, если рассмотреть простейший пример использования функции ВПР.

Синтаксис функции ВПР

Прежде чем переходить к рассмотрению примера, посмотрим на синтаксис функции ВПР, то есть какие параметры она принимает и для чего они нужны.

ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Важно! Параметр 2 (таблица) нужно указывать так, чтобы в диапазон ячеек не попадали заголовки таблицы, если они есть. Нужны только сами данные.

В простом примере ниже эти аргументы рассмотрены на практике.

Функция ВПР, пошаговая инструкция

Итак, изначально у нас есть две таблицы, которые Вы можете видеть на скриншоте ниже. Общим столбцом для обеих таблиц является «Должность».

Цель: добавить в первую (верхнюю) таблицу в столбец «Зарплата» данные из аналогичного столбца второй (нижней) таблицы.

Если Вы подумали, что это можно сделать и вручную, то это большая ошибка, поскольку строк в таблицах может быть многие тысячи, а порядок их следования в обеих таблицах вовсе не обязан совпадать!

При помощи же функции ВПР мы можем использовать Excel для совмещения двух таблиц, причём очень быстро. Начнём с ячейки C4 в первой таблице и на её примере покажем простую инструкцию по вставке функции ВПР.

Функция ВПР, инструкция

Добавим ВПР в ячейку.
Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.
Указываем параметр «Искомое значение»
В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).
Указываем параметр «Таблица».
Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12». Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.
Указываем параметр «Номер столбца».
Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).
Указываем параметр «Интервальный просмотр».
В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.

Обратите внимание на символ «$» перед номерами строк в диапазоне (аргумент ВПР номер 2). Это нужно для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки.

В результате для ячейки C4 мы получим следующую формулу:
=ВПР(B4; A$10:B$12; 2; 0)

Как работает функция ВПР

В нашем примере функция ВПР в Эксель для ячейки C4 работает следующим образом. Excel ищет слово «Директор» в колонке «Должность» второй таблицы (как и было указано ранее, поиск выполняется в первом столбце указанного диапазона). Искомое значение найдено во второй строке второй таблицы (заголовки не считаем).

Поскольку в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в результате то, что указано в колонке «Зарплата».

Это значение и будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше Вы можете видеть, что значение «40 000» вписано в ячейку C4 первой таблицы.

Скачать файл Excel (*.xlsx) с этим примером Вы можете после статьи.

Заполним аналогичными формулами остальные ячейки

В данном случае лишь необходимо учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен меняться. Именно поэтому перед номерами строк в диапазоне поставлены символы «$». Для первого аргумента (искомое значение) это, напротив, не нужно, поскольку для каждой строки первой таблицы искомое значение будет в разной ячейке (в нашем примере: A4, A5, A6).

Почему не работает функция ВПР

Если Вы не можете получить нужный результат, значит либо во второй таблице (аргумент 2) нет совпадений, либо Вы неверно задаёте параметры ВПР. Вот несколько типичных ошибок при использовании данной формулы на практике:

Не указали символ доллара в диапазоне (аргумент 2).
В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
Неверно указан диапазон поиска.
Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
Неверно указан номер столбца (аргумент 3).
Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

Если Вы хотите изучить Excel, воспользуйтесь специализированным учебным видеокурсом по данной программе. С примерами некоторых уроков и учебным планом можно познакомиться по ссылке.

Функция ВПР в Excel, примеры

Более сложный пример использования функции ВПР можно посмотреть во втором прикреплённом файле после статьи, он называется «Использование функции ВПР на примере.zip». В архиве файл формата *.xlsx, в котором рассмотрен пример совмещения двух таблиц, находящихся на разных листах Эксель.

Пример представляет из себя практическую задачу объединения двух выгрузок из программы 1С:Бухгалтерия — в одной из них находится список товаров, а в другой цены. В результате к списку товаров добавляется цена, причём это может быть как цена покупки, так и цена продажи.

Также в примере задействованы и некоторые другие формулы, например функция ЕСЛИ.

Пример функции ВПР Вы можете придумать и сами: если интересуетесь применением данной формулы, то у Вас в любом случае должна быть причина, то есть исходные данные, требующие обработки. Если решите задачу самостоятельно, напишите в комментариях как именно Вы это сделали — это поможет другим пользователям лучше понять работу в Excel!

Если же всё ещё остались вопросы по применению ВПР на практике, посмотрите учебное видео к данной статье.

Уникальная метка публикации: E196CFD0-BACD-89F6-F4CC-6B7DD0AF035B
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/funkcziya-vpr-v-excel/

Смотреть видео
Функция ВПР в Excel

Прикреплённые документы

Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.

Файлы для загрузки

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

[expert_bq id=»1570″]Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников инструкция. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать именованный диапазон для него и ввести имя непосредственно в аргументе таблица вашей формулы ВПР.

Функция ВПР в Excel | MS Office | Учебные статьи

Добавим ВПР в ячейку.
Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.
Указываем параметр «Искомое значение»
В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).
Указываем параметр «Таблица».
Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12». Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.
Указываем параметр «Номер столбца».
Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).
Указываем параметр «Интервальный просмотр».
В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.