Առնչվող աղյուսակներ Excel- ում. Մանրամասն հրահանգներ

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

Առնչվող սեղանները շատ հարմար են օգտագործել մեծ քանակությամբ տեղեկատվություն վարելու համար: Տեղադրեք բոլոր տեղեկությունները մեկ սեղանի մեջ, բացի այդ, եթե այն համասեռ չէ, ոչ այնքան հարմար: Դժվար է աշխատել նման առարկաների հետ եւ որոնել դրանք: Նշված խնդիրը պարզապես նախագծված է հարակից սեղանները վերացնելու համար, որի միջեւ բաշխված տեղեկատվությունը բաժանվում է, բայց միեւնույն ժամանակ փոխկապակցված է: Առնչվող աղյուսակները կարող են լինել ոչ միայն մեկ թերթի կամ մեկ գրքի շրջանակներում, այլեւ տեղակայվել առանձին գրքերում (ֆայլեր): Գործնականում վերջին երկու տարբերակները առավել հաճախ օգտագործվում են, քանի որ այս տեխնոլոգիայի նպատակը պարզապես տվյալների կուտակումից հեռանալու համար է, եւ մեկ էջում նրանց քթումը չի լուծում: Եկեք սովորենք, թե ինչպես ստեղծել եւ ինչպես աշխատել տվյալների նման տեսակի կառավարման միջոցով:

Առնչվող սեղանների ստեղծում

Առաջին հերթին, եկեք կենտրոնանանք հարցի վրա, որտեղ ճանապարհով հնարավոր է կապ ստեղծել տարբեր սեղանների միջեւ:

Մեթոդ 1. Ուղղակի պարտադիր սեղանների բանաձեւ

Տվյալների պահպանման ամենահեշտ ձեւը բանաձեւերի օգտագործումն է, որում կան հղումներ այլ սեղանների: Այն կոչվում է ուղղակի պարտադիր: Այս մեթոդը ինտուիտիվ է, քանի որ այն կապում է գրեթե ճիշտ այնպես, ինչպես մեկ սեղանի զանգվածում տվյալների հղումներ ստեղծելը:

Տեսնենք, թե ինչպես կարող եք օրինակ լինել հաղորդակցությունը ուղղակիորեն պարտադիր: Երկու թերթի վրա ունենք երկու սեղան: Նույն սեղանի շուրջ աշխատավարձը հաշվարկվում է բանաձեւի միջոցով `աշխատողի գները բազմապատկելով մեկ գործակիցի համար:

Երկրորդ թերթի վրա կա սեղանի միջակայք, որում կա աշխատավարձի ցուցակ, աշխատավարձով: Երկու դեպքերում աշխատողների ցուցակը ներկայացված է մեկ պատվերով:

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

1. Առաջին թերթի վրա մենք հատկացնում ենք «խաղադրույք» սյունակի առաջին բջիջը: Մենք դրա մեջ դրեցինք նշանը «=»: Հաջորդը կտտացրեք «Թերթ 2» պիտակի վրա, որը տեղադրված է Excel ինտերֆեյսի ձախ մասում `կարգավիճակի բարում:



2. Փաստաթղթի երկրորդ տարածքում կա շարժում: Կտտացրեք «Խաղադրույքի» սյունակում առաջին բջիջը: Այնուհետեւ կտտացրեք ստեղնաշարի վրա մուտքագրման կոճակը, տվյալները մուտքագրելու համար բջիջ մուտք գործելու համար, որում նախկինում տեղադրվել է «հավասար» նշան:



3. Այնուհետեւ կա ավտոմատ անցում դեպի առաջին թերթիկը: Ինչպես տեսնում ենք, երկրորդ սեղանից առաջին աշխատողի արժեքը քաշվում է համապատասխան խցում: Տեղադրելով կուրսորը խաղադրույքներ պարունակող խցում, մենք տեսնում ենք, որ սովորական բանաձեւը օգտագործվում է էկրանին տվյալները ցուցադրելու համար: Բայց բջիջի կոորդինատների դիմաց, որտեղից տվյալներն են, կա «ցուցակ 2» արտահայտություն, որը ցույց է տալիս այն փաստաթղթի տարածքի անվանումը, որտեղ նրանք գտնվում են: Մեր դեպքում ընդհանուր բանաձեւը այսպիսին է.

   = Ցուցակ 2! B2



4. Այժմ հարկավոր է տվյալներ փոխանցել ձեռնարկության բոլոր մյուս աշխատողների տեմպերի մասին: Իհարկե, դա կարելի է անել այնպես, որ մենք կատարեցինք առաջադրանքը առաջին աշխատողի համար, բայց հաշվի առնելով, որ երկու աշխատողների ցուցակները տեղակայված են նույն կարգով: Դա կարելի է անել պարզապես ձեւաթուղթը պատճենելով ստորեւ նշված միջակայքում: Շնորհիվ այն բանի, որ Excel- ի հղումները հարազատ են, դրանց արժեքները պատճենելիս արժեքների հերթափոխը տեղափոխվում է, որ մեզ անհրաժեշտ է: Պատճենահանման կարգը ինքնին կարող է կատարվել `օգտագործելով լրացնող ցուցիչ:

   Այսպիսով, մենք կուրսորը դնում ենք տարրի ցածր աջ տարածքին, բանաձեւով: Դրանից հետո կուրսորը պետք է վերածվի լցոնման ցուցիչի, սեւ խաչի տեսքով: Մենք կատարում ենք մկնիկի ձախ կոճակի սեղմակը եւ կուրսորը քաշում սյունակի քանակին:



5. Սեղանի վրա գտնվող նմանատիպ սյունակից բոլոր տվյալները թերթիկի վրա քաշվել են սեղանի վրա 1. Երբ տվյալների փոփոխվում է 2-րդ թերթի վրա, դրանք ինքնաբերաբար կփոխվեն առաջինի վրա:

Մեթոդ 2. Օգտագործելով օպերատորների ցուցիչի սխալը - Որոնում

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

1. Մենք կարեւորում ենք «Խաղադրույքի» սյունակի առաջին տարրը: Գնացեք գործառույթների հրաշագործ `կտտացնելով« Տեղադրեք գործառույթ »պատկերակը:



2. «Հղումներ եւ զանգվածներ» խմբում գործառույթների հրաշագործում մենք գտնում եւ տեղադրում ենք «ինդեքս» անվանումը:



3. Այս օպերատորն ունի երկու ձեւ, ձեւի եւ տեղեկանքի հետ աշխատելու ձեւ: Մեր դեպքում, առաջին տարբերակը պահանջվում է, ուստի հաջորդ ձեւի ընտրության պատուհանում, որը բացվում է, ընտրեք այն եւ կտտացրեք «Լավ» կոճակին:



4. Օպերատորի փաստարկների ցուցիչը սկսում է վազել: Նշված գործառույթի խնդիրն է ընտրված տիրույթում տեղակայված արժեքի արդյունքը նշված թվով նշված համարով: Ընդհանուր Formula օպերատորի ինդեքսը.

   = Ինդեքս (զանգված; number_name; [number_stolbits])

   «Զանգվածը» փաստարկ է, որը պարունակում է այն միջակայքի միջակայքը, որից մենք տեղեկատվություն ենք արդյունահանելու նշված շարքի քանակով:

   «Տողի համարը» փաստարկ է, որը այս տողի քանակն է: Կարեւոր է իմանալ, որ գծի համարը պետք է նշվի ոչ թե ամբողջ փաստաթղթի համեմատ, այլ միայն հարաբերական է հատկացված զանգվածի հետ:

   «Սյունակի քանակը» փաստարկ է, որն ընտրովի է: Հատկապես մեր առաջադրանքի լուծման համար մենք դա չենք օգտագործի, ուստի անհրաժեշտ չէ այն առանձին նկարագրել:

   Մենք կուրսորը դնում ենք «զանգված» դաշտում: Դրանից հետո գնացեք թերթ 2 եւ պահելով մկնիկի ձախ կոճակը, ընտրեք «RACE» սյունակի ամբողջ բովանդակությունը:



5. Օպերատորի պատուհանում ցուցադրվելուց հետո մենք կուրսորը դնում ենք «տողի համար» դաշտում: Մենք կհանենք այս փաստարկը `օգտագործելով որոնողական օպերատորը: Հետեւաբար կտտացրեք եռանկյունին, որը գտնվում է գործառույթի տողի ձախ կողմում: Բացվում է նոր օգտագործված օպերատորների ցուցակը: Եթե ​​նրանց մեջ գտնեք «Որոնման ընկերություն» անվանումը, կարող եք սեղմել դրա վրա: Հակառակ դեպքում կտտացրեք ցուցակի վերջին կետին `« Այլ գործառույթներ ... »:



6. Սկսվում է ստանդարտ պատուհանի հրաշագործ պատուհանը: Գնացեք դրան նույն խմբում «Հղումներ եւ զանգվածներ»: Այս անգամ ցուցակում ընտրեք «Որոնման ընկերություն» կետը: Կատարեք կտտացրեք «OK» կոճակի վրա:



7. Կատարվում է որոնման օպերատորի փաստարկների ակտիվացումը: Նշված գործառույթը նախատեսված է արժեքի համարը որոշակի զանգվածի մեջ իր անունով արտադրել: Այս հատկության շնորհիվ է, որ մենք հաշվարկում ենք գործառույթի գործառույթի համար հատուկ արժեքի տողի քանակը: Որոնման խորհրդի շարահյուսությունը ներկայացված է.

   = Որոնման խորհուրդ (search_name; դիտում __nassive; [Type_station])

   «The անկալի» -ը փաստարկ է, որը պարունակում է երրորդ կողմի միջակայքի բջիջի անվանումը կամ հասցեն, որում տեղակայված է: Դա թիրախային տիրույթում այս անվանման դիրքն է եւ պետք է հաշվարկվի: Մեր դեպքում առաջին փաստարկի դերը կուղղվի թերթի 1-ին բջիջների վրա, որոնցում գտնվում են աշխատակիցները:

   «Litl ուցակված զանգվածը» փաստարկ է, որը հղում է զանգվածին, որը կատարում է նշված արժեքի որոնումը `դրա դիրքը որոշելու համար: Այս դերը կունենանք «Անուն» սյունակի հասցեն `թերթի 2-րդ թերթի հասցեն:

   «Համեմատության տեսակը». Վիճաբանություն, որը ընտրովի է, բայց ի տարբերություն նախորդ օպերատորի, անհրաժեշտ կլինի այս կամընտիր փաստարկը: Այն ցույց է տալիս, թե ինչպես համապատասխանեցնել օպերատորին `ցանկալի արժեքը զանգվածով: Այս փաստարկը կարող է ունենալ երեք արժեքներից մեկը. -1; 0; 1. Անկարգացված զանգվածների համար ընտրեք «0» տարբերակը: Այս տարբերակը հարմար է մեր գործի համար:

   Այսպիսով, շարունակեք լրացնել փաստարկի պատուհանի դաշտերը: Մենք կուրսորը դնում ենք «ֆոգլուլային արժեք» դաշտում կտտացրեք թերթի առաջին բջջային «Անուն» սյունը 1-ին:



8. Կոորդինացիները ցուցադրելուց հետո կուրսորը տեղադրեք «Listingsing Massive» դաշտում եւ գնացեք «Թերթ 2» պիտակի մեջ, որը գտնվում է կարգավիճակի բարից վերեւում Excel պատուհանի ներքեւում: Կլեմենտ մկնիկի ձախ կոճակը եւ կարեւորեք կուրսորը «Անուն» սյունակի բոլոր բջիջները:



9. Նրանց կոորդինատները ցուցադրվելուց հետո «Listingsing Massive» դաշտում անցեք «քարտեզագրման տիպի» դաշտը եւ ստեղնաշարի «0» համարը սահմանեք: Դրանից հետո մենք կրկին վերադառնում ենք «նայելով զանգվածին» դաշտը: Փաստն այն է, որ մենք կկատարենք բանաձեւի պատճենումը, ինչպես մենք արեցինք նախորդ մեթոդով: Կլինի հասցեների տեղաշարժ, բայց այստեղ դիտվում են զանգվածի կոորդինատները, որոնք մենք պետք է ապահովենք: Նա չպետք է տեղափոխվի: Մենք կարեւորում ենք կուրսորի հետ կոորդինատները եւ կտտացնում F4 գործառույթի ստեղնը: Ինչպես տեսնում եք, դոլարի նշանը հայտնվեց կոորդինատների առջեւ, ինչը նշանակում է, որ հարաբերական հղումը վերածվել է բացարձակ: Այնուհետեւ կտտացրեք «OK» կոճակը:



10. Արդյունքը ցուցադրվում է «Գրազ» սյունակի առաջին խցում: Բայց նախքան պատճենելը, մենք պետք է շտկենք մեկ այլ տարածք, մասնավորապես `առաջին փաստարկի գործառույթի ցուցանիշը: Դա անելու համար ընտրեք սյունակի տարրը, որը պարունակում է բանաձեւ եւ տեղափոխվում է բանաձեւի լար: Բաշխեք օպերատորի ինդեքսի առաջին փաստարկը (B2: B7) եւ կտտացրեք F4 կոճակը: Ինչպես տեսնում եք, դոլարի նշանը հայտնվեց ընտրված կոորդինատների մոտ: Կտտացրեք Enter ստեղնը: Ընդհանուր առմամբ, բանաձեւը վերցրեց հետեւյալ ձեւը.

    = Index (Sheet2! $ B $ 2: $ B $ 7; Որոնման խորհուրդը (Sheet1! A4; List2 $ $ 7))



11. Այժմ կարող եք պատճենել `օգտագործելով լրացնող մարկեր: Մենք այն անվանում ենք այնպես, որ ավելի վաղ խոսեցինք եւ ձգվում ենք աղյուսակի միջակայքի ավարտին:



12. Ինչպես տեսնում եք, չնայած այն հանգամանքին, որ երկու հարակից աղյուսակներով տողերի կարգը չի համընկնում, այնուամենայնիվ, բոլոր արժեքները խստացվում են աշխատողների անունների համաձայն: Դա ձեռք է բերվել `օպերատորների ինդեքսի որոնման համադրման շնորհիվ:

Մեթոդ 4. Հատուկ ներդիր

Կապի սեղանի հավաքները Excel- ում կարող են նաեւ օգտագործել հատուկ ներդիր:

1. Ընտրեք այն արժեքները, որոնք ցանկանում եք «խստացնել» մեկ այլ սեղան: Մեր դեպքում սա թերթի «խաղադրույք» սյունի միջակայքն է 2. Կտտացրեք Նվիրված հատվածի վրա `մկնիկի աջ կոճակով: Բացվող ցուցակում ընտրեք «Պատճեն» կետը: Այլընտրանքային համադրությունը Ctrl + C ստեղնաշարի համադրությունն է: Դրանից հետո մենք տեղափոխվում ենք թերթ 1:



2. Տեղափոխվելով ձեզ անհրաժեշտ գրքի տարածքին, հատկացրեք այն բջիջները, որոնցում անհրաժեշտ է խստացնել արժեքները: Մեր դեպքում սա «հայտի» սյունն է: Կտտացրեք մկնիկի աջ կոճակով նվիրված հատվածի վրա: «Տեղադրեք պարամետրեր» գործիքագոտու համատեքստում կտտացրեք «Տեղադրեք հաղորդակցություն» պատկերակին:

   

   Կա նաեւ այլընտրանք: Նա, ի դեպ, Excel- ի ավելի հին տարբերակների համար միակն է: Համատեքստի ընտրացանկում մենք կուրսորը բերում ենք «Հատուկ ներդիր» կետին: Լրացուցիչ ընտրացանկում, որը բացվում է, ընտրեք դիրքը նույն անունով:



3. Դրանից հետո բացվում է հատուկ ներդիրի պատուհանը: Կտտացրեք «Տեղադրեք հաղորդակցություն» կոճակը բջիջի ստորին ձախ անկյունում:



4. Ձեր ընտրած տարբերակը, մի սեղանի զանգվածից արժեքները կտեղադրվեն մեկ այլ: Աղբյուրում տվյալները փոխելիս դրանք նույնպես ավտոմատ կերպով կփոխվեն տեղադրված տիրույթում:

Դաս. Հատուկ ներդիր Excel- ում

Մեթոդ 5. Մի քանի գրքերում սեղանների միջեւ հաղորդակցություն

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

1. Ընտրեք տվյալների տեսականին `տեղափոխվելու մեկ այլ գիրք: Կտտացրեք այն մկնիկի աջ կոճակը եւ ընտրեք «Պատճեն» դիրքը բացված ընտրացանկում:



2. Այնուհետեւ մենք տեղափոխվում ենք այն գիրքը, որտեղ այս տվյալները պետք է տեղադրվեն: Ընտրեք ցանկալի տիրույթը: Կտտացրեք մկնիկի աջ կոճակը: «Տեղադրեք պարամետրեր» խմբում համատեքստի ընտրացանկում ընտրեք «Տեղադրեք հաղորդակցություն» կետը:



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

Բայց հարկ է նշել, որ այս դեպքում ներդրումը արտադրվելու է անփոփոխ զանգվածի տեսքով: Տեղադրված տվյալներով ցանկացած խց `փորձելով փոխել, մի հաղորդագրություն կբնակեցվի, տեղեկացնելով դա անելու անկարողության մասին:

Նման զանգվածի փոփոխությունները, որոնք կապված են մեկ այլ գրքի հետ, կարող են ընդմիջել միայն կապը:

Վերնագիրը կոտրվում է սեղանների միջեւ

Երբեմն պահանջվում է կոտրել կապը սեղանների միջեւ: Դրա պատճառը կարող է լինել վերը նկարագրված, երբ ցանկանում եք փոխել մեկ այլ գրքից տեղադրված զանգվածը եւ պարզապես օգտագործողի դժկամությունը, որպեսզի նույն սեղանի տվյալները ավտոմատ կերպով թարմացվեն:

Մեթոդ 1. Գրքերի միջեւ կապի ընդմիջումները

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

1. Գրքում, որում այլ ֆայլերից արժեքները խստացվում են, անցեք տվյալների ներդիրին: Կտտացրեք «Փոխել հղումները» պատկերակին, որը գտնվում է «կապի» գործիքագոտու ժապավենի վրա: Հարկ է նշել, որ եթե ներկայիս գիրքը չի պարունակում կապեր այլ ֆայլերի հետ, ապա այս կոճակը անգործ է:



2. Գործարկվում է հղման փոփոխության պատուհանը: Ընտրեք հարակից գրքերի ցանկից (եթե դրանցից մի քանիսը կան) այն ֆայլը, որի միջոցով մենք ցանկանում ենք կոտրել կապը: Կտտացրեք «Կոտրեք կապը» կոճակը:



3. Բացվում է տեղեկատվական պատուհանը, որն ապահովում է նախազգուշացում հետագա գործողությունների հետեւանքների վերաբերյալ: Եթե ​​վստահ եք, որ պատրաստվում եք անել, կտտացրեք «Break Community» կոճակը:



4. Դրանից հետո ընթացիկ փաստաթղթում նշված ֆայլի բոլոր հղումները կփոխարինվեն ստատիկ արժեքներով:

Մեթոդ 2. Տեղադրման արժեքներ

Բայց վերը նշված մեթոդը հարմար է միայն այն դեպքում, եթե ձեզ հարկավոր է ամբողջությամբ կոտրել երկու գրքերի միջեւ եղած բոլոր կապերը: Ինչ անել, եթե անհրաժեշտ է անջատել կապված սեղանները նույն ֆայլի մեջ: Դուք կարող եք դա անել `պատճենելով տվյալները, այնուհետեւ նույն տեղում տեղադրելով արժեքներ: Ի դեպ, այս մեթոդը կարող է խզվել տարբեր գրքերի անհատական ​​տվյալների միջակայքերի միջեւ, առանց ֆայլերի միջեւ ընդհանուր փոխհարաբերություններ խախտելու: Տեսնենք, թե ինչպես է այս մեթոդը գործում պրակտիկայում:

1. Մենք կարեւորում ենք այն միջակայքը, որում մենք ցանկանում ենք ջնջել հաղորդակցությունը մեկ այլ սեղանի հետ: Կտտացրեք այն մկնիկի աջ կոճակը: Բաց ընտրացանկում ընտրեք «Պատճեն» կետը: Նշված գործողությունների փոխարեն կարող եք հավաքել տաք ստեղների Ctrl + C- ի այլընտրանքային համադրություն:



2. Հաջորդը, առանց ընտրությունը նույն հատվածից հեռացնելու, կրկին կտտացրեք այն մկնիկի աջ կոճակով: Այս անգամ Գործողության ցանկում կտտացրեք «Արժեք» պատկերակին, որը տեղադրված է Insert Parameters Group- ում:



3. Դրանից հետո, նվիրված սահմաններում բոլոր հղումները կփոխարինվեն ստատիկ արժեքներով:

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