Jak uśredniać według koloru w programie excel (przykład krok po kroku)

Często możesz chcieć uśrednić wartości w Excelu na podstawie ich koloru.

Załóżmy na przykład, że mamy następujący zestaw danych i chcemy obliczyć średnią wartość komórki na podstawie kolorów komórek:

Najłatwiej to zrobić, pisząc kod VBA w Excelu.

Może się to wydawać onieśmielające, jeśli nie znasz języka VBA, ale proces jest prosty, a poniższy przykład krok po kroku pokazuje dokładnie, jak to zrobić.

Krok 1: Wprowadź dane

Najpierw wprowadź wartości danych do Excela:

Krok 2: Pokaż kartę Deweloper w Excelu

Następnie musimy upewnić się, że zakładka Deweloper jest widoczna na górnej wstążce Excela.

Aby to zrobić, kliknij zakładkę Plik , następnie Opcje i Dostosuj Wstążkę .

W sekcji zatytułowanej Karty główne zaznacz pole obok Deweloper , a następnie kliknij OK :

Krok 3: Utwórz makro za pomocą VBA

Następnie kliknij kartę Deweloper na górnej wstążce, a następnie kliknij ikonę Visual Basic :

Następnie kliknij zakładkę Wstaw , a następnie z rozwijanego menu wybierz opcję Moduł :

Następnie wklej następujący kod do edytora kodu modułu:

 Function AvgCellsByColor(CellRange As Range, CellColor As Range)

Dim CellColorValue As Integer
Dim RunningAvg As Long
Dim RunningSum As Long
Dim RunningCount As Long

CellColorValue = CellColor.Interior.ColorIndex
Set i = CellRange

For Each i In CellRange
    If i.Interior.ColorIndex = CellColorValue Then
    RunningSum = RunningSum + i.Value
    RunningCount = RunningCount + 1
End If
Next i

AvgCellsByColor = RunningSum / RunningCount

End Function

Poniższy zrzut ekranu pokazuje, jak to zrobić:

Następnie zamknij edytor VB.

Krok 4: Użyj makro, aby uśrednić według koloru

Na koniec możemy użyć utworzonego przez nas makra, aby uśrednić wartości komórek na podstawie koloru.

Najpierw wypełnij komórki C2:C4 kolorami, dla których chcesz obliczyć średnią wartość.

Następnie wpisz następującą formułę w komórce D2 :

 =AvgCellsByColor( $A$2:$A$11 , C2 )

Przeciągnij i wypełnij tę formułę do każdej pozostałej komórki w kolumnie D, a formuła automatycznie obliczy średnią wartość dla każdej komórki z określonymi kolorami tła:

Na przykład widzimy, że średnia wartość komórek z jasnozielonym tłem wynosi 17,67 .

Możemy to potwierdzić, ręcznie uśredniając każdą komórkę na jasnozielonym tle:

Średnia komórek na jasnozielonym tle: (20 + 13 + 20) / 3 = 17,67 .

Odpowiada to wartości obliczonej przez nasz wzór.

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe operacje w programie Excel:

Jak obliczyć średnią według grupy w programie Excel
Jak obliczyć średnią miesięczną w Excelu
Jak obliczyć średnią według dnia tygodnia w programie Excel