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.
Và giờ đây tất cả chúng ta sẽ tận dụng kiến thức này và tạo ra những bảng tính phân biệt giữa những ngày trong tuần và cuối tuần, làm điển hình nổi bật những đợt nghỉ lễ chính thức và hiển mốc thời hạn sắp tới. Nói cách khác, tất cả chúng ta sẽ vận dụng định dạng có điều kiện Excel cho ngày. Nếu bạn có một số ít kiến thức cơ bản về những công thức Excel, bạn hoàn toàn có thể quen với một số ít hàm ngày giờ như NOW, TODAY, DATE, WEEKDAY, vv Trong hướng dẫn này, tất cả chúng ta sẽ tìm hiểu và khám phá sâu hơn những hàm để định dạng có điều kiện ngày theo cách bạn muốn .
Định dạng có điều kiện cho ngày tháng (các quy tắc cài sẵn)
Microsoft Excel cung ứng 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...
- Chọn một trong các tùy chọn ngày từ danh sách thả xuống ở phần bên tay trái của cửa sổ, từ tháng trước đến tháng sau.
- Cuối cùng, chọn một trong các định dạng có sẵn hoặc thiết lập định dạng của bạn bằng cách chọn các tuỳ chọn khác nhau trên tab Font, Border và Fill. Nếu bảng tính cơ bản không đủ, bạn có thể nhấp vào nút More colors…
- Nhấp vào OK và tận hưởng kết quả! 🙂
Tuy nhiên, cách nhanh và trực tiếp này có hai hạn chế đáng kể – 1 ) chỉ hoạt động giải trí với những ô đã chọn và 2 ) định dạng có điều kiện luôn được vận dụng dựa trên ngày hiện tại .
Công thức định dạng có điều kiện của Excel cho ngày tháng
Nếu bạn muốn làm điển hình nổi bật những ô hoặc hàng loạt hàng dựa trên một ngày trong một ô khác, hoặc tạo những quy tắc cho những khoảng chừng thời hạn lớn hơn ( tức là hơn một tháng kể từ ngày hiện tại ), bạn sẽ phải tự tạo ra quy tắc định dạng có điều kiện của mình dựa trên một công thức. Dưới đây bạn sẽ tìm thấy một vài ví dụ về định dạng có điều kiện của Excel cho ngày tháng .
Xem thêm : Cách dùng hàm IF trong Exel : Công thức cho số, ký tự, tài liệu và ô trống
Cách làm nổi bật ngày cuối tuần trong Excel:
Thật đáng tiếc, Microsoft Excel không có lịch trình tích hợp tựa như như Outlook. Chúng ta hãy xem làm thế nào để hoàn toàn có thể tạo ra lịch tự động hóa của riêng mình .
Khi thiết kế lịch, bạn có thể sử dụng hàm = DATE (năm, tháng, ngày) để hiển thị những ngày trong tuần. Đơn giản chỉ cần nhập năm và số tháng vào nơi nào đó trong bảng tính của bạn và tham chiếu những ô đó trong công thức. Tất nhiên, bạn có thể nhập các con số trực tiếp vào công thức, nhưng đây không phải là cách làm hiệu quả vì bạn phải điều chỉnh công thức cho mỗi tháng.
Hình bên dưới minh họa hàm DATE. Tôi đã sử dụng công thức = DATE ($B$2, $B$1, B$4) được sao chép qua hàng thứ 5.
Mẹo. Nếu bạn chỉ muốn hiển thị những ngày trong tuần như bạn thấy trong hình trên, hãy chọn các ô có công thức (dòng 5 trong trường hợp của chúng ta), nhấp chuột phải và chọn Format Cells …> Number> Custom. Từ danh sách tùy chọn dưới Type, chọn dddd hoặc ddd để hiển thị tên đầy đủ hoặc tên viết tắt, tương ứng. Lịch Excel của bạn đã gần xong, và bạn chỉ cần thay đổi màu sắc cuối tuần. Đương nhiên, bạn sẽ không phải làm thủ công. Chúng ta sẽ tự động định dạng các ngày cuối tuần bằng cách tạo quy tắc định dạng có điều kiện dựa trên hàm WEEKDAY.
- Bạn bắt đầu bằng cách chọn lịch Excel của mình nơi bạn muốn đánh dấu những ngày cuối tuần. Trong trường hợp này, đó là vùng $B$4: $AE$10. Hãy chắc chắn bắt đầu lựa chọn với cột ngày đầu tiên – Colum B trong ví dụ này.
- Trên tab Home, nhấp vào Conditional Formatting menu> New Rule.
- Tạo ra một quy tắc định dạng mới dựa trên một công thức, như đã được giải thích trong Làm thế nào để tạo các quy tắc định dạng có điều kiện của Excel với một công thức.
- Trong hộp “Format values where this formula is true”, hãy nhập công thức WEEKDAY sau đây để xác định ô nào là Thứ bảy và Chủ nhật: = WEEKDAY (B$5,2)> 5.
- Nhấp vào nút Format … và thiết lập định dạng tùy chỉnh của bạn bằng cách chuyển giữa các tab Font, Border và Fill và các tùy chọn khác. Khi hoàn tất, nhấp vào nút OK để xem lại quy tắc.
Bây giờ, hãy để tôi giải thích ngắn gọn công thức WEEKDAY (serial_number, [return_type]) để bạn có thể nhanh chóng điều chỉnh nó cho bảng tính của riêng bạn.
- serial_number đại diện cho ngày mà bạn đang tìm kiếm. Bạn nhập một tham chiếu đến ô đầu tiên của bạn với một ngày, B$5 trong trường hợp của chúng tôi.
- [return_type] xác định loại tuần (dấu ngoặc vuông ngụ ý nó là tùy chọn). Bạn nhập 2 là loại trả về cho một tuần bắt đầu từ Thứ Hai (1) đến Chủ Nhật (7).
- Cuối cùng, bạn viết> 5 để chỉ các ngày thứ bảy (6) và chủ nhật (7).
Hình dưới đây cho thấy hiệu quả trong Excel 2013 – những ngày cuối tuần được ghi lại bằng màu đỏ .
Mẹo:
- Nếu công ty bạn có ngày nghỉ không theo chuẩn, ví dụ: Thứ Sáu và Thứ Bảy, sau đó bạn cần tinh chỉnh công thức để bắt đầu đếm từ Chủ Nhật (1) và làm nổi bật ngày 6 (Thứ 6) và 7 (Thứ 7) – WEEKDAY(B$5,1)> 5.
- Nếu bạn đang tạo lịch ngang, hãy sử dụng cột tương đối (không có $) và dòng tuyệt đối (với $) trong tham chiếu ô vì bạn nên khóa tham chiếu của hàng – trong ví dụ trên là hàng 5, do đó Chúng tôi đã nhập vào B$5. Nhưng nếu bạn đang thiết kế lịch theo hướng dọc, bạn nên làm ngược lại, tức là sử dụng một cột tuyệt đối và hàng tương đối, ví dụ: $B5 như bạn thấy trong hình dưới đây:
Làm thế nào để làm nổi bật ngày nghỉ trong Excel:
Để cải tổ lịch của bạn hơn nữa, bạn cũng hoàn toàn có thể ghi lại những đợt nghỉ lễ. Để làm điều đó, bạn sẽ cần liệt kê những ngày lễ bạn muốn làm điển hình nổi bật trong cùng một bảng tính hoặc một số ít bảng tính khác .
Ví dụ : tôi đã thêm những đợt nghỉ lễ sau vào cột A ( $ A $ 14 : USD A $ 17 ) .
Một lần nữa, bạn mở Conditional Formatting> New Rule. Trong trường hợp nghỉ lễ, bạn sẽ sử dụng hàm MATCH hoặc COUNTIF:
- = COUNTIF ($A$14: $A$17, B$5)> 0
- = MATCH (B$5, $A$14: $A$17,0)
Chú thích. Nếu bạn đã chọn màu khác cho ngày nghỉ, bạn cần di chuyển danh sách ngày nghỉ lễ lên đầu thông qua Conditional Formatting> Manage Rules…
Hình dưới đây cho thấy tác dụng trong Excel 2013 :
Định dạng có điều kiện một ô khi giá trị ô được thay đổi thành một ngày:
Không phải là một yếu tố lớn khi định dạng có điều kiện một ô khi một ngày được thêm vào ô đó hoặc bất kể ô nào khác trong cùng hàng miễn là không có loại giá trị khác. Trong trường hợp này, bạn chỉ cần sử dụng một công thức để làm điển hình nổi bật khoảng trống, như được diễn đạt trong những công thức có điều kiện của Excel so với những khoảng trống và không trống. Nhưng nếu những ô đó có giá trị, ví dụ : văn bản, và bạn muốn biến hóa màu nền khi văn bản được đổi khác thành một ngày ?
Nhiệm vụ nghe có vẻ như phức tạp, nhưng giải pháp rất đơn thuần .
- Trước hết, bạn cần phải xác định mã định dạng của ngày. Đây chỉ là một vài ví dụ:
- D1: dd-mmm-yy hoặc d-mmm-yy
- D2: dd-mmm hoặc d-mmm
- D3: mmm-yy
- D4: mm / dd / yy hoặc m / d / yy hoặc m / d / yy h: mm
- Bạn có thể tìm thấy danh sách đầy đủ mã số ngày trong bài viết này.
- Chọn cột nơi bạn muốn thay đổi màu của các ô hoặc toàn bộ bảng trong trường hợp bạn muốn đánh dấu các hàng.
- Và bây giờ tạo một quy tắc định dạng có điều kiện bằng cách sử dụng công thức tương tự như sau: = CELL (“format”, $ A2) = “D1”. Trong công thức, A là cột có ngày tháng và D1 là định dạng ngày tháng.
Nếu bảng của bạn có ngày ở 2 hoặc nhiều định dạng, sau đó sử dụng hàm OR, ví dụ: =OR(cell(“format”, $A2)=”D1″, cell(“format”,$A2)=”D2″, cell(“format”, $A2)=”D3″)
Hình bên dưới minh họa tác dụng của quy tắc định dạng có điều kiện cho ngày tháng .
Làm thế nào để làm nổi bật các hàng dựa trên một ngày nhất định trong một cột nhất định:
Giả sử bạn có một bảng tính Excel lớn có chứa hai cột ngày tháng ( B và C ). Bạn muốn làm điển hình nổi bật mỗi hàng có một ngày nhất định, ví dụ 13 – May-14, trong cột C .
Để vận dụng định dạng có điều kiện cho một ngày nào đó, thứ nhất bạn cần phải tìm giá trị số. Như bạn hoàn toàn có thể biết, Microsoft Excel tàng trữ những ngày như thể số thứ tự, mở màn từ ngày 1 tháng 1 năm 1900. Vì vậy, 1 tháng 1 năm 1900 được tàng trữ là 1, 2 tháng 1 năm 1900 được tàng trữ là 2 … và 13 – May-14 là 41772 .
Để tìm số ngày tháng, nhấp chuột phải vào ô, chọn Format Cells> Number và chọn định dạng General. Ghi lại số bạn thấy và nhấp Cancel bởi vì bạn không thực sự muốn thay đổi định dạng ngày.
Đó thực sự là phần chính của công việc và bây giờ bạn chỉ cần tạo ra một quy tắc định dạng có điều kiện cho toàn bộ bảng với công thức rất đơn giản này: = $C2 = 41772. Công thức hàm ý rằng bảng của bạn có tiêu đề và hàng 2 là hàng đầu tiên có dữ liệu.
Cách khác là sử dụng hàm DATEVALUE để chuyển đổi ngày thành định dạng số, ví dụ: = $C2 = DATEVALUE (“5/13/2014”)
Cho dù công thức bạn sử dụng, nó sẽ có tác dụng như nhau :
Đị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
Hình bên dưới minh họa những quy tắc trên .
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.
Xem thêm : Hàm IF và cách sử dụng hàm IF trong Excel
Đố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à đã qua
Giả 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, [weekend], [holidays]), chính xác hơn = WORKDAY.INTL(B2, C2,1).
Trong công thứ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à ngày lễ. Bạ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 có thể sử dụng tham số thứ 4 [ngày lễ], 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 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 ( ) < = 7 ) – lưu lại tổng thể những hàng có End date ( cột D ) trong 7 ngày tiếp theo. Công thức này thực sự có ích khi theo dõi ngày hết hạn sắp tới hoặc thanh toán giao dịch. = VÀ ( TODAY ( ) – USD D2 > = 0, TODAY ( ) – USD D2 < = 7 ) – ghi lại tổng thể những hàng trong đó End Date ( cột D ) là trong 7 ngày qua. Bạn hoàn toàn có thể sử dụng công thức này để theo dõi những khoản giao dịch thanh toán quá hạn và những chậm trễ khác .
Dưới đây là một vài ví dụ về công thức có thể được áp dụng cho bảng ở trên:
= $ D2
= USD D2 > TODAY ( ) – làm điển hình nổi bật toàn bộ những ngày trong tương lai ( nghĩa là ngày lớn hơn ngày hiện tại ). Bạn hoàn toàn có thể sử dụng nó để làm điển hình nổi bật những sự kiện sắp tới .
Tấ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 đề tổng thể 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 qua
- Hơn 30 ngày trước đây: = TODAY () – $A2> 30
- Từ 30 đến 15 ngày trước, bao gồm: = AND (TODAY () – $A2> = 15, TODAY () – $A2 <= 30)
- Chưa đến 15 ngày trước: = AND (TODAY () – $A2> = 1, TODAY () – $A2 <15)
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 ()> 30
- Trong 30 đến 15 ngày, bao gồm: = AND ($A2-TODAY ()> = 15, $A2-TODAY () <= 30)
- Trong chưa đầy 15 ngày: = AND ($A2-TODAY ()> = 1, $A2-TODAY () <15)
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 vừa đủ 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 .
Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các 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ện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Cá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 formatting
- Thiết lập điều kiện nhập dữ liệu với Data Validation
- Cách đặt Name và sử dụng Name trong công thức
- Lập báo cáo với Pivot Table…
Rất nhiều kỹ năng và kiến thức phải không nào ? Toàn bộ những kỹ năng và kiến thức 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 kiến thức và kỹ năng một cách khá đầy đủ, 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ó tặng thêm rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online
Source: https://www.doom.vodka
Category: Tin tức
Leave a Reply
You must be logged in to post a comment.