Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически
Вопрос от пользователя
Здравствуйте!
У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1.
Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация).
Заранее благодарю. Андрей, Москва.
Д оброго дня всем!
То, что вы описали — относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.
Пример работы с функцией ВПР
В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена).
Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.
Две таблицы в Excel — сравниваем первые столбцы
Как это сделать.
Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )
где:
A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Какая должна быть формула
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Значение было найдено и подставлено автоматически
Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
Растягиваем формулу (копируем формулу в другие ячейки)
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Значения из одной таблицы подставлены в другую
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
Эксель почему одинаковый текст не равен
Добрый день!
В ячейках значения полностью одинаковые (они из разных систем) но формула считает, что они разные.
Естественно, если скопировать одно вместо другого — будет Ок. Но у меня много данных и ВПР не работает из-за этого.
Пробелов лишних нет. Формат ячеек одинаковый.
В чем может быть проблема?
Добрый день!
В ячейках значения полностью одинаковые (они из разных систем) но формула считает, что они разные.
Естественно, если скопировать одно вместо другого — будет Ок. Но у меня много данных и ВПР не работает из-за этого.
Пробелов лишних нет. Формат ячеек одинаковый.
В чем может быть проблема?
Сообщение Добрый день!
В ячейках значения полностью одинаковые (они из разных систем) но формула считает, что они разные.
Естественно, если скопировать одно вместо другого — будет Ок. Но у меня много данных и ВПР не работает из-за этого.
Пробелов лишних нет. Формат ячеек одинаковый.
В чем может быть проблема?
Спасибо! Автор — Jack-shade
Дата добавления — 18.08.2017 в 17:44
Почему Excel не сравнивает ячейки?
Сравниваю ячейки C и S, но Excel их не может сравнить. Почему?
И если, сделать преобразовать в число, то тогда сравнение отрабатывает верно. Но как у всех ячеек сделать это преобразование?
- Вопрос задан более трёх лет назад
- 593 просмотра
- Вконтакте
- Вконтакте
И если, сделать преобразовать в число, то тогда сравнение отрабатывает верно. Но как у всех ячеек сделать это преобразование?
Почему Excel обрабатывает эти две одинаково выглядящие ячейки как разные?
У меня необычная проблема, Excel обрабатывает ячейки, содержащие явно одинаковые значения, как будто они разные.
Чтобы повторить проблему, я приложил электронную таблицу, показывающую это поведение здесь.
Если вы не хотите его скачивать, то это выглядит так:
Excel обрабатывает ячейки А2 и В2 так, как будто они разные, хотя буква «G» в обоих случаях. Ячейка C2 проверяет это с =EXACT(A2,B2) .
Реальная проблема заключается в том, что когда я импортирую эти данные, скажем, в R, он обрабатывает их как разные и препятствует надлежащему анализу данных.
В этом случае я могу удалить эти ячейки, набрать «G», и теперь они будут одинаковыми, но у меня есть огромный набор данных, в котором многие идентичные символы рассматриваются как разные.
Что вызывает это? И есть ли быстрый способ автоматически исправить большую версию этого?
1 ответ 1
Подобные проблемы обычно возникают из-за проблемы, о которой @ScottCraner упоминал в комментариях. Причина этого заключается в следующем .
Используя сокращение таблицы, которое вы предоставили в своем вопросе, скажем .
- есть пробел после G в ячейке A2 , и
- в ячейке G после B2 нет пробела
Если это так, хотя визуально для нас они выглядят одинаково .
- ячейка A2 будет видна Excel как равная «G » , и
- ячейка B2 будет видна Excel как равная «G»
Следовательно, вы получите FALSE в ячейке C2
Вам потребуется обрезать и очистить все ячейки, содержащие конечные непечатаемые символы (удаление конечных и начальных пробелов, возврат каретки и т.д.), Чтобы обеспечить согласованность данных в ячейках.
Вы можете сделать это вручную, повторно введя значение, которое должно быть там, однако, если у вас много ячеек, которые могут иметь эту проблему, то создание макроса может быть полезным по причинам экономии времени.
VBA, чтобы сделать задачу.
Для большого количества кода вы можете использовать быстрый код, предложенный Гуру электронных таблиц, который предназначен для
обработка обрезки (удаление начальных и конечных пробелов) и очистка (удаление непечатаемых символов) для ваших данных Excel.