Cách dùng hàm Subtotal trong Excel chi tiết nhất 2023
Hàm Subtotal trở thành một công cụ vô cùng hữu ích trong việc giải quyết các yêu cầu tính toán và thống kê phức tạp. Bằng cách đọc và áp dụng Cách dùng hàm Subtotal dưới đây, bạn có thể nhanh chóng tính tổng, đếm, tìm giá trị lớn nhất/nhỏ nhất và thực hiện các phép tính khác trên dữ liệu theo nhóm hoặc phạm vi cụ thể. Cùng tìm hiểu!Cách dùng hàm Subtotal trong Excel chi tiết nhất 2023
1. Hàm Subtotal là gì? Công thức hàm Subtotal
Hàm Subtotal trong Excel là một hàm được sử dụng để tính tổng phụ (subtotal) trong một danh sách hoặc cơ sở dữ liệu. Hàm này có khả năng thực hiện nhiều tác vụ tính toán khác nhau và được sử dụng phổ biến trong các phiên bản Excel từ 2007 đến 2016.
Công thức hàm SUBTOTAL
Hàm Subtotal có thể được sử dụng để tính tổng, đếm số ô, tính trung bình, tìm giá trị lớn nhất hoặc nhỏ nhất trong một phạm vi dữ liệu. Người dùng có thể chọn các loại tính toán bằng cách chỉ định một số gọi là function_num trong công thức hàm Subtotal như sau:
=SUBTOTAL(function_num, ref1, [ref2], …)
Trong đó:
Function_num là một số từ 1 đến 11 hoặc từ 101 đến 111, xác định loại tính toán được áp dụng cho tổng phụ. Mỗi số tương ứng với một loại tính toán khác nhau.
Ref1 là phạm vi dữ liệu đầu tiên mà người dùng muốn tính tổng phụ.
[Ref2]… là các phạm vi dữ liệu khác mà người dùng muốn tính tổng phụ. Có thể chỉ định từ 2 đến 254 phạm vi.
Ví dụ, để tính tổng các giá trị trong phạm vi A1:A10, bạn có thể sử dụng hàm: =SUBTOTAL(9, A1:A10). Trong đó, số 9 tương ứng với tính toán tổng, và A1:A10 là phạm vi dữ liệu cần tính tổng phụ.
2. Cách dùng hàm Subtotal
2.1 Cách dùng hàm Subtotal để tính tổng các vùng được lọc
Giả sử bạn có một bảng dữ liệu và bạn cần lọc ra tổng KPI của đội A. Để thực hiện thao tác này, bạn xem hướng dẫn Cách dùng hàm Subtotal để tính tổng dưới đây:
Bước 1: Bôi đen bảng chứa dữ liệu cần tính, sau đó truy cập vào tab ‘Home’ và chọn ‘Sort and Filter’, tiếp theo chọn ‘Filter’.
Lọc thông tin
Bước 2: Nhấp vào dấu mũi tên hình tam giác ngược ở tiêu đề cột ‘Đội’, sau đó chọn ‘A’ và nhấn OK.
KPI của đội A
Sau khi lọc, Microsoft Excel cho kết quả như sau:
Kết quả lọc
Bước 3: Bảng dữ liệu của đội A đã được lọc ra bằng Excel. Bạn hãy nhập công thức vào ô cần lấy: =SUBTOTAL(9,D2:D11)
Giải thích công thức:
function_num = 9 là giá trị đối số tương ứng với hàm được sử dụng. Khi bạn nhập công thức hàm SUBTOTAL, giá trị đối số này sẽ hiển thị. Trong trường hợp này, chúng ta cần tính tổng, vì vậy chúng ta chọn hàm SUM, tương ứng với số 9.
ref1 = D2:D11 là phạm vi cần tính tổng
Nhập công thức
Thu được kết quả sau:
Kết quả tính tổng KPI của đội A thu được khi sử dụng hàm SUBTOTAL
Xem video hướng dẫn chi tiết Cách dùng hàm Subtotal tính tổng các vùng được lọc tại đây:
Nếu người dùng chỉ cần đánh số thứ tự cho danh sách dữ liệu, xem hướng dẫn Cách dùng hàm Subtotal để đánh số dưới đây!
Nhập công thức =SUBTOTAL(3,$B$2:B2)
Trong đó:
function_num là 3, tương ứng với cách tính là COUNTA, tức đếm số ô không trống.
ref1 là $B$2:B2, đếm số ký tự phát sinh trong phạm vi $B$2:B2. Ở đây, ô B2 được cố định bằng dấu “$” ở trước địa chỉ hàng và cột. Khi công thức được sao chép, giá trị sẽ thay đổi theo $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.”
Công thức đánh số thứ tự bằng SUBTOTAL
Thu được kết quả sau:
Kết quả
Kéo mũi tên nằm ở góc phải bên dưới ô kết quả vừa tính xuống hết cột. Như vậy, bạn vừa áp dụng công thức cho các ô còn lại.
Áp dụng công thức cho các ô còn lại
Bạn sẽ thu về kết quả như hình dưới đây:
Kết quả Cách dùng hàm Subtotal để điền số thứ tự
Xem video hướng dẫn chi tiết Cách dùng hàm Subtotal để điền số thứ tự tại đây:
2.3 Cách dùng hàm Subtotal đếm các ô được lọc không trống
Giả sử bạn đang có một bảng dữ liệu như được minh họa bên dưới. Nhiệm vụ của bạn là đếm số lượng sinh viên thuộc nhóm A đã nộp link bài tập để tiện thống kê.
Tại ô cần lấy kết quả, bạn nhập: =SUBTOTAL(3,D2:D11)
Giải thích công thức:
function_num = 3 là giá trị đối số tương ứng với hàm được sử dụng. Khi bạn nhập công thức hàm SUBTOTAL, giá trị đối số này sẽ hiển thị. Trong trường hợp này, chúng ta cần đếm các ô không trống, vì vậy chúng ta chọn hàm COUNTA, tương ứng với số 3.
ref1 = D4:D14 là phạm vi cần tính tổng.
Nhập công thức
Kết quả thu được:
Kết quả
Xem video hướng dẫn chi tiết Cách dùng hàm Subtotal để đếm các ô được lọc không trống tại đây:
Thông báo #VALUE!: Số xác định chức năng không nằm trong khoảng từ 1 đến 11 hoặc từ 101 đến 111, hoặc có tham chiếu (ref) là tham chiếu 3D.
3.2 #DIV/0!
Thông báo #DIV/0!: Xảy ra khi một tổng cụ thể phải được chia cho 0. Ví dụ, khi tính trung bình cộng hoặc độ lệch chuẩn của một dãy ô mà không có giá trị số nào.
Để khắc phục, bạn cần kiểm tra và đảm bảo không có giá trị 0 và ô trống trong phạm vi dữ liệu.
Để khắc phục, hãy kiểm tra lại các biến số và đảm bảo rằng chúng được nhập và sử dụng đúng cách trong hàm subtotal.
Lời kết:
Bài viết đã trình bày những khái niệm cơ bản về hàm Subtotal trong Excel và Cách dùng hàm Subtotal. Hàm này không chỉ linh hoạt, mang tính ứng dụng cao, mà còn mang lại tiện ích vượt trội so với các hàm chuyên biệt khác. Hiểu rõ thông tin và biết cách sử dụng hàm Subtotal là một kỹ năng thiết yếu, đặc biệt đối với những người làm việc với bảng tính dữ liệu lớn. Chúc bạn thành công khi áp dụng hàm này vào công việc! Bạn đọc truy cập chuyên mục việc làm kế toán của muaban.net để tìm cho bản thân một công việc như ý!
Hàm subtotal rất cần thiết khi làm những công việc bán thời gian như thu ngân, văn phòng,… xem ngay công việc tại Mua Bán:
