Đánh giá lequocthai.com:
Microsoft Excel định nghĩa Subtotal Function (Hàm Tổng phụ) là “trả về tổng phụ trong một danh sách hoặc cơ sở dữ liệu”.
Công thức Subtotal nhận hai đối số:
- Operation Code – mã thao tác
- Range of Cells – dải ô
Trong phần dưới đây, chúng ta sẽ xem cú pháp của hàm Subtotal.
Cú pháp của hàm Subtotal
Hàm Subtotal có thể viết dưới dạng:
`=SUBTOTAL(Operation_Code, Range1)`
Trong đó Operation_Code chỉ loại phép toán mà bạn muốn thực hiện trên dải ô được chỉ định.
Bảng mã thao tác (Operation Code)
| Mã | Tên thao tác | Mô tả |
|---|---|---|
| 1 | AVERAGE | Tính trung bình của dải ô đã cho |
| 2 | COUNT | Đếm số ô chứa dữ liệu số trong dải ô |
| 3 | COUNTA | Đếm số ô không rỗng trong dải ô |
| 4 | MAX | Tìm giá trị lớn nhất trong dải ô |
| 5 | MIN | Tìm giá trị nhỏ nhất trong dải ô |
| 6 | PRODUCT | Tính tích của các ô trong dải ô |
| 7 | STDEV | Ước lượng độ lệch chuẩn mẫu trong dải ô |
| 8 | STDEVP | Tính độ lệch chuẩn dựa trên toàn bộ tổng thể |
| 9 | SUM | Tính tổng của dải ô |
| 10 | VAR | Ước lượng phương sai mẫu trong dải ô |
| 11 | VARP | Ước lượng phương sai dựa trên toàn bộ tổng thể |
Range1 biểu thị dải ô (array of cells).
Ví dụ về hàm Subtotal trong Excel
Trong ví dụ dưới đây, tôi sử dụng nhiều hàm Subtotal khác nhau.
Công thức
=SUBTOTAL(9,B2:B11)trả về SUM (mã thao tác = 9) của tất cả các phần tử trong dải `B2:B11`.
Công thức
=SUBTOTAL(1,B2:B11)trả về AVERAGE (mã thao tác = 1) của các phần tử trong cùng dải.
Tương tự,
=SUBTOTAL(7,B2:B11)trả về STDEV (mã thao tác = 7) của dải được chỉ định.
Tại sao nên dùng hàm Subtotal?
Sau khi đọc về hàm Subtotal, bạn có thể thắc mắc: “Tại sao tôi phải dùng Subtotal khi đã có các hàm riêng biệt như SUM, AVERAGE, …?”.
Câu trả lời rất đơn giản: Subtotal cung cấp hai khả năng quan trọng mà các hàm truyền thống không có.
- Kết quả tự động cập nhật
Khi bạn áp dụng hàm Subtotal cho một dải ô, kết quả sẽ tự động thay đổi khi dữ liệu được sửa đổi hoặc khi bạn lọc (filter) bảng. Xem ví dụ dưới đây:
Giá trị subtotal thay đổi một cách động theo bộ lọc.
- Bỏ qua các hàng ẩn
Excel có tính năng “Hide rows” (ẩn hàng). Khi bạn ẩn một số bản ghi không liên quan, các hàm tính tổng truyền thống sẽ vẫn tính cả các hàng ẩn. Ngược lại, Subtotal cho phép bạn chỉ tính các hàng được hiển thị.
Để thực hiện điều này, bạn chỉ cần dùng các mã thao tác đặc biệt bằng cách cộng 100 vào mã gốc. Ví dụ, mã trung bình bình thường là 1, thì mã 101 sẽ tính trung bình chỉ trên các hàng hiển thị.
=SUBTOTAL(9,B2:B5) // tính tổng bao gồm cả hàng ẩn
=SUBTOTAL(109,B2:B5) // tính tổng chỉ các hàng hiển thị, bỏ qua hàng ẩnSử dụng hàm Subtotal từ Ribbon của Excel
Bạn cũng có thể thực hiện Subtotal thông qua giao diện Ribbon: Data → Subtotal.
Việc này tạo ra các nút collapse/expand (+/-) ở bên trái các hàng, giúp bạn xem dữ liệu mà không cần cuộn.
Các bước thực hiện:
- Chọn bảng dữ liệu của bạn, sau đó vào Data > Subtotal.
- Trong cửa sổ Subtotal, ở mục At each change in chọn cột sẽ dùng làm tiêu chí nhóm (trong ví dụ tôi dùng cột Region). Tiếp theo chọn hàm cần dùng và tích chọn cột sẽ áp dụng công thức.
- Nhấn OK, công thức sẽ được chèn tự động.
Đó là toàn bộ nội dung về Hàm Subtotal (Subtotal Function) trong Excel. Hãy chia sẻ ý tưởng của bạn về công cụ hữu ích này.
