関数で出来ますか～？ 第310回 孫と思いがけない休み遊び

【問】

孫たちと
2文字の生き物あてゲーム　

黄色のセルの
4文字で

緑の2つのセルに
2文字の生き物を
表示させてほしい

49のリスト(F列)
から2つ選んでね

勿論G列以降（右）
は使ってはいけない

・・・将来孫たちは
3,4と生き物の数を
増やそうとするかも・・・
恐ろしい・・・

（ジョー３さん）

解答用参考ファイル： 20200316_3をダウンロード

***********************************************************************************

４名の方の解答作成完了宣言の後に、このスレに直接解答式をお書きください。

この問題に関しては解答用の別スレは作成いたしません。

*******************************************************************************

 

★解答リスト

1.　y sakudaさん

=INDEX(IF(D3=0,MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1)),MATCH(,MATCH(MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),G$2:G$50,)*MATCH(MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1),G$2:G$50,)/(MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1)＜＞D3),-1))

2.　からくちさん

=LOOKUP(1,0/FIND(G$2:G$50,SUBSTITUTE(B$3&LEFT(B$3)&MID(B$3,3,1)&LEFT(B$3)&RIGHT(B$3)&MID(B$3,2,1)&RIGHT(B$3)&MID(B$3,3,1)&MID(B$3,2,1)&LEFT(B$3)&RIGHT(B$3)&LEFT(B$3),D3,)),G$2:G$50)

3. y sakudaさん（２つ目）

=OFFSET(G$1,MOD(MAX((ROW($1:$49)/1%+COLUMN($A:$AW))*(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B$3,LEFT(G$2:G$50),,1),RIGHT(G$2:G$50),,1),LEFT(TRANSPOSE(G$2:G$50)),,1),RIGHT(TRANSPOSE(G$2:G$50)),,1))=0)*(ROW($1:$49)<>COLUMN($A:$AW)))/100^(D3=0),100),)

4.　niさん

D4(178)　{=OFFSET(G1,MAX(IFNA(MATCH(MID(B3,{1,2,1,3,1,4},1)&MID(B3,{2,1,3,1,4,1},1),G2:G50,)+MATCH(MID(B3,{3,3,2,2,2,2;4,4,4,4,3,3},1)&MID(B3,{4,4,4,4,3,3;3,3,2,2,2,2},1),G2:G50,)*0,)),)}
D5(180) {=OFFSET(G1,MAX(IFNA(MATCH(MID(SUBSTITUTE(SUBSTITUTE(B3,RIGHT(D4,1),,1),LEFT(D4,1),,1),{1,2},1)&MID(SUBSTITUTE(SUBSTITUTE(B3,RIGHT(D4,1),,1),LEFT(D4,1),,1),{2,1},1),G2:G50,),0)),)}

5.　ジョー３さん（出題者解答例/１匹だけ）

=MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4-3,1),1)&MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4-2,1),1)

6. y sakudaさん（3つ目/１番目改）

=INDEX(IF(D3=0,MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1)),MATCH(,MATCH(MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),G$2:G$50,)*MATCH(MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1),IF(G$2:G$50=D3,,G$2:G$50),),-1))

7.　ジョー３さん（出題者解答例/１匹だけ）

{=INDEX(R2:R50,MAX(IFERROR(MATCH(MID(B3,{1,1,2,2,3,3},1)&MID(B3,{2,3,1,3,1,2},1),R2:R50,0),0)))}

Comments

[投稿情報: y sakuda]

変なとこに感心するようですが、からくちさんの、重複を避ける手法が秀逸ｗ
式の構成見ながら、どうやって、D4と同じものが出ないようにするのか首ひねってましたｗｗｗ

[投稿情報: y sakuda]

私の式途中が消えてる・・・・再チャレンジ
=OFFSET(G$1,MOD(MAX((ROW($1:$49)/1%+COLUMN($A:$AW))*(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B$3,LEFT(G$2:G$50),,1),RIGHT(G$2:G$50),,1),LEFT(TRANSPOSE(G$2:G$50)),,1),RIGHT(TRANSPOSE(G$2:G$50)),,1))=0)*(ROW($1:$49)＜＞COLUMN($A:$AW)))/100^(D3=0),100),)

[投稿情報: y sakuda]

再度やりましたが、途中消えちゃいますね。投稿直後は見えてるんですが・・・
クマプーさんに送ります。

[投稿情報: くまぷー]

>クマプーさんに送ります。

了解です。
アップしました。

[投稿情報: からくち]

わたしのは不完全版です
端からその様に考えると非常に楽ですね
と言っても200弱もあるけど

そこで考えたのが、、、
エクセル的に言うとcombin(4,2)=6なのでと思った時
そうかくまぷーさんの問題、、、回文だ、、、と
1234321これで全種出来る、、、
勿論ですが被りもあるで、、、
これを何も思わず出来るのが不完全版の良さ
なんて勝手に思って、、、書きました

[投稿情報: y sakuda]

私の長いやつは、組み合わせは　4C2 の6通り、ただ、その2枚が入れ替えられるから×4の24通り。
でも。1番目と二番目が入れ替わっても回答的には問題ないから12通りやれば全部できる。
⇒それなら総当たりやっちゃえ　だったのですが、いいかか　が出現した文字の順番によっては　いかいか　になっちゃう。
ここだけがどうにもならず、それ避けたら100以上長くなりました。
200台の方は重複避ける手段は比較的短くできましたが・・・・
この問題、いか、かい　が入ったことでとんでもなく面倒になってる。
半面、「二文字の生き物名がならんでます」　だけで、例示なしの仕様でプログラム組んだら、いかにも見落としてバグだしそうなところで、かんがえさせられちゃいますねーー

[投稿情報: からくち]

sakudaさんのOFFSET
1番最初に頭に浮かんだ中の１つです
ただ以前から言っていますが嫌いな関数故使いません
と言うか使えませんので、、、

それにしても、、、１の式は凄い
わたしだと書き間違い犯しそう

[投稿情報: からくち]

くまぷーさん、、、
次回の問題が若しわたしのなら
易し過ぎなので直ぐに出題して
21日の土曜日解答日でも余りある日程です
他の方の問題なら別ですが

[投稿情報: y sakuda]

>わたしだと書き間違い犯しそう
やりましたｗ
コピー＆ペーストでやって、｛｝の中をなおしたんですが、思わぬところでエラーになる。
見直したら、後ろの方で数字が逆転してたｗｗｗ

[投稿情報: ｎｉ]

回答が出てる＾＾
では
D4(178)　{=OFFSET(G1,MAX(IFNA(MATCH(MID(B3,{1,2,1,3,1,4},1)&MID(B3,{2,1,3,1,4,1},1),G2:G50,)+MATCH(MID(B3,{3,3,2,2,2,2;4,4,4,4,3,3},1)&MID(B3,{4,4,4,4,3,3;3,3,2,2,2,2},1),G2:G50,)*0,)),)}
D5(180) {=OFFSET(G1,MAX(IFNA(MATCH(MID(SUBSTITUTE(SUBSTITUTE(B3,RIGHT(D4,1),,1),LEFT(D4,1),,1),{1,2},1)&MID(SUBSTITUTE(SUBSTITUTE(B3,RIGHT(D4,1),,1),LEFT(D4,1),,1),{2,1},1),G2:G50,),0)),)}

[投稿情報: くまぷー]

niさんの解答を「解答リスト」に加えました。

[投稿情報: くまぷー]

>くまぷーさん、、、
>次回の問題が若しわたしのなら
>易し過ぎなので直ぐに出題して
>21日の土曜日解答日でも余りある日程です

第３１１回の出題は来週の火曜日予定です。
もう少々、お待ちください。
今週はジョー３さんの週という事で・・・m(__)m

[投稿情報: jyo3]

>もう少々、お待ちください。

わたしも、新問題・・・ひとつ、、、
作ったかも、、、

でもなーーー

私のは、、、次回も、、、３っつで一つにしよう・・・・

今回も、、、全部、、、まけ、or引き分け・・・

完璧に勝ちたいな―――

[投稿情報: くまぷー]

ところでジョー３さんの解答発表がまだのようですが・・・
ジョー３さんの発表が無いと４人にならないので
sakudaさんがフライングになってしまいます(^^ゞ

[投稿情報: jyo3]

>ところでジョー３さんの解答発表がまだのようですが・・・
ん、、、、間に合わせで、、、１時間ぐらいかなーーー

試してもいない・・・

いかいか
かいかい
いかかい
かいいか

・・・・・・・・・・・・・・そのへんで、、、完璧と、、、

おもってましたが、、、

・・・・・・・・・・・・・・
送りますね、

さいしょのやつ、、、

[投稿情報: くまぷー]

＞送りますね、

送る必要はありません。

＞４名の方の解答作成完了宣言の後に、このスレに直接解答式をお書きください。

[投稿情報: ｎｉ]

からくちさんの式、最後のRIGHT(B$3)&LEFT(B$3)の&LEFT(B$3)は余分ですね。
4文字目、1文字目の並びは最初のB$3&LEFT(B$3)で既に作られています。
にしても、すごい式だなぁ

[投稿情報: jyo3]

書いたのが、、、

だいじょうぶかなーーー

やってみますね、、、送ったけど、、、

=MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4-3,1),1)&MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4-2,1),1)

[投稿情報: jyo3]

いまみたら、、、かいどくできませーーーーん、、、

[投稿情報: くまぷー]

＞送ったけど、、、

有り難うございます。
リストに追加しました。

[投稿情報: jyo3]

みなさん、、、すごいです、、、わたし、、、りはびり、、、中。。。。

[投稿情報: jyo3]

完璧と自信満々が、、、、

いかかい
かいいか

良かったと思うが

かかいい
いいかか、、、

なんでだめなのーーー

さようならーーーー、、、

[投稿情報: ｎｉ]

F9押していろいろな言葉が出てくるのがなんだかおもしろくなって、
お遊び、4つの文字でできる動物をすべて表示
{=T(OFFSET(G$1,LARGE(IFNA(MATCH(MID(B$3,{1,2,3,4},1)&MID(B$3,{2,1,1,1;3,3,2,2;4,4,4,3},1),G$2:G$50,),),ROW(A1)),))}
でも、同じ文字が2つあると、同じ動物が2回出てきてしまう。

[投稿情報: くまぷー]

＞さようならーーーー、、、

戻って来てくれ～
提示された数式はD4（一匹目）だけであってD5（２匹目）の分が無いような・・・

[投稿情報: y sakuda]

niさんの、いかかい　などで、かい、かい　となっちゃいますね。
ジョー３のは、コピーできないタイプの式ですが、二番目は別式があるんでしょうか？

[投稿情報: ｎｉ]

＞niさんの、いかかい　などで、かい、かい　となっちゃいますね。
はい、リストの下に「かい」がある限り、「いか」は出てこないです。
これが私の限界です～

[投稿情報: jyo3]

もうのんだからだめ、、、数字を、、
２こかえたような・・・

さようなら、、、

またつくるから、、、
yurusitene,,,

[投稿情報: jyo3]

{=INDEX(R2:R50,MAX(IFERROR(MATCH(MID(B3,{1,1,2,2,3,3},1)&MID(B3,{2,3,1,3,1,2},1),R2:R50,0),0)))}

こんなのも、、、でてきたり・・・

[投稿情報: y sakuda]

今更ですが、437の奴、かい　いか　を避けるもっと気の利いた手見つけて374でできました。

=INDEX(IF(D3=0,MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1)),MATCH(,MATCH(MID(B$3,{1,1,1,1,1,1,2,2,2,2,3,3},1)&MID(B$3,{2,2,3,3,4,4,1,1,3,4,1,2},1),G$2:G$50,)*MATCH(MID(B$3,{3,4,2,4,2,3,3,4,4,3,4,4},1)&MID(B$3,{4,3,4,2,3,2,4,3,1,1,2,1},1),IF(G$2:G$50=D3,,G$2:G$50),),-1))

[投稿情報: y sakuda]

書き忘れました。回避策は簡単で、二番目を確認するMatchのテーブルを
IF(G$2:G$50=D3,,G$2:G$50)
として、一番目の解答（D4）を消すだけ。　提示の式はD4の式ですので、D3となってますが、D5にコピーした時はここが一番目の答えになり、テーブルからはずれます。

[投稿情報: くまぷー]

お早うございます。

ジョー３さん、sakudaさんが新しく投稿された分をリストにつかしました。

maronさんの姿が全然見えないけど、どうしたんだろう・・・

[投稿情報: maron]

◆ご心配いただいて、すみません。
◆maronはどの問題もできずに、完敗です。（とほほ・・・）

[投稿情報: jyo3]

上の式のと、、、ほとんど同じ
下の式
まあ、一つにできるが・・・

=MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4-1,1),1)&MID(B3,MID("123412432134214313241342312431421423143241234132",MATCH(2,COUNTIF(G2:G50,MID(B3,{1,1,2,2,1,1,3,3,1,1,4,4},1)&MID(B3,{2,2,1,1,3,3,1,1,4,4,1,1},1))+COUNTIF(G2:G50,MID(B3,{3,4,3,4,2,4,2,4,2,3,2,3},1)&MID(B3,{4,3,4,3,4,2,4,2,3,2,3,2},1)),0)*4,1),1)

[投稿情報: jyo3]

みなさん、
お付き合いいただき
感謝致します

からくちさん、sakudaさん、niさん
２×３単語でも・・・出来そうな、、、気がしてきます

[投稿情報: からくち]

＞２×３単語でも
B3 の文字数だけ一定なら、、、
単語数・単語文字数は決まって無くても
出来ると思います
ただしリスト列の作り方の方が難しくなるのでは

[投稿情報: y sakuda]

難癖つけるのが趣味ってわけじゃないですが、チェック用プログラム作ったんでｗ
ジョー３のペアも、
いいかか、かいかい、いかいか
で同じものがでちゃいますね。

ここが、問題の素晴らしい所とも言えます。システム設計やテストをする場合、分かってみれば当然見たいな所に誰も気が付かず、大トラブルになるって良くあります。
トラブル起こると「なんでちゃんとテストしていないんだ」といろんなところから非難されますが、テストケースどんなに良く考えてもこういうのを誰もおもいつかず、すっぽ抜けるってのが実情です。

[投稿情報: y sakuda]

この問題、性格上長くなるのは初めから分かってました。
更に、単純な発想で力仕事やるだけだと思ってましたが、結構工夫の余地がありますし、からくちさんの回文みたいな発想も出てきて、長大な回答しかない問題にもかかわらず結構おもしろかった^^
昨夜と言うか今朝投稿した改善案なんて、ボヤっとしながら、なんぼなんでも一か所訂正するのにこんなバカな方法じゃ情けないなと眺めてたら閃きました。

[投稿情報: jyo3]

>テストケースどんなに良く考えてもこういうのを誰もおもいつかず、すっぽ抜けるってのが実情です。
ありがとうございます。

>からくちさんの回文みたいな発想も出てきて、長大な回答しかない問題にもかかわらず結構おもしろかった^^

わたしも、、裏から・・・やりかけたけど、、からくちさんのような、技術がない。

[投稿情報: ｎｉ]

＞２×３単語でも
からくちさんの式を使って3単語バージョンを作成してみました。
=LOOKUP(1,0/FIND(G$2:G$50,SUBSTITUTE(SUBSTITUTE(B$3&MID(B$3,2,1)&MID(B$3,4,1)&MID(B$3,1,1)&MID(B$3,3,1)&MID(B$3,2,1)&MID(B$3,5,1)&MID(B$3,1,1)&MID(B$3,6,1)&MID(B$3,5,1)&MID(B$3,3,1)&MID(B$3,6,1)&MID(B$3,1,1)&MID(B$3,4,1)&MID(B$3,2,1)&MID(B$3,6,1)&MID(B$3,3,1)&MID(B$3,5,1)&MID(B$3,4,1)&MID(B$3,6,1)&MID(B$3,4,1)&MID(B$3,3,1)&MID(B$3,1,1)&MID(B$3,5,1)&MID(B$3,2,1)&MID(B$3,1,1),D3,),D2,)),G$2:G$50)

時々2つ目と3つ目が同じ解を返してしまいます。（＞＜）

回文文字列準の一例　1234562413251653614263546431521

[投稿情報: jyo3]

>からくちさんの式を使って3単語バージョンを作成してみました。
わかわかしい・・・・・

[投稿情報: くまぷー]

今回のジョー３さん、３連戦も一段落したと思いますので
もしも、ジョー３さんのお許しが出れば、
３連休でもありますし、予定を早めて第３１１回を公開したいと思いますが・・・

ジョー３さん、如何でしょうか？

[投稿情報: jyo3]

>もしも、ジョー３さんのお許しが出れば、
賛成です。

許しなんて、、要りませんですし、、、

[投稿情報: くまぷー]

＞許しなんて、、要りませんですし、、、

有り難うございます。
それではお言葉に甘えて・・・