Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


11322 / 13646 ツリー ←次へ | 前へ→

【16722】時間計算について教えて下さい ackkn 04/8/7(土) 0:39 質問[未読]
【16724】Re:時間計算について教えて下さい ichinose 04/8/7(土) 7:53 発言[未読]
【16725】Re:時間計算について教えて下さい ackkn 04/8/7(土) 11:04 回答[未読]
【16735】Re:時間計算について教えて下さい ichinose 04/8/7(土) 18:14 発言[未読]
【16741】Re:時間計算について教えて下さい 訂正 ichinose 04/8/7(土) 20:38 発言[未読]
【16744】Re:時間計算について教えて下さい 訂正 ackkn 04/8/7(土) 22:26 質問[未読]
【16746】Re:時間計算について教えて下さい 訂正 ichinose 04/8/7(土) 23:15 発言[未読]
【16756】Re:時間計算について教えて下さい 訂正 ackkn 04/8/8(日) 21:46 お礼[未読]

【16722】時間計算について教えて下さい
質問  ackkn  - 04/8/7(土) 0:39 -

引用なし
パスワード
   ackknと申します。
どなたかご教示下さい、よろしくお願いします。
時間計算で困っています、作業実績データの中に、昼休み時間が含まれたデータは昼休み時間(1H)を差し引くという物です。

担当者コード 担当者名 開始時刻 終了時刻 昼休 所要時間 所要分
 25638  山田花子  11:03   13:19  0:00  2:16  136

上記のような作業実績データが複数行あります、上記の開始時刻、終了時刻には、
実データとして 日付、時間が "2004/8/5 11:03:38" このような形で入っています。(これらの実績データはバーコードハンディーターミナルにて収集されている関係上)
で、上記の昼休の算出をしたいのですが、昼休みは 11:30〜12:30です、よって、
上記データは昼休み時間を含んでいる事になりますので、昼休のセルに"1:00"をセットしたいのです。
昼休のセルに"1:00"がセットできれば、その分を所要時間から引けばOKです。
関数でもいけそうな気がするのですが、なかなか上手くいきません。
どうかよろしくお願い致します。

【16724】Re:時間計算について教えて下さい
発言  ichinose  - 04/8/7(土) 7:53 -

引用なし
パスワード
   ▼ackkn さん:
おはようございます。

>どなたかご教示下さい、よろしくお願いします。
>時間計算で困っています、作業実績データの中に、昼休み時間が含まれたデータは昼休み時間(1H)を差し引くという物です。
>
>担当者コード 担当者名 開始時刻 終了時刻 昼休 所要時間 所要分
> 25638  山田花子  11:03   13:19  0:00  2:16  136
>
>上記のような作業実績データが複数行あります、上記の開始時刻、終了時刻には、
>実データとして 日付、時間が "2004/8/5 11:03:38" このような形で入っています。(これらの実績データはバーコードハンディーターミナルにて収集されている関係上)
>で、上記の昼休の算出をしたいのですが、昼休みは 11:30〜12:30です、よって、
>上記データは昼休み時間を含んでいる事になりますので、昼休のセルに"1:00"をセットしたいのです。
>昼休のセルに"1:00"がセットできれば、その分を所要時間から引けばOKです。
>関数でもいけそうな気がするのですが、なかなか上手くいきません。
>どうかよろしくお願い致します。

作業開始時刻------a
作業終了時刻------b
昼休み開始時刻----c
昼休み終了時刻----d

この四つの時刻の並び方で想定されるのは

a-b-c-d  午前中(昼休み前)に作業終了
a-c-b-d  昼休みの途中で作業終了
a-c-d-b  オーソドックスなパターン
c-a-b-d  昼休み中だけ作業(実質ありえない)
c-d-a-b  午後(昼休み終了後)から作業開始

基本の計算は、

MIN(B,D)-MAX(A,C)

で可能ですよね(但し、昼休み時間として、0:30という解も出てきます、いや、でてこないと単純には作業時間-昼休み時間という計算で実作業時間はえられませんよね?)

後は、上記の計算をするかしないかの場合分けです。


「=IF(AND(MIN(B,D)=D,MAX(A,C)=A),
    TIMEVALUE("1:00"),
   IF(MIN(B,D)-MAX(A,C)<=0,
       0,
      MIN(B,D)-MAX(A,C)))」

AND(MIN(B,D)=D,MAX(A,C)=A)で、ありえないパターンを見つけています。
この時は、昼休み時間 1:00
後半は、「MIN(B,D)-MAX(A,C)」の値が正か負かで分岐しました。

実際にはa,b,c,dをセルアドレスに変えて試してみて下さい。
(昼休み開始、時間もどこかのセルに入れておいたほうがよいですよ!!
 めったに変わらないだろうけど、可能性は0ではないから)

時間が交差したときの細かい計算方法が不明なので独断で行いました。

【16725】Re:時間計算について教えて下さい
回答  ackkn  - 04/8/7(土) 11:04 -

引用なし
パスワード
   ▼ichinose さん:
>▼ackkn さん:
>おはようございます。
>
>>どなたかご教示下さい、よろしくお願いします。
>>時間計算で困っています、作業実績データの中に、昼休み時間が含まれたデータは昼休み時間(1H)を差し引くという物です。
>>
>>担当者コード 担当者名 開始時刻 終了時刻 昼休 所要時間 所要分
>> 25638  山田花子  11:03   13:19  0:00  2:16  136
>>
>>上記のような作業実績データが複数行あります、上記の開始時刻、終了時刻には、
>>実データとして 日付、時間が "2004/8/5 11:03:38" このような形で入っています。(これらの実績データはバーコードハンディーターミナルにて収集されている関係上)
>>で、上記の昼休の算出をしたいのですが、昼休みは 11:30〜12:30です、よって、
>>上記データは昼休み時間を含んでいる事になりますので、昼休のセルに"1:00"をセットしたいのです。
>>昼休のセルに"1:00"がセットできれば、その分を所要時間から引けばOKです。
>>関数でもいけそうな気がするのですが、なかなか上手くいきません。
>>どうかよろしくお願い致します。
>
>作業開始時刻------a
>作業終了時刻------b
>昼休み開始時刻----c
>昼休み終了時刻----d
>
>この四つの時刻の並び方で想定されるのは
>
>a-b-c-d  午前中(昼休み前)に作業終了
>a-c-b-d  昼休みの途中で作業終了
>a-c-d-b  オーソドックスなパターン
>c-a-b-d  昼休み中だけ作業(実質ありえない)
>c-d-a-b  午後(昼休み終了後)から作業開始
>
>基本の計算は、
>
>MIN(B,D)-MAX(A,C)
>
>で可能ですよね(但し、昼休み時間として、0:30という解も出てきます、いや、でてこないと単純には作業時間-昼休み時間という計算で実作業時間はえられませんよね?)
>
>後は、上記の計算をするかしないかの場合分けです。
>
>
>「=IF(AND(MIN(B,D)=D,MAX(A,C)=A),
>    TIMEVALUE("1:00"),
>   IF(MIN(B,D)-MAX(A,C)<=0,
>       0,
>      MIN(B,D)-MAX(A,C)))」
>
>AND(MIN(B,D)=D,MAX(A,C)=A)で、ありえないパターンを見つけています。
>この時は、昼休み時間 1:00
>後半は、「MIN(B,D)-MAX(A,C)」の値が正か負かで分岐しました。
>
>実際にはa,b,c,dをセルアドレスに変えて試してみて下さい。
>(昼休み開始、時間もどこかのセルに入れておいたほうがよいですよ!!
> めったに変わらないだろうけど、可能性は0ではないから)
>
>時間が交差したときの細かい計算方法が不明なので独断で行いました。

ichinose さん ありがとうございました。

早速実際のデータで試してみましたが、私のやり方がまずいのか?、上手くいきません。

以下のように、昼休み開始終了は固定値、作業開始終了をセル参照で試しました。
但し、最初に書きました通り、作業開始終了には、実データとして "2004/8/5 11:03:38" のようなデータが入っています。

=IF(AND(MIN(N5,"12:30")="12:30",MAX(M5,"11:30")=M5),TIMEVALUE("1:00"),IF(MIN(N5,"12:30")-MAX(M5,"11:30")<=0,0,MIN(N5,"12:30")-MAX(M5,"11:30")))

N5:作業終了時間、M5:作業開始時間

これでいくと、常に結果はゼロになってしまいます。
よろしくご教示下さい。

【16735】Re:時間計算について教えて下さい
発言  ichinose  - 04/8/7(土) 18:14 -

引用なし
パスワード
   ▼ackkn さん:
こんばんは。
遅くなりました(一日、スズメバチと格闘してたもので・・・)。


>
>以下のように、昼休み開始終了は固定値、作業開始終了をセル参照で試しました。
>但し、最初に書きました通り、作業開始終了には、実データとして "2004/8/5 11:03:38" のようなデータが入っています。

あっ、これ考慮してませんでした(日付分引いときましょう)。しかも、ちょっと
間違ってました。

但し、この"2004/8/5 11:03:38"って、書式は、標準か日付の「yyyy/m/d h:m:s」の
場合を想定しています。つまり、文字列ではないものです。

>=IF(AND(MIN(N5,"12:30")="12:30",MAX(M5,"11:30")=M5),TIMEVALUE("1:00"),IF(MIN(N5,"12:30")-MAX(M5,"11:30")<=0,0,MIN(N5,"12:30")-MAX(M5,"11:30")))
>
>N5:作業終了時間、M5:作業開始時間
>
>これでいくと、常に結果はゼロになってしまいます。
>よろしくご教示下さい。
「=IF(AND(MAX(N5-INT(N5),TIMEVALUE("12:30"))=TIMEVALUE("12:30"),
  MAX(M5-INT(M5),TIMEVALUE("11:30"))=M5-INT(M5)),
  TIMEVALUE("1:00"),
  IF(MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")<=0,
    0,
    MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")))」
これで確認して下さい。
但し、数式を入れるセルの書式は日付の「h:m」にして下さい。

もう一度、再度確認願います。

【16741】Re:時間計算について教えて下さい 訂正
発言  ichinose  - 04/8/7(土) 20:38 -

引用なし
パスワード
   >>
>>これでいくと、常に結果はゼロになってしまいます。
>>よろしくご教示下さい。
>「=IF(AND(MAX(N5-INT(N5),TIMEVALUE("12:30"))=TIMEVALUE("12:30"),
>  MAX(M5-INT(M5),TIMEVALUE("11:30"))=M5-INT(M5)),
>  TIMEVALUE("1:00"),
>  IF(MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")<=0,
>    0,
>    MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")))」
>これで確認して下さい。
但し、数式を入れるセルの書式は日付ではなく、時間の「h:m」にして下さい。
>
もう一度、再度(←なんだろう、これ・・失礼しました)、確認願います。

【16744】Re:時間計算について教えて下さい 訂正
質問  ackkn  - 04/8/7(土) 22:26 -

引用なし
パスワード
   ▼ichinose さん:
>>>
>>>これでいくと、常に結果はゼロになってしまいます。
>>>よろしくご教示下さい。
>>「=IF(AND(MAX(N5-INT(N5),TIMEVALUE("12:30"))=TIMEVALUE("12:30"),
>>  MAX(M5-INT(M5),TIMEVALUE("11:30"))=M5-INT(M5)),
>>  TIMEVALUE("1:00"),
>>  IF(MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")<=0,
>>    0,
>>    MIN(N5-INT(N5),"12:30")-MAX(M5-INT(M5),"11:30")))」
>>これで確認して下さい。
>但し、数式を入れるセルの書式は日付ではなく、時間の「h:m」にして下さい。
>>
>もう一度、再度(←なんだろう、これ・・失礼しました)、確認願います。

式の内容解読でいっぱいいっぱいで、気付いていませんでした、(^^;)
「h:m」を見て時間だと思い込んでいましたので...

で!、なりました、なりました!(-_-)<- なのにこの顔は何?
なったらなったで、小さな事が気になって...
と言うのも、最初の例のようにすっきりした時間データ(昼休みがすっぽり作業時間内に入っているケース)はバッチリです。
でも、数千のデータをチェックしていたら、どんよりしたデータを見つけてしまい...それが先程の顔の原因です。
どんよりしたデータとは、作業時間の終了時刻が、昼休み時間にちょっと掛かっているようなデータです(例えば、11:38 に終了とか)。
この場合、8分と表示されます、こんなどんよりデータまで10分以内はゼロと表示しようなどと考え出すと、今度は午後の開始時間がちょっと掛かっているどんより2も、俺も俺もと言い出すし、これらはまとめて無視したが無難かなと...

で、最初のすっきりデータ(1:00表示)の場合のみ、その昼休み時間のセル(h:m書式)を黄色で塗る事は可能でしょうか?

勝手ばかり言って申し訳ありませんが、よろしくご教示下さい。

【16746】Re:時間計算について教えて下さい 訂正
発言  ichinose  - 04/8/7(土) 23:15 -

引用なし
パスワード
   ▼ackkn さん:
こんばんは。


>
>で、最初のすっきりデータ(1:00表示)の場合のみ、その昼休み時間のセル(h:m書式)を黄色で塗る事は可能でしょうか?
これは、条件付書式を使用すると可能ですよ!!
昼休み時間を計算する数式を設定したセル範囲を選択した状態で、

・「書式」----「条件付書式」とクリックすると、条件付書式の設定という
  ダイアログが表示されます。

・ここで、「セルの値が」----「次の値に等しい」を選択し、
 その右隣のセルに 1:00(""とかは付けない)と指定します。

・「書式」ボタンをクリックすると「セルの書式設定」ダイアログが表示されますから
 ここの「パターン」でセルの色を選択し、OKをクリック。

・再度、条件付書式の設定に戻りますから、OKをクリックします。

これで設定完了です。1:00のセルが指定した色で塗りつぶされるはずです。

確認して下さい。

【16756】Re:時間計算について教えて下さい 訂正
お礼  ackkn  - 04/8/8(日) 21:46 -

引用なし
パスワード
   ▼ichinose さん:
>▼ackkn さん:
>こんばんは。
>
>
>>
>>で、最初のすっきりデータ(1:00表示)の場合のみ、その昼休み時間のセル(h:m書式)を黄色で塗る事は可能でしょうか?
>これは、条件付書式を使用すると可能ですよ!!
>昼休み時間を計算する数式を設定したセル範囲を選択した状態で、
>
>・「書式」----「条件付書式」とクリックすると、条件付書式の設定という
>  ダイアログが表示されます。
>
>・ここで、「セルの値が」----「次の値に等しい」を選択し、
> その右隣のセルに 1:00(""とかは付けない)と指定します。
>
>・「書式」ボタンをクリックすると「セルの書式設定」ダイアログが表示されますから
> ここの「パターン」でセルの色を選択し、OKをクリック。
>
>・再度、条件付書式の設定に戻りますから、OKをクリックします。
>
>これで設定完了です。1:00のセルが指定した色で塗りつぶされるはずです。
>
>確認して下さい

なりました、なりました!
色々と本当にありがとうございました。

11322 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free