Ինչպես կարգավորել Excel 2010 Pivot աղյուսակներ

by Tim Perdue

01-ից 15-ը

Վերջնական արդյունքը

Սա սույն քայլի քայլ առ քայլ ձեռնարկի վերջնական արդյունքն է - Սեղմեք պատկերին, ամբողջական չափի տարբերակը տեսնելու համար:

Բազմաթիվ տարիներ եղել են Microsoft Excel- ի եւ բարձր մակարդակի բիզնեսի հետախուզության (BI) պլատֆորմների միջեւ: Microsoft Excel 2010 Pivot աղյուսակի կատարելագործման եւ մի քանի այլ BI առանձնահատկություններով այն դարձել է իրական մրցակից Enterprise BI- ի համար: Excel- ը ավանդաբար օգտագործվել է ինքնաշխատ վերլուծության եւ ստանդարտ գործիքի միջոցով, որը բոլորը արտահանում են իրենց վերջնական հաշվետվությունները: Մասնագիտական բիզնեսի հետախուզությունը ավանդաբար վերապահվել է SAS, Business Objects եւ SAP- ի նմանությունների համար:

Microsoft Excel 2010-ը (Excel 2010 Pivot աղյուսակի հետ), ինչպես նաեւ SQL Server 2008 R2- ի, SharePoint 2010-ի եւ անվճար Microsoft Excel 2010-ի «PowerPivot» հավելումը հանգեցրել է բարձր վերջնական բիզնեսի հետախուզության եւ հաշվետվությունների լուծմանը:

Այս ձեռնարկը ներառում է ուղիղ առաջ սցենար `Excel 2010 PivotTable- ի հետ, կապված SQL Server 2008 R2 տվյալների բազայի հետ` օգտագործելով պարզ SQL հարցումներ: Ես նաեւ օգտագործում եմ Slicers- ը տեսողական ֆիլտրացման համար, որը նոր է Excel 2010-ում: Ես կներկայացնեմ ավելի բարդ BI մեթոդներ, օգտագործելով տվյալների վերլուծության արտահայտություններ (DAX) PowerPivot- ում Excel 2010-ի համար: Microsoft Excel 2010-ի այս վերջին թողարկումն իրական արժեք է Ձեր օգտվողի համայնքի համար:

02-ից 15-ը

Տեղադրեք Pivot աղյուսակը

Տեղադրեք ձեր կուրսորը հենց այն վայրում, որտեղ ցանկանում եք ձեր առանցքային սեղանը եւ սեղմեք Տեղադրեք | Pivot աղյուսակ:

Դուք կարող եք Ներածել աղյուսակի նոր կամ գոյություն ունեցող Excel աշխատանքային գրքում: Կարող եք մտածել, որ ձեր կուրսորը տեղադրի վերեւից մի քանի շարքով: Սա ձեզ հնարավորություն կտա բաժին կամ ընկերության տեղեկատվության համար, եթե դուք կիսում եք աշխատաթերթը կամ տպեք այն:

Բացեք նոր կամ գոյություն ունեցող Excel 2010 աշխատանքային գրքույկը եւ սեղմեք այն բջիջը, որտեղ ցանկանում եք շրջել աղյուսակի վերին ձախ անկյունը:
Կտտացրեք Ներդիրի ներդիրը եւ կտտացրեք Ներածման աղյուսակը աղյուսակների բաժնում: Ընտրեք PivotTable- ը: Սա կսկսի Ստեղծել PivotTable երկխոսության ձեւը:

03-ից 15-ը

Միացրեք Pivot աղյուսակը SQL Server- ում (կամ այլ տվյալների շտեմարանում)

Ստեղծեք ձեր SQL հարցումը եւ այնուհետեւ միացեք SQL Server- ին `կապի տվյալների ստանդարտը Excel աղյուսակի մեջ ներդնելու համար:

Excel 2010-ը կարող է տվյալներ ստանալ բոլոր խոշոր RDBMS- ների (Relational Database Management System) պրովայդերների կողմից: SQL Server- ի վարորդները պետք է հասանելի լինեն կանխադրվածի համար: Սակայն բոլոր հիմնական տվյալների բազայի ծրագրային ապահովումը դարձնում է ODBC (Open Database Connectivity) վարորդների թույլտվությունը: Ստուգեք իրենց կայքը, եթե Ձեզ անհրաժեշտ է բեռնել ODBC վարորդներին:

Այս ձեռնարկի դեպքում ես կապում եմ SQL Server 2008 R2 (SQL Express անվճար տարբերակ):

A- Ստեղծեք PivotTable ձեւը առաջին ձեւն է, որը ստեղծում է SQL Server- ի կապը: Ընտրեք «Օգտագործեք արտաքին տվյալների աղբյուրը» եւ սեղմեք «Ընտրել Ընտրել» կոճակը: Թողեք այն տեղը, որտեղ Pivot աղյուսակը կտեղադրվի, եթե ցանկանում եք ստեղծել նոր աշխատաթերթ եւ տեղադրել այն:
B - Գործող կապերի ձեւը թվարկում է ընթացիկ աշխատանքային գրքում, ձեր համակարգչում եւ ցանցում միացված որեւէ կապ: Գոյություն ունեցող կապերը իսկապես ուղղակի տեքստային ֆայլեր են, որոնք կապված են կոնկրետ տվյալների աղբյուրի մուտք գործելու հետ: Մեր դեպքում մենք պատրաստվում ենք ստեղծել նոր տվյալների աղբյուր: Սեղմեք «Լրացուցիչ փնտրելու համար» կոճակը:
C - Սեղմեք Նոր Աղբյուրը կոճակը կսկսի տվյալների միացման մոգ:
D - Ընտրեք Microsoft SQL Server եւ սեղմեք Հաջորդը:
E - Մուտքագրեք սերվերի անունը եւ մուտքագրեք հավատարմագրերը: Ընտրեք համապատասխան վավերացման մեթոդը: Եթե չգիտեք, թե որ մեթոդը օգտագործվի, կապվեք ձեր տվյալների շտեմարանի ադմինիստրատորի հետ:
- Օգտագործեք Windows Authentication- ը: Այս մեթոդը օգտագործում է ձեր ցանցային մուտքն SQL Server տվյալների բազաներ մուտք գործելու համար:
- Օգտագործեք հետեւյալ անունը եւ գաղտնաբառը: Այս մեթոդը օգտագործվում է այն ժամանակ, երբ SQL Server- ը կազմաձեւված է ինքնաբերաբար օգտագործողների հետ `տվյալների բազաներ մուտք գործելու համար:
F - Այս քայլում մենք պատրաստվում ենք ընտրել որպես սեղան որպես տեղահանում: Մենք պատրաստվում ենք փոխարինել աղյուսակը մաքսային SQL- ով, որը կտրամադրի այն տվյալները, որոնք մենք ուզում ենք մեր Excel- ում:
- Ընտրեք այն բազան, որը կապվելու է: Այս օրինակում մենք կապում ենք Microsoft- ի կողմից տրամադրված AdventureWorks նմուշի տվյալների բազայի հետ: Ստուգեք մի կոնկրետ աղյուսակում եւ ընտրեք առաջին սեղանը: Հիշեք, որ մենք չենք պատրաստվում տվյալներ ստանալ այս աղյուսակից:
- Սեղմեք Finish- ը, որը կփակի կախարդը եւ վերադարձնի աշխատանքը: Մենք կփոխանցենք տեղահանումների սեղանը մեր մաքսային SQL հարցման համար:

Դուք կվերադառնաք Ստեղծեք PivotTable ձեւը (A): Սեղմեք OK:

04-ից 15-ը

Pivot աղյուսակը ժամանակավորապես կապված է SQL աղյուսակին

PivotTable- ը միացված է SQL Server- ին, տեղաբաշխիչի սեղանին:

Այս պահին դուք միացել եք տեղահանումների սեղանին եւ դուք ունեք դատարկ PivotTable: Դուք կարող եք տեսնել ձախ կողմում, PivotTable- ը կլինի եւ աջ կողմում կա առկա դաշտերի ցանկ:

05-ից 15-ը

Բաց կապի առանձնահատկությունները

Բաց կապի հատկությունների ձեւը:

Նախքան ընտրելով PivotTable- ի տվյալները ընտրելու համար, մենք պետք է փոխենք կապը SQL հարցմանը: Համոզվեք, որ գտնվում եք Ընտրք էջում եւ սեղմեք «Տվյալների աղբյուրը փոխելու» բաժինը, որը բացվում է Տվյալների բաժնում: Ընտրեք կապի առանձնահատկությունները:

Սա կապում է Միացման հատկությունների ձեւը: Սեղմիր Սահմանում ներդիրին: Սա ցույց է տալիս, որ կապի տվյալները ներկայիս կապի SQL Server- ի համար: Թեեւ այն հղում է հղում ֆայլին, տվյալները իրականում տեղադրված են աղյուսակում:

06-ից 15-ը

Թարմացնել կապի հատկությունները հարցումով

Փոխել աղյուսակը SQL հարցմանը:

Փոխեք հրամանների տեսակը սեղանից դեպի SQL եւ վերագրանցեք առկա Հրահանգային տեքստը ձեր SQL Query- ով: Ահա այն հարցումը, որ ես ստեղծել եմ AdventureWorks նմուշի տվյալների բազայից:

SELECT Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.Unit Գինը,
Sales.SalesOrderDetail.LineTotal,
Արտադրություն: Արտադրանքի անուն,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Արտադրություն:
Արտադրություն: Արտադրություն: Արտադրություն,
Արտադրություն.ԱպրանքներԱպահովագրություն.Այս AS ProductCategory
Sales.SalesOrderDetail- ից INNER JOIN Sales.SalesOrderHeader ON- ից
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN- ի արտադրությունը: Արտադրանքի ON Sales.SalesOrderDetail.ProductID =
Product.Product.ProductID INNER JOIN Sales.Customer ON- ը
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID եւ
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID ներդիրը
Արտադրություն.Արտադրանքի բաժին Կարգավիճակ ON Արտադրություն. Արտադրություն. ԱպրանքԱվելի կատեգորիա ID =
Արտադրություն.ԱպրանքԿապավենի կատեգորիա. ԱպրանքԱյցելի անվանում

Սեղմեք OK:

07-ից 15-ը

Ստացեք կապի ծանուցում

Կտտացրեք Այո `կապվածության մասին նախազգուշացմանը:

Դուք կստանաք Microsoft Excel Warning երկխոսության վանդակը: Դա այն պատճառով, որ փոխել ենք կապի տվյալները: Երբ մենք ի սկզբանե ստեղծեցինք կապը, այն փրկեց տեղեկատվությունը արտաքին .ODC ֆայլում (ODBC Data Connection): Աշխատանքային տետրում տվյալները նույնն են, ինչ .ODC ֆայլը, մինչեւ մենք փոխեցինք սեղանի հրամանների տիպից SQL- ի հրաման տիպի քայլը # 6-ում: Նախազգուշացումը ձեզ ասում է, որ տվյալները այլեւս չեն համընկնում եւ աշխատանքից հանվում է արտաքին ֆայլի հղումը: Սա լավն է: Սեղմեք Այո:

08-ից 15-ը

Pivot աղյուսակը կապված է SQL Server- ով հարցման հետ

PivotTable- ը պատրաստ է ձեր տվյալները ավելացնել:

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

09-ից 15-ը

Ավելացնել դաշտեր առանցք սեղանին

Ավելացնել PivotTable դաշտերը:

Ներածված տաղավարի դաշտային ցուցակում, քաշեք ProductCategory- ը, տողի պիտակների տարածքի, OrderDate- ի սյունակային պիտակների տարածքի եւ TotalDue արժեքների տարածքի մեջ: Պատկերը ցույց է տալիս արդյունքները: Ինչպես տեսնում եք, ամսաթիվը դաշտը ունի անհատական ամսաթվերը, այնպես որ PivotTable- ը յուրաքանչյուր եզակի ամսաթվի համար ստեղծել է սյուն: Բարեբախտաբար, Excel 2010-ը որոշակի գործառույթներ ունի, որոնք կօգնեն մեզ կազմակերպել ամսաթիվներ:

10-ից 15-ը

Ավելացրեք խմբագրման ամսաթիվ դաշտերի համար

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

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

11-ից 15-ը

Ընտրեք ըստ արժեքների խմբավորման

Ընտրել խմբավորման տարրերը ամսաթվի դաշտում:

Կախված այն տեսակներից, որոնք խմբավորում եք, ձեւը մի փոքր այլ է: Excel 2010-ը թույլ է տալիս խմբագրել ամսաթվերը, թվերը եւ ընտրված տեքստային տվյալները: Մենք հավաքում ենք OrderDate- ը այս ձեռնարկում, որպեսզի ձեւը ցույց կտա ամսաթվի խմբավորումների հետ կապված տարբերակներ:

Սեղմեք ամսվա եւ տարին եւ սեղմեք OK:

12-ից 15-ը

Pivot աղյուսակ, ըստ տարիների եւ ամիսների

Ամսաթիվ դաշտերը խմբավորված են տարիներով եւ ամիսներով:

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

Այս պահին, PivotTable- ը բավականին օգտակար է: Յուրաքանչյուր դաշտը կարող է զտված լինել, սակայն խնդիրը չկա զտիչի թիրախը ֆիլտրերի ներկայիս վիճակի վերաբերյալ: Բացի այդ, մի քանի սեղմեք վերցնում է տեսքը փոխելու համար:

13-ից 15-ը

Տեղադրեք Սխալիչ (Նոր Excel 2010-ում)

Ավելացրեք Խուզարկուներ PivotTable- ին:

Սխտորները նոր են Excel 2010-ում: Սխտողները հիմնականում համարվում են առկա դաշտերի տեսողական ֆիլտրերի համարժեքության եւ հաշվետվական զտիչների ստեղծման համարժեքը, եթե այն, որ ցանկանում եք զտել, ներկա PivotTable տեսանկյունից չէ: Սլաքերների մասին այս գեղեցիկ բանը շատ հեշտ է դառնում օգտվողին փոխել Ներածման տախտակի տվյալների տեսակները, ինչպես նաեւ տրամադրել տեսողական ցուցանիշներ `ֆիլտրերի ներկայիս վիճակի վերաբերյալ:

Սլաքերը տեղադրելու համար ընտրեք Ընտրք էջանիշը եւ կտտացրեք Կտրող սեղմողը Sort & Filter բաժնում: Ընտրեք Insert Slicer- ը, որը բացում է Սեղմող կտրողներ ձեւը: Ստուգեք ինչպես դաշտերը, որքան ցանկանում եք ունենալ: Մեր օրինակում ես ավելացրել եմ տարիներ, CountryRegionName եւ ProductCategory: դուք կարող եք դիրքորոշել Սլաքերներին, որտեղ ցանկանում եք դրանք: Լռելյայնորեն, բոլոր արժեքները ընտրված են, ինչը նշանակում է, որ որեւէ ֆիլտր չի կիրառվել:

14-ից 15-ը

Ուղղահայաց սեղան Օգտագործողների համար հարմար սղոցներով

Սլաքերը հեշտացնում են օգտվողների համար PivotTables- ը զտելու համար:

Ինչպես տեսնում եք, Slicers- ը ցույց է տալիս բոլոր տվյալները որպես ընտրված: Հասկանալի է, որ օգտվողը հստակ է, թե ինչ տվյալներ են PivotTable- ի ընթացիկ տեսանկյունից:

15-ից 15-ը

Ընտրեք արժեքները, որոնք թարմացվում են առանցքային աղյուսակում

Ընտրեք կլիենտների կոմբինացիաներ, փոխելու տվյալները դիտելը:

Սեղմեք արժեքի տարբեր կոմբինացիաներին եւ տեսեք, թե ինչպես է PivotTable- ի տեսակետը փոխվում է: Դուք կարող եք օգտագործել սովորական Microsoft- ի սեղմելով Slicers- ում, նշանակում է, որ եթե Դուք կարող եք Control + Click- ից օգտվել բազմաթիվ արժեքներ ընտրելու կամ Shift + Click, ընտրեք արժեքների շարք: Յուրաքանչյուր կտրող սարքը դրսեւորում է ընտրված արժեքները, որոնք այն իսկապես ակնհայտ են դարձնում, թե ինչ է PivotTable- ի վիճակը ֆիլտրերի առումով: Դուք կարող եք փոխել Ստրկատերերի ոճերը, եթե ցանկանում եք սեղմել Quick Styles- ը, Ընտրանքների ներդիրի Slicer բաժնում:

Slicers- ի ներդրումը իսկապես բարելավել է PivotTables- ի գործածելիությունը եւ Excel 2010-ը շատ ավելի մոտ է տեղափոխել պրոֆեսիոնալ բիզնեսի հետախուզական գործիք: PivotTables- ը բավականին լավացել է Excel 2010-ին եւ երբ նոր PowerPivot- ի հետ համատեղվում է շատ բարձր արդյունավետության վերլուծական միջավայր: