Subtotal là hàm được sử dụng để tính toán tập hợp con của một danh sách hoặc cơ sở dữ liệu trong bảng tính Excel. Hàm này cho phép bạn thực hiện các phép tính như tính tổng, tính trung bình, tìm giá trị lớn nhất hoặc nhỏ nhất,…cho một phần dữ liệu được chọn trong danh sách, bỏ qua các giá trị ẩn. Tuy nhiên nhiều bạn vẫn còn nhầm lẫn giữa hàm Subtotal và hàm Sum. Để hiểu rõ hơn về nó, mời bạn cùng Jobsnew theo dõi bài viết sau đây!
1. Giới thiệu chung về hàm Subtotal
1.1 Hàm Subtotal là gì? Định nghĩa và chức năng cơ bản
Hàm Subtotal là hàm trong Excel được sử dụng để tính toán các giá trị trong một tập hợp con của dữ liệu, cho phép bạn thực hiện các phép tính như tính tổng, trung bình, đếm,… trên một phần cụ thể của bảng dữ liệu. Hàm Subtotal có các chức năng cơ bản sau:
- Tính toán các giá trị trong một tập hợp con của dữ liệu: Cho phép chỉ tính toán các giá trị trong một phần cụ thể của bảng dữ liệu, giúp bạn dễ dàng phân tích dữ liệu theo nhóm hoặc theo các tiêu chí khác nhau.
- Bỏ qua các hàng ẩn hoặc được lọc: Có thể bỏ qua các hàng ẩn hoặc được lọc, giúp bạn tập trung vào dữ liệu muốn phân tích.
- Sử dụng với nhiều loại phép toán khác nhau: Được sử dụng với nhiều loại phép toán khác nhau, giúp thực hiện nhiều dạng phân tích dữ liệu khác nhau.
- Kết hợp với các hàm Excel khác: Có thể được sử dụng với các hàm Excel khác để tạo ra các công thức phức tạp hơn.
Sử dụng hàm Subtotal mang lại nhiều lợi ích, bao gồm:
- Tăng hiệu quả: Giúp tiết kiệm thời gian, công sức khi thực hiện các phép toán trên dữ liệu.
- Cải thiện độ chính xác: Giúp giảm thiểu sai sót khi tính toán dữ liệu.
- Dễ dàng phân tích dữ liệu: Giúp dễ dàng phân tích dữ liệu theo nhóm hoặc theo các tiêu chí khác nhau.
- Tăng tính linh hoạt: Có thể được sử dụng với nhiều loại dữ liệu và nhiều loại phép toán khác nhau.
1.2 Sự khác biệt giữa hàm Subtotal và hàm Sum trong Excel
Hàm Sum dùng để tính tổng các giá trị trong một phạm vi. Ví dụ: =SUM(A1:A10) sẽ tính tổng các giá trị từ ô A1 đến A10.
Trong khi hàm Subtotal dùng để tính tổng các giá trị trong một phạm vi, với các tùy chọn bổ sung để bỏ qua các hàng ẩn, các hàng được lọc hoặc các nhóm con trong dữ liệu được sắp xếp. Ví dụ: =SUBTOTAL(9,A1:A10) sẽ tính tổng các giá trị từ ô A1 đến A10, nhưng bỏ qua các hàng ẩn.
Hàm Sum và hàm Subtotal đều là những công cụ hữu ích để tính tổng các giá trị trong Excel. Tuy nhiên, hàm Subtotal cung cấp nhiều tùy chọn hơn để kiểm soát cách tính tổng, khiến nó trở nên linh hoạt hơn trong nhiều trường hợp. Dưới đây là bảng so sánh giúp bạn đánh giá rõ hơn về sự khác biệt của 2 loại hàm này:
| Tính năng | Hàm Sum | Hàm Subtotal |
| Chức năng | Tính tổng các giá trị | Tính tổng các giá trị với các tùy chọn bổ sung |
| Bỏ qua hàng ẩn | Không | Có |
| Bỏ qua hàng được lọc | Không | Có |
| Tính tổng cho các nhóm con | Không | Có |
| Lồng nhau | Có thể dẫn đến tính hai lần | Không tính hai lần |
| Dễ sử dụng | Dễ sử dụng hơn | Khó sử dụng hơn |
Ví dụ: Giả sử bạn có bảng dữ liệu sau:
| A | B | C |
| 1 | 10 | 20 |
| 2 | 20 | 30 |
| 3 | 30 | 40 |
| 4 | 40 | 50 |
Hàm Sum:
=SUM(A1:A4) sẽ trả về kết quả là 100, là tổng của tất cả các giá trị trong cột A.
Hàm Subtotal:
=SUBTOTAL(9,A1:A4) cũng sẽ trả về kết quả là 100, nhưng nếu bạn ẩn hàng 2, hàm Subtotal sẽ tự động điều chỉnh kết quả thành 70, vì nó chỉ tính tổng các giá trị trong các hàng hiển thị.
2. Cách sử dụng hàm Subtotal trong Excel
2.1 Cú pháp cơ bản của hàm Subtotal
Cú pháp cơ bản của hàm Subtotal là: =SUBTOTAL(function_num, ref1, [ref2], …)
Trong đó:
- Function_num: Là số xác định loại phép toán mà bạn muốn thực hiện.
- Ref1: Là phạm vi dữ liệu đầu tiên mà bạn muốn tính toán.
- Ref2, …: Là các phạm vi dữ liệu tiếp theo (tùy chọn) mà bạn muốn tính toán.
2.2 Bước đầu sử dụng hàm Subtotal: Ví dụ cơ bản
Ví dụ (Tính tổng cộng): Giả sử bạn có bảng dữ liệu doanh thu bán hàng theo tháng như sau:
| Tháng | Doanh thu |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
Để tính tổng doanh thu, bạn có thể sử dụng hàm Subtotal như sau:
Bước 1: Nhập công thức vào ô bất kỳ, ví dụ: ô B6.
Bước 2: Gõ công thức: =SUBTOTAL(9, B2:B5)
- 9 là mã số tương ứng với hàm SUM (tính tổng).
- B2: B5 là phạm vi dữ liệu cần tính tổng.
Bước 3: Nhấn Enter. Kết quả hiển thị trong ô B6 là 1500, là tổng doanh thu từ tháng 1 đến tháng 5.
3. Ứng dụng thực tế của hàm Subtotal có điều kiện trong Excel
Một số ứng dụng thực tế của hàm SUBTOTAL có điều kiện:
Ví dụ 1 – Tính tổng doanh thu theo khu vực và loại sản phẩm
Giả sử bạn có bảng dữ liệu bán hàng với các thông tin về khu vực, loại sản phẩm và doanh thu. Bạn muốn tính tổng doanh thu cho từng khu vực, đồng thời phân biệt theo từng loại sản phẩm.
Công thức: =SUBTOTAL(9, SUM(B2:B10), IF(A2:A10=”Khu vực 1″, C2:C10, “”))
Giải thích:
- 9: Mã chức năng cho SUM (tổng)
- B2:B10: Phạm vi chứa dữ liệu doanh thu
- IF(A2:A10=”Khu vực 1″, C2:C10, “”): Lọc dữ liệu theo khu vực “Khu vực 1” và lấy giá trị loại sản phẩm tương ứng.
Ví dụ 2 – Tính trung bình điểm thi theo môn học và giới tính
Giả sử bạn có bảng dữ liệu điểm thi của học sinh với các thông tin về tên học sinh, giới tính, môn học và điểm thi. Bạn muốn tính trung bình điểm thi cho từng môn học, đồng thời phân biệt theo giới tính nam và nữ.
Công thức: =SUBTOTAL(1, AVERAGE(D2:D10), IF(AND(A2:A10=”Nam”, B2:B10=”Toán”), C2:C10, “”))
Giải thích:
- 1: Mã chức năng cho AVERAGE (trung bình cộng)
- D2:D10: Phạm vi chứa dữ liệu điểm thi
- IF(AND(A2:A10=”Nam”, B2:B10=”Toán”), C2:C10, “”): Lọc dữ liệu theo giới tính “Nam” và môn học “Toán” và lấy giá trị điểm thi tương ứng.
4. Giải quyết các vấn đề thường gặp khi sử dụng hàm Subtotal
4.1 Phổ biến nhất: sửa lỗi #value! trong hàm Subtotal
Lỗi #VALUE! xuất hiện phổ biến nhất khi chúng ta sử dụng hàm Subtotal. Nguyên nhân của lỗi này xuất phát từ số xác định chức năng function_num không hợp lệ. Số này phải nằm trong khoảng từ 1 đến 11 hoặc 101 đến 111. Hoặc tham chiếu ô không chính xác. Nói dễ hiểu hơn là tham chiếu ô phải nằm trong cùng một trang tính với công thức Subtotal.
Giải pháp để khắc phục sửa lỗi #VALUE! là:
- Kiểm tra số function_num: Đảm bảo rằng số này nằm trong khoảng hợp lệ.
- Kiểm tra tham chiếu ô: Đảm bảo rằng tham chiếu ô chính xác và nằm trong cùng một trang tính với công thức Subtotal.
4.2 Mẹo và thủ thuật khi làm việc với hàm Subtotal là gì?
Hàm Subtotal trong Excel là công cụ đắc lực giúp bạn tính toán các giá trị trong một danh sách dữ liệu, bao gồm cả các giá trị ẩn và bị lọc. Dưới đây là một số mẹo và thủ thuật để sử dụng hàm Subtotal hiệu quả hơn:
Sử dụng các số chức năng phù hợp
Hàm Subtotal có 111 số chức năng khác nhau, mỗi số tương ứng với một phép toán khác nhau. Ví dụ:
- 1: Tính tổng
- 2: Tính trung bình
- 3: Đếm số ô không trống
- 101: Tính tổng (bỏ qua các giá trị ẩn)
- 103: Đếm số ô không trống (bỏ qua các giá trị ẩn)
Bạn cần lựa chọn số chức năng phù hợp với nhu cầu tính toán của mình.
Bỏ qua các hàng và cột ẩn
Hàm Subtotal có thể bỏ qua các hàng và cột ẩn khi tính toán. Để làm điều này, bạn cần sử dụng các số chức năng từ 101 đến 111.
Sử dụng hàm Subtotal với các hàm khác
Bạn có thể kết hợp hàm Subtotal với các hàm khác để tạo ra các công thức phức tạp hơn. Ví dụ:
- =SUBTOTAL(9,A1:A10) + B1 sẽ tính tổng các giá trị trong phạm vi A1:A10 và cộng thêm giá trị trong ô B1.
- =IF(SUBTOTAL(3,A1:A10)>5,”Đạt”,”Không đạt”) sẽ kiểm tra xem số lượng ô không trống trong phạm vi A1:A10 có lớn hơn 5 hay không.
Sử dụng hàm Subtotal với các bảng dữ liệu động
Hàm Subtotal có thể tự động cập nhật kết quả khi bạn thêm hoặc xóa dữ liệu trong bảng dữ liệu. Điều này giúp bạn tiết kiệm thời gian và công sức khi cần cập nhật báo cáo.
Sử dụng tên để tham chiếu các phạm vi dữ liệu
Sử dụng tên để tham chiếu các phạm vi dữ liệu giúp công thức của bạn dễ đọc và dễ hiểu hơn. Ví dụ:
- =SUBTOTAL(9,Sales) sẽ tính tổng các giá trị trong cột Sales.
Một số mẹo và thủ thuật khác:
- Sử dụng phím tắt Ctrl + Shift + Subtotal để nhanh chóng chèn hàm Subtotal vào công thức.
- Sử dụng chức năng Go To Special để nhanh chóng chọn các ô ẩn hoặc bị lọc.
- Sử dụng Conditional Formatting để tô màu các giá trị vượt quá hoặc dưới một giá trị nhất định.
Lưu ý: Hàm Subtotal không thể tính toán các giá trị trong các bảng dữ liệu khác. Nó có thể hoạt động chậm với các tập dữ liệu lớn.
5. Kết luận
Hàm Subtotal đóng vai trò quan trọng trong phân tích dữ liệu Excel. Nó giúp bạn tính toán linh hoạt, lọc và ẩn dữ liệu hiệu quả, đồng thời tăng năng suất làm việc. Biết cách dùng Subtotal trong Excel sẽ giúp bạn khai thác tối đa dữ liệu, đưa ra các quyết định sáng suốt và nâng cao hiệu quả công việc. Nếu thấy những thông tin từ bài viết vừa rồi Jobsnew chia sẻ là bổ ích, hãy thường xuyên theo dõi Jobsnew Blog để có thể theo dõi thêm nhiều kiến thức thú vị khác nhé!
