はじめに
先日、データ移行に伴い、部署別のExcel資料を100ファイル以上作成する業務がありました。
元データには部署、ID、氏名などの一覧があり、これを各部署に配布するためのExcelファイルに加工する必要がありました。
単にデータを分割するだけではありません。
- 部署別にデータを抽出する
- 部署ごとにExcelファイルを生成する
- 書式や罫線を整える
- 表紙と説明資料を挿入する
- ファイル名を規則に従って整形する
- 起動時に表紙が表示されるようにする
- ブックの構成保護を設定する
こうした処理を100ファイル以上に対して行います。
今回、実装言語にはPowerShellを選択し、コードの生成にはAIを利用しました。
ただし、AIに最初から完成形を作らせたわけではありません。
処理を工程単位に分割し、AIに一つずつ実装させ、実行とエラー確認は自分で行いました。
この記事では、その進め方と、PowerShellによるExcel操作の実装ポイントを紹介します。
なお、記事中のコードは、実際の業務コードではなく、処理内容を説明するために新たに作成した参考例です。
1. 今回の要件と実行環境
今回の処理はWindows環境で実施しました。
| 項目 | 内容 |
|---|---|
| OS | Windows |
| 対象 | Excelブック（100ファイル以上） |
| 入力データ | 部署、ID、氏名などの一覧 |
| 実装言語 | PowerShell |
| Excel操作 | COMオートメーション |
| コード生成 | AI |
| 実行・エラー確認 | 人間 |
| 出力 | 部署別Excelファイル |
PowerShellとExcel COMを選択した理由は、既存のWindows環境を利用できることです。
Excelがインストールされていれば、PowerShellからExcelのオブジェクトモデルを操作できます。
Pythonとopenpyxlによる実装も可能ですが、今回の環境ではPythonのインストールやライブラリの準備が必要でした。
また、VBAでも実現できますが、マクロを含むExcelブックを用意して実行するより、PowerShellから直接ファイルを処理する構成が適していると判断しました。
もちろん、これはPowerShellが常に最適という意味ではありません。
実行環境と業務内容に応じた技術選定です。
2. 処理全体を10工程に分割する
今回の作業は、以下の順序で進めました。
| 工程 | 処理内容 |
|---|---|
| ① | 部署・ID・氏名の元データ準備 |
| ② | 部署によるソート |
| ③ | AIへPowerShellによる実装を指示 |
| ④ | inputフォルダの準備 |
| ⑤ | 一覧をグループごとにシートへ分割 |
| ⑥ | 各シートを個別のExcelファイルへ保存 |
| ⑦ | 書式・罫線の調整 |
| ⑧ | 表紙・説明テンプレートの挿入 |
| ⑨ | ファイル名の整形 |
| ⑩ | 表示位置・アクティブシート・ブック保護の設定 |
ここでのポイントは、データ加工と、Excelの見た目を整える処理を分離したことです。
最初からすべての機能を実装するのではなく、まず正しくデータを分割する。
次にファイルとして出力する。
その後、書式やテンプレート、保護を整えます。
この順番にすることで、不具合の原因を切り分けやすくなります。
3. PowerShellからExcelを操作する
PowerShellでは、COMオブジェクトを利用してExcelを操作できます。
基本的なコードは以下のとおりです。
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$workbook = $excel.Workbooks.Open(
"C:\Work\input\source.xlsx"
)
$sheet = $workbook.Worksheets.Item(1)
Write-Host $sheet.Name
$workbook.Close($false)
$excel.Quit()
Excel VBAで利用するWorkbookやWorksheetのオブジェクトモデルを、PowerShellから扱う形です。
ただし、実際の処理ではCOMオブジェクトの解放まで適切に実装する必要があります。
特に100ファイル以上を扱う場合、Excelプロセスが残留しないように注意します。
4. 入力と出力のフォルダを分離する
今回、処理対象ファイルを格納するinputフォルダを準備しました。
継続的に使う仕組みなら、例えば次のような構成が考えられます。
Work
├── input
│ └── source.xlsx
│
├── output
│
├── template
│ └── template.xlsx
│
└── logs
入力データ、出力ファイル、テンプレートを分けておくと、処理対象を把握しやすくなります。
また、再実行時に過去の出力ファイルを誤って入力として読み込むことも防げます。
業務用スクリプトでは、こうしたファイル管理も重要です。
5. データを部署単位に分割する
元データには、部署、ID、氏名などの列がありました。
今回の業務では、データが事前にソートされていることをAIに伝え、グループ単位でシートを生成するよう指示しました。
ここでは説明を分かりやすくするため、次の構造を仮定します。
| A列：部署 | B列：ID | C列：氏名 |
|---|---|---|
| 営業部 | 001 | Aさん |
| 営業部 | 002 | Bさん |
| 総務部 | 003 | Cさん |
| 情報部 | 004 | Dさん |
実際の業務ではG列の氏名と部署情報を使っていましたが、正確な分割キーの仕様はここでは省略しています。
PowerShellによるグループ分割の考え方
一例として、元データをPowerShellのオブジェクトへ読み込み、部署名でグループ化できます。
$data = @(
[PSCustomObject]@{
Department = "営業部"
ID = "001"
Name = "Aさん"
},
[PSCustomObject]@{
Department = "営業部"
ID = "002"
Name = "Bさん"
},
[PSCustomObject]@{
Department = "総務部"
ID = "003"
Name = "Cさん"
}
)
$groups = $data | Group-Object Department
foreach ($group in $groups) {
Write-Host "部署: $($group.Name)"
Write-Host "件数: $($group.Count)"
}
この例では、部署ごとにレコードをグループ化します。
実際には、Excel COMでセルの値を読み込み、グループごとにワークシートを作成することになります。
データ件数が多い場合は、1セルずつ読み込むより、RangeのValue2をまとめて取得する方法が効率的です。
なお、Group-Objectであれば、必ずしも事前のソートは必要ありません。
今回は既存のデータをソートしてから処理しましたが、汎用ツールにするならソート前提を外す設計も考えられます。
6. シートを個別のExcelファイルに保存する
次に、分割したシートを別のExcelファイルとして保存する処理です。
Excel COMでは、WorksheetのCopy()メソッドを利用できます。
# $sheet は既存ブック内のWorksheetとする
$sheet.Copy()
$newWorkbook = $excel.ActiveWorkbook
$outputPath = "C:\Work\output\営業部.xlsx"
$newWorkbook.SaveAs(
$outputPath,
51
)
$newWorkbook.Close($false)
51は、通常の.xlsx形式を指定するExcelのファイル形式定数です。
この方法では、対象シートを新しいWorkbookへコピーできます。
今回の業務では、このような処理を使って個別ファイルを生成する仕組みをAIに実装させました。
注意点として、シート名をファイル名に使用する場合、ファイル名に利用できない文字や既存ファイルとの重複を考慮する必要があります。
また、シート名には31文字の長さ制限があるため、部署名が長い場合には別途対応が必要です。
7. 書式と罫線を整える
データを個別ファイルに分割できても、それだけでは配布資料として完成しません。
今回も、テキストデータはコピーされていましたが、必要な書式や罫線を調整する必要がありました。
そこで、AIに書式設定の処理を追加してもらいました。
例えば、次のような処理です。
$sheet = $newWorkbook.Worksheets.Item(1)
$lastRow = $sheet.UsedRange.Rows.Count
$range = $sheet.Range(
"A1:C$lastRow"
)
# 罫線を設定
$range.Borders.LineStyle = 1
# 列幅を自動調整
$range.Columns.AutoFit()
# 見出し行を太字にする
$sheet.Range("A1:C1").Font.Bold = $true
このコードは参考例です。
実際には、表の範囲や必要な書式に合わせて調整します。
業務自動化では、データ処理が正しいことと、利用者が使いやすい資料になることは別の要件です。
特に配布するExcelでは、書式や表示位置も品質の一部になります。
8. テンプレートのシートを挿入する
次に、あらかじめ用意した表紙と説明資料のテンプレートを、生成したExcelファイルへ挿入します。
今回の完成形は、概ね次のような構成でした。
部署別ファイル.xlsx
Sheet1：表紙
Sheet2：説明資料
Sheet3：移行対象者一覧
テンプレートのシートをコピーするには、WorksheetのCopy()を利用できます。
例えば、テンプレートの2枚目のシートを、対象ブックの1枚目より前にコピーする場合です。
$templateBook = $excel.Workbooks.Open(
"C:\Work\template\template.xlsx"
)
$targetSheet = $newWorkbook.Worksheets.Item(1)
$templateSheet = $templateBook.Worksheets.Item(2)
$templateSheet.Copy(
$targetSheet,
[System.Type]::Missing
)
$templateBook.Close($false)
実際には、表紙と説明資料を希望する順番に並べる必要があります。
また、コピー先に同じ名前のシートがある場合や、元のシートが別のブックを参照している場合なども確認が必要です。
今回のように、書式が決まっているシートをテンプレートとして用意する方法にはメリットがあります。
AIに毎回資料のデザインを生成させる必要がないことです。
変化するデータはスクリプトで処理し、変化しない部分はテンプレートを使う。
この設計は、Excelの自動化では有効だと思います。
9. ファイル名をルールに従って整形する
生成したExcelファイルには、部署名を含む規則的な名前を設定しました。
例えば、以下のような形式です。
移行対象者一覧_営業部.xlsx
移行対象者一覧_総務部.xlsx
移行対象者一覧_情報部.xlsx
PowerShellでは文字列を組み合わせるだけです。
$department = "営業部"
$fileName = "移行対象者一覧_$department.xlsx"
$outputPath = Join-Path `
"C:\Work\output" `
$fileName
技術的には非常に単純な処理ですが、大量のファイルを配布する業務では、命名規則の統一は重要です。
ファイル名に部署を含めることで、対象部署を識別しやすくなります。
また、後続の処理でファイル名を利用する場合にも役立ちます。
10. 表示シートとブックの保護を設定する
最後に、Excelを開いたときの状態を整えました。
例えば、表紙を最初に表示する場合は、次のようにします。
$coverSheet = $newWorkbook.Worksheets.Item(1)
$coverSheet.Activate()
$coverSheet.Range("A1").Select()
さらに、利用者が誤ってシートを追加・削除しないよう、ブックの構成保護を設定します。
$newWorkbook.Protect(
"password",
$true,
$false
)
第2引数の$trueは、ブック構成の保護を意味します。
これはセル編集を制限するワークシート保護とは別の機能です。
なお、ブックの構成保護は機密情報を暗号化するためのセキュリティ機能ではありません。
あくまで、意図しないシート操作を防ぐ目的で利用します。
ここまで処理して、ようやく各部署に配布できるExcelファイルが完成します。
11. AIに実装させるときに意識したこと
今回の実装で重要だったのは、AIに最初からすべてを任せなかったことです。
例えば、最初から次のような指示を出すこともできます。
元データを部署別に分割し、それぞれExcelファイルを作成して、書式を整え、表紙を追加し、保護を設定してください。
しかし、これだけの処理を一度に生成させると、エラーが発生した際に原因を切り分けるのが難しくなります。
そこで、機能単位に分割して実装を進めました。
段階的に実装する
STEP 1：データを分割する
まずは正しくデータが分割されることを確認します。
STEP 2：ファイルとして保存する
次に、分割したデータを個別ファイルへ保存します。
STEP 3：書式を整える
ファイルの見た目を調整します。
STEP 4：テンプレートを挿入する
表紙や説明資料を追加します。
STEP 5：配布用の設定を行う
ファイル名や保護設定を整えます。
この進め方であれば、問題が発生したときに、どの工程を修正すればよいか判断しやすくなります。
AIに生成させるコードも、工程ごとに目的が明確になります。
実行とエラー確認は人間が行う
今回はAIにコードを生成させましたが、実行とエラー確認は自分で行いました。
生成AIが作成したコードは、必ずしも正しく動作するとは限りません。
特に100ファイル以上のExcelを一括処理する場合、誤った処理によって大量のファイルを変更するリスクがあります。
そのため、実行前にコードを確認し、実行結果を見ながら修正していく方法を選びました。
これは私なりの、AIと人間の責任範囲の分け方です。
12. 正常終了と業務上の成功は違う
今回のようなExcelの大量処理では、PowerShellがエラーなく終了しただけでは、正しく処理されたとは判断できません。
例えば、次のような問題が考えられます。
- 対象者の一部が出力されていない
- 他部署のデータが混入している
- 一部の行が重複している
- ファイル名が誤っている
- 表紙や説明資料が正しく挿入されていない
- ブックの保護設定が不足している
そのため、処理結果の検証が必要です。
例えば、元データと出力データの件数を比較します。
if ($inputCount -ne $outputCount) {
throw "件数が一致しません"
}
さらに、部署ごとの件数を集計し、入力側と出力側で一致しているか確認します。
ただし、件数が一致していても、別の人のデータに置き換わっている可能性があります。
したがって、実務ではIDなどの一意な識別子を利用して照合することも重要です。
プログラムが正常終了したことと、業務として正しい結果が得られたことは違います。
これはAIを利用した開発でも変わりません。
13. 継続利用するなら改善したいこと
今回の仕組みは、特定のデータ移行に伴う処理として作成しました。
ただし、継続的に利用するなら、さらに以下の機能を追加したいところです。
| 機能 | 目的 |
|---|---|
| 設定ファイル | 入出力パスや対象列を外部から指定する |
| ドライラン | ファイルを変更せずに処理対象を確認する |
| 実行ログ | 成功・失敗とエラー内容を記録する |
| 入出力照合 | 対象件数やIDの一致を確認する |
| バックアップ | 変更前のデータを保存する |
| 再実行 | 失敗した対象だけ処理をやり直す |
| COMオブジェクト管理 | Excelプロセスの残留を防ぐ |
特にExcel COMを利用する場合、例外発生時の終了処理は重要です。
また、OfficeアプリケーションのCOMオートメーションは、ユーザーが操作するデスクトップ環境を前提としています。
Microsoftは、Windowsサービスなどでの無人・サーバーサイドOfficeオートメーションを推奨・サポートしていません。
継続的にサーバー上で実行する仕組みが必要なら、別のライブラリやファイル処理方式も検討すべきでしょう。
14. まとめ：AIに実装を任せても、工程設計は必要
今回の業務では、PowerShellを使って部署別のExcelファイルを作成しました。
コードの生成にはAIを利用しましたが、処理の順序や技術選定は自分で考えています。
実際に行ったのは、次のような役割分担です。
| 人間 | AI |
|---|---|
| 業務要件の理解 | コードの生成 |
| 処理工程の分解 | 実装案の作成 |
| 技術選定 | コードの修正 |
| 実行 | エラーに対する修正案の提示 |
| エラー確認 | — |
| 結果検証 | — |
AIによって、コードを書く負担は確かに軽くなりました。
しかし、何を実現するのか、どのように処理を分解するのか、どこまでAIに任せるのかという設計判断は依然として必要です。
むしろ、AIがコードを書くようになることで、エンジニアが処理全体を設計する重要性は、さらに高まるのではないでしょうか。
AIがプログラムを書くのであれば、人間はプログラムに仕事をさせる設計をする。
今回のExcel業務自動化は、そんなことを考える機会になりました。