Как сравнить две таблицы в эксель

Сравнение двух таблиц в Excel

Довольно часто приходится сравнивать таблицы Excel между собой. Задача элементарная, если строк в таблице немного и всего один столбец для сравнения. А сколько времени необходимо потратить если необходимо сопоставить таблицы по трем столбцам? А если строк в таблице более 1000? Надстройка поможет справиться с этой задачей гораздо быстрее.

Описание работы программы

После установки надстройки у вас появится новая вкладка с командой вызова функции. При нажатии на команду Сравнение диапазонов появляется диалоговое окно для ввода параметров.

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

Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.

Чтобы сравнить таблицы необходимо выполнить следующие действия:

• Указать диапазоны таблиц.
• Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
• Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
• Указать тип сравнения.
• Выбрать вариант выдачи результатов.

Тип сравнения таблиц

Программа позволяет выбрать несколько типов сравнения таблиц:

Найти строки одной таблицы, которые отсутствуют в другой таблице

При выборе данного типа сравнения программа ищет строки одной таблицы, которые отсутствуют в другой. Если вы сопоставляете таблицы по нескольким столбцам, то результатом работы будут строки, в которых имеется различие хотя бы по одному из столбцов.

Найти совпадающие строки

При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.

Пример работы программы в данном режиме представлен справа на картинке.

Сопоставить таблицы на основе выбранной

В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.

Сравнение таблиц по четырем и более столбцам

Если вам не хватает функционала программы и необходимо сопоставить таблицы по четырем или более столбцам, то вы можете выйти из положения следующим образом:

• Создайте в таблицах по пустому столбцу.
• В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.

Таким образом, вы получите 1 столбец, содержащий значения нескольких столбцов. Ну а как сопоставить один столбец вы уже знаете.

Надстройка
VBA-Excel

Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.

Сравнение таблиц в Excel

При работе с большими объемами информации пользователь может столкнуться с такой задачей, как сравнение двух табличных источников данных. При хранении данных в единой системе учета (например, системы на базе 1С Предприятие, системы, использующие SQL базы данных), для сравнения данных могут использоваться встроенные в систему или СУБД возможности. Как правило, для этого достаточно привлечь программиста, который напишет запрос к базе данных, или программный механизм отчета. С запросом может справиться и опытный пользователь, владеющий навыком написания запросов 1C, либо SQL.

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

Для решения обозначенных проблем идеальным приемом является использование для сравнения данных табличного редактора Microsoft Excel. Большинство распространенных систем управленческого и регламентированного учета поддерживают выгрузку в формат Excel. Эта задача потребует всего лишь определенной квалификации пользователя по работе с этим офисным пакетом и не потребует навыков программирования.

Рассмотрим решение задачи сравнения таблиц в Excel на примере. Мы имеем две таблицы, содержащие списки квартир. Источники выгрузки — 1С Предприятие (учёт строительства) и таблица в Excel (учёт продаж). Таблицы размещены в рабочей книге Excel на первом и втором листах соответственно.

Перед нами стоит задача сравнить эти списки по адресу. В первой таблице — все квартиры дома. Во второй таблице — только проданные квартиры и имя покупателя. Конечная цель — отобразить в первой таблице по каждой квартире имя покупателя (для тех квартир, которые были проданы). Задача осложняется тем, что адрес квартиры в каждой таблице является строительным и состоит из нескольких полей: 1) адрес корпуса (дома), 2) секция (подъезд), 3) этаж, 4) номер на этаже (например, от 1 до 4).

Для сравнения двух таблиц Excel нам нужно добиться того, чтобы в обеих таблицах каждая строка идентифицировалась бы одним полем, а не четырьмя. Получить такое поле можно объединив значения четырех полей адреса функцией Сцепить(). Назначение функции Сцепить() — объединение нескольких текстовых значений в одну строку. Значения в функции перечисляются через символ «;». В качестве значений могут выступать как адреса ячеек, так и произвольный текст , заданный в кавычках.

Шаг 1. Вставим в начале первой таблицы пустую колонку «A» и пропишем в ячейке этой колонки напротив первой строки с данными формулу:
=СЦЕПИТЬ(B3;»-«;C3;»-«;D3;»-«;E3)
Для удобства визуального восприятия между значениями объединяемых ячеек мы установили символы «-«.

Шаг 2. Скопируем формулу в нижеследующие ячейки колонки А.

Шаг 3. Далее выполним те же операции (шаг 1 и шаг 2) для таблицы 2 на втором листе рабочей книги. Теперь можно переходить к сравнению двух таблиц по значениям колонки «A».

Шаг 4. Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР(). Назначение функции ВПР() — поиск значения в крайнем левом столбце таблицы и возвращение значения ячейки, находящейся в указанном столбце той же строки. Первый параметр — искомое значение. Второй параметр — таблица, в которой будет осуществляться поиск значения. Третий параметр — номер столбца, из ячейки которого в найденной строке будет возвращено значение. Четвертый параметр — тип поиска: ложь — точное совпадение, истина — приближенное совпадение. Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней. Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ)
При копировании формул «умный» Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами «$». Вместо «Лист2!A3:F10» делаем «Лист2!$A$3:$F$10».

Шаг 5. Скопируем формулу в нижеследующие ячейки результирующей колонки.

Результат решения задачи:

Другие интересные статьи

• Как сравнить две таблицы в Excel с использованием макросов VBA?
• Горячие клавиши Excel

Как сравнить две таблицы по артикулу?

Имеются две гугл таблицы, с разным количеством строк и разной сортировкой в каждой таблице есть столбец «Артикул». Нужно извлечь только те строки которые совпали по Артикулу и представить в таком виде для сравнения цены:

Отслеживать
задан 25 мар 2020 в 23:28
17 4 4 бронзовых знака

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

В первой таблице в D3 и протянуть ниже:

 =IFERROR(VLOOKUP(D3;$F$3:$H;3;);) 

Подтянутся цены из второй таблицы.

Дальше — фильтр по значениям >0, копирование видимых в отдельный диапазон

Отслеживать
ответ дан 26 мар 2020 в 10:50
vikttur_Stop_RU_war_in_UA vikttur_Stop_RU_war_in_UA
8,276 3 3 золотых знака 18 18 серебряных знаков 24 24 бронзовых знака

• excel
• сравнение
• google-spreadsheet
• таблицы

Важное на Мете

Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.11.29.1725

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

покупка

Сравнение двух таблиц — обычная задача в Excel. Возможно, вы захотите определить одинаковые, разные или отсутствующие в столбцах элементы. Например, у нас есть две похожие таблицы с одинаковыми именами столбцов, но между ними есть некоторые различия. Теперь вы хотите найти различия для сравнения каждой строки двух таблиц, как показано на скриншотах ниже. В этом уроке будет показано, как использовать Power Query функция для решения этой задачи в Excel.

Сравните две таблицы в Power Query

Пожалуйста, следуйте пошаговому руководству, чтобы выполнить эту работу:

Создание запросов из двух таблиц

1. Выберите первую таблицу, которую вы хотите сравнить, затем в Excel 2019 и Excel 365 щелкните Данные > Из таблицы/диапазона, см. снимок экрана:

Внимание: в Excel 2016 и Excel 2021 нажмите Данные > Из таблицы, см. снимок экрана:

2. Затем в открытом Power Query редактор окна, нажмите Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:

3, В следующих Импортировать данные диалоговое окно, выберите Только создать соединение вариант, а затем щелкните OK кнопку, чтобы закрыть диалог. см. скриншот:

4. Повторите те же шаги 1-3, чтобы создать соединение для второй таблицы. Наконец, у вас должно быть два запроса, соответствующих вашим таблицам. И вы можете увидеть две таблицы в Запросы и связи панель. Смотрите скриншот:

Объединение запросов для сравнения двух таблиц

После создания запросов из двух таблиц вы должны объединить запросы в новую таблицу.

5. Пожалуйста, продолжайте нажимать Данные > Получить данные > Объединить запросы > идти в Excel 2019 и Excel 365 см. снимок экрана:

Внимание: в Excel 2016 и Excel 2021 нажмите Данные > Новый запрос > Объединить запросы > идти, см. снимок экрана:

6. Во всплывающем идти диалоговом окне выполните следующие действия:

• Выберите имя первой и второй таблицы из выпадающего списка по отдельности.
• Выберите столбцы, которые вы хотите сравнить, из двух таблиц. (Чтобы выбрать несколько столбцов, нажмите Ctrl чтобы выбрать их один за другим) В этом примере я выберу все столбцы для сравнения.
• И затем выберите Полный внешний (все строки из обоих) под Присоединяйтесь к добру падать.
• Наконец, нажмите OK кнопку.

7. Затем новый столбец для второй таблицы вставляется рядом с первой таблицей:

• Нажмите Расширьте значок рядом с заголовком столбца.
• Проверьте Расширьте опцию.
• Отметьте столбцы, которые вы хотите вставить в таблицу сравнения.
• Наконец, нажмите OK кнопку.

8. Затем сразу же заполняются данные второй таблицы. А теперь, пожалуйста, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить для загрузки данных в новый рабочий лист. Смотрите скриншот:

9. Теперь вы можете быстро и легко найти разные строки двух таблиц. Строки, содержащие одни и те же данные указанных столбцов, перечислены в одной строке, а пустые столбцы указывают строки с разными данными. Смотрите скриншот:

Лучшие инструменты для офисной работы

Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего.

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!