Công thức subtotal là gì

Trong bài viết này, Excel Online vẫn giải thích cụ thể hàm SUBTOTAL với 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 những ô quan sát thấy.

Bạn đang xem: Công thức subtotal là gì


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

Hàm SUBTOTAL vào Excel có trọng trách để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường vừa lòng này, “tổng phụ” (subtotal) chưa 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ế chỉ để tiến hành 1 tác dụng cụ thể, mà lại hàm SUBTOTAL lại rất linh thiêng hoạt – hoàn toàn có thể tính toán hoặc làm cho phép xúc tích và ngắn gọn như đếm số ô, tính trung bình, tìm giá trị mập nhất/nhỏ nhất…

Hàm SUBTOTAL bao gồm ở tất cả 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 tác dụng thực hiện

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



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

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

Bạn không nhất thiết phải nhớ hết các 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ẽ giới thiệu danh sách các con số cho bạn.

Ví dụ, đó là cách bạn dùng phương pháp SUBTOTAL 9 để cộng tổng các giá trị vào ô từ C2 đến C8:

*

Để thêm một số xác định chức nă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 cùng nhấn Enter. Công thức hoàn chỉnh sẽ như sau:

=SUBTOTAL(9,C2:C8)

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

*

Lưu ý:

Khi bạn dùng bí quyết SUBTOTAL Excel với tính năng tính tổng như SUM tuyệt AVERAGE, Excel sẽ chỉ tính các ô cất số, bỏ qua mất ô trống với ô cất giá trị chưa phải số.

Bạn đã biết phương pháp lập công thức SUBTOTAL trong Excel, câu hỏi chính là lý do lại đề xuất học hàm này? Sao ko dùng các hàm 1-1 giản, bình thường như SUM, COUNT, MAX? tại sao sẽ được trình diễn dưới đây.

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

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

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

Vì hàm Excel SUBTOTAL vứt qua các 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, các giá trị trong tổng phụ được auto tính lại theo cỗ 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, phương pháp SUBTOTAL sẽ auto điều chỉnh để bỏ qua tất cả vùng khác lúc tính tổng.

*

Lưu ý:

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

Xem thêm: Các Loại Môi Trường Kinh Doanh Quốc Tế Là Gì, Ra Trường Làm Gì?

Tính những ô quan sát thấy

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

Bỏ qua quý giá trong cách làm SUBTOTAL lồng ghép

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

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

*

VÍ DỤ DÙNG SUBTOTAL trong EXCEL

Khi các bạn mới biết hàm SUBTOTAL, rất 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ó để giải quyết và xử lý công việc, bạn sẽ nhận ra nhuần nhuyễn hàm SUBTOTAL ko khó. Lấy ví dụ như sau đây để giúp đỡ bạn với một vài mẹo nhỏ 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 đồng ý 2 cỗ số xác định tính năng 1-11 với 101-111. Cả hai bộ số đều vứt qua những hàng đã được lọc ra, nhưng lại 1-11 bao hàm các sản phẩm được ẩn bằng tay thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về điểm khác biệt này, xem lấy ví dụ sau:

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

*

Nhưng ví như có các hàng không liên quan đã ẩn thủ công bằng phương pháp 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 các hàng, tiếp nối Hide, mà giờ bạn chỉ cần tổng giá chỉ trị trong các hàng nhìn thấy, chọn lọc SUBTOTAL 109:

*

Các số tác dụng khác cũng làm theo cách tương tự. Ví dụ, nhằm đếm ô không trống đang chọn, sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Dẫu vậy SUBTOTAL 103 hoàn toàn có thể đếm những ô không trống chú ý thấy chính xác nếu có bất cứ hàng ẩn làm sao trong dãy.

*

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

Trong 1 ô, sản xuất 1 list 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 công thức hàm IF lồng ghép gồm chứa công thức SUBTOTAL tương xứng với các hàm trong danh sách.

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

*

Mẹo nhỏ:

Nếu đùng một cái danh sách với ô công thức mất tích khỏi bảng tính, thì bạn hãy lựa chọn lại bọn 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 rất có thể là trong những lý vì chưng sau đây

#VALUE! Số xác định tính năng không nằm trong vòng 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 của 1 hàng ô không đựng giá trị số)

#NAME? thương hiệu hàm SUBTOTAL sai thiết yếu tả.

Mẹo nhỏ:

Nếu các bạn chưa quen với hàm SUBTOTAL, bạn cũng có thể dùng các chức năng thiết đặt sẵn của hàm này cùng để công thức tự hoàn thiện khiến cho bạn hoặc theo dõi đoạn clip sau đây:

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

Một số hàm cơ bản thường gặp mặt như:

SUMIF, SUMIFS để tính tổng theo một điểm kiện, các điều kiệnCOUNTIF, COUNTIFS để 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 đk với Conditional formattingThiết lập đk nhập tài liệu với Data ValidationCách đặt Name và áp dụng Name trong công thứcLập report với Pivot Table…

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