如何在每个单元格中保持合并单元格的值?

I created a sheet with merged cells, but the value of the merged cells is only stored in the first cell. Is there anyway, to keep the same value in each of the cells, I need that for a formula I use. Thanks!

我创建了一个带有合并单元格的表单，但是合并后的单元格的值只存储在第一个单元格中。无论如何，为了在每个细胞中保持相同的值，我需要一个我使用的公式。谢谢!

7 个解决方案

#1

In Excel 2003 this macro does the job:

在excel2003中，这个宏完成了以下工作:

Public Sub UnmergeAndFill()
    With Selection
        If .MergeCells Then
            .MergeCells = False
            Selection.Cells(1, 1).Copy
            ActiveSheet.Paste 'Or PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    End With
End Sub

Create the macro by

创建的宏

1. pressing Alt-F11, Ctrl-R, menu Insert/Module, paste the code;
2. 按Alt-F11、Ctrl-R、菜单插入/模块，粘贴代码;
3. alternatively: Alt-F8, type a new name (UnmergeAndFill, e.g.), click Make or Create (? don't know the English button text)
4. 或者:Alt-F8，输入一个新名称(UnmergeAndFill，例如)，单击Make或Create (?)不知道英文按钮文字)

Invoke the Macro by pressing Alt-F8, select it, Run. Alternatively map it to a key

通过按Alt-F8调用宏，选择它并运行。或者将它映射到密钥

#2

I know this is a rather old question, but this is the first place I landed when looking for an answer, and the accepted answer did not help at all. I DID discover an excellent answer, however, on MrExcel which I thought was worth putting on this thread for the benefit of anyone else googling for an answer:
http://www.mrexcel.com/forum/general-excel-discussion-other-questions/487941-data-multiple-cells-within-merged-cell-possible.html

我知道这是一个相当古老的问题，但这是我在寻找答案时遇到的第一个问题，而公认的答案对我毫无帮助。然而，我确实在MrExcel上发现了一个很好的答案，我认为它值得放在这个线程上，让其他搜索答案的人受益:http://www.mrexcel.com/forum/general- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

To save looking up the link, the answer is remarkably simple; if you merge cells using Excel Format Painter, rather than Merge Cells, it preserves the data/formulae 'underlying' the merged cells. You just need to create a temporary merged block of cells in the right format somewhere else, to use as a template for the Format Painter. You can delete them afterwards. One thing to watch out for, though, is having 'hidden' data like this can be a trap for the unwary, since editing the visible cell does not change the invisible ones.

为了节省查找链接的时间，答案非常简单;如果你使用Excel格式的绘制器来合并单元格，而不是合并单元格，它会保存合并后的单元格的数据/公式。您只需要在其他地方以正确的格式创建一个临时合并的单元格块，作为格式绘制器的模板。之后你可以删除它们。不过，需要注意的一件事是，像这样的“隐藏”数据可能会成为粗心的人的陷阱，因为编辑可见的单元格不会改变不可见的单元格。

#3

you can make a new column (or row) and apply this formula in first cell and drag it down:

你可以做一个新的列(或行)，把这个公式应用到第一个单元格中，然后拖下来:

I suppose in column A you have merged cells (for example A1:A3 & A5:A8 are merged).

我假设在A列中已经合并了单元格(例如A1:A3 & A5:A8被合并)。

1. Insert a column before column A

   在a列之前插入一列

2. In A1 type:

   在A1型:

   =B1
   

3. Copy the formula below in A2 :

   复制下面A2的公式:

   =IF(B2="",A1,B2)
   

4. Drag down the formula u typed in A2

   拖拽A2输入的公式u

In your formulas use the newly created column and after use you can hide it.

在公式中使用新创建的列，使用后可以隐藏它。

#4

can you not store the actual values somewhere else? instead of the merged cell? and yes , use a formula to display the value on the merged cell.

你不能把实际值存储在别的地方吗?而不是合并的单元格?是的，使用公式在合并的单元格上显示值。

#5

Assume column "A" has merged cells - put this in B1 and copy it to fill the rest of the column:

假设列“A”合并了单元格——将其放在B1中，并将其复制到该列的其余部分:

=IF(ISBLANK(A1);OFFSET(B1;-1;0);A1)

It checks if the cell to the left has a value if it has it returns its value, if not, it takes the value from the upper cell.

它检查左边的单元格是否有一个值，如果有，它返回它的值，如果没有，它从上面的单元格获取值。

Note that it doesn't work for the empty cells. First fill empty cells in column "A" with something unique, like "(empty)" and replace it back with emptiness both in "A" and "B" after filling column "B".

注意，它对空单元格不起作用。首先，在“A”栏中填满“A”栏中的空单元格，填入“B”栏后，用“A”栏和“B”栏中的空单元格替换为“(空)”栏。

#6

You could use a custom VBA function that gives directly the value of the merged cell, no matter which one you select. In that case it is not necessary to duplicate the values.

您可以使用定制的VBA函数，该函数直接给出合并单元格的值，无论您选择哪个单元格。在这种情况下，不需要重复这些值。

1. Switch to VBA view (Alt-F11)
2. 切换到VBA视图(Alt-F11)
3. Create a new module via Insert > Module
4. 通过插入>模块创建一个新的模块。
5. In your project, browse to the new module (you might want to give it a new name via the (name) property just under the explorer)
6. 在项目中，浏览到新模块(您可能希望通过资源管理器下的(name)属性给它一个新名称)
7. Copy the following code in the module (pasting the code in ThisWorkbook will not work)
8. 在模块中复制以下代码(粘贴此工作簿中的代码将不起作用)

Code:

代码:

Option Explicit

Function GetMergedValue(location As Range)
    If location.MergeCells = True Then
        GetMergedValue = location.MergeArea(1, 1)
    Else
        GetMergedValue = location
    End If
End Function

5. You can now use the formula in excel
6. 现在可以使用excel中的公式

Code:

代码:

=GetMergedValue(A1)

Where A1 is a part of a merged cell.

其中A1是合并单元格的一部分。

#7

I improved on sehe's macro to do as many merged cells as you select.

我在sehe的宏上进行了改进，可以做任意多的合并单元格。

Code:

代码:

Public Sub UnmergeAndFillMultiple()
    Dim w As Range, m As Range, n As Range
    For Each w In Selection.Cells
        If w.MergeCells Then
            Set n = w.MergeArea.Cells(1, 1)
            Set m = w.MergeArea
            w.MergeCells = False
            n.Copy
            m.PasteSpecial
        End If
    Next
End Sub