基于动态命名范围的每日对数收益率计算实现问询

先明确下：你提到的公式表述应该是笔误，正确的对数收益率逻辑是当日收盘价与前一日收盘价比值的自然对数，也就是LN(当日收盘价/前一日收盘价)，下面给你落地两种实用的实现方式，适配你的动态命名范围场景：

方法1：纯Excel公式实现（快速简洁） #

你的HDaCClose是100行×X列的收盘价数据，对应的HDaCReturns需要设置为99行×X列（第一行没有前一日数据可计算）。

如果HDaCClose是Excel结构化表格，直接在HDaCReturns的对应单元格输入：

=LN(HDaCClose[#This Row]/HDaCClose[#Previous Row])

横向+纵向填充即可，表格会自动适配动态范围的变化。

如果是普通的动态命名范围（非表格），用INDEX函数精准定位单元格：

=LN(INDEX(HDaCClose,ROW()-ROW(HDaCClose)+1,COLUMN()-COLUMN(HDaCClose)+1)/INDEX(HDaCClose,ROW()-ROW(HDaCClose),COLUMN()-COLUMN(HDaCClose)+1))

同样填充到所有需要计算的单元格，这个公式能自动适配动态范围的位置变化。

方法2：VBA遍历单元格实现（符合你的遍历逻辑） #

如果你倾向于用代码遍历每一列的每个单元格完成计算，以下是完整的VBA代码，直接复制到Excel的模块中运行即可：

Sub ComputeLogReturns()
    Dim rngClose As Range, rngReturns As Range
    Dim colNum As Integer, rowNum As Integer
    
    ' 引用你的两个动态命名范围
    Set rngClose = ThisWorkbook.Names("HDaCClose").RefersToRange
    Set rngReturns = ThisWorkbook.Names("HDaCReturns").RefersToRange
    
    ' 遍历每一列
    For colNum = 1 To rngClose.Columns.Count
        ' 从第2行开始遍历（第1行无前置数据）
        For rowNum = 2 To rngClose.Rows.Count
            ' 计算对数收益率并写入HDaCReturns对应位置
            rngReturns.Cells(rowNum - 1, colNum).Value = WorksheetFunction.Ln(rngClose.Cells(rowNum, colNum).Value / rngClose.Cells(rowNum - 1, colNum).Value)
        Next rowNum
    Next colNum
End Sub

代码要点说明： #

• 先获取两个动态范围的实际引用，确保代码里的命名和你定义的一致
• 内层循环从第2行开始，因为第1行没有前一日收盘价可以计算
• HDaCReturns的行数要比HDaCClose少1行（99行），所以用rowNum - 1对应到收益率范围的行

关键注意事项 #

• 确认HDaCReturns的范围尺寸：必须是X列×99行，否则会出现数据溢出或者空白
• 如果你的动态命名范围是用OFFSET/INDEX定义的，要确保它能正确识别数据区域，比如HDaCClose的定义可以是：=OFFSET(Sheet1!$A$2,0,0,100,COUNTA(Sheet1!$2:$2))（假设第2行是数据起始行，第1行是列标题）

内容的提问来源于stack exchange，提问作者I. Я. Newb