Thủ Thuật Excel

Chia khoảng thời gian để làm bài toán phân tích dữ liệu lượt truy cập

Posted on by Jina
26
Th9

Hôm nọ mình có tình cờ đọc được bài viết trong ảnh này. Yêu cầu ở đây là từ dữ liệu dạng datetime (có cả ngày tháng năm và giờ phút giây), tìm ra khung giờ nào có nhiều lượt truy cập nhất. Đây là một bài toán khá đơn giản nhưng cũng vô cùng thực tế với nhiều ứng dụng, chẳng hạn như để phân tích thói quen và hành vi người dùng, hay xác định các kênh tiếp cận khách hàng hợp lý. Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách chia khoảng thời gian (cụ thể là theo giờ) để bắt đầu phân tích dữ liệu.

Dữ liệu

Dưới đây là bảng dữ liệu mẫu:

Time Event
3/3/2023 15:13 1
3/3/2023 14:16 1
3/3/2023 14:15 1
2/27/2023 12:25 1
2/27/2023 10:57 1
2/24/2023 9:58 1
2/21/2023 8:47 1
2/20/2023 16:07 1
1/16/2023 7:49 1

Giờ ta sẽ tiến hành bóc tách dữ liệu.

Tách dữ liệu theo ngày/tháng/năm

Đầu tiên, ta cần phải chắc chắc dữ liệu của mình ở dạng Date (hoặc DateTime). Một cách để kiểm tra trong Excel đó là sử dụng hàm ISNUMBER (trong Google Sheets, ta có thêm hàm ISDATE)

Sau khi đã chắc chắn dữ liệu đúng chuẩn, ta tiến hành đưa vào Pivot Table bằng cách chọn toàn bộ bảng và chọn Insert -> Pivot Table -> From Data/Range

Tính năng Group giúp tách và nhóm dữ liệu theo ý muốn

Khi ta tiến hành đưa dữ liệu vào Pivot tương ứng, kết quả sẽ chưa thể hiển thị ngay bởi dữ liệu của từng dòng hoàn toàn khác nhau. Trong trường hợp này, tính năng Group trong Pivot Table sẽ là giải pháp.

Để có thể truy cập được tính năng này, bước đầu tiên bạn phải chọn dữ liệu muốn group, bởi khi đó tính năng này mới cho phép bạn chọn. Trong trường hợp trên, có hai cách. Hoặc ta chọn toàn bộ cột Time trong Pivot Table, sau đó chọn tới thẻ PivotTable Analyze và chọn Group Selection. Hoặc, ta chọn 1 ô trong cột Time, sau đó chọn Group Field (chỉ khi chọn 1 ô mới có thể sử dụng Group Field).

Khi hộp thoại Grouping hiện ra, có những thành phần sau:

  • Starting at: Thời điểm bắt đầu chia nhóm. Chẳng hạn, nếu ta bắt đầu từ ngày 24 tháng 2, tất cả những ngày trước đó sẽ được gom vào chung 1 nhóm với tên “<2/21/2023”
  • Ending at: Thời điểm kết thúc chia nhóm. Tương tự Starting at, những ngày sau ngày ending sẽ được đưa vào 1 nhóm chung “>ending date”
  • By: Xác định cách hiển thị dữ liệu được nhóm. Chẳng hạn, nếu nhóm theo tháng, ta sẽ có 3 dòng duy nhất là Tháng 1, Tháng 2 và Tháng 3. Bạn cũng có thể chọn nhiều cách hiển thị trong này, ví dụ nếu chọn Days và Months thì ta sẽ có 2 trường dữ liệu mới tự động được sinh ra trong bảng
  • Number of days: Với dữ liệu days, bạn có thể tạo các khoảng, vd như 3 ngày, 5 ngày, 10 ngày.
Trường dữ liệu sinh ra khi chọn nhiều mục trong By
Kết quả khi chọn Number of days = 5

Tới đây, ta đã đạt được mục đích phân chia theo khoảng ngày. Tuy nhiên, nếu ta muốn phân chia theo khoảng khác, chẳng hạn như 3 giờ, 5 giờ, 8 giờ, 15 phút, 30 phút… thì sao? Khi mà tính năng mặc định Group không thể giúp, ta sẽ tự tạo ra các nhãn thời gian.

Tìm hiểu thêm:  Nghiệp vụ kế toán là gì? Phân loại nghiệp vụ kế toán trong doanh nghiệp

Tự tạo nhãn chia khoảng thời gian giờ, phút, giây trong Excel

Với bài toán cụ thể theo dõi lưu lượng truy cập, ta có thẻ bỏ qua dữ liệu ngày tháng mà chỉ cần tập trung tới thời gian. Để trả lời cho câu hỏi “khung giờ nào có lượng truy cập lớn nhất trong suốt thời gian qua”, ta sẽ chia giờ trong ngày ra nhiều phần. Ở ví dụ này, ta chia ra thống kê dữ liệu của mỗi 3 tiếng.

Đầu tiên, ta tạo thêm 1 cột nữa để trích xuất riêng giờ ra với công thức:

=HOUR(dữ liệu)

Hàm trên sẽ trả về giờ tương ứng.

Time Event =HOUR(A2)
3/3/2023 15:13 1 15
3/3/2023 14:16 1 14
3/3/2023 14:15 1 14
2/27/2023 12:25 1 12
2/27/2023 10:57 1 10
2/24/2023 9:58 1 9
2/21/2023 8:47 1 8
2/20/2023 16:07 1 16
1/16/2023 7:49 1 7

Bởi ta cần 1 khoảng thời gian 3 tiếng, nên dữ liệu của ta sẽ chia cho 3 và lấy phần nguyên. Ở đây ta có thể sử dụng hàm INT hoặc QUOTIENT

=INT(HOUR([TIME])/3) hoặc =INT(cột bên trái/3)
Time Event =HOUR(A2) =INT(HOUR(A2)/3)
3/3/2023 15:13 1 15 5
3/3/2023 14:16 1 14 4
3/3/2023 14:15 1 14 4
2/27/2023 12:25 1 12 4
2/27/2023 10:57 1 10 3
2/24/2023 9:58 1 9 3
2/21/2023 8:47 1 8 2
2/20/2023 16:07 1 16 5
1/16/2023 7:49 1 7 2

Tới bước này có thể thấy ta đã “nhóm” các mốc thời gian vào các nhóm tương ứng: từ 12h tới 15h ở nhóm 4, từ 9h tới 12h ở nhóm 3,…

Bước cuối cùng, ta sẽ tạo 1 nhãn dán cho từng nhóm. Quy định là từ 0h tới 3h thuộc nhóm 1, 3h tới 6h thuộc nhóm 2… từ đó ta có công thức như sau:

=3*INT(HOUR([TIME])/3)&”-”&3*INT(HOUR([TIME])/3)+3 hoặc =3*cột bên trái&”-”&3*cột bên trái + 3
Time Event =HOUR(A2) =INT(HOUR(A2)/3) =3[@Convert]&”-“&3[@Convert]+3
3/3/2023 15:13 1 15 5 15-18
3/3/2023 14:16 1 14 4 12-15
3/3/2023 14:15 1 14 4 12-15
2/27/2023 12:25 1 12 4 12-15
2/27/2023 10:57 1 10 3 9-12
2/24/2023 9:58 1 9 3 9-12
2/21/2023 8:47 1 8 2 6-9
2/20/2023 16:07 1 16 5 15-18
1/16/2023 7:49 1 7 2 6-9

Bước tiếp theo ta load dữ liệu vào PivotTable, kéo cột cuối cùng vào Rows và Event vào Values

Lấy dữ liệu theo khoảng thời gian phút, giây

Tương tự với lấy khoảng theo giờ, ta có thể lấy chi tiết hơn theo phút, giây (thường sẽ theo phút). Trong trường hợp đó, ta cần sửa một chút ở những cột phụ thêm vào

Tìm hiểu thêm:  Hàm CLOSINGBALANCEQUARTER trong Power BI DAX Khóa học mới xuất bản

Cột trích xuất thời gian: lấy phút thay vì giờ

=HOUR(A2) =MINUTE(A2)

Cột chia khoảng: Làm tương tự, chia cho số phút muốn chia khoảng

=INT(MINUTE([TIME])/15) hoặc =INT(cột bên trái/15)  --15 phut

Cột tạo nhãn: Lồng thêm hàm để hiển thị định dạng giờ phút giây

=TEXT(TIME(HOUR([@Time]),15*[@[Each_15_mins]],0),"hh:mm:ss")&"-"&TEXT(TIME(HOUR([@Time]),15*[@[Each_15_mins]]+15,0),"hh:mm:ss")

Kết quả:

Xem thêm các bài viết cùng chủ đề:
Cách cộng trừ khoảng thời gian trong Excel
Sử dụng các hàm ngày tháng năm trong Excel