如何自动提取Excel表格最后两行数值计算WoW周环比

函数公式方案 #

适用所有Excel版本的通用公式 #

假设Page Views数据存储在E列，E1为表头，下方为连续无空值的历史数据，可直接使用以下公式：
=INDEX(E:E,COUNTA(E:E))/INDEX(E:E,COUNTA(E:E)-1)-1
公式逻辑：

• COUNTA(E:E) 统计E列非空单元格总数，对应最后一行PV数据的行号
• 两次INDEX分别提取最后一行和倒数第二行的PV值，代入环比公式计算

如果数据列中间存在空行，或者E列下方有其他无关内容，可将查找范围限定为PV可能存在的最大行，比如调整为：
=INDEX(E1:E1000,COUNTA(E1:E1000))/INDEX(E1:E1000,COUNTA(E1:E1000)-1)-1

365/2021及以上版本优化公式 #

使用XLOOKUP从下往上匹配，可以兼容数据中间有空行的场景，稳定性更高：
=XLOOKUP(TRUE,E:E<>"",E:E,,0,-1)/XLOOKUP(TRUE,E:E<>"",E:E,,0,-2)-1

结构化表专属公式 #

如果你的数据已经转换为Excel结构化表（选中数据区域按Ctrl+T创建），假设表名为WeeklyData，PV列名为Page Views，可使用以下公式，新增行时表会自动扩展，无需调整公式范围：
=INDEX(WeeklyData[Page Views],ROWS(WeeklyData[Page Views]))/INDEX(WeeklyData[Page Views],ROWS(WeeklyData[Page Views])-1)-1

VBA自动更新方案 #

如果需要实现无感自动更新，无需手动修改任何内容，可使用VBA实现：

1. 打开目标表格，按Alt+F11调出VBA编辑器
2. 在左侧项目列表中双击存储数据的工作表，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRow As Long
    ' 此处E为PV数据所在列，可按需修改为对应列号/列字母
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    ' 此处G1为环比结果输出单元格，可按需修改
    If Not Intersect(Target, Columns("E")) Is Nothing Then
        If lastRow >= 3 Then ' 至少2行有效数据才可计算环比
            Range("G1").Value = Cells(lastRow, "E").Value / Cells(lastRow - 1, "E").Value - 1
            Range("G1").NumberFormat = "0.00%"
        Else
            Range("G1").Value = "数据不足"
        End If
    End If
End Sub

3. 保存文件为xlsm格式即可生效
   代码逻辑：每次修改E列数据时，会自动识别最后两行PV值计算环比，结果自动写入指定单元格并设置为百分比格式，数据不足时会给出提示。

内容的提问来源于stack exchange，提问作者Tom Burke