第１３３回 最大連続数 解答例

名案も無いまま、この問題を出題してしまったのですが
とりあえず苦し紛れに用意しておいた解答例は
 
５３：{=COUNT(FIND(REPT(1,B2:M2),SUM((B3:M3>0)*10^B2:M2)))}
 
0より大きい数字は1、そうで無い場合は0として、左から10の1乗・2乗・3乗・・・12乗
を乗算し合計した数値に存在する1の束を調べるべく、REPTで1を1～12文字並べた
配列を検索値としてFINDでチェックさせ、有効だった個数をCOUNTで数える。
という方法でした。
一応この問題の範囲内ならこの解答でもいいのですが、もっと広い範囲ではエクセルで
扱える桁の限界で対応できないので不満を抱えていたので、「いい解答とはいえない」と
書いた次第です＾＾；
 
からくちさんのリクエストで５３を別の関数に置き換えた式
６９：{=MATCH(,-SEARCH(LEFT(111111111111,B2:M2),NPV(,(B3:M3>0)*10^B2:M2)))}
 
くまぷーさんには今回ほぼ2週間の締め切り期間をいただき、その間 からくちさん、y sakudaさん、minさん
から「MAXで出来る」と教えていただき、「＞0」ではなく、「＜1」を調べればいいことに気づけ、
ようやく満足のいく式にたどり着くことができました。　ほんとに皆様のおかげですm(__)m
 
最初に考えたMAXを使う式
６７：=MAX(MMULT(LARGE((A3:M3<1)*A2:M2+{0;13},ROW(1:13)+{0,1}),{1;-1})-1)
 
この式はかなり気に入ってましたが・・・
５１：=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M3<1)),2:2)-1)
・・・どうやら2度手間をしていたんですね、、、

--
（む印さん）

Comments

[投稿情報: からくち]

プレビューを見る

式を書くとコメントごと消えてしまっているのは何故

[投稿情報: くまぷー]

>式を書くとコメントごと消えてしまっているのは何故

試しにむ印さんの式をコピペすると・・・

＞５１：=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M3<1)),2:2)-1)

[投稿情報: くまぷー]

からくちさん、こちらは問題なく出来るようです。

特に私の方で設定を変えたわけではありません。

[投稿情報: からくち]

＞取り敢えず何も考えず書いてみました、、、６３
{=COUNT(FIND(REPT(1,ROW(1:12)),SUM((B3:M3>0)*10^COLUMN(A:L))))}

＞５３、、、文字数だけは同じ式を作ってみました
=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M30)*10^B2:M2)))}

やはり同じ式、、、でしたね

[投稿情報: からくち]

途中、、、消えてしまった部分、、、です

ROW()・COLUMN()をA2:M2にしてもう１つ５３
{=COUNT(FIND(REPT(1,B2:M2),SUM((B3:M3>0)*10^B2:M2)))}

やはり同じ式、、、でしたね

[投稿情報: からくち]

＞=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M30)*10^B2:M2)))}
=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M3<1)),A2:M2))-1

最後の-1が消えている

[投稿情報: からくち]

よく見ると、、、
＞=MAX(FREQUENCY(MATCH(A2:M2,A2:M2/(A3:M30)*10^B2:M2)))}
最後に ｝など書いていないのに、、、何故

[投稿情報: min]

maxを使わないものは、
55{=MATCH(,-FIND(REPT(1,B2:M2),SUM((B3:M3>0)*10^A2:L2)))}
までしかやりませんでした。

[投稿情報: min]

REPTを外して
56{=MATCH(,-FIND(10^B2:M2,SUM((B3:M3<1)*10^A2:L2)+10^12))}
１文字増えてしまいました。

[投稿情報: min]

長いけど、、
72{=12-MIN(LEN(SUBSTITUTE(SUM(((B3:M3>0)+1)*10^A2:L2),REPT(2,B2:M2),,1)))}

[投稿情報: む印]

遅くなってしまいました、、、

今日は職場で慣れない重労働を強いられ、
帰って食後に いつの間にやら眠ってしまってました、、

　
う～ん、やはり、、凄いですねー
からくちさんもminさんも短時間にこの解答を思いついておられたとは、、
お二人とも、2週間は長かったのでは^^

[投稿情報: む印]

minさんの４２は多分これであってるかな？

＞どうやら2度手間をしていた・・・
５１のMATCHを外してもいけることに気づいて４２

=MAX(FREQUENCY(A2:M2+1,(B3:M3<1)*B2:M2)-1)

[投稿情報: min]

42は同じなんですが、
42=MAX(FREQUENCY(A2:M2+1,(A3:M3<1)*A2:M2))-1
でやっていました。

42=MAX(FREQUENCY(-A2:M2,(A3:M3<1)*-A2:M2))-1
でも同じようなものですが。

[投稿情報: からくち]

＞2週間は長かったのでは
時間がある時は、勝手に問題を変更して遊ばせて戴いてます
今回の問題で１番長い式になったのは、、、
連続の最後のセル番地を出す式、、、
問題例なら「 D3 」となる式、、、81

[投稿情報: min]

＞2週間は長かったのでは^^
週末くらいしか、ｐｃを立ち上げる気力がなかったのでちょうど良かったです。。

[投稿情報: y sakuda]

おそくなりました。
すごいですねーー
私は2行目のヘッダ使うって発想がでてこなかったので、Columnを使った63どまりでした。

[投稿情報: y sakuda]

42はすごすぎるな。（良く分かってないけど）

[投稿情報: む印]

＞勝手に問題を変更して遊・・・
＞連続の最後のセル番地を出す式、、、
なるほど！そういうのも面白いｄ

＞Columnを使った63
そうでしたか、、この問題の元ネタでは
2行目のヘッダは「1月～12月」と文字列でしたが、
使ってもらうのを意識して数値にしました^^

あと、ジョー３さんのアイデアや、
ｎｉさんの、２７１文字の大作を是非拝見したいですねｄｄ

[投稿情報: む印]

＞なるほど！そういうのも面白いｄ
minさんもまた出題をおねがいします^^

[投稿情報: ｎｉ]

もう、今回全然ダメで
{=MAX(MATCH(FALSE,B3:$M3>0,),MATCH(FALSE,C3:$M3>0,),MATCH(FALSE,D3:$M3>0,),MATCH(FALSE,E3:$M3>0,),MATCH(FALSE,F3:$M3>0,),MATCH(FALSE,G3:$M3>0,),MATCH(FALSE,H3:$M3>0,),MATCH(FALSE,I3:$M3>0,),MATCH(FALSE,J3:$M3>0,),MATCH(FALSE,K3:$M3>0,),MATCH(FALSE,L3:$M3>0,),--(M3>0))-1}

＄もいらないんだけど、補助セル使った名残。
L3とM3が正だと　＃N/A、　どうしようもない・・・

[投稿情報: min]

＞REPTを外して
REPTの外し方に無駄があったので、
む印さんの53を例にREPTを外して
52{=COUNT(FIND(10^B2:M2-1,SUM((B3:M3>0)*10^B2:M2*9)))}
一文字減りました。

今回、手っ取り早いのはFREQUENCYしかないだろうなと思っていました。
頭の中で考えていたときFREQUENCYの第二引数（区間配列）は昇順の配列になっていないとダメだよなあ、でも、もしかしたら昇順じゃなくても出来るんじゃないかな、、やってみる価値はあるか、、
と思っていました。
実際にＰＣでやってみたら昇順じゃなくても出来ましたので短くまとめることが出来ました。
これがわかっただけでも収穫です。

＞minさんもまた出題をおねがいします^^
みなさんみたいに問題作りのセンスが全くないので良い問題は全く思いつきません。。

[投稿情報: min]

＞みなさんみたいに問題作りのセンスが全くないので
言葉が変でした。。
みなさんと違って問題作りのセンスが全くないので良い問題は思いつきません。。

[投稿情報: む印]

ｎｉさんの271もＭＡＸだったんですねー
＞L3とM3が正だと　＃N/A
なっとくです、、

　
＞52{=COUNT(FIND(10^B2:M2-1,SUM((B3:M3>0)*10^B2:M2*9)))}
なるほど！9の束をチェックとは、、、
そこまで考えが及びません、、、、

＞問題作りのセンスが全くないので
そんなことはないでしょう
爆弾やバレーボールのスコア表など、とても印象に残る
いい問題でしたよねー（難しかったけど^^;）

ところで、からくちさんの
＞連続の最後のセル番地を出す式
細かなルールがわからないのですが
私も暇なとき自習してみようと思います^^

[投稿情報: からくち]

＞細かなルールがわからないのですが
細かな、、、と言う程の事では無いので

下記のような場合、む印さんの問題では
E3～H3が最大の連続なので連続数４が答え
わたしのお遊びは
その最大連続セル範囲 E3：H3 の最終セル H3が答え

A___B___C___D___E___F___G___H___I___J___K___L___M___N
____0___2___0___2___5___2___3___0___3___2___0___0___H3

最大連続数の答えをそのまま使っても良し、、、けど長くなる
全く新たにでは、、、易しくヒントにはなってない
やはり、、、それなりに、、、か

む印さんの問題シートそのままですし
くまぷーさんに態々ご足労をお掛けするのも・・・
と言う事で、このまま今週の問題にしても、、、です

短い式は苦手ですので、、、８０前後位

[投稿情報: む印]

＞その最大連続セル範囲 E3：H3 の最終セル H3が答え
了解です。

あと
＞細かなルール
1）連続してない場合は？
2）0より大きい数値がない場合は？
3）最大連続が複数ある場合は？

[投稿情報: からくち]

＞1）連続してない場合は？
最初（B3に近い）の０以上のセル番地

＞2）0より大きい数値がない場合は？
セル番地を表示出来ませんのでエラーでも

＞3）最大連続が複数ある場合は？
B3に近いセル番地

で、、、どうでしょうか

[投稿情報: む印]

＞＞3）最大連続が複数ある場合は？
＞B3に近いセル番地
B3に近い連続の最終セルということで理解していいでしょうか？

[投稿情報: からくち]

＞B3に近い連続の最終セル
そうですね、、、

A___B___C___D___E___F___G___H___I___J___K___L___M___N
____0___2___0___2___5___0___3___2___0___2___5___0___F3

この場合、E3:F3・H3:I3・K3:L3の３ヶ所に連続セル範囲がありますが
B3に１番近いE3:F3が対象となり、その最終セルと言う事で
答え：F3

＞理解していいでしょうか
はい

[投稿情報: からくち]

＞３ヶ所に連続セル範囲が
３ヶ所に最大連続セル範囲が
に訂正

[投稿情報: む印]

からくちさんありがとうございます。

とりあえず出来たのは84でした、、
また合間をみていい手を探したいと思います^^

[投稿情報: む印]

ちょっとだけ再開

84の短縮を試みて80、、

[投稿情報: からくち]

＞84の短縮を試みて80、、
う～～～ん、、、微妙に違うのかな、、、
わたしの式は81
因みに
=C(M(F(,T(S(()*()),R(,)),)))
こんな式です、、、関数６種６個

同じ＜＞似てる＜＞違う
どれかな

[投稿情報: む印]

>同じ＜＞似てる＜＞違う
既存の方法を並べただけの84から
よくある短縮を図っただけですの
=A(,M(M(M(,/())),M(,/())))
で関数3種5個です、、

＞=C(M(F(,T(S(()*()),R(,)),)))
こういうのを思いつきたいんですがアイデアが浮かびません、、
　
　
やってるうちに最大連続数42の別ものができました、、
｛=MAX(A2:M2-MATCH(A2:M2,2:2/(A3:M3<1))+1)｝

[投稿情報: む印]

最大連続数の別物42をヒントに
連続の最後74が出来ました！

[投稿情報: む印]

最初関数、AをCに替えて74→71

[投稿情報: min]

何となく頭にあったやつをやってみました。
とりあえずやったものは78でした。
無駄がありそうです。

[投稿情報: min]

＞問題例なら「 D3 」となる式
あら、問題を勘違いしました。
もう少し長くなりそうです。

[投稿情報: min]

ざっと補正して８８でした。

[投稿情報: からくち]

＞AをCに替えて74→71
A－C＝２と思うのですが、、、
何かが、、、違うのか・・・

どちらにせよ７０強とは、、、

＞補正して８８でした
これまた違うのが出て来た
見るのが楽しみ

[投稿情報: む印]

71を70に出来ましたが、Cを使ったこのやり方だと
2）０より大きな数値がない場合
にエラーにもならずセル番地ともいえない文字列になってしまいます、、

[投稿情報: からくち]

＞０より大きな数値がない場合
＞にエラーにもならずセル番地ともいえない文字列に
わたしの８１もＣですが、上記の場合はエラーです
その場合Ｍがエラーになってしまいますので

[投稿情報: む印]

エラー表示に対応させるため
CをAに戻して73、、、

ところで、
いつものように明日の20時以降に答え合わせでしょうか？

[投稿情報: からくち]

＞いつものように
この問題は１３４回では無く
あくまで、む印さんの１３３回の延長戦ですので
む印さんのご都合で良いかと思いますが、、、

[投稿情報: くまぷー]

>む印さんのご都合で良いかと思いますが、、、

それでよろしいかと・・・

[投稿情報: む印]

私の都合に合わせていただける、、
ありがとうございます。m(_ _)m

今日は遠出をしておりますのでお言葉に甘えて
答え合わせは明日11日の20時以降でお願いします。

[投稿情報: min]

＞無駄がありそうです。

＞ざっと補正して８８でした。

最初の手法を変えずに無駄を省いてみました。
71
む印さんの71はLを使っているのかな？

[投稿情報: む印]

＞明日11日
スイマセン、間違ってました、、
11日×→12日○
　
　
私の71にはLで始まる関数は使ってないです。
この問題では考え方が色々あるみたいで楽しみですね！

[投稿情報: jyo-3]

みなさん、、、すごいですねーー

ようやく、いそがしさ、、、いちだんらく、、、もんだいつくるいよくが
わいてきた。

[投稿情報: む印]

いま帰ってきました！

＞もんだいつくるいよくがわいてきた。
まってました^^
　

では僭越ですがわたしの解答例から･･･

最初の80
{=ADDRESS(3,MATCH(MODE(MATCH(A2:M2,2:2/(A3:M3<1))),MATCH(A2:M2,2:2/(A3:M3<1))))}

0より大きな数値がない場合のERR表示対応
73
{=ADDRESS(3,13-MOD(MAX((A2:M2-MATCH(A2:M2,2:2/(A3:M3<1))&12)-A2:M2),50))}

0より大きな数値がないと「A3」や「'3」と表示してしまいます、、、
74
{=CHAR(78-DAY(MAX(1*(A2:M2-MATCH(A2:M2,2:2/(A3:M3<1))+2&-1&A2:M2-13))))&3}
71
{=CHAR(77-RIGHT(MAX((A2:M2-MATCH(A2:M2,2:2/(A3:M3<1))&12)-A2:M2),2))&3}
70
{=CHAR(77-MOD(MAX((A2:M2-MATCH(A2:M2,2:2/(A3:M3<1))&12)-A2:M2),50))&3}
　

[投稿情報: min]

出題者解答例が出ないようですが。

先に出してもいいのかなあ？

[投稿情報: min]

む印さんから解答が出たようですので、
とりあえずちょうど１００になったもの

{=CHAR(78-MIN(LEN(-RIGHT(SUBSTITUTE(SUM(IF(B3:M3＞0,9,2)*10^(12-B2:M2)),10^B2:M2-1,10^15,1),13))))&3}
0より大きな数値がないと「A3」となるのでちょっとルールに反するのかな？

[投稿情報: からくち]

わたしの８１です

=CHAR(MODE(FIND(0,TEXT(SUM((B3:M3>0)*10^(12-B2:M2)),REPT(0,12))&0,A2:M2+1))+64)&3

む印さんの式とはやはり全く違うようです

[投稿情報: min]

からくちさんの式もむ印さんの式ももう少しちゃんとみないと良くわからんですね。

０より大きな数値がないとエラーになるもの。
最初にやったもの
86
{=CHAR(64-LOOKUP(,-A2:L2-FIND(REPT(1,B2:M2),SUM((B3:M3>0)*10^(13-B2:M2))+10^13*2)))&3}
無駄を省いたもの
71
{=CHAR(65-LOOKUP(,-2:2-FIND(10^B2:M2,SUM((A3:M3<1)*10^(12-A2:M2)))))&3}
考え方は単純です。

[投稿情報: min]

ん？{}はいらないのかな？
69
=CHAR(65-LOOKUP(,-2:2-FIND(10^B2:M2,SUM((A3:M3<1)*10^(12-A2:M2)))))&3

[投稿情報: む印]

お二人ともSUM((B3:M3>0)*10^(12-B2:M2))
なるほど、、こっち方面は考えませんでした、、、

[投稿情報: む印]

＞-2:2
昔のｳﾞｧｰｼﾞｮﾝでは対応できなかったと思います、、

[投稿情報: min]

＞昔のｳﾞｧｰｼﾞｮﾝでは対応できなかったと思います、、
そうでしたっけ？？

それにしても、む印さんの解答は難しいですね。

からくちさんのMODE、、
む印さんも使っていますが、最頻値が複数あった場合は、さいしょの数値を返すのか。。
さすがですね。

[投稿情報: min]

FREQUENCYを使ってもできそうだなと思って
やってみました。
こんな感じ、
{=MOD(-MAX(FREQUENCY(A2:M2+1,(B3:M3＜1)*B2:M2)*20-ROW(1:13)),20)-1}

あれ、む印さんもMOD使っていたよなあと思って、
む印さんの
＞70
＞{=CHAR(77-MOD(MAX((A2:M2-MATCH(A2:M2,2:2/(A3:M3＜1))&12)-A2:M2),50))&3}
を見てみて、難しいことやっているなあ、、と感じ
短縮してみたくなって、、やってみました。
65
{=CHAR(65+MOD(MIN(MATCH(A2:M2,2:2/(A3:M3＜1))-A2:M2*95%),1)*20)&3}
５文字減らせました。　

演算誤差は難しい。。

[投稿情報: jyo-3]

この問題、、、すっきりの、、、解き方がありそうに思って、、、、
・・・・・・・・・・・・だが、、、、
駄目だった。・・・の・・・・わたし・・・・・

みなさんすごいなーーーー

＞演算誤差は難しい。。
うん、うん、、、でも、、、面白い。。。

さて、問題を作って、、、
春さんにやって貰った。

割とすぐ（何時間?かなーーー）解かれちゃった。
でも、字数は多いとのこと。
わたしは、、、、短手数だけ探って、、、、うまくいかない。
・・・・・・・・・・・・・・・・・・・なんか、、、自信なくする。

[投稿情報: む印]

おおっ、、
＞MOD(MIN(MATCH(A2:M2,2:2/(A3:M3<1))-A2:M2*95%),1)*20
なるほどー、こんなにすっきりするんですねっ、、、

＞さて、問題を作って、、、
出題をチェックするようにしま～す！

[投稿情報: くまぷー]

minさん、む印さん、お早うございます。

＞みなさんすごいなーーーー　×２
いつもながら、本当にそう思います。

jyo-3さん、お久しぶりです。
いよいよ、復活ですね。
EWでもお姿をしばらく見かけなかったので
寂しかったですよ～。またよろしくです。m(__)m

ところで先日、名古屋市役所の職員研修会で
「関数でできますか～？」の問題を改題して使いたいというメールが来ました。
いや～、まともな人は相手をしてくれることはないと思っていたのに･･･(^^ゞ

[投稿情報: y sakuda]

> 関数でできますか～？」の問題を改題して使いたいという
飛んでもねー研修になりそうｗ
しかし、解答ちゃんと説明できるのかな・・・・

[投稿情報: min]

＞「関数でできますか～？」の問題を改題して使いたいというメールが来ました。
＞いや～、まともな人は相手をしてくれることはないと思っていたのに･･･(^^ゞ

どんな研修なんでしょうね。
実務に役立つ問題ってあったのかなあ。

ま、ここを見て評価？してくれているってことはうれしいことですね。

[投稿情報: jyo-3]

では、、、
お送りします。

将棋盤で数字カード遊び・・・みたいなのです。

[投稿情報: jyo-3]

＞ま、ここを見て評価？してくれているってことはうれしいことですね。
でも、、、書くとき・・・飲んで、、わかりにくいこと書いたら・・・・だめねーーー

[投稿情報: くまぷー]

じょー３さん、どもです　m(__)m
それでは明日にでも公開させて頂きます。m(__)m

[投稿情報: y sakuda]

おお、ジョー3の出題か。楽しみにしてます。