Hàm Subtotal là gì và công thức, cách sử dụng hàm Subtotal trong Excel như thế nào. Hãy cùng chúng tôi tìm hiểu ngay dưới đây nhé.
Trong Excel có rất nhiều hàm dùng để tính tổng và hàm Subtotal là một số trong đó. Tuy nhiên, hàm Subtotal khác với hàm SUM thông thường. Để hiểu rõ hơn mời bạn truy cập ngay David Lowe.
Hàm Subtotal là gì?
Hiện nay có nhiều hàm tính tổng như hàm SUM, mặc dù không nhất thiết bạn phải sử dụng hàm Subtotal. Tuy nhiên, nó giúp cho việc phân tích dữ liệu được nhắm mục tiêu dễ dàng hơn rất nhiều. Bạn có thể chèn công thức Subtotal vào một dải ô nào đó mà không ảnh hưởng đến tổng số chung vì Subtotal bỏ qua các ô khác có chứa công thức Subtotal.
Subtotal hoạt động giống với các tính năng Excel khác, chẳng hạn như lọc ô. Nếu bạn lọc một bảng bằng một giá trị, công thức Subtotal sẽ nhận dạng bộ lọc và loại trừ các ô tương ứng. Subtotal áp dụng cũng hữu ích để bỏ qua các giá trị ẩn, điều mà những hàm khác (như SUM) không thể làm được.
Công thức Subtotal trong Excel
Hàm Subtotal được tính bằng cách tính toán tổng giá trị của một phạm vi ô, dựa trên một hàm toán học khác. Subtotal sử dụng tới 11 hàm toán học khác nhau (từ AVERAGE đến VAR.P) để tìm tổng giá trị phù hợp, đồng thời làm việc trong các tham số của hàm (ví dụ, bỏ qua các công thức Subtotal khác).
Hàm SUBTOTAL có cú pháp = SUBTOTAL(function_num, ref1, [ref2],…).
Trong đó:
- Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
- Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.
Lưu ý:
- Hàm Subtotal để tính toán cho các cột số liệu theo chiều dọc.
- Nếu các đối số ref1, ref2,… có chứa hàm Subtotal thì sẽ được bỏ qua để tránh tính trùng 2 lần.
- Nếu function_num từ 1 đến 11 thì hàm Subtotal được tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm Subtotal chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
- Đối với vùng dữ liệu bị ẩn do Filter, Subtotal sẽ bỏ qua.
Cách sử dụng hàm Subtotal trong Excel
Dưới đây, chúng tôi sẽ hướng dẫn bạn cách sử dụng hàm Subtotal trong Excel. Hãy cùng theo dõi ngay dưới đây tiếp nhé.
Tính tổng các hàng được lọc
Về cơ bản, hàm Subtotal được dùng trong trường hợp tính tổng các hàng được lọc như sau: =SUBTOTAL(9,pham_vi).
Trong đó:
- pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.
Đếm các ô được lọc không trống
Tại đây chúng ta sẽ sử dụng đến Subtotal 3 hoặc Subtotal 103. Nếu trường hợp có hàng ẩn thì bạn phải sử dụng hàm Subtotal 103 để đếm chính xác các ô không trống nhìn thấy.
Ví dụ như với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng Subtotal 3 hoặc Subtotal 103 sẽ cho ra 2 kết quả khác nhau.
Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ
Kết quả khi dùng Subtotal 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.
Còn với Subtotal 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.
Bỏ qua các giá trị trong các công thức Subtotal lồng nhau
Ví dụ như dưới đây chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.
Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.
Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.
Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.
Nếu các bạn thích bài viết trên và có ý định xem thêm những bài viết liên quan Excel: Thủ thuật tạo, bỏ page break (ngắt trang) trong Excel, Hướng dẫn cách sử dụng hàm Hlookup trong Excel hay Hướng dẫn cách sử dụng hàm Offset trong Excel.
Chuyên mục kiến thức tại chia sẻ công thức và cách sử dụng hàm Subtotal trong Excel. Mong rằng những kiến thức bên trên giúp ích cho việc học và công việc của bạn. Nếu bạn có thắc mắc hay cần tư vấn gì thì hãy liên hệ với chúng tôi ngay để được tư vấn nhé.