30 najlepszych trików w Excelu - Szkolenie Excel · 2013-02-04 · 5 | Strona Trik 2. Szybkie...

30 najlepszych trików w Excelu

www.SzkolenieExcel.pl

2 | S t r o n a


Czy korzystanie z Excela sprawia Ci trudności?

Nadszedł więc czas skorzystać z profesjonalnego szkolenia. Zdajemy sobie sprawę, że ciężko zmobilizować się do samodzielnej nauki w domu. Niech specjaliści zajmą się podniesieniem Twoich kwalifikacji.

Dobierz szkolenie dla siebie www.SzkolenieExcel.pl Starasz się o wymarzoną pracę, w której dobra znajomość Excela to warunek konieczny? W trakcie naszych szkoleń stawiamy na użyteczność prezentowanych rozwiązań i praktykę. Twój przyszły szef niczym Cię nie zaskoczy. Po ukończeniu szkolenia otrzymasz od nas certyfikat w języku polskim i angielskim.

Podnieś z nami swoje kwalifikacje. Wejdź na www.SzkolenieExcel.pl Szukasz szkolenia zamkniętego dla swoich pracowników? Każde z naszych szkoleń może być zrealizowane w trybie zamkniętym na terenie całej Polski. Tematyka i zakres zagadnień może być także dostosowana do indywidualnych wymagań klienta. Jesteśmy elastyczni przy negocjowaniu warunków finansowych.

Skorzystaj z bezpłatnej konsultacji szkoleniowej na www.SzkolenieExcel.pl

Ogólne szkolenie z Excela Cię nie interesuje? Może warto wybrać się na szkolenie profilowane (np. Excel dla managerów) lub narzędziowe (Pulpity menedżerskie w Excelu – Dashboards). Nasza oferta szkoleń stale się powiększa.

Sprawdź nasze specjalistyczne szkolenia na www.SzkolenieExcel.pl

Dostarczamy wiedzę, która będzie procentować latami…

3 | S t r o n a


Trik 1. Rozdzielenie wyrazów do osobnych kolumn....................................... 4

Trik 2. Szybkie wprowadzanie wartości leżących powyżej ............................. 5

Trik 3. Automatyczne usuwanie powtórzonych wpisów ................................. 6

Trik 4. Szybkie przesuwanie kolumn ........................................................... 7

Trik 5. Komunikat pojawiający się po kliknięciu komórki ............................... 8

Trik 6. Numeracja wierszy odporna na filtrowanie ........................................ 9

Trik 7. Szybkie wyrównanie szerokości kolumn ........................................... 11

Trik 8. Oznaczenie celu na wykresie .......................................................... 12

Trik 9. Szybkie zaznaczanie 15000 komórek............................................... 14

Trik 10. Wprowadzanie danych do wielu arkuszy jednocześnie ..................... 15

Trik 11. Sprytny sposób ukrycia arkusza .................................................... 16

Trik 12. Wyliczenie dni roboczych pomiędzy datami .................................... 17

Trik 13. Wygodna zmiana adresowania komórek......................................... 18

Trik 14. Nazwa arkusza wyświetlana w komórce ......................................... 18

Trik 15. Podsumowanie wszystkich wierszy i kolumn tabeli .......................... 19

Trik 16. Szybkie wyznaczenie całkowitej wartości zamówienia ..................... 20

Trik 17. Wyśrodkowany tytuł bez konieczności scalania komórek ................. 21

Trik 18. Hurtowa aktualizacja wszystkich cen ............................................. 22

Trik 19. Numeracja porządkowa tylko dla wypełnionych wierszy .................. 23

Trik 20. Ochrona formuł w arkuszu ............................................................ 24

Trik 21. Wygodna praca z wieloarkuszowym raportem ................................ 25

Trik 22. Efektowne wyróżnienie ważnej wartości ......................................... 26

Trik 23. Własna lista autowypełniania ........................................................ 27

Trik 24. Przenoszenie formatu z komórki na komórkę ................................. 28

Trik 25. Poprawa czytelności długich tabel.................................................. 29

Trik 26. Hurtowe uzupełnienie pominiętych komórek................................... 30

Trik 27. Wyświetlenie daty razem z nazwą dnia tygodnia............................. 32

Trik 28. Korekta wartości czasu ................................................................. 33

Trik 29. Dowolna liczba przedstawiona za pomocą cyfr rzymskich ................ 34

Trik 30. Problem z podsumowaniem czasu przekraczającym 24H ................. 35

Spis treści

4 | S t r o n a


Trik 1. Rozdzielenie wyrazów do osobnych kolumn

Przykład W arkuszu Excela do jednej komórki wprowadzono kody pocztowe i nazwy miast. Taki sposób wpisania danych uniemożliwia sortowanie wg miast. Dane trzeba rozdzielić do osobnych kolumn.

Instrukcja krok po kroku

• Zaznacz zakres komórek zawierający połączone dane (w przykładzie

A2:A7).

• Uaktywnij kartę Dane i wskaż polecenie Tekst jako kolumny.

• W wyświetlonym oknie zaznacz opcję Rozdzielany i kliknij Dalej.

• W kolejnym oknie określ ogranicznik Spacja i wybierz Dalej.

• Określ miejsce, od którego mają się rozpoczynać rozdzielone dane (w

przykładzie B2) i kliknij Zakończ.

5 | S t r o n a


Trik 2. Szybkie wprowadzanie wartości leżących

powyżej

Przykład Arkusz Excela z uwagi na tabelaryczny układ idealnie nadaje się do prowadzenia różnego rodzaju rejestrów. W tego typu zestawieniach dane są bardzo często wprowadzane ręcznie i pewne informacje się powtarzają. W Excelu można ułatwić sobie wprowadzanie danych, które znajdują się już w rejestrze. Na rysunku widoczny jest uproszczony rejestr zaliczek. W kolumnie D znajdują się dość długie wpisy, które się powtarzają dla różnych pracowników. Ich ręczne wpisywanie to kompletna strata czasu. Pokażemy Ci, jak to usprawnić.


• Zaznacz pierwszą pustą komórkę bezpośrednio poniżej ostatniego wpisu w kolumnie D.

• Wciśnij kombinację klawiszy lewy Alt + Strzałka w dół. Pojawi się rozwijana lista zawierająca wszystkie wpisy leżące powyżej.

• Kliknij nazwę odpowiedniego zakładu

Ten sposób działa tylko dla tekstów i musi być zaznaczone najbliższa niewypełnione komórka w kolumnie.

6 | S t r o n a


Trik 3. Automatyczne usuwanie powtórzonych wpisów

Przykład Na rysunku widnieje zestawienie, w którym powtarzają się niektóre wiersze. Ty potrzebujesz pozbyć się powtórzeń.


• Zaznacz dowolną komórkę w obrębie zestawienia.

• Uaktywnij kartę Dane.

• W grupie poleceń Narzędzia danych wybierz polecenie Usuń

duplikaty.

• Zostanie wyświetlone okno dialogowe, w którym zaznacz kolumny

zawierające zduplikowane wartości.

• Zatwierdź, klikając OK.

7 | S t r o n a


Trik 4. Szybkie przesuwanie kolumn

Przykład Jeżeli chcesz przesunąć kolumnę w inne miejsce zestawienia, zapomnij o ręcznym wstawianiu pustej kolumny i korzystaniu z poleceń Kopiuj/Wklej. Poznaj nasz szybki sposób wykonania tego zadania. Przyjmijmy, że w zestawieniu widocznym na rysunku ktoś niefortunnie rozplanował kolumny. Podsumowanie kwartalne zostało umieszczone przed kwotami za poszczególne miesiące tego okresu. Pokażemy Ci, jak szybko przesunąć kolumnę Suma Kwartał 1 za kolumnę Marzec.


• Zaznacz całą kolumnę B poprzez kliknięcie jej nagłówka z oznaczeniem

literowym.

• Najedź myszą nad boczną krawędź zaznaczenia. Wskaźnik powinien

przyjąć kształt czterokierunkowej strzałki.

• Trzymaj wciśnięty klawisz Shift i z wciśniętym lewym przyciskiem

myszy przeciągnij kolumnę w prawo pomiędzy kolumny E i F (aktualne

położenie będzie oznaczone pionowym znacznikiem).

• Teraz najpierw zwolnij przyciski myszy, a potem klawisz Shift. Gotowe.

Kolumna zmieniła swoje położenie.

8 | S t r o n a


Trik 5. Komunikat pojawiający się po kliknięciu komórki

Przykład Gdy przygotowujesz arkusz, który będzie wypełniany przez Twoich współpracowników, warto abyś opatrzył go odpowiednią instrukcją korzystania. Pokażemy Ci, co zrobić, aby po kliknięciu określonej komórki pojawił się komunikat z określoną przez Ciebie treścią. Załóżmy, że chcesz, aby osoba wypełniająca poniższy formularz, w komórce B8 podała datę urodzenia w określonym formacie.


• Zaznacz komórkę B8 i uaktywnij kartę Dane. • W grupie poleceń Narzędzia danych wskaż Poprawność danych. • W wyświetlonym oknie dialogowym przejdź do zakładki Komunikat

wejściowy. • Zaznacz pole Pokazuj komunikat wejściowy przy wyborze

komórki. • W polu Tytuł wpisz UWAGA! • W polu Komunikat wejściowy wprowadź swoją treść, np. Wprowadź

datę w formacie rrrr-mm-dd.

9 | S t r o n a


Zatwierdź, klikając OK, a komunikat zostanie nałożony na komórkę B8. Po jej kliknięciu powinien pojawić się zdefiniowany przez Ciebie komunikat.

Trik 6. Numeracja wierszy odporna na filtrowanie

Przykład Nadanie numeracji porządkowej w tabeli nie jest szczególnie trudnym zadaniem. Wystarczy w pierwsze kolumnie zastosować autowypełnianie kolejnymi liczbami. Co jednak w sytuacji, gdy tabela jest często filtrowana? Przecież wtedy numery wierszy się wówczas nie dostosują. Jeżeli chcesz, aby numeracja się automatycznie dopasowywała, skorzystaj z formuły. Rysunek przedstawia przykładową tabelę, która będzie filtrowana.

10 | S t r o n a



• Zaznacz całą kolumnę A, klikając jej nagłówek z oznaczeniem literowym i wciśnij kombinację klawiszy Ctrl + Shift + = (znak równości).

• Nowej kolumnie nadaj nazwę: Lp. • W komórce A2 wprowadź następującą formułę:

=JEŻELI(B2="";"";SUMY.CZĘŚCIOWE(3;B$2:B2))

• Skopiuj ją poniżej na większą liczbę wierszy (co najmniej o jeden więcej) niż jest aktualnie wypełnionych w tabeli. Numeracja została nadana, jak to przedstawia rysunek

11 | S t r o n a


• Teraz zastosuj filtrowanie według kolumny Miejscowość. W tym celu kliknij dowolną komórkę w obrębie zestawienia i na karcie Dane wybierz polecenie Filtruj. Rozwiń listę w kolumnie Miejscowość i określ nazwę miasta. Tabela zostanie przefiltrowana.

Numeracja porządkowa działa znakomicie, co widać na kolejnym rysunku.

Trik 7. Szybkie wyrównanie szerokości kolumn

Przykład Równa szerokość kolumn to jeden z elementów wpływających na estetykę tworzonej tabeli. Pokażemy Ci, jak błyskawicznie wyrównywać kolumny. Przykładowe zestawienie, które ma kolumny o różnej szerokości, przedstawia rysunek.


• Zaznacz całe kolumny w tabeli. W tym celu kliknij nagłówek z literą A i przeciągnij w prawo.

• Teraz wskaźnik myszy przesuń nad krawędź nagłówka jednej z zaznaczonych kolumn. Powinien przyjąć kształt dwukierunkowej strzałki.

• Trzymaj wciśnięty lewy przycisk myszy i rozciągaj kolumnę do odpowiedniej szerokości (zobaczysz wskazówkę informującą o aktualnym rozmiarze).

• Zwolnij przycisk myszy, a kolumny zostaną wyrównane.

12 | S t r o n a


Warto wiedzieć Z łatwością możesz dopasować rozmiar kolumn do ich zawartości. W tym celu podświetl je w całości i lewym przyciskiem myszy kliknij dwukrotnie (szybko) krawędź jednego z nagłówków.

Trik 8. Oznaczenie celu na wykresie

Przykład Wykresy budowane w Excelu bardzo często pełnią rolę porównawczą. W przypadku sprzedaży najczęściej wyznacza się jakiś cel (np. oczekiwaną kwotę utargu) i do niego przyrównuje osiągnięte wyniki. Pokażemy Ci, jak szybko umieścić na wykresie kolumnowym linię określającą wartość celu. Przykładowe zestawienie sprzedaży, a także zbudowany na tej podstawie prosty wykres kolumnowy przedstawia kolejny rysunek.

13 | S t r o n a



• W komórce C1 wpisz nazwę nagłówka kolumny np. Cel. • Do komórek C2:C7 wprowadź wartość celu, np. 75000. Twoja tabela

źródłowa powinna wyglądać jak na kolejnym rysunku.

• Teraz Twoim zadaniem jest dodanie wartości celu do wykresu. W tym celu zaznacz wykres, a w tabeli źródłowej zostaną obramowane kwoty sprzedaży. Przeciągnij prawy górny narożnik ramki nad kolumnę z celem. Operację ilustruje kolejny rysunek.

14 | S t r o n a


Wartość celu została umieszczona na wykresie. Obecnie jednak jest prezentowana za pomocą kolumn. Wykres byłby dużo bardziej czytelny, gdyby cel reprezentowany był poziomą linią. Aby taki efekt osiągnąć:

• Prawym przyciskiem myszy kliknij jedną z kolumn prezentujących cel. Z podręcznego menu wybierz polecenie Zmień typ wykresu seryjnego.

• W wyświetlonym oknie wskaż typ Liniowy i zatwierdź OK. Poniższy rysunek prezentuje gotowy, sformatowany wykres.

Trik 9. Szybkie zaznaczanie 15000 komórek

Przykład Przyjmijmy, że masz zestawienie rozciągające się na 15000 wierszy. Chcesz zmienić format danych znajdujących się w kolumnie A. Zaznaczanie myszą tak obszernego zakresu komórek to kompletna strata czasu. Zastosuj trik.

15 | S t r o n a



• Kliknij myszą w polu nazwy znajdującym się po lewej stronie pola formuły.

• Wpisz odwołanie do zakresu, który chcesz zaznaczyć (np. A1:A15000). • Wciśnij na klawiaturze klawisz Enter. Gotowe!

Trik 10. Wprowadzanie danych do wielu arkuszy

jednocześnie

Przykład Przyjmijmy, że chcesz w kilku arkuszach Twojego skoroszytu powinna się znaleźć identyczna tabela. Czy takie zestawienie musisz przygotowywać kilkanaście razy lub ręcznie je kopiować? Niekoniecznie. Jest pewien trik, który pozwala to zautomatyzować.


• Najpierw zgrupuj arkusze, w których powinna się znaleźć identyczna tabela. W tym celu kliknij zakładkę pierwszego z nich, przytrzymaj klawisz Shift, a następnie kliknij zakładkę ostatniego. Arkusze zostaną podświetlone na biało, jak to przedstawiono na rysunku.

• Teraz zbuduj swoją tabelę w aktywnym arkuszu (w przykładzie Arkusz1).

16 | S t r o n a


• Gdy już będzie gotowa, rozgrupuj arkusze. Aby to zrobić, kliknij zakładkę dowolnego nieaktywnego arkusza. Gotowe! We wszystkich arkuszach została wstawiona ta sama tabela.

Warto wiedzieć Jeżeli chcesz zgrupować arkusze, których zakładki nie leżą obok siebie, zaznaczaj je z wciśniętym klawiszem Ctrl, a nie Shift.

Trik 11. Sprytny sposób ukrycia arkusza

Przykład Chcesz szybko ukryć arkusz zawierający ważne dane bez konieczności nakładania haseł? Jest sprytny sposób, aby to zrobić.


• Uruchom Edytor VBA za pomocą skrótu klawiaturowego lewy Alt + F11.

• Z menu View wybierz polecenie Project Explorer. • W oknie Project – VBAProject zaznacz nazwę arkusza, który chcesz

ukryć i wciśnij klawisz F4. • Właściwość Visible ustaw jako xlSheetVeryHidden. Wybierzesz ją z

rozwijanej listy jak na rysunku.

Warto wiedzieć Aby odkryć arkusz, właściwość Visible zmień na xlSheetVisible.

17 | S t r o n a


Trik 12. Wyliczenie dni roboczych pomiędzy datami

Przykład Gdy odejmiemy od siebie 2 daty, uzyskamy różnicę dni. Co jednak zrobić w sytuacji, jeżeli interesują nas różnica, ale tylko dni roboczych (weekendy i święta nie powinny być uwzględnione w wyniku). Excel jest przygotowany na taką ewentualność i oferuje funkcję arkuszową o nazwie DNI.ROBOCZE. W Excelu 2007 funkcja ta nosi nazwę NETWORKDAYS. Do arkusza wprowadziliśmy daty początkowe i końcowe z grudnia 2013 r. Obok zestawienia umieściliśmy daty świąt. Widok arkusza przedstawia rysunek.


• W komórce C2 wprowadź następującą formułę: =DNI.ROBOCZE(A2;B2;$E$2:$E$3)

• Przeciągnij ją do komórek poniżej za pomocą uchwytu wypełnienia (prawy dolny narożnik komórki C2).

18 | S t r o n a


Trik 13. Wygodna zmiana adresowania komórek

Przykład Przy budowaniu formuł bardzo często trzeba stosować różne odwołania do zakresów i komórek (np. mieszane $A2:$B3, bezwzględne $A$2:$B$3 i względne A2:B3). Ręczne wpisywanie znaków $ jest dość uciążliwe, wiec warto sobie to ułatwić.


• Po wpisaniu odwołania naciśnij jednokrotnie klawisz F4, a Excel zamieni adresowanie na bezwzględne.

• Naciśnij ponownie klawisz F4, a adresowanie zostanie zmienione na mieszane.

• Naciskaj wspomniany klawisz tyle razy, aż osiągniesz oczekiwany efekt. Warto wiedzieć Za pomocą tego klawisza możesz także zmieniać adresowanie w napisanych już formułach. Wystarczy wówczas wyedytować swój wzór, kliknąć odwołanie wewnątrz formuły i naciskać klawisz F4.

Trik 14. Nazwa arkusza wyświetlana w komórce

Przykład Dość wygodną metodą identyfikacji danych jest umieszczanie w arkuszu jego nazwy widniejącej na karcie u dołu okna. Aby uniknąć, ręcznego wpisywania, proponujemy zastosować pewien trik.


• Zapisz swój skoroszyt na dysku komputera. • Do dowolnej pustej komórki wpisz następującą formułę:

=FRAGMENT.TEKSTU(KOMÓRKA("nazwa_pliku"); ZNAJDŹ("]";KOMÓRKA("nazwa_pliku"))+1; DŁ(KOMÓRKA("nazwa_pliku"))) W komórce pojawi się nazwa arkusza, co jest widoczne na rysunku.

19 | S t r o n a


Trik 15. Podsumowanie wszystkich wierszy i kolumn

tabeli

Przykład Przyjmijmy, że Twoim zadaniem jest podsumowanie wszystkich wierszy i kolumn w tabeli. Przykładowe zestawienie przedstawia poniższy rysunek. Aby to zrobić, nie musisz stosować autosumowania dla każdej kolumny czy wiersza. Podsumowanie błyskawicznie przeprowadzisz za pomocą prostego skrótu klawiaturowego.


• Zaznacz wszystkie dane liczbowe i dodatkowo przylegającą pustą kolumnę po prawej i pusty wiersz poniżej.

• Wciśnij kombinację klawiszy lewy Alt + = (znak równości). Gotowe! Dane we wszystkich wierszach i kolumnach zostały podsumowane, jak to ilustruje kolejny rysunek.

20 | S t r o n a


Trik 16. Szybkie wyznaczenie całkowitej wartości

zamówienia

Przykład Czasami w arkuszu możesz napotkać dane w takim układzie, że wykonanie podsumowania będzie możliwe dopiero po wykonaniu obliczeń pośrednich. Wyobraź sobie, że masz zestawienie zamówionych produktów, w którym widnieją jedynie ceny jednostkowe oraz liczby sztuk (patrz rysunek). Aby sprawdzić wartość całego zamówienia, należałoby najpierw przemnożyć liczbę sztuk przez cenę jednostkową każdego produktu, a potem dopiero wykonać sumowanie. Okazuje się, że można to zrobić na skróty.


• Do dowolnej pustej komórki arkusza wprowadź następującą formułę: =SUMA(C3:C7*D3:D7)

• Koniecznie zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Poprawnie wprowadzona zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły. Nie próbuj wpisywać ich ręcznie.

21 | S t r o n a


Całkowita wartość zamówienia została wyznaczona, co widać na kolejnym rysunku.

Trik 17. Wyśrodkowany tytuł bez konieczności scalania

komórek

Przykład Tytuł zestawienia jest najczęściej umieszczany tuż nad danymi. Do wyśrodkowania go względem tabeli użytkownicy zwykle stosują polecenie Scal i wyśrodkuj. Okazuje się, że scalanie komórek nie jest konieczne. Skorzystaj z naszego triku.


• Zaznacz zakres komórek B2:F2, czyli obszar, w którego obrębie tytuł powinien być wyśrodkowany.

• Wciśnij kombinację klawiszy Ctrl + 1. • W wyświetlonym oknie dialogowym przejdź do zakładki Wyrównanie. • Z rozwijanej listy Poziomo wybierz pozycję Wyrównaj zaznaczenie

do środka. Po zatwierdzeniu przyciskiem OK, uzyskasz oczekiwany efekt.

22 | S t r o n a


Trik 18. Hurtowa aktualizacja wszystkich cen

Przykład Załóżmy, że w Excelu sporządzono cennik wszystkich towarów oferowanych przez firmę. Lista rozciąga się na wiele wierszy. W pewnym momencie kierownictwo firmy zdecydowało o podniesieniu ceny każdego produktu o 10 procent. Aby zaktualizować cennik nie musisz korzystać z formuł. Użyj triku. Przykładowy cennik przedstawia rysunek. Potrzebujesz podnieść ceny w kolumnie B o 10 proc. w górę.


• W dowolnej pustej komórce arkusza wpisz liczbę 1,1. • Zaznacz tę komórkę i skopiuj ją. • Podświetl wszystkie ceny w kolumnie B i rozwiń listę poniżej przycisku

Wklej. • Wybierz z niej polecenie Wklej specjalnie. • W wyświetlonym oknie dialogowym zaznacz pozycję Przemnóż. • Zatwierdź, klikając przycisk OK.

Gotowe! Ceny zostały powiększone o 10%.

23 | S t r o n a


Trik 19. Numeracja porządkowa tylko dla wypełnionych

wierszy

Przykład Niektóre zestawienia charakteryzuje to, że ich zawartość jest często korygowana, np. poprzez czyszczenie całych wierszy z informacjami. Jeżeli w takiej tabeli zastosuje się zwykłą numeracje porządkową, to nie będzie ona uwzględniać aktualnej ilości pozycji. Problem taki ilustruje rysunek. Numeracja w kolumnie A wskazuje, że w zestawieniu znajduje się 9 transakcji, pomimo że w rzeczywistości jest ich 6. Taki problem można z łatwością rozwiązać za pomocą sprytnej formuły.


• W komórce A2 wprowadź następującą formułę =JEŻELI(B2<>"";ILE.NIEPUSTYCH($B$2:B2);"")

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

24 | S t r o n a


Numery zostały dopasowane do liczby wypełnionych wierszy, co przedstawia kolejny rysunek.

Trik 20. Ochrona formuł w arkuszu

Przykład Jeżeli z utworzonego przez Ciebie arkusza będą korzystały inne osoby, warto zabezpieczyć się przed przypadkowym skasowaniem lub nadpisaniem formuł. Taką ochroną nałożysz bardzo szybko. W przykładowym arkuszu wypełnione powinny zostać komórki z obszaru B2:D6. Pozostałe komórki, a w szczególności formuły nie powinny zostać naruszone.


• W pierwszej kolejności zaznacz komórki, które będą uzupełniane przez inne osoby. W przykładzie B2:D6. Jeżeli zakresy nie przylegają do siebie, podświetlaj je z wciśniętym klawiszem Ctrl.

• Wywołaj okno formatowania komórek (skrót Ctrl + 1).

25 | S t r o n a


• W wyświetlonym oknie przejdź do zakładki Ochrona. • Wyłącz opcję Zablokuj i zatwierdź, klikając OK.

Gdy komórki przeznaczone do edycji zostały odblokowane, możesz nałożyć ochronę na arkusz.

• Uaktywnij kartę Recenzja i w grupie poleceń Zmiany kliknij Chroń arkusz.

• Zaznacz pole Chroń skoroszyt i zawartość zablokowanych komórek.

• W polu poniżej ustaw hasło ochrony arkusza (w razie potrzeby). • Wyłącz opcję Zaznaczanie zablokowanych komórek i zatwierdź OK.

Trik 21. Wygodna praca z wieloarkuszowym raportem

Przykład Jeżeli w skoroszycie znajduje się kilkadziesiąt arkuszy, to przełączanie ich za pomocą strzałek nawigacji nie jest zbyt wygodnym sposobem pracy. Skorzystaj z wygodniejszej metody.


• Prawym przyciskiem myszy kliknij jeden z przycisków nawigacji u dołu okna obok karty arkusza. Pojawi się lista arkuszy w układzie pionowym.

• Lewym przyciskiem myszy wybierz nazwę, a zostaniesz przeniesiony do odpowiedniego arkusza.

26 | S t r o n a


Trik 22. Efektowne wyróżnienie ważnej wartości

Przykład Niekiedy potrzebujesz wyróżnić pewną wartość w taki sposób, aby od razu przyciągnąć wzrok użytkownika arkusza. Proponujemy wykorzystać do tego celu autokształt.


• W pierwszej kolejności wybierz kształt i wstaw go do arkusza. W tym celu uaktywnij kartę Wstawianie i w sekcji Ilustracje wybierz Kształty.

• Narysuj w arkuszu obiekt i sformatuj go według swoich potrzeb. • Teraz zaznacz go, kliknij na pasku formuły, wpisz znak równości a

następnie wskaż komórkę, która zawiera ważną wartość (np. kwotę). • Zatwierdź Enterem.

Uzyskasz oczekiwany efekt jak na kolejnym rysunku.

27 | S t r o n a


Trik 23. Własna lista autowypełniania

Przykład Autowypełniania używa się najczęściej do wstawiania numeracji porządkowej tabel lub do kopiowania formuł. Wpisujesz pierwszy element i przeciągasz prawy dolny róg komórki w dół. Excel sam wówczas określa, że wypełnianych komórkach mają się np. pojawić kolejne liczby lub formuły z dopasowanymi odwołaniami. Okazuje się, że dość łatwo można przygotować własną listę autowypełniania.


• Uruchom okno opcji Excela. Przejdź do zakładki Plik i wybierz Opcje. • Zaznacz kategorię Zaawansowane i w prawej części okna odszukaj

sekcję Ogólne. • Kliknij przycisk Edytuj listy niestandardowe. • W wyświetlonym oknie zaznacz pozycję NOWA LISTA. • W okienku Lista wpisów wprowadź w osobnych wierszach elementy

listy niestandardowej (np. nazwy miast).

• Kliknij Dodaj i zatwierdź OK. Od tego momentu, gdy wpiszesz do arkusza jedno z miast i skorzystasz z uchwytu wypełnienia, do kolejnych komórek Excel wprowadzi nazwy kolejnych miast zgodnie z Twoją listą niestandardową.

28 | S t r o n a


Trik 24. Przenoszenie formatu z komórki na komórkę

Przykład Jeżeli chcesz zachować spójny wygląd swoich zestawień i raportów, odpowiednie grupy komórek powinny mieć takie samo formatowanie. Oczywiście nie będziesz za każdym razem klikać w opcje formatowania i określać krój czcionki, kolor, wypełnienie tła itp. Do przenoszenia formatowania warto wykorzystać odpowiednie narzędzie.


• Zaznacz odpowiednio sformatowaną komórkę bazową. • Na karcie Narzędzia główne wybierz Malarz formatów. W tym

momencie Twoja komórka bazowa zostanie otoczona przerywanym obramowaniem, a wskaźnik myszy przybierze kształt pędzelka.

• Kliknij komórkę docelową, na którą chcesz przenieść formatowanie. Gotowe!

Warto wiedzieć Jeżeli chcesz seryjnie zastosować malarza formatów na nieprzylegających komórkach, w menu kliknij szybko dwukrotnie ikonkę tego narzędzia. Będziesz mógł przenosić format na wiele komórek. Aby wyłączyć tryb przenoszenia formatu, wciśnij klawisz Esc.

29 | S t r o n a


Trik 25. Poprawa czytelności długich tabel

Przykład Załóżmy, że w arkuszu znajduje się kilkaset transakcji sprzedaży posortowanych wg dat w kolumnie A (patrz rysunek). Przeglądanie tak długiego zestawienia jest dosyć uciążliwe. Pokażemy Ci, jak automatycznie oddzielić linią transakcje odnoszące się do poszczególnych dni.


• Zaznacz zakres komórek ze wszystkimi transakcjami (w przykładzie A2:C10).

• Uaktywnij kartę Narzędzia główne i wskaż Formatowanie warunkowe/Nowa reguła.

• Wybierz opcję Użyj formuły do określenia komórek, które należy sformatować.

• Wpisz formułę: =$A2<>$A3

• Kliknij przycisk Formatuj i w wyświetlonym oknie przejdź do zakładki Obramowanie.

• Uaktywnij obramowanie dolne i zatwierdź, klikając OK. Okno z warunkiem formatowanie powinno wyglądać jak na kolejnym rysunku.

30 | S t r o n a


Po zatwierdzeniu OK uzyskasz oczekiwany efekt, jak to przedstawia ostatni rysunek.

Trik 26. Hurtowe uzupełnienie pominiętych komórek

Przykład Czasami możesz napotkać zestawienia, w którym znajdują się luki. Przykład prezentuje kolejny rysunek. Puste komórki powinny być uzupełnione nazwami miast z komórki powyżej. Aby uniknąć ręcznego wprowadzania, zastosuj trik.

31 | S t r o n a



• Zaznacz obszar kolumny A, w którym powinny znajdować się nazwy miast. Podświetlanie rozpocznij od komórki A2.

• Na klawiaturze wciśnij klawisz funkcyjny F5. • W oknie dialogowym, które się pojawi, wskaż przycisk Specjalnie. • Zaznacz pole Puste i zatwierdź, klikając OK. W kolumnie zostaną

podświetlone tylko puste komórki, które powinny zostać uzupełnione nazwami miast.

• Nie zmieniaj zaznaczenia komórek, wpisz znak równości i wciśnij na klawiaturze klawisz Strzałka w górę. W komórce A3 powinno pojawić się odwołanie =A2.

• Wciśnij kombinację klawiszy Ctrl + Enter. Gotowe! Wszystkie puste komórki zostały uzupełnione.

32 | S t r o n a


Trik 27. Wyświetlenie daty razem z nazwą dnia tygodnia

Przykład Dla lepszej orientacji w zestawieniach związanych z datami korzystnie jest wyświetlać daty razem z nazwami dnia tygodnia. Oczywiście nie będziemy ich wprowadzać ręcznie. Wystarczy zastosowanie formatu niestandardowego.


• Zaznacz zakres komórek zawierający daty. • Wciśnij kombinację klawiszy Ctrl + 1. • W wyświetlonym oknie przejdź do zakładki Liczby. • Zaznacz kategorię Niestandardowe i w polu Typ wpisz:

dddd rrrr-mm-dd

• Zatwierdź, klikając OK. W komórkach z datami pojawią się nazwy dni tygodnia.

33 | S t r o n a


Warto wiedzieć Jeżeli chcesz, aby w komórce nazwa dnia tygodnia była wyrównana do lewej, a data do prawej, wówczas zastosuj format dddd * rrrr-mm-dd Taki sposób wyświetlania dat może być dla Ciebie czytelniejszy.

Trik 28. Korekta wartości czasu

Przykład Przyjmijmy, że w arkuszu znajduje się kilkadziesiąt wartości czasu. W pewnym momencie okazało się, że są błędnie wpisane, ponieważ powinny być o 2 godziny późniejsze. Czy to oznacza, że czeka Cię ręczne wpisywanie godzin od nowa? Niekoniecznie!


• Zaznacz komórkę C3. • Wpisz do niej następującą formułę:

=CZAS(GODZINA(B3)+2;MINUTA(B3);SEKUNDA(B3))

• Następnie skopiuj ją do komórek poniżej.

34 | S t r o n a


Trik 29. Dowolna liczba przedstawiona za pomocą cyfr

rzymskich

Przykład Cyfry rzymskie to nie jest Twoja mocna strona? Żaden problem. Jeżeli potrzebujesz zamienić zapis liczby z arabskiego na rzymski, w Excelu wykonasz to błyskawicznie.


• Wprowadź do komórki formułę opartą na funkcji RZYMSKIE, np.:

=RZYMSKIE(B3)

• Następnie skopiuj ją do komórek poniżej.

Przykładowe zastosowanie w arkuszu przedstawia rysunek.

35 | S t r o n a


Trik 30. Problem z podsumowaniem czasu

przekraczającym 24H

Przykład Chcesz podsumować łączny czas nadgodzin wszystkich pracowników? Możesz mieć problem z tym obliczeniem, jeżeli suma przekracza 24 godziny (patrz rysunek). Zastosuj ten trik, aby zawsze otrzymywać poprawne wyniki.


• Zaznacz komórkę zawierającą podsumowanie i wciśnij kombinację klawiszy Ctrl + 1.

• W wyświetlonym oknie przejdź do zakładki Liczby. • Zaznacz kategorię Niestandardowe i w polu Typ wpisz:

[gg]:mm:ss Po zatwierdzeniu OK, w arkuszu pojawi się poprawna suma.

30 najlepszych trików w Excelu - Szkolenie Excel · 2013-02-04 · 5 | Strona Trik 2. Szybkie...

Documents

Transcript of 30 najlepszych trików w Excelu - Szkolenie Excel · 2013-02-04 · 5 | Strona Trik 2. Szybkie...