如何找到当前单元格上方最近的非空白单元格? [英] How to find nearest non-blank cell above the current cell?

问题描述

我有一列数据，我需要找到之前的非空白单元格.例如，如果我有以下数据:

I have a column of data, and I need to find the previous non-blank cell. For example, if I have the following data:

foo
-
-
-
-
(formula)

其中，-表示空白单元格，然后我希望(formula)单元格找到对包含foo的单元格的引用，无论它们之间插入了多少空白单元格.可以随时在公式行和上一个非空白行之间插入在测试列中具有空白单元格的新行，并且该公式应该能够处理该行.

where - denotes a blank cell, then I want the (formula) cell to find a reference to the cell containing foo no matter how many blank cells are inserted between them. It is possible that new rows with blank cells in the test column could be inserted between the formula row and the previous non-blank row at any time, and the formula should be able to handle that.

理想情况下，我希望能够将公式放在一行的任何单元格中，以找到该行上方(例如A列)中最接近的非空白单元格.

Ideally, I would like to be able to put the formula in any cell on a row to find the nearest non-blank cell above that row in, say, column A.

进一步说明我的需求的图片(也许会引出其他方式来实现我的需求):

An image to further illustrate my needs (and maybe elicit alternative ways to do what I want):

推荐答案

使用FILTER，ROW，MAX + ISBLANK()公式来获取它，

Use FILTER, ROW, MAX + ISBLANK() formulas to get it,

=FILTER(B1:B9, ROW(B1:B9)=MAX(FILTER(ARRAYFORMULA(ROW(B1:B9)), NOT(ISBLANK(B1:B9)))))

基本上可以满足您的需求.更准确地说，下面的公式是按您要求的将其粘贴到任何地方" 类型，只需将下面的公式粘贴到任何单元格上，它将为您提供最后一个非空白的单元格值.

That does basically what you need. More precisely the below formula is "paste it anywhere" type as you asked, just paste the below formula on any cell and it will give you the last nonblank cell value.

=FILTER( INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1) , ROW(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))=MAX( FILTER( ARRAYFORMULA(ROW(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))) , NOT(ISBLANK(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))))))

这篇关于如何找到当前单元格上方最近的非空白单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！