KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中查找并返回特定行或列中的倒数第二个值？

作者Siluvia修改日期

处理大型 Excel 工作表时，您往往不仅需要识别行或列中的最后一个值，还需精准定位倒数第二个值。例如，在下图所示的 A1:E16 数据区域中，您可能希望获取第 6 行或 B 列中的倒数第二个值。这类需求常见于追踪最近更改、监控时间序列数据，或分析数据集中最新但非当前的条目。相比简单查找最后一个值，定位倒数第二个值更具挑战性——尤其当数据包含空单元格或处于定期更新状态时。本文为您提供清晰、分步的解决方案，助您高效完成任务，轻松避开常见陷阱。

Excel 中显示行和列数据的表格截图

使用公式在特定行或列中查找并返回倒数第二个值

如上图所示，当您需要在 A1:E16 范围内的第 6 行或 B 列中动态查找并返回倒数第二个值时，Excel 公式提供了一种高效灵活的解决方案。在数据频繁更新、或因增删操作导致倒数第二个值位置变动的场景下，公式尤为适用——与手动方法不同，它能自动适配最新数据，确保结果始终准确无误。

在 B 列中查找并返回倒数第二个值

1. 选择一个空白单元格用于显示倒数第二个值。在编辑栏中输入以下数组公式，然后按 Ctrl + Shift + Enter（适用于旧版 Excel）将其确认为数组公式；在 Excel 365 或 Excel 2021 中，只需按 Enter 即可，因为数组公式会自动处理。

=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))

在 Excel 中查找列中倒数第二个值的公式截图

注意：在此公式中，B:B 表示 B 列。如需获取其他列的倒数第二个值，只需将 B:B 替换为对应列的引用（例如，改为 ）C:C 即可表示 C 列）。此方法适用于包含或不包含空白单元格的数据区域，但若存在隐藏值或被筛选掉的数据，请留意可能产生的偏差。如未看到预期结果，请务必仔细检查您的数据区域。

在第 6 行中查找并返回倒数第二个值

请选择一个空白单元格用于显示第 6 行的结果，然后在编辑栏中输入以下公式，并按 Enter 确认。

=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)

在 Excel 中查找行中倒数第二个值的公式截图

注意：在上述公式中，$A$6 是第 6 行的第一个单元格，而 6:6 表示整行 6. 如有需要，可调整这些引用以定位其他行。该公式会动态统计第 6 行中非空单元格的数量，并据此从起始单元格偏移，精准定位倒数第二个已填充的单元格。如果您的行中包含返回空字符串（）""）的公式，COUNTA 仍可能将其视为非空，从而影响结果。对于包含常量与公式的混合区域，请务必仔细核对结果，避免出错。

公式使用技巧：

  • 处理大型数据集时，使用整列引用（例如 ）B:B）可能影响性能。如非必要，请将范围限定在所需区域（例如 B1:B100），以提升效率！
  • 如果您的数据包含隐藏行或已应用筛选，公式仍会计算那些被隐藏或被筛掉的单元格。针对筛选后的数据，建议使用专门的分类汇总函数或辅助列。
  • 若目标行或列中的所有值均为空白，这些公式可能会返回错误或意外结果；请确保您的数据中至少包含两个非空值。

VBA 代码 – 使用宏在指定行或列中查找倒数第二个值

对于经常需要在不同或大型数据范围内查找倒数第二个值的用户，使用 VBA 宏自动化该过程可显著节省时间，尤其适用于动态或结构复杂的表格。当数据集大小频繁变动、您希望避免反复修改公式，或需以相同方式从多个不同位置提取值时，宏的优势尤为突出。以下 VBA 代码解决方案将提示您选择目标行或列，并自动返回倒数第二个值，彻底省去手动计数或调整的繁琐操作。

1. 转到 Excel 功能区中的开发工具选项卡，单击 Visual Basic 以打开 VBA 编辑器。在编辑器中，单击插入> 模块，然后将以下 VBA 代码粘贴到新建的模块中：

Sub GetSecondToLastValue()
    Dim rng As Range
    Dim arr As Variant
    Dim values As Collection
    Dim i As Long
    Dim secondLast As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
    If rng Is Nothing Then Exit Sub
    
    arr = rng.Value
    Set values = New Collection
    
    If rng.Rows.Count = 1 Then
        For i = 1 To rng.Columns.Count
            If arr(1, i) <> "" Then
                values.Add arr(1, i)
            End If
        Next i
    ElseIf rng.Columns.Count = 1 Then
        For i = 1 To rng.Rows.Count
            If arr(i, 1) <> "" Then
                values.Add arr(i, 1)
            End If
        Next i
    Else
        MsgBox "Please select a single row or single column range.", vbExclamation
        Exit Sub
    End If
    
    If values.Count < 2 Then
        MsgBox "There are less than two non-blank values in the selected range.", vbInformation
        Exit Sub
    End If
    
    secondLast = values(values.Count - 1)
    MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub

2. 输入代码后，返回 Excel，点击运行按钮运行按钮，或按 Alt + F8 并从列表中选择 GetSecondToLastValue。系统将提示您选择单行或单列范围（例如，列用 B1:B16，行用 A6:E6）。确认选择后，宏将在对话框中显示倒数第二个值。

注意事项：

  • 此宏仅统计非空单元格，将自动跳过所选行或列中的空白单元格。
  • 如果所选区域中的非空值少于两个，系统将提示警告消息，且不会返回任何结果。
  • 该宏适用于单行或单列的选择。若同时选中多行与多列，系统将提示您调整选择范围。
  • 如需实现自动化，您可进一步修改代码，将结果直接复制到工作表的指定单元格，而非弹出消息框。

此 VBA 解决方案专为处理动态表格或频繁变动的数据集而设计，能显著减少手动错误并省去重复性操作。

其他内置 Excel 方法 – 筛选所有空白单元格并手动识别倒数第二个值

尽管公式和宏能自动查找倒数第二个值，但有时您可能更青睐一种快速、直观的方法——尤其适用于不规则或非连续数据，或仅需偶尔核查某个值的场景。借助 Excel 内置的筛选工具，您可以临时隐藏空白或无关单元格，无需编写或修改公式，轻松锁定目标条目。

筛选方法：

  • 请选择您的列或行数据区域（列：选择单元格 B1:B16；行：在工作表中选择 A6:E6）。
  • 在 Excel 功能区上，单击数据 > 筛选 以启用筛选箭头。然后，在列标题或所选区域旁的筛选下拉菜单中单击。
  • 取消勾选(空白）即可隐藏空单元格，此时数据显示仅包含非空值。
  • 对于列，请滚动至筛选列表底部，查看倒数第二个值；对于行（若支持筛选），请从右侧开始计数，以定位倒数第二个非空项。

注意事项与技巧：

  • 处理小型到中型列表，或需直观确认数值位置时，筛选是最快捷的方法。
  • 如果您的数据集非常庞大，筛选操作可能会拖慢性能，且不适用于自动化或重复使用。
  • 应用筛选时，请确保您查看的是正确的上下文（筛选列表可能会忽略隐藏的行或列）。
  • 要清除筛选，请单击“数据”选项卡中的清除。

此方法虽不太适用于持续的动态分析或大型数据集，却提供了一种透明的手动检查方式，便于快速排查问题或验证倒数第二个非空项。

相关文章：

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱