Poznaj wszystkie możliwości Excela, aby sprawnie i skutecznie wykorzystywać
formuły oraz funkcje
Jak używać narzędzi modelowania biznesowego w Excelu?
Jak korzystać z funkcji szukania wyniku?
Jak poprawiać błędy w formułach?
Większość możliwości Excela pozostaje nieodkryta przez jego użytkowników, przez co cierpi ich efektywność. A gdybyś zawsze miał pod ręką podpowiedź, jak tworzyć formuły i używać funkcji? Gdybyś znał odpowiednie metody i narzędzia? Wtedy byłoby znacznie łatwiej, a Excel zacząłby po prostu znakomicie służyć Twoim celom. Oto dobra wiadomość - właśnie trzymasz w ręku książkę, która stanowi zbiór takich porad i wskazówek.
W książce 'Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie' odarto z tajemnic formuły arkuszowe i przedstawiono najbardziej przydatne funkcje Excela za pomocą przystępnego i wolnego od żargonu języka. Znajdziesz w niej także wyjaśnienia, dlaczego opisywane funkcje są użyteczne i jak z nich korzystać, oraz wiele praktycznych przykładów, przygotowanych z myślą o użytkowniku biznesowym. Nauczysz się stosować analizę co-jeśli, tworzyć formuły pożyczek, obliczać okres zwrotu inwestycji i zdobędziesz wiele innych przydatnych umiejętności, które pozwolą Ci pracować z Excelem efektywnie i bez stresu.
Tworzenie formuł
Używanie nazw zakresów
Struktura funkcji Excela
Przeprowadzanie wielokrotnych testów logicznych
Tworzenie modeli biznesowych
Analizowanie danych za pomocą tabel
Tworzenie niestandardowych obliczeń w tabeli przestawnej
Praca ze scenariuszami
Sporządzanie prognoz
Tworzenie formuł finansowych oraz inwestycji
Rozwiązywanie złożonych problemów za pomocą dodatku Solver
Sprawdzone metody i wskazówki zawsze warto mieć pod ręką!
Darmowy fragment publikacji:
Microsoft Excel 2007 PL.
Formu‡y i funkcje.
Rozwi„zania w biznesie
Autor: Paul McFedries
T‡umaczenie: Ireneusz Jak(cid:243)bik
ISBN: 978-83-246-1350-2
Tytu‡ orygina‡u: Formulas and Functions with
Microsoft Office Excel 2007 (Business Solutions)
Format: 170x230, stron: 576
Poznaj wszystkie mo¿liwo(cid:156)ci Excela, aby sprawnie
i skutecznie wykorzystywa(cid:230) formu‡y oraz funkcje
(cid:149) Jak u¿ywa(cid:230) narzŒdzi modelowania biznesowego w Excelu?
(cid:149) Jak korzysta(cid:230) z funkcji szukania wyniku?
(cid:149) Jak poprawia(cid:230) b‡Œdy w formu‡ach?
WiŒkszo(cid:156)(cid:230) mo¿liwo(cid:156)ci Excela pozostaje nieodkryta przez jego u¿ytkownik(cid:243)w, przez co
cierpi ich efektywno(cid:156)(cid:230). A gdyby(cid:156) zawsze mia‡ pod rŒk„ podpowied(cid:159), jak tworzy(cid:230) formu‡y
i u¿ywa(cid:230) funkcji? Gdyby(cid:156) zna‡ odpowiednie metody i narzŒdzia? Wtedy by‡oby znacznie
‡atwiej, a Excel zacz„‡by po prostu znakomicie s‡u¿y(cid:230) Twoim celom. Oto dobra wiadomo(cid:156)(cid:230)
(cid:150) w‡a(cid:156)nie trzymasz w rŒku ksi„¿kŒ, kt(cid:243)ra stanowi zbi(cid:243)r takich porad i wskaz(cid:243)wek.
W ksi„¿ce (cid:132)Microsoft Excel 2007 PL. Formu‡y i funkcje. Rozwi„zania w biznesie(cid:148) odarto
z tajemnic formu‡y arkuszowe i przedstawiono najbardziej przydatne funkcje Excela
za pomoc„ przystŒpnego i wolnego od ¿argonu jŒzyka. Znajdziesz w niej tak¿e
wyja(cid:156)nienia, dlaczego opisywane funkcje s„ u¿yteczne i jak z nich korzysta(cid:230), oraz wiele
praktycznych przyk‡ad(cid:243)w, przygotowanych z my(cid:156)l„ o u¿ytkowniku biznesowym.
Nauczysz siŒ stosowa(cid:230) analizŒ co-je(cid:156)li, tworzy(cid:230) formu‡y po¿yczek, oblicza(cid:230) okres
zwrotu inwestycji i zdobŒdziesz wiele innych przydatnych umiejŒtno(cid:156)ci, kt(cid:243)re pozwol„
Ci pracowa(cid:230) z Excelem efektywnie i bez stresu.
(cid:149) Tworzenie formu‡
(cid:149) U¿ywanie nazw zakres(cid:243)w
(cid:149) Struktura funkcji Excela
(cid:149) Przeprowadzanie wielokrotnych test(cid:243)w logicznych
(cid:149) Tworzenie modeli biznesowych
(cid:149) Analizowanie danych za pomoc„ tabel
(cid:149) Tworzenie niestandardowych obliczeæ w tabeli przestawnej
(cid:149) Praca ze scenariuszami
(cid:149) Sporz„dzanie prognoz
(cid:149) Tworzenie formu‡ finansowych oraz inwestycji
(cid:149) Rozwi„zywanie z‡o¿onych problem(cid:243)w za pomoc„ dodatku Solver
Sprawdzone metody i wskaz(cid:243)wki zawsze warto mie(cid:230) pod rŒk„!
Wydawnictwo Helion
ul. Ko(cid:156)ciuszki 1c
44-100 Gliwice
tel. 032 230 98 63
e-mail: helion@helion.pl
Spis treści
Wprowadzenie .................................................................................... 17
Co znajduje się w tej książce .....................................................................................................18
Cechy szczególne tej książki .....................................................................................................19
I PANOWANIE NAD ZAKRESAMI I FORMUŁAMI EXCELA
1 Uzyskać jak najwięcej z zakresów .......................................................... 23
Zaawansowane techniki wyboru zakresów ..............................................................................24
Sztuczki z myszą ...............................................................................................................24
Sztuczki z klawiaturą ........................................................................................................25
Praca z zakresami trójwymiarowymi ................................................................................25
Wybieranie zakresu za pomocą polecenia Przejdź do .......................................................26
Użycie okna dialogowego Przechodzenie do - specjalnie .................................................27
Wprowadzanie danych w zakresach ........................................................................................32
Wypełnianie zakresów .............................................................................................................33
Używanie uchwytu wypełniania ..............................................................................................33
Użycie funkcji autowypełniania w celu tworzenia serii tekstowych i numerycznych ............33
Tworzenie niestandardowej listy autowypełniania ..........................................................35
Wypełnianie zakresów ......................................................................................................36
Tworzenie serii .........................................................................................................................37
Zaawansowane metody kopiowania zakresów ........................................................................38
Kopiowanie wybranych atrybutów komórek ....................................................................39
Operacje arytmetyczne na komórkach źródłowych i docelowych .....................................40
Transpozycja wierszy i kolumn .........................................................................................41
Czyszczenie zakresów ...............................................................................................................42
Nadawanie zakresom formatowania warunkowego ...............................................................42
Tworzenie reguł wyróżniania komórek .............................................................................43
Tworzenie reguł pierwszych/ostatnich .............................................................................45
Dodawanie pasków danych ..............................................................................................48
Dodawanie skali kolorów ..................................................................................................51
Dodawanie zestawu ikon ..................................................................................................53
Z tego miejsca… ..............................................................................................................55
2 Używanie nazw zakresów ..................................................................... 57
Definiowanie nazwy zakresu ....................................................................................................58
Praca z polem nazwy ........................................................................................................59
Korzystanie z okna dialogowego Nowa nazwa .................................................................60
4
Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie
Zmiana zakresu w celu definiowania nazw na poziomie arkusza ..................................... 61
Korzystanie z tekstów w arkuszu w celu definiowania nazw ........................................... 62
Nadawanie nazw stałym .................................................................................................. 64
Praca z nazwami zakresów ...................................................................................................... 65
Odwołania do nazwy zakresu ........................................................................................... 66
Praca z funkcją autouzupełniania ..................................................................................... 67
Nawigowanie za pomocą nazw zakresów ........................................................................ 68
Wklejanie listy nazw zakresów do arkusza ....................................................................... 68
Wyświetlanie Menedżera nazw ....................................................................................... 69
Filtrowanie nazw .............................................................................................................. 69
Edycja współrzędnych zakresu ......................................................................................... 70
Automatyczne dostosowywanie współrzędnych powiązanych z nazwą zakresu ............. 70
Zmiana nazwy zakresu ..................................................................................................... 72
Usuwanie zakresu ............................................................................................................ 72
Używanie nazw z operatorem przecięcia ......................................................................... 72
Z tego miejsca… ............................................................................................................. 73
3 Tworzenie podstawowych formuł ..........................................................75
Podstawy formuł ..................................................................................................................... 75
Ograniczenia formuł w Excelu 2007 ................................................................................. 76
Wprowadzanie i edycja formuł ......................................................................................... 76
Używanie formuł arytmetycznych .................................................................................... 77
Używanie formuł porównania .......................................................................................... 78
Używanie formuł tekstowych ........................................................................................... 79
Używanie formuł odwołań ............................................................................................... 79
Wyjaśnienie priorytetu operatorów ......................................................................................... 80
Priorytety operatorów ...................................................................................................... 80
Zmiana kolejności działań ................................................................................................ 81
Kontrolowanie obliczeń w arkuszu .......................................................................................... 83
Kopiowanie i przenoszenie formuł .......................................................................................... 84
Zrozumieć format odwołań względnych .......................................................................... 85
Zrozumieć format odwołań bezwzględnych .................................................................... 87
Kopiowanie formuły bez zmiany odwołań względnych ................................................... 87
Wyświetlanie formuł arkusza .................................................................................................. 88
Zamiana formuły na wartość ................................................................................................... 88
Stosowanie nazw zakresów w formułach ................................................................................ 89
Wklejanie nazw do formuł ............................................................................................... 90
Stosowanie nazw w formułach ........................................................................................ 90
Nadawanie nazw formułom ............................................................................................. 93
Praca z łączami w formułach ................................................................................................... 94
Zrozumieć odwołania zewnętrzne ................................................................................... 95
Spis treści
5
Aktualizacja łączy ..............................................................................................................96
Zmiana źródła łącza ..........................................................................................................97
Formatowanie liczb, dat i czasu ...............................................................................................97
Formaty służące do wyświetlania wartości liczbowych ....................................................98
Formaty daty i czasu .......................................................................................................106
Usuwanie formatów niestandardowych .........................................................................108
Z tego miejsca… ............................................................................................................109
4 Tworzenie zaawansowanych formuł ....................................................111
Praca z tablicami ....................................................................................................................111
Używanie formuł tablicowych ........................................................................................112
Używanie stałych tablicowych ........................................................................................115
Funkcje korzystające z tablic lub je zwracające ...............................................................116
Używanie iteracji i odwołań cyklicznych ................................................................................117
Konsolidowanie danych z wielu arkuszy ................................................................................119
Konsolidowanie według pozycji .....................................................................................120
Konsolidowanie według kategorii ..................................................................................123
Stosowanie w komórkach reguł sprawdzania poprawności danych ......................................125
Używanie w arkuszach formantów pól dialogowych .............................................................128
Używanie formantów formularza ...................................................................................128
Dodawanie formantu do arkusza ....................................................................................129
Przypisywanie formantom łącza komórki .......................................................................129
Zrozumieć formanty arkusza ...........................................................................................130
Z tego miejsca… ............................................................................................................134
5 Rozwiązywanie problemów w formułach .............................................137
Zrozumieć wartości błędów Excela .........................................................................................138
#DZIEL/0! ........................................................................................................................138
#N/D! ..............................................................................................................................139
#NAZWA? ........................................................................................................................139
Unikanie błędów #NAZWA? podczas usuwania nazw zakresów .....................................140
#ZERO! ............................................................................................................................141
#LICZBA! ..........................................................................................................................141
#ADR! ..............................................................................................................................141
#ARG! ..............................................................................................................................142
Poprawianie innych błędów w formułach ..............................................................................142
Brakujące lub niesparowane nawiasy .............................................................................142
Błędne wyniki formuł .....................................................................................................143
Naprawianie odwołań cyklicznych ..................................................................................144
Obsługiwanie błędów w formułach za pomocą funkcji JEŻELI.BŁĄD() ...................................145
Korzystanie z funkcji sprawdzania błędów w formułach ........................................................146
Wybór czynności po wykryciu błędu ...............................................................................147
Ustawianie opcji sprawdzania błędów ...........................................................................148
6
Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie
Dokonywanie inspekcji arkusza ............................................................................................. 150
Zrozumieć inspekcję ....................................................................................................... 151
Śledzenie poprzedników komórek ................................................................................. 152
Śledzenie zależności komórek ........................................................................................ 152
Śledzenie błędów komórek ............................................................................................ 152
Usuwanie strzałek śledzących ........................................................................................ 153
Szacowanie formuł ......................................................................................................... 153
Obserwowanie wartości komórek .................................................................................. 154
Z tego miejsca… ........................................................................................................... 155
II UJARZMIANIE MOCY FUNKCJI
6 Zrozumieć funkcje ..............................................................................159
O funkcjach Excela ................................................................................................................. 160
Struktura funkcji .................................................................................................................... 161
Wpisywanie funkcji do formuł ............................................................................................... 163
Korzystanie z możliwości wstawiania funkcji ........................................................................ 164
Wczytywanie dodatku Analysis ToolPak ................................................................................ 166
Z tego miejsca… ........................................................................................................... 167
7 Praca z funkcjami tekstowymi .............................................................169
Funkcje tekstowe Excela ........................................................................................................ 169
Praca ze znakami i ich kodami ............................................................................................... 171
Funkcja ZNAK() ............................................................................................................... 171
Funkcja KOD() ................................................................................................................. 173
Konwertowanie tekstu .......................................................................................................... 174
Funkcja LITERY.MAŁE() ................................................................................................... 175
Funkcja LITERY.WIELKIE() ............................................................................................... 175
Funkcja Z.WIELKIEJ.LITERY() .......................................................................................... 175
Formatowanie tekstu ............................................................................................................ 176
Funkcja KWOTA() ............................................................................................................ 176
Funkcja ZAOKR.DO.TEKST() ............................................................................................. 176
Funkcja TEKST() .............................................................................................................. 177
Wyświetlanie daty i czasu ostatniej aktualizacji skoroszytu ........................................... 178
Przetwarzanie tekstu ............................................................................................................. 178
Usuwanie niechcianych znaków z łańcucha tekstowego ....................................................... 179
Funkcja USUŃ.ZBĘDNE.ODSTĘPY() .................................................................................. 179
Funkcja OCZYŚĆ() ........................................................................................................... 180
Funkcja POWT() — powtarzanie znaku ......................................................................... 180
Wypełnianie komórki ..................................................................................................... 180
Tworzenie wykresów tekstowych .................................................................................. 181
Spis treści
7
Wyodrębnianie podłańcucha znaków ....................................................................................182
Funkcja LEWY() ...............................................................................................................182
Funkcja PRAWY() ............................................................................................................183
Funkcja FRAGMENT.TEKSTU() .........................................................................................183
Zmiana wielkości liter jak w zdaniu ................................................................................183
Formuła do konwersji dat ...............................................................................................184
Generowanie numeru kontrahenta ........................................................................................185
Szukanie podłańcucha znaków ..............................................................................................185
Funkcje ZNAJDŹ() i SZUKAJ.TEKST() ................................................................................185
Wyodrębnianie imienia bądź nazwiska ..........................................................................186
Wyodrębnianie imienia, nazwiska oraz inicjału ..............................................................187
Określanie kolumny arkusza ...........................................................................................188
Zastępowanie jednego podłańcucha znaków innym .............................................................189
Funkcja ZASTĄP() ............................................................................................................189
Funkcja PODSTAW() ........................................................................................................190
Usuwanie znaku z łańcucha ............................................................................................190
Usuwanie z łańcucha dwóch różnych znaków ................................................................191
Usuwanie znaków nowego wiersza ................................................................................191
Generowanie numeru kontrahenta, część 2 ...........................................................................192
Z tego miejsca… ............................................................................................................193
8 Praca z funkcjami logicznymi i informacyjnymi .....................................195
Dodawanie inteligentnych zachowań z wykorzystaniem funkcji logicznych .........................195
Używanie funkcji JEŻELI() ...............................................................................................196
Przeprowadzanie wielokrotnych testów logicznych .......................................................200
Łączenie funkcji logicznych z tablicami ..........................................................................206
Tworzenie arkusza z należnościami przeterminowanymi ......................................................212
Inteligentne obliczanie terminu płatności ......................................................................212
Klasyfikowanie należności przeterminowanych .............................................................213
Gromadzenie danych za pomocą funkcji informacyjnych ......................................................214
Funkcja KOMÓRKA() ........................................................................................................215
Funkcja NR.BŁĘDU() ........................................................................................................218
Funkcja INFO() ................................................................................................................219
Funkcje CZY .....................................................................................................................220
Z tego miejsca… ............................................................................................................222
9 Praca z funkcjami wyszukiwania ..........................................................225
Zrozumieć tabele przeglądowe ..............................................................................................226
Funkcja WYBIERZ() .................................................................................................................227
Wyznaczanie nazwy dnia tygodnia .................................................................................228
Wyznaczanie kolejnego miesiąca w roku podatkowym ..................................................228
Obliczanie wyników w kwestionariuszu z pytaniami ważonymi ....................................230
Integracja funkcji WYBIERZ() z przyciskami opcji arkusza ...............................................230
8
Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie
Odnajdowanie wartości w tabelach ....................................................................................... 231
Funkcja WYSZUKAJ.PIONOWO() ..................................................................................... 231
Funkcja WYSZUKAJ.POZIOMO() ...................................................................................... 232
Zwracanie rabatu przysługującego klientowi
za pomocą funkcji wyszukiwania w zakresie ............................................................. 233
Zwracanie stawki podatkowej za pomocą funkcji wyszukiwania w zakresie ................. 235
Odszukiwanie dokładnych dopasowań .......................................................................... 236
Zaawansowane funkcje wyszukiwania .......................................................................... 237
Z tego miejsca… ........................................................................................................... 243
10 Praca z funkcjami daty i czasu ..............................................................245
Jak Excel obsługuje daty i czas ............................................................................................... 245
Wprowadzanie dat i czasu .............................................................................................. 246
Excel i lata dwucyfrowe .................................................................................................. 247
Korzystanie z funkcji daty ...................................................................................................... 248
Zwracanie daty ............................................................................................................... 250
Zwracanie części daty ..................................................................................................... 251
Obliczanie różnicy występującej między dwoma datami ............................................... 261
Korzystanie z funkcji czasu ..................................................................................................... 265
Zwracanie czasu ............................................................................................................. 266
Zwracanie części czasu ................................................................................................... 267
Obliczanie różnicy między dwoma czasami .................................................................... 270
Tworzenie karty czasu pracy pracownika ............................................................................... 270
Z tego miejsca… ........................................................................................................... 273
11 Praca z funkcjami matematycznymi .....................................................275
Zrozumieć funkcje zaokrąglające Excela ................................................................................ 278
Funkcja ZAOKR() ............................................................................................................. 279
Funkcja MROUND() ......................................................................................................... 280
Funkcje ZAOKR.DÓŁ() i ZAOKR.GÓRA() ........................................................................... 280
Funkcje ZAOKR.W.GÓRĘ() i ZAOKR.W.DÓŁ() ................................................................... 281
Określanie kwartału podatkowego, do którego należy data .......................................... 281
Obliczanie dat Świąt Wielkanocnych .............................................................................. 282
Funkcje ZAOKR.DO.PARZ() i ZAOKR.DO.NPARZ() ............................................................ 282
Funkcje ZAOKR.DO.CAŁK() i LICZBA.CAŁK() ..................................................................... 283
Stosowanie zaokrąglania w celu zapobiegania powstawaniu błędów obliczeniowych ........284
Ustalanie poziomów cen ................................................................................................. 284
Zaokrąglanie czasu płatnego ................................................................................................. 285
Sumowanie wartości ............................................................................................................. 286
Funkcja SUMA() .............................................................................................................. 286
Obliczanie sum narastających ........................................................................................ 286
Sumowanie w zakresie wyłącznie wartości ujemnych lub dodatnich ............................ 287
Spis treści
9
Funkcja MOD() ........................................................................................................................288
Lepsza formuła do obliczania różnic w czasie .................................................................288
Sumowanie n-tych wierszy .............................................................................................289
Określanie, czy dany rok jest rokiem przestępnym .........................................................289
Tworzenie cieniowania naprzemiennego .......................................................................290
Generowanie liczb losowych ..................................................................................................291
Funkcja LOS() ..................................................................................................................291
Funkcja RANDBETWEEN() ...............................................................................................294
Z tego miejsca… ............................................................................................................295
12 Praca z funkcjami statystycznymi .........................................................297
Zrozumieć statystykę opisową ...............................................................................................299
Zliczanie elementów za pomocą funkcji ILE.LICZB() ...............................................................300
Obliczanie wartości średnich ..................................................................................................301
Funkcja ŚREDNIA() ..........................................................................................................301
Funkcja MEDIANA() .........................................................................................................302
Funkcja WYST.NAJCZĘŚCIEJ() ..........................................................................................302
Obliczanie średniej ważonej ...........................................................................................302
Obliczanie wartości skrajnych ................................................................................................303
Funkcje MAX() i MIN() .....................................................................................................304
Funkcje MAX.K() i MIN.K() ..............................................................................................305
Wykonywanie obliczeń na k górnych wartościach ..........................................................305
Wykonywanie obliczeń na k dolnych wartościach ..........................................................306
Obliczanie wielkości odchylenia .............................................................................................306
Obliczanie rozstępu .........................................................................................................307
Obliczanie wariancji za pomocą funkcji WARIANCJA() ....................................................307
Obliczanie odchylenia standardowego za pomocą funkcji ODCH.STANDARD.POPUL()
i ODCH.STANDARDOWE() .............................................................................................308
Praca z rozkładami częstości ..................................................................................................309
Funkcja CZĘSTOŚĆ() ........................................................................................................310
Zrozumieć rozkład normalny i funkcję ROZKŁAD.NORMALNY() ......................................312
Kształt krzywej I: funkcja SKOŚNOŚĆ() ............................................................................313
Kształt krzywej II: funkcja KURTOZA() .............................................................................314
Używanie narzędzi statystycznych dodatku Analysis ToolPak ................................................315
Korzystanie z narzędzia statystyki opisowej ...................................................................319
Określanie korelacji zachodzącej między danymi ...........................................................321
Praca z histogramami .....................................................................................................323
Używanie generatora liczb losowych ..............................................................................325
Praca z rangą i percentylem ............................................................................................328
Z tego miejsca… ............................................................................................................330
10
Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie
III
TWORZENIE MODELI BIZNESOWYCH
13 Analizowanie danych za pomocą tabel .................................................333
Zamiana zakresu w tabelę ..................................................................................................... 335
Podstawowe operacje na tabelach ........................................................................................ 335
Sortowanie tabeli .................................................................................................................. 338
Sortowanie tabeli w kolejności naturalnej ..................................................................... 340
Sortowanie na podstawie fragmentu pola ..................................................................... 341
Sortowanie z pominięciem przedimków ........................................................................ 342
Filtrowanie danych w tabeli .................................................................................................. 343
Stosowanie list filtrowania w celu filtrowania tabeli ..................................................... 343
Używanie zaawansowanych kryteriów w celu filtrowania tabel .................................... 347
Wprowadzanie kryterium obliczanego .......................................................................... 351
Kopiowanie przefiltrowanych danych do innego zakresu .............................................. 352
Odwoływanie się do tabel w formułach ................................................................................. 353
Używanie specyfikatorów tabel ..................................................................................... 354
Wprowadzanie formuł tabel .......................................................................................... 355
Funkcje tabel Excela .............................................................................................................. 357
O funkcjach tabel ............................................................................................................ 357
Funkcje tabel, które nie wymagają zakresu kryteriów ................................................... 358
Funkcje tabel, które przyjmują wiele kryteriów ............................................................. 360
Funkcje tabel, które wymagają zakresu kryteriów ......................................................... 363
Zastosowanie statystycznych funkcji tabel w bazie danych z brakami .................................. 366
Z tego miejsca… ........................................................................................................... 367
14 Analizowanie danych za pomocą tabel przestawnych ............................369
Czym są tabele przestawne? .................................................................................................. 369
Jak działają tabele przestawne? ..................................................................................... 370
Niektóre pojęcia związane z tabelami przestawnymi ..................................................... 372
Konstruowanie tabel przestawnych ...................................................................................... 373
Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu ..................................... 373
Tworzenie tabeli przestawnej na podstawie zewnętrznej bazy danych ......................... 377
Praca z tabelą przestawną i jej dostosowywanie ........................................................... 377
Praca z sumami częściowymi tabeli przestawnej .................................................................. 378
Ukrywanie sum końcowych w tabeli przestawnej .......................................................... 379
Ukrywanie sum częściowych w tabeli przestawnej ........................................................ 379
Dostosowywanie sposobu obliczania sum częściowych ................................................. 380
Zmiana sposobu obliczania podsumowań pola danych ......................................................... 380
Używanie podsumowań różnic ...................................................................................... 381
Używanie podsumowań wartości procentowych ........................................................... 383
Używanie podsumowań wartości bieżących .................................................................. 386
Używanie podsumowań indeksowych ........................................................................... 387
Spis treści
11
Tworzenie niestandardowych obliczeń w tabeli przestawnej ................................................389
Tworzenie pola obliczeniowego .....................................................................................391
Tworzenie elementu obliczeniowego .............................................................................393
Tworzenie budżetu z elementami obliczeniowymi ................................................................395
Używanie wyników z tabeli przestawnej w formułach arkuszowych .....................................397
Z tego miejsca… ............................................................................................................399
15 Używanie narzędzi modelowania biznesowego w Excelu .......................401
Stosowanie analizy co-jeśli ....................................................................................................401
Konfigurowanie tabeli danych z jedną wartością wejściową ..........................................402
Dodawanie większej liczby formuł do tabeli wejściowej ................................................404
Konfigurowanie tabeli danych z dwoma wartościami wejściowymi ..............................405
Edycja tabeli danych .......................................................................................................407
Praca z funkcją szukania wyniku ............................................................................................407
Jak działa funkcja szukania wyniku? ...............................................................................408
Uruchamianie funkcji szukania wyniku ..........................................................................408
Optymalizacja rentowności produkcji .............................................................................410
Uwaga na temat przybliżeń funkcji szukania wyniku .....................................................411
Analiza progu rentowności .............................................................................................413
Rozwiązywanie równań algebraicznych .........................................................................414
Praca ze scenariuszami ...........................................................................................................415
Zrozumieć scenariusze ....................................................................................................416
Konfigurowanie arkusza do pracy ze scenariuszami .......................................................417
Dodawanie scenariusza ..................................................................................................417
Wyświetlanie scenariusza ...............................................................................................419
Edycja scenariusza ..........................................................................................................420
Scalanie scenariuszy .......................................................................................................421
Generowanie raportu z podsumowaniem ......................................................................421
Usuwanie scenariusza .....................................................................................................423
Z tego miejsca… ............................................................................................................424
16 Użycie regresji w celu śledzenia trendu i sporządzania prognoz ..............425
Wybór metody regresji ...........................................................................................................426
Użycie prostej regresji dla danych liniowych ..........................................................................427
Analiza trendu za pomocą linii najlepszego dopasowania ..............................................427
Sporządzanie prognoz.....................................................................................................436
Analiza trendu i sporządzanie prognoz dla modelu sprzedaży sezonowej .............................441
Użycie prostej regresji dla danych nieliniowych .....................................................................449
Praca z trendem wykładniczym ......................................................................................449
Praca z trendem logarytmicznym ...................................................................................454
Praca z trendem potęgowym ..........................................................................................458
Użycie analizy regresji wielomianowej ...........................................................................461
Użycie regresji wielokrotnej w analizie ..................................................................................464
Z tego miejsca… ............................................................................................................466
12
Microsoft Excel 2007 PL. Formuły i funkcje. Rozwiązania w biznesie
17 Rozwiązywanie złożonych problemów za pomocą dodatku Solver ..........467
Podstawowe informacje na temat dodatku Solver ................................................................ 467
Zalety Solvera ................................................................................................................. 468
Kiedy używać Solvera? ................................................................................................... 469
Wczytywanie dodatku Solver ................................................................................................ 470
Korzystanie z dodatku Solver ................................................................................................. 470
Dodawanie warunków ograniczających ................................................................................ 473
Zapisywanie rozwiązania jako scenariusza ............................................................................ 475
Konfigurowanie pozostałych opcji Solvera ............................................................................ 476
Sprawowanie kontroli nad Solverem ............................................................................. 476
Wybór metody używanej w dodatku Solver ................................................................... 478
Praca z modelami Solvera .............................................................................................. 478
Zrozumieć komunikaty Solvera .............................................................................................. 480
Rozwiązywanie zagadnienia transportowego ....................................................................... 481
Wyświetlanie raportów Solvera ............................................................................................. 483
Raport wyników ............................................................................................................. 484
Raport wrażliwości ......................................................................................................... 485
Raport granic .................................................................................................................. 487
Z tego miejsca… ........................................................................................................... 488
IV TWORZENIE FORMUŁ FINANSOWYCH
18 Tworzenie formuł pożyczek .................................................................491
Zrozumieć wartość pieniądza w czasie .................................................................................. 491
Obliczanie rat pożyczki .......................................................................................................... 493
Analiza spłaty pożyczki ................................................................................................... 493
Praca z pożyczką balonową ............................................................................................ 494
Obliczanie kosztu odsetek, część I .................................................................................. 495
Obliczanie kapitału i odsetek .......................................................................................... 496
Obliczanie kosztu odsetek, część II ................................................................................. 496
Obliczanie skumulowanych wartości kapitału i odsetek ................................................ 497
Tworzenie schematu amortyzacji pożyczki ............................................................................ 499
Tworzenie schematu amortyzacji dla pożyczki o stałym oprocentowaniu ..................... 499
Tworzenie dynamicznego schematu amortyzacji ........................................................... 500
Obliczanie czasu trwania pożyczki ......................................................................................... 502
Obliczanie wymaganej stopy procentowej dla pożyczki ........................................................ 504
Obliczanie kwoty, jaką można pożyczyć ................................................................................ 506
Praca z kredytami hipotecznymi ............................................................................................ 507
Tworzenie schematu amortyzacji dla kredytu hipotecznego
o zmiennej stopie procentowej ................................................................................... 507
Spis treści
13
Umożliwienie dokonywania dodatkowych spłat kapitału ..............................................509
Z tego miejsca… ............................................................................................................510
19 Tworzenie formuł inwestycji ................................................................511
Praca ze stopami procentowymi ............................................................................................511
Zrozumieć procent składany ...........................................................................................512
Nominalna stopa procentowa a efektywna stopa procentowa ......................................512
Zamiana nominalnej stopy procentowej na efektywną i odwrotnie ...............................513
Obliczanie przyszłej wartości ..................................................................................................514
Przyszła wartość płatności jednorazowej ........................................................................515
Przyszła wartość serii płatności .......................................................................................516
Przyszła wartość wpłaty początkowej i serii płatności ....................................................516
Osiąganie założonego celu inwestycyjnego ...........................................................................517
Obliczanie wymaganej stopy procentowej .....................................................................517
Obliczanie wymaganej liczby okresów ...........................................................................518
Obliczanie wymaganej stałej wpłaty ..............................................................................519
Obliczanie wymaganej wpłaty wstępnej ........................................................................519
Obliczanie przyszłej wartości przy zmiennych stopach procentowych ............................520
Tworzenie schematu inwestycji .............................................................................................522
Z tego miejsca… ............................................................................................................524
20 Tworzenie formuł dyskonta .................................................................525
Obliczanie wartości bieżącej ...................................................................................................526
Uwzględnianie inflacji ....................................................................................................527
Obliczanie wartości bieżącej za pomocą funkcji PV() ......................................................527
Inwestycja w papiery wartościowe a inwestycja w nieruchomość .................................528
Zakup a leasing ...............................................................................................................529
Dyskontowanie przepływów pieniężnych ..............................................................................531
Obliczanie wartości bieżącej netto ..................................................................................532
Obliczanie wartości bieżącej netto za pomocą funkcji NPV() ..........................................533
Wartość bieżąca netto i zmienne przepływy pieniężne ..................................................535
Wartość bieżąca netto i nieperiodyczne przepływy pieniężne ........................................536
Obliczanie okresu zwrotu inwestycji ......................................................................................537
Prosty, niezdyskontowany okres zwrotu inwestycji ........................................................537
Dokładny, niezdyskontowany moment zwrotu inwestycji .............................................538
Zdyskontowany okres zwrotu inwestycji ........................................................................539
Obliczanie wewnętrznej stopy zwrotu ...................................................................................540
Użycie funkcji IRR() .........................................................................................................540
Obliczanie wewnętrznej stopy zwrotu dla nieperiodycznych przepływów pieniężnych .541
Obliczanie wielu wewnętrznych stóp zwrotu .................................................................542
Publikowanie książki ..............................................................................................................543
Z tego miejsca… ............................................................................................................547
Skorowidz ..........................................................................................549
4
W T Y M R O Z D Z I A L E :
Praca z tablicami ...................................111
Używanie iteracji i odwołań cyklicznych ... 117
Konsolidowanie danych
z wielu arkuszy .....................................119
Stosowanie w komórkach
reguł sprawdzania poprawności danych ....125
Używanie w arkuszach
formantów pól dialogowych ..................128
Tworzenie
zaawansowanych formuł
Excel jest wszechstronnym programem o wielu zasto-
sowaniach — od listy kontrolnej po system zarządza-
nia kartotekową bazą danych, od narzędzia służącego
do rozwiązywania równań do doskonałego kalkulatora.
Większość użytkowników biznesowych moc Excela
będzie jednak wykorzystywała w budowie modeli, które
umożliwią ocenę określonych aspektów przedsięwzię-
cia. Szkielet modelu biznesowego bazuje na informa-
cjach wprowadzonych, zaimportowanych lub skopio-
wanych do arkuszy kalkulacyjnych. Napędem modelu
i jego esencją są z kolei zbiory formuł, które podsumo-
wują dane, udzielają odpowiedzi i dokonują prognoz.
Jak pokazano w rozdziale 3., „Tworzenie podstawo-
wych formuł”, wystarczy uzbroić się w skromny znak
równości i zestaw operatorów z operandami, aby mieć
możliwość kreowania przydatnych oraz uniwersalnych
formuł. W elektronicznym kapeluszu Excela kryje się
jeszcze więcej sztuczek. Umożliwiają one konstru-
owanie potężnych formuł, które potrafią dźwignąć
modele biznesowe o cały poziom wyżej.
Praca z tablicami
Podczas pracy z zakresami komórek można odnieść
wrażenie, że pracuje się z jednym obiektem. W rze-
czywistości jednak Excel traktuje zakresy jak zbiór
dyskretnych jednostek.
Stanowi to kontrast z tablicami, które są tematem
niniejszego podrozdziału. Tablica to grupa komórek
lub wartości traktowanych w Excelu jak jedna całość.
Excel nie odnosi się do takich komórek indywidualnie,
lecz pracuje jednocześnie na wszystkich komórkach,
dzięki czemu można na przykład wprowadzić formułę
do każdej komórki, używając jednego tylko polecenia.
112
Rozdział 4
Tworzenie zaawansowanych formuł
Tablice można tworzyć, wywołując funkcję, która w wyniku zwraca tablicę (na przykład
REGLINP(); zobacz podrozdział „Funkcje korzystające z tablic lub je zwracające” w dalszej
części tego rozdziału), lub wprowadzając formułę tablicową, która jest formułą pobierającą
tablicę jako argument lub zwracającą wynik swojego działania do wielu komórek.
Używanie formuł tablicowych
Oto prosty przykład ilustrujący sposób działania formuł tablicowych. W arkuszu Wydatki,
pokazanym na rysunku 4.1, wydatki w wierszu BUDŻET 2008 są liczone za pomocą od-
rębnych formuł dla każdego z miesięcy:
BUDŻET 2008 styczeń
BUDŻET 2008 luty
BUDŻET 2008 marzec
=C11*$C$3
=D11*$C$3
=E11*$C$3
Rysunek 4.1.
W tym arkuszu użyto
trzech odrębnych formuł
w celu obliczenia
wartości w wierszu
BUDŻET 2008
4
Wszystkie trzy formuły można zastąpić jedną formułą tablicową, postępując zgodnie z na-
stępującymi wskazówkami:
1. Zaznacz zakres, którego chcesz użyć w formule tablicowej. W wierszu BUDŻET 2008
będzie to zakres C13:E13.
2. Wprowadź formułę i w miejscu, w którym zwykle znalazłoby się odwołanie do komórki,
wpisz odwołanie do zakresu obejmującego komórki mające być uwzględniane w obli-
czeniach. W naszym przykładzie wpisz =C11:E11*$C$3. Po skończeniu nie naciskaj
klawisza Enter. Powtórzę, nie naciskaj klawisza Enter.
3. Aby zatwierdzić formułę w postaci tablicy, naciśnij klawisze Ctrl+Shift+Enter.
Teraz komórki w wierszu BUDŻET 2008 (C13, D13 i E13) będą zawierać tę samą formułę:
{=C11:E11*$C$3}
Tworzenie zaawansowanych formuł
113
Innymi słowy, udało się wprowadzić formułę do trzech różnych komórek, korzystając z jednej
tylko operacji. Dzięki temu można zaoszczędzić mnóstwo czasu, gdy zachodzi potrzeba
wprowadzenia tej samej formuły do wielu różnych komórek.
Należy zauważyć, że formuła jest otoczona nawiasami klamrowymi ({}). Taki sposób zapisu
identyfikuje formułę jako formułę tablicową (w chwili wpisywania formuł nie ma potrzeby
wprowadzania tych nawiasów; Excel dodaje je automatycznie).
UWAGA
Ponieważ Excel traktuje tablice jako jednostki, nie ma możliwości przeniesienia ani usunięcia części
tablicy. Jeśli zajdzie potrzeba pracy z istniejącą tablicą, należy zaznaczyć ją w całości. W przypadku
konieczności zmiany rozmiarów tablicy, należy ją zaznaczyć, uaktywnić pasek formuły, a następnie
nacisnąć klawisze Ctrl+Enter w celu zamiany wpisu w zwykłą formułę. Teraz można będzie zaznaczyć
inny zakres i ponownie wprowadzić formułę tablicową.
Warto zapamiętać, że tablicę można szybko zaznaczyć, aktywując jedną z jej komórek i naciskając
klawisze Ctrl+/.
Zrozumieć formuły tablicowe
Aby zrozumieć sposób przetwarzania tablic w Excelu, należy pamiętać, że Excel zawsze
ustanawia powiązanie między komórkami tablicy i komórkami zakresu wprowadzonego do
formuły tablicowej. W przykładzie z kalkulacją wydatków budżetowych na rok 2008 tablica
składa się z komórek C13, D13 i E13, a zakres użyty w formule to komórki C11, D11 i E11.
Excel ustanawia powiązanie między komórką tablicy C13 a komórką wejściową C11, mię-
dzy komórkami D13 a D11 oraz między komórkami E13 a E11. Aby na przykład obliczyć
wartość komórki C13 (BUDŻET 2008 styczeń), Excel pobierze wartość wejściową z komórki
C11 i zastąpi ją w formule. Na rysunku 4.2 pokazano diagram ilustrujący ten proces.
4
Rysunek 4.2.
Podczas przetwarzania
formuły tablicowej Excel
ustanawia powiązanie
między komórkami
tablicy i zakresem
użytym w formule
Formuły tablicowe mogą wydawać się nieco skomplikowane, jeśli jednak pamiętamy o usta-
nawianych w nich powiązaniach, zrozumienie ich działania nie powinno nastręczać więk-
szych trudności.
114
Rozdział 4
Tworzenie zaawansowanych formuł
Formuły tablicowe działające na wielu zakresach
W poprzednim przykładzie formuła tablicowa działała na jednym zakresie, niemniej formuły
tablicowe mogą też operować wieloma zakresami. Rozważmy na przykład arkusz o nazwie
Szablon rachunku pokazany na rysunku 4.3. Kwoty w kolumnie Wartość (komórki od
F12 do F16) są uzyskiwane za pomocą mnożenia ceny produktu przez zamówioną ilość:
Komórka
F12
F13
F14
F15
F16
Formuła
=B12*E12
=B13*E13
=B14*E14
=B15*E15
=B16*E16
Rysunek 4.3.
W tym arkuszu użyto
kilku formuł w celu
obliczenia wartości
towaru w każdym
wierszu
4
Wszystkie te formuły można zastąpić, wprowadzając w zakresie F12:F16 następującą formułę
tablicową:
=B12:B16*E12:E16
Tutaj też formuła tablicowa została utworzona przez zastąpienie każdego odwołania do
komórki odpowiednim zakresem (i naciśnięciem klawiszy Ctrl+Shift+Enter).
UWAGA
Formuły tablicowe nie muszą być wprowadzane do kilku komórek. Jeśli na przykład nie potrzebujesz
wartości poszczególnych towarów w arkuszu z szablonem rachunku, możesz obliczyć ich łączną
cenę netto, wprowadzając następującą formułę tablicową do komórki F17:
=SUM(B12:B16*E12:E16)
Tworzenie zaawansowanych formuł
115
Używanie stałych tablicowych
W pokazanych formułach tablicowych argumentami tablic były zakresy komórek. W roli ar-
gumentów tablic można użyć również wartości stałych. Dzięki temu do formuł można wpro-
wadzać wartości bez konieczności umieszczania ich w komórkach arkusza.
Aby do formuły wprowadzić stałą tablicową, wprowadź wartości bezpośrednio do formuły,
przestrzegając jednocześnie następujących zaleceń:
(cid:81) Wartości powinny być zamknięte w nawiasach klamrowych ({}).
(cid:81) Aby wartości były traktowane jako wiersze, należy je rozdzielić odwrotnym ukośnikiem.
(cid:81) Aby wartości były traktowane jako kolumny, należy je rozdzielić średnikiem.
Poniższa stała tablicowa jest na przykład równoważna z wprowadzeniem odrębnych wartości
do kolumny arkusza:
{1234}
Podobnie następująca stała tablicowa jest równoważna z wprowadzeniem do arkusza warto-
ści w trzech kolumnach i dwóch wierszach:
{1;2;34;5;6}
Na rysunku 4.4 pokazano praktyczny przykład zawierający dwie formuły tablicowe. Formuła
z lewej strony (użyta w zakresie E4:E7) oblicza raty pożyczki na podstawie różnych wyso-
kości oprocentowania znajdujących się w zakresie C5:C8. Formuła tablicowa z prawej strony
(użyta w zakresie F4:F7) dokonuje takich samych obliczeń, ale wartości oprocentowania
zostały wprowadzone pod postacią tablicy bezpośrednio w formule.
4
Rysunek 4.4.
Użycie stałych
tablicowych w formułach
tablicowych oznacza,
że wartości wejściowych
nie trzeba umieszczać
w komórkach arkusza
(cid:206) Aby poznać działanie funkcji PMT(), zajrzyj do rozdziału „Obliczanie rat pożyczki” na stronie 493.
116
Rozdział 4
Tworzenie zaawansowanych formuł
Funkcje korzystające z tablic lub je zwracające
Wiele funkcji arkuszowych Excela wymaga argumentu w postaci tablicy albo zwraca wynik
będący tablicą (lub jedno i drugie). W tabeli 4.1 wymieniono kilkanaście takich funkcji i wy-
jaśniono, w jaki sposób każda z nich korzysta z tablic (dokładniejsze opisy tych funkcji znaj-
dują się w części 2., „Ujarzmianie mocy funkcji”).
Tabela 4.1. Niektóre z funkcji Excela korzystające z tablic
4
Nazwa funkcji
ILE.WIERSZY()
INDEKS()
LICZBA.KOLUMN()
MACIERZ.ILOCZYN()
MACIERZ.ODW()
NR.KOLUMNY()
PODAJ.POZYCJĘ()
REGEXPP()
REGEXPW()
REGLINP()
REGLINW()
SUMA.ILOCZYNÓW()
TRANSPONUJ()
WIERSZ()
WYSZUKAJ()
WYSZUKAJ.PIONOWO()
WYSZUKAJ.POZIOMO()
WYZNACZNIK.MACIERZY()
Argument tablicowy
Zwraca w wyniku tablicę
Tak
Tak
Tak
Nie
Nie
Nie
Tak
Nie
Tak
Nie
Tak
Tak
Tak
Nie
Tak
Tak
Tak
Tak
Nie
Tak
Nie
Tak
Tak
Tak, jeśli argument jest zakresem
Nie
Tak
Tak
Tak
Tak
Nie
Tak
Tak, jeśli argument jest zakresem
Nie
Nie
Nie
Nie
Podczas używania funkcji zwracających tablice należy pamiętać o zaznaczeniu zakresu, który
będzie wystarczająco duży dla zwracanych tablic, a także o wprowadzeniu funkcji jako for-
muły tablicowej.
(cid:206) Tablice staną się naprawdę potężną bronią w arsenale Excela, gdy zostaną użyte łącznie z takimi funkcjami arkusza jak JEŻELI()
albo SUMA(). Przedstawię o wiele więcej przy
Pobierz darmowy fragment (pdf)
