Automatyzacja programu Excel - Automatyzuj zadania, pisząc lub rejestrując makra

Automatyzacja programu Excel (spis treści)

• Wprowadzenie do automatyzacji w Excelu
• Przykłady automatyzacji programu Excel

Wprowadzenie do automatyzacji w Excelu

Automatyzacja w programie Excel zasadniczo obejmuje kodowanie w języku VBA (Visual Basic for Applications), który jest odmianą języka Visual Basic do integracji z aplikacjami Microsoft Office. Jest to język programowania oparty na programie Excel, który pomaga użytkownikowi automatyzować zadania poprzez pisanie lub nagrywanie makr. Makro to kod działający w środowisku programu Excel i służy do automatyzacji powtarzalnych zadań, tj. Jeśli zestaw akcji musi zostać powtórzony kilka razy, program Excel może nagrać te akcje i wygenerować makro zawierające kod, aby powtórzyć te kroki. Po zarejestrowaniu makra zestaw działań można powtórzyć dowolną liczbę razy, uruchamiając zarejestrowane makro. Kod VBA wygenerowany przez makro jest umieszczany w module, który można przeglądać za pomocą VBE.

Niektóre dodatki automatyzacji, takie jak XLTools, AutoMacro, służą do automatyzacji własnych rutynowych zadań Excela bez makr.

Przykłady automatyzacji programu Excel

Zobaczmy poniżej, jak można wykorzystać zarejestrowane makro do automatyzacji codziennego raportu.

Możesz pobrać ten szablon Excel Automation tutaj - Szablon Excel Automation

Przykład # 1 - Automatyzacja poprzez rejestrację makr

Powiedzmy, że mamy zestaw danych z pewnymi liczbami i chcemy mieć dla nich podsumowujące statystyki wierszy (takie jak suma, średnia, minimum, maksimum). Ponadto chcemy zastosować pewien styl formatowania do zestawu danych. Zapisujemy więc te kroki w makrze, aby za każdym razem, gdy mamy nowy zestaw danych, który wymaga wykonania na nim tych samych operacji, to zarejestrowane makro można po prostu uruchomić, aby wykonać to zadanie.

Zobaczmy, jak wygląda zestaw danych:

Teraz, aby wykonać wymagane operacje i zapisać je w makrze, wykonamy następujący proces:

• Przejdź do „Deweloper” i kliknij „Nagrywaj makro”:

• Po wykonaniu tej czynności wyskakujące okno otwiera się w następujący sposób.

• Nadaj makrze odpowiednią nazwę i wybierz „Osobisty skoroszyt makr” w menu rozwijanym „Zapisz makro w”, a następnie kliknij OK.

• Teraz możemy wykonać wymagane operacje na zbiorze danych w następujący sposób.

• Po użyciu formuły wynik pokazano poniżej.

• Po zastosowaniu średniej formuły w komórce H2 wynik pokazano poniżej.

• Po zastosowaniu formuły MIN w komórce I2 wynik pokazano poniżej.

• Po zastosowaniu formuły MAX w komórce J2 wynik pokazano poniżej.

• Teraz przeciągnij je, aby uzyskać statystyki dla wszystkich wierszy.

• Teraz, gdy wszystkie te operacje są wykonywane, przestajemy rejestrować makro jak poniżej.

Uwaga: Zaleca się używanie klawiszy klawiatury podczas rejestrowania makra. Myszy należy używać tylko wtedy, gdy jest to konieczne.

Przykład # 2 - Korzystanie z zarejestrowanych makr

Powiedzmy teraz, że mamy inny podobny zestaw danych, taki jak ten w „Przykładzie 2” pliku Excel, który wymaga tych samych operacji i formatowania. Aby to zrobić, po prostu uruchamiamy powyższe makro i nasze zadanie zostanie wykonane.

• Przejdź do „Deweloper” i kliknij „Makra”.

• Teraz wybierz makro: „Marks_Macro1” i kliknij Uruchom.

• Teraz po kliknięciu Uruchom zestaw danych będzie wyglądał następująco.

Możemy więc zobaczyć na powyższym zrzucie ekranu, że po uruchomieniu zarejestrowanego makra o nazwie „Marks_macro1” zautomatyzowaliśmy, a zatem zreplikowaliśmy operacje wykonane na Arkuszu 1 w Przykładzie 1 na Arkuszu 2. Jest to automatyzacja poprzez rejestrowanie makra.

Tego rodzaju automatyzacja może być używana do prostych powtarzalnych operacji, takich jak codzienne formatowanie raportu, komunikacja z bazami danych, łączenie plików, tworzenie nowych dokumentów, manipulowanie danymi itp.

Jak makra są zapisywane jako procedury VBA Code w VBE

• Dostęp do edytora Visual Basic można uzyskać, naciskając klawisze Alt + F11, aby otworzyć okno edytora Visual Basic.

• Po wykonaniu tej czynności otworzy się okno. Teraz w okienku „Project-VBAProject” będziemy przechowywać makro w jednym z modułów w następujący sposób.

• Dzięki VBA Macro Recorder nie musimy kodować makra, a jedynie go nagrać.
• Makra mogą być przechowywane w osobistym skoroszycie, który jest ukrytym skoroszytem, ​​który otwiera się w tle przy każdym uruchomieniu programu Excel. Zapisanie makr w osobistym skoroszycie powoduje, że makro jest zawsze dostępne, ponieważ osobisty skoroszyt nie jest specyficzny dla systemu lub pliku.
• Podczas rejestrowania makr program Excel zawsze tworzy podprocedurę (nie procedurę). Zapisane kody są więc przydatne tylko dla prostych makr. Bardziej złożone makra można generować, pisząc kody VBA, włączając menu „Deweloper” w programie Excel.
• Rejestrowanie makr w celu automatyzacji zadań może mieć pewne ograniczenia. Nie zawsze może być możliwe rejestrowanie makr, które działają dokładnie tak, jak chcemy. Kod makr często wymaga pewnych ręcznych aktualizacji, więc w takim przypadku można użyć AutoMacro, który jest dodatkiem, który instaluje się bezpośrednio w VBE. Wymaga bardzo małej wiedzy na temat kodowania VBA.

O czym należy pamiętać o automatyzacji programu Excel

• Niektóre narzędzia używane do automatyzacji programu Excel bez znajomości kodowania to Power Query, VBA Macro Recorder i niektóre dodatki do automatyzacji, takie jak Auto Macro, XLTools. XLTools to dodatek do automatyzacji, który może nawet integrować program Excel z innymi aplikacjami i narzędziami, takimi jak SQL, Python. Można go używać do pisania poleceń w prostych tabelach Excela.
• Makra można nawet dodawać do menu funkcji programu Excel za pomocą przycisku, dzięki czemu są one dostępne dla użytkownika, podobnie jak wbudowane funkcje programu Excel.
• Nagrywanie makra pomaga wykonywać proste powtarzalne zadania, a także, gdy piszemy złożone kody makr poprzez edycję zarejestrowanych kodów.

Polecane artykuły

Jest to przewodnik po automatyzacji programu Excel. Tutaj omawiamy sposób automatyzacji zadań przez pisanie lub rejestrowanie makr wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

1. Pole nazwy w programie Excel
2. Obliczenia Excela
3. Szablon mapy drogowej w programie Excel
4. Pasujące kolumny w programie Excel
5. Przykłady makr programu Excel