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

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

Разберемся на примере:

  • У нас есть отчет о продажах для продавца, региона и продукта за 2012-2014 гг.

img1

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

img2

  • По всем условиям; нам нужно создать диапазоны имен. Чтобы создать динамический диапазон имен для продавца:

Нажмите сочетание клавиш CTRL + F3> нажмите «Создать» и введите формулу как = OFFSET (Sheet1! $ B $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

img3

Аналогичным образом создайте именованные диапазоны для других условий. * Нажмите CTRL + F3, чтобы открыть окно диспетчера имен (см. Списки, которые уже созданы)

img4

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

img5

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

img6

nDate * = OFFSET (Sheet1! $ A $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

nProduct * = OFFSET (Sheet1! $ D $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

nRegion * = OFFSET (Sheet1! $ C $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

nSales * = OFFSET (Sheet1! $ E $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

nПродавец * = OFFSET (Sheet1! $ B $ 1,1,0, COUNTA (Sheet1! $ A: $ A) -1)

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

img7

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

img8

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

  • Введите следующий код в подпрограмму модуля VBASumifs () mysalesman = [H3] myregion = [H4] myproduct = [H5]

tsales = Application.WorksheetFunction.SumIfs ([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct)

[H6] = tsales End Sub

img9

Нажмите «Вставить ленту»> «Фигуры»> «Нарисуйте картинку». Щелкните правой кнопкой мыши на картинке и выберите «Назначить макрос»

img10

img11

  • После назначения макроса; нажмите кнопку «Обновить продажи», и мы получим результат в ячейке H6

img12

Примечание: — После изменения имени продавца, региона и продукта вы должны нажать кнопку «Обновить продажи».

Чтобы узнать Продажи, соответствующие условиям между 2 датами; Ниже приведен снимок критериев:

img13

Мы будем использовать следующий код:

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

img14

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

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

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

img15

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