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

皆さんも経験があるでしょう。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を保存して完了です。

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