如何在Excel中批量获取每行G-U列首尾有效值以计算YoY增长率

方法1：Excel内置函数（无需编程） #

适合Excel 2019及以上/365版本，直接用公式批量计算：

获取首个有效值（年初值）

• 针对数值型数据，用数组公式（旧版本按Ctrl+Shift+Enter确认，365自动生效）：
  =INDEX(G2:U2,MATCH(TRUE,ISNUMBER(G2:U2),0))
• 用XLOOKUP更简洁（仅365支持）：
  =XLOOKUP(TRUE,ISNUMBER(G2:U2),G2:U2,,1,1)

获取最后一个有效值（年末值）

• 针对数值型数据，快速公式：
  =LOOKUP(9.99E+307,G2:U2)
• 兼容非数值/空值的通用公式：
  =LOOKUP(2,1/(G2:U2<>""),G2:U2)

直接计算YoY增长率

把两个公式结合，加上错误处理：
=IFERROR((LOOKUP(9.99E+307,G2:U2)/INDEX(G2:U2,MATCH(TRUE,ISNUMBER(G2:U2),0)))-1,"无有效数据")
下拉填充即可批量应用到所有行。

方法2：VBA宏（适合批量复杂处理） #

如果需要更灵活的逻辑（比如忽略特定值、批量写入结果），可以用VBA实现自动化：

1. 按Alt+F11打开VBA编辑器，插入模块
2. 粘贴以下代码：

Sub CalculateYoYWithAutoValues()
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim colStart As Integer, colEnd As Integer
    Dim firstValidVal As Variant, lastValidVal As Variant
    Dim cell As Range
    
    ' 指定目标工作表，替换成你的表名
    Set targetSheet = ThisWorkbook.Sheets("目标数据集")
    colStart = 7 ' G列
    colEnd = 21 ' U列
    
    ' 获取数据最后一行
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, colStart).End(xlUp).Row
    
    ' 遍历每行（假设第1行是表头，从第2行开始）
    For currentRow = 2 To lastRow
        firstValidVal = Empty
        lastValidVal = Empty
        
        ' 查找首个有效值（仅识别数值）
        For Each cell In targetSheet.Range(targetSheet.Cells(currentRow, colStart), targetSheet.Cells(currentRow, colEnd))
            If IsNumeric(cell.Value) And cell.Value <> "" Then
                firstValidVal = cell.Value
                Exit For
            End If
        Next cell
        
        ' 查找最后一个有效值（仅识别数值）
        For Each cell In targetSheet.Range(targetSheet.Cells(currentRow, colStart), targetSheet.Cells(currentRow, colEnd))
            If IsNumeric(cell.Value) And cell.Value <> "" Then
                lastValidVal = cell.Value
            End If
        Next cell
        
        ' 写入结果到指定列（示例：V列存年初值，W列存年末值，X列存YoY）
        targetSheet.Cells(currentRow, "V").Value = firstValidVal
        targetSheet.Cells(currentRow, "W").Value = lastValidVal
        
        ' 计算YoY，处理异常情况
        If Not IsEmpty(firstValidVal) And Not IsEmpty(lastValidVal) And firstValidVal <> 0 Then
            targetSheet.Cells(currentRow, "X").Value = (lastValidVal / firstValidVal) - 1
        Else
            targetSheet.Cells(currentRow, "X").Value = "无效数据"
        End If
    Next currentRow
End Sub

3. 修改代码中的工作表名、结果输出列，按F5运行宏即可完成批量处理。

内容的提问来源于stack exchange，提问作者Vishesh Saini