Як усереднити за кольором у 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