Excelの表をグループごとに別ブックへ切り出す

ある一覧表から特定の値をグループ化した表を別ファイルとして切り出せると便利ですよね。

たとえば、あなたは複数の店舗を経営しています。本店にはすべての店舗情報を網羅した一覧表があります。
各店から何かしらの情報を集めたいときに、地区ごとにグループ化した表を別ファイルとして切り出せれば、それを各エリアマネージャーに送って必要事項を入力してもらうことができます。エリアマネージャーには自分の管轄以外の不必要な情報が渡ることはなく管理上の利点があります。

このようにマスターの表から一部を切り出して渡すという場面は、ぼちぼち登場するのではないでしょうか。複数の表を一つに統合する機能はExcelに標準装備されていたと思いますが（使ったことないですが）、逆に一つの表から複数の表として別ファイルへ切り出すという機能はなさそうなのでプログラムでやります。

VBAでExcel表を切り出す

サンプルとして、我らがさいたま市の人口データを使用します。データはこちらから入手できます。多少装飾をして次のような構成になっています。

処理としては

表のA列を下へ走査していく
走査行とその下の行のA列の値を比較する
同じ値の場合は何もせず下の行へうつる
違う値の場合は走査行を終点として、始点から終点までの行を別ファイルへ切り出す
次の行を始点に設定して１へ戻る

がメインとなります。

ここにヘッダー行の取得、ファイルの生成保存などが加わり次のようなコードになりました。

Option Explicit

Sub main()
    Dim tgtRow As Long
    Dim stRow As Long
    Dim edRow As Long
    
    tgtRow = 2
    stRow = tgtRow
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Dim edCol As Long
    edCol = ws.Cells(1, 1).CurrentRegion.Columns.Count
    
    Dim th As Range
    Set th = Range(ws.Cells(1, 1), ws.Cells(1, edCol))
    
    Do
        If Not ws.Cells(tgtRow, 1) = ws.Cells(tgtRow + 1, 1).Value Then
            edRow = tgtRow
            Call cutOut(th, Range(ws.Cells(stRow, 1), ws.Cells(edRow, edCol)))
            stRow = tgtRow + 1
        End If
        
        tgtRow = tgtRow + 1
    Loop Until ws.Cells(tgtRow, 1) = ""
End Sub

Sub cutOut(th As Range, td As Range)
    Dim wb As Workbook
    Set wb = Workbooks.Add
    
    Dim row As Long
    Dim col As Long
    row = td.Rows.Count
    col = td.Columns.Count
    
    With wb.Worksheets(1)
        'header
        Range(.Cells(1, 1), .Cells(1, col)).Value = th.Value
        'data
        Range(.Cells(2, 1), .Cells(row, col)).Value = td.Value
    End With
    
    Dim fn As String
    fn = td(1, 1).Value
    
    wb.SaveAs ThisWorkbook.Path & "\" & fn & ".xlsx"
    wb.Close
End Sub

全部をベタ書きするとごちゃごちゃするので、ファイルへの切り出しとメインロジックは別のプロシージャにしています。

データの条件としては

対象シートはインデックス1
1行目にヘッダー
2行目からデータ
A列がグループ化対象
データはA列でソート済み（昇降順は問わず）

となっております。

あまり多様な条件の表に対応しようとすると処理が煩雑になるので、これぐらいは手作業で整形してやりましょう。なにより全自動にしてしまうと、VBAerとして、社会人としてもっとも重要なスキル「奥義：仕事をしているふり」が発動できなくなってしまいますからね。

実行するとこのようにファイルがたくさんできます。

ファイル名が区名コードになっていてわかりにくいと思いますが、私レベルの特級さいたま市民になると区名コードが脳内で自動的に区名に変換されるマトリックスみたいなことが可能です（嘘）。

一つ中身を確認してみると

うまく切り出せているようです。

VBA版補足

ヘッダーとデータを別個で書き出しているこの部分が美しくないですが

With wb.Worksheets(1)
    'header
    Range(.Cells(1, 1), .Cells(1, col)).Value = th.Value
    'data
    Range(.Cells(2, 1), .Cells(row, col)).Value = td.Value
End With

本来はmain側でUnionでヘッダーとデータをくっつけてcutOutに渡すという処理だったんですが、これをやるとcutOut側でファイル作成→保存のところが無限ループするという怪奇現象に見舞われます。延々とヘッダーだけのファイルが生成されます。

ループ処理で無限ループなら理解できるのですが、ループも何もしていないコードが無限ループするという、プログラミングの原則を超越した神の領域に踏み込んでしまったようなのであきらめました。Unionに関する私の理解が間違っている故の挙動か、バグですかね。う～ん、前者かなぁ？VBAって結構こういうことあります。

PythonでExcel表を切り出す

当然、Pythonでもできます、やります。Excelファイルは毎度おなじみOpenPyXLで扱います。Excelファイルの条件は前段と概ね同じです。

OpenPyXLの場合、行を複数同時に走査していくというVBAと同じ処理をやろうとすると冗長なコードになりそうなので、直前に走査した値をバッファーにとっておいて、それと走査値を比較する方式にしました。

行の走査はiter_rows()というめちゃくちゃ便利なメソッドがあるのでそれでやります。次のようなコードになりました。

import openpyxl as excel
import sys

def dst_wb_save(wb, file_name):
    if wb is not None:
        wb.save(str(file_name) + ".xlsx")

def main():
    fp = sys.argv[1]
    src_wb = excel.load_workbook(fp, data_only=True)
    src_ws = src_wb.active

    dst_wb = None
    dst_ws = None
    buf = None
    is_th = True
    th = None
    r = 0

    for row in src_ws.iter_rows():
        if is_th:
            th = row
            is_th = False
        else:
            tgt = row[0].value

            if tgt is None:
                dst_wb_save(dst_wb, buf)
                break

            if buf != tgt:
                dst_wb_save(dst_wb, buf)
                dst_wb = excel.Workbook()
                dst_ws = dst_wb.active

                for h in th:
                    dst_ws.cell(1, h.col_idx).value = h.value

                r = 2
                buf = tgt
            else:
                r += 1

            for c in row:
                dst_ws.cell(r, c.col_idx).value = c.value

main()

グループの切り替わりをバッファーで管理している以外はメインのロジックはVBA版と同じようなことをやっております。

大きな違いはファイルの扱いで、VBAではグループのセル範囲を確定したら最後に作って一気にデータをぶち込み保存でしたが、Python版では最初に作っておいてどんどん走査データを追記、範囲が確定次第保存というところですかね。

実行するとVBA版と同様にファイルがたくさんできます。