Godziny pracy: Ukryte skarby w programie Excel 2007

Jean Philippe Bagel 1 października 2007

Jean Philippe Bagel

Bywa, że miłość od pierwszego wejrzenia trwa latami. W tym tygodniu nasz felietonista przedstawi kilka ciekawych zastosowań programu Excel. Chyba nikt nie kocha tego programu równie mocno jak Jean Philippe, dlatego zachęcamy do uważnego przeczytania tego, co ma do powiedzenia. Dzięki jego poradom można podnieść swoją wydajność, dobrze się przy tym bawiąc.

Dotyczy programu
Microsoft Office Excel 2007


Pracując przez wiele lat dla firmy Microsoft, poznałem liczne niesamowite zastosowania dla naszych produktów i de facto stałem się głównym źródłem pomocy technicznej dla mojej rodziny i przyjaciół. Ten felieton jest dla mnie okazją, aby przedstawić szerszej grupie odbiorców kilka opracowanych przeze mnie rozwiązań i opisać niektóre funkcje różnych programów pakietu Microsoft Office System, które poznałem dzięki innym ludziom i które uważam za ukryte skarby.

Swoją karierę w firmie Microsoft zaczynałem jako tłumacz zajmujący się lokalizacją programu Excel. Wykonując moją obecną pracę, spędzam wiele czasu z tą aplikacją, więc cieszę się, że mam szansę podzielić się moimi poradami na temat programu Excel. Dzisiaj pokażę szybkie poruszanie się po skoroszycie programu Excel, wyświetlanie zestawu ikon dla warunku Prawda lub Fałsz oraz grupowanie dat w raporcie tabeli przestawnej.

Tak wiele arkuszy, tak mało miejsca

Czy kiedykolwiek zdarzyło Ci się pracować ze skoroszytem programu Excel zawierającym wiele arkuszy, których nazwy były inne niż tradycyjne Arkusz1, Arkusz2 i Arkusz3? W firmie mojej żony często można spotkać się z taką sytuacją — dane biznesowe są umieszczane w skoroszytach, których karty odpowiadają miesiącom od stycznia do grudnia:

Karty w programie Excel 2007

W takim przypadku szybko dochodzi się do punktu, w którym nie można zobaczyć wszystkich kart naraz. Oczywiście można skorzystać z wbudowanych przycisków służących do nawigacji między kartami, aby przejść do następnego, poprzedniego, pierwszego lub ostatniego arkusza. Ale czy nie byłoby wspaniale, gdyby można było wyświetlać wszystkie arkusze i wybierać te, nad którymi chce się pracować, klikając je? Taka możliwość istnieje bodaj już od czasów programu Excel 4. Wystarczy kliknąć prawym przyciskiem myszy przyciski nawigacyjne. Zostanie wówczas wyświetlona przestawna lista wszystkich arkuszy w skoroszycie (jak pokazano na poniższej ilustracji). Ta metoda jest szczególnie przydatna w przypadku arkuszy o długich nazwach. Korzystam z niej cały czas i uważam, że jest to najlepszy sposób nawigowania w złożonych arkuszach kalkulacyjnych. Przy okazji warto wspomnieć o dwóch wspaniałych skrótach klawiaturowych: kombinacji CTRL+PageUp, która uaktywnia poprzedni arkusz w skoroszycie, i kombinacji CTRL+PageDown, która uaktywnia następny arkusz.

Przestawna lista arkuszy w programie Excel 2007

Łatwe i automatyczne kontrolowanie listy

Za następną cenną poradę chciałbym podziękować mojemu koledze Mike'owi, który opracował ją kilka dni temu. Pomyślałem, że warto zamieścić tutaj jego pomysł. Jak często zdarza Ci się zapisywać w arkuszach różne „rzeczy”? Przyjmijmy bardzo proste założenie, że chcemy potwierdzić chęć przybycia poszczególnych gości zaproszonych na przyjęcie i przy każdej osobie, która zdecyduje się przyjść, stawiamy znak „X” (zgodnie z poniższą ilustracją po lewej stronie). Program Excel 2007 oferuje nowe, ciekawe reguły formatowania warunkowego, dzięki którym można automatycznie wyświetlić zestawy ładnie sformatowanych ikon. Czy nasza poprzednia lista nie prezentowałaby się lepiej, gdyby wyglądała jak ta na ilustracji z prawej strony?

Lista uczestników Formatowanie warunkowe w programie Excel 2007

Prześledźmy sposób uzyskania takiego efektu. Po pierwsze należy pamiętać, że program Excel jest arkuszem kalkulacyjnym, więc jego głównym przeznaczeniem jest obsługa liczb. Z tego powodu obecność danej osoby należy przedstawić raczej za pomocą pewnej wartości, a nie tekstu. Nie jest to takie skomplikowane. Załóżmy, że osoba, która przyjdzie na przyjęcie, będzie reprezentowana przez liczbę 1 (a nie znak „X”) w kolumnie „Weźmie udział”. Osoba, która nie przyjdzie, będzie reprezentowana przez liczbę 0. Proste, prawda?

A teraz wykonaj pięć prostych kroków opisanych poniżej, aby przejść do sedna sprawy:

  1. Zaznacz zakres wartości, które będą reprezentowane (w naszym przypadku komórki od B2 do B8).
  2. Kliknij polecenie Formatowanie warunkowe na karcie Narzędzia główne, wybierz polecenie Zestawy ikon, a następnie wybierz pozycję 3 Symbole (niezakreślone) w galerii opcji. Te symbole to zielone pole wyboru, żółty wykrzyknik i czerwony znak „X”. Na tym etapie w arkuszu są reprezentowane zarówno wartości (0 lub 1), jaki i ikona.
  3. Kliknij ponownie polecenie Formatowanie warunkowe, a następnie kliknij polecenie Zarządzaj regułami.
  4. Kliknij przycisk Edytuj regułę.
  5. Zaznacz pole wyboru Pokaż tylko ikonę, kliknij przycisk Zastosuj, a następnie kliknij przycisk OK. Gotowe.

Jeszcze ciekawszą funkcją programu Excel 2007 jest możliwość filtrowania takiej listy według kolorów. Gdy jest aktywna dowolna pozycja listy, można kliknąć kartę Dane, a następnie kliknąć przycisk Filtruj. Jak widać, nagłówek każdej kolumny zawiera teraz strzałkę rozwijaną, która wskazuje, że zawartość kolumny jest filtrowana. Można kliknąć filtr w kolumnie Weźmie udział, a następnie wybrać polecenie Filtruj według koloru i sprawdzić, jak działa filtrowanie według zielonych pól wyboru, wyświetlając tylko te osoby, które przyjdą na przyjęcie.

Przykład formatowania warunkowego

Grupowanie dat w raportach tabeli przestawnej

Ostatnia cenna porada w tym felietonie pozwoli odkryć nowe funkcje raportów tabeli przestawnej, a w szczególności grupowania według dat. Kilka tygodni temu analizowałem pewne dane pomiarowe związane z witryną Office Online na potrzeby tematów Pomocy dla deweloperów. Po upływie dziewięciu miesięcy od momentu uruchomienia tej witryny zbierane dane stają się naprawdę interesujące. Nasz system każdego dnia rejestruje następujące informacje (pokazane na poniższym fragmencie arkusza): temat Pomocy, liczbę odwiedzin danej strony i datę. Oczywiście cały arkusz zawiera dziesiątki tysięcy wierszy obejmujących wszystkie dni od listopada 2006 roku.

Przykład grupowania danych w programie Excel 2007

Moje zadanie polegało na przedstawieniu danych (posortowanych według miesiąca i tematu Pomocy), w przypadku których program Excel podsumował liczbę odwiedzin strony. Udało mi się, jednak najpierw musiałem wynaleźć koło na nowo, dzieląc informacje znajdujące się w kolumnie Data na kolumny Rok i Miesiąc. Mój kolega Dearbhla z Irlandii potwierdził moje przeczucie, że w programie Excel można poradzić sobie z tą operacją znacznie sprawniej. I wcale nie trzeba do tego stosować żadnych nowych funkcji wprowadzonych w programie Excel 2007.

Najpierw utworzyłem prostą tabelę przestawną, klikając przycisk Tabela przestawna w grupie Tabele na karcie Wstawianie. Dzięki temu została wyświetlona pusta tabela przestawna z otwartym okienkiem Lista pól tabeli przestawnej. Z tego miejsca:

  • Przeciągnąłem pole Tytuł i upuściłem na obszar Etykiety wierszy.
  • Przeciągnąłem pole Liczba odwiedzin strony i upuściłem na obszar Wartości (w którym program Excel automatycznie wybrał sumowanie).
  • Przeciągnąłem pole Data upuściłem na obszar Etykiety kolumn.

Jak widać poniżej, na tym etapie dane w tabeli przestawnej zostały posortowane według tematu i dnia. To był krok w dobrym kierunku, ale nie zostały udostępnione prawie żadne dane, na których można wykonywać operacje. W niektóre dni poszczególne tematy Pomocy nie są w ogóle czytane. A w sytuacji, gdy reprezentowany jest każdy dzień, dane są wstawiane aż do kolumny JY. W takim momencie niezwykle przydatne staje się grupowanie według miesiąca.

Przykładowa tabela przestawna w programie Excel 2007

Aby uzyskać logiczne grupowanie według miesiąca, wystarczy umieścić kursor na jednym z dni, uaktywnić kartę Opcje w sekcji Narzędzia tabel przestawnych i wybrać polecenie Grupuj zaznaczenie. W efekcie zostanie otwarte okno dialogowe Grupowanie. Na liście Według należy zaznaczyć pozycję Miesiące i lata, a następnie kliknąć przycisk OK. Jak widać na poniższej ilustracji, tabela przestawna zawiera teraz idealne podsumowanie:

Przykładowa tabela przestawna w programie Excel 2007

O tabelach przestawnych można by napisać jeszcze wiele felietonów, podobnie jak o innych funkcjach programu Excel. Pamiętam bardzo dobrze moje początki pracy z tym programem w wersji 2.1c i pisanie makr XLM. Do dzisiaj program Excel pozostaje moją ulubioną aplikacją. A gdy mam zbyt wiele danych do przeanalizowania, z pomocą zawsze przychodzi program Access.

O autorze

Jean Philippe Bagel jest menedżerem grupy zajmującej się dokumentacją pakietu Office dla deweloperów, która pracuje nad szerokim zakresem zagadnień — od języka VBA po materiały dla deweloperów programu SharePoint. Wcześniej stworzył Centrum deweloperów pakietu Office w witrynie MSDN (w języku angielskim), gdzie wciąż publikujemy wszystkie nasze materiały. Swój wolny czas spędza z żoną i dwójką cudownych dzieci, bawiąc się klockami LEGO, grając na konsoli Xbox (tylko wtedy, gdy dzieci śpią) i czytając bez końca.