シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > セル-セル結合 > セル結合の解除で開いた空白セルに値を埋め戻したい

セル結合の解除で開いた空白セルに値を埋め戻したい

セル-セル結合 データ

今回は、セル結合を解除したあとの空白セルに値を一括で入力する方法を調べます。

f:id:shego:20210619071356p:plain

セル結合しましたが、何か？

人は何故にデータのセルを結合するのでしょうか。

ひとえにそれは「同じ値のセル範囲をまとめたい」からでしょう。

であるのなら当然、逆の「セル結合の解除」で期待されるのは、元通りに全てのセルが同じ値になっている状態のはずです。

ところが実際には、解除後に値があるのは最初のセルのみで、残りは空白セルからなる穴開きデータとなってしまいます。

つまりセル結合を使うと、データが壊されるのです。

データ作成者は良かれと思って施したセル結合ですが、データ利用者はそれを手作業で修復するという余計な仕事に泣かされることになります。

これがセル結合の嫌われる理由の一つです。

穴開きデータにはならないようにセル結合を解除する方法はないのでしょうか。

結合解除しつつ空白セルを補完するには

Excel に例えば「セル結合の解除とフィル」のような機能がればいい話なのですが、探してもみあたりません。

仕方がないので既存の機能を組み合わせて、セル結合を解除しつつデータを補完する簡単な方法がないか考えます。

本記事では以下の２つの手順を試してみたいと思います。

「フィル」を使う

Excel の「フィル」機能には「下方向へコピー」や「右方向にコピー」といった便利な機能があるのでこれらを活用してみることにします。

f:id:shego:20210517111600p:plain

フィルの基本操作

フィルは特定の値や連番などを空白セルに一括で入力する機能の総称です。

「下方向へコピー(Ctrl+D)」や「右方向にコピー(Ctrl+R)」は隣接する空白セルに先頭セルの値を一括でコピーします。

「セル結合の解除」をすると、元のセル範囲が選択状態になるので、そのまま「下方向へコピー」や「右方向にコピー」をすれば、穴開きセルの値を取り戻すことができます。

f:id:shego:20210509112126p:plain

f:id:shego:20210509113242p:plain

f:id:shego:20210614080919p:plain

ショートカットは、Ctrlと下方向が Down の D、右方向が Right の R で覚えやすいです。

コピーの方向を間違えると、意図しないセルの値が入力されてしまうので注意してください。

簡単とはいえ、解除したい結合セルがいくつもあると、「セル結合の解除」コマンドが深いところにあるので、アクセスキー（Alt＋H⇒M⇒U）を使っても毎回呼び出すのは結構おっくうになります。

複数のセル結合を解除

複数の結合セルを範囲選択して一括で解除すれば良さそうです。

しかし実際にやってみると、結合セル間の並びかたによって、そのあとのフィルがうまくいきません。

例えば、隣りあった列方向のセル結合を解除する場合、横並びなら問題ないのですが、縦並びになっていると先頭の結合セルの値が隣の結合セルの範囲にまでコピーされてしまうのです。

f:id:shego:20210614091431p:plain

これを防ぐには複数の結合セルを範囲選択するのではなく、個別選択（Ctrl キー＋クリック）を使います。

ただそれだけではまだ上書きを防げません。

そこでさらに、別の列にあるセルを一つおまけで選択に追加します。 （行方向のセル結合の場合は別の行にあるセルをおまけで選択）

その後結合を解除しフィルを実行すると、一括で正しくセルが補完されます。

f:id:shego:20210614090207p:plain

どういうことかというと、複数の結合セルの並びが縦横にきれいにそろっていると上書きコピーになり、１か所でも段差や不揃いがあると個別コピーになるということのようです。

【手順例】

  1. 結合セルを個別選択します
    • Ctrl キーを押しながら結合セルを選択していきます
  2. 結合セルとは別の列・行にあるセルを一つ、選択に追加します【重要】
    • 結合セルが不揃いに並んでいるところがあればこれを省略できます
  3. 「セル結合の解除」を実行します
    f:id:shego:20210514132938p:plain
    • メニュー：「ホーム」タブ⇒「配置」セクション⇒「セル結合の解除」
    • アクセスキー：Alt + H⇒M⇒U
  4. そのまま、「下方向へコピー」や「右方向にコピー」を実行します
    f:id:shego:20210514133445p:plain
    • メニュー：「ホーム」タブ⇒「編集」セクション⇒「フィル」メニュー⇒「下方向へコピー」or「右方向にコピー」
    • ショートカット
      • 下方向へコピー：Ctrl + D
      • 右方向へコピー：Ctrl + R
  5. Esc キーを押して選択状態を解除します

これでも結合セルが10個ぐらいならいいのですが、もっと大量にあると１個１個の結合セルをクリックで選択していくのもしんどくなります。

入りくんだセル結合がたくさんある表や、何回もスクロールするほど大きなデータシートだったりするともうやっていられませんね。

セル結合を一括選択する

「書式の検索」を使えば、シート上の結合したセルを一括で個別選択状態にすることができます。

セル結合とは結局セルの書式の一種なので、検索条件としてセル結合状態の書式を対象に検索すれば、 結果として結合セルのみを選択状態にすることになります。

【手順例】

  1. Ctrl + F を押して、「検索と置換」ダイアログを開き「オプション」ボタンを押します f:id:shego:20210514180606p:plain
  2. 「書式」ボタンを押して「書式の検索」ダイアログを開きます
    f:id:shego:20210615010227p:plain
  3. 「書式の検索」ダイアログで「配置」タブを開き、「セルを結合する」にチェックを入れて「OK」ボタンを押します
    • チェックボックスが「■」になっていますが、かまわずクリックして「✓」を入れます f:id:shego:20210514180820p:plain
  4. 「検索と置換」ダイアログで「すべて検索」ボタンを押します
    • 検索結果一覧に、シート内の全ての結合セルが表示されます f:id:shego:20210615004916p:plain
  5. 検索結果一覧で項目を全て選択します（Ctrl+ A）
    • シート上の全ての結合セルが選択状態になります f:id:shego:20210615005039p:plain
    • ところどころグレー表示が抜けていますが、ちゃんと結合セルが選択されています
  6. そのまま（ダイアログを閉じなくていいです）結合セルとは別の列・行にあるセルを一つ、Ctrlキー+クリックで選択に追加します【重要】
    • 上図のように、結合セルが不揃いになっているところがあればこれを省略できます
  7. そのまま「セル結合の解除」を実行します
  8. そのまま「下方向へコピー」や「右方向にコピー」を実行します
  9. 「書式」プルダウンから、「書式検索のクリア」を選択し「閉じる」ボタンを押します
    • セル結合の検索条件設定が解除されます
    • 前のステップで「検索と置換」ダイアログを閉じてしまっていたら再度. Ctrl + F を押して開きなおしてクリアしてください f:id:shego:20210615011323p:plain

最後の「書式検索のクリア」を忘れずに行ってください。 そうしないと、次に何かを検索したときに何も引っかからないことになります。

シート全体ではなく、一部の領域に含まれる結合セルのみを選択したい場合には、あらかじめその領域を範囲選択してから検索を実行してください。

　

　

Power Query を使う

セル結合を解除したいのは、集計や分析のためにデータを取り込みたいからでしょう。

もしデータを Power Query に取り込むつもりなら、Power Query 自体に空白セルを補完する機能があるので余計な手作業は必要なくなります。

また、表形式のセル範囲は Power Query で取り込むと Excel テーブル化されるのですが、セル結合の解除もこの時に自動で行われます。

【手順例】

  1. データ範囲内のセル（結合セル以外）を選択してデータの取得を実行します
    • メニュー：「データ」タブ ⇒「データの取得と変換」セクション ⇒「テーブルまたは範囲から」
      • Excel 2016 では「取得と変換」にあります
    • ショートカット：App + G f:id:shego:20210615013712p:plain
  2. 「テーブルの作成」ダイアログで見出し行のありなしをチェックして「OK」ボタンを押します
    • データ範囲が Excel テーブルに変換され、セル結合は解除されます f:id:shego:20210615013756p:plain
  3. Power Query エディターが開きます
    • データのプレビューではセル結合だったセルが null になっています（下図）
  4. データのプレビューでセル結合があった列の見出しをクリックして選択状態にします
    • 複数列を選択するにはCtrlキーを押しながら見出しをクリックします（下図）
  5. Power Query エディターの「変換」タブ⇒「任意の列」セクション⇒「フィル」メニューから「下」を実行します
    • null の上にある値が下方向にコピーされます f:id:shego:20210615015013p:plain
  6. Power Query クエリーエディタの「ホーム」タブ⇒「閉じて読み込む」メニューから「閉じて次に読み込む」を実行します
  7. 「データのインポート」ダイアログでそのまま「OK」ボタンを押します f:id:shego:20210517020131p:plain
  8. 新規シートに補完されたデータが Excel テーブル（緑のシマシマ）として作成されます

もし値として空白がありうる（NULL可な）列では、その空白セルも上の行値で埋められてしまうので注意してください。

Power Query は「データの取得と変換」使うときに呼び出される技術のことで外部からデータを取り込んだり加工するための機能です。Excel 2013 以降で使用できます。（Excel 2013 ではアドインとして提供）

Power Query で作成されたテーブル（緑のシマシマ）はクエリという元データとの関連が残っていて、いつでも変更内容で更新することができます。

変換後のデータだけが必要でクエリ自体はもはや不要なら、画面右の「クエリと接続」から右クリック⇒「削除」で削除できます。 　 　

　 　

セル結合を解除して値を補完するマクロ

空白セルなしでセル結合を解除するマクロを作成しました。

結合セルを選択した状態でこのマクロを実行すると、範囲内の全てのセル結合解除したうえで解除セルには元の値を補完します。

複数ある結合セルの指定はマウスドラック等で範囲選択したセル範囲にある全ての結合セルを一括で解除します。個々に個別選択する必要はありません。

Option Explicit

Sub セル結合の解除とフィル()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    
    Dim rng As Range
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    If rng Is Nothing Then Beep: Exit Sub
    
    Application.ScreenUpdating = False
    Call forEachMergedRange(rng, "unmergeAndFillValue")
    Application.ScreenUpdating = True
End Sub

Private Sub forEachMergedRange(rng As Range, proc As String)
    Dim col As Range
    Dim cur As Range
    Dim nxt As Range
    Dim outOfRange As Long
    
    For Each col In rng.Columns ' 列方向優先
        If IsNull(col.MergeCells) Or col.MergeCells Then
            outOfRange = col.Row + col.Rows.Count
            Set cur = col.Cells(1)
            Do
                Set nxt = cur.Offset(1, 0)
                If cur.MergeCells Then
                    If cur.MergeArea.Cells(1).Address = cur.Address Then
                        Application.Run proc, cur
                    End If
                End If
                Set cur = nxt
            Loop While nxt.Row < outOfRange
        End If
    Next
End Sub

Private Sub unmergeAndFillValue(mergedCell As Range)
    With mergedCell.MergeArea
        .UnMerge
        .Value = .Cells(1).Value
    End With
End Sub

【使い方】

  1. 上記VBAプログラムを新規作成した標準モジュールにコピー＆ペーストします
    • データとは別に用意したワークブックにマクロ登録することをお勧めします
  2. 結合セルを選択します
    • 範囲選択や列選択、行選択をすることで複数の結合セルを一括で処理でできます。
  3. マクロを実行します
  4. セル結合が解除され、セル値も補完されます

シート全体を処理対象としたい場合には、全選択（Ctrl+A）後に実行してください。

結合解除後のセル範囲のスタイルや書式は基本的に元のセルからコピーされているようですが、罫線は再現されません。

【注意】 マクロによるデータの変更は元に戻す（アンドゥ（Ctrl+Z））することができません。 必ず元データをコピーまたはバックアップを取ってから本記事のマクロを実行してください。
【免責】本記事のマクロの不具合や誤操作に起因するいかなる損害・損失についても本ブロクの筆者は責任を負いかねますのであらかじめご了承ください。

まとめ

筆者自身は一部世間で言われるほどセル結合が悪いことだとは思っていません。

冗長な値表示を結合セルにまとめれば、値と適用範囲を分かり易く示せます。

データを分かり易く見せたいと思うのは人間にとって自然な心情であり、その努力と工夫は称賛されはしても一概に非難はできないと思います。

問題は、結合解除でデータが壊れたり、コピー＆ペーストが思うようにできない、ソートできないなど、セル結合に対する Excel 側の操作性の悪さの方にあると考えます。

データ活用者としては、セル結合の解除の機能として、同一値を補完するようなコマンドなりオプションなりが Excel に用意されるだけでもだいぶマシになるはずです。

もっと言えば、結局は見た目だけの問題なので、見かけ上セル結合のようで操作を阻害せずにデータの毀損も起こさないような、柔軟で動的な表示形式があったら理想的で、ニーズもあると思うのですが、どうでしょうか、エクセルさん。

本記事の操作とマクロは Window 10 環境の Office 365 版 Excel で検証しました。

関連記事

www.shegolab.jp

www.shegolab.jp