Как сравнить два листа и получить общие строки

Вопрос или проблема

Col-1  Col-2    Col-3
one    data11   data12
two    data21   date22
three  data31   data32
five   date49   data69


Col-1   Col-2   Col-3
one     data11  data12
two     data21  date22
three   data31  data36
five    date45  data69

Col-1 Col-2 Col-3
one data11  data12
two data21  date22

Ответ или решение

Теория

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

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

Пример

Рассмотрим два листа Excel. Предположим, что первый лист (Sheet1) и второй лист (Sheet2) имеют одинаковую структуру данных:

Sheet1:

Col-1  Col-2    Col-3
one    data11   data12
two    data21   date22
three  data31   data32
five   date49   data69

Sheet2:

Col-1   Col-2   Col-3
one     data11  data12
two     data21  date22
three   data31  data36
five    date45  data69

Необходимо определить общие строки в обоих листах. В данном примере это строки:

Col-1 Col-2 Col-3
one data11  data12
two data21  date22

Применение

Использование Power Query

1. Открытие Power Query:
   Откройте Excel и перейдите к вкладке "Данные". Здесь вы найдете "Получить и преобразовать данные", в котором есть команда "Запустить Power Query Editor".

2. Загрузка данных:

   • Выберите "Загрузить из таблицы/диапазона" для каждого из листов (Sheet1 и Sheet2). Это создаст два отдельных запроса в Power Query Editor.

3. Сравнение таблиц:

   • Перейдите на панель "Главная" в Power Query Editor.
   • Используйте функцию "Объединить запросы", чтобы выполнить слияние двух запросов. Здесь потребуется указать, какие столбцы использовать для объединения (выберите три столбца на обоих листах).
   • Убедитесь, что выбрана опция "Только совпадающие строки" для фильтрации данных.

4. Удаление ненужных данных:

   • После выполнения объединения Power Query добавит "Новый столбец" с данными из второго листа, которые совпали.
   • Удалите этот "Новый столбец" или скройте его, если он не нужен для дальнейшего анализа.

5. Загрузка результата:

   • Нажмите "Закрыть и загрузить" для добавления отфильтрованного набора данных с общими строками в книгу Excel.

Проверка и верификация

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

Советы по оптимизации:

• Используйте именованные диапазоны в Excel для удобства работы в Power Query.
• Убедитесь, что данные на обоих листах отформатированы одинаково, чтобы избежать лишних ошибок при сравнении.
• Регулярно обновляйте источники данных в Power Query, чтобы поддерживать актуальность информации.

Заключение

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