Excel – Baza danych i tabela przestawna

Opracowanie:
dr inż. Marek Sikora

1          

• Zakładać w Excelu bazę danych

• Sortować dane

• Tworzyć przyjazny dla użytkownika formularz do wprowadzania danych

• Wyszukiwać dane

• Tworzyć zestawienia danych

1.1           Przykład1

Rejestruj sprzedaż

Każda baza danych jest ustrukturalizowanym sposobem rejestrowania danych, tak by łatwo było je wyszukiwać. Ty i pracownicy Twojej firmy doszliście zgodnie do wniosku, że należałoby spróbować rejestrować przez tydzień sprzedaż towarów. Chcecie wiedzieć, na jakie towary jest najwyższy popyt, jakie są trendy w sprzedaży - co mogłoby być przydatne przy zakupie nowych towarów.

Nie potrzeba do tego pomocy programistów i wydatków na rozwój systemu komputerowego, ponieważ rejestracja sprzedawanych towarów może być wykonywana za pomocą bazy danych Excela. Twój stworzony domowym sposobem "system" będzie posiadał przyjazny dla użytkownika formularz do wprowadzania danych i bez trudu znajdziesz w nim konkretne informacje. Gdybyś potrzebował wyciągu z danych, to będziesz mógł utworzyć Raport tabeli przestawnej, który jest podsumowaniem danych. Do roboty, za pół godziny Twój system będzie gotowy!

Tworzenie formularza

Załóżmy nowy skoroszyt, wpiszmy tytuły i zacznijmy wprowadzać dane.

Spróbuj sam:

1. Otwórz nowy skoroszyt klikając na przycisku Nowy.

2. W komórkę A1 wpisz pierwszy tytuł: Data. W tytułach zawsze używaj pojedynczych wyrazów lub skrótów, żadnych spacji, przecinków, myślników itp.

3. Kolejny tytuł: Produkt wpisz w komórkę B1. Jeszcze jedna rzecz, którą dobrze wiedzieć: pomiędzy kolumnami zawierającymi dane nie powinno być pustych kolumn.

4. W komórkę C1 wpisujesz: Sprzedaż, a w komórkę D1: Sprzedawca.

5. Gratulacje, baza danych właśnie została utworzona i jest już gotowa do użytku! Wyróżnij tytuły poprzez pogrubienie czcionki, jaką zostały napisane (zaznacz tytuły i kliknij na przycisku Pogrubienie).

6. Wpisz w komórki pierwszego wiersza dane - tak jak pokazano na ilustracji. Trzeciego sierpnia pan Doleżal sprzedał świeczki za 20 zł.

7. A gdzie przyjazny dla użytkownika formularz do wprowadzania danych? Umieść kursor w polu rekordu, np. w komórce A1. Z menu Dane wybierz pozycję Formularz.

8. Kliknij na przycisku Nowy. Napisz: 3-sier. Wciśnij klawisz Tab i napisz: Świeczki. Ponownie wciśnij Tab i wpisz: 20. Jeszcze raz naciśnij Tab i na koniec napisz Doleżal. W ten sposób cały formularz został wypełniony.

9. Chcesz zobaczyć rezultat? Jeśli tak, to kliknij na przycisku Zamknij. Dane zostały umieszczone w kolumnach. Wniosek: Do wpisywania danych mo-żesz używać formularza lub wprowadzać je ręcznie.

10. Z menu Dane wybierz ponownie pozycję Formularz. Do bazy danych wpiszemy jeszcze kilka rekordów, dlatego kliknij teraz na przycisku Nowy

11. Wpisz te oto dane: 03-sier/Ozdoby/52/Włodarski (znaków "/" nie wpisuj, w podanym tutaj zapisie oznaczają one, że należy przejść do następnego pola formularza, czyli wcisnąć klawisz Tab).

12. Po wypełnieniu formularza kliknij na przycisku Nowy i wpisz nowy rekord: 03-sier/Herbata/50/Doleżal.

13. Ponownie kliknij na przycisku Nowy i wprowadź do bazy danych podane niżej rekordy Pamiętaj o wciśnięciu przycisku Nowy po każdym wypełnieniu formularza.

14. 04-sier/świeczki/20/Chowanec

15. 04-sfer/ozdoby/72/Doleżal

16. 05-sfer/świeczki/11 /Włodarski

17. 05-sier/herbata/25/Doleżal

18. 05-sier/herbata/43/Chowanec

19. Wciśnij przycisk Zamknij i spójrz na rezultat. Powinien on wyglądać tak jak na zamieszczonej obok ilustracji.

20. Formularza można również używać, by ułatwić i przyspieszyć wyszukiwanie. Docenisz tę funkcję, kiedy będziesz musiał znaleźć jakieś dane z bazy, w którą wpisano setki rekordów. Spróbujmy odszukać, co sprzedał Chowanec 4 sierpnia. Wywołaj formularz, wybierając z menu Dane pozycję Formularz.

21. Wciśnij przycisk Kryteria i wpisz w pole Data: 8-4. Kliknij w polu Sprzedawca lub przeskocz do niego za pomocą klawisza Tab i wpisz : Chowanec. Dla pewności sprawdź, czy wypełniłeś formularz tak samo, jak na zamieszczonej obok ilustracji.

22. Kliknij na przycisku Znajdź następny i proszę bardzo - odnalazł się rekord spełniający kryteria wyszukiwania.

Jeśli Chowanec sprzedał tego dnia więcej towarów, to możesz obejrzeć wszystkie jego transakcje, wciskając przyciski Znajdź następny i Znajdź poprzedni. Jeżeli po kliknięciu na przycisku usłyszysz krótkie "bip", oznacza to, że nie ma więcej rekordów speiniających kryteria wyszukiwania.

23. Spróbuj przeglądać rekordy za pomocą klawiszy  . Na koniec zamknij formularz, klikając na przycisku Zamknij.

24. Zapisz skoroszyt pod nazwą "Sprzedaż i statystyka". Nie zamykaj go jednak, gdyż to nie koniec zabawy.

Wyszukiwanie za pomocą Autofiltra

Dla uzyskania przejrzystości dobrze byłoby odizolować niektóre dane z tej długiej i nieprzejrzystej listy. Ułatwiłoby to znalezienie odpowiedzi na pytania: co zostało sprzedane 5 sierpnia? Ile razy sprzedano świece? Co sprzedał Chowanec? Ile razy sprzedaż przewyższała kwotę 20 zł?

Spróbuj sam:

1. Umieść kursor w dowolnym polu listy, w którym wpisano dane, i wybierz z menu Dane pozycję Filtr, a następnie Autofiltr.

2. Przy wszystkich tytułach kolumn pojawiły się teraz strzałki. Kliknij na tej, która znajduje się przy tytule Data.

3. Z listy, która się wyświetli, wybierz 05-sie. Proszę bardzo - ukazała się sprzedaż tylko z 5 sierpnia. Zwróć uwagę, że strzałka stała się niebieska. Oznacza to, że w kolumnie Data został uaktywniony filtr.

4. Kliknij ponownie na strzałce w kolumnie Data. Wybierz z listy pozycję Wszystkie. Widzisz znowu wszystkie rekordy, a strzałka zmieniła kolor na czarny.

5. Kliknij na strzałce w kolumnie Produkt i z listy wybierz pozycję "świeczki". Zobaczysz wszystkie transakcje, podczas których sprzedano świeczki. Kliknij ponownie na strzałce i wybierz pozycję Wszystkie.

6. Spróbujmy teraz określić, co 3 sierpnia sprzedał Doleżal. Kliknij na strzałce w kolumnie Data i z listy wybierz pozycję 3-sie. Naciśnij strzałkę w kolumnie Sprzedawca i wybierz nazwisko Doleżal. Gotowe! Po obejrzeniu rezultatu klikaj po kolei każdą z niebieskich strzałek i wybierz w każdej z list pozycje Wszystkie.

7. Przyjrzyjmy się teraz wszystkim wartościom sprzedaży powyżej 40 zł. Kliknij na strzałce w kolumnie Sprzedaż i wybierz z listy pozycję Inne.

8. Otworzy się okno dialogowe, w którym wciśnij strzałkę w polu z wyświetlonym hasłem "równa się". Z listy, która się pojawi, wybierz hasło "jest większe niż". W polu sąsiednim (po prawej stronie) wpisz liczbę 40, tak jak na ilustracji obok. Wciśnij przycisk OK. Teraz zostaną wyświetlone wszystkie transakcje powyżej 40 zł.

9. Jeśli chciałbyś zrezygnować z Autofiltra, możesz go po prostu wyłączyć. W tym celu z menu Dane wybierz pozycję Filtr, a następnie pozycję Autofiltr.

Sortowanie danych

Autofiltr pozwala uzyskać wspaniałą przejrzystość danych, ale możesz je również posortować w prosty sposób. Pamiętaj o jednym: nie zaznaczaj kolumn, a jedynie umieść kursor w dowolnym miejscu tej kolumny, według której chcesz posortować dane.

Spróbuj sam:

1. Zacznijmy od alfabetycznego posortowania danych według nazwisk sprzedawców. Umieść kursor w dowolnej komórce, w którą wpisane jest nazwisko sprzedawcy, np. Chowanec.

2. Kliknij na przycisku Sortuj rosnąco i gotowe.

3. A co byś powiedział na posortowanie według daty w porządku malejącym? Umieść kursor w kolumnie z datami. Kliknij na przycisku narzędzia Sortuj malejąco.

4. Posortujesz teraz według daty w porządku malejącym, a rekordy z tą samą datą według produktu w rosnącym porządku alfabetycznym. Wybierz z menu Dane pozycję Sortuj.

5. Wypełnij pola okna dialogowego tak, jak pokazano na ilustracji. W pierwszym polu umieść hasło Data i zaznacz znajdujące się po prawej stronie pole wyboru Malejąco. W następnym - hasło Produkt i zaznacz pole Rosnąco. U dołu okna dialogowego kliknij w polu Lista opcję "Ma wiersz nagłówka". Kliknij na przycisku OK.

Sumy pośrednie

Sumy pośrednie informują nas o całkowitej sprzedaży np. z danego dnia, danego produktu, każdego ze sprzedawców. Sumy pośrednie dla każdej daty możesz uzyskać tylko wtedy, gdy lista jest posortowana według daty. Innymi słowy - powinieneś zacząć od posortowania danych - a reszta zrobi się prawie sama!

Spróbuj sam:

1. Umieść kursor w dowolnym miejscu kolumny Data i kliknij na przycisku narzędzia Sortuj rosnąco.

2. Z menu Dane wybierz pozycję Sumy pośrednie.

3. W polu "Dla każdej zmiany w:" wybierz hasło Data (powinno być już wybrane).

4. W polu "Użyj funkcji" wybierz hasło suma.

5. W polu "Dodaj sumę pośrednią do" zaznacz pozycję Sprzedaż. Znajdziesz tu pasek przewijania, którym możesz przewinąć listę pozycji w górę, by sprawdzić, czy żadna inna opcja nie jest zaznaczona.

6. Wciśnij OK. Lista wygląda teraz zupełnie inaczej. Po pierwsze, pojawiły się sumy pośrednie i suma końcowa, po drugie zmieniła się forma listy - po lewej stronie widzisz przyciski wskazujące na formę konspektu. Kliknij po kolei na przyciskach z symbolem minusa. Co się dzieje? Lista jest zwijana tak, że widoczne są tylko sumy pośrednie. Klikaj po kolei na przyciskach z symbolem plusa, żeby ponownie rozwinąć listę.

7. Sum pośrednich pozbędziesz się w następujący sposób: z menu Dane wybierz pozycję Sumy pośrednie, a następnie wciśnij "Usuń wszystko" w oknie dialogowym.

Zamrażanie tytułów w przypadku wielu danych

Na razie do systemu wprowadzono niewiele danych. Jednak w krótkim czasie może przybyć ich tak wiele, że lista stanie się nieprzejrzysta. Kiedy przewijasz w dół długie listy, tracisz z pola widzenia tytuły kolumn, co może być bardzo irytujące. Jeśli jednak je zamrozisz (zablokujesz) to masz ten problem z głowy!

Spróbuj sam:

1. Ustaw kursor w komórce A2. Zawsze blokowane są wiersze znajdujące się nad komórką, w której umieszczony jest kursor i kolumny po jej lewej stronie. Jeśli jednak kursor znajduje się w kolumnie A, to zablokowany zostaje tylko wiersz znajdujący się nad komórką z kursorem - w naszym przypadku wiersz 1.

2. Z menu Okno wybierz pozycję Zablokuj okienka.

3. Między wierszami 1 i 2 pojawiła się linia. Za pomocą klawisza PageDown przewiń arkusz w dół. W trakcie przewijania dotarłeś do pustych obszarów arkusza, ale tytuły nadal są widoczne. Przewiń arkusz w górę klawiszem PageUp.

4. Odblokuj wiersz, wybierając z menu Okno pozycję Odblokuj okienka.

Statystyka za pomocą tabeli przestawnej

Tabela przestawna ułatwia życie, kiedy potrzebujesz szybkiego zestawienia tego co, kto i kiedy sprzedał.

Przykład:

Statystyka sprzedaży

Po utworzeniu tabeli przestawnej, będziesz mógł np. zobaczyć za jaką kwotę, 3 sierpnia 1998, każdy ze sprzedawców sprzedał produkty danego rodzaju.

Spróbuj sam:

1. Umieść kursor w dowolnym miejscu listy, w którym znajdują się dane.

2. Z menu Dane wybierz pozycję Raport tabeli przestawnej. Uruchomiony zostanie Kreator tabel przestawnych.

3. Kliknij na przycisku Dalej, a w następnym oknie należy podać lub zaznaczyć myszą obszar w arkuszu zawierającym dane. Wciśnij przycisk Dalej.

4. Tabelę tworzy się poprzez wybór danych, które mają być zestawione. Element "Produkt" przeciągnij myszą do obszaru o nazwie Kolumna. Pozycję Sprzedawca przeciągnij w pole o nazwie Wiersz, a Sprzedaż przeciągnij do pola Dane (spowoduje to zmianę tego elementu na Suma: Sprzedaż). Kliknij na przycisku Zakończ.

5. W ten sposób została utworzona tabela przestawna. Zwróć uwagę na znajdujące się u dołu okna Excela zakładki arkuszy - dla potrzeb tabeli stworzony został całkiem nowy arkusz.

6. Tabelę możesz jeszcze poprawić. Z menu Dane wybierz pozycję Raport tabeli przestawnej (kursor powinien być umieszczony w jednej z komórek tabeli). Wrócisz w ten sposób do Kreatora tabel przestawnych i do zestawienia, które zrobiłeś przed chwilą.

7. Element Data przeciągnij myszką na pole Strona i kliknij na przycisku Zakończ.

8. Teraz możesz oglądać rekordy sprzedaży dla danego dnia albo po prostu wszystkie rekordy, niezależnie od daty sprzedaży Kliknij na strzałce znajdującej się w polu obok pola o nazwie Data i sam wypróbuj możliwości tabeli.

Zadanie:

Utwórz w nowym arkuszu kartotekę klientów firmy. Użyj formularza do wprowadzania rekordów. Spróbuj za pomocą Autofiltra znaleźć np. wszystkich klientów o danym kodzie pocztowym. Za pomocą formularza wyszukaj określonego klienta. Zakończ zadanie, sortując klientów według nazwy firmy, której są reprezentantami.

 

 

 

Aby:	Należy:
Założyć bazę danych	W pustym arkuszu, w komórki A1, B1, C1 itd. wpisać tytuły kolumn. Nazywając je używaj pojedynczych wyrazów i unikaj pustych kolumn.
Używać formularza	Umieścić kursor w komórce, w której są dane i z menu Dane wybrać pozycję Formularz. Kliknij na przycisku Nowy. Wpisz dane i wciśnij klawisz Tab, by przejść do następnego pola.
Szukać przy użyciu formularza	W oknie formularza kliknąć na przycisku Kryteria. Wpisz w jedno lub więcej pól kryteria wyszukiwania. Wciskaj przyciski Znajdź następny i Znajdź poprzedni, by odszukać dane spełniające określone wcześniej wymagania.
Używać Autofiltra	Umieścić kursor w komórce, w której znajdują się dane. Z menu Dane wybierz pozycję Filtr, a następnie pozycję Autofiltr. Wciskaj znajdujące się przy tytułach kolumn przyciski, by określić kryteria, według których mają być filtrowane. Wyłącz Autofiltr, wybierając ponownie z menu Dane opcję Filtr, a następnie pozycję Autofiltr.
Sortować dane listy	Umieścić kursor w kolumnie, według której trzeba posortować dane. Kliknij na przycisku Sortuj rosnąco (lub Sortuj malejąco, jeśli chcesz posortować dane według porządku malejącego).
Wstawić Sumy pośrednie	Posortować listę według wybranego kryterium w porządku rosnącym, a potem wybrać z menu Dane pozycję Sumy pośrednie.
Zamrozić (zablokować) tytuły	Aby zablokować wiersz 1, umieścić kursor w ko- mórce A2. Z menu Okno wybierz pozycję Zablo- kuj okienka. Odblokuj wiersz, wybierając opcję Odblokuj okienka w menu Okno. Zeby zablokować kolumnę A, umieść kursor w komórce B1, następnie wybierz w menu Okno pozycję Zablokuj okienka.
Stworzyć tabelę	Umieścić kursor w dowolnej komórce zawierającej dane. Z menu Dane wybierz pozycję Raport tabeli przestawnej i wykonuj poszczególne kroki Kreatora.