Tw-city.info

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

Как расшифровывается впр в excel

Функция ВПР в Excel и её секреты. Поиск VLOOKUP в базах данных

Функция ВПР в Exce l позволяет работать с большими таблицами и базами данных. В статье рассмотрим примеры использования функции ВПР в Excel. Дополнительно с функцией Excel VLOOKUP разберем динамический индекс Col index num, IF и поиск в нескольких столбцах или базе ДВССЫЛ INDIRECT.

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

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

  • Критерий поиска (Lookup_value)
  • Матрица (Table_array)
  • Индекс (Col_index_num)
  • Порядок сортировки (Range_lookup).

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

Структура формулы ВПР в Excel

Первая строка – значение, которое вы хотите найти в базе данных Excel. В примере необходимая информация находится в ячейке B3. Если вы введете ее адрес в первое поле, при помощи выражения можно найти нужные сведения в списке со сведениями о персонале.

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

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

Существует два варианта значений для параметра «Порядок сортировки». Введете 0 – тогда результатом вставки будет ошибка #Н/Д, если не сможет найти в базе Экселя результаты по запросу.

Результат отображается только в том случае, если между заданным критерием отбора (Lookup_value) и результатами в крайней первой колонке массива (Table_array) будет обнаружено абсолютное совпадение.

Введете 1 – и формула тоже отобразит результат, если найдет полное совпадение между Lookup_value и содержимым какой-либо строки в первом столбце матрицы (Table_array).

Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Другими словами, если вы в примере вместо B3 напишете 12, функция вернет ответ «Gwendy», потому что 10 является числом, максимально приближенным к 12. Первая колонка в таблице заранее должна быть отсортирована по возрастанию, если вы вставляете 1 в Range_lookup.

Поиск приблизительного значения ВПР в Excel

В примере (снимок № 2) ВПР используется для вычисления прибавки на основе текущего оклада. Критерий отбора – это заработная плата, написана в F3. Матрица – это диапазон $I$3:$J$10 (весь массив).

Адрес является абсолютным (это показывают знаки $), чтобы осталась возможность копировать всю получившуюся конструкцию без изменения диапазона. Col_index_num равен 2 (процент повышения находится в соседнем массиве).

Параметр порядка сортировки Range_lookup – 1, потому что нужно, чтобы формула выдавала результат, даже если нет идеального соответствия. Если Lookup_value составляет 35 850, ВПР не может найти идеальное совпадение в массиве, но ближайшее минимальное число – 35 000, поэтому ответом на выражение будет 4%. На этом ликбез « ВПР в Excel для чайников » заканчивается.

Сравнение двух списков с помощью ВПР в Excel

Вы можете использовать ВПР в Excel для сравнения двух баз данных . Критерий отбора в этом примере (скриншот № 3) – идентификатор персонала (он должен быть уникальным значением) в первой колонке.

Матрица – это диапазон идентификаторов персонала во втором списке или массиве. Сделайте ссылку на массив абсолютной, используя знаки $ в адресах ячеек ($I$3:$I$9), чтобы иметь возможность копировать выражение без изменения ссылок на ячейки диапазона.

В аргументе «Порядок сортировки» введите 0 – это обязательно потому, что вам нужно только точное пересечение. Скопируйте получившееся, и если в ответ отображается #Н/Д, это значит, что во втором списке или в базе искомая запись отсутствует.

Динамический индекс (Col_index_num) с использованием нумерованных столбцов

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

Это позволяет сэкономить очень много времени и не вводить номер столбца каждый раз. На снимке № 4 нумерация колонок выполняется в строке 4. В поле Col_index_num нужно ввести C4, и получится первый динамический индекс.

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

Динамический индекс со вложенными IF (ЕСЛИ)

Вы можете использовать формулы IF (ЕСЛИ) в Экселе для создания динамического индекса. В примере (скриншот № 5) премия рассчитывается в зависимости от отдела и категории.

Если сотрудник, работая в отделе продаж, входит в группу 1 бонусной категории, формула VLOOKUP должна вернуть 3%, если в группу 5 бонусной категории – 4%, а если в группу 10 – 5%.

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

Критерий поиска – это отдел, E3. Матрица – диапазон $J$3:$M$7. В поле «Индекс» указывается конструкция по типу IF(G3=«Группа 1»,2,IF(G3=«Группа 5»,3,4)).

С помощью первой функции IF вы сможете узнать, соответствует ли G3 запросу «Группа 1». Если это так, формула VLOOKUP выберет бонус из второго столбца. Если это неправда, второе выражение IF проверяет, соответствует ли G3 запросу «Группа 5».

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

Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)

В примере (снимок № 6) имеется 3 таблицы. Диапазоны данных в каждой из них имеют названия «Финансы», «Производство» и «Продажи».

В этом примере вам нужно найти сотрудника из отдела продаж с идентификатором 3. Название диапазона вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT воспринимает содержимое C19 как имя диапазона.

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

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

Особенности функции вертикального поиска в Excel

  • Регистр в запросах не учитывается.
  • В строке «Порядок сортировки» значение по умолчанию равно 1 (TRUE-ПРАВДА). Поэтому лучше не пропускать этот аргумент, если вам нужны только точные совпадения.
  • В первом поле (там, где вы пишете запрос) допускается использование подстановочных знаков.

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

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

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

Описание функции ВПР

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

Читать еще:  Доверительный интервал в excel

Применение функции ВПР на практике

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

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

Порядок действий в данном случае следующий:

  1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
  3. Теперь предстоит правильно заполнить аргументы функции:
    • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
    • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
      Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
    • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
    • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
    • в значении аргумента “Интервальный_просмотр” можно указать два значения:
      • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
      • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
      • мы выбираем первый вариант, так как нам важна предельная точность.
    • Когда все готово, нажимаем OK.
  4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
  5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
  6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать excel в разных окнах

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

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

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

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

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

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

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

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

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

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

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

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

  • 12.01.2016
  • Просмотров: 12600
  • Excel

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

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

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

  • 07.03.2019
  • Просмотров: 4848
  • Excel

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

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

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

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

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

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

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

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

Функция ВПР является одной из востребованных в Экселе, конечно, для тех, кто имеет представление о ее существовании.

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

Пример задания аргументов функции ВПР в экселе

Как всякая функция Excel, данная имеет собственный синтаксис, которому необходимо следовать. Она включает четыре параметра: искомое значение, ссылку на диапазон значений, номер столбца, из которого будет возвращено искомое значение, и последний параметр — «отсортировано». Он может быть представлен как истина или ложь или вовсе опущен. Тогда он считается как 1 — истина.

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

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

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

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

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

Читать еще:  Папка автосохранения excel

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

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

Функция ВПР незаменима при необходимости объединить данные из разных таблиц, имеющих какое-либо общее поле (наименование, штрих-код и т.д.)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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