Cập nhật 7 tháng trước
23.945
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 Điện máy XANH tìm hiểu về công thức hàm SUBTOTAL và cách sử dụng cụ thể nhé!
- Hướng dẫn dưới đây được thực hiện trên laptop hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể thực hiện tương tự trên các phiên bản Excel 2007, 2010, 2013 và 2019.
- Bạn có thể tải một số ví dụ về hàm SUBTOTAL trong Excel để làm quen nhé!
1Công thức hàm SUBTOTAL
Hàm SUBTOTAL trong Excel được ứng dụng trong nhiều trường hợp, đơn cử hoàn toàn 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 tài liệu, đánh số thứ tự tự động hóa, …
Công thức hàm SUBTOTAL như sau :
=SUBTOTAL(function_num, ref1, ref2,…)
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 ( gồm có giá trị ẩn ) | Function_num ( bỏ lỡ giá trị ẩn ) |
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 |
Lưu ý:
– Hàm SUBTOTAL được sử dụng cho những cột số liệu theo chiều dọc .
– Trường hợp ref1, ref2, … có chứa hàm SUBTOTAL thì sẽ được bỏ lỡ để tránh tính trùng 2 lần .
– SUBTOTAL sẽ bỏ qua phần dữ liệu bị ẩn bởi Filter (dữ liệu không thỏa mãn bộ lọc).
– Cần phân biệt rõ giá trị bị ẩn và giá trị bị loại do không thỏa mãn nhu cầu bộ lọc .
- Giá trị bị ẩn là giá trị mà chính bạn đã chủ động ẩn đi bằng thao tác bạn chọn hàng > Nhấn chuột phải chọn Hide.
- Giá trị bị loại bỏ do không thỏa mãn 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.
2 Ví dụ hàm SUBTOTAL
Dưới đây là một số ít cách ứng dụng hàm SUBTOTAL thông dụng trong thực tiễn, mời bạn cùng tìm hiểu thêm 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 triển khai 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 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 tác dụng 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 tài 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 tài liệu xong, tại ô cần lấy hiệu 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 hoàn toàn có thể xem cách hoạt động giải trí của hàm COUNTA và một số ít ví dụ TẠI ĐÂY .
Đánh số thứ tự nhanh
Nếu chỉ cần đánh số thứ tự cho một list tài liệu liên tục ( không chứa những hàng trống ) như hình dưới thì bạn hoàn toàn có thể nhập công thức đơn thuần vào ô A2 là :
= SUBTOTAL ( 3, USD B USD 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 ô sau cuối 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:
=IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,…))
Xem chi tiết: Cách dùng hàm SUBTOTAL và hàm IF đánh số thứ tự tự động trong Excel
Ví dụ, bạn có bảng tài 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:
3 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 những ô 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 do tiên phong do sai số xác lập tính năng, bạn cần kiểm tra lại xem mình đã dùng đúng số công dụng chưa hoặc trong quy trình nhập có dư ký tự nào không để chỉnh lại cho đúng .
Đối với nguyên do 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 triển khai với hàm SUBTOTAL .
Trên đây là hướng dẫn cách dùng hàm SUBTOTAL trong Excel đơn thuần, dễ thực thi. Hy vọng bài viết sẽ giúp ích cho bạn khi thao tác với bảng tính nhé !
Source: https://wikifin.net
Category: Blog