Как купить билет на сайте РЖД (пошаговый пример оформления электронного билета без накруток и ...
Вопрос от пользователя
Здравствуйте!
У меня есть одна задачка, и уже третий день ломаю голову - не знаю, как ее выполнить. Есть 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-го столбца);
ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Какая должна быть формула
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Значение было найдено и подставлено автоматически
Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
Растягиваем формулу (копируем формулу в другие ячейки)
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Значения из одной таблицы подставлены в другую
Примечание: должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
*
Дополнения по теме приветствуются!
Ну а у меня на этом пока всё, удачи!
👋
Первая публикация: 25.11.2017
Корректировка: 23.11.2022
Другие записи:
То что и искал! Большое спасибо!!
Здравствуйте, возникла похожая ситуация, задача в том, что: есть 2 таблицы (склад и потребности) надо чтобы из таблицы "потребностей" происходила сравнение с таблицей "склад" и сколько надо выдать по таблице "потребности" минусовала в "складе" количество сколько есть на складе.
Ну так подставьте денные и отнимете значение...
Здравствуйте! Подскажите пожалуйста, если задача похожая, но искомые значения являются адреса клиентов и их надо сопоставить, по аналогии с задачей описанной выше?
День добрый. Если адреса задаются в одинаковом формате - то аналогично.
Но все же, обычно, с адресами немного сложнее, т.к. при заполнении часто многое зависит от человека: то одно слово сократят, то в другой последовательности и т.д. и т.п. Если это так, то при работе с большими таблицами - лучше прогнать это дело через скрипт (для приведения к единому стандарту), и потом уже все остальное (но это уже сложнее, и в одной статье вряд ли опишешь...).
Да именно такая задача и стоит, нужно сопоставить две базы разного периода времени, и посмотреть кто перестал пользоваться а кто остался
Как сделать чтобы при в 1 условий выводила 2 данных. Например выбирая яблоко , должен выйти цена и количества.
Просто в еще одном столбце поставьте аналогичную формулу
Спасибо! Столько уже статей пересмотрел и нигде толкового ответа, как сопоставить таблицы в Excel, не встретил. Спасибо, что у Вас есть!
Огромное спасибо! Но если количество строк в первой таблице, меньше количества строк второй таблицы и необходимо чтобы значения подставились только в совпадающие ячейки?
А какая разница сколько строк? Функция ВПР просто ищет совпадение в оговоренном интервале, если находит - подставляет...
Добрый день! А если искомое значение состоит из 2 столбцов (т.е. номенклатура состоит из: в первом столбце название, а во втором характеристика) и необходимо сравнить 2 столбца одной таблицы и другой! В первой таблице указана количество, а во второй таблице цену нужно найти и подтянуть!
Добрый.
Слишком мало данных, непонятно что вы искать хотите. Характеристики и номенклатура в ячейках, которые нужно искать, одинаковые?
Здравствуйте!
Подскажите, пожалуйста, есть ли механизм сопоставления двух таблиц, по трем условиям (например: дата, наименование, поставщик), одна таблица - планируемые показатели, вторая (меньшее кол-во строк, гораздо...), фактические. Необходимо подтянуть фактические данные на строку с планируемыми.
Спасибо автору! Так просто о сложном, и все понятно! А то как начнешь читать разъяснения по применению формул - ум за разум заходит.
Это круто, это очень круто.
Весь день потратил вручную вбивав в таблицу информацию,
но не успел сегодня закончить. Придётся завтра выходить (воскресенье), отчаялся...
Днём пытался найти инструкцию, не помогло.
Вечером, перекусив, отдохнув, и... о чудо, нашёл Вашу тему...
Попробовал, шикарно.
И даже уже ответ на второй вопрос увидел: "если надо два значения" ))
Спасибо!!!
Добрый день. Подскажите, а если два условия в таблице. То есть не просто колбаса, а еще есть потребители колбасы, например, Сергей и Маша. Как сделать так, чтобы из другой таблицы искалась не только колбаса, но именно колбаса у Маши?
Добрый день.
Самый простой вариант: создать доп. вспомогательный столбец, в него сцепить 2 столбика (знак сцепления: A1&B1), и сравнить таблицы по этому вспомогательному столбцу... Скрин ниже
Здравствуйте! А как сделать то же самое с применением надстройки Power Query? Не могу нигде найти. Просто большой объем и ВПР сильно тормозит от этого.
Спасибо вам за статью. Вот только не могу подстроить под себя. У меня таблица 1 ( с заполненной стоимостью) на 1 листе. Таблица 2(таблицы) Это несколько спецификаций. т.е. у меня яблоко, попадается несколько раз.
Задача найти все совпадения по столбцу "Товар" и подставить везде цену.
Круто! Доходчиво! Я примерно раз в пол года сталкиваюсь с наложением таблиц и переносом данных, постоянно в этот период зависая в поиске решения. Ресурсов где есть выход - много. Но чтобы найти и сделать быстро - всегда нужно потратить много времени. Добавлю в закладки. Спасибо!
Здравствуйте. При растягивании формулы вниз у меня получается вот такое #Н/Д, т.к. ведь и смещается вниз диапазон таблицы. Т.е если мы будем на строке А3,то искать будет значения уже не в диапазоне Е1:F7, а в диапазоне E3:F9.
Здравствуйте.
У вас нет значка "$", который делает постоянным диапазон и запрещает его изменять...
АВТОРУ огромное Спасибо. Доступно и понятно. Главное работает.
У меня такой вопрос: имеется одна общая таблица (много строк, стобцов 8) первый столбец "Заказчик". Заказчики закреплены за несколькими менеджерами.В таблице этой информации нет, менеджер сам знает "своих" заказчиков. Таблица отправлена всем. Каждый менеджер, заносит данные по своим заказчикам. В результате нужно снова собрать одну таблицу из нескольких заполненных.
Можно, как вариант, формулой проверить пусто ли в ячейке и применить ВПР, но может есть способ проще?
Спасибо.
Приветствую.
Сори, но слишком сложно для восприятия "на слух" без визуального осмотра
Добрый день!
Спасибо большое, все очень понятно, но... все сделала как Вы написали. Задача аналогичная. Две таблицы, два столбца, надо сравнить и при совпадении перенести данные. Но у меня выдает в ячейке, куда должны переносится данные - #NAME? в чем может быть ошибка? У меня Excel на англ. языке, может и формулу надо писать на англ языке? Например СУММ на англ SUM, может и ВПР надо на англ., а как?
Добрый.
Не подскажу, с англ. версиями Excel не работаю уже больше 10 лет... (да и раньше-то... не так чтобы плотно).
Что касается "NAME" - возникает, когда Excel не может распознать текст в формуле (например, из-за опечатки).
То что надо! Огромное спасибо!
Спасибо, очень понятное объяснение.
Может кому пригодится. Надстройка к Excel lk сравнения списков/прайсов http://joiner34.ru/programmy/nadstrojka-prajs
Как быть, если в таблице, в которой происходит поиск, несколько значений и все их необходимо вставить в свою ячейку?
Можно предварительно объединить нужные ячейки (т.е. создать технический столбец), а потом этот столбик перенести (например).
Дело в том что каждое значение из таблицы в которой происходит поиск необходимо вставить в соответствующую ячейку. ВПР вставляет только первое значение остальные не хочет.
Здравствуйте! Спасибо за инструкцию. Все понятно. На цифрах работает. Спасибо!
Но, в моем случае необходимо из одной таблицы с артикулами найти подходящее название и перенести в другую таблицу. Думала, что не важно цифры, либо буквы в значении, но видимо важно. В ячейке появляется #Н/Д. Подскажите, как можно решить мой вопрос?
Здравствуйте.
Так сравниваются то как раз ячейки ("Колбаса", "Рис" и пр.). Цифры просто копируются из столбика (там вместо них могут быть и др. значения).
Спасибо, Вы просто Самый Полезный Человек в сети! Я весь день голову ломала, столько сложностей перечитала и перепробовала, а всё так просто
А если такая задача: две таблицы. В одной столбец с уникальными значениями, например: вишня, груша, яблоко.
А во второй, таких значений ячеек несколько, например: вишня, груша, вишня, яблоко, вишня. Во втором столбце какое то слово: x, y, z, t.
Задача: как мне в первой таблице получить в ячейку склейку значений второго столба соответственно?
Доброго времени.
Для начала бы я склеил во втором столбце все строки, где повторяются значения (вишня, груша, вишня, яблоко, вишня). Например, через функцию "Сцепить".
Затем, всё аналогично, как в статье.
Не очень понял - может я криво объяснил - вот картинка с пояснением
Я имел ввиду сначала сделать так (либо я не понял до конца вашу задачу
)
Добрый день!
Не получается применить формулу. Как видно из прилагаемого изображения есть 2 ошибки #Н/Д
и #ССЫЛКА!
Подскажите с чем связана проблема?
Добрый вечер! Все работает, спасибо.
Формулу вашу скопировала.
Там , где находит соответствие - все хорошо.
А вот в ячейке, где нет такого значения во второй таблице, в первой таблице в ячейке появляется #Н/Д. Это, конечно можно исправить, но почему не пробел? Может в формуле что-то не так. Спасибо
Добрый день.
Подскажите, а можно применить функцию ВПР к двум таблица из двух листов одного файла? (исходная таблица находится на листе-1, таблица, с которой сравниваем - на листе-2 этого же файла).
Добрый.
Почему нет?
Если совсем не получается - просто скопируйте таблицу с листа-2 на лист-1...
Приветствую! Памятник тебе при жизни надо поставить!
Сделал сервис, который пишет формулы ВПР сам. Для частых операций - проще и быстрее, чем каждый раз руками всё делать http://excelmagic.ru
Добрый день, а если в одной критерии повторяются, то возможно ли вывести их в другую таблицу как-то?
Например:
яблоки 100
яблоки 105
груши 103
арбузы 50
...
яблоки 200
И в другой таблице вывести все строки, где яблоки.
Добрый.
Не подскажу...
Подскажите пожалуйста, сделал все по инструкции, но все равно не работает... везде вылетаем #Н/Д
Здравствуйте, как сделать чтобы копировалось текстовая информация. Копируется только цифры, цена
Здравствуйте. Копируется то, что есть в ячейке
Здравствуйте! Как количество раскидать по ячейкам размеров!
Спасибо
Здравствуйте, помогите составить формулу
Задача:
1. на столбике "D" около 250 номерованных товарных артикулов и на столбике "E" их килограммы указаны.
2. надо находить из столбца "D" 2 или больше одинаковых артикулов и сложить их килограммы, и вставить в другую ячейку новой книги excel.
Заранее спасибо.
Здравствуйте.
На "слух" (по описанию) такое тяжело воспринимается. Если бы сделали скрин исходных данных и показали на одном значение что надо - может кто и подсказал бы.
Здравствуете,
как просили скриншот сделал с примечаниями:
Разными способами можно решить (если я правильно понял).
Например, сначала скопировать столбец с артикулами в другой столбец и удалить из него дубликаты. Затем воспользоваться формулой =суммесли(), и получить общий вес по каждому артикулу. Пару скринов ниже.
1)
2)
Здравствуйте. Кто может мне помочь: сделал всё как в описании, формула работает до середины, а дальше выводит н/д, фото прилагаю.