Trong Excel, việc áp dụng định dạng trực tiếp cho các ô (ví dụ: đổi màu nền, kiểu chữ) là một cách hiệu quả để 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òn được gọi là “mảng tràn” (spilled array) – việc áp dụng đị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 là do định dạng trực tiếp gắn liền với các ô cụ thể, chứ không phải với dữ liệu mà chúng chứa.
Bài viết này, được biên soạn bởi các chuyên gia của thuthuatvitinh.com, sẽ hướng dẫn bạn cách vượt qua hạn chế này bằng cách sử dụng Định dạng có Điều kiện (Conditional Formatting) dựa trên công thức. Phương pháp này đảm bảo rằng định dạng của bạn sẽ tự động điều chỉnh theo dữ liệu, mang lại sự linh hoạt và chuyên nghiệp cho bảng tính của bạn, đồng thời giúp bạn tối ưu hóa quy trình làm việc trên Excel.
Tại Sao Định Dạng Trực Tiếp Mảng Tràn Gây Rắc Rối?
Mảng tràn là kết quả của các hàm động trong Excel (ví dụ: FILTER, SORT, UNIQUE, PIVOTBY) khi chúng trả về nhiều giá trị vào các ô liền kề. Giả sử 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 tại các khu vực khác nhau trong bốn năm.
Bảng tính Excel hiển thị kết quả mảng tràn từ công thức PIVOTBY, chưa được định dạng.
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 trong bảng Excel được chọn và áp dụng định dạng trực tiếp màu xanh lá nhạt.
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 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 có thể gây ra sự nhầm lẫn lớn, như bạn có thể thấy trong ảnh chụp màn hình dưới đây, nơi 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.
Bảng Excel với mảng tràn PIVOTBY bị lỗi định dạng trực tiếp sau khi dữ liệu thay đổi.
Giải Pháp: Sử Dụng Định Dạng Có Điều Kiện (Conditional Formatting)
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ị hoặc các điều kiện cụ thể mà chúng đáp ứng. Khi dữ liệu thay đổi, Excel sẽ tự động kiểm tra lại các điều kiện và cập nhật định dạng, đảm bảo tính nhất quán và chính xác cho bảng tính của bạn.
Để bắt đầu, hãy chọn tất cả dữ liệu của bạn – cộng thêm một số hàng trống ở phía dưới để dự phòng cho trường hợp dữ liệu mở rộng. Sau đó, trên tab Home của thanh ribbon, nhấp vào Conditional Formatting > Manage Rules (Quản lý Quy tắc).
Chọn dữ liệu và mở tùy chọn "Quản lý Quy tắc" trong mục Định dạng có Điều kiện (Conditional Formatting) trên Excel.
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 nút New Rule (Quy tắc Mới).
Nút "Quy tắc Mới" (New Rule) được chọn trong hộp thoại Quản lý Quy tắc Định dạng có Điều kiện của Excel.
Đối với mỗi quy tắc định dạng mà bạn sắp tạo để định dạng mảng tràn, bạn sẽ cần sử dụng một công thức. Vì vậy, trong khu vực Select A Rule Type (Chọn Loại 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).
Chọn tùy chọn "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.
Hướng Dẫn Định Dạng Các Phần Cụ Thể Của Mảng Tràn
Bây giờ chúng ta sẽ đi sâu vào việc tạo các quy tắc cụ thể để định dạng từng phần của mảng tràn, đảm bảo tính linh hoạt và chính xác khi dữ liệu thay đổi.
Định Dạng Hàng Tiêu Đề
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 ô này có nền màu xám để dễ dàng nhận diện.
Để đạ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 độc đáo 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)Lưu ý rằng hàm ISTEXT sẽ coi các ô trống và các ô chứa văn bản là một giá trị văn bản. Do đó, quy tắc định dạng có điều kiện sẽ coi các ô từ G1 đến G3 là chứa văn bản, trong khi các ô còn lại trong cột G chứa các 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ẽ khóa đị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. Khi bạn đã nhập tham chiếu ô, thay vì gõ dấu đô la thủ công, bạn có thể nhấn phím F4 để chuyển đổi giữa các loại tham chiếu (tuyệt đối: $G$1, hỗn hợp: $G1 hoặc G$1, và tương đối: G1).
Cuối cùng, vì bạn đã chọn dữ liệu ban đầu trong các cột từ A đến G, định dạng có điều kiện sẽ được áp dụng cho toàn bộ hàng khi điều kiện được đáp ứng.
Bây giờ, 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 vào “OK” trong 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 Ô" (Format Cells) trong Excel, đang chọn màu nền xám cho hàng tiêu đề.
Khi bạn nhấp vào “Apply” (Áp dụng) 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ảng tràn dữ liệu trong Excel đã được tô màu xám sau khi áp dụng quy tắc định dạng.
Định Dạng Hàng Tổng Phụ
Tiếp theo, bạn muốn định dạng các hàng tổng phụ để chúng có màu nền xanh lá nhạt, giúp phân biệt rõ ràng hơn.
Một lần nữa, hãy cẩn thận xem xét dữ liệu để tìm ra những điều kiện 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 chứa từ “Grand Total”.
Với hộp thoại Conditional Formatting Rules Manager vẫn đang mở, nhấp vào “New Rule” (Quy tắc Mới), 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
ANDcho 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 có 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”.
Cũng 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ờ, nhấp vào “Format” để chọn màu nền xanh lá nhạt, và sau khi đóng 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á nhạt.
Mảng tràn trong Excel với hàng tiêu đề màu xám và hàng tổng phụ màu xanh lá nhạt.
Định Dạng Hàng Tổng Cộng
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 để tạo điểm nhấn cuối cùng cho dữ liệu.
Vì hàng tổng cộng là hàng duy nhất chứa các 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 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 đã được áp dụng định dạng này.
Mảng tràn Excel đã được định dạng hoàn chỉnh: tiêu đề xám, tổng phụ xanh lá nhạt, tổng cộng xanh đậm.
Kiểm Tra Tính Linh Hoạt Của Định Dạng
Sau khi bạn đã áp dụng tất cả các quy tắc định dạng, hãy nhấp vào “Close” trong hộp thoại Conditional Formatting Rules Manager. Bây giờ, 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 như định dạng của nó cập nhật tương ứng.
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. Các hàng tiêu đề được tô 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, chứng tỏ khả năng tự động điều chỉnh tuyệt vời của Conditional Formatting dựa trên công thức.
Mảng tràn trong Excel vẫn giữ nguyên định dạng chính xác thông qua Conditional Formatting dù dữ liệu gốc thay đổi.
Quản Lý Và Chỉnh Sửa 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 định dạng mà bạn đã 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 vào Conditional Formatting > Manage Rules để mở lại hộp thoại Conditional Formatting Rules Manager. Sau đó, bạn có thể nhấp đúp vào một quy tắc để chỉnh sửa các điều kiện của nó hoặc thay đổi định dạng áp dụng.
Kết Luận
Việc định dạng mảng tràn trong Excel bằng Định dạng có Điều kiện dựa trên công thức là một kỹ thuật mạnh mẽ và cần thiết cho bất kỳ ai làm việc thường xuyên với dữ liệu động. Thay vì gặp phải những lỗi định dạng khó chịu khi dữ liệu thay đổi, phương pháp này cho phép bạn tạo ra các bảng tính linh hoạt, chuyên nghiệp và luôn được định dạng chính xác. Áp dụng thủ thuật này không chỉ giúp bảng tính của bạn trở nên trực quan hơn mà còn tiết kiệm thời gian đáng kể trong việc quản lý và cập nhật dữ liệu. Hãy thử ngay để nâng cao kỹ năng Excel của bạn!
Bạn có muốn khám phá thêm các thủ thuật và hướng dẫn chuyên sâu về Excel và các công nghệ khác? Hãy theo dõi thuthuatvitinh.com để không bỏ lỡ những bài viết hữu ích khác nhé!
