Hàm Subtotal là gì và cách áp dụng trong Excel

Là một công cụ không thể thiếu trên Excel ngày nay, Hàm Subtotal là lựa chọn thông minh giúp tối ưu hóa công việc. Bạn muốn biết cách sử dụng nhanh chóng và hiệu quả? Khám phá ngay!

Hiểu rõ về Hàm Subtotal

Hàm Subtotal trong Excel không chỉ đơn thuần tính tổng, mà còn linh hoạt trong việc phân loại dữ liệu. Bạn có thể đếm số lượng ô, tính trung bình và xác định giá trị lớn nhất/ nhỏ nhất một cách dễ dàng.

Hàm Subtotal - Đối tác đáng tin cậy cho việc tính tổng phụ trong Excel

Bí quyết thành công với Công thức Hàm Subtotal

Người dùng thường ưa chuộng hàm Subtotal trên phiên bản Excel từ 2007 đến 2016. Hàm này linh hoạt, tính toán đa dạng từ trung bình, tổng đến giá trị lớn nhất và nhỏ nhất. Công thức sử dụng như sau:

=SUBTOTAL (function_num, ref1, [ref2], …)

Trong đó:

• Function_num là số từ 1 đến 11 hoặc 101 đến 111, xác định loại hàm cần tính.
• Ref1 là phạm vi tham chiếu được đặt tên mà người dùng muốn tính tổng phụ.
• Ref2… có thể từ 2 đến 254 phạm vi tham chiếu khác cần tính tổng phụ.

Những điều cần lưu ý khi sử dụng hàm Subtotal

Hiện nay, hàm Subtotal mang lại nhiều ưu điểm cho người dùng. Tuy nhiên, hãy nhớ một số điều quan trọng trước khi bắt đầu sử dụng, để đảm bảo công việc diễn ra suôn sẻ.

• Người dùng khi áp dụng giá trị từ 1-11 hoặc 101-111 trong Excel có thể gặp lỗi #VALUE!. Điều này đặc biệt xảy ra khi tham chiếu đến ô 3D, tức là cùng một ô trên nhiều trang tính và được tham chiếu trong một phạm vi.
• Khi sử dụng phạm vi ô như A1:D1, giá trị ẩn sẽ tự động xuất hiện. Đây là một hạn chế của Subtotal mà không thể khắc phục.
• Việc sử dụng Subtotal trên dữ liệu đảm bảo rằng các giá trị ẩn luôn bị bỏ qua, bất kể đối số function_num được sử dụng.
• Hàm Subtotal có thể được sử dụng như một hàm lồng nhau chứa các công thức có hàm IF.
• Nếu công thức Subtotal không thuộc phạm vi dữ liệu Refl, nó sẽ bị bỏ qua, loại bỏ khỏi phép tính tổng thể.
• Excel hỗ trợ việc sử dụng tối đa 254 phạm vi ô trong công thức Subtotal.

Những điều cần lưu ý quan trọng trước khi sử dụng hàm Subtotal trong Excel

Cách sử dụng hàm Subtotal trong Excel để tính tổng phụ và ví dụ

Thông tin về cách sử dụng hàm Subtotal trong Excel để tính tổng phụ thu hút sự quan tâm đặc biệt của người dùng. Mytour sẽ chia sẻ với bạn các bước thực hiện ngay sau đây:

Tính tổng vùng được lọc

Khi cần lọc tổng KPI của đội A, người dùng sẽ thực hiện các thao tác như sau:

Bước 1: Chọn bảng chứa dữ liệu cần tính và nhấn vào 'Trang Chính'. Tiếp theo, chọn 'Sắp xếp và Lọc' và nhấn 'Lọc'.

Chọn bảng dữ liệu cần tính

Bước 2: Bấm chọn biểu tượng tam giác ngược ở tiêu đề cột 'Đội'. Sau đó, chọn 'A' và nhấn OK.

Bấm chọn biểu tượng tam giác ngược ở tiêu đề cột

Bước 3: Bảng dữ liệu của đội A sau khi được Excel lọc, tại ô cần lấy kết quả, nhập công thức: =SUBTOTAL(9,D2:D11). Trong đó: function_num = 9 là giá trị đối số tương ứng với hàm cần sử dụng. Giá trị đối số này sẽ hiển thị khi nhập công thức hàm.

Bảng dữ liệu của đội A sau khi được Excel lọc

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

Để đếm số lượng sinh viên thuộc nhóm C có link bài tập, người dùng lọc dữ liệu và nhập công thức:  =SUBTOTAL(3,D4:D14). Trong đó:

• function_num = 3 là giá trị đối số cho hàm cần sử dụng. Giá trị này hiển thị khi nhập công thức hàm Subtotal. Sau đó, thực hiện đếm số ô không trống và sử dụng hàm COUNTA với đối số là 3.
• ref1 = D4:D14 là phạm vi cần tính tổng.

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

Người dùng có thể sử dụng giá trị đối số là 3. Ngoài ra, cần đếm cả các giá trị đã bị ẩn thủ công do Hide. Hoặc sử dụng giá trị 103 và bỏ qua đếm các giá trị đã bị ẩn thủ công. Điều này giúp đếm số ô trống trong các hàng đã được lọc.

Đánh số thứ tự nhanh

Nếu muốn đánh số thứ tự cho danh sách dữ liệu mà không có hàng trống, người dùng nhập công thức =SUBTOTAL(3,$B$2:B2). Trong đó:

• function_num là 3 tương ứng với việc sử dụng COUNTA để đếm ô không trống.
• ref1 là $B$2:B2, đếm số ký tự xuất hiện trong khoảng $B$2:B2. Ở đây, ô B2 được cố định với dấu '$' trước địa chỉ hàng và cột. Khi sao chép công thức, giá trị sẽ tự động điều chỉnh thành $B$2:B3, $B$2:B4, $B$2:B5,... Điều này đảm bảo hàm đếm đúng và mang lại kết quả như mong muốn.

Đánh số thứ tự nhanh

Sự khác biệt giữa hàm Subtotal và hàm SUM

Hiện nay, sự khác biệt giữa hàm Subtotal và hàm SUM là vấn đề được nhiều người quan tâm. Sự khác biệt này sẽ được thể hiện qua ba phương diện như sau:

Sự khác biệt giữa hàm Subtotal và hàm SUM

Phần kết

Dưới đây là thông tin về hàm Subtotal, giúp công việc của người dùng trở nên thuận lợi hơn. Bạn có thể áp dụng hàm Subtotal để thực hiện các tính toán, phép logic, đếm số ô, tính trung bình,… Để biết thêm chi tiết, hãy liên hệ ngay với Mytour!