Configureer Excel 2010 draaitabellen

Er is al vele jaren een kloof tussen Microsoft Excel en de toplaag van BI-platforms (Business Intelligence). De verbeteringen in de draaitabel van Microsoft Excel 2010 samen met een aantal andere BI-functies hebben ervoor gezorgd dat het een echte concurrent is voor enterprise BI. Excel is van oudsher gebruikt voor zelfstandige analyse en de standaardtool die iedereen zijn eindrapporten exporteert. Professionele bedrijfsinformatie is van oudsher gereserveerd voor bedrijven als SAS, Business Objects en SAP.

01 van 15

Eindresultaat

Microsoft Excel 2010 (met de draaitabel van Excel 2010) samen met SQL Server 2008 R2, SharePoint 2010 en de gratis Microsoft Excel 2010 add-on "PowerPivot" heeft geresulteerd in een high-end business intelligence en rapportage-oplossing.

Deze zelfstudie behandelt een eenvoudig scenario met een Excel 2010 draaitabel verbonden met een SQL Server 2008 R2-database met behulp van een eenvoudige SQL-query.

Lees hieronder verder

02 van 15

Insert Pivot Table

U kunt een draaitabel invoegen in een nieuwe of bestaande Excel-werkmap. U zou kunnen overwegen om uw cursor een paar rijen vanaf de bovenkant neer te leggen. Dit geeft u ruimte voor een koptekst of bedrijfsinformatie voor het geval u het werkblad deelt of uitprint.

• Open een nieuwe of bestaande Excel 2010-werkmap en klik op de cel waar u de linkerbovenhoek van de draaitabel wilt hebben.
• Klik op het tabblad Invoegen en klik op de draaitabel in het gedeelte Tafels. Kies draaitabel. Hiermee wordt het dialoogvenster Draaitabel starten gestart.

Lees hieronder verder

03 van 15

Pivot-tabel verbinden met SQL Server

Excel 2010 kan gegevens ophalen van alle grote RDBMS-providers (Relational Database Management System). SQL Server-stuurprogramma's moeten standaard beschikbaar zijn voor de verbinding. Controleer hun website als u ODBC-stuurprogramma's moet downloaden.

In het geval van deze tutorial verbinden we met SQL Server 2008 R2 (gratis versie van SQL Express).

1. Open het formulier Create PivotTable. Selecteer "Gebruik een externe gegevensbron" en klik op de knop Verbinding kiezen. Verlaat de locatie waar de draaitabel zal worden geplaatst.
2. Open het formulier Bestaande verbindingen. Klik op de knop Bladeren naar meer.
3. Klik op de knop Nieuwe bron om de wizard Gegevensverbinding te starten.
4. Kies Microsoft SQL Server en klik op Volgende.
5. Voer de servernaam en inloggegevens in. Kies de juiste authenticatiemethode:
   1. Gebruik Windows-verificatie: Deze methode gebruikt uw netwerkaanmelding om toegang te krijgen tot SQL Server-databases.
   2. Gebruik de volgende gebruikersnaam en wachtwoord: Deze methode wordt gebruikt wanneer de SQL Server is geconfigureerd met zelfstandige gebruikers voor toegang tot databases.
6. Vervang de tabel door aangepaste SQL die precies de gegevens levert die we in onze Excel-werkmap willen:
   1. Selecteer de database waarmee u verbinding wilt maken. In dit voorbeeld maken we verbinding met de voorbeelddatabase van AdventureWorks die wordt aangeboden door Microsoft. Controleer de verbinding maken met een specifieke tabel en kies de eerste tabel. Vergeet niet dat we geen gegevens uit deze tabel zullen ophalen.
   2. Klik op Finish, waarna de wizard wordt gesloten en u terugkeert naar de werkmap. We zullen de placeholder-tabel omwisselen voor onze aangepaste SQL-query.

U keert terug naar het formulier Create PivotTable (A). Klik OK.

04 van 15

Draaitabel tijdelijk verbonden met SQL-tabel

Op dit punt hebt u verbinding gemaakt met de plaatshouderstabel en hebt u een lege draaitabel. U ziet aan de linkerkant waar de draaitabel zal staan ​​en aan de rechterkant is een lijst met beschikbare velden.

Lees hieronder verder

05 van 15

Open Connection Properties

Zorg ervoor dat u op het tabblad Opties staat en klik op Gegevensbron wijzigen in de sectie Gegevens. Kies Verbindingseigenschappen.

Hiermee wordt het formulier Connection Properties geopend. Klik op het tabblad Definitie. Hier ziet u de verbindingsinformatie voor de huidige verbinding met SQL Server. Hoewel het verwijst naar een verbindingsbestand, zijn de gegevens daadwerkelijk ingesloten in het werkblad.

06 van 15

Verbindingsinstellingen bijwerken met query

Wijzig het opdrachttype van tabel in SQL en overschrijf de bestaande opdrachttekst met uw SQL-query. Hier is de query die we hebben gemaakt met de voorbeelddatabase van 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,Productie.Product.naam,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Productie.Product.Lijstprijs,Productie.Product.Productlijn,Production.ProductSubcategory.Name AS ProductCategoryFROM Sales.SalesOrderDetail BINNEN JOIN Sales.SalesOrderHeader AANSales.SalesOrderDetail.SalesOrderID = SalesSalesOrderHeader.SalesOrderIDINNER JOIN Product.Product OP Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNERLIJK JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID BINNEN JOINProductie.ProductSubcategorie OP Productie.Product.ProductSubcategorieID =Production.ProductSubcategory.ProductSubcategoryID

Klik OK.

Lees hieronder verder

07 van 15

Verbindingswaarschuwing ontvangen

U ontvangt een dialoogvenster van Microsoft Excel Warning. Dit komt omdat we de verbindingsinformatie hebben gewijzigd. Toen we de verbinding oorspronkelijk maakten, werd de informatie opgeslagen in een extern .ODC-bestand (ODBC-gegevensverbinding). De gegevens in de werkmap waren hetzelfde als het .ODC-bestand totdat we in Stap # 6 veranderden van een tabelopdrachttype naar een SQL-opdrachttype. De waarschuwing vertelt u dat de gegevens niet langer gesynchroniseerd zijn en dat de verwijzing naar het externe bestand in de werkmap zal worden verwijderd. Dit is oke. Klik op Ja.

08 van 15

Draaitabel Verbonden met SQL Server met query

Dit neemt terug naar de Excel 2010-werkmap met een lege draaitabel. U kunt zien dat de beschikbare velden nu verschillend zijn en overeenkomen met de velden in de SQL-query. We kunnen nu beginnen met het toevoegen van velden aan de draaitabel.

Lees hieronder verder

09 van 15

Velden toevoegen aan draaitabel

Sleep ProductCategory naar de velden Rijlabels, Orderdatum naar kolomlabels en Totaalwaarden naar waarden in de lijst met draaitabelvelden. Zoals u kunt zien, heeft het datumveld afzonderlijke datums, zodat de draaitabel een kolom heeft gemaakt voor elke unieke datum. Excel 2010 heeft een aantal ingebouwde functies om ons te helpen bij het organiseren van datumvelden.

10 van 15

Groepering toevoegen voor datumvelden

Met de functie Grouping kunnen we datums in jaren, maanden, kwartalen enz. Organiseren. Dit helpt de gegevens samen te vatten en het voor de gebruiker gemakkelijker te maken om ermee om te gaan. Klik met de rechtermuisknop op een van de kolomkoppen van de datum en kies Groep die het Groeperingsformulier opent.

Lees hieronder verder

11 van 15

Kies Groeperen op waarden

Afhankelijk van het soort gegevens dat u groepeert, ziet het formulier er iets anders uit. In Excel 2010 kunt u datums, nummers en geselecteerde tekstgegevens groeperen. We groeperen OrderDate in deze tutorial zodat het formulier opties toont met betrekking tot datumgroepen.

Klik op Maanden en jaren en klik op OK.

12 van 15

Draaitabel Gegroepeerd op jaar en maanden

De gegevens zijn gegroepeerd per jaar eerst en vervolgens per maand. Elk heeft een plus- en minteken waarmee u kunt uitvouwen en samenvouwen afhankelijk van hoe u de gegevens wilt zien.

Op dit punt is de draaitabel vrij handig. Elk van de velden kan worden gefilterd, maar het probleem is dat er geen visuele aanwijzing is voor de huidige status van de filters. Er zijn verschillende klikken nodig om de weergave te wijzigen.

13 van 15

Slicer invoegen (nieuw in Excel 2010)

Slicers zijn nieuw in Excel 2010. Slicers zijn in feite het equivalent van visueel ingestelde filters van de bestaande velden en het maken van rapportfilters in het geval dat het item dat u wilt filteren zich niet in de huidige draaitabelweergave bevindt. Dit leuke aan Slicers is dat het voor de gebruiker heel gemakkelijk is om de weergave van de gegevens in de draaitabel te wijzigen en ook visuele indicatoren te geven voor de huidige status van de filters.

Als u Slicers wilt invoegen, klikt u op het tabblad Opties en klikt u op Slicer invoegen in het gedeelte Sorteren en filteren. Kies Insert Slicer waarmee het Insert Slicers-formulier wordt geopend. Controleer zo veel velden als u beschikbaar wilt hebben.

14 van 15

Draaitabel met gebruikersvriendelijke slicers

Zoals u kunt zien, tonen de Slicers alle gegevens zoals ze zijn geselecteerd. Het is de gebruiker heel duidelijk welke gegevens in de huidige weergave van de draaitabel staan.

15 van 15

Kies waarden van slicers die de draaitabel bijwerken

Klik op verschillende combinaties van waarden en zie hoe de weergave van de draaitabel verandert. U kunt typisch Microsoft-klikken gebruiken in de Slicers, wat betekent dat als u Control + Click kunt gebruiken om meerdere waarden te selecteren of Shift + klik om een ​​bereik van waarden te selecteren.

Elke Slicer geeft de geselecteerde waarden weer, waardoor het heel duidelijk wordt wat de staat van de draaitabel is in termen van filters. U kunt de stijlen van de Slicers desgewenst wijzigen door op het dropdown-menu Quick Styles in het gedeelte Slicer op het tabblad Opties te klikken.