Trong video này, chúng ta sẽ xem xét cách tính toán và làm nổi bật các ngày hết hạn.
Giả sử công ty của bạn đã bắt đầu một chương trình thành viên nào đó và sếp của bạn vừa gửi cho bạn một bộ dữ liệu. Cô ấy đã cung cấp cho bạn danh sách 1000 người đã gia hạn tư cách thành viên trong năm ngoái hoặc lâu hơn và cô ấy đang tìm kiếm một số điều.
Đầu tiên, cô ấy muốn bạn tính ngày hết hạn một năm trong tương lai, vào ngày cuối cùng của tháng mà tư cách thành viên đã được gia hạn.
Thứ hai, cô ấy muốn xem còn bao nhiêu ngày trước khi hết hạn.
Thứ ba, cô ấy muốn thấy trạng thái "Đã hết hạn" đối với mọi tư cách thành viên đã hết hạn và "Sắp hết hạn" đối với mọi tư cách thành viên sẽ hết hạn trong 30 ngày tới.
Cuối cùng, cô ấy nói sẽ rất vui khi thấy những thành viên hết hạn được tô màu hồng, và những thành viên sắp hết hạn được tô màu vàng.
Ngoài ra - cô ấy có thể có nó trước cuộc họp ăn trưa của cô ấy vào buổi trưa không?
Chắc chắn rồi.
Đầu tiên, hãy chuyển đổi dữ liệu này sang một Bảng Excel thích hợp. Điều này sẽ giúp nhập công thức dễ dàng hơn nhiều, vì Excel sẽ tự động sao chép chúng khi chúng ta tiếp tục.
Bây giờ chúng ta hãy tính toán ngày hết hạn. Những thứ này được cho là vào cuối tháng đó một năm sau, nhưng chúng ta hãy sử dụng một bản hack đơn giản trước, để vào sân chơi bóng. Như bạn đã thấy trong các video trước đó, ngày tháng chỉ là số sê-ri, vì vậy chúng tôi có thể nhập công thức thêm 365 ngày vào ngày Gia hạn.
Đây là một khởi đầu tốt. Chúng tôi có thể hoàn thành giải pháp thô và quay lại sửa lỗi này sau.
Khi bạn đang giải quyết một vấn đề phức tạp hơn trong Excel, bạn nên xác thực phương pháp tổng thể, sau đó quay lại chi tiết ở phần cuối. Bạn không muốn mắc kẹt vào một việc nhỏ ngay khi bắt đầu, đặc biệt nếu cách tiếp cận có thể thay đổi.
Bây giờ chúng ta đã có ngày hết hạn, chúng ta có thể tính số ngày còn lại. Điều này cần cập nhật tự động trong tương lai, vì vậy chúng tôi sẽ sử dụng hàm TODAY, hàm này luôn trả về ngày trong ngày.
Công thức chỉ đơn giản là E5 trừ TODAY (). Khi tôi nhấn trở lại, chúng tôi còn lại số ngày trước khi hết hạn. Số âm cho biết tư cách thành viên đã hết hạn.
Đối với trạng thái, chúng tôi sẽ sử dụng công thức IF lồng nhau đơn giản. Nếu số ngày còn lại ít hơn 0, tư cách thành viên sẽ hết hạn. Ngược lại, nếu còn dưới 30 ngày, trạng thái sẽ là "Sắp hết hạn". Nếu không, tình trạng không là gì cả.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Tiếp theo, chúng ta cần tạo các quy tắc định dạng có điều kiện để làm nổi bật các giá trị này.
Đầu tiên, chọn dữ liệu và đặt ô hiện hoạt ở góc trên bên phải. Sau đó, tạo quy tắc công thức kiểm tra ô đang hoạt động cho giá trị "Đã hết hạn". Cột phải được khóa.
Bây giờ, hãy lặp lại quy trình tương tự đối với tư cách thành viên "Sắp hết hạn".
Điều này có vẻ tốt. Chúng tôi chỉ cần sửa ngày hết hạn để hạ cánh vào ngày cuối cùng của tháng.
Chà, hóa ra có một chức năng thú vị được gọi là EOMONTH (cho cuối tháng) dùng để xem ngày cuối cùng của tháng trong quá khứ hoặc tương lai.
Ngày bắt đầu là ngày được gia hạn và tháng là 12.
Và bạn đã có nó, mọi thứ mà sếp của bạn muốn, và bạn vẫn dành thời gian cho một tách cà phê trước bữa trưa.
Khóa học
Công thức cốt lõiCác phím tắt liên quan
Chèn bảng Ctrl + T ⌃ + T Mở rộng hoặc thu gọn dải băng Ctrl + F1 ⌘ + ⌥ + R Chọn bảng Ctrl + A ⌘ + A Di chuyển ô hiện hoạt theo chiều kim đồng hồ trong vùng chọn Ctrl + . ⌃ + .
