Обединяване на два списъка без дубликати

Класическа ситуация: имате два списъка, които трябва да бъдат обединени в един. Освен това в първоначалните списъци може да има както уникални елементи, така и съвпадащи (както между списъците, така и вътре), но на изхода трябва да получите списък без дубликати (повторения):

Нека традиционно да разгледаме няколко начина за решаване на такъв често срещан проблем - от примитивно „на челото“ до по-сложно, но елегантно.

Метод 1: Премахване на дубликати

Можете да разрешите проблема по най-простия начин - копирайте ръчно елементите от двата списъка в един и след това приложете инструмента към получения набор. Премахване на дубликати от раздела Дата (Данни — премахване на дубликати):

Разбира се, този метод няма да работи, ако данните в списъците с източници често се променят – ще трябва да повтаряте цялата процедура след всяка промяна отново. 

Метод 1а. осева таблица

Този метод всъщност е логично продължение на предишния. Ако списъците не са много големи и максималният брой елементи в тях е известен предварително (например не повече от 10), тогава можете да комбинирате две таблици в една чрез директни връзки, да добавите колона с такива вдясно и изградете обобщена таблица въз основа на получената таблица:

Както знаете, обобщената таблица игнорира повторенията, така че на изхода ще получим комбиниран списък без дубликати. Помощната колона с 1 е необходима само защото Excel може да изгради обобщени таблици, съдържащи поне две колони.

Когато оригиналните списъци се променят, новите данни ще отидат в комбинираната таблица чрез директни връзки, но обобщената таблица ще трябва да се актуализира ръчно (щракнете с десния бутон на мишката – Актуализиране и запазване). Ако не се нуждаете от преизчисляване в движение, тогава е по-добре да използвате други опции.

Метод 2: Формула за масив

Можете да решите проблема с формули. В този случай преизчисляването и актуализирането на резултатите ще се извърши автоматично и незабавно, веднага след промените в оригиналните списъци. За удобство и краткост нека дадем имената на нашите списъци. Списък 1 и Списък 2използвайки Мениджър на имена етикет формула (Формули — Мениджър на имена — Създаване):

След именуването формулата, от която се нуждаем, ще изглежда така:

На пръв поглед изглежда страховито, но всъщност всичко не е толкова страшно. Позволете ми да разширя тази формула на няколко реда с помощта на клавишната комбинация Alt+Enter и отстъп с интервали, както направихме, например тук:

Логиката тук е следната:

• Формулата INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) избира всички уникални елементи от първия списък. Веднага щом се изчерпят, започва да дава грешка #N/A:

  

• Формулата INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) извлича уникалните елементи от втория списък по същия начин.
• Вложени една в друга две IFERROR функции реализират изхода първо на уникалните от списък-1, а след това от списък-2 една след друга.

Обърнете внимание, че това е формула за масив, т.е. след въвеждане трябва да се въведе в клетка, която не е обикновена Въведете, но с клавишна комбинация Ctrl+Превключване+Въведете и след това копирайте (плъзнете) надолу към дъщерните клетки с поле.

В английската версия на Excel тази формула изглежда така:

=АКОГРЕШКА(АКОГРЕШКА(ИНДЕКС(Списък1, МАЧ(0, COUNTIF($E$1:E1, Списък1), 0)), ИНДЕКС(Списък2, МАЧ(0, COUNTIF($E$1:E1, Списък2), 0)) ), “”) 

Недостатъкът на този подход е, че формулите за масиви значително забавят работата с файла, ако изходните таблици имат голям (няколкостотин или повече) брой елементи. 

Метод 3. Power Query

Ако вашите списъци с източници имат голям брой елементи, например няколко стотици или хиляди, тогава вместо бавна формула за масив е по-добре да използвате фундаментално различен подход, а именно инструментите за добавки на Power Query. Тази добавка е вградена в Excel 2016 по подразбиране. Ако имате Excel 2010 или 2013, можете да го изтеглите и инсталирате отделно (безплатно).

Алгоритъмът на действията е следният:

1. Отворете отделен раздел на инсталираната добавка Запитване за захранване (ако имате Excel 2010-2013) или просто отидете в раздела Дата (ако имате Excel 2016).
2. Изберете първия списък и натиснете бутона От таблица/обхват (От диапазон/таблица). Когато ни попитат за създаването на „интелигентна маса“ от нашия списък, ние се съгласяваме:

   

3. Отваря се прозорецът на редактора на заявката, където можете да видите заредените данни и името на заявката Таблица 1 (можете да го промените на свой собствен, ако искате).
4. Кликнете два пъти върху заглавката на таблицата (word Списък 1) и го преименувайте на всяко друго (напр Хора). Какво точно да назовем не е важно, но измисленото име трябва да се запомни, т.к. ще трябва да се използва отново по-късно при импортиране на втората таблица. Обединяването на две таблици в бъдеще ще работи само ако заглавията на колоните им съвпадат.
5. Разгънете падащия списък в горния ляв ъгъл затворете и изтеглете И изберете Затворете и заредете в... (Затвори&Зареди до...):

   

6. В следващия диалогов прозорец (може да изглежда малко по-различно – не се тревожете), изберете Просто създайте връзка (Само създаване на връзка):

   

7. Повтаряме цялата процедура (точки 2-6) за втория списък. Когато преименувате заглавие на колона, е важно да използвате същото име (Хора), както в предишната заявка.
8. В прозореца на Excel в раздела Дата или на раздела Запитване за захранване Изберете Получаване на данни – Комбиниране на заявки – Добавяне (Получаване на данни — Обединяване на заявки — Добавяне):

   

9. В диалоговия прозорец, който се показва, изберете нашите заявки от падащите списъци:

   

10. В резултат на това ще получим нова заявка, където два списъка ще бъдат свързани един под друг. Остава да премахнете дубликати с бутона Изтриване на редове – премахване на дубликати (Изтриване на редове — Изтриване на дубликати):

    

11. Завършената заявка може да бъде преименувана от дясната страна на панела с опции, давайки й разумно име (това всъщност ще бъде името на таблицата с резултати) и всичко може да бъде качено в листа с командата затворете и изтеглете (Затваряне и зареждане):

    

В бъдеще, с всякакви промени или допълнения към оригиналните списъци, ще бъде достатъчно просто да щракнете с десния бутон, за да актуализирате таблицата с резултати.

• Как да събирате множество таблици от различни файлове с помощта на Power Query
• Извличане на уникални елементи от списък
• Как да сравните два списъка един с друг за съвпадения и разлики