Okuqukethwe
Kwesinye isikhathi kuthatha isikhathi eside kakhulu ukuqhamuka nezinto ezithile. Kodwa uma SEKUNGEZIQALILE, ngemuva kweqiniso zibonakala zisobala futhi zivinjelwe. Kusukela ochungechungeni "yini, kwakungenzeka?".
Kusukela ezinguqulweni zokuqala, ibha yesimo engezansi kwewindi le-Microsoft Excel ngokuvamile iboniswa amanani amaseli akhethiwe:
Uma uthanda, bekungenzeka ukuthi uchofoze kwesokudla kule miphumela bese ukhetha kumenyu yokuqukethwe ukuthi yimiphi imisebenzi esifuna ukuyibona:
Futhi muva nje, ezibuyekezweni zakamuva ze-Excel, abathuthukisi beMicrosoft bengeze isici esilula kodwa esihlakaniphile - manje uma uchofoza le miphumela, ikopishwa ebhodini lokunamathisela!
Ubuhle.
Kodwa kuthiwani ngalabo abangakabi nalo (noma kakade?) abangakabi nalo inguqulo ye-Excel? Yilapho amamakhro alula angasiza khona.
Ikopisha isamba samaseli akhethiwe ebhodini lokunamathisela usebenzisa i-macro
Vula kuthebhu Unjiniyela (Unjiniyela) umhleli Visual Basic noma sebenzisa lesi sinqamuleli sekhibhodi Alt+F11. Faka imojuli entsha engenalutho ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:
I-SumSumSelected() Uma I-TypeName(Ukhetho) <> "Ububanzi" Bese Uphuma Ku-Sub Nge-GetObject("Okusha:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Khetho Lokukhethayo) EndIn Sublipboard . I-logic yayo ilula:
- Okokuqala kufika "ukuvikelwa koyisiwula" - sihlola ukuthi yini ngempela egqanyisiwe. Uma kungewona amaseli akhethiwe (kodwa, isibonelo, ishadi), bese uphuma kumamakhro.
- Bese usebenzisa umyalo I-Getobject sakha into entsha yedatha lapho isamba sethu samaseli akhethiwe sizogcinwa kamuva. Ikhodi ende nengaqondakali ye-alphanumeric, empeleni, iyisixhumanisi segatsha lokubhalisa le-Windows lapho umtapo wezincwadi ukhona. I-Microsoft Forms 2.0 Object Library, okungadala izinto ezinjalo. Ngezinye izikhathi leli qhinga libizwa nangokuthi ukubopha sekwephuzile okungacacile. Uma ungayisebenzisi, kuzodingeka wenze isixhumanisi salo mtapo wezincwadi efayelini ngokusebenzisa imenyu Amathuluzi - Izikhombo.
- Isamba samaseli akhethiwe sithathwa njengomyalo Umsebenzi Weshidi Lokusebenza.Isamba(Ukukhethwa), bese inani eliwumphumela libekwa ebhodini lokunamathisela ngomyalo I-PutInClipboard
Ukuze kube lula ukuyisebenzisa, ungakwazi, vele, ukwabela le macro kusinqamuleli sekhibhodi usebenzisa inkinobho amamakhro ithebhu Unjiniyela (Unjiniyela - Amamakhro).
Futhi uma ufuna ukubona ukuthi yini ngempela ekopishwe ngemva kokusebenzisa i-macro, ungavula iphaneli ye-Clipboard usebenzisa umcibisholo omncane osekhoneni elingezansi kwesokudla leqembu elihambisanayo. The main (Ikhaya) ithebhu:
Hhayi kuphela inani
Uma, ngaphezu kwenani le-banal, ufuna enye into, ungasebenzisa noma yimiphi imisebenzi into esihlinzeka ngayo. Umsebenzi Weshidi Lokusebenza:
Ngokwesibonelo, kukhona:
- Isamba - isamba
- Isilinganiso - isilinganiso se-arithmetic
- Bala – inombolo yamaseli anezinombolo
- I-CountA – inombolo yamaseli agcwalisiwe
- I-CountBlank – inombolo yamaseli angenalutho
- Ubuncane - inani elincane
- Ubukhulu – inani eliphezulu
- I-Median - i-median (inani elimaphakathi)
- … Njll.
Kuthiwani uma imigqa noma amakholomu efihliwe (ngesandla noma ngesihlungi) ebangeni elikhethiwe? Ukuze singazinaki kumathothali, sizodinga ukushintsha kancane ikhodi yethu ngokungeza entweni. Ukukhethwa impahla Amaseli Akhethekile(xlCellTypeVisible):
I-SumSumVisible() Uma I-TypeName(Ukhetho) <> "Ububanzi" Bese Uphuma Ku-Sub Nge-GetObject("Okusha:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.Vil Cell)Izinhlobo I-PutInClipboard End With End Sub Kulesi simo, ukubalwa kwanoma yimuphi umsebenzi ophelele kuzosetshenziswa kumaseli abonakalayo kuphela.
Uma udinga ifomula ephilayo
Uma uphupha, ungaqhamuka nezimo lapho kungcono ukukopisha hhayi inombolo (eqhubekayo), kodwa ifomula ephilayo ku-buffer, ebala amanani esiwadingayo kumaseli akhethiwe. Kulokhu, kuzodingeka unamathisele ifomula ezicucu, wengeze kukho ukukhishwa kwezimpawu zamadola bese ufaka ukhefana (osetshenziswa njengesihlukanisi phakathi kwamakheli ezigaba ezimbalwa ezikhethiwe ku-VBA) nge-semicolon:
I-SumSumFormula() Uma I-TypeName(Ukukhetha) <> "Ububanzi" Bese Uphuma Ku-Sub Nge-GetObject("Okusha:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Faka esikhundleni(Buyisela(Ukhetho. Ikheli, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub Isifinyezo esinemibandela eyengeziwe
Futhi, ekugcineni, kuma-maniacs ngokuphelele, ungabhala i-macro engeke ihlanganise wonke amaseli akhethiwe, kodwa kuphela lawo anelisa imibandela enikeziwe. Ngakho-ke, ngokwesibonelo, i-macro izobukeka sengathi ibeka isamba samaseli akhethiwe ku-Buffer, uma amanani awo amakhulu kuno-5 futhi ngesikhathi esifanayo agcwele noma yimuphi umbala:
I-Sub CustomCalc() Fiphalisa Ububanzi Bami Njengobubanzi Uma I-TypeName(Ukukhetha) <> "Ububanzi" Bese Uphuma Ngaphansi Kweseli Ngalinye Elikhethiwe Uma iseli.Value > 5 kanye neseli.Interior.ColorIndex <> xlNone Bese Uma URange wami Lulutho Bese Setha uRange wami = iseli Else Setha i-myRange = Inyunyana(uRange wami, iseli) Qeda Uma Iphela Uma Iseli Elandelayo Nge-GetObject("Okusha:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .End With SubClipboard Njengoba ungase ucabange kalula, izimo zingasethwa ngokuphelele - kuze kufike kumafomethi weseli - nanoma yiliphi inani (okuhlanganisa ngokuwaxhumanisa nama-opharetha anengqondo noma noma kanye). Kukhona indawo enkulu yokucabanga.
- Guqula amafomula abe amanani (izindlela ezi-6)
- Ayini ama-macros, angawasebenzisa kanjani, lapho ufaka khona ikhodi ye-Visual Basic
- Ulwazi oluwusizo kubha yesimo ye-Microsoft Excel