
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で質問しましょう!
似たような質問が見つかりました
- FTTH・光回線 ソフトバンク光2年縛り IP電話番号 同番移行出来ない? 光回線変更は面倒で難しいですか? 4 2023/01/07 17:23
- FTTH・光回線 ソフトバンク光2年縛り IP電話番号 同番移行出来ない? 光回線変更は面倒で難しいですか? 1 2023/01/08 21:54
- その他(資産運用・投資) 年利回りの計算方法について 5 2023/01/03 19:38
- ライフスタイル・ヘルスケア クレジットカードの使用履歴管理 1 2022/06/28 11:56
- FTTH・光回線 ソフトバンク光2年縛りを使用しています 家族3人ワイモバイル おてがる光などに変えて安くなる? 4 2023/01/04 14:56
- 借金・自己破産・債務整理 自己破産中のMNPについて 1 2023/07/31 01:24
- FTTH・光回線 新居のインターネット契約についてご相談 4 2023/02/26 17:31
- 固定電話・IP電話・FAX 固定電話のお勧めを教えて下さい。 2 2022/06/14 13:26
- テレビ 光回線のテレビ契約を解約して一般のテレビ回線をつなぐにはどうしたらいいか教えてください。 アナログ放 5 2022/11/02 02:12
- 格安スマホ・SIMフリースマホ MNPを利用してドコモからドコモ回線を使う格安携帯会社に乗り換えた場合、電話帳のデータと通話履歴は消 4 2022/10/22 12:51
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
彼女との通話が苦痛です。 遠距...
-
男友達が、実家に帰ってる時は...
-
昨日夜、彼からLINEで不在着信...
-
新婚ですが、嫁が他の男と連絡...
-
彼氏がほかの女の子と寝落ち通話
-
最近彼が呼び方を間違えます…
-
友達としか思ってない異性と寝...
-
「距離を置きたい」と言った彼...
-
男友達と通話してて切る時に相...
-
彼女が口だけなんですけど、ど...
-
愛方とは何ですか? 私はネット...
-
斎藤さんという通話アプリでい...
-
通話が苦手なんですけど(自分の...
-
パソコンについて PCのdiscord...
-
これ嘘かどうか教えてください...
-
通話中に歌う人の心理ってなん...
-
から知り合った女の子がいまし...
-
スマホの通話切断について
-
Twitterでできた女友達b子と毎...
-
みなさん異性との寝落ち通話っ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
彼女との通話が苦痛です。 遠距...
-
昨日夜、彼からLINEで不在着信...
-
新婚ですが、嫁が他の男と連絡...
-
discordで通話しててちゃんと切...
-
「距離を置きたい」と言った彼...
-
スマホの通話切断について
-
通話中に歌う人の心理ってなん...
-
会ってる時は楽しいのに通話の...
-
友達としか思ってない異性と寝...
-
パソコンについて PCのdiscord...
-
最近彼が呼び方を間違えます…
-
駅のホームでは、通話禁止です...
-
男友達が、実家に帰ってる時は...
-
これ嘘かどうか教えてください...
-
Bluetoothイヤホンをケーズデン...
-
夜の通話について 自分が夜(22...
-
彼氏がほかの女の子と寝落ち通話
-
ネット恋愛
-
自分の通話嫌いに嫌気がします...
-
電話をかけるとき通話待機中と...
おすすめ情報