[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『重複しない組合せ数を求める方法』（はっしー）
①②③④－③④⑤⑥⑦－⑤⑥⑦⑧⑨⑩⑪⑫ で組合せを考えたときの組合せ数を求める式がわかりません。
Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ Ｊ Ｋ Ｌ
1 ① ② ③ ④ ⑤ ⑥ ⑦ ⑧ ⑨ ⑩ ⑪ ⑫
2
上記のようにＡ～Ｌ列に①～⑫までが入力されてるとして、 組合せ数を求める式はどのようにしたら良いのでしょう？
組合せを求める際の条件としては
（1）１つの組合せの中に同じ数字は重複しない。
例） 3-5-5 4-4-8 など
（2）組順の違う同じ数字の組合せは重複しない。
例） 4-5-6 4-6-5 など
※競馬などの３連複フォーメーションをイメージしてもらえれば良いと思います。 ３連単ではないです。
考え方としては…
１番目が①のとき ａ．①－③④－⑤⑥⑦⑧⑨⑩⑪⑫→16点 ｂ．①－⑤⑥⑦－⑤⑥⑦→3点 ｃ．①－⑤⑥⑦－⑧⑨⑩⑪⑫→15点（小計）34点
１番目が②のとき ｄ．②－③④－⑤⑥⑦⑧⑨⑩⑪⑫→16点 ｅ．②－⑤⑥⑦－⑤⑥⑦→3点 ｆ．②－⑤⑥⑦－⑧⑨⑩⑪⑫→15点（小計）34点
１番目が③④のとき ｇ．③④－③④－⑤⑥⑦⑧⑨⑩⑪⑫→8点 ｈ．③④－⑤⑥⑦－⑤⑥⑦→6点 ｉ．③④－⑤⑥⑦－⑧⑨⑩⑪⑫→30点（小計）44点
（合計）ａ＋ｂ＋ｃ＋ｄ＋ｅ＋ｆ＋ｇ＋ｈ＋ｉ＝112点
ですが、これを求めるための式をExcel上で作る場合どのようにすればよいですか？
[Excel2003,WindowsXP]
順列組合せの「組合せ数」なら COMBIN（組合せ）、 PERMUT （順列）などの関数があるし、別の方法で http://homepage1.nifty.com/gfk/permutation.htm
ただ、要件は、その組み合わせたもの、そのもののピックアップ、組み合わせた要素の合計。 これについては、過去にもいろんなところで様々なＱ／Ａがでているけど、「関数式」で解決したものは みたことがない。おそらくはＶＢＡ処理が必要なのでは？
VBA 組合せ といったワードで検索すると事例が探せると思う。
追記） あぁ、組合せの数でよかったんだね。
(ぶらっと)
下記で解決したのでしょうか
http://www.efcit.co.jp/cgi-bin2/exqalounge.cgi?print+201109/11090018.txt
①②③④－③④⑤⑥⑦－⑤⑥⑦⑧⑨⑩⑪⑫で組合せを Aグループ Bグループ Cグループ
A,B,Cグループに共通する数字はあるのかな? Bグループのみに存在する数値はあるのかな? (その場合A,Cグループ共通数字はありますか)
すべての条件を網羅して数式を考えるより、条件が少ないほど式も簡単になるので・・・
By
上記で紹介していただいたサイトを見ました。数式を関数式に展開するには いろいろ試みてみましたが今の私にはちょっと壁が高いです。
また、VBAについては基礎の基礎からやる必要があり、今この課題にて取り込むことは 時間的にも技量的にも非常に難しいので、またの機会ということにしたいと思います。
By様、ありがとうございます。お世話になります。
上記サイトでは確認を怠った自分が悪いのですが禁止事項に触れたようですので、回答 して頂いてた方たちには申し訳なく思いましたが、未解決での終了をさせて頂きました。 今後はできればこちらで解決までお世話になれればと思ってますので宜しくお願いします。
「Aグループ－Bグループ－Cグループ」という表示形式で
Aグループ＝①②③④ Bグループ＝③④⑤⑥⑦ Cグループ＝⑤⑥⑦⑧⑨⑩⑪⑫
という規則性があるため、
>A,B,Cグループに共通する数字はあるのかな? A,B,C全てに共通するものはありません。
>Bグループのみに存在する数値はあるのかな? ③④はAグループにも含まれ、⑤⑥⑦はCグループにも含まれるためBグループのみはありません。
>(その場合A,Cグループ共通数字はありますか) Aグループは①～④、Bグループは⑤～⑫なのでAとCのグループに共通はありません。
例えば、"④＝15" である場合、"15"はAグループとBグループにしか存在しません。 また、①～⑫には全て違う数字が入ります。（①＝10、⑥＝10等はありえません）
自分でも"COMBIN"、"PERMUT"や"FACT"などを組み合わせていろいろ試行錯誤してますが 今のところ形になっていません。
出来ることであれば1セル内での処理を考えてますが、作業域などが必要なんでしょうかね。
状況としては… 組合せを表示する「①②③④－③④⑤⑥⑦－⑤⑥⑦⑧⑨⑩⑪⑫ 計○通り」的な表示が 1シート内で100～200程度あり、それをシートコピーして使います。 1ブックあたり最大で10シート程度で構成されます。
このような感じなので、1セルに式を詰め込むのと別スペースに作業域を設けるのとで どちらがより軽い動作をしてくれるのか？ということも悩んではいますが…、まぁそれは 出来上がりを確認した後でもいいことですが…。
何はともあれ、いろいろと問題や不備等もあるかもしれませんが不明な点がありましたら聞いていただければ答えますので、何卒宜しくお願い致します。
（はっしー）
表がこんな感じとして
A B C D E F G H I 1 Aグループ ① ② ③ ④ 2 Bグループ ③ ④ ⑤ ⑥ ⑦ 3 Cグループ ⑤ ⑥ ⑦ ⑧ ⑨ ⑩ ⑪ ⑫ 4 5 6 2 2 3 5 7 8 112
A6=SUMPRODUCT((COUNTIF($B$1:$I$3,B1:E1)=1)*1) ・・・Aグループのみ(①②)の個数 B6=COUNTA(B1:E1)-A6 ・・・A,B共通(③④)の個数 C6=COUNTA(B3:I3)-D6 ・・・B,C共通(⑤⑥⑦)の個数 D6=SUMPRODUCT((COUNTIF($B$1:$I$3,B3:I3)=1)*1) ・・・Cのみ(⑧⑨⑩⑪⑫)の個数
A8=(A6*B6+COMBIN(B6,2))*(C6+D6)+(A6+B6)*(C6*D6+COMBIN(C6,2))
作業用セルを使用したくない場合は、A6:D6の式をA8に代入してください
こちらの方がいいかな =(A6*B6+IF(B6<2,0,COMBIN(B6,2)))*(C6+D6)+(A6+B6)*(C6*D6+IF(C6<2,0,COMBIN(C6,2)))
9/16 8:40 一部編集(説明補足)
By
>１番目が①のとき >ａ．①－③④－⑤⑥⑦⑧⑨⑩⑪⑫→16点 >ｂ．①－⑤⑥⑦－⑤⑥⑦→3点 >ｃ．①－⑤⑥⑦－⑧⑨⑩⑪⑫→15点（小計）34点
>１番目が②のとき >ｄ．②－③④－⑤⑥⑦⑧⑨⑩⑪⑫→16点 >ｅ．②－⑤⑥⑦－⑤⑥⑦→3点 >ｆ．②－⑤⑥⑦－⑧⑨⑩⑪⑫→15点（小計）34点
>１番目が③④のとき >ｇ．③④－③④－⑤⑥⑦⑧⑨⑩⑪⑫→8点 >ｈ．③④－⑤⑥⑦－⑤⑥⑦→6点 >ｉ．③④－⑤⑥⑦－⑧⑨⑩⑪⑫→30点（小計）44点
>（合計）ａ＋ｂ＋ｃ＋ｄ＋ｅ＋ｆ＋ｇ＋ｈ＋ｉ＝112点
これを式にすればいいのでは・・ 式１ a+d ①②－③④－⑤⑥⑦⑧⑨⑩⑪⑫ 2 * 2 * 8 = 32 (Aグループのみの個数 * A,B共通の個数 * Cグループの個数)
式２ g ③④－③④－⑤⑥⑦⑧⑨⑩⑪⑫ COMBIN(2,2)* 8 =8 (A,B共通の値の組み合わせ*Cグループの個数)
式１と式２を加算したものが、前回提示した式の前段 (A6*B6+COMBIN(B6,2))*(C6+D6) になります
式３ b+e+h ①②③④－⑤⑥⑦－⑤⑥⑦ 4 * COMBIN(3,2) =12 (Aグループの個数 * B,C共通の値の組み合わせ)
式４ c+f+i ①②③④－⑤⑥⑦－⑧⑨⑩⑪⑫ 4 * 3 * 5 =60 (Aグループの個数 * B,C共通の個数 * Cグループのみの個数)
式３と式４を加算したものが、前回提示した式の後段 (A6+B6)*(C6*D6+COMBIN(C6,2)) になります
この回答は、いろんな条件ごとに重複しない組み合わせを計算し加算したものです
マルチポストしたサイトに、SHさんの回答がありますので参考までに・・ (すべての組み合わせから重複組み合わせを引くという考え方)
最後に >（合計）ａ＋ｂ＋ｃ＋ｄ＋ｅ＋ｆ＋ｇ＋ｈ＋ｉ＝112点 というように、考え方は理解されているようですので 個々の計算式(Aグループのみの個数 や A,B共通の個数)の立て方の ヒントが提示されていますので応用してみて下さい
因みに、ながながと回答をしましたが、下記の質問文から どこにどんな値が有り、どこをみれはAグルーブが①②③④とわかるのでしょう
>①②③④－③④⑤⑥⑦－⑤⑥⑦⑧⑨⑩⑪⑫ >で組合せを考えたときの組合せ数を求める式がわかりません。
> Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ Ｊ Ｋ Ｌ >1 ① ② ③ ④ ⑤ ⑥ ⑦ ⑧ ⑨ ⑩ ⑪ ⑫ >2
>上記のようにＡ～Ｌ列に①～⑫までが入力されてるとして、 >組合せ数を求める式はどのようにしたら良いのでしょう？
By
By様、ありがとうございます。 大変お世話になってます。
非常にわかりやすく説明して頂き、感謝してます。 By様の例を以下のように試して見ました。
> A B C D E F G H I >1 Aグループ ① ② ③ ④ >2 Bグループ ③ ④ ⑤ ⑥ ⑦ >3 Cグループ ⑤ ⑥ ⑦ ⑧ ⑨ ⑩ ⑪ ⑫ >4 >5 >6 2 2 3 5 >7 >8 112
この配列で各々指定のセルに式を挿入したところうまくいきました。 それを踏まえ、実際のワークシートへ展開しましたところ以下のような現象が出ました。
（例1） Aグループ 12 2 11 8 Bグループ 11 8 4 1 6 Cグループ 4 1 6 7 3 10 78 2 2 3 3 ※1 "78"は上記の"112"（A8)、続いて左から"2","2","3","3"は上記のA6～D6の式を入れてます。
（例2） Aグループ 17 12 16 5 Bグループ 16 5 1 10 18 Cグループ 1 10 18 13 9 8 4 79 2 2 2 5
（例1）のように通常は正解が表示されます。全グループがフルに入力されてる場合は組数"112"になり、問題ありません。 しかし、（例2）のような"79"が表示される場合があります。Cグループ末尾"4"を消すと表示は"78"と なり正常にもどります。 また、"4"の後ろへ重複しない別の数字を入力する（フル入力状態）とこれもまた、"112"と正常です。
正常であれば（例2）の場合"95"と表示されなければならないのですが…。
実際に使用してるワークシートでは提案していただいた式を挿入するセルが違うため、入力間違いなど 無いかセル番、範囲など何度も確認しましたが問題はありません。（と、思います）
ただ1つ、引っかかってるのは実際に入力してる部分を今回の計算させる部分に参照させてることです。
ここでいうB1:I3の数値は別で入力したものを参照してます。 入力部分が空欄だと参照部分は"0"表示されるため、それを回避するために （例）=IF(B1="","",B1) という式をいれてます。
それ以外は問題ないと思います。 また、（例2）でいうCグループの数字の数が7個の場合におかしくなるのか、6個や8個（フル）では問題ないのか不思議です。 一応、5個の場合から1個の場合まで確認しました。おかしくなるのは7個の場合だけです。 まだどこか確認不足というか勘違いなどしてる範囲や式があるのかなぁ… はっきり言って、今テンパってます。
By様からの質問ですが… >どこにどんな値が有り、どこをみれはAグルーブが①②③④とわかるのでしょう
ワークシートの①～⑫の表示の下のセルに該当する数字を入力します。 別表で①②③④－③④⑤⑥⑦－⑤⑥⑦⑧⑨⑩⑪⑫のパターン表示があります。 また、組合せの一覧（①-③-⑤・・・④-⑦-⑫まで）がありそれを見ながら組数を手入力してました。
今までも自動で表示できるよう試行錯誤しましたができず、仕方なく数えて入力してました。 しかしながら、このままだとこの作業だけで大変時間や労力を割いてしまい効率が悪すぎるので 今回どうにかしたいと再度、取り掛かった次第です。
あと少しのところまで来てると思いますので、できればもう少しお付き合い頂ければ助かります。 宜しくお願いします。
（はっしー）
>（例2） >Aグループ 17 12 16 5 >Bグループ 16 5 1 10 18 >Cグループ 1 10 18 13 9 8 4 >79 2 2 2 5
最後の行が 79 2 2 3 4 になっていないと言うことは、Cグループの個数がきちんと計算されていないと言うことです
>ここでいうB1:I3の数値は別で入力したものを参照してます。 >入力部分が空欄だと参照部分は"0"表示されるため、それを回避するために >（例）=IF(B1="","",B1) >という式をいれてます。 数式により、空白にしているところが、個数を正しく出せない原因です
空白に見えるセルが、未入力セルと数式で空白文字にしているセルが混在しませんか
>B6=COUNTA(B1:E1)-A6 ・・・A,B共通(③④)の個数 >C6=COUNTA(B3:I3)-D6 ・・・B,C共通(⑤⑥⑦)の個数 の式の COUNTA を COUNT に変更してみたらどのようになりますか
変更した式( Cのみ(⑧⑨⑩⑪⑫)の個数を求める式、B,C共通(⑤⑥⑦)の個数を求める式) および空白に見えるセルに入力した式を提示ください
By
By様、ありがとうございます。お世話になります。
>COUNTA を COUNT に変更してみたらどのようになりますか
説明不足ですみません。前記投稿前に変更済みです。COUNT にしても起こる現象です。
>変更した式( Cのみ(⑧⑨⑩⑪⑫)の個数を求める式、B,C共通(⑤⑥⑦)の個数を求める式)
Cのみ(⑧⑨⑩⑪⑫) BE17に =SUMPRODUCT((COUNTIF($BB$14:$BI$16,BB16:BI16)=1)*1) B,C共通(⑤⑥⑦) BD17に =COUNT(BB16:BI16)-BE17
>および空白に見えるセルに入力した式を提示ください
Aグループ 17 12 16 5 ←17=IF(F16="","",F16)、12=IF(G16="","",G16)、・・・5=IF(I16="","",I16) Bグループ 16 5 1 10 18 ←16=IF(H16="","",H16)、5=IF(I16="","",I16)、・・・18=IF(M16="","",M16) Cグループ 1 10 18 13 9 8 4 ←1=IF(K16="","",K16)、10=IF(L16="","",L16)・・・4=IF(Q16="","",Q16) ※BB14～BI16に参照させてます。 そのうちBF14～BI14、BG15～BI15は空欄です。
（はっしー）
とりあえずの解決方法として
>Cのみ(⑧⑨⑩⑪⑫) BE17に =SUMPRODUCT((COUNTIF($BB$14:$BI$16,BB16:BI16)=1)*1) =SUMPRODUCT((COUNTIF($BB$14:$BI$16,BB16:BI16)*(BB16:BI16<>"")=1)*1)
BG16:BI15 が スペースまたは0になっていませんか
By
By様、おはようございます。 ありがとうございます、お世話になります。
> BG16:BI15 が スペースまたは0になっていませんか BG14：BI15 or BG15:BI15 ではなくてですか？ BG16：BI16にはCグループの参照式が入ってます。 BG15：BI15は空欄でスペースor0は入ってません。 またBF14:BI14も空欄で何も入ってません。
>=SUMPRODUCT((COUNTIF($BB$14:$BI$16,BB16:BI16)*(BB16:BI16<>"")=1)*1) これを挿入したところ正常に計算されました。
また上記の空欄部分に入力することの無い空きセルを参照させる式（例=IF(A1="","",A1)）などを 入れて対応しても正常に計算されました。（でも、これは邪道ですかね）
何れにせよ、これでうまく行けそうです。 とても感謝しています。本当にありがとうございました。
（はっしー）
>> BG16:BI15 が スペースまたは0になっていませんか >BG14：BI15 or BG15:BI15 ではなくてですか？ おみ込みの通り、間違いです すいませんでした
>BG15：BI15は空欄でスペースor0は入ってません。 空欄というのは、未入力と言うことですね
>また上記の空欄部分に入力することの無い空きセルを参照させる式（例=IF(A1="","",A1)） >などを入れて対応しても正常に計算されました。（でも、これは邪道ですかね） 今回の場合に限れば、それでも問題ありません 邪道と考えるかどうかは、個々人の見解の相違だと思います
By
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.