Contents
Wani yana so, amma ni da kaina ina buƙatar sandar matsayi kawai a cikin lokuta 2-3:
- bayan tacewa, yana nuna adadin ƙimar da suka rage bayan zaɓin
- lokacin da aka zaɓi kewayon, yana nuna jimla, matsakaita, da adadin sel da aka zaɓa
- a cikin yanayin manyan fayiloli, za ku iya ganin ci gaban da ake samu wajen sake lissafin ƙididdiga a cikin littafin.
Ba sosai ga layin da ke ɗaukar kusan faɗin allon gaba ɗaya ba kuma yana rataye akan shi koyaushe. Bari mu yi ƙoƙari mu faɗaɗa wannan madaidaicin jeri kuma mu ƙara wasu ƙarin fasaloli masu amfani gare shi 🙂
Gabaɗaya ƙa'idodi don sarrafa sandar matsayi
Sarrafa sandar matsayi tare da Kayayyakin Kayayyakin gani abu ne mai sauqi. Don nuna rubutun ku a ciki, zaku iya amfani da macro mai sauƙi:
Sub MyStatus() Application.StatusBar = "Pривет!" Ƙarshen Sub
Bayan gudanar da shi, muna samun:
Don dawo da ainihin yanayin ma'aunin matsayi, kuna buƙatar gajeriyar “anti-macro” iri ɗaya:
Sub MyStatus_Off() Application.StatusBar = Ƙarshen Ƙarshen Ƙarshen
A cikin sigar asali, kamar yadda kuke gani, komai yana da sauqi qwarai. Yanzu bari muyi kokarin haɓaka ra'ayin…
Adireshin kewayon da aka zaɓa a cikin ma'aunin matsayi
A cikin kusurwar hagu na sama na taga Excel a cikin mashaya dabara, koyaushe zaka iya ganin adireshin tantanin halitta na yanzu. Amma idan an zaɓi duka kewayon, to, da rashin alheri, ba za mu ga adireshin zaɓi a can ba - ana nuna tantanin halitta guda ɗaya mai aiki:
Don magance wannan matsalar, zaku iya amfani da macro mai sauƙi wanda zai nuna adireshin yankin da aka zaɓa a cikin ma'aunin matsayi. Bugu da ƙari, wannan macro ya kamata a ƙaddamar da shi ta atomatik, tare da kowane canji a cikin zaɓi akan kowane takarda - don wannan za mu sanya shi a cikin mai kula da taron. Zaɓi Canza littafinmu.
Bude Editan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Ta amfani da maballin suna iri ɗaya akan shafin developer (Mai haɓakawa) ko gajerun hanyoyin keyboard Hagu Alt+F11. Nemo littafin ku a kusurwar hagu na sama na panel Project kuma buɗe tsarin a cikinsa ta danna sau biyu wannan littafin (Wannan Littafin Aiki):
A cikin taga da yake buɗewa, kwafi kuma liƙa macro code mai zuwa:
Mai zaman kansa Sub Workbook_SheetSelectionChange(ByVal Sh A matsayin Abu, ByVal Target A Matsayin Range) Aikace-aikace.MatsayiBar = "Bayani:" & Zaɓi.Adireshin(0, 0) Ƙarshen Ƙarshe
Yanzu, lokacin da aka zaɓi kowane kewayo (ciki har da fiye da ɗaya!), adireshinsa za a nuna a mashigin matsayi:
Don hana adiresoshin jeri da yawa da aka zaɓa tare da Ctrl daga haɗuwa, zaku iya ƙara ƙaramin haɓakawa - yi amfani da aikin Sauya don maye gurbin waƙafi da waƙafi tare da sarari:
Mai zaman kansa Sub Workbook_SheetSelectionChange(ByVal Sh A matsayin Abu, ByVal Target As Range) Application.StatusBar = "Bыделено:" & Sauya (Zaɓi.Adireshin (0, 0), ",",", ", ") Ƙarshen Sub.
Adadin sel da aka zaɓa a cikin ma'aunin matsayi
Lokacin da aka zaɓi kowane kewayo, adadin sel waɗanda ba su da fanko suna nunawa a gefen dama na ma'aunin matsayi ta tsohuwa. Wani lokaci kana buƙatar sanin adadin da aka ware. Hakanan za'a iya cika wannan aikin tare da macro mai sauƙi don gudanar da taron littafin SelectionChange, kamar yadda yake cikin misalin da ya gabata. Kuna buƙatar macro kamar:
Canje-canje na Subbook_SheetSelectionChange(ByVal Sh A Matsayin Abu, ByVal Target As Range) Dim CellCount As Variant, rng A Matsayin Range Ga Kowane rng A Zaɓin. Yankuna 'Haɓaka ta duk zaɓin RowsCount = rng.Rows.Count' adadin Layuka ginshiƙai = sng. . Ƙidaya 'yawan ginshiƙai CellCount = CellCount + RowsCount * ColumnsCount' tattara jimlar adadin sel Gaba 'nunawa a cikin ma'aunin matsayi Application.StatusBar = "An zaɓa:" & CellCount & "sel" Ƙarshe Sub.
Wannan macro madaukai ta duk wuraren da aka zaɓa na Ctrl (idan akwai fiye da ɗaya), yana adana adadin layuka da ginshiƙai a kowane yanki a cikin RowsCount da ColumnsCount masu canji, kuma yana tara adadin sel a cikin ma'aunin CellCount, wanda sai a nuna shi. a cikin matsayi bar. A wurin aiki zai kasance kamar haka:
Tabbas, zaku iya haɗa wannan da macro na baya don nuna duka adireshi na kewayon da aka zaɓa da adadin sel a lokaci guda. Kuna buƙatar canza layi ɗaya kawai zuwa:
Application.StatusBar = "An Zaba:" & Sauya (Zaɓi.Adireshin (0, 0), ","," ", ") & "- jimlar" & CellCount & "sels"
Sa'an nan hoton zai zama mai ban mamaki sosai:
To, ina tsammanin kun sami ra'ayin. Ba da shawara a cikin sharhi - menene kuma zai zama da amfani don nunawa a ma'aunin matsayi?
- Menene macros, yadda suke aiki, yadda ake amfani da su da ƙirƙirar su
- Zaɓin daidaitawa mai dacewa akan takardar Excel
- Yadda ake yin hadaddun dabarar ƙarin gani