Аб'яднанне двух спісаў без дублікатаў

Класічная сітуацыя: у вас ёсць два спісы, якія трэба аб'яднаць у адзін. Прычым у зыходных спісах могуць быць як унікальныя элементы, так і супадаючыя (як паміж спісамі, так і ўнутры), але на выхадзе трэба атрымаць спіс без дублікатаў (паўтораў):

Традыцыйна разгледзім некалькі спосабаў вырашэння такой распаўсюджанай праблемы – ад прымітыўных «на лоб» да больш складаных, але элегантных.

Спосаб 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, List1), 0)), INDEX(Спіс2, СУПАДЗЕННЕ(0, COUNTIF($E$1:E1, List2), 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
• Выманне унікальных элементаў са спісу
• Як параўнаць два спісы адзін з адным на супадзенні і адрозненні