Сложить ячейки vba excel
WorksheetFunction.SumIfs – это метод VBA Excel, который вычисляет сумму числовых значений в диапазоне ячеек с учетом нескольких условий (критериев).
Синтаксис метода WorksheetFunction.SumIfs
- ДСумм – диапазон суммирования, представляющий из себя часть таблицы, в ячейках которого, соответствующих условиям (критериям), суммируются значения.
- ДУсл – диапазон условия, представляющий из себя часть обрабатываемой таблицы, в ячейках которого ищется совпадение с условием (критерием) суммирования.
- Усл – это условие, которое применимо к диапазону, указанному перед ним, и определяющее критерий суммирования.
Первые три аргумента являются обязательными, остальные – необязательные. Всего выражение может содержать один диапазон суммирования (ДСумм) и четырнадцать пар диапазон+условие (ДУсл+Усл).
Диапазоны условий не должны повторяться – повторение приведет к ошибке. Диапазон суммирования можно один раз использовать как диапазон условия.
В параметре «Усл» метода 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
Применение двух числовых условий в качестве критериев суммирования и использование диапазона суммирования в качестве диапазона условия:
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Range | Диапазон — диапазон ячеек, который необходимо оценить по критериям. |
Arg2 | Обязательный | Variant | Критерии — критерии в виде числа, выражения или текста, определяя, какие ячейки будут добавлены. Например, критерии могут быть выражены как 32, "32", ">32" или "яблоки". |
Arg3 | Необязательный | Variant | Sum_range — фактические ячейки, которые необходимо добавить, если их соответствующие ячейки в диапазоне соответствуют критериям. Если sum_range опущен, ячейки в диапазоне оцениваются по критериям и добавляются, если они соответствуют критериям. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Sum_range не должен быть того же размера и формы, что и диапазон. Фактические ячейки, которые добавляются, определяются с помощью верхней левой ячейки в sum_range в качестве первой ячейки, а затем в том числе ячеек, которые соответствуют размеру и форме для диапазона. Пример:
Если диапазон | И sum_range это | Фактические ячейки |
---|---|---|
A1:A5 | B1:B5 | B1:B5 |
A1:A5 | B1:B3 | B1:B5 |
A1:B4 | C1:D4 | C1:D4 |
A1:B4 | C1:C2 | C1:D4 |
В критериях используйте символы подпольной карты, знак вопросов (?) и звездочки (*). Знак вопроса совпадает с любым одним персонажем; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический знак вопроса или звездочки, введите тильду (~) перед персонажем.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Добавляет ячейки в диапазон, который соответствует нескольким критериям.
Синтаксис
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Range | Sum_range — диапазон суммы. |
Arg2 | Обязательный | Range | Criteria_range1, criteria_range2. - Один или несколько диапазонов, в которых можно оценить связанные критерии. |
Arg3 — Arg30 | Обязательный | Variant | Criteria1, criteria2. - Один или несколько критериев в виде числа, выражения, ссылки на ячейки или текста, определяя, какие ячейки будут добавлены. Например, критерии могут быть выражены как 32, "32", ">32", "яблоки" или B4. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Каждая ячейка в sum_range суммируется только в том случае, если все указанные соответствующие критерии соответствуют действительности для этой ячейки.
Ячейки в sum_range, содержащие TRUE, оцениваются как 1; ячейки в sum_range, содержащие FALSE, оцениваются как 0 (ноль).
В критериях используйте символы подпольной карты, знак вопросов (?) и звездочки (*). Знак вопроса совпадает с любым одним персонажем; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический знак вопроса или звездочки, введите тильду (~) перед персонажем.
Каждый criteria_range не должен быть того же размера и формы, что и sum_range. Фактические ячейки, которые добавляются, определяются с помощью верхней левой ячейки в этой criteria_range в качестве первой ячейки, а затем в том числе ячеек, которые соответствуют размеру и форме для sum_range. Пример:
Если sum_range | И criteria_range | Фактические оцениваемые ячейки |
---|---|---|
A1:A5 | B1:B5 | B1:B5 |
A1:A5 | B1:B3 | B1:B5 |
A1:B4 | C1:D4 | C1:D4 |
A1:B4 | C1:C2 | C1:D4 |
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :
Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :
Способ 4. Суммирование каждой 2-й, 3-й. N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
In Excel, you can use VBA to calculate the sum of values from a range of cells or multiple ranges. And, in this tutorial, we are going to learn the different ways that we can use this.
Sum in VBA using WorksheetFunction
In VBA, there are multiple functions that you can use, but there’s no specific function for this purpose. That does not mean we can’t do a sum. In VBA, there’s a property called WorksheetFunction that can help you to call functions into a VBA code.
Let sum values from the range A1:A10.
Now when you run this code, it will calculate the sum for the values that you have in the range A1:A10 and enter the value in cell B1.
Sum Values from an Entire Column or a Row
In that just need to specify a row or column instead of the range that we have used in the earlier example.
Use VBA to Sum Values from the Selection
Now let’s say you want to sum value from the selected cells only in that you can use a code just like the following.
In the above code, we have used the selection and then specified it to the variable “sRange” and then use that range variable’s address to get the sum.
VBA Sum All Cells Above
The following code takes all the cells and sum values from them and enters the result in the selected cell.
Sum a Dynamic Range using VBA
And in the same way, you can use a dynamic range while using VBA to sum values.
Sum a Dynamic Column or a Row
In the same way, if you want to use a dynamic column you can use the following code where it will take the column of the active cell and sum for all the values that you have in it.
Using SUMIF with VBA
Just like sum you can use the SUMIF function to sum values with criteria just like the following example.
Sub vba_sumif() Dim cRange As Range Dim sRange As Range Set cRange = Range("A2:A13") Set sRange = Range("B2:B13") Range("C2") = _ WorksheetFunction.SumIf(cRange, "Product B", sRange) End Sub
There’s More
This is VBA tutorial is written by Puneet Gogia for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Mac.
Читайте также: