Суммирование числовых значений ячеек в диапазоне с учетом нескольких условий в коде VBA Excel. Метод WorksheetFunction.SumIfs – синтаксис, параметры, примеры.
Синтаксис метода 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
Применение двух числовых условий в качестве критериев суммирования и использование диапазона суммирования в качестве диапазона условия:
|
1 2 3 4 5 6 7 |
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
Использование в условиях суммирования переменных и подстановочных знаков:
|
1 2 3 4 5 6 7 8 9 |
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 (суммирование с одним условием).
Добрый день!
Подскажите, можно ли использовать данную функцию, если суммирование по признакам должно происходить в другой книге?
Например, в Книге1, лист1 — таблица, из которой выбираются значения, в книга 2, лист1 — туда, куда вставляются данные. Как в таком случае записать функцию?
Здравствуйте, Анна!
Если таблица расположена в другой книге, добавьте в процедуру имя книги и имя листа (в примере — Отчет.xlsx и Лист1):
Другая книга должна быть предварительно открыта.
Большое спасибо!