Excelで電車の出発時刻表示をしたい

質問

手も足も出ず、大変困っています。みなさんのお力を貸してください！

（1）まず、あるセルに1を入力するとその横のセルに「平日」、2なら「休日」、それ以外なら「再入力」と表示するようにします。（2）そして、何時台（7～19時）で、何本目（「1～5」の数字で入力）の電車かを入力すると（3）電車の出発時刻が表示されるようにします。その際、平日の場合は「平日ダイヤ」、休日の場合は「休日ダイヤ」を参照し「○時○分」の形式で表示するようにします。

平日ダイヤ
　　（休日ダイヤも時間は違いますが同様の形です）
　　　　A　　B　　　C　　D　　　E　　F
　1　　　　　1　　　2　　　3　　　4　　　5←何本目か
　2　　7時　　0　　15　　30　　40　　55
　3　　8時　　2　　16　　30　　40　　56
　4　　9時　　1　　15　　30　　46　　56
　・　　・　　・　　・　　・　　・　　・
　・　　・　　・　　・　　・　　・　　・
　11　16時　　1　　15　　30　　40　　55

時刻換算表
　　　　G　　　H
　1　　7時　　1
　2　　8時　　2
　3　　9時　　3
　・　　・　　・
　・　　・　　・
　10　16時　　10

Excel初心者で、何をどのように使って出発時刻をだしたらいいのか分かりません。ifを使って地道に計算していくと式がかなり長くなるし…VLOOKUPも組み合わせるのかなとも思ったのですが、どこにどう組み入れていいのかわかりません。
どのような方法で計算したらいいのか教えて下さい。よろしくお願いします。

回答No.5

imogasi

#3です。一般化という話が出たのを見て
例データ
「平日ダイヤ」とSheet1のシート名を改める
7時 0 15 30 40 55
8時 2 16 30 40 56
9時 1 15 30 46 56
時間の列は、入力データは７，８，９・・として、書式でユーザー定義「＃時」を設定して、上記のように見えているもの。
休日ダイヤとSheet2のシート名をかえる。
7時 2 17 32 43 57
8時 4 18 32 43 58
9時 3 17 32 43 57
７，８，９時の時は書式で出している。
Sheet３に行って
2 休日
何時台 何本目 出発時刻
8 3 32
出発時刻の下のセルの式は
=INDEX(INDIRECT(B1&"ダイヤ!$A$1:$j$19"),MATCH(A3,INDIRECT(B1&"ダイヤ!$A$1:$A$19"),0),B3+1)
J列のｊー＞１日の各時間帯のうち、最大本数の列で修正してください
＄１９の１９－＞１日の時間の最終ダイヤがある最下行で修正してください。
A1に３以上を入れたときのエラーは=IF(A!>2,・・・）や
=IF(OR(A1=1,A1=2),・・・）を加えてください。
ーーー
この質問は各時刻の「何番目」を指定することになっている。時刻表では、この何番目は、時刻表が手元に見てないはずで、判るはずがないので設問としておかしい。
何かほかの（職場などの）問題をそのまま質問に公表することを恐れ、時刻表の例に変えたのではないですか。しかし元の問題の解決に戻すときに、初心者が適切に式や考え方を修正できるか疑問あり。
時々そういう推測例に出くわすが、できるだけ正直に実際例に近い例で、質問したほうが良いのでは。
「下司の勘ぐり」であればすみません。

お礼

またのご回答ありがとうございます。学校の課題なのですが、質問をどうまとめたら良いか分からず初心者丸出しで
書き込んでしまいました（^_^; これからしっかり勉強したいと思います。丁寧なご回答ありがとうございました！

回答No.4

NCU

#2です。
こっちの方がより正確で汎用性があるかな。（長いけど）

=INDEX((平日ダイヤ,休日ダイヤ),MATCH(h時,INDEX((平日「時」リスト,休日「時」リスト),,,平日休日を示す番号),0),n本目,平日休日を示す番号)

お礼

またのご回答ありがとうございます。完成できました。このような方法もあるのですね！これからぜひ勉強したいと思います。ありがとうございました！

回答No.3

imogasi

例データ
Sheet1　　平日ダイヤ
7時 0 15 30 40 55
8時 2 16 30 40 56
9時 1 15 30 46 56
Sheet2　　休日ダイヤ
7時 2 17 32 43 57
8時 4 18 32 43 58
9時 3 17 32 43 57
Sheet3のA1に１，２，３を入れる。
B1に=IF(A1=1,"平日",IF(A1=2,"休日","再入力"))
A2:D2に
何時台 何本目 出発時刻
A3に７、B3に２
D3に
=IF(A1=1,INDEX(Sheet1!$A$1:$I$19,A3-6,B3+1),IF(A1=2,INDEX(Sheet2!$A$1:$I$19,A3-6,B3+1),""))
で１５
該当無しエラー対策は略（複雑になり焦点がぼやけるので）

お礼

ご回答ありがとうございました！

回答No.2

NCU

=INDEX((平日ダイヤ,休日ダイヤ),MATCH(h時台,同左リスト,0),n本目,平日/休日を示す番号)

お礼

ご回答ありがとうございました！

回答No.1

onntao

お仕事でしょうか
学校でしょうか
丸投げというつもりはないのでしょうけど・・・・

先ずは下記あたりをを参考にしてください

検索/行列関数（INDIRECT関数）
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_9_5.htm#検索/行列関数（INDIRECT関数）
の使用例 2

日付/時刻関数（DATE、TIME関数）
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_8.htm#日付/時刻関数（DATE、TIME関数）
のTIME関数

お礼

ご回答ありがとうございます。学校ででた急な課題であせっていましたが、完成できました。参考にさせて頂きました。INDIRECT関数はまだ勉強していないので、これからぜひ勉強したいと思います。ありがとうございました！