Hàm Subtotal

Hàm subtotal là một hàm tính trong excel sinh ra để khắc phục những nhược điểm của hàm sum (hàm tính tổng một dãy số). Ưu điểm lớn nhất của hàm này là tính hiệu quả của nó nhưng lại khá khó thực hiện, cần người dùng phải nắm chắc cú pháp của nó.

Hàm subtotal là gì?

Hàm subtotal là hàm tính cho một nhóm nhỏ trong danh sách hay bảng số liệu, phụ thuộc vào phép tính bạn lựa chọn trong đối số thứ nhất.

Đối số thứ nhất của hàm tính subtotal là số cố định có giá trị từ 1 đến 11 hoặc 101 đến 111 đại diện cho một phép tính cần thực hiện trên bảng số liệu. Tùy thuộc vào bạn muốn dùng hàm tính nào mà đối số được lựa chọn.

Bảng dưới đây là các đối số cần nhớ của hàm subtotal

Ví dụ nếu đối số của hàm này là 1 thì hàm sẽ được tính toán như hàm Average, giá trị bằng 9 sẽ hoạt động giống hàm Sum,....

Cú pháp hàm subtotal

Hàm subtotal trong bảng excel có cú pháp như sau:

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

Trong đó:

  • Function_num: Là đối số thứ nhất có giá trị là các con số từ 1 đến 11 hoặc 101 đến 111 quy định hàm tính nào sẽ được dùng để tính toán trong subtotal.

  • Ref1, [ref2], …: Là một hoặc nhiều ô, hoặc dãy ô để tính tổng phụ.

Một số lưu ý khi dùng hàm tính này:

  • Hàm subtotal là hàm tính được sinh ra để tính toán cho các cột số liệu theo chiều dọc.

  • Nếu các đối số ref1, ref2,… có chứa hàm tính subtotal thì sẽ được bỏ qua để tránh tính trùng 2 lần.

  • Nếu function_num từ 1 đến 11 thì hàm subtotal tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).

  • Hàm tính subtotal sẽ bỏ qua các vùng dữ liệu bị ẩn do filter.

Cách dùng hàm subtotal

Cách dùng hàm subtotal sẽ phụ thuộc vào yêu cầu tính toán của bạn, phụ thuộc vào hàm được chọn thông qua đối số thứ nhất.

Tính giá trị các hàng được chọn

Vì hàm tính Subtotal sẽ bỏ qua các hàng đã được lọc ra, nên bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ đã được tự động tính lại theo bộ lọc.

Ví dụ, ta có bảng doanh số bán hàng của 3 vùng Bắc, Trung, Nam. Nếu các bạn lọc bảng doanh số bán hàng chỉ của vùng Miền Bắc, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng Trung, Nam khi tính tổng.

Lưu ý: Vì cách hai bộ số xác định chức năng (1 - 11 và 101 - 111) đều bỏ qua các ô đã được lọc ra, bạn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.

Tính các hàng được nhìn thấy

Trong bảng tính sẽ có những hàng, những ô được lọc hay ẩn đi, khi bạn chỉ muốn tính các ô được nhìn thấy thì lúc dùng công thức SUBTOTAL bạn chọn đối số thứ nhất là số nằm trong khoảng 101 - 111.

Nếu bạn muốn tính cả những hàng đã bị ẩn thì khi dùng công thức Subtotal bạn chọn đối số từ 1 - 11, hàm sẽ tự động hiểu và thực hiện phép tính cho bạn.

Khi hàm subtotal được lồng ghép trong phép tính

Nếu dãy ô trong công thức Subtotal có chứa thêm một công thức Subtotal khác, thì công thức subtotal được lồng vào sẽ bị bỏ qua. Vậy nên kết quả trong bảng sẽ không phải tính 2 lần.

Trong hình dưới, công thức tính tổng chính SUBTOTAL(9, E2:E17) bỏ qua kết quả của công thức SUBTOTAL trong ô E8.

Cách sửa lỗi khi dùng hàm subtotal

Vì hàm subtotal là hàm ghép nên nếu bạn không nắm được cú pháp cũng như cách dùng rất dễ xảy ra lỗi trong quá trình tính toán. Hãy cùng xem các lỗi khi dùng hàm tính subtotal và cách sửa lỗi sau đây nhé.

Các lỗi thường gặp

Một số lỗi bạn thường gặp khi sử dụng hàm subtotal trong bảng excel bao gồm:

  • #VALUE!: Lỗi không xác định được giá trị. Thông thường lỗi này xảy ra khi 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!: Lỗi này chỉ xảy ra khi một tổng cụ thể phải chia cho 0 ( giả dụ: tính trung bình cộng hoặc độ lệch chuẩn của một dãy ô không chứa giá trị số).

  • #NAME?: Đây là lỗi tên hàm SUBTOTAL sai chính tả.

Cách khắc phục lỗi khi dùng hàm Subtotal

  • Với lỗi #VALUE!: Bạn nên xem lại cú pháp hàm tính subtotal xem đúng chưa, tham số thứ nhất của hàm phải là một trong các số từ 1 đến 11 hoặc từ 101 đến 111. Với tham chiếu (ref) nên cân nhắc khi chọn các vùng ở một sheet khác.

  • Với lỗi #DIV/0: Lỗi này thường xảy ra khi tham số thứ nhất là 1 hay 101, hàm subtotal hoạt động như hàm average. Bạn nên xem lại các giá trị của các ô được tính xem có giá trị nào không xác định hay không.

  • Với lỗi #NAME? thì bạn chỉ cần xem lại tên hàm đã viết đúng hay chưa? Nếu sai lỗi chính tả khi viết “subtotal” thì bạn chỉ cần sửa lại là xong.

Trên đây là tổng quan về hàm subtotal từ cú pháp, cách dùng đến các lỗi thường gặp và cách khắc phục. Hàm tính subtotal là một hàm rất quan trong trong excel giúp việc xử lý dữ liệu trong bảng tính trở nên dễ dàng hơn. Hy vọng bài viết trên đây có thể giúp ích cho bạn.

giỏ hàng dbk 0 (0) sản phẩm.
DBK VIỆT NAM, https://dbk.vn/ham-subtotal.html,
21/15 đường số 17
Thu Đuc, HCM, 700000
Việt Nam
+84919219111