Obsah
Excel má výkonnú, ale zároveň veľmi zriedka používanú schopnosť vytvárať automatické sekvencie akcií pomocou makier. Makro je ideálnym východiskom, ak riešite rovnaký typ úlohy, ktorý sa mnohokrát opakuje. Napríklad spracovanie dát alebo formátovanie dokumentu podľa štandardizovanej šablóny. V tomto prípade nepotrebujete znalosť programovacích jazykov.
Už ste zvedaví, čo je makro a ako funguje? Potom smelo do toho – potom s vami krok za krokom urobíme celý proces vytvárania makra.
Čo je to makro?
Makro v Microsoft Office (áno, táto funkcia funguje rovnako v mnohých aplikáciách balíka Microsoft Office) je programový kód v programovacom jazyku Visual Basic pre aplikácie (VBA) uložené vo vnútri dokumentu. Aby to bolo jasnejšie, dokument Microsoft Office možno porovnať so stránkou HTML, potom je makro analógom Javascriptu. Čo dokáže Javascript s údajmi HTML na webovej stránke, je veľmi podobné tomu, čo dokáže urobiť makro s údajmi v dokumente balíka Microsoft Office.
Makrá môžu v dokumente robiť takmer všetko, čo chcete. Tu sú niektoré z nich (veľmi malá časť):
- Použiť štýly a formátovanie.
- Vykonávajte rôzne operácie s číselnými a textovými údajmi.
- Používajte externé zdroje údajov (databázové súbory, textové dokumenty atď.)
- Vytvorte nový dokument.
- Urobte všetko vyššie uvedené v akejkoľvek kombinácii.
Vytvorenie makra – praktický príklad
Zoberme si napríklad najbežnejší súbor CSV. Ide o jednoduchú tabuľku s rozmermi 10 × 20 naplnenú číslami od 0 do 100 s nadpismi stĺpcov a riadkov. Našou úlohou je premeniť tento súbor údajov na prezentovateľne naformátovanú tabuľku a vygenerovať súčty v každom riadku.
Ako už bolo spomenuté, makro je kód napísaný v programovacom jazyku VBA. Ale v Exceli môžete vytvoriť program bez písania riadku kódu, čo urobíme práve teraz.
Ak chcete vytvoriť makro, otvorte pohľad (Typ) > Makrá (Makro) > Záznam makra (Makro nahrávanie...)
Pomenujte svoje makro (bez medzier) a kliknite OK.
Od tohto momentu sa zaznamenávajú VŠETKY vaše akcie s dokumentom: zmeny buniek, rolovanie v tabuľke, dokonca aj zmena veľkosti okna.
Excel signalizuje, že režim záznamu makra je povolený na dvoch miestach. Najprv v ponuke Makrá (Makrá) – namiesto reťazca Záznam makra Objavil sa riadok (Nahrávanie makra...). Zastaviť nahrávanie (Zastaviť nahrávanie).
Po druhé, v ľavom dolnom rohu okna programu Excel. Ikona Stop (malý štvorec) znamená, že je aktivovaný režim makro záznamu. Kliknutím naň zastavíte nahrávanie. Naopak, keď režim nahrávania nie je povolený, na tomto mieste sa nachádza ikona na povolenie nahrávania makra. Kliknutím naň získate rovnaký výsledok ako zapnutie nahrávania cez menu.
Teraz, keď je zapnutý režim záznamu makra, poďme k našej úlohe. V prvom rade pridajme hlavičky pre súhrnné údaje.
Ďalej zadajte vzorce do buniek v súlade s názvami nadpisov (uvedené sú varianty vzorcov pre angličtinu a verzie programu Excel, adresy buniek sú vždy latinské písmená a čísla):
- =SUM(B2:K2) or =SUM(B2:K2)
- =AVERAGE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Teraz vyberte bunky so vzorcami a skopírujte ich do všetkých riadkov našej tabuľky potiahnutím rukoväte automatického dopĺňania.
Po dokončení tohto kroku by mal mať každý riadok zodpovedajúce súčty.
Ďalej zhrnieme výsledky pre celú tabuľku, preto vykonáme niekoľko ďalších matematických operácií:
Respektíve:
- =SUM(L2:L21) or =SUM(L2:L21)
- =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) – na výpočet tejto hodnoty je potrebné vziať presne počiatočné údaje tabuľky. Ak vezmete priemer priemerov pre jednotlivé riadky, výsledok bude iný.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – z vyššie uvedeného dôvodu uvažujeme o použití počiatočných údajov tabuľky.
Teraz, keď sme skončili s výpočtami, urobme nejaké formátovanie. Najprv nastavme rovnaký formát zobrazenia údajov pre všetky bunky. Vyberte všetky bunky na hárku, použite klávesovú skratku Ctrl +alebo kliknite na ikonu Vybrať všetko, ktorý sa nachádza na priesečníku nadpisov riadkov a stĺpcov. Potom kliknite Štýl čiarky (Formát s oddeľovačmi). Domov (Domov).
Ďalej zmeňte vzhľad hlavičiek stĺpcov a riadkov:
- Tučný štýl písma.
- Zarovnanie na stred.
- Farebná výplň.
A nakoniec nastavíme formát súčtov.
Takto by to malo vyzerať na konci:
Ak vám všetko vyhovuje, zastavte nahrávanie makra.
Gratulujem! Práve ste sami zaznamenali svoje prvé makro v Exceli.
Ak chcete použiť vygenerované makro, musíme uložiť dokument programu Excel vo formáte, ktorý podporuje makrá. Najprv musíme vymazať všetky údaje z tabuľky, ktorú sme vytvorili, teda urobiť z nej prázdnu šablónu. Faktom je, že v budúcnosti pri práci s touto šablónou do nej budeme importovať najnovšie a relevantné údaje.
Ak chcete vymazať všetky bunky z údajov, kliknite pravým tlačidlom myši na ikonu Vybrať všetko, ktorý sa nachádza na priesečníku nadpisov riadkov a stĺpcov a z kontextového menu vyberte vymazať (Vymazať).
Teraz je náš hárok úplne vymazaný zo všetkých údajov, zatiaľ čo makro zostáva zaznamenané. Musíme uložiť zošit ako šablónu Excel s podporou makier, ktorá má príponu XLTM.
Dôležitý bod! Ak uložíte súbor s príponou XLTX, potom v ňom makro nebude fungovať. Mimochodom, zošit môžete uložiť ako šablónu programu Excel 97-2003, ktorá má formát XLT rozšírenie, podporuje aj makrá.
Po uložení šablóny môžete Excel bezpečne zavrieť.
Spustenie makra v Exceli
Pred odhalením všetkých možností makra, ktoré ste vytvorili, si myslím, že je správne venovať pozornosť niekoľkým dôležitým bodom týkajúcim sa makier vo všeobecnosti:
- Makrá môžu byť škodlivé.
- Prečítajte si ešte raz predchádzajúci odsek.
Kód VBA je veľmi výkonný. Najmä môže vykonávať operácie so súbormi mimo aktuálneho dokumentu. Makro môže napríklad odstrániť alebo upraviť ľubovoľné súbory v priečinku Moje dokumenty. Z tohto dôvodu spúšťajte a povoľujte makrá len zo zdrojov, ktorým dôverujete.
Ak chcete spustiť naše makro na formátovanie údajov, otvorte súbor šablóny, ktorý sme vytvorili v prvej časti tohto návodu. Ak máte štandardné nastavenia zabezpečenia, pri otvorení súboru sa nad tabuľkou zobrazí upozornenie, že makrá sú zakázané, a tlačidlo na ich povolenie. Keďže sme si šablónu vyrobili sami a veríme si, stlačíme tlačidlo Povoliť obsah (Zahrňte obsah).
Ďalším krokom je import najnovšieho aktualizovaného súboru údajov zo súboru CSV (na základe takéhoto súboru sme vytvorili naše makro).
Keď importujete údaje zo súboru CSV, Excel vás môže požiadať o nastavenie niektorých nastavení, aby sa údaje správne preniesli do tabuľky.
Po dokončení importu prejdite do ponuky Makrá (Makrá). pohľad (Zobraziť) a vyberte príkaz Zobraziť makrá (Makro).
V dialógovom okne, ktoré sa otvorí, uvidíme riadok s názvom nášho makra FormatData. Vyberte ho a kliknite beh (Vykonať).
Keď sa makro spustí, uvidíte, že kurzor tabuľky skáče z bunky na bunku. Po niekoľkých sekundách sa s údajmi vykonajú rovnaké operácie ako pri nahrávaní makra. Keď je všetko pripravené, tabuľka by mala vyzerať rovnako ako originál, ktorý sme naformátovali ručne, len s inými údajmi v bunkách.
Pozrime sa pod kapotu: Ako funguje makro?
Ako už bolo viackrát spomenuté, makro je programový kód v programovacom jazyku. Visual Basic pre aplikácie (VBA). Keď zapnete režim záznamu makier, Excel skutočne zaznamená každú vykonanú akciu vo forme inštrukcií VBA. Jednoducho povedané, Excel napíše kód za vás.
Ak chcete vidieť tento programový kód, musíte v menu Makrá (Makrá). pohľad (zobraziť) kliknúť Zobraziť makrá (Makrá) a v dialógovom okne, ktoré sa otvorí, kliknite editovať (Zmena).
Otvorí sa okno. Visual Basic pre aplikácie, v ktorom uvidíme programový kód makra, ktoré sme zaznamenali. Áno, pochopili ste správne, tu môžete tento kód zmeniť a dokonca vytvoriť nové makro. Akcie, ktoré sme vykonali s tabuľkou v tejto lekcii, je možné zaznamenať pomocou automatického záznamu makier v Exceli. Ale zložitejšie makrá s jemne vyladenou sekvenciou a akčnou logikou vyžadujú manuálne programovanie.
Pridajme k našej úlohe ešte jeden krok…
Predstavte si, že náš pôvodný dátový súbor data.csv sa vytvára automaticky nejakým procesom a je vždy uložený na disku na rovnakom mieste. Napríklad, C:Datadata.csv – cesta k súboru s aktualizovanými údajmi. Proces otvárania tohto súboru a importu údajov z neho možno zaznamenať aj v makre:
- Otvorte súbor šablóny, do ktorého sme uložili makro − FormatData.
- Vytvorte nové makro s názvom LoadData.
- Počas nahrávania makra LoadData importovať údaje zo súboru data.csv – ako sme to urobili v predchádzajúcej časti lekcie.
- Po dokončení importu zastavte zaznamenávanie makra.
- Odstráňte všetky údaje z buniek.
- Uložte súbor ako šablónu programu Excel s podporou makier (rozšírenie XLTM).
Spustením tejto šablóny teda získate prístup k dvom makrám – jedno načíta dáta, druhé ich formátuje.
Ak sa chcete pustiť do programovania, môžete spojiť akcie týchto dvoch makier do jedného – jednoducho skopírovaním kódu z LoadData na začiatok kódu FormatData.