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

Այս ձեռնարկում ես ձեզ ցույց կտամ ամեն ինչ ՝ Excel 2013 -ում առանցքային աղյուսակ ստեղծելու համար ՝ տվյալների բազայի միջոցով մի քանի թերթերի միջոցով:

Տեսահոլովակը

Եթե ​​ցանկանում եք հետևել ձեռնարկին ՝ օգտագործելով ձեր սեփական Excel ֆայլը, կարող եք դա անել: Կամ ներբեռնեք այս ձեռնարկի zip ֆայլը, որը պարունակում է օրինակելի գիրք Առանցքային համախմբում. Xlsx.

Տվյալների ուսումնասիրություն

Այս գրքում կա երեք աշխատանքային թերթ ՝ հաճախորդի մասին տեղեկություններ, պատվերի և վճարման տեղեկություններ:

Սեղմեք թերթիկի վրա Հաճախորդի տեղեկատվությունև համոզվեք, որ այն պարունակում է պատվերի համարներ և հաճախորդների անուն և կարգավիճակ:

Հաճախորդի տեղեկատվական թերթիկ

Սեղմեք թերթիկի վրա Պատվիրեք տեղեկատվությունև տեսեք, որ այն պարունակում է պատվերի համարներ, ինչպես նաև ամսվա դաշտերը, պատվիրված ապրանքները և արդյոք այդ ապրանքներն օրգանական են:

Պատվերի մասին տեղեկատվական թերթիկ

Գնացեք «Վճարման տեղեկատվություն» ներդիրին և հաստատեք, որ այն պարունակում է պատվերի համարներ, յուրաքանչյուր վաճառքի դոլար, վճարման եղանակ և այն, թե ինչպես է նոր կամ գոյություն ունեցող հաճախորդը պատվեր տալիս:

Վճարման տեղեկատվական թերթիկ

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

Ստեղծել անունով աղյուսակներ

Նախքան առանցքային սեղան ստեղծելը, եկեք յուրաքանչյուր թերթիկից ստեղծենք սեղան:

Սեղմեք նորից մեջ Հաճախորդների սեղան, ապա կտտացրեք տվյալների տիրույթի ցանկացած վայրում: Գնալ դեպի ներդիր Տեղադրեքժապավենի բարում և կտտացրեք Սեղան.

Փոխակերպեք տվյալները աշխատանքային թերթիկում ՝ ընտրելով Տեղադրել> Աղյուսակ

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

Ստեղծել աղյուսակ երկխոսությունը պետք է ճիշտ կռահի տվյալների տարածքը

Սեղմեք լավև դուք այժմ ունեք սեղան `փոփոխական հարվածներով և զտիչի կոճակներով: Կարող եք սեղմել ներս ՝ այն ապընտրելու համար, եթե ցանկանում եք ավելի լավ տեսնել (պարզապես սեղանից դուրս մի սեղմեք): Ribապավենի վահանակում ցուցադրվում է նաև ներդիր Դիզայնսեղանի համար: Տուփի մեջ գտնվող ժապավենի ձախ կողմում Աղյուսակի անվանումըցուցադրվում է Table1 ժամանակավոր անունը: Thisնջեք սա և անվանեք Customer_Info(բացատրի փոխարեն օգտագործեք ընդգծում): Սեղմեք Մուտքագրեք.

Կիրառեք անունը յուրաքանչյուր սեղանին

Կրկնեք այս քայլերը Պատվերի և Վճարման տեղեկատվության թերթերի համար: Անվանեք աղյուսակները Պատվիր_Ինֆոեւ Payment_Info.

Այժմ մենք պատրաստ ենք տեղադրել առանցքային աղյուսակը:

Տեղադրելով առանցքային աղյուսակ

Համոզվեք, որ կուրսորը գտնվում է Վճարման տեղեկատվության թերթիկի աղյուսակում: Վերադառնալ էջանիշ Տեղադրեքժապավենը և կտտացրեք պատկերակին Առանցքային սեղան(սա առաջին պատկերակն է):

Ընտրեք աղյուսակներից մեկի ներսում գտնվող կուրսորը Տեղադրել> առանցքային աղյուսակ

Բացվող երկխոսության դաշտում դուք պետք է ճիշտ սահմանեք աղյուսակը և ընտրեք այնպես, որ առանցքային աղյուսակը անցնի նոր աշխատանքային թերթ: Նշեք ներքևի վանդակը Այս տվյալները ավելացրեք Տվյալների մոդելին... Սեղմեք լավ.

Տվյալների մոդելին տվյալների ավելացումն այն է, ինչը ստիպում է կապերը աշխատել

Այժմ դուք կունենաք առանցքային աղյուսակ նոր թերթում, առաջադրանքի տողը կհայտնվի էկրանի աջ կողմում, իսկ «Վերլուծել» ներդիրը ՝ ժապավենի վրա:

Առաջադրանքների տողը ցուցադրում է միայն ակտիվ թերթի աղյուսակը և դաշտերը, այնպես որ կտտացրեք ԲՈԼՈՐԸձեր ստեղծած բոլոր աղյուսակները տեսնելու համար: Բայց նախքան դրանք օգտագործելը, մենք պետք է դրանք կապենք միմյանց հետ, ինչը նշանակում է հարաբերություններ ստեղծել: Սեղմեք կոճակը Հարաբերություններժապավենի վահանակի վրա:

Սեղանի հարաբերություններ հաստատելը

Սեղմելով այս կոճակը ՝ ցուցադրվում է «Կառավարեք հարաբերությունները» երկխոսության տուփը: Սեղմեք կոճակը Նորև հայտնվում է Ստեղծել հարաբերություններ երկխոսության տուփը: Մենք կստեղծենք երկու հարաբերություններ ՝ օգտագործելով Order # դաշտը որպես միակցիչ:

Բացվող ցուցակներից ընտրեք Payment_Infoսեղանի համար, և դրա կողքին ընտրեք Պատվեր #Սյունակի բացվող բաժնում: Երկրորդ տողում ընտրեք Customer_InfoԱռնչվող աղյուսակի բացվող ցուցակից և դրա կողքին ընտրեք Պատվեր # Related Column բացվող ցուցակից:

Կան երեք սեղաններ, այնպես որ ստեղծեք երկու հարաբերություններ

Սա նշանակում է, որ Payment_Info և Customer_Info աղյուսակները կապված են պատվերի համարի համընկման հետ:

Սեղմեք կոճակը լավև մենք կտեսնենք այս հարաբերությունները թվարկված Manage Relationship պատուհանում:

Կրկնեք այս գործընթացը ՝ Payment_Info- ի և Order_Info- ի միջև հարաբերություններ ստեղծելու համար ՝ օգտագործելով նաև Պատվեր # դաշտը: Այժմ Կառավարեք հարաբերություններ պատուհանը այս տեսքն ունի.

Հարաբերությունների կառավարում երկխոսությունը ցուցադրում է ձեր ստեղծած հարաբերությունները

Նկատի ունեցեք, որ Order_Info և Customer_Info սեղանների միջև հարաբերություններ ստեղծելու կարիք չկա, քանի որ դրանք ինքնաբերաբար միանում են Payment_Info աղյուսակի միջոցով:

Սեղմեք կոճակը փակելպատուհանի ներքևում: Այժմ մենք վերջապես կարող ենք դաշտերը քաշել և թողնել առանցքային աղյուսակի մեջ:

Տեղադրելով դաշտերը PivotTable- ում

Առաջադրանքների տողի ԲՈԼՈՐ բաժնում կտտացրեք փոքր սլաքների վրա ՝ երեք աղյուսակները ընդլայնելու համար ՝ դրանց դաշտերը տեսնելու համար: Քաշեք առանցքային աղյուսակի դաշտերը հետևյալ կերպ.

• Պետությունեւ Ամիսգծերի մեջ
• Արտադրանքսյունակներում
• $ Վաճառքարժեքների մեջ
• Կարգավիճակըֆիլտրերի մեջ

Երեք աղյուսակներից յուրաքանչյուրի դաշտերը քաշեք դեպի առանցքային սեղան

Այժմ դուք կարող եք օգտագործել և փոփոխել այն, ինչպես ցանկացած այլ առանցքային աղյուսակ:

Եզրակացություն

Օգտագործելով նոր գործառույթ Object Data Model- ը Excel 2013 -ում, կարող եք վարդագույն տուփեր ընտրել բազմաթիվ թերթերից ՝ մեկ PivotTable ստեղծելու համար: Հիշեք, որ յուրաքանչյուր սեղանի տողերը պետք է ինչ -որ կերպ կապված լինեն միմյանց հետ: Ավելի հավանական է, որ դուք հաջողության հասնեք, երբ աղյուսակները կիսեն եզակի արժեքներով ընդհանուր դաշտ:

Եթե ​​դուք փնտրում եք լավ եղանակներիր տվյալների ներկայացմանը, Envato Market- ն ունի լավ ընտրությունինչպես նաև փոխակերպվել Excel- ի տվյալներըդեպի վեբ ձևաչափեր և հակառակը:

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

Այսպիսով, կան երկու Excel թերթեր, որոնք պետք է համակցվեն տվյալների հետագա վերլուծության համար: Ենթադրենք, մեկ աղյուսակը պարունակում է ձեր վաճառվող գները (Գինը սյունակը) և ապրանքների նկարագրությունը (Գարեջրի սյունակ), իսկ երկրորդը `պահեստում ապրանքների առկայության վերաբերյալ տվյալները (Պահեստավորված սյունակ): Եթե ​​դուք կամ ձեր գործընկերները կազմել եք կատալոգի երկու աղյուսակները, ապա երկուսն էլ պետք է պարունակեն առնվազն մեկ առանցքային սյուն `ապրանքների եզակի նույնացուցիչներով: Ապրանքի նկարագրությունը կամ գինը կարող են փոխվել, սակայն եզակի նույնացուցիչը միշտ մնում է նույնը:

Դժվարությունները սկսվում են, երբ որոշ սեղաններ եք ստանում արտադրողից կամ ընկերության այլ ստորաբաժանումներից: Գործը կարող է էլ ավելի բարդանալ, եթե այն հանկարծակի ներկայացվի նոր ձևաչափեզակի նույնացուցիչներ կամ SKU- ի շատ փոքր փոփոխություն: Եվ դուք կանգնած եք միավորվելու առաջադրանքի առջև Excel նորև հին տվյալների աղյուսակը: Այսպես թե այնպես, իրավիճակ է ստեղծվում, երբ առանցքային սյունակներում գրանցումների միայն մասնակի համընկնում կա, օրինակ ՝ « 12345 "և" 12345-new_suffix« Ձեզ համար պարզ է, որ սա նույն SKU- ն է, բայց համակարգիչն այնքան էլ արագաշարժ չէ: Սա ոչ ճշգրիտ համընկնում է դարձնում անհնարին կանոնավոր օգտագործումը Excel բանաձեւերերկու աղյուսակներից տվյալների համադրման համար:

Եվ ինչն է իսկապես վատ. Համապատասխանությունները կարող են ամբողջովին անհասկանալի լինել, և « Ինչ -որ ընկերություն«Մեկ սեղանի մեջ կարող է դառնալ» «Որոշ ընկերություններ» ՓԲԸ«Մեկ այլ սեղանի վրա և Նոր ընկերություն(նախկինում ինչ -որ ընկերություն)"և" Հին ընկերություն«Նաև կդառնա ռեկորդ նույն ընկերության մասին: Դուք գիտեք սա, բայց ինչպե՞ս կարող է դա բացատրել Excel- ը:

Մեկնաբանություն:Այս հոդվածում նկարագրված լուծումները համընդհանուր են: Դուք կարող եք դրանք հարմարեցնել հետագա օգտագործման համար ցանկացած ստանդարտ բանաձևերի հետ, ինչպիսիք են VLOOKUP(VLOOKUP), ՓՈԽՈՄ(ՀԱՄԱԿԱՐԳ), GPR(HLOOKUP) և այլն:

Ընտրեք համապատասխան օրինակը ՝ անմիջապես ձեր ուզած լուծմանը անցնելու համար.

Աղյուսակներից մեկի հիմնական սյունակը պարունակում է լրացուցիչ նիշ

Դիտարկենք երկու սեղան: Առաջին սեղանի սյուները պարունակում են SKU, գարեջրի անվանումը (Գարեջուր) և դրա գինը (Գինը): Երկրորդ աղյուսակը պարունակում է SKU և պահեստավորված շշերի քանակը (Պահեստում): Գարեջրի փոխարեն կարող է լինել ցանկացած ապրանք, և ներսում սյունակների քանակը իրական կյանքկարող էր շատ ավելին լինել:

Աղյուսակում ստեղծեք օժանդակ սյուն `լրացուցիչ նիշերով: Դուք կարող եք այն ավելացնել աղյուսակի վերջում, բայց ամենալավն այն տեղադրեք առանցքային սյունակից անմիջապես աջ կողմում, որպեսզի այն տեսանելի լինի:

Մեր օրինակի աղյուսակի բանալին սյունակն է Ա SKU տվյալների հետ, և դուք պետք է հանեք առաջին 5 նիշը դրանից: Եկեք ավելացնենք օգնականի սյունակը և անվանենք այն SKU օգնական:

Պատրաստ է Այժմ մենք ունենք ճշգրիտ համընկնումների հիմնական սյունակներ `սյունակ SKU օգնականհիմնական աղյուսակում և սյունակում SKUաղյուսակում, որտեղ կկատարվի որոնումը:

Այժմ օգտագործելով գործառույթը VLOOKUP(VLOOKUP) մենք ստանում ենք ցանկալի արդյունքը.

Այլ բանաձևեր

• Հանել առաջինը ԱԱնիշերն աջից. օրինակ ՝ 6 նիշ աջից «DSFH-164900»-ից: Բանաձևը այսպիսին կլինի.

  RԻՇՏ (A2, 6)
  = IGHԻՇՏ (A2,6)

• Բաց թողնել առաջինը ԱԱնիշերը, հանեք հետևյալը Յնիշեր. օրինակ ՝ դուք պետք է հանեք «0123» - ը «PREFIX_0123_SUFF» ձայնագրությունից: Այստեղ մենք պետք է բաց թողնենք առաջին 8 նիշերը և հանենք հաջորդ 4 նիշերը: Բանաձևը այսպիսին կլինի.

  PSTR (A2; 8; 4)
  = MID (A2,8,4)

• Հանել բոլոր նիշերը մինչև սահմանազատիչը, արդյունքում հաջորդականության երկարությունը կարող է տարբեր լինել: Օրինակ, համապատասխանաբար «123456-վերջածանց» և «0123-վերջածանց» գրառումներից անհրաժեշտ է հանել «123456» և «0123»: Բանաձևը այսպիսին կլինի.

  ՁԱՅՆ (A2; ԳՏՆԵԼ (" -"; A2) -1)
  = ՁԱՅՆ (A2, ԳՏՆԵԼ (" -", A2) -1)

Մի խոսքով, դուք կարող եք օգտագործել այդպիսի Excel գործառույթներ, ինչպե՞ս ԼԵՎՍԻՄՎ(ՁԱԽ), ՃԻՇՏ(ՃԻՇՏ), PSTR(MID), ԳՏՆԵՔ(ԳՏՆԵԼ) ՝ բաղադրյալ ինդեքսի ցանկացած հատված առբերելու համար: Եթե ​​դրա հետ կապված որևէ դժվարություն ունեք, մենք կանենք հնարավորը ՝ ձեզ օգնելու համար:

Առաջին աղյուսակի առանցքային սյունակի տվյալները երկրորդ աղյուսակի երկու կամ ավելի սյուների են բաժանվում

Ենթադրենք, որ որոնվող աղյուսակը պարունակում է նույնացուցիչներով սյունակ: Այս սյունակի բջիջները պարունակում են ձևի գրառումներ XXXX-YYYY, որտեղ XXXX- սա մի խումբ ապրանքների ծածկագրի նշում է ( Բջջային հեռախոսները, Հեռուստացույցներ, տեսախցիկներ, տեսախցիկներ) և YYYY- Սա խմբի ներսում արտադրանքի կոդն է: Հիմնական աղյուսակը բաղկացած է երկու սյունակից. Մեկը պարունակում է ապրանքային խմբերի կոդերը (Խումբ), երկրորդը `ապրանքների կոդերը (ID): Մենք չենք կարող պարզապես հրաժարվել ապրանքային խմբերի կոդերից, քանի որ նույն ապրանքի կոդը կարող է կրկնվել տարբեր խմբերում:

Խցում C2մենք գրում ենք հետևյալ բանաձևը.

ՀԱՄԱԿԱՐԳ (A2; "-"; B2)
= CONCATENATE (A2, "-", B2)

Այստեղ Ա 2- սա խմբի կոդը պարունակող բջիջի հասցեն է. խորհրդանիշ " – «Առանձնացնող է. B2Արդյո՞ք բջջի հասցեն պարունակում է ապրանքի կոդը: Եկեք պատճենենք բանաձևը մնացած տողերում:

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

Հիմնական սյունակների տվյալները չեն համընկնում

Ահա մի օրինակ. Դուք փոքր խանութի սեփականատեր եք, ապրանքներ եք ստանում մեկ կամ մի քանի մատակարարներից: Նրանցից յուրաքանչյուրն ունի իր նոմենկլատուրան, որը տարբերվում է ձերից: Արդյունքում, կան իրավիճակներ, երբ ձեր «Case-Ip4S-01» գրառումը համապատասխանում է «SPK-A1403» ռեկորդին Excel ֆայլստացվել է մատակարարից: Նման անհամապատասխանություններ առաջանում են պատահականորեն և չկա ընդհանուր կանոն«SPK-A1403»-ն ինքնաբերաբար փոխակերպելու «Case-Ip4S-01»-ի:

Վատ լուրեր:Այս երկուսում պարունակվող տվյալները Excel աղյուսակներ, պետք է ձեռքով մշակվեն, որպեսզի հետագայում հնարավոր լինի դրանք համատեղել:

Լավ լուր:Դուք պետք է դա անեք միայն մեկ անգամ, և արդյունքում ստացված օժանդակ սեղանը կարող է պահվել հետագա օգտագործման համար: Այնուհետև կարող եք ինքնաբերաբար համատեղել այս սեղանները և դրանով իսկ խնայել շատ ժամանակ:

1. Ստեղծեք օժանդակ որոնման աղյուսակ:

Ստեղծեք նորը Excel թերթիկև զանգահարեք այն SKU փոխարկիչ... Պատճենեք ամբողջ սյունակը Մեր. SKUթերթից Խանութնոր թերթի վրա, հեռացրեք կրկնօրինակները և թողեք դրանում միայն եզակի արժեքներ:

Կողքին ավելացնել սյունակ Supp.SKUև ձեռքով փնտրում համապատասխանություններ սյունակի արժեքների միջև Մեր. SKUեւ Supp.SKU(սյունակի նկարագրությունները մեզ կօգնեն դրանում: Նկարագրություն): Սա ձանձրալի աշխատանք է, թող այն միտքը, որ պետք է դա անել միայն մեկ անգամ, ձեզ ուրախացնի :-):

Արդյունքում մենք ունենք հետևյալ աղյուսակը.

2. Թարմացրեք հիմնական աղյուսակը որոնման աղյուսակի տվյալներով:

Տեղադրեք նոր սյունակ հիմնական աղյուսակում (Պահեստավորված թերթ) Supp.SKU.

Ավելին, օգտագործելով գործառույթը VLOOKUP(VLOOKUP) համեմատել թերթերը Խանութեւ SKU փոխարկիչ Մեր. SKU, իսկ թարմացված տվյալների համար `սյունակը Supp.SKU.

Սյունակ Supp.SKUլրացված արտադրողի օրիգինալ ծածկագրերով:

Մեկնաբանություն:Եթե ​​սյունակը Supp.SKUկան դատարկ բջիջներ, ապա պետք է վերցնել բոլոր կոդերը SKUսրանց համապատասխան դատարկ բջիջներ, ավելացնել դրանք սեղանին SKU փոխարկիչև գտնել համապատասխան ծածկագիրը մատակարարի աղյուսակից: Այնուհետև կրկնում ենք 2 -րդ քայլը:

3. Մենք տվյալները փոխանցում ենք որոնման աղյուսակից հիմնական աղյուսակին

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

Օգտագործելով գործառույթը VLOOKUP(VLOOKUP) միավորել թերթի տվյալները Խանութթերթի տվյալներով Մեծածախ մատակարար 1օգտագործելով սյունակը համընկնումներ որոնելու համար Supp.SKU.

Ահա սյունակում թարմացված տվյալների օրինակ Մեծածախ գինը:

Պարզ է, այնպես չէ՞: Ձեր հարցերը տվեք հոդվածի մեկնաբանություններում, ես կփորձեմ հնարավորինս արագ պատասխանել:

Առանցքային աղյուսակները օգտակար են տվյալների վերլուծության և դրանցով հաշվետվություններ ստեղծելու համար: Եվ եթե դա հարաբերական տվյալներ են (այսինքն ՝ տվյալներ, որոնք պահվում են առանձին աղյուսակներում, բայց կարող են համակցվել ընդհանուր արժեքների պատճառով), ապա ընդամենը մի քանի րոպեում կարող եք ստեղծել այսպիսի առանցքային աղյուսակ.

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

PivotTable դաշտերի ցանկում բազմաթիվ աղյուսակներ համատեղելու համար.

Ստորև բերված է տվյալների բազայից բազմաթիվ աղյուսակների ներմուծման կարգը SQL տվյալներՍերվեր.

Խնդրում ենք նկատի ունենալ. Դաշտերի ցանկը պարունակում է մի քանի աղյուսակ: Սրանք այն բոլոր աղյուսակներն են, որոնք ընտրել եք ներմուծման ընթացքում: Յուրաքանչյուր աղյուսակ կարող է ընդլայնվել և փլուզվել ՝ իր դաշտերը դիտելու համար: Քանի որ աղյուսակները կապված են, կարող եք ստեղծել առանցքային աղյուսակ ՝ դաշտերը ցանկացած սեղանից քարշ տալով տարածք ԱՐUԵՔՆԵՐ, ԳԻկամ Սյունակներ.

Աղյուսակների ներմուծում այլ աղբյուրներից

Բացի SQL Server- ից, կարող եք աղյուսակներ ներմուծել մի շարք այլ հարաբերական տվյալների շտեմարաններից:

Հարաբերական տվյալների բազաները տվյալների միակ աղբյուրը չեն, որոնք ապահովում են PivotTable դաշտերի ցանկի բազմաթիվ աղյուսակներ: Դուք կարող եք օգտագործել աղյուսակներ ձեր աշխատանքային գրքում կամ կարող եք ներմուծել տվյալների հոսքեր, այնուհետև դրանք ինտեգրել ձեր աշխատանքային գրքույկի այլ տվյալների աղյուսակներին: Որպեսզի այս բոլոր չկապված տվյալները միասին աշխատեն, յուրաքանչյուր աղյուսակ ավելացնում եք տվյալների մոդելին, այնուհետև նրանց միջև հարաբերություններ եք ստեղծում ՝ օգտագործելով համապատասխան դաշտային արժեքները:

Տվյալների մոդելի օգտագործումը ՝ նոր առանցքային աղյուսակ ստեղծելու համար

Հավանաբար, դուք ստեղծել եք հարաբերություններ Տվյալների մոդելների աղյուսակների միջև և այժմ պատրաստ եք դրանք օգտագործել վերլուծության համար: Ստորև նկարագրված է, թե ինչպես կարելի է ստեղծել նոր PivotTable կամ PivotChart ՝ աշխատանքային գրքում առկա տվյալների մոդելի միջոցով:

Շատ հաճախ, մեծ քանակությամբ տվյալների հիման վրա հաշվետվություններ (վերլուծություններ) կազմելիս պետք է Excel- ի աշխատանքային գրքույկի մի քանի թերթերից տեղեկատվությունը մեկ սեղանի մեջ ամփոփել:
Իդեալում, Excel- ի տարբեր թերթերից տեղեկատվություն հավաքելու և վերլուծելու համար ցանկալի է կազմել առանցքային աղյուսակ, որը տվյալներ կվերցնի մի քանի աղբյուրներից (գրքի թերթեր):

Այս աղյուսակը կարող է կատարվել երկու տարբեր եղանակներով:

Դիտարկենք առաջին ուղին:

Ստեղծեք առանցքային աղյուսակ տարբեր թերթերից `օգտագործելով ստանդարտ հնարավորություններ և գործիքներ:

Քայլ առաջին.

Գործիքադարակում անհրաժեշտ է ավելացնել PivotTable և Chart Wizard կոճակը:

Դա անելու համար աջ սեղմեք ժապավենի (գործիքագոտու) վրա և բացվող ընտրացանկից ընտրեք «Անհատականացնել ժապավենը» կետը:

կամ մուտքագրեք ներդիրը

Ֆայլ => Ընտրանքներ => Անհատականացնել ժապավենը:

Հաջորդը, ժապավենի պարամետրերում, «Ընտրել հրամաններ» պիտակի ներքևի բացվող ցուցակից, ընտրեք «Բոլոր հրամանները» տարրը: Ստորև բերված պատուհանը կցուցադրի բոլոր հնարավոր հրամանները, որոնք կարող են տեղադրվել վահանակի վրա ՝ այբբենական կարգով:

Theանկից ընտրեք «Առանցքային աղյուսակներ և գծապատկերների հրաշագործ»

Աջ պատուհանում, օգտագործելով «Ստեղծել խումբ» կոճակը, ստեղծեք նոր խումբգործիքներ: Խմբի համար կարող եք ընտրել ձեզ հարմար անուն: Օրինակ ՝ «Սեփական խումբ»: Կարող եք ընտրել, թե որ ներդիրի վրա է ստեղծվելու խումբը: Իմ օրինակում ես ընտրեցի Գլխավոր էջանիշը:

Երբ խումբը ստեղծվում է, ընտրեք այն կուրսորը, ձախ պատուհանում նշեք «PivotTable and Chart Wizard» և կտտացրեք «Ավելացնել >> կոճակին:

Այնուհետեւ կտտացրեք «Լավ»:

PivotTable and Chart Wizard- ն այժմ գործիքագոտու հիմնական ներդիրում է:

Քայլ երկրորդ. Ստեղծեք առանցքային աղյուսակ տվյալների բազմաթիվ աղբյուրներից:

Երկրորդ ճանապարհ.

Ստեղծելով աղյուսակ, որը տվյալներ է վերցնում բազմաթիվ թերթերից ՝ օգտագործելով Power Query հավելման մեջ ստեղծված հարցումը:

Այս մեթոդըէ օգտագործել Power Query հավելման հարցումը:

Ստեղծեք Power Query ՝ աշխատանքային գրքի բազմաթիվ էջերը մեկ սեղանի մեջ հարթեցնելու համար:

Քայլ առաջին.

Դուք պետք է ստեղծեք երկու հարցում, որոնցից յուրաքանչյուրը տեղեկատվություն կվերցնի առանձին աղյուսակից:

Քայլ երկրորդ.

Դա անելու համար Power Query ներդիրում անհրաժեշտ է սեղմել «Աղյուսակից» կոճակը և պատուհանում, որը հայտնվում է, սահմանել ՝ տվյալների աղբյուրը: Այնուհետեւ կտտացրեք «Լավ»:

Քայլ երեք.

Երբ երկրորդ հարցումը ստեղծվում է, դուք պետք է կտտացրեք «Հարցումների միաձուլում» կոճակին Power Query ներդիրում և կարգավորեք ստացված ընդհանուր աղյուսակի տեսքը հայտնված պատուհանում:

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

Նկարը ցույց է տալիս այն սկզբունքը, որը ես նկարագրեցի: Նրանք A2: E5 տիրույթը սկզբնական առանցքային աղյուսակն է, որը փոխարկվում է տվյալների ցանկի (միջակայք H2: J14): Երկրորդ աղյուսակը ներկայացնում է նույն տվյալների հավաքածուն, միայն այլ տեսանկյունից: Սկզբնական առանցքային աղյուսակի յուրաքանչյուր արժեք հայտնվում է որպես տող, որը բաղկացած է տողի դաշտի տարրից, սյունակի դաշտից և դրանց համապատասխան արժեքից: Տվյալների այս ցուցադրումը օգտակար է այն դեպքում, երբ անհրաժեշտ է տվյալները դասավորել և շահարկել այլ եղանակներով:

Նման ցանկ ստեղծելու ունակությունն իրականացնելու համար մենք կօգտագործենք առանցքային աղյուսակի գործիքները: Եկեք մի կոճակ ավելացնենք Առանցքային սեղանի հրաշագործարագ մուտք գործելու վահանակին, որը մեզ հասանելի չէ ժապավենի վրա, բայց մնացել է որպես ավելի շատերից որպես սկզբնաղբյուր վաղ տարբերակները Excel

Գնալ դեպի ներդիր Ֆայլ -> Ընտրանքներ... Երկխոսության դաշտում, որը հայտնվում է ԸնտրանքներExcel,ներդիրում Պանել արագ մուտք ձախ լուսանցքում գտեք տարրը PivotTable and Chart Wizardև ավելացրեք այն ճիշտին: Սեղմեք OK:

Այժմ դուք ունեք նոր պատկերակ Արագ մուտքի գործիքագոտում:

Կտտացրեք այս ներդիրին ՝ գործարկելու համար Առանցքային սեղանի հրաշագործ:

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

2a քայլում նշեք, թե ինչպես ստեղծել էջի լուսանցքներ: Տեղադրեք անջատիչը Ստեղծեք էջի դաշտեր -> Հաջորդ:

2 բ քայլում, դաշտում Միջակայքընտրեք տվյալները պարունակող տիրույթը և կտտացրեք Ավելացնել:Մեր դեպքում սա կլինի սկզբնական առանցքային A1: E4 աղյուսակի գտնվելու վայրը:

Երրորդ քայլում դուք պետք է որոշեք, թե որտեղ եք ցանկանում տեղադրել առանցքային աղյուսակը և կտտացրեք կոճակին Պատրաստ է:

Excel- ը կստեղծի առանցքային աղյուսակ ՝ տվյալներով: Էկրանի ձախ կողմում կտեսնեք տարածքը Առանցքային աղյուսակի դաշտերի ցանկ:Հեռացրեք բոլոր տարրերը տողի և սյունակի դաշտերից: Ես դրա մասին ավելի մանրամասն գրել եմ նախորդ հոդվածում:

Դուք կավարտեք փոքր մեկ բջիջի առանցքային աղյուսակով, որը պարունակում է սկզբնական աղյուսակի բոլոր արժեքների գումարը:

Կրկնակի սեղմեք այս բջիջի վրա: Excel- ը կստեղծի նոր թերթ, որը կպարունակի աղյուսակ արժեքների ցանկով:

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