Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00210 007723 15713374 na godz. na dobę w sumie
Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela - książka
Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela - książka
Autor: Liczba stron: 568
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-2893-3 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Poszerz swoją wiedzę o bezcenne informacje
z zakresu formuł i funkcji Excela 2010!

Zdecydowana większość użytkowników Excela korzysta ze znikomej części jego olbrzymich możliwości. Ci użytkownicy wiedzą jednocześnie, że mogliby o wiele wydajniej pracować, jeśli tylko ktoś pomógłby im w nauce używania funkcji i tworzenia formuł. Niestety, zbyt często ta część arkusza kalkulacyjnego jest postrzegana jako zbyt trudna i skomplikowana, a na dodatek opisana niemożliwym do zrozumienia językiem.

Jeśli powyższy opis dotyczy Twojej sytuacji, a jesteś osobą, która musi korzystać z Excela w swojej pracy, trzymasz w rękach najodpowiedniejszą książkę. 'Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela' to Twój prywatny zbiór porad i wskazówek, podsuwający Ci zawsze właściwą metodę i przydatne narzędzie. Dzięki tej książce zaprzęgniesz arkusz do pracy - aby służył wyłącznie Twoim celom.

Znajdziesz tutaj odarte z tajemnic formuły arkuszowe i najbardziej przydatne funkcje Excela 2010, opisane w sposób przystępny i łatwy do przyswojenia. Ten poradnik zawiera nie tylko zaawansowane metody konstruowania formuł, ale też wyjaśnienie, dlaczego są one przydatne i jak ich używać w codziennych sytuacjach oraz przy modelowaniu rzeczywistego świata.

Naucz się wszystkiego o tworzeniu formuł w Excelu oraz opanuj funkcje tekstowe, logiczne, informacyjne, odwołań, daty, czasu, matematyczne i statystyczne na podstawie praktycznych przykładów pokazujących ich zastosowanie. Zapoznaj się z analizą danych za pomocą tabel przestawnych oraz analizą 'co jeśli'. Śledź trendy, sporządzaj prognozy, a na koniec dowiedz się, jakie biznesowe dobrodziejstwa skrywa przed Tobą Excel 2010.

Wszystko, co niezbędne, abyś został mistrzem w pracy z Excelem 2010!

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Idź do • Spis treści • Przykładowy rozdział Katalog książek • Katalog online • Zamów drukowany katalog Twój koszyk • Dodaj do koszyka Cennik i informacje • Zamów informacje o nowościach • Zamów cennik Czytelnia • Fragmenty książek online Kontakt Helion SA ul. Kościuszki 1c 44-100 Gliwice tel. 32 230 98 63 e-mail: helion@helion.pl © Helion 1991–2010 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela Autor: Paul McFedries Tłumaczenie: Ireneusz Jakóbik ISBN: 978-83-246-2893-3 Tytuł oryginału: Formulas and Functions: Microsoft Excel 2010 (MrExcel Library) Format: B5, stron: 576 Poszerz swoją wiedzę o bezcenne informacje z zakresu formuł i funkcji Excela 2010! • Modelowanie biznesowe • Sporządzanie prognoz • Wyszukiwanie i korekta błędów w formułach Zdecydowana większość użytkowników Excela korzysta ze znikomej części jego olbrzymich możliwości. Ci użytkownicy wiedzą jednocześnie, że mogliby o wiele wydajniej pracować, jeśli tylko ktoś pomógłby im w nauce używania funkcji i tworzenia formuł. Niestety, zbyt często ta część arkusza kalkulacyjnego jest postrzegana jako zbyt trudna i skomplikowana, a na dodatek opisana niemożliwym do zrozumienia językiem. Jeśli powyższy opis dotyczy Twojej sytuacji, a jesteś osobą, która musi korzystać z Excela w swojej pracy, trzymasz w rękach najodpowiedniejszą książkę. „Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela” to Twój prywatny zbiór porad i wskazówek, podsuwający Ci zawsze właściwą metodę i przydatne narzędzie. Dzięki tej książce zaprzęgniesz arkusz do pracy – aby służył wyłącznie Twoim celom. Znajdziesz tutaj odarte z tajemnic formuły arkuszowe i najbardziej przydatne funkcje Excela 2010, opisane w sposób przystępny i łatwy do przyswojenia. Ten poradnik zawiera nie tylko zaawansowane metody konstruowania formuł, ale też wyjaśnienie, dlaczego są one przydatne i jak ich używać w codziennych sytuacjach oraz przy modelowaniu rzeczywistego świata. Naucz się wszystkiego o tworzeniu formuł w Excelu oraz opanuj funkcje tekstowe, logiczne, informacyjne, odwołań, daty, czasu, matematyczne i statystyczne na podstawie praktycznych przykładów pokazujących ich zastosowanie. Zapoznaj się z analizą danych za pomocą tabel przestawnych oraz analizą „co jeśli”. Śledź trendy, sporządzaj prognozy, a na koniec dowiedz się, jakie biznesowe dobrodziejstwa skrywa przed Tobą Excel 2010. • Zakresy • Formuły • Operatory • Praca z tablicami • Wyszukiwanie i poprawa błędów • Funkcje tekstowe, logiczne, informacyjne, odwołań, daty, czasu, matematyczne i statystyczne • Analiza danych • Tabele przestawne • Modelowanie biznesowe • Trendy i prognozy • Dodatek Solver • Formuły finansowe Wszystko, co niezbędne, abyś został mistrzem w pracy z Excelem 2010! Spis treści Wprowadzenie .......................................................................................... 19 Co znajduje się w tej książce .....................................................................................................20 Cechy szczególne tej książki .....................................................................................................21 I PANOWANIE NAD ZAKRESAMI I FORMUŁAMI EXCELA 1 Uzyskać jak najwięcej z zakresów .......................................................... 25 Zaawansowane techniki wyboru zakresów ..............................................................................26 Sztuczki z myszą ...............................................................................................................26 Sztuczki z klawiaturą ........................................................................................................27 Praca z zakresami trójwymiarowymi ................................................................................27 Wybieranie zakresu za pomocą polecenia Przejdź do .......................................................28 Użycie okna dialogowego Przechodzenie do - specjalnie .................................................29 Wprowadzanie danych w zakresach ........................................................................................34 Wypełnianie zakresu ................................................................................................................34 Używanie uchwytu wypełniania ..............................................................................................35 Użycie funkcji autowypełniania w celu tworzenia serii tekstowych i numerycznych ........36 Tworzenie niestandardowej listy autowypełniania ..........................................................37 Wypełnianie zakresu .........................................................................................................39 Tworzenie serii .........................................................................................................................39 Zaawansowane metody kopiowania zakresów ........................................................................40 Kopiowanie wybranych atrybutów komórek ....................................................................41 Operacje arytmetyczne na komórkach źródłowych i docelowych .....................................42 Transpozycja wierszy i kolumn .........................................................................................43 Czyszczenie zakresów ...............................................................................................................43 Nadawanie zakresom formatowania warunkowego ...............................................................44 Tworzenie reguł wyróżniania komórek .............................................................................45 Tworzenie reguł pierwszych/ostatnich .............................................................................46 Dodawanie pasków danych ..............................................................................................49 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 .................................................................59 Zmiana zakresu w celu definiowania nazw na poziomie arkusza .....................................61 6 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela 3 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 ........................................................................................... 65 Praca z funkcją autouzupełniania ..................................................................................... 67 Nawigowanie za pomocą nazw zakresów ........................................................................ 67 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 ..................................................................................................... 71 Usuwanie zakresu ............................................................................................................ 72 Używanie nazw z operatorem przecięcia ......................................................................... 72 Z tego miejsca… ............................................................................................................. 73 Tworzenie podstawowych formuł ..........................................................75 Podstawy formuł ..................................................................................................................... 75 Ograniczenia formuł w Excelu 2007 i 2010 ....................................................................... 76 Wprowadzanie i edycja formuł ......................................................................................... 76 Używanie formuł arytmetycznych .................................................................................... 78 Używanie formuł porównania .......................................................................................... 78 Używanie formuł tekstowych ........................................................................................... 79 Używanie formuł odwołań ............................................................................................... 79 Wyjaśnienie priorytetu operatorów ......................................................................................... 79 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ł ............................................................................................... 89 Stosowanie nazw w formułach ........................................................................................ 90 Nadawanie nazw formułom ............................................................................................. 93 Praca z łączami w formułach ................................................................................................... 94 Zrozumieć odwołania zewnętrzne ................................................................................... 95 Aktualizacja łączy ............................................................................................................. 95 Zmiana źródła łącza .......................................................................................................... 96 Spis treści 7 4 Formatowanie liczb, dat i czasu ...............................................................................................97 Formaty służące do wyświetlania wartości liczbowych ....................................................97 Formaty daty i czasu .......................................................................................................105 Usuwanie formatów niestandardowych .........................................................................107 Z tego miejsca… ............................................................................................................108 Tworzenie zaawansowanych formuł ....................................................111 Praca z tablicami ....................................................................................................................111 Używanie formuł tablicowych ........................................................................................112 Zrozumieć formuły tablicowe .................................................................................................113 Formuły tablicowe działające na wielu zakresach ..........................................................114 Używanie stałych tablicowych ...............................................................................................115 Funkcje korzystające z tablic lub je zwracające ...............................................................115 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 ......................................124 Używanie w arkuszach formantów pól dialogowych .............................................................128 Wyświetlanie karty Deweloper .......................................................................................128 Używanie formantów formularza ...................................................................................128 Dodawanie formantu do arkusza ....................................................................................128 Przypisywanie formantom łącza do 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 Studium przypadku: Unikanie błędów #NAZWA? podczas usuwania nazw zakresów ..... 140 #ZERO! ............................................................................................................................141 #LICZBA! ..........................................................................................................................141 #ADR! ..............................................................................................................................142 #ARG! ..............................................................................................................................142 Poprawianie innych błędów w formułach ..............................................................................142 Brakujące lub niesparowane nawiasy .............................................................................143 Błędne wyniki formuł .....................................................................................................144 Naprawianie odwołań cyklicznych ..................................................................................145 Obsługiwanie błędów w formułach za pomocą funkcji JEŻELI.BŁĄD() ......................................145 8 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela Korzystanie z funkcji sprawdzania błędów w formułach ....................................................... 147 Wybór czynności po wykryciu błędu ............................................................................... 147 Ustawianie opcji sprawdzania błędów ........................................................................... 148 Dokonywanie inspekcji arkusza ............................................................................................. 151 Zrozumieć inspekcję ....................................................................................................... 151 Śledzenie poprzedników komórek ................................................................................. 152 Śledzenie zależności komórek ........................................................................................ 153 Śledzenie błędów komórek ............................................................................................ 153 Usuwanie strzałek śledzących ........................................................................................ 153 Szacowanie formuł ......................................................................................................... 153 Obserwowanie wartości komórek .................................................................................. 154 Z tego miejsca… ........................................................................................................... 155 6 II UJARZMIANIE MOCY FUNKCJI 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 ............................................................................................... 169 Funkcja ZNAK() ............................................................................................................... 171 Funkcja KOD() ................................................................................................................. 173 Konwertowanie tekstu .......................................................................................................... 175 Funkcja LITERY.MAŁE() ................................................................................................... 175 Funkcja LITERY.WIELKIE() ............................................................................................... 175 Funkcja Z.WIELKIEJ.LITERY() .......................................................................................... 175 Formatowanie tekstu ............................................................................................................ 176 Funkcja KWOTA() ............................................................................................................ 176 Funkcja ZAOKR.DO.TEKST() ............................................................................................. 177 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ŚĆ() ........................................................................................................... 179 Funkcja POWT() — powtarzanie znaku ......................................................................... 180 Spis treści 9 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 Szukanie podłańcucha znaków ..............................................................................................184 Funkcje ZNAJDŹ() i SZUKAJ.TEKST() ................................................................................185 Studium przypadku: Generowanie numeru kontrahenta ......................................................185 Wyodrębnianie imienia bądź nazwiska ..........................................................................186 Wyodrębnianie imienia, nazwiska oraz inicjału ..............................................................187 Określanie litery kolumny ...............................................................................................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 ................................................................190 Studium przypadku: Generowanie numeru kontrahenta, część 2 .........................................191 Usuwanie znaków nowego wiersza ................................................................................191 Z tego miejsca… ............................................................................................................192 8 Praca z funkcjami logicznymi i informacyjnymi .....................................193 Dodawanie inteligentnych zachowań z wykorzystaniem funkcji logicznych .........................193 Używanie funkcji JEŻELI() ...............................................................................................194 Przeprowadzanie wielokrotnych testów logicznych .......................................................197 Łączenie funkcji logicznych z tablicami ..........................................................................203 Studium przypadku: Tworzenie arkusza z należnościami przeterminowanymi ....................209 Uzyskiwanie danych za pomocą funkcji informacyjnych ........................................................211 Funkcja KOMÓRKA() ........................................................................................................212 Funkcja NR.BŁĘDU() ........................................................................................................215 Funkcja INFO() ................................................................................................................216 Funkcje CZY .....................................................................................................................217 Z tego miejsca… ............................................................................................................220 9 Praca z funkcjami wyszukiwania ..........................................................221 Zrozumieć tabele przeglądowe ..............................................................................................222 Funkcja WYBIERZ() .................................................................................................................223 Wyznaczanie nazwy dnia tygodnia .................................................................................224 Wyznaczanie kolejnego miesiąca w roku podatkowym ..................................................225 Obliczanie ważonych wyników w kwestionariuszu ........................................................226 Integracja funkcji WYBIERZ() z przyciskami opcji arkusza ...............................................226 10 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela Odnajdowanie wartości w tabelach ....................................................................................... 227 Funkcja WYSZUKAJ.PIONOWO() ..................................................................................... 227 Funkcja WYSZUKAJ.POZIOMO() ...................................................................................... 228 Zwracanie rabatu przysługującego klientowi za pomocą funkcji wyszukiwania w zakresie ............................................................... 229 Zwracanie stawki podatkowej za pomocą funkcji wyszukiwania w zakresie ................. 230 Odszukiwanie dokładnych dopasowań .......................................................................... 231 Zaawansowane funkcje wyszukiwania .......................................................................... 232 Z tego miejsca… ........................................................................................................... 238 10 Praca z funkcjami daty i czasu ..............................................................239 Jak Excel obsługuje daty i czas ............................................................................................... 239 Wprowadzanie dat i czasu .............................................................................................. 240 Excel i lata dwucyfrowe .................................................................................................. 241 Korzystanie z funkcji daty ...................................................................................................... 243 Zwracanie daty ............................................................................................................... 243 Zwracanie części daty ..................................................................................................... 245 Obliczanie różnicy występującej między dwoma datami ............................................... 255 Korzystanie z funkcji czasu ..................................................................................................... 259 Zwracanie czasu ............................................................................................................. 259 Zwracanie części czasu ................................................................................................... 261 Obliczanie różnicy między dwoma czasami .................................................................... 264 Studium przypadku: Tworzenie karty czasu pracy pracownika ............................................. 264 Z tego miejsca… ........................................................................................................... 268 11 Praca z funkcjami matematycznymi .....................................................269 Zrozumieć funkcje zaokrąglające Excela ................................................................................ 273 Funkcja ZAOKR() ............................................................................................................. 273 Funkcja MROUND() ......................................................................................................... 274 Funkcje ZAOKR.DÓŁ() i ZAOKR.GÓRA() ........................................................................... 274 Funkcje ZAOKR.W.GÓRĘ() i ZAOKR.W.DÓŁ() ................................................................... 275 Określanie kwartału podatkowego, do którego należy data .......................................... 275 Obliczanie dat Świąt Wielkanocnych .............................................................................. 276 Funkcje ZAOKR.DO.PARZ() i ZAOKR.DO.NPARZ() ............................................................ 276 Funkcje ZAOKR.DO.CAŁK() i LICZBA.CAŁK() ..................................................................... 277 Stosowanie zaokrąglania w celu zapobiegania powstawaniu błędów obliczeniowych ........................................................................ 278 Ustalanie poziomów cen ................................................................................................ 278 Studium przypadku: Zaokrąglanie czasu płatnego ............................................................... 279 Sumowanie wartości ............................................................................................................. 280 Funkcja SUMA() .............................................................................................................. 280 Obliczanie sum narastających ........................................................................................ 280 Sumowanie w zakresie wyłącznie wartości ujemnych lub dodatnich ............................ 281 Spis treści 11 Funkcja MOD() ........................................................................................................................282 Lepsza formuła do obliczania różnic w czasie .................................................................282 Sumowanie n-tych wierszy .............................................................................................283 Określanie, czy dany rok jest rokiem przestępnym .........................................................283 Tworzenie cieniowania naprzemiennego .......................................................................284 Generowanie liczb losowych ..................................................................................................286 Funkcja LOS() ..................................................................................................................286 Funkcja RANDBETWEEN() ...............................................................................................288 Z tego miejsca… ............................................................................................................289 12 Praca z funkcjami statystycznymi .........................................................291 Zrozumieć statystykę opisową ...............................................................................................291 Zliczanie elementów za pomocą funkcji ILE.LICZB() ...............................................................294 Obliczanie wartości średnich ..................................................................................................295 Funkcja ŚREDNIA() ..........................................................................................................296 Funkcja MEDIANA() .........................................................................................................296 Funkcja WYST.NAJCZĘŚCIEJ() ..........................................................................................297 Obliczanie średniej ważonej ...........................................................................................297 Obliczanie wartości skrajnych ................................................................................................299 Funkcje MAX() i MIN() .....................................................................................................299 Funkcje MAX.K() i MIN.K() ..............................................................................................300 Wykonywanie obliczeń na k górnych wartościach ..........................................................301 Wykonywanie obliczeń na k dolnych wartościach ..........................................................301 Obliczanie wielkości odchylenia .............................................................................................301 Obliczanie rozstępu .........................................................................................................302 Obliczanie wariancji ........................................................................................................302 Obliczanie odchylenia standardowego ...........................................................................303 Praca z rozkładami częstości ..................................................................................................305 Funkcja CZĘSTOŚĆ() ........................................................................................................305 Zrozumieć rozkład normalny i funkcję ROZKŁAD.NORMALNY() ......................................306 Kształt krzywej I: funkcja SKOŚNOŚĆ() ............................................................................308 Kształt krzywej II: funkcja KURTOZA() .............................................................................309 Używanie narzędzi statystycznych dodatku Analysis ToolPak ................................................310 Korzystanie z narzędzia statystyki opisowej ...................................................................314 Określanie korelacji zachodzącej między danymi ...........................................................315 Praca z histogramami .....................................................................................................317 Używanie generatora liczb losowych ..............................................................................320 Praca z rangą i percentylem ............................................................................................323 Z tego miejsca… ............................................................................................................325 12 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela III TWORZENIE MODELI BIZNESOWYCH 13 Analizowanie danych za pomocą tabel .................................................329 Zamiana zakresu w tabelę ..................................................................................................... 331 Podstawowe operacje na tabelach ........................................................................................ 332 Sortowanie tabeli .................................................................................................................. 334 Sortowanie tabeli w kolejności naturalnej ..................................................................... 336 Sortowanie na podstawie fragmentu pola ..................................................................... 337 Sortowanie z pominięciem przedimków ........................................................................ 338 Filtrowanie danych w tabeli .................................................................................................. 339 Stosowanie list filtrowania w celu filtrowania tabeli ..................................................... 339 Używanie zaawansowanych kryteriów w celu filtrowania tabel .................................... 343 Wprowadzanie kryterium obliczanego .......................................................................... 346 Kopiowanie przefiltrowanych danych do innego zakresu .............................................. 348 Odwoływanie się do tabel w formułach ................................................................................. 348 Używanie specyfikatorów tabel ..................................................................................... 349 Wprowadzanie formuł tabel .......................................................................................... 351 Funkcje tabel Excela .............................................................................................................. 353 O funkcjach tabel ............................................................................................................ 353 Funkcje tabel, które nie wymagają zakresu kryteriów ................................................... 353 Funkcje tabel, które przyjmują wiele kryteriów ............................................................. 355 Funkcje tabel, które wymagają zakresu kryteriów ......................................................... 357 Studium przypadku: Zastosowanie statystycznych funkcji tabel w bazie danych z brakami ................................................................................................... 361 Z tego miejsca… ........................................................................................................... 362 14 Analizowanie danych za pomocą tabel przestawnych ............................363 Czym są tabele przestawne? .................................................................................................. 363 Jak działają tabele przestawne? ..................................................................................... 364 Pojęcia związane z tabelami przestawnymi ................................................................... 365 Konstruowanie tabel przestawnych ...................................................................................... 367 Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu ..................................... 367 Tworzenie tabeli przestawnej na podstawie zewnętrznej bazy danych ......................... 371 Praca z tabelą przestawną i jej dostosowywanie ........................................................... 371 Praca z sumami częściowymi tabeli przestawnej .................................................................. 372 Ukrywanie sum końcowych w tabeli przestawnej .......................................................... 373 Ukrywanie sum częściowych w tabeli przestawnej ........................................................ 373 Dostosowywanie sposobu obliczania sum częściowych ................................................. 374 Zmiana sposobu obliczania podsumowań pola danych ......................................................... 374 Używanie podsumowań różnic ...................................................................................... 374 Używanie podsumowań wartości procentowych ........................................................... 377 Spis treści 13 Używanie podsumowań wartości bieżących ...................................................................379 Używanie podsumowań indeksowych ............................................................................380 Tworzenie niestandardowych obliczeń w tabeli przestawnej ................................................382 Tworzenie pola obliczeniowego .....................................................................................384 Tworzenie elementu obliczeniowego .............................................................................386 Studium przypadku: Tworzenie budżetu z elementami obliczeniowymi ..............................387 Używanie wyników z tabeli przestawnej w formułach arkuszowych .....................................389 Z tego miejsca… ............................................................................................................391 15 Używanie narzędzi modelowania biznesowego w Excelu .......................393 Stosowanie analizy co-jeśli ....................................................................................................393 Konfigurowanie tabeli danych z jedną wartością wejściową ..........................................394 Dodawanie większej liczby formuł do tabeli wejściowej ................................................395 Konfigurowanie tabeli danych z dwoma wartościami wejściowymi ..............................398 Edycja tabeli danych .......................................................................................................399 Praca z funkcją szukania wyniku ............................................................................................400 Jak działa funkcja szukania wyniku? ...............................................................................400 Uruchamianie funkcji szukania wyniku ..........................................................................400 Optymalizacja rentowności produkcji .............................................................................402 Uwaga na temat przybliżeń funkcji szukania wyniku .....................................................403 Analiza progu rentowności .............................................................................................405 Rozwiązywanie równań algebraicznych .........................................................................405 Praca ze scenariuszami ...........................................................................................................407 Zrozumieć scenariusze ....................................................................................................407 Konfigurowanie arkusza do pracy ze scenariuszami .......................................................408 Dodawanie scenariusza ..................................................................................................409 Wyświetlanie scenariusza ...............................................................................................411 Edycja scenariusza ..........................................................................................................411 Scalanie scenariuszy .......................................................................................................412 Generowanie raportu z podsumowaniem ......................................................................413 Usuwanie scenariusza .....................................................................................................414 Z tego miejsca… ............................................................................................................415 16 Użycie regresji w celu śledzenia trendu i sporządzania prognoz ..............417 Konfiguracja i przeprowadzanie wyszukiwania .....................................................................417 Wybór metody regresji ...........................................................................................................418 Użycie prostej regresji dla danych liniowych ..........................................................................419 Analiza trendu za pomocą linii najlepszego dopasowania ..............................................419 Sporządzanie prognoz ....................................................................................................427 Studium przypadku: Analiza trendu i sporządzanie prognoz dla modelu sprzedaży sezonowej ........................................................................................433 14 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela Użycie prostej regresji dla danych nieliniowych .................................................................... 440 Praca z trendem wykładniczym ...................................................................................... 440 Praca z trendem logarytmicznym ................................................................................... 445 Praca z trendem potęgowym ......................................................................................... 447 Użycie analizy regresji wielomianowej ........................................................................... 450 Użycie regresji wielokrotnej w analizie .................................................................................. 454 Z tego miejsca… ........................................................................................................... 456 17 Rozwiązywanie złożonych problemów za pomocą dodatku Solver ..........457 Podstawowe informacje na temat dodatku Solver ................................................................ 457 Zalety Solvera ................................................................................................................. 458 Kiedy używać Solvera? ................................................................................................... 458 Wczytywanie dodatku Solver ................................................................................................ 459 Korzystanie z dodatku Solver ................................................................................................. 460 Dodawanie warunków ograniczających ................................................................................ 463 Zapisywanie rozwiązania jako scenariusza ............................................................................ 465 Konfigurowanie pozostałych opcji Solvera ............................................................................ 466 Wybór metody używanej przez Solvera ......................................................................... 466 Sprawowanie kontroli nad Solverem ............................................................................. 467 Praca z modelami Solvera .............................................................................................. 471 Zrozumieć komunikaty Solvera .............................................................................................. 472 Studium przypadku: Rozwiązywanie zagadnienia transportowego ..................................... 473 Wyświetlanie raportów Solvera ............................................................................................. 476 Raport wyników ............................................................................................................. 476 Raport wrażliwości ......................................................................................................... 477 Raport granic .................................................................................................................. 479 Z tego miejsca… ........................................................................................................... 479 IV TWORZENIE FORMUŁ FINANSOWYCH 18 Tworzenie formuł pożyczek .................................................................483 Zrozumieć wartość pieniądza w czasie .................................................................................. 483 Obliczanie rat pożyczki .......................................................................................................... 485 Analiza spłaty pożyczki ................................................................................................... 485 Praca z pożyczką balonową ............................................................................................ 486 Obliczanie kosztu odsetek, część I .................................................................................. 487 Obliczanie kapitału i odsetek .......................................................................................... 488 Obliczanie kosztu odsetek, część II ................................................................................. 489 Obliczanie skumulowanych wartości kapitału i odsetek ................................................ 489 Spis treści 15 Tworzenie schematu amortyzacji pożyczki ............................................................................491 Tworzenie schematu amortyzacji dla pożyczki o stałym oprocentowaniu ......................491 Tworzenie dynamicznego schematu amortyzacji ...........................................................492 Obliczanie czasu trwania pożyczki ..........................................................................................494 Obliczanie wymaganej stopy procentowej dla pożyczki ........................................................496 Obliczanie kwoty, jaką można pożyczyć .................................................................................498 Studium przypadku: Praca z kredytami hipotecznymi ..........................................................498 Z tego miejsca… ............................................................................................................501 19 Tworzenie formuł inwestycji ................................................................503 Praca ze stopami procentowymi ............................................................................................503 Zrozumieć procent składany ...........................................................................................504 Nominalna stopa procentowa a efektywna stopa procentowa ......................................504 Zamiana nominalnej stopy procentowej na efektywną i odwrotnie ...............................505 Obliczanie przyszłej wartości ..................................................................................................506 Przyszła wartość płatności jednorazowej ........................................................................507 Przyszła wartość serii płatności .......................................................................................507 Przyszła wartość wpłaty początkowej i serii płatności ....................................................508 Osiąganie założonego celu inwestycyjnego ...........................................................................508 Obliczanie wymaganej stopy procentowej .....................................................................509 Obliczanie wymaganej liczby okresów ...........................................................................510 Obliczanie wymaganej stałej wpłaty ..............................................................................510 Obliczanie wymaganej wpłaty wstępnej ........................................................................511 Obliczanie przyszłej wartości przy zmiennych stopach procentowych ............................512 Studium przypadku: Tworzenie schematu inwestycji ...........................................................513 Z tego miejsca… ............................................................................................................515 20 Tworzenie formuł dyskonta .................................................................517 Obliczanie wartości bieżącej ...................................................................................................518 Uwzględnianie inflacji ....................................................................................................519 Obliczanie wartości bieżącej za pomocą funkcji PV() ......................................................519 Inwestycja w papiery wartościowe a inwestycja w nieruchomość .................................520 Zakup a leasing ...............................................................................................................521 Dyskontowanie przepływów pieniężnych ..............................................................................523 Obliczanie wartości bieżącej netto ..................................................................................524 Obliczanie wartości bieżącej netto za pomocą funkcji NPV() ..........................................525 Wartość bieżąca netto i zmienne przepływy pieniężne ..................................................526 Wartość bieżąca netto i nieperiodyczne przepływy pieniężne ........................................527 Obliczanie okresu zwrotu inwestycji ......................................................................................528 Prosty, niezdyskontowany okres zwrotu inwestycji ........................................................529 Dokładny, niezdyskontowany moment zwrotu inwestycji .............................................530 Zdyskontowany okres zwrotu inwestycji ........................................................................531 16 Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela Obliczanie wewnętrznej stopy zwrotu ................................................................................... 531 Użycie funkcji IRR() ......................................................................................................... 532 Obliczanie wewnętrznej stopy zwrotu dla nieperiodycznych przepływów pieniężnych .......................................................... 533 Obliczanie wielu wewnętrznych stóp zwrotu ................................................................. 534 Studium przypadku: Publikowanie książki ........................................................................... 535 Z tego miejsca… ........................................................................................................... 538 Skorowidz ..........................................................................................539 4 W T Y M R O Z D Z I A L E : Praca z tablicami .......................111 Zrozumieć formuły tablicowe .....113 Używanie stałych tablicowych ....115 Używanie iteracji i odwołań cyklicznych ................117 Konsolidowanie danych z wielu arkuszy .........................119 Stosowanie w komórkach reguł sprawdzania poprawności danych ..................124 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ą- dzania 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 budo- wie modeli, które umożliwią ocenę określonych aspektów przedsięwzięcia. Szkielet modelu bizne- sowego bazuje na informacjach wprowadzonych, zaimportowanych lub skopiowanych do arkuszy kal- kulacyjnych. Napędem modelu i jego esencją są z kolei zbiory formuł, które podsumowują 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 uniwer- salnych formuł. W elektronicznym kapeluszu Excela kryje się jeszcze więcej sztuczek. Umożliwiają one konstruowanie 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 tego podrozdziału. Tablica to grupa komórek lub wartości traktowanych w Excelu jak jedna całość. 112 Rozdział 4 Tworzenie zaawansowanych formuł Excel nie odnosi się do takich komórek indywidualnie, lecz pracuje jednocześnie na wszyst- kich komórkach, dzięki czemu można na przykład wprowadzić formułę do każdej komórki, używając jednego tylko polecenia. Tablice można tworzyć, wywołując funkcję, która w wyniku zwraca tablicę, taką jak na przykład REGLINP(), którą omówię w podrozdziale „Funkcje korzystające z tablic lub je zwracające” w dalszej części tego rozdziału. Można też wprowadzić 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 2011 są liczone za pomocą odręb- nych formuł dla każdego z miesięcy: BUDŻET 2011 styczeń BUDŻET 2011 luty BUDŻET 2011 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 2011 Wszystkie trzy formuły można zastąpić jedną formułą tablicową, postępując zgodnie z nastę- pującymi wskazówkami: 1. Zaznacz zakres, którego chcesz użyć w formule tablicowej. W wierszu BUDŻET 2011 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. Powtarzam, nie naciskaj klawisza Enter. 3. Aby zatwierdzić formułę w postaci tablicy, naciśnij klawisze Ctrl+Shift+Enter. Zrozumieć formuły tablicowe 113 Teraz komórki w wierszu BUDŻET 2011 (C13, D13 i E13) będą zawierać tę samą formułę: {=C11:E11*$C$3} Innymi słowy, udało się wprowadzić formułę do trzech różnych komórek, korzystając z jed- nej 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ą. WSKAZÓWKA 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 usta- nawia 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 2011 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, czyli BUDŻET 2011 na 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. Rysunek 4.2. Podczas przetwarzania formuły tablicowej Excel ustanawia powiązanie między komórkami tablicy i zakresem użytym w formule 114 Rozdział 4 Tworzenie zaawansowanych formuł 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. Formuły tablicowe działające na wielu zakresach W poprzednim przykładzie formuła tablicowa działała na jednym zakresie, niemniej for- muł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ĂÊ, czyli 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 Wszystkie te formuły można zastąpić, wprowadzając w zakresie F12:F16 następującą for- mułę tablicową: =B12:B16*E12:E16 Tutaj też formuła tablicowa została utworzona przez zastąpienie każdego odwołania do ko- mórki odpowiednim zakresem i naciśnięcie klawiszy Ctrl+Shift+Enter. Używanie stałych tablicowych 115 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) Używanie stałych tablicowych W pokazanych formułach tablicowych argumentami tablic były zakresy komórek. W roli argumentów tablic można użyć również wartości stałych. Dzięki temu do formuł można wprowadzać wartości bez potrzeby zaśmiecania nimi arkusza. Aby do formuły wprowadzić stałą tablicową, wprowadź wartości bezpośrednio do formuły, przestrzegając jednocześnie następujących zaleceń: Q Wartości powinny być zamknięte w nawiasach klamrowych ({}). Q Aby wartości były traktowane jako wiersze, należy je rozdzielić odwrotnym ukośnikiem. Q 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. For- muła z lewej strony, użyta w zakresie E4:E7, oblicza raty pożyczki na podstawie różnych wysokoś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 oprocentowa- nia zostały wprowadzone pod postacią tablicy bezpośrednio w formule. Î Aby poznać działanie funkcji PMT(), zajrzyj do rozdziału „Obliczanie rat pożyczki” na stronie 485. 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 wyjaśniono, w jaki sposób każda z nich korzysta z tablic (dokładniejsze opisy tych funkcji znajdują się w części drugiej, „Ujarzmianie mocy funkcji”). 116 Rozdział 4 Tworzenie zaawansowanych formuł 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 Tabela 4.1. Niektóre z funkcji Excela korzystające z tablic Nazwa funkcji Argument tablicowy Zwraca w wyniku tablicę 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() 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 Używanie iteracji i odwołań cyklicznych 117 UWAGA 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 formuły tablicowej. Î Tabli
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: