Sub total là gì

Trong nội dung bài viết này, Excel Online sẽ giải thích cụ thể hàm SUBTOTAL và hướng dẫn chúng ta cách thực hiện công thức SUBTOTAL để cộng dữ liệu trong những ô nhìn thấy.

Bạn đang xem: Sub total là gì

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

Hàm SUBTOTAL vào Excel có nhiệm vụ để tính tổng phụ trong list hoặc các đại lý dữ liệu. Trong trường thích hợp này, “tổng phụ” (subtotal) chưa hẳn tổng những con số trong một dãy ô xác định. Những hàm Excel khác được thiết kế theo phong cách chỉ để triển khai 1 chức năng cụ thể, tuy nhiên hàm SUBTOTAL lại rất thiêng hoạt – hoàn toàn 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ị bự nhất/nhỏ nhất…

Hàm SUBTOTAL tất cả ở tất cả các phiên phiên bản Excel từ năm 2016 đến 2007 và 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ác dụng thực hiện

Ref1, Ref2, …: 1 hoặc những ô, hoặc hàng ô nhằm tính tổng phụ. Cần phải có Ref 1, tự Ref 2 mang đến 254 là tuỳ chọn.

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

1 -11 bỏ qua các ô đã làm được lọc ra, cơ mà để lại các hàng được ẩn thủ công. 101 – 111 bỏ qua các ô ẩn – đã lọc ra cùng ẩn thủ công.

Function_num	Chức năng	Mô tả
1	101	AVERAGE	Tính trung bình các con số
2	102	COUNT	Đếm số ô chứa giá trị số
3	103	COUNTA	Đếm số ô không trống
4	104	MAX	Tìm giá trị mập nhất
5	105	MIN	Tìm giá trị nhỏ dại nhất
6	106	PRODUCT	Tính hiệu quả của các ô
7	107	STDEV	Tính độ lệch chuẩn mẫu dựa trên mẫu
8	108	STDEVP	Tính độ lệch chuẩn chỉnh dựa trên toàn cục số
9	109	SUM	Cộng những số
10	110	VAR	Ước tính độ giao động dựa bên trên mẫu
11	111	VARP	Ước tính độ xấp xỉ dựa trên cục 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ẽ đưa ra danh sách các con số mang lại bạn.

Ví dụ, đây là cách chúng ta dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 mang lại C8:

Để thêm một số xác định tính năng vào công thức, dìm đúp chuột, đánh dấu phẩy, xác định 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 bí quyết SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa 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 các bạn dùng bí quyết SUBTOTAL Excel với tác dụng tính tổng như SUM xuất xắc AVERAGE, Excel vẫn chỉ tính các ô chứa số, làm lơ ô trống với ô đựng giá trị không hẳn 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 bắt buộc học hàm này? Sao không dùng các hàm đối kháng giản, thông thường như SUM, COUNT, MAX? lý do 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 mình những điểm mạnh sau:

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

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

Ví dụ, nếu bọn họ lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, phương pháp SUBTOTAL sẽ auto điều chỉnh để quăng quật qua toàn khu vực khác lúc tính tổng.

Lưu ý:

Vì những 2 cỗ số xác định công dụng (1-11 cùng 101-111) đều quăng quật qua các ô đã có lọc ra, bạn cũng có thể dùng phương pháp SUBTOTAL 9 hoặc SUBTOTAL 109.

Xem thêm: Tặng 100+ Hình Ảnh Bánh Sinh Nhật Siêu Độc Và Miễn Phí, Tặng 100+ Hình Ảnh Bánh Sinh Nhật Siêu Độc

Tính những ô nhìn thấy

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

Bỏ qua quý hiếm trong phương pháp SUBTOTAL lồng ghép

Nếu hàng ô trong bí quyết SUBTOTAL tất cả chứa công thức 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 phải tính 2 lần.

Trong hình dưới, bí quyết tính trung bình thiết yếu SUBTOTAL(1, C2:C10) bỏ qua tác dụng của phương pháp SUBTOTAL trong ô C3 cùng C10.

VÍ DỤ DÙNG SUBTOTAL vào EXCEL

Khi chúng ta 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 bạn dùng nó để xử lý công việc, các bạn sẽ nhận ra nhuần nhuyễn hàm SUBTOTAL không khó. Lấy một ví dụ sau đây để giúp đỡ bạn với một trong những mẹo nhỏ dại khi cần sử dụng hàm SUBTOTAL.

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

Như chúng ta đã biết, Excel SUBTOTAL gật đầu 2 cỗ số xác định chức năng 1-11 cùng 101-111. Cả hai bộ số đều quăng quật qua những hàng đã làm được lọc ra, nhưng lại 1-11 bao gồm các sản phẩm được ẩn thủ công bằng tay còn 101-111 thì nhiều 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 các hàng đã có được chọn, bạn cũng có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

Nhưng giả dụ có các hàng không tương quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, với nhấn chuột đề xuất vào những hàng, tiếp nối Hide, nhưng giờ bạn chỉ việc tổng giá chỉ trị trong các hàng chú ý thấy, chắt 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 ô không trống vẫn chọn, cần sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy thế SUBTOTAL 103 hoàn toàn có thể đếm những ô ko trống nhìn thấy đúng chuẩn nếu có bất kì hàng ẩn nào trong dãy.

Lưu ý: Hàm Excel SUBTOTAL cùng với số tác dụng 101-111 làm lơ giá trị trong mặt hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu như khách hàng dùng bí quyết 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 report tổng kết, cần được đưa ra các dữ liệu tổng kết khác biệt nhưng bạn không tồn tại chỗ trống cho toàn bộ số liệu, thì cách sau có thể là giải pháp:

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

Ví dụ, giả sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 đựng Total, Average, Max, Min, bí quyết hàm SUBTOTAL đã 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, phương pháp SUBTOTAL khớp ứ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 với ô 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 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 có thể là trong số những lý bởi vì 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 gồm 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 hàng ô không chứa giá trị số)

#NAME? thương hiệu hàm SUBTOTAL sai bao gồm tả.

Mẹo nhỏ:

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

Ngoài ra để áp dụng Excel vào các bước một cách hiệu quả thì các bạn còn nên sử dụng giỏi các hàm, những công cố kỉnh khác của Excel.Một số hàm cơ phiên bản thường gặp mặt như: SUMIF, SUMIFS nhằm tính tổng theo 1 điều kiện, nhiều điều kiện COUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, nhiều điều kiện các hàm xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số… những 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 formatting tùy chỉnh điều khiếu nại nhập dữ liệu với Data Validation giải pháp đặt Name và sử dụng Name trong bí quyết Lập báo cáo với Pivot Table…Rất nhiều kiến thức và kỹ năng phải không nào? toàn thể những kiến thức và kỹ năng này chúng ta đều có thể học được trong khóa học EX101 – Excel trường đoản cú cơ bạn dạng tới siêng gia của học Excel Online. Đây là khóa học giúp cho bạn hệ thống kỹ năng một phương pháp đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu giúp lại kiến thức khi cần. Hiện thời hệ thống đang có ưu đãi vô cùng lớn cho bạn khi đk tham gia khóa học. Cụ thể xem tại: spqnam.edu.vn.Online