excel - 在单元格中显示公式(平均函数)

Question

我有一些数据:

对于每一列，我想计算最近一年的平均值。在下面的示例中，我想计算 2020 年的数据，因此第 164、165 和 166 行。结果应该出现在第 163 行。
但是，我想要的不仅仅是结果，我想要公式，以便用户可以看到正在计算的内容，但我不能。也许相关:我正在使用德语 Excel。我尝试使用 .FormulaLocal和 MITTELWERT (德语为 AVERAGE )但我认为我犯了另一种错误。两者都有.Formula和 .FormulaLocal我在 .Formula 行中遇到运行时错误 13(类型) .

Sub FormulaLeased(ByVal fRow As Long, ByVal lCol As Long, ws As Worksheet)
    Dim i       As Long
    Dim iCol    As Long
    
   'manually setting values for this example:
    fRow = 164
    lCol = 7
    Set ws = ActiveSheet

    With ws
        Select Case Mid(.Cells(fRow, "A").Value, 4, 2) 'Determining the most recent quarter 
        Case "12"
            i = 3
        Case "09"
            i = 2
        Case "06"
            i = 1
        Case "03"
            i = 0
        End Select
        
        For iCol = 2 To lCol
            '---works, but only shows the result, not the forumla:                
            '.Cells(fRow - 1, iCol).Value = Application.WorksheetFunction.Average(.Range(.Cells(fRow, iCol), .Cells(fRow + i, iCol))) 

            '---results in type error:
            .Cells(fRow - 1, iCol).Formula = "= Application.WorksheetFunction.Average(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")" 

        '---trying to use .FormulaLocal -> Type error
        '.Cells(fRow - 1, iCol).FormulaLocal = "= MITTELWERT(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")"
        Next iCol
    End With
End Sub

Answer 1

Application.WorksheetFunction不适用于 .Formula或 .FormulaLocal . .Formula需要英文公式，就像你在单元格中写的一样，.FormulaLocal需要本地化公式(德语)，就像您将其写在单元格中一样。
所以这种方法是一种方法:

.Cells(fRow - 1, iCol).FormulaLocal = "= MITTELWERT(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")"

但需要改为

.Cells(fRow - 1, iCol).FormulaLocal = "=MITTELWERT(" & .Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False) & ")"

没有 Range只需要两个单元格的地址。
请注意，此代码仅在您在德语 Excel 中运行时才有效，因为 MITTELWERT不会翻译成其他本地化版本。如果您计划您的代码也应该在法语 Excel 上运行，那么您需要使用英文公式和 .Formula .这将转化为任何本地化，因此可以在 中使用。全部 Excel 版本。我强烈推荐使用 .Formula并将它们自己翻译成英文( https://excel-translator.de 可能会有所帮助):

.Cells(fRow - 1, iCol).Formula = "=AVERAGE(" & .Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False) & ")"