Định dạng có điều kiện trong Excel - chi tiết với các ví dụ

Nếu bạn cần trình bày thông tin một cách trực quan thì sẽ rất thuận tiện khi thực hiện việc này bằng cách sử dụng định dạng có điều kiện. Người dùng sử dụng dịch vụ này sẽ tiết kiệm được một lượng lớn năng lượng và thời gian. Để có được thông tin cần thiết, chỉ cần xem nhanh tệp là đủ.

Cách thực hiện định dạng có điều kiện trong Excel

Bạn có thể tìm thấy “Định dạng có điều kiện” trên tab đầu tiên của dải băng bằng cách đi tới phần “Kiểu”. 

Tiếp theo, bạn cần dùng mắt tìm biểu tượng mũi tên hơi chếch về bên phải, di chuyển con trỏ đến đó. Sau đó, cài đặt sẽ mở ra, nơi bạn có thể cấu hình linh hoạt tất cả các tham số cần thiết. 

Tiếp theo, bạn cần chọn toán tử thích hợp để so sánh biến mong muốn với một số. Có bốn toán tử so sánh – lớn hơn, nhỏ hơn, bằng và giữa. Chúng được liệt kê trong menu quy tắc. 

Tiếp theo, chúng tôi chỉ định một chuỗi số trong phạm vi A1:A11.

Hãy định dạng một tập dữ liệu phù hợp. Sau đó, chúng tôi mở menu cài đặt “Định dạng có điều kiện” và đặt tiêu chí lựa chọn dữ liệu cần thiết. Ví dụ: trong trường hợp của chúng tôi, chúng tôi sẽ chọn tiêu chí “Thêm”. 

Một cửa sổ với một bộ tùy chọn sẽ mở ra. Ở phần bên trái của cửa sổ có một trường mà bạn cần chỉ định số 15. Ở phần bên phải, phương pháp đánh dấu thông tin được chỉ định, miễn là nó đáp ứng tiêu chí đã chỉ định trước đó. Kết quả sẽ được hiển thị ngay lập tức.

Tiếp theo, hoàn tất cấu hình bằng nút OK.

Định dạng có điều kiện để làm gì?

Ở một mức độ nào đó, có thể nói nó là một công cụ, sau khi tìm hiểu thì làm việc với Excel sẽ không bao giờ giống nhau. Điều này là do nó làm cho cuộc sống dễ dàng hơn nhiều. Thay vì mỗi lần thiết lập thủ công định dạng của một ô phù hợp với một điều kiện nhất định và cũng cố gắng tự kiểm tra xem nó có tuân thủ tiêu chí này hay không, bạn chỉ cần đặt cài đặt một lần và sau đó Excel sẽ tự thực hiện mọi việc.

Ví dụ: bạn có thể làm cho tất cả các ô chứa số lớn hơn 100 chuyển sang màu đỏ. Hoặc, xác định xem còn bao nhiêu ngày nữa cho đến lần thanh toán tiếp theo, sau đó tô màu xanh lục vào những ô mà thời hạn vẫn chưa còn đủ xa. 

Hãy lấy một ví dụ.

Bảng này hiển thị hàng tồn kho trong kho. Nó có cột. dưới dạng sản phẩm, doanh số bán hàng trung bình (được tính bằng đơn vị mỗi tuần), lượng hàng tồn kho và số tuần sản phẩm này còn lại.

Hơn nữa, nhiệm vụ của người quản lý mua hàng là xác định những vị trí đó và việc bổ sung vị trí đó là cần thiết. Để làm điều này, bạn cần nhìn vào cột thứ tư từ bên trái, cột này ghi lại lượng hàng tồn kho theo tuần.

Giả sử tiêu chí xác định nguyên nhân gây hoảng loạn là thời gian tồn kho dưới 3 tuần. Điều này cho thấy rằng chúng ta cần chuẩn bị một đơn đặt hàng. Nếu lượng hàng tồn kho dưới hai tuần, thì điều này cho thấy cần phải đặt hàng gấp. Nếu bảng chứa một số lượng lớn các vị trí, thì việc kiểm tra thủ công xem còn lại bao nhiêu tuần là khá khó khăn. Ngay cả khi bạn sử dụng tìm kiếm. Và bây giờ chúng ta hãy xem chiếc bàn trông như thế nào, trong đó làm nổi bật hàng hóa khan hiếm bằng màu đỏ.

Và thực sự, nó dễ dàng điều hướng hơn nhiều. 

Đúng, ví dụ này mang tính giáo dục, có phần đơn giản hơn so với hình ảnh thực tế. Và số giây và số phút được lưu khi sử dụng thường xuyên các bảng như vậy sẽ chuyển thành giờ. Bây giờ chỉ cần nhìn vào bảng là đủ hiểu mặt hàng nào đang thiếu và không mất hàng giờ để phân tích từng ô (nếu có hàng nghìn mặt hàng như vậy).

Nếu có hàng “màu vàng” thì bạn cần bắt đầu mua chúng. Nếu vị trí tương ứng có màu đỏ thì bạn cần phải thực hiện ngay. 

Theo giá trị của ô khác

Bây giờ chúng ta hãy xem ví dụ thực tế sau đây.

Giả sử chúng ta có một bảng như vậy và chúng ta phải đối mặt với nhiệm vụ đánh dấu các hàng chứa các giá trị nhất định. 

Vì vậy, nếu dự án vẫn đang được thực hiện (nghĩa là nó chưa được hoàn thành nên được đánh dấu bằng chữ “P”), thì chúng ta cần làm nền của nó màu đỏ. Các dự án đã hoàn thành được đánh dấu bằng màu xanh lá cây.

Trình tự hành động của chúng ta trong tình huống này sẽ như sau:

1. Chọn một phạm vi giá trị. 
2. Nhấp vào nút “Định dạng có điều kiện” – nút “Tạo quy tắc”.
3. Xin lưu ý rằng trong trường hợp của chúng tôi, bạn cần áp dụng công thức dưới dạng quy tắc. Tiếp theo chúng ta sử dụng hàm IFđể làm nổi bật các dòng phù hợp. 

Tiếp theo, điền vào các dòng như trong hình này.

Quan trọng! Chuỗi phải được tham chiếu tuyệt đối. Nếu chúng ta tham chiếu đến một ô, thì trong trường hợp này nó được trộn lẫn (có cố định cột).

Tương tự, một quy tắc được tạo cho những dự án công việc chưa được hoàn thành cho đến nay. 

Tiêu chí của chúng tôi trông như thế này.

Và cuối cùng, kết quả là chúng ta có một cơ sở dữ liệu như vậy.

Nhiều điều kiện

Để rõ ràng, hãy lấy phạm vi A1:A11 từ bảng để chứng minh cách, trong một ví dụ thực tế thực tế, bạn có thể sử dụng một số điều kiện để xác định định dạng ô.

Bản thân các điều kiện như sau: nếu số trong ô vượt quá giá trị 6 thì nó sẽ được tô sáng màu đỏ. Nếu nó là màu xanh lá cây thì màu xanh lá cây. Và cuối cùng, những số lớn nhất, hơn 20, sẽ được tô màu vàng. 

Có một số cách thực hiện định dạng có điều kiện theo một số quy tắc.

1. Phương pháp 1. Chúng tôi chọn phạm vi của mình, sau đó, trong menu cài đặt định dạng có điều kiện, chọn quy tắc chọn ô như “Thêm”. Số 6 được viết ở bên trái và định dạng được đặt ở bên phải. Trong trường hợp của chúng tôi, phần tô phải có màu đỏ. Sau đó, chu kỳ được lặp lại hai lần, nhưng các thông số khác đã được đặt - lần lượt là hơn 10 và màu xanh lá cây và hơn 20 và màu vàng. Bạn sẽ nhận được kết quả như vậy.
   

   

2. Phương pháp 2. Vào menu cài đặt chính của công cụ “Định dạng có điều kiện” Excel. Ở đó, chúng tôi tìm thấy menu “Tạo quy tắc” và nhấp chuột trái vào mục này.
   

   

    Sau đó, Tiếp theo, chọn mục “Sử dụng công thức…” (được đánh dấu trong hình có khung màu đỏ) và đặt điều kiện đầu tiên. Sau đó, bấm vào OK. Và sau đó chu trình được lặp lại cho các điều kiện tiếp theo theo cách tương tự như mô tả ở trên, nhưng công thức được sử dụng.

   

Điều quan trọng là phải tính đến điều này. Trong ví dụ của chúng tôi, một số ô khớp với nhiều tiêu chí cùng một lúc. Excel giải quyết xung đột này như sau: quy tắc trên được áp dụng trước tiên. 

Và đây là một ảnh chụp màn hình để hiểu rõ hơn.

Ví dụ: chúng ta có số 24. Nó đáp ứng đồng thời cả ba điều kiện. Trong trường hợp này, sẽ có phần lấp đầy đáp ứng điều kiện đầu tiên, mặc dù thực tế là nó nằm ở điều kiện thứ ba nhiều hơn. Vì vậy, nếu điều quan trọng là các số trên 20 phải tô màu vàng thì điều kiện thứ ba phải được đặt lên hàng đầu. 

Định dạng ngày có điều kiện

Bây giờ hãy xem cách định dạng có điều kiện hoạt động với ngày tháng. Chúng tôi có một phạm vi tuyệt vời như vậy.

Trong trường hợp này, bạn cần đặt quy tắc định dạng có điều kiện, chẳng hạn như “Ngày”.

Sau đó, một hộp thoại sẽ mở ra với đầy đủ các điều kiện. Bạn có thể làm quen với chúng một cách chi tiết trong ảnh chụp màn hình này (chúng được liệt kê ở phía bên trái màn hình dưới dạng danh sách).

Chúng ta chỉ cần chọn cái thích hợp và nhấp vào nút “OK”.

Chúng tôi thấy rằng những ngày đề cập đến tuần trước tại thời điểm phạm vi này được vẽ được đánh dấu màu đỏ. 

Sử dụng công thức

Bộ quy tắc định dạng có điều kiện tiêu chuẩn khá lớn. Nhưng các tình huống đều khác nhau và danh sách tiêu chuẩn có thể không đủ. Trong trường hợp này, bạn có thể tạo quy tắc định dạng của riêng mình bằng công thức. 

Để thực hiện việc này, hãy nhấp vào nút “Tạo quy tắc” trong menu định dạng có điều kiện, sau đó chọn mục hiển thị trong ảnh chụp màn hình.

Hàng (theo giá trị ô)

Giả sử chúng ta cần đánh dấu hàng chứa một ô có giá trị nhất định. Trong trường hợp này, chúng ta cần thực hiện chuỗi hành động tương tự như trên, nhưng đối với chuỗi. Nghĩa là, bạn có thể chọn phạm vi áp dụng định dạng có điều kiện. 

Cách tạo quy tắc

Để tạo quy tắc, bạn phải sử dụng tùy chọn thích hợp trong phần “Định dạng có điều kiện”. Chúng ta hãy xem xét một chuỗi hành động cụ thể. 

Trước tiên, bạn cần tìm mục “Định dạng có điều kiện” trên dải băng trên tab “Trang chủ”. Nó có thể được nhận biết bằng các ô nhiều màu đặc trưng có kích thước khác nhau với một hình ảnh bên cạnh có dấu bằng bị gạch chéo. 

Có nút “Tạo quy tắc”. Bạn cũng có thể nhấp vào nút "Quản lý quy tắc", sau đó một hộp thoại sẽ mở ra, qua đó bạn cũng có thể tạo quy tắc bằng cách nhấp vào nút tương ứng ở góc trên bên trái. 

Bạn cũng có thể thấy trong cửa sổ này những quy tắc đã được áp dụng.

Quy tắc chọn ô

Được rồi, chúng ta đã nói rất nhiều về cách tạo quy tắc, nhưng nó là gì? Quy tắc chọn ô đề cập đến các tính năng được chương trình tính đến để quyết định cách định dạng các ô phù hợp với chúng. Ví dụ: điều kiện có thể lớn hơn một số nhất định, nhỏ hơn một số nhất định, một công thức, v.v. Có cả một bộ chúng. Bạn có thể làm quen với chúng và thực hành cách sử dụng chúng “trong hộp cát”. 

Định dạng tất cả các ô dựa trên giá trị của chúng

Một người có thể thiết lập phạm vi một cách độc lập: toàn bộ dòng, toàn bộ tài liệu hoặc một ô. Chương trình chỉ có thể tập trung vào giá trị của một ô. Điều này thêm tính linh hoạt cho quá trình. 

Chỉ định dạng các ô duy nhất hoặc trùng lặp

Có một số lượng lớn các quy tắc được đưa trực tiếp vào chương trình. Bạn có thể làm quen với chúng trong menu “Định dạng có điều kiện”. Đặc biệt, để định dạng chỉ áp dụng riêng cho các giá trị trùng lặp hoặc chỉ cho các giá trị duy nhất, có một tùy chọn đặc biệt – “Giá trị trùng lặp”.

Nếu bạn chọn mục này, một cửa sổ cài đặt sẽ mở ra, nơi bạn cũng có thể chọn tùy chọn ngược lại – chỉ chọn các giá trị duy nhất. 

Định dạng các giá trị trong phạm vi dưới và trên mức trung bình

Để định dạng các giá trị dưới hoặc trên mức trung bình, cũng có một tùy chọn đặc biệt trong cùng một menu. Nhưng bạn cần chọn một menu con khác – “Quy tắc chọn giá trị đầu tiên và giá trị cuối cùng”. 

Chỉ định dạng giá trị đầu tiên và cuối cùng

Trong cùng một menu con, chỉ có thể làm nổi bật các giá trị đầu tiên và cuối cùng bằng màu sắc, phông chữ đặc biệt và các phương pháp định dạng khác. Theo tiêu chuẩn, có một tùy chọn để chọn mười phần tử đầu tiên và cuối cùng, cũng như 10% tổng số ô có trong phạm vi này. Nhưng người dùng có thể độc lập xác định số lượng ô mình cần chọn.

Chỉ định dạng các ô có nội dung cụ thể

Để chỉ định dạng các ô có nội dung cụ thể, bạn phải chọn quy tắc định dạng Bằng hoặc Chứa văn bản. Sự khác biệt giữa chúng là trong trường hợp đầu tiên, chuỗi phải khớp hoàn toàn với tiêu chí và trong trường hợp thứ hai, chỉ một phần. 

Như bạn có thể thấy, định dạng có điều kiện là một tính năng đa chức năng của chương trình Excel mang lại rất nhiều lợi ích cho người đã thành thạo nó. Chúng tôi biết rằng thoạt nhìn, tất cả điều này có vẻ phức tạp. Nhưng trên thực tế, các bàn tay sẽ bị thu hút ngay khi nó xuất hiện để làm nổi bật một đoạn văn bản nhất định bằng màu đỏ (hoặc định dạng nó theo bất kỳ cách nào khác), dựa trên nội dung của nó hoặc theo một tiêu chí khác. Hàm này nằm trong bộ kiến ​​thức Excel cơ bản, nếu không có nó thì ngay cả những công việc nghiệp dư với bảng tính và cơ sở dữ liệu cũng không thể thực hiện được.