Konfigurer Excel 2010 Pivot Tables

Der har været en kløft mellem Microsoft Excel og de øverste tier business intelligence (BI) platforme i mange år. Microsoft Excel 2010 Pivot Table forbedringer sammen med et par andre BI funktioner har gjort det til en reel konkurrent for enterprise BI. Excel er traditionelt blevet brugt til selvstændig analyse og standardværktøjet, som alle eksporterer deres endelige rapporter til. Professionel business intelligence har traditionelt været forbeholdt SAS, Business Objects og SAP.

01 af 15

Endeligt resultat

Microsoft Excel 2010 (med Excel 2010 Pivot Table) sammen med SQL Server 2008 R2, SharePoint 2010 og den gratis Microsoft Excel 2010 add-on "PowerPivot" har resulteret i en avanceret business intelligence og rapporteringsløsning.

Denne vejledning dækker et simpelt scenario med en Excel 2010 PivotTable, der er forbundet til en SQL Server 2008 R2-database ved hjælp af en simpel SQL-forespørgsel.

Fortsæt læsning herunder

02 af 15

Indsæt pivottabel

Du kan indsætte en pivottabel i en ny eller eksisterende Excel-projektmappe. Du vil måske overveje at placere markøren ned et par rækker fra toppen. Dette giver dig plads til en overskrift eller virksomhedsinformation, hvis du deler regnearket eller udskriver det.

• Åbn en ny eller eksisterende Excel 2010-projektmappe, og klik på cellen, hvor du vil have det øverste venstre hjørne af pivottabellen.
• Klik på fanen Indsæt og klik på pivottabellen ned i tabellen. Vælg PivotTable. Dette vil starte dialogboksen Create PivotTable.

Fortsæt læsning herunder

03 af 15

Forbind pivottabellen til SQL Server

Excel 2010 kan hente data fra alle større RDBMS (Relational Database Management System) udbydere. SQL Server-drivere skal som standard være tilgængelige for forbindelsen. Tjek deres hjemmeside, hvis du har brug for at downloade ODBC drivere.

I forbindelse med denne vejledning forbinder vi os med SQL Server 2008 R2 (SQL Express gratis version).

1. Åbn Create PivotTable formularen. Vælg "Brug en ekstern datakilde", og klik på knappen Vælg forbindelse. Lad placeringen af ​​hvor pivottabellen placeres.
2. Åbn formularen Eksisterende forbindelser. Klik på knappen Gennemse for mere.
3. Klik på knappen Ny kilde vil starte guiden Dataforbindelse.
4. Vælg Microsoft SQL Server og klik på Næste.
5. Indtast servernavn og loginoplysninger. Vælg den relevante godkendelsesmetode:
   1. Brug Windows-godkendelse: Denne metode bruger dit netværks login til at få adgang til SQL Server-databaser.
   2. Brug følgende brugernavn og adgangskode: Denne metode bruges, når SQL Server er konfigureret med selvstændige brugere til at få adgang til databaser.
6. Udskift tabellen med brugerdefineret SQL, der vil give nøjagtigt de data, vi ønsker i vores Excel-projektmappe:
   1. Vælg den database, du vil oprette forbindelse til. I dette eksempel forbinder vi til AdventureWorks-prøvedatabasen fra Microsoft. Kontroller forbindelsen til en bestemt tabel og vælg den første tabel. Husk, vi kommer ikke til at hente data fra denne tabel.
   2. Klik på Afslut, som lukker guiden og returnerer dig til projektmappen. Vi udskifter pladsholdertabellen til vores tilpassede SQL-forespørgsel.

Du vil blive returneret til Create PivotTable formularen (A). Klik på OK.

04 af 15

Pivottabel midlertidigt forbundet til SQL-tabel

På dette tidspunkt har du tilsluttet pladsholderbordet, og du har en tom pivottabel. Du kan se til venstre, hvor PivotTable vil være, og til højre er der en liste over tilgængelige felter.

Fortsæt læsning herunder

05 af 15

Åbn forbindelsesegenskaber

Sørg for, at du er på fanebladet Indstillinger, og klik på Skift datakilde drop down fra dataafsnittet. Vælg forbindelsesegenskaber.

Dette medfører formularen Connection Properties. Klik på fanen Definition. Dette viser dig forbindelsesoplysningerne for den aktuelle forbindelse til SQL Server. Mens det refererer til en forbindelsesfil, er dataene faktisk indlejret i regnearket.

06 af 15

Opdater forbindelsesegenskaber med forespørgsel

Skift kommandotype fra tabel til SQL og overskriv den eksisterende kommanditekst med din SQL-forespørgsel. Her er spørgsmålet, vi oprettede fra AdventureWorks eksempeldatabase:

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 ProductCategoryFRA Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID OGSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

Klik på OK.

Fortsæt læsning herunder

07 af 15

Modtag advarsel om tilslutning

Du vil modtage en Microsoft Excel Advarsel dialogboks. Dette skyldes, at vi ændrede forbindelsesoplysningerne. Da vi oprindeligt oprettede forbindelsen, gemte den informationen i en ekstern .ODC-fil (ODBC Data Connection). Dataene i projektmappen var de samme som .ODC-filen, indtil vi skiftede fra en kommandotype til SQL-kommandotype i trin # 6. Advarslen fortæller dig, at dataene ikke længere synkroniseres, og henvisningen til den eksterne fil i projektmappen fjernes. Det er i orden. Klik på Ja.

08 af 15

Pivottabel forbundet til SQL Server med forespørgsel

Dette tager tilbage til Excel 2010-projektmappen med en tom pivottabel. Du kan se, at de tilgængelige felter nu er forskellige og svarer til felterne i SQL-forespørgslen. Vi kan nu begynde at tilføje felter til pivottabellen.

Fortsæt læsning herunder

09 af 15

Tilføj felter til pivottabellen

I feltet PivotTable Field skal du trække produktkategori til rækkefelterområde, rækkefølgeordre til kolonnefelter og totaldue til værdier. Som du kan se, har datofeltet individuelle datoer, så PivotTable har oprettet en kolonne for hver unik dato. Excel 2010 har nogle indbyggede funktioner til at hjælpe os med at organisere datafelter.

10 af 15

Tilføj gruppering for datafelter

Grupperingsfunktionen giver os mulighed for at organisere datoer i år, måneder, kvartaler osv. Dette vil hjælpe med at opsummere dataene og gøre det lettere for brugeren at interagere med det. Højreklik på en kolonneoverskrift, og vælg Gruppe, der åbner grupperingsformularen.

Fortsæt læsning herunder

11 af 15

Vælg gruppering efter værdier

Afhængigt af hvilken type data du grupperer, ser formularen lidt anderledes ud. Excel 2010 giver dig mulighed for at gruppere datoer, tal og valgte tekstdata. Vi grupperer OrderDate i denne vejledning, så formularen vil vise muligheder relateret til datagrupperinger.

Klik på Måneder og År, og klik på OK.

12 af 15

Pivottabel grupperet efter år og måneder

Dataene grupperes efter år først og derefter efter måned. Hver har et plus- og minustegn, som giver dig mulighed for at udvide og falde sammen, afhængigt af hvordan du ønsker at se dataene.

På dette tidspunkt er PivotTable ret nyttigt. Hver af felterne kan filtreres, men problemet er, at der ikke er en visuel indikation af filtreens aktuelle tilstand. Det tager flere klik at ændre visningen.

13 af 15

Indsæt slicer (nyt i Excel 2010)

Skærere er nye i Excel 2010. Skiver er stort set det samme som visuelt indstillede filtre af de eksisterende felter og oprettelse af rapportfiltre, hvis det element, du vil filtrere på, ikke er i den aktuelle PivotTable-visning. Denne flotte ting om Slicers er, at det bliver meget nemt for brugeren at ændre visningen af ​​dataene i pivottabellen samt at give visuelle indikatorer om filtreens aktuelle tilstand.

For at indsætte Slicers skal du klikke på fanen Indstillinger og klikke på Indsæt slicer i afsnittet Sorter og filtrer. Vælg Indsæt slicer, der åbner skærmen Indsæt slicer. Tjek så mange af felterne som du vil have adgang til.

14 af 15

Pivottabel med brugervenlige skiver

Som du kan se, viser skiverne alle data som valgt. Det er meget klart for brugeren, præcis hvilke data der er i den aktuelle visning af pivottabellen.

15 af 15

Vælg værdier fra slicers Hvilke opdateringer drejer tabel

Klik på forskellige kombinationer af værdier og se, hvordan visningen af ​​pivottabellen ændres. Du kan bruge typisk Microsoft-klik i Slicers, hvilket betyder, at hvis du kan bruge Control + Click til at vælge flere værdier eller Skift + Klik for at vælge en række værdier.

Hver Slicer viser de valgte værdier, hvilket gør det virkelig indlysende, hvad tilstanden af ​​PivotTable er i form af filtre. Du kan ændre stilen på skiverne, hvis du vil ved at klikke på rullelisten Hurtige stilarter i afsnittet Skærer på fanen Indstillinger.