Excelで、CSV電話ログから実際に使用した回線数の履歴を割り出したい。 12回線の契約でビジネス

Excelで、CSV電話ログから実際に使用した回線数の履歴を割り出したい。


12回線の契約でビジネスホンを利用しています。
実際に時間帯別で何回線利用しているのかをExcelで割り出したいのですが、計算方法が分かりません。

「通話開始時間（201710301200）,通話時間（00-03-00）,」のcsvがあります。
このcsvから実際に使用（通話）した回線数（1〜12）を割り出す計算式をご教授頂けますでしょうか。

1時間毎の最大値でも分かれば助かります。

よろしくお願い致します。

No.4 回答者： fujillin 回答日時： 2017/11/08 11:31

#3です。

しつこくて、申し訳ありません。（笑）

#2を作成する際に#1を利用してしまいましたが、考え方を変えたのでD列、E列に時間換算の数値を記入する必要が無くなっていました。

…ということで、再度書き直してみました。
内容は#3と同じですが、セルへの記入や読み出しを行わないので、計算が速くなるはずです。
なお、#2、#1に書き忘れましたが、データチェック等は省略していますので、A列、B列に異なるフォーマットのデータが混ざっているとエラーになります。悪しからず。
（途中に空白行があるような場合も、上記の理由でエラーになります）

Sub Sample3()

Dim rw As Long, rwMax As Long
Dim v As String, tDate As String
Dim i As Integer, tCount(1440) As Integer
Dim tStart As Integer, tEnd As Integer
Dim m As String, msg As String, cnt As Integer

Const interval = 60 '←集計用の単位時間（単位：分

rwMax = Cells(Rows.Count, 1).End(xlUp).Row
tDate = Left(Mid(Cells(1, 1).Value, 8, 12), 8)

For rw = 1 To rwMax
　v = Mid(Cells(rw, 1).Value, 8, 12)
　If Left(v, 8) = tDate Then
　　tStart = Mid(v, 9, 2) * 60 + Right(v, 2)
　　v = Mid(Cells(rw, 2).Value, 6, 8)
　　tEnd = tStart + Left(v, 2) * 60 + Mid(v, 4, 2) - 1
　　If Right(v, 2) > 0 Then tEnd = tEnd + 1
　　If tEnd > 1440 Then tEnd = 1440
　　For i = tStart To tEnd
　　　tCount(i) = tCount(i) + 1
　　Next i
　End If
Next rw

msg = ""
For tStart = 0 To 1439 Step interval
　cnt = 0
　tEnd = tStart + interval - 1
　If tEnd > 1439 Then tEnd = 1439
　For i = tStart To tEnd
　　If tCount(i) > cnt Then cnt = tCount(i)
　Next i
　If cnt > 0 Then
　　m = Right("0" & Int(tStart / 60), 2) & ":"
　　m = m & Right("0" & (tStart Mod 60), 2) & "～"
　　m = m & Right("0" & Int(tEnd / 60), 2) & ":"
　　m = m & Right("0" & (tEnd Mod 60), 2)
　　msg = msg & m & " の同時通話件数 " & cnt & " 件" & vbCrLf
　End If
Next tStart

MsgBox msg

End Sub

No.3 回答者： fujillin 回答日時： 2017/11/07 14:47

#2です。

まったく反応が無いみたいなので、回答しても無駄なのかも知れませんが・・・


#2の回答だと、超長電話が1本あるとその間の短い電話を全部カウントしてしまうので、やはり正確性に欠けるかなと思い直しました。
今回の方法は、1分単位毎に通話数を集計して同時通話数を調べる方式なので、それなりに正確になると思います。

※ 基本的な仮定条件は#2と同じですが、データの並べ替え（ソート）は行いません。
※ とりあえず同じ日（1日分）を集計するようにしています。
　 A1セルと同じ日の通話のみ集計し、その他の日のデータは無視します
※ 表示用の集計間隔は設定可能です（現状は60分に設定してあります→intervalの値）
※ 結果の表示方法が不明なので、メッセージボックスに表示する方式は同じままです

Sub Sample2()

Dim rg As Range, v As String
Dim rw As Long, rwMax As Long
Dim cDay As Date
Dim i As Integer, tCount(1440) As Integer
Dim tStart As Integer, tEnd As Integer
Dim msg As String, cnt As Integer

Const interval = 60 '←集計用の単位時間（単位：分）

Columns("D:E").ClearContents
Columns("D:E").NumberFormatLocal = "m/d h:mm"
rwMax = Cells(Rows.Count, 1).End(xlUp).Row

For rw = 1 To rwMax
　Set rg = Cells(rw, 1).Resize(1, 5)
　v = Mid(rg(1).Value, 8, 12)
　rg(4).Value = DateSerial(Left(v, 4), Mid(v, 5, 2), Mid(v, 7, 2)) _
　　　+ TimeSerial(Mid(v, 9, 2), Right(v, 2), 0)
　v = Mid(rg(2).Value, 6, 8)
　rg(5).Value = rg(4).Value + TimeSerial(Left(v, 2), Mid(v, 4, 2), Right(v, 2))
Next rw

cDay = Int(Cells(1, 4))
For rw = 1 To rwMax
　If cDay = Int(Cells(rw, 4)) Then
　　tStart = Hour(Cells(rw, 4)) * 60 + Minute(Cells(rw, 4))
　　tEnd = Hour(Cells(rw, 5)) * 60 + Minute(Cells(rw, 5)) - 1
　　If tEnd > 1440 Then tEnd = 1440
　　For i = tStart To tEnd
　　　tCount(i) = tCount(i) + 1
　　Next i
　End If
Next rw

msg = ""
For i = 0 To 1439 Step interval
　cnt = 0
　tEnd = WorksheetFunction.Min(i + interval - 1, 1439)
　For j = i To tEnd
　　If tCount(j) > cnt Then cnt = tCount(j)
　Next j
　If cnt > 0 Then
　　msg = msg & WorksheetFunction.Text(TimeSerial(0, i, 0), "hh:mm") & "～" & _
　　　WorksheetFunction.Text(TimeSerial(0, tEnd, 0), "hh:mm") & _
　　　" の同時通話件数 " & cnt & " 件" & vbCrLf
　End If
Next i

MsgBox msg
End Sub

この回答へのお礼

ご丁寧に有難うございます!
大変助かりました!

お礼日時：2017/11/07 19:52

No.2 回答者： fujillin 回答日時： 2017/10/31 16:33

こんにちは

関数でもできないことはなさそうですが、面倒そうなのでVBAでの回答です。


＞「通話開始時間（201710301200）,通話時間（00-03-00）,」のcsvがあります
#1様が仮定なさっているように、1行につき2項目（3項目目は空白）というCVSが複数行あるものと想定しました。
そのまま読み込めば、A列に「通話開始時間（201710301200」、B列に「通話時間（00-03-00）」に相当するデータが各行に順に並ぶものと想定しています。
また、開始時間は0埋めのしてある12桁の数字で、年（4桁）、月（2桁）、日（2桁）、時間（2桁）、分（2桁）となっており、通話時間も同様に、ハイフンによる連結8桁で、時間（2桁）、分（2桁）、秒（2桁）という意味になっていると仮定しています。

上記のようにCSVを読み込んだ状態で、以下のVBAを実行すると、
　D列に　開始日時（シリアル値）
　E列に　終了日時（シリアル値）
　F列に　その通話の時間帯に重複する通話数（自身も含むので、単独の場合は1です）
が表示されます。

これから先はどのようになさりたいのかよくわかりませんので、とりあえず、全体の最大の同時通話数を表示して終了するようにしてあります。
ある範囲での同時使用の最大数を知りたければ、その範囲でのF列の値の最大値を見ればよろしいかと思います。


※ データ処理の都合上、元のデータを開始日時順にソートしていますので、読み込んだ直後とは順序が変わる可能性があります。
（元データが、開始時刻順になっている保証があれば、ソートする必要はありませんが）
※　データが実質的には分単位なのかも知れませんので、通話の終了時刻と他の通話の開始時刻が同じ場合、ぎりぎりで同時ではない可能性もありますが、安全側の判断として同時利用としてカウントしています。
※ 一つの通話の時間帯に、他の通話で時間帯が重なっているものがあれば、単純にカウントするというロジックにしていますので、重複数は若干多めに出る可能性があります。
（例えば、ある通話Aと同じ時間帯の前半で通話Bが行われ、Bの終了後に通話Cが発生したとします。厳密には重複は2ということになりますが、提示したサンプルでは全部カウントする方式なので3と表示されます。）
厳密に計算したければ、時間帯を細かく区切って、各時間帯に該当する通話数をピックアップしてカウントしてゆけば可能と思います。（もっと効率的な方法があるかも知れませんが…）

Sub Sample()
Dim rg As Range, v As String
Dim rw As Long, rwMax As Long, r As Long
Dim cnt As Long, cntMax As Long
Dim t_start As Date, t_end As Date

Columns("D:F").ClearContents
Columns("D:E").NumberFormatLocal = "m/d h:mm"
rwMax = Cells(Rows.Count, 1).End(xlUp).Row
cntMax = 0

For rw = 1 To rwMax
　Set rg = Cells(rw, 1).Resize(1, 5)
　v = Mid(rg(1).Value, 8, 12)
　rg(4).Value = DateSerial(Left(v, 4), Mid(v, 5, 2), Mid(v, 7, 2)) _
　　　+ TimeSerial(Mid(v, 9, 2), Right(v, 2), 0)
　v = Mid(rg(2).Value, 6, 8)
　rg(5).Value = rg(4).Value + TimeSerial(Left(v, 2), Mid(v, 4, 2), Right(v, 2))
Next rw

Range("A1").Resize(rwMax, 5).Sort key1:=Range("D1"), Header:=xlNo

For rw = 1 To rwMax
　t_start = Cells(rw, 4).Value
　t_end = Cells(rw, 5).Value
　cnt = 1
　For r = 1 To rw - 1
　　If Cells(r, 5).Value >= t_start Then cnt = cnt + 1
　Next r
　For r = rw + 1 To rwMax
　　If Cells(r, 4) > t_end Then Exit For
　　If Cells(r, 4).Value <= t_end Then cnt = cnt + 1
　Next r
　Cells(rw, 6).Value = cnt
　If cnt > cntMax Then cntMax = cnt
Next rw

MsgBox "最大同時通話数＝" & cntMax
End Sub

No.1 回答者： neKo_deux 回答日時： 2017/10/30 22:04

例えば、

通話開始時間（201710301200）,通話時間（00-03-00）,
通話開始時間（201710301205）,通話時間（00-04-00）,
通話開始時間（201710301206）,通話時間（00-02-00）,
ってのを、

日時　　　　　　　　　　　種別(開始:+1, 終了:-1)　SUM
2017/10/30 12:00:00　　+1　　　　　　　　　　　　1
2017/10/30 12:03:00　　-1　　　　　　　　　　　　0
2017/10/30 12:05:00　　+1　　　　　　　　　　　　1
2017/10/30 12:06:00　　+1　　　　　　　　　　　　2
2017/10/30 12:08:00　　-1　　　　　　　　　　　　1
2017/10/30 12:09:00　　-1　　　　　　　　　　　　0

って開始終了の時刻にして並べ替えしてしまえば、種別の部分を上からSUMしちゃえば、その時点の使用回線数が数えられます。
途中に、
2017/10/30 13:00:00　　+0
とかっての入れれば、特定日時での回線数も出せるハズ。