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

一覧をグループごとに別ファイルへ保存。

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

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

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

VBAでExcel表を切り出す

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

cutout1.png

処理としては

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

がメインとなります。

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

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として、社会人としてもっとも重要なスキル「奥義:仕事をしているふり」が発動できなくなってしまいますからね。


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

cutout2.png

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

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

cutout3.png

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

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版と同様にファイルがたくさんできます。

cutout4.png

ファイル名が区名コードになっていてわかりにくいと思いますが、私レベルの特級さいたま市民になると(以下略)

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

cutout5.png

こちらも問題なくできています。

今、気づいたんですがOpenPyXLで作成した方は、セルの文字があのクソフォントじゃなくMSPゴシックになっていますね。いいですね。セルのサイズも横長になってるので、旧バージョンの仕様になっているようです。

Pythonなら対象のExcelファイルをドラッグ&ドロップするだけで実行できるので便利です。何個もファイルを処理する場合はこっちの方がいいですね。ドラッグ&ドロップでExcelファイルをやっつける方法は別記事に詳しいのでぜひご覧ください。

速度比較

VBAとPythonどっちが速いのか比べてみます。そもそもロジックが違うので純粋なスピード勝負にはなりません。参考までに。

データ件数は約650で10個のファイルができます。なお当方の実行環境は事務用PCが100台束になってかかってきてもダブルスコアで勝てるレベルのスペックなので、あらかじめご了承ください。

VBA:
一応、ScreenUpdating と EnableEvents を False で実行します。

cutout6.png

2.027秒


Python:
面倒くさいのでD&Dではなくパス直書きで実行。D&Dでもそんなに変わらんでしょう。

cutout7.png

0.225秒

圧倒的じゃないか、我が軍は。

体感でもあきらかにVBAの方がワンテンポ遅れてファイルができるのがわかります。ファイル生成まわりでPythonにぶっちぎられているのかな?

どこがVBAのボトルネックになっているかまでは検証しませんが、今回の用途においては今回のコードであればPythonでやったほうが速いという結果になりました。でも2秒だし、いくら事務用のへっぽこPCでも10秒はかからないでしょうから実務ではどっちでもいいですかね。

時間計測用のコードは次の記事で使用したものと同様です。ページ最後にリンクがあります。

サンプルファイルダウンロード

VBAコード付きのサンプルファイルを次からダウンロードできます。

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

excel_cutout.xlsm

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

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

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

実行には次の外部モジュールが必要です。pipでインストールしてください。

・openpyxl

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

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

excel_cutout.py

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

おわり。

related pages
PCのカメラでバーコードを読み取りExcelに取り込む
Excelシートでバーコードによる物品管理を企むの巻。

VBAではバーコードの扱いは何とかなるにしても、カメラは絶望的です。世界は広いのでVBAから使えるカメラ制御DLLがあるのかもしれませんが、あったとしても私の技量では扱える気がしないので早々にあきらめ、Pythonでやります。

Read More ...
VBAからLINEにメッセージを送る
LINE Notify APIにVBAからリクエストする方法。

LINE Notifyというサービスがあります。API経由でLINEにメッセージが送れます。これをVBAから利用できればいろいろ面白いことができそうですよね。たとえば、Excelファイルのイニシャライズイベントに仕込んでおけば、ファイルが開かれたらLINEにメッセージが届きます。発想次第で画期的な何かが生まれそうな予感がします。

Read More ...
ExcelでWordを書き換える
ExcelシートのデータをWordへ書き込む方法。

ExcelデータをWordのフォーマットにはめ込む必要に迫られました。Wordとか興味ないし、ネットからコピペしたコードでやろうかなと思ってググったのですが、WordのVBAに関する情報はほとんどありません。同じ境遇のあなたがここにたどり着いたときにコピペで済ませられるようにしておきます。

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

2020-12-05

更新日

2020-12-09

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