Спојување на две листи без дупликати

Класична ситуација: имате две листи кои треба да се спојат во една. Покрај тоа, во почетните списоци може да има и уникатни елементи и соодветни (и помеѓу списоците и внатре), но на излезот треба да добиете листа без дупликати (повторувања):

Ајде традиционално да погледнеме неколку начини за решавање на таков заеднички проблем - од примитивни „на чело“ до посложени, но елегантни.

Метод 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, оваа формула изгледа вака:

=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(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
• Извлекување на уникатни ставки од список
• Како да споредите две списоци едни со други за совпаѓања и разлики