Konfigurieren Sie Excel 2010-Pivot-Tabellen

Zwischen Microsoft Excel und den führenden Business-Intelligence-Plattformen (BI-Plattformen) besteht seit vielen Jahren eine Lücke. Die Verbesserungen der Microsoft Excel 2010-Pivot-Tabelle zusammen mit einigen anderen BI-Funktionen haben sie zu einem echten Konkurrenten für Enterprise BI gemacht. Excel wurde traditionell für die Einzelanalyse und das Standardwerkzeug verwendet, in das alle ihre Abschlussberichte exportieren. Professionelle Business Intelligence ist traditionell für SAS, Business Objects und SAP reserviert.

01 von 15

Endergebnis

Microsoft Excel 2010 (mit der Excel 2010-Pivot-Tabelle) zusammen mit SQL Server 2008 R2, SharePoint 2010 und dem kostenlosen Microsoft Excel 2010-Add-On "PowerPivot" hat zu einer hochwertigen Business Intelligence- und Berichterstellungslösung geführt.

In diesem Lernprogramm wird ein einfaches Szenario beschrieben, bei dem eine Excel 2010-PivotTable-Instanz mit einer SQL Server 2008 R2-Datenbank unter Verwendung einer einfachen SQL-Abfrage verbunden ist.

Lesen Sie weiter unten

02 von 15

Pivot-Tabelle einfügen

Sie können eine Pivot-Tabelle in eine neue oder vorhandene Excel-Arbeitsmappe einfügen. Vielleicht möchten Sie den Cursor einige Zeilen von oben nach unten positionieren. Dadurch haben Sie Platz für eine Kopfzeile oder Firmeninformationen, falls Sie das Arbeitsblatt freigeben oder ausdrucken möchten.

• Öffnen Sie eine neue oder vorhandene Excel 2010-Arbeitsmappe und klicken Sie auf die Zelle, in der sich die obere linke Ecke der Pivot-Tabelle befinden soll.
• Klicken Sie auf die Registerkarte Einfügen, und klicken Sie im Abschnitt Tabellen auf die Dropdown-Liste PivotTable. Wählen Sie PivotTable. Dadurch wird das Dialogfeld PivotTable erstellen geöffnet.

Lesen Sie weiter unten

03 von 15

Verbinden Sie die Pivot-Tabelle mit dem SQL Server

Excel 2010 kann Daten von allen großen RDBMS-Anbietern (Relational Database Management System) abrufen. SQL Server-Treiber sollten standardmäßig für die Verbindung verfügbar sein. Überprüfen Sie die Website, wenn Sie ODBC-Treiber herunterladen müssen.

Im Fall dieses Lernprogramms stellen wir eine Verbindung zu SQL Server 2008 R2 (SQL Express-freie Version) her.

1. Öffnen Sie das Create PivotTable-Formular. Wählen Sie "Externe Datenquelle verwenden" und klicken Sie auf die Schaltfläche "Verbindung auswählen". Verlassen Sie den Ort, an dem der Pivot-Tisch platziert wird.
2. Öffnen Sie das Formular Vorhandene Verbindungen. Klicken Sie auf die Schaltfläche Browse for More.
3. Klicken Sie auf die Schaltfläche Neue Quelle, um den Datenverbindungsassistenten zu starten.
4. Wählen Sie Microsoft SQL Server und klicken Sie auf Weiter.
5. Geben Sie den Servernamen und die Anmeldeinformationen ein. Wählen Sie die geeignete Authentifizierungsmethode aus:
   1. Verwenden Sie die Windows-Authentifizierung: Diese Methode verwendet Ihre Netzwerkanmeldung, um auf SQL Server-Datenbanken zuzugreifen.
   2. Verwenden Sie den folgenden Benutzernamen und das folgende Passwort: Diese Methode wird verwendet, wenn der SQL Server mit eigenständigen Benutzern für den Zugriff auf Datenbanken konfiguriert wurde.
6. Ersetzen Sie die Tabelle durch benutzerdefiniertes SQL, das genau die Daten enthält, die wir in unserer Excel-Arbeitsmappe benötigen:
   1. Wählen Sie die Datenbank aus, zu der Sie eine Verbindung herstellen möchten. In diesem Beispiel stellen wir eine Verbindung zur AdventureWorks-Beispieldatenbank von Microsoft her. Überprüfen Sie die Verbindung zu einer bestimmten Tabelle und wählen Sie die erste Tabelle aus. Denken Sie daran, dass wir keine Daten aus dieser Tabelle abrufen werden.
   2. Klicken Sie auf Fertig stellen, um den Assistenten zu schließen und zur Arbeitsmappe zurückzukehren. Wir werden die Platzhaltertabelle für unsere benutzerdefinierte SQL-Abfrage austauschen.

Sie kehren zum Formular Create PivotTable (A) zurück. OK klicken.

04 von 15

Pivot-Tabelle vorübergehend mit SQL-Tabelle verbunden

Zu diesem Zeitpunkt haben Sie eine Verbindung mit der Platzhaltertabelle hergestellt und Sie haben eine leere PivotTable. Auf der linken Seite sehen Sie, wo sich die PivotTable befinden wird, und auf der rechten Seite befindet sich eine Liste der verfügbaren Felder.

Lesen Sie weiter unten

05 von 15

Öffnen Sie die Verbindungseigenschaften

Stellen Sie sicher, dass Sie sich auf der Registerkarte Optionen befinden, und klicken Sie im Datenbereich auf die Dropdown-Liste Datenquelle ändern. Wählen Sie Verbindungseigenschaften.

Das Formular Verbindungseigenschaften wird angezeigt. Klicken Sie auf die Registerkarte Definition. Hier werden die Verbindungsinformationen für die aktuelle Verbindung zu SQL Server angezeigt. Während es auf eine Verbindungsdatei verweist, sind die Daten tatsächlich in die Kalkulationstabelle eingebettet.

06 von 15

Verbindungseigenschaften mit Abfrage aktualisieren

Ändern Sie den Befehlstyp von Tabelle in SQL und überschreiben Sie den vorhandenen Befehlstext mit Ihrer SQL-Abfrage. Hier ist die Abfrage, die wir aus der AdventureWorks-Beispieldatenbank erstellt haben:

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,Produktion.Produktname,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Produktion.Produkt.ListePreis,Produktion.Produkt.Produktlinie,Production.ProductSubcategory.Name AS ProduktkategorieFROM 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 ANDSales.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

OK klicken.

Lesen Sie weiter unten

07 von 15

Erhalten Sie eine Verbindungswarnung

Sie erhalten ein Microsoft Excel-Dialogfeld Warnung. Dies liegt daran, dass wir die Verbindungsinformationen geändert haben. Wenn wir die Verbindung ursprünglich erstellt haben, wurden die Informationen in einer externen .ODC-Datei (ODBC-Datenverbindung) gespeichert. Die Daten in der Arbeitsmappe waren die gleichen wie die .ODC-Datei, bis wir in Schritt # 6 von einem Tabellenbefehlstyp in einen SQL-Befehlstyp geändert wurden. Die Warnung weist Sie darauf hin, dass die Daten nicht mehr synchron sind und der Verweis auf die externe Datei in der Arbeitsmappe entfernt wird. Das ist in Ordnung. Klicken Sie auf Ja.

08 von 15

Pivot-Tabelle, die mit SQL Server mit Abfrage verbunden ist

Dies führt zurück zu der Excel 2010-Arbeitsmappe mit einer leeren PivotTable. Sie sehen, dass die verfügbaren Felder jetzt unterschiedlich sind und den Feldern in der SQL-Abfrage entsprechen. Wir können jetzt damit beginnen, Felder zur PivotTable hinzuzufügen.

Lesen Sie weiter unten

09 von 15

Felder zur Pivot-Tabelle hinzufügen

Ziehen Sie in der PivotTable-Feldliste ProductCategory in den Bereich Zeilenbeschriftungen, OrderDate in den Bereich Spaltenbeschriftungen und TotalDue in den Bereich Werte. Wie Sie sehen, hat das Datumsfeld individuelle Datumsangaben, sodass PivotTable für jedes eindeutige Datum eine Spalte erstellt hat. Excel 2010 verfügt über einige integrierte Funktionen, mit deren Hilfe wir Datumsfelder organisieren können.

10 von 15

Gruppierung für Datumsfelder hinzufügen

Die Gruppierungsfunktion ermöglicht es uns, Datumsangaben nach Jahren, Monaten, Quartalen usw. zu organisieren. Dies hilft dabei, die Daten zusammenzufassen und dem Benutzer die Interaktion mit ihm zu erleichtern. Klicken Sie mit der rechten Maustaste auf eine der Spaltenüberschriften für Datumsangaben, und wählen Sie Gruppe aus, um das Gruppierungsformular aufzurufen.

Lesen Sie weiter unten

11 von 15

Wählen Sie Gruppierung nach Werten

Je nach Art der Daten, die Sie gruppieren, sieht das Formular etwas anders aus. In Excel 2010 können Sie Datumsangaben, Zahlen und ausgewählte Textdaten gruppieren. In diesem Lernprogramm gruppieren wir OrderDate, sodass das Formular Optionen für Datumsgruppierungen enthält.

Klicken Sie auf Monate und Jahre und klicken Sie auf OK.

12 von 15

Pivot-Tabelle nach Jahren und Monaten gruppiert

Die Daten werden zuerst nach Jahr und dann nach Monat gruppiert. Jedes hat ein Plus- und Minuszeichen, mit dem Sie je nach Wunsch die Daten erweitern und reduzieren können.

An diesem Punkt ist die PivotTable ziemlich nützlich. Jedes der Felder kann gefiltert werden, das Problem ist jedoch, dass der aktuelle Status der Filter nicht angezeigt wird. Es dauert mehrere Klicks, um die Ansicht zu ändern.

13 von 15

Slicer einfügen (neu in Excel 2010)

Slicer sind neu in Excel 2010. Slicer sind im Grunde das Äquivalent dazu, Filter für vorhandene Felder visuell festzulegen und Berichtsfilter zu erstellen, falls das Element, nach dem Sie filtern möchten, nicht in der aktuellen PivotTable-Ansicht enthalten ist. Das Schöne an Slicers ist, dass der Benutzer die Ansicht der Daten in der PivotTable sehr leicht ändern und visuelle Indikatoren für den aktuellen Status der Filter anzeigen kann.

Um Slicer einzufügen, klicken Sie auf die Registerkarte Optionen und klicken Sie im Abschnitt Sortieren und Filtern auf Slicer einfügen. Wählen Sie Slicer einfügen, um das Formular Slicer einfügen zu öffnen. Markieren Sie so viele Felder, wie Sie zur Verfügung haben möchten.

14 von 15

Pivot-Tabelle mit benutzerfreundlichen Aufschnittmaschinen

Wie Sie sehen, zeigen die Slicers alle ausgewählten Daten an. Dem Benutzer ist sehr genau bekannt, welche Daten sich in der aktuellen Ansicht der PivotTable befinden.

15 von 15

Wählen Sie Werte aus Slicern aus, die die Pivot-Tabelle aktualisieren

Klicken Sie auf verschiedene Kombinationen von Werten und sehen Sie, wie sich die Ansicht der PivotTable ändert. Sie können ein typisches Microsoft-Klicken in den Slicers verwenden. Dies bedeutet, dass Sie mit Strg + Klick mehrere Werte auswählen können oder mit Umschalt + Klicken einen Wertebereich auswählen können.

Jeder Slicer zeigt die ausgewählten Werte an, wodurch der Status der PivotTable in Bezug auf Filter wirklich deutlich wird. Sie können die Stile der Slicer ändern, wenn Sie möchten, indem Sie im Abschnitt Slicer der Registerkarte Optionen auf die Dropdown-Liste Quick Styles klicken.