Kiến thức

Hướng dẫn dùng hàm Subtotal trong excel-Ví dụ và video

Excel cơ bản

/

Tin học văn phòng

Hướng dẫn dùng hàm Subtotal 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, 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. Để hiểu rõ hơn về cách sử dụng hàm Subtotal trong excel, các bạn cùng theo dõi bài viết của mình nhé

Bạn đang xem: Hướng dẫn dùng hàm Subtotal trong excel-Ví dụ và video

1/ Cú pháp của hàm Subtotal:

Cú pháp hàm SUBTOTAL:

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.

Số xác định chức năng thực hiện có 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.
Hàm subtotal trong excel
Cú pháp hàm Subtotal trong excel

Lưu ý: Bạn không cần phải nhớ hết các con số chức năng. Khi nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra hướng dẫn về danh sách các con số cho bạn.

Tính năng được áp dụng nhiều nhất của hàm subtotal là tính Trung bình (Average: 1 hoặc 101) và tính Tổng (Sum: 9 hoặc 109).

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

Tại sao lại là Subtotal mà không phải dùng Sum hay Average. Chúng ta hãy cùng đi tiếp sang phần 2 – Lý do sử dụng hàm Subtotal trong excel.

2. Tại sao lại dùng hàm Subtotal – Các trường hợp sử dụng hàm Subtotal trong excel:

2.1 Tính giá trị trong danh sách được lọc

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, ta 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.

Vd: Trong bảng tồn kho, ta điền công thức subtotal, số chức năng là 9, dùng để tính tổng số lượng sản phẩm của kho số 1.

Kết quả đầu ta được tổng toàn bộ sản phẩm trong kho (chưa lọc điều kiện)

Kết quả tiếp theo ta lọc phân loại các sản phẩm tốt, kết quả số lượng hàng trong kho 1 thay đổi, chỉ tính tổng các hàng sau khi đã lọc dữ liệu.

2.2 Tính toán các ô được nhìn thấy.

Bạn lưu ý rằng, khái niệm các ô được nhìn thấy sẽ không gồm 2 nhóm:

  • Nhóm 1: Các ô bị ẩn đi do dùng chức năng lọc dữ liệu
  • Nhóm 2: Các ô bị ẩn đi 1 cách thủ công

Hàm Subtotal với 2 bộ số chức năng:

  • Từ 1 đến 9: tính toán và bỏ qua các ô đã được lọc ra (nhóm 1)
  • Từ 101 đến 109: tính toán và bỏ qua các ô đã được lọc ra (nhóm 1) và bỏ qua các ô bị ẩn thủ công (nhóm 2)

2.3 Bỏ qua các giá trị khi hàm Subtotal lồng nhau

Trong trường hợp hàm subtotal lồng nhau, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần. Ở ví dụ trong hình bên dưới, bạn sẽ thấy kết quả Tổng cộng đã bỏ qua giá trị ở Tổng kho 1 và Tổng kho 2.

Hàm subtotal lồng nhau

Xem video hướng dẫn chi tiết cách dùng hàm subtotal có ví dụ minh hoạ:

3. Các lỗi thường gặp khi dùng hàm Subtotal().

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

#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ả.

Ngoài ra, để sử dụng excel 1 cách hiệu quả, bạn nên tìm hiểu thêm về các hàm và tính năng khác:

Một số hàm cơ bản trong excel:

  • Các hàm tính tổng trong excel

  • Sử dụng Vlookup để tìm kiếm

  • Các hàm về ngày tháng

Một số tính năng, công cụ khác trong excel:

  • Học cách sử dụng Pivot Table trong excel

  • Cách định dạng trước khi in file excel

Chuyên mục: Kiến thức

Related Articles

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 *

Check Also
Close
Back to top button