Paano Itakda ang Excel 2010 Pivot Tables

by Tim Perdue

01 ng 15

Final Result

Ito ang huling resulta ng tutorial na Hakbang sa Hakbang na ito - Mag-click sa larawan upang makita ang isang buong laki ng bersyon.

Nagkaroon ng isang agwat sa pagitan ng Microsoft Excel at ang nangungunang tier ng negosyo katalinuhan (BI) platform para sa maraming mga taon. Ang mga pagpapahusay ng Microsoft Excel 2010 Pivot Table kasama ang ilang iba pang mga tampok ng BI ay ginawa itong isang tunay na katunggali para sa enterprise BI. Ang tradisyonal na ginamit sa Excel para sa standalone analysis at ang karaniwang tool na ang lahat ay nag-export ng kanilang mga huling ulat sa. Ang propesyonal na katalinuhan sa negosyo ay ayon sa kaugalian ay nakalaan para sa mga kagustuhan ng SAS, Mga Bagay sa Negosyo at SAP.

Microsoft Excel 2010 (kasama ang Excel 2010 Pivot Table) kasama ang SQL Server 2008 R2, SharePoint 2010 at ang libreng Microsoft Excel 2010 add-on na "PowerPivot" ay nagresulta sa isang mataas na dulo ng katalinuhan ng negosyo at pag-uulat ng solusyon.

Ang tutorial na ito ay sumasaklaw sa isang tuwid pasulong sitwasyon sa isang Excel 2010 PivotTable konektado sa isang SQL Server 2008 R2 database gamit ang isang simpleng query SQL. Gumagamit ako ng Slicers para sa visual na pag-filter na bago sa Excel 2010. Masasakop ko ang mas kumplikadong pamamaraan ng BI gamit ang Data Analysis Expressions (DAX) sa PowerPivot para sa Excel 2010 sa malapit na hinaharap. Ang pinakabagong release ng Microsoft Excel 2010 ay maaaring magbigay ng isang tunay na halaga para sa iyong komunidad ng gumagamit.

02 ng 15

Ipasok ang Pivot Table

Puwesto ang iyong cursor kung saan mismo gusto mo ang iyong pivot table at mag-click sa Insert | Pivot Table.

Maaari kang magpasok ng Pivot Table sa isang bago o umiiral na workbook ng Excel. Baka gusto mong isaalang-alang ang pagpoposisyon ng iyong cursor pababa ng ilang mga hanay mula sa itaas. Bibigyan ka nito ng espasyo para sa isang header o impormasyon ng kumpanya kung sakaling ibabahagi mo ang worksheet o i-print ito.

Buksan ang isang bago o umiiral na Excel 2010 workbook at mag-click sa cell kung saan mo nais ang itaas na kaliwang sulok ng Pivot Table upang maging.
Mag-click sa tab na Magsingit at mag-click sa drop na PivotTable sa seksyon ng Mga Tab. Pumili ng PivotTable. Ilulunsad nito ang form na Create PivotTable dialog.

03 ng 15

Ikonekta ang Pivot Table sa SQL Server (o Iba pang Database)

Lumikha ng iyong SQL query at pagkatapos ay kumonekta sa SQL Server upang i-embed ang string data ng koneksyon sa Excel spreadsheet.

Maaaring makuha ng Excel 2010 ang data mula sa lahat ng mga pangunahing provider ng RDBMS (Relational Database Management System) . Ang mga driver ng SQL Server ay dapat na magagamit para sa koneksyon sa pamamagitan ng default. Ngunit ang lahat ng mga pangunahing database ng software ay gumawa ng ODBC (Buksan ang Database Connectivity) driver upang payagan mong gawin ang koneksyon. Tingnan ang kanilang website kung kailangan mong mag-download ng mga driver ng ODBC.

Sa kaso ng tutorial na ito, ako ay kumukonekta sa SQL Server 2008 R2 (SQL Express libreng bersyon).

A - Ang Lumikha ng PivotTable form ay ang unang form sa paglikha ng koneksyon sa SQL Server. Piliin ang "Gumamit ng panlabas na mapagkukunan ng data" at mag-click sa pindutan ng Piliin ang Koneksyon. Iwanan ang lokasyon kung saan ilalagay ang Pivot Table maliban kung gusto mong lumikha ng isang bagong worksheet at ilagay ito doon.
B - Ang kasalukuyang form ng Connections ay naglilista ng anumang mga koneksyon sa kasalukuyang workbook, sa iyong computer at sa network na kasalukuyang nakakonekta sa iyo. Ang mga kasalukuyang koneksyon ay talagang mga tekstong file lamang na may impormasyon ng koneksyon na kinakailangan upang ma-access ang isang partikular na pinagmulan ng data. Sa aming kaso, kami ay gumawa ng isang bagong pinagmulan ng data. Mag-click sa pindutan ng Mag-browse para sa Higit Pa.
C - Mag-click sa pindutan ng Bagong Pinagmulan ay ilunsad ang Data Connection Wizard.
D - Piliin ang Microsoft SQL Server at i-click ang Susunod.
E - Ipasok ang pangalan ng Server at mag-log sa mga kredensyal. Piliin ang naaangkop na paraan ng pagpapatunay. Kung hindi ka sigurado kung aling paraan ang gagamitin, kontakin ang administrator ng iyong database.
- Gamitin ang Windows Authentication: Ang pamamaraang ito ay gumagamit ng iyong login sa network upang ma-access ang mga database ng SQL Server.
- Gamitin ang sumusunod na Pangalan ng User at Password: Ang paraang ito ay ginagamit kapag ang SQL Server ay na-configure na may standalone na mga gumagamit upang ma-access ang mga database.
F - Sa hakbang na ito, pipiliin namin ang isang talahanayan bilang isang placeholder. Kami ay papalitan ang talahanayan ng pasadyang SQL na magbibigay ng eksaktong data na gusto namin sa aming workbook sa Excel.
- Piliin ang database na iyong ikinukunekta. Sa halimbawang ito, kami ay nakakonekta sa database ng sample ng AdventureWorks na ibinigay ng Microsoft. Lagyan ng tsek ang Ikonekta sa isang partikular na talahanayan at piliin ang unang talahanayan. Tandaan, hindi kami makakakuha ng pagkuha ng data mula sa table na ito.
- I-click ang Tapos na magsara sa wizard at ibalik ka sa workbook. Magpapalit kami ng talahanayan ng placeholder para sa aming pasadyang query sa SQL.

Mababalik ka sa form na Create PivotTable (A). I-click ang OK.

04 ng 15

Pivot Table Temporarily Connected to SQL Table

Ang PivotTable ay konektado sa SQL Server na may placeholder table.

Sa puntong ito, nakakonekta ka sa talahanayan ng placeholder at mayroon kang walang laman na PivotTable. Maaari mong makita sa kaliwa ay ang PivotTable ay magiging at sa kanan may isang listahan ng magagamit na mga patlang.

05 ng 15

Buksan ang Mga Katangian ng Koneksyon

Buksan ang Form ng Mga Katangian ng Connection.

Bago simulan namin ang pagpili ng data para sa PivotTable, kailangan naming baguhin ang koneksyon sa query sa SQL. Tiyaking ikaw ay nasa tab na Mga Pagpipilian at mag-click sa Drop Data Source drop down mula sa seksyon ng Data. Piliin ang Mga Katangian ng Koneksyon.

Pinagsasama nito ang form ng Mga Katangian ng Koneksyon. Mag-click sa tab na Kahulugan. Ipinapakita nito sa iyo ang impormasyon ng koneksyon para sa kasalukuyang koneksyon sa SQL Server. Habang tumutukoy ito sa isang file ng koneksyon, ang data ay aktwal na naka-embed sa spreadsheet.

06 ng 15

I-update ang Mga Katangian ng Koneksyon sa Query

Baguhin ang talahanayan sa query sa SQL.

Baguhin ang Uri ng Command mula sa Table sa SQL at i-overwrite ang umiiral na Command Text sa iyong SQL Query. Narito ang query na aking nilikha mula sa database ng AdventureWorks sample:

Piliin ang 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,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer SA
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

I-click ang OK.

07 ng 15

Tumanggap ng Babala ng Koneksyon

Mag-click sa Oo sa Babala ng Koneksyon.

Makakatanggap ka ng isang dialog box na Microsoft Excel Warning. Ito ay dahil binago namin ang impormasyon ng koneksyon. Kapag orihinal naming nilikha ang koneksyon, nai-save na ang impormasyon sa isang panlabas na .ODC file (ODBC Data Connection). Ang data sa workbook ay kapareho ng .ODC file hanggang nagbago kami mula sa isang uri ng command ng table sa uri ng command ng SQL sa Hakbang # 6. Ang babala ay nagsasabi sa iyo na ang data ay hindi na naka-sync at ang reference sa panlabas na file sa workbook ay aalisin. Ito ay OK. I-click ang Oo.

08 ng 15

Pivot Table Connected to SQL Server With Query

PivotTable ay handa na para sa iyo upang magdagdag ng data.

Ito ay tumatagal ng pabalik sa Excel 2010 workbook na may isang walang laman na PivotTable. Maaari mong makita na ang magagamit na mga patlang ay naiiba na ngayon at tumutugma sa mga patlang sa query SQL. Maaari na tayong magsimulang magdagdag ng mga patlang sa PivotTable.

09 ng 15

Magdagdag ng Mga Patlang sa Pivot Table

Magdagdag ng mga patlang sa PivotTable.

Sa PivotTable Field List, i-drag ProductCategory sa Row Label area, OrderDate sa Area ng Label na Label at TotalDue sa Area na Halaga. Ipinapakita ng larawan ang mga resulta. Tulad ng iyong nakikita, ang petsa ng petsa ay may mga indibidwal na petsa upang ang PivotTable ay lumikha ng haligi para sa bawat natatanging petsa. Sa kabutihang palad, ang Excel 2010 ay may ilang mga built in na function upang matulungan kaming ayusin ang mga field ng petsa.

10 ng 15

Magdagdag ng Pagpapangkat para sa Mga Patlang ng Petsa

Magdagdag ng Mga Grouping para sa field ng petsa.

Ang pag-grupo ng function ay nagpapahintulot sa amin na ayusin ang mga petsa sa mga taon, buwan, tirahan, atbp Ito ay makakatulong sa buod ng data at gawing mas madali para sa gumagamit na makipag-ugnay dito. Mag-right click sa isa sa mga header ng hanay ng petsa at piliin ang Grupo na pinagsasama ang Pangkat ng pangkat.

11 ng 15

Piliin ang Pagpapangkat Ayon sa Mga Halaga

Pinipili ang pagpapangkat ng mga item para sa field ng petsa.

Depende sa uri ng data na iyong pinagsasama, ang form ay magiging kaunti lamang. Pinapayagan ka ng Excel 2010 na mag-grupo ng mga petsa, numero at piniling data ng teksto. Pinagsasama namin ang OrderDate sa tutorial na ito upang ang form ay magpapakita ng mga opsyon na may kaugnayan sa mga pagpapangkat ng petsa.

Mag-click sa Mga Buwan at Taon at i-click ang OK.

12 ng 15

Pivot Table Grouped by Years and Months

Ang mga patlang ng petsa ay pinagsama sa pamamagitan ng mga taon at buwan.

Tulad ng makikita mo sa larawan sa itaas, ang data ay naka-grupo sa unang taon at pagkatapos ay sa pamamagitan ng buwan. Ang bawat isa ay may plus at minus sign na nagbibigay-daan sa iyo upang palawakin at tiklupin depende sa kung paano mo gustong makita ang data.

Sa puntong ito, ang PivotTable ay medyo kapaki-pakinabang. Maaaring i-filter ang bawat isa sa mga patlang ngunit ang problema ay walang visual na bakas sa kasalukuyang estado ng mga filter. Gayundin, kinakailangan ng ilang pag-click upang baguhin ang view.

13 ng 15

Ipasok ang Slicer (Bago sa Excel 2010)

Magdagdag ng mga Slicer sa PivotTable.

Ang mga slicer ay bago sa Excel 2010. Ang mga slicer ay karaniwang ang katumbas ng mga setting ng visual na pagtatakda ng mga umiiral na field at paglikha ng Mga Filter ng Ulat kung ang item na gusto mong i-filter ay wala sa kasalukuyang view ng PivotTable. Ang magaling na bagay tungkol sa mga Slicer ay napakadali para sa user na baguhin ang pagtingin sa data sa PivotTable pati na rin ang pagbibigay ng mga visual indicator sa kasalukuyang estado ng mga filter.

Upang magsingit ng Slicers, mag-click sa tab na Mga Pagpipilian at mag-click sa Ipasok ang Slicer mula sa seksyon ng Pagsunud-sunurin at Filter. Piliin ang Ipasok ang Slicer na bubukas ang form na Insert Slicers. Suriin ang maraming mga patlang na nais mong magkaroon ng magagamit. Sa aming halimbawa, nagdagdag ako ng Taon, CountryRegionName at ProductCategory. baka kailangan mong ilagay ang Slicers kung saan mo gusto ang mga ito. Bilang default, ang lahat ng mga halaga ay napili na nangangahulugan na walang mga filter na naipapatupad.

14 ng 15

Pivot Table With User Friendly Slicers

Pinapadali ng mga slicer para sa mga gumagamit na i-filter ang PivotTables.

Tulad ng iyong nakikita, ipinapakita ng Slicer ang lahat ng data bilang napili. Ito ay napakalinaw sa gumagamit nang eksakto kung ano ang data ay nasa kasalukuyang view ng PivotTable.

15 ng 15

Pumili ng Mga Halaga Mula sa Slicer Aling Mga Update Pivot Table

Pumili ng mga kumbinasyon ng Mga Slicer upang baguhin ang pagtingin sa data.

Mag-click sa iba't ibang mga kumbinasyon ng mga halaga at makita kung paano ang pagtingin sa mga pagbabago sa PivotTable. Maaari mong gamitin ang karaniwang pag-click ng Microsoft sa Slicers na nangangahulugang kung maaari mong gamitin ang Control + I-click upang pumili ng maramihang halaga o Shift + I-click upang pumili ng isang hanay ng mga halaga. Ipinapakita ng bawat Slicer ang mga napiling halaga na ginagawang talagang halata kung ano ang estado ng PivotTable sa mga tuntunin ng mga filter. Maaari mong baguhin ang mga estilo ng Mga Slicer kung gusto mo sa pamamagitan ng pag-click sa drop sa Quick Styles sa seksyon ng Slicer ng tab na Mga Pagpipilian.

Ang pagpapakilala ng mga Slicer ay talagang pinabuting ang usability ng PivotTables at inilipat Excel 2010 mas malapit sa pagiging isang propesyonal na kasangkapan sa negosyo katalinuhan. Ang PivotTables ay napabuti ng kaunti sa Excel 2010 at kapag pinagsama sa bagong PowerPivot lumilikha ng isang napakataas na pagganap ng analitiko na kapaligiran.