Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej
publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną,
fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym
powoduje naruszenie praw autorskich niniejszej publikacji.

Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi
ich właścicieli.

Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje
były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie,
ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz
Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody
wynikłe z wykorzystania informacji zawartych w książce.

Redaktor prowadzący: Ewelina Burska

Projekt okładki: ULABUKA

Materiały graficzne na okładce zostały wykorzystane za zgodą Shutterstock.

Wydawnictwo HELION
ul. Kościuszki 1c, 44-100 GLIWICE
tel. [phone]
e-mail: [e-mail]
WWW: http://helion.pl (księgarnia internetowa, katalog książek)

Drogi Czytelniku!
Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres
http://helion.pl/user/opinie?w80zae
Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.

Dodatkowe materiały do książki można znaleźć pod adresem:
ftp://ftp.helion.pl/przyklady/w80zae.zip

ISBN: 978-83-246-4756-9

Copyright © Helion 2013

Printed in Poland.

•

Kup książkę

•

Poleć książkę

•

Oceń książkę

•

Księgarnia internetowa

•

Lubię to! » Nasza społeczność

Spis tre"ci

Wprowadzenie ..................................................................................................... 7

Zadanie 1. Funkcja klamrowa ............................................................................ 9

Zadanie 2. S!owa ............................................................................................ 13

Zadanie 3. Mieszkania .................................................................................... 19

Zadanie 4. Tabliczka mno#enia ........................................................................ 23

Zadanie 5. O$wietlenie ulic ............................................................................. 29

Zadanie 6. Trójk%ty ......................................................................................... 37

Zadanie 7. Kursy walut .................................................................................... 41

Zadanie 8. Lotto ............................................................................................. 45

Zadanie 9. Test ............................................................................................... 49

Zadanie 10. BMI ............................................................................................... 55

Zadanie 11. Temperatury .................................................................................. 61

Zadanie 12. Gie!da ............................................................................................ 67

Zadanie 13. Odleg!o$ci ..................................................................................... 73

Zadanie 14. Smok Heighwaya ........................................................................... 77

Zadanie 15. Transakcje kasowe ........................................................................ 83

Zadanie 16. Bankomat ...................................................................................... 87

Zadanie 17. PESEL ........................................................................................... 91

Zadanie 18. Cyfry ............................................................................................. 99

Zadanie 19. Ranking zawodników .................................................................... 105

Kup ksi

ąĪkĊ

Pole

ü ksiąĪkĊ

4

W 80 zada& dooko!a Excela

Zadanie 20. Wybory ........................................................................................ 111

Zadanie 21. Audiotele ..................................................................................... 117

Zadanie 22. Klasy energetyczne ...................................................................... 123

Zadanie 23. Imiona ......................................................................................... 127

Zadanie 24. Kody ............................................................................................ 131

Zadanie 25. Litera A ....................................................................................... 135

Zadanie 26. Adresy e-mailowe ......................................................................... 139

Zadanie 27. Liczby s!ownie ............................................................................. 143

Zadanie 28. Palindromy .................................................................................. 147

Zadanie 29. Szyfr Cezara ................................................................................. 151

Zadanie 30. Szyfr podstawieniowy ................................................................... 155

Zadanie 31. Szyfr gaderypoluki ........................................................................ 159

Zadanie 32. Szyfr zegarowy ............................................................................. 163

Zadanie 33. Pechowe pi%tki ............................................................................ 169

Zadanie 34. Cukiernia ..................................................................................... 173

Zadanie 35. Fundusze inwestycyjne ................................................................. 177

Zadanie 36. Czas podró#y ............................................................................... 183

Zadanie 37. Park wodny .................................................................................. 189

Zadanie 38. Pomoc techniczna ........................................................................ 193

Zadanie 39. Koszty pracy ................................................................................ 199

Zadanie 40. Urodziny ...................................................................................... 203

Zadanie 41. Call Center .................................................................................. 207

Zadanie 42. Nagrody pracownicze ................................................................... 213

Zadanie 43. Badania okresowe pojazdów ......................................................... 219

Zadanie 44. Badania profilaktyczne ................................................................. 223

Zadanie 45. Wykresy funkcji ........................................................................... 227

Zadanie 46. Wykres mieszany ......................................................................... 233

Zadanie 47. Ci%g Fibonacciego ....................................................................... 239

Zadanie 48. Cosinus ....................................................................................... 243

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Spis tre$ci

5

Zadanie 49. Wykres pó!ko!owy ........................................................................ 247

Zadanie 50. Wykres Gantta ............................................................................. 251

Zadanie 51. Piramida wieku ............................................................................ 257

Zadanie 52. Dynamiczny wykres ...................................................................... 263

Zadanie 53. Funkcja 3D .................................................................................. 269

Zadanie 54. Zielone miasto ............................................................................. 273

Zadanie 55. Polana ......................................................................................... 277

Zadanie 56. Samochody .................................................................................. 281

Zadanie 57. Solaris ......................................................................................... 285

Zadanie 58. Oszcz'dno$ci ............................................................................... 289

Zadanie 59. Planeta ........................................................................................ 293

Zadanie 60. Gra .............................................................................................. 299

Zadanie 61. Pole ............................................................................................ 303

Zadanie 62. Liczba ....................................................................................... 305

Zadanie 63. Koszty przejazdu .......................................................................... 309

Zadanie 64. Fundusz inwestycyjny — wykres ................................................... 315

Zadanie 65. Przystanki autobusowe ................................................................ 323

Zadanie 66. Autobusy ..................................................................................... 329

Zadanie 67. Pole kombi .................................................................................. 335

Zadanie 68. Pola wyboru ................................................................................. 341

Zadanie 69. Oferta handlowa ........................................................................... 347

Zadanie 70. Paski przewijania ......................................................................... 351

Zadanie 71. Pole opcji .................................................................................... 357

Zadanie 72. Kilka pól opcji .............................................................................. 363

Zadanie 73. Minimum, maksimum i miejsce zerowe funkcji .............................. 369

Zadanie 74. Uk!ad równa& ............................................................................... 377

Zadanie 75. Stypendia .................................................................................... 381

Zadanie 76. Podatek liniowy ........................................................................... 387

Zadanie 77. Firma kurierska ............................................................................ 393

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

6

W 80 zada& dooko!a Excela

Zadanie 78. Sonda# wyborczy .......................................................................... 399

Zadanie 79. Ankieta ....................................................................................... 405

Zadanie 80. Archiwum pogody ........................................................................ 413

Dodatek Wykorzystane funkcje ........................................................................ 421

Indeks funkcji ................................................................................................. 429

Indeks narz'dzi ............................................................................................... 433

Indeks poziomów trudno$ci ............................................................................. 437

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Zadanie 32. " Szyfr zegarowy

163

Zadanie 32.

Szyfr zegarowy

Wykorzystane funkcje:

D"

,

FRAGMENT.TEKSTU

,

JE,ELI

,

LOS

,

PODAJ.POZYCJ0

,

TEKST

,

WIERSZ

,

ZAOKR.DO.CA"K

Wykorzystane narz%dzia: formu'y, funkcje
Poziom trudno(ci: 5/5

Tre"+ zadania

Przygotuj arkusz, za pomoc3 którego b6dzie mo8na zaszyfrowa: s'owa o maksymalnej
d'ugo<ci 20 znaków, korzystaj3c z szyfru zegarowego. W szyfrze tym literom przy-
porz3dkowuje si6 odpowiednie godziny zgodnie z tym, co pokazano na poni8szym ry-
sunku. Wiadomo<: zaszyfrowan3 zapisuje si6, podaj3c odpowiednie cyfry (poprze-
dzone zerem w przypadku godzin jednocyfrowych) jako godzin6 oraz losow3 liczb6
minut po znaku dwukropka. Poszczególne wskazania czasu oddzielane s3 przecinka-
mi.

Przyk'adowo s'owo latarka mo8e by: zaszyfrowane jako 12:33,01:14,19:45,01:08,
17:24,11:06,01:22.

Przyjmij za'o8enie, 8e s'owo do zaszyfrowania zapisane jest z u8yciem liter alfabetu
'aciDskiego (bez polskich znaków diakrytycznych).

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

164

W 80 zada& dooko!a Excela

Rozwi/zanie

Przygotowywanie arkusza

Z algorytmu dzia'ania szyfru wynika, 8e szyfrator nie jest jednocze<nie deszyfratorem,
dlatego konieczne b6dzie przygotowanie dwóch arkuszy — jednego do szyfrowania
has'a i drugiego do deszyfrowania. Rozpocznij od przygotowania arkusza do szyfrowania.
Przygotowanie arkusza do deszyfrowania b6dzie zadaniem dodatkowym.

1.

S'owo-has'o do zaszyfrowania wpisywane b6dzie do komórki A1. Przyk'adowo
wpisz has'o

latarka

.

2.

W komórkach zakresu M1:N24 przygotuj tabel6 zawieraj3c3 spis liter alfabetu
i odpowiadaj3cych im godzin. Zawarto<: tej tabeli zosta'a przedstawiona poni8ej.

1

2

3

4

5

6

7

8

9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

r

s

t

u

w

x

y

z

Rozbijanie wyrazu na litery

1.

Najpierw trzeba s'owo do zaszyfrowania rozbi: na pojedyncze litery, zapisuj3c
ka8d3 z liter w osobnej komórce. W tym celu do komórki C1 wstaw funkcj6

JE,ELI

.

Jako argument

test_logiczny

wstaw funkcj6

D"

, wskazuj3c jako jej argument

tekst

komórk6 A1 (zastosuj adresowanie bezwzgl6dne). Za funkcj3

D"

dopisz

znak nieostrej wi6kszo<ci

>=

, a nast6pnie wstaw bezargumentow3 posta:

funkcji

WIERSZ

. Argument

test_logiczny

przyjmie zatem posta: nast6puj3cej

formu'y

D"($A$1)>=WIERSZ()

. Za chwil6 zawarto<: komórki C1 zostanie

skopiowana w dó' arkusza a8 do komórki C20, tak aby w rezultacie litery
has'a by'y zapisane w kolejnych wierszach kolumny C. Zadaniem tej formu'y
b6dzie rozpoznanie, czy pozosta' jeszcze jaki< znak has'a do zapisania w danej
komórce z zakresu C1:C20.

Jako argument

warto!"_je#eli_prawda

wstaw funkcj6

FRAGMENT.TEKSTU

,

wskazuj3c jako jej argument

tekst

komórk6 A1 (zastosuj adresowanie

bezwzgl6dne). Jako argument

liczba_pocz%tkowa

wstaw bezargumentow3

posta: funkcji

WIERSZ

, a jako argument

liczba_znaków

wpisz warto<:

1

.

Argument

warto!"_je#eli_prawda

przyjmie zatem posta: nast6puj3cej formu'y

FRAGMENT.TEKSTU($A$1;WIERSZ();1)

.

Za pomoc3 tej formu'y w ka8dej kolejnej komórce zakresu C1:C20
„wycinana” jest kolejna (ustalana za pomoc3 funkcji

WIERSZ

) litera z has'a.

Jako argument

warto!"_je#eli_fa&sz

wpisz wyra8enie tekstowe

""

. Je<li

nie ma ju8 znaków do wyci6cia z has'a, w kolejnych komórkach zakresu
C1:C20 zapisywane b6dzie puste wyra8enie tekstowe.

Ca'a formu'a w komórce C1 b6dzie nast6puj3cej postaci:

=JE,ELI(D"($A$1)>=WIERSZ();FRAGMENT.TEKSTU($A$1;WIERSZ();1);"")

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Zadanie 32. " Szyfr zegarowy

165

2.

Skopiuj zawarto<: komórki C1 w dó' arkusza a8 do komórki C20.

Kodowanie liter

1.

Do komórki D1 wstaw funkcj6

JE,ELI

.

Jako argument

test_logiczny

wpisz formu'6

C1<>""

.

Jako argument

warto!"_je#eli_prawda

wstaw funkcj6

TEKST

. Jako argument

warto!"

funkcji

TEKST

wstaw funkcj6

PODAJ.POZYCJ0

. Funkcja ta b6dzie

ustala: pozycje kolejnych liter z has'a w tabeli godzin. Argumentem

szukana_warto!"

b6dzie zatem komórka C1, argumentem

przeszukiwana_tab

b6dzie zakres komórek N1:N24 (zastosuj adresowanie bezwzgl6dne),
a argumentem

typ_porównania

warto<:

0

. Uzupe'nij jeszcze argument

format_tekst

funkcji

TEKST

wyra8eniem tekstowym

"00"

. Dzi6ki tak

skonstruowanej formule b6d3cej argumentem

warto!"_je#eli_prawda

do kolejnych komórek zakresu D1:D20 wstawiane b6d3 zawsze godziny
zapisane w postaci dwucyfrowej, odpowiadaj3ce poszczególnym literom
alfabetu.

Jako argument

warto!"_je#eli_fa&sz

wpisz wyra8enie tekstowe

""

. Je<li

nie ma ju8 wyci6tych z has'a liter, w kolejnych komórkach zakresu D1:D20
zapisywane b6dzie puste wyra8enie tekstowe.

Formu'a w komórce D1 powinna mie: zatem posta::

=JE,ELI(C1<>"";TEKST(PODAJ.POZYCJ0(C1;$N$1:$N$24;0);"00");"")

2.

Skopiuj formu'6 z komórki D1 w dó' arkusza a8 do komórki D20.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

166

W 80 zada& dooko!a Excela

Teraz dla ka8dej litery has'a trzeba wygenerowa: losow3 liczb6 minut.

1.

Do komórki E1 wstaw funkcj6

JE,ELI

.

Jako argument

test_logiczny

wpisz formu'6

C1<>""

.

Jako argument

warto!"_je#eli_prawda

wstaw funkcj6

TEKST

. Jako argument

warto!"

funkcji

TEKST

wstaw funkcj6

ZAOKR.DO.CA"K

. Jako argument

liczba

tej funkcji wpisz formu'6

LOS()*60

, a jako argument

format_tekst

wpisz

wyra8enie tekstowe

"00"

. Tak skonstruowana formu'a odpowiedzialna jest

za wygenerowanie rzeczywistej liczby losowej z zakresu od 0 do 60, która
nast6pnie zaokr3glana jest do cz6<ci ca'kowitych (tak aby uzyska: minuty).
Ostatecznie funkcja

TEKST

„wymusza” zapisanie tej liczby w formacie

dwucyfrowym.

Jako argument

warto!"_je#eli_fa&sz

wpisz wyra8enie tekstowe

""

. Je<li

nie ma ju8 wyci6tych z has'a liter, w kolejnych komórkach zakresu E1:E20
zapisywane b6dzie puste wyra8enie tekstowe.

Formu'a w komórce E1 powinna mie: zatem posta::

=JE,ELI(C1<>"";TEKST(ZAOKR.DO.CA"K(LOS()*60);"00");"")

2.

Skopiuj formu'6 z komórki E1 w dó' arkusza a8 do komórki E20.

Kolejnym etapem jest z'3czenie cyfr oznaczaj3cych godzin6 i wygenerowanych minut,
tak aby uzyska: zapis w formacie gg:mm.

1.

Do komórki F1 wstaw funkcj6

JE,ELI

.

Jako argument

test_logiczny

wpisz formu'6

C1<>""

.

Jako argument

warto!"_je#eli_prawda

wpisz formu'6

D1&":"&E1

. Symbol

&

jest u8yty tutaj do po'3czenia wyra8eD tekstowych.

Jako argument

warto!"_je#eli_fa&sz

wpisz wyra8enie tekstowe

""

. Je<li

nie ma ju8 wyci6tych z has'a liter (a tym samym w komórkach kolumn D
i E nie ma oznaczenia godziny i odpowiednio minut), wówczas w kolejnych
komórkach zakresu E1:E20 zapisywane b6dzie puste wyra8enie tekstowe.

Formu'a w komórce F1 powinna mie: zatem posta:

=JE,ELI(C1<>"";D1&":"&E1;"")

.

2.

Skopiuj formu'6 z komórki F1 w dó' arkusza a8 do komórki F20.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Zadanie 32. " Szyfr zegarowy

167

Przedostatnim etapem rozwi3zania b6dzie dopisanie na koDcu ka8dego wyra8enia
oznaczaj3cego czas (utworzonego w poprzednim kroku) symbolu przecinka, gdy8
w'a<nie takim symbolem maj3 by: rozdzielone te wyra8enia w zaszyfrowanym ha<le.
Wyj3tkiem jest ostatnie wyra8enie b6d3ce rezultatem zakodowania ostatniej litery has'a
— po nim przecinek nie mo8e by: dodawany.

1.

Do komórki G1 wstaw funkcj6

JE,ELI

.

Jako argument

test_logiczny

wpisz formu'6

(C1<>"")*(C2<>"")

. Warunek ten

s'u8y sprawdzeniu, czy komórki bie83cego i kolejnego wiersza nie s3 puste.

Je<li komórki bie83cego i kolejnego wiersza nie s3 puste, wówczas do
wyra8enia zawieraj3cego godzin6 i minuty, zapisanego w formacie gg:mm,
dopisywany jest przecinek. Dlatego jako argument

warto!"_je#eli_prawda

wpisz formu'6

F1&","

.

Je<li warunek

testu_logicznego

nie jest spe'niony, wówczas przepisywana

jest warto<: z komórki F1 bez dopisywania symbolu przecinka.

Formu'a w komórce G1 powinna mie: zatem posta::

=JE,ELI((C1<>"")*(C2<>"");F1&",";F1)

2.

Skopiuj formu'6 z komórki G1 w dó' arkusza a8 do komórki G20.

Ostatnim ju8 krokiem jest z'3czenie wyra8eD tekstowych tworz3cych zaszyfrowane
has'o. Dlatego do komórki I1 wpisz formu'6

=G1&G2&G3&G4&G5&G6&G7&G8&G9&G10&

G11&G12&G13&G14&G15&G16&G17&G18&G19&G20

. Has'o latarka po zaszyfrowaniu mo8e

przyj3: posta: 12:33,01:14,19:45,01:08,17:24,11:06,01:22. Uzyskane przez Ciebie
warto<ci minut mog3 by: inne ni8 te podane w tym zaszyfrowanym ha<le! (S3 to
przecie8 losowe warto<ci).

Zadanie dodatkowe

Przygotuj arkusz, za pomoc3 którego b6dzie mo8na odszyfrowywa: has'a o d'ugo<ci co
najwy8ej 20 znaków.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

168

W 80 zada& dooko!a Excela

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Zadanie 33. " Pechowe pi%tki

169

Zadanie 33.

Pechowe pi/tki

Wykorzystane funkcje:

DZIEV

,

LICZ.WARUNKI

Wykorzystane narz%dzia: funkcje, formatowanie niestandardowe, formu'y
Poziom trudno(ci: 1/5

Tre"+ zadania

Oblicz, ile pi3tków w roku 2012 przypada trzynastego dnia miesi3ca.

Rozwi/zanie

Przygotowywanie danych

Rozwi3zanie zadania rozpocznij od sprawdzenia, w którym dniu tygodnia rozpocz3'
si6 rok 2012. W tym celu:

1.

Do komórki A1 wpisz tekst

Pocz[tek roku

.

2.

Do komórki B1 wpisz dat6

2012-01-01

.

3.

Do komórki C1 wpisz formu'6

=B1

.

4.

Klikaj3c komórk6 C1 prawym przyciskiem myszy, wybierz z menu
kontekstowego polecenie Formatuj komórki.

5.

Uaktywnij zak'adk6 Liczby.

6.

Wybierz kategori6 Niestandardowe, a w polu Typ wpisz

dddd

.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

170

W 80 zada& dooko!a Excela

Dzi6ki nadaniu komórce takiego formatu sprawdzisz, jakiego dnia tygodnia rozpocz3'
si6 rok 2012.

Lista wszystkich pi%tków

Wiedz3c, w którym dniu tygodnia rozpocz3' si6 rok 2012, mo8na przygotowa: list6
wszystkich pi3tków tego roku.

1.

Utwórz w komórkach A4:A55 list6 kolejnych liczb naturalnych. B6d3 to numery
tygodni roku 2012. List6 tak3 mo8esz szybko utworzy:, wpisuj3c do komórki
A4 liczb6

1

i przeci3gaj3c j3 w dó' za prawy dolny róg z wci<ni6tym

przyciskiem Ctrl.

2.

Do komórki B3 wpisz tekst

Data pi[tku

.

3.

Do komórki B4 wpisz formu'6

=B1+5

, aby wyznaczy: dat6 pierwszego pi3tku

roku 2012. Data ta powsta'a przez dodanie pi6ciu dni do daty 01.01.2012
przypadaj3cej w niedziel6.

4.

Do komórki B5 wpisz formu'6

=B4+7

i skopiuj j3 w dó' arkusza a8 do komórki

B55. W ten sposób wyznaczone zostan3 daty wszystkich pi3tków roku 2012.

5.

W komórkach obok dat pi3tków okre<lone zostan3 dni miesi3ca, w których
przypadaj3 pi3tki. Do komórki C3 wpisz tekst

Dzied

.

6.

Do komórki C4 wstaw funkcj6

=DZIEV(B4)

, dzi6ki której z daty zawartej w komórce

B4 wytniesz numer dnia.

7.

Skopiuj formu'6 z komórki C4 w dó' arkusza a8 do komórki C55.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Zadanie 33. " Pechowe pi%tki

171

Pi%tki trzynastego

Wiedz3c, którego dnia miesi3ca przypadaj3 kolejne pi3tki roku 2012, mo8na ju8 obliczy:,
ile spo<ród nich przypada w'a<nie 13 dnia.

1.

Do komórki E1 wpisz tekst

Liczba pi[tków przypadaj[cych 13 dnia

miesi[ca

.

2.

Do komórki E2 wstaw funkcj6

LICZ.WARUNKI(C4:C55;13)

. Jako argument

kryteria_zakres1

wska8 zakres komórek C4:C55, a jako argument

kryteria1

wpisz liczb6 13.

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ

Czytaj dalej...

172

W 80 zada& dooko!a Excela

Pole

ü ksiąĪkĊ

Kup ksi

ąĪkĊ