Excel: Como dar células escolhidas por alguns critérios como um parâmetro na variância (ou qualquer outra) fórmula?

Question

Para a seguinte tabela do 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

Eu preciso da coluna C para ter automaticamente a variação de todas as pontuações (coluna A), mas apenas para a categoria dessa linha (coluna B). O que significa que as linhas 2,3,6 terão o mesmo valor (todas compartilham a categoria Orange ), que é VAR.P(A2, A3, A6) .

Gostaria que todas as células na coluna C tivessem a mesma fórmula, que terá VAR.P sempre executado apenas nas células da Coluna A que têm uma coluna B que é igual à coluna B. Eu escrevo esta fórmula?

O ideal seria ter um equivalente de SUMIF() e AVERAGEIF() para variação, mas, como não existe, estou procurando uma solução mais genérica (que poderia funcionar com qualquer função que receba um conjunto de células) . Eu tentei fazer isso com IF() , LOOKUP() , etc., mas nada se encaixou.

    

Answer 1

Abra o editor de macro, na árvore esquerda, em Módulos, insira um novo módulo e, no lado direito, cole isto:

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

Agora, para o seu exemplo, na célula C2 você escreve:

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

Sobre os parâmetros:

• O primeiro é a Condição, neste caso B2 is Orange .
• O próximo campo é o intervalo de dados numéricos, neste caso $A$2:$A$7
• O último é a lista de categorias, neste caso $B$2:$B$7