Как усреднить по цвету в excel (пошаговый пример)

Часто вам может потребоваться усреднить значения в Excel на основе их цвета.

Например, предположим, что у нас есть следующий набор данных и мы хотим вычислить среднее значение ячейки на основе цвета ячейки:

Самый простой способ сделать это — написать код VBA в Excel.

Это может показаться пугающим, если вы не знакомы с VBA, но процесс прост, и следующий пошаговый пример показывает, как именно это сделать.

Шаг 1: Введите данные

Сначала введите значения данных в Excel:

Шаг 2. Показать вкладку «Разработчик» в Excel

Далее нам нужно убедиться, что вкладка «Разработчик» видна на верхней ленте Excel.

Для этого перейдите на вкладку «Файл» , затем «Параметры» , затем «Настроить ленту» .

В разделе « Основные вкладки» установите флажок «Разработчик» и нажмите «ОК» :

Шаг 3. Создайте макрос с помощью VBA

Затем щелкните вкладку «Разработчик» на верхней ленте, а затем щелкните значок Visual Basic :

Затем перейдите на вкладку «Вставка» и выберите «Модуль» в раскрывающемся меню:

Затем вставьте следующий код в редактор кода модуля:

 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

На следующем снимке экрана показано, как это сделать:

Затем закройте редактор VB.

Шаг 4. Используйте макрос для усреднения по цвету

Наконец, мы можем использовать созданный нами макрос для усреднения значений ячеек на основе цвета.

Сначала заполните ячейки C2:C4 цветами, для которых вы хотите рассчитать среднее значение.

Затем введите следующую формулу в ячейку D2 :

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

Перетащите эту формулу в каждую оставшуюся ячейку столбца D, и формула автоматически рассчитает среднее значение для каждой ячейки с определенным цветом фона:

Например, мы видим, что среднее значение ячеек со светло-зеленым фоном равно 17,67 .

Мы можем подтвердить это, вручную усреднив каждую ячейку со светло-зеленым фоном:

Среднее значение ячеек на светло-зеленом фоне: (20 + 13 + 20)/3 = 17,67 .

Это соответствует значению, рассчитанному по нашей формуле.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как посчитать среднее значение по группе в Excel
Как посчитать среднее значение за месяц в Excel
Как посчитать среднее значение по дням недели в Excel