Երկու ցուցակների միավորում առանց կրկնօրինակների

Դասական իրավիճակ. դուք ունեք երկու ցուցակ, որոնք պետք է միավորվեն մեկի մեջ: Ավելին, սկզբնական ցուցակներում կարող են լինել և՛ եզակի տարրեր, և՛ համընկնող տարրեր (ինչպես ցուցակների միջև, այնպես էլ ներսում), բայց ելքում դուք պետք է ստանաք ցուցակ առանց կրկնօրինակների (կրկնություններ).

Եկեք ավանդաբար դիտարկենք նման ընդհանուր խնդրի լուծման մի քանի ուղիներ՝ պարզունակ «ճակատի վրա» մինչև ավելի բարդ, բայց էլեգանտ:

Մեթոդ 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, Ցուցակ1), 0)), INDEX(Ցուցակ2, MATCH(0, COUNTIF($E$1:E1, Ցուցակ2), 0)) ), «») 

Այս մոտեցման բացասական կողմն այն է, որ զանգվածի բանաձևերը նկատելիորեն դանդաղեցնում են աշխատանքը ֆայլի հետ, եթե սկզբնաղբյուր աղյուսակներն ունեն մեծ (մի քանի հարյուր և ավելի) թվով տարրեր: 

Մեթոդ 3. Power Query

Եթե ​​ձեր սկզբնաղբյուրների ցուցակներն ունեն մեծ թվով էլեմենտներ, օրինակ՝ մի քանի հարյուր կամ հազար, ապա դանդաղ զանգվածի բանաձևի փոխարեն ավելի լավ է օգտագործել սկզբունքորեն այլ մոտեցում, այն է՝ Power Query հավելյալ գործիքները: Այս հավելումը լռելյայն ներկառուցված է Excel 2016-ում: Եթե ​​ունեք Excel 2010 կամ 2013, կարող եք ներբեռնել և տեղադրել այն առանձին (անվճար):

Գործողությունների ալգորիթմը հետևյալն է.

1. Բացեք տեղադրված հավելման առանձին ներդիր Power հարցում (եթե ունեք Excel 2010-2013) կամ պարզապես գնացեք ներդիր Ամսաթիվ (եթե ունեք Excel 2016):
2. Ընտրեք առաջին ցուցակը և սեղմեք կոճակը Սեղանից / միջակայքից (Տեսականից/Սեղանից). Մեր ցանկից «խելացի աղյուսակ» ստեղծելու մասին հարցին, մենք համաձայն ենք.

   

3. Բացվում է հարցումների խմբագրիչի պատուհանը, որտեղ կարող եք տեսնել բեռնված տվյալները և հարցման անունը Աղյուսակ 1 (եթե ցանկանում եք, կարող եք փոխել այն ձեր սեփականը):
4. Կրկնակի սեղմեք աղյուսակի վերնագրի վրա (բառ Ցանկ 1) և վերանվանեք այն որևէ այլի (օրինակ Մարդիկ) Թե կոնկրետ ինչ անվանել, կարևոր չէ, բայց հորինված անունը պետք է հիշել, քանի որ. այն պետք է նորից օգտագործվի ավելի ուշ՝ երկրորդ աղյուսակը ներմուծելիս: Երկու աղյուսակների միավորումը ապագայում կաշխատի միայն այն դեպքում, եթե դրանց սյունակների վերնագրերը համընկնեն:
5. Ընդարձակեք բացվող ցանկը վերին ձախ անկյունում փակիր և ներբեռնիր եւ ընտրեք Փակեք և բեռնեք… (Փակել և բեռնել…):

   

6. Հաջորդ երկխոսության վանդակում (այն կարող է մի փոքր այլ տեսք ունենալ. մի անհանգստացեք), ընտրեք Պարզապես կապ ստեղծեք (Միայն ստեղծել կապ):

   

7. Երկրորդ ցուցակի համար կրկնում ենք ամբողջ պրոցեդուրան (կետեր 2-6): Սյունակի վերնագիրը վերանվանելիս կարևոր է օգտագործել նույն անունը (Մարդիկ), ինչպես նախորդ հարցումում:
8. Ներդիրի Excel պատուհանում Ամսաթիվ կամ ներդիրի վրա Power հարցում Ընտրել Ստացեք տվյալներ – Միավորել հարցումները – Ավելացնել (Ստացեք տվյալներ — Միաձուլման հարցումներ — Հավելեք):

   

9. Երկխոսության վանդակում, որը երևում է, ընտրեք մեր հարցումները բացվող ցուցակներից.

   

10. Արդյունքում մենք կստանանք նոր հարցում, որտեղ երկու ցուցակներ կմիացվեն միմյանց տակ։ Մնում է կոճակով հեռացնել կրկնօրինակները Ջնջել տողերը – Հեռացնել կրկնօրինակները (Ջնջել տողերը — Ջնջել կրկնօրինակները):

    

11. Ավարտված հարցումը կարող է վերանվանվել ընտրանքների վահանակի աջ կողմում՝ տալով նրան ողջամիտ անուն (սա իրականում կլինի արդյունքների աղյուսակի անունը) և ամեն ինչ կարող է վերբեռնվել թերթիկի վրա հրամանի միջոցով: փակիր և ներբեռնիր (Փակել և բեռնել):

    

Հետագայում սկզբնական ցուցակներում ցանկացած փոփոխության կամ լրացման դեպքում բավական կլինի միայն աջ սեղմել՝ արդյունքների աղյուսակը թարմացնելու համար:

• Ինչպես հավաքել բազմաթիվ աղյուսակներ տարբեր ֆայլերից՝ օգտագործելով Power Query
• Ցանկից եզակի տարրերի դուրսբերում
• Ինչպես համեմատել երկու ցուցակները միմյանց հետ համապատասխանությունների և տարբերությունների համար