使用excel公式将一列中的内容移过一个空列 [英] move content in a column past an empty column using the excel formula

问题描述

如何使用excel公式将列中的内容移动到空列之外.例如a1 =香蕉，a2 ="，a3 =苹果，a4 =番茄，a5 ="，a6 =葡萄.像香蕉，苹果，番茄，葡萄这样的结果从上到下，谢谢.

how to move content in a column past an empty column using the excel formula. for example a1 = banana, a2 = "", a3 = apple, a4 = tomato, a5 = "", a6 = grape. and the results from top to bottom like this, banana, apple, tomato, grape, thank you.

推荐答案

如果必须使用公式:

在单元格B1中:

=IFERROR(INDEX($A$1:$A$6,AGGREGATE(15,3,ISTEXT($A$1:$A$6)*ROW($A$1:$A$6),ROW()+COUNTBLANK($A$1:$A$6))),"")

向下拖动...

为清楚起见，该范围在第15行开始时有效，您只需要编辑从$A$1:$A$6到$A$1:$A$20的范围:

To make clear it works when the range would start in row 15, you just need to edit the range from $A$1:$A$6 to $A$1:$A$20:

EDIT2 :为清楚起见，当您开始遍历函数和数据时必须更改更多引用:

To make clear you have to change more references when you start moving around the functions and data:

C6中的公式将变为:

=IFERROR(INDEX($B$1:$B$11,AGGREGATE(15,3,ISTEXT($B$6:$B$11)*ROW($B$6:$B$11),ROW(A1)+COUNTBLANK($B$6:$B$11))),"")

如果您真的想了解发生了什么，请尝试通过Formulas选项卡上的内置函数来评估公式.

If you really want to understand what is going on, also try and evaluate the formula through the build-in function on the Formulas tab.

这篇关于使用excel公式将一列中的内容移过一个空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！