Khi làm việc với subtotal trong Excel bằng lệnh Data ➝ Outline ➝ Subtotal, các dòng subtotal rất khó phân biệt với các số liệu khi có nhiều cột dữ liệu. Cụ thể là, các cột subtotal xuất hiện bên phải tập dữ liệu trong khi tiêu đề của nó thường lại nằm ở cột đầu tiên và các subtotal lại không được định dạng in đậm như các tiêu đề nên gây khó khăn cho việc đọc các kết quả. Trong phần này sẽ hướng dẫn bạn các cách sử dụng định dạng theo điều kiện để định dạng các subtotal sao cho dễ phân biệt và dễ đọc hơn.
Trước tiên, chúng ta cần có tập số liệu để thực hành. Các bạn nhập vào các số liệu như hình sau:
Tiếp theo là các bạn hãy tính subtotal cho từng Region bằng cách đặt ô hiện hành vào dùng số liệu và vào Data ➝ Outline ➝ Subtotal (E2003: Data ➝ Subtotals). Hộp thoại Subtotal xuất hiện, bạn thiết lập các tuỳ chọn như hình sau và nhấn nút OK.
Trong hình trên, bạn thấy rằng các tiêu đề subtotal của từng Region tại cột A được in đậm nhưng các giá trị subtotal bên cột B thì không. Đối với tập số liệu trên thì cũng không quá khó để đọc các kết quả, tuy nhiên chúng ta sẽ khó nhận biết ngay các subtotal khi có nhiều cột số liệu hơn.
Chúng ta sẽ dùng định dạng theo điều kiện để định các các giá trị subtotal in đậm một cách tự động và bạn phải thực hiện điều này trước khi dùng lệnh subtotal. Do vậy nếu đã thực hiện lệnh subtotal thì bạn hãy xoá bỏ nó theo cách sau: chọn ô hiện hành trong vùng kết quả subtotal và vào Data ➝ Outline ➝ Subtotal ➝ chọn Remove All.
Sau đó chọn vùng A1:B16, lưu ý để ô hiện hành tại tại ô A1, sau đó vào Home ➝ Styles ➝ Conditional Formatting ➝ New Rules… ➝ Use a formula to determine which cells to format (E2003: Format ➝ Conditional Formatting…➝ Formula Is) và nhập vào công thức sau tại hộp “Format values where this formula is true”:
=Right($A1,5)=”Total”
Lưu ý phải cố định cột A (thêm $ vào trước A) và dùng tham chiếu tương đối cho dòng. Nhấn nút Format… ➝ vào hộp Format Cells ➝ chọn ngăn Font ➝ chọn Bold tại Font Style và chọn màu đỏ tại Color ➝ nhấn OK ➝ nhấn tiếp OK để trở về bảng tính.
Tiếp theo, bạn chọn một ô nào đó trong vùng số liệu và vào Data ➝ Outline ➝ Subtotal ➝ để các tuỳ chọn mặc định ➝ OK. Kết quả như hình sau:
Do bạn chọn ô A1 là ô mặc định và sau khi áp dụng định dạng theo điều kiện, Excel sẽ thay đổi địa chỉ tham chiếu trong công thức định dạng trên cho các ô trong vùng chọn. Ví dụ như tại ô A2 và B2 sẽ có công thức định dạng theo điều kiện là =Right($A2,5)=”Total” và các ô A3, B3 sẽ là =Right($A3,5)=”Total”. Khi công thức trong ô nào trả về True thì ô đó sẽ được áp dụng định dạng theo thiết lập trong lệnh định dạng theo điều kiện.
Cải tiến định dạng cho subtotal
Các định dạng theo hướng dẫn trên sẽ như nhau cho các dòng có tính subtotal, do vậy cũng sẽ khó phân biệt dòng Grand Total với các dòng subtotal khác. Phần này, chúng ta cũng sẽ dùng định dạng theo điều kiện để làm cho chúng có định dạng khác nhau.
Trước tiên, bạn phải xoá định dạng theo điều kiện ở phần trước bằng cách đặt ô hiện hành trong vùng số liệu rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rules ➝ chọn Rule và nhấn nút Delete Rule.
Bạn phải xoá vùng kết quả Subtotal tương tự như hướng dẫn ở phần trước. Sau đó, bạn chọn vùng A1:B16, nhớ chọn ô A1 là ô hiện hành rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rule ➝ New Rule ➝“Use a formula to determine which cells to format” (E2003, Format ➝ Conditional Formatting… ➝ Formula Is) ➝ nhập công thức sau tại hộp “Format values where this formula is true”
=$A1=”Grand Total”
Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold tại Font Style ➝ chọn màu xanh tại Color ➝ nhấn OK. Tiếp tục, bạn nhấn nút New Rule (E2003: nhấn Add) ➝ chọn “Use a formula to determine which cells to format” ➝nhập công thức sau tại hộp “Format values where this formula is true”:
=Right($A1,5)=”Total”
Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold Italic tại Font Style ➝ chọn Single tại Underline ➝ nhấn OK ➝ OK. Nhấp tiếp OK để trở lại màn hình bảng tính.
Trong Excel 2007, Rule thêm vào trước sẽ nằm dưới cùng. Do vậy bạn chọn Rule tạo đầu tiên =$A1=”Grand Total” và nhấn Move Up để đưa nó lên đầu tiên trong danh sách các Rule.
Cuối cùng, bạn vào Data ➝ Outline ➝ chọn Subtotal ➝ nhấn OK và xem kết quả như hình sau:
