Phần mềm Excel thường được nhiều người dùng để tối ưu công việc một cách tốt hơn. Sử dụng Excel lâu ngày bạn đã biết hết các hàm trong đó chưa? Hôm nay sửa máy tính chuyên nghiệp Đà Nẵng tiếp tục chia sẻ với bạn thêm với bạn hàm SUBTOTAL, đây là hàm cũng được nhiều người sử dụng, nếu bạn muốn tìm hiểu:
- Hàm SUBTOTAL là hàm dùng để làm gì?
- Công thức hàm SUBTOTAL?
- Cách sử dụng hàm SUBTOTAL?
Nội dụng mục lục
I. Hàm subtotal trong Excel là gì?
Hàm subtotal được dùng làm để tính tổng phụ trong danh sách hoặc đếm số ô, cơ sở dữ liệu,… trong Excel.
Thường thì hàm SUBTOTAL được sử dụng để tính giá trị trong các hàng được chọn; tính các ô nhìn thấy; tính giá trị lồng ghép;…
II. Công thức tính hàm subtotal trong Excel
=SUBTOTAL (function_num, ref1, [ref2], …)
Trong đó:
- Function_num: Là một số từ 1 đến 11 hoặc 101 đến 111 để xác định dùng hàm nào trong tính toán tổng phụ trong danh sách.
- Ref1: Là phạm vi vùng tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ.
- Ref2…: Có thể từ 2 đến 254 phạm vi mà bạn muốn tính tổng phụ.
– Một số lưu ý khi sử dụng hàm SUBTOTAL:
- Nếu có hàm SUBTOTAL khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính toán nhằm tránh trường hợp tính toán 2 lần.
- Đối với các hằng số function_num từ 1 đến 11. Hàm SUBTOTAL tính toán cả các giá trị ẩn trong vùng dữ liệu.
- Đối với các hằng số từ 101 đến 111 chỉ tính toán cho các giá trị không ẩn trong vùng dữ liệu.
- Hàm SUBTOTAL sẽ bỏ qua bất kỳ hàng nào bị ẩn bởi lệnh Filter (Auto Filter).
- Hàm SUBTOTAL được dùng cho các cột dữ liệu. Nó không được dùng cho các hàng dữ liệu.
- Bất kỳ dữ liệu nào dưới dạng 3-D thì hàm SUBTOTAL sẽ báo lỗi #VALUE!.
- Bảng Function_num bao gồm các mã số dùng để tính các trường hợp khác nhau trong hàm SUBTOTAL:
| 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 |
III. Hướng dẫn sử dụng hàm SUBTOTAL trong Excel bằng ví dụ minh họa
Ví dụ 1: Tính tổng trong bảng tính Excel nhờ hàm SUBTOTAL
Bạn cần thực hiện theo các bước sau
Bước 1: Trong bảng dữ liệu tính Excel, nhập hàm =SUBTOTAL(9,F6:F14) vào ô tham chiếu muốn hiển thị kết quả.
Sử dụng hàm SUBTOTAL để tính tổng thuế trong bảng trên
Bước 2: Sau khi nhập xong công thức như bảng trên, bạn nhấn Enter để nhận lại kết quả tổng.
Kết quả nhận được sau khi dùng hàm SUBTOTAL để tính tổng trong bảng tính Excel
Ví dụ 2: Tính số trung bình thu được trong bảng tính Excel nhờ hàm SUBTOTAL
Trong ví dụ này mình sẽ tính số tiền trả trước trung bình nhờ vào bảng tính Excel nhờ hàm SUBTOTAL.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =SUBTOTAL(1,G6:G14) vào ô tham chiếu muốn hiển thị kết quả.
Tính số trung bình thu được trong bảng tính Excel nhờ hàm SUBTOTAL
Ví dụ 3: Tính tổng vùng được lọc
Giả sử bạn có bảng sau và cần lọc ra tổng tiền của vải Katê. Bạn thực hiện thao tác như sau:
Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.
Vào Home > Sort and Filter > Filter
Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Tên hàng”, tick chọn “Vải Katê” và nhấn OK.
Hướng dẫn cách lọc như trên bảng
Bước 3: Bảng dữ liệu của Vải Katê được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập:
=SUBTOTAL(9,D7:D8)
Lọc vải Katê ra để dễ dàng tính tổng
Giải thích công thức:
- function_num = 9 là giá trị đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây do chúng ta cần tính tổng nên sẽ chọn hàm SUM – tương ứng với số 9.
- ref1 = D7:D9 là phạm vi cần tính tổng.
Bước 4: Nhấn Enter để nhận lại kết quả.
Sau khi hoàn thành xong công thức bạn nhấn Enter để hiện kết quả như hình trên
Ví dụ 4: Đếm số ô được lọc không trống
Giả sử bạn có bảng dữ liệu như minh họa bên dưới. Nhiệm vụ của bạn là đếm số tên của mã nhân viên đã có tên đơn vị để tiện thống kê.
Bước 1: Đầu tiên thì bạn cần lọc tên thuộc mã nhân viên A theo hướng dẫn dưới đây.
Lọc mã nhân viên A để tiếp tục đếm số đã có tên đơn vị
Bước 2: Ta tiếp tục thực hiện các thao tác trong hình để lọc ra được mã nhân viên A.
Thực hiện một vài thao tác để lọc được mã nhân viên A trong bảng tính
Bước 3: Sau khi lọc xong được mã nhân viên A bạn sẽ được như bảng cửa sổ dưới đây.
Hoàn tất được việc lọc mã nhân viên A trong bảng tính
Bước 4: Bây giờ thì bạn có thể bắt đầu nhập công thức để tính những bạn đã có tên đơn vị bằng công thức sau =SUBTOTAL(3,D4:D13)
Nhập theo công thức như trên hình để tính những bạn đã có tên đơn vị
Bước 5: Nhấn Enter để kiểm tra lại kết quả.
Kết quả của phép tính sẽ được hiển thị như trên hình
IV. Tổng kết
Như vậy trong bài viết ngày hôm nay, sua may tinh chuyen nghiep Da Nang đã hướng dẫn bạn cách sử dụng hàm SUBTOTAL trong Excel. Hi vọng những thông tin trên sẽ có ích cho bạn trong việc tính toán.
.
