☰

Excel：如何将根据某些标准选择的单元格作为方差（或任何其他）公式中的参数？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：如何将根据某些标准选择的单元格作为方差（或任何其他）公式中的参数？

对于以下 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()等来做到这一点，但都不适合。

答案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

相关内容