Naucz się używać makr Excela do automatyzacji żmudnych zadań

Jedną z potężniejszych, ale rzadko używanych funkcji programu Excel jest możliwość łatwego tworzenia zautomatyzowanych zadań i niestandardowej logiki w makrach. Makra stanowią idealny sposób na zaoszczędzenie czasu na przewidywalnych, powtarzalnych zadaniach, a także na standaryzację formatów dokumentów - wiele razy bez konieczności pisania pojedynczej linii kodu.

Jeśli jesteś ciekawy, jakie są makra i jak je tworzyć, nie ma problemu - przeprowadzimy Cię przez cały proces.

Uwaga: ten sam proces powinien działać w większości wersji pakietu Microsoft Office. Zrzuty ekranu mogą wyglądać nieco inaczej.

Co to jest makro?

Makro Microsoft Office (ponieważ ta funkcjonalność dotyczy wielu aplikacji MS Office) to po prostu kod Visual Basic for Applications (VBA) zapisany w dokumencie. Dla porównywalnej analogii pomyśl o dokumencie jako HTML i makrze jako Javascript. W dużym stopniu w ten sam sposób, w jaki JavaScript może manipulować HTML na stronie internetowej, makro może manipulować dokumentem.

Makra są niesamowicie potężne i potrafią zrobić prawie wszystko, co Twoja wyobraźnia potrafi wyczarować. Jako (bardzo) krótka lista funkcji, które można wykonać za pomocą makra:

Zastosuj styl i formatowanie.
Manipuluj danymi i tekstem.
Komunikuj się ze źródłami danych (bazą danych, plikami tekstowymi itp.).
Twórz zupełnie nowe dokumenty.
Dowolna kombinacja, w dowolnej kolejności, dowolnego z powyższych.

Tworzenie makra: objaśnienie za pomocą przykładu

Zaczynamy od pliku CSV odmiany ogrodu. Nic specjalnego tutaj, tylko 10 × 20 zestaw liczb od 0 do 100 zarówno z nagłówkiem wiersza i kolumny. Naszym celem jest stworzenie dobrze sformatowanego, prezentowalnego arkusza danych, który zawiera podsumowania dla każdego wiersza.

Jak napisaliśmy powyżej, makro to kod VBA, ale jedną z miłych rzeczy na temat programu Excel jest możliwość tworzenia / rejestrowania ich przy zerowym kodowaniu - tak jak my to zrobimy.

Aby utworzyć makro, przejdź do Widok> Makra> Nagraj makro.

Przypisz makro jako nazwę (bez spacji) i kliknij OK.

Gdy to zrobisz, wszystko Twoje działania są rejestrowane - każda zmiana komórki, przewijanie, zmiana rozmiaru okna, możesz to nazwać.

Istnieje kilka miejsc wskazujących, że Excel jest trybem nagrywania. Jednym z nich jest oglądanie menu Makro i zwracanie uwagi, że funkcja zatrzymania nagrywania zastąpiła opcję Record Macro.

Drugi znajduje się w prawym dolnym rogu. Ikona "stop" wskazuje, że jest w trybie makro, a naciśnięcie tego przycisku zatrzyma nagrywanie (podobnie, gdy nie jest w trybie nagrywania, ta ikona będzie przyciskiem Record Macro, którego można użyć zamiast przechodzenia do menu Makra).

Teraz, gdy nagrywamy nasze makro, zastosujmy nasze obliczenia sumaryczne. Najpierw dodaj nagłówki.

Następnie zastosuj odpowiednie formuły (odpowiednio):

= SUMA (B2: K2)
= AVERAGE (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

Teraz zaznacz wszystkie komórki obliczeniowe i przeciągnij długość wszystkich wierszy danych, aby zastosować obliczenia do każdego wiersza.

Gdy to zrobisz, każdy wiersz powinien wyświetlić odpowiednie podsumowania.

Teraz chcemy uzyskać dane podsumowujące dla całego arkusza, więc stosujemy jeszcze kilka obliczeń:

Odpowiednio:

= SUMA (L2: L21)
= AVERAGE (B2: K21) *Należy to obliczyć dla wszystkich danych, ponieważ średnia ze średnich rzędów niekoniecznie jest równa średniej wszystkich wartości.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAN (B2: K21) * Obliczono dla wszystkich danych z tego samego powodu, co powyżej.

Po wykonaniu obliczeń zastosujemy styl i formatowanie. Najpierw zastosuj ogólne formatowanie liczb we wszystkich komórkach, wykonując Zaznacz wszystko (Ctrl + A lub kliknij komórkę między nagłówkiem wiersza i kolumny) i wybierz ikonę "Styl przecinania" w menu głównym.

Następnie zastosuj trochę formatowania wizualnego do nagłówków wierszy i kolumn:

Pogrubienie.
Wyśrodkowany.
Kolor wypełnienia tła.

I wreszcie, zastosuj jakiś styl do sum.

Kiedy wszystko się skończy, oto jak wygląda nasz arkusz danych:

Ponieważ jesteśmy zadowoleni z wyników, zatrzymaj nagrywanie makra.

Gratulacje - właśnie utworzyłeś makro Excel.

Aby użyć naszego nowo zarejestrowanego makra, musimy zapisać nasz skoroszyt programu Excel w formacie pliku włączonego makra. Jednak zanim to zrobimy, najpierw musimy wyczyścić wszystkie istniejące dane, aby nie były one osadzone w naszym szablonie (przy czym za każdym razem, gdy używamy tego szablonu, zaimportujemy najbardziej aktualne dane).

Aby to zrobić, zaznacz wszystkie komórki i usuń je.

Po wyczyszczeniu danych (ale makr nadal zawartych w pliku Excel), chcemy zapisać plik jako plik szablonu obsługującego makra (XLTM). Ważne jest, aby pamiętać, że zapisanie tego jako pliku standardowego szablonu (XLTX) spowoduje, że makra będą nie być w stanie z niego uciekać. Alternatywnie można zapisać plik jako plik starszego szablonu (XLT), który umożliwi uruchamianie makr.

Po zapisaniu pliku jako szablonu zamknij program Excel.

Używanie makra programu Excel

Zanim omówimy, w jaki sposób możemy zastosować to nowo zarejestrowane makro, ważne jest ogólne omówienie kilku punktów dotyczących makr:

Makra mogą być złośliwe.
Zobacz punkt powyżej.

Kod VBA jest w rzeczywistości dość potężny i może manipulować plikami poza zakresem bieżącego dokumentu. Na przykład makro może zmieniać lub usuwać losowe pliki w folderze Moje dokumenty. W związku z tym ważne jest, aby upewnić się tylko uruchamiać makra z zaufanych źródeł.

Aby użyć naszego makra do formatu danych, otwórz plik szablonu Excel, który został utworzony powyżej. Gdy to zrobisz, zakładając, że masz włączone standardowe ustawienia zabezpieczeń, na górze skoroszytu zobaczysz ostrzeżenie, że makra są wyłączone. Ponieważ ufamy makro utworzonemu przez nas, kliknij przycisk "Włącz treść".

Następnie zaimportujemy najnowszy zestaw danych z pliku CSV (jest to źródło, którego użyliśmy do utworzenia naszego makra).

Aby ukończyć import pliku CSV, konieczne może być ustawienie kilku opcji, aby program Excel poprawnie je interpretował (np. Ogranicznik, obecne nagłówki itp.).

Po zaimportowaniu naszych danych przejdź do menu Makra (w zakładce Widok) i wybierz Zobacz makra.

W wynikowym oknie dialogowym widzimy makro "FormatData", które zapisaliśmy powyżej. Wybierz i kliknij Uruchom.

Po uruchomieniu możesz zobaczyć kursor przeskakiwania na kilka chwil, ale tak jak to widzisz dane są manipulowane dokładnie jak to nagrywaliśmy. Kiedy wszystko jest powiedziane i zrobione, powinno wyglądać tak samo jak nasze oryginalne - z wyjątkiem różnych danych.

Looking Under the Hood: Co sprawia, że praca w makrze

Jak już wspomnieliśmy kilka razy, makro jest sterowane przez kod Visual Basic for Applications (VBA). Kiedy "nagrywasz" makro, Excel faktycznie tłumaczy wszystko, co robisz, na odpowiednie instrukcje VBA. Mówiąc prościej - nie musisz pisać żadnego kodu, ponieważ Excel pisze dla ciebie kod.

Aby wyświetlić kod, który powoduje uruchomienie naszego makra, w oknie dialogowym Makra kliknij przycisk Edytuj.

W oknie, które się otworzy, wyświetlany jest kod źródłowy zapisany podczas akcji tworzenia naszych makr. Oczywiście możesz edytować ten kod, a nawet tworzyć nowe makra całkowicie w oknie kodu. Chociaż akcja nagrywania używana w tym artykule będzie prawdopodobnie pasować do większości potrzeb, bardziej dostosowane akcje lub warunkowe działania wymagałyby edycji kodu źródłowego.

Podążanie za naszym przykładem o jeden krok dalej ...

Hipotetycznie, zakładamy, że nasz plik danych źródłowych data.csv jest tworzony przez zautomatyzowany proces, który zawsze zapisuje plik w tej samej lokalizacji (np. C: \ Data \ data.csv jest zawsze najnowszymi danymi). Proces otwierania tego pliku i importowania go można łatwo przekształcić w makro:

Otwórz plik szablonu Excel zawierający nasze makro "FormatData".
Zapisz nowe makro o nazwie "LoadData".
Podczas nagrywania w trybie makro zaimportuj plik danych tak jak zwykle.
Po zaimportowaniu danych zatrzymaj nagrywanie makra.
Usuń wszystkie dane komórki (wybierz wszystko, a następnie usuń).
Zapisz zaktualizowany szablon (pamiętaj, aby użyć formatu szablonu obsługującego makra).

Gdy to zrobisz, za każdym razem, gdy otworzy się szablon, pojawią się dwa makra - jeden, który ładuje nasze dane, a drugi formatuje.

Jeśli naprawdę chcesz zabrudzić sobie ręce za pomocą edycji kodu, możesz łatwo połączyć te działania w jedno makro, kopiując kod wygenerowany z "LoadData" i wstawiając go na początku kodu z "FormatData".

Pobierz ten szablon

Dla Twojej wygody umieściliśmy zarówno szablon Excela opracowany w tym artykule, jak i przykładowy plik danych, na którym możesz się bawić.

Pobierz Excel Macro Template z How-To Geek