Hàm SUBTOTAL trong Excel có thể được sử dụng để thêm, đếm, đánh số tự động, v.v. các ô không trống trong phạm vi dữ liệu đã lọc. Nhưng nhiều bạn vẫn chưa thực sự hiểu rõ về SUBTOTAL và ứng dụng của nó trong học tập và công việc.
Vì tên của hàm Subtotal không gợi nhiều ý nghĩa cho chúng ta như Sum, Count, Vlookup, .. Hóa ra một điều khó khăn khác về Subtotal là các đối số và ý nghĩa của chúng. Hãy cùng theo dõi bài viết sau của kế toán Bảo Tín về cách sử dụng hàm TOTAL và các ví dụ minh họa nhé.
1. Công thức về hàm SUBTOTAL trong Excel
Hàm SUBTOTAL trong Excel được áp dụng trong nhiều trường hợp, đó là tính tổng, trung bình, đếm số ô, tìm giá trị dữ liệu lớn nhất/nhỏ nhất, đánh số tự động, v.v.
Công thức về hàm SUBTOTAL:
= SUBTOTAL(function_num, ref1, ref2,…)
Trong đó:
function_num: nếu function_num từ 1 – 11 thì hàm SUBTOTAL thực hiện các phép tính bao gồm các giá trị ẩn trong dải dữ liệu chứa giá trị ẩn đó. Nếu function_num được chọn từ 101 – 111 thì hàm SUBTOTAL bỏ qua nó, nó không đếm các giá trị trong hàng ẩn.
Xem thêm: Cách sử dụng hàm COUNTIF trong EXCEL đơn giản nhất
Ref1, Ref2,…: 1 hoặc nhiều ô hoặc dãy ô để tổng phụ lên đến 254.
Mã số Function_num như sau:
Lưu ý:
Hàm SUBTOTAL được sử dụng cho các cột số liệu theo chiều dọc
Ref1, ref2,… có chứa hàm SUBTOTAL thì được bỏ qua để tránh đếm hai lần.
SUBTOTAL sẽ bỏ qua các dữ liệu bị ẩn bởi Filter ( dữ liệu không thỏa mãn bộ lọc )
Điều quan trọng là phải phân biệt giữa giá trị bị ẩn và giá trị bị từ chối do không thỏa mãn bộ lọc.
Giá trị bị ẩn là giá trị mà bạn chủ động ẩn bằng cách thao tác trên hàng đã chọn > nhấp chuột phải và chọn hide
Các giá trị bị từ chối vì chúng không khớp với bộ lọc là các giá trị không khớp với yêu cầu tìm kiếm của bạn.
Xem thêm: Cách dùng hàm SUMIF trong EXCEL đơn giản và dễ hiểu nhất
2. Ví dụ minh họa về hàm SUBTOTAL trong Excel
Tính tổng vùng được lọc
Giả sử bạn có bảng sau và bạn cần lọc các KPI tổng thể cho đội A tiến hành như sau:
Bước 1: Chọn vùng trong bảng chứa dữ liệu cần tính → Home → Sort and Filter → Filter
Bước 2: Nhấp vào mũi tên hình tam giác ngược trong tiêu đề cột “Đội”, đánh dấu chọn “A” và nhấn OK
Bước 3: Excel lọc bảng dữ liệu cho Đội A. Tại thời điểm này, trong ô nhận kết quả, hãy nhập:
= SUBTOTAL(9,D2:D11)
Giải thích công thức:
function_num = 9 là giá trị của đối số tương ứng với hàm cần sử dụng. Bạn thấy giá trị đối số này được hiển thị khi bạn nhập công thức của hàm SUBTOTAL. Vì chúng ta cần tính tổng ở đâ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
Bạn sẽ nhận được kết quả như sau:
Xem thêm: Dịch vụ kế toán trọn gói giá rẻ
Lưu ý: Bạn có thể sử dụng giá trị đối số là 9 (tính tổng các giá trị đã bị ẩn) hoặc 109 (bỏ qua giá trị đã bị ẩn) để tính tổng các hàng đã lọc.
Đếm các ô đã lọc không trống
Giả sử bạn có một bảng dữ liệu như hình bên dưới. Nhiệm vụ của bạn là đếm số học sinh trong nhóm C có liên kết đến các bài tập cho mục đích thống kê.
Đầu tiên, tiến hành lọc các sinh viên trong nhóm C bằng thao tác lọc tương tự như đã hướng dẫn ở phần trước.
Sau khi lọc dữ liệu trong ô để nhận kết quả, hãy nhập:
= SUBTOTAL(3,D4:D14)
Giải thích công thức:
function_num = 3 là giá trị của đối số tương ứng với hàm cần sử dụng. Giá trị đối số này được hiển thị khi bạn nhập công thức của hàm SUBTOTAL. Vì chúng ta cần đếm các ô không trống ở đâ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
Bạn sẽ nhận được kết quả như sau:
Lưu ý: Bạn có thể sử dụng giá trị đối số là 3 (cũng đếm các giá trị ẩn theo cách thủ công do chức năng Hide) hoặc 103 (bỏ qua, không đếm các giá trị ẩn theo cách thủ công) để đếm số ô không trống trong các hàng đã lọc
Đánh số thứ tự nhanh
Nếu bạn chỉ cần đánh số một danh sách dữ liệu liên tục (không có hàng trống) như hình minh họa bên dưới, bạn có thể nhập một công thức đơn giản vào ô A2 như sau:
= SUBTOTAL(3,$B$2:B2)
Giải thích công thức:
Function_num là 3: Tùy thuộc vào phương pháp tính toán, COUNTA đếm các ô không trống.
Ref1 là $B$2:B2: Số lượng ký tự nằm trong phạm vi $B$2:B2, trong đó ô B2 được đặt bằng “$” ở phía trước hàng và địa chỉ ô. Khi công thức được sao chép xuống dưới, giá trị thay đổi như sau: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm chính xác và trả về kết quả mong muốn.
Xem thêm: Hướng dẫn dùng hàm UPPER trong Excel để chuyển chữ thường thành chữ in hoa
Sau đó kéo và sao chép công thức (bằng cách chọn ô chứa công thức cần sao chép → di chuột qua góc dưới bên phải của ô và kéo dấu cộng vừa xuất hiện ở cuối ô cuối cùng cần điền, sử dụng kéo và thả chuột), kết quả thu được như sau:
Tuy nhiên, nếu bạn gặp phải dữ liệu “khó” với các hàng trống và không trống không đúng thứ tự, bạn cần kết hợp hàm SUBTOTAL trong Excel với hàm IF. Số thứ tự của giá trị đã chọn được viết lại từ đầu. Lọc dữ liệu và phương pháp này chỉ áp dụng được khi sắp xếp theo cột.
Cú pháp tổng quát:
= IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,…))
Trên đây là hướng dẫn sử dụng hàm SUBTOTAL trong Excel, đơn giản và dễ thực hiện. Bảo Tín hi vọng bài viết sẽ giúp ích cho các bạn khi làm việc với bảng tính!
