TECHNOLOGIA INFORMACYJNA  laboratorium
arkusz kalkulacyjny: MS Excel
Legenda:
aplikacja, program, arkusz
opcja menu, funkcja
Ćwiczenia cz. 5
plik, folder, ścieżka
pole, kod pola
tekst do wpisania
1 Wprowadzenie do ćwiczeń
Pobierz ze strony WWW przedmiotu skoroszyt agencja_reklamowa.xls.
Skoroszyt zawiera arkusz z fikcyjnymi danymi projektów realizowanych przez agencję reklamową dla
wybranych klientów. Dla każdego projektu przechowywane są następujące dane:
" numer projektu,
" klient,
" wartość zamówienia,
" branża, do której należy klient lub produkt,
" medium, w jakim ukazała się lub ma ukazać się reklama,
" kierownik projektu,
" status projektu.
Poniższy zestaw ćwiczeń służy zapoznaniu się z wybranymi metodami ułatwiającymi wprowadzanie
danych do tabel oraz podstawowymi możliwościami narzędzia analitycznego, jakim są tabele
przestawne. Na końcu zestawu znajdują się zadania służące poznaniu funkcji
WYSZUKAJ.PIONOWO.
2 Sprawdzanie poprawności
2.1 Lista oparta na zawartości kolumny
Excel pozwala tworzyć listę rozwijalną na podstawie danych zawartych w wybranej kolumnie.
1. W arkuszu dane fikcyjne otwartego skoroszytu
w komórce D21 brakuje informacji o branży
klienta.
2. Rozwiń menu kontekstowe dla komórki D21
i wskaż opcję Wybierz z listy rozwijanej&
3. Excel wyświetli listę rozwijaną zawierającą
wszystkie unikatowe wartości komórek znalezione
w bieżącej kolumnie (za wyjątkiem wartości
komórki D1, która jest nagłówkiem tabeli danych).
4. Z listy wybierz pozycję  spożywcza .
5. Sprawdz
, jak funkcja listy rozwijanej działa
w innych kolumnach arkusza.
Podobną funkcją stosowaną przez Excel jest
autouzupełnianie danych: podczas wpisywania
zawartości do komórki aplikacja stara się  dokończyć
ciąg znaków sugerując identycznie zaczynający się
tekst, który już wcześniej został wpisany w komórki
bieżącej kolumny.
Autouzupełnianie można wyłączyć w oknie menu
Opcje programu Excel Zaawansowane Opcje
edycji.
1/4
ST.E
TECHNOLOGIA INFORMACYJNA  laboratorium
arkusz kalkulacyjny: MS Excel
2.2 Lista funkcji Sprawdzanie poprawności
Excel pozwala na stosowanie w komórkach list rozwijanych, dla których kafeteria (tj. warianty)
odpowiedzi jest zdefiniowana w innym miejscu skoroszytu. Taką funkcję zastosowano dla kolumny
Media arkusza dane fikcyjne.
Twoim zadaniem jest stworzenie podobnej funkcjonalności tabeli dla kolumny zawierającej statusy
projektów. Oto sposób:
1. Przejdz
do arkusza listy.
2. W komórce B1 wpisz Status (nie jest to niezbędne, ale ułatwia orientowanie się w przeznaczeniu
komórek).
3. W komórkach B2:B4 wpisz kolejno następujące typy statusów:
" przetarg
" w realizacji
" zrealizowane
4. Zakresowi komórek B2:B4 nadaj nazwę status (funkcja, której użyjemy, nie pozwala
bezpośrednio odwoływać się do komórek z innego arkusza, ale można to ograniczenie obejść
nadając komórkom nazwę).
5. Wróć do arkusza dane fikcyjne i zaznacz tam całą kolumnę G.
6. Ze wstęgi Dane w sekcji Narzędzia danych wybierz Poprawność danych&
7. W zakładce Ustawienia z listy Dozwolone wybierz Lista.
8. W polu y
ródło, w którym podaje się zakres komórek, gdzie znajduje się kafeteria listy wpisz
=status (jest to odwołanie do zakresu komórek utworzonego w punkcie 4.).
9. Kliknij przycisk OK i sprawdz
, czy listy rozwijalne z odpowiednimi wariantami są dostępne we
w komórkach kolumny G.
10. Żeby pozbyć się listy rozwijalnej z komórki G1 zaznacz ją i w Sprawdzaniu poprawności ustaw, że
dozwolona jest tam dowolna wartość.
11. Zmień status projektu nr 30 z przetarg na w realizacji.
2/4
ST.E
TECHNOLOGIA INFORMACYJNA  laboratorium
arkusz kalkulacyjny: MS Excel
3 Tabela przestawna
3.1 Opis
Za pomocą raportu tabeli przestawnej można podsumowywać, analizować, przeglądać i przedstawiać
dane podsumowania. Za pomocą raportu wykresu przestawnego można graficznie przedstawiać dane
podsumowania zawarte w raportach tabel przestawnych oraz przedstawiać czytelne porównania,
wzorce i trendy. Raporty tabeli przestawnej i raporty wykresu przestawnego umożliwiają
podejmowanie bardziej świadomych decyzji dotyczących istotnych danych w przedsiębiorstwie.
Raport tabeli przestawnej oferuje interakcyjny sposób szybkiego podsumowywania dużych ilości
danych. Raport tabeli przestawnej umożliwia szczegółowe analizowanie danych liczbowych
i uzyskiwanie odpowiedzi na nieprzewidziane pytania dotyczące tych danych. Główne zastosowania
raportu tabeli przestawnej są następujące:
" tworzenie kwerend (zapytań) dotyczących dużych ilości danych na wiele przyjaznych dla
użytkownika sposobów;
" częściowe podsumowywanie i gromadzenie danych liczbowych, podsumowywanie danych według
kategorii i podkategorii oraz tworzenie niestandardowych obliczeń i formuł;
" rozwijanie i zwijanie poziomów danych w celu sprecyzowania uzyskanych wyników oraz
przechodzenie do szczegółów danych podsumowania w celu analizowania interesujących
użytkownika obszarów;
" przenoszenie wierszy do kolumn lub kolumn do wierszy (czyli  przestawianie ) w celu uzyskania
różnych podsumowań danych z
ródłowych;
" filtrowanie, sortowanie, grupowanie i warunkowe formatowanie najbardziej użytecznych
i interesujących podzbiorów danych, umożliwiające użytkownikowi skoncentrowanie się na
żądanych informacjach;
" prezentowanie zwięzłych, atrakcyjnych i opatrzonych adnotacjami raportów w trybie online lub
wydrukowanych.
Raporty tabeli przestawnej są często używane do analizowania powiązanych podsumowań, zwłaszcza
wówczas, gdy jest konieczne podsumowanie długiej listy wartości i porównanie kilku aspektów
poszczególnych wartości.
3.2 Polecenia
Wykorzystując dane z arkusza dane fikcyjne, stwórz w osobnych arkuszach otwartego skoroszytu
następujące tabele przestawne (Wstawianie Tabela przestawna):
1. Tabela, która pozwala wybrać kierownika projektu i zobaczyć, jaką wartość mają projekty
o określonym statusie dla poszczególnych klientów obsługiwanych przez tego kierownika wraz
z podsumowaniem kwot dla każdego z klientów oraz każdego typu statusu.
2. Tabela, która pozwala wybrać klienta i zobaczyć, jaką wartość mają projekty o określonym
statusie w ujęciu pokazującym typy mediów, w jakich ukazały lub mają się ukazać reklamy dla
3/4
ST.E
TECHNOLOGIA INFORMACYJNA  laboratorium
arkusz kalkulacyjny: MS Excel
wskazanego klienta. Na podstawie tej tabeli każ Excelowi narysować wykres przestawny. Jakie
opcje udostępnia taki wykres?
3. Tabela, która pozwala wybrać typ statusu projektu i ukazuje, ile projektów o danym statusie
znajduje się w bazie. Przy czym pokazane jest, jakie branże wybierają jakie media reklamowe.
4. Tabela, w której można zobaczyć, ile projektów i dla jakich branż prowadzi lub prowadził każdy
z kierowników. Tabela pozwala jednocześnie przełączać dane według różnych typów mediów.
4 Funkcja WYSZUKAJ.PIONOWO
Na podstawie danych z arkusza zatrudnieni stwórz wyszukiwarkę, która po wpisaniu numeru ID
pozwoli uzyskać nazwisko, płacę i wiek zatrudnionego. Skorzystaj z żółtych pól arkusza zatrudnieni.
Następnie stwórz taką wyszukiwarkę, która po wpisaniu nazwiska pozwoli uzyskać pełną informację
na temat pracownika. Skorzystaj z zielonych pól arkusza zatrudnieni. Jeśli uważasz, że jest taka
potrzeba, utwórz kopię listy pracowników i zmodyfikuj ją tak, by funkcja WYSZUKAJ.PIONOWO
działała. Pamiętaj, że funkcja poszukuje wartości pierwszego argumentu w pierwszej kolumnie
zakresu komórek (tabeli) podanego jako drugi argument.
5 Literatura
" Sprawdzanie poprawności danych: http://office.microsoft.com/pl-pl/excel/CH100648501045.aspx
" Raporty tabel przestawnych i wykresów przestawnych:
" http://office.microsoft.com/pl-pl/excel/CH100648481045.aspx
" http://office.microsoft.com/training/training.aspx?AssetID=RC102058721045
" http://office.microsoft.com/training/training.aspx?AssetID=RC102080371045
" http://office.microsoft.com/training/training.aspx?AssetID=RC102102791045
" Tabele: http://office.microsoft.com/pl-pl/excel/HA100485461045.aspx
" Funkcja WYSZUKAJ.PIONOWO: http://office.microsoft.com/pl-pl/excel/HP100698351045.aspx
4/4
ST.E