Hàm subtotal là gì

     

Trong nội dung bài viết này, Excel Online đang giải thích chi tiết hàm SUBTOTAL cùng hướng dẫn các bạn cách sử dụng công thức SUBTOTAL để cộng dữ liệu trong những ô quan sát thấy.

Bạn đang xem: Hàm subtotal là gì


HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG

Hàm SUBTOTAL trong Excel có trọng trách để tính tổng phụ trong list hoặc cơ sở dữ liệu. Trong trường phù hợp này, “tổng phụ” (subtotal) không phải tổng những con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế theo phong cách chỉ để tiến hành 1 công dụng cụ thể, nhưng mà hàm SUBTOTAL lại rất thiêng hoạt – có thể tính toán hoặc có tác dụng phép ngắn gọn xúc tích như đếm số ô, tính trung bình, tìm giá chỉ trị mập nhất/nhỏ nhất…

Hàm SUBTOTAL bao gồm ở toàn bộ các phiên bạn dạng Excel từ năm 2016 đến 2007 cùng cả phiên phiên bản thấp hơn.

Cú pháp hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, ,…)

Trong đó:

Function_num: con số xác định tính năng thực hiện

Ref1, Ref2, …: 1 hoặc những ô, hoặc dãy ô nhằm tính tổng phụ. Rất cần được có Ref 1, trường đoản cú Ref 2 mang đến 254 là tuỳ chọn.



Số xác định chức năng thực hiện gồm 2 một số loại sau:

1 -11 bỏ qua những ô đã có lọc ra, tuy thế để lại những hàng được ẩn thủ công.101 – 111 bỏ qua các ô ẩn – vẫn lọc ra cùng ẩn thủ công.
Function_numChức năngMô tả
1101AVERAGETính trung bình những con số
2102COUNTĐếm số ô cất giá trị số
3103COUNTAĐếm số ô ko trống
4104MAXTìm giá chỉ trị khủng nhất
5105MINTìm giá bán trị nhỏ tuổi nhất
6106PRODUCTTính công dụng của các ô
7107STDEVTính độ lệch chuẩn mẫu dựa vào mẫu
8108STDEVPTính độ lệch chuẩn dựa trên cục bộ số
9109SUMCộng các số
10110VARƯớc tính độ giao động dựa trên mẫu
11111VARPƯớc tính độ giao động dựa trên toàn bộ số

Bạn không cần phải nhớ hết những con số chức năng. Ngay khi bạn nhập hàm SUBTOTAL Excel vào 1 ô hoặc bên trên thanh công thức, Excel sẽ giới thiệu danh sách các con số đến bạn.

Ví dụ, đó là cách chúng ta dùng bí quyết SUBTOTAL 9 để cùng tổng các giá trị trong ô tự C2 cho C8:

*

Để thêm 1 số xác định công dụng vào công thức, dấn đúp chuột, khắc ghi phẩy, xác định dãy ô, đóng góp ngoặc cùng nhấn Enter. Công thức hoàn hảo sẽ như sau:

=SUBTOTAL(9,C2:C8)

Tương tự, bạn cũng có thể viết bí quyết SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 nhằm đếm ô đựng số, SUBTOTAL 3 nhằm đếm ô không trống. Trong hình dưới, 3 tác dụng khác đang được dùng.

*

Lưu ý:

Khi chúng ta dùng phương pháp SUBTOTAL Excel với tính năng tính tổng như SUM tốt AVERAGE, Excel đang chỉ tính các ô đựng số, làm lơ ô trống cùng ô cất giá trị chưa hẳn số.

Bạn đã hiểu phương pháp lập công thức SUBTOTAL trong Excel, câu hỏi chính là tại sao lại đề nghị học hàm này? Sao ko dùng những hàm đơn giản, thông thường như SUM, COUNT, MAX? vì sao sẽ được trình diễn dưới đây.

3 LÝ vày ĐỂ DÙNG HÀM SUBTOTAL

So sánh với các hàm Excel truyền thống, SUBTOTAL cho mình những điểm mạnh sau:

Tính giá bán trị trong những hàng được chọn

Vì hàm Excel SUBTOTAL bỏ qua những hàng đã có được lọc ra, bạn cũng có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, những giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ, nếu họ lọc bảng doanh số bán sản phẩm chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ auto điều chỉnh để bỏ qua toàn khu vực khác khi tính tổng.

*

Lưu ý:

Vì những 2 bộ số xác định tính năng (1-11 cùng 101-111) đều vứt qua những ô đã được lọc ra, chúng ta cũng có thể dùng cách làm SUBTOTAL 9 hoặc SUBTOTAL 109.

Xem thêm: 5 Cách Chuyển Từ Chữ Hoa Sang Thường Trong Excel 2022, Thay Đổi Kiểu Chữ Hoa/Thường

Tính các ô chú ý thấy

Công thức SUBTOTAL với số xác định chức năng 101-111 vứt qua các ô đang ẩn, đang lọc ra với ẩn thủ công. Vì thế, khi chúng ta sử dụng công dụng Excel’s Hide nhằm ẩn dữ liệu không liên quan, cần sử dụng số chức năng 101-111 để sa thải các quý hiếm từ mọi hàng ẩn khỏi tổng phụ.

Bỏ qua cực hiếm trong bí quyết SUBTOTAL lồng ghép

Nếu dãy ô trong phương pháp SUBTOTAL có chứa phương pháp SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ ảnh hưởng bỏ qua. Vậy nên số lượng trong bảng sẽ không hẳn tính 2 lần.

Trong hình dưới, bí quyết tính trung bình bao gồm SUBTOTAL(1, C2:C10) bỏ qua kết quả của bí quyết SUBTOTAL vào ô C3 cùng C10.

*

VÍ DỤ DÙNG SUBTOTAL trong EXCEL

Khi chúng ta mới biết hàm SUBTOTAL, hoàn toàn có thể thấy hàm này phức tạp, rắc rối vô nghĩa. Nhưng khi chúng ta dùng nó để xử lý công việc, bạn sẽ nhận ra thuần thục hàm SUBTOTAL ko khó. Ví dụ như sau đây để giúp đỡ bạn với một số trong những mẹo bé dại khi dùng hàm SUBTOTAL.

VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109

Như các bạn đã biết, Excel SUBTOTAL chấp nhận 2 cỗ số xác định chức năng 1-11 và 101-111. Cả 2 bộ số đều vứt qua những hàng đã có lọc ra, tuy vậy 1-11 bao hàm các mặt hàng được ẩn thủ công còn 101-111 thì một số loại trừ. Để nắm rõ hơn về điểm khác hoàn toàn này, xem ví dụ như sau:

Với tổng những hàng đã có được chọn, chúng ta có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

*

Nhưng nếu có các hàng không liên quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows vào Home tab > Cells group > Format > Hide và Unhide, với nhấn chuột phải vào những hàng, kế tiếp Hide, nhưng giờ bạn chỉ việc tổng giá trị trong số hàng chú ý thấy, sàng lọc SUBTOTAL 109:

*

Các số tác dụng khác cũng tuân theo cách tương tự. Ví dụ, để đếm ô ko trống sẽ chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm những ô ko trống chú ý thấy đúng chuẩn nếu có bất kỳ hàng ẩn làm sao trong dãy.

*

Lưu ý: Hàm Excel SUBTOTAL cùng với số chức năng 101-111 bỏ lỡ giá trị trong mặt hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu bạn dùng bí quyết như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn vẫn không tác động đến tổng phụ.

VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU

Nếu ai đang lập báo cáo tổng kết, cần được đưa ra những dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho toàn bộ số liệu, thì biện pháp sau hoàn toàn có thể là giải pháp:

Trong 1 ô, sản xuất 1 danh sách dạng thả xuống (drop-down) bao gồm chứa tên những hàm như Total, Max, Min…Trong ô kế ô danh sách, thêm phương pháp hàm IF lồng ghép tất cả chứa cách làm SUBTOTAL khớp ứng với các hàm trong danh sách.

Ví dụ, mang sử giá trị để tính tổng phụ vào ô C12:C16, và danh sách trong ô A17 cất Total, Average, Max, Min, phương pháp hàm SUBTOTAL vẫn như sau:

=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))

Và tiếp theo, tuỳ vào hàm mà người sử dụng chọn vào danh sách, công thức SUBTOTAL tương xứng sẽ tính giá trị trong những hàng được chọn.

*

Mẹo nhỏ:

Nếu đùng một cái danh sách cùng ô công thức biến mất khỏi bảng tính, thì bạn nên lựa chọn lại chúng trong list lọc.

HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP

Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân hoàn toàn có thể là trong những lý do sau đây

#VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

#DIV/0! xẩy ra khi 1 tổng rõ ràng phải phân tách cho 0 (ví dụ: tính trung bình cùng hoặc độ lệch chuẩn chỉnh của 1 hàng ô không cất giá trị số)

#NAME? tên hàm SUBTOTAL sai bao gồm tả.

Xem thêm: Bộ Công Thương Hoả Tốc Công Bố Danh Mục Mặt Hàng Thiết Yếu, Danh Sách Các Mặt Hàng Thiết Yếu Mới Nhất

Mẹo nhỏ:

Nếu chúng ta chưa quen thuộc với hàm SUBTOTAL, bạn có thể dùng những chức năng thiết đặt sẵn của hàm này cùng để phương pháp tự trả thiện giúp cho bạn hoặc theo dõi đoạn phim sau đây:

Ngoài ra để vận dụng Excel vào quá trình một cách công dụng thì chúng ta còn nên sử dụng xuất sắc các hàm, các công vắt khác của Excel.

Một số hàm cơ bản thường chạm chán như:

SUMIF, SUMIFS nhằm tính tổng theo 1 điểm kiện, các điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, các điều kiệnCác hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò search tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ thường được sử dụng như:

Định dạng theo điều kiện với Conditional formattingThiết lập điều kiện nhập tài liệu với Data ValidationCách đặt Name và thực hiện Name vào công thứcLập báo cáo với Pivot Table…

Rất nhiều kỹ năng và kiến thức phải không nào? toàn cục những kỹ năng này chúng ta đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của học Excel Online. Đây là khóa học giúp bạn hệ thống kỹ năng và kiến thức một biện pháp đầy đủ, đưa ra tiết. Rộng nữa không hề có số lượng giới hạn về thời hạn học tập nên chúng ta cũng có thể thoải mái học bất kể lúc nào, dễ ợt tra cứu giúp lại kiến thức và kỹ năng khi cần. Hiện giờ hệ thống đang có ưu đãi cực kỳ lớn cho mình khi đăng ký tham gia khóa học. Chi tiết xem tại: phauthuatcatmimat.com.Online