Šī apmācība parādīs, kā VBA izmantot Excel SUMIF un SUMIFS funkcijas
VBA nav SUMIF vai SUMIFS funkciju ekvivalenta, ko varat izmantot - lietotājam ir jāizmanto VBA iebūvētās Excel funkcijas, izmantojot WorkSheetFunction objekts.
SUMIF darblapas funkcija
Objektu WorksheetFunction var izmantot, lai izsauktu lielāko daļu Excel funkciju, kas ir pieejamas Excel dialoglodziņā Funkcijas ievietošana. Funkcija SUMIF ir viena no tām.
| 123 | Sub TestSumIf ()Diapazons ("D10") = Application.WorksheetFunction.SumIf (Diapazons ("C2: C9"), 150, Diapazons ("D2: D9"))Beigu apakš |
Iepriekš minētā procedūra saskaitīs šūnas diapazonā (D2: D9) tikai tad, ja atbilstošā šūna kolonnā C = 150.
SUMIF rezultāta piešķiršana mainīgajam
Iespējams, vēlēsities izmantot formulas rezultātu citur kodā, nevis rakstīt to tieši atpakaļ un Excel diapazonā. Ja tas tā ir, rezultātu varat piešķirt mainīgajam, lai to izmantotu vēlāk savā kodā.
| 1234567 | Sub AssignSumIfVariable ()Iegūstiet dubultu rezultātu'Piešķiriet mainīgorezultāts = darblapas funkcija.SumIf (diapazons ("C2: C9"), 150, diapazons ("D2: D9"))'Parādiet rezultātuMsgBox "Kopējais rezultāts, kas atbilst 150 pārdošanas kodam, ir" & resultBeigu apakš |
Izmantojot SUMIFS
Funkcija SUMIFS ir līdzīga SUMIF darblapas funkcijai, taču tā ļauj pārbaudīt vairāk nekā vienu kritēriju. Tālāk redzamajā piemērā mēs cenšamies saskaitīt pārdošanas cenu, ja pārdošanas kods ir 150 UN pašizmaksa ir lielāka par 2. Ņemiet vērā, ka šajā formulā pievienojamo šūnu diapazons ir priekšā kritērijiem, bet SUMIF funkcijā tas ir aiz muguras.
| 123 | Sub MultipleSumIfs ()Diapazons ("D10") = WorksheetFunction.SumIfs (Diapazons ("D2: D9"), Diapazons ("C2: C9"), 150, Diapazons ("E2: E9"), "> 2")Beigu apakš |
SUMIF izmantošana ar diapazona objektu
Objektam Range varat piešķirt šūnu grupu un pēc tam izmantot šo diapazona objektu ar DarblapaFunkcija objekts.
| 123456789101112 | ApakštestsSumIFRange ()Dim rngCriteria As RangeDim rngSum kā diapazons"piešķirt šūnu diapazonuIestatīt rngCriteria = Diapazons ("C2: C9")Iestatīt rngSum = Diapazons ("D2: D9")'izmantojiet diapazonu formulāDiapazons ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'atlaidiet diapazona objektusIestatīt rngCriteria = NekasSet rngSum = NekasBeigu apakš |
SUMIFS izmantošana vairāku diapazonu objektos
Līdzīgi jūs varat izmantot SUMIFS vairākiem diapazona objektiem.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 kā diapazonsDim rngCriteria2 kā diapazonsDim rngSum kā diapazons"piešķirt šūnu diapazonuIestatīt rngCriteria1 = Diapazons ("C2: C9")Iestatīt rngCriteria2 = Diapazons ("E2: E10")Iestatīt rngSum = Diapazons ("D2: D10")'izmantojiet formulas diapazonusDiapazons ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'atlaidiet diapazona objektuSet rngCriteria1 = NekasSet rngCriteria2 = NekasSet rngSum = NekasBeigu apakš |
Ņemiet vērā, ka, tā kā izmantojat lielākas par zīmi, kritērijiem, kas lielāki par 2, jābūt iekavās.
SUMIF formula
Kad izmantojat DarblapaFunkcija.SUMIF lai pievienotu summu darblapas diapazonam, tiek atgriezta statiska summa, nevis elastīga formula. Tas nozīmē, ka, mainoties skaitļiem programmā Excel, vērtība, kuru ir atgriezusi DarblapaFunkcija nemainīsies.
Iepriekš minētajā piemērā procedūra ir pievienojusi diapazonu (D2: D9), kur C slejā SaleCode ir 150, un rezultāts tika ievietots D10. Kā redzat formulas joslā, šis rezultāts ir skaitlis, nevis formula.
Ja kāda no vērtībām mainās diapazonā (D2: D9) vai diapazonā (C2: D9), rezultāts D10 NĒ mainīt.
Tā vietā, lai izmantotu WorksheetFunction.SumIf, varat izmantot VBA, lai šūnai lietotu SUMIF funkciju, izmantojot Formula vai FormulaR1C1 metodes.
Formulas metode
Formulas metode ļauj īpaši norādīt uz šūnu diapazonu, piemēram: D2: D10, kā parādīts zemāk.
| 123 | Sub TestSumIf ()Diapazons ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Beigu apakš |
FormulaR1C1 metode
FormulaR1C1 metode ir elastīgāka, jo tā neierobežo jūs ar noteiktu šūnu diapazonu. Tālāk sniegtais piemērs sniegs mums tādu pašu atbildi kā iepriekš.
| 123 | Sub TestSumIf ()Diapazons ("D10"). FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Beigu apakš |
Tomēr, lai padarītu formulu elastīgāku, mēs varētu grozīt kodu šādi:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Beigu apakš |
Lai kur jūs atrastos darblapā, formula pēc tam saskaitīs šūnas, kas atbilst kritērijiem tieši virs tās, un ievietos atbildi savā ActiveCell. Funkcijas SUMIF diapazons ir jāatsaucas, izmantojot rindu (R) un kolonnas (C) sintaksi.
Abas šīs metodes ļauj VBA izmantot dinamiskās Excel formulas.
Tagad vērtības D10 vietā būs formula.
