E kope i ka huina o nā pūnaewele i koho ʻia i ka Clipboard

I kekahi manawa, lōʻihi ka manawa e hoʻopuka ai i kekahi mau mea. Akā i ka wā i hoʻokumu ʻia ai lākou, ma hope o ka ʻoiaʻiʻo ua ʻike ʻia lākou a ʻike ʻole ʻia. Mai ka moʻolelo "he aha, hiki?".

Mai nā mana mua loa, ua hōʻike maʻamau ka pae kūlana ma lalo o ka puka makani Microsoft Excel i nā huina no nā cell i koho ʻia:

Inā makemake ʻia, hiki nō ke kaomi ʻākau i kēia mau hopena a koho mai ka papa kuhikuhi kikoʻī i nā hana a mākou e makemake ai e ʻike:

A i kēia manawa, i nā mea hou Excel, ua hoʻohui nā mea hoʻomohala Microsoft i kahi hiʻohiʻona maʻalahi akā naʻauao - i kēia manawa ke kaomi ʻana i kēia mau hopena, kope ʻia lākou i ka clipboard!

Nani. 

Akā pehea ka poʻe i loaʻa ʻole (a i ʻole?) ʻO kēia kahi e hiki ai i nā macros maʻalahi ke kōkua.

Ke kope ʻana i ka huina o nā cell i koho ʻia i ka Clipboard me ka macro

Wehe i ka pā mea haku polokalamu (Ka mea hoʻomohala) hoʻoponopono Ke kākoʻo XIX. a i ʻole e hoʻohana i kēia ʻaoʻao pōkole keyboard Alt+F11. E hoʻokomo i ka module hakahaka hou ma ka papa kuhikuhi Hoʻokomo - Module a kope i kēia code ma laila:

Sub SumSelected() Inā TypeName(Selection) <> "Range" A laila e haʻalele i ka Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard  

He mea maʻalahi kona manaʻo:

• ʻO ka mua ka "pale mai ka naʻaupō" - nānā mākou i ka mea i hōʻike ʻia. Inā ʻaʻole koho ʻia nā cell (akā, no ka laʻana, he pakuhi), a laila e haʻalele i ka macro.
• A laila hoʻohana i ke kauoha Getobject hana mākou i kahi mea ʻikepili hou kahi e mālama ʻia ai kā mākou huina o nā cell i koho ʻia ma hope. ʻO kahi code alphanumeric lōʻihi a hiki ʻole ke hoʻomaopopo ʻia, he loulou i ka lālā hoʻopaʻa inoa Windows kahi i loaʻa ai ka waihona. Microsoft Forms 2.0 Object Library, hiki ke hana i ia mau mea. I kekahi manawa ua kapa ʻia kēia hana hoʻopunipuni hoʻopaʻa paʻa hope. Inā ʻaʻole ʻoe e hoʻohana, a laila pono ʻoe e hana i kahi loulou i kēia waihona ma ka faila ma o ka papa kuhikuhi Nā mea hana - Nā kuhikuhi.
• ʻO ka huina o nā pūnaewele i koho ʻia he kauoha Pepa HaʻawinaFunction.Sum(Selection), a laila kau ʻia ka huina hopena ma ka clipboard me ke kauoha PutInClipboard

No ka maʻalahi o ka hoʻohana ʻana, hiki iā ʻoe, ʻoiaʻiʻo, hāʻawi i kēia macro i kahi pōkole keyboard me ka hoʻohana ʻana i ke pihi macros uku pila mea haku polokalamu (Ka mea hoʻomohala — Macros).

A inā makemake ʻoe e ʻike i ka mea i kope pono ʻia ma hope o ka holo ʻana i ka macro, hiki iā ʻoe ke hoʻohuli i ka papa Clipboard me ka hoʻohana ʻana i ka pua liʻiliʻi ma ke kihi ʻākau haʻahaʻa o ka hui pili ma. o ka papa kuhikuhi (Home) papa:

ʻAʻole wale ka nui

Inā makemake ʻoe i kahi mea ʻē aʻe, a laila hiki iā ʻoe ke hoʻohana i kekahi o nā hana i hāʻawi ʻia e ka mea iā mākou. Hana Pepa Hana:

Eia kekahi laʻana, aia:

• huina – huina
• Awelika – helu helu
• Helu – helu o nā pūnaewele me nā helu
• HeluA – ka helu o nā pūnaewele i hoʻopiha ʻia
• CountBlank – ka helu o nā keena hakahaka
• Min - ka waiwai liʻiliʻi
• Max – ka waiwai nui
• Median – median (waiwai waena)
• … Etc.

Me nā kānana a me nā lālani huna

He aha inā hūnā ʻia nā lālani a i ʻole nā ​​kolamu (ma ka lima a i ʻole ke kānana) i ka pae i koho ʻia? I ʻole e noʻonoʻo iā lākou i ka huina, pono mākou e hoʻololi iki i kā mākou code ma ka hoʻohui ʻana i ka mea. koho waiwai Nā Pūnaewele Kūikawā(xlCellTypeVisible):

Sub SumVisible() Inā TypeName(Selection) <> "Range" A laila e ha'alele i Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCellCell)Ty Hoʻopau ʻo PutInClipboard me End Sub  

I kēia hihia, e hoʻohana ʻia ka helu ʻana o nā hana holoʻokoʻa i nā cell ʻike wale ʻia.

Inā makemake ʻoe i kahi ʻano ola

Inā moeʻuhane ʻoe, hiki iā ʻoe ke hana i nā hiʻohiʻona inā ʻoi aku ka maikaʻi o ke kope ʻana ʻaʻole i kahi helu (mau), akā he ʻano ola i loko o ka buffer, e helu ana i nā huina e pono ai mākou no nā cell i koho ʻia. I kēia hihia, pono ʻoe e hoʻopili i ke ʻano mai nā ʻāpana, me ka hoʻohui ʻana i ka wehe ʻana i nā hōʻailona kālā a hoʻololi i ke koma (i hoʻohana ʻia ma ke ʻano he mea hoʻokaʻawale ma waena o nā helu o nā pae i koho ʻia ma VBA) me kahi semicolon:

Sub SumFormula() Inā TypeName(Selection) <> "Range" A laila e haʻalele i ka Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Hoʻololi (Selection). Wahi, ",", ";"), "$", "") & ")".  

ʻO ka hōʻuluʻulu me nā kūlana hou

A ʻo ka hope, no nā maniac piha, hiki iā ʻoe ke kākau i kahi macro e hōʻuluʻulu ʻaʻole i nā cell i koho ʻia, akā ʻo nā mea wale nō e hoʻokō i nā kūlana i hāʻawi ʻia. No laila, no ka laʻana, e like ka macro me ka waiho ʻana i ka huina o nā cell i koho ʻia i loko o ka Buffer, inā ʻoi aku ka nui o kā lākou waiwai ma mua o 5 a i ka manawa like ua hoʻopiha ʻia lākou me nā kala:

 Sub CustomCalc() E ho'ēmi i koʻu Range e like me ka laulā inā TypeName(Selection) <> "Range" A laila e haʻalele i ka Sub no kēlā me kēia cell i ke koho ʻana inā cell.Value > 5 A me cell.Interior.ColorIndex <> xlʻAʻohe a laila inā ʻaʻohe mea koʻuRange A laila hoʻonoho i kaʻuRange = cell E hoʻonoho i koʻuRange = Union(myRange, cell) Hoʻopau Inā Hoʻopau ʻia inā hoʻopau ke kelepona me GetObject("Nā Hou:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End  

E like me kāu e noʻonoʻo maʻalahi ai, hiki ke hoʻonohonoho ʻia nā kūlana i kēlā me kēia - a hiki i nā ʻano kelepona - a i kēlā me kēia nui (me ka hoʻopili ʻana iā lākou me nā mea hoʻohana loiloi a i ʻole a). Nui ka lumi no ka noʻonoʻo.

• E hoʻohuli i nā ʻōkuhi i nā waiwai (6 ala)
• He aha nā macros, pehea e hoʻohana ai iā lākou, kahi e hoʻokomo ai i ke code Visual Basic
• ʻIke pono i ka pae kūlana o Microsoft Excel