Power Query: сравнение двух таблиц в Excel
Сравнение двух таблиц в Excel — частая задача при работе с данными из разных источников, обновлёнными наборами информации или проведении аудита. Типичные цели включают выявление записей, присутствующих в обеих таблицах, определение различий или поиск записей, отсутствующих в одном из списков. Например, вы можете получить новые данные от коллеги или из внешней системы и вам потребуется сравнить их со своим основным списком на предмет обновлений или расхождений. На приведённых ниже снимках экрана показаны две таблицы с одинаковой структурой столбцов, содержащие как совпадающие, так и отличающиеся записи. Умение эффективно выделять эти различия или совпадения крайне важно для проверки данных и принятия обоснованных бизнес-решений.
В этом руководстве подробно описано, как сравнивать две таблицы с помощью Power Query. Кроме того, если вы ищете альтернативные и практичные методы — в том числе с использованием формул, кода VBA или условного форматирования— ознакомьтесь с решениями в приведённом ниже оглавлении.
Сравнение двух таблиц в Power Query
Альтернативные решения
- Формула Excel — сравнение двух таблиц с помощью формулы
- Код VBA — сравнение двух таблиц с помощью макроса
- Использовать условное форматирование — визуальное сравнение таблиц
 |
 |
 |
Сравнение двух таблиц в Power Query
Power Query — эффективный инструмент в Excel для преобразования и анализа данных из нескольких источников. При необходимости построчного сравнения двух таблиц Power Query автоматизирует большую часть процесса, снижая риск ошибок при ручном сопоставлении и экономя время. Следуйте пошаговому руководству ниже для сравнения двух таблиц; в него включены примечания и практические советы, обеспечивающие бесперебойную работу.
Создание запросов из двух таблиц
1. Выделите первую таблицу, которую нужно сравнить. В Excel 2019 и Excel 365 перейдите на вкладку Данные и нажмите кнопку Из таблицы/диапазона. См. снимок экрана ниже.
Совет: перед началом убедитесь, что ваша таблица отформатирована как настоящая таблица Excel (Ctrl+T). Это поможет Power Query точно определить границы данных.
Примечание: в Excel 2016 и Excel 2021 соответствующий пункт меню называется Данные > Из таблицы. Эти команды функционально эквивалентны.
Если выделенный диапазон не отформатирован как таблица, Excel может предложить создать её.
2. Откроется окно Редактор Power Query. Здесь при необходимости можно просмотреть или очистить данные, но для сравнения можно сразу перейти к следующему шагу. Нажмите кнопку Закрыть и загрузить > Закрыть и загрузить в…, чтобы задать параметры подключения.
3. В диалоговом окне Импорт данных выберите параметр Только создать подключение, затем нажмите кнопку ОК. Эта опция позволяет использовать данные только внутри Power Query, не загружая их немедленно на лист. См. следующий снимок экрана.
4. Повторите действия из шагов 1–3, чтобы создать подключение для второй таблицы. Теперь обе таблицы отображаются как отдельные подключения на панели Запросы и подключения. Это подготовит данные к этапу сравнения.
Совет: дважды проверьте, что обе таблицы имеют одинаковые имена столбцов и структуру — это обеспечит точность сравнения на следующем шаге.
Объединение запросов для сравнения двух таблиц
После создания обоих запросов их необходимо объединить, чтобы построчно выявить различия или совпадения.
5. В Excel 2019 и Excel 365 перейдите на вкладку Данные, затем нажмите кнопку Получить данные > Объединить запросы > Объединить. Это запустит процесс объединения. См. снимок экрана.
Примечание: в Excel 2016 и Excel 2021 этот пункт доступен через меню Данные > Новый запрос > Объединить запросы > Объединить — сам процесс остаётся прежним.
6. В диалоговом окне Объединение:
- Выберите запросы первой и второй таблицы в двух выпадающих списках.
- Выберите столбцы для сравнения в каждой таблице — нажмите Ctrl, чтобы выделить несколько столбцов. Как правило, для корректного построчного сравнения нужно выбрать все столбцы.
- Выберите вариант Полное внешнее соединение (все строки из обеих таблиц) в качестве типа соединения. Этот параметр сопоставляет все строки и выделяет отсутствующие, лишние или отличающиеся записи.
- Нажмите кнопку ОК, чтобы продолжить.
7. Появится новый столбец с сопоставленными данными из второй таблицы.
- Щёлкните маленькую кнопку Развернуть (две стрелки) рядом с заголовком нового столбца.
- Выберите команду Развернуть и укажите, какие столбцы включить в результаты (обычно — все).
- Нажмите кнопку ОК, чтобы вставить их.
8. Данные второй таблицы теперь отображаются рядом с данными первой, что упрощает сравнение записей. Чтобы вернуть объединённые данные в Excel, перейдите в меню Главная > Закрыть и загрузить > Закрыть и загрузить. В результате построчное сравнение будет добавлено на новый лист.
9. На полученном листе легко выявить совпадения и расхождения: идентичные строки отображаются рядом, а различия — в пустых или отличающихся ячейках. Такой формат позволяет эффективно находить уникальные, отсутствующие или изменённые записи в обеих таблицах.
Совет по устранению неполадок: если некоторые записи не совпадают, как ожидалось, убедитесь, что столбцы для соединения имеют согласованный формат и что в исходных данных отсутствуют лишние пробелы или опечатки. Power Query чувствителен даже к самым незначительным отличиям.
В заключение, Power Query значительно упрощает сложный процесс сравнения двух таблиц в Excel. Освоив эти шаги, вы сможете легко применять их и в более сложных сценариях — например, при сравнении таблиц из разных файлов или сопоставлении по нескольким полям. Если вы регулярно работаете с обновляемыми данными, сохранение последовательности действий Power Query в виде шаблона существенно ускорит будущие сравнения аналогичных структур. В случае возникновения трудностей с выбором столбцов или объединением данных дополнительно проверьте типы данных в столбцах и заранее удалите все дублирующие строки, чтобы избежать проблем при сопоставлении.
Если Power Query покажется слишком сложным для разовых задач или вам нужно выделять различия прямо на листе, а также автоматизировать повторяющиеся сравнения, воспользуйтесь альтернативными решениями, описанными ниже.
Формула Excel — сравнение двух таблиц с помощью формулы
Одним из эффективных способов построчного сравнения двух таблиц для выявления различий — использование функции TEXTJOIN в Excel совместно с формулой ЕСЛИ.
Допустим, у вас есть Таблица1 в диапазоне A2:C10 и Таблица2 в диапазоне F1:H10, и вы хотите выяснить, какие элементы из Таблицы1 отсутствуют в Таблице2.
1. Введите следующую формулу в ячейку I2:
=IF(TEXTJOIN("|",,A2:C2)=TEXTJOIN("|",,F2:H2), "Match", "Mismatch")2. Затем протяните формулу на другие ячейки, чтобы получить результат. Если строки в обеих таблицах полностью совпадают, формула возвращает «Match» («Совпадение»); в противном случае — «Mismatch» («Несовпадение»).
- TEXTJOIN(«|»;;A2:C2) объединяет значения в ячейках A2–C2 в одну текстовую строку, разделяя их символом вертикальной черты «|».
- TEXTJOIN(«|»;;F2:H2) выполняет то же самое для ячеек F2–H2.
- Функция ЕСЛИ проверяет, идентичны ли две объединённые строки. Если они совпадают — возвращает «Match» («Совпадение»), а если различаются — «Mismatch» («Несовпадение»).
Код VBA — сравнение двух таблиц с помощью автоматизации макросами
Подход с использованием VBA идеально подходит, когда вы хотите быстро и наглядно сравнить две таблицы построчно, не загромождая лист формулами. Он особенно полезен при проверке или верификации импортированных данных — чистым и удобным для пользователя способом.
1. Щелкните Инструменты разработчика > Visual Basic, чтобы открыть редактор VBA.
2. В редакторе нажмите Вставка > Модуль и вставьте следующий код в окно модуля:
Sub CompareSelectedTablesRowByRow()
Dim rng1 As Range, rng2 As Range
Dim rowCount As Long, colCount As Long
Dim r As Long, c As Long
Dim xTitle As String
xTitle = "Compare Tables - KutoolsforExcel"
On Error Resume Next
Set rng1 = Application.InputBox("Select the first table range:", xTitle, Type:=8)
If rng1 Is Nothing Then Exit Sub
Set rng2 = Application.InputBox("Select the second table range:", xTitle, Type:=8)
If rng2 Is Nothing Then Exit Sub
On Error GoTo 0
If rng1.Rows.Count <> rng2.Rows.Count Or rng1.Columns.Count <> rng2.Columns.Count Then
MsgBox "Selected ranges do not have the same size.", vbExclamation, xTitle
Exit Sub
End If
rng1.Interior.ColorIndex = xlNone
rng2.Interior.ColorIndex = xlNone
For r = 1 To rng1.Rows.Count
For c = 1 To rng1.Columns.Count
If rng1.Cells(r, c).Value <> rng2.Cells(r, c).Value Then
rng1.Cells(r, c).Interior.Color = vbYellow
rng2.Cells(r, c).Interior.Color = vbYellow
End If
Next c
Next r
MsgBox "Comparison complete. Differences are highlighted in yellow.", vbInformation, xTitle
End Sub
3. Чтобы запустить код, нажмите кнопку Выполнить в окне VBA или клавишу F5. При появлении запроса сначала выделите диапазон первой таблицы, затем — второй. Макрос будет сравнивать значения ячеек обеих таблиц построчно; если значения различаются, соответствующие ячейки в обеих таблицах выделятся желтым цветом.
Использовать условное форматирование — Наглядное сравнение таблиц
Использовать условное форматирование предлагает наглядный и интуитивно понятный способ выделять различия или совпадения непосредственно на листе. Этот метод подходит, когда необходимо определить дублирующиеся или уникальные значения, не изменяя исходные данные, и особенно эффективен для небольших таблиц, расположенных рядом друг с другом.
1. Выделите диапазон первой таблицы (например,)A1:C10).
2. Перейдите на вкладку Главная > Использовать условное форматирование > Создать правило.
3. Выберите пункт Использовать формулу для определения форматируемых ячеек и введите следующую формулу: =A2F2.
4. Нажмите кнопку Формат, выберите Цвет заполнения и щелкните OK > OK, чтобы применить правило.
Результат: выделенные ячейки показывают значения из Таблицы 1, отсутствующие в Таблице 2. При необходимости повторите процедуру, чтобы сравнить Таблицу 2 с Таблицей 1.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек