Mukati
Excel ine simba, asi panguva imwechete isingawanzo shandiswa, kugona kugadzira otomatiki kutevedzana kwezviito uchishandisa macros. A macro inzira yakanaka yekubuda kana uri kubata nemhando imwechete yebasa rinodzokororwa kakawanda. Semuyenzaniso, kugadzirisa data kana kufomatisa gwaro zvinoenderana neyakamisikidzwa template. Muchiitiko ichi, haudi ruzivo rwemitauro yekuronga.
Uri kutoda kuziva kuti macro chii uye kuti inoshanda sei? Wobva washinga enda kumberi - ipapo isu tichaita nhanho nhanho kuita maitiro ese ekugadzira macro newe.
Chii chinonzi Macro?
A macro muMicrosoft Office (hongu, kushanda uku kunoshanda zvakafanana mumashandisirwo mazhinji eMicrosoft Office package) ikodhi yepurogiramu mumutauro wechirongwa. Visual Basic yeZvikumbiro (VBA) yakachengetwa mukati megwaro. Kuti zvijeke, gwaro reMicrosoft Office rinogona kufananidzwa nepeji reHTML, ipapo macro is analogue yeJavascript. Izvo Javascript inogona kuita neHTML data mune peji rewebhu yakafanana chaizvo neiyo macro inogona kuita nedata muMicrosoft Office gwaro.
Macros anogona kuita chero chero chaunoda mugwaro. Heano mamwe acho (chikamu chidiki kwazvo):
- Shandisa masitayera uye mafomati.
- Ita mashandiro akasiyana nenhamba uye zvinyorwa data.
- Shandisa ekunze data masosi (database mafaera, zvinyorwa zvinyorwa, nezvimwewo)
- Gadzira gwaro nyowani.
- Ita zvese zviri pamusoro mune chero musanganiswa.
Kugadzira macro - muenzaniso unoshanda
Semuenzaniso, ngatitorei faira yakajairika CSV. Iyi itafura yakapfava 10 × 20 izere nenhamba kubva pa0 kusvika pa100 ine misoro yemakoramu nemitsara. Basa redu nderekushandura iyi data seti kuita tafura yakarongeka uye kugadzira mahota mumutsara wega wega.
Sezvatotaurwa, macro kodhi yakanyorwa mumutauro weVBA programming. Asi muExcel, unogona kugadzira chirongwa pasina kunyora mutsara wekodhi, izvo zvatichaita izvozvi.
Kugadzira macro, vhura maonero (Rudzi) > macro (Makoro) > Nyora Macro (Macro kurekodha…)
Ipa yako macro zita (hapana nzvimbo) uye tinya OK.
Kutanga kubva panguva ino, ZVINHU zvako zvese negwaro zvinorekodhwa: shanduko kumaseru, kupuruzira patafura, kunyange kudzoreredza hwindo.
Excel inosaina kuti iyo macro yekurekodha modhi inogoneswa munzvimbo mbiri. Kutanga, pane menyu macro (Macros) - pachinzvimbo chetambo Nyora Macro (Kurekodha macro…) mutsara wakaonekwa Mira Kurekodha (Misa kurekodha).
Chechipiri, mukona yezasi kuruboshwe yehwindo reExcel. Icon mira (skweya diki) inoratidza kuti iyo macro yekurekodha modhi inogoneswa. Kudzvanya pairi kucharega kurekodha. Sezvineiwo, kana iyo yekurekodha modhi isina kugoneswa, pane icon yekugonesa macro kurekodha munzvimbo ino. Kudzvanya pairi kunopa mhedzisiro yakafanana nekubatidza kurekodha kuburikidza nemenyu.
Zvino kuti iyo macro yekurekodha modhi yakagoneswa, ngatisvikei kubasa redu. Chekutanga, ngatiwedzerei misoro yepfupi data.
Tevere, isa mafomula mumaseru zvinoenderana nemazita emisoro (misiyano yemafomula echiRungu uye shanduro dzeExcel dzinopihwa, kero dzesero dzinogara dziri mavara echiLatin nenhamba):
- =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)
Zvino sarudza maseru ane mafomula uye woakopa kune ese mitsara yetafura yedu nekukweva iyo autofill mubato.
Kana wapedza nhanho iyi, mutsara wega wega unofanirwa kuve nehwerengedzo inoenderana.
Tevere, tichapfupisa mibairo yetafura yese, nekuda kweizvi tinoita mamwe mashoma masvomhu mabasa:
Zvichienderana:
- =SUM(L2:L21) or =SUM(L2:L21)
- =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) - kuverenga kukosha uku, zvinodikanwa kutora chaizvo data rekutanga retafura. Kana iwe ukatora avhareji yeavhareji yemitsara yega yega, mhedzisiro ichave yakasiyana.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) - tinofunga kushandisa data yekutanga yetafura, nekuda kwechikonzero chakaratidzwa pamusoro.
Zvino zvatapedza macalculations, ngatiitei mamwe mafomati. Kutanga, ngatisete iyo yakafanana data kuratidza fomati yemasero ese. Sarudza maseru ese papepa, kuti uite izvi, shandisa nzira yekudimbudzira keyboard Ctrl + Akana kuti tinya pane icon Sarudza zvose, iyo iri pamharadzano yemisoro yemutsara nembiru. Wobva wadzvanya Comma Style (Delimited Format) tab Home (Kumba).
Tevere, shandura chimiro chekoromo uye mitsara misoro:
- Bold font style.
- Kurongeka kwepakati.
- Kuzadza ruvara.
Uye pakupedzisira, ngatisengei mafomati ehuwandu.
Aya ndiwo maitiro anofanira kutaridzika senge pakupedzisira:
Kana zvese zvichikukodzera, rega kurekodha macro.
Makorokoto! Iwe uchangorekodha yako yekutanga macro muExcel iwe pachako.
Kuti tishandise macro yakagadzirwa, tinoda kuchengetedza gwaro reExcel mufomati inotsigira macros. Kutanga, isu tinofanirwa kudzima data rese kubva patafura yatakagadzira, kureva kuti iite isina chinhu template. Icho chokwadi ndechekuti mune ramangwana, tichishanda ne template iyi, isu tichapinza iyo yazvino uye yakakosha data mairi.
Kuti ubvise maseru ese kubva padata, tinya-kurudyi pane icon Sarudza zvose, iyo iri pamharadzano yemutsara uye mbiru misoro, uye kubva pane zviri mukati menyu, sarudza Kudzima (Delete).
Iye zvino pepa redu rakanyatsocheneswa data rese, nepo macro inoramba yakanyorwa. Isu tinofanirwa kuchengetedza bhuku rebasa senge macro-enabled Excel template ine yekuwedzera XLTM.
Pfungwa yakakosha! Kana iwe ukachengeta faira nekuwedzera XLTX, ipapo macro haishande mairi. Nenzira, iwe unogona kuchengetedza bhuku rebasa seExcel 97-2003 template, ine fomati. XLT, inotsigirawo macros.
Kana iyo template yachengetwa, unogona kuvhara zvakachengeteka Excel.
Kumhanyisa Macro muExcel
Ndisati ndaburitsa zvese zvingangoitika zve macro iwe wawakagadzira, ndinofunga kuti zvakanaka kuterera kune akati wandei mapoinzi akakosha maererano nemacros zvakazara:
- Macros inogona kukuvadza.
- Verenga ndima yapfuura zvakare.
VBA kodhi ine simba kwazvo. Kunyanya, inogona kuita mashandiro pamafaira ari kunze kwegwaro razvino. Semuenzaniso, macro inogona kudzima kana kugadzirisa chero mafaera muforodha Zvinyorwa zvangu. Nechikonzero ichi, mhanya chete uye ubvumire macros kubva kwaunovimba.
Kuti timhanye yedu data-fomati macro, vhura iyo template faira yatakagadzira muchikamu chekutanga chechidzidzo ichi. Kana iwe uine yakajairwa kuchengetedza zvigadziriso, saka kana iwe ukavhura faira, yambiro ichaonekwa pamusoro petafura iyo macros akaremara, uye bhatani rekugonesa. Sezvo isu takagadzira template isu pachedu uye tichizvivimba, tinodzvanya bhatani Gonesa zvirimo (Batanidza zvirimo).
Nhanho inotevera ndeyekuunza iyo yazvino yakagadziridzwa dataset kubva mufaira CSV (zvichienderana nefaira rakadaro, isu takagadzira yedu macro).
Paunopinza dhata kubva kuCSV faira, Excel inogona kukukumbira kuti uise mamwe marongero kuitira kunyatsoendesa data kutafura.
Kana kupinza kwapera, enda kumenyu macro (Macros) tab maonero (Ona) uye sarudza murairo View Macros (Makoro).
Mubhokisi rebhokisi rinovhura, tichaona mutsara une zita re macro yedu FormatData. Sarudza iyo uye tinya manya (Execute).
Kana iyo macro inotanga kumhanya, iwe uchaona tafura cursor ichisvetuka kubva kune sero kuenda kune sero. Mushure memasekondi mashoma, mashandiro akafanana achaitwa nedata senge pakurekodha macro. Kana zvese zvagadzirwa, tafura inofanirwa kutaridzika yakafanana neyekutanga yatakagadzira nemaoko, chete nedata rakasiyana mumaseru.
Ngatitarisei pasi pehodhi: Iyo macro inoshanda sei?
Sezvakataurwa kanopfuura kamwe, macro ikodhi yepurogiramu mumutauro wepurogiramu. Visual Basic yeZvikumbiro (VBA). Paunobatidza macro kurekodha modhi, Excel inorekodha zvese zvaunoita nenzira yeVBA mirairo. Zvichitaurwa zviri nyore, Excel inonyora iyo kodhi iwe.
Kuti uone iyi kodhi yepurogiramu, iwe unoda mune menyu macro (Macros) tab maonero (ona) tinya View Macros (Macros) uye mubhokisi rebhokisi rinovhura, tinya pepeta (Shandura).
Hwindo rinovhurika. Visual Basic yeZvikumbiro, umo tichaona kodhi yepurogiramu ye macro yatakanyora. Ehe, iwe wakanzwisisa nemazvo, pano iwe unogona kushandura iyi kodhi uye kunyange kugadzira macro nyowani. Zviito zvatakaita netafura muchidzidzo ichi zvinogona kurekodhwa tichishandisa otomatiki macro kurekodha muExcel. Asi mamwe macros akaomarara, ane kutevedzana kwakanyatso kurongeka uye maitiro ekuita, anoda hurongwa hwemanyorero.
Ngatiwedzere imwe nhanho kubasa redu…
Fungidzira kuti yedu yepakutanga data faira data.csv inogadzirwa otomatiki neimwe nzira uye inogara ichichengetwa pa diski munzvimbo imwechete. Semuyenzaniso, C:Datadata.csv - nzira yefaira ine data yakagadziridzwa. Maitiro ekuvhura iyi faira uye kupinza data kubva kwairi anogona zvakare kurekodhwa mune macro:
- Vhura iyo template faira kwatakachengeta iyo macro - FormatData.
- Gadzira itsva macro inonzi LoadData.
- Ndichiri kurekodha macro LoadData pinza data kubva kufaira data.csv - sezvatakaita muchikamu chakapfuura chechidzidzo.
- Kana kupinza kwapera, rega kurekodha macro.
- Bvisa data rese kubva kumaseru.
- Sevha iyo faira senge macro-enabled Excel template (XLTM yekuwedzera).
Nekudaro, nekumhanyisa iyi template, iwe unowana mukana kune maviri macros - imwe inotakura iyo data, imwe inoagadzira.
Kana iwe uchida kupinda muchirongwa, unogona kusanganisa zviito zveaya macros kuita imwe - nekungokopa kodhi kubva. LoadData kusvika pakutanga kwekodhi FormatData.