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

Bạn đang xem: Cách dùng hàm subtotal trong excel 2010


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

Hàm SUBTOTAL trong Excel có trọng trách để tính tổng phụ trong danh sách hoặc các đại lý dữ liệu. Trong trường đúng theo 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 tính năng cụ thể, tuy vậy hàm SUBTOTAL lại linh thiêng hoạt – hoàn toàn có thể tính toán hoặc làm phép xúc tích như đếm số ô, tính trung bình, tìm giá trị béo nhất/nhỏ nhất…

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

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô nhằm tính tổng phụ. Rất cần được có Ref 1, tự Ref 2 cho 254 là tuỳ chọn.



Số xác định công dụng thực hiện có 2 các loại sau:

1 -11 bỏ qua những ô đã được lọc ra, nhưng để lại những hàng được ẩn thủ công.101 – 111 bỏ qua các ô ẩn – đã lọc ra với ẩn thủ công.
Function_numChức năngMô tả
1101AVERAGETính trung bình những con số
2102COUNTĐếm số ô đựng giá trị số
3103COUNTAĐếm số ô ko trống
4104MAXTìm giá bán trị to nhất
5105MINTìm giá trị nhỏ nhất
6106PRODUCTTính công dụng của các ô
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 những số
10110VARƯớc tính độ xê dịch dựa bên trên mẫu
11111VARPƯớc tính độ dao động dựa trên tổng thể số

Bạn không cần 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 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ụ, đó là cách các bạn dùng công thức SUBTOTAL 9 để cùng tổng những giá trị vào ô trường đoản cú C2 cho C8:

*

Để thêm 1 số xác định tính năng vào công thức, thừa nhận đúp chuột, khắc ghi phẩy, xác minh dãy ô, đóng góp ngoặc và nhấn Enter. Công thức hoàn hảo sẽ như sau:

=SUBTOTAL(9,C2:C8)

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

*

Lưu ý:

Khi bạn dùng phương pháp SUBTOTAL Excel với tính năng tính tổng như SUM tốt AVERAGE, Excel đã chỉ tính các ô chứa số, bỏ qua mất ô trống cùng ô chứa giá trị chưa hẳn số.

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

3 LÝ vì chưng ĐỂ 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 ưu thế sau:

Tính giá trị 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ũng có thể sử dụng nó để tính tổng dữ liệu một 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 hàng chỉ của vùng Miền Đông, bí quyết SUBTOTAL sẽ auto điều chỉnh để bỏ qua tất cả khu vực khác lúc tính tổng.

*

Lưu ý:

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

Xem thêm: Miêu Tả Tết Trung Thu Bằng Tiếng Anh Về Lễ Hội Trung Thu Bằng Tiếng Anh

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 quăng quật qua những ô đang ẩn, sẽ lọc ra cùng ẩn thủ công. Bởi thế, khi bạn sử dụng tính năng Excel’s Hide để ẩn tài liệu không liên quan, dùng số tác dụng 101-111 để loại trừ các quý giá từ đông đảo hàng ẩn khỏi tổng phụ.

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

Nếu dãy ô trong công thức SUBTOTAL có chứa cách làm SUBTOTAL khác, phương pháp 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, phương pháp tính trung bình chủ yếu SUBTOTAL(1, C2:C10) vứt qua hiệu quả của phương pháp SUBTOTAL vào ô C3 và C10.

*

VÍ DỤ DÙNG SUBTOTAL trong EXCEL

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

Với tổng những hàng đã được chọn, bạn cũng có thể dùng cả SUBTOTAL 9 và 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 cách dùng lệnh Hide Rows vào Home tab > Cells group > Format > Hide và Unhide, cùng nhấn chuột đề xuất vào những hàng, tiếp đến Hide, nhưng giờ bạn chỉ cần tổng giá bán trị trong các hàng nhìn thấy, chắt lọc SUBTOTAL 109:

*

Các số công dụng khác cũng tuân theo cách tương tự. Ví dụ, nhằm đếm ô ko trống đang chọn, cần sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên 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 trong dãy.

*

Lưu ý: Hàm Excel SUBTOTAL cùng với số chức năng 101-111 bỏ lỡ giá trị trong sản phẩm 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 sẽ 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, rất cần phải đưa ra những dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho tất cả số liệu, thì phương pháp sau có thể là giải pháp:

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

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

*

Mẹo nhỏ:

Nếu đùng một phát danh sách cùng ô công thức mất tích khỏi bảng tính, thì bạn nên chọn lựa 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 nhân có thể là một trong những lý vị 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 gồm tham chiếu (ref) là tham chiếu 3D.

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

#NAME? thương hiệu hàm SUBTOTAL sai chính tả.

Mẹo nhỏ:

Nếu các bạn chưa quen thuộc với hàm SUBTOTAL, bạn cũng có thể dùng những chức năng setup sẵn của hàm này cùng để công thức 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ông việc một cách công dụng thì bạn còn đề nghị sử dụng xuất sắc các hàm, các công chũm 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 một điểm kiện, nhiều điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, nhiều điều kiệnCác hàm xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tìm kiếm tham chiếu Index+Match, hàm SUMPRODUCT…

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

Định dạng theo đk với Conditional formattingThiết lập đk nhập dữ liệu với Data ValidationCách đặt Name và thực hiện Name trong công thứcLập report với Pivot Table…

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