今日を乗り切るExcel研究所

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

同じ値のセルを結合したい

同じ値が連続しているセル範囲の結合と解除をする方法を検討します。また、それを一括で行うマクロを作成します。

セル結合の誘惑

シートに、同じ値が連続しているセル領域があります。

ここではそのようなセル領域を「連続セル」と呼ぶことにします。

人にはどうも、連続セルを一つのセルに結合したくなる強い習性があるようです。

確かに、表形式の列などで、冗長な連続セルを一つに結合すれば、体裁として構成が分かり易くなります。

一方、セルを結合されてしまうと、並べ替えや CSV 出力など、データとしてシートを使いたい時に困ることになるといった弊害があることも、今ではよく知られているところです。

見た目はきれいでも、それがために強いられる余分で非効率な作業にはイライラが募ります。

イライラの原因は、連続セルの結合と解除を一括でやる効率的な方法が無いからで、使う方だけでなく、作る方でも相当なイライラとなっています。

逆に言えば、この手間さえ軽減できれば、結合セルもそんなに悪いものではありません。

問題は、連続セルの結合そのものが悪いのではなく、それを一括で手軽に行う手段が Excel の機能として提供されないことにあります。

手作業で連続セルを結合してみる

色々調べましたが、 Excel には連続セルの一括結合を支援するような、コマンドやショートカットの技はありません。

個別に結合するのは仕方ないにしても、もう少し効率よくやる手順がないものか、探ってみました。

課題は二つあって、一つは、連続セルを簡単に選択する方法、もう一つはセル結合時の警告を抑制することです。

ここでは、それらを何とかする小技を2つ紹介します。

「アクティブ列との相違」で連続セルを選択する小技

「限られた状況」でなら、次のように連続セルを選択する方法がないこともありません。

「アクティブ列との相違」コマンド(Ctrl+Shift+¥)を駆使します。

【手順例】

  1. 複数の連続セル(縦)を持つ範囲の先頭セルを選択します
    • 先頭セルの直上は空白セルである必要があります
    • 異なる値の連続セルが範囲選択される必要があります
  2. 下向き選択と上向き選択で2回、「アクティブ列との相違」を実行します
    • キー操作: Cntl+Shift + ↓¥↑¥
      Ctrl キーと Shift キーを押したまま、「↓¥↑¥」とタイプします
    • 先頭の連続セルが範囲選択されます
  3. 「セルの結合」を実行します
    • キー操作: AltHMMEnter
    • 警告ダイアログが表示されますが、速攻で「OK」(Enter)して瞬殺します。
  4. 範囲選択された連続セルが結合されます
  5. 一つ下のセルに(次の連続セルの先頭)に移動します
  6. 2.から繰り返します。
    • 最後の1範囲はCntl+Shift + のみで選択できます。

f:id:shego:20171028213345p:plain

「限られた状況」というのは、

  • 先頭セルの直上のセルが空白であること、
  • 下の方に、別の連続セルがあること、
  • 同じ値の連続セル領域が複数ないこと

などです。
そうでないと、思うような挙動にはなりません。

直上の空白セルについては、結合セルが空白扱いされるので、うまいこと次の連続セルから繰り返すことができます。

残念ながらこのやり方では、セル結合にやり直しのショートカット(Alt + Enterなど)は利きません。 面倒ですが毎度「セルの結合」コマンドを実行します。

小技といっても、1回のセル結合のために、昇〇拳ばりにキーを連打する操作がつらいところです。

ただ、セル結合が目的でなくても、連続セルがスクロールを必要とするほど長くて範囲選択が必要なときには便利なワザかもしれません。

 



 

「書式のみ貼り付け」でセル結合する小技

連続セルで「セルの結合」をすると以下の警告が表示されます。

「セルを結合すると、左上の端にあるセルの値のみが保持され、他のセルの値は破棄されます」

f:id:shego:20171023005414p:plain

これで「OK」を押すとセルが結合されますが、そのあとの値は最初のセルの値のみが採用され、下敷きとなった他のセルの値は失われるということです。

ためしに結合セルを解除してみると、一番左上のセル以外が空になっているのがわかります。

それはいいとしても、いちいちこの警告ダイアログが表示されるのは、作業スピードの妨げになりイライラの元です。

調べてみた限り、 この警告を抑制する設定や手段は存在しないようです。(マクロからなら可能)

ここで裏技です。

結合セルをコピーし「書式のみ貼り付け」をすると、結合状態も書式として貼り付けされます。 つまり書式のコピー&ペーストでもセル結合をすることができるのです。

この時警告ダイアログ一切出ません。

コピー元の結合セルは貼り付け先より大きめにとっておくのがポイントです。

操作手順としては、キー操作とマウス操作のやり方が考えられますが、たぶんマウスを使った操作の方が早いでしょう。

【手順例】

  1. 連続セルで一番長そうな範囲を選択します
  2. 普通に「セルの結合」をします
  3. そのまま、「ホーム」リボンの「書式のコピー/貼り付け」を ダブルクリック し、書式貼り付けモードにします
    • 結合セルの書式がコピーされます
    • 十字カーソルの脇にブラシ(刷毛)が表示されます
  4. あとはマウスドラッグで範囲選択するだけで、次々とセル結合していきます
  5. Escキーを押して、書式貼り付けモードを終了します

f:id:shego:20171029020634p:plain

これでストレスなく素早くセル結合することができます。

ただ、通常のセル結合と違う点もありますので注意が必要です。

この手順で結合したセルの結合を解除してみてください。

なんと、セルに結合前の値がそのまま残っています。

f:id:shego:20171028213512p:plain

連続セルにとっては、データが残るのでその方が好都合なのですが、 Excel が通常のセル結合でそれを許さないからには、なにかよくない影響があるのかもしれません。

セル結合を手早くやる方法については以下の記事にまとめましたのでご参考にしてください。「書式のコピー/貼り付け」を使った応用もあります。 www.shegolab.jp

 



 

連続セルを結合するマクロ

連続セルのセル結合を自動化するマクロを作成してみました。

マクロは二つ用意しました。

一つは連続セルを一括で結合するもので、もうひとつは逆にセル結合を解除して連続セルに復元するものです。

タテ方向(列)の連続セルのみ対応します。

f:id:shego:20171029010028p:plain

連続セルの結合_タテ」マクロは同じ値が縦(列方向)に続くセル領域を一つに結合するものです。 対象セル範囲を選択した状態でこのマクロ VBA を実行すると、選択範囲の各列内にある連続セルが一括で結合されます。 また、空白セルは上のセルと同じ値を持つものと見なし、一緒に結合されます。

連続セルの結合解除_タテ」マクロは選択範囲内のセル結合をすべて解除します。 Excel の「セル結合を解除」コマンドとの違いは、解除されたセルが空白にならずに、結合セルの値で縦方向に充填されて連続セルとなるところです。 対象範囲が結合セルでなくても、空白セルがあるところは上のセルの値の連続セルになります。

Option Explicit

Sub 連続セルの結合_タテ()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    enableUpdating False
    forEachSameValueInColumns Selection, "doMerge"
    enableUpdating True
End Sub

Sub 連続セルの結合解除_タテ()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    enableUpdating False
    forEachSameValueInColumns Selection, "doUnmerge"
    enableUpdating True
End Sub

Private Sub enableUpdating(updatable As Boolean)
    Application.Calculation = IIf(updatable, xlCalculationAutomatic, xlCalculationManual)
    Application.EnableEvents = updatable
    Application.ScreenUpdating = updatable
End Sub

Private Sub forEachSameValueInColumns(rng As Range, procName As String)
    Dim col As Range
    Dim max As Long
    Dim vals As Variant
    Dim pos As Long
    Dim mrk As Long
            
    For Each col In rng.Columns
        max = col.Cells.CountLarge
        vals = col.Value
        mrk = 1
        For pos = 2 To max
            If vals(pos, 1) <> vals(mrk, 1) And Not IsEmpty(vals(pos, 1)) Then
                Application.Run procName, col.Cells(mrk).Resize(pos - mrk)
                mrk = pos
            End If
        Next
        Application.Run procName, col.Cells(mrk).Resize(pos - mrk)
    Next col
End Sub

Private Sub doMerge(rng As Range)
    If rng.Rows.Count > 1 Then
        Application.DisplayAlerts = False
        rng.Merge
        Application.DisplayAlerts = True
    End If
End Sub

Private Sub doUnmerge(rng As Range)
    rng.UnMerge
    rng.fillDown
End Sub

【使い方】

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします。
  2. 連続セルを含む領域を範囲選択します
  3. 「連続セルの結合」マクロを実行します
  4. 連続セルが一括で結合されます
  5. 「連続セルの結合解除」マクロを実行します
  6. セル結合が解除され連続セルに戻ります

【注意】 本マクロの実行結果を元に戻す(Undo)することはできません。

【暫定追加】データの階層を考慮したい

ご要望があったので、一部改修したマクロを作成してみました。

これまでのマクロでは、データ列の階層関係を考慮しておらず、値の並びによっては、下位(右列)の結合範囲が上位(左列)の結合範囲をまたぐこと(つまり入れ違い)になっていました。

実用上、そのような状況でも対応できるように後々改修しておきたいのですが、とりあえず暫定で、入れ違いにならない版 VBA プログラムを作成してみました。今いまお困りの方はお試しください。

f:id:shego:20181217000114p:plain

ただし、あくまで暫定実装なので「連続セルの結合タテ階層α」とし、十分な検証を経ていませんので、よく結果のご確認をお願いします。

下記VBAプログラムを、最初の上記マクロとは別の新しい標準モジュールにコピー&ペーストしてください。

使い方は同じですが、Ctrlキーを押しながらの複数範囲選択には対応していません。

Undo はできませんが、「連続セルの結合解除_タテ」がそのまま使えるはずです。

Option Explicit

Sub 連続セルの結合_タテ階層_α()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    On Error GoTo failed
    Application.ScreenUpdating = False
    traverseSameValueInColumns Selection, "doMerge2"
failed:
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description, vbOKOnly, "エラーになったら教えてください"
End Sub

Private Sub traverseSameValueInColumns(ByVal targetRng As Range, procName As String)
    Dim curRng As Range
    Dim max As Long
    Dim vals As Variant
    Dim pos As Long
    Dim mrk As Long
    
    Set targetRng = targetRng.Areas(1)
    max = targetRng.Rows.CountLarge
    vals = targetRng.Columns(1).Resize(max + 1).Value
    mrk = 1
    For pos = 2 To max + 1
        If (vals(pos, 1) <> vals(mrk, 1) And Not IsEmpty(vals(pos, 1))) Or pos > max Then
            Set curRng = targetRng.Rows(mrk).Resize(pos - mrk)
            Application.Run procName, curRng.Columns(1)
            If (targetRng.Columns.Count > 1) Then
                traverseSameValueInColumns Intersect(targetRng, curRng.Offset(, 1)), procName
            End If
            mrk = pos
        End If
    Next
End Sub

Private Sub doMerge2(ByVal rng As Range)
    If rng.Rows.Count > 1 Then
        Application.DisplayAlerts = False
        rng.Merge
        Application.DisplayAlerts = True
    End If
End Sub

使ってみて問題がありましたら、コメント等でお知らせください。

まとめ

連続セルの結合は見た目はきれいですがコストがかかります。

ユーザのニーズを鑑みて、Excel 自体がいつか対応してくれることを切に希望します。

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

関連記事

www.shegolab.jp

www.shegolab.jp

www.shegolab.jp

www.shegolab.jp