Как использовать SUMIFS между двумя датами с помощью VBA в Microsoft Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

В этой статье мы узнаем, как использовать СУММЕСЛИ между двумя датами с помощью VBA в Microsoft Excel.

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

Функция СУММЕСЛИМН

Функция СУММЕСЛИМН возвращает сумму значений, соответствующих выбранным критериям. В этом случае критерии и диапазон критериев — это если дата находится между заданной датой.

Синтаксис:

=SUMIFS(Sum_range

, range ,»>=» & date1, range, «⇐» & date2)*

Sum_range: диапазон, в котором требуется сумма range: набор дат &: оператор, используемый для объединения другого оператора.

ПРИМЕЧАНИЕ:

Есть 3 способа указать значение даты в качестве аргумента формулы. . Введите дату напрямую, это просто, но приведет к ошибке, если формат не распознается Excel.

Используйте функцию ДАТА, например ДАТА (гггг, мм, дд), здесь гггг, мм, дд — числовые значения года, месяца и даты.
В-третьих, просто введите значение даты в другую ячейку и выберите его, используя ссылку на ячейку, как описано в примере ниже.

Давайте разберемся с этой функцией на примере.

Здесь нам нужно найти сумму TotalPrice, если дата находится между 20.02.2019 (после) и 9.07.2019 (до).

Итак, мы воспользуемся формулой, чтобы получить сумму

=SUMIFS (F2:F10 , A2:A10 , «>=» & I3, A2:A10 , «⇐» & J3)

F2: F10: Sum_range A2: A10: диапазон, в котором применяется условие «> =» & I3: больше, чем значение даты в I3 (20.02.2019).

«⇐» & J3: меньше значения даты в J3. (9.07.2019).

Используйте формулу, указанную выше, и нажмите Enter.

Как видите, формула возвращает 487,4, итоговую цену между датами.

Для настройки просто измените даты в ячейках I3 и J3 и получите результаты с формулой.

Пример VBA:

Все это может сбивать с толку. Давайте разберемся, как пользоваться функцией, на примере. Здесь у нас есть отчет о продажах для продавца, региона и продукта за 2012–2014 гг.

Мы хотим узнать цифру продаж, которая соответствует следующим условиям между двумя датами

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

Нажмите CTRL + F3, чтобы открыть окно диспетчера имен (списки уже созданы)

Используя функцию «Вставить имена» на вкладке «Формулы», мы получим в ячейках весь список диапазонов определенных имен.

Нажмите «Вставить список»

Чтобы запустить редактор VB, нам нужно выполнить следующие шаги. Щелкните вкладку «Разработчик». В группе «Код» выберите Visual Basic

Нажмите «Вставить», а затем «Модуль»

Это создаст новый модуль.

Введите следующий код в модуль

Sub Sumifs2Dates()

mysalesman = [H3]

myregion = [H4]

myproduct = [H5]

stdate = [H6]

EndDate = [H7]



tsales = Application.WorksheetFunction.SumIfs([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct, [ndate], ">=" &stdate, [ndate], "<=" &EndDate)

[H8] = tsales

End Sub

Нажмите сочетание клавиш ALT + F8, чтобы открыть окно макроса, а затем выберите макрос.

Кроме того, вы можете нажать F5, чтобы запустить код на экране VBA.

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

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

Вот все заметки с использованием СУММЕСЛИ между двумя датами с использованием функции в Excel

Примечания:

Формула работает только с числами.
Формула работает только тогда, когда в поисковой таблице нет дубликатов. Функция СУММПРОИЗВ рассматривает нечисловые значения (например, текст abc) и значения ошибок (например, #NUM!, #NULL!) Как нулевые значения.
Функция СУММПРОИЗВ рассматривает логическое значение ИСТИНА как 1 и Ложь как 0.
Массив аргументов должен быть той же длины, что и функция.

Надеюсь, эта статья о том, как использовать СУММЕСЛИ между двумя датами с помощью VBA в Microsoft Excel, будет пояснительной. Дополнительные статьи о формулах СУММЕСЛИ и связанных формулах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

Статьи по теме:

link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel.

link: / summing-sum-if-date-is-between [SUM if date is between]: возвращает СУММУ значений между заданными датами или периодом в Excel.

link: / summing-sum-if-date-more-than-given-date [Сумма, если дата больше заданной даты]: * Возвращает СУММУ значений после заданной даты или периода в Excel.

link: / summing-2-way-to-sum-by-month-in-excel [2 способа суммирования по месяцам в Excel]: * Возвращает СУММУ значений за данный конкретный месяц в excel.

link: / summing-how-to-sum-multiple-columns-with-condition [Как суммировать несколько столбцов с условием]: возвращает СУММУ значений по нескольким столбцам, имеющим условие в excel `link: / tips-excel- wildcards [Как использовать подстановочные знаки в excel *] `: Подсчет ячеек, соответствующих фразам, используя подстановочные знаки в excel