エクセルで各行の合計が最小になる列の組合せを表示したい

約500列1000行に0と1の数値が入力されている表があります。
各行の合計が1以上最小の値とした場合の列の組み合せを関数等（行列を入れ替えてフィルター詳細設定でなんとかなるものでしょうか・・・さっぱり分かりません）で表示させたいのですが可能でしょうか。可能であれば教えていただけますでしょうか。
例えば以下の表では「B列、E列」（の合計が1以上で最小の組み合わせ）が求めたい解です。
よろしくお願い致します。

A B C D E F G
1 1 0 0 0 1 2
2 0 1 1 1 1 3
3 1 0 1 0 0 2
4 0 1 0 1 1 3
5 0 0 0 1 0 1

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

• 添付の表も説明不足間違いがあって失礼いたしました。表のA列は行番号、G列は合計です。
  「B列、E列」のほかにG列の合計が1以上の「B列、C列、E列」等の3列以上の組み合わせがあるので「最小の組み合わせ」と表現しました。
  
  2行目のG列が間違ってました。
  
  ＜正＞
  A B C D E F G
  1 1 0 0 0 1 2
  2 0 1 1 1 1 4
  3 1 0 1 0 0 2
  4 0 1 0 1 1 3
  5 0 0 0 1 0 1

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/07/03 17:54

• すみません。

  No.2の回答に寄せられた補足コメントです。 補足日時：2019/07/03 17:56

• 表のA列は行番号、G列は合計です。失礼いたしました。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/07/03 18:16

• A列は列番号、G列は合計ということで間違いないです。
  １行目がヘッダーです。

  No.4の回答に寄せられた補足コメントです。 補足日時：2019/07/03 18:21

No.15 回答者： fujillin 回答日時： 2019/07/05 15:34

こんにちは

かなりの人数の方が「わからない」とおっしゃっているので、私にわかるとも思えませんが、勝手な解釈での回答を試みてみます。

500列から2列を取る組み合わせで、単純に総和の最小値を求めるだけなら簡単ですが、
＞各行の合計が1以上
というのが、『2列の各行の組み合わせが｛0,0｝である組合せは対象外とする』ということと解釈しましたので、各行（約1000行分）のチェックを行わなければならないのが面倒そうですね。
500列から2列を取る組合せは約12.5万通りあるので、それぞれに対して1000行分のチェックを行わなければならなくなります。


これらの複合計算を一つの式にまとめるのは相当に面倒そうなので、分解して単純化し、「作業表」を利用する方法を考えてみました。

実際の式はセル位置や表の大きさに影響されるので、ここではミニチュア版として考え方のみの例とします。
添付図では左側の「5列9行（B2:F10の範囲）」を元データとしています。
（計算に関係の無い、ご質問文のG列は削除しています）
※ ご提示のデータのままだと、該当する組み合わせが一つしか出ないので、数値を若干変えて複数が対象となるようにしてあります。

添付図の右側の表は、「列数×列数」の作業表で、ここに全ての組み合わせをチェックした結果を表示します。
実際にはそれなりのサイズになるでしょうから、別シートに作成したほうが良いでしょう。
添付図では、I2セルに以下の式を入れ、必要な範囲にフィルコピーしています。
（5列から2列を取り出す組合せなので、合計10通りになります）
=(SUM(OFFSET($A$2:$A$10,0,LEFT(I$1,1)))+SUM(OFFSET($A$2:$A$10,0,LEFT($H2,1))))/(PRODUCT(INDEX(OFFSET($A$2:$A$10,0,LEFT(I$1,1))+OFFSET($A$2:$A$10,0,LEFT($H2,1)),,))>0)

対象列の組み合わせ（タイトルの薄青部の列の組合わせ）で、その総和を求めています。
『行に{0,0}が存在する組合せを含む』か否かのチェックのために、「総和」を「行列和の各要素の積」で除していますので、「#DIV/0!」のエラー表示の組み合わせは、条件に該当しないことがわかるようになっています。

この表から最小値を求めて、そのタイトル行・列を参照すれば求める組合せがわかりますので、添付図のI8セルには以下の式を入力して、求めたい組合せを算出しています。
添付図の例では、成立している4つの組合せのうち、最小値であるK9に相当する組合せ（B列とE列）を算出します。

=LEFT(RIGHT(OFFSET($H$1,INT(AGGREGATE(15,6,INDEX((ROW(I2:L5)+COLUMN(I2:L5)/10000)/(I2:L5=AGGREGATE(5,6,I2:L5)),,),1))-1,0),2),1)&"列と"&LEFT(RIGHT(OFFSET($H$1,0,MOD(10000*AGGREGATE(15,6,INDEX((ROW(I2:L5)+COLUMN(I2:L5)/10000)/(I2:L5=AGGREGATE(5,6,I2:L5)),,),1),10000)-8),2),1)&"列"

※ エラー処理はしていませんので、組み合わせが存在しない場合にはエラーとなります。
※ はなから解釈が違っている可能性も高いですが、何かのご参考にでもなれば。

No.14 回答者： rukaandkaito 回答日時： 2019/07/05 09:35

No.8です

おっしゃる通りで、その関数は左端のINDEX（列番号）しか取得できません。
No.6で記載している関数ならフィルで出来ますが重いので一つ提案です。
A列INDEXが空白でなければVLOOKUPで取ってくる方法は如何ですか？
No.6よりは随分軽くなると思いますよ。

No.13 回答者： fxq11011 回答日時： 2019/07/04 18:55

＞G列の合計が1以上の「B列、C列、E列」等の3列以上の組み合わせがあるので「最小の組み合わせ」と表現しました

これまともな日本語になっていません。
＞「B列、C列、E列」等の3列以上の組み合わせがあるので
複数列を選択して一覧表にした場合、G列の１０００行がすべて１以上になる列の組み合わせで列の数が最小になる列の選択ということですね。
感覚としては、実際に実行して確認するしかないでしょうね。
少ない列の数の組み合わせなので、当然少ない組み合わせから・・・。
その作業をマクロ等でどこまで自動化できるか・・・がせいぜいのように思います。

No.12 回答者： moto_koukousei 回答日時： 2019/07/04 18:40

No.10へのコメントを読んでも、さっぱり分かりません。

できたら、❶～❹に解説してください。
❶　
①②について
約500列1000行に0と1の数値がランダムに入力され各行の合計は30～60の範囲、各列の合計は80～100、「(0/1の1000行での並びが同じである列)も表中にはあるという条件」については、表中にはありません。
❶－１　文章を長い構文にせず、いくつかの文章に分かって、書き直してください、
❶－２　｢各行の合計は30～60の範囲、各列の合計は80～100、｣を、｢各行の合計は30～60の範囲｣がどこにどのように続くのか、｢各列の合計は80～100｣がどこにどのように続くのか、わかるようにしてください。
❶－３　[各行の合計は30～60の範囲]というのは、どの範囲の合計でしょうか。1列1000行の数値の合計ではないですね。1行約500列の数値の合計でもないですね。[各列の合計は80～100]についても、どのような合計のことかなのかが分かるように、書き直してください。　
❶－４　0と1をランダムに選んで、その選んだ数値を単純合計した場合、合計対象数が300の場合でも、合計値は０～300の範囲にあることになります。[合計はＸ～Ｙ]となっていて、Xが0でも1でもなく、X>1、Yが、Y<300　となる理由が分かり分かりません。　表は0と1をランダム選んで作るのではないのでしょうか。ランダムではない制約条件があるのでしょうか。　あるいは　合計範囲の取り方が、表のある列の全行、表のある行の全列ではなくて、特別のルールで合計する範囲を決めているのでしょうか。　そのあたりが分かる文章に書き直してください。
❶－５　No.10では{(0/1の1000行での並びが同じである列)も表中にはあるという条件であるのかも気になります。}と書きました。　これに対して{「(0/1の1000行での並びが同じである列)も表中にはあるという条件」については、表中にはありません。}ということでしょうか。そうなると｛表中には(0/1の1000行での並びが同じである列)は存在しない｝ということでしょうか。0か1かをランダムに1000個並べると、4個目にも、7個目にも、8個目にも、0か1かあることになります。そのような作業を2回したら、4個目が[0;0]、7個目が[0;0]、8個目が[0;0]となり、3回目をやったら、4個目が[0;0;1]、7個目が[0;0;1]、8個目が[0;0;1]となり、4回目をやったら、4個目が[0;0;1;0]、7個目が[0;0;1;0]、8個目が[0;0;1;0]となり、というように、偶然に試行回数毎に出現する数が同じになるということもおきるというのがランダムということなのだと私は理解しています。表中には同じ並びが出現することも非常にまれには起きえるが、そうした場合には同じ並びとなったものは表から削除して、表中には同じ並びのものはなくしてあるということなのでしょうか。

❷
③について
：各行ごとの約500列の合計は30～60の範囲
：00001110　と10101000　を同等扱い
：上のように合計が3になる組み合わせが15個あった場合には、1つの組み合わせでも良いので列の組み合わせが知りたい
❷－１　No.10では{：各行ごとの約500列の合計が１以上？　(１行すべてが０である)行を除外？　して、ということなのか？}と書きました。これに対して、{（1行約500列の合計が30～60の範囲に入る行)のみを選んで対象にし、約500列の合計が30未満の行は対象にしない、また約500列の合計が61以上の行は対象にしない}というコメントだったのですか。
❷－２　No.10では{：列の組み合わせ？　順列vs組み合せの組み合わせのこと？　00001110　と10101000　を同等扱いすること？}と書きました。これに対して{00001110　と10101000　を同等扱い}と書かれたということは、並び順や組み合わせ状態は無視して、合計値だけを扱えば良いということでしょうか。　そして、合計値が30～60の範囲の、31個の数値だけを扱えば良いということでしょうか。　0と1をランダムにしてある表それ自体はほとんど意味はなく、30～60の範囲の最大31種(例えば、42と53は偶然なかったということもあるから)の数値だけを扱うということでしょうか。　
❷－３　❶の補則解説希望にも{約500列1000行に0と1の数値がランダムに入力され各行の合計は30～60の範囲、各列の合計は80～100、}と引用しましたが、[各行の合計は30～60]と[各列の合計は80～100]とは相互にどのような関係になっていることを述べているのでしょうか。　1行中の列並びの例00001010101……で合計44の行、1列中の行並びの例111001100……で合計95の列がある場合、その扱い方法が分かるように書き直すか、補足してください。
❷－４　No.10では{：上のように合計が3になる組み合わせが15個あった場合には、何を、どのように表示させたいの？}と書きました。これに対して{：上のように合計が3になる組み合わせが15個あった場合には、1つの組み合わせでも良いので列の組み合わせが知りたい}と回答されています。　{00001110　と10101000　を同等扱い}ということは、1行の全列の合計が30～60の範囲になるものの1つの組み合わせでも[それに該当する行の約500列]を表中で示したい、例えば、該当行の約500セルの背景に色をつけたいというようなことでしょうか。
❷－５　もしも❷－４のようなことならば、ある行の全約500列の合計値が30～60の範囲に入ったら、その行のB列～SG列の条件書式で色を付けるというようなことでも良いのでしょうか。　それとも行の全約500列の合計値が30～60の範囲に入った行データだけをフィルタなどで残せばいいのでしょうか。
❷－６　もしも❷－５のようなイメージの場合、[各列の合計は80～100]ということは、どのように扱うことを書いているのでしょうか。

No.11 回答者： ぐーるぐる 回答日時： 2019/07/04 18:02

相変わらず、何がしたいのか説明ができていると思えないけど、

結果どんな答えが欲しいのかな？そしてみんな飽きてそうだけど。。。
Excelに書いて画像貼ってごらんよ。

No.10 回答者： moto_koukousei 回答日時： 2019/07/04 10:34

さっぱり分かりません

①　約500列1000行に0と1の数値が入力されている表
②　0か1を1000行に入力するケースは[2^1000]になります。約500列では全ケースをカバーできません。
　　また、(0/1の1000行での並びが同じである列)も表中にはあるという条件であるのかも気になります。
③　各行の合計が1以上最小の値とした場合の列の組み合せを関数等で表示させたい
　　　：各行ごとの約500列の合計が１以上？　(１行すべてが０である)行を除外？　して、ということなのか？
　　　：列の組み合わせ？　　順列vs組み合せの組み合わせのこと？　　00001110　と10101000　を同等扱いすること？
　　　：そのために　000011100000……⇒３　101000100000……⇒３　のように、全列の合計を計算しているの？
　　　：上のように合計が3になる組み合わせが15個あった場合には、何を、どのように表示させたいの？
④　「B列、E列」のほかにG列の合計が1以上の「B列、C列、E列」等の3列以上の組み合わせがあるので「最小の組み合わせ」と表現しました。
　　０か１が約500列で、合計が1以上ならば、合計は（１，２，３，４，……、（約500行だから）…… 約500）の中の約500種、
　　①の表のデータ状況によっては、全列合計が、22か123かのどちらかで（100行で）2種類しかないということもありうる
　　0か1かを20行並べる並びでも100万通りにはなるけれど、その中から約500通りを選んだ場合、各行の合計値は0～約500の中の1値
　　なにを、どのように表示させたいの？

この回答へのお礼

ありがとうございます。
①②について
約500列1000行に0と1の数値がランダムに入力され各行の合計は30～60の範囲、各列の合計は80～100、「(0/1の1000行での並びが同じである列)も表中にはあるという条件」については、表中にはありません。
③について
：各行ごとの約500列の合計は30～60の範囲
：00001110　と10101000　を同等扱い
：上のように合計が3になる組み合わせが15個あった場合には、1つの組み合わせでも良いので列の組み合わせが知りたい
④については前段のとおりです。わかりにくくてすみません。

お礼日時：2019/07/04 13:48

No.9 回答者： ぐーるぐる 回答日時： 2019/07/03 18:38

貴方がやりたいのはこういう事なのかな？

画像を今度から貼ってみてね。
分かりにくいから質問が多いんだよ(^_^;)

=SUM(テーブル13[[#この行],[あ]:[お]])

この回答へのお礼

今度から画像を貼るようにします。ご指摘ありがとうございます。
貼っていただいた表中の合計欄は、ご指摘の関数ではなくただの合計です。
知りたいのは、合計1以上を満たす（最小の）列の組み合わせです。
「あ列」と「え列」が全ての行合計が1になるので、どうにかしてその解がときたいのです。
実際の表には約500列1000行に0、1がランダムに入力され、行合計は30～60の範囲、列合計は80～100の範囲とイメージしていただけると幸いです。

お礼日時：2019/07/04 13:30

No.8 回答者： rukaandkaito 回答日時： 2019/07/03 18:03

No.7です

再び登場しました(笑)
エラー処理が抜けていましたので
『=IFERROR(SMALL(IF(Sheet1!$SH:$SH=MIN(Sheet1!$SH:$SH),ROW($A:$A)),ROW(A1)),"")』
こちらでお願いします(;´･ω･)

この回答へのお礼

ご検討くださり感謝いたします。
取り急ぎ例の表で確認しました。シート２のＡ１に『{=IFERROR(SMALL(IF(Sheet1!$G:$G=MIN(Sheet1!$G:$G),ROW($A:$A)),ROW(A1)),"")}』の結果は「5」でした。組み合わせ内容も知りたいので横にフィルすると1行目が全て「5」でした。
例の表で、行合計の1以上を満たす最小の組み合わせ「B列、E列」が分かりませんでした。
ご指摘のとおり「当該関数は死ぬほど遅くなって動作保証できない」ようですので関数では無理のような気がしてきました。

お礼日時：2019/07/04 13:14

No.7 回答者： rukaandkaito 回答日時： 2019/07/03 17:56

No.6です

たびたびすみません(笑)
じっくり考えてみたのですが、A列に連番が入っており、Ｂ～ＳＧまで５００列にデータがあってＳＨに合計がある前提・・・だとするとＩＮＤＥＸと行番号が等しくなるため、先ほどの関数を簡素化出来ますね！
シート２のＡ１に入れる関数は
『=SMALL(IF(Sheet1!$SH:$SH=MIN(Sheet1!$SH:$SH),ROW($A:$A)),ROW(A1))』
これだけで良いですね♪
後は同様に編集状態でCtrl＋Shift＋Enterで確定して『配列数式』にしていただければ、下に２～３０行フィルすることで行番号だけなら取得出来ます。
お試しください！

No.6 回答者： rukaandkaito 回答日時： 2019/07/03 17:44

No.4です

先ほどの前提条件にて関数を作成しました。
データはシート１に記載されているものとし、シート２のＡ１に以下の関数を挿入してください
なお、約５００列ということだったので参照列は行見出しと合計を含む５０２番目のＳＨとしています
『=IF(COUNTIF(Sheet1!$SH:$SH,MIN(Sheet1!$SH:$SH))<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$SH:$SH=MIN(Sheet1!$SH:$SH),ROW($A:$A)),ROW(A1))))』
関数が入力出来たら確定の前にCtrl＋Shiftを押しながらEnterで確定してください
こちらは配列関数となりますので、上手くいけばセル内の関数には前後に『{}』が付いているはずです。
１０００行の内の該当数はどれくらいを想定しているでしょうか？
１０件程度であればA1からA10までフィルしていただければ良いですが、多くなるほど遅くなります。当該関数は死ぬほど遅いです(笑)
組み合わせを知りたいということでしたのでシート２に行番号のみを抜き出すようにしましたが、組み合わせ内容も知りたければ横にフィルして５００列・・・これははっきり言って動作保証が出来ません・・・

という事で代案です。
合計列ＳＨ２の横にＭＩＮ(ＳＨ：ＳＨ)で最小値を求め、フィルタでそれと一致するデータを表示する方が数段早いのではないでしょうか？
１以上の最小値ということなのでこの場合０が該当する場合もありますが(笑)
シートをコピーしておいて、０の行は削除する・・・というのじゃダメですかねｗ