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 pojedynczego wiersza 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.
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 potrafi wyczarować wyobraźnia. Jako (bardzo) krótka lista funkcji, które można wykonać za pomocą makra:
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 użyciu zerowego kodowania - tak jak to zrobimy tutaj.
Aby utworzyć makro, przejdź do Widok> Makra> Zapisz makro.
Przypisz makro jako nazwę (bez spacji) i kliknij OK.
Gdy to zrobisz, wszystko twoich działań są rejestrowane - każda zmiana komórki, akcja przewijania, 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 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):
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świetlać odpowiednie podsumowania.
Teraz chcemy uzyskać dane podsumowujące dla całego arkusza, więc stosujemy jeszcze kilka obliczeń:
Odpowiednio:
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:
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 starszy plik szablonu (XLT), który umożliwi uruchamianie makr.
Po zapisaniu pliku jako szablonu zamknij program 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:
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. 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 zinterpretował (np. Ogranicznik, obecne nagłówki itp.).
Po zaimportowaniu naszych danych przejdź do menu Makra (na karcie Widok) i wybierz 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 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.
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, który został zarejestrowany na podstawie naszych działań 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.
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:
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".
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
