![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
Excelで、CSV電話ログから実際に使用した回線数の履歴を割り出したい。
12回線の契約でビジネスホンを利用しています。
実際に時間帯別で何回線利用しているのかをExcelで割り出したいのですが、計算方法が分かりません。
「通話開始時間(201710301200),通話時間(00-03-00),」のcsvがあります。
このcsvから実際に使用(通話)した回線数(1〜12)を割り出す計算式をご教授頂けますでしょうか。
1時間毎の最大値でも分かれば助かります。
よろしくお願い致します。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
#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
- 回答日時:
#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
No.2
- 回答日時:
こんにちは
関数でもできないことはなさそうですが、面倒そうなので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
- 回答日時:
例えば、
通話開始時間(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
とかっての入れれば、特定日時での回線数も出せるハズ。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「距離を置きたい」と言った彼...
-
昨日夜、彼からLINEで不在着信...
-
discordで通話しててちゃんと切...
-
通話中に歌う人の心理ってなん...
-
毎日の電話に疲れたってのは嫌...
-
通話中にずっと彼氏がYouTubeを...
-
彼女との通話が苦痛です。 遠距...
-
会ってる時は楽しいのに通話の...
-
新婚ですが、嫁が他の男と連絡...
-
斎藤さんという通話アプリでい...
-
友達としか思ってない異性と寝...
-
これ嘘かどうか教えてください...
-
ネット恋愛について 中2女子で...
-
コロナで親が家にいるのでネッ...
-
助けてください。 些細なことな...
-
先日声ともというアプリで話す...
-
パソコンについて PCのdiscord...
-
夜の通話について 自分が夜(22...
-
私は嫉妬が激しくて、ネットで...
-
通話中テレビ見る人どう思いますか
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
昨日夜、彼からLINEで不在着信...
-
discordで通話しててちゃんと切...
-
「距離を置きたい」と言った彼...
-
通話中に歌う人の心理ってなん...
-
通話中にずっと彼氏がYouTubeを...
-
毎日の電話に疲れたってのは嫌...
-
彼女との通話が苦痛です。 遠距...
-
会ってる時は楽しいのに通話の...
-
パソコンについて PCのdiscord...
-
彼氏がほかの女の子と寝落ち通話
-
新婚ですが、嫁が他の男と連絡...
-
これ嘘かどうか教えてください...
-
ネット恋愛について 中2女子で...
-
友達としか思ってない異性と寝...
-
電話をかけるとき通話待機中と...
-
あまり深く知らない人から通話...
-
最近失恋した異性の友達が少し...
-
彼女が口だけなんですけど、ど...
-
駅のホームでは、通話禁止です...
-
スマホの通話切断について
おすすめ情報