Ten samouczek pokaże Ci, jak korzystać z funkcji średniej Excela w VBA.
Funkcja Excel AVERAGE służy do obliczania średniej z komórek zakresu w arkuszu, które zawierają wartości. W VBA dostęp do niego uzyskuje się za pomocą metody WorksheetFunction.
ŚREDNIA Arkusz Funkcja
Obiekt WorksheetFunction może służyć do wywoływania większości funkcji programu Excel dostępnych w oknie dialogowym Wstaw funkcję w programie Excel. Jedną z nich jest funkcja ŚREDNIA.
| 123 | Funkcja podrzędnaZakres("D33") = Application.WorksheetFunction.Average("D1:D32")Napis końcowy |
Możesz mieć do 30 argumentów w funkcji ŚREDNIA. Każdy z argumentów musi odnosić się do zakresu komórek.
Poniższy przykład wygeneruje średnią sumy komórek od B11 do N11
| 123 | Podrzędna średnia testowa()Zakres("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))Napis końcowy |
Poniższy przykład wygeneruje średnią sumę komórek w B11 do N11 i sumę komórek w B12:N12. Jeśli nie wpiszesz obiektu Application, zostanie to przyjęte.
| 123 | Podrzędna średnia testowa()Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12"))Napis końcowy |
Przypisywanie ŚREDNIEGO wyniku do zmiennej
Możesz chcieć użyć wyniku formuły w innym miejscu w kodzie, zamiast zapisywać go bezpośrednio z powrotem w zakresie programu Excel. W takim przypadku możesz przypisać wynik do zmiennej, która będzie używana później w kodzie.
| 1234567 | Przypisz podrzędną średnią ()Dim wynik As Integer'Przypisz zmiennąwynik = WorksheetFunction.Average(Range("A10:N10"))'Pokaż wynikMsgBox "Średnia dla komórek w tym zakresie to " i wynikNapis końcowy |
ŚREDNIA z obiektem zakresu
Możesz przypisać grupę komórek do obiektu Range, a następnie użyć tego obiektu Range z Funkcja arkusza roboczego obiekt.
| 123456789 | Podtestowy zakres średniej()Dim rng As Range'przypisz zakres komórekUstaw rng = Zakres("G2:G7")'użyj zakresu we wzorzeZakres("G8") = WorksheetFunction.Average(rng)'zwolnij obiekt zakresuUstaw rng = NicNapis końcowy |
ŚREDNIA Obiekty o wielu zakresach
Podobnie możesz obliczyć średnią komórek z wielu obiektów zakresu.
| 123456789101112 | PodtestAverageMultipleRanges()Dim rngA jako zakresDim rngB jako zakres'przypisz zakres komórekUstaw rngA = Zakres("D2:D10")Ustaw rngB = Zakres("E2:E10")'użyj zakresu we wzorzeZakres("E11") = WorksheetFunction.Average(rngA, rngB)'zwolnij obiekt zakresuUstaw rngA = NicUstaw rngB = NicNapis końcowy |
Korzystanie ŚREDNIA
Funkcja ŚREDNIA różni się od funkcji ŚREDNIA tym, że tworzy średnią ze wszystkich komórek w zakresie, nawet jeśli jedna z komórek zawiera tekst - zastępuje tekst zerem i uwzględnia to przy obliczaniu średniej. Funkcja ŚREDNIA zignoruje tę komórkę i nie uwzględni jej w obliczeniach.
| 123 | Podtest ŚredniaA()Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11"))Napis końcowy |
W poniższym przykładzie funkcja ŚREDNIA zwraca inną wartość funkcji ŚREDNIA, gdy obliczenia są używane w komórkach od A10 do A11
Odpowiedź dla formuły ŚREDNIA jest niższa niż formuła ŚREDNIA, ponieważ zastępuje tekst w A11 zerem, a zatem uśrednia 13 wartości, a nie 12 wartości, dla których obliczana jest ŚREDNIA.
Używam ŚREDNIA JEŻELI
Funkcja AVERAGEIF umożliwia uśrednienie sumy zakresu komórek spełniających określone kryteria.
| 123 | Sub średnia, jeśli ()Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Oszczędności", Range("G5:G30"))Napis końcowy |
Powyższa procedura uśrednia tylko komórki z zakresu G5:G30, w których odpowiadająca komórka w kolumnie F zawiera słowo „Oszczędności”. Kryteria, których używasz, muszą być w cudzysłowie.
Wady funkcji arkusza roboczego
Kiedy używasz Funkcja arkusza roboczego aby uśrednić wartości z zakresu w arkuszu, zwracana jest wartość statyczna, a nie elastyczna formuła. Oznacza to, że gdy zmienią się Twoje dane w Excelu, wartość zwrócona przez Funkcja arkusza roboczego nie zmieni się.
W powyższym przykładzie procedura TestAverage utworzyła średnią z B11:M11 i umieściła odpowiedź w N11. Jak widać na pasku formuły, ten wynik jest liczbą, a nie formułą.
Jeśli którakolwiek z wartości zmieni się w związku z tym w zakresie (B11:M11 ), wyniki w N11 będą NIE reszta.
Zamiast używać Arkusz Funkcja.Średnia, możesz użyć VBA, aby zastosować funkcję ŚREDNIA do komórki za pomocą Formuła lub FormułaR1C1 metody.
Korzystanie z metody formuły
Metoda formuł pozwala wskazać konkretny zakres komórek, np.: B11:M11, jak pokazano poniżej.
| 123 | PodtestuśredniaFormuła()Zakres("N11").Formuła = "=Średnia(B11:M11)"Napis końcowy |
Korzystanie z metody FormulaR1C1
Metoda FomulaR1C1 jest bardziej elastyczna, ponieważ nie ogranicza Cię do określonego zakresu komórek. Poniższy przykład da nam taką samą odpowiedź jak powyższy.
| 123 | PodtestuśredniaFormuła()Zakres("N11").Formuła = "=Średnia(RC[-12]:RC[-1])"Napis końcowy |
Aby jednak formuła była bardziej elastyczna, możemy zmienić kod tak, aby wyglądał tak:
| 123 | Podrzędna formuła liczby testów()ActiveCell.FormulaR1C1 = "=Liczba(R[-11]C:R[-1]C)"Napis końcowy |
Gdziekolwiek jesteś w arkuszu, formuła uśrednia wartości w 12 komórkach bezpośrednio po lewej stronie i umieści odpowiedź w ActiveCell. Do zakresu wewnątrz funkcji ŚREDNIA należy odwoływać się za pomocą składni Wiersz (R) i Kolumna (C).
Obie te metody umożliwiają korzystanie z formuł Dynamic Excel w VBA.
W N11 będzie teraz formuła zamiast wartości.
