Sut i Gosod Tabl Pivot Excel 2010

by Tim Perdue

01 o 15

Canlyniad Terfynol

Dyma ganlyniad terfynol y tiwtorial Cam wrth Gam hwn - Cliciwch ar y ddelwedd i weld fersiwn llawn.

Bu bwlch rhwng llwyfannau Microsoft Excel a'r cudd-wybodaeth busnes haen uchaf (BI) ers blynyddoedd lawer. Mae gwelliannau Tabl Pivot Microsoft Excel 2010 ynghyd â chwpl o nodweddion BI eraill wedi ei gwneud yn gystadleuydd go iawn ar gyfer menter BI. Yn draddodiadol, defnyddiwyd Excel ar gyfer dadansoddiad annibynnol a'r offeryn safonol y mae pawb yn allforio eu hadroddiadau terfynol. Yn draddodiadol, mae gwybodaeth fusnes broffesiynol wedi'i neilltuo ar gyfer rhai fel SAS, Object Objects a SAP.

Mae Microsoft Excel 2010 (gyda Tabl Pivot Excel 2010) ynghyd â SQL Server 2008 R2, SharePoint 2010 ac ychwanegiad Microsoft Excel 2010 rhad ac am ddim "PowerPivot" wedi arwain at ddatrysiad busnes ac adrodd busnes diwedd uchel.

Mae'r tiwtorial hwn yn cwmpasu senario syth gyda PivotTable Excel 2010 wedi'i gysylltu â gronfa ddata SQL Server 2008 R2 gan ddefnyddio ymholiad SQL syml. Rwyf hefyd yn defnyddio Slicers ar gyfer hidlo gweledol sy'n newydd yn Excel 2010. Byddaf yn ymdrin â thechnegau BI mwy cymhleth gan ddefnyddio Mynegiadau Dadansoddi Data (DAX) yn PowerPivot ar gyfer Excel 2010 yn y dyfodol agos. Gall y datganiad diweddaraf hwn o Microsoft Excel 2010 roi gwerth go iawn i'ch cymuned ddefnyddiwr.

02 o 15

Mewnosod Tabl Pivot

Safwch eich cyrchwr yn union lle rydych chi eisiau eich bwrdd pivot a chliciwch ar Insert | Tabl Pivot.

Gallwch chi fewnosod Tabl Pivot mewn llyfr gwaith Excel neu newydd. Efallai y byddwch am ystyried gosod eich cyrchwr i lawr ychydig rhesi o'r brig. Bydd hyn yn rhoi lle i chi am wybodaeth pennawd neu gwmni rhag ofn i chi rannu'r daflen waith neu ei argraffu.

Agorwch lyfr gwaith Excel 2010 newydd neu sy'n bodoli eisoes a chliciwch ar y gell lle rydych chi am weld cornel uchaf chwith y Tabl Pivot.
Cliciwch ar y tab Insert a chliciwch ar y gollyngiad PivotTable yn yr adran Tablau. Dewiswch PivotTable. Bydd hyn yn lansio ffurflen deialog Creu PivotTable.

03 o 15

Cyswllt Tabl Pivot i SQL Server (neu Gronfa Ddata Eraill)

Creu eich ymholiad SQL ac yna cysylltu â SQL Server i fewnosod y llinyn data cysylltiad i mewn i'r daenlen Excel.

Gall Excel 2010 adfer data o'r holl ddarparwyr RDBMS (System Rheoli Cronfa Ddata Relational) mawr . Dylai gyrwyr SQL Server fod ar gael ar gyfer y cysylltiad yn ddiofyn. Ond mae pob meddalwedd cronfa ddata mawr yn gwneud gyrwyr ODBC (Agored Database Connectivity) i ganiatáu i chi wneud y cysylltiad. Edrychwch ar eu gwefan os oes angen i chi lawrlwytho gyrwyr ODBC.

Yn achos y tiwtorial hwn, yr wyf yn cysylltu â SQL Server 2008 R2 (fersiwn rhad ac am ddim SQL Express).

A - Y ffurflen Creu PivotTable yw'r ffurflen gyntaf wrth greu'r cysylltiad â SQL Server. Dewiswch "Defnyddio ffynhonnell ddata allanol" a chliciwch ar y botwm Dewis Cysylltiad. Gadewch leoliad lle bydd y Tabl Pivot yn cael ei roi oni bai eich bod am greu taflen waith newydd a'i roi yno.
B - Mae'r ffurflen Cysylltiadau Presennol yn rhestru unrhyw gysylltiadau yn y llyfr gwaith cyfredol, ar eich cyfrifiadur a'r rhwydwaith yr ydych yn gysylltiedig â hi ar hyn o bryd. Mewn gwirionedd, dim ond ffeiliau testun sydd â'r cysylltiadau presennol sydd â'r wybodaeth gysylltiad sydd ei hangen i gael mynediad at ffynhonnell ddata benodol. Yn ein hachos ni, byddwn yn creu ffynhonnell ddata newydd. Cliciwch ar y botwm Por Por Mwy.
C - Bydd Cliciwch ar y botwm Ffynhonnell Newydd yn lansio'r Dewin Cysylltu Data.
D - Dewiswch Microsoft SQL Server a chliciwch Next.
E - Rhowch enw'r Gweinyddwr a chofnodwch nodiadau credyd. Dewiswch y dull dilysu priodol. Os nad ydych yn siŵr pa ddull i'w ddefnyddio, cysylltwch â'ch gweinyddwr cronfa ddata.
- Defnyddio Dilysu Ffenestri: Mae'r dull hwn yn defnyddio'ch mewngofnodi rhwydwaith i gael mynediad at gronfeydd data SQL Server.
- Defnyddiwch y Defnyddiwr a Chyfrinair canlynol: Defnyddir y dull hwn pan fydd y Gweinyddwr SQL wedi'i ffurfweddu gyda defnyddwyr annibynnol i gael mynediad at gronfeydd data.
F - Yn y cam hwn, byddwn yn dewis bwrdd fel llechenydd lle. Byddwn yn ailosod y bwrdd gyda SQL arferol a fydd yn darparu'r union ddata yr ydym ei eisiau yn ein llyfr gwaith Excel.
- Dewiswch y gronfa ddata y byddwch yn cysylltu â hi. Yn yr enghraifft hon, yr ydym yn cysylltu â gronfa ddata sampl AdventureWorks a ddarperir gan Microsoft. Edrychwch ar y Cyswllt i dabl penodol a dewiswch y tabl cyntaf. Cofiwch, nid ydym am adfer data o'r tabl hwn.
- Cliciwch Gorffen a fydd yn cau'r dewin ac yn dychwelyd chi i'r llyfr gwaith. Byddwn yn cyfnewid y tabl llefydd ar gyfer ein hymholiad SQL arferol.

Fe'ch dychwelir at y ffurflen Creu PivotTable (A). Cliciwch OK.

04 o 15

Tabl Pivot Wedi'i Chysylltu â Thabla SQL dros dro

Mae PivotTable wedi'i gysylltu â SQL Server gyda'r bwrdd llefydd.

Ar y pwynt hwn, rydych chi wedi cysylltu â'r bwrdd lleoliadau ac mae gennych chi PivotTable gwag. Gallwch weld ar y chwith y bydd y PivotTable ac ar y dde mae rhestr o'r meysydd sydd ar gael.

05 o 15

Eiddo Cysylltiad Agored

Ffurflen Eiddo Cysylltiad Agored.

Cyn i ni ddechrau dewis data ar gyfer y PivotTable, mae angen i ni newid y cysylltiad â'r ymholiad SQL. Gwnewch yn siŵr eich bod ar y tab Opsiynau a chliciwch ar Newid Ffynhonnell Data i ollwng o'r adran Data. Dewiswch Eiddo Cysylltiad.

Daw hyn i fyny'r ffurflen Eiddo Cysylltiad. Cliciwch ar y tab Diffiniad. Mae hyn yn dangos y wybodaeth gyswllt i chi ar gyfer y cysylltiad cyfredol â SQL Server. Er ei fod yn cyfeirio ffeil gysylltiad, mae'r data wedi'i fewnosod yn y daenlen.

06 o 15

Diweddaru Eiddo Cysylltiad Gyda Gofyn

Newid tabl i ymholiad SQL.

Newid y Math Reoli o Dabl i SQL a gorysgrifennwch y Testun Rheoli presennol gyda'ch Holiadur SQL. Dyma'r ymholiad a grëais o gronfa ddata sampl 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,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
O OFFER Sales.SalesOrderDetail INNER YMWNEUD Sales.SalesOrderHeader AR
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER CYSYLLTU Production.Product AR Sales.SalesOrderDetail.ProductID =
INNERNERNU Production.Product.ProductID YMUNNU Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER YMWNEUD
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Gwerthwr
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Cliciwch OK.

07 o 15

Derbyn Rhybudd Cysylltiad

Cliciwch ar Ydw i Rhybudd Cysylltiad.

Byddwch yn derbyn blwch deialu Rhybudd Microsoft Excel. Mae hyn oherwydd ein bod ni wedi newid y wybodaeth cysylltiad. Pan wnaethom greu y cysylltiad yn wreiddiol, achubodd y wybodaeth mewn ffeil .ODC allanol (Cysylltiad Data ODBC). Roedd y data yn y llyfr gwaith yr un fath â'r ffeil .ODC nes i ni newid o fath gorchymyn tabl i fath gorchymyn SQL yn Cam # 6. Mae'r rhybudd yn dweud wrthych nad yw'r data bellach yn cydamseru a bydd y cyfeiriad at y ffeil allanol yn y llyfr gwaith yn cael ei ddileu. Mae hyn yn iawn. Cliciwch Ydw.

08 o 15

Tabl Pivot Cysylltiedig â Gweinyddwr SQL Gyda Gofyn

Mae PivotTable yn barod i chi ychwanegu data.

Mae hyn yn mynd yn ôl i lyfr gwaith Excel 2010 gyda PivotTable gwag. Gallwch weld bod y meysydd sydd ar gael bellach yn wahanol ac yn cyfateb i'r meysydd yn yr ymholiad SQL. Gallwn nawr ddechrau ychwanegu caeau i'r PivotTable.

09 o 15

Ychwanegu Caeau i Fwrdd Pivot

Ychwanegwch feysydd i PivotTable.

Yn y Rhestr Maes PivotTable, llusgo ProductCategory i ardal Labeli Row, Gorchymyn Archebu i ledaenau Colofn ac ardal TotalDue to Values. Mae'r ddelwedd yn dangos y canlyniadau. Fel y gwelwch, mae gan y maes dyddiad ddyddiadau unigol felly mae'r PivotTable wedi creu colofn ar gyfer pob dyddiad unigryw. Yn ffodus, mae gan Excel 2010 rai swyddogaethau adeiledig i'n helpu ni i drefnu meysydd dydd.

10 o 15

Ychwanegwch Grwpio ar gyfer Caeau Dyddiad

Ychwanegwch Grwpiau ar gyfer maes dyddiad.

Mae'r swyddogaeth Grwpio yn ein galluogi i drefnu dyddiadau i flynyddoedd, misoedd, chwarteri, ac ati. Bydd hyn yn helpu i grynhoi'r data a'i gwneud yn haws i'r defnyddiwr ryngweithio ag ef. Cliciwch ar y dde ar un o'r penawdau colofn dyddiad a dewiswch Grwp sy'n dod â'r ffurflen Grwpio i fyny.

11 o 15

Dewiswch Grwpio Yn ôl Gwerthoedd

Dosbarthwch eitemau grwp ar gyfer y maes dyddiad.

Gan ddibynnu ar y math o ddata rydych chi'n ei grwpio, bydd y ffurflen yn edrych ychydig yn wahanol. Mae Excel 2010 yn caniatáu i chi ddyddiadau grŵp, rhifau a data testun dethol. Rydyn ni'n grwpio OrderDate yn y tiwtorial hwn felly bydd y ffurflen yn dangos opsiynau sy'n ymwneud â grwpiau dydd.

Cliciwch ar Misoedd a Blynyddoedd a chliciwch OK.

12 o 15

Tabl Pivot wedi'i Grwpio gan Flynyddoedd a Misoedd

Mae'r caeau dyddiad yn cael eu grwpio gan flynyddoedd a misoedd.

Fel y gwelwch yn y ddelwedd uchod, mae'r data wedi'i grwpio erbyn y flwyddyn gyntaf ac yna fesul mis. Mae gan bob un arwydd ychwanegol a minws sy'n eich galluogi i ehangu a chwympo yn dibynnu ar sut rydych chi'n dymuno gweld y data.

Ar y pwynt hwn, mae'r PivotTable yn eithaf defnyddiol. Gellir hidlo pob un o'r caeau ond y broblem yw nad oes syniad gweledol o ran cyflwr presennol y hidlwyr. Hefyd, mae'n cymryd sawl clic i newid y farn.

13 o 15

Mewnosod Slicer (Newydd yn Excel 2010)

Ychwanegu Slicers i PivotTable.

Mae slicers yn newydd yn Excel 2010. Yn y bôn, mae'r sleidiau yn cyfateb i hidlyddion gosod gweledol y meysydd presennol a chreu Hidlau Adrodd yn yr achos nad yw'r eitem yr ydych am ei hidlo arno yn y golwg PivotTable gyfredol. Mae'r peth neis hwn am Slicers yn dod yn hawdd iawn i'r defnyddiwr newid golwg y data yn y PivotTable yn ogystal â darparu dangosyddion gweledol o ran cyflwr presennol y hidlwyr.

I fewnosod Slicers, cliciwch ar y tab Opsiynau a chliciwch ar Insert Slicer o'r adran Didoli a Hidlo. Dewiswch Mewnosod Slicer sy'n agor y ffurflen Insert Slicers. Gwiriwch gymaint o'r meysydd ag yr ydych am fod ar gael. Yn ein hesiampl, ychwanegais Flynyddoedd, CountryRegionName a ProductCategory. efallai y bydd yn rhaid i chi osod y Slicers lle rydych chi eisiau iddynt. Yn ddiofyn, dewisir pob un o'r gwerthoedd sy'n golygu nad oes hidlwyr wedi'u cymhwyso.

14 o 15

Tabl Pivot Gyda Sliperi Cyfeillgar i'r Defnyddiwr

Mae sleisyddion yn ei gwneud hi'n haws i ddefnyddwyr hidlo PivotTables.

Fel y gwelwch, mae'r Slicers yn dangos yr holl ddata a ddewiswyd. Mae'n glir iawn i'r defnyddiwr yn union pa ddata sydd yng ngwelediad cyfredol y PivotTable.

15 o 15

Dewiswch Gwerthoedd O Slicers Pa Fwrdd Pivot Diweddariadau

Dewiswch gyfuniadau o Slicers i newid golwg ar ddata.

Cliciwch ar wahanol gyfuniadau o werthoedd a gweld sut mae barn y PivotTable yn newid. Gallwch chi ddefnyddio Microsoft nodweddiadol yn clicio yn y Slicers sy'n golygu, os gallwch chi ddefnyddio Rheolaeth + Cliciwch i ddewis lluosog o werthoedd neu Shift + Cliciwch i ddewis ystod o werthoedd. Mae pob Slicer yn dangos y gwerthoedd a ddewiswyd sy'n ei gwneud hi'n amlwg beth yw cyflwr y PivotTable o ran hidlwyr. Gallwch newid arddulliau'r Slicers os ydych chi am glicio ar y 'Styles Quick' yn syrthio i lawr yn adran Slicer y tab Opsiynau.

Mae cyflwyno Slicers wedi gwella defnyddioldeb PivotTables mewn gwirionedd ac wedi symud Excel 2010 yn llawer agosach at fod yn offeryn busnes busnes proffesiynol. Mae PivotTables wedi gwella cryn dipyn yn Excel 2010 a phan gyfunir hyn gyda'r PowerPivot newydd yn creu amgylchedd dadansoddol perfformiad uchel iawn.