Excel: как дать ячейки, выбранные по некоторым критериям, в качестве параметра в формуле дисперсии (или любой другой)?

Question

Для следующей таблицы Excel:

           A          B           C
1        Score     Category   VariancePerCategory(Calculated)
2       0.3146      Orange      0.034214             
3       0.5711      Orange      0.034214     
4       0.7129      Apple       0.0          
5       0.8426      Lemon       0.000215
6       0.1194      Orange      0.034214     
7       0.8719      Lemon       0.000215

Мне нужен столбец C, чтобы автоматически иметь дисперсию всех оценок (столбец A), но только для категории этой строки (столбец B). Это означает, что строки 2,3,6 будут иметь одинаковое значение (все они имеют категорию Orange ), то есть VAR.P(A2, A3, A6) .

Я хотел бы, чтобы все ячейки в столбце C имели одинаковую формулу, в которой VAR.P всегда запускается только в ячейках столбца A, столбец B которых равен столбцу текущей строки B. Как можно написать это формула?

В идеале я хотел бы иметь некоторый эквивалент SUMIF() и AVERAGEIF() для дисперсии, но так как он не существует, я ищу более общее решение (которое могло бы работать с любой функцией, которая получает набор ячеек). Я пытался сделать это с помощью IF() , LOOKUP() и т.д., Но ничего не подходит.

Answer 1

Откройте редактор макросов, в левом дереве в разделе Модули вставьте новый модуль и в правой части вставьте:

Function cond_Variance(Condition As String, Data As Range, Categories As Range) As Double
    Dim numbers() As Double
    DataRows = Data.Rows.Count
    CategoriesRows = Categories.Rows.Count
    firstDataRow = Data.Row
    firstDataColumn = Data.Column
    firstCategoriesRow = Categories.Row
    firstCategoriesColumn = Categories.Column
    ReDim numbers(DataRows)
    counter = 1
    Total = 0
    For i = firstCategoriesRow To firstCategoriesRow + CategoriesRows - 1
        selectedcell = Cells(i, firstCategoriesColumn)
        If selectedcell = Condition Then
            numbers(counter) = Cells(i, firstDataColumn)
            counter = counter + 1
            Total = Total + Cells(i, firstDataColumn)
        End If
    Next i
    counter = counter - 1
    mean_value = Total / counter
    vTotal = 0
    For i = 1 To counter
        nDiff = (numbers(i) - mean_value) * (numbers(i) - mean_value)
        vTotal = vTotal + nDiff
    Next i
    cond_Variance = vTotal / counter
End Function

Теперь, для вашего примера, в ячейке C2 вы пишете:

=cond_Variance(B2,$A$2:$A$7,$B$2:$B$7)

О параметрах:

• Первым является Условие, в этом случае B2 is Orange .
• Следующее поле - это диапазон числовых данных, в данном случае $A$2:$A$7
• Последним является список категорий, в данном случае $B$2:$B$7