Konfigurera Excel 2010-pivottabeller

Det har funnits en lucka mellan Microsoft Excel och de bästa plattformarna för business intelligence (BI) i många år. Förbättringar av Microsoft Excel 2010-pivottabellen tillsammans med ett par andra BI-funktioner har gjort det till en riktig konkurrent för Enterprise BI. Excel har traditionellt använts för fristående analys och standardverktyget som alla exporterar sina slutrapporter till. Professionell affärsintelligens har traditionellt varit reserverad för SAS, Business Objects och SAP.

01 av 15

Slutresultat

Microsoft Excel 2010 (med Excel 2010-pivottabellen) tillsammans med SQL Server 2008 R2, SharePoint 2010 och gratis Microsoft Excel 2010-tillägg "PowerPivot" har resulterat i en avancerad affärsinformation och rapporteringslösning.

Denna handledning täcker ett enkelt scenario med en Excel 2010 PivotTable kopplad till en SQL Server 2008 R2-databas med en enkel SQL-fråga.

Fortsätt läsa nedan

02 av 15

Sätt in pivottabellen

Du kan infoga ett pivottabell i en ny eller existerande Excel-arbetsbok. Du kanske vill överväga att placera markören ner några rader från toppen. Detta ger dig plats för en rubrik eller företagsinformation om du delar arbetsbladet eller skriver ut det.

• Öppna en ny eller befintlig Excel 2010-arbetsbok och klicka på den cell där du vill att det övre vänstra hörnet av pivottabellen ska vara.
• Klicka på fliken Infoga och klicka på PivotTable-fönstret i tabellen. Välj PivotTable. Detta startar dialogrutan Skapa pivottabell.

Fortsätt läsa nedan

03 av 15

Anslut pivottabell till SQL Server

Excel 2010 kan hämta data från alla större RDBMS-leverantörer (Relational Database Management System). SQL Server-drivrutiner ska vara tillgängliga för anslutningen som standard. Kolla deras hemsida om du behöver ladda ner ODBC-drivrutiner.

När det gäller den här handledningen kopplas vi till SQL Server 2008 R2 (SQL Express fri version).

1. Öppna formuläret Skapa pivottabell. Välj "Använd en extern datakälla" och klicka på knappen Välj anslutning. Lämna platsen för var pivottabellen ska placeras.
2. Öppna formuläret Existerande anslutningar. Klicka på knappen Bläddra efter mer.
3. Klicka på knappen Ny källa startar guiden Dataanslutning.
4. Välj Microsoft SQL Server och klicka på Nästa.
5. Ange serverns namn och inloggningsuppgifter. Välj lämplig autentiseringsmetod:
   1. Använd Windows-autentisering: Den här metoden använder din nätverksloggning för att komma åt SQL Server-databaser.
   2. Använd följande användarnamn och lösenord: Denna metod används när SQL Server har konfigurerats med fristående användare för att komma åt databaser.
6. Byt ut tabellen med anpassad SQL som ger exakt de uppgifter vi vill ha i vår Excel-arbetsbok:
   1. Välj den databas du ska ansluta till. I det här exemplet kopplar vi oss till AdventureWorks-provdatabasen från Microsoft. Kontrollera anslutningen till en specifik tabell och välj den första tabellen. Kom ihåg att vi inte kommer att hämta data från den här tabellen.
   2. Klicka på Slutför som stänger guiden och returnerar dig till arbetsboken. Vi ska byta ut platshållartabellen för vår anpassade SQL-fråga.

Du kommer att returneras till formuläret Create PivotTable (A). Klicka på OK.

04 av 15

Pivottabell tillfälligt ansluten till SQL-tabell

Vid denna tidpunkt har du anslutit till platshållartabellen och du har en tom pivottabell. Du kan se till vänster där PivotTable kommer att vara, och till höger finns en lista över tillgängliga fält.

Fortsätt läsa nedan

05 av 15

Öppna anslutningsegenskaper

Se till att du är på fliken Alternativ och klicka på Ändra datakälla drop down från Data-sektionen. Välj anslutningsegenskaper.

Detta leder till Connection Properties-formuläret. Klicka på fliken Definition. Detta visar anslutningsinformationen för den aktuella anslutningen till SQL Server. Medan den hänvisar till en anslutningsfil, är data faktiskt inbäddad i kalkylbladet.

06 av 15

Uppdatera anslutningsegenskaper med fråga

Ändra kommandotypen från tabell till SQL och skriv över befintlig kommandotekst med din SQL-fråga. Här är frågan vi skapade från AdventureWorks provdatabas:

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 ProductCategoryFROM 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 OCHSales.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

Klicka på OK.

Fortsätt läsa nedan

07 av 15

Ta emot anslutningsvarning

Du kommer att få en dialogruta för Microsoft Excel-varning. Detta beror på att vi ändrade anslutningsinformationen. När vi ursprungligen skapade anslutningen, sparade den informationen i en extern .ODC-fil (ODBC Data Connection). Datan i arbetsboken var densamma som .ODC-filen tills vi ändrade från en tabellkommandotyp till SQL-kommandotyp i steg # 6. Varningen säger att data inte längre synkroniseras och referensen till den externa filen i arbetsboken tas bort. Det här är ok. Klicka på Ja.

08 av 15

Pivottabell ansluten till SQL Server med förfrågan

Detta tar tillbaka till Excel 2010-arbetsboken med en tom pivottabell. Du kan se att de tillgängliga fälten nu är olika och motsvarar fälten i SQL-frågan. Vi kan nu börja lägga till fält till pivottabellen.

Fortsätt läsa nedan

09 av 15

Lägg till fält till pivottabell

I fältet PivotTable-fält, dra produktkategori till radmarkeringsområde, OrderDate to Column Labels-området och TotalDue till Values-området. Som du kan se har datumfältet enskilda datum så att pivottabellen har skapat en kolumn för varje unikt datum. Excel 2010 har några inbyggda funktioner för att hjälpa oss att organisera datumfält.

10 av 15

Lägg till gruppering för datumfält

Grupperingsfunktionen låter oss organisera datum i år, månader, kvartaler etc. Det här hjälper till att sammanfatta data och underlätta för användaren att interagera med det. Högerklicka på en av kolumnrubrikerna och välj Grupp som ger upp grupperingsformuläret.

Fortsätt läsa nedan

11 av 15

Välj gruppering efter värden

Beroende på vilken typ av data du grupperar kommer formuläret att se lite annorlunda ut. I Excel 2010 kan du gruppera datum, nummer och valda textdata. Vi grupperar OrderDate i den här handledningen så att formuläret visar alternativ relaterade till datumgrupperingar.

Klicka på Månader och år och klicka på OK.

12 av 15

Pivottabell grupperat efter år och månader

Uppgifterna grupperas efter år först och sedan per månad. Var och en har ett plus- och minustecken som gör att du kan expandera och kollapsa beroende på hur du vill se data.

Vid denna tidpunkt är PivotTable ganska användbar. Var och en av fälten kan filtreras men problemet är att det inte finns någon visuell aning om filtorns nuvarande tillstånd. Det krävs flera klick för att ändra vyn.

13 av 15

Infoga skivare (Ny i Excel 2010)

Skivare är nya i Excel 2010. Skivare är i grunden lika med visuellt inställda filter i befintliga fält och skapar rapportfiltre om det objekt du vill filtrera på inte finns i den aktuella pivottabellen. Den här fina saken om Slicers är att det blir väldigt lätt för användaren att ändra visningen av data i pivottabellen samt att ge visuella indikatorer om filtrets aktuella tillstånd.

För att infoga skivor klickar du på fliken Alternativ och klickar på Infoga skivare i avsnittet Sortera och filtrera. Välj Infoga skavare som öppnar formuläret Infoga skivor. Kontrollera så många fält som du vill ha tillgängliga.

14 av 15

Pivottabell med användarvänliga skivor

Som du kan se visar skivarna alla data som valda. Det är mycket tydligt för användaren exakt vilken data som finns i den aktuella vyn av pivottabellen.

15 av 15

Välj värden från skivor Vilka uppdateringar pivottabell

Klicka på olika kombinationer av värden och se hur vyen av pivottabellen ändras. Du kan använda typiskt Microsoft-klick i Slicers, vilket innebär att om du kan använda Control + Click för att välja flera värden eller Shift + Click för att välja ett värdeområde.

Varje skärare visar de valda värdena, vilket gör det verkligen uppenbart vad tillståndet för pivottabellen är när det gäller filter. Du kan ändra formatet på skivorna om du vill genom att klicka på snabba format i nedrullningsområdet i fliken Alternativ.