Contents
Excel yana da ƙarfi, amma a lokaci guda ba a cika amfani da shi ba, ikon ƙirƙirar jerin ayyuka ta atomatik ta amfani da macros. Macro hanya ce mai kyau don fita idan kuna ma'amala da nau'in aiki iri ɗaya wanda ake maimaita sau da yawa. Misali, sarrafa bayanai ko tsara daftarin aiki bisa ga daidaitaccen samfuri. A wannan yanayin, ba kwa buƙatar sanin harsunan shirye-shirye.
Shin kun riga kun sha'awar menene macro da yadda yake aiki? Sa'an nan gaba gaba gaba - sa'an nan za mu mataki-mataki yi dukan tsari na samar da macro tare da ku.
Menene Macro?
Macro a cikin Microsoft Office (e, wannan aikin yana aiki iri ɗaya a yawancin aikace-aikacen kunshin Microsoft Office) lambar shirin ne a cikin yaren shirye-shirye. Kayayyakin Kayayyakin Kayayyakin Aikace-aikace (VBA) da aka adana a cikin daftarin aiki. Don ƙara bayyanawa, ana iya kwatanta daftarin aiki na Microsoft Office da shafin HTML, sannan macro shine analog na Javascript. Abin da Javascript zai iya yi da bayanan HTML a cikin shafin yanar gizon yana kama da abin da macro zai iya yi da bayanai a cikin takaddun Microsoft Office.
Macros na iya yin kusan duk abin da kuke so a cikin takarda. Ga wasu daga cikinsu (kadan kadan):
- Aiwatar da salo da tsarawa.
- Yi ayyuka daban-daban tare da bayanan lamba da rubutu.
- Yi amfani da tushen bayanan waje (fayil ɗin bayanan bayanai, takaddun rubutu, da sauransu)
- Irƙiri sabon daftarin aiki.
- Yi duk abubuwan da ke sama a kowace haɗuwa.
Ƙirƙirar macro - misali mai amfani
Misali, bari mu dauki mafi yawan fayil ɗin CSV. Wannan tebur ne mai sauƙi 10 × 20 cike da lambobi daga 0 zuwa 100 tare da kanun labarai na ginshiƙai da layuka. Aikinmu shine mu mayar da wannan saitin bayanai zuwa tebur wanda aka tsara da kuma samar da jimlar a kowane jere.
Kamar yadda aka ambata, macro shine lambar da aka rubuta a cikin harshen shirye-shiryen VBA. Amma a cikin Excel, zaku iya ƙirƙirar shirin ba tare da rubuta layin lamba ba, wanda zamuyi yanzu.
Don ƙirƙirar macro, buɗe view (Nau'i) > Macros (Macro) > Yi rikodin Macro (Macro rikodin…)
Ba macro suna (babu sarari) kuma danna OK.
Fara daga wannan lokacin, ana yin rikodin duk ayyukanku tare da takaddun: canje-canje zuwa sel, gungurawa cikin tebur, har ma da canza girman taga.
Excel yana nuna cewa an kunna yanayin rikodin macro a wurare biyu. Na farko, akan menu Macros (Macros) - maimakon kirtani Yi rikodin Macro (Yi rikodin macro…) layi ya bayyana Tsaya Rikodi (Dakatar da rikodi).
Na biyu, a cikin ƙananan hagu na taga Excel. Ikon Tsaya (karamin murabba'i) yana nuna cewa an kunna yanayin rikodin macro. Danna kan shi zai daina yin rikodin. Akasin haka, lokacin da ba a kunna yanayin rikodi ba, akwai gunki don kunna rikodin macro a wannan wurin. Danna kan shi zai ba da sakamako iri ɗaya kamar kunna rikodin ta menu.
Yanzu da yanayin rikodin macro ya kunna, bari mu shiga aikinmu. Da farko, bari mu ƙara kanun labarai don taƙaitaccen bayani.
Na gaba, shigar da dabarar a cikin sel daidai da sunayen taken (an ba da bambance-bambancen dabarun Ingilishi da nau'ikan Excel, adiresoshin salula koyaushe haruffan Latin ne da lambobi):
- = SUM(B2:K2) or = SUM(B2:K2)
- =MAGASKIYA(B2:K2) or (B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MADIYA(B2:K2) or =MADIYA(B2:K2)
Yanzu zaɓi sel ɗin tare da dabara kuma kwafa su zuwa duk layuka na teburin mu ta hanyar jan hannun cikawa ta atomatik.
Bayan kun kammala wannan mataki, kowane jere ya kamata ya sami jimlar daidai.
Na gaba, za mu taƙaita sakamakon duka tebur ɗin, don wannan muna yin wasu ƙarin ayyuka na lissafi:
Bi da bi:
- = SUM(L2:L21) or = SUM(L2:L21)
- =MAGASKIYA(B2:K21) or (B2:K21) - don ƙididdige wannan ƙimar, wajibi ne a ɗauki ainihin bayanan farko na tebur. Idan ka ɗauki matsakaicin matsakaici don layuka ɗaya, sakamakon zai bambanta.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MADIYA(B2:K21) or =MADIYA(B2:K21) - muna la'akari da yin amfani da bayanan farko na tebur, saboda dalilin da aka nuna a sama.
Yanzu da muka gama da lissafin, bari mu yi wani format. Da farko, bari mu saita tsarin nunin bayanai iri ɗaya don duk sel. Zaɓi duk sel akan takardar, don yin wannan, yi amfani da gajeriyar hanyar madannai Ctrl + Ako danna kan icon Zaɓi duk, wanda ke tsakanin mahadar layi da kanun shafi. Sannan danna Salon Wakafi (Delimited Format) tab Gida (Gida).
Na gaba, canza bayyanar ginshiƙi da masu kan layi:
- Salon rubutu mai ƙarfi.
- Daidaita tsakiya.
- Cika launi.
Kuma a ƙarshe, bari mu saita tsarin jimlar.
Wannan shi ne yadda yakamata ya kasance a ƙarshe:
Idan komai ya dace da ku, daina yin rikodin macro.
Taya murna! Kun yi rikodin macro na farko a cikin Excel da kanku.
Don amfani da macro da aka samar, muna buƙatar adana daftarin aiki na Excel a cikin tsarin da ke goyan bayan macros. Da farko, muna buƙatar share duk bayanan daga teburin da muka ƙirƙira, watau sanya shi samfuri mara kyau. Gaskiyar ita ce, a nan gaba, yin aiki tare da wannan samfuri, za mu shigo da mafi kwanan nan kuma bayanan da suka dace a ciki.
Don share duk sel daga bayanai, danna dama akan gunkin Zaɓi duk, wanda yake a tsakiyar layin layi da kanun shafi, kuma daga menu na mahallin, zaɓi share (Share).
Yanzu takardar mu an share shi gaba ɗaya daga duk bayanan, yayin da macro ya rage rikodin. Muna buƙatar adana littafin aiki azaman samfuri na Excel mai kunnawa macro wanda ke da tsawo XLTM.
Muhimmin batu! Idan kun ajiye fayil ɗin tare da tsawo XLTX, to, macro ba zai yi aiki a ciki ba. Af, zaku iya ajiye littafin aiki azaman samfuri na Excel 97-2003, wanda ke da tsari XLT, yana kuma goyan bayan macros.
Lokacin da aka ajiye samfuri, zaku iya rufe Excel lafiya.
Gudanar da Macro a cikin Excel
Kafin bayyana duk yuwuwar macro da kuka ƙirƙira, Ina tsammanin yana da kyau a kula da wasu mahimman mahimman bayanai game da macros gabaɗaya:
- Macros na iya zama cutarwa.
- Karanta sakin layi na baya kuma.
Lambar VBA tana da ƙarfi sosai. Musamman, yana iya aiwatar da ayyuka akan fayiloli a waje da takaddar yanzu. Misali, macro na iya share ko gyara kowane fayiloli a cikin babban fayil My takardun. Don wannan dalili, kawai gudu kuma ba da izinin macros daga tushen da kuka amince da su.
Don gudanar da macro na tsara bayanai, buɗe fayil ɗin samfuri da muka ƙirƙira a ɓangaren farko na wannan koyawa. Idan kuna da daidaitattun saitunan tsaro, to lokacin da kuka buɗe fayil, gargadi zai bayyana a saman tebur cewa macros ba su da rauni, da maɓallin kunna su. Tun da mun yi samfurin da kanmu kuma mun amince da kanmu, muna danna maɓallin Sanya Abun ciki (Hada abun ciki).
Mataki na gaba shine shigo da sabbin bayanan da aka sabunta daga fayil ɗin CSV (bisa irin wannan fayil ɗin, mun ƙirƙiri macro ɗin mu).
Lokacin da kake shigo da bayanai daga fayil ɗin CSV, Excel na iya tambayarka ka saita wasu saitunan don canja wurin bayanai daidai zuwa tebur.
Lokacin da aka gama shigo da kaya, je zuwa menu Macros (Macros) tab view (Duba) kuma zaɓi umarni Duba Macros (Macro).
A cikin akwatin maganganu da ke buɗewa, za mu ga layi tare da sunan macro FormatData. Zaɓi shi kuma danna Run (Kashe).
Lokacin da macro ya fara aiki, za ku ga siginan tebur yana tsalle daga cell zuwa tantanin halitta. Bayan ƴan daƙiƙa guda, za a yi ayyuka iri ɗaya tare da bayanan kamar lokacin rikodin macro. Lokacin da komai ya shirya, tebur ya kamata yayi kama da ainihin wanda muka tsara da hannu, kawai tare da bayanai daban-daban a cikin sel.
Bari mu dubi ƙarƙashin hular: Yaya macro ke aiki?
Kamar yadda aka ambata fiye da sau ɗaya, macro shine lambar shirin a cikin yaren shirye-shirye. Kayayyakin Kayayyakin Kayayyakin Aikace-aikace (VBA). Lokacin da kuka kunna yanayin rikodin macro, Excel a zahiri yana yin rikodin kowane aikin da kuka yi ta hanyar umarnin VBA. A sauƙaƙe, Excel yana rubuta muku lambar.
Don ganin wannan lambar shirin, kuna buƙatar a cikin menu Macros (Macros) tab view (view) danna Duba Macros (Macros) kuma a cikin akwatin maganganu da ke buɗewa, danna Shirya (canji).
Tagan yana buɗewa. Kayayyakin Kayayyakin Kayayyakin Aikace-aikace, wanda za mu ga lambar shirin na macro da muka yi rikodin. Ee, kun fahimta daidai, anan zaku iya canza wannan lambar har ma da ƙirƙirar sabon macro. Ayyukan da muka yi tare da tebur a cikin wannan darasi za a iya yin rikodin su ta amfani da rikodin macro ta atomatik a cikin Excel. Amma ƙarin hadaddun macros, tare da ingantaccen tsarin tsarin da dabaru na aiki, suna buƙatar shirye-shiryen hannu.
Bari mu ƙara mataki ɗaya zuwa aikinmu…
Ka yi tunanin fayil ɗin bayanan mu na asali data.csv ana ƙirƙira ta atomatik ta wasu tsari kuma koyaushe ana adana shi akan faifai a wuri ɗaya. Misali, C:Datadata.csv - hanyar zuwa fayil tare da sabunta bayanai. Hakanan ana iya yin rikodin tsarin buɗe wannan fayil ɗin da shigo da bayanai a cikin macro:
- Bude fayil ɗin samfuri inda muka ajiye macro- FormatData.
- Ƙirƙiri sabon macro mai suna LoadData.
- Yayin rikodin macro LoadData shigo da bayanai daga fayil data.csv – kamar yadda muka yi a sashin darasin da ya gabata.
- Lokacin da shigo da kaya ya cika, daina yin rikodin macro.
- Share duk bayanai daga sel.
- Ajiye fayil ɗin azaman samfuri na Excel mai kunna macro (tsawon XLTM).
Don haka, ta hanyar gudanar da wannan samfuri, kuna samun damar yin amfani da macro guda biyu - ɗayan yana ɗaukar bayanan, ɗayan yana tsara su.
Idan kuna son shiga cikin shirye-shirye, zaku iya haɗa ayyukan waɗannan macro guda biyu zuwa ɗaya - ta hanyar kwafin lambar daga kawai. LoadData zuwa farkon lambar FormatData.