Hàm Excel SUBTOTAL 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 EDUSA tìm hiểu về công thức hàm Excel SUBTOTAL và cách sử dụng cụ thể nhé!
1. Công thức hàm Excel 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: =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.
Lưu ý:
- Hàm SUBTOTAL được sử dụng cho các 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ỏ qua để 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 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.
Xem thêm: Cách dùng công thức Excel lọc dữ liệu đơn giản và dễ hiểu
2. Ví dụ hàm Excel SUBTOTAL
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ả.
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.
Xem thêm: Cách sử dụng hàm Excel làm tròn lên cực kỳ đơn giản
3. Đế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.
Bạn sẽ thu được kết quả.
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.
Xem thêm: Cách chuyển dấu chấm phẩy trong Excel
4. Đá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: =IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,…))
Xem thêm: Lỗi hàm Excel không ra kết quả khi dùng hàm COUNTIF
5. Cách fix lỗi #VALUE! hàm Excel SUBTOTAL
5.1 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.
5.2 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.
Xem thêm: Cách sử dụng hàm Excel VLOOKUP chi tiết và dễ hiểu nhất
6. Trung tâm dạy Excel uy tín – EDUSA
Nếu bạn đang tìm cho mình địa điểm học Excel đảm bảo bạn thi là sẽ đạt nhưng vẫn đang băn khoăn chưa biết chọn đâu uy tín thì EDUSA là lựa chọn tuyệt vời dành cho bạn. Hiện nay trung tâm có khóa học và thi tin học Excel cam kết đậu ngay từ lần thi đầu tiên, hoàn 100% lệ phí học và thi khi thi không đạt chứng chỉ. Khi đăng ký khóa học, học viên sẽ được hưởng trải nghiệm các dịch vụ và lợi ích sau:
- EDUSA cam kết giúp các học viên đạt chứng chỉ ngay từ lần thi đầu tiên.
- Các bài giảng được đội ngũ giảng viên hệ thống lại kiến thức chi tiết, cụ thể, ngắn gọn, dễ hiểu phù hợp với các trình độ.
- Học bất cứ thời gian nào rảnh, tổng thời lượng học kéo dài trong 1-2 giờ
- Lộ trình học Excel rõ ràng, chi tiết giúp các bạn học viên nắm vững kiến thức sau mỗi buổi học.
- Cam kết học và thi lại miễn phí 100% nếu học viên thi không đạt chứng chỉ.
- Hỗ trợ đăng ký thi, nhận chứng chỉ và giao chứng chỉ đến tận nhà học viên.
- Đội ngũ giảng viên chuyên nghiệp, nhiệt tình.
Xem thêm: Khóa học Excel Offline chất lượng tại EDUSA
Xem thêm: Khóa học Excel Online chất lượng tại EDUSA
7. Câu hỏi thường gặp
7.1 Thời gian học Excel tại EDUSA có linh động không?
100% thời gian học dù là online hay offline đều linh động theo lịch rảnh cá nhân của học viên, đảm bảo được sự thoải mái xuyên suốt quá trình học.
7.2 Khóa học Excel tại EDUSA có bao đậu không?
Khóa học của EDUSA đều sát với đề thi thật, bài tập và đề thi thử được cập nhật thường xuyên. Với chính sách cam kết đầu ra 100% bằng văn bản cụ thể sẽ giúp học viên yên tâm trong suốt quá trình học tại đây.
7.3 EDUSA có gì khác biệt so với các trung tâm luyện thi Excel khác hay không?
Học viên có thể hoàn toàn yên tâm và tin tưởng khi lựa chọn luyện thi Excel tại lớp. Tất cả đều được chúng tôi thể hiện qua văn bản rõ ràng khi thực hiện cam kết với học viên.
8. Kết luận
Trên đây là hướng dẫn cách dùng hàm Excel SUBTOTAL đơn giản, dễ thực hiện. EDUSA 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é!
