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 -