Скопировать сумму выбранных ячеек в буфер обмена

Иногда на придумывание некоторых вещей уходит очень много времени. Но когда они УЖЕ изобретены, постфактум кажутся очевидными и даже банальными. Из серии «а что, такое было возможно?».

С самых первых версий в статусной строке внизу окна Microsoft Excel традиционно отображались итоги по выделенным ячейкам:

Скопировать сумму выбранных ячеек в буфер обмена

При желании можно было даже щелкнуть правой кнопкой мыши по этим результатам и выбрать из контекстного меню, какие именно функции мы хотим видеть:

Скопировать сумму выбранных ячеек в буфер обмена

А совсем недавно в последних обновлениях Excel разработчики Microsoft добавили простую, но гениальную функцию — теперь при нажатии на эти результаты они копируются в буфер обмена!

Скопировать сумму выбранных ячеек в буфер обмена

Красота. 

А как быть тем, у кого еще нет (или уже?) такой версии Excel? Здесь могут помочь простые макросы.

Копирование суммы выделенных ячеек в буфер обмена с помощью макроса

Открыть во вкладке разработчик (Разработчик) редактор Визуальный Бейсик или используйте это сочетание клавиш другой+F11. Вставьте новый пустой модуль через меню Вставка – Модуль и скопируйте туда следующий код:

Sub SumSelected() Если TypeName(Selection) <> "Range" Затем выйдите из Sub с помощью GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection).PutInClipboard End With End Sub

Его логика проста:

  • Сначала идет «защита от дурака» — проверяем, что именно выделено. Если выбраны не ячейки (а, например, диаграмма), то выходим из макроса.
  • Затем с помощью команды Получить объект мы создаем новый объект данных, в котором позже будет храниться наша сумма выбранных ячеек. Длинный и непонятный буквенно-цифровой код — это, по сути, ссылка на ветку реестра Windows, где находится библиотека. Библиотека объектов Microsoft Forms 2.0, который может создавать такие объекты. Иногда этот трюк еще называют неявное позднее связывание. Если вы ею не пользуетесь, то вам придется сделать ссылку на эту библиотеку в файле через меню Инструменты — Справочники.
  • Сумма выделенных ячеек считается командой WorksheetFunction.Sum(Выбор), а затем полученная сумма помещается в буфер обмена командой Вставить в буфер обмена

Для удобства использования можно, конечно, назначить этому макросу сочетание клавиш с помощью кнопки Макрос таб разработчик (Разработчик — Макросы).

А если вы хотите посмотреть, что именно было скопировано после запуска макроса, вы можете включить панель «Буфер обмена» с помощью маленькой стрелочки в правом нижнем углу соответствующей группы на Основной (Главная) Вкладка:

Скопировать сумму выбранных ячеек в буфер обмена

Не только сумма

Если помимо банальной суммы хочется чего-то еще, то можно воспользоваться любой из функций, которые нам предоставляет объект. Рабочий листФункция:

Скопировать сумму выбранных ячеек в буфер обмена

Например, есть:

  • Сумма – сумма
  • Среднее – среднее арифметическое
  • Count – количество ячеек с числами
  • CountA – количество заполненных ячеек
  • CountBlank – количество пустых ячеек
  • Мин – минимальное значение
  • Макс – максимальное значение
  • Медиана – медиана (центральное значение)
  • ... и т. д.

Включая фильтры и скрытые строки-столбцы

Что делать, если строки или столбцы скрыты (вручную или с помощью фильтра) в выбранном диапазоне? Чтобы не учитывать их в итогах, нам нужно будет немного доработать наш код, добавив в объект Выбор собственность Спеццеллс (xlCellTypeVisible):

Sub SumVisible() If TypeName(Selection) <> "Range" Затем выйдите из Sub с помощью GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)) . PutInClipboard End с конечным подзаголовком

В этом случае вычисление любой итоговой функции будет применяться только к видимым ячейкам.

Если вам нужна живая формула

Если пофантазировать, то можно придумать сценарии, когда лучше копировать в буфер не число (константу), а живую формулу, которая подсчитывает нужные нам итоги по выделенным ячейкам. В этом случае вам придется склеивать формулу из фрагментов, добавляя к ней удаление знаков доллара и замену запятой (которая в VBA используется как разделитель между адресами нескольких выделенных диапазонов) на точку с запятой:

Sub SumFormula() Если TypeName(Selection) <> "Диапазон" Затем выйдите из Sub с помощью GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Замените(Replace(Selection. Адрес, ",", ";"), "$", "") & ")".PutInClipboard End With End Sub

Суммирование с дополнительными условиями

И, наконец, для совсем маньяков можно написать макрос, который будет суммировать не все выбранные ячейки, а только те, которые удовлетворяют заданным условиям. Так, например, будет выглядеть макрос, помещающий в Буфер сумму выделенных ячеек, если их значения больше 5 и при этом они заполнены каким-либо цветом:

 Sub CustomCalc() Dim myRange As Range, если TypeName(Selection) <> «Range», затем выйти из Sub для каждой выделенной ячейки, если cell.Value > 5 и cell.Interior.ColorIndex <> xlNone, тогда, если myRange ничего не значит, установите myRange = cell Else Set myRange = Union(myRange, cell) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange). .PutInClipboard End With End Sub

Как легко представить, условия можно задавать абсолютно любые – вплоть до форматов ячеек – и в любом количестве (в том числе связывая их между собой логическими операторами или или и). Здесь есть много места для фантазии.

  • Преобразование формул в значения (6 способов)
  • Что такое макросы, как их использовать, куда вставлять код Visual Basic
  • Полезная информация в строке состояния Microsoft Excel

Оставьте комментарий