Sử dụng hàm subtotal có điều kiện

Trong bài viết này, Excel Online vẫn phân tích và lý giải cụ thể hàm SUBTOTAL với gợi ý các bạn bí quyết áp dụng bí quyết SUBTOTAL nhằm cộng tài liệu trong số ô thấy được.

You watching: Sử dụng hàm subtotal có điều kiện


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

Hàm SUBTOTAL vào Excel bao gồm nhiệm vụ để tính tổng phú trong danh sách hoặc cửa hàng dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) chưa hẳn tổng những con số trong một hàng ô xác minh. Các hàm Excel khác có phong cách thiết kế chỉ để triển khai 1 tính năng rõ ràng, tuy nhiên hàm SUBTOTAL lại linh thiêng hoạt – có thể tính toán hoặc lấy lệ xúc tích nhỏng đếm số ô, tính trung bình, tìm kiếm quý giá lớn nhất/nhỏ tuổi nhất…

Hàm SUBTOTAL gồm sống tất cả những phiên bản Excel trường đoản cú 2016 đến 2007 và cả phiên bạn dạng phải chăng rộng.

Cú pháp hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, ,…)

Trong đó:

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

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc hàng ô để tính tổng prúc. Cần đề xuất gồm Ref 1, trường đoản cú Ref 2 đến 254 là tuỳ lựa chọn.



Số xác minh chức năng tiến hành bao gồm 2 loại sau:

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

quý khách 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 chúng ta nhập hàm SUBTOTAL Excel vào 1 ô hoặc trên tkhô cứng phương pháp, Excel sẽ giới thiệu list các số lượng cho bạn.

Ví dụ, đấy là bí quyết bạn sử dụng công thức SUBTOTAL 9 nhằm cùng tổng các quý giá trong ô trường đoản cú C2 mang lại C8:

*

Để thêm 1 số xác minh tính năng vào phương pháp, thừa nhận lưu ban loài chuột, lưu lại phẩy, xác minh dãy ô, đóng góp ngoặc với thừa nhận Enter. Công thức hoàn hảo vẫn nlỗi sau:

=SUBTOTAL(9,C2:C8)

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

*

Lưu ý:

Khi chúng ta dùng bí quyết SUBTOTAL Excel cùng với chức năng tính tổng nhỏng SUM tốt AVERAGE, Excel đã chỉ tính các ô đựng số, bỏ qua ô trống và ô cất giá trị không hẳn số.

quý khách hàng đã hiểu phương pháp lập bí quyết SUBTOTAL vào Excel, thắc mắc đó là tại vì sao lại nên học tập hàm này? Sao không sử dụng các hàm dễ dàng, thông thường như SUM, COUNT, MAX? Lý vày sẽ được trình diễn dưới đây.

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

So sánh với các hàm Excel truyền thống cuội nguồn, SUBTOTAL cho chính mình hầu hết ưu thế sau:

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

Vì hàm Excel SUBTOTAL bỏ qua mất các hàng đã làm được thanh lọc ra, bạn cũng có thể sử dụng nó nhằm tính tổng tài liệu một cách linh hoạt, những cực hiếm trong tổng phú được tự động hóa tính lại theo cỗ lọc.

lấy ví dụ như, ví như chúng ta thanh lọc bảng doanh số bán sản phẩm chỉ của vùng Miền Đông, công thức SUBTOTAL đang auto điều chỉnh nhằm làm lơ tất cả vùng khác Khi tính tổng.

*

Lưu ý:

Vì các 2 cỗ số xác định tính năng (1-11 cùng 101-111) hầu như bỏ qua mất những ô đã có thanh lọc ra, chúng ta có thể sử dụng cách làm SUBTOTAL 9 hoặc SUBTOTAL 109.

See more: Hướng Dẫn Cắt Hình Ảnh Trong Photoshop Cực Dễ, Có Video Hướng Dẫn

Tính các ô nhìn thấy

Công thức SUBTOTAL với số xác minh công dụng 101-111 bỏ qua mất các ô vẫn ẩn, sẽ lọc ra cùng ẩn bằng tay thủ công. Vì cầm cố, khi bạn áp dụng tác dụng Excel’s Hide nhằm ẩn tài liệu không tương quan, sử dụng số tác dụng 101-111 để loại trừ những giá trị trường đoản cú mọi sản phẩm ẩn ngoài tổng prúc.

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

Nếu dãy ô vào công thức SUBTOTAL tất cả chứa cách làm SUBTOTAL không giống, bí quyết SUBTOTAL được lồng vào sẽ ảnh hưởng bỏ qua. Vậy bắt buộc con số vào bảng đang không phải tính 2 lần.

Trong hình dưới, cách làm tính mức độ vừa phải thiết yếu SUBTOTAL(1, C2:C10) bỏ lỡ công dụng của bí quyết SUBTOTAL vào ô C3 với C10.

*

VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL

khi các bạn new biết hàm SUBTOTAL, hoàn toàn có thể thấy hàm này phức hợp, vấn đề vô nghĩa. Nhưng khi bạn dùng nó để giải quyết các bước, các bạn sẽ nhận ra thuần thục hàm SUBTOTAL ko khó. ví dụ như sau đây sẽ giúp các bạn với một trong những mẹo nhỏ dại khi sử dụng hàm SUBTOTAL.

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

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

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

*

Nhưng ví như có các sản phẩm không tương quan đã ẩn bằng tay thủ công bằng cách cần sử dụng lệnh Hide Rows trong trang chủ tab > Cells group > Format > Hide & Unhide, và dìm chuột buộc phải vào các sản phẩm, sau đó Hide, nhưng giờ bạn chỉ việc tổng mức trong số hàng nhìn thấy, gạn lọc SUBTOTAL 109:

*

Các số tác dụng không giống cũng tuân theo phương pháp tương tự. lấy ví dụ, nhằm đếm ô không trống đang lựa chọn, cần sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 rất có thể đếm những ô ko trống nhận thấy đúng đắn giả dụ tất cả bất cứ sản phẩm ẩn như thế nào vào dãy.

*

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 làm lơ quý giá vào sản phẩm bị ẩn, không phải cột bị ẩn. lấy ví dụ như, nếu như bạn cần sử dụng cách làm nlỗi SUBTOTAL(109, A1:E1) nhằm tính tổng những số vào mặt hàng ngang, cột bị ẩn sẽ không còn tác động mang đến tổng phụ.

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

Nếu nhiều người đang lập report tổng kết, rất cần được giới thiệu những tài liệu tổng kết không giống nhau tuy thế các bạn không có chỗ trống đến tất cả số liệu, thì biện pháp sau có thể là giải pháp:

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

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

*

Mẹo nhỏ:

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

#VALUE! Số xác minh tính năng ko ở trong tầm 1-11 hoặc 101-111 tốt gồm tsay mê chiếu (ref) là tmê mẩn chiếu 3D.

#DIV/0! Xảy ra lúc một tổng ví dụ buộc phải phân tách đến 0 (ví dụ: tính vừa phải cộng hoặc độ lệch chuẩn chỉnh của 1 dãy ô không đựng quý giá số)

#NAME? thương hiệu hàm SUBTOTAL không đúng chủ yếu tả.

See more: Phần Mềm Hwinfo64 Là Gì - Xem Thông Tin Phần Mềm Hwinfo32 Là Gì

Mẹo nhỏ:

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

Bên cạnh đó để vận dụng Excel vào quá trình một giải pháp công dụng thì bạn còn đề xuất sử dụng tốt những hàm, các cơ chế khác của Excel.

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

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

Một số hình thức thường được sử dụng như:

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

Rất các kiến thức yêu cầu ko nào? Toàn cỗ đa số kiến thức và kỹ năng này chúng ta những có thể học được trong khóa học EX101 – Excel tự cơ bạn dạng cho tới chăm gia của Học Excel Online. Đây là khóa đào tạo và huấn luyện giúp cho bạn hệ thống kiến thức và kỹ năng một biện pháp không thiếu thốn, cụ thể. ngoại giả không còn tất cả giới hạn về thời hạn học hành đề nghị bạn có thể thoải mái học tập bất kể lúc như thế nào, dễ dàng tra cứu giúp lại kiến thức Lúc bắt buộc. Trong thời điểm này hệ thống đang xuất hiện chiết khấu rất lớn cho bạn lúc đăng ký tmê man gia khóa học. Chi huyết xem tại: bepgasvuson.vn.Online