Hàm SUBTOTAL là gì? Hàm SUBTOTAL khác gì hàm SUM?

ham-subtotal-la-gi

Như các bạn đã biết, hàm SUM là hàm cơ bản nhất trong Excel dùng để tính tổng các giá trị. Tuy nhiên, để làm tốt hơn những gì hàm SUM có thể, bạn hãy sử dụng hàm SUBTOTA. Vậy Hàm SUBTOTAL là gì? Hãy cùng Tino Group tìm hiểu qua bài viết này nhé.

Hàm SUBTOTAL là gì?

Định nghĩa hàm SUBTOTAL

Hàm SUBTOTAL là một hàm của Excel có chức năng dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu.

Đa số các hàm trong Excel được thiết kế với một nhiệm vụ riêng biệt, nhưng hàm SUBTOTAL lại rất linh hoạt, bạn có thể sử dụng hàm này để tính toán hoặc làm phép logic, đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất,..

Bạn có thể sử dụng hàm SUBTOTAL trên các phiên bản Excel từ 2007 đến 2016.

Ứng dụng của hàm SUBTOTAL trong kế toán

Hàm SUBTOTAL được sử dụng rất nhiều bởi dân văn phòng, đặc biệt là kế toán.

Cú pháp và cách sử dụng của hàm SUBTOTAL

Cú pháp

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

Trong đó:

Lưu ý:

Ngoài ra, bạn không cần phải nhớ các con số chức năng. Khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ tự động đưa ra danh sách các con số cho bạn.

Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8

Cú pháp:

=SUBTOTAL(9,C2:C8)

Lưu ý: Khi bạn dùng SUBTOTAL với các chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ thực hiện trên các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

Lý do tại sao nên sử dụng hàm SUBTOTAL trong Excel?

Như đã chia sẻ, hàm SUBTOTAL sở hữu hai bộ số xác định chức năng từ 1-11 và từ 101-111. Cả hai đều bỏ qua các hàng đã được lọc ra, nhưng các chức năng từ 1-11 sẽ bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về sự khác biệt này, bạn hãy xem ví dụ sau đây:

Để tính tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

Giả sử các hàng không liên quan trong bản đã được ẩn thủ công bằng cách dùng lệnh Hide Rows, nhưng bạn chỉ muốn tính tổng giá trị trong các hàng nhìn thấy, hãy lựa chọn SUBTOTAL 109:

Tiếp theo, để đếm ô không trống đã chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm các ô không trống nhìn thấy nếu có bất kỳ hàng ẩn nào trong dãy.

Ngoài ra, nếu dãy ô có chứa hai công thức SUBTOTAL, SUBTOTAL được lồng vào sau sẽ bị bỏ qua. Do đó, con số trong bảng sẽ không cần phải tính 2 lần.

Để dễ hình dung hãy nhìn hì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ác SUBTOTAL khác.

Sự khác biệt giữa hàm SUBTOTAL và hàm SUM

Trong Excel, hàm SUM được định nghĩa là một phép tính tổng. Nhiệm vụ cơ bản của hàm SUM là tính tổng số những con số và tham số có trong các ô và dãy ô.

Để tìm hiểu sự khác biệt giữa hàm SUBTOTAL và hàm SUM, bạn hãy xem qua ví dụ dưới đây:

Trong trường hợp trên, nếu chúng ta thêm thao tác gì thì kết quả của hàm SUM là đúng.

Tuy nhiên, nếu chúng ta chỉ cần lọc những người ở bộ phận Kinh doanh. Khi đó kết quả của bảng tính vẫn sẽ là:

Vì vậy, chúng ta sẽ không thể sử dụng hàm SUM nữa mà thay vào đó là hàm SUBTOTAL với cú pháp:

=SUBTOTAL(9,C2;C6)

Mặc dù hàm tham chiếu số liệu từ C2:C6 nhưng kết quả chỉ được tính cho các ô C2, C3, C4 và các ô đã bị ẩn gồm C5, C6 sẽ không được tính. Đây chính là sự khác biệt rất lớn giữa SUM và SUBTOTAL

Cụ thể, sự khác biệt giữa hai hàm là:

Mục đích sử dụng:

Cú pháp:

Trường hợp hay sử dụng trong kế toán:

Trên đây là những gì cơ bản nhất về hàm SUBTOTAL bao gồm cú pháp và các ứng dụng thực tế. Hy vọng bạn có thể tận dụng tối đa những tính năng hữu ích mà hàm này mang lại để công việc được diễn ra hiệu quả hơn.

FAQs về hàm SUBTOTAL

Những lỗi thường gặp hàm SUBTOTAL là gì?

Nếu hàm SUBTOTAL trả về lỗi, nguyên nhân rất có thể đến từ một trong những lý do sau đây:

  • #VALUE!: Lỗi này xảy ra khi số xác định chức năng không thuộc các khoảng 1-11 hay 101-111 hoặc có tham chiếu (ref) là tham chiếu 3D.
  • #DIV/0!: Lỗi này xảy ra khi có một tổng nào đó chia cho 0 (ví dụ: Khi bạn tính trung bình cộng hoặc tính độ lệch chuẩn của 1 dãy ô mà không chứa giá trị số)
  • #NAME?: Lỗi này xảy ra khi tên hàm SUBTOTAL sai chính tả.

Tại sao hàm SUBTOTAL hiệu quả hơn hàm SUM?

Ưu điểm rất lớn của hàm SUM là sự tiện lợi, nhanh chóng. Hàm SUM viết nhanh và đơn giản cũng như ít có tham số hơn, đặc biệt là Excel còn hỗ trợ người dùng một phương thức khác có tên AutoSum vô cùng tiện lợi.

Đối với hàm SUBTOTAL, ưu điểm lớn nhất chính là sự hiệu quả. Bởi vì công việc của chúng ta không chỉ là tính ra kết quả, mà kết quả đó phải có tính tùy biến cao và luôn đúng với mọi trường hợp. Những thao tác như ẩn hay lọc dữ liệu rất thường được sử dụng trong Excel, muốn tính được kết quả thì không thể dùng hàm SUM thông thường được, chúng ta phải cần tới hàm SUBTOTAL.

Chức năng 101-111 khác với 1-11 như thế nào?

Công thức SUBTOTAL với số xác định chức năng 101-111 cho phép bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng một vài chức năng để ẩn dữ liệu không liên quan, bạn cần phải dùng số chức năng 101-111 nhằm loại bỏ các giá trị từ những hàng ẩn ra khỏi tổng phụ.

SUBTOTAL có tính các giá trị bị ẩn do Filter không?

Filter là công cụ để lọc và hiển thị dữ liệu có mục đích, giúp người thực hiện dễ dàng theo dõi và thống kê. Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

CÔNG TY CỔ PHẦN TẬP ĐOÀN TINO

Exit mobile version