Този урок ще ви покаже как да използвате функциите SUMIF и SUMIFS на Excel във VBA
VBA няма еквивалент на функциите SUMIF или SUMIFS, които можете да използвате - потребителят трябва да използва вградените функции на Excel във VBA, като използва WorkSheetFunction обект.
SUMIF Работен лист Функция
Обектът WorksheetFunction може да се използва за извикване на повечето от функциите на Excel, които са налични в диалоговия прозорец Вмъкване на функция в Excel. Функцията SUMIF е една от тях.
| 123 | Sub TestSumIf ()Range ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))End Sub |
Горната процедура ще добави клетките в диапазон (D2: D9) само ако съответната клетка в колона C = 150.
Присвояване на резултат SUMIF на променлива
Може да искате да използвате резултата от формулата си другаде в кода, вместо да го пишете директно обратно в и Excel диапазон. Ако случаят е такъв, можете да присвоите резултата на променлива, която да използвате по -късно във вашия код.
| 1234567 | Sub AssignSumIfVariable ()Затъмнете резултата като двоен'Задайте променливатаresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Покажете резултатаMsgBox "Общият резултат, съответстващ на 150 кода за продажби, е" & резултатEnd Sub |
Използване на SUMIFS
Функцията SUMIFS е подобна на функцията SUMIF WorksheetFunction, но ви позволява да проверите за повече от един критерий. В примера по -долу се стремим да съберем продажната цена, ако кодът за продажба е 150 И себестойността е по -голяма от 2. Обърнете внимание, че в тази формула диапазонът от клетки за добавяне е пред критериите, докато във функцията SUMIF, тя е отзад.
| 123 | Sub MultipleSumIfs ()Диапазон ("D10") = Функция на работен лист.End Sub |
Използване на SUMIF с Range Object
Можете да присвоите група клетки на обекта Range и след това да използвате този Range обект с Работен лист Функция обект.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum като Range'задайте диапазона от клеткиЗадайте rngCriteria = Range ("C2: C9")Задайте rngSum = Диапазон ("D2: D9")'използвайте диапазона във формулатаДиапазон ("D10") = Работен лист Function.SumIf (rngCriteria, 150, rngSum)'освободете обектите на диапазонаЗадайте rngCriteria = НищоЗадайте rngSum = НищоEnd Sub |
Използване на SUMIFS върху обекти с множество обхвати
По същия начин можете да използвате SUMIFS на множество Range обекти.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 като диапазонDim rngSum като Range'задайте диапазона от клеткиЗадайте rngCriteria1 = Обхват ("C2: C9")Задайте rngCriteria2 = Обхват ("E2: E10")Задайте rngSum = Диапазон ("D2: D10")'използвайте диапазоните във формулатаRange ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'освободете обекта rangeЗадайте rngCriteria1 = НищоЗадайте rngCriteria2 = НищоЗадайте rngSum = НищоEnd Sub |
Забележете, че тъй като използвате знак по -голям от, критериите по -големи от 2 трябва да бъдат в скоби.
Формула SUMIF
Когато използвате Работен лист Функция.SUMIF за да добавите сума към диапазон във вашия работен лист, се връща статична сума, а не гъвкава формула. Това означава, че когато вашите цифри в Excel се променят, стойността, която е била върната от Работен лист Функция няма да се промени.
В горния пример процедурата е добавила Range (D2: D9), където SaleCode е равен на 150 в колона C, а резултатът е поставен в D10. Както можете да видите в лентата с формули, този резултат е цифра, а не формула.
Ако някоя от стойностите се промени в Обхват (D2: D9) или Обхват (C2: D9), резултатът в D10 ще НЕ промяна.
Вместо да използвате Работен листFunction.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 вместо стойност.
