:: Trik 1. Wydruk tabel cząstkowych od razu na oddzielnych stronach
:: Trik 2. Wygodne zaznaczanie całych tabel
:: Trik 3. Hurtowe usuwanie wierszy na podstawie maski tekstowej
:: Trik 4. Dostosowanie kolejności danych na wykresie kilkoma kliknięciami
:: Trik 5. Szybkie oznaczenie wierszy z danymi spełniającymi kryteria
Trik 1
Wydruk tabel cząstkowych od razu na oddzielnych stronach
Pobierz plik z przykładem
Przyjmijmy, że w arkuszu znajduje się lista transakcji zrealizowanych przez kilku przedstawicieli handlowych. Dane rozciągają się na kilkaset komórek, a Ty potrzebujesz podsumować wyniki każdego z handlowców i wydrukować je na oddzielnych kartkach.
Rys. 1. Lista transakcji zrealizowanych przez handlowców
Możesz oczywiście zrobić to za pomocą autofiltra. Czyli najpierw przefiltrować kolumnę Handlowiec, wstawić podsumowanie i uruchomić drukowanie. Następnie powtórzyć operację filtrowania dla kolejnego handlowca. Okazuje się, że jest szybszy sposób sporządzenia takich podsumowujących zestawień cząstkowych. Do tego celu idealnie nadaje się narzędzie Excela o nazwie Sumy częściowe (Sumy pośrednie).
=> Aby z niego skorzystać:
1. W pierwszej kolejności posortuj zestawienie według nazwisk. Kliknij dowolne nazwisko i wybierz polecenie Sortuj rosnąco.
2. Teraz kliknij dowolną komórkę w obrębie listy i z menu Dane wybierz polecenie Sumy częściowe (w Excelu 2007: uaktywnij kartę Dane i w grupie poleceń Konspekt wskaż Suma częściowa). Zostanie wyświetlone okno dialogowe z odpowiednimi opcjami.
3. Z pierwszej rozwijanej listy wybierz pozycję Handlowiec, bo według tej kolumny mają być pogrupowane kwoty transakcji.
4. Z listy Użyj funkcji wybierz Suma, bo mają to być zestawienia podsumowujące.
5. W polu Dodaj sumę pośrednią do zaznacz nazwę kolumny z kwotami (Kwota w zł).
6. U dołu zaznacz opcje Podsumowanie poniżej danych oraz Podział strony między grupami.
Dzięki uaktywnieniu drugiej opcji uzyskasz oczekiwany efekt: zestawienia cząstkowe dotyczące poszczególnych handlowców będą umieszczone na osobnych kartkach.
Rys. 2. Ustawienia Sum pośrednich
Po zatwierdzeniu OK dane zostaną pogrupowane według nazwisk handlowców. Poniżej danych widoczne będą podsumowania cząstkowe. Zestawy wartości znajdą się na oddzielnych stronach wydruku. Świadczą o tym przerywane linie podziału widoczne na rysunku 3.
Rys. 3. Podsumowania znajdą się na oddzielnych kartkach
Trik 2
Wygodne zaznaczanie całych tabel
Pobierz plik z przykładem
Na pewno nieraz w trakcie pracy potrzebowałeś zaznaczać całe tabele, aby wykonać na nich pewną operację (np. kopiowanie, wklejanie, wypełnianie, czy zaznaczania obszarów arkusza do wydruku). Jeśli tabele składają się z kilkunastu komórek, nie stanowi to problemu. Jednak gdy obszar składa się kilkunastu oddzielnych tabel i obejmuje bardzo dużą liczbę komórek, czynność ta okazuje się uciążliwa. Aby temu zaradzić, pokażemy Ci, w jaki sposób za pomocą trzech klawiszy możesz zaznaczać bardzo obszerne tabele danych.
Rys. 1. Dwa proste zestawienia w jednym arkuszu
=> Aby błyskawicznie zaznaczyć cały obszar górnej tabeli:
1. Kliknij jej dowolna wypełnioną komórkę, np. C3.
2. Wciśnij kombinację klawiszy Ctrl + Shift + 8. Zauważ, że wszystkie komórki tabeli zostały zaznaczone.
Rys. 2. Zaznaczona cała górna tabela
Spróbuj w identyczny sposób zaznaczyć tabelę poniżej. Po wciśnięciu kombinacji klawiszy powinien podświetlić się zakres komórek B9:D14. Przedstawiony skrót działa w ten sposób, że zaznacza wszystkie wypełnione danymi komórki, które przylegają do aktywnej komórki. Nie uwzględnia natomiast komórek pustych.
Trik 3
Hurtowe usuwanie wierszy na podstawie maski tekstowej
Pobierz plik z przykładem
W arkuszu znajduje się wykaz księgowań wewnętrznych firmy, w którym są informacje na temat przeksięgowań kosztów z jednego konta na inne. Dane w tym arkuszu często się zmieniają i co jakiś czas usuwane są wszystkie księgowania z kont rozpoczynających się od cyfr: 501-000-. Jak zautomatyzować taką czynność?
Rys. 1. Zestaw księgowań wewnętrznych firmy
Najprościej w tym celu wykorzystać operator Like, który porównuje wartości na podstawie odpowiedniej maski tekstowej.
=> W tym celu:
1. Otwórz Edytor Visual Basic za pomocą polecenia Narzędzia/Makro/Edytor Visual Basic (w Excelu 2007: kliknij na kartę Deweloper i w grupie poleceń Kod wskaż Visual Basic) lub poprzez naciśnięcie klawiszy lewy Alt + F11.
Uwaga
Jeśli karta Deweloper nie jest widoczna, to wybierz Przycisk pakietu Office, naciśnij Opcje programu Excel, w zakładce Popularne zaznacz Pokaż kartę Deweloper na wstążce i kliknij OK.
2. W oknie Edytora Visual Basic wybierz polecenie menu Insert/Module.
3. Wpisz kod przedstawiony na rysunku 2. Znajdziesz go także w pliku z przykładem załączonym do tego triku.
Rys. 2. Makro usuwające wiersze na podstawie odpowiedniej maski tekstowej
W makrze wykorzystujemy pętlę, która sprawdza po kolei każdy wiersz zestawienia (począwszy od ostatniego), czy w kolumnie C znajduje się tekst rozpoczynający się od znaków: 501-000-. Gdy znajdzie tekst zgodny z maską tekstową 501-000-*, to makro usuwa cały wiersz i kontynuuje sprawdzanie w kolejnym wierszu leżącym powyżej.
Wskazówka
Po operatorze Like musisz wpisać maskę tekstową. Może ona zawierać znak gwiazdki: (*), który zastępuje dowolną liczbę znaków lub znak zapytania (?), który zastępuje pojedynczy znak. Przy usuwaniu elementów zawsze stosuj pętlę rozpoczynającą działanie od końca zestawienia. Unikniesz w ten sposób sytuacji, w której pętla pominęła niektóre elementy.
4. Zamknij Edytor Visual Basic.
5. Teraz możesz uruchomić makro za pomocą polecenia Narzędzia/Makro/Makra (w Excelu 2007: na karcie Widok, w grupie poleceń Makra wybierz Makra). W oknie Makro zaznacz nazwę makra UsuńWiersze i kliknij przycisk Uruchom.
Wskazówka
Do makra możesz przypisać skrót klawiaturowy za pomocą przycisku Opcje, w oknie dialogowym Makro służącym do uruchamiania makr.
Trik 4
Dostosowanie kolejności danych na wykresie kilkoma kliknięciami
Pobierz plik z przykładem
Zbudowany w Excelu wykres prawie nigdy w pełni nie odpowiada oczekiwaniom użytkowników. Jedną z dość często ręcznie wykonywanych operacji jest dostosowanie kolejność serii danych. Okazuje się, że wcale nie potrzeba stracić na to wiele czasu.
Załóżmy, że przygotowałeś zestawienie zamówień na określone towary z pewnego okresu. Znajdują się w nim dane cząstkowe oraz podsumowanie. Na podstawie takiej tabeli źródłowej stworzyłeś wykres słupkowy. Niestety Excel wyświetlił serie danych w nieodpowiedniej kolejności.
Rys. 1. Przykładowy wykres
Słupki prezentujące podsumowania znajdują się powyżej serii danych odnoszących się do poszczególnych produktów. Dodatkowo produkty są prezentowane w nieodpowiedniej kolejności. Czy musisz zmieniać układ zestawienia, aby uzyskać poprawną prezentację? Niekoniecznie! Wystarczy drobna zmiana ustawień wykresu.
=> W tym celu:
1. Kliknij prawym przyciskiem myszy jeden ze słupków i z menu podręcznego wybierz polecenie Formatuj serie danych (w Excelu 2007: Zaznacz dane).
2. W oknie, które się pojawi, przejdź do zakładki Kolejność serii (w Excelu 2007: pomiń ten krok).
3. Zaznacz serię, którą chcesz przesunąć i wybierz jeden z przycisków: Przenieś w górę lub Przenieś w dół. W okienku podglądu możesz oglądać efekt wprowadzonych zmian.
Rys. 2. Ustal kolejność serii
Po zatwierdzeniu ustawień uzyskasz poprawną prezentację. Zauważ, że nie musisz otwierać okna dla każdej serii z osobna. Układ wykresu możesz dopracować za jednym razem.
Trik 5
Szybkie oznaczenie wierszy z danymi spełniającymi kryteria
Pobierz plik z przykładem
W arkuszu znajduje się zestawienie klientów Twojej firmy. Kontrahenci mają siedziby w różnych regionach kraju, a Ty planujesz przyznać upusty wybranym firmom. Rabat należy się jedynie tym, których siedziba mieści się w Warszawie, Poznaniu, Gdańsku i Krakowie. Potrzebujesz szybko oznaczyć tych klientów. W jaki sposób to zrobić bez potrzeby każdorazowego filtrowania danych? Rozwiązaniem jest prosta formuła warunkowa.
Przykładowe dane przedstawia rysunek 1.
Rys. 1. Lista klientów
W pierwszej kolejności stwórz tabelę pomocniczą, w której określisz kryteria dla formuły.
=> W tym celu:
1. W komórkach D1:D4 wpisz nazwy miast jedna pod drugą. Muszą być identyczne co nazwy znajdujące się na liście. W przeciwnym razie formuła ich nie skojarzy i nie osiągniesz oczekiwanego efektu.
Rys. 2. Kryteria dla formuły
2. Do komórki C2 wpisz teraz formułę, która uwzględni kryteria z tabeli pomocniczej i oznaczy odpowiednich klientów.
=JEŻELI(LUB(B2=$D$1;B2=$D$2;B2=$D$3;B2=$D$4);"Rabat";"")
3. Skopiuj ją poniżej, klikając dwukrotnie prawy dolny róg komórki C2.
Rys. 3. Klienci zostali oznaczeni
Wyjaśnienie działania formuły:
W pierwszym argumencie funkcji JEŻELI została zagnieżdżona funkcja LUB. W przykładzie zwraca ona w wyniku wartość PRAWDA, jeśli jedno z równań będących jej argumentami jest prawdziwe. Jeśli więc nazwa miasta z kolumny B zgadza się z jedną z nazw zawartych w tabeli pomocniczej zwracany jest tekst Rabat. W przeciwnym razie wynikiem jest pusty ciąg znaków.
|