Công thức SUBTOTAL 

Bài viết hôm nay lytc.edu.vn  sẽ hướng dẫn cách dùng công thức SUBTOTAL chi tiết cùng ví dụ giúp bạn có thể hiểu rõ hơn về hàm này.

Công thức SUBTOTAL

Ý nghĩa của hàm SUBTOTAL trong excel là để tính tổng phụ(không phải tổng các con số trong dãy ô xác định) trong cơ sở dữ liệu, hàm này được sử dụng để làm phép logic hay tính toán trung bình, tìm giá trị nhỏ nhất/ lớn nhất….

Hàm này có mặt trên hầu hết các phiên bản từ excel 2007 cho đến 2016, kể cả các phiên bản cũ hơn vẫn có thể sử dụng được.

Cú pháp của hàm SUBTOTAL như sau :

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

Trong đó các giá trị như :

  • Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ (Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn)
  • Function_num: con số xác định chức năng thực hiện

Số xác định chức năng thực hiện gồm có 2 loại như sau:

  • 1 -11 bỏ qua các ô đã được lọc ra, nhưng các hàng được ẩn thủ công sẽ được giữ lại
  • 101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công

công dụng Công thức SUBTOTAL 

Khi bạn bắt đầu nhập hàm SUBTOTAL trên thanh công cụ hay ô trống nào đó, excel sẽ tự động đưa ra các con số cho bạn, chẳng hạn như ví dụ sau : công thức tính tổng các giá trị có trong các ô từ C2 cho đến C8 là SUBTOTAL9
công thức tính tổng các giá trị

Công thức chính xác sẽ là =SUBTOTAL(9,C2:C8)

Tương tự như cách trên, bạn có thể tính công thức trung bình đếm trung bình là SUBTOTAL 1, đếm ô chứa số là SUBTOTAL 2, đếm ô không trống là SUBTOTAL 3, như ví dụ dưới đây các bạn có thể thấy 3 công thức khác đang được dùng:

công thức tính tổng SUBTOTAL 

Lưu ý: Excel sẽ bỏ qua ô chứa giá trị không phải số hay ô trống, chỉ tính các ô chứa số nếu như bạn dùng công thức SUBTOTAL có chức năng tính tổng như Average hay Sum.

Tại sao phải dùng hàm SUBTOTAL

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

Bạn có thể tính tổng các dữ liệu 1 cách nhanh chóng và linh hoạt, hay các giá trị trong tổng phụ sẽ được tự động tính, bởi vì hàm SUBTOTAL sẽ bỏ qua các hàng đã được lọc.

Chẳng hạn như khi ta loại bỏ doanh số bán hàng theo Miền Đông, thì hàm SUBTOTAL sẽ tự động bỏ qua các vùng khác khi thực hiện phép tình tổng.

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

Tính các ô nhìn thấy

Công thức sử dụng hàm SUBTOTAL với số xác định chức năng 101-111 sẽ bỏ qua các ô đã ẩn thủ công, đã lọc, vì vậy mà chức năng Excel’s Hide để ẩn các dự liệu không cần thiết, chức năng 101-111 để loại bỏ các giá trị từ các hàng ẩn.

Bỏ qua giá trị

Nếu trong dãy ô thuộc công thức SUBTOTAL có chứa thêm 1 công thức hàm SUBTOTAL khác nữa thì công thức hàm SUBTOTAL sau sẽ bị bỏ qua, và con số trong bảng sẽ không cần phải tính toán 2 lần

Qua hình minh họa bên dưới, các bạn có thể thấy công thức tính trung bình chính SUBTOTAL(1,C2:C10) đã bỏ qua kết quả của hàm SUBTOTAL trong ô C3 và C10

Bỏ qua giá trị 

VÍ DỤ sử dụng hàm SUBTOTAL

SUBTOTAL 9 VS. SUBTOTAL 109

Hàm SUBTOTAL trong excel có thể chấp nhận 2 bộ số xác định chức năng là 101-111 và 1-11, cả 2 bộ số này đều bỏ qua các hàng đã được lọc bỏ, tuy nhiên, 101-111 thì loại trừ các hàng thủ công, còn 1-11 thì lại ẩn.

Ví dụ: Bạn có thể dùng cả 2 hàm SUBTOTAL 109 và SUBTOTAL 9 như hình minh họa dưới đây:

SUBTOTAL 9 VS. SUBTOTAL 109

Nhưng nếu các hàng không có liên quan đã được bạn dùng lệnh hide để ẩn thủ công, mà giờ bạn lại cần tổng giá trị trong các hàng nhìn thấy được , bạn nên dùng SUBTOTAL 109

SUBTOTAL 9 VS. SUBTOTAL 109 1

Các số chức năng khác cũng thực hiện theo cách tương tự như trên, chẳng hạn như dùng SUBTOTAL 3 hay SUBTOTAL 103 để đếm ô không trống đã chọn, nhưng hàm SUBTOTAL 103 sẽ đếm chính xác nếu như có hàng ẩn bất kỳ nào có trong dãy

SUBTOTAL 9 VS. SUBTOTAL 109 2

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 sẽ bỏ qua giá trị trong các hàng bị ẩn, không phải cột bị ẩn, Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn sẽ không ảnh hưởng đến tổng phụ.

VÍ DỤ 2: TÍNH TỔNG DỮ LIỆU bằng IF + SUBTOTAL

Nếu bạn cần đưa ra các dữ liệu tổng kết khác nhau nhưng lại thiếu các chổ trống, thì bạn hãy thực hiện theo cách sau:

  • Tại 1 ô, tạo danh sách theo dạng drop-down có chứa tên của các hàm như Min, Max, Total…
  • Trong ô kế bên, bạn hãy thêm hàm IF có chứa công thức SUBTOTAL tương ứng vào để giúp excel hiểu rõ hơn

Ví dụ để tính tổng phụ các ô C12:C16, và danh sách trong ô A17 chứa các hàm như Min, Max, Total, công thức hàm SUBTOTAL sẽ được viết 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),””))))

Tùy vào hàm trong danh sách được bạn chọn, công thức SUBTOTAL sẽ tính giá trị trong các hàng đã được bạn chọn.

TÍNH TỔNG DỮ LIỆU bằng IF + SUBTOTAL

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

Nếu kết quả trả về lỗi, thì nguyên nhân có thể là một trong những điều sau:

  • #NAME? nghĩa là tên hàm SUBTOTAL bị sai chính tả
  • #DIV/0! lỗi này thường xảy ra khi 1 tổng cụ thể phải chia cho 0
  • #VALUE! nghĩa là số xác định chức năng không nằm trong khoảng 101-111 hoặc 1-11 hay có tham chiếu (ref) là tham chiếu 3D.

Thật đơn giản và dễ hiểu phải không nào .

==>> Xem thêm Tổng hợp cách sử dụng công thức IF Excel đơn giản

Hy vọng bài viết về cách sử dụng công thức subtotal trong excel trên đây đã giúp bạn hiểu hơn về công dụng của hàm này.

Đánh giá bài viết

Leave a Reply

Your email address will not be published. Required fields are marked *