Kopeerige kiirstatistika väärtused lõikelauale - Exceli näpunäited

Lang L: none (table-of-contents)

Mis on vahelehe ASCII kood?

Küsimus tuli Tampas Exceli seminari ajal: kas poleks lahe, kui saaksite statistika olekuribalt lõikelauale kopeerida, et hiljem vahemikku kleepida?

Vajutasin inimesele, kes esitas küsimuse, kuidas pasta täpselt peaks toimima. Muidugi ei saa te statistikat kohe kleepida, kuna teil on valitud hulk olulisi lahtrid. Peaksite ootama, valige teine arvutustabeli tühi vahemik, kleepige (nagu Ctrl + V-s) ja statistika kuvatakse kuue rea kaupa kahe veeru kaupa. Küsimuse esitanud isik soovitas, et need oleksid staatilised väärtused.

Ma ei püüdnud seminari ajal küsimusele vastata, sest teadsin, et selle välja tõmbamine võib olla natuke keeruline.

Kuid hiljuti käivitasin makro, et näha, kas seda saab teha. Minu idee oli ehitada pikk tekstistring, mida saaks kleepida. Üksuste kahes veerus ilmumise sundimiseks peab tekstistringil olema veeru 1 silt (Summa) ja seejärel tabulaator ning veeru 2 väärtus. Seejärel vajate veo tagastust, et rida 2, veerg 1, seejärel veel üks vaheleht, väärtus jne.

Teadsin, et Application.WorksheetFunction on suurepärane viis Exceli funktsioonide tulemuste tagastamiseks VBA-le, kuid see ei toeta kõiki enam kui 400 Exceli funktsioone. Mõnikord, kui VBA-l on juba sarnane funktsioon (LEFT, RIGHT, MID), siis Application.WorksheetFunction ei toeta seda funktsiooni. Käivitasin VBA-ga Alt + F11, kuvasin vahetu paani klahvikombinatsiooniga Ctrl + G ja tippisin seejärel mõned käsud, et veenduda, et kõiki kuut olekuriba funktsiooni toetatakse. Õnneks tagastasid kõik kuus väärtust, mis vastasid olekuribal kuvatavale.

Makro lühemaks muutmiseks saate määrata muutujale Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Seejärel saate makros hiljem viidata lihtsalt rakendusele WP.Sum (Valik), selle asemel et tippida välja Application.WorksheetFunction.

Mis on vahelehe ASCII kood?

Hakkasin tekstistringi ehitama. Valisin MyStringi jaoks muutuja MS.

MS = "Sum:" &

See on punkt, kus vajasin vahelehe märki. Olen piisavalt geek, et teada mõnda ASCII tähemärki (10 = LineFeed, 13 = Carriage Return, 32 = tühik, 65 = A, 90 = Z), kuid ma ei suutnud vahelehte meelde jätta. Kui kavatsesin selle otsimiseks Bingi poole pöörduda, meenus mulle, et võite kasutada koodis vblf reavahetuse jaoks või vbcr-d oma koodis käru tagasisaatmiseks, nii et kirjutasin vbtab väiketähtedega. Seejärel liikusin uuele reale, et Exceli VBA saaks mõistetud sõnad suurtähtedega kirjutada. Lootsin näha, kuidas vbtab tõstab suurt kapitali, ja tõepoolest, rida sai suurtähega tähistatud, mis näitab, et VBA annab mulle vahelehe märgi.

Kui sisestate VBA väiketähtedega, näete uuele reale minnes, kuidas kõik õigesti kirjutatud sõnad tõstavad kusagil sõnas suurt tähte. Alloleval pildil on vblf, vbcr, vbtab vba teada ja suurtähed kirjutatakse pärast uuele reale liikumist. Kuid see asi, mille ma välja mõtlesin, vbampersand, pole VBA-le teadaolev asi, nii et see ei lähe suurtähtede alla.

Siinkohal oli tegemist 6 sildi ja 6 väärtuse ühendamisega ühte pika stringi. Pidage allolevas koodis meeles, et iga rea lõpus olev _ tähendab, et koodirida jätkatakse järgmisel real.

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

Pärast kõigi siltide ja väärtuste ühendamist tahtsin oma tööd imetleda, nii et kuvasin tulemuse MsgBoxis. Jooksin koodi ja see töötas suurepäraselt:

Arvasin, et olen vaba kodus. Kui ma saaksin lihtsalt MS lõikelauale saada, võiksin alustada Podcast 1894. salvestamist. Võib-olla teeks MS.Copy selle triki?

Paraku polnud see nii lihtne. MS.Copy ei olnud kehtiv koodirida.

Niisiis, läksin Google'i ja otsisin "Excel VBA Copy Variable to Clipboard". Üks tipptulemusi oli see teadetetahvli postitus. Selles postituses püüdsid mu vanad sõbrad Juan Pablo ja NateO OP-d aidata. Tegelik näpunäide oli aga see, kus Juan Pablo soovitas kasutada mõnda koodi Exceli MVP Chip Pearsoni saidilt. Leidsin selle lehe, kus selgitati, kuidas muutuja lõikelauale saada.

Midagi lõikelauale lisamiseks peate esmalt minema VBA akna menüüsse Tööriistad ja valima Viited. Esialgu näete mõnda viidet vaikimisi kontrollituna. Microsofti vormide 2.0 teeki ei kontrollita. Peate selle leidma väga pikast loendist ja lisama. Õnneks oli see minu jaoks valikute esimesel lehel, umbes sellest, kus roheline nool seda näitab. Kui lisate viite juurde linnukese, liigub see üles.

Kiibi kood ei tööta, kui te viitet ei lisa, seega ärge jätke ülaltoodud sammu vahele!

Kui lisate viite, lõpetage makro Chipi koodi abil:

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

Enne taskuhäälingu salvestamist tegin testi, et veenduda, kas see töötab. Muidugi, kui jooksin makro, valisin siis uue vahemiku ja vajutasin kleepimiseks klahvikombinatsiooni Ctrl + V, tühjendati lõikepuhver 6 rea x 2 veeru vahemikku.

Ohoo! Valmistasin episoodi jaoks ette PowerPointi tiitlikaardi, lülitasin sisse Camtasia Recorder ja salvestasin kõik ülaltoodud. Aga … kui ma näitasin lõputiite, tuli mind näriv tunne. See makro kleepis statistikat staatiliste väärtustena. Mis siis, kui alusandmed muutuksid? Kas te ei tahaks, et kleebitud plokki värskendataks? Podcastis oli pikem paus, kus kaalusin, mida teha. Lõpuks klõpsasin ikooni Camtasia Pause Recording ja vaatasin, kas ma saaksin valemi MS-i stringi sisse panna ja kas see kleebitakse õigesti. Päris nii, et läks. Ma ei lõpetanud makrot isegi täielikult ega teinud rohkem kui ühte testi, kui ma maki uuesti sisse lülitasin ja sellest makrost rääkisin. Podcastis teoreetisin, et see ei toimi mitte kunagi külgnevate valikute korral, kuid hilisemas testimises see siiski töötab.Valemitena kleepitav makro:

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

Pärast video postitamist küsis tavavaataja Mike Fliss, kas on võimalus luua valemeid, mida pidevalt värskendatakse, et kuvada valitud vahemiku statistika. Selleks oleks vaja makrot Worksheet_SelectionChange, mis värskendaks pidevalt valitud vahemikku, et see vastaks valikule. Kuigi see on lahe trikk, sunnib see makrot jooksma iga kord, kui liigutate lahtrikursorit ja see puhastab UnDo-virna pidevalt. Seega, kui kasutate seda makrot, tuleb see lisada igale töölehe koodipaanile, kus soovite, et see töötaks, ja peate nendel töölehtedel elama ilma tagasivõtmiseta.

Kõigepealt paremklõpsake Excelis Exceli lehel vahekaarti ja valige Kuva kood. Seejärel kleepige see kood sisse.

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

Lülitage tagasi Exceli juurde. Valige uus lahter ja tippige valem =SUM(SelectedData). Esialgu saate ümmarguse viite. Seejärel valige teine arv lahtrite vahemikku ja värskendatakse äsja loodud valemi koguarvu.