Ինչպես կառուցել առանցքային աղյուսակ Excel-ում: Ինչպես աշխատել տվյալների հետ

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

Աղբյուրի տվյալների պահանջները.

Այսպիսով, ցանկացած առանցքային աղյուսակի հիմքը տվյալների ճիշտ կառուցված զանգվածն է՝ «ճիշտ աղյուսակը»: Ստորև բերված նկարում կարող եք տեսնել ճիշտ ձևավորված տվյալների զանգվածի օրինակ.

Տեսնենք, թե իրականում ո՞րն է այս աղյուսակի «ճիշտությունը»։ Ճիշտն այն է, որ.

• Յուրաքանչյուր սյունակ պարունակում է միայն նույն տեսակի տվյալներ՝ սյունակը Ամիայն Ամսաթվերը, սյունակում INմիայն Փաստաթղթեր, սյունակում ՀԵՏմիայն Հաճախորդներ, Money in Money, Suppliers in Suppliers, Product Categories in Categories եւ այլն... Նման աղյուսակում մենք կարող ենք շատ հեշտությամբ օգտագործել Զտել;
• Նույն տեսակի տվյալների հետ սյունակները չեն կրկնվում.
• Աղյուսակում ընդհանուր տողեր չկան, միայն «մաքուր տվյալներ».
• Աղյուսակի տեքստային տվյալների մեջ դատարկ բջիջներ չկան, յուրաքանչյուր տող պարունակում է Հաճախորդի, Ապրանքի, Մատակարարի, Կառավարչի և այլնի անունները...

Ի դեպ, զանգվածում ընդհանրապես նշանակություն չունի, թե ինչ հաջորդականությամբ են գտնվում սյունակները և ինչ դաշտով են դասավորված տվյալները։ Սա որևէ կերպ չի ազդում Pivot Table-ի կառուցման վրա:

Եվ ահա «սխալ աղյուսակի» օրինակ, որից, եթե անգամ ճեղքեք, Pivot Table-ը չի կառուցվի, իսկ եթե անգամ կառուցվի, տվյալների հետ աշխատելը բոլորովին անհնար կլինի... Մեր « ֆավորիտ» հաշվապահական համակարգերը սովորաբար մեզ «հաճեցնում են» այսպիսի աղյուսակներով, որոնք մեզ տալիս են դրանք հետագա վերլուծության համար բացարձակապես ոչ պիտանի հաշվետվությունների տեսքով.

Ահա «Անվավեր զանգվածի» մեկ այլ օրինակ.

Այստեղ «անկանոնությունների» մի ամբողջ փունջ կա.

• Նախ, «Մատակարար» և «Կատեգորիա» սյունակներում կան դատարկ բջիջներ, ուստի մենք չենք կարող օգտագործել զտիչը.
• Երկրորդ, գրեթե յուրաքանչյուր սյունակում կա «Ընդամենը...» տողերը, դրանք բոլորովին ավելորդ են առանցքային աղյուսակը կառուցելու համար, ավելին, դրանք միայն կխանգարեն.
• Երրորդ, «Grand Total» սյունակը նույնպես անհրաժեշտ չէ.
• Չորրորդ, նույն տեսակի տվյալները, այն է՝ «Փողը», գտնվում է երեք սյունակում՝ «Յան», «Փետրվար» և «Մար», ինչը զգալիորեն կբարդացնի առանցքային աղյուսակի կառուցումը, ինչը նշանակում է, որ մենք ստիպված կլինենք. մի բան արեք դրա հետ...

Բայց այս ամենը առանձին զրույցի համար է, եթե ցանկանում եք սովորել, թե ինչպես արագ վերածել նման «ծուռ աղյուսակները» «ճիշտ զանգվածների», կարդացեք հոդվածները. «Ինչպե՞ս արագ կառուցել առանցքային աղյուսակ 1C կամ SAP զեկույցից»: և «Ինչպե՞ս արագորեն աղյուսակը վերածել առանցքային աղյուսակների զանգվածի»:

Փաստորեն, առանցքային աղյուսակի կառուցում.

Վերցրեք «ճիշտ զանգվածը», տեղադրեք կուրսորը զանգվածի ցանկացած բջիջում, հիմնական ընտրացանկում ընտրեք «Տեղադրեք» ներդիրը, ձախ անկյունում, «Սեղաններ» բաժնում, սեղմեք «Առանցքային աղյուսակ» կոճակը.

«Ստեղծել առանցքային աղյուսակ» երկխոսության դաշտում, որը բացվում է, սեղմեք «OK»:

MS Excel-ը կստեղծի նոր թերթ, որի վրա կնշի այն վայրը, որտեղ տեղադրվելու է Pivot Table-ը, իսկ աջ կողմում կցուցադրվի Pivot Table դաշտերը կարգավորելու պատուհան, որում կտեսնեք սյունակների բոլոր անունները: ձեր զանգվածը:

Սկսենք կարգավորել Pivot Table-ը: Ինչպիսի կտրվածք ենք ուզում: Ապրանքի կատեգորիաներ ըստ մենեջերի - խնդրում եմ: Մկնիկը տեղափոխեք «Կատեգորիա» դաշտի վրայով, սեղմեք դրա վրա մկնիկի ձախ կոճակով և քաշեք այն «ՏԱՐԵՐ» դաշտ: «Կառավարիչ» դաշտը քաշվում է «ՍՅՈՒՆՆԵՐ», իսկ «Գումարը»՝ «ԱՐԺԵՔՆԵՐ»՝

Pivot աղյուսակները MS Excel-ի ամենաարդյունավետ գործիքներից են: Նրանց օգնությամբ դուք կարող եք տվյալների միլիոնավոր տողերը վայրկյանների ընթացքում վերածել հակիրճ հաշվետվության: Արդյունքների արագ ամփոփումից բացի, առանցքային աղյուսակները թույլ են տալիս բառացիորեն փոխել ձեր վերլուծության ձևը՝ քաշելով դաշտերը հաշվետվության մի տարածքից մյուսը:

Pivot աղյուսակները նաև Excel-ի ամենաթերագնահատված գործիքներից են: Օգտատերերի մեծամասնությունը տեղյակ չէ այն հնարավորությունների մասին, որոնք իրենց ձեռքում են: Պատկերացնենք, որ առանցքային աղյուսակները դեռ չեն հորինվել։ Դուք աշխատում եք մի ընկերությունում, որն իր արտադրանքը վաճառում է տարբեր հաճախորդների: Պարզության համար տեսականու մեջ կա ընդամենը 4 ապրանք։ Ապրանքները պարբերաբար գնում են տարբեր մարզերում գտնվող մի քանի տասնյակ հաճախորդներ։ Յուրաքանչյուր գործարք մուտքագրվում է տվյալների բազա և ներկայացնում է առանձին տող:

Ձեր տնօրենը ձեզ հանձնարարում է համառոտ հաշվետվություն կազմել բոլոր ապրանքների վաճառքի վերաբերյալ՝ ըստ տարածաշրջանի (տարածաշրջանի): Խնդիրը կարող է լուծվել հետևյալ կերպ.

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

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

Դուք կատարել եք առաջադրանքը և հաշվետվությունը ցույց տվեք տնօրենին: Նայելով սեղանին՝ նա միանգամից մի քանի հիանալի գաղափար է առաջացնում:

— Հնարավո՞ր է հաշվետվություն կազմել ոչ թե եկամտի, այլ շահույթի մասին։

— Հնարավո՞ր է ապրանքները ցույց տալ ըստ տողերի և շրջաններն ըստ սյունակների:

— Հնարավո՞ր է նման աղյուսակներ ստեղծել յուրաքանչյուր մենեջերի համար առանձին։

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

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

Եթե ​​չգիտեք, թե ինչպես կազմակերպել առկա տվյալները, կարող եք օգտագործել հրամանը Առաջարկվող առանցքային աղյուսակներ. Excel-ը ցույց կտա ձեր տվյալների հիման վրա հնարավոր դասավորությունների մանրապատկերները:

Սեղմեք համապատասխան տարբերակի վրա և առանցքային աղյուսակը պատրաստ է: Մնում է դա հիշել, քանի որ... Քիչ հավանական է, որ ստանդարտ պատրաստումը լիովին համընկնի ձեր ցանկությունների հետ: Եթե ​​Ձեզ անհրաժեշտ է կառուցել առանցքային աղյուսակ զրոյից, կամ դուք ունեք ծրագրի հին տարբերակը, ապա սեղմեք կոճակը: Կհայտնվի պատուհան, որտեղ դուք պետք է նշեք աղբյուրի տիրույթը (եթե ակտիվացնեք Excel աղյուսակի որևէ բջիջ, այն ինքնին կորոշվի) և ապագա առանցքային աղյուսակի գտնվելու վայրը (նոր թերթիկը կընտրվի լռելյայն):

Շատ հաճախ այստեղ որևէ բան փոխելու կարիք չկա։ Լավ սեղմելուց հետո կստեղծվի նոր Excel թերթ՝ դատարկ PivotTable դասավորությամբ:

Նախքան կարգավորումներին անցնելը, եկեք ծանոթանանք ինտերֆեյսին և հիմնական հասկացություններին։ Սեղանի դասավորությունը կարգավորելի է վահանակում Պտտեք աղյուսակի դաշտերը, որը գտնվում է թերթի աջ կողմում։

Վահանակի վերևում կա բոլոր հասանելի դաշտերի ցանկը, այսինքն՝ սկզբնաղբյուրի տվյալների սյունակները: Եթե ​​Ձեզ անհրաժեշտ է նոր դաշտ ավելացնել դասավորությանը, կարող եք ստուգել դրա կողքին գտնվող վանդակը. Excel-ն ինքն է որոշելու, թե որտեղ պետք է տեղադրվի այս դաշտը: Այնուամենայնիվ, դա միշտ չէ, որ ճիշտ է, ուստի ավելի լավ է մկնիկը քաշել դասավորության ցանկալի տեղը: Դաշտերը կարող են ջնջվել նաև՝ հեռացնելով կամ հետ քաշելով դրանք:

Առանցքային աղյուսակը բաղկացած է 4 հատվածից, որոնք գտնվում են վահանակի ներքևում՝ արժեքներ, տողեր, սյունակներ, զտիչներ: Եկեք մանրամասն նայենք դրանց նպատակին:

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

Դուք կարող եք օգտագործել հաշվարկման այլ մեթոդներ PivotTable բջիջներում: Կան մոտ 20 տեսակ (միջին, նվազագույն արժեք, համամասնություն և այլն)։ Հաշվարկի մեթոդը փոխելու մի քանի եղանակ կա. Ամենապարզը հենց առանցքային աղյուսակի ցանկալի դաշտի ցանկացած բջիջի վրա մկնիկի աջ սեղմումն է և ընտրելու այլ ագրեգացման մեթոդ:

Գծի տարածք- այն տողերի անունները, որոնք գտնվում են ամենաձախ սյունակում: Սրանք բոլոր ընտրված դաշտի (սյունակի) եզակի արժեքներն են: Շարքի տարածքում կարող են լինել մի քանի դաշտեր, ապա աղյուսակը դառնում է բազմամակարդակ: Այստեղ սովորաբար տեղադրվում են որակական փոփոխականներ, ինչպիսիք են ապրանքների անվանումները, ամիսները, տարածաշրջանները և այլն:

Սյուների տարածքը- տողերի նման, ցույց է տալիս ընտրված դաշտի եզակի արժեքները միայն սյունակներում: Սյունակների անվանումները նույնպես սովորաբար որակական ցուցանիշ են: Օրինակ՝ տարիներ և ամիսներ, ապրանքային խմբեր։

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

Նշված տարածքներում դաշտեր ավելացնելով և հեռացնելով՝ դուք կարող եք վայրկյանների ընթացքում հարմարեցնել ձեր ուզած տվյալների ցանկացած հատված:

Տեսնենք, թե ինչպես է սա աշխատում գործողության մեջ: Առայժմ եկեք ստեղծենք նույն աղյուսակը, որն արդեն ստեղծվել է SUMIFS ֆունկցիայի միջոցով: Դա անելու համար քաշեք տարածք Արժեքներդաշտում «Եկամուտներ», տարածքում Լարայիններքաշել «Տարածաշրջան» դաշտը (վաճառքի շրջան): Սյունակներ- «Ապրանք».

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

Այն կառուցելու համար տառացիորեն պահանջվել է 5-10 վայրկյան: Գոյություն ունեցող առանցքային աղյուսակը փոփոխելը նույնպես հեշտ է: Տեսնենք, թե ինչպես են ռեժիսորի ցանկությունները հեշտությամբ վերածվում իրականության։

Եկամուտը փոխարինենք շահույթով։

Ապրանքները և տարածքները կարող են փոխանակվել նաև մկնիկը քաշելով:

Առանցքային աղյուսակները զտելու համար հասանելի են մի քանի գործիքներ: Այս դեպքում մենք պարզապես կտեղադրենք «Կառավարիչ» դաշտը ֆիլտրի տարածքում:

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

Նախնական տվյալներ

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

1. Տվյալների համար լավագույն ձևաչափը Excel աղյուսակն է: Դա լավ է, քանի որ յուրաքանչյուր դաշտ ունի անուն, և երբ նոր տողեր են ավելացվում, դրանք ավտոմատ կերպով ներառվում են առանցքային աղյուսակում:

2. Խուսափեք սյունակներում խմբերի կրկնությունից: Օրինակ, բոլոր ամսաթվերը պետք է լինեն մեկ դաշտում, այլ ոչ թե բաժանվեն, օրինակ, ըստ ամիսների առանձին սյունակներում:

3. Հեռացրեք բացերը և դատարկ բջիջները, հակառակ դեպքում այս տողը կարող է դուրս մնալ վերլուծությունից:

4. Կիրառեք համապատասխան ձևաչափում դաշտերին: Թվերը պետք է լինեն թվային ձևաչափով, ամսաթվերը պետք է լինեն ամսաթվեր: Հակառակ դեպքում խնդիրներ կառաջանան խմբավորման եւ մաթեմատիկական մշակման ժամանակ։ Բայց Excel-ը ձեզ կօգնի այստեղ, քանի որ... Այն բավականին լավ է որոշում տվյալների ձևաչափը։

Ընդհանուր առմամբ, պահանջները քիչ են, բայց դուք պետք է իմանաք դրանք։

Եթե ​​դուք փոփոխություններ եք կատարում սկզբնաղբյուրում (օրինակ՝ ավելացնում եք նոր տողեր), ապա առանցքային աղյուսակը չի փոխվի, քանի դեռ չեք թարմացնել այն՝ օգտագործելով մկնիկի աջ կոճակը:

կամ
ներդիրի հրամանի միջոցով Տվյալներ – Թարմացրեք բոլորը.

Դա արվել է միտումնավոր, քանի որ առանցքային աղյուսակը շատ տեղ է զբաղեցնում RAM-ում: Հետևաբար, համակարգչային ռեսուրսներն ավելի խնայողաբար օգտագործելու համար աշխատանքը կատարվում է ոչ թե ուղղակիորեն աղբյուրի, այլ քեշի հետ, որտեղ գտնվում է աղբյուրի տվյալների պատկերը։

Օգտագործելով առանցքային աղյուսակները նույնիսկ այս հիմնական մակարդակում, դուք կարող եք զգալիորեն բարձրացնել մեծ քանակությամբ տվյալների մշակման արագությունն ու որակը: Եթե ​​ինչ-որ բան անհասկանալի է, գրեք մեկնաբանություններում։

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

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

Մի փոքր պատմություն

Աղյուսակների ստեղծման ծրագրերի մշակման արշալույսին Lotus-ի կանոններն էին 1-2-3։ Նրա գերիշխանությունն այնքան ամբողջական էր, որ Microsoft-ի ջանքերը՝ մշակելու սեփական ծրագրաշարը (Excel), որպես Lotus-ի այլընտրանք, թվում էր ժամանակի կորուստ: Այժմ արագ առաջ դեպի 2010 թ. Excel-ը գերիշխում է աղյուսակների արդյունաբերության մեջ ավելի շատ, քան Lotus-ը երբևէ արել է իր պատմության ընթացքում, և այն մարդկանց թիվը, ովքեր դեռ օգտագործում են Lotus-ը, մոտենում է զրոյի: Ինչպե՞ս կարող էր դա տեղի ունենալ: Ինչո՞վ էր պայմանավորված իրադարձությունների նման դրամատիկ զարգացումը։

Վերլուծաբանները առանձնացնում են երկու հիմնական գործոն.

• Նախ, Lotus-ը որոշեց, որ այս նորաստեղծ GUI պլատֆորմը, որը կոչվում է Windows, պարզապես անցողիկ մոդա էր, որը երկար չի տևի: Նրանք հրաժարվեցին ստեղծել Lotus 1-2-3 Windows-ի տարբերակը (բայց միայն մի քանի տարի)՝ կանխատեսելով, որ իրենց ծրագրաշարի DOS տարբերակն այն ամենն է, ինչ սպառողներին երբևէ պետք կգա: Microsoft-ը, իհարկե, մշակել է Excel-ը հատուկ Windows-ի համար:
• Երկրորդ, Microsoft-ը Excel-ում մշակեց մի գործիք, որը կոչվում էր pivot tables, որը հասանելի չէր Lotus 1-2-3-ում: Առանցքային աղյուսակները, Excel-ի բացառիկ տարբերակը, այնքան աներևակայելի օգտակար էին, որ մարդիկ հակված էին սովորել նոր Excel ծրագրակազմը, այլ ոչ թե մնալ Lotus 1-2-3-ի հետ, որը չուներ դրանք:

Առանցքային աղյուսակները, ընդհանուր առմամբ, Windows-ի հաջողության թերագնահատման հետ մեկտեղ, Lotus 1-2-3-ի համար մահվան երթ խաղացին և նշանավորեցին Microsoft Excel-ի հաջողության սկիզբը:

Որոնք են առանցքային աղյուսակները:

Այսպիսով, ո՞րն է լավագույն միջոցը նկարագրելու, թե որոնք են առանցքային աղյուսակները:

Պարզ ասած, առանցքային աղյուսակները որոշ տվյալների ամփոփումներ են, որոնք ստեղծվել են այս տվյալների վերլուծությունը հեշտացնելու համար: Ի տարբերություն ձեռքով ստեղծված ընդհանուրների, Excel PivotTable-ները ինտերակտիվ են: Ստեղծվելուց հետո դուք կարող եք հեշտությամբ փոխել դրանք, եթե նրանք չեն տալիս այն նկարը, որը դուք ցանկանում էիք ստանալ: Ընդամենը մի քանի կտտոցով գումարները կարող են շրջվել, որպեսզի սյունակների վերնագրերը դառնան տողերի վերնագրեր և հակառակը: Կան բազմաթիվ տարբեր բաներ, որոնք դուք կարող եք անել առանցքային աղյուսակների հետ: Փոխանակ բառերով նկարագրել առանցքային աղյուսակների բոլոր հնարավորությունները, ավելի հեշտ է դա գործնականում դրսևորել...

Տվյալները, որոնք դուք վերլուծում եք PivotTables-ի միջոցով, չեն կարող լինել որևէ տեսակի տվյալ: Սա պետք է լինի հում տվյալներ, ինչպես ինչ-որ տեսակի ցուցակ: Օրինակ, սա կարող է լինել վերջին վեց ամսվա ընթացքում ընկերությունում ավարտված վաճառքների ցանկը:

Նայեք ստորև բերված նկարում ներկայացված տվյալներին.

Խնդրում ենք նկատի ունենալ, որ դրանք հումքային տվյալներ չեն, քանի որ դրանք արդեն ամփոփված են: B3 բջիջում մենք տեսնում ենք $30,000, ինչը, հավանաբար, հունվարին Ջեյմս Քուկի վաստակած ընդհանուր գումարն է: Որտեղ են այդ դեպքում աղբյուրի տվյալները: Որտեղի՞ց է եկել 30,000 դոլարի թիվը: Որտե՞ղ է սկզբնական վաճառքի ցուցակը, որից ստացվել է այս ամսական ընդհանուր գումարը: Հասկանալի է, որ ինչ-որ մեկը մեծ ջանքեր է գործադրել՝ կազմակերպելու և տեսակավորելու վերջին վեց ամիսների վաճառքի բոլոր տվյալները և դրանք վերածելու մեր տեսած ամփոփ աղյուսակի: Ի՞նչ եք կարծում, որքա՞ն ժամանակ է պահանջվել: Ժամ? Ժամը տասի՞ն։

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

Եթե ​​վերադառնանք սկզբնական վաճառքի ցուցակին, ապա այն կունենա հետևյալ տեսքը.

Դուք կարող եք զարմանալ, որ առևտրային գործարքների այս ցանկից, օգտագործելով առանցքային աղյուսակները և ընդամենը մի քանի վայրկյանում, մենք կարող ենք Excel-ում ստեղծել վաճառքի ամսական հաշվետվություն, որը մենք քննարկեցինք վերևում: Այո, մենք կարող ենք դա անել և շատ ավելին:

Ինչպե՞ս ստեղծել առանցքային աղյուսակ:

Նախ, համոզվեք, որ ունեք որոշ աղբյուրի տվյալներ Excel թերթում: Ֆինանսական գործարքների ցանկը հայտնաբերված ամենաբնորոշ բանն է։ Իրականում, դա կարող է լինել ցանկացած բանի ցանկ՝ աշխատակիցների կոնտակտային տվյալներ, սկավառակների հավաքածու կամ ձեր ընկերության վառելիքի սպառման տվյալները:

Այսպիսով, եկեք գործարկենք Excel-ը և բեռնենք այս ցուցակը...

Այս ցանկը Excel-ում բացելուց հետո կարող ենք սկսել առանցքային աղյուսակ ստեղծել:

Ընտրեք ցանկացած բջիջ այս ցանկից.

Այնուհետև ներդիրում Ներդիր(Տեղադրեք) ընտրեք հրամանը Առանցքային աղյուսակ(Ամփոփ աղյուսակ):

Երկխոսության տուփ կհայտնվի Ստեղծեք առանցքային աղյուսակ(Առանցքային աղյուսակի ստեղծում) երկու հարցով ձեզ համար.

• Ի՞նչ տվյալներ պետք է օգտագործեմ նոր առանցքային աղյուսակ ստեղծելու համար:
• Որտեղ պետք է տեղադրեմ առանցքային աղյուսակը:

Քանի որ նախորդ քայլում մենք արդեն ընտրել ենք ցանկի բջիջներից մեկը, ամբողջ ցուցակը կընտրվի ավտոմատ կերպով՝ առանցքային աղյուսակ ստեղծելու համար: Նկատի ունեցեք, որ մենք կարող ենք ընտրել այլ տիրույթ, այլ աղյուսակ կամ նույնիսկ արտաքին տվյալների աղբյուր, օրինակ՝ Access կամ MS-SQL տվյալների բազայի աղյուսակը: Բացի այդ, մենք պետք է ընտրենք, թե որտեղ տեղադրենք նոր առանցքային աղյուսակը՝ նոր թերթիկի վրա, թե գոյություն ունեցողներից մեկի վրա: Այս օրինակում մենք կընտրենք տարբերակը. Նոր աշխատանքային թերթիկ(Նոր թերթիկի վրա).

Excel-ը կստեղծի նոր աշխատաթերթ և դրա վրա կտեղադրի դատարկ առանցքային աղյուսակ.

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

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

Տարածաշրջան Արժեքներ(Իմաստները) հավանաբար չորսից ամենակարեւորն է: Թե ինչ վերնագիր է տեղադրված այս տարածքում, որոշում է, թե ինչ տվյալներ են ամփոփվելու (գումար, միջին, առավելագույն, նվազագույն և այլն): Սրանք գրեթե միշտ թվային արժեքներ են: Այս ոլորտում պաշտոնի համար գերազանց թեկնածու՝ վերնագրի տակ գտնվող տվյալներ ԳումարըՄեր օրիգինալ սեղանի (արժեքը): Եկեք այս վերնագիրը քաշենք տարածք Արժեքներ(Արժեքներ):

Խնդրում ենք նկատի ունենալ, որ վերնագիրը Գումարըայժմ նշվում է ստուգման նշանով, իսկ տարածքում ԱրժեքներՀայտնվել է (արժեքներ) գրառումը Գումարի գումար(Գումարը ըստ գումարի դաշտ), նշելով, որ սյունակը Գումարըամփոփել է

Եթե ​​նայենք հենց առանցքային աղյուսակին, ապա կտեսնենք սյունակի բոլոր արժեքների գումարը Գումարըօրիգինալ սեղան.

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

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

Այն ավելի հետաքրքիր է դառնում: Մեր առանցքային աղյուսակը սկսում է ձևավորվել...

Տեսնու՞մ եք օգուտները: Մի քանի կտտոցով մենք ստեղծեցինք աղյուսակ, որը ձեռքով ստեղծելու համար շատ երկար ժամանակ կպահանջվեր:

Էլ ի՞նչ կարող ենք անել։ Դե, ինչ-որ առումով մեր առանցքային աղյուսակն արդեն պատրաստ է։ Մենք ստեղծել ենք չմշակված տվյալների օգտակար ամփոփագիր: Կարևոր տեղեկություն արդեն ստացվել է։ Այս հոդվածի մնացած մասում մենք կքննարկենք ավելի բարդ առանցքային աղյուսակներ ստեղծելու մի քանի եղանակներ և կսովորենք, թե ինչպես հարմարեցնել դրանք:

Առանցքային աղյուսակի ստեղծում

Նախ, մենք կարող ենք ստեղծել երկչափ առանցքային աղյուսակ: Եկեք դա անենք՝ օգտագործելով սյունակի վերնագիրը Վճարման եղանակ(Վճարման եղանակ). Պարզապես քաշեք վերնագիրը Վճարման եղանակդեպի տարածաշրջան Սյունակների պիտակներ(Սյունակներ):

Մենք ստանում ենք արդյունքը.

Շատ թույն տեսք ունի:

Հիմա եկեք կազմենք եռաչափ աղյուսակ։ Ինչպիսի՞ն կարող է լինել նման սեղանը: Եկեք նայենք…

Քաշեք վերնագիրը Փաթեթ(Համալիր) դեպի տարածք Հաշվետվության զտիչ(Զտիչներ):

Ուշադրություն դարձրեք, թե որտեղ է նա հայտնվել...

Սա մեզ հնարավորություն է տալիս զտել հաշվետվությունը «Ո՞ր հանգստյան համալիրի համար է վճարվել» հատկանիշով։ Օրինակ, մենք կարող ենք տեսնել բաժանումը ըստ վաճառողների և վճարման եղանակների բոլոր համալիրների համար, կամ մի քանի կտտոցով կարող ենք փոխել ամփոփ աղյուսակի տեսքը և ցույց տալ նույն դասակարգումը միայն նրանց համար, ովքեր պատվիրել են համալիրը: Sunseekers.

Այսպիսով, եթե դուք դա ճիշտ եք հասկանում, ապա մեր առանցքային աղյուսակը կարելի է անվանել եռաչափ: Շարունակենք կարգավորել...

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

Եկեք տեսնենք, թե ինչպիսին է մեր առանցքային աղյուսակը հիմա: Ինչպես տեսնում եք, սյունակը Կանխիկանհետացել է նրանից:

Առանցքային աղյուսակների ձևաչափում Excel-ում

Ակնհայտ է, որ PivotTables-ը շատ հզոր գործիք է, բայց մինչ այժմ արդյունքները մի փոքր պարզ և ձանձրալի են թվում: Օրինակ, այն թվերը, որոնք մենք հավաքում ենք, նման չեն դոլարային գումարների, դրանք պարզապես թվեր են: Եկեք սա շտկենք:

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

Նախ, եկեք գտնենք մուտքը Գումարի գումարտարածքում Արժեքներ(արժեքներ) և սեղմեք դրա վրա: Հայտնվող ընտրացանկում ընտրեք տարրը Արժեքի դաշտի կարգավորումներ(Արժեքի դաշտի ընտրանքներ):

Երկխոսության տուփ կհայտնվի Արժեքի դաշտի կարգավորումներ(Արժեքի դաշտի պարամետրեր):

Սեղմեք կոճակը Թվերի ձևաչափ(Թվերի ձևաչափ), հայտնվում է երկխոսության տուփ Ձևաչափել բջիջները(Բջջի ձևաչափ):

Ցուցակից Կարգավիճակ(Թվերի ձևաչափեր) ընտրել Հաշվապահություն(Ֆինանսական) և տասնորդական թվերի թիվը սահմանել զրո: Այժմ սեղմեք մի քանի անգամ լավվերադառնալ մեր առանցքային աղյուսակին:

Ինչպես տեսնում եք, թվերն ի վերջո ձևակերպվեցին որպես դոլարային գումարներ:

Այժմ, երբ մենք ավարտեցինք ֆորմատավորումը, եկեք կարգավորենք ամբողջ առանցքային աղյուսակի ձևաչափը: Դա անելու մի քանի եղանակ կա: Եկեք օգտագործենք ավելի պարզը...

Բացեք ներդիրը PivotTable գործիքներ. Դիզայն(Աշխատանք առանցքային աղյուսակների հետ. Դիզայներ):

Ընտրեք ցանկացած համապատասխան ոճ և նայեք արդյունքին ձեր առանցքային աղյուսակում.

Excel-ում առանցքային աղյուսակների այլ կարգավորումներ

Երբեմն դուք պետք է զտեք տվյալները ըստ ամսաթվերի: Օրինակ՝ մեր առևտրային գործարքների ցանկը պարունակում է շատ ու շատ ժամկետներ։ Excel-ը տրամադրում է տվյալներ ըստ օրվա, ամսվա, տարվա և այլնի խմբավորման գործիք: Տեսնենք, թե ինչպես է դա արվում:

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

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

Դա շտկելու համար աջ սեղմեք ցանկացած ամսաթվի վրա և ընտրեք համատեքստի ընտրացանկից Խումբ(Խումբ):

Խմբավորման երկխոսության տուփը հայտնվում է: Մենք ընտրում ենք Ամիսներ(Ամիս) եւ մամուլ լավ:

Voila! Այս աղյուսակը շատ ավելի օգտակար է.

Ի դեպ, այս աղյուսակը գրեթե նույնական է հոդվածի սկզբում ցուցադրվածին, որտեղ վաճառքի արդյունքները կազմվել են ձեռքով։

Կա ևս մեկ շատ կարևոր կետ, որը դուք պետք է իմանաք: Դուք կարող եք ստեղծել ոչ թե մեկ, այլ տողերի (կամ սյունակների) վերնագրերի մի քանի մակարդակ.

...և այն կունենա այսպիսի տեսք...

Նույնը կարելի է անել սյունակների վերնագրերով (կամ նույնիսկ զտիչներով):

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

Սկսելու համար սեղմեք Գումարի գումարև հայտնվող մենյուից ընտրեք Արժեքի դաշտի կարգավորումներ(Արժեքի դաշտի ընտրանքներ):

Ցուցակում Ամփոփել արժեքի դաշտը ըստ(Գործողություն) երկխոսության վանդակում Արժեքի դաշտի կարգավորումներ(Արժեք դաշտի ընտրանքներ) ընտրեք Միջին(միջին):

Միևնույն ժամանակ, քանի դեռ այստեղ ենք, եկեք փոխվենք Հատուկ անուն(Պատվերով անունը) հետ Գումարի միջինը(Քանակը ըստ քանակի դաշտ) ավելի կարճ բանի: Մուտքագրեք նման բան այս դաշտում Միջին:

Սեղմել լավև տեսեք, թե ինչ է տեղի ունեցել. Ուշադրություն դարձրեք, որ բոլոր արժեքները ընդհանուրից փոխվել են միջինների, իսկ աղյուսակի վերնագիրը (վերևի ձախ բջիջում) փոխվել է Միջին:

Ցանկության դեպքում կարող եք անմիջապես ստանալ մեկ առանցքային աղյուսակում տեղադրված գումարը, միջինը և քանակը (վաճառքը):

Ահա քայլ առ քայլ ուղեցույց, թե ինչպես դա անել՝ սկսած դատարկ առանցքային աղյուսակից.

1. Քաշեք վերնագիրը Վաճառող(Վաճառքի ներկայացուցիչ) մարզում Սյունակների պիտակներ(Սյունակներ):
2. Քաշեք վերնագիրը երեք անգամ Գումարը(Արժեքը) դեպի տարածք Արժեքներ(Արժեքներ):
3. Առաջին դաշտի համար Գումարըփոխել անունը Ընդամենը(Գումար), և այս դաշտում թվերի ձևաչափն է Հաշվապահություն(Ֆինանսական): Տասնորդական տեղերի թիվը զրո է:
4. Երկրորդ դաշտ ԳումարըԱնուն Միջինե, սահմանել գործողությունը դրա համար Միջին(միջին) և այս դաշտում թվերի ձևաչափը փոխեք Հաշվապահություն(Ֆինանսական) զրոյի հավասար տասնորդական թվերով։
5. Երրորդ դաշտի համար Գումարըսահմանել վերնագիր հաշվելև վիրահատություն նրա համար - հաշվել(Քանակ)
6. Տարածքում Սյունակների պիտակներ(Սյունակներ) դաշտը ավտոմատ կերպով ստեղծվել է Σ արժեքներ(Σ արժեքներ) – քաշեք այն տարածք Տողերի պիտակներ(լարային)

Ահա թե ինչ ենք ստանում վերջում.

Ընդհանուր, միջին և վաճառքների քանակը՝ բոլորը մեկ առանցքային աղյուսակում:

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

Microsoft Excel-ի առանցքային աղյուսակները պարունակում են շատ ու շատ գործառույթներ և կարգավորումներ: Նման կարճ հոդվածը չի կարող նույնիսկ մոտենալ բոլորին լուսաբանելուն: Առանցքային աղյուսակների բոլոր հնարավորությունները ամբողջությամբ նկարագրելու համար կպահանջվի փոքր գիրք կամ մեծ կայք: Համարձակ և հետաքրքրասեր ընթերցողները կարող են շարունակել ուսումնասիրել առանցքային աղյուսակները: Դա անելու համար պարզապես սեղմեք առանցքային աղյուսակի գրեթե ցանկացած տարրի վրա և տեսեք, թե ինչ գործառույթներ և կարգավորումներ են բացվում: Ժապավենի վրա դուք կգտնեք երկու ներդիր. PivotTable գործիքներ. Ընտրանքներ(Վերլուծություն) և Դիզայն(Կառուցող): Մի վախեցեք սխալվելուց, դուք միշտ կարող եք ջնջել առանցքային աղյուսակը և սկսել նորից: Դուք ունեք հնարավորություն, որը երկարամյա DOS և Lotus 1-2-3 օգտվողները երբեք չեն ունեցել:

Excel-ի առանցքային աղյուսակները թույլ են տալիս վերլուծել տվյալները, որոնք գտնվում են մեկ մեծ տիրույթում: Ավելին, աղբյուրի աղյուսակը, որը կօգտագործվի վերլուծության համար, կարող է ստեղծվել կամ Excel-ում, կամ մեկ այլ տվյալների բազայում կամ այլ փաստաթղթում:

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

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

Ինչպես ստեղծել

Հիմա եկեք դիտարկենք առանցքային աղյուսակ ստեղծելու օրինակ: Ենթադրենք՝ հագուստի խանութի վաճառքի վերաբերյալ տվյալներ ունենք՝ ո՞ր վաճառողը, ինչ քանակի, ինչ ապրանքներ է վաճառել և ինչ գումարով։

Առանցքային աղյուսակ ստեղծելու համար ընտրեք ցանկացած բջիջ բնօրինակից, այնուհետև անցեք «Տեղադրեք» ներդիրը և սեղմեք կոճակը: «Առանցքային աղյուսակ».

Հետևյալ երկխոսության տուփը հայտնվում է. Դրանում անհրաժեշտ է նշել աղյուսակի տիրույթը կամ անվանումը աղբյուրի տվյալներով, իսկ այստեղ կարող եք այն ընտրել այլ աղբյուրից։ Այնուհետև նշեք, թե որտեղ պետք է տեղադրվի ստեղծվելիք հաշվետվությունը: Եկեք դա անենք նոր թերթիկի վրա: Սեղմեք OK:

Excel-ի բաց աշխատանքային գրքում կստեղծվի նոր թերթ, որի վրա կտեղադրվի դեռեւս դատարկ առանցքային աղյուսակը։

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

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

Ինչպես դասավորել դաշտերը

Հիմա որոշենք, թե ինչ սկզբունքով ենք վերլուծելու տվյալները։ Օրինակ, դուք պետք է պարզեք, թե որ վաճառողը որ ապրանքն է վաճառել ամեն ամիս և ինչ գումարով։

Մենք կզտենք տվյալները՝ ըստ վաճառողների ընտրված միջակայքի: Այսինքն՝ մենք ընտրում ենք վաճառող, և աղյուսակում կցուցադրվեն նրա վաճառած ապրանքները։ Ձախ սեղմեք «Վաճառող» դաշտը և քաշեք այն «Հաշվետվության զտիչ» տարածք: Աղյուսակը փոխվել է, և ավելացված դաշտն այժմ նշված է ստուգման նշանով:

Տողերի համար ընտրեք «Ապրանքներ»: Նմանապես, քաշեք ցանկալի դաշտը տարածք «Տողերի վերնագրեր».

Խնդրում ենք նկատի ունենալ, որ դուք կարող եք մի քանի դաշտ տեղադրել տարածքում: Օրինակ՝ տողերում մենք կընտրենք ապրանքներ և նշենք գինը։ Ապրանքները դարձել են բացվող ցուցակներ, որոնք ցուցադրում են գինը: Եթե ​​նախ նշեք գինը, ապա ապրանքները, բացվող ցուցակը կդառնա գին: Այստեղ նշանակություն ունի դաշտերի հերթականությունը։

Եթե ​​սկզբնական աղյուսակում ունենայինք «Միավորներ» սյունակը: Հետո այս ամփոփ աղյուսակը ցույց կտա, թե որ վաճառողը, որ ամսում, քանի միավոր ապրանք է վաճառել որոշակի գնով։

Դեպի տարածաշրջան «Սյունակների վերնագրեր»Քաշեք «Ամսաթիվ» դաշտը: Վաճառքները ցուցադրելու համար ոչ թե ամեն օր, այլ, օրինակ, ըստ ամիսների, աջ սեղմեք ցանկացած ամսաթվի վրա և ընտրացանկից ընտրեք «Խումբ»:

Աղյուսակը կունենա հետևյալ ձևը.

Այժմ քաշեք «Գումար» դաշտը «Արժեքներ» տարածք:

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

Դա շտկելու համար ամփոփագրում ընտրեք բջիջների ցանկալի տիրույթը և սեղմեք մկնիկի աջ կոճակը: ապա ընտրեք ընտրացանկից «Թվերի ձևաչափ».

Հաջորդ պատուհանում ընտրեք «Թվային», կարող եք ստուգել վանդակը «Բիթ խմբերի բաժանարար»և սեղմեք «OK»:

Ինչպես աշխատել տվյալների հետ

Բոլոր անհրաժեշտ դաշտերը հաշվի առնելուց հետո կարող ենք սկսել աշխատել Excel-ի առանցքային աղյուսակի հետ։ Վաճառողի ընտրություն. կարող եք ընտրել մեկը, մի քանիսը կամ բոլորը միանգամից՝ նշելով վանդակը «Ընտրեք մի քանի տարրեր».

Կարող եք նաև օգտագործել ֆիլտր տողերի և սյունակների համար: Օրինակում դրանք ապրանքներ և ամիսներ են: Օրինակ, կոստյումների և շալվարների վանդակները նշելով, կարող եք պարզել, թե որքանով են դրանք վաճառվել բոլոր վաճառողների կամ կոնկրետ վաճառողի կողմից:

Արժեքների տարածքում կարող եք կարգավորել դաշտի կարգավորումները: Օրինակը ցույց է տալիս արժեքների հանրագումարը. Ռոման փետրվարին վաճառել է վերնաշապիկներ 1800.00-ի չափով: տեսնենք քանի կտոր է։ Ձախ սեղմեք տողի վրա «Գումար ըստ դաշտի...»:և ընտրացանկից ընտրեք «Արժեքի դաշտի ընտրանքներ».

Հաջորդ պատուհանում ընտրեք «Քանակը» ցանկից, ընտրեք այն, ինչը հարմար է հատուկ ձեր գործի համար և սեղմեք «OK»:

Հիմա, նայելով արժեքներին, կարող ենք հասկանալ, որ «Ռոման» փետրվարին վաճառել է երկու մարզաշապիկ։

Հիմա եկեք կազմենք աղյուսակի ընդհանուր զտիչը ըստ ամիսների։ Մենք փոխում ենք տարածքները. «Report Filter» -ում մենք քաշում ենք «Date» դաշտը, մեջ «Սյունակների վերնագրեր»- "Վաճառող" .

Նման ամփոփ աղյուսակը ցույց կտա, թե յուրաքանչյուր վաճառող որքան և ինչ ապրանք է վաճառել ամբողջ ժամանակահատվածի կամ կոնկրետ ամսվա համար:

Ուշադրություն դարձրեք նաև ժապավենին: Երբ դուք ընտրում եք որևէ բջիջ առանցքային աղյուսակից, դրա վրա հայտնվում է ներդիր «Աշխատանք առանցքային աղյուսակների հետ»երկու ենթաներդիրներով՝ «Պարամետրեր» և «Դիզայներ»:

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

PivotTables-ի հզորությունը տեսնելը նույնքան պարզ է, որքան սկզբնական տվյալները PivotTable-ի տարբերակի հետ համեմատելը. Ընդամենը մի քանի կտտոցով ես կարողացա տեսնել ապրանքի միջին գինը ըստ նահանգների:

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

Մենք այս դասը կկառուցենք ըստ մեր դասի՝ տվյալների հետ ավելի լավ աշխատելու համար։ Ես ձեզ ցույց կտամ իմ սիրելի առանցքային աղյուսակի հինգ լավագույն փորձը:

Այս ձեռնարկի ողջ ընթացքում ես կօգտագործեմ այս էջում Microsoft-ի տրամադրած նմուշային տվյալները: Օգտագործեք այս տվյալները օրինակները վերստեղծելու կամ իմ ցուցադրած գործառույթները փորձարկելու համար:

Ինչպես օգտագործել առաջադեմ առանցքային աղյուսակի տեխնիկան Excel-ում (կարճ տեսանյութ)

Ինձ դուր է գալիս դասավանդել՝ օգտագործելով էկրաններ, որոնք հնարավորություն են տալիս դիտել, թե ինչպես եմ քայլ առ քայլ օգտագործում գործառույթները: Դիտեք ստորև ներկայացված կարճ տեսանյութը, որն ընդգրկում է Excel-ի առանցքային աղյուսակի իմ սիրելի առաջադեմ հինգ առանձնահատկությունները.

5 Ընդլայնված առանցքային աղյուսակի հնարքներ Excel-ում

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

1. Բաժիններ

Շերտեր- Կետ և սեղմեք գործիք Excel առանցքային աղյուսակում ներառված տվյալները ճշգրտելու համար: Տեղադրեք կտրիչ և հեշտությամբ կարող եք փոխել առանցքային աղյուսակում ներառված տվյալները:

Այս օրինակում ես տեղադրել եմ մի կտոր տեսակի համար Նյութ. Սեղմելուց հետո Մեջքի պայուսակ, առանցքային աղյուսակը ցույց է տալիս աղյուսակում միայն այս պարամետրը:

Շատ անգամ ես մշակում եմ վահանակի հաշվետվություններ, որոնք կօգտագործվեն ուրիշների կողմից: Կտրող սարքերի ավելացումը կարող է օգնել վերջնական օգտագործողին հարմարեցնել հաշվետվությունը ըստ իրենց ցանկության:

Կտրող սարք ավելացնելու համար սեղմեք առանցքային աղյուսակի վրա և գտեք ներդիրը Վերլուծություն Excel ժապավենի վրա:

Նշեք մի քանի վանդակներ՝ ներառելու այս սյունակները, որոնցից յուրաքանչյուրը կլինի մի հատված:

Պահել Ctrlստեղնաշարի վրա՝ մի քանի հատիչ տարրեր ընտրելու համար, որոնք կներառեն մի քանի ընտրություն սյունակից՝ որպես առանցքային աղյուսակի տվյալներ:

2. Ժամանակացույց

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

Գնացեք բաժին Վերլուծել > Տեղադրել ժամանակացույցըժամանակացույց ավելացնելու համար, հատուկ տեսակի կտրիչ, որը կառավարում է առաջադեմ Excel PivotTable-ում ներառված տվյալները՝ հիմնված ամսաթվի վրա:

Հուշում. Եթե այս գործառույթը չի աշխատում ձեզ մոտ, համոզվեք, որ ձեր աղբյուրի տվյալները աղյուսակում ձևաչափված ամսաթիվ ունեն որպես ամսաթիվ:

Ժամանակացույց ավելացնելու համար համոզվեք, որ ընտրել եք PivotTable (սեղմեք դրա ներսում) և այնուհետև սեղմեք կոճակը Վերլուծել > Տեղադրել ժամանակացույցը Excel ժապավենի վրա: Բացվող պատուհանում ընտրեք ամսաթվի սյունակ (կամ մի քանի սյունակ) և սեղմեք լավժամանակացույց ստեղծելու համար:

Սեղմեք և քաշեք ժամանակացույցի պատուհանում՝ առանցքային աղյուսակի համար որոշակի ժամանակային միջակայք ընտրելու համար:

Ժամանակացույցը տեղադրվելուց հետո դուք կարող եք սեղմել և տեղափոխել բռնակները ժամանակացույցի վրա՝ փոփոխելու այն, ինչ ներառված է առանցքային աղյուսակում:

Դուք կարող եք փոխել ժամանակացույցի աշխատանքի եղանակը՝ սեղմելով ներքևի աջ անկյունում գտնվող բացվող ցանկը: Դուք կարող եք փոխել ժամանակացույցը՝ օրինակ՝ կոնկրետ ամսաթվերի փոխարեն եռամսյակային կամ տարեկան տվյալները ցուցադրելու համար:

3. Աղյուսակային տեսք

Excel-ում PivotTables-ի ստանդարտ տեսքը ջրվեժի տեսք ունի. Քանի որ ավելի շատ դաշտեր եք քաշում տողերի վրա, Excel-ը տվյալների մեջ ավելի շատ «շերտեր» է ստեղծում:

Խնդիրն այն է, որ ստանդարտ տեսքում դժվար է բանաձևերը տեղավորել PivotTables-ում: Եթե ​​դուք ունեք տվյալներ PivotTable-ում, բայց ցանկանում եք դրանք դիտել որպես սովորական աղյուսակ, դուք պետք է օգտագործեք աղյուսակի տեսքը PivotTable-ի համար:

Ընտրել Դիզայներ > Հաշվետվության դասավորություն > Ցուցադրել աղյուսակային տեսքովաշխատել ձեր առանցքային աղյուսակի հետ որպես սովորական աղյուսակ:

Ինչու՞ պետք է օգտագործել աղյուսակի տեսքը: Ձեր PivotTable-ի տվյալները դասական աղյուսակային ոճով բերելը թույլ կտա ձեզ ավելի հեշտությամբ մուտքագրել տվյալների բանաձևեր կամ դրանք զետեղել առանձին հաշվետվության մեջ:

Օգտագործեք «Table View»՝ ձեր առանցքային աղյուսակը ավելի շատ նմանեցնելու ստանդարտ տողերի և սյունակների:

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

4. Հաշվարկված դաշտեր

Հաշվարկված դաշտերը առանցքային աղյուսակում սյունակ ավելացնելու միջոց են, որը աղբյուրի տվյալների մեջ չէ: Դուք կարող եք օգտագործել ստանդարտ մաթեմատիկական գործողություններ՝ աշխատելու համար բոլորովին նոր դաշտեր ստեղծելու համար: Վերցրեք երկու գոյություն ունեցող սյունակներ և օգտագործեք մաթեմատիկան՝ բոլորովին նորերը ստեղծելու համար:

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

Հաշվարկված դաշտերի հետ աշխատելու համար սկսեք սեղմելով PivotTable-ի ներսում և այնուհետև գտնել ժապավենի վրա Վերլուծություն. Սեղմեք մենյու Դաշտեր, տարրեր և հավաքածուներիսկ հետո ընտրեք Հաշվարկված դաշտ.

Օգտագործեք Վերլուծություն > Դաշտեր, Անդամներ և Կոմպլեկտներ > Հաշվարկված դաշտհաշվարկված դաշտը ձեր PivotTable-ում տեղադրելու համար:

Նոր բացվող պատուհանում սկսեք հաշվարկված դաշտին անուն տալով: իմ դեպքում ես դա կանվանեմ Ընդհանուր պատվեր. Պատվերի ընդհանուր արժեքն այն քանակն է, որը բազմապատկվում է յուրաքանչյուր միավորի գնով: Հետո ես կրկնակի սեղմեցի այս պատուհանի դաշտերի ցանկի առաջին վերնագրի դաշտի (քանակի) վրա։

Պատվերի ընդհանուր արժեքը հաշվարկելու համար ես բազմապատկեցի գոյություն ունեցող միավոր գնի դաշտը ( Միավոր գինը) քանակի դաշտում ( Քանակ).

Այս դաշտի անունը ավելացնելուց հետո կավելացնեմ բազմապատկման նշան * , ապա կրկնակի սեղմեք ընդհանուր քանակի (քանակի) վրա։ Եկեք առաջ գնանք և սեղմենք լավ.

Այժմ Excel-ը թարմացրել է իմ ընդլայնված առանցքային աղյուսակը և ավելացրել է նոր հաշվարկված դաշտ: Դուք նաև կտեսնեք PivotTables-ի ցանկը Դաշտային ցանկում, որպեսզի կարողանաք դրանք քաշել զեկույցի ցանկացած վայրում, երբ դրանք ձեզ անհրաժեշտ լինեն:

Եթե ​​դուք չեք ցանկանում օգտագործել երկսյունակ թվաբանություն, կարող եք նաև մուտքագրել ձեր սեփական թվաբանական արժեքները հաշվարկված դաշտում: Օրինակ, եթե ես ուզում եմ պարզապես ավելացնել 5% վաճառքի հարկ յուրաքանչյուր պատվերի համար, ապա կարող եմ գրել հետևյալը հաշվարկված դաշտում.

Ես բազմապատկեցի պատվերի ընդհանուր արժեքը 1.05-ով, որպեսզի հաշվարկեմ արժեքը՝ ներառյալ վաճառքի հարկը. Դուք կարող եք օգտագործել թվային արժեքներ առկա դաշտերի հետ միասին:

Սկզբունքորեն, հաշվարկված դաշտերը կարող են պարունակել ցանկացած ստանդարտ մաթեմատիկական օպերատորներ, ինչպիսիք են գումարումը, հանումը, բազմապատկումը և բաժանումը: Օգտագործեք այս հաշվարկված դաշտերը, եթե չեք ցանկանում թարմացնել սկզբնական տվյալները:

Գնալ Տեղադրեք >փորձելու այս հատկությունը:

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

Բացվող պատուհանը պարունակում է բազմաթիվ տարբերակներ աղբյուրի տվյալներից առանցքային աղյուսակ ստեղծելու համար: Կտտացրեք այս պատուհանի ձախ կողմում գտնվող մանրապատկերներին՝ Excel-ի կողմից տրամադրված առաջարկվող PivotTable տարբերակները դիտելու համար:

PivotTable Recommendation ֆունկցիան առաջարկում է բազմաթիվ տարբերակներ՝ ձեր տվյալները մեկ սեղմումով վերլուծելու համար:

Թեև սա առաջադեմ առանձնահատկություն է, որի մասին քչերն են տեղյակ, այն նաև հիանալի գործիք է առանցքային աղյուսակներ գործարկելու համար: Ոչինչ չի խանգարում ձեզ փոփոխել PivotTable-ը՝ ինքներդ փոխարինելով դաշտերը, բայց դա լավ մեկնարկային կետ է:

Բացի այդ, ինձ դուր է գալիս այս հատկությունը որպես տվյալների որոնման գործիք: Եթե ​​ես չգիտեմ, թե ինչ եմ փնտրում, երբ սկսում եմ ուսումնասիրել տվյալները, ապա առաջարկվող Excel առանցքային աղյուսակները հաճախ ավելի խորաթափանց են, քան ես:

Կրկնեք և շարունակեք սովորել (ավելի շատ Excel դասերով)

Excel-ի այս առաջադեմ դասը օգնեց ձեզ ավելի խորանալ PivotTables-ի մեջ՝ Excel-ի աղյուսակը վերլուծելու և դիտելու իմ սիրելի հնարավորություններից մեկը: Ես օգտագործում եմ առանցքային աղյուսակները մեծ տվյալների հավաքածուներում իմաստ գտնելու համար, որպեսզի կարողանամ ավելի լավ որոշումներ կայացնել և քայլեր ձեռնարկել:

Այս դասերը Excel-ի և Pivot Tables-ի հետ ձեր հմտությունները կբարձրացնեն հաջորդ մակարդակ: Ստուգեք դրանք.

• ExcelZoo-ն հիանալի ակնարկ ունի առանցքային աղյուսակների տեխնիկայի վերաբերյալ իր հոդվածում՝ 10 դասեր առանցքային աղյուսակների յուրացման համար (անգլերեն):
• Մենք Envato Tuts+-ում ծածկեցինք առանցքային աղյուսակները սկսնակների համար նախատեսված ձեռնարկով:
• Microsoft Excel-ի ավելի հեշտ ներածության համար ստուգեք մեր ձեռնարկների շարքը:

Ի՞նչ եք դեռ ուզում իմանալ առանցքային աղյուսակների մասին: Տեղեկացրեք ինձ ձեր գաղափարները կամ հարցերը այս ձեռնարկի տակ գտնվող մեկնաբանություններում: