Hàm SUBTOTAL trong Excel – Cách dùng và ví dụ minh họa

Các ý chính trong bài viết

Hàm SUBTOTAL trong Excel có chức năng tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong hàm còn hỗ trợ thêm 11 function, nhờ đó nó có sức mạnh tính toán tương đương các hàm SUM, AVERAGE, COUNTA, PRODUCT,…Chính vì vậy hàm SUBTOTAL vô cùng linh hoạt, có thể thực hiện các phép tính toán trên bảng dữ liệu Excel một cách nhanh chóng và đơn giản. Bài viết dưới đây mình sẽ hướng dẫn các bạn cách sử dụng hàm SUBTOTAL chi tiết nhất.

Cách dùng hàm SUBTOTAL

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

Trong đó:

  • Function_num: con số xác định chức năng thực hiện
  • 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 có thể là 1 trong 2 loại sau:

  • 1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công.
  • 101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.
Function_num Chức năng Mô tả
1 101 AVERAGE Tính trung bình các con số
2 102 COUNT Đếm số ô chứa giá trị số
3 103 COUNTA Đếm số ô không trống
4 104 MAX Tìm giá trị lớn nhất
5 105 MIN Tìm giá trị nhỏ nhất
6 106 PRODUCT Tính kết quả của các ô
7 107 STDEV Tính độ lệch chuẩn mẫu dựa trên mẫu
8 108 STDEVP Tính độ lệch chuẩn dựa trên toàn bộ số
9 109 SUM Cộng các số
10 110 VAR Ước tính độ dao động dựa trên mẫu
11 111 VARP Ước tính độ dao động dựa trên toàn bộ số

Ví dụ minh họa về cách sử dụng hàm SUBTOTAL trong Excel

Ví dụ 1

Cho bảng dữ liệu sau. Hãy tính tổng số sản phẩm và tìm ra lượng sản phẩm còn ít nhất là bao nhiêu và nhiều nhất là bao nhiêu.

Ví dụ cách sử dụng hàm SUBTOTAL
Ví dụ cách sử dụng hàm SUBTOTAL

Với bài toàn này, chúng ta sẽ sử dụng hàm SUBTOTAL và dựa vào bảng giá trị function_num ở trên để chọn hàm và tính được lần lượt các giá trị như sau:

  • Nhập công thức tính tổng tại ô D15:  =SUBTOTAL(9;D5:D13)
  • Nhập công thức tính giá trị nhỏ nhất tại ô D16:  =SUBTOTAL(5;D5:D13)
  • Nhập công thức tính giá trị lớn nhất tại ô D17:  =SUBTOTAL(4;D5:D13)

Khi đó ta thu được kết quả như sau:

Kết quả thu được khi sử dụng hàm SUBTOTAL
Kết quả thu được khi sử dụng hàm SUBTOTAL

Ví dụ 2

Yêu cầu tính tổng các sản phẩm của mỗi nhà cung cấp.

Để thực hiện điều này, bạn cần phải sắp xếp tên nhà cung cấp trùng nhau ở gần nhau. Ở ví dụ này mình đã sắp xếp trước rồi nên mình không cần làm việc này nữa. Sau khi sắp xếp xong, bạn chọn vùng dữ liệu cần tính toán rồi vào tab Data ->Subtotal

Chọn Data ->Subtotal
Chọn Data ->Subtotal

Khi đó hộp thoại Subtotal hiện lên như sau:

Hộp thoại Subtotal
Hộp thoại Subtotal

Trong đó có các thông số sau:

  • At each change in: Chọn Nhà cung cấp vì chúng ta sẽ tính tổng cho từng Nhà cung cấp.
  • Use function: Chọn hàm cần để tính. Ở đây tính tổng nên sẽ chọn là SUM.
  • Add subtotal to: Chọn cột cần tính toán. Ở đây tính số lượng nên chọn cột Số lượng.

Ấn OK và kết quả thu được như sau

Kết quả thu được khi sử dụng hàm SUBTOTAL
Kết quả thu được khi sử dụng hàm SUBTOTAL

Ưu điểm nổi bật của hàm SUBTOTAL trong Excel

Cũng trong ví dụ 1 bên trên bên cột E mình sẽ sử dụng các hàm SUM, MIN, MAX để tính và có kết quả như hình dưới

Kết quả khi sử dụng các hàm SUM, MIN, MAX
Kết quả khi sử dụng các hàm SUM, MIN, MAX

Có thể thấy, kết quả tính toán là giống nhau. Bây giờ mình sẽ làm như sau và bạn sẽ thấy ưu điểm nổi bật của hàm SUBTOTAL

Trong bảng dữ liệu nay, chúng ta dùng Filter để ẩn số liệu của nhà cung cấp Lazada. Lúc này, kết quả thu được như sau:

Sự khác biệt khi dùng hàm SUBTOTAL và các hàm tính toán thông thường
Sự khác biệt khi dùng hàm SUBTOTAL và các hàm tính toán thông thường

Ta nhận thấy, kết quả đã có sự khác biệt khi sử dụng hàm SUBTOTAL

  • Khi sử dụng hàm SUBTOTAL thì tổng là 34 vì hàm SUBTOTAL chỉ tính tổng của các sản phẩm hiển thị của nhà cung cấp Adayroi, còn hàm SUM vẫn tính cả tổng các mặt hàng đã bị ẩn.
  • Giá trị nhỏ nhất thì cả 2 hàm giống nhau vì giá trị trong bảng lúc hiển thị và lúc ẩn đều là 7.
  • Giá trị lớn nhất khi sử dụng hàm SUBTOTAL là 10 vì hàm SUBTOTAL chỉ dò tìm các giá trị hiển thị còn hàm MAX vẫn dò tìm cả các giá trị bị ẩn.

Có 1 điểm đặc biệt trong kết quả này nếu bạn chú ý kỹ sẽ thấy đó là trong các công thức trên mình dùng các giá trị function_num là 9,5,4 (các function_num sẽ tính toán bao gồm cả các giá trị ẩn). Nhưng trong này nó lại không tính. Đó là do hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter). Còn trường hợp bạn ẩn bằng công cụ Hide thì nếu không muốn tính các giá trị ẩn thì dùng function_num là 109, 105 và 104.

Trên đây là cách sử dụng của hàm SUBTOTAL trong Excel. Bạn có thể xem thêm các bài tập minh họa kèm đáp án tại mục NỘI DUNG KHÓA dưới đây. Để mở khóa nội dung này, bạn làm theo hướng dẫn sau:

  • Bước 1: Click vào mục VIDEO HƯỚNG DẪN LẤY MẬT KHẨU
  • Bước 2: Nhập mật khẩu vào ô nhập khẩu và bấm XEM NGAY
Nội dung khóa
Bạn cần nhập mật khẩu để mở khóa nội dung này

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 *

Bài viết liên quan