Копирайте стойностите на бързата статистика в клипборда - Съвети на Excel

Съдържание

Какъв е ASCII кодът за раздел?

Въпросът възникна по време на семинар на Excel в Тампа: Не би ли било страхотно, ако можете да копирате статистическите данни от лентата на състоянието в клипборда за по-късно поставяне в диапазон?

Натиснах човека, който зададе въпроса как точно трябва да работи пастата. Разбира се, не можете да поставите статистиката веднага, защото сте избрали куп важни клетки. Ще трябва да изчакате, да изберете друг празен диапазон на електронната таблица, пастата (както при Ctrl + V) и статистическите данни ще се появят в диапазон от 6 реда от 2 колони. Човекът, който е задал въпроса, предполага, че това са статични стойности.

Не се опитах да отговоря на въпроса по време на семинара, защото знаех, че може да е малко сложно да се направи това.

Но наскоро стартирах макрос, за да видя дали това може да се направи. Идеята ми беше да изградя дълъг текстов низ, който да може да се постави. За да принуди елементите да се показват в две колони, текстовият низ трябва да има етикет за колона 1 (Сума) и след това Tab и стойността за колона 2. След това ще се нуждаете от връщане на карета, етикетът за ред 2, колона 1, след това друг раздел, стойността и т.н.

Знаех, че Application.WorksheetFunction е чудесен начин да върнете резултатите от функциите на Excel във VBA, но че не поддържа всички 400+ функции на Excel. Понякога, ако VBA вече има подобна функция (НАЛЯВО, НАДЯСНО, СРЕДНО), тогава Application.WorksheetFunction няма да поддържа тази функция. Задействах VBA с Alt + F11, показах непосредствения панел с Ctrl + G и след това въведох някои команди, за да се уверя, че всички шест функции в лентата на състоянието се поддържат. За щастие и шестте върнати стойности, които съответстват на това, което се появява в лентата на състоянието.

За да направите макроса по-кратък, можете да присвоите Application.WorksheetFunction на променлива:

Set WF = Application.WorksheetFunction

След това, по-късно в макроса, можете просто да се обърнете към WF.Sum (Избор), вместо да въвеждате Application.WorksheetFunction отново и отново.

Какъв е ASCII кодът за раздел?

Започнах да изграждам текстовия низ. Избрах променлива на MS за MyString.

MS = "Sum:" &

Това е точката, в която имах нужда от табулатор. Достатъчно съм отвратителен, за да знам няколко ASCII символа (10 = LineFeed, 13 = Връщане на каретата, 32 = интервал, 65 = A, 90 = Z), но не можах да си спомня раздела. Тъй като щях да се насоча към Bing, за да го потърся, си спомних, че можете да използвате vblf във вашия код за linefeed или vbcr в кода си за връщане на карета, затова написах vbtab с малки букви. След това преминах към нов ред, за да позволя на Excel VBA да изписва с главни думи думите, които разбира. Надявах се да видя vbtab да вдига капитал и със сигурност редът стана с главни букви, което показва, че VBA ще ми даде знак за табулация.

Ако въведете своя VBA с малки букви, когато отидете на нов ред, ще видите всички правилно изписани думи да вземат главна буква някъде в думата. На изображението по-долу vblf, vbcr, vbtab са известни на vba и се пишат с главни букви след преминаване към нов ред. Нещото, което измислих, vbampersand, не е известно на VBA, така че не се изписва с главни букви.

В този момент ставаше въпрос за обединяване на 6 етикета и 6 стойности в един дълъг низ. Не забравяйте в кода по-долу, че _ в края на всеки ред означава, че редът на кода е продължен на следващия ред.

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

След като обединих всички етикети и ценности заедно, исках да се възхищавам на работата си, така че показах резултата в MsgBox. Пуснах кода и той работи прекрасно:

Мислех си, че съм у дома си свободен. Ако можех просто да вкарам MS в клипборда, бих могъл да започна да записвам Podcast 1894. Може би MS.Copy би свършил работа?

За съжаление не беше толкова лесно. MS.Copy не е валиден ред код.

И така, отидох в Google и потърсих „Excel VBA Copy Variable to Clipboard“. Един от най-добрите резултати беше тази публикация в Board Board. В този пост старите ми приятели Хуан Пабло и NateO се опитваха да помогнат на ОП. Истинският съвет обаче беше, когато Хуан Пабло предложи да се използва код от сайта на Excel MVP Chip Pearson. Намерих тази страница, която обяснява как да вкарам променливата в клипборда.

За да добавите нещо в клипборда, първо трябва да отидете в менюто Инструменти на прозореца на VBA и да изберете Референции. Първоначално ще видите няколко препратки, проверени по подразбиране. Библиотеката на Microsoft Forms 2.0 няма да бъде проверена. Трябва да го намерите в много дългия списък и да го добавите. За щастие за мен беше на първата страница с избори, където зелената стрелка го показва. След като добавите отметката до препратката, тя се премества в горната част.

Кодът на чипа няма да работи, ако не добавите референцията, така че не пропускайте горната стъпка!

След като добавите референцията, завършете макроса, като използвате кода на Чип:

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

Преди да запиша подкаста, направих тест, за да се уверя, че работи. Разбира се, когато стартирах макроса, след това избрах нов диапазон и натиснах Ctrl + V, за да го поставя, клипбордът беше изпразнен в диапазон от 6 реда x 2 колони.

У-у-у! Подготвих заглавната карта на PowerPoint за епизода, включих Camtasia Recorder и записах всичко по-горе. Но … тъй като щях да покажа заключителните кредити, ме обзе чувство на неприятности. Този макрос поставяше статистическите данни като статични стойности. Ами ако основните данни се променят? Не искате ли поставеният блок да се актуализира? Настъпи дълга пауза в подкаста, където обмислях какво да направя. Накрая щракнах иконата на Camtasia Pause Recording и отидох да видя дали мога да поставя формула в низа на MS и дали тя ще бъде поставена правилно. Разбира се, така е. Дори не завърших напълно макроса или направих повече от един тест, когато включих рекордера отново и говорех за този макрос. В подкаста предположих, че това никога няма да работи за непрекъснати селекции, но при по-късно тестване наистина работи.Ето макроса, който да поставите като формули:

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

След публикуването на видеоклипа, редовният зрител Майк Флис попита дали има начин да се изградят формулите, които непрекъснато да се актуализират, за да се покаже статистиката за какъвто и да е диапазон. Това ще изисква макрос Worksheet_SelectionChange, който постоянно ще актуализира именен диапазон, за да съответства на избора. Въпреки че това е страхотна хитрост, той принуждава макроса да се изпълнява всеки път, когато премествате показалеца на клетката и това ще изчиства постоянно стека UnDo. Така че, ако използвате този макрос, той трябва да бъде добавен във всеки панел с код на работен лист, където искате да работи, и ще трябва да живеете без Undo на тези работни листове.

Първо от Excel щракнете с десния бутон върху раздела на лист и изберете Преглед на кода. След това поставете този код в.

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

Върнете се обратно в Excel. Изберете нова клетка и въведете формулата =SUM(SelectedData). Първоначално ще получите кръгова справка. След това изберете друг диапазон от числови клетки и общата сума на формулата, която току-що създадохте, ще се актуализира.