Chiêu thứ 36: Làm nổi các Subtotal của Excel
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.