Acest tutorial vă va arăta cum să utilizați funcțiile Excel SUMIF și SUMIFS în VBA
VBA nu are un echivalent al funcțiilor SUMIF sau SUMIFS pe care le puteți utiliza - un utilizator trebuie să utilizeze funcțiile Excel încorporate în VBA folosind WorkSheetFunction obiect.
SUMIF WorksheetFunction
Obiectul WorksheetFunction poate fi utilizat pentru a apela majoritatea funcțiilor Excel disponibile în caseta de dialog Insert Function din Excel. Funcția SUMIF este una dintre ele.
| 123 | SubTestSumIf ()Range ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))Sfârșitul Sub |
Procedura de mai sus va adăuga celulele din Range (D2: D9) numai dacă celula corespunzătoare din coloana C = 150.
Atribuirea unui rezultat SUMIF unei variabile
Poate doriți să utilizați rezultatul formulei dvs. în altă parte în cod, mai degrabă decât să o scrieți direct înapoi și în intervalul Excel. Dacă acesta este cazul, puteți atribui rezultatul unei variabile pentru a o utiliza ulterior în cod.
| 1234567 | Sub AssignSumIfVariable ()Reduceți rezultatul ca Dublu'Atribuiți variabilaresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Arată rezultatulMsgBox "Totalul rezultatului care corespunde celor 150 de coduri de vânzare este" & rezultatSfârșitul Sub |
Folosind SUMIFS
Funcția SUMIFS este similară cu SUMIF WorksheetFunction, dar vă permite să verificați mai multe criterii. În exemplul de mai jos, căutăm să adăugăm prețul de vânzare dacă codul de vânzare este 150 ȘI Prețul de cost este mai mare de 2. Observați că, în această formulă, intervalul de celule de adăugat este în fața criteriilor, întrucât în funcția SUMIF, se află în spatele.
| 123 | Sub MultipleSumIfs ()Range ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")Sfârșitul Sub |
Utilizarea SUMIF cu un obiect Range
Puteți atribui un grup de celule obiectului Range și apoi utilizați acel obiect Range cu Foaie de lucruFuncție obiect.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum ca Range'atribuiți gama de celuleSetați rngCriteria = Range ("C2: C9")Setați rngSum = Range ("D2: D9")'utilizați intervalul din formulăRange ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'eliberați obiectele de gamăSet rngCriteria = NimicSet rngSum = NimicSfârșitul Sub |
Folosind SUMIFS pe obiecte cu mai multe domenii
În mod similar, puteți utiliza SUMIFS pe mai multe obiecte Range.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 ca intervalDim rngCriteria2 ca RangeDim rngSum ca Range'atribuiți gama de celuleSetați rngCriteria1 = Range ("C2: C9")Setați rngCriteria2 = Range ("E2: E10")Setați rngSum = Range ("D2: D10")'utilizați intervalele din formulăRange ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'eliberați obiectul de gamăSet rngCriteria1 = NimicSetați rngCriteria2 = NimicSet rngSum = NimicSfârșitul Sub |
Observați că, deoarece utilizați un semn mai mare decât, criteriile mai mari de 2 trebuie să fie în paranteză.
Formula SUMIF
Când utilizați WorksheetFunction.SUMIF pentru a adăuga o sumă într-un interval din foaia de lucru, se returnează o sumă statică, nu o formulă flexibilă. Aceasta înseamnă că atunci când cifrele dvs. din Excel se schimbă, valoarea care a fost returnată de Foaie de lucruFuncție nu se va schimba.
În exemplul de mai sus, procedura a adăugat Range (D2: D9) unde SaleCode este egal cu 150 în coloana C, iar rezultatul a fost introdus în D10. După cum puteți vedea în bara de formule, acest rezultat este o cifră și nu o formulă.
Dacă oricare dintre valori se modifică fie în Range (D2: D9), fie în Range (C2: D9), rezultatul în D10 va fi NU Schimbare.
În loc să utilizați WorksheetFunction.SumIf, puteți utiliza VBA pentru a aplica o funcție SUMIF unei celule folosind Formulă sau FormulaR1C1 metode.
Metoda Formula
Metoda formulă vă permite să indicați în mod specific către o gamă de celule, de exemplu: D2: D10 așa cum se arată mai jos.
| 123 | SubTestSumIf ()Interval ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Sfârșitul Sub |
Metoda FormulaR1C1
Metoda FormulaR1C1 este mai flexibilă în sensul că nu vă restrânge la un interval set de celule. Exemplul de mai jos ne va oferi același răspuns ca cel de mai sus.
| 123 | SubTestSumIf ()Interval ("D10"). FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Sfârșitul Sub |
Cu toate acestea, pentru a face formula mai flexibilă, am putea modifica codul astfel:
| 123 | SubTestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Sfârșitul Sub |
Oriunde vă aflați în foaia dvs. de lucru, formula va adăuga apoi celulele care îndeplinesc criteriile direct deasupra acesteia și vor plasa răspunsul în ActiveCell. Gama din interiorul funcției SUMIF trebuie menționată folosind sintaxa Rând (R) și Coloană (C).
Ambele metode vă permit să utilizați formule dinamice Excel în cadrul VBA.
Acum va exista o formulă în D10 în loc de o valoare.
