今日を乗り切るExcel研究所

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

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

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

セル結合の誘惑

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

【手順例】

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

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 にしてみれがどうしても許したくない事態のようなので、なにかよくない影響があるのかもしれません。



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

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

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

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

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

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)することはできません。

まとめ

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

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

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

関連記事

www.shegolab.jp

www.shegolab.jp

www.shegolab.jp