Divu sarakstu sapludināšana bez dublikātiem

Klasiska situācija: jums ir divi saraksti, kas ir jāapvieno vienā. Turklāt sākotnējos sarakstos var būt gan unikāli elementi, gan atbilstoši (gan starp sarakstiem, gan iekšā), bet izejā jāiegūst saraksts bez dublikātiem (atkārtojumiem):

Tradicionāli apskatīsim vairākus veidus, kā atrisināt tik bieži sastopamu problēmu – no primitīvas “uz pieres” līdz sarežģītākai, bet elegantai.

1. metode: noņemiet dublikātus

Problēmu var atrisināt visvienkāršākajā veidā – manuāli kopēt abu sarakstu elementus vienā un pēc tam lietot rīku iegūtajai kopai. Noņemt dublikātus no cilnes Datums (Dati — noņemt dublikātus):

Protams, šī metode nedarbosies, ja bieži mainās dati avotu sarakstos – pēc katras izmaiņas būs jāatkārto visa procedūra vēlreiz. 

1.a metode. rakurstabula

Šī metode faktiski ir loģisks iepriekšējās turpinājums. Ja saraksti nav ļoti lieli un maksimālais tajos esošo elementu skaits ir zināms iepriekš (piemēram, ne vairāk kā 10), tad varat apvienot divas tabulas vienā ar tiešajām saitēm, pievienot kolonnu ar labajām un izveidojiet kopsavilkuma tabulu, pamatojoties uz iegūto tabulu:

Kā jūs zināt, rakurstabula ignorē atkārtojumus, tāpēc izejā mēs iegūsim kombinētu sarakstu bez dublikātiem. Papildu kolonna ar 1 ir nepieciešama tikai tāpēc, ka Excel var izveidot kopsavilkuma tabulas, kurās ir vismaz divas kolonnas.

Mainot sākotnējos sarakstus, jaunie dati nokļūs kombinētajā tabulā, izmantojot tiešas saites, bet rakurstabula būs jāatjaunina manuāli (noklikšķiniet ar peles labo pogu - Atjaunināt un saglabāt). Ja jums nav nepieciešams pārrēķins lidojumā, tad labāk izmantot citas iespējas.

2. metode: masīva formula

Problēmu var atrisināt ar formulām. Šajā gadījumā rezultātu pārrēķins un atjaunināšana notiks automātiski un uzreiz, uzreiz pēc izmaiņām sākotnējos sarakstos. Ērtības un īsuma labad piešķirsim saviem sarakstiem nosaukumus. Saraksts 1 и Saraksts 2izmantojot Nosaukuma vadītājs tab formula (Formulas — nosaukumu pārvaldnieks — izveide):

Pēc nosaukuma piešķiršanas mums nepieciešamā formula izskatīsies šādi:

No pirmā acu uzmetiena tas izskatās rāpojoši, bet patiesībā viss nav tik biedējoši. Ļaujiet man izvērst šo formulu vairākās rindās, izmantojot taustiņu kombināciju Alt+Enter un atkāpi ar atstarpēm, kā mēs to darījām, piemēram, šeit:

Loģika šeit ir šāda:

• Formula INDEX(Saraksts1;MATCH(0;COUNTIF($E$1:E1;List1); 0) atlasa visus unikālos elementus no pirmā saraksta. Tiklīdz tie beidzas, tas sāk rādīt #N/A kļūdu:

  

• Formula INDEX(Saraksts2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) tādā pašā veidā izņem unikālos elementus no otrā saraksta.
• Viena otrā ligzdotas divas IFERROR funkcijas vispirms realizē unikālo izvadi no saraksta-1 un pēc tam vienu pēc otras no saraksta-2.

Ņemiet vērā, ka šī ir masīva formula, proti, pēc ierakstīšanas tā jāievada šūnā, kas nav parasta ienākt, bet ar īsinājumtaustiņu Ctrl+maiņa+ienākt un pēc tam kopējiet (velciet) uz leju uz bērnu šūnām ar piemali.

Programmas Excel angļu valodas versijā šī formula izskatās šādi:

=IFERROR(IFERROR(INDEKSS(saraksts1, ATbilstība(0, SKAITS($E$1:E1, saraksts1), 0)), INDEKSS(saraksts2, ATbilstība(0, SKAITS($E$1:E1, Saraksts2), 0)) ), “) 

Šīs pieejas negatīvā puse ir tāda, ka masīvu formulas ievērojami palēnina darbu ar failu, ja avota tabulās ir liels (vairāki simti vai vairāk) elementu skaits. 

3. metode. Power Query

Ja jūsu avotu sarakstos ir liels elementu skaits, piemēram, vairāki simti vai tūkstoši, tad lēnas masīva formulas vietā labāk izmantot principiāli atšķirīgu pieeju, proti, Power Query pievienojumprogrammas rīkus. Šī pievienojumprogramma pēc noklusējuma ir iebūvēta programmā Excel 2016. Ja jums ir Excel 2010 vai 2013, varat to lejupielādēt un instalēt atsevišķi (bez maksas).

Darbību algoritms ir šāds:

1. Atveriet atsevišķu instalētā papildinājuma cilni Jaudas vaicājums (ja jums ir Excel 2010–2013) vai vienkārši atveriet cilni Datums (ja jums ir Excel 2016).
2. Izvēlieties pirmo sarakstu un nospiediet pogu No tabulas/diapazona (No diapazona/tabulas). Jautāti par “viedā galda” izveidi no mūsu saraksta, mēs piekrītam:

   

3. Atveras vaicājumu redaktora logs, kurā var redzēt ielādētos datus un vaicājuma nosaukumu Tabula 1 (ja vēlaties, varat to mainīt uz savu).
4. Veiciet dubultklikšķi uz tabulas galvenes (word Saraksts 1) un pārdēvējiet to par jebkuru citu (piemēram, cilvēki). Ko tieši nosaukt, nav svarīgi, bet izdomātais nosaukums ir jāatceras, jo. vēlāk, importējot otro tabulu, tas būs jāizmanto vēlreiz. Divu tabulu sapludināšana nākotnē darbosies tikai tad, ja to kolonnu virsraksti sakritīs.
5. Izvērsiet nolaižamo sarakstu augšējā kreisajā stūrī aizveriet un lejupielādējiet Un izvēlies Aizveriet un ielādējiet… (Aizvērt&ielādēt uz...):

   

6. Nākamajā dialoglodziņā (tas var izskatīties nedaudz savādāk — nebaidieties) atlasiet Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu):

   

7. Mēs atkārtojam visu procedūru (2.-6. punkts) otrajam sarakstam. Pārdēvējot kolonnas virsrakstu, ir svarīgi izmantot to pašu nosaukumu (People) kā iepriekšējā vaicājumā.
8. Excel logā cilnē Datums vai cilnē Jaudas vaicājums izvēlēties Iegūt datus – apvienot pieprasījumus – pievienot (Iegūt datus — sapludināt vaicājumus — pievienot):

   

9. Parādītajā dialoglodziņā nolaižamajos sarakstos atlasiet mūsu pieprasījumus:

   

10. Rezultātā mēs iegūsim jaunu vaicājumu, kur divi saraksti tiks savienoti viens zem otra. Atliek noņemt dublikātus ar pogu Dzēst rindas — noņemiet dublikātus (Dzēst rindas — dzēst dublikātus):

    

11. Gatavo vaicājumu var pārdēvēt opciju paneļa labajā pusē, piešķirot tam saprātīgu nosaukumu (tāds patiesībā būs rezultātu tabulas nosaukums) un visu var augšupielādēt lapā ar komandu aizveriet un lejupielādējiet (Aizvērt un ielādēt):

    

Nākotnē, veicot izmaiņas vai papildinājumus sākotnējos sarakstos, pietiks ar peles labo pogu noklikšķināt, lai atjauninātu rezultātu tabulu.

• Kā savākt vairākas tabulas no dažādiem failiem, izmantojot Power Query
• Unikālo vienumu izvilkšana no saraksta
• Kā salīdzināt divus sarakstus, lai noteiktu atbilstību un atšķirības