Yuav ua li cas Configure Excel 2010 Pivot Ntxhuav

by Tim Perdue

01 ntawm 15

Cov txiaj ntsim kawg

Qhov no yog qhov kawg ntawm cov kauj ruam ntawm kauj ruam ntawm kauj ruam - Nyem ntawm daim duab los saib ib daim ntawv qhia txog kev teev meej.

Muaj ib qho kev sib txawv ntawm Microsoft Excel thiab sab saum toj kev lag luam txawj ntse (BI) platforms tau ntau xyoo. Lub Microsoft Excel 2010 Pivot Table enhancements nrog ob peb lwm BI nta tau ua nws ib tug tiag tiag competitor rau Enterprise BI. Excel muaj kab lig kev cai tau siv rau kev pom kev sib sim ntawm tus kheej thiab cov cuab yeej txheem uas sawv daws xa lawv cov ntaub ntawv xa tuaj rau hauv. Kev txawj ua lag luam ntawm kev lag luam muaj kev ncaj ncees rau cov nyiam SAS, Lag luam Khoom thiab SAP.

Microsoft Excel 2010 (nrog Excel Excel Pivot Table) nrog rau SQL neeg rau zaub mov 2008 R2, SharePoint 2010 thiab dawb Microsoft Excel 2010 ntxiv rau "PowerPivot" tau ua rau kev lag luam siab ua lag luam thiab kev daws teeb meem.

No nyeem npog ncaj nraim scenario nrog ib qho Excel 2010 PivotTable txuas nrog SQL Server 2008 R2 database siv cov lus nug yooj yim SQL. Kuv kuj siv Slicers rau kev pom kev lim ntshav uas yog tshiab hauv Excel 2010. Kuv yuav qhia ntau txoj hauv kev BI uas yog siv Cov Ntawv Qhia Txog Cov Ntsiab Lus (DAX) hauv PowerPivot rau Excel 2010 yav tom ntej. Qhov tseeb tso tawm ntawm Microsoft Excel 2010 yuav muab ib qho txiaj ntsig rau koj cov neeg siv hauv zej zog.

02 ntawm 15

Insert Pivot Table

Tso koj tus cursor raws nraim qhov koj xav kom koj cov lus pivot thiab nias ntawm Txais | Pivot Table.

Koj tuaj yeem ntxig ib lub Pivot Table nyob rau hauv phau ntawv sau tshiab los yog muaj Excel. Koj yuav tsum xav txog qhov muab koj tus cursor tso rau ntawm ob peb kab los rau saum. Qhov no yuav muab koj qhov chaw rau cov lus qhia los yog cov tuam txhab lag luam yog tias koj qhia cov ntawv sau los yog sau tawm.

Qhib cov ntawv tshiab los yog ua hauj lwm hauv Excel 2010 thiab nias ntawm lub xov tooj ntawm tes uas koj xav kom sab laug sab saum toj ntawm Pivot Cov Ntaub Ntawv los ua.
Nias ntawm Insert tab thiab nias rau ntawm PivotTable nco cia hauv seem ntu. Xaiv PivotTable. Qhov no yuav pib tsim Tsab Ntawv PivotTable dialog.

03 ntawm 15

Txuas Pivot cov lus rau SQL neeg rau zaub mov (lossis lwm cov ntaub ntawv Database)

Ua kom koj cov lus nug SQL thiab txuas rau SQL neeg rau zaub mov kom embed txoj kev txuas cov ntaub ntawv txuas mus rau Excel spreadsheet.

Excel 2010 tau muab cov ntaub ntawv los ntawm tag nrho cov loj RDBMS (Kev Paub Txog Database Management System) . SQL neeg tsav tsheb yuav tsum muaj rau kev sib txuas ua ke. Tab sis tag nrho cov loj database software ua ODBC (Qhib Database Connectivity) tsav tsheb cia koj ua qhov kev twb kev txuas. Saib lawv lub vev xaib yog tias koj xav tau download tau ODBC tsav tsheb.

Nyob rau hauv cov ntaub ntawv ntawm no nyeem, kuv txuas mus rau SQL neeg rau zaub mov 2008 R2 (SQL Express dawb version).

IB - Daim PivotTable daim ntawv yog thawj daim ntawv tsim cov kev twb kev txuas mus rau SQL Server. Xaiv "Siv lwm cov ntaub ntawv" thiab nias rau ntawm Xaiv Qhov Xaiv. Tawm ntawm qhov chaw nyob ntawm qhov chaw Pivot yuav muab tso rau hauv tshwj tsis yog tias koj xav tsim ib daim ntawv tshiab thiab muab tso rau ntawd.
B - Daim Ntawv Teev Kev Sib Txuas Lus Taw Qhia muaj cov kev sib txuas hauv phau ntawv ua haujlwm tam sim no, hauv koj lub computer thiab lub network koj tab tom txuas nrog. Cov kev sib txuas muaj tiag tiag yog cov ntaub ntawv tsuas yog cov ntaub ntawv nrog rau cov ncauj lus sib txuas sib txuas ntxiv uas yuav tsum tau nkag mus rau cov ntaub ntawv muaj kev. Nyob rau hauv peb cov ntaub ntawv, peb tab tom yuav tsim ib cov ntaub ntawv tshiab. Nias rau ntawm Xauj rau Ntau lub pob.
C - Nias ntawm qhov New Source button yuav tso tawm cov Data Connection Wizard.
D - Xaiv Microsoft SQL Server thiab nias Next.
E - Nkag mus rau Lub Xib Fwb lub npe thiab nkag rau ntawm daim ntawv pov thawj. Xaiv tus qauv kev tsim nyog. Yog tias koj tsis paub tseeb tias txoj hauv kev siv, hu rau koj tus neeg tuav ntaub ntawv database.
- Siv Qhov Cuab Authentication Windows: Hom no siv koj tus naj npawb network nkag mus rau SQL Server databases.
- Siv cov npe hauv qab no thiab tus Password: Cov qauv no yog siv thaum tus neeg tau txais SQL neeg tau teeb tsa nrog cov neeg siv tib si mus saib databases.
F - Hauv kauj ruam no, peb yuav xaiv ib lub rooj ua ib qho chaw nyob. Peb yuav tsum hloov lub rooj nrog kev cai SQL uas yuav muab cov ntaub ntawv peb xav tau nyob rau hauv peb phau ntawv Excel.
- Xaiv lub database uas koj yuav tsum txuas rau. Hauv qhov ua piv txwv, peb txuas mus rau AdventureWorks qauv database los ntawm Microsoft. Kos rau Txuas mus rau ib lub rooj thiab xaiv thawj lub rooj. Nco ntsoov tias, peb tsis tuaj yeem xa cov ntaub ntawv los ntawm lub rooj ntawv no.
- Nyem Finish uas yuav kaw cov kwvtij thiab xa rov qab tuaj rau hauv phau ntawv ua haujlwm. Peb yuav tsum swapping tawm ntawm qhov chaw tableholder rau peb cov lus nug SQL.

Koj yuav raug xa rov qab mus rau Tsim daim ntawv PivotTable (A). Nyem OK.

04 ntawm 15

Pivot Table Temporarily Connected to SQL Table

PivotTable txuas nrog SQL neeg rau zaub mov nrog qhov chaw placeholder.

Txij ntawm no tus taw tes, koj tau txuas nrog qhov chaw placeholder thiab koj muaj tus PivotTable npliag. Koj tuaj yeem pom ntawm sab laug yog PivotTable yuav thiab nyob ntawm sab xis yog cov npe ntawm cov chaw.

05 ntawm 15

Qhib Cov Khoom Sib Mus

Qhib Cov Khoom Sib Tug Sib Qhib.

Ua ntej peb pib xaiv cov ntaub ntawv rau PivotTable, peb yuav tsum tau hloov qhov kev txuas mus rau cov lus nug SQL. Nco ntsoov tias koj nyob rau ntawm Qhov Kev Xaiv thiab nias rau Kev Hloov Cov Ntaub Ntawv Tshaj Tawm los ntawm Tshooj Cov Ntaub Ntawv. Xaiv qhov chaw sib txuas.

Qhov no coj tau cov Khoom Siv Kev Sib Txuas Zoo. Nias rau ntawm lub Ntsiab Lus. Qhov no qhia koj cov ntaub ntawv txuas rau kev sib txuas mus rau SQL Server. Thaum nws references ib cov ntaub ntawv txuas, cov ntaub ntawv yog tiag embedded nyob rau hauv lub spreadsheet.

06 ntawm 15

Hloov Cov Khoom Sib Txuas Nrog Lus Nug

Hloov cov lus rau SQL cov lus nug.

Hloov cov yam hais kom ua los ntawm cov lus rau SQL thiab overwrite tus uas twb muaj lawm cov ntawv sau nrog koj cov lus nug SQL. Ntawm no yog cov lus nug kuv tsim los ntawm AdventureWorks qauv database:

Xaiv Muag.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,
Ntau lawm.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Ntau lawm.Product.ListPrice,
Production.Product.ProductLine,
Ntau lawm.ProductSubcategory.Name AS ProductCategory
LOS NTAWM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Ntau lawm.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID THIAB
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID TSWV YIM
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID NYOB RAU HAUV
Ntau lawm.ProductSubcategory RAU ntau lawm.Product.ProductSubcategoryID =
Ntau lawm.ProductSubcategory.ProductSubcategoryID

Nyem OK.

07 ntawm 15

Tau Txais Kev Ceeb Toom

Nias rau ntawm Yes to Warning Connection.

Koj yuav tau txais lub Microsoft Excel Ceeb Toom dialog box. Qhov no yog vim peb hloov cov ntaub ntawv txuas. Thaum peb pib tsim qhov kev twb kev txuas, nws tau txais kev cawmdim cov ntaub ntawv nyob rau hauv ib daim ntawv sab nraud .ODC (ODBC Data Connection). Cov ntaub ntawv hauv phau ntawv siv tau tib yam li cov ntaub ntawv .ODC mus txog thaum peb hloov ntawm lub rooj hais ua lus hais rau SQL hais kom ua hom hauv Kauj ruam # 6. Tsab ntawv ceeb toom no qhia koj tias cov ntaub ntawv tsis nyob hauv kev sib txuas thiab qhov kev siv rau lwm cov ntaub ntawv hauv phau ntawv yuav raug muab tshem tawm. Qhov no yog OK. Nyem Yog.

08 ntawm 15

Pivot Table txuas nrog SQL neeg rau zaub mov nrog Query

PivotTable yog npaj rau koj kom ntxiv cov ntaub ntawv.

Qhov no yuav siv sij hawm rov qab mus rau Excel 2010 phau ntawv nrog tus PivotTable npliag. Koj tuaj yeem pom tias cov chaw ua haujlwm tam sim no sib txawv thiab sib haum rau cov teb nyob rau hauv cov lus nug SQL. Peb tuaj yeem pib muab cov lus teb rau PivotTable.

09 ntawm 15

Ntxiv Liaj Teb rau Pivot Table

Ntxiv cov teb rau PivotTable.

Hauv PivotTable Field List, nyem ProductCategory mus rau cheeb tsam Cov Ntawv Pov Tseg, OrderDate rau Kem Sau Ntawv thiab TotalDue rau Qhov Chaw Tshawb Fawb. Daim duab qhia cov ntsiab lus. Raws li koj tuaj yeem pom, hnub sau teb muaj ib tus neeg hnub ntawd ces PivotTable tau tsim ib kem rau txhua hnub tshwj xeeb. Zoo hmoo, Excel 2010 muaj qee tus txhim tsa ua haujlwm los pab peb npaj cov hnub qub.

10 ntawm 15

Ntxiv Grouping rau Hnub Nres

Ntxiv Groupings rau hnub kos npe.

Pawg Grouping muaj kev tso cai rau peb npaj cov hnub nyoog hauv lub xyoo, hli, quarters, etc. Qhov no yuav pab xaus rau cov ntaub ntawv thiab ua kom yooj yim rau tus neeg siv los nrog nws. Txoj cai nias ntawm ib qho ntawm cov kab ntawv sab nraud hnub vas thib thiab xaiv Pawg uas coj tau cov Pab Pawg.

11 ntawm 15

Xaiv Grouping By Values

Xaiv cov khoom rau hnub tim.

Nyob ntawm seb cov ntaub ntawv koj tau sib sau ua ke, daim ntawv yuav pom txawv me ntsis. Excel 2010 tso cai rau koj mus koom hnub, cov zauv thiab xaiv cov ntaub ntawv. Peb tau ua raws li OrderDate hauv phau ntawv nyeem li no ces daim ntawv yuav qhia cov kev xaiv uas muaj hnub nyoog.

Nias ntawm lub Hlis thiab Ntau Xyoos thiab nias OK.

12 ntawm 15

Pivot Table Grouped los ntawm Xyoo thiab Hlis

Cov hnub suav yog pawg los ntawm ntau xyoo thiab lub hli.

Raws li koj pom hauv daim duab saum toj no, cov ntaub ntawv yog pawg ua ntej lub xyoo ua ntej thiab tom qab lub hli. Txhua tus muaj ib qho ntxiv thiab rho tawm kos npe uas tso cai rau koj nthuav thiab vau nyob ntawm seb koj xav pom cov ntaub ntawv.

Txij ntawm no mus, PivotTable yog qhov zoo nkauj heev. Txhua lub teb yuav lim, tiam sis qhov teeb meem yog tsis muaj kev pom tseeb raws li lub xeev tam sim no ntawm cov ntxaij lim dej. Ntxiv thiab, nws yuav siv ob peb nias los hloov qhov kev pom.

13 ntawm 15

Ntxig Slicer (Tshiab hauv Excel 2010)

Ntxiv Slicers rau PivotTable.

Slicers tshiab nyob rau hauv Excel 2010. Slicers yeej yog qhov sib npaug ntawm cov visually teem cov ntxaij lim dej ntawm cov chaw ua haujlwm thiab tsim Tsab Ntawv Filters nyob rau hauv rooj plaub tias qhov khoom koj xav lim rau tsis nyob hauv PivotTable saib tam sim no. Qhov zoo tshaj plaws txog Slicers no nws yog ib qho yooj yim heev rau tus neeg siv los hloov qhov pom ntawm cov ntaub ntawv hauv PivotTable nrog rau kev muab cov yeeb yam xws li lub xeev tam sim no ntawm cov ntxaij lim dej.

Mus ntxig Slicers, nyem rau ntawm Qhov Xaiv thiab nias ntawm Ntxig Slicer los ntawm Tsi thiab Lim nqe. Xaiv Qhov Ntxig Txuas Lus uas qhib qhov ntxig Slicers daim ntawv. Kos ntau lub teb raws li koj xav kom muaj. Hauv peb tus piv txwv, kuv ntxiv cov Xyoo, CountryRegionName thiab ProductCategory. koj yuav tsum tau tso cov Slicers uas koj xav tau. Yog vim li ntawd, tag nrho cov kev cai yog xaiv uas txhais tau tias tsis muaj cov ntxaij lim dej tau siv.

14 ntawm 15

Pivot cov lus nrog cov neeg siv cov phooj ywg Slicers

Slicers ua kom yooj yim rau cov neeg siv los lim PivotTables.

Raws li koj tau pom, Cov Slicers qhia tag nrho cov ntaub ntawv raws li tau xaiv. Nws yog tseeb heev rau tus neeg siv raws nraim li cas cov ntaub ntawv nyob rau hauv qhov tam sim no pom ntawm PivotTable.

15 ntawm 15

Xaiv Qhov Yam Ntxim Saib Ntxim Saib Ntxim Saib Ntxim Saib Ntxim Ua Uas Xaiv Cov Pivot Table

Kev xaiv ob peb zaug ntawm Slicers los hloov cov ntaub ntawv.

Nias rau ntau yam kev sib txuas ntawm qhov tseem ceeb thiab pom qhov pom qhov PivotTable hloov li cas. Koj siv tau Microsoft lub npe nyob rau hauv Slicers lub ntsiab lus uas yog tias koj tuaj yeem siv Kev Tswj + Xaiv los xaiv ntau qhov tseem ceeb los ua haujlwm + Nias mus xaiv ib thaj tsam ntawm qhov tseem ceeb. Txhua Slicer qhia qhov xaiv qhov tseem ceeb uas ua rau nws pom tseeb li cas hauv lub xeev ntawm PivotTable nyob hauv cov ntsiab lus ntawm cov ntxaij lim dej. Koj tuaj yeem hloov qhov kev xaiv ntawm cov Slicers yog tias koj xav tau los ntawm kev nias rau ntawm Quick Styles poob rau hauv seem Slicer ntawm Cov Xaiv.

Cov kev qhia txog Slicers tau zoo tuaj yeem tsim kho tau cov PivotTables thiab tau tsiv mus rau Excel 2010 kom ze rau kev ua lag luam kev txawj ntse cuab tam. PivotTables tau nce kuj nce mentsis rau hauv Excel 2010 thiab thaum siv nrog PowerPivot tshiab tsim ib qhov chaw kawm siab heev analytic ib puag ncig.