Konfigurer Excel 2010 Pivot Tables

Det har vært et mellomrom mellom Microsoft Excel og de øverste tier business intelligence (BI) plattformene i mange år. Microsoft Excel 2010 Pivot Table forbedringer sammen med et par andre BI funksjoner har gjort det til en ekte konkurrent for enterprise BI. Excel har tradisjonelt blitt brukt til frittstående analyser og standardverktøyet som alle eksporterer sine endelige rapporter til. Profesjonell business intelligence har tradisjonelt blitt reservert for likeså SAS, Business Objects, og SAP.

01 av 15

Endelig resultat

Microsoft Excel 2010 (med Excel 2010 Pivot Table) sammen med SQL Server 2008 R2, SharePoint 2010 og den gratis Microsoft Excel 2010-tillegget "PowerPivot" har resultert i en avansert forretningsmessig intelligens og rapporteringsløsning.

Denne opplæringen dekker et rettferdig scenario med en Excel 2010 PivotTable koblet til en SQL Server 2008 R2-database ved hjelp av en enkel SQL-spørring.

Fortsett å lese nedenfor

02 av 15

Sett inn pivottabellen

Du kan sette inn et pivottabell i en ny eller eksisterende Excel-arbeidsbok. Du vil kanskje vurdere å plassere markøren ned noen få rader fra toppen. Dette gir deg plass til en header eller bedriftsinformasjon hvis du deler regnearket eller skriver det ut.

• Åpne en ny eller eksisterende Excel 2010 arbeidsbok og klikk på cellen der du vil at øverste venstre hjørne av pivottabellen skal være.
• Klikk på Sett inn-fanen og klikk på PivotTable-rullegardinmenyen i tabellen. Velg PivotTable. Dette vil starte dialogboksen Lag pivottabell.

Fortsett å lese nedenfor

03 av 15

Koble pivottabell til SQL Server

Excel 2010 kan hente data fra alle de store RDBMS-leverandørene (Relational Database Management System). SQL Server-drivere bør være tilgjengelig for tilkoblingen som standard. Sjekk nettsiden din hvis du trenger å laste ned ODBC-drivere.

I tilfelle av denne opplæringen, kobler vi oss til SQL Server 2008 R2 (SQL Express gratisversjon).

1. Åpne Create PivotTable-skjemaet. Velg "Bruk en ekstern datakilde" og klikk på knappen Velg tilkobling. La plasseringen av hvor pivottabellen plasseres.
2. Åpne skjemaet Eksisterende tilkoblinger. Klikk på Browse for More-knappen.
3. Klikk på knappen Ny kilde vil starte veiviseren for dataforbindelse.
4. Velg Microsoft SQL Server og klikk Neste.
5. Skriv inn servernavnet og påloggingsinformasjonen. Velg riktig godkjenningsmetode:
   1. Bruk Windows-godkjenning: Denne metoden bruker nettverksloggingen din for å få tilgang til SQL Server-databaser.
   2. Bruk følgende brukernavn og passord: Denne metoden brukes når SQL Server er konfigurert med frittstående brukere for å få tilgang til databaser.
6. Erstatt bordet med tilpasset SQL som vil gi nøyaktig de dataene vi vil ha i vår Excel-arbeidsbok:
   1. Velg databasen du vil koble til. I dette eksemplet kobler vi til AdventureWorks eksempeldatabase levert av Microsoft. Sjekk Koble til et bestemt bord og velg den første tabellen. Husk at vi ikke skal hente data fra denne tabellen.
   2. Klikk Fullfør, som lukker veiviseren og returnerer deg til arbeidsboken. Vi bytter ut plassholderbordet for vår tilpassede SQL-spørring.

Du vil bli returnert til Create PivotTable-skjemaet (A). Klikk på OK.

04 av 15

Pivottabell midlertidig koblet til SQL-tabell

På dette tidspunktet har du koblet til plassholderbordet og du har en tom pivottabell. Du kan se til venstre hvor PivotTable vil være, og til høyre er det en liste over tilgjengelige felt.

Fortsett å lese nedenfor

05 av 15

Åpne tilkoblingsegenskaper

Pass på at du er på fanen Alternativer, og klikk på Endre datakilde, slett ned fra data-delen. Velg Tilkoblingsegenskaper.

Dette bringer opp Connection Properties-skjemaet. Klikk på kategorien Definisjon. Dette viser koblingsinformasjonen for den nåværende tilkoblingen til SQL Server. Mens det refererer til en tilkoblingsfil, er dataene faktisk innebygd i regnearket.

06 av 15

Oppdater forbindelsesegenskaper med spørring

Endre kommandotype fra tabell til SQL og skriv over eksisterende kommanditekst med SQL-spørringen. Her er spørringen vi opprettet 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,Produksjon.Produkt.Navn,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

Klikk på OK.

Fortsett å lese nedenfor

07 av 15

Motta tilkoblingsadvarsel

Du får en dialogboksen for Microsoft Excel-advarsel. Dette skyldes at vi endret tilkoblingsinformasjonen. Da vi opprinnelig opprettet forbindelsen, lagret den informasjonen i en ekstern .ODC-fil (ODBC Data Connection). Dataene i arbeidsboken var de samme som .ODC-filen til vi endret fra en tabellkommandotype til SQL-kommandotype i trinn # 6. Advarselen forteller deg at dataene ikke lenger synkroniseres, og referansen til den eksterne filen i arbeidsboken fjernes. Dette er greit. Klikk Ja.

08 av 15

Pivottabell koblet til SQL Server med spørring

Dette tar tilbake til Excel 2010-arbeidsboken med en tom pivottabell. Du kan se at de tilgjengelige feltene nå er forskjellige og samsvarer med feltene i SQL-spørringen. Vi kan nå begynne å legge til felt i pivottabellen.

Fortsett å lese nedenfor

09 av 15

Legg til felt til pivottabell

I PivotTable-feltlisten drar du ProductCategory til Row Labels-området, OrderDate to Column Labels-området og TotalDue til Verdier-området. Som du kan se har datofeltet individuelle datoer, slik at pivottabellen har opprettet en kolonne for hver unik dato. Excel 2010 har noen innebygde funksjoner for å hjelpe oss med å organisere datafelt.

10 av 15

Legg til gruppering for datafelt

Grupperingsfunksjonen gir oss mulighet til å organisere datoer i år, måneder, kvartaler etc. Dette vil bidra til å oppsummere dataene og gjøre det lettere for brukeren å samhandle med den. Høyreklikk på en kolonneoverskrift for dato og velg Gruppe som bringer opp grupperingsskjemaet.

Fortsett å lese nedenfor

11 av 15

Velg gruppering etter verdier

Avhengig av hvilken type data du grupperer, vil skjemaet se litt annerledes ut. Excel 2010 lar deg gruppere datoer, tall og valgte tekstdata. Vi grupperer OrderDate i denne opplæringen, slik at skjemaet vil vise alternativer knyttet til datogrupperinger.

Klikk på Måneder og år, og klikk OK.

12 av 15

Pivottabell gruppert etter år og måneder

Dataene grupperes etter år først og deretter etter måned. Hver har et pluss- og minustegn som lar deg utvide og kollapse, avhengig av hvordan du ønsker å se dataene.

På dette punktet er PivotTable ganske nyttig. Hver av feltene kan filtreres, men problemet er at det ikke er noen visuell indikasjon på filtreens nåværende tilstand. Det tar flere klikk å endre visningen.

13 av 15

Sett inn slicer (Ny i Excel 2010)

Slicers er nye i Excel 2010. Slicers er i utgangspunktet ekvivalent med visuelt innstilte filtre av de eksisterende feltene og oppretter Report Filters i tilfelle at elementet du vil filtrere på, ikke er i den nåværende PivotTable-visningen. Denne fine tingen om Slicers er det blir veldig enkelt for brukeren å endre visningen av dataene i pivottabellen, samt å gi visuelle indikatorer om filtreens nåværende tilstand.

For å sette inn Slicers, klikk på Options-fanen og klikk på Sett inn slicer fra Sorter og filter-delen. Velg Sett inn slicer som åpner skjemaet Sett inn skiver. Sjekk så mange av feltene som du vil ha tilgjengelig.

14 av 15

Pivottabell med brukervennlige skiver

Som du ser, viser Slicers alle dataene som valgt. Det er veldig klart for brukeren nøyaktig hvilke data som er i gjeldende visning av pivottabellen.

15 av 15

Velg verdier fra Slicers Hvilke oppdateringer Pivot Table

Klikk på ulike kombinasjoner av verdier og se hvordan visningen av pivottabellen endres. Du kan bruke vanlig Microsoft-klikke i Slicers, noe som betyr at hvis du kan bruke Control + Click for å velge flere verdier eller Shift + Klikk for å velge en rekke verdier.

Hver Slicer viser de valgte verdiene som gjør det veldig tydelig hva tilstanden til PivotTable er når det gjelder filtre. Du kan endre stilene til skiverne hvis du vil ved å klikke på rullegardinmenyen nedover i Skarver-delen av kategorien Alternativer.