エクセルで複数文字を結合してランダムに抽出したい

質問

よろしくお願いします。

エクセルで複数の単語をランダムに組み合わせて、
なおかつ重複がないように表示させる方法を教えて頂けないでしょうか？

例）

　　A　　B　　　C　　　　　D
1　父　　は　おはよう　　と言った
2　母　　が　おやすみ　　と言う
3　兄　　と　こんにちは　と言わなくてはいけない
4　姉　　へ　さようなら　と言おうか迷っている
5　妹　　に　こんばんは　と言おう

ランダムで以下の様に表記

父がさようならと言う
兄はおはようと言おうか迷っている
母とこんばんはと言おう
妹へおやすみと言った
姉にこんにちはと言わなくてはいけない
父とおやすみと言おう

よろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

お示しのようにデータがA列からD列に入力されているとします。
そこで作業列を作って対応します。
H１セルには次の式を入力してK１セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(A1="","",RAND())

そこでお求めの文章ですがF列に表示させるとしてF１セルには次の式を入力して下方にドラッグコピーします。

=IF(A1="","",INDEX(A:A,MATCH(SMALL(H:H,ROW(A1)),H:H,0))&INDEX(B:B,MATCH(SMALL(I:I,ROW(A1)),I:I,0))&INDEX(C:C,MATCH(SMALL(J:J,ROW(A1)),J:J,0))&INDEX(D:D,MATCH(SMALL(K:K,ROW(A1)),K:K,0)))

なお、念のために同じ文章になったものが有る場合にはG列に１の表示をさせるためにG１セルに次の式を入力して下方にドラッグコピーします。

=IF(F1="","",IF(COUNTIF(F$1:F1,F1)=1,"",1))

同じ文章が有ることは通常は考えられませんがもしも１がG列に見られた場合にはF９キーを押すことで再表示をさせるようにすればよいでしょう。

お示しの答えの例では最初の表が５行であるのに６行になっていますね。このように多くのケースを書くのでしたらA１セルからD５のデータを範囲として下方にドラッグコピーするようにすればよいでしょう。

お礼

ありがとうございます。

希望に近いものができあがりました。

この計算式を元に理想のシートを作り上げていきます。

回答No.2

Cupper-2

自分だったら、列の中での並べ替えと捉えて処理をするかな。

各セルにＲＡＮＤ関数を使って乱数を割り当てて、数値の大きい順または小さい順に並べ替えます。
でもってそれをつなげて表示。

例えば、Ａ列に対して作業列としてＥ列を、Ｂ列にはＦ列を、Ｃ列にはＧ列、Ｄ列にはＨ列を
それぞれ割り当てて処理するかなあ。（作業列に乱数を格納します）

でもって、乱数で同じ数字が出ちゃうと困るので、乱数値の最小桁の数値よりも小さい値を
上から順に加えておきます。
　例：＝ＲＡＮＤ（）＊１００＋ＲＯＷ（）／０．０１
これなら偶然同じ乱数が発生しても行番号の若い行が小さい値になり順番に並べる
…ってか抽出することができるようになります。
（※乱数を使った順位の決定をするときに使う処理方法です。
　　滅多に使わないけど応用が効くので覚えておくと吉）

そんなこんなで、
　＝ＩＮＤＥＸ（Ａ１：Ａ５，ＭＡＴＣＨ（ＬＡＲＧＥ（Ｅ１：Ｅ５，１）Ｅ１：Ｅ５，０））
これで一番大きい乱数に対する文字列を拾えます。
あとは同様にしてＢ列、Ｃ列、Ｄ列に対し処理を行いこれを繋げるだけ。
（絶対参照とか指定しておくとコピーして２番目や３番目の文字列を拾い出すのが楽になりますよ）

補足

ありがとうございます。

教えて頂いたRAND関数とINDEX関数を使ってみたのですが、
ランダムに表示されずに、常に同じ値だけを返してきてしまいます・・・

回答No.1

keithin

＞なおかつ重複がないように表示させる方法

手順：
ご利用のエクセルのバージョンに応じた方法でエクセルのオプションで反復計算のチェックを入れる

Ｆ１セルに
=IF(OR(F1=0,COUNTIF($F$1:F1,F1)>1),INDEX(A:A,INT(RAND()*5)+1)&INDEX(B:B,INT(RAND()*5)+1)&INDEX(C:C,INT(RAND()*5)+1)&INDEX(D:D,INT(RAND()*5)+1),F1)
と記入し、必要数下向けにコピー貼り付ける。

＃補足
＞単語をランダムに組み合わせて、

ランダムに組み合わせるだけなら、ふつーに乱数を使えばごく簡単にできます。
しかし一般のやり方では、一度イイカンジで組み合わせが出来ても何かの拍子ですぐに全部再計算されてしまい、答えを固定するにはコピーして形式を選んで貼り付けでわざわざ値に変えるみたいな手間が必要です。
上述の方法は一回結果を出したら、そこで結果は固定になります。

＃再計算したいときは一回Ｆ２以下の数式を全部消去、改めてＦ１の式をコピー貼り付け直します。

補足

ありがとうございます。

言葉足らずで申し訳ありません。

ブログタイトルや、簡単な紹介文に使いたいと考えており、
再計算も頻繁に使うので、固定されないほうがありがたいです。