Как связать ячейки на разных листах

ячейки Для поиска данных по строкам в выделенном диапазоне, их перенесения из одной таблицы в другую (размещенную в одном или разных листах, книгах Excel), содержащую однотипные показатели, служит функция ВПР в 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:$C; 3; 0) — если в A7 указан текстовый формат, а в таблице — числовые значения;

 

=ВПР(D7 & «»); Продукты!$A:$C; 3; 0) — в обратном порядке.

 

Для перевода текста в числовые значения предусмотрено несколько способов:

 

  • Двойного отрицания —D7.
  • Умножения на один - D71.
  • Суммирования с нулём 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:$C. Я рекомендую размещать справочный массив на отдельных листах рабочей книги. Он не будет путаться под руками, да и лучше сохранится. А еще лучше, объявить эту таблицу именованным диапазоном.

 

При указании массива многие пользуются конструкцией типа A:C, отображая столбцы полностью. Это позволяет предотвратить отслеживание того факта, что массив включает все необходимые строчки. При добавлении строк на лист с исходными данными, диапазон, заданный, как A:C, не потребует корректировки. Очень удобно, рекомендую.

 

Видео

 


Источник: http://sekrety-zhizni.ru/funkciya-vpr-v-excel-dlya-chajnikov-primery




Рекомендуем посмотреть ещё:


Закрыть ... [X]


Подстановка данных в excel функция ВПР Занимательные уроки Схема вязания джемпера для мальчика спицами

Как связать ячейки на разных листах Функция ВПР в Excel: для чайников примеры, формула - отзывы
Как связать ячейки на разных листах Трюк 13. Удаление несуществующих ссылок на книги Excel MS
Как связать ячейки на разных листах Панель инструментов ФОРМЫ
Как связать ячейки на разных листах Раздельные колонтитулы
Как связать ячейки на разных листах Cached
Как связать ячейки на разных листах ВЫКРОЙКА ПАЛЬТО С
Венецианская штукатурка видео Вязание спицами - Пупс Вязание спицами и крючком Из газетных трубочек - Плетение из газет Как сделать кораблик из бумаги? Пошаговая инструкция Квашеная капуста в домашних условиях Домашний способ Лечение прыщей. Прыщи на лице, лбу, подбородке, носу. Прыщи Натюрморты Схемы вышивки крестом, крестиком Вышивка Педикюр гель-лаком (фото)

ШОКИРУЮЩИЕ НОВОСТИ