Trong Excel, việc áp dụng định dạng trực tiếp cho các ô có thể giúp 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 sẽ 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 khi làm việc với các hàm động như PIVOTBY. Để giải quyết thách thức này và đảm bảo bảng tính luôn hiển thị chuyên nghiệp, phương pháp tối ưu là 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. Bài viết này sẽ hướng dẫn bạn cách tối ưu tự động định dạng mảng tràn trong Excel một cách hiệu quả, đảm bảo tính nhất quán và linh hoạt cho dữ liệu của bạn.
Hãy tưởng tượng bạn có một bảng tính chứa kết quả tràn từ công thức PIVOTBY, hiển thị số lượng người xem theo từng môn thể thao ở các khu vực khác nhau trong bốn năm.
Bảng tính Excel chứa kết quả tràn của công thức PIVOTBY
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. Ở 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 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 được chọn trong bảng tính Excel và một màu xanh lá cây nhạt được áp dụng thông qua định dạng trực tiếp
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. Điều này là do đị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 với dữ liệu mà chúng chứa. Bạn có thể thấy sự nhầm lẫn này trong ảnh chụp màn hình dưới đây, khi dữ liệu đã thu nhỏ, nhưng định dạng trực tiếp vẫn được áp dụng cho cùng các hàng cũ.
Bảng tính Excel chứa kết quả tràn của công thức PIVOTBY, với định dạng trực tiếp được áp dụng không chính xác cho dữ liệu
Thay vào đó, bạn nên sử dụng định dạng có điều kiện trong Excel, cho phép bạn định dạng các ô và hàng dựa trên giá trị của chúng, đảm bảo tính tự động và chính xác cho mảng tràn.
Hướng Dẫn Từng Bước Tự Động Định Dạng Mảng Tràn Bằng Conditional Formatting
Để bắt đầu, chúng ta sẽ tạo các quy tắc định dạng có điều kiện để định dạng tự động các thành phần khác nhau của mảng tràn, bao gồm hàng tiêu đề, hàng tổng phụ và hàng tổng cộng.
Bước 1: Chuẩn Bị Dữ Liệu và Mở Hộp Thoại Quản Lý Quy Tắc
Chọn toàn bộ vùng dữ liệu mà mảng tràn của bạn có thể chiếm dụng, bao gồm cả một số hàng trống phía dưới để phòng trường hợp dữ liệu tăng lên. Sau đó, trên tab Home của ribbon, nhấp vào Conditional Formatting > Manage Rules.
Dữ liệu trong Excel được chọn và tùy chọn Manage Rules của menu thả xuống Conditional Formatting được chọn
Tiếp theo, trong hộp thoại Conditional Formatting Rules Manager, nhấp vào “New Rule”.
Nút New Rule được chọn trong Excel
Đối với mỗi quy tắc bạn sẽ tạo để định dạng mảng tràn Excel, bạn cần sử dụng một công thức. Vì vậy, trong khu vực Select a Rule Type của hộp thoại New Formatting Rule, chọn tùy chọn cuối cùng, “Use a formula to determine which cells to format”.
Tùy chọn Use A Formula To Determine Which Cells To Format được chọn trong Excel
Bước 2: Tạo Quy Tắc Định Dạng Cho Hàng Tiêu Đề (Header Rows)
Quy tắc đầu tiên bạn muốn tạo là 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 xác định điểm độc đáo của các hàng tiêu đề so với các hàng khác trong bảng của bạn. Trong ví dụ 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)Hàm ISTEXT trong Excel coi các ô trống và các ô chứa văn bản là giá trị văn bản. Do đó, quy tắc định dạng có điều kiện sẽ coi các ô G1 đến G3 chứa văn bản, còn 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 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.
Tham chiếu tương đối, tuyệt đối và hỗn hợp trong Excel
Cuối cùng, vì bạn ban đầu đã 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ếu điều kiện được đáp ứng.
Bây giờ, nhấp vào “Format” để 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 tô màu xám. Sau đó, nhấp vào “OK” trong các hộp thoại Format Cells và Edit Formatting Rule.
Khi bạn nhấp vào “Apply” trong hộp thoại Conditional Formatting Rules Manager, 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 – tức là các hàng tiêu đề – được tô màu xám.
Các hàng tiêu đề của một số dữ liệu tràn trong Excel được tô màu xám sau khi nhấp nút Apply trong hộp thoại Conditional Formatting Rules Manager
Bước 3: Tạo Quy Tắc Định Dạng Cho 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á cây 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 trong cột A nhưng không có gì trong cột B. Ngoài ra, vì hàng tổng cộ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ụm từ “Grand Total”.
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 chứa ô trống (“”).$B1=""cho Excel biết tìm các ô trong cột B chứa (=) ô trống (“”).$A1<>"Grand Total"cho Excel biết loại trừ (<>) bất kỳ ô nào trong cột A chứa văn bản “Grand Total”.
Tương tự như quy tắc trước, hãy nhớ chèn dấu $ trước các tham chiếu cột để Excel áp dụng cùng một quy tắc cho tất cả các hàng được chọn.
Bây giờ, nhấp vào “Format” để chọn màu nền xanh lá cây nhạt, và sau khi đóng các hộp thoại Format Cells và Edit Formatting Rule, nhấp vào “Apply” để xem các hàng tổng phụ được tô màu xanh lá cây nhạt.
Một mảng tràn trong Excel có các hàng tiêu đề màu xám và các hàng tổng phụ màu xanh lá cây nhạt
Bước 4: Tạo Quy Tắc Định Dạng Cho 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á cây đậm hơn.
Vì hàng tổng cộng là hàng duy nhất chứa cụm từ “Grand Total” 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 “Format”, và chọn màu nền xanh lá cây đậ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 vào “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ột mảng tràn trong Excel có các hàng tiêu đề màu xám, các hàng tổng phụ màu xanh lá cây nhạt và hàng tổng cộng màu xanh lá cây đậm
Kết Quả và Lợi Ích Của Việc Định Dạng Tự Động Mảng Tràn
Giờ đây, bạn đã áp dụng tất cả các quy tắc của mình, nhấp vào “Close” 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à xem kết quả 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á cây nhạt và hàng tổng cộng màu xanh lá cây đậm. Điều này chứng tỏ hiệu quả của việc tự động định dạng dữ liệu tràn trong Excel bằng Conditional Formatting.
Một mảng tràn trong Excel chứa định dạng thông qua định dạng có điều kiện
Cách Chỉnh Sửa Các Quy Tắc Định Dạng
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 bạn đã 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 Conditional Formatting > Manage Rules để khởi chạy lại Conditional Formatting Rules Manager. Sau đó, nhấp đúp vào một quy tắc để thay đổi điều kiện của nó.
Kết Luận
Việc tối ưu tự động định dạng mảng tràn trong Excel bằng Conditional Formatting là một kỹ năng vô cùng giá trị, giúp bạn làm việc hiệu quả hơn với dữ liệu động. Thay vì tốn thời gian định dạng thủ công và lo lắng về việc dữ liệu thay đổi làm hỏng bố cục, phương pháp này đảm bảo tính chính xác, nhất quán và chuyên nghiệp cho mọi bảng tính. Áp dụng các công thức thông minh vào định dạng có điều kiện không chỉ giải quyết vấn đề định dạng mảng tràn mà còn nâng cao khả năng quản lý và trình bày dữ liệu của bạn. Hãy bắt đầu áp dụng ngay hôm nay để trải nghiệm sự khác biệt! Đừng quên theo dõi tinhoccongnghe.net để cập nhật thêm nhiều thủ thuật Excel và công nghệ hữu ích khác!
