Khi trang tính Excel của bạn chứa các hàng ẩn, dữ liệu đã 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 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 phép tính.
Cú pháp của hàm SUBTOTAL là: SUBTOTAL (function_num, ref1, ref2,…)
Đối số function_numlà 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 phạm vi, đếm số giá trị trong một phạm vi đã 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 đưa các hàng ẩn vào 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ố của function_num bao gồm:
| Chức năng Nhiệm vụ | hàm_num | hàm_num |
|---|---|---|
| (bao gồm các giá trị ẩn) | (không bao gồm các giá trị ẩn) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| PHÚT | 5 | 105 |
| SẢN | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
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.
Đối số ref1là 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 ô.
Các đối số ref2,…là tùy chọn. Đây là các ô bổ sung được bao gồm trong phép tính.
Sử dụng Hàm SUBTOTAL với 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:
- Bắt đầu với một trang tính chứa nhiều hàng dữ liệu.
- Chọn ô sẽ chứa số lượng các hàng hiển thị.
-
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 chức năng SUBTOTAL.
Để sử dụng hộp thoại Đối số Hàm để nhập hàm SUBTOTAL, hãy chuyển đến Formulasvà chọn Math &Trig> TRƯỞNG.
Image -
Trong menu thả xuống xuất hiện, nhấp đúp vào đối số 102 - COUNTfunction_num.
Image -
Nhập dấu phẩy (,).
Image -
Trong trang tính, hãy chọn các ô để đưa vào công thức.
Image -
Nhấn Nhậpđể xem kết quả trong ô bạn đã chọn ở bước 2.
Image - Chọn ô chứa tổng số hàng hiển thị và hàng ẩn.
- 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 chức năng SUBTOTAL.
- Trong menu thả xuống xuất hiện, nhấp đúp vào đối số 2 - COUNTfunction_num, sau đó nhập dấu phẩy (,).
-
Trong trang tính, chọn các ô để đưa vào công thức, sau đó nhấn Nhập.
Image -
Ẩ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 đã bị ẩn.
Image
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ị 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:
-
Tạo công thức HẤP DẪN. 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 đã 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ị hay ẩn. Cả hai đối số đều cung cấp cùng một kết quả trong dữ liệu đã lọc.
Image - Chọn ô bất kỳ trong tập dữ liệu.
-
Vào Home, sau đó chọn Sort &Filter> Filter.
Image -
Sử dụng các mũi tên thả xuống để lọc dữ liệu trang tính.
Image -
Lưu ý các giá trị thay đổi như thế nào mỗi khi bạn chọn các tiêu chí lọc khác nhau.
Image
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.
- Chọn ô bất kỳ trong tập dữ liệu.
-
Chọn Data> Subtotal để mở hộp thoại Subtotal.
Image - Chọn Tại mỗi thay đổi trong mũi tên thả xuốngvà chọn nhóm mà mỗi tổng phụ sẽ được tính.
- Chọn mũi tên thả xuống Sử dụng chức năngvà chọn một hàm_num.
- Trong danh sách Thêm tổng phụ vào, hãy chọn cột mà công thức sẽ được áp dụng.
-
Chọn OK.
Image -
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.
Image - Để thay đổi hàm_num, đánh dấu ô bất kỳ trong tập dữ liệu và chọn Data> Subtotal. Sau đó, đưa ra lựa chọn của bạn trong hộp thoại Subtotal.
