Rekord Modyfikuj Uruchom makro programu Excel - porady dotyczące programu Excel

To jest 19. cotygodniowa wskazówka dotycząca programu Excel w witrynie.com. Wiele wskazówek dotyczących programu Excel zawiera jakieś sztuczki z makrami. W tym tygodniu użytkownikom programu Excel, którzy nigdy nie napisali makra, oferuję wprowadzenie do rejestrowania, a następnie dostosowywania przydatnych makr programu Excel.

Przykładowe dane adresowe

Załóżmy, że masz 400 wierszy danych adresowych, takich jak pokazane na górnym rysunku po lewej stronie. Pole nazwy znajduje się w kolumnie A, adres w kolumnie B, a miasto w kolumnie C.

Twoim celem jest przekonwertowanie danych do pojedynczej kolumny, jak pokazano na drugim rysunku.

Ten prosty problem zostanie użyty do zilustrowania sposobu rejestrowania, modyfikowania, a następnie uruchamiania prostego makra.

Użytkownicy programu Excel 95: Po zarejestrowaniu makra program Excel umieści je w arkuszu o nazwie Moduł1 w skoroszycie. Możesz po prostu kliknąć arkusz, aby uzyskać dostęp do makra.

Chociaż w tym arkuszu jest 400 rekordów, chcę nagrać niewielką część makra, które zajmuje się tylko pierwszym adresem. Makro przyjmie, że celka wskaźnika znajduje się na imieniu. Wstawi trzy puste wiersze. Skopiuje komórkę po prawej stronie oryginalnej komórki do komórki pod pierwotną komórką. Skopiuje komórkę miasta do 2 wierszy komórki pod oryginalną komórką. Powinien następnie przesunąć wskaźnik komórki w dół, tak aby znalazł się na następnej nazwie.

Kluczem jest przemyślenie tego procesu przed jego nagraniem. Nie chcesz popełniać wielu błędów podczas nagrywania makra.

Więc umieść wskaźnik komórki w komórce A1. Przejdź do menu i wybierz Narzędzia> Makro> Zarejestruj nowe makro. Okno dialogowe Record Macro sugeruje nazwę Macro1. To jest w porządku, więc naciśnij OK.

Rejestrator makr programu Excel ma jedno bardzo głupie ustawienie domyślne, które absolutnie musisz zmienić, aby to makro działało. W programie Excel 95 przejdź do opcji Narzędzia> Makro> Użyj odwołań względnych W programie Excel 97-2003 kliknij drugą ikonę na pasku narzędzi Zatrzymaj rejestrowanie. Ikona wygląda jak mały arkusz roboczy. Czerwona krwinka w C3 wskazuje na inną czerwoną krwinkę w A3. Ikona nazywa się Względne odniesienie. Kiedy ta ikona jest włączona, wokół ikony jest jakiś kolor. Ikona zapamiętuje ostatnie ustawienie z bieżącej sesji programu Excel, więc może być konieczne kliknięcie jej kilka razy, aby dowiedzieć się, która metoda jest włączona, czy nie. W programie Excel 2007 użyj widoku - makra - użyj odwołań względnych.

OK, jesteśmy gotowi do pracy. Wykonaj następujące kroki:

Naciśnij raz strzałkę w dół, aby przejść do komórki B1.
Przytrzymaj klawisz Shift i dwukrotnie naciśnij strzałkę w dół, aby wybrać wiersze 2, 3 i 4
Z menu wybierz opcję Wstaw, a następnie opcję Wiersze, aby wstawić trzy puste wiersze.
Naciśnij strzałkę w górę, a następnie strzałkę w prawo, aby przejść do komórki B2.
Naciśnij Ctrl X, aby wyciąć komórkę B2.
Naciśnij strzałkę w dół, strzałkę w lewo, a następnie Ctrl V, aby wkleić do komórki A2.
Naciśnij strzałkę w górę, strzałkę w prawo, strzałkę w prawo, Ctrl X, strzałkę w lewo, strzałkę w lewo, strzałkę w dół, strzałkę w dół, Ctrl V, aby przenieść C1 do A3.
Naciśnij dwukrotnie strzałkę w dół, aby wskaźnik komórki znalazł się teraz na następnej nazwie w wierszu A5.
Kliknij ikonę „Zatrzymaj nagrywanie” na pasku narzędzi, aby zatrzymać nagrywanie makra.

Cóż, nagrałeś swoje pierwsze makro. Spójrzmy. Przejdź do opcji Narzędzia> Makro> Makra. Na liście podświetl Macro1 i naciśnij przycisk Edit. Powinieneś zobaczyć coś, co wygląda tak.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Hej, jeśli nie jesteś programistą, prawdopodobnie wygląda to dość onieśmielająco. Nie pozwól temu. Jeśli czegoś nie rozumiesz, możesz liczyć na doskonałą pomoc. Kliknij kursor gdzieś w słowie kluczowym Offset i naciśnij F1. Jeśli zainstalowałeś plik pomocy VBA, zobaczysz temat pomocy dla słowa kluczowego Offset. Pomoc zawiera informacje o składni instrukcji. Mówi, że jest to Offset (RowOffset, ColumnOffset). Wciąż niezbyt jasne? Poszukaj zielonego podkreślonego słowa „przykład” w górnej części pomocy. Przykłady VBA programu Excel pozwolą Ci dowiedzieć się, co się dzieje. W przykładzie Offset mówi się, aby aktywować komórkę dwa wiersze poniżej i trzy wiersze po prawej stronie bieżącej komórki, użyj:

ActiveCell.Offset(3, 2).Activate

OK, to jest wskazówka. Funkcja offset to sposób poruszania się po arkuszu kalkulacyjnym Excel. Biorąc pod uwagę tę część informacji, możesz w pewnym sensie zobaczyć, co robi makro. Pierwsze przesunięcie (1, 0) to miejsce, w którym przenieśliśmy wskaźnik komórki w dół do A2. W następnym przesunięciu przesunęliśmy się o jeden wiersz w górę (-1 wierszy) i ponad 1 kolumnę. Możesz nie rozumieć niczego innego w makrze, ale nadal jest to przydatne.

Wróć do arkusza programu Excel. Umieść wskaźnik komórki w komórce A5. Wybierz polecenie Narzędzia> Makro> Makra> Makro1> Uruchom. Makro zostanie uruchomione, a drugi adres zostanie sformatowany.

Być może mówisz, że wybranie tego całego długiego, dużego ciągu poleceń jest trudniejsze niż zwykłe formatowanie ręczne. OK, a następnie wykonaj Narzędzia> Makro> Makra> Opcje. W polu skrótu powiedz Ctrl + w to klawisz skrótu dla tego makra. Kliknij OK, a następnie zamknij okno dialogowe Makro, klikając Anuluj. Teraz, gdy naciśniesz Ctrl w, makro zostanie uruchomione. Możesz sformatować adres jednym naciśnięciem klawisza.

Czy jesteś gotowy na wielki czas? Ile adresów Ci zostało? Nacisnąłem Ctrl kilka razy, więc zostało mi 395. Wróć do swojego makra. Zamierzamy umieścić cały kod makra w pętli. Wstaw nową linię z napisem „Do Until activecell.value =" "" przed pierwszą linią kodu makra. Wstaw wiersz z napisem „Loop” przed wierszem End Sub. Pętla Do wykona wszystko między wierszami Do i Loop, aż dojdzie do pustej linii. Makro wygląda teraz następująco:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Wróć do arkusza Excela. Umieść wskaźnik komórki na następnej nazwie. Naciśnij Ctrl w, a makro sformatuje wszystkie twoje rekordy w ciągu kilku sekund.

Autorzy książek Excela twierdzą, że nie można zrobić nic pożytecznego, rejestrując makro. Nie prawda! Dla osoby, która będzie musiała wyciąć i wkleić 800 razy, to makro jest bardzo przydatne. Nagrywanie i dostosowywanie zajęło kilka minut. Tak, profesjonalni programiści zwrócą uwagę, że kod jest strasznie nieefektywny. Excel umieszcza tam całą masę rzeczy, których nie musi tam umieszczać. Tak, jeśli wiesz, co robisz, możesz wykonać to samo zadanie z połową linii, które będą działać w ciągu 1,2 sekundy zamiast 3 sekund. WIĘC CO? 3 sekundy to znacznie szybciej niż 30 minut, które zajęłoby zadanie.

Więcej wskazówek dla początkujących rejestratorów makr:

Apostrof jest używany, aby wskazać komentarz. Wszystko po apostrofie jest ignorowane przez VBA
To jest programowanie obiektowe. Podstawowa składnia to object.action. Gdyby kompilator zorientowany obiektowo grał w piłkę nożną, powiedziałby „ball.kick”, aby kopnąć piłkę. Tak więc „Selection.Cut” mówi, żeby wykonać „edytuj> wytnij” na bieżącym zaznaczeniu.
W powyższym przykładzie modyfikatory Range odnoszą się do aktywnej komórki. Jeśli aktywna komórka znajduje się w B2 i powiesz „ActiveCell.Range („ A1: C3 ”). Select”, wybierz obszar 3 wiersze na 3 kolumny, zaczynając od komórki B2. Innymi słowy, wybierasz B2: D4. Powiedzenie „ActiveCell.Range („ A1 ”)” powoduje wybranie zakresu komórek 1 x 1, zaczynając od komórki aktywnej. To jest niesamowicie zbędne. Jest to równoważne z powiedzeniem „ActiveCell.Select”.
Zapisz skoroszyt przed pierwszym uruchomieniem makra. W ten sposób, jeśli wystąpi błąd i zrobi coś nieoczekiwanego, możesz zamknąć bez zapisywania i powrócić do zapisanej wersji.

Miejmy nadzieję, że ten prosty przykład da początkującym rejestratorom makr odwagę do zarejestrowania prostego makra, gdy następnym razem będziesz mieć powtarzające się zadanie do wykonania w programie Excel.