Kopa huwandu hwemasero akasarudzwa kuClipboard

Dzimwe nguva zvinotora nguva yakareba kuti uuye nezvimwe zvinhu. Asi kana ZVINO ZVIRI kugadzirwa, mushure mezvo zvinoita sezviri pachena uye kunyange banal. Kubva pane zvakatevedzana "chii, zvaigoneka?".

Kubva pashanduro dzekutanga chaidzo, bhara repazasi peMicrosoft Excel hwindo rinogara richiratidza huwandu hwemasero akasarudzwa:

Kopa huwandu hwemasero akasarudzwa kuClipboard

Kana uchida, zvaitokwanisika kudzvanya-kurudyi pane izvi zvabuda uye wosarudza kubva mumenu yemamiriro ezvinhu chaizvo kuti ndeapi mabasa atinoda kuona:

Kopa huwandu hwemasero akasarudzwa kuClipboard

Uye nguva pfupi yadarika, mune dzichangoburwa dzeExcel, Vagadziri veMicrosoft vakawedzera chinhu chiri nyore asi chine hungwaru - ikozvino kana iwe ukadzvanya pane izvi zvabuda, zvinokopwa kuclipboard!

Kopa huwandu hwemasero akasarudzwa kuClipboard

Runako. 

Asi zvakadini nevaya vasati (kana kuti vatove?) vane vhezheni yakadaro yeExcel? Apa ndipo panogona kubatsira macros akareruka.

Kukopa huwandu hwemasero akasarudzwa kuClipboard uchishandisa macro

Vhura mu tab yokuvaka (Mugadziri) mupepeti Visual Basic kana kushandisa iyi nzira yekudimbudzira kiibhodhi alt+F11. Isa nyowani isina chinhu module kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:

Sub SumSelected() Kana TypeName(Sarudzo) <> "Range" Wobuda Muchikamu NeGetObject("Itsva:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection With Sublipboard) .PutIn Sublipboard .

Zvinonzwisisika zviri nyore:

  • Kutanga kunouya "dziviriro kubva kubenzi" - isu tinotarisa kuti chii chaizvo chakasimbiswa. Kana asiri masero akasarudzwa (asi, semuenzaniso, chati), wobva wabuda iyo macro.
  • Zvadaro kushandisa murairo Getobject tinogadzira chinhu chitsva che data apo huwandu hwedu hwemasero akasarudzwa huchachengetwa gare gare. Iyo yakareba uye isinganzwisisike alphanumeric kodhi, chokwadi, chinongedzo kuWindows registry bazi uko raibhurari iripo. Microsoft Mafomu 2.0 Chinhu Raibhurari, izvo zvinogona kugadzira zvinhu zvakadaro. Dzimwe nguva hunyengeri uhwu hunodanwawo kusungirirwa kwenguva refu. Kana iwe ukasaishandisa, saka iwe unofanirwa kuita chinongedzo kuraibhurari iyi mufaira kuburikidza nemenyu Zvishandiso - References.
  • Huwandu hwemasero akasarudzwa hunotorwa sekuraira WorksheetFunction.Sm(Sarudzo), uyezve huwandu hunobuda hunoiswa pane clipboard nemurairo PutInClipboard

Kuti zvive nyore kushandisa, iwe unogona, hongu, kugovera iyi macro kune nzira yekudimbudzira keyboard uchishandisa bhatani macro Tab yokuvaka (Mugadziri - Macros).

Uye kana iwe uchida kuona kuti chii chaizvo chakakopwa mushure mekumhanyisa macro, unogona kubatidza Clipboard panel uchishandisa museve mudiki pakona yekurudyi yeboka rinoenderana. huru (Musha) tab:

Kopa huwandu hwemasero akasarudzwa kuClipboard

Kwete chete mari

Kana, kuwedzera kune banal mari, iwe uchida chimwe chinhu, saka iwe unogona kushandisa chero emabasa ayo chinhu chinotipa. WorksheetFunction:

Kopa huwandu hwemasero akasarudzwa kuClipboard

Somuenzaniso, pane:

  • Sum – sum
  • Avhareji - arithmetic zvinoreva
  • Count - nhamba yemasero ane nhamba
  • CountA - nhamba yemasero akazadzwa
  • CountBlank - nhamba yemasero asina chinhu
  • Min - shoma kukosha
  • Max - kukosha kukuru
  • Median - yepakati (pakati kukosha)
  • … Etc.

Kusanganisira mafirita uye akavanzika mitsara-makoramu

Ko kana mitsetse kana makoramu akavanzwa (nemaoko kana nesefa) muchikamu chakasarudzwa? Kuti tirege kuvaisa munhoroondo muhuwandu, isu tichada kushandura zvishoma kodhi yedu nekuwedzera kune chinhu. sarudzo pfuma SpecialCells(xlCellTypeVisible):

Sub SumVisible() Kana TypeName(Sarudzo) <> "Range" Wobva wabuda Sub Uine GetObject("Itsva:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.Vii Cell)Marudzi(Selection.SpecialCell) PutInClipboard End Nekupera Sub

Muchiitiko ichi, kuverenga kwechero basa rose richashandiswa kune masero anooneka chete.

Kana uchida formula mhenyu

Kana iwe ukarota kumusoro, iwe unogona kuuya nemamiriro ezvinhu kana zviri nani kukopa kwete nhamba (nguva dzose), asi fomu mhenyu mubhafa, iyo inoverengera huwandu hwatinoda kumaseru akasarudzwa. Muchiitiko ichi, iwe uchafanirwa kunama fomura kubva muzvidimbu, uchiwedzera kwairi kubviswa kwezviratidzo zvemadhora uye kutsiva koma (iyo inoshandiswa sekupatsanura pakati pekero dzezvikamu zvakasarudzwa zvakasarudzwa muVBA) ine semicolon:

Sub SumFormula() Kana TypeName(Sarudzo) <> "Range" Wobuda Muchikamu NeGetObject("Itsva:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Tsiva(Tsiva(Sarudzo. Kero, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub

Pfupiso ine mamwe mamiriro

Uye, pakupedzisira, kune maniacs akazara, unogona kunyora macro iyo inopfupisa kwete ese akasarudzwa maseru, asi iwo chete anogutsa mamiriro akapihwa. Saka, semuenzaniso, macro ichaita senge iyo inoisa huwandu hwemasero akasarudzwa muBuffer, kana hunhu hwavo hwakakura kupfuura 5 uye panguva imwe chete ivo vakazadzwa nechero ruvara:

 Sub CustomCalc() Dim myRange SeRange Kana TypeName(Sarudzo) <> "Range" Wobva Wabuda Sub Kune Yesero Sero Irikusarudzwa Kana cell.Value > 5 Uye cell.Interior.ColorIndex <> xlNone Zvino Kana myRange Isisiri Chinhu Woseta myRange = cell Else Set myRange = Union(myRange, cell) End Kana Ikazopera Kana Next cell With GetObject("Itsva:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .EndIn

Sezvaunogona kufungidzira zviri nyore, mamiriro anogona kusetwa zvachose chero - kusvika kumasero mafomati - uye mune chero huwandu (kusanganisira nekuvabatanidza pamwe chete neane musoro vanoshanda kana kana uye). Pane nzvimbo yakawanda yekufungidzira.

  • Shandura mafomula kuita maitiro (6 nzira)
  • Chii macros, maitiro ekuashandisa, kupi kwekuisa Visual Basic kodhi
  • Ruzivo runobatsira mune iyo mamiriro bar yeMicrosoft Excel

Leave a Reply