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??
公式:
{=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)))}
推荐答案
我认为问题在于您的范围是 B4:$B$12
,所以因为 B4:$B$12
周围没有 $ 符号code>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
=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))),"")
假设您的第一个单元格是 D4
(更改 ROWS(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
=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))))
这篇关于Excel - 跳过空单元格的数组公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!