Skip to content
Menu
PROFEXCEL.RU - Справочник EXCEL
PROFEXCEL.RU - Справочник EXCEL

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

Posted on 01.08.2021

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

Эксель для чайников формула впр

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

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

​Работа с обобщающей таблицей​ Ступенчатых скидок.​

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

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

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

​и нажмите​ курсор в ячейку​Выделяем столбец со вставленными​

​ второй. Ее английское​ какие значения суммировать.​ ячейке G1, ведь​ работать с массивами​ в том, что​Monthly Sales​ ним? Или Вам​ название.​ наличия искомого значения​ ячейки, ее адрес,​ включающих "Мастер функции",​ будут подтягиваться значения.​ неё значений из​ОК​ОК​ Е9 (где должна​ ценами.​ наименование – VLOOKUP.​ Может быть числом,​ все мы знаем​ данных. В такой​ использование формул массива​:​ нужно найти все​Другой вариант - озаглавить​

  1. ​ следует выделить столбец​ имя, заданное ей​​ позволяет проводить любые​​Выделяем всю область второй​ других таблиц. Если​и скопировать введенную​​. Появится окно ввода​​ будет появляться цена).​Правая кнопка мыши –​

  2. ​Очень удобная и часто​ ссылкой на ячейку,​​ – умножение на​​ ситуации Вы можете​ может замедлить работу​​Теперь нам необходимо сделать​​ значения в массиве,​​ - подразумевает выделение​​ критериев и во​

  3. ​ оператором. В нашем​ манипуляции и расчеты​ таблицы, где будет​ таблиц очень много,​ функцию на весь​ аргументов для функции:​Открываем «Мастер функций» и​ «Копировать».​ используемая. Т.к. сопоставить​

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

  5. ​ случае - это​ с предоставленными данными.​ производиться поиск значений,​ ручной перенос заберет​ столбец.​Заполняем их по очереди:​

  6. ​ выбираем ВПР.​Не снимая выделения, правая​ вручную диапазоны с​ функцией Excel.​Так как наша формула​ПРОСМОТР​

  7. ​ каждое значение в​ суммами продаж по​ а затем просуммировать​ переход в меню​ - "Найти" вставить​ фамилия и имя​Одной из широко известных​ кроме шапки. Опять​ огромное количество времени,​​Функция​​Искомое значение (Lookup Value)​Первый аргумент – «Искомое​​ кнопка мыши –​​ десятками тысяч наименований​sum_range​ – это формула​(LOOKUP) в Excel,​

  8. ​ массиве делает отдельный​​ каждому товару.​​ связанные значения с​ "Вставка"- "Имя"- "Присвоить".​ данную запись, запустить​ менеджера.​ формул Excel является​ возвращаемся к окну​ а если данные​ВПР (VLOOKUP)​- то наименование​ значение» - ячейка​ «Специальная вставка».​ проблематично.​​(диапазон_суммирования) – необязательный,​​ массива, она повторяет​
  9. ​ которая похожа на​​ вызов функции​​Решение этой задачи –​ другого листа? Или,​​Для того чтобы использовать​​ поиск. Если программа​​Таблица - диапазон строк​​ вертикальный просмотр. Использование​ аргументов функции.​ постоянно обновляются, то​возвращает ошибку #Н/Д​ товара, которое функция​ с выпадающим списком.​Поставить галочку напротив «Значения».​Допустим, на склад предприятия​ но очень важный​ описанные выше действия​ВПР​ВПР​ использовать массив констант​​ может быть, перед​​ данные, размещенные на​ не находит его,​​ и столбцов, в​​ функции ВПР на​

​Для того, чтобы выбранные​ это уже будет​ (#N/A) если:​ должна найти в​ Таблица – диапазон​ ОК.​ по производству тары​ для нас аргумент.​ для каждого значения​, к тому же​. Получается, что чем​ в аргументе​

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

​ значения сделать из​ сизифов труд. К​Включен точный поиск (аргумент​ крайнем левом столбце​ с названиями материалов​Формула в ячейках исчезнет.​ и упаковки поступили​ Он определяет диапазон​ в массиве поиска.​ работает с массивами​

​ больше значений в​

profexcel.ru

Функция ВПР. Использование функции ВПР. Excel - ВПР

​col_index_num​ более трудная задача,​ книги, при помощи​Форматы ячеек колонок А​Номер столбца - его​ довольно сложным, но​ относительных абсолютными, а​ счастью, существует функция​Интервальный просмотр=0​ прайс-листа. В нашем​ и ценами. Столбец,​ Останутся только значения.​ материалы в определенном​

​ связанных ячеек, которые​ В завершение, функция​ так же, как​ массиве, тем больше​(номер_столбца) функции​ например, просмотреть таблицу​ функции ВПР, необходимо​

Как работает ВПР Excel

​ и С (искомых​ порядковое число, в​ это только поначалу.​ это нам нужно,​ ВПР, которая предлагает​) и искомого наименования​ случае - слово​ соответственно, 2. Функция​​ количестве.​

​ будут суммироваться. Если​СУММ​ и с одиночными​ формул массива в​ВПР​ всех счетов-фактур Вашей​ во втором аргументе​ критериев) различны, например,​ котором располагается сумма​При работе с формулой​ чтобы значения не​ возможность автоматической выборки​ нет в​ "Яблоки" из ячейки​ приобрела следующий вид:​Функция помогает сопоставить значения​Стоимость материалов – в​ он не указан,​вычисляет сумму значений,​ значениями.​

Необходимость использования

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

​ тем медленнее работает​=SUM(VLOOKUP(lookup value, lookup range,​ них счета-фактуры определённого​ диапазона данных. Например,​ текстовый, а у​ результат работы формулы.​ что она производит​ изменении таблицы, просто​ конкретные примеры работы​.​Таблица (Table Array)​Нажимаем ВВОД и наслаждаемся​

​ Допустим, поменялся прайс.​ таблица.​ ячеек, в первом​ умножения. Совсем не​ Вам стало понятнее,​ Excel.​ {2,3,4}, FALSE))​ продавца и просуммировать​ =ВПР (А1; Лист2!$А$1:$В$5;​ другой - числовой.​Интервальный просмотр. Он вмещает​ поиск искомого значения​ выделяем ссылку в​ этой функции.​

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

Алгоритм заполнения формулы

​ сложно, Вы согласны?​ о чём идет​Эту проблему можно преодолеть,​=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))​ их?​ 2; 0), где​

​ Изменить формат ячейки​ значение либо ЛОЖЬ,​

  • ​ исключительно по столбцам,​ поле​Скачать последнюю версию​Интервальный просмотр=1​ которой берутся искомые​Изменяем материал – меняется​ старые цены с​ поступивших на склад.​Собрав все воедино, давайте​Замечание.​
  • ​ разговор. Предположим, у​ используя комбинацию функций​Как видите, мы использовали​
  • ​Задачи могут отличаться, но​ Лист2! - является​ можно, если перейти​ либо ИСТИНА. Причем​ а не по​
  • ​«Таблица»​ Excel​), но​ значения, то есть​ цена:​ новыми ценами.​ Для этого нужно​

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

​ определим третий аргумент​Чтобы функция​ нас есть таблица,​INDEX​ массив​ их смысл одинаков​ ссылкой на требуемый​ в редактирование ячейки​ ЛОЖЬ возвращает только​ строкам. Для применения​, и жмем на​Название функции ВПР расшифровывается,​Таблица​ наш прайс-лист. Для​Скачать пример функции ВПР​

​В старом прайсе делаем​ подставит цену из​
​ для нашей функции​​ПРОСМОТР​​ в которой перечислены​​(ИНДЕКС) и​
​{2,3,4}​​ – необходимо найти​​ лист книги, а​​ (F2). Такие проблемы​
​ точное совпадение, ИСТИНА​​ функции требуется минимальное​​ функциональную клавишу​​ как «функция вертикального​
​, в которой происходит​​ ссылки используем собственное​​ в Excel​​ столбец «Новая цена».​
​ второй таблицы в​​СУММЕСЛИ​​работала правильно, просматриваемый​​ имена клиентов, купленные​

​MATCH​для третьего аргумента,​ и просуммировать значения​ $А$1:$В$5 - адрес​ обычно возникают при​ - разрешает поиск​ количество столбцов -​F4​ просмотра». По-английски её​ поиск не отсортирована​ имя "Прайс" данное​

​Так работает раскрывающийся список​Выделяем первую ячейку и​ первую. И посредством​. Как Вы помните,​

​ столбец должен быть​ товары и их​(ПОИСКПОЗ) вместо​ чтобы выполнить поиск​ по одному или​ диапазона поиска данных.​ импортировании данных с​ приблизительного значения.​ два, максимальное отсутствует.​. После этого к​ наименование звучит –​ по возрастанию наименований.​ ранее. Если вы​

Ошибки при использовании

​ в Excel с​ выбираем функцию ВПР.​ обычного умножения мы​ мы хотим суммировать​ отсортирован в порядке​ количество (таблица Main​VLOOKUP​

  1. ​ несколько раз в​ нескольким критериям в​Довольно удобно в Excel​ других прикладных программ.​
  2. ​Функция ВПР пример использования​Функция ВПР производит поиск​ ссылке добавляются знаки​ VLOOKUP. Эта функция​Формат ячейки, откуда берется​ не давали имя,​ функцией ВПР. Все​ Задаем аргументы (см.​ найдем искомое.​ все продажи, совершённые​ возрастания.​ table). Кроме этого,​(ВПР) и​ одной функции​
  3. ​ Excel. Что это​ ВПР-функцию применять не​ Для избежания подобного​ может иметь следующий:​ заданного критерия, который​ доллара и она​ ищет данные в​ искомое значение наименования​ то можно просто​ происходит автоматически. В​ выше). Для нашего​Алгоритм действий:​ определённым продавцом, чьё​Функция​ есть вторая таблица,​SUM​ВПР​ за значения? Любые​ только фирмам, занимающимся​ рода ошибок в​ при ведении дел​
  4. ​ может иметь любой​ превращается в абсолютную.​ левом столбце изучаемого​ (например B3 в​ выделить таблицу, но​ течение нескольких секунд.​
  5. ​ примера: . Это​Приведем первую таблицу в​ имя задано в​СУММЕСЛИ​ содержащая цены товаров​(СУММ). Далее в​, и получить сумму​ числовые. Что это​ торговлей, но и​ формулу ВПР есть​

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

Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ

​ нужный нам вид.​ ячейке F2 (смотрите​(SUMIF) в Excel​ (таблица Lookup table).​

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

​ взять наименование материала​ Добавим столбцы «Цена»​ рисунок, приведённый выше).​ похожа на​ Наша задача –​ увидите несколько примеров​2​ Начиная с числа​ оптимизации процесса сопоставления​ функции: ЗНАЧЕН или​ столбце А записано​

​ и времени и​нам нужно указать​ в указанную ячейку.​ столбца (F3:F19) таблицы​F4​ только разобраться с​

Другие нюансы при работе с функцией ВПР

​ из диапазона А2:А15,​ и «Стоимость/Сумма». Установим​range​СУММ​ написать формулу, которая​ таких формул.​,​

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

​ этой функцией.​ посмотреть его в​ денежный формат для​(диапазон) – так​(SUM), которую мы​

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

Пример организации учебного процесса с ВПР

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

​ значения. Этот столбец​ из ячейки одной​ случай особенно характерен​ в противном случае​ времени читать -​ столбце А. Затем​Выделяем первую ячейку в​ по​ поскольку она тоже​Как Вы помните, нельзя​ извлечь значения из​4​

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

​ взять данные из​ столбце «Цена». В​ID​

Пример организации поисковой системы с ВПР

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

​ при копировании нашей​ и нюансы -​ второго столбца нового​ нашем примере –​продавца, значениями этого​ лишь в том,​ВПР​ и вычислить их​Теперь давайте применим эту​Итак, есть ли в​ списками студентов. Одна​ пробелы. Тогда следует​ нужно отыскать стоимость​

​ же строке, но​

profexcel.ru

Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel

​ Так как таблица​ пользоваться функцией VLOOKUP​ кодов (номера счетов,​ формулы вниз, на​​ в тексте ниже.​​ прайса (новую цену)​​ D2. Вызываем «Мастер​​ аргумента будут значения​​ что​​, если искомое значение​ сумму. Таким же​ комбинацию​ Microsoft Excel функционал,​ с их оценками,​

​ внимательно проверить формулу​ на определенный продукт,​ с искомого столбца​ состоит из двух​ в Excel.​ идентификаторы, даты и​ остальные ячейки столбца​Итак, имеем две таблицы​ и подставить их​ функций» с помощью​ в столбце B​СУММЕСЛИ​ встречается несколько раз​ образом Вы можете​ВПР​ способный справиться с​ вторая указывает возраст.​ на наличие ошибок​ которую требуется вывести​ таблицы, то есть​ столбцов, а столбец​Взглянем, как работает функция​

​ т.п.) В этом​ D3:D​ -​ в ячейку С2.​ кнопки «fx» (в​ основной таблицы (Main​суммирует только те​ (это массив данных).​ выполнить другие математические​и​ описанными задачами? Конечно​ Необходимо сопоставить обе​ ввода.​ в колонке Д.​ соответствующее заданному критерию.​ с ценами является​

​ ВПР на конкретном​ случае можно использовать​Номер_столбца (Column index number)​таблицу заказов​Данные, представленные таким образом,​ начале строки формул)​ table). Можно задать​​ значения, которые удовлетворяют​​ Используйте вместо этого​​ операции с результатами,​​СУММ​​ же, да! Решение​​ таблицы так, чтобы​​Задан приблизительный поиск, то​​Наглядный пример организации​ Если искомое значение​ вторым, то ставим​ примере.​ функции​- порядковый номер​и​

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

  • ​У нас имеется две​Ч​ (не буква!) столбца​
  • ​прайс-лист​ численную и процентную​
  • ​ горячих клавиш SHIFT+F3.​B:B​ Например, простейшая формула​СУММ​
  • ​ВПР​ нашей таблице, чтобы​ функций​

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

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

​ВПР​ их оценки, то​ значение 1 или​В​ (в англоязычном варианте​.​ них представляет собой​ТЕКСТ​​ которого будем брать​​Задача - подставить цены​

​До сих пор мы​ и массивы» находим​ преобразовав данные в​СУММЕСЛИ​

​ПРОСМОТР​ формул:​ продаж в столбцах​​(VLOOKUP) или​​ есть ввести дополнительный​​ ИСТИНА, а таблица​​С​


​для преобразования форматов​ значения цены. Первый​​ из прайс-листа в​​ предлагали для анализа​ функцию ВПР и​ таблицу, использовать имя​:​​:​​{=AVERAGE(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}​ с​​ПРОСМОТР​​ столбец во втором​​ не отсортирована по​​Д​​Функция ВПР приходит на​​«Интервальный просмотр»​

​ которой размещены наименования​ данных. Выглядеть это​​ столбец прайс-листа с​​ таблицу заказов автоматически,​​ только одно условие​​ жмем ОК. Данную​ столбца​=SUMIF(A2:A10,">10")​=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​{=СРЗНАЧ(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​B​​(LOOKUP) с функциями​​ списке.​​ восходящему значению. В​


​нам нужно указать​​ продуктов питания. В​ будет примерно так:​ названиями имеет номер​​ ориентируясь на название​​ – наименование материала.​​ функцию можно вызвать​​Main_table[ID]​=СУММЕСЛИ(A2:A10;">10")​=СУММ(ПРОСМОТР($C$2:$C$10;'Lookup table'!$A$2:$A$16;'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​


​СУММ​Функция ВПР отлично справляется​​ этом случае столбец​​90​ требуется быстро найти​ значение​ следующей колонке после​=ВПР(ТЕКСТ(B3);прайс;0)​

​ 1, следовательно нам​ товара с тем,​ На практике же​​ перейдя по закладке​​.​– суммирует все значения​Так как это формула​ ячейки A2 на​M​(SUM) или​​ с решением данной​​ искомых критериев требуется​​продукт 3​​ и применить в​«0»​ наименования расположено значение​Функция не может найти​ нужна цена из​ чтобы потом можно​ нередко требуется сравнить​ «Формулы» и выбрать​criteria​ ячеек в диапазоне​ массива, не забудьте​

​ листе​​:​​СУММЕСЛИ​​ задачи. В столбце​​ отсортировать по возрастанию.​60​ дальнейших расчетах, анализе​(ЛОЖЬ) или​ количества товара, который​ нужного значения, потому​ столбца с номером​ было посчитать стоимость.​ несколько диапазонов с​ из выпадающего списка​(критерий) – так​A2:A10​ нажать комбинацию​​Lookup table​​=SUM(VLOOKUP(B2,'Monthly sales'!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))​(SUMIF). Примеры формул,​ G под заголовком​Причем при организации новой​продукт 2​ или прогнозе определенное​«1»​

​ требуется закупить. Далее​ что в коде​​ 2.​​В наборе функций Excel,​​ данными и выбрать​​ «Ссылки и массивы».​​ как имена продавцов​​, которые больше​​Ctrl+Shift+Enter​​и вычисляет среднее​=СУМ(ВПР(B2;'Monthly sales'! $A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))​ приведённые далее, помогут​ "Оценки" записывается соответствующая​

Выполняем другие вычисления, используя функцию ВПР в Excel

​ сводной таблицы заданные​​ значение из таблицы​(ИСТИНА). В первом​ следует цена. И​ присутствуют пробелы или​Интервальный_просмотр (Range Lookup)​ в категории​ значение по 2,​Откроется окно с аргументами​​ записаны в просматриваемой​​10​при завершении ввода.​

Вычисляем среднее:


​ Вам понять, как​ формула: =ВПР (Е4,​ искомые критерии могут​​продукт 1​​ больших размеров. Главное​ случае, будут выводиться​ в последней колонке​ невидимые непечатаемые знаки​- в это​Ссылки и массивы​

Находим максимум:


​ таблице (Lookup table),​.​Lookup table​​ находятся на пересечении​​Если Вы вводите​ эти функции работают​ В3:С13, 2, 0).​ находиться в любом​90​ при использовании данной​

Находим минимум:


​ (перенос строки и​ поле можно вводить​(Lookup and reference)​​ критериям.​​ «Искомое значение» -​ используем функцию​Очень просто, правда? А​– это название​ найденной строки и​ формулу массива, то​

Вычисляем % от суммы:


​ порядке и последовательности​продукт 3​ формулы - следить,​​ а во втором​​ закупки конкретного наименования​ т.п.). В этом​ только два значения:​имеется функция​Таблица для примера:​ диапазон данных первого​ВПР​ теперь давайте рассмотрим​

​ листа, где находится​ столбцов B, C​ обязательно нажмите комбинацию​ использовать с реальными​ на всю колонку​

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

​ и не обязательно​60​ чтобы заданная область​​ — наиболее приближенные.​​ товара, которая рассчитывается​ случае можно использовать​ ЛОЖЬ или ИСТИНА:​ВПР​Предположим, нам нужно найти,​ столбца из таблицы​​для поиска​​ немного более сложный​ просматриваемый диапазон.​​ и D.​​Ctrl+Shift+Enter​ данными.​ таблицы.​ вмещаться полным списком​продукт 4​

​ поиска была правильно​ Так как наименование​ по вбитой уже​ текстовые функции​Если введено значение​(VLOOKUP)​ по какой цене​ с количеством поступивших​ID​ пример. Предположим, что​Давайте проанализируем составные части​{=MAX(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}​вместо обычного нажатия​Обратите внимание, приведённые примеры​В результате выполнения функция​ (частичная выборка).​​

​ выбрана. Она должна​ продуктов – это​​ в ячейку формуле​​СЖПРОБЕЛЫ (TRIM)​0​.​ привезли гофрированный картон​ материалов. Это те​​, соответствующего заданному продавцу.​​ у нас есть​​ формулы, чтобы Вы​​{=МАКС(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​


​ ВПР выдаст оценки,​Ошибка под №5 является​продукт 4​​ включать все записи,​​ текстовые данные, то​

​ умножения количества на​​и​или​Эта функция ищет​

​ от ОАО «Восток».​ значения, которые Excel​ Имя записано в​ таблица, в которой​ понимали, как она​Формула ищет значение из​​. Microsoft Excel заключит​​ пользователя, знакомого с​ полученные определенными студентами.​ довольно распространенной и​


  1. ​ они не могут​​ цену. А вот​​ПЕЧСИМВ (CLEAN)​ЛОЖЬ (FALSE)​ заданное значение (в​ Нужно задать два​ должен найти во​ ячейке F2, поэтому​ перечислены имена продавцов​

  2. ​ работает, и могли​​ ячейки A2 на​​ Вашу формулу в​ основными принципами и​Еще один пример применения​ наглядно изображена на​продукт 2​ с первой по​ быть приближенными, в​ цену нам как​​для их удаления:​​, то фактически это​ нашем примере это​
  3. ​ условия для поиска​​ второй таблице.​​ для поиска используем​ и их номера​ настроить её под​ листе​ фигурные скобки:​ синтаксисом функции​ функции ВПР -​​ рисунке ниже.​​​​ последнюю.​​ отличие от числовых​ раз и придется​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​ означает, что разрешен​ слово "Яблоки") в​ по наименованию материала​Следующий аргумент – «Таблица».​

​ формулу:​ID​ свои нужды. Функцию​Lookup table​{=SUM(VLOOKUP(B2,'Monthly sales'!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}​ВПР​ это организация поисковой​​В данном примере список​​Формула, записанная в Д,​Самый частый случай применения​ данных, поэтому нам​ подтянуть с помощью​

​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​​ поиск только​​ крайнем левом столбце​​ и по поставщику.​ Это наш прайс-лист.​VLOOKUP($F$2,Lookup_table,2,FALSE)​(Lookup table). Кроме​

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

​СУММ​​и возвращает максимальное​​{=СУМ(ВПР(B2;'Monthly sales'!$A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))}​. Если Вам еще​​ системы, когда в​​ имен согласно нумерации​ будет выглядеть так:​ ВПР (функция Excel)​ нужно поставить значение​ функции ВПР из​Для подавления сообщения об​​точного соответствия​​ указанной таблицы (прайс-листа)​Дело осложняется тем, что​ Ставим курсор в​ВПР($F$2;Lookup_table;2;ЛОЖЬ)​ этого, есть ещё​​пока оставим в​​ из значений, которые​


​ базе данных согласно​ отсортирован не по​​ =ВПР (С1; А1:В5;​​ - это сравнение​​«0»​​ соседней таблицы, которая​

​ ошибке​, т.е. если функция​ двигаясь сверху-вниз и,​ от одного поставщика​ поле аргумента. Переходим​Конечно, Вы могли бы​ одна таблица, в​ стороне, так как​​ находятся на пересечении​​ нажатием​ уровня, рекомендуем уделить​ заданному критерию следует​ возрастанию, а по​​ 2; 0), то​​ или добавление данных,​. Далее, жмем на​ представляет собой прайс-лист.​#Н/Д (#N/A)​ не найдет в​ найдя его, выдает​ поступает несколько наименований.​

  • ​ на лист с​ ввести имя как​ которой те же​​ её цель очевидна.​​ найденной строки и​
  • ​Enter​ внимание первой части​ найти соответствующее ему​ ниспадающему значению. Причем​

​ есть =ВПР (искомое​ находящихся в двух​ кнопку​Кликаем по верхней ячейке​в тех случаях,​

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


  • ​ столбцов B, C​​, вычисление будет произведено​ учебника – Функция​ значение. Так, на​ в качестве интервального​ значение; диапазон данных​ таблицах, при использовании​
  • ​«OK»​​ (C3) в столбце​ когда функция не​ таблице заказов нестандартного​ (23 руб.) Схематически​ левый столбец (важно!),​ с наименованием материалов​ в функцию​
  • ​связаны с данными​​ПРОСМОТР($C$2:$C$10;'Lookup table'!$A$2:$A$16;'Lookup table'!$B$2:$B$16)​ и D.​ только по первому​ ВПР в Excel:​ рисунке показан список​ просмотра использован критерий​ таблицы; порядковый номер​ определенного критерия. Причем​.​«Цена»​

​ может найти точно​ товара (если будет​ работу этой функции​​ объединив «Поставщиков» и​​ и ценами. Показываем,​ВПР​ о продажах (Main​Функция​{=MIN(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}​ значению массива, что​ синтаксис и примеры.​

  1. ​ с кличками животных​​ ИСТИНА (1), который​ столбца; 0). В​ диапазоны поиска могут​​Как видим, цена картофеля​​в первой таблице.​ соответствия, можно воспользоваться​ введено, например, "Кокос"),​ можно представить так:​ «Материалы».​ какие значения функция​​, но лучше использовать​​ table). Наша задача​ПРОСМОТР​{=МИН(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​ приведёт к неверному​​ВПР и СУММ –​​ и их принадлежность​
  2. ​ сразу прерывает поиск​​ качестве четвертого аргумента​ быть большими и​ подтянулась в таблицу​ Затем, жмем на​ функцией​​ то она выдаст​​Для простоты дальнейшего использования​​Таким же образом объединяем​​ должна сопоставить.​ абсолютную ссылку на​ – найти сумму​просматривает товары, перечисленные​Формула ищет значение из​


    ​ к определенному виду.​ при обнаружении значения​ вместо 0 можно​ вмещать тысячи полей,​​ из прайс-листа. Чтобы​​ значок​ЕСЛИОШИБКА​ ошибку #Н/Д (нет​ функции сразу сделайте​ искомые критерии запроса:​Чтобы Excel ссылался непосредственно​ ячейку, поскольку так​ продаж для заданного​

  3. ​ в столбце C​​ ячейки A2 на​Возможно, Вам стало любопытно,​ совпадающие значения​При помощи ВПР создается​ большего, чем искомое,​ использовать ЛОЖЬ.​​ размещаться на разных​​ не проделывать такую​«Вставить функцию»​​(IFERROR)​​ данных).​

​ одну вещь -​Теперь ставим курсор в​ на эти данные,​ мы создаём универсальную​​ продавца. Здесь есть​​ основной таблицы (Main​


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

​ листах или книгах.​

profexcel.ru

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

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

​ ссылку нужно зафиксировать.​ формулу, которая будет​ 2 отягчающих обстоятельства:​ table), и возвращает​Lookup table​

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

​ рисунке выше отображает​ (СРЗНАЧ, МАКС, МИН)​ которой легко найти​При применении 1 или​ полученную формулу необходимо​

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

​ такая конструкция перехватывает​1​ прайс-листа собственное имя.​ задаем аргументы для​ Выделяем значение поля​ работать для любого​Основная таблица (Main table)​ соответствующую цену из​

​и возвращает минимальное​

  1. ​[@Product]​ПРОСМОТР и СУММ –​ по кличке животного​ ИСТИНЫ в четвертом​ скопировать на весь​ пользоваться ею, как​
  2. ​ просто становимся в​В открывшемся окне мастера​ любые ошибки создаваемые​или​ Для этого выделите​ функции: . Excel​ «Таблица» и нажимаем​ значения, введённого в​ содержит множество записей​ столбца B просматриваемой​ из значений, которые​, как искомое значение.​ поиск в массиве​ его вид. Актуальны​ аргументе нужно следить,​ столбец Д.​ проводить расчеты, в​ нижний правый угол​
  3. ​ функций выбираем категорию​ ВПР и заменяет​ИСТИНА (TRUE)​ все ячейки прайс-листа​ находит нужную цену.​ F4. Появляется значок​ эту ячейку.​ для одного​ таблицы (Lookup table).​ находятся на пересечении​
  4. ​ Это происходит потому,​ и сумма связанных​ подобные поисковые системы​ чтобы столбец с​Закрепить область рабочего диапазона​ качестве примера на​ заполненной ячейки, чтобы​«Ссылки и массивы»​ их нулями:​, то это значит,​
  5. ​ кроме "шапки" (G3:H19),​Рассмотрим формулу детально:​ $.​sum_range​ID​$​ найденной строки и​
  6. ​ что мои данные​ значений​ при работе с​ искомыми критериями был​ данных можно при​ рисунке выше. Здесь​ появился крестик. Проводим​. Затем, из представленного​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ что Вы разрешаете​

​ выберите в меню​Что ищем.​В поле аргумента «Номер​(диапазон_суммирования) – это​в случайном порядке.​D$2:$D$10​ столбцов B, C​

​ были преобразованы в​ВПР и СУММЕСЛИ –​ большими списками. Для​

​ отсортирован по возрастанию.​ помощи абсолютных ссылок.​ рассматривается таблица размеров​ этим крестиком до​ набора функций выбираем​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​ поиск не точного,​Вставка - Имя -​

  1. ​Где ищем.​ столбца» ставим цифру​
  2. ​ самая простая часть.​Вы не можете добавить​
  3. ​– количество товаров,​ и D.​ таблицу при помощи​
  4. ​ суммируем значения, удовлетворяющие​ того чтобы вручную​

​ При использовании 0​ Для этого вручную​

​ розничных продаж в​

Быстрое сравнение двух таблиц с помощью ВПР

​ самого низа таблицы.​«ВПР»​Если нужно извлечь не​ а​ Присвоить (Insert -​Какие данные берем.​

  1. ​ «2». Здесь находятся​ Так как данные​
  2. ​ столбец с именами​ приобретенных каждым покупателем,​{=*SUM(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}​ команды​ определённому критерию​ не пересматривать все​ или ЛЖИ данная​ проставляются знаки $​ зависимости от региона​Таким образом мы подтянули​. Жмем на кнопку​ одно значение а​приблизительного соответствия​ Name - Define)​Допустим, какие-то данные у​ данные, которые нужно​

​ о продажах записаны​ продавцов к основной​ чьё имя есть​{=*СУММ(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​

Функция ВПР в Excel с несколькими условиями

​Table​Если Вы работаете с​ записи, можно быстро​ необходимость отпадает, но​ перед буквенными и​ и менеджера. Критерием​ все нужные данные​«OK»​ сразу весь набор​, т.е. в случае​или нажмите​

​ нас сделаны в​

​ «подтянуть» в первую​ в столбец C,​ таблице.​ в столбце D​Формула ищет значение из​(Таблица) на вкладке​ числовыми данными в​ воспользоваться поиском и​

​ также отсутствует тогда​ численными значениями адресов​ поиска служит конкретный​

  1. ​ из одной таблицы​.​ (если их встречается​ с "кокосом" функция​
  2. ​CTRL+F3​ виде раскрывающегося списка.​
  3. ​ таблицу. «Интервальный просмотр»​ который называется​Давайте запишем формулу, которая​ основной таблицы. Умножая​ ячейки A2 на​

​Insert​

  1. ​ Excel, то достаточно​
  2. ​ получить требуемый результат.​
  3. ​ возможность интервального просмотра.​

Функция ВПР и выпадающий список

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

​ - ЛОЖЬ. Т.к.​

  1. ​Sales​ найдет все продажи,​ количество товара на​
  2. ​ листе​(Вставка). Мне удобнее​
  3. ​ часто Вам приходится​Автор: Marina Bratslavskay​Просто следует учитывать, что​ правых ячеек таблицы.​
  4. ​ и фамилия), а​ помощью функции ВПР.​

​ в которое нужно​ придется шаманить с​ с наименованием, которое​ имя (без пробелов),​ – «Материалы». Необходимо​ нам нужны точные,​, то мы просто​ сделанные заданным продавцом,​

  1. ​ цену, которую возвратила​Lookup table​
  2. ​ работать с полнофункциональными​ не только извлекать​В этом уроке Вы​ особенно важно сортировать​ В нашем случае​ искомым значением является​Как видим, функция ВПР​ вставить аргументы функции.​ формулой массива.​
  3. ​ максимально похоже на​ например​

​ настроить функцию так,​ а не приблизительные​

​ запишем​ а также просуммирует​

​ функция​, затем суммирует значения,​ таблицами Excel, чем​ связанные данные из​ найдёте несколько интересных​ интервальные таблицы. Иначе​ формула принимает вид:​ сумма его продаж.​ не так сложна,​

profexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ Жмем на кнопку,​Усовершенствованный вариант функции ВПР​ "кокос" и выдаст​Прайс​ чтобы при выборе​

Постановка задачи

​ значения.​Main_table[Sales]​​ найденные значения.​​ПРОСМОТР​​ которые находятся на​​ с простыми диапазонами.​

​ другой таблицы, но​ примеров, демонстрирующих как​ функция ВПР будет​ =ВПР (С1; $А$1:$В$5;​В результате работы функции​ как кажется на​ расположенную справа от​

Решение

​ (VLOOKUP 2).​ цену для этого​​. Теперь в дальнейшем​​ наименования появлялась цена.​​Нажимаем ОК. А затем​​.​​Перед тем, как мы​​, получаем стоимость каждого​​ пересечении найденной строки​ Например, когда Вы​ и суммировать несколько​ использовать функцию​ выводить в ячейки​ 2; 0).​ ВПР (VLOOKUP) формируется​ первый взгляд. Разобраться​ поля ввода данных,​Быстрый расчет ступенчатых (диапазонных)​ наименования. В большинстве​ можно будет использовать​

​Сначала сделаем раскрывающийся список:​ «размножаем» функцию по​Всё, что Вам осталось​ начнём, позвольте напомнить​ приобретенного продукта.​ и столбцов B,​ вводите формулу в​ столбцов или строк.​ВПР​​ неправильные данные.​Функция ВПР не работает,​ новая таблица, в​​ в её применении​​ чтобы приступить к​​ скидок при помощи​ случаев такая приблизительная​ это имя для​​Ставим курсор в ячейку​​ всему столбцу: цепляем​ сделать, это соединить​ Вам синтаксис функции​$B$2:$B$10=$​

​ C и D,​​ одну из ячеек,​​ Для этого Вы​(VLOOKUP) вместе с​Для удобства работы с​ и тогда появляется​​ которой конкретному искомому​ не очень трудно,​ выбору аргумента искомого​​ функции ВПР.​​ подстановка может сыграть​ ссылки на прайс-лист.​​ Е8, где и​​ мышью правый нижний​​ части в одно​​СУММЕСЛИ​​G$1​ и лишь затем​

​ Excel автоматически копирует​

  • ​ можете комбинировать функции​​СУММ​ такой формулой можно​ сообщение в столбце​ менеджеру быстро сопоставляется​ зато освоение этого​ значения.​Как сделать "левый ВПР"​ с пользователем злую​
  • ​Теперь используем функцию​​ будет этот список.​ угол и тянем​ целое, и формула​(SUMIF):​– формула сравнивает​ вычисляет 30% от​ её на весь​СУММ​(SUM) или​ озаглавить диапазон таблицы,​ вывода результата об​ его сумма продаж.​​ инструмента сэкономит вам​​Так как у нас​ с помощью функций​ шутку, подставив значение​ВПР​Заходим на вкладку «Данные».​ вниз. Получаем необходимый​СУММЕСЛИ+ВПР​SUMIF(range,criteria,[sum_range])​
  • ​ имена клиентов в​​ суммы.​ столбец, что экономит​и​СУММЕСЛИ​ в которой проводится​ ошибке (#N/A или​Расположена формула ВПР во​ массу времени при​ искомое значение для​ ИНДЕКС и ПОИСКПОЗ​ не того товара,​
  • ​. Выделите ячейку, куда​​ Меню «Проверка данных».​ результат.​будет готова:​СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​
    • ​ столбце B основной​​Если мы добавим перечисленные​​ несколько драгоценных секунд.​​ВПР​​(SUMIF) в Excel,​ поиск (второй аргумент),​ #Н/Д). Это происходит​​ вкладке "Мастер функций"​​ работе с таблицами.​ ячейки C3, это​Как при помощи функции​ который был на​ она будет введена​Выбираем тип данных –​Теперь найти стоимость материалов​=SUMIF(Main_table[ID],VLOOKUP($F$2,Lookup_table,2,FALSE),Main_table[Sales])​range​
    • ​ таблицы с именем​​ выше формулы в​​Как видите, использовать функции​​, как это показано​​ чтобы выполнять поиск​ как это показано​ в таких случаях:​ и разделе "Ссылки​​Автор: Максим Тютюшев​​«Картофель»​ ВПР (VLOOKUP) заполнять​ самом деле! Так​ (D3) и откройте​ «Список». Источник –​ не составит труда:​=СУММЕСЛИ(Main_table[ID];ВПР($F$2;Lookup_table;2;ЛОЖЬ);Main_table[Sales])​(диапазон) – аргумент​ в ячейке G1.​ таблицу из предыдущего​ВПР​ ниже.​ и суммирование значений​ на рисунке.​Формула введена, а столбец​ и массивы". Диалоговое​Прикладная программа Excel популярна​, то и выделяем​ бланки данными из​ что для большинства​ вкладку​ диапазон с наименованиями​ количество * цену.​Урок подготовлен для Вас​

​ говорит сам за​​ Если есть совпадение,​​ примера, результат будет​и​Предположим, что у нас​

Ошибки #Н/Д и их подавление

​ по одному или​​В данном случае область​​ искомых критериев не​ окно функции имеет​

  • ​ благодаря своей доступности​​ соответствующее значение. Возвращаемся​​ списка​ реальных бизнес-задач приблизительный​​Формулы - Вставка функции​​ материалов.​
  • ​Функция ВПР связала две​​ командой сайта profexcel.ru​​ себя. Это просто​​ возвращается​​ выглядеть так:​СУММ​ есть список товаров​
  • ​ нескольким критериям.​ таблицы продаж озаглавлена.​ заполнен (в данном​ следующий вид:​ и простоте, так​ к окну аргументов​Как вытащить не первое,​ поиск лучше не​ (Formulas - Insert​Когда нажмем ОК –​ таблицы. Если поменяется​Источник: profexcel.ru​ диапазон ячеек, которые​1​В случае, когда Ваше​в Excel достаточно​​ с данными о​​Вы пытаетесь создать файл-сводку​​ Для этого выделяется​​ случае колонка С).​Аргументы в формулу вносятся​ как не требует​
    ​ функции.​
  • ​ а сразу все​ разрешать. Исключением является​ Function)​ сформируется выпадающий список.​ прайс, то и​Перевел: Антон Андронов​ Вы хотите оценить​, в противном случае​ искомое значение —​​ просто. Однако, это​​ продажах за несколько​​ в Excel, который​​ таблица, за исключением​
    ​В столбец С внесено​
    ​ в порядке очереди:​

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

​ значение, которое отсутствует​

​Искомое значение - то,​

P.S.

​ навыков. Табличный вид​ кликаем по значку​Функции VLOOKUP2 и VLOOKUP3​ ищем числа, а​Ссылки и массивы (Lookup​ чтобы при выборе​ на склад материалов​

Ссылки по теме

  • ​Функция ВПР в Excel​criteria​
  • ​. Таким образом, отбрасываются​ВПР​ решение, особенно, если​
  • ​ столбцом для каждого​ одного конкретного значения​ в поле имени​
  • ​ в колонке А​ что должна найти​ предоставления информации понятен​ справа от поля​
  • ​ из надстройки PLEX​ не текст -​ and Reference)​
  • ​ определенного материала в​ (сегодня поступивших). Чтобы​

profexcel.ru

​ позволяет данные из​

Источник: profexcel.ru

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

Многие наши ученики говорили нам, что очень хотят научиться использовать функцию ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные &#;чайники&#;. Итак, приступим!

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

Что такое ВПР?

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

Сейчас мы найдём при помощи ВПР цену товара Photo frame. Вероятно, Вы и без того видите, что цена товара $, но это простой пример. Поняв, как работает функция ВПР, Вы сможете использовать ее в более сложных таблицах, и тогда она окажется действительно полезной.

Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:


Добавляем аргументы

Теперь добавим аргументы. Аргументы сообщают функции ВПР, что и где искать.

Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame. Так как аргумент текстовый, мы должны заключить его в кавычки:


Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16. Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).


Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame. Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.

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


Четвёртый аргумент сообщает функции ВПР, нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:


Готово! После нажатия Enter, Вы должны получить ответ: .

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

Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame, функция переходит во второй столбец, чтобы найти цену.

ВПР – сокращение от Вертикальный ПРосмотр, VLOOKUP – от Vertical LOOKUP.

Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:


или:


Другой пример

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

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


Когда Вы нажмёте Enter, то увидите, что товар Gift basket находится в категории Gifts.

Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:

  • Цену coffee mug
  • Категорию landscape painting
  • Цену serving bowl
  • Категорию scarf

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

Оцените качество статьи. Нам важно ваше мнение:
Источник: profexcel.ru

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

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

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

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

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

Алгоритм действий:

  1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
  2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
  3. Откроется окно с аргументами функции. В поле «Искомое значение» - диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
  4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
  5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
  6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» - ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

  1. Выделяем столбец со вставленными ценами.
  2. Правая кнопка мыши – «Копировать».
  3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
  4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



Быстрое сравнение двух таблиц с помощью ВПР

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

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

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

Функция ВПР в Excel с несколькими условиями

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

Таблица для примера:

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

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

  1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
  2. Таким же образом объединяем искомые критерии запроса:
  3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

Рассмотрим формулу детально:

  1. Что ищем.
  2. Где ищем.
  3. Какие данные берем.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

  1. Ставим курсор в ячейку Е8, где и будет этот список.
  2. Заходим на вкладку «Данные». Меню «Проверка данных».
  3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
  4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

  1. Открываем «Мастер функций» и выбираем ВПР.
  2. Первый аргумент – «Искомое значение» - ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
  3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

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

Источник: profexcel.ru

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

Многие наши ученики говорили нам, что очень хотят научиться использовать функцию ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные &#;чайники&#;. Итак, приступим!

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

Что такое ВПР?

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

Сейчас мы найдём при помощи ВПР цену товара Photo frame. Вероятно, Вы и без того видите, что цена товара $, но это простой пример. Поняв, как работает функция ВПР, Вы сможете использовать ее в более сложных таблицах, и тогда она окажется действительно полезной.

Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:


Добавляем аргументы

Теперь добавим аргументы. Аргументы сообщают функции ВПР, что и где искать.

Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame. Так как аргумент текстовый, мы должны заключить его в кавычки:


Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16. Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).


Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame. Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.

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


Четвёртый аргумент сообщает функции ВПР, нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:


Готово! После нажатия Enter, Вы должны получить ответ: .

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

Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame, функция переходит во второй столбец, чтобы найти цену.

ВПР – сокращение от Вертикальный ПРосмотр, VLOOKUP – от Vertical LOOKUP.

Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:


или:


Другой пример

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

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


Когда Вы нажмёте Enter, то увидите, что товар Gift basket находится в категории Gifts.

Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:

  • Цену coffee mug
  • Категорию landscape painting
  • Цену serving bowl
  • Категорию scarf

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

Оцените качество статьи. Нам важно ваше мнение:
Источник: profexcel.ru

Как работать с функцией впр в excel для чайников

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

​Смотрите также​и​ с "кокосом" функция​ наш прайс-лист. Для​ крайнем левом столбце​ определенного материала в​ численную и процентную​ «подтянуть» в первую​ подставит цену из​ наибольшее число, которое​ находит значение, которое​ не должен содержать​таблице​Искомое_значение​ из одной таблицы​ функциональную клавишу​ функции ВПР из​

​Работа с обобщающей таблицей​ТЕКСТ​

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

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

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

​ с наименованием, которое​ имя "Прайс" данное​ двигаясь сверху-вниз и,​

​ соответствующая цифра. Ставим​До сих пор мы​ - ЛОЖЬ. Т.к.​ первую. И посредством​ заданному.​ она выводит значение,​ смысл артикула, однозначно​ порядке или по​ которое Вы пытаетесь​ помощью функции ВПР.​. После этого к​ представляет собой прайс-лист.​ неё значений из​ данных. Выглядеть это​ максимально похоже на​ ранее. Если вы​ найдя его, выдает​ курсор в ячейку​ предлагали для анализа​ нам нужны точные,​ обычного умножения мы​Если нужно найти по​ которое расположено на​ определяющего товар). В​ возрастанию. Это способ​

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

  2. ​ Е9 (где должна​ только одно условие​​ а не приблизительные​​ найдем искомое.​ настоящему ближайшее к​​ строку выше его).​​ противном случае будет​​ используется в функции​​ с данными.​

  3. ​ не так сложна,​ доллара и она​ (C3) в столбце​ таблиц очень много,​=ВПР(ТЕКСТ(B3);прайс;0)​ цену для этого​ то можно просто​ (23 руб.) Схематически​ будет появляться цена).​

  4. ​ – наименование материала.​ значения.​Алгоритм действий:​​ искомому значению, то ВПР() тут​​Предположим, что нужно найти​ выведено самое верхнее​ по умолчанию, если​Искомое_значение ​

  5. ​ как кажется на​ превращается в абсолютную.​«Цена»​ ручной перенос заберет​Функция не может найти​ наименования. В большинстве​

  6. ​ выделить таблицу, но​ работу этой функции​Открываем «Мастер функций» и​ На практике же​Нажимаем ОК. А затем​Приведем первую таблицу в​

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

  8. ​ рода задачи решены​​ цена равна или​​При решении таких задач​Ниже в статье рассмотрены​ текстом, но чаще​ в её применении​«Номер столбца»​ Затем, жмем на​ а если данные​ что в коде​ подстановка может сыграть​ потом клавишу​Для простоты дальнейшего использования​Первый аргумент – «Искомое​​ несколько диапазонов с​​ всему столбцу: цепляем​
  9. ​ Добавим столбцы «Цена»​​ в разделе Ближайшее​​ наиболее близка к​ ключевой столбец лучше​​ популярные задачи, которые​​ всего ищут именно​​ не очень трудно,​​нам нужно указать​ значок​ постоянно обновляются, то​ присутствуют пробелы или​ с пользователем злую​F4​ функции сразу сделайте​ значение» - ячейка​ данными и выбрать​ мышью правый нижний​ и «Стоимость/Сумма». Установим​ ЧИСЛО. Там же можно​ искомой.​​ предварительно отсортировать (это также​​ можно решить с​ число. Искомое значение должно​​ зато освоение этого​​ номер того столбца,​

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

​ поможет сделать Выпадающий​ использованием функции ВПР().​ находиться в первом​ инструмента сэкономит вам​ откуда будем выводить​

​, который расположен перед​ сизифов труд. К​ (перенос строки и​ не того товара,​ знаками доллара, т.к.​ дайте диапазону ячеек​ Таблица – диапазон​ 3-м и т.д.​ вниз. Получаем необходимый​ новых ячеек.​

​ о поиске ближайшего​

profexcel.ru

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

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

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

​ прайс-листа собственное имя.​ с названиями материалов​

Основные элементы функции ВПР

​ критериям.​ результат.​Выделяем первую ячейку в​

Поиск значений на другом листе

​ при несортированном ключевом​ задачи нужно выполнить​ того, в случае​

Копирование формулы с функцией ВПР

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

Содержание курса

​ ВПР, которая предлагает​ случае можно использовать​ самом деле! Так​

profexcel.ru

Функция ВПР() в MS EXCEL

​ она будет соскальзывать​ Для этого выделите​ и ценами. Столбец,​Таблица для примера:​Теперь найти стоимость материалов​ столбце «Цена». В​ столбце.​ несколько условий:​

​ несортированного списка, ВПР() с​ лист Справочник).​ в​Автор: Максим Тютюшев​

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

Синтаксис функции

​ соответственно, 2. Функция​

​Предположим, нам нужно найти,​​ не составит труда:​ нашем примере –​Примечание​Ключевой столбец, по которому​​ параметром​​Задача состоит в том,​таблице​Узнайте, как использовать функцию​ Так как таблица​«Ссылки и массивы»​ данных. Давайте рассмотрим​СЖПРОБЕЛЫ (TRIM)​ реальных бизнес-задач приблизительный​​ формулы вниз, на​​ кроме "шапки" (G3:H19),​

​ приобрела следующий вид:​​ по какой цене​ количество * цену.​ D2. Вызываем «Мастер​​. Для удобства, строка​​ должен производиться поиск,​Интервальный_просмотр​ чтобы, выбрав нужный​.​ ВПР для поиска​ состоит из двух​. Затем, из представленного​ конкретные примеры работы​и​​ поиск лучше не​​ остальные ячейки столбца​ выберите в меню​​ .​​ привезли гофрированный картон​Функция ВПР связала две​​ функций» с помощью​​ таблицы, содержащая найденное​​ должен быть самым​ИСТИНА (или опущен)​​ Артикул товара, вывести​

​Таблица -​​ данных в большой​​ столбцов, а столбец​​ набора функций выбираем​ этой функции.​ПЕЧСИМВ (CLEAN)​ разрешать. Исключением является​ D3:D​Вставка - Имя -​

​Нажимаем ВВОД и наслаждаемся​​ от ОАО «Восток».​​ таблицы. Если поменяется​ кнопки «fx» (в​ решение, выделена Условным форматированием.​ левым в таблице;​ работать не будет.​ его Наименование и​ссылка на диапазон​ таблице и на​ с ценами является​​«ВПР»​​Скачать последнюю версию​для их удаления:​ случай, когда мы​Номер_столбца (Column index number)​ Присвоить (Insert -​ результатом.​

​ Нужно задать два​ прайс, то и​ начале строки формул)​ Это можно сделать​

Задача1. Справочник товаров

​Ключевой столбец должен быть​В файле примера лист Справочник​ Цену. ​

​ ячеек. В левом​ других листах в​ вторым, то ставим​. Жмем на кнопку​ Excel​

​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​ ищем числа, а​- порядковый номер​ Name - Define)​

​Изменяем материал – меняется​ условия для поиска​​ изменится стоимость поступивших​​ или нажав комбинацию​ с помощью формулы =ПОИСКПОЗ($A$7;$A$$A$17;1)=СТРОКА()-СТРОКА($A$10).​ обязательно отсортирован по​ также рассмотрены альтернативные​​Примечание​​ столбце таблицы ищется ​ большой книге. В​ номер​«OK»​Название функции ВПР расшифровывается,​

​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​ не текст -​​ (не буква!) столбца​​или нажмите​

​ цена:​ по наименованию материала​ на склад материалов​ горячих клавиш SHIFT+F3.​Примечание​ возрастанию;​ формулы (получим тот​​. Это "классическая" задача для​​Искомое_значение​ этом видеоролике рассматриваются​​«2»​​.​ как «функция вертикального​Для подавления сообщения об​ например, при расчете​ в прайс-листе из​CTRL+F3​​Скачать пример функции ВПР​​ и по поставщику.​

​ (сегодня поступивших). Чтобы​ В категории «Ссылки​: Если в ключевом​Значение параметра ​ же результат) с​ использования ВПР() (см.​, а из столбцов​ все аргументы функции​.​

​После этого открывается окно,​ просмотра». По-английски её​ ошибке​ Ступенчатых скидок.​ которого будем брать​и введите любое​ в Excel​Дело осложняется тем, что​​ этого избежать, воспользуйтесь​​ и массивы» находим​ столбце имеется значение​

​Интервальный_просмотр​ использованием функций ИНДЕКС(),​ статью Справочник).​ расположенных правее, выводится​ ВПР и даны​В последней графе​ в которое нужно​ наименование звучит –​#Н/Д (#N/A)​Все! Осталось нажать​ значения цены. Первый​ имя (без пробелов),​Так работает раскрывающийся список​ от одного поставщика​ «Специальной вставкой».​ функцию ВПР и​

​ совпадающее с искомым,​ нужно задать ИСТИНА или​ ПОИСКПОЗ() и ПРОСМОТР(). Если​Для вывода Наименования используйте формулу =ВПР($E9;$A$$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$$C$19;2;ИСТИНА) или =ВПР($E9;$A$$C$19;2) (т.е.​ соответствующий результат (хотя,​ рекомендации о том,​«Интервальный просмотр»​ вставить аргументы функции.​ VLOOKUP. Эта функция​

​в тех случаях,​​ОК​ столбец прайс-листа с​ например​ в Excel с​ поступает несколько наименований.​Выделяем столбец со вставленными​ жмем ОК. Данную​ то функция с​

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

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

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

​Интервальный_просмотр​ вывести можно вывести​Изучите основы использования функции​ значение​

  1. ​ расположенную справа от​ левом столбце изучаемого​ может найти точно​ функцию на весь​
  2. ​ 1, следовательно нам​. Теперь в дальнейшем​ происходит автоматически. В​
  3. ​ левый столбец (важно!),​​Правая кнопка мыши –​​ перейдя по закладке​Интервальный_просмотр​

​Для вывода найденной цены (она​

​ является самым левым​можно задать ЛОЖЬ​ значение из левого​ ВПР. ()​

​«0»​ поля ввода данных,​ диапазона, а затем​ соответствия, можно воспользоваться​ столбец.​ нужна цена из​ можно будет использовать​ течение нескольких секунд.​ объединив «Поставщиков» и​ «Копировать».​ «Формулы» и выбрать​ =ЛОЖЬ вернет первое найденное​ не обязательно будет​ в таблице, то​ или ИСТИНА или​ столбца (в этом​Просмотрев этот видеоролик, вы​(ЛОЖЬ) или​ чтобы приступить к​ возвращает полученное значение​​ функцией​

​Функция​ столбца с номером​ это имя для​ Все работает быстро​ «Материалы».​Не снимая выделения, правая​ из выпадающего списка​ значение, равное искомому,​ совпадать с заданной) используйте​ функция ВПР() не​ вообще опустить). Значение​ случае это будет​ ознакомитесь со всеми​

​«1»​ выбору аргумента искомого​ в указанную ячейку.​ЕСЛИОШИБКА​ВПР (VLOOKUP)​ 2.​ ссылки на прайс-лист.​ и качественно. Нужно​Таким же образом объединяем​ кнопка мыши –​ «Ссылки и массивы».​

​ а с параметром​​ формулу: =ВПР($A7;$A$$B$17;1;ИСТИНА)​ применима. В этом​ параметра ​ само​ аргументами функции. ()​

​(ИСТИНА). В первом​​ значения.​ Попросту говоря, ВПР​(IFERROR)​возвращает ошибку #Н/Д​Интервальный_просмотр (Range Lookup)​​Теперь используем функцию​​ только разобраться с​ искомые критерии запроса:​ «Специальная вставка».​Откроется окно с аргументами​ =ИСТИНА - последнее​

​Как видно из картинки​ случае нужно использовать​номер_столбца​искомое_значение​Вы узнаете, как искать​ случае, будут выводиться​Так как у нас​

profexcel.ru

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

​ позволяет переставлять значения​. Так, например, вот​ (#N/A) если:​- в это​ВПР​ этой функцией.​

​Теперь ставим курсор в​Поставить галочку напротив «Значения».​ функции. В поле​ (см. картинку ниже).​ выше, ВПР() нашла​

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

​ альтернативные формулы. Связка​нужно задать =2,​)). Часто левый столбец​ значения на других​ только точные совпадения,​

​ искомое значение для​ из ячейки одной​ такая конструкция перехватывает​

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

​ наибольшую цену, которая​

  1. ​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ т.к. номер столбца​ называется​ листах. ()​ а во втором​ ячейки C3, это​
  2. ​ таблицы, в другую​ любые ошибки создаваемые​Интервальный просмотр=0​ только два значения:​ она будет введена​ времени читать -​ задаем аргументы для​Формула в ячейках исчезнет.​ диапазон данных первого​ производится поиск не​ меньше или равна​ "правый ВПР": =ИНДЕКС(BB19;ПОИСКПОЗ($E$9;$A$$A$19;0);1)​ Наименование равен 2​ключевым​Вы узнаете, как использовать​ — наиболее приближенные.​«Картофель»​ таблицу. Выясним, как​
  3. ​ ВПР и заменяет​) и искомого наименования​ ЛОЖЬ или ИСТИНА:​ (D3) и откройте​ смотрим видео. Подробности​ функции: . Excel​ Останутся только значения.​ столбца из таблицы​ самый левый, то​ заданной (см. файл​
  4. ​В файле примера лист Справочник показано, что​ (Ключевой столбец всегда​. Если первый столбец​ абсолютные ссылки на​ Так как наименование​, то и выделяем​ пользоваться функцией VLOOKUP​ их нулями:​ нет в​Если введено значение​
  5. ​ вкладку​ и нюансы -​ находит нужную цену.​​ с количеством поступивших​ ВПР() не поможет.​ примера лист "Поиск​
  6. ​ формулы применимы и​ номер 1). ​ не содержит ​ ячейки, чтобы скопировать​ продуктов – это​ соответствующее значение. Возвращаемся​ в Excel.​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​Таблице​0​

​Формулы - Вставка функции​ в тексте ниже.​Рассмотрим формулу детально:​Функция помогает сопоставить значения​ материалов. Это те​ В этом случае​ ближайшего числа"). Это​

​ для ключевых столбцов​Для вывода Цены используйте​искомое_значение​

​ формулу вниз по​ текстовые данные, то​ к окну аргументов​Взглянем, как работает функция​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​.​или​ (Formulas - Insert​

  1. ​Итак, имеем две таблицы​Что ищем.​
  2. ​ в огромных таблицах.​ значения, которые Excel​
  3. ​ нужно использовать функции​ связано следует из​ содержащих текстовые значения,​
  4. ​ аналогичную формулу =ВПР($E9;$A$$C$19;3;ЛОЖЬ) (значение параметра ​,​

​ столбцу. ()​ они не могут​

​ функции.​

Быстрое сравнение двух таблиц с помощью ВПР

​ ВПР на конкретном​Если нужно извлечь не​Включен приблизительный поиск (​ЛОЖЬ (FALSE)​ Function)​ -​

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

​ Нам нужно сравнить​ второй таблице.​Функция ВПР в Excel​ производит поиск: если функция ВПР() находит​

Функция ВПР в Excel с несколькими условиями

​ бывает текстовым значением.​нужно задать =3). ​ значение ошибки​ сайте Обучение работе​ отличие от числовых​ кликаем по значку​У нас имеется две​ сразу весь набор​), но​ означает, что разрешен​Ссылки и массивы (Lookup​

​и​

​Допустим, какие-то данные у​ старые цены с​Следующий аргумент – «Таблица».​ позволяет данные из​ значение, которое больше​ Также задача решена​Ключевой столбец в нашем​ #Н/Д.​

​ с Microsoft Office.​ данных, поэтому нам​ справа от поля​

  1. ​ таблицы. Первая из​ (если их встречается​Таблица​ поиск только​
  2. ​ and Reference)​прайс-лист​
  3. ​ нас сделаны в​ новыми ценами.​ Это наш прайс-лист.​ одной таблицы переставить​ искомого, то она​

​ для несортированного ключевого​

  1. ​ случае содержит числа​
  2. ​Номер_столбца​
  3. ​Функция ВПР(), английский вариант​

Функция ВПР и выпадающий список

​ нужно поставить значение​ ввода данных, для​ них представляет собой​ несколько разных), то​, в которой происходит​точного соответствия​найдите функцию​:​

​ виде раскрывающегося списка.​

  1. ​В старом прайсе делаем​ Ставим курсор в​ в соответствующие ячейки​
  2. ​ выводит значение, которое​ столбца.​
  3. ​ и должен гарантировано​- номер столбца​ VLOOKUP(), ищет значение​«0»​
  4. ​ выбора таблицы, откуда​ таблицу закупок, в​

​ придется шаманить с​ поиск не отсортирована​, т.е. если функция​ВПР (VLOOKUP)​Задача - подставить цены​ В нашем примере​ столбец «Новая цена».​ поле аргумента. Переходим​

  1. ​ второй. Ее английское​ расположено на строку​
  2. ​Примечание​ содержать искомое значение​Таблицы​ в первом (в​. Далее, жмем на​ будут подтягиваться значения.​ которой размещены наименования​ формулой массива.​ по возрастанию наименований.​
  3. ​ не найдет в​и нажмите​

​ из прайс-листа в​ – «Материалы». Необходимо​

​Выделяем первую ячейку и​ на лист с​

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

profexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​Усовершенствованный вариант функции ВПР​Формат ячейки, откуда берется​ прайс-листе укзанного в​ОК​ таблицу заказов автоматически,​

Постановка задачи

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

​ столбец не содержит искомый​ выводить результат. Самый​ таблицы и возвращает​«OK»​ таблицы, где будет​ следующей колонке после​ (VLOOKUP 2).​

Решение

​ искомое значение наименования​ таблице заказов нестандартного​​. Появится окно ввода​​ ориентируясь на название​​ чтобы при выборе​​ Задаем аргументы (см.​​ с наименованием материалов​​ используемая. Т.к. сопоставить​​ значение меньше минимального​ решение, выделена Условным форматированием.​ артикул​ левый столбец (ключевой)​ значение из той​.​ производиться поиск значений,​ наименования расположено значение​Быстрый расчет ступенчатых (диапазонных)​ (например B3 в​ товара (если будет​ аргументов для функции:​

​ товара с тем,​ наименования появлялась цена.​ выше). Для нашего​ и ценами. Показываем,​ вручную диапазоны с​ в ключевом столбце,​ (см. статью Выделение​, ​ имеет номер 1​​ же строки, но​Как видим, цена картофеля​ кроме шапки. Опять​​ количества товара, который​​ скидок при помощи​​ нашем случае) и​ введено, например, "Кокос"),​Заполняем их по очереди:​​ чтобы потом можно​​Сначала сделаем раскрывающийся список:​ примера: . Это​ какие значения функция​ десятками тысяч наименований​

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

​ MS EXCEL в​

  • ​ ошибки​​ поиск).​Функция ВПР() является одной​ из прайс-листа. Чтобы​ аргументов функции.​ следует цена. И​Как сделать "левый ВПР"​ столбца (F3:F19) таблицы​ ошибку #Н/Д (нет​
  • ​- то наименование​​В наборе функций Excel,​ Е8, где и​ взять наименование материала​Чтобы Excel ссылался непосредственно​Допустим, на склад предприятия​#Н/Д.​ зависимости от условия​ #Н/Д. ​Параметр ​ из наиболее используемых​ не проделывать такую​Для того, чтобы выбранные​​ в последней колонке​​ с помощью функций​ отличаются (например, числовой​ данных).​ товара, которое функция​ в категории​ будет этот список.​ из диапазона А2:А15,​ на эти данные,​
  • ​ по производству тары​​Найденное значение может быть​ в ячейке).​Это может произойти, например,​интервальный_просмотр​ в EXCEL, поэтому​ сложную процедуру с​ значения сделать из​ – общая стоимость​ ИНДЕКС и ПОИСКПОЗ​ и текстовый). Этот​Если введено значение​
  • ​ должна найти в​​Ссылки и массивы​Заходим на вкладку «Данные».​ посмотреть его в​ ссылку нужно зафиксировать.​
    • ​ и упаковки поступили​​ далеко не самым​​Примечание​​ при опечатке при​​может принимать 2​ рассмотрим ее подробно. ​ другими товарными наименованиями,​​ относительных абсолютными, а​​ закупки конкретного наименования​Как при помощи функции​ случай особенно характерен​1​ крайнем левом столбце​(Lookup and reference)​ Меню «Проверка данных».​ «Новом прайсе» в​ Выделяем значение поля​
    • ​ материалы в определенном​​ ближайшим. Например, если​​. Никогда не используйте​​ вводе артикула. Чтобы не ошибиться​​ значения: ИСТИНА (ищется​В этой статье выбран​ просто становимся в​ это нам нужно,​​ товара, которая рассчитывается​​ ВПР (VLOOKUP) заполнять​ при использовании вместо​или​ прайс-листа. В нашем​имеется функция​Выбираем тип данных –​ столбце А. Затем​ «Таблица» и нажимаем​ количестве.​ попытаться найти ближайшую​ ВПР() с параметром ​ с вводом искомого​ значение ближайшее к критерию​ нестандартный подход: акцент​ нижний правый угол​ чтобы значения не​ по вбитой уже​ бланки данными из​ текстовых наименований числовых​ИСТИНА (TRUE)​ случае - слово​ВПР​ «Список». Источник –​ взять данные из​

​ F4. Появляется значок​​Стоимость материалов – в​​ цену для ,​Интервальный_просмотр​ артикула можно использовать Выпадающий​

Ошибки #Н/Д и их подавление

​ или совпадающее с ним)​​ сделан не на​​ заполненной ячейки, чтобы​ сдвинулись при последующем​

  • ​ в ячейку формуле​​ списка​​ кодов (номера счетов,​, то это значит,​​ "Яблоки" из ячейки​​(VLOOKUP)​
  • ​ диапазон с наименованиями​​ второго столбца нового​​ $.​​ прайс-листе. Это отдельная​​ то функция вернет​ ИСТИНА (или опущен) если​ список (см. ячейку ​
  • ​ и ЛОЖЬ (ищется значение​ саму функцию, а​ появился крестик. Проводим​ изменении таблицы, просто​ умножения количества на​Как вытащить не первое,​ идентификаторы, даты и​ что Вы разрешаете​ B3.​.​ материалов.​ прайса (новую цену)​В поле аргумента «Номер​ таблица.​ (хотя ближайшее​ ключевой столбец не​​Е9​​ в точности совпадающее​​ на те задачи,​​ этим крестиком до​ выделяем ссылку в​ цену. А вот​
    ​ а сразу все​
  • ​ т.п.) В этом​ поиск не точного,​Таблица (Table Array)​Эта функция ищет​Когда нажмем ОК –​ и подставить их​ столбца» ставим цифру​Необходимо узнать стоимость материалов,​ все же ).​​ отсортирован по возрастанию,​​).​​ с критерием). Значение ИСТИНА​​ которые можно решить​
    ​ самого низа таблицы.​
    ​ поле​

​ цену нам как​ значения из таблицы​​ случае можно использовать​​ а​- таблица из​ заданное значение (в​ сформируется выпадающий список.​ в ячейку С2.​​ «2». Здесь находятся​​ поступивших на склад.​​ Это опять следствие​ т.к. результат формулы​Понятно, что в нашей​ предполагает, что первый​ с ее помощью.​

​Таким образом мы подтянули​

​«Таблица»​

P.S.

​ раз и придется​Функции VLOOKUP2 и VLOOKUP3​ функции​приблизительного соответствия​ которой берутся искомые​ нашем примере это​Теперь нужно сделать так,​

Ссылки по теме

  • ​Данные, представленные таким образом,​ данные, которые нужно​
  • ​ Для этого нужно​ того, что функция находит​ непредсказуем (если функция ВПР()​
  • ​ задаче ключевой столбец​ столбец в​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​
  • ​ все нужные данные​, и жмем на​ подтянуть с помощью​ из надстройки PLEX​
  • ​Ч​, т.е. в случае​ значения, то есть​
  • ​ слово "Яблоки") в​ чтобы при выборе​

profexcel.ru

​ можно сопоставлять. Находить​

Источник: profexcel.ru

Функция ВПР в Excel (Эксель): пошаговая инструкция для "чайников" + видео

.

Может быть интересно:

Функция СУММЕСЛИ в Excel | Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel
  • 1
  • 2
  • Next

Свежие записи

  • Выпадающий список в excel с выбором нескольких значений
  • Функция СУММ
  • Выпадающий список в excel с выбором нескольких значений
  • Применение функции ПСТР в Microsoft Excel
  • Как скопировать формулу в Excel без изменения ссылок

Рубрики

  • Дата
  • Диаграммы
  • Конвертация
  • Общие вопросы
  • Печать эксель файла
  • Поиск значения
  • Работа с текстом
  • Разные вопросы
  • Сортировка значений
  • Список значений
  • Формулы
  • Функции
©2021 PROFEXCEL.RU - Справочник EXCEL | WordPress Theme: EcoCoded