Примеры функции впр в 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>

ВПР в 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) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

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

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

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

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

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. Протянуть формулу вниз, чтобы заполнить все ячейки.

Вопросы от новичков

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

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

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

Открыть обе таблицы, а при необходимости — перенести их на один лист.

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

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

Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

Как с помощью ВПР сделать выпадающий список?

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

Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

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

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

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

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

Нужно точное совпадение

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

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

Необходима фиксация ссылок на таблицу

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

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

Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать

Очень хорошо это видно на примере ниже. Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать.

19

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

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

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

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

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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

Использование формул массива вместе с ВПР.

Здесь все гораздо сложнее. Вновь вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Прайс1» и «Прайс2».

Создадим из наименований товаров в каждой из таблиц именованный диапазон, как это показано на рисунке.

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».

В ячейке A5 запишем формулу

Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.

В  результате получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований товаров.

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

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

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

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.

Скопируйте эту формулу по столбцу вниз. Список уникальных значений готов.

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

Запишем в В5:

Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).

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

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…

Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

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

Задача2. Поиск ближайшего числа

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

Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:

1. Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
2. Ключевой столбец должен быть обязательно отсортирован по возрастанию;
3. Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист «Поиск ближайшего числа»). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.

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

Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО. Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.

Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).

Примечание: Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).

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

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

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

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

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

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

Функция ВПР в 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.

1

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

2

Наша задача – определить, сколько стоит каждый из поступивших на склад товаров. Чтобы достичь этой цели, необходимо записать в первой таблице стоимость из второй. После этого умножить одно значение на другое. Так можно определить искомое.

Если говорить более точно, последовательность действий следующая:

1. Приводим внешний вид таблицы в нужный нам вид путем вставки двух колонок, которые называются «Цена» и «Стоимость/Сумма». При этом нужно применить к ячейкам денежный формат. 

2. Нажимаем на ячейку, являющуюся первой в нашей колонке «Цена». В случае с нами она имеет адрес D2. С использованием мастера функций пользователь всегда может найти ВПР в категории «Ссылки и массивы», независимо от версии Excel. А для вызова мастера функций есть два метода. Первый – это нажать на кнопку fx рядом со строкой ввода формулы. Второй же – комбинация клавиш SHIFT + F3. После того, как нужная нам функция будет выбрана, надо нажать на клавишу ОК, чтобы подтвердить свои действия. Есть еще один способ вызова этой функции. Нужно перейти на вкладку «Формулы» и там найти тот же пункт «Ссылки и массивы».

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

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

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

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

   • Номер столбца. В этом аргументе мы пишем цифру два.

   • Интервальный просмотр. Этот параметр нужен, если ищутся только приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем второй вариант, поскольку нам требуется точная информация.

4. После этого нажимаем кнопку «ОК».
5. Далее функция размножается на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 

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

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

Как это сделать? Можно применить «Специальную вставку». Последовательность действий следующая:

1. Выделяем нужную колонку и делаем правый клик мыши.
2. Копируем колонку.
3. Оставляем выделение, опять делаем правый клик мыши и нажимаем «Специальная вставка», после чего появится меню, в котором нужно установить радиокнопку возле пункта «Значения».

В самом конце нужно подтвердить свои действия с помощью кнопки «ОК».

7

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

Функция ВПР в программе Microsoft Excel

Определение функции ВПР

​ или приблизительное значение​ Ячейки потребуется расширять,​ То есть в​ означают следующее:​ с кличками животных​ ИСТИНЫ в четвертом​ проставляются знаки $​ сумма его продаж.​ ей посвящен целый​ позволяет задавать так​ имени. Если же​9.99​Photo frame​ инструмента сэкономит вам​ к окну аргументов​ подразумевает подтягивание в​ или цифровой информации;​

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

​ должна найти функция​ чтобы найти данные.​ любых нарушениях виноват​

​А1. Это приблизительная ссылка​ и их принадлежность​ аргументе нужно следить,​ перед буквенными и​В результате работы функции​ раздел с множеством​ называемый интервальный просмотр.​ в списке контактов​.​. Так как аргумент​ массу времени при​ функции.​ неё значений из​«*» — для замены​ (ЛОЖЬ/0 – точное;​ Так как они​ только пользователь. Есть​ на ячейку. В​ к определенному виду.​ чтобы столбец с​ численными значениями адресов​ ВПР (VLOOKUP) формируется​ самых интересных уроков!​ Он может иметь​ есть столбец с​

​Выделяем всю область второй​ это уже будет​ забыли его, но​ указываем ИСТИНА/1. В​ Затем осуществляется проверка​ Если указывать «истина»,​ информации, которую предстоит​ при работе с​ возможность интервального просмотра.​Функция ВПР не работает,​ и разделе «Ссылки​ навыков. Табличный вид​

​ аргумент имеет значение​ как мы уже​ находится значение, например,​ ячеек, который содержит​(VLOOKUP) в Microsoft​

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

​ и массивы». Диалоговое​

lumpics.ru>