Bạn đang xem bài viết Hướng dẫn sử dụng hàm SUBTOTAL trong Excel dễ dàng kèm ví dụ minh họa tại thtrangdai.edu.vn. Bạn có thể truy cập nhanh những thông tin cần thiết trong mục lục bài viết dưới đây.
Hàm SUBTOTAL trong Excel có thể dùng để tính tổng, đếm ô không trống trong vùng dữ liệu được lọc, đánh số tự động,… Mời các bạn cùng tham gia bài viết sau của thtrangdai.edu.vn để tìm hiểu về các công thức. Chức năng SUBTOTAL và cách sử dụng cụ thể!
Công thức hàm SUBTOTAL
Hàm SUBTOTAL trong Excel được áp 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,…
Công thức hàm SUBTOTAL như sau:
Trong đó:
- Hàm_num: Nếu như hàm_num Từ 1 đến 11, hàm SUBTOTAL thực hiện các phép tính bao gồm các giá trị ẩn trong vùng dữ liệu chứa giá trị ẩn đó. Nếu như hàm_num Nếu được chọn từ 101 – 111 thì hàm SUBTOTAL sẽ bỏ qua và không tính các giá trị ở các hàng ẩn.
- Tham chiếu1, Tham chiếu2,…: 1 ô trở lên hoặc phạm vi ô để tính tổng phụ, tối đa 254.
Mã của Hàm_num như sau:
|
Hàm_num(bao gồm các giá trị ẩn) |
Hàm_num(bỏ qua các giá trị ẩn) |
Tương đương Hàm |
|---|---|---|
|
Đầu tiên |
101 |
TRUNG BÌNH |
|
2 |
102 |
ĐẾM |
|
3 |
103 |
QUẬN |
|
4 |
104 |
TỐI ĐA |
|
5 |
105 |
PHÚT |
|
6 |
106 |
SẢN PHẨM |
|
7 |
107 |
STDEV |
|
số 8 |
108 |
STDEVP |
|
9 |
109 |
TỔNG |
|
mười |
110 |
VAR |
|
11 |
111 |
VARP |
Ví dụ về hàm SUBTOTAL
Dưới đây là một số cách phổ biến để áp dụng hàm SUBTOTAL trong thực tế, mời các bạn tham khảo nhé!
Tính tổng diện tích lọc
Giả sử bạn có bảng sau và cần lọc 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 cần tính > Vào Trang chủ > Sắp xếp và Lọc > Lọc.
Bước 2: Click vào mũi tên tam giác ngược ở tiêu đề cột “Đội”đánh dấu “MỘT” 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 ô muốn lấy kết quả bạn nhập:
=SUBTOTAL(9,D2:D11)
Giải thích công thức:
- hàm_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 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 tính tổng.
Bạn sẽ nhận được kết quả sau:
Lưu ý: Bạn có thể sử dụng giá trị đối số là 9 (tổng các giá trị ẩn) hoặc 109 (bỏ qua các giá trị ẩ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ư hình dưới đây. Nhiệm vụ của bạn là đếm số học sinh khối C có link bài tập để dễ thống kê.
Đầu tiên, bạn lọc ra những học sinh thuộc nhóm C bằng cách sử dụ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, tại ô muốn lấy kết quả bạn nhập:
=SUBTOTAL(3,D4:D14)
Giải thích công thức:
- hàm_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 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 tính tổng.
Kết quả thu được sẽ như sau:
Lưu ý: Bạn có thể sử dụng giá trị đối số 3 (đếm các giá trị đã bị ẩn thủ công do tính năng Hide) hoặc 103 (bỏ qua, không tính các giá trị bị ẩn thủ công) để đếm các giá trị. các ô không trống trong các hàng được lọc.
Bạn có thể xem hàm COUNTA hoạt động như thế nào và một số ví dụ TẠI ĐÂY.
Đánh số thứ tự nhanh
Nếu chỉ cần đánh số cho danh sách dữ liệu liên tiếp (không chứa hàng trống) như hình dưới đây, bạn có thể nhập công thức đơn giản vào ô A2 như:
=SUBTOTAL(3,$B$2:B2)
Giải thích công thức:
- hàm_num là 3: Tương ứng với phương pháp tính COUNTA – đếm các ô không trống.
- ref1 là $B$2:B2: Đếm các ký tự xuất hiện trong phạm vi $B$2:B2, trong đó ô B2 được cố định bằng a “$” ký tên trước hàng và địa chỉ ô. Nếu bạn sao chép công thức xuống, giá trị sẽ thay đổi như sau: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm chính xác, mang lại kết quả như mong muốn.
Sau đó, các bạn 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 chuyển chuột đến góc dưới bên phải ô và kéo thả dấu cộng hiển thị đến cuối ô cuối cùng cần sao chép). điền). Kết quả thu được như sau:
Tuy nhiên, nếu bạn gặp phải “khó” dữ liệu 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 số lại từ đầu khi lọc dữ liệu và cách này chỉ áp dụng được khi sắp xếp theo cột.
Cú pháp chung:
Ví dụ bạn có bảng dữ liệu như hình dưới đây.
Tại ô A2, nhập công thức:
=IF(B2=””,””,SUBTOTAL(3,$B$2:B2))
Suy luận: Hàm chính cần sử dụng sẽ là IF. Nếu có ô trống thì bỏ qua. Nếu ô không trống, hãy sử dụng SUBTOTAL để đánh số ô đó.
Công thức của hàm IF là: =IF(logic_test, value_if_true, value_if_false), với:
- kiểm tra logic: Điều kiện của hàm IF.
- giá trị_if_true: Trả về giá trị nếu điều kiện đúng.
- giá trị_if_false: Trả về giá trị nếu điều kiện sai. Ở đây chúng ta sử dụng hàm SUBTOTAL.
Các đối số cần điền vào hàm SUBTOTAL là:
- hàm_num = 3: Phương pháp tính là COUNTA – đếm các ô không trống.
- ref1 = $B$2:B2: Đếm các ký tự xuất hiện trong phạm vi $B$2:B2, trong đó ô B2 được cố định bằng a “$” ký tên trước hàng và địa chỉ ô. Nếu bạn sao chép công thức xuống, giá trị sẽ thay đổi như sau: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm chính xác, mang lại kết quả như mong muốn.
Sau khi nhập công thức xong nhấn Enter rồi copy công thức từ ô A2 sang các ô còn lại. Kết quả là chúng ta có được bảng sau:
Cách khắc phục #VALUE! hàm SUBTOTAL
Nguyên nhân lỗi:
– Số nhận dạng chức năng hàm_num không nằm trong phạm vi 1 – 11 hoặc 101 – 111.
– Thẩm quyền giải quyết giới thiệu tham chiếu đến các ô trong một bảng tính (trang tính khác), ví dụ bạn đang sử dụng SUBTOTAL trong trang tính STT2 nhưng lại tham chiếu đến một ô, một phạm vi ô nhất định trong trang tính STT1.
Làm thế nào để khắc phục:
Nguyên nhân thứ nhất là do lỗi xác định hàm, bạn cần kiểm tra xem mình đã sử dụng đúng số hàm chưa hoặc trong quá trình nhập có thừa ký tự nào để sửa lại không.
Đối với nguyên nhân thứ hai do lỗi tham chiếu, hãy xóa tham chiếu sai và thay thế bằng phạm vi ô đúng đặt trong cùng bảng 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 các bạn khi làm việc với bảng tính!
Cảm ơn các bạn đã xem bài viết Cách sử dụng hàm SUBTOTAL trong Excel rất dễ hiểu, có ví dụ minh họa tại thtrangdai.edu.vn. Các bạn có thể bình luận, xem thêm các bài viết liên quan bên dưới và hy vọng nó sẽ hữu ích. mang đến cho bạn những thông tin thú vị.
Nhớ để nguồn: Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ minh họa tại thtrangdai.edu.vn
Chuyên mục: Blog
