Това е 19-ият седмичен съвет на Excel на адрес.com. Много от съветите на Excel включват някакъв макро трик. Тази седмица за потребителите на Excel, които никога не са писали макрос, предлагам буква за това как да запишат и след това да персонализират полезен макрос на Excel.
Да предположим, че имате 400 реда адресни данни като тези, показани в горната фигура вляво. Полето с името е в колона А, адресът на улицата в колона Б, а градът в колона В.
Вашата цел е да преобразувате данните в една колона като тази, показана на втората фигура.
Този прост проблем ще бъде използван, за да илюстрира как да записвате, модифицирате и след това да стартирате прост макрос.
За потребители на Excel 95: След запис на макроса, Excel ще постави вашия макрос на лист, наречен Module1 във вашата работна книга. Можете просто да щракнете върху листа за достъп до макроса.
Въпреки че в този работен лист има 400 записа, искам да запиша малко от макроса, който се грижи само за първия адрес. Макросът ще приеме, че клетъчният указател е на първо име. Той ще вмъкне три празни реда. Той ще копира клетката отдясно на оригиналната клетка в клетката под оригиналната клетка. Той ще копира градската клетка в клетката 2 реда под оригиналната клетка. След това трябва да премести показалеца на клетката надолу, така че да е на следващото име.
Ключът е да обмислите този процес, преди да го запишете. Не искате да правите много грешки при запис на макроса.
Така че, поставете показалеца на клетката в клетка A1. Отидете в менюто и изберете Tools> Macro> Record new Macro. Диалоговият прозорец Record Macro предлага име на Macro1. Това е добре, така че натиснете OK.
Рекордерът на макроси на Excel има една много глупава настройка по подразбиране, която задължително трябва да промените, за да работи този макрос. В Excel 95 отидете на Инструменти> Макрос> Използване на относителни референции В Excel 97-2003 щракнете върху втората икона на лентата с инструменти за спиране на записа. Иконата изглежда като малък работен лист. Червена клетка в С3 сочи към друга червена клетка в А3. Иконата се нарича Relative Reference. Когато тази икона е включена, има някакъв цвят около нея. Иконата помни последната настройка от текущата сесия на Excel, така че може да се наложи да щракнете върху нея няколко пъти, за да разберете кой метод е включен или не. В Excel 2007 използвайте Изглед - Макроси - Използвайте относителни препратки.
Добре, готови сме да тръгнем. Следвай тези стъпки:
- Натиснете стрелката надолу веднъж, за да преминете към клетка B1.
- Задръжте натиснат клавиша Shift и натиснете два пъти стрелката надолу, за да изберете редове 2, 3 и 4
- От менюто изберете Вмъкване, след което изберете Редове, за да вмъкнете три празни реда.
- Натиснете стрелката нагоре и след това стрелката надясно, за да преминете към клетка B2.
- Натиснете Ctrl X, за да изрежете клетка B2.
- Натиснете стрелката надолу, стрелката наляво, след това Ctrl V, за да поставите в клетка A2.
- Натиснете стрелка нагоре, стрелка надясно, стрелка надясно, Ctrl X, стрелка наляво, стрелка наляво, стрелка надолу, стрелка надолу, Ctrl V, за да преместите C1 до A3.
- Удряйте стрелката надолу два пъти, така че указателят на клетката вече да е на следващото име в ред A5.
- Щракнете върху иконата „Stop Recording“ в лентата с инструменти, за да спрете записа на макроса.
Е, записали сте първия си макрос. Нека да разгледаме. Отидете на Инструменти> Макрос> Макроси. От списъка маркирайте Macro1 и натиснете бутона Редактиране. Трябва да видите нещо, което изглежда така.
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Хей, ако не сте програмист, това вероятно изглежда доста смущаващо. Не позволявайте. Ако има нещо, което не разбирате, има отлична помощ. Щракнете курсора някъде в ключовата дума Offset и натиснете F1. Ако сте инсталирали помощния файл на VBA, ще видите помощната тема за ключовата дума Offset. Помощта ви казва синтаксиса на израза. Пише, че е Offset (RowOffset, ColumnOffset). Все още не е много ясно? Потърсете зелената подчертана дума „пример“ в горната част на помощта. Примерите за VBA на Excel ще ви позволят да научите какво се случва. В примера за Offset той казва, че за да активирате клетката два реда отдолу и три реда вдясно от текущата клетка, ще използвате:
ActiveCell.Offset(3, 2).ActivateДобре, това е улика. Функцията за компенсиране е начин за придвижване в електронната таблица на Excel. Като се има предвид тази част от информацията, можете някак да видите какво прави макросът. Първото отместване (1, 0) е мястото, където преместихме клетката надолу към A2. Следващото отместване е мястото, където се преместихме с един ред нагоре (-1 реда) и над 1 колона. Може да не разбирате нищо друго в макроса, но все пак е полезно.
Върнете се в работния лист на Excel. Поставете показалеца на клетката в клетка A5. Изберете Tools> Macro> Macros> Macro1> Run. Макросът се изпълнява и вторият ви адрес е форматиран.
Може би казвате, че изборът на цялата тази дълга голяма поредица от команди е по-труден от просто форматиране на ръка. Добре, след това направете Инструменти> Макрос> Макроси> Опции. В полето за пряк път кажете Ctrl + w е клавиш за пряк път за този макрос. Щракнете върху OK, след това отхвърлете диалоговия прозорец Macro с Cancel. Сега, когато натиснете Ctrl w, макросът ще се стартира. Можете да форматирате адрес с едно натискане на клавиш.
Готови ли сте за голямото време? Колко адреса ви остават? Ударих Ctrl wa няколко пъти, така че ми останаха 395. Върнете се към вашия макрос. Ще поставим целия макрокод в цикъл. Вмъкнете нов ред, който казва „Do Do activecell.value =" "" преди първия ред на кода на макроса. Поставете ред, който казва "Loop" преди End Sub line. Цикълът Do ще изпълнява всичко между реда Do и Loop, докато не стигне до празен ред. Макросът сега изглежда така:
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Върнете се на вашия лист в Excel. Поставете показалеца на клетката върху следващото име. Натиснете Ctrl w и макросът ще форматира всичките ви записи за няколко секунди.
The authors of Excel books say that you can not do anything useful by recording a macro. Not true! For the person who was going to have to cut and paste 800 times, this macro is very useful. It took a few minutes to record and customize. Yes, professional programmers will point out that the code is horribly inefficient. Excel puts a whole bunch of stuff in there that it does not need to put in there. Yes, if you knew what you are doing, you can accomplish the same task with half the lines which will run in 1.2 seconds instead of 3 seconds. SO WHAT? 3 seconds is far faster than the 30 minutes the task would have taken.
Some more tips for beginning macro recorders:
- The apostrophe is used do indicate a comment. Anything after the apostrophe is ignored by VBA
- This is object oriented programming. The basic syntax is object.action. If a object oriented compiler were playing soccer, it would say "ball.kick" in order to kick the ball. So "Selection.Cut" says to do an "edit> cut" on the current selection.
- In the above example, the Range modifiers are relative to the active cell. If the active cell is in B2 and you say "ActiveCell.Range("A1:C3").Select", then you select the 3 row by 3 column area starting in cell B2. In other words, you select B2:D4. Saying "ActiveCell.Range("A1")" says to select the 1 x 1 cell range starting with the active cell. This is incredibly redundant. It is equivalent to saying "ActiveCell.Select".
- Запазете работната си книга, преди да стартирате макрос за първи път. По този начин, ако има грешка и направи нещо неочаквано, можете да затворите, без да записвате, и да се върнете към запазената версия.
Надяваме се, че този прост пример ще ви даде смелост на начинаещите записващи макроси следващия път, когато имате повтаряща се задача в Excel.
