Šī apmācība parādīs, kā izmantot Excel vidējo funkciju VBA.
Funkcija Excel AVERAGE tiek izmantota, lai aprēķinātu vidējo vērtību no darblapas diapazona šūnām, kurās ir vērtības. VBA tam piekļūst, izmantojot metodi WorkheetFunction.
VIDĒJĀ darblapas funkcija
Objektu WorksheetFunction var izmantot, lai izsauktu lielāko daļu Excel funkciju, kas ir pieejamas Excel dialoglodziņā Funkcijas ievietošana. Funkcija VIDĒJĀ ir viena no tām.
| 123 | Apakšpārbaudes funkcijaDiapazons ("D33") = Application.WorksheetFunction.Average ("D1: D32")Beigu apakš |
Funkcijā AVERAGE var būt līdz 30 argumentiem. Katram no argumentiem ir jāattiecas uz šūnu diapazonu.
Šis piemērs turpmāk parādīs šūnu B11 līdz N11 summas vidējo lielumu
| 123 | ApakšpārbaudeAverage ()Diapazons ("O11") = Application.WorksheetFunction.Average (Diapazons ("B11: N11"))Beigu apakš |
Tālāk sniegtajā piemērā tiks iegūta vidējā šūnu summa no B11 līdz N11 un šūnu summa no B12: N12. Ja neievadīsiet lietojumprogrammas objektu, tas tiks pieņemts.
| 123 | ApakšpārbaudeAverage ()Diapazons ("O11") = WorksheetFunction.Average (Diapazons ("B11: N11"), diapazons ("B12: N12"))Beigu apakš |
VIDĒJĀ rezultāta piešķiršana mainīgajam
Iespējams, vēlēsities izmantot formulas rezultātu citur kodā, nevis rakstīt to atpakaļ Excel diapazonā. Ja tas tā ir, rezultātu varat piešķirt mainīgajam, lai to izmantotu vēlāk savā kodā.
| 1234567 | Sub AssignAverage ()Dim rezultāts kā vesels skaitlis'Piešķiriet mainīgorezultāts = WorksheetFunction.Average (Diapazons ("A10: N10"))'Parādiet rezultātuMsgBox "Vidējais rādītājs šūnām šajā diapazonā ir" & rezultātsBeigu apakš |
VIDĒJAIS ar diapazona objektu
Objektam Range varat piešķirt šūnu grupu un pēc tam izmantot šo diapazona objektu ar DarblapaFunkcija objekts.
| 123456789 | ApakšpārbaudeAverageRange ()Dim rng As Range"piešķirt šūnu diapazonuIestatīt rng = Diapazons ("G2: G7")'izmantojiet diapazonu formulāDiapazons ("G8") = WorksheetFunction.Average (rng)'atlaidiet diapazona objektuSet rng = NekasBeigu apakš |
VIDĒJI vairāku diapazonu objekti
Līdzīgi jūs varat aprēķināt vidējo šūnu skaitu no vairākiem diapazona objektiem.
| 123456789101112 | ApakšpārbaudeAverageMultipleRanges ()Dim rngA As RangeDim rngB kā diapazons"piešķirt šūnu diapazonuIestatīt rngA = Diapazons ("D2: D10")Iestatīt rngB = diapazons ("E2: E10")'izmantojiet diapazonu formulāDiapazons ("E11") = WorksheetFunction.Average (rngA, rngB)'atlaidiet diapazona objektuIestatīt rngA = NekasSet rngB = NekasBeigu apakš |
Izmantojot AVERAGEA
Funkcija AVERAGEA atšķiras no funkcijas AVERAGE, jo tā veido vidējo vērtību no visām diapazona šūnām, pat ja vienā no šūnām ir teksts - tā aizstāj tekstu ar nulli un iekļauj to vidējā aprēķinā. Funkcija VIDĒJĀ ignorē šo šūnu un neņem to vērā aprēķinos.
| 123 | ApakšpārbaudeAverage (()Diapazons ("B8) = Application.WorksheetFunction.AverageA (Diapazons (" A10: A11 "))Beigu apakš |
Tālāk redzamajā piemērā funkcija AVERAGE atgriež funkciju AVERAGEA atšķirīgu vērtību, kad aprēķins tiek izmantots šūnās A10 līdz A11
Atbilde uz AVERAGEA formulu ir zemāka par AVERAGE formulu, jo tā aizstāj A11 tekstu ar nulli, un tāpēc vidējās vērtības pārsniedz 13 vērtības, nevis 12 vērtības, ar kurām AVERAGE aprēķina.
Izmantojot AVERAGEIF
Funkcija AVERAGEIF ļauj vidēji aprēķināt šūnu diapazona summu, kas atbilst noteiktiem kritērijiem.
| 123 | Zem vidējā If ()Diapazons ("F31") = WorksheetFunction.AverageIf (Diapazons ("F5: F30"), "Ietaupījumi", diapazons ("G5: G30"))Beigu apakš |
Iepriekš minētā procedūra vidēji aprēķinās tikai šūnas diapazonā G5: G30, kur atbilstošajā šūnā F slejā ir vārds “Ietaupījumi”. Izmantotajiem kritērijiem ir jābūt pēdiņās.
WorksheetFunction trūkumi
Kad izmantojat DarblapaFunkcija lai aprēķinātu vidējās vērtības jūsu darblapas diapazonā, tiek atgriezta statiska vērtība, 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 TestAverage ir izveidojusi vidējo vērtību B11: M11 un ievieto atbildi N11. Kā redzat formulas joslā, šis rezultāts ir skaitlis, nevis formula.
Ja kāda no vērtībām mainās diapazonā (B11: M11), rezultāti N11 mainīsies NĒ mainīt.
Tā vietā, lai izmantotu DarblapaFunkcija.Vidējais, varat izmantot VBA, lai šūnā lietotu AVERAGE funkciju, izmantojot Formula vai FormulaR1C1 metodes.
Izmantojot formulas metodi
Formulas metode ļauj īpaši norādīt uz šūnu diapazonu, piemēram: B11: M11, kā parādīts zemāk.
| 123 | ApakšpārbaudeAverageFormula ()Diapazons ("N11"). Formula = "= Vidējais (B11: M11)"Beigu apakš |
Izmantojot FormulaR1C1 metodi
FomulaR1C1 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 | ApakšpārbaudeAverageFormula ()Diapazons ("N11"). Formula = "= Vidējais (RC [-12]: RC [-1])"Beigu apakš |
Tomēr, lai padarītu formulu elastīgāku, mēs varētu grozīt kodu šādi:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Skaits (R [-11] C: R [-1] C)"Beigu apakš |
Lai kur jūs atrastos darblapā, formula pēc tam vidēji aprēķinās vērtības 12 šūnās pa kreisi no tās un ievietos atbildi savā ActiveCell. Funkcijas AVERAGE diapazons ir jāatsaucas, izmantojot rindu (R) un kolonnu (C) sintaksi.
Abas šīs metodes ļauj VBA izmantot dinamiskās Excel formulas.
Tagad vērtības N11 vietā būs formula.
