Сумма диапазона ячеек Vba Excel

Обновлено: 21.11.2024

Функция "Сумма" в Excel — одна из наиболее широко используемых функций и, по сути, одна из первых функций, с которыми знакомится пользователь Excel, когда учится работать с Excel! Проще говоря, функция Sum суммирует диапазон ячеек для пользователя. Эту функцию, наряду со всеми встроенными функциями Excel, также можно использовать в коде VBA.

Синтаксис функции суммирования

Как и в случае с функцией, используемой в Excel, синтаксис функции суммирования включает имя функции, а затем диапазон ячеек для сложения.

=СУММ(A1:A3)

Использование функции Sum в коде VBA

Чтобы использовать эту функцию в коде VBA, строка кода может быть следующей:

Обратите внимание, что для работы функция должна быть заключена в кавычки.

Использование функции Sum со ссылками на ячейки

Вы также можете использовать объект Range и использовать ссылки на ячейки в коде VBA при использовании функции суммы

Объект Range позволяет ссылаться на ячейки, которые вы хотите сложить, ссылаясь на позицию строки и столбца ячеек — в приведенном выше примере сначала указывается строка, а затем столбец. Например, Cells(1,1) — это то же самое, что сказать A1, а Cells(3,1) — это то же самое, что сказать A3.

В обоих этих случаях при запуске кода формула суммы будет помещена на лист Excel.

Использование функции суммирования с командой функции рабочего листа

Вместо того, чтобы заключать формулу в кавычки, мы можем использовать функцию суммирования VBA.

WorksheetFunction.Sum(Range("A1:A3")

Чтобы использовать эту функцию в коде VBA, строка кода может быть следующей:

ИЛИ с использованием ссылок на ячейки

В обоих этих случаях, когда вы запускаете код, он помещает значение в код, а НЕ в формулу!

Это может вызвать проблемы, если значения в A1–A3 изменятся в будущем.

В этой статье вы узнали, как использовать функцию суммы Excel в коде VBA, а также WorksheetFunction.Sum в коде VBA. Функция суммирования Excel определенно более гибкая, поскольку если ваши значения в ячейках Excel изменяются, то ответ в сумме изменится без необходимости повторного запуска процедуры, которую вы создали для суммирования ячеек.

Об авторе Мелани Дженкинс

Мелани Дженкинс занимается проектированием баз данных с использованием Microsoft Access, SQL, VB и VBA в течение последних 23 лет. Кроме того, Мелани является сертифицированным инструктором Microsoft MOS и преподает все уровни офиса Microsoft, специализируясь на расширенном обучении Excel, Access и VBA. Мелани также написала множество технических руководств и руководств по обучению пользователей как к разработанному ею программному обеспечению, так и к обучающим курсам, которые она преподает.

В Excel вы можете использовать VBA для вычисления суммы значений из диапазона ячеек или нескольких диапазонов. И в этом уроке мы узнаем, как это можно использовать.

Суммировать в VBA с помощью WorksheetFunction

В VBA есть несколько функций, которые вы можете использовать, но нет специальной функции для этой цели. Это не значит, что мы не можем сделать сумму. В VBA есть свойство WorksheetFunction, которое может помочь вам вызывать функции в коде VBA.

Позвольте суммировать значения из диапазона A1:A10.

Теперь, когда вы запускаете этот код, он вычисляет сумму значений в диапазоне A1:A10 и вводит значение в ячейку B1.

Суммировать значения из всего столбца или строки

В этом случае просто нужно указать строку или столбец вместо диапазона, который мы использовали в предыдущем примере.

Использование VBA для суммирования значений из выделения

Теперь предположим, что вы хотите суммировать значения из выбранных ячеек только в том случае, если вы можете использовать код, подобный следующему.

В приведенном выше коде мы использовали выборку, а затем указали ее в переменной «sRange», а затем использовали адрес этой переменной диапазона для получения суммы.

VBA суммирует все ячейки выше

Следующий код берет все ячейки и суммирует значения из них и вводит результат в выбранную ячейку.

Суммировать динамический диапазон с помощью VBA

Точно так же вы можете использовать динамический диапазон при использовании VBA для суммирования значений.

Суммирование динамического столбца или строки

Точно так же, если вы хотите использовать динамический столбец, вы можете использовать следующий код, где он возьмет столбец активной ячейки и суммирует все значения, которые у вас есть в нем.

Использование SUMIF с VBA

Как и в случае с суммой, вы можете использовать функцию СУММЕСЛИ для суммирования значений с критериями, как в следующем примере.

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 , "Товар Б", sRange) End Sub

Есть еще

Это руководство по VBA написано Пунитом Гогиа для Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Excel для Mac.

Вы можете использовать простую формулу для суммирования чисел в диапазоне (группе ячеек), но функцию СУММ проще использовать, когда вы работаете с несколькими числами. Например, =СУММ(A2:A6) с меньшей вероятностью содержит опечатки, чем =A2+A3+A4+A5+A6.

В следующей формуле используются два диапазона ячеек: =СУММ(A2:A4;C2:C3) суммирует числа в диапазонах A2:A4 и C2:C3. Вы должны нажать Enter, чтобы получить общее количество 39787.

Чтобы создать формулу:

Введите =СУММ в ячейке, а затем открывающую скобку (.

Чтобы ввести первый диапазон формулы, который называется аргументом (фрагментом данных, необходимых для выполнения формулы), введите A2:A4 (или выберите ячейку A2 и перетащите через ячейку A6).

Введите запятую (,), чтобы отделить первый аргумент от следующего.

Введите второй аргумент, C2:C3 (или перетащите, чтобы выделить ячейки).

Введите закрывающую скобку ), а затем нажмите Enter.

Каждый аргумент может быть диапазоном, числом или ссылкой на одну ячейку, разделенными запятыми.

Совет. Если вам нужно суммировать столбцы или строки чисел рядом друг с другом, используйте функцию автосуммирования для суммирования чисел.

Попробуйте

Если вы хотите поиграть с нашими образцами данных, вот некоторые данные для использования.

Вы можете увидеть, как работает функция СУММ, скопировав следующую таблицу на рабочий лист и вставив ее в ячейку A1.

Описание

Складывает 5, 15 и 1. Текстовое значение "5" сначала преобразуется в число, а логическое значение ИСТИНА сначала преобразуется в число 1.

Складывает значения в ячейках с A2 по A4.

Складывает значения в ячейках с A2 по A4, а затем добавляет к этому результату 15.

Складывает значения в ячейках A5 и A6, а затем добавляет 2 к этому результату. Поскольку нечисловые значения в ссылках не переводятся — значение в ячейке A5 ('5) и значение в ячейке A6 (ИСТИНА) обрабатываются как текст — значения в этих ячейках игнорируются.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В Excel есть несколько действительно замечательных функций, но в нем нет функции, которая может суммировать значения ячеек на основе цвета ячейки.

Например, у меня есть набор данных, показанный ниже, и я хочу получить сумму всех ячеек оранжевого и желтого цветов.

К сожалению, для этого нет встроенной функции.

Но никогда не говори никогда!

В этом уроке я покажу вам три простых метода, которые вы можете использовать для суммирования по цветам в Excel.

Это руководство охватывает:

СУММ ЯЧИКИ по цвету с использованием фильтра и ПРОМЕЖУТОЧНЫХ ИТОГОВ

Начнем с самого простого.

Ниже у меня есть набор данных, в котором есть имена сотрудников и их номера продаж.

И в этом наборе данных я хочу получить сумму всех ячеек, окрашенных в желтый и оранжевый цвета.

Хотя в Excel нет встроенной функции для суммирования значений на основе цвета ячейки, существует простой обходной путь, основанный на том факте, что вы можете фильтровать ячейки на основе цвета ячейки.

Для этого метода введите приведенную ниже формулу в ячейку B17 (или любую ячейку в том же столбце под набором данных с цветными ячейками).

В приведенной выше формуле ПРОМЕЖУТОЧНЫХ ИТОГОВ я использовал 9 в качестве первого аргумента, который сообщает функции, что я хочу получить сумму диапазона, указанного в качестве второго аргумента.

Но почему бы просто не использовать формулу СУММ?

Это связано с тем, что когда у меня есть формула ПРОМЕЖУТОЧНЫЙ ИТОГ и я фильтрую ячейки, чтобы показать только те ячейки, которые имеют определенный цвет, формула ПРОМЕЖУТОЧНЫЙ ИТОГ покажет мне только сумму видимых ячеек (то, что формула СУММ не может делать).

Поэтому, когда у вас есть формула ПРОМЕЖУТОЧНЫХ ИТОГОВ, выполните следующие шаги, чтобы получить СУММУ на основе цвета ячейки:

  1. Выберите любую ячейку в наборе данных.
  2. Перейдите на вкладку "Данные".

  1. В группе «Сортировка и фильтр» нажмите значок «Фильтр». Это применит фильтр к набору данных, и вы сможете увидеть значок фильтра в заголовках.
  1. В ячейке заголовка "Продажи" нажмите значок "Фильтр".
  1. Перейти к параметру «Фильтр по цвету»
  2. Выберите цвет, по которому вы хотите отфильтровать набор данных.

Как только вы это сделаете, вы заметите, что результат промежуточного итога изменился, и теперь он даст вам только сумму тех ячеек, которые видны (это будут только те ячейки, которые имеют цвет, по которому вы отфильтровали набор данных). ).

Аналогично, если вы отфильтруете набор данных по другому цвету (скажем, по оранжевому, а не по желтому), функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ соответствующим образом скорректирует и даст вам сумму всех ячеек с оранжевым цветом

Совет для профессионалов. Сочетание клавиш для применения фильтра к набору данных: Control + Shift + L (удерживайте клавиши Control и Shift, а затем нажмите клавишу L). Если вы используете Mac, используйте Command + Shift + L

Суммировать ячейки по цвету с помощью VBA

Я упомянул, что в Excel нет встроенной формулы для суммирования на основе значения цвета ячейки. Однако для этого можно создать собственную формулу с помощью VBA.

С помощью VBA вы можете создать пользовательскую функцию, которую можно сохранить в серверной части, а затем использовать ее как любую другую обычную функцию на листе.

Ниже приведен код VBA, который создаст пользовательскую функцию, позволяющую выполнять суммирование по цвету в Excel.

Чтобы использовать эту настраиваемую функцию VBA, сначала нужно скопировать этот код и вставить его в бэкенд редактора VB.

После этого вы сможете использовать эту функцию на листе.

Ниже приведены шаги по добавлению этого кода в редактор VB.

  1. Перейдите на вкладку "Разработчик" на ленте (если у вас не отображается вкладка "Разработчик", нажмите здесь, чтобы узнать, как ее открыть).
  2. Нажмите значок Visual Basic. Откроется редактор Excel Visual Basic.

  1. Нажмите в меню пункт «Вставить».
  2. Нажмите «Модуль». Это вставит новый модуль, и вы сможете увидеть его в проводнике проекта (панель слева, на которой показаны все объекты). Если вы не видите панель Project Explorer, нажмите View, а затем нажмите Project Explorer

  1. Скопируйте приведенный выше код VBA и вставьте его в новое окно кода модуля.

Теперь, когда у вас есть код в серверной части Excel, вы сможете использовать созданную нами функцию (SumByColor) на листе.

Чтобы эта функция работала, мне понадобится ячейка на листе, содержащая тот же цвет, для которого я хочу получить сумму.

В нашем примере я сделал это с ячейками D2 и D3, где D2 имеет желтый цвет, а D3 — оранжевый.

Теперь я могу использовать приведенную ниже формулу в этих ячейках:

Приведенная выше формула принимает два аргумента:

  • Диапазон ячеек с цветом, который я хочу добавить, который содержит цвет (чтобы формула могла выбрать индекс цвета и использовать его в качестве условия для добавления значений)

Обратите внимание, что формула является динамической и автоматически обновляется, если вы вносите какие-либо изменения в набор данных (например, изменяете любое значение или применяете/удаляете цвет из некоторых ячеек). А если вы заметили, что формула не обновляется, нажмите клавишу F9, и она обновится.

Поскольку мы использовали код VBA в рабочей книге, ее необходимо сохранить как рабочую книгу с поддержкой макросов (с расширением .XLSM).

Совет для профессионалов. Если вам приходится часто добавлять ячейки на основе цвета их фона, я рекомендую вам скопировать и вставить этот код VBA для пользовательской формулы в личную книгу макросов. Таким образом, это будет доступно во всех ваших книгах в вашей системе.

Суммировать ячейки по цвету с помощью GET.CELL

Последний метод, который я хочу вам показать, включает скрытую формулу Excel (о которой мало кто знает).

В этом методе используется функция GET.CELL, которая может получить значение индекса цвета цветных ячеек.

И как только у нас будет значение индекса цвета для каждой ячейки, мы можем использовать простую формулу суммы, если, чтобы получить только сумму ячеек с определенным цветом в ней.

Это не так элегантно, как пользовательская функция VBA, о которой я рассказывал ранее, но если вы не хотите использовать VBA, то этот способ может вам подойти.

GET.CELL — это старая функция Macro 4, которая была сохранена в Excel из соображений совместимости, но вы не найдете подробностей о ней (поскольку она редко используется).

Ниже у меня есть набор данных с цветными ячейками, которые я хочу суммировать.

Чтобы этот метод работал, нам сначала нужно создать именованный диапазон, который будет использовать функцию GET.CELL для присвоения значения цвета цветной ячейки.

Вот как это сделать:

  1. Перейдите на вкладку "Формулы" на ленте.
  2. В группе "Определенные имена" нажмите "Диспетчер имен".

  1. В диалоговом окне «Новое имя» введите Имя — SumColor.

  1. В поле Относится к введите следующую формулу: =GET.CELL(38,$B2)

  1. Нажмите "ОК".
  2. Закройте диалоговое окно "Диспетчер имен".

Вышеуказанные шаги создали именованный диапазон, который теперь можно использовать в книге.

Примечание: функция GET.CELL принимает два аргумента, первый из которых – это число, сообщающее функции, какая информация нам нужна, а второй – ссылка на саму ячейку. В этом случае у меня есть 38 в качестве первого аргумента, который даст нам значение индекса цвета указанной ячейки.

Теперь второй шаг — получить значения индекса цвета для всех цветов в столбце B.

Ниже приведены шаги для этого:

  1. В ячейке C1 введите заголовок — Индекс цвета (или как вы его назовете)

  1. В ячейке C2 введите следующую формулу: =SumColor

  1. Примените одну и ту же формулу ко всем ячейкам в столбце C (вы можете использовать маркер заполнения или просто скопировать и вставить ячейку C2).

Вышеуказанные шаги дадут вам значение, которое будет представлять индекс цвета ячейки в столбце B (ячейка слева).

SumColor — это именованный диапазон, который мы создали, и он использует функцию GET.CELL для получения значения индекса цвета ячейки слева. Вы можете использовать эту формулу в любом столбце, но она всегда должна начинаться со второй ячейки в этом столбце. Например, вместо столбца C вы можете иметь это в столбце H или J, но из вторых ячеек в этих столбцах.

Теперь, когда у нас есть уникальный номер для каждого цвета, мы можем использовать его, чтобы получить СУММУ ячеек на основе их цвета.

Ниже приведены шаги для этого:

  1. В ячейках E2 и E3 укажите цвет ячеек, для которых вы хотите получить сумму. В моем случае у меня желтый цвет в ячейке E2 и оранжевый в ячейке E3.

  1. В ячейке F2 введите следующую формулу: =СУММЕСЛИ(C2:C15;СуммаЦветов,B2:B15)

  1. Скопируйте ячейку и вставьте в ячейку F3 (при этом также можно будет скопировать формулу и изменить ссылки).

Вышеуказанные шаги дадут вам сумму всех ячеек, имеющих цвет в соседнем столбце продаж F.

Мы использовали формулу СУММЕСЛИ, которая складывает все значения в столбце B, если индекс цвета ячейки слева (в столбце F) такой же, как и в столбце C.

Несмотря на то, что это определенно немного более длительный способ суммирования ячеек по цвету (по сравнению с ПРОМЕЖУТОЧНЫМИ ИТОГОМИ или VBA), он выполняет свою работу, и вам нужно выполнить эту настройку только один раз.

Обратите внимание: несмотря на то, что формула является динамической, в случае внесения каких-либо изменений (например, изменения цвета ячеек в столбце B или удаления из него цвета) изменение может не сразу отразиться в формуле СУММЕСЛИ. Простой обходной путь для этого — перейти в ячейку с формулой, нажать клавишу F2, чтобы попасть в аэропорт, а затем нажать клавишу ввода. Это заставит формулу пересчитаться, и вы получите обновленный результат.

Итак, это три метода, которые вы можете использовать для суммирования по цветам в Excel. Хотя ПРОМЕЖУТОЧНЫЙ ИТОГ довольно прост и понятен, лично мне больше нравится метод VBA.

Читайте также: