CÔNG THỨC VÀ CÁCH DÙNG HÀM SUBTOTAL TRONG EXCEL DỄ HIỂU, CÓ VÍ DỤ

Trong bài viết này, Excel Online đã giải thích cụ thể hàm SUBTOTAL và hướng dẫn các bạn cách thực hiện công thức SUBTOTAL để cùng dữ liệu trong các ô chú ý thấy.

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

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

Cú pháp hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, ,…)

Trong đó:

Function_num: con số xác định công dụng thực hiện

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc hàng ô nhằm tính tổng phụ. Rất cần phải có Ref 1, từ bỏ Ref 2 mang lại 254 là tuỳ chọn.

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

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

Ví dụ, đây là cách bạn dùng cách làm 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, nhấn đúp chuột, lưu lại phẩy, xác minh dãy ô, đóng ngoặc với nhấn Enter. Công thức hoàn hảo sẽ như sau:

=SUBTOTAL(9,C2:C8)

Tương tự, chúng ta cũng có thể viết phương pháp SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 nhằm đếm ô cất số, SUBTOTAL 3 nhằm đếm ô ko trống. Trong hình dưới, 3 chức năng khác đang rất được dùng.

Lưu ý:

Khi chúng ta dùng bí quyết SUBTOTAL Excel với tính năng tính tổng như SUM hay AVERAGE, Excel đã chỉ tính những ô đựng số, bỏ qua mất ô trống cùng ô chứa giá trị chưa hẳn số.

Bạn đã hiểu phương pháp lập công thức SUBTOTAL trong Excel, thắc mắc chính là lý do lại bắt buộc học hàm này? Sao không dùng những hàm đơn giản, thông thường như SUM, COUNT, MAX? vì sao sẽ được trình bày dưới đây.

Xem thêm: Dịch vụ đăng ký khám bệnh qua tổng đài 1081, cách đăng ký khám bệnh qua tổng đài 1080

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

So sánh với những hàm Excel truyền thống, SUBTOTAL cho mình những lợi thế sau:

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

Ví dụ, nếu chúng ta lọc bảng doanh số bán sản phẩm chỉ của vùng Miền Đông, cách làm SUBTOTAL sẽ tự động điều chỉnh để quăng quật 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ác dụng (1-11 và 101-111) đều bỏ qua các ô đã làm được lọc ra, chúng ta cũng có thể dùng cách làm SUBTOTAL 9 hoặc SUBTOTAL 109.

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

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

Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) vứt qua kết quả của phương pháp SUBTOTAL vào ô C3 cùng C10.

VÍ DỤ DÙNG SUBTOTAL trong EXCEL

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

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

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

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

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

Các số tính năng khác cũng làm theo cách tương tự. Ví dụ, để đếm ô ko trống vẫn chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Mà lại SUBTOTAL 103 hoàn toàn có thể đếm các ô ko trống quan sát thấy đúng chuẩn nếu có bất cứ hàng ẩn như thế nào trong dãy.

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 bỏ qua mất giá trị trong mặt hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu như bạn dùng bí quyết như SUBTOTAL(109, A1:E1) nhằm tính tổng những số trong sản phẩm ngang, cột bị ẩn đang không ảnh hưở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 phải đưa ra các dữ liệu tổng kết khác biệt nhưng bạn không có chỗ trống cho toàn bộ số liệu, thì biện pháp sau rất có thể là giải pháp:

Ví dụ, mang sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 cất Total, Average, Max, Min, phương pháp hàm SUBTOTAL sẽ 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 tiêu dùng chọn vào danh sách, công thức SUBTOTAL tương ứng sẽ tính giá trị trong các hàng được chọn.

Mẹo nhỏ:

Nếu đùng một cái danh sách và ô công thức bặt tăm khỏi bảng tính, thì bạn nên chọn lại bọn chúng trong danh sách 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 có thể là một trong những lý do sau đây

#VALUE! Số xác định tác dụng không nằm trong tầm 1-11 hoặc 101-111 hay tất cả tham chiếu (ref) là tham chiếu 3D.

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

#NAME? tên hàm SUBTOTAL sai chính tả.

Mẹo nhỏ:

Nếu chúng ta chưa thân quen với hàm SUBTOTAL, chúng ta có thể dùng các chức năng thiết lập sẵn của hàm này và để bí quyết tự trả thiện giúp cho bạn hoặc theo dõi video clip sau đây: