Hôm nọ mình có tình cờ đọc được bài viết trong ảnh này. Buộc phải ở đây là trong khoảng dữ liệu dạng datetime (có cả tháng ngày năm và giờ khắc giây), tìm ra khung giờ nào có phổ quát lượt tróc nã cập nhất. Đây là 1 bài toán hơi thuần tuý nhưng cũng khôn xiết thực tại sở hữu nhiều ứng dụng, chẳng hạn như để phân tích thói quen và hành vi khách hàng, hay xác định những 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 Anh chị cách thức chia khoảng thời gian (cụ thể là theo giờ) để khởi đầ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 | một |
| 3/3/2023 14:15 | một |
| 2/27/2023 12:25 | một |
| 2/27/2023 10:57 | một |
| 2/24/2023 9:58 | 1 |
| 2/21/2023 8:47 | một |
| 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 thức để kiểm tra trong Excel ấy là sử dụng hàm ISNUMBER (trong Google Sheets, ta mang thêm hàm ISDATE)
Sau lúc đã vững chắc dữ liệu đúng chuẩn, ta tiến hành đưa vào Pivot Table bằng cách chọn đa số 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
lúc 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 mẫu 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.
Để sở hữu thể tầm nã cập được tính năng này, bước trước nhất 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, mang 2 cách. Hoặc ta chọn tất cả 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ỉ lúc chọn một ô mới sở hữu thể dùng Group Field).
khi hộp thoại Grouping hiện ra, mang các thành phần sau:
- Starting at: thời khắc bắt đầu chia nhóm. Chẳng hạn, nếu ta tính từ lúc ngày 24 tháng 2, đa số những ngày trước đó sẽ được gom vào chung một đội ngũ mang tên "<2/21/2023"
- Ending at: thời điểm chấm dứt chia hàng ngũ. Tương tự Starting at, các ngày sau ngày ending sẽ được đưa vào 1 hàng ngũ chung ">ending date"
- By: Xác định bí quyết hiển thị dữ liệu được đội ngũ. Chả hạn, nếu như hàng ngũ theo tháng, ta sẽ mang 3 dòng độc nhất vô nhị là Tháng 1, Tháng 2 và Tháng 3. Bạn cũng có thể chọn đa dạng phương pháp hiển thị trong này, ví dụ nếu như chọn Days và Months thì ta sẽ mang hai 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 mang 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 phổ thông mục trong By
Kết quả khi chọn Number of days = 5
đến đây, ta đã đạt được mục đích phân chia theo khoảng ngày. Tuy nhiên, nếu như ta muốn phân chia theo khoảng khác, chả hạn như 3 giờ, 5 giờ, 8 giờ, 15 phút, 30 phút… thì sao? Trong khi 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 kì.
Tự tạo nhãn chia khoảng thời kì giờ, phút, giây trong Excel
có bài toán cụ thể theo dõi lưu lượng truy cập, ta với thẻ bỏ qua dữ liệu ngày tháng mà chỉ cần tập kết tới thời kì. Để tư vấn cho thắc mắc "khung giờ nào mang lượng truy hỏi cập lớn nhất trong suốt thời gian qua", ta sẽ chia giờ trong ngày ra phổ quát phần. Ở tỉ dụ này, ta chia ra Con số dữ liệu của mỗi 3 tiếng.
trước nhất, 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 | một | 14 |
| 2/27/2023 12:25 | 1 | 12 |
| 2/27/2023 10:57 | một | 10 |
| 2/24/2023 9:58 | một | 9 |
| 2/21/2023 8:47 | 1 | 8 |
| 2/20/2023 16:07 | một | 16 |
| 1/16/2023 7:49 | một | 7 |
Bởi ta cần một 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 mang thể 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 | một | 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 | một | 9 | 3 |
| 2/21/2023 8:47 | một | 8 | 2 |
| 2/20/2023 16:07 | 1 | 16 | 5 |
| 1/16/2023 7:49 | 1 | 7 | 2 |
tới bước này mang thể thấy ta đã "nhóm" những mốc thời gian vào các hàng ngũ tương ứng: từ 12h đến 15h ở hàng ngũ 4, từ 9h đến 12h ở hàng ngũ 3,…
Bước chung cục, ta sẽ tạo một nhãn dán cho từng hàng ngũ. Quy định là trong khoảng 0h đến 3h thuộc hàng ngũ 1, 3h đến 6h thuộc hàng ngũ 2… trong khoảng ấy ta sở hữu 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 | một | 15 | 5 | 15-18 |
| 3/3/2023 14:16 | một | 14 | 4 | 12-15 |
| 3/3/2023 14:15 | một | 14 | 4 | 12-15 |
| 2/27/2023 12:25 | một | 12 | 4 | 12-15 |
| 2/27/2023 10:57 | một | 10 | 3 | 9-12 |
| 2/24/2023 9:58 | một | 9 | 3 | 9-12 |
| 2/21/2023 8:47 | một | 8 | hai | 6-9 |
| 2/20/2023 16:07 | một | 16 | 5 | 15-18 |
| 1/16/2023 7:49 | một | 7 | hai | 6-9 |
Bước tiếp theo ta load dữ liệu vào PivotTable, kéo cột chung cuộc 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 với thể lấy chi tiết hơn theo phút, giây (thường sẽ theo phút). Trong trường hợp ấy, ta cần sửa một tí ở các cột phụ thêm vào
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ờ chốc lát
=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ả:
Bài viết tổng hợp trong khoảng Đức Nguyễn
0 nhận xét:
Đăng nhận xét