Съдържание
Понякога отнема много време, за да се измислят някои неща. Но когато ВЕЧЕ са измислени, постфактум изглеждат очевидни и дори банални. От поредицата „Какво, възможно ли беше?“.
Още от първите версии лентата на състоянието в долната част на прозореца на Microsoft Excel традиционно показва общи суми за избрани клетки:
Ако желаете, дори беше възможно да щракнете с десния бутон върху тези резултати и да изберете от контекстното меню точно кои функции искаме да видим:
И съвсем наскоро, в най-новите актуализации на Excel, разработчиците на Microsoft добавиха проста, но гениална функция – сега, когато щракнете върху тези резултати, те се копират в клипборда!
Красота.
Но какво да кажем за тези, които все още нямат (или вече?) имат такава версия на Excel? Тук могат да помогнат прости макроси.
Копиране на сумата от избрани клетки в клипборда с помощта на макрос
Отворете в раздела предприемач (Разработчик) редактор Visual Basic или използвайте тази клавишна комбинация Друг+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 For Every 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 Както лесно можете да си представите, условията могат да бъдат зададени абсолютно всякакви - до клетъчни формати - и във всяко количество (включително чрез свързването им заедно с логически оператори или или и). Има много поле за въображение.
- Преобразувайте формули в стойности (6 начина)
- Какво представляват макросите, как да ги използвате, къде да вмъкнете кода на Visual Basic
- Полезна информация в лентата на състоянието на Microsoft Excel