Слияние двух списков без дубликатов
Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):
Давайте традиционно рассмотрим несколько способов решения такой распространенной задачи - от примитивных "в лоб" до более сложных, но изящных.
Способ 1. Удаление дубликатов
Можно решить задачу самым простым путем - руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data - Remove Duplicates):
Само-собой, такой способ не подойдет, если данные в исходных списках часто меняются - придется повторять всю процедуру после каждого изменения заново.
Способ 1а. Сводная таблица
Этот способ является, по сути, логическим продолжением предыдущего. Если списки не очень большого размера и заранее известно предельное количество элементов в них (например, не больше 10), то можно объединить две таблицы в одну прямыми ссылками, добавить справа столбец с единичками и построить по получившейся таблице сводную:
При изменении исходных списков новые данные по прямым ссылкам попадут в объединенную таблицу, но сводную придется обновить уже вручную (правой кнопкой мыши - Обновить). Если не нужен пересчет "на лету", то лучше воспользоваться другими вариантами.
Способ 2. Формула массива
Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas - Name Manager - Create):
После именования, нужная нам формула будет выглядеть следующим образом:
На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут:
Логика тут следующая:
- Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются - начинает выдавать ошибку #Н/Д:
- Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным образом извлекает уникальные элементы из второго списка.
- Вложенные друг в друга две функции ЕСЛИОШИБКА реализуют вывод сначала уникальных из списка-1, а потом из списка-2 друг за другом.
Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter, а сочетанием клавиш Ctrl+Shift+Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.
В английской версии Excel эта формула выглядит как:
=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), "")
Минус у такого подхода в том, что формулы массива ощутимо замедляют работу с файлом, если в исходных таблицах большое (несколько сотен и более) количество элементов.
Способ 3. Power Query
Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно - инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).
Алгоритм действий следующий:
- Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
- Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table). На вопрос про создание из нашего списка "умной таблицы" - соглашаемся:
- Открывается окно редактора запросов, где будет видно загруженные данные и имя запроса Таблица1 (можно поменять на свое, если хотите).
- Делаем двойной щелчок в заголовок таблицы (слово Список1) и переименовываем на любой другой (например Люди). Каки именно назвать - не важно, но придуманное название нужно запомнить, т.к. его придется использовать потом еще раз при импорте второй таблицы. Объединить две таблицы в дальнейшем получится только если заголовки их столбцов совпадают.
- Разворачиваем выпадающий список в левом верхнем углу Закрыть и загрузить и выбираем Закрыть и загрузить в... (Close&Load to...):
- В следующем диалоговом окне (оно может выглядеть немного по-другому - не пугайтесь) выбираем Только создать подключение (Only create connection):
- Повторяем всю процедуру (пункты 2-6) для второго списка. При переименовании заголовка столбца важно использовать то же имя (Люди), что и в предыдущем запросе.
- В окне Excel на вкладке Данные (Data) или на вкладке Power Query выбираем Получить данные - Объединить запросы - Добавить (Get Data - Merge Queries - Append):
- В появившемся диалоговом окне выбираем наши запросы из выпадающих списков:
- В итоге получим новый запрос, где два списка будут соединены друг под другом. Осталось удалить дубликаты кнопкой Удалить строки - Удалить дубликаты (Delete Rows - Delete Duplicates):
- Готовый запрос можно переименовать справа на панели параметров, дав ему вменяемое имя (это будет имя таблицы-результата по факту) и все и можно все выгружать на лист командой Закрыть и загрузить (Close&Load):
В будущем, при любых изменениях или дополнениях в исходных списках, достаточно будет лишь правой кнопкой мыши обновить таблицу результатов.
Ссылки по теме
- Как собрать несколько таблиц из разных файлов с помощью Power Query
- Извлечение уникальных элементов из списка
- Как сравнить два списка между собой на совпадения и отличия
Не знал в каком разделе написать свой вопрос, может направите по итогам рассмотрения?
Есть несколько таблиц - в таблицах есть ячейки, данные в которых должны быть идентичны, если нет - то цифра введенная в такие ячейки окрашивается красным цветом автоматически и будет гореть красным, пока во всех ячейках эта цифра не будет прописана.
Или же, цифра в одной ячейке таблицы должна быть равна сумме или разнице чисел в ячейке из другой таблицы - при этом пока все цифры не приведут в соответствие, все они будут окрашены красным цветом.
Все это происходит в одной книге эксель, но в разных листах (таблицы).
Вопрос - как сделать такое подкрашивание красным цветом?
Благодарю заранее за ответ (гугл не помог в этом случае никак ).
а где вы нашли такой формат окна? у вас другая версия эксель?
Спасибо!
П.С.: естественно решение чтобы вместо "0" была просто пустая ячейка, но разделяющая уникальный список не катит. Еще раз спасибо!