Ocena brak

Baza danych w Excelu. Operacje na listach

Autor /Liliana Dodano /26.03.2011

Wymagany Adobe Flash Player wesja 10.0.0 lub nowsza.

praca w formacie pdf Baza danych w Excelu. Operacje na listach

Transkrypt

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. 50000
>50000
>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
ka dego sprzedawcy.

- 187 -

sprzeda y dla

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:
Dzielnica

 

Suma: Sprzeda

Kategoria
Art. gospodarstw a dom. Chemia gospodarcza Kosmetyki

Suma całkow ita

Bielany

7200

414910

189980

Staw ki

3800

151890

57390

213080

250

304220

152950

457420

5600

100758

181480

287838

16850

971778

581800

1570428

¡

ródmie c ie
¢

Wrzosy

Suma całkow ita

612090

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.

 

Art. gospodarstw

Dzielnica
¡

Kategoria

Miesi c

Bielany
1

Art. gospodarstw a dom. - Suma
Chemia gospodar

1

Staw ki
7200
7200

r ódmie c ie Wrzosy

¢

Suma: Sprzeda

Now ak

£

Sprzedaw ca

250

7450

250
71130

Uwaga!

- 196 -

45000

Suma całkow ita
7450

96620

212750

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:

Dzielnica

 

Suma: Sprzeda

Kategoria
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 c ie
¢

Wrzosy

Suma całkow ita

0,05%

66,51%

33,44%

100,00%

1,95%

35,01%

63,05%

100,00%

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:
¡

- 198 -

Sprzedawca
Nowak
Kami s ki
Kami s ki
Nowak

¢
¢

Kategoria Miesi c Sprzeda
Kosmetyki
4
49530
Kosmetyki
3
26660
Kosmetyki
2
60810
Kosmetyki
1
44480

Dzielnica
Wrzosy
Wrzosy
Wrzosy
Wrzosy

 

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 -

Podobne prace

Do góry