Cách sử dụng hàm SUBTOTAL trong Excel

Khi trang tính Excel của bạn chứa các hàng ẩn, dữ liệu được lọc hoặc dữ liệu được nhóm, hãy sử dụng hàm SUBTOTAL của Excel. Hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị ẩn trong tính toán. Ngoài việc tìm tổng của một nhóm dữ liệu, Excel còn có thể tính trung bình, giá trị lớn nhất, nhỏ nhất, độ lệch chuẩn và phương sai của dữ liệu. Dưới đây là cách chèn tổng phụ trong Excel.

Hướng dẫn trong bài viết này áp dụng cho Excel cho Microsoft 365, Excel 2019 và Excel 2016.

Cú pháp của hàm SUBTOTAL

Sử dụng hàm SUBTOTAL trong Excel để tóm tắt các giá trị trong trang tính theo nhiều cách khác nhau. Nó đặc biệt hữu ích khi trang tính của bạn chứa các hàng ẩn mà bạn muốn đưa vào tính toán.

Cú pháp của hàm SUBTOTAL là: SUBTOTAL(function_num,ref1,ref2,…)

Đối số function_num là bắt buộc và chỉ định loại phép toán toán học để sử dụng cho tổng phụ. Hàm SUBTOTAL có thể cộng các số, tính giá trị trung bình của các số được chọn, tìm giá trị lớn nhất và nhỏ nhất trong một phạm vi, đếm số lượng giá trị trong một phạm vi được chọn, và nhiều hơn nữa.

Hàm SUBTOTAL bỏ qua các ô không chứa dữ liệu và các ô có giá trị không phải số.

Đối số này là một số và tùy thuộc vào việc bạn có muốn bao gồm các hàng ẩn trong kết quả hay loại trừ các hàng ẩn khỏi kết quả. Các hàng này có thể bị ẩn thủ công hoặc bị ẩn bởi bộ lọc.

Các đối số function_num bao gồm:

Các đối số tham chiếu function_num từ 1 đến 11 chỉ bao gồm giá trị trong các hàng ẩn khi sử dụng lệnh Hide để ẩn hàng. Khi sử dụng lệnh Filter, các tính toán SUBTOTAL không bao gồm kết quả lọc ẩn.

Đối số ref1 là bắt buộc. Đây là các ô được dùng để tính kết quả của đối số function_num được chọn. Đối số này có thể là một giá trị, một ô đơn lẻ hoặc một phạm vi ô.

Các đối số ref2,… là tùy chọn. Đây là các ô bổ sung được đưa vào tính toán.

Sử dụng hàm SUBTOTAL với các hàng ẩn

Các hàm Excel có thể được nhập thủ công hoặc với sự trợ giúp của hộp đối số Function Arguments. Để minh họa cách nhập hàm thủ công sử dụng thanh công thức, ví dụ sau sử dụng đối số function_num COUNT để đếm số giá trị trong các hàng hiển thị và cả hàng hiển thị lẫn ẩn.

Để sử dụng hàm SUBTOTAL đếm số hàng trong trang tính:

1. Bắt đầu với một trang tính chứa nhiều hàng dữ liệu.

2. Chọn ô sẽ chứa số đếm của các hàng hiển thị.

3. Trong thanh công thức, nhập =SUBTOTAL. Khi bạn gõ, Excel gợi ý một hàm. Nhấp đúp vào hàm SUBTOTAL.

   Để dùng hộp đối số Function Arguments nhập hàm SUBTOTAL, vào Formulas và chọn Math & Trig > SUBTOTAL.

   

4. Trong menu thả xuống xuất hiện, nhấp đúp vào đối số 102 – COUNT function_num.

   

5. Gõ một dấu phẩy (,).

   

6. Trong trang tính, chọn các ô để đưa vào công thức.

   

7. Nhấn Enter để xem kết quả trong ô bạn chọn ở bước 2.

   

8. Chọn ô sẽ chứa số đếm của các hàng hiển thị và ẩn.

9. Trong thanh công thức, nhập =SUBTOTAL. Khi bạn gõ, Excel gợi ý một hàm. Nhấp đúp vào hàm SUBTOTAL.

10. Trong menu thả xuống xuất hiện, nhấp đúp vào đối số 2 – COUNT function_num, sau đó gõ một dấu phẩy (,).

11. Trong trang tính, chọn các ô để đưa vào công thức, sau đó nhấn Enter.

    

12. Ẩn một vài hàng dữ liệu. Trong ví dụ này, các hàng chỉ có doanh số dưới $100,000 đã bị ẩn.

    

Sử dụng hàm SUBTOTAL với dữ liệu được lọc

Sử dụng hàm SUBTOTAL trên dữ liệu được lọc sẽ bỏ qua dữ liệu trong các hàng đã bị loại bỏ bởi bộ lọc. Mỗi khi tiêu chí lọc thay đổi, hàm sẽ tính toán lại để hiển thị tổng phụ cho các hàng hiển thị.

Để dùng hàm SUBTOTAL xem sự khác biệt trong kết quả tính toán khi lọc dữ liệu:

1. Tạo các công thức SUBTOTAL. Ví dụ: tạo công thức để xác định tổng phụ và giá trị trung bình của dữ liệu được lọc.

   Không quan trọng bạn dùng đối số function_num cho hàng hiển thị hay ẩn. Cả hai đều cho cùng kết quả với dữ liệu được lọc.

   

2. Chọn bất kỳ ô nào trong tập dữ liệu.

3. Vào Home, rồi chọn Sort & Filter > Filter.

   

4. Sử dụng các mũi tên thả xuống để lọc dữ liệu trang tính.

   

5. Chú ý cách các giá trị thay đổi mỗi khi bạn chọn tiêu chí lọc khác nhau.

   

Sử dụng hàm SUBTOTAL với dữ liệu được nhóm

Khi dữ liệu được nhóm, có một cách để áp dụng hàm SUBTOTAL cho từng nhóm riêng lẻ và sau đó tính tổng cộng cho toàn bộ tập dữ liệu.

1. Chọn bất kỳ ô nào trong tập dữ liệu.

2. Chọn Data > Subtotal để mở hộp thoại Subtotal.

   

3. Chọn mũi tên thả xuống At each change in và chọn nhóm mà mỗi tổng phụ sẽ được tính toán.

4. Chọn mũi tên thả xuống Use function và chọn một function_num.

5. Trong danh sách Add subtotal to, chọn cột mà công thức sẽ được áp dụng.

6. Chọn OK.

   

7. Các tổng phụ được chèn cho từng nhóm dữ liệu, và một tổng cộng được chèn ở cuối tập dữ liệu.

   

8. Để thay đổi function_num, đánh dấu bất kỳ ô nào trong tập dữ liệu và chọn Data > Subtotal. Sau đó, làm các lựa chọn của bạn trong hộp thoại Subtotal.