Excel dla zaawansowanych

Zaawansowane techniki pracy z Excelem

Microsoft Excel 2002 posiada cały szereg zaawansowanych mechanizmów, dzięki którym jest naprawdę potężnym narzędziem do przetwarzania i analizy danych. W niniejszym rozdziale omówimy kilka najbardziej przydatnych technik, które można wykorzystać w codziennej pracy:

• Etykiety zakresów (rysunek 13.1) — pozwala na używanie łatwych do zapamiętania etykiet komórek i zakresów komórek. Etykiet możesz używać w formułach zamiast odwołań.

• Odwołania 3-W (rysunek 13.2) — pozwalają na tworzenie formuł odwołujących się do innych arkuszy i skoroszytów.

• Konsolidacja danych (rysunek 13.3) — umożliwia zbieranie informacji z wielu różnych obszarów źródłowych i umieszczanie ich w obszarze docelowym.

• Niestandardowe widoki definiowane przez użytkownika — pozwalają na tworzenie predefiniowanych widoków zawartości arkusza.

• Makra — pozwalają na zautomatyzowanie powtarzających się zadań i operacji.

Rady

• Informacje zawarte w niniejszym rozdziale opierają się na wiadomościach przedstawionych w poprzednich rozdziałach. Zanim rozpoczniesz lekturę tego rozdziału powinieneś dobrze przyswoić te umiejętności.

• Excel posiada wbudowany język programowania, Visual Basic, który umożliwia tworzenie różnego rodzaju makr i wysoce wyspecjalizowanych skoroszytów zawierających definiowane przez użytkownika okna dialogowe, paski menu oraz narzędzia. Niestety omówienie tych mechanizmów wykracza daleko poza ramy tej książki.

Rysunek 13.1. Odwołania do poszczególnych zakresów komórek będą o wiele bardziej użyteczne, jeżeli nadasz im łatwe do zapamiętania etykiety, np. Kwartał1 zamiast A54:D59.

Rysunek 13.2. Odwołania 3-W pozwalają na tworzenie formuł odwołujących się do innych arkuszy i skoroszytów.

=SUMA(`Kwartał1:Kwartał4'!E3)

Rysunek 13.3. Konsolidacja danych umożliwia zbieranie informacji z wielu różnych obszarów źródłowych i umieszczanie ich w obszarze docelowym.

Etykiety zakresów

Poważnym problemem przy korzystaniu z odwołań do komórek w formułach są trudne do zapamiętania adresy. Co gorsza, jeżeli jakieś obszary komórek powyżej lub poniżej zakresu źródłowego zostaną usunięte lub wstawione, to odwołania do zakresu źródłowego również ulegną zmianie.

Mechanizm nadawania łatwych do zapamiętania etykiet wybranym zakresom komórek umożliwia wyeliminowanie tego problemu. Po zastosowaniu w formułach etykiet zamiast odwołań bezpośrednio do komórek nie musisz się już o nic martwić — etykieta będzie zawsze reprezentowała poprawny obszar niezależnie od modyfikacji, jakim zostanie poddany arkusz.

Rady

• Excel potrafi automatycznie rozpoznawać nagłówki wielu kolumn i wierszy jako etykiety komórek bądź zakresów komórek. Więcej informacji na ten temat znajdziesz w następnym podrozdziale.

• Nazwy etykiet mogą się składać maksymalnie z 255 znaków i może zawierać litery, cyfry, kropki, znaki zapytania oraz znaki podkreślenia. Nazwa etykiety musi rozpoczynać się od litery. Nazwy nie mogą zawierać spacji ani „wyglądać” jak odwołania do komórek.

• Jeżeli podczas tworzenia formuły użyjesz nieprawidłowej etykiety, to może się wydarzyć jedna z dwóch sytuacji:

• Excel zaoferuje automatyczne poprawienie formuły (rysunek 13.4) — jeżeli tylko Excel będzie w stanie „odgadnąć” jaką nazwę miałeś na myśli.

• W komórce zawierającej niepoprawną formułę pojawi się błąd #NAZWA? (rysunek 13.5).

Rysunek 13.4. Od czasu do czasu Excel może próbować automatycznie naprawić błędnie wprowadzoną formułę.

Rysunek 13.5. Jeżeli użyta w formule etykieta nie jest poprawna, to w komórce pojawi się błąd #NAZWA?.

Rysunek 13.6. Podmenu Nazwa oferuje szereg poleceń do obsługi etykiet.

Aby zdefiniować etykietę zakresu

1. Zaznacz zakres komórek, dla którego chcesz zdefiniować etykietę (rysunek 13.1).

2. Z menu głównego wybierz polecenie Wstaw Nazwa Definiuj (rysunek 13.6).

3. Na ekranie pojawi się okno dialogowe Definiuj nazwy. Excel może zasugerować nazwę, aczkolwiek nic nie stoi na przeszkodzie, abyś wprowadził swoją nazwę (rysunek 13.7).

4. Odwołanie widoczne w polu Odwołuje się do powinno odpowiadać zaznaczonemu wcześniej zakresowi. Aby wprowadzić inne odwołanie usuń istniejący wpis, a następnie wprowadź lub zaznacz nowy zakres.

5. Naciśnij przycisk OK.

Rada

• Aby zdefiniować więcej niż jedną etykietę, wykonaj powyższe polecenia dla pierwszej etykiety a potem w kroku 5 naciśnij przycisk Dodaj. Następnie ponownie wykonaj polecenia z kroków 3 do 5 dla każdej etykiety, którą chcesz zdefiniować. Po zakończeniu naciśnij przycisk OK.

Aby skorzystać z etykiet

Upewnij się, że opcja Akceptuj etykiety w formułach, znajdująca się na zakładce Przeliczanie okna dialogowego Opcje (rysunek 13.8) jest włączona. Więcej informacji na temat opcji Excela znajdziesz w rozdziale 15.

Rady

• Opcja Akceptuj etykiety w formułach jest domyślnie wyłączona.

• Poniżej opisano sposób, w jaki Excel automatycznie korzysta z etykiet do zakresów komórek (rysunek 13.9):

• Aby odwołać się do kolumny, zastosuj etykietę znajdującą się na górze danej kolumny.

• Aby odwołać się do wiersza, zastosuj etykietę znajdującą się w lewej, skrajnej komórce wiersza.

• Aby odwołać się do danej komórki, zastosuj etykietę składającą się z etykiety kolumny i wiersza, na przecięciu których znajduje się ta komórka.

Rysunek 13.7. Aby zdefiniować etykietę skorzystaj z okna dialogowego Definiuj nazwy. Jak widać, nazwa arkusza jest częścią odwołania do zakresu komórek.

Rysunek 13.8. Aby skorzystać z etykiet w formułach, upewnij się, że opcja Akceptuj etykiety w formułach jest włączona.

Rysunek 13.9. Przykłady automatycznego rozpoznawania etykiet zakresów i komórek.

(Jan — Styczeń; Jean — Dawid; MarJoan — MarzecJakub)

Aby utworzyć wiele etykiet naraz

1. Zaznacz zakres komórek, dla których chcesz utworzyć etykiety łącznie z sąsiadującymi z nimi komórkami zawierającymi nazwy etykiet (rysunek 13.10).

2. Z menu głównego wybierz polecenie Wstaw Nazwa Utwórz (rysunek 13.6).

3. Na ekranie pojawi się okno dialogowe Utwórz nazwy (rysunek 13.11). Włącz opcje odpowiadające komórkom, które chcesz wykorzystać jako etykiety.

4. Naciśnij przycisk OK.
   
   Excel użyje nazw zlokalizowanych we wskazanych komórkach jako etykiet dla sąsiadujących komórek. Rezultaty tej operacji możesz obejrzeć po otworzeniu okna dialogowego Definiuj nazwy (rysunek 13.12).

Rada

• Opisana powyżej metoda jest najszybszym sposobem zdefiniowania wielu etykiet jednocześnie.

Aby usunąć etykiety

1. Z menu głównego wybierz polecenie Wstaw Nazwa Definiuj (rysunek 13.6).

2. W liście Nazwy w skoroszycie, znajdującej się w oknie dialogowym Definiuj nazwy (rysunek 13.12), zaznacz etykietę, którą chcesz usunąć.

3. Naciśnij przycisk Usuń. Wybrana etykieta zostanie usunięta z listy.

4. Aby usunąć inne etykiety powtórz operacje z punktów 2 i 3.

5. Po zakończeniu naciśnij przycisk OK.

Rada

• Usuwanie etykiet nie powoduje usunięcia komórek, do których odwołują się usuwane etykiety.

Rysunek 13.10. Aby utworzyć wiele etykiet naraz zaznacz zakres komórek, dla których chcesz utworzyć etykiety, łącznie z sąsiadującymi z nimi komórkami zawierającymi nazwy etykiet.

Rysunek 13.11. W oknie dialogowym Utwórz nazwy włącz opcje odpowiadające komórkom, które chcesz wykorzystać jako etykiety.

Rysunek 13.12. W oknie dialogowym Definiuj nazwy znajdziesz wszystkie zdefiniowane etykiety wraz z odpowiadającymi im odwołaniami.

Aby skorzystać z etykiet w formułach

1. Ustaw wskaźnik aktywnej komórki w komórce, w której chcesz umieścić formułę.

2. Wpisz formułę zamieniając odwołania do komórek na odpowiednie etykiety (rysunek 13.13).

3. Naciśnij klawisz Enter lub naciśnij przycisk Wpis
   znajdujący się na pasku formuły.
   
   Excel wykona niezbędne obliczenia dokładnie tak, jakby w formule zastosowane odwołania do komórek (rysunek 13.14).

Rady

• Podczas tworzenia formuł możesz skorzystać z polecenia Wklej nazwę. Postępuj zgodnie z powyższymi instrukcjami, a kiedy przyjdzie czas na wpisanie etykiety, z menu głównego wybierz polecenie Wstaw Nazwa Wklej (rysunek 13.6). Na ekranie pojawi się okno dialogowe Wklej nazwę (rysunek 13.15) — zaznacz i wklej wybraną etykietę. Z mechanizmu wklejania etykiet możesz korzystać nawet podczas pracy z oknem Wstaw funkcję. Więcej informacji na ten temat znajdziesz w rozdziale 5.

• Jeżeli usuniesz daną etykietę, to Excel we wszystkich komórkach, w których dana etykieta była wykorzystywana, wyświetli błąd #NAZWA? (rysunek 13.16). Wszystkie takie formuły będą musiały być ręcznie poprawione.

Rysunek 13.13. Po zdefiniowaniu etykiety zakresu będziesz mógł jej używać w formułach zamiast odwołania.

Rysunek 13.14. Po zatwierdzeniu formuły, jej rezultat pojawi się w komórce.

Rysunek 13.15. Podczas tworzenia formuł możesz skorzystać z okna dialogowego Wklej nazwę.

Rysunek 13.16. Jeżeli usuniesz daną etykietę, to Excel we wszystkich komórkach, w których dana etykieta była wykorzystywana, wyświetli błąd #NAZWA?.

Aby zastosować etykiety w istniejących formułach

1. Zaznacz komórki zawierające formuły, do których chcesz zastosować etykiety zakresów. Jeżeli chcesz zastosować etykiety w całym arkuszu, zaznacz tylko jedną, dowolną komórkę.

2. Z menu głównego wybierz polecenie Wstaw Nazwa Zastosuj (rysunek 13.6).

3. Na ekranie pojawi się okno dialogowe Zastosuj nazwy (rysunek 13.17). Zaznacz etykiety, które chcesz zastosować w formułach.

4. Naciśnij przycisk OK.
   
   Excel zamieni odwołania na odpowiednie etykiety. Na rysunku 13.18 przedstawiono przykład formuł, w których wprowadzone zostały etykiety Styczeń, Luty oraz Marzec (rysunek 13.17).

Rady

• Jeżeli zaznaczysz tylko jedną komórkę, to Excel zastosuje etykiety zaznaczone w oknie Zastosuj nazwy a nie w zaznaczonej komórce.

• Jeżeli w oknie Zastosuj nazwy wyłączysz opcję Ignoruj względne/bezwzględne (rysunek 13.17), to Excel automatycznie dopasuje typy odwołań. Więcej informacji na temat typów odwołań znajdziesz w rozdziale 3.

Rysunek 13.17. W oknie dialogowym Zastosuj nazwy zaznacz etykiety, które chcesz zastosować w formułach.

Rysunek 13.18. Excel zastosuje wybrane etykiety w formułach zaznaczonego obszaru.

(Cell — Komórka; Before — Przed; After — Po;

B9 =SUMA(B3:B8) =SUMA(Styczeń)

C9 =SUMA(C3:C8) =SUMA(Luty)

D9 =SUMA(D3:D8) =SUMA(Marzec)

E9 =SUMA(E3:E8) =SUMA(Razem) )

Aby zaznaczyć zakres komórek odpowiadający etykiecie

Z pola nazw, znajdującego się po lewej stronie paska formuł wybierz żądaną etykietę (rysunek 13.19).

lub

1. Kliknij w polu nazw znajdującym się po lewej stronie paska formuł.

2. Wpisz nazwę etykiety odpowiadającej wybranemu zakresowi komórek (rysunek 13.20).

3. Naciśnij przycisk Enter.

lub

1. Z menu głównego wybierz polecenie Edycja Przejdź do (rysunek 13.21).

2. Na ekranie pojawi się okno dialogowe Przejdź do (rysunek 13.22). Zaznacz wybraną nazwę etykiety.

3. Naciśnij przycisk OK.

Rada

• Gdy zakres komórek odpowiadający wybranej etykiecie zostanie zaznaczony, to w polu nazw pojawi się nazwa tej etykiety.

Rysunek 13.19. Lista rozwijana Pole nazw pozwala na szybkie zaznaczenie zakresu komórek odpowiadającego wybranej etykiecie.

Rysunek 13.20. W polu nazw możesz bezpośrednio wpisać nazwę etykiety odpowiadającej wybranemu zakresowi komórek.

Rysunek 13.21. Z menu głównego wybierz polecenie Edycja Przejdź do …

Rysunek 13.22. … a następnie wybierz nazwę(y) wybranej etykiety.

Odwołania 3-W

Zastosowanie odwołań 3-W pozwala na tworzenie formuł odwołujących się do komórek (zakresów komórek) znajdujących się na innych arkuszach bądź skoroszytach. Utworzone w ten sposób połączenia są cały czas aktywne — jeżeli zmieni się zawartość dowolnej komórki użytej w odwołaniu, zmieni się również wynik działania formuły korzystającej z odwołania do takiej komórki.

Excel udostępnia kilka sposobów tworzenia formuł wykorzystujących odwołania 3-W:

• Wykorzystanie etykiet — więcej informacji na ten temat znajdziesz na początku niniejszego rozdziału. Przykład zastosowania tej metody przedstawiono na rysunku 13.23.

• Bezpośrednie wpisywanie odwołań 3-W — Kiedy tworzysz odwołanie 3-W, musisz umieścić w nim nazwę arkusza (jeżeli nazwa arkusza zawiera spacje, to musi zostać ujęta w znaki apostrofu), po której następuje znak wykrzyknika (!) oraz odwołanie do komórki. Jeżeli tworzysz odwołanie 3-W do komórki znajdującej się w innym skoroszycie, to w odwołaniu musisz również umieścić nazwę skoroszytu ujętą w nawiasy kwadratowe. Przykłady przedstawiono na rysunkach 13.24, 13.25 oraz 13.26.

• Klikanie — klikając na odpowiednich komórkach uzyskasz ten sam efekt, co podczas wpisywania odwołań ręcznie — z tym, że to Excel „odwala” całą robotę za Ciebie.

• Zastosowanie polecenia Wklej specjalnie — Przycisk Wklej łącze, znajdujący się w oknie dialogowym Wklej specjalnie, pozwala na przekazywanie odwołań do komórek pomiędzy poszczególnymi arkuszami i skoroszytami.

Rady

• Jeżeli usuniesz komórkę, to Excel wyświetli błąd #ADR! we wszystkich komórkach, które do niej się odwoływały. Formuły znajdujące się w takich komórkach muszą zostać poprawione ręcznie.

• Nigdy nie twórz odwołań do komórek znajdujących się w skoroszytach, które nie zostały jeszcze zapisane na dysku. Jeżeli utworzysz takie odwołanie, po czym zamkniesz plik bez zapisywania, to Excel nie będzie w stanie zaktualizować połączenia.

=SUMA(Styczeń;Luty;Marzec;Kwiecień)

Rysunek 13.23. Przykład zastosowania funkcji SUMA do zsumowania zawartości zakresów komórek Styczeń, Luty, Marzec i Kwiecień znajdujących się na tym samym arkuszu.

='Raport roczny'!$B$9

Rysunek 13.24. Przykład odwołania do komórki B9 znajdującej się na arkuszu o nazwie Raport roczny, w tym samym skoroszycie.

=SUMA(`Kwartał1:Kwartał4'!E9)

Rysunek 13.25. Przykład zastosowania funkcji SUMA do zsumowania zawartości komórek E9 znajdujących się na kolejnych arkuszach począwszy od Kwartał1 aż do Kwartał4.

=[Sprzedaż]'Raport roczny'!$B$9

Rysunek 13.26. Przykład odwołania do komórki B9 znajdującej się w skoroszycie o nazwie Sprzedaż na arkuszu Raport roczny.

Aby utworzyć odwołanie 3-W do zakresu komórek reprezentowanych przez etykietę

1. Zaznacz komórkę, w której chcesz umieścić odwołanie.

2. Wpisz znak równości (=).

3. Jeżeli arkusz zawierający komórkę docelową znajduje się na innym skoroszycie, wpisz nazwę tego skoroszytu (jeżeli nazwa skoroszytu zawiera spacje, to musisz ją umieścić w znakach apostrofu), następnie wpisz znak wykrzyknika (!).

4. Wpisz etykietę komórki, do której chcesz się odwołać (rysunek 13.27 i 13.28).

5. Naciśnij klawisz Enter lub naciśnij przycisk Wpis
   znajdujący się na pasku formuły.

Rada

• Jeżeli komórka reprezentowana przez wybraną etykietę znajduje się na tym samym skoroszycie, to odwołanie do niej możesz utworzyć korzystając z polecenia Wstaw Nazwa Wklej (rysunek 13.6). Więcej informacji na ten temat znajdziesz we wcześniejszych podrozdziałach.

Aby utworzyć odwołanie 3-W za pomocą myszki

1. Zaznacz komórkę, w której chcesz umieścić odwołanie.

2. Wpisz znak równości (=).

3. Jeżeli arkusz zawierający komórkę docelową znajduje się na innym skoroszycie, przejdź do tego skoroszytu.

4. Kliknij na zakładce arkusza, na którym znajduje się komórka docelowa.

5. Zaznacz komórkę(ki), do których się chcesz odwoływać (rysunek 13.29).

6. Naciśnij klawisz Enter lub naciśnij przycisk Wpis
   znajdujący się na pasku formuły.

Rysunki 13.27 i 13.28. Dwa przykłady odwołań 3-W wykorzystujących etykiety. Pierwszy przykład stanowi odwołanie do zakresu komórek znajdujących się na tym samym skoroszycie. Drugi przykład ilustruje sytuację, w której komórki docelowe znajdują się na innym skoroszycie.

Rysunek 13.29. Po wpisaniu znaku równości zaznacz za pomocą myszki komórki docelowe.

Aby bezpośrednio wpisać odwołanie 3-W

1. Zaznacz komórkę, w której chcesz wpisać odwołanie 3-W.

2. Wpisz znak równości (=).

3. Jeżeli będziesz tworzył odwołanie do komórek leżących na innym skoroszycie, to w nawiasach kwadratowych ([])wpisz nazwę tego skoroszytu.

4. Wpisz nazwę wybranego arkusza a następnie znak wykrzyknika (!).

5. Wpisz odwołanie do wybranej komórki(ek).

6. Naciśnij klawisz Enter lub naciśnij przycisk Wpis
   znajdujący się na pasku formuły.

Rada

• Jeżeli nazwa arkusza zawiera spacje, to musisz ją wpisywać w apostrofach. Przykłady znajdziesz na rysunkach 13.24, 13.25 i 13.26.

Aby utworzyć odwołanie 3-W pry pomocy polecenia Wklej specjalnie

1. Zaznacz komórkę, do której chcesz utworzyć odwołanie 3-W.

2. Z menu głównego wybierz polecenie Edycja Kopiuj (rysunek 13.21), naciśnij kombinację klawiszy CTRL-C lub po prostu naciśnij przycisk Kopiuj
   znajdujący się na standardowym pasku narzędzi.

3. Przejdź do arkusza, w którym chcesz utworzyć odwołanie 3-W.

4. Zaznacz komórkę, w której chcesz umieścić odwołanie.

5. Z menu głównego wybierz polecenie Edycja Wklej specjalnie (rysunek 13.21).

6. Na ekranie pojawi się okno dialogowe Wklej specjalnie (rysunek 13.30). Naciśnij przycisk Wklej łącze.

Rysunek 13.30. Do tworzenia odwołań możesz wykorzystać przycisk Wklej łącze, znajdujący się w oknie dialogowym Wklej specjalnie.

Rady

• Po użyciu polecenia Wklej specjalnie nie naciskaj klawisza Enter! Jeżeli naciśniesz ten klawisz, to obecna zawartość komórki zostanie zastąpiona zawartością Schowka, co spowoduje nadpisanie świeżo wklejonego łącza.

• Zastosowanie przycisku Wklej łącze do zakresu komórek spowoduje utworzenie specjalnego rodzaju tablicy. --> Każda komórka w tablicy będzie zawierała odwołanie do odpowiadającej jej komórki w obszarze źródłowym.[Author:SK]

Aby utworzyć formułę wykorzystującą odwołania 3-W

1. Zaznacz komórkę, w której chcesz umieścić formułę.

2. Wpisz znak równości (=).

3. Zastosuj jeden z poniższych sposobów tworzenia formuł:

• Aby wprowadzić funkcję użyj polecenia Wstaw funkcję, lub po prostu wpisz nazwę funkcji i odpowiednie argumenty bezpośrednio z klawiatury. Więcej informacji na temat polecenie Wstaw funkcję znajdziesz w rozdziale 5.

• Aby wprowadzić do formuły operator, po prostu wpisz go z klawiatury. Więcej informacji na temat operatorów znajdziesz w rozdziale 2.

• Aby wprowadzić odwołanie do komórki, przy pomocy myszki zaznacz komórkę, do której formuła będzie się odwoływać, bądź po prostu wpisz odpowiednie odwołanie z klawiatury. Wpisując odwołania 3-W pamiętaj o poprawnym używaniu nawiasów kwadratowych, apostrofów i znaków wykrzyknika, jak to opisano w poprzednim podrozdziale.

Naciśnij klawisz Enter lub naciśnij przycisk Wpis
znajdujący się na pasku formuły.

Aby utworzyć formułę, która sumuje zawartość szeregu komórek o tym samym adresie, ale znajdujących się na wielu kolejnych arkuszach

1. Zaznacz komórkę, w której chcesz wprowadzić formułę.

2. Wpisz =SUMA( — rysunek 13.31.

3. Jeżeli komórki, które chcesz sumować znajdują się w innym skoroszycie, to przejdź do tego skoroszytu.

4. Kliknij na zakładce pierwszego arkusza zawierającego komórkę, która będzie sumowana.

5. Wciśnij i przytrzymaj klawisz Shift a następnie kliknij na zakładce ostatniego arkusza zawierającego komórkę, która będzie sumowana. Wszystkie zakładki pomiędzy pierwszą i ostatnią zostaną zaznaczone (rysunek 13.32). W chwili obecnej formuła w pasku formuł powinna wyglądać mniej więcej tak, jak to przedstawiono na rysunku 13.33.

6. Kliknij na komórce, która ma być sumowana (rysunek 13.34). Odwołanie do komórki zostanie dodane do tworzonej formuły (rysunek 13.35).

7. Wpisz nawias zamykający formułę ).

8. Naciśnij klawisz Enter lub naciśnij przycisk Wpis
   znajdujący się na pasku formuły.

Rady

• Opisanej metody można używać do łączenia komórek znajdujących się na arkuszach o identycznej strukturze. Efekt przypomina nieco operowanie na arkuszu trójwymiarowym.

• Pomimo, że opisanej metody można również używać do konsolidacji danych, to jednak w takiej sytuacji lepszym rozwiązaniem będzie zastosowanie polecenia Konsoliduj, o którym dowiesz się więcej w jednym z następnych podrozdziałów.

Rysunek 13.31. Tworzenie formuły rozpocznij od wpisania funkcji SUMA…

Rysunek 13.32. … następnie zaznacz wszystkie arkusze zawierające komórki, które chcesz zsumować…

Rysunek 13.33. … — nazwy zaznaczonych arkuszy zostaną dołączono do formuły jako zakres arkuszy.

Rysunek 13.34. Następnie kliknij na komórce, którą chcesz dodać do formuły…

Rysunek 13.35. … dzięki czemu odwołanie do niej pojawi się na pasku formuły.

Otwieranie skoroszytów zawierających łącza

Jeżeli otwierasz skoroszyt zawierający łącza do innych skoroszytów, to na ekranie pojawi się okno dialogowe przedstawione na rysunku 13.36.

• Jeżeli naciśniesz przycisk Aktualizuj, to Excel sprawdzi pliki wskazywane przez łącza i zaktualizuje odpowiednie dane. Jeżeli Excel nie będzie w stanie odnaleźć wskazanych plików, to zostaniesz o tym poinformowany.

• Jeżeli naciśniesz przycisk Nie aktualizuj, to Excel nie będzie sprawdzał łączy i użyje poprzednich informacji.

Rysunek 13.36. Powyższe okno dialogowe pojawi się na ekranie w sytuacji, kiedy będziesz otwierał skoroszyt zawierający łącza do innych plików.

Konsolidacje

Polecenie Konsolidacja pozwala na łączenie danych pochodzących z różnych źródeł. Excel umożliwia przeprowadzenie konsolidacji danych na dwa sposoby:

• Konsolidacja w oparciu o strukturę danych — jest to opcja szczególnie użyteczna w sytuacji, gdy zachodzi potrzeba przetwarzania danych znajdujących się w tych samych miejscach szeregu arkuszy o identycznej strukturze (rysunek 13.3).

• Konsolidacja w oparciu o etykiety bądź kategorie danych — jest to opcja przydatna w sytuacji, gdy zachodzi potrzeba przetwarzania danych znajdujących się na szeregu arkuszy o różnych strukturach.

Rada

• Niezależnie od zastosowanej metody Excel może utworzyć łącza do danych źródłowych, dzięki czemu informacje po konsolidacji będą automatycznie aktualizowane w sytuacji, gdy jakieś dane źródłowe zostaną zmodyfikowane.

Aby dokonać konsolidacji w oparciu o strukturę danych

1. Zaznacz komórkę(ki), w których chcesz umieścić dane po konsolidacji (rysunek 13.37).

2. Z menu głównego wybierz polecenie Dane Konsoliduj (rysunek 13.38).

3. Na ekranie pojawi się okno dialogowe Konsoliduj (rysunek 13.39). Z listy rozwijanej Funkcja wybierz funkcję, która będzie użyta do konsolidacji (rysunek 13.40).

4. Kliknij przycisk Zwiń okno dialogowe i przejdź do arkusza zawierającego pierwszą komórkę do konsolidacji. Odwołanie do arkusza zostanie wpisane w polu tekstowym Odwołanie.

5. Zaznacz komórkę(ki), których zawartości chcesz konsolidować (rysunek 13.41). Odpowiednie odwołanie zostanie wprowadzone w polu tekstowym Odwołanie.

6. Naciśnij przycisk Dodaj.

Rysunek 13.37. Zaznacz komórki, w których chcesz umieścić dane po konsolidacji.

Rysunek 13.38. Z menu głównego wybierz polecenie Dane Konsoliduj.

Rysunek 13.39. Za pomocą okna dialogowego Konsoliduj możesz zdefiniować komórki, których zawartości będą poddane konsolidacji.

Rysunek 13.40. Z listy rozwijanej Funkcja wybierz funkcję, która będzie użyta do konsolidacji.

7. Powtórz kroki 4, 5 i 6 dla wszystkich zakresów komórek, których zawartości mają zostać poddane konsolidacji. Po zakończeniu lista Wszystkie odwołania znajdująca się w oknie dialogowym Konsoliduj może wyglądać np. tak, jak to przedstawiono na rysunku 13.42.

8. Aby utworzyć łącza pomiędzy danymi źródłowymi a komórkami, w których znajdują się dane po konsolidacji włącz opcję Utwórz łacze z danymi źródłowymi.

9. Naciśnij przycisk OK.
   
   Excel dokona konsolidacji danych z komórek źródłowych (rysunek 13.43).

Rady

• Aby opisany powyżej mechanizm działał poprawnie, poszczególne zakresy komórek źródłowych muszą mieć identyczna strukturę i składać się z identycznej ilości komórek.

• Jeżeli po otwarciu okna dialogowego Konsoliduj znajdziesz tak wpisane już jakieś odwołania do zakresów komórek, to możesz je usunąć zaznaczając je kolejno i naciskając przycisk Usuń.

• Jeżeli włączysz opcję Utwórz łacze z danymi źródłowymi, to Excel utworzy konspekt danych (rysunek 13.43) z łączami do wszystkich zakresów źródłowych. Za pomocą paska konspektu możesz odpowiednio ukrywać i wyświetlać poszczególne zakresy danych. Więcej informacji na temat konspektów znajdziesz w rozdziale 10.

Rysunek 13.41. Zaznacz zakres komórek, których zawartości mają zostać poddane konsolidacji.

Rysunek 13.42. Po zakończeniu definiowania zakresów źródłowych, lista Wszystkie odwołania znajdująca się w oknie dialogowym Konsoliduj może wyglądać np. tak, jak to przedstawiono na rysunku.

Rysunek 13.43. Excel umieszcza wyniki konsolidacji w wybranych uprzednio komórkach docelowych.

Aby dokonać konsolidacji w oparciu o etykiety bądź kategorie danych

1. Zaznacz komórkę(ki), w których chcesz umieścić dane po konsolidacji. Jak to przedstawiono na rysunku 13.44, możesz zaznaczyć nawet tylko jedną komórkę.

2. Z menu głównego wybierz polecenie Dane Konsoliduj (rysunek 13.38).

3. Na ekranie pojawi się okno dialogowe Konsoliduj (rysunek 13.39). Z listy rozwijanej Funkcja wybierz funkcję, która będzie użyta do konsolidacji (rysunek 13.40).

4. Kliknij przycisk Zwiń okno dialogowe i przejdź do arkusza zawierającego pierwszą komórkę do konsolidacji. Odwołanie do arkusza zostanie wpisane w polu tekstowym Odwołanie.

5. Zaznacz komórkę(ki), których zawartości chcesz konsolidować, włączając w to sąsiadujące komórki zawierające etykiety identyfikujące dane (rysunek 13.45). Odwołanie do komórek zostanie wpisane w polu tekstowym Odwołanie.

6. Naciśnij przycisk Dodaj.

7. Powtórz kroki 4, 5 i 6 dla wszystkich zakresów komórek, których zawartości mają zostać poddane konsolidacji. Na rysunkach 13.46 i 13.47 przedstawiono przykłady dwóch kolejnych obszarów danych źródłowych. Po zakończeniu okno dialogowe Konsoliduj może wyglądać np. tak, jak to przedstawiono na rysunku 13.48.

8. Włącz odpowiednie opcje znajdujące się w grupie Użyj etykiet w, tak aby poinformować Excela, gdize znajdują się etykiety danych.

9. Naciśnij przycisk OK.
   
   Excel dokona konsolidacji danych z komórek źródłowych (rysunek 13.49).

Rysunek 13.44. Zaznacz komórkę(ki) docelowe.

Rysunki 13.45, 13.46 i 13.47. Zaznacz zakresy komórek, których zawartości mają zostać poddane konsolidacji.

Rysunek 13.48. W oknie dialogowym Konsoliduj znajdziesz definicję poszczególnych zakresów danych źródłowych oraz możesz wybrać odpowiednie opcje etykiet.

Rysunek 13.49. Rezultat konsolidacji danych źródłowych.

Widoki niestandardowe

Mechanizm tworzenia widoków niestandardowych pozwala użytkownikowi na tworzenie dopasowanych do konkretnych potrzeb sposobów prezentacji (widoków) zawartości arkusza. Widok może zawierać informacje o rozmiarach i pozycjach poszczególnych okien, lokalizacji aktywnej komórki arkusza, współczynniku powiększenia wyświetlania, ukrytych kolumnach i wierszach oraz ustawieniach drukowania. Po zdefiniowaniu widoku niestandardowego można z niego w razie potrzeby bardzo szybko skorzystać.

Rada

• Dołączanie ustawień drukowania do definicji widoku niestandardowego pozwala na tworzenie i zachowywanie wielu różnych raportów przeznaczonych do drukowania.

Aby utworzyć widok niestandardowy

1. Zmodyfikuj ustawienia wyświetlania zawartości arkusza tak, aby uzyskać żądany wygląd arkusza (rysunek 13.50).

2. Z menu głównego wybierz polecenie Widok Widoki niestandardowe (rysunek 13.51).

3. Na ekranie pojawi się okno dialogowe Widoki niestandardowe (rysunek 13.52). Naciśnij przycisk Dodaj.

4. Na ekranie pojawi się okno dialogowe Dodaj widok (rysunek 13.53). W polu Nazwa wpisz nazwę tworzonego widoku.

5. Włącz wybrane opcje z grupy Dołącz do widoku:

• Ustawienia wydruku — powoduje dołączenie do definicji widoku bieżących ustawień strony i innych opcji drukowania dokumentu.

• Ustawienia filtru, ukrytych wierszy i kolumn — powoduje dołączenie do definicji widoku bieżących ustawień filtrowania oraz ukrytych wierszy i kolumn.

Naciśnij przycisk OK.

Rysunek 13.50. Zmodyfikuj ustawienia wyświetlania zawartości arkusza tak, aby uzyskać żądany wygląd arkusza.

Rysunek 13.51. Z menu głównego wybierz polecenie Widok Widoki niestandardowe.

Rysunek 13.52. Okno dialogowe Widoki niestandardowe.

Rysunek 13.53. Na ekranie pojawi się okno dialogowe Dodaj widok — w polu Nazwa wpisz nazwę tworzonego widoku a następnie wybierz odpowiednie opcje widoku.

Aby zmienić bieżący widok

1. Przejdź na arkusz, na którym zostały zdefiniowane widoki niestandardowe.

2. Z menu głównego wybierz polecenie Widok Widoki niestandardowe (rysunek 13.51).

3. Na ekranie pojawi się okno dialogowe Widoki niestandardowe (rysunek 13.54). Zaznacz nazwę wybranego widoku.

4. Naciśnij przycisk Pokaż.
   
   Excel zmieni sposób wyświetlania zawartości arkusza zgodnie z definicją wybranego widoku.

Aby usunąć widok niestandardowy

1. Przejdź na arkusz, na którym zostały zdefiniowane widoki niestandardowe.

2. Z menu głównego wybierz polecenie Widok Widoki niestandardowe (rysunek 13.51).

3. Na ekranie pojawi się okno dialogowe Widoki niestandardowe (rysunek 13.54). Zaznacz nazwę widoku, który chcesz usunąć.

4. Naciśnij przycisk Usuń.

5. Na ekranie pojawi się okno dialogowe z prośbą o potwierdzenie usunięcia widoku (rysunek 13.55) — naciśnij przycisk Tak.

6. Aby usunąć kolejne widoki, powtarzaj operacje opisane w punktach 3 do 5.

7. Po zakończenie naciśnij przycisk Zamknij.

Rada

• Usunięcie widoku nie ma żądnego wpływu na zawartość arkusza — powoduje to jedynie usunięcie pozycji odpowiadająca danemu widokowi z listy widoków niestandardowych (rysunek 13.54).

Rysunek 13.54. Aby zmienić bieżący widok arkusza (usunąć wybrany widok), w oknie dialogowym Widoki niestandardowe zaznacz wybrany widok a następnie naciśnij przycisk Pokaż (Usuń).

Rysunek 13.55. Przed usunięciem widoku niestandardowego, Excel poprosi o potwierdzenie.

Makra

Makro jest to inaczej mówiąc seria poleceń, którą Excel jest w stanie wykonać automatycznie. Proste makra mogą być wykorzystywane np. do automatyzacji powtarzających się zadań i operacji jak np. wprowadzanie danych czy formatowanie komórek.

Pomimo, że makra są zapisywane w postaci kodu wbudowanego języka Visual Basic, to jednak, aby utworzyć makro wcale nie musisz być programistą. Excel udostępnia tzw. rejestrator makr, który zapisuje wykonywane przez Ciebie czynności, naciśnięcia klawiszy, wybierane z menu polecenia i opcje itd. a następnie automatycznie tworzy na tej podstawie odpowiednie kod programu (makra). Dzięki takiemu rozwiązaniu tworzenie makr jest niezwykle prostą operacją, nawet dla zupełnie początkującego użytkownika programu Excel.

Aby utworzyć makro przy pomocy rejestratora makr

1. Z menu głównego wybierz polecenie Narzędzia Makro Zarejestruj nowe makro (rysunek 13.56). Na ekranie pojawi się okno dialogowe Rejestruj makro (rysunek 13.57).

2. W polu Nazwa makra wpisz wybraną nazwę identyfikującą tworzone makro.

3. Jeżeli to konieczne, zdefiniuj klawisz skrótu, za pomocą którego tworzone makro będzie uruchamiane (pole Ctrl+).

4. Jeżeli to konieczne, zmodyfikuj opis tworzonego makra wprowadzając odpowiednie informacje w polu Opis.

5. Naciśnij przycisk OK.

6. Rozpocznij wykonywanie tych wszystkich czynności, które powinny zostać zarejestrowane w postaci makra. Pamiętaj, że Excel rejestruje wszystko — nawet popełnianie błędy!

7. Aby zakończyć rejestrowania makra naciśnij przycisk Zatrzymaj rejestrowanie
   , znajdujący się na maleńkim pasku narzędzi makr (rysunek 13.58).

Rysunek 13.56. Podmenu Makro udostępnia szereg poleceń do tworzenia i modyfikacji makr.

Rysunek 13.57. W oknie Rejestruj makro możesz zdefiniować nazwę tworzonego makra oraz określić inne jego opcje.

Rysunek 13.58. Aby zakończyć rejestrowania makra naciśnij przycisk Zatrzymaj rejestrowanie, znajdujący się na maleńkim pasku narzędzi makr.

Aby uruchomić wybrane makro

Naciśnij klawisz skrótu zdefiniowany podczas tworzenia makra.

lub

1. Z menu głównego wybierz polecenie Narzędzia Makro Makra (rysunek 13.56).

2. Zaznacz nazwę wybranego makra na liście Nazwa makra (rysunek 13.59).

3. Naciśnij przycisk Uruchom.
   
   Excel wykona wybrane makro dokładnie w taki sposób, w jaki zostało zarejestrowane.

Rady

• Zawsze zapisuj skoroszyt przed pierwszym uruchomieniem nowego makra. Wyniki działania makra nie zawsze muszą być zgodne z Twoimi oczekiwaniami, a ponieważ Excel nie potrafi wycofywać poleceń wykonywanych przez makra, to jedyną metodą przywrócenia stanu arkusza sprzed wykonania makra jest po prostu otwarcie ostatnio zapisanej wersji arkusza.

• Poszczególne makra są zapisywane w skoroszycie jako tzw. moduły. Jeżeli chcesz przejrzeć kod programu danego makra, to powinieneś je zaznaczyć w oknie dialogowym Makro, a następnie nacisnąć przycisk Edycja. Przykład został przedstawiony na rysunku 13.60. Samodzielna modyfikacja kodu nie jest polecanym rozwiązaniem dopóty, dopóki użytkownik nie posiądzie przynajmniej podstawowych umiejętności programowania w języku Visual Basic.

• Bardziej zaawansowane makra umożliwiają tworzenie własnych niestandardowych funkcji a nawet całych aplikacji użytkownika.

Rysunek 13.59. Okno dialogowe Makra pozwala na uruchamianie, edycję i usuwanie makr.

Rysunek 13.60. Kod przykładowego makra.

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

2 C:\Documents and Settings\skipper\Moje dokumenty\Helion\Po prostu Excel XP\rozdzial13.doc

To tłumaczenie nie do końca odpowiada oryginałowi, ale w oryginale autorka chyba nieco rozmija się z prawdą...:-)

---