Idź do
• Spis treści
• Przykładowy rozdział
Helion SA
ul. Kościuszki 1c
44-100 Gliwice
tel. 32 230 98 63
e-mail: helion@helion.pl
© Helion 1991–2010
Katalog książek
Twój koszyk
Cennik i informacje
Czytelnia
Kontakt
Excel 2010 PL.
Biblia
Autor:
Tłumaczenie: Daniel Kaczmarek
ISBN: 978-83-246-2862-9
Tytuł oryginału:
Format: 172×245, stron: 992
Twoje kompletne źródło wiedzy o Excelu 2010!
• Przedstaw dane za pomocą wykresów
• Dokonaj szczegółowej analizy trendów
• Ujarzmij formuły, funkcje, tabele, arkusze i skoroszyty
Excel 2010 to najnowsza wersja najbardziej popularnego obecnie na rynku arkusza kalkulacyjnego
firmy Microsoft. Nie ma lepszego sposobu na jego poznanie niż nauka z Johnem Walkenbachem –
guru Excela i autorem wszystkich napisanych o tym programie książek z serii „Biblia”. „Excel 2010
PL. Biblia” to podręcznik przeznaczony zarówno dla początkujących, jak i zaawansowanych
użytkowników. Zawiera kompletne informacje o komponentach Excela. Autor wzbogacił go też
o praktyczne porady i wskazówki oraz zamieścił w nim użyteczne przykłady.
Znajdziesz tutaj podstawowe wiadomości na temat programu Excel. Nauczysz się tworzyć formuły
i funkcje oraz dokonywać obliczeń. Poznasz tajniki tworzenia wszelkiego rodzaju wykresów oraz
sposoby analizy danych w arkuszu kalkulacyjnym. W końcu dostosujesz program do swoich potrzeb,
korzystając z potężnego narzędzia, jakim jest programowanie i tworzenie makr w języku VBA.
• Podstawowe informacje o Excelu
• Tabele
• Formatowanie arkuszy
• Szablony
• Tworzenie konspektów
• Formuły i funkcje
• Wizualizacja danych
• Tworzenie wykresów przebiegu w czasie
• Współpraca z innymi aplikacjami pakietu Office oraz internetem
• Ochrona danych
• Praca z bazami danych
• Tabele przestawne i analiza danych
• Język VBA
Jeśli coś można zrobić w Excelu, tutaj znajdziesz sposób, w jaki można tego dokonać!
Spis treci
O autorze ........................................................................................ 29
Wstp .............................................................................................. 31
Cz I Podstawowe informacje o Excelu .................................... 35
Rozdzia 1. Wprowadzenie do Excela ............................................................... 37
Kiedy korzystamy z Excela .................................................................................................................37
Nowoci w Excelu 2010 .....................................................................................................................38
Czym s arkusze i skoroszyty .............................................................................................................39
Poruszanie si po arkuszu ...................................................................................................................39
Nawigacja za pomoc klawiatury .................................................................................................39
Nawigacja za pomoc myszy ........................................................................................................42
Narzdzie Wstka ..............................................................................................................................43
Karty narzdzia Wstka ..............................................................................................................43
Karty kontekstowe ........................................................................................................................45
Typy polece narzdzia Wstka ..................................................................................................45
Obsuga interfejsu narzdzia Wstka za pomoc klawiatury ......................................................47
Korzystanie z menu podrcznego .......................................................................................................47
Dostosowywanie paska Szybki dostp ................................................................................................48
Okna dialogowe ..................................................................................................................................50
Nawigacja w oknach dialogowych ...............................................................................................51
Karty w oknach dialogowych .......................................................................................................51
Zastosowanie okna zada ....................................................................................................................52
Tworzenie pierwszego arkusza w Excelu ...........................................................................................53
Rozpoczcie pracy ........................................................................................................................53
Wpisywanie nazw miesicy ..........................................................................................................53
Wprowadzanie danych o wysokoci obrotów ...............................................................................53
Formatowanie wartoci .................................................................................................................54
Nadawanie arkuszowi bardziej wyszukanego wygldu ................................................................55
Dodawanie wartoci ......................................................................................................................55
Tworzenie wykresu .......................................................................................................................55
Drukowanie arkusza .....................................................................................................................56
Zapisywanie skoroszytu ................................................................................................................56
10
Excel 2010 PL. Biblia
Rozdzia 2. Wprowadzanie danych i ich edycja w arkuszu ............................. 59
Typy danych uywanych w arkuszu ...................................................................................................59
Wartoci liczbowe ........................................................................................................................59
Tekst .............................................................................................................................................60
Formuy ........................................................................................................................................60
Wprowadzanie tekstu i wartoci do arkusza .......................................................................................60
Wprowadzanie dat i godzin do arkusza ...............................................................................................62
Wprowadzanie dat ........................................................................................................................62
Wprowadzanie godzin ..................................................................................................................62
Modyfikacja zawartoci komórki ........................................................................................................63
Usuwanie zawartoci komórki ......................................................................................................63
Zastpienie zawartoci komórki ...................................................................................................63
Edycja zawartoci komórki ...........................................................................................................64
Przydatne wskazówki dotyczce wprowadzania danych ..............................................................65
Formatowanie liczb .............................................................................................................................70
Automatyczne formatowanie liczb ...............................................................................................70
Formatowanie za pomoc narzdzia Wstka ..............................................................................71
Formatowanie za pomoc klawiszy skrótu ...................................................................................71
Formatowanie za pomoc okna dialogowego Formatowanie komórek ........................................71
Dodawanie wasnych formatów liczbowych .................................................................................73
Rozdzia 3. Podstawowe operacje na arkuszach ............................................ 75
Podstawowe zasady pracy z arkuszami ...............................................................................................75
Praca w oknach Excela .................................................................................................................75
Uaktywnianie arkusza ...................................................................................................................78
Dodawanie nowego arkusza do skoroszytu ..................................................................................78
Usuwanie arkusza .........................................................................................................................79
Zmiana nazwy arkusza ..................................................................................................................79
Zmiana koloru karty arkusza ........................................................................................................80
Przenoszenie i kopiowanie arkuszy ..............................................................................................80
Ukrywanie i odkrywanie arkusza ..................................................................................................81
Okrelanie widoku arkusza .................................................................................................................81
Powikszanie i zmniejszanie arkuszy ...........................................................................................81
Ogldanie skoroszytu w wielu oknach ..........................................................................................83
Porównywanie arkuszy obok siebie ..............................................................................................84
Dzielenie arkusza na okienka ........................................................................................................85
Blokowanie okienek .....................................................................................................................85
Kontrola okienek za pomoc okna czujki .....................................................................................87
Wiersze i kolumny ..............................................................................................................................87
Wstawianie wierszy i kolumn .......................................................................................................87
Usuwanie wierszy i kolumn ..........................................................................................................88
Ukrywanie wierszy i kolumn ........................................................................................................88
Zmiana szerokoci kolumn i wysokoci wierszy ..........................................................................89
Rozdzia 4. Komórki i obszary komórek ........................................................... 91
Komórki a obszary komórek ...............................................................................................................91
Zaznaczanie obszarów ..................................................................................................................92
Zaznaczanie caych wierszy i kolumn ...........................................................................................92
Zaznaczanie obszarów niecigych ...............................................................................................93
Zaznaczanie obszarów w kilku arkuszach ....................................................................................94
Zaznaczanie okrelonych typów komórek ....................................................................................95
Zaznaczanie komórek za pomoc opcji Znajd ............................................................................97
Spis treci
11
Kopiowanie i przenoszenie obszarów .................................................................................................98
Kopiowanie za pomoc polece narzdzia Wstka .....................................................................99
Kopiowanie za pomoc polece menu ..........................................................................................99
Kopiowanie za pomoc klawiszy skrótu .....................................................................................100
Kopiowanie lub przenoszenie przy uyciu metody „przecignij i upu” ..................................100
Kopiowanie do przylegajcych komórek ....................................................................................101
Kopiowanie obszaru komórek do innych arkuszy ......................................................................102
Wklejanie za pomoc schowka Office ........................................................................................102
Wklejanie specjalne ....................................................................................................................104
Zastosowanie okna dialogowego Wklejanie specjalne ...............................................................105
Nadawanie nazw obszarom ...............................................................................................................107
Tworzenie nazw obszarów w skoroszycie ..................................................................................107
Zarzdzanie nazwami .................................................................................................................109
Dodawanie komentarzy do komórek .................................................................................................110
Formatowanie komentarzy .........................................................................................................111
Zmiana ksztatu komentarza .......................................................................................................112
Odczytywanie komentarzy .........................................................................................................112
Drukowanie komentarzy .............................................................................................................112
Ukrywanie i pokazywanie komentarzy .......................................................................................113
Zaznaczanie komentarzy .............................................................................................................114
Edytowanie komentarzy .............................................................................................................114
Usuwanie komentarzy .................................................................................................................114
Rozdzia 5. Tabele ........................................................................................... 115
Czym jest tabela? ..............................................................................................................................115
Tworzenie tabeli ...............................................................................................................................117
Zmiana wygldu tabeli ......................................................................................................................118
Praca z tabelami ................................................................................................................................119
Nawigowanie w obrbie tabeli ....................................................................................................120
Zaznaczanie fragmentów tabeli ..................................................................................................120
Dodawanie nowych wierszy lub kolumn ....................................................................................120
Usuwanie wierszy lub kolumn ....................................................................................................121
Przenoszenie tabeli .....................................................................................................................121
Ustawianie opcji tabeli ................................................................................................................121
Korzystanie z wiersza sumy ........................................................................................................122
Usuwanie z tabeli powielonych wierszy .....................................................................................122
Sortowanie i filtrowanie tabeli ....................................................................................................123
Zamiana tabeli z powrotem na zakres .........................................................................................126
Rozdzia 6. Formatowanie arkusza ................................................................ 127
Narzdzia suce do formatowania ..................................................................................................127
Zastosowanie narzdzi formatujcych karty Narzdzia gówne .................................................128
Zastosowanie paska narzdzi Minipasek ....................................................................................128
Zastosowanie okna dialogowego Formatowanie komórek .........................................................129
Stosowanie rónych czcionek do formatowania arkuszy ..................................................................130
Zmiana wyrównania tekstu ...............................................................................................................132
Opcje wyrównania poziomego ...................................................................................................132
Opcje wyrównania pionowego ...................................................................................................133
Zawijanie i zmniejszanie tekstu ..................................................................................................134
Scalanie komórek arkusza ..........................................................................................................135
Obracanie tekstu .........................................................................................................................135
Okrelanie kierunku tekstu .........................................................................................................136
12
Excel 2010 PL. Biblia
Kolory i cieniowanie .........................................................................................................................136
Obramowanie i krawdzie ................................................................................................................137
Grafika w tle .....................................................................................................................................139
Nadawanie nazw stylom ...................................................................................................................140
Stosowanie stylów ......................................................................................................................140
Modyfikowanie istniejcego stylu ..............................................................................................141
Tworzenie nowych stylów ..........................................................................................................142
Scalanie stylów ...........................................................................................................................142
Zapisywanie stylów w szablonach ..............................................................................................142
Motywy dokumentu ..........................................................................................................................143
Uycie motywu ...........................................................................................................................144
Dostosowywanie motywu ...........................................................................................................145
Rozdzia 7. Pliki Excela .................................................................................... 147
Tworzenie nowego skoroszytu ..........................................................................................................147
Otwieranie istniejcych skoroszytów ................................................................................................149
Uywanie Ulubionych ................................................................................................................151
Filtrowanie nazw plików ............................................................................................................151
Zmiana widoku plików ...............................................................................................................151
Zapisywanie skoroszytu ....................................................................................................................151
Autoodzyskiwanie .............................................................................................................................153
Odzyskiwanie wersji biecego skoroszytu ................................................................................153
Odzyskiwanie danych, które nie zostay zapisane ......................................................................154
Okrelanie hasa ................................................................................................................................154
Organizacja plików ...........................................................................................................................155
Inne opcje skoroszytu .......................................................................................................................157
Sekcja Ostrzeenie o zabezpieczeniach ......................................................................................158
Sekcja Tryb zgodnoci ................................................................................................................158
Sekcja Uprawnienia ....................................................................................................................158
Sekcja Przygotuj do udostpniania .............................................................................................158
Sekcja Wersje .............................................................................................................................159
Zamykanie skoroszytów ...................................................................................................................159
Zabezpieczenie efektów pracy ..........................................................................................................160
Zgodno plików Excela ...................................................................................................................160
Sprawdzanie zgodnoci ...............................................................................................................160
Rozpoznawanie formatów plików Excela 2010 ..........................................................................161
Zapisywanie pliku, który bdzie przetwarzany za pomoc wczeniejszej wersji Excela ............162
Rozdzia 8. Tworzenie i zastosowanie szablonów ........................................ 163
Szablony Excela ................................................................................................................................163
Przegldanie szablonów ..............................................................................................................163
Tworzenie skoroszytu przy uyciu szablonu ..............................................................................164
Modyfikowanie szablonu ............................................................................................................165
Niestandardowe szablony Excela ......................................................................................................166
Szablony domylne .....................................................................................................................167
Tworzenie szablonów niestandardowych ...................................................................................169
Rozdzia 9. Drukowanie arkuszy ..................................................................... 173
Drukowanie jednym klikniciem myszy ...........................................................................................173
Zmiana widoku strony ......................................................................................................................174
Widok normalny .........................................................................................................................175
Widok ukadu stron .....................................................................................................................175
Podgld podziau stron ................................................................................................................177
Spis treci
13
Dostosowywanie typowych ustawie strony .....................................................................................178
Wybieranie drukarki ...................................................................................................................178
Okrelanie obszaru drukowania ..................................................................................................178
Zmiana orientacji strony .............................................................................................................179
Okrelanie rozmiaru papieru .......................................................................................................180
Drukowanie kilku kopii arkusza .................................................................................................180
Dostosowywanie marginesów strony ..........................................................................................180
Podzia na strony ........................................................................................................................181
Drukowanie tytuów wierszy i kolumn .......................................................................................182
Skalowanie wydruku ..................................................................................................................183
Drukowanie linii siatki ................................................................................................................183
Drukowanie nagówków wierszy i kolumn .................................................................................183
Zastosowanie obrazu ta ..............................................................................................................183
Dodawanie do raportów nagówka lub stopki ...................................................................................184
Wybieranie predefiniowanego nagówka lub stopki ...................................................................184
Elementy kodu nagówka i stopki ...............................................................................................186
Inne opcje nagówka i stopki ......................................................................................................186
Kopiowanie ustawie strony midzy arkuszami ...............................................................................187
Ukrywanie niektórych komórek podczas drukowania ......................................................................187
Blokowanie moliwoci drukowania obiektów .................................................................................188
Tworzenie widoków niestandardowych arkusza ...............................................................................188
Cz II Formuy i funkcje ........................................................... 191
Rozdzia 10. Wprowadzenie do formu i funkcji ............................................ 193
Podstawowe informacje o formuach ................................................................................................193
Operatory uywane w formuach ................................................................................................194
Pierwszestwo operatorów w formuach ....................................................................................195
Wykorzystywanie funkcji w formuach ......................................................................................196
Wprowadzanie formu do arkusza .....................................................................................................200
Wprowadzanie formu z klawiatury ............................................................................................201
Wprowadzanie formu przez wskazywanie .................................................................................201
Wklejanie do formu nazw obszarów ..........................................................................................201
Wstawianie funkcji do formu ....................................................................................................202
Kilka informacji na temat wstawiania funkcji ............................................................................203
Edytowanie formu ............................................................................................................................204
Odwoywanie si do komórek w formuach ......................................................................................204
Odwoania wzgldne, bezwzgldne i mieszane ..........................................................................205
Zmiana charakteru odwoania .....................................................................................................206
Odwoania do komórek znajdujcych si poza arkuszem ...........................................................207
Uycie formu w tabelach .................................................................................................................208
Podsumowywanie danych tabeli .................................................................................................208
Zastosowanie formu w tabeli .....................................................................................................209
Odwoywanie si do danych tabeli .............................................................................................210
Poprawianie bdów w formuach .....................................................................................................211
Odwoania cykliczne ..................................................................................................................211
Okrelanie momentu przeliczania formu ...................................................................................213
Zaawansowane techniki nadawania nazw .........................................................................................214
Nadawanie nazw wartociom staym ..........................................................................................214
Nadawanie nazw formuom ........................................................................................................215
Cz wspólna obszarów ............................................................................................................215
Przypisywanie nazw do istniejcych odwoa ............................................................................216
14
Excel 2010 PL. Biblia
Wskazówki dotyczce formu ...........................................................................................................217
Unikanie sztywnego wpisywania wartoci .................................................................................217
Uywanie paska formuy jako kalkulatora ..................................................................................217
Tworzenie dokadnej kopii formuy ............................................................................................218
Przeksztacanie formu w wartoci .............................................................................................218
Rozdzia 11. Formuy uatwiajce prac nad tekstem .................................. 219
Kilka sów na temat tekstu ................................................................................................................219
Funkcje tekstowe ..............................................................................................................................220
Kody znaków ..............................................................................................................................220
Porównanie dwóch acuchów znaków ......................................................................................222
czenie kilku komórek ..............................................................................................................223
Wywietlanie sformatowanych wartoci jako tekstu ..................................................................223
Wywietlanie wartoci w formacie Walutowe jako tekstu ..........................................................224
Powtarzanie znaku lub acucha znaków ....................................................................................225
Histogram tekstowy ....................................................................................................................225
Dodawanie znaków do wartoci .................................................................................................225
Usuwanie niepotrzebnych odstpów oraz znaków, które nie mog by drukowane ...................226
Obliczenie liczby znaków w acuchu ........................................................................................226
Zmiana wielkoci liter ................................................................................................................227
Usuwanie znaków z acucha .....................................................................................................228
Zastpienie tekstu innym tekstem ...............................................................................................228
Przeszukiwanie acucha znaków ...............................................................................................229
Szukanie i zamienianie cigu w acuchu znaków .....................................................................229
Zaawansowane formuy tekstowe .....................................................................................................230
Zliczanie okrelonych znaków w komórce .................................................................................230
Obliczanie czstotliwoci wystpowania okrelonego fragmentu acucha znaków w komórce ......230
Odnalezienie nazwy pliku na jego ciece dostpu .....................................................................230
Odnalezienie pierwszego sowa w acuchu znaków ..................................................................231
Odnalezienie ostatniego sowa w acuchu znaków ...................................................................231
Usunicie pierwszego znaku w acuchu ....................................................................................231
Odnalezienie imienia, drugiego imienia oraz nazwiska ..............................................................232
Usuwanie tytuów sprzed nazwisk ..............................................................................................232
Tworzenie liczby porzdkowej ...................................................................................................232
Obliczanie liczby wyrazów w komórce ......................................................................................233
Rozdzia 12. Praca z datami i czasami ............................................................ 235
W jaki sposób Excel traktuje daty i czas ...........................................................................................235
Rozumienie kolejnych liczb dat ..................................................................................................235
Wpisywanie dat ..........................................................................................................................236
Interpretacja kolejnych liczb czasu .............................................................................................237
Wpisywanie pór dnia ..................................................................................................................238
Formatowanie dat i czasów .........................................................................................................239
Problemy z datami ......................................................................................................................240
Funkcje zwizane z datami ...............................................................................................................242
Wywietlanie biecej daty .........................................................................................................242
Wywietlanie dowolnej daty .......................................................................................................243
Wprowadzanie serii dat ..............................................................................................................243
Konwertowanie tekstu na dat ....................................................................................................244
Wyliczanie liczby dni midzy dwiema datami ...........................................................................245
Ustalanie liczby dni roboczych midzy dwiema datami .............................................................245
Zmiana daty o okrelon liczb dni roboczych ...........................................................................246
Obliczanie liczby lat midzy dwiema datami .............................................................................247
Spis treci
15
Obliczanie wieku osoby ..............................................................................................................247
Wyznaczanie dnia roku ...............................................................................................................248
Wyznaczanie dnia tygodnia ........................................................................................................248
Wyznaczanie daty ostatniej niedzieli ..........................................................................................249
Wyznaczanie pierwszego dnia tygodnia po okrelonej dacie .....................................................249
Wyznaczanie n-tego wystpienia dnia tygodnia w miesicu ......................................................249
Obliczanie dni witecznych .......................................................................................................249
Wyznaczanie ostatniego dnia w miesicu ...................................................................................251
Ustalanie, czy dany rok jest rokiem przestpnym .......................................................................252
Wyznaczanie kwartau roku ........................................................................................................252
Funkcje zwizane z jednostkami czasu .............................................................................................252
Wywietlanie czasu biecego ....................................................................................................253
Wywietlanie dowolnego czasu ..................................................................................................253
Obliczanie rónicy midzy dwoma czasami ...............................................................................254
Sumy czasu wiksze ni 24 godziny ...........................................................................................254
Konwertowanie czasu wojskowego ............................................................................................256
Konwertowanie godzin, minut i sekund z zapisu dziesitnego na wartoci czasu ......................256
Dodawanie godzin, minut lub sekund do okrelonej wartoci czasu ..........................................257
Zaokrglanie wartoci czasu .......................................................................................................257
Praca z wartociami czasu nieoznaczajcymi godziny w cigu dnia ..........................................258
Rozdzia 13. Tworzenie formu, które zliczaj i sumuj ................................ 259
Zliczanie i sumowanie komórek arkusza ..........................................................................................259
Podstawowe formuy zliczajce ........................................................................................................260
Wyznaczanie liczby wszystkich komórek ..................................................................................261
Wyznaczanie liczby pustych komórek ........................................................................................262
Wyznaczanie liczby niepustych komórek ...................................................................................262
Wyznaczanie liczby komórek z wartociami liczbowymi ..........................................................262
Wyznaczanie liczby komórek tekstowych ..................................................................................262
Wyznaczanie liczby komórek niezawierajcych tekstu ..............................................................262
Zliczanie wartoci logicznych .....................................................................................................263
Zliczanie wartoci bdów w obszarze ........................................................................................263
Zaawansowane formuy zliczajce ....................................................................................................263
Zliczanie komórek przy uyciu funkcji LICZ.JEELI ...............................................................263
Zliczanie komórek przy uyciu wielu kryteriów .........................................................................263
Okrelanie, ile razy pojawi si najczciej wystpujcy wpis ...................................................266
Zliczanie wystpie okrelonego tekstu ......................................................................................267
Ustalanie liczby rónych (unikalnych) wartoci w obszarze .......................................................268
Tworzenie rozkadu czstoci .....................................................................................................268
Formuy sumowania ..........................................................................................................................272
Sumowanie wszystkich komórek z zakresu ................................................................................273
Liczenie sum skumulowanych ....................................................................................................273
Sumowania „n” najwikszych wartoci ......................................................................................274
Sumy warunkowe z jednym kryterium .............................................................................................275
Sumowanie tylko wartoci ujemnych .........................................................................................275
Sumowanie wartoci na podstawie innego obszaru ....................................................................276
Sumowanie wartoci na podstawie porówna tekstów ...............................................................276
Sumowanie wartoci na podstawie porówna dat .......................................................................276
Sumy warunkowe z wieloma kryteriami ...........................................................................................276
Uywanie kryterium Oraz ...........................................................................................................276
Uywanie kryterium Lub ............................................................................................................277
Uywanie kryterium Oraz i Lub .................................................................................................277
16
Excel 2010 PL. Biblia
Rozdzia 14. Tworzenie formu, które wyszukuj wartoci .......................... 279
Wprowadzenie do formu wyszukujcych ........................................................................................279
Funkcje zwizane z wyszukiwaniem ................................................................................................280
Podstawowe formuy wyszukiwania .................................................................................................280
Funkcja WYSZUKAJ.PIONOWO .............................................................................................280
Funkcja WYSZUKAJ.POZIOMO ..............................................................................................282
Funkcja WYSZUKAJ .................................................................................................................283
czne uycie funkcji PODAJ.POZYCJ i INDEKS ................................................................284
Specjalne formuy wyszukujce ........................................................................................................285
Wyszukiwanie dokadnej wartoci ..............................................................................................286
Wyszukiwanie wartoci z lewej strony .......................................................................................286
Przeprowadzanie wyszukiwania z uwzgldnieniem wielkoci liter ............................................287
Wyszukiwanie wartoci w wicej ni jednej tabeli wyszukiwania .............................................288
Ustalanie ocen na podstawie liczby punktów z testu ..................................................................288
Obliczanie redniej oceny z punktów przyznawanych za kursy .................................................289
Wyszukiwanie dwóch wartoci ...................................................................................................290
Wyszukiwanie wartoci w dwóch kolumnach ............................................................................291
Ustalanie adresu komórki w obszarze na podstawie jej wartoci ................................................291
Wyszukiwanie wartoci najbliszej wartoci szukanej ...............................................................292
Rozdzia 15. Tworzenie formu do zastosowa finansowych ...................... 295
Warto pienidza w czasie ..............................................................................................................295
Obliczenia dla poyczek ...................................................................................................................296
Funkcje Excela do wyliczania informacji o poyczce ................................................................297
Przykad oblicze dla kredytu .....................................................................................................299
Spata zaduenia z karty kredytowej ..........................................................................................299
Tworzenie harmonogramu spaty poyczki ................................................................................301
Podsumowywanie rónych opcji poyczek za pomoc tabel danych .........................................301
Obliczenia dla poyczki z nieregularnymi spatami ....................................................................304
Obliczenia dla inwestycji ..................................................................................................................305
Warto przysza pojedynczego depozytu ..................................................................................306
Warto przysza serii depozytów ...............................................................................................308
Obliczenia dla amortyzacji ................................................................................................................310
Rozdzia 16. Wprowadzenie do formu tablicowych ..................................... 313
Pojcie formu tablicowych ..............................................................................................................313
Formua tablicowa w wielu komórkach ......................................................................................314
Formua tablicowa w jednej komórce .........................................................................................315
Tworzenie staej tablicowej ........................................................................................................316
Elementy staej tablicowej ..........................................................................................................316
Pojcie wymiarów tablicy .................................................................................................................317
Jednowymiarowe tablice poziome ..............................................................................................317
Jednowymiarowe tablice pionowe ..............................................................................................317
Tablice dwuwymiarowe ..............................................................................................................318
Nazywanie staych tablicowych ........................................................................................................319
Praca z formuami tablicowymi ........................................................................................................319
Wpisywanie formuy tablicowej .................................................................................................320
Zaznaczanie zakresu formuy tablicowej ....................................................................................320
Edytowanie formuy tablicowej ..................................................................................................320
Zwikszanie lub zmniejszanie obszaru formuy tablicowej, umieszczonej w wielu komórkach ....321
Uywanie wielokomórkowych formu tablicowych ..........................................................................322
Tworzenie tablicy na podstawie wartoci z obszaru ...................................................................322
Tworzenie staej tablicowej na podstawie wartoci z obszaru ....................................................322
Spis treci
17
Przeprowadzanie dziaa na tablicach ........................................................................................322
Uywanie funkcji dla tablicy ......................................................................................................323
Transponowanie tablicy ..............................................................................................................324
Tworzenie tablicy skadajcej si z kolejnych liczb cakowitych ...............................................324
Uywanie jednokomórkowych formu tablicowych ..........................................................................325
Liczenie znaków w obszarze ......................................................................................................325
Sumowanie trzech najmniejszych wartoci obszaru ...................................................................326
Liczenie komórek tekstowych w zakresie ...................................................................................327
Eliminowanie formu porednich ................................................................................................327
Uywanie staych tablicowych zamiast odwoa do obszaru ......................................................328
Rozdzia 17. Efekty magiczne,
uzyskiwane przy uyciu formu tablicowych ........................... 331
Praca z jednokomórkowymi formuami tablicowymi .......................................................................331
Sumowanie wartoci z obszaru, w którym s bdy ....................................................................332
Liczenie bdów w zakresie ........................................................................................................332
Sumowanie n najwikszych wartoci w zakresie ........................................................................333
Liczenie redniej z wykluczeniem zer ........................................................................................333
Ustalanie, czy okrelona warto wystpuje w zakresie .............................................................334
Ustalanie liczby rónic midzy dwoma zakresami .....................................................................335
Zwracanie pozycji najwikszej wartoci z zakresu .....................................................................335
Znajdowanie wiersza n-tego wystpienia wartoci w zakresie ...................................................335
Zwracanie najduszego tekstu z zakresu ...................................................................................336
Ustalanie, czy obszar zawiera prawidowe wpisy .......................................................................336
Sumowanie cyfr liczby cakowitej ..............................................................................................336
Sumowanie wartoci zaokrglonych ...........................................................................................337
Sumowanie kadej n-tej wartoci z zakresu ................................................................................338
Usuwanie z tekstu wszystkich znaków poza cyframi .................................................................339
Ustalanie najbliszej wartoci w zakresie ...................................................................................339
Zwracanie ostatniej wartoci z kolumny .....................................................................................339
Zwracanie ostatniej wartoci z wiersza .......................................................................................340
Nadawanie rang przy uyciu formuy tablicowej ........................................................................340
Praca z wielokomórkowymi formuami tablicowymi .......................................................................341
Zwracanie wycznie wartoci dodatnich z zakresu ....................................................................342
Zwracanie komórek niepustych z zakresu ..................................................................................342
Odwracanie kolejnoci komórek w zakresie ...............................................................................343
Dynamiczne porzdkowanie zakresu wartoci ...........................................................................343
Zwracanie listy wartoci unikalnych z zakresu ...........................................................................344
Wywietlanie kalendarza w zakresie ..........................................................................................344
Cz III Tworzenie wykresów i grafiki ...................................... 347
Rozdzia 18. Podstawowe techniki tworzenia wykresów ............................ 349
Co to jest wykres? .............................................................................................................................349
Jak Excel posuguje si wykresami? .................................................................................................350
Wykresy osadzone ......................................................................................................................352
Arkusze wykresów ......................................................................................................................352
Tworzenie wykresu ...........................................................................................................................353
Tworzenie i dostosowywanie wykresu ..............................................................................................353
Zaznaczanie danych ....................................................................................................................353
Wybieranie typu wykresu ...........................................................................................................354
Eksperymentowanie z rónymi ukadami ...................................................................................354
Sprawdzanie innego widoku danych ...........................................................................................355
18
Excel 2010 PL. Biblia
Sprawdzanie innych typów wykresów ........................................................................................355
Sprawdzanie innych stylów wykresu ..........................................................................................356
Praca z wykresami ............................................................................................................................357
Zmienianie rozmiaru wykresu ....................................................................................................357
Przenoszenie wykresu .................................................................................................................357
Kopiowanie wykresu ..................................................................................................................357
Usuwanie wykresu ......................................................................................................................358
Dodawanie elementów wykresu .................................................................................................358
Przenoszenie i usuwanie elementów wykresu ............................................................................358
Formatowanie elementów wykresu ............................................................................................358
Drukowanie wykresów ...............................................................................................................359
Pojcie typów wykresów ..................................................................................................................360
Wybieranie typu wykresu ...........................................................................................................360
Wykresy kolumnowe ..................................................................................................................361
Wykresy supkowe ......................................................................................................................363
Wykresy liniowe .........................................................................................................................363
Wykresy koowe .........................................................................................................................364
Wykresy XY ...............................................................................................................................366
Wykresy warstwowe ...................................................................................................................367
Wykresy piercieniowe ...............................................................................................................368
Wykresy radarowe ......................................................................................................................369
Wykresy powierzchniowe ...........................................................................................................370
Wykresy bbelkowe ....................................................................................................................371
Wykresy giedowe ......................................................................................................................372
Wicej informacji ..............................................................................................................................372
Rozdzia 19. Zaawansowane techniki tworzenia wykresów ........................ 375
Zaznaczanie elementów wykresu ......................................................................................................375
Zaznaczanie przy uyciu myszy .................................................................................................376
Zaznaczanie przy uyciu klawiatury ...........................................................................................377
Zaznaczanie przy uyciu kontrolki Elementy wykresu ...............................................................377
Moliwoci modyfikacji elementów wykresu oferowane przez interfejs uytkownika ....................378
Zastosowanie okna dialogowego Formatowanie ........................................................................378
Uycie narzdzia Wstka ..........................................................................................................378
Uycie paska Minipasek .............................................................................................................379
Modyfikowanie obszaru wykresu .....................................................................................................379
Modyfikowanie obszaru krelenia ....................................................................................................380
Praca z tytuami wykresu ..................................................................................................................381
Zmiana legendy .................................................................................................................................381
Zmiana linii siatki .............................................................................................................................382
Modyfikowanie osi ...........................................................................................................................383
Praca na seriach danych ....................................................................................................................387
Usuwanie serii danych ................................................................................................................388
Dodawanie nowej serii danych do wykresu ................................................................................389
Zmiana danych uywanych przez seri .......................................................................................389
Wywietlanie etykiet danych na wykresie ..................................................................................391
Obsugiwanie brakujcych danych .............................................................................................392
Dodawanie supków bdów .......................................................................................................393
Dodawanie linii trendu ................................................................................................................394
Modyfikowanie wykresów 3-W ..................................................................................................394
Tworzenie wykresów zoonych .................................................................................................395
Wywietlanie tabeli danych ........................................................................................................397
Spis treci
19
Tworzenie szablonów wykresów ......................................................................................................398
Sztuczki z wykresami .......................................................................................................................398
Tworzenie wykresów rysunkowych ............................................................................................399
Tworzenie wykresu podobnego do termometru ..........................................................................399
Tworzenie wykresu w ksztacie wskanika ................................................................................400
Warunkowe wywietlanie kolorów na wykresie kolumnowym ..................................................401
Tworzenie histogramu porównawczego .....................................................................................402
Tworzenie wykresu Gantta .........................................................................................................403
Tworzenie wykresów funkcji matematycznych z jedn zmienn ...............................................404
Tworzenie wykresów funkcji matematycznych z dwiema zmiennymi .......................................405
Rozdzia 20. Wizualizacja danych
przy uyciu formatowania warunkowego ............................... 407
Formatowanie warunkowe ................................................................................................................407
Okrelanie formatowania warunkowego ...........................................................................................409
Dostpne typy formatowania ......................................................................................................409
Tworzenie wasnych regu ..........................................................................................................410
Formaty warunkowe uywajce grafiki ............................................................................................411
Zastosowanie pasków danych .....................................................................................................411
Zastosowanie skali kolorów ........................................................................................................413
Zastosowanie zestawu ikon .........................................................................................................415
Tworzenie regu bazujcych na formule ...........................................................................................417
Odwoania wzgldne i bezwzgldne ...........................................................................................418
Przykady formu formatowania warunkowego ..........................................................................419
Zastosowanie formatów warunkowych .............................................................................................421
Zarzdzanie reguami ..................................................................................................................421
Kopiowanie komórek zawierajcych formatowanie warunkowe ................................................422
Usuwanie formatowania warunkowego ......................................................................................422
Lokalizacja komórek zawierajcych formatowanie warunkowe .................................................422
Rozdzia 21. Tworzenie wykresów przebiegu w czasie ................................ 423
Typy wykresów przebiegu w czasie ..................................................................................................423
Tworzenie wykresów przebiegu w czasie .........................................................................................424
Dostosowywanie wykresów przebiegu w czasie ...............................................................................426
Wyznaczanie rozmiaru wykresów przebiegu w czasie ...............................................................426
Obsuga ukrytych lub brakujcych danych .................................................................................427
Zmiana typu wykresu przebiegu w czasie ..................................................................................427
Zmiana koloru i szerokoci linii wykresu przebiegu w czasie ....................................................427
Wyrónianie wybranych punktów danych ..................................................................................428
Dostosowywanie skalowania osi wykresu przebiegu w czasie ...................................................428
Symulowana linia referencyjna ...................................................................................................429
Wykorzystanie osi dat .......................................................................................................................430
Wykresy przebiegu w czasie uaktualniane automatycznie ................................................................431
Wywietlanie wykresu przebiegu w czasie dla dynamicznego zakresu komórek .............................431
Rozdzia 22. Dodawanie rysunków i grafiki do arkuszy ............................... 433
Uywanie ksztatów ..........................................................................................................................433
Dodawanie ksztatu .....................................................................................................................433
Dodawanie tekstu do ksztatu .....................................................................................................435
Formatowanie ksztatów .............................................................................................................435
Grupowanie obiektów .................................................................................................................437
Wyrównywanie i ustalanie odlegoci midzy obiektami ...........................................................437
20
Excel 2010 PL. Biblia
Modyfikowanie ksztatów ...........................................................................................................438
Drukowanie obiektów .................................................................................................................439
Zastosowanie obiektów SmartArt .....................................................................................................440
Wstawianie obiektu SmartArt .....................................................................................................440
Dostosowywanie obiektu SmartArt ............................................................................................441
Zmiana ukadu ............................................................................................................................442
Zmiana stylu ...............................................................................................................................442
Wicej informacji na temat obiektów SmartArt .........................................................................443
Zastosowanie obiektów WordArt ......................................................................................................443
Praca z innymi plikami graficznymi .................................................................................................444
O plikach graficznych .................................................................................................................444
Uywanie panelu zada Clipart ..................................................................................................444
Wstawianie plików graficznych ..................................................................................................445
Wstawianie zrzutów ekranu ........................................................................................................446
Uywanie obrazu jako ta arkusza ..............................................................................................446
Edytor równa ...................................................................................................................................446
Cz IV Zaawansowane waciwoci Excela ............................. 449
Rozdzia 23. Dostosowywanie interfejsu uytkownika Excela ..................... 451
Dostosowywanie paska narzdzi Szybki dostp ...............................................................................451
Pasek narzdzi Szybki dostp .....................................................................................................451
Dodawanie nowych polece do paska Szybki dostp .......................................................................452
Inne operacje zwizane z paskiem Szybki dostp .............................................................................454
Dostosowywanie Wstki .................................................................................................................455
Po co dostosowywa Wstk? ..................................................................................................455
Moliwoci dostosowywania ......................................................................................................455
Sposoby dostosowywania Wstki .............................................................................................456
Rozdzia 24. Uywanie niestandardowych formatów liczbowych ............... 459
Formatowanie liczbowe ....................................................................................................................459
Automatyczne formatowanie liczb .............................................................................................460
Formatowanie liczb przy uyciu narzdzia Wstka ..................................................................460
Uywanie klawiszy skrótów do formatowania liczb ...................................................................461
Uywanie okna dialogowego Formatowanie komórek do formatowania liczb ...........................461
Tworzenie niestandardowego formatu liczbowego ...........................................................................462
Czci kodu formatowania liczbowego ......................................................................................464
Kody niestandardowego formatowania liczbowego ...................................................................464
Przykady niestandardowych formatów liczbowych .........................................................................465
Skalowanie wartoci ...................................................................................................................465
Dodawanie zer przed liczbami ....................................................................................................469
Wywietlanie uamków ...............................................................................................................469
Wywietlanie znaku minus po prawej ........................................................................................471
Formatowanie dat i czasów .........................................................................................................471
Wywietlanie cznie tekstu i liczb .............................................................................................471
Ukrywanie pewnych typów danych ............................................................................................472
Wypenianie komórki powtarzajcym si znakiem .....................................................................472
Rozdzia 25. Sprawdzanie poprawnoci danych ............................................ 473
Sprawdzanie poprawnoci danych ....................................................................................................473
Okrelanie kryteriów sprawdzania poprawnoci danych ..................................................................474
Typy dostpnych kryteriów sprawdzania poprawnoci danych ........................................................474
Spis treci
21
Tworzenie list rozwijanych ...............................................................................................................476
Zastosowanie formu przy definiowaniu zasad sprawdzania poprawnoci danych ...........................477
Odwoania do komórek .....................................................................................................................477
Przykady formu sprawdzania poprawnoci danych ........................................................................478
Akceptowanie tylko tekstu ..........................................................................................................479
Akceptowanie wartoci tylko wtedy,
gdy jest ona wiksza od wartoci znajdujcej si w poprzedniej komórce .............................479
Akceptowanie tylko unikatowych wartoci ................................................................................479
Akceptowanie tylko cigów znaków zawierajcych konkretny znak .........................................479
Akceptowanie tylko dat, które wskazuj okrelony dzie tygodnia ............................................480
Akceptowanie tylko tych wartoci, które nie przekraczaj sumy ................................................480
Tworzenie listy zalenej .............................................................................................................480
Rozdzia 26. Tworzenie i stosowanie konspektów ....................................... 483
Podstawowe informacje na temat konspektów ..................................................................................483
Tworzenie konspektu ........................................................................................................................486
Przygotowanie danych ................................................................................................................486
Automatyczne tworzenie konspektu ...........................................................................................487
Rczne tworzenie konspektu .......................................................................................................487
Praca z konspektami .........................................................................................................................488
Wywietlanie poziomów .............................................................................................................488
Dodawanie danych do konspektu ...............................................................................................489
Usuwanie konspektu ...................................................................................................................489
Ukrywanie symboli konspektu ...................................................................................................489
Rozdzia 27. czenie i konsolidacja arkuszy ................................................. 491
czenie skoroszytów .......................................................................................................................491
Tworzenie formu odwoa zewntrznych ........................................................................................492
Zapoznanie si ze skadni formuy czcej ..............................................................................492
Tworzenie formuy czcej metod wskazania ..........................................................................493
Wklejanie czy ..........................................................................................................................493
Praca z formuami zewntrznych odwoa .......................................................................................493
Tworzenie czy w niezapisanych skoroszytach .........................................................................494
Otwieranie skoroszytu przy uyciu formu odwoa zewntrznych ............................................494
Okrelanie domylnego zachowania ...........................................................................................495
Aktualizacja czy .......................................................................................................................495
Zmiana róda cza ....................................................................................................................496
Przerywanie czy .......................................................................................................................496
Unikanie potencjalnych problemów z formuami odwoa zewntrznych ........................................496
Zmiana nazwy lub przenoszenie ródowego skoroszytu ...........................................................497
Zastosowanie polecenia Zapisz jako ...........................................................................................497
Modyfikowanie ródowego skoroszytu .....................................................................................497
cza poredniczce ...................................................................................................................498
Konsolidacja arkuszy ........................................................................................................................498
Konsolidacja arkuszy przy uyciu formu ...................................................................................499
Konsolidacja arkuszy przy uyciu polecenia Wklej specjalnie ...................................................499
Konsolidacja arkuszy przy uyciu polecenia Konsoliduj ............................................................500
Odwieanie konsolidacji ...........................................................................................................503
Wicej informacji na temat konsolidacji .....................................................................................504
22
Excel 2010 PL. Biblia
Rozdzia 28. Wspópraca programu Excel z internetem ............................... 505
Zapoznanie si ze sposobem uywania przez program Excel jzyka HTML ....................................505
Róne formaty internetowe ...............................................................................................................505
Tworzenie pliku HTML ..............................................................................................................506
Tworzenie pojedynczego pliku strony WWW ............................................................................507
Otwieranie pliku HTML ...................................................................................................................508
Praca z hiperczami .........................................................................................................................509
Wstawianie hipercza ................................................................................................................509
Zastosowanie hiperczy .............................................................................................................510
Zastosowanie kwerend internetowych ..............................................................................................510
Pozostae funkcje umoliwiajce korzystanie z internetu .................................................................511
Rozdzia 29. Wspódzielenie danych z innymi aplikacjami pakietu Office .... 515
Kopiowanie i wklejanie ....................................................................................................................515
Kopiowanie danych z programu Excel do edytora Word ..................................................................516
Wklejanie statycznych informacji ...............................................................................................517
Wklejanie cza ...........................................................................................................................519
Osadzanie obiektów w arkuszu .........................................................................................................520
Osadzanie dokumentów Worda ..................................................................................................521
Osadzanie innego typu dokumentów ..........................................................................................521
Osadzanie skoroszytu Excela w dokumencie Worda ........................................................................521
Osadzanie skoroszytu w dokumencie Worda przez kopiowanie .................................................522
Osadzanie w dokumencie Worda zapisanego skoroszytu ...........................................................523
Tworzenie w edytorze Word nowego obiektu programu Excel ..................................................523
Rozdzia 30. Zastosowanie programu Excel w grupie roboczej ................... 525
Zastosowanie programu Excel w sieci ..............................................................................................525
Rezerwowanie plików .......................................................................................................................525
Wspódzielenie skoroszytów .............................................................................................................527
Wspódzielone skoroszyty ..........................................................................................................527
Udostpnianie skoroszytu ...........................................................................................................528
Zarzdzanie zaawansowanymi ustawieniami udostpniania .......................................................528
ledzenie zmian dokonywanych w skoroszycie ................................................................................530
Wczanie i wyczanie funkcji ledzenia zmian ........................................................................530
Przegldanie zmian .....................................................................................................................531
Rozdzia 31. Ochrona danych .......................................................................... 533
Typy ochrony ....................................................................................................................................533
Ochrona arkusza ...............................................................................................................................533
Odblokowywanie komórek .........................................................................................................534
Opcje ochrony arkusza ................................................................................................................535
Przypisywanie uprawnie uytkownika ......................................................................................536
Ochrona skoroszytu ..........................................................................................................................536
Wymóg podania hasa w celu otwarcia skoroszytu .....................................................................536
Ochrona struktury skoroszytu .....................................................................................................537
Ochrona okien skoroszytu ..........................................................................................................538
Ochrona projektu Visual Basic .........................................................................................................538
Powizane zagadnienia .....................................................................................................................539
Zapisywanie arkusza jako pliku PDF ..........................................................................................539
Finalizowanie skoroszytu ...........................................................................................................540
Inspekcja skoroszytu ...................................................................................................................540
Zastosowanie cyfrowego podpisu ...............................................................................................541
Spis treci
23
Rozdzia 32. Sposoby unikania bdów w arkuszach .................................... 543
Identyfikacja i usuwanie bdów formu ...........................................................................................543
Brakujce nawiasy okrge .........................................................................................................544
Komórki wypenione znakami # .................................................................................................544
Puste komórki wcale takimi nie s ..............................................................................................545
Nadmiarowe znaki spacji ............................................................................................................546
Formuy zwracajce bd ............................................................................................................546
Problemy z odwoaniami bezwzgldnymi i wzgldnymi ............................................................549
Problemy z kolejnoci stosowania operatorów .........................................................................550
Formuy nie s obliczane ............................................................................................................550
Wartoci rzeczywiste i wywietlane ...........................................................................................551
Bdy zwizane z liczbami zmiennoprzecinkowymi ..................................................................551
Bdy zwizane z czami „fantomami” .....................................................................................552
Zastosowanie narzdzi inspekcji programu Excel .............................................................................553
Identyfikowanie komórek okrelonego typu ...............................................................................553
Przegldanie formu ....................................................................................................................554
ledzenie powiza pomidzy komórkami .................................................................................554
ledzenie wartoci bdów ..........................................................................................................556
Usuwanie bdów odwoa cyklicznych .....................................................................................556
Zastosowanie funkcji sprawdzania bdów w tle ........................................................................556
Zastosowanie narzdzia programu Excel szacujcego formuy ..................................................558
Szukanie i zastpowanie ...................................................................................................................559
Szukanie informacji ....................................................................................................................559
Zastpowanie danych ..................................................................................................................560
Wyszukiwanie formatowania ......................................................................................................560
Sprawdzanie pisowni w arkuszach ....................................................................................................561
Zastosowanie autokorekty .................................................................................................................562
Cz V Analiza danych ............................................................... 565
Rozdzia 33. Pobieranie danych z zewntrznych plików baz danych .......... 567
Zewntrzne pliki baz danych ............................................................................................................567
Importowanie tabel Accessa .............................................................................................................568
Pobieranie danych za pomoc programu Microsoft Query — przykad ...........................................570
Plik bazy danych .........................................................................................................................570
Zadanie .......................................................................................................................................570
Uywanie kreatora kwerend .......................................................................................................572
Praca z danymi pobranymi za pomoc kwerendy .............................................................................575
Ustawianie waciwoci zakresu danych zewntrznych ..............................................................576
Odwieanie kwerendy ...............................................................................................................576
Usuwanie kwerendy ....................................................................................................................577
Zmiana kwerendy .......................................................................................................................577
Uywanie Microsoft Query bez kreatora kwerend ............................................................................577
Rczne tworzenie kwerendy .......................................................................................................577
Uywanie wielu tabel z bazy danych ..........................................................................................578
Dodawanie i edytowanie rekordów w zewntrznych tabelach baz danych .................................579
Formatowanie danych .................................................................................................................579
Jak dowiedzie si wicej o programie Query ..................................................................................579
24
Excel 2010 PL. Biblia
Rozdzia 34. Tabele przestawne — wprowadzenie ....................................... 581
Tabele przestawne .............................................................................................................................581
Tabela przestawna na przykadzie ..............................................................................................582
Dane odpowiednie dla tabeli przestawnej ...................................................................................583
Tworzenie tabeli przestawnej ............................................................................................................585
Okrelanie danych ......................................................................................................................586
Okrelanie lokalizacji tabeli przestawnej ....................................................................................586
Okrelanie ukadu tabeli przestawnej .........................................................................................586
Formatowanie tabeli przestawnej ................................................................................................587
Modyfikowanie tabeli przestawnej .............................................................................................590
Dodatkowe przykady tabel przestawnych ........................................................................................590
Pytanie 1. ....................................................................................................................................591
Pytanie 2. ....................................................................................................................................593
Pytanie 3. ....................................................................................................................................593
Pytanie 4. ....................................................................................................................................594
Pytanie 5. ....................................................................................................................................594
Pytanie 6. ....................................................................................................................................595
Pytanie 7. ....................................................................................................................................596
Wicej informacji ..............................................................................................................................596
Rozdzia 35. Analiza danych za pomoc tabel przestawnych ...................... 597
Praca z danymi nienumerycznymi ....................................................................................................597
Grupowanie pozycji tabeli przestawnej ............................................................................................598
Przykad rcznego grupowania ...................................................................................................599
Przykady automatycznego grupowania .....................................................................................600
Tworzenie rozkadu czstoci ...........................................................................................................602
Wstawianie do tabeli pól i elementów obliczeniowych ....................................................................604
Tworzenie pola obliczeniowego .................................................................................................605
Wstawianie elementów obliczeniowych .....................................................................................607
Filtrowanie tabel przestawnych przy uyciu fragmentatorów ...........................................................608
Odwoywanie si do komórek w obrbie tabeli przestawnej ............................................................611
Tworzenie wykresów przestawnych .................................................................................................611
Przykad wykresu przestawnego .................................................................................................612
Dodatkowe informacje na temat wykresów przestawnych .........................................................614
Kolejny przykad tabeli przestawnej .................................................................................................615
Tworzenie raportu tabeli przestawnej ...............................................................................................617
Rozdzia 36. Analiza co-jeli ............................................................................ 619
Przykad analizy co-jeli ...................................................................................................................619
Typy analiz co-jeli ...........................................................................................................................620
Rczna analiza co-jeli ......................................................................................................................620
Tworzenie tabel danych ....................................................................................................................621
Tworzenie tabeli opartej na jednej komórce wejciowej ............................................................621
Tworzenie tabeli opartej na dwóch komórkach wejciowych .....................................................623
Meneder scenariuszy .......................................................................................................................625
Definiowanie scenariuszy ...........................................................................................................626
Wywietlanie scenariuszy ...........................................................................................................628
Modyfikowanie scenariuszy .......................................................................................................628
Scalanie scenariuszy ...................................................................................................................629
Generowanie raportu scenariusza ...............................................................................................629
Spis treci
25
Rozdzia 37. Analiza danych przy uyciu Szukanie wyniku i Solvera ........... 631
Odwrotna analiza co-jeli ..................................................................................................................631
Szukanie wyniku jednej komórki ......................................................................................................632
Przykad szukania wyniku ..........................................................................................................632
Wicej o szukaniu wyniku ..........................................................................................................633
Narzdzie Solver ...............................................................................................................................634
Do jakich zada wykorzystamy Solvera? ...................................................................................634
Prosty przykad Solvera ..............................................................................................................634
Wicej o Solverze .......................................................................................................................637
Przykady wykorzystania narzdzia Solver .......................................................................................639
Rozwizywanie równowanych równa liniowych ....................................................................639
Minimalizacja kosztów wysyki .................................................................................................640
Przydzia zasobów ......................................................................................................................642
Optymalizacja portfela inwestycyjnego ......................................................................................644
Rozdzia 38. Analiza danych za pomoc dodatku Analysis ToolPak ............. 647
Analysis ToolPak — przegld moliwoci analizy danych ..............................................................647
Instalowanie dodatku Analysis ToolPak ...........................................................................................648
Uywanie narzdzi analizy danych ...................................................................................................648
Narzdzia dodatku Analysis ToolPak ...............................................................................................649
Analiza wariancji ........................................................................................................................649
Korelacja .....................................................................................................................................650
Kowariancja ................................................................................................................................650
Statystyka opisowa .....................................................................................................................650
Wygadzanie wykadnicze ..........................................................................................................650
Test F (z dwiema próbami dla wariancji) ...................................................................................651
Analiza Fouriera .........................................................................................................................651
Histogram ...................................................................................................................................651
rednia ruchoma .........................................................................................................................652
Generowanie liczb pseudolosowych ...........................................................................................653
Ranga i percentyl ........................................................................................................................654
Regresja ......................................................................................................................................654
Próbkowanie ...............................................................................................................................655
Test t ...........................................................................................................................................655
Test z (z dwiema próbami dla rednich) .....................................................................................655
Cz VI Program Excel i programowanie w jzyku VBA .......... 657
Rozdzia 39. Podstawowe informacje na temat jzyka programowania
Visual Basic for Applications ..................................................... 659
Podstawowe informacje na temat makr jzyka VBA ........................................................................659
Wywietlanie karty Deweloper .........................................................................................................660
Bezpieczestwo makr .......................................................................................................................661
Zapisywanie skoroszytów zawierajcych makra ...............................................................................661
Dwa typy makr jzyka VBA .............................................................................................................662
Procedury Sub jzyka VBA ........................................................................................................662
Funkcje jzyka VBA ...................................................................................................................663
Tworzenie makr jzyka VBA ............................................................................................................664
Rejestrowanie makr jzyka VBA ................................................................................................664
Wicej informacji na temat rejestracji makr jzyka VBA ...........................................................670
Pisanie kodu ródowego w jzyku VBA ...................................................................................672
Wicej informacji na temat jzyka VBA ..........................................................................................680
26
Excel 2010 PL. Biblia
Rozdzia 40. Tworzenie niestandardowych funkcji arkusza ......................... 683
Podstawowe informacje na temat funkcji jzyka VBA .....................................................................683
Przykad wprowadzajcy ..................................................................................................................684
Funkcja niestandardowa .............................................................................................................684
Zastosowanie funkcji w arkuszu .................................................................................................684
Analiza funkcji niestandardowej .................................................................................................684
Procedury Function ...........................................................................................................................686
Wywoywanie procedur Function .....................................................................................................686
Wywoywanie funkcji niestandardowych z procedury ...............................................................686
Zastosowanie funkcji niestandardowych w formule arkusza ......................................................686
Argumenty procedury Function ........................................................................................................687
Funkcja pozbawiona argumentów ..............................................................................................688
Funkcja zawierajca jeden argument ..........................................................................................688
Kolejna funkcja z jednym argumentem ......................................................................................689
Funkcja zawierajca dwa argumenty ..........................................................................................690
Funkcja opierajca si na argumencie bdcym zakresem .........................................................690
Usuwanie bdów funkcji niestandardowych ....................................................................................691
Wklejanie funkcji niestandardowych ................................................................................................692
Dodatkowe informacje ......................................................................................................................693
Rozdzia 41. Tworzenie okien dialogowych ................................................... 695
Dlaczego s tworzone okna dialogowe? ...........................................................................................695
Alternatywy dla okien dialogowych .................................................................................................696
Funkcja InputBox .......................................................................................................................696
Funkcja MsgBox .........................................................................................................................697
Podstawowe informacje na temat tworzenia okien dialogowych ......................................................699
Praca z formularzami UserForm .................................................................................................699
Dodawanie kontrolek ..................................................................................................................699
Modyfikacja waciwoci kontrolki ............................................................................................701
Obsuga zdarze .........................................................................................................................701
Wywietlanie formularza UserForm ...........................................................................................702
Przykad formularza UserForm .........................................................................................................702
Tworzenie formularza UserForm ................................................................................................702
Testowanie formularza UserForm ..............................................................................................703
Tworzenie procedury obsugujcej zdarzenie .............................................................................704
Kolejny przykad formularza UserForm ...........................................................................................704
Tworzenie formularza UserForm ................................................................................................705
Testowanie formularza UserForm ..............................................................................................706
Tworzenie procedur obsugujcych zdarzenia ............................................................................707
Testowanie formularza UserForm ..............................................................................................708
Udostpnianie makra przy uyciu przycisku arkusza .................................................................708
Udostpnianie makra na pasku narzdzi Szybki dostp ..............................................................709
Wicej informacji na temat okien dialogowych ................................................................................709
Dodawanie skrótów klawiaturowych ..........................................................................................709
Kontrolowanie kolejnoci uaktywniania kontrolek przez klawisz Tab .......................................710
Uzyskanie dodatkowych informacji ..................................................................................................710
Rozdzia 42. Zastosowanie w arkuszu kontrolek okien dialogowych .......... 711
Dlaczego stosuje si kontrolki w arkuszu? ........................................................................................711
Zastosowanie kontrolek ....................................................................................................................712
Dodawanie kontrolki ..................................................................................................................713
Tryb projektowania .....................................................................................................................713
Spis treci
27
Modyfikowanie waciwoci ......................................................................................................713
Waciwoci wspódzielone ........................................................................................................714
czenie kontrolek z komórkami ................................................................................................715
Tworzenie makr dla kontrolek ....................................................................................................715
Dostpne kontrolki ActiveX .............................................................................................................716
Kontrolka Pole wyboru ...............................................................................................................717
Kontrolka Pole kombi .................................................................................................................717
Kontrolka Przycisk polecenia .....................................................................................................718
Kontrolka Obraz .........................................................................................................................718
Kontrolka Etykieta ......................................................................................................................718
Kontrolka Pole listy ....................................................................................................................718
Kontrolka Przycisk opcji ............................................................................................................719
Kontrolka Pasek przewijania ......................................................................................................719
Kontrolka Przycisk pokrta .......................................................................................................720
Kontrolka Pole tekstowe .............................................................................................................720
Kontrolka Przycisk przecznika ................................................................................................721
Rozdzia 43. Praca ze zdarzeniami programu Excel ...................................... 723
Zdarzenia ..........................................................................................................................................723
Wprowadzanie kodu ródowego procedury jzyka VBA obsugujcej zdarzenie ...........................724
Zastosowanie zdarze na poziomie skoroszytu .................................................................................725
Zastosowanie zdarzenia Open .....................................................................................................725
Zastosowanie zdarzenia SheetActivate .......................................................................................727
Zastosowanie zdarzenia NewSheet .............................................................................................727
Zastosowanie zdarzenia BeforeSave ...........................................................................................727
Zastosowanie zdarzenia BeforeClose .........................................................................................728
Praca ze zdarzeniami arkusza ............................................................................................................728
Zastosowanie zdarzenia Change .................................................................................................729
Monitorowanie zmian w okrelonym zakresie ............................................................................729
Zastosowanie zdarzenia SelectionChange ..................................................................................730
Zastosowanie zdarzenia BeforeRightClick .................................................................................731
Zastosowanie zdarze niepowizanych z obiektami ............................................................................731
Zastosowanie zdarzenia OnTime ................................................................................................731
Zastosowanie zdarzenia OnKey ..................................................................................................732
Rozdzia 44. Przykady aplikacji napisanych w jzyku VBA .......................... 733
Praca z zakresami ..............................................................................................................................733
Kopiowanie zakresu ....................................................................................................................734
Kopiowanie zakresu o zmiennej wielkoci .................................................................................734
Zaznaczanie komórek od aktywnej a do koca wiersza lub kolumny .......................................735
Zaznaczanie wiersza lub kolumny ..............................................................................................736
Przenoszenie zakresu ..................................................................................................................736
Optymalne wykonywanie ptli w zakresie .................................................................................736
Wywietlenie proby o wprowadzenie do komórki wartoci ......................................................737
Okrelanie typu zaznaczenia .......................................................................................................738
Identyfikacja zaznacze wielokrotnych ......................................................................................739
Zliczanie zaznaczonych komórek ...............................................................................................739
Praca ze skoroszytami .......................................................................................................................740
Zapisywanie wszystkich skoroszytów ........................................................................................740
Zapisywanie i zamykanie wszystkich skoroszytów ....................................................................740
Praca z wykresami ............................................................................................................................740
Modyfikowanie typu wykresu ....................................................................................................741
28
Excel 2010 PL. Biblia
Modyfikowanie waciwoci wykresu ........................................................................................741
Formatowanie wykresu ...............................................................................................................742
Rady dotyczce metod przyspieszenia programów VBA .................................................................742
Wyczanie funkcji aktualizacji zawartoci ekranu ....................................................................742
Zapobieganie wywietlaniu komunikatów ostrzegawczych ........................................................742
Upraszczanie odwoa do obiektów ...........................................................................................743
Deklarowanie typów zmiennych .................................................................................................743
Rozdzia 45. Tworzenie wasnych dodatków do programu Excel ................ 747
Czym jest dodatek? ...........................................................................................................................747
Praca z dodatkami .............................................................................................................................748
Dlaczego tworzy si dodatki? ...........................................................................................................749
Tworzenie dodatków .........................................................................................................................750
Przykad dodatku ..............................................................................................................................751
Tworzenie skoroszytu .................................................................................................................751
Procedury moduu Module1 .......................................................................................................751
Formularz UserForm ...................................................................................................................752
Testowanie skoroszytu ................................................................................................................753
Dodawanie opisów ......................................................................................................................753
Ochrona projektu ........................................................................................................................753
Tworzenie dodatku .....................................................................................................................754
Tworzenie interfejsu uytkownika dla makra dodatku ...............................................................754
Instalowanie dodatku ..................................................................................................................754
Dodatki ....................................................................................... 757
Dodatek A Opis funkcji arkusza ..................................................................... 759
Dodatek B Zawarto dysku CD-ROM ............................................................. 773
Dodatek C Dodatkowe zasoby dla programu Excel ...................................... 781
Dodatek D Skróty klawiszowe stosowane w programie Excel .................... 787
Skorowidz ..................................................................................... 793
Rozdzia 36.
Analiza co-jeli
W tym rozdziale:
Przykad analizy co-jeli.
Rodzaje analiz co-jeli.
Proste wykonywanie analizy co-jeli.
Tworzenie tablic danych opartych na jednej i dwóch komórkach wejciowych.
Korzystanie z Menedera scenariuszy.
Jedn z najciekawszych moliwoci Excela jest tworzenie dynamicznych modeli danych. Wyko-
rzystuj one formuy, które przeliczaj arkusz na nowo po kadej zmianie wartoci w komórkach,
do których te formuy si odwouj. Gdy wartoci w komórkach zmienia si w sposób systema-
tyczny po to, by sprawdzi, jaki efekt zmiany te bd mie na wartoci konkretnych formu, wyko-
nuje si wówczas wanie analiz typu „co-jeli”.
Analiza co-jeli stanowi proces systematycznego wprowadzania zmian wartoci do okrelonych
komórek i obserwacji efektów tych modyfikacji. Polega ona na zadawania pyta typu: „Co si
stanie, gdy stopa procentowa poyczki wzronie nie do 7,5 procent, a do 7,0 procent?” albo „Co si
stanie, jeeli podniesiemy cen naszego produktu o 5 procent?”
Jeeli ustawienia arkusza s prawidowe, odpowiedzi na te pytania uzyska si, wstawiajc kilka
nowych wartoci i obserwujc wyniki przeliczania danych. Przeprowadzanie analiz co-jeli jest
wspomagane przez kilka przydatnych narzdzi.
Przykad analizy co-jeli
Rysunek 36.1 przedstawia prosty arkusz, który oblicza parametry dotyczce kredytu hipotecznego.
Arkusz zosta podzielony na dwie czci — w pierwszej znajduj si komórki wejciowe, w dru-
giej komórki wynikowe (wyjciowe), które zawieraj formuy.
Na doczonej do ksiki pycie CD znajdzie si plik z omawianym przykadem. Plik nosi nazw
kredyt hipoteczny.xlsx.
Korzystajc z tego arkusza mona z atwoci odpowiedzie na nastpujce pytania typu co-jeli:
Co si stanie, jeli uda si wynegocjowa nisz cen nieruchomoci?
Co si stanie, jeli bank bdzie wymaga przedpaty w wysokoci 20 procent?
Co si stanie, jeli dostanie si kredyt na 40 lat?
Co si stanie, jeli stopa procentowa poyczki wzronie do 7,0 procent?
620
Cz V Analiza danych
Unikanie sztywnego wpisywania wartoci do formu
W tym momencie warto zwróci uwag na wan zasad dotyczc budowania arkusza — powinien on
w jak najwikszym stopniu by podatny na dokonywanie wszelkich zmian. Podstawowa zasada przy two-
rzeniu arkuszy brzmi:
Nie naley przypisywa sztywno wartoci do formu. Zamiast tego naley umieszcza wartoci w osobnych
komórkach i odwoywa si do nich w formule.
Pojcie sztywnego przypisywania wie si z umieszczaniem konkretnych wartoci lub staych w formule.
W przykadzie z kredytem hipotecznym wszystkie formuy odnosz si do komórek, a nie do wartoci.
W komórce C11 widocznej na rysunku 36.1 mona wpisa warto 360 jako argument funkcji
PMT,
jednak skorzystanie z odniesienia ma dwie podstawowe zalety. Po pierwsze, nie ma wtpliwoci co do
tego, jakie wartoci s uywane w formuach, poniewa nie s w nich schowane. Po drugie, wartoci takie
mona atwo zmienia (jest to prostsze od edytowania formuy).
Powysza zasada nie ma wikszego znaczenia, gdy korzystamy tylko z jednego arkusza. atwo sobie
jednak wyobrazi, co by si dziao, gdyby wartoci zostay sztywno przypisane do kilkuset formu, znaj-
dujcych si w jednym arkuszu.
Rysunek 36.1.
Arkusz z prostym
modelem danych,
który zawiera cztery
komórki wejciowe,
stanowice podstaw
do obliczania wyników
Odpowiedzi uzyska si, wpisujc odpowiednie wartoci do komórek obszaru C4:C7 i obserwujc
wyniki oblicze w komórkach zalenych (C10:C13). Oczywicie, mona dowolnie zmienia liczb
komórek wejciowych.
Typy analiz co-jeli
Excel radzi sobie take z modelami bardziej skomplikowanymi ni ten przedstawiony w przyka-
dzie z kredytem hipotecznym. Do dyspozycji s trzy metody przeprowadzania analizy co-jeli:
Rczna analiza co-jeli — rcznie wstawia si nowe wartoci i obserwuje wyniki oblicze
w komórkach zalenych.
Tabele danych — tworzy si specjalny typ tabeli, która pokazuje wyniki wybranych komórek
zawierajcych formuy. Jednoczenie systematycznie zmienia si jedn lub dwie komórki
wejciowe.
Meneder scenariuszy — tworzy si scenariusz i generuje raporty wykorzystujce konspekty
lub tabele przestawne.
W pozostaej czci rozdziau prezentowane s poszczególne typy analiz „co-jeli”.
Rczna analiza co-jeli
Rczna analiza co-jeli nie wymaga obszerniejszych wyjanie. Poznano j ju w przykadzie
zamieszczonym na pocztku tego rozdziau. Metoda rcznej analizy polega na tym, e ma si jedn
lub kilka komórek wejciowych, które wpywaj na komórki wynikowe, zawierajce odpowiednie
Rozdzia 36. Analiza co-jeli
621
formuy. Mona dowolnie zmienia wartoci w komórkach wejciowych i obserwowa wyniki obli-
cze w komórkach zalenych. W razie potrzeby mona wydrukowa wyniki albo zapisa kady
scenariusz w nowym formularzu. Termin scenariusz odnosi si do okrelonego zestawu wartoci
zawartych w jednej lub kilku komórkach.
Rczna analiza co-jeli jest bardzo powszechna. Uytkownicy czsto z niej korzystaj, nawet nie
uwiadamiajc sobie, e czego takiego uywaj. Rczna analiza co-jeli nie ma wikszych wad,
jednak warto zapozna si równie z pozostaymi metodami.
Jeeli komórki wejciowe nie znajduj si w pobliu komórek formuy, w celu monitorowania
wyników formuy w przenonym oknie pod uwag warto wzi zastosowanie polecenia Okno
czujki (omówiono j w rozdziale 3.).
Tworzenie tabel danych
W niniejszym podrozdziale przedstawiono jeden z najrzadziej wykorzystywanych elementów Excela,
czyli tabele danych. Tabela danych jest dynamicznym zakresem zestawiajcym komórki for-
muy dla rónych komórek wejciowych. Cho tabele danych mona utworzy w do prosty
sposób, posiadaj pewne ograniczenia. W szczególnoci w danej chwili tabela danych moe obsu-
giwa tylko jedn lub dwie komórki wejciowe. Ograniczenie to bdzie jasne po zapoznaniu si
z przykadami.
Meneder scenariuszy przedstawiony w dalszej czci rozdziau (podrozdzia „Meneder
scenariuszy”) moe wygenerowa raport zestawiajcy dowoln liczb komórek wejciowych
i wynikowych.
Tabeli danych nie naley myli ze zwyk tabel (tworzona przy uyciu polecenia Wstawianie/
Tabele/Tabela). Te dwa typy tabel s cakowicie od siebie niezalene.
Tworzenie tabeli opartej na jednej komórce wejciowej
Tabela oparta na jednej komórce wejciowej prezentuje wyniki jednej lub kilku formu dla rónych
wartoci jednej komórki wejciowej. Na rysunku 36.2 podany jest przykad takiej tabeli. Odpo-
wiedni tabel trzeba skonstruowa samodzielnie — rcznie. Niestety, Excel nie potrafi przygo-
towa jej automatycznie.
Rysunek 36.2.
Schemat tabeli opartej
na jednej komórce
wejciowej
Mona j umieci w dowolnym miejscu arkusza. Lewa kolumna zawiera róne wartoci jednej
komórki wejciowej. Górny wiersz zawiera odwoania do formu, znajdujcych si w innym miej-
scu arkusza. Mona korzysta z dowolnej liczby odwoa do formu. Komórka znajdujca si
w lewym górnym rogu tabeli pozostaje pusta. Excel oblicza wyniki formu dla poszczególnych
wartoci komórek wejciowych i umieszcza je pod kadym odniesieniem do formuy.
622
Cz V Analiza danych
W tym przykadzie ponownie skorzystamy z arkusza zawierajcego informacje o kredycie hipo-
tecznym (naley zapozna si z podrozdziaem „Przykad analizy co-jeli”). Naszym celem bdzie
utworzenie tabeli, która bdzie wywietla wartoci czterech formu (kwota kredytu, miesiczna
rata, suma wpat, cakowita kwota odsetek) dla rónych stóp procentowych, mieszczcych si
w przedziale od 6 do 8 procent, z przyrostem wynoszcym 0,25 procent.
Skoroszyt znajduje si na pycie CD doczonej do ksiki. Plik nosi nazw kredyt hipoteczny
tabela danych.xlsx.
Rysunek 36.3 przedstawia przygotowania do utworzenia tabeli danych. W wierszu 3. mieszcz si
odwoania do formu znajdujcych si w arkuszu — na przykad komórka F3 zawiera formu
=C10
, a komórka G3 zawiera formu
=C11
. Wiersz numer 2 zawiera opcjonalne, opisowe etykiety,
które tak naprawd nie stanowi elementu tabeli danych. W kolumnie E s umieszczone warto-
ci pojedynczych komórek wejciowych (stopa procentowa kredytu), które bd wykorzystane
w tabeli.
Rysunek 36.3.
Przygotowania
do utworzenia tabeli
danych dla jednej
komórki wejciowej
Aby utworzy tabel, naley zaznaczy odpowiedni obszar danych (w tym przypadku bdzie to
E3:I12) i wybra polecenie Dane/Narzdzia danych/Analiza symulacji/Tabela danych. Pojawi si
okno dialogowe Tabela danych, przedstawione na rysunku 36.4.
Rysunek 36.4.
Okno dialogowe
Tabela danych
Trzeba zdefiniowa komórk, która ma zawiera warto wejciow. Odwoanie do niej wpisz
w polu
Kolumnowa komórka wejciowa
, poniewa zmienne dla tej komórki bd si pojawia
w kolumnie tabeli. Naley wpisa
C7
albo wskaza t komórk w arkuszu. Pole
Wierszowa komórka
wejciowa
pozostaw puste. Teraz ju tylko naley klikn przycisk OK, a Excel zapeni tabel
odpowiednimi wynikami (zobacz rysunek 36.5).
Tabela pokazuje teraz wartoci kredytu dla rónych stóp procentowych. Warto zwróci uwag
na to, e wartoci wywietlone w poszczególnych komórkach, bdce wynikiem oblicze, zostay
wygenerowane za pomoc formuy tablicowej:
=TABELA(,C7).
Rozdzia 36. Analiza co-jeli
623
Rysunek 36.5.
Posta wynikowa
tabeli opartej na
jednej komórce
wejciowej
Jak ju powiedziano w rozdziale 16., formua tablicowa jest to pojedyncza formua, zwracajca
wynik w wielu komórkach. Tabela wykorzystuje formuy, a wic jest obiektem dynamicznym. Ozna-
cza to, e za kadym razem, gdy zmienia si odwoania komórek znajdujcych si w pierwszym
wierszu, a take wtedy, gdy wpisze si inne wartoci stóp procentowych w pierwszej kolumnie
tabeli, zostanie ona zaktualizowana.
Tabela oparta na jednej komórce wejciowej moe mie ukad pionowy (tak jak w naszym
przykadzie) albo poziomy. Jeeli wartoci komórki wejciowej wstawi si w wierszu, w oknie
dialogowym Tabela danych naley wpisa odwoanie do nich w polu Wierszowa komórka
wejciowa.
Tworzenie tabeli opartej na dwóch komórkach wejciowych
Jak sama nazwa wskazuje, tabela oparta na dwóch komórkach wejciowych pozwala na wstawie-
nie dwóch komórek wejciowych. Rysunek 36.6 przedstawia przykad takiej tabeli. Jest ona bardzo
podobna do tabeli opartej na jednej komórce wejciowej, jednak róni si od niej tym, e w tym
samym czasie moe prezentowa wyniki tylko jednej formuy. Tabela danych dla jednej komórki
wejciowej moe zawiera w górnym wierszu dowoln liczb formu lub odwoa do nich. W przy-
padku tabeli dla dwóch komórek wejciowych wiersz ten jest zajty przez wartoci drugiej komórki
wejciowej. Komórka znajdujca si w lewym górnym rogu tabeli zawiera odwoanie do poje-
dynczej formuy wynikowej.
Rysunek 36.6.
Schemat tabeli opartej
na dwóch komórkach
wejciowych
Posugujc si arkuszem kredytu hipotecznego, mona zbudowa tabel pokazujc wyniki jakiej
formuy, na przykad miesicznej patnoci, dla rónych kombinacji dwóch komórek wejcio-
wych (takich jak stopa procentowa i wysoko przedpaty wyraona w procentach). Aby pozna
wyniki dla innych formu, trzeba utworzy wicej tabel danych — po jednej dla kadej formuy.
Omawiany przykad wykorzystuje arkusz pokazany na rysunku 36.7, prezentujcy tabel danych
opart na dwóch komórkach wejciowych. Przykad ten dotyczy pewnej firmy, która chce prze-
prowadzi akcj promocyjn sprzedawanego przez siebie produktu w formie przesyek reklamo-
wych. Arkusz ma za zadanie obliczy zysk netto, osignity w wyniku tej promocji.
624
Cz V Analiza danych
Rysunek 36.7.
Arkusz obliczajcy
zysk netto, osignity
w wyniku akcji
promocyjnej produktu
Na doczonej do ksiki pycie CD znajdziesz plik z omawianym przykadem, noszcy nazw
akcja promocyjna.xlsx.
Model ten zawiera dwie komórki wejciowe: liczba wysanych materiaów promocyjnych i prze-
widywana reakcja na promocj. W obszarze Parametry wida nastpujce pozycje:
Jednostkowy koszt wydruku — jest to koszt wydrukowania jednej ulotki reklamowej.
Zaley on od liczby wydrukowanych ulotek: 0,20 z, jeeli jest ich mniej ni 200 000,
0,15 z w przedziale od 200 001 do 300 000 ulotek i wreszcie 0,10 z, jeli wydrukowano
ich ponad 300 000. Korzystamy tutaj z nastpujcej formuy:
=JEELI(B4<200000;0,2;JEELI(B4<300000;0,15;0,1))
Jednostkowy koszt przesyki — jest to staa kwota i wynosi 0,28 z.
Liczba zamówie — jest to stosunek odpowiedzi klientów do liczby wysanych ulotek.
Oblicza go nastpujca formua:
=B4*B5
Zysk z jednego zamówienia — jest to warto staa. Firma obliczya, e na jednym
zamówieniu osiga zysk w wysokoci 18,50 z.
Zysk brutto — jest to prosta formua, której wynikiem jest iloczyn liczby zamówie i zysku
osignitego z jednego zamówienia:
=B10*B11
Koszt cakowity — ta formua oblicza koszt caej promocji, czyli sum kosztów wydruku
i jego przesania:
=B4*(B8+B9)
Zysk netto — jest to wynik kocowy, który uzyskamy, kiedy odejmiemy koszty od zysku
brutto.
Wstaw róne wartoci do komórek wejciowych, aby zobaczy, jak si zmienia zysk netto. Warto
zwróci uwag na to, e w wielu przypadkach osiga on warto ujemn, co oznacza, e akcja pro-
mocyjna przynosi straty.
Rysunek 36.8 przedstawia przygotowania do utworzenia tabeli danych dla dwóch komórek wej-
ciowych. Tabela ta oblicza zysk netto dla rónych kombinacji dwóch danych: liczby wysanych
ulotek i procentowego udziau odpowiedzi na te ulotki. Tabela zajmuje na arkuszu obszar E4:M14.
W komórce E4 umieszczono formu, która zawiera odwoanie do komórki
Zysk netto
:
=B14
Aby utworzy tabel, naley wykona nastpujce kroki:
1.
W zakresie F4:M4 wprowadzi wartoci reakcji na promocj.
2.
W zakresie E5:E14 wprowadzi wartoci liczby wysanych materiaów.
Rozdzia 36. Analiza co-jeli
625
Rysunek 36.8. Przygotowania do utworzenia tabeli danych dla dwóch komórek wejciowych
3.
Zaznaczy zakres E4:M14 i wybra pozycj Dane/Narzdzia danych/Analiza symulacji/
Tabela danych.
4.
W oknie dialogowym Tabela danych okreli komórk B5 jako wierszow komórk
wejciow (reakcja na promocj), natomiast komórk B4 jako kolumnow komórk
wejciow (liczba wysanych materiaów).
5.
Klikn przycisk OK. Excel wypeni tabel danych.
Rysunek 36.9 przedstawia wynik. Jak wida, spora liczba kombinacji reakcji na promocj i iloci
wysanych materiaów zamiast zysku spowodowaa strat.
Rysunek 36.9. Posta wynikowa tabeli opartej na dwóch komórkach wejciowych
Podobnie jak tabela dla jednej komórki wejciowej, tabela oparta na dwóch komórkach ma cha-
rakter dynamiczny. Mona wic zmieni formu z komórki E4 na inne, odwoujce si do innych
komórek (na przykad
Zysk brutto
). Mona te wprowadzi róne wartoci dla reakcji na promocj
i liczby wysanych materiaów.
Meneder scenariuszy
Tabele danych s bardzo uyteczne, lecz maj kilka ogranicze:
mona zmienia tylko jedn lub dwie komórki wejciowe jednoczenie;
tworzenie tabeli nie jest do koca intuicyjne;
tabela danych dla dwóch komórek wejciowych pokazuje wyniki tylko jednej formuy
naraz (cho oczywicie mona tworzy osobne tabele dla kolejnych formu);
z reguy interesujcych jest tylko kilka wybranych kombinacji komórek wejciowych,
a nie caa tabela, prezentujca wszystkie moliwe kombinacje.
626
Cz V Analiza danych
Meneder scenariuszy dokonuje automatyzacji modeli co-jeli. Za jego pomoc mona przecho-
wywa róne zestawy wartoci wejciowych (noszcych w Menederze nazw komórek zmie-
nianych) dla dowolnej liczby zmiennych i kademu z nich nada nazw. Jeeli wybierzesz jeden
z tych zestawów, Excel wywietli arkusz zbudowany na podstawie danych w nim zawartych.
Mona take generowa raport zbiorczy, przedstawiajcy dane wynikowe powstae na skutek zasto-
sowania rónych kombinacji. Raporty takie maj posta konspektu lub tabeli przestawnej.
Przykadowo, prognozy sprzeday na dany rok mog zalee od kilku czynników. Zdefiniuj zatem
trzy scenariusze: przypadek najlepszy, przypadek najgorszy i przypadek najbardziej prawdopo-
dobny. Póniej tylko naley wskaza wybrany scenariusz, a Excel wstawi w arkuszu odpowiednie
wartoci wejciowe i przeliczy na nowo formuy.
Definiowanie scenariuszy
Zaczniemy od prostego przykadu, opartego na modelu produkcji pokazanym na rysunku 36.10.
Rysunek 36.10.
Meneder scenariuszy
— prosty model produkcji
Na doczonej do ksiki pycie CD znajdzie si plik z omawianym przykadem, noszcy nazw
model produkcji.xlsx.
Arkusz przedstawiony na rysunku zawiera dwie komórki wejciowe: godzinny koszt pracy
(komórka B2) oraz jednostkowy koszt materiaów (komórka B3). Firma produkuje trzy róne
towary — do ich wytworzenia potrzeba rónej iloci materiaów, a proces produkcji w kadym
przypadku ma rón dugo.
Naszym zadaniem jest obliczenie za pomoc formu zysku osignitego ze sprzeday kadego z pro-
duktów (wiersz 13.) oraz cakowitego zysku ze sprzeday wszystkich trzech produktów (komórka
B15). Kierownictwo firmy chce przewidzie wysoko cakowitego zysku, ale nie wie, jakie bd
koszty pracy i materiaów. Dlatego stworzylimy trzy scenariusze, które bior pod uwag nast-
pujce moliwoci: przypadek najlepszy, najgorszy i najbardziej prawdopodobny (tabela 36.1).
Tabela 36.1. Trzy scenariusze dla modelu produkcji
Scenariusz
Godzinny koszt pracy
Koszt materiaów
Przypadek najlepszy
30
57
Przypadek najgorszy
38
62
Przypadek najbardziej prawdopodobny
34
59
Przypadek najlepszy zakada najniszy godzinny koszt pracy oraz najniszy koszt materiaów.
Przypadek najgorszy to sytuacja, w której zarówno koszt pracy, jak i koszt materiaów jest wysoki.
Rozdzia 36. Analiza co-jeli
627
Trzeci scenariusz opisuje przypadek najbardziej prawdopodobny, czyli taki, w którym wartoci obu
tych komórek s umiarkowane. Kierownictwo musi by przygotowane na najgorsz z moliwoci,
co nie oznacza, e nie jest zainteresowane przypadkiem najlepszym.
W celu wywietlenia okna Meneder scenariuszy naley wybra pozycj Dane/Narzdzia danych/
Analiza symulacji/Meneder scenariuszy. Gdy pojawi si ono po raz pierwszy, poinformuje, e
nie ma adnych zdefiniowanych scenariuszy. Nic dziwnego, pierwszy z nich utworzy si dopiero
za chwil. W momencie, gdy nada si nazwy swoim scenariuszom, pojawi si one w oknie
dialogowym Meneder scenariuszy.
Dobrym zwyczajem jest nadawanie nazw komórkom zmienianym i tym wszystkim komórkom
wynikowym, które chce si obejrze. Excel bdzie uywa tych nazw w oknach dialogowych
i w generowanych raportach. Stosowanie nazw uatwia orientacj w scenariuszach i wpywa
na czytelno raportów.
Aby doda scenariusz, naley wybra przycisk Dodaj w oknie dialogowym Meneder scenariuszy.
Ukae si okno dialogowe, przedstawione na rysunku 36.11.
Rysunek 36.11.
Okno dialogowe
Dodawanie scenariusza,
umoliwiajce tworzenie
nowych scenariuszy
Okno to skada si z czterech czci:
Nazwa scenariusza — scenariuszowi mona nada dowoln nazw, najlepiej tak, która
bdzie penia funkcj informacyjn.
Komórki zmieniane — s to komórki wejciowe dla scenariusza. Mona wpisa ich adresy
albo wskaza je w arkuszu. Jeli dla komórek zdefiniowano nazw, naley j wprowadzi.
Dozwolone s nieprzylegajce do siebie komórki (w celu wybrania wielu komórek podczas
ich klikania naley trzyma wcinity klawisz Ctrl). Róne scenariusze mog korzysta
z tego samego zestawu komórek zmienianych, cho oczywicie nie musz. Maksymalna
liczba komórek zmienianych dla jednego scenariusza to 32.
Komentarz — w tym polu Excel domylnie wywietla nazwisko osoby, która utworzya
scenariusz. Komentarz mona zmieni, uzupeni lub usun.
Ochrona — dwie opcje ochrony bd funkcjonoway poprawnie dopiero wtedy, gdy cay
arkusz bdzie podlega ochronie, a w oknie dialogowym Chro arkusz zaznaczysz opcj
Scenariusze. Ochrona scenariusza polega na tym, e adna nieuprawniona osoba nie moe
dokona w nim zmian. Zaznaczenie opcji Ukryj spowoduje, e scenariusz nie bdzie
widoczny w oknie dialogowym Meneder scenariuszy.
W tym przykadzie zdefiniujemy trzy scenariusze, wymienione w tabeli 36.1. Komórkami zmie-
nianymi bd
godzinny_koszt_pracy
(B2) i
koszt_materiaów
(B3).
Jeli wprowadzono wszystkie informacje w oknie dialogowym Dodawanie scenariusza, naley klik-
n przycisk OK. Pojawi si okno dialogowe Wartoci scenariusza, pokazane na rysunku 36.12.
Widnieje w nim tyle pól, ile komórek zmienianych zdefiniowae w poprzednim oknie dialogowym.
628
Cz V Analiza danych
Korzystanie z listy rozwijanej Scenariusze
Narzdzie Scenariusze to lista rozwijana, która zawiera wszystkie zdefiniowane scenariusze i umoli-
wia ich szybkie wywietlanie. Co dziwne, to przydatne narzdzie nie wchodzi w skad narzdzia Wst-
ka. Jeli chce si uywa Menedera scenariuszy, mona doda narzdzie Scenariusze do paska
Szybki dostp. W tym celu naley wykona nastpujce czynnoci:
1.
Prawym przyciskiem myszy klikn pasek Szybki dostp i w menu podrcznym wybra
pozycj Dostosuj pasek narzdzi Szybki dostp. Excel wywietli kart Pasek narzdzi Szybki
dostp okna dialogowego Opcje programu Excel.
2.
Z listy rozwijanej Wybierz polecenia z wybra pozycj Polecenia, których nie ma na
Wstce.
3.
Przewin w dó list i wybra pozycj Scenariusz.
4.
Klikn przycisk Dodaj.
5.
Klikn przycisk OK, aby zamkn okno dialogowe Opcje programu Excel.
Alternatywnie kontrolk Scenariusze mona doda do Wstki. Wicej informacji na temat dostosowy-
wania paska narzdzi Szybki dostp mona znale w rozdziale 23.
Rysunek 36.12.
Wprowadzanie wartoci
w oknie dialogowym
Wartoci scenariusza
Naley wprowadzi wartoci dla kadej komórki i kliknij przycisk OK, a nastpnie powróci do
Menedera scenariuszy, który doczy scenariusz do listy. Jeli chce si utworzy wicej scena-
riuszy, naley klikn przycisk Dodaj i powtórzy wszystkie czynnoci.
Wywietlanie scenariuszy
Po zdefiniowaniu scenariuszy powró do okna dialogowego Meneder scenariuszy. Meneder
pokazuje teraz list wszystkich zdefiniowanych scenariuszy. Jeli zaznaczy si jeden z nich i kliknie
przycisk Poka, Excel wstawi odpowiednie wartoci do komórek zmienianych i dokona w arkuszu
przelicze, które bd suyy uzyskaniu wyników dla tego scenariusza. Rysunek 36.13 poka-
zuje, jak wybiera si scenariusz.
Rysunek 36.13.
Wybór scenariusza,
który ma by
wywietlony
Modyfikowanie scenariuszy
Gdy utworzy si scenariusz, by moe trzeba bdzie go zmieni. Przycisk Edytuj w oknie dialo-
gowym Meneder scenariuszy suy do zmiany jednej lub kilku wartoci komórek zmienianych
scenariusza. Jeli chce si zmodyfikowa jaki scenariusz, naley go zaznaczy na licie rozwijanej
Rozdzia 36. Analiza co-jeli
629
i klikn przycisk Edytuj, a nastpnie OK, aby przej do okna dialogowego Wartoci scenariusza,
w którym wprowadzisz zmiany. Warto zauway, e Excel automatycznie aktualizuje pole Komen-
tarz, dodajc informacj o tym, kiedy dokonano modyfikacji scenariusza.
Scalanie scenariuszy
Jeeli pracuje si w grupie, moe zaj taka sytuacja, e kilka osób utworzy róne scenariusze.
W jednej firmie inne zdanie na temat danych wejciowych moe mie dzia marketingu, inne dzia
ksigowoci, a jeszcze inne — dyrektor naczelny.
Excel pozwala poczy róne scenariusze w jednym skoroszycie. Zanim jednak przystpi si do
czenia scenariuszy, warto si najpierw upewni, e skoroszyt ródowy jest otwarty. Nastpnie
trzeba wykona nastpujce czynnoci:
1.
Klikn przycisk Scalaj, znajdujcy si w oknie dialogowym Meneder scenariuszy.
2.
W oknie dialogowym Meneder scenariuszy wybra z listy rozwijanej Skoroszyt
skoroszyt majcy podlega scalaniu.
3.
Z listy rozwijanej Arkusz wybra arkusz, w którym znajduj si odpowiednie
scenariusze. W miar przewijania zawartoci listy rozwijanej u dou okna dialogowego
mona zobaczy, ile scenariuszy zawiera dany arkusz.
4.
Klikn przycisk OK. Nastpi powrót do poprzedniego okna dialogowego, które teraz
wywietla nazwy scenariuszy doczonych z innego skoroszytu.
Generowanie raportu scenariusza
Jeli utworzono wiele scenariuszy, to generujc raport podsumowujcy scenariusze, mona udo-
kumentowa efekty pracy. Naley klikn przycisk Podsumowanie w oknie dialogowym Meneder
scenariuszy. Pojawi si okno dialogowe Podsumowanie scenariuszy.
Do wyboru s dwa rodzaje raportów:
Podsumowanie scenariuszy — raport ma posta konspektu.
Raport tabeli przestawnej scenariuszy — raport ma posta tabeli przestawnej.
W rozdziale 26. zamieszczono wicej informacji na temat konspektów, natomiast w rozdziale
34. na temat tabel przestawnych.
Podsumowanie scenariuszy wystarcza na potrzeby typowych scenariuszy. Jeli jednak ma si duo
scenariuszy, zdefiniowanych przez wiele formu wynikowych, uyteczniejsza w formuowaniu
raportu bdzie tabela przestawna.
W oknie dialogowym Podsumowanie scenariuszy naley okreli komórki wynikowe (czyli te,
które zawieraj dane formuy). W naszym przykadzie chcemy, aby w raporcie znalaz si zysk
osignity ze sprzeday kadego z produktów oraz zysk cakowity ze sprzeday wszystkich trzech
produktów, dlatego zaznaczymy obszar B13:D13 oraz B15 (zaznaczenie wielokrotne).
Meneder scenariuszy ma jedn istotn wad — scenariusz nie moe zawiera wicej ni
32 komórki zmieniane. Gdyby spróbowano przekroczy t liczb, na ekranie pojawi si
informacja o bdzie.
Excel umieszcza tabel podsumowujc w nowym arkuszu. Rysunek 36.14 przedstawia raport
utworzony w formie podsumowania scenariuszy. Tabela uywa nazw, które nadae komórkom
zmienianym, a jeli dana komórka nie ma nazwy, w tabeli znajdzie si jej adres.
630
Cz V Analiza danych
Rysunek 36.14. Podsumowanie scenariuszy wygenerowane przez Menedera scenariuszy
Wyszukiwarka
Podobne podstrony:
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia 2
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia ex21bi
ABC Excel 2010 PL abce21
Microsoft Excel 2010 PL Jezyk VBA i makra Akademia Excela e21vba
Excel 2010 PL cwiczenia praktyczne dla bystrzakow cbex21
Excel 2010 PL Rozwiazywanie problemow dla kazdego ex21rp
Excel 2010 PL cwiczenia praktyczne dla bystrzakow
Excel 2010 PL cwiczenia praktyczne cwex10
Excel 2010 PL Kurs ex10ku
Excel 2010 PL Ilustrowany przewodnik ex10ip
Excel 2010 PL Formuly ex21fo
Excel 2010 PL cwiczenia praktyczne dla bystrzakow cbex21
więcej podobnych podstron