TECHNOLOGIA INFORMACYJNA — laboratorium

arkusz kalkulacyjny: MS Excel

1/4

ST.E

Ć

wiczenia cz. 5

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.

Sprawdź, 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.

Legenda:

aplikacja, program, arkusz

opcja menu, funkcja

plik, folder, ścieżka

pole, kod pola

tekst do wpisania

TECHNOLOGIA INFORMACYJNA — laboratorium

arkusz kalkulacyjny: MS Excel

2/4

ST.E

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.

Przejdź 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 Ź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 sprawdź, 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.

TECHNOLOGIA INFORMACYJNA — laboratorium

arkusz kalkulacyjny: MS Excel

3/4

ST.E

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 ź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

TECHNOLOGIA INFORMACYJNA — laboratorium

arkusz kalkulacyjny: MS Excel

4/4

ST.E

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