Вычисление суммы числовых аргументов или значений диапазона ячеек с помощью кода VBA Excel. Метод WorksheetFunction.Sum – синтаксис, примеры.
Метод WorksheetFunction.Sum
Метод Sum объекта WorksheetFunction возвращает сумму значений своих аргументов. Аргументы могут быть числами, переменными и выражениями, возвращающими числовые значения.
Синтаксис метода WorksheetFunction.Sum:
|
1 |
WorksheetFunction.Sum(Arg1, Arg2, Arg3, ..., Arg30) |
- Arg – аргумент, который может быть числом, переменной, выражением. Тип данных — Variant. Максимальное количество аргументов – 30.
- Метод WorksheetFunction.Sum возвращает значение типа Double.
Значение функции рабочего листа Sum может быть присвоено:
- переменной числового типа Double или универсального типа Variant (при использовании числовых переменных других типов возможны недопустимые округления значений, возвращаемых методом WorksheetFunction.Sum);
- выражению, возвращающему диапазон ячеек (точнее, возвращающему свойство Value диапазона, которое является свойством по умолчанию и его в выражениях можно не указывать);
- другой функции в качестве аргумента.
Примеры вычисления сумм в коде VBA
Пример 1
Присвоение значений, вычисленных методом WorksheetFunction.Sum, переменной:
|
1 2 3 4 5 6 7 |
Sub Primer1() Dim a As Integer a = WorksheetFunction.Sum(5.5, 25, 8, -28) MsgBox a a = WorksheetFunction.Sum(4.5, 25, 8, -28) MsgBox a End Sub |
Наверно, вы удивитесь, но информационное окно MsgBox дважды покажет одно и то же число 10. Почему так происходит?
Дело в том, что переменная a объявлена как целочисленная (Integer). Дробные числа, возвращенные функцией рабочего листа Sum, были округлены, а в VBA Excel применяется бухгалтерское округление, которое отличается от общепринятого.
При бухгалтерском округлении 10.5 и 9.5 округляются до 10. Будьте внимательны при выборе типа переменной.
Пример 2
Вычисление суммы значений диапазона ячеек, расположенного на текущем листе:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub Primer2() 'Итог в 6 ячейке столбца "A" Cells(6, 1) = WorksheetFunction.Sum(Cells(1, 1), Cells(2, 1), _ Cells(3, 1), Cells(4, 1), Cells(5, 1)) 'Итог в 6 ячейке столбца "B" Range("B6") = WorksheetFunction.Sum(Range(Cells(1, 2), Cells(5, 2))) 'Итог в 6 ячейке столбца "C" Range("B6").Offset(, 1) = WorksheetFunction.Sum(Range("C1:C5")) 'Присвоение суммы диапазону ячеек Range("A8:C10") = WorksheetFunction.Sum(Range("A1:C5")) End Sub |
Если хотите проверить работу кода в своем редакторе VBA, заполните на рабочем листе Excel диапазон A1:C5 любыми числами.
Самая удобная формулировка по моему мнению:
Cells(10, 6) = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(9, 6))) ,
где вместо номеров строк и столбцов можно использовать переменные.
Пример 3
Вычисление суммы значений диапазона ячеек, расположенного на другом листе:
|
1 2 3 |
Sub Primer3() Лист1.Cells(3, 10) = WorksheetFunction.Sum(Range(Лист2.Cells(2, 5), Лист2.Cells(100, 5))) End Sub |
Пример 4
Самый простой пример, где метод WorksheetFunction.Sum используется в качестве аргумента другой функции:
|
1 2 3 |
Sub Primer4() MsgBox WorksheetFunction.Sum(24, -5, 8 * 2) End Sub |
В данном случае значение функции рабочего листа Sum является аргументом функции MsgBox.
Возможно, вам интересно, откуда я взял, что функция рабочего листа (WorksheetFunction) является объектом, а сумма (Sum) ее методом? Из справки Microsoft.
Смотрите также статьи о методах WorksheetFunction.SumIf (суммирование с одним условием) и WorksheetFunction.SumIfs (суммирование с несколькими условиями).
Подскажите, можно ли таким образом
WorksheetFunction.Sum(Range(Cells(1, 2), Cells(5, 2)))выделить несколько несмежных диапазонов? С указанием столбцов и строк вроде как можно. А вот указать несколько диапазонов через cells (x,y) — это возможно?Привет, Алексей!
Посчитать сумму несмежного диапазона можно так:
а выделить его — так:
Информационные источники предлагают еще одно решение.
Какие преимущества/недостатки каждого из них?
Application.Sum и WorksheetFunction.Sum – два способа записи одной и той же функции. Отличие в том, что запись Application.Sum не упоминается в документации по VBA Excel, хотя и работает, как и другие встроенные функции рабочего листа. Я предпочитаю запись WorksheetFunction.Sum.
Доброго времени суток!
Подскажите как получить сумму на Листе2, а результат вывести в ячейке на Листе1?!
PosStr — переменная с последней ячейкой
Здравствуйте, Виктор!
Здравствуйте, Евгений!
К сожалению не работает.
Ошибка:
Application-defined or object-defined errorВот в принципе весь код по нажатию кнопки на листе1:
Будет отлично, если Вы поможете решить эту задачку.
Заранее спасибо!
Виктор, попробуйте запустить одну строку кода:
Лист1.Cells(3, 10): Лист1 — имя листа в проводнике редактора VBA;Sheets("Лист1"): Лист1 — имя ярлычка.Добрый день!
Можно ли использовать данную функцию, если необходимо сложить одинаковые ячейки, но в разных книгах?
У меня 50 разных файлов, одинаковых по структуре, необходимо свести все данные в 1 общий файл, сохранив структуру.
Здравствуйте, Анна!
Можно, но без WorksheetFunction будет попроще:
WorksheetFunction вам не подойдет, так как эта функция может обработать только 30 аргументов.
Добрый день!
Каким образом в таком случае лучше записать макрос? может быть, есть какой-то подходящий пример?
Здравствуйте, Анна!
Смотрите примеры в статье Сбор данных из открытых книг.
Доброго вечера!
Можно ли этой функцией суммировать необходимый диапазон в массиве?
Есть массив с числами, нужно постоянно сдвигать диапазон суммирования и возвращать сумму для формулы.
Как лучше сделать?