Hàm SUBTOTAL trong Excel có thể dùng để tính tổng, lọc thống kê các ô không trống trong một dải dữ liệu, đánh số tự động, … Mời các bạn cùng tìm hiểu công thức và cách sử dụng cụ thể trong bài viết dưới đây về hàm SUBTOTAL của chúng tôi nhé!
Các hướng dẫn sau đây được thực hiện trên máy tính xách tay hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể làm tương tự trên Excel 2007, 2010, 2013 và 2019.
Bạn có thể tải xuống một số ví dụ về hàm SUBTOTAL trong Excel để làm quen với nó!
Công thức tính hàm Subtotal
Hàm SUBTOTAL trong Excel được sử dụng trong nhiều trường hợp, cụ thể là tính tổng, tính trung bình, đếm ô, tìm giá trị lớn nhất / nhỏ nhất của dữ liệu, đánh số tự động, v.v. ..
Công thức cho hàm SUBTOTAL như sau:
= SUBTOTAL (function_num, ref1, ref2, …)
ở đó:
Function_num: Nếu function_num từ 1 đến 11, thì hàm SUBTOTAL thực hiện các phép tính bao gồm cả giá trị ẩn trong vùng dữ liệu có chứa giá trị ẩn. Nếu function_num được chọn từ 101 – 111, thì hàm SUBTOTAL bỏ qua các giá trị hàng ẩn.
Ref1, Ref2,…: 1 hoặc nhiều ô hoặc phạm vi ô tính thành tổng phụ, tối đa 254.
Mã cho Function_num như sau:
Xem thêm: Clorua Vôi Là Gì? Công Thức, Ứng Dụng Và Nguy Cơ Của Clorua Vôi
Ghi chú:
– Hàm SUBTOTAL được sử dụng cho các cột dữ liệu dọc.
– Nếu ref1, ref2, … có chứa hàm SUBTOTAL thì nó sẽ bị bỏ qua để tránh đếm hai lần.
– SUBTOTAL sẽ bỏ qua dữ liệu bị ẩn bởi bộ lọc (dữ liệu không thỏa mãn bộ lọc).
– Cần phân biệt rõ ràng 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ị ẩn là giá trị được ẩn chủ động khỏi hàng bạn chọn bằng cách thực hiện> Nhấp chuột phải và chọn Ẩn.
Các giá trị bị từ chối do không đáp ứng bộ lọc là các giá trị không phù hợp với yêu cầu tìm kiếm của bạn.
Ví dụ
Dưới đây là một số cách ứng dụng hàm SUBTOTAL phổ biến trong thực tế, mời các bạn tham khảo!
Tính tổng vùng bộ lọc
Giả sử bạn có bảng sau và cần lọc ra tổng KPI của Nhóm A. Bạn làm như sau:
Bước 1: Đánh dấu bảng chứa dữ liệu muốn tính toán> vào Trang chủ> Sắp xếp & Bộ lọc> Bộ lọc.
Bước 2: Nhấp vào mũi tên tam giác ngược trong tiêu đề cột “Đội”, đánh dấu “A” và nhấp OK.
Bước 3: Dùng Excel để lọc ra bảng dữ liệu của Đội A. Bây giờ nhận kết quả của đầu vào của bạn trong ô:
= Tổng phụ (9, D2: D11)
Giải thích công thức:
function_num = 9 là giá trị tham số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị tham số này được hiển thị khi bạn nhập công thức hàm SUBTOTAL. Ở đây vì cần tính tổng nên chúng ta sẽ chọn hàm SUM – tương ứng với số 9.
ref1 = D2: D11 là phạm vi được tính tổng.
Bạn sẽ nhận được kết quả sau:
Lưu ý: Bạn có thể tính tổng các hàng đã lọc bằng cách sử dụng các giá trị tham số là 9 (để tổng các giá trị ẩn) hoặc 109 (để bỏ qua các giá trị ẩn).
Tham khảo thêm: 10 Bí Quyết Để Loại Bỏ “Vô Duyên” trong Giao Tiếp
Đếm các ô không trống đã lọc
Giả sử bạn có một bảng dữ liệu như hình dưới đây. Nhiệm vụ của bạn là đếm xem có bao nhiêu sinh viên trong nhóm C có liên kết thực hành để phục vụ mục đích thống kê.
Đầu tiên, bạn tiếp tục lọc ra các sinh viên trong nhóm C bằng cách sử dụng thao tác lọc tương tự như đã giải thích trong phần trước.
Sau khi lọc dữ liệu, trong ô mà bạn nhận được kết quả, hãy nhập:
= Tổng phụ (3, D4: D14)
Giải thích công thức:
function_num = 3 là giá trị tham số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị tham số này được hiển thị khi bạn nhập công thức hàm SUBTOTAL. Ở đây vì cần đếm các ô không trống nên chúng ta sẽ chọn hàm COUNTA – tương ứng với số 3.
ref1 = D4: D14 là phạm vi được tính tổng.
Kết quả thu được như sau:
Hàm SUBTOTAL được sử dụng để đếm các ô không trống đã lọc
Lưu ý: Bạn có thể đếm các ô không trống trong các hàng được lọc bằng cách sử dụng giá trị tham số 3 (do tính năng Ẩn, các giá trị ẩn cũng có thể được tính theo cách thủ công) hoặc 103 (bị bỏ qua, các giá trị ẩn thủ công không được tính toán).
Bạn có thể xem cách hoạt động của hàm COUNTA và một số ví dụ tại đây.
Số nhanh
Nếu bạn chỉ cần đánh số một danh sách dữ liệu liền kề (không có hàng trống) như hình dưới đây, bạn có thể nhập một công thức đơn giản vào ô A2 như sau:
= Tổng phụ (3, $ B $ 2: B2)
Giải thích công thức:
function_num là 3: cách tính tương ứng là COUNTA – Đếm các ô không trống.
ref1 là $ B $ 2: B2: Đếm các ký tự trong phạm vi $ B $ 2: B2, trong đó ô B2 được cố định bằng “$” ở phía trước hàng và địa chỉ ô. Nếu bạn sao chép công thức xuống, các giá trị sẽ thay đổi thành: $ B $ 2: B3, $ B $ 2: B4, $ B $ 2: B5,… và hàm sẽ đếm chính xác, cho ra kết quả mong muốn. .
Sau đó, bạn kéo và sao chép công thức (bằng cách chọn ô chứa công thức bạn muốn sao chép> di chuột qua góc dưới cùng bên phải của ô và kéo dấu cộng vừa hiển thị đến cuối ô cuối cùng để lấy nét). Kết quả thu được như sau:
Tuy nhiên, nếu bạn gặp phải dữ liệu “cứng” với các dòng trống và không trống lộn xộn, bạn cần sử dụng SUBTOTAL kết hợp với hàm IF. Khi lọc dữ liệu, số thứ tự của giá trị đã chọn được nhập lại từ đầu, phương pháp này chỉ hoạt động khi sắp xếp theo cột.
Cú pháp chung
= IF (logic_test, value_if_true, SUBTOTAL (function_num, ref1, …))
Xem chi tiết: Cách sử dụng hàm SUBTOTAL và hàm IF để đánh số tự động trong Excel
Ví dụ, bạn có một bảng dữ liệu như hình dưới đây.
Bảng dữ liệu với các ô trống, không trống xen kẽ
Nhập công thức vào ô A2:
= IF (B2 = “”, “”, Tổng phụ (3, $ B $ 2: B2))
Hệ quả: Hàm chính cần sử dụng là IF. Nếu có ô trống, số lượng bị bỏ qua. Nếu các ô không trống, hãy sử dụng SUBTOTAL để đánh số.
Công thức cho hàm IF là: = IF (logic_test, value_if_true, value_if_false), trong đó:
logic_test: Điều kiện của hàm IF.
value_if_true: Trả về giá trị nếu điều kiện là đúng.
value_if_false: Trả về giá trị nếu điều kiện sai. Ở đây chúng tôi sử dụng hàm SUBTOTAL.
Các tham số được điền vào hàm SUBTOTAL là:
function_num = 3: Cách tính tương ứng là COUNTA – Đếm các ô không trống.
ref1 = $ B $ 2: B2: Đếm các ký tự trong phạm vi $ B $ 2: B2, trong đó ô B2 được cố định bằng “$” ở phía trước địa chỉ hàng và ô. Nếu bạn sao chép công thức xuống, các giá trị sẽ thay đổi thành: $ B $ 2: B3, $ B $ 2: B4, $ B $ 2: B5,… và hàm sẽ đếm chính xác, cho ra kết quả mong muốn. .
Sau khi nhập công thức, nhấn Enter, sau đó sao chép công thức từ ô A2 sang các ô còn lại. Kết quả như sau:
Cách sửa lỗi #VALUE! hàm SUBTOTAL
Lý do sai:
– Định danh hàm function_num không nằm trong khoảng 1 – 11 hoặc 101 – 111.
– Một ref tham chiếu đề cập đến một ô trong một trang tính khác (một trang tính khác), ví dụ: bạn sử dụng SUBTOTAL trong trang tính STT2, nhưng tham chiếu đến một ô hoặc phạm vi ô trong trang tính STT1.
Tham khảo: Kiêu Ngạo Là Gì – Cách Thay Đổi Tính Cách Kiêu Ngạo
Làm thế nào để khắc phục?
Nguyên nhân đầu tiên là do định nghĩa hàm bị sai, bạn cần kiểm tra lại xem có đúng số hàm được sử dụng hay không, hoặc có thừa ký tự trong quá trình nhập liệu hay không để sửa lại.
Đối với nguyên nhân thứ hai gây ra lỗi trích dẫn, hãy xóa tham chiếu bị lỗi và thay thế bằng phạm vi ô chính xác đượ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 sử dụng hàm SUBTOTAL trong Excel đơn giản và dễ thực hiện. Hy vọng bài viết này sẽ giúp ích cho bạn khi làm việc với bảng tính!
Hãy thường xuyên truy cập website Chung cư Tabudec của chúng tôi để cập nhật nhiều thông tin hữu ích nhé!
