Dviejų sąrašų sujungimas be dublikatų

Klasikinė situacija: turite du sąrašus, kuriuos reikia sujungti į vieną. Be to, pradiniuose sąrašuose gali būti ir unikalių elementų, ir atitinkančių (tiek tarp sąrašų, tiek viduje), tačiau išvestyje reikia gauti sąrašą be dublikatų (pakartojimų):

Tradiciškai pažvelkime į kelis tokios dažnos problemos sprendimo būdus – nuo ​​primityvaus „ant kaktos“ iki sudėtingesnių, bet elegantiškų.

1 būdas: pašalinkite dublikatus

Problemą galite išspręsti paprasčiausiu būdu – rankiniu būdu nukopijuokite abiejų sąrašų elementus į vieną ir tada pritaikykite įrankį gautam rinkiniui. Pašalinti dublikatus iš skirtuko Duomenys (Duomenys – pašalinti dublikatus):

Žinoma, šis būdas neveiks, jei šaltinių sąrašuose esantys duomenys dažnai keisis – po kiekvieno pakeitimo teks kartoti visą procedūrą dar kartą. 

1a metodas. suvestinės lentelės

Šis metodas iš tikrųjų yra logiškas ankstesnio tęsinys. Jei sąrašai nėra labai dideli ir iš anksto žinomas maksimalus elementų skaičius juose (pavyzdžiui, ne daugiau kaip 10), tuomet galite sujungti dvi lenteles į vieną tiesioginėmis nuorodomis, pridėti stulpelį su vienais dešinėje ir remdamiesi gauta lentele sukurkite suvestinę lentelę:

Kaip žinote, suvestinė lentelė nepaiso pasikartojimų, todėl išvestyje gausime kombinuotą sąrašą be dublikatų. Pagalbinis stulpelis su 1 reikalingas tik todėl, kad „Excel“ gali sudaryti suvestinės lenteles, kuriose yra bent du stulpeliai.

Pakeitus pirminius sąrašus, nauji duomenys per tiesiogines nuorodas pateks į kombinuotą lentelę, tačiau suvestinę lentelę teks atnaujinti rankiniu būdu (dešiniuoju pelės mygtuku spustelėkite – Atnaujinkite ir išsaugokite). Jei jums nereikia perskaičiuoti skrydžio metu, geriau naudoti kitas parinktis.

2 būdas: masyvo formulė

Galite išspręsti problemą formulėmis. Tokiu atveju rezultatų perskaičiavimas ir atnaujinimas įvyks automatiškai ir akimirksniu, iškart pasikeitus pirminiuose sąrašuose. Kad būtų patogiau ir trumpai, suteikime savo sąrašams pavadinimus. 1 sąrašas и 2 sąrašasnaudojant Vardų tvarkytuvė kortelė formulė (Formulės – Vardų tvarkyklė – Kurti):

Suteikus pavadinimą, mums reikalinga formulė atrodys taip:

Iš pirmo žvilgsnio tai atrodo baisu, bet iš tikrųjų viskas nėra taip baisu. Leiskite išplėsti šią formulę keliomis eilutėmis, naudojant klavišų kombinaciją Alt + Enter ir įtrauką su tarpais, kaip tai padarėme, pavyzdžiui, čia:

Logika čia yra tokia:

• Formulė INDEX(Sąrašas1;MATCH(0;COUNTIF($E$1:E1;List1); 0) parenka visus unikalius elementus iš pirmojo sąrašo. Kai tik jie baigiasi, pradedama rodyti klaidą #N/A:

  

• Formulė INDEX(Sąrašas2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) tuo pačiu būdu ištraukia unikalius elementus iš antrojo sąrašo.
• Dvi IFERROR funkcijos, įdėtos viena į kitą, pirmiausia įgyvendina unikalių išvestį iš sąrašo-1, o po to vieną po kitos iš sąrašo-2.

Atkreipkite dėmesį, kad tai yra masyvo formulė, ty po įvedimo ji turi būti įvesta į ląstelę, kuri nėra įprasta įeiti, bet su sparčiuoju klavišu "Ctrl"+perėjimas+įeiti ir tada nukopijuokite (vilkite) žemyn į antrinius langelius su parašte.

Angliškoje „Excel“ versijoje ši formulė atrodo taip:

=IFERROR(IFERROR(INDEX(Sąrašas1, ATITIKTIS(0, COUNTIF($E$1:E1, Sąrašas1), 0)), INDEX(Sąrašas2, ATITIKTIS(0, COUNTIF($E$1:E1, Sąrašas2), 0)) ), “) 

Šio metodo trūkumas yra tas, kad masyvo formulės pastebimai sulėtina darbą su failu, jei šaltinio lentelėse yra daug (kelis šimtus ar daugiau) elementų. 

3 metodas. Power Query

Jei jūsų šaltinių sąrašuose yra daug elementų, pavyzdžiui, keli šimtai ar tūkstančiai, tada vietoj lėtos masyvo formulės geriau naudoti iš esmės skirtingą metodą, būtent „Power Query“ priedų įrankius. Šis priedas pagal numatytuosius nustatymus yra integruotas į „Excel 2016“. Jei turite Excel 2010 arba 2013, galite atsisiųsti ir įdiegti ją atskirai (nemokamai).

Veiksmų algoritmas yra toks:

1. Atidarykite atskirą įdiegto priedo skirtuką „Power Query“ (jei turite Excel 2010–2013) arba tiesiog eikite į skirtuką Duomenys (jei turite Excel 2016).
2. Pasirinkite pirmąjį sąrašą ir paspauskite mygtuką Iš lentelės/diapazono (Iš diapazono / lentelės). Paklausti, kaip sukurti „protingą lentelę“ iš mūsų sąrašo, sutinkame:

   

3. Atsidaro užklausų rengyklės langas, kuriame matysite įkeltus duomenis ir užklausos pavadinimą Lentelė 1 (jei norite, galite pakeisti į savo).
4. Dukart spustelėkite lentelės antraštę (žodis 1 sąrašas) ir pervardykite jį į bet kurį kitą (pavyzdžiui žmonės). Ką tiksliai pavadinti nėra svarbu, bet sugalvotą pavadinimą reikia atsiminti, nes. vėliau, importuojant antrąją lentelę, ją reikės naudoti dar kartą. Dviejų lentelių sujungimas ateityje veiks tik tuo atveju, jei jų stulpelių antraštės atitiks.
5. Išskleiskite išskleidžiamąjį sąrašą viršutiniame kairiajame kampe uždaryti ir atsisiųsti Ir pasirinkti Uždarykite ir įkelkite… (Uždaryti ir įkelti į…):

   

6. Kitame dialogo lange (jis gali atrodyti šiek tiek kitaip – ​​neišsigąskite) pasirinkite Tiesiog sukurkite ryšį (Tik sukurti ryšį):

   

7. Pakartojame visą procedūrą (2-6 taškai) antrajam sąrašui. Pervardijant stulpelio antraštę, svarbu naudoti tą patį pavadinimą (Žmonės), kaip ir ankstesnėje užklausoje.
8. Skirtuke esančiame „Excel“ lange Duomenys arba skirtuke „Power Query“ Pasirinkti Gauti duomenis – sujungti užklausas – pridėti (Gauti duomenis – sujungti užklausas – pridėti):

   

9. Pasirodžiusiame dialogo lange išskleidžiamajame sąraše pasirinkite mūsų užklausas:

   

10. Dėl to gausime naują užklausą, kurioje du sąrašai bus sujungti vienas po kito. Belieka pašalinti dublikatus mygtuku Ištrinti eilutes – pašalinti dublikatus (Ištrinti eilutes – ištrinti dublikatus):

    

11. Užbaigtą užklausą galima pervardyti dešinėje parinkčių skydelio pusėje, suteikiant jai protingą pavadinimą (iš tikrųjų tai bus rezultatų lentelės pavadinimas) ir viską galima įkelti į lapą su komanda uždaryti ir atsisiųsti (Uždaryti ir įkelti):

    

Ateityje atlikus bet kokius pradinių sąrašų pakeitimus ar papildymus, užteks tik dešiniuoju pelės mygtuku spustelėti, kad atnaujintumėte rezultatų lentelę.

• Kaip surinkti kelias lenteles iš skirtingų failų naudojant „Power Query“.
• Unikalių elementų ištraukimas iš sąrašo
• Kaip palyginti du sąrašus dėl atitikčių ir skirtumų