Tôi có hai loại giải pháp để đếm sự kiện trong các khung giờ cụ thể trên Google Sheets.
Chúng ta có thể sử dụng các công thức hoặc nhóm dữ liệu theo giờ trong Pivot table để đếm số lượng sự kiện. Tôi sẽ bắt đầu với các công thức.
Trước khi đi vào chi tiết, hãy hiểu về tình huống.
Hãy giả sử tôi có các dấu thời gian (DateTime) của cuộc gọi từ khách hàng trong một cột trên Google Sheets.
Tôi muốn đếm số cuộc gọi nhận được trong một khung thời gian cụ thể, ví dụ từ 10:00:00 đến 12:00:00.
Trong tình huống này, chúng ta có thể sử dụng công thức COUNTIFS hoặc kết hợp COUNT và FILTER.
Hãy đọc: Cách sử dụng tất cả các hàm đếm trong Google Sheets.
Dù bạn đã biết cách sử dụng COUNTIFS trong Google Sheets, bạn có thể gặp vấn đề vì bạn có một cột ngày-giờ (timestamp) thay vì một cột thời gian.
Nếu bạn muốn đếm các sự kiện trong các khung thời gian hàng giờ như số sự kiện lúc 10 AM, 11 AM và 12 PM, thì tốt hơn hết là dựa vào Pivot table.
Trong bài hướng dẫn Google Sheets này, tôi đã bao gồm phương pháp đó.
Đếm sự kiện trong một khung giờ cụ thể trên Google Sheets
Trong ví dụ dưới đây, dải ô A2:A chứa các dấu thời gian. Đó là thời gian ghi lại các cuộc gọi từ khách hàng.
Để đếm số sự kiện (ở đây là số cuộc gọi nhận được) trong khung thời gian từ 10:00:00 đến 12:00:00, chúng ta có thể sử dụng công thức COUNTIFS sau đây.
=ArrayFormula(countifs(mod(A2:A,1),">="&time(10,0,0),mod(A2:A,1),"<"&time(12,0,0)))
Ở đây, các tiêu chí COUNTIFS là “>=”&time(10,0,0) và “<“&time(12,0,0). Điều đó có nghĩa là các tiêu chí được ghi cứng vào công thức.
Để tham chiếu đến các tiêu chí được cung cấp trong hai ô, hãy làm theo hướng dẫn dưới đây.
Nhập thời gian 10:00:00 vào ô C1 và 12:00:00 vào ô D1. Sau đó, sử dụng công thức COUNTIFS sau đây để đếm sự kiện trong khung thời gian cụ thể đã cho.
=ArrayFormula(countifs(mod(A2:A,1),">="&C1,mod(A2:A,1),"<"&D1))Dưới đây là giải thích công thức COUNTIFS được sử dụng để đếm sự kiện trong khung thời gian cụ thể trên Google Sheets.
Giải thích công thức
Phần cốt lõi trong công thức COUNTIFS trên là phần MOD.
Công thức MOD chuyển đổi dấu thời gian thành thời gian trong công thức COUNTIFS, hoặc nói cách khác, chúng ta có thể nói rằng nó trích xuất thời gian từ dấu thời gian (còn được gọi là DateTime).
MOD không phải là lựa chọn duy nhất cho mục đích trên. Tìm hiểu thêm một số phương pháp thay thế ở đây – Cách trích xuất phần thập phân của một số trong Google Sheets.
Lưu ý: Google Sheets ghi lại thành phần thời gian trong dấu thời gian dưới dạng một số thập phân.
Tôi sẽ cung cấp cho bạn một giải pháp khác cho vấn đề đếm sự kiện trong một khung thời gian cụ thể. Xem bên dưới.
Lọc và đếm một cột dấu thời gian
Trong phương pháp này, chúng ta sẽ lọc các dấu thời gian nằm trong khung thời gian cho trước. Sau đó, chúng ta sẽ đếm chúng (nhưng tất cả trong một công thức).
Xem công thức dưới đây.
=count(filter(A2:A,mod(A2:A,1)>=time(10,0,0),mod(A2:A,1)<time(12,0,0)))Phần FILTER lọc dải A2:A (giá trị trong cột dấu thời gian) phù hợp với khung thời gian (tiêu chí). Ở đây, phần MOD đóng vai trò quan trọng.
Cùng công thức COUNT + FILTER trên, với tiêu chí trong hai ô.
=count(filter(A2:A,mod(A2:A,1)>=C1,mod(A2:A,1)<D1))Để đếm sự kiện trong các khung thời gian hàng giờ khác nhau trên Google Sheets, chúng ta có thể sử dụng Pivot Table. Hãy xem cách làm nhé.
Đếm sự kiện trong các khung thời gian bằng cách sử dụng Pivot Table trên Google Sheets
Ở đây, tôi cũng sử dụng dữ liệu mẫu trong A2:A12.
Sau đây là các bước để tạo Pivot Table để đếm sự kiện trong các khung thời gian trên Google Sheets.
- Chọn A1:A12.
- Nhấp vào DATA và chọn PIVOT TABLE (bạn có thể tìm thấy tất cả các lệnh liên quan đến dữ liệu trong menu DATA).
- Chọn “Existing sheet”, sau đó chọn ô D1 và nhấp vào “Create”.
- Trên bảng biên tập Pivot table, nhấp vào nút “Add” trong phần “Rows” và chọn “Time”.
- Nhấp vào nút “Add” trong phần “Values” và chọn “Time”. Sau đó, trong trường xuất hiện dưới “Summarize by”, chọn “Count”.
Để bạn hiểu rõ hơn về các bước 4 và 5, tôi đã bao gồm hình ảnh liên quan dưới đây. - Nhấp chuột phải vào bất kỳ ô nào trong phạm vi bảng tổng hợp (ví dụ, ô D2).
- Nhấp chuột phải vào mục “Create pivot date group” và sau đó chọn “Hour”.
Bạn sẽ có tổng hợp theo giờ dưới đây.
Đó là tất cả về cách đếm sự kiện trong các khung thời gian cụ thể trên Google Sheets.
Cảm ơn bạn đã ở lại, thưởng thức nhé!
Liên quan: Tạo chuỗi khung thời gian tùy chỉnh trong Google Sheets.
Click vào đây để tìm hiểu thêm về Crawlan.
