Trong nội dung bài viết này, Excel Online đang lý giải chi tiết hàm SUBTOTAL và gợi ý các bạn bí quyết sử dụng cách làm SUBTOTAL để cộng tài liệu trong số ô nhìn thấy.

Bạn đang xem: Subtotal là gì

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

Hàm SUBTOTAL vào Excel bao gồm nhiệm vụ nhằm tính tổng phụ vào danh sách hoặc cơ sở dữ liệu. Trong ngôi trường hợp này, “tổng phụ” (subtotal) chưa hẳn tổng những số lượng trong một dãy ô xác minh. Các hàm Excel khác có thiết kế chỉ nhằm tiến hành 1 chức năng rõ ràng, tuy nhiên hàm SUBTOTAL lại rất thiêng hoạt – hoàn toàn có thể tính tân oán hoặc làm phép súc tích nlỗi đếm số ô, tính vừa phải, tìm quý hiếm Khủng nhất/bé dại nhất…

Hàm SUBTOTAL có sinh hoạt tất cả các phiên bạn dạng Excel tự năm 2016 mang lại 2007 với cả phiên bản rẻ hơn.

Cú pháp hàm SUBTOTAL:



SUBTOTAL(function_num, ref1, ,…)

Trong đó:

Function_num: con số xác minh 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ụ. Cần phải gồm Ref 1, tự Ref 2 đến 254 là tuỳ chọn.

Số xác định công dụng triển khai bao gồm 2 loại sau:

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

quý khách hàng không cần thiết phải ghi nhớ không còn các số lượng chức năng. Ngay khi bạn nhập hàm SUBTOTAL Excel vào 1 ô hoặc trên tkhô giòn cách làm, Excel vẫn đưa ra list những con số cho mình.

ví dụ như, đó là biện pháp các bạn dùng phương pháp SUBTOTAL 9 để cộng tổng những quý hiếm trong ô từ C2 mang đến C8:

*

Để thêm 1 số khẳng định tính năng vào cách làm, dìm lưu ban con 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 vẫn nlỗi sau:

=SUBTOTAL(9,C2:C8)

Tương từ, chúng ta có thể viết bí quyết SUBTOTAL 1 nhằm tính vừa đủ, SUBTOTAL 2 nhằm đếm ô cất số, SUBTOTAL 3 nhằm đếm ô ko trống. Trong hình dưới, 3 tính năng khác đang được sử dụng.

*

Lưu ý:

lúc bạn dùng bí quyết SUBTOTAL Excel cùng với tính năng tính tổng như SUM tốt AVERAGE, Excel sẽ chỉ tính những ô cất số, bỏ qua ô trống với ô chứa cực hiếm không hẳn số.

Quý Khách đã biết phương pháp lập công thức SUBTOTAL vào Excel, thắc mắc đó là tại vì sao lại buộc phải học tập hàm này? Sao ko cần sử dụng các hàm đơn giản và dễ dàng, bình thường nlỗi SUM, COUNT, MAX? Lý vì chưng sẽ được trình diễn dưới đây.

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

So sánh cùng với những hàm Excel truyền thống lâu đời, SUBTOTAL cho chính mình đông đảo lợi thế sau:

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

Vì hàm Excel SUBTOTAL bỏ qua các mặt hàng đã làm được thanh lọc ra, chúng ta có thể sử dụng nó nhằm tính tổng tài liệu một cách linc hoạt, những quý hiếm vào tổng phụ được auto tính lại theo bộ lọc.

Ví dụ, trường hợp bọn họ thanh 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 kiểm soát và điều chỉnh để bỏ qua toàn vùng khác Khi tính tổng.

*

Lưu ý:

Vì những 2 bộ số xác minh công dụng (1-11 với 101-111) phần lớn bỏ lỡ các ô đã có lọc ra, chúng ta cũng có thể cần sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.

Xem thêm: Tiểu Sử Danh Hài Chiến Thắng, Nhan Sắc Vợ Mới Xinh Đẹp Của Danh Hài Chiến Thắng

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

Công thức SUBTOTAL với số xác minh công dụng 101-111 bỏ lỡ những ô vẫn ẩn, đã thanh lọc ra và ẩn bằng tay. Vì cầm cố, khi bạn sử dụng chức nă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 để vứt bỏ các quý hiếm từ các 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 hàng ô trong cách làm SUBTOTAL tất cả chứa bí quyết SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy phải con số vào bảng đang không hẳn tính 2 lần.

Trong hình dưới, cách làm tính mức độ vừa phải chính SUBTOTAL(1, C2:C10) bỏ qua mất công dụng của phương pháp SUBTOTAL trong ô C3 và 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, trắc trở bất nghĩa. Nhưng khi chúng ta cần sử dụng nó nhằm giải quyết các bước, các bạn sẽ nhận biết nhuần nhuyễn hàm SUBTOTAL ko cực nhọc. lấy ví dụ như dưới đây sẽ giúp chúng ta với một vài mẹo nhỏ tuổi khi sử dụng hàm SUBTOTAL.

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

Nhỏng các bạn vẫn biết, Excel SUBTOTAL đồng ý 2 cỗ số khẳng định tính năng 1-11 với 101-111. Cả 2 cỗ số đầy đủ bỏ lỡ các sản phẩm đã được thanh lọc ra, dẫu vậy 1-11 bao gồm những hàng được ẩn thủ công còn 101-111 thì loại bỏ. Để hiểu rõ rộng về điểm khác biệt này, xem ví dụ sau:

Với tổng các sản phẩm đã có được chọn, chúng ta có thể cần sử dụng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

*

Nhưng giả dụ có những hàng ko liên quan sẽ ẩn bằng tay thủ công bằng cách dùng lệnh Hide Rows vào Home tab > Cells group > Format > Hide và Unhide, cùng thừa nhận chuột yêu cầu vào các hàng, tiếp đến Hide, nhưng giờ chúng ta chỉ việc tổng giá trị trong những hàng nhận thấy, tuyển lựa SUBTOTAL 109:

*

Các số chức năng khác cũng tuân theo biện pháp tương tự như. ví dụ như, nhằm đếm ô không trống sẽ lựa chọn, cần sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 hoàn toàn có thể đếm các ô ko trống bắt gặp đúng mực nếu như bao gồm bất kì hàng ẩn như thế nào trong hàng.

*

Lưu ý: Hàm Excel SUBTOTAL với số công dụng 101-111 làm lơ giá trị vào mặt hàng bị ẩn, chưa phải cột bị ẩn. Ví dụ, nếu như khách hàng dùng phương pháp nlỗi SUBTOTAL(109, A1:E1) để tính tổng những số vào sản phẩm ngang, cột bị ẩn sẽ không tác động cho tổng phụ.

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

Nếu ai đang lập report tổng kết, cần được chỉ dẫn những dữ liệu tổng kết khác biệt cơ mà chúng ta không có khu vực trống cho tất cả số liệu, thì biện pháp sau rất có thể là giải pháp:

Trong 1 ô, chế tạo ra 1 list dạng thả xuống (drop-down) gồm đựng thương hiệu các hàm như Total, Max, Min…Trong ô kế ô list, thêm cách làm hàm IF lồng ghnghiền gồm cất bí quyết SUBTOTAL tương ứng với những hàm vào list.

lấy ví dụ, giả sử giá trị để tính tổng phú trong ô C12:C16, và danh sách trong ô A17 chứa Total, Average, Max, Min, bí quyết hàm SUBTOTAL đang nlỗi 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 cơ mà người sử dụng chọn vào list, bí quyết SUBTOTAL khớp ứng công thêm giá trị trong những sản phẩm được chọn.

*

Mẹo nhỏ:

Nếu đùng một phát danh sách cùng ô bí quyết biến mất ngoài bảng tính, thì bạn hãy lựa chọn lại bọn chúng vào 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à 1 Một trong những nguyên do sau đây

#VALUE! Số xác minh chức năng ko nằm trong tầm 1-11 hoặc 101-111 xuất xắc tất cả tsay mê chiếu (ref) là tsay đắm chiếu 3 chiều.

#DIV/0! Xảy ra khi 1 tổng rõ ràng đề nghị phân tách mang đến 0 (ví dụ: tính vừa đủ cùng hoặc độ lệch chuẩn của một dãy ô ko cất cực hiếm số)

#NAME? tên hàm SUBTOTAL sai chủ yếu tả.

Mẹo nhỏ:

Nếu các bạn không quen cùng với hàm SUBTOTAL, chúng ta có thể sử dụng những tính năng setup sẵn của hàm này và để công thức trường đoản cú triển khai xong giúp bạn hoặc theo dõi và quan sát đoạn Clip sau đây:

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

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

SUMIF, SUMIFS để tính tổng theo 1 ĐK, 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ò tìm kiếm tmê say chiếu Index+Match, hàm SUMPRODUCT…

Một số dụng cụ hay sử dụng như:

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

Rất nhiều kiến thức bắt buộc ko nào? Toàn cỗ rất nhiều kỹ năng và kiến thức này các bạn đa số có thể học được trong khóa học EX101 – Excel từ cơ phiên bản cho tới chuyên gia của Học Excel Online. Đây là khóa đào tạo giúp cho bạn khối hệ thống kỹ năng một bí quyết khá đầy đủ, chi tiết. Ngoài ra không thể tất cả số lượng giới hạn về thời gian học hành đề nghị chúng ta cũng có thể thoải mái và dễ chịu học tập bất kể cơ hội làm sao, dễ dãi tra cứu vớt lại kiến thức Khi buộc phải. Lúc này hệ thống đang sẵn có ưu tiên rất cao cho chính mình khi đăng ký tsi mê gia khóa đào tạo. Chi tiết xem tại: bigbiglands.com.Online

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *