Phần mềm

Hàm SUBTOTAL trong Excel, cách sử dụng và ứng dụng hàm

Trong Excel, nếu nhắc đến một hàm tính tổng đa năng nhất thì hàm SUBTOTAL là số một. Bạn cần tính toán các số liệu hàng tồn kho. Bạn muốn tính tổng các chi phí phát sinh trong một quý, hay 1 khoảng thời gian,…Hàm SUBTOTAL có thể giải quyết tất cả cho bạn. Hàm SUBTOTAL trong Excel rất đa nhiệm. Nó có thể trả về kết quả như là hàm SUM, COUNT, MAX hay bất cứ hàm khác. Tựu chung lại theo định nghĩa của Microsoft thì: hàm SUBTOTAL sẽ xuất về tổng phụ trong một danh sách hay cơ sở dữ liệu.

Sự đa năng, đa nhiệm của hàm SUBTOTAL được thể hiện rõ ràng. Hàm này có thể giải quyết được tất cả các bài toán về số học. Các bài toán mang tính logic ví dụ: tính trung bình cộng, đếm ô, tìm các giá trị tối thiểu hoặc tối đa,…

Không chần chừ nữa. Hãy cùng tôi khám phá ngay hàm này nhé!

Cú pháp và cách ứng dụng hàm SUBTOTAL trong Excel

Đối với những ai thường xuyên làm việc trên Excel sẽ đều rât quen thuộc với hàm SBTOTAL. Vậy hàm  này có cấu trúc và cách sử dụng ra sao?

Ham-Subtotal-trong-Excel
Hàm Subtotal trong Excel

Cú pháp hàm SUBTOTAL

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

Trong đó:

  • function_num: Là bắt buộc. Các khoảng dao động từ 1-11 hay từ 101-111  đều có ý nghĩa khác nhau.
  • Cụ thể là khi function_num nằm trong dao động từ 1-11. SUBTOTAL sẽ chứa các giá trị bị ẩn bằng cách thủ công.
  • Khi function_num thuộc vào khoảng từ 101-111. SUBTOTAL sẽ loại bỏ các giá trị bị ẩn ra.
  • ref1: là bắt buộc. Tính tổng phụ cho các phạm vi hoặc tham chiếu được đặt tên trước tiên.
  • ref2: Không bắt buộc. Phạm vi hay lần lượt các chuỗi được đặt tên từ 2 –> 254 mà bạn định tính tổng phụ cho nó.

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

Như đã nói ở trên thì hàm SUBTOTAL có thể có nhiều chức năng khác nhau khi tính tổng phụ.

                                                                      

Ham-Subtotal-trong-Excel-01
Hàm SUBTOTAL trong Excel

Bảng dưới đây chỉ rõ cho bạn thấy cách thức ứng dụng hàm:

Funtion_num( bao gồm các giá trị ẩn ) Không tính các giá trị ẩn và hàm tính cốt lõi Giải thích hàm
1 101 – AVERAGE Tính trung bình cộng các số
2 102 – COUNT Đếm ô chứa ký tự số
3 103 – COUTA Đếm ô không bỏ trống
4 104 – MAX Xuất giá trị lớn nhất
5 105 – MIN Xuất giá trị nhỏ nhất
6 106 – PRODUCT Tính số liệu các ô 
7 107 – STDEV Xuất độ lệch chuẩn dựa trên mẫu
8 108 – STDEVP Xuất độ lệch chuẩn dựa trên tất cả số liệu
9 109 – SUM Tính tổng các số
10 110 – VAR Ước chừng độ dao động dựa vào mẫu
11 111 – VARP Ước chừng độ dao động dựa vào toàn bộ số liệu

3 lý do khiến bạn nên sử dụng hàm SUBTOTAL

Để cụ thể hóa về các thế mạnh của hàm SUBTOTAL, hãy đọc kỹ 3 lý do sau:

Lí do 1: Hàm chỉ tính toán với các biến số nhìn thấy được

Với tất cả các công thức SUBTOTAL với bộ function_num từ 101 –> 111. Hàm tính sẽ bỏ qua hết tất cả các ô ẩn và được lọc, ẩn bằng cách thủ công. 

Ví dụ: Ở đây bạn sẽ thử ẩn đi các hàng từ cột ngang thứ 5 –> 10 nhé. Bạn quét 1 đường bao phủ và nhấn giữ CTRL + 9

 

                               

Ham-Subtotal-trong-Excel-02
Lý do sử dụng hàm SUBTOTAL

Sau khi nhấn CTRL 9:

Ham-Subtotal-trong-Excel-03
Lý do sử dụng hàm SUBTOTAL

Cột ngang từ số 5–>9 đã được ẩn.

Tiếp đó, bạn hãy sử dụng hàm SUBTOTAL để demo:

Lý do sử dụng hàm SUBTOTAL

Kết quả là: Khi bạn tính tổng bằng SUBTOTAL. Kết quả của hàm vẫn được tính ra như bình thường. Chứng tỏ hàm chỉ tính được với các ô nhìn thấy được.

Hàm SUBTOTAL trong Excel

                         

Lí do 2: Không chấp nhận các giá trị Subtotal ghép vào nhau

Nếu bạn bắt đầu tính toán các biến bằng hàm SUBTOTAL. Bạn phải biết rằng: hàm sẽ bỏ qua các giá trị subtotal ghép vào giữa các biến số. Cụ thể là: ở cột ngang số 11 đang tính SUBTOTAL từ C3-C10. Ở cột ngang thứ 20 là kết quả hàm SUBTOTAL từ C12-C19. Nếu ta tính thêm 1 hàm SUB nữa ở dưới thì hàm tính sau cùng sẽ bỏ qua các hàm SUB đã tính trước đó.

                                                      

Ham-Subtotal-trong-Excel-05
Lý do sử dụng hàm SUBTOTAL

Lí do 3: Chỉ tính toán các giá trị thuộc các hàng đã lọc

Ví dụ cụ thể cho bạn: Ở bảng tính này đang để tất cả các giá trị chưa qua lọc.Các bạn có thể thấy hàm SUBTOTAL đang tính tất cả các giá trị trên bảng. Bây giờ mình sẽ tiến hành lọc cột KHU VỰC với hai biến khu vực là ” Bắc và Nam “                                                             

Ham-Subtotal-trong-Excel-06
Lý do sử dụng hàm SUBTOTAL

Sau khi lọc: Hàm SUBTOTAL ở hình ban đầu sau khi lọc sẽ tự động tính lại cho bạn chỉ ở 2 khu vực: Bắc và Nam

Ham-Subtotal-trong-Excel-07
Lý do sử dụng hàm SUBTOTAL

Như vậy, hàm SUBTOTAL giúp cho bạn tính tự động ngay sau khi lọc mà không cần nhập lại phép tính. 

Ví dụ điển hình về hàm SUBTOTAL 

Ví dụ 1: Sự khác nhau giữa SUBTOTAL-9 và SUBTOTAL-109

Bạn hãy nhìn bảng số liệu dưới đây và quan sát: Đây là SUBTOTAL 9 khi chưa ẩn bất cứ hàng nào. Kết quả cho ra là                                                                 

Ham-Subtotal-trong-Excel-08
Ví dụ về hàm SUBTOTAL

Và đây là SUBTOTAL khi ẩn cột ngang từ 5 –> 10: Kết quả vẫn ra như cũ                                     

Ham-Subtotal-trong-Excel-09
Ví dụ về hàm SUBTOTAL

Hãy thử với SUBTOTAL-109 và vẫn là tính tổng hàng ẩn nhé:  

Ví dụ về hàm SUBTOTAL

Kết luận đó là: ” Hàm SUBTOTAL-9 có thể tính tổng của cả các hàng đã ẩn. Còn SUBTOTAL-109 chỉ tính tổng được của các biến số mà nhìn thấy được “.

Ví dụ 2: Tính nhiều hàm cùng 1 lúc và lưu ý

Hãy nhìn ví dụ dưới đây nhé: Bạn có thể tính cùng 1 lúc nhiều hàm tính khác nhau( Vd: Tính MAX, MIN, AVERAGE,…)                                        

Ham-Subtotal-trong-Excel-10
Ví dụ về hàm SUBTOTAL

Nhưng khi bạn lọc từng vị trí. Ví dụ giờ tôi lọc để lại Bắc và Nam. Hãy cùng xem kết quả:

Ham-Subtotal-trong-Excel-11
Ví dụ về hàm SUBTOTAL

Hàm SUBTOTAL sẽ tự động tính tất cả các giá trị và biến số đã được lọc qua. Bạn thấy nó có thực sự đa năng và hữu dụng rồi chứ.

Các lỗi thường gặp trong hàm SUBTOTAL – những lỗi thường gặp phải

Có 3 lỗi chính thường thấy khi mọi người sử dụng hàm SUBTOTAL trong tính toán. Đó là:

  • #NAME: Là do tên của 1 hàm SUBTOTAL bị điền sai
  • #VALUE: Nguyên nhân chính xảy ra lỗi này là vì đối số của 1 function_num không là 1 số nguyên dao động từ 1-11 hoặc là từ 101-111                              
Ham-Subtotal-trong-Excel-12
Các lỗi thường gặp trong hàm SUBTOTAL
  • #DIV/0!  : Lỗi này xảy ra bởi nguyên nhân một tổng cụ thể sẽ phải chia cho 0 ( ví dụ: đếm 1 hàng không chứa biến số là số )

Dưới đây là toàn bộ các kiến thức cô đọng nhất về hàm SUBTOTAL trong Excel dành cho bạn. Tất cả các kiến thức đều được chúng tôi chọn lọc kỹ lưỡng và đã qua làm việc bài bản. Nó sẽ giúp ích cho bạn rất nhiều trong công việc văn phòng của bạn. Bài viết rất mong nhận được sự đánh giá từ bạn đọc. Xin cảm ơn và trân trọng!

>>> Xem thêm: Hàm trim trong excel

>>> Xem thêm: 9 hàm thống kê thông dụng trong Excel bạn không nên bỏ qua – digitalfuture

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button