VietNetwork.Vn

Không áp dụng định dạng trực tiếp.

    Áp dụng định dạng trực tiếp vào mảng bị tràn trong Excel có thể gây ra sự cố nếu dữ liệu thay đổi hình dạng hoặc kích thước.
    Các quy tắc định dạng có điều kiện dựa trên công thức cho phép điều chỉnh định dạng tự động khi các tham số dữ liệu thay đổi.
    Đặt ký hiệu đô la trước tham chiếu cột cho phép bạn áp dụng các quy tắc cho tất cả các hàng trong dữ liệu.


Trong Excel, bạn có thể áp dụng định dạng trực tiếp cho giá trị hoặc nền của ô để giúp bảng tính dễ đọc hơn. Tuy nhiên, khi công thức Excel trả về một tập hợp các giá trị—được gọi là mảng tràn—áp dụng định dạng trực tiếp sẽ gây ra sự cố nếu kích thước hoặc hình dạng của dữ liệu thay đổi.

Giả sử bạn có bảng tính chứa kết quả của công thức PIVOTBY, hiển thị số lượng người xem theo từng môn thể thao ở nhiều khu vực khác nhau trong bốn năm.

Để theo dõi khi bạn đọc, hãy tải xuống một bản sao của sổ làm việc Excel này. Sau khi nhấp vào liên kết, bạn sẽ thấy nút tải xuống ở góc trên bên phải màn hình.


Vì hàm PIVOTBY không bao gồm đối số cho phép bạn định dạng kết quả nên việc phân biệt giữa các hàng tiêu đề, hàng dữ liệu, hàng tổng phụ và hàng tổng cộng rất khó khăn.

Ở giai đoạn này, bạn có thể muốn áp dụng định dạng trực tiếp—thông qua nhóm Phông chữ của tab Trang chủ trên ruy-băng—để phân tách trực quan các loại hàng khác nhau trong dữ liệu.


Tuy nhiên, nếu sau này bạn sửa đổi các tham số trong công thức PIVOTBY hoặc nếu kết quả tăng hoặc giảm do dữ liệu gốc thay đổi, định dạng trực tiếp bạn áp dụng sẽ không điều chỉnh theo. Điều này là do định dạng trực tiếp trong Excel được liên kết với các ô, chứ không phải dữ liệu chúng chứa. Xem sự nhầm lẫn mà điều này có thể gây ra trong ảnh chụp màn hình bên dưới, trong đó dữ liệu đã giảm, nhưng định dạng trực tiếp vẫn được áp dụng cho cùng một hàng.


Thay vào đó, bạn nên sử dụng định dạng có điều kiện, cho phép bạn định dạng các ô và hàng theo giá trị của chúng.

Chọn toàn bộ dữ liệu—cộng thêm một số hàng ở phía dưới để dữ liệu có thể phát triển theo chiều dọc—và trong tab Trang chủ trên ruy-băng, nhấp vào Định dạng có điều kiện > Quản lý quy tắc.


Tiếp theo, trong hộp thoại Trình quản lý quy tắc định dạng có điều kiện, hãy nhấp vào "Quy tắc mới".


Đối với mỗi quy tắc bạn sẽ tạo để định dạng mảng tràn của mình, bạn sẽ cần sử dụng một công thức. Vì vậy, trong vùng Chọn loại quy tắc của hộp thoại Quy tắc định dạng mới, hãy chọn tùy chọn cuối cùng, "Sử dụng công thức để xác định ô nào cần định dạng".


Quy tắc đầu tiên bạn muốn tạo liên quan đến các hàng tiêu đề. Cụ thể, bạn muốn các ô đó có nền màu xám.

Để thực hiện điều này, hãy dành chút thời gian để xác định điều gì làm cho các hàng tiêu đề trở nên độc đáo so với các hàng khác trong bảng của bạn. Trong trường hợp này, các hàng tiêu đề là những hàng duy nhất không chứa số trong cột G. Vì vậy, trong trường công thức, hãy nhập:

=ISTEXT($G1)
Vì hàm ISTEXT coi các ô trống và ô chứa văn bản là giá trị văn bản nên quy tắc định dạng có điều kiện sẽ coi các ô G1 đến G3 là chứa văn bản, trong khi các ô còn lại trong cột G chứa giá trị số.

Điều quan trọng là việc thêm dấu đô la ($) trước tham chiếu cột—còn được gọi là tham chiếu hỗn hợp—sẽ sửa định dạng có điều kiện cho cột này, đồng thời cho phép Excel áp dụng quy tắc cho các hàng còn lại.

Sau khi nhập tham chiếu ô, thay vì nhập dấu đô la theo cách thủ công, hãy nhấn F4 để chuyển đổi giữa các tham chiếu tuyệt đối (ví dụ: $G$1), tham chiếu hỗn hợp (ví dụ: $G1 hoặc G$1) và tham chiếu tương đối (ví dụ: G1).

Cuối cùng, vì ban đầu bạn đã chọn dữ liệu trong các cột từ A đến G nên định dạng có điều kiện sẽ áp dụng cho toàn bộ hàng đáp ứng điều kiện.

Bây giờ, hãy nhấp vào "Định dạng" để chọn định dạng cho các hàng tiêu đề. Trong trường hợp này, bạn muốn chúng có màu xám. Sau đó, nhấp vào "OK" trong hộp thoại Định dạng ô và Chỉnh sửa quy tắc định dạng.


Khi bạn nhấp vào "Áp dụng" trong hộp thoại Trình quản lý quy tắc định dạng có điều kiện, bạn sẽ thấy rằng chỉ những hàng có cột G chứa ô trống hoặc văn bản—nói cách khác là các hàng tiêu đề—mới được tô màu xám.


Tiếp theo, bạn muốn định dạng các hàng tổng phụ để chúng có màu xanh lục nhạt.

Một lần nữa, hãy xem xét cẩn thận dữ liệu để xem bạn có thể sử dụng điều kiện nào để áp dụng định dạng chỉ cho các hàng này. Trong trường hợp này, các hàng tổng phụ chứa văn bản trong cột A nhưng không có gì trong cột B. Ngoài ra, vì hàng tổng cũng đáp ứng các tiêu chí này, bạn cần loại trừ bất kỳ ô nào trong cột A chứa các từ "Tổng cộng".

Với hộp thoại Conditional Formatting Rules Manager vẫn mở, hãy nhấp vào "New Rule" và chọn tùy chọn cho phép bạn sử dụng công thức để định dạng các ô. Lần này, trong trường công thức, hãy nhập:

=AND($A1<>"",$B1="",$A1<>"Grand Total")
Trong đó:

    Hàm AND cho phép bạn chỉ định nhiều hơn một điều kiện trong dấu ngoặc đơn,
    $A1 < >"" yêu cầu Excel tìm kiếm các ô trong cột A không chứa (<>) ô trống (""),
    $B1="" yêu cầu Excel tìm kiếm các ô trong cột B có chứa (=) ô trống ("") và
    $A1 < >"Tổng cộng" yêu cầu Excel loại trừ (<>) bất kỳ ô nào trong cột A có chứa văn bản "Tổng cộng".

Tương tự như quy tắc trước, hãy nhớ chèn dấu $ trước tham chiếu cột để Excel có thể áp dụng cùng một quy tắc cho tất cả các hàng đã chọn.

Bây giờ, hãy nhấp vào "Định dạng" để chọn màu tô xanh lục nhạt và sau khi đóng hộp thoại Định dạng ô và Chỉnh sửa quy tắc định dạng, hãy nhấp vào "Áp dụng" để xem các hàng tổng phụ được tô màu xanh lục nhạt.


Cuối cùng, bạn muốn các ô trong hàng tổng được tô màu xanh lá cây đậm hơn.

Vì hàng tổng cộng là hàng duy nhất chứa các từ "Tổng cộng" trong cột A, đây là tiêu chí bạn có thể sử dụng cho định dạng có điều kiện. Trong hộp thoại Conditional Formatting Rules Manager, nhấp vào "New Rule" và chọn tùy chọn cuối cùng trong danh sách Select A Rule Type. Bây giờ, trong trường công thức, hãy nhập:

=$A1="Grand Total"
Tiếp theo, nhấp vào "Định dạng" và chọn màu tô xanh đậm để áp dụng cho các ô phù hợp với tiêu chí này. Bây giờ, khi bạn đóng hộp thoại Định dạng ô và Chỉnh sửa quy tắc định dạng và nhấp vào "Áp dụng" trong hộp thoại Trình quản lý quy tắc định dạng có điều kiện, bạn sẽ thấy hàng tổng của mình đã áp dụng định dạng này.


Bây giờ bạn đã áp dụng tất cả các quy tắc của mình, hãy nhấp vào "Đóng" trong hộp thoại Conditional Formatting Rules Manager. Sau đó, điều chỉnh một số dữ liệu của bạn trong bảng gốc và xem kết quả tràn và định dạng của nó được cập nhật cho phù hợp.

Trong ví dụ này, mặc dù tôi đã xóa 12 hàng khỏi bảng dữ liệu gốc, kết quả PIVOTBY bị đổ vẫn được định dạng đúng, với các hàng tiêu đề có màu xám, các hàng tổng phụ có màu xanh lá cây nhạt và hàng tổng có màu xanh lá cây đậm.


Nếu bạn cần thực hiện bất kỳ thay đổi nào đối với các quy tắc đã tạo và áp dụng, chỉ cần chọn bất kỳ ô nào trong dữ liệu và nhấp vào Định dạng có điều kiện > Quản lý quy tắc để khởi chạy lại trình quản lý Quy tắc định dạng có điều kiện. Sau đó, nhấp đúp vào một quy tắc để thay đổi các điều kiện của quy tắc đó.