9Sep

Preberite, kako uporabljati makroje Excel za avtomatizacijo dolgih nalog

Ena od močnejših, a redko uporabljenih funkcij Excela je zmožnost zelo enostavnega ustvarjanja avtomatiziranih nalog in logike po meri v makrih. Makri so idealni način za prihranek časa pri predvidljivih, ponavljajočih se opravilih in standardiziranju formatov dokumentov - večkrat brez pisanja ene same vrstice kode.

Če vas zanima, kakšni makri so ali kako jih dejansko ustvarite, ni problema - mi vas bomo skozi celoten proces.

Opomba: isti proces mora delovati v večini različic Microsoft Office. Posnetki zaslona so lahko nekoliko drugačni.

Kaj je makro?

Microsoft Office Macro( kot ta funkcija velja za več aplikacij MS Office) je preprosto koda Visual Basic for Applications( VBA), shranjena v dokumentu. Za primerljivo analogijo, pomislite na dokument kot HTML in makro kot Javascript. Na podoben način, da lahko Javascript manipulira HTML na spletni strani, lahko makro manipulira z dokumentom. Makroji

so neverjetno močni in lahko naredijo kar koli, kar lahko domišljija predstavlja. Kot( zelo) kratek seznam funkcij, ki jih lahko naredite z makrom:

  • Uporabi slog in oblikovanje.
  • Manipulira podatke in besedilo.
  • Komunicirajte z viri podatkov( baze podatkov, besedilne datoteke itd.).
  • Ustvari popolnoma nove dokumente.
  • Vsaka kombinacija, v katerem koli vrstnem redu, katerega koli od zgoraj navedenih.

Ustvarjanje makro: pojasnilo na primer

Začnemo z vašo datoteko CSV različice vrta. Tu ni nič posebnega, le 10 × 20 nizov med 0 in 100 z glavo in stolpcem. Naš cilj je izdelati dobro oblikovan, predstavljiv podatkovni list, ki vključuje povzetke vsote za vsako vrstico.

Kot smo že omenili, je makro kodo VBA, vendar je ena izmed lepih stvari o Excelu, da jih lahko ustvarite / zabeležite z ničelnim kodiranjem, kar bomo naredili tukaj.

Če želite ustvariti makro, pojdite na Prikaži & gt;Makri & gt;Snemajte makro.

Dodeli makro ime( brez presledkov) in kliknite V redu.

Ko je to storjeno, zabeleži vse vaših dejanj - vsako spremembo celice, pomikanje, spreminjanje velikosti okna, jo imenujemo.

Obstaja nekaj krajev, ki kažejo, da je Excel način snemanja. Ena je tako, da si ogledate meni Macro in opozorite, da je Stop Recording nadomestil možnost za Macro Record.

Drugi je v spodnjem desnem kotu. Ikona za ustavitev označuje, da je v makro načinu, in s pritiskom na to tipko bo snemanje ustavljeno( prav tako, ko je v načinu snemanja, bo ta ikona gumb za snemanje makrov, ki ga lahko uporabite namesto da bi šel v meni Macros).

Zdaj, ko snemamo naš makro, uporabimo naše povzetke. Najprej dodajte glave.

Nato uporabite ustrezne formule( oziroma):

  • = SUM( B2: K2)
  • = AVERAGE( B2: K2)
  • = MIN( B2: K2)
  • = MAX( B2: K2)
  • = MEDIAN)

Zdaj označite vse računske celice in povlecite dolžino vseh vrst podatkov, da uporabite izračune za vsako vrstico.

Ko se to naredi, mora vsaka vrstica prikazati ustrezne povzetke.

Zdaj želimo pridobiti povzetek podatkov za celoten list, zato bomo uporabili še nekaj izračunov:

Ustrezno:

  • = SUM( L2: L21)
  • = AVERAGE( B2: K21) * To je treba izračunati za vsepodatkov, ker povprećje povprećja vrstic ni nujno enako povprećju vseh vrednosti.
  • = MIN( N2: N21)
  • = MAX( O2: O21)
  • = MEDIAN( B2: K21) * Izračunano po vseh podatkih iz istega razloga kot zgoraj.

Zdaj, ko so izračuni opravljeni, bomo uporabili slog in oblikovanje. Najprej uporabite splošno oblikovanje številk v vseh celicah, tako da izberete Select All( bodisi Ctrl + A bodisi kliknete celico med glave vrstice in stolpca) in v meniju Domov izberite ikono »Comma Style«.

Nato uporabite nekaj vizualnega oblikovanja v glave vrstic in stolpcev:

  • Bold.
  • Centered.
  • Barva ozadja za ozadje.

In nazadnje, uporabite nekaj sloga za vsote.

Ko je vse končano, je tisto, kar izgleda na našem podatkovnem listu:

Ker smo zadovoljni z rezultati, ustavimo snemanje makra.

Čestitam - pravkar ste ustvarili makro Excel.

Če želimo uporabiti naš novo narejeni makro, moramo shraniti naš Excelov delovni zvezek v makro omogočen format datoteke. Preden to storimo, moramo najprej izbrisati vse obstoječe podatke, tako da jih ni vdelano v našo predlogo( ideja je, da vsakič, ko uporabljamo to predlogo, uvažamo najnovejše podatke).

Če želite to narediti, izberite vse celice in jih izbrišite.

S podatki, ki so zdaj izbrisani( vendar so makri še vedno vključeni v datoteko Excel), jo želimo shraniti kot datoteko z makro omogočeno predlogo( XLTM).Pomembno je upoštevati, da če shranite to kot standardno datoteko( XLTX), potem makri ne bodo mogli zagnati in ne .Lahko pa datoteko shranite kot datoteko starejše predloge( XLT), ki bo omogočila zagon makrov.

Ko ste datoteko shranili kot predlogo, pojdite naprej in zaprite Excel.

Uporaba programa Excel Macro

Preden začnete pokrivati, kako lahko uporabimo ta novo zabeleženi makro, je pomembno, da pokrije nekaj točk o makrosih na splošno:

  • Makri so lahko zlonamerni.
  • Glejte zgornjo točko.

VBA koda je dejansko precej močna in lahko manipulira z datotekami zunaj obsega trenutnega dokumenta. Na primer, makro lahko spremeni ali izbriše naključne datoteke v mapi Moji dokumenti. Zato je pomembno, da samo iz zaženete makre iz zaupanja vrednih virov.

Če želite uporabiti makro za obliko podatkov, odprite datoteko Excel Template, ki je bila ustvarjena zgoraj. Ko to storite, če imate omogočene standardne varnostne nastavitve, boste na vrhu delovnega zvezka videli opozorilo, ki pravi, da so makri onemogočeni. Ker zaupamo makro, ki smo ga ustvarili sami, kliknite gumb »Omogoči vsebino«.

V nadaljevanju bomo uvozili najnovejši niz podatkov iz CSV-ja( to je vir, v katerem je delovni list ustvaril naš makro).

Če želite dokončati uvoz datoteke CSV, boste morda morali nastaviti nekaj možnosti, da bo Excel pravilno razlagal( npr. Razmejilnik, prisotni glavi itd.).

Ko so naši podatki uvoženi, preprosto pojdite v meni Makri( na kartici Pogled) in izberite Prikaži makre.

V dobljenem pogovornem oknu vidimo makro "FormatData", ki smo ga zabeležili zgoraj. Izberite to in kliknite Zaženi.

Po zagonu lahko vidite, da se kazalec za nekaj trenutkov skokne, vendar pa boste videli, kako so bili podatki manipulirani s točno , ko smo ga posneli. Ko je vse rečeno in storjeno, bi moralo izgledati podobno kot naš izvirnik - razen z različnimi podatki.

Pod nadzorom: Kaj naredi makro delo

Kot smo omenili že nekajkrat, je makro poganja koda Visual Basic for Applications( VBA).Ko "zapisujete" makro, Excel dejansko prevede vse, kar delate v svoja navodila VBA.Če rečete preprosto, vam ni treba napisati nobene kode, ker Excel piše kode za vas.

Če si želite ogledati kodo, v kateri je makro run, v pogovornem oknu Macros kliknite gumb Uredi.

V oknu, ki se odpre, se pri ustvarjanju makra prikaže izvorna koda, ki je bila posneta iz naših dejanj. Seveda lahko to kodo urejate ali celo v celoti ustvarjajo nove makre v oknu kode. Medtem ko bo ukrep za snemanje, uporabljen v tem članku, verjetno ustrezal večini potreb, bi morali zaradi zahtevnejših akcij ali pogojnih dejanj urediti izvorno kodo.

Vzorec našega primera En korak naprej. ..

Hipotetično, predpostavimo, da je naša izvorna podatkovna datoteka data.csv proizvedena s samodejnim procesom, ki vedno shrani datoteko na isto lokacijo( npr. C: \ Data \ data.csv je vedno najboljnedavni podatki).Postopek odpiranja te datoteke in njenega uvoza je mogoče enostavno narediti tudi v makro:

  1. Odprite datoteko Excel Template, ki vsebuje naš makro "FormatData".
  2. Zapišite nov makro z imenom "LoadData".
  3. Z makro snemanjem uvozite podatkovno datoteko, kot ste običajno.
  4. Ko so podatki uvoženi, prenehajte snemati makro.
  5. Izbrišite vse podatke celice( izberite vse, nato izbrišite).
  6. Shranite posodobljeno predlogo( ne pozabite uporabiti makro omogočene oblike predloge).

Ko se to naredi, se ob vsaki odprti predlogi pojavita dva makra - ena, ki nalaga podatke in druga, ki jo oblikuje.

Če ste resnično želeli, da bi svoje roke umazali z malo urejanja kod, lahko enostavno združite ta dejanja v en sam makro tako, da kopirate kodo, proizvedeno iz "LoadData", in jo vstavite na začetek kode iz "FormatData".

Prenos te predloge

Za vaše udobje smo vključili tako predlogo Excel, ki je izdelana v tem članku kot tudi vzorčno podatkovno datoteko, s katero se lahko igrate.

Prenesite predlogo Excel za Macro iz How-To Geek