PRZYDATNE Triki w EXELU.doc

Trik 1
Wygodne porównywanie różnych części arkusza

Pracujesz nad rozbudowanym zestawieniem znajdującym się w jednym arkuszu. Dokonujesz w nim weryfikacji obliczeń. Jednak powiązane ze sobą dane znajdują się w znacznie od siebie oddalonych zakresach komórek. Uciążliwe jest przechodzenie z jednego końca arkusza do drugiego w celu porównania kilku wartości. Aby usprawnić tę czynność, proponujemy wykorzystać dostępną opcję dzielenia okna.

W tym celu:
1. Zaznacz komórkę znajdującą się mniej więcej po środku zestawienia.
2. Z menu Okno wybierz polecenie Podziel.


Rys. 1. W menu Okno znajduje się odpowiednie polecenie

Pojawią się linie dzielące arkusz na cztery okna. Możesz dowolnie dostosować obszar wyświetlanych zakresów komórek.


Rys. 2. Cztery części arkusza wyświetlane w oddzielnych okienkach

Wskazówka
Możesz dodatkowo zablokować okna w ten sposób, aby jedno było nieruchome. W tym celu skorzystaj z polecenia Zablokuj okienka.

Jeśli chcesz poznać inne sposoby wygodnego porównywania danych, skorzystaj z poradnika Excel w praktyce. Dowiesz się m.in.: jak wyświetlić kilka arkuszy w oddzielnych oknach? Jak wykorzystać pole tekstowe do porównywania danych? Jak szybko przenieść sumy końcowe do zablokowanego nagłówka tabeli?

Trik 2

Powtórzone wpisy na liście mogą powodować niepoprawne obliczenia, gdyż wyniki najczęściej będą zawyżone. Jeżeli nie chcesz mozolnie przeglądać listy wiersz po wierszu w poszukiwaniu duplikatów, użyj makra, które je odnajdzie i oznaczy

Łatwe usuwanie duplikatów z wykorzystaniem makra

Otrzymałeś listę firm, które nawiązały współpracę w minionym roku. Dane były uzupełniane przez kilku pracowników i niestety na liście znajdują się powtórzone nazwy. Ty potrzebujesz szybko je usunąć. Proponujemy zastosowania makra, które zrobi to błyskawicznie.

Przykładową listę zawierającą powtórzone wartości przedstawia rysunek 1.

Rys. 1. Lista zawierająca powtórzone nazwy firm

=> Aby szybko pozbyć się duplikatów, wykonaj następujące wartości.
1. Kliknij dowolną komórkę zawierającą nazwę firmy i wybierz na pasku narzędziowym ikonę polecenia Sortuj rosnąco.
2. Wciśnij kombinację klawiszy lewy Alt + F11, aby otworzyć Edytor VBA.
3. W oknie Edytora wstaw nowy moduł, wybierając z menu Insert polecenie Module.
4. Do okna po prawej wpisz kod widoczny na rysunku 2.

Rys. 1. Kod makra

5. Zapisz i zamknij Edytor VBA.
6. Teraz zaznacz zakres komórek zawierający nazwy firm i wciśnij kombinację klawiszy lewy Alt + F8.
7. Zostanie wyświetlone okno dialogowe Makro, w którym kliknij nazwę makra OznaczDuplikaty i kliknij przycisk Uruchom.

Zamiast powtórzonych nazw firm w komórkach pojawiło się słowo Duplikat. A zatem wszystkie wiersze, w których się ono znajduje, powinny być usunięte.

=> Aby to szybko zrobić:
8. Zaznacz dowolną komórkę w pierwszym wierszu zestawienia (np. B1) i z menu Dane wybierz polecenie Filtr/Autofiltr.
9. Kliknij rozwijaną listę w komórce B1 i wybierz nazwę Duplikat.

Rys. 3. Przefiltruj listę, tak aby widoczne były tylko wiersze do usunięcia

W efekcie widoczne będą tylko wiersze, w których znajduje się słowo Duplikat.

10. Zaznacz widoczne dane i z menu Edycja wybierz polecenie Usuń wiersz.
11. W oknie komunikatu, który się pojawi, wybierz odpowiedź twierdzącą.
12. Teraz rozwiń listę z komórki B1 i wybierz pozycję (Wszystkie).
13. Na koniec wyłącz Autofiltr (Dane/Filtr/Autofiltr).

Rys. 4. Lista bez powtórzeń

Przedstawione rozwiązanie okazuje się wyjątkowo przydatne, gdy potrzebujesz usunąć duplikaty z listy obejmującej wiele tysięcy wierszy. Musisz jednak zawsze pamiętać o tym, aby przed uruchomieniem makra posortować listę.

Trik 3
Chcesz porównać oferty towarów i sprawdzić, które ceny uległy zmianie. Użyj formatowania warunkowego, które je automatycznie wyszuka i wyróżni.

W arkuszu znajdują się listy towarów znajdujących się w dwóch magazynach. Chcesz je ze sobą porównać i oznaczyć nazwy tych, które są w jednym magazynie, ale nie ma ich w drugim. Na przeglądanie arkusza komórka po komórce szkoda czasu. Proponujemy zastosowanie formatowania warunkowego.

Przykładowe listy przedstawia rysunek 1.

Rys. 1. Towary do porównania

Załóżmy, że kolorem zielonym chcesz oznaczyć te nazwy towarów, które występują w pierwszym magazynie, ale nie ma ich w drugim. Kolorem żółtym wyróżnione powinny być towary znajdujące się jedynie w magazynie drugim.

=> Aby taki efekt uzyskać:
1. Zaznacz zakres komórek A2:A8 i z menu Wstaw wybierz polecenie Nazwa/Definiuj. Pojawi się okno dialogowe Definiuj nazwy.
2. W polu Nazwy w skoroszycie wpisz magazyn1 i kliknij przycisk Dodaj.
3. Kliknij myszą pole Odwołuje się do i zaznacz w arkuszu zakres B2:B8.
4. W polu Nazwy w skoroszycie wpisz nazwę magazyn2.

Rys. 2. Zdefiniowane nazwy zakresów

5. Kliknij przycisk Dodaj, a następnie Zamknij.

Możesz już zastosować formatowanie warunkowe.

=> W tym celu:
6. Zaznacz zakres komórek A2:A8 i z menu Format wybierz polecenie Formatowanie warunkowe.
7. Z pierwszej rozwijanej listy po lewej wybierz pozycję Formuła jest.
8. W pole po prawej wprowadź następującą formułę:

=LICZ.JEŻELI(magazyn2;A2)=0

9. Kliknij przycisk Formatuj i w zakładce Desenie wybierz kolor zielony.

Okno zawierające warunek formatowania powinno wyglądać jak na rysunku 3.

Rys. 3. Warunek formatowania

10. Zatwierdź ustawienia, klikając w obu otwartych oknach przycisk OK.
11. Teraz zaznacz zakres komórek B2:B8 i ponownie otwórz okno dialogowe Formatowanie warunkowe.
12. Tym razem zastosuj następującą formułę:

=LICZ.JEŻELI(magazyn1;B2)=0

13. Określ żółty kolor wypełnienia tła komórek.

Efekt końcowy przedstawia rysunek 4.

Rys. 4. Towary występujące tylko w jednym magazynie zostały wyróżnione

W szybki sposób oznaczyłeś listy. Oczywiście przydatność tego rozwiązania docenisz wówczas, gdy do porównania będą listy składające się z kilkuset komórek.

Trik 4
Szybkie przenoszenie formatu między komórkami

Chcesz ujednolicić form at komórek w całym zestawieniu i uniknąć klikania kolejnych ikonek na pasku narzędziowym? Poznaj 2 szybkie sposoby przenoszenia formatu z jednej komórki na drugą.

Przyjmijmy, że prowadzisz rejestr, w którym wpisujesz dane do kolejnych wierszy. Z innego arkusza skopiowałeś określony fragment danych i dokleiłeś je do jednej z kolumn swojego rejestru. Operacja przebiegła poprawnie i chcesz teraz przenieść formatowanie używane w rejestrze na nowo dodane komórki. Czy musisz wybierać kolejne ikony na pasku narzędzi, aby ujednolicić formatowanie? Niekoniecznie, są szybsze sposoby.

Rys. 1. Nowe dane, na które chcesz przenieść formatowanie

Malarz formatów

Jednym ze sposobów jest skorzystanie z malarza formatów, którego ikona jest widoczna na pasku narzędziowym.

Rys. 2. Malarz formatów

=> Aby przenieść format:
1. Zaznacz jedną z komórek bazowych (sformatowanych) i kliknij ikonę malarza formatów.
2. Komórka zostanie otoczona przerywanym obramowaniem. Zaznacz zakres docelowy - w przykładzie C7:C10.

Po zwolnieniu myszy format zostanie przeniesiony jak to przedstawia rysunek 3.

Rys. 3. Format został przeniesiony

Polecenie Wklej specjalnie

Innym sposobem jest użycie polecenia Wklej specjalnie, które umożliwia wklejanie tylko niektórych elementów.

=> Aby z niego skorzystać:
1. Zaznacz komórkę bazową i wciśnij kombinację klawiszy Ctrl + C.
2. Zaznacz komórki docelowe i z menu Edycja wybierz polecenie Wklej specjalnie.
3. W oknie dialogowym, które się pojawiło, zaznacz pole Formaty i zatwierdź, klikając OK.

Rys. 4. Wklejanie formatów

Korzystając z tego sposobu, także uzyskałeś oczekiwany efekt: błyskawiczne przeniesienie formatów między komórkami.

Trik 5
Wykres z nałożoną linią obrazującą wartość średnią

Porównanie osiągniętych wyników z wielkością przeciętną to jedna z częściej przygotowywanych prezentacji. Sprawdź, jak szybko stworzyć czytelny wykres tego typu.

Analizujesz wyniki handlowców osiągnięte w minionym kwartale. Potrzebujesz przedstawić je na wykresie. Na prezentacji powinna się także znaleźć linia obrazująca przeciętny utarg handlowca, aby szybko sprawdzić, którzy pracują poniżej normy. Szybka rozbudowa tabeli źródłowej i nieznaczna zmiana właściwości wykresu pozwolą uzyskać taką prezentację.

Przykładowe dane przedstawia rysunek 1.

Rys. 1. Dane źródłowe

Rozbudowa tabeli źródłowej

W pierwszej kolejności dodaj do tabeli kolumnę, w której znajdą się wartości określające średni utarg.

=> W tym celu:
1. Do komórki C2 wprowadź następującą formułę:

=ŚREDNIA($B$2:$B$8)

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

Przygotowanie wykresu

Możesz już przystąpić do zbudowania wykresu i nałożenia linii obrazującej średni utarg.

=> Aby to zrobić:
1. Zaznacz zakres komórek A2:C8, uruchom kreator wykresów i wybierz typ wykresu Kolumnowy.
2. Usuń poziome linie siatki osi wartości i usuń kolor dla obszaru kreślenia (domyślnie szary). (Kliknij prawym przyciskiem myszy każdy z tych elementów i z menu podręcznego wybierz polecenie Wyczyść).
3. Na gotowym wykresie kliknij prawym przyciskiem myszy kolumnę z danymi reprezentującą średnią i z menu podręcznego wybierz polecenie Typ wykresu.

Rys. 2. Zaznaczona seria danych dla średniej

4. Wybierz z listy XY (Punktowy) i zatwierdź klikając OK.
5. Kliknij dwukrotnie myszą jeden z punktów poziomego wykresu XY (Punktowy). Otworzy się okno dialogowe Formatowanie serii danych.
6. W zakładce Desenie, w polach Linia i Znacznik zaznacz opcję Brak.
7. Przejdź do zakładki Słupki błędów X i w polu Wyświetl zaznacz opcję Razem.

Rys. 3. Formatowanie serii danych

Po zatwierdzeniu OK uzyskasz oczekiwany efekt jak na rysunku 4.

Rys. 4. Wykres z linią średnią

Wskazówka
Jeśli chcesz bardziej uwydatnić poziomą linię, kliknij ją prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Formatuj słupki błędów. W zakładce Desenie znajdziesz potrzebne opcje formatowania.