Cách sử dụng lệnh subtotal trong excel

Trong nội dung bài viết này, Excel Online vẫn giải thích cụ thể hàm SUBTOTAL và khuyên bảo các bạn giải pháp áp dụng cách làm SUBTOTAL để cùng tài liệu trong số ô nhận thấy.

Bạn đang xem: Cách sử dụng lệnh subtotal trong excel

HÀM SUBTOTAL – CÚ PHÁPhường VÀ CÁCH SỬ DỤNG

Hàm SUBTOTAL vào Excel tất cả trách nhiệm nhằm tính tổng prúc vào list hoặc đại lý tài liệu. Trong trường hòa hợp này, “tổng phụ” (subtotal) chưa hẳn tổng các số lượng trong một hàng ô xác định. Các hàm Excel khác được thiết kế với chỉ nhằm thực hiện 1 tác dụng rõ ràng, nhưng mà hàm SUBTOTAL lại linh thiêng hoạt – rất có thể tính toán hoặc làm phép ngắn gọn xúc tích nhỏng đếm số ô, tính mức độ vừa phải, search cực hiếm mập nhất/nhỏ nhất…

Hàm SUBTOTAL tất cả làm việc tất cả những phiên bản Excel từ năm 2016 cho 2007 cùng cả phiên phiên bản tốt rộng.



Cú pháp hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, ,…)

Trong đó:

Function_num: số lượng xác minh công dụng thực hiện

Ref1, Ref2, …: 1 hoặc các ô, hoặc dãy ô nhằm tính tổng phú. Cần buộc phải có Ref 1, từ bỏ Ref 2 đến 254 là tuỳ chọn.

Số khẳng định công dụng tiến hành gồm 2 loại sau:

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

 

quý khách hàng không cần thiết phải ghi nhớ hết những con số chức năng. Ngay khi chúng ta nhập hàm SUBTOTAL vào 1 ô hoặc trên tkhô nóng bí quyết, Excel đã đưa ra danh sách các con số cho chính mình.

lấy ví dụ, đó là cách bạn sử dụng phương pháp SUBTOTAL 9 để cùng tổng những quý giá trong ô từ C2 đến C8:

*

Để thêm một số xác minh chức năng vào công thức, dấn lưu ban chuột, khắc ghi phẩy, xác minh dãy ô, đóng góp ngoặc với nhấn Enter. Công thức hoàn chỉnh vẫn nlỗi sau:

=SUBTOTAL(9,C2:C8)

Tương trường đoản cú, chúng ta cũng có thể viết công thức SUBTOTAL 1 nhằm tính vừa đủ, SUBTOTAL 2 nhằm đếm ô chứa số, SUBTOTAL 3 nhằm đếm ô ko trống. Trong hình bên dưới, 3 công dụng không giống đang rất được dùng.

*

Lưu ý:

lúc chúng ta dùng cách làm SUBTOTAL với công dụng tính tổng nhỏng SUM tuyệt AVERAGE, Excel đang chỉ tính những ô chứa số, bỏ qua mất ô trống và ô đựng cực hiếm không hẳn số.

Quý Khách đã hiểu phương pháp lập cách làm SUBTOTAL vào Excel, câu hỏi chính là tại sao lại nên học hàm này? Sao ko sử dụng các hàm đơn giản dễ dàng, bình thường nhỏng SUM, COUNT, MAX? Lý vày sẽ được trình bày 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 cuội nguồn, SUBTOTAL cho bạn số đông ưu thế sau:

Tính quý hiếm trong các hàng được chọn

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã có được lọc ra, chúng ta có thể áp dụng nó để tính tổng tài 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.

lấy ví dụ như, nếu họ lọc bảng doanh thu bán sản phẩm chỉ của vùng Miền Đông, công thức SUBTOTAL đang tự động hóa kiểm soát và điều chỉnh nhằm bỏ lỡ toàn vùng không giống lúc tính tổng.

*

Lưu ý:

Vì các 2 bộ số xác minh tác dụng (1-11 cùng 101-111) hầu hết bỏ qua mất những ô đã được lọc ra, bạn có thể dùng cách làm SUBTOTAL 9 hoặc SUBTOTAL 109.

Xem thêm: Cách Sử Dụng Picture Collage Maker Pro Full, Picture Collage Maker Pro Full

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

Công thức SUBTOTAL cùng với số xác định tác dụng 101-111 làm lơ các ô sẽ ẩn, đã lọc ra với ẩn thủ công. Vì nỗ lực, khi bạn sử dụng tác dụng Excel’s Hide nhằm ẩn dữ liệu ko liên quan, sử dụng số tác dụng 101-111 nhằm thải trừ những quý hiếm trường đoản cú rất nhiều sản phẩm ẩn khỏi tổng prúc.

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

Nếu hàng ô trong bí quyết SUBTOTAL tất cả cất bí quyết SUBTOTAL khác, bí quyết SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy cần số lượng vào bảng đang không hẳn tính gấp đôi.

Trong hình dưới, phương pháp tính vừa đủ thiết yếu SUBTOTAL(1, C2:C10) bỏ qua mất kết quả của công thức SUBTOTAL trong ô C3 cùng C10.

*

VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL

lúc các bạn new biết hàm SUBTOTAL, rất có thể thấy hàm này tinh vi, rắc rối vô nghĩa. Nhưng khi chúng ta sử dụng nó để giải quyết và xử lý quá trình, bạn sẽ nhận ra nhuần nhuyễn hàm SUBTOTAL không khó. Ví dụ sau đây sẽ giúp đỡ bạn với một vài mẹo nhỏ khi sử dụng hàm SUBTOTAL.

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

Như chúng ta đang biết, Excel SUBTOTAL đồng ý 2 bộ số xác minh công dụng 1-11 với 101-111. Cả 2 cỗ số hầu như bỏ qua các mặt hàng đã được thanh lọc ra, mà lại 1-11 bao gồm các mặt hàng được ẩn thủ công còn 101-111 thì vứt bỏ. Để làm rõ hơn 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 lựa chọn, chúng ta cũng có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

*

Nhưng trường hợp tất cả những hàng không tương quan đang ẩn thủ công bằng tay bằng phương pháp cần sử dụng lệnh Hide Rows vào trang chủ tab > Cells group > Format > Hide và Unhide, và nhấn loài chuột buộc phải vào các sản phẩm, tiếp nối Hide, mà giờ bạn chỉ cần tổng giá trị trong các sản phẩm nhận thấy, chọn lọc SUBTOTAL 109:

*

Các số chức năng không giống cũng làm theo cách giống như. Ví dụ, để đếm ô không trống sẽ chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm các ô ko trống nhận thấy đúng chuẩn nếu có bất kỳ hàng ẩn nào vào hàng.

*

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 bỏ qua giá trị trong mặt hàng bị ẩn, chưa phải cột bị ẩn. Ví dụ, nếu bạn cần sử dụng công thức nhỏng SUBTOTAL(109, A1:E1) nhằm tính tổng các số vào sản phẩm ngang, cột bị ẩn sẽ không còn ảnh hưởng mang đến tổng phụ.

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

Nếu bạn đang lập report tổng kết, rất cần phải chỉ dẫn các dữ liệu tổng kết khác nhau dẫu vậy các bạn không có khu vực trống đến tất cả số liệu, thì giải pháp sau có thể là giải pháp:

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

lấy ví dụ như, mang sử quý hiếm để tính tổng phụ trong ô C12:C16, cùng danh sách trong ô A17 cất Total, Average, Max, Min, phương pháp hàm SUBTOTAL đã 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 sau, tuỳ vào hàm cơ mà người tiêu dùng lựa chọn trong list, công thức SUBTOTAL tương xứng sẽ tính quý hiếm trong số sản phẩm được lựa chọn.

*

Mẹo nhỏ:

Nếu đùng một cái list với ô cách làm biến mất ngoài bảng tính, thì các bạn nên lựa chọn lại 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 ổn nhân có thể là một trong những Một trong những nguyên do sau đây

#VALUE! Số khẳng định chức năng không nằm trong vòng 1-11 hoặc 101-111 giỏi có tham mê chiếu (ref) là tđam mê chiếu 3 chiều.

#DIV/0! Xảy ra lúc 1 tổng ví dụ nên chia mang lại 0 (ví dụ: tính vừa đủ cùng hoặc độ lệch chuẩn của một dãy ô ko chứa quý giá số)

#NAME? tên hàm SUBTOTAL không nên chính tả.

Xem thêm: 1 Click Tạo Boot Ổ Cứng Di Dộng Với Phân Vùng Ẩn Hỗ Trợ Uefi

Mẹo nhỏ:

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

Dường như nhằm ứng dụng Excel vào công việc một giải pháp kết quả thì chúng ta còn đề xuất áp dụng tốt những hàm, các điều khoản khác của Excel.

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

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

Một số lý lẽ hay đượ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 dữ liệu với Data ValidationCách đặt Name với thực hiện Name trong công thứcLập report cùng với Pivot Table…

Rất nhiều kiến thức và kỹ năng phải không nào? Toàn cỗ đều kiến thức này các bạn hồ hết hoàn toàn có thể học được vào khóa học EX101 – Excel tự cơ phiên bản tới chuyên gia của Học Excel Online. Đây là khóa đào tạo và huấn luyện giúp cho bạn khối hệ thống kiến thức và kỹ năng một phương pháp đầy đủ, cụ thể. Hơn nữa không hề có số lượng giới hạn về thời gian tiếp thu kiến thức phải chúng ta cũng có thể dễ chịu học tập bất cứ cơ hội nào, dễ dàng tra cứu vớt lại kiến thức và kỹ năng Lúc yêu cầu. Lúc này hệ thống đang sẵn có khuyến mãi không hề nhỏ cho chính mình khi đăng ký tsi mê gia khóa học. Chi máu coi tại: mammasfigata.com.Online


Chuyên mục: Kiến thức