Trong bài viết này, Học Excel Online sẽ giải thích cách áp dụng các quy tắc được xây dựng trong Excel và tạo các định dạng có điều kiện của riêng bạn dựa trên các công thức.
Bạn đang xem : Công thức nhắc nhở ngày tháng trong excel
Và bây giờ chúng ta sẽ tận dụng kiến thức này và tạo ra các bảng tính phân biệt giữa các ngày trong tuần và cuối tuần, làm nổi bật các ngày lễ chính thức và hiển mốc thời gian sắp tới. Nói cách khác, chúng ta sẽ áp dụng định dạng có điều kiện Excel cho ngày. Nếu bạn có một số kiến thức cơ bản về các công thức Excel, bạn có thể quen với một số hàm ngày giờ như NOW, TODAY, DATE, WEEKDAY, vv Trong hướng dẫn này, chúng ta sẽ tìm hiểu sâu hơn các hàm để định dạng có điều kiện ngày theo cách bạn muốn.
Mục lục bài viết
Định dạng có điều kiện cho ngày tháng (các quy tắc cài sẵn)
Microsoft Excel phân phối 10 tùy chọn để định dạng những ô được chọn dựa trên ngày hiện tại .Để áp dụng định dạng, bạn chỉ cần vào tab Home> Conditional Formatting>Highlight Cell Rules và chọn A Date Occurring...Để vận dụng định dạng, bạn chỉ cần vào tabvà chọn
Định dạng có điều kiện ngày tháng trong Excel dựa trên ngày hiện tại:
Như bạn có thể biết Microsoft Excel cung cấp hàm TODAY () cho các phép tính khác nhau dựa trên ngày hiện tại. Đây chỉ là một vài ví dụ về cách bạn có thể sử dụng nó để định dạng có điều kiện trong Excel.
Ví dụ 1. Làm nổi bật ngày bằng, lớn hơn hoặc nhỏ hơn ngày hôm nay
Để định dạng có điều kiện các ô hoặc toàn bộ hàng dựa trên ngày hôm nay, bạn sử dụng hàm TODAY như sau:
Bằng với ngày hôm nay: = $ B2 = TODAY ()
Lớn hơn ngày hôm nay: = $ B2> TODAY ()
Nhỏ hơn ngày hôm nay: = $ B2 Ví dụ 2. Định dạng có điều kiện ngày dựa trên nhiều điều kiện
Tương tự như vậy, bạn có thể sử dụng hàm TODAY kết hợp với các hàm khác để xử lý các tình huống phức tạp hơn. Ví dụ: bạn có thể muốn công thức định dạng có điều kiện tô màu cột Invoice khi Ngày Giao hàng bằng hoặc lớn hơn ngày hôm nay nhưng bạn muốn định dạng trên biến mất khi nhập số hoá đơn.
Đối với trách nhiệm này, bạn sẽ cần một cột bổ trợ với công thức sau ( trong đó E là cột Deliveryvà cột F là Invoice :
= IF (E2> = TODAY (), IF (F2 = “”, 1, 0), 0)
Nếu ngày giao hàng lớn hơn hoặc bằng ngày hiện tại và không có số trong cột Hóa đơn, công thức sẽ trả về 1, nếu không thì số đó là 0 .
Sau đó bạn tạo một quy tắc định dạng điều kiện đơn giản cho cột Invoice có công thức = $G2 = 1 trong đó G là cột bổ sung của bạn. Tất nhiên, bạn sẽ có thể ẩn cột này sau.
Ví dụ 3. Đánh dấu những ngày sắp tới và đã quaGiả sử bạn có lịch trình dự án Bất Động Sản liệt kê những trách nhiệm, ngày mở màn và thời hạn triển khai của chúng. Những gì bạn muốn là ngày kết thúc cho mỗi việc làm được đo lường và thống kê tự động hóa. Một thử thách nữa là định dạng này được xem lại với mỗi cuối tuần. Ví dụ : nếu ngày mở màn là 13 tháng 6 năm năm trước và số ngày thao tác ( Duration ) là 2, ngày kết thúc sẽ là vào ngày 17 tháng 6 năm năm trước, vì 14 – tháng 6 và 15 – tháng 6 là Thứ 7 và Chủ Nhật .
Để làm điều này, chúng tôi sẽ sử dụng WORKDAY.INTL (start_date, days,, ), chính xác hơn = WORKDAY.INTL(B2, C2,1).
, là một bộ ngày tháng (phạm vi ô) cần được loại trừ khỏi lịch làm việc.Và ở đầu cuối, bạn hoàn toàn có thể muốn làm điển hình nổi bật những hàng tùy thuộc vào khoảng chừng thời hạn đến hạn. Ví dụ : những quy tắc định dạng có điều kiện kèm theo dựa trên hai công thức sau đây làm điển hình nổi bật ngày sắp kết thúc và ngày kết thúc gần đây, tương ứng :
= VÀ ($ D2-TODAY ()> = 0, $ D2-TODAY () = 0, TODAY () – $ D2 TODAY () – làm nổi bật tất cả các ngày trong tương lai (nghĩa là ngày lớn hơn ngày hiện tại). Bạn có thể sử dụng nó để làm nổi bật các sự kiện sắp tới.
Xem thêm : Cách Gỡ Bỏ Visual Studio năm ngoái Hoàn Toàn, Cách Gỡ Visual Studio năm ngoái Hoàn ToànTất nhiên, có những biến thể vô hạn của những công thức trên, tùy thuộc vào trách nhiệm đơn cử của bạn. Ví dụ := $ D2-TODAY ( ) > = 6 – làm điển hình nổi bật ngày xảy ra trong 6 ngày trở lên .= USD D2 = TODAY ( ) – 14 – nhấn mạnh vấn đề những ngày xảy ra đúng chuẩn 2 tuần trước .
Làm thế nào để làm nổi bật ngày tháng trong một phạm vi ngày:
Nếu bạn có một list dài những ngày trong bảng tính của mình, bạn cũng hoàn toàn có thể muốn làm điển hình nổi bật những ô hoặc hàng nằm trong khoanh vùng phạm vi ngày nhất định, tức là nhấn mạnh vấn đề toàn bộ những ngày nằm giữa hai ngày nhất định .
Bạn có thể hoàn thành nhiệm vụ này bằng cách sử dụng chức năng TODAY (). Bạn sẽ chỉ cần phải xây dựng một công thức tinh vi hơn một chút như trong các ví dụ dưới đây.
Công thức để làm nổi bật ngày tháng vừa quaHơn 30 ngày trước đây: = TODAY () – $A2> 30Từ 30 đến 15 ngày trước, bao gồm: = AND (TODAY () – $A2> = 15, TODAY () – $A2 Chưa đến 15 ngày trước: = AND (TODAY () – $A2> = 1, TODAY () – $A2 Ngày hiện tại và bất kể ngày tháng tương lai nào không được tô màu .
Công thức để làm nổi bật ngày trong tương lai Sẽ xảy ra trong hơn 30 ngày kể từ bây giờ: = $A2-TODAY ()> 30Trong 30 đến 15 ngày, bao gồm: = AND ($A2-TODAY ()> = 15, $A2-TODAY () Trong chưa đầy 15 ngày: = AND ($A2-TODAY ()> = 1, $A2-TODAY () Ngày hiện tại và bất kể ngày tháng vừa mới qua nào không được tô màu .
Làm thế nào để tô màu các khoảng trống và khoảng thời gian:
Trong ví dụ cuối cùng này, chúng ta sẽ sử dụng thêm một hàm date khác của Excel – DATEDIF (start_date, end_date, interval). Hàm này tính toán sự khác biệt giữa hai ngày dựa trên khoảng thời gian quy định. Nó khác với tất cả các hàm khác mà chúng tôi đã thảo luận trong hướng dẫn này theo cách mà nó cho phép bạn bỏ qua tháng hoặc năm và chỉ tính sự khác biệt giữa các ngày hoặc tháng, tùy theo bạn chọn.
Hãy tâm lý về nó bằng một cách khác … Giả sử bạn có một list những ngày sinh của những thành viên mái ấm gia đình và bạn hữu của bạn. Bạn có muốn biết có bao nhiêu ngày cho đến ngày sinh nhật tiếp theo ? Hơn nữa, còn bao nhiêu ngày nữa là đến ngày cưới và những sự kiện khác mà bạn không muốn bỏ lỡ ? Dễ dàng !Đây là công thức bạn cần ( trong đó A là cột Ngày của bạn ) := DATEDIF ( TODAY ( ), DATE ( ( NĂM ( TODAY ( ) ) + 1 ), MONTH ( $ A2 ), DAY ( $ A2 ) ), “ yd ” )Loại khoảng chừng thời hạn “ yd ” ở cuối của công thức được sử dụng để bỏ lỡ năm và chỉ tính chênh lệch giữa những ngày. Để có list không thiếu những loại khoảng chừng thời hạn khả dụng, hãy nhìn vào đây .
Mẹo. Nếu bạn quên mất hoặc đặt sai tên công thức phức tạp đó, bạn có thể sử dụng công thức phức tạp này: = 365-DATEDIF ($A2, TODAY (), “yd”). Nó tạo ra kết quả chính xác như nhau, chỉ cần nhớ thay thế 365 thành 366 trong năm nhuận:)
Và bây giờ hãy tạo một quy tắc định dạng có điều kiện để tô màu các khoảng trống khác nhau với các màu khác nhau. Trong trường hợp này, bạn nên sử dụng Excel Scales Color (Conditional formatting> Color Scales) hơn là tạo ra một quy tắc riêng cho từng khoảng thời gian.
Hình dưới đây cho thấy tác dụng trong Excel – một chuỗi màu với mức độ từ màu xanh lá cây đến màu đỏ trải qua màu vàng .
Ngoài ra để ứng dụng Excel vào việc làm một cách hiệu suất cao thì bạn còn phải sử dụng tốt những hàm, những công cụ khác của Excel .
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiệnCOUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiệnCác hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…Một số công cụ hay sử dụng như :Định dạng theo điều kiện với Conditional formattingThiết lập điều kiện nhập dữ liệu với Data ValidationCách đặt Name và sử dụng Name trong công thứcLập báo cáo với Pivot Table…Rất nhiều kiến thức và kỹ năng phải không nào ? Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kỹ năng và kiến thức một cách rất đầy đủ, chi tiết cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kỳ khi nào, thuận tiện tra cứu lại kỹ năng và kiến thức khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : bdkhtravinh.vn. OnlineTrong công thức, tất cả chúng ta nhập 1 làm tham số thứ 3 vì nó chỉ ra ngày thứ 7 và chủ nhật là đợt nghỉ lễ. Bạn hoàn toàn có thể sử dụng một giá trị khác nếu ngày cuối tuần của bạn khác, giả sử, Thứ Sáu và Thứ Bảy Theo tùy chọn, bạn cũng hoàn toàn có thể sử dụng tham số thứ 4
Source: https://bem2.vn
Category: TỔNG HỢP
