Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Автор: Alexandr, 25.11.2017 Рубрики: Офис

nahodim-znacheniya-avtomaticheski

Вопрос от пользователя

Здравствуйте!

У меня есть одна задачка, и уже третий день ломаю голову - не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут - скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете (прим.: фотка вырезана цензурой, все-таки личная информация).

Заранее благодарю. Андрей, Москва.

 

Доброго дня всем!

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

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

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B - товар и цена) нет данных по столбцу B; во второй - заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая...

Две таблицы в Excel - сравниваем первые столбцы

Две таблицы в Excel - сравниваем первые столбцы

Как это сделать...

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

=ВПР(A2;$E$1:$F$7;2;ЛОЖЬ)

где:

A2 - значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 - полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" - он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 - номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца - то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ - ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара - просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

Растягиваем формулу (копируем формулу в другие ячейки)

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

Значения из одной таблицы подставлены в другую

Значения из одной таблицы подставлены в другую

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

На этом всё, удачи!

Полезный софт:

  • утилита для обновления драйверов
  • Driver Booster

  • Лучшее ПО для обновления драйверов (работает автоматически, поддерживет 99,99% оборудования).
    Весит <20 МБ!
  • утилита для оптимизации
  • Advanced System Care

  • Программа для очистки Windows от мусора (ускоряет систему, удаляет мусор, оптимизирует реестр).
    Весит <30 МБ!

6 комментариев

  1. Сергей:

    То что и искал! Большое спасибо!! ;-)

  2. денис:

    Здравствуйте, возникла похожая ситуация, задача в том, что: есть 2 таблицы (склад и потребности) надо чтобы из таблицы "потребностей" происходила сравнение с таблицей "склад" и сколько надо выдать по таблице "потребности" минусовала в "складе" количество сколько есть на складе.

    • Alexandr:

      Ну так подставьте денные и отнимете значение...

  3. Антон:

    Здравствуйте! Подскажите пожалуйста, если задача похожая, но искомые значения являются адреса клиентов и их надо сопоставить, по аналогии с задачей описанной выше?

    • Alexandr:

      День добрый. Если адреса задаются в одинаковом формате - то аналогично.

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

  4. Антон:

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

Написать комментарий

 

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

  • Интересное
  • Оптимизируем и ускоряем
  • Удаляем вирусы
  • Настраиваем звук
  •