Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00665 008163 10499516 na godz. na dobę w sumie
100 sposobów na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy - książka
100 sposobów na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy - książka
Autor: , Liczba stron: 368
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-1331-1 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Wykorzystaj wszystkie możliwości Excela, aby tworzyć funkcjonalne i efektowne arkusze

Większość użytkowników Excela zna tylko drobną część dostępnych w nim możliwości. Istnieje jednak wiele metod pozwalających znacząco poszerzyć wydajność tego programu i tworzyć efektowne arkusze kalkulacyjne bez potrzeby czasochłonnego zdobywania wiedzy. Nowa wersja Excela umożliwia chociażby łatwiejsze korzystanie z mechanizmu tabel przestawnych, formatowania warunkowego i nazw zakresów, podglądu 'na żywo', galerii predefiniowanych stylów oraz grafik SmartArt. Wszystko to sprawia, że każdy może dziś w swoich arkuszach używać bardziej skomplikowanych elementów wizualnych i graficznych.

Obecnie, kiedy wszystko dzieje się coraz szybciej, czas staje się jedną z najistotniejszych i pożądanych wartości. Książka '100 sposobów na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy' pozwala Ci właśnie zyskać na czasie - oferuje ponad sto gotowych metod tworzenia funkcjonalnych i efektownych arkuszy, szybkich i niezawodnych rozwiązań skomplikowanych problemów. Warto także wykorzystać te metody do przeanalizowania oraz zastosowania niektórych możliwości języka Visual Basic for Applications (VBA) - dzięki temu będziesz mógł zaadaptować wszystkie zawarte tu propozycje do własnych potrzeb.

Wszystko, co chciałbyś widzieć o Excelu, aby natychmiast z niego skorzystać.

Poznaj ponad setkę sposobów skutecznego radzenia sobie z Excelem!

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

Darmowy fragment publikacji:

100 sposob(cid:243)w na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy Autor: David Hawley, Raina Hawley T‡umaczenie: Zbigniew Smogur ISBN: 978-83-246-1331-1 Tytu‡ orygina‡u: Excel Hacks: Tips Tools for Streamlining Your Spreadsheets Format: 168x237, stron: 368 Wykorzystaj wszystkie mo¿liwo(cid:156)ci Excela, aby tworzy(cid:230) funkcjonalne i efektowne arkusze (cid:149) Jak analizowa(cid:230) dane i zarz„dza(cid:230) nimi? (cid:149) Jak optymalnie wykorzystywa(cid:230) tabele przestawne? (cid:149) Jak tworzy(cid:230) spersonalizowane wykresy? WiŒkszo(cid:156)(cid:230) u¿ytkownik(cid:243)w Excela zna tylko drobn„ czŒ(cid:156)(cid:230) dostŒpnych w nim mo¿liwo(cid:156)ci. Istnieje jednak wiele metod pozwalaj„cych znacz„co poszerzy(cid:230) wydajno(cid:156)(cid:230) tego programu i tworzy(cid:230) efektowne arkusze kalkulacyjne bez potrzeby czasoch‡onnego zdobywania wiedzy. Nowa wersja Excela umo¿liwia chocia¿by ‡atwiejsze korzystanie z mechanizmu tabel przestawnych, formatowania warunkowego i nazw zakres(cid:243)w, podgl„du (cid:132)na ¿ywo(cid:148), galerii predefiniowanych styl(cid:243)w oraz grafik SmartArt. Wszystko to sprawia, ¿e ka¿dy mo¿e dzi(cid:156) w swoich arkuszach u¿ywa(cid:230) bardziej skomplikowanych element(cid:243)w wizualnych i graficznych. Obecnie, kiedy wszystko dzieje siŒ coraz szybciej, czas staje siŒ jedn„ z najistotniejszych i po¿„danych warto(cid:156)ci. Ksi„¿ka (cid:132)100 sposob(cid:243)w na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy(cid:148) pozwala Ci w‡a(cid:156)nie zyska(cid:230) na czasie (cid:150) oferuje ponad sto gotowych metod tworzenia funkcjonalnych i efektownych arkuszy, szybkich i niezawodnych rozwi„zaæ skomplikowanych problem(cid:243)w. Warto tak¿e wykorzysta(cid:230) te metody do przeanalizowania oraz zastosowania niekt(cid:243)rych mo¿liwo(cid:156)ci jŒzyka Visual Basic for Applications (VBA) (cid:150) dziŒki temu bŒdziesz m(cid:243)g‡ zaadaptowa(cid:230) wszystkie zawarte tu propozycje do w‡asnych potrzeb. (cid:149) Skoroszyty i arkusze (cid:149) Wbudowane mechanizmy do analizy danych i zarz„dzania nimi (cid:149) Metody tworzenia nazw i zakres(cid:243)w kom(cid:243)rek (cid:149) Tabele przestawne (cid:149) Formu‡y i funkcje (cid:149) Wykresy i makra (cid:149) Wsp(cid:243)‡praca Excela z innymi aplikacjami pakietu Office Wszystko, co chcia‡by(cid:156) widzie(cid:230) o Excelu, aby natychmiast z niego skorzysta(cid:230). Poznaj ponad setkŒ sposob(cid:243)w skutecznego radzenia sobie z Excelem! Wydawnictwo Helion ul. Ko(cid:156)ciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl Spis treści O Autorach ....................................................................................................................................11 Przedmowa ....................................................................................................................................13 Rozdział 1. Ujarzmianie skoroszytów i arkuszy .........................................................................19 1. Tworzenie własnego widoku skoroszytów ................................................................23 2. Wpisywanie danych w kilku arkuszach jednocześnie ..............................................26 3. Uniemożliwianie użytkownikom wykonywania określonych czynności ..............28 4. Zapobieganie wyświetlaniu niepotrzebnych komunikatów ....................................32 5. Ukrywanie arkuszy w taki sposób, by nie można było ich odkryć ........................36 6. Dostosowywanie okna dialogowego Szablony i skoroszytu domyślnego ............37 7. Tworzenie indeksu arkuszy znajdujących się w skoroszycie ..................................40 8. Ograniczanie zakresu przewijania arkusza ................................................................42 9. Blokowanie i ochrona komórek zawierających formuły ...........................................46 10. Znajdowanie powtarzających się danych przy użyciu formatowania warunkowego ..................................................................49 11. Znajdowanie danych występujących przynajmniej dwa razy przy użyciu formatowania warunkowego ..................................................................50 12. Przypisywanie własnych pasków narzędzi do konkretnych skoroszytów ...........52 13. Przechytrzanie mechanizmu odwołań względnych ..................................................53 14. Usuwanie pustych łączy ze skoroszytów ...................................................................54 15. Zmniejszanie rozmiaru spuchniętego skoroszytu .....................................................57 16. Odzyskiwanie danych z uszkodzonego arkusza .......................................................60 Rozdział 2. Sposoby na wbudowane mechanizmy Excela ........................................................63 17. Sprawdzanie poprawności danych na podstawie listy z innego arkusza .............63 18. Sterowanie formatowaniem warunkowym przy użyciu pól wyboru ....................65 19. Odnajdywanie formuł przy użyciu formatowania warunkowego .........................69 20. Zliczanie lub sumowanie komórek spełniających kryteria formatowania warunkowego ..................................................................................................................70 21. Wyróżnianie co drugiego wiersza lub kolumny ........................................................72 22. Tworzenie efektów 3D w tabelach lub komórkach ...................................................74 23. Włączanie i wyłączanie formatowania warunkowego i sprawdzania poprawności danych przy użyciu pola wyboru ..............................76 Spis treści | 5 24. Obsługa kilku list przy użyciu pola kombi ................................................................ 78 25. Tworzenie list sprawdzania poprawności zmieniających się zależnie od wartości na innej liście .............................................................................. 80 26. Usuwanie niechcianych znaków przy użyciu polecenia Zamień… ....................... 82 27. Przekształcanie liczb zapisanych w tekście w wartości liczbowe ........................... 83 28. Wyodrębnianie części numerycznej z zawartości komórki ..................................... 84 29. Dostosowywanie komentarzy w komórkach ............................................................. 85 30. Sortowanie względem więcej niż trzech kolumn ...................................................... 87 31. Sortowanie losowe ......................................................................................................... 88 32. Manipulowanie danymi przy użyciu Filtra zaawansowanego ............................... 89 33. Tworzenie własnych formatów liczbowych ............................................................... 92 34. Zwiększanie liczby możliwych operacji Cofnij w Excelu dla Windowsa .............. 97 35. Tworzenie własnych list ................................................................................................ 98 36. Pogrubianie znaków sum pośrednich Excela ............................................................. 98 37. Przekształcanie formuł i funkcji Excela w wartości ................................................ 101 38. Automatyczne dodawanie danych do list sprawdzania poprawności danych .. 103 39. Sposoby na datę i godzinę w Excelu ......................................................................... 106 40. Używanie mechanizmów grupowania i konspektu w zabezpieczonych skoroszytach .................................................................................................................. 110 41. Zabezpieczanie przed pojawianiem się w tabeli pustych lub brakujących pól .. 112 42. Tworzenie zmniejszającej się listy sprawdzania poprawności danych ................ 113 43. Dodawanie listy użytkownika do uchwytu wypełnienia ...................................... 114 Rozdział 3. Sposoby na nazwy ..................................................................................................117 44. Odwoływanie się do danych przy użyciu nazwy ................................................... 117 45. Używanie tej samej nazwy dla zakresów w różnych arkuszach .......................... 118 46. Tworzenie własnych funkcji z użyciem nazwy ....................................................... 120 47. Tworzenie zakresów rozszerzających się i zwężających ........................................ 123 48. Zagnieżdżanie zakresów dynamicznych dla zmaksymalizowania elastyczności ...................................................................... 129 49. Znajdowanie w arkuszu zakresów nazwanych ....................................................... 132 Rozdział 4. Sposoby na tabele przestawne ..............................................................................135 50. Tabele przestawne: sposób sam w sobie ................................................................... 135 51. Współdzielenie tabel przestawnych bez udostępniania danych źródłowych ..... 139 52. Automatyzacja tworzenia tabel przestawnych ........................................................ 141 53. Przesuwanie sum końcowych tabeli przestawnej ................................................... 144 54. Wydajne przestawianie danych z innego skoroszytu ............................................. 146 6 | Spis treści Rozdział 5. Sposoby na wykresy ...............................................................................................149 55. Wysuwanie części wykresu kołowego ......................................................................149 56. Prezentowanie dwóch kompletów części wykresu kołowego na jednym wykresie .....................................................................................................151 57. Tworzenie wykresów dopasowujących się do danych ...........................................153 58. Interakcja z wykresami za pośrednictwem własnych kontrolek ...........................156 59. Cztery sposoby na szybkie uaktualnienie wykresów .............................................160 60. Tworzenie wykresu mającego postać termometru ..................................................164 61. Tworzenie wykresu kolumnowego z kolumnami o zmiennych szerokościach i wysokościach .....................................................................................167 62. Tworzenie wykresu przypominającego licznik .......................................................171 63. Łączenie tekstowych elementów wykresu z komórkami .......................................178 64. Pomijanie na wykresie pustych komórek źródłowych lub komórek zawierających wartość FAŁSZ ....................................................................................179 65. Dodawanie strzałki pokazującej trend na końcu serii liniowej .............................182 66. Umieszczanie strzałki na końcu osi poziomej (X) ...................................................183 67. Poprawianie wąskich kolumn, kiedy na wykresie korzysta się z dat ..................186 68. Pozycjonowanie etykiet osi .........................................................................................187 69. Wykres typu tornado ...................................................................................................190 70. Wykres wskaźnikowy ..................................................................................................191 71. Warunkowe podświetlanie etykiet osi ......................................................................193 72. Tworzenie sum na skumulowanych wykresach kolumnowych ...........................196 Rozdział 6. Sposoby na formuły i funkcje ................................................................................199 73. Dodawanie opisu do formuł .......................................................................................199 74. Przesuwanie formuł względnych bez zmiany odwołań do komórek ..................200 75. Porównywanie dwóch zakresów komórek Excela ..................................................201 76. Wypełnienie wszystkich pustych komórek znajdujących się na liście .................203 77. Zwiększanie numerów wierszy w formułach kopiowanych między kolumnami .....................................................................................................................205 78. Przekształcanie dat w daty w formacie Excela ........................................................207 79. Sumowanie lub zliczanie komórek z pominięciem wartości błędnych ................208 80. Zmniejszanie wpływu funkcji zmiennych na wydajność obliczeń .........................210 81. Zliczanie tylko pojedynczych wystąpień każdej pozycji na liście .........................211 82. Sumowanie co drugiego, co trzeciego lub co n-tego wiersza lub komórki .........213 83. Znajdowanie n-tego wystąpienia wartości ...............................................................215 84. Nadawanie dynamicznego charakteru funkcji Excela SUMY.POŚREDNIE ........217 85. Dodawanie rozszerzeń dat ..........................................................................................219 86. Przekształcanie liczb ze znakiem minus z prawej strony na liczby rozpoznawane przez Excela .......................................................................220 87. Wyświetlanie ujemnych wartości godzinowych ......................................................222 Spis treści | 7 88. Używanie funkcji WYSZUKAJ.PIONOWO względem kilku tabel ...................... 224 89. Prezentowanie sumy czasu jako liczby dni, godzin i minut .................................. 226 90. Ustalanie liczby wystąpień podanych dni w miesiącu ........................................... 227 91. Tworzenie megaformuł ............................................................................................... 228 92. Sposób na megaformuły odwołujące się do innych skoroszytów ........................ 231 93. Zastępowanie wielu funkcji Excela przez jedną z funkcji bazy danych .............. 232 94. Wyciąganie określonych wyrazów z łańcucha znaków ......................................... 238 95. Liczenie wyrazów w komórce lub zakresie komórek ............................................. 239 96. Zwracanie w komórce nazwy arkusza ...................................................................... 241 97. Sumowanie komórek z wieloma warunkami .......................................................... 244 98. Obliczanie komórek z wieloma kryteriami .............................................................. 247 99. Obliczanie zmiennej (progresywnej) skali podatkowej .......................................... 250 100. Dodawanie i odejmowanie miesięcy od daty .......................................................... 255 101. Odnajdywanie ostatniego dnia w dowolnym miesiącu ......................................... 257 102. Obliczanie wieku osoby ............................................................................................... 259 103. Zwracanie dnia tygodnia podanej daty .................................................................... 260 104. Szacowanie równania tekstowego ............................................................................. 261 105. Wyszukiwanie z poziomu komórki ........................................................................... 263 Rozdział 7. Sposoby na makra ..................................................................................................267 106. Przyspieszanie wykonania kodu przez wyłączenie migotania ekranu ................ 267 107. Uruchamianie makra o określonym czasie ............................................................... 268 108. Używanie nazw kodowych jako odwołań do arkuszy w skoroszytach Excela .. 269 109. Łatwe przypisywanie makr do przycisków ............................................................. 271 110. Wyświetlanie okienek powitalnych ........................................................................... 272 111. Wyświetlanie komunikatu „Proszę czekać” ............................................................. 274 112. Umieszczanie lub usuwanie z wybieranych komórek symbolu zaznaczenia ..... 275 113. Zliczanie lub sumowanie komórek o określonym kolorze cieniowania .............. 276 114. Dodawanie do skoroszytów Excela kontrolki kalendarza ..................................... 277 115. Zabezpieczenie hasłem i odbezpieczanie wszystkich arkuszy Excela w jednym ruchu ............................................................................................................ 280 116. Odczytywanie nazwy i ścieżki skoroszytu ............................................................... 282 117. Radzenie sobie z ograniczeniem pozwalającym zdefiniować jedynie trzy warunki formatowania warunkowego ............................................................. 283 118. Wykonywanie procedur w zabezpieczonych arkuszach ....................................... 285 119. Udostępnianie makr ..................................................................................................... 287 120. Kasowanie wierszy w oparciu o warunek ................................................................ 291 121. Śledzenie i raportowanie zmian w Excelu ................................................................ 295 122. Automatyczne wpisywanie do komórki daty lub godziny przy wpisywaniu danych ............................................................................................ 299 123. Tworzenie listy odnośników do skoroszytów ......................................................... 300 8 | Spis treści 124. Zaawansowane wyszukiwanie ...................................................................................302 125. Znajdowanie liczby z podanego zakresu ..................................................................307 126. Zamiana odwołań w formułach ze względnych na bezwzględne ........................310 127. Nazywanie skoroszytu tekstem z komórki ..............................................................315 128. Ukrywanie i przywracanie pasków narzędzi w Excelu .........................................315 129. Sortowanie arkuszy ......................................................................................................318 130. Zabezpieczanie arkusza hasłem przed przeglądaniem ..........................................319 131. Zamiana tekstu na wielkie litery lub na nazwy własne .........................................320 132. Wymuszanie, aby tekst był pisany wielką literą lub jak nazwy własne ..............323 133. Blokowanie reagowania na wielkie litery w kodzie VBA ......................................326 134. Wyświetlanie kryterium filtra automatycznego ......................................................327 Rozdział 8. Sposoby na współpracę różnych aplikacji ...........................................................329 135. Importowanie danych z Accessa 2007 do Excela 2007 ............................................329 136. Odczytywanie danych z zamkniętych skoroszytów ...............................................334 137. Automatyzacja Worda z poziomu Excela .................................................................341 138. Automatyzacja Outlooka z poziomu Excela .............................................................345 Skorowidz ....................................................................................................................................351 Spis treści | 9 Sprawdzanie poprawności danych na podstawie listy z innego arkusza SPOSÓB 17. R O Z D Z I A Ł D R U G I Sposoby na wbudowane mechanizmy Excela Sposoby 17. – 43. Excel posiada co prawda dosyć szeroki zbiór standardowych mechanizmów zarządzania i analizy danych, lecz ograniczenia tych narzędzi często wprawiają w irytację. Sposoby przedstawione w tym rozdziale mają na celu obejście tych ograniczeń i w efekcie uczy- nienie z Excela narzędzia o znacznie większych możliwościach. S PO SÓB 17. Wszystkie Sprawdzanie poprawności danych na podstawie listy z innego arkusza Sprawdzanie poprawności danych to narzędzie, w którym można zdefiniować reguły poprawności danych. Niestety Excel „upiera się”, że listy używane w trakcie sprawdzania poprawności muszą znajdować się w tym samym arkuszu, co weryfikowane dane. Na szczęście istnieją sposoby, bo ominąć ten wymóg. W tym sposobie przedstawimy dwie metody, których można użyć do sprawdzenia po- prawności danych na podstawie listy znajdującej się w innym arkuszu. W pierwszej meto- dzie wykorzystywane są zakresy nazwane (opisane bardziej szczegółowo w rozdziale 3.), druga natomiast polega na wywołaniu odpowiedniej funkcji. Metoda 1. Zakresy nazwane Chyba najłatwiejszą i najprostszą metodą obejścia ograniczeń istniejących w mechanizmie sprawdzania poprawności danych jest zdefiniowanie komórek, w których znajduje się lista, jako zakresu nazwanego. Aby utworzyć zakres nazwany, należy zaznaczyć komórki zawierające listę i w polu Nazwa znajdującym się na lewym krańcu paska formuł wpisać ich nazwę. W tym przykładzie przyjmiemy, że zakres ten nosi nazwę MójZakres. Należy zaznaczyć komórkę, w której będzie miała się znajdować lista rozwijana, i z karty Dane wybrać polecenie Narzędzia danych→Poprawność danych (w poprzednich wersjach Dane →Sprawdzanie poprawności…). W polu Dozwolone: należy wskazać pozycję Lista, a w polu Źródło: wpisać =MójZakres, po czym kliknąć przycisk OK. Dzięki użyciu zakresu nazwanego danej listy można użyć jako listy poprawności, mimo że znajduje się ona w innym arkuszu. Sposoby na wbudowane mechanizmy Excela | 63 SPOSÓB 17. Sprawdzanie poprawności danych na podstawie listy z innego arkusza Metoda 2. Funkcja ADR.POŚR Funkcja ADR.POŚR pozwala na odwoływanie się do komórek zawierających tekst repre- zentujący adres komórki. Komórki tej można zatem użyć jako odwołania do komórki lokalnej, pomimo że pobiera ona dane z innego arkusza. Dzięki temu mechanizmowi można więc odwoływać się do arkusza, w którym zdefiniowano listę. Załóżmy, że listę zdefiniowano w arkuszu Arkusz1, w zakresie komórek $A$1:$A$8. W innym arkuszu należy kliknąć komórkę, w której ma się pojawić lista sprawdzania po- prawności danych (lista wyboru). Następnie na karcie Dane trzeba wybrać polecenie Na- rzędzia danych→Poprawność danych (we wcześniejszych wersjach Dane→Sprawdzanie po- prawności…) i w polu Dozwolone: wskazać pozycję Lista, a w polu Źródło: wpisać: =ADR.POŚR( Arkusz1!$A$1:$A$8 ) Należy jeszcze upewnić się, że zaznaczone jest pole wyboru Rozwinięcia w komórce, po czym kliknąć przycisk OK. Na rozwijanej liście sprawdzania poprawności powinna pojawić się lista zdefiniowana w arkuszu Arkusz1. Jeżeli nazwa arkusza, w którym zdefiniowano listę, zawiera znaki spacji, trzeba będzie użyć funkcji ADR.POŚR w następujący sposób: =ADR.POŚR( Arkusz 1 !$A$1:$A$8 ) Różnica polega na tym, że w drugim przypadku należy wpisać znak apostrofu zaraz za cudzysłowem otwierającym oraz kolejny apostrof przed znakiem wykrzyknika. Dobrą praktyką jest używanie znaku apostrofu bez względu na to, czy nazwa arkusza zawiera znaki spacji, czy nie. Obecność apostrofów nie zmieni sposobu odwoływania się do arkusza, którego nazwa nie zawiera spacji, natomiast znacznie ułatwi wprowadzanie ewentualnych zmian w przyszłości. Wady i zalety obydwu metod Zakresy nazwane i funkcja ADR.POŚR mają określone wady i zalety. Zaletą zakresów nazwanych jest to, że jakakolwiek zmiana w nazwie arkusza nie będzie miała żadnego wpływu na listę sprawdzania poprawności. Od razu widać więc wadę funkcji ADR.POŚR: zmiana nazwy arkusza nie zostanie w tej funkcji odzwierciedlona automatycznie, trzeba więc będzie ręcznie zmienić jej definicję, tak by była ona zgodna z nową nazwą arkusza. Zaletą funkcji ADR.POŚR jest to, że określony przez użytkownika zakres zawsze będzie taki sam, co oznacza, że jeżeli wykorzystuje się taki nazwany zakres i usunie z niego wiersze lub kolumny, to nazwany zakres zostanie samoczynnie odpowiednio dopasowany. 64 | Sposoby na wbudowane mechanizmy Excela S PO SÓB 18. Wszystkie Sterowanie formatowaniem warunkowym przy użyciu pól wyboru SPOSÓB 18. Sterowanie formatowaniem warunkowym przy użyciu pól wyboru Formatowanie warunkowe to jeden z najważniejszych mechanizmów Excela, lecz konieczność każdorazowego jego włączania i wyłączania z poziomu wstążki stanowi pewną niedogodność. Dodanie w arkuszu pól wyboru, które będą włączać i wyłączać formatowanie warunkowe, sprawi, że znacznie łatwiej będzie czytać dane w dowolny sposób i w dowolnym momencie. Formatowanie warunkowe — narzędzie dostępne począwszy od Excela w wersji 97 — służy do definiowania formatów dla komórek, których wartości spełniają określone kryteria definiowane za pomocą osobnych wartości lub innych formuł. Warunki forma- towania są najczęściej definiowane na podstawie wartości komórek, lecz użycie w ich definicjach formuł zapewnia na tyle dużą elastyczność, że formatowanie warunkowe będzie można zastosować w całej siatce arkusza. Ustawianie wartości pól wyboru dla formatowania warunkowego Pola wyboru dostępne w grupie Formanty formularza, które można znaleźć na karcie Dewelo- per po wybraniu polecenia Formanty→Wstaw (we wcześniejszych wersjach znajdują się one na pasku narzędzi Formularze), zwracają w komórce, z którą są powiązane, wartości PRAWDA lub FAŁSZ (zaznaczone lub nie). Wykorzystując pole wyboru z grupy Formanty formularza w połączeniu z formatowaniem warunkowym z ustawioną opcją Użyj formuły do określenia komórek, które należy sformatować (we wcześniejszych wersjach jest to opcja Formuła jest), widoczną na rysunku 2.1, można włączać i wyłączać formatowanie warun- kowe w zależności od stanu pola wyboru. Rysunek 2.1. Okno dialogowe Formatowanie warunkowe z opcją Formuły Jeśli formatowanie warunkowe zostanie zdefiniowane przy użyciu formuły (na przykład z opcją Użyj formuły do określenia komórek, które należy sformatować), komórki zostaną sformatowane tylko wówczas, gdy wynikiem formuły będzie PRAWDA. Każda formuła używana w tym sposobie musi zatem zwracać wartości PRAWDA lub FAŁSZ. Sposoby na wbudowane mechanizmy Excela | 65 SPOSÓB 18. Sterowanie formatowaniem warunkowym przy użyciu pól wyboru Aby pokazać, co dokładnie mamy na myśli, posłużymy się prostym przykładem, w którym przy użyciu formatowania warunkowego i pola wyboru zostaną ukryte dane. W przy- kładzie tym użyjemy komórek z zakresu $A$1:$A$10 wypełnionych kolejnymi licz- bami od 1 do 10. Aby z grupy Formanty formularza wstawić pole wyboru, należy przejść na kartę Deweloper i wybrać polecenie Wstaw znajdujące się w grupie poleceń Formanty (we wcześniejszych wersjach należy wyświetlić pasek narzędzi Formularze, wybierając polecenie Widok→Paski narzędzi→Formularze), kliknąć pole wyboru, a następnie kliknąć w pobliżu komórki C1, by umieścić je w tym miejscu. Następnie trzeba kliknąć prawym przyciskiem myszy na polu wyboru i wybrać polecenie Formatuj formant…→Formant, w polu Łącze komórki wpisać C1, tak jak na rysunku 2.2, i kliknąć przycisk OK. Rysunek 2.2. Okno dialogowe Formatowanie formantu Gdy pole wyboru umieszczone na komórce C1 zostanie kliknięte myszą, w komórce tej pojawi się wartość PRAWDA lub FAŁSZ. Wartości te nie muszą być widoczne, można zatem zaznaczyć komórkę C1 i zmienić kolor jej czcionki na biały. W kolejnym kroku należy zaznaczyć komórki $A$1:$A$10, rozpoczynając zaznaczanie od komórki A1, po czym przejść na kartę Narzędzia główne i z grupy opcji Style wybrać polecenie Formatowanie warunkowe→Nowa reguła…. Następnie należy zaznaczyć opcję Użyj formuły do określenia komórek, które należy sformatować (w wersjach wcześniejszych należy kliknąć polecenie Format→Formatowanie warunkowe…, wskazać opcję Formuła jest; początkowo wybrana będzie opcja Wartość komórki jest). W polu Formatuj wartości, dla których następująca formuła jest prawdziwa: (w starszych wersjach w polu formuły z prawej strony) trzeba wpisać =$C$1. Następnie w oknie dialogowym Nowa reguła formatowania trzeba kliknąć przycisk Formatuj… (w starszych wersjach w oknie dialogowym Formatowanie warunkowe trzeba wybrać zakładkę Czcionka) i zmienić kolor czcionki na biały. Na koniec należy kliknąć przycisk OK i ponownie OK. 66 | Sposoby na wbudowane mechanizmy Excela SPOSÓB 18. Sterowanie formatowaniem warunkowym przy użyciu pól wyboru Zaznaczenie pola wyboru spowoduje, że kolor czcionki w komórkach z zakresu $A$1: $A$10 automatycznie zmieni się na biały. Usunięcie zaznaczenia pola wyboru sprawi, że komórki powrócą do normalnego stanu. Włączanie i wyłączanie wyróżniania liczb Możliwość automatycznego wyróżniania liczb spełniających określone kryteria zdecy- dowanie ułatwia wyszukiwanie w arkuszu potrzebnych danych. Możliwość wykorzystania formatowania warunkowego do formatowania liczb z pewnego zakresu jest nową opcją dostępną w Excelu 2007. Jednakże możliwość zaznaczania i wyłączania pola wyboru opisana w tym sposobie taką nie jest. Aby skorzystać z tego udogodnienia, należy najpierw zaznaczyć komórkę E1 (lub dowolną inną komórkę) i nadać jej nazwę ŁączePolaWyboru, wpisując ją w polu Nazwa z lewej strony paska formuł (rysunek 2.3). Rysunek 2.3. Komórce E1 nadano nazwę ŁączePolaWyboru W komórce F1 należy umieścić pole wyboru z grupy narzędzi Formanty formularza (w po- przednich wersjach z paska narzędzi Formularze). Jako Łącze komórki pola wyboru należy wskazać komórkę ŁączePolaWyboru — w tym celu należy kliknąć pole wyboru prawym przyciskiem myszy, wybrać Formatuj formant…→Formant, w polu Łącze komórki: wpisać ŁączePolaWyboru i kliknąć OK. Następnie należy ponownie kliknąć prawym przyciskiem myszy pole wyboru, wybrać pozycję Edytuj tekst i wpisać słowa Pokaż mnie. W kolumnie A w innym arkuszu trzeba wpisać liczby z zakresu od 25 do 2500, z których każda będzie wyższa od poprzedniej o 25. Zakresowi temu należy nadać nazwę Liczby, a sam arkusz ukryć za pomocą polecenia Widok→Ukryj (wcześniej Format→Arkusz→Ukryj). Aby szybko wpisać te liczby, należy wpisać w komórce A1 liczbę 25. Następnie należy kliknąć prawym przyciskiem myszy (kliknąć przycisk Control na komputerach Mac) punkt wypełnienia (który pojawia się w formie małego, czarnego kwadracika w prawym dolnym rogu zaznaczenia) i nadal trzymając naciśnięty prawy przycisk myszy, przeciągnąć kursor o około 100 wierszy. Później trzeba zwolnić przycisk myszy, z menu kontekstowego wybrać polecenie Seria danych…, w polu Wartość kroku wpisać 25, a w polu Wartość końcowa wpisać 2500. Na koniec trzeba kliknąć przycisk OK. Sposoby na wbudowane mechanizmy Excela | 67 SPOSÓB 18. Sterowanie formatowaniem warunkowym przy użyciu pól wyboru W kolejnym kroku na arkuszu PolaWyboru należy zaznaczyć komórkę B1 i nadać jej nazwę PierwszaLiczba, po czym zaznaczyć komórkę D1 i nazwać ją DrugaLiczba. W komórce C1 natomiast trzeba wpisać wartość ORAZ. Następnie należy zaznaczyć komórkę B1 (Pierw- szaLiczba) i trzymając wciśnięty klawisz Ctrl, zaznaczyć komórkę D1 (DrugaLiczba), po czym wybrać polecenie Dane→Narzędzia danych→Poprawność danych (w wersjach wcze- śniejszych jest to polecenie Dane→Sprawdzanie poprawności…→Ustawienia) i w polu Do- zwolone: wybrać pozycję Lista, a w polu Źródło: wpisać =Liczby. Po upewnieniu się, że pole wyboru Rozwinięcia w komórce jest zaznaczone, należy kliknąć przycisk OK. Dzięki tej operacji w obu komórkach pojawią się listy rozwijane zawierające liczby od 25 do 2500. W komórce A1 należy wpisać nagłówek Wartość, a w znajdującym się zaraz pod nią za- kresie A2:A20 wpisać dowolne liczby należące do przedziału od 25 do 2500. Następnie należy zaznaczyć zakres A2:A20 (upewniając się, że zaznaczenie rozpoczyna się od komórki A2, która powinna być również komórką aktywną zaznaczenia) i wybrać polecenie Narzędzia główne→Style→Formatowanie warunkowe→Nowa reguła… (w starszych wersjach należy wybrać polecenie Format→Formatowanie warunkowe…). W wyświetlonym oknie dialogowym przedstawionym na rysunku 2.4 trzeba wskazać opcję Użyj formuły do określenia komórek, które należy sformatować (w wersjach wcześniej- szych jest to opcja Formuła jest; na początku opcją aktywną będzie Wartość komórki jest). Rysunek 2.4. Nowe okno dialogowe Formatowanie warunkowe Następnie w polu Formatuj wartości, dla których następująca formuła jest prawdziwa (w wer- sjach wcześniejszych jest to pole formuły) trzeba wpisać następującą formułę: =ORAZ($A2 =PierwszaLiczba;$A2 =DrugaLiczba;ŁączePolaWyboru) W oknie dialogowym Formatowanie komórek wyświetlonym po kliknięciu przycisku For- matuj… należy zdefiniować pożądany format lub ich kombinację. Następnie trzeba kliknąć przycisk OK, po czym kliknąć OK jeszcze raz w celu zamknięcia okien dialogowych. Kolor czcionki w komórce ŁączePolaWyboru (E1) należy zmienić na biały, by wartości PRAWDA i FAŁSZ nie były widoczne. W komórce PierwszaLiczba (B1) należy wybrać dowolną liczbę, a w komórce DrugaLiczba (D1) — dowolną liczbę większą od pierwszej. 68 | Sposoby na wbudowane mechanizmy Excela Odnajdywanie formuł przy użyciu formatowania warunkowego SPOSÓB 19. Po zaznaczeniu pola wyboru zdefiniowane wcześniej formatowanie warunkowe zostanie automatycznie zastosowane względem liczb, które będą należeć do wskazanego przedziału. Usunięcie zaznaczenia pola wyboru spowoduje przywrócenie formatowania domyślnego. Jak widać, odpowiednio wykorzystując pole wyboru i formatowanie warunkowe, można wykonać operacje, które zdaniem większości użytkowników są możliwe do przeprowa- dzenia jedynie przy użyciu kodu VBA. Odnajdywanie formuł przy użyciu formatowania warunkowego O tym, czy dana komórka zawiera wartość statyczną, czy też wartość ta jest wynikiem formuły, można przekonać się, jedynie klikając w tej komórce i sprawdzając jej zawartość na pasku formuł lub klikając klawisze Ctrl+~ (znak tyldy). Niniejszy sposób wypełnia tę lukę dzięki odpowiedniej funkcji. S PO SÓB19. Wszystkie Kod VBA zawarty w tej funkcji (zwanej również funkcją zdefiniowaną przez użytkownika) pozwala na odszukanie komórek zawierających formuły bez konieczności klikania po kolei 10 000 komórek i sprawdzania zawartości każdej z nich. Można byłoby wybrać polecenie Formatowanie warunkowe→Nowa reguła… (w wersjach wcześniejszych jest to polecenie Format→Formatowanie warunkowe →Formuła jest…) oraz wykorzystać formułę =KOMÓRKA( Typ ;A1) w sekcji Użyj formuły do określenia komórek, które należy sformatować, ale należy mieć zawsze świadomość, że jest to funkcja zmienna. Oznacza to, że jeżeli użytkownik wykona jakąkolwiek zmianę w danym lub innym skoroszycie, gdy skoroszyt zawierający formatowanie warunkowe jest nadal otwarty, zmusi to wszystkie komórki używające funkcji KOMÓRKA do ponownego przeliczenia. Owe globalne rekalkulacje mogą znacznie wpłynąć na wydajność rozbudowanych skoroszytów. Niniejszy sposób reprezentuje lepsze rozwiązanie. Aby stać się przebiegłym łowcą formuł, należy z karty Deweloper wybrać polecenie Kod→ Visual Basic (w wersjach wcześniejszych jest to polecenie Narzędzia→Makro→Edytor Visual Basic) lub nacisnąć klawisze Alt/Option+F11, po czym wybrać Insert→Module i wpisać następującą funkcję: Function IsFormula(Check_Cell As Range) IsFormula = Check_Cell.HasFormula End Function Po jej wpisaniu należy zamknąć okno (naciskając w tym celu Alt/(cid:97)+Q lub klikając przy- cisk Zamknij na pasku tytułu okna). Od teraz funkcja ta będzie dostępna w każdej komórce każdego arkusza aktywnego skoroszytu, a w celu jej wywołania trzeba będzie wpisać jedynie formułę =IsFormula($A$1). Do funkcji tej można również uzyskać dostęp, wybie- rając polecenie Formuły→Biblioteka funkcji→Wstaw funkcję (wcześniej Wstaw→Funkcja…), w polu kategorii funkcji wskazując wartość Użytkownika i na liście funkcji zaznaczając po- zycję IsFormula. Formuła ta zwróci wartość PRAWDA, jeśli w komórce, do której będzie się odwoływać, będzie znajdować się formuła, a wartość FAŁSZ w przeciwnym razie. Zwracaną wartość logiczną można zastosować wraz z formatowaniem warunkowym w taki sposób, by wszystkie formuły zostały automatycznie wyróżnione przy użyciu wybranego formatu. Sposoby na wbudowane mechanizmy Excela | 69 SPOSÓB 20. Zliczanie lub sumowanie komórek spełniających kryteria formatowania warunkowego Najważniejszą zaletą tej metody jest fakt, że możliwości wyszukiwania formuł w arkuszach będą miały charakter dynamiczny. Oznacza to, że jeśli formuła zostanie w danej komórce wpisana lub z niej usunięta, odpowiednio zmienione zostanie również formatowanie. Poniżej przedstawiamy czynności, które należy w tym celu wykonać. Najpierw trzeba zaznaczyć zakres komórek arkusza, powiedzmy A1:J500, a w później- szym czasie, gdy w arkuszu znajdzie się więcej formuł, odpowiednio go rozszerzyć. Nie należy zaznaczać całego arkusza, ponieważ może to niepotrzebnie zmniejszyć wydajność działania całego skoroszytu. Gdy komórki zostaną zaznaczone, a komórką aktywną zaznaczenia będzie A1, należy wybrać polecenie Narzędzia główne→Formatowanie warunkowe→Nowa reguła…→Użyj for- muły do określenia komórek, które należy sformatować i w polu Formatuj wartości, dla których następująca formuła jest prawdziwa: wprowadzić formułę (w wersjach wcześniejszych należy kliknąć polecenie Format→Formatowanie warunkowe…, zamiast opcji Wartość komórki jest wybrać opcję Formuła jest i w polu formuły wpisać: =IsFormula(A1)). Następnie trzeba kliknąć przycisk Formatuj… i zdefiniować pożądane formatowanie, które ma wskazywać komórki zawierające formuły. Na koniec należy kliknąć przycisk OK i jeszcze raz OK. Czasami po wpisaniu formuły w oknie dialogowym Formatowanie warunkowe i kliknięciu przycisku OK Excel wstawi znaki cudzysłowu wokół formuły. Będzie to znaczyło, że Excel potraktował wpisane wartości jako tekst, a nie jako formułę. W takich przypadkach należy wrócić do okna Formatowanie warunkowe, usunąć znaki cudzysłowu i kliknąć OK. W tym momencie zdefiniowany format powinien zostać zastosowany we wszystkich komórkach arkusza zawierających formuły. Jeśli w którejś z tych komórek zostanie wpisana nowa wartość lub zostanie ona usunięta, formatowanie warunkowe zniknie. Analogicznie, jeśli w dowolnej komórce należącej do wskazanego zakresu wpisana zostanie formuła, komórka ta zostanie wyróżniona. Dzięki temu prostemu sposobowi można znacznie ułatwić sobie pracę z arkuszem, a zwłasz- cza skrócić czas potrzebny na jego utrzymywanie i modyfikowanie. S PO SÓB 20. Wszystkie Zliczanie lub sumowanie komórek spełniających kryteria formatowania warunkowego Gdy na ekranie będą już widoczne wyniki formatowania warunkowego, można pokusić się o napisanie formuł odwołujących się jedynie do tych danych, które zostały warunkowo sformatowane. Excel nie uwzględnia w swoich obliczeniach formatowania warunkowego, można jednak sprawić, że będzie to robił. Użytkownicy Excela często pytają: „W jaki sposób można wykonać obliczenia jedynie na tych komórkach, których tło ma określony kolor?”. Pytanie to słyszymy bardzo często, ponieważ Excel nie udostępnia żadnej standardowej funkcji spełniającej takie zadanie. Można jednak wykorzystać do tego celu własną funkcję, co opisano w części „Zliczanie lub sumowanie komórek z określonym kolorze cieniowania” [Sposób 113.]. 70 | Sposoby na wbudowane mechanizmy Excela Zliczanie lub sumowanie komórek spełniających kryteria formatowania warunkowego SPOSÓB 20. Jedyny kłopot z funkcją zdefiniowaną przez użytkownika polega na tym, że nie potrafi ona rozpoznać formatowania będącego efektem zdefiniowanego formatowania warunko- wego. Jednak jeśli się trochę nad tym zastanowić, można osiągnąć pożądany wynik bez pisania własnej funkcji. Załóżmy, że mamy długą listę liczb, znajdującą się w komórkach $A$2:$A$100. Zasto- sowaliśmy w nich formatowanie warunkowe, dzięki czemu wyróżniane są liczby należące do przedziału od 10 do 20. Teraz należy odnaleźć wartości komórek spełniających zdefi- niowane kryterium oraz obliczyć sumę wartości sformatowanych warunkowo. Nie trze- ba zwracać uwagi na to, jaki konkretnie format warunkowy zdefiniowano, trzeba jednak znać kryteria, na podstawie których komórki były wyróżniane (w naszym przypadku są to komórki z wartościami z przedziału od 10 do 20). Aby dodać do siebie wartości komórek spełniających odpowiednie — ale tylko jedno — kryterium, można użyć funkcji SUMA.JEŻELI. Jeśli konieczne będzie uwzględnienie więcej niż jednego kryterium, trzeba będzie użyć formuły tablicowej. Można użyć następującej formuły: =SUMA(JEŻELI($A$2:$A$100 10;JEŻELI($A$2:$A$100 20;$A$2:$A$100))) Wpisując formuły tablicowe, nie należy naciskać klawisza Enter, lecz klawisze Ctrl+Shift+Enter. Wówczas Excel zamknie definicję formuły w nawiasach klamrowych, a więc będzie ona miała postać: {=SUMA(JEŻELI($A$2:$A$100 10;JEŻELI($A$2:$A$100 20;$A$2:$A$100)))} Jeśli nawiasy klamrowe wpisze się samemu, formuła nie zadziała — czynność tę musi wykonać sam Excel. Trzeba również zaznaczyć, że używanie formuł tablicowych, posiadających zbyt wiele odwołań do dużych zakresów komórek, może znacznie zmniejszyć tempo obliczeń wykonywanych przez Excela. Więcej informacji na temat formuł tablicowych można znaleźć na stronie WWW pod adresem http://www.ozgrid.com/Excel/arrays.htm. Rozwiązanie alternatywne Inne możliwe rozwiązanie polega na użyciu dodatkowej kolumny (na przykład kolumny B) odwołującej się do komórek w kolumnie A. Odwołanie to powinno zwracać w kolumnie B odpowiedni wynik tylko wówczas, gdy wartość będzie spełniać zdefiniowane kryterium, na przykład 10, 20. W tym celu należy wykonać czynności opisane poniżej. Najpierw trzeba zaznaczyć komórkę B1 i wpisać w niej następującą formułę: =JEŻELI(ORAZ(A2 10;A2 20);A2; ) Formułą tą należy wypełnić wszystkie komórki aż do B100. Po wpisaniu wartości w kolum- nie B powinny pojawić się wartości należące do przedziału od 10 do 20. Sposoby na wbudowane mechanizmy Excela | 71 SPOSÓB 21. Wyróżnianie co drugiego wiersza lub kolumny Aby szybko skopiować formułę aż do ostatniego używanego wiersza kolumny sąsiedniej, należy wpisać formułę w pierwszej komórce (B2), ponownie ją zaznaczyć i dwukrotnie kliknąć uchwyt wypełnienia. Ten sam efekt można osiągnąć, przechodząc na kartę Narzędzia główne i wybierając polecenie Edycja→Wypełnienie→W dół (w starszych wersjach Edycja→Wypełnij→W dół). Teraz można już zaznaczyć dowolną komórkę, w której powinna się pojawić wartość sumy, i użyć w niej standardowej funkcji SUMA, która tę wartość obliczy. (W razie potrzeby można ukryć kolumnę B, by dodatkowa kolumna, pełna wartości zwróconych przez formułę, stała się niewidoczna). Przedstawione metody pozwolą w pełni zrealizować postawione zadanie, jednak Excel udostępnia jeszcze jedną funkcję umożliwiającą zdefiniowanie dwóch lub więcej kryte- riów. Funkcja ta należy do kategorii Bazy danych i nosi nazwę BD.SUMA. Aby sprawdzić jej działanie, ponownie wykorzystamy ten sam zbiór liczb z komórek A2:A100. Najpierw należy zaznaczyć zakres komórek C1:D2 i nadać mu nazwę KryteriumSumy, wpisując ją w polu nazwy z lewej strony paska formuły. Następnie trzeba zaznaczyć komórkę C1 i wpi- sać =$A$1, czyli odwołanie do pierwszej komórki arkusza. Formułę tę należy skopiować również do komórki D1 — w efekcie powinny powstać dwie kopie nagłówka kolumny A. Kopie te zostaną użyte jako nagłówki kryterium dla funkcji BD.SUMA (C1:D2), które nazwa- liśmy KryteriumSumy. W komórce C2 trzeba wpisać 10, a w komórce D2 20. Natomiast w komórce, w której ma być widoczny wynik, należy wpisać następującą formułę: =BD.SUMA($A$1:$A$100;$A$1;KryteriumSumy) Użycie funkcji BD.SUMA to zalecana i najbardziej wydajna metoda pracy z komórkami spełniającymi określone kryteria. W odróżnieniu od tablic, wbudowane funkcje bazy danych zostały zaprojektowane właśnie do takich celów. Dzięki temu nawet gdy odwołują się one do naprawdę dużych zakresów komórek i w arkuszu znajduje się ich znaczna liczba, powodowany przez nie spadek prędkości i wydajności obliczeń jest niewielki w porów- naniu z negatywnymi efektami używania formuł tablicowych. S PO SÓB 21. Wszystkie Wyróżnianie co drugiego wiersza lub kolumny Często widzi się arkusze Excela, w których wiersze są na przemian w dwóch kolorach: na przykład wiersze o numerach nieparzystych są białe, a wiersze o numerach parzystych szare. Efekt taki osiąga się dzięki formatowaniu warunkowemu. Stosowane na przemian dwa kolory lub cieniowanie nadaje arkuszom profesjonalny wygląd i sprawia, że dane są bardziej czytelne. Formatowanie takie można ustawić ręcznie, lecz nietrudno zgadnąć — a niektórzy Czytelnicy zapewne sami się o tym przekonali — że zadanie to jest czasochłonne, a sam arkusz wymaga ciągłego uaktualniania w miarę do- dawania i usuwania wierszy z tabeli. Oczywiście trzeba się przy tym wykazać również anielską cierpliwością. Na szczęście dzięki formatowaniu warunkowemu można uzyskać profesjonalny wygląd arkusza bez nadwerężania własnej cierpliwości. 72 | Sposoby na wbudowane mechanizmy Excela SPOSÓB 21. Wyróżnianie co drugiego wiersza lub kolumny Załóżmy, że dane zapisane są komórkach z zakresu A1:H100. Komórki te należy zazna- czyć, rozpoczynając od komórki A1, by była ona aktywną komórką zaznaczenia. Następnie trzeba wybrać polecenie Narzędzia główne→Formatowanie warunkowe→Nowa reguła…→Użyj formuły do określenia komórek, które należy sformatować, a następnie w polu Formatuj warto- ści, dla których następująca formuła jest prawdziwa wpisać poniższą formułę (w starszych wersjach należy wybrać polecenie Format→Formatowanie warunkowe… i na liście rozwijanej, której domyślną wartością jest Wartość komórki jest, wybrać pozycję Formuła jest, a następnie w polu formuły wpisać poniższą formułę), widoczną również na rysunku 2.5: =MOD(WIERSZ();2) Rysunek 2.5. Nowe okno dialogowe Formatowanie warunkowe z wpisaną formułą MOD, służącą do formatowania co drugiego wiersza z zakresu komórek W kolejnym kroku należy kliknąć przycisk Formatuj… i wybrać format, jaki ma zostać zastosowany w co drugim wierszu, po czym kliknąć OK i jeszcze raz OK. Zdefiniowany format powinien zostać zastosowany w co drugim wierszu z zakresu A1:H100, a użyt- kownik nie będzie musiał wykazywać się anielską cierpliwością. Jeśli odpowiedni format trzeba zastosować w co drugiej kolumnie, a nie wierszu, należy użyć następującej formuły: =MOD(NR.KOLUMNY();2) Metoda ta pozwala na szybkie i łatwe zastosowanie wybranego formatu w co drugim wierszu lub kolumnie, lecz nie jest ona metodą dynamiczną — wiersze, które nie będą zawierać danych, również będą formatowane. Arkusz będzie przez to wyglądał niepo- rządnie, a jego czytanie będzie utrudnione. Aby wyróżnianie co drugiego wiersza lub kolumny przebiegało w sposób dynamiczny, trzeba zastosować nieco bardziej wyrafi- nowane rozwiązanie. Sposoby na wbudowane mechanizmy Excela | 73 SPOSÓB 22. Tworzenie efektów 3D w tabelach lub komórkach Wyróżnianie dynamiczne Ponownie należy zaznaczyć zakres A1:H100, upewniając się, że A1 będzie komórką aktyw- ną. Następnie należy wybrać polecenie Narzędzia główne→Formatowanie warunkowe→Nowa reguła…→ Użyj formuły do określenia komórek, które należy sformatować, a następnie w polu Formatuj wartości, dla których następująca formuła jest prawdziwa (w starszych wersjach należy wybrać polecenie Format→Formatowanie warunkowe… i na liście rozwijanej, której domyślną wartością jest Wartość komórki jest, wybrać pozycję Formuła jest) wpisać poniższą formułę: =ORAZ(MOD(WIERSZ();2);ILE.NIEPUSTYCH($A1:$H1)) Należy zwrócić uwagę, że w formule tej odwołanie do kolumn ma charakter bezwzględny (o czym decyduje znak dolara), natomiast odwołanie do wierszy jest względne. Następnie należy kliknąć przycisk Formatuj… i wskazać pożądane formatowanie, po czym kliknąć przycisk OK i jeszcze raz OK. W wierszach z zakresu A1:H100, w których nie ma danych, formatowanie warunkowe nie zostanie zastosowane. Jeśli z któregoś wiersza dane zostaną usunięte, wiersz ten również nie będzie podlegał formatowaniu. Jeśli do którejś z komórek z zakresu zostanie wpisana jakaś wartość, zostanie ona uwzględniona w for- matowaniu warunkowym. Rozwiązanie to działa, ponieważ formuła formatowania warunkowego musi zwrócić war- tość PRAWDA lub FAŁSZ. W języku formuł Excela 0 jest równoznaczne z wartością logiczną FAŁSZ, natomiast każda wartość większa od zera ma wartość logiczną PRAWDA. Zatem formuła =MOD(WIERSZ();2) zwróci wartość 0 (FAŁSZ) lub liczbę większą od zera. Funkcja WIERSZ jest funkcją zmienną, która zawsze zwraca numer wiersza komórki, w której ją wpisano. Funkcja MOD natomiast zwraca resztę z dzielenia pierwszej liczby przez drugą. W formule użytej w naszym sposobie dzielimy numer wiersza przez 2, dzięki czemu we wszystkich wierszach o numerach parzystych formuła zwróci wartość 0, a w wierszach o numerach nieparzystych — zawsze wartość większą od zera. Zagnieżdżenie funkcji WIERSZ() i ILE.NIEPUSTYCH w funkcji ORAZ powoduje, że ta ostat- nia zwróci wartość PRAWDA jedynie wówczas, gdy obie funkcje MOD i ILE.NIEPUSTYCH zwrócą wartość PRAWDA lub dowolną liczbę większą od zera. ILE.NIEPUSTYCH zwraca liczbę komórek niepustych. S PO SÓB22. Wszystkie Tworzenie efektów 3D w tabelach lub komórkach Gdy w programach lub aplikacjach takich jak Excel spotyka się przyciągające uwagę efekty trójwymiarowości, tak naprawdę widzi się jedynie złudzenie wywołane odpowiednio zastosowanym formatowaniem. Łatwo jest samemu stworzyć takie złudzenie, stosując odpowiednie formatowanie w komórce lub zakresie komórek. Wraz z wypuszczeniem na rynek Excela 2007 dostępne stały się style komórek, dzięki czemu można stworzyć efekty 3D i zapisać je do późniejszego wykorzystania. Na początek zajmiemy się prostym przykładem, w którym w komórce zastosujemy efekt trójwymiarowości. Dzięki niemu komórka będzie wyglądać, jakby była nieco podniesiona — jak przycisk. W pustym arkuszu należy zaznaczyć komórkę D5 (zaznaczamy komórkę D5, ponieważ nie leży ona na krawędzi arkusza) i z grupy opcji Komórki znajdującej się 74 | Sposoby na wbudowane mechanizmy Excela Tworzenie efektów 3D w tabelach lub komórkach SPOSÓB 22. na karcie Narzędzia główne wybrać polecenie Format→Formatuj komórki…→Obramowanie (w starszych wersjach jest to polecenie Format→Komórki…→ Obramowanie). W polu Linia należy wskazać drugą najgrubszą linię i upewnić się, że jej kolorem jest czarny (lub Automatyczny, jeśli nie zmieniono domyślnej wartości). Następnie trzeba kliknąć obramo- wanie z prawej strony oraz obramowanie dolne. W polu Linia trzeba teraz ustawić kolor linii na biały, pozostawić styl drugiej najgrubszej linii i kliknąć pozostałe dwa obramowania komórki: obramowanie górne i lewostronne. W oknie dialogowym Formatowanie komórek trzeba następnie kliknąć zakładkę Wypełnienie (wcześniej Desenie) i jako kolor cieniowania komórki wskazać kolor szary. W kolejnym kroku należy kliknąć OK i zaznaczyć komórkę inną niż D5. Komórka D5 będzie teraz podniesiona, dzięki czemu będzie przypominać przycisk. A wszystko to jedynie dzięki obramowaniom i cieniowaniu. Jeżeli dla zabawy lub zwiększenia różnorodności komórka ma wyglądać, jakby była wci- śnięta lub wcięta, należy zaznaczyć komórkę E5 (ponieważ jest to następna komórka po D5, a my wykonujemy następne ćwiczenie), wybrać polecenie Narzędzia główne→Komórki→ Format→Formatuj komórki…→Obramowanie (w starszych wersjach należy kliknąć polecenie Format→Komórki…→ Obramowanie), na liście stylów linii zaznaczyć drugie najgrubsze obramowanie i upewnić się, że jego kolorem jest czarny. Formatowanie to należy zastosować do górnego i lewostronnego obramowania komórki. Następnie należy wybrać kolor biały i białą linią obramować komórkę z prawej strony i u dołu, po czym kliknąć zakładkę Desenie i zmienić format komórki na szary. Na koniec należy kliknąć OK — komórka E5 powinna teraz wyglądać, jakby była wciśnięta. Wrażenie to jest jeszcze mocniejsze dzięki sąsiadującej z nią komórce D5, w której zastosowaliśmy efekt podniesienia. Jeżeli jest się zadowolonym ze stylu, jaki się stworzyło, należy wybrać polecenie Narzędzia główne→Style→Style komórki→Nowy styl komórki…, podać nazwę nowego stylu oraz kliknąć przycisk OK. Należy pamiętać, że style komórek zapisywane są w bieżącym skoroszycie, ale można uwzględniać style z innych skoroszytów. Opcja ta nie jest dostępna w wersjach wcześniejszych niż Excel 2007. Aby zachować style w starszych wersjach Excela, należy użyć polecenia Format→Style. Efekt 3D w tabeli danych W kolejnym kroku wykorzystamy to samo rozwiązanie, aby pokazać kilka efektów, które można zastosować w tabelach lub arkuszach w celu nadania im wrażenia trójwymiaro- wości. Najpierw należy zaznaczyć komórki D5 i E5 i na karcie Narzędzia główne w grupie opcji Schowek (w starszych wersjach na standardowym pasku narzędzi) kliknąć ikonę Malarz formatów (ikonę pędzla). Następnie trzeba kliknąć lewym przyciskiem myszy na komór- ce F5 i przy wciśniętym przycisku przeciągnąć zaznaczenie aż do komórki J5, po czym zwolnić przycisk. Następnie trzeba zaznaczyć komórki D5:J5 i ponownie na standardowym pasku narzę- dzi kliknąć ikonę Malarz formatów, po czym zaznaczyć komórkę D6 poprzez wciśnięcie Sposoby na wbudowane mechanizmy Excela | 75 SPOSÓB 23. Włączanie i wyłączanie formatowania warunkowego i sprawdzania poprawności danych przy użyciu pola wyboru lewego przycisku myszy, przeciągnąć zaznaczenie aż do komórki J15 i zwolnić przy- cisk. W efekcie powinniśmy otrzymać efekt przedstawiony na rysunku 2.6. Rysunek 2.6. Efekt 3D zastosowany w zakresie komórek Aby zachować stworzony styl tabeli, należy wybrać polecenie Narzędzia główne→Style→Formatuj jako tabelę→Nowy styl tabeli.... W starszych wersjach Excela (przed wersją 2007) opcja ta nie jest dostępna. Aby zwiększyć wyrazistość efektu trójwymiarowości, zastosowaliśmy dość grube obra- mowanie, można jednak trochę ten efekt złagodzić, wybierając styl cieńszej linii. Można również wybrać jeden z pozostałych stylów linii, aby osiągnąć jeszcze ciekawszy efekt. Najlepsze rozwiązanie znajduje się metodą prób i błędów, wypróbowując w pustym arku- szu kolejne kombinacje, aż do osiągnięcia pożądanego efektu. Ograniczeniem jest jedynie wyobraźnia i gust. Efekty trójwymiarowości zwiększają czytelność arkuszy i nadają im profesjonalny wygląd, lecz zawsze należy pamiętać, że nadużywanie tego efektu może przynieść skutek odwrotny od zamierzonego — wszak wszystko trzeba robić z umiarem. Aby pójść jeszcze krok dalej i stosować efekt trójwymiarowości w sposób automatyczny i dynamiczny, można połączyć efekt 3D i formatowanie warunkowe. Dzięki temu forma- towanie będzie przebiegać automatycznie przy użyciu zdefiniowanego stylu. Włączanie i wyłączanie formatowania warunkowego i sprawdzania poprawności danych przy użyciu pola wyboru Sprawdzanie poprawności danych znacznie zmniejsza prawdopodobieństwo, że któryś z użytkowników przypadkowo wpisze nieprawidłowe dane. Czasami jednak zachodzi potrzeba ułatwienia wpisywania danych, które w innej sytuacji byłyby oznaczane jako nieprawidłowe przez formatowanie warunkowe lub których wpisanie byłoby zablokowane przez mechanizm sprawdzania poprawności. S PO SÓB23. Wszystkie Pierwsze przychodzące na myśl rozwiązanie mające na celu umożliwienie użytkownikom wpisywania danych, które w innej sytuacji byłyby oznaczone jako niepoprawne, to usunię- cie z komórek formatowania warunkowego i (lub) mechanizmu sprawdzania poprawności 76 | Sposoby na wbudowane mechanizmy Excela Włączanie i wyłączanie formatowania warunkowego i sprawdzania poprawności danych przy użyciu pola wyboru SPOSÓB 23. danych. Istnieje jednak prostszy sposób: z mechanizmem sprawdzania poprawności danych można połączyć zwykłe pole wyboru z paska narzędzi Formularze. W tym przykładzie w zakresie komórek tak zdefiniujemy formatowanie warunkowe, aby wartości występujące więcej niż jeden raz były wyróżniane i łatwo było je odszukać. Zakła- damy, że tabela danych zajmuje zakres komórek $A$1:$H$100. Aby warunkowo sforma- tować ten zakres danych w taki sposób, by wartości powtarzające się były wyróżniane, trzeba wykonać kilka czynności. Najpierw należy zaznaczyć komórkę K1 i nadać jej nazwę ŁączePolaWyboru, wpisując ją w polu Nazwa znajdującym się z lewej strony paska formuł i naciskając klawisz Enter. Następnie należy kliknąć w komórkę I3 i wybrać polecenie Deweloper→Wstaw→Formanty formularza→Pole wyboru (w starszych wersjach, jeśli pasek narzędzi Formularze nie jest jeszcze widoczny, należy kliknąć prawym przyciskiem myszy na dowolnym pasku na- rzędzi i wybrać Formularze, a następnie wybrać z niego ikonę Pole wyboru). Później trzeba kliknąć w arkuszu w którejś z komórek, która nie należy do zakresu A1:H100, i umieścić w niej pole wyboru. W kolejnym kroku trzeba kliknąć na polu wyboru prawym przyciskiem myszy i wybrać polecenie Formatuj formant…→Formant, po czym w polu Łącze komórki wpisać nazwę ŁączePolaWyboru i kliknąć OK. Następnie należy zaznaczyć komórkę A1 i przeciągnąć myszą zaznaczenie do komórki H100. Ważne jest, by komórka A1 była aktywną komórką zaznaczenia. Należy wybrać polecenie Narzędzia główne→Formatowanie warunkowe→ Nowa reguła…→Użyj formuły do określenia komórek, które należy sformatować i w polu For- matuj wartości, dla których następująca formuła jest prawdziwa (we wcześniejszych wersjach należy wybrać polecenie Format→Formatowanie warunkowe… i w polu o wartości Wartość komórki jest wybrać pozycję Formuła jest) wpisać następującą formułę (widoczną również na rysunku 2.7): =ORAZ(LICZ.JEŻELI($A$1:$H:$100;A1) 1;ŁączePolaWyboru) Rysunek 2.7. Okno dialogowe Formatowanie warunkowe z wpisaną formułą warunkowego formatowania komórek zawierających powtarzające się dane Sposoby na wbudowane mechanizmy Excela | 77 SPOSÓB 24. Obsługa kilku list przy użyciu pola kombi Następnie trzeba kliknąć przycisk Formatuj… i na zakładce Desenie wskazać kolor, jakim mają być wyróżniane dane powtarzające się. Na koniec należy kliknąć przycisk OK i jesz- cze raz OK. Jeśli pole wyboru umieszczone w arkuszu będzie zaznaczone, wartością łącza komórki K1 (ŁączePolaWyboru) będzie PRAWDA i wszystkie wartości powtarzające się w zakresie $A$1:$H$100 zostaną wyróżnione. Po usunięciu zaznaczenia pola wyboru łącze komórki (ŁączePolaWyboru) będzie miało wartość FAŁSZ i duplikaty nie będą wyróżniane. Pole wyboru można przełączać, dzięki czemu możemy przy jego użyciu włączać i wyłączać w arkuszu formatowanie warunkowe bez konieczności każdorazowego odwoływania się do okna dialogowego Formatowanie warunkowe. Tę samą zasadę można zastosować w przy- padku sprawdzania poprawności danych, również używając formuły. Sposób ten działa dzięki zastosowaniu funkcji ORAZ. W funkcji tej muszą zostać spełnione dwa warunki: funkcja LICZ.JEŻELI($A$1:$H$100;A1) 1 musi mieć wartość PRAWDA oraz łącze komórki pola wyboru (ŁączePolaWyboru) również musi mieć wartość PRAWDA. Inaczej mówiąc, funkcja ORAZ zwróci wartość PRAWDA, jeśli obydwa warunki będą miały wartość PRAWDA. S PO SÓB 24. Wszystkie Obsługa kilku list przy użyciu pola kombi W trakcie pracy z kilkoma listami można wymuszać ich przełączanie, używając do tego przycisków opcji oraz pola kombi. Excel udostępnia użytkownikom szereg sposobów wybierania pozycji z listy na przykład nazw, produktów, dni tygodnia — zależnie od tego, czego lista dotyczy. Aby jednak uzy- skać dostęp jednocześnie do więcej niż jednej listy, trzeba czasem użyć trzech oddzielnych kontrolek, na przykład pól kombi z grupy narzędzi Formanty formularza (w starszych wersjach z paska narzędzi Formularze). Istnieje jednak inna możliwość: można użyć pola kombi w połączeniu z przyciskami opcji (nazywanymi również przełącznikami i również dostępnymi na pasku narzędzi Formu- larze) w taki sposób, by lista zmieniała się zgodnie z wybranym przyciskiem opcji. Aby zobaczyć, jak to działa, należy najpierw w nowym arkuszu, w komórkach z zakresu A1:A7 wpisać liczby od 1 do 7, w komórkach B1:B7 wpisać dni tygodnia, na pierwszym miejscu umieszczając Poniedziałek, a na ostatnim wpisując Niedziela, a w komórkach C1:C7 wpisać nazwy miesięcy z zakresu Styczeń–Lipiec. Wykonanie tego zadania znacznie ułatwią narzędzia autowypełniania znajdujące się w Excelu. Najpierw w komórce A1 należy wpisać 1, zaznaczyć tę komórkę, nacisnąć klawisz Ctrl ((cid:97) na komputerach Macintosh) i lewym przyciskiem myszy kliknąć uchwyt wypełniania. Następnie, trzymając wciśnięte jednocześnie klawisz Ctrl i lewy przycisk myszy, trzeba przeciągnąć myszą do wiersza numer 7. Excel sam wówczas wpisze odpowiednie liczby. Następnie w komórce B1 należy wpisać Poniedziałek i dwukrotnie kliknąć w niej uchwyt wypełnienia, po czym w komórce C1 trzeba wpisać Styczeń i znów dwukrotnie kliknąć w niej uchwyt wypełnienia. Excel samodzielnie wpisze pozostałe nazwy dni tygodnia i miesięcy. 78 | Sposoby na wbudowane mechanizmy Excela Obsługa kilku list przy użyciu pola kombi SPOSÓB 24. W dalszej kolejności trzeba wybrać polecenie Deweloper→Formanty→Wstaw→Formanty formularza (w starszych wersjach należy kliknąć polecenie Widok→Paski narzędzi→Formularze) i kliknąć przycisk opcji, po czym kliknąć w trzech dowolnych miejscach arkusza, umiesz- czając w nich trzy przyciski opcji. Następnie należy postępować w ten sam sposób, ale tym razem w grupie Formanty formula- rza trzeba kliknąć pole kombi i ponownie kliknąć myszą w dowolnym miejscu arkusza, aby umieścić w nim to pole. Używając uchwytów przeciągania, można odpowiednio ustawić rozmiar pola kombi, a następnie bezpośrednio pod nim trzeba umieścić przyciski opcji. W kolejnym kroku trzeba kliknąć prawym przyciskiem myszy na pierwszym przycisku opcji, wybrać polecenie Edytuj tekst i w miejsce tekstu Przycisk opcji 1 wpisać słowo Liczby. Tę samą czynność należy wykonać dla Przycisku opcji 2, nadając mu nazwę Dni tygodnia oraz dla Pola opcji 3, nadaj
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

100 sposobów na Excel 2007 PL. Tworzenie funkcjonalnych arkuszy
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ą: