Копіювати суму вибраних клітинок до буфера обміну

Іноді потрібно дуже багато часу, щоб придумати якісь речі. Але коли вони ВЖЕ вигадані, постфактум вони здаються очевидними і навіть банальними. Із серії «А що, це було можливо?».

З найперших версій рядок стану внизу вікна Microsoft Excel традиційно відображав підсумки для вибраних комірок:

Копіювати суму вибраних клітинок до буфера обміну

При бажанні можна було навіть клацнути правою кнопкою миші на цих результатах і вибрати в контекстному меню, які саме функції ми хочемо побачити:

Копіювати суму вибраних клітинок до буфера обміну

І зовсім недавно, в останніх оновленнях Excel, розробники Microsoft додали просту, але геніальну функцію – тепер, коли ви натискаєте ці результати, вони копіюються в буфер обміну!

Копіювати суму вибраних клітинок до буфера обміну

Краса. 

Але що робити тим, хто ще не має (чи вже?) має таку версію Excel? Тут можуть допомогти прості макроси.

Копіювання суми виділених комірок у буфер обміну за допомогою макросу

Відкрити у вкладці розробник (розробник) редактор Візуальний Бейсік або скористайтеся цією комбінацією клавіш інший+F11. Вставте новий порожній модуль через меню Вставка – модуль і скопіюйте туди наступний код:

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, які можуть створювати такі об’єкти. Іноді цей трюк ще називають неявне пізнє зв'язування. Якщо ви не використовуєте її, то вам доведеться зробити посилання на цю бібліотеку у файлі через меню Інструменти — Посилання.
  • Сума виділених клітинок вважається командою WorksheetFunction.Sum(Selection), а потім отримана сума поміщається в буфер обміну командою Помістити в буфер обміну

Для зручності використання ви можете, звичайно, призначити цей макрос комбінації клавіш за допомогою кнопки Макрос таб розробник (Розробник — Макроси).

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

Копіювати суму вибраних клітинок до буфера обміну

Не тільки сума

Якщо крім банальної суми хочеться ще чогось, то можна скористатися будь-якою з функцій, які надає нам об'єкт Функція аркуша:

Копіювати суму вибраних клітинок до буфера обміну

Наприклад, є:

  • Сума – сума
  • Середнє – середнє арифметичне
  • Кількість – кількість комірок з числами
  • CountA – кількість заповнених комірок
  • CountBlank – кількість порожніх комірок
  • Min – мінімальне значення
  • Max – максимальне значення
  • Медіана – медіана (центральне значення)
  • ... тощо

Включаючи фільтри та приховані рядки-стовпці

Що робити, якщо рядки або стовпці приховані (вручну або за допомогою фільтра) у вибраному діапазоні? Щоб не враховувати їх у підсумках, нам потрібно буде трохи змінити наш код, додавши до об’єкта вибір власність 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. Адреса, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub

Підсумовування з додатковими умовами

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

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub Для кожної клітинки у виділенні 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

Як неважко уявити, умови можна задавати абсолютно будь-які – аж до форматів комірок – і в будь-якій кількості (в тому числі, зв’язуючи їх між собою логічними операторами або або і). Простір для фантазії великий.

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

залишити коментар