如何一次替换多行公式上的row#？

Question

所以我有一张记录员工生产力的表格。每一行代表一天，每个人有四列。

目前，我每周结束时都要排一排，为这四列中的每一列提取一个“平均总数”。我注意到，它不正确地计算，如果一个人是外出的一天病假/假期，因为一个零或0%是计算成平均数。因此，我试图修改公式，以便使用Find &置换根据其他平均总数计算百分比。

我的问题是，我可以使用通配符来“查找”=AVERAGE(D***:D***)，但我无法知道如何替换新公式中的行号，而不需要更改每一行的行号。

示例：Cell D6的公式为=AVERAGE(D1:D5)。我可以在没有问题的情况下使用=AVERAGE(D***:D***)。我想用=IF(ISERROR($B6/$A6),0,$B6/$A6)替换它，但不需要输入行号，这样我就可以“替换所有”。

这对大多数人来说可能是显而易见的，但我确实尝试过在“替换”字段中使用Wilcard，但是它显然没有那样的功能。=IF(ISERROR($B***/$A***),0,$B***/$A***)。我也不能仅仅将公式拖到其他行，因为公式每6-7行只有一次。

我希望这是足够的信息，但如果我遗漏了一个细节，请告诉我。提前感谢！

Answer 1

对于VBA来说，这可能更容易。

假设，如您的示例所示，要替换的公式位于从D6开始的D列中，并且在此范围中没有包含单词AVERAGE的其他公式，则可以循环该范围，如果字符串平均值在cell.Formula中找到，它将替换为您所述的新公式。我简化了你的替换公式，因为你只需要一个ISERROR。

R1C1表示法的好处是，您只需声明一个替换公式，当将它放置在单元格中时，它将正确应用(相对引用)。这段代码将放在一个标准模块中，您将按下F5来运行。

通过searchRange.SpecialCells方法传递类型参数xlCellTypeFormulas，仅以包含公式的单元格为目标，进一步提高了效率。

如下所示：

Sub ReplaceFormula()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1") ' change as appropriate to your sheet name

    Dim lastRowInColD As Long

    lastRowInColD = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    Dim searchRange As Range
    Dim cell As Range

    Set searchRange = ws.Range(ws.Cells(6, "D"), ws.Cells(lastRowInColD, "D")).SpecialCells(xlCellTypeFormulas)

    For Each cell in searchRange.Cells
        If Instr(1, cell.Formula, "AVERAGE") > 0 Then cell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-3],0)"
    Next cell

End Sub