Excel 2010 PL Biblia ex21bi

background image
background image

Idź do

• Spis treści
• Przykładowy rozdział

• Katalog online

• Dodaj do koszyka

• Zamów cennik

• Zamów informacje

o nowościach

• Fragmenty książek

online

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

• Zamów drukowany

katalog

Excel 2010 PL.
Biblia

Autor:

John Walkenbach

Tłumaczenie: Daniel Kaczmarek
ISBN: 978-83-246-2862-9
Tytuł oryginału:

Excel 2010 Bible

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ć!

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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?

background image

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

background image

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.

background image

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).

background image

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.

background image

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.

background image

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.

background image

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.

background image

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.

background image

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

background image

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.

background image

630

Cz V  Analiza danych

Rysunek 36.14. Podsumowanie scenariuszy wygenerowane przez Menedera scenariuszy

background image

Wyszukiwarka

Podobne podstrony:
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia ex21bi
Excel 2010 PL Biblia 2
Excel 2010 PL Biblia
Excel 2010 PL Biblia
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