Toto je 19. týdenní tip na Excel na.com. Mnoho tipů pro Excel zahrnuje nějaký trik s makry. Tento týden pro uživatele aplikace Excel, kteří nikdy nenapsali makro, nabízím základní postup, jak zaznamenat a poté přizpůsobit užitečné makro aplikace Excel.
Řekněme, že máte 400 řádků adresních dat, která jsou uvedena na horním obrázku vlevo. Pole názvu je ve sloupci A, adresa ve sloupci B a město ve sloupci C.
Vaším cílem je převést data do jednoho sloupce, který je uveden na druhém obrázku.
Tento jednoduchý problém bude použit k ilustraci, jak zaznamenat, upravit a poté spustit jednoduché makro.
Pro uživatele aplikace Excel 95: Po zaznamenání makra aplikace Excel umístí vaše makro na list s názvem Module1 v sešitu. Stačí kliknout na list pro přístup k makru.
Ačkoli v tomto listu je 400 záznamů, chci zaznamenat malý kousek makra, které se postará pouze o první adresu. Makro bude předpokládat, že cellpointer je na křestní jméno. Vloží tři prázdné řádky. Zkopíruje buňku napravo od původní buňky do buňky pod původní buňkou. Zkopíruje městskou buňku do buňky 2 řádky pod původní buňkou. Poté by měl posunout ukazatel buňky dolů, aby byl na dalším jménu.
Klíčem je tento proces promyslet, než jej zaznamenáte. Při záznamu makra nechcete dělat spoustu chyb.
Umístěte tedy ukazatel do buňky A1. Přejděte do nabídky a vyberte Nástroje> Makro> Zaznamenat nové makro. Dialogové okno Záznam makra navrhuje název Macro1. To je v pořádku, takže stiskněte OK.
Záznamník maker aplikace Excel má jedno velmi hloupé výchozí nastavení, které musíte bezpodmínečně změnit, aby toto makro fungovalo. V aplikaci Excel 95 přejděte do nabídky Nástroje> Makro> Použít relativní odkazy V aplikaci Excel 97-2003 klikněte na druhou ikonu na panelu nástrojů Zastavit záznam. Ikona vypadá jako malý list. Červená buňka v C3 ukazuje na jinou červenou buňku v A3. Ikona se nazývá relativní reference. Když je tato ikona zapnutá, je kolem ní nějaká barva. Ikona si pamatuje poslední nastavení z aktuální relace aplikace Excel, takže možná budete muset několikrát kliknout, abyste zjistili, která metoda je nebo není. V aplikaci Excel 2007 použijte Zobrazit - Makra - Použít relativní odkazy.
Dobře, jsme připraveni jít. Následuj tyto kroky:
- Jedním stisknutím šipky dolů přejděte do buňky B1.
- Podržte klávesu Shift a dvakrát stiskněte šipku dolů a vyberte řádky 2, 3 a 4
- V nabídce vyberte Vložit a poté vyberte Řádky a vložte tři prázdné řádky.
- Stisknutím šipky nahoru a poté šipky doprava přejděte do buňky B2.
- Stisknutím klávesy Ctrl X rozříznete buňku B2.
- Stisknutím šipky dolů, šipky doleva a poté Ctrl V vložte do buňky A2.
- Stisknutím klávesy šipka nahoru, šipka vpravo, šipka vpravo, Ctrl X, šipka vlevo, šipka vlevo, šipka dolů, šipka dolů, Ctrl V pro přesun C1 na A3.
- Dvakrát stiskněte šipku dolů, takže ukazatel buňky je nyní na další jméno v řádku A5.
- Kliknutím na ikonu „Zastavit záznam“ na panelu nástrojů zastavíte nahrávání makra.
Zaznamenali jste své první makro. Podívejme se na to. Přejděte na Nástroje> Makro> Makra. V seznamu zvýrazněte Macro1 a stiskněte tlačítko Upravit. Měli byste vidět něco, co vypadá takto.
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 SubHej, pokud nejsi programátor, asi to vypadá dost zastrašujícím dojmem. Nenechte to být. Pokud existuje něco, čemu nerozumíte, existuje vynikající pomoc. Klikněte na kurzor někde v klíčovém slově Odsazení a stiskněte F1. Pokud jste nainstalovali soubor nápovědy VBA, zobrazí se téma nápovědy pro klíčové slovo Offset. Nápověda vám řekne syntaxi příkazu. Říká, že je Offset (RowOffset, ColumnOffset). Stále to není příliš jasné? V horní části nápovědy vyhledejte zelené podtržené slovo „příklad“. Příklady VBA aplikace Excel vám umožní zjistit, o co jde. V příkladu ofsetu se říká, že k aktivaci buňky dva řádky pod a tři řádky napravo od aktuální buňky byste použili:
ActiveCell.Offset(3, 2).ActivateDobře, to je vodítko. Funkce offset je způsob, jak se pohybovat po tabulce aplikace Excel. Vzhledem k těmto informacím můžete trochu vidět, co makro dělá. První posun (1, 0) je místo, kde jsme přesunuli ukazatele buněk dolů na A2. Další ofset je místo, kde jsme se posunuli nahoru o jeden řádek (-1 řádky) a přes 1 sloupec. Možná v makru nebudete rozumět ničemu jinému, ale je to stále užitečné.
Vraťte se do listu aplikace Excel. Umístěte ukazatel na buňku do buňky A5. Zvolte Nástroje> Makro> Makra> Makro1> Spustit. Spustí se makro a naformátuje se vaše druhá adresa.
Možná říkáte, že výběr celého tohoto dlouhého řetězce příkazů je těžší než jen ruční formátování. Dobře, pak proveďte Nástroje> Makro> Makra> Možnosti. V poli zkratky řekněme Ctrl + w je klávesová zkratka pro toto makro. Klikněte na OK a poté zavřete dialog Makro pomocí Storno. Nyní, když stisknete Ctrl w, makro se spustí. Adresu můžete naformátovat jediným stisknutím klávesy.
Jste připraveni na velký čas? Kolik adres vám ještě zbývá? Několikrát jsem trefil Ctrl wa, takže mi zbývá 395. Vraťte se ke svému makru. Celý kód makra vložíme do smyčky. Před první řádek kódu makra vložte nový řádek s textem „Do Until activecell.value =" "". Vložte řádek, který říká „Smyčka“ před řádek End Sub. Smyčka Do provede vše mezi řádky Do a Loop, dokud se nespustí do prázdného řádku. Makro nyní vypadá takto:
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 SubVraťte se na svůj excelový list. Umístěte ukazatel buňky na další jméno. Stiskněte Ctrl w a makro naformátuje všechny vaše záznamy během několika sekund.
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".
- Před prvním spuštěním makra uložte sešit. Tímto způsobem, pokud dojde k chybě a provede něco neočekávaného, můžete zavřít bez uložení a vrátit se zpět k uložené verzi.
Doufejme, že tento jednoduchý příklad vám dá začínajícím záznamníkům maker odvahu zaznamenat jednoduché makro, až příště budete mít v aplikaci Excel opakující se úkol.
