Najbardziej przydatne makra Excela

Jeśli dotychczas nie korzystałeś jeszcze z makr, rezygnujesz z jednego z najpotężniejszych narzędzi Excela. Makra pozwalają zaoszczędzić sporo czasu, wyręczając użytkownika w wykonywaniu często powtarzających się czynności. Nie musisz być programistą, ani znać podstaw języka VBA (Visual Basic Applications), aby tworzyć własne makra do stosowania w arkuszu kalkulacyjnym. W Excelu 2013 czynność ta jest równie łatwa co ręczne wprowadzanie poleceń. Poniżej opisujemy, jak zautomatyzować za pomocą makr pięć najczęstszych zadań w Excelu.

Podstawy tworzenia makr

Makra tworzy się poprzez ich rejestrowanie. Aby wykonać tę czynność, kliknij polecenie Zarejestruj makro na karcie Widok.

Zobacz również:

• Excel staje się e-sportem. Tak, chodzi o arkusz kalkulacyjny

Przed rozpoczęciem rejestrowania należy wypełnić pola w oknie wg poniższego opisu.

• Nazwa makra – pierwszy znak musi być literą, pozostała część nazwy musi składać się z dowolnych liter, cyfr i/lub podkreśleń;
• Klawisz skrótu – są dostępne skróty klawiszowe [Ctrl J] i [Ctrl M]. Wybierając inny skrót, nadpiszesz inne makro, które było przypisane do niego do tej pory;
• Przechowuj makro w – makra zapisane jako Nowy skoroszyt lub Ten skoroszyt działają tylko w obrębie tych skoroszytów. Aby makro było dostępne we wszystkich arkuszach i skoroszytach, trzeba zapisać je jako Skoroszyt makr osobistych;
• Opis – w razie potrzeby możesz wpisać tu krótki tekst objaśniający.

Makra służą do automatyzowania często ponawianych czynności, więc powinny być zaprojektowane tak, aby można było stosować je we wszystkich lub przynajmniej w większości arkuszy. Dlatego nie należy z góry określać zakresu komórek (np. C1, D1), chyba że wszystkie arkusze mają identyczną strukturę. Aby makro działało w różnych skoroszytach o zbliżonej strukturze, należy wyznaczyć zakres za pomocą klawiszy strzałek (wówczas adresy komórek nie odgrywają roli) i rozpoczynać za każdym razem od komórki A1.

Reorganizowanie i formatowanie importowanych danych

Dane importowane do Excela są dostępne przeważnie w formacie TSV (tabulator jako separator) lub CSV (przecinek lub spacja jako separator). Gdy otrzymujesz dwa tuziny takich plików miesięcznie, ręczne usunięcie niepożądanych danych i posortowanie wg określonych kryteriów trwa godzinami. Makro upora się z tym zadaniem w ciągu kilku sekund.

Otwórz skoroszyt CSV, utwórz nowe makro (patrz wyżej) i zarejestruj następujące czynności.

1. Umieść kursor w komórce A1. Przytrzymując naciśnięty klawisz [Ctrl], zaznacz kolumny, które chcesz usunąć. Wskaż menu Narzędzia główne | Usuń | Usuń kolumny arkusza.

2. Ponownie przytrzymaj naciśnięty klawisz [Ctrl] i zaznacz pozostałe kolumny. Kliknij menu Narzędzia główne | Format | Szerokość kolumny. Ustaw żądaną szerokość.

3. Zaznacz wszystkie dane w obrębie arkusza (klawisze [Ctrl Home] i [Ctrl A]). Wskaż menu Narzędzia główne | Sortuj i filtruj | Sortowanie niestandardowe. W polu listy Sortuj według wybierz kolumnę, według której ma być uporządkowany skoroszyt. W rubryce Sortowanie zaznacz opcję Wartości, a w rubryce Kolejność opcję Od A do Z.

4. Kliknij ikonę kwadratu w lewej części zielonego paska przy dolnej krawędzi okna. W ten sposób zatrzymasz rejestrowanie makra. Zapisz skoroszyt jako plik Excela. Następnie otwórz ponownie plik CSV, kliknij menu Deweloper | Makra, zaznacz zarejestrowanie przed chwilą makro i kliknij polecenie Uruchom. Cały skoroszyt zostanie sformatowany wg twoich upodobań w ciągu zaledwie kilku sekund.

Dzielenie kolumn zawierających imiona i nazwiska

Gdy masz do czynienia ze skoroszytami zawierającymi imiona i nazwiska w jednej kolumnie, a są ci potrzebne oddzielne kolumny z imionami i nazwiskami, ręczne sformatowanie zawartości okazuje się bardzo uciążliwe. Stosowne makro upora się z tym błyskawicznie, sortując dodatkowo zawartość i podając liczbę wpisów.

Otwórz dany skoroszyt Excela, utwórz nowe makro (patrz wyżej) i zarejestruj następujące czynności.

1. Ustaw kursor w pierwszej kolumnie i zaznacz zgromadzone w niej dane klawiszami [Ctrl A]. Wskaż menu Dane | Tekst jako kolumny. W nowym oknie zaznacz opcję Rozdzielany i kliknij przycisk Dalej. Na następnej stronie wybierz znak, którym są rozdzielone imiona i nazwiska w skoroszycie. Np. zaznacz pole wyboru Spacja. Kliknij przycisk Dalej, zaznacz opcję Tekst i potwierdź przyciskiem Zakończ.

2. Ustaw kursor w komórce A1 (klawisze [Ctrl Home]) i naciśnij klawisze [Ctrl A]. Wskaż menu Narzędzia główne | Sortuj i filtruj | Sortowanie niestandardowe. W polu listy Sortuj według zaznacz opcję Kolumna B (nazwiska). Kliknij przycisk Dodaj poziom i zaznacz opcję Kolumna A w polu listy Następnie według. W rubryce Sortowanie pozostaw domyślną opcję Wartości, a w rubryce Kolejność opcję Od A do Z.

3. Przeskocz do komórki A1. Naciśnij klawisze [Shift Strzałka w prawo], aby zaznaczyć komórki A1 i B1. Kliknij menu Narzędzia główne | Format | Szerokość kolumny, po czym wpisz wartość 15.

4. Powróć do komórki A1. Kliknij menu Narzędzia główne | Wstaw | Wstaw wiersze arkusza. Wpisz w pustej komórce A1 tytuł Łączna liczba osób:. Przeskocz do komórki B1 klawiszem [Strzałka w prawo], wpisz w niej =ILE.NIEPUSTYCH(, po czym naciśnij klawisze [Ctrl Strzałka w dół], [End], [Shift Strzałka w dół], [Enter], [Ctrl Home]. W komórce B1 pojawi się liczba osób figurujących w arkuszu.

5. Zatrzymaj rejestrowanie makra i zapisz je.

Dzielenie kolumn z dwoma imionami

Co począć, gdy na liście są osoby z dwoma imionami lub inicjałami? Poniższe makro wyodrębnia je i scala z pierwszym imieniem. W ten sposób uzyskasz jedną kolumnę z nazwiskami i drugą z imionami i/lub inicjałami. Otwórz skoroszyt Excela, utwórz nowe makro (patrz wyżej) i zarejestruj następujące czynności.

1. Zaznacz dane w kolumnie z imionami i nazwiskami, naciskając klawisze [Ctrl Home] i [Ctrl A]. Kliknij menu Dane | Tekst jako kolumny, a w nowym oknie zaznacz opcję Rozdzielany i kliknij przycisk Dalej. Zaznacz pole wyboru Spacja i potwierdź, klikając przyciski Dalej i Zakończ. Tabela nazwisk i imion składa się teraz z trzech kolumn.

2. Zaznacz wszystkie trzy kolumny i wskaż menu Narzędzia główne | Sortuj i filtruj | Sortowanie niestandardowe. W polu listy Sortuj według zaznacz opcję Kolumna C. (nazwiska). Kliknij przycisk Dodaj poziom i zaznacz opcję Kolumna A w polu listy Następnie według. W rubryce Sortowanie pozostaw domyślną opcję Wartości, a w rubryce Kolejność opcję Od A do Z.

3. Naciśnij klawisze [Ctrl Home] i dwa razy klawisz [Strzałka w prawo]. Następnie naciśnij [End], dwa razy [Strzałka w dół] i raz [Strzałka w prawo]. W ten sposób przeskoczysz do pierwszej pustej komórki w kolumnie C, a następnie do sąsiadującej komórki w kolumnie D. Wpisz w niej STOP. Naciśnij klawisze [Strzałka w górę], [End], [Strzałka w górę]. Wprowadź formułę =A1&" "&B1, po czym naciśnij [Enter] i [Strzałka w górę].

4. Naciśnij klawisze [Ctrl C], a następnie [Strzałka w dół]. Przytrzymaj naciśnięty klawisz [Shift] i naciskaj kolejno [End], [Strzałka w dół], [Strzałka w górę], [Enter]. Tak skopiujesz formułę. Naciśnij [Strzałka w górę], przytrzymaj naciśnięty klawisz [Shift] i naciśnij [End], a następnie [Strzałka w dół] i [Strzałka w górę]. Tak zaznaczysz obszar do napisu STOP.

5. Naciśnij klawisze [Ctrl C] i [Ctrl Home]. Wskaż menu Narzędzia główne | Wklej | Wklej specjalnie | Wartości. Naciśnij klawisz [Esc], klawisze [Ctrl Home] i dwa razy klawisz [Strzałka w prawo]. Przytrzymując naciśnięty klawisz [Shift], naciśnij [End], [Strzałka w dół], [Ctrl C], [Strzałka w lewo] i [Enter], aby skopiować nazwiska. Naciśnij klawisz [Strzałka w prawo], a następnie klawisze [Shift Strzałka w prawo].

6. Wskaż menu Narzędzia główne | Usuń | Usuń kolumny arkusza. Naciśnij klawisze [Ctrl Home]. Zapisz dokument, otwórz poprzedni skoroszyt i uruchom makro.

Wielokrotne wprowadzanie identycznego tekstu

Jeśli musisz kilka(naście) razy dziennie wpisywać ten sam tekst, warto utworzyć makro. Wykona tę czynność szybko i bezbłędnie. W poniższym przykładzie makro wkleja dane firmy i bieżącą datę.

Otwórz nowy skoroszyt, utwórz makro (patrz wyżej) i zarejestruj następujące czynności.

1. Naciśnij klawisze [Ctrl Home]. Przytrzymując naciśnięty [Shift], naciśnij dwa razy klawisz [Strzałka w prawo]. Wskaż menu Narzędzia główne | Scal i wyśrodkuj | Scal wszystkie. Wpisz w komórce A1 formułę =DZIŚ(). Naciśnij klawisze [Enter] i [Strzałka w górę]. Wskaż menu Narzędzia główne | Formatuj | Formatuj komórki | Data. Wybierz żądany format z listy i kliknij OK. Naciśnij dwa razy klawisz [Strzałka w dół].

2. Wprowadź powtarzający się element tekstowy, potwierdzając klawiszem [Enter] na końcu każdego wiersza. Naciśnij klawisz [Strzałka w dół]. Wskaż menu Deweloper | Zatrzymaj rejestrowanie. Usuń całą zawartość skoroszytu, rozdziel komórki A1-A3 i uruchom makro. Następnie zapisz skoroszyt.

Usuwanie pustych wierszy

Puste wiersze w arkuszu znacznie utrudniają sortowanie, przeliczanie i inne operacje. Poniższe makro usuwa je.

Otwórz skoroszyt z pustymi wierszami, utwórz nowe makro (patrz wyżej) i zarejestruj następujące czynności.

1. Naciśnij klawisze [Ctrl Home]. Choć naciśnięciem klawiszy [Ctrl A] nie zaznaczysz całej zawartości arkusza, gdy występują w nim puste wiersze, uda ci się to za pomocą poniższego makra.

2. Wskaż menu Narzędzia główne | Wstaw | Wstaw kolumny arkusza. Naciśnij [End], [Strzałka w dół], [Strzałka w prawo], [End], [Strzałka w górę]. Następnie naciśnij [Ctrl Shift Home], [Shift Strzałka w prawo], [Ctrl Shift Strzałka w prawo]. W ten sposób zaznaczysz cały obszar danych.

3. Wskaż menu Narzędzia główne | Znajdź i zaznacz | Przejdź do - specjalnie. Zaznacz opcję Puste i kliknij OK, a puste wiersze zostaną zaznaczone szarym tłem. Wskaż menu Narzędzia główne | Usuń | Usuń komórki | Przesuń komórki do góry. Puste wiersze znikną. Naciśnij klawisze [Ctrl Home], po czym wskaż menu Narzędzia główne | Usuń | Usuń kolumny arkusza, aby usunąć wstawioną uprzednio kolumnę.

4. Zatrzymaj rejestrowanie makra. Cofnij wszystkie operacje, po czym uruchom makro. Zapisz skoroszyt.