Công thức và cách sử dụng hàn Subtotal trong excel và ví dụ minh họa

Hàm SUBTOTAL trong Excel là gì? Công thức tính hàm subtotal - Hướng dẫn sử dụng hàm subtotal trong Excel

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 Mega 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?

Bài viết ngày hôm nay, Mega sẽ giải đáp những chi tiết liên quan đến hàm SUBTOTAL trong Excel. 

Hướng dẫn sử dụng hàm SUBTOTAL trong Excel chi tiết 

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:

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, Mega đã 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. 

Ngoài ra tại trang web của Mega còn có  những nội dung về các hàm, cách sửa lỗi trong Excel. Nếu bạn quan tâm có thể truy cập vào mega.com.vn để cập nhật thông tin và những nội dung mới nhất nhé!. 

Xem thêm >>>

Cách sử dụng hàm Choose trong Excel đơn giản kèm ví dụ minh hoạ

Hàm day là gì? Cách sử dụng hàm day đơn giản nhất

Hướng dẫn dùng hàm Mod dễ dàng trong Excel

copyright © mega.com.vn