HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Excel 2021)
複数列のデータの組み合わせ
投稿日時: 25/11/09 23:25:47
投稿者: Poyann

皆様よろしくお願いいたします。
 
BA1:BC7に
A B C
1 6 4
2 11 7
3 14 9
5 16 10
8 17 12
13 18 15
 
という数字が入力されています。
 
A,B,Cそれぞれのグループに含まれる数字があるわけですが、
これらから3つの数字の組み合わせを表示する式を作りたいです。
組み合わせのパターンとしては、例えばBから2つ、Cから1つ数字を選んだ組み合わせを表示する式をどなたか教えてください。
結果は
D列、E列、F列に組み合わせの3つの数字をする、また3つの数字は順不同でD列<E列<F列で、仮に数字が重複する組み合わせは除外したいです。
 
Let関数を使うらしいですが、上手くいかなかったためできれば難しい式を使わない方法でやる必要があります。またVBAではなく関数で表示したいです。
回答
投稿日時: 25/11/10 11:47:59
投稿者: sk

引用:
BA1:BC7に
A B C
1 6 4
2 11 7
3 14 9
5 16 10
8 17 12
13 18 15
  
という数字が入力されています。

セル範囲 BA2:BC7 において、いずれかのセルの値が
他のセルの値と重複することはないのでしょうか。
 
引用:
Bから2つ、Cから1つ数字を選んだ組み合わせ

引用:
数字が重複する組み合わせは除外したい

例えば、値が 19 であるセルが B グループにも C グループにも含まれているとして、
もし B グループから 19 が選ばれたら C グループからは 19 を選ばないようにする、
といった処理を行なう必要はないのでしょうか。
 
そういったことを行なわず、単純に「 B グループからランダムに 2 つのセルを抜き出す」
「 C グループからランダムに 1 つのセルを抜き出す」というだけならば、
例えば次のような数式を設定なされば一応は可能でしょうけど。
 
（ D1 セルの数式）
------------------------------------------------------------
 
=LET(source,$BB$2:$BB$7,keys,RANDARRAY(ROWS(source),1,0,ROWS(source),FALSE),sorted,SORTBY(source,keys,1),TOROW(CHOOSEROWS(sorted,1,2)))
 
------------------------------------------------------------
 
（ F1 セルの数式）
------------------------------------------------------------
 
=LET(source,$BC$2:$BC$7,key,RANDBETWEEN(1,ROWS(source)),CHOOSEROWS(source,key))
 
------------------------------------------------------------
 
引用:
Windows 11 Pro : Excel 2021

但し、その Excel のバージョンだと一部の関数が対応していないかも知れません。
回答
投稿日時: 25/11/10 15:42:20
投稿者: Suzu

数式自体について、当方から どうこう言えるスキルは持ち合わせていませんが
回答者の立場から、条件が不明瞭なので、それを明確にする為の質問をさせてください。
 
 

引用:
例えばBから2つ、Cから1つ数字を選んだ組み合わせを表示
ABCそれぞれから1つづつ、あるいは、Aから1つ C から2つ の様に変わる可能性があるのでしょうか？
それを、どこかのセルで指定する？
 
引用:
D列、E列、F列に組み合わせの3つの数字
2行目だけに表示ではなく、条件に一致する組み合わせ全てを行単位に表示させるのでしょうか？
それとも、全ての組み合わせから、そのうちの一つだけ表示すれば良いのでしょうか？
 
引用:
3つの数字は順不同でD列<E列<F列
例では、A列ならA列内で昇順に並んでいるがそれが保証されないという事でしょうか？
値が小さい順に、D、E、F列 に並んでほしいという事でしょうか？
　3つの数字が順不動 なのに、値が小さい順に、D、E、F列 に並んでほしい
　 は順不同とは言わないでしょうし・・
当方には、意図が判らないので、詳しくご説明ください。
 
引用:
仮に数字が重複する組み合わせは除外
もともと、A2：C7 には 重複した値が無い前提でしょうか？
　　　　　A列ならA列、B列ならB列、C列にはC列 に 重複値が無い前提？
それとも、B列から 2つ 取り出したとき、その二つは同じ値になってはいけない？
 
 
例の数字群から
・B列から2つ、C列から 1つ
・同じ列から抽出する値は重複してはいけない(今回はB列から取得する値)
・D列<E列<F列
の条件だけでも
 
組み合わせとしては
6    11    12
6    11    15
6    14    15
11    14    15
しか無い。
 
例に対し、どういう結果を得たいのか明示しれもらえると、
質問者さんと、回答者の間で条件について意思疎通が取りやすいと思います。
 
 
引用:
できれば難しい式を使わない方法でやる必要があります。

skさんが既に提示くださっていますが、
少なくとも D列<E列<F列[ が加われば 数式長さは倍になるでしょう。
他の条件も加味するのであれば、もっと長くなるのは自明ですね。
回答
投稿日時: 25/11/10 19:04:02
投稿者: んなっと

おなじみの組合せ列挙ですね。
2021はBYROWなどの強力な関数が使えないので、面倒です。
 
まずD1:F1にA:C各列の選択数を入力して...
 
　　A　 B　 C　D　E　F
1　 A　 B　 C　0　2　1
2　 1　 6　 4　　　　　
3　 2　11　 7　　　　　
4　 3　14　 9　　　　　
5　 5　16　10　　　　　
6　 8　17　12　　　　　
7　13　18　15　　　　　
 
G1
=LET(a,A2:C7,b,ROWS(a),c,COLUMNS(a),d,D1:F1*1,
p,SUM(d),q,SEQUENCE(,p),
v,INDEX(MMULT(d,N(SEQUENCE(c)<=SEQUENCE(,c))),1,),
w,XMATCH(q,v,1),
x,MOD(INT(SEQUENCE(b^p,,0)/b^(p-q)),b)+1,
INDEX(a,x,w))
 
M1
=IF(G1="","",LET(d,INDEX(G$1#,ROW(),),IF(AND(XMATCH(d,d)=SEQUENCE(,COLUMNS(d))),SORT(d,,,TRUE),"")))
下方向・↓
 
S1
=UNIQUE(FILTER(OFFSET(M:M,,,,COLUMNS(G$1#)),M:M<>""))
 
 
　　 A　 B　 C　D　E　F　G　 H　 I　J　K　L　M　 N　 O　P　Q　R　S　 T　 U
 1　 A　 B　 C　0　2　1　6　 6　 4　　　　　　　　　　　　　　　 4　 6　11
 2　 1　 6　 4　　　　　 6　 6　 7　　　　　　　　　　　　　　　 6　 7　11
 3　 2　11　 7　　　　　 6　 6　 9　　　　　　　　　　　　　　　 6　 9　11
 4　 3　14　 9　　　　　 6　 6　10　　　　　　　　　　　　　　　 6　10　11
 5　 5　16　10　　　　　 6　 6　12　　　　　　　　　　　　　　　 6　11　12
 6　 8　17　12　　　　　 6　 6　15　　　　　　　　　　　　　　　 6　11　15
 7　13　18　15　　　　　 6　11　 4　　　　　 4　 6　11　　　　　 4　 6　14
 8　　　　　　　　　　　 6　11　 7　　　　　 6　 7　11　　　　　 6　 7　14
 9　　　　　　　　　　　 6　11　 9　　　　　 6　 9　11　　　　　 6　 9　14
10　　　　　　　　　　　 6　11　10　　　　　 6　10　11　　　　　 6　10　14
11　　　　　　　　　　　 6　11　12　　　　　 6　11　12　　　　　 6　12　14
12　　　　　　　　　　　 6　11　15　　　　　 6　11　15　　　　　 6　14　15
13　　　　　　　　　　　 6　14　 4　　　　　 4　 6　14　　　　　 4　 6　16
14　　　　　　　　　　　 6　14　 7　　　　　 6　 7　14　　　　　 6　 7　16
15　　　　　　　　　　　 6　14　 9　　　　　 6　 9　14　　　　　 6　 9　16
16　　　　　　　　　　　 6　14　10　　　　　 6　10　14　　　　　 6　10　16
17　　　　　　　　　　　 6　14　12　　　　　 6　12　14　　　　　 6　12　16
18　　　　　　　　　　　 6　14　15　　　　　 6　14　15　　　　　 6　15　16
19　　　　　　　　　　　 6　16　 4　　　　　 4　 6　16　　　　　 4　 6　17

投稿日時: 25/11/10 21:52:48
投稿者: Poyann

皆様、ありがとうございます。
んなっと様、実際に入力してうまくいきました。
おっしゃる通り、ものすごく長い式になりますね。
ただ365やweb版が使えないため、こちらの方法で進めさせていただきます。
ご協力いただき、ありがとうございます。