Cách sử dụng hàm tổng phụ 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 lại, 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 số của một nhóm dữ liệu, Excel có thể tính giá trị trung bình, tối đa, tối thiểu, độ lệch chuẩn và phương sai của dữ liệu của bạn. Đâ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 for 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 những 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 phép tính.

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

function_num đối số là bắt buộc và chỉ định loại phép toán sẽ sử dụng cho tổng phụ. Hàm SUBTOTAL có thể thêm số, tính giá trị trung bình của các số đã chọn, tìm giá trị lớn nhất và nhỏ nhất trong một dải ô, đếm số lượng giá trị trong một dải ô đã chọn và hơn thế 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à phụ thuộc vào việc bạn 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ể được ẩn hoặc ẩn bằng bộ lọc theo cách thủ công.

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 các giá trị trong các hàng bị ẩn khi sử dụng lệnh Ẩn để ẩn các hàng. Khi sử dụng lệnh Bộ lọc, các phép tính SUBTOTAL không bao gồm các kết quả lọc ẩn.

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

ref2,… đối số là tùy chọn. Đây là những ô bổ sung được bao gồm trong phép tính.

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

Các hàm Excel có thể được nhập theo cách thủ công hoặc với sự trợ giúp của hộp thoại Đối số Hàm. Để minh họa cách nhập hàm theo cách thủ công bằng thanh công thức, ví dụ sau sử dụng đối số COUNT function_num để đếm số giá trị trong các hàng hiển thị và cả trong các hàng hiển thị và ẩ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ố lượng hàng hiển thị.

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

   Để sử dụng hộp thoại Đối số hàm để nhập hàm SUBTOTAL, hãy đi tới Công thức và chọn Math &Trig > TIÊU ĐỀ .

   

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

   

5. Nhập dấu phẩy (, ).

   

6. Trong trang tính, hãy 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 tổng số hàng hiển thị và hàng ẩn.

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

10. Trong trình đơn thả xuống xuất hiện, nhấp đúp vào 2 - COUNT đối số function_num, sau đó nhập dấu phẩy (, ).

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

    

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

    

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

Việ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ị bộ lọc loại bỏ. Mỗi khi tiêu chí bộ 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ị.

Để sử 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ông thức SUBTOTAL. Ví dụ:tạo công thức để xác định giá trị tổng phụ và giá trị trung bình của dữ liệu đã lọc.

   Không thành vấn đề nếu bạn sử dụng đối số function_num cho các hàng hiển thị hoặc ẩn. Cả hai đối số đều cung cấp cùng một kết quả trong dữ liệu đã lọc.

   

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

3. Đi tới Trang chủ , sau đó chọn Sắp xếp &Bộ lọc > Bộ lọc .

   

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

   

5. Lưu ý cách các giá trị thay đổi mỗi khi bạn chọn các 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 lại, 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 tổ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 Dữ liệu > Tổng phụ để mở Tổng phụ hộp thoại.

   

3. Chọn nút Ở mỗi thay đổi trong mũi tên thả xuống và chọn nhóm mà mỗi tổng phụ sẽ được tính.

4. Chọn Sử dụng chức năng mũi tên thả xuống và chọn function_num.

5. Trong phần Thêm tổng phụ vào danh sách, chọn cột mà công thức sẽ được áp dụng.

6. Chọn OK .

   

7. Tổng phụ được chèn cho mỗi nhóm dữ liệu và tổng số lớn được chèn vào cuối tập dữ liệu.

   

8. Để thay đổi function_num, hãy đánh dấu bất kỳ ô nào trong tập dữ liệu và chọn Dữ liệu > Tổng phụ . Sau đó, đưa ra lựa chọn của bạn trong Tổng phụ hộp thoại.