[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時刻表の検索』（K・Y）
よろしくお願いいたします。（windowsXP、Excel2003) バスの時刻表がありまして、下の表の 1列目→時間 2～4列目→平日の分 5列目→土日の分、となってます。 日付と時刻を入力したら、次のバスの時間を表示できるような 数式を入れたいと思っています。 日付が土日の場合は表示できたのですが 平日の場合の表示のやり方が分かりません。 どなたかよろしくお願いいたします。
平日 平日 平日 土日 時 分 分 分 分 8 0 15 40 25 9 0 20 40 30 10 0 20 40 30 11 5 25 45 20 12 5 25 45 15 13 10 25 50 45
> 日付が土日の場合は表示できたのですが
その数式をご提示いただけませんか？ (説明の補強情報になりますので）
(半平太) 2011/04/26 11:39
説明不足で申し訳ございません。 表の右側に、H2→日付、H3→時刻を入力したら H16に次のバスの時間を表示させるようにしています。 ここでは時刻は13時までしか入力していませんが 実際は22時まであります。 H16には次の数式を入れています。 =INDEX(B4:E9,MATCH(HOUR(H3),A4:A9),IF(WEEKDAY(H2,2),4,"?")) B4～E9には分の数値が入っています。 数式の"？"の部分が分からない部分です。 わかりづらいかもしれませんが よろしくお願い致します。
平日 平日 平日 土日 日付 2011/4/23 時 分 分 分 分 時刻 12：10 8 0 15 40 25 9 0 20 40 30 10 0 20 40 30 次のバス →セルH6 11 5 25 45 20 12 5 25 45 15 13 10 25 50 45
（K・Y）
１．ご提示の数式ですと、時刻が12:20 の時「12時15分」にならないですか？
２．平日は必ず3便あるとは限らないですね？
３．時刻は秒も含んでいるのでしょうか？
４．通常、既に何秒か経っていると思うのですが、 59秒までは、無視していいと云うことでしょうか？ それとも、1秒でもすぎたら、次の便ですか？
・・とお訊きしたものの、私には、難題のようです。(^^ゞ
(半平太) 2011/04/26 13:15
ご返事下さりありがとうございます。
> 時刻が12:20 の時「12時15分」にならないですか？
はい。ならないです。
> 平日は必ず3便あるとは限らないですね？
全て3便になっています。
> 時刻は秒も含んでいるのでしょうか？
含んでいません。
> 通常、既に何秒か経っていると思うのですが、 59秒までは、無視していいと云うことでしょうか？ それとも、1秒でもすぎたら、次の便ですか？
秒数は無視していただいてかまいません。
よろしくお願い致します。
（K・Y）
> > 時刻が12:20 の時「12時15分」にならないですか？ > はい。ならないです。
あれれ？ 私がやるとH6に15と出るのですが。。。 私は問題を何か勘違いしているのでしょうか？
行 _A_ __B__ __C__ __D__ _ E _ _F_ __G__ ____H____ 1 2 平日 平日 平日 土日 日付 2011/4/23 3 時 分 分 分 分 時刻 12:20 4 8 0 15 40 25 5 9 0 20 40 30 6 10 0 20 40 30 15 ←=INDEX(B4:E9,MATCH(HOUR(H3),A4:A9),IF(WEEKDAY(H2,2),4,"?")) 7 11 5 25 45 20 8 12 5 25 45 15 9 13 10 25 50 45
(半平太) 2011/04/26 14:01
僕も半平太さんと同じように｢15｣が表示されます。
提示された式だとWEEKDAYの戻り値が絶対に0が無い状態（WEEKDAYの種類が2）なので、IF関数はTRUEの結果しか 返さないから、INDEX関数の第3引数が4で固定されているように思います。 それが12:20のときに15が返る原因だと思いますよ。 （フェンリル）
半平太様、大変申し訳ありません。 私が間違っていました。 「15分」で 正解です。 実際のデータと少し変えていたのを忘れていました・・・。 悩ませてしまい、すみません・・・。
（K・Y）
> 「15分」で 正解です。
これは、ご提示の数式を使うと15と出ておかしくないと云う意味ですね？
問題としての「正解」はどうなんでしょう。次便である「45」ですか？ それとも引き続き「15」で変わりないのでしょうか？
あと、何故「分」だけ取り出すのですか？
普通は「時:分」一緒だと思うのですが、 「時」の方は別のセルに出しているのですか？
(半平太) 2011/04/26 15:02
> 問題としての「正解」はどうなんでしょう。次便である「45」ですか？ それとも引き続き「15」で変わりないのでしょうか？
そうでした・・・ 確かに「45」と表示させなければ正解にならないですね。 ここまでの数式にたどり着くのに精一杯で、全く気付けませんでした。
> あと、何故「分」だけ取り出すのですか？ 普通は「時:分」一緒だと思うのですが、 「時」の方は別のセルに出しているのですか？
こちらも余裕がなくて分までしか出せない・・という感じです。 できれば「時:分」と一つのセルに表示させたいと思っています。 よろしくお願い致します。
（K・Y）
<前準備> H6セルの結果が 0 になった場合にそれを空白に見せる為、 セルの書式(表示形式)をユーザー定義でこんな風に設定しておいてください。 ↓ [h]:mm;;;@
<H6セルの数式>
=IF(WEEKDAY(H2-1)<6,LOOKUP(2,IF({1,0},0,SMALL(INDEX((A4:A18&":"&B4:D18)*1,0),SUMPRODUCT(COUNTIF(H3,">"&A4:A18&":"&B4:D18))+1))),LOOKUP(2,IF({1,0},0,SMALL(INDEX((A4:A18&":"&E4:E18)*1,0),SUMPRODUCT(COUNTIF(H3,">"&A4:A18&":"&E4:E18))+1))))
(半平太) 2011/04/26 16:00
半平太さん、ありがとうございます。 教えて頂いた数式を入れてみたのですが いろんな日付を入れてみても空白になるのですが やり方がおかしいのでしょうか・・・？
（K・Y）
そう云われましても、当方は問題なく結果が出ております。
そちらのご説明の基づいて、下記の状態を想定しておりますが、 実際は、なにか違うのでしょうか？
念のため、H6セルの書式を「標準」にするとどうなるか、教えてください。 例：0.572916667 （← 13:45が正解の場合)
<結果図> 行 _A_ __B__ __C__ __D__ __E__ _F_ __G__ ____H____ 1 2 平日 平日 平日 土日 日付 2011/4/23 3 時 分 分 分 分 時刻 12:20 4 8 0 15 40 25 5 9 0 20 40 30 6 10 0 20 40 30 13:45
17 21 5 25 45 20 18 22 0 20 40 30
(半平太) 2011/04/26 16:53
書式を標準にしてみたのですが やはり 0 になってしまいます。 原因をじっくり確かめてみて ご返事したいと思います。 ありがとうございます。
（K・Y）
時刻を＝NOW() にしていないですか？
その場合は、こちらに変えてください。 ↓ H3セル =TEXT(NOW(),"h:m")*1
(半平太) 2011/04/26 17:20
今やっと、こちらをのぞいてみましたら、半平太様が書き込み下さっていたようで・・・ 半平太様の教えて頂いた数式ですが、私のコピーのやり方が悪かったようで、 ご心配おかけしてすみませんでした。 やり直したところ、希望通りの結果がでました！ ありがとうございます。
数式の方ですが、たくさん勉強させて頂き、COUNTIFを使って表内の時刻を表すやり方や、 表全体の中での時刻の位置を表すやり方など、ただただ感動するばかりでした。 ただ、SMALL関数の中身までは何とか解読できた（つもり）のですが 「LOOKUP」関数の検索値に"2"を入れて、何故答えの数値が出てくるのかが全く不思議でした。 もし、よろしければ「LOOKUP」と「IF」の部分の意味合いを教えていただけないでしょうか？
（K・Y)
>私のコピーのやり方が悪かったようで、
トホホですぅ (^^ゞ
>「LOOKUP」関数の検索値に"2"を入れて、何故答えの数値が出てくるのかが全く不思議でした。 > もし、よろしければ「LOOKUP」と「IF」の部分の意味合いを教えていただけないでしょうか？
最終便がなくなると、次便はないので、何も手当てしないとエラーになっちゃいます。 つまり、エラーをどうやって空白に見せるかと云う対策の一種です。
if関数で、最終便時刻より後かどうか判定して分岐するのが一般的です。 なので、 K・Yさんは、オーソドックスにそうしてください。
またよくある手は、if(iserror(本来の式),"",本来の式) ですね。 しかし、これは「本来の式」が2回も使われます。これはバカげています。 「本来の式」が長ったらしいかったらムカムカします。
XL2007ではIFERROR関数が追加されたので、このダブりは回避できるようになったのですが、 今回はXL2003とのことだったので、この関数は利用できません。
今回は 0 でも空白に見えるようにセルの書式がセットされていますので、 エラーをどうやって0に持っていくか、と云う問題に変わります。
さて、長ったらしい式が「#N/A」か「時刻(18:00)」を返してきたとすると、
提示した数式は、この下のどちらかになります。
↓
LOOKUP(2,IF({1,0},0,#N/A) → LOOKUP(2,{0,#N/A})
LOOKUP(2,IF({1,0},0,0.75) → LOOKUP(2,{0,0.75})
↑
IFの第1引数を配列定数にしたので、LOOKUPの第2引数はこれと同じことになります。
※IF関数は、 正式にはこう書くのでしょうけども、見た目長くなりますのでやらない。
↓
=IF({TRUE,FALSE},0,0.75)
それで、上のLOOKUPの形・・・ 時々見かけませんか？ 検索値が第二引数のどれよりも大きかったら、一番最後の数値をヒットすると云う数式です。
時刻データは1より小さいですよね、１日未満なんですから。 そこで2を検索値にすれば、時刻データ、または(それがエラーなら) 0が返ることになります。
じゃ、２でなく１を検索値にすればいいじゃないか、と云われそうですね。
・・・・仰るとおりです。武士の情、お許しを m(__)m
少しは余裕を持ちたいじゃないですかぁ、人情として・・・・
(半平太) 2011/04/27 19:31
半平太様、大変詳しくご説明して下さりありがとうございます。 今回の数式で、初めて分かったことの連続で感動してる程度のレベルで恐縮なのですが 質問してもよろしいでしょうか？
> =IF({TRUE,FALSE},0,0.75)
この式のように、IFの論理式に"TRUE"と"FALSE"を使うやり方も初めて知り 「真の場合」に"０"がある理由も分らなかったのですが、 これは、"0"が0.1よりも小さく、エラー値より大きい（?)からということでしょうか？
>それで、上のLOOKUPの形・・・ 時々見かけませんか？ 検索値が第二引数のどれよりも大きかったら、一番最後の数値をヒットすると云う数式です。
今回初めて見ました・・・。 そんなものをヒットするんですね・・・！ それで謎が解けました。
>時刻データは1より小さいですよね、１日未満なんですから。 そこで2を検索値にすれば、時刻データ、または(それがエラーなら) 0が返ることになります。
そういうことだったんですね！ それでLOOKUPの意味合いがわかってきました。
>最終便がなくなると、次便はないので、何も手当てしないとエラーになっちゃいます。 つまり、エラーをどうやって空白に見せるかと云う対策の一種です。
IFとLOOKUPの意味が大分わかってきました。 それでこの対策が使われたのですね。
配列数式は頭の中が数字だらけになりそうですが 引き続き解読していきたいと思います。
（K・Y)
> > =IF({TRUE,FALSE},0,0.75)
>
> この式のように、IFの論理式に"TRUE"と"FALSE"を使うやり方も初めて知り
> 「真の場合」に"０"がある理由も分らなかったのですが、
> これは、"0"が0.1よりも小さく、エラー値より大きい（?)からということでしょうか？
どこから0.1が出てきたのかですか？
真の場合に0にしたと云うより、配列の左に0を置きたかったからです。 偽の場合に0にしたら、0が配列の一番最後の数値になるので、 上述LOOKUP関数は、いつも0を返してくることになってしまいます。
>検索値が第二引数のどれよりも大きかったら、一番最後の数値をヒットすると云う数式です。 一番最後とは最右端と云うことです。そこに数式の返り値を置かないと意味がないです。 エラー値は、無視されるので大きさは関係ありません。
(半平太) 2011/04/27 23:34
半平太様、ありがとうございます。 すみません。私は大きな勘違いをしてました・・・。
> これは、"0"が0.1よりも小さく、エラー値より大きい（?)からということでしょうか？ >どこから0.1が出てきたのかですか？
>それで、上のLOOKUPの形・・・ 時々見かけませんか？ >検索値が第二引数のどれよりも大きかったら、一番最後の数値をヒットすると云う数式です。
こちらの文章の、「一番最後の数値」のところを「一番大きい数値」と思い込んでいました。
なので、
=IF({TRUE,FALSE},0,0.75) → 0.75
=IF({TRUE,FALSE},0,エラー値) → エラー値（"エラー値＜0"なのかなという思い込み）
という結果になるのかなと勝手に解釈していました。
なので、何故"0.1"を出したのかというと、そういう思い込みから、
単に"0"より大きい数値を出した・・・ということになります。
>真の場合に0にしたと云うより、配列の左に0を置きたかったからです。 偽の場合に0にしたら、0が配列の一番最後の数値になるので、 上述LOOKUP関数は、いつも0を返してくることになってしまいます。
これで、この意味もよくわかりました。 本当にありがとうございました。
最後にもう一つ質問があるのですが 関数の意味に集中してて、何となく後回しにしてたのですが
>INDEX((A4:A18&":"&B4:D18)*1,0)
この式の"*1"の1をかけた意味を教えていただけないでしょうか？ よろしくお願いいたします。
（K・Y)
>この式の"*1"の1をかけた意味
エクセルは色々と癖のある処理を行います。
一般的な時刻は「12:00」などと表示されますが、 実体は0.5(一日の半分)で、それを時刻に見せているだけです。 つまり、実体は数値と云うことになります。
では文字列の「12:00」は何でしょうか？ 文字列なんですから5字の文字です。当り前ですね (^^ゞ
=0.5="12:00" ←当然、これはFALSEになります。同じじゃないんですから。でも、
=0.5="12:00"*1 ならTRUEになります。
文字時刻("12:00")に対し四則演算をすると数値(0.5)にしてくれたことになります。
今回、最終的に時刻を数値で把握する積りなので「*1」で数値化させたものです。
※ 文字列と数値は違うのに、何故COUNTIFでは数値化しなかったのに上手く計算してくれたのか？
そう云われそうですが、ビルゲイツ君がそう作ったと云うしかないです。
下例のA12セルが、なぜ1を返すのか、ご自身で考えてみてください。
私のレスはこれで最終とさせていただきます。 m(__)m
(1) B2セル =A2 (2) C5セル =A5&":"&B5 (3) D5セル =C5*1
行 _____A_____ _______B_______ ____C____ _____D_____ 1 時刻データ 同左、標準書式 2 12:00 0.5 3 4 数値 数値 合成文字 合成文字*1 5 12 0 12:0 0.5 6 7 FALSE ←=0.5="12:00" 8 TRUE ←=0.5="12:00"*1 9 10 1 ←=COUNTIF(A2,C5) 11 1 ←=COUNTIF(A2,C5*1)
12 1 ←=COUNTIF(A13,"12E2") 13 1200
(半平太) 2011/04/28 13:41
半平太様、ありがとうございます。
>文字時刻("12:00")に対し四則演算をすると数値(0.5)にしてくれたことになり ます。
今回、最終的に時刻を数値で把握する積りなので「*1」で数値化させたものです。
そういうことだったのですね。 ご提示して頂いた例の内容を勉強させていただき、 文字時刻が四則演算によって数値化することや COUNTIFの不思議な計算力などがよく分かりました。
>下例のA12セルが、なぜ1を返すのか、ご自身で考えてみてください。
こちらの方は全くわかりませんが じっくり考えさせていきたいと思います。
半平太様、今回、たくさんのことを分かりやすく教えていただき 大変勉強になりました。 Excelは本当に知らないことばかりなのだなあと 改めて思いました。
長い間お付き合いいただき、本当にありがとうございました。
フェンリル様、ご説明いただきありがとうございました。
（K・Y)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.