Excel在同一单元格中输入和输出

I'm not a programmer and I need some help!

我不是程序员,我需要一些帮助!

I have an excel sheet where I want to have a formula in the background and when I input a number I want it to be calculated in the formula without deleting it in the same cell.

我有一张excel表,我想在背景中有一个公式,当我输入一个数字时,我希望它在公式中计算而不在同一单元格中删除它。

The formula is like this X*6.75%+X , where X is the input. Some help would be appreciated.

公式类似于X * 6.75%+ X,其中X是输入。一些帮助将不胜感激。

Thanks.

2 个解决方案

#1

You haven't tagged as requiring VBA, but that's the only way I can think of achieving this.
As @FoxfireAndBurnsAndBurns says - you'd have to update the formula each time without VBA.

你还没有被标记为要求VBA,但这是我能想到的唯一方法。正如@FoxfireAndBurnsAndBurns所说 - 每次没有VBA你都必须更新公式。

For a VBA method you'd add this code to the specific worksheet module (updating the cell reference as required):

对于VBA方法,您可以将此代码添加到特定工作表模块(根据需要更新单元格引用):

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        If IsNumeric(Target) Then
            Application.EnableEvents = False
            'Target = Target * 6.75 / 100 + Target 'Place value in cell.
            Target.Formula = "=" & Target & "* 6.75 / 100 + " & Target 'Update formula in cell.
            Application.EnableEvents = True
        End If
    End If

End Sub

Edit: I've updated code to include suggestions in comments.

编辑:我已更新代码以在评论中包含建议。

#2

I don't see how this is better than simply using an extra column with formula, but...

我没有看到这比仅使用配方的额外列更好,但......

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
  'fire only in column A

    If IsNumeric(Target.Value2) Then ' only makes sense to apply if it's a number _
    otherwise it would produce a Type mismatch error

        Application.EnableEvents = False 'to prevent constant looping on change
        Target = Target * 6.75 / 100 + Target
        Application.EnableEvents = True

    Else
         MsgBox ("I can only take numeric values")
    End If

End Sub

NOTE: It's important to put the code for Worksheet_Change Event inside the module of the respective Sheet Tab. eg. if you want to the formula to fire in your sheet that is named "Sheet1", you will need to make sure you have the module of Sheet1 selected:

注意:将Worksheet_Change事件的代码放在相应的“工作表”选项卡的模块中非常重要。例如。如果您希望在名为“Sheet1”的工作表中触发公式,则需要确保选择了Sheet1模块:

Pasting this code anywhere else (eg. Sheet2, or Module 1) will not work!

在其他任何地方粘贴此代码(例如,Sheet2或模块1)将无效!

#1