Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00204 004830 14461019 na godz. na dobę w sumie
Excel. Najlepsze sztuczki i chwyty - książka
Excel. Najlepsze sztuczki i chwyty - książka
Autor: Liczba stron: 400
Wydawca: Helion Język publikacji: polski
ISBN: 83-246-0321-2 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Excel naprawdę może pracować wydajniej!

Arkusz kalkulacyjny Excel to jeden z najpopularniejszych programów na świecie. Jest codziennie używany przez miliony ludzi, jednak większość z nich nie zna nawet połowy jego niesamowitych możliwości. Jeśli zajrzymy 'pod maskę', poznamy te cechy aplikacji, dzięki którym możemy pracować szybciej, wygodniej i efektywniej. Samodzielne odkrywanie możliwości programu to ciekawe zajęcie, jednak pochłania mnóstwo czasu. Poradnik, który je kompleksowo prezentuje, stanowi więc nieocenioną pomoc.

Książka 'Excel. Najlepsze sztuczki i chwyty' zawiera ponad 200 wskazówek, dzięki którym nauczysz się optymalizować rutynowe procedury, budować dynamiczne wykresy i przetwarzać dane z wykorzystaniem formuł. Dowiesz się, jak rozwiązywać najczęstsze problemy związane z konfiguracją aplikacji, tworzyć własne dodatki, ukrywać przyciski pól na wykresach przestawnych, kontrolować automatyczne funkcje oraz rejestrować i uruchamiać makra w skoroszytach.

Zostań mistrzem Excela.

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

Darmowy fragment publikacji:

IDZ DO IDZ DO PRZYK£ADOWY ROZDZIA£ PRZYK£ADOWY ROZDZIA£ SPIS TREŒCI SPIS TREŒCI KATALOG KSI¥¯EK KATALOG KSI¥¯EK KATALOG ONLINE KATALOG ONLINE ZAMÓW DRUKOWANY KATALOG ZAMÓW DRUKOWANY KATALOG TWÓJ KOSZYK TWÓJ KOSZYK DODAJ DO KOSZYKA DODAJ DO KOSZYKA CENNIK I INFORMACJE CENNIK I INFORMACJE ZAMÓW INFORMACJE ZAMÓW INFORMACJE O NOWOŒCIACH O NOWOŒCIACH ZAMÓW CENNIK ZAMÓW CENNIK CZYTELNIA CZYTELNIA FRAGMENTY KSI¥¯EK ONLINE FRAGMENTY KSI¥¯EK ONLINE Wydawnictwo Helion ul. Chopina 6 44-100 Gliwice tel. (32)230-98-63 e-mail: helion@helion.pl Excel. Najlepsze sztuczki i chwyty Autor: John Walkenbach T³umaczenie: £ukasz Suma ISBN: 83-246-0321-2 Tytu³ orygina³u: John Walkenbachs Favorite Excel Tips Tricks Format: B5, stron: 400 Excel naprawdê mo¿e pracowaæ wydajniej! (cid:127) Przyspiesz proces wprowadzania danych (cid:129) Wykorzystaj formu³y i funkcje (cid:129) Zautomatyzuj pracê za pomoc¹ makr Arkusz kalkulacyjny Excel to jeden z najpopularniejszych programów na œwiecie. Jest codziennie u¿ywany przez miliony ludzi, jednak wiêkszoœæ z nich nie zna nawet po³owy jego niesamowitych mo¿liwoœci. Jeœli zajrzymy „pod maskê”, poznamy te cechy aplikacji, dziêki którym mo¿emy pracowaæ szybciej, wygodniej i efektywniej. Samodzielne odkrywanie mo¿liwoœci programu to ciekawe zajêcie, jednak poch³ania mnóstwo czasu. Poradnik, który je kompleksowo prezentuje, stanowi wiêc nieocenion¹ pomoc. Ksi¹¿ka „Excel. Najlepsze sztuczki i chwyty” zawiera ponad 200 wskazówek, dziêki którym nauczysz siê optymalizowaæ rutynowe procedury, budowaæ dynamiczne wykresy i przetwarzaæ dane z wykorzystaniem formu³. Dowiesz siê, jak rozwi¹zywaæ najczêstsze problemy zwi¹zane z konfiguracj¹ aplikacji, tworzyæ w³asne dodatki, ukrywaæ przyciski pól na wykresach przestawnych, kontrolowaæ automatyczne funkcje oraz rejestrowaæ i uruchamiaæ makra w skoroszytach. (cid:129) Korzystanie ze skrótów klawiaturowych (cid:129) Zaznaczanie komórek (cid:129) Konfigurowanie interfejsu u¿ytkownika (cid:129) Formatowanie danych i arkuszy (cid:129) Stosowanie formu³ (cid:129) Tworzenie wykresów (cid:129) Drukowanie arkuszy (cid:129) Rejestrowanie i stosowanie makr (cid:129) Korzystanie z VBA Zostañ mistrzem Excela O autorze ......................................................................................... 9 Wstęp ............................................................................................ 11 Rozdział 1. Podstawy korzystania z programu Excel .......................................... 19 Wersje programu Excel ....................................................................................................21 Zwiększanie wydajności korzystania z menu programu ..................................................23 Wydajne zaznaczanie komórek ........................................................................................25 „Specjalne” zaznaczanie zakresów ..................................................................................29 Cofanie, ponowne wykonywanie i powtarzanie operacji .................................................31 Zmiana liczby poziomów cofania operacji ......................................................................32 Kilka przydatnych skrótów klawiaturowych ....................................................................34 Przemieszczanie się pomiędzy arkuszami w ramach skoroszytu .....................................35 Zerowanie znacznika używanego obszaru arkusza kalkulacyjnego ................................36 Różnica między skoroszytami i oknami ...........................................................................37 Unikanie używania okienka zadań podczas korzystania z systemu pomocy programu Excel 2003 .....................................................................................................38 Dostosowywanie domyślnego skoroszytu .......................................................................40 Zmiana wyglądu zakładki arkusza ...................................................................................42 Ukrywanie elementów interfejsu użytkownika ................................................................43 Ukrywanie kolumn i wierszy ...........................................................................................46 Ukrywanie zawartości komórek .......................................................................................46 Przeprowadzanie niedokładnych wyszukiwań .................................................................47 Zmiana formatowania ......................................................................................................49 Zwiększanie liczby wierszy i kolumn ..............................................................................51 Ograniczanie użytecznej powierzchni arkusza kalkulacyjnego .......................................53 Używanie rozwiązań alternatywnych dla komentarzy do komórek .................................56 Zmiana rozmiaru tekstu w oknie systemu pomocy programu Excel ...............................57 Skuteczne ukrywanie arkusza kalkulacyjnego .................................................................57 Rozwiązywanie typowych problemów z konfiguracją programu ....................................59 Rozdział 2. Wprowadzanie danych ..................................................................... 65 Wprowadzenie do typów danych .....................................................................................67 Przemieszczanie wskaźnika aktywnej komórki po wprowadzeniu danych .....................71 Zaznaczanie zakresu komórek wejściowych przed wprowadzaniem danych ..................72 Korzystanie z opcji Autouzupełnianie do automatyzacji wprowadzania danych ............73 Zapewnianie wyświetlania nagłówków dzięki możliwości blokowania okienek ............74 Automatyczne wypełnianie zakresu komórek arkusza z wykorzystaniem serii ..............75 Praca z ułamkami .............................................................................................................77 D:drukExcel Najlepsze sztuczki i chwyty9_druk!spis tresci.doc 3 4 Excel. Najlepsze sztuczki i chwyty Odczytywanie danych za pomocą narzędzia „Tekst na mowę” .......................................79 Kontrolowanie automatycznych hiperłączy .....................................................................80 Wprowadzanie numerów kart kredytowych ....................................................................83 Używanie formularza wprowadzania danych oferowanego przez program Excel ..........83 Dostosowywanie i udostępnianie wpisów Autokorekty ..................................................85 Ograniczanie możliwości przemieszczania kursora jedynie do komórek wprowadzania danych ...................................................................................................86 Kontrolowanie Schowka pakietu Office ..........................................................................88 Tworzenie listy rozwijanej w komórce arkusza ...............................................................89 Rozdział 3. Formatowanie ................................................................................. 93 Szybkie formatowanie liczb .............................................................................................95 Używanie odłączanych pasków narzędzi .........................................................................95 Tworzenie niestandardowych formatów liczbowych .......................................................97 Używanie niestandardowych formatowań liczb do skalowania wartości ......................100 Używanie niestandardowych formatowań wartości daty i czasu ...................................102 Kilka przydatnych niestandardowych formatowań liczbowych ....................................103 Wyświetlanie tekstów i wartości liczbowych w jednej komórce ...................................107 Scalanie komórek ...........................................................................................................109 Formatowanie poszczególnych znaków w komórce arkusza .........................................110 Wyświetlanie wartości czasu większych niż 24 godziny ...............................................111 Przywracanie liczbom wartości numerycznych .............................................................112 Używanie funkcji Autoformatowanie ............................................................................113 Posługiwanie się liniami siatki, obramowaniami oraz podkreśleniami .........................115 Tworzenie formatowań wykorzystujących efekty trójwymiarowe ................................117 Zawijanie tekstu w komórce ..........................................................................................118 Przeglądanie wszystkich dostępnych znaków czcionki .................................................119 Wprowadzanie znaków specjalnych ..............................................................................121 Używanie stylów nazwanych .........................................................................................122 Sposób obsługi kolorów przez program Excel ...............................................................125 Stosowanie naprzemiennego wypełniania wierszy arkusza ...........................................127 Używanie obrazu graficznego w charakterze tła arkusza kalkulacyjnego .....................130 Rozdział 4. Podstawowe formuły i funkcje ....................................................... 131 Kiedy używać odwołań bezwzględnych ........................................................................133 Kiedy używać odwołań mieszanych ..............................................................................134 Zmiana typu odwołań do komórek .................................................................................135 Sztuczki z poleceniem Autosumowanie .........................................................................136 Używanie statystycznych możliwości paska stanu ........................................................138 Konwertowanie formuł na wartości ...............................................................................139 Przetwarzanie danych bez korzystania z formuł ............................................................139 Przetwarzanie danych za pomocą formuł .......................................................................140 Usuwanie wartości przy zachowaniu formuł .................................................................142 Używanie argumentów funkcji ......................................................................................143 Opisywanie formuł bez konieczności używania komentarzy ........................................144 Tworzenie dokładnej kopii zakresu komórek przechowujących formuły .....................145 Kontrolowanie komórek z formułami z dowolnego miejsca arkusza kalkulacyjnego ...146 Wyświetlanie i drukowanie formuł ................................................................................147 Unikanie wyświetlania błędów w formułach .................................................................148 Używanie narzędzia Szukaj wyniku ..............................................................................150 Sekret związany z nazwami ...........................................................................................152 Używanie nazwanych stałych ........................................................................................153 Używanie funkcji w nazwach ........................................................................................154 4 D:drukExcel Najlepsze sztuczki i chwyty9_druk!spis tresci.doc Spis treści 5 Edytowanie odwołań nazw .............................................................................................156 Używanie dynamicznych nazw ......................................................................................156 Tworzenie nazw na poziomie arkusza ...........................................................................158 Obsługa dat sprzed roku 1900 ........................................................................................159 Przetwarzanie ujemnych wartości czasu ........................................................................161 Rozdział 5. Przydatne przykłady formuł ............................................................ 163 Wyznaczanie dat dni świątecznych ................................................................................165 Obliczanie średniej ważonej ...........................................................................................167 Obliczanie wieku osób ...................................................................................................168 Szeregowanie wartości za pomocą formuły tablicowej .................................................170 Zliczanie znaków w komórce .........................................................................................171 Wyrażanie liczb w postaci liczebników porządkowych w języku angielskim ..............172 Wyodrębnianie słów z tekstów ......................................................................................173 Rozdzielanie nazwisk .....................................................................................................174 Usuwanie tytułów z nazwisk ..........................................................................................176 Generowanie serii dat .....................................................................................................176 Określanie specyficznych dat .........................................................................................178 Wyświetlanie kalendarza w zakresie komórek arkusza .................................................181 Różne metody zaokrąglania liczb ..................................................................................182 Zaokrąglanie wartości czasu ..........................................................................................185 Pobieranie zawartości ostatniej niepustej komórki w kolumnie lub wierszu .................186 Używanie funkcji LICZ.JEŻELI ....................................................................................187 Zliczanie komórek spełniających wiele kryteriów jednocześnie ...................................189 Obliczanie liczby różnych wpisów w zakresie ..............................................................191 Obliczanie sum warunkowych wykorzystujących pojedynczy warunek .......................192 Obliczanie sum warunkowych wykorzystujących wiele warunków ..............................194 Wyszukiwanie wartości dokładnej .................................................................................196 Przeprowadzanie wyszukiwań dwuwymiarowych .........................................................198 Przeprowadzanie wyszukiwania w dwóch kolumnach ..................................................200 Przeprowadzanie wyszukiwania przy użyciu tablicy .....................................................201 Używanie funkcji ADR.POŚR .......................................................................................202 Tworzenie megaformuł ..................................................................................................204 Rozdział 6. Wykresy i elementy grafiki ............................................................ 207 Tworzenie wykresu tekstowego bezpośrednio w zakresie komórek .............................209 Komentowanie zawartości wykresu ...............................................................................211 Tworzenie samopowiększającego się wykresu ..............................................................212 Tworzenie kombinacji wykresów ..................................................................................214 Obsługa brakujących danych na wykresie liniowym .....................................................216 Tworzenie wykresów Gantta ..........................................................................................218 Tworzenie wykresów przypominających termometr .....................................................219 Tworzenie wykresów wykorzystujących elementy graficzne ........................................222 Wykreślanie matematycznych funkcji jednej zmiennej .................................................224 Wykreślanie matematycznych funkcji dwóch zmiennych .............................................225 Tworzenie półprzezroczystych serii danych na wykresie ..............................................227 Zapisywanie wykresu w postaci pliku graficznego ........................................................228 Ustalanie identycznych rozmiarów wykresów ...............................................................230 Wyświetlanie wielu wykresów w jednym arkuszu wykresu ..........................................232 „Zamrażanie” wykresu ...................................................................................................232 Dodawanie „znaku wodnego” do arkusza ......................................................................235 Zmiana kształtu pola komentarza do komórki ...............................................................236 Wstawianie grafiki w pole komentarza do komórki ......................................................237 D:drukExcel Najlepsze sztuczki i chwyty9_druk!spis tresci.doc 5 6 Excel. Najlepsze sztuczki i chwyty Rozdział 7. Analiza danych i listy .................................................................... 239 Korzystanie z możliwości związanych z listami w Excelu 2003 ...................................241 Sortowanie w porządku określonym dla więcej niż trzech kolumn ...............................243 Używanie widoków niestandardowych wraz z możliwościami automatycznego filtrowania .........................................................................................244 Umieszczanie wyników działania zaawansowanego filtra w różnych arkuszach kalkulacyjnych ..........................................................................246 Porównywanie dwóch zakresów za pomocą formatowania warunkowego ...................247 Układanie rekordów listy w przypadkowej kolejności ..................................................249 Wypełnianie pustych miejsc w raporcie .........................................................................251 Tworzenie listy z tabeli podsumowania .........................................................................253 Odnajdowanie powtórzeń przy użyciu formatowania warunkowego ............................255 Uniemożliwianie wstawiania wierszy lub kolumn w ramach zakresu ...........................257 Szybkie tworzenie tabeli liczby wystąpień ....................................................................259 Kontrolowanie odwołań do komórek w tabeli przestawnej ...........................................261 Grupowanie elementów w tabeli przestawnej według dat .............................................262 Ukrywanie przycisków pól na wykresie przestawnym ..................................................264 Rozdział 8. Praca z plikami ............................................................................. 267 Importowanie pliku tekstowego do zakresu komórek arkusza ......................................269 Pobieranie danych ze strony WWW ..............................................................................270 Wyświetlanie pełnej ścieżki dostępu do skoroszytu ......................................................274 Zapisywanie podglądu skoroszytu .................................................................................275 Korzystanie z właściwości dokumentu ..........................................................................276 Sprawdzanie informacji o użytkowniku, który otworzył plik jako ostatni ....................278 Odszukiwanie brakującego przycisku „Nie na wszystkie” podczas zamykania plików 280 Pobieranie listy nazw plików .........................................................................................281 Znaczenie haseł programu Excel ....................................................................................283 Używanie plików obszaru roboczego ............................................................................283 Zmniejszanie rozmiaru skoroszytu .................................................................................284 Rozdział 9. Drukowanie .................................................................................. 285 Wybieranie elementów do wydrukowania .....................................................................287 Umieszczanie powtarzających się wierszy lub kolumn na wydruku .............................288 Drukowanie nieciągłych zakresów komórek na jednej stronie ......................................289 Uniemożliwianie drukowania obiektów .........................................................................291 Sztuczki związane z numerowaniem stron .....................................................................292 Podgląd podziału stron ...................................................................................................294 Dodawanie i usuwanie znaków podziału stron ..............................................................296 Drukowanie danych do pliku PDF .................................................................................297 Unikanie drukowania określonych wierszy ...................................................................297 Drukowanie arkusza na jednej stronie ...........................................................................300 Drukowanie formuł ........................................................................................................301 Kopiowanie ustawień strony pomiędzy arkuszami ........................................................303 Używanie widoków niestandardowych przy drukowaniu .............................................304 Rozdział 10. Dostosowywanie menu i pasków narzędzi ...................................... 307 Odnajdowanie wielofunkcyjnych przycisków pasków narzędzi ...................................309 Odszukiwanie ukrytych poleceń menu ..........................................................................310 Dostosowywanie menu i pasków narzędzi .....................................................................310 Tworzenie niestandardowego paska narzędzi ................................................................312 Wyłączanie automatycznego wyświetlania pasków narzędzi ........................................314 Dołączanie pasków narzędzi do arkuszy kalkulacyjnych ..............................................315 Tworzenie kopii zapasowych samodzielnie dostosowanych menu i pasków narzędzi .316 6 D:drukExcel Najlepsze sztuczki i chwyty9_druk!spis tresci.doc Spis treści 7 Rozdział 11. Znajdowanie, naprawianie i unikanie błędów ................................. 317 Korzystanie z możliwości sprawdzania błędów w Excelu .............................................319 Znajdowanie komórek formuł ........................................................................................321 Metody radzenia sobie z problemami związanymi z liczbami zmiennoprzecinkowymi .............................................................................323 Tworzenie tabeli nazw komórek i zakresów ..................................................................324 Graficzne przeglądanie nazw .........................................................................................325 Odszukiwanie „ślepych” łączy .......................................................................................325 Różnica między wartościami wyświetlanymi a rzeczywistymi .....................................326 Śledzenie powiązań występujących pomiędzy komórkami ...........................................327 Rozdział 12. Podstawy języka VBA i korzystanie z makr .................................... 331 Podstawowe informacje o makrach i języku VBA ........................................................333 Rejestrowanie makra ......................................................................................................334 Zagadnienia bezpieczeństwa związane z makrami ........................................................337 Korzystanie ze skoroszytu makr osobistych ..................................................................339 Różnice między funkcjami a procedurami .....................................................................340 Wyświetlanie okien komunikatów .................................................................................342 Pobieranie informacji od użytkownika ..........................................................................345 Uruchamianie makra przy otwieraniu skoroszytu ..........................................................346 Tworzenie prostych funkcji arkusza kalkulacyjnego .....................................................349 Sprawianie, by Excel przemówił ....................................................................................351 Ograniczenia funkcji niestandardowych ........................................................................352 Wywoływanie poleceń menu za pomocą makra ............................................................353 Zapisywanie funkcji niestandardowych w postaci dodatku do programu .....................354 Wyświetlanie połączonej kontrolki kalendarza ..............................................................355 Używanie dodatków do programu Excel .......................................................................357 Rozdział 13. Konwersje i obliczenia matematyczne ........................................... 361 Przeliczanie wartości między różnymi systemami jednostek ........................................363 Konwersja temperatur ....................................................................................................366 Wyznaczanie parametrów trójkątów prostokątnych ......................................................367 Obliczanie pól powierzchni, obwodów oraz pojemności ...............................................369 Rozwiązywanie liniowych układów równań ..................................................................372 Generowanie unikalnych całkowitych liczb losowych ..................................................373 Generowanie liczb losowych .........................................................................................375 Obliczanie pierwiastków i reszt z dzielenia ...................................................................376 Obliczanie średniej warunkowej ....................................................................................377 Rozdział 14. Źródła informacji na temat programu Excel ................................... 379 Używanie systemu pomocy programu Excel .................................................................381 Wyszukiwanie pomocy w internecie ..............................................................................382 Korzystanie z grup dyskusyjnych dotyczących Excela ..................................................382 Ciekawe strony WWW na temat Excela ........................................................................383 Skorowidz ..................................................................................... 387 D:drukExcel Najlepsze sztuczki i chwyty9_druk!spis tresci.doc 7 Rozdział 5. W rozdziale tym znajdziesz wiele przykładów formuł. Niektóre z nich będziesz mógł wykorzystać dokładnie w takiej formie, w jakiej zostały przedstawione. Inne zaś będziesz musiał dostosować do swoich wła- snych potrzeb. D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 163 164 Rozdział 5. ¨ Przydatne przykłady formuł Spis sposobów Sposób 85. Wyznaczanie dat dni świątecznych Sposób 86. Obliczanie średniej ważonej Sposób 87. Obliczanie wieku osób Sposób 88. Szeregowanie wartości za pomocą formuły tablicowej Sposób 89. Zliczanie znaków w komórce Sposób 90. Wyrażanie liczb w postaci liczebników porządkowych w języku angielskim Sposób 91. Wyodrębnianie słów z tekstów Sposób 92. Rozdzielanie nazwisk Sposób 93. Usuwanie tytułów z nazwisk Sposób 94. Generowanie serii dat Sposób 95. Określanie specyficznych dat Sposób 96. Wyświetlanie kalendarza w zakresie komórek arkusza Sposób 97. Różne metody zaokrąglania liczb Sposób 98. Zaokrąglanie wartości czasu Sposób 99. Pobieranie zawartości ostatniej niepustej komórki w kolumnie lub wierszu Sposób 100. Używanie funkcji LICZ.JEŻELI Sposób 101. Zliczanie komórek spełniających wiele kryteriów jednocześnie Sposób 102. Obliczanie liczby różnych wpisów w zakresie Sposób 103. Obliczanie sum warunkowych wykorzystujących pojedynczy warunek Sposób 104. Obliczanie sum warunkowych wykorzystujących wiele warunków Sposób 105. Wyszukiwanie wartości dokładnej Sposób 106. Przeprowadzanie wyszukiwań dwuwymiarowych Sposób 107. Przeprowadzanie wyszukiwania w dwóch kolumnach Sposób 108. Przeprowadzanie wyszukiwania przy użyciu tablicy Sposób 109. Używanie funkcji ADR.POŚR Sposób 110. Tworzenie megaformuł 165 167 168 170 171 172 173 174 176 176 178 181 182 185 186 187 189 191 192 194 196 198 200 201 202 204 164 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 85. Wyznaczanie dat dni świątecznych 165 Sposób 85. Wyznaczanie dat dni świątecznych Określenie dat niektórych świąt może być dość skomplikowane. Część z nich jest bar- dziej niż oczywista, ponieważ zawsze występują w tych samych dniach każdego roku, żeby wymienić tu chociażby Nowy Rok czy Święto Niepodległości. W przypadku te- go typu świąt powinieneś po prostu skorzystać z funkcji DDAD. Aby na przykład spraw- dzić, jakim dniem tygodnia będzie Nowy Rok (który zawsze przypada 1 stycznia) ro- ku określonego za pomocą danej znajdującej się w komórce A1, należy sformatować odpowiednio komórkę (na przykład dddd — zajrzyj do sposobu 42.) i skorzystać z na- stępującej formuły: =DATA(A1;1;1) Inne święta są zdefiniowane jako określone wystąpienie pewnego dnia tygodnia w kon- kretnym miesiącu lub są wręcz uzależnione od faz księżyca. Przykładem może tu być większość obchodzonych w Polsce świąt kościelnych, takich jak Boże Ciało czy Wiel- kanoc, bądź też niektóre z państwowych świąt amerykańskich, takich jak Dzień Pre- zydenta czy też Święto Dziękczynienia. Przy tworzeniu wszystkich wymienionych poniżej formuł założono, że wartość okre- ślająca rok znajduje się w komórce A1. Zwróć uwagę na fakt, że ponieważ Nowy Rok, Święto Wojska Polskiego, Święto Niepodległości czy Boże Narodzenie obchodzone są zawsze tego samego dnia roku, obliczenie ich dat sprowadza się do prostego wywo- łania funkcji DDAD. Nowy Rok Święto to zawsze przypada dnia 1 stycznia, więc odpowiednia dla niego formuła bę- dzie miała postać: =DATA(A1;1;1) Dzień Martina Luthera Kinga To amerykańskie święto wypada w trzeci poniedziałek stycznia. Przedstawiona poni- żej formuła oblicza datę święta Martina Luthera Kinga w roku określonym zawarto- ścią komórki A1: =DATA(A1;1;1)+JEŻELI(2 DZIEŃ.TYG(DATA(A1;1;1));7-DZIEŃ.TYG(DATA(A1;1;1)) +2;2-DZIEŃ.TYG(DATA(A1;1;1)))+((3-1)*7) Dzień Prezydenta Dzień ten w Stanach Zjednoczonych jest wyznaczony na trzeci poniedziałek lutego. Datę tę w roku zdefiniowanym w komórce A1 można obliczyć, korzystając z następu- jącej formuły: D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 165 166 Rozdział 5. ¨ Przydatne przykłady formuł =DATA(A1;2;1)+JEŻELI(2 DZIEŃ.TYG(DATA(A1;2;1));7-DZIEŃ.TYG(DATA(A1;2;1)) +2;2-DZIEŃ.TYG(DATA(A1;2;1)))+((3-1)*7) Wielkanoc Wyznaczenie daty Wielkanocy jest dość trudne z uwagi na sposób określenia dnia te- go święta. Jest to bowiem pierwsza niedziela po pierwszej pełni księżyca występują- cej po równonocy wiosennej, która przypada 21 marca. Przedstawioną tu formułę zna- lazłem w internecie i szczerze mówiąc, nie mam pojęcia, w jaki sposób działa: =ZAO=Z.O.D.W(DATA(A1;(;DZIEŃ(IIŃ(TA(A1A3A)A2+(/));7)-37 Pamiętaj tu o wybraniu dla komórki któregoś z formatów daty, gdyż w innym przy- padku zostanie w niej wyświetlona niewiele znacząca wartość numeryczna. Powyższa formuła zwraca poprawną datę Niedzieli Wielkanocnej dla lat z przedziału od roku 1900 do 2078. Myślę, że ten zakres okaże się wystarczający dla większości użytkowników programu. Jeśli w Twoim przypadku jest inaczej, będziesz mógł po- szukać odpowiedniego rozwiązania w sieci. Na stronach internetowych znaleźć moż- na liczne kody makr VBA pozwalających na wyznaczenie daty Wielkanocy na wiele różnych sposobów. Święto Konstytucji 3 Maja Działanie jest tu proste, gdyż — jak sama nazwa wskazuje — święto to przypada zaw- sze dnia 3 maja: =DATA(A1;(;3) Dzień Pamięci W ostatni poniedziałek maja Amerykanie obchodzą Dzień Pamięci. Formuła pozwa- lająca obliczyć datę tego dnia w roku podanym w komórce A1 ma postać: =DATA(A1;/;1)+JEŻELI(2 DZIEŃ.TYG(DATA(A1;/;1));7-DZIEŃ.TYG(DATA(A1;/;1)) +2;2-DZIEŃ.TYG(DATA(A1;/;1)))+((1-1)*7)-7 Zwróć uwagę, że powyższa formuła oblicza tak naprawdę datę pierwszego poniedziałku czerwca określonego roku, a następnie odejmuje liczbę 7 w celu wyznaczenia ponie- działku o tydzień wcześniejszego, czyli ostatniego w maju. Święto Pracy Święto Pracy obchodzone jest w Stanach Zjednoczonych zupełnie innego dnia niż w Europie, gdyż wypada ono w pierwszy poniedziałek września. Formuła wyznacza- jąca tę datę dla roku określonego w komórce A1 ma następującą postać: 166 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 86. Obliczanie średniej ważonej 167 =DATA(A1;9;1)+JEŻELI(2 DZIEŃ.TYG(DATA(A1;9;1));7-DZIEŃ.TYG(DATA(A1;9;1)) +2;2-DZIEŃ.TYG(DATA(A1;9;1)))+((1-1)*7) Oczywiście, żeby wyznaczyć dzień Święta Pracy w Polsce, zastosujesz raczej dużo prostszą formułę o postaci: =DATA(A1;(;1) Dzień Krzysztofa Kolumba To amerykańskie święto przypada na drugi poniedziałek października. Poniższa for- muła pozwala na wyznaczenie jego daty w roku określonym w komórce A1: =DATA(A1;10;1)+JEŻELI(2 DZIEŃ.TYG(DATA(A1;10;1));7-DZIEŃ.TYG(DATA(A1;10;1)) +2;2-DZIEŃ.TYG(DATA(A1;10;1)))+((2-1)*7) Święto Niepodległości Święto to ustalono na dzień 11 listopada: =DATA(A1;11;11) Święto Dziękczynienia Jedno z najważniejszych świąt w Stanach Zjednoczonych obchodzone jest w czwarty czwartek listopada. Datę Święta Dziękczynienia w roku podanym w komórce A1 moż- na obliczyć przy użyciu następującej formuły: =DATA(A1;11;1)+JEŻELI(( DZIEŃ.TYG(DATA(A1;11;1));7-DZIEŃ.TYG(DATA(A1;11;1)) +(;(-DZIEŃ.TYG(DATA(A1;11;1)))+((7-1)*7) Boże Narodzenie Jak wiadomo, święto to przypada na dzień 25 grudnia: =DATA(A1;12;2() Sposób 86. Obliczanie średniej ważonej Oferowana przez program Excel funkcja ŚREDNID zwraca średnią (czy też przeciętną) wartość liczb znajdujących się w określonym zakresie komórek. Bardzo często jednak zachodzi konieczność obliczenia średniej ważonej. Możesz stracić na poszukiwania cały D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 167 168 Rozdział 5. ¨ Przydatne przykłady formuł dzień, lecz mimo to nie znajdziesz funkcji Excela, która przeprowadzałaby podobne działanie. Masz jednak możliwość obliczenia średniej ważonej za pomocą odpowied- niej formuły używającej funkcji SUMD.ILOCZYNÓW oraz SUMD. Na rysunku 86.1 przedstawiono prosty przykład arkusza kalkulacyjnego zawierające- go ceny paliwa gazowego odnotowane w okresie 30 dni. Na przykład przez pierwsze cztery dni miesiąca litr gazu kosztował 2,48 zł, jego cena spadła następnie do pozio- mu 2,41 zł i utrzymała tę wartość przez dwa kolejne dni, by potem znów zmaleć na kolejne trzy dni do kwoty 2,39 zł i tak dalej. Rysunek 86.1. Formuła znajdująca się w komórce B16 oblicza średnią ważoną cen płynnego gazu W komórce B15 umieszczono formułę, która używa funkcji ŚREDNID: =ŚZEDŃIA(B7:B13) Ale, wbrew temu, co może się wydawać, formuła ta nie zwraca właściwego wyniku. Aby takowy otrzymać, poszczególnym cenom musiałyby być przypisane odpowiednie wagi związane z ilością dni, przez które obowiązywała każda z wartości. Innymi sło- wy, właściwym sposobem obliczania wartości średniej byłaby tu raczej średnia ważona. Średnią taką można obliczyć za pomocą poniższej formuły, która w arkuszu została umieszczona w komórce B16: =S(IA.ILOCZYŃ.O(B7:B13;C7:C13)AS(IA(C7:C13) Sposób 87. Obliczanie wieku osób Obliczanie wieku ludzkiego w programie Excel wymaga użycia pewnej sztuczki, po- nieważ wynik nie zależy wyłącznie od bieżącego roku, lecz również od aktualnego dnia, a sytuację komplikują dodatkowo lata przestępne. 168 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 87. Obliczanie wieku osób 169 Przedstawię tutaj trzy metody obliczania wieku osób. W wykorzystanych do tego celu formułach przyjąłem założenie, że data urodzenia znajduje się w komórce B1, zaś w ko- mórce B2 umieszczona jest aktualna data, tak jak zostało to pokazane na rysunku 87.1. Rysunek 87.1. Obliczanie wieku osób Metoda 1 Poniższa formuła odejmuje datę urodzenia od aktualnej daty i dzieli otrzymany wynik przez liczbę 365,25. Funkcja ZDOZR.DO.CD.Z usuwa część ułamkową rezultatu. =ZAO=Z.DO.CAW=((B2-B1)A3/(,2() Formuła ta nie jest dokładna w stu procentach, ponieważ przeprowadza dzielenie przez średnią liczbę dni w roku. W niektórych przypadkach zatem zwraca niepoprawne wy- niki. Przykładem może tu być obliczanie wieku dziecka, które ma dokładnie rok; w sy- tuacji takiej powyższa formuła zwróci wartość 0 zamiast 1. Metoda 2 Bardziej dokładną metodą obliczania wieku będzie zastosowanie funkcji YEDRFRDC, która jest dostępna w ramach dodatku Analysis ToolPak. =ZAO=Z.DO.CAW=(YEAZFZAC(B2;B1)) Metoda 3 Trzecia metoda wyznaczania wieku korzysta z funkcji DDAD.RÓŻNICD. W zależności od tego, której wersji Excela aktualnie używasz, może się zdarzyć, że funkcja ta nie bę- dzie udokumentowana w systemie pomocy programu. =DATA.Z.ŻŃICA(B1;B2; Y ) Jeśli bardzo zależy Ci na dokładności, możesz zastosować nieco zmodyfikowaną wer- sję tej formuły: =DATA.Z.ŻŃICA(B1;B2; Y ) la=, DATA.Z.ŻŃICA(B1;B2; YI ) miesięcy, DATA.Z.ŻŃICA(B1;B2; ID ) dni Formuła ta zwróci ciąg znaków podobny do przedstawionego poniżej: 32 la=, 7 miesięcy, 10 dni D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 169 170 Rozdział 5. ¨ Przydatne przykłady formuł Sposób 88. Szeregowanie wartości za pomocą formuły tablicowej Ustalanie porządku wartości znajdujących się w zakresie komórek okazuje się czasem bardzo przydatną możliwością. Jeśli masz na przykład arkusz kalkulacyjny zawierają- cy dane o rocznych wartościach sprzedaży osiągniętych przez dwudziestu przedstawi- cieli handlowych Twojej firmy, możesz dzięki temu dokonać klasyfikacji każdej z tych osób i dowiedzieć się, kto zajmuje jaką pozycję w rankingu sprzedaży przedsiębiorstwa, zaczynając od najwyższej, a kończąc na najniższej. Jeżeli zdarzyło Ci się już korzystać z oferowanej przez program Excel funkcji POZY- CJD, z pewnością zauważyłeś, że wyniki jej działania nie zawsze w pełni Ci odpowia- dają. Jeśli bowiem dwie wartości mają zajmować na przykład trzecie miejsce, funk- cja POZYCJD obydwu przypisze pozycję 3, a Ty być może wolałbyś przypisać im jakąś wartość średnią czy też środkową, co w tym przypadku oznaczałoby pozycję 3,5 dla obu danych. Na rysunku 88.1 przedstawiono arkusz kalkulacyjny, w którym zastosowano obie wy- mienione wyżej metody pozycjonowania kolumny wartości. Pierwsza z tych metod — wyniki jej działania widoczne są w kolumnie C — korzysta ze standardowej funk- cji POZYCJD programu Excel. W kolumnie D natomiast umieszczono wyniki działania formuł tablicowych zastosowanych do ustalenia pozycji poszczególnych liczb na liście. Zakres komórek B2:B9 nosi nazwę WartośćSprzedaży. Rysunek 88.1. Ustalanie pozycji danych za pomocą funkcji POZYCJA oferowanej przez program Excel oraz przy wykorzystaniu odpowiedniej formuły tablicowej Poniżej znajduje się formuła umieszczona w komórce D2; skopiowano ją również do komórek widocznych pod nią: =S(IA(1*(B2 =Oar=ośćSprzedaży))-(S(IA(1*(B2=Oar=ośćSprzedaży))-1)A2 Formuła tablicowa jest szczególnym rodzajem formuły i działa w odniesieniu do da- nych umieszczonych w tablicy. Podczas wprowadzania takiej formuły powinieneś na- cisnąć kombinację klawiszy Ctrl+Shift+Enter zamiast samego klawisza Enter, aby powiadomić program, że wpisana została właśnie formuła tablicowa, nie zaś zwykła. Excel wyświetla tego typu formuły w nawiasach klamrowych, co ma na celu przypo- mnienie Ci, że masz do czynienia z formułą tablicową. Efektu tego nie osiągniesz, wpisując nawiasy klamrowe z klawiatury. 170 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 89. Zliczanie znaków w komórce 171 Sposób 89. Zliczanie znaków w komórce Tutaj znajdziesz przykłady formuł, których zadaniem jest obliczanie liczby znaków wpisanych do komórki. Zliczanie wystąpień określonych znaków w komórce Podana niżej formuła oblicza liczbę wystąpień litery B (tylko wielkiej litery) w ciągu znaków umieszczonym w komórce A1: =DW(A1)-DW(PODSTAO(A1; B ; )) Działanie tej formuły opiera się na wykorzystaniu funkcji PODSADW tworzącej w pamię- ci programu nowy ciąg znaków, z którego usunięto wszystkie litery B. Kolejnym kro- kiem jest odjęcie długości otrzymanego ciągu od długości oryginalnego tekstu znajdu- jącego się w komórce i uzyskanie w ten sposób informacji na temat liczby wystąpień w nim litery B. Jeśli w komórce A1 będzie się na przykład znajdował tekst Biały bBbałyibą, formuła zwróci wartość 1. Przedstawiona poniżej formuła jest bardziej uniwersalna, gdyż pozwala na obliczenie liczby wystąpień litery B — zarówno wielkiej, jak i małej — w tekście znajdującym się w komórce A1: =DW(A1)-DW(PODSTAO(LITEZY.OIEL=IE(A1); B ; )) Umieszczenie w komórce A1 tekstu Biały bBbałyibą spowoduje, że formuła zwróci wartość 3. Zliczanie wystąpień ciągu znakowego w komórce Kolejna przedstawiona tu formuła pozwala na znajdowanie liczby wystąpień konkret- nego ciągu znaków. Zwraca ona liczbę wystąpień określonego ciągu tekstowego znaj- dującego się w komórce B1 w tekście umieszczonym w komórce A1. Poszukiwany ciąg tekstowy może zawierać dowolną liczbę znaków. =(DW(A1)-DW(PODSTAO(A1;B1; )))ADW(B1) Jeśli na przykład w komórce A1 zostanie umieszczony tekst Lbśnik na Lbśniku, a w B2 będzie się znajdował ciąg znaków Lbśnik, powyższa formuła zwróci liczbę 2. Wykonywane porównanie uwzględnia wielkość zastosowanych liter, a więc umieszcze- nie w komórce B1 tekstu abśnik spowoduje, że formuła zwróci wartość 0. Aby ominąć D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 171 172 Rozdział 5. ¨ Przydatne przykłady formuł to ograniczenie, powinieneś skorzystać ze zmodyfikowanej wersji formuły, która ma następującą postać: =(DW(A1)-DW(PODSTAO(LITEZY.OIEL=IE(A1); LITEZY.OIEL=IE(B1); )))ADW(B1) Sposób 90. Wyrażanie liczb w postaci liczebników porządkowych w języku angielskim Przydatna bywa nieraz możliwość wyrażania liczb w postaci liczebników porządko- wych. Zamienianie liczb na pełne słowa byłoby, co prawda, czynnością zbyt skom- plikowaną, a tworzenie skrótów cyfrowo-literowych w takim przypadku jest w języku polskim niepoprawne, w odróżnieniu od języka angielskiego, gdzie jest to normalną praktyką. Liczba 21 traktowana jako liczebnik porządkowy jest w nim na przykład wyrażana poprzez dodanie odpowiedniej końcówki, którą w tym przypadku jest st — a więc liczebnik przyjmuje postać 21st. Program Excel nie oferuje specjalnego for- matu liczbowego, który pomógłby w takiej sytuacji, możliwe jest jednak opracowanie odpowiedniej formuły, która wypełni to zadanie. W języku angielskim istnieją cztery końcówki dodawane do liczby w celu uzyskania liczebnika porządkowego. Są to: st, nd, rd i th. Wybór jednej z nich zależny jest od wartości przekształcanej liczby, a rządząca nim reguła jest dość zawiła. Z tego powo- du odpowiednia formuła również będzie dość skomplikowana. Większość liczb wy- maga użycia końcówki th. Wyjątkami od tej reguły będą liczby kończące się cyframi 1, 2 i 3, jednak nie takie, których drugą od końca cyfrą jest 1, a więc nie wartości koń- czące się liczbami 11, 12 i 13. Zasada ta może się wydawać dość zagmatwana, ale da się ją przełożyć na język zrozumiały dla Excela, a więc na formułę. Przedstawiona poniżej formuła przekształca liczbę całkowitą umieszczoną w komórce A1 na odpowiedni liczebnik porządkowy języka angielskiego: =A1 JEŻELI(L(B(OAZTOŚĆ(PZAOY(A1;2))={11;12;13}); =h ;JEŻELI(L(B(OAZTOŚĆ(PZAOY(A1))= {1;2;3});OYBIEZZ(PZAOY(A1); s= ; nd ; rd ); =h )) Formuła ta jest dość skomplikowana, postaram się więc wytłumaczyć Ci jej sposób działania. Jest on w skrócie taki: 1. Jeśli ostatnie dwie cyfry liczby to 11, 12 lub 13, użyj końcówki th. 2. Jeśli zasada 1. nie znajduje zastosowania, sprawdź ostatnią cyfrę. Jeżeli ostatnią cyfrą liczby jest 1, użyj końcówki st. Jeżeli ostatnią cyfrą liczby jest 2, skorzystaj z końcówki nd. Jeżeli ostatnią cyfrą liczby jest 3, użyj końcówki rd. 3. Jeśli żadna z powyższych zasad nie została zastosowana, użyj końcówki th. Na rysunku 90.1 przedstawiono efekty działania podanej wyżej formuły. 172 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 91. Wyodrębnianie słów z tekstów 173 Rysunek 90.1. Korzystanie z formuły do wyrażania liczb w postaci angielskich liczebników porządkowych Sposób 91. Wyodrębnianie słów z tekstów Zaprezentowane tutaj formuły będą przydatne do wyodrębniania słów z ciągów zna- ków znajdujących się w komórkach arkusza kalkulacyjnego. Jednej z nich możesz na przykład użyć do wydzielenia pierwszego słowa z tekstu. Wyodrębnianie pierwszego słowa z ciągu tekstowego Aby wydobyć pierwsze słowo z określonego tekstu, formuła musi zlokalizować w nim pozycję pierwszego znaku spacji, a następnie użyć tej informacji jako argumentu funk- cji LEWY. Działanie takie wykonuje następująca formuła: =LEOY(A1;ZŃAJDŹ( ;A1)-1) Zwraca ona wszystkie znaki, które znajdują się w tekście umieszczonym w komórce A1 przed wystąpieniem pierwszej spacji. Pojawia się tu jednak pewien problem — je- śli w komórce tej nie występuje żaden znak spacji, bo zawiera ona tylko jedno słowo, formuła zwróci kod błędu. Nieco bardziej rozbudowana wersja formuły rozwiązuje ten kłopot dzięki wykorzystaniu dodatkowych funkcji JEŻELI oraz CZY.B. do sprawdzenia faktu wystąpienia błędu: =JEŻELI(CZY.BW(ZŃAJDŹ( ;A1));A1;LEOY(A1;ZŃAJDŹ( ;A1)-1)) D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 173 174 Rozdział 5. ¨ Przydatne przykłady formuł Wyodrębnianie ostatniego słowa z ciągu tekstowego Wydobycie ostatniego słowa z łańcucha tekstowego jest nieco trudniejsze, ponieważ funkcja ZNDJDŹ przeszukuje teksty zawsze od lewej do prawej strony. Z tego powodu problemem jest tu znalezienie ostatniego znaku spacji w zadanym ciągu. Istnieje jed- nak pewne rozwiązanie, czego najlepszym dowodem jest zaprezentowana poniżej for- muła. Zwraca ona ostatnie słowo należące do tekstu, czyli wszystkie znaki znajdujące się po ostatniej spacji, która w nim występuje: =PZAOY(A1;DW(A1)-ZŃAJDŹ( * ;PODSTAO(A1; ; * ;DW(A1)-DW(PODSTAO(A1; ; ))))) Z formułą tą wiąże się jednak ten sam problem, który pojawił się w przypadku pierw- szej formuły przedstawionej wyżej: zwraca ona kod błędu w sytuacji, gdy zadany ciąg znaków nie zawiera przynajmniej jednej spacji. Zmodyfikowana wersja formuły wy- korzystuje funkcję JEŻELI do sprawdzenia, czy w tekście umieszczonym w komórce A1 znajdują się jakiekolwiek znaki spacji. Jeśli ich nie ma, zwrócona zostanie cała za- wartość tej komórki. W innym przypadku do akcji wkroczy przedstawiona wcześniej formuła: =JEŻELI(CZY.BW(ZŃAJDŹ( ;A1));A1;PZAOY(A1;DW(A1)-ZŃAJDŹ( * ;PODSTAO (A1; ; * ;DW(A1)-DW(PODSTAO(A1; ; )))))) Wyodrębnianie wszystkich słów z wyjątkiem pierwszego z ciągu tekstowego Następująca formuła zwraca zawartość komórki A1 z pominięciem pierwszego słowa: =PZAOY(A1;DW(A1)-ZŃAJDŹ( ;A1;1)) Jeśli komórka A1 będzie zawierała tekst Wstępny budżbt na rłk 2006, powyższa for- muła zwróci ciąg znaków budżbt na rłk 2006. Formuła ta zwróci natomiast kod błędu, gdy w komórce będzie się znajdować tylko jedno słowo. Problem ten rozwiązano w przedstawionej niżej formule, która w podob- nej sytuacji zwróci pusty ciąg tekstowy: =JEŻELI(CZY.BW(ZŃAJDŹ( ;A1)); ;PZAOY(A1;DW(A1)-ZŃAJDŹ( ;A1;1))) Sposób 92. Rozdzielanie nazwisk Załóżmy, że masz listę pełnych imion i nazwisk ludzi, znajdującą się w jednej kolum- nie. Twoim zadaniem jest rozdzielenie tych nazwisk na trzy kolumny w taki sposób, aby w pierwszej z nich znalazły się pierwsze imiona, w kolejnej drugie imiona lub inicjały, zaś w trzeciej nazwiska. Zadanie to jest bardziej skomplikowane, niż mogło- by się początkowo wydawać, ponieważ nie we wszystkich nazwiskach występujących w kolumnie użyto drugich imion czy też dodatkowych inicjałów. Mimo to problem jest możliwy do rozwiązania. 174 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 92. Rozdzielanie nazwisk 175 Opisane powyżej zadanie będzie dużo bardziej skomplikowane, gdy na liście znajdą się jeszcze nazwiska poprzedzone tytułami, takimi jak Pan czy dr, lub nazwiska za- wierające dodatkowe informacje, w rodzaju Jr. czy III. Przedstawione tu rozwiąza- nia nie uwzględniają tego typu trudnych przypadków, mimo to wyniki ich działania na- dal stanowić będą dobry punkt wyjścia, a z pojedynczymi błędnymi wpisami będziesz mógł sobie poradzić, ręcznie edytując odpowiednie komórki. We wszystkich zaprezentowanych niżej formułach przyjęto założenie, że imiona i na- zwisko umieszczone są w komórce A1. W prosty sposób możesz opracować formułę, która będzie wyodrębniała imię: =LEOY(A1;ZŃAJDŹ( ;A1)-1) Poniższa formuła będzie natomiast zwracała nazwisko: =PZAOY(A1;DW(A1)-ZŃAJDŹ( * ;PODSTAO(A1; ; * ;DW(A1)-DW(PODSTAO(A1; ; ))))) Następująca formuła wydobywa z całości zapisu drugie imię. Przy jej tworzeniu zało- żono, że pierwsze imię znajduje się w komórce B1, a wyodrębnione nazwisko umiesz- czone zostało w komórce D1: =JEŻELI(DW(B1 D1)+2 =DW(A1); ;FZAGIEŃT.TE=ST((A1;DW(B1)+2;DW(A1)-DW(B1 D1)-2)) Jak możesz zauważyć na rysunku 92.1, przedstawione tu formuły spisują się całkiem nieźle. W widocznym na nim arkuszu występują, co prawda, pewne problemy, zwłasz- cza w przypadku obcych nazwisk szlacheckich, w których pojawiają się dodatkowe słowa typu Van, ale zwykłe nazwiska rozdzielane są poprawnie. Poza tym, jak już wcze- śniej wspomniałem, te nieliczne błędy możesz poprawić ręcznie. Rysunek 92.1. W arkuszu tym użyto formuł do wyodrębnienia pierwszego imienia, drugiego imienia lub jego inicjału oraz nazwiska z wpisów imion i nazwisk znajdujących się na liście widocznej w kolumnie A W wielu przypadkach będziesz mógł wyeliminować konieczność używania formuł dzięki oferowanemu Ci przez program poleceniu Dane/Tekst jako kolumny…. Po- zwala ono na rozdzielenie tekstu na poszczególne elementy składowe. Wybranie tej komendy spowoduje wywołanie okna dialogowego Kreator konwersji tekstu na ko- lumny, który w kilku krokach przeprowadzi Cię przez proces przetwarzania pojedyn- czej kolumny danych w zbiór kolumn. W pierwszym kroku działania kreatora będziesz przeważnie używał opcji Rozdzielany, a w drugim kroku jako ogranicznik tekstu wy- bierzesz spację. D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 175 176 Rozdział 5. ¨ Przydatne przykłady formuł Sposób 93. Usuwanie tytułów z nazwisk Może się zdarzyć sytuacja, w której będziesz zmuszony do usunięcia tytułów (takich jak Pan, Pani czy Państył) poprzedzających nazwiska znajdujące się na liście umiesz- czonej w arkuszu Excela. Operację tę będziesz prawdopodobnie chciał przeprowadzić przed opisanym wcześniej rozdzielaniem pełnych nazwisk na ich części składowe. Z zamieszczonej poniżej formuły będziesz mógł skorzystać w celu usunięcia z komó- rek przechowujących nazwiska trzech występujących najczęściej tytułów, czyli słów Pan, Pani oraz Państył. Jeśli komórka A1 będzie na przykład zawierała nazwisko Pan Frydbryk Misiasty, efektem działania formuły będzie ciąg znaków Frydbryk Misiasty. =JEŻELI(L(B(LEOY(A1;7)= Pan ;LEOY(A1;()= Pani ;LEOY(A1;A)= Pa5s= o ); PZAOY(A1;DW(A1)-ZŃAJDŹ( ;A1));A1) W powyższej formule sprawdzane są trzy warunki. Jeśli zechcesz sprawdzać większą ich liczbę, na przykład w celu wyeliminowania kolejnych tytułów, powinieneś po pro- stu dodać odpowiednie argumenty w wywołaniu funkcji LUB. Sposób 94. Generowanie serii dat Z pewnością często zdarza się, że chcesz wprowadzić do arkusza serię dat. Na przy- kład przy zapisywaniu tygodniowych wartości obrotów firmy będziesz chciał wpro- wadzić serię dat oddzielonych od siebie o siedem dni. Daty te mogą służyć do identy- fikowania liczb opisujących sprzedaż. Używanie możliwości Autowypełnienie Najbardziej efektywna metoda wprowadzania serii danych nie wykorzystuje jakich- kolwiek formuł, sprowadza się bowiem do użycia możliwości automatycznego wypeł- niania kolejnych komórek arkusza następującymi po sobie datami. Żeby z niej skorzy- stać, powinieneś wpisać pierwszą datę, a następnie przeciągnąć uchwyt wypełniania komórek przy użyciu prawego przycisku myszki. Po zwolnieniu przycisku na ekranie pojawi się menu kontekstowe, z którego będziesz mógł wybrać odpowiednią dla sie- bie opcję, tak jak zostało to przedstawione na rysunku 94.1. Używanie formuł Przewagą rozwiązania wykorzystującego formuły nad używaniem funkcji Autowypeł- nienie do utworzenia serii dat jest możliwość zmiany pierwszej daty w serii, co pocią- gnie za sobą aktualizację wszystkich pozostałych danych. W celu skorzystania z tego 176 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 94. Generowanie serii dat 177 Rysunek 94.1. Korzystanie z możliwości Autowypełnienie w celu utworzenia serii dat rozwiązania powinieneś jedynie wpisać do pierwszej komórki właściwą datę począt- kową, a następnie do kolejnych komórek wprowadzić formuły, których zadaniem bę- dzie generowanie odpowiednich wartości. Przy tworzeniu przedstawionych niżej przykładów formuł przyjęto założenie, że pierw- sza data serii umieszczona została w komórce A1, a pierwsza formuła znajduje się w komórce A2. Odpowiednią liczbę kolejnych komórek należy po prostu wypełnić ko- pią tej formuły. Aby otrzymać serię dat oddzielonych od siebie okresem siedmiu dni, użyj następują- cej formuły: =A1+7 Aby wygenerować serię dat odległych od siebie o miesiąc, skorzystaj z formuły: =DATA(ZO=(A1);IIESIĄC(A1)+1;DZIEŃ(A1)) W celu otrzymania serii dat odległych od siebie dokładnie o rok zastosuj poniższą formułę: =DATA(ZO=(A1)+1;IIESIĄC(A1);DZIEŃ(A1)) Aby wygenerować serię dat składających się wyłącznie z dni tygodnia (bez sobót i nie- dziel), powinieneś skorzystać z zamieszczonej poniżej formuły. Formuła utworzona została przy założeniu, że data znajdująca się w komórce A1 jest dniem powszednim, czyli nie jest sobotą lub niedzielą. =JEŻELI(DZIEŃ.TYG(A1)=/;A1+3;A1+1) D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 177 178 Rozdział 5. ¨ Przydatne przykłady formuł Sposób 95. Określanie specyficznych dat Tutaj znajdziesz szereg przydatnych formuł, które zwracają pewne specyficzne daty. Określanie dnia roku 1 stycznia jest pierwszym dniem każdego roku, a 31 grudnia jest jego dniem ostatnim. Ale co z pozostałymi dniami, znajdującymi się pomiędzy nimi? Przedstawiona poniżej formuła zwraca kolejny numer dnia w roku dla daty przechowywanej w komórce A1: =A1-DATA(ZO=(A1);1;0) Kolejny numer dnia w roku określany jest czasem mianem daty juliańskiej. Następująca formuła zwraca liczbę dni, które pozostały do końca roku, licząc od po- danej daty umieszczonej w komórce A1: =DATA(ZO=(A1);12;31)-A1 Wprowadzenie którejkolwiek z powyższych formuł spowoduje, że program Excel za- stosuje formatowanie wartości daty w przypadku przechowujących je komórek. Bę- dziesz więc musiał sformatować je za pomocą któregoś z formatów numerycznych, aby móc przeglądać wyniki działania formuł w postaci liczbowej. Określanie dnia tygodnia Jeśli zajdzie potrzeba wyznaczenia, na jaki dzień tygodnia przypada określona data, z pomocą przyjdzie Ci funkcja DZIEŃ.AYG. Funkcja ta przyjmuje argument stanowiący datę i zwraca liczbę całkowitą z przedziału od 1 do 7, która odpowiada numerowi dnia w tygodniu — przy założeniu, że tydzień zaczyna się w niedzielę. Podana niżej formuła zwraca na przykład wartość 1, gdyż pierwszym dniem roku 2006 jest właśnie niedziela: =DZIEŃ.TYG(DATA(200/;1;1)) Dzień tygodnia dla określonej daty możesz również wyznaczyć, stosując do przecho- wującej ją komórki odpowiednie formatowanie niestandardowe. Aby dzień tygodnia wyświetlany był w postaci słowa stanowiącego jego nazwę, powinieneś na przykład zastosować następujący ciąg formatujący: dddd Pamiętaj jednak, że komórka naprawdę będzie nadal przechowywała pełną datę, a nie jedynie kolejny numer dnia tygodnia, jak w przypadku rozwiązania korzystają- cego z formuły. 178 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 95. Określanie specyficznych dat 179 Funkcja DZIEŃ.AYG umożliwia również podanie drugiego, opcjonalnego argumentu, któ- ry określa stosowany przez nią system numerowania dni w tygodniu. Jeśli użyjesz w tym celu liczby 2, funkcja zwróci wartość 1 dla poniedziałku, 2 dla wtorku i tak dalej. Zasto- sowanie liczby 3 jako drugiego argumentu funkcji DZIEŃ.AYG spowoduje, że w przypad- ku poniedziałku zwrócona zostanie wartość 0, w przypadku wtorku — 1 i tak dalej. Określanie daty ostatniej niedzieli Formuła, którą tu przedstawiam, zwraca datę ostatniego wystąpienia określonego dnia tygodnia. Możesz z niej skorzystać na przykład do wyznaczenia daty ostatniej niedzie- li, przy czym, jeśli aktualnym dniem jest niedziela, formuła zwróci datę dzisiejszą. Pa- miętaj o takim sformatowaniu komórki, aby wyświetlane były wartości daty. =DZIŚ()-IOD(DZIŚ()-1;7) Aby zmodyfikować powyższą formułę w celu wyznaczania dat innych dni niż niedzie- la, powinieneś zmienić występującą w niej liczbę 1 na inną wartość z przedziału od 2 (w przypadku poniedziałku) do 7 (dla soboty). Określanie pierwszego dnia tygodnia występującego po podanej dacie Znajdująca się poniżej formuła może być wykorzystana do wyznaczenia daty poda- nego dnia tygodnia, który będzie następował po określonej dacie. Możesz więc dzięki niej na przykład sprawdzić datę, jaką będzie miał pierwszy poniedziałek po 1 czerwca 2006 roku. Przy tworzeniu formuły przyjęto założenie, że w komórce A1 znajduje się data, a ko- mórka A2 zawiera liczbę z przedziału od 1 do 7, która określa dzień tygodnia, przy czym 1 oznacza niedzielę, 2 — poniedziałek i tak dalej. =A1+A2-DZIEŃ.TYG(A1)+(A2 DZIEŃ.TYG(A1))*7 W przypadku, gdy w komórce A1 znajduje się data 1 ązbryibą 2006, a komórka A2 zawiera oznaczającą poniedziałek liczbę 2, wynikiem działania formuły będzie 5 ązbr- yibą 2006 — ten dzień bowiem przypada w pierwszy poniedziałek po 1 czerwca 2006 (czwartek). Określanie n-tego wystąpienia podanego dnia tygodnia w miesiącu Podczas Twojej pracy może Ci się czasem przydać formuła pozwalająca na wyznacze- nie daty określonego wystąpienia w danym miesiącu pewnego dnia tygodnia. Wyobraź sobie na przykład, że dniem wypłaty pensji w Twojej firmie jest zawsze drugi piątek miesiąca, a Twoim zadaniem jest określenie dat wszystkich wypłat w rozpoczynającym się właśnie roku. Odpowiednie obliczenia wykona dla Ciebie poniższa formuła: D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc 179 180 Rozdział 5. ¨ Przydatne przykłady formuł =DATA(A1;A2;1)+A3-DZIEŃ.TYG(DATA(A1;A2;1))+(A7-(A3 =DZIEŃ.TYG(DATA(A1;A2;1))))*7 Przy tworzeniu tej formuły przyjęte zostały następujące założenia: t komórka A1 zawiera rok, t komórka A2 przechowuje miesiąc, t t w komórce A3 umieszczony jest kolejny numer odpowiedniego dnia tygodnia, czyli liczba 1 dla niedzieli, 2 dla poniedziałku i tak dalej, komórka A4 zawiera numer poszukiwanego wystąpienia określonego dnia, czyli na przykład 2 w sytuacji, gdy chcesz wyznaczyć datę drugiego wystąpienia dnia tygodnia określonego argumentem przechowywanym w komórce A3. Wykorzystanie tej formuły do określenia daty pierwszego piątku czerwca 2006 spowo- duje otrzymanie wartości 2 ązbryibą 2006. Określanie ostatniego dnia miesiąca W celu znalezienia daty odpowiadającej ostatniemu dniu określonego miesiąca możesz skorzystać z funkcji DDAD. Należy tu wykorzystać fakt, że zerowy dzień następnego miesiąca jest traktowany przez tę funkcję jako ostatni dzień miesiąca go poprzedzające- go, a więc trzeba podać jej argument miesiąca zwiększony o 1 i wartość dnia równą 0. Przy opracowywaniu podanej niżej formuły założono, że w komórce A1 znajduje się data określająca wybrany miesiąc. Formuła w wyniku swojego działania zwróci ostat- ni dzień tego właśnie miesiąca. =DATA(ZO=(A1);IIESIĄC(A1)+1;0) Wariacji tej formuły możesz użyć do wyznaczenia liczby dni wchodzących w skład podanego miesiąca. Zaprezentowana poniżej formuła zwraca liczbę całkowitą okre- ślającą liczbę dni miesiąca zdefiniowanego za pomocą daty umieszczonej w komórce A1. Upewnij się, że komórka przechowująca tę formułę korzysta ze zwykłego forma- towania liczbowego, nie zaś z formatowania daty. =DZIEŃ(DATA(ZO=(A1);IIESIĄC(A1)+1;0)) Określanie kwartału, do którego należy podany dzień Przy tworzeniu raportów finansowych pomocna może się okazać możliwość prezen- towania informacji odnoszących się do poszczególnych kwartałów danego roku. Po- dana niżej formuła zwraca liczbę całkowitą z przedziału od 1 do 4. Liczba ta określa kwartał, do którego należy data znajdująca się w komórce A1: =ZAO=Z.G.ZA(IIESIĄC(A1)A3;0) Działanie tej formuły polega na podzieleniu numeru miesiąca przez liczbę 3, a następ- nie zaokrągleniu otrzymanego wyniku w górę. 180 D:drukExcel Najlepsze sztuczki i chwyty9_druk 05.doc Sposób 96. Wyświetlanie kalendarza w zakresie komórek arkusza 181 Sposób 96. Wyświetlanie kalendarza w zakresie komórek arkusza Tutaj znajdziesz opis metody tworzenia w zakresie komórek dynamicznego kalenda- rza na dowolny miesiąc wybranego roku. Na rysunku 96.1 przedstawiono przykłado- wy kalendarz tego typu. Zmiana daty widocznej w jego górnej części spowoduje, że kalendarz zostanie przeliczony od nowa tak, aby wyświetlane były daty dla podanego roku i miesiąca. Rysunek 96.1. Pokazany tu kalendarz został utworzony za pomocą skomplikowanej formuły tablicowej Aby utworzyć ten kalendarz w komórkach B2:H9, postępuj według następujących in- strukcji: 1. Zaznacz zakres komórek B2:H2 i scal komórki, klikając przycisk Scal i wyśrodkuj widoczny na pasku narzędzi Formatowanie. 2. Do scalonego zakresu wprowadź datę. Podany dzień miesiąca nie będzie tu miał żadnego znaczenia. 3. Do komórek B3:H3 wpisz skróty nazw dni tygodnia. 4
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel. Najlepsze sztuczki i chwyty
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ą: