Excel-ում տվյալների համեմատման բանաձև. Ինչպես համեմատել երկու սյունակ Excel-ում - Excel տվյալների համեմատության մեթոդներ

Համեմատենք գրեթե նույն կառուցվածքով երկու աղյուսակ։ Աղյուսակները տարբերվում են առանձին տողերի արժեքներով.

Թող դա լինի սավանների վրա հունվարԵվ փետրվարՀամապատասխան հաշիվների համար առկա է շրջանառության երկու աղյուսակ:

Ինչպես երևում է նկարներից, աղյուսակները տարբերվում են.

1. Տողերի (հաշվի անվանումների) առկայությունը (բացակայությունը): Օրինակ, թերթիկի վրա գտնվող աղյուսակում հունվար 26-ը չկա (տես օրինակ ֆայլը), իսկ թերթիկի աղյուսակում փետրվար 10 հաշիվը և դրա ենթահաշիվները բացակայում են:
2. Տարբեր արժեքներ տողերում: Օրինակ՝ 57 հաշվի համաձայն հունվար-փետրվար ամիսների շրջանառությունը չի համընկնում։

Եթե ​​աղյուսակի կառուցվածքները մոտավորապես նույնն են (հաշվի անունների (տողերի) մեծ մասը նույնն են, սյունակների թիվը և անունները նույնն են), ապա կարող եք համեմատել երկու աղյուսակները: Համեմատություն անենք երկու եղանակով՝ մեկը ավելի հեշտ է իրականացնել, մյուսը՝ ավելի տեսողական։

Պարզ տարբերակ 2 աղյուսակների համեմատության համար

Նախ, եկեք որոշենք, թե որ տողերը (հաշվի անունները) կան մի աղյուսակում, բայց ոչ մյուսում: Այնուհետև, աղյուսակում, որտեղ ավելի քիչ տողեր են բացակայում (առավել ամբողջական աղյուսակը), մենք կցուցադրենք համեմատական ​​հաշվետվություն, որը ներկայացնում է տարբերությունն ըստ սյունակների (հունվար և փետրվար ամիսների շրջանառության տարբերությունը):

Այս մոտեցման հիմնական թերությունն այն է, որ աղյուսակի համեմատության հաշվետվությունը չի ներառում տողեր, որոնք բացակայում են առավել ամբողջական աղյուսակից: Օրինակ՝ մեր դիտարկած դեպքում ամենաշատը ամբողջական սեղանաղյուսակ է աշխատանքային թերթիկի վրա հունվար,որում բացակայում է փետրվարյան աղյուսակից 26 հաշիվը։

Որոշելու համար, թե երկու աղյուսակներից որն է առավել ամբողջական, պետք է պատասխանել 2 հարցի. Փետրվարյան աղյուսակի ո՞ր հաշիվներն են բացակայում հունվարյան աղյուսակում: և հունվարյան աղյուսակի ո՞ր հաշիվներն են բացակայում հունվարյան աղյուսակում:

Դա կարելի է անել բանաձևերի միջոցով (տես սյունակ E): = IF(END(VLOOKUP(A7,հունվար!$A$7:$A$81,1,0));"Ոչ","Այո")և = IF(END(VLOOKUP(A7,Փետրվար!$A$7:$A$77,1,0));"Ոչ","Այո")

Մենք համեմատելու ենք հաշիվների շրջանառությունը՝ օգտագործելով բանաձևերը՝ = IF(END(VLOOKUP($A7, փետրվար!$A$7:$C77,2,0)),0,VLOOKUP($A7,փետրվար!$A$7:$C77,2,0))-B7և = IF(END(VLOOKUP($A7, փետրվար!$A$7:$C77,3,0)),0,VLOOKUP($A7,փետրվար!$A$7:$C77,3,0))-C7

Եթե ​​համապատասխան տող չկա, VLOOKUP() ֆունկցիան վերադարձնում է #N/A սխալը, որը մշակվում է END() և IF() ֆունկցիաների համակցությամբ՝ սխալը փոխարինելով 0-ով (եթե տողը բացակայում է) կամ համապատասխան սյունակի արժեքով:

Դուք կարող եք սա օգտագործել անհամապատասխանություններն ընդգծելու համար (օրինակ՝ կարմիրով):

2 աղյուսակների համեմատման ավելի տեսողական տարբերակ (բայց ավելի բարդ)

Հոդվածում լուծված խնդրին անալոգիայով դուք կարող եք ստեղծել հաշիվների անունների ցուցակ, ներառյալ ԲՈԼՈՐ հաշիվների անունները երկու աղյուսակներից (առանց կրկնությունների): Այնուհետև ցուցադրեք տարբերությունն ըստ սյունակի:

Դա անելու համար ձեզ հարկավոր է.

1. Օգտագործելով = IFERROR(IFERROR(INDEX(հունվար, MATCH(0,COUNTIF(A$4:$A4,հունվար),0)), INDEX(փետրվար,MATCH(0,COUNTIF(A$4:$A4,փետրվար),0))) ;"")Ա սյունակի երկու աղյուսակներից ստեղծել հաշիվների ցուցակ (առանց կրկնությունների);
2. Օգտագործելով = IFERROR(INDEX(Ցանկ, MATCH(SMALL(COUNTIF(Ցուցակ, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , որտեղ List-ը երկու աղյուսակներից (սյունակ Ա) հաշիվների ցանկն է, նախորդ փուլում ստացված հաշիվները.
3. Օգտագործելով բանաձևը = IF(END(VLOOKUP($B5,հունվար!$A$7:$C$81,2,0)),0,VLOOKUP($B5,հունվար!$A$7:$C$81,2,0)) - IF( UND(VLOOKUP($B5, փետրվար!$A$7:$C$77,2,0)),0,VLOOKUP($B5,փետրվար!$A$7:$C$77,2,0))համեմատել հաշվի շրջանառությունը;
4. Օգտագործեք գույնը, որպեսզի ընդգծեք անհամապատասխանությունները, ինչպես նաև ընդգծեք միայն մեկ աղյուսակում հայտնաբերված հաշիվները (օրինակ, վերևի նկարում միայն հունվարյան աղյուսակում պարունակվող հաշիվները ընդգծված են կապույտով, իսկ միայն փետրվարյան աղյուսակի հաշիվները՝ դեղինով):

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

Նկ. Նկար 164.1-ը ցույց է տալիս անունների երկու բազմասյուն ցուցակների օրինակ: Պայմանական ձևաչափման օգտագործումը կարող է ակնհայտ դարձնել ցուցակների տարբերությունները: Ցուցակի այս օրինակները պարունակում են տեքստ, սակայն խնդրո առարկա մեթոդն աշխատում է նաև թվային տվյալների հետ:

Առաջին ցանկը A2:B31 է, այս միջակայքը կոչվում է Հին Ցուցակ. Երկրորդ ցուցակը D2:E31 է, միջակայքը կոչվում է Նոր Ցուցակ. Շրջանակները անվանվել են հրամանի միջոցով Բանաձևեր Սահմանված անուններ Նշեք անուն. Պարտադիր չէ անվանել միջակայքերը, բայց դա հեշտացնում է նրանց հետ աշխատելը։

Սկսենք հին ցուցակին պայմանական ձևաչափում ավելացնելով:

1. Ընտրեք բջիջները տիրույթում Հին Ցուցակ.
2. Ընտրել.
3. Պատուհանում Ստեղծեք ձևաչափման կանոնընտրեք կոչվող տարրը Օգտագործեք բանաձև
4. Մուտքագրեք այս բանաձևը պատուհանի դաշտում (նկ. 164.2): =COUNTIF(NewList;A2)=0:
5. Սեղմեք կոճակը Ձևաչափև նշեք ձևաչափումը, որը կկիրառվի, երբ պայմանը ճիշտ է: Ավելի լավ է ընտրել լցոնման տարբեր գույներ:
6. Սեղմեք OK:

Բջիջները տիրույթում Նոր Ցուցակօգտագործեք նմանատիպ պայմանական ձևաչափման բանաձև:

1. Ընտրեք բջիջները տիրույթում Նոր Ցուցակ.
2. Ընտրել Գլխավոր Պայմանական ֆորմատավորում Ստեղծեք կանոներկխոսության տուփ բացելու համար Ստեղծեք ձևաչափման կանոն.
3. Պատուհանում Ստեղծեք կանոնֆորմատավորում ընտրեք տարրը Օգտագործեք բանաձևֆորմատավորվող բջիջները սահմանելու համար:
4. Մուտքագրեք այս բանաձևը պատուհանի դաշտում՝ =COUNTIF(OldList;D2)=0:
5. Սեղմեք կոճակը Ձևաչափև սահմանեք ձևաչափումը, որը պետք է կիրառվի, երբ պայմանը ճիշտ է (տարբեր լրացման գույն):
6. Սեղմեք OK:

Արդյունքում կընդգծվեն այն անունները, որոնք կան հին ցանկում, բայց ոչ նորում (նկ. 164.3): Բացի այդ, նոր ցուցակի անունները, որոնք հին ցուցակում չեն, նույնպես ընդգծված են, բայց այլ գույնով: Երկու ցուցակներում հայտնված անունները ընդգծված չեն:

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

Շատ հաճախ Excel-ի օգտատերերը բախվում են երկու աղյուսակների կամ ցուցակների համեմատության հետ՝ դրանցում տարբերությունները կամ բացակայող տարրերը բացահայտելու համար: Յուրաքանչյուր օգտվող յուրովի է հաղթահարում այս խնդիրը, բայց ամենից հաճախ բավականին մեծ ժամանակ է ծախսվում այս խնդրի լուծման վրա, քանի որ այս խնդրի ոչ բոլոր մոտեցումներն են ռացիոնալ: Միևնույն ժամանակ, կան գործողության մի քանի ապացուցված ալգորիթմներ, որոնք թույլ կտան ձեզ համեմատել ցուցակները կամ աղյուսակների զանգվածները բավականին կարճ ժամանակում՝ նվազագույն ջանքերով: Եկեք ավելի սերտ նայենք այս տարբերակներին:

Excel-ում սեղանի տարածքները համեմատելու մի քանի եղանակ կա, բայց դրանք բոլորը կարելի է բաժանել երեք մեծ խմբերի.

Հենց այս դասակարգման հիման վրա է, որ առաջին հերթին ընտրվում են համեմատության մեթոդները, որոշվում են առաջադրանքը կատարելու կոնկրետ գործողություններ և ալգորիթմներ։ Օրինակ, տարբեր աշխատանքային գրքերի համեմատություններ կատարելիս պետք է միաժամանակ երկու Excel ֆայլ բացել:

Բացի այդ, պետք է ասել, որ իմաստ ունի համեմատել սեղանի տարածքները միայն այն դեպքում, երբ դրանք ունեն նմանատիպ կառուցվածք։

Մեթոդ 1. պարզ բանաձև

Երկու աղյուսակներում տվյալները համեմատելու ամենահեշտ ձևը հավասարության պարզ բանաձևի օգտագործումն է: Եթե ​​տվյալները համընկնում են, ապա այն վերադարձնում է TRUE, իսկ եթե ոչ, ապա FALSE: Դուք կարող եք համեմատել ինչպես թվային, այնպես էլ տեքստային տվյալները: Այս մեթոդի թերությունն այն է, որ այն կարող է օգտագործվել միայն այն դեպքում, եթե աղյուսակի տվյալները դասավորված կամ դասավորված են նույն ձևով, համաժամանակացված և ունեն հավասար թվով տողեր։ Տեսնենք, թե ինչպես կարելի է գործնականում կիրառել այս մեթոդը՝ օգտագործելով մեկ թերթիկի վրա տեղադրված երկու աղյուսակների օրինակը:

Այսպիսով, մենք ունենք երկու պարզ աղյուսակ՝ ձեռնարկության աշխատակիցների ցուցակներով և նրանց աշխատավարձերով։ Անհրաժեշտ է համեմատել աշխատողների ցուցակները և բացահայտել անհամապատասխանությունները սյունակների միջև, որոնցում գտնվում են անունները:

1. Դա անելու համար մեզ անհրաժեշտ է թերթի վրա լրացուցիչ սյունակ: Այնտեղ նշան գրեք «=» . Այնուհետև կտտացրեք այն անունը, որը ցանկանում եք համեմատել առաջին ցուցակում: Կրկին դրեցինք խորհրդանիշը «=» ստեղնաշարից։ Հաջորդը, սեղմեք սյունակի առաջին բջիջի վրա, որը մենք համեմատում ենք երկրորդ աղյուսակում: Արդյունքը հետևյալ տեսակի արտահայտությունն է.

   

   Թեեւ, իհարկե, յուրաքանչյուր կոնկրետ դեպքում կոորդինատները կտարբերվեն, բայց էությունը կմնա նույնը։

2. Սեղմեք բանալին Մուտքագրեքհամեմատության արդյունքներ ստանալու համար: Ինչպես տեսնում եք, երկու ցուցակների առաջին բջիջները համեմատելիս ծրագիրը նշել է ցուցիչը «ՃԻՇՏ», ինչը նշանակում է, որ տվյալները համընկնում են:



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

   Պատճենման ընթացակարգը ամենահեշտն իրականացվում է լրացման մարկերի միջոցով: Մենք կուրսորը տեղափոխում ենք բջիջի ստորին աջ անկյունը, որտեղ մենք ստացել ենք ցուցիչը «ՃԻՇՏ». Միևնույն ժամանակ, այն պետք է վերածվի սև խաչի: Սա լրացման նշանն է: Սեղմեք մկնիկի ձախ կոճակը և կուրսորը քաշեք ներքև՝ համեմատվող աղյուսակի զանգվածների տողերի քանակով:



4. Ինչպես տեսնում եք, այժմ լրացուցիչ սյունակում ցուցադրվում են տվյալների համեմատության բոլոր արդյունքները աղյուսակային զանգվածների երկու սյունակներում: Մեր դեպքում միայն մեկ շարքի տվյալները չեն համընկնում։ Դրանք համեմատելիս բանաձեւը տվեց արդյունքը «ՍՈՒՏ». Մնացած բոլոր տողերի համար, ինչպես տեսնում ենք, համեմատության բանաձևը տվել է ցուցանիշը «ՃԻՇՏ».



5. Բացի այդ, հնարավոր է հաշվարկել անհամապատասխանությունների թիվը՝ օգտագործելով հատուկ բանաձեւ: Դա անելու համար ընտրեք թերթի այն տարրը, որտեղ այն կցուցադրվի: Այնուհետև սեղմեք պատկերակի վրա «Տեղադրել գործառույթը».



6. Պատուհանում Function Wizardsօպերատորի խմբում «Մաթեմատիկական»ընդգծել անունը SUMPRODUCT. Սեղմեք կոճակի վրա «Լավ».



7. Գործառույթի արգումենտների պատուհանը ակտիվացված է SUMPRODUCT, որի հիմնական խնդիրն է հաշվարկել ընտրված տեսականու արտադրանքի գումարը։ Բայց այս ֆունկցիան կարող է օգտագործվել նաև մեր նպատակների համար։ Դրա շարահյուսությունը բավականին պարզ է.

   SUMPRODUCT (զանգված 1, զանգված 2,…)

   Ընդհանուր առմամբ, որպես արգումենտ կարող են օգտագործվել մինչև 255 զանգվածի հասցեներ: Բայց մեր դեպքում մենք կօգտագործենք ընդամենը երկու զանգված, ընդ որում՝ որպես մեկ փաստարկ։

   Տեղադրեք կուրսորը դաշտում «Array1»և ընտրեք տվյալների տիրույթը, որը պետք է համեմատվի թերթի առաջին տարածքում: Դրանից հետո դաշտում նշան դրեք «հավասար չէ» (<> ) և ընտրեք երկրորդ տարածքի համեմատվող տիրույթը: Այնուհետև ստացված արտահայտությունը փաթաթում ենք փակագծերում, որոնցից առաջ երկու նշան ենք դնում «-» . Մեր դեպքում ստացանք հետևյալ արտահայտությունը.

   --(A2:A7<>D2:D7)

   Սեղմեք կոճակի վրա «Լավ».



8. Օպերատորը կատարում է հաշվարկը և ցուցադրում արդյունքը: Ինչպես տեսնում եք, մեր դեպքում արդյունքը հավասար է թվին «1», այսինքն՝ սա նշանակում է, որ համեմատվող ցուցակներում հայտնաբերվել է մեկ անհամապատասխանություն։ Եթե ​​ցուցակները լիովին նույնական լինեին, ապա արդյունքը հավասար կլիներ թվին «0» .

Նույն կերպ, դուք կարող եք համեմատել տվյալները աղյուսակներում, որոնք գտնվում են տարբեր թերթիկների վրա: Բայց այս դեպքում ցանկալի է, որ դրանցում եղած տողերը համարակալված լինեն։ Հակառակ դեպքում, համեմատության կարգը գրեթե նույնն է, ինչ վերը նկարագրված է, բացառությամբ այն փաստի, որ բանաձևը մուտքագրելիս ստիպված կլինեք անցնել թերթերի միջև: Մեր դեպքում արտահայտությունը կունենա հետևյալ տեսքը.

B2=Թերթ2!B2

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

Մեթոդ 2. Բջիջների խմբերի ընտրություն

Համեմատությունները կարող են կատարվել բջիջների խմբի ընտրության գործիքի միջոցով: Այն կարող է նաև համեմատել միայն համաժամեցված և պատվիրված ցուցակները: Բացի այդ, այս դեպքում ցուցակները պետք է տեղադրվեն միմյանց կողքին՝ նույն թերթիկի վրա։

Մեթոդ 3. Պայմանական ձևաչափում

Դուք կարող եք համեմատություն կատարել՝ օգտագործելով պայմանական ձևաչափման մեթոդը: Ինչպես նախորդ մեթոդում, համեմատվող տարածքները պետք է լինեն նույն Excel աշխատաթերթում և համաժամանակացվեն միմյանց հետ:

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

Ցանկության դեպքում կարող եք, ընդհակառակը, գունավորել այն տարրերը, որոնք չեն համընկնում, իսկ այդ ցուցիչները, որոնք համընկնում են, թողնել նույն լցավորման գույնի հետ։ Այս դեպքում գործողությունների ալգորիթմը գրեթե նույնն է, բայց պարամետրերի պատուհանում պարամետրի փոխարեն առաջին դաշտում ընդգծվում են կրկնվող արժեքները: «Պարբերական»դուք պետք է ընտրեք տարբերակը «Յուրահատուկ». Դրանից հետո սեղմեք կոճակը «Լավ».

Այսպիսով, ընդգծվելու են հենց այն ցուցանիշները, որոնք չեն համընկնում։

Մեթոդ 4. բարդ բանաձև

Կարող եք նաև համեմատել տվյալները՝ օգտագործելով ֆունկցիայի վրա հիմնված բարդ բանաձև COUNTIF. Օգտագործելով այս գործիքը, կարող եք հաշվարկել, թե երկրորդ աղյուսակի ընտրված սյունակից յուրաքանչյուր տարր քանիսն է կրկնվում առաջինում:

Օպերատոր COUNTIFպատկանում է ֆունկցիաների վիճակագրական խմբին։ Դրա խնդիրն է հաշվել բջիջների քանակը, որոնց արժեքները բավարարում են տվյալ պայմանին: Այս օպերատորի շարահյուսությունը հետևյալն է.

COUNTIF (միջակայք, չափանիշ)

Փաստարկ «Շրջանակ»այն զանգվածի հասցեն է, որում հաշվվում են համապատասխան արժեքները:

Փաստարկ «Չափանիշ»նշում է համապատասխանության պայմանը. Մեր դեպքում այն ​​կներկայացնի առաջին աղյուսակի տարածքի կոնկրետ բջիջների կոորդինատները:

Իհարկե, այս արտահայտությունը կարող է օգտագործվել իր առկա ձևով աղյուսակային ցուցանիշները համեմատելու համար, բայց հնարավոր է այն բարելավել:

Եկեք համոզվենք, որ այն արժեքները, որոնք գտնվում են երկրորդ աղյուսակում, բայց ոչ առաջինում, ցուցադրվում են առանձին ցուցակում:

1. Նախ, եկեք մի փոքր վերամշակենք մեր բանաձեւը COUNTIF, մասնավորապես եկեք դա դարձնենք օպերատորի փաստարկներից մեկը ԵԹԵ. Դա անելու համար ընտրեք առաջին բջիջը, որում գտնվում է օպերատորը COUNTIF. Բանաձևի տողում ավելացրեք արտահայտությունը դրա դիմաց «ԵԹԵ»առանց չակերտների և բացիր փակագծերը։ Հաջորդը, մեզ համար ավելի հեշտ աշխատելու համար, նշեք արժեքը բանաձևի տողում «ԵԹԵ»և սեղմեք պատկերակի վրա «Տեղադրել գործառույթը».



2. Բացվում է ֆունկցիայի փաստարկների պատուհանը ԵԹԵ. Ինչպես տեսնում եք, պատուհանի առաջին դաշտն արդեն լցված է օպերատորի արժեքով COUNTIF. Բայց այս դաշտում պետք է մի բան էլ ավելացնենք. Տեղադրեք կուրսորը այնտեղ և ավելացրեք առկա արտահայտությանը «=0» առանց չակերտների.

   Սրանից հետո անցնում ենք խաղադաշտ «Արժեքը, եթե ճիշտ է». Այստեղ մենք կօգտագործենք մեկ այլ ներդիր ֆունկցիա՝ ԳԻԾ. Մուտքագրեք բառը «LINE»առանց չակերտների, այնուհետև բացում ենք փակագծերը և երկրորդ աղյուսակում նշում ենք ազգանունով առաջին վանդակի կոորդինատները, որից հետո փակում ենք փակագծերը։ Կոնկրետ մեր դեպքում ոլորտում «Արժեքը, եթե ճիշտ է»ստացվել է հետևյալ արտահայտությունը.

   ROW (D2)

   Այժմ օպերատորը ԳԻԾկզեկուցի գործառույթները ԵԹԵտողի համարը, որում գտնվում է կոնկրետ ազգանունը, և այն դեպքում, երբ բավարարված է առաջին դաշտում նշված պայմանը, գործառույթը. ԵԹԵայս թիվը դուրս կբերի բջիջ: Սեղմեք կոճակի վրա «Լավ».



3. Ինչպես տեսնում ենք, առաջին արդյունքը ցուցադրվում է այսպես «ՍՈՒՏ». Սա նշանակում է, որ արժեքը չի բավարարում օպերատորի պայմաններին ԵԹԵ. Այսինքն՝ առաջին ազգանունն առկա է երկու ցուցակներում էլ։



4. Օգտագործելով լրացման նշիչը, մենք պատճենում ենք օպերատորի արտահայտությունը սովորական ձևով ԵԹԵամբողջ սյունակի համար: Ինչպես տեսնում եք, երկու դիրքերի համար, որոնք առկա են երկրորդ աղյուսակում, բայց ոչ առաջինում, բանաձևը արտադրում է տողերի համարներ:



5. Սեղանի տարածքից հետ ենք քաշվում դեպի աջ և սյունակը հերթականությամբ լրացնում ենք թվերով՝ սկսած դրանից 1 . Թվերի թիվը պետք է համապատասխանի համեմատվող երկրորդ աղյուսակի տողերի թվին: Դուք կարող եք նաև օգտագործել լրացման մարկեր՝ համարակալման գործընթացը արագացնելու համար:



6. Դրանից հետո ընտրեք թվերով սյունակի աջ կողմում գտնվող առաջին բջիջը և սեղմեք պատկերակի վրա «Տեղադրել գործառույթը».



7. Բացվում է Function Wizard. Անցեք կատեգորիա «Վիճակագրական»և ընտրել անուն «ՆՈՔԻՆ». Սեղմեք կոճակի վրա «Լավ».



8. Գործառույթ ՆՈՔԻՆ, որի արգումենտի պատուհանը բացվել է, նախատեսված է ցուցադրելու հաշվի կողմից նշված ամենափոքր արժեքը։

   Դաշտում «Զանգված»Դուք պետք է նշեք լրացուցիչ սյունակի տիրույթի կոորդինատները «Համապատասխանությունների քանակը», որը մենք նախկինում փոխակերպել ենք՝ օգտագործելով ֆունկցիան ԵԹԵ. Մենք բոլոր հղումները դարձնում ենք բացարձակ:

   Դաշտում «Կ»ցույց է տալիս թողարկվող ամենափոքր արժեքը: Այստեղ մենք նշում ենք համարակալված սյունակի առաջին բջիջի կոորդինատները, որոնք վերջերս ավելացրել ենք: Թողնում ենք ազգականի հասցեն։ Սեղմեք կոճակի վրա «Լավ».



9. Օպերատորը թողարկում է արդյունքը՝ թիվ 3 . Այն ամենափոքրն է աղյուսակների զանգվածների չհամապատասխանող տողերի համարակալումից։ Օգտագործելով լրացման նշիչը, պատճենեք բանաձեւը հենց ներքևում:



10. Այժմ, իմանալով չհամապատասխանող տարրերի տողերի համարները, մենք կարող ենք դրանց արժեքները բջիջի մեջ տեղադրել՝ օգտագործելով ֆունկցիան: ԻՆԴԵՔՍ. Ընտրեք բանաձևը պարունակող թերթիկի առաջին տարրը ՆՈՔԻՆ. Դրանից հետո անցեք բանաձևի տող և անունից առաջ «ՆՈՔԻՆ»ավելացնել անունը «Ինդեքս»առանց չակերտների, անմիջապես բացեք փակագիծը և դրեք ստորակետ ( ; ). Այնուհետև ընտրեք անունը բանաձևի տողում «Ինդեքս»և սեղմեք պատկերակի վրա «Տեղադրել գործառույթը».



11. Դրանից հետո բացվում է մի փոքր պատուհան, որտեղ դուք պետք է որոշեք, թե ինչ տեղեկանքի տեսակ պետք է ունենա գործառույթը ԻՆԴԵՔՍկամ նախատեսված է զանգվածների հետ աշխատելու համար: Մեզ երկրորդ տարբերակ է պետք. Այն տեղադրված է լռելյայնորեն, ուստի այս պատուհանում մենք պարզապես սեղմում ենք կոճակը «Լավ».



12. Բացվում է ֆունկցիայի փաստարկների պատուհանը ԻՆԴԵՔՍ. Այս օպերատորը նախատեսված է ցուցադրելու արժեք, որը գտնվում է որոշակի զանգվածում նշված տողում:

    Ինչպես տեսնում ենք, դաշտ «Գծի համարը»արդեն լցված է ֆունկցիայի արժեքներով ՆՈՔԻՆ. Այն արժեքից, որն արդեն գոյություն ունի, դուք պետք է հանեք Excel թերթի և աղյուսակի ներքին համարակալման տարբերությունը: Ինչպես տեսնում եք, մենք ունենք միայն վերնագիր աղյուսակի արժեքներից վեր: Սա նշանակում է, որ տարբերությունը մեկ տող է: Հետեւաբար, մենք դաշտում ավելացնում ենք «Գծի համարը»իմաստը «-1»առանց չակերտների.

    Դաշտում «Զանգված»նշեք երկրորդ աղյուսակի արժեքների տիրույթի հասցեն: Միևնույն ժամանակ բոլոր կոորդինատները դարձնում ենք բացարձակ, այսինքն՝ դրանց դիմաց դոլարի նշան ենք դնում այնպես, ինչպես նախկինում նկարագրեցինք։

    Սեղմեք կոճակի վրա «Լավ».



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

Մեթոդ 5. Համեմատելով զանգվածները տարբեր աշխատանքային գրքույկներում

Տարբեր աշխատանքային գրքույկներում միջակայքերը համեմատելիս կարող եք օգտագործել վերը թվարկված մեթոդները՝ բացառելով այն տարբերակները, որոնք պահանջում են աղյուսակի երկու հատվածները նույն թերթիկի վրա դնել: Համեմատության ընթացակարգի իրականացման հիմնական պայմանն այս դեպքում երկու ֆայլերի պատուհանները միաժամանակ բացելն է։ Excel 2013 և ավելի նոր տարբերակների համար, ինչպես նաև Excel 2007-ից առաջ տարբերակների համար այս պայմանը բավարարելու հետ կապված խնդիրներ չկան: Բայց Excel 2007-ում և Excel 2010-ում երկու պատուհանները միաժամանակ բացելու համար անհրաժեշտ են լրացուցիչ մանիպուլյացիաներ։ Ինչպես դա անել, նկարագրված է առանձին դասում:

Ինչպես տեսնում եք, աղյուսակները միմյանց հետ համեմատելու մի շարք հնարավորություններ կան: Որ տարբերակն օգտագործել կախված է նրանից, թե որտեղ են գտնվում աղյուսակային տվյալները միմյանց համեմատ (նույն թերթիկի վրա, տարբեր աշխատանքային գրքույկներում, տարբեր թերթերում), ինչպես նաև այն բանից, թե ինչպես է օգտատերը ցանկանում, որ այս համեմատությունը ցուցադրվի էկրանին:

Հարց օգտատերից

Ողջույն

Ես մեկ առաջադրանք ունեմ, և արդեն երրորդ օրն է, ինչ ուղեղս խառնում եմ, չգիտեմ, թե ինչպես ավարտեմ այն: Կա 2 աղյուսակ (յուրաքանչյուրում մոտ 500-600 տող), դուք պետք է մի աղյուսակից վերցնեք ապրանքի անվանման սյունակը և համեմատեք այն մյուսի ապրանքի անվան հետ, իսկ եթե ապրանքները համընկնում են, պատճենեք և Տեղադրեք արժեքը աղյուսակ 2-ից աղյուսակ 1-ում: Շփոթված բացատրված է, բայց կարծում եմ, որ դուք կհասկանաք առաջադրանքը լուսանկարից ( մոտ. Լուսանկարը կտրվել է գրաքննության արդյունքում, այն դեռ անձնական տվյալ է).

Կանխավ շնորհակալ եմ։ Անդրեյ, Մոսկվա.

Բարի օր բոլորին!

Ձեր նկարագրածը վերաբերում է բավականին հայտնի առաջադրանքներին, որոնք համեմատաբար հեշտ և արագ են լուծվում Excel-ի միջոցով: Ձեզ անհրաժեշտ է ընդամենը ձեր երկու աղյուսակները տեղադրել ծրագրի մեջ և օգտագործել VLOOKUP ֆունկցիան: Նրա աշխատանքի մասին մանրամասն՝ ստորև...

VLOOKUP ֆունկցիայի հետ աշխատելու օրինակ

Որպես օրինակ, ես վերցրեցի երկու փոքր նշան, դրանք ներկայացված են ստորև ներկայացված սքրինշոթում: Առաջին աղյուսակում (սյունակներ Ա, Բ- ապրանքը և գինը) սյունակի վերաբերյալ տվյալներ չկան Բ; երկրորդում լրացվում են երկու սյունակները (ապրանքը և գինը): Այժմ դուք պետք է ստուգեք առաջին սյունակները երկու աղյուսակներում և ավտոմատ կերպով, եթե համընկնում է, պատճենեք գինը առաջին աղյուսակում: Կարծես թե պարզ խնդիր է...

Ինչպես դա անել...

Տեղադրեք մկնիկի ցուցիչը բջիջի մեջ B2- այսինքն՝ սյունակի առաջին բջիջում, որտեղ մենք արժեք չունենք և գրում ենք բանաձևը.

=VLOOKUP(A2,$E$1:$F$7,2,FALSE)

A2- արժեքը առաջին աղյուսակի առաջին սյունակից (ինչ մենք կփնտրենք երկրորդ աղյուսակի առաջին սյունակում);

$E$1:$F$7- ամբողջությամբ ընտրված երկրորդ աղյուսակ (որում մենք ցանկանում ենք ինչ-որ բան գտնել և պատճենել): Ուշադրություն դարձրեք «$» նշանին. անհրաժեշտ է, որպեսզի բանաձևը պատճենելիս ընտրված երկրորդ աղյուսակի բջիջները չփոխվեն.

2 - սյունակի համարը, որից մենք պատճենում ենք արժեքը (նկատի ունեցեք, որ մեր ընտրած երկրորդ աղյուսակը ունի ընդամենը 2 սյունակ: Եթե այն ուներ 3 սյունակ, ապա արժեքը կարող է պատճենվել 2-րդ կամ 3-րդ սյունակից);

ՍՈՒՏ- Մենք ճշգրիտ համընկնում ենք փնտրում (հակառակ դեպքում առաջին նմանատիպը կփոխարինվի, ինչը մեզ ակնհայտորեն չի համապատասխանում):

Փաստորեն, դուք կարող եք հարմարեցնել պատրաստի բանաձևը ձեր կարիքներին՝ մի փոքր փոխելով այն: Բանաձևի արդյունքը ներկայացված է ստորև նկարում՝ գինը գտնվել է երկրորդ աղյուսակում և մուտքագրվել է ավտոմատ ռեժիմում։ Ամեն ինչ աշխատում է:

Այլ ապրանքների անվանումների գինը սահմանելու համար պարզապես ընդլայնեք (պատճենեք) բանաձևը այլ բջիջների վրա: Օրինակ ստորև.

Որից հետո, ինչպես տեսնում եք, աղյուսակների առաջին սյունակները կհամեմատվեն. այն տողերից, որտեղ բջիջների արժեքները համընկնում են, անհրաժեշտ տվյալները կպատճենվեն և կփոխարինվեն: Ընդհանուր առմամբ, պարզ է, որ սեղանները կարող են շատ ավելի մեծ լինել:

ՆշումՊետք է ասեմ, որ VLOOKUP ֆունկցիան բավականին պահանջկոտ է համակարգչային ռեսուրսների համար: Որոշ դեպքերում, չափազանց մեծ փաստաթղթի դեպքում, աղյուսակները համեմատելը կարող է բավականին երկար ժամանակ պահանջել: Այս դեպքերում արժե դիտարկել կամ այլ բանաձեւեր, կամ բոլորովին այլ լուծումներ (յուրաքանչյուր դեպք անհատական ​​է):

Այսքանը, հաջողություն:

Ասեք, որ ցանկանում եք համեմատել աշխատանքային գրքույկի տարբերակները, վերլուծել աշխատանքային գրքույկը խնդիրների կամ անհամապատասխանությունների համար, կամ տեսնել աշխատանքային գրքույկի կամ աշխատաթերթերի միջև եղած հղումները: Եթե ​​ձեր համակարգչում տեղադրված է Microsoft Office 365 կամ Office Professional Plus 2013, Spreadsheet Inquire հավելումը հասանելի է Excel-ում:

Այս բոլոր առաջադրանքները և ավելին կատարելու համար կարող եք օգտագործել «Հարցում» ներդիրի հրամանները: Excel ժապավենի «Հարցում» ներդիրում կան ստորև նկարագրված հրամանների կոճակներ:

Եթե ​​դուք չեք տեսնում Հարցրեք Excel ժապավենի ներդիրում, տես Միացնել աղյուսակի հարցումների հավելումը:

Համեմատեք երկու աշխատանքային գրքույկ

Այն Համեմատեք ֆայլերըհրամանը թույլ է տալիս բջիջ առ բջիջ տեսնել երկու աշխատանքային գրքույկների միջև եղած տարբերությունները: Այս հրամանը գործարկելու համար Excel-ում պետք է բացված երկու աշխատանքային գրքույկ:

Արդյունքները գունավոր կոդավորված են ըստ բովանդակության տեսակի, ինչպիսիք են մուտքագրված արժեքները, բանաձևերը, անվանված միջակայքերը և ձևաչափերը: Նույնիսկ կա մի պատուհան, որը կարող է ցույց տալ VBA կոդի փոփոխությունները տող առ տող: Բջիջների միջև տարբերությունները ցուցադրվում են հեշտ ընթեռնելի ցանցի դասավորության մեջ, ինչպես հետևյալը.

Այն Համեմատեք ֆայլերըհրամանը օգտագործում է Microsoft Spreadsheet Համեմատել երկու ֆայլերը համեմատելու համար: Windows 8-ում կարող եք սկսել Spreadsheet Compare-ը Excel-ից դուրս՝ սեղմելով Աղյուսակ Համեմատելվրա Հավելվածներէկրան. Windows 7-ում սեղմեք Windows Սկսելկոճակը և այնուհետև > Բոլոր ծրագրերը > Microsoft Office 2013 > Office 2013 գործիքներ > Աղյուսակ Համեմատեք 2013 թ.

Աղյուսակների համեմատության և ֆայլերի համեմատման մասին ավելին իմանալու համար կարդացեք Համեմատեք աշխատանքային գրքի երկու տարբերակները:

Վերլուծել աշխատանքային գրքույկը

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

Ցույց տալ աշխատանքային գրքույկի հղումները

Բջջային հղումների միջոցով այլ աշխատանքային գրքերի հետ կապված աշխատանքային գրքույկները կարող են շփոթեցնել: Օգտագործեք ֆայլերի միջև կապերի (հղումների) միջոցով ստեղծված աշխատանքային գրքույկի կախվածությունների ինտերակտիվ, գրաֆիկական քարտեզ ստեղծելու համար: Դիագրամի հղումների տեսակները կարող են ներառել այլ աշխատանքային գրքեր, Access տվյալների բազաներ, տեքստային ֆայլեր, HTML էջեր, SQL Server տվյալների բազաներ և տվյալների այլ աղբյուրներ: Հարաբերությունների գծապատկերում դուք կարող եք ընտրել տարրեր և գտնել դրանց մասին ավելի շատ տեղեկություններ և քաշել կապի գծերը՝ դիագրամի ձևը փոխելու համար:

Այս դիագրամը ցույց է տալիս ձախ կողմում գտնվող ընթացիկ աշխատանքային գիրքը և դրա և այլ աշխատանքային գրքերի և տվյալների աղբյուրների միջև կապերը: Այն նաև ցույց է տալիս աշխատանքային գրքույկի կապերի լրացուցիչ մակարդակներ՝ տալով ձեզ աշխատանքային գրքի տվյալների ծագման պատկերը:

Ցույց տալ աշխատանքային թերթիկի հղումները

Ունե՞ք շատ աշխատանքային թերթեր, որոնք կախված են միմյանցից: Օգտագործեք՝ ստեղծելու ինտերակտիվ, գրաֆիկական միացումների (հղումների) քարտեզ աշխատաթերթերի միջև ինչպես նույն աշխատանքային գրքում, այնպես էլ այլ աշխատանքային գրքում: Սա օգնում է ձեզ ավելի հստակ պատկերացում կազմել, թե ինչպես կարող են ձեր տվյալները կախված լինել այլ վայրերի բջիջներից:

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

Ցույց տալ բջիջների հարաբերությունները

Այլ աշխատաթերթերի կամ նույնիսկ այլ աշխատանքային գրքույկի բոլոր հղումների մանրամասն, ինտերակտիվ դիագրամ ստանալու համար օգտագործեք Բջջային հարաբերություններգործիք. Այս հարաբերությունները այլ բջիջների հետ կարող են գոյություն ունենալ բանաձևերում կամ հղումներով անվանված տիրույթներին: Դիագրամը կարող է հատել աշխատանքային թերթերը և աշխատանքային գրքույկները:

Այս դիագրամը ցույց է տալիս բջիջների փոխհարաբերությունների երկու մակարդակ A10 բջիջի համար Sheet5-ում Book1.xlsx-ում: Այս բջիջը կախված է Թերթ 1-ի C6 բջիջից մեկ այլ աշխատանքային գրքում՝ Book2.xlsx: Այս բջիջը նախադեպ է նույն ֆայլի այլ աշխատաթերթերի մի քանի բջիջների համար:

Բջջային հարաբերությունները դիտելու մասին ավելին իմանալու համար կարդացեք Տես բջիջների միջև կապերը:

Մաքրել ավելորդ բջիջների ձևաչափումը

Երբևէ բացե՞լ եք աշխատանքային գրքույկը և գտնեք, որ այն դանդաղ է բեռնվում, թե՞ հսկայական է դարձել: Այն կարող է կիրառվել այն տողերի կամ սյունակների վրա, որոնց մասին դուք տեղյակ չեք Մաքրել ավելցուկային բջիջների ձևաչափումըհրաման՝ հեռացնելու ավելորդ ձևաչափումը և զգալիորեն նվազեցնել ֆայլի չափը: Սա օգնում է ձեզ խուսափել «աղյուսակների փքվածությունից», ինչը բարելավում է Excel-ի արագությունը:

Կառավարեք գաղտնաբառերը

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