Сравнение таблиц Эксель ВПР: ошибка, расшифровка
Примеры использования формулы Excel с функцией ВПР позволяют узнать ее принцип действия и избежать возникновения ошибок:
- Функция подбирает заданные параметры, используя критерий из первой таблицы, обращается ко второму диапазону, откуда будут «подтягиваться» значения.
- С самой верхней ячейки колонки критериев, просматривает все значения.
- Отыскав совпадения с заданными критериями, отсчитывает заданное число колонок вправо и попадает в ячейку, где расположено искомое значение, которое «затягивается» в ту ячейку, где указана формула.
Пример применения функции ВПР
В приведенном примере требуется узнать стоимость проданного товара. Для ее расчета следует найти произведение количества и цен (колонки с данными размещены в соседних столбцах). В пустой колонке рядом прописывают формулу произведения двух ячеек и протягивают вниз до конца списка товаров.
Исходная информация может содержаться в разных диапазонах и другом порядке. Первая таблица указывает на количество реализованного товара:
Вторая - на цены:
При совпадении товаров в обеих таблицах, путем использования комбинации клавиш Ctrl+C и Ctrl+V, показатели цен можно было бы легко подставить к количеству. Но в таблицах разная очередность позиций.
Информация по многим товарам не соответствует расположенным рядом показателям. В данном случае исключена возможность прописывания формулы умножения и «протягивания» вниз для всех позиций.
Как подставить цены из второй таблицы к соответствующим показателям количества из первой, то есть цену позиции А к количеству позиции А, цену Б к количеству Б.
При использовании функции ВПР, цены из второй таблицы «подтягивают» к количеству из первой таблицы, согласно названиям товара.
Порядок действий:
В первой таблице требуется добавить новый столбец, где будут проставлены цены с помощью формулы ВПР.
Функцию ВПР вызывают через Мастера функций или прописывают вручную.
Вызов опции через Мастера заключается в активации ячейки, где будет указана формула, и нажимают на кнопку f(x) в начале строки формул. В появившемся диалоговом окне Мастера из предложенного перечня требуется указать ВПР.
Формула ВПР в Excel для чайников требует правильности заполнения полей в диалоговом окне Мастера функций:
- Первая графа «Искомое значение» позволяет установить критерии для ячейки, где будет прописана формула. В приведенном примере ячейка содержит товар «А».
- Следующая строка «Таблица». При внесении диапазона данных, она позволит отыскать нужные значения. Для примера использовалась вторая таблица с ценами. Так как цены «подтягивают» к количеству. При этом важно учесть необходимость в содержании крайним левым (первым слева) столбцом выделяемого диапазона аналогичных критериев для поиска. В примере это колонка с названием товара. Потом таблица выделяется вправо до колонки, где содержатся искомые данные (цены). Можно продлить выделение вправо, но это ни на что не повлияет, так как колонка с искомыми показателями будет однозначно определена следующим параметром. Важно, чтобы выделенные таблицы начинались с колонки критерий и захватывали интересующий столбец с данными.
- «Номер столбца» - числа, на которые колонки с искомыми данными (ценами) отстоят от колонки с критериями (названием товара). Отсчет начинается с самой колонки критериев. Если во второй таблице обе колонки расположены рядом, следует указать цифру 2 (первая – критерии, вторая - цены). Возможно размещение данных по отношению к критериям на 10 или 20 колонок. Это не имеет значения, Ексель произведет верные расчеты.
- Последняя графа «Интервальный просмотр», где указаны варианты поиска: точные (0) или приблизительные (1) совпадения критериев. Сейчас следует указать 0 (или ЛОЖЬ).
Остается кликнуть по кнопке ОК или «Ввод». При правильном внесении данных и наличии критерия в обеих таблицах, на месте указанной формулы отобразится некоторый показатель. Достаточно протянуть (или скопировать) формулу вниз до последней строчки таблицы.
Дальнейший расчет производится путем определения произведения количества и цены, протягивания формулы вниз до конца таблицы, так как пары цена-количество уже совпадают.
Альтернативным вариантом выступает прописывание формулы ВПР в ячейке, прописав между параметрами знак «;».
Допускается прописывание наименования «впр» с помощью маленьких букв, реестр не имеет значения.
Как использовать специальную вставку?
В результате использования функции ВПР остаются связанными две таблицы. При внесении изменений в прайс, поменяется и стоимость поступившего на склад товара. Во избежание подобной ситуации следует воспользоваться «Специальной вставкой».
- Выделить столбец с указанными ценами.
- Правой кнопкой мыши – «Скопировать».
- Не убирая выделений, правой кнопкой мышки – «Специальные вставки».
- Установить галку против «Значения». ОК.
В ячейках останутся лишь значения, формула аннулируется.
Как быстро сравнить две таблицы с помощью ВПР?
Функция используется для сопоставления значений в больших таблицах. Например, при наличии изменений в прайсе. Требуется сравнить старые цены с новыми.
Порядок действий:
В старом прайсе требуется создать колонку «Новые цены».
Выделить первую ячейку и выбрать формулу ВПР. Задать аргументы (см. выше). Например:
Указанная формула сообщает о необходимости взять название товара из диапазона А2:А15, осуществить его просмотр в «Новом прайсе», используя колонку А. Затем воспользоваться данными из второй колонки нового прайса (новыми ценами) и внести их в ячейку С2.
Выбор способа отображения данных позволяет их сопоставлять, определять разницу в числах и процентах.
Формула ВПР в Ексель с рядом условий
Выше были рассмотрены примеры анализа с одним условием – названием товара. На практике может появиться необходимость в сравнении нескольких диапазонов с данными и выборе значений по 2, 3-м критериям. Ниже приведена таблица для наглядного обзора:
Для поиска цены, по которой привезен гофрокартон от ОАО «Восток», следует создать 2 условия: по названию материала и по поставщикам.
Основная сложность заключается в наличии нескольких названий товара от одного поставщика.
Пошаговая инструкция включает ряд действий:
Добавление в таблицу крайнего левого столбца (важно!), объединение «Поставщиков» и «Материалов».
Объединение искомых критериев по аналогии:
Размещение курсора в нужном месте и установка аргументов для формулы:
Ексель осуществляет поиск нужной цены.
Детальное рассмотрение формулы:
- Что искать.
- Где искать.
- Какие данные брать.
Формулы ВПР и выпадающие списки
Пусть, определенные данные внесены в виде выпадающего списка. В приведенном примере ими являются «Материалы». Важно произвести настройку функции так, чтобы при выборе названия отображалась цена.
Для создания раскрывающегося списка следует произвести ряд действий:
Поставить курсор в ячейку Е8, где планируется размещение списка.
Открыть закладку «Данные». Меню «Проверить данные».
Выбрать тип данных – «Списки». Источники – диапазон с названиями материалов.
При нажатии кнопки ОК – будет создан раскрывающийся список.
Остается задать функцию, позволяющую при выборе материалов, отображать в графе цена соответствующие значения. Следует установить курсор в ячейке Е9 (где будет отображаться цена).
Открыть «Мастер функций» и выбрать ВПР.
Первый аргумент – «Искомое значение» - ячейки с раскрывающимися списками. Таблица – диапазон с наименованием материала и цен. Колонка - 2. Функция будет отображаться в следующем формате:
Остается нажать ВВОД и наслаждаться результатом.
При смене материала – изменяется цена:
Так функционирует выпадающий список в Excel с функцией ВПР. Все осуществляется в авторежиме, в считанные секунды.
Особенности работы с формулой ВПР
Перед тем, как пользоваться функцией ВПР в Excel, следует ознакомиться с ее особенностями:
- При использовании опции для ряда ячеек путем указания формулы в одной из них и копированием в остальные, важно контролировать относительность и абсолютность ссылок. В ВПР критерии (первые поля) должны иметь относительные ссылки (без $), что определяется наличием собственных критериев у каждой ячейки. Диапазоны должны иметь абсолютные ссылки (адреса диапазонов указывают через $). В противном случае при копировании формул диапазон «поплывет» вниз и многие показатели не отобразятся в поиске, так как искать будет негде.
- Номера столбцов, указываемые в третьем поле «Номер столбца» при использовании Мастера функций, должны отсчитываться с колонки критериев.
- При отсутствии критериев в таблицах, где осуществляется поиск данных, выпадает ошибка #Н/Д, вызывающая сложности при подсчете итогов (суммы, средней и др.). Для решения проблемы можно воспользоваться функцией СУММЕСЛИ (вместо ВПР) или ЕСЛИОШИБКА (поставить перед ВПР).
- При использовании числовых значений вместо критериев (кодов, артикул), то формула ВПР имеет повышенную чувствительность к форматам ячейки. При наличии в одной таблице критериев в числовом формате, а в другой в текстовом, то при полном совпадении показателей отобразится ошибка #Н/Д. Достаточно осуществить проверку совпадений формата полей с критериями и сделать их идентичными или воспользоваться функцией СУММЕСЛИ (для нее формат не имеет значения).
- Не рекомендуется использовать длинные критерии с целью уменьшения вероятности «случайных» различий. Например, наличие лишнего пробела между словами или одной неправильной буквы приведет к отсутствию сопоставимости одинаковых значений критериев. Артикулы или штрихкоды товаров годятся, но названия из нескольких слов не рекомендованы для критериев.
- Функции ВПР из таблицы с искомыми данными выдают первый сверху показатель. Если во второй таблице, откуда «подтягиваются» данные, имеется ряд ячеек с одинаковыми критериями, то в пределах выделенного диапазона ВПР захватывается первый сверху показатель. Это важно учитывать. Например, при необходимости к цене товаров подтянуть количество из другого диапазона, а там этот товар повторяется в нескольких графах, тогда к цене подтянутся первые сверху показатели (количество), другие останутся проигнорированными.
- Наличие последнего параметра в виде цифры 0 (нуль) обязательно. В противном случае формула криво работает.
- После применения функции ВПР, формулу рекомендуется сразу удалять, оставив лишь полученные результаты. Порядок действий требует выделения диапазона с полученными результатами, использования кнопки «скопировать» и установки значений на это место с помощью специальной вставки. При размещении таблиц в разных книгах Excel, более удобным вариантом станет разрыв внешних связей (оставив данные) с помощью инструмента, в разделе Данные → Изменить связи.
После вызова функции разрыва внешних связей отобразится диалоговое окно, где остается активировать кнопку «Разрыв связи» и «Закрыть».
Это способствует удалению сразу всех внешних ссылок.
- Альтернативным вариантом ВПР выступает опция ГПР. Разница состоит в просмотре списка данных по горизонтали.
Отзывы
Анна Будкова, 43 года, Новосибирск
Давно работаю с таблицами Ексель и для решения проблемы с засорением ячеек лишними пробелами, использую функцию очистки СЖПРОБЕЛЫ (TRIM).
При возникновении разного формата данных, рекомендую обратить внимание:
Если первый параметр формулы ВПР ссылается на ячейку с цифрами, которая отображает их в виде текста, а первый столбец массива включает цифры в правильном формате, поиск завершится неудачно. Бывает и наоборот. Проблему легко решить путем перевода параметра 1 в правильный формат:
=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если в A7 указан текстовый формат, а в таблице — числовые значения;
=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — в обратном порядке.
Для перевода текста в числовые значения предусмотрено несколько способов:
- Двойного отрицания —D7.
- Умножения на один - D7*1.
- Суммирования с нулём D7+0.
- Возведения в 1-ю степень D7^1.
Для перевода числового формата в текстовый требуется сцепка с пустой строчкой, заставляющей Ексель преобразовать типы данных.
Инна Кремпович, 31 год, Сочи
Когда мне нужно отыскать данные по строкам в таблице или диапазоне (к примеру, найти цену товара по его номеру), я обращаюсь к формулам ВПР, формулы всегда вбиваю вручную.
Для построения синтаксиса функции ВПР, рекомендую подготовить следующую информацию:
- Значений для поиска.
- Диапазона, где указан искомый показатель (в первой колонке таблицы). Если искомый параметр имеется в ячейке C2, таблица должна начинаться с C.
- Номер колонки в таблице, где имеется «подтягиваемое» значение. Так, если диапазон задан B2:D11, я считаю B первой колонкой, C — второй.
- Для получения приблизительного совпадения можно указать слово ИСТИНА, для точного - ЛОЖЬ. Если я ничего не указываю, по умолчанию подбирается вариант ИСТИНА.
Остается объединить все перечисленные выше аргументы:
=ВПР(искомый показатель; диапазон с искомыми данными; номер колонки в таблице с «подтягиваемыми данными; ИСТИНА/ЛОЖЬ).
Привожу ниже пример настройки функции ВПР для получения цены тормозных дисков, равной 85,73.
- Ячейка D13 включает искомое_значение.
- Ячейки B2:E11 (с желтой заливкой) выступают в качестве таблицы или диапазона, содержащего искомое значение.
- 3 — номер_столбца в диапазоне, содержащем «подтягиваемое» значение. Третий столбец указывает на цены деталей, поэтому результат формулы укажет на значения из этой колонки.
- Аргумент интервальный_просмотр (ЛОЖЬ) позволяет получить точные совпадения.
Результат формулы ВПР отображает цену тормозных дисков, равную 85,73.
Анна Будкова, 25 лет, Новосибирск
Недавно стала использовать на практике таблицы ВПР и уже успела столкнуться с рядом неудобств, относительно осуществления поиска данных лишь в первой колонке диапазона, и только с правой стороны. А на практике столбцы, содержащие заданные показатели, нередко оказываются слева от колонки, где осуществляется поиск. Этих недостатков лишена связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), используемая в качестве гибкого решения по извлечению информации из таблиц в сравнении с ВПР.
Если диапазон, в который я извлекаю данные с помощью ВПР, имеет структуру, аналогичную справочной таблице, но с меньшим количеством строк, то очень удобно применять в ВПР команду СТОЛБЕЦ() для авторасчёта номеров извлекаемых колонок. ВПР-формулы будут иметь одинаковые значения (с поправками на первый показатель с автоматическим изменением). Важно помнить, что у первого параметра координата колонки остается абсолютной.
Ольга Новикова, 32 года, СПб
Из практики знаю, что многие часто забывают сделать ссылку массива абсолютной, что приводит к его «плывучести» при протягивании. Например, вместо A3:C4 важно указывать $A$3:$C$4. Я рекомендую размещать справочный массив на отдельных листах рабочей книги. Он не будет путаться под руками, да и лучше сохранится. А еще лучше, объявить эту таблицу именованным диапазоном.
При указании массива многие пользуются конструкцией типа A:C, отображая столбцы полностью. Это позволяет предотвратить отслеживание того факта, что массив включает все необходимые строчки. При добавлении строк на лист с исходными данными, диапазон, заданный, как A:C, не потребует корректировки. Очень удобно, рекомендую.
Видео
Написать отзыв
Ваше имя:Ваш EMail: (не для публикации)
Ваш город:
Ваш отзыв:
Оценка: Плохо Хорошо
Введите код, указанный на картинке: