Excel跳过空白或值为0的单元格 [英] Excel skip blank or 0-valued cells
问题描述
我希望有一个公式可以扫描一列数据,并且如果此列为非零值,则它将单元格的内容注册到另一列但同一行中.我还希望它可以删去空白单元格.在Excel 2007中可以吗?
I would like to have a formula that scans through one column of data, and should this column be non-zero, it registers the contents of a cell in another column, but the same row. I would also like it to trim out the blank cells. Is this possible in Excel 2007?
例如:我有
column A
A
B
C
G
H
column B
1
2
blank
blank
8
如果条件是非零列B,我们将得到以下输出:
If the criteria is a non-zero column B, we have the following output:
column N
A
B
H
推荐答案
执行此操作的通常方法是在输入的数组公式中使用INDEX
和SMALL
:
The usual way to do something like this would be to use INDEX
and SMALL
in an array entered formula:
=IFERROR(INDEX(A:A,SMALL(IF(B:B<>0,ROW(B:B)),ROWS($B$1:B1))),"")
在第N列的第一个单元格中输入公式后,不要按 Enter .而是按 Ctrl + Shift + Enter
Put that in the first cell in column N and after typing the formula, don't press Enter. Instead, press Ctrl+Shift+Enter
然后您可以将其向下拖动.
And then you can drag it down.
这篇关于Excel跳过空白或值为0的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!