Home / Tiện ích / hàm tính tổng của các tổng trong excel HÀM TÍNH TỔNG CỦA CÁC TỔNG TRONG EXCEL 12/10/2021 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 các bạn cách thực hiện công thức SUBTOTAL để cùng dữ liệu trong các ô quan sát thấy.Bạn đang xem: Hàm tính tổng của các tổng trong excelHÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNGHàm SUBTOTAL trong Excel có trách nhiệm để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường đúng theo này, “tổng phụ” (subtotal) không phải 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ể, cơ mà 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 xúc tích và ngắn gọn như đếm số ô, tính trung bình, tìm giá trị to nhất/nhỏ nhất…Hàm SUBTOTAL tất cả ở tất cả các phiên phiên bản Excel từ 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ệnRef1, Ref2, …: 1 hoặc các ô, hoặc hàng ô để tính tổng phụ. Cần được có Ref 1, trường đoản cú Ref 2 mang đến 254 là tuỳ chọn.Số xác định tính năng thực hiện tất cả 2 một số loại sau:1 -11 bỏ qua những ô đã có lọc ra, tuy vậy để lại các hàng được ẩn thủ công.101 – 111 bỏ qua các ô ẩn – vẫn lọc ra cùng ẩ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ống4104MAXTìm giá chỉ trị mập nhất5105MINTìm giá bán trị nhỏ tuổi nhất6106PRODUCTTính hiệu quả của những ô7107STDEVTính độ lệch chuẩn chỉnh mẫu dựa vào mẫu8108STDEVPTính độ lệch chuẩn chỉnh dựa trên toàn thể số9109SUMCộng các số10110VARƯớc tính độ dao động dựa bên trên mẫu11111VARPƯớc tính độ giao động dựa trên toàn bộ sốBạn không nhất 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 trên thanh công thức, Excel sẽ đưa ra danh sách những con số cho bạn.Ví dụ, đấy là cách bạn dùng phương pháp SUBTOTAL 9 để cùng tổng những giá trị trong ô trường đoản cú C2 đến C8:Để thêm 1 số xác định tác dụng vào công thức, thừa nhận đúp chuột, ghi lại 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ự, chúng ta có thể viết bí quyết 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 công dụng khác đang được dùng.Lưu ý:Khi chúng ta dùng bí quyết SUBTOTAL Excel với công dụng tính tổng như SUM giỏi AVERAGE, Excel vẫn chỉ tính những ô chứa số, bỏ qua ô trống cùng ô cất giá trị không phải số.Bạn đã hiểu cách thức lập công thức SUBTOTAL vào Excel, câu hỏi chính là vì sao lại đề xuất học hàm này? Sao không dùng các hàm solo giản, bình thường như SUM, COUNT, MAX? vì sao sẽ được trình bày dưới đây.3 LÝ do ĐỂ DÙNG HÀM SUBTOTALSo sánh với các hàm Excel truyền thống, SUBTOTAL cho chính mình những điểm mạnh sau:Tính giá chỉ trị trong các hàng được chọnVì hàm Excel SUBTOTAL vứt qua các hàng đã được lọc ra, chúng ta có thể sử dụng nó nhằm tính tổng dữ liệu một cách linh hoạt, các giá trị trong tổng phụ được tự động 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, phương pháp SUBTOTAL sẽ tự động điều chỉnh để vứt qua tất cả vùng khác khi 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 những ô đã được lọc ra, bạn có thể dùng bí quyết SUBTOTAL 9 hoặc SUBTOTAL 109.Xem thêm: Tổng Hợp Hình Ảnh Người Đẹp Và Xe Moto Cực Chất, Ảnh Người Đẹp Và Xe Mô Tô Gợi Cảm Đầy Quyến RũTính các ô chú ý thấyCông thức SUBTOTAL cùng với số xác định chức năng 101-111 quăng quật qua những ô đang ẩn, đã lọc ra cùng ẩn thủ công. Vày thế, khi bạn sử dụng công dụ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 giá trị từ đa số hàng ẩn ngoài tổng phụ.Bỏ qua quý giá trong bí quyết SUBTOTAL lồng ghépNếu hàng ô trong công thức SUBTOTAL bao gồm chứa phương pháp SUBTOTAL khác, bí quyết SUBTOTAL được lồng vào có khả năng 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, cách làm tính trung bình chính SUBTOTAL(1, C2:C10) vứt qua tác dụng của cách làm SUBTOTAL trong ô C3 với C10.VÍ DỤ DÙNG SUBTOTAL vào EXCELKhi chúng ta 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ó để giải quyết và xử lý công việc, các bạn sẽ nhận ra thuần thục hàm SUBTOTAL không khó. Ví dụ như sau đây sẽ giúp đỡ bạn với một trong những mẹo bé dại khi cần sử dụng hàm SUBTOTAL.VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109Như chúng ta đã biết, Excel SUBTOTAL chấp nhận 2 bộ số xác định tính năng 1-11 cùng 101-111. Cả hai bộ số đều quăng quật qua các hàng đã làm được lọc ra, tuy thế 1-11 bao gồm các mặt hàng được ẩn thủ công còn 101-111 thì nhiều loại trừ. Để là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 các hàng đã làm được chọn, chúng ta cũng có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:Nhưng nếu như có những hàng không liên quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide và Unhide, cùng nhấn chuột đề nghị vào các hàng, kế tiếp Hide, mà lại giờ bạn chỉ cần tổng giá trị trong các hàng quan sát 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, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 rất có thể đếm các ô không trống nhìn thấy đúng chuẩn nếu có bất cứ hàng ẩn như thế nào trong dãy.Lưu ý: Hàm Excel SUBTOTAL cùng với số tác dụng 101-111 bỏ qua mất giá trị trong hàng bị ẩn, không hẳn cột bị ẩn. Ví dụ, nếu như bạn dùng công thức như SUBTOTAL(109, A1:E1) nhằm tính tổng các số trong sản phẩm ngang, cột bị ẩn đã không tác động đến tổng phụ.VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆUNếu bạn đang lập report tổng kết, rấ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 tất cả số liệu, thì phương pháp sau rất có thể là giải pháp:Trong 1 ô, sản xuất 1 list dạng thả xuống (drop-down) tất cả chứa tên các hàm như Total, Max, Min…Trong ô kế ô danh sách, thêm bí quyết hàm IF lồng ghép có chứa công thức SUBTOTAL tương ứng với những hàm trong danh sách.Ví dụ, mang sử quý hiếm để tính tổng phụ vào ô C12:C16, và list trong ô A17 đựng Total, Average, Max, Min, bí quyết hàm SUBTOTAL đang 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, bí quyết SUBTOTAL tương ứng sẽ tính giá bán trị trong các hàng được chọn.Mẹo nhỏ:Nếu đùng một phát danh sách và ô công thức mất tích khỏi bảng tính, thì bạn hãy 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ẶPNếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân rất có thể là giữa những lý bởi sau đây#VALUE! Số xác định tính năng không nằm trong tầm 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 rõ ràng 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 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 bạn chưa quen với hàm SUBTOTAL, chúng ta có thể dùng những chức năng setup sẵn của hàm này cùng để bí quyết tự hoàn thiện giúp đỡ bạn hoặc theo dõi đoạn clip sau đây:Ngoài ra để áp dụng Excel vào công việc một cách kết quả thì bạn còn phải sử dụng xuất sắc các hàm, các công cố kỉnh khác của Excel.Một số hàm cơ bạn dạng thường chạm chán như:SUMIF, SUMIFS nhằm tính tổng theo 1 điểm kiện, nhiều điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, những đ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ụ thường dùng như:Định dạng theo đk với Conditional formattingThiết lập điều kiện nhập dữ liệu với Data ValidationCách để Name và sử dụng Name vào công thứcLập report với Pivot Table…Rất nhiều kiến thức phải không nào? toàn cục những kỹ năng này các bạn đều rất có thể học được trong khóa học EX101 – Excel trường đoản cú cơ bạn dạng tới chăm gia của học tập Excel Online. Đây là khóa học khiến cho bạn hệ thống kiến thức một cách đầy đủ, đưa ra tiết. Hơn nữa không hề có số lượng 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 vãn lại kỹ năng khi cần. Bây chừ hệ thống đang có ưu đãi vô cùng lớn cho bạn khi đk tham gia khóa học. Chi tiết xem tại: kftvietnam.com.Online