Nzira yekugadzira Excel 2010 Pivot Tables

by Tim Perdue

01 ye15

Chigumo chekupedzisira

Ichi ndicho chigumisiro chekupedzisira cheNhanho iyi neChidzidzo chekudzidzisa - Dzvanya pamufananidzo kuti uone huwandu hwakakwana.

Pakave nepakati pakati peMicrosoft Excel uye nepamusoro-soro bhizinesi rezvebhizimisi (BI) mapurisa kwemakore mazhinji. Kuvandudzika kweMicrosoft Excel 2010 Pivot Table pamwe chete nezvimwe zvinhu zveBI zvakasiyana-siyana zvakaita kuti rive mukwikwi chaiwo webhizimisi BI. Excel yakagara yashandiswa pakuongorora zvakananga uye chimiro chakakwana icho munhu wose anotumira zvinyorwa zvekupedzisira. Bhizimisi bhizinesi rezvebhizimisi zvakagara zvachengetwa kune zvinodiwa zveSAS, Business Objects uye SAP.

Microsoft Excel 2010 (pamwe neExcel 2010 Pivot Table) pamwe chete neSQL Server 2008 R2, SharePoint 2010 uye mahwanda eMicrosoft Excel 2010 yakawedzerwa "PowerPivot" yakaguma nehupamhi hwekutsvaga bhizimusi nekugadzirisa.

Izvi zvidzidzo zvinotarisana nemamiriro ezvinhu akajeka neEexcel 2010 PivotTable yakabatana neSQL Server 2008 R2 database vachishandisa SQL iri nyore mhinduro. Ndiri kushandisawo Slicers kuti kuonekwe kwekuona izvo zvitsva muExcel 2010. Ndichaona maitiro akaoma eBI achishandisa Data Analysis Expressions (DAX) mu PowerPivot yeExcel 2010 munguva pfupi iri mberi. Izvi zvinobudiswa zveMicrosoft Excel 2010 zvinokwanisa kupa kukosha kwekushandisa kwevanhu vemunharaunda yako.

02 ye15

Isa Pivot Table

Isa nzvimbo yako yakakosha chaizvo kwaunoda tafura yako yepivot uye tora paIndaneti | Pivot Table.

Iwe unogona kuisa Pivot Table mune rimwe bhuku kana kuti riripo re Excel. Iwe unogona kunge uchifunga kufunga nzvimbo yako pasi pasi mitsetse shoma kubva kumusoro. Izvi zvingakupa nzvimbo yekutungamira musoro kana kambani kana iwe uchigovera pepa rekushandura kana kuti uridhindure.

Vhura bhuku rekushanda reExpcel 2010 kana kuti riripo uye chengeta pane sero apo iwe unoda kona yepamusoro yekona yePivot Table kuti ive.
Dzvanya paItai tab uye chengetera pasi pePivotTable iri pasi muTables section. Sarudza PivotTable. Izvi zvinotanga kuumba iyo PivotTable fomu yekugovera.

03 we15

Connect Pivot Table ku SQL Server (kana Other Database)

Gadzira SQL yako svondo uye taurirana kuSQL Server kuti uvhare foni yedhina yekubatanidza muExpcel spreadsheet.

Excel 2010 inogona kutora dhamari kubva kune makuru makuru eDBBMS (Relational Database Management System) vateresi. Shanduro dzeSQL Server dzinofanirwa kuwanika nokuda kwekubatanidza nekutadza. Asi yose huru database yakagadzirwa inoita ODBC (Open Database Connectivity) vatyairi kukubvumira kuti uite hukama. Chengetai webhusaiti yavo kana iwe uchida kutora madereji eODBC.

Muchiitiko chekudzidzisa uku, ndiri kushamwaridzana neSQL Server 2008 R2 (SQL Express version yakasununguka).

A - The Create PivotTable fomu ndiyo fomu yekutanga pakusangana kweSQL Server. Sarudza "Shandisa iri kunze kwemashoko" uye chengetera pane Bhokisi Rokushandisa. Siya nzvimbo iyo Pivot Table ichaiswa kunze kwekuti iwe unoda kugadzira pepa rekushanda uye uise ipapo.
B - Inonzi Connections Iripo inotarisa chero hunobatanidza mubhuku rino rekushandisa, pamakombiyuta yako uye nheyo yawakabatanidzwa ikozvino. Kubatanidza kuripo kunongova kungoona mafaira emafaira nehutano hwekubatanidza hunodiwa kuti uwane imwe nzvimbo yepa data. Mune mhaka yedu, tichaenda kugadzira dhiyabhorosi itsva. Dzvanya pane Browse pane mamwe Bhokisi.
C - Kushinha paIndaneti yeNew Source ichaunza Data Connection Wizard.
D - Sarudza Microsoft SQL Server uye chovha Next.
E-Tumira zita reSeva uye ugoona pane zvidzidzo. Sarudza nzira yakakodzera yekuvimbisa. Kana iwe usingazivi kuti ndeipi nzira yaungashandisa, taura ne database yako administrator.
- Shandisa Windows Authentication: Iyi nzira inoshandisa network yako kupinda kuti iwane SQL Server databases.
- Shandisa iri rinotevera Zita reMushandisi uye Nhepfenyuro: Iyi nzira inoshandiswa apo SQL Server yakagadzirirwa nevashandisi vemazuva ose kuti vawane zvinyorwa.
F - Mudanho rino, isu tichaenda tafura semunhu. Tichave tatsiva tafura netsika yeSQL iyo ichapa chaiyo data yatinoda mubhuku redu re Excel.
- Sarudza dhesi raunenge uchisangana nayo. Mumuenzaniso uyu, tiri kubatana neDynamicWorld sample sangano rakapiwa neMicrosoft. Chengetedza Ita kune imwe tafura uye sarudza tafura yekutanga. Yeuka, isu hatizove tichiwana deta kubva petafura iyi.
- Dhinda Finish iyo ichavhara wizard uye ichakudzorera kubhuku rebhuku. Tichave tichibvisa tafura yepamberi kwetsika yedu SQL mhinduro.

Iwe uchadzoswa kuGadzira PivotTable fomu (A). Dzvanya OK.

04 we15

Peji yePivot Yakazongowanikwa kuSQL Table

PivotTable inosangana neSQL Server nefurafura yepaiti.

Panguva ino, iwe wakabatana nefurafura yepaiti uye iwe une PivotTable isina chinhu. Iwe unogona kuona kuruboshwe kwaive nePivotTable ichave uye kune kurudyi pane urongwa hwemainowanikwa masimi.

05 of 15

Vhura Zviratidzo Zvokubatanidza

Vhura Fomu Yemafungiro eFomu fomu.

Tisati tatanga kusarudza data yePivotTable, tinoda kuchinja kubatana kweSQL mhinduro. Iva nechokwadi kuti uri pane Zvokusarudza tab uye tora paDesource Data Source ichidzika kubva muchikamu cheData. Sarudza Zvinhu Zvakagadzikana.

Izvi zvinounza fomu yeZvakirano zveMafungiro. Dzvanya pane Tsanangudzo tab. Izvi zvinokuratidza iwe ruzivo rwekubatanidza kwekubatanidza ikozvino kuSQL Server. Kunyange zvazvo iyo inotaura nezvefaira rekubatanidza, iyo data inowanikwa yakaiswa mupasiteteti.

06 of 15

Kuvandudza Zvirungo Zvokubatana NeChikumbiro

Shandura tafura kuSQL mhinduro.

Shandura Mutemo Wemira kubva paTebheni kuenda kuSQL uye wonyora pasi Mirairo yeMirairo iripo neSQL Yechikumbiro chako. Heino mhinduro yandakaumba kubva kuWadgetWorks sample database:

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,
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 ON
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 Kuita.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Dzvanya OK.

07 we15

Gamuchira Connection Warning

Dzvanya paIe kuAkunyorerana Kunyeverwa.

Iwe uchagamuchira Microsoft Excel Warning dialog box. Izvi ndechokuti takashandura ruzivo rwekubatana. Apo isu takatanga kusika ukama, yakachengetedza ruzivo rwekunze .ODC file (ODBC Data Connection). Nhoroondo iri mubhuku rebhuku yakanga yakafanana neyo .ODC faira kusvikira tashandura kubva patafura yemirayiridzo yerudzi rwechikwata cheSQL mutemo muNhanho # 6. Nyevero irikutaurira kuti data haisati iri mu sync uye kureva kune yekunze faira mubhuku rebhuku richabviswa. Izvi zvakanaka. Dhora Yega.

08 we15

Pivot Table Yakabatanidzwa kuSQL Server neChikumbiro

PivotTable yakagadzirira iwe kuti uwedzere deta.

Izvi zvinotorazve kubhuku rebhuku raExcel 2010 rine PivotTable isina chinhu. Unogona kuona kuti minda iripo ikozvino yakasiyana uye inoenderana neminda munyaya yeSQL. Isu tinogona kutanga kutanga kuwedzera masimi kuPivotTable.

09 we15

Wedzera Munda kune Pivot Table

Wedzera minda kuPivotTable.

Mune PivotTable Field List, dhonza ProductCategory kuRow Labels nzvimbo, Dhaira Dha kuArase Mahara nzvimbo uye TotalDue kune Values nzvimbo. Mufananidzo wacho unoratidza zvawanikwa. Sezvaunogona kuona, munda wemusi iwoyo unotarisa kuitira kuti PivotTable yakagadzire korona yezuva rimwe nerimwe rakasiyana. Nenzira yakanaka, Excel 2010 ine zvimwe zvakavakwa mumabasa kuitira kuti tibatsire kuronga zuva reminda.

10 pa15

Wedzera Chikwata Chemasikati Masikati

Wedzera Groupings for date field.

Basa Rokugadzirira rinotibvumira kuronga matanho mumakore, mwedzi, makamuri, etc. Izvi zvichabatsira kufupikisa data uye kuita kuti zvive nyore kumushandisi kuti ataure nayo. Kurudyi pfuurira pane imwe yezuva rakatenderera misoro uye sarudza Gulu rinounza fomu yeGrouping.

11 pa15

Sarudza Kubatana Nemitemo

Sarudza kuunganidza zvinhu zvemasikati.

Zvichienderana nemhando ye data iwe uri kuunganidza, fomu ichaita seyakasiyana. Excel 2010 inokubvumira kuunganidza mazuva, nhamba uye zvakasarudzwa zvinyorwa. Isu tiri kuunganidza OrderDate mune iyi tutorial kuitira kuti fomu icharatidza zvisarudzo zvine chokuita nekutambidzwa kwemazuva.

Dzvanya paMwedzi uye Makore uye chovha OK.

12 pa15

Pivot Tafura Yakabatanidzwa nemakore nemwedzi

Musi masikati akabatanidzwa nemakore nemwedzi.

Sezvaunogona kuona mumufananidzo wepamusoro, deta iri yakarongerwa negore kutanga uye nemwedzi. Chimwe nechimwe chine chiratidzo chekuwedzera uye chinoshandiswa chinokubvumira kuti uwedzere uye ugowa nekuda kwekuti unoshuva kuona sei data.

Pano, PivotTable yakakosha chaizvo. Zuva rimwe nerimwe reminda rinogona kusvibiswa asi dambudziko racho harisi izvo zvinoratidzika sezviri kuitika iye zvino ma filters. Uyezve, zvinotora zvishoma kuti uchinje maonero.

13 pa15

Isa Slicer (Itsva muExcel 2010)

Wedzera Slicers kuPivotTable.

Slicers idzva muExcel 2010. Slicers inonyanya kuenzana nekuona mafungiro ekutsvaga emasimo akange aripo nekugadzira Report Filters munyaya iyo chinhu chaunoda kuisa pane hachisi pane PivotTable parizvino. Ichi chinhu chakanaka pamusoro peSlicers zvinowanzova nyore kwazvo kumushandisi kuchinja maonero e data muPivotTable pamwe nekupa zviratidzo zvinoratidzika sezviri kuitika iye zvino ma filters.

Kuti uise Slicers, chengeta pane Options pane uye chengeta pa Insert Slicer kubva muGadziriro & Firiji chikamu. Sarudza Inser Slicer iyo inotanga fomu Insert Slicers. Tarisa seminda yakawanda sezvaunoda kuvapo. Mumuenzaniso wedu, ndakawedzera Makore, CountryRegionName uye ProductCategory. iwe unogona kuisa Slicers kwaunoda ivo. Nokutadza, zvose zvehutano zvakasarudzwa izvo zvinoreva kuti hapana mafiritsi akashandiswa.

14 pa15

Pivot Table Newe Ushamwari Shamwari Slicers

Slicers inoita kuti zvive nyore kune vashandisi kufuta PivotTables.

Sezvaunogona kuona, Slicers inoratidza data yose sechasarudzwa. Zvinonyatsojeka kumunhu kunyatsotsvaga dhidhi iri maonero azvino ePivotTable.

15 pa15

Sarudza Maitiro Kubva Pazvikwata Zvichidzokorora Pivot Table

Sarudza zvikamu zveSlicers kuti uchinje maonero e data.

Dzvanya pakusanganiswa kwakasiyana-siyana kwehutano uye ona kuti maonero ePivotTable anochinja sei. Iwe unogona kushandisa chimiro cheMicrosoft chichinyorwa muSlicers zvinoreva kuti kana iwe ukakwanisa kushandisa Kudzora + Kiraya kuti usarudze maitiro akawanda kana Shift + Click kuti uwane mararamiro ehutano. Slicure imwe neimwe inoratidza maitiro akasarudzwa anoita kuti zvive pachena zviri pachena kuti nyika yePivotTable iri maererano nemafuta. Iwe unogona kuchinja mafaira eSlicers kana uchida nekukanya pa Quick Styles inodonha pasi muchikamu cheSlicer pane Options tab.

Kutanga kweSlicers kwakanyatsogadzirisa kushandiswa kwePivotTables uye kwaita kuti Excel 2010 iswedere pedyo nekuve yeruzivo rwebhizimisi rekuchenjera. PivotTables yakavandudzika chaizvo muExcel 2010 uye kana yakabatanidzwa nePowerPivot itsva inogadzira yakakwirira kwazvo inyanzvi yekuongorora.