Zamkatimu
Excel ili ndi mphamvu, koma nthawi yomweyo imagwiritsidwa ntchito kawirikawiri, kuthekera kopanga zotsatizana zokha pogwiritsa ntchito macros. A macro ndi njira yabwino yotulukira ngati mukuchita ndi mtundu womwewo wa ntchito yomwe imabwerezedwa nthawi zambiri. Mwachitsanzo, kukonza deta kapena kupanga zolemba molingana ndi template yokhazikika. Pankhaniyi, simuyenera kudziwa zilankhulo zamapulogalamu.
Mukufuna kale kudziwa kuti macro ndi chiyani komanso momwe imagwirira ntchito? Kenako molimba mtima pitirirani patsogolo - ndiye tidzachita pang'onopang'ono njira yonse yopangira macro ndi inu.
Macro ndi chiyani?
Macro mu Microsoft Office (inde, magwiridwe antchitowa amagwiranso ntchito mofananamo pamapulogalamu ambiri a Microsoft Office phukusi) ndi kachidindo ka chilankhulo cha pulogalamu. Zowoneka Mwachidule pa Mapulogalamu (VBA) yosungidwa mkati mwa chikalatacho. Kuti zimveke bwino, chikalata cha Microsoft Office chingafanane ndi tsamba la HTML, ndiye macro ndi analogue ya Javascript. Zomwe Javascript ingachite ndi data ya HTML patsamba lawebusayiti ndizofanana kwambiri ndi zomwe macro angachite ndi data mu chikalata cha Microsoft Office.
Macros amatha kuchita chilichonse chomwe mungafune muzolemba. Nazi zina mwa izo (gawo laling'ono kwambiri):
- Ikani masitayelo ndi masanjidwe.
- Chitani ntchito zosiyanasiyana pogwiritsa ntchito manambala ndi zolemba.
- Gwiritsani ntchito zinthu zakunja (mafayilo a database, zolemba, ndi zina zotero)
- Pangani chikalata chatsopano.
- Chitani zonse pamwambapa kuphatikiza kulikonse.
Kupanga macro - chitsanzo chothandiza
Mwachitsanzo, tiyeni titenge wapamwamba kwambiri CSV. Ili ndi tebulo losavuta la 10 × 20 lodzaza ndi manambala kuyambira 0 mpaka 100 okhala ndi mitu yamizere ndi mizere. Ntchito yathu ndikusandutsa deta iyi kukhala tebulo lokonzedwa bwino ndikupanga ziwopsezo pamzere uliwonse.
Monga tanenera kale, macro ndi code yolembedwa m'chinenero cha pulogalamu ya VBA. Koma mu Excel, mutha kupanga pulogalamu osalemba mzere wamakhodi, zomwe tizichita pakali pano.
Kuti mupange macro, tsegulani View (Mtundu) > Macros (Zambiri) > Lembani Macro (Kujambula kwa Macro…)
Patsani macro anu dzina (palibe mipata) ndikudina OK.
Kuyambira pano, zochita zanu ZONSE ndi chikalatacho zimajambulidwa: kusintha kwa ma cell, kuyendayenda patebulo, ngakhale kusinthira zenera.
Excel imawonetsa kuti kujambula kwa macro kumayatsidwa m'malo awiri. Choyamba, pa menyu Macros (Macros) - m'malo mwa chingwe Lembani Macro (Kujambula zazikulu…) mzere unawonekera Lekani Kujambula (Imani kujambula).
Chachiwiri, pakona yakumanzere kwa zenera la Excel. Chizindikiro Imani (mabwalo ang'onoang'ono) akuwonetsa kuti njira yojambulira yayikulu ndiyoyambitsidwa. Kusindikizapo kumasiya kujambula. Mosiyana ndi zimenezi, pamene kujambula sikuloledwa, pali chithunzi chothandizira kujambula kwakukulu pamalo ano. Kusindikiza pa izo kudzapereka zotsatira zofanana ndi kuyatsa kujambula kudzera menyu.
Tsopano popeza njira yojambulira macro yayatsidwa, tiyeni tifike kuntchito yathu. Choyamba, tiyeni tiwonjezere mitu yachidule cha data.
Kenako, lowetsani mafomuwa m'maselo molingana ndi mayina amitu (mitundu yosiyanasiyana yachingerezi ndi mitundu ya Excel imaperekedwa, ma adilesi a cell nthawi zonse amakhala zilembo zachilatini ndi manambala):
- =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)
Tsopano sankhani ma cell okhala ndi ma formula ndikuwakopera ku mizere yonse ya tebulo lathu pokokera chogwirizira chodzaza okha.
Mukamaliza sitepe iyi, mzere uliwonse uyenera kukhala ndi ziwopsezo zofananira.
Kenako, tifotokoza mwachidule zotsatira za tebulo lonse, chifukwa cha izi timachita masamu angapo:
Motsatira:
- =SUM(L2:L21) or =SUM(L2:L21)
- =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) - kuti muwerenge mtengo uwu, m'pofunika kutenga ndendende deta yoyamba ya tebulo. Ngati mutenga avareji ya mizere payokha, zotsatira zake zimakhala zosiyana.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) - timaganizira kugwiritsa ntchito deta yoyamba ya tebulo, pazifukwa zomwe tazitchula pamwambapa.
Tsopano popeza tamaliza kuwerengera, tiyeni tichite masanjidwe. Choyamba, tiyeni tiyike mawonekedwe amtundu womwewo wamaselo onse. Sankhani maselo onse papepala, kuti muchite izi, gwiritsani ntchito njira yachidule ya kiyibodi Ctrl + Akapena dinani chizindikirocho Sankhani zonse, yomwe ili pa mphambano ya mitu ya mizere ndi mizere. Kenako dinani Koma Style (Delimited Format) tabu Kunyumba (Kunyumba).
Kenako, sinthani mawonekedwe amizere ndi mitu yamizere:
- Mtundu wa zilembo za Bold.
- Kuyanjanitsa pakati.
- Kudzaza mitundu.
Ndipo pomaliza, tiyeni tikonze mawonekedwe a ziwopsezo.
Umu ndi momwe ziyenera kuwonekera pomaliza:
Ngati zonse zikugwirizana ndi inu, siyani kujambula ma macro.
Zabwino zonse! Mwangojambulira macro anu oyamba mu Excel nokha.
Kuti tigwiritse ntchito macro opangidwa, tiyenera kusunga chikalata cha Excel mumtundu womwe umathandizira ma macros. Choyamba, tifunika kuchotsa deta yonse patebulo lomwe tapanga, mwachitsanzo, likhale template yopanda kanthu. Chowonadi ndi chakuti mtsogolomo, pogwira ntchito ndi template iyi, tidzalowetsamo zaposachedwa komanso zofunikira.
Kuti muchotse ma cell onse ku data, dinani kumanja pachizindikirocho Sankhani zonse, yomwe ili m'mphepete mwa mizere ndi mitu, ndipo kuchokera pa menyu yankhaniyo, sankhani Chotsani (Chotsani).
Tsopano pepala lathu lachotsedwa kwathunthu kuzinthu zonse, pomwe macro amalembedwa. Tiyenera kusunga bukhuli ngati template ya Excel yothandizidwa ndi macro-yomwe ili ndi zowonjezera Zithunzi za XLTM.
Mfundo yofunika! Ngati musunga fayilo ndikuwonjezera Zithunzi za XLTX, ndiye macro sigwira ntchito mmenemo. Mwa njira, mutha kusunga bukuli ngati template ya Excel 97-2003, yomwe ili ndi mawonekedwe. XLT, imathandizanso ma macros.
Template ikasungidwa, mutha kutseka bwino Excel.
Kukhazikitsa Macro mu Excel
Ndisanaulule kuthekera konse kwa macro omwe mudapanga, ndikuganiza kuti ndibwino kulabadira mfundo zingapo zofunika pazambiri zonse:
- Macros akhoza kuvulaza.
- Werenganinso ndime yapitayi.
VBA code ndi yamphamvu kwambiri. Makamaka, imatha kugwira ntchito pamafayilo kunja kwa chikalata chomwe chilipo. Mwachitsanzo, macro amatha kufufuta kapena kusintha mafayilo aliwonse mufoda Zolembedwa zanga. Pazifukwa izi, ingothamanga ndikulola ma macros kuchokera kumagwero omwe mumawakhulupirira.
Kuti mugwiritse ntchito macro-formating macro, tsegulani fayilo ya template yomwe tidapanga gawo loyamba la phunziroli. Ngati muli ndi zoikamo zotetezedwa, ndiye mukatsegula fayilo, chenjezo lidzawonekera pamwamba pa tebulo kuti macros ali olumala, ndi batani kuti muwathandize. Popeza tinapanga template tokha ndipo timadzidalira tokha, timakanikiza batani Onetsani Zamkatimu (Phatikizanipo zomwe zili).
Chotsatira ndikulowetsa deta yosinthidwa posachedwa kuchokera mufayilo CSV (kutengera fayilo yotere, tidapanga macro athu).
Mukatumiza deta kuchokera ku fayilo ya CSV, Excel ikhoza kukufunsani kuti muyike zoikamo kuti muthe kusamutsa detayo patebulo.
Pamene kuitanitsa kwatha, pitani ku menyu Macros (Macros) tabu View (Onani) ndikusankha lamulo Onani Macros (Macro).
Mu bokosi la zokambirana lomwe limatsegulidwa, tiwona mzere wokhala ndi dzina la macro athu FormatData. Sankhani izo ndi kumadula Thamangani (Kuchita).
Pamene macro ayamba kuthamanga, mudzawona cholozera patebulo chikudumpha kuchokera ku selo kupita ku selo. Pambuyo pa masekondi angapo, ntchito zomwezo zidzachitidwa ndi deta monga pojambula macro. Zonse zikakonzeka, tebulo liyenera kuwoneka lofanana ndi loyambirira lomwe tidapanga ndi dzanja, pokhapokha ndi data yosiyana m'maselo.
Tiyeni tiwone pansi pa hood: Kodi macro amagwira ntchito bwanji?
Monga tafotokozera kangapo, macro ndi code code m'chinenero cha pulogalamu. Zowoneka Mwachidule pa Mapulogalamu (VBA). Mukayatsa njira yojambulira macro, Excel imalemba chilichonse chomwe mumapanga ngati malangizo a VBA. Mwachidule, Excel imakulemberani code.
Kuti muwone khodi ya pulogalamuyi, muyenera menyu Macros (Macros) tabu View (onani) dinani Onani Macros (Macros) ndi m'bokosi la zokambirana lomwe limatsegula, dinani Sinthani (Sintha).
Zenera likutseguka. Zowoneka Mwachidule pa Mapulogalamu, momwe tidzawonera nambala ya pulogalamu ya macro yomwe talemba. Inde, mudamvetsetsa bwino, apa mutha kusintha kachidindo iyi ndikupanga macro yatsopano. Zomwe tidachita ndi tebulo muphunziroli zitha kujambulidwa pogwiritsa ntchito kujambula kwa macro ku Excel. Koma ma macros ovuta kwambiri, omwe ali ndi ndondomeko yokhazikika bwino komanso malingaliro ochitapo kanthu, amafunikira mapulogalamu apamanja.
Tiyeni tiwonjezepo gawo lina ku ntchito yathu…
Tangoganizani kuti fayilo yathu yoyambirira data.csv imapangidwa yokha ndi njira ina ndipo imasungidwa pa diski pamalo omwewo. Mwachitsanzo, C:Datadata.csv - njira yopita ku fayilo yokhala ndi data yosinthidwa. Njira yotsegula fayiloyi ndikulowetsa deta kuchokera mmenemo imathanso kulembedwa mu macro:
- Tsegulani fayilo ya template pomwe tidasunga macro − FormatData.
- Pangani macro yatsopano yotchedwa LoadData.
- Pamene mukujambula macro LoadData lowetsani zambiri kuchokera ku fayilo data.csv - monga tinachitira m'gawo lapitalo la phunzirolo.
- Kutumiza kukamaliza, siyani kujambula ma macro.
- Chotsani deta yonse m'maselo.
- Sungani fayilo ngati template ya Excel yothandizidwa ndi macro (XLTM extension).
Chifukwa chake, poyendetsa template iyi, mumatha kupeza ma macros awiri - imodzi imanyamula deta, ina imapanga.
Ngati mukufuna kulowa mu mapulogalamu, mutha kuphatikiza zochita za ma macros awiriwa kukhala amodzi - kungotengera kachidindo kuchokera. LoadData mpaka koyambirira kwa code FormatData.