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

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

セルの結合による弊害

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

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

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

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


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

結果は

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

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

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

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

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

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

ならば、やりましょう。

解説

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

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

import os
import openpyxl as excel
import sys

try:
    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

    p = sys.executable if getattr(sys, 'frozen', False) else __file__
    cd = os.path.dirname(os.path.abspath(p))

    dst_book.save(os.path.join(cd, 'normalize.xlsx'))

except:
    pass

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

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を保存して完了です。

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

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

使用方法

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

1.ページ下部のダウンロードボタンからアプリを入手して適当な場所へ配置します。得体の知れないサイトの実行ファイルなんて使えねーという場合は、ソースコードをコピペしてお使いください。

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

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

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

アプリを入手する

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

利用上のご注意

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

Downloadボタンを押下した時点で注意事項に同意したものとみなします。

excel_merged_cell_normalization.exe

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

openpyxlで別ブックにシートをコピーする - Qiita
Introductionこの記事ではopenpyxlで別ブックにあるシートをコピーする方法を掲載しています。openpyxlでは基本的には別ブックにあるシートを丸ごとコピーすることはできません(…

おわり。