Originally posted on 27/03/2022 @ 16:23
Bạn đã bao giờ có dữ liệu ở định dạng lịch và chỉ ước bạn có thể đưa nó ở định dạng bảng? Một cái gì đó như thế này:
Bạn có thể sử dụng công thức Excel hoặc Power Query để thực hiện việc này. Trong bài viết này, chúng ta hãy xem xét cách tiếp cận dựa trên công thức với hai lựa chọn tuyệt vời.
Công thức Lịch để Bảng – Video
Nếu bạn muốn hiểu các công thức, hãy xem video bên dưới hoặc đọc tiếp.
Sử dụng công thức SUMIFS
Chúng ta có thể sử dụng công thức SUMIFS để dễ dàng lấy dữ liệu cho bất kỳ ngày nào, vì nó cũng hoạt động với phạm vi 2D .
Giả sử dữ liệu lịch của chúng tôi nằm trong phạm vi B5: H16,
Sử dụng công thức = SUMIFS ( B6: H16 , B5: H15 , “1-8-2020”) để nhận giá trị tương ứng với 1-8-2020.
Lưu ý sự khác biệt giữa phạm vi Tổng và phạm vi tiêu chí .
Chúng tôi di chuyển phạm vi tổng xuống một hàng để chúng tôi có thể xem các ngày ở trên và nhận giá trị tương ứng từ bên dưới.
Hạn chế của phương pháp SUMIFS:
- Chỉ hoạt động cho các con số . Nếu bạn có các giá trị văn bản so với mỗi ngày, thì phương pháp này sẽ không hoạt động.
- Có thể sai . Giả sử một trong các giá trị là 44044. Đó là đại diện số của ngày 1 tháng 8 năm 2020 (vì ngày tháng trong Excel chỉ là số. Thông tin thêm về cách làm việc với ngày và giờ tại đây ) Khi đó kết quả SUMIFS của chúng tôi sẽ sai.
Tìm hiểu thêm về công thức SUMIFS .
Sử dụng công thức INDEX
Chúng ta có thể sử dụng công thức INDEX để truy cập bất kỳ mục nào trong một phạm vi bằng cách chỉ định số hàng & cột.
Ví dụ, = INDEX (A1: D10, 3,2) sẽ trả về giá trị ở hàng thứ 3 & cột thứ 2 của A1: D10, tức là giá trị B3.
Vì vậy, nếu chúng ta có thể tính toán tọa độ hàng & cột cho một ngày nhất định từ lịch của mình, chúng ta có thể dễ dàng nhận được câu trả lời.
Số cột: Vì dữ liệu của chúng tôi ở định dạng lịch nên mỗi ngày sẽ chỉ nằm trong cột tương ứng với số ngày trong tuần. Chúng ta có thể sử dụng = WEEKDAY (input_date, 2) để lấy số cột, vì lịch của chúng ta là từ Thứ Hai đến Chủ Nhật.
Số hàng: Khi chúng ta biết số cột, chúng ta có thể trích xuất toàn bộ cột và tìm kiếm ngày đã cho trong cột đó bằng hàm MATCH. Điều này cũng sẽ cung cấp cho chúng tôi số hàng. Công thức dưới đây hoạt động tốt.
= MATCH (input_date, INDEX (calendar_data,, WEEKDAY (input_date, 2)) , 0)
Lưu ý: công thức INDEX () nội bộ trả về toàn bộ cột vì chúng tôi đã bỏ qua tham số hàng. Đọc thêm về chức năng này của công thức INDEX .
Công thức cuối cùng:
Đây là công thức cuối cùng của chúng tôi (với lịch trong phạm vi B5: H16)
= INDEX ($ B $ 5: $ H $ 16, MATCH ( input_date , INDEX ($ B $ 5: $ H $ 16,, WEEKDAY ( input_date , 2)), 0) +1, WEEKDAY ( input_date , 2))
Tải xuống Sổ làm việc Ví dụ
Vui lòng nhấp vào đây để tải xuống tệp ví dụ với dữ liệu lịch cho các công thức định dạng bảng. Kiểm tra các công thức hoặc viết của riêng bạn để hiểu kỹ thuật này.
Các cách khác để chuyển đổi dữ liệu định dạng lịch
- Bạn có thể sử dụng Power Query để thực hiện công việc này. Tôi thực sự khuyên bạn nên sử dụng PQ, nếu bạn thường xuyên xử lý dữ liệu kiểu lịch. Đây là phần sơ lược về Power Query .
- Trước đây tôi đã viết về vấn đề này (với giải pháp SUMIFS) và yêu cầu độc giả của tôi chia sẻ công thức của họ. Bạn có thể xem thêm nhiều giải pháp thú vị & sáng tạo khác tại đây
- VBA : Bạn có thể sử dụng một macro đơn giản để nhanh chóng định hình lại dữ liệu. Đây chỉ là tùy chọn ưu tiên nếu bạn không thể sử dụng Power Query. Đây là một hướng dẫn với macro như vậy . Các nhận xét trên liên kết này cũng có nhiều biến đổi dựa trên Power Query.
Bạn sẽ xử lý như thế nào với dữ liệu kiểu lịch?
Đối với một cái gì đó nhanh chóng và dễ dàng, tôi sẽ sử dụng công thức. Đối với một tình huống đang diễn ra, tôi sẽ sử dụng Power Query.
Thế còn bạn? Bạn sẽ xử lý như thế nào với tập dữ liệu kiểu lịch? Hãy chia sẻ suy nghĩ của bạn trong phần bình luận.
