如何将公式应用于小计行？

Question 1

小计行通常是可识别的（例如，使用Average或Count- 或最常见的是和，就像您的情况一样Total）。筛选“在列中每次更改时”以查找包含“Tot”以仅访问小计行。

请注意，虽然经过过滤，结果可能看起来不正确，但在移除过滤器后应该进行适当调整。

SU585375 示例

Answer

小计行通常是可识别的（例如，使用Average或Count- 或最常见的是和，就像您的情况一样Total）。筛选“在列中每次更改时”以查找包含“Tot”以仅访问小计行。

请注意，虽然经过过滤，结果可能看起来不正确，但在移除过滤器后应该进行适当调整。

SU585375 示例

Question 2

我必须对带有单独加权类别的加权决策矩阵执行类似操作。电子表格如下所示：

excel 截图

出现“#NAME？”的地方是几个公式，我将对此进行描述。电子表格依赖于宏 - 一旦启用宏，“#NAME？”就会消失，并显示正确的数字。

每个类别都有多个带有权重的标准。标准下方会计算类别总数，但总数左侧的行中有一个单元格，其中写有“类别”一词。类别分数使用以下公式：

=ROUND(SUMPRODUCT(range_up($C83),range_up(D83))/SUM(range_up($C83)),1)

如果将上述公式输入到单元格 D84 中，并且类别权重在 $C84 中，则该公式是正确的。

最终得分采用以下公式：

=sum_categories($B4:$B98,1,2)

其中“$B4:$B98”是包含单词“Category”的范围，“1”是权重在“Category”列右侧出现多少列，“2”是分数在“Category”列右侧出现多少列（即，这应该是公式所在的列）。

上述公式使用了下面提供的两个函数，range_up 和 sum_categories：

Function range_up(r As Range) As Range
  Dim t As Range, b As Range

  Application.Volatile

  Set b = r.Cells(1, 1) 'make sure it's only one cell

  If IsEmpty(b.Value) Then 'if cell is empty, start one cell up
    Set b = b.offset(-1)
  End If

  'end(xlup) has strange behaviour if cell above is blank, so fix it manually
  If IsEmpty(b.offset(-1)) Then
    Set t = b
  Else
    Set t = b.End(xlUp)
  End If
  Set range_up = t.Resize(b.Row - t.Row + 1)

End Function

Function sum_categories(r As Range, offset1 As Integer, offset2 As Integer) As Variant
  Dim sum As Variant
  Dim c As Range

  Application.Volatile

  sum = 0
  For Each c In r.Cells
    If c.Value = "Category" Then
      sum = sum + c.offset(0, offset1).Value * c.offset(0, offset2).Value
    End If
  Next c

  sum_categories = sum
End Function

最后，如果您想手动重新计算，请在表单上添加一个调用此函数的按钮：

Sub force_recalc()
  Application.CalculateFullRebuild
End Sub

Answer