Любой, кто имеет дело с числами — будь то в профессиональных целях или просто из любви к искусству — конечно же, первым делом овладевает калькулятором. Что дальше? На очереди — блестящая программа для работы с числами: Excel. Знать эту программу в совершенстве, как и в совершенстве математики, вероятно, невозможно. Однако в программе есть базовые функции, функции Excel, зная, что с 90% уверенностью можно говорить о возможности работы с данными на уровне защищенного пользователя. Одна из этих самых важных функций в Excel — ВПР, она очень универсальна. Как использовать ВПР в Excel (для чайников) рассказано в статье. Конечно, понимание теории не приведет к полному пониманию процесса использования функции. Практика требуется сначала с небольшими наборами данных, а затем с таблицами любого размера.
Сущность и назначение функции ВПР в Excel
Функция ВПР экономит много времени для таких специалистов, как экономисты, финансисты, бухгалтеры, технологи. Описание функции ВПР в Excel и пример ее использования следует начинать с рассмотрения ситуаций, с которыми профессионалы всех мастей часто сталкиваются в своей работе. Если есть две таблицы, в которых появляются одни и те же определяемые данные (например, названия товаров), но определения для них в разных таблицах различаются, например, в одной есть количество, а в другой — цена. При этом стоит задача объединить все эти данные воедино и провести соответствующие вычисления — в таких ситуациях на помощь приходит функция ВПР. Чтобы понять, как работает ВПР в Excel, есть две таблицы ниже. Каждый из них содержит код продукта. Один содержит соответствующее количество, другой — цену. ВПР используется для замены цены в первой таблице.
Замена данных между таблицами вручную — огромная трата времени и усилий сотрудника, и ошибок нельзя избежать. Используя ВПР, вы можете быстро и легко найти совпадающие значения. Для более глубокого понимания того, как использовать ВПР в Excel, необходимо проанализировать все аргументы функции.
Для использования функции необходимо перейти в получившуюся ячейку, выбрать на ленте вкладку «Формулы» — «Ссылки и массивы» — «ВПР». Теперь в ячейке есть слова «= ВПР («. Теперь вам нужно правильно ввести аргументы функции. Вы можете сделать это через точку с запятой прямо в строке формул. Однако новичку удобнее сделать это через диалоговое окно аргумента функции.
Функция ВПР имеет четыре аргумента: значение подстановки, таблица, номер столбца, сканирование диапазона. Первые три, выделенные жирным шрифтом в диалоговом окне, являются обязательными. Поясним, каковы аргументы функции:
- желаемое значение — на что обращать внимание;
- таблица — где смотреть;
- номер столбца — в каком столбце искать;
- просмотр через промежуток — аккуратно.
Аргумент «Искомое значение»
В первом поле вы должны ввести то, что вы хотите найти. Это может быть число, текст, дата. Вы можете ввести абсолютное значение, вы можете ввести ссылку на ячейку с желаемым значением. Желаемое значение теоретически должно присутствовать в обеих таблицах. В приведенном выше примере это значение может быть кодом продукта. Во избежание ошибок лучше воспользоваться ссылкой.
Рекламная пауза
При использовании текста в качестве значения поиска его необходимо заключать в кавычки. При ссылке на ячейку с текстом кавычки не требуются. При вводе текста шанс не имеет значения. Нечеткий поиск можно использовать по фрагменту текста. Для этого в кавычки необходимо заключить фрагмент текста между * символами *.
Самая распространенная проблема при использовании функции — это различный формат ячеек в диапазонах. Если в поле первого аргумента вы вводите ссылку на ячейку, где данные находятся в текстовом формате, а в диапазоне поиска данные будут в числовом формате, если значения внешне идентичны, ВПР не найдет совпадения.
Преобразовать в числовой формат просто — умножьте массив на единицу.
Аргумент «Таблица»
Здесь вам нужно ввести диапазон, в котором функция будет искать первое введенное значение. Диапазон, в котором нужно искать значения, будет первым столбцом выбора. Значение, которое вы хотите найти и вставить в результате, должно находиться в столбце справа от столбца поиска совпадений. Это один из основных недостатков использования ВПР: для работы с ним часто приходится перестраивать таблицу, чтобы требуемые данные находились слева. Диапазон можно ввести вручную или для справки. В примере это выглядит так:
В таблице справа указан диапазон. Требуемое значение находится в правом столбце, где Excel будет искать значение 3187849428, значение, которое необходимо найти и заменить в результате формулы слева. Для получения точного результата лучше зафиксировать диапазон, выделив его и нажав клавишу F4, ссылка на массив станет абсолютной.
Аргумент «Номер столбца»
Здесь нужно ввести число, в столбце которого, начиная с крайнего левого, нужно брать значение для замены как результат расчета. В приведенном выше примере это второй столбец, в строке аргумента необходимо ввести число 2. Если бы между столбцами «Код» и «Цена» был другой столбец, то необходимо было бы ввести число 3 и так улица.
Аргумент «Интервальный просмотр»
Заполнение этого поля необязательно, но может быть очень важным. Это может быть одно из двух значений: 1 (истина) или 0 (ложь). Большинство пользователей полагают, что функция данного аргумента состоит в том, чтобы определить, точно ли совпадают значения поиска. Это не совсем так. Если вы укажете единицу измерения, если в диапазоне таблицы есть дубликаты, функция вернет последнее найденное значение. В этом случае функция ВПР будет учитывать все значения, меньшие или равные значению, введенному в поле «Значение поиска». Если функция находит большее значение, но не находит меньшее или равное значение, она генерирует ошибку N / A.
Видно, что в поле «Значение» введено число 3187849425, такого значения в желаемом диапазоне нет и программа, найдя все значения, меньшие или равные желаемому, вернула значение, соответствующее последний совпадающий код в списке — 3187848593, цена которого составляет 2479,46 руб. Если вы оставите поле Preview Range пустым, функция будет работать так же, как и значение единицы.
Когда вы вводите значение 0 в поле аргумента, функция возвращает только то значение, которое соответствует желаемому. Если в диапазоне поиска есть повторы, функция возьмет первое совпадение. При использовании формулы с аргументом 0 функция занимает гораздо больше времени, но гораздо точнее.
Особенности использования ВПР по нескольким условиям
Использование функции ВПР часто представляет собой творческий процесс, требующий от пользователя математического мышления. Часто бывает необходимо найти совпадение не для одного столбца, а для двух и даже более. Вы также можете использовать функцию ВПР с дополнительными шагами. Вам необходимо создать дополнительные столбцы в обеих таблицах, которые объединяют данные из этих столбцов. Это можно сделать с помощью функции ЦЕПЬ или значка «&».
Как пользоваться функцией ВПР, если данные на разных листах
Как использовать ВПР в Excel на разных листах, в разных файлах ?. Часто пользователи создают какие-то справочники, которые нужно сравнивать и сопоставлять. Отличие работы в таких условиях заключается в несколько ином виде поля «Таблица» в окне аргументов функции. Перед вводом диапазона необходимо ввести номер листа (если данные находятся в книге) или имя файла (если данных нет в книге). Если в приведенном выше примере прайс-лист скопирован в отдельный файл, назовите его «Цена» и с помощью функции ВПР найдите там цены, он будет выглядеть следующим образом:
Все вышеперечисленные действия можно выполнить по ссылке. Вам необходимо установить курсор в поле «Таблица», перейти к нужному файлу и выделить диапазон мышью. Иногда бывает, что таблица не вставляется как ссылка в окне. Далее нужно сделать следующее: открыть исходную таблицу, в которой нужно произвести расчеты, затем через меню «Файл» — «Открыть» найти вторую таблицу. Файлы, открытые таким образом, отлично взаимодействуют.
Все вышеперечисленные действия можно выполнить по ссылке. Вам необходимо установить курсор в поле «Таблица», перейти к нужному файлу и выделить диапазон мышью. Иногда бывает, что таблица не вставляется как ссылка в окне. Далее нужно сделать следующее: открыть исходную таблицу, в которой нужно произвести расчеты, затем через меню «Файл» — «Открыть» найти вторую таблицу. Файлы, открытые таким образом, отлично взаимодействуют.
Рекламная пауза
Файл, содержащий формулы ВПР, будет весить намного больше при связывании с другими файлами, чем без них. Это может, например, сделать передачу файла проблематичной. Чтобы избежать этих проблем, вам необходимо преобразовать формулу в значения. На ленте выберите подменю «Данные» и команду «Изменить ссылки». Даже если данные поступают не из другого файла, всегда полезно заменять формулы значениями: это делает вычисления более надежными.
Выпадающий список для облегчения работы с ВПР
Часто функция ВПР не работает с небольшими неточностями данных. Либо в тексте есть лишний пробел, либо данные введены с ошибками. Вы можете избежать всех этих проблем, используя раскрывающийся список в Excel для ввода значений. Имеет смысл запустить его при работе с постоянно повторяющимися данными. Если существует справочник, используемый в качестве таблицы для сравнения, диапазон сравнения может быть взят как данные для раскрывающегося списка и использован для формирования таблицы, в которой данные затем будут заменены функцией ВПР. Курсор помещается в ячейку, в которую нужно ввести значение. Далее на ленте находится подраздел «Данные», выбрана команда «Проверить данные». В диалоговом окне поле Тип данных заполняется списком. Диапазон справочной таблицы записывается в поле «Источник». Выпадающий список завершен. Теперь при заполнении таблицы полное совпадение значений гарантировано.
Пример использования функции VLOOKUP в Excel
Как использовать ВПР в Excel каждый пользователь решает сам, исходя из практических задач в своей работе. Учитывая все вышеперечисленные нюансы, можно довести предыдущий пример до логического завершения и создать удобный и понятный алгоритм практического использования функции ВПР. Отсюда и оценка затрат на запчасти для ремонта оборудования. Также есть прайс-лист на запчасти. Экономист должен найти стоимость запчастей для ремонта.
Во-первых, вам нужно правильно написать формулу.
Поэтому при использовании функции ВПР цена, соответствующая коду, появится в первой строке. Вам нужно умножить формулу вниз, для этого выделите ячейку с формулой и перетащите квадрат в правом углу ячейки вниз. В столбце D функция вернула цены, соответствующие кодам. Замените формулы значениями. Для этого выделите заполненные ячейки в столбце D, скопируйте и вставьте их как значения. Затем вам нужно создать столбец «Сумма», в который следует ввести формулу для произведения количества и цены, а затем использовать сумму для отображения общей стоимости.
Это пример того, как работает ВПР в Excel.
Ошибки при использовании функции ВПР
На начальном этапе использования вместо желаемых значений функция часто указывает различные типы ошибок. Знание того, что означает конкретная ошибка, — верный способ ее быстро исправить. Наиболее часто возвращаемые ошибки:
- «Н / Д» — наиболее распространенный тип ошибки. Это может возникнуть по нескольким причинам.
- Столбец, в котором функция ищет совпадение, расположен неправильно (должен быть крайним левым). Если возникает ситуация, когда искомое значение находится слева от зоны поиска совпадений, таблицу необходимо преобразовать. Например, скопируйте нужный столбец и вставьте его справа от области поиска.
- Ошибка «Н / Д» может быть возвращена, если диапазон поиска не заблокирован при извлечении формулы ВПР.
- Если в аргументе Interval Lookup указан точный поиск (которому предшествует 0), возвращается ошибка N / A, если две таблицы не имеют точного совпадения.
- Для аргумента «Диапазон отображения» устанавливается ближайшее значение (число равно 1 или поле пустое), а диапазон поиска не сортируется. Для неточного поиска обязательно отсортируйте крайний левый столбец диапазона поиска.
- В сравниваемых данных есть лишние пробелы (для их удаления можно использовать функцию «ОБРЕЗАТЬ», применив ее к таблице и желаемому значению), другой формат, лишние кавычки. Для одинакового написания значений в обеих таблицах имеет смысл использовать выпадающий список.
- «ССЫЛКА» — эта ошибка часто возникает при неверном указании номера столбца, если такое количество столбцов не попадает в выбранный диапазон. В таких случаях нужно помнить, что число проставляется, считая от левого столбца выбранного диапазона, а не от таблицы в целом.
- «ИМЯ» — ошибка часто возвращается, если текст введен неверно в «Значение поиска». Текст должен быть написан в кавычках.
Знание того, как использовать ВПР в Excel, приходит, как и все остальное в этой программе, с практикой. Изучение одной теории вместо ясности рискует нанести ущерб пониманию конкретной операции в программе. При изучении любой функции Excel пользователю всегда эффективнее полагаться на конкретную, пусть и небольшую, пластину для образцов. Это позволяет глубже понять суть анализа, закрепить полученные знания. То же самое и с функцией ВПР в Excel. Практическое применение показывает, что это не так сложно, а полезно.
