☰

Excel，我可以在同一单元格中显示具有公式结果的不同文本吗

tag-icon • tag-icon microsoft-excel
Excel，我可以在同一单元格中显示具有公式结果的不同文本吗

我希望使用 Excel 公式来有效地替换或显示同一个单元格中的不同值...

只是为了解释一下，这是举重杠铃的计算...例如...如果我在单元格中输入 45 来表示每侧 45 公斤，那么我想要显示的数字/文本是 110，即 (45x2)+20

是否可以用公式结果替换相同的单元格值？

答案1

' the range where the replace must be performed
Private Const DataAddress As String = "B1:B10"  

Private Sub Worksheet_Change(ByVal Target As Range)
Static busy As Boolean
Dim destination As String
Dim datarange As Range
Dim onecell As Range
' Check does the method is already executed for to forbid chain execution
If busy Then Exit Sub
busy = True
' Convert single-cell address to range address
If InStr(Target.Address, ":") = 0 Then
    destination = Target.Address & ":" & Target.Address
Else
    destination = Target.Address
End If
' Check does the range in interest is altered
Set datarange = Application.Intersect(Range(DataAddress), Range(destination))
If Not datarange Is Nothing Then
    ' Replace each cell in the range with new value
    For Each onecell In datarange
        onecell.Value = 2 * onecell.Value + 20
    Next
End If
' Drop busy flag
busy = False
End Sub

允许编辑单个单元格（手动）和某个范围（复制粘贴）。

不要检查插入的值是否是数字值 - 如果需要，您可以添加它。

相关内容