Kopirajte vrednosti hitre statistike v odložišče - Excel Nasveti

Kazalo

Kaj je koda ASCII za zavihek?

Vprašanje se je pojavilo med Excelovim seminarjem v Tampi: Ali ne bi bilo v redu, če bi lahko kopirali statistične podatke iz vrstice stanja v odložišče za poznejše lepljenje v obseg?

Pritisnil sem na osebo, ki je postavila vprašanje, kako natančno mora pasta delovati. Statističnih podatkov seveda ne morete takoj prilepiti, ker imate izbranih kup pomembnih celic. Morali bi počakati, izbrati drug prazen obseg preglednice, prilepiti (kot pri Ctrl + V) in statistika bi se pojavila v obsegu od 6 vrstic do 2 stolpca. Oseba, ki je postavila vprašanje, je predlagala, da gre za statične vrednosti.

Med seminarjem nisem poskušal odgovoriti na vprašanje, ker sem vedel, da bi bilo to nekoliko zapleteno.

Toda pred kratkim sem začel makro, da bi ugotovil, ali je to mogoče storiti. Moja ideja je bila zgraditi dolg besedilni niz, ki bi ga lahko prilepili. Če želite, da se elementi pojavijo v dveh stolpcih, bi moral imeti besedilni niz oznako za stolpec 1 (Vsota) in nato zavihek ter vrednost za stolpec 2. Nato bi potrebovali vrnitev nosilca, oznako za vrstica 2, stolpec 1, nato še en zavihek, vrednost itd.

Vedela sem, da je Application.WorksheetFunction odličen način za vrnitev rezultatov Excelovih funkcij v VBA, vendar ne podpira vseh 400+ Excelovih funkcij. Če ima VBA že podobno funkcijo (LEVO, DESNO, MID), potem Application.WorksheetFunction te funkcije ne bo podpiral. Vžgal sem VBA z Alt + F11, prikazal takojšnje podokno s Ctrl + G in nato vnesel nekaj ukazov, da se prepričam, da je podprtih vseh šest funkcij vrstice stanja. Na srečo je vseh šest vrnilo vrednosti, ki so se ujemale s prikazom v vrstici stanja.

Če želite makro skrajšati, lahko spremenljivki dodelite Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Nato se lahko pozneje v makru preprosto sklicujete na WF.Sum (izbor), namesto da vedno znova vtipkate Application.WorksheetFunction.

Kaj je koda ASCII za zavihek?

Začel sem graditi besedilni niz. Za MyString sem izbral spremenljivko MS.

MS = "Sum:" &

Tu sem potreboval znak zavihka. Dovolj sem geeky, da poznam nekaj znakov ASCII (10 = LineFeed, 13 = Vrnitev nosilca, 32 = presledek, 65 = A, 90 = Z), vendar se zavihka nisem mogel spomniti. Ko sem se hotel odpraviti v Bing, da bi jo poiskal, sem se spomnil, da lahko v svoji kodi za vrnitev kočije uporabite vblf ali vbcr v svoji kodi, zato sem vtipkal vbtab z malimi črkami. Nato sem se preselil v novo vrstico, da omogočim Excelu VBA uporabo velikih začetnic z besedami, ki jih je razumel. Upal sem, da bo vbtab pobral kapital in zagotovo se je vrstica začela uporabljati z velikimi črkami, kar pomeni, da mi bo VBA namenil znak zavihka.

Če vnesete VBA z malimi črkami, boste ob prehodu na novo vrstico videli, da vse pravilno črkovane besede nekje v besedi poberejo veliko začetnico. Na spodnji sliki je vblf, vbcr, vbtab znano, da vba in se po selitvi v novo vrstico zapišejo z velikimi črkami. Vendar stvar, ki sem si jo izmislil, vbampersand VBA ni znana, zato se ne uporablja z velikimi črkami.

V tem trenutku je šlo za združitev 6 oznak in 6 vrednosti v en dolg niz. V spodnji kodi ne pozabite, da _ na koncu vsake vrstice pomeni, da se vrstica kode nadaljuje v naslednji vrstici.

Sub CopyQuickStatsToClipboard1() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr MsgBox MS End Sub

Po združitvi vseh oznak in vrednot sem želel občudovati svoje delo, zato sem rezultat prikazal v MsgBoxu. Zagnal sem kodo in je delovala čudovito:

Mislil sem, da sem brez domov. Če bi lahko MS samo spravil v odložišče, bi lahko začel snemati Podcast 1894. Mogoče bi MS.Copy naredil trik?

Na žalost ni bilo tako enostavno. MS.Copy ni bila veljavna vrstica kode.

Torej, odšel sem do Googla in iskal »Spremenljivka za kopiranje v VBA v Excel«. Eden najboljših rezultatov je bila ta objava na forumu za sporočila. V tej objavi sta moja stara prijatelja Juan Pablo in NateO poskušala pomagati OP. Dejanski namig pa je bil, kjer je Juan Pablo predlagal, da uporabi nekaj kode s spletnega mesta Excel MVP Chip Pearson. Našel sem to stran, ki je razložila, kako spremenljivko spraviti v odložišče.

Če želite nekaj dodati v odložišče, morate najprej odpreti meni Orodja v oknu VBA in izbrati Reference. Na začetku boste videli nekaj referenc, ki so privzeto preverjene. Knjižnica Microsoft Forms 2.0 ne bo preverjena. Najti ga morate na zelo dolgem seznamu in dodati. Na mojo srečo je bilo na prvi strani izbire, približno tam, kjer jo prikazuje zelena puščica. Ko dodate referenčno oznako poleg sklica, se premakne na vrh.

Koda čipa ne bo delovala, če ne dodate reference, zato ne preskočite zgornjega koraka!

Ko dodate referenco, dokončajte makro z uporabo čipove kode:

Sub CopyQuickStatsToClipboard() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Pred snemanjem podcasta sem naredil test, da se prepričam, ali deluje. Seveda, ko sem zagnal makro, nato izbral nov obseg in pritisnil Ctrl + V, da sem ga prilepil, je bilo odložišče izpraznjeno v obseg stolpcev 6 vrstic x 2.

Huu-hoo! Za epizodo sem pripravil naslovno kartico PowerPointa, vklopil Camtasia Recorder in posnel vse zgoraj. Toda … ko sem nameraval pokazati zaključne kredite, me je prevzel moteč občutek. Ta makro je lepil statistiko kot statične vrednosti. Kaj če bi se osnovni podatki spremenili? Ali ne želite, da se prilepljeni blok posodobi? V podcastu je bila dolga pavza, kjer sem razmišljal, kaj naj storim. Na koncu sem kliknil ikono Camtasia Pause Recording in si ogledal, ali lahko vstavim formulo v niz MS in ali bo pravilno prilepljena. Seveda je. Ko sem ponovno vklopil snemalnik in se pogovarjal o tem makru, makra nisem niti popolnoma dokončal niti naredil več kot en test. V podcastu sem teoretiziral, da to nikoli ne bo delovalo pri nepretrganih izborih, vendar pri kasnejših testiranjih to deluje.Tu je makro, ki ga lahko prilepite kot formule:

Sub CopyQuickStatsAsFormulas() Set WF = Application.WorksheetFunction MA = Selection.Address MS = "Average: " & vbTab & "=AVERAGE(" & MA & ")" & vbCr _ & "Count: " & vbTab & "=CountA(" & MA & ")" & vbCr _ & "Numerical Count: " & vbTab & "=Count(" & MA & ")" & vbCr _ & "Min: " & vbTab & "=Min(" & MA & ")" & vbCr _ & "Max: " & vbTab & "=Max(" & MA & ")" & vbCr _ & "Sum: " & vbTab & "=Sum(" & MA & ")" & vbCr _ ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Po objavi videoposnetka je redni gledalec Mike Fliss vprašal, ali obstaja način za oblikovanje formul, ki bi se nenehno posodabljale in prikazovale statistične podatke za kateri koli obseg. To bi zahtevalo makro Worksheet_SelectionChange, ki bi nenehno posodabljal imenovani obseg, da se ujema z izbiro. Čeprav je to kul trik, prisili makro, da se zažene vsakič, ko premaknete kazalec celice, kar bo nenehno počistilo sklad UnDo. Torej, če uporabljate ta makro, ga morate dodati v vsako podokno s kodami delovnega lista, kjer želite, da deluje, in na teh delovnih listih boste morali živeti brez razveljavitve.

Najprej v Excelu z desno miškino tipko kliknite zavihek lista in izberite Prikaži kodo. Nato prilepite to kodo.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Selection.Name = "SelectedData" End Sub

Preklopite nazaj na Excel. Izberite novo celico in vnesite formulo =SUM(SelectedData). Sprva boste dobili krožno referenco. Nato izberite drug obseg številskih celic in vsota formule, ki ste jo pravkar ustvarili, se bo posodobila.