重複するパターンの算出について。

エクセルについて教えていただきたく、質問をさせていただいております。
長文でわかりずらいのですが、お力添えいただければ幸いです。

画像のように、シート１のＡ２～Ｉ９データの、全ての重複組み合わせのパターンを、シート２に出力する方法を、ＶＢＡを使って実現することは可能でしょうか。
左の画像の場合、(2.3.4.5.6.7.8.9)(1.3.4.5.6.7.8.9)(1.2.4.5.6.7.8.9)(1.2.3.5.6.7.8.9)(1.2.3.4.6.7.8.9)(1.2.3.4.5.7.8.9)(1.2.3.4.5.6.8.9)(1.2.3.4.5.6.7.9)(1.2.3.4.5.6.7.8)の９つのデータの重複組み合わせのすべてのパターンを出力した場合です。

実際には、右の図のように数値が虫食いのように、消えて変動するので、その時に、ＶＢＡを使ったマクロボタンにてデータを算出できるようにできればと考えております。

実際には、出力結果がエクセルの最大行数内で収まる場合のみに使用する目的です。

エクセルに詳しい方がいらっしゃいましたら、お力添えいただけると助かります。
宜しくお願い致します。

質問者からの補足コメント

• 回答ありがとうございます。
  
  シート１のイメージとしては、
  Ａ列＝①ｻﾝの投票　⇒　投票先は2･3･4･5･6･7･8･9のうちのどれか。
  Ｂ列＝②ｻﾝの投票　⇒　投票先は1･3･4･5･6･7･8･9のうちのどれか。
  ･･････
  ･･･
  Ｉ列＝⑨ｻﾝの投票　⇒　投票先は1･2･3･4･5･6･7･8のうちのどれか。
  として、①～⑨の投票の組み合わせをすべて算出し、
  その結果をシート２に出力したいのですが難しいでしょうか。
  
  excelは2016を使用していて、最大行数が1,048,576 行なので、検出結果が1,048,576 パターン以内で終わる場合のみに使用したいと考えております。

    補足日時：2017/05/21 11:07

No.4 ベストアンサー 回答者： tatsu99 回答日時： 2017/05/22 09:48

以下のマクロを標準モジュールに登録してください。

文字数オーバーなので下記URLに記述しました。
http://climbi.com/b/10197/0

使用時の注意事項です。
実際のシート名ですが
シート１はSheet1
シート２はSheet2
であることが前提です。
Sheet2の2行目から出力します。（1行目はそのままです。変更されません）

Const MaxLimit As Long = 10000
ですが、組み合わせの件数が、この値を超えた場合、処理をしないようにしています。
現在は10000なので、最大行数の1,048,576 行まで出力するなら、
Const MaxLimit As Long = 1048575　としてください。（2行目から書くので、出力できる件数は１件少なくなります。）
但し、1048575　を設定すると、excelの限界まで書きますので、その結果、どうなるかは私は判りません。（自己責任でお願いします）
（最低でも該当ブックのバックアップをとってから実行なさってください）

MaxLimit 以内の場合でも、実行前に「実行するかどうか」の確認メッセージがでますので、キャンセルをクリックすれば中止します。

この回答へのお礼

VBAというものを、今回初めて知って、回答者様のデータを見たときに、URLで説明をする方法や、こんなにも難しいデータを私にもわかりやすく説明してくださって、ただただ驚くばかりでした！
ご教示いただき、本当にありがとうございました。

お礼日時：2017/05/23 00:26

No.5 回答者： taigoito 回答日時： 2017/05/22 10:23

別の猛者に一歩遅れをとりましたが。

前のご質問で回答した者ですが・・・。

https://oshiete.goo.ne.jp/qa/9762293.html（前の質問）

ざっくり手順だけお伝えします。
VBAで配列をご理解できれば、ご自身で書けると思います。
逆に僕はVBAで配列の操作を良く知らないものでして、別の言語（HTML + JavaScript）で検証しました。

(1) 表の改変
現状の表でも行けないことはないですが、
縦軸、横軸ともに候補者1～9を表し、
例えば、候補者1が、候補者2に投票する可能性があれば1列目2行目のセルにTRUE(真)、なければFALSE(偽)を記入された方が、VBAを組みやすくなります。

(2) 投票相手のパターンを二次元配列化
候補者1～9の投票相手を表す配列に、それぞれ投票する可能性のある相手の番号を、配列で指定します。
VBAの書き方と少し違います（[]が配列を表します）が、
例えば候補者が4人で、
[[2,3],[1,3],[1,2,4],[2]]
というような配列なら、
1サン（2サンか3サンに投票）、2サン（1サンか3サンに投票）、3サン（1サンか2サンか3サンに投票）、4サン（2サンに投票）
という意味を示します。

(3) 投票パターン出力
ここは数学的な考え方をお伝えするのが難しいので、プログラムの概要を説明します。
「候補者数」、「投票パターン数」、「総パターン数」、「投票パターン数」、「投票パターン」、「パターン積」、「投票結果配列」の7つの変数を用意してください。
「パターン積」はLong型で1を代入する初期化を行ってください。
「投票結果配列」は、シート2のセルの配置をイメージした二次元配列です。

(3-1) 手順(2)の二次元配列の各配列の要素数の積を、総パターン数に代入する。

(3-2) 「for i = 1 To 候補者数」で大ループを作る

(3-3) 「パターン積」に手順(2)の二次元配列のi番目の配列の要素数を積算する。

(3-4) 大ループの中に「for j = 1 To 総パターン数」で小ループを作る。

(3-5) 投票パターン =　(Int(j / 総パターン数 / パターン積)) を 投票パターン数 で割った余り。

(3-6) 「投票結果配列(i,j)」 = 手順(2)の二次元配列(i,投票パターン)

(3-7) 「投票結果配列(i,j)」は、iサンのjパターン目の投票相手を示すので、これをシート2に出力。

・・・以上。
楽しいお題でハマってしまいました（笑）



余談ですが、当選者が同じ票数だった場合どうします？？
僕の場合が、同じ票数だったらどちらも当選と見なしています。
なので、僕のサンプル（↓）で出力される確率の和は100%を超えてます。

http://web.tgco.jp/apps/votes/

このサンプルをちょっと改変して済むことでしたら、もうちょっとだけお手伝いしますよ！

No.3 回答者： moto_koukousei 回答日時： 2017/05/21 14:38

https://oshiete.goo.ne.jp/qa/9762293.html
この質問をみました。
できれば、質問履歴を公開しておくか、前に関連する質問をしている場合には、その質問を参照できるようにしておいた方がいいと思います。
また　この質問が前の質問に関連しているとすると、例えば、❶さんが７票で最高得票の場合、❶さんに投票しなかったのは、❶さん以外にあと一人の合計２人ですが、❶さんが❷～❾さんのどれかか誰にも投票しなかったのは９パターンになるのでしょうか。
あと一人の方についても無記名投票を含めて９パターンあるとカウントするのでしょうか。　そのようなパターンカウントも可能だと思いますが、それが今回のパターンカウントの狙いなのでしょうか。
何をどのような条件でカウントするのかが決まらないと、やり方は出てきにくいと思います。

No.2 回答者： soixante 回答日時： 2017/05/21 09:32

元データ（シート１）で、A~Iまですべて入っているとき、

（＝ひとつも欠けていない場合）
のみ、シート2に書き出すというイメージですか。

＞出力結果がエクセルの最大行数内で収まる場合のみに使用
いまいち意味が読み取れませんが、あなたの使用バージョン、最大何行まで行けるかくらい
書いときましょう。

ちなみに、９の９乗は、3億8,000万を超えます。
エクセルの最大行は今のものでも、1,048,576行です。

No.1 回答者： moto_koukousei 回答日時： 2017/05/21 09:20

意図が理解できません。

｢シート１｣の｢重複組み合わせのパターン｣というのは何でしょう。
なにをどう判断すると、｢シート２｣のA2＝２、A3=2、B4=1、I4=3のように判断できるのでしょう。
｢出力結果がエクセルの最大行数内で収まる場合のみに使用する目的です｣も意味がわかりません。｢シート１のA3：F13の全部、D7:I12の全部だけに、1桁の数字が入力されている｣状況があった場合、｢シート２｣にはどのセルに出力がでる可能性があるのでしょう。