Bạn đang xem: Hàm SUBTOTAL: Công thức và cách sử dụng hàm SUBTOTAL trong Excel Tại DongnaiArt

Khác với hàm SUM trong Excel, hàm Subtotal một trong các hàm cơ bản Excel được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, đếm số ô, tính trung bình trong Excel, tìm giá trị lớn nhất/nhỏ nhất hay tính tổng giá trị danh sách đã lọc trong Excel mà Quản trị mạng đã hướng dẫn bạn đọc. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUBTOTAL trong Excel.

Nội dung chính về hàm SUBTOTAL

• Công thức SUBTOTAL trong Excel
• Cách sử dụng hàm SUBTOTAL trong Excel
  • 1. Tính tổng các hàng được lọc
  • 2. Đếm các ô được lọc không trống
  • 3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau
• Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:

Bạn đang xem: lệnh subtotal trong excel

• Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
• Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

Lưu ý:

• Hàm SUBTOTAL được thiết kế để 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 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).
• Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

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

1. Tính tổng các hàng được lọc

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:

=SUBTOTAL(9,pham_vi)

pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

Xem thêm: giảm dung lượng file rar online

Xem Thêm : tạo mục lục tự động word 2007

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Xem thêm: Cách nhanh nhất để đăng ký bán hàng dành cho quán ăn trên wiki.onlineaz.vn

Xem Thêm : Giấy thôi trả lương 2021

Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.

Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

• #VALUE!: 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!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).
• #NAME? tên hàm SUBTOTAL sai chính tả.

Xem thêm:

Có thể bạn quan tâm: cách viết đơn xin việc làm

• Hàm SUMPRODUCT trong Excel: Tính tổng tích các giá trị tương ứng
• Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung

• Microsoft Excel hiện đã cho phép nhập dữ liệu từ file PDF
• Đặt pass cho file Excel, cách khóa file Excel bằng mật khẩu
• Office 365 bản quyền mua ở đâu? Vì sao nên mua Office bản quyền?
• Cách hiển thị sheet tab trong Excel
• Cách cố định dòng trong Excel, cố định cột trong Excel
• Cách cố định vị trí biểu đồ trong Excel