Hàm SUBTOTAL trong Excel: công thức, cách dùng và ví dụ

So với các hàm khác thì hàm SUBTOTAL trong Excel linh hoạt hơn và ứng dụng rộng rãi hơn trong tính toán. Trong bài viết này, Gitiho sẽ cùng bạn tìm hiểu cách sử dụng cũng như ứng dụng hàm SUBTOTAL trong công việc nhé.

Xem thêm: Thành thạo Subtotal và 150+ hàm Excel thông dụng khác nhờ Tuyệt đỉnh Excel

Hàm SUBTOTAL trong Excel là gì?

Microsoft định nghĩa hàm SUBTOTAL trong Excel là hàm trả về tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này “tổng phụ” không chỉ là tổng số trong một phạm vi ô xác định.

Không giống như các hàm Excel khác được thiết kế để chỉ thực hiện một việc cụ thể SUBTOTAL linh hoạt hơn khi có thể thực hiện các phép toán số học và logic khác nhau như đếm ô, tính trung bình, tìm giá trị tối thiểu hoặc tối đa…

Cú pháp:

=SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó:

• Function_num – Một số chỉ định hàm nào sẽ sử dụng cho tổng phụ.
• Ref1, Ref2 … – Một hoặc nhiều ô hoặc phạm vi thành tổng phụ. Đối số Ref đầu tiên là bắt buộc, các đối số khác (tối đa là 254) là tùy chọn.
• Đối số Function_num có thể thuộc về một trong các số sau:
• 1 – 11 bỏ qua các ô được lọc, nhưng bao gồm các hàng ẩn thủ công.
• 101 – 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.

Hàm số   Chức năng Diễn giải 1 101 AVERAGE Trả về trung bình của các số 2 102 COUNT Đếm các ô chứa giá trị số 3 103 COUNTA Đếm các ô không trống 4 104 MAX Trả về giá trị lớn nhất 5 105 MIN Trả về giá trị nhỏ nhất 6 106 PRODUCT Tính kết quả của các ô 7 107 STDEV Trả về độ lệch chuẩn mẫu dựa trên mẫu 8 108 STDEVP Trả về độ lệch chuẩn dựa trên toàn bộ số 9 109 SUM Cộng các số 10 110 VAR Ước tính độ dao động dựa trên mẫu 11 111 VARP Ước tính độ dao động dựa trên toàn bộ số

Ví dụ: Đây là cách bạn có thể tạo công thức hàm SUBTOTAL 9 để tổng hợp các giá trị trong các ô từ C2 đến C8:

Để thêm số hàm vào công thức, bấm đúp vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức đầy đủ sẽ như sau:

=SUBTOTAL(9,C2:C8)

Theo cách tương tự, bạn có thể viết công thức 1 để tính trung bình, SUBTOTAL 2 để đếm các ô có số, hàm SUBTOTAL 3 để đếm các khoảng trống,...

Như hình ảnh dưới đây cho thấy một vài công thức khác của hàm SUBTOTAL đã thực hiện:

Ghi chú: Khi bạn sử dụng công thức SUBTOTAL với các hàm tóm tắt như hàm SUM hoặc hàm AVERAGE, nó chỉ tính toán các ô có số bỏ qua khoảng trắng và các ô chứa giá trị không phải là số.

Như vậy bạn đã biết cách tạo công thức SUBTOTAL trong Excel, nhưng thực tế vẫn có rất nhiều người dùng lại gặp rắc rối khi sử dụng hàm này.

Tại sao không chỉ đơn giản là sử dụng một hàm thông thường như SUM, COUNT, MAX …? Câu trả lời bạn sẽ tìm thấy ở ngay dưới đây.

Hàm SUBTOTAL trong Excel dùng để làm gì?

So với các hàm Excel truyền thống khác, hàm SUBTOTAL trong Excel có những lợi thế quan trọng sau.

1. SUBTOTAL tính được giá trị sau khi lọc

Vì hàm SUBTOTAL của Excel bỏ qua các giá trị trong các hàng được lọc, bạn có thể sử dụng để tạo một bản tóm tắt dữ liệu động trong đó các giá trị Subtotal được tính lại tự động theo bộ lọc.

Ví dụ: Nếu lọc bảng để chỉ hiển thị doanh số cho khu vực phía Đông (East), công thức hàm SUBTOTAL phụ sẽ tự động điều chỉnh để tất cả các khu vực khác được xóa khỏi tổng số.

Ghi chú: Vì cả hai bộ hàm số (1-11 và 101-111) đều bỏ qua các ô được lọc, bạn có thể sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 trong trường hợp này đều được.

2. Hàm SUBTOTAL chỉ tính các ô có thể nhìn thấy

Các công thức SUBTOTAL với Function_num 101 đến 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.

Vì vậy, khi bạn sử dụng tính năng Hide (ẩn) của Excel để xóa dữ liệu không liên quan khỏi chế độ xem, hãy sử dụng hàm số 101-111 để loại trừ các giá trị trong các hàng ẩn khỏi Subtotal.

Bỏ qua các giá trị trong các công thức SUBTOTAL lồng nhau

Nếu phạm vi được cung cấp cho công thức Subtotal trong Excel của bạn chứa bất kỳ công thức Subtotal nào khác thì các hàm Subtotal được lồng vào sẽ bị bỏ qua, do đó các số tương tự sẽ không được tính hai lần.

Trong ví dụ dưới đây, công thức tính trung bình AVERAGE của công thức SUBTOTAL(1,C2:C10) sẽ bỏ qua kết quả của các công thức Subtotal trong các ô C3 và C10, như bạn đã sử dụng công thức trung bình AVERAGEA với 2 phạm vi riêng biệt AVERAGE(C2:C5,C7:C9).

Xem thêm: Cách tính trung bình các ô chỉ có giá trị (loại bỏ 0 hoặc ô trống) trong Excel

Cách dùng hàm subtotal trong Excel 

Khi lần đầu tiên sử dụng SUBTOTAL, nó có vẻ rất phức tạp và khó khăn. Nhưng khi tìm hiểu từ những ví dụ đơn giản nhất, bạn sẽ thấy nó không khó để làm chủ. Các ví dụ dưới đây sẽ cho bạn thấy một vài lời khuyên hữu ích và ý tưởng để sử dụng.

Ví dụ 1: SUBTOTAL 9 với SUBTOTAL 109

Như bạn đã biết, hàm SUBTOTAL chấp nhận 2 bộ hàm số: 1-11 và 101-111. Cả hai đều bỏ qua các hàng được lọc, nhưng các số 1-11 bao gồm các hàng được ẩn thủ công, trong khi 101-111 lại loại trừ chúng. Để hiểu rõ hơn về sự khác biệt, hãy xem xét ví dụ sau.

Để tính tổng các hàng được lọc, bạn có thể sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 như hiển thị trong hình ảnh dưới đây:

Nhưng nếu đã ẩn các mục không liên quan theo cách thủ công bằng cách sử dụng lệnh Hide Rows từ thẻ Home > Cells group > Format > Hide & Unhide hoặc bằng cách kích chuột phải vào các dòng, sau đó chọn lệnh Hide.

Bây giờ nếu muốn tổng giá trị trong các hàng hiển thị thì hàm SUBTOTAL 109 là lựa chọn duy nhất:

Ví dụ: Để đếm các ô được lọc không trống bạn có thể sử dụng công thức SUBTOTAL 3 hoặc SUBTOTAL 103.

Nhưng chỉ SUBTOTAL 103 mới có thể đếm chính xác các khoảng trống có thể nhìn thấy nếu có bất kỳ hàng nào được ẩn nào trong phạm vi tính toán:

Ghi chú: SUBTOTAL của Excel với Function_num từ 101-111 sẽ bỏ qua các giá trị trong các hàng ẩn, nhưng không ở các cột ẩn .

Ví dụ: Nếu bạn sử dụng công thức như SUBTOTAL(109,A1:E1) để tính tổng các số trong phạm vi ngang, việc ẩn một cột sẽ không ảnh hưởng đến SUBTOTAL.

Ví dụ 2: Sử dụng hàm IF + SUBTOTAL để tự động tóm tắt dữ liệu

Nếu bạn đang tạo một báo cáo tóm tắt hoặc bảng điều khiển phải hiển thị các bản tóm tắt dữ liệu khác nhau nhưng không có không gian cho tất cả mọi thứ, cách tiếp cận sau đây có thể là một giải pháp:

Bước 1: Trong một ô, tạo danh sách thả xuống có chứa các tên hàm như Total, Max, Min …

Bước 2: Trong một ô bên cạnh danh sách thả xuống, hãy nhập công thức IF lồng nhau với các hàm SUBTOTAL được nhúng tương ứng với các tên hàm trong danh sách thả xuống.

Ví dụ: Giả sử các giá trị cho SUBTOTAL nằm trong các ô C2:C16 và danh sách thả xuống trong ô A17 chứa các mục Total, Average, Max và Min. Vậy thì công thức SUBTOTAL “động” như sau:

=IF(A17=”total”,SUBTOTAL(9,C2:C16),IF(A17=”average”,SUBTOTAL(1,C2:C16),IF(A17=”min”,SUBTOTAL(5,C2:C16),IF(A17=”max”,SUBTOTAL(4,C2:C16),””))))
 

Bây giờ, tùy thuộc vào chức năng nào mà người dùng chọn từ danh sách thả xuống, hàm SUBTOTAL tương ứng sẽ tính toán các giá trị trong các hàng được lọc:

Các lỗi SUBTOTAL phổ biến trong Excel

Nếu công thức SUBTOTAL của bạn trả về lỗi, có thể do một trong những lý do sau:

• VALUE! – Đối số Function_num không phải là số nguyên trong khoảng 1 – 11 hoặc 101 – 111; hoặc bất kỳ đối số Ref nào chứa tham chiếu 3-D.
• DIV/0! – Nếu một hàm tóm tắt được chỉ định phải thực hiện phép chia cho 0.
• NAME? – Tên của công thức SUBTOTAL bị sai chính tả.

Ví dụ 3: Sử dụng hàm SUBTOTAL để tạo báo cáo

Bây giờ chúng ta hãy cùng thử sức với một bài tập về công thức SUBTOTAL để các bạn thấy được ứng dụng mạnh mẽ của nó khi dùng trong báo cáo Excel nhé.

Cho bảng dữ liệu bán hàng như sau:

Yêu cầu đề bài: Dùng hàm SUBTOTAL để xác định kết quả cho:

1. Dòng tổng cộng (vùng D23:E23) theo chức năng hàm SUM.
2. Cột STT (số thứ tự - vùng A3:A22) theo chức năng của hàm COUNTA.

Cách thực hiện:

Để thực hiện yêu cầu 1, chúng ta sẽ tính tổng bằng công thức như sau:

=SUBTOTAL(9, D3:D22)

Trong lúc thao tác thực tế thì các bạn chỉ cần gõ SUBTOTAL là sẽ thấy thông tin của hàm hiện lên để lựa chọn dễ dàng mà không cần nhớ function_num.

Kết quả chúng ta thu được với cột số lượng như sau:

Tương tự như vậy, với cột Số tiền thì chúng ta dùng công thức như sau:

=SUBTOTAL(9, E3:E22)

Kết quả mà chúng ta thu được là:

Vậy là đã làm xong yêu cầu tính tổng, bây giờ chúng ta sẽ đánh số thứ tự bằng hàm SUBTOTAL.

Tại ô A3, các bạn nhập công thức như sau:

=SUBTOTAL(3, $BS3:B3)

Lưu ý: Phần ref1 các bạn cần chọn ô trong một cột có dữ liệu ở tất cả các hàng, nếu bạn chọn ô có dòng trống thì kết quả ra sẽ không chính xác. 

Trong công thức trên các bạn có thấy chúng mình đang cố định công thức ở điểm đầu là vì để khi sao chép công thức thì điểm đầu sẽ cố định, điểm cuối thay đổi thì số thứ tự sẽ tăng dần.

Kết quả của thao tác này như sau:

Bây giờ chúng mình sẽ thêm bộ lọc Filter vào bảng tính để các bạn hình dung rõ hơn chức năng của SUBTOTAL.

Giả sử, chúng mình chỉ lọc ra các dòng dữ liệu liên quan đến Chi nhánh B thì SUBTOTAL sẽ hiển thị kết quả chỉ theo chi nhánh B, không liên quan đến chi nhánh khác. Đồng thời số thứ tự cũng được hiển thị lại từ 1 đến 5, không bị ảnh hưởng bởi STT ban đầu.

Tổng kết

Trên đây là cách sử dụng hàm SUBTOTAL trong Excel với những ví dụ cụ thể. Quá trình sử dụng nếu gặp khó khăn gì trong việc chấm công hãy chia sẻ và bình luận ngay dưới bài viết này để Gitiho sẽ giúp bạn giải quyết mọi thắc mắc nhé.