Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : Excel 2010)
検索について2
投稿日時: 17/10/14 12:54:19
投稿者: もこもこ

お世話になります。
 
先日は無事に解決でき大変助かりました。
ありがとうございました。
検索について(投稿日時: 17/10/06、17/10/09解決済み)
 
実は、更なる課題を出されてしまいとても困っております。
 
1、前後の号車が解らないか?
今現在(解決済み)
検索(J5) 番号(K5) 結果(L5)
「は駅」 「6:26」 「2号」
 
新たな課題(2号の結果が出てそのうえで前後がわかる結果)
前の号車を検索 「は駅」    「6:26」   「1号」
後の号車を検索 「は駅」    「6:26」   「3号」
    A B C D
1 1号 2号 3号
2 い駅 6:10 6:24 6:37
3 ろ駅 6:11 6:25 6:38
4 は駅 6:12 6:26 6:39
5 に駅 6:13 6:27 6:40
6 ほ駅 6:13 6:27 6:40
 
2、逆に、「号車」「番号」を入力してどこにいるのか?
「2号」、「6:26」「???」
これに関しては該当がなければ「該当なし」で大丈夫なのですが、、、
 
3、検索する時刻表が10種類以上あって切り替える方法があるのか?
解らずに困っております。
いま、10数種類ごとに検索欄を作成し調べることはできたのですが、利便性を考え一つの検索で調べられないか?今は行先ごとに表を開いて検索しています。
 
「検索」「番号」「検索、番号に応じた表で結果」
ブックは1つにしてありますが表の大きさがバラバラです。
 
仕事中で返信は遅くなると思います。

回答
投稿日時: 17/10/14 14:09:45
投稿者: んなっと

● 3.の質問は10種類の表ごとに「名前の定義」をして、
行先で検索範囲を変化させることになるような気がします。
情報不足ですので、後日、別スレッドを立てて詳細を書いてください。
 
● 2.の質問は、前回と行列を入れ替えるだけでいいと思います。
 
   O   P   Q   R
 5 3号 6:36      
 6 3号 6:37 い駅 い駅
 7 3号 6:38 い駅   
 8 3号 6:39 ろ駅 ろ駅
 9 3号 6:40 ろ駅   
10 3号 6:41 は駅 は駅
11 3号 6:42 は駅   
12 3号 6:43 に駅 に駅
13 3号 6:44 に駅   
14 3号 6:45 ほ駅 ほ駅
15 3号 6:46 ほ駅   
 
Q5
=IFERROR(LOOKUP(P5,INDEX(B$2:D$6,,MATCH(O5,B$1:D$1,0)),A$2:A$6),"")
下方向・↓
 
または 
R5
=IFERROR(INDEX(A$2:A$6,MATCH(P5,INDEX(B$2:H$6,,MATCH(O5,B$1:H$1,0)),0)),"")
下方向・↓
 
● 1.の質問が問題です。
 
1号が2号を途中で追い越すことがないなど、条件が単純であれば
 
   A   B   C   D
1     1号  2号  3号
2 い駅 6:10 6:24 6:37
3 ろ駅 6:12 6:25 6:39
4 は駅 6:14 6:26 6:41
5 に駅 6:16 6:27 6:43
6 ほ駅 6:18 6:28 6:45
 
    J   K  L  M  N
 4        前  中  後
 5 は駅 6:25    1号 2号
 6 は駅 6:26 1号 2号 3号
 7 は駅 6:27 1号 2号 3号
 8 は駅 6:28 1号 2号 3号
 9 は駅 6:29 1号 2号 3号
10 は駅 6:30 1号 2号 3号
11 は駅 6:31 1号 2号 3号
12 は駅 6:32 1号 2号 3号
13 は駅 6:33 1号 2号 3号
14 は駅 6:34 1号 2号 3号
15 は駅 6:35 1号 2号 3号
16 は駅 6:36 1号 2号 3号
17 は駅 6:37 1号 2号 3号
18 は駅 6:38 1号 2号 3号
19 は駅 6:39 1号 2号 3号
20 は駅 6:40 1号 2号 3号
21 は駅 6:41 2号 3号   
 
L5
=IFERROR(LOOKUP(K5,INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),A$1:G$1)&"","")
下方向・↓
M5
=IFERROR(LOOKUP(K5,INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),B$1:H$1),"")
下方向・↓
N5
=IFERROR(LOOKUP(K5,INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),C$1:I$1)&"","")
下方向・↓
 
しかし2号が快速電車で、普通電車の1号が途中の駅で2号の通過待ち、
追い抜かれるなどのことがあると少し難しくなります。
具体例は...
 
   A   B   C   D
1     1号  2号  3号
2 い駅 6:10 6:24 6:37
3 ろ駅 6:27    6:39
4 は駅 6:29 6:26 6:41
5 に駅 6:31    6:43
6 ほ駅 6:33 6:28   
 
こんなこともありますか?

投稿日時: 17/10/14 15:54:39
投稿者: もこもこ

んなっと様
 
返信ありがとうございます。
 
まだ、試してはいませんが取り急ぎ連絡いたします。
 
● 3.の質問は10種類の表ごとに「名前の定義」をして、
行先で検索範囲を変化させることになるような気がします。
情報不足ですので、後日、別スレッドを立てて詳細を書いてください。
  
はいわかりました。
こちらはあとでもう少し詳しく書いてみます。
 
● 2.の質問は、前回と行列を入れ替えるだけでいいと思います。
  
時間が出来ましたらやってみます。
  
● 1.の質問が問題です。
 
しかし2号が快速電車で、普通電車の1号が途中の駅で2号の通過待ち、
追い抜かれるなどのことがあると少し難しくなります。
具体例は...
  
   A   B   C   D
1     1号  2号  3号
2 い駅 6:10 6:24 6:37
3 ろ駅 6:27    6:39
4 は駅 6:29 6:26 6:41
5 に駅 6:31    6:43
6 ほ駅 6:33 6:28   
  
こんなこともありますか?
 
ご指摘があるように途中で追い越す駅がありました。
複数の発車地から終点近くに合流する駅で上記の図のような現象があります。

回答
投稿日時: 17/10/14 16:07:12
投稿者: んなっと

時間をかければもう少し短い式になりますが、とりあえずの案です。
元の時刻表に0:00が存在すると、さらに工夫が必要になります。
 
    J   K  L  M  N
 4        前  中  後
 5 は駅 6:24      2号
 6 は駅 6:25      2号
 7 は駅 6:26    2号 1号
 8 は駅 6:27    2号 1号
 9 は駅 6:28    2号 1号
10 は駅 6:29 2号 1号 3号
11 は駅 6:30 2号 1号 3号
12 は駅 6:31 2号 1号 3号
 
L5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(14,6,(1*TEXT(INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),"[<="&K5+"0:00:01"&"]h:mm;;")),2),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
M5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(14,6,(1*TEXT(INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),"[<="&K5+"0:00:01"&"]h:mm;;")),1),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
N5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(15,6,(1*TEXT(INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),"[>"&K5+"0:00:01"&"]h:mm;;")),1),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
 
    J   K  L  M  N
 4        前  中  後
 5 ろ駅 6:24      1号
 6 ろ駅 6:25      1号
 7 ろ駅 6:26      1号
 8 ろ駅 6:27    1号 3号
 9 ろ駅 6:28    1号 3号
10 ろ駅 6:29    1号 3号
11 ろ駅 6:30    1号 3号
12 ろ駅 6:31    1号 3号
13 ろ駅 6:32    1号 3号
14 ろ駅 6:33    1号 3号
15 ろ駅 6:34    1号 3号
16 ろ駅 6:35    1号 3号
17 ろ駅 6:36    1号 3号
18 ろ駅 6:37    1号 3号
19 ろ駅 6:38    1号 3号
20 ろ駅 6:39 1号 3号   
21 ろ駅 6:40 1号 3号   
 
このような複雑な数式になるときは、本来作業列や作業セルを使うのが鉄則です。
しかしやり取りに時間がかかるような気がするので省略します。
手抜き回答でごめんなさい。

投稿日時: 17/10/14 18:47:11
投稿者: もこもこ

んなっと様
 
● 2.の質問は、前回と行列を入れ替えるだけでいいと思います。
 
質問をしたのですが、2番目を試してほかのことに手が付きません。
お時間かかるかもしれません。
 
 
全体の表の一部です。時間と号車はもっと伸びます。
(混乱しそうなので行と列番号は記載していません。)
 
    1号    2号    3号    1号    4号    2号
い駅    6:10    6:24    6:37    6:48    6:59    7:09
ろ駅    6:11    6:25    6:38    6:49    7:00    7:10
は駅    6:12    6:26    6:39    6:50    7:01    7:11
に駅    6:13    6:27    6:40    6:51    7:02    7:12
ほ駅    6:13    6:27    6:40    6:51    7:02    7:12
へ駅    6:14    6:28    6:41    6:52    7:03    7:13
と駅    6:14    6:28    6:41    6:52    7:03    7:13
 
検索(J5) 番号(K5) 結果(L5)
「1号」「6:10」「い駅」
 
=L5
=IFERROR(LOOKUP(K5,INDEX(B$2:G$8,,MATCH(J5,B$1:G$1,0)),A$2:A$8),"")
 
 
次の検索をかけると
 
「1号」「6:48」「と駅」
 
になってしまいます。
 
同じ号車が往復するので何度も出てきます。
 
結果は
んなっと様のQ列の表示できるといいのですが。
 
 
   O   P   Q   R
  5 3号 6:36      
 6 3号 6:37 い駅 い駅
 7 3号 6:38 い駅   
 8 3号 6:39 ろ駅 ろ駅
 9 3号 6:40 ろ駅   
10 3号 6:41 は駅 は駅
11 3号 6:42 は駅   
12 3号 6:43 に駅 に駅
13 3号 6:44 に駅   
14 3号 6:45 ほ駅 ほ駅
15 3号 6:46 ほ駅   
 
行と列を入れ替えるの意味が間違っていたらすいません。
 
 

回答
投稿日時: 17/10/14 19:03:30
投稿者: んなっと

少し速度が遅くなるけど、こんなのも。
 
L5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(14,6,B$2:H$6/(B$2:H$6<=K5)/(A$2:A$6=J5),2),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
M5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(14,6,B$2:H$6/(B$2:H$6<=K5)/(A$2:A$6=J5),1),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
N5
=IFERROR(INDEX(B$1:H$1,MATCH(AGGREGATE(15,6,B$2:H$6/(B$2:H$6>K5)/(A$2:A$6=J5),1),INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓

回答
投稿日時: 17/10/14 19:34:37
投稿者: んなっと

引用:
同じ号車が往復するので何度も出てきます。

最初の表では、この情報がなかったのですから当然です。それはわかりますよね?
 
   J   K   L
 5 1号 6:10 い駅
 6 1号 6:11 ろ駅
 7 1号 6:12 は駅
 8 1号 6:13 に駅
 9 1号 6:14 ほ駅
10 1号 6:15 へ駅
11 1号 6:16 と駅
12 1号 6:17 と駅
13 1号 6:18 と駅
14 1号 6:19 と駅
15 1号 6:46 と駅
16 1号 6:47 と駅
17 1号 6:48 い駅
18 1号 6:49 ろ駅
19 1号 6:50 は駅
20 1号 6:51 に駅
21 1号 6:52 ほ駅
22 1号 6:53 へ駅
23 1号 6:54 と駅
24 1号 6:55 と駅
 
L5
=LOOKUP(K5,INDEX(B$2:H$8,,MATCH(K5,INDEX(B$2:H$2/(B$1:H$1=J5),))),A$2:A$8)
下方向・↓

投稿日時: 17/10/14 19:44:16
投稿者: もこもこ

んなっと様
 
ありがとうございます。
 
初めの表に1号〜3号までしか作ってなかったので説明が足りませんでした。
 
申し訳ありません。
 
 

回答
投稿日時: 17/10/14 19:56:41
投稿者: sy

こんばんわ。
 
前回と同じレイアウトなら、1と2は以下で出来ると思います。
 

1、
   J    K    L      M        N
4 駅名 時間 号車 前の号車 後の号車
5 ほ駅 6:26 1号 該当なし  2号
M5 =IFERROR(INDEX(B1:D1,MATCH(K5,INDEX(B2:D6,MATCH(J5,A2:A6,0),0))-1),"該当なし")
N5 =IFERROR(INDEX(B1:D1,MATCH(K5,INDEX(B2:D6,MATCH(J5,A2:A6,0),0))+1),"該当なし")

2,
   J    K    L
7 号車 時間 駅名
8 2号 6:26 は駅
L8 =LOOKUP(K8,INDEX(B2:D6,0,MATCH(J8,B1:D1,0)),A2:A6)

 
3に関しては、全てのリストは同じレイアウトになってるんですか?

投稿日時: 17/10/14 20:14:28
投稿者: もこもこ

んなっと 様
 
ありがとうございました。
思っている通りになりました。
 
 
sy 様
 
まだ、手つかずですが、3番のレイアウトは少しづつ変わっています。
駅がたくさんあるところや号車がたくさんんあるところがあるので少し表の大きさは変わります。
列に駅名
行に号車のレイアウトは変わりません。
 

回答
投稿日時: 17/10/14 20:19:45
投稿者: んなっと

● 3. さらに短く。
 
   A   B   C   D   E   F   G
1     1号  2号  3号  1号  4号  2号
2 い駅 6:10 6:24 6:37 6:48 6:59 7:09
3 ろ駅 6:27    6:39 6:49 7:12   
4 は駅 6:29 6:26 6:41 6:50 7:14 7:11
5 に駅 6:31    6:43 6:51 7:16   
6 ほ駅 6:33 6:28    6:52 7:18 7:13
7 へ駅 6:35       6:53 7:20   
8 と駅 6:37 6:30    6:54 7:22 7:15
 
    J   K  L  M  N
 4        前  中  後
 5 は駅 6:24      2号
 6 は駅 6:25      2号
 7 は駅 6:26    2号 1号
 8 は駅 6:27    2号 1号
 9 は駅 6:28    2号 1号
10 は駅 6:29 2号 1号 3号
11 は駅 6:30 2号 1号 3号
12 は駅 6:31 2号 1号 3号
13 は駅 6:50 3号 1号 2号
14 は駅 7:13 1号 2号 4号
15 は駅 7:14 2号 4号   
16 は駅 7:15 2号 4号   
 
L5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(14,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"hmm;;")/(B$2:H$8<=K5)/(A$2:A$8=J5),2),1)*1000),"")
下方向・↓
M5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(14,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"hmm;;")/(B$2:H$8<=K5)/(A$2:A$8=J5),1),1)*1000),"")
下方向・↓
N5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(15,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"hmm;;")/(B$2:H$8>K5)/(A$2:A$8=J5),1),1)*1000),"")
下方向・↓

回答
投稿日時: 17/10/14 20:23:58
投稿者: sy

もこもこ さんの引用:
全体の表の一部です。時間と号車はもっと伸びます。
(混乱しそうなので行と列番号は記載していません。)
 
    1号    2号    3号    1号    4号    2号
い駅    6:10    6:24    6:37    6:48    6:59    7:09
ろ駅    6:11    6:25    6:38    6:49    7:00    7:10
は駅    6:12    6:26    6:39    6:50    7:01    7:11
に駅    6:13    6:27    6:40    6:51    7:02    7:12
ほ駅    6:13    6:27    6:40    6:51    7:02    7:12
へ駅    6:14    6:28    6:41    6:52    7:03    7:13
と駅    6:14    6:28    6:41    6:52    7:03    7:13
 
検索(J5) 番号(K5) 結果(L5)
「1号」「6:10」「い駅」
これ、号車は最高I列までなんですか?
J5セルに駅名の検索条件を入れるなら、I列までしかリストは広げられないですよね?
それとも、検索条件や結果のセルは別のシートにあって、J列より右にもリストは広がる可能性もあるんですか?
 
何れにしても範囲が広がる(可変)可能性があるなら、式を以下に変更して下さい。
L5 =IFERROR(LOOKUP(K5,INDEX(A:I,MATCH(J5,A:A,0),0),1:1),"該当なし")
M5 =IFERROR(TEXT(INDEX(1:1,MATCH(K5,INDEX(A:I,MATCH(J5,A:A,0),0))-1),";;該当なし"),"該当なし")
N5 =IFERROR(TEXT(INDEX(1:1,MATCH(K5,INDEX(A:I,MATCH(J5,A:A,0),0))+1),";;該当なし"),"1号")
L8 =IFERROR(LOOKUP(K8,INDEX(A:I,0,MATCH(J8,1:1,0)),A:A),"該当なし")
INDEXのA:Iの範囲はリストの横幅に合わせて下さい。

回答
投稿日時: 17/10/14 20:28:09
投稿者: sy

もこもこ さんの引用:
まだ、手つかずですが、3番のレイアウトは少しづつ変わっています。
駅がたくさんあるところや号車がたくさんんあるところがあるので少し表の大きさは変わります。
列に駅名
行に号車のレイアウトは変わりません。

レイアウトは同じで縦横の大きさが違うだけですか?
 
それなら検索で使う数式が参照しているセル範囲に、複数のリストを読込むようにすれば、
先ほど私が提示した式で全て表示出来ますね。
 

回答
投稿日時: 17/10/14 20:29:48
投稿者: んなっと

修正。
● 3. さらに短く。
  ↓
● 1. さらに短く。
 
1.は列車が前の列車を追い越すことがあるということなので、面倒です。

回答
投稿日時: 17/10/14 20:31:55
投稿者: sy

送信してしまった。
 
各リストの、シート名(シートで別れているなら)と、セル範囲を全て書出してください。
 

例
リスト1 Sheet1A1:I100
リスト2 Sheet2A1:G30

のように書き出して下さい。

回答
投稿日時: 17/10/14 20:34:51
投稿者: sy

もこもこ さんの引用:
    1号    2号    3号    1号    4号    2号

今気付いたけど、何で3号の右が1号に戻ってるんですか?

投稿日時: 17/10/14 20:34:58
投稿者: もこもこ

んなっと 様 
 
少々お時間ください。(汗)
 
 
sy 様
 
ありがとうございます。
 
すいません。
今現在の表は試験的に作った表なので、検索するシートは別にあります。
 
ブックの構成は
検索シート(1枚)
時刻表(10数枚)表は大きさはバラバラになります。
で構成する予定です。
 
これから仕事が忙しくなりますので、返事は遅れます。
 

投稿日時: 17/10/14 20:49:18
投稿者: もこもこ

 
sy様
 
もこもこ さんの引用:
    1号 2号 3号 1号 4号 2号
 
 今気付いたけど、何で3号の右が1号に戻ってるんですか?
 
 
少し慌てています。
返信も前後していますが許してください。
 
説明がたらなかったようで申し訳ありません。
 

1号
い駅→ほ駅
ほ駅→い駅
い駅→ほ駅(元に戻る)
 
を繰り返します。
 
2号
ほ駅→い駅
い駅→ほ駅
ほ駅→い駅(元に戻る)→ここで休憩等に入り時刻表より時間が無くなります。
繰り返しますが休憩が入ると番号が前後します。
 
各号車が行ったり来たりしますので、何往復もします。
説明が足らない気がしますが(汗)
 

回答
投稿日時: 17/10/14 21:05:15
投稿者: sy

もこもこ さんの引用:
同じ号車が往復するので何度も出てきます。

すいません。
見逃してました。
 
L8 =IFERROR(INDEX(A:A,MATCH(K8,INDEX(1:1048576,0,AGGREGATE(14,6,(2:2<=K8)*(1:1=J8)*COLUMN(1:1),1)))),"該当なし")

にして下さい。
 
投稿日時: 17/10/14 20:23:58の式も、範囲のI列の部分は、全てのリスト中で一番大きい列番号のリスト以上の列番号にして下さい。
式のセル数は少ないので、めんどくさかったらA:XFD(1:1048576)でも良いです。
 
もこもこ さんの引用:
ブックの構成は
検索シート(1枚)
時刻表(10数枚)表は大きさはバラバラになります。
で構成する予定です。
  
これから仕事が忙しくなりますので、返事は遅れます。

ご自身のペースに合わせて頂いてで結構です。
リストのアドレスを挙げてくれたら、切り替える方法を提示します。

回答
投稿日時: 17/10/14 21:20:07
投稿者: んなっと

整理します。最終形です。
 
元データ
 
   A   B   C   D   E   F   G
1     1号  2号  3号  1号  4号  2号
2 い駅 6:10 6:24 6:37 6:48 6:59 7:09
3 ろ駅 6:27    6:39 6:49 7:12   
4 は駅 6:29 6:26 6:41 6:50 7:14 7:11 ←2号が1号を追い越すこともあり難しい。
5 に駅 6:31    6:43 6:51 7:16   
6 ほ駅 6:33 6:28    6:52 7:18 7:13
7 へ駅 6:35       6:53 7:20   
8 と駅 6:37 6:30    6:54 7:22 7:15
 
● 1.の質問
 
    J   K  L  M  N
 4        前  中  後
 5 は駅 6:24      2号
 6 は駅 6:25      2号
 7 は駅 6:26    2号 1号
 8 は駅 6:27    2号 1号
 9 は駅 6:28    2号 1号
10 は駅 6:29 2号 1号 3号
11 は駅 6:30 2号 1号 3号
12 は駅 6:31 2号 1号 3号
13 は駅 6:50 3号 1号 2号
14 は駅 7:13 1号 2号 4号
15 は駅 7:14 2号 4号   
16 は駅 7:15 2号 4号   
 
L5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(14,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"[h]mm;;")/(B$2:H$8<=K5)/(A$2:A$8=J5),2),1)*1000),"")
下方向・↓
M5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(14,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"[h]mm;;")/(B$2:H$8<=K5)/(A$2:A$8=J5),1),1)*1000),"")
下方向・↓
N5
=IFERROR(INDEX($1:$1,MOD(AGGREGATE(15,6,COLUMN(B:H)/1000+TEXT(B$2:H$8,"[h]mm;;")/(B$2:H$8>K5)/(A$2:A$8=J5),1),1)*1000),"")
下方向・↓
 
 
● 2.の質問
 
   J   K   L
 5 2号 6:23   
 6 2号 6:24 い駅
 7 2号 6:25 い駅
 8 2号 6:26 は駅
 9 2号 6:27 は駅
10 2号 6:28 ほ駅
11 2号 6:29 ほ駅
12 2号 6:30 と駅
 
L5
=IFERROR(LOOKUP(K5,INDEX(B$2:H$8,,MATCH(K5,INDEX(B$2:H$2/(B$1:H$1=J5),))),A$2:A$8),"")
下方向・↓
 
● 3.の質問
 
質問が多いので、別スレッドということで同意していただきました。

回答
投稿日時: 17/10/14 22:02:17
投稿者: sy

んなっと さんの引用:

   A   B   C   D   E   F   G
1     1号  2号  3号  1号  4号  2号
2 い駅 6:10 6:24 6:37 6:48 6:59 7:09
3 ろ駅 6:27    6:39 6:49 7:12   
4 は駅 6:29 6:26 6:41 6:50 7:14 7:11 ←2号が1号を追い越すこともあり難しい。
5 に駅 6:31    6:43 6:51 7:16   
6 ほ駅 6:33 6:28    6:52 7:18 7:13
7 へ駅 6:35       6:53 7:20   
8 と駅 6:37 6:30    6:54 7:22 7:15

ちょっと私が勘違いしてるのかな?
私は出張などで特急の時刻表とかを検索してると思ってるんですけど、
その場合は全て同じ駅数・同じ時間間隔になると思ってたんですけど。
 
今回の質問は、各路線の普通車から特急までの全ての時刻表の検索ですか?
 
それだと都市部などの路線では膨大なデータ量になりませんか?
私の家は比較的田舎ですけど、それでも1日1つの駅で140本以上の電車が走ってますよ。
都市部に近付いたら、1日1つの駅で400本とか走ってますよ。
そこまでのデータ量でしたら、数式では処理が重くなりすぎるのでマクロになると思いますし、
ホントにそんな検索がしたいんですか?
 
いったい検索の目的は何ですか?
 

回答
投稿日時: 17/10/14 22:08:44
投稿者: んなっと

もこもこさんも書かれていますよ。

引用:
ご指摘があるように途中で追い越す駅がありました。
複数の発車地から終点近くに合流する駅で上記の図のような現象があります。

回答
投稿日時: 17/10/14 22:19:07
投稿者: sy

んなっと さんの引用:
もこもこさんも書かれていますよ。
引用:
ご指摘があるように途中で追い越す駅がありました。
複数の発車地から終点近くに合流する駅で上記の図のような現象があります。

あっ、
んなっとさんへのレスでは無くて、上述したように私が勘違いしてる部分はあると思ったんですけど、
普通車から特急までの全ての時刻表検索だと、かなり膨大なデータ規模になると思いますので、
数式よりマクロが必要になってくるでしょうし、今回の目的がホントに同じ時系列リストの中に、
普通車から特急全て含めさせる必要性があるのかなど、もう一度きちんと確認したいと思いました。
 
目的次第では混在させる必要は無いと思いましたので。

投稿日時: 17/10/14 22:23:12
投稿者: もこもこ

sy 様
 
今回の目的は忘れ物なんです。
 
時刻表があるのですが、
 
お客様から問い合わせが 
何時「時間」
どこで「駅」
どれ「号車」
 
アバウトな時間を言われ探すのに時間がかかることもあり
どの号車かわかればすぐに見つけることが可能なためできないかな?って質問してみました。
 
初めの質問で検索することはできたのですが、アバウトな時間を言われると
次のことが必要になると考えました。
 
質問1「前後の号車の検索」
 
質問2「その号車がどこにいるか検索」
 
質問3「一つの検索で全体の表を開かずに検索」
 
これが今回の内容です。

回答
投稿日時: 17/10/14 23:06:20
投稿者: sy

すいません、ちょっと文章に矛盾があったので差し替えました。
 

もこもこ さんの引用:
今回の目的は忘れ物なんです。

問い合わせに、自分が乗ったのが急行なのか各停なのか分からないと言う人は多いんですか?
多いなら各停〜急行まですべてのリストを検索する必要があるから、ちょっと手間ですけど、
(最大で各停・準急・区間急行・急行とからへんの4区分くらいはあるかな?)
別ければ追い越し現象は無くなるので、検索も簡単になります。
どうせリストは複数あるんですからリスト選択の条件は必要ですし、
入力規則のリストにすれば選択は視覚的に分かりやすく出来ます。
リストを路線別・区分別に分ける事は出来ないですか?
 
 
もこもこ さんの引用:
質問1「前後の号車の検索」
質問2「その号車がどこにいるか検索」
質問3「一つの検索で全体の表を開かずに検索」
これが今回の内容です。

路線別・区分別のリストにするなら質問1〜3全て大して難しい事ではありません。
 
 
質問1・2は路線別・区分別のリストなら、私が提示した式を変更なしで使えます。
質問3も「路線・区分」の名称のシート名でリストを別けていれば、切り替え用シートに読み込むようにすれば式を変更する必要も無く、読込みも簡単な式で出来ます。

投稿日時: 17/10/15 01:15:51
投稿者: もこもこ

んなっと様
 
遅くなりました。
最終形でやってみました。
 
調べたかったことができました。
順番まで変って表示されるとは思ってもみませんでした。
すごいって驚いています。
 
ありがとうございました。
 
 
sy様
 
リストを路線別・区分別に分ける事は出来ないですか?
 
今まで、やったことがありませんでした。
今あるものを加工することはNGだったのでその発想はありませんでした。
確かに、路線別に分ければ追い越すことはなくなります。
路線別に加工して作ってみます。(この作業は少し時間かかります)
 
遅くまでお付き合いいただきありがとうございました。
 
sys様の式はまだ試していません。
あす、仕事が明けたらやってみます。(夜になるかと思います)
 
 
自分もすっかり夢中になってこんな時間になってしまいました。
ありがとうございました。おやすみなさい。
 

回答
投稿日時: 17/10/15 02:49:04
投稿者: sy

ちょっと別案です。
要件が忘れ物の号車の特定と、現在何処を走っているかを知りたいと言う事なので、
以下のようなレイアウトの方が、分かりやすいんじゃないかなぁと思いました。
此方の案では、各停と急行などを別ける必要はありません。
発車時刻が急行が追い抜かしたりしても問題ありません。
 
検索シート
各行とも横幅は此処に掲載出来る列数しか掲載していませんが、実際の横幅に合わせて下さい。
式は最高2分に1本電車が来ることを想定して、400列分を参照しています。

     |[A]     |[B]     |[C]     |[D] |[E]     |[F] |[G] |[H] |[I] |[J] |[K]|[L]|[M]|[N]|[O]
 [1] |路線    |駅名    |発車時刻|    |        |   1|   2|   3|   4|   5|  6|  7|  8|  9| 10
 [2] |路線1   |は駅    |6:41    |    |発車時刻|6:26|6:29|6:41|6:50|7:11|   |   |   |   |   
 [3] |        |        |        |    |号車    |2号 |1号 |3号 |1号 |2号 |   |   |   |   |   
 [4] |        |現在時刻|2:17    |    |現在駅  |車庫|車庫|車庫|車庫|車庫|   |   |   |   |   
 [5] |        |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [6] |号車    |1号     |2号     |3号 |1号     |4号 |2号 |    |    |    |   |   |   |   |   
 [7] |時刻    |6:29    |6:26    |6:41|6:50    |    |7:11|    |    |    |   |   |   |   |   
 [8] |        |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [9] |        |        |        |    |列番号  |   2|   1|   3|   4|   6|   |   |   |   |   
 [10]|        |        |        |    |行番号  |   3|    |    |    |    |   |   |   |   |   
 [11]|        |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [12]|リスト名|        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [13]|路線1   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [14]|路線2   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [15]|路線3   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [16]|路線4   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [17]|路線5   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [18]|路線6   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [19]|路線7   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [20]|路線8   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [21]|路線9   |        |        |    |        |    |    |    |    |    |   |   |   |   |   
 [22]|路線10  |        |        |    |        |    |    |    |    |    |   |   |   |   |   

 
入力規則のリストの数式欄の式
A2 =OFFSET(A13,0,0,COUNTA(A13:A97),1)
B2 =OFFSET(表示!A2,0,0,COUNTIF(表示!A1:A100,"*駅"),1)
C2の時刻はお客さんから言われた通りに入力した方が良いと思います。

 
条件付き書式
適用範囲:F1〜AI4
(一応2分に1本で1時間分の30列、実際の最大列数に合わせて下さい)
条件式1 =TEXT(F$2,"h:mm")-$C$2=0
(指定時刻丁度の時刻の号車:色はお好きな色で)
条件式2 =AGGREGATE(14,6,($F$2:$O$2<$C$2)*$F$2:$O$2,1)=F$2
条件式3 =AGGREGATE(15,6,1/($F$2:$O$2>$C$2)*$F$2:$O$2,1)=F$2
(条件2と3は指定時刻から最も近い前後の号車:丁度の色とは変えた方が良いと思います)

 
セル内の数式(範囲は2分に1本に合わせているので、実際の最大列数に合わせて下さい)
C4 =TEXT(NOW()-TODAY(),"h:mm")*1
F2 =IFERROR(SMALL($B7:$OK7,COLUMN(A1)),"")
F3 =IF(F2="","",INDEX(表示!$B1:$OK1,F9))
F4 =IF(F2="","",IFERROR(LOOKUP($C4,INDEX(表示!$B2:$OK100,0,F9),表示!$A2:$A100),"車庫"))
F2〜F4をAI列までフィルコピー
B6 =表示!B1&""
B7 =IF(AND(INDEX(表示!B2:B100,$F10)>=$C2-"0:30",INDEX(表示!B2:B100,$F10)<=$C2+"0:30"),INDEX(表示!B2:B100,$F10),"")
B6〜B7をOK列までフィルコピー
F9 =IF(F2="","",MATCH(F2,INDEX(表示!$B2:$OK100,$F$10,0),0))
AI列までフィルコピー
F10 =MATCH(B2,表示!A2:A100,0)

 
表示シート(此方も実際の1日の最大本数に合わせた列数と最大駅数に合わせた行数にして下さい)
B1 =INDIRECT(検索!$A$2&"!RC",0)&""
OK列までフィルコピー
A2 =INDIRECT(検索!$A$2&"!RC",0)&""
100行目までフィルコピー
B2 =IFERROR(1/(1/INDIRECT(検索!$A$2&"!RC",0)),"")
OK列と100行目までフィルコピー

 
路線1〜路線n までのシート
A列に駅名
1行目に号車
で始まる時刻

回答
投稿日時: 17/10/15 15:50:08
投稿者: んなっと

うっかりしていました。
始発駅が時刻表の一番上とは限らないので、2行目を追加して始発時間を並べるように修正します。
 
時刻表シートをすべてグループ化した状態で2行目を追加し、
B2
=INDEX(B3:B1000,MATCH(TRUE,INDEX(ISNUMBER(B3:B1000),),0))
右方向・→
 
以下のシート名は例です。
 
●シート名:京王線新宿上り
 
   A   B   C   D   E   F   G
1     1号  2号  3号  1号  4号  2号
2    6:10 6:24 6:37 6:48 6:59 7:11 ←追加
3 い駅 6:10 6:24 6:37 6:48 6:59 
4 ろ駅 6:27    6:39 6:49 7:12   
5 は駅 6:29 6:26 6:41 6:50 7:14 7:11
6 に駅 6:31    6:43 6:51 7:16   
7 ほ駅 6:33 6:28    6:52 7:18 7:13
8 へ駅 6:35       6:53 7:20   
9 と駅 6:37 6:30    6:54 7:22 7:15
 
 
 
●シート名:中央線高尾下り
http://ekikara.jp/newdata/line/1301062/down1_3_sat.htm
から引用しました。
 
       A   B   C   D   E   F   G
 1       725T 741T 703T 703H 761T 717H
 2       7:32 7:37 7:40 7:44 7:46 7:51 ←追加
 3    東京 7:32 7:37 7:40    7:46 7:51
 4    神田 7:34 7:39 7:42    7:48 7:53
 5  御茶ノ水 7:37 7:41 7:44    7:51 7:56
 6   四ツ谷 7:41 7:46 7:49    7:55 8:00
 7    新宿 7:47 7:51 7:54 7:44 8:03 8:06
 8    中野 7:51 7:55 7:59 7:49 8:08 8:10
 9   高円寺                  
10  阿佐ヶ谷                  
11    荻窪 7:56    8:03    8:12 8:14
12   西荻窪                  
13   吉祥寺 8:00    8:07    8:16 8:18
14    三鷹 8:06 8:04 8:11 7:58 8:19 8:25
15   武蔵境 8:08    8:14    8:21 8:27
16  東小金井 8:10    8:16    8:23 8:30
17 武蔵小金井       8:19    8:26 8:33
18   国分寺    8:11 8:24 8:05 8:33 8:41
 
●京下り
  
   A   B   C   D   E   F   G
1     1号  2号  3号  1号  4号  2号
2    8:10 8:24 8:37 8:48 8:59 9:09 ←追加
3 と駅 8:10 8:24 8:37 8:48 8:59 9:09
4 へ駅 8:27    8:39 8:49 9:12   
5 ほ駅 8:29 8:26 8:41 8:50 9:14 9:11
6 に駅 8:31    8:43 8:51 9:16   
7 は駅 8:33 8:28    8:52 9:18 9:13
8 ろ駅 8:35    2:00 8:53 9:20   
9 い駅 8:37 8:30 2:00 8:54 9:22 9:15
 
●中上り
  
       A   B   C   D   E   F   G
 1        725T  741T  703T  703H  761T  717H
 2        9:32  9:37  9:40  9:44  9:46  9:51 ←追加
 3   国分寺  9:32  9:37  9:40     9:46  9:51
 4 武蔵小金井  9:34  9:39  9:42     9:48  9:53
 5  東小金井  9:37  9:41  9:44     9:51  9:56
 6   武蔵境  9:41  9:46  9:49     9:55 10:00
 7    三鷹  9:47  9:51  9:54  9:44 10:03 10:06
 8   吉祥寺  9:51  9:55  9:59  9:49 10:08 10:10
 9   西荻窪                     
10    荻窪                     
11  阿佐ヶ谷  9:56     10:03     10:12 10:14
12   高円寺                     
13    中野 10:00     10:07     10:16 10:18
14    新宿 10:06 10:04 10:11  9:58 10:19 10:25
15   四ツ谷 10:08     10:14     10:21 10:27
16  御茶ノ水 10:10     10:16     10:23 10:30
17    神田        10:19     10:26 10:33
18    東京     10:11 10:24 10:05 10:33 10:41
 
●検索シート
 
・駅名から車両検索
 
G列から右に検索駅の発車時刻を取り出して、そこから検索。
 
         A   B   C   D   E   F   G   H   I   J   K   L
 4    シート名 駅名 時刻  前  中  後                  
 5 京王線新宿上り は駅 6:24        2号 6:29 6:26 6:41 6:50 7:14 7:11
 6 京王線新宿上り は駅 6:25        2号 6:29 6:26 6:41 6:50 7:14 7:11
 7 京王線新宿上り は駅 6:26     2号  1号 6:29 6:26 6:41 6:50 7:14 7:11
 8 京王線新宿上り は駅 6:27     2号  1号 6:29 6:26 6:41 6:50 7:14 7:11
 9 京王線新宿上り は駅 6:28     2号  1号 6:29 6:26 6:41 6:50 7:14 7:11
10 京王線新宿上り は駅 6:29  2号  1号  3号 6:29 6:26 6:41 6:50 7:14 7:11
11 京王線新宿上り は駅 6:30  2号  1号  3号 6:29 6:26 6:41 6:50 7:14 7:11
12 京王線新宿上り は駅 6:31  2号  1号  3号 6:29 6:26 6:41 6:50 7:14 7:11
13 京王線新宿上り は駅 6:50  3号  1号  2号 6:29 6:26 6:41 6:50 7:14 7:11
14 京王線新宿上り は駅 7:13  1号  2号  4号 6:29 6:26 6:41 6:50 7:14 7:11
15 京王線新宿上り は駅 7:14  2号  4号    6:29 6:26 6:41 6:50 7:14 7:11
16 京王線新宿上り は駅 7:15  2号  4号    6:29 6:26 6:41 6:50 7:14 7:11
17 中央線高尾下り 三鷹 7:56       703H 8:06 8:04 8:11 7:58 8:19 8:25
18 中央線高尾下り 三鷹 7:57       703H 8:06 8:04 8:11 7:58 8:19 8:25
19 中央線高尾下り 三鷹 7:58    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
20 中央線高尾下り 三鷹 7:59    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
21 中央線高尾下り 三鷹 8:00    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
22 中央線高尾下り 三鷹 8:01    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
23 中央線高尾下り 三鷹 8:02    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
24 中央線高尾下り 三鷹 8:03    703H 741T 8:06 8:04 8:11 7:58 8:19 8:25
25 中央線高尾下り 三鷹 8:04 703H 741T 725T 8:06 8:04 8:11 7:58 8:19 8:25
26 中央線高尾下り 三鷹 8:05 703H 741T 725T 8:06 8:04 8:11 7:58 8:19 8:25
27 中央線高尾下り 三鷹 8:06 741T 725T 703T 8:06 8:04 8:11 7:58 8:19 8:25
28 中央線高尾下り 三鷹 8:07 741T 725T 703T 8:06 8:04 8:11 7:58 8:19 8:25
 
D5
=IFERROR(INDEX(INDIRECT("'"&$A5&"'!1:1"),MOD(AGGREGATE(14,6,COLUMN(B:AU)/1000+TEXT(G5:AZ5,"[h]mm;;")/(G5:AZ5<=C5),2),1)*1000),"")
下方向・↓
E5
=IFERROR(INDEX(INDIRECT("'"&$A5&"'!1:1"),MOD(AGGREGATE(14,6,COLUMN(B:AU)/1000+TEXT(G5:AZ5,"[h]mm;;")/(G5:AZ5<=C5),1),1)*1000),"")
下方向・↓
F5
=IFERROR(INDEX(INDIRECT("'"&$A5&"'!1:1"),MOD(AGGREGATE(15,6,COLUMN(B:AU)/1000+TEXT(G5:AZ5/(LEN(G5:AZ5)>0),"[h]mm;;")/(G5:AZ5>C5),1),1)*1000),"")
下方向・↓
G5
=IFERROR(TEXT(VLOOKUP($B5,INDIRECT("'"&$A5&"'!A:AU"),COLUMN(B:B),FALSE),"[h]:mm;;")*1,"")
右方向・→下方向・↓
 
 
・車両から駅名検索
 
E30から右にシート名一覧を入力しておいて...
 
    A   B    C        D        E        F    G    H
30 車両 時刻        シート名 中央線高尾下り 京王線新宿上り 中上り 京下り
31  2号 6:23                                    
32  2号 6:24  い駅 京王線新宿上り              6:24        
33  2号 6:25  い駅 京王線新宿上り              6:24        
34  2号 6:26  は駅 京王線新宿上り              6:24        
35  2号 6:27  は駅 京王線新宿上り              6:24        
36  2号 6:28  ほ駅 京王線新宿上り              6:24        
37  2号 6:29  ほ駅 京王線新宿上り              6:24        
38  2号 6:30  と駅 京王線新宿上り              6:24        
39  2号 9:12  ほ駅     京下り              7:11      9:09
40  2号 9:13  は駅     京下り              7:11      9:09
41  2号 9:14  は駅     京下り              7:11      9:09
42  2号 9:15  い駅     京下り              7:11      9:09
43 703T 7:58  新宿 中央線高尾下り      7:40                
44 703T 7:59  中野 中央線高尾下り      7:40                
45 703T 8:00  中野 中央線高尾下り      7:40                
46 703T 8:01  中野 中央線高尾下り      7:40                
47 703T 8:02  中野 中央線高尾下り      7:40                
48 703T 8:03  荻窪 中央線高尾下り      7:40                
49 703T 8:04  荻窪 中央線高尾下り      7:40                
50 703T 8:05  荻窪 中央線高尾下り      7:40                
51 703T 8:06  荻窪 中央線高尾下り      7:40                
52 703T 8:07 吉祥寺 中央線高尾下り      7:40                
53 703T 8:08 吉祥寺 中央線高尾下り      7:40                
54 703T 9:58  三鷹     中上り      7:40          9:40    
55 703T 9:59 吉祥寺     中上り      7:40          9:40    
 
C31
=IFERROR(LOOKUP(B31,INDEX(INDIRECT("'"&$D31&"'!B3:AU1000"),,MATCH(B31,INDEX(INDIRECT("'"&$D31&"'!B2:AU2")/(INDIRECT("'"&$D31&"'!B1:AU1")=$A31),))),INDIRECT("'"&$D31&"'!A3:A1000")),"")
下方向・↓
D31
=IFERROR(LOOKUP(MAX(E31:AU31),E31:AU31,E$30:AU$30),"")
下方向・↓
E31
=IFERROR(LOOKUP($B31,INDIRECT("'"&E$30&"'!B2:AU2")/(INDIRECT("'"&E$30&"'!B1:AU1")=$A31)),"")
右方向・→下方向・↓
 
 
※元の時刻表に0:00や0:01があるときは、24:00や24:01に変更してください。
検索シートは、テスト用にそれぞれ何十行も下にコピーしています。
無駄にコピーされた行は、最後に削除してください。

投稿日時: 17/10/15 22:20:19
投稿者: もこもこ

sy 様
 
お返事ありがとうございます。
 
式を途中まで試しました。
この検索方法は目視できるので(検索時間をみながら)とても便利です。
 
 
ここで躓いています。
 
最小シート構成は「表示(時刻表)」「検索シート」「表示シート」でしょうか?
 
表示シート(此方も実際の1日の最大本数に合わせた列数と最大駅数に合わせた行数にして下さい)
B1 =INDIRECT(検索!$A$2&"!RC",0)&""
OK列までフィルコピー
A2 =INDIRECT(検索!$A$2&"!RC",0)&""
100行目までフィルコピー
B2 =IFERROR(1/(1/INDIRECT(検索!$A$2&"!RC",0)),"")
OK列と100行目までフィルコピー
 
リスト名(A12)
A12〜のリスト名は「シート名」?
勘違いしていたらすいません
 
私では少し時間がかかると思われます。(汗)
 
 
んなっと 様
ありがとうございます。
 
まだ、とても追いつきません。(汗)
はい、上りに関しては始発地がバラバラのため、んなっと様のいう通りです。
 
時刻表に関しては1路線4シート構成になっております。
平日・休日の上り、下り(合計4枚)
 
遅くなると申し訳ないので進捗状況です。
 
  
 
 
 

回答
投稿日時: 17/10/15 23:44:29
投稿者: sy

もこもこ さんの引用:
この検索方法は目視できるので(検索時間をみながら)とても便利です。

今回の要件でしたら、この別案が一番見やすく、前後の車両と現在の居場所も、一望できるのでお勧めです。
 
目的が分かれば、こう言う別案も出てきやすいので、次回から別の質問などをする時にも、初めに目的を伝えるようにした方が良いですよ。
 
 
所々説明が抜けていました。
申し訳ありません。
もこもこ さんの引用:
ここで躓いています。
最小シート構成は「表示(時刻表)」「検索シート」「表示シート」でしょうか?

はい。
検索シートが実際に検索して結果を表示させるシートです。
表示シートは検索シートが参照する各時刻表を切り替える為のシートです。
表示(時刻表)が、各時刻表ですね。
 
 
もこもこ さんの引用:
リスト名(A12)
A12〜のリスト名は「シート名」?
勘違いしていたらすいません

此方も説明忘れてました。
すいません。
A13以降に各時刻表のシート名を登録して下さい。
入力規則は自動で認識するような数式にしてるので、シート数は増減しても問題ありません。
EXCELが勝手にプルダウン時のリスト数は調整します。
 
 
もこもこ さんの引用:
表示シート(此方も実際の1日の最大本数に合わせた列数と最大駅数に合わせた行数にして下さい)
B1 =INDIRECT(検索!$A$2&"!RC",0)&""
OK列までフィルコピー
A2 =INDIRECT(検索!$A$2&"!RC",0)&""
100行目までフィルコピー
B2 =IFERROR(1/(1/INDIRECT(検索!$A$2&"!RC",0)),"")
OK列と100行目までフィルコピー

24:00以降が0:00表記で登録されている可能性を考慮していませんでした。
B2の式を以下に変更して下さい。
(各時刻表の方は24:00表記でも0:00表記どちらでも構いません、自動で判別します。)
B2 =IFERROR(IF(1/(1/INDIRECT(検索!$A$2&"!"&"RC",0))<"4:00"*1,INDIRECT(検索!$A$2&"!"&"RC",0)+"24:00",INDIRECT(検索!$A$2&"!"&"RC",0)),"")
フィルコピーをOK列にしてるのは、一番列数の多い時刻表が、都市部で1日分全ての車両を表示していたら、それくらいになるだろうと予想しての列幅です。
(んなっとさんの提示のリンク先でも、1日分全て一つの時刻表に表示させると330以上の本数、LZ列くらいまでになってますよね。)
もっと少なければ、それに合わせて貰えばそれだけ処理も早くなります。
ただし、全ての時刻表の中で一番列数の多いシートの列幅に合わせてフィルコピーして下さい。
行も駅数が一番多い時刻表の行数に合わせて下さい。
(最大列数の時刻表と違う時刻表でも良いです。)
 
 
後検索シートの条件付き書式の、条件2・3の式を以下に変更お願いします。
条件式2 =AGGREGATE(14,6,($F$2:$AI$2<$C$2)*$F$2:$AI$2,1)=F$2
条件式3 =AGGREGATE(15,6,1/($F$2:$AI$2>$C$2)*$F$2:$AI$2,1)=F$2
(適用範囲が30本のAI列までなのに、式がO列までになってました。)
 
 
それとC2に入力する0:00以降の時刻も、24:00と0:00どちらの入力にも対応できるようにする為に、何処でも良いんですけど仮にF11に以下の式を追加して頂いて、
F11 =IF(C2="","",IF(C2<"4:00"*1,C2+"24:00",C2))
B7の式を以下に修正して下さい。
B7 =IF(AND(INDEX(表示!B2:B100,$F10)>=$F11-"0:30",INDEX(表示!B2:B100,$F10)<=$F11+"0:30"),INDEX(表示!B2:B100,$F10),"")
B7の式は、C2に入力した時刻の前後30分の車両を全て表示するようにしてるので、そんなに要らない場合は、-"0:30"や+"0:30"の部分を、例えば15分にするなら"0:15"と変更して下さい。
 
 
最後に検索シートの数式の参照範囲は全てOK列と100行までになってるので、もこもこさんの方で調整後の表示シートの列数や行数に合わせて下さい。
 
 
 
此処まで書いたんですけど、まぁ分かりにくかったら、一番車両数の多い時刻表の列数と、一番駅数の多い時刻表の行数だけ提示して頂けたら、明日仕事から帰ったら式の範囲も修正した物を再アップしますよ。

回答
投稿日時: 17/10/16 16:50:27
投稿者: んなっと

引用:
時刻表に関しては1路線4シート構成になっております。
平日・休日の上り、下り

「上り」「下り」の2シートから車両検索するだけなら、少し簡単になります。
時刻表の各シートの2行目に行挿入する必要はありません。
複数シートからの車両位置検索が不要なら無視してくださいね。
 
     A    B    C   D    E   F   G   H   I   J   K
 4 シート  駅名 発車時         1号  2号  3号  1号  4号  2号
 5 京上り  は駅  6:30        6:29 6:26 6:41 6:50 7:14 7:11
 6         現在時                         
 7          8:20                         
 8  候補 シート  駅名 発車                      
 9   2号 京上り  と駅 7:15 京上り    6:30          7:15
10                京下り                  
11   1号 京下り  と駅 8:10 京上り 6:37       6:54      
12                京下り 8:10               
13   3号 京上り  に駅 6:43 京上り       6:43         
14                京下り                  
 
・F列から右に指定駅の発車時刻
F4
=IFERROR(INDEX(INDIRECT("'"&$A5&"'!1:1"),COLUMN(B:B)),"")&""
右方向・→
F5
=IFERROR(TEXT(VLOOKUP($B5,INDIRECT("'"&$A5&"'!A:EZ"),COLUMN(B:B),FALSE),"[h]:mm;;")*1,"")
右方向・→
 
・前、中、後候補
A9
=IFERROR(INDEX($4:$4,MOD(AGGREGATE(14,6,COLUMN(F:EZ)/1000+TEXT(F$5:EZ$5,"[h]mm;;")/(F$5:EZ$5<=C$5),2),1)*1000),"")
A11
=IFERROR(INDEX($4:$4,MOD(AGGREGATE(14,6,COLUMN(F:EZ)/1000+TEXT(F$5:EZ$5,"[h]mm;;")/(F$5:EZ$5<=C$5),1),1)*1000),"")
A13
=IFERROR(INDEX($4:$4,MOD(AGGREGATE(15,6,COLUMN(F:EZ)/1000+TEXT(F$5:EZ$5,"[h]mm;;")/(F$5:EZ$5>=C$5),1),1)*1000),"")
 
・B〜E列に車両位置検索結果
B9
=IF(IFERROR(LOOKUP($C$7,F10:EZ10)=D9,),E10,E9)
C9
=IFERROR(LOOKUP(C$7,INDEX(INDIRECT("'"&B9&"'!B2:EZ1000"),,MATCH(C$7,INDEX(F9:EZ10,MATCH(B9,E9:E10,0),))),INDIRECT("'"&B9&"'!A2:A1000")),"")
D9
=MAX(F9:EZ10)
E9
=$A$5
E10
=SUBSTITUTE($A$5,IF(COUNTIF($A$5,"*上り*"),"上り","下り"),IF(COUNTIF($A$5,"*上り*"),"下り","上り"))
B9:E10を選択してコピー
→B11:E14を選択して貼り付け
 
・F列から右に、各候補車両の現在時刻を超えない最大時刻
F9
=IF(INDEX(INDIRECT("'"&$E9&"'!1:1"),COLUMN(B:B))=LOOKUP("ーー",$A$9:$A9),IFERROR(LOOKUP($C$7,INDEX(INDIRECT("'"&$E9&"'!2:1000"),,COLUMN(B:B))),""),"")
右方向・→下方向・↓

投稿日時: 17/10/16 16:55:49
投稿者: もこもこ

んなっと 様
 
お世話になっております。
 
まったく同じシートを作成し試してました。
シート名は例と言うことなのですが、間違えそうなので同じものを作ってみました。
(2行目は追加して)
 
京王線新宿上り
中央線高尾下り
京下り
中上り
検索シート
 
G列から右に検索駅の発車時刻を取り出して、そこから検索。
 
 
ここで躓いております。
 
 
取り出すことが出来ないので、とりあえず検索シートG列より右の列を手動で入力したのですが、
D、E、F(数式を入力)空白のままで表示されません。
 
なにが足らないのか?わかりませんでした。
 
 
あ!
 
んなっと 様
 
返信いただいていたのですね。
ありがとうございます。
 
時間かかりますが一つづつこなしてみます。

回答
投稿日時: 17/10/17 10:38:16
投稿者: んなっと

最後の私の書き込み、一部修正。
 
A13
=IFERROR(INDEX($4:$4,MOD(AGGREGATE(15,6,COLUMN(F:EZ)/1000+TEXT(F$5:EZ$5,"[h]mm;;")/(F$5:EZ$5>=C$5),1),1)*1000),"")
  ↓
A13
=IFERROR(INDEX($4:$4,MOD(AGGREGATE(15,6,COLUMN(F:EZ)/1000+TEXT(F$5:EZ$5,"[h]mm;;")/(F$5:EZ$5>C$5),1),1)*1000),"")

投稿日時: 17/10/17 12:28:44
投稿者: もこもこ

sy 様
 
いろいろ試してみているのですが、どうしてもわからないことがありまして、
 
検索シートB2
B2 =OFFSET(表示!A2,0,0,COUNTIF(表示!A1:A100,"*駅"),1)
 
説明不足でした。
実際に○○駅と付くのは数カ所しかないです。
(そもそも、どうして駅名が表示されるのか?わかりません)
自分の認識ですと「表示(時刻表)」のA2列駅が何番目にあるのか?検索だと思うんですが、、、
 
 
 
sy様 引用
 
「A13以降に各時刻表のシート名を登録して下さい。
 入力規則は自動で認識するような数式にしてるので、シート数は増減しても問題ありません。
EXCELが勝手にプルダウン時のリスト数は調整します。 」
 
 
これがA13以降にシートを(2個以上)登録して入力すると「A2」がエラーになります。
 
A13行目に1つ入力した「時刻表」は参照しました。(複数入れるとエラーに)
 
 
 
 
んなっと様
 
ありがとうございます。
 
少々お時間ください。(汗)

回答
投稿日時: 17/10/17 20:19:57
投稿者: んなっと

マクロを最初一回だけ使って
検索シート追加→数式を設定
を自動で行うなら...
(マクロを有効にする方法はご自分で調べてください。)
 
 Alt+F11同時押し
→挿入 標準モジュール
→右にできた白くて大きいところに以下のコード貼り付け
 
Sub test1()
  Dim Sh As Worksheet, mySh As Worksheet
  Dim i As Long
  Application.ScreenUpdating = False
  Set mySh = Sheets.Add(before:=Sheets(1))
  i = 30
  For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name Like "*[上下]り*" Then
      mySh.Cells(i, 1).Value = Sh.Name
      i = i + 1
    End If
  Next
  Range("A4").Value = "シート"
  Range("B4").Value = "駅名"
  Range("C4").Value = "発車時"
  Range("A5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=OFFSET(A30,,,COUNTA(A30:A1000))"
  Range("A5").Value = Range("A30").Value
  Range("B5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=INDIRECT(""'""&A5&""'!A2:A""&COUNTA(INDIRECT(""'""&A5&""'!A:A""))+1)"
  Range("C5").Value = TimeValue("6:29")
  Range("C5").NumberFormatLocal = "[h]:mm"
  Range("C6").Value = "現在時"
  Range("C7").Value = TimeValue("8:20")
  Range("C7").NumberFormatLocal = "[h]:mm"
  Range("A8").Value = "候補"
  Range("B8").Value = "シート"
  Range("C8").Value = "駅名"
  Range("D8").Value = "発車"
  Range("F4").FormulaR1C1 = _
      "=IFERROR(INDEX(INDIRECT(""'""&R[1]C1&""'!1:1""),COLUMN(C[-4])),"""")&"""""
  Range("F5").FormulaR1C1 = _
      "=IFERROR(TEXT(VLOOKUP(RC2,INDIRECT(""'""&RC1&""'!A:EZ""),COLUMN(C[-4]),FALSE),""[h]:mm;;"")*1,"""")"
  Range("F5").NumberFormatLocal = "[h]:mm"
  Range("F4:F5").AutoFill Destination:=Range("F4:EZ5"), Type:=xlFillDefault
  Range("A9").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=R5C[2]),2),1)*1000),"""")"
  Range("A11").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=R5C[2]),1),1)*1000),"""")"
  Range("A13").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(15,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]>R5C[2]),1),1)*1000),"""")"
  Range("B9").FormulaR1C1 = _
      "=IF(RC[-1]="""","""",IF(IFERROR(LOOKUP(R7C3,R[1]C[4]:R[1]C[154])=RC[2],),R[1]C[3],RC[3]))"
  Range("C9").FormulaR1C1 = _
      "=IF(RC[-2]="""","""",IFERROR(LOOKUP(R7C,TEXT(INDEX(INDIRECT(""'""&RC[-1]&""'!B2:EZ1000""),,MATCH(R7C,INDEX(RC[3]:R[1]C[153],MATCH(RC[-1],RC[2]:R[1]C[2],0),))),""[>0.166][h]:mm;"")*1,INDIRECT(""'""&RC[-1]&""'!A2:A1000"")),""""))"
  Range("D9").FormulaR1C1 = "=IF(RC[-3]="""","""",MAX(RC[2]:R[1]C[152]))"
  Range("D9").NumberFormatLocal = "[h]:mm"
  Range("E9").FormulaR1C1 = "=IF(RC[-4]="""","""",R5C1)"
  Range("E10").FormulaR1C1 = _
      "=IF(R[-1]C[-4]="""","""",SUBSTITUTE(R5C1,IF(COUNTIF(R5C1,""*上り*""),""上り"",""下り""),IF(COUNTIF(R5C1,""*上り*""),""下り"",""上り"")))"
  Range("B9:E10").Copy Destination:=Range("B11:D12")
  Range("B9:E10").Copy Destination:=Range("B13:D14")
  Range("F9").FormulaR1C1 = _
      "=IF(RC5="""","""",IF(INDEX(INDIRECT(""'""&RC5&""'!1:1""),COLUMN(C[-4]))=LOOKUP(""ーー"",R9C1:RC1),IFERROR(LOOKUP(R7C3,TEXT(INDEX(INDIRECT(""'""&RC5&""'!2:1000""),,COLUMN(C[-4])),""[>0.166][h]:mm;"")*1),""""),""""))"
  Range("F9").NumberFormatLocal = "[h]:mm"
  Range("F9").Copy Destination:=Range("F9:EZ14")
  Cells.EntireColumn.AutoFit
  Application.ScreenUpdating = True
End Sub
 
→実行 Sub/ユーザー 〜 の実行 上のtest1を実行
→いろいろ試す
→*.xlsmで保存
→必要ならマクロを無効に戻しておく
   
※複数シートからの車両位置検索の場合0:00が悩みの種なので、
0:00〜3:59は除外してみました。力不足でごめんなさい。

回答
投稿日時: 17/10/18 00:37:09
投稿者: sy

もこもこ さんの引用:
検索シートB2
B2 =OFFSET(表示!A2,0,0,COUNTIF(表示!A1:A100,"*駅"),1)
説明不足でした。
実際に○○駅と付くのは数カ所しかないです。

すいません。
最後に「駅」がつかないのは、想定してませんでした。
 
 
もこもこ さんの引用:
これがA13以降にシートを(2個以上)登録して入力すると「A2」がエラーになります。
A13行目に1つ入力した「時刻表」は参照しました。(複数入れるとエラーに)

此方はA13以降が未入力か、数式などでセルにエラーが入っているとかでないと、A2はエラーにならないような数式なので、もこもこさんのPC画面を見る事が出来ないので、ちょっと原因が分かりません。
申し訳ありません。
 
  
後此方も申し訳ありません。
表示シートは必要なかったです。
検索シートだけで十分でした、OK列100行ほどの大きな時刻表相手でも一瞬で読込めました。
 
 
 
それと私も仕事帰り、んなっとさんのようにマクロで設定する方向で考えてましたのでアップします。
  
前準備として、実際の元の時刻表のファイルを適当な名前で保存して(検索シートや表示シートの無いファイルです)そのファイルを開いて下さい。
 
注意事項として、コードを実行すると、全てのシート名がA13以降に登録されます。
そして右端のシート名をA2セルに入力するので(これは入力規則を設定する時には、OFFSETやINDIRECT関数を使っていると、予め参照元に値が入力されていないとエラーになるからです。)、右端のシートは必ず時刻表にして下さい。
出来れば実行時は時刻表以外のシートは削除して時刻表だけにして下さい。
(必要なら後から追加して下さい)
(逆に手入力時の入力ミス防止の為、時刻表は初めから全て揃っていた方が良いです。)

 
1、[Alt]+[f11]でVBEが起動します。
2、左上にシート名が出てきていると思いますので、どれでも良いのでダブルクリックします。
3、右に画面が出てくるので、そこに以下のコードを全て貼り付けます。
4、Sub 作成()の真横くらいをマウスでクリックします。(Sub 作成()の右にカーソルが点滅する筈です。)
5、その状態で[f5]を押します。
6、検索シートが出来ていたら成功です。(此方は表示シートは必要無いので作られません。)
7、最後に貼り付けたコードを全て選択して、削除します。
   (XLSXでメッセージを無視して保存しても勝手に削除されます)

 
以下コードです。
 
 
Sub 作成()
    Dim shF As Worksheet
    Dim i As Long

    On Error Resume Next
    Set shF = Sheets("検索")
    If shF Is Nothing Then
        Sheets.Add Sheets(1)
        Sheets(1).Name = "検索"
    End If
    On Error GoTo 0
    Set shF = Sheets("検索")

    With shF
        .Cells.Clear
        .Range("A1").Value = "シート選択"
        .Range("B1").Value = "駅選択"
        .Range("C1").Value = "発車時刻"
        .Range("E2").Value = "発車時刻"
        .Range("E3").Value = "車両名"
        .Range("E4").Value = "現在駅"
        .Range("A6").Value = "車両名"
        .Range("A7").Value = "発車時刻"
        .Range("E10").Value = "列番号"
        .Range("E11").Value = "発車時刻"
        .Range("E12").Value = "対象行"
        .Range("E13").Value = "行数"
        .Range("A12").Value = "シート名"
        .Range("F1:AI1").Formula = "=COLUMN(A1)"
        .Range("C2,C4,F2:AI2,F11,B7:OK8").NumberFormatLocal = "[h]:mm"
        .Range("C4").Formula = "=TEXT(IF(NOW()-TODAY()<""4:00""*1,NOW()-TODAY()+1,NOW()-TODAY()),""[h]:mm"")*1"
        .Range("F2:AI2").Formula = "=IFERROR(SMALL($B7:$OK7,COLUMN(A1)),"""")"
        .Range("F3:AI3").Formula = "=IF(F2="""","""",INDEX(6:6,F10))"
        .Range("F4:AI4").Formula = "=IFERROR(IF(F2="""","""",INDIRECT($A2&""!A""&AGGREGATE(14,6,1/((INDIRECT($A2&""!R1C""&F10&"":R100C""&F10,0)<>"""")*((INDIRECT($A2&""!R1C""&F10&"":R100C""&F10,0)<""4:00""*1)*(INDIRECT($A2&""!R1C""&F10&"":R100C""&F10,0)<=$C4-""23:59:58"")+(INDIRECT($A2&""!R1C""&F10&"":R100C""&F10,0)>=""4:00""*1)*(INDIRECT($A2&""!R1C""&F10&"":R100C""&F10,0)<=$C4)))*ROW(1:100),1))),""車庫"")"
        .Range("B6:OK6").Formula = "=IFERROR(INDIRECT($A2&""!R1C"",0)&"""","""")"
        .Range("B7:OK7").Formula = "=IFERROR(IF(AND(B8>=$F11-""0:30"",B8<=$F11+""0:30""),B8,""""),"""")"
        .Range("B8:OK8").Formula = "=IFERROR(IF(1/(1/INDIRECT($A2&""!R""&$F12&""C"",0))<""4:00""*1,INDIRECT($A2&""!R""&$F12&""C"",0)+""24:00"",INDIRECT($A2&""!R""&$F12&""C"",0)),"""")"
        .Range("F10:AI10").Formula = "=IF(F2="""","""",MATCH(F2,$A7:$OK7,0))"
        .Range("F11").Formula = "=IF(C2="""","""",IF(C2<""4:00""*1,C2+""24:00"",C2))"
        .Range("F12").Formula = "=MATCH(B2,INDIRECT(A2&""!A1:A100""),0)"
        .Range("F13").Formula = "=COUNTA(INDIRECT(A2&""!A2:A100""))+1"

        For i = 2 To Sheets.Count
            .Range("A" & i + 11).Value = Sheets(i).Name
        Next i

        With .Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(A13,0,0,COUNTA(A13:A100),1)"
        End With

        .Range("A2").Value = Sheets(Sheets.Count).Name
        With .Range("B2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(INDIRECT(A2&""!A2""),0,0,F13,1)"
        End With
        .Range("A2").ClearContents

        With .Range("F1:AI4")
            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=AGGREGATE(15,6,1/($F$2:$AI$2>$F$11)*$F$2:$AI$2,1)=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258
            .FormatConditions(1).StopIfTrue = False

            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=AGGREGATE(14,6,($F$2:$AI$2<$F$11)*$F$2:$AI$2,1)=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258
            .FormatConditions(1).StopIfTrue = False

            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=TEXT(F$2,""[h]:mm"")-$F$11=0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 16247773
            .FormatConditions(1).StopIfTrue = False
        End With

    End With

End Sub

回答
投稿日時: 17/10/18 00:53:35
投稿者: sy

誤解が有ったらいけないので、補足です。
 

引用:
前準備として、実際の元の時刻表のファイルを適当な名前で保存して(検索シートや表示シートの無いファイルです)そのファイルを開いて下さい。

この文面は、必ず元の時刻表ファイルのバックアップを取ってから実際の時刻表のコピーのファイル(架空の時刻表では無いファイル)で実行して下さい。
と言う事です。
 

回答
投稿日時: 17/10/18 17:20:11
投稿者: んなっと

0:00と空白セルの区別が簡単にできるようになりました。
今度のtest2は0:00〜3:59にも対応していると思います。
 
Sub test2()
  Dim Sh As Worksheet, mySh As Worksheet
  Dim i As Long
  Application.ScreenUpdating = False
  Set mySh = Sheets.Add(before:=Sheets(1))
  i = 30
  For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name Like "*[上下]り*" Then
      mySh.Cells(i, 1).Value = Sh.Name
      i = i + 1
    End If
  Next
  Range("A4").Value = "シート"
  Range("B4").Value = "駅名"
  Range("C4").Value = "発車時"
  Range("A5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=OFFSET(A30,,,COUNTA(A30:A1000))"
  Range("A5").Value = Range("A30").Value
  Range("B5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=INDIRECT(""'""&A5&""'!A2:A""&COUNTA(INDIRECT(""'""&A5&""'!A:A""))+1)"
  Range("C5").Value = TimeValue("6:29")
  Range("C5").NumberFormatLocal = "[h]:mm"
  Range("C6").Value = "現在時"
  Range("C7").Value = TimeValue("8:20")
  Range("C7").NumberFormatLocal = "[h]:mm"
  Range("A8").Value = "候補"
  Range("B8").Value = "シート"
  Range("C8").Value = "駅名"
  Range("D8").Value = "発車"
  Range("F4").FormulaR1C1 = _
      "=IFERROR(INDEX(INDIRECT(""'""&R[1]C1&""'!1:1""),COLUMN(C[-4])),"""")&"""""
  Range("F5").FormulaR1C1 = _
      "=IFERROR(TEXT((VLOOKUP(RC2,INDIRECT(""'""&RC1&""'!A:EZ""),COLUMN(C[-4]),FALSE)&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,"""")"
  Range("F5").NumberFormatLocal = "h:mm"
  Range("F4:F5").AutoFill Destination:=Range("F4:EZ5"), Type:=xlFillDefault
  Range("A9").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=(R5C3&"""")+(R5C3-""4:""<0)),2),1)*1000),"""")"
  Range("A11").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=(R5C3&"""")+(R5C3-""4:""<0)),1),1)*1000),"""")"
  Range("A13").FormulaR1C1 = _
      "=IFERROR(INDEX(R4,MOD(AGGREGATE(15,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]>(R5C3&"""")+(R5C3-""4:""<0)),1),1)*1000),"""")"
  Range("B9").FormulaR1C1 = _
      "=IF(RC[-1]="""","""",IF(IFERROR(MAX(R[1]C[4]:R[1]C[154])=RC[2],),R[1]C[3],RC[3]))"
  Range("C9").FormulaR1C1 = _
      "=IF(RC[-2]="""","""",IFERROR(LOOKUP((R7C3&"""")+(R7C3-""4:""<0),TEXT((INDEX(INDIRECT(""'""&RC[-1]&""'!B2:EZ1000""),,MATCH(RC[1],INDEX(RC[3]:R[1]C[153],MATCH(RC[-1],RC[2]:R[1]C[2],0),),0))&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,INDIRECT(""'""&RC[-1]&""'!A2:A1000"")),""""))"
  Range("D9").FormulaR1C1 = "=IF(RC[-3]="""","""",MAX(RC[2]:R[1]C[152]))"
  Range("D9").NumberFormatLocal = "h:mm"
  Range("E9").FormulaR1C1 = "=IF(RC[-4]="""","""",R5C1)"
  Range("E10").FormulaR1C1 = _
      "=IF(R[-1]C[-4]="""","""",SUBSTITUTE(R5C1,IF(COUNTIF(R5C1,""*上り*""),""上り"",""下り""),IF(COUNTIF(R5C1,""*上り*""),""下り"",""上り"")))"
  Range("B9:E10").Copy Destination:=Range("B11:D12")
  Range("B9:E10").Copy Destination:=Range("B13:D14")
  Range("F9").FormulaR1C1 = _
      "=IF(RC5="""","""",IF(INDEX(INDIRECT(""'""&RC5&""'!1:1""),COLUMN(C[-4]))=LOOKUP(""ーー"",R9C1:RC1),IFERROR(LOOKUP((R7C3&"""")+(R7C3-""4:""<0),TEXT((INDEX(INDIRECT(""'""&RC5&""'!2:1000""),,COLUMN(C[-4]))&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1),""""),""""))"
  Range("F9").NumberFormatLocal = "h:mm"
  Range("F9").Copy Destination:=Range("F9:EZ14")
  Range("A9:d14").Font.Color = -16776961
  Cells.EntireColumn.AutoFit
  Application.ScreenUpdating = True
End Sub
 
結果はこんな感じです。
 
     A    B    C   D    E   F   G   H  I   J   K
 4 シート  駅名 発車時         1号  2号  3号 1号  4号  2号
 5 京上り  15駅  0:41        1:30 0:26 0:41    1:14  2:11
 6         現在時                          
 7          3:39                          
 8  候補 シート  駅名 発車                       
 9   2号 京下り  18駅 3:00 京上り    1:28           2:00
10                京下り    3:00          16:15
11   3号 京下り  13駅 3:37 京上り       0:43          
12                京下り       3:37          
13   4号 京上り  18駅 2:20 京上り             2:20    
14                京下り             16:22    

投稿日時: 17/10/18 19:15:55
投稿者: もこもこ

んなっと様
sy様
 
返事が遅くなりまして申し訳ありません。
マクロまで組んでいただきありがとうございます。
 
マクロは少しだけ勉強しましたが、、、難しくて挫折しました(汗)
(変数の宣言したり、オブジェクトとか箱の中にとかetc…)
 
なにもわからないままマクロを貼り付け試してみました。
用意したもの、、、時刻表(のみ)
 
sy様のマクロを試しました。
すごく見やすくマクロって便利だとものすごーく思いました。
ほとんどやりたいことが凝縮していました。
 
ありがとうございました。
 
わからなかった点は「R1C1」ってなに?(私はこんな初級者です)
 
今の時刻表ですと
始発地(始発)→駅(終点)→駅(始発)→始発地(終点)を繰り返す時刻表になっています。
F4列(現在地が上りの時刻表)を参照するのだと思うのですが、折り返した時に終点の表示になってしまいます。(上りと下りの切り替わりの時に)
始発地(始発)→駅(終点)上り時刻表
駅(始発)→始発地(終点)下り時刻表
 
 
んなっと様
 
ありがとうございました。
(途中のマクロなしのところはまだ試していません。(汗))
 
時刻表のみを用意して、中身はわからなかったですが、マクロを試してみました。
もちろん、数式もマクロも解読はできませんでした。(すいません)
 
時刻は検索できました。(シートごとに検索できるようになっているのだと思います。)
 
わからなかったのは、
シート名(A4プルダウン)、駅名(B4プルダウン)、発車時刻(C4検索したい時間入力?)
現在時刻(C7、現在の時間)を入力したのですが、F9〜F14(行)が何も表示されませんでした。
ここを参照するのだと思うのですが、「C9、D9」も表示ないです。
 
あ!(19:13現在)
んなっと 様
また、あげていただいたのですね。
ありがとうございます。

回答
投稿日時: 17/10/19 03:01:19
投稿者: sy

もこもこ さんの引用:
すごく見やすくマクロって便利だとものすごーく思いました。

ちょっと違いますね。
マクロで結果を表示させるようなファイルではありません。
今回提示したマクロは、もこもこさんが手作業でレイアウトを作ったり、数式を入力していた作業を代わりにしてくれるマクロです。
なので完成したファイルはマクロは関係ない、今まで提示してきた数式だけのファイルになります。
ファイルが出来たら、もうこのマクロは必要ありません。
 
 
もこもこ さんの引用:
わからなかった点は「R1C1」ってなに?(私はこんな初級者です)

マクロは関係ないですね。
R1C1形式は行列の参照形式の事です。
標準はA1形式で、列がアルファベットで、行が数字になりますよね。
R1C1形式は行列とも数字でセル位置を表現する形式です。
 
B4セルなら、R4C2 と R が Row(行) 、C が Column(列) を表し、A1セルから数えて何番目と表現したりします。
 
またR1C1形式は相対的な位置を数式で表現出来るので、入力セルが何処にあっても同じ表記で相対位置を求める事が出来ると言う利点があります。
例えば右隣のセルを参照したいとかなら、A形式だと=B1、=C1と入力セルに合わせて数式内の文字も変えないといけませんが、R1C1形式だと全てのセルで=RC[1]と同じ文字で右隣を参照出来ます。
 
 
 
もこもこ さんの引用:
今の時刻表ですと
始発地(始発)→駅(終点)→駅(始発)→始発地(終点)を繰り返す時刻表になっています。
F4列(現在地が上りの時刻表)を参照するのだと思うのですが、折り返した時に終点の表示になってしまいます。(上りと下りの切り替わりの時に)
始発地(始発)→駅(終点)上り時刻表
駅(始発)→始発地(終点)下り時刻表

同じ車両が上り下りで行き来してるから、現在時刻によっては反対のシートを参照しないといけないと言う事ですか。
すいません。
レスが多すぎて全部読み切れてませんでした。
この場合はシート名は、「xxx 上り 平日」などのように統一したシート名になってますか?
路線名、上り下り、平日祝日、の文字の並びはどれでも良いですけど、全てのシートで同じ配置に統一して下さい。
 
その上でもう一度以下の修正コードを実行して下さい。
前の検索シートは削除して下さい。
使い方は前回と同じです。
 
Sub 作成()
    Dim shF As Worksheet
    Dim i As Long

    On Error Resume Next
    Set shF = Sheets("検索")
    If shF Is Nothing Then
        Sheets.Add Sheets(1)
        Sheets(1).Name = "検索"
    End If
    On Error GoTo 0
    Set shF = Sheets("検索")

    With shF
        .Cells.Clear
        .Range("A1").Value = "シート選択"
        .Range("B1").Value = "駅選択"
        .Range("C1").Value = "発車時刻"
        .Range("E2").Value = "発車時刻"
        .Range("E3").Value = "車両名"
        .Range("E5").Value = "現在駅"
        .Range("E6").Value = "発車時刻"
        .Range("E7").Value = "方面"
        .Range("A30").Value = "車両名"
        .Range("A31").Value = "発車時刻"
        .Range("A32").Value = "発車時刻"
        .Range("A33").Value = "現在位置"
        .Range("A34").Value = "反対車両"
        .Range("A35").Value = "現在位置反対"
        .Range("E37").Value = "列番号"
        .Range("E38").Value = "列番号"
        .Range("E39").Value = "列番号反対"
        .Range("E40").Value = "発車時刻"
        .Range("E41").Value = "対象行"
        .Range("E42").Value = "行数"
        .Range("E43").Value = "反対シート"
        .Range("E44").Value = "方面"
        .Range("E45").Value = "方面反対"
        .Range("A5").Value = "シート名"
        .Range("C4").Value = "現在時刻"
        .Range("F1:AI1").Formula = "=COLUMN(A1)"
        .Range("C2,C5,F2:AI2,F6:AI6").NumberFormatLocal = "[h]:mm"
        .Range("C5").Formula = "=TEXT(IF(NOW()-TODAY()<""4:00""*1,NOW()-TODAY()+1,NOW()-TODAY()),""[h]:mm"")*1"
        .Range("F2:AI2").Formula = "=IFERROR(SMALL($B31:$OK31,COLUMN(A1)),"""")"
        .Range("F3:AI3").Formula = "=IF(F2="""","""",INDEX(30:30,F37))"
        .Range("F5:AI5").Formula = "=IF(F2="""","""",LOOKUP($C5,INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)+(1/(INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)<>""""))*(INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)<""4:00""*1)*""24:00"",INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!A2:A100"")))"
        .Range("F6:AI6").Formula = "=IF(F2="""","""",LOOKUP($C5,INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)+(1/(INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)<>""""))*(INDIRECT(""'""&IF(F7=$F44,$A2,$F43)&""'!R2C""&IF(F7=$F44,F37,F39)&"":R100C""&IF(F7=$F44,F37,F39),0)<""4:00""*1)*""24:00""))"
        .Range("F7:AI7").Formula = "=IF(F2="""","""",IF(SUMIF($B34:$OK34,F3,$B35:$OK35)>SUMIF($B30:$OK30,F3,$B33:$OK33),$F45,$F44))"
        
        .Range("B30:OK30").Formula = "=IFERROR(INDIRECT(""'""&$A2&""'!R1C"",0)&"""","""")"
        .Range("B31:OK31").Formula = "=IFERROR(IF(AND(B32>=$F40-""0:30"",B32<=$F40+""0:30""),B32,""""),"""")"
        .Range("B32:OK32").Formula = "=IFERROR(IF(1/(1/INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0))<""4:00""*1,INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0)+""24:00"",INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0)),"""")"
        .Range("B33:OK33").Formula = "=IFERROR(LOOKUP($C5,INDIRECT(""'""&$A2&""'!R2C:R100C"",0)+(1/(INDIRECT(""'""&$A2&""'!R2C:R100C"",0)<>""""))*(INDIRECT(""'""&$A2&""'!R2C:R100C"",0)<""4:00""*1)*""24:00""),"""")"
        .Range("B34:OK34").Formula = "=IFERROR(INDIRECT(""'""&$F43&""'!R1C"",0)&"""","""")"
        .Range("B35:OK35").Formula = "=IFERROR(LOOKUP($C5,INDIRECT(""'""&$F43&""'!R2C:R100C"",0)+(1/(INDIRECT(""'""&$F43&""'!R2C:R100C"",0)<>""""))*(INDIRECT(""'""&$F43&""'!R2C:R100C"",0)<""4:00""*1)*""24:00""),"""")"
        .Range("F37:AI37").Formula = "=IF(F2="""","""",MATCH(F2,$A31:$OK31,0))"
        .Range("F38:AI38").Formula = "=IFERROR(LOOKUP(1000,1/(($B30:$OK30=F3)*($B33:$OK33<>""""))*COLUMN($B1:$OK1)),"""")"
        .Range("F39:AI39").Formula = "=IFERROR(LOOKUP(1000,1/(($B34:$OK34=F3)*($B35:$OK35<>""""))*COLUMN($B1:$OK1)),"""")"
        .Range("F40").Formula = "=IF(C2="""","""",IF(C2<""4:00""*1,C2+""24:00"",C2))"
        .Range("F41").Formula = "=MATCH(B2,INDIRECT(""'""&A2&""'!A1:A100""),0)"
        .Range("F42").Formula = "=COUNTA(INDIRECT(""'""&A2&""'!A2:A100""))+1"
        .Range("F43").Formula = "=IF(COUNTIF(A2,""*上り*""),SUBSTITUTE(A2,""上り"",""下り""),SUBSTITUTE(A2,""下り"",""上り""))"
        .Range("F44").Formula = "=IF(COUNTIF(A2,""*上り*""),""上り"",""下り"")"
        .Range("F45").Formula = "=IF(F44=""上り"",""下り"",""上り"")"

        For i = 2 To Sheets.Count
            .Range("A" & i + 5).Value = Sheets(i).Name
        Next i

        With .Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(A6,0,0,COUNTA(A6:A29),1)"
        End With

        .Range("A2").Value = Sheets(Sheets.Count).Name
        With .Range("B2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(INDIRECT(""'""&A2&""'!A2""),0,0,F42,1)"
        End With
        .Range("A2").ClearContents

        With .Range("F1:AI7")
            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=AGGREGATE(15,6,1/($F$2:$AI$2>$F$40)*$F$2:$AI$2,1)=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258
            .FormatConditions(1).StopIfTrue = False

            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=AGGREGATE(14,6,($F$2:$AI$2<$F$40)*$F$2:$AI$2,1)=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258
            .FormatConditions(1).StopIfTrue = False

            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=TEXT(F$2,""[h]:mm"")-$F$40=0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 16247773
            .FormatConditions(1).StopIfTrue = False
        End With

    End With

End Sub

投稿日時: 17/10/19 09:49:02
投稿者: もこもこ

sy 様
 
ありがとうございます。
 
そうなんですね。
すっかり、マクロで表示されてるのかと思いました。
 
マクロ実行後、組みあがった表を見て数式が全部入っていたのでこんなこともできるんだ〜
マクロってすごいな〜と思ってました。(汗)
 
それで、んなっと様も一度だけマクロを使っていいならって言っていたのですね。
 
すいません本題に戻ります。
 
シート名はすべて
統一に変えます。
「○○平日上り」「○○休日上り」
 
帰宅後にやってみますのでご報告は夜になります。
 
 
 

投稿日時: 17/10/19 23:49:16
投稿者: もこもこ

sy 様
 
ありがとうございました。
 
遅くなりましたができました。
(今までかかってしまいましたすいません)
 
本当にありがとうございました。
 
 
 
んなっと様
 
本日、途中になってしまっていますので明日、続きをいたします。
 
時間かかりすぎて申し訳ありません。(汗)
 
ちょっと遅くなってしまいましたので今日はこの辺で休みます。
すいません。

回答
投稿日時: 17/10/20 00:08:19
投稿者: sy

すいません。
修正版のコードの方ですけど、2か所セルの位置が間違ってました。
 
全文アップすると長いので、
コードの一番下、End Sub の直ぐ上に以下の2行を貼り付けてから実行して下さい。

shF.Range("C4:C5").Cut shF.Range("C5")
shF.Range("A7:A29").Copy shF.Range("A6")

 
下から数行分の完成形を記載します。
以下のようなイメージになったら良いです。
            .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=TEXT(F$2,""[h]:mm"")-$F$40=0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 16247773
            .FormatConditions(1).StopIfTrue = False
        End With

    End With

shF.Range("C4:C5").Cut shF.Range("C5")
shF.Range("A7:A29").Copy shF.Range("A6")

End Sub

 
もうマクロ実行された後だったんですね><
お手数ですけど、もう一度上記の修正後に実行して下さい。
二度手間にさせてしまって大変申し訳ありません。
 
それと回答者は皆さん一切気にしないので、修正修正ばかりで逆に気の毒になるので、少しづつで良いので、もこもこさんのペースで作成して下さい。

回答
投稿日時: 17/10/20 10:24:47
投稿者: んなっと

すみません、私も修正させてください。
下のtest3でお願いします。
C7の現在時は、テスト用にいろいろな時刻を「直接入力」してください。
日付をまたいだ深夜の時刻は24:01の形ではなく、00:01でお願いします。
たぶんこれでいけると思います。
 
Sub test3()
  Dim Sh As Worksheet, mySh As Worksheet
  Dim i As Long
  Application.ScreenUpdating = False
  Set mySh = Sheets.Add(before:=Sheets(1))
  i = 30
  For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name Like "*[上下]り*" Then
      mySh.Cells(i, 1).Value = Sh.Name
      i = i + 1
    End If
  Next
  Range("A4").Value = "シート"
  Range("B4").Value = "駅名"
  Range("C4").Value = "発車時"
  Range("A5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=OFFSET(A30,,,COUNTA(A30:A1000))"
  Range("A5").Value = Range("A30").Value
  Range("B5").Validation.Add _
    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=INDIRECT(""'""&A5&""'!A2:A""&COUNTA(INDIRECT(""'""&A5&""'!A:A""))+1)"
  Range("C5").Value = TimeValue("6:29")
  Range("C5").NumberFormatLocal = "[h]:mm"
  Range("C6").Value = "現在時"
  Range("C7").Value = TimeValue("8:20")
  Range("C7").NumberFormatLocal = "[h]:mm"
  Range("A8").Value = "候補"
  Range("B8").Value = "シート"
  Range("C8").Value = "駅名"
  Range("D8").Value = "発車"
  Range("F4").FormulaR1C1 = _
      "=IFERROR(INDEX(INDIRECT(""'""&R[1]C1&""'!1:1""),COLUMN(C[-4])),"""")&"""""
  Range("F5").FormulaR1C1 = _
      "=IFERROR(TEXT((VLOOKUP(RC2,INDIRECT(""'""&RC1&""'!A:EZ""),COLUMN(C[-4]),FALSE)&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,"""")"
  Range("F5").NumberFormatLocal = "h:mm"
  Range("F4:F5").AutoFill Destination:=Range("F4:EZ5"), Type:=xlFillDefault
  Range("A9").FormulaR1C1 = _
     "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=TEXT((R5C3&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1),2),1)*1000),"""")"
  Range("A11").FormulaR1C1 = _
     "=IFERROR(INDEX(R4,MOD(AGGREGATE(14,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]<=TEXT((R5C3&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1),1),1)*1000),"""")"
  Range("A13").FormulaR1C1 = _
     "=IFERROR(INDEX(R4,MOD(AGGREGATE(15,6,COLUMN(C[5]:C[155])/1000+TEXT(R5C[5]:R5C[155],""[h]mm;;"")/(R5C[5]:R5C[155]>TEXT((R5C3&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1),1),1)*1000),"""")"
  Range("B9").FormulaR1C1 = _
      "=IF(RC[-1]="""","""",IF(IFERROR(MAX(R[1]C[4]:R[1]C[154])=RC[2],),R[1]C[3],RC[3]))"
  Range("C9").FormulaR1C1 = _
     "=IF(RC[-2]="""","""",IFERROR(LOOKUP(TEXT((R7C3&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,TEXT((INDEX(INDIRECT(""'""&RC[-1]&""'!B2:EZ1000""),,MATCH(RC[1],INDEX(RC[3]:R[1]C[153],MATCH(RC[-1],RC[2]:R[1]C[2],0),),0))&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,INDIRECT(""'""&RC[-1]&""'!A2:A1000"")),""""))"
  Range("D9").FormulaR1C1 = "=IF(RC[-3]="""","""",MAX(RC[2]:R[1]C[152]))"
  Range("D9").NumberFormatLocal = "h:mm"
  Range("E9").FormulaR1C1 = "=IF(RC[-4]="""","""",R5C1)"
  Range("E10").FormulaR1C1 = _
      "=IF(R[-1]C[-4]="""","""",SUBSTITUTE(R5C1,IF(COUNTIF(R5C1,""*上り*""),""上り"",""下り""),IF(COUNTIF(R5C1,""*上り*""),""下り"",""上り"")))"
  Range("B9:E10").Copy Destination:=Range("B11:D12")
  Range("B9:E10").Copy Destination:=Range("B13:D14")
  Range("F9").FormulaR1C1 = _
     "=IF(RC5="""","""",IF(INDEX(INDIRECT(""'""&RC5&""'!1:1""),COLUMN(C[-4]))=LOOKUP(""ーー"",R9C1:RC1),IFERROR(LOOKUP(TEXT((R7C3&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1,TEXT((INDEX(INDIRECT(""'""&RC5&""'!2:1000""),,COLUMN(C[-4]))&"""")+1,""[>1.166]h:mm;[>0][h]:mm;;"")*1),""""),""""))"
  Range("F9").NumberFormatLocal = "h:mm"
  Range("F9").Copy Destination:=Range("F9:EZ14")
  Range("A9:d14").Font.Color = -16776961
  Cells.EntireColumn.AutoFit
  Application.ScreenUpdating = True
End Sub

回答
投稿日時: 17/10/21 12:45:50
投稿者: sy

すいません。
しっかり確認したら数式に不具合がありました。
言い訳にしかならないけど、最近残業多いから、しっかりと確認出来てませんでした。
今度は朝から色々時刻変えてみて確認したので大丈夫と思います。
(この質問の要件自体がかなり複雑な要件なので、絶対とは言い切れないです、すいません。)
  
何度も手直しばかりで申し訳ありませんが、作成用のマクロを以下に差し替えお願いします。
  
それとマクロで検索する方法も次レスに掲示しておきます。
  
1点だけ、無いと思いますけど、発車時刻が同じ時刻の車両が2本以上あれば、全て時刻表の左側の車両になってしまいます。
(同じ時刻に同じ方向に発車すれば衝突するので、こんなケースは無いと思いますが。。。)
因みにマクロ検索の方は、こう言うケースでも、正しく表示されます。
  
以下、数式検索用の作成マクロ(最終版)です。
使い方は前回までと同じです。
  

Sub 数式検索作成()
    Dim shF As Worksheet
    Dim sh As Worksheet
    Dim i As Long

    On Error Resume Next
    Set shF = Sheets("検索")
    On Error GoTo 0
    If shF Is Nothing Then
        Set shF = Sheets.Add(Sheets(1))
        shF.Name = "検索"
    End If

    With shF
        .Cells.Clear

        .Range("A1").Value = "シート選択"
        .Range("B1").Value = "駅選択"
        .Range("C1").Value = "発車時刻"
        .Range("C5").Value = "現在時刻"
        .Range("A5").Value = "シート名"
        .Range("E2").Value = "発車時刻"
        .Range("E3").Value = "車両名"
        .Range("E5").Value = "現在駅"
        .Range("E6").Value = "発車時刻"
        .Range("E7").Value = "方面"

        .Range("A30").Value = "車両名"
        .Range("A31").Value = "発車時刻"
        .Range("A32").Value = "発車時刻"
        .Range("A33").Value = "現在位置"
        .Range("A34").Value = "反対車両"
        .Range("A35").Value = "現在位置反対"
        .Range("E37").Value = "列番号"
        .Range("E38").Value = "列番号"
        .Range("E39").Value = "列番号反対"
        .Range("E40").Value = "発車時刻"
        .Range("E41").Value = "対象行"
        .Range("E42").Value = "行数"
        .Range("E43").Value = "反対シート"
        .Range("E44").Value = "方面"
        .Range("E45").Value = "方面反対"

        .Range("F1:AI1").Formula = "=COLUMN(A1)"
        .Range("C6").Formula = "=TEXT(IF(NOW()-TODAY()<""4:00""*1,NOW()-TODAY()+1,NOW()-TODAY()),""[h]:mm"")*1"
        .Range("F2:AI2").Formula = "=IFERROR(SMALL($B31:$OK31,COLUMN(A1)),0)"
        .Range("F3:AI3").Formula = "=IF(F2=0,"""",INDEX(30:30,F37))"
        .Range("F5:AI5").Formula = "=IF(F2=0,"""",LOOKUP($C6,1/(OFFSET(INDIRECT(""'""&IF(COUNTIF($A2,""*""&F7&""*""),$A2,$F43)&""'!A2""),0,IF(COUNTIF($A2,""*""&F7&""*""),F38,F39)-1,99,1)<>"""")*(INDIRECT(""'""&IF(COUNTIF($A2,""*""&F7&""*""),$A2,$F43)&""'!R2C""&IF(COUNTIF($A2,""*""&F7&""*""),F38,F39)&"":R100C""&IF(COUNTIF($A2,""*""&F7&""*""),F38,F39),0)+(OFFSET(INDIRECT(""'""&IF(COUNTIF($A2,""*""&F7&""*""),$A2,$F43)&""'!A2""),0,IF(COUNTIF($A2,""*""&F7&""*""),F38,F39)-1,99,1)<""4:00""*1)),INDIRECT(""'""&IF(COUNTIF($A2,""*""&F7&""*""),$A2,$F43)&""'!A2:A100"")))"
        .Range("F6:AI6").Formula = "=IF(F2=0,"""",IF(COUNTIF($A2,""*""&F7&""*""),INDEX(33:33,F38),INDEX(35:35,F39)))"
        .Range("F7:AI7").Formula = "=IF(F2=0,"""",IF(SUMIF($B34:$OK34,F3,$B35:$OK35)>SUMIF($B30:$OK30,F3,$B33:$OK33),$F45,$F44))"

        .Range("B30:OK30").Formula = "=IFERROR(INDIRECT(""'""&$A2&""'!R1C"",0)&"""","""")"
        .Range("B31:OK31").Formula = "=IFERROR(IF(AND(B32>$F40-""0:30"",B32<$F40+""0:30""),B32,""""),"""")"
        .Range("B32:OK32").Formula = "=IFERROR(IF((INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0)&"""")*1<""4:00""*1,INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0)+1,INDIRECT(""'""&$A2&""'!R""&$F41&""C"",0)),"""")"
        .Range("B33:OK33").Formula = "=IFERROR(LOOKUP($C6,INDIRECT(""'""&$A2&""'!R2C:R100C"",0)+(1/(INDIRECT(""'""&$A2&""'!R2C:R100C"",0)<>""""))*(INDIRECT(""'""&$A2&""'!R2C:R100C"",0)<""4:00""*1)),"""")"
        .Range("B34:OK34").Formula = "=IFERROR(INDIRECT(""'""&$F43&""'!R1C"",0)&"""","""")"
        .Range("B35:OK35").Formula = "=IFERROR(LOOKUP($C6,INDIRECT(""'""&$F43&""'!R2C:R100C"",0)+(1/(INDIRECT(""'""&$F43&""'!R2C:R100C"",0)<>""""))*(INDIRECT(""'""&$F43&""'!R2C:R100C"",0)<""4:00""*1)),"""")"
        .Range("F37:AI37").Formula = "=IF(F2=0,"""",MATCH(F2,$A31:$OK31,0))"
        .Range("F38:AI38").Formula = "=IFERROR(LOOKUP(1000,1/(($B30:$OK30=F3)*($B33:$OK33<>""""))*COLUMN($B1:$OK1)),"""")"
        .Range("F39:AI39").Formula = "=IFERROR(LOOKUP(1000,1/(($B34:$OK34=F3)*($B35:$OK35<>""""))*COLUMN($B1:$OK1)),"""")"
        .Range("F40").Formula = "=IF(C2="""","""",IF(C2<""4:00""*1,C2+1,C2))"
        .Range("F41").Formula = "=MATCH(B2,INDIRECT(""'""&A2&""'!A1:A100""),0)"
        .Range("F42").Formula = "=COUNTA(INDIRECT(""'""&A2&""'!A2:A100""))"
        .Range("F43").Formula = "=IF(COUNTIF(A2,""*上り*""),SUBSTITUTE(A2,""上り"",""下り""),SUBSTITUTE(A2,""下り"",""上り""))"
        .Range("F44").Formula = "=IF(COUNTIF(A2,""*上り*""),""上り"",""下り"")"
        .Range("F45").Formula = "=IF(F44=""上り"",""下り"",""上り"")"

        .Cells.NumberFormatLocal = "G/標準"
        .Range("C2,C6,F6:AI6,B31:OK35,F40").NumberFormatLocal = "[h]:mm"
        .Range("F2:AI2").NumberFormatLocal = "[h]:mm;;"
        .Cells.ColumnWidth = 7
        .Range("A:C,E:E").ColumnWidth = 10
        .Cells.ShrinkToFit = True

        i = 5
        For Each sh In Worksheets
            If sh.Name Like "*上り*" Or sh.Name Like "*下り*" Then
                i = i + 1
                .Range("A" & i).Value = sh.Name
            End If
        Next sh

        With .Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(A6,0,0,COUNTA(A6:A29),1)"
        End With

        .Range("A2").Value = Sheets(Sheets.Count).Name
        With .Range("B2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=OFFSET(INDIRECT(""'""&A2&""'!A2""),0,0,F42,1)"
        End With
        .Range("A2").ClearContents

        With .Range("F1:AI7")
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MAX(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<$F$40)*$F$2:$AI$2,0))=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258

            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MIN(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<=$F$40)*10+$F$2:$AI$2,0))=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258

            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=TEXT(F$2,""[h]:mm"")*1=$F$40"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 16247773
        End With

    End With

End Sub

回答
投稿日時: 17/10/21 12:47:19
投稿者: sy

次いで、マクロ検索用のシート作成コードと、検索を実行する為のコードを提示します。
  
初めにシート作成用コードは、数式のシート作成コードと使い方は同じです。
以下シート作成コードです。
  

Sub マクロ検索作成()
    Dim shF As Worksheet
    Dim sh As Worksheet
    Dim s As String

    On Error Resume Next
    Set shF = Sheets("検索M")
    On Error GoTo 0
    If shF Is Nothing Then
        Set shF = Sheets.Add(Sheets(1))
        shF.Name = "検索M"
    End If

    With shF
        .Cells.Clear

        .Range("A1").Value = "シート選択"
        .Range("B1").Value = "駅選択"
        .Range("C1").Value = "発車時刻"
        .Range("C5").Value = "現在時刻"
        .Range("E2").Value = "発車時刻"
        .Range("E3").Value = "車両名"
        .Range("E5").Value = "現在駅"
        .Range("E6").Value = "発車時刻"
        .Range("E7").Value = "方面"
        .Range("C6").Formula = "=TEXT(IF(NOW()-TODAY()<""4:00""*1,NOW()-TODAY()+1,NOW()-TODAY()),""[h]:mm"")*1"

        .Cells.NumberFormatLocal = "G/標準"
        .Range("C2,C6,F2:AI2,F6:AI6").NumberFormatLocal = "[h]:mm"
        .Cells.ColumnWidth = 7
        .Range("A:C,E:E").ColumnWidth = 10
        .Cells.ShrinkToFit = True

        With .Range("F1:AI7")
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MAX(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")*1)*$F$2:$AI$2,0))=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258

            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MIN(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<=TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")*1)*10+$F$2:$AI$2,0))=F$2"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 14348258

            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")=TEXT(F$2,""[h]:mm"")"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 16247773
        End With

        For Each sh In Worksheets
            If sh.Name Like "*上り*" Or sh.Name Like "*下り*" Then
                s = s & "," & sh.Name
            End If
        Next sh
        s = Mid(s, 2)

        With .Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:=s
        End With

    End With

End Sub

 
  
実行すれば、「検索M」と言うシートが作成されます。
そのシートのモジュール(VBEの左上に表示されているシートに「検索M」が追加されている筈ですので、それをダブルクリック)に以下のコードを、全て貼り付けます。
  
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim v1 As Variant
    Dim v2 As Variant
    Dim t0 As Double
    Dim t1 As Double
    Dim t2 As Double
    Dim i As Long
    Dim k As Long
    Dim m As Long
    Dim p As Long
    Dim a As Long

    If Intersect(Target, Range("A2:C2")) Is Nothing Then Exit Sub
    If WorksheetFunction.CountA(Range("A2:C2")) < 3 Then Exit Sub

    Set sh1 = Sheets(Range("A2").Value)
    If InStr(sh1.Name, "上り") Then
        Set sh2 = Sheets(Replace(sh1.Name, "上り", "下り"))
    Else
        Set sh2 = Sheets(Replace(sh1.Name, "下り", "上り"))
    End If

    v1 = sh1.Range("A1").CurrentRegion.Value
    v2 = sh2.Range("A1").CurrentRegion.Value
    t0 = Range("C2").Value - (Range("C2").Value < TimeValue("4:00:00"))

    ReDim ary(1 To 7, 1 To 30)
    i = WorksheetFunction.Match(Range("B2").Value, sh1.Range("A:A"), 0)
    For k = 2 To UBound(v1, 2)
        If IsNumeric(v1(i, k)) And v1(i, k) <> "" Then
            t1 = v1(i, k) - (v1(i, k) < TimeValue("4:00:00"))
            If t1 < t0 + TimeValue("0:30:00") And t1 > t0 - TimeValue("0:30:00") Then
                m = m + 1
                ary(1, m) = m
                ary(2, m) = t1
                ary(3, m) = v1(1, k)
                For p = UBound(v1, 2) To 2 Step -1
                    If v1(1, p) = ary(3, m) Then
                        For a = 2 To UBound(v1, 1)
                            If IsNumeric(v1(a, p)) And v1(a, p) <> "" Then
                                t2 = v1(a, p) - (v1(a, p) < TimeValue("4:00:00"))
                                If t2 <= Range("C6").Value Then
                                    ary(5, m) = v1(a, 1)
                                    ary(6, m) = t2
                                    ary(7, m) = IIf(InStr(Range("A2").Value, "上り"), "上り", "下り")
                                    If a = UBound(v1, 1) Then
                                        GoTo step1
                                    End If
                                Else
                                    If ary(5, m) = "" Then
                                        Exit For
                                    Else
                                        GoTo step1
                                    End If
                                End If
                            End If
                        Next a
                    End If
                Next p
step1:
                For p = UBound(v2, 2) To 2 Step -1
                    If v2(1, p) = ary(3, m) Then
                        For a = 2 To UBound(v2, 1)
                            If IsNumeric(v2(a, p)) And v2(a, p) <> "" Then
                                t2 = v2(a, p) - (v2(a, p) < TimeValue("4:00:00"))
                                If t2 <= Range("C6").Value Then
                                    If ary(6, m) < t2 Then
                                        ary(5, m) = v2(a, 1)
                                        ary(6, m) = t2
                                        ary(7, m) = IIf(InStr(Range("A2").Value, "上り"), "下り", "上り")
                                    End If
                                    If a = UBound(v1, 1) Then
                                        GoTo step2
                                    End If
                                Else
                                    If ary(5, m) = "" Then
                                        Exit For
                                    End If
                                End If
                            End If
                        Next a
                    End If
                Next p
step2:
            End If
        End If
    Next k
    Range("F1:AI7").Value = ary
    With Me.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("F2")
        .SetRange Range("F2:AI7")
        .Header = xlNo
        .Orientation = xlLeftToRight
        .Apply
    End With

End Sub


Private Sub Worksheet_Activate()
    Dim sh As Worksheet
    Dim s As String

    For Each sh In Worksheets
        If sh.Name Like "*上り*" Or sh.Name Like "*下り*" Then
            s = s & "," & sh.Name
        End If
    Next sh
    s = Mid(s, 2)

    With Range("A2").Validation
        .Delete
        .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=s
    End With

    Cells.NumberFormatLocal = "G/標準"
    Range("C2,C6,F2:AI2,F6:AI6").NumberFormatLocal = "[h]:mm"
    Cells.ColumnWidth = 7
    Range("A:C,E:E").ColumnWidth = 10
    Cells.ShrinkToFit = True

    Cells.FormatConditions.Delete
    With Range("F1:AI7")
        .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MAX(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")*1)*$F$2:$AI$2,0))=F$2"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = 14348258
        
        .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=MIN(INDEX((TEXT($F$2:$AI$2,""[h]:mm"")*1<=TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")*1)*10+$F$2:$AI$2,0))=F$2"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = 14348258

        .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=TEXT($C$2+($C$2<""4:00""*1),""[h]:mm"")=TEXT(F$2,""[h]:mm"")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = 16247773
    End With

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sh As Worksheet
    Dim s As String

    If Target.Address = "$A$2" Then
        For Each sh In Worksheets
            If sh.Name Like "*上り*" Or sh.Name Like "*下り*" Then
                s = s & "," & sh.Name
            End If
        Next sh
        s = Mid(s, 2)

        With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:=s
        End With
    End If

    If Target.Address = "$B$2" Then
        If Range("A2").Value <> "" Then
            Set sh = Sheets(Range("A2").Value)
            s = Join(WorksheetFunction.Transpose(sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).Value), ",")
            With Range("B2").Validation
                .Delete
                .Add Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=s
            End With
        End If
    End If

End Sub

 
  
検索の方法は、数式シートと同じです。
前レスで述べた、同じ方向に同じ時刻に発車する車両がある場合は(衝突しないなら)、こちらの検索では正しく車両ごとに別けて表示されます。

投稿日時: 17/10/21 23:06:45
投稿者: もこもこ

んなっと 様
 
ありがとうございました。
 
遅くなりました。
(昨晩は寝落ちしてしまい今になってしまいました。すいません)
 
んなっと 様
test3でできました。
 
自分の方の時刻表がおかしかったので表示されなかったのです。
どうしても表記がされないのでいろいろ試しました。
 
時刻表
    1号    2号    3号    1号  ←ここ(「号」入力されている)
    7:44    7:56    8:06    8:17
い駅    7:45    7:57    8:07    8:18
ろ駅    7:46    7:58    8:08    8:19
は駅    7:47    7:59    8:09    8:20
に駅    7:48    8:00    8:10    8:21
ほ駅    7:48    8:00    8:10    8:21
 
 
時刻表(書式設定で号が表示されている)
    1号    2号    3号    1号  ←ここ(書式設定で「号」が表示されている中身は数字のみ)
    7:44    7:56    8:06    8:17
い駅    7:45    7:57    8:07    8:18
ろ駅    7:46    7:58    8:08    8:19
は駅    7:47    7:59    8:09    8:20
に駅    7:48    8:00    8:10    8:21
ほ駅    7:48    8:00    8:10    8:21
 
数字のみの場合
F9から右側のセルが何も表示されませんでした。
なんで表示されないのか?
まったく理解できずに時間だけが過ぎていました。(汗)
 
 
 
 
sy 様
 
お忙しい中何度も何度もありがとうございます。
 
明日、休みが取れましたのでやってみます。
 
いろいろ試行錯誤していただきましてありがとうございます。
 
取り急ぎ連絡いたします。
 
                
                                        
                                        
                                        
                                        
 
 
 
 

投稿日時: 17/10/22 12:13:00
投稿者: もこもこ

sy 様
 
遅くなりました。
 
マクロあり、マクロなし両方試しました。
両方とも正確な時間がでました。
ありがとうございました。
 
お礼の言いようがないくらい感謝しております。
今回、この掲示板は大切に使わせていただきます。(保管もします)
時刻表でいろいろな案を出していただき勉強になりました。
路線ごとに分けてやることも視野に入れて行こうと考えています。
 
自分なりにですがかなり勉強になりました。
 
 
 
んなっと 様
sy 様
 
お忙しい中、貴重な時間を使っていただき
本当にありがとうございました。
お礼しか言えないところがさみしいところです。
 
 
レス数が長期にわたりレスをしていただき申し訳ないのですが、
来週の火曜日に解決にを押させていただきます。
 
 
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 17/10/22 12:16:36
投稿者: sy

引用:
1点だけ、無いと思いますけど、発車時刻が同じ時刻の車両が2本以上あれば、全て時刻表の左側の車両になってしまいます。
(同じ時刻に同じ方向に発車すれば衝突するので、こんなケースは無いと思いますが。。。)

私の数式検索の方の上記に関してですけど、もしも同時刻に同じ方向に発車する電車があるなら、
(2つ以上の路線を1シートの時刻表に纏めると言う事をしていたら有り得るかも?)
 
以下の1行だけ式を変更して頂ければ、対応出来ます。
(マクロ検索と同じ、正しく車両毎に別れた結果が出るようになります。)
F37 =IF(F2=0,"",AGGREGATE(15,6,1/($A31:$OK31=F2)*COLUMN($A1:$OK1),COUNTIF($F2:F2,F2)))
AI37までフィルコピー

 
1シートの時刻表は1つの路線のみなら、そもそも同方面に2台同時に発車したら衝突するから関係ないので無視して下さい。

回答
投稿日時: 17/10/22 19:19:50
投稿者: んなっと

引用:
数字のみの場合
F9から右側のセルが何も表示されませんでした。

最後に列幅を狭くしたのが余計なおせっかいだったようです。
文字数の少ない数字に列幅をあわせてしまったため、
#が表示されて時刻が見えなくなっていたんですね。

回答
投稿日時: 17/10/22 19:51:47
投稿者: sy

んなっと さんの引用:
引用:
数字のみの場合F9から右側 〜略〜

〜略〜 #が表示されて時刻が見えなくなっていたんですね。

違いますよ。
んなっとさんのF9以降の式が、
引用:
LOOKUP("ーー",$A$9:$A9)

なので候補のA列が文字じゃないと認識できない為ですね。

回答
投稿日時: 17/10/22 20:45:01
投稿者: んなっと

そうでしたか。ありがとうございました。

投稿日時: 17/10/23 11:45:18
投稿者: もこもこ

sy 様
 
ありがとうございます。
 
引用:
1点だけ、無いと思いますけど、発車時刻が同じ時刻の車両が2本以上あれば、全て時刻表の左側の車両になってしまいます。
(同じ時刻に同じ方向に発車すれば衝突するので、こんなケースは無いと思いますが。。。)
 
このケース何本か存在します。
路線が集中するときに同時刻というのがあります。
 
この方法を回避するのはsy様が提案していただいた「路線ごとに」分ける
事で回避しようかと考えておりました。
路線ごとに分ければ同時刻は存在しないと思うので、、、
 
何度もすいません。
 
 
 
 
 
 
 
 

回答
投稿日時: 17/10/23 20:26:57
投稿者: sy

もこもこ さんの引用:
このケース何本か存在します。
路線が集中するときに同時刻というのがあります。
 
この方法を回避するのはsy様が提案していただいた「路線ごとに」分ける
事で回避しようかと考えておりました。
路線ごとに分ければ同時刻は存在しないと思うので、、、

複数の路線を1シートに纏めてると言う事ですね。
 
なら、投稿日時: 17/10/22 12:16:36 投稿者: sy でも提示しているように、F37〜AI37の式を以下に変えて下さい。
F37 =IF(F2=0,"",AGGREGATE(15,6,1/($A31:$OK31=F2)*COLUMN($A1:$OK1),COUNTIF($F2:F2,F2)))
AI37までフィルコピー

これで路線ごとに別けなくても、今のままの時刻表で同時刻に複数発車にも対応出来ます。
 
因みにマクロ検索の方は、時刻表のレイアウトが1行目が車両名、A列が駅名のレイアウトで、
シート名の規則が今現在使われている統一されたシート名でさえいれば、
殆どどんな事にも対応できるようになっています。

投稿日時: 17/10/24 07:55:00
投稿者: もこもこ

sy 様
 
おはようございます。
 
ありがとうございます。
 
まだ、追加の式を試していませんが(帰宅後)試してみます。
 

投稿日時: 17/10/24 22:14:18
投稿者: もこもこ

sy 様
 
遅くなりました。
 
最後に追加した式で同時刻検索できました。
 
試しに複数同時刻にして試しました。
自分の思っている以上の検索ができてとてもうれしい限りです。
 
最後の最後までご面倒をおかけしまして申し訳ありませんでした。
 
とても感謝しております。ありがとうございました。
これで何時間という時間のロスをなくせることか図り知れません。
このスレは大切にいたします。
 
スレはこれで解決済みにさせていただきます。
 
本当にありがとうございました。
 
 
sy 様
んなっと様
 
最後までお付き合いいただきありがとうございました。