Excel jako baza danych ?

Excel jako baza danych ?

• Mimo, że Excel jest „stworzony” do

wykonywania zadań obliczeniowych to
organizacja

danych

w postaci układu wierszy i kolumn ułatwia
nie tylko przechowywanie list danych ( baz
danych ), ale również ich przetwarzanie i
prezentację

• Zasadniczo zaleca się stosowanie tego

programu dla list składających się, z co
najwyżej , 2000 wierszy. Powyżej tej liczby
zaleca

się

stosowanie

programów

wyspecjalizowanych

w

bezpiecznym

przechowywaniu

i

udostępnianiu

baz

danych.

• Praktycznie wszystkie polecenia

umożliwiające
przetwarzanie list zgrupowane są w menu
Dane.

Organizacja arkuszy - list

Organizacja arkuszy - list

• Aby móc efektywnie wykorzystywać wszystkie dostępne narzędzia w

Excelu, już na etapie tworzenia listy należy przewidzieć właściwą
organizację danych w kolumnach i wierszach.

• Najistotniejszą częścią procesu tworzenia tabeli jest określenie

struktury danych, ponieważ określa to możliwość późniejszego ich
przetwarzania. Podstawą jest określenie , jakie dane będziemy
pobierali z listy i w jakiej kolejności.Tworząc listę musimy patrzeć na
nią w terminach konspektu – należy porządkować dane w układzie
hierarchicznym, od najbardziej do najmniej istotnych.

• Aby sprawdzić, czy utworzona lista ma właściwą strukturę, można ją

dać do oceny przyszłemu użytkownikowi. Jeśli jest dobrze
skonstruowana, powinien on zrozumieć jej logikę bez pomocy z
zewnątrz.

• Istotny jest

całkowity zakaz tworzenia w liście pustych wierszy

–

na przykład w celu rozdzielenia grup danych. Jeżeli jedna musimy z
jakichś powodów wyróżnić grupy danych, to można to zrobić,
zmieniając wysokość wiersza lub jego format.

Anatomia listy

Anatomia listy

Wiersz
nagłówka

–

zawiera

nazwy

kolumn ( pól )
listy. Wprawdzie
nie

jest

bezwzględnie
wymagany,

ale

przy jego braku
wiele opcji będzie
pracowało źle lub
wcale.

Rekord - komórki

danych tworzące

pojedynczy wiersz

Pole danych –

każda komórka

przechowująca

dane

Nazwa pola – opisuje
rodzaj
( kategorię danych ).
Zwykle pozycjonowana jest
jako nagłówek kolumny.

Importowanie danych 1/2

Importowanie danych 1/2

• Często ściąga się dane z głównego komputera w firmie do pliku tekstowego

( trudno nawet przyjąć, że wszystkie aplikacje mogą zapisywać dane w
formacie Excela ), aby następnie poddać je analizie za pomocą Excela.

• Plik tekstowy nie musi zawierać żadnego formatowania i można go otwierać za

pomocą najprostszych edytorów tekstu.

Otwórz plik testowy sprzedaż.txt . Plik
ten to przykład pliku z danymi
rozdzielonymi przecinkami. Czasem
takie pliki nazywane są plikami CSV.
Czasami dane mogą być rozdzielone
tabulatorami. Stosuje się również pliki
o stałej długości pola w każdym
rekordzie.
Aby ujrzeć go w oknie dialogowym
Otwórz , będziesz musiał zmienić
zawartość pola Pliki na Pliki Tekstowe
Excel

otworzy

Kreatora

importu

tekstu. Zauważ, że musimy zmienić
wartość w polu Zacznij import od
wiersza na 2, gdyż nie potrzebujemy
wiersza nagłówkowego.

Importowanie danych 2/2

Importowanie danych 2/2

Ćwiczenie c.d:

• W kroku2 określamy, że

separator to przecinek.
Kliknij przycisk Dalej.

• W kroku 3 można określić

formaty
poszczególnych pól. Nie musimy
wprowadzać zmian, więc kliknij
Zakończ.

Zastosowanie standardowych

formularzy

Zastosowanie standardowych

formularzy

Wypełniając tabele danymi, a następnie wykonując na wpisanych danych różne

operacje możemy zrezygnować z „klasycznych” metod wpisywania komórka –
po komórce i ułatwić sobie pracę, wykorzystując polecenie Dane / Formularz.

Wskazując dowolną komórkę w
tabeli oraz wybierając polecenie
Dane / Formularz, powodujemy
automatyczną

generację

formularza, którego pola opisane
są

przez

wykryte nagłówki tabeli.
Jeżeli

rekordy

zawierają

powtarzające się dane, to aby nie
wpisywać

wielokrotnie

tych

samych

danych,

można

wykorzystać

opcję

Autouzupełnianie po wybraniu
Narzędzia / Opcje

Zarządzanie listami - sortowanie

Zarządzanie listami - sortowanie

Zarządzanie listami to zbiór czynności mających na celu porządkowanie listy
( sortowanie i grupowanie), wyszukiwanie danych ( według ustalonych
kryteriów ) oraz wykonywanie obliczeń na zawartościach list.

SORTOWANIE LIST

Tworząc listę, często nie mamy

możliwości ( lub chęci ) porządkowania
danych w chwili ich wpisywania. Łatwo
uporządkować je później – program zrobi
to „w mgnieniu oka” o ile odpowiednio go
„poinstruujemy”.

Sortowanie to nic innego jak

ustawianie rekordów bazy w określonym
porządku. Porządkowanie jest wykonywane
zawsze według klucza wybranego pola
( kolumny)

Zarządzanie listami - sortowanie

Zarządzanie listami - sortowanie

Ćwiczenie:

• Otwórz arkusz klienci ze skoroszytu listy.xls.

Przejdź do dowolnej komórki i wydaj polecenie
Dane / Sortuj

• Wybierz sortowanie według nazwiska klienta,

anastępnie wg miejscowości.

• Sprawdź czy wiersz nagłówkowy został wyłączony

z zakresu sortowanego.

• Naciśnij OK.

Po wydaniu polecenia Dane / Sortuj zakres bazy

został ustalony automatycznie.

Gdyby wśród rekordów znalazł się jeden pusty,

Excel uznałby go za granicę bazy.

Zarządzanie listami - sortowanie

Zarządzanie listami - sortowanie

Ćwiczenie cd. :

• Excel pozwala sortować maksymalnie według trzech kluczy
• Sortowanie według drugiego klucza jest podrzędne wobec sortowania

według klucza pierwszego, co w przykładzie oznacza uporządkowanie
rekordów najpierw wg nazwisk. Dopiero rekordy osób o tym samym
nazwisku są sortowane wg miejscowości.

• Zwróć uwagę na pierwszą kolumnę. Po wykonaniu sortowania

( jednego lub wielu) porządek rekordów nie będzie miał nic wspólnego
z pierwotnym. Sortując wg pierwszej kolumny, będziesz mógł
przywrócić początkową postać bazy.

• Opcja Rosnąco / Malejąco - umożliwia wybór porządku sortowania: w

przypadku rosnącego ( A do Z i ) do 9 ) lub malejącego ( Z do A lub 9
do 0).

• Sortowanie odbywa się z uwzględnieniem przeglądania w kierunku od

lewej do prawej, znak po znaku, tzn. najpierw litery ( cyfry ), następnie
spacje, symbole
i ostatnie litery.

Zarządzanie listami – filtrowanie list

Zarządzanie listami – filtrowanie list

Filtrowanie to po prostu wybranie z bazy rekordów spełniających określone warunki np. wybranie
wszystkich osób z Karkowa lub Szczecina mających mniej niż 35 lat.

Ćwiczenie:

Z bazy danych wykorzystywanej w poprzednim ćwiczeniu wybierz wszystkie rekordy osób z Warszawy.

Najprostszym rozwiązaniem jest użycie Autofiltra.

• Przejdź do jednej z komórek bazy i wydaj polecenie Dane / Filtr / Autofiltr

• Wybierz opcje Warszawa. Rekordy nie związane z Warszawą

zostaną pominięte.

• W dolnym wierszu stanu pojawi się wynik zliczania rekordów.

Ponowne wydanie polecenia Dane / Filtr / Autofiltr wyłącza autofiltr.

Zarządzanie listami –

– filtrowanie list z użyciem Filtru

Zaawansowanego

Zarządzanie listami –

– filtrowanie list z użyciem Filtru

Zaawansowanego

Ćwiczenie:

Z bazy danych wykorzystywanej w poprzednim ćwiczeniu wybierz wszystkie rekordy osób
z Warszawy, których wybór

brzmi „tak”

Należy zastosować Filtr zaawansowany.
W dowolnym zakresie komórek poza bazą np.
J2:K3, wpisz kryteria sortowania.
Przejdź do jednej z komórek bazy i wydaj
polecenie Dane / Filtr / Filtr Zaawansowany W
oknie dialogowym Zaawansowany filtr wybierz
opcje

i

wypełnij

pola

zgodnie z rysunkiem i włącz sortowanie.
Nagłówki kryteriów muszą być dokładnie takie
same jak nagłówki kolumn komórkach wiersza
nagłówkowego bazy danych.
Najbezpieczniejszym

rozwiązaniem

jest

kopiowanie zawartości komórek.
Ważne jest, żeby miedzy zakresem kryteriów, a
listą znajdował się przynajmniej jeden pusty
wiersz.
Aby ponownie wyświetlić wszystkie rekordy,
wydaj
polecenie Dane / Filtr / Pokaż wszystko

Grupowanie danych i tworzenie

konspektów

Grupowanie danych i tworzenie

konspektów

DWIE WAŻNE METODY

ZARZĄDZANIA DANYMI.

Grupowanie danych

Tworzenie konspektów

Grupowanie danych i tworzenie

konspektów

Grupowanie danych i tworzenie

konspektów

• Można grupować zarówno wiersze, jak i kolumny.
• Grupowanie różni się od tworzenia konspektu tym, że grupowanie

może być definiowane na dowolną „głębokość” danych oraz pozwala
na ich wyświetlanie i ukrywanie, natomiast konspekt jest oparty na
strukturalizowanych

listach

danych

i tabelach zawierających wstępne podsumowania.

• Wszystkie opcje związane z grupowaniem i tworzeniem konspektu

udostępnione są w menu Dane / Grupy i konspekt. Kolejny poziom
podmenu udostępnia listę poleceń pozwalających na tworzenie grup i
konspektów oraz zarządzanie nimi. W przypadku, gdy często
korzystamy z tych narzędzi możemy sami utworzyć odpowiedni pasek
narzędzi.

Operacje na danych –

- grupowanie danych

Operacje na danych –

- grupowanie danych

• Excel umożliwia grupowanie danych posiadających wspólne atrybuty w celu zwiększenia ich czytelności.

• Grupowanie danych umożliwia tworzenie potrzebnych kombinacji wierszy i kolumn przez udostępnienie

możliwości ukrywania odpowiednich grup, za pomocą pojedynczego kliknięcia myszy

• Grupowanie może odbywać się automatycznie, gdy lista zawiera wiersze podsumowań lub ręcznie, gdy ich

nie ma.

Ćwiczenie:

Utwórz ręcznie grupę klientów, którzy nie dokonali wpłat w arkuszu grupy_klientów,
wykorzystując jako kryterium dokonanie wpłat.

• Posortuje bazę wg wpłat

• Zaznacz rekordy przeznaczone do grupowania

• Wybierz Dane / Grupy i konspekt / Grupuj

• Na lewo od listy pojawia się się pole zawierające „klamry”. Kliknięcie na przycisk z
symbole „-” powoduje ukrycie odpowiedniej grupy danych, a na „+” jej odkrycie.

Aby usunąć grupę, należy wybrać Dane / Grupy i konspekt / Rozgrupuj

Operacje na danych –

- grupowanie danych z wykorzystaniem

Autokonspektu

Operacje na danych –

- grupowanie danych z wykorzystaniem

Autokonspektu

Ćwiczenie:

• Otwórz arkusz autokonspekt ze skoroszytu

lista zawierający listy z podsumowaniem
danych.

• Zaznacz obszar danych, które zamierzamy

grupować

• Wybierz Dane / Grupy i konspekt /

Autokonspekt

• Autokonspekt automatycznie rozpoznaje

strukturę listy danych.
Grupuje dane w oparciu o znajdujące się w
tabeli podsumowania( w tabeli pojawiły się
dwie klamry).

• Aby usunąć grupy wybierz Dane / Grupy i

konspekt/ Wyczyść konspekt

Wyższa szkoła jazdy, czyli tabele

przestawne

Wyższa szkoła jazdy, czyli tabele

przestawne

•

Tabele przestawne to niezwykle użyteczna

technika analityczna i prezentacyjna Excela, która z
"płaskich"

tabel

potrafi

wydobyć

użyteczne

informacje.

• Jest także zaskakująco łatwa - jej opanowanie

wymaga jedynie paru godzin pracy.

Na początek surowa tabela

Na początek surowa tabela

• Co tak naprawdę

daje

nam

takie

zwykłe

ujęcie?

W

gruncie

rzeczy

wiemy z tej tabeli
tylko tyle, że w danym
roku i danym kwartale
sprzedaż konkretnego
modelu samochodu w
danym

mieście

wyniosła

tyle

i tyle sztuk, miała
taką
i taką wartość.

Chcemy czegoś więcej

Chcemy czegoś więcej

• Co jednak zrobić, gdy chcemy się szybko dowiedzieć, jaka była

kwartalna dynamika sprzedaży Punto w Łodzi w kolejnych latach
albo roczna dynamika sprzedaży Seicento w Warszawie?

• Jak porównać informacje o sprzedaży w obu miastach?
• Jak pokazać sprzedaż w kolejnych kwartałach w obu miastach

jednocześnie?

Przecież tabela jest w gruncie rzeczy wielowymiarowa, a jej
"płaskie" ujęcie w oryginalnym arkuszu nie pozwala
zorientować

się

w rozmaitych aspektach sprzedaży, gdyż ludzki umysł nie
jest
w stanie w precyzyjny sposób wyselekcjonować i ułożyć
informacje.

Szkielet tabeli przestawnej czeka

na wprowadzenie pól

Szkielet tabeli przestawnej czeka

na wprowadzenie pól

Pierwsza próba

Pierwsza próba

Przypuśćmy, że chcesz się
dowiedzieć, jak kształtuje się
sprzedaż

samochodów

w

kolejnych latach w sztukach.
W oryginalnej tabeli trzeba by
dopiero

podsumować

poszczególne

kwartały

i

ułożyć lata obok siebie.
Tabela przestawna zwolni nas
od tych uciążliwych zabiegów.
Uchwyć myszą pole Rok i
przeciągnij je na obszar Upuść
pola wierszy tutaj. Pole Liczba
przeciągnij

na

Upuść

elementy danych tutaj.
.

Excel ustawił kolejne lata i liczbę sprzedanych samochodów w
poszczególnych latach oraz łącznie za cały okres

W

najprostszym

przykładzie tabeli
przestawnej
użyliśmy jednego
pola wierszy

Modyfikacje

Modyfikacje

Chwytamy myszą pole Rok
w

tabeli

przestawnej

i

wysuwamy je poza obszar
tabeli. Na to samo miejsce z
listy pól przeciągamy pole
Kwartał.
Excel

przedstawił

teraz

sprzedaż

w

podziale

na

kwartały, przy czym są to
oczywiście sumy kwartałów w
kolejnych trzech latach.

W

obszarze

wierszy

możemy

także

umieszczać inne pola z

listy

pól

tabeli

przestawnej

.

Kolejna wariacja

Kolejna wariacja

A gdybyś chciał się dowiedzieć,
jak

wyglądała

sprzedaż

w

poszczególnych kwartałach w
kolejnych latach? Uchwyć myszą
pole Rok i przesuń je na obszar
zajęty teraz przez Kwartał, ale
na lewą jego część, tak aby Rok
poprzedzał Kwartał.
Tym

razem

tabela

została

zbudowana w taki sposób, że
główny

podział

danych

przebiega

według

kryterium

roku,

a

kolejny,

bardziej

szczegółowy

-

według

kwartałów.
W obszarze Elementy danych
widzimy

cały

czas

liczbę

sprzedanych samochodów. Jak
nietrudno

dostrzec,

są

to

wszystko ujęcia, jakie trudno by
było

szybko

wydobyć

z

oryginalnej tabeli.

W bardziej skomplikowanym
przykładzie

do

obszaru

wprowadziliśmy dwa pola
tabeli.

Włączamy kolumny

Włączamy kolumny

W tabeli przestawnej możemy
też

wykorzystać

kolumny,

czyniąc

obraz

bardziej

przejrzystym.

W bardziej skomplikowanym
przykładzie

do

obszaru

wprowadziliśmy dwa pola
tabeli.

Co się (nie) da zrobić ?

Co się (nie) da zrobić ?

•

Jak widać, nowe układy tabeli wydobywają z jej pierwotnej,

surowej postaci te wszystkie aspekty, których nie jesteśmy w
stanie

dostrzec

w oryginalnym układzie.

• Oczywiście, przesuwając pola tabeli na szkielet powinniśmy się

kierować logiką - na przykład nie ma sensu przesuwanie pola
Cena do Elementów danych, gdyż nie ceny są przedmiotem
podziału na lata czy miasta, lecz liczba lub wartość sprzedanych
samochodów.

• Zauważ też, że pola Rok i Kwartał można umieścić w obszarze

kolumn,
a Miasto w obrębie wierszy. Uzyskane dane będą identyczne, ale

w innym układzie.

Filtrowanie wartości

Filtrowanie wartości

Autoformatowanie

Autoformatowanie

Filtrowanie wartości

Filtrowanie wartości

Ten raport wygląda

zdecydowanie

bardziej elegancko od

surowej

tabeli

i

nadaje

się

do

wydrukowania.

Tabela przestawna

Tabela przestawna

•

Tabela przestawna, zwana inaczej tabelą Pivot’a, jest

znakomitym narzędziem ułatwiającym wykonywanie operacji na
danych w postaci tabeli dwuwymiarowej lub trójwymiarowej.

• Raport tabeli przestawnej jest narzędziem interakcyjnym ( po

jego utworzeniu można zmieniać położenie elementów przez
przeciąganie

–

a przez to można zmieniać jego strukturę ), ułatwiającym
szybkie

łączenie

i porównywanie dużej ilości danych. Dzięki temu jest on
narzędziem elastycznym

• Jego kolejną istotną cechą jest możliwość wykonywania obliczeń

na prezentowanych danych oraz możliwość pobierania danych z
różnych źródeł ( pojedyncze i wielokrotne zakresy, zewnętrzne
źródła danych, inne tabele przestawne).

Tabela przestawna

Tabela przestawna

• Istotną cechą tabeli przestawnej jest konieczność odświeżania

tabeli po zmian danych źródłowych.

• Raport tabeli przestawnej tworzymy wyłącznie za pomocą

kreatora – wywoływanego poleceniem Dane / Raport tabeli
przestawnej i wykresu przestawnego.

• Większość opcji związanych z tworzeniem i modyfikacja tabeli

przestawnej zgromadzonych jest w pasku narzędzi Tabela
Przestawna

Tworzenie tabeli przestawnej 1/3

Tworzenie tabeli przestawnej 1/3

Ćwiczenie:

•Otwórz arkusz tabela przestawna

zawierający dane do tworzenia tabeli i
umieść

wskaźnik

w obszarze danych.

•Wybierz Dane / Raport tabeli przestawnej

i wykresu przestawnego.

•Sprawdzamy ustawienia. Klikamy Dalej.

Sprawdzamy prawidłowość wybranego
zakresu danych i jeślizachodzi potrzeba,
korygujemy go.

Tworzenie tabeli przestawnej 2/3

Tworzenie tabeli przestawnej 2/3

Ćwiczenie c.d:

• Ostatnie okno kreatora umożliwia

wybór

lokalizacji

nowej

tabeli przestawnej – zaznaczany
opcję Nowy arkusz i klikamy
Zakończ.

Utworzony

zostaje

(

przed

arkuszem

z bazą) nowy arkusz, zawierający
siatkę projektową tabeli).

• Ostatnim etapem jest utworzenie

tabeli.
W tym celu przeciągamy pola :
Nazwisko – do pól wierszy
Oddział i Wydział - do pól
kolumn
Sprzedaż – do lewego górnego
narożnika tabeli.

Tworzenie tabeli przestawnej 3/3

Tworzenie tabeli przestawnej 3/3

Ćwiczenie c.d :

• W obszarze danych, umieszczamy pole sprzedaż. Zamykamy okno Lista pól

tabeli przestawnej. I mamy już tabelę.

Układ tabeli przestawnej –

- listy rozwijane z polami tabeli.

Układ tabeli przestawnej –

- listy rozwijane z polami tabeli.

Z każdym polem umieszczonym w obszarze wiersza lub kolumny tabeli przestawnej związana jest lista
rozwijana, udostępniająca odpowiednie elementy tabeli źródłowej. Można ukryć wszystkie lub wybrane.
Należy pamiętać, że tak ja ukrywaliśmy pola, możemy je ponownie wyświetlić.

Ćwiczenie :

Do ćwiczenia wykorzystamy wcześniej utworzoną tabelę przestawną.
Usuń z list rozwijanych pól Nazwisko ( pozycje Babacka, Baranowski,
Horodecki), a na liście Oddział pozostaw tylko Andrychów i Gdańsk.

• Rozwiń listę Nazwiska. Odhacz wskazane nazwiska. Nie powinny już

pojawiać się dane związane z nimi.

• Podobnie postąp z listą oddziałów.

• Uzyskujesz już odpowiednio zestawioną tabelę.

Zmiana układu tabeli przestawnej –

- ustawienie pola.

Zmiana układu tabeli przestawnej –

- ustawienie pola.

Tabele przestawne, stworzone w celu zabezpieczenia konkretnych potrzeb, podlegają tym samym
prawom, jak wszystkie obiekty w informatyce użytkowej. Po jakimś czasie trzeba tabele przekonstruować.
W przypadku tabeli przestawnej modyfikacja układu polega na przeciąganiu przycisków w wybrane
miejsca.

Ćwiczenie :

Do ćwiczenia wykorzystamy wcześniej utworzoną tabelę przestawną.
Zmień jej układ zgodnie z poleceniami.

• Wyświetl układ tabeli, wybierając Kreator tabeli przestawnej ...

I wybieramy Układ

• W oknie Kreator tabel i wykresów – układ przeciągamy

odpowiednie przyciski, tworząc układ jak na rysunku.

Zmiana układu tabeli przestawnej

1/2

Zmiana układu tabeli przestawnej

1/2

Ćwiczenie c.d:

• Klikając przycisk OK., powodujemy

wyświetlenie ostatniego
okna, w którym określamy
lokalizację nowej tabeli.

• Jako wynik otrzymujemy tabele

przestawną o postaci

Zmiana układu tabeli przestawnej

2/2

Zmiana układu tabeli przestawnej

2/2

Ćwiczenie c.d:

• Dokonajmy jeszcze jednej modyfikacji tabeli – modyfikacji, której celem jest

przyporządkowanie każdemu pracownikowi odrębnego arkusza, prezentującego
dotyczące go dane. W tym celu należy zmodyfikować układ tabeli, przeciągając
przycisk Nazwisko w obszar Strona.

Tworzenie wykresu tabeli przestawnej

Tworzenie wykresu tabeli przestawnej

Raport wykresu tabeli przestawnej można utworzyć, zaznaczając w pierwszym
oknie Kreatora tabel i wykresów przestawnych opcje raport wykresu
przestawnego.

Jeśli istnieje utworzona wcześniej tabela przestawna, to
Excel zaproponuje, za pomocą okna informacyjnego, możliwość
jej wykorzystania.

Ćwiczenie:
Rozpocznij tworzenie wykresu w
oparciu

o

wykorzystywana

wcześniej

tabelę.

Sprawdź, czy w drugim kroku
dobrze został określony zakres
danych.

Zmiana układu tabeli przestawnej –

- ustawienie pola.

Zmiana układu tabeli przestawnej –

- ustawienie pola.

Ćwiczenie c.d:

• Metodą przeciągania pól skomponuj wykres, jak na rysunku.