結合セルで集計できない表を何とかする

人間にしか読めない表をPCで使えるように正規化します。

皆さんも経験があるでしょう。Excelでコピペ、オートフィルを使おうとすると紛れ込んできて妨害するアイツ。そう、今回の主役はその結合セルです。

セルの結合による弊害

いわゆる神エクセルでもっとも駆使されているであろう「セルの結合」

連続する同じデータがある表を人間にとって見やすく体裁を整えようとすると、次のようにしたくなります(画像のデータはさいたま市のWEBサイトで公開されているExcelデータを加工したものです)。

merged_normalize1.png

区名が同じところを結合セルで表現しています。たしかに印刷して人間が見るだけの用途であれば、これはスッキリしていて見やすいです。

しかし、セルの結合を使ったその瞬間からコンピューターが扱う「データとしての価値」はなくなります。Excelは結合されたセルが同じ値をグループ化したものだとは認識しません。


上の表で区ごとに乗車人員の合計を出したいとします。集計表を別に作ってSUMIF関数で集計してみます。

merged_normalize2.png

結果は

merged_normalize3.png

浦和区の合計が与野駅しか計上されません。

このことからわかるように、結合セルで実際に値をもっているのは一番上(列方向にも結合されていれば一番左)の1セルだけなのです。

画像の場合見た目はA3~5というセルが1つあるように見えますが
内部的には【A3は”浦和区”、A4は空白、A5は空白】という状態になっています。

正しく計算するためには次のような表でなければなりません。

merged_normalize4.png

このような任意の1行ですべての情報が完結している表を「正規形」といいます。対して、4行目のA列の値は3行目のA列を参照しなければわからないような表は「非正規形」といいます。集計などの計算をしたり、データベースとして使用したりするためには正規形へと正規化されている必要があるのです。

結合セルを含む表の正規化は「結合範囲の一番左上のセルの値を結合を解除して他のセルへコピーする」というロジックで行えます。例外はなく、極めてプログラムによる自動化に適している作業です。

ならば、やりましょう。

解説

VBAでやってもいいでしょう。ググってみると、やっている方がいらっしゃいます。「それ、使えばいいじゃん」だと終わってしまうので、このWEBサイトのコンセプトであるExcel×Pythonを実践し、ここはPythonスクリプトでやってみます。Excelファイルの処理はOpenPyXLを利用します。

ロジックは先述の通りなので、あとはコーディングしていくだけです。ソースコードは次のようになりました。

import openpyxl as excel
import sys

file = sys.argv[1]
src_book = excel.load_workbook(file, data_only=True)
sheet_names = src_book.sheetnames
dst_book = excel.Workbook()

for cnt, sn in enumerate(sheet_names):
    if cnt == 0:
        dst_sheet = dst_book.active
        dst_sheet.title = sn
    else:
        dst_sheet = dst_book.create_sheet(sn)

    src_sheet = src_book[sn]
    merged_ranges = src_sheet.merged_cells.ranges

    for row in src_sheet:
        for cell in row:
            dst_sheet[cell.coordinate].value = cell.value

    for mr in merged_ranges:
        src_val = src_sheet.cell(mr.min_row, mr.min_col).value
        for row in range(mr.min_row, mr.max_row + 1):
            for col in range(mr.min_col, mr.max_col + 1):
                dst_sheet.cell(row, col).value = src_val

dst_book.save('normalize.xlsx')

すこし詳しく見ていきましょう。

src_book = excel.load_workbook(file, data_only=True)
sheet_names = src_book.sheetnames
dst_book = excel.Workbook()

ExcelファイルはOpenPyXLにより変数src_bookへ格納されます。load_workbookメソッドの引数data_onlyにTrueを渡すことで、計算式の場合は評価した結果を取得します。

src_bookのシート名をsheet_namesへ格納しています。

開いたファイルとは別に出力用のワークブックを新たにdst_bookとして作成しています。

事前準備が整ったところで以降の正規化処理へ移ります。

for cnt, sn in enumerate(sheet_names):
    if cnt == 0:
        dst_sheet = dst_book.active
        dst_sheet.title = sn
    else:
        dst_sheet = dst_book.create_sheet(sn)

    src_sheet = src_book[sn]
    merged_ranges = src_sheet.merged_cells.ranges

開いたワークブックからシート名をsheet_namesとしてListで取り出しているので、forで総当たり処理していきます。要するにすべてのシートへ処理を実行します。

ループのインデックスをcntとしてカウントしているのは、初回ループのみアクティブシートへ処理を、2回目以降のループでは新しくシートを作成して、そのシートに処理をするようにしているからです。はじめから新規でシートを追加していくと、完成したブックに空のシートが1枚できてしまうのを回避する策です。力業でちょっとスマートじゃないですが。

merged_cells.rangesでシートのすべての結合セルの情報をCellRangeオブジェクトのListで取得できます。CellRangeオブジェクトはセル範囲をしめすOpenPyXLのクラスで、どこからどこまでが結合セルなのかをmax_row,min_rowといったプロパティから取得できます。

for row in src_sheet:
    for cell in row:
        dst_sheet[cell.coordinate].value = cell.value

for mr in merged_ranges:
    src_val = src_sheet.cell(mr.min_row, mr.min_col).value
    for row in range(mr.min_row, mr.max_row + 1):
        for col in range(mr.min_col, mr.max_col + 1):
            dst_sheet.cell(row, col).value = src_val

メインのロジックですが、まず開いたワークブックのシート(以下src)から新しいワークブックのシート(以下dst)へ値のみを丸ごとコピーします。

この状態ではdstに結合セルは存在していません。つまりdstはsrcの結合セルがすべて解除された状態と同じということです。

そこでmerged_rangesとしてsrcの結合セルの範囲をListで取ってきていますので、これを総当たりで回していきます。

srcの結合セルの一番左上のセルの値をsrc_valとして確保します。そしてその値をdst側のsrc結合セルに対応するセル範囲すべてへ書き込んでいきます。

これでdstが【srcの結合セルを解除し、かつ結合範囲の空白セルを左上のセルの値で補完した状態】となりました。

最後にdstを保存して完了です。

最初の表を処理した結果はこのようになります。

merged_normalize5.png


今回はできるだけ解説サイトに頼らないでOpenPyXLのリファレンスを見ながらやりましたが、いや~OpenPyXLの完成度、半端ないですね。天才達が作っているんでしょうね。リファレンスもすごくわかりやすかったです。

使用方法

このアプリに含まれるOpenPyXLモジュールはデフォルトではXMLの脆弱性を利用した攻撃を防ぐことはできません。信頼できないExcelファイルを処理しないでください。

拡張子xlsのファイルは処理できません。xlsxに保存しなおすか、あきらめてください。

1.ページ下部のダウンロードボタンからアプリを入手して適当な場所へ配置します。

merged_normalize6.png


2.アプリへ対象のExcelファイルをドラッグ&ドロップします。
コマンドプロンプトの黒い画面が一瞬見えると思いますが、こいつの仕業です。ウイルスではありません。ご安心ください。

merged_normalize8.png


3.Excelファイルと同じ場所へ、処理した結果が【normalize.xlsx】というファイルで出力されます。

merged_normalize9.png

あとは、データを整形して有効活用しましょう。

アプリを入手する

利用上のご注意
  • ダウンロードしたファイルを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • Windows10-64bit Excel2016-32bit環境でのみ動作確認済み。

excel_merged_cell_normalization.exe

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

ソースコードから実行する

得体の知れないサイトで拾った実行ファイルなんて使えねーよというかたは、ソースコードから実行できます。ダウンロードボタンからソースコード(.pyファイル)を入手してください。

対象のExcelブックをコマンドラインから引数でわたすか.pyファイルにドラッグ&ドロップすると実行されます。詳細は次の記事を参考にしてください。

ソースコードをダウンロード

excel_merged_cell_normalization.py

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

参考サイト

OpenPyXLでシートを別のワークブックにコピーする方法はこちらのサイトのやり方を参考にしています。

おわり。

related pages
ExcelをSQLiteへ変換するPythonスクリプト
ExcelシートをSQLiteのテーブルへ変換。

SQLiteをはじめとしたデータベースはプログラマーではないユーザーが直接操作をするのが難しいというデメリットがあります。故にUIを用意して、それを操作してもらう仕組みにするのがセオリーだと思います。これってすごい面倒なんですよね。Excelから一発でデータベースに変換できたら便利だね、というお話です。

Read More ...
ExcelブックからPythonでメールを送る
PythonでExcelブックのリストからメール送信。

当然といえば当然なのですが、Pythonでメールの送受信をすることができます。それもBattery Includedの理念にあるように、標準モジュールのみの構成で実現できます。ここに神ツールOpenPyXLを組み合わせると、Excelシートにあるメールアドレス一覧へ片っ端からメールを送信するスクリプトができあがります。

Read More ...
VBAでファイルを開くのが遅すぎるのでPythonで無理矢理に高速化する
VBAとPythonを連携させてファイル読み込み高速化。

我々VBAerにとっては周知の事実ですが、Excel-VBAで時間がかかる処理と言えば、「ワークシートへのアクセス」と「ファイルを開く」です。Pythonでデータ取得してVBAでExcelへ取り込むことでファイルを開く処理を高速化します。

Read More ...
この記事の
作成日

2019-11-09

更新日

2020-07-12

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園