Excel 方眼紙を解消したい

今回は、Excel 方眼紙上にセル結合で作られた表からデータを取り出しやすくするための整形方法を調べます。

f:id:shego:20220213180349p:plain

Excel 方眼紙からデータを取り出したい

セル結合でガチガチに固められた Excel 方眼紙というのはやっかいです。そこから単純にデータを取り出すだけでも苦労させられます。

まずコピー＆ペーストでは単純にうまくいきません。それならと、一括でセル結合の解除すれば、今度はバラバラに散らばったデータを集めてこなくてはなりません。方眼紙のマス目にはデータが収まりきれず、はみ出したり「##」になったりして、どこに何のデータがあるのか判りません。

そんなデータを拾い集めてくるなんて、余計な仕事に時間を取られたくありません。

ガチ結合の方眼紙から効率的にデータを取り出す方法はないのでしょうか。

Excel 方眼紙を解消するには

発想を変えてみます。

値のあるセルを拾い集めるのではなく、余分な行や列のほうを削除するというのではどうでしょうか。

結合されたセル範囲のうち値があるのは左上端にある１セルだけで、残りは空白のままになっています。セル結合で組まれた表には、空白セルしかない吹き抜けの行や列があり、そこは削除しても問題ありません。

f:id:shego:20220211142054p:plain

つまり、空白行や空白列のみを一括で削除できる方法があれば、少なくとも値のあるセルのみを寄せ集めることができるはずです。

しかし残念ながら、 Excel にそのような機能はありません。

この記事では Excel の既存機能を工夫して、なんとか空白行と空白列の削除ができないか考えてみます。

並べ替えと条件付き書式で値のある行と列を寄せる（Excekl2013まで）
Power Query で空白行と空白列を削除する（Excel 2016 以降）
LAMBDA 関数を使って数式で値のある行と列を寄せる（Microsoft 365）
空白列と空白行を除去するマクロ

以降サンプルシートとして、ちょっとわざとらしいですが以下のような方眼紙の表を使います。

【方眼紙イメージ】

f:id:shego:20220214002806p:plain

並べ替えと条件付き書式で値のある行と列を寄せる（Excekl2013まで）

色々な方法が考えられるのですが、ここでは「並べ替え」と「条件付き書式」を組み合わせてみます。

考え方としてはこうです。

「条件付き書式」で吹き抜けにあるセルを数式で検出し、空白行と空白列を色付けします
行と列に対し「セルの色」を基準に「並べ替え」をして、色のない（=値のある）行・列のみを寄せ集めます

空白行・列を削除するのではなく、後ろにまわしてしまいます。並べ替えは行だけでなく列に対しても行えるのがポイントです。

【手順例】

方眼紙を解除したい対象シートをコピーして作業用シートとします
先頭行と先頭列を新規追加します
- Ctrl+Home ⇒ Ctrl++
A1セルに、適当な値（例えば1）を入力しておきます【重要】
シート上の使用範囲を選択状態にします
- ショートカット：Ctrl+Home ⇒ Ctrl+Shift+End
- 以降のステップではこの選択状態を変えないまま作業します
シート全体で「セル結合の解除」をします
- コマンド：「ホーム」タブ⇒「セル結合」メニュー⇒「セル結合の解除」
- アクセスキー：Alt⇒H⇒M⇒U
表内に数式がある場合は値のみに変換しておきます
- いったんコピーしてから「値のみ貼り付け」で上書き
- ショートカット：Ctrl + C ⇒ App ⇒Tab ⇒ V
空行と空列に色をつける条件付き書式を設定します。
1. 条件付き書式の「新しい書式ルール」ダイアログを開きます
  - コマンド：「ホーム」タブ⇒「条件付き書式」メニュー⇒「新しいルール」
  - アクセスキー：Alt⇒H⇒L⇒N
2. 以下のような条件付き書式のルールを追加します
  - 「ルールの種類を選択してください」：「数式を使用して、書式設定するセルを決定」
  - 「次の数式を満たす場合には対応書式設定」：以下の数式をコピー＆ペースト
    =OR(COUNTA(A:A)=0, COUNTA(1:1)=0)
  - 「セルの書式設定」：「塗りつぶし」⇒ 「背景色」⇒ 好きな色
3. 「OK」ボタンを押すと、空白行・列が色付けされている状態になります（１行目とA列目が色分けされている）
セルの色で列の並べ替えをします
1. 並べ替えダイアログを開きます
  - コマンド：「ホーム」タブ⇒「並べ替えとフィルター」メニュー⇒「ユーザ設定の並べ替え」
  - ショートカット：Alt+ D⇒S
2. 「オプション」ボタンを押して「並べ替えのオプション」ダイアログを開きます
3. 「方向」に「列単位」を選択し、「OK」ボタンを押します
4. 以下のような並べ替え条件を設定し、「OK」ボタンを押します
  - 「最優先されるキー」：「行１」
  - 「並べ替えのキー」：「セルの色」
5. 並べ替えダイアログを開きます
6. 「オプション」ボタンを押して「並べ替えのオプション」ダイアログを開きます
7. 「方向」に「行単位」を選択し、「OK」ボタンを押します
8. 以下のような並べ替え条件を設定し、「OK」ボタンを押します
  - 「最優先されるキー」：「列Ａ」
  - 「並べ替えのキー」：「セルの色」
値のある行と列が色のないセルとして寄せられている状態です
条件付き書式を解除します
- 「ホーム」タブ⇒「書式」メニュー⇒「ルールのクリア」
値が確認できるように列幅や行の高さを調整し、セルの書式やスタイルを修正します
- 「ホーム」タブ⇒「書式」メニュー⇒「行の高さの自動調整」/「列の幅の自動調整」

【結果イメージ】

f:id:shego:20220211152111p:plain

手順として書き出すと長くなって面倒くさそうですが、やっていることが解ればそれほどでもないと思います。

コツはセル範囲の選択状態を解除しないでことです。もし途中で選択状態を変えてしまったら、Ctrl+Home⇒Ctrl+Shift+Endで選択しなおしてください。またもしどこかで間違えても、「元に戻す」（Ctrl+Z）でステップを戻せばやり直せます。

色付けの条件に使った数式は、そのセルの位置する行と列のどちらかが吹き抜けになってると TRUE を返します。セルの色による並べ替えでは、「セルの色なし」が最優先のため、値のある行と列のみが左上に寄せられ、結果的に空白行・列を削除したのと同じことになります。 Excel の並べ替えのアルゴリズムは安定ソートなので、同じ色のセルなら位置関係（順番）が崩される心配はありません。

注意点としては、並べ替えで数式、図形、罫線等が壊されてしまうので、あらかじめ除去しておいた方がいいでしょう。

この手順を何度も行う場合、一度「マクロの記録」しておけば、あとは自動化できると思います¹。ただしその場合は、「元に戻す」ことができませんので注意してください。

さてこれでだいぶデータが取り出しやすくなったと思います。

ただ、集計や分析の元データとして供するにはこれだけでは不十分で、さらにゴミデータの削除、空白セルの埋め戻し、書式や型の変換、表構造の組み換えといった修正・加工の仕事が待っています。

最近の Excel 上級者なら迷わず Power Query を活用するところでしょう。どうせなら、空白行・列の削除も始めから Power Query でできてくれればうれしいです。

Power Query で空白行と空白列を削除する（Excel 2016 以降）

Power Query は Excel 2016 以降で標準サポートされた高度なデータの取得・変換機能で、様々なデータ加工コマンドを組み合わせ、柔軟で自動化されたクエリの実行環境を提供しています。

Power Query には「空白行の削除」というコマンドならあるのですが、空白列の方の削除機能がありません。ほかのコマンドを組み合わせても、空白列の削除はできないようです。

そこで、セル範囲から空白列と空白行を削除してデータを抽出するというクエリを作成してみました。これで Power Query でも方眼紙からデータを抽出することがでるはずです。

let
    removeBlankRows = (t as table) => Table.SelectRows(t, each List.MatchesAny(Record.FieldValues(_), each _ <> null)),
    removeBlankColumns = (t as table) => let 
        cols = List.Select(Table.ColumnNames(t), each List.MatchesAny(Table.Column(t, _), each _ <> null))
    in
        Table.SelectColumns(t, cols),
    src = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    aaa = removeBlankRows(src),
    zzz = removeBlankColumns(aaa)
in
    zzz

【使い方】

対象のシートを新規ワークブックにコピーし、作業用シートとします
作業用シートで、空列空行を削除したいセル範囲を選択し「テーブルまたは範囲から」を実行します
- 「テーブルの作成」ダイアログでは先頭行を見出しとするチェックはオフのままでＯＫです
- 結合セルがあっても、自動的に解除されます
- 数式があっても値として抽出されるので問題ありません
「Power Query エディター」が開きます
1. 「詳細エディター」を開き上記クエリをコピー＆ペーストします
2. 「完了」ボタンを押します
「閉じて読み込む」を押して、シートにデータを展開します

【結果イメージ】

f:id:shego:20220211153503p:plain

あとは通常のクエリのように必要な加工ステップを追加してしていきます。ゴミデータの除去、データ型の変換、先頭行のヘッダ昇格、Nullセルのフィル補完、ピボットの解除等の加工が必要になるでしょう。「並べ替え」を使った方法とは違い、最初にデータ範囲を指定できるので、シート全体ではなく必要なデータがある範囲に絞っておけばゴミデータを削除する手間を減らせます。

このクエリは、新規ブックにデータがコピーされていることを前提にテーブル名を決め打ちにしているので、使い回すことができません。

そのかわり、空白行や空白列を削除するロジックは関数にしてあるので、これらをカスタム関数として登録しておけば別のクエリからでも１ステップとして呼び出せるはずです。

LAMBDA 関数を使って数式で値のある行と列を寄せる（Microsoft 365）

最新の Microsoft 365 版の Excel を使用されている場合、数式のみで値のある行と列を寄せ集めることができます。

最近 Microsoft 365 版 Excel に追加された新関数である、LAMBDA 関数とそれに関連したヘルパー関数を使います。

LAMBDA 関数というのは、関数を作成する関数という特殊な関数です。「LAMBDA」は「ラムダ」と読みます。

今までは数式で使えるユーザ独自の関数（「ユーザ定義関数」あるいは「カスタム関数」とも）を作成するには、VBA プログラムとして用意するしかなかったのですが、これからは LAMBDA 関数により、VBA を使わずとも数式のみで関数の作成、つまりプログラミングができるようになりました。

LAMBDA 関数の導入に伴って、それを活用する便利なヘルパー関数もいくつか追加されました。これらは、LAMBDA 関数によって作成されたカスタム関数を引数にとり、セル範囲や配列の値を一括して処理できるようにするものです。

今回はそのうち、行ごとの処理をする BYROW 関数と列ごとの処理をする BYCOL 関数を使ってみました。

=LAMBDA(rng,
    LET(
        cs, BYCOL(rng, LAMBDA(c, COUNTA(c)>0)),
        rs, BYROW(rng, LAMBDA(r, COUNTA(r)>0)),
        FILTER(FILTER(rng,cs),rs)
    )
)(A1:C3)

【手順例】

任意のセルに上記数式をコピー＆ペーストします
数式の最後のかっこ内（A1:C3となっているところ）に方眼紙のデータのセル範囲を入力します

【結果イメージ】

f:id:shego:20220220104838p:plain

上記数式は長くて扱いづらいかもしれませんが、これを「名前の定義」に登録しておくと、通常の関数の様に使えるようになります。

「数式」タブにある「名前の定義」に以下のように入力して、数式に名前を付けてください。以降、その名前を関数名として数式で仕様できます。

名前任意。例えば「SHRINK」、「PACK」、あるいは日本語で「空白行列削除」、「データ寄せ」、「脱方眼」など
コメント 「空白行と空白列を削除します」
参照範囲 以下の数式をコピー＆ペースト

=LAMBDA(範囲,
    LET(
        cs, BYCOL(範囲, LAMBDA(c, COUNTA(c)>0)),
        rs, BYROW(範囲, LAMBDA(r, COUNTA(r)>0)),
        FILTER(FILTER(範囲,cs),rs)
    )
)

f:id:shego:20220220123529p:plain

f:id:shego:20220220125128p:plain

空白列と空白行を除去するマクロ

空白列と空白行を除去して Excel 方眼紙を解消するマクロを作成してみました。

セル範囲を選択して本マクロを実行すると、位置合わせのためだけに使われている無用な空白列や空白行が削除されます。

ただ削除するだけではありません。

このとき、処理後の見た目がなるべく変わらないよう、できるだけ元のデータの構造と体裁を維持します。例えばセル結合が多用された方眼紙でも、値のあるセルのサイズが元の結合セルから引き継がれます。また最小限のセル結合が残されます。

マクロは空白行と空白列の両方を除去する「空白行と空白列の除去」マクロと、空白列のみを除去する「空白列の除去」マクロの２つを用意しましたので、用途によって選んで実行してください。

Option Explicit

Sub 空白行と空白列の除去()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    
    Dim rng As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Selection.Areas(1))
    If rng Is Nothing Then Beep: Exit Sub
    Set rng = rng.Areas(1)
    If rng.Cells.Count = 1 Then Beep: Exit Sub

    Application.ScreenUpdating = False
    ActiveSheet.Copy after:=ActiveSheet
    Set rng = Range(rng.Address)
    Call unifyBlankColumns(rng)
    Call unifyBlankRows(rng)
    rng.Select
    Application.ScreenUpdating = True
End Sub


Sub 空白列の除去()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    
    Dim rng As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Selection.Areas(1))
    If rng Is Nothing Then Beep: Exit Sub
    If rng.Cells.Count = 1 Then Beep: Exit Sub

    Application.ScreenUpdating = False
    ActiveSheet.Copy after:=ActiveSheet
    Set rng = Range(rng.Address)
    Call unifyBlankColumns(rng)
    rng.Select
    Application.ScreenUpdating = True
End Sub

Private Sub unifyBlankColumns(rng As Range)
    Dim blanks As Range
    Set blanks = collectBlankRanges(rng.Columns)
    If blanks Is Nothing Then Exit Sub
    If blanks.Column = 1 Then
        rng.Worksheet.Range("A:A").Insert
        rng.Worksheet.Range("A:A").ColumnWidth = 0
    End If
        
    Dim i As Long
    For i = blanks.Areas.Count To 1 Step -1
        Dim blk As Range
        Dim pre As Range
        Dim wpt As Double
        
        Set blk = blanks.Areas(i)
        Set pre = blk.Offset(, -1).Columns(1)
        wpt = Union(pre, blk).Width
        pre.ColumnWidth = blk.Cells(1).ColumnWidth
        pre.ColumnWidth = wpt / pre.Width * pre.ColumnWidth
        pre.ColumnWidth = wpt / pre.Width * pre.ColumnWidth
        pre.ColumnWidth = wpt / pre.Width * pre.ColumnWidth
        blk.EntireColumn.Delete
    Next
End Sub

Private Sub unifyBlankRows(rng As Range)
    Dim blanks As Range
    Set blanks = collectBlankRanges(rng.Rows)
    If blanks Is Nothing Then Exit Sub
    If blanks.Row = 1 Then
        rng.Worksheet.Range("1:1").Insert
        rng.Worksheet.Range("1:1").RowHeight = 0
    End If
    
    Dim i As Long
    For i = blanks.Areas.Count To 1 Step -1
        Dim blk As Range
        Dim pre As Range
        
        Set blk = blanks.Areas(i)
        Set pre = blk.Offset(-1).Rows(1)
        pre.RowHeight = Union(pre, blk).Height
        blk.EntireRow.Delete
    Next
End Sub

Private Function collectBlankRanges(rngs As Range) As Range
    Dim rng As Range
    For Each rng In rngs
        If WorksheetFunction.CountA(rng) = 0 Then
            If collectBlankRanges Is Nothing Then
                Set collectBlankRanges = rng
            Else
                Set collectBlankRanges = Union(collectBlankRanges, rng)
            End If
        End If
    Next
End Function

【使い方】

上記VBAプログラムを新規モジュールにコピー＆ペーストします
- データとは別のマクロ用の新規ワークブックを用意することをお勧めします
方眼紙を解消したいセル範囲を選択します
本マクロを実行します
新規ワークシートが追加され、方眼紙が解消されたデータがコピーされます

【結果イメージ】

f:id:shego:20220211154428p:plain

一見、変化が起きてなさそうに見えますが、よく見ると無用な行と列が除去（融合）されて方眼紙が解消されています。値のある結合セルは列幅と行高さを維持したままほとんどが１セルに変換されています。

一部セル結合は残りますが、それでもだいぶ扱いやすい表になるはずです。

以下注意点です。

空行・空列の判定は、指定した選択範囲のみで行われます。したがって、もし選択範囲外に値があっても行・列ごと削除されることがあります
結合セルの配置によっては最小限のセル結合が残されます
結合セルでも値がなければ（空白ならば）除去されます。構造を残したい結合セルには、あらかじめ何らかの値を入力しておいてください。
A列目と１行目は空でも削除されません
罫線などのスタイルは期待通りに維持されず、多くは壊れます
「元に戻す」（Undo）はできません。必ず、オリジナルではなくコピーしたシートで実行してください

ところで方眼紙を解消するというのはちょっと思わせ振りな言い方だったかもしれません。

もっと複雑な方眼紙で試してみた方は、期待とは違う結果となったでしょう。お役所の文書などにあり勝ちな、細かいマス目と複雑なセル結合でレイアウトされたような非定形のテキスト主体の書類等の方眼紙には対応しきれていません。このマクロが有効なのは、あくまである程度構造化された表形式になっている場合なのです。

そのような方眼紙に対応したマクロにも、もし需要があるようなら別記事の課題として検討したいと思います。

まとめ

Excel 方眼紙からデータを取り出すために、空白行や空白列を削除する方法を調べました。

Excel の基本機能には単純に空白行列を削除する機能が存在しないので、ほかの機能を組み合わせて工夫するしかありません。この記事では「条件付き書式」と「並べ替え」を組み合わせる方法とPower Queryを使う方法を紹介しました²。

たまたま必要に迫られたなら条件付き書式での並べ替えで充分ですが、定期的に発生する業務なら、クエリに固めてしまった方がよいでしょう。

どちらもちょっと面倒くさい方法でしたが、これでも色々考えた中ではマシな方です。

本来 Excel に、単純に空白行や空白列を削除する機能があればそれだけでこと足りる話でした。

あるいは列や行の内容で比較して、全て一致した行や列を選択できるような機能があればもっと楽でした。ちょうど「アクティブ行との相違」とは逆の「アクティブ行との一致」のイメージですが、あれば便利で使い出はあると思います。（Office がサブスクになってから、ずっと痒かったところが地味に改善されてきているようですが、どうでしょう、エクセルさん。）

さて、Excel 方眼紙にはいつも苦労させられます³。それでも筆者はセル結合や Excel 方眼紙に対して世間で強硬に主張されるほど否定的な立場ではありません。使われないことに越したことはありませんが、使われる理由も理解したいと思っています。問題は別のところにあると考えていますが、それはまた別の記事で。

編集履歴

[2022/02/20] LAMBDA 関数を使った方法を追加しました

www.shegolab.jp

対象範囲をSelect しているところを、 ActiveSheet.UsedRangeに差し替えれば、より使い回しがきくようになるでしょう。↩
列の並べ替えなんて初めて使いました。↩
今回方眼紙のサンプルを作るのにも苦労しました。↩

シーゴの Excel 研究室

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

Excel 方眼紙からデータを取り出したい

Excel 方眼紙を解消するには

【方眼紙イメージ】

並べ替えと条件付き書式で値のある行と列を寄せる（Excekl2013まで）

【手順例】

【結果イメージ】

Power Query で空白行と空白列を削除する（Excel 2016 以降）

【使い方】

【結果イメージ】

LAMBDA 関数を使って数式で値のある行と列を寄せる（Microsoft 365）

【手順例】

【結果イメージ】

空白列と空白行を除去するマクロ

【使い方】

【結果イメージ】

まとめ

編集履歴

関連記事