9Sep
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.
Czym jest makro?
Makr 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 całkowicie nowe dokumenty.
- Dowolna kombinacja, w dowolnej kolejności, dowolnego z powyższych.
Tworzenie makra: Wyjaśnienie za pomocą przykładu
Rozpoczynamy 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 opracowanie 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 / zapisywania ich przy użyciu zerowego kodowania - tak jak to zrobimy tutaj.
Aby utworzyć makro, przejdź do opcji Widok & gt;Makra & gt;Nagraj makro.
Przypisz makro jako nazwę( bez spacji) i kliknij OK.
Gdy to zrobisz, wszystkie twoich działań są rejestrowane - każda zmiana komórki, akcja przewijania, zmiana rozmiaru okna, ty go nazwij.
Istnieje kilka miejsc wskazujących, że Excel jest trybem nagrywania. Jednym z nich jest oglądanie menu Makro i zwracanie uwagi, że Zatrzymanie nagrywania zastąpiło 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 zapisu, 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)
- = ŚREDNIA( 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.
Po wykonaniu tej czynności każdy wiersz powinien wyświetlać odpowiednie podsumowania.
Teraz chcemy uzyskać dane podsumowujące dla całego arkusza, więc stosujemy kilka dodatkowych obliczeń:
Odpowiednio:
- = SUMA( L2: L21)
- = ŚREDNIA( B2: K21) * Musi być obliczona dla wszystkichdane, 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 przecięcia" w menu głównym.
Następnie zastosuj trochę formatowania wizualnego do nagłówków wierszy i kolumn:
- Bold.
- Wyśrodkowany.
- Kolor wypełnienia tła.
I na koniec zastosuj jakiś styl do sum.
Kiedy wszystko jest gotowe, 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ą mogły zostać uruchomione z , a nie .Alternatywnie można zapisać plik jako starszy plik szablonu( XLT), który umożliwi uruchamianie makr.
Po zapisaniu pliku jako szablonu zamknij program Excel.
Korzystanie z makra programu Excel
Przed omówieniem sposobu zastosowania nowo zarejestrowanego makra ważne jest omówienie kilku punktów dotyczących makr w ogóle: Makra
- mogą być złośliwe.
- Patrz 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 zmienić lub usunąć losowe pliki w folderze Moje dokumenty. Jako takie, ważne jest, aby upewnić się, że 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 dokończyć import pliku CSV, konieczne może być ustawienie kilku opcji, aby program Excel poprawnie je zinterpretował( np. Ogranicznik, obecne nagłówki itp.).
Po zaimportowaniu danych wystarczy przejść do menu Makra( na karcie Widok) i wybrać opcję Wyświetl makra.
W wynikowym oknie dialogowym widzimy makro "FormatData", które zapisaliśmy powyżej. Wybierz i kliknij Uruchom.
Po uruchomieniu możesz zobaczyć kursor przeskakujący na kilka chwil, ale tak jak to zobaczysz, dane będą przetwarzane dokładnie podczas nagrywania. 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 makro działa
Jak wspomnieliśmy już 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, który został zarejestrowany na podstawie naszych akcji podczas tworzenia makra. 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 działania lub warunkowe działania wymagałyby edycji kodu źródłowego.
Przenoszenie naszego przykładu 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 najbardziejnajnowsze dane).Proces otwierania tego pliku i importowania go można łatwo przekształcić w makro:
- Otwórz plik szablonu Excel zawierający nasze makro "FormatData".
- Zarejestruj 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 wszystkie, a następnie usuń).
- Zapisz zaktualizowany szablon( pamiętaj, aby użyć formatu szablonu obsługującego makra).
Po wykonaniu tej czynności, za każdym razem, gdy szablon zostanie otwarty, 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 zawarliśmy zarówno szablon Excela wyprodukowany w tym artykule, jak i przykładowy plik danych, na którym możesz się bawić.
Pobierz szablon makr programu Excel z As-To Geek