Excel VBA質問箱 IV

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

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


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

【39955】時間の丸めについて kaoru 06/7/4(火) 16:21 質問[未読]
【39958】Re:時間の丸めについて ハチ 06/7/4(火) 16:28 発言[未読]
【39959】Re:時間の丸めについて kaoru 06/7/4(火) 16:31 発言[未読]
【39964】Re:時間の丸めについて ハチ 06/7/4(火) 17:07 回答[未読]
【39969】Re:時間の丸めについて kaoru 06/7/4(火) 17:37 お礼[未読]
【39963】Re:時間の丸めについて kaoru 06/7/4(火) 17:05 発言[未読]
【39968】Re:時間の丸めについて ハチ 06/7/4(火) 17:24 回答[未読]
【39971】Re:時間の丸めについて kaoru 06/7/4(火) 17:55 質問[未読]
【39976】Re:時間の丸めについて ichinose 06/7/4(火) 20:08 発言[未読]
【39998】Re:時間の丸めについて kaoru 06/7/5(水) 8:56 お礼[未読]
【40009】Re:時間の丸めについて ichinose 06/7/5(水) 13:42 発言[未読]
【40057】Re:時間の丸めについて kaoru 06/7/6(木) 10:13 お礼[未読]

【39955】時間の丸めについて
質問  kaoru  - 06/7/4(火) 16:21 -

引用なし
パスワード
   範囲指定された場所(セル番地B2〜B10)に入力された
   b
1  6:15
2  8:20
3 10:15
4  9:55
5 
6  6:45
7 10:05
8  6:45
9
10 6:20


同じ場所(セル番地B2〜B10)に変換された時間を入力する
   b
1  7:00
2  8:30
3 10:30
4 10:00
5 
6  7:00
7 10:30
8  7:00
9
10 7:00

b列の値が7:00以前であれば7:00に置き換え
b列の値が8:00〜8:29であれば8:30に置き換え
b列の値が9:30〜10:29であれば10:30に置き換え
という事なのですが。

Range("B3").Value = ここになにを入れたらよいのか分かりません。
B3のセルの場合のコマンドだけでもいいです教えてください。
よろしくお願いいたします。

【39958】Re:時間の丸めについて
発言  ハチ  - 06/7/4(火) 16:28 -

引用なし
パスワード
   ▼kaoru さん:
>範囲指定された場所(セル番地B2〜B10)に入力された
>   b
>1  6:15
>2  8:20
>3 10:15
>4  9:55
>5 
>6  6:45
>7 10:05
>8  6:45
>9
>10 6:20
>を
>
>同じ場所(セル番地B2〜B10)に変換された時間を入力する
>   b
>1  7:00
>2  8:30
>3 10:30
>4 10:00
>5 
>6  7:00
>7 10:30
>8  7:00
>9
>10 7:00
>
>b列の値が7:00以前であれば7:00に置き換え
>b列の値が8:00〜8:29であれば8:30に置き換え
>b列の値が9:30〜10:29であれば10:30に置き換え
>という事なのですが。
>
>Range("B3").Value = ここになにを入れたらよいのか分かりません。
>B3のセルの場合のコマンドだけでもいいです教えてください。
>よろしくお願いいたします。

丸めの基準を明確したほうが良いのでは?
9:10ならどっちですか?

【39959】Re:時間の丸めについて
発言  kaoru  - 06/7/4(火) 16:31 -

引用なし
パスワード
   ▼ハチ さん:
こんにちは、早速の回答ありがとうございます。
9:10の場合は10:30です。
値を置き換えた後任意でも変更できるように
セルに関数を入れたくないのです。
>
>丸めの基準を明確したほうが良いのでは?
>9:10ならどっちですか?

【39963】Re:時間の丸めについて
発言  kaoru  - 06/7/4(火) 17:05 -

引用なし
パスワード
   ▼ハチ さん:
>丸めの基準を明確したほうが良いのでは?
>9:10ならどっちですか?
7:00以下なら7:00です
7:00以上8:30以下なら8:30です
8:30以上9:00以下なら9:00です
9:00以上10:30以下なら10:30です。
よろしくお願いいたします。
関数で表現すると(:はエラーになりますが・・)
=if(H14<7:00,7:00,if(and(H14<8:30,H14>7:00),8:30,(if(and(H14<9:00,H14>8:30),9:00,(if(and(H14<10:30,H14>9:00),10:30,h14))))
 です。

【39964】Re:時間の丸めについて
回答  ハチ  - 06/7/4(火) 17:07 -

引用なし
パスワード
   ▼kaoru さん:
>▼ハチ さん:
>こんにちは、早速の回答ありがとうございます。
>9:10の場合は10:30です。

??
9:30ではなく10:30ですか?
よくわかりません。
Function内を変更してください。

ユーザー定義関数を作る方法でやってみました。
作業列にC列を使ってますので困るようだったら
Offsetの値を変更してください。
もっと簡単な方法がありそうな気がします。

Option Explicit

Sub Time_test()

'C列を作業列に使う。困る場合はOffsetの値を変更
With ActiveSheet
  With .Range("B1", .Range("B65536").End(xlUp))
    .Offset(, 1).Formula = "=Time_Round(B1)"
    .Value = .Offset(, 1).Value
    .Offset(, 1).Clear
  End With
End With

End Sub

Function Time_Round(OrgT As Date) As Date

Dim H, M As Integer 'H時、M分

H = Hour(OrgT)
M = Minute(OrgT)

'このへんは適当に修正してください
Select Case M
  Case Is < 10
    M = 0
  Case Is < 45
    M = 30
  Case Else
    M = 0
    H = H + 1
End Select

'24時以降は0:00に
If H = 24 Then
  H = 0
  M = 0
End If

Time_Round = CDate(H & ":" & M)

End Function

【39968】Re:時間の丸めについて
回答  ハチ  - 06/7/4(火) 17:24 -

引用なし
パスワード
   ▼kaoru さん:
>▼ハチ さん:
>>丸めの基準を明確したほうが良いのでは?
>>9:10ならどっちですか?
>7:00以下なら7:00です
>7:00以上8:30以下なら8:30です
>8:30以上9:00以下なら9:00です
>9:00以上10:30以下なら10:30です。
>よろしくお願いいたします。
>関数で表現すると(:はエラーになりますが・・)
>=if(H14<7:00,7:00,if(and(H14<8:30,H14>7:00),8:30,(if(and(H14<9:00,H14>8:30),9:00,(if(and(H14<10:30,H14>9:00),10:30,h14))))
> です。

10:30以降は・・
なんでしょう?

Option Explicit

Sub Time_test()

'C列を作業列に使う。困る場合はOffsetの値を変更
With ActiveSheet
  With .Range("B1", .Range("B65536").End(xlUp))
    .Offset(, 1).Formula = "=Time_Round(B1)"
    .Value = .Offset(, 1).Value
    .Offset(, 1).Clear
  End With
End With

End Sub

Function Time_Round(OrgT As Date) As Date

Select Case OrgT
  Case Is <= CDate("7:00")
    Time_Round = CDate("7:00")
  Case Is <= CDate("8:30")
    Time_Round = CDate("8:30")
  Case Is <= CDate("9:00")
    Time_Round = CDate("9:00")
  Case Is <= CDate("10:30")
    Time_Round = CDate("10:30")
  Case Else
    'それ以降は・・適当に12:00
    Time_Round = CDate("12:00")
  End Select

End Function

【39969】Re:時間の丸めについて
お礼  kaoru  - 06/7/4(火) 17:37 -

引用なし
パスワード
   ▼ハチ さん:
>▼kaoru さん:
ご回答ありがとうございました。
 判断が分かりにくいので、vlookup関数で表現しようかと悩んでいました。
ありがとうございます。
>>▼ハチ さん:
>9:30ではなく10:30ですか?
>よくわかりません。
>Function内を変更してください。
>
>ユーザー定義関数を作る方法でやってみました。
>作業列にC列を使ってますので困るようだったら
>Offsetの値を変更してください。
>もっと簡単な方法がありそうな気がします。
>
>Option Explicit
>
>Sub Time_test()
>
>'C列を作業列に使う。困る場合はOffsetの値を変更
>With ActiveSheet
>  With .Range("B1", .Range("B65536").End(xlUp))
>    .Offset(, 1).Formula = "=Time_Round(B1)"
>    .Value = .Offset(, 1).Value
>    .Offset(, 1).Clear
>  End With
>End With
>
>End Sub
>
>Function Time_Round(OrgT As Date) As Date
>
>Dim H, M As Integer 'H時、M分
>
>H = Hour(OrgT)
>M = Minute(OrgT)
>
>'このへんは適当に修正してください
>Select Case M
>  Case Is < 10
>    M = 0
>  Case Is < 45
>    M = 30
>  Case Else
>    M = 0
>    H = H + 1
>End Select
>
>'24時以降は0:00に
>If H = 24 Then
>  H = 0
>  M = 0
>End If
>
>Time_Round = CDate(H & ":" & M)
>
>End Function

【39971】Re:時間の丸めについて
質問  kaoru  - 06/7/4(火) 17:55 -

引用なし
パスワード
   ▼ハチ さん:
ご回答さらにありがとうございます。
ただ、実行後にb1から時間入力セルに#NAME?
と出ます。
10:30以降は書き換えしません。
すみません。

>▼kaoru さん:
>>▼ハチ さん:
>>>丸めの基準を明確したほうが良いのでは?
>>>9:10ならどっちですか?
>>7:00以下なら7:00です
>>7:00以上8:30以下なら8:30です
>>8:30以上9:00以下なら9:00です
>>9:00以上10:30以下なら10:30です。
>>よろしくお願いいたします。
>>関数で表現すると(:はエラーになりますが・・)
>>=if(H14<7:00,7:00,if(and(H14<8:30,H14>7:00),8:30,(if(and(H14<9:00,H14>8:30),9:00,(if(and(H14<10:30,H14>9:00),10:30,h14))))
>> です。
>
>10:30以降は・・
>なんでしょう?
>
>Option Explicit
>
>Sub Time_test()
>
>'C列を作業列に使う。困る場合はOffsetの値を変更
>With ActiveSheet
>  With .Range("B1", .Range("B65536").End(xlUp))
>    .Offset(, 1).Formula = "=Time_Round(B1)"
>    .Value = .Offset(, 1).Value
>    .Offset(, 1).Clear
>  End With
>End With
>
>End Sub
>
>Function Time_Round(OrgT As Date) As Date
>
>Select Case OrgT
>  Case Is <= CDate("7:00")
>    Time_Round = CDate("7:00")
>  Case Is <= CDate("8:30")
>    Time_Round = CDate("8:30")
>  Case Is <= CDate("9:00")
>    Time_Round = CDate("9:00")
>  Case Is <= CDate("10:30")
>    Time_Round = CDate("10:30")
>  Case Else
>    'それ以降は・・適当に12:00
>    Time_Round = CDate("12:00")
>  End Select
>
>End Function

【39976】Re:時間の丸めについて
発言  ichinose  - 06/7/4(火) 20:08 -

引用なし
パスワード
   こんばんは。
>範囲指定された場所(セル番地B2〜B10)に入力された
>   b
>1  6:15
>2  8:20
>3 10:15
>4  9:55
>5 
>6  6:45
>7 10:05
>8  6:45
>9
>10 6:20

アクティブシートのB列に上記のようなデータがあったとします。
(時刻は、シリアル値)


標準モジュールに
'=========================================
Sub test()
  Dim tmarray As Variant
  Dim rng As Range
  Dim crng As Range
  Dim ans As Variant
  tmarray = Array([TimeValue("10:30")], [TimeValue("9:00")], [TimeValue("8:30")], [TimeValue("7:00")])
  Set rng = Range("b1", Cells(Rows.Count, "b").End(xlUp))
  For Each crng In rng
    With crng
     If .Value <> "" Then
       ans = Application.Match(.Value, tmarray, -1)
       If Not IsError(ans) Then
        .Value = tmarray(ans - 1)
        End If
       End If
     End With
    Next
  Erase tmarray
End Sub


でtestを実行して試してみて下さい。
上記は、10:30以降は、変化しません。

【39998】Re:時間の丸めについて
お礼  kaoru  - 06/7/5(水) 8:56 -

引用なし
パスワード
   ▼ichinose さん:
ichinose様 おはようございます。
早速ためしてみました、完璧です!
ありがとうございました。
ちなみに

>  tmarray = Array([TimeValue("10:30")], [TimeValue("9:00")], [TimeValue("8:30")], [TimeValue("7:00")])
>  Set rng = Range("b1", Cells(Rows.Count, "b").End(xlUp))
>  For Each crng In rng
>    With crng
>     If .Value <> "" Then
>       ans = Application.Match(.Value, tmarray, -1)
>       If Not IsError(ans) Then
>        .Value = tmarray(ans - 1)
>        End If
>       End If
>     End With
>    Next
>  Erase tmarray
 の解説を簡単にお願いいたします。よろしくお願いいたします。

【40009】Re:時間の丸めについて
発言  ichinose  - 06/7/5(水) 13:42 -

引用なし
パスワード
   ▼kaoru さん:
こんにちは。

ちょっと訂正
>
   tmarray = Array(TimeValue("10:30"), TimeValue("9:00"), TimeValue("8:30"),TimeValue("7:00"))
'[]での囲みを取りました。付けておいても不具合はないですが、意味としては
'取ったほうが良いと思います。
>>  Set rng = Range("b1", Cells(Rows.Count, "b").End(xlUp))
>>  For Each crng In rng
>>    With crng
>>     If .Value <> "" Then
>>       ans = Application.Match(.Value, tmarray, -1)
>>       If Not IsError(ans) Then
>>        .Value = tmarray(ans - 1)
>>        End If
>>       End If
>>     End With
>>    Next
>>  Erase tmarray

> の解説を簡単にお願いいたします。

これだけのコードですから、HELP等を利用して解読してみてください。
その上で不明な点は具体的に記述してください。

このコードは、「ワークシート関数のMatchをVBAで使う」
がキーポイントです。
   tmarray = Array(TimeValue("10:30"), TimeValue("9:00"), TimeValue("8:30"),TimeValue("7:00"))

というコードで配列を作っていますが、この仕様だと本来は
コード内に定数(10:30や9:00や8:30や7:00の記述です)を記述すべきでは
ないと思っています。

だって、将来、10:30 が 11:00に変わる可能性は十分考えられますからねえ!!
また、基準になる時刻が増える可能性も否定できませんよね?

本当は、予めセルに上記の時刻を入力しておけば、時刻の変更や基準になる時刻が
増えてもコードの変更をしなくても済むと思いますよ!!

こんなところが上記のコードのコンセプトです。

【40057】Re:時間の丸めについて
お礼  kaoru  - 06/7/6(木) 10:13 -

引用なし
パスワード
   ▼ichinose さん:
ichinoseさんありがとうございました。
解説もありがとうございました。

>▼kaoru さん:
>こんにちは。
>
>ちょっと訂正
>>
>   tmarray = Array(TimeValue("10:30"), TimeValue("9:00"), TimeValue("8:30"),TimeValue("7:00"))
>'[]での囲みを取りました。付けておいても不具合はないですが、意味としては
>'取ったほうが良いと思います。
>>>  Set rng = Range("b1", Cells(Rows.Count, "b").End(xlUp))
>>>  For Each crng In rng
>>>    With crng
>>>     If .Value <> "" Then
>>>       ans = Application.Match(.Value, tmarray, -1)
>>>       If Not IsError(ans) Then
>>>        .Value = tmarray(ans - 1)
>>>        End If
>>>       End If
>>>     End With
>>>    Next
>>>  Erase tmarray
>
>> の解説を簡単にお願いいたします。
>
>これだけのコードですから、HELP等を利用して解読してみてください。
>その上で不明な点は具体的に記述してください。
>
>このコードは、「ワークシート関数のMatchをVBAで使う」
>がキーポイントです。
>   tmarray = Array(TimeValue("10:30"), TimeValue("9:00"), TimeValue("8:30"),TimeValue("7:00"))
>
>というコードで配列を作っていますが、この仕様だと本来は
>コード内に定数(10:30や9:00や8:30や7:00の記述です)を記述すべきでは
>ないと思っています。
>
>だって、将来、10:30 が 11:00に変わる可能性は十分考えられますからねえ!!
>また、基準になる時刻が増える可能性も否定できませんよね?
>
>本当は、予めセルに上記の時刻を入力しておけば、時刻の変更や基準になる時刻が
>増えてもコードの変更をしなくても済むと思いますよ!!
>
>こんなところが上記のコードのコンセプトです。

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