Microsoft Excel 2000
Baza danych w Excelu. Operacje na listach
Listą w Excelu jest tabela zbudowana z kolumn zaopatrzonych
w nagłówki. Wiersze takiej tabeli stanowią rekordy bazy danych.
Kolumny tabeli są natomiast polami bazy danych. W obszarze listy
nie może być pustych wierszy ani kolumn. Należy powstrzymać
ewentualną skłonność do oddzielania nagłówków tabeli od jej
zawartości pustym wierszem!
Lista jest rozpoznawana jako całość, gdy komórka aktywna
znajduje się gdziekolwiek w jej obrębie. Nie trzeba więc zaznaczać
całej listy by ją posortować, wyszukać w niej dane, wprowadzić sumy
pośrednie, czy utworzyć tabelę przestawną.
Gdy
dopisujemy
nowe
wiersze
do
listy,
następuje
automatyczne rozszerzenie formatowania i skopiowanie formuł, co
bardzo ułatwia pracę.
Uwaga!
Arkusze, z których korzystamy w większości następnych przykładów
i zadań zawierają wiele danych i aby oszczędzić czytelnikowi ich
wpisywania, załączamy je na dyskietce w folderze Zadania.
Filtrowanie danych
Jedną z podstawowych operacji w bazie danych jest
wyszukiwanie danych spełniających określone kryteria, czyli
filtrowanie danych. Do prostego wyszukiwania wystarcza mechanizm
Autofiltru,
który
włączamy poprzez Dane | Filtr | Autofiltr.
- 184 -
Microsoft Excel 2000
Nagłówki kolumn zostają zamienione w rozwijalne listy, z których można wybrać żądane wartości. Jedną z możliwości jest wybór Inne i
zdefiniowanie filtru niestandardowego, np. dla wybrania jednocześnie
dwóch wartości z listy. Powrót do wyświetlania wszystkich danych
uzyskujemy poprzez Dane | Filtr | Pokaż wszystko. Wyłączenie autofiltru następuje poprzez ponowne kliknięcie w Dane | Filtr |
Autofiltr.
Zadanie 41.
Otwórz plik sprzedaż.xls i korzystając z Autofiltru wybierz z załączonej tam listy wiersze dotyczące dzielnicy Stawki, dla których
wysokość sprzedaży przekroczyła 50 000zł.
Zadanie 42.
Z tej samej listy wybrać wiersze dotyczące dzielnic Stawki, Wrzosy i
Bielany i w których sprzedaż przekroczyła 50 000 zł.
Rozwiązanie.
W przypadku tego zadania mechanizm Autofiltru nie wystarcza,
ponieważ chcemy wybrać więcej, niż dwie dzielnice. Możemy za to
skorzystać z Dane | Filtr | Zaawansowany filtr. W takim przypadku
kryteria należy wpisać w wybranym zakresie komórek opierając się na
następujących zasadach:
1. W pierwszym wierszu wpisujemy nagłówki tych kolumn, które
będą wykorzystane w kryteriach,
2. W kolejnych wierszach wpisujemy wartości, które mają być
wybrane lub warunki, jakie mają spełniać (np. <1000),
3. Kryteria wpisane obok siebie w wierszu są połączone logicznym
„I”,
4. Kryteria z różnych wierszy są połączone logicznym „LUB”.
- 185 -
Microsoft Excel 2000
Inne przykłady zaawansowanych kryteriów filtrowania znajdzie
czytelnik w Pomocy.
W naszym zadaniu kryteria wpisujemy np. do komórek G1:H4:
Dzielnica Sprzeda
Stawki
>50000
Wrzosy
>50000
Bielany
>50000
W oknie dialogowym, które pojawia się po wybraniu Dane | Filtr |
Filtr zaawansowany... wskazujemy zakresy danych i kryteriów.
Mamy tam także możliwość zdecydowania, czy dane mają być
filtrowane w miejscu, czy wyniki mają być skopiowane do innej
części arkusza (wystarczy wskazać pierwszą komórkę obszaru
wynikowego).
Zadanie 43.
W oparciu o tabelę utworzoną w Zadaniu 25:
1. dokonać wybrania i przekopiowania w inne miejsce arkusza (od
wiersza nr 50) danych dotyczących ludzi, należących do kategorii
junior i senior,
2. obliczyć poniżej przekopiowanych danych średnią wartość wieku,
3. zobrazować na wykresie wiek pracowników (po uporządkowaniu
według wieku).
Formularze
Dopisywanie danych do długiej listy, czy jej przeglądanie jest
kłopotliwe ze względu na konieczność przewijania arkusza. Dużo
wygodniej jest skorzystać z Formularza wybierając Dane | Formularz
(należy pamiętać o wcześniejszym ustawieniu aktywnej komórki w
obrębie listy).
- 186 -
Microsoft Excel 2000
Dla arkusza „Sprzedaż” formularz wygląda następująco:
Po kliknięciu w przycisk Kryteria można wpisać np. nazwisko
wybranego sprzedawcy w polu Sprzedawca i dalej poprzez Znajdź
poprzedni lub Znajdź następny przeglądać wiersze sprzedawcy o tym nazwisku.
Sumy pośrednie
Po uprzednim posortowaniu listy wg pola grupującego
możemy za pomocą menu Dane | Sumy pośrednie wprowadzić
różnego rodzaju statystyki dla grupy: sumy, średnie, minima itd.
Zadanie 44.
W arkuszu „Sprzedaż” wprowadzić podsumowania sprzedaży dla
każdego sprzedawcy.
- 187 -
Microsoft Excel 2000
Rozwiązanie.
1. Posortować arkusz wg kolumny „Sprzedawca”
2. W oknie dialogowym sum pośrednich wybrać następujące
ustawienia.
W rezultacie otrzymamy poniższe zestawienie:
- 188 -
Microsoft Excel 2000
Uwaga!
1. Wygląd przedstawionego wyżej arkusza nieco się różni od
otrzymanego bezpośrednio po operacji wprowadzenia sum
pośrednich. Po kliknięciu w przycisk - , po lewej stronie
arkusza przy nazwisku Nowak zostały ukryte rekordy
szczegółowe dla tego sprzedawcy. Zwijać i rozwijać listę
można korzystając również z przycisków 1 , 2 , 3 .
2. Można utworzyć wykres używający tylko danych, które są
widoczne. Jeżeli wyświetlimy lub ukryjemy szczegóły listy, to
wykres zostanie również odpowiednio zaktualizowany.
Zadanie 45.
Zastąpić podsumowania z poprzedniego zadania nowymi, w których
otrzymamy sumę sprzedaży dla każdej dzielnicy, a w ramach
dzielnicy dla każdej kategorii.
Uwaga!
1. Sortowanie danych musi być teraz przeprowadzone według dwóch
kluczy: dzielnicy i kategorii.
2. Sumy pośrednie wprowadzamy w dwóch etapach: najpierw dla
dzielnic, potem dla kategorii. Tworząc podsumowania dla
kategorii musimy pamiętać o wyłączeniu w oknie dialogowym
opcji Zamień bieżące sumy pośrednie.
Zadanie 46.
Zachowując sumy pośrednie utworzone w zadaniu poprzednim,
przygotować pod listą tabelkę z nazwiskami sprzedawców oraz łączną
sumą sprzedaży dla każdego z nich. Zastosować funkcję
SUMA.JEŻELI.
- 189 -
Microsoft Excel 2000
Tabele przestawne
Tabele przestawne są znakomitym interaktywnym narzędziem
pozwalającym na zestawianie danych w różnych układach. Już po
utworzeniu tabeli można z łatwością przestawiać w niej wiersze i
kolumny, by uzyskać wygodniejszy układ.
Zadanie 47.
Na podstawie danych z arkusza Sprzedaż utworzyć tabelę przestawną
w postaci:
Suma: Sprzeda
Kategoria
Dzielnica
Art. gospodarstw a dom. Chemia gospodarcza Kosmetyki
Suma całkow ita
Bielany
7200
414910
189980
612090
Staw ki
3800
151890
57390
213080
ródmie cie
250
304220
152950
457420
Wrzosy
5600
100758
181480
287838
Suma całkow ita
16850
971778
581800
1570428
Wskazówki do rozwiązania.
Po ustawieniu kursora w obrębie listy wybierz Dane | Raport
tabeli przestawnej. Zostanie uruchomiony Kreator tabel i
wykresów przestawnych. Pierwsze dwa kroki kreatora są raczej
jasne, zwróćmy uwagę na Krok 3
- 190 -
Microsoft Excel 2000
Mamy tu możliwość wybrania klawisza Zakończ lub Układ.
Każdy z tych wyborów daje nam inne możliwości.
Klawisz Zakończ (zanim go wybierzemy powinniśmy określić
czy dane mają być umieszczone w nowym arkuszu, który zostanie
wstawiony do zeszytu, czy też w którymś z istniejących arkuszy,
poczynając od konkretnej komórki) powoduje wstawienie do zeszytu
nowego arkusza z narysowanymi obszarami Wiersza, Kolumny i
Danych jak na rysunku.
Na pasku narzędziowym tabeli przestawnej znajdujemy
nagłówki kolumn listy zawierającej dane o sprzedaży. Przeciągamy
myszką nagłówek Dzielnica w miejsce przeznaczone na wiersze,
nagłówek Kategoria do obszaru „pola kolumn” zaś nagłówek
Sprzedaż do głównego obszaru danych. Tabela, która powstanie
wygląda dokładnie tak jak żądana.
Druga możliwość tzn. wybranie klawisza Układ powoduje
wyświetlenie okna, w którym określamy podobnie jak poprzednio,
- 191 -
Microsoft Excel 2000
nagłówki wierszy, nagłówki kolumn oraz dane. Ten sposób kreowania
tabeli przestawnej jest taki sam jak w Excelu z wersji Office 97.
Dwukrotne kliknięcie w Suma: Sprzedaż wywołuje okno dialogowe
pozwalające zamiast domyślnej sumy wybrać innego typu agregację
danych. Można też w tym miejscu określić, w jaki sposób i z jaką
dokładnością mają być wyświetlane wartości w tabeli przestawnej.
- 192 -
Microsoft Excel 2000
Warto zwrócić uwagę, że mechanizm tabel przestawnych
cechuje się pewnymi ustawieniami automatycznymi, co powoduje np.
wybranie typu podsumowań w zależności od typu danych w polu
źródłowym. I tak jeśli pole źródłowe jest liczbowe to domyślnie
zostanie wybrane sumowanie, jeśli pole to zawiera teksty to
domyślnie wybrane zostanie zliczanie itd.
W następnym kroku pozostaje tylko zdecydować, czy tabela
ma się znaleźć w nowym arkuszu, czy we wskazanym miejscu
arkusza istniejącego. Przycisk Opcje pozwala w szerokim zakresie
wpływać na ostateczny kształt tabeli:
Uwaga!
Kliknięcie w przycisk obok nagłówka wiersza lub kolumny w tabeli
przestawnej daje możliwość ukrywania wybranych wierszy lub
kolumn.
- 193 -
Microsoft Excel 2000
Zadanie 48.
W tabeli przestawnej z zadania poprzedniego wprowadzić w
kolumnach dodatkowy podział na miesiące.
Uwaga!
1. Wystarczy kliknąć prawym przyciskiem myszy w istniejącą tabelę
przestawną i wybrawszy Kreator (lub wybrać opcję Kreator z klawisza
Tabela
przestawna
na
pasku
narzędziowym),
przeciągnąć pole Miesiąc za pole Kategoria.
2. Tak utworzona tabela jest szeroka i niewygodna do drukowania.
Wygodniejszy układ uzyskamy przeciągając w gotowej tabeli
Dzielnicę w miejsce kolumn, a Kategorię i Miesiąc w miejsce wierszy.
Przycisk Formatuj daje możliwość wyboru jednej z 22 propozycji
wyglądu i ułożenia danych w tabelach przestawnych (nazywanych tu
raportami). Rysunek poniżej pokazuje przybliżony układ sześciu
początkowych formatów raportu. Użytkownik drogą prób powinien
dopasować format do utworzonej tabeli przestawnej tak, aby dane
były łatwe do czytania i interpretacji.
- 194 -
Microsoft Excel 2000
Podgląd wydruku fragmentu gotowego raportu pokazany jest
na poniższym rysunku.
W Excelu 2000 wprowadzono też możliwość tworzenia
wykresów przestawnych. Kliknięcie klawisza Kreator wykresów na
pasku narzędziowym tabeli przestawnej spowoduje wstawienie do
zeszytu arkusza zawierającego wykres utworzony na podstawie
danych z tabeli przestawnej. Przez domniemanie jest to wykres
skumulowany kolumnowy, ale użytkownik może wybrać dowolny
inny typ wykresu aby lepiej zaprezentować dane.
Wykres na poniższym rysunku pokazuje dane z tworzonej w
tym przykładzie tabeli przestawnej z pominięciem dzielnicy Wrzosy
oraz miesiąca lutego. Wyboru danych do prezentacji na wykresie
dokonujemy już po jego utworzeniu posługując się przyciskami u dołu
wykresu oraz przy legendzie.
- 195 -
Microsoft Excel 2000
Zadanie 49.
W tabeli z Zadania 47 utworzyć pole strony do wyboru
sprzedawców, pozwalające na wybór jednego, lub wszyskich
sprzedawców.
Sprzedaw ca
Now ak
Suma: Sprzeda
Dzielnica
Kategoria
Miesic
Bielany
Staw ki
ródmie cie Wrzosy
Suma całkow ita
Art. gospodarstw
1
7200
250
7450
Art. gospodarstw a dom. - Suma
7200
250
7450
Chemia gospodar
1
71130
45000
96620
212750
Uwaga!
- 196 -
Microsoft Excel 2000
Efekt ten uzyskamy przeciągając w oknie Kreatora pole Sprzedawca
jak niżej:
Zadanie 50.
Dla arkusza Sprzedaż przedstawić w tabeli przestawnej udział %
poszczególnych kategorii produktów w sprzedaży dla poszczególnych
dzielnic:
Suma: Sprzeda
Kategoria
Dzielnica
Art. gospodarstw a dom. Chemia gospodarcza
Kosmetyki
Suma całkow ita
Bielany
1,18%
67,79%
31,04%
100,00%
Staw ki
1,78%
71,28%
26,93%
100,00%
ródmie cie
0,05%
66,51%
33,44%
100,00%
Wrzosy
1,95%
35,01%
63,05%
100,00%
Suma całkow ita
1,07%
61,88%
37,05%
100,00%
- 197 -
Microsoft Excel 2000
Uwaga!
1. Zadanie rozwiązuje się identycznie, jak zadanie 47, z
wyjątkiem kroku 3 Kreatora, gdzie w oknie dialogowym "Pole
tabeli przestawnej" trzeba kliknąć w "Opcje>>" i wybrać z listy "% wiersza".
2. Tabele przestawne dają również możliwość tzw. drąż enia
danych. Dwukrotnie klikając w wybrany element danych
uzyskamy w nowym arkuszu zestawienie szczegółowych
danych, które złożyły się na tę wartość. Na przykład klikając
(dwukrotnie) w liczbę obrazującą sprzedaż kosmetyków na
Wrzosach otrzymamy:
Kategoria Miesi c Sprzeda
Sprzedawca Dzielnica
Kosmetyki
4
49530 Nowak
Wrzosy
Kosmetyki
3
26660 Kami ski
Wrzosy
Kosmetyki
2
60810 Kami ski
Wrzosy
Kosmetyki
1
44480 Nowak
Wrzosy
- 198 -
Microsoft Excel 2000
Zadanie 51.
Otworzyć skoroszyt video.xls i rozwiązać następujące zadania:
a) podać liczbę wypożyczeń poszczególnych filmów z podziałem
na kobiety i mężczyzn,
b) podać udział % poszczególnych tytułów we wszystkich
wypożyczeniach kobiet i mężczyzn,
c) dodać kolumnę "Do zapłaty", wyliczyć należności za
wypożyczenia przyjmując stawkę 2 zł za dobę i obliczyć
łączną kwotę do zapłaty dla każdego z klientów,
d) obliczyć przychody z wypożyczeń poszczególnych filmów w
kolejnych miesiącach,
e) dodać kolumnę "Opóźnienia", która ma zawierać wartość
PRAWDA, jeżeli data zwrotu jest późniejsza od terminu
zwrotu, a następnie podać liczbę zwrotów opóźnionych i nie
opóźnionych dla każdego z klientów.
Uwaga!
Rozwiązując zadanie d) należy w tabeli przestawnej zawierającej datę
wypożyczenia zgrupować daty w miesiące. Kliknięcie prawym
klawiszem myszy w "DATA_WYPOŻ" daje dostęp do opcji "Grupy i
konspekt". Dalej po wybraniu "Grupuj" mamy możliwość wyboru przedziału grupowania:
- 199 -