Скопируйте значения быстрой статистики в буфер обмена - Советы по Excel

Содержание

Что такое код ASCII для вкладки?

Вопрос возник во время семинара по Excel в Тампе: не было бы здорово, если бы вы могли скопировать статистику из строки состояния в буфер обмена для последующей вставки в диапазон?

Я нажал на человека, задавшего вопрос, как именно должна работать паста. Конечно, вы не можете вставить статистику сразу, потому что у вас выделена куча важных ячеек. Вам придется подождать, выбрать другой пустой диапазон электронной таблицы, вставить (как в Ctrl + V), и статистика появится в диапазоне из 6 строк на 2 столбца. Человек, задавший вопрос, предположил, что это будут статические значения.

Я не пытался ответить на этот вопрос во время семинара, потому что знал, что это может быть немного сложно.

Но недавно я запустил макрос, чтобы посмотреть, можно ли это сделать. Моя идея заключалась в том, чтобы создать длинную текстовую строку, которую можно было бы вставить. Чтобы элементы отображались в двух столбцах, текстовая строка должна иметь метку для столбца 1 (Сумма), а затем вкладку и значение для столбца 2. Затем вам потребуется возврат каретки, метка для строка 2, столбец 1, затем еще одна вкладка, значение и так далее.

Я знал, что Application.WorksheetFunction - отличный способ вернуть результаты функций Excel в VBA, но он не поддерживает все 400+ функций Excel. Иногда, если в VBA уже есть аналогичная функция (LEFT, RIGHT, MID), Application.WorksheetFunction не поддерживает эту функцию. Я запустил VBA с помощью Alt + F11, отобразил панель Immediate Pane с помощью Ctrl + G, а затем ввел несколько команд, чтобы убедиться, что все шесть функций строки состояния поддерживаются. К счастью, все шесть возвращенных значений совпадали с тем, что отображалось в строке состояния.

Чтобы сделать макрос короче, вы можете назначить Application.WorksheetFunction переменной:

Set WF = Application.WorksheetFunction

Затем, позже в макросе, вы можете просто ссылаться на WF.Sum (Selection) вместо того, чтобы снова и снова вводить Application.WorksheetFunction.

Что такое код ASCII для вкладки?

Я начал строить текстовую строку. Я выбрал переменную MS для MyString.

MS = "Sum:" &

Это тот момент, когда мне понадобился символ табуляции. Я достаточно увлечен, чтобы знать несколько символов ASCII (10 = LineFeed, 13 = возврат каретки, 32 = пробел, 65 = A, 90 = Z), но я не мог вспомнить вкладку. Собираясь найти его в Bing, я вспомнил, что вы можете использовать vblf в своем коде для перевода строки или 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». Одним из лучших результатов был этот пост на доске объявлений. В этом посте мои старые друзья Хуан Пабло и 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. Итак, если вы используете этот макрос, его нужно добавить на каждую панель кода рабочего листа, где вы хотите, чтобы он работал, и вам придется жить без отмены на этих листах.

Сначала в Excel щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код. Затем вставьте этот код в.

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

Вернитесь в Excel. Выберите новую ячейку и введите формулу =SUM(SelectedData). Сначала вы получите круговую ссылку. Но затем выберите другой диапазон числовых ячеек, и сумма только что созданной формулы обновится.