Định dạng có điều kiện trong Excel: Hướng dẫn và Sử dụng

Sử dụng các hướng dẫn này để tạo nhiều định dạng khác nhau

Định dạng có điều kiện trong Microsoft Excel là một công cụ hữu ích để làm cho bảng, báo cáo hoặc ô nổi bật hơn, để đánh dấu một khu vực hoặc một số cột, để phân tích dữ liệu, để làm rõ sự khác biệt hoặc xác định xu hướng. Thanh dữ liệu, bộ ký hiệu hoặc thang màu được sử dụng cho việc này. Tùy thuộc vào các điều kiện được chỉ định, việc hiển thị các ô được thay đổi hoặc phạm vi ô được định dạng. Bạn có thể tạo của riêng bạn cho nhiều điều kiện tích hợp.

Định dạng có điều kiện có ý nghĩa và giúp bạn

Định dạng có điều kiện trong Excel có thể được áp dụng theo nhiều cách, cho dù đó là cho bảng Excel, một phạm vi ô cụ thể hoặc báo cáo PivotTable. Nhiều hành động và các mẫu định dạng có sẵn cho bạn. Lợi thế quyết định đối với người dùng Excel là các giá trị hoặc kết quả đặc biệt có thể được đánh dấu bằng mã màu. Nếu một giá trị thay đổi, định dạng sẽ tự động được điều chỉnh - tùy thuộc vào các điều kiện.

thông tin: PivotTable ("Bảng Pivot") là một bảng đặc biệt có thể tính toán, tóm tắt và phân tích một lượng lớn dữ liệu. Ngoài ra, bạn có thể nhanh chóng so sánh và xác định xu hướng trong dữ liệu khám bệnh.

Định dạng có điều kiện với các quy tắc hiện có cho số, nội dung văn bản hoặc ngày tháng

Nếu bạn định dạng bằng toán tử so sánh, các ô có thể được tìm thấy dễ dàng hơn. Bạn có thể làm cho các giá trị hiển thị rõ ràng hơn, ví dụ: lớn hơn hoặc nhỏ hơn một giá trị đã xác định hoặc một số giá trị nhất định nằm giữa giới hạn trên và giới hạn dưới. Điều này có nghĩa là dữ liệu mong muốn hoặc được tìm kiếm được hiển thị bằng màu sắc. Định dạng nhanh hoặc định dạng nâng cao có sẵn để chỉnh sửa. Với định dạng mở rộng, bạn có thể làm việc với các công thức, chẳng hạn như để tự động đánh dấu một cột Excel bằng màu.

Ví dụ:

• Một nhân viên kho muốn đánh dấu tất cả các cổ phiếu dưới 15 chiếc bằng nền màu.
• Tất cả các thành phố với hơn 150.000 dân phải được đánh dấu trong một bảng.
• Một công ty sản xuất muốn làm nổi bật tất cả hàng hóa được sản xuất trong khoảng thời gian từ ngày 1 tháng 1 năm 2022-2023 đến ngày 31 tháng 1 năm 2022-2023 bằng màu đỏ.

Định dạng nhanh:

Lựa chọn một hoặc nhiều ô.

Nhấp vào "Bắt đầu" trên tab.

Đi tới mũi tên bên cạnh nút "Định dạng có điều kiện" trong "Kiểu".

Nhấp vào "Quy tắc đánh dấu ô".

Chọn lệnh mong muốn, ví dụ: B. lớn hơn, nhỏ hơn, giữa, ngày, v.v.

Nhập giá trị và chọn định dạng.

Định dạng mở rộng:

1. Chọn một hoặc nhiều ô trong bảng hoặc báo cáo PivotTable.

2. Đi tới tab "Trang chủ", trong nhóm Kiểu, nhấp vào mũi tên bên cạnh "Định dạng có điều kiện".

3. Nhấp vào "Quản lý quy tắc": Hộp thoại "Trình quản lý quy tắc định dạng có điều kiện" xuất hiện.

4. Chọn hành động:

   • Nhấp vào "Quy tắc mới": Hộp thoại "Quy tắc định dạng mới" xuất hiện.
   • Để thay đổi định dạng, hãy đảm bảo rằng trang tính hoặc bảng chính xác được chọn trong hộp danh sách "Hiển thị quy tắc định dạng cho".
   • Trong trường "Áp dụng cho", chuyển đến "Thu gọn hộp thoại"; sau đó chọn phạm vi ô mới trên trang tính.
   • Nhấp vào nút "Mở rộng Hộp thoại".
   • Chọn quy tắc và nhấp vào "Chỉnh sửa quy tắc": Hộp thoại "Chỉnh sửa quy tắc định dạng" xuất hiện.

5. Để thay đổi phạm vi cho các trường trong vùng giá trị "Áp dụng quy tắc cho" của báo cáo PivotTable, hãy thực hiện như sau:

   • Lựa chọn: Nhấp vào "Chỉ những ô này"
   • Trường tương ứng: Nhấp vào "Tất cả các ô có cùng trường"
   • Trường giá trị: Nhấp vào "Tất cả các ô"

6. Trong "Chọn loại quy tắc", hãy nhấp vào "Chỉ định dạng các ô có chứa"

Bây giờ, bạn có thể thực hiện các hành động trong "Chỉnh sửa mô tả quy tắc" trong trường danh sách "Chỉ định dạng ô với":

Định dạng theo số, ngày hoặc giờ:

Để thực hiện việc này, hãy chọn giá trị ô và toán tử so sánh và nhập số, ngày hoặc giờ. thí dụ: Đối với "Giữa", nhập 300 và 500 hoặc chọn "Bằng" và nhập ngày.

Bạn có thể sử dụng công thức để lấy lại giá trị số, ngày tháng hoặc thời gian.

Định dạng theo văn bản:

Chọn "Văn bản cụ thể" và "Toán tử quan hệ".

Nhập văn bản, ví dụ: B. on "chứa" từ "gold" hoặc trên "bắt đầu bằng" một tiền tố mà bạn muốn tìm kiếm.

Có thể nhập tối đa 255 ký tự.

Giá trị có thể được trả về bằng cách sử dụng công thức. Để làm điều này, công thức phải bắt đầu bằng (=). Công thức không hợp lệ không được định dạng. Nên kiểm tra công thức để tìm lỗi trước.

Định dạng theo ngày:

• Chọn ngày và ngày so sánh, ví dụ: B. Hôm qua, tuần sau hoặc tháng này

Sau đó: Để xác định một định dạng, nhấp chuột vào "Định dạng", hộp thoại "Định dạng ô" xuất hiện. Chọn định dạng số mong muốn, định dạng điền, khung hoặc phông chữ, nhấp vào "OK".

Có thể chọn nhiều định dạng. Bạn có thể xem định dạng nào đã được chọn trong trường "Xem trước".

Định dạng có điều kiện với cơ sở dữ liệu, lược đồ màu và bộ ký hiệu

Nội dung và giá trị trong ô có thể được nâng cấp trực quan với cơ sở dữ liệu, thang màu hoặc bộ ký hiệu và làm cho hấp dẫn hơn và rõ ràng hơn. Nếu các giá trị trong một phạm vi ô được so sánh với nhau, thì điều này sẽ dễ dàng hơn với định dạng có điều kiện này.

Định dạng ô bằng thanh dữ liệu

Thanh dữ liệu giúp so sánh các ô với nhau dễ dàng hơn. Giá trị trong ô được biểu thị bằng độ dài của thanh. Giá trị càng cao thì thanh dữ liệu càng dài, giá trị càng thấp thì thanh dữ liệu càng ngắn. Điều này cho phép xác định giá trị cao hoặc thấp một cách nhanh chóng.

Định dạng nhanh:

Đánh dấu vùng mong muốn (ô, bảng hoặc toàn bộ trang tính).

Trên tab Trang đầu, bấm vào nút Định dạng có Điều kiện.

Đi tới "thanh dữ liệu" và chọn điền.

Định dạng các ô có bezels màu

Thang màu giúp phân loại thông tin dữ liệu khác nhau dễ dàng hơn và có thể nhanh chóng so sánh nội dung của chúng với nhau. Tùy thuộc vào bóng màu, các giá trị cao hoặc thấp được hiển thị. Bạn có thể định dạng ô với thang 2 hoặc 3 màu.

Thang 2 màu

Định dạng nhanh:

Chọn ô, bảng hoặc toàn bộ trang tính.

Trên tab Trang đầu, bấm Định dạng có Điều kiện.

Đi tới "thang màu" và nhấp vào định dạng thang 2 màu mong muốn. Màu trên tượng trưng cho các giá trị cao hơn, màu dưới cho các giá trị thấp hơn.

Thang 3 màu

Màu thứ ba cho phép hiển thị các giá trị trung bình - ngoài việc thể hiện màu của các giá trị cao và thấp như trong thang 2 màu.

Định dạng nhanh:

Chọn ô, bảng hoặc toàn bộ trang tính.

Trên tab Trang đầu, bấm Định dạng có Điều kiện.

Đi tới "thang màu" và nhấp vào định dạng thang 3 màu mong muốn. Màu trên tượng trưng cho các giá trị cao hơn, màu dưới cho các giá trị thấp hơn.

Định dạng ô với bộ ký hiệu

Với một tập hợp các ký hiệu, dữ liệu có thể được xem và phân loại thành các danh mục riêng biệt. Một biểu tượng là viết tắt của một phạm vi giá trị nhất định. Định dạng có điều kiện trong Excel cung cấp các biểu tượng màu khác nhau: mũi tên, dấu chấm, dấu thập, dấu chấm than và nhiều hơn nữa.

Định dạng nhanh:

Chọn ô, bảng hoặc toàn bộ trang tính.

Trên tab Trang đầu, bấm Định dạng có Điều kiện.

Đi tới "Bộ ký hiệu" và chọn bộ ký hiệu mong muốn

Tự tạo quy tắc định dạng

Nếu bạn không thể tìm thấy tùy chọn phù hợp trong định dạng có điều kiện do Excel cung cấp, bạn có thể dễ dàng tạo các quy tắc riêng để định dạng.

Đánh dấu phạm vi ô được định dạng có điều kiện.

Đi tới "Định dạng có điều kiện" trên tab "Trang chủ".

Chọn "Quy tắc mới" và xác nhận bằng "OK".

Nếu bạn không thể tìm thấy tùy chọn bạn cần cho quy tắc định dạng của riêng mình, thì một công thức logic có thể giúp ích. Ví dụ: bạn có thể sử dụng công thức để tự động tô màu các ô Excel ngay khi có nội dung. Ngược lại, các ô trống có thể được định dạng có điều kiện.

Thí dụ: Áp dụng công thức cho phép bạn so sánh dữ liệu bên ngoài phạm vi đã chọn. Điều này thậm chí bao gồm dữ liệu nằm trên một trang tính khác trong cùng một sổ làm việc. Hoặc bạn có thể so sánh các giá trị với một kết quả được trả về bởi một hàm. Công thức phải logic và trả về "true" hoặc "false".

Bằng cách đánh dấu ô trực tiếp trên trang tính, bạn có thể nhập các tham chiếu ô trong một công thức. Đây là các tham chiếu ô tuyệt đối. Tuy nhiên, Excel cũng có thể điều chỉnh các tham chiếu ô cho mỗi ô trong vùng đã chọn. Nếu bạn muốn, hãy chọn tham chiếu ô tương đối.

Hướng dẫn từng bước để tạo quy tắc

1. Trên tab "Trang chủ", nhóm "Kiểu", đi tới mũi tên bên cạnh "Định dạng có điều kiện" và nhấp vào "Quản lý quy tắc". Hộp thoại Trình quản lý quy tắc định dạng có điều kiện xuất hiện.

2. Có thể thực hiện các hành động sau:

   • Nếu bạn muốn thêm định dạng có điều kiện, hãy chuyển đến "Quy tắc mới".
   • Thay đổi định dạng có điều kiện:
   • • Kiểm tra xem bạn đang ở trên trang tính chính xác.
     • Đầu tiên, trong Áp dụng Cho, bấm Thu gọn Hộp thoại. Sau đó chọn phạm vi ô mới và đi tới "Mở rộng hộp thoại".
     • Chọn quy tắc mong muốn, nhấp vào "Chỉnh sửa quy tắc". "Chỉnh sửa quy tắc định dạng" xuất hiện.

3. Trong "Chọn loại quy tắc", hãy nhấp vào "Sử dụng công thức để xác định các ô cần định dạng".

   • Trong "Chỉnh sửa mô tả quy tắc", hãy nhập công thức vào "Định dạng giá trị mà quy tắc này đúng". Công thức bắt đầu bằng (=) và phải trả về “true” (1) hoặc “false” (0).
   • Bấm Định dạng để hiển thị hộp thoại Định dạng Ô.
   • Bây giờ chọn số, phông chữ, định dạng điền, v.v. sẽ được sử dụng nếu giá trị ô là đúng và xác nhận với "OK". Các định dạng đã chọn được hiển thị trong "Xem trước".

Ví dụ: công thức COUNTIF trả về các giá trị lôgic. Nếu công thức này được sử dụng, các giá trị trùng lặp có thể được xác định hoặc nội dung xuất hiện trong một tần suất nhất định có thể được đánh dấu bằng màu. Bằng cách này, các vật phẩm độc đáo cũng có thể được tìm thấy và tô màu. Các công thức khác có thể được sử dụng để định dạng có điều kiện là

• REST: để các màu đường xen kẽ có thể được đặt tự động
• SẮC KÝ: Với công thức này, dữ liệu bạn đang tìm kiếm có thể được đánh dấu trong bảng Excel tương ứng với một giá trị được chỉ định
• ISERROR: tự động đánh dấu các ô trống trong bảng Excel hoặc làm cho các giá trị lỗi biến mất khỏi sổ làm việc
• NOT và ISNUMBER: Định dạng tất cả các ô Excel không chứa số
• MAX: giá trị lớn nhất của ô được đánh dấu trong bảng
• MIN: cho phép định dạng có điều kiện của giá trị nhỏ nhất trong danh sách Excel để có thể nhìn thấy giá trị nhỏ nhất trong nháy mắt
• TRÁI: tùy thuộc vào một chuỗi ký tự ở đầu, các ô Excel được tự động định dạng và có màu văn bản thống nhất
• LARGE và LEFT: cho phép tô màu một ô có nội dung mới
• QUYỀN: Các ô có thể được định dạng dựa trên ký tự cuối cùng của chuỗi
• HÔM NAY: Với tính năng này, các cuộc hẹn có thể được định dạng bằng màu bằng phương pháp đèn giao thông hoặc các giá trị ngày có thể được đánh dấu sẽ sớm được cập nhật
• THÁNG hoặc NĂM: cho phép bạn chia danh sách Excel thành tháng hoặc năm; Với YEAR, các giá trị ngày của năm hiện tại cũng có thể được đánh dấu và các giá trị hàng năm có thể được đánh dấu
• HOẶC và TUẦN: làm nổi bật các giá trị theo ngày trong tuần bằng màu

Ví dụ về cách tạo quy tắc cho kiểm tra VÀ và HOẶC:

Đối với ví dụ này, hai giá trị được sử dụng để định dạng có điều kiện. Nếu quy tắc đầu tiên không áp dụng, quy tắc thứ hai sẽ tự động áp dụng.

Quy tắc đầu tiên: Người mua nhà muốn kiểm tra xem liệu việc mua bất động sản theo kế hoạch có phù hợp với ngân sách của mình hay không. Anh ta có thể mang đến 80.000 € trong số tiền của chính mình dưới dạng trả trước và trả khoản vay hàng tháng với số tiền tối đa là 1.200 €. Nếu cả khoản trả trước và khoản vay trả góp đều đáp ứng các yêu cầu này, các ô tương ứng được đánh dấu màu xanh lục.

Quy tắc thứ hai: Nếu khoản trả trước hoặc khoản trả góp không đáp ứng yêu cầu, các ô tương ứng được đánh dấu màu đỏ.

Công thức để tạo quy tắc đầu tiên sử dụng màu xanh lá cây như sau:

= VÀ (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Công thức để tạo quy tắc thứ hai sử dụng màu đỏ như sau:

= HOẶC (IF ($ B $ 4 = 1200,1))

Chuyển và xóa định dạng có điều kiện

Định dạng có điều kiện có thể được sao chép và áp dụng cho dữ liệu khác hoặc dữ liệu mới trong trang tính hoặc xóa lại.

Chuyển định dạng có điều kiện

Để sao chép định dạng có điều kiện và dán nó vào các ô hoặc phạm vi ô khác, hãy sử dụng nút "Chuyển Định dạng".

Chọn các ô có định dạng có điều kiện mà bạn muốn sao chép và dán vào các ô khác và nhấp vào chúng.

Bây giờ đi tới "Bắt đầu" và sau đó "Định dạng chuyển". Con trỏ xuất hiện dưới dạng một bàn chải.

Bạn có thể thêm định dạng có điều kiện vào một ô hoặc dải ô bằng cách di chuyển bàn chải qua ô đó.

Với ESC, bạn kết thúc việc sử dụng bàn chải.

Bạn vẫn có thể sử dụng bút vẽ để chèn định dạng có điều kiện bằng cách nhấp đúp vào "Chuyển định dạng".

Ngoài ra, định dạng có điều kiện có thể được sao chép bằng các phím tắt và dán vào một ô Excel khác.

Xóa định dạng có điều kiện

Sự khác biệt được thực hiện giữa quá trình xóa trên trang tính và quá trình xóa trong một phạm vi ô.

Xóa trên trang tính hoặc trong toàn bộ trang tính

Đi tới "Định dạng có điều kiện" trên tab "Trang chủ".

Sau đó nhấp vào "xóa quy tắc"

Sau đó nhấp vào "Xóa quy tắc trong toàn bộ trang tính".

Ngoài ra, bạn có thể xóa định dạng có điều kiện bằng cách sử dụng hộp thoại "Chọn Nội dung".

Xóa trong phạm vi ô

Đánh dấu các ô chứa định dạng có điều kiện mà bạn muốn.

Nhấp vào nút "Kính lúp phân tích đọc" (nút nằm ở phía dưới cùng bên phải bên cạnh dữ liệu đã chọn).

Đi tới "Xóa định dạng".

Tìm và xóa các định dạng có điều kiện giống nhau trong toàn bộ trang tính

Bấm vào ô có định dạng có điều kiện thích hợp.

Trên tab "Trang chủ", nhấp vào mũi tên bên cạnh "Tìm và Chọn", sau đó nhấp vào "Chọn Nội dung".

Nhấp vào "Định dạng có điều kiện".

Trong "Xác minh dữ liệu", chuyển đến "Giống nhau" (điều này sẽ chọn tất cả các ô có cùng định dạng có điều kiện).

Trên tab "Bắt đầu", chuyển đến "Định dạng có điều kiện", sau đó "Xóa quy tắc" và "Xóa quy tắc trong các ô đã chọn".

Định dạng có điều kiện trong báo cáo PivotTable

Với báo cáo PivotTable, người dùng có thể nhanh chóng tóm tắt một lượng lớn dữ liệu. Phạm vi ô, bảng tính Excel hoặc báo cáo PivotTable - định dạng có điều kiện về cơ bản hoạt động giống nhau ở mọi nơi. Chỉ trong báo cáo PivotTable mới có một số tính năng đặc biệt mà bạn nên biết:

• Một số định dạng có điều kiện không hoạt động trên các trường trong phạm vi giá trị của báo cáo PivotTable.
• Nếu bạn thay đổi bố cục của báo cáo PivotTable theo bất kỳ cách nào, định dạng có điều kiện sẽ vẫn tồn tại cho đến khi các trường trong dữ liệu cơ bản bị xóa.
• Định dạng có điều kiện cho các trường trong phạm vi giá trị có thể dựa trên phân cấp dữ liệu. Phạm vi của định dạng có điều kiện này được xác định bởi cấp thấp hơn hoặc cao hơn tiếp theo của hệ thống phân cấp trong các hàng cho một hoặc nhiều cột hoặc trong các cột cho một hoặc nhiều hàng.
• Phạm vi định dạng có điều kiện cho các trường trong phạm vi giá trị có thể được xác định bằng ba phương pháp:

• • sự lựa chọn
  • Trường tương ứng
  • Trường giá trị

Phương pháp mặc định là lựa chọn.

Định nghĩa khu vực theo lựa chọn

Đi tới "Áp dụng quy tắc định dạng cho".

Bấm vào Hộp thoại Quy tắc Định dạng Mới hoặc Hộp thoại Chỉnh sửa Quy tắc Định dạng.

Phương pháp này phù hợp với một loạt trường liền nhau trong phạm vi giá trị, ví dụ: B. dữ liệu khảo sát cho một vùng, cũng như cho một chuỗi trường không liền nhau trong phạm vi giá trị, chẳng hạn như dữ liệu khảo sát cho các vùng khác nhau ở các cấp độ khác nhau của hệ thống phân cấp dữ liệu.

Định nghĩa khu vực thông qua một trường tương ứng

Phương pháp này hoạt động nếu bạn có

• muốn tránh phải chọn nhiều mục không liên quan
• muốn định dạng có điều kiện một số trường trong phạm vi giá trị cho cấp phân cấp dữ liệu
• Muốn loại trừ kết quả từng phần

Định nghĩa vùng thông qua trường giá trị

Phương pháp này hoạt động nếu bạn có

• muốn tránh phải chọn nhiều mục không liên quan
• muốn định dạng có điều kiện một số trường trong phạm vi giá trị cho tất cả các cấp phân cấp
• Muốn bao gồm tổng và kết quả từng phần.

Kết luận: Định dạng có điều kiện trong Excel giúp theo dõi dữ liệu dễ dàng hơn

Nếu bạn muốn tô sáng các giá trị dữ liệu nhất định trong bảng Excel của mình bằng màu, thì định dạng có điều kiện trong Excel là lý tưởng. Bạn có hai tùy chọn để áp dụng định dạng có điều kiện. Bạn sử dụng các quy tắc do Excel cung cấp để tô màu nội dung văn bản, giá trị số hoặc ngày tháng. Hoặc bạn có thể tự xác định quy tắc với các điều kiện khác nhau mà theo đó các giá trị liên quan sẽ được đánh dấu. Bạn có thể dễ dàng và hiệu quả so sánh dữ liệu với nhau bằng cách sử dụng thanh dữ liệu, thanh màu và bộ ký hiệu. Nếu các tùy chọn thiết kế không còn cần thiết, bạn có thể xóa chúng bằng một vài cú nhấp chuột.

Các bài viết khác về định dạng có điều kiện

• Cách tự động tạo mũi tên trong danh sách Excel
• Hiển thị các giá trị thời gian âm trong bảng Excel
• Tô màu phần trăm âm màu đỏ
• Nhận biết phần trăm âm trong nháy mắt
• Định dạng số với định dạng tùy chỉnh về màu sắc
• Định dạng các ô Excel không có giá trị được chỉ định
• Tự động lưu trữ các ô với các định dạng tiền tệ tương ứng
• Đánh dấu các ô bằng màu bằng cách sử dụng một hàm macro
• Sử dụng định dạng có điều kiện và đánh dấu dữ liệu lớn hơn hoặc bằng ô lớn thứ ba
• Giá trị sự thật đảo ngược