Zrozumieć Excela. Funkcje i wyrażenia - ebook

Funkcje i wyrażenia Excela? Nic prostszego!

• Naucz się podstaw
• Poznaj funkcje
• Zrozum Excela

Na rynku nie brakuje książek opisujących obsługę i zastosowania arkusza kalkulacyjnego MS Excel, żadna jednak nie wprowadzi Cię w tę tematykę tak skutecznie jak ta! Omiń rafy i białe plamy dokumentacji, skorzystaj z doświadczenia autora i śmiało wkrocz w świat niesamowitych możliwości Excela.

Dzięki tej książce poznasz podstawy działania arkusza kalkulacyjnego i najbardziej przydatne funkcje oferowane przez program. Nauczysz się posługiwać tablicami i tabelami, sprawdzać poprawność danych i obliczeń oraz stosować różne sposoby formatowania. Dowiesz się także, jak wykorzystać Excela w roli systemu bazodanowego.

• Podstawowe możliwości arkusza
• Przydatne funkcje Excela
• Zastosowanie tablic i tabel
• Interpretacja wyrażeń
• Kontrola poprawności danych i obliczeń
• Przeszukiwanie tablic
• Formatowanie komórek
• Funkcje bazodanowe
• Analiza danych w Excelu
• Zastosowanie hiperłączy

Dowiedz się, jak działa Excel i jak wykorzystać go w swojej pracy!

Wstęp 15

Rozdział 1. Wprowadzenie 23

• Czym jest arkusz kalkulacyjny Excel? 23
• Rozwój arkusza kalkulacyjnego 25
  
  • Excel w wersji Online 27
  • Kompatybilność z programem Lotus 29

Rozdział 2. Podstawy działania arkusza kalkulacyjnego 31

• Struktura arkusza kalkulacyjnego 31
• Rodzaje informacji przechowywanych w arkuszu 31
  
  • Typy danych 32
  • Wartości logiczne i wartości błędów 33
  • Format wprowadzanych danych liczbowych 33
• Konstrukcja formuł (wyrażeń) w Excelu 39
  
  • Stałe (literały) 39
  • Adresy: komórek lub obszarów 40
  • Nazwy: stałych, komórek, obszarów (zakresów) lub formuł 51
  • Operatory działań 64
  • Wywołania funkcji 71
  • Nawiasy okrągłe 72
  • Użycie spacji w celu poprawy czytelności wyrażeń 73

Rozdział 3. Kilka użytecznych funkcji 75

• Popularne funkcje matematyczne 75
  
  • Odmienność konwencji przy zapisie formuł w matematyce i w Excelu 75
  • Funkcja MODUŁ.LICZBY 78
  • Funkcje PIERWIASTEK i POTĘGA 78
  • Funkcje do obliczania logarytmów 78
  • Funkcje do zaokrąglania liczb 78
  • Funkcja MOD - zasady użycia i ograniczenia 79
  • Wyspecjalizowane funkcje do sumowania 81
  • Funkcja SUMA.ILOCZYNÓW 81
• Funkcja JEŻELI 82
  
  • Funkcja JEŻELI zwracająca wartość pustą 83
  • Pierwszeństwo odwołania przed wartością 83
  • Efektywność funkcji JEŻELI 84
• Funkcja WYBIERZ 84
• Nowe funkcje logiczne (Excel 2016) 85
  
  • Funkcja WARUNKI 85
  • Funkcja PRZEŁĄCZ 86
• Funkcja JEŻELI.BŁĄD 87
• Funkcje ułatwiające odczytywanie i wyliczanie adresów 88
  
  • Numery wiersza i kolumny w arkuszu 88
  • Funkcje ILE.WIERSZY i LICZBA.KOLUMN 90
  • Funkcja ADRES 90
  • Funkcja ADR.POŚR 92
• Funkcja PODAJ.POZYCJĘ 97
• Funkcje specjalne L (N) i T 99
• Funkcja KOMÓRKA 100
  
  • Odczyt nazwy arkusza lub nazwy pliku 102
  • Śledzenie komórki 103
• Funkcja INFO 103

Rozdział 4. Użycie tablic i tabel 105

• Podstawowe pojęcia dotyczące tablic i macierzy 105
• Reprezentacja tablic w Excelu 106
• Wprowadzanie formuł tablicowych 108
• Operacje tablicowe zaimplementowane w Excelu 110
  
  • Działania arytmetyczne z użyciem tablic 110
  • Zasada superpozycji 112
  • Łączenie tablic i tworzenie tablic mieszanych 114
• Funkcje do wykonywania działań na macierzach (tablicach) 116
  
  • Mnożenie macierzowe 116
  • Transpozycja - funkcja i operacja 117
  • Macierz odwrotna i wyznacznik macierzy 121
  • Macierz jednostkowa 121
  • Macierze trójkątne 122
• Użycie funkcji JEŻELI i WYBIERZ z argumentami grupowymi 123
  
  • Użycie funkcji JEŻELI z argumentami grupowymi w wyrażeniu tablicowym 123
  • Użycie funkcji JEŻELI z argumentami grupowymi w wyrażeniu zwykłym 125
  • Funkcja WYBIERZ z argumentami w postaci tablic 126
  • Dodatkowe informacje o funkcji WYBIERZ 128
  • Interpretacja argumentów grupowych przez funkcje JEŻELI i WYBIERZ 130
• Używanie nazw tablic i formuł tablicowych 130
• Odwołania strukturalne w tabelach 131
  
  • Reguły składni odwołań strukturalnych 133
  • Elementy składowe tabeli 134
  • Predefiniowane specyfikatory wierszy 134
  • Operatory odwołania 134
  • Odwołania strukturalne kwalifikowane i niekwalifikowane 135
  • Przykłady użycia odwołań strukturalnych 135
  • Praca z odwołaniami strukturalnymi 136
• Użycie i interpretacja odwołań wielopoziomowych 140
  
  • Ogólna charakterystyka odwołań 3-W 140
  • Funkcje, które akceptują odwołania 3-W 141
  • Tworzenie odwołań wielopoziomowych w arkuszu 142
  • Nadawanie nazwy odwołaniu wielopoziomowemu 142
  • Wpływ zmian wprowadzonych w arkuszu na odwołania 3-W 143
  • Przykłady wykorzystania odwołań 3-W 143

Rozdział 5. Modyfikacja zawartości arkusza 147

• Wyświetlanie danych w arkuszu 147
  
  • Wyświetlanie tekstów formuł zamiast wyników 148
• Sposoby zaznaczania komórek 149
  
  • Zaznaczanie niewielkich zakresów komórek 149
  • Zaznaczanie nieprzylegających komórek 149
  • Zaznaczanie całego arkusza, całych wierszy lub kolumn 150
  • Zaznaczanie pierwszych lub ostatnich komórek arkusza, wierszy lub kolumn 151
  • Zaznaczanie zakresów komórek o znanych adresach 151
  • Zaznaczanie dużych zakresów komórek 151
• Edycja 153
  
  • Użycie klawisza F9 w czasie edycji 153
  • Znajdowanie i zamiana 156
• Kopiowanie formuł 157
  
  • Wklejanie specjalne 159
  • Modyfikacja adresów w formułach przy kopiowaniu 160
  • Tworzenie serii odwołań bezwzględnych 161
  • Kopiowanie odwołań do pełnych wierszy i kolumn 162
  • Kopiowanie formuł zawierających nazwy 162
  • Kopiowanie formuł z odwołaniami względnymi bez modyfikacji odwołań 163
• Komórki scalone 164
  
  • Scalanie komórek w sposób standardowy 165
  • Scalanie komórek w sposób niestandardowy 167
  • Kopiowanie formuł w komórkach scalonych 167
  • Formuły tablicowe w komórkach scalonych 169
• Kopiowanie i łączenie formuł i stałych w komórkach 169
• Kopiowanie tylko widocznych komórek 170
• Kopiowanie za pomocą Schowka pakietu Office 171
• Przenoszenie komórek zawierających formuły 173
  
  • Przenoszenie powiązanych skoroszytów 174
• Wstawianie i usuwanie komórek, wierszy lub kolumn 174
• Edycja formuł tablicowych 176
  
  • Zmiana zakresu formuł tablicowych 176
  • Zamiana wyników formuł tablicowych na stałe 177
  • Przekształcanie formuł tablicowych w formuły zwykłe 178
• Kopiowanie formuł tablicowych 178
• Przeliczanie arkusza i skoroszytu 179
• Funkcje i działania ulotne 181
• Zmiana czcionki i początkowego wyglądu arkusza 182

Rozdział 6. Odwołania do elementów lub fragmentów tablic 185

• Użycie funkcji INDEKS 185
  
  • Argument główny powinien być tablicą lub odwołaniem 187
  • Użycie zera lub pominięcie wartości indeksu 187
  • Użycie funkcji INDEKS do obszaru dwuwymiarowego 187
  • Odwołanie do komórki o wyliczonym adresie 188
  • Łączenie fragmentów tablic 189
  • Zastosowania odwołaniowej formy funkcji INDEKS 189
  • Odwracanie porządku elementów w tablicy 190
  • Uproszczenie zapisu przy wielokrotnym zagnieżdżeniu funkcji JEŻELI 190
  • Listowanie zawartości tablicy jednowymiarowej wierszami lub kolumnami 191
• Użycie funkcji PRZESUNIĘCIE 191
  
  • Proste przykłady użycia funkcji PRZESUNIĘCIE 194
  • Odwracanie porządku elementów w zakresie 195
• Bez użycia funkcji 196
  
  • Początkowe elementy tablicy 196
  • Część wspólna zakresów 196
• Dynamiczne generowanie tablic i zakresów o zadanych rozmiarach 197
  
  • Definicje tablic bazowych 197
  • Wykorzystanie tablic bazowych do definicji innych tablic 199
  • Plan eksperymentu czynnikowego 200
  • Selektywne sumowanie elementów tablic dwuwymiarowych 201
  • Łączenie tablic z wykorzystaniem funkcji MACIERZ.ILOCZYN 203
  • Zakresy dynamiczne 204
  • Formuły tablicowe uwzględniające faktyczne rozmiary obszaru formuły 206

Rozdział 7. Klasyfikacja funkcji i interpretacja wyrażeń 209

• Struktura wyrażeń w Excelu 209
  
  • Wyrażenia jedno- i wielowartościowe 209
  • Struktura tablicy lub jej brak 210
  • Potrzeba zatwierdzenia tablicowego lub jej brak 211
• Podział funkcji na grupy 213
  
  • Rodzaje argumentów funkcji 214
  • Ogólne zasady interpretacji argumentów funkcji 215
  • Przegląd ważniejszych funkcji z podziałem na grupy 215
  • Podział funkcji ze względu na liczbę wartości wynikowych 223
  • Funkcje, które mogą zwracać w wyniku odwołania 224
• Funkcje bliźniacze odmiennie interpretujące argumenty różnych typów 225
• Pomijanie argumentów, argumenty opcjonalne, argumenty puste 227
  
  • Puste argumenty a puste komórki 228
  • Symulowane pominięcie argumentu opcjonalnego 229
• Konwersja typów argumentów w operacjach arytmetycznych i tekstowych 231
  
  • Połączone użycie operatorów arytmetycznych i tekstowych 231
  • Użycie operatorów arytmetycznych do danych logicznych 231
  • Użycie plusa jednoargumentowego 233
  • Funkcje ułatwiające konwersję typów danych 234
• Skutki użycia argumentów innego typu niż przewidziane w opisie funkcji 236
  
  • Konwersja na tekst 236
  • Konwersja na wartość logiczną 236
  • Konwersja na wartość liczbową 237
  • Użycie liczb z częścią ułamkową zamiast liczb całkowitych 239
• Użycie odwołań do zamkniętych skoroszytów jako argumentów funkcji 240
• Powiązania nazw z adresami różnego typu 242
  
  • Nazwy skojarzone z adresami absolutnymi i względnymi 242
  • Użycie samego wykrzyknika jako kwalifikatora w definicjach nazw 245
• Zasady interpretacji nazw i adresów w zwykłych wyrażeniach 247
  
  • Dane grupowe w zwykłych wyrażeniach 247
  • Dane grupowe jako argumenty funkcji 248
  • Nazwy i adresy w zwykłych wyrażeniach - podsumowanie 250
• Zasady interpretacji nazw i adresów w wyrażeniach tablicowych 251
  
  • Zestawienie sposobów interpretacji danych grupowych przez funkcje różnych rodzajów 254

Rozdział 8. Kontrola poprawności danych i obliczeń 257

• Kontrola poprawności wprowadzania danych 257
  
  • Konfiguracja listy rozwijanej 261
  • Lista rozwijana jako podpowiedź 263
  • Lista rozwijana tworzona ad hoc 263
  • Zakreślanie niepoprawnych danych 264
• Tworzenie dynamicznych list rozwijanych w arkuszu 265
  
  • Zestawienie pojedynczego wyboru (permutacja) 266
  • Wybór wielokrotny 269
  • Lista wyboru z aktywną podpowiedzią 271
• Tworzenie list zależnych 272
  
  • Wynik wyboru w innej komórce niż lista rozwijana 274
  • Lista z wykorzystaniem "Aparatu fotograficznego" 275
  • Lista z wykorzystaniem formatowania warunkowego 275
  • Listy z obsługą zdarzenia Change 276
  • Wykorzystanie formantów pola kombi 276
• Sygnalizacja i kontrola błędów w obliczeniach 278
  
  • Kody błędów i ich propagacja 278
  • Funkcje do diagnozowania błędów obliczeń 278
  • Maskowanie wartości błędów 281
  • Ukrywanie wartości zerowych 285
• Puste teksty i puste komórki 287
  
  • Identyfikacja 287
  • Puste komórki jako dane 288
  • Usuwanie pustych tekstów 289
• Wykorzystanie kolumn pomocniczych 290
  
  • Eliminacja błędnych lub brakujących danych 290
  • Przyspieszenie obliczeń 292
• Narzędzia wspomagające analizę zawartości arkusza i poprawności formuł 292
  
  • Poprzedniki i komórki zależne 293
  • Identyfikacja przyczyn błędów 294
  • Czujki 295
  • Szacowanie formuł 296

Rozdział 9. Tablicowanie funkcji i wyszukiwanie w tablicach 299

• Tablicowanie funkcji jednej zmiennej 299
  
  • Funkcja dana w postaci parametrycznej 304
• Tablicowanie funkcji dwu zmiennych 304
• Specyfika użycia polecenia Tabela danych 308
• Przeszukiwanie tablic funkcji jednej zmiennej 310
  
  • Funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO 311
  • Funkcja WYSZUKAJ 315
  • Połączenie funkcji PODAJ.POZYCJĘ i INDEKS 318
  • Wyszukiwanie pierwszej lub ostatniej liczby lub tekstu 319
  • Dane liczbowe uporządkowane rosnąco - zaokrąglenie w górę 321
  • Wyszukiwanie wartości najbliższej w stosunku do wzorca 322
  • Wyszukiwanie odpowiedników wartości zduplikowanych 322
  • Funkcja CZĘSTOŚĆ 325
  • Odczyt gęstości roztworu o podanym stężeniu 330
• Wyszukiwanie znaczących danych w tablicach 331
  
  • Eliminacja niepoprawnych danych 331
  • Listowanie danych rozproszonych 332
• Wyszukiwanie danych w układzie dwuwymiarowym 333
  
  • Wyszukiwanie danych w zakresie 334
  • Wyszukiwanie danych w tablicy 336

Rozdział 10. Kody formatu komórek w Excelu 339

• Bezpośrednie formatowanie komórek w arkuszu 340
  
  • Typowe rodzaje formatowania zawartości komórek 340
  • Struktura kodów formatu 341
  • Wzorce formatu sekcji 344
  • Wykorzystanie kodów formatu przy konstrukcji list rozwijanych 349
  • Wykorzystanie formatu ułamkowego i jego alternatywa 350
  • Formatowanie daty i czasu 352
  • Odczyt kodu formatu 356
  • Wykorzystanie kodów formatu w funkcjach trygonometrycznych i cyklometrycznych 357
• Uproszczony system informowania o formacie komórki 359

Rozdział 11. Formatowanie warunkowe 361

• Opcje formatowania warunkowego dostępne we wszystkich wersjach Excela 362
• Opcje formatowania warunkowego dostępne od wersji 2007 Excela 364
  
  • Formatowanie komórek zakresu przy użyciu skali barw 365
  • Formatowanie komórek zakresu przy użyciu pasków danych 367
  • Formatowanie komórek zakresu przy użyciu zestawu ikon 369
  • Formatowanie komórek zawierających tekst, liczbę albo wartości daty lub godziny 372
  • Formatowanie wartości sklasyfikowanych jako pierwsze i ostatnie 375
  • Formatowanie wartości powyżej lub poniżej średniej 376
  • Formatowanie wartości unikatowych lub zduplikowanych 378
  • Używanie formuły do określenia komórek, które należy sformatować 380
  • Zasady ustalania zakresu formatowania i interpretacji formuł w formatowaniu warunkowym 381
  • Usuwanie formatów warunkowych 384
  • Zamiana formatowania warunkowego na stałe 384
  • Zarządzanie pierwszeństwem reguł formatowania warunkowego 385
  • Znajdowanie komórek mających formaty warunkowe 386

Rozdział 12. Proste bazy danych 389

• Struktura tabeli bazy danych 389
• Sortowanie i filtrowanie 390
  
  • Kierunek i kolejność sortowania 390
  • Sortowanie czy filtrowanie? 392
  • Sortowanie zakresów zawierających formuły 396
  • Odwołania do komórek z sortowanych obszarów 397
  • Użycie autofiltra 399
• Filtr zaawansowany 401
  
  • Zakres kryteriów 402
  • Użycie filtra zaawansowanego 406
• Użycie formuł zamiast filtra 407
• Użycie funkcji do odzyskiwania danych z tabeli lub listy 409
• Operacja konsolidacji 414
  
  • Konsolidacja według położenia 414
  • Konsolidacja według kategorii 417

Rozdział 13. Funkcje do obsługi baz danych 419

• Funkcje podsumowań 419
  
  • Funkcja SUMY.CZĘŚCIOWE 419
  • Polecenie Suma częściowa 422
  • Funkcja AGREGUJ 422
• Funkcje zliczania i sumowania warunkowego 426
  
  • Składnia funkcji 426
  • Postać kryteriów 428
  • Odpowiedniość między zakresem kontrolnym a zakresem sumowania 432
  • Uwzględnianie kilku kryteriów 432
  • Alternatywy funkcji LICZ.JEŻELI 433
  • Formuły równoważne funkcjom LICZ.WARUNKI i SUMA.WARUNKÓW 435
  • Formuły analogiczne do funkcji ŚREDNIA.JEŻELI i ŚREDNIA.WARUNKÓW 436
  • Obsługa długich ciągów cyfr 437
  • Wyrażenia warunkowe tworzone z użyciem funkcji LICZ.JEŻELI 437
  • Odwrotne wykorzystanie funkcji LICZ.JEŻELI 438
  • Inne przykłady użycia funkcji zliczania i sumowania warunkowego 438
• Funkcje maksimum i minimum warunkowego (Excel 2016) 440
• Zliczanie pustych i niepustych komórek 441
• Grupa funkcji baz danych 444
  
  • Połączenie funkcji baz danych z tabelą danych 447

Rozdział 14. Typowe problemy analizy danych 449

• Numerowanie wierszy i kolumn z danymi 449
  
  • Licznik statyczny 449
  • Licznik oparty na odwołaniu względnym do poprzedniej komórki 449
  • Licznik z wykorzystaniem funkcji WIERSZ lub NR.KOLUMNY 450
  • Licznik oparty na funkcji MAX 451
  • Licznik generujący określoną liczbę numerów 452
  • Licznik działający z filtrem lub ukrytymi wierszami 453
• Ranking danych bez sortowania 453
  
  • Funkcje MIN.K i MAX.K 454
  • Wyznaczanie wskaźników położenia 454
  • Ustalanie rankingu za pomocą formuł 456
  • Uwzględnienie dodatkowych kryteriów sortowania 458
• Zliczanie wartości unikatowych 459
  
  • Dane zawarte w zakresie 459
  • Dane zawarte w tablicy 461
  • Wykorzystanie funkcji CZĘSTOŚĆ, dane w zakresie lub tablicy 462
• Numerowanie wartości unikatowych 463
• Tworzenie listy unikatów 464
  
  • Formuły z funkcją LICZ.JEŻELI 464
  • Formuły z funkcją WYSZUKAJ 467
  • Formuły z funkcją CZĘSTOŚĆ 468
• Porównywanie zbiorów danych 469
  
  • Wyszukiwanie wartości wspólnych (iloczyn zbiorów) 469
  • Wyszukiwanie wartości, które występują tylko w jednym zbiorze (różnica zbiorów) 470
  • Wyszukiwanie wartości, które występują w obu zbiorach (suma zbiorów) 471
• Transpozycja danych zduplikowanych 472
  
  • Z układu pionowego na poziomy 472
  • Z układu poziomego na pionowy 473

Rozdział 15. Obsługa baz danych za pomocą programu Microsoft Query 475

• Nawiązywanie połączenia ze źródłem danych 476
• Definiowanie kwerendy za pomocą Kreatora kwerend 477
• Definiowanie kwerendy bezpośrednio w programie Microsoft Query 477
• Ponowne używanie i udostępnianie kwerend 479
• Praca z danymi w programie Excel 479
• Formatowanie pobranych danych 481
• Odświeżanie danych zewnętrznych 482
• Określanie rekordów do pobrania przy użyciu kryteriów 482
  
  • Co to są kryteria? 482
  • Spełnianie pojedynczego warunku 483
  • Spełnianie wielu warunków 483
  • Wyszukiwanie podobnych rekordów 484
  • Określanie zakresu wartości 484
  • Przykłady wyrażeń z operatorami porównania 485
  • Korzystanie z obliczania wartości kryteriów 485
  • Przykłady wyrażeń wykorzystujących operatory arytmetyczne 485
  • Tworzenie pola obliczeniowego za pomocą wyrażenia 486
  • Tworzenie pola obliczeniowego za pomocą funkcji 487
  • Funkcje dostępne w polu obliczeniowym 487
  • Przykłady wyrażeń z operatorami logicznymi 488
  • Przykłady wyrażeń z operatorami innego typu 488
• Tworzenie kwerendy parametrycznej 489
• Pobieranie rekordów z wielu tabel 490
  
  • Sprzęganie tabel 490
  • Sprzężenia wewnętrzne 491
  • Sprzężenia zewnętrzne 491
  • Zmienianie typu sprzężenia między tabelami w kwerendzie 491
  • Samosprzężenia 491
  • Pełne sprzężenia zewnętrzne 492
  • Sprzężenia różnicowe 493
• Pobieranie tabel z różnych skoroszytów do jednej kwerendy 494

Rozdział 16. Hiperłącza 497

• Co to są hiperłącza? 497
• Tworzenie i usuwanie hiperłączy w sposób bezpośredni 498
  
  • Tworzenie hiperłącza 498
  • Ustawianie adresu podstawowego hiperłączy w skoroszycie 499
  • Usuwanie hiperłącza 500
  • Zaznaczanie komórki z hiperłączem 500
  • Automatyczne tworzenie hiperłączy 500
  • Wyłączenie opcji automatycznego formatowania na stałe 501
  • Jednorazowe wyłączenie opcji automatycznego formatowania 501
  • Powrót z hiperłącza 501
  • Dodatkowy tekst w komórce z hiperłączem 501
• Tworzenie hiperłączy za pomocą funkcji HIPERŁĄCZE 502
  
  • Składnia i ogólne zasady użycia funkcji 502
  • Skok do skoroszytu i wyświetlenie określonego tekstu skoku 503
  • Skok do określonej komórki w arkuszu 503
  • Skok do konkretnego zakresu arkusza i wyświetlenie określonego tekstu skoku 504
  • Skok do określonej lokalizacji w dokumencie programu Word 504
  • Skok do skoroszytu na serwerze sieciowym 504
  • Skok do skoroszytu na innym dysku 504
  • Skok do określonego obszaru w skoroszycie zewnętrznym 504
  • Skok do innej komórki w tym samym skoroszycie 505
  • Użycie alternatywnego stylu adresowania do określenia adresu miejsca docelowego łącza 506
  • Użycie odwołania jako źródła adresu miejsca docelowego łącza 506
  • Użycie funkcji HIPERŁĄCZE z argumentem tablicowym 507
  • Hiperłącza warunkowe 507
  • Puste hiperłącza 507
  • Łącze do programu pocztowego 508
  • Wykorzystanie funkcji HIPERŁĄCZE w sposób niestandardowy 508

Rozdział 17. Wybrane funkcje wbudowane w Excelu 509

Rozdział 18. Słowniczek ważniejszych pojęć 553

Skorowidz 585