Trong bài viết này, học excel trực tuyến sẽ giải thích cách áp dụng các quy tắc có sẵn 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à bây giờ chúng tôi sẽ xây dựng dựa trên kiến thức này và tạo 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 thị lịch trình sắp tới. Nói cách khác, chúng tôi sẽ áp dụng định dạng có điều kiện trong excel cho ngày tháng. Nếu bạn có một số kiến thức cơ bản về công thức excel, bạn có thể quen thuộc với một số hàm ngày và giờ như bây giờ, hôm nay, ngày, ngày trong tuần, v.v. trong hướng dẫn này, chúng tôi sẽ đi sâu vào các hàm để định dạng có điều kiện ngày như bạn muốn. / p>
định dạng có điều kiện cho ngày (quy tắc đặt trước)
microsoft excel cung cấp 10 tùy chọn để định dạng các ô đã chọn dựa trên ngày hiện tại.
- Để áp dụng định dạng, chỉ cần truy cập trang chủ & gt; định dạng có điều kiện> đánh dấu quy tắc ô và chọn a ngày xảy ra .. .
- chọn một trong các tùy chọn ngày từ danh sách thả xuống ở bên trái 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 định cấu hình của riêng bạn bằng cách chọn các tùy chọn khác nhau trong các tab phông chữ, đường viền và điền . nếu bảng tính cơ bản không đủ, bạn có thể nhấp vào nút thêm màu …
- nhấp vào ok và tận hưởng kết quả.
- bắt đầu bằng cách chọn lịch excel của bạn nơi bạn muốn đánh dấu các ngày cuối tuần. trong trường hợp này, nó là vùng $ b $ 4: $ ae $ 10. đảm bảo bắt đầu lựa chọn với cột ngày đầu tiên – cột b trong ví dụ này.
- trên tab trang chủ , nhấp vào trình đơn định dạng có điều kiện & gt; quy tắc mới .
- tạo quy tắc định dạng mới dựa trên công thức, như được giải thích trong cách tạo quy tắc định dạng có điều kiện trong excel với công thức .
- Trong hộp “ giá trị định dạng mà công thức này đúng “, hãy nhập công thức ngày trong tuần sau để xác định ô nào là Thứ Bảy và Chủ Nhật: = ngày trong tuần (b $ 5, 2) & gt; 5 .
- nhấp vào nút định dạng … và đặt định dạng tùy chỉnh của bạn bằng cách chuyển đổi giữa các tab phông chữ, đường viền và điền và các tùy chọn khác. Khi bạn hoàn tất, hãy nhấp vào nút OK để xem lại quy tắc.
- serial_number đại diện cho ngày bạn đang tìm kiếm. bạn nhập một tham chiếu đến ô đầu tiên của mình 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 đơn ngụ ý là tùy chọn). nhập 2 làm kiểu trả về cho một tuần từ Thứ Hai (1) đến Chủ Nhật (7).
- cuối cùng, bạn nhập & gt; 5 để chỉ Thứ Bảy (6) và Chủ Nhật (7).
- nếu công ty của bạn có những ngày nghỉ không theo tiêu chuẩn, chẳng hạn như thứ sáu và thứ bảy, bạn cần điều chỉnh công thức để bắt đầu tính từ Chủ nhật (1) và đánh dấu ngày thứ 6 (thứ sáu) và thứ bảy (thứ bảy) – ngày trong tuần (b $ 5,1) & gt; 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à một hàng tuyệt đối (có $) trong tham chiếu ô vì bạn phải khóa tham chiếu hàng; trong ví dụ trên, hàng 5, chúng tôi đã nhập 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à một hàng tương đối, ví dụ: $ b5 như trong hình bên dưới:
- = count ($ a $ 14: $ a $ 17, b $ 5) & gt; 0
- = khớp (b $ 5, $ a $ 14: $ a $ 17,0)
- Trước tiên, bạn phải chỉ định mã định dạng 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 đủ các mã ngày trong bài viết này.
- chọn cột mà 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 quy tắc định dạng có điều kiện bằ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 và d1 là định dạng ngày.
- công thức để đánh dấu các ngày trong quá khứ
- hơn 30 ngày trước: = today () – $ a2 & gt; 30
- từ 30 đến 15 ngày trước, bao gồm: = và (hôm nay () – $ a2> = 15, hôm nay () – $ a2 <= 30)
- chưa đầy 15 ngày trước: = và (hôm nay () – $ a2> = 1, hôm nay () – $ a2 <15)
- sẽ xảy ra sau 30 ngày hoặc hơn: = $ a2-today () & gt; 30
- trong 30 đến 15 ngày, bao gồm: = và ($ a2-hôm nay ()> = 15, $ a2-hôm nay () <= 30)
- trong vòng chưa đầy 15 ngày: = y ($ a2-hôm nay ()> = 1, $ a2-hôm nay () <15)
- sumif, sumif để tính tổng theo 1 điều kiện, nhiều điều kiện
- countif, countifs cho thống kê, đếm theo một điều kiện, nhiều điều kiện
- hàm xử lý chuỗi, ngày tháng, dữ liệu số…
- chỉ mục + kết hợp, sản phẩm …
- định dạng với định dạng có điều kiện
- thiết lập các điều kiện nhập dữ liệu với xác thực dữ liệu
- cách đặt tên và sử dụng tên trong công thức
- báo cáo với bảng tổng hợp …
Tuy nhiên, cách nhanh chóng và đơn giản này có hai hạn chế chính: 1) nó chỉ hoạt động với các ô đã chọn và 2) định dạng có điều kiện luôn được áp dụng dựa trên ngày hiện tại.
công thức định dạng có điều kiện trong excel cho ngày
Nếu bạn muốn đánh dấu các ô hoặc toàn bộ hàng dựa trên một ngày trong một ô khác hoặc tạo các quy tắc trong khoảng thời gian dài hơn (tức là hơn một tháng kể từ ngày hiện tại), bạn sẽ cần tạo điểm đánh dấu của riêng mình quy tắc. định dạng có điều kiện dựa trên công thức. Dưới đây là một số ví dụ về định dạng có điều kiện trong excel cho ngày tháng.
xem thêm: cách sử dụng hàm if trong excel: công thức cho số, ký tự, dữ liệu và ô trống
cách đánh dấu các ngày cuối tuần trong excel:
Rất tiếc, Microsoft Excel không có bộ lập lịch tích hợp tương tự như Outlook. hãy xem cách bạn có thể tạo lịch tự động của riêng mình.
Khi thiết kế lịch, bạn có thể sử dụng chức năng = date (năm, tháng, ngày) để hiển thị các ngày trong tuần. chỉ cần nhập số năm và tháng ở đâu đó trong bảng tính của bạn và tham chiếu các ô đó trong công thức. Tất nhiên, bạn có thể nhập số trực tiếp vào công thức, nhưng điều này không hiệu quả vì bạn phải điều chỉnh công thức cho mỗi tháng.
Hình ảnh sau đây cho thấy chức năng ngày. Tôi đã sử dụng công thức = date ($ b $ 2, $ b $ 1, b $ 4) được sao chép vào hàng thứ năm.
mẹo . nếu bạn chỉ muốn hiển thị các 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 (trong trường hợp của chúng tôi là dòng 5), nhấp chuột phải và chọn định dạng ô… & gt; số & gt; phong tục . từ danh sách các tùy chọn trong loại , hãy 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 như đã sẵn sàng và bạn chỉ cần thay đổi màu sắc của ngày cuối tuần. Tất nhiên, bạn sẽ không phải làm điều đó theo cách thủ công. chúng tôi sẽ tự động định dạng vào 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 ngày trong tuần.
Bây giờ, hãy để tôi giải thích ngắn gọn công thức ngày trong tuần (serial_number, [return_type]) để bạn có thể nhanh chóng điều chỉnh công thức này cho phù hợp với bảng tính của mình.
Hình ảnh dưới đây cho thấy kết quả trong excel 2013 – các ngày cuối tuần được đánh dấu bằng màu đỏ.
Mẹo:
cách đánh dấu các ngày lễ trong excel:
Để nâng cao hơn nữa lịch của bạn, bạn cũng có thể đánh dấu các ngày lễ. Để làm được điều đó, bạn cần liệt kê những ngày lễ mà bạn muốn đánh dấu trên cùng một trang tính hoặc một trang tính khác.
ví dụ: tôi đã thêm các ngày lễ sau vào cột a ($ a $ 14: $ 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:
bình luận. nếu bạn đã chọn một màu khác cho ngày lễ, bạn cần di chuyển danh sách các ngày lễ lên đầu thông qua định dạng có điều kiện & gt; quản lý các quy tắc …
Hình ảnh sau đây cho thấy kết quả 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:
Định dạng ô có điều kiện không phải là vấn đề lớn khi ngày được thêm vào ô đó hoặc bất kỳ ô nào khác trong cùng một hàng, miễn là không có loại giá trị nào khác. Trong trường hợp này, bạn có thể chỉ cần sử dụng một công thức để đánh dấu khoảng trắng, như được mô tả trong Công thức có điều kiện của Excel cho Khoảng trắng và Không phải Khoảng trắng. nhưng điều gì sẽ xảy ra nếu những ô đó có một giá trị, chẳng hạn như văn bản và bạn muốn thay đổi màu nền khi văn bản được thay đổi thành ngày tháng?
Nhiệm vụ nghe có vẻ phức tạp, nhưng giải pháp rất đơn giản.
nếu bảng của bạn có ngày ở 2 định dạng trở lên, hãy sử dụng hàm o, ví dụ = hoặc (ô (“định dạng”, $ a2) = ”d1 ″, ô (“ định dạng ”)”, $ a2) = ”d2 ″, ô (“ format ”, $ a2) =” d3 ″)
họa tiết bên dưới minh họa kết quả của quy tắc định dạng có điều kiện cho ngày tháng.
cách đánh dấu các hàng dựa trên một ngày cụ thể trong một cột nhất định:
Giả sử bạn có một bảng tính excel lớn chứa hai cột ngày tháng (b và c). bạn muốn đánh dấu từng hàng với một ngày nhất định, ví dụ: 13-tháng-năm-14, trong cột c.
Để áp dụng định dạng có điều kiện cho một ngày, trước tiên bạn phải tìm giá trị số. như bạn có thể biết, microsoft excel lưu trữ ngày tháng dưới dạng số thứ tự, bắt đầu bằng jan 1, 1900. vì vậy jan 1, 1900 được lưu trữ là 1, jan 2, 1900 được lưu trữ là 2 … và vào ngày 13 tháng 5 năm 14 là 41772.
Để tìm số ngày, hãy nhấp chuột phải vào ô, chọn định dạng ô>; số và chọn định dạng chung . ghi lại số bạn thấy và nhấp vào hủy vì bạn thực sự không 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 quy tắc định dạng có điều kiện cho toàn bảng với công thức rất đơn giản sau: = $ c2 = 41772
strong>. công thức ngụ ý 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.
Ngoài ra, hãy sử dụng hàm date value để chuyển đổi ngày sang định dạng số, ví dụ: = $ c2 = date value (“05/13/2014”)
bất kể bạn sử dụng công thức nào, nó sẽ có cùng kết quả:
Đị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.
xem thêm: tài liệu excel cơ bản hay nhất của một thời đại
ví dụ 1. đánh dấu các ngày bằng, lớn hơn hoặc nhỏ hơn ngày hôm nay
Để định dạng ô hoặc toàn bộ hàng có điều kiện dựa trên ngày hôm nay, hãy sử dụng hàm hôm nay như sau:
bằng với ngày hôm nay: = $ b2 = hôm nay ()
lớn hơn ngày hôm nay: = $ b2 & gt; hôm nay ()
ít hơn hôm nay: = $ b2 & lt; hôm nay ()
Dấu gạch đầu dòng sau minh họa các 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ự, bạn có thể sử dụng hàm hôm nay 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 hóa đơn khi ngày giao hàng bằng hoặc muộn hơn ngày hôm nay, nhưng bạn muốn định dạng cũ biến mất khi nhập ngày hóa đơn. con số.
xem thêm: hàm if và cách sử dụng hàm if trong excel
cho tác vụ này, bạn sẽ cần một cột bổ sung có công thức sau (trong đó e là cột giao hàng và cột f là hóa đơn):
= 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 đó tạo quy tắc định dạng có điều kiện đơn giản cho cột hóa đơn có công thức = $ g2 = 1 trong đó g là cột bổ sung của bạn. tất nhiên, bạn 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ó một lịch trình dự án liệt kê các nhiệm vụ, ngày và giờ bắt đầu của chúng. bạn chỉ muốn ngày kết thúc cho mỗi công việc được tính toán tự động. một thách thức khác là định dạng này được xem xét vào mỗi cuối tuần. Ví dụ: nếu ngày bắt đầu là ngày 13 tháng 6 năm 2014 và số ngày làm việc (thời lượng) là 2 thì ngày kết thúc sẽ là ngày 17 tháng 6 năm 2014, vì ngày 14 tháng 6 và ngày 15 tháng 6 là Thứ Bảy và Chủ Nhật.
Để thực hiện việc này, chúng tôi sẽ sử dụng workday.intl (start_date, days, [cuối tuần], [ngày lễ]) , chính xác hơn là = 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à cuối cùng, bạn có thể muốn đánh dấu các hàng dựa trên thời gian hết hạn. ví dụ: các quy tắc định dạng có điều kiện dựa trên hai công thức sau đánh dấu ngày hoàn thành gần đây và ngày gần hoàn thành, tương ứng:
= y ($ d2-today ()> = 0, $ d2-today () & lt; = 7) – đánh dấu tất cả các hàng có ngày kết thúc (cột d) trong 7 ngày tới. Công thức này thực sự hữu ích khi theo dõi các ngày đến hạn hoặc các khoản thanh toán sắp tới. = y (hôm nay () – $ d2> = 0, hôm nay () – $ d2 <= 7) – đánh dấu tất cả các hàng có ngày kết thúc (cột d) trong 7 ngày qua. bạn có thể sử dụng công thức này để theo dõi các khoản thanh toán quá hạn và các khoản chậm trễ khác.
Dưới đây là một số ví dụ về công thức có thể áp dụng cho bảng trên:
= $ d2 & lt; today () – đánh dấu tất cả các ngày trong quá khứ (tức là ngày trước ngày hiện tại). có thể được sử dụng để định dạng các đăng ký quá hạn, thanh toán quá hạn, v.v.
= $ d2 & gt; hôm nay () – Đánh dấu tất cả các ngày trong tương lai (tức là các ngày sau ngày hiện tại). bạn có thể sử dụng nó để đánh dấu các sự kiện sắp tới.
Tất nhiên, có vô số biến thể của các công thức trên, tùy thuộc vào nhiệm vụ cụ thể của bạn. ví dụ:
= $ d2-today () & gt; = 6: đánh dấu các ngày diễn ra từ 6 ngày trở lên.
= $ d2 = today () – 14 – những ngày nổi bật diễn ra cách đây đúng 2 tuần.
cách đánh dấu các ngày trong một phạm vi ngày:
Nếu bạn có một danh sách dài các ngày trong bảng tính của mình, bạn cũng có thể đánh dấu các ô hoặc hàng nằm trong một phạm vi ngày nhất định, tức là đánh dấu tất cả các ngày giữa hai ngày nhất định.
Bạn có thể hoàn thành tác vụ này bằng cách sử dụng hàm today (). Bạn chỉ cần tạo một công thức phức tạp hơn một chút như được hiển thị trong các ví dụ bên dưới. .
ngày hiện tại và ngày trong tương lai không được tô màu.
ngày hiện tại và ngày trước không được tô màu.
cách tô màu khoảng trắng và khoảng cách:
Trong ví dụ cuối cùng này, chúng tôi sẽ sử dụng một hàm ngày khác trong excel: dateif (ngày tháng bắt đầu, ngày kết thúc, khoảng thời gian). 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 đã chỉ định. nó khác với tất cả các chức năng khác mà chúng ta thảo luận trong hướng dẫn này ở chỗ nó cho phép bạn bỏ qua tháng hoặc năm và chỉ tính toán sự khác biệt giữa các ngày hoặc tháng, tùy theo bạn chọn.
Hãy nghĩ theo cách khác … giả sử bạn có danh sách sinh nhật của bạn bè và gia đình. Bạn có muốn biết còn bao nhiêu ngày nữa là đến sinh nhật tiếp theo không? Ngoài ra, còn bao nhiêu ngày nữa là đến ngày cưới của bạn 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 tháng của bạn):
= datei (hôm nay ()), ngày ((năm (hôm nay ()) + 1), tháng ($ a2), ngày ($ a2)), “yd”)
Loại dấu chấm “yd” ở cuối công thức được sử dụng để bỏ qua năm và chỉ tính toán sự khác biệt giữa các ngày. để biết danh sách đầy đủ các loại khoảng thời gian có sẵn, hãy xem tại đây.
mẹo . nếu bạn quên hoặc đặt tên sai cho 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-dateif ($ a2, today (), “yd”) . tạo ra kết quả chính xác, chỉ cần nhớ thay 365 bằng 366 vào năm nhuận 🙂
và bây giờ chúng ta sẽ tạo quy tắc định dạng có điều kiện để tô màu khoảng trắng khác với các màu khác nhau. trong trường hợp này, tốt hơn nên sử dụng thang màu excel ( định dạng có điều kiện > thang màu ) thay vì tạo quy tắc riêng cho từng khoảng thời gian. > & gt; thang màu p>
xem thêm: khóa học vba tại hà nội
Hình sau cho thấy kết quả trong excel: một loạt các màu có độ từ xanh lục đến đỏ đến vàng.
nguồn: canbits, được dịch và chỉnh sửa bởi hocexcel trực tuyến.
Ngoài việc áp dụng excel để làm việc hiệu quả, bạn còn phải sử dụng tốt các hàm excel và các công cụ khác.
một số chức năng cơ bản phổ biến như:
một số công cụ hữu ích như:
rất nhiều kiến thức, phải không? Bạn có thể học tất cả những kiến thức này trong khóa học ex101 – excel từ cơ bản đến chuyên gia excel trực tuyến. Đây là khóa học giúp bạn hiểu về hệ thống một cách đầy đủ và chi tiết. Ngoài ra, không giới hạn thời gian học nên bạn có thể thoải mái học bất cứ lúc nào và dễ dàng tìm kiếm kiến thức khi cần thiết. Hiện tại hệ thống đang có chương trình ưu đãi giảm giá cực tốt cho các bạn khi đăng ký khóa học. xem chi tiết tại: hocexcel.online