vba 取消合并excel行和重复数据

Unmerging excel rows, and duplicate data

提问by H3katonkheir

I have been given a fairly large database stored in Microsoft Excel, which I have to try convert into something useful.
However, one of the problems that I am encountering is that some of the data is merged together (horizontally in 2s).

我得到了一个存储在 Microsoft Excel 中的相当大的数据库，我必须尝试将其转换为有用的东西。
但是，我遇到的问题之一是某些数据合并在一起（水平在 2 秒内）。

For example;

例如;

row 1: [ x ][ x ][ x ][ x ][ x ]
row 2: [ x ][ x ][ o    o ][ x ]
row 3: [ o    o ][ x ][ o    o ]

Where x's are single cells and o's are merged together

其中 x 是单个单元格，而 o 是合并在一起的

What I want to do is unmerge all the rows (which I could do fairly easy with the unmerge button), but for where the merged cell was, have the data duplicated across the 2 cells.
From; [[ Some Data ]]
To; [ Some Data ][ Some Data ]

我想要做的是取消合并所有行（使用取消合并按钮我可以很容易地做到这一点），但是对于合并的单元格所在的位置，在 2 个单元格之间复制数据。
从; [[ Some Data ]]
到;[ Some Data ][ Some Data ]

Thanks! Any help is appreciated.

谢谢！任何帮助表示赞赏。

回答by aevanko

This is a VBA solution. This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range (what the value was).

这是一个 VBA 解决方案。此宏将搜索活动工作表中的每个单元格以查看它们是否已合并。如果是，它将合并单元格的范围存储在临时文件中。range 变量，取消合并单元格，然后用未合并范围内第一个单元格的值（值是什么）填充该范围。

Sub UnMergeFill()

Dim cell As Range, joinedCells As Range

For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

End Sub

回答by Montells

1. Select the range which has merged data
2. Click on Merge and Centre to unmerge cells
3. Select the range of data again
4. Press Ctrl+G > Special > Blanks
5. Press = and up arrow key
6. Press Ctrl+Enter

1. 选择合并数据的范围
2. 单击合并和居中以取消合并单元格
3. 再次选择数据范围
4. 按 Ctrl+G > 特殊 > 空白
5. 按 = 和向上箭头键
6. 按 Ctrl+Enter

回答by thanos.a

You don't need VBA for something like that.

你不需要 VBA 来做这样的事情。

1. Select the range of the merged cells
2. Unmerge the cells
3. Home -> Find and Select -> Go to special... -> Blanks -> ok
4. Type "=" move one cell up and press Ctrl + Enter

1. 选择合并单元格的范围
2. 取消合并单元格
3. Home -> Find and Select -> Go to special... -> Blanks -> ok
4. 键入“=”向上移动一个单元格，然后按 Ctrl + Enter

If R1C1 styles are enabled from: File -> Options -> Formula -> R1C1 Reference style then

如果从以下位置启用 R1C1 样式：文件 -> 选项 -> 公式 -> R1C1 参考样式，则

1. ...
2. ...
3. ...
4. Type "=R[-1]c" and press Ctrl + Enter

1. ...
2. ...
3. ...
4. 输入“=R[-1]c”并按Ctrl + Enter

回答by David Martin

I don't know how to quickly implement the VBA code, but I used a formula to populate another column with the values... then I did the copy/paste magic of pasting values over the unmerged cells. This formula is based on the fact that the value is associated with the FIRST cell of the merged cells, and all others resolve to 0 (zero).

我不知道如何快速实现 VBA 代码，但我使用了一个公式来用值填充另一列......然后我在未合并的单元格上粘贴了值的复制/粘贴魔术。此公式基于这样一个事实，即该值与合并单元格的第一个单元格相关联，并且所有其他单元格都解析为 0（零）。

If Col. A2:A100 has the merged cells, I did the following:

如果 Col. A2:A100 有合并的单元格，我执行以下操作：

1. In Cell B2, I entered this formula: =IF(A2<>0,A2, B1)
2. Copy the formula down to B100
3. Copy/Paste VALUES ONLY to column C.
4. Unmerge all of Column A.
5. Copy Values from Column C into Column A.

1. 在单元格 B2 中，我输入了这个公式： =IF(A2<>0,A2, B1)
2. 将公式复制到 B100
3. 仅将 VALUES 复制/粘贴到 C 列。
4. 取消合并 A 列的所有内容。
5. 将 C 列中的值复制到 A 列中。

Actually, you can simplify it to this series if you're bold...

其实，如果你大胆的话，你可以把它简化成这个系列......

1. Unmerge all of Column A
2. In Cell B2, I entered this formula: =IF(A2<>0,A2, B1)
3. Copy the formula down to B100
4. Copy/Paste VALUES ONLY to column A

1. 取消合并 A 列的所有内容
2. 在单元格 B2 中，我输入了这个公式： =IF(A2<>0,A2, B1)
3. 将公式复制到 B100
4. 仅将值复制/粘贴到 A 列

That skips the use of Column C...

这跳过了 C 列的使用......

回答by Jozef Bulat

here is another way to have data in all cells. I have a report that is published daily with merged cells, to avoid daily unmerging cells I refer to report with Power Query.

这是在所有单元格中保存数据的另一种方法。我有一个每天发布的带有合并单元格的报告，为了避免每天取消合并单元格，我参考 Power Query 报告。

using Fill down option I can populate data to all cells in the merged range. Works for me! :)

使用向下填充选项，我可以将数据填充到合并范围内的所有单元格。对我来说有效！:)

Report view with marked FIll Down option

带有标记为“向下填充”选项的报告视图