Цей підручник покаже вам, як користуватися функціями Excel SUMIF та SUMIFS у VBA
VBA не має еквівалента функцій SUMIF або SUMIFS, які можна використовувати - користувач повинен використовувати вбудовані функції Excel у VBA за допомогою Робочий лист об'єкт.
Функція робочого аркуша SUMIF
Об'єкт WorksheetFunction можна використовувати для виклику більшості функцій Excel, доступних у діалоговому вікні Вставка функції в Excel. Функція SUMIF - одна з них.
| 123 | Sub TestSumIf ()Діапазон ("D10") = Application.WorksheetFunction.SumIf (Діапазон ("C2: C9"), 150, Діапазон ("D2: D9"))End Sub |
Наведена вище процедура додасть клітинки в діапазоні (D2: D9), лише якщо відповідна клітинка у стовпці C = 150.
Присвоєння результату SUMIF змінній
Можливо, ви захочете використати результат своєї формули в іншому місці коду, а не писати його безпосередньо в діапазон Excel. Якщо це так, ви можете призначити результат змінній для використання пізніше у вашому коді.
| 1234567 | Sub AssignSumIfVariable ()Затемнити результат як подвійний'Призначте зміннуresult = WorksheetFunction.SumIf (Діапазон ("C2: C9"), 150, Діапазон ("D2: D9"))'Покажіть результатMsgBox "Загальна сума результату, що відповідає 150 коду продажів, - це" & результатEnd Sub |
Використання SUMIFS
Функція SUMIFS схожа на функцію SUMIF WorksheetFunction, але дозволяє перевірити наявність декількох критеріїв. У наведеному нижче прикладі ми прагнемо додати ціну продажу, якщо код продажу 150 І собівартість більша за 2. Зверніть увагу, що в цій формулі діапазон клітинок для додавання стоїть перед критеріями, тоді як у функції SUMIF вона знаходиться позаду.
| 123 | Sub MultipleSumIfs ()Діапазон ("D10") = Функція робочого аркуша.SumIfs (Діапазон ("D2: D9"), Діапазон ("C2: C9"), 150, Діапазон ("E2: E9"), "> 2")End Sub |
Використання SUMIF з об'єктом діапазону
Ви можете призначити групу клітинок об'єкту Range, а потім використовувати цей об'єкт Range разом із Робочий лист Функція об'єкт.
| 123456789101112 | Sub TestSumIFRange ()Зменшити критерії як діапазонЗменшити rngSum як діапазон'призначити діапазон клітинокВстановити rngCriteria = Діапазон ("C2: C9")Встановити rngSum = Діапазон ("D2: D9")'використовуйте діапазон у формуліДіапазон ("D10") = Функція робочого аркуша.SumIf (rngCriteria, 150, rngSum)'звільнення об'єктів діапазонуВстановити rngCriteria = НічогоВстановити rngSum = НічогоEnd Sub |
Використання SUMIFS на об'єктах з кількома діапазонами
Аналогічно, ви можете використовувати SUMIFS для кількох об'єктів діапазону.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeЗменшити rngCriteria2 як діапазонЗменшити rngSum як діапазон'призначити діапазон клітинокВстановити rngCriteria1 = Діапазон ("C2: C9")Встановити rngCriteria2 = Діапазон ("E2: E10")Встановити rngSum = Діапазон ("D2: D10")'використовуйте діапазони у формуліДіапазон ("D10") = Робочий лист Функція.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'випустіть об'єкт діапазонуВстановити rngCriteria1 = НічогоВстановити rngCriteria2 = НічогоВстановити rngSum = НічогоEnd Sub |
Зверніть увагу, що оскільки ви використовуєте знак більший за, критерії, більші за 2, повинні бути в дужках.
Формула SUMIF
Коли ви використовуєте Робочий лист Функція.SUMIF щоб додати суму до діапазону на вашому аркуші, повертається статична сума, а не гнучка формула. Це означає, що коли ваші цифри в Excel змінюються, значення, яке було повернуто Робочий лист Функція не зміниться.
У наведеному вище прикладі процедура додала Діапазон (D2: D9), де SaleCode дорівнює 150 у стовпці C, а результат був поміщений у D10. Як ви можете бачити на панелі формул, цей результат є цифрою, а не формулою.
Якщо якесь із значень змінюється в діапазоні (D2: D9) або в діапазоні (C2: D9), результат у D10 буде НІ зміна.
Замість того, щоб використовувати WorksheetFunction.SumIf, ви можете використовувати VBA, щоб застосувати функцію SUMIF до клітинки за допомогою Формула або Формула R1C1 методи.
Метод формули
Метод формули дозволяє вказати конкретно на діапазон клітинок, наприклад: D2: D10, як показано нижче.
| 123 | Sub TestSumIf ()Діапазон ("D10"). Формула R1C1 = "= SUMIF (C2: C9,150, D2: D9)"End Sub |
Формула R1C1 Метод
Метод FormulaR1C1 є більш гнучким, оскільки не обмежує вас певним діапазоном клітинок. Наведений нижче приклад дасть нам таку ж відповідь, як і наведений вище.
| 123 | Sub TestSumIf ()Діапазон ("D10"). Формула R1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "End Sub |
Однак, щоб зробити формулу більш гнучкою, ми могли б змінити код так:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"End Sub |
Де б ви не були на своєму робочому аркуші, формула додасть клітинки, які відповідають критеріям, безпосередньо над ним, і розмістить відповідь у вашій ActiveCell. Діапазон всередині функції SUMIF слід посилати за допомогою синтаксису рядка (R) та стовпця (C).
Обидва ці методи дозволяють використовувати формули динамічного Excel у VBA.
Тепер у D10 замість значення буде формула.
