Spis treści
Excel posiada potężną, ale jednocześnie bardzo rzadko wykorzystywaną, możliwość tworzenia automatycznych sekwencji akcji za pomocą makr. Makro jest idealnym wyjściem, jeśli masz do czynienia z tym samym typem zadania, które powtarza się wiele razy. Na przykład przetwarzanie danych lub formatowanie dokumentów według standardowego szablonu. W takim przypadku nie potrzebujesz znajomości języków programowania.
Czy jesteś już ciekaw, czym jest makro i jak działa? Następnie śmiało śmiało – wtedy krok po kroku przeprowadzimy z Tobą cały proces tworzenia makra.
Co to jest makro?
Makro w Microsoft Office (tak, ta funkcjonalność działa tak samo w wielu aplikacjach pakietu Microsoft Office) to kod programu w języku programowania Visual Basic dla aplikacji (VBA) przechowywane w dokumencie. Aby było jaśniej, dokument Microsoft Office można porównać do strony HTML, a makro jest odpowiednikiem JavaScript. To, co JavaScript może zrobić z danymi HTML na stronie internetowej, jest bardzo podobne do tego, co makro może zrobić z danymi w dokumencie Microsoft Office.
Makra mogą zrobić prawie wszystko, co chcesz w dokumencie. Oto niektóre z nich (bardzo mała część):
- Zastosuj style i formatowanie.
- Wykonuj różne operacje na danych liczbowych i tekstowych.
- Korzystaj z zewnętrznych źródeł danych (plików baz danych, dokumentów tekstowych itp.)
- Utwórz nowy dokument.
- Wykonaj wszystkie powyższe w dowolnej kombinacji.
Tworzenie makra – praktyczny przykład
Weźmy na przykład najpopularniejszy plik CSV. Jest to prosta tabela 10×20 wypełniona liczbami od 0 do 100 z nagłówkami kolumn i wierszy. Naszym zadaniem jest przekształcenie tego zestawu danych w odpowiednio sformatowaną tabelę i wygenerowanie sum w każdym wierszu.
Jak już wspomniano, makro to kod napisany w języku programowania VBA. Ale w Excelu możesz stworzyć program bez pisania linijki kodu, co zrobimy teraz.
Aby utworzyć makro, otwórz Zobacz i wysłuchaj (Typ) > Makra (Makro) > Nagraj makro (Nagrywanie makr…)
Nadaj swojemu makro nazwę (bez spacji) i kliknij OK.
Począwszy od tego momentu, WSZYSTKIE Twoje działania na dokumencie są rejestrowane: zmiany w komórkach, przewijanie tabeli, a nawet zmiana rozmiaru okna.
Excel sygnalizuje, że tryb nagrywania makr jest włączony w dwóch miejscach. Najpierw w menu Makra (Makra) – zamiast napisu Nagraj makro (Nagrywam makro…) pojawił się wiersz Zatrzymaj nagrywanie (Zatrzymaj nagrywanie).
Po drugie, w lewym dolnym rogu okna Excela. Ikona Stop (mały kwadrat) wskazuje, że tryb nagrywania makro jest włączony. Kliknięcie go zatrzyma nagrywanie. I odwrotnie, gdy tryb nagrywania nie jest włączony, w tej lokalizacji znajduje się ikona umożliwiająca nagrywanie makr. Kliknięcie go da ten sam wynik, co włączenie nagrywania za pomocą menu.
Teraz, gdy włączony jest tryb nagrywania makr, przejdźmy do naszego zadania. Przede wszystkim dodajmy nagłówki dla danych podsumowujących.
Następnie wprowadź formuły do komórek zgodnie z nazwami nagłówków (podane są warianty formuł dla wersji angielskiej i Excel, adresy komórek są zawsze literami i cyframi łacińskimi):
- =SUMA(B2:K2) or =SUMA(B2:K2)
- =ŚREDNIA(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAKS(B2:K2) or =MAKS(B2:K2)
- =MEDIANA(B2:K2) or =MEDIANA(B2:K2)
Teraz zaznacz komórki z formułami i skopiuj je do wszystkich wierszy naszej tabeli, przeciągając uchwyt autouzupełniania.
Po wykonaniu tego kroku każdy wiersz powinien mieć odpowiednie sumy.
Następnie podsumujemy wyniki dla całej tabeli, w tym celu wykonujemy jeszcze kilka operacji matematycznych:
Odpowiednio:
- =SUMA(L2:L21) or =SUMA(L2:L21)
- =ŚREDNIA(B2:K21) or =СРЗНАЧ(B2:K21) – aby obliczyć tę wartość, należy wziąć dokładnie dane początkowe tabeli. Jeśli weźmiesz średnią ze średnich dla poszczególnych wierszy, wynik będzie inny.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAKS(O2:O21) or =MAKS(O2:O21)
- =MEDIANA(B2:K21) or =MEDIANA(B2:K21) – rozważamy skorzystanie z danych początkowych tabeli, z powodu wskazanego powyżej.
Teraz, gdy skończyliśmy z obliczeniami, zróbmy trochę formatowania. Najpierw ustawmy ten sam format wyświetlania danych dla wszystkich komórek. Zaznacz wszystkie komórki w arkuszu, w tym celu użyj skrótu klawiaturowego Ctrl +lub kliknij ikonę Wybierz wszystko, który znajduje się na przecięciu nagłówków wierszy i kolumn. Następnie kliknij Styl przecinka Zakładka (Rozdzielany format) Strona główna (Dom).
Następnie zmień wygląd nagłówków kolumn i wierszy:
- Pogrubiony styl czcionki.
- Wyśrodkowanie.
- Wypełnienie kolorem.
I na koniec ustawmy format sum.
Tak powinno wyglądać w końcu:
Jeśli wszystko ci odpowiada, przestań nagrywać makro.
Gratulacje! Właśnie nagrałeś swoje pierwsze makro w programie Excel.
Aby skorzystać z wygenerowanego makra, musimy zapisać dokument Excel w formacie obsługującym makra. Najpierw musimy usunąć wszystkie dane z utworzonej przez nas tabeli, czyli uczynić z niej pusty szablon. Faktem jest, że w przyszłości, pracując z tym szablonem, zaimportujemy do niego najnowsze i najistotniejsze dane.
Aby usunąć wszystkie komórki z danych, kliknij prawym przyciskiem myszy ikonę Wybierz wszystko, który znajduje się na przecięciu nagłówków wierszy i kolumn, a następnie z menu kontekstowego wybierz Usuń (Kasować).
Teraz nasz arkusz jest całkowicie wyczyszczony ze wszystkich danych, podczas gdy makro pozostaje zapisane. Skoroszyt musimy zapisać jako szablon Excela z obsługą makr, który ma rozszerzenie XLTM.
Ważny punkt! Jeśli zapiszesz plik z rozszerzeniem XLTX, to makro nie będzie w nim działać. Przy okazji możesz zapisać skoroszyt jako szablon programu Excel 97-2003, który ma format XLT, obsługuje również makra.
Po zapisaniu szablonu możesz bezpiecznie zamknąć program Excel.
Uruchamianie makra w programie Excel
Przed ujawnieniem wszystkich możliwości makra, które stworzyłeś, myślę, że dobrze jest zwrócić uwagę na kilka ważnych punktów dotyczących makr w ogóle:
- Makra mogą być szkodliwe.
- Przeczytaj ponownie poprzedni akapit.
Kod VBA jest bardzo potężny. W szczególności może wykonywać operacje na plikach poza bieżącym dokumentem. Na przykład makro może usuwać lub modyfikować dowolne pliki w folderze Moje dokumenty. Z tego powodu uruchamiaj i zezwalaj tylko na makra ze źródeł, którym ufasz.
Aby uruchomić nasze makro formatowania danych, otwórz plik szablonu, który utworzyliśmy w pierwszej części tego samouczka. Jeśli masz standardowe ustawienia zabezpieczeń, po otwarciu pliku nad tabelą pojawi się ostrzeżenie, że makra są wyłączone, oraz przycisk do ich włączenia. Ponieważ sami stworzyliśmy szablon i ufamy sobie, wciskamy przycisk Włącz treść (Dołącz treść).
Następnym krokiem jest zaimportowanie najnowszego zaktualizowanego zestawu danych z pliku CSV (na podstawie takiego pliku stworzyliśmy nasze makro).
Kiedy importujesz dane z pliku CSV, Excel może poprosić Cię o skonfigurowanie pewnych ustawień w celu poprawnego przeniesienia danych do tabeli.
Po zakończeniu importu przejdź do menu Makra Zakładka (Makra) Zobacz i wysłuchaj (Widok) i wybierz polecenie Wyświetl makra (Makro).
W oknie, które się otworzy, zobaczymy linię z nazwą naszego makra Sformatuj dane. Wybierz go i kliknij run (Wykonać).
Gdy makro zacznie działać, zobaczysz kursor tabeli przeskakujący z komórki do komórki. Po kilku sekundach na danych zostaną wykonane te same operacje, co podczas nagrywania makra. Gdy wszystko będzie gotowe, tabela powinna wyglądać tak samo jak oryginał, który sformatowaliśmy ręcznie, tylko z innymi danymi w komórkach.
Zajrzyjmy pod maskę: Jak działa makro?
Jak wspomniano więcej niż raz, makro to kod programu w języku programowania. Visual Basic dla aplikacji (VBA). Po włączeniu trybu nagrywania makr Excel faktycznie rejestruje każdą wykonaną akcję w postaci instrukcji VBA. Mówiąc najprościej, Excel pisze kod za Ciebie.
Aby zobaczyć ten kod programu, potrzebujesz w menu Makra Zakładka (Makra) Zobacz i wysłuchaj (widok) kliknij Wyświetl makra (Makra) i w otwartym oknie dialogowym kliknij Edytuj (Zmiana).
Otworzy się okno. Visual Basic dla aplikacji, w którym zobaczymy kod programu nagranego przez nas makra. Tak, dobrze zrozumiałeś, tutaj możesz zmienić ten kod, a nawet utworzyć nowe makro. Czynności, które wykonaliśmy z tabelą w tej lekcji, można rejestrować za pomocą automatycznego nagrywania makr w programie Excel. Jednak bardziej złożone makra, z precyzyjnie dostrojoną sekwencją i logiką działania, wymagają ręcznego programowania.
Dodajmy jeszcze jeden krok do naszego zadania…
Wyobraź sobie, że nasz oryginalny plik danych dane.csv jest tworzony automatycznie przez jakiś proces i zawsze jest przechowywany na dysku w tym samym miejscu. Na przykład, C:Dane.csv – ścieżka do pliku ze zaktualizowanymi danymi. Proces otwierania tego pliku i importowania z niego danych można również zarejestrować w makrze:
- Otwórz plik szablonu, w którym zapisaliśmy makro − Sformatuj dane.
- Utwórz nowe makro o nazwie Wczytaj dane.
- Podczas nagrywania makra Wczytaj dane importuj dane z pliku dane.csv – tak jak w poprzedniej części lekcji.
- Po zakończeniu importowania zatrzymaj nagrywanie makra.
- Usuń wszystkie dane z komórek.
- Zapisz plik jako szablon programu Excel z obsługą makr (rozszerzenie XLTM).
W ten sposób uruchamiając ten szablon, uzyskujesz dostęp do dwóch makr – jedno ładuje dane, drugie je formatuje.
Jeśli chcesz zająć się programowaniem, możesz połączyć działania tych dwóch makr w jedno – po prostu kopiując kod z Wczytaj dane do początku kodu Sformatuj dane.