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屋！