Trong Excel, việc áp dụng định dạng trực tiếp vào các ô (như tô màu nền, đổi kiểu chữ) là cách phổ biến để làm cho bảng tính dễ đọc hơn. Tuy nhiên, khi một 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 (spilled array) – việc định dạng trực tiếp có thể gây ra nhiều vấn đề nếu kích thước hoặc hình dạng của dữ liệu thay đổi. Điều này thường xuyên xảy ra trong các bảng tính động, nơi dữ liệu được cập nhật liên tục.
Bài viết này sẽ hướng dẫn bạn cách sử dụng Định dạng có điều kiện (Conditional Formatting) kết hợp với các công thức thông minh để tự động điều chỉnh định dạng cho mảng tràn, đảm bảo bảng tính của bạn luôn chính xác và chuyên nghiệp, bất kể dữ liệu có thay đổi như thế nào. Đây là một kỹ thuật SEO on-page và tối ưu nội dung quan trọng cho người dùng Excel tại Việt Nam, giúp tăng cường trải nghiệm và tính hữu ích của bảng tính.
Tại Sao Định Dạng Trực Tiếp Lại Gây Rắc Rối Với Mảng Tràn?
Hãy tưởng tượng bạn có một bảng tính chứa kết quả mảng tràn từ một công thức PIVOTBY – một tính năng mạnh mẽ trong Excel cho phép tổng hợp dữ liệu linh hoạt. Bảng này hiển thị số lượng người xem theo từng môn thể thao, trên nhiều khu vực trong bốn năm.
Để theo dõi các bước hướng dẫn một cách thuận tiện, bạn có thể tải xuống bản sao của tệp 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.
Bảng tính Excel hiển thị kết quả mảng tràn của hàm PIVOTBY, với dữ liệu về số người xem thể thao theo vùng và năm.
Vì hàm PIVOTBY không bao gồm đối số cho phép bạn định dạng trực tiếp 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 trở nên khó khăn. Lúc này, bạn có thể có xu hướng áp dụng định dạng trực tiếp – thông qua nhóm Font trên tab Home của ribbon – để phân tách trực quan các loại hàng khác nhau trong dữ liệu.
Một hàng trong bảng tính Excel được chọn và áp dụng định dạng trực tiếp màu xanh lá nhạt, minh họa cách định dạng thủ công.
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/giảm do thay đổi trong dữ liệu gốc, định dạng trực tiếp bạn đã áp dụng sẽ không tự động điều chỉnh theo. Điều này xảy ra vì định dạng trực tiếp trong Excel được liên kết với các ô cụ thể, chứ không phải dữ liệu mà chúng chứa. Hãy xem sự nhầm lẫn có thể xảy ra trong ảnh dưới đây, khi dữ liệu đã thu hẹp nhưng định dạng trực tiếp vẫn được áp dụng cho các hàng cũ, gây sai lệch thông tin.
Bảng tính Excel với kết quả hàm PIVOTBY bị sai định dạng trực tiếp, cho thấy các hàng trống vẫn giữ màu khi dữ liệu mảng tràn co lại.
Giải Pháp: Sử Dụng Định Dạng Có Điều Kiện Với Công Thức
Thay vì định dạng trực tiếp, bạn nên sử dụng Định dạng có điều kiện (Conditional Formatting). Tính năng này cho phép bạn định dạng các ô và hàng dựa trên giá trị của chúng, giúp định dạng tự động thích ứng với sự thay đổi của dữ liệu trong mảng tràn.
Để bắt đầu, hãy chọn toàn bộ vùng dữ liệu của bạn – cộng thêm một vài hàng trống ở phía dưới để dự phòng trường hợp dữ liệu mở rộng theo chiều dọc. Sau đó, trên tab Home của ribbon, nhấp vào “Conditional Formatting” (Định dạng có điều kiện) > “Manage Rules” (Quản lý Quy tắc).
Chọn một vùng dữ liệu trong Excel, sau đó mở menu Định dạng có điều kiện và nhấp vào Quản lý Quy tắc để bắt đầu tạo quy tắc mới.
Tiếp theo, trong hộp thoại “Conditional Formatting Rules Manager” (Quản lý Quy tắc Định dạng có điều kiện), nhấp vào “New Rule” (Quy tắc Mới).
Trong hộp thoại Quản lý Quy tắc Định dạng có Điều kiện của Excel, nút "Quy tắc Mới" được chọn để tạo một quy tắc định dạng 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 cần sử dụng một công thức. Vì vậy, trong khu vực “Select A Rule Type” (Chọn Kiểu Quy tắc) của hộp thoại “New Formatting Rule” (Quy tắc Định dạng Mới), hãy chọn tùy chọn cuối cùng: “Use A Formula To Determine Which Cells To Format” (Sử dụng công thức để xác định ô cần định dạng).
Trong hộp thoại Quy tắc Định dạng Mới của Excel, tùy chọn "Sử dụng công thức để xác định ô cần định dạng" được chọn, chuẩn bị nhập công thức định dạng.
Thiết Lập Quy Tắc Định Dạng Cho Từng Loại Hàng
Chúng ta sẽ tạo ba quy tắc định dạng riêng biệt để phân loại các hàng trong mảng tràn của bạn.
Quy Tắc 1: Định Dạng Hàng Tiêu Đề (Header Rows)
Quy tắc đầu tiên bạn muốn tạo là dành cho các hàng tiêu đề. Cụ thể, bạn muốn các ô này có nền màu xám.
Để đạt được điều này, hãy dành một chút thời gian để xác định điều gì làm cho các hàng tiêu đề trở nên khác biệt 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à các ô chứa văn bản là giá trị văn bả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, với các ô còn lại trong cột G chứa giá trị số.
Quan trọng là, việc thêm ký hiệu đô la ($) trước tham chiếu cột – còn được gọi là tham chiếu hỗn hợp – sẽ cố định định dạng có điều kiện vào 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.
Mẹo: Sau khi nhập tham chiếu ô, thay vì nhập ký hiệu đô la 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ì bạn đã chọn dữ liệu trong các cột A đến G, định dạng có điều kiện sẽ áp dụng cho toàn bộ hàng nơi điều kiện được đáp ứng.
Bây giờ, hãy nhấp vào “Format” (Đị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 “OK” trong các hộp thoại “Format Cells” (Định dạng ô) và “Edit Formatting Rule” (Chỉnh sửa Quy tắc Định dạng).
Hộp thoại Định dạng Ô trong Excel, mở từ hộp thoại Chỉnh sửa Quy tắc Định dạng, với tùy chọn "Tô màu" xám được chọn cho hàng tiêu đề.
Khi bạn nhấp “Apply” (Áp dụng) trong hộp thoại “Conditional Formatting Rules Manager” (Quản lý Quy tắc Định dạng có điều kiện), bạn sẽ thấy rằng chỉ các hàng mà cột G chứa ô trống hoặc văn bản – nói cách khác, các hàng tiêu đề – được tô màu xám.
Các hàng tiêu đề của mảng tràn trong Excel được tô màu xám sau khi áp dụng quy tắc Định dạng có điều kiện, giúp dễ dàng nhận diện.
Quy Tắc 2: Định Dạng Hàng Tổng Phụ (Subtotal Rows)
Tiếp theo, bạn muốn định dạng các hàng tổng phụ để chúng có nền màu xanh lá nhạt.
Một lần nữa, hãy xem xét kỹ dữ liệu để xem những điều kiện nào bạn có thể sử dụng để á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 ở cột A nhưng không có gì ở cột B. Ngoài ra, vì hàng tổng cộng (grand total) cũng đáp ứng các tiêu chí này, bạn cần loại trừ bất kỳ ô nào ở cột A có chứa từ “Grand Total” (Tổng cộng).
Với hộp thoại “Conditional Formatting Rules Manager” vẫn đang mở, 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<>""cho Excel biết tìm các ô trong cột A không trống.$B1=""cho Excel biết tìm các ô trong cột B trống.$A1<>"Grand Total"cho Excel biết loại trừ bất kỳ ô nào trong cột A có chứa văn bản “Grand Total”.
Tương tự như quy tắc trước, hãy nhớ chèn ký hiệu $ trước các tham chiếu cột để Excel có thể áp dụng cùng một quy tắc xuống tất cả các hàng đã chọn.
Bây giờ, hãy nhấp “Format” để chọn màu nền xanh lá nhạt, và sau khi đóng các hộp thoại “Format Cells” và “Edit Formatting Rule”, nhấp “Apply” để thấy các hàng tổng phụ được tô màu xanh lá nhạt.
Mảng tràn trong Excel với các hàng tiêu đề màu xám và hàng tổng phụ màu xanh lá nhạt, minh họa quy tắc định dạng có điều kiện thứ hai.
Quy Tắc 3: Định Dạng Hàng Tổng Cộng (Grand Total Row)
Cuối cùng, bạn muốn các ô trong hàng tổng cộng được tô màu xanh lá đậm hơn.
Vì hàng tổng cộng là hàng duy nhất chứa từ “Grand Total” ở 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 “Format”, và chọn màu nền xanh lá đậm để áp dụng cho các ô khớp với tiêu chí này. Bây giờ, khi bạn đóng các hộp thoại “Format Cells” và “Edit Formatting Rule”, và nhấp “Apply” trong hộp thoại “Conditional Formatting Rules Manager”, bạn sẽ thấy rằng hàng tổng cộng của bạn đã áp dụng định dạng này.
Mảng tràn Excel được định dạng hoàn chỉnh với hàng tiêu đề xám, hàng tổng phụ xanh nhạt và hàng tổng cộng màu xanh đậm, thể hiện đầy đủ các quy tắc.
Kiểm Tra Và Chỉnh Sửa Định Dạng Tự Động
Bây giờ bạn đã áp dụng tất cả các quy tắc định dạng cần thiết, hãy nhấp “Close” (Đóng) trong hộp thoại “Conditional Formatting Rules Manager”. Sau đó, hãy thử điều chỉnh một số dữ liệu trong bảng gốc của bạn và quan sát kết quả mảng tràn cùng với định dạng của nó tự động cập nhật.
Trong ví dụ này, ngay cả khi tôi đã xóa 12 hàng khỏi bảng dữ liệu gốc, kết quả PIVOTBY tràn vẫn được định dạng chính xác, với các hàng tiêu đề màu xám, các hàng tổng phụ màu xanh lá nhạt và hàng tổng cộng màu xanh lá đậm. Điều này chứng tỏ sức mạnh của định dạng có điều kiện trong việc duy trì tính nhất quán và rõ ràng cho dữ liệu động.
Mảng tràn trong Excel vẫn giữ nguyên định dạng chính xác bằng Conditional Formatting ngay cả khi dữ liệu gốc đã bị thay đổi, chứng minh tính linh hoạt của phương pháp.
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 vùng dữ liệu, và nhấp “Conditional Formatting” (Định dạng có điều kiện) > “Manage Rules” (Quản lý Quy tắc) để mở lại trình quản lý quy tắc. Sau đó, nhấp đúp vào một quy tắc để thay đổi các điều kiện hoặc định dạng của nó.
Với kỹ thuật định dạng có điều kiện này, bạn không chỉ làm cho bảng tính Excel của mình trở nên chuyên nghiệp hơn mà còn tiết kiệm đáng kể thời gian và công sức trong việc quản lý dữ liệu động. Hãy thử áp dụng ngay hôm nay để nâng cao hiệu quả làm việc của bạn!
