Skonfiguruj tabele przestawne programu Excel 2010

Przez wiele lat istniała luka między Microsoft Excel a platformami Business Intelligence (BI) najwyższego poziomu. Udoskonalenia tabeli przestawnej w programie Microsoft Excel 2010 oraz kilka innych funkcji BI sprawiły, że stał się prawdziwym konkurentem w zakresie BI dla przedsiębiorstw. Excel był tradycyjnie używany do samodzielnej analizy i standardowego narzędzia, do którego wszyscy eksportują swoje raporty końcowe. Profesjonalne analizy biznesowe są tradycyjnie zarezerwowane dla takich firm jak SAS, Business Objects i SAP.

01 z 15

Ostateczny wynik

Microsoft Excel 2010 (z tabelą przestawną programu Excel 2010) wraz z programem SQL Server 2008 R2, programem SharePoint 2010 i bezpłatnym dodatkiem Microsoft Excel 2010 "PowerPivot" zaowocował rozwiązaniem klasy biznesowej i raportowania najwyższej klasy.

Ten samouczek obejmuje prosty scenariusz z tabelą przestawną Excel 2010 połączoną z bazą danych SQL Server 2008 R2 przy użyciu prostego zapytania SQL.

Kontynuuj czytanie poniżej

02 z 15

Wstaw tabelę przestawną

Tabelę przestawną można wstawić do nowego lub istniejącego skoroszytu programu Excel. Możesz rozważyć ustawienie kursora w dół o kilka rzędów od góry. Da ci to miejsce na nagłówek lub informacje o firmie w przypadku udostępnienia arkusza roboczego lub wydrukowania go.

• Otwórz nowy lub istniejący skoroszyt programu Excel 2010 i kliknij komórkę, w której ma znajdować się lewy górny róg tabeli przestawnej.
• Kliknij kartę Wstaw i kliknij menu rozwijane Tabela przestawna w sekcji Tabele. Wybierz tabelę przestawną. Spowoduje to uruchomienie formularza okna dialogowego Utwórz tabelę przestawną.

Kontynuuj czytanie poniżej

03 z 15

Połącz tabelę przestawną z programem SQL Server

Excel 2010 może pobierać dane ze wszystkich głównych dostawców RDBMS (Relational Database Management System). Sterowniki SQL Server powinny być domyślnie dostępne dla połączenia. Sprawdź ich stronę internetową, jeśli chcesz pobrać sterowniki ODBC.

W przypadku tego samouczka łączymy się z SQL Server 2008 R2 (darmowa wersja SQL Express).

1. Otwórz formularz Create PivotTable. Wybierz "Użyj zewnętrznego źródła danych" i kliknij przycisk Wybierz połączenie. Pozostaw lokalizację, w której zostanie umieszczony stół przestawny.
2. Otwórz formularz Istniejące połączenia. Kliknij przycisk Przeglądaj, aby uzyskać więcej.
3. Kliknij przycisk Nowe źródło, aby uruchomić Kreatora połączeń danych.
4. Wybierz Microsoft SQL Server i kliknij Dalej.
5. Wprowadź nazwę serwera i dane logowania. Wybierz odpowiednią metodę uwierzytelniania:
   1. Użyj uwierzytelniania systemu Windows: Ta metoda używa loginu sieciowego w celu uzyskania dostępu do baz danych SQL Server.
   2. Użyj następującej nazwy użytkownika i hasła: Ta metoda jest używana, gdy SQL Server został skonfigurowany z autonomicznymi użytkownikami, aby uzyskać dostęp do baz danych.
6. Zastąp tabelę niestandardowym kodem SQL, który zapewni dokładnie takie dane, jakie chcemy w naszym skoroszycie programu Excel:
   1. Wybierz bazę danych, z którą chcesz się połączyć. W tym przykładzie łączymy się z przykładową bazą danych AdventureWorks dostarczoną przez firmę Microsoft. Zaznacz opcję Połącz z konkretną tabelą i wybierz pierwszą tabelę. Pamiętaj, że nie będziemy pobierać danych z tej tabeli.
   2. Kliknij przycisk Zakończ, który zamknie kreatora i powróci do skoroszytu. Będziemy zamieniać tabelę symboli zastępczych dla naszego niestandardowego zapytania SQL.

Powrócisz do formularza Utwórz tabelę przestawną (A). Kliknij OK.

04 z 15

Tabela przestawna tymczasowo połączona z tabelą SQL

W tym momencie połączyłeś się z tabelą symboli zastępczych i masz pustą tabelę przestawną. Po lewej stronie widać tabelę przestawną, a po prawej znajduje się lista dostępnych pól.

Kontynuuj czytanie poniżej

05 z 15

Otwórz Właściwości połączenia

Upewnij się, że jesteś na karcie Opcje i kliknij opcję Zmień źródło danych z sekcji Dane. Wybierz Właściwości połączenia.

Pojawi się formularz Właściwości połączenia. Kliknij kartę Definicja. Spowoduje to wyświetlenie informacji o połączeniu dla bieżącego połączenia z serwerem SQL. Chociaż odwołuje się do pliku połączenia, dane są faktycznie osadzone w arkuszu kalkulacyjnym.

06 z 15

Zaktualizuj właściwości połączenia za pomocą zapytania

Zmień typ polecenia z tabeli na SQL i nadpisz istniejący tekst polecenia za pomocą zapytania SQL. Oto zapytanie, które stworzyliśmy z przykładowej bazy danych 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,Production.Product.Name,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Production.Product.ListPrice,Production.Product.ProductLine,Production.ProductSubcategory.Name AS ProductCategoryZ Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER DOŁĄCZ DO 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

Kliknij OK.

Kontynuuj czytanie poniżej

07 z 15

Odbierz ostrzeżenie o połączeniu

Otrzymasz okno dialogowe Ostrzeżenie Microsoft Excel. To dlatego, że zmieniliśmy informacje o połączeniu. Kiedy pierwotnie stworzyliśmy połączenie, zapisano je w zewnętrznym pliku .ODC (ODBC Data Connection). Dane w skoroszycie były takie same jak plik .ODC, dopóki nie zmieniliśmy z typu polecenia tabeli na typ polecenia SQL w kroku 6. Ostrzeżenie informuje, że dane nie są już zsynchronizowane, a odwołanie do zewnętrznego pliku w skoroszycie zostanie usunięte. To jest wporządku. Kliknij Tak.

08 z 15

Tabela przestawna połączona z serwerem SQL z zapytaniem

Spowoduje to powrót do skoroszytu programu Excel 2010 z pustą tabelą przestawną. Widać, że dostępne pola są teraz różne i odpowiadają polom w zapytaniu SQL. Możemy teraz rozpocząć dodawanie pól do tabeli przestawnej.

Kontynuuj czytanie poniżej

09 z 15

Dodaj pola do tabeli przestawnej

Na liście pól tabeli przestawnej przeciągnij pole ProductCategory do obszaru etykiety wierszy, pola OrderDate to Column Labels i TotalDue to Values. Jak widać, pole daty ma indywidualne daty, dlatego w tabeli przestawnej została utworzona kolumna dla każdej unikalnej daty. Excel 2010 ma wbudowane funkcje ułatwiające organizowanie pól daty.

10 z 15

Dodaj grupowanie pól dat

Funkcja grupowania pozwala nam organizować daty na lata, miesiące, kwartały itp. Pomoże to podsumować dane i ułatwić użytkownikowi interakcję z nim. Kliknij prawym przyciskiem myszy jeden z nagłówków kolumn daty i wybierz Grupa, która wyświetla formularz Grupowanie.

Kontynuuj czytanie poniżej

11 z 15

Wybierz Grupowanie według wartości

W zależności od rodzaju danych, które grupujesz, formularz będzie wyglądał trochę inaczej. Excel 2010 umożliwia grupowanie dat, liczb i wybranych danych tekstowych. Grupujemy OrderDate w tym samouczku, więc formularz pokaże opcje związane z grupowaniem dat.

Kliknij Miesiące i Lata i kliknij OK.

12 z 15

Tabela przestawna pogrupowane według lat i miesięcy

Dane są pogrupowane według roku, a następnie miesiąca. Każdy ma znak plusa i minusa, który pozwala na rozwijanie i zwijanie w zależności od tego, jak chcesz zobaczyć dane.

W tym momencie tabela przestawna jest całkiem przydatna. Każde z pól można filtrować, ale problem polega na tym, że nie ma wizualnej wskazówki co do bieżącego stanu filtrów. Zmiana widoku wymaga kilku kliknięć.

13 z 15

Wstaw fragmentator (nowość w programie Excel 2010)

Krajarki są nowe w programie Excel 2010. Krajarki są w zasadzie odpowiednikiem wizualnego ustawiania filtrów istniejących pól i tworzenia filtrów raportów w przypadku, gdy element, na który chcesz filtrować, nie znajduje się w bieżącym widoku tabeli przestawnej. Ta miła rzecz w Slicersach sprawia, że ​​użytkownik bardzo łatwo zmienia widok danych w tabeli przestawnej, a także dostarcza wizualne wskaźniki dotyczące aktualnego stanu filtrów.

Aby wstawić Slicers, kliknij kartę Opcje i kliknij Wstaw fragmentator w sekcji Sortowanie i filtrowanie. Wybierz Insert Slicer, który otwiera formularz Insert Slicers. Zaznacz tyle pól, ile chcesz mieć.

14 z 15

Tabela przestawna z przyjaznymi dla użytkownika krajalnicami

Jak widać, fragmentatory wyświetlają wszystkie dane w wybrany sposób. Użytkownik dokładnie wie, jakie dane znajdują się w bieżącym widoku tabeli przestawnej.

15 z 15

Wybierz wartości z fragmentatorów, które aktualizuje tabelę przestawną

Kliknij różne kombinacje wartości i zobacz, jak zmienia się widok tabeli przestawnej. Możesz użyć typowego kliknięcia Microsoft w Slicers, co oznacza, że ​​jeśli możesz użyć Control + Kliknij, aby wybrać wiele wartości lub Shift + Kliknij, aby wybrać zakres wartości.

Każdy element wyskakujący wyświetla wybrane wartości, co sprawia, że ​​jest oczywiste, jaki jest stan tabeli przestawnej pod względem filtrów. Możesz zmienić style fragmentatorów, jeśli chcesz, klikając menu rozwijane Szybkie style w sekcji Slicer na karcie Opcje.