Из этого туториала Вы узнаете, как использовать функции Excel СУММЕСЛИ и СУММЕСЛИМН в VBA.
VBA не имеет эквивалента функций СУММЕСЛИ или СУММЕСЛИМН, которые вы можете использовать - пользователь должен использовать встроенные функции Excel в VBA, используя Рабочий лист объект.
Рабочий лист СУММЕСЛИ
Объект WorksheetFunction можно использовать для вызова большинства функций Excel, доступных в диалоговом окне «Вставить функцию» в Excel. Функция СУММЕСЛИ - одна из них.
| 123 | Sub TestSumIf ()Диапазон ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))Конец подписки |
Приведенная выше процедура суммирует только ячейки в диапазоне (D2: D9), если соответствующая ячейка в столбце C = 150.
Присвоение результата СУММЕСЛИ переменной
Возможно, вы захотите использовать результат своей формулы в другом месте кода, а не записывать его непосредственно обратно в Excel Range. В этом случае вы можете присвоить результат переменной, которая будет использоваться позже в вашем коде.
| 1234567 | Sub AssignSumIfVariable ()Тусклый результат как двойной'Назначьте переменнуюresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Показать результатMsgBox "Суммарный результат, соответствующий коду продажи 150:" & resultКонец подписки |
Использование СУММЕСЛИМН
Функция СУММЕСЛИМН аналогична функции рабочего листа СУММЕСЛИ, но позволяет проверять более одного критерия. В приведенном ниже примере мы пытаемся сложить продажную цену, если код продажи равен 150 И Себестоимость больше 2. Обратите внимание, что в этой формуле диапазон ячеек для суммирования находится перед критериями, тогда как в функции СУММЕСЛИ - позади.
| 123 | Sub MultipleSumIfs ()Range ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")Конец подписки |
Использование СУММЕСЛИ с объектом диапазона
Вы можете назначить группу ячеек объекту Range, а затем использовать этот объект Range с Рабочий лист объект.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum as Range'назначить диапазон ячеекУстановить rngCriteria = Range ("C2: C9")Установить rngSum = Range ("D2: D9")'используйте диапазон в формулеДиапазон ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'освободить объекты диапазонаУстановить rngCriteria = NothingУстановить rngSum = NothingКонец подписки |
Использование СУММЕСЛИМН для объектов с несколькими диапазонами
Точно так же вы можете использовать СУММЕСЛИМН для нескольких объектов диапазона.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As ДиапазонDim rngCriteria2 as RangeDim rngSum as Range'назначить диапазон ячеекУстановить rngCriteria1 = Range ("C2: C9")Установить rngCriteria2 = Range ("E2: E10")Установить rngSum = Range ("D2: D10")'используйте диапазоны в формулеДиапазон ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'отпустить объект диапазонаУстановить rngCriteria1 = NothingУстановить rngCriteria2 = NothingУстановить rngSum = NothingКонец подписки |
Обратите внимание: поскольку вы используете знак «больше», критерии больше 2 должны быть заключены в круглые скобки.
Формула СУММЕСЛИ
Когда вы используете Рабочий лист Функция СУММЕСЛИ чтобы добавить сумму к диапазону на листе, возвращается статическая сумма, а не гибкая формула. Это означает, что при изменении ваших цифр в Excel значение, возвращаемое Рабочий лист не изменится.
В приведенном выше примере процедура суммировала Range (D2: D9), где SaleCode равняется 150 в столбце C, а результат был помещен в D10. Как вы можете видеть в строке формул, это число, а не формула.
Если любое из значений изменится в диапазоне (D2: D9) или в диапазоне (C2: D9), результат в D10 будет НЕТ изменение.
Вместо использования Рабочий лист Функция. Сумма Если, вы можете использовать VBA для применения функции СУММЕСЛИ к ячейке с помощью Формула или Формула R1C1 методы.
Формула Метод
Метод формулы позволяет указать конкретный диапазон ячеек, например: D2: D10, как показано ниже.
| 123 | Sub TestSumIf ()Диапазон ("D10"). FormulaR1C1 = "= СУММЕСЛИ (C2: C9,150, D2: D9)"Конец подписки |
Метод FormulaR1C1
Метод FormulaR1C1 более гибкий, поскольку он не ограничивает вас заданным диапазоном ячеек. Пример ниже даст нам тот же ответ, что и приведенный выше.
| 123 | Sub TestSumIf ()Диапазон ("D10"). FormulaR1C1 = "= СУММЕСЛИ (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Конец подписки |
Однако, чтобы сделать формулу более гибкой, мы могли бы изменить код, чтобы он выглядел так:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= СУММЕСЛИ (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)»Конец подписки |
Где бы вы ни находились на своем листе, формула складывает ячейки, соответствующие критериям, прямо над ней и помещает ответ в вашу ActiveCell. На диапазон внутри функции СУММЕСЛИ необходимо ссылаться с использованием синтаксиса строки (R) и столбца (C).
Оба эти метода позволяют использовать динамические формулы Excel в VBA.
Теперь вместо значения в D10 будет формула.
