Excel，如何在单元格公式中显示值而不是引用 [英] Excel, how to display values in cell formula instead of references

问题描述

当您在 Excel 中单击一个单元格时，您会看到一个类似于 =A1+B1 的公式.那是带有单元格引用的公式.我想查看包含单元格值的公式，例如:=10+20.

When you click a cell in Excel, you see a formula like =A1+B1. That is the formula with the cell references. I want to see the formula with the cell values, for example: =10+20.

A1 =10           //Cell A1 has the value 10
A2 =20           //Cell A2 has the value 20
C2 =A1+B1        //Cell C2 has the value A1 + A2

D2 =10+20        //I want Excel to derive this.

我不想在单元格或任何东西中显示公式.我仍然希望 C2 显示 30.我只是想要这样，如果我单击 C2，我可以在公式栏中看到 =10+20.

I don't want the formula displayed in the cell or anything. I still want C2 to display 30. I just want it so that if I click on C2, I can see =10+20 in the formula bar.

或者，如果 C2 在显示计算的地方显示注释(因此单击 C2 将显示注释=10+20"，那也可以.

Alternatively, if C2 displayed a comment where it showed the calculations (so clicking on C2 would display the comment "=10+20", that would work too.

推荐答案

Excel 不是这样工作的；如果您在单元格中有公式，这就是将显示在公式栏中的内容.

That's not how Excel works; if you have a formula in a cell, that is what is going to show up in the formula bar.

编辑

您可以使用 VBA(来自 Tools > Macros > Visual Basic Editor)，但是，编写将使用 A + B 值更新 C 单元格内容的代码，像这样:

You can use VBA (from Tools > Macros > Visual Basic Editor), however, to write code that will update the contents of the C cells with the the values of A + B, like this:

Private Sub HideFormula()
    Dim lastrow As Long, r1 As Long

    ' Get the last row in the worksheet
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    For r1 = 1 To lastrow

        ' If A & B aren't blank, make the formula of cell C equal to A + B.
        If Sheet1.Range("$A$" & r1).Value <> "" And _
            Sheet1.Range("$B$" & r1).Value <> "" Then

            ' In the example, C2 = A1 + B1, so offset C by one
            Sheet1.Range("$C$" & (r1 + 1)).Value = _
                "=" & Sheet1.Range("$A$" & r1).Value & "+" & _
                Sheet1.Range("$B$" & r1).Value

        End If

    Next

End Sub

编辑 2

如果你想用公式中的值替换一个 C 单元格的内容，你可以使用 .Formula 值来找到它的公式，然后从那里开始:

If you want to replace the contents of a C cell with the values in the formula, you can use the .Formula value to find its formula, and go from there:

Private Sub ReplaceFormulaWithValues()
    Dim lastrow As Long, r1 As Long
    Dim temp As String, arTemp

    ' Get the last row in the worksheet
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    For r1 = 1 To lastrow

        ' If A & B aren't blank, make the formula of cell C equal to A + B.
        If Sheet1.Range("$C$" & r1).Value <> "" Then

            ' Get the formula for the current C cell
            temp = Replace(Sheet1.Range("$C$" & r1).Formula, "=", "")

            ' Create an array by splitting the formula on the + sign
            arTemp = Split(temp, "+")

            Sheet1.Range("$C$" & r1).Value = _
                "=" & Sheet1.Range(arTemp(0)).Value & "+" & _
                Sheet1.Range(arTemp(1)).Value


        End If

    Next

End Sub

这篇关于Excel，如何在单元格公式中显示值而不是引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！