如何返回行或列中的第一个/最后一个非空单元格?
在日常的Excel工作中,经常会遇到只有一些单元格包含值,而其他单元格为空的数据集。通常情况下,您可能需要快速找到某一行或某一列中第一个或最后一个有值的单元格。虽然对于少量数据来说这并不困难,但在较长的行或列中,手动查找这些值会变得效率低下、耗时且容易出错。
有几种实用的方法可以高效地检索行或列中的第一个或最后一个非空单元格值,帮助您自动化此任务并节省宝贵的时间。下面,您将找到基于公式的以及其他实操解决方案,适用于不同的实际场景,包括处理动态数据区域或大型数据集。
使用公式返回行或列中的第一个非空单元格
要从行或列中提取第一个包含数据的单元格,您可以结合使用INDEX和MATCH函数。当您希望以编程方式获取值时,这种方法非常适用,因为基础数据的变化会使您的工作表保持动态更新。以下是具体操作方法:
1. 在数据范围旁边的一个空白单元格中,输入以下公式:
=INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0))
例如,如果您的数据在A1到A13单元格中,此公式将返回第一个非空且非零的单元格。请参见以下示例:
2. 按下Enter键。该范围内第一个非空(或根据公式逻辑非零)单元格的值将显示如下:
注意事项:
- 在上述公式中,您可以调整A1:A13以引用任何列或引用行(例如,1:1表示第一行,或B2:M2表示行的一部分)。
- 此方法可可靠地应用于单行或单列。对于表格或多选范围,请考虑将公式分别应用于每一行或列。
- 如果公式返回错误(#N/A),请仔细检查您的范围是否至少包含一个非空且非零的单元格。
- 请注意,对于真正的空白单元格(""),如果您希望忽略仅为空的单元格而不是零,请将
<>0
替换为<>""
。

使用 Kutools AI 解锁 Excel 魔法
- 智能执行:执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
- 自定义公式:生成量身定制的公式,优化您的工作流程。
- VBA 编码:轻松编写和实现 VBA 代码。
- 公式解释:轻松理解复杂公式。
- 文本翻译:打破电子表格中的语言障碍。
使用公式返回行或列中的最后一个非空单元格
要从给定范围中获取最后一个非空单元格的值,基于数组的LOOKUP公式提供了一种高效且直接的解决方案,尤其适用于自动识别列表或汇总表中的最新数据条目。
1. 在目标范围旁边的空白单元格中输入以下公式:
=LOOKUP(2,1/(A1:A13<>""),A1:A13)
此公式扫描指定范围并返回最后一个非空单元格的值。例如,使用A1:A13作为范围:
2。按下 Enter后,Excel将计算并显示最后一个非空单元格中的值:
注意事项与建议:
- 您可以将此公式用于任何单列或单行(B1:B20、F8:F30或2:2等)。根据需要更新范围引用。
- 如果您的数据包含您希望忽略的零,可以将
A1:A13<>""
替换为A1:A13<>0
,但请确保真正空白与零符合您的意图。 - 此方法最适合简单的数据范围。对于返回“”(空文本)的公式范围,此公式将此类单元格视为空白。
- 如果所有单元格均为空,公式将返回#N/A错误。
通过VBA宏返回第一个或最后一个非空单元格值
对于处理大型数据集或需要自动化重复任务的用户,简单的VBA宏可以大大简化流程——尤其是在范围变化或涉及多行多列时。与公式不同,VBA执行按需操作,如查找第一个或最后一个非空单元格,非常适合跨多个范围的重复操作。
1. 通过转到开发工具 > Visual Basic打开VBA编辑器。在出现的VBA窗口中,点击插入 > 模块,并将以下过程之一粘贴到模块窗口中:
在选定范围内查找第一个非空单元格的宏:
Sub FindFirstNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim firstValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
firstValue = ""
For Each cell In rng
If cell.Value <> "" Then
firstValue = cell.Value
Exit For
End If
Next cell
If firstValue <> "" Then
MsgBox "The first non blank cell value is: " & firstValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End Sub
同样,这是查找最后一个非空单元格的代码:
Sub FindLastNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim lastValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
lastValue = ""
For Each cell In rng
If cell.Value <> "" Then
lastValue = cell.Value
End If
Next cell
If lastValue <> "" Then
MsgBox "The last non blank cell value is: " & lastValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End Sub
2。要执行代码,请点击 运行 按钮 在VBA编辑器中。系统将提示您选择目标范围以搜索非空单元格。在进行选择并确认后,对话框将显示第一个或最后一个非空单元格值,具体取决于您运行的是哪个宏。
- 这些宏灵活且适用于行和列,无论数据大小如何。
- VBA允许自动化和可重复处理,非常适合频繁或大规模的任务。
- 运行宏时,请确保保存您的工作簿并根据需要启用宏。始终在样本数据上测试以验证准确性,然后再应用于重要数据。
通过使用Excel的筛选功能查找第一个或最后一个非空单元格
对于需要快速视觉方式识别非空值的用户——尤其是拥有大量列数据时——Excel内置的筛选功能可以帮助一眼突出显示非空条目。尽管此方法不会自动将值返回到另一个单元格,但在数据分析期间进行审查或导航时非常有效。
以下是通过筛选直观定位第一个或最后一个非空单元格的方法:
- 选择包含您数据的列或行。您可以选择整个列(例如,点击列字母)以便更容易筛选。
- 点击数据选项卡,然后选择筛选。
- 点击范围或表格标题中的小筛选箭头。
- 取消勾选(空白)选项,以便仅保留可见的填充单元格。
- 筛选后,列顶部的第一个可见值就是您的第一个非空单元格;滚动到底部查看最后一个。
优点: 筛选方法快捷、无需公式,即使对于包含数千行的列也表现良好。
缺点: 该解决方案仅为视觉展示——它不会将结果输出到单元格,也不支持像公式和VBA那样的自动化。不过,它非常适合手动检查、审查和交互式数据探索。
故障排除与建议:
如果筛选似乎不起作用,请确保您没有仅选择部分数据,因为这可能导致筛选应用不正确。完成后,通过点击 数据 > 清除 恢复完整数据集视图。
最佳办公效率工具
🤖 | Kutools AI 助手:基于智能执行、生成代码、创建自定义公式、分析数据并生成图表、调用 Kutools 函数等功能,彻底改变数据分析方式… |
热门功能:查找、高亮或标记重复项 | 删除空行 | 合并不丢失数据的列或单元格 | 四舍五入 ... | |
高级 LOOKUP:多条件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高级下拉列表:快速创建下拉列表 | 从属下拉列表 | 多选下拉列表 .... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域和列 ... | |
精选功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...)... | |
排名前 15 的工具集: 12 种文本 工具(添加文本、删除特定字符等) | 50 多 种图表 类型(甘特图等) | 40 多种实用 公式(基于生日计算年龄等) | 19 种插入 工具(插入二维码、根据路径插入图片等) | 12 种转换 工具(小写金额转大写、汇率转换等) | 7 种合并与分割 工具(高级合并行、分割单元格等) | 还有更多... |
使用 Kutools for Excel 提升您的 Excel 技能,体验前所未有的高效。 Kutools for Excel 提供超过 300 种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 将标签式界面引入 Office,让您的工作更加轻松
- 在 Word、Excel、PowerPoint 中启用标签式编辑和阅读。
- 在同一窗口的新标签页中打开和创建多个文档,而不是在新窗口中进行操作。
- 将您的生产力提升 50%,每天为您减少数百次鼠标点击!