Сравнение таблиц Эксель ВПР: ошибка, расшифровка

Сравнение таблиц Эксель ВПР: ошибка, расшифровка

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

 

  1. Функция подбирает заданные параметры, используя критерий из первой таблицы, обращается ко второму диапазону, откуда будут «подтягиваться» значения.
  2. С самой верхней ячейки колонки критериев, просматривает все значения.
  3. Отыскав совпадения с заданными критериями, отсчитывает заданное число колонок вправо и попадает в ячейку, где расположено искомое значение, которое «затягивается» в ту ячейку, где указана формула.

 

вставить функцию впр

 

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

 

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

 

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

 

товар количество

 

Вторая - на цены:

 

товар цены

 

При совпадении товаров в обеих таблицах, путем использования комбинации клавиш Ctrl+C и Ctrl+V, показатели цен можно было бы легко подставить к количеству. Но в таблицах разная очередность позиций.

 

сравнение 2-х таблиц

 

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

 

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

 

подставление значений

 

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

 

 Порядок действий:

 

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

 

добавить столбец

 

Функцию ВПР вызывают через Мастера функций или прописывают вручную.

 

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

 

поиск функции впр

 

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

 

  1. Первая графа «Искомое значение» позволяет установить критерии для ячейки, где будет прописана формула. В приведенном примере ячейка содержит товар «А».
  2. Следующая строка «Таблица». При внесении диапазона данных, она позволит отыскать нужные значения. Для примера использовалась вторая таблица с ценами. Так как цены «подтягивают» к количеству. При этом важно учесть необходимость в содержании крайним левым (первым слева) столбцом выделяемого диапазона аналогичных критериев для поиска. В примере это колонка с названием товара. Потом таблица выделяется вправо до колонки, где содержатся искомые данные (цены). Можно продлить выделение вправо, но это ни на что не повлияет, так как колонка с искомыми показателями будет однозначно определена следующим параметром. Важно, чтобы выделенные таблицы начинались с колонки критерий и захватывали интересующий столбец с данными.
  3.  «Номер столбца» - числа, на которые колонки с искомыми данными (ценами) отстоят от колонки с критериями (названием товара). Отсчет начинается с самой колонки критериев. Если во второй таблице обе колонки расположены рядом, следует указать цифру 2 (первая – критерии, вторая - цены). Возможно размещение данных по отношению к критериям на 10 или 20 колонок. Это не имеет значения, Ексель произведет верные расчеты.
  4. Последняя графа «Интервальный просмотр», где указаны варианты поиска: точные (0) или приблизительные (1) совпадения критериев. Сейчас следует указать 0 (или ЛОЖЬ).

 

искомое значение

 

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

 

выделить область

 

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

 

Альтернативным вариантом выступает прописывание формулы ВПР в ячейке, прописав между параметрами знак «;».

 

прописать знак ;

 

Допускается прописывание наименования «впр» с помощью маленьких букв, реестр не имеет значения.

 

Как использовать специальную вставку?

 

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

 

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

 

значение

 

В ячейках останутся лишь значения, формула аннулируется.

 

Как быстро сравнить две таблицы с помощью ВПР?

 

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

 

материалы в таблице

 

Порядок действий:

 

В старом прайсе требуется создать колонку «Новые цены».

 

блок новая цена

 

Выделить первую ячейку и выбрать формулу ВПР. Задать аргументы (см. выше). Например:

 

задать аргументы

 

Указанная формула сообщает о необходимости взять название товара из диапазона А2:А15, осуществить его просмотр в «Новом прайсе», используя колонку А. Затем воспользоваться данными из второй колонки нового прайса (новыми ценами) и внести их в ячейку С2.

 

определилась новая цена

 

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

 

Формула ВПР в Ексель с рядом условий

 

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

поставщик материал цена

 

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

Основная сложность заключается в наличии нескольких названий товара от одного поставщика.

 

Пошаговая инструкция включает ряд действий:

 

Добавление в таблицу крайнего левого столбца (важно!), объединение «Поставщиков» и «Материалов».

 

первое действие

 

Объединение искомых критериев по аналогии:

 

объединение по аналогии

 

Размещение курсора в нужном месте и установка аргументов для формулы:

 

аргументы для формулы

 

Ексель осуществляет поиск нужной цены.

 

поиск нужной цены

 

Детальное рассмотрение формулы:

 

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

 

Формулы ВПР и выпадающие списки

 

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

 

Для создания раскрывающегося списка следует произвести ряд действий:

 

Поставить курсор в ячейку Е8, где планируется размещение списка.

Открыть закладку «Данные». Меню «Проверить данные».

 

проверка данных

 

Выбрать тип данных – «Списки». Источники – диапазон с названиями материалов.

 

параметры список источники

 

При нажатии кнопки ОК – будет создан раскрывающийся список.

 

раскроется список

 

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

 

Открыть «Мастер функций» и выбрать ВПР.

Первый аргумент – «Искомое значение» - ячейки с раскрывающимися списками. Таблица – диапазон с наименованием материала и цен. Колонка - 2. Функция будет отображаться в следующем формате:

 

отобразится формула

 

Остается нажать ВВОД и наслаждаться результатом.

 

получить результат

 

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

 

изменение цены

 

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

 

Особенности работы с формулой ВПР

 

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

 

  1. При использовании опции для ряда ячеек путем указания формулы в одной из них и копированием в остальные, важно контролировать относительность и абсолютность ссылок. В ВПР критерии (первые поля) должны иметь относительные ссылки (без $), что определяется наличием собственных критериев у каждой ячейки. Диапазоны должны иметь абсолютные ссылки (адреса диапазонов указывают через $). В противном случае при копировании формул диапазон «поплывет» вниз и многие показатели не отобразятся в поиске, так как искать будет негде.
  2. Номера столбцов, указываемые в третьем поле «Номер столбца» при использовании Мастера функций, должны отсчитываться с колонки критериев.
  3. При отсутствии критериев в таблицах, где осуществляется поиск данных, выпадает ошибка #Н/Д, вызывающая сложности при подсчете итогов (суммы, средней и др.). Для решения проблемы можно воспользоваться функцией СУММЕСЛИ (вместо ВПР) или ЕСЛИОШИБКА (поставить перед ВПР).
  4. При использовании числовых значений вместо критериев (кодов, артикул), то формула ВПР имеет повышенную чувствительность к форматам ячейки. При наличии в одной таблице критериев в числовом формате, а в другой в текстовом, то при полном совпадении показателей отобразится ошибка #Н/Д. Достаточно осуществить проверку совпадений формата полей с критериями и сделать их идентичными или воспользоваться функцией СУММЕСЛИ (для нее формат не имеет значения).
  5. Не рекомендуется использовать длинные критерии с целью уменьшения вероятности «случайных» различий. Например, наличие лишнего пробела между словами или одной неправильной буквы приведет к отсутствию сопоставимости одинаковых значений критериев. Артикулы или штрихкоды товаров годятся, но названия из нескольких слов не рекомендованы для критериев.
  6. Функции ВПР из таблицы с искомыми данными выдают первый сверху показатель. Если во второй таблице, откуда «подтягиваются» данные, имеется ряд ячеек с одинаковыми критериями, то в пределах выделенного диапазона ВПР захватывается первый сверху показатель. Это важно учитывать. Например, при необходимости к цене товаров подтянуть количество из другого диапазона, а там этот товар повторяется в нескольких графах, тогда к цене подтянутся первые сверху показатели (количество), другие останутся проигнорированными.
  7. Наличие последнего параметра в виде цифры 0 (нуль) обязательно. В противном случае формула криво работает.
  8. После применения функции ВПР, формулу рекомендуется сразу удалять, оставив лишь полученные результаты. Порядок действий требует выделения диапазона с полученными результатами, использования кнопки «скопировать» и установки значений на это место с помощью специальной вставки. При размещении таблиц в разных книгах Excel, более удобным вариантом станет разрыв внешних связей (оставив данные) с помощью инструмента, в разделе Данные → Изменить связи.

 

изменить связи

 

После вызова функции разрыва внешних связей отобразится диалоговое окно, где остается активировать кнопку «Разрыв связи» и «Закрыть».

 

разорвать свзь

 

Это способствует удалению сразу всех внешних ссылок.

  1. Альтернативным вариантом ВПР выступает опция ГПР. Разница состоит в просмотре списка данных по горизонтали.

 

Отзывы

 

Анна Будкова, 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: (не для публикации)

Ваш город:

Ваш отзыв:
Примечание: HTML разметка не поддерживается! Используйте обычный текст.

Оценка: Плохо Хорошо

Введите код, указанный на картинке: