Впр и гпр в эксель для чайников

В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

Описание

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

Буква Г в аббревиатуре "ГПР" означает "горизонтальный".

Синтаксис

Аргументы функции ГПР описаны ниже.

Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. "Искомое_значение" может быть значением, ссылкой или текстовой строкой.

Таблица — обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или имя диапазона.

Значения в первой строке аргумента "таблица" могут быть текстом, числами или логическими значениями.

Если аргумент "интервальный_просмотр" имеет значение ИСТИНА, то значения в первой строке аргумента "таблица" должны быть расположены в возрастающем порядке: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ, таблица может быть не отсортирована.

В текстовых строках регистр букв не учитывается.

Значения сортируются слева направо по возрастанию. Дополнительные сведения см. в разделе Сортировка данных в диапазоне или таблице.

Номер_строки — обязательный аргумент. Номер строки в аргументе "таблица", из которой будет возвращено соответствующее значение. Если значение аргумента "номер_строки" равно 1, возвращается значение из первой строки аргумента "таблица", если оно равно 2 — из второй строки и т. д. Если значение аргумента "номер_строки" меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе "таблица", возвращается значение ошибки #ССЫЛ!.

Интервальный_просмотр — необязательный аргумент. Логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем "искомое_значение". Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если найти его не удается, возвращается значение ошибки #Н/Д.

Примечание

Если функция ГПР не может найти "искомое_значение" и аргумент "интервальный_просмотр" имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем "искомое_значение".

Если значение аргумента "искомое_значение" меньше, чем наименьшее значение в первой строке аргумента "таблица", функция ГПР возвращает значение ошибки #Н/Д.

Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ и аргумент "искомое_значение" является текстом, в аргументе "искомое_значение" можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функции ВПР и ГПР среди пользователей Excel очень популярны. Первая применяется для вертикального анализа, сопоставления. То есть используется, когда информация сосредоточена в столбцах.

ГПР, соответственно, для горизонтального. Так как в таблицах редко строк больше, чем столбцов, функцию эту вызывают нечасто.

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

Функции имеют 4 аргумента:

1. ЧТО ищем – искомый параметр (цифры и/или текст) либо ссылка на ячейку с искомым значением;
2. ГДЕ ищем – массив данных, где будет производиться поиск (для ВПР – поиск значения осуществляется в ПЕРВОМ столбце таблицы; для ГПР – в ПЕРВОЙ строке);
3. НОМЕР столбца/строки – откуда именно возвращается соответствующее значение (1 – из первого столбца или первой строки, 2 – из второго и т.д.);
4. ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное или приблизительное значение должна найти функция (ЛОЖЬ/0 – точное; ИСТИНА/1/не указано – приблизительное).

! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.

Как пользоваться функцией ВПР в Excel: примеры

Для учебных целей возьмем таблицу с данными:

Формула	Описание	Результат
Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение.
Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено».
Если «бананы» сменить на «груши», результат будет «Найдено»
Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА.	Мы узнаем, были ли продажи 05.08.15
Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными.	Мы захотели узнать, кто работал 8.06.15.
Поиск приблизительного значения.

1. Функция ВПР всегда ищет данные в крайнем левом столбце таблицы со значениями.
2. Регистр не учитывается: маленькие и большие буквы для Excel одинаковы.
3. Если искомое меньше, чем минимальное значение в массиве, программа выдаст ошибку #Н/Д.
4. Если задать номер столбца 0, функция покажет #ЗНАЧ. Если третий аргумент больше числа столбцов в таблице – #ССЫЛКА.
5. Чтобы при копировании сохранялся правильный массив, применяем абсолютные ссылки (клавиша F4).

Как пользоваться функцией ГПР в Excel: примеры

Для учебных целей возьмем такую табличку:

Формула	Описание	Результат
Поиск значения ячейки I16 и возврат значения из третьей строки того же столбца.
Еще один пример поиска точного совпадения в другой табличке.

Применение ГПР на практике ограничено, так как горизонтальное представление информации используется очень редко.

Символы подстановки в функциях ВПР и ГПР

Случается, пользователь не помнит точного названия. Задавая искомое значение, он может применить символы подстановки:

• «?» — заменяет любой символ в текстовой или цифровой информации;
• «*» — для замены любой последовательности символов.

1. Найдем текст, который начинается или заканчивается определенным набором символов. Предположим, нам нужно отыскать название компании. Мы забыли его, но помним, что начинается с Kol. С задачей справится следующая формула: .
2. Нам нужно отыскать название компании, которое заканчивается на — "uda". Поможет следующая формула: .
3. Найдем компанию, название которой начинается на "Ce" и заканчивается на –"sef". Формула ВПР будет выглядеть так: .

Когда проблемы с памятью устранены, можно работать с данными, используя все те же функции.

Как сравнить листы с помощью ВПР и ГПР

У нас есть данные о продажах за январь и февраль. Эти таблицы необходимо сравнить с помощью формул ВПР и ГПР. Для наглядности мы пока поместим их на один лист. Но будем работать в условиях, когда диапазоны находятся на разных листах.

Как сравнить листы с помощью ВПР в Excel?

Решим проблему 1 : сравним наименования товаров в январе и феврале. Так как в феврале их больше, вводить формулу будем на листе «Февраль».

Решим проблему 2 : сравним продажи по позициям в январе и феврале. Используем следующую формулу:

Как сравнить листы с помощью ГПР в Excel?

Для демонстрации действия функции ГПР возьмем две «горизонтальные» таблицы, расположенные на разных листах.

Задача – сравнить продажи по позициям за январь и февраль.

Создаем новый лист «Сравнение». Это не обязательное условие. Сопоставлять данные и отображать разницу можно на любом листе («Январь» или «Февраль»).

Проанализируем части формулы:

«Половина» до знака «-»:

. Искомое значение – первая ячейка в таблице для сравнения. Анализируемый диапазон – таблица с продажами за февраль. Функция ГПР «берет» данные из 2 строки в «точном» воспроизведении.

. Все то же самое. Кроме диапазона. Здесь берется таблица с продажами за январь.

Когда мы вводим формулу, Excel подсказывает, какой сейчас аргумент нужно ввести.

С помощью функции ВПР (в переводе на английский VLOOKUP) пользователи программы Exсel имеют возможность переставлять данные из одной таблицы в другую со схожими параметрами. Эта услуга подойдёт для тех, кому приходится работать с большими списками. Ведь вписывать каждое значение отдельно может занять очень большое количество времени.

Использование ВПР в программе Excel

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

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

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

Этапы работы (инструкция):

1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
   
2. Отметить верхнее поле в новом.
   
3. Выбрать раздел формулы, и нажать «Вставить функцию».
   
4. Из предложенных категорий Excel отметить «Ссылки и массивы».
   
5. Найти ВПР, и нажать «ОК».
   
6. Заполнить открывшееся окно «Аргументы».
   

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

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

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

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

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Делается это в несколько шагов:

1. Открыть первую со старой информацией.
   
2. Добавить дополнительный столбик для новых данных «Новая стоимость».
   
3. Выделить первое пустое поле в созданном столбце (С2).
   
4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
   
5. Найти категорию Excel «Ссылки и массивы».
   
6. Выбрать ВПР.
7. Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

Поиск с помощью ВПР по нескольким условиям

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

Итак, имеется документ, в котором обозначены: компании, товары и цены.

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

Чтобы осуществить поиск следует:

1. Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

• выделить крайнюю левую ячейку (А1);
• щёлкнуть ПКМ и выбрать «Вставить»;
• отметить добавление столбца и нажать «ОК».

1. Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
   
   
2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
   
3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

Как сделать выпадающий список через функцию ВПР

Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
   
2. Зайти в раздел «Данные», и выбрать «Проверка данных».
   
3. Установить тип данных, как список.

1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).
   

Выпадающий список готов.

Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

1. Создать новое поле с названием «Цена».
   
2. Вставить аргументы.

– ячейка Excel, в которой находится выпадающий список (Е2).

– выделенный фрагмент с предметами и ценами (А2-В5).

Номер столбца – 2 (в нём находятся цены).

Интервальный просмотр – ЛОЖЬ.

После подтверждения команды можно пользоваться списком и просмотром цены.

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