Excel (一般機能)

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

 
(指定なし : Excel 2010)
検索について
投稿日時: 17/10/06 21:37:53
投稿者: もこもこ

お世話になります。
エクセルは得意ではないので困っております。
このような検索ができるのかどうかもわかりません。
 
    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
 
このような時刻表があります。
 
検索(J5) 番号(K5) 結果(L5)
 は駅  6:12  1号
 
と調べられる方法があるのでしょうか?
結果的に駅と時刻を入力すると号車が検索できるのでしょうか?
 
初歩的な質問でしたら申し訳ありません。
 
 

回答
投稿日時: 17/10/06 22:18:08
投稿者: 半平太

ちょっと確認したいのですが、
(番号(K5)と言うは、時刻(K5)だと思うのですが)
 
それが 6:12 とか 6:26 とかだったらいいのですが、
例えば 6:00 とか 6:15 とか入力されたら、どうするんでしょうか?

投稿日時: 17/10/06 22:42:01
投稿者: もこもこ

半平太様
早速の返答ありがとうございます。
 
 
すいません。
番号は時刻でした。
 
検索(J5) 時刻(K5) 結果(L5)
 は駅  6:12  1号
 
それが 6:12 とか 6:26 とかだったらいいのですが、
 例えば 6:00 とか 6:15 とか入力されたら、どうするんでしょうか?
 
考えていたのは
検索(J5) 時刻(K5) 結果(L5)
 は駅  6:12  1号
    時刻(K6)  結果(L6)
K6に=K5+TIMEVALUE("0:01") 
    時刻(K7) 結果(L7)
K7に=K5+TIMEVALUE("0:02")
と時刻のセルを増やし検索しようかと考えています。(プラス10分くらいまで)
 
ほかに考えられませんでした。
 
理想は
「は駅」、「時刻6:12〜10分くらいの範囲」、「号車」が調べられるといいのですが、、、

回答
投稿日時: 17/10/06 23:01:15
投稿者: 半平太

>理想は
>「は駅」、「時刻6:12〜10分くらいの範囲」、「号車」が調べられるといい
 
ちょっとあいまいですね。
 
下図のどこまで1号で、どこから2号に変わるんですか? 
その考え方も一緒に示してください。
 

 駅   時刻  結果
 は駅    6:12    1号
 は駅    6:13     ?
 は駅    6:14     ?
 は駅    6:15     ?
 は駅    6:16     ?
 は駅    6:17     ?
 は駅    6:18     ?
 は駅    6:19     ?
 は駅    6:20     ?
 は駅    6:21     ?
 は駅    6:22     ?
 は駅    6:23     ?
 は駅    6:24     ?
 は駅    6:25     ?
 は駅    6:26    2号

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

半平太様
 
説明が下手で申し訳ありません。
 
駅   時刻  結果
 は駅 6:12 1号
 は駅 6:13 ?
 は駅 6:14 ?
 は駅 6:15 ?
 は駅 6:16 ?
 は駅 6:17 ?
 は駅 6:18 ?
 は駅 6:19 ?
 は駅 6:20 ?
 は駅 6:21 ?
 は駅 6:22 ?
 は駅 6:23 ?
 は駅 6:24 ?
 は駅 6:25 ?
 は駅 6:26 2号
 
時刻は遅れたりする場合があるので(プラス時間を5分〜10分)を考えていましたが、この
表だと10分足していくと次の発車になってしまいますね。
 
駅   時刻  結果
 は駅 6:12 1号
 は駅 6:13 該当なし
 は駅 6:14 該当なし
 は駅 6:15 該当なし
 は駅 6:16 ?
 は駅 6:17 ?
 は駅 6:18 ?
 は駅 6:19 ?
 は駅 6:20 ?
 は駅 6:21 ?
 は駅 6:22 ?
 は駅 6:23 ?
 は駅 6:24 ?
 は駅 6:25 ?
 は駅 6:26 2号
 
下図のどこまで1号で、どこから2号に変わるんですか? 
その考え方も一緒に示してください。
 
はい
B列が1号車
C列が2号車



G列が1号車
H列が2号車と繰り返します。
 
この説明で足りてるか不安ですが、、、

回答
投稿日時: 17/10/06 23:31:43
投稿者: 半平太

聞き方が良くなかったようで、勘違いされてしまいましたが、
 
6:13〜6:15 が「該当なし」は分かりました。では、6:16〜6:25 は「何」になるんですか?

投稿日時: 17/10/06 23:49:40
投稿者: もこもこ

半平太様
 
何度もすいません。
 
該当がない時間は「該当なし」が理想なのですが。
 
駅通過時間は早く通過することはないのでその時間を(含む)後の時間が何号車かわかればよいのですが
説明が足らないようですいません。
 
 
 

回答
投稿日時: 17/10/07 00:08:54
投稿者: 半平太

私には何が「理想」なのか分かりません。
 
何度も謝られているので、これ以上お聞きしにくいのでドロップアウトします。
 
他の回答者のレスをお待ちください。・・と言っても、明日以降になるでしょうね。

投稿日時: 17/10/07 00:39:14
投稿者: もこもこ

半平太様
ありがとうございました。
 
もう少し自分なりに考えてみます。
 
要点をまとめたらもう一度質問してみることにします。
ありがとうございました。
 

回答
投稿日時: 17/10/07 08:14:53
投稿者: sy

おはようございます。
 
半平太さんからの質問の意味を理解されて無いようで、話自体は噛み合ってませんが、半平太さんが聞きたかった事はこの文面です。

もこもこ さんの引用:
駅通過時間は早く通過することはないのでその時間を(含む)後の時間が何号車かわかればよいのですが
この文章の内容、「発車時刻より後はあり得るけど前は無い」がルールなのか知りたかったと思います。
 
でもその前で、
もこもこ さんの引用:
駅   時刻  結果
は駅 6:12 1号
は駅 6:13 該当なし
と言う返答があったので、「1分でも遅れた時刻を入力すると、全て該当なし」がルールなのか、こちらでは判断が出来ないので、お聞きしてるんだと思いますよ。
 
もこもこ さんの引用:
は駅 6:12 6:26 6:39
の場合、
もこもこ さんの引用:
「時刻6:12〜10分くらいの範囲」
と言う事なので、
6:11以前は「該当なし」
6:12〜6:22(プラス10分)までは「1号車」、6:23〜6:25は「該当なし」、
6:26〜6:36(プラス10分)までは「2号車」、6:37〜6:38は「該当なし」
6:39〜6:49(プラス10分)までは「3号車」、それ以降は「該当なし」
と言うルールで良いんでしょうか?

回答
投稿日時: 17/10/07 08:31:44
投稿者: sy

もこもこ さんの引用:
この表だと10分足していくと次の発車になってしまいますね。

混乱してるようですけど、遅れる時刻は最大10分、それまでに次の発車時刻になると、次の号車
と言うルールではダメなんですか?
 
例えば
1号車 6:12
2号車 6:18
この場合、
6:12〜6:17までが「1号車」
6:18は2号車(10分以内だけど、次の発車時刻なので)
こういうルールはOK・NGどちらでしょうか?

回答
投稿日時: 17/10/07 08:43:46
投稿者: んなっと

半平太さん、syさんの質問と同じような内容です。
 
          L:N列のうち、どの結果が正しいのでしょうか。
         ↓
    J   K   L    M   N
 4       厳守 5分遅許 全許
 5 は駅 6:09           
 6 は駅 6:10           
 7 は駅 6:11           
 8 は駅 6:12  1号   1号  1号
 9 は駅 6:13      1号  1号
10 は駅 6:14      1号  1号
11 は駅 6:15      1号  1号
12 は駅 6:16      1号  1号
13 は駅 6:17      1号  1号
14 は駅 6:18         1号
15 は駅 6:19         1号
16 は駅 6:20         1号
17 は駅 6:21         1号
18 は駅 6:22         1号
19 は駅 6:23         1号
20 は駅 6:24         1号
21 は駅 6:25         1号
22 は駅 6:26  2号   2号  2号
23 は駅 6:27      2号  2号
24 は駅 6:28      2号  2号
25 は駅 6:29      2号  2号
26 は駅 6:30      2号  2号
27 は駅 6:31      2号  2号
28 は駅 6:32         2号
29 は駅 6:33         2号
30 は駅 6:34         2号
31 は駅 6:35         2号
32 は駅 6:36         2号
33 は駅 6:37         2号
34 は駅 6:38         2号
35 は駅 6:39  3号   3号  3号
36 は駅 6:40      3号  3号
37 は駅 6:41      3号  3号
38 は駅 6:42      3号  3号
39 は駅 6:43      3号  3号
40 は駅 6:44      3号  3号
 
L5
=IFERROR(INDEX(B$1:H$1,MATCH(K5,INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),),0)),"")
下方向・↓
M5
=IFERROR(INDEX($1:$1,AGGREGATE(14,6,COLUMN(B:H)/(A$2:A$6=J5)/(K5>=B$2:H$6)/(K5<=B$2:H$6+"0:05:01"),1)),"")
下方向・↓
N5
=IFERROR(INDEX(B$1:H$1,MATCH(K5,INDEX(B$2:H$6,MATCH(J5,A$2:A$6,0),))),"")
下方向・↓

投稿日時: 17/10/07 10:32:37
投稿者: もこもこ

sy 様
んなっと 様
 
お返事ありがとうございます。
 
sy様の説明
なるほどと思いました。ありがとうございます。
 
んなっと様
図で書いていただいたのでものすごくよくわかりました。
んなっと様の図で言うと
N:全許可です。
 
わからなかったのは、
 
は駅 6:12 1号
 
遅延したとして6:13、6:14、。。。と進むと
 
次の
は駅 6:26 2車 
と検索結果が出てしまうのではないのか?
と考えてしまい、5分から10分と答えてしまいました。(んなっと様の図M:5分遅許)
 
改めて、んなっと様の式でやってみたいと思います。
少しお時間かかるかもしれません。

投稿日時: 17/10/07 10:41:58
投稿者: もこもこ

sy さんの引用:
もこもこ さんの引用:
この表だと10分足していくと次の発車になってしまいますね。

混乱してるようですけど、遅れる時刻は最大10分、それまでに次の発車時刻になると、次の号車
と言うルールではダメなんですか?
 
例えば
1号車 6:12
2号車 6:18
この場合、
6:12〜6:17までが「1号車」
6:18は2号車(10分以内だけど、次の発車時刻なので)
こういうルールはOK・NGどちらでしょうか?

 
はい、まさにこれが言いたいことです。
それで、遅れすぎた場合が考え付かずに悩んでいました。

投稿日時: 17/10/07 11:03:34
投稿者: もこもこ

sy様
んなっと様
 
本日、仕事で遅くなりそうなので、明日試してみます。
返事が遅くなります。
 
 

回答
投稿日時: 17/10/07 11:05:04
投稿者: WinArrow
投稿者のウェブサイトに移動

INDEX関数,INDIRECT関数を使った検索の例です。
 
準備事項
(1)表全体に名前を設定します。例:範囲
(2)表の左端のセル(例A1)に「駅名」を入力します。
(3)「駅名」の列に名前を設定します。
 表の左端の列の1行目から最終行まで選択して
 「数式」→「選択範囲から作成」→「上端行」
(4)駅ごとの行に名前を設定します。
 表の2行目から表の最終行まで選択して
 「数式」→「選択範囲から作成」→「左端列」
 
検索用セル
(1)セルJ5に入力規則を設定します。
  「リスト」・・・元の値:=駅名
セルK5に入力規則を設定します。
  「リスト」・・・元の値:=INDIRECT(J5)
  セルK5に表示形式を時刻(h:mm)で設定します。
セルL5に数式を入力します。
  =INDEX(範囲,1,MATCH(L5,INDIRECT(K5))+1)
 
 
これで時刻を目視で選択できます。
 
 
 

回答
投稿日時: 17/10/07 11:23:56
投稿者: sy

もこもこ さんの引用:
んなっと様の図で言うと
N:全許可です。
「次の号車までの遅延は全て許す」がルールですか。
これなら簡単ですね。
 
初めのもこもこさんのレイアウトを使って、
もこもこ さんの引用:
     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 
 
検索(J5) 番号(K5) 結果(L5) 
  は駅     6:12     1号

L5 =IFERROR(LOOKUP(K5,INDEX(B1:D6,MATCH(J5,A1:A6,0),0),B1:D1),"該当なし")

回答
投稿日時: 17/10/07 11:51:09
投稿者: んなっと

syさんの式のほうがいいですね。
syさんの式でお願いします。

回答
投稿日時: 17/10/08 12:30:44
投稿者: Mike

1.範囲 A2:D6 選択
2.Alt+MC ⇒ “左端列”のみにチェック残して[OK]
3.セル L5 に次式を入力
 ̄ ̄ =IF(K5>MAX(INDIRECT(J5)),"なし",INDEX($B$1:$D$1,IFERROR(MATCH(K5,INDIRECT(J5),0),MATCH(K5+"0:20",INDIRECT(J5)))))

回答
投稿日時: 17/10/08 12:54:19
投稿者: Mike

訂正します。
L5: =IF(K5<MIN(INDIRECT(J5)),"1号",IF(K5>MAX(INDIRECT(J5)),"なし",INDEX($B$1:$D$1,IFERROR(MATCH(K5,INDIRECT(J5),0),MATCH(K5+"0:20",INDIRECT(J5))))))
 
1号の時刻未満値が K5 に入力された場合は、「1号」が返ります。
3号の時刻値が K5 に入力された場合は、「なし」が返ります。

投稿日時: 17/10/08 21:15:54
投稿者: もこもこ

半平太様
Sy様
んなっと様
WinArrow様
Mike様
 
お返事ありがとうございました。
自宅に戻り式を試してみました。
Sy様式が思っていた通りのことができて感動しております。
すごい!って叫んでいました。
 
WinArrow様のラインごとに名前の設定はまったく考えていなかったのでびっくりしています。
WinArrow様の式も同じ結果ができました。(目視は便利です)
 
Mike様
Alt+MCは初めて使ってみました。
式を試したのですが1分遅延したと入力すると「ほ駅」「6:28」「3」となってしまいます。
(私の文章読解力がないもので間違えていたらすいません)
 
皆さま本当にありがとうございました。
明日、本当の大きな表で実際にやつてみようと思います。
実際の表でできたことを報告後、解決済みにさせていただきます。

回答
投稿日時: 17/10/08 23:29:31
投稿者: Mike

もこもこ さんの引用:
Mike様
Alt+MCは初めて使ってみました。
式を試したのですが1分遅延したと入力すると「ほ駅」「6:28」「3」となってしまいます。
「1分遅延したと入力」とは具体的に何処に何を入力したのですか?そして、結果はどうなることを希望されるのか、参考までに教ええてください。

回答
投稿日時: 17/10/09 00:23:16
投稿者: sy

Mikeさん
 
以下が、Mikeさんの式と望む結果の違いです。
 

		正解	Mikeさんの式
は駅	6:09	なし	1号
は駅	6:10	なし	1号
は駅	6:11	なし	1号
は駅	6:12	1号	1号
は駅	6:13	1号	2号
は駅	6:14	1号	2号
は駅	6:15	1号	2号
は駅	6:16	1号	2号
は駅	6:17	1号	2号
は駅	6:18	1号	2号
は駅	6:19	1号	2号
は駅	6:20	1号	2号
は駅	6:21	1号	2号
は駅	6:22	1号	2号
は駅	6:23	1号	2号
は駅	6:24	1号	2号
は駅	6:25	1号	2号
は駅	6:26	2号	2号
は駅	6:27	2号	3号
は駅	6:28	2号	3号
は駅	6:29	2号	3号
は駅	6:30	2号	3号
は駅	6:31	2号	3号
は駅	6:32	2号	3号
は駅	6:33	2号	3号
は駅	6:34	2号	3号
は駅	6:35	2号	3号
は駅	6:36	2号	3号
は駅	6:37	2号	3号
は駅	6:38	2号	3号
は駅	6:39	3号	3号
は駅	6:40	3号	なし
は駅	6:41	3号	なし
は駅	6:42	3号	なし
は駅	6:43	3号	なし
は駅	6:44	3号	なし

回答
投稿日時: 17/10/09 08:27:10
投稿者: Mike

sy さんの引用:
以下が、Mikeさんの式と望む結果の違いです
正誤表を作成していただいて多謝です。
私の質問に対する解釈が間違っていたのですね。私の意図は、以下の如くでした。
 
「番号(K5)」の時刻が
“時刻表”の発車時刻以前なら、あるいは、過ぎていたら、それ以後の列車を選択し、
“終列車”(3号車)の発車時刻を過ぎていたら、(最早どうしようもないので)「なし」に。
 
ありがとうございました。

投稿日時: 17/10/09 17:18:54
投稿者: もこもこ

半平太様
Sy様
んなっと様
WinArrow様
Mike様
 
 
会社で元の表で式を試しました。
皆様のおかげで思い通りの結果が得られ感謝しております。
 
今回、自分はちんぷんかんぷんな事を聞いてしまったのかな?
と、恥ずかしかったですがとても勉強になりました。
 
本当に皆様に感謝しております。
 
ありがとうございました。