在Excel中,如何用最后一个非空白单元格填充空白单元格? [英] In Excel, how to fill blank cells with the last preceding non-blank cell?
问题描述
如何填写最后一个非空白单元格的空白单元格?
例如,我有这两列表:
A
abc
dce
fds
B
kjh
abc
我想要的是:
A
A abc
A dce
A fds
B
B kjh
B abc
$我尝试过一个像= IF(ISBLANK(A2),A1,A2)这样的公式,但是它告诉我有关冗余,而不是' 解决方案
- 在手册可以执行以下操作:
选择列,转到编辑>转到>特殊>空白,在公式栏中键入=点击向上箭头然后按Ctrl + Enter。您现在应该使用单元格填充上面的值
- 在使用VBA的更多自动化您可以执行以下操作:
选择要填充空白单元格的范围,
然后点击开发人员标签 - > Visual Basic - >插入 - >模块
然后将代码粘贴到模块中,然后单击 run :
代码:
Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks)。选择
Selection.FormulaR1C1 == R [-1] C
End Sub
正如@Jerry正确指出的那样,确保复制粘贴特殊值以便删除填充列后面的公式。这样可以防止任何不期望的结果!
How to fill blank cells with the last preceding non-blank cell ?
For example, I have this two-columns sheet:
A
abc
dce
fds
B
kjh
abc
What I would like is:
A
A abc
A dce
A fds
B
B kjh
B abc
I tried a formula like "=IF(ISBLANK(A2), A1, A2)" but it warns me about redundancy and doesn't work.
解决方案
- In a "manual" fashion, you could do the following:
Select the column, go to Edit > Go To > Special > Blanks, type = in the formula bars, hit the up arrow then Ctrl+Enter. you should now have the cells fill with the values above
- In a more "automated" fashion using VBA, you could do the following:
Select the range where you want to fill the blank cells,
Then click on Developer Tab -> Visual Basic -> Insert -> Module
Then paste the code in the Module, and click on run:
Code:
Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
As @Jerry rightfully pointed out, make sure to copy-paste special the values in order to remove the formulas behind your filled column. This will prevent any undesirable results later!
这篇关于在Excel中,如何用最后一个非空白单元格填充空白单元格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!