Zrozumieć Excela. Formuły dynamiczne i nowe funkcje w Microsoft 365 Rybnik

Poznaj całkiem nowego Excela W ciągu ostatnich kilku lat arkusz kalkulacyjny Excel intensywnie się rozwijał. Pojawiły się nowe funkcje i wbudowane narzędzia, pozwalające rozwiązywać typowe problemy w sposób bardziej zwięzły i efektywny. Zaszła między innymi rewolucyjna zmiana w podejściu do tablic i wykonywanych z ich udziałem obliczeń. Wyjściowo Excel był "komórkocentryczny" - główny nacisk kładziono w nim na użycie do obliczeń pojedynczych komórek traktowanych odrębnie i tworzenie na ich podstawie ciągów obliczeniowych. Aktualnie akcentuje się przede wszystkim przetwarzanie od razu całych tablic danych, obejmujących wiele komórek. Ta książka przybliża zagadnienia związane z użyciem formuł dynamicznych (inaczej: dynamicznych formuł tablicowych / formuł rozlewających się). Formuła dynamiczna zwraca tablicę wyników w sąsiednich komórkach, a nie tylko w komórce, w której została wprowadzona. Dzięki temu może się automatycznie dopasować do nowego zakresu podczas dodawania czy też usuwania danych w arkuszu. Podczas lektury między innymi: Poznasz nową koncepcję obliczeń tablicowych w Excelu, a także jej wpływ na zapis formuł i organizację obliczeń Zrozumiesz różnice między dawnymi formułami tablicowymi a nowymi formułami dynamicznymi Przyjrzysz się rozszerzonej koncepcji nazw definiowanych, które teraz mogą mieć zasięg zawężony do pojedynczej formuły (dzięki użyciu funkcji LET), ale mogą też być nadawane funkcjom definiowanym przez użytkownika (za pomocą metafunkcji LAMBDA) Dowiesz się, jak działają funkcje pozwalające wyszukiwać, filtrować, sortować i eliminować duplikaty, jak również funkcje do pracy z tekstem Zapoznasz się z nowym typem danych w Excelu, tj. z danymi rozszerzonymi, a także z zaawansowanymi funkcjami stanowiącymi zamiennik tabel przestawnych Przybliżysz sobie rozmaite nowinki i ułatwienia, które są obecne w najnowszej edycji Excela, a dzięki ćwiczeniom nauczysz się z nich korzystać Przypomnisz sobie funkcje służące do obliczeń finansowych, dostępne w pokaźnej liczbie w Excelu, a dotąd nieomawiane w książkach z cyklu "Zrozumieć Excela" Spis treści: Wstęp Rozdział 1. Nowe podejście do obliczeń z udziałem tablic Koncepcja formuł dynamicznych Formuła odwołująca się do grupy danych jest domyślnie formułą tablicową Cechy formuł dynamicznych Interpretacja formuł dynamicznych w starym stylu według zasady rzutu prostokątnego Działanie operatora @ Cechy operatora @ Użycie operatora zakresu rozlanego # Nowe rodzaje błędów i komunikaty o ich występowaniu Zasady zachowania zgodności między starszymi a nowymi wersjami Excela Jak pogodzić formuły dynamiczne z tabelami Excela? Metoda tablicy zsynchronizowanej Metoda wyboru pojedynczych wartości Metoda zwiniętego tekstu Tabela utworzona na podstawie fragmentu tablicy dynamicznej Ukrycie pustych wierszy za pomocą filtra z fragmentatorem Użycie formuł dynamicznych z innymi funkcjonalnościami Excela Menedżer nazw Wykresy Obrazy połączone Formatowanie warunkowe Walidacja danych Tabele danych Tabele przestawne Obsługa formuł dynamicznych w VBA Nowe wersje właściwości Range.Formula Właściwości identyfikujące formuły i zakresy dynamiczne Funkcja UDF odpowiadająca właściwości Range.CurrentRegion Rozdział 2. Rozszerzona interpretacja pojęcia nazwy Zasięg nazwy ograniczony do jednej funkcji - funkcja LET Podstawy użycia nazw definiowanych w Excelu Udostępnienie funkcji LET Ponowne użycie tych samych nazw Zalety i wady funkcji LET Kluczowe korzyści ze stosowania funkcji LET Definiowanie funkcji obliczeniowych przez użytkownika z możliwością nadawania im własnych nazw - funkcja LAMBDA Geneza Funkcja LAMBDA - składnia i podstawowe wymogi Tworzenie funkcji LAMBDA Konwencje nazewnictwa funkcji LAMBDA Konwencje stosowane do nazw parametrów Komentarze w nazwanych funkcjach Współdziałanie funkcji LET i LAMBDA Kopiowanie LAMBDY do innych skoroszytów Uwaga na adresy kwalifikowane Użycie innej funkcji jako argumentu funkcji LAMBDA Zasady ogólne Używanie nazw funkcji standardowych jako LAMBD - eta lambdy Rozdział 3. Zaawansowane techniki użycia funkcji LAMBDA Zależne użycie funkcji LAMBDA (w tej samej formule) Funkcje LAMBDA z różnymi argumentami Skrytka ("thunk") do tymczasowego przechowywania danych Zastosowanie funkcji LAMBDA do funkcji makr XLM Użycie parametrów opcjonalnych Deklaracja parametrów opcjonalnych Funkcja pomocnicza ISOMITTED Iteracyjne i rekurencyjne wywołanie funkcji LAMBDA Geneza Obliczanie wyrazów ciągu Fibonacciego Rozwiązywanie równań nieliniowych metodą Newtona Zamiana wielu znaków Rozdział 4. Funkcje pomocnicze funkcji LAMBDA Koncepcja funkcji pomocniczych Ogólne uwagi o składni Schematy wywołania Funkcja MAP Uwagi dotyczące użytkowania Kiedy używać MAP Funkcja SCAN Uwagi dotyczące użytkowania Funkcja REDUCE Uwagi dotyczące użytkowania Funkcja MAKEARRAY Uwagi dotyczące użytkowania Funkcje BYCOL i WIERSZAMI Uwagi dotyczące użytkowania Porównanie funkcji ze względu na strukturę danych i wyników Wykorzystanie zasobów sieciowych Rozdział 5. Profesjonalny edytor formuł Jak uzyskać dostęp do nowego edytora formuł? Praca w zaawansowanym środowisku formuł (AFE) Koncepcja działania programu Grid (siatka arkusza) Names (nazwy zdefiniowane) Modules (moduły) Przyszłość zaawansowanego edytora formuł Rozdział 6. Funkcje ułatwiające typowe operacje tablicowe Funkcja FILTRUJ w zestawieniu z klasycznymi filtrami Funkcje do sortowania SORTUJ i SORTUJ.WEDŁUG Funkcje do wyszukiwania X.WYSZUKAJ i X.DOPASUJ Wyszukiwanie ostatniego wpisu w kolumnie lub wierszu Funkcja UNIKATOWE pomijająca duplikaty Funkcja SEKWENCJA do numerowania komórek Funkcja LOSOWA.TABLICA Rozdział 7. Nowe funkcje do pracy z tekstem Funkcje ZŁĄCZ.TEKST i POŁĄCZ.TEKSTY Funkcja ZŁĄCZ.TEKST Funkcja POŁĄCZ.TEKSTY Funkcje PODZIEL.TEKST, TEKST.PRZED i TEKST.PO Funkcja PODZIEL.TEKST Funkcje TEKST.PRZED i TEKST.PO Funkcje WARTOŚĆ.NA.TEKST i TABLICA.NA.TEKST Porównanie funkcji TABLICA.NA.TEKST i POŁĄCZ.TEKSTY Funkcje TŁUMACZ i WYKRYJJĘZYK Jakie błędy mogą wystąpić? Zapis liczby słownie Rozdział 8. Funkcje ułatwiające przekształcanie tablic Funkcje STOS.POZ i STOS.PION Funkcje WYBIERZ.KOLUMNY i WYBIERZ.WIERSZE Funkcje DO.KOLUMNY i DO.WIERSZA Funkcje ZAWIŃ.KOLUMNY i ZAWIŃ.WIERSZE Funkcja POMIŃ Funkcja WYCINEK Funkcja ROZSZERZ Użycie odwołań 3-W jako argumentów nowych funkcji tablicowych Geneza Przegląd nowych funkcji, które obsługują odwołania 3-W Użycie znaków zastępczych do tworzenia odwołań 3-W Rozdział 9. Funkcja OBRAZ - wstawianie grafiki bezpośrednio do komórek Wstawianie obrazu do komórki w programie Excel Wstawianie obrazu z wykorzystaniem wstążki Przełączanie pomiędzy obrazem w komórce a obiektem obrazu Dodawanie tekstu alternatywnego do obrazów Wklejanie obrazu ze schowka do komórki Funkcja OBRAZ Użycie funkcji OBRAZ z adresami URL SharePoint Użycie obrazów w formułach programu Excel Pobieranie grafiki z sieci Tworzenie zestawień zawierających obrazy Użycie obrazu jako kryterium wyszukiwania Wyszukiwanie grafiki w Google za pomocą Excela Obsługa obrazów z poziomu VBA Właściwości komórki zawierającej obraz Umieszczenie obrazu w komórce i konwersja na kształt Rozdział 10. Funkcje GRUPUJ.WEDŁUG i PRZESTAWIAJ.WEDŁUG zamiast tabel przestawnych? Geneza Funkcja GRUPUJ.WEDŁUG Sposób działania funkcji obliczeniowych Funkcja PRZESTAWIAJ.WEDŁUG Użycie funkcji GRUPUJ.WEDŁUG i PRZESTAWIAJ.WEDŁUG z fragmentatorami Zastosowanie formatowania warunkowego do wyników funkcji Jak zastosować formatowanie warunkowe do wyników funkcji GRUPUJ.WEDŁUG i PRZESTAWIAJ.WEDŁUG? Formatowanie wyników funkcji GRUPUJ.WEDŁUG Formatowanie wyników funkcji PRZESTAWIAJ.WEDŁUG Porównanie funkcji GRUPUJ i PRZESTAWIAJ z tabelami przestawnymi Rozdział 11. Funkcja PRZYTNIJZAKRES i skróty przycinania Funkcja PRZYTNIJZAKRES Do czego służy funkcja PRZYTNIJZAKRES? Porównanie funkcji PRZYTNIJZAKRES z funkcjami DO.KOLUMNY i DO.WIERSZA Użycie funkcji PRZYTNIJZAKRES do definicji listy sprawdzania poprawności danych Użycie funkcji PRZYTNIJZAKRES w formatowaniu warunkowym Użycie funkcji PRZYTNIJZAKRES z wykresami Nowy operator kropkowy w skrótach przycinania Warianty użycia operatora kropkowego Podsumowanie Rozdział 12. Funkcje do obsługi wyrażeń regularnych Co to są wyrażenia regularne? Droga do wyrażeń regularnych w Excelu Symbole wieloznaczne a wyrażenia regularne Konstrukcja wyrażeń regularnych Tabela metaznaków (tokenów) wyrażeń regularnych Interpretacja wyrażeń regularnych Funkcje Excela do obsługi wyrażeń regularnych Funkcja REGEXTEST Funkcja REGEXWYODRĘBNIJ Funkcja REGEXZASTĄP Podsumowanie Nowe tryby regex dla funkcji X.WYSZUKAJ i X.DOPASUJ Rozdział 13. Kontrola sposobu pracy z arkuszem Nowe kontrolki pola wyboru Wstawianie pól wyboru do arkusza Formatowanie pola wyboru Usuwanie pola wyboru Przełączanie pola wyboru Używanie odwołań Obsługa pól wyboru z poziomu VBA Obiekt CellControl Usuwanie pól wyboru Możliwość uniknięcia niepożądanej konwersji formatu liczb Nowy wygląd formuł na pasku formuły Wyraźne zaznaczenie komórki aktywnej Przekreślenie nieaktualnych wartości Źródło problemu Opis zastosowanego rozwiązania Jak w praktyce działa przekreślanie nieaktualnych wartości? Przekreślanie nieaktualnych wartości a metoda Range.Dirty w VBA Okienko nawigacji Geneza Pokazywanie okienka nawigacji Wprowadzanie zmian Wyszukiwanie/filtrowanie Konfiguracja okienka nawigacji Etykietki podglądu wyników obliczeń Geneza Sprawdzanie formuły Włączanie i wyłączanie etykietek podglądu Korzyści i pułapki związane z używaniem etykietek podglądu wartości Modyfikacja działania list kontroli poprawności Interpretacja źródła listy Wyświetlanie listy Usprawnienie wyboru z listy Ciemny tryb wyświetlania na ekranie Wersje zgodności Zmiana domyślnej wersji zgodności w skoroszycie Ulepszenia funkcji tekstowych wprowadzone w wersji zgodności nr 2 Rozdział 14. Nowy typ danych - dane połączone Połączone typy danych: Giełdowe i Geograficzne Bieżące kursy akcji i innych papierów wartościowych Bieżące kursy walut Dane geograficzne Formuły i funkcje odwołujące się do danych połączonych Bezpośredni odczyt pola danych połączonych Funkcja POLE.WARTOŚĆ Czy w komórce są dane połączone? Formatowanie warunkowe Listy rozwijane Sortowanie i filtrowanie Funkcja HISTORIA.AKCJI Definiowanie własnych typów danych przez użytkownika Tworzenie typu danych za pomocą edytora dodatku Power Query Tworzenie zagnieżdżonych typów danych Połączone typy danych z punktu widzenia VBA Sprawdzenie, czy zakres zawiera dane połączone Przekształcenie zakresu w dane połączone Przekształcenie danych połączonych w wartości Kopiowanie typu danych z komórki do komórki bez formatowania Wyświetlanie karty danych połączonych Przełączanie widoczności ikon typu danych Sortowanie zakresu zawierającego dane połączone Filtrowanie zakresu zawierającego dane połączone Rozdział 15. Excel w przeglądarce Rozwój arkusza kalkulacyjnego w wersji online Cechy Excela w wersji sieciowej Dostępność funkcji w internetowej wersji Excela Definiowanie i użycie nazw Rozdział 16. Funkcje do obliczeń finansowych Ogólna charakterystyka funkcji finansowych Obliczenia związane ze zmianą wartości pieniądza w czasie Obecna i przyszła nominalna kwota pieniędzy (PV i FV) Sposób naliczania odsetek - metoda liniowa Kapitalizacja odsetek PV i FV jako funkcje czasu Funkcje PV i FV dostępne w Excelu Funkcja NPV Funkcja XNPV Funkcja WART.PRZYSZŁ.KAP Inne funkcje powiązane z PV i FV Funkcje do przeliczania stóp lub wskaźników procentowych Wewnętrzna stopa zwrotu, równe okresy - funkcja IRR Wewnętrzna stopa zwrotu, dowolne daty - funkcja XIRR Zmodyfikowana wewnętrzna stopa zwrotu - funkcja MIRR Zmiany stóp procentowych według harmonogramu Podsumowanie Obliczanie amortyzacji środków trwałych Funkcje wspomagające obliczenia amortyzacji Amortyzacja liniowa - funkcja AMORT.LIN Amortyzacja liniowa - funkcja SLN Amortyzacja nieliniowa - funkcja SYD Amortyzacja degresywna - funkcja DB Amortyzacja degresywna - funkcja DDB Amortyzacja degresywna - funkcja VDB Obliczenia wspomagające obsługę papierów wartościowych Stopa procentowa, stopa dyskontowa i rentowność Typowe argumenty funkcji do obliczeń cen lub rentowności Obliczenie ceny przy założonej rentowności Obliczenie kwoty wykupu lokaty zdyskontowanej Obliczenie kwoty odsetek Obliczenie rentowności Obliczenie wskaźników wrażliwości Obliczenia stóp procentowych Obliczenia związane z datami wypłaty odsetek Problem z funkcjami o dużej liczbie argumentów Rozdział 17. Zestawienie omówionych funkcji Literatura cytowana Skorowidz O autorze: Dr inż. Maciej Gonet jest emerytowanym pracownikiem naukowo-dydaktycznym Politechniki Śląskiej w Gliwicach. Jego główną specjalnością naukową jest technologia chemiczna, a zwłaszcza elektrochemia, lecz przez kilkanaście ostatnich lat równolegle interesował się metodami obliczeniowymi stosowanymi do opracowania danych doświadczalnych, w tym szczególnie z użyciem programu Excel. Jest autorem czterech książek dotyczących Excela, jak również autorem lub współautorem kilkudziesięciu innych publikacji naukowych i popularyzatorskich.