Khám phá chi tiết cách sử dụng hàm SUBTOTAL trong Excel từ cơ bản đến nâng cao qua bài viết hướng dẫn từ A đến Z kèm theo các ví dụ minh họa thực tế. SUBTOTAL là hàm không thể bỏ qua đối với người dùng thường xuyên làm việc với bảng tính, đặc biệt là khi cần phân tích dữ liệu một cách nhanh chóng và chính xác.
Trong Bài viết này, Việc làm 24h sẽ làm rõ SUBTOTAL là hàm gì và cách dùng hàm SUBTOTAL trong Excel sao cho hiệu quả với các hướng dẫn cụ thể để từ đó giúp bạn có góc nhìn tổng quan, để bước đầu chinh phục Excel ngay từ hôm nay!
1. Hàm SUBTOTAL là gì?
Hàm SUBTOTAL dùng để thực hiện các phép tính tổng hợp như tính tổng, trung bình, đếm trên dữ liệu đã được lọc hoặc ẩn. Điều này đặc biệt hữu ích khi bạn cần tính toán những chỉ số quan trọng như tổng doanh thu, trung bình điểm số hay đếm số lượng sản phẩm nhằm đáp ứng theo tiêu chí nhất định.
2. Cú pháp và các thành phần của hàm SUBTOTAL
Để thực hành hàm SUBTOTAL bạn cần sử dụng cú pháp: =SUBTOTAL(function_num, ref1, [ref2], …)
Trong đó các thành phần sử dụng trong hàm SUBTOTAL là:
- function_num: Một số nguyên biểu thị hàm tính toán bạn muốn sử dụng (ví dụ: 9 để tính tổng, 10 để tính trung bình).
- ref1, [ref2], …: Các vùng dữ liệu bạn muốn tính toán.
3. Các phép tính hỗ trợ trong hàm SUBTOTAL
Hàm SUBTOTAL hỗ trợ rất nhiều hàm tính toán khác nhau, từ các phép tính cơ bản như tổng, trung bình, đếm, đến những hàm phức tạp hơn như tìm giá trị lớn nhất, nhỏ nhất…
Danh sách function_num phổ biến (Tổng, Trung bình, Đếm,…).
Hàm SUBTOTAL cung cấp một loạt các hàm tính toán như SUM (tính tổng), AVERAGE (tính trung bình), COUNT (đếm), MIN (tìm giá trị nhỏ nhất), MAX (tìm giá trị lớn nhất)… Nhờ đó, bạn có thể dễ dàng thực hiện nhiều phép tính phức tạp trên dữ liệu mà không cần phải viết công thức quá dài.
Phân biệt giữa các nhóm function_num (bỏ qua hoặc bao gồm giá trị ẩn).
Để phân biệt giữa các nhóm function_num trong hàm SUBTOTAL, chúng ta có thể dựa vào các nhóm giá trị sau:
- Nhóm từ 1-11: Bao gồm cả các giá trị ẩn. Ví dụ: =SUBTOTAL(9; A2:A10) sẽ tính tổng tất cả các giá trị, bao gồm cả các hàng ẩn.
- Nhóm từ 101-111: Bỏ qua các giá trị ở hàng ẩn. Ví dụ: =SUBTOTAL(109; A2:A10) sẽ chỉ tính tổng các giá trị ở hàng hiện, bỏ qua các hàng đã bị ẩn.
| Function_num (bao gồm các hàng ẩn) | Function_num (bỏ qua các hàng ẩn) | Hàm |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
4. Ứng dụng thực tế của hàm SUBTOTAL
Phân tích dữ liệu sau khi áp dụng bộ lọc
Hàm SUBTOTAL rất hữu ích khi bạn muốn phân tích dữ liệu đã được lọc trong Excel. Nó cho phép bạn thực hiện các phép tính tổng hợp mà bỏ qua các giá trị bị ẩn do bộ lọc, giúp bạn có được kết quả chính xác hơn mà không cần xử lý thủ công.
Tạo báo cáo động trong Excel
Bằng cách sử dụng hàm SUBTOTAL, bạn có thể tạo các báo cáo động, hiển thị kết quả tổng hợp một cách linh hoạt khi dữ liệu thay đổi. Điều này đặc biệt hữu ích khi làm việc với nhiều báo cáo cần cập nhật thường xuyên và khi sử dụng bộ lọc dữ liệu để chỉ hiển thị các thông tin cần thiết.
Thay thế các hàm tổng hợp khác khi cần bỏ qua giá trị ẩn
Hàm SUBTOTAL là lựa chọn lý tưởng để thay thế các hàm tổng hợp thông thường như SUM, AVERAGE hay COUNT khi bạn cần bỏ qua các giá trị bị ẩn. Điều này giúp bạn tiết kiệm thời gian và tránh sai sót khi xử lý các tập dữ liệu lớn và phức tạp.
5. Lưu ý khi sử dụng hàm SUBTOTAL
Khi sử dụng hàm SUBTOTAL người dùng nên ghi nhớ các lưu ý dưới đây, để trả về kết quả đúng.
Hoạt động hiệu quả nhất với dữ liệu lọc
Hàm SUBTOTAL hoạt động tối ưu khi dữ liệu đã được lọc, cho phép bạn thực hiện phép tính tổng hợp mà không bị ảnh hưởng bởi các giá trị ẩn. Người dùng sẽ có được kết quả chính xác và nhanh chóng khi làm việc với dữ liệu lớn.
Cẩn thận với các vùng dữ liệu trống hoặc liên kết ngoài
Khi sử dụng hàm SUBTOTAL, hãy cẩn thận với các vùng dữ liệu trống hoặc liên kết dữ liệu từ các bảng khác. Những yếu tố này có thể ảnh hưởng đến kết quả tính toán của bạn, vì vậy hãy kiểm tra kỹ trước khi sử dụng.
6. Ví dụ minh họa
Ví dụ 1: Tính tổng doanh thu hiển thị sau khi lọc
Giả sử bạn có một bảng dữ liệu về doanh số bán hàng và muốn tính tổng doanh thu của các sản phẩm có giá bán trên 100.000 đồng sau khi đã lọc. Bạn có thể sử dụng công thức: =SUBTOTAL(9; B2:B10) (giả sử cột B chứa dữ liệu doanh thu).
- Bước 1: Chuẩn bị dữ liệu
| Sản phẩm | Giá bán |
| Sản phẩm A | 150.000 VND |
| Sản phẩm B | 90.000 VND |
| Sản phẩm C | 120.000 VND |
| Sản phẩm D | 110.000 VND |
| Sản phẩm E | 80.000 VND |
| Sản phẩm F | 130.000 VND |
| Sản phẩm G | 95.000 VND |
| Sản phẩm H | 140.000 VND |
| Sản phẩm I | 85.000 VND |
- Bước 2: Áp dụng bộ lọc cho dữ liệu
Chọn vùng dữ liệu: Bôi đen vùng dữ liệu từ cột “Sản phẩm” và “Giá bán” (A1:B10). Để thêm bộ lọc, vào tab Data trên thanh công cụ → Chọn Filter. Một mũi tên sẽ xuất hiện ở đầu mỗi cột.
- Bước 3: Lọc dữ liệu
Để lọc giá bán, bạn hãy nhấn vào mũi tên ở cột Giá bán → Chọn Number Filters (Bộ lọc số) -> Greater Than or Equal To (Lớn hơn hoặc bằng). Tiếp theo, bạn cần nhập 100.000 vào ô giá trị →Nhấn OK. Bây giờ, chỉ các hàng có giá bán từ 100.000 VND trở lên sẽ được hiển thị.
- Bước 4: Sử dụng hàm SUBTOTAL
Nhập công thức: Trong một ô trống dưới bảng dữ liệu (giả sử ô B11), nhập công thức sau để tính tổng doanh thu của các sản phẩm hiển thị: =SUBTOTAL(9; B2:B10)
- Bước 5: Kiểm tra kết quả
Hàm SUBTOTAL sẽ tính tổng doanh thu của các sản phẩm hiển thị, bỏ qua các giá trị bị ẩn do bộ lọc.
Sau khi bạn áp dụng bộ lọc, kết quả của hàm SUBTOTAL sẽ là tổng doanh thu của các sản phẩm có giá bán từ 100.000 VND trở lên, giúp bạn có được kết quả chính xác mà không cần xử lý thủ công.
Ví dụ 2: Đếm số lượng sản phẩm hiển thị trong danh sách lọc
Tương tự, trong trường hợp bạn muốn đếm số lượng sản phẩm thuộc loại “Điện thoại” sau khi lọc. Công thức sẽ là: =SUBTOTAL(3; A2:A10) (giả sử cột A chứa dữ liệu loại sản phẩm).
Gợi ý sử dụng để nâng cao hiệu quả làm việc với dữ liệu trong Excel
Sử dụng hàm SUBTOTAL thay cho các hàm tổng hợp khác giúp bạn bỏ qua các giá trị ẩn, mang lại kết quả chính xác hơn. Kết hợp với bộ lọc dữ liệu, SUBTOTAL giúp phân tích và tính toán dữ liệu một cách linh hoạt, hỗ trợ bạn tạo các báo cáo động tự động cập nhật khi dữ liệu thay đổi, tiết kiệm thời gian và công sức.
7. Kết luận
Hàm SUBTOTAL trong excel là hàm tính tổng linh hoạt và chính xác, đặc biệt khi làm việc với dữ liệu bị lọc hoặc ẩn. Bởi các ưu điểm nổi bật như tùy chọn nhiều phép tính và tương thích cao với bộ lọc dữ liệu. Sử dụng hàm SUBTOTAL giúp người tối ưu hóa quá trình làm việc với dữ liệu hiệu quả hơn.
Để nâng cao chất lượng làm việc, hãy sử dụng hàm SUBTOTAL thay cho các hàm tổng hợp khác khi cần bỏ qua giá trị ẩn. Kết hợp với bộ lọc dữ liệu, hàm SUBTOTAL giúp bạn tạo các báo cáo động cập nhật tự động, tiết kiệm thời gian và đảm bảo kết quả chính xác.
Hy vọng rằng, bài viết đã giúp bạn giải đáp thắc mắc về hàm SUBTOTAL cũng như cách sử dụng sao cho chuẩn chỉnh. Hãy theo dõi Việc làm 24h để không bỏ lỡ những kiến thức hữu ích, và cùng chúng tôi chinh phục và phát triển sự nghiệp của bạn!
