Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Nthawi zina zimatenga nthawi yayitali kuti mupange zinthu zina. Koma akapangidwa KALE, pambuyo pake amawoneka omveka komanso oletsa. Kuchokera pamutu wakuti "chiyani, zinali zotheka?".

Kuchokera pamatembenuzidwe oyambirira, malo omwe ali pansi pa zenera la Microsoft Excel nthawi zambiri amawonetsa ma cell osankhidwa:

Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Ngati mungafune, zinali zotheka kudina kumanja pazotsatirazi ndikusankha kuchokera pazosankha zomwe tikufuna kuwona:

Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Ndipo posachedwapa, muzosintha zaposachedwa za Excel, opanga Microsoft adawonjezera chinthu chosavuta koma chanzeru - tsopano mukadina pazotsatira izi, zimakopera pa bolodi!

Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Kukongola. 

Koma bwanji za iwo omwe alibe (kapena kale?) kukhala ndi mtundu wotere wa Excel? Apa ndipamene ma macros osavuta angathandize.

Kutengera kuchuluka kwa maselo osankhidwa ku Clipboard pogwiritsa ntchito macro

Tsegulani mu tabu Woyambitsa (Wolemba Mapulogalamu) mkonzi Zooneka Basic kapena gwiritsani ntchito njira yachidule ya kiyibodi alt+F11. Lowetsani gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module ndi kukopera khodi ili:

Sub SumSelected() Ngati TypeName(Selection) <> "Range" Kenako Tulukani Sub Ndi GetObject("Chatsopano:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection Sublipboard) EndIn Sublipboard .

Malingaliro ake ndi osavuta:

  • Choyamba pamabwera "chitetezo kwa chitsiru" - timayang'ana zomwe zasonyezedwa. Ngati ma cell sanasankhidwe (koma, mwachitsanzo, tchati), ndiye tulukani macro.
  • Kenako kugwiritsa ntchito lamulo Getobject timapanga chinthu chatsopano cha data pomwe kuchuluka kwathu kwa maselo osankhidwa kudzasungidwa pambuyo pake. Khodi ya zilembo zazitali komanso zosamvetsetseka ndi ulalo wopita ku nthambi yolembetsa ya Windows komwe laibulale ili. Microsoft Forms 2.0 Object Library, zomwe zingathe kupanga zinthu zoterezi. Nthawi zina chinyengo ichi chimatchedwanso kumangika mochedwa. Ngati simugwiritsa ntchito, ndiye kuti muyenera kupanga ulalo wa library iyi mufayilo kudzera pa menyu Zida - Maumboni.
  • Kuchuluka kwa maselo osankhidwa kumatengedwa ngati lamulo WorksheetFunction.Sm(Kusankha), ndiyeno kuchuluka kwake kumayikidwa pa clipboard ndi lamulo PutInClipboard

Kuti mugwiritse ntchito mosavuta, mutha kugawa macro iyi pachidule cha kiyibodi pogwiritsa ntchito batani Macros tsamba Woyambitsa (Wopanga - Macros).

Ndipo ngati mukufuna kuwona zomwe zidakopedwa mutatha kuyendetsa macro, mutha kuyatsa gulu la Clipboard pogwiritsa ntchito muvi wawung'ono kumunsi kumanja kwa gulu lofananira. waukulu (Kunyumba) tabu:

Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Osati ndalama zokha

Ngati, kuwonjezera pa kuchuluka kwa banal, mukufuna chinthu china, ndiye kuti mutha kugwiritsa ntchito chilichonse chomwe chinthucho chimatipatsa. WorksheetFunction:

Lembani kuchuluka kwa maselo osankhidwa ku Clipboard

Mwachitsanzo, pali:

  • Sumu - ndalama
  • Avereji - amatanthauza masamu
  • Kuwerengera - chiwerengero cha maselo okhala ndi manambala
  • CountA - chiwerengero cha maselo odzazidwa
  • CountBlank - chiwerengero cha maselo opanda kanthu
  • Min - mtengo wocheperako
  • Max - mtengo wapamwamba
  • Median - wapakatikati (mtengo wapakati)
  • … Ndi zina.

Kuphatikizapo zosefera ndi mizere yobisika

Bwanji ngati mizere kapena mizati yabisika (pamanja kapena ndi fyuluta) mumndandanda wosankhidwa? Kuti tisawaganizire pazowerengera, tidzafunika kusintha pang'ono code yathu powonjezera pa chinthucho. kusankha katundu SpecialCell(xlCellTypeVisible):

Sub SumVisible() Ngati TypeName(Selection) <> "Range" Kenako Tulukani Sub Ndi GetObject("Chatsopano:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.Viscal Cellpells)Type PutInClipboard End Ndi End Sub

Pankhaniyi, kuwerengera kwa ntchito yonse yonse kudzagwiritsidwa ntchito pamaselo owoneka okha.

Ngati mukufuna njira yamoyo

Ngati mumalota, mutha kubwera ndi zochitika pamene kuli bwino kuti musakopere nambala (nthawi zonse), koma fomula yamoyo mu buffer, yomwe imawerengera ziwerengero zomwe timafunikira pamaselo osankhidwa. Pachifukwa ichi, muyenera kumata chilinganizocho kuchokera pazidutswa, ndikuwonjezerapo kuchotsa zizindikiro za dola ndikusintha koma (yomwe imagwiritsidwa ntchito ngati cholekanitsa pakati pa maadiresi amitundu ingapo yosankhidwa mu VBA) ndi semicolon:

SubSumFormula() Ngati TypeName(Kusankha) <> "Range" Kenako Tulukani Sub Ndi GetObject("Chatsopano:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Bwezerani(Bwezerani(Kusankha. Adilesi, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub

Chidule ndi zina zowonjezera

Ndipo, potsiriza, kwa maniacs kwathunthu, mutha kulemba macro omwe angaphatikize osati ma cell onse osankhidwa, koma okhawo omwe amakwaniritsa zomwe mwapatsidwa. Chifukwa chake, mwachitsanzo, macro adzawoneka ngati omwe amayika kuchuluka kwa maselo osankhidwa mu Buffer, ngati zikhalidwe zawo ndi zazikulu kuposa 5 ndipo nthawi yomweyo amadzazidwa ndi mtundu uliwonse:

 Sub CustomCalc() Dim myRange Monga Range Ngati TypeName(Selection) <> "Range" Kenako Tulukani Sub Pa selo Lililonse Mukusankha Ngati cell.Value > 5 Ndi cell.Interior.ColorIndex <> xlNone Ndiye Ngati myRange Sikanthu Ndiye Ikani myRange = cell Else Set myRange = Union(myRange, cell) End If End Cell Next With GetObject("Chatsopano:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .EndInSubClipboard

Monga momwe mungaganizire mosavuta, mikhalidwe imatha kukhazikitsidwa mwamtheradi - mpaka mawonekedwe a cell - komanso kuchuluka kulikonse (kuphatikiza ndi kuwalumikiza ndi ogwiritsa ntchito momveka bwino kapena kapena ndi). Pali malo ambiri olingalira.

  • Sinthani ma formula kukhala ma values ​​(6 njira)
  • Kodi macros ndi chiyani, momwe mungagwiritsire ntchito, momwe mungayikitsire Visual Basic code
  • Zambiri zothandiza mu bar ya Microsoft Excel

Siyani Mumakonda