Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00032 007578 18991676 na godz. na dobę w sumie
Zrozumieć Excela. Funkcje i wyrażenia - książka
Zrozumieć Excela. Funkcje i wyrażenia - książka
Autor: Liczba stron: 600
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-5692-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook (-35%), audiobook).

Funkcje i wyrażenia Excela? Nic prostszego!

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.

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

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

Darmowy fragment publikacji:

Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Redaktor prowadzący: Małgorzata Kulik Projekt okładki: Studio Gravite / Olsztyn Obarek, Pokoński, Pazdrijowski, Zaprucki Grafika na okładce została wykorzystana za zgodą Shutterstock.com Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/zrexfw Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. ISBN: 978-83-283-5692-4 Copyright © Helion 2019 Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis treści 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 Poleć książkęKup książkę 4 Zrozumieć Excela. Funkcje i wyrażenia 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 Poleć książkęKup książkę Spis treści 5 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 Poleć książkęKup książkę 6 Zrozumieć Excela. Funkcje i wyrażenia 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 Poleć książkęKup książkę Spis treści 7 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 Poleć książkęKup książkę 8 Zrozumieć Excela. Funkcje i wyrażenia 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 Poleć książkęKup książkę Spis treści 9 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 Poleć książkęKup książkę 10 Zrozumieć Excela. Funkcje i wyrażenia 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 Poleć książkęKup książkę Spis treści 11 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 Poleć książkęKup książkę 12 Zrozumieć Excela. Funkcje i wyrażenia 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 Poleć książkęKup książkę Spis treści 13 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 Poleć książkęKup książkę 14 Zrozumieć Excela. Funkcje i wyrażenia Poleć książkęKup książkę Rozdział 6. Odwołania do elementów lub fragmentów tablic Użycie funkcji INDEKS W przypadku, gdy nie potrzeba ujawniać całej tablicy wyników funkcji czy operacji tablicowej, a jedynie wybrane elementy wynikowej tablicy, należy użyć funkcji INDEKS. Umożliwia ona wybór pojedynczego elementu lub wskazanych elementów tablicy danych lub wyników. Jeżeli funkcja ma zwrócić pojedynczą wartość, zatwierdza się ją samym klawiszem Enter, jeżeli zaś wynikiem ma być tablica lub obszar, zaznacza się najpierw miejsce na wynik, a następnie zatwierdza przez kombinację Ctrl+Shift+Enter. Funkcja INDEKS ma formalnie (według dokumentacji) dwa warianty składni: tablicową i adresową. W zależności od tego, co jest pierwszym argumentem, może mieć zastosowanie forma tablicowa: INDEKS(tablica; nr_wiersza; nr_kolumny) lub forma adresowa, zwana też odwołaniową, która różni się tylko obecnością dodatkowego opcjo- nalnego argumentu: INDEKS(odwołanie; nr_wiersza; nr_kolumny; nr_obszaru) Pierwszy argument funkcji INDEKS będę nazywał argumentem głównym, pozostałe — argu- mentami indeksowymi. Tablica jest tablicą albo wyrażeniem dającym w wyniku tablicę, a odwołanie jest zakresem ko- mórek, który może obejmować więcej niż jeden obszar. Jeśli odwołanie obejmuje adresy kilku obszarów, ich listę należy ująć w nawiasy. W tym miejscu może wystąpić również wyrażenie, dające w wyniku odwołanie. Nr_wiersza wybiera wiersz tablicy lub obszaru, z którego ma pochodzić wartość wynikowa. Jeśli pominiemy ten argument lub zostanie użyta wartość 0, to w zależności od sposobu użycia funk- cji wybrane zostaną albo wszystkie wiersze, albo wiersz bieżący, albo pierwszy. Poleć książkęKup książkę 186 Zrozumieć Excela. Funkcje i wyrażenia Argument nr_kolumny określa kolumnę tablicy lub obszaru, z której ma pochodzić wartość wyniko- wa. Jeśli pominiemy go lub zostanie użyta wartość 0, to w zależności od sposobu użycia funkcji wy- brane zostaną albo wszystkie kolumny, albo kolumna bieżąca, albo pierwsza. Nr_wiersza i nr_kolumny muszą odnosić się do elementu lub komórki wewnątrz tablicy lub obszaru, w innym przypadku funkcja INDEKS podaje w wyniku wartość błędu #ADR!, ale tylko w tych wierszach lub kolumnach, które zostały błędnie zaadresowane. Wiersze i kolumny argumentu głównego są numerowane od 1. Jeżeli odwołanie obejmuje kilka obszarów, numeracja każdego z nich jest niezależna. Jeśli zostanie użyty zarówno argument nr_wiersza, jak i nr_kolumny, to funkcja INDEKS daje w wyni- ku odwołanie do komórki na przecięciu wiersza i kolumny o numerach podanych w nr_wiersza i nr_kolumny lub odpowiednią wartość z tablicy. Jeśli argument główny zawiera tylko jeden wiersz lub kolumnę, to jeden z argumentów nr_wiersza lub nr_kolumny (wraz z poprzedzającym średnikiem) można pominąć. Można również pozostawić sam średnik lub użyć argumentu o wartości 0 lub 1. Jeśli argumentem głównym funkcji INDEKS jest odwołanie do obszaru dwuwymiarowego lub za- kresu nieregularnego, aby wyrażenie było poprawne składniowo, w wywołaniu muszą być co najmniej dwa średniki; w innych przypadkach wystarczy jeden. Argument nr_obszaru określa, z którego obszaru ma zostać zwrócony wynik. Ma on znaczenie, gdy odwołanie obejmuje kilka obszarów; w razie pominięcia tego argumentu (wraz z poprzedzają- cym średnikiem) uwzględniony zostanie pierwszy obszar. Argument ten nie może mieć wartości ze- rowej, nie można też pozostawić w wywołaniu trzeciego średnika bez wartości. Jeśli argumen- tem głównym jest odwołanie do pojedynczego obszaru lub tablica, to argument nr_obszaru (jeśli wystąpi) musi mieć wartość 1. Wynika stąd, że w istocie nie ma dwóch wariantów składni, a tylko jeden — wariant odwołaniowy z ograniczeniami dotyczącymi argumentu nr_obszaru. Jeśli argument główny jest tablicą, funkcja zwraca w wyniku zawsze pojedynczą wartość lub ta- blicę, jeśli zaś ten argument jest odwołaniem, funkcja zwraca odwołanie, o ile jest to możliwe, a jeśli nie — zwraca wartość lub tablicę wartości. Argumenty indeksowe mogą mieć również formę tablic lub zakresów komórek zawierających numery wierszy, kolumn lub obszarów. Ta funkcjonalność nie jest jednak opisana w oficjalnej dokumentacji. Należy jej używać z dużą ostrożnością, szczególnie gdy formuły są złożone, bo wynik nie zawsze da się łatwo przewidzieć [B20]. Właśnie w takich przypadkach funkcja INDEKS po- mimo użycia formy adresowej zwraca tablice, a nie odwołania. Argumentem głównym funkcji INDEKS nie mogą być tzw. odwołania 3-W (piszę o nich w roz- dziale 4., w podrozdziale „Użycie i interpretacja odwołań wielopoziomowych”), gdyż w sensie for- malnym nie są to ani odwołania, ani tablice. Poleć książkęKup książkę Rozdział 6.  Odwołania do elementów lub fragmentów tablic 187 Argument główny powinien być tablicą lub odwołaniem Jeżeli jako argumentu głównego funkcji INDEKS użyto stałej lub wyrażenia zwracającego pojedynczą wartość, wynik jest poprawny tylko wtedy, gdy jest to liczba. W każdym innym przypadku funkcja zwraca wartość błędu (jeżeli wyrażenie zwraca błąd, otrzymamy wartość tego błędu, je- śli zaś wyrażenie zwraca wartość nieliczbową, wynikiem będzie wartość błędu #ARG!). Przyczy- ną takiego zachowania funkcji INDEKS jest inna — w porównaniu z pojedynczą wartością — struktura tablicy. Aby uniknąć tego problemu (kiedy tablica może czasem być pojedynczą warto- ścią nieliczbową), należy zastosować konstrukcję: = INDEKS(JEŻELI({1};tablica); nr_wiersza; nr_kolumny) Opisana wyżej sytuacja dotyczy również innych funkcji tablicowych. Użycie zera lub pominięcie wartości indeksu Jeżeli wykorzystany zostanie tylko jeden argument: nr_wiersza lub nr_kolumny, a drugi zostanie pominięty (gdy argumentem jest odwołanie do zakresu dwuwymiarowego, średnik musi pozostać) lub jako argument indeksowy zostanie podana wartość 0, to INDEKS da w wyniku odwołanie lub tablicę z całym wierszem lub kolumną. Aby wyświetlić te wartości w arkuszu jako sekwencję, należy wprowadzić INDEKS jako funkcję tablicową, naciskając Ctrl+Shift+Enter. Przy zatwierdzeniu samym klawiszem Enter, jeśli argumentem głównym jest odwołanie, funkcja INDEKS zwraca wartość z tego samego wiersza lub tej samej kolumny na zasadzie rzutu prostokątnego, analo- gicznie jak w przypadku odwołania do zakresu w zwykłej formule, natomiast jeśli argumentem głównym jest tablica, INDEKS zwraca jej pierwszy element (por. rozdział 7., punkt „Dane gru- powe w zwykłych wyrażeniach”). Opis ten dotyczy typowego użycia funkcji INDEKS, to znaczy takiego, w którym argumenty indeksowe nie mają struktury tablicy, lecz są zwykłymi liczbami. Użycie funkcji INDEKS do obszaru dwuwymiarowego Przykład 6.1. Jako źródło danych wykorzystamy zakres (obszar) TA o wymiarach 4×4, umieszczony w arkuszu. 1 3 6 5 2 5 8 7 4 –3 10 4 6 7 –2 3 Jego pierwszą kolumnę oznaczymy TX, a pierwszy wiersz — TY. Odwołanie do drugiego elementu (wiersza) tablicy TX może mieć jedną z postaci: = INDEKS(TX;2) = INDEKS(TX;2;) = INDEKS(TX;2;0) = INDEKS(TX;2;1) Poleć książkęKup książkę 188 Zrozumieć Excela. Funkcje i wyrażenia Podobnie, odwołanie do trzeciego elementu (kolumny) tablicy TY może mieć postać (alterna- tywnie): = INDEKS(TY;3) = INDEKS(TY;;3) = INDEKS(TY;0;3) = INDEKS(TY;1;3) Jeśli TA oznacza tablicę lub zakres dwuwymiarowy, możliwe są na przykład następujące odwo- łania tablicowe: = INDEKS(TA;1;) = INDEKS(TA;1;0) — w obu przypadkach wynikiem jest cały pierwszy wiersz tablicy TA. Jeśli TA jest odwołaniem do zakresu, wyrażenie = INDEKS(TA;1) byłoby błędne, ale już wyrażenie = INDEKS(+TA;1) jest poprawne. W przypadku, gdy TA oznacza stałą tablicową, prawidłowe są oba ostatnie wyrażenia. = INDEKS(TA;;2) = INDEKS(TA;0;2) — w obu przypadkach wynikiem jest cała druga kolumna tablicy TA. Jeśli TA jest odwołaniem do zakresu, wyrażenie = INDEKS(TA;2) byłoby błędne. Pojedynczy element tablicy lub obszaru TA odczytamy za pomocą wyrażenia: = INDEKS(TA;2;4) W tym przypadku będzie to element na przecięciu drugiego wiersza i czwartej kolumny (war- tość 7 lub odwołanie do komórki zawierającej tę wartość). Odwołanie do komórki o wyliczonym adresie Pierwszy argument funkcji INDEKS może reprezentować cały arkusz, co można zapisać jako za- kres wierszy $1:$1048576 (od wersji 2007) bądź $1:$65536 (we wcześniejszych wersjach). Jeżeli odwołanie ma dotyczyć arkusza innego niż bieżący, należy je poprzedzić kwalifikatorem arkusza. Można oczywiście użyć również mniejszego zakresu wierszy lub odpowiedniego zakresu ko- lumn, począwszy od kolumny A. Użycie funkcji INDEKS w takiej formie umożliwia odwołanie do komórki lub komórek o wyliczonym adresie. Jeśli nie pamiętamy numeru ostatniego wiersza lub ostatniej kolumny, to wszystko można wyliczyć:  numer ostatniego wiersza — = ILE.WIERSZY(A:A)  numer ostatniej kolumny — = LICZBA.KOLUMN(1:1)  oznaczenie literowe ostatniej kolumny — = PODSTAW(ADRES(1;LICZBA.KOLUMN(1:1);4);1; ) Aby odczytać wartość z komórki o określonych absolutnych numerach wiersza i kolumny, można użyć wyrażenia: = INDEKS(ADR.POŚR( nazwa_arkusza!1: ILE.WIERSZY($A:$A)); numer_wiersza; numer_kolumny) Poleć książkęKup książkę Rozdział 6.  Odwołania do elementów lub fragmentów tablic 189 Ten sam wynik można uzyskać za pomocą funkcji ADR.POŚR i ADRES: = ADR.POŚR(ADRES(numer_wiersza; numer_kolumny;;; nazwa_arkusza)) Jeżeli odwołanie dotyczy bieżącego arkusza, w obu wersjach można pominąć nazwę_arkusza. Łączenie fragmentów tablic Przykład 6.2. Załóżmy, że mamy dwie tablice (lub obszary danych): tabl1, obejmującą trzy kolumny, oraz tabl2, złożoną z dwóch kolumn. Za pomocą odpowiedniej formuły tablicowej w połączeniu z funkcją INDEKS możemy utworzyć tablicę złożoną z pięciu kolumn, tak jakby były zapisane obok siebie. = {1;0;0;0;0}*INDEKS(tabl1;;1)+ {0;1;0;0;0}*INDEKS(tabl1;;2)+ {0;0;1;0;0}*INDEKS(tabl1;;3)+ {0;0;0;1;0}*INDEKS(tabl2;;1)+ {0;0;0;0;1}*INDEKS(tabl2;;2) Ta formuła jest analogiczna do użytej w przykładzie 4.2. Jej koncepcja polega na podziale ory- ginalnych tablic na kolumny za pomocą funkcji INDEKS i połączeniu tak otrzymanych kolumn za pomocą operacji tablicowych. Ze względu na zasadę superpozycji, która umożliwia dopasowanie wymiarów przez kopiowanie tylko pojedynczych wierszy lub kolumn, przy takiej konstrukcji wyrażenia nie ma możliwości uwzględnienia tablicy dwuwymiarowej w całości. Właściwości tej formuły są podobne do tych z przykładu 4.2.: tablice muszą zawierać tylko dane liczbowe, for- muła wymaga zatwierdzenia tablicowego, ale po takim zatwierdzeniu może być użyta do dal- szych obliczeń. Jest to wyrażenie z odwołaniem lub tablicą. Wygodniejsze okazuje się w tym przypadku użycie funkcji WYBIERZ: = WYBIERZ({1;2;3;4;5}; INDEKS(tabl1;;1); INDEKS(tabl1;;2); INDEKS(tabl1;;3); INDEKS(tabl2;;1); INDEKS(tabl2;;2)) albo jeszcze prościej, ale w sposób mniej oczywisty: = WYBIERZ({1;1;1;2;3}; tabl1; INDEKS(tabl2;;1); INDEKS(tabl2;;2)) Czytelnikowi pozostawiam odpowiedź na pytanie, dlaczego tablicy tabl2 nie można przywołać w jednej pozycji. Formuły z funkcją WYBIERZ dają wyniki niemal pełnowartościowe (TPA — wyjaśnienie tego skrótu można znaleźć w rozdziale 4., w punkcie „Interpretacja argumentów gru- powych przez funkcje JEŻELI i WYBIERZ”), gotowe do dalszego przetwarzania. Alternatywne sposoby rozwiązania tego problemu korzystają z funkcji INDEKS w sposób niestandar- dowy [B20]. Jeszcze inną metodę, z wykorzystaniem funkcji MACIERZ.ILOCZYN, pokazałem nieco da- lej, w przykładzie 6.5. Problem można rozwiązać również posługując się Visual Basiciem [C6]. Zastosowania odwołaniowej formy funkcji INDEKS Odwołaniowa forma funkcji INDEKS ma szczególne zastosowanie w przypadku zakresów o nie- regularnych kształtach, złożonych z kilku obszarów. Jeżeli zakres złożony stanowią pojedyncze komórki, można pominąć nr_wiersza i nr_kolumny i podać tylko nr_obszaru. W ten sposób można zebrać rozproszone dane w jednym miejscu. Poleć książkęKup książkę 190 Zrozumieć Excela. Funkcje i wyrażenia Wykorzystując fakt, że ta forma funkcji INDEKS daje w wyniku adres, można jej użyć do utwo- rzenia zakresu dynamicznego o zmiennym kształcie: adr_pocz:INDEKS(kom_kon;;;nr_obszaru) Zakres kom_kon powinien być zakresem nieregularnym i składać się z komórek wyznaczających prawe dolne narożniki konstruowanych obszarów. Początek zakresu jest w tym przykładzie stały, ale można wyobrazić sobie, że w tym miejscu zamiast adresu podanego wprost u
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Zrozumieć Excela. Funkcje i wyrażenia
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ą: