Hàm SUBTOTAL trong Excel
Hàm Subtotal trong Excel là một hàm có sẵn được sử dụng để cung cấp cho chúng ta tổng phụ của một bảng dữ liệu hoặc tập dữ liệu nhất định, công thức này nhận hai đối số, một là số hàm và đối số thứ hai là phạm vi, phạm vi có thể là nhiều, excel có giá trị đặt trước số hàm mà chúng ta cần nhớ trước khi sử dụng hàm này vì 1 đại diện cho trung bình và 2 đại diện cho số lượng, v.v.
Cú pháp
Công thức SUBTOTAL chấp nhận các tham số và đối số sau:
- function_num - Đây là số chỉ định hàm nào nên được sử dụng cho các phép tính tổng phụ trong danh sách.
- ref1 - Đây là một phạm vi được đặt tên hoặc một tham chiếu đến tổng phụ.
- ref2 - Đây là tùy chọn. Đây là phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.
- Giá trị trả về: Giá trị trả về sẽ là một giá trị số, đại diện cho một loại tổng phụ cụ thể.
Ghi chú sử dụng
- Hàm SUBTOTAL được sử dụng để lấy tổng phụ trong danh sách hoặc cơ sở dữ liệu.
- SUBTOTAL có thể sử dụng các hàm khác nhau khi tính tổng phụ, bao gồm AVERAGE, COUNT, MAX và các hàm khác (tất cả các hàm được liệt kê bên dưới).
- Hàm SUBTOTAL loại trừ các giá trị trong hàng bị ẩn bởi bộ lọc và nó là giá trị mặc định.
- Hàm SUBTOTAL là một hàm rất hữu ích trong Microsoft Excel.
Các chức năng có sẵn cho SUBTOTAL
Như đã nêu ở trên, hàm SUBTOTAL có thể sử dụng các hàm khác nhau khi tính tổng; đây là danh sách các hàm có sẵn với việc bao gồm hoặc loại trừ các giá trị trong các hàng ẩn. Hoạt động của hàm SUBTOTAL được điều khiển bởi đối số “function_num.” Xem bảng dưới đây -
|
Chức năng | function_num
Bao gồm các giá trị ẩn | function_num
Loại trừ các giá trị ẩn |
|
TRUNG BÌNH CỘNG | 1 |
101 |
|
ĐẾM | 2 |
102 |
|
COUNTA | 3 |
103 |
|
TỐI ĐA | 4 |
104 |
|
MIN | 5 |
105 |
|
SẢN PHẨM | 6 |
106 |
|
STDEV | 7 |
107 |
|
STDEVP | số 8 |
108 |
|
TỔNG |
9 |
109 |
|
VAR |
10 |
110 |
|
VARP | 11 |
111 |
Giá trị ẩn theo cách thủ công trong hàm SUBTOTAL
Bạn phải nhận thấy rằng các giá trị được sử dụng cho đối số function_num được ghép nối trong bảng trên, ví dụ: 1-101, 2-102, 3-103, v.v. Điều này liên quan đến cách hàm SUBTOTAL xử lý các hàng ẩn theo cách thủ công.
Hãy làm cho nó rõ ràng hơn.
- Khi giá trị của function_num nằm trong khoảng 1-11 , hàm SUBTOTAL sẽ bao gồm các ô được ẩn theo cách thủ công.
- Khi giá trị của function_num nằm trong khoảng 101-111 , hàm SUBTOTAL sẽ loại trừ hoặc bỏ qua các giá trị trong các hàng được ẩn theo cách thủ công.
Luôn nhớ rằng hàm SUBTOTAL loại trừ các giá trị trong các ô được ẩn bằng bộ lọc. Bất kể function_num, các giá trị có trong các hàng 'đã lọc ra' sẽ bị loại trừ.
Làm thế nào để mở hàm SUBTOTAL trong Excel?
- Bạn chỉ cần nhập công thức SUBTOTAL mong muốn vào ô bắt buộc để đạt được giá trị trả về trên đối số.
- Bạn có thể mở thủ công hộp thoại công thức SUBTOTAL trong bảng tính và nhập các giá trị lôgic để đạt được giá trị trả về.
- Hãy xem ảnh chụp màn hình bên dưới để xem tùy chọn SUBTOTAL trong menu Math & Trig Function.
- Nhấp vào tùy chọn SUBTOTAL. Hộp thoại công thức SUBTOTAL sẽ mở ra nơi bạn có thể đặt các giá trị đối số để nhận giá trị trả về.
Cách sử dụng Hàm SUBTOTAL trong Excel? (với các ví dụ)
Dưới đây chúng ta hãy xem xét một số ví dụ về hàm SUBTOTAL. Những ví dụ này sẽ giúp bạn khám phá cách sử dụng hàm SUBTOTAL.
Dựa trên bảng tính Excel ở trên, hãy xem xét ba ví dụ và xem hàm SUBTOTAL trả về dựa trên Công thức SUBTOTAL trong Excel.
Hãy xem xét các ảnh chụp màn hình dưới đây của các ví dụ trên để hiểu rõ ràng.
Ví dụ 1
Ví dụ số 2
Ví dụ # 3
Ví dụ # 4
Ví dụ # 5
Ví dụ # 6
Ví dụ # 7
Ví dụ # 8
Ví dụ # 9
Ví dụ # 10
Ví dụ # 11
Các lỗi trong Hàm SubTotal
Nếu bạn gặp bất kỳ loại lỗi nào từ Hàm SUBTOTAL trong excel, thì đó có thể là bất kỳ lỗi nào sau đây:
- #GIÁ TRỊ! - Lỗi này xảy ra nếu đối số function_num đã cho không phải là một trong các giá trị được phép (tức là giữa 1 -11 hoặc 101-111).
- # DIV / 0! - Lỗi này xảy ra đối với phép tính bắt buộc liên quan đến phép chia cho số 0. Phép tính bắt buộc có thể là bất kỳ điều gì, ví dụ: tìm giá trị trung bình, độ lệch chuẩn hoặc phương sai cho một phạm vi ô không chứa bất kỳ giá trị số nào.
Những điều cần ghi nhớ
- Hàm SUBTOTAL trả về tổng phụ của các số có trong một cột của danh sách hoặc cơ sở dữ liệu.
- Hàm SUBTOTAL có khả năng trả về SUM, AVERAGE, COUNT, MAX và các giá trị khác có giá trị bao gồm hoặc loại trừ trong các hàng ẩn.
- Khi giá trị của function_num nằm trong khoảng 1-11, hàm SUBTOTAL sẽ bao gồm các giá trị ẩn.
- Khi giá trị của function_num nằm trong khoảng 101-111, hàm SUBTOTAL sẽ loại trừ hoặc bỏ qua các giá trị ẩn.
- Trong danh sách được lọc, SUBTOTAL sẽ luôn bỏ qua các giá trị có trong các hàng ẩn, bất kể function_num.
- Hàm SUBTOTAL bỏ qua giá trị tổng phụ khác đã tồn tại trong các tham chiếu. Nó được thực hiện để tránh lỗi đếm kép.
- Hàm SUBTOTAL trong Excel được thiết kế để tính toán giá trị dữ liệu theo chiều dọc, được sắp xếp theo chiều dọc trong trang tính excel. Trong phạm vi ngang, giá trị trong các cột ẩn sẽ luôn được bao gồm.
