INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

1/12

ST.IiE

Laboratorium 2

Celem

poniższych ćwiczeń jest zdobycie umiejętności grupowania i prezentowania danych w aplikacji

MS Excel tak, by zwiększyć ich czytelność i przydatność dla osób, które chcą odczytać z arkuszy
istotne dla nich informacje.
W pierwszej części zestawu wykorzystamy do tego celu fikcyjne dane mówiące o sprzedaży

samochodów dziesięciu różnych marek na pięciu rynkach europejskich (krajach) w ciągu 12 miesięcy
(od stycznia do grudnia).

1 Wczytywanie danych

1. Zapisz na dysku plik tekstowy z danymi auta.txt ze strony WWW przedmiotu.

Uruchom aplikację MS Excel 2007.

3. Zaimportuj dane z pliku auta.txt do arkusza Arkusz1 w Excelu.

Niech lewy górny narożnik

importowanych danych znajdzie się w komórce A1 (w dalszej części ćwiczeń zakładamy, że tak

właśnie jest). Podczas importowania upewnij się, że wybrane są następujące opcje:

•

Typ danych źródłowych: Rozdzielany

• Rozpocznij import danych od wiersza: 1
•

Pochodzenie pliku: 1250 : Europa Środkowa (Windows)

• Ograniczniki: Tabulator
• Format danych w kolumnach: Ogólny

Rysunek 1

Importowanie danych zewnętrznych z pliku tekstowego.

Zmień nazwę arkusza Arkusz1 na samochody.

Dla komórek A1:N1, w których znajdują się etykiety (nagłówek) danych, ustaw wypełnienie na
szare.

Rysunek 2

Wyróżnienie komórek nagłówka danych (A1:N1:).

6. Skoroszyt zapisz jako Nazwisko-auta.xlsx

i wykonuj w tym pliku pozostałe zadania

(w miejsce Nazwisko wpisz swoje nazwisko).

Legenda:

aplikacja, program

opcja menu, funkcja

plik, folder, ścieżka

formuła, pole, kod pola

KLAWISZ

tekst do przepisania

miejsce częstych błędów

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

2/12

ST.IiE

2 Formatowanie warunkowe

Formatowanie warunkowe to narzędzie pozwalające dla zaznaczonego zakresu komórek ustalić

reguły związane z wartościami tam wpisanymi, dla których te komórki przybiorą określony wygląd
(kolor desenia, format czcionki).
Formatowanie warunkowe działa dynamicznie, tj. reaguje na zmiany wartości komórek, które nastąpią
po

użyciu tego narzędzia.

Rysunek 3 Formatowanie warunkowe —

reguły wyróżniania komórek.

Narzędzia formatowania warunkowego znajdują się na wstędze

Narzędzia główne w sekcji Style.

W wersji 2007

aplikacji MS Excel znacznie rozbudowano możliwości formatowania warunkowego. Na

początek poznamy najprostsze zastosowania, które dostępne są też w starszych wersjach Excela.

Jeśli popełnisz błąd przy tworzeniu reguły, możesz go naprawić w oknie Menedżera reguł…, który

znajduje się w menu formatowania warunkowego pod hasłem Zarządzaj regułami… (vide: Rysunek 3).

1. Zaznacz komórki za

wierające liczby sprzedanych samochodów (C2:N51). Punkty 2–4 wykonuj

dodając kolejne warunki.

2. Sformatuj

przy użyciu żółtego wypełnienia z ciemnożółtym tekstem (skorzystaj z gotowych

ustawień formatowania) wszystkie zaznaczone komórki, których wartość jest

między 100 000

a 500 000.

3. Sformatuj czc

ionką pogrubioną, czarną na jasnozielonym tle (format niestandardowy) wszystkie

komórki z zakresu C2:N51,

których wartość jest

większa niż 1 000 000. W ten sposób łatwo

zauważyć, gdzie sprzedaż samochodów była najwyższa.

Teraz zobaczymy, że formatowanie warunkowe potrafi też pobierać wartości z komórek: wpisz do

komórki Q2 wartość 10000. Teraz aby łatwo znaleźć najsłabsze wyniki sprzedaży samochodów,

zaznacz komórki C2:N51 i za pomocą formatowania warunkowego sformatuj białą czcionką na
czerwonym tle komó

rki, których wartość jest mniejsza

niż zawartość komórki Q2 (najlepiej

klikając na niej, gdy kursor znajduje się w polu, w którym podaje się wartość, ewentualnie

wpisując tam =$Q$2).

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

3/12

ST.IiE

Efekt działania formatowania warunkowego powinien przypominać to, co pokazuje Rysunek 4.
5.

Zmień wartość w komórce Q2 na inną (np. 5000, 20000) i zobacz, jaki ma to wpływ na
formatowanie warunkowe.

Jeśli efektu nie widać od razu, przełącz się do okna innej aplikacji lub

zminimalizuj okno Excela, a następnie wróć do okna z arkuszem samochody.

Rysunek 4

Efekt działania formatowania warunkowego.

Teraz spróbujemy użyć formatowania warunkowego, którego nie znajdziemy w Excelu starszym niż
wersja 2007.

Najpierw wyczyść istniejące formatowanie warunkowe (Narzędzia główne



Style



Formatowanie warunkowe



Wyczyść reguły



Wyczyść reguły z całego arkusza).

Zaznacz komórki zawierające liczby sprzedanych samochodów (C2:N51).

3. Przejrzyj efekty formatowania warunkowego z grup: Paski danych, Skale kolorów, Zestawy ikon.

Zwróć uwagę, że w każdej z tych grup dostępna jest opcja Więcej reguł…

Gdy skończysz przyglądaniu się efektom, wyczyść formatowanie warunkowe z całego arkusza.

5. Wyc

zyść także zawartość komórki Q2.

6. I nie

smuć się, że efekt Twojej pracy znika — ważne, by pozostała umiejętność korzystania

z formatowania warunkowego.

Jeszcze może Ci się przydać, np. na sprawdzianie.

marka

region sprzedaży styczeń luty

marzec

kwiecień maj

czerwiec lipiec

sierpień wrzesień październik listopad grudzień

Skoda

Niemcy

56000

49958

97632

28024

147590

8921

13382

33900

203

28024

26622

48958

10000

Peugeot

Niemcy

39945

29930

64887

33365

94817

7493

11239

22458

1245

33365

31697

28930

Mazda

Francja

29948

2933

32392

255268

35325

979

1469

2446

3445

255268 242504

1933

Honda

Ukraina

20093

9300

27843

54013

37143

4628

6943

9278

3556

54013

51313

8300

Peugeot

Włochy

3456

90330

78731

5443

169061

43225

53222

345677

3556

5443

5171

89330

Skoda

Austria

29930

20399

46929

36681

67328

6816

10223

17015

4124

36681

34847

19399

Citroen

Francja

95991

93211

173667

25746

266878

9710

14566

56316

6778

25746

24458

92211

Volkswagen

Włochy

39990

35995

69986

27775

105981

9001

13502

26999

9511

27775

26386

34995

Citroen

Niemcy

14506

10029

22864

36160

32893

6914

10371

11928

14706

36160

34352

9029

Ford

Francja

39400

39588

72390

24881

111978

10048

15072

29842

15561

24881

23637

38588

Fiat

Austria

13495

9394

21323

35914

30717

6961

10442

11658

15566

35914

34118

8394

Skoda

Francja

23400

20993

40894

27866

61887

8971

13457

19468

18496

27866

26473

19993

Toyota

Francja

29488

29944

54441

24619

84385

10155

15232

25127

21025

24619

23388

28944

Mazda

Niemcy

69940

93055

147486

18790

240541

13305

19957

59832

23155

18790

17850

92055

Fiat

Francja

30499

39049

63040

19526

102089

12803

19205

32328

33837

19526

18550

38049

Toyota

Austria

94002

59904

143922

39230

203826

6373

9559

36325

34456

39230

37269

58904

Ford

Włochy

30049

9056

37596

82953

46652

3014

4521

7542

34456

82953

78806

8056

Fiat

Ukraina

39950

4955

44079

201564

49034

1240

1860

3718

34467

201564 191486

3955

Peugeot

Ukraina

49930

39000

82430

32006

121430

7811

11716

27311

35567

32006

30406

38000

Peugeot

Francja

29934

39902

63186

18755

103088

13330

19995

33281

36672

18755

17817

38902

Peugeot

Austria

29903

39922

63171

18726

103093

13350

20026

33311

36785

18726

17790

38922

Skoda

Włochy

3990

4599

7823

21689

12422

11526

17289

13826

38652

21689

20605

3599

Ford

Niemcy

34000

49950

75625

17017

125575

14691

22037

39666

42394

17017

16166

48950

Mercedes

Austria

50300

39905

83554

31512

123459

7933

11900

27886

44332

31512

29937

38905

Toyota

Niemcy

78000

33940

106283

57454

140223

4351

6527

21321

45567

57454

54582

32940

Mercedes

Włochy

30028

10085

38432

74437

48517

3359

5038

8401

49499

74437

70715

9085

Mazda

Austria

21200

34532

49977

15348

84509

16289

24433

33555

53076

15348

14581

33532

Ford

Ukraina

30045

4002

33380

187687

37382

1332

1998

3333

54423

187687 178303

3002

Mazda

Włochy

23345

58830

72370

9921

131200

25200

34667

54615

56471

9921

9425

57830

Honda

Włochy

48392

90893

124136

13310

215029

18783

28174

64229

62794

13310

12645

89893

Honda

Austria

20399

39200

53066

13010

92266

19217

28825

38817

66459

13010

12359

38200

Citroen

Ukraina

20301

39885

53539

12725

93424

19647

29470

39589

68405

12725

12088

38885

Volkswagen Ukraina

30040

59902

79958

12537

139860

19941

29911

49892

69704

12537

11910

58902

Volkswagen Austria

19299

39940

52582

12080

92522

20695

31043

40665

73105

12080

11476

38940

Mercedes

Niemcy

76599

48599

117098

39404

165697

6345

9517

30644

76554

39404

37433

47599

Fiat

Włochy

45030

98802

127365

11394

226167

21941

32912

71342

81166

11394

10824

97802

Volkswagen Francja

28830

68933

86274

10456

155207

23910

35865

58377

89322

10456

9933

67933

Ford

Austria

39295

93990

117620

10452

211610

23919

35879

70914

91417

10452

9929

92990

Toyota

Ukraina

38799

93002

116301

10430

209303

23970

35955

70471

91598

10430

9908

92002

Citroen

Włochy

20393

90033

95421

5663

185454

44149

66223

89165

179145

5663

5380

89033

Fiat

Niemcy

12399

58840

61432

5268

120272

47455

71183

76875

183115

5268

5005

57840

Mercedes

Ukraina

20330

98555

102459

5157

201014

48478

72716

97755

198619

5157

4899

97555

Volkswagen Niemcy

5999

34598

34831

4335

69429

57673

86509

74972

213155

4335

4118

33598

Mercedes

Francja

5002

29300

29419

4268

58719

58577

87865

73227

214666

4268

4055

28300

Citroen

Austria

12345

86644

84548

3562

171192

70186

105278 113508

276626

3562

3384

85644

Honda

Francja

2833

39599

35832

1789

75431

139778 209666 159577

506188

1789

1699

38599

Toyota

Włochy

3302

50022

44987

1650

95009

151490 227235 176501

551924

1650

1568

49022

Skoda

Ukraina

3455

95600

83122

904

178722

276700 415051 324500

1012797

904

858

94600

Honda

Niemcy

12453

398488 344526

781

743014

319994 479990

519238

1306769

781

742

397488

Mazda

Ukraina

2003

90002

77005

556

167007

449336

674004

494337

1615674

556

529

89002

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

4/12

ST.IiE

3 Sortowanie

UWAGA!

Sortowanie tylko jednej lub paru kolumn zamiast całej tabeli doprowadzi do tego, że część

danych przemieści się, a część pozostanie na swoim miejscu. Czy tak powinno być? Wyobraź sobie,

do czego doprowadziłoby na liście studentów posortowanie imion, gdyby kolumna z nazwiskami w tym

czasie była wyłączona z obszaru sortowania.

Wykonaj następujące 3

niezależne

sortowania

(to jest każdy punkt 1–3 traktuj jako zadanie

posortowania tabeli A1:N51 nie przejmując się tym, że za każdym razem dane są układane na nowo
—

to tylko ćwiczenie) w arkuszu samochody: Wiersz 1 traktujemy jako nagłówek danych. Jeśli kursor

znajduje się na którejś z komórek z danymi, to włączenie sortowania spowoduje automatyczne

zaznaczenie całego zakresu i wybranie 1. wiersza jako nagłówka.

1. Po

sortuj dane alfabetycznie względem kolumny z nazwą koncernu i regionu sprzedaży.

Rysunek 5

Sortowanie dwupoziomowe wg marki i regionu sprzedaży (dane tekstwowe).

2. Posortuj

malejąco dane wg sprzedaży w lipcu.

Rysunek 6 Sortowanie jednopoziomowe danych liczbowych.

3. Posortuj

rosnąco dane wg sprzedaży w grudniu.

4. Przejd

ź do zadań na kolejnej stronie.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

5/12

ST.IiE

Sumy częściowe (Konspekt)

Program Microsoft Excel może automatycznie obliczyć sumy częściowe (w starszych wersjach: sumy

pośrednie) i końcowe wartości znajdujących się na liście. Podczas wstawiania automatycznych sum

częściowych program Microsoft Excel tworzy konspekt listy, co umożliwia wyświetlanie i ukrywanie

wierszy szczegółów dla każdej sumy częściowej.
Aby wstawić sumy częściowe, należy najpierw posortować listę, tak aby wiersze, dla których mają

zostać obliczone sumy częściowe, zostały pogrupowane razem. Potem można już obliczyć sumy

częściowe dla każdej kolumny zawierającej liczby.
W tym zadaniu będziemy chcieli dowiedzieć się, ile wynosi łączna sprzedaż (suma) każdej z marek na

wszystkich rynkach w każdym z 12 miesięcy. Następnie podsumujemy dane stosując funkcję liczącą

średnią, a potem wartość maksymalną. W tym celu wykonaj następujące polecenia:
1.

Posortuj ponownie dane o sprzedaży aut by były ułożone według marki (dla większego porządku

możesz dodać jako drugi poziom region sprzedaży). Jest to konieczne, bo właśnie grupy marek

samochodów będziemy chcieli podsumować. Jeśli dane nie będą dobrze posortowane, narzędzie

sum częściowych nie da przydatnego efektu, a wręcz może na dłuższy czas zawiesić pracę
programu.

2. Na podstawie danych w tabeli

wyświetl sumy częściowe z regionów dla każdego koncernu

pokazujące sumę sprzedanych aut w każdym z miesięcy. Osiągniesz to ustawiając w oknie sum

częściowych (Dane



Konspekt



Suma częściowa) następujące dane:

•

Dla każdej zmiany w: marka

•

Użyj funkcji: Suma

•

Dodaj sumę częściową do: styczeń, luty, marzec, kwiecień, maj, czerwiec lipiec, sierpień,

wrzesień, październik, listopad, grudzień

•

Zamień bieżące sumy częściowe: włączone

•

Podział strony pomiędzy grupami: wyłączone

• Podsu

mowanie poniżej danych: włączone

Rysunek 7 Ustawienia i efekt

działania sum częściowych.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

6/12

ST.IiE

Jeśli Excel zacznie niektóre liczby wyświetlać w postaci 2E+06 lub podobnej, to znaczy, że kolumna

jest zbyt wąska, by wyświetlić wartość w zapisie normalnym i zamiast tego stosowany jest format

naukowy. Wystarczy rozszerzyć kolumnę, by przywrócić normalne wyświetlanie.
3.

Zwróć uwagę na to, że w wierszach podsumowań, które się pojawiły, znajdują się formuły
z

funkcją SUMY.POŚREDNIE. Nie będziemy jej poznawać szczegółowo. Zawiera ona numer

przypisany konkretnemu typowi podsumowania oraz zakres komórek, który służy za dane

wejściowe. Informacja, że w komórkach, w których pojawiły się sumy częściowe, są formuły, a nie

liczby, jest potrzebna do zrozumienia, dlaczego w dalszej części ćwiczenia konieczne będzie

wklejenie komórek z pozostawieniem wyłącznie wartości.

Zwiń sumy częściowe (użyj mechanizmu suwaków, który pojawił się przy lewej krawędzi okna), by

widoczne było tylko podsumowanie poszczególnych marek oraz całości sprzedaży. Można od

razu kliknąć na ikonę

Spróbuj przekopiować otrzymaną listę do Arkusz2. Jaki jest efekt?

Cofnij operację wklejania, by Arkusz2 był znów pusty.

Korzystając ze wskazówek z następnego akapitu (

to ten pisany czerwoną czcionką

) przekopiuj

sum

y pośrednie do Arkusz2 w taki sposób, aby w wierszach znalazły się wartości

z poszczególnych mies

ięcy (czyli obrócone o 90° względem danych źródłowych), jak na ilustracji

poniżej (podczas wklejania danych niech wybrana będzie komórka A1):

UWAGA!

Będziesz musiał(a) wykorzystać umiejętnie techniki zaznaczania zakresów komórek:

zaznacz pierwszy wiersz

klikając na jego numerze (1), naciśnij klawisz Control i trzymając klikaj

kolejno na numery wiersz

y, w których Excel wstawił podsumowania: 7, 13, 19… aż do 62. Następnie

wybierz opcję Kopiuj, przełącz się do Arkusz2, zaznacz komórkę A1, a potem wybierz narzędzie
Wklej specjalnie, gdzie zaznaczysz opcje:

Wartości oraz Transpozycja. Podczas zaznaczania

pierwszego wiersza nie przyciskaj jeszcze klawisza Control .

Rysunek 8

Wklejone specjalnie sumy częściowe.

Zmień nazwę Arkusz2 na wyniki.

Wróć do arkusza samochody. Każ teraz za pomocą narzędzia sum częściowych Excelowi
oblicz

yć wartości średnie sprzedaży w poszczególnych miesiącach dla każdego z koncernów.

W tym celu

usuń zaznaczenie wierszy ( Esc ), zaznacz dowolną komórkę w obrębie tabeli danych,

włącz okno Sumy częściowe i zmień funkcję Suma na Średnia.

10.

Otrzymane sumy pośrednie skopiuj tak samo jak za pierwszym razem do arkusza wyniki, by

znalazły się pod wierszami z sumami marek.

11.

Aby umiejętność jeszcze lepiej utrwalić, uzyskaj teraz w arkuszu samochody sumy częściowe
z

wartościami maksymalnymi dla każdej marki w każdym z 12 miesięcy i skopiuj je by wkleić do

arkusza wyniki pod wierszami

z wartościami średnimi (także tylko wartości z transpozycją).

12.

Wróć do arkusza samochody i wyłącz funkcję Suma częściowa, naciskając w jej oknie przycisk

Usuń wszystko.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

7/12

ST.IiE

5 Filtrowanie danych

5.1 Filtr prosty (Autofiltr)

Korzystając z funkcji Filtruj (symbol lejka, w starszych wersja MS Excel — Autofiltr) zobacz
w arkuszu samochody

, sprzedaż których marek samochodów we Włoszech w grudniu mieściła

się w przedziale między 10000 a 50000 (skorzystaj z opcji Filtry liczb).

2. Wyniki autofiltru skopiuj i wklej

(tym razem już bez żadnych specjalnych ustawień) do Arkusz3.

Wyłącz autofiltr (duży symbol lejka).

5.2 Filtr zaawansowany

Filtr zaawansowany do działania potrzebuje kryteriów wpisanych do komórek. W pierwszym wierszu

wpisujemy nazwy kolumn odpowiadające tytułom kolumn źródłowej tabeli (tej, której zwartość ma być

przefiltrowana), a pod nimi wpisujemy kryteria, które mają być spełnione (vide: rysunek poniżej).
4.

podstawie

tabeli

sprzedaży

koncernów

samochodowych

(arkusz

samochody)

wykonaj

filtrowanie zaawansowane, tak aby obok lub

poniżej

tabeli znalazły się informacje o koncernach, których

sprzedaż na Ukrainie w lipcu była mniejsza od 2000,
a w grudniu mniejsza od 10000. W tym celu, zanim

jeszcze wywołasz okno filtru zaawansowanego, do
dowolnych komórek w arkuszu

musisz najpierw

bezbłędnie wpisać

zakres kryteriów filtrowania (obok

pokazano kryteria wpisane do komórek Q5:S6).
Zakres listy

to oczywiście kompletne dane o sprzedaży

samochodów. Pole Kopiuj do

określa lewy górny róg

tabeli, która pojawi się jako efekt filtrowania — należy

wskazać jakąś pustą komórkę znajdującą się w tym
samym arkuszu, co lista i kryteria.

5. Wynik filtru (

dla naszych przykładowych danych

powinny to być 2 pozycje) przekopiuj do Arkusz3

poniżej wyników z autofiltru.

Sprawdź, co się stanie, jeśli warunek <10000
przeniesiesz o jeden wi

ersz niżej i zmienisz zakres

kryteriów

filtru tak, aby obejmowały i ten warunek (czyli, w opisanym tu przykładzie, Q5:S7;

efekcie powinno pojawić się 9 wyników). Jaki płynie z tego wniosek dotyczący koniunkcji

i alternatywy kryteriów?

7. Wynik filtrowania skopiuj do arkusza Arkusz3 pod poprzednimi wynikami.

8. Przemianuj Arkusz3 na filtry.

Zapisz skoroszyt i jeśli chcesz przesłać pracę domową złożoną z tego pliku oraz zadań
z wykresami, które znajdziesz na dalszych stronach, zachowaj ten plik w miejscu, z którego potem

będziesz mieć do niego dostęp.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

8/12

ST.IiE

6 Wykresy

Podobno jeden obraz wart jest tysiąca słów. Jeśli prawda, to jeden dobrze przygotowany wykres może

zastąpić (lub wspomóc) duży zbiór liczb. Należy zdawać sobie sprawę, jakie typy wykresów najlepiej
nadaj

ą się do prezentowania różnych typów danych. A pracując nad estetyką wykresu należy

pamiętać, że celem jest czytelne przekazanie informacji.

Pobierz ze strony WWW przedmiotu i otwórz skoroszyt wykresy.xlsx

. Wykonuj w nim ćwiczenia

zawarte w tym punkcie zestawu.

Obok każdego arkusza ćwiczeniowego znajdziesz arkusz

pomocniczy, w którym przedstawione zostały rysunki pokazujące, jak powinny wyglądać poprawnie
wykonane wykresy.
Wykresy najczęściej tworzy się zaznaczając zakres komórek z danymi, które mają być pokazane na

wykresie (łącznie z komórkami służącymi opisowi danych), a następnie wybierając z karty Wstawianie
odpowiedni typ wykresu w sekcji Wykresy.

Często

wystarczy zaznaczyć tylko jedną komórkę, a Excel

odnajdzie  cały  potrzebny  do  narysowania  wykresu
zakres.
Po  stworzeniu  wykresu  można  go  modyfikować  za

pomocą kart Narzędzi wykresów: Projektowanie,

Układ i Formatowanie.

6.1 Wykresy kolumnowe

Wykresy kolumnowe s

ą przydatne do porównywania punktów danych (tzw. dane dyskretne, czyli

nieciągłe). Można za ich pomocą pokazywać jedną lub wiele serii danych.
Podobnemu celowi służą wykresy słupkowe, które w praktyce są wykresami kolumnowymi obróconymi
o 90° zgodnie z ruchem wskazówek zegara.

W arkuszu kolumnowy

znajdują się przykładowe półroczne dane o sprzedaży pewnego salonu

handlującego książkami, muzyką, filmami i multimediami (gry, programy itp.).
1. Stwórz w tym arkuszu wykres kolumnowy grupowany

(nazwy typów wykresów są widoczne, gdy

zatrzyma się kursor nad ich ikoną), na którym na osi poziomej (odciętych) znajdą się kolejne
kategorie produktów

(punkty danych), na osi pionowej (rzędnych) widoczna będzie skala wartości

sprzedaży, a serie (miesiące) będą grupami przylegających do siebie kolumn w ramach każdego

miesiąca. Niech na prawo od obszaru kreślenia widoczna będzie legenda objaśniająca, który kolor

kolumny oznacza którą serię danych. Wykres powinien nosić tytuł: Wartość sprzedaży salonu.

Stworzony przez Ciebie wykres powinien przypominać wykres oznaczony numerem 1 w arkuszu
kolumnowy-pomoc.

2. Stwórz w arkuszu kolumnowy obok poprzedniego wykresu wykres kolumnowy skumulowany, jaki

widoczny jest na rys. 2 w arkuszu kolumnowy-pomoc

. Jakie informacje można z niego

odczytać? Czy widać na nim więcej informacji niż na poprzednim wykresie lub w tabeli źródłowej?

3. Stwórz w tym samym arkuszu kolejny wykres — kolumnowy 3-W (vide: arkusz kolumnowy-

pomoc

, rys. 3). Czy ten wykres jest czytelny? Co można zrobić, by uratować choć część

informacji, jakie powinien ten wykres prezentować? Rozważ funkcje, które udostępnia dla tego
wykresu okno

Wybieranie źródła danych (Narzędzia wykresów > karta Projektowanie > sekcja

Dane > ikona Zaznacz dane).

Z którego wykresu jesteś w stanie więcej się dowiedzieć — z tego,

czy z poprzedniego?

4. Jako czwarty stwórz wykres 100% skumulowan

y kolumnowy, jaki widać na rys. 4 w arkuszu

kolumnowy-pomoc

. Zauważ, że seria „Książki” jest w górnej części obszaru kreślenia, pod nią

„Muzyka”, niżej „Filmy”, a na samym dole „Multimedia”. Wykorzystaj umiejętności zdobyte przy
„ratowaniu” wykresu nr 3.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

9/12

ST.IiE

Pamiętaj o zapisywaniu skoroszytu co pewien czas.

6.2 Wykresy liniowe

Wykresy liniowe nadają się do prezentowania danych ciągłych i przydają się m.in. przy odnajdowaniu
trendów pokazywanych zjawisk.

Jakkolwiek wykreślanie trendów za pomocą wykresów Excela jest

umiejętnością przydatną, to pozostawia się studiowanie tego zagadnienia dociekliwości studentów

(czytaj: trendów nie będzie na zaliczeniu).
Arkusz liniowy

zawiera tabelę danych dotyczącą prognozy urodzeń z zgonów w Polsce w latach

2008–2035 (tzw. ruch na

turalny ludności). Dane są rzeczywiste i pochodzą z Głównego Urzędu

Statystycznego.

1. W oparciu o te

dane stwórz wykres liniowy pokazujący urodzenia i zgony w kolejnych latach. Aby

wykres wyglądał jak ten widoczny w arkuszu liniowy-pomoc

• dobierz odpowiedni styl z karty Projektowanie,
•

ustaw wyrównanie tekstu osi poziomej tak, by tekst był obrócony o 270°.

•

dodaj tytuł wykresu oraz osi poziomej,

•

zmień kolory linii każdej z dwóch serii,

•

upewnij się, że dla każdej linii włączone jest wygładzanie (Formatowanie serii danych > Styl
linii),

•

przenieś legendę w okolice lewego górnego narożnika obszaru kreślenia.

Czy ten wykres w czytelny sposób przekazuje informacje? Czy dobór koloru tła jest odpowiedni?

Dlaczego? Czym powinno się kierować przy doborze kolorów wykresu?
Wykresy mogą być elementami arkuszy lub same być arkuszami. Spróbujmy tej drugiej możliwości:
2.

Zaznacz wykres w arkuszu liniowy, skopiuj go i wklej pod oryginałem.

3. Zaznacz ten drugi wykres i menu kontekstowego prawego przycisku myszy lub karty

Projektowanie > sekcja Lokalizacja

wybierz opcję Przenieś wykres. Tam wybierz opcję Nowy

arkusz

i zmień nazwę na „Wykres liniowy”. Zaakceptuj przyciskiem OK.

Efektem jest nowy arkusz, który nie zawiera komórek, a jedynie obiekt, jakim jest wykres. Natomiast
w arkuszu liniowy

ten drugi wykres zniknął. Tej czynności nie można cofnąć w zwykłym trybie,

dlatego lepiej wcześniej zapisać plik, gdyby coś miało pójść źle.

0 zł

20 000 zł

40 000 zł

60 000 zł

80 000 zł

100 000 zł

120 000 zł

140 000 zł

160 000 zł

Książki

Muzyka

Filmy

Multimedia

Wartość sprzedaży salonu

styczeń

luty

marzec

kwiecień

maj

czerwiec

0 zł

50 000 zł

100 000 zł

150 000 zł

200 000 zł

250 000 zł

300 000 zł

350 000 zł

400 000 zł

styczeń

luty

marzec kwiecień maj

czerwiec

Wartość sprzedaży salonu

Multimedia

Filmy

Muzyka

Książki

Filmy

0 zł

50 000 zł

100 000 zł

150 000 zł

Wartość sprzedaży salonu

Książki

Muzyka

Filmy

Multimedia

20%

40%

60%

80%

100%

styczeń

luty

marzec kwiecień

maj

czerwiec

Wartość sprzedaży salonu

Książki

Muzyka

Filmy

Multimedia

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

10/12

ST.IiE

6.3

Wykresy kołowe

Wykres kołowy jest przydatny, gdy chcemy pokazać proporcje między danymi lub ich udział w całości.
Na tym wykresie

można pokazać tylko jedną serię danych. Powinno się go stosować do niezbyt wielu

punktów danych, bo przy większej liczbie niż 5–6 staje się nieczytelny.
Arkusz

kołowy prezentuje przykładowe wyniki ankiety pytającej o ulubiony typ wykresu

. Obok

odpowiedzi

podane, ile osób spośród badanych wskazało dany typ wykresu.

W oparciu o te dane stwórz wykres kołowy pokazujący, jaki procent respondentów wskazał każdą

z odpowiedzi. Aby wykres wyglądał tak, jak ten przedstawiony w arkuszu

kołowy-pomoc, zwróć

uwagę na:

• odpowiedni styl wykresu,
•

wypełnienie niektórych punktów teksturą (chodzi o pokazanie, że można użyć dowolnej grafiki

do kolorowania wykresów, ale też że często nie jest to dobry pomysł),

•

pokazywanie etykiet danych, które zawierać będą wartość procentową i będą położone przy

końcu wewnętrznym wykresu, a także kolor i rozmiar czcionki dobrane będą tak, by etykiety

były czytelne,

•

tytuł wykresu — skoro pokazywane są dane procentowe, to nie jest to już liczba, a rozkład lub
odsetek odpowiedzi.

Przyjrzyj się wykresowi i legendzie. Czy przy takiej liczbie odpowiedzi i doborze kolorów łatwo jest

ustalić, który wycinek koła dotyczy której odpowiedzi?

Oczywiście jest to pytanie bez sensu, bo typ wykresu należy dobierać do typu danych, a nie do koloru firanek w salonie lub

znaku zodiaku czy przekonań politycznych

250 000

300 000

350 000

400 000

450 000

500 000

Ruch naturalny ludności w latach 2008-2035

Urodzenia

Zgony

20%

18%

25%

17%

Rozkład odpowiedzi

kolumnowy

liniowy

kołowy

słupkowy

warstwowy

punktowy

radarowy

trudno powiedzieć

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

11/12

ST.IiE

6.4 Wykresy punktowe

Wykres punktowy (zwany także wykresem XY) służy pokazywaniu zależności między dwiema

wartościami. Nadaje się zatem m.in. do graficznej prezentacji funkcji jednej zmiennej.
Arkusz punktowy zawiera fragment tabeli

wyliczającej dla zmiennej X (kolumna A) wartość sinusa

(kolumna B) oraz cosinusa (kolumna B).

Wykorzystując wiedzę i umiejętności zdobyte na poprzednim laboratorium wypełnij w kolumnie A

komórki od A4 do A 34 liczbami od 1 do 16 przyrastającymi o 0,5.

Uzupełnij komórki B3:B34 oraz C3:C34 formułami tak, by w każdym wierszu sinus i cosinus były

wyliczane dla wartości z kolumny A znajdującej się w tym samym wierszu.

Stwórz wykres punktowy z wygładzonymi liniami pokazujący przebieg funkcji sin(x) oraz cos(x) dla

podanego w kolumnie A zakresu zmiennych. Aby wyglądał on tak, jak zaprezentowano w arkuszu
punktowy-pomoc

, zwróć uwagę na następujące elementy:

•

maksimum osi poziomej powinno wynosić 16, a jednostka główna 1

•

oprócz głównych poziomych linii siatki powinny być wyświetlane główne linie pionowe siatki;

aby nie odciągały wzroku od wykresu powinny być jasnoszare,

•

oś odciętych powinna być podpisana jako „x”, a oś rzędnych jako „f(x)”.

Dodatkowo można spróbować dobrać ekstrema osi Y i/lub rozmiar okna wykresu, by jednostki na obu

osiach miały taki sam rozmiar.

4. Zapisz skoroszyt.

6.5 Interakcje z wykresami

Przejdź do arkusza interakcja. Przedstawia ona wykresy sin(x) i cos(x). W okolicach górnych

narożników znajdują się suwaki za pomocą których można regulować parametry wyświetlanych

funkcji, takie jak amplituda oraz okres. Sprawdź, jak ten mechanizm działa. W materiałach do jednego
z kolejnych laboratoriów znajdziesz opis korzystania z formantów potrzebnych do stworzenia takiego
interaktywnego mechanizmu.

7 Praca domowa

Wykonane ćwiczenia z tego zestawu (oba pliki:

Nazwisko-auta.xlsx

wykresy.xlsx

)

odeślij na

adres e-mail nauczyciela

prowadzącego laboratorium.

8 Pytania s

prawdzające wiedzę i umiejętności

Sprawdź, czy potrafisz odpowiedzieć na poniższe pytania. Pomocą, poza wykonanymi ćwiczeniami,

służy też polecana niżej

literatura

Jak skorzystać z formatowania warunkowego, by zmieniał się wygląd komórki zależnie od jej

zawartości?

Jak skorzystać z formatowania warunkowego, by na podstawie zawartości jednej komórki zmieniał

się wygląd innej komórki?

-1,5

-1

-0,5

0,5

1,5

f(x)

sin(x)

cos(x)

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

12/12

ST.IiE

Jak można zarządzać (zmieniać, usuwać) już istniejącym formatowaniem warunkowym na
poziomie komórki, arkusza i skoroszytu?

4. Co jest celem sortowania danych?

O czym należy pamiętać określając zakres danych, które mają być posortowane?

Czym się różni sortowanie wielopoziomowe od jednopoziomowego?

Czy można w Excelu sortować dane nie według kolumn, ale według wierszy? Jak to zrobić?

Po co i jak stosuje się sumy częściowe?

Jak zaznacza się, kopiuje i wkleja w Excelu wiele obszarów komórek jednocześnie?

10.

Co w narzędziu Wklej specjalnie oznaczają różne metody wklejania (Wszystko, Formuły,

Wartości…), operacje (Dodaj, Odejmij…) oraz polecenia (Pomijaj puste, Transpozycja)?

11.

Do czego służy filtrowanie danych?

12.

Jeśli mielibyśmy w arkuszu listę zawierającą w jednej z kolumn imiona osób, to w jaki sposób za

pomocą filtra prostego moglibyśmy wyświetlić tylko kobiety, zakładając w uproszczeniu, że

wszystkie żeńskie i tylko żeńskie imiona kończą się na literę „a”?

A jak przy tych samych

założeniach uzyskać listę wyłącznie mężczyzn? (Możesz do prób wykorzystać listę studentów

opublikowaną na witrynie wydziałowej.)

13.

Jak w filtrze zaawansowanym uzyskać koniunkcję warunków, a jak alternatywę?

14.

Z jakich elementów składa się wykres i do czego poszczególne elementy służą?

15.

W jaki sposób ustawia się, by Excel sam dobrał skale osi?

16.

Jakiego typu dane nadają się do pokazania ich za pomocą wykresu liniowego?

17. Który typ wykresu jest najlepszy do rysowania funkcji matematycznych jednej zmiennej?

Dlaczego?

18.

Czym różni się wykres liniowy od punktowego?

19. W jaki sposób

stosowanie wykresów trójwymiarowych zwiększa czytelność prezentowanych

danych?

9 Literatura

• Kopiowanie i przenoszenie danych:

http://office.microsoft.com/pl-pl/excel/CH100648341045.aspx

• Filtrowanie, sortowanie i warunkowe formatowanie danych:

http://office.microsoft.com/pl-pl/excel/CH100648451045.aspx

• Sumy

częściowe (konspekty):

• John Walkenbach. Excel 2007 PL. Biblia. Helion, Gliwice 2007, s. 537–544.
•

http://office.microsoft.com/pl-pl/excel/HP100791921045.aspx

• Wykresy:

• John Walkenbach. Excel 2007 PL. Biblia. Helion, Gliwice 2007, s. 403–466.
• Jinjer Simon. Excel. Profesjonalna analiza i prezentacja danych. Helion, Gliwice 2006,

s. 106–119.

•

http://office.microsoft.com/pl-pl/excel/HP012163481045.aspx

•

http://office.microsoft.com/pl-pl/excel/CH100648161045.aspx

•

http://office.microsoft.com/pl-pl/excel/CH100648761045.aspx

•

http://office.microsoft.com/training/training.aspx?AssetID=RC011055061045

•

http://office.microsoft.com/training/training.aspx?AssetID=RC011097791045

•

http://office.microsoft.com/training/training.aspx?AssetID=RC011185911045

Niestety, ta metoda nie poradzi sobie dobrze z imionami męskimi takimi jak Barnaba, Bonawentura, Jarema, Kosma itp. oraz

niektórymi imionami żeńskimi, np. Beatrycze, Karmen czy Inez.

Document Outline