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（来自工具>宏> Visual Basic编辑器），但是要编写将使用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屋！