HÀM SUBTOTAL TRONG EXCEL 2007

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

Bạn đang xem: Hàm subtotal trong excel 2007


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 list hoặc đại lý dữ liệu. Vào trường thích hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong một dãy ô xác định. Các hàm Excel khác được thiết kế với chỉ để tiến hành 1 chức năng cụ thể, nhưng lại hàm SUBTOTAL lại rất linh thiêng hoạt – rất có thể tính toán hoặc làm cho phép lô ghích như đếm số ô, tính trung bình, tìm giá bán trị phệ nhất/nhỏ nhất…

Hàm SUBTOTAL có ở tất cả các phiên phiên bản Excel từ năm nhâm thìn đế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 chức năng thực hiện

Ref1, Ref2, …: 1 hoặc các ô, hoặc hàng ô để tính tổng phụ. Rất cần phải có Ref 1, trường đoản cú Ref 2 mang đến 254 là tuỳ chọn.



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

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

Bạn không cần thiết phải nhớ hết những 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ố mang lại bạn.

Ví dụ, đây là cách bạn dùng bí quyết SUBTOTAL 9 để cùng tổng các giá trị trong ô tự C2 đến C8:

*

Để thêm 1 số xác định tính năng vào công thức, nhấn đúp chuột, khắc ghi phẩy, xác định dãy ô, đóng góp 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 cách làm SUBTOTAL 1 nhằm tính trung bình, SUBTOTAL 2 để đếm ô cất số, SUBTOTAL 3 nhằm đếm ô không trống. Vào hình dưới, 3 công dụng khác đang rất được dùng.

*

Lưu ý:

Khi chúng ta dùng cách làm SUBTOTAL Excel với tác dụng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống cùng ô đựng giá trị chưa phải số.

Bạn đã hiểu cách thức lập công thức SUBTOTAL trong Excel, thắc mắc chính là nguyên nhân lại cần học hàm này? Sao không dùng những hàm đơn giản, thông thường như SUM, COUNT, MAX? lý do sẽ được trình bày 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 điểm mạnh sau:

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

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã làm được lọc ra, bạn cũng có thể sử dụng nó để tính tổng dữ liệu một cách linh hoạt, các giá trị vào tổng phụ được tự động tính lại theo bộ 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, bí quyết SUBTOTAL sẽ auto điều chỉnh để quăng quật qua toàn khu vực khác khi tính tổng.

*

Lưu ý:

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

Xem thêm: Trường Trung Cấp Nghề Việt Hàn Bình Dương, Trung Cấp Việt Hàn: Trang Chủ

Tính những ô chú ý thấy

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

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

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

Trong hình dưới, phương pháp tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua hiệu quả của công thức SUBTOTAL trong ô C3 với C10.

*

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

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

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

*

Nhưng ví như có những hàng không tương quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows vào Home tab > Cells group > Format > Hide & Unhide, với nhấn chuột đề nghị vào những hàng, tiếp nối Hide, mà lại giờ bạn chỉ cần tổng giá bán trị trong những hàng chú ý thấy, chắt lọc SUBTOTAL 109:

*

Các số công dụng khác cũng làm theo cách tương tự. Ví dụ, để đếm ô không trống sẽ chọn, cần sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Mà lại SUBTOTAL 103 hoàn toàn có thể đếm các ô ko trống chú ý thấy chính xác nếu có bất kỳ hàng ẩn như thế nào trong dãy.

*

Lưu ý: Hàm Excel SUBTOTAL cùng với số công dụng 101-111 bỏ qua giá trị trong sản phẩm bị ẩn, chưa 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 các số trong mặt hàng ngang, cột bị ẩn vẫn không ảnh hưởng đế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, 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 tồn tại chỗ trống cho toàn bộ số liệu, thì cách sau rất có thể là giải pháp:

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

Ví dụ, đưa sử quý hiếm để tính tổng phụ trong ô C12:C16, và list trong ô A17 đựng Total, Average, Max, Min, cách làm 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 sử dụng chọn trong danh sách, bí quyết 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 và ô công thức biến mất khỏi bảng tính, thì bạn hãy lựa chọn lại 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 hoàn toàn có thể là một trong những lý vì sau đây

#VALUE! Số xác định công dụ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 rõ ràng phải phân tách 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 chính tả.

Mẹo nhỏ:

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

Ngoài ra để vận dụng Excel vào các bước một cách tác dụng thì chúng ta còn cần sử dụng giỏi các hàm, những công nắm khác của Excel.

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

SUMIF, SUMIFS nhằm tính tổng theo một điểm kiện, những điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, các đ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ò tra cứu tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ thường 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à sử 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 và 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 tự cơ bản tới siêng gia của học tập 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 bí quyết đầy đủ, chi tiết. Rộng 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 kể lúc nào, tiện lợi tra cứu lại kiến thức và kỹ năng khi cần. Hiện thời hệ thống đang sẵn có ưu đãi cực kỳ lớn cho mình khi đk tham gia khóa học. Cụ thể xem tại: hctv.com.vn.Online