- Tham gia
- 16/3/07
- Bài viết
- 2,104
- Được thích
- 19,156
- Giới tính
- Nam
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.
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:
Một số bài viết có liên quan:
1/ Chiêu 35: Tự tạo danh sách để fill
2/ Chiêu 33: Tạo các định dạng số cho riêng bạn
3/ Chiêu 32: Thao tác trên dữ liệu với Advanced Filter
4/ Chiêu thứ 31: Sắp xếp ngẫu nhiên
5/ Chiêu thứ 29: Tùy biến chú thích của ô bảng tính
6/ Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA
7/ Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực
8/ Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
9/ Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác
10/ Chiêu số 24: Dùng nhiều List cho 1 Combobox
http://www.giaiphapexcel.com/vbb/content.php?351
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:
Một số bài viết có liên quan:
1/ Chiêu 35: Tự tạo danh sách để fill
2/ Chiêu 33: Tạo các định dạng số cho riêng bạn
3/ Chiêu 32: Thao tác trên dữ liệu với Advanced Filter
4/ Chiêu thứ 31: Sắp xếp ngẫu nhiên
5/ Chiêu thứ 29: Tùy biến chú thích của ô bảng tính
6/ Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA
7/ Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực
8/ Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
9/ Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác
10/ Chiêu số 24: Dùng nhiều List cho 1 Combobox
http://www.giaiphapexcel.com/vbb/content.php?351
Upvote
0
