Hướng Dẫn Chi Tiết Cách Dùng Công Thức Lệnh Hàm SUBTOTAL Trong ...

Hàm SUBTOTAL được ứng dụng nhiều trong công việc tính toán vì nó có những ưu điểm là có thể tính tổng, tính giá trị trung bình, tính max min, đếm… của một ô, dãy ô cần tính tổng phụ. Vậy cú pháp và cách dùng hàm SUBTOTAL trong excel như thế nào? Mời bạn theo dõi nội dung bài viết. 

Hàm SUBTOTAL trong Excel là gì? 

– Hàm SUBTOTAL trong Excel được hiểu là hàm trả về kết quả tổng phụ trong danh sách hoặc cơ sở dữ liệu. Tổng phụ được tính ở đây không phải là các con số trong phạm vi ô được xác định.

– Hàm SUBTOTAL linh hoạt hơn các hàm excel khác khi thực hiện các phép tính toán số học và logic khác nhau như tính tổng, tính trung bình, đếm ô, tìm giá trị lớn nhất, nhỏ nhất…

Cú pháp hàm SUBTOTAL trong Excel

– Cú pháp hàm SUBTOTAL: SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó: 

• Function_num. Là con số xác định chức năng thực hiện phép tính được qui định trong bảng.
• Ref1, Ref2, …. Là một ố, nhiều ô hoặc dãy ô để tính tổng phụ. Giá trị Ref 1 là giá trị cần thiết phải có để công thức được hoạt động. Còn giá trị từ Ref 2 đến 254 là tuỳ chọn.

– Số xác định chức năng thực hiện có 2 loại để tính toán như sau: 

• Số 1 – 11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công.
• Số 101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.

– Bảng con số chức năng thực hiện tính toán được qui định như sau:

– Khi thực hiện tính toán bằng công thức hàm SUBTOTAL, bạn không cần phải nhớ cụ thể từng con số này mà khi nhập công thức vào ô thì nó sẽ tự động hiển thị các con số này để bạn lựa chọn. 

– Ví dụ: Bạn có thể tạo công thức SUBTOTAL 9 để tính tổng các giá trị trong các ô từ C2 đến C8 như hình dưới đây: 

– Để lựa chọn vùng tính toán, bạn nhập thêm phần giá trị vùng vào phía sau để công thức hàm SUBTOTAL đầy đủ là được. 

– Công thức hàm SUBTOTAL đầy đủ nhập cho ô C9 như sau: =SUBTOTAL(9,C2:C8).

– Tương tự như vậy, bạn lựa chọn giá trị Function_num (con số chức năng) để thực hiện tính toán. Ví dụ như trong hình dưới đây, sử dụng hàm SUBTOTAL để tính giá trị trung bình, giá trị lớn nhất và giá trị nhỏ nhất.

Chú ý: Khi sử dụng hàm SUBTOTAL với chức năng tính tổng, giá trị trung bình, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

Cách sử dụng hàm SUBTOTAL trong Excel

Vậy tại sao hàm SUM, AVERAGE, COUNT, MAX, MIN đã có rồi mà chúng ta lại có thêm hàm SUBTOTAL trong Excel nữa? Có sự khác biệt gì khi chúng ta sử dụng hàm SUBTOTAL này. 

Câu trả lời là: hàm SUBTOTAL trong Excel sẽ tính các giá trị các ô trong bảng được lọc ra thay vì tính toàn bộ giá trị trong bảng. Hàm SUBTOTAL sẽ tính các ô nhìn thấy được và hàm SUBTOTAL sẽ bỏ qua các giá trị đã sử dụng hàm SUBTOTAL để tính tổng phụ. Cụ thể như thế nào, chúng ta cùng đi vào chi tiết với các trường hợp cụ thể sau.

Tính giá trị của các hàng được lọc

– Khi bạn lọc một mảng dữ liệu nào đó trong bảng, bạn hoàn toàn có thể sử dụng hàm SUBTOTAL để tính tổng phụ một cách linh hoạt. 

– Như trong ví dụ dưới đây, bạn lọc để lấy dữ liệu là doanh số bán hàng vùng miền Đông (east) trong bảng dữ liệu thì công thức hàm SUBTOTAL sẽ tự động điều chỉnh chỉ tính tổng các giá trị còn lại sau khi được lọc, còn các ô không xuất hiện sau khi lọc (tức các ô được lọc) sẽ không tính giá trị. 

 Lưu ý thêm là vì 2 bộ số xác định chức năng (1-11 và 101-111) đều thực hiện chức năng tính toán bỏ qua các ô không xuất hiện sau khi được lọc nên trong trường hợp này bạn sử dụng nhóm số chức năng xác định nào cũng được.

Tính các ô nhìn thấy được 

– Công thức hàm SUBTOTAL sử dụng nhóm số chức năng từ 101 đến 111 khi tính toán sẽ bỏ qua tất cả các ô ẩn đã được lọc và ẩn thủ công.

– Vậy nên, khi bạn sử dụng tính năng ẩn (Hide) với ô, vùng chọn nào đó trong bảng dữ liệu thì công thức hàm SUBTOTAL sẽ tự động loại trừ các ô đó khỏi vùng tính toán của nó, nó sẽ không nhận các giá trị đó để tính toán.

Các công thức SUBTOTAL lồng nhau được bỏ qua giá trị khi tính 

– Nếu bạn đã sử dụng công thức SUBTOTAL để tính tổng phụ của các giá trị trong dãy thì công thức hàm SUBTOTAL sẽ xác định các ô có chứa hàm SUBTOTAL và loại bỏ nó ra khi tính toán. Có nghĩa là hàm SUBTOTAL sẽ không tính toán giá trị lồng với nhau. 

–  Với ví dụ như ở dưới đây, công thức tính trung bình chính SUBTOTAL(1, C2:C10) đã bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10.

Ví dụ hàm SUBTOTAL trong Excel

Ban đầu bạn sẽ cảm thấy hàm SUBTOTAL tương đối phức tạp, rắc rối do chưa hiểu thật sự chức năng và công dụng của nó. Tuy nhiên, trên thực tế công việc hàng ngày, đôi khi bạn không tính tổng của một dãy liên tục mà cần tính tổng từng nhóm nhỏ nào đó thì bạn sẽ thấy được ưu điểm của hàm này. Ví dụ sau đây sẽ giúp bạn hiểu rõ hơn về các số chức năng của hàm SUBTOTAL.

VD1 – SUBTOTAL 9 với SUBTOTAL 109

– Như đã giới thiệu về nhóm số xác định chức năng, có 2 bộ số là 1 – 11 và 101 – 111. Cùng so sánh 2 nhóm số này.

– Giống nhau: 

+ Cả 2 nhóm số đều tính toán bỏ qua các ô, các dữ liệu đã được lọc đi. 

– Khác nhau:

+ nhóm 1 – 11 sẽ tính cả các hàng đã được ẩn thủ công.

+ nhóm 101 – 111 sẽ loại bỏ các hàng được ẩn khi tính toán. 

– Ví dụ tính tổng các hàng được chọn, bạn có thể dùng SUBTOTAL 9 và SUBTOTAL 109 như hình dưới đây. (không có hàng nào bị ẩn). 

– Nhưng nếu đã có hàng 10, 11, 12 bị ẩn như ví dụ dưới đây với lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide thì bạn sẽ không tính được những giá trị đó, mà chỉ tính toán được những giá trị hiện hữu (được nhìn thấy). 

– Một các đơn giản thì số xác định chức năng cho phép lấy hoặc không thấy dữ liệu bị ẩn. Nhóm 1 – 11 thì lấy tất cả, nhóm 101 – 111 thì chỉ lấy giá trị nhìn thấy trên bảng.

– Với các số xác định chức năng khác cũng dùng tương tự như vậy. Ở ví dụ dưới đây, thực hiện đếm các ô không trống trong vùng chọn. SUBTOTAL 103 sẽ chỉ đếm các ô không trống mà chúng ta nhìn thấy được bảng. Còn hàm dùng SUBTOTAL 3 sẽ đếm tất cả các ô trống của vùng chọn, bao gồm các ô trống đã bị ẩn mất đi khi sử dụng tính năng ẩn thủ công Hide.

VD2 – Hàm IF + SUBTOTAL để tự động tóm tắt dữ liệu

– Trong trường hợp bạn muốn tính nhiều giá trị tổng phụ là SUM, AVERAGE, MAX, MIX đối với vùng dữ liệu được chọn thì bạn có thể thực hiện thanh sổ (drop – down) để bảng trình bày được đẹp mắt hơn. Các bước thực hiện như sau:

Bước 1. Tạo danh sách thanh sổ xuống (drop-down) có chứa tên các hàm như bạn sử dụng như Total, Average, Max, Min…

Bước 2. Trong ô hiện kết quả trả về thì bạn sử dụng hàm IF lồng có chứa công thức hàm SUBTOTAL tương ứng với các hàm trong danh sách.

– Ví dụ dưới đây, bạn tính tổng phụ trong vùng C12:C16 và danh sách tính toán trong ô A17 là chứa Total, Average, Max, Min thì công thức hàm SUBTOTAL được nhập trong ô B17 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),””))))

– Như vậy, bạn tùy chọn danh sách sổ xuống và thực hiện câu lệnh theo điều kiện IF với danh sách bạn chọn hiện kết quả là được.

Xem thêm: Cách dùng hàm SUBTOTAL trong excel 

Các lỗi thường gặp khi dùng hàm SUBTOTAL

– Khi sử dụng hàm SUBTOTAL bạn sẽ thấy excel báo những lỗi cụ thể như sau: 

VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

#DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số)

#NAME? tên hàm SUBTOTAL sai chính tả.

Như vậy, với hàm SUBTOTAL tính tổng phụ thì bạn ứng dụng nhiều trong công việc chia tách từng mảng nhỏ khi tính tổng, thuận lợi tính hàng hóa các vùng khác nhau, các điều kiện được phân chia khác nhau. Hàm SUBTOTAL thật sự không khó, bởi vì nó ít được giới thiệu trong chương trình học phổ thông nên bạn thấy lạ thôi. Hãy xem video chúng tôi chia sẻ và đọc lại thật kĩ nội dung bài để nắm chắc hàm SUBTOTAL và cách dùng hàm SUBTOTAL trong  excel.