Excel - 在公式中跳过行的逻辑 [英] Excel - logic for skipping rows in a formula

问题描述

我正在尝试通过公式而不是 VBA/宏来执行以下操作(我不想要复制/粘贴特殊解决方案，因为我需要更多控制).是否可以:

I am trying to do the following via a formula rather than VBA/macro (i do not want a copy/paste special solution as i need more control). is it possible to:

我有一列，其中有一些空白行和一些带有值的行.我们称该列为 A.在 b 列中，如果 A 列有值，我只想复制单元格.如果没有，我希望它跳到 A 列中的下一个单元格，但保持在 b 列的同一单元格上.

I have a column in which there are some blank rows and some rows with values. let's call that column A. In column b, i only want to copy the cells if the column A has a value. if it doesn't, i want it to skip to the next cell in column A, but stay on on the same cell for column b.

A 列
1
2
[空白]
4
[空白]
6

Column A
1
2
[blank]
4
[blank]
6

我希望 b 列输出为(但没有空行).这意味着以某种方式公式需要循环并具有一些循环跳过逻辑:
1
2
4
6

i want column b output to be (but with no blank rows). this would imply that somehow the formula would need to loop and have some loop skipping logic:
1
2
4
6

通常我只会设置单元格 B1 = A1 来复制它，但由于我想跳过 A 列中的空白行，我不知道除了正确的宏之外还能做什么.我可以使用数组或任何其他创造性的解决方案吗?最终，我也会将它用于具有特定值的项目(不仅仅是空白).

Normally i would just set cell B1 = A1 to copy it over, but since i want to skip rows that are blank in column A, i'm not sure what to do besides right a macro. Can i use an array or any other creative solution? Eventually, i would also use this for items with specific values (not just blanks).

谢谢！

推荐答案

一般在单元格 C2 中复制下来....

Generically in cell C2 copied down....

=IFERROR(INDEX(Range,SMALL(IF(Range=criteria,ROW(Range)-MIN(ROW(Range))+1),ROWS(C$2:C2))),"")

通过 CTRL+SHIFT+ENTER

所以如果您的数据在 A1:A10 中并且您想要从 B1 向下的结果

so here if your data is in A1:A10 and you want results from B1 down

=IFERROR(INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS(B$1:B1))),"")

用 CTRL+SHIFT+ENTER 确认并复制

当你用完数据时，你会得到空白

when you run out of data you get blanks

这篇关于Excel - 在公式中跳过行的逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！