Hàm Subtotal, các ứng dụng của Subtotal trong Excel

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Hàm Subtotal, các ứng dụng của Subtotal trong Excel

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Trong bài viết này, Chúng tôi sẽ giải thích chi tiết hàm SUBTOTAL và hướng dẫn các bạn cách sử dụng công thức SUBTOTAL để cộng dữ liệu trong các ô nhìn thấy.

1. Hàm subtotal – cú pháp và cách sử dụng

Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…

Hàm SUBTOTAL có ở tất cả các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.

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.

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

Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8:

Hàm subtotal – cú pháp và cách sử dụng

Hàm subtotal – cú pháp và cách sử dụng

Để thêm 1 số xác định chức năng vào công thức, nhấn đúp chuột, đánh dấu phẩy, xác định dãy ô, đóng ngoặc và nhấn Enter. Công thức hoàn chỉnh sẽ như sau:

=SUBTOTAL(9,C2:C8)

Tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô không trống. Trong hình dưới, 3 chức năng khác đang được dùng.

Lưu ý: Khi sử dụng hàm Subtatal trong Excel

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ố.

Bạn đã biết cách lập công thức SUBTOTAL trong Excel, câu hỏi chính là tại sao lại phải học hàm này? Sao không dùng các hàm đơn giản, bình thường như SUM, COUNT, MAX? Lý do sẽ được trình bày dưới đây.

2. Những lý do để dùng hàm Subtotal

So sánh với các hàm Excel truyền thống, hàm Subtotal cho bạn những lợi thế sau:

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

Vì hàm Subtotal bỏ qua các hàng đã được lọc ra, 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ụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, công thức Subtotal sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.

Lưu ý: Vì các 2 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 hàm Subtotal 9 hoặc SUBTOTAL 109.

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

Công thức Subtotal với số xác định chức năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.

2.3. Bỏ qua giá trị trong hàm Subtotal lồng ghép

Nếu dãy ô trong công thức Subtotal có chứa công thức Subtotal khác, hàm 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.

Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10.

Chúc bạn thực hiện thành công.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ