如何从Mac的Excel行返回第二个非空白单元格 [英] How to return the second non-blank cell from row excel for Mac

问题描述

要返回行中的第一个非空白单元格，请使用:

  = INDEX(C1:F1，MATCH(TRUE，INDEX((C1:F1 <0)，0)，0)) 

但是，如何返回第二个非空白单元格呢?我的手机里只有文字.

当我尝试= INDEX(C1:F1，AGGREGATE(15,6，COLUMN(C1:F1)/SIGN(LEN(C1:F1))，2))，按建议.

我没有得到下一个非空白的单元格，我得到了F单元格，即使之前应该返回其他单元格也是如此.如果F中没有任何内容，我将得到0.

解决方案

使用

将列索引号返回到

To return the first non-blank cell in a row I use:

=INDEX(C1:F1,MATCH(TRUE,INDEX((C1:F1<>0),0),0))

However, how do you return the second non-blank cell in a row? I only have text in my cells.

When I attempt =INDEX(C1:F1,AGGREGATE(15,6,COLUMN(C1:F1)/SIGN(LEN(C1:F1)),2)), as suggested.

I don't get the next non-blank cell returned, I get F cell returned, even if there are others that should have been returned before it. If there is nothing in F, I get a 0.

解决方案

Use the AGGREGATE function's SMALL subfunction.

=INDEX(A1:A13, AGGREGATE(15, 6, ROW(1:13)/SIGN(LEN(A1:A13)), 2))

The 2 is the k for the small subfunction. Replace it with ROW(2:2) and tighten up all of the other cell references if you want to fill down for the third, fourth, etc.

When returning the column index number back to the INDEX function, AGGREGATE function uses the COLUMN function instead of the ROW function.. COLUMN($A:$D) will give you the position within columns C:F; e.g. 1, 2, 3 or 4.

这篇关于如何从Mac的Excel行返回第二个非空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！