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 HELIONul. Kościuszki 1c, 44-100 GLIWICEtel. 32 231 22 19, 32 230 98 63e-mail: helion@helion.plWWW: 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?w80zaeMoż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�ciWprowadzenie ..................................................................................................... 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

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

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

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

Zadanie 32. � Szyfr zegarowy 163

Zadanie 32.

Szyfr zegarowy

Wykorzystane funkcje: D, FRAGMENT.TEKSTU, JE�ELI, LOS, PODAJ.POZYCJ�, TEKST,WIERSZ, ZAOKR.DO.CAKWykorzystane narz�dzia: formu�y, funkcjePoziom trudno�ci: 5/5

Tre�� zadaniaPrzygotuj arkusz, za pomoc� którego b�dzie mo�na zaszyfrowa� s�owa o maksymalnejd�ugo�ci 20 znaków, korzystaj�c z szyfru zegarowego. W szyfrze tym literom przy-porz�dkowuje si� odpowiednie godziny zgodnie z tym, co pokazano na poni�szym ry-sunku. Wiadomo�� zaszyfrowan� zapisuje si�, podaj�c odpowiednie cyfry (poprze-dzone zerem w przypadku godzin jednocyfrowych) jako godzin� oraz losow� liczb�minut po znaku dwukropka. Poszczególne wskazania czasu oddzielane s� przecinka-mi.

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

Przyjmij za�o�enie, �e s�owo do zaszyfrowania zapisane jest z u�yciem liter alfabetu�aci�skiego (bez polskich znaków diakrytycznych).

164 W 80 zada� dooko�a Excela

Rozwi�zanie

Przygotowywanie arkuszaZ algorytmu dzia�ania szyfru wynika, �e szyfrator nie jest jednocze�nie deszyfratorem,dlatego konieczne b�dzie przygotowanie dwóch arkuszy — jednego do szyfrowaniahas�a i drugiego do deszyfrowania. Rozpocznij od przygotowania arkusza do szyfrowania.Przygotowanie arkusza do deszyfrowania b�dzie zadaniem dodatkowym.

1. S�owo-has�o do zaszyfrowania wpisywane b�dzie do komórki A1. Przyk�adowowpisz has�o latarka.

2. W komórkach zakresu M1:N24 przygotuj tabel� zawieraj�c� spis liter alfabetui odpowiadaj�cych im godzin. Zawarto�� tej tabeli zosta�a przedstawiona poni�ej.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24a 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, zapisuj�cka�d� z liter w osobnej komórce. W tym celu do komórki C1 wstaw funkcj� JE�ELI.

� Jako argument test_logiczny wstaw funkcj� D, wskazuj�c jako jej argumenttekst komórk� A1 (zastosuj adresowanie bezwzgl�dne). Za funkcj� D dopiszznak nieostrej wi�kszo�ci >=, a nast�pnie wstaw bezargumentow� posta�funkcji WIERSZ. Argument test_logiczny przyjmie zatem posta� nast�puj�cejformu�y D($A$1)>=WIERSZ(). Za chwil� zawarto�� komórki C1 zostanieskopiowana w dó� arkusza a� do komórki C20, tak aby w rezultacie literyhas�a by�y zapisane w kolejnych wierszach kolumny C. Zadaniem tej formu�yb�dzie rozpoznanie, czy pozosta� jeszcze jaki� znak has�a do zapisania w danejkomórce z zakresu C1:C20.

� Jako argument warto��_je�eli_prawda wstaw funkcj� FRAGMENT.TEKSTU,wskazuj�c jako jej argument tekst komórk� A1 (zastosuj adresowaniebezwzgl�dne). Jako argument liczba_pocz�tkowa wstaw bezargumentow�posta� funkcji WIERSZ, a jako argument liczba_znaków wpisz warto�� 1.Argument warto��_je�eli_prawda przyjmie zatem posta� nast�puj�cej formu�yFRAGMENT.TEKSTU($A$1;WIERSZ();1).

Za pomoc� tej formu�y w ka�dej kolejnej komórce zakresu C1:C20„wycinana” jest kolejna (ustalana za pomoc� funkcji WIERSZ) litera z has�a.

� Jako argument warto��_je�eli_fa�sz wpisz wyra�enie tekstowe "". Je�linie ma ju� znaków do wyci�cia z has�a, w kolejnych komórkach zakresuC1:C20 zapisywane b�dzie puste wyra�enie tekstowe.

Ca�a formu�a w komórce C1 b�dzie nast�puj�cej postaci:=JE�ELI(D($A$1)>=WIERSZ();FRAGMENT.TEKSTU($A$1;WIERSZ();1);"")

Zadanie 32. � Szyfr zegarowy 165

2. Skopiuj zawarto�� komórki C1 w dó� arkusza a� do komórki C20.

Kodowanie liter

1. Do komórki D1 wstaw funkcj� JE�ELI.

� Jako argument test_logiczny wpisz formu�� C1<>"".

� Jako argument warto��_je�eli_prawda wstaw funkcj� TEKST. Jako argumentwarto�� funkcji TEKST wstaw funkcj� PODAJ.POZYCJ�. Funkcja ta b�dzieustala� pozycje kolejnych liter z has�a w tabeli godzin. Argumentemszukana_warto�� b�dzie zatem komórka C1, argumentem przeszukiwana_tabb�dzie zakres komórek N1:N24 (zastosuj adresowanie bezwzgl�dne),a argumentem typ_porównania warto�� 0. Uzupe�nij jeszcze argumentformat_tekst funkcji TEKST wyra�eniem tekstowym "00". Dzi�ki takskonstruowanej formule b�d�cej argumentem warto��_je�eli_prawdado kolejnych komórek zakresu D1:D20 wstawiane b�d� zawsze godzinyzapisane w postaci dwucyfrowej, odpowiadaj�ce poszczególnym literomalfabetu.

� Jako argument warto��_je�eli_fa�sz wpisz wyra�enie tekstowe "". Je�linie ma ju� wyci�tych z has�a liter, w kolejnych komórkach zakresu D1:D20zapisywane b�dzie puste wyra�enie tekstowe.

Formu�a w komórce D1 powinna mie� zatem posta�:=JE�ELI(C1<>"";TEKST(PODAJ.POZYCJ�(C1;$N$1:$N$24;0);"00");"")

2. Skopiuj formu�� z komórki D1 w dó� arkusza a� do komórki D20.

166 W 80 zada� dooko�a Excela

Teraz dla ka�dej litery has�a trzeba wygenerowa� losow� liczb� minut.

1. Do komórki E1 wstaw funkcj� JE�ELI.

� Jako argument test_logiczny wpisz formu�� C1<>"".

� Jako argument warto��_je�eli_prawda wstaw funkcj� TEKST. Jako argumentwarto�� funkcji TEKST wstaw funkcj� ZAOKR.DO.CAK. Jako argument liczbatej funkcji wpisz formu�� LOS()*60, a jako argument format_tekst wpiszwyra�enie tekstowe "00". Tak skonstruowana formu�a odpowiedzialna jestza wygenerowanie rzeczywistej liczby losowej z zakresu od 0 do 60, któranast�pnie zaokr�glana jest do cz��ci ca�kowitych (tak aby uzyska� minuty).Ostatecznie funkcja TEKST „wymusza” zapisanie tej liczby w formaciedwucyfrowym.

� Jako argument warto��_je�eli_fa�sz wpisz wyra�enie tekstowe "". Je�linie ma ju� wyci�tych z has�a liter, w kolejnych komórkach zakresu E1:E20zapisywane b�dzie puste wyra�enie tekstowe.

Formu�a w komórce E1 powinna mie� zatem posta�:=JE�ELI(C1<>"";TEKST(ZAOKR.DO.CAK(LOS()*60);"00");"")

2. Skopiuj formu�� z komórki E1 w dó� arkusza a� do komórki E20.

Kolejnym etapem jest z��czenie cyfr oznaczaj�cych godzin� i wygenerowanych minut,tak aby uzyska� zapis w formacie gg:mm.

1. Do komórki F1 wstaw funkcj� JE�ELI.

� Jako argument test_logiczny wpisz formu�� C1<>"".

� Jako argument warto��_je�eli_prawda wpisz formu�� D1&":"&E1. Symbol &jest u�yty tutaj do po��czenia wyra�e� tekstowych.

� Jako argument warto��_je�eli_fa�sz wpisz wyra�enie tekstowe "". Je�linie ma ju� wyci�tych z has�a liter (a tym samym w komórkach kolumn Di E nie ma oznaczenia godziny i odpowiednio minut), wówczas w kolejnychkomórkach zakresu E1:E20 zapisywane b�dzie puste wyra�enie tekstowe.

Formu�a w komórce F1 powinna mie� zatem posta�=JE�ELI(C1<>"";D1&":"&E1;"").

2. Skopiuj formu�� z komórki F1 w dó� arkusza a� do komórki F20.

Zadanie 32. � Szyfr zegarowy 167

Przedostatnim etapem rozwi�zania b�dzie dopisanie na ko�cu ka�dego wyra�eniaoznaczaj�cego czas (utworzonego w poprzednim kroku) symbolu przecinka, gdy�w�a�nie takim symbolem maj� by� rozdzielone te wyra�enia w zaszyfrowanym ha�le.Wyj�tkiem jest ostatnie wyra�enie b�d�ce rezultatem zakodowania ostatniej litery has�a— po nim przecinek nie mo�e by� dodawany.

1. Do komórki G1 wstaw funkcj� JE�ELI.

� Jako argument test_logiczny wpisz formu�� (C1<>"")*(C2<>""). Warunek tens�u�y sprawdzeniu, czy komórki bie��cego i kolejnego wiersza nie s� puste.

� Je�li komórki bie��cego i kolejnego wiersza nie s� puste, wówczas dowyra�enia zawieraj�cego godzin� i minuty, zapisanego w formacie gg:mm,dopisywany jest przecinek. Dlatego jako argument warto��_je�eli_prawdawpisz formu�� F1&",".

� Je�li warunek testu_logicznego nie jest spe�niony, wówczas przepisywanajest 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�� z komórki G1 w dó� arkusza a� do komórki G20.

Ostatnim ju� krokiem jest z��czenie wyra�e� tekstowych tworz�cych zaszyfrowanehas�o. Dlatego do komórki I1 wpisz formu�� =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 mo�eprzyj�� posta� 12:33,01:14,19:45,01:08,17:24,11:06,01:22. Uzyskane przez Ciebiewarto�ci minut mog� by� inne ni� te podane w tym zaszyfrowanym ha�le! (S� toprzecie� losowe warto�ci).

Zadanie dodatkowePrzygotuj arkusz, za pomoc� którego b�dzie mo�na odszyfrowywa� has�a o d�ugo�ci conajwy�ej 20 znaków.

168 W 80 zada� dooko�a Excela

Zadanie 33. � Pechowe pi�tki 169

Zadanie 33.

Pechowe pi�tki

Wykorzystane funkcje: DZIE�, LICZ.WARUNKIWykorzystane narz�dzia: funkcje, formatowanie niestandardowe, formu�yPoziom trudno�ci: 1/5

Tre�� zadaniaOblicz, ile pi�tków w roku 2012 przypada trzynastego dnia miesi�ca.

Rozwi�zanie

Przygotowywanie danych

Rozwi�zanie zadania rozpocznij od sprawdzenia, w którym dniu tygodnia rozpocz��si� rok 2012. W tym celu:

1. Do komórki A1 wpisz tekst Pocz�tek roku.

2. Do komórki B1 wpisz dat� 2012-01-01.

3. Do komórki C1 wpisz formu�� =B1.

4. Klikaj�c komórk� C1 prawym przyciskiem myszy, wybierz z menukontekstowego polecenie Formatuj komórki.

5. Uaktywnij zak�adk� Liczby.

6. Wybierz kategori� Niestandardowe, a w polu Typ wpisz dddd.

170 W 80 zada� dooko�a Excela

Dzi�ki nadaniu komórce takiego formatu sprawdzisz, jakiego dnia tygodnia rozpocz��si� rok 2012.

Lista wszystkich pi�tków

Wiedz�c, w którym dniu tygodnia rozpocz�� si� rok 2012, mo�na przygotowa� list�wszystkich pi�tków tego roku.

1. Utwórz w komórkach A4:A55 list� kolejnych liczb naturalnych. B�d� to numerytygodni roku 2012. List� tak� mo�esz szybko utworzy�, wpisuj�c do komórkiA4 liczb� 1 i przeci�gaj�c j� w dó� za prawy dolny róg z wci�ni�tymprzyciskiem Ctrl.

2. Do komórki B3 wpisz tekst Data pi�tku.

3. Do komórki B4 wpisz formu�� =B1+5, aby wyznaczy� dat� pierwszego pi�tkuroku 2012. Data ta powsta�a przez dodanie pi�ciu dni do daty 01.01.2012przypadaj�cej w niedziel�.

4. Do komórki B5 wpisz formu�� =B4+7 i skopiuj j� w dó� arkusza a� do komórkiB55. W ten sposób wyznaczone zostan� daty wszystkich pi�tków roku 2012.

5. W komórkach obok dat pi�tków okre�lone zostan� dni miesi�ca, w którychprzypadaj� pi�tki. Do komórki C3 wpisz tekst Dzie�.

6. Do komórki C4 wstaw funkcj� =DZIE�(B4), dzi�ki której z daty zawartej w komórceB4 wytniesz numer dnia.

7. Skopiuj formu�� z komórki C4 w dó� arkusza a� do komórki C55.

Zadanie 33. � Pechowe pi�tki 171

Pi�tki trzynastego

Wiedz�c, którego dnia miesi�ca przypadaj� kolejne pi�tki roku 2012, mo�na ju� 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 dniamiesi�ca.

2. Do komórki E2 wstaw funkcj� LICZ.WARUNKI(C4:C55;13). Jako argumentkryteria_zakres1 wska� zakres komórek C4:C55, a jako argument kryteria1wpisz liczb� 13.

172 W 80 zada� dooko�a Excela

http://program-partnerski.helion.pl