Bashkimi i dy listave pa dublikatë

Një situatë klasike: ju keni dy lista që duhet të bashkohen në një. Për më tepër, në listat fillestare mund të ketë elemente unike dhe ato që përputhen (si midis listave ashtu edhe brenda), por në dalje ju duhet të merrni një listë pa dublikatë (përsëritje):

Le të shohim tradicionalisht disa mënyra për të zgjidhur një problem kaq të zakonshëm - nga primitive "në ballë" në më komplekse, por elegante.

Metoda 1: Hiqni dublikatat

Ju mund ta zgjidhni problemin në mënyrën më të thjeshtë - kopjoni manualisht elementët e të dy listave në një dhe më pas aplikoni mjetin në grupin që rezulton. Hiq Dublikatat nga skeda Data (Të dhënat - Hiqni dublikatat):

Sigurisht, kjo metodë nuk do të funksionojë nëse të dhënat në listat burimore shpesh ndryshojnë - do të duhet të përsërisni të gjithë procedurën pas çdo ndryshimi përsëri. 

Metoda 1a. Tabela strumbullar

Kjo metodë është, në fakt, një vazhdim logjik i asaj të mëparshme. Nëse listat nuk janë shumë të mëdha dhe numri maksimal i elementeve në to dihet paraprakisht (për shembull, jo më shumë se 10), atëherë mund të kombinoni dy tabela në një me lidhje të drejtpërdrejta, të shtoni një kolonë me ato në të djathtë dhe ndërtoni një tabelë përmbledhëse bazuar në tabelën që rezulton:

Siç e dini, tabela kryesore injoron përsëritjet, kështu që në dalje do të marrim një listë të kombinuar pa dublikatë. Kolona ndihmëse me 1 nevojitet vetëm sepse Excel mund të ndërtojë tabela përmbledhëse që përmbajnë të paktën dy kolona.

Kur të ndryshohen listat origjinale, të dhënat e reja do të shkojnë në tabelën e kombinuar përmes lidhjeve të drejtpërdrejta, por tabela kryesore do të duhet të përditësohet manualisht (kliko me të djathtën - Përditëso & Ruaj). Nëse nuk keni nevojë për rillogaritje në fluturim, atëherë është më mirë të përdorni opsione të tjera.

Metoda 2: Formula e vargjeve

Ju mund ta zgjidhni problemin me formula. Në këtë rast, rillogaritja dhe përditësimi i rezultateve do të ndodhë automatikisht dhe në çast, menjëherë pas ndryshimeve në listat origjinale. Për lehtësi dhe shkurtësi, le të japim emrat e listave tona. Listoni 1 и Listoni 2përdorim Emri Menaxher tab formulë (Formulat - Menaxheri i emrit - Krijo):

Pas emërtimit, formula që na nevojitet do të duket si kjo:

Në pamje të parë, duket e frikshme, por, në fakt, gjithçka nuk është aq e frikshme. Më lejoni ta zgjeroj këtë formulë në disa rreshta duke përdorur kombinimin e tastit Alt+Enter dhe indentimin me hapësira, siç bëmë, për shembull këtu:

Logjika këtu është si vijon:

• Formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) zgjedh të gjithë elementët unikë nga lista e parë. Sapo të mbarojnë, ajo fillon të japë një gabim #N/A:

  

• Formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) nxjerr elementet unike nga lista e dytë në të njëjtën mënyrë.
• Të ndërthurur në njëri-tjetrin dy funksione IFERROR zbatojnë fillimisht daljen e atyre unike nga lista-1, dhe më pas nga lista-2 njëri pas tjetrit.

Vini re se kjo është një formulë grupi, dmth pas shtypjes, duhet të futet në një qelizë që nuk është e zakonshme Shkruani, por me një shkurtore të tastierës Ctrl+Ndryshim+Shkruani dhe më pas kopjoni (tërhiqni) poshtë në qelizat fëmijë me një diferencë.

Në versionin anglisht të Excel, kjo formulë duket si kjo:

=IFERROR(IFERROR(INDEX(Lista1, MATCH(0, COUNTIF($E$1:E1, Lista1), 0)), INDEX(Lista2, MATCH(0, COUNTIF($E$1:E1, Lista2), 0)) ), "") 

Ana negative e kësaj qasjeje është se formulat e grupeve ngadalësojnë dukshëm punën me skedarin nëse tabelat burimore kanë një numër të madh (disa qindra ose më shumë) elementësh. 

Metoda 3. Query Power

Nëse listat tuaja burimore kanë një numër të madh elementësh, për shembull, disa qindra ose mijëra, atëherë në vend të një formule të ngadaltë të grupit, është më mirë të përdorni një qasje thelbësisht të ndryshme, përkatësisht mjetet shtesë të Power Query. Kjo shtesë është e integruar në Excel 2016 si parazgjedhje. Nëse keni Excel 2010 ose 2013, mund ta shkarkoni dhe instaloni veçmas (falas).

Algoritmi i veprimeve është si më poshtë:

1. Hapni një skedë të veçantë të shtesës së instaluar Kërkesa e energjisë (nëse keni Excel 2010-2013) ose thjesht shkoni te skeda Data (nëse keni Excel 2016).
2. Zgjidhni listën e parë dhe shtypni butonin Nga tabela/gama (Nga diapazoni/Tabela). Kur pyetemi për krijimin e një "tabele të zgjuar" nga lista jonë, ne jemi dakord:

   

3. Hapet dritarja e redaktuesit të pyetjeve, ku mund të shihni të dhënat e ngarkuara dhe emrin e pyetjes Tabela 1 (ju mund ta ndryshoni atë në tuajën nëse dëshironi).
4. Klikoni dy herë në kokën e tabelës (fjalë Listoni 1) dhe riemëroni atë në ndonjë tjetër (për shembull Njerëz). Çfarë saktësisht të emërtoni nuk është e rëndësishme, por emri i shpikur duhet të mbahet mend, sepse. do të duhet të përdoret sërish më vonë gjatë importimit të tabelës së dytë. Bashkimi i dy tabelave në të ardhmen do të funksionojë vetëm nëse titujt e kolonave të tyre përputhen.
5. Zgjero listën rënëse në këndin e sipërm të majtë mbyllni dhe shkarkoni dhe zgjidhni Mbylle dhe ngarko në… (Mbyll&Ngarko te…):

   

6. Në kutinë e dialogut tjetër (mund të duket pak më ndryshe - mos u shqetësoni), zgjidhni Thjesht krijoni një lidhje (Vetëm krijoni lidhje):

   

7. Përsëritim të gjithë procedurën (pikat 2-6) për listën e dytë. Kur riemërtoni një titull kolone, është e rëndësishme të përdorni të njëjtin emër (Njerëzit) si në pyetjen e mëparshme.
8. Në dritaren e Excel në skedën Data ose në skedën Kërkesa e energjisë Zgjedh Merrni të dhëna - Kombinoni kërkesat - Shto (Merr të dhëna - Pyetje për bashkimin - Shtojce):

   

9. Në kutinë e dialogut që shfaqet, zgjidhni kërkesat tona nga listat rënëse:

   

10. Si rezultat, ne do të marrim një pyetje të re, ku dy lista do të lidhen nën njëra-tjetrën. Mbetet për të hequr dublikatat me butonin Fshini rreshtat - Hiqni dublikatat (Fshi rreshtat - Fshi dublikatat):

    

11. Pyetja e përfunduar mund të riemërtohet në anën e djathtë të panelit të opsioneve, duke i dhënë një emër të arsyeshëm (ky do të jetë emri i tabelës së rezultateve në fakt) dhe gjithçka mund të ngarkohet në fletë me komandën mbyllni dhe shkarkoni (Mbyll&Ngarko):

    

Në të ardhmen, me çdo ndryshim apo shtesë në listat origjinale, do të mjaftojë vetëm të klikoni me të djathtën për të përditësuar tabelën e rezultateve.

• Si të mblidhni tabela të shumta nga skedarë të ndryshëm duke përdorur Power Query
• Nxjerrja e artikujve unikë nga një listë
• Si të krahasoni dy lista me njëra-tjetrën për ndeshjet dhe dallimet