Cách dùng Hàm SUBTOTAL trong Excel - Lê Ánh HR

Nội dung bài viết được cố vấn chuyên môn bởi Th.S Nguyễn Quốc Tới - Giảng viên giảng dạy và ứng dụng công nghệ thông tin từ cơ bản đến nâng cao tại Trường ĐH Công nghệ GTVT - Giảng viên Khóa học tin học văn phòng tại trung tâm đào tạo thực hành Lê Ánh.

Khi học tin học văn phòng chúng ta thường được nghe nhắc nhiều đến hàm Subtotal. Vậy bạn đã biết gì về Subtotal rồi? Nó là một hàm mang đến nhiều lợi ích cho người dùng, giúp người dùng tính toán dễ dàng hơn khi gặp quá nhiều con số.

Để hiểu rõ hơn về hàm Subtotal trong Excel, bạn đừng bỏ qua bài viết hướng dẫn của Lê Ánh HR dưới đây nhé!

I. Hàm Subtotal Là Gì? Ý Nghĩa Của Hàm Subtotal

1. Hàm Subtotal

Hàm Subtotal là hàm được ứng dụng nhiều trong Excel, thường dùng để tính tổng, tính trung bình, giá trị lớn nhất hoặc giá trị nhỏ nhất trong các danh sách hoặc các cơ sở dữ liệu.

2. Ý nghĩa của hàm Subtotal

Subtotal trong tiếng Anh dịch ra hiểu nôm na là tính tổng phụ. Subtotal là hàm được dùng để tính tổng phụ của một bảng, hay một cơ sở dữ liệu nào đó trong Excel. Subtotal là công cụ hữu hiệu giúp người dùng đặc biệt là những người phải làm việc với các bảng tính toán một cách dễ dàng hơn.

Từ phiên bản Excel 2003 hàm Subtotal đã được ứng dụng, hàm này giúp cho người dùng Excel gia tăng các tùy chọn cho số đầu tiên của hàm. Do đó người dùng thường xuyên làm việc với các con số lớn, phức tạp sẽ được đơn giản hóa.

Tuy nhiên nó có một nhược điểm đó là phải bắt buộc ghi nhớ đối với số đầu tiên mà nó đại diện cho phép tính của bảng dữ liệu cũ. Excel phiên bản 2007, hệ thống đã được cải tiến, hàm này đã có thể tự ghi nhớ đối số để tránh bị nhầm lẫn, hay quên lãng.

»»» Tham khảo: Khóa Học Tin Học Văn Phòng - Lê Ánh HR

3. Hàm Subtotal khác gì hàm Sum

Hàm Subtotal khác hàm Sum trên 3 phương diện:

- Thứ nhất là mục đích:

+ Hàm Sum dùng để tính tổng cố định hay ta có thể hiểu là tính tổng chung cho tất cả các đối tượng mà không tách thành tổng các nhóm nhỏ (Và giá trị không bị thay đổi cho đến khi thực hiện lệnh Filter). Và khi ta lồng các hàm Sum với nhau thì số liệu sẽ mặc định tính 2 lần.

+ Hàm Subtotal dùng để tính tổng cố định và không cố định, tức là tính tổng riêng cho từng nhóm, giá trị sẽ thay đổi khi thực hiện lệnh Filter. Và khi ta lồng ghép các hàm Subtotal với nhau thì số liệu sẽ không bị tính 2 lần.

- Thứ hai là cú pháp

+ Hàm Sum: SUM (number1;[number2];...)

+ Hàm Subtotal: SUBTOTAL(function_num;ref1;ref2;...)

Xem thêm: Cách sử dụng hàm SUMIF trong Excel

- Thứ ba là tình huống sử dụng

+ Hàm Sum được sử dụng trong trường hợp tính tổng phát sinh nợ có trong sổ nhật ký chung. Hoặc tính khấu hao tài sản cố định trong bảng phân bổ 211, 142, 242,...

+ Hàm Subtotal được dùng để tính tổng các phát sinh đầu, trong và cuối kỳ của một tài khoản cấp 1 bất kỳ trên bảng cân đối tài khoản, bảng cân đối kế toán... Hoặc tính tổng số tiền tồn cuối ngày trong số tiền mặt, tiền gửi ngân hàng,..

II. Cách Dùng Hàm Subtotal Trong Excel

1. Công thức hàm Subtotal

Ta có công thức như sau: SUBTOTAL(function_num; ref1; ref2; ...)

Hàm Subtotal

Trong đó có:

• Function_num: là những con số từ 1 đến 11 và 101 đến 111, những con số này quy định hàm nào sẽ được dùng để tính toán trong hàm Subtotal.
• Ref1: đây là phạm vi vùng tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng
• Ref2...: đây có thể có 1 hoặc nhiều ô, hoặc cả một dãy ô dùng để tính tổng phụ, nhưng tối đa 254.

Lưu ý:

- Nếu như lồng các hàm Subtotal với nhau và đặt tại các đối số ref1, ref2,... thì các hàm này sẽ bị qua, tránh trường hợp bị tính toán 2 lần.

- Nếu như các hằng số từ 1-11 thì hàm Subtotal sẽ thực hiện tính toán bao gồm cả các giá trị ẩn trong vùng dữ liệu chứa giá trị ẩn đó.

- Nếu các hằng số được chọn từ 101-111 thì hàm Subtotal sẽ bỏ qua, không tính các giá trị ở hàng ẩn.

2. Cách sử dụng hàm Subtotal qua ví dụ thực tế

*Ví dụ cách sử dụng hàm Subtotal để tính tổng đơn giản

- Tính tổng các hàng được chọn lọc

• Muốn tính được chúng ta phải biết công thức hàm Subtotal, trong trường hợp này sẽ là: =SUBTOTAL (9, pham_vi)
• Phạm vi được nói đến ở đây chính là vùng dữ liệu mà bạn muốn tính tổng.

*Ví dụ cách sử dụng hàm Subtotal để tính tổng có điều kiện

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

Các bạn hãy quan sát ví dụ sau, đề bài yêu cầu đếm số lượng sinh viên của nhóm C đã có link bài tập để thống kê. Chúng ta làm theo những bước sau:

Bước 1: Chúng ta lọc ra các sinh viên thuộc nhóm C

Bước 2: Nhập công thức =SUBTOTAL(3,D4:D14)

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

Sau khi thực hiện các bước sau bạn sẽ đạt được kết quả.

- Đánh số thứ tự nhanh

Đối với ví dụ bên dưới, cách thực hiện rất dễ dàng, bạn nhập công thức sau =SUBTOTAL(3,$B$2:B2) vào ô A2

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

Trong đó có:

• Funcation_num là 3: Ứng với hàm COUNTA - đếm ô không trống.
• ref1 là $B$2:B2: $B$2:B2 là khoảng được đếm ký tự phát sinh, $B$2 là giá trị tuyệt đối của B2.

Sau khi thực hiện các bước như trên, bạn có được kết quả:

- Tính số tiền trung bình có được

Đề bài yêu cầu sử dụng hàm Subtotal để tính số tiền trung bình thu được sau khi bán hàng

Bước 1: Bạn nhập công thức =SUBTOTAL(1,F2:F9)

Hàm Subtotal hàm tính tổng phụ trong Excel

Trong đó có:

• SUBTOTAL chính là lệnh hàm
• Function_num là 1
• Vùng dữ liệu được tham chiếu là từ F4:F9

Bước 2: Nhấn Enter và xem kết quả.

III. Những Lưu Ý Khi Sử Dụng Hàm Subtotal

- Đối với tham chiếu ô 3D (cùng một ô, trên nhiều trang tính khác nhau, nhưng được tham chiếu trong một phạm vi) thì khi sử dụng giá trị khác 1-11 hoặc 101-111 sẽ làm cho Excel mặc định trả về lỗi #VALUE!

Xem thêm: Tổng Hợp Các Lỗi Trong Excel Thường Gặp Và Cách Khắc Phục

- Ngoài những ưu điểm của mình thì hàm Subtotal có một hạn chế và tới hiện tại vẫn chưa khắc phục được. Đó là khi ta sử dụng phạm vi các ô ngang như A1:D1, thì các giá trị ẩn sẽ tự động được đưa vào (bất kể giá trị đối với số function_num)

- Mặc dù ta chỉ cần 1 phạm vi ô để công thức hoạt động tuy nhiên Excel lại cho phép ta sử dụng đến tối đa 254 phạm vi ô trong công thức Subtotal.

- Nếu bất kỳ một công thức Subtotal nào mà nằm trong phạm vi dữ liệu ref1, thì Subtotal sẽ mặc định bỏ qua nó và loại trừ nó khỏi phép tính tổng thể.

- Như những hàm khác thì Subtotal cũng có thể được sử dụng lồng ghép nhau, bao gồm những công thức có chứa hàm IF.

Xem thêm: 

• Cách Sử Dụng Hàm LEN Trong Excel
• Các Hàm Trong Excel Cơ Bản
• Mẫu Bảng Chấm Công Excel

Bài viết trên của chúng tôi đã cung cấp đầy đủ và chi tiết cho các bạn thông tin về “Hàm Subtotal”. Cảm ơn bạn đã theo dõi và chúc bạn thành công trong cuộc sống.

Lê Ánh HR - Nơi đào tạo hành chính nhân sự uy tín nhất hiện nay, đã tổ chức thành công rất nhiều khóa học hành chính nhân sự, khóa học C&B, khóa học tin học văn phòng ... và hỗ trợ kết nối tuyển dụng cho hàng nghìn học viên.

Để biết thông tin chi tiết, bạn vui lòng liên hệ với chúng tôi theo số hotline: 0904.84.88.55 để được tư vấn trực tiếp về các khoá học này.

Ngoài các khóa học hành chính nhân sự chất lượng thì trung tâm Lê Ánh còn cung cấp các khóa học kế toán online - offline, khóa học xuất nhập khẩu chất lượng tốt nhất hiện nay.