填充非相邻空白单元格，其值从第一个空白单元格上方[复制]

This question already has an answer here:

这个问题已经有了答案:

Fill non-contiguous blank cells with the value from the cell above the first blank 1 answer
填充非连续的空白单元格，其值从单元格上方的第一个空白1的答案。

I want to know if there is a way to do this conditional in excel or open office:

我想知道是否有办法在excel或open office中做到这一点:

if the cell is empty then
    the cell will have the same value as the cell above it
else
    do nothing.

Should I use a macro for this? Please help me out; I have very little experience with Excel.

我应该使用宏吗?请帮我;我对Excel没有什么经验。

c 1 | 10/21/2011
c 2 |
c 3 |
c 4 | 10/24/2011
c 5 |
c 6 |
c 7 | 10/25/2011
c 8 |
c 9 |
c10| 10/26/2011

c | | | | | | | | | | | | | bbbc10 /2011 / 10/ 2011

6 个解决方案

#1

Select the range that contains blank cells you need to fill.

选择包含需要填充的空白单元格的范围。
Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

点击Home >找到并选择>到Special…，然后出现一个特殊的对话框出现，然后检查Blanks选项。
Click OK, and all of the blank cells have been selected. Then input the formula “=B2” into active cell B3 without changing the selection. This cell reference can be changed as you need.

单击OK，所有空白单元格都已被选中。然后在不改变选择的情况下，将公式“=B2”输入到活动单元B3中。可以根据需要更改此单元格引用。
Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

按Ctrl + Enter, Excel会将相应的公式复制到所有空白单元格中。
At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

此时，填充的内容是公式，我们需要将形式转换为值。然后选择整个范围，右键点击选择Copy，然后按Ctrl + Alt + V来激活粘贴特殊的…对话框。并从粘贴中选择Values选项，并从操作中选择None选项。
Then click OK. And all of the formulas have been converted to values.

然后单击OK。所有的公式都转化为数值。

#2

This is what I once wrote:

这就是我曾经写的:

Sub FillInBlankCellsInColumns()
 'Go down and when you find an empty cell, put the value from the previous cell in there.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim MyCounter As Long
MyCounter = 0
    For Each r In Selection
        On Error Resume Next
        If r.Value = "" Then
            r.Value = r.Offset(-1, 0).Value
        End If
    Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

#3

In Excel:

If you have a table that someone has merged cells on or if you are copying a table from an html site where there can be many inconsistnacies in the copied data such as empty rows that really should have the same value as the previous row an easy way to fix this is go to an empty column and create the formula that follows.

This assumes that you have a header in A1 and A2 contains the first data and a valid row value.
If only every other cell is empty you can do the following:

= if(A2="",A1,A2)

The above function checks if A2 is blank, if it IS, it assigns the value of A1, if it is NOT, it assigns the value of A2.

However, if there are multiple empty rows that need to be filled it is better to do the following:
(assume cell whose value you want to duplicate in all subsequent empty cells begins at A2, you are willing to set the first two cells in the following column manually, column I:I is first empty column in your table)

在Excel中:如果你有一个表,有人合并细胞或如果你复制表从一个html网站可以有许多inconsistnacies复制数据如空行应该有相同的值作为前一行一个简单的方法来解决这个问题是去一个空的列并创建公式。这假设您在A1和A2中有一个头，包含第一个数据和一个有效的行值。如果所有其他单元格都是空的，则可以执行以下操作:= If (A2="，A1,A2)以上函数检查A2是否为空，如果是，则赋值A1的值，如果不是，则赋值A2的值。然而,如果有多个空行需要填充最好做到以下几点:(假设细胞,其价值要复制所有后续空细胞始于A2,你愿意手动设置第一两个细胞在接下来的列,列我:我是第一个空的列在你的表)

Step One: In cells I2 and I3 ensure the proper vales are present for the associated rows.
Step Two: In cell I4 enter the following formula:

= if(A4="",if(A3<>"",A3,I3),A4)

The above function checks if A4 is blank, if it IS, it checks if A3 is NOT blank, if it indeed is NOT, it assigns the value of A3, if it too is blank, it assigns the value in I3, finally, if A4 was NOT blank, it assigns the value of A4.

You will have to drag the formula down manually (since there are empty rows).

步骤一:在细胞I2和I3中，确保相应的行有适当的vales。第二步:在细胞当输入以下公式:=如果(A4 = ",如果(A3 < > ",A3,I3),A4)上面的函数检查A4是空白的,如果是,它检查如果A3不是空白,如果它的确不是,它分配A3的价值,如果它是空白,它分配I3的价值,最后,如果A4不是空白,它分配A4的价值。您将不得不手动拖动公式(因为有空行)。

Any empty row that follows data that you don't want should be easily identifiable by sorting the column by a key field.

任何不需要的数据的空行都应该通过一个键字段对列进行排序，从而很容易识别。

As indicated by Robert above you are dealing with formula values so you can't just move them around, overwriting their source cells, but if you do a "special" paste you can paste the values directly over your source cells.

正如罗伯特所指出的，你正在处理公式值，所以你不能仅仅移动它们，覆盖它们的源单元，但是如果你做一个“特殊的”粘贴，你可以直接把值粘贴到你的源单元格上。

#4

Debra Dalgleish provides both the

Debra Dalgleish提供了。

manual SpecialCells Method (that Excellll refers to)
手动specialcell方法(卓越的是指)
the VBA SpecialCells method which seems to be more your area of focs
VBA的特殊细胞法似乎更适合你的病灶。

at her site under Excel Data Entry -- Fill Blank Cells in Excel Column

在她的网站Excel数据项下——填写Excel列中的空白单元格。

#5

Alternatively, you could use the following formula in a new column:

或者，您可以在新列中使用以下公式:

D2: =IF(ISBLANK(C2),D1,C2)

D2:如果(ISBLANK(C2),D1,C2)

Create a new column next to C.
在C旁边创建一个新的列。
Previous column D will now be named column E. Your data is in column C, the new data is temporarily created in column D.
以前的列D现在将被命名为列。您的数据在C列中，新的数据暂时在D列中创建。
The first value from C1 must be copied manually to D1
C1的第一个值必须手动复制到D1。
Then in D2 you put this formula. Copy the formula all the way down.
然后在D2中放入这个公式。把公式抄下来。
Then copy column D and use Paste-Special Values Only to paste the results of column D over the existing data in column C.
然后复制列D并使用Paste-Special值将列D的结果粘贴到列C中的现有数据。
Now you can remove column D
现在可以删除列D。

This is a little more "work" but since you state that you are not experienced in Excel, writing a macro could be challenging.

这有点“工作”，但既然你说你在Excel中没有经验，写一个宏可能会很有挑战性。

Regards,

问候,

Robert Ilbrink

罗伯特Ilbrink

#6

use Vlookup

使用Vlookup

=+VLOOKUP("Output",D1:G7,1)

Replace D1 with the address of the starting cell to lookup
将D1替换为启动单元的地址查找。
Replace G7 with the address of the Ending cell to lookup
用结束单元的地址替换G7查找。
Replace 1 with the column you want the value to be returned from
将1替换为要返回的值。

    Apple   1       Apple
            2       Apple
            7       Apple
    Mango   3       Mango
    =====================
    col1    Col2    Formula

#1

Select the range that contains blank cells you need to fill.

选择包含需要填充的空白单元格的范围。
Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

点击Home >找到并选择>到Special…，然后出现一个特殊的对话框出现，然后检查Blanks选项。
Click OK, and all of the blank cells have been selected. Then input the formula “=B2” into active cell B3 without changing the selection. This cell reference can be changed as you need.

单击OK，所有空白单元格都已被选中。然后在不改变选择的情况下，将公式“=B2”输入到活动单元B3中。可以根据需要更改此单元格引用。
Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

按Ctrl + Enter, Excel会将相应的公式复制到所有空白单元格中。
At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

此时，填充的内容是公式，我们需要将形式转换为值。然后选择整个范围，右键点击选择Copy，然后按Ctrl + Alt + V来激活粘贴特殊的…对话框。并从粘贴中选择Values选项，并从操作中选择None选项。
Then click OK. And all of the formulas have been converted to values.

然后单击OK。所有的公式都转化为数值。

#2