第30問 上野発上越新幹線時刻表 解答例

出題者でありながら、お恥ずかしいですが完全版ではありません。

今週はお盆関係で忙しかったのと、曜日を一日勘違いしていまして

気がついたら時間が有りませんでした。(^^ゞ

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ますたあさん談）

A  209文字
=INDEX(A4:A39,MATCH(HOUR(I8),A4:A39,))&":"&INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),IF(ISNA(MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),))),1,MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,0),))+1))

B　190文字
=VLOOKUP(INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,)-1,IF(ISNA(MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),))),1,MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,0),))+1)),A42:B49,2,0)

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

くまぷーのファイル：Q30_2009_KUMAPOOH.lzhをダウンロード

Comments

[投稿情報: y sakuda]

いやー、今回は難しかったです。
最初に考えた方法は200超で始まって、190くらいまでは減らしましたがそれからどうにもならなかった。

で、発想を変えて上手く行ったのがこれです。
時刻（終電以降の入力禁止）63
{=MIN(MOD(IF(ISNUMBER(B4:G39),TIME(A4:A39,B4:G39,),)-I8,2))+I8}

終電以降の入力を可とした82
{=MIN(MIN(MOD(IF(ISNUMBER(B4:G39),TIME(A4:A39,B4:G39,),)-I8,2))+I8,TIME(6,B5,)+1)}

列車名は共通で94
=VLOOKUP(OFFSET(A1,HOUR(J11)*2-9,MATCH(MINUTE(J11),OFFSET(B2:G2,HOUR(J11)*2-9,),)),A42:B49,2,)

かなり初めの頃からTIME(A4:A39,B4:G39,)でなんとかならないかと考えていたのですが、指定時刻直近の
ポジションを特定する手段が見つからず、お蔵入りさせてました。
時間を直接取り出す手段も思いつかず、2でModを取って負の数字を上に追いやるというのを思いつくのに
3日もかかった＾＾；

[投稿情報: jyo-3]

この問題を見て、
途中で、やめちゃったけど、、、

mod
index
match
vlookup
あと、、
2,3の関数が、頭の中で・・・・
ぐるぐる・・・していた。

[投稿情報: くまぷー]

>いやー、今回は難しかったです。　×２

でも、問題自体は元祖エクセルコンテストのようで
良問だったと思います。

・・・で結局、ワンセルのかっこいい解答を
ひねり出せなかったので・・・
作業列アリのをアップさせて頂きました・
お目汚しですが、ご覧下さい　m(__)m

[投稿情報: y sakuda]

>問題自体は元祖エクセルコンテストのようで良問だったと思います。
ですね。
それを式一つでやろうとするから話がおかしくなるｗ
まあ、これはなんとかなりましたけどｗ

[投稿情報: くまぷー]

ところでむ印さんはどうしちゃったんだろう？

[投稿情報: kir]

こんばんは。

※現在の時刻表で、始発～最終までの検索のみ可
77：{=-LOOKUP(-I8,SMALL(-IF(ISERR(0/B5:G39),1,TIME(A5:A39,B5:G39,)),ROW(1:210)))}

※終電後は始発時間を表示
※時刻表の「分」は0分が出来ても対応
※0：00～翌始発時間までの検索ｏｋ（25:00入力ｏｋ→1:00扱い）
91：{=-LOOKUP(-I8,SMALL(-IF(ISNUMBER(B5:G39),TIME(A5:A39,B5:G39,),1+TIME(A5,B5,)),ROW(1:210)))}

列車名
89：=VLOOKUP(LOOKUP(MINUTE(J11),INDEX(B:G,HOUR(J11)*2-7,),INDEX(B:G,HOUR(J11)*2-8,)),A:B,2,0)

>2でModを取って負の数字を上に追いやる
なるほど～これは気付きませんでした。

[投稿情報: y sakuda]

kirさんの拝見してました。
私は今回なぜか、SMALL、LARGEは全く検討しなかったです。
Lookupは使えそうに思ったんですが、結局上手くゆかず断念しました。

終電の扱いのアイデア
1+TIME(A5,B5,)
は同じでしたね＾＾

列車名のVlookupで　A:Bっての気が付かなかった。
私ので応用すると90になりますね。

[投稿情報: y sakuda]

最初の頃の200超のやつも晒して置きます。
全部は残してないので比較的まともな終電対応版の205です。
=IF(I8＜1/4,TIME(6,B5,),IF(MAX(OFFSET(B2:G2,HOUR(I8)*2-9,))＜MINUTE(I8),TIME(I8*24+1,OFFSET(B2,HOUR(I8)*2-7,),),TIME(I8*24,INDEX(B2:G39,HOUR(I8)*2-8,FREQUENCY(OFFSET(B2:G2,HOUR(I8)*2-9,),MINUTE(I8)-1)+1),)))

[投稿情報: む印]

こんばんはぁ～

このごろ解答だけのパターンでスミマセン、、、

何の対処もなしですが・・・
時刻：７１
｛=MIN(IF(IF(B5:F39＜"",A5:A39&":"&B5:F39)*1>=I8,A5:A39&":"&B5:F39,1)*1)｝

列車名：８７
=VLOOKUP(INDEX(B:F,HOUR(J11)*2-8,RANK(MINUTE(J11),INDEX(B:F,HOUR(J11)*2-7,),1)),A:B,2,)

[投稿情報: minmax]

今回はmroundだなと思ってちょっと考えてみたけどおおざっぱに数えて190位かな…で紙に書いてちゃんと数えようと思ったけど気力が出ずに止めました
modは言われてみればそうだなあって感じです

kirさんの式ってあっているの？
ＰＣないからよくわからん

[投稿情報: y sakuda]

む印さんのは、見せられるとなるほどって感じですねーー
列車名でRANKも言われてみれば当然か＾＾；

[投稿情報: minmax]

む印さんお久しぶりです
む印さんの式もあっているの？わからん？

[投稿情報: y sakuda]

>kirさんの式ってあっているの？
あってますよーー
本物では、F9で表示できないので、範囲狭くして動きを確認しました。
（動き見ないと意味分からなかったｗ）

[投稿情報: y sakuda]

>む印さんの式もあっているの？わからん？
これも当然のようにOKです＾＾

からくちさんまだかな？

[投稿情報: minmax]

そうか、あっているのかー
ぱっとみ入力した時刻以後の電車を表示しているように見えた。

[投稿情報: くまぷー]

お～、理解を越えた式がたくさん・・・(￣□￣;)

[投稿情報: y sakuda]

む印さんのは、空欄と列車名のところを全部１にして上に追いやり、Minで直近の時刻を取得してます。
考え方は私のと似てます。

>お～、理解を越えた式がたくさん・・・(￣□￣;)
いきなり見たらそんな感じですねーー
でも、一週間ああでもないこうでもないと似たようなの試してましたので、「あっ！」と言う感じで分かりますね＾＾；

[投稿情報: minmax]

しつこくて本当にすみません。
む印さんのも10:18と入力すると10:17になるんですよね

[投稿情報: y sakuda]

>む印さんのも10:18と入力すると10:17になるんですよね
違います。
その時間以降の出発便ですから10;21とでます。

質問があり、出題者が↓で答えてます
>投稿： ますたあ | 2009年8月10日 (月) 午後 08時03分
>＞２、直近が複数ある場合、どちらか表示でＯＫ？もしくは前or後ｏｒ両方表示？
>指定時間後の直近だけを表示。
>前の列車はすでに発車しています。(^^ゞ

[投稿情報: minmax]

sakudaさんありがとうございます
それってちょうど同じ間隔の時間離れている電車が前後にあった場合のみ後発の電車時刻を表示するのかと思っていました。
そう思ったの私だけだったのね

む印さんとほぼおんなじ式なら最初に考えてこれじゃダメだと思い込んでいた…

[投稿情報: y sakuda]

素朴に時刻表検索のイメージで出題されたようです。
徹底的に凝れば、行き先、停車駅の判定も入れてってことになり、そこまで行くと式の世界ですべてやるのはワークセル使っても結構きびしいかもですｗ

[投稿情報: ますたあ]

大変遅くなりました。ペコ（自宅からなので顔文字が出ないんです。）

今日は、花火大会で先ほどまで息子一家や
親族で大賑わいでした。（汗

やっと静かになったので、アクセスできました。

ほんと、出題者でありながら、情けないです。（涙

でも、皆さんのすばらしい式を拝見できて感激しています。

かなり酔っ払っていますので、たぶん文章が支離滅裂だと．．．。（汗

明日、じっくり鑑賞させていただきます。

今回の問題は単純に発想だけで作ってしまいまいしたが
やはり、難しすぎましたね。
もっとすぐに式が作れるような問題であれば
レスの数も多くついたんでしょうが．．．。

でも、こんなに集中して式を作ったのは久しぶりですかね。
切羽詰ってね。（汗
あらためて、ああ頑張ればできるんだなんて
自分に感心したりして．．．。
ご参加いただきましてありがとうございました。（ペコ

それにしても、くまぷーさんすごいですね。
検索かけたら一発で見つけられました。

[投稿情報: ますたあ]

＞素朴に時刻表検索のイメージで出題されたようです。
そうです。
一応は、答えが出せるように問題を絞りました。
行く先が高崎だけなどなどにしたりしてね。

＞徹底的に凝れば、行き先、停車駅の判定も入れてってことになり
これも考えました。
行く先を指定して、それに対応した表に
作り変えてなんて．．．
そうすると、すべての時間の到着駅を調べて
どこかにそれを表示しなければいけませんし、
作るのにかなりの時間がかかるのと
果たしてそこまで作って関数だけでできるのか
分かりませんでしたので止めました。（汗

[投稿情報: y sakuda]

>やはり、難しすぎましたね。
どうなんでしょ？
私なんかは経過でご承知の通り、苦戦しましたが、回答を見るといともあっさり風のがぞろぞろ＾＾

>ペコ（自宅からなので顔文字が出ないんです。）
何時もは自宅からじゃないんですか？
まさか、牛舎の中からとかｗ

[投稿情報: minmax]

ますたあさんお疲れ様です
今回の問題はだいたいこのころの電車は何時がいいかなあって問題だと思い込んでいました
だからmroundを使おうと思った次第です
明日夜時間があったらそれでやろうかな

ところで、からくちさんまだかなあ〜

[投稿情報: y sakuda]

ますたーさんの所、首都圏とは目と鼻の先なんですが、お盆とかそういう行事は田舎の乗りになっちゃうみたいでたいへんですねーー
私なんかは、電車がすいてるってだけですがｗ

>ところで、からくちさんまだかなあ〜
×２
私ものべつチェックしながら待ってる＾＾

[投稿情報: y sakuda]

Googleで　くまぷー　で検索したら、
こんなのが出てきたｗ
http://members.jcom.home.ne.jp/kumano-pooh-san/
あるのも知らなかったｗ

[投稿情報: minmax]

くまぷーさんの道場訓は有名ですよ
私の中では

[投稿情報: む印]

からくちさんは確か静岡だったのでは…
地震以来、顔を出しておられない様なので
ちょっと心配ですねぇ～(-_-;

[投稿情報: y sakuda]

>地震以来、顔を出しておられない様なので
そう言えば全く顔をだされてないですね。
大丈夫かな？

[投稿情報: くまぷー]

お早うございます。

＞くまぷーさんの道場訓は有名ですよ
＞私の中では

だはははは(^^ゞ

[投稿情報: くまぷー]

＞>地震以来、顔を出しておられない様なので
＞そう言えば全く顔をだされてないですね。

そうですか～

＞ちょっと心配ですねぇ～(-_-;

いよいよ、在庫が底をつき
このままでは次回がファイナルで
出題者はからくちさんなんですけど・・・

[投稿情報: ますたあ]

＞何時もは自宅からじゃないんですか？
はいそうです。

場内に事務所がありまして、そこからです。
仕事の合間にチョコチョコやってます。(^^ゞ
すぐに見られるんだけど、すぐに見れない（仕事の関係で）環境です。

＞首都圏とは目と鼻の先なんですが、お盆とかそういう行事は
たしかに、昔からの行事や風習は大切にしています。
今は皆さん仕事がばらばらで
何かの行事でもないと、顔を合わせることすらないですからね。

それ（行事や風習の継続）をこなすのは大変な面もありますが
人と人との繋がりが保てますので
結構いいものなのかな～と私は思っています。

[投稿情報: くまぷー]

ますたあさんのところみたいな地域・・・
憬れるなあ・・・
うらやますぃ・・・

[投稿情報: y sakuda]

>うらやますぃ・・・
くまぷーさんとこだって、裏はすぐ国有の裏山になっているのではｗ

[投稿情報: y sakuda]

>いよいよ、在庫が底をつき
>このままでは次回がファイナルで

おーーい、ジョー3～～

[投稿情報: くまぷー]

>くまぷーさんとこだって、裏はすぐ国有の裏山になっているのではｗ

いや、民間人は立ち入り禁止です。(^^ゞ

[投稿情報: くまぷー]

＞おーーい、ジョー3～～　×２

[投稿情報: y sakuda]

しかし、本当にでてこないな＞からくちさん

[投稿情報: くまぷー]

>しかし、本当にでてこないな＞からくちさん

旅行にでも行っているのならいいのだが・・・

[投稿情報: からくち]

何かご心配をおかけしてしまったようで申し訳ないです

＞なお、指定できる時間は終電前としてください。
わたしの解釈 ： ０：００～２３：０７ の指定を満たせるもの

：{=-LOOKUP(-I8,-LARGE(IF(ISNUMBER(B4:G39),TIME(A4:A39,B4:G39,)),ROW(1:68)))}
：{=-LOOKUP(-I8,-LARGE(IF(ISNUMBER(B4:G39),A4:A39/24+B4:G39/1440),ROW(1:99)))}
：{=-LOOKUP(-I8,-LARGE(IF(A4:A39>0,
IF(B4:G39<>"",A4:A39/24+B4:G39/1440)),ROW(1:99)))}
：{=-LOOKUP(-I8,-LARGE(A4:A39/24+
IF((A4:A39>0)*(B4:G39<>""),B4:G39/1440,1),ROW(1:216)))}
：{=-LOOKUP(-I8,-LARGE(IF(ISNUMBER(B4:G39),
TIME(A4:A39,B4:G39,),1+TIME(6,13,)),ROW(1:216)))}
：{=-LOOKUP(-I8,-LARGE(A4:A39/24+
IF(ISERR(1/B4:G39),1+TIME(A5,B5,),B4:G39/1440),ROW(1:216)))}
：{=-LOOKUP(-I8,-LARGE(A4:A39/24+IF(ISERR(1/B4:G39),1,B4:G39/1440),ROW(1:216)))}
：{=-LOOKUP(-I8,-LARGE(A4:A39/24+
IF(ISNUMBER(B4:G39),B4:G39/1440,(24+A5)/24+B5/1440),ROW(1:216)))}

：=VLOOKUP(INDEX(B:G,HOUR(J11)*2-8,
MATCH(MINUTE(J11),INDEX(B:G,HOUR(J11)*2-7,),)),A:B,2,)
：=VLOOKUP(INDEX(B:G,HOUR(J11)*2-8,
MATCH(MINUTE(J11),OFFSET(B1,HOUR(J11)*2-8,,,6))),A:B,2,)
：=VLOOKUP(OFFSET(A1,HOUR(J11)*2-9,
MATCH(MINUTE(J11),OFFSET(B1,HOUR(J11)*2-8,,,6),)),A:B,2,)
：=VLOOKUP(OFFSET(A1,HOUR(J11)*2-9,
MATCH(MINUTE(J11),INDEX(B:G,HOUR(J11)*2-7,),)),A:B,2,)
：=VLOOKUP(INDEX(B:G,HOUR(J11)*2-8,
RANK(MINUTE(J11),INDEX(B:G,HOUR(J11)*2-7,),1)),A:B,2,)

組み合わせ方の違いでみな同じ

[投稿情報: からくち]

＞このままでは次回がファイナルで
残念です

やはり問題を作るのは難しいからか、、、

・誰もが手を出し易い、易しさのある問題
・いろいろな解がある問題
・面白さのある問題

こんな考えは必要ないと思うのですが
確かに易しい問題か、そうでもないか
いろいろあると思いますが・・・

１つ疑問と言うか提案と言うか、、、
それは 出題＞＞＞解答 これは何のため
・ただのお遊び・暇つぶし
・スキルアップ

前者なら、易しかろうが難しかろうが関係ないのでは
答えが１つしかなかろうが、あまり面白く無かろうが関係ないのでは

後者なら、若し問題が思ってた以上に難しいと捉えた方がいたら
出来た方にヒントの提示を要求すればいい
理由は、
（１）最後に解答を見てそれを勉強
（２）いただいたヒントから自身で解を導く
その解が自分のものになる・今後にいかせるようになる
多分（２）の方が数十倍はいいと思う

自分はこんな解を作ったぞ
自分はここまで無駄を省いたぞ
こんなことが目的では無いはず

それがどんな解であろうが自身での解を提示しあう
自身で解を導く喜びを得る
スキルアップが目的ならこれが最重要と思う

と考えれば、どんな問題でもいい
どんなに難解でもいい
出題者が出来ていなくてもいい
思いつきの問題でいい

先日、Q&Aに出ていたものを
このまま問題にしてもいいのでは
と１つ送っておいたのですが、、、そんなのでもいいと思う
わたしがその時送ったのは
出ていた回答に疑問を持ったから
そんなのでもいいと思う

もっと違った解があるかも
そんなのでもいいと思う

*新参者の戯言です*

[投稿情報: からくち]

＞出題者はからくちさんなんですけど・・・
易し過ぎると怒らないでください
ただ、終わりは見えません、、、果てしなく

[投稿情報: y sakuda]

おお、からくちさん登場。
待ってました。これからゆっくり鑑賞させていただきます。

>・ただのお遊び・暇つぶし
>・スキルアップ
私見ですが・・・・
お遊びでよろしいかと
ただ、こういうメンバーが集まってるとやはり思わぬ手法が随所に登場し、その結果スキルアップにつながるんでは？
まあ、私らの年でスキルアップしてもしょうがないんですが・・・・
私なんかは関数苦手な方ですから、前回の一筆書きと今回のでずいぶんいろんな手を覚えさせていただきました。

まあ、何回か書きましたが質問掲示板で初心者相手に示すべきではないようなものが大半でしょうけど＾＾；

[投稿情報: y sakuda]

からくちさんの拝見しました。
確かにLookupを利用した同系統のものですが、よくもまあ、あんなにヴァリエーションを＾＾；

私はLookup利用はかなり早い段階で挫折して放棄しました。

[投稿情報: jyo-3]

>・ただのお遊び・暇つぶし
>・スキルアップ
・・・・・
問題案は、あるのだが、（いくつか）

今、、、こねくり回しています。

私は、問題を作るのが楽しみです。
初見)・・・えーー、、こんなの、出来ないじゃん!!
ちょっとして）・・・あーーー出来るじゃん・・・

で、どさどさ、、発展していく。・・・・

ん、、呑んだから、日本語分かるかなーーー

[投稿情報: jyo-3]

その1：

おもてもんを，作って＝すぐ出来た（解答）
うらもんは、、ああーーー
呑んだから、面倒くさくなった。

[投稿情報: からくち]

＞確かにLookupを利用した同系統のものですが
ここには書きませんが （１）
他にはTEXT・FREQUENCYなど

ここには書きませんが （２）
若しこの表を実際にわたしが使うと言う前提で作ってみたら、、、３８
あっ、もちろん作業セル・名前は使いません
わたし専用ですので

[投稿情報: y sakuda]

38 ????
とんでもねー代物だな・・・・
一体全体どうやってそんなことを・・・・・

[投稿情報: minmax]

ようやく、さっき帰ってきて何日かぶりに自宅のPCを使える・・・
ってことで
今回の問題は思いこみで難しく考えていました。
入力時間に対してその後の列車時刻を探すなら紙でもかける程度なのですが、
入力時間に対して前後の最も近い列車時刻（ただし、入力時間に対して前後の列車が同間隔なら後発の列車時刻）を表示という私の思いこみの条件だと結構難しいと思い、結局紙に書いてやる気になれなかった。

自宅に帰ってきて私の思いこみの条件で殴り書きでやってみた。
入力時刻は3:06から23:06まで限定ですが、
{=MROUND(I8-MAX(IF(B4:G39＜"",IF(--(A4:A39&":"&B4:G39)＜I8,--(A4:A39&":"&B4:G39),),)),SUM(LARGE(IF(B4:G39＜"",--(A4:A39&":"&B4:G39),),SUM(--(IF(B4:G39＜"",--(A4:A39&":"&B4:G39),)＞I8))+{0,1})*{1,-1}))+MAX(IF(B4:G39＜"",IF(--(A4:A39&":"&B4:G39)

で、268文字と予定していた200弱より結構のびてしまった。
あくまでも、自己満足でした。

[投稿情報: y sakuda]

からくちさんの38ってのは想像もつかないですが、
私のとむ印さんの手法を合体したら59までダイエットできました＾＾
{=MIN(MOD(IF(ISERR(0/B4:G39),,A4:A39&":"&B4:G39)-I8,2))+I8}

[投稿情報: y sakuda]

minmaxさんの労作試してみたいのですが、最後のとこ切れてませんか？

[投稿情報: minmax]

sakudaさんならからくちさんの38を追及してるんだろなと思った
やっぱり気力が違う〜

[投稿情報: y sakuda]

>38を追及してるんだろなと思った
やっぱり気力が違う〜
してませんよ＾＾；
全く見当もつかないです

[投稿情報: minmax]

２６８文字
再掲
{=MROUND(I8-MAX(IF(B4:G39＜"",IF(--(A4:A39&":"&B4:G39)＜I8,--(A4:A39&":"&B4:G39),),)),SUM(LARGE(IF(B4:G39＜"",--(A4:A39&":"&B4:G39),),SUM(--(IF(B4:G39＜"",--(A4:A39&":"&B4:G39),)＞I8))+{0,1})*{1,-1}))+MAX(IF(B4:G39＜"",IF(--(A4:A39&":"&B4:G39)＜I8,--(A4:A39&":"&B4:G39),),))}
今度は？

[投稿情報: y sakuda]

今度はばっちりですが・・・・・・
からくりがさっぱり分からんｗ
MRoundなんて使ったことないと思いますねーー

[投稿情報: minmax]

＞MRoundなんて使ったことないと思いますねーー
私も人生で初めて使いました。

[投稿情報: y sakuda]

む印さんのテクもう一つ拝借すると53になった
{=MIN(MOD(IF(B4:G39<"",A4:A39&":"&B4:G39,)-I8,2))+I8}

最初始めた時の200越してもできるんだろうか？
というの考えると私としては満足です＾＾

[投稿情報: y sakuda]

>私も人生で初めて使いました。
www
Excelの関数って財務関数とか統計関数で意味は分かっていても使う局面が無いのはたくさんありますが、MRoundなんかはどういうところで使うのか見当もつかないやつの一つですよね＾＾
Frequencyなんかもほとんど使ったことはありませんが、これは一回だけですが、田中さんの掲示板のレスで使ったことをはっきり覚えてます。
Lookupなんかも使ったことがあったかどうか極めて怪しい＾＾；

[投稿情報: kir]

>む印さんのテクもう一つ拝借すると53になった
>{=MIN(MOD(IF(B4:G39<"",A4:A39&":"&B4:G39,)-I8,2))+I8}

IFの２つ目の「,」削除で52ですね！

[投稿情報: jyo-3]

sakudaさん、、意欲的だなーー
からくち　さん・・・・すごいなーーー
むーさんも・・・すごい・・・・

kirさんも、minmaxさんも・・・鋭いし・・・

おれーーーかなわんなーーー

で、問題作って、送った。
お遊び、お遊び・・・・おもしれーーー

[投稿情報: くまぷー]

>で、問題作って、送った。

えっ、？！
ジョー３さん、届きませんが・・・(^^ゞ

[投稿情報: y sakuda]

>IFの２つ目の「,」削除で52ですね！
あははっ、見落としてました＾＾