☰

使用其他列中的参数进行多次连续替换

tag-icon • tag-icon microsoft-excel worksheet-function
使用其他列中的参数进行多次连续替换

在 Excel 工作表中：

A列全部填充需要更新的随机文本值（例如单词/句子）。

B 列和 C 列包含指定需要对 A 列进行的更改的字符串。B 列的每个单元格都用旧文本填充，C 列则用新文本填充，每行都相互对应，也就是说，B1 中的文本需要用 C1 文本替换，B2 中的文本需要用 C2 文本替换。在 B 列中，要替换的内容之一是"。

B 列中没有重复的值。但是 C 列中的单元格可以重复。替换必须遵循写入的顺序。D 列是结果。

A 列中的每个单元格都需要经过 B1-C1、B2-C2、B3-C3 等替换，直到 BC 列的替换命令结束。因此，在给出最终结果之前，A 列的文本将更改几次，并且只有在所有更改之后才会在 D 列中显示结果。

例子：

Cell A1: Hello!
Cell A2: How are you "John"?
Cell A3: "Nice! thanks"

Cell B1: !                           Cell C1: &
Cell B2: &                           Cell C2: .
Cell B3: "                           Cell C3:    (empty)
Cell B4: ?                           Cell C4: #
Cell B5: Nice. thanks                Cell C5: Fine, THANKS.

因此单元格 A1（即Hello!）将被 B1-C1 更改，因此 将是Hello&。然后将更改 B2-C2，并对临时结果进行更改，Hello&因此 将是Hello.。然后将更改 B3-C3，然后执行 B4-C4，然后执行 B5-C5，并且什么也不会发生，因为"中不存在Hello.。

此后结果 D1 为Hello.。

然后它将对 A2 执行更改 B1-C1（无需执行），然后应用于此将对 B2-C2 执行更改（无需执行），然后对 B3-C3 执行更改，结果为How are you John?（即，引号字符将被删除），然后对 B4-C4 执行更改How are you John#，然后对 B5-C5 执行更改，这里无需执行任何操作。

所以结果 D2 是How are you John#。

同样的道理，A3 中的值"Nice! thanks"首先会从 B1-C1 发生变化，变成"Nice& thanks"，然后 B2-C2 变成"Nice. thanks"，然后 B3-C3 变成Nice. thanks（同样，引号会被去掉），然后 B4-C4 什么也不做，然后 B5-C5 会使其最后变成Fine, THANKSD3 中的值。

结果：

D1: Hello.
D2: How are you John#
D3: Fine, THANKS.

在 D 列中，按照 B 列和 C 列的行顺序进行多次替换，从而使 BC 列有超过 300 行，那么 D 列中的公式是什么？（如果可能的话，我想要一个纯粹基于公式的解决方案，而不是 VBA。）

来自上面的样本数据以及处理流程图

答案1

你可以做你描述/说明的事情

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, B$1, C$1), B$2, C$2), B$3, C$3), B$4, C$4), B$5, C$5)

如果将上述内容放入单元格 D1并向下拖动/填充，则将A1更新为A2、A3等，但替换字符串参数（B$1、、、等）不会改变。我相信很明显，这会对执行C$1→ 替换 B$2，然后对第一次替换的结果执行→ 替换 ，然后对第二次替换的结果执行 → 替换，依此类推。C$2B1C1A1B2C2B3C3

对于 5 个替换来说，这很尴尬，而对于更多替换来说，这很快就会变得难以处理。如果您可能想要进行多次替换（例如，最多 312 次），请使用辅助列：

  • 设置AA1​

    =SUBSTITUTE(A1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))

    这相当于=SUBSTITUTE(A1, B1, C1)，但它通过取列号（ →27）并减去 26（列 的列号 ）来动态获取B1和值，并使用结果（1）作为和 列中的行号。C1AAZBC

  • 拖动/填充AA1至AB1，因此变成

    =SUBSTITUTE(B1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))

    并改为B1，AA1因此变成

    =SUBSTITUTE(AA1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))

    这相当于=SUBSTITUTE(AA1, B2, C2)，相当于 =SUBSTITUTE(SUBSTITUTE(A1, B1, C1), B2, C2)。

  • 拖动/填充AB1到。这将是应用从到 的 所有替换LZ1的结果（因为是第 12 个字母，而 12×26 是 312）。A1B1:C1B312:C312L
  • 设置D1为=LZ1，以反映所有替换的结果。
  • 向下拖动/填充D1直至AA1:LZ1列中有数据 A。

答案2

我希望能找到一种巧妙的方法来做到这一点，使用一个数组公式，但找不到（我很想被证明是错的！）我认为用每行输入一个公式来实现这一点的唯一方法是嵌套函数SUBSTITUTE，如@G-Man 的回答遗憾的是，此策略不适用于 300 个替换，因为 Excel 有一个局限性该函数的嵌套深度只能为 64 级（在 Excel 2007 之前为 7 级）。

因此我认为您需要辅助列（如果有助于展示，您可以随时隐藏它们）。下面的方法使用的公式比 G-Man 的公式更简单。它使用的辅助单元格比他的多一些：每个输入字符串一列 × 每个替换一行加一个：

  • 从按照示例布局的工作表开始，在页面顶部插入一个空白行。选择单元格F1:H1，键入公式=TRANSPOSE(A2:A4)，然后按Ctrl+ Shift+ Enter，这样它就会作为数组公式输入。您应该看到公式周围出现花括号，并且单元格应该填充您输入的字符串。

  • 在单元格中F2输入公式=SUBSTITUTE(F1,$B2,$C2)。将此公式复制到所有单元格中F2:H6。您想要的输出将显示在中F6:H6。

  • 要将输出返回到列D，请选择D2:D4并输入公式=TRANSPOSE(F6:H6)。与之前一样，Ctrl++ Shift，Enter因此它可用作数组公式。

这应该可以轻松扩展更多输入字符串（使用更多列）或替换（使用更多行）；只需相应地调整范围即可。如果您实际上不需要列中的输入和输出，您TRANSPOSE也可以轻松地不使用公式；只需在顶行输入您的输入即可。

答案3

虽然我知道您正在寻找基于公式的解决方案，但我认为 VBA 版本将有助于参考，因为我在网上找不到：

Function MULTISUB(aString As String, oldVals As Range, newVals As Range) As Variant
    oldW = oldVals.Columns.Count
    oldH = oldVals.Rows.Count

    If (oldW = newVals.Columns.Count) And (oldH = newVals.Rows.Count) And (oldW = 1 Or oldH = 1) Then
        MULTISUB = aString
        For i = 1 To oldVals.Count
            MULTISUB = Replace(MULTISUB, oldVals.Cells(i), newVals.Cells(i))
        Next i
    Else
        MULTISUB = CVErr(xlErrRef)
    End If
End Function

举个例子，您可以=MULTISUB(A1,$B$1:$B$5,$C$1:$C$5)在单元格中输入D1并将其复制到D3。

该函数接受一个字符串和两个范围。它在字符串中搜索第一个范围中每个单元格的值，并将其替换为第二个范围中相应单元格的值。如果两个范围的形状和大小不同，则返回#REF。

相关内容