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屋!