简单的Excel查找并替换公式 [英] Simple excel find and replace for formulas

问题描述

在工作表上到处都有很多单元格，它们看起来像=((E9-E8)/E8).我想使用前两个值来输入这个新公式(EXP((LN(E9/E8)/14.32))-1).

I have numerous cells all over the place on a worksheet that look like =((E9-E8)/E8). I want to use the first two values to go into this new formula, (EXP((LN(E9/E8)/14.32))-1).

如何一口气将它们全部更改为新公式?

How can I change them all to the new formula in one fell swoop?

推荐答案

如果公式相同，则可以使用查找并替换"并选中"Match entire cell contents"和"Look in: Formulas".选择范围，进入查找和替换，进行输入并全部替换.

If the formulas are identical you can use Find and Replace with Match entire cell contents checked and Look in: Formulas. Select the range, go into Find and Replace, make your entries and `Replace All.

或者您是说存在多个具有相同形式但公式不同的单元格引用的公式?如果是这样，那么一种方法是使用正则表达式匹配并替换.正则表达式不是内置在Excel(或VBA)中的，但是可以通过Microsoft的VBScript正则表达式库进行访问.

Or do you mean that there are several formulas with this same form, but different cell references? If so, then one way to go is a regular expression match and replace. Regular expressions are not built into Excel (or VBA), but can be accessed via Microsoft's VBScript Regular Expressions library.

以下功能提供必要的匹配和替换功能.它可用于子例程中，该子例程将标识具有指定范围内的公式的单元格并将这些公式用作函数的输入.对于与您要查找的模式匹配的公式字符串，该函数将生成替换公式，然后可以将其写回到工作表中.

The following function provides the necessary match and replace capability. It can be used in a subroutine that would identify cells with formulas in the specified range and use the formulas as inputs to the function. For formulas strings that match the pattern you are looking for, the function will produce the replacement formula, which could then be written back to the worksheet.

Function RegexFormulaReplace(formula As String)
    Dim regex As New RegExp
    regex.Pattern = "=\(\(([A-Z]+\d+)-([A-Z]+\d+)\)/([A-Z]+\d+)\)"
'   Test if a match is found
    If regex.Test(formula) = True Then
        RegexFormulaReplace = regex.Replace(formula, "=(EXP((LN($1/$2)/14.32))-1")
    Else
        RegexFormulaReplace = CVErr(xlErrValue)
    End If
    Set regex = Nothing
End Function

为使该功能正常工作，您需要添加对Microsoft VBScript Regular Expressions 5.5库的引用.在主功能区的Developer选项卡中，选择VBA，然后从主工具栏中选择References.向下滚动以找到对该库的引用，然后选中它旁边的框.

In order for the function to work, you would need to add a reference to the Microsoft VBScript Regular Expressions 5.5 library. From the Developer tab of the main ribbon, select VBA and then References from the main toolbar. Scroll down to find the reference to the library and check the box next to it.

这篇关于简单的Excel查找并替换公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！