☰

如何编写 Excel 公式以将特定值粘贴到不同的单元格中？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function cells
如何编写 Excel 公式以将特定值粘贴到不同的单元格中？

所以基本上我想编写一个公式来检查条件，如果满足该条件，那么我想将特定行的文本粘贴到另一个单元格中。我应该注意，我不希望公式存在于我想要粘贴值的单元格中。例如，如果我想将值粘贴到 B5，我不希望公式位于单元格 B5 中...

答案1

以下方法利用了描述的解决方法这里启用 VBA 中定义的工作表函数来设置另一个单元格的值。

自定义函数将目标单元格的地址和要设置的值存储在全局变量中。然后，工作表重新计算时触发的宏将读取全局变量并将目标单元格设置为指定值。

自定义函数的使用非常简单：

  =SetCellValue(target_cell, value)

其中target_cell是工作表中单元格的字符串引用（例如“A1”）或计算结果为此类引用的表达式。这包括 B14 的值为“A1”之类的表达式=B14。该函数可用于任何有效表达式。

SetCellValue如果成功将值写入目标单元格，则返回 1，否则返回 0。目标单元格的任何先前内容都将被覆盖。

需要三段代码：

  • 代码定义SetCellValue自身
  • 工作表计算事件触发的宏；以及
  • 一个实用函数IsCellAddress来确保这target_cell是一个有效的单元格地址。

SetCellValue 函数代码

此代码需要粘贴到插入工作簿的标准模块中。可以通过 Visual Basic 编辑器菜单插入模块，可通过选择功能区选项卡Visual Basic来访问该菜单。Developer

  Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

Worksheet_Calculate 宏代码

此代码必须包含在您将使用的工作表的特定代码中SetCellValue。最简单的方法是右键单击视图中的工作表选项卡Home，选择View Code，然后将代码粘贴到出现的编辑器窗格中。

  Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

IsCellAddress 函数代码

该代码可以粘贴到与代码相同的模块中SetCellValue。

  Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range           ' Input is valid cell reference if it can be
      On Error GoTo GetOut       ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long            'convert single cell "range" address to
      colonPos = InStr(aValue, ":")   'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If                          'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function

答案2

假设如果单元格 B5 包含值“绿色”，您希望在单元格 C5 中显示文本“文本 A”。

你能使用公式方法，但由于公式不能改变其他单元格中的值，因此需要将公式输入到单元格 C5 中。

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

如果 B5 包含单词“green”，则单元格 C5 现在将仅显示“文本 A”。

此类公式可以适用于多种情况。您需要定义您的需求，以便针对您的具体情况获得帮助。

如果您不希望 C5 包含公式，也可以使用 VBA 方法。您可以运行工作表更改事件，该事件将在单元格 B5 发生更改时运行，更改方式包括手动编辑值或粘贴内容。

这种宏的一个示例可能是

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

当然，输出的条件和位置仅仅是一个例子，需要根据您的要求进行调整。

公式和宏观方法的区别在于

  • 使用公式方法时，单元格 C5 将包含一个公式。如果用户意外删除了该公式，它提供的功能也将被删除。（不过，有办法可以解决这个问题）
  • 使用 VBA 宏，单元格 C5 将不会显示任何公式，并将逐字文本作为值，但更改设置需要了解 Excel VBA。此外，使用 VBA 方法，工作簿必须保存为启用宏的工作簿，并且用户必须允许宏或将文件设为受信任文件。

注意：以上只是示例。您需要定义您的要求，是否评估数字或文本，评估是否区分大小写，评估规则是什么，将结果放在何处等。

答案3

要检查条件是否满足，请在某个单元格中写入 IF 公式以更新“不同单元格中的特定文本行”。结果单元格将仅包含公式的值，而不是生成该值的公式，如下所示：

工作单元格（例如 J5）=IF(A1="yes","Specific line of text","")
结果单元格（例如 B5）=J5

因此，如果满足条件（A1=“是”），B5 将包含“特定文本行”。否则它将保持空白。

笔记：
具有变量值的单元格通常会包含某种公式来不断更新其值。

如果您不想让任何人知道创建该值的真实公式，则公式可以进入其他单元格来生成该值，然后将其复制到结果单元格，例如 B5。

要隐藏生成值的公式或隐藏 B5 引用的单元格（以继续检查值是否需要更新），请参阅：
https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

其他可以达到相同效果的函数包括CHOOSE、HLOOKUP、LOOKUP、VLOOKUP。

答案4

=值（单元格）

使用的公式很简单，遇到了同样的问题，但仍然有效。

相关内容