Ինչպես համեմատել երկու ցուցակները Excel-ում

Excel-ը տվյալների մշակման արդյունավետ ծրագիր է: Իսկ տեղեկատվության վերլուծության մեթոդներից մեկը երկու ցուցակների համեմատությունն է։ Եթե ​​դուք ճիշտ համեմատեք երկու ցուցակները Excel-ում, ապա այս գործընթացը կազմակերպելը շատ հեշտ կլինի։ Բավական է միայն հետևել որոշ կետերի, որոնք այսօր կքննարկվեն։ Այս մեթոդի գործնական իրականացումը լիովին կախված է տվյալ պահին անձի կամ կազմակերպության կարիքներից: Հետեւաբար, պետք է դիտարկել մի քանի հնարավոր դեպքեր:

Excel-ում երկու ցուցակների համեմատություն

Իհարկե, դուք կարող եք ձեռքով համեմատել երկու ցուցակ: Բայց դա երկար ժամանակ կպահանջի։ Excel-ն ունի իր խելացի գործիքակազմը, որը թույլ կտա համեմատել տվյալները ոչ միայն արագ, այլ նաև ստանալ այնպիսի տեղեկատվություն, որն այնքան էլ հեշտ չէ ստանալ ձեր աչքերով: Ենթադրենք, մենք ունենք A և B կոորդինատներով երկու սյունակ: Որոշ արժեքներ կրկնվում են դրանցում:

Խնդրի ձևակերպում

Այսպիսով, մենք պետք է համեմատենք այս սյունակները: Երկու փաստաթղթերի համեմատման կարգը հետևյալն է.

1. Եթե ​​այս ցուցակներից յուրաքանչյուրի եզակի բջիջները նույնն են, և եզակի բջիջների ընդհանուր թիվը նույնն է, իսկ բջիջները նույնն են, ապա այս ցուցակները կարելի է համարել նույնը: Այս ցուցակի արժեքների դասավորության հերթականությունը այնքան էլ կարևոր չէ:
2. Մենք կարող ենք խոսել ցուցակների մասնակի համընկնման մասին, եթե եզակի արժեքներն իրենք նույնն են, բայց կրկնությունների քանակը տարբեր է: Հետեւաբար, նման ցուցակները կարող են պարունակել տարբեր քանակի տարրեր:
3. Այն փաստը, որ երկու ցուցակները չեն համընկնում, նշվում է եզակի արժեքների տարբեր հավաքածուով:

Այս երեք պայմանները միաժամանակ մեր խնդրի պայմաններն են։

Խնդրի լուծումը

Եկեք ստեղծենք երկու դինամիկ միջակայք, որպեսզի ավելի հեշտ լինի համեմատել ցուցակները: Նրանցից յուրաքանչյուրը կհամապատասխանի ցուցակներից յուրաքանչյուրին։

Երկու ցուցակները համեմատելու համար կատարեք հետևյալը.

1. Առանձին սյունակում մենք ստեղծում ենք եզակի արժեքների ցանկ, որոնք հատուկ են երկու ցուցակներին: Դրա համար մենք օգտագործում ենք բանաձևը. ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Բանաձևն ինքնին պետք է գրվի որպես զանգվածի բանաձև:
2. Եկեք որոշենք, թե յուրաքանչյուր եզակի արժեք քանի անգամ է հայտնվում տվյալների զանգվածում: Ահա դա անելու բանաձևերը. =COUNTIF(List1,D5) և =COUNTI(List2,D5):
3. Եթե ​​և՛ կրկնությունների քանակը, և՛ եզակի արժեքների թիվը նույնն են բոլոր ցուցակներում, որոնք ներառված են այս տիրույթներում, ապա ֆունկցիան վերադարձնում է արժեքը 0: Սա ցույց է տալիս, որ համընկնում է XNUMX%: Այս դեպքում այս ցուցակների վերնագրերը ձեռք կբերեն կանաչ ֆոն:
4. Եթե ​​ամբողջ եզակի բովանդակությունը երկու ցուցակներում է, ապա վերադարձվում է բանաձևերով =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) արժեքը կլինի զրո: Եթե ​​E1-ը զրո չի պարունակում, բայց նման արժեք կա E2 և F2 բջիջներում, ապա այս դեպքում միջակայքերը կճանաչվեն համընկնող, բայց միայն մասամբ: Այս դեպքում համապատասխան ցուցակների վերնագրերը կդառնան նարնջագույն։
5. Եվ եթե վերը նկարագրված բանաձևերից մեկը վերադարձնի ոչ զրոյական արժեք, ապա ցուցակները լիովին անհամապատասխան կլինեն:

Սա այն հարցի պատասխանն է, թե ինչպես կարելի է վերլուծել սյունակները համընկնումների համար՝ օգտագործելով բանաձևերը: Ինչպես տեսնում եք, ֆունկցիաների կիրառմամբ կարող եք իրականացնել գրեթե ցանկացած առաջադրանք, որն առաջին հայացքից կապված չէ մաթեմատիկայի հետ։

Փորձարկման օրինակ

Աղյուսակի մեր տարբերակում վերը նկարագրված յուրաքանչյուր տեսակի ցուցակների երեք տեսակ կա: Ունի մասամբ և ամբողջությամբ համապատասխանող, ինչպես նաև չհամապատասխանող։

Տվյալները համեմատելու համար մենք օգտագործում ենք A5:B19 միջակայքը, որում մենք հերթափոխով տեղադրում ենք այս զույգ ցուցակները: Թե ինչ արդյունք կունենա համեմատությունը, մենք կհասկանանք սկզբնական ցուցակների գույնով։ Եթե ​​դրանք բոլորովին տարբեր են, ապա դա կլինի կարմիր ֆոն: Եթե ​​տվյալների մի մասը նույնն է, ապա դեղին: Ամբողջական ինքնության դեպքում համապատասխան վերնագրերը կլինեն կանաչ: Ինչպե՞ս գույն պատրաստել՝ կախված նրանից, թե որն է արդյունքը: Սա պահանջում է պայմանական ձևաչափում:

Երկու ցուցակներում տարբերություններ գտնել երկու եղանակով

Եկեք նկարագրենք տարբերություններ գտնելու ևս երկու եղանակ՝ կախված ցուցակների համաժամանակա՞ն լինելուց, թե՞ ոչ։

Տարբերակ 1. Սինխրոն ցուցակներ

Սա հեշտ տարբերակ է։ Ենթադրենք, մենք ունենք նման ցուցակներ.

Որոշելու համար, թե քանի անգամ արժեքները չեն համընկնում, կարող եք օգտագործել բանաձևը. =SUMPRODUCT(—(A2:A20<>B2:B20)): Եթե ​​արդյունքում ստացանք 0, դա նշանակում է, որ երկու ցուցակները նույնն են։

Տարբերակ 2. Խառը ցուցակներ

Եթե ​​ցուցակները նույնական չեն իրենց պարունակած օբյեկտների հերթականությամբ, դուք պետք է կիրառեք այնպիսի հատկանիշ, ինչպիսին է պայմանական ձևաչափումը և կրկնվող արժեքների գունավորումը: Կամ օգտագործեք գործառույթը COUNTIF, որի միջոցով մենք որոշում ենք, թե քանի անգամ է մեկ ցուցակի տարրը հայտնվում երկրորդում:

Ինչպես համեմատել 2 սյունակ տող առ տող

Երբ մենք համեմատում ենք երկու սյունակ, մենք հաճախ պետք է համեմատենք տեղեկատվությունը, որը գտնվում է տարբեր տողերում: Դա անելու համար մեզ կօգնի օպերատորը ԵԹԵ. Եկեք նայենք, թե ինչպես է այն աշխատում գործնականում: Դա անելու համար ներկայացնում ենք մի քանի պատկերավոր իրավիճակներ։

Օրինակ. Ինչպես համեմատել 2 սյունակները մեկ տողում համընկնումների և տարբերությունների համար

Վերլուծելու համար, թե արդյոք նույն շարքում գտնվող, բայց տարբեր սյունակներում գտնվող արժեքները նույնն են, մենք գրում ենք գործառույթը IF. Բանաձևը տեղադրվում է օժանդակ սյունակում տեղադրված յուրաքանչյուր տողում, որտեղ կցուցադրվեն տվյալների մշակման արդյունքները: Բայց ամենևին էլ անհրաժեշտ չէ այն սահմանել յուրաքանչյուր տողում, պարզապես պատճենեք այն այս սյունակի մնացած բջիջներում կամ օգտագործեք ավտոմատ լրացման նշիչը:

Մենք պետք է գրենք նման բանաձև՝ հասկանալու համար, թե երկու սյունակներում էլ արժեքները նույնն են, թե ոչ. =IF(A2=B2, «Համապատասխան», «»): Այս ֆունկցիայի տրամաբանությունը շատ պարզ է. այն համեմատում է A2 և B2 բջիջների արժեքները, և եթե դրանք նույնն են, ապա ցուցադրում է «Համընկնել» արժեքը: Եթե ​​տվյալները տարբեր են, այն ոչ մի արժեք չի վերադարձնում: Կարող եք նաև ստուգել բջիջները՝ տեսնելու, թե արդյոք դրանց միջև համընկնում կա: Այս դեպքում օգտագործվում է բանաձևը. =IF(A2<>B2, «Չի համապատասխանում», «»): Սկզբունքը նույնն է, նախ ստուգումն իրականացվում է։ Եթե ​​պարզվում է, որ բջիջները համապատասխանում են չափանիշին, ապա ցուցադրվում է «Չի համընկնում» արժեքը:

Հնարավոր է նաև բանաձևի դաշտում օգտագործել հետևյալ բանաձևը՝ «Համապատասխանություն», եթե արժեքները նույնն են, և «Չհամընկնել», եթե դրանք տարբեր են. =IF(A2=B2; «Համապատասխանում», «Չհամընկնել»): Դուք կարող եք նաև օգտագործել անհավասարության օպերատորը հավասարության օպերատորի փոխարեն: Միայն արժեքների հերթականությունը, որը կցուցադրվի այս դեպքում, մի փոքր տարբեր կլինի. =IF(A2<>B2, «Չհամընկնել», «Համընկնել»): Բանաձևի առաջին տարբերակն օգտագործելուց հետո արդյունքը կլինի հետևյալը.

Բանաձևի այս փոփոխությունը մեծատառերի զգայուն չէ: Հետևաբար, եթե մեկ սյունակի արժեքները տարբերվում են մյուսներից միայն նրանով, որ դրանք գրված են մեծատառերով, ապա ծրագիրը չի նկատի այս տարբերությունը: Համեմատությունը մեծատառերի նկատմամբ զգայուն դարձնելու համար դուք պետք է օգտագործեք գործառույթը չափորոշիչների մեջ EXACT- ը. Մնացած փաստարկները մնացել են անփոփոխ. =IF (EXACT(A2,B2), «Համապատասխան», «Յուրահատուկ»):

Ինչպես համեմատել բազմաթիվ սյունակներ մեկ տողում համընկնումների համար

Հնարավոր է վերլուծել արժեքները ցուցակներում ըստ չափանիշների մի ամբողջ շարքի.

1. Գտեք այն տողերը, որոնք ամենուր ունեն նույն արժեքները:
2. Գտեք այն տողերը, որտեղ համընկնում են ընդամենը երկու ցուցակում:

Դիտարկենք մի քանի օրինակ, թե ինչպես վարվել այս դեպքերից յուրաքանչյուրում:

Օրինակ. Ինչպես գտնել համընկնումներ մեկ տողում աղյուսակի մի քանի սյունակներում

Ենթադրենք, մենք ունենք մի շարք սյունակներ, որոնք պարունակում են մեզ անհրաժեշտ տեղեկատվությունը: Մեզ առջեւ խնդիր է դրված որոշել այն տողերը, որոնցում արժեքները նույնն են: Դա անելու համար հարկավոր է օգտագործել հետևյալ բանաձևը. =IF(AND(A2=B2,A2=C2), «համապատասխանում», «»):

Եթե ​​աղյուսակում շատ սյունակներ կան, ապա պարզապես անհրաժեշտ է օգտագործել այն ֆունկցիայի հետ միասին IF օպերատոր COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"համապատասխանել";""): Այս բանաձևում օգտագործված թիվը ցույց է տալիս ստուգման ենթակա սյունակների քանակը: Եթե ​​դա տարբերվում է, ապա դուք պետք է գրեք այնքան, որքան ճիշտ է ձեր իրավիճակի համար:

Օրինակ. Ինչպես գտնել համընկնումներ աղյուսակի ցանկացած 2 սյունակում մեկ տողում

Ենթադրենք, մենք պետք է ստուգենք, թե արդյոք մեկ տողի արժեքները համընկնում են աղյուսակի երկու սյունակում: Դա անելու համար անհրաժեշտ է օգտագործել ֆունկցիան որպես պայման OR, որտեղ հերթափոխով գրեք սյունակներից յուրաքանչյուրի հավասարությունը մյուսին: Ահա մի օրինակ.

Մենք օգտագործում ենք այս բանաձևը. =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Կարող է լինել իրավիճակ, երբ աղյուսակում շատ սյունակներ կան: Այս դեպքում բանաձևը հսկայական կլինի, և բոլոր անհրաժեշտ համակցությունները ընտրելու համար կարող է շատ ժամանակ պահանջվել: Այս խնդիրը լուծելու համար անհրաժեշտ է օգտագործել ֆունկցիան COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; «Եզակի տող»; «Ոչ եզակի տող»)

Մենք տեսնում ենք, որ ընդհանուր առմամբ մենք ունենք երկու գործառույթ COUNTIF. Առաջինի հետ հերթափոխով որոշում ենք, թե քանի սյունակ ունի նմանություն A2-ին, իսկ երկրորդի հետ՝ B2 արժեքով ստուգում ենք նմանությունների քանակը։ Եթե ​​այս բանաձևով հաշվարկի արդյունքում մենք ստանում ենք զրոյական արժեք, դա ցույց է տալիս, որ այս սյունակի բոլոր տողերը եզակի են, եթե ավելի շատ, կան նմանություններ: Հետևաբար, եթե երկու բանաձևով հաշվարկելու և վերջնական արդյունքներն ավելացնելու արդյունքում ստանում ենք զրոյական արժեք, ապա վերադարձվում է «Յուրահատուկ տող» տեքստային արժեքը, եթե այս թիվը ավելի մեծ է, գրվում է, որ այս տողը եզակի չէ։

Ինչպես համեմատել 2 սյունակ Excel-ում համընկնումների համար

Հիմա օրինակ բերենք. Ենթադրենք, մենք ունենք երկու սյունակ ունեցող աղյուսակ: Դուք պետք է ստուգեք, թե արդյոք դրանք համապատասխանում են: Դա անելու համար անհրաժեշտ է կիրառել բանաձեւը, որտեղ կօգտագործվի նաեւ ֆունկցիան IF, և օպերատորը COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, «B սյունակում համընկնումներ չկան», «B սյունակում համընկնումներ կան»)

Ոչ մի հետագա գործողություն չի պահանջվում: Այս բանաձևով արդյունքը հաշվարկելուց հետո մենք ստանում ենք ֆունկցիայի երրորդ արգումենտի արժեքը IF լուցկի. Եթե ​​չկան, ապա երկրորդ փաստարկի բովանդակությունը:

Ինչպես համեմատել 2 սյունակ Excel-ում համընկնումների համար և ընդգծել գույնի հետ

Որպեսզի ավելի հեշտ լինի տեսողականորեն նույնականացնել համապատասխան սյունակները, կարող եք դրանք ընդգծել գույնով: Դա անելու համար անհրաժեշտ է օգտագործել «Պայմանական ձևաչափում» գործառույթը: Եկեք տեսնենք գործնականում.

Գտնել և ընդգծել համընկնումները ըստ գույնի բազմաթիվ սյունակներում

Համընկնումները որոշելու և դրանք ընդգծելու համար նախ պետք է ընտրեք տվյալների տիրույթը, որում կիրականացվի ստուգումը, այնուհետև բացեք «Պայմանական ձևաչափում» տարրը «Տուն» ներդիրում: Այնտեղ, որպես բջիջների ընտրության կանոն, ընտրեք «Կրկնօրինակ արժեքներ»:

Դրանից հետո կհայտնվի նոր երկխոսության տուփ, որում ձախ բացվող ցուցակում մենք գտնում ենք «Կրկնվող» տարբերակը, իսկ աջ ցուցակում ընտրում ենք այն գույնը, որը կօգտագործվի ընտրության համար: «OK» կոճակը սեղմելուց հետո կընտրվի բոլոր նմանություններ ունեցող բջիջների ֆոնը: Այնուհետև պարզապես համեմատեք սյունակները աչքով:

Գտնել և ընդգծել համապատասխան գծերը

Ստուգելու տեխնիկան, թե արդյոք տողերը համընկնում են, մի փոքր այլ է: Նախ, մենք պետք է ստեղծենք լրացուցիչ սյունակ, և այնտեղ մենք կօգտագործենք համակցված արժեքները՝ օգտագործելով & օպերատորը: Դա անելու համար հարկավոր է գրել ձևի բանաձև. =A2&B2&C2&D2.

Մենք ընտրում ենք այն սյունակը, որը ստեղծվել է և պարունակում է համակցված արժեքներ: Հաջորդը, մենք կատարում ենք գործողությունների նույն հաջորդականությունը, որը վերը նկարագրված է սյունակների համար: Կրկնվող տողերը կնշվեն ձեր նշած գույնով:

Մենք տեսնում ենք, որ կրկնություններ փնտրելու մեջ դժվար բան չկա։ Excel-ը պարունակում է դրա համար անհրաժեշտ բոլոր գործիքները: Այս ամբողջ գիտելիքը գործնականում կիրառելուց առաջ կարևոր է պարզապես զբաղվել: