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


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

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

Итоги по выделенным ячейкам

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

Выбор функций

И только совсем недавно в последних обновлениях Excel разработчики Microsoft добавили простую, но гениальную фишку - теперь при щелчке мышью по этим итогам они копируются в буфер!

Копирование итогов в буфер

Красота. 

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

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

Откройте на вкладке Разработчик (Developer) редактор Visual Basic или воспользуйтесь для этого сочетанием клавиш Alt+F11. Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:

Sub SumSelected()
    If TypeName(Selection) <> "Range" Then Exit Sub
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText WorksheetFunction.Sum(Selection)
        .PutInClipboard
    End With
End Sub

Логика его работы проста:

  • Сначала идёт "защита от дурака" - мы проверяем что именно выделено. Если выделены не ячейки (а, например, диаграмма), то выходим из макроса.
  • Затем при помощи команды GetObject мы создаем новый объект данных, где будет храниться впоследствии наша сумма выделенных ячеек. Длинный и непонятный буквенно-цифровой код - это, на самом деле, ссылка на ветку реестра Windows, где лежит библиотека Microsoft Forms 2.0 Object Library, которая умеет создавать такие объекты. Иногда такой трюк ещё называют неявным поздним связыванием. Если его не использовать, то пришлось бы заранее делать в файле ссылку на эту библиотеку через меню Tools - References.
  • Сумма выделенных ячеек считается командой WorksheetFunction.Sum(Selection), а затем полученная сумма помещается в буфер обмена командой PutInClipboard

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

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

Содержимое Буфера

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

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

Другие функции итогов

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

  • Sum - сумма
  • Average - среднее арифметическое
  • Count - количество ячеек с числами
  • CountA - количество заполненных ячеек
  • CountBlank - количество пустых ячеек
  • Min - минимальное значение
  • Max - максимальное значение
  • Median - медиана (центральное значение)
  • ... и т.д.

С учетом фильтров и скрытых строк-столбцов

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

Sub SumVisible()
    If TypeName(Selection) <> "Range" Then Exit Sub
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible))
        .PutInClipboard
    End With
End Sub

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

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

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

Sub SumFormula()
    If TypeName(Selection) <> "Range" Then Exit Sub
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText "=СУММ(" & Replace(Replace(Selection.Address, ",", ";"), "$", "") & ")"
        .PutInClipboard
    End With
End Sub

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

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

 Sub CustomCalc()
    Dim myRange As Range
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    For Each cell In Selection
        If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then
            If myRange Is Nothing Then
                Set 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

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

Ссылки по теме


Полезные фишки в работе, спасибо!
06.04.2022 04:51:52
Добрый день! Столкнулся с проблемой.
При запуске макроса и Ctrl+V в ячейку вставляются два квадратика с вопросительными знаками внутри. Если открыть окно буфера обмена, то там будут такие же символы.
Если запустить макрос с уже открытым окном буфера обмена, то в нем будет отображаться сумма выделенных ячеек.  Вставить через Ctrl+V также не получается. А если кликнуть на сумму ячеек в буфере обмена, то вставка происходит нормально.
Можете подсказать, как решить проблему?
06.04.2022 15:04:34
Добрый день!

Та же проблема:)
11.04.2022 11:09:13
А у вас параллельно с Excel есть какие-нибудь открытые окна Проводника?
Если есть, то закройте их и попробуйте еще разок :)
27.03.2024 13:50:09
:D:D:D
Один нюанс. Проводник у меня бывает открыт, если мне нужен открытый проводник
Здравствуйте, Microsoft 365 не копирует. Может где то галочку нужно поставить? Подскажите пожалуйста.
22.04.2022 15:41:02
Николай, поменяйте название этой статьи в блоге: там название скопировано из предыдущей статьи.
17.06.2022 15:42:10
Макрос работает только если файл сохранен на жестком диске ПК. Если файл сохранен на сетевом диск  -макрос не работает.
25.10.2022 10:54:58
У меня работает с файлами на сетевом диске
08.07.2022 09:51:53
а где взять обновления копирование с статуса?
И все же может кто то подскажет, excel 2021, windows 10.
Функция копирования в буфер из статус бара Не работает. в чем дело может быть ?  
Добрый день!
А можно написать макрос который будет копировать в буфер несколько диаграмм?
31.10.2022 11:46:46
Добрый день !

Очень полезная "фишка" в работе, особенно "Если нужная живая формула". Но как ее модифицировать, если нужная формула ссылающаяся на данные на другом листе?

Цены бы не было :)
03.11.2022 10:55:27
Николай Владимирович, здравствуйте!
Всё, что связано с АДРЕСОМ диапазона сработает только для адресов не более 255 символов.
Если длиннее, нужно сумму сумм делать…

Остальным, задающим вопросы: если ответа нет, то спрашивайте на местном форуме
Наверх