Tôi có cái tổng đài
Giả sử có cái log cuộc gọi có cấu trúc:
Stt, t0, T1
Trong đó t0 là start time (24h time format), t1 là long (giây)
Bây giờ tôi muốn làm thế nào đếm xem tại mỗi phút có bao nhiêu cuộc gọi ?
Dùng các kiểu VBA, Hàm, name, macro để chạy để tổng hợp được hết
File ví dụ có đính kèm
Nếu dùng để thống kê dữ liệu tổng đài (cao điểm và thấp điểm) thì mình nghĩ bài của bạn phải phát triển thêm tới : làm thế nào để biết tại phút:giây cụ thể đó ta có bao nhiêu cuộc gọi cùng lúc.
Nếu chỉ tính theo số phút cụ thể thôi thì sợ kô đúng hoàn toàn.
Tại thời điểm cụ thể, như DD/MM/YYYY hh:mm:ss cần đếm số cuộc gọi (bấm máy) đang còn diễn ra
Có cuộc gọi khởi điểm trước đó 30 phút nhưng cháo chưa nhừ họ vẫn nấu tiếp; nhưng có cuộc gọi trước đó vài ba phút đã ngưng nghỉ.
Theo mình, thuật toàn sẽ phại tìm từ giá trị 2 cột gộp chung mới được!
Sẽ xóa bài này, khi không cần nữa hay có bài khác thay thế.!
Vậy thì sort StartTime trước, rồi thêm cột EndTime nữa... Từ cơ sở này mà tính em nghĩ là được (giống như hồi trước có bài toán : "tính xem trong ngày 15 có bao nhiêu khác đang trọ")
Vậy thì sort StartTime trước, rồi thêm cột EndTime nữa... Từ cơ sở này mà tính em nghĩ là được (giống như hồi trước có bài toán : "tính xem trong ngày 15 có bao nhiêu khác đang trọ")
Nếu dùng để thống kê dữ liệu tổng đài (cao điểm và thấp điểm) thì mình nghĩ bài của bạn phải phát triển thêm tới : làm thế nào để biết tại phút:giây cụ thể đó ta có bao nhiêu cuộc gọi cùng lúc.
Nếu chỉ tính theo số phút cụ thể thôi thì sợ kô đúng hoàn toàn.
Chính xác là tại sheet "Count", tôi muốn đếm tại mỗi thời điểm HH:mm có bao nhiêu cuộc đang gọi, nghĩa là đã Start và chưa End
Đang thử dùng Count ( (StartTime < CheckTime) AND (CheckTime-StartTime) < Long) )
Nên quy đổi Long thành hh:mm:ss hay nên quy đổi StartTime thành kiểu Number nhỉ ?
Có lẽ chuyển cái thứ 2 thành giây thứ bao nhiêu trong ngày thì dễ hơn ?
1h = 3600s
1m = 60s
HH:mm = HH x 3600 + 60m
Nếu thế thì dể mà! Có thể dùng SUMPRODUCT... Nhưng qua thí nghiệm tôi thấy hàm này chạy quá chậm! Tôi chuyển sang COUNIF (nhanh hơn nhiều)
(Bạn chú ý thêm: Nếu so sánh GIỜ thì đương nhiên bạn phải cho NGAY THANG vào, nếu không ai biết được 1:00 là của ngày tháng năm nào)
Xem file
Bạn xóa cột 'A' bên Sheets("Count") đi
Sau đó chép macro sau đây vô CS VBE của sheetName này
Nhập thử 1 giá trị ngày giờ như bên 'Data' xem sao
PHP:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dDate As Date, dTime As Date
Dim dblTime As Double, DateTime As Double, DateTime0 As Double
Dim lDate As Double
Dim lRow As Long, Zw As Long, lDem As Integer
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
If Not IsDate(Target) Then
MsgBox "Non valid date and time in Target": Exit Sub
End If
dDate = MaThGian(Target): DateTime0 = dDate
dTime = MaThGian(Target, False): dblTime = dTime
DateTime0 = DateTime0 + dblTime
With Sheets("Data")
lRow = .[b65432].End(xlUp).Row
Range("B1:B" & lRow).Interior.ColorIndex = 0
For Zw = 2 To lRow
dDate = MaThGian(.Cells(Zw, 2)): DateTime = dDate
dTime = MaThGian(.Cells(Zw, 2), False): dblTime = dTime
DateTime = DateTime + dblTime
If DateTime <= DateTime0 And DateTime + .Cells(Zw, 2).Offset(, 1) _
/ 3600 >= DateTime0 Then
.Cells(Zw, 2).Interior.ColorIndex = 35
lDem = 1 + lDem
End If
Next Zw
End With
Target.Offset(, 1) = lDem
End If
Exit Sub
End Sub
Tiếp buớc bác SA, em góp thêm 1 UDF.
Tuy nhiên áp dụng cho 1.440 cell thì hơi nặng (khoảng 100 cell thì vô tư)
Đang nghĩ đến Match hoặc Find để rút ngắn vòng lặp nhưng chưa nghĩ ra cách trọn vẹn.
PHP:
Function SoCuocGoi(ThoiGian As Date, Mang As Range) As Integer
Application.Volatile (False)
Dim i As Long, Dau As Date
ThoiGian = TimeSerial(Hour(ThoiGian), Minute(ThoiGian), 0)
For i = 1 To Mang.Rows.Count
Dau = TimeSerial(Hour(Mang(i, 1)), Minute(Mang(i, 1)), 0)
If Dau > ThoiGian Then Exit Function
If ThoiGian <= Dau + TimeSerial(0, Int(Mang(i, 2) / 60), 0) Then
SoCuocGoi = SoCuocGoi + 1
End If
Next
End Function
Nếu thế thì dể mà! Có thể dùng SUMPRODUCT... Nhưng qua thí nghiệm tôi thấy hàm này chạy quá chậm! Tôi chuyển sang COUNIF (nhanh hơn nhiều)
(Bạn chú ý thêm: Nếu so sánh GIỜ thì đương nhiên bạn phải cho NGAY THANG vào, nếu không ai biết được 1:00 là của ngày tháng năm nào)
Xem file
ThoiGian = TimeSerial(Hour(ThoiGian), Minute(ThoiGian), 0)
For i = 1 To Mang.Rows.Count
Dau = TimeSerial(Hour(Mang(i, 1)), Minute(Mang(i, 1)), 0)
If Dau > ThoiGian Then Exit Function
If ThoiGian <= Dau + TimeSerial(0, Int(Mang(i, 2) / 60), 0) Then
SoCuocGoi = SoCuocGoi + 1
End If
Next
End Function
BAB ta không tính ngày, nên giao thời của 2 ngày kế tiếp hàm của BAB chưa chắc chính xác(?!);
Mcr của mình trong biến DateTime & DateTime0 có tính đến ngày; đó là phần nguyên trong biến; còn giờ, phút... thì ẩn trong phần thập phân.
Bài trên của mình còn thiếu dẫn ra hàm MaThGian, như sau:
PHP:
Function MaThGian(Rng As Range, Optional Ngay As Boolean = True)
If Ngay Then
MaThGian = DateSerial(Year(Rng), Month(Rng), Day(Rng))
Else
MaThGian = TimeSerial(Hour(Rng), Minute(Rng), Second(Rng))
End If
End Function
BAB ta không tính ngày, nên giao thời của 2 ngày kế tiếp hàm của BAB chưa chắc chính xác(?!);
Mcr của mình trong biến DateTime & DateTime0 có tính đến ngày; đó là phần nguyên trong biến; còn giờ, phút... thì ẩn trong phần thập phân.
Bài trên của mình còn thiếu dẫn ra hàm MaThGian, như sau:
PHP:
Function MaThGian(Rng As Range, Optional Ngay As Boolean = True)
If Ngay Then
MaThGian = DateSerial(Year(Rng), Month(Rng), Day(Rng))
Else
MaThGian = TimeSerial(Hour(Rng), Minute(Rng), Second(Rng))
End If
End Function
Vâng, em cũng rất muốn tình theo ngày để có thể sử dụng được Match hoặc Find, nhưng đề bài lại không có mà chỉ có giờ và phút
Hàm của em chỉ đúng cho 1 ngày, nó theo quy luật : Data là 1 ngày, được sắp xếp từ nhỏ đến lớn. Nếu đúng quy luật đó thì OK, không sao cả. Còn nếu khác quy luật đó thì . . bó tay ạ.
Vì thế nhất thiết trong báo cáo (thời gian mình muốn xét) có cả ngày thì mới làm được ạ.
Tổng hợp các ý kiến của các bác, tôi thấy có mấy vđ: 1. Ngày (tất nhiên là kèm theo tháng và năm)
- Hiện tại, mặc định ngày đếm chính là ngày trong log (đây là tôi mới trích 1 khúc của log), tuy nhiên, (nếu lấy log của 1 số ngày mà đếm số cuộc gọi đến vào các thời điểm từng phút của mọi ngày khác nhau, cũng là vấn đề - nhưng sẽ xử lý lúc khác, nếu có của từng ngày thì việc pivot là chuyện nhỏ - 00:01:00-23:59:00 của tất cả các ngày)
- Hiện tại tôi tạm chưa quan tâm đến các cuộc gọi của ngày trước chưa kết thúc, nếu xử lý được từ 0h thì tôi hoàn toàn có thể lấy log từ các cuộc trong vòng T(giây) = Max(long) giây trước 0:0:0 của ngày hôm trước để đưa vào, và xử lý riêng phần log này cho những cuộc gọi từ hôm trước chuyển sang. 2. Hiệu năng:
- Với 1440 dòng trong bảng count và khoảng 10K dòng trong log thì không phải hàm nào (kể cả UDF) cũng có thể hoạt động - đặc biệt là các hàm có sử dụng FOR, vì khi đó số vòng lặp là cực lớn !!! Trong trường hợp này, tôi thường dùng macro tính toán các cột phụ để giảm tải cho hàm (ví dụ hàm MaThGian hay TimeSerial để tính các cột phụ trước. - Hiện tôi đã chuyển hướng, mang sang MS Access thì nó chạy khá nhanh theo code sau:
PHP:
Sub CountByMin(Optional iThang As Long = 7, Optional iNgay As Long = 28)
' Tinh so cuoc goi den tong dai trong moi phut
' chua tinh den cac cuoc goi den tu ngay hom truoc
Dim iTime As Long, sTime As Date, iCount As Long, iid As Long
CurrentDb.Execute "delete from 1day" ' Xóa bảng phụ
' Lọc Log để lấy các cuộc gọi trong 1 ngày chỉ ra (chưa xử lý năm) qua biến iNgay, iThang
sSQL = "INSERT INTO 1day ( H, M, Start, [long] ) SELECT Hour([thoi_gian]) AS H, Minute([thoi_gian]) AS M, [H]*3600+[M]*60 AS Start, data.long FROM data WHERE (Month([thoi_gian])=" & iThang & ") AND (Day([thoi_gian])=" & iNgay & ") ORDER BY Hour([thoi_gian]), Minute([thoi_gian]), data.long;"
CurrentDb.Execute sSQL
' Lấy stickid và id của dòng trong bảng chứa 1440 dòng, stickid chính là cột Time trong sheet "Count")
sSQL = "SELECT stickid, id from time_stick order by stickid"
' Nạp cái đó vào bộ nhớ, tại biến RS
Set RS = CurrentDb.OpenRecordset(sSQL)
While Not RS.EOF
' Duyệt từ đầu
sTime = RS.Fields(0)
iid = RS.Fields(1)
iTime = Hour(sTime) * 3600 + Minute(sTime) * 60 ' Chuyển sang Serial
sSQL = "SELECT Count(*) FROM 1Day WHERE (Start<=" & iTime & ") AND ((long+start)>=" & iTime & ")"
' Luật để đếm: Cuộc gọi đã Start và chưa kết thúc
Set RS1 = CurrentDb.OpenRecordset(sSQL)
iCount = CLng(RS1.Fields(0)) ' Đếm và cho vào biến iCount
Set RS1 = Nothing
sSQL = "Update time_stick set count=" & iCount & " WHERE id=" & iid
CurrentDb.Execute sSQL ' Ghi iCount vào dòng có id đang xét
RS.MoveNext
Wend
RS.Close
MsgBox "Da tinh xong"
End Sub
Tổng hợp các ý kiến của các bác, tôi thấy có mấy vđ: 1. Ngày (tất nhiên là kèm theo tháng và năm)
- Hiện tại, mặc định ngày đếm chính là ngày trong log (đây là tôi mới trích 1 khúc của log), tuy nhiên, (nếu lấy log của 1 số ngày mà đếm số cuộc gọi đến vào các thời điểm từng phút của mọi ngày khác nhau, cũng là vấn đề - nhưng sẽ xử lý lúc khác, nếu có của từng ngày thì việc pivot là chuyện nhỏ - 00:01:00-23:59:00 của tất cả các ngày)
- Hiện tại tôi tạm chưa quan tâm đến các cuộc gọi của ngày trước chưa kết thúc, nếu xử lý được từ 0h thì tôi hoàn toàn có thể lấy log từ các cuộc trong vòng T(giây) = Max(long) giây trước 0:0:0 của ngày hôm trước để đưa vào, và xử lý riêng phần log này cho những cuộc gọi từ hôm trước chuyển sang. 2. Hiệu năng:
- Với 1440 dòng trong bảng count và khoảng 10K dòng trong log thì không phải hàm nào (kể cả UDF) cũng có thể hoạt động - đặc biệt là các hàm có sử dụng FOR, vì khi đó số vòng lặp là cực lớn !!! Trong trường hợp này, tôi thường dùng macro tính toán các cột phụ để giảm tải cho hàm (ví dụ hàm MaThGian hay TimeSerial để tính các cột phụ trước. - Hiện tôi đã chuyển hướng, mang sang MS Access thì nó chạy khá nhanh theo code sau:
PHP:
Sub CountByMin(Optional iThang As Long = 7, Optional iNgay As Long = 28)
' Tinh so cuoc goi den tong dai trong moi phut
' chua tinh den cac cuoc goi den tu ngay hom truoc
Dim iTime As Long, sTime As Date, iCount As Long, iid As Long
CurrentDb.Execute "delete from 1day" ' Xóa bảng phụ
' Lọc Log để lấy các cuộc gọi trong 1 ngày chỉ ra (chưa xử lý năm) qua biến iNgay, iThang
sSQL = "INSERT INTO 1day ( H, M, Start, [long] ) SELECT Hour([thoi_gian]) AS H, Minute([thoi_gian]) AS M, [H]*3600+[M]*60 AS Start, data.long FROM data WHERE (Month([thoi_gian])=" & iThang & ") AND (Day([thoi_gian])=" & iNgay & ") ORDER BY Hour([thoi_gian]), Minute([thoi_gian]), data.long;"
CurrentDb.Execute sSQL
' Lấy stickid và id của dòng trong bảng chứa 1440 dòng, stickid chính là cột Time trong sheet "Count")
sSQL = "SELECT stickid, id from time_stick order by stickid"
' Nạp cái đó vào bộ nhớ, tại biến RS
Set RS = CurrentDb.OpenRecordset(sSQL)
While Not RS.EOF
' Duyệt từ đầu
sTime = RS.Fields(0)
iid = RS.Fields(1)
iTime = Hour(sTime) * 3600 + Minute(sTime) * 60 ' Chuyển sang Serial
sSQL = "SELECT Count(*) FROM 1Day WHERE (Start<=" & iTime & ") AND ((long+start)>=" & iTime & ")"
' Luật để đếm: Cuộc gọi đã Start và chưa kết thúc
Set RS1 = CurrentDb.OpenRecordset(sSQL)
iCount = CLng(RS1.Fields(0)) ' Đếm và cho vào biến iCount
Set RS1 = Nothing
sSQL = "Update time_stick set count=" & iCount & " WHERE id=" & iid
CurrentDb.Execute sSQL ' Ghi iCount vào dòng có id đang xét
RS.MoveNext
Wend
RS.Close
MsgBox "Da tinh xong"
End Sub
