Tabele przestawne i wykresy w Excel 2007 - Poziom: 200

Autor: Adam Walocha

Opublikowano: 23 lutego 2007

Zawartość strony

	Wprowadzenie
	Łączenie z serwerem SQL
	Łączenie z plikiem bazy Microsoft Access
	Zaawansowane opcje importu danych
	Konfiguracja tabeli przestawnej
	Wykresy przestawne

Wprowadzenie

Tabele przestawne są narzędziem służącym do analizy i przeglądania danych, dostępnym w arkuszu kalkulacyjnym Microsoft Excel. W połączeniu z systemem dostępu do baz danych, który również jest możliwy w najnowszym Excelu, łatwo i wygodnie możemy pracować na naszych danych zapisanych na serwerze SQL lub w pliku bazy danych Microsoft Access. Nie musimy w tym celu znać się na obsłudze tych serwerów, a tym bardziej znać języka SQL. Tabele przestawne umożliwiają nie tylko przeglądanie i publikowanie tabel, ale również tworzenie kwerend, sumowanie według zadanych kryteriów, filtrowanie, sortowanie, grupowanie i formatowanie, a także hierarchiczny sposób wyświetlania danych.

Po przygotowaniu tabeli przestawnej jednym kliknięciem możemy stworzyć też wykres, ilustrujący informacje w niej przedstawione.

Do początku strony

Łączenie z serwerem SQL

Aby połączyć się z serwerem SQL, musimy znać jego nazwę. Jeśli łączymy się z bazą uruchomioną na naszym komputerze, to nazwę można sprawdzić programem SQL Server Configuration Manager, który jest domyślnie instalowany z serwerem Microsoft SQL Express. Po uruchomieniu tej aplikacji wybieramy z listy po lewej stronie SQL Server 2005 Services. W głównym oknie powinniśmy zobaczyć spis usług bazodanowych dostępnych na komputerze. Nas interesuje w tym momencie usługa SQL Server, czyli po prostu główny serwer. Gdy znajdziemy na liście nasz, to w nawiasie będzie umieszczona szukana nazwa. W tym przypadku jest to SQLEXPRESS.

Rys. 1. Połączenie z serwerem SQL

Jeśli łączymy się ze zdalnym serwerem SQL to musimy znać dokładną jego nazwę oraz nazwę komputera, na którym on pracuje. Dodatkowo serwer zdalny musi dopuszczać połączenia z innych komputerów.

Po ustaleniu potrzebnych informacji odnośnie serwera, możemy zająć się połączeniem w samym Excelu. Po stworzeniu nowego arkusza wybieramy z menu głównego zakładkę Dane. Następnie klikamy element Z innych źródeł znajdujący się w grupie Dane zewnętrzne. Z wyświetlonej listy wybieramy Z programu SQL server. Powinno się wyświetlić okno dialogowe kreatora połączenia danych:

Rys. 2. Kreator połączenia danych

W polu Nazwa serwera musimy podać nazwę komputera i serwera, którą wcześniej sprawdzaliśmy, w następującej postaci:

<nazwa komputera>\<nazwa serwera>

W naszym przypadku podajemy nazwę własnego komputera (na nim pracuje serwer) i nazwę SQLEXPRESS . Jeśli łączymy się zdalnie, to musimy oczywiście podać nazwę sieciową komputera zdalnego. Drugi punkt dotyczy sposobu logowania na serwer. W przypadku łączenia się z serwerem lokalnym przeważnie opcja Użyj uwierzytelniania systemu Windows będzie odpowiednia. Oznacza ona, że na serwerze jest jedno konto domyślne i na nie się logujemy. Na serwerach zdalnych przeważnie jest wymagane logowanie na konto (musimy znać login i hasło). Informacje te powinien podać administrator danego serwera. Jeśli połączenie się powiedzie to zobaczymy dialog wyboru bazy danych i tabel. W tym przykładzie wybieramy z bazy test tabelę Produkty.

Rys. 3. Kreator połączenia danych

Kolejne okno dialogowe dotyczy pliku połączenia (przechowuje informacje o tym jak Excel ma się łączyć z bazą). My na razie zapiszemy ustawienia w domyślnym pliku - klikamy zakończ. Przechodzimy do okna importowania danych. Wybieramy Raport tabeli przestawnej lub Raporty tabeli przestawnej i wykresu przestawnego. W ten sposób stworzyliśmy tabelę przestawną ew. wraz z wykresem. Dalszy opis konfiguracji tabeli znajduje się następnej części artykułu.

Do początku strony

Łączenie z plikiem bazy Microsoft Access

Wykorzystywanie baz danych stworzonych w programie Microsoft Access jest prostsze, niż łączenie z serwerem SQL. Zamiast znać nazwę serwera i komputera wystarczy nazwa pliku bazy o rozszerzeniu MDB. Zaczynamy. Po stworzeniu nowego arkusza wybieramy z menu głównego zakładkę Dane. Następnie klikamy element z programu Access z grupy Dane zewnętrzne. Wyświetli się dialog wyboru pliku. Wskazujemy plik bazy danych i potwierdzamy. Teraz musimy wybrać tabelę z bazy, dla której tworzymy tabelę przestawną.

Rys. 4. Wybieranie tabeli

Następnie zobaczymy ten sam dialog importu danych co w przypadku serwera SQL. Jako sposób wyświetlania zaznaczamy tabelę przestawną, ew. od razu z wykresem. Otrzymujemy na razie pustą tabelę przestawną. Opis jej konfiguracji znajduje się dalszej części artykułu.

Do początku strony

Zaawansowane opcje importu danych

W dwóch wcześniejszych rozdziałach zajmowaliśmy się łączeniem tabeli przestawnej z jedną konkretną tabelą z bazy danych. Często istnieje potrzeba analizy tabeli będącej wynikiem pewnego zapytania SQL wykonanego na serwerze. Oczywiście nie ma sensu chociażby sortowanie, bo można to o wiele prościej zrobić już w samej tabeli przestawnej. Świetnym przykładem może być tutaj łączenie tabel (operacja JOIN ). W naszej bazie produktów istnieją trzy tabele: Produkty, Sprzedaże i Klienci. Zastanówmy się, jak otrzymać tabelę pokazującą co zakupił każdy klient ze sklepu i za jaką kwotę. Do tabeli przestawnej będziemy musieli użyć tabeli, jaką zwróci następujące zapytanie SQL:

SELECT Klienci.Imie, Klienci.Nazwisko, Sprzedaze.idSprzedazy, _

Sprzedaze.Cena, Sprzedaze.Ilosc,Produkty.Nazwa, Produkty.Kategoria, Produkty.Marka 

FROM Klienci 

INNER JOIN Sprzedaze ON Klienci.idKlienta = Sprzedaze.idKlienta

INNER JOIN Produkty ON Sprzedaze.idProduktu = Produkty.idProduktu

Rys. 5. Właściwości połączenia

Spowoduje ono połączenie tych trzech tabel według kluczy obcych w tabeli Sprzedaże. Tworzenie tabeli przestawnej będzie się różniło tylko ostatnim etapem. W ostatnim oknie dialogowym Importowanie danych wybieramy jak wcześniej Raport tabeli przestawnej, ale przed potwierdzeniem klikamy przycisk Właściwości. Z wyświetlonego okna Właściwości połączenia wybieramy zakładkę Definicja. Jako typ polecenia wybieramy SQL, a w polu tekst polecenia wpisujemy pożądane zapytanie SQL. Po zatwierdzeniu tworzona jest tabela przestawna prezentująca dane zwrócone jako wynik zapytania SQL.

Rys. 6. Etykiety wierszy

Do początku strony

Konfiguracja tabeli przestawnej

Zaraz po stworzeniu tabeli przestawnej nie wyświetla ona żadnych informacji. Sposoby jej konfiguracji pokażemy na przykładzie zaimportowanej wcześniej tabeli produktów.

Wyświetlanie i grupowanie

Po kliknięciu na tabelę przestawną po prawej stronie pojawia się okienko Lista pól tabeli przestawnej. Tam też możemy dodawać kolejne pola do wyświetlania, zaznaczając kolejne nazwy z listy.

Rys. 7. Lista pól tabeli przestawnej

Załóżmy, że chcemy pogrupować produkty najpierw według kategorii, potem według marki, a dodatkowo mają być wyświetlane nazwy produktów w poszczególnych grupach. Aby uzyskać taki efekt, zaznaczamy kolejno pola:

1. Kategoria 2. Marka 3. Nazwa

Rys. 8. Etykiety wierszy

Jeśli zmienilibyśmy kolejność zaznaczania na np. taką:

1. Marka 2. Kategoria 3. Nazwa

to produkty byłyby najpierw grupowane według marki, a potem w ramach każdej z nich byłyby dzielone na kategorie.

Rys. 9. Etykiety wierszy

Sumowanie i zestawienia

Pole IlośćMagazynowa oznacza ilość danego produktu w magazynie. Spróbujmy sporządzić takie zestawienie, aby dowiedzieć się ile jest produktów w każdej kategorii oraz aby mieć możliwość sprawdzenia ilości poszczególnych produktów w kategorii. Dodatkowo niech każdy produkt ma wyświetloną swoją markę (ale bez grupowania według niej). Zaznaczamy pola według kolejności:

1 .Kategoria 2. Nazwa 3.Marka 4.IloscMagazynowa

Ostatnie pole może być, tak naprawdę, zaznaczone w dowolnym momencie. Typy liczbowe nie są brane pod uwagę przy grupowaniu.

Rys. 10. Sumowanie

Oczywiście zamiana miejscami pól 2 i 3 spowoduje grupowanie produktów w każdej kategorii według marki. Jak widać, kolejność ma tutaj znaczenie.

Formuły

Często zwykłe sumowanie to za mało. W naszej bazie każdy produkt ma cenę. Jak więc rozbudować przykład wcześniejszy, aby poza ilością, wyświetlana była dla każdego produktu również całkowita wartość, czyli po prostu iloczyn ilości danego produktu w magazynie i jego ceny? Z pomocą przychodzą nam formuły. Załóżmy, że mamy tabelę przestawną z poprzedniego przykładu (sumowanie i zestawianie). Zaznaczamy ją i wybieramy zakładkę Opcje z menu głównego. Klikamy Formuły, potem podmenu Pole obliczeniowe. Uzupełniamy wyświetlony dialog tak, jak na obrazku i potwierdzamy.

Rys. 11. Wstawianie pola obliczeniowego

Do naszej tabeli przestawnej została dodana kolumna zawierająca potrzebne nam iloczyny. Podobnie jak ilość w magazynie, poszczególne iloczyny są sumowane dla każdej grupy.

Okienko Lista pól przestawnych

W okienku Lista pól przestawnych znajdują się na dole miejsca, pomiędzy którymi możemy przenosić pola. Obszar Etykiety wierszy zawiera te pozycje, które zaznaczyliśmy w sposób jaki opisaliśmy powyżej. Każdy z elementów tego obszaru możemy przeciągnąć na wyższe lub niższe miejsce, co spowoduje zmianę sposobu grupowania. Zmiana grupowania przedstawionego na rysunku 5, na to z rysunku 6, polegałaby na przesunięciu pola marka nad pole kategoria. Warto poeksperymentować z tym narzędziem. Etykiety kolumn umożliwiają użycie pewnego pola do indeksowania kolumn. Załóżmy, że chcemy w formie tabeli pokazać ile produktów każdej marki należy do poszczególnych kategorii. Najczytelniej byłoby w wierszach umieścić marki, a w kolumnach kategorie. Aby to zrobić, dodajemy najpierw zaznaczając z listy pól: Marka, Kategoria, IlośćMagazynowa. Otrzymujemy tabelę taką, jak wcześniej. Spójrzmy na obszar Etykiety wierszy.

Rys. 12. Lista pól przestawnych

Znajdują się tam w tej chwili dwa pola. Przeciągnijmy jedno z nich do Etykiety kolumn, dzięki czemu uzyskamy planowany efekt.

Rys. 13. Lista pól przestawnych

Układ i style

Po zaznaczeniu tabeli przestawnej wybierzmy z menu głównego zakładkę Projektowanie. Znajdują się na niej narzędzia do zmiany stylu. Jednym kliknięciem możemy dostosować kolorystykę raportu, wybierając odpowiadający styl z listy. Dostępne są również opcje stylu - włączanie i wyłączanie kolorowania nagłówków, wierszy i kolumn. Grupa opcji Układ umożliwia dostosowanie sposobu wyświetlania sum częściowych i końcowych. Wyłączenie tych pierwszych spowoduje, że będą one pokazywane dopiero po zwinięciu grupy - jest to o wiele czytelniejsze. Z kolei sumy końcowe nie zawsze są nam potrzebne i tu możemy je wyłączyć (dla wierszy i kolumn, lub tylko dla jednego z nich). Pozostałe opcje pozwalają dostosować sposób wyświetlania grup (rozwijane po kliknięciu, lub w formie tabeli).

Do początku strony

Wykresy przestawne

Wykres przestawny jest niczym innym, jak ilustracją danych z tabeli przestawnej. Aby go stworzyć, zaznaczamy tabelę przestawną, a następnie wybieramy z menu głównego zakładkę Opcje i klikamy Wykres przestawny. Przedstawia on zawsze dane odpowiadające aktualnemu stanowi powiązanej z nim tabeli. Jeśli rozwiniemy w niej pewną grupę, to na wykresie również zostanie ona podzielona. Na poniższym przykładzie jest to tylko jedna grupa, Procesor. Gdy została rozwinięta - na wykresie nastąpił podział jednego elementu.

Rys. 14. Wykres przestawny

Po zaznaczeniu wykresu, w menu głównym pojawiają się cztery nowe zakładki (zaznaczone na zielono) . Z ich pomocą formatowanie jest łatwe i wygodne.

Pracując z tabelami przestawnymi dochodzę do wniosku, że jest to jeden z najprostszych sposobów na korzystanie z danych przechowywanych na serwerach SQL. Prostota obsługi powoduje, że większość użytkowników nie mających wcześniej styczności z bazami danych, bez problemu sobie z tym poradzi, a przy tym wcale nie będą się oni musieli uczyć się języka SQL.

Do początku strony