lekcja 20.doc

Wersja do wydruku [PDF]

:: Trik 1. Analiza wynagrodzeń względem wieku w tabeli przestawnej
:: Trik 2. Szybkie formatowanie nagłówków zestawień
:: Trik 3. Oznaczenie powtórzonych wierszy za pomocą formuły
:: Trik 4. Dynamicznie aktualizowana zawartość rozwijanej listy
:: Trik 5. Stosowanie malarza formatów na nieprzylegających komórkach

Trik 1
Analiza wynagrodzeń względem wieku w tabeli przestawnej

Pobierz plik z przykładem

Otrzymałeś wyniki badania przeprowadzonego wśród aktywnych zawodowo respondentów. Chcesz przeanalizować zależność pomiędzy wiekiem pracownika i jego zarobkami. Potrzebujesz się dowiedzieć, ile wynosi średnie wynagrodzenie zatrudnionych, którzy należą do określonej grupy wiekowej. Jeśli chciałbyś takie obliczenia wykonywać za pomocą formuł warunkowych, stracisz mnóstwo czasu. Dużo szybciej i wygodniej przygotujesz odpowiedni raport oparty na tabeli przestawnej.


Rys. 1. Fragment listy z danymi

Obszar z danymi obejmuje zakres komórek A2:A55. W pierwszej kolejności zbuduj na jego podstawie prostą tabelę przestawną. Sposób jej przygotowania jest różny w zależności od wersji Excela, którego używasz.

=> Jeżeli pracujesz we wcześniejszej wersji Excela:
1. Zaznacz dowolną komórkę w obrębie listy i z menu Dane wybierz polecenie Raport tabeli przestawnej i wykresu przestawnego.
2. W kroku 1. zaznacz na górze pole Lista lub baza danych Microsoft Excel, a na dole Tabela przestawna.
3. Kliknij Dalej, aby przejść do kolejnego kroku.
4. W kroku 2. nie zmieniaj ustawień i kliknij Dalej.
5. W ostatnim kroku zaznacz pole Nowy arkusz i kliknij przycisk Zakończ.

=> Jeżeli korzystasz z Excela 2007:
1. Zaznacz komórkę w obrębie listy, uaktywnij kartę Wstawianie i grupie poleceń Tabele kliknij Tabela przestawna.
2. W wyświetlonym oknie upewnij się, czy Excel poprawnie rozpoznał zakres danych źródłowych.
3. U dołu zaznacz opcję Nowy arkusz i kliknij OK.

Zostanie wyświetlony projekt tabeli przestawnej jak na rysunku 2.


Rys. 2. Projekt tabeli przestawnej

6. Chwyć myszą pole Wiek w latach i przeciągnij je nad obszar nazwany Upuść pola wierszy tutaj.
7. Pole Zarobki brutto umieść w obszarze Upuść elementy danych tutaj.

Tabela przestawna jest gotowa. Teraz w kolumnie Wiek w latach utwórz grupy wiekowe.

=> W tym celu:
1. Prawym przyciskiem myszy kliknij dowolną liczbę w kolumnie A.
2. Z menu podręcznego, które się pojawiło, wybierz polecenie Grupuj i pokaż szczegóły/Grupuj (w Excelu 2007: Grupuj).
3. W polach Początek i Koniec wpisz odpowiednio 20 i 60, a w polu według wprowadź 10.


Rys. 3. Opcje grupowania

4. Zatwierdź, klikając OK.

Lata zostały ujęte w grupy wiekowe. Pozostała Ci na koniec zamiana podsumowań na wartości średnie.

=> Aby to zrobić:
1. Prawym przyciskiem myszy kliknij dowolną sumę w kolumnie B i z menu podręcznego wybierz polecenie Ustawienia pola.
2. W polu Podsumowanie według zaznacz Średnia i kliknij OK.
3. Otrzymanym wartościom nadaj format walutowy.


Rys. 4. Gotowy raport

Wskazówka
W tak zbudowanym raporcie bardzo szybko możesz np. sprawdzić, jakie są najwyższe zarobki w obrębie grupy wiekowej. W tym celu dostosuj rodzaj obliczeń za pomocą polecenia Ustawienia pola.

[do góry]

Trik 2
Szybkie formatowanie nagłówków zestawień

Pobierz plik z przykładem

Znaczna część użytkowników Excela rozpoczyna budowanie swoich zestawień od sformatowania wiersza nagłówkowego, w którym znajdą się oznaczenia kategorii danych dla każdej kolumny. W związku z tym należy uruchomić wiele opcji formatowania (wypełnienie tła, obramowanie, pogrubienie czcionki itd.). Ty od teraz nie musisz na to tracić czasu, jeśli zastosujesz poniższy sposób oparty na formatowaniu warunkowym. Przyjmijmy, że będziesz budował podobne zestawienia w różnych arkuszach pustego skoroszytu. Aby uniknąć każdorazowego formatowania wierszy nagłówkowych, w pierwszej kolejności zgrupuj arkusze.

=> W tym celu:
1. Zaznacz zakładkę pierwszego arkusza, wciśnij klawisz Shift i kliknij zakładkę ostatniego.
2. Teraz przejdź do pierwszego arkusza i zaznacz cały pierwszy wiersz, klikając jego etykietę z numerem.


Rys. 1. Zgrupowane arkusze i zaznaczony pierwszy wiersz

3. Z menu Format wybierz polecenie Formatowanie warunkowe (w Excelu 2007: uaktywnij kartę Narzędzia główne i wskaż Formatowanie warunkowe/Nowa reguła).
4. Z pierwszej rozwijanej listy wybierz pozycję Formuła jest (w Excelu 2007: uaktywnij opcję Użyj formuły do określenia komórek, które należy sformatować), a w pole edycji wpisz taką formułę:

=NIE(CZY.PUSTA(A$1))

5. Kliknij przycisk Formatuj i określ sposób formatowania wiersza nagłówkowego zestawień.


Rys. 2. Poprawnie zdefiniowany warunek formatowania

6. Na koniec zatwierdź, klikając OK.

Gdy będziesz chciał wpisać kolejną nazwę kategorii danych do pierwszego wiersza, nagłówek zostanie odpowiednio wyróżniony.


Rys. 3. Format nadawany po wpisaniu nazwy kategorii danych w nagłówku

[do góry]

Trik 3
Oznaczenie powtórzonych wierszy za pomocą formuły

Pobierz plik z przykładem

Załóżmy, że w arkuszu rejestrujesz dane wszystkich klientów. Nazwy firm wpisujesz do zestawienia na podstawie wystawianych dokumentów. Niestety w trakcie przeglądania arkusza zauważasz, że niektóre nazwy się powtarzają. Aby pozbyć się wielokrotnych wpisów, należałoby je odszukać i ręcznie usunąć całe wiersze. W jaki sposób to szybko zrobić? Posłuż się sprytną formułą!

Przykładowe dane przedstawia rysunek 1.


Rys. 1. Rejestr zawierający powtórzone wpisy

=> Aby oznaczyć identyfikatory klientów, które występują wielokrotnie:
1. W komórce C2 wprowadź następującą formułę:

=JEŻELI(LICZ.JEŻELI($B$2:$B$8;
B2)>1;"Wielokrotnie";"")

2. Skopiuj ją do komórek poniżej.


Rys. 2. Odpowiednie wiersze zostały oznaczone

Wszyscy klienci, którzy występują na liście więcej niż 1 raz, zostali oznaczeni. Teraz już wiesz, które pozycje należy usunąć z rejestru.

Wyjaśnienie działania formuły:
Funkcja LICZ.JEŻELI ma za zadanie sprawdzić, czy w zakresie komórek B2:B8, identyfikator z komórki B2 występuje więcej niż jeden raz. Jeśli tak, dzięki zastosowaniu funkcji JEŻELI zwracany jest tekst Wielokrotnie. W przeciwnym razie komórka pozostaje pusta. Zauważ, że zastosowano odpowiednie adresowanie komórek. Dzięki temu kopiowana w dół formuła sprawdza kolejne identyfikatory znajdujące się w komórkach kolumny B.

[do góry]

Trik 4
Dynamicznie aktualizowana zawartość rozwijanej listy

Pobierz plik z przykładem

Na pewno przekonałeś się, że korzystanie z rozwijanej listy przy wypełnianiu rejestru jest bardzo wygodne. Niestety problem pojawia się wówczas, gdy trzeba zaktualizować jej zawartość. W tym triku pokażemy Ci, w jaki sposób zbudować dynamiczne odwołanie do danych źródłowych rozwijanej listy. Jeśli dopiszesz nowe wartości, zostaną one automatycznie uwzględnione wśród pozycji listy.

Załóżmy, że w arkuszu o nazwie Zamówienia zbudowałeś rejestr zamówień, który jest uzupełniany za pomocą rozwijanej listy stworzonej z wykorzystaniem Sprawdzania poprawności. W arkuszu Klienci znajduje się natomiast spis firm, których nazwy pojawiają się po rozwinięciu listy w rejestrze.


Rys. 1. Arkusz Zamówienia, w którym znajduje się rejestr


Rys. 2. Arkusz Klienci, w którym znajdują się dane źródłowe dla rozwijanej listy

Dynamiczne odwołanie

Aby rozwijane listy wstawione w kolumnie B rejestru automatycznie uwzględniały dopisane nazwy klientów w arkuszu Klienci, powinieneś stworzyć dynamiczne odwołanie.

=> W tym celu:
1. Z menu Wstaw wybierz polecenie Nazwa/Definiuj (w Excelu 2007: uaktywnij kartę Formuły i w grupie poleceń Nazwy zdefiniowane wskaż Definiuj nazwę).
2. W polu Nazwy w skoroszycie (w Excelu 2007: Nazwa) wpisz Lista_klientów.
3. W polu Odwołuje się do wprowadź następującą formułę:

=PRZESUNIĘCIE(Klienci!$A$2;;;
ILE.NIEPUSTYCH(Klienci!$A:$A)-1)


Rys. 3. Definiowanie dynamicznego odwołania

Wyjaśnienie działania formuły:
W pierwszym argumencie funkcji PRZESUNIĘCIE podany jest adres komórki, od której zaczyna się lista klientów. Funkcja ILE.NIEPUSTYCH zlicza wypełnione komórki w kolumnie A. Od wyliczonej wartości odejmowana jest jedna komórka oznaczająca nagłówek. Pozostała wartość jest użyta w czwartym argumencie funkcji PRZESUNIĘCIE i na jej podstawie funkcja ta rozszerza obszar o tyle komórek w dół, ile pozycji zostało wpisanych do listy.

4. Kliknij przycisk Dodaj, a następnie OK.

Tworzenie rozwijanych list

Możesz teraz przystąpić do wstawienia rozwijanych list w kolumnie B.

=> W tym celu:
1. Zaznacz komórkę B2 w arkuszu Zamówienia i z menu Dane wybierz polecenie Sprawdzanie poprawności (w Excelu 2007: uaktywnij kartę Dane i w grupie poleceń Narzędzia danych wskaż Poprawność danych).
2. W wyświetlonym oknie przejdź do zakładki Ustawienia.
3. Z pierwszej rozwijanej listy wybierz pozycję Lista, a w polu Źródło wpisz:

=Lista_klientów


Rys. 4. Tworzenie rozwijanej listy

4. Zatwierdź ustawienia, klikając OK.
5. Wstawioną listę przeciągnij w dół za pomocą uchwytu wypełnienia (prawy dolny róg komórki B2).
6. Aby sprawdzić, czy automatyczna aktualizacja rozwijanej listy działa poprawnie, dopisz dowolną nazwę na końcu listy klientów w arkuszu Klienci (np. Test).
7. Przejdź do arkusza Zamówienia i sprawdź, czy rozwijane listy zostały zaktualizowane.


Rys. 5. Automatyczna aktualizacja rozwijanej listy działa poprawnie

Uwaga
Na liście klientów w arkuszu Klienci nie mogą znajdować się puste komórki pomiędzy nazwami.

[do góry]

Trik 5
Stosowanie malarza formatów w nieprzylegających komórkach

Pobierz plik z przykładem

Malarz formatów jest niezwykle przydatnym narzędziem. Nie ma szybszego i wygodniejszego sposobu przenoszenia formatu z jednej komórki na inną. Niestety ma on pewne ograniczenie. Jeśli komórki, na które ma zostać przeniesiony format, nie tworzą spójnego zakresu, wówczas operację przeniesienia formatu trzeba powtórzyć dla każdej komórki lub osobnego zakresu. Okazuje się, że można łatwo obejść to ograniczenie.

Przyjmijmy, że komórka A1 ma nałożony format, który chciałbyś przenieść na pozostałe liczby widoczne na rysunku 1.


Rys. 1. Z komórki A1 chcesz przenieść format na pozostałe

=> Aby to szybko zrobić:
1. Zaznacz komórkę A1 i kliknij dwukrotnie lewym przyciskiem myszy ikonę malarza formatów widoczną na pasku narzędziowym (w Excelu 2007: ikona malarza formatów znajduje się na karcie Narzędzia główne, w grupie poleceń Schowek).
2. Klikaj kolejno komórki, na które ma zostać przeniesiony format.


Rys. 2. Malarz formatów zastosowany na niespójnych zakresach komórek

Jeśli w pierwszym kroku kliknąłbyś ikonę tylko jeden raz, wówczas format zostałby przeniesiony tylko na pierwszą klikniętą komórkę i całą operację musiałbyś powtórzyć. Teraz możesz tego uniknąć.

Wskazówka
Aby wyłączyć tryb przenoszenia formatu za pomocą malarza formatów, wciśnij klawisz Esc.

[do góry]

Wiedza i Praktyka sp. z o.o., ul. Łotewska 9a, 03-918 Warszawa,
Centrum Obsługi Klienta: tel.: (022) 518 29 29 fax: (022) 617 60 10, e-mail: e-serwis@wip.pl

Numer NIP: 526-19-92-256 Numer KRS: 0000098264 - Sad Rejonowy dla m.st. Warszawy, Sad Gospodarczy XIII Wydzial Gospodarczy Rejestrowy Wysokosc kapitalu zakladowego: 200 000 zl