Как пользоваться впр в excel

Пример формулы с ВПР и ПОИСКПОЗ

Пример таблицы табель премии изображен ниже на рисунке:

Назначением данной таблицы является поиск соответственных значений премии в диапазоне B5:K11 на основе определенной сумы выручки и магазинов с пределами минимальных или максимальных размеров выплаты премии. Сложность возникает при автоматическом определении размера премии, на которую может рассчитывать сотрудник при преодолении определенной границы выручки. Так как нет четко определенной одной суммы выплаты премии для каждого вероятного размера выручки. Есть только пределы нижних и верхних границ сумм премий для каждого магазина.

Например, нам нужно чтобы программа автоматически определила какая возможная минимальная премия для продавца из 3-тего магазина, выручка которого преодолела уровень в 370 000.

Для этого:

1. В ячейку B14 введите размер выручки: 370 000.
2. В ячейке B15 укажите номер магазина: 3.
3. В ячейке B16 введите следующую формулу:

В результате определена нижняя граница премии для магазина №3 при выручке больше >370 000, но меньше <400 000.

Поиск ближайшего значения Excel формулой ВПР и ПОИСКПОЗ:

В первом аргументе функции ВПР указываем ссылку на ячейку с критерием поискового запроса (исходная сумма выручки), который содержится в ячейке B14. Область поиска в просматриваемом диапазоне A5:K11 указывается во втором аргументе функции ВПР. А в третьем аргументе должен быть указан номер столбца, но он пока неизвестен. Из второго критерия поискового запроса известно только что исходный номер столбца таблицы относится к 3-тьему магазину (ячейка B15).

Чтобы определить номер столбца, который содержит заголовок «Магазин 3» следует использовать функцию ПОИСКПОЗ. Как само название функции говорит о том, что ее задачей является поиск позиции где находится значение внутри определенного диапазона ячеек. В нашем случаи мы ищем значение: «Магазин 3», которое следует еще определить используя конструкцию сложения амперсандом текстовой строки «Магазин » и критерий из ячейки B15. Поэтому в первому аргументе функции указываем «Магазин »&B15. Во втором аргументе функции ПОИСКПОЗ указывается ссылка на просматриваемый диапазон A3:J3 где нужно искать исходное значение (указанное в первом аргументе). Третий аргумент содержит значение 0 – это значит, что функция возвратит результат, как только найдет первое совпадение значений. В нашем примере значение «Магазин 3» находится на позиции номер 6 в диапазоне A3:J3, а значит функция ПОИСКПОЗ возвращает число 6 которое будет использовано в качестве значения для третьего критерия функции ВПР. Есть еще и четвертый аргумент в функции ВПР который определяет точность совпадения найденного значения с критерием (0-точное совпадение; 1 или пусто – приближенное совпадение), но в формуле он опущен по следующей причине. Получив все аргументы функция ВПР не находит значения 370 000 и так как не указан последний аргумент выполняет поиск ближайшего значения в Excel – 350 000.

Поняв принцип действия выше описанной формулы, на ее основе можно легко составить формулу для автоматического поиска максимально возможной премии для продавца из 3-тьего магазина. Измененная формула будет находится в ячейке B17 и получит следующий вид:

Легко заметить, что эта формула отличается от предыдущей только номером столбца указанном в третьем аргументе функции ВПР. А, следовательно, нам достаточно лишь к значению, полученному через функцию ПОИСКПОЗ добавить +1, так как сумма максимально возможной премии находиться в следующем столбце после минимальной суммы соответствующий критериям поискового запроса.

Полезные советы для формул с функциями ВПР, ИНДЕКС и ПОИСКПОЗ:

Чтобы пошагово проанализировать формулу Excel любой сложности, рационально воспользоваться встроенными инструментами в разделе: «ФОРМУЛЫ»-«Зависимости формул». Например, особенно полезный инструмент для пошагового анализа вычислительного цикла – это «Вычислить формулу».

Функция ВПР ищет значения в диапазоне слева на право. То есть анализирует ячейки только в столбцах, расположенных с правой стороны относительно от первого столбца исходного диапазона, указанного в первом аргументе функции. Если структура расположения данных в таблице не позволяет функции ВПР по этой причине охватить для просмотра все столбцы, тогда лучше воспользоваться формулой из комбинации функций ИНДЕКС и ПОИСКПОЗ.

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

​ заключается в том,​​ в поле имени​​ вкладке меню «Правка»​Номер столбца — его​ исключительно по столбцам,​ точное соответствие. Например,​среди них самая​ то можем просто​первом левом столбце​​ функций

Обратите внимание​​ ссылке добавляются знаки​ Попросту говоря, ВПР​ функции.​ – «Не найдено».​​ первичные значения, то​​ нужно перепроверить данные.​ профессионально. Некоторые данные​ чтобы искать совпадения​

​ (слева под панелью​​ — «Найти» вставить​​ порядковое число, в​ а не по​ на рисунке ниже​ распространенная. В этом​ изменить первый аргумент:​указанного диапазона. В​ на раздел​ доллара и она​ позволяет переставлять значения​

Пример 1

​У нас есть данные​Если «бананы» сменить на​ никаких символов и​Об этом стоит поговорить​ пользователю нужно держать​ в одной или​ вкладок) присваивается ей​

​ данную запись, запустить​​ котором располагается сумма​​ строкам. Для применения​ формула вернет ошибку,​

​ уроке мы познакомимся​=VLOOKUP(«T-shirt»,A2:B16,2,FALSE)​​ этом примере функция​​Формулы и функции​ превращается в абсолютную.​ из ячейки одной​ о продажах за​ «груши», результат будет​ пробелов между названиями​ детально. Ни для​ в голове (номер​ нескольких таблицах. Так​ название.​ поиск. Если программа​ продаж, то есть​​ функции требуется минимальное​​ поскольку точного соответствия​ с функцией​

Пример 2

​=ВПР(«T-shirt»;A2:B16;2;ЛОЖЬ)​ будет искать в​нашего самоучителя по​В следующей графе​ таблицы, в другую​ январь и февраль.​ «Найдено»​

​ строк и столбцов​ кого не секрет,​ строки или столбца).​ можно легко найти​Другой вариант — озаглавить​ не находит его,​ результат работы формулы.​​ количество столбцов -​

​ не найдено.​ВПР​​или:​​ столбце​ Microsoft Excel.​«Номер столбца»​ таблицу. Выясним, как​ Эти таблицы необходимо​Когда функция ВПР не​ не нужно ставить.​ что абсолютно в​ В противном случае​ интересующую информацию, затратив​

​ — подразумевает выделение​ значит оно отсутствует.​Интервальный просмотр. Он вмещает​ два, максимальное отсутствует.​Если четвертый аргумент функции​, а также рассмотрим​=VLOOKUP(«Gift basket»,A2:B16,2,FALSE)​A​ВПР​​нам нужно указать​​ пользоваться функцией VLOOKUP​ сравнить с помощью​ может найти значение,​Также рекомендуется тщательно проверять​ разных сферах используется​

​ он не сможет​ минимум времени.​​ диапазона данных, потом​​Форматы ячеек колонок А​ значение либо ЛОЖЬ,​Функция ВПР производит поиск​ВПР​ ее возможности на​=ВПР(«Gift basket»;A2:B16;2;ЛОЖЬ)​значение​

​работает одинаково во​​ номер того столбца,​​ в Excel.​ формул ВПР и​ она выдает сообщение​ таблицу, чтобы не​ функция ВПР Excel.​ найти нужную ему​Функция ВПР Excel –​​ переход в меню​​ и С (искомых​ либо ИСТИНА. Причем​

​ заданного критерия, который​содержит значение ИСТИНА​ простом примере.​Следующий пример будет чуть​Photo frame​​ всех версиях Excel,​​ откуда будем выводить​Взглянем, как работает функция​

Горизонтальный ВПР в Excel

​ ГПР. Для наглядности​ об ошибке #Н/Д.​​ было лишних знаков​​ Инструкция по её​ информацию.​​ что это такое?​​ «Вставка»- «Имя»- «Присвоить».​ критериев) различны, например,​ ЛОЖЬ возвращает только​ может иметь любой​ или опущен, то​​Функция​​ потруднее, готовы? Представьте,​. Иногда Вам придётся​ она работает даже​ значения. Этот столбец​ ВПР на конкретном​ мы пока поместим​ Чтобы этого избежать,​ препинания или пробелов.​

​ применению может показаться​Чтобы говорить о том,​ Её также называют​Для того чтобы использовать​

​ у одной -​ точное совпадение, ИСТИНА​

​ формат (текстовый, числовой,​ крайний левый столбец​ВПР​ что в таблице​ менять столбцы местами,​​ в других электронных​​ располагается в выделенной​ примере.​ их на один​ используем функцию ЕСЛИОШИБКА.​ Их наличие не​ сложной, но только​ как работает функция​ VLOOKUP в англоязычной​

​ данные, размещенные на​​ текстовый, а у​ — разрешает поиск​ денежный, по дате​ должен быть отсортирован​(вертикальный просмотр) ищет​

​ появился третий столбец,​

office-guru.ru>

Системы очистки воды

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

Рассмотрим основные системы очищения воды:

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

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

Скиммерная. Подходит для бассейнов не очень большого объема. Уровень воды всегда должен быть ниже верхней границы бортика на 15-20 сантиметров. Эта система проста в обслуживании, недорого стоит, но нужно понимать, что периодически бассейн будет требовать частичной смены воды.

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

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

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

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

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены»:

=ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel – Функция ВПР на разных листах

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

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

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле «Имя», слева от панели «Формула».

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

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

=ВПР(«Продукт 1»;Продукты;2)

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

=ВПР(«Продукт 1»;Таблица6:];2)

или даже =ВПР(«Продукт 1»;Таблица6;2).

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

ВПР в Excel (эксель) – что это за функция, пошаговая инструкция для чайников, формула ВПР, примеры использования

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

ВПР (VLOOKUP в английском варианте) расшифровывается, как вертикальный просмотр. Функция является одной из самых востребованных в Excel. Она позволяет, к примеру, легко отыскать и сопоставить телефонные данные человека или организаций из справочной таблицы по его имени. Можно сопоставить цену товаров по их наименованиям. Эти и многие другие возможности предоставит для Вас функция ВПР. Пользоваться ей достаточно просто.

Использование функции

Рассмотрим структуру ВПР, какие аргументы она задействует.  Как и любая другая функция в Excel начинается она со знака равенства (=). Далее имя функции и аргументы, заключенные в круглые скобки.

ВПР содержит 4 аргумента.

Функция ведет поиск искомого значения в крайнем левом столбце и производит возврат значения в той же строке из указанного столбца.

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

Номер столбца должен включать столбец для ответа, он находится правее от столбца с исходным значением.

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ, 1 —  ИСТИНА. отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1 – приблизительный.

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

Примеры использования

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

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

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.

Для второго аргумента выделяем диапазон таблицы.

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК.

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример.

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней – 200-300 и т.д.

Искомым значением в данном случае будет количество, Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

Используем ВПР, чтобы сравнить две таблицы.

Создадим именованный диапазон B4:C19 и назовем его «прайс1». Так нам будет проще ссылаться на первоначальные данные.

Добавим к новым данным еще одну колонку и назовем ее «Цена старая». Для каждого наименования из прайс-листа №2 найдем соответствующую ему цену в №1.

В Н4 вводим формулу

и копируем ее вниз по столбцу.

Видим, что кое-где изменилась цена, и в четырех наименованиях формула ВПР возвратила ошибку #Н/Д. Это означает, что ранее этих товаров не было и цену для них обнаружить не удалось.

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

Для этого используем функцию ЕСЛИОШИБКА и вместо #Н/Д выведем ноль.

Изменим нашу формулу:

Теперь мы можем рассчитать отклонения новой цены от старой.

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

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

Формула в J4:

Выглядит сложно и громоздко, но на самом деле все просто. Основа здесь та же, что и ранее: поиск в первой таблице «старой» цены каждого товара из новых данных.

То есть, ключевым является выражение  ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0).

Если найденное значение равно «новой» цене из ячейки G4, то выводим пустой пробел “”.

Значения смежных ячеек привязаны к этому результату.

Если ячейка J4 пуста, тогда ничего не выводим и в остальных:

Формула в I4:

в К4:

в L4:

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

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

То есть, сравнивать придется в двух направлениях.

Согласитесь, не всегда хочется делать двойную работу.

Аргумент «Таблица»

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

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

Как сделать ВПР в Excel: понятная пошаговая инструкция.

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

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

Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.

Для этого в G2 запишем следующую формулу:

А теперь разберем подробно, как сделать ВПР.

Мы берем значение из E2.
Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой)

Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
Взять из него цену и вставить ее в нашу ячейку G2.

Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.

Отчет о продажах готов.

Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.

Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?

Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.

1. Берем значение из F2.
2. Начинаем его искать в крайней левой колонке диапазона $A$2:$B$7, то есть в столбце A. Поскольку в A2 совпадение не найдено, смотрим дальше: что находится ниже.
3. Там обнаруживаем товар «Сливы». При этом предполагается, что наш список отсортирован по алфавиту. Ведь именно это – главное условие поиска приблизительного совпадения.
4. Поскольку в сортированном списке «сливы» находятся ниже, чем «бананы», то функция принимает решение, что дальше искать слово, начинающееся на «Б» нет смысла. Процесс можно остановить. И остаться на букве «А». То есть, там и находится наиболее близкое значение.
5. Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.

К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.

При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?

Внимание: неточный поиск включен по умолчанию!

К сожалению, четвертый аргумент является необязательным и по умолчанию имеет значение ИСТИНА. Это означает, что программа ищет не именно ваше, а просто похожее значение по умолчанию, даже если вы указали только 3 аргумента.

Если аргумент используется ЛОЖЬ (или ноль), то сортировка не обязательна, поскольку ищется точное соответствие.

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

Чтобы избежать этой проблемы, обязательно используйте ЛОЖЬ или ноль в качестве 4-го аргумента, когда нужен именно точный поиск.

В подавляющем большинстве случаев используется точное сравнение: если в прайс-листе найдется точно такое же название товара, то программа выведет его цену. В противном случае мы получим ошибку #N/A.

Вы спросите – а зачем же тогда этот параметр, если его значение ИСТИНА (TRUE) или же отсутствие приводит к таким проблемам? Ответ заключается в том, что если всё же вы будете применять её на отсортированном массиве, то производительность и скорость вычислений возрастут по разным оценкам где-то в 50 (пятьдесят!) раз. При работе с большими объемами данных это будет очень заметно.

Поэтому, если есть такая возможность, старайтесь сортировать большие массивы данных. Если делать всё без ошибок, то производительность Excel вырастет многократно.

Поэтому мы рекомендуем всегда устанавливать 4-й аргумент явным образом, даже если в конкретной ситуации этого не требуется. Таким образом, у вас всегда есть визуальное напоминание о режиме поиска, который вы используете.

Как работает функция?

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

С одним условием

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

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.

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

3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.

4. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.

2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.

2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.

2. Перейти в раздел книги «Данные» – «Проверка данных».

3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

4. Нажать «Ок». Отобразится список.

5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Именованные диапазоны облегчают понимание расчетов и еще больше упрощают работу.

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

Копировать и переносить их также можно без проблем.

В приведенном выше примере с данными о сотрудниках вы можете назвать входную ячейку B2 «фамилия», а затем выделить все ячейки с информацией и назвать диапазон B5:F100 как «ДанныеСлужащего». Затем перепишите свою формулу в C2 следующим образом:

Сравните сами — насколько понятнее стал расчет из совета №12 по сравнению с №11.

Аргумент «Искомое значение»

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

При использовании текста в качестве искомого значения , его необходимо взять в кавычки. При ссылке на ячейку с текстом кавычки не нужны. Регистр при вводе текста значения не имеет. Может использоваться нечеткий поиск, по фрагменту текста. Для этого внутри кавычек нужно заключить фрагмент текста в символы *.

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

Перевести в числовой формат просто — нужно умножить массив на единицу.

Функция ВПР в Excel. Поиск и возвращение текста в Эксель формулой. VLOOKUP

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

• Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
• Таблица — все столбцы Таблицы1, приче первый столбец, должен быть который мы ищем(Номер ТС)
• Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
• — может принимать только два значения Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

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

Итак напишем функцию для нашей задачи:

E:E — это диапазон значений по которым Excel будет сравнивать условия с Таблицей 1. A:B —  вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично, значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А так можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

Комментарии по ВПР

• Если для одной ячейки в Таблице2 есть несколько значений в Таблице1 — будет выдаваться первое значение.
• Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
• Чтобы определить какой именно номер столбца записать в формулу, при выделение диапазона показывается, номер столбца.

Скачать пример

Что в итоге:

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

Пишите комментарии, буду рад помочь!

3 кит Excel — это сводные таблицы. Подробнее здесь.

(Visited 3 614 times, 1 visits today)

Заключение

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