複数リストから全ての組み合わせデータを作るバッチとクエリ

今回は、複数リストから全ての組み合わせデータを作る Batch スクリプトと、Power Query のクエリを作成します。

組み合わせのつらみ

以前、Excel を使って複数リストからの組み合わせデータを作成する方法について紹介しました。

このサビれたブログにしては思いのほか一定のアクセスがあるようです。

結構マニアックな話題だと思っていたのですが、こんな作業にそんなに需要があるものでしょうか。

紹介したどの方法も、Excel 芸としては面白いかもしれませんが、トリッキーかつ複雑な手順でとても覚えられたものではありません。

もしこれを必要に迫られて、単発ではなく、定期的あるいは日常的な業務作業として使っている方が多くおられるのだとしてたら、まさに Excel に働かされているわけで、少し気の毒なことだったと感じています。

本当に業務手順として必須のステップなら、できるだけ自動化を検討すべきです。

本記事では、組み合わせデータの作成を少しでも自動化できるよう、以下の２つのスクリプトを作成します。

テキストファイルから組み合わせデータを生成するバッチ
Excel テーブルから組み合わせデータを生成するクエリ

【追記】 バッチが遅いというご指摘をいただきました。別記事ですが処理速度を改善した VBScript 版と PowerShell 版も作りましたのでお試しください。

VBScript で組み合わせを生成する（別記事）

PowerShell で組み合わせを生成する（別記事）

1. テキストファイルから組み合わせデータを生成するバッチ

まずは、Windows のバッチファイルによる実装です。

@echo off
setlocal enabledelayedexpansion

call :crossjoin "" %*
exit /b

:crossjoin
  for /F "tokens=*" %%l in (%~2) do (
    if [%~3] == [] (
      set x=%~1%%l
      echo !x:__=,!
    ) else (
      set y= %*
      set y=!y: %1=!
      set y=!y: %2=!
      call :crossjoin "%~1%%l__" !y!
    )
  )

本バッチは、複数のテキストファイルの各行の組み合わせをカンマ区切りで標準出力に出力します。

使い方は基本的に、以下のようにしてコマンドプロンプトから実行します。

xj.bat list1.txt list2.txt list3.txt ...

バッチ（ここでは「xj.bat」）の引数には項目リストのテキストファイル名を並べます。リストファイルの個数に特に制限はありません（コマンドラインの最大長の制約はあります）。

C:\kumiawase>xj.bat 具.txt カレー.txt 辛さ.txt
マトン,マサラ,甘口
マトン,マサラ,中辛
マトン,マサラ,大辛
マトン,マサラ,激辛
マトン,キーマ,甘口
マトン,キーマ,中辛
マトン,キーマ,大辛
マトン,キーマ,激辛
マトン,サグ,甘口
マトン,サグ,中辛
・・・
・・・
ダル,マサラ,激辛
ダル,キーマ,甘口
ダル,キーマ,中辛
ダル,キーマ,大辛
ダル,キーマ,激辛
ダル,サグ,甘口
ダル,サグ,中辛
ダル,サグ,大辛
ダル,サグ,激辛

【使い方】

フォルダを一つ新規作成して開きます
上記バッチスクリプトを BAT ファイル（.bat）としてそのフォルダに保存します
- ファイル名は任意ですが、拡張子（ファイル名の最後）を「.bat」としてください
組み合わせたいリストをそれぞれテキストファイル（.txt）として同フォルダに保存します
- 先頭行から１行１項目とします（見出し行なし）
フォルダでコマンドプロンプトを開きます
- フォルダウィンドウの上部にあるアドレスバーに cmd と入力してEnterを押すと、フォルダのパスでコマンドプロンプト（文字だけの黒いウィンドウ）を開けます
コマンドプロンプトでバッチファイルを実行します
- 各ファイルの項目の組み合わせデータがカンマ区切りで出力されます

以下のような使い方を想定しています。

組み合わせデータをテキストファイルとして保存する
C:\kumiawase> xj.bat list1.txt list2.txt list3.txt > kumiawase.txt

クリップボードにコピーする
C:\kumiawase> xj.bat list1.txt list2.txt list3.txt | clip

デフォルトの文字コードは「シフトJIS(cp932)」です。バッチやリストはシフトJISで保存してください（メモ帳の保存では「ANSI」を選択します）。

リストファイルの文字コードを「UTF-8」で扱いたい場合には以下のように chcp コマンドでコマンドプロンプトの文字コード（コードページ）を変更後、バッチを使用します。

文字コードを UTF-8 に変更
C:\kumiawase> chcp 65001

バッチを実行
C:\kumiawase> xj.bat list1.txt list2.txt > out.txt

文字コードをシフトJIS（cp932）に戻す
C:\kumiawase> chcp 932

その場合、リストファイルは必ず UTF-8 の「BOMなし」で保存してください。「BOM付き」だとデータ中にゴミ（BOM）が混入します。

出力結果の組み合わせデータの内容は間違いがないか必ず確認してください。

バッチスクリプトの制約から生じる、今分かっている制限事項としては以下があります。

引数のリストファイルに同じファイル名を複数個指定すると正常に動作しません。
リストファイル内のテキストに引数ファイル名のどれかが含まれると正常に動作しません。
リストファイルのパスやファイル名に特殊な文字が含まれると、うまく動かない可能性があります。

2. Excel テーブルから組み合わせデータを生成するクエリ

ちょっと上級者向けになりますが、 Power Query で組み合わせデータを作るクエリも用意しました。 Excel 2016 以上か、Microsoft 365 版のバージョンの Excel で使用できます。

let
    t1 = Excel.CurrentWorkbook(),
    t2 = Table.SelectRows(t1, each not (Text.StartsWith([Name],"クエリ") or Text.EndsWith([Name], "_FilterDatabase"))),
    t3 = Table.AddColumn(t2, "col_names", each Table.ColumnNames([Content])),
    t4 = Table.AddColumn(t3, "new_names", (t) => List.Transform(t[col_names], each t[Name] & "." & _)),
    t5 = Table.ToRecords(t4),
    t6 = List.Accumulate(t5, #table({"a"}, {{1}}), (t, r) => Table.AddColumn(t, r[Name], each r[Content])),
    t7 = List.Accumulate(t5, t6, (t, r) => Table.ExpandTableColumn(t, r[Name], r[col_names], r[new_names])),
    t8 = Table.RemoveColumns(t7, {"a"})
in
    t8

本クエリはワークブック上の全てのテーブルをクロス結合します。

使い方は、元となるリストの組を Excel テーブルとして用意したら「空のクエリ」を開き、上記クエリを「詳細エディター」にコピー＆ペーストして「閉じて読み込む」だけです。

シート上に初回の「閉じて読み込む」をしたとき、なぜかゴミカラム（「読み込み中...」的な）が追加されることがありますが、そのまま「すべて更新」すれば正常な出力に戻ります。

クエリには列の列名や型の変更など、適宜クエリステップを追加してかまいません。特に日付や時間のデータ列には日付/時間のデータ型の指定を追加した方がいいかもしれません。

またワークブック上の全てのテーブルではなく、一部のテーブルを指定して実行したい場合には、 t1 ステップを選択してから「Name」列のフィルターによりクロス結合したいテーブル名以外を除外するステップを追加します。

注意として、クエリは必ず「空のクエリ」から作成してください。「空のクエリ」で生成されるテーブル名（「クエリ」で始まる）をあてにしてクエリ自身のテーブルを除外しているので、これに漏れるとクエリの出力結果まで結合されてしまいます。

Power Query には Excel シートでの 100 万行のような制限がありませんが、うかつに実行すると組み合わせ爆発によって天文学的なレコード数を生成してしまうので注意してください。

まとめ

組み合わせデータを作成するバッチとクエリを作成してみました。

中身は呪文のようかもしれませんが、手作業でトリッキーで複雑なワザを駆使するよりは、ずっと簡単になったのではないでしょうか。

ただ本来の理想は、人の手作業の介在自体を失くすことです。

Excel に働かされる工程を RPA など自動化の１ステップに組み込んでもらうための参考となれば幸いです。

スクリプトの動作確認は Windows 10 上の Microsoft 365 版 Excel で行いました。スクリプトに実業務での使用実績はないので、動作や使い勝手に不具合等があったらコメント等でお知らせください。

おまけ

「組み合わせ」といっても、今回のような複数リストからの組み合わせではなく、数学で習ったような「順列と組み合わせ」の組み合わせ、つまり n 個の要素からなるリストから m 個を重複なく取り出すパターンを作りたいということもあるようです。

以下のバッチはパラメータに与えられたリストからなる組み合わせを生成し、カンマ区切りで出力するものです。

@if(0)==(0) echo off
  cscript.exe //nologo //E:JScript "%~f0" %*
  exit /b
@end
function combination(n, cmb, vals) {
    for(var i=0; i < vals.length; i++) {
        cmb.push(vals[i]);
        if (n<=1) {
            WScript.Echo(cmb.join(","));
        } else {
            combination(n-1, cmb, vals.slice(i+1));
        }
        cmb.pop();
    } 
}
var args = [];
for(var i=0; i<WScript.arguments.length;i++) {
  args.push(WScript.arguments(i));
}
combination(args[0], [], args.slice(1));

【使い方】

上記パッチをファイルに保存し、拡張子を .bat とします。最初のパラメータに組み合わせたい個数を、それ以降のパラメータに組み合わせたい値をスペース区切りで指定します。

C:\> 組み合わせ.bat 3 日 月 火 水 木 金 土
日,月,火
日,月,水
日,月,木
日,月,金
日,月,土
日,火,水
日,火,木
・・・
水,木,金
水,木,土
水,金,土
木,金,土

www.shegolab.jp

変更履歴

2022/04/22 VBS 版と PowerShell 版の記事へのリンクを追加

シーゴの Excel 研究室

タイトル変更しました（旧称：今日を乗り切るExcel研究所）

複数リストから全ての組み合わせデータを作るバッチとクエリ

組み合わせのつらみ

1. テキストファイルから組み合わせデータを生成するバッチ

【使い方】

2. Excel テーブルから組み合わせデータを生成するクエリ

まとめ

おまけ

【使い方】

関連記事

変更履歴