Excelでしりとりを作る方法

いつもお世話になっております。よろしくお願いします。

Excelでしりとりを作りたいです。

100個ほどの単語があるとします。
最初の文字と最後の文字を抽出することが可能ですよね?
それを踏まえてそれらをつないでしりとりを作る事は可能でしょうか?

結果的に100個の単語が全部繋がらなくてもそれは仕方がないのですが、なるべく長くしりとりでつながると理想的です。
極力同じ言葉は二度と使わないようにしたいですが、使うことでしりとりが長くつながるのであればそれはそれで構わないです。

ちなみに添付画像のA2は「き」で繋げ、A14は「じ」で繋ぐというように、いまは後ろから2つ目の文字で繋げていますが、
仲間内で使うだけなので、そのあたりはあいまいでも差支えないです。

できれば関数などで簡単にできれば良いのですが、何かマクロなどを使わないといけなかったりVBなどを使わないといけないのであればなるべく簡単な方法を教えてください。

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

• ありがとうございます。
  
  いま添付のようになりました。
  次のワードだけがわかる形なんですね?
  
  例えばH列に並べ替えて全部表示できたりできませんでしょうか。
  H1:いす
  H2:すとれす
  H3:すぴーち
  H4:ちきゅう
  …といった感じで。
  
  よろしくお願いいたします。

  
    補足日時：2019/06/25 13:58

• H1:いす
  H2:すとれす
  H3:すぴーち…
  
  と表示させるには、
  H1:=A1
  H2:=A18
  H3:=A19
  と値が入ればいいわけですが、
  H2:=A(F1の値)
  H3:=A(F18の値)
  とするための関数がわかりません。
  説明が下手で申し訳ありませんが…

    補足日時：2019/06/25 19:34

• No.8のご回答につきまして。
  
  実行しますと、エラーが出るのですが、
  H列にはH2から
  
  がいこく
  くりこし
  しっぱい
  いじ
  じゅうみん
  みれん
  れいぼう
  うんめい
  いふく
  くじょう
  うれのこり
  りょうほう
  うわき
  きにいらない
  いーめーる
  
  と表示されています。
  
  これは「る」で終わるとそのあとが繋がらなくて終わったのかなと、素人推察しています。
  ただ、なぜ始まりが「がいこく」なのでしょうか?
  
  もう一度実行してみたら、
  
  いんすたんと
  とう
  うたがう
  うれのこり
  りかい
  いーめーる
  
  になりました。
  
  ちなみに現在単語は736個入れています。

  
    補足日時：2019/06/27 22:42

• いま、3回実行してみました。
  
  (1回目)
  (H1:表示なし)
  いんすたんと
  とう
  うたがう
  うれのこり
  りかい
  いーめーる
  
  (H1:表示なし)
  (「いーめーる」を消去し2回目)
  かいごふくしし
  しゃぶしゃぶ
  ぶか
  かろりー
  りすとら
  らんぼう
  うれのこり
  りよう
  うんえい
  いれちがい
  いやりんぐ
  ぐたいてき
  きほんきゅう
  うまれつき
  きゅうしょく
  くさる
  
  (「くさる」を消去し3回目)
  (H1:表示なし)
  わりかん
  かべ
  べてらん
  らくご
  ごうけい
  いらい
  いんふるえんざ
  ざんぎょう
  うりきれ
  れいぎ
  ぎかい
  いきる
  
  ------------------------
  700ちょっとの単語の中で、「る」で終わる単語が57個もありました。
  ・したがって「る」で終わるものはその前の文字で続けること
  ・最初の単語を指定できる
  
  の2点ができると結構違うかもしれません。

    補足日時：2019/06/27 23:01

• ①冒頭を下記にしました。
  --------------------------------------------------------------------------------
  With Sheet1
  strB = .Cells(1, 8).Value
  .Range("H:H").ClearContents
  .Cells(1, 8).Value = strB
  Application.ScreenUpdating = False
  Randomize
  '■初期処理■
  --------------------------------------------------------------------------------
  同じく②

  No.11の回答に寄せられた補足コメントです。 補足日時：2019/06/29 16:26

• --------------------------------------------------------------------------------
  '■書出処理■
  intO = 1
  Do
  If intO = 1 Then
  If .Cells(1, 8).Value = "" Then
  '先頭語句をランダム選出
  intM = Int(Rnd() * intR)
  If intM < 1 Then intM = 2
  strB = .Cells(intM, 2).Value
  Else

    補足日時：2019/06/29 16:27

• strB = Application.GetPhonetic(.Cells(1, 8).Value)
  End If
  '管理テーブル検索
  --------------------------------------------------------------------------------
  同じく③
  '管理テーブル検索
  For intL = 1 To intS
  IfIf Right(strB, 1) = "ル" Or _
  Right(strB, 1) = "ン" Or _
  Right(strB, 1) = "ー" Then

    補足日時：2019/06/29 16:29

• strL = Left(Right(strB, 2), 1)
  Else
  strL = Right(strB, 1)
  End If
  Next intL
  'データテーブル検索
  --------------------------------------------------------------------------------
  
  実行すると「インデックスが有効範囲にありません」
  デバックを押すと
  intD = Int(Rnd() * tblS(3, intL) + 1)
  が黄色くなります。
  
  そもそもコピーがうまくできているかが自信ないのですが、間違っておりませんでしょうか。

    補足日時：2019/06/29 16:34

• こちら、ソースを全部コピペってできないんですね…
  使い方下手ですみません。

    補足日時：2019/06/29 16:35

• すみません、お返事ないな～と思っていましたら、わたしの捕捉が付いていませんでした!!!
  大変失礼いたしました。
  
  実行すると「インデックスが有効範囲にありません。」と表示されます。
  ただ結果は出てくるのでそういった意味では問題ないです。
  
  問題があるとすれば、同じ語がかなり頻繁に選択されるみたいです。
  750語くらい入れているので、ひとつの文字について10個くらいはあるみたいですが、
  これは仕方がないでしょうか。
  いまは選ばれたものは手動で削除しています。
  
  この点が仕様ということであれば、これで大丈夫です。
  かなり満足しています<(_ _)><(_ _)><(_ _)>

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

No.3 回答者： rukaandkaito 回答日時： 2019/06/24 14:01

No.2です

先ほどお答えした関数少し不備がありますね(-_-;)
E1:=INDEX($B:$B,MATCH($D1,$C:$C,0)+IF(COUNTIF($C:$C,$D1)=0,1,INT(RAND()*COUNTIF($C:$C,$D1)+1))-1)
これを
E1:=INDEX($B:$B,MATCH($D1,$C:$C,0)+IF(COUNTIF($C:$C,$D1)=1,1,INT(RAND()*COUNTIF($C:$C,$D1)+1))-1)
にしてください。
ただし、参照元のデータが変更されていくため今回の関数では１００％の回答が得られません。ただこれ以上の方法が思いつかないのも事実ですが・・・・・

ちなみに表を挿入タブのテーブルに変換しておくと追加削除などの作業が楽になりますよ♪

No.2 回答者： rukaandkaito 回答日時： 2019/06/24 13:31

まずは定義を明確にしましょう

No.1の方も仰られているように、半音や濁音等の場合にどう動くかを明確にしなければ関数を組めません。
画像でいただいた情報を元にサンプルで関数を組みましたので参考までにお試しください

前提条件：
①Ｄ列の条件として、末尾が『ン』および『－』の場合には後ろから２番目の文字とする
②全ての末尾の文字から始まる語句が必ず存在する
③濁音で終了した場合には濁音で開始するもののみを有効とする
④半音で終わるものには対応していない

B1:=PHONETIC($A1)
C1:=LEFT($B1,1)
D1:=IF(RIGHT($B1,1)="ン",MID($B1,LEN($B1)-1,1),IF(RIGHT($B1,1)="ー",MID($B1,LEN($B1)-1,1),RIGHT($B1,1)))
E1:=INDEX($B:$B,MATCH($D1,$C:$C,0)+IF(COUNTIF($C:$C,$D1)=0,1,INT(RAND()*COUNTIF($C:$C,$D1)+1))-1)

あとはデータ終了までフィルでコピーしてください
Ｅ列に次の語句がランダムで表示されますので、順に追いかけていけば良いです。ちなみに次の語句が何行目のデータか知りたければＦ列等にＥ列の関数内の後ろの部分にある『MATCH($D1,$C:$C,0)+IF(COUNTIF($C:$C,$D1)=0,1,INT(RAND()*COUNTIF($C:$C,$D1)+1))-1』を記載し、Ｅ列にはＦ列を参照するようにすれば対応出来ます。

尚、ランダム関数を使用していますので、シート内でなんらかの変更があるたびに再計算が行われ表示データが変わりますのでお気を付けください(笑)

No.1 回答者： yokomaya 回答日時： 2019/06/22 19:32

RIGHT(,1),LEFT(,1)が判っておられるのですよね。

でしたら自動ではありませんが、その文字がそれぞれに何個あるかを
COUNTIFで数えれば繋げられる可能性について判断できます。
つまり、片方が０の言葉は使えません。
正確には最初と最後なら使えますが。

また開始文字の個数と終了文字の個数の差が大きい場合小さい方の組み合わせしか
使えない事になります。

この数字を見ながら語彙を準備するのが妥当ではないかと。

＞そのあたりはあいまいでも差支えないです。
貴方が差し支えないと考えるのは全く無意味、
むしろ曖昧は混乱を招くだけ。
曖昧はコンピューターにとって困難な処理ということは
記憶に留めてほしいです。

長音と「ん」が最後にくる場合は前の文字を用いると
明確にするか、諦めるかの２択です。

あと、拗音（「きゃ」　とか　「くぁ」　とか）を
2文字を一文字扱いで取り扱うか等の定義も必要かと。

そんな言葉は取り上げないというのが最も簡単な解ですけどね。


実際の作業でしりとりにする為の補助的な条件書式を準備すれば
手動でも出来るんじゃないかな、１００個でしょ。

使用済みの文字列を白化とか、最後に選んだ文字列列に繋がる文字を
着色するとかすれば、プログラム考えてる時間より早くに終わるかと。

この回答へのお礼

ありがとうございます。
実際に使うときは800個になるんですが(笑)

>貴方が差し支えないと考えるのは全く無意味、
>むしろ曖昧は混乱を招くだけ。
勉強になります<(_ _)>

お礼日時：2019/06/22 20:07