Kopējiet ātro statistikas vērtību starpliktuvē - Excel padomi

Satura rādītājs

Kāds ir cilnes ASCII kods?

Jautājums radās Excel seminārā Tampā: vai nebūtu forši, ja jūs varētu kopēt statistiku no statusa joslas uz starpliktuvi vēlākai ielīmēšanai diapazonā?

Es piespiedu personu, kas uzdeva jautājumu, kā tieši pastai vajadzētu darboties. Protams, jūs nevarat nekavējoties ielīmēt statistiku, jo jums ir atlasīta virkne svarīgu šūnu. Jums būs jāgaida, atlasiet citu tukšu izklājlapas diapazonu, ielīmējiet (kā Ctrl + V), un statistika parādīsies 6 rindu pa 2 kolonnām diapazonā. Persona, kas uzdeva jautājumu, ieteica, ka tās būtu statiskas vērtības.

Semināra laikā es nemēģināju atbildēt uz jautājumu, jo zināju, ka varētu būt mazliet grūts to izvilkt.

Bet es nesen sāku makro, lai redzētu, vai to varētu izdarīt. Mana ideja bija izveidot garu teksta virkni, kuru varētu ielīmēt. Lai piespiestu vienumus parādīties divās kolonnās, teksta virknei ir jābūt 1. slejas etiķetei (Summa) un pēc tam tabulēšanai, kā arī 2. slejas vērtībai. Pēc tam jums būs nepieciešama karietes atgriešana, etiķete 2. rinda, 1. sleja, pēc tam vēl viena cilne, vērtība utt.

Es zināju, ka Application.WorksheetFunction ir lielisks veids, kā atgriezt Excel funkciju rezultātus VBA, taču tas neatbalsta visas 400+ Excel funkcijas. Dažreiz, ja VBA jau ir līdzīga funkcija (LEFT, RIGHT, MID), tad Application.WorksheetFunction neatbalstīs šo funkciju. Es aktivizēju VBA ar Alt + F11, parādīju tūlītējo rūti ar Ctrl + G un pēc tam ierakstīju dažas komandas, lai pārliecinātos, vai tiek atbalstītas visas sešas statusa joslas funkcijas. Par laimi visas sešas atgrieztās vērtības, kas atbilda statusu joslā parādītajam.

Lai padarītu makro īsāku, mainīgajam varat piešķirt Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Tad vēlāk makro jūs varat vienkārši atsaukties uz WF.Sum (Selection), nevis ierakstīt Application.WorksheetFunction atkārtoti.

Kāds ir cilnes ASCII kods?

Es sāku veidot teksta virkni. Es izvēlējos MS mainīgo mainīgo MyString.

MS = "Sum:" &

Šis ir brīdis, kad man vajadzēja cilnes rakstzīmi. Esmu pietiekami geeks, lai zinātu dažas ASCII rakstzīmes (10 = LineFeed, 13 = Carriage Return, 32 = atstarpe, 65 = A, 90 = Z), taču nevarēju atcerēties cilni. Kad es grasījos doties uz Bingu, lai to uzmeklētu, es atcerējos, ka jūs varat izmantot vblf savā kodā līnijas padevei vai vbcr savā kodā, lai atgrieztos pret karieti, tāpēc es ierakstīju vbtab ar mazajiem burtiem. Pēc tam es pārcēlos uz jaunu rindu, lai ļautu Excel VBA kapitalizēt vārdus, kurus tā saprata. Es cerēju redzēt, kā vbtab uzņem kapitālu, un, protams, līnija kļuva ar lielo burtu, norādot, ka VBA man piešķirs cilnes rakstzīmi.

Ja ierakstīsit VBA mazajiem burtiem, pārejot uz jaunu rindu, jūs redzēsiet, kā visi pareizi uzrakstītie vārdi kaut kur paņem lielo burtu. Zemāk redzamajā attēlā vblf, vbcr, vbtab ir zināmi vba, un, pārejot uz jaunu līniju, tie tiek kapitalizēti. Tomēr lieta, ko es izdomāju, vbampersand, VBA nav zināma lieta, tāpēc tā netiek kapitalizēta.

Šajā brīdī bija jāapvieno 6 etiķetes un 6 vērtības vienā garā virknē. Atcerieties zemāk esošajā kodā, ka _ katras rindas beigās nozīmē, ka koda rindiņa tiek turpināta nākamajā rindā.

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ēc visu etiķešu un vērtību apvienošanas es gribēju apbrīnot savu darbu, tāpēc rezultātu parādīju MsgBox. Es palaistu kodu, un tas darbojās lieliski:

Es domāju, ka esmu mājās bez maksas. Ja es varētu vienkārši ievietot MS starpliktuvē, es varētu sākt ierakstīt Podcast 1894. Varbūt MS.Copy darītu šo triku?

Diemžēl tas nebija tik vienkārši. MS.Copy nebija derīga koda rinda.

Tātad, es devos uz Google un meklēju "Excel VBA Copy Variable to Clipboard". Viens no labākajiem rezultātiem bija šis ziņu dēļa ieraksts. Šajā ierakstā mani vecie draugi Huans Pablo un NateO mēģināja palīdzēt OP. Faktiskais padoms tomēr bija tas, kur Huans Pablo ieteica izmantot kādu kodu no Excel MVP Chip Pearson vietnes. Es atradu šo lapu, kurā paskaidrots, kā mainīgo nokļūt starpliktuvē.

Lai kaut ko pievienotu starpliktuvei, vispirms jādodas uz VBA loga izvēlni Rīki un jāizvēlas Atsauces. Sākotnēji jūs redzēsiet dažas atsauces pēc noklusējuma. Microsoft Forms 2.0 bibliotēka netiks pārbaudīta. Jums tas jāatrod ļoti garajā sarakstā un jāpievieno. Par laimi, man tas bija pirmajā izvēles lappusē par to, kur zaļā bulta to rāda. Kad esat pievienojis atzīmi blakus atsaucei, tā pāriet uz augšu.

Mikroshēmas kods nedarbosies, ja nepievienosiet atsauci, tāpēc neizlaidiet iepriekš minēto darbību!

Kad esat pievienojis atsauci, pabeidziet makro, izmantojot Chip kodu:

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

Pirms apraides ierakstīšanas es veicu pārbaudi, lai pārliecinātos, ka tā darbojas. Protams, kad es palaidu makro, pēc tam izvēlējos jaunu diapazonu un nospiedu Ctrl + V, lai ielīmētu, starpliktuve tika iztukšota 6 rindu x 2 kolonnu diapazonā.

Kūūū! Es sagatavoju epizodei PowerPoint virsraksta karti, ieslēdzu Camtasia Recorder un ierakstīju visu iepriekš minēto. Bet … kad es gatavojos parādīt noslēguma kredītpunktus, mani pārņēma nepatīkama sajūta. Šis makro statistiku ielīmēja kā statiskas vērtības. Ko darīt, ja mainās pamatā esošie dati? Vai jūs nevēlaties, lai ielīmētais bloks tiktu atjaunināts? Podcast epizodē bija ilga pauze, kur es apsvēru, ko darīt. Visbeidzot, es noklikšķināju uz ikonas Camtasia Pause Recording un devos pārbaudīt, vai es varu ievietot formulu MS virknē un vai tā tiks pareizi ielīmēta. Protams, tā arī notika. Es pat nepabeidzu makro pilnībā vai veicu vairākus testus, kad atkal ieslēdzu reģistratoru un runāju par šo makro. Podcast epizodē es teicu, ka tas nekad nedarbosies blakus esošās atlasēs, bet vēlāk testējot, tas darbojas.Šeit ir makro, kas jāielīmē kā formulas:

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ēc videoklipa ievietošanas parastais skatītājs Maiks Fliss jautāja, vai ir veids, kā izveidot formulas, kuras pastāvīgi atjauninātu, lai parādītu statistiku par jebkuru atlasīto diapazonu. Tam būtu nepieciešams makets Worksheet_SelectionChange, kas pastāvīgi atjauninātu nosaukto diapazonu, lai tas atbilstu izvēlei. Lai gan tas ir atdzist mazliet viltīgs, tas liek makro palaist katru reizi, kad pārvietojat šūnu rādītāju, un tas nepārtraukti notīrīs UnDo kaudzīti. Tātad, ja izmantojat šo makro, tas ir jāpievieno visām darblapas koda rūtīm, kur vēlaties, lai tas darbotos, un šajās darblapās būs jādzīvo bez Atsaukt.

Pirmkārt, programmā Excel ar peles labo pogu noklikšķiniet uz cilnes lapa un izvēlieties Skatīt kodu. Pēc tam ielīmējiet šo kodu.

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

Pārslēdzieties atpakaļ uz programmu Excel. Atlasiet jaunu šūnu un ierakstiet formulu =SUM(SelectedData). Sākotnēji jūs saņemsiet apļveida atsauci. Pēc tam atlasiet citu ciparu šūnu diapazonu, un tikko izveidotās formulas kopsumma tiks atjaunināta.