Contents
Wani lokaci yana ɗaukar lokaci mai tsawo don fito da wasu abubuwa. Amma a lokacin da aka riga aka ƙirƙira su, bayan gaskiyar sun kasance a bayyane kuma har ma da banal. Daga jerin "menene, zai yiwu?".
Daga nau'ikan farko, madaidaicin matsayi a ƙasan taga Microsoft Excel bisa ga al'ada yana nuna jimlar sel da aka zaɓa:
Idan ana so, yana yiwuwa ma a danna-dama akan waɗannan sakamakon kuma zaɓi daga menu na mahallin daidai ayyukan da muke son gani:
Kuma kwanan nan, a cikin sabbin abubuwan sabuntawa na Excel, masu haɓaka Microsoft sun ƙara fasali mai sauƙi amma fasaha - yanzu lokacin da kuka danna waɗannan sakamakon, ana kwafi su zuwa allo!
Beauty.
Amma menene game da waɗanda ba tukuna (ko riga?) suna da irin wannan sigar Excel? Wannan shine inda macros masu sauƙi zasu iya taimakawa.
Kwafi jimlar sel da aka zaɓa zuwa Clipboard ta amfani da macro
Bude cikin shafin developer (Mai haɓakawa) edita Kayayyakin aikin Basic ko amfani da wannan gajeriyar hanyar keyboard alt+F11. Saka sabon tsarin komai ta hanyar menu Saka - Module sannan ka kwafi wannan code din can:
Sub SumSelected() If TypeName(Selection) <> "Range" Sai Ku Fita Sub Tare da GetObject("Sabo:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") Hankalinsa mai sauki ne:
- Na farko ya zo "kariya daga wawa" - muna duba abin da aka nuna daidai. Idan ba sel ba an zaɓi (amma, alal misali, ginshiƙi), sannan fita macro.
- Sannan amfani da umarnin Samun abu mun ƙirƙiri sabon abu na bayanai inda za a adana jimlar zaɓaɓɓun sel daga baya. Dogayen lambar haruffa da ba za a iya fahimta ba ita ce, a haƙiƙa, hanyar haɗi zuwa reshen rajista na Windows inda ɗakin karatu yake. Microsoft Forms 2.0 Library Library, wanda zai iya haifar da irin waɗannan abubuwa. Wani lokaci ana kiran wannan dabara daure a fakaice. Idan ba ku yi amfani da shi ba, to dole ne ku yi hanyar haɗi zuwa wannan ɗakin karatu a cikin fayil ta menu Kayayyakin aiki - Nassoshi.
- Jimlar sel da aka zaɓa ana ɗaukar umarni Takardun Aiki.Sum(Zaɓi), sannan ana sanya adadin da aka samu akan allo tare da umarnin PutInClipboard
Don sauƙin amfani, zaku iya, ba shakka, sanya wannan macro zuwa gajeriyar hanyar madannai ta amfani da maɓallin Macros tab developer (Developer - Macros).
Kuma idan kuna son ganin ainihin abin da aka kwafi bayan kunna macro, zaku iya kunna panel Clipboard ta amfani da ƙaramin kibiya a kusurwar dama na ƙungiyar da ta dace akan. babban (Gida) tab:
Ba kawai adadin ba
Idan, ban da adadin banal, kuna son wani abu dabam, to zaku iya amfani da kowane ɗayan ayyukan da abin ya samar mana. Aikin Aiki:
Misali, akwai:
- Suma - suma
- Matsakaicin - ma'anar lissafi
- Ƙidaya – adadin ƙwayoyin sel masu lambobi
- CountA - adadin cika sel
- CountBlank – adadin sel mara komai
- Min - mafi ƙarancin ƙima
- Max - matsakaicin ƙimar
- Matsakaici - Matsakaici (ƙimar tsakiya)
- … Da dai sauransu
Me zai faru idan layuka ko ginshiƙai suna ɓoye (da hannu ko ta hanyar tacewa) a cikin kewayon da aka zaɓa? Domin kada mu yi la'akari da su a cikin jimlar, za mu buƙaci mu ɗan canza lambar mu ta ƙara zuwa abu selection dukiya SpecialCells (xlCellType Ana iya gani):
Sub SumVisible() If TypeName(Selection) <> "Range" Sai Ku Fita Sub Tare da GetObject("Sabo:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Zaɓi.SpecialCell)T Ƙarshen PutInClipboard Tare da Ƙarshen Sub A wannan yanayin, lissafin kowane aiki gabaɗaya za a yi amfani da shi ga sel masu gani kawai.
Idan kuna buƙatar tsarin rayuwa
Idan kun yi mafarki, za ku iya zuwa tare da al'amuran lokacin da ya fi dacewa don kwafi ba lamba (m), amma tsari mai rai a cikin buffer, wanda ke ƙididdige jimlar da muke bukata don sel da aka zaɓa. A wannan yanayin, dole ne ku manne dabarar daga gutsuttsura, ƙara masa cire alamun dollar da maye gurbin waƙafi (wanda ake amfani da shi azaman mai raba tsakanin adiresoshin jeri da yawa da aka zaɓa a cikin VBA) tare da semicolon:
Sub SumFormula() If TypeName(Selection) <> "Range" Sai Ku Fita Sub Tare da GetObject("Sabon:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") Adireshin, ",", ";"), "$", "") & ")" .PutInClipboard Ƙarshen Ƙarshen Ƙarshe Takaitawa tare da ƙarin sharuɗɗa
Kuma, a ƙarshe, don maniacs gaba ɗaya, za ku iya rubuta macro wanda ba zai taƙaita duk sel da aka zaɓa ba, amma kawai waɗanda suka gamsar da yanayin da aka bayar. Don haka, alal misali, macro zai yi kama da wanda ke sanya jimlar sel da aka zaɓa a cikin Buffer, idan ƙimar su ta fi 5 kuma a lokaci guda suna cike da kowane launi:
Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Sai Ka Fita Sub For Kowane cell In Selection If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone To Idan myRange Is Nothing Sai Set myRange = cell Else Set myRange = Ƙungiyar (myRange, cell) Ƙare Idan Ƙare Idan Tantanin halitta na gaba Tare da GetObject("Sabo:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) . Tare da Ƙarshen SubClipboard Kamar yadda zaku iya tunani cikin sauƙi, ana iya saita yanayi gaba ɗaya - har zuwa tsarin tantanin halitta - kuma a kowane adadi (ciki har da haɗa su tare da masu aiki masu ma'ana ko ko kuma). Akwai wuri mai yawa don tunani.
- Mayar da dabara zuwa ƙima (hanyoyi 6)
- Menene macros, yadda ake amfani da su, inda za a saka lambar Visual Basic
- Bayani mai amfani a cikin matsayi na Microsoft Excel