Excel VBA質問箱 IV

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

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


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

【13332】時間帯の重なり除去 sこー 04/5/1(土) 20:27 質問[未読]
【13337】Re:時間帯の重なり除去 bykin 04/5/2(日) 12:12 回答[未読]
【13351】Re:時間帯の重なり除去 sこー 04/5/2(日) 21:23 質問[未読]
【13398】Re:時間帯の重なり除去 ちん 04/5/4(火) 13:29 回答[未読]
【13399】Re:時間帯の重なり除去 ちん 04/5/4(火) 13:57 発言[未読]
【13577】Re:時間帯の重なり除去 sこー 04/5/7(金) 23:42 お礼[未読]
【13404】Re:時間帯の重なり除去 Hirofumi 04/5/4(火) 16:21 回答[未読]

【13332】時間帯の重なり除去
質問  sこー  - 04/5/1(土) 20:27 -

引用なし
パスワード
   皆様こんばんは。いつもいつもありがとうございます。

この度、会社で外にでている時間帯を計算する事になったのですが、
2人以上いると重なってしまう時間帯がでてきまして、
その処理をどうすればよいのかわからず悩んでいます。


    A   B   
1   9:30 10:30   ・・・・・・・(60分)
2  11:40 12:30   ・・・・・・・(50分)
3  12:00 12:40   ・・・・・・・(40分)
4  13:00 13:50   ・・・・・・・(50分)


Bの時間からAの時間を引き、最後に合計する為に

a = DateDiff("n", Range("a1"), Range("b1")) 

で引き算し、forで1〜4行を計算し、合計を出したいのですが、
そのまま計算してしまうと全ての合計時間の200分と表示されます。
実際には2行目と3行目は時間帯が重なっており、11:40〜12:40なので60分、
実際の合計時間は170分になりますよね。
重なりを除去して合計できるような事は可能でしょうか?

さらにここに

5   9:00 14:00  ・・・・・・・(300分)

と追加されれば、1〜4行をすべてカウントせず5行目だけの合計を出さなければならず、不勉強の私には考えつかない為、セルに色々計算式をいれて間違いだらけになりながらも、どうにか現状をしのいでおります。

皆様、どうか良い方法があれば、ご教授ください。
どうぞ、よろしくお願い致します.

【13337】Re:時間帯の重なり除去
回答  bykin  - 04/5/2(日) 12:12 -

引用なし
パスワード
   こんにちわ。

マクロやのうて、数式でしかも作業列を思いっきり使うベタベタの方法(^^;;

「前提条件」
●勤務時間は9:00〜22:00
●時間は10分単位

1.ご提示の例のように1行目からデータが入っている場合は1行目に1行挿入する。
  →例の場合はデータ範囲がA2:B5となる
2.C1に9:00、C2に9:10と入れて、C1:C2を選択してCB1までドラッグする。
  →CB1には21:50と表示される
3.C2に=IF(AND(ROUND($A2,6)<=ROUND(C$1,6),ROUND($B2,6)>ROUND(C$1,6)),1,0)
  と入れて、CB5までコピーする。
4.C6に=SIGN(SUM(C2:C5))と入れてCB6までコピーする。
5.答えを求めたいセルに=SUM(C6:CB6)*10と入れる。

もし、どんどんデータを追加していく必要があるんなら↓こんな方法も・・・

1.ご提示の例のように1行目からデータが入っている場合は1行目に2行挿入する。
  →例の場合はデータ範囲がA3:B6となる
2.C1に9:00、C2に9:10と入れて、C1:C2を選択してCB1までドラッグする。
  →CB1には21:50と表示される
3.C2に=SIGN(SUM(INDIRECT(ADDRESS(3,COLUMN(C2))&":"&ADDRESS(COUNTA($A$3:$A$65526)+2,COLUMN(C2)))))
  と入れてCB2までコピーする。
4.C3に=IF(AND(ROUND($A3,6)<=ROUND(C$1,6),ROUND($B3,6)>ROUND(C$1,6)),1,0)
  と入れて、CB6までコピーする。
5.答えを求めたいセル(この方法の場合はA1〜B2の範囲内のセルがベター)に=SUM(C2:CB2)*10と入れる。

2つめの方法では[ツール]-[オプション]の「編集」タブの中で「リスト形式および数式を拡張する」に
チェックが入ってて、データが4行以上あったら、A・B列にデータを追加すればC列以降の数式は自動的に入ります。

ま、ええ方法が出てくるまでの繋ぎってことで・・・(^^;;
ほな。

【13351】Re:時間帯の重なり除去
質問  sこー  - 04/5/2(日) 21:23 -

引用なし
パスワード
   ▼bykin さん:
こんばんは。
早速のご回答ありがとうございます。

難しい数式ですね。勉強不足なので、意味をしっかり理解してみます。

質問しておいて大変申し訳ないのですが、
ただ今出先でして、パソコンにエクセルが入っておりません。
家に帰り次第、試させて頂きます。どうもありがとうございました。


皆様、私はVBAをかじりかけたばかりで、
ほとんどヨチヨチ歩き程度の知識ではよく解らないのですが、
やはり、VBAでどうこうしようとするのは、難しい事なのでしょうか?
宜しくお願いいたします。

【13398】Re:時間帯の重なり除去
回答  ちん  - 04/5/4(火) 13:29 -

引用なし
パスワード
   sこー さん こんにちは、ちんといいます。
タイムチャートで考えてみましょう。
C列を時間の確認ように使用しますので、属性:文字列にします。

考え方は、
午前0時〜24時までの横線(一本線)を引いて下さい。
0時10分から、10分刻みで縦線を23時50分まで引きましょう。
-----1-----2-----3-----4 ・・・ と、いうように。
例として、開始時間1:00〜終了時間2時30までの実績時間を求めるとしたら、
-----*********---3-----4 ・・・
というふうに、10分刻みで"*"マークに置き換えます。(*はダブリ防止用)

というふうに、ロジックを考えると、以下のようになります。

Sub ボタン1_Click()
 Dim C_bar As String
 Dim i1 As Long, i2 As Integer
 Dim Fun_bar As Integer
 Dim Fun As Integer  '<-- 10分単位の個数
 
 For i1 = 1 To 23
  C_bar = C_bar + "-----" + Right$(Format(i1, "00"), 1)
 Next i1
 For i1 = 1 To 4
  a = DateDiff("n", Cells(i1, 1).Value, Cells(i1, 2).Value)
  GoSub Time_chart
 Next i1

 MsgBox (Fun * 10) & "分作業しました。"
 Cells(10, 10).Value = C_bar
 Exit Sub

Time_chart:
 Fun_bar = a / 10 '<-- 10分単位で 1とする。(20分なら2となる)
 b = DateDiff("n", "0:00", Cells(i1, 1).Value) '<-- "*"を書き込む位置の計算
 For i2 = (b / 10) To (b / 10) + (Fun_bar - 1)
  If Mid(C_bar, i2, 1) <> "*" Then '<-- ダブリのチェック
   Fun = Fun + 1  '<-- 10分単位の個数を数える
   Mid(C_bar, i2, 1) = "*"
  End If
 Next i2
 Cells(i1, 3).Value = C_bar
 Return
 
End Sub

以上参考までに、

▼sこー さん:
>皆様こんばんは。いつもいつもありがとうございます。
>
>この度、会社で外にでている時間帯を計算する事になったのですが、
>2人以上いると重なってしまう時間帯がでてきまして、
>その処理をどうすればよいのかわからず悩んでいます。
>
>
>    A   B   
>1   9:30 10:30   ・・・・・・・(60分)
>2  11:40 12:30   ・・・・・・・(50分)
>3  12:00 12:40   ・・・・・・・(40分)
>4  13:00 13:50   ・・・・・・・(50分)
>
>
>Bの時間からAの時間を引き、最後に合計する為に
>
>a = DateDiff("n", Range("a1"), Range("b1")) 
>
>で引き算し、forで1〜4行を計算し、合計を出したいのですが、
>そのまま計算してしまうと全ての合計時間の200分と表示されます。
>実際には2行目と3行目は時間帯が重なっており、11:40〜12:40なので60分、
>実際の合計時間は170分になりますよね。
>重なりを除去して合計できるような事は可能でしょうか?
>
>さらにここに
>
>5   9:00 14:00  ・・・・・・・(300分)
>
>と追加されれば、1〜4行をすべてカウントせず5行目だけの合計を出さなければならず、不勉強の私には考えつかない為、セルに色々計算式をいれて間違いだらけになりながらも、どうにか現状をしのいでおります。
>
>皆様、どうか良い方法があれば、ご教授ください。
>どうぞ、よろしくお願い致します.

【13399】Re:時間帯の重なり除去
発言  ちん  - 04/5/4(火) 13:57 -

引用なし
パスワード
   こんにちは、ちんといいます。
補足です。
答えのタイムチャートを表示するとき、
9時から18時まで表示すれば良いとおもうので、
以下の※印を参考にして下さい。
(MID命令で、9時〜18時までのタイムチャートをC列にセット)

以上、

▼ちん さん:
>sこー さん こんにちは、ちんといいます。
>タイムチャートで考えてみましょう。
>C列を時間の確認ように使用しますので、属性:文字列にします。
>
>考え方は、
>午前0時〜24時までの横線(一本線)を引いて下さい。
>0時10分から、10分刻みで縦線を23時50分まで引きましょう。
>-----1-----2-----3-----4 ・・・ と、いうように。
>例として、開始時間1:00〜終了時間2時30までの実績時間を求めるとしたら、
>-----*********---3-----4 ・・・
>というふうに、10分刻みで"*"マークに置き換えます。(*はダブリ防止用)
>
>というふうに、ロジックを考えると、以下のようになります。
>
>Sub ボタン1_Click()
> Dim C_bar As String
> Dim i1 As Long, i2 As Integer
> Dim Fun_bar As Integer
> Dim Fun As Integer  '<-- 10分単位の個数
> 
> For i1 = 1 To 23
>  C_bar = C_bar + "-----" + Right$(Format(i1, "00"), 1)
> Next i1
> For i1 = 1 To 4
>  a = DateDiff("n", Cells(i1, 1).Value, Cells(i1, 2).Value)
>  GoSub Time_chart
> Next i1
>
> MsgBox (Fun * 10) & "分作業しました。"
> Cells(10, 10).Value = C_bar
※ 上記の行は、不要でした。

> Exit Sub
>
>Time_chart:
> Fun_bar = a / 10 '<-- 10分単位で 1とする。(20分なら2となる)
> b = DateDiff("n", "0:00", Cells(i1, 1).Value) '<-- "*"を書き込む位置の計算
> For i2 = (b / 10) To (b / 10) + (Fun_bar - 1)
>  If Mid(C_bar, i2, 1) <> "*" Then '<-- ダブリのチェック
>   Fun = Fun + 1  '<-- 10分単位の個数を数える
>   Mid(C_bar, i2, 1) = "*"
>  End If
> Next i2
> Cells(i1, 3).Value = C_bar
※ Cells(10, 10).Value = Mid(C_bar, 54, 55)  '<---MID命令で、9時〜18時
> Return
> 
>End Sub
>
>以上参考までに、
>
>▼sこー さん:
>>皆様こんばんは。いつもいつもありがとうございます。
>>
>>この度、会社で外にでている時間帯を計算する事になったのですが、
>>2人以上いると重なってしまう時間帯がでてきまして、
>>その処理をどうすればよいのかわからず悩んでいます。
>>
>>
>>    A   B   
>>1   9:30 10:30   ・・・・・・・(60分)
>>2  11:40 12:30   ・・・・・・・(50分)
>>3  12:00 12:40   ・・・・・・・(40分)
>>4  13:00 13:50   ・・・・・・・(50分)
>>
>>
>>Bの時間からAの時間を引き、最後に合計する為に
>>
>>a = DateDiff("n", Range("a1"), Range("b1")) 
>>
>>で引き算し、forで1〜4行を計算し、合計を出したいのですが、
>>そのまま計算してしまうと全ての合計時間の200分と表示されます。
>>実際には2行目と3行目は時間帯が重なっており、11:40〜12:40なので60分、
>>実際の合計時間は170分になりますよね。
>>重なりを除去して合計できるような事は可能でしょうか?
>>
>>さらにここに
>>
>>5   9:00 14:00  ・・・・・・・(300分)
>>
>>と追加されれば、1〜4行をすべてカウントせず5行目だけの合計を出さなければならず、不勉強の私には考えつかない為、セルに色々計算式をいれて間違いだらけになりながらも、どうにか現状をしのいでおります。
>>
>>皆様、どうか良い方法があれば、ご教授ください。
>>どうぞ、よろしくお願い致します.

【13404】Re:時間帯の重なり除去
回答  Hirofumi E-MAIL  - 04/5/4(火) 16:21 -

引用なし
パスワード
   こんなのでも善いかな?
データは必ずA列がB列より小さい
データはA列をKeyとして並べ替えられる
結果の出力位置は、D、E、F列とする

Option Explicit

Public Sub TimeCalc()

  Const lngColCount As Long = 3
  
  Dim i As Long
  Dim j As Long
  Dim rngList As Range
  Dim vntData As Variant
  Dim vntResult As Variant
  Dim lngArrayEnd As Long
  
  'データListの範囲を取得
  Set rngList = ActiveSheet.Cells(1, "A").CurrentRegion
  'データ範囲をソート
  With rngList
    .Sort _
      Key1:=.Item(1, 1), Order1:=xlAscending, _
      Header:=xlNo, Orientation:=xlTopToBottom
  End With
  vntData = rngList.Value
  
  '時間の重なりを除去したデータの配列を作成
  j = 1
  ReDim vntResult(1 To lngColCount, 1 To j)
  vntResult(1, j) = vntData(j, 1)
  vntResult(2, j) = vntData(j, 2)
  For i = 2 To UBound(vntData, 1)
    If vntData(i, 1) > vntResult(2, j) Then
      j = j + 1
      ReDim Preserve vntResult(1 To lngColCount, 1 To j)
      vntResult(1, j) = vntData(i, 1)
      vntResult(2, j) = vntData(i, 2)
    Else
      If vntResult(1, j) <= vntData(i, 1) Then
        If vntResult(2, j) < vntData(i, 2) Then
          vntResult(2, j) = vntData(i, 2)
        End If
      End If
    End If
  Next i
  
  '分を集計
  j = j + 1
  ReDim Preserve vntResult(1 To lngColCount, 1 To j)
  For i = 1 To j - 1
    vntResult(3, i) = vntResult(2, i) - vntResult(1, i)
    vntResult(3, j) = vntResult(3, j) + vntResult(3, i)
  Next i
  
  '結果出力
  With rngList(1, rngList.Columns.Count + 2)
    '時分に書式設定
    With .Resize(j, lngColCount - 1)
      .NumberFormat = "h:mm"
    End With
    '分に書式設定
    With .Offset(, 2).Resize(j)
      .NumberFormat = "[mm]"
    End With
    '結果を出力
    With .Resize(j, lngColCount)
      .Value = Application.Transpose(vntResult)
    End With
  End With
  
End Sub

【13577】Re:時間帯の重なり除去
お礼  sこー  - 04/5/7(金) 23:42 -

引用なし
パスワード
   皆様お礼が遅くなり、大変申し訳ありませんでした。


bykinさん、色々考えながらやってみたのですが、未だうまくいっておりません。
もう少し考えながら、理解してみます。

ちんさん、解りやすいご説明、ありがとうございます。
ああやって、タイムチャートにしてみれば、重なっている時間が一目瞭然ですね。
*をつけてする方法など、目からうろこです。ああいう使い方もできるのですね。
VBAは奥が深い!良い勉強になりました。

Hirofumiさん、ありがとうございます。25時などでも計算できるのですね。
私のしたかったのは、まさにこれです。本当に感動しております。
ですが、私に知識が無いばかりに、コードが30%くらいしか理解できておりません。
がんばって勉強して解読させて頂きます。

皆さんに例題を出して頂き(?)、それを解いていくのが本当に楽しいです。
GW中の貴重なお時間を割いていただき、誠にありがとうございました。

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