Перейти к содержимому

VBA Excel. Метод WorksheetFunction.SumIfs

Суммирование числовых значений ячеек в диапазоне с учетом нескольких условий в коде VBA Excel. Метод WorksheetFunction.SumIfs – синтаксис, параметры, примеры.

  1. Синтаксис метода WorksheetFunction.SumIfs
  2. Вычисление сумм с несколькими условиями
WorksheetFunction.SumIfs – это метод VBA Excel, который вычисляет сумму числовых значений в диапазоне ячеек с учетом нескольких условий (критериев).

Синтаксис метода WorksheetFunction.SumIfs

WorksheetFunction.SumIfs (ДСумм, ДУсл_1, Усл_1, ДУсл_2, Усл_2, ..., ДУсл_14, Усл_14)
  • ДСумм – диапазон суммирования, представляющий из себя часть таблицы, в ячейках которого, соответствующих условиям (критериям), суммируются значения.
  • ДУсл – диапазон условия, представляющий из себя часть обрабатываемой таблицы, в ячейках которого ищется совпадение с условием (критерием) суммирования.
  • Усл – это условие, которое применимо к диапазону, указанному перед ним, и определяющее критерий суммирования.

Первые три аргумента являются обязательными, остальные – необязательные. Всего выражение может содержать один диапазон суммирования (ДСумм) и четырнадцать пар диапазон+условие (ДУсл+Усл).

Диапазоны условий не должны повторяться – повторение приведет к ошибке. Диапазон суммирования можно один раз использовать как диапазон условия.

В параметре «Усл» метода WorksheetFunction.SumIfs можно использовать знаки подстановки:

  • вопросительный знак (?) – заменяет один любой символ;
  • звездочка (*) – заменяет любую последовательность символов (в том числе ни одного символа);
  • тильда (~) – ставится перед вопросительным знаком или звездочкой, чтобы они обозначали сами себя.

Вычисление сумм с несколькими условиями

Таблица, которая использовалась для реализации примеров в коде VBA Excel:

Дата Магазин Продавец Выручка
01.11.2019 Липка Лыкова 20000
01.11.2019 Берёзка Серёжкина 18000
01.11.2019 Дубок Бочкина 23000
02.11.2019 Липка Лаптева 30000
02.11.2019 Берёзка Брунькина 25000
02.11.2019 Дубок Жёлудева 17000
03.11.2019 Липка Лыкова 24000
03.11.2019 Берёзка Серёжкина 19000
03.11.2019 Дубок Бочкина 35000
04.11.2019 Липка Лаптева 27000
04.11.2019 Берёзка Брунькина 31000
04.11.2019 Дубок Жёлудева 26000
05.11.2019 Липка Лыкова 16000
05.11.2019 Берёзка Серёжкина 22000
05.11.2019 Дубок Бочкина 33000
06.11.2019 Липка Лаптева 16000
06.11.2019 Берёзка Брунькина 28000
06.11.2019 Дубок Жёлудева 29000

Если хотите повторить примеры, скопируйте эту таблицу и вставьте на рабочий лист Excel в ячейку A1. Таблица займет диапазон A1:D19.

Пример 1
Применение двух числовых условий в качестве критериев суммирования и использование диапазона суммирования в качестве диапазона условия:

Sub Primer1()
Dim a As Double
  a = WorksheetFunction.SumIfs(Range("D2:D19"), _
  Range("A2:A19"), DateValue("03.11.2019"), _
  Range("D2:D19"), ">20000")
MsgBox a
End Sub

В этом примере кода VBA Excel складываются все значения в диапазоне D2:D19, которые соответствуют дате 03.11.2019 в диапазоне A2:A19 и превышают 20000 в диапазоне D2:D19. Обратите внимание, что условие ">20000" заключено в прямые кавычки.

Диапазон D2:D19 используется одновременно как диапазон суммирования и как диапазон условия.

Столбец «Дата» в исходной таблице содержит даты в числовом формате, поэтому мы текстовое отображение нужной даты преобразовали в число: DateValue("03.11.2019"). Если ячейкам этого столбца присвоить текстовый формат, то в условии суммирования дату необходимо будет указать как строку: "03.11.2019".

Пример 2
Использование в условиях суммирования переменных и подстановочных знаков:

Sub Primer2()
Dim a As Double, b As String, c As String
  b = ">=" & CLng(DateValue("04.11.2019"))
  c = "*ина"
  a = WorksheetFunction.SumIfs(Range("D2:D19"), _
  Range("A2:A19"), b, Range("B2:B19"), "Берёзка", _
  Range("C2:C19"), c)
MsgBox a
End Sub

В коде второго примера переменные в строке с методом WorksheetFunction.SumIfs можно заменить выражениями, которые присваиваются этим переменным.

Переменная «b» содержит условие, которое помогает отобрать из диапазона A2:A19 все даты, начиная с 04.11.2019 по 06.11.2019. Функция преобразования типа данных CLng необходима для того, чтобы при записи в строковую переменную дата записалась в виде числа, а не в формате «ДД.ММ.ГГГГ».

Из диапазона B2:B19 отбираются строки, содержащие текст «Берёзка».

Переменная «c» позволяет выбрать в диапазоне C2:C19 все фамилии, оканчивающиеся на «ина».

Третье условие на результаты выполнения кода примера 2 не влияет, так как фамилии всех продавцов «Берёзки» оканчиваются на «ина»: Серёжкина, Брунькина. Но вот если исключить второе условие, то к ним добавится Бочкина.

Смотрите также статьи о методах WorksheetFunction.Sum (суммирование без условия) и WorksheetFunction.SumIf (суммирование с одним условием).

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег [vb] и по окончании кода - [/vb].