Поиск совпадений в 3,4,5... и более списках
Поиск совпадений в двух списках в Microsoft Excel - задача типовая и решается плюс-минус стандартными способами - я уже делал на эту тему пару видео и писал подробные статьи с разбором нескольких способов: формулами, через сводные таблицы и даже Power Query. Но что делать, если нужно найти общие элементы не в двух, а большем количестве списков? Трех, четырех, десяти?! На первый взгляд кажется, что задача кратно усложняется, но, на самом деле, всё не так страшно.
Способ 1. Обычные формулы
Первый способ будет основан на обычных формулах и, по сравнению с другими подходами, я бы назвал его несколько громоздким. Но зато универсальным и работающим абсолютно в любой версии Excel.
Чтобы наши формулы были короче и понятнее, давайте превратим наши списки в именованные диапазоны, чтобы их названия потом можно было использовать в вычислениях. Для этого выделим все три списка (удерживая клавишу Ctrl), а затем выберем команду Формулы - Создать из выделенного - В строке выше (Formulas - Create from selection - Top row):
Проверить получившиеся имена можно там же, на вкладке Формулы в Диспетчере имен (Names manager).
Для простоты понимания, сначала давайте разомнёмся и начнём со сравнения пока только двух списков, забыв на минуту про третий. Для наглядности можно ещё дополнительно их выделить и включить подсветку совпадений через Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate values).
Для поиска количеств вхождения элементов первого списка во второй можно использовать классическую функцию СЧЁТЕСЛИ, которая в первом аргументе ищет вхождения второго. Если получился 0 - совпадения нет, если не 0 - элемент является общим для обоих списков:
Теперь домножим полученный массив нулей и единиц на номер строки каждого элемента, используя функцию СТРОКА (ROW). Поскольку наш первый список начинается с ячейки А2 (т.е. со второй строки на листе), то вычтем дополнительно единицу, чтобы это скомпенсировать. Получим массив из номеров строк совпадений и минус единиц там, где совпадения не было:
Чтобы вывести номера совпадений подряд, избавившись от -1 между ними, используем функцию НАИБОЛЬШИЙ (LARGE), которая умеет выводить N-е по счету максимальное значение из исходного списка. Тут нам пригодятся вспомогательные порядковые номера слева:
Ну и, наконец, чтобы вывести элементы списка с найденными номерами, применим функцию ИНДЕКС (INDEX), первый аргумент которой представляет собой список, откуда нужно извлечь данные, а второй - порядковый номер элемента, который нам нужен. Ну, а чтобы спрятать ошибки в конце списка, завернём полученную формулу в функцию ЕСЛИОШИБКА (IFERROR), заменив с её помощью ошибки на пустую текстовую строку "":
Дальше - проще. Если мы хотим добавить к поиску совпадений третий список, то нам потребуется лишь вставить в формулу еще один множитель в виде функции СЧЁТЕСЛИ (COUNTIF), проверяющий совпадения Списка1 и Списка3:
Эта функция, как и предыдущая, генерит массив из нулей (при несовпадении) и единичек (на совпадениях), который потом умножается на предыдущий массив нулей и единиц, возвращенный первой функцией СЧЁТЕСЛИ. Таким образом остаются только те элементы, которые дают 1 во обоих функциях, т.е. встречаются во всех трёх списках. Само собой, можно легко продолжать в том же духе, добавляя любое количество списков в виде множителей с функциями СЧЁТЕСЛИ при необходимости.
Формула получается, конечно, достаточно громоздкой + требует вспомогательного столбца с порядковыми номерами. Но её преимущество в универсальности - она работает в любой версии Excel.
Способ 2. Динамические массивы и функция ФИЛЬТР
Если вы работаете в версии Microsoft Excel, которая уже поддерживает динамические массивы (а это Excel 2021 и новее), то нашу задачу можно решить гораздо изящнее:
Как и в предыдущем способе, сначала сравним 1 и 2, а также 1 и 3 списки с помощью функции СЧЁТЕСЛИ и перемножим массивы нулей и единичек, которые они возвращают. Это даст нам массив, где 1 будут у тех элементов Списка1, которые встречаются в обоих следующих списках. А чтобы получить значения совпадающих ячеек, используем функцию ФИЛЬТР (FILTER), которая отберет из Списка1 те значения, для которых перемножение массивов дает 1.
Вот и всё. Быстро, компактно и нужно заморачиваться с перехватом ошибок. Само собой, в эту формулу также легко можно добавить 4-й, 5-й и т.д. списки в виде дополнительных множителей с функцией СЧЁТЕСЛИ(СписокN; Список1).
Способ 3. Мощь Power Query
Но не формулами едиными живы пользователи Excel, правда? Так что под занавес, давайте решим ту же задачу с помощью надстройки Power Query, которая доступна в Excel, начиная с 2010 года - так что этот способ тоже практически полностью универсален. Ну, а чтобы вы полностью прочувствовали подход, давайте слегка усложним задачу и будем сравнивать не 2, не 3, и даже не 4 списка, а сразу 15! (на самом деле, количество списков тут вообще роли не играет - увидите сами).
В качестве исходных данных возьмём вот такой рабочий график, где по столбцам идут дни, по строкам - 2-х часовые рабочие смены, а на пересечении лежит имя сотрудника. Некоторые сотрудники могут брать 2-3 смены в день - для нас это не принципиально:
Задача предельно проста - вывести список людей, которые работали каждый день (в любую смену). Т.е. другими словами, нам нужно сравнить 15 списков сотрудников за 15 дней и вывести элементы, которые присутствуют в каждом из них.
Поскольку с высокой степенью вероятности к этой таблице могут в будущем дописывать новые столбцы или строки, то давайте конвертируем её сначала в динамическую (умную) сочетанием клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table). Если по каким-то причинам для вас это преобразование нежелательно, то вместо умной таблицы можно сделать динамически растягивающийся именованный диапазон. Я делал на эту тему видео и писал статью пару лет назад - ссылка будет в описании, кому интересно.
Алгоритм действий такой:
- Грузим нашу таблицу в Power Query, используя команду Данные - Из таблицы / диапазона (Data - From Table/Range).
- Убираем первый столбец с временем смен (он нам не важен).
- Создаем новый шаг, нажав на кнопку fx в строке формул
- Дописываем к имени предыдущего шага в строке формул функцию Table.ToColumns, которая разбивает таблицу на столбцы - получаем объект список (list), состоящий из списков, где каждый вложенный список - это столбец из нашей исходной таблицы.
- Теперь найдем общие элементы всех вложенных списков-столбцов - для этого завернём нашу формулу в функцию List.Intersect, которая ищет пересечения всех заданных ей в качестве аргумента списков:
Вот и всё.
Останется преобразовать наш список в таблицу для удобства (хотя это и не обязательно), дать имя нашему столбцу и выгрузить результаты на лист командой Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to...)
Само собой, в будущем при внесении любых изменений в график или дописывании к нему, например, новых столбцов справа, нам останется лишь обновить наш запрос командой Данные - Обновить всё (Data - Refresh All) - и мы тут же получим обновленный список наших трудоголиков :)
Ссылки по теме
- Сравнение двух списков в Microsoft Excel
- Поиск совпадений в двух списках в Excel
- Слияние двух списков без дубликатов в Excel
Подскажите, пожалуйста, а как на примере способа №2 сделать список с различиями из нескольких источников?
Но это более узкая задача и менее интересная (поиск того кто был именно во всех списках), и на практике малоприменимая, потому как чаще нужно будет знать в скольких списках встречалось определенное значение, из которой уже можно получить решение данной задачи. Т.е. какое количество раз сотрудник был смене.
Подскажите пожалуйста, нет ли у вас понимания, какие существуют способы, кроме сводной таблицы сделать данный анализ? Существуют ли способы в итоговом результате отразить не только количество вхождений в разных списках, но и наименование списков?
Например, Иван -, всего 4 раза - 1марта,2марта,3марта,5 марта.
1. Отменить свертывание других столбцов (кроме 1-го со сменами): дни в отдельном столбце, имена в отдельном столбце
2. Удалить колонку со сменами
3. Удалить в таблице строки-дубликаты (исключаем лишние строки, когда у сотрудника несколько смен в день)
4. Сгруппировать кол-во смен по именам
5. Отфильтровать колонку с кол-вом смен по значению "15"
Ваш способ, конечно, интереснее и короче)