Mmene Mungakonzere Matables Pivot 2010

by Tim Perdue

01 pa 15

Chotsatira chomaliza

Ichi ndi zotsatira zomaliza za phunziroli - Dinani pa chithunzi kuti muwone kukula kwake.

Pakhala pali kusiyana pakati pa Microsoft Excel ndi mapulatifomu apamwamba kwambiri a bizinesi (BI) kwa zaka zambiri. Kulimbikitsidwa kwa Microsoft Excel 2010 Pivot Table pamodzi ndi zinthu zina zambiri za BI zakhala mpikisano weniweni wa BI. Excel kawirikawiri imagwiritsidwa ntchito pofufuza mwachidziwitso ndi chida choyenera kuti aliyense atumize malipoti awo omalizira. Katswiri wamalonda wamalonda wakhala akusungidwa monga SAS, Business Objects ndi SAP.

Microsoft Excel 2010 (yomwe ili ndi Excel 2010 Pivot Table) pamodzi ndi SQL Server 2008 R2, SharePoint 2010 komanso "PowerPivot" yaulere ya Microsoft Excel 2010 yachititsa kuti pakhale njira yothetsera malonda komanso mapeto a malonda.

Phunziroli likufotokoza zochitika molunjika ndi Excel 2010 PivotTable yolumikizidwa ku SQL Server 2008 R2 database pogwiritsa ntchito SQL yovuta funso. Ndikugwiritsanso ntchito Slicers zojambula zojambula zomwe zili zatsopano ku Excel 2010. Ndidzayang'ana njira zovuta zambiri za BI pogwiritsa ntchito Data Analysis Expressions (DAX) mu PowerPivot for Excel 2010 posachedwapa. Kutulutsidwa kwatsopano kwa Microsoft Excel 2010 kungapereke phindu lenileni kwa anthu ogwiritsa ntchito.

02 pa 15

Ikani Pivot Table

Ikani malonda anu pomwe mukufuna tebulo lanu la pivot ndipo dinani pa Insert | Pivot Table.

Mukhoza kukhazikitsa Pivot Table mubuku latsopano kapena lomwe liripo Excel. Mungafune kuganizira kuika ndondomeko yanu pansi pa mizere ingapo. Izi zidzakupatsani malo oti mudziwe mutu kapena kampani ngati mutagawana pepala lanu kapena mulisindikize.

Tsegulani buku latsopano la Excel 2010 kapena chatsopano ndipo dinani selo limene mukufuna pamwamba pangodya la Pivot Table.
Dinani ku Insert tab ndipo dinani pazithunzi za PivotTable m'magawo. Sankhani PivotTable. Izi zimayambitsa mawonekedwe a PivotTable dialog.

03 pa 15

Tsegulani Pivot Table ku SQL Server (kapena Other Database)

Pangani funso lanu la SQL ndikugwirizanitsa ku SQL Server kuti mulowetse chingwe chachinsinsi cha data mu Excel spreadsheet.

Excel 2010 akhoza kutenga data kuchokera kuzipangizo zazikulu za RDBMS (Relational Database Management System) . Madalaivala a SQL Server ayenera kupezeka kuti agwirizane ndi osasintha. Koma mapulogalamu onse akuluakulu osungirako zolemba amapanga ODBC (Open Database Connectivity) madalaivala kuti akulolereni kugwirizanitsa. Yang'anani pa webusaiti yawo ngati mukufuna kutsegula madalaivala ODBC.

Pankhani ya phunziroli, ndikugwirizanitsa ndi SQL Server 2008 R2 (version ya SQL Express yaulere).

A - The Pangani PivotTable mawonekedwe ndi mawonekedwe oyambirira popanga kugwirizana kwa SQL Server. Sankhani "Gwiritsani ntchito chitsime chakunja kwa deta" ndipo dinani pa Chosankha Chotsutsana. Siyani malo pomwe Pivot Table idzayikidwa pokhapokha ngati mukufuna kupanga pepala latsopano ndikuyika izo apo.
B - Zogwirizanitsa Zomwe Zilipo Panopa zimatanthauzira kugwirizana kulikonse mu buku lino, pa kompyuta yanu ndi intaneti yomwe mukugwirizanako. Kulumikizana komwe kulipo kumangokhala mafayilo olemba ndi chidziwitso chogwirizanitsa chofunikira kuti mupeze malo enaake a deta. Kwa ife, tilenga gwero latsopano la deta. Dinani pa Fufuzani Mafupa Ena.
C - Dinani pa Bungwe Latsopano la Chitsimikizo lidzatsegula Wowonjezera Wowonjezera Data.
D - Sankhani Microsoft SQL Server ndipo dinani Kenako.
E - Lowani dzina la Seva ndikulembera pazovomerezeka. Sankhani njira yoyenera yolondola. Ngati simukudziwa njira yomwe mungagwiritsire ntchito, kambiranani ndi woyang'anira deta yanu.
- Gwiritsani ntchito Windows Authentication: Njira iyi imagwiritsa ntchito intaneti yanu kulowa mu SABL Server.
- Gwiritsani ntchito Dzina Lotsatila ndi Chinsinsi: Njirayi imagwiritsidwa ntchito pamene SQL Server yakhazikitsidwa ndi ogwiritsa ntchito okhazikika kuti apeze mauthenga.
F - Mu sitepe iyi, tidzasankha tebulo ngati malo ogwira ntchito. Tidzasintha tebulo ndi SQL yachizolowezi yomwe idzakupatseni ndondomeko yomwe tikufuna mubuku lathu la Excel.
- Sankhani deta yomwe mudzalumikiza. Mu chitsanzo ichi, tikugwirizanitsa ndichitsulo chowonetseratu cha AdventureWorks choperekedwa ndi Microsoft. Onetsetsani kuti Gwiritsani ntchito gome limodzi ndikusankha tebulo loyamba. Kumbukirani, sitidzatenga data kuchokera pa tebulo ili.
- Dinani Kutsirizitsa chomwe chidzatseka wizara ndikukubweretsani ku bukhuli. Tidzasintha tebulo la malo ogwiritsira ntchito funso lathu la SQL.

Mudzabwezeredwa ku mawonekedwe a PivotTable (A). Dinani OK.

04 pa 15

Pivot Table Yowonjezera Kwambiri ku SQL Table

PivotTable imagwirizanitsidwa ndi SQL Server ndi tebulo la malo.

Panthawiyi, mwagwirizanitsa ndi tebulo la malo ogwiritsira ntchito ndipo muli ndi Pivotable yosafunika. Mukhoza kuona kumanzere kunali PivotTable kudzakhala pomwepo pali mndandanda wa masamba omwe alipo.

05 ya 15

Tsegulani Zida Zogwirizana

Tsegulani mawonekedwe a Zogwirizana Zogwirizana.

Tisanayambe kusankha deta ya PivotTable, tifunika kusintha kugwirizana kwa funso la SQL. Onetsetsani kuti muli pazomwe Mungasankhe pakutha ndipo dinani pa Chitsitsi Chitsitsi Chotsitsa pansi pa gawo la Data. Sankhani Malo Ogwirizana.

Izi zimabweretsa fomu ya Ma Connection. Dinani pa tanthauzo la tanthauzo. Izi zikuwonetsani inu zokhudzana ndi kugwirizana kwa kugwirizana komweku ndi SQL Server. Pamene imatanthawuza fayilo yothandizira, deta imakhala yotsekedwa mu spreadsheet.

06 pa 15

Sinthani Malo Ogwirizanitsa Ndi Pepala

Sinthani tebulo ku funso la SQL.

Sinthani mtundu wa Lamulo kuchokera ku Table kufika ku SQL ndi kulembetsa Malembo Olemba omwe alipo ndi SQL Sewani Yanu. Pano pali funso limene ndinapanga kuchokera ku database database ya 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.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
Kupanga.Product.Name,
Malonda.ZowonjezeraCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Malonda.Customer.CustomerType,
Kupanga.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
Kuchokera ku Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderMsalimo ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
YAM'MBUYO YOTSATIRA MITU YA NKHANI YAM'MBUYO YOTSATIRA TUMIZANI LINKI SINDIKIZANI KOPERANI MAPODIKASITI MITU YA NKHANI
Kupanga.Product.ProductID INNER YAM'MBUYO ZOTHANDIZA
Sales.SalesOrderHeader.CustomerID = Ma Sales.Customer.CustomerID NDI
Sales.SalesOrderHeader.CustomerID = Malonda.Customer.CustomerID INNER JOIN
Kugulitsa.KopezaPaulendoWotumiza ku Sales.Customer.CustomerID =
Malonda.ZimalondaCustomer.CustomerID INNER JOIN
Zojambula.ProductSubcategory KU Kupanga.Product.ProductSubcategoryID =
Zopanga.ProductSubcategory.ProductSubcategoryID

Dinani OK.

07 pa 15

Landirani Chenjezo Yogwirizana

Dinani pa Inde ku Chenjezo la Kugwirizana.

Mudzalandira bokosi la bokosi la machenjezo la Microsoft Excel. Izi ndichifukwa chakuti tasintha chidziwitso cha kugwirizana. Pamene tinayambitsa kulumikizana, adasungira zomwe zili kunja .fayile yaODOD (ODBC Data Connection). Deta yomwe ili m'bukuli inali yofanana ndi fayilo ya .ODC mpaka titasintha kuchokera ku tebulo la lamulo la mtundu wa SQL ku gawo # 6. Chenjezo likukuwuzani kuti deta sichiyanjanitsa ndipo kutchulidwa kwa fayilo yakunja mu bukhuli lidzachotsedwa. Izi ndi zabwino. Dinani Inde.

08 pa 15

Pivot Table Yowonjezera kwa SQL Server ndi Funso

PivotTable ili okonzeka kuti muwonjezere deta.

Izi zimabwereranso ku bukhu la ntchito ya Excel 2010 ndi Vesi Lopanda kanthu. Mukhoza kuona kuti minda yomwe ilipo tsopano ndi yosiyana ndipo ikufanana ndi minda mu funso la SQL. Tsopano tikhoza kuyamba kuwonjezera minda ku PivotTable.

09 pa 15

Onjezani Masamba ku Pivot Table

Onjezani masamba ku PivotTable.

Mu Mndandanda wa Masamba Wowoneka PivotTable, dulani Chigawo Chachigawo ku Malo Olemba Maadiresi, Dongosolo Lotsalira ku Malemba a Malemba ndi TotalDue to Values. Chithunzicho chikuwonetsa zotsatira. Monga momwe mukuonera, munda wa tsiku uli payekha ndipo PivotTable yakhazikitsa ndondomeko ya tsiku lililonse lapadera. Mwamwayi, Excel 2010 ili ndi zina zomwe zakhala zikugwiritsidwa ntchito kuti zitithandize kukonzekera madera a tsiku.

10 pa 15

Onjezani magulu a Masamba a Tsiku

Onjezerani Gulu lamasewera a tsiku.

Ntchito Yogwirizanitsa imatilola kulinganiza masiku mu zaka, miyezi, nyumba, ndi zina. Izi zidzakuthandizani kufotokozera deta ndikupanga mosavuta wogwiritsa ntchitoyo. Dinani pomwepo pa imodzi ya mutu wa mutuwo ndi kusankha Gulu lomwe limabweretsa fomu ya gulu.

11 mwa 15

Sankhani Magulu ndi Malamulo

Sankhani zinthu zogwiritsa ntchito patsiku lasuku.

Malinga ndi mtundu wa deta yomwe mukugwirana, mawonekedwewo adzawoneka mosiyana. Excel 2010 imakulolani kuti muzilemba masiku, mawerengero ndi deta yosankhidwa. Tikugwiritsira ntchito OrderDate mu phunziro ili fomuyo iwonetsanso zosankha zokhudzana ndi gululi.

Dinani pa Miyezi ndi Zaka ndipo dinani OK.

12 pa 15

Pivot Table Yopangidwa ndi Zaka ndi Miyezi

Masamba a tsiku amagawidwa ndi zaka ndi miyezi.

Monga momwe mukuwonera pa chithunzi pamwambapa, detayi ikuphatikizidwa chaka choyamba ndi mwezi. Aliyense ali ndi chizindikiro chowonjezera komanso chosasintha chimene chimakupatsani kuti muwonjezere ndi kugwa malinga ndi momwe mukufunira kuona deta.

Panthawiyi, PivotTable ndi yabwino kwambiri. Munda uliwonse ukhoza kusankhidwa koma vuto liribe chithunzithunzi cha m'mene zimakhalira panopa. Ndiponso, zimatengera kambirimbiri kuti musinthe malingaliro.

13 pa 15

Ikani Slicer (Chatsopano mu Excel 2010)

Onjezerani Slicers ku PivotTable.

Slicers ndi atsopano mu Excel 2010. Slicers kwenikweni ndi ofanana ndi mawonekedwe posankha mafayilo alipo minda ndi kulenga Report Filters ngati chotsacho mukufuna kufufuza pa si PivotTable panopa mawonedwe. Chinthu chabwino ichi pa Slicers ndikumakhala kosavuta kuti wogwiritsa ntchito kusintha malingaliro ake pa PivotTable komanso kupereka zizindikiro zowonetsera poyerekeza ndi zomwe zilipo pano.

Kuti muike Slicers, dinani pa Zosankha tab ndipo dinani pa Insert Slicer kuchokera ku mtundu wa Fuluta & Fuluta. Sankhani Insert Slicer yomwe imatsegula mawonekedwe a Insert Slicers. Onani malo ambiri omwe mukufuna kukhala nawo. Mu chitsanzo chathu, ndawonjezera zaka, CountryRegionName ndi ProductCategory. mukhoza kuyika Slicers kumene mumawafuna. Mwachikhazikitso, makhalidwe onse amasankhidwa omwe amatanthawuza kuti palibe mafayilo omwe agwiritsidwa ntchito.

14 pa 15

Pivot Table ndi Othandizira Slicers

Zokonza zimapangitsa kuti ogwiritsa ntchito awulutse PivotTables.

Monga mukuonera, Slicers amasonyeza deta yonse monga asankhidwa. Zili bwino kwa wosuta ndendende momwe deta ikuwonera pa PivotTable.

15 mwa 15

Sankhani Makhalidwe Ochokera ku Slicers Ndizomwe Zowonjezera Pivot Table

Sankhani kuphatikiza kwa Slicers kuti musinthe mawonedwe a deta.

Dinani paziyanjano zosiyana siyana ndikuwona momwe lingaliro la PivotTable likusinthira. Mungagwiritse ntchito machitidwe a Microsoft omwe akugwiritsidwa ntchito pa Slicers kutanthauza kuti ngati mutha kugwiritsa ntchito Control + Dinani kuti muzisankha miyezo yambiri kapena Shift + Dinani kuti muyankhe miyezo yambiri. Slicer iliyonse ikuwonetsera machitidwe omwe asankhidwa omwe amatsimikizira momveka bwino momwe dziko la PivotTable lirili muzinthu zosankhidwa. Mukhoza kusintha mafashoni a Slicers ngati mukufuna podutsa pa Quick Styles pansi pa Slicer gawo la Options tab.

Kuyamba kwa Slicers kwatithandiza kwambiri kusintha kwa PivotTables ndipo kwasintha Excel 2010 kuyandikira kwambiri kukhala chida chazamalonda chazamalonda. PivotTables yayamba bwino kwambiri mu Excel 2010 ndipo pamene ikugwirizana ndi PowerPivot yatsopano imapanga malo apamwamba kwambiri owonetsera zachilengedwe.