今日を乗り切るExcel研究所

Excel に働かされていませんか

結合セルをコピー&ペーストしたい

結合セルのコピー&ペーストを行う手順と、それを自動化するマクロを検討します。

結合セルはコピペが面倒

結合セルの何が困ると言えば、コピー&ペーストが思うようにできないことが一番ではないでしょうか。

特に方眼紙 Excelで、わざわざ結合セルで組まれた表からセルの値を取り出さなければならないときには、どうにもこうにもできずイライラの極致に達します。

結合セルは、通常の結合なしセルに貼り付けたり、また逆に通常セルから結合セルに貼り付けようとすると、貼り付け先が壊されたり、エラーになったりします。

1セルずつなら結合していてもなんとかコピーできるようですが、複数セル範囲になるともうだめです。あの手この手で試しても、何だかんだ言って断られます。

  • 「この操作は結合したセルには行えません。」
    f:id:shego:20170529133218p:plain
  • 「コピー領域と貼り付け領域のサイズが違うため、貼り付けることができません。」
    f:id:shego:20170529134513p:plain
  • 「この操作を行うには、すべての結合セルを同じサイズにする必要があります。」
    f:id:shego:20170529134648p:plain

結合セルに関係なく、値だけを簡単にコピー&ペーストをする方法はないのでしょうか。

結合セルの値のみをコピー&ペーストするには

結合セルを1個ずつコピーする

結合セルから結合セルへのコピーは基本的に出来ないのですが、条件によっては可能となります。

まず、セル1個ずつなら、結合セル間でもコピー&ペーストができるようです。

複数セルの場合でも、結合セルの構成(形や数)が完全に一致する領域間なら普通に可能です。 しかし、結合セルの構成に食い違いがあると、どうしてもエラーになります。

片方が通常セルの場合、1通常セル→1結合セルは問題なくコピペできますが、1結合セル→1通常セルでは結合状態も貼り付けされてしまいます。

結合セルから通常セルへ値のみコピーする

結合セルを含む領域を、通常の領域に普通にコピー&ペーストすると、値だけでなく、セルの結合状態もそのまま貼付けされて、コピー先が壊されてしまいます。

貼り付けをしたいのは、セルの値のみです。

そこで、今度は値のみで貼り付けをしてみます(「形式を選択して貼り付け」で「値」を選択)。 すると、セルの結合なしで値だけが貼付けられます。

f:id:shego:20170813020311p:plain

これで、結合セルから値だけを取り出せたことにはなります。 ただし、複数セルを値のみで貼り付けてみると、値の位置がバラバラに散ってしまいます。

これらを詰めて寄せるには、いったん作業領域に貼り付けをして、一つ一つコピペで集めなければなりません。 それなら、最初から一個ずつコピペするのと変わりません。

ただ、もし値のセルが、タテヨコきれいにそろっていたら、以下のセルを寄せる小技が使えます。

  1. タテヨコに飛びとびの値のあるセル範囲を選択します
  2. 「ホーム」>「検索と選択」>「条件を選択してジャンプ」>「定数」を指定して「OK」します
    • 値のあるセルのみが複数選択されている状態になります
    • セルの数が少なければ Ctrlキーを押しながらのクリックで複数セル選択をしたほうが早いです
  3. このままコピーして別の空き場所で貼り付けます
    • バラバラだったセルが隙間なく、貼り付けられます

f:id:shego:20170813020915p:plain

1カ所でも値セルの位置がずれたり欠けたりしているとと、もうコピーをさせてもらえないのでこの手順は使えません。

通常セルから結合セルへ値のみコピーする

先ほどの「書式なし」でバラバラに貼付けられたセルの位置関係をよく見直してみます。 結合が解けた値が残されたセルの位置は、どれも結合セルの左上角のセルに対応します。

どうも結合セルとは、この左上セルにつけられた書式の一種ということのようです。 つまり、セル結合とは本当にセルの構造を結合しているわけではなく、書式による単なる見せかけに過ぎないようです。

逆にいうと、このフォーメーションに合わせたセルを用意しておけば、書式なしの貼り付けをすることで、結合セルへ値をコピーすることができるはずです。

  1. 作業用領域で結合セルの左上角に対応するセルに値を配置します
  2. 作業用領域をコピーしします
  3. 元の結合セルのある貼り付け先を選択し、値のみ貼り付けます。
    • 「形式を選択して貼り付け」で「数式」あるいは「数式と数値の書式」を実行します。
    • なぜか「値」や「値と数値の書式」ではうまくいきません。
  4. 結合セルへ値のみ貼り付けられます。

f:id:shego:20170813022021p:plain

作業領域に値を一つずつ配置するのもまた面倒なことですが、これを軽減する小技はなさそうです。

結局

結局、効率よく、作業領域も使わずに、直接一括で結合セルをコピー&ペーストする方法はなさそうです。(いい方法をご存知でしたら教えてください)

このような状況では、マクロを使うしかありません。

  


結合セルの値をコピー&ペーストするマクロ

Excel が結合セルのコピーをさせてくれないのには何か理由があるのかもしれませんが、そこは気にしないことにして、結合セルの値のみをコピペするマクロを作成してみました。

下記 VBA マクロで、結合セルを含む領域を普通にコピー&ペーストできます。

結合セルを含むセル範囲を本マクロでコピーすると、コピー先を壊さずに値のみ貼り付けされます。 結合セルから通常セルへ、逆に通常セルから結合セルへ、あるいは結合セル同士の間でもかまいません。

f:id:shego:20170813011408p:plain

本マクロのロジックはセルを1個ずつたどって地道に値をコピーするものです。 比較的重い処理になりますので、大きなシートでの使用は避けたほうがいいでしょう。

また、複雑な結合をしたセル領域に対する使用もあまりお勧めしません。 結合セルが入れ違いで組み合わされているような状況でも動作はするのですが、どのセルがどこのセルへコピーされるか予想がつかず、抜け漏れもあるかもしれません。

マクロを「マクロの実行」ダイアログから実行する場合と、ショートカットやボタンに割り付ける場合とでは、使い方と挙動が多少異なりますので注意してください。

Option Explicit

Sub 結合セルをコピー_値のみ()
    Dim copyRange As Range
    Dim pasteRange As Range
    
    If Application.CutCopyMode = xlCopy Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Set pasteRange = Selection.Cells(1, 1)
        With Workbooks.add
            With .ActiveSheet
                .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
                .Range("A1").PasteSpecial xlPasteFormats
                Set copyRange = .UsedRange

                copyMergedCellsValue copyRange, pasteRange
            End With
            .Close SaveChanges:=False
        End With
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    ElseIf TypeName(Selection) = "Range" Then
        On Error Resume Next
        Set copyRange = Application.InputBox(prompt:="コピー元のセル範囲を選択してください", default:=Selection.Address, Type:=8)
        If copyRange Is Nothing Then Exit Sub
        Set pasteRange = Application.InputBox(prompt:="貼り付け先を選択してください", Type:=8)
        If pasteRange Is Nothing Then Exit Sub
        On Error GoTo 0
        
        Application.ScreenUpdating = False
        
        copyMergedCellsValue copyRange, pasteRange
    
        Application.ScreenUpdating = True
    End If
    
End Sub

Private Sub copyMergedCellsValue(src As Range, dst As Range)
    Dim bottomRight As Range
    Dim srcRow As Range
    Dim srcCell As Range
    Dim dstRow As Range
    Dim dstCell As Range
    Dim dstAddrs As String
    
    Set bottomRight = src.Cells(src.Rows.Count, src.Columns.Count)
    Set srcRow = src.Cells(1, 1)
    Set dstRow = dst.Cells(1, 1)
    dstAddrs = dstRow.Address
    Do
        Set srcCell = srcRow
        Set dstCell = dstRow
        Do
            dstCell.Value = srcCell.Value
            dstCell.NumberFormatLocal = srcCell.NumberFormatLocal
            dstAddrs = Union(Range(dstAddrs), Range(dstCell.MergeArea.Address)).Address
            Set srcCell = srcCell.Offset(0, 1)
            Set dstCell = dstCell.Offset(0, 1)
        Loop While srcCell.Column <= bottomRight.Column
        Set srcRow = srcRow.Offset(1, 0)
        Set dstRow = dstRow.Offset(1, 0)
    Loop While srcRow.row <= bottomRight.row

    dst.Worksheet.Activate
    Range(dstAddrs).Select
End Sub

【使い方1】「マクロの実行」ダイアログ

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします
  2. 「表示」タブ → 「マクロ」→ 「マクロの表示」を選択し、「マクロの実行」ダイアログを表示します
  3. 「マクロ名」リストから「結合セルをコピー_値のみ」マクロを選択し、「実行」します
  4. 最初の「入力」ダイアログで、コピー元のセル範囲を入力し、「OK」ボタンを押します
  5. 次の「入力」ダイアログで、コピー先のセル範囲を入力し、「OK」ボタンを押します
  6. 貼り付け先にコピー元の内容が入力されます

【使い方2】ショートカットやボタンに割り付け

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします
  2. あらかじめ、このマクロにショートカットキーを割り付けるか、マクロボタンに登録します。
  3. コピー元のセル範囲をコピーします(Ctrl + C)
  4. 貼り付け先のセルを選択し、ショートカットキーやマクロボタンで実行します。
    • コピー中のセル範囲なしで実行した場合は【使い方1】と同様の動作になります
  5. 貼り付け先にコピー元の内容が入力されます

【注意】結果を「元に戻す(Undo)」ことはできません。

【注意】「切り取り(Cut)」したセル範囲の貼り付けはできません。

まとめ

本記事の内容は Window 10 の Excel 2013 で動作確認しました。