Contents
Idan har yanzu ba ku saba da macros a cikin Excel ba, to na ɗan yi muku hassada. Jin ikon komai da fahimtar cewa Microsoft Excel na iya haɓaka kusan zuwa rashin iyaka wanda zai zo muku bayan sanin macros yana jin daɗi.
Duk da haka, wannan labarin yana ga waɗanda suka riga sun "koyi ikon" kuma sun fara amfani da macros (baƙin waje ko rubuta da kansu - ba kome) a cikin aikin yau da kullum.
Macro shine lambar (layi da yawa) a cikin Visual Basic harshe wanda ke sa Excel yin abin da kuke buƙata: sarrafa bayanai, samar da rahoto, kwafi-manna tebur mai maimaitawa da yawa, da dai sauransu. Tambayar ita ce ta ina za a adana waɗannan ƙananan layukan lambobin? Bayan haka, inda aka adana macro zai dogara ne akan inda zai iya (ko ba zai iya) aiki ba.
Если макрос решает небольшает Otchet dannыe osobыm obrazom), don yin la'akari da koyo game da abin da ke faruwa. Без вопросов.
Kuma idan macro ya kamata ya zama ingantacciyar duniya kuma ana buƙata a cikin kowane littafin aikin Excel - kamar, alal misali, macro don canza ƙima zuwa ƙima? Me ya sa ba kwa kwafin Kayayyakin Basic code ɗin sa cikin kowane littafi kowane lokaci? Bugu da kari, ba dade ko ba dade, kusan kowane mai amfani ya zo ga ƙarshe cewa zai yi kyau a saka duk macros a cikin akwati ɗaya, watau su kasance koyaushe a hannu. Kuma watakila ma ba gudu da hannu ba, amma tare da gajerun hanyoyin keyboard? Wannan shine inda Littafin Ayyukan Macro na Keɓaɓɓen zai iya zama babban taimako.
Yadda ake ƙirƙirar Littafin macro na sirri
A gaskiya ma, Littafin Macros na sirri (LMB) fayil ne na Excel na yau da kullun a tsarin littafin aiki na binary (Na sirri.xlsb), wanda ke buɗewa ta atomatik a yanayin ɓoye lokaci ɗaya da Microsoft Excel. Wadancan. lokacin da kawai ka fara Excel ko buɗe kowane fayil daga diski, ana buɗe fayiloli guda biyu - naka da Personal.xlsb, amma ba mu ga na biyun ba. Don haka, duk macros da aka adana a cikin LMB suna samuwa don ƙaddamarwa a kowane lokaci yayin da Excel ke buɗewa.
Idan baku taɓa amfani da LMB ba, to da farko fayil ɗin Personal.xlsb ba ya wanzu. Hanya mafi sauƙi don ƙirƙirar shi ita ce yin rikodin wasu macro mara ma'ana mara amfani tare da mai rikodin, amma ƙayyade Littafin Keɓaɓɓen azaman wurin da za a adana shi - sannan za a tilasta Excel ya ƙirƙira muku ta atomatik. Don wannan:
- danna developer (Mai haɓakawa). Idan tabs developer ba a bayyane, to ana iya kunna shi a cikin saitunan ta hanyar Fayil - Zaɓuɓɓuka - Saitin Ribbon (Gida - Zaɓuɓɓuka - Kirkirar da Ribbon).
- A kan Babba shafin developer click Rikodin macro (Macro rikodin). A cikin taga da yake buɗewa, zaɓi Littafin macro na sirri (Littafin Aikin Macro na Sirri) azaman wurin adana lambar da aka rubuta kuma latsa OK:
- Dakatar da rikodi tare da maɓalli Tsaya Rikodi (Dakatar da Rikodi) tab developer (Mai haɓakawa)
Kuna iya duba sakamakon ta danna maɓallin Kayayyakin aikin Basic dama can kan tab. developer – a cikin bude editan taga a saman kusurwar hagu a kan panel Project — VBA Project ya kamata fayil ɗin mu ya bayyana NA KAI. XLSB. Reshensa wanda za'a iya fadada shi tare da alamar ƙari a hagu, yana kaiwa Module1, inda aka adana lambar macro mara ma'ana da muka yi rikodin yanzu:
Taya murna, yanzu kun ƙirƙiri naku Littafin Macro na Keɓaɓɓen! Kada ka manta kawai danna maɓallin ajiyewa tare da faifan floppy a kusurwar hagu na sama na mashaya.
Yadda ake amfani da Littafin Macro na sirri
Sannan komai yana da sauki. Duk wani macro da kuke buƙata (watau yanki na lamba farawa da sub kuma ƙarewa karshen Sub) za a iya kwafi da liƙa cikin aminci cikin aminci Module1, ko a cikin keɓantaccen tsarin, ƙara shi a baya ta cikin menu Saka - Module. Ajiye duk macros a cikin module ɗaya ko shimfiɗa su a cikin daban-daban al'amari ne na ɗanɗano kawai. Ya kamata yayi kama da wani abu kamar haka:
Kuna iya kunna macro da aka ƙara a cikin akwatin maganganu da ake kira tare da maɓallin Macros (Macros) tab developer:
A cikin wannan taga, ta danna maballin Siga (Zaɓuɓɓuka), za ka iya saita gajeriyar hanyar madannai don tafiyar da macro da sauri daga madannai. Yi hankali: gajerun hanyoyin madannai don macros suna bambanta tsakanin shimfidawa (ko Turanci) da harka.
Baya ga tsarin macro da aka saba a cikin Littafin Keɓaɓɓu, kuna iya adanawa ayyuka macro na al'ada (UDF = Ƙayyadaddun Ayyukan Mai Amfani). Ba kamar matakai ba, lambar aiki tana farawa da sanarwa aikior Ayyukan Jama'a, kuma ya ƙare da karshen Aiki:
Dole ne a kwafi lambar ta hanya ɗaya zuwa kowane nau'i na littafin PERSONAL.XLSB, sa'an nan kuma zai yiwu a kira aikin a hanyar da aka saba, kamar kowane daidaitaccen aikin Excel, ta danna maɓallin. fx a cikin dabara bar kuma zaɓi wani aiki a cikin taga Mayukan Ayyuka a cikin category Ma'anar Mai amfani (An bayyana mai amfani):
Ana iya samun misalan irin waɗannan ayyuka da yawa akan Intanet ko a nan akan rukunin yanar gizo (yawan cikin kalmomi, ƙimantan binciken rubutu, VLOOKUP 2.0, canza Cyrillic zuwa fassarar, da sauransu).
Ina ake adana Littafin Macro na Keɓaɓɓen?
Idan kuna amfani da Keɓaɓɓen Littafin Macros, to ba dade ko ba dade za ku sami sha'awar:
- raba tarin macros ɗinku tare da sauran masu amfani
- kwafi da canja wurin Keɓaɓɓen Littafin zuwa wata kwamfuta
- yi kwafin ajiya
Don yin wannan, kuna buƙatar nemo fayil ɗin PERSONAL.XLSB akan faifan kwamfutarka. Ta hanyar tsoho, ana adana wannan fayil a cikin babban fayil na farawa na Excel mai suna XLSTART. Don haka duk abin da ake buƙata shine zuwa wannan babban fayil akan PC ɗin mu. Kuma anan ne aka ɗan samu matsala, domin wurin da wannan babban fayil ɗin yake ya dogara da nau'in Windows da Office kuma yana iya bambanta. Wannan yawanci ɗaya ne daga cikin zaɓuɓɓuka masu zuwa:
- C: Fayilolin ShirinMicrosoft OfficeOffice12XLSTART
- C: Takardu da Saituna ComputerApplication DataMicrosoftExcelXLSTART
- C: Masu amfanisunan ku-asusuAppDataRoamingMicrosoftExcelXLSTART
A madadin, zaku iya tambayar Excel kanta don wurin da wannan babban fayil yake ta amfani da VBA. Don yin wannan, a cikin Visual Basic edita (button Kayayyakin aikin Basic tab developer) нужно открыть окно nan da nan Hanyar gajeren hanya Ctrl + G, rubuta a cikin umurnin ? Aikace-aikace.Hanyar Farawa da kuma danna kan Shigar:
Za a iya kwafi da liƙa hanyar da ta haifar a cikin saman layi na taga Explorer a cikin Windows kuma danna Shigar - kuma za mu ga babban fayil tare da Keɓaɓɓen Littafin Macros fayil:
PS
Kuma wasu 'yan nuances masu amfani a cikin neman:
- Lokacin amfani da Littafin Macro na Keɓaɓɓen, Excel zai ɗan yi tafiya a hankali, musamman akan kwamfutoci masu rauni
- yana da daraja lokaci-lokaci share Littafi Mai-Tsarki daga sharar bayanai, tsofaffi da macro maras buƙata, da sauransu.
- Masu amfani da kamfani wani lokaci suna samun matsala ta amfani da Littafin Keɓaɓɓu, tk. wannan fayil ne a cikin babban fayil ɗin ɓoye na tsarin
- Menene macro da yadda ake amfani da su a cikin aikin ku
- Amfani ga mai tsara shirye-shiryen VBA
- Horar da "Macro Programming a cikin VBA a cikin Microsoft Excel"