Excel中 - 数组公式跳过空白单元格 [英] Excel - Array Formula to skip empty cells
问题描述
我有一个数组公式来填充单元格时,它包含文本,但我想如果它的空白它忽略细胞并转到下一个。我遇到的问题是,它不会做它的顺序我有,例如:
I have an array formula to fill in the cell when it contains text but I want it to ignore the cell if its blank and go to the next one. The issue I am having is that it does not do it in the order i have for example:
范围为
-A
-(BLANK CELL)
-(BLANK CELL)
-D
-E
-F
-(BLANK CELL)
-H
-(BLANK CELL)
从公式结果
-A
-E
-F
-H
据跳过包含字母细胞。有人可以帮??
It is skipping the cells that contain letters. CAN SOMEONE HELP??
FORMULA:
<$c$c>{=IF(ISERROR(SMALL(IF(B4:$B$12<>\"\",ROW(B4:$B$12)),ROW(B4:$B$12)-3)),\"\",INDEX(B4:$B$12,MATCH(SMALL(IF(B4:$B$12<>\"\",ROW(B4:$B$12)),ROW(B4:$B$12)-3),IF(B4:$B$12<>\"\",ROW(B4:$B$12)),0)))}$c$c>
推荐答案
我认为问题是,你有范围为 B4:$ B $ 12个
,是因为那里周围 B4
$没有迹象表明引用变化拖动公式向下列.......但公式看起来有点复杂得多,你需要让我宁愿提出一个更好的选择:
I think the problem is that you have the range as B4:$B$12
, so because there are no $ signs around B4
that reference changes as you drag the formula down the column.......but the formula looks a little more complex than you need so I'd rather suggest a better alternative:
哪个版本的Excel您使用?
Which version of Excel are you using?
在Excel 2007或稍后再试此版本的
In Excel 2007 or later try this version
<$c$c>=IFERROR(INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>\"\",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))),\"\")$c$c>
假设你的第一个单元格 D4
(改变行(D $ 4:D4)
部分匹配您实际开始细胞),并您拖动公式向下列
Assumes your first cell is D4
(change ROWS(D$4:D4)
part to match your actual start cell) and you are dragging the formula down the column
与<大骨节病> CTRL 骨节病> + <大骨节病> SHIFT证实骨节病> + <大骨节病> ENTER 骨节病>
在早期版本中,其中 IFERROR
功能不可用,你可以使用这个版本。
In earlier versions where IFERROR
function is not available, you can use this version
<$c$c>=IF(ROWS(D$4:D4)>COUNTA(B$4:B$12),\"\",INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>\"\",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))))$c$c>
这篇关于Excel中 - 数组公式跳过空白单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!