Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

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

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

  • Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
  • Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
  • Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
  • Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

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

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

  • Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
  • Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
  • Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
    =ВПР(ТЕКСТ(B3);прайс;0)
  • Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
    =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
    =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

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

Как работать с функцией ВПР в Эксель: инструкция для новичков

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как узнать сумму столбца в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для решения задачи нам необходимо подтянуть цены на товары из таблицы «Прайс-лист» в столбец «Цена за кг» таблицы «Заказы». Т.е. подставить цены, которые соответствуют каждому товару из нижней таблицы в верхнюю.

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

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

У функции ВПР 4 аргумента. Первым аргументом, который мы подставим в формулу, является то самое искомое значение, цену для которого нужно найти во второй таблице «Прайс-лист». После написания названия функции и добавления открывающей скобки, щелкаем по искомой ячейке и добавляем ее аргументом. В моем случае это ячейка «E4» со значением «Яблоки». Далее ставим разделитель — точка с запятой.

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

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

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

Последний параметр называется «Интервальный просмотр» — это логический аргумент и может принимать только 2 значения: 0 или 1 (включено/выключено). Данным значением мы определяем, точно, цифра 0, или приблизительно, цифра 1, мы ищем наименование в прайс-листе. В случае с текстовыми наименованиями лучше использовать точный поиск (цифра 0), потому что приблизительный более менее точно работает в случае ячеек с числами. Вводим интервальный просмотр как 0 и закрываем скобку. После этого нажимаем клавишу Enter, чтобы формула сработала. В итоге мы получим ячейку с ценой, подтянутой из таблицы прайс-листа.

В колонку «Итоговая стоимость» прописываем простую формулу умножения веса партии на цену за кг.

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

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

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

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

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

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

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

Читать еще:  Как создать шаблон в word

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

Есть некоторые особенности работы, которые необходимо пояснить, чтобы было понятно, что здесь произошло. Мы видим, что при определенном весе функция вытащила из второй таблицы определенную партию. Чтобы более точно понять логику, давайте посмотрим на вес, к примеру, в 15 кг. В таблице с размерами партий, такого критерия нет. Но когда мы ставим интервальный просмотр в значение 1, функция будет вытаскивать значение, которое является ближайшим меньшим. При весе партии в 15 кг, ВПР вытащит из таблицы значение с критерием 10. Если вес партии будет 47 кг, то функция вытащит значение с критерием 30, которое будет ближайшим и меньшим.

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

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

Не забудьте поделиться ссылкой на статью ⇒

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

  • 27.11.2015
  • Просмотров: 4414
  • Excel

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

  • 24.12.2015
  • Просмотров: 68813
  • Excel

В сегодняшнем уроке я расскажу как вставить формулу в Word. На самом деле существует несколько способов это сделать и по возможности я постараюсь рассмотреть их все.

  • 04.02.2016
  • Просмотров: 18673
  • Word

В этой статье расскажу как удалить дубликаты в Excel. Рассматривать будем самый простой штатный способ, который появился начиная с Excel 2007.

  • 30.10.2015
  • Просмотров: 4724
  • Excel
  • Видеоурок

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

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

Здравствуй уважаемый читатель!

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

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

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

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

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

  • искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
  • таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
  • номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
  • интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE). Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

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

Формула ищет текст «GM», в столбике А на текущем листе. Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как абсолютные ссылки (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

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

=ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

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

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

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

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

  • «?» (знак вопроса) – позволит заменить один любой символ;
  • «*» (звёздочка) – заменит любое количество и последовательность символов.

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ». Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

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

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

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

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

Для поиска используем ВПР следующего вида:

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

Читать еще:  Как восстановить несохраненный документ word 2020

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Без сомнения, бережливость лучше расточительности, но также неоспоримо, что она хуже полезной затраты.
Генри Форд

Спасаемся от рутинной работы посредством функции ВПР в Excel

Наверняка многим активным пользователям табличного редактора Excel периодически приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некий товар. В нашем распоряжении имеется два файла: один с перечнем наименований полученного товара, второй — прайс-лист этого самого товара. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и расположены они не в той последовательности, что в файле с перечнем наименований. Вряд ли кому-то из нас понравится идея сверить оба файла и перенести цены из одного документа в другой вручную. Разумеется, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных вполне возможно, но что делать, если число наименований переваливает за 1000? В таком случае справиться с монотонной работой нам поможет Excel и его волшебная функция ВПР (или vlookup, если речь идёт об англоязычной версии программы).

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

Что такое ВПР и как ею пользоваться?

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

Допустим, нам необходимо заполнить “Таблицу заказов” данными из “Прайс листа”

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

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

Внесите формулу для первого столбца

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Важный момент

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

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

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

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

Потяните маркер в правом нижнем углу ячейки для применения формулы ко всей таблице

Как сравнить две таблицы в Excel?

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

Возможность работы с несколькими условиями

Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

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

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

Ссылка на основную публикацию
Adblock
detector