Как сравнить несколько таблиц в excel. Как сравнить два столбца в Excel на совпадения. Как сравнить файлы Excel
Вопрос от пользователя
Здравствуйте!
У меня есть одна задачка, и уже третий день ломаю голову - не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут - скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете (прим. : фотка вырезана цензурой, все-таки личная информация) .
Заранее благодарю. Андрей, Москва.
Доброго дня всем!
То, что вы описали - относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже...
Пример работы с функцией ВПР
В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B - товар и цена) нет данных по столбцу B ; во второй - заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая...
Как это сделать...
Ставим указатель мышки в ячейку B2 - то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
=ВПР(A2 ;$E$1:$F$7 ;2 ;ЛОЖЬ )
A2 - значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 - полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" - он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 - номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца - то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ - ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Чтобы цена была проставлена и для других наименований товара - просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали - будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
На этом всё, удачи!
После установки надстройки у вас появится новая вкладка с командой вызова функции. При нажатии на команду Сравнение диапазонов появляется диалоговое окно для ввода параметров.
Данный макрос позволяет сравнить таблицы любого объема и с любым количеством столбцов. Сравнение таблиц можно проводить по одному, двум или трем столбцам одновременно.
Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.
Чтобы сравнить таблицы необходимо выполнить следующие действия:
- Указать диапазоны таблиц.
- Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
- Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
- Указать тип сравнения.
- Выбрать вариант выдачи результатов.
Тип сравнения таблиц
Программа позволяет выбрать несколько типов сравнения таблиц:
Найти строки одной таблицы, которые отсутствуют в другой таблице
При выборе данного типа сравнения программа ищет строки одной таблицы, которые отсутствуют в другой. Если вы сопоставляете таблицы по нескольким столбцам, то результатом работы будут строки, в которых имеется различие хотя бы по одному из столбцов.
Найти совпадающие строки
При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.
Пример работы программы в данном режиме представлен справа на картинке.
Сопоставить таблицы на основе выбранной
В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.
Сравнение таблиц по четырем и более столбцам
Если вам не хватает функционала программы и необходимо сопоставить таблицы по четырем или более столбцам, то вы можете выйти из положения следующим образом:
- Создайте в таблицах по пустому столбцу.
- В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.
Таким образом, вы получите 1 столбец, содержащий значения нескольких столбцов. Ну а как сопоставить один столбец вы уже знаете.
Иногда нужно просмотреть в таблице Access только те записи, которым соответствуют записи из другой таблицы, содержащие поля с совпадающими данными. Например, вам может быть нужно просмотреть записи сотрудников, обработавших хотя бы один заказ, чтобы определить, кто из них заслуживает поощрения. Или же вам может потребоваться просмотреть контактные данные клиентов, проживающих в одном городе с сотрудником, чтобы организовать личную встречу.
Если вам нужно сравнить две таблицы Access и найти совпадающие данные, возможны два варианта действий.
Создайте запрос, объединяющий поля из каждой таблицы, которые содержат подходящие данные, используя для этого существующую связь или объединение, созданное для запроса. Этот способ оптимален по скорости возврата результатов запроса, но не позволяет объединять поля с данными разного типа.
Создать запрос для сравнения полей, в котором одно поле используется в качестве условия для другого. Этот способ обычно требует больше времени, поскольку при объединении строки исключаются из результатов запроса до чтения базовых таблиц, тогда как условия применяются к результатам запроса после чтения этих таблиц. Зато поле можно использовать в качестве условия для сравнения полей с данными разного типа, чего нельзя сделать при использовании объединений.
В этой статье рассматривается сравнение двух таблиц для выявления совпадающих данных и приводится образец данных, которые можно использовать в примерах процедур.
В этой статье
Сравнение двух таблиц с помощью объединений
Для сравнения двух таблиц с помощью объединений нужно создать запрос на выборку, включающий обе таблицы. Если между таблицами еще нет связи по полям, содержащим нужные данные, вам нужно создать объединения по ним. Объединений можно создать сколько угодно, но каждая пара объединенных полей должна содержать данные одного и того же или совместимого типа.
Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблица, содержащая данные о профилирующих предметах, и таблица, содержащая данные о студентах, которые их изучают. Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах - в таблице "Специализации". Чтобы увидеть, как после недавних изменений в учебном плане изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации".
Подготовка примера данных
В данном примере вы создаете запрос, который определяет, как недавние изменения в учебном плане по математике повлияли на оценки студентов с соответствующим профилирующим предметом. Используйте две приведенные ниже таблицы: "Специализации" и "Учащиеся". Добавьте их в базу данных.
Access предоставляет несколько способов добавления этих таблиц образец базы данных. Можно ввести данные вручную, скопируйте каждую таблицу в электронную таблицу программы (например, Microsoft Office Excel 2007) и затем импортируйте листы в Access или можно вставлять данные в текстовом редакторе, например Блокнот и затем импортировать данные из результирующего текстовые файлы.
В пошаговых инструкциях этого раздела объясняется, как вводить данные вручную на пустой лист, а также как копировать примеры таблиц в Excel и затем импортировать их в Access.
Специализации
Код учащегося | Специализация |
|
|---|---|---|
Учащиеся
Код учащегося | Семестр | Учебный план | Номер предмета | Оценка |
|
|---|---|---|---|---|---|
Если вы собираетесь вводить пример данных в электронной таблице, можете .
Ввод примеров данных вручную
Если вас не интересует создание листа на основе примера данных, пропустите следующий раздел ("Создание листов с примерами данных").
Создание листов с примерами данных
Создание таблиц базы данных на основе листов
Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений
Теперь все готово для сравнения таблиц "Учащиеся" и "Специализации". Так как связи между двумя таблицами не определены, вам необходимо создать объединения соответствующих полей в запросе. Таблицы содержат по несколько полей, и вам потребуется создать объединение для каждой пары общих полей: "Код учащегося", "Год", а также "Учебный план" (в таблице "Учащиеся") и "Специализация" (в таблице "Специализации"). В данном случае нас интересует только математика, поэтому можно ограничить результаты запроса с помощью условия поля.
На вкладке Создание нажмите кнопку Конструктор запросов .
В диалоговом окне Добавление таблицы дважды щелкните таблицу, которая содержит нужные записи (Учащиеся ), а затем дважды щелкните таблицу, с которой ее сравниваете (Специализации ).
Закройте диалоговое окно Добавление таблицы .
Перетащите поле Код учащегося из таблицы Учащиеся в поле Код учащегося таблицы Специализации . В бланке запроса между двумя таблицами появится линия, которая показывает, что создано объединение. Дважды щелкните линию, чтобы открыть диалоговое окно Параметры объединения .
Обратите внимание на три варианта в диалоговом окне Параметры объединения . По умолчанию выбран вариант 1. В некоторых случаях требуется добавить в параметры объединения дополнительные строки из одной таблицы. Так как вам нужно найти только совпадающие данные, оставьте для объединения значение 1. Закройте диалоговое окно Параметры объединения , нажав кнопку Отмена .
Вам нужно создать еще два объединения. Для этого перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации .
В таблице Учащиеся дважды щелкните звездочку (* ), чтобы добавить все поля таблицы в бланк запроса.
Примечание: Учащиеся.* .
В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.
Показать столбца Специализация .
В строке Условие отбора столбца Специализация введите МАТЕМ .
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .
Сравнение двух таблиц с использованием поля в качестве условия
Иногда необходимо сравнить таблицы на основе полей с совпадающими значениями, но разными типами данных. Например, поле в одной таблице может иметь числовой тип данных, а сравнить его нужно с полем из другой таблицы, которое имеет текстовый тип данных. Поля, содержащие аналогичные данные разного типа, могут появиться при сохранении чисел в качестве текста (например, при импорте данных из другой программы). Так как создать объединения полей с данными разного типа невозможно, вам потребуется использовать другой способ. Для сравнения двух полей с данными разного типа можно использовать одно поле в качестве условия для другого.
Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблицы "Специализации" и "Учащиеся". Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах - в таблице "Специализации". Чтобы увидеть, как изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации". Однако у одного из полей, которые вы хотите использовать для сравнения таблиц, тип данных не такой, как у поля, с которым оно сопоставляется.
Для сравнения двух таблиц с использованием поля в качестве условия нужно создать запрос на выборку, включающий обе таблицы. Включите в запрос поля, которые хотите отобразить, а также поле, соответствующее тому полю, которое будет использоваться в качестве условия. Затем создайте условие для сравнения таблиц. Вы можете создать столько условий, сколько потребуется для сравнения полей.
Чтобы проиллюстрировать этот способ, мы используем , но в поле "Код учащегося" таблицы "Специализации" изменим числовой тип данных на текстовый. Так как нельзя создать объединение двух полей с разными типами данных, нам придется сравнить два поля "Код учащегося", используя одно поле в качестве условия для другого.
Изменение типа данных в поле "Код учащегося" таблицы "Специализации"
Откройте базу данных, в которой вы сохранили примеры таблиц.
В области навигации щелкните таблицу "Специализации" правой кнопкой мыши и выберите пункт Конструктор .
Таблица "Специализации" откроется в режиме конструктора.
В столбце Тип данных измените для поля Код учащегося тип данных Число на Текст .
Закройте таблицу "Специализации". Нажмите кнопку Да , когда вам будет предложено сохранить изменения.
Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля
Ниже показано, как сравнить два поля "Код учащегося", используя поле из таблицы "Учащиеся" в качестве условия для поля из таблицы "Специализации". С помощью ключевого слова Like вы можете сравнить два поля, даже если они содержат данные разного типа.
На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов .
В диалоговом окне Добавление таблицы дважды щелкните таблицу Учащиеся , а затем таблицу Специализации .
Закройте диалоговое окно Добавление таблицы .
Перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации . Эти поля содержат данные одного типа, поэтому для их сравнения можно использовать объединения. Для сравнения полей с данными одного типа рекомендуется использовать объединения.
Дважды щелкните звездочку (* ) в таблице Учащиеся , чтобы добавить все поля таблицы в бланк запроса.
Примечание: При использовании звездочки для добавления всех полей в бланке отображается только один столбец. Имя этого столбца включает имя таблицы, за которым следуют точка (.) и звездочка (*). В этом примере столбец получает имя Учащиеся.* .
В таблице Специализации дважды щелкните поле Код учащегося , чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Код учащегося . В строке Условие отбора столбца Код учащегося введите Like [Учащиеся].[Код учащегося] .
В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Специализация . В строке Условие отбора введите МАТЕМ .
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .
Запрос выполняется, и отображаются оценки по математике только тех учащихся, у которых этот предмет профилирующий.
Иногда возникает необходимость сравнить два файла MS Excel. Это может быть нахождение расхождения цен на определенные позиции или изменение каких-либо показаний, не суть важно, главное, что необходимо найти определенные расхождения.
Не лишним будет упомянуть, что, если в файле MS Excel находится пара-тройка записей, тогда прибегать к автоматизации нет смысла. Если же файл насчитывает несколько сотен, а то и тысяч записей, то без помощи вычислительной мощности компьютера не обойтись.
Смоделируем ситуацию, когда два файла имеют одинаковое количество строк, а расхождение необходимо искать в определенном столбце либо в нескольких столбцах. Такая ситуация возможна, например, если необходимо сравнить цену на товары по двум прейскурантам, либо сравнить замеры спортсменов до и после тренировочного сезона, правда для такой автоматизации их должно быть очень много.
В качестве рабочего примера возьмем файл с показателями вымышленных участников: бег на 100 метров, бег на 3000 метров и подтягивания. Первый файл – это замер в начале сезона, а второй – конец сезона.
Первый способ решения поставленной задачи. Решение только силами формул MS Excel.
Поскольку записи выстроены вертикально (наиболее логичное построение) то необходимо воспользоваться функцией . В случае использования горизонтального размещения записей придется воспользоваться функцией .
Для сравнения показателей бега на 100 метров формула выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА);"Разницы нет")
В случае, если разницы нет, выводится сообщение, что разницы нет, если она присутствует, тогда от значения в конце сезона отнимается показатель начала сезона.
Формула для бега на 3000 метров выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;4;ИСТИНА)<>E2;"Разница есть";"Разницы нет")
Если конечное и начальное значения не равны выводится соответствующее сообщение. Формула для подтягиваний может быть аналогична любой из предыдущих, дополнительно приводить ее смысла нет. Конечный файл с найденными расхождениями приведен ниже.
Небольшое пояснение. Для удобства читаемости формул данные из двух файлов были перемещены в один (на разные листы), но этого можно было не делать.
Видео сравнения двух файлов MS Excel с помощью функций и .
Второй способ решения задачи. Решение с помощью MS Access.
Поставленную задачу можно решить, если предварительно импортировать файлы MS Excel в Access. Что касается самого способа импорта внешних данных, то для нахождения различающихся полей разницы нет (любой из представленных вариантов подойдет).
Последний представляет собой связь файлов Excel и Access, поэтому при изменении данных в файлах Excel, расхождения будут находится автоматически при выполнении запроса в MS Access.
Следующим шагом после произведения импорта будет создание связей между таблицами. В качестве связующего поля выбираем уникальное поле «№ п/п».
Третьим шагом будет создание простого запроса на выборку с помощью конструктора запросов.
В первом столбце указываем, какие записи необходимо вывести, а во втором – при каких условиях записи будут выведены. Естественно, для второго и третьего поля действия будут аналогичными.
Видео сравнения файлов MS в Excel, с помощью MS Access.
В результате проделанных манипуляций выведены все записи, с разными данными в поле: «Бег на 100 метров». Файл MS Access представлен ниже (к сожалению, внедрить, как файл Excel, SkyDrive не позволяет)
Вот такие два способа существуют для нахождение расхождений в таблицах MS Excel. У каждого есть как преимущества, так и недостатки. Понятно, что это не исчерпывающий список сравнения двух файлов Excel. Ваши предложения ждем в комментариях.
Чтение этой статьи займёт у Вас около 10 минут. В следующие 5 минут Вы сможете легко сравнить два столбца в Excel и узнать о наличии в них дубликатов, удалить их или выделить цветом. Итак, время пошло!
Excel – это очень мощное и действительно крутое приложение для создания и обработки больших массивов данных. Если у Вас есть несколько рабочих книг с данными (или только одна огромная таблица), то, вероятно, Вы захотите сравнить 2 столбца, найти повторяющиеся значения, а затем совершить с ними какие-либо действия, например, удалить, выделить цветом или очистить содержимое. Столбцы могут находиться в одной таблице, быть смежными или не смежными, могут быть расположены на 2-х разных листах или даже в разных книгах.
Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B . Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.
Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B .
Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3 .
В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!
Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул
Вариант А: оба столбца находятся на одном листе
Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).
Вариант В: два столбца находятся на разных листах (в разных книгах)
Обработка найденных дубликатов
Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними делать. Просматривать все повторяющиеся записи в таблице вручную довольно неэффективно и занимает слишком много времени. Существуют пути получше.
Показать только повторяющиеся строки в столбце А
Если Ваши столбцы не имеют заголовков, то их необходимо добавить. Для этого поместите курсор на число, обозначающее первую строку, при этом он превратится в чёрную стрелку, как показано на рисунке ниже:
Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):
Дайте названия столбцам, например, “Name ” и “Duplicate? ” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):
После этого нажмите меленькую серую стрелку рядом с “Duplicate? “, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate , и нажмите ОК .
Вот и всё, теперь Вы видите только те элементы столбца А , которые дублируются в столбце В . В нашей учебной таблице таких ячеек всего две, но, как Вы понимаете, на практике их встретится намного больше.
Чтобы снова отобразить все строки столбца А , кликните символ фильтра в столбце В , который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:
Изменение цвета или выделение найденных дубликатов
Если пометки “Duplicate ” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…
В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1 , чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.
Теперь Вы точно не пропустите ни одной ячейки с дубликатами:
Удаление повторяющихся значений из первого столбца
Отфильтруйте таблицу так, чтобы показаны были только ячейки с повторяющимися значениями, и выделите эти ячейки.
Если 2 столбца, которые Вы сравниваете, находятся на разных листах , то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):
Нажмите ОК , когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:
Если 2 столбца расположены на одном листе , вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А , сделайте следующее:
Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.
