Hàm SUBTOTAL cùng các ví dụ minh họa chi tiết và dễ áp dụng

Hàm SUBTOTAL trong Excel là gì? Đây là hàm dùng để tính tổng phụ cho một vùng dữ liệu cụ thể trong bảng tính. Bài viết dưới đây sẽ giải thích chi tiết công thức và cách sử dụng hàm SUBTOTAL một cách hiệu quả. Đừng bỏ lỡ!

Hàm SUBTOTAL trong Excel là gì? 

Hàm SUBTOTAL trong Excel là công cụ hữu ích để tính toán các tập hợp con trong danh sách hoặc cơ sở dữ liệu. Hàm này có thể áp dụng để tính tổng, trung bình, đánh số thứ tự, hoặc xác định giá trị lớn nhất, nhỏ nhất trong bảng dữ liệu một cách linh hoạt.

Những lợi ích nổi bật khi sử dụng hàm SUBTOTAL trong Excel bao gồm:

• Khả năng tính tổng các giá trị trong hàng được chọn một cách linh hoạt và nhanh chóng.
• Tính toán dễ dàng ngay cả với các giá trị ẩn trong bảng dữ liệu.
• Nếu ref1, ref2,... chứa hàm SUBTOTAL, chúng sẽ tự động bị bỏ qua để tránh tính trùng lặp.

Công thức của hàm SUBTOTAL

Cú pháp sử dụng hàm SUBTOTAL: =SUBTOTAL (function_num, ref1, [ref2],…)

Các thành phần trong công thức: 

• Function_num: Là số từ 1-11 hoặc 101-111 để chỉ định hàm tính toán tổng phụ. Các số từ 1-11 bao gồm cả giá trị ẩn, trong khi số từ 101-111 chỉ tính toán các giá trị không ẩn trong vùng dữ liệu.
• Ref1: Là phạm vi hoặc tham chiếu đầu tiên được chọn để tính tổng phụ.
• Ref2,…: Là các phạm vi hoặc tham chiếu bổ sung từ 2 đến 254 để tính tổng phụ.

Dưới đây là bảng chi tiết các mã số Function_num được sử dụng trong hàm SUBTOTAL:

Những điều cần lưu ý khi sử dụng hàm SUBTOTAL trong Excel:

• Hàm SUBTOTAL chỉ áp dụng cho các cột dữ liệu hoặc phạm vi dọc, không dùng cho hàng dữ liệu hoặc phạm vi ngang.
• Hàm này chỉ tính giá trị của hàng ẩn khi sử dụng lệnh Hide, không tính các hàng ẩn bởi Auto Filter.
• Nếu có hàm SUBTOTAL lồng trong ref1, ref2,…, chúng sẽ bị bỏ qua để tránh tính toán trùng lặp.
• Dữ liệu dạng 3-D sẽ khiến hàm SUBTOTAL trả về lỗi #VALUE!.

Hướng dẫn sử dụng hàm SUBTOTAL

Để nắm vững cách sử dụng hàm SUBTOTAL trong Excel, bạn có thể xem qua các ví dụ dưới đây.

Ví dụ 1: Tính tổng dữ liệu đã lọc

Trong bảng tính DANH SÁCH HOA HỒNG THÁNG, có hai cột là Nhóm và Phí hoa hồng. Bạn cần lọc ra tổng phí hoa hồng của đội 1. Cách thực hiện như sau:

Bước 1: Chọn toàn bộ bảng dữ liệu, vào mục Home và chọn Sort and Filter.

Bước 2: Trong phần Sort and Filter, chọn tiếp Filter để áp dụng bộ lọc.

Bước 3: Ở cột Nhóm, nhấn vào biểu tượng tam giác ngược. Tiếp theo, tích chọn 1 rồi bấm OK.

Bước 4: Dữ liệu của Nhóm 1 đã được lọc thành công. Tại ô kết quả, hãy nhập công thức: =SUBTOTAL(9,D3:D9)

Các thành phần trong công thức bao gồm: 

• function_num: Nhập giá trị 9 để tính tổng phí hoa hồng. Giá trị này đại diện cho hàm SUM (tính tổng các giá trị).
• ref1: Vùng dữ liệu cần tính tổng, từ ô D3 đến D9.

Sau khi nhập công thức, nhấn Enter để nhận kết quả như hình minh họa:

Trong đối số function_num, bạn có thể sử dụng giá trị 109 để bỏ qua các hàng bị ẩn và chỉ tính tổng các hàng đã lọc.

Ví dụ 2: Đếm số ô không trống trong vùng dữ liệu đã lọc

Giả sử bạn có bảng DANH SÁCH SẢN PHẨM CẦN MUA, bao gồm các danh mục như đồ gia dụng và mỹ phẩm. Bạn cần đếm số lượng sản phẩm thuộc nhóm đồ gia dụng đã có link mua hàng để phục vụ việc thống kê. 

Bước 1: Trước tiên, hãy lọc ra các sản phẩm thuộc nhóm đồ gia dụng bằng cách sử dụng tính năng Auto Filter. Thao tác lọc tương tự như ví dụ trước, vào Sort and Filter và chọn Filter.

Bước 2: Tại cột Danh mục, nhấn vào biểu tượng tam giác ngược để tích chọn mục Đồ gia dụng và bấm OK.

Bước 3: Sau khi hoàn tất việc lọc, nhập công thức hàm SUBTOTAL vào ô kết quả: =SUBTOTAL(3,D3:D10)

Giải thích công thức trên: 

• function_num: là giá trị đại diện cho hàm cần sử dụng. Vì bạn cần đếm các ô không trống, giá trị này sẽ là 3, tương ứng với hàm COUNTA.
• ref1: là phạm vi dữ liệu cần tính, từ D3 đến D10 sau khi đã lọc.

Bước 4: Nhấn Enter để nhận kết quả như sau: 

Trong bước nhập công thức, bạn có thể sử dụng đối số 103 (bỏ qua các giá trị ẩn) để đếm các ô không trống trong vùng đã lọc.

Ví dụ 3: Tạo số thứ tự nhanh chóng

Giả sử bạn cần tạo số thứ tự cho một danh sách với các hàng liên tiếp. Bạn có thể sử dụng hàm SUBTOTAL trong Excel theo cách sau: 

Bước 1: Tại cột STT, nhập công thức vào ô A2 như sau: =SUBTOTAL(3,$B$2:B2) và nhấn Enter.

Giải thích:

• function_num: giá trị 3 tương ứng với hàm COUNTA, dùng để đếm số ô không trống.
• ref1: nhập $B$2:B2 để đếm ký tự trong phạm vi này. Để cố định ô B2, hãy nhấn phím F4. 

Bước 2: Sau đó, ở góc dưới bên phải của ô B2 xuất hiện dấu cộng, bạn hãy nhấp chuột kéo thẳng xuống các ô khác để sao chép công thức. 

Bước 3: Bạn sẽ nhận được kết quả như hình sau:

Lưu ý: khi sao chép công thức, giá trị phải thay đổi theo kiểu: $B$2:B3, $B$2:B4,... thì hàm mới đếm đúng.

Ví dụ 4: Tìm giá trị lớn nhất

Trong bảng dữ liệu DANH SÁCH ĐỒ DÙNG, bạn cần xác định sản phẩm có giá trị cao nhất. Hãy sử dụng hàm SUBTOTAL để tìm giá trị lớn nhất trong bảng tính.

Bước 1: Tại ô hiển thị kết quả, nhập công thức =SUBTOTAL(4,C3:C7).

Giải thích:

• function_num: giá trị 4 tương ứng với hàm MAX để tìm giá trị lớn nhất.
• ref1: là phạm vi dữ liệu cần tính, từ C3 đến C7.

Bước 2: Sau khi nhập công thức, nhấn Enter để hiển thị kết quả.

Tương tự, bạn có thể tìm giá trị nhỏ nhất trong bảng dữ liệu Excel bằng cách thay đổi giá trị đối số function_num thành 5 - tương ứng với hàm MIN.

Kết hợp hàm SUBTOTAL với các hàm khác trong Excel

Hàm SUBTOTAL còn có thể kết hợp với các hàm khác như VLOOKUP hoặc IF. Sự kết hợp này giúp hàm SUBTOTAL hoạt động hiệu quả hơn trong việc tính toán.

Kết hợp Hàm SUBTOTAL và VLOOKUP

Khi kết hợp hàm SUBTOTAL với VLOOKUP, công thức sẽ có dạng như sau:

=SUBTOTAL(funtion_num, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Hàm VLOOKUP được dùng để tìm kiếm dữ liệu trong một bảng và trả về giá trị khớp với hàng ngang tương ứng. Khi kết hợp với hàm SUBTOTAL, bạn có thể phân tích dữ liệu nhanh chóng mà không cần tạo bảng tổng hợp phức tạp.

Hãy xem ví dụ sau: 

Giả sử bạn có hai bảng dữ liệu: ĐIỂM KPI CỦA CÁC NHÓM và DANH SÁCH NHÂN VIÊN TỪNG NHÓM. Bạn muốn tìm điểm KPI cao nhất trong Tháng 11 của nhân viên thuộc Nhóm 2. Dưới đây là các bước thực hiện:

Bước 1: Nhập công thức vào ô cần hiển thị kết quả:

=SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false))

Mã hàm SUBTOTAL là 4, tương ứng với việc tìm giá trị lớn nhất trong bảng.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Hàm VLOOKUP đã trả về giá trị điểm KPI cho nhân viên nhóm 2. Khi kết hợp với hàm SUBTOTAL, nó sẽ tìm ra giá trị lớn nhất trong các kết quả trả về.

Kết hợp Hàm SUBTOTAL và Hàm IF

Hàm SUBTOTAL có thể được sử dụng như một hàm lồng trong hàm IF. Nó thường được áp dụng khi cần lọc dữ liệu theo điều kiện hoặc đánh số thứ tự theo cột.

Cú pháp chung là: =IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,...))

Ví dụ: Khi bạn có một bảng dữ liệu cần đánh số thứ tự, nhưng có các hàng trống và không trống xen kẽ. Trong trường hợp này, bạn có thể kết hợp SUBTOTAL với hàm IF như sau:

Bước 1: Tại cột STT, nhập công thức vào ô A2: =IF(B2='','',SUBTOTAL(3,$B$2:B2)).

Với hàm IF:

• logical_test: điều kiện kiểm tra, nếu ô B2 trống thì trả về giá trị đúng.
• value_if_true: Trả về giá trị trống (“”) nếu điều kiện đúng.
• value_if_false: Trả về giá trị từ hàm SUBTOTAL nếu điều kiện sai, dùng để đánh số thứ tự.

Trong hàm SUBTOTAL, giá trị đối số 3 tương ứng với hàm COUNTA - đếm số ô không trống. Số thứ tự sẽ được đánh lại từ đầu khi lọc dữ liệu theo điều kiện.

Bước 2: Sau khi nhập công thức, nhấn Enter và sao chép công thức từ ô A2 đến các ô khác. Kết quả thu được như sau:

Tóm lại, nếu gặp ô trống, hàm IF sẽ bỏ qua không đếm. Khi gặp ô không trống, hàm SUBTOTAL sẽ đánh số thứ tự.

Một số lỗi thường gặp khi sử dụng hàm SUBTOTAL trong Excel

Khi sử dụng hàm SUBTOTAL, đôi khi bạn sẽ gặp phải một số lỗi như sau:

Lỗi #VALUE! 

Lỗi này thường xảy ra do một trong hai nguyên nhân sau:

• Mã chức năng function_num không nằm trong khoảng 1-11 hoặc 101-111. 
• Phạm vi dữ liệu cần tính là tham chiếu dạng 3D. Nếu phạm vi bạn chọn bao gồm các ô từ một trang tính khác, lỗi này sẽ xuất hiện.

Để khắc phục lỗi #VALUE!, hãy kiểm tra và sửa lại giá trị function_num cũng như phạm vi tham chiếu dữ liệu.

Lỗi #DIV/0!

Lỗi này xảy ra khi bạn thực hiện phép chia một số hoặc tổng cho 0. Thường gặp khi tính giá trị trung bình hoặc độ lệch chuẩn của một dãy ô không chứa số.

Để sửa lỗi này, hãy kiểm tra lại dữ liệu nhập vào các ô tính.

Lỗi #NAME?

Lỗi này xảy ra khi bạn nhập sai tên hàm SUBTOTAL. Để tránh lỗi, hãy nhập chính xác tên hàm. 

Khi nhập công thức trong Excel, chỉ cần gõ dấu = và chữ cái đầu S, một bảng gợi ý sẽ hiện ra. Bạn có thể chọn hàm SUBTOTAL từ danh sách gợi ý này.

Kết luận

Trên đây là hướng dẫn chi tiết cách sử dụng hàm SUBTOTAL trong Excel kèm theo các ví dụ minh họa. Bạn có thể áp dụng hàm này để tối ưu hóa hiệu quả tính toán. Hy vọng bài viết sẽ giúp công việc của bạn trở nên đơn giản và hiệu quả hơn.