Hàm subtotal, công thức sử dụng subtotal trong Excel

Hàm Subtotal so với các hàm khác trong Excel thì linh hoạt hơn và ứng dụng được rộng rãi hơn trong tính toán. Bài viết dưới đây sẽ giới thiệu bạn hàm Subtotal và công thức sử dụng hàm subtotal trong Excel.

Video hướng dẫn cách sử dụng hàm subtotal trong Excel

Công thức và cách sử dụng hàm SUBTOTAL

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

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

Hàm SUBTOTAL này được hỗ trợ tất cả các phiên bản của Excel và công thức hàm Subtotal trong Excel như sau:
=SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó:

• Function_num – Có nghĩa là một số chỉ định hàm nào sẽ sử dụng cho tổng phụ.
• Ref1, Ref2 … – Có nghĩa là một hoặc nhiều ô hoặc phạm vi thành tổng phụ. Tham số Ref đầu tiên là bắt buộc, các tham số khác (tối đa là 254) là tùy chọn.
• Đối số Function_num hoàn toàn 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 được ẩn thủ công.
• 101 – 111 bỏ qua tất cả các ô ẩn – chúng được lọc và ẩn thủ công.

Trong thực tế, bạn không nhất định phải ghi nhớ tất cả các số chức năng ở trên. Ngay khi bạn bắt đầu nhập công thức hàm SUBTOTAL của excel trong một ô hoặc trong thanh công thức, Microsoft Excel sẽ hiển thị được danh sách các hàm số có sẵn cho bạn.

Ví dụ: Đây là cách bạn hoàn toàn có thể tạo công thức 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ạn bấm đúp vào nó, sau đó bạn nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và bạn nhấn Enter. Công thức của hàm SUBTOTAL đầy đủ sẽ như sau:

=SUBTOTAL(9,C2:C8)

Theo cách tương tự, bạn hoàn toàn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm các ô có chứa dữ liệu số và SUBTOTAL 3 để đếm các khoảng trống ….

Như hình ảnh ví dụ 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 trong excel 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ó dữ liệu số bỏ qua khoảng trắng và các ô chứa các giá trị không phải là dữ liệu 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.

3 lý do để sử dụng hàm SUBTOTAL trong Excel

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

1. Hàm SUBTOTAL tính giá trị trong các hàng được lọc

Vì trong hàm SUBTOTAL của Excel bỏ qua các giá trị trong các hàng được lọc, bạn hoàn toà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 bạn thực hiện lọc bảng để chỉ hiển thị doanh số cho khu vực phía Đông (East), công thức của ham SUBTOTAL phụ sẽ tự động điều chỉnh để tất cả các khu vực khác được xóa khỏi giá trị tổng số:

Ghi chú: Vì cả hai bộ hàm số (1-11 và 101-111) đều bỏ qua được các ô đã được lọc, bạn hoàn toà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. Chỉ tính các ô hoàn toàn có thể nhìn thấy

Các công thức của hàm SUBTOTAL trong excel 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 hoàn toàn có thể 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, bạn 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 hàm Subtotal.

3. 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 của hàm Subtotal trong Excel chứa bất kỳ công thức hàm 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 hề được tính hai lần.

Trong ví dụ dưới đây, công thức tính trung bình hàm AVERAGEA của công thức hàm SUBTOTAL(1,C2:C10) sẽ bỏ qua kết quả của các công thức hàm 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 theo công thức AVERAGE(C2:C5,C7:C9).

Sử dụng hàm SUBTOTAL trong Excel – Ví dụ về công thức

Khi lần đầu tiên bạn sử dụng hàm SUBTOTAL, nó có vẻ rất phức tạp và khó khăn cho bạn. Nhưng khi bạn tìm hiểu từ những ví dụ đơn giản nhất, bạn hoàn toàn có thể sẽ thấy nó không khó để làm chủ. Các ví dụ sau đây sẽ cho bạn thấy một vài lời khuyên hữu ích và ý tưởng để bạn 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ố là: 1-11 và 101-111. Cả hai bộ hàm này đề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 các số 101-111 lại loại trừ chúng. Để hiểu rõ hơn về sự khác biệt, bạn hãy xem xét ví dụ sau.

Để tính tổng các hàng được lọc, bạn hoàn toà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 bạn đã ẩ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 đó bạn chọn lệnh Hide.

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

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

Nhưng chỉ hàm SUBTOTAL 103 mới hoàn toàn có thể đếm chính xác các khoảng trống mà nó 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ú: Hàm SUBTOTAL trong 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 xuất hiện ở 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 thực hiện tạo một báo cáo tóm tắt hoặc bảng điều khiển bắt buộc 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 hoàn toàn có thể là một giải pháp:

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

Bước 2: Trong một ô bên cạnh danh sách bạn vừa thả xuống, bạn 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 bạn 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:

=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à bạn có thể chọn từ danh sách thả xuống, hàm SUBTOTAL trong excel 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 hàm SUBTOTAL trong excel của bạn trả về lỗi, hoàn toàn có thể do một trong những lý do sau:

• VALUE! – Có nghĩa là tham 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ỳ tham số Ref nào chứa tham chiếu 3-D .
• DIV/0! – Có nghĩa là 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? – Có nghĩa là tên của hàm SUBTOTAL bị sai chính tả.

Hy vọng những thông tin hữu ích về hàm Subtotal trong Excel dưới đây sẽ giúp bạn có thêm nhiều kiến thức hữu ích!