Hàm SUBTOTAL trong Excel có thể dùng trong việc tính tổng, đếm các ô không trống trong một vùng dữ liệu được lọc, đánh số thứ tự tự động,… Mời bạn cùng bài viết sau của LiveFB tìm hiểu về công thức hàm SUBTOTAL và cách sử dụng cụ thể nhé!
Công thức hàm SUBTOTAL
Hàm SUBTOTAL trong Excel được ứng dụng trong nhiều trường hợp, cụ thể có thể kể đến là tính tổng, trung bình cộng, đếm số ô, tìm giá trị lớn nhất/nhỏ nhất của dữ liệu, đánh số thứ tự tự động,…
Công thức hàm SUBTOTAL như sau:
Trong đó:
- Function_num: Nếu function_num từ 1 – 11 thì hàm SUBTOTAL 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 function_num được chọn từ 101 – 111 thì hàm SUBTOTAL sẽ bỏ qua, không tính các giá trị ở hàng ẩn.
- Ref1, Ref2,…: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.
Mã số của Function_num như sau:
|
Function_num |
Function_num |
Tương đương Hàm |
|---|---|---|
|
1 |
101 |
AVERAGE |
|
2 |
102 |
COUNT |
|
3 |
103 |
COUNTA |
|
4 |
104 |
MAX |
|
5 |
105 |
MIN |
|
6 |
106 |
PRODUCT |
|
7 |
107 |
STDEV |
|
8 |
108 |
STDEVP |
|
9 |
109 |
SUM |
|
10 |
110 |
VAR |
|
11 |
111 |
VARP |
Ví dụ hàm SUBTOTAL
Dưới đây là một số cách ứng dụng hàm SUBTOTAL phổ biến trong thực tế, mời bạn cùng tham khảo nhé!
Tính tổng vùng được lọc
Giả sử bạn có bảng sau và cần lọc ra tổng KPI của đội A. Bạn thực hiện thao tác như sau:
Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.
Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Đội”, tick chọn “A” và nhấn OK.
Bước 3: Bảng dữ liệu của đội A được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập:
=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ần sử dụng. Bạn sẽ thấy giá trị đối số này hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây do chúng ta cần tính tổng nên sẽ 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ẽ thu được kết quả như sau:
Lưu ý: Bạn có thể dùng giá trị đối số là 9 (tính tổng các giá trị đã bị ẩn) hoặc 109 (bỏ qua các giá trị đã bị ẩn) để tính tổng các hàng đã lọc.
Đếm các ô được lọc không trống
Giả sử bạn có bảng dữ liệu như minh họa bên dưới. Nhiệm vụ của bạn là đếm số lượng sinh viên của nhóm C đã có link bài tập để tiện thống kê.
Đầu tiên, bạn tiến hành lọc ra các sinh viên thuộc nhóm C bằng thao tác lọc tương tự như hướng dẫn ở mục trước.
Sau khi lọc dữ liệu xong, tại ô cần lấy kết quả, bạn nhập:
=SUBTOTAL(3,D4:D14)
Giải thích công thức:
- function_num = 3 là giá trị đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây do chúng ta cần đếm các ô không trống nên sẽ 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.
Kết quả thu được sẽ như sau:
Lưu ý: Bạn có thể dùng giá trị đối số là 3 (đếm cả các giá trị đã bị ẩn thủ công do tính năng Hide) hoặc 103 (bỏ qua, không đếm các giá trị đã bị ẩn thủ công) để tính đếm các ô không trống trong các hàng đã lọc.
Bạn có thể xem cách hoạt động của hàm COUNTA và một số ví dụ TẠI ĐÂY.
Đánh số thứ tự nhanh
Nếu chỉ cần đánh số thứ tự cho một danh sách dữ liệu liên tiếp (không chứa các hàng trống) như hình dưới thì bạn có thể nhập công thức đơn giản vào ô A2 là:
=SUBTOTAL(3,$B$2:B2)
Giải thích công thức:
- function_num là 3: Ứng với cách tính là COUNTA – đếm ô không trống.
- ref1 là $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu”$” ở trước địa chỉ hàng và ô. Nếu sao chép công thức xuống dưới, giá trị sẽ thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm đúng, mang về kết quả như mong muốn.
Sau đó, bạn kéo sao chép công thức (bằng cách chọn vào ô chứa công thức cần sao chép > di chuột đến góc dưới, bên phải ô và kéo thả chuột dấu cộng vừa hiển thị đến hết ô cuối cùng cần điền). Kết quả thu được như sau:
Tuy nhiên, nếu gặp phải một dữ liệu “khó nhằn” với các hàng trống và không trống lộn xộn, bạn sẽ cần kết hợp SUBTOTAL với hàm IF. Số thứ tự của giá trị được chọn sẽ được đánh lại từ đầu khi thực hiện lọc dữ liệu và cách này chỉ áp dụng được khi đánh thứ tự theo cột.
Cú pháp tổng quát:
Ví dụ, bạn có bảng dữ liệu như hình dưới.
Tại ô A2 bạn nhập công thức:
=IF(B2=””,””,SUBTOTAL(3,$B$2:B2))
Suy luận: Hàm chính cần dùng sẽ là IF. Nếu như có một ô rỗng thì bỏ qua không đếm. Nếu ô không rỗng thì dùng SUBTOTAL để đánh số thứ tự.
Công thức của hàm IF là: =IF(logical_test, value_if_true, value_if_false), với:
- logical_test: Điều kiện của hàm IF.
- value_if_true: Giá trị trả về nếu điều kiện đúng.
- value_if_false: Giá trị trả về nếu điều kiện sai. Ở đây chúng ta dùng hàm SUBTOTAL.
Các đối số cần điền trong hàm SUBTOTAL là:
- function_num = 3: Ứng với cách tính là COUNTA – đếm ô không trống.
- ref1 = $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu”$” ở trước địa chỉ hàng và ô. Nếu sao chép công thức xuống dưới, giá trị sẽ thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm đúng, mang về kết quả như mong muốn.
Sau khi nhập công thức, bạn nhấn Enter rồi tiến hành sao chép công thức từ ô A2 đến các ô còn lại. Kết quả ta được bảng sau:
Cách fix lỗi #VALUE! hàm SUBTOTAL
Nguyên nhân lỗi:
– Số xác định chức năng function_num không nằm trong khoảng 1 – 11 hoặc 101 – 111.
– Tham chiếu ref tham chiếu đến các ô trong một trang tính (sheet khác), ví dụ bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy ô nào đó ở sheet STT1.
Cách fix:
Đối với nguyên nhân đầu tiên do sai số xác định chức năng, bạn cần kiểm tra lại xem mình đã dùng đúng số chức năng chưa hoặc trong quá trình nhập có dư ký tự nào không để chỉnh lại cho đúng.
Đối với nguyên nhân thứ 2 do lỗi tham chiếu, hãy xóa phần tham chiếu sai đi, thế lại bằng dãy ô đúng được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL.
Trên đây là hướng dẫn cách dùng hàm SUBTOTAL trong Excel đơn giản, dễ thực hiện. Hy vọng bài viết sẽ giúp ích cho bạn khi làm việc với bảng tính nhé!
Trả lời