Funkcje programu Excel są potężne, ale mają swoje ograniczenia. Kiedyś polegałem na Funkcje złożone i zagnieżdżone Ich tworzenie zajmuje dużo czasu i trudno je rozwiązać, ale zacząłem częściej korzystać z makr. Nie chodzi o to, że funkcje są złe – świetnie nadają się do precyzyjnych obliczeń i manipulacji danymi. Ale gdy masz do czynienia z powtarzalnymi zadaniami, które obejmują wiele kroków, zmiany formatowania lub czynności, które muszą być wykonywane w różnych arkuszach, makra mają większy sens.
Najlepsze jest to, że makra radzą sobie z rzeczami, których funkcje nie potrafią. Podczas gdy funkcja może obliczać wartości, makro może obliczać, formatować, kopiować dane do innego arkusza, wysyłać e-maile i tworzyć wykresy jednocześnie. Transformacja staje się więc oczywista, gdy uświadomisz sobie, że marnujesz czas na pracę ręczną, którą można by zautomatyzować.
Szybkie linki
Jak nagrać swoją pierwszą makroinstrukcję w programie Excel
Rozpocznij korzystanie z wbudowanego rejestratora makr w programie Excel
Rejestrowanie makra w programie Excel jest proste – w zasadzie wystarczy, że poinstruujesz program Excel, aby monitorował Twoje działania i zapamiętywał je później. Rejestrator makr rejestruje każde kliknięcie, naciśnięcie klawisza i wykonaną czynność, a następnie konwertuje je na kod Visual Basic for Applications (VBA), który można odtworzyć w dowolnym momencie.
Zanim zaczniesz nagrywać, ważne jest, aby dokładnie zaplanować, co makro ma robić. Dokładnie przemyśl każdy krok, ponieważ rejestrator rejestruje wszystko, łącznie z błędami i niepotrzebnymi kliknięciami.
Chociaż dostęp do nagrywania makr można uzyskać z karty Widok, warto włączyć kartę Deweloper, aby ułatwić sobie dostęp do narzędzi do makr. Karta Deweloper jest domyślnie wyłączona, ale po jej włączeniu dostępne są niestandardowe przyciski makr i bardziej zaawansowane opcje.
Aby włączyć kartę Deweloper, przejdź do Plik > Opcje > Dostosuj wstążkę, a następnie zaznacz pole. Deweloper W prawym panelu kliknij dobrzePo włączeniu zobaczysz dwa przyciski. Nagrywanie makro و Zatrzymaj nagrywanie Bezpośrednio na karcie Deweloper, wraz z narzędziami do edycji i zarządzania makrami.
Oto jak zacząć:
- Przejdź do zakładki Deweloper Na wstążce i kliknij Nagrywanie makro.
- W wyświetlonym oknie dialogowym nadaj makrowi opisową nazwę. Unikaj spacji – zamiast nich używaj podkreśleń.
- Przypisz klawisz skrótu, jeśli chcesz później szybko uzyskać do niego dostęp. Na przykład: Ctrl+Shift+Q Działa dobrze.
Wybierz miejsce przechowywania makra. Wybierz Ta praca
- Upewnij się, że zapiszesz plik w bieżącym pliku tylko wtedy, gdy będzie to potrzebne.
- W polu Opis dodaj krótki opis działania makra.
- dotknij OK rozpocząć nagrywanie.
Teraz wykonaj dokładnie te czynności, które chcesz, aby Excel zapamiętał. Po zakończeniu wróć do zakładki. Deweloper i kliknij Zatrzymaj nagrywanieTo wszystko.
Zacznij od prostych zadań w miarę nauki. Nagranie makra formatującego komórki lub kopiującego dane między arkuszami jest o wiele łatwiejsze niż próba automatyzacji skomplikowanych obliczeń od razu.
Makro jest teraz zapisane i gotowe do użycia. Możesz je uruchomić za pomocą przypisanego skrótu klawiszowego lub przechodząc do Deweloper> Makra Następnie wybierz go z listy.
Moja ulubiona makroinstrukcja, która zastąpiła równania
Trzy podstawowe makra, które wykonują większość moich powtarzalnych zadań.
Z czasem stworzyłem zestaw makr, które rozwiązują powtarzające się problemy, z którymi kiedyś miałem problem. Nie są one wymyślne ani skomplikowane – to po prostu praktyczne rozwiązania, które oszczędzają mi ciągłego wykonywania tej samej, żmudnej pracy.
Moje najczęściej używane makro automatycznie czyści zaimportowane dane. Używam go do usuwania zbędnych spacji i Wyczyść dane za pomocą funkcji TRIMRANGE, konwertuj tekst na odpowiednią wielkość liter i ujednolicaj formaty dat w wielu kolumnach.
Innym makro, z którego korzystam, jest agregacja danych z wielu arkuszy w raporcie podsumowującym. Pobiera ono określone zakresy z różnych arkuszy, stosuje spójne formatowanie i tworzy przejrzysty przegląd. Jest to przydatne w przypadku raportów miesięcznych, które wymagają za każdym razem tej samej struktury.
Mam też makro, które ma zastosowanie Reguły koordynacji warunkowej Oparte na wielu kryteriach – co staje się kłopotliwe, gdy próbujesz zbudować je z zagnieżdżonymi funkcjami. Makro może analizować wiele kolumn, stosować różne schematy kolorów, a nawet dodawać paski danych lub ikony na podstawie znalezionych wartości.
Największą zaletą tych makr jest to, że po ich skonfigurowaniu może z nich korzystać każdy członek zespołu. Nie musisz rozumieć ich logiki – wystarczy uruchomić makro i za każdym razem uzyskać spójne wyniki.
Odrobina VBA sprawia, że makra stają się bardziej wydajne.
Drobne modyfikacje kodu przekształcają podstawowe zarejestrowane makra.
Chociaż nagrane makra działają świetnie, gdy już zaczniesz je stosować, nauczenie się odrobiny Programowanie VBA w Excelu To przeniesie Cię na wyższy poziom. Nie musisz zostać ekspertem w programowaniu – samo zrozumienie podstawowych pojęć sprawi, że Twoje makra będą bardziej inteligentne i elastyczne.
Rejestrator makr generuje kod funkcjonalny, ale często jest on nieefektywny i nieelastyczny. Przechwytuje bezwzględne odwołania do komórek, a czasem niepotrzebne dodatkowe polecenia, co może spowalniać działanie programu. Z odrobiną znajomości języka VBA można modyfikować makra, aby dynamicznie obsługiwać różne zakresy i uruchamiać je szybciej.
Oto jak rozpocząć edycję zarejestrowanych makr:
- przejdź do Deweloper> Makra Zdefiniuj swoje makro.
- Kliknij Edytuj Aby otworzyć edytor VBA.
- Szukaj zaszyfrowanych odniesień do komórek, takich jak Zakres(“A1:C10”).
- Zastąp je dynamicznymi odniesieniami za pomocą Aktualny region أو Koniec (xlDown).
- Dodaj prostą obsługę błędów za pomocą On Error Resume Next.
Jednym z największych usprawnień, jakie możesz wprowadzić, jest dodanie danych wprowadzanych przez użytkownika. Zamiast oddzielnych makr dla różnych scenariuszy, użyj Pole wprowadzania Umożliwia użytkownikom określanie zakresów lub kryteriów. Dzięki temu sztywne, zarejestrowane makro staje się elastycznym narzędziem.
Dodanie podstawowych pętli zwiększa również moc makr. Pętla może Dla każdego Łatwe przetwarzanie wielu arkuszy kalkulacyjnych lub zakresów bez konieczności rejestrowania oddzielnych działań dla każdego z nich.
Edytor VBA może początkowo wydawać się onieśmielający, ale zacznij od małych rzeczy. Zmień odwołanie do komórki tutaj, dodaj pole wiadomości tam. Gdy zobaczysz, jak te drobne zmiany udoskonalają Twoje makra, zechcesz dowiedzieć się więcej.
Gdzie makra zawodzą?
Kiedy funkcje są nadal najlepszym wyborem?
Makra nie są idealne i z pewnością istnieją sytuacje, w których funkcje lub inne rozwiązania sprawdzają się lepiej. Znajomość tych ograniczeń pomoże Ci wybrać odpowiednie narzędzie do każdego zadania.
Bezpieczeństwo to prawdopodobnie największy problem, z jakim spotkasz się w przypadku makr. Ponieważ makra mogą zawierać złośliwy kod, wiele firm domyślnie je wyłącza, aby zachować integralność danych. Oznacza to, że automatyzacja oparta na makrach może nie działać u każdego, chyba że wie, jak je włączyć – a powiedzmy sobie szczerze, nie każdy będzie chciał lub czuł się z tym komfortowo. Udostępniając pliki, warto dołączyć jasne instrukcje dotyczące bezpiecznego włączania makr, ale bądź przygotowany na to, że niektórzy użytkownicy napotkają trudności.
Makra nie zastąpią obliczeń w czasie rzeczywistym, ponieważ działają tylko po ich jawnej inicjalizacji. Funkcje natomiast aktualizują się natychmiast po każdej zmianie danych, dlatego wciąż stanowią najlepszy wybór, gdy zależy Ci na bezpośrednich i dokładnych wynikach. Dlatego traktuj makra jako sposób na automatyzację zadań, a nie jako zamiennik codziennych obliczeń.
Rozwiązywanie problemów z niedziałającymi makrami może być frustrujące, zwłaszcza jeśli nie znasz się na VBA. Gdy funkcja ulega awarii, błąd jest zazwyczaj oczywisty. Gdy makro przestaje działać, znalezienie przyczyny często wymaga zagłębienia się w kod i przetestowania różnych scenariuszy.
Wydajność staje się również problemem w przypadku dużych zbiorów danych. Źle napisane makro może spowolnić działanie programu Excel i zająć dużo czasu, ale dobrze zaprojektowane funkcje lub inne narzędzia, takie jak Power Query często bezproblemowo przetwarza te same dane.Jeśli priorytetem jest dla Ciebie szybkość, możesz swobodnie łączyć makra z tymi opcjami, aby uzyskać to, co najlepsze z obu światów.
Makra nie są magiczne, ale są blisko.
Znajdź odpowiednią równowagę między automatyzacją i funkcjami.
Makra nie zastąpią wszystkich funkcji w pakiecie narzędzi Excela, ale są przydatne do obsługi powtarzalnych, wieloetapowych zadań, które mogą być czasochłonne. Kluczem jest wiedza, kiedy użyć każdego narzędzia. Funkcje idealnie sprawdzają się w przypadku obliczeń, które wymagają automatycznej aktualizacji. Makra doskonale sprawdzają się w złożonych przepływach pracy, które obejmują wiele działań w różnych częściach skoroszytu.
Zacznij od prostego korzystania z rejestratora makr, a następnie stopniowo dodawaj modyfikacje VBA, w miarę jak nabierasz wprawy. W mgnieniu oka otrzymasz zestaw niestandardowych narzędzi automatyzacyjnych, dzięki którym Excel będzie działał dokładnie tak, jak tego potrzebujesz.
