今回は、いくつかの項目リストをもとに、その組み合わせの全パターンデータを、Excel を使って作成する手順について調べます。 また、それを自動生成するマクロを作成します。
長くなったので記事を２回に分けます。今回は Excel 2010 と 2013 に対応した手順です。
【追記 2021/09/13】 本記事の方法よりお手軽にできるバッチとクエリを作成しましたので以下記事もお試しください。
【追記 2022/04/25】 VBS版 と PowerShell 版も作成しましたのでお試しを。
組み合わせの問題
たとえば、洋服の色とサイズ、入試の学科と試験科目、タスクの分類とステータスのように、私たちが普段扱っている「データ」というものは、ある程度決まった項目内容（種別や区分）を組み合わせるものが多いです。
業務によっては、これら項目の組み合わせを全て網羅したデータを書き出すという作業をすることがあります。
この「組み合わせ」というのはなかなかやっかいです。
それが２つのリストの組み合わせなら、コピー＆ペーストの単純労働で何とかなるでしょう。 でも、３つ４つの組み合わせとなると、もう人間にはムリです。 まず精神がくじけます。
組み合わせによるパターンの数はリスト内容の数の掛け算になるので、 対象リストが増えるにつれ作業量は爆発的に膨れ上がるのです。
このような力業作業こそ、本来コンピュータが得意とする仕事のはずです。
我らが頼れる Excel さんなら、一発で一瞬にしてやっつけてくれることでしょう。
Excel のデータ分析機能で組み合わせを作る
やっつけてくれません。
そんな機能はありません。
Excel さん、これは一体どうしたことでしょうか。
そもそも、複数の表形式データから組み合わせを作るという操作は、データベースの世界では「クロス結合」とか「直積（デカルト積）」と呼ばれているもので、現在の一般的なデータベースシステム（リレーショナル・データベース）にとっては、理論的な基礎となる考え方です。
つまり、データベースを扱えるなら、組み合わせパターンの作成など簡単なことなのです。 たとえばそこらをフラフラしているエンジニアを捕まえれば、ジュース１本でも喜んでやってくれるでしょう。
Excel はデータベース・ソフトではありませんが、 外部の様々なデータベースからデータを取り込んで集計や分析をできるようにする関係上、 表形式（テーブル）のデータ構造を操作する基本的な機能も備えていることになります。
ということは当然、基本であるクロス結合もできるはずで、 Excel だけでも組み合わせパターンなど簡単に作成できるはずです。
我らが Excel さんにできないはずはありません。
やってみましょう。
調べてみると、Excel に表形式のデータを扱えるデータベース的な機能は、 いくつもあることがわかります。
- Microsoft Query（MS クエリ）
- ピボットテーブル
- DAX
- データの取得と変換（Power Query）
- Power Pivot
ええ、全部試してみました。
まあ、できたといえばできたのですが・・・、 どれを使っても、とても「簡単に」とは言えません。
それでも、一番お手軽な手順を１つだけ紹介しよう、と、思ったのですが、・・・そうもいかないのです。
Excel のバージョンによって、使える機能が異なるからです。
お手元の Excel のバージョンから以下の手順を参考にしてみてください。
- Excel 2010 まで ⇒ Microsoft Query を使う
- Excel 2013 ⇒ ピボットテーブルを使う
- 【参考】DAXを使う
- Excel 2016 / Office 365 ⇒ 「データの取得と変換」を使う (次回の記事)
Excel 2010 まで ⇒ Microsoft Query を使う
Excel 2010 までの古めの Excel の場合、使える機能は「Microsoft Query（MS クエリ）」のみです。
MS クエリは昔からある技術者向けの機能で、外部のデータベースやファイルから取り込んだデータを SQL というデータベース用言語で操作できます。
今回、SQLを書かなくても クロス結合できる手順を調べましたので紹介します。
【手順例】Microsoft Query を使う
（画面キャプチャは Excel 2013 です。2010 以前では UI や手順が一部異なる可能性があります）
- 作業用に新規ブックを開きます
- １シートにつき１リストを貼り付け、適当なファイル名でいったん保存します
- MS クエリでその Excel ファイルにデータソースとして接続します
- 「データ」タブ ⇒「その他のデータソース」⇒「Microsoft Query」
- 「データソースの選択」ダイアログ
- 「データベース」で「Excel files*」を選択
- 「ブックの選択」ダイアログ
- 保存した自分のExcelファイルを選択
- 「クエリウィザード - 列の選択」ダイアログ
- 「使用可能なテーブルと列」からリストを「クエリ」の列に追加します
- 「>」ボタンで左から右へ追加します
- 「使用可能なテーブルと列」リストが空だったら、「システム テーブル」を有効にします
- 「オプション」ボタン⇒「テーブル オプション」⇒「システム テーブル」にチェック
- 「キャンセル」ボタンを押します
- 「次へ」を押さないようにします
- 「使用可能なテーブルと列」からリストを「クエリ」の列に追加します
- 警告ダイアログが表示されますが、「はい」ボタンを押します
- 「Microsoft Query」ウィンドウが開きクエリの結果が出力されます
- クエリ結果は選択したリストのクロス結合で、全ての組み合わせになっています。
- クエリ結果を新規ワークシートに展開します
- ツールバーの「データを返す」ボタンを押す
- 「データのインポート」ダイアログ ⇒ 「新規ワークシート」⇒「OK」
- 結果の組み合わせデータがワークシートに展開されます
- 組み合わせデータ全体をコピーし別のブックのシートに「値のみ貼り付け」します
- 作業用ブックを閉じファイルを破棄します
「クエリウィザード」で「キャンセル」を選ぶところがちょっとしたワザです。
MSクエリは Excel 2016 など最近の Excel でもかろうじて残っていて、この手順もまだ使えるようですが、 将来のバージョンではなくなる可能性が高いです。
本手順は以下の記事を参考にさせていただきました。
- Excel Cross Join With Microsoft Query
https://www.contextures.com/excelmsquerycartesian.html
Excel 2013 ⇒ ピボットテーブルを使う
Excel 2013 なら「ピボットテーブル」を使う方法があります。
ピボットテーブルは Excel 2013 より、複数のデータシートから結合したデータを集計できるように進歩しました。
それなら、クロス結合も簡単に出来るようになっているものと当然期待しますが、 いくら調べてもそのような機能はみあたりません。
仕方ないので、リスト内容を全てピボットテーブルの行ラベルに展開するという小細工を使って、 組み合わせデータを得る手順を紹介します。
【手順例】ピボットテーブルを使う
- 作業用の Excel ブックを新規作成します
- 複数のリストデータを作業用シートにコピー＆ペーストします
- 同じシート内でかまいません
- 各リストを個別に「テーブル化」します
- リストのセル範囲を選択し、リボンの「挿入」タブ⇒「テーブル」(あるいは
Ctrl+T)を実行
- リストのセル範囲を選択し、リボンの「挿入」タブ⇒「テーブル」(あるいは
- リストテーブルの一つから「ピボットテーブル」を作成します
- テーブル化したリストの一つを選択し「挿入」タブ⇒「ピボットテーブル」を実行
- 「データモデルに追加」をチェックして「OK」
- 新規ピボットテーブルが作成されます
- 各リストを行（行ラベル）に追加します
- 画面右の「ピボットテーブルのフィールド」で「すべてのフィールド」を開きます
- テーブル化したリストが現れます
- 各テーブルのフィールド名をチェックして「行」ボックスに追加します
- 行以外のボックスに入ってしまったら、ドラッグ＆ドロップで「行」ボックスに移動してください
- 他のボックス（「フィルター」、「列」、「値」）は空のままにしておきます
- 画面右の「ピボットテーブルのフィールド」で「すべてのフィールド」を開きます
- リボンの「ピボットテーブルツール」の「デザイン」タブで、以下の操作を行います
- 「小計」⇒「小計を表示しない」
- 「総計」⇒「行と列の集計を行わない」
- 「レポートのレイアウト」⇒「表形式で表示」
- 「レポートのレイアウト」⇒「アイテムのラベルを全て繰り返す」
- 行ラベルとして全てのリスト内容が展開されている状態になっています
- ピボットテーブル全体をコピーし、本来のブックのシートに「値のみ貼り付け」します
- 作業用の Excel ブックを閉じて破棄します
この手順で作られた組み合わせの結果は単純なクロス結合とは少し異なるので注意が必要です。
- 重複するデータ行は１行になります
- 空白セルは"(空白)"になります
- 数値の書式が反映されないかもしれません
など。
今までピボットテーブルで「データモデル」や「リレーションシップ」なんて使ったことはありませんでしたが、 ちゃんと使えるようになると便利なのかもしれません。 これまで苦労して VLOOKUP などを駆使した数式を組んでいたようなところが、かなり楽になりそうです。
【参考】DAXを使う
ところで Excel 2013 で追加された機能の一つに、「DAX」という、データ分析・技術者向けの機能もあります。 DAX のほうにはクロス結合の機能がちゃんと用意されていて、これなら直に組み合わせを作成することができます。
ただ、DAX の話題はあまりにも技術者向けに寄りすぎるので本記事での説明は控えさせてもらいます。 興味のある方の参考までに GIF アニメだけ紹介します
Excel で DAX を使う方法については以下の Youtube 動画を参考にさせてもらいました。
- Excel Magic Trick 1433: Materialize DAX Table Functions: Existing Connections, DAX Studio, Power BI? - YouTube
https://www.youtube.com/watch?v=bJkPiuOl_r8
Excel 2016 / Office 365 ⇒ 「データの取得と変換」を使う
Excel 2016 での手順は次回の記事とさせていただきます。Excel 2016 以降をご使用の方は次の記事をご参考にしてください。
