Hàm SUBTOTAL trong Excel là một hàm linh hoạt và mạnh mẽ, được sử dụng để thực hiện các phép tính toán học trên một phạm vi dữ liệu, đồng thời hỗ trợ bỏ qua các hàng đã được ẩn hoặc lọc. Hàm này thường được áp dụng trong các bài toán liên quan đến tổng hợp, phân tích dữ liệu và báo cáo. Bài viết này sẽ cung cấp thông tin đầy đủ về cú pháp, cách sử dụng, lưu ý quan trọng, ví dụ minh họa và các ứng dụng thực tế của hàm SUBTOTAL.
Giới Thiệu Về Hàm SUBTOTAL
Hàm SUBTOTAL có thể thực hiện nhiều phép tính khác nhau như tính tổng, trung bình, đếm, tìm giá trị lớn nhất/nhỏ nhất, v.v. Điểm đặc biệt của hàm này là nó có thể bỏ qua các hàng đã được ẩn hoặc lọc, giúp việc tính toán trở nên linh hoạt hơn .
Cú Pháp Hàm SUBTOTAL
Cú pháp của hàm SUBTOTAL như sau:
=SUBTOTAL(function_num, ref1, [ref2], ...)
Trong đó:
- function_num: Mã số xác định phép tính cần thực hiện (bắt buộc). Các mã số từ 1 đến 11 bỏ qua các hàng đã được ẩn thủ công, trong khi các mã số từ 101 đến 111 bỏ qua cả các hàng đã được ẩn thủ công và lọc .
- ref1, ref2, …: Phạm vi dữ liệu cần tính toán (bắt buộc). Có thể là một hoặc nhiều phạm vi ô .
Các Mã Số Function_num
Hàm SUBTOTAL hỗ trợ các mã số sau:
| Mã số | Phép tính | Mã số (bỏ qua hàng ẩn/lọc) |
|---|---|---|
| 1 | AVERAGE (Trung bình) | 101 |
| 2 | COUNT (Đếm số) | 102 |
| 3 | COUNTA (Đếm ô không rỗng) | 103 |
| 4 | MAX (Giá trị lớn nhất) | 104 |
| 5 | MIN (Giá trị nhỏ nhất) | 105 |
| 6 | PRODUCT (Tích) | 106 |
| 7 | STDEV (Độ lệch chuẩn) | 107 |
| 8 | STDEVP (Độ lệch chuẩn tổng thể) | 108 |
| 9 | SUM (Tổng) | 109 |
| 10 | VAR (Phương sai) | 110 |
| 11 | VARP (Phương sai tổng thể) | 111 |
Cách Sử Dụng Hàm SUBTOTAL
Để sử dụng hàm SUBTOTAL, bạn có thể thực hiện theo các bước sau:
- Nhập dữ liệu: Nhập dữ liệu cần tính toán vào một phạm vi ô trong Excel (ví dụ:
B5:B10). - Nhập công thức: Trong ô bạn muốn hiển thị kết quả, nhập công thức
=SUBTOTAL(function_num, B5:B10). - Nhấn Enter: Kết quả sẽ hiển thị dựa trên phép tính và phạm vi dữ liệu đã chọn .
Ví Dụ Minh Họa
Ví dụ 1: Tính tổng bỏ qua hàng ẩn
Giả sử ô B5:B10 chứa các giá trị 10, 20, 30, 40, 50, 60 và hàng chứa 30 đã được ẩn. Để tính tổng bỏ qua hàng ẩn, công thức:
=SUBTOTAL(109, B5:B10)
Kết quả trả về là 180 (bỏ qua giá trị 30).
Ví dụ 2: Tính trung bình bỏ qua hàng lọc
Giả sử ô B5:B10 chứa các giá trị 10, 20, 30, 40, 50, 60 và các hàng chứa 20 và 40 đã được lọc. Để tính trung bình bỏ qua hàng lọc, công thức:
=SUBTOTAL(101, B5:B10)
Kết quả trả về là 35 (chỉ tính các giá trị 10, 30, 50, 60).
Ví dụ 3: Đếm số ô không rỗng
Giả sử ô B5:B10 chứa các giá trị 10, "", 30, 40, "", 60. Để đếm số ô không rỗng, công thức:
=SUBTOTAL(103, B5:B10)
Kết quả trả về là 4.
Lưu Ý Khi Sử Dụng Hàm SUBTOTAL
Khi sử dụng hàm SUBTOTAL, bạn cần lưu ý những điểm sau:
- Mã số function_num: Chọn mã số phù hợp với phép tính cần thực hiện. Các mã số từ 1 đến 11 bỏ qua hàng ẩn thủ công, trong khi các mã số từ 101 đến 111 bỏ qua cả hàng ẩn thủ công và lọc .
- Phạm vi dữ liệu: Đảm bảo phạm vi dữ liệu được chọn chính xác. Nếu phạm vi không hợp lệ, hàm sẽ trả về lỗi
#VALUE!. - Hàm lồng nhau: Hàm
SUBTOTALkhông thể lồng nhau. Nếu cần thực hiện nhiều phép tính, hãy sử dụng các hàm riêng biệt .
Ứng Dụng Thực Tế Hàm SUBTOTAL
Hàm SUBTOTAL thường được sử dụng trong các tình huống như:
- Tổng hợp dữ liệu: Tính toán các giá trị tổng hợp từ một phạm vi dữ liệu, đặc biệt khi dữ liệu được lọc hoặc ẩn .
- Phân tích dữ liệu: Áp dụng trong các bài toán liên quan đến phân tích và báo cáo dữ liệu .
- Báo cáo động: Tạo các báo cáo động có thể tự động cập nhật khi dữ liệu được lọc hoặc ẩn .
Kết luận
Hàm SUBTOTAL là một công cụ hữu ích giúp bạn thực hiện các phép tính toán học một cách linh hoạt và chính xác, đặc biệt khi làm việc với dữ liệu được lọc hoặc ẩn. Hy vọng bài viết này đã cung cấp cho bạn những kiến thức cần thiết để áp dụng hàm này vào công việc hàng ngày. Hãy thử ngay để trải nghiệm sự tiện lợi của hàm SUBTOTAL!
