Contents
Khi bạn làm việc với bảng tính chứa nhiều số, bạn sẽ cần hàm SUBTOTAL trong Excel. Bạn đã biết chức năng này là gì và nó hoạt động như thế nào trong công việc của bạn? Nếu chưa thì còn chần chừ gì nữa mà bạn chưa đọc ngay bài viết này Thuthuat.tip.edu.vn?
SUBTOTAL trong Excel là gì?
SUBTOTAL là một hàm tích hợp sẵn trong Excel. Nếu bảng tính của bạn hoạt động với các con số, thì đây là chức năng bạn sẽ cần thường xuyên. Hàm SUBTOTAL có nhiều chức năng, sẽ giúp ích rất nhiều cho bạn trong mọi công việc với bảng tính.
Các chức năng của hàm SUBTOTAL trong Excel
Hàm SUBTOTAL là một trong những hàm cơ bản của Excel. Nó được sử dụng để tổng phụ trong danh sách hoặc cơ sở dữ liệu, đếm ô, trung bình trong Excel, tìm giá trị tối đa / nhỏ nhất hoặc tổng các giá trị danh sách đã lọc. Nó chứa rất nhiều chức năng nên nếu bạn biết cách sử dụng thì công việc của bạn sẽ được hoàn thành rất nhanh chóng.
Cú pháp và cách sử dụng hàm SUBTOTAL trong Excel
Cú pháp của hàm SUBTOTAL trong Excel
Công thức của hàm SUBTOTAL trong Excel là:
= SUBTOTAL (function_num; ref1, [ref2]…)
Trong đó:
- function_num: bạn sẽ thay thế bằng các số từ 1 đến 11 và 101 đến 111. Nó sẽ quyết định sử dụng hàm nào để tính toán. Bạn cần nhớ bảng sau:
|
Function_num |
Function_num (bỏ qua giá trị ẩn) |
Hàm |
| Đầu tiên | 101 | TRUNG BÌNH |
| 2 | 102 | ĐẾM |
| 3 | 103 | COUNTA |
| 4 | 104 | TỐI ĐA |
| 5 | 105 | MIN |
| 6 | 106 | SẢN PHẨM |
| 7 | 107 | STDEV |
| số 8 | 108 | STDEVP |
| 9 | 109 | TÓM LƯỢC |
| mười | 110 | VAR |
| 11 | 111 | VARP |
- Tham chiếu 1, Tham khảo 2,…: phạm vi cần tính toán.
Sử dụng hàm SUBTOTAL trong Excel để tính tổng các hàng đã lọc
Để tính tổng các hàng đã lọc bằng hàm SUBTOTAL trong Excel, hãy sử dụng công thức sau:
= SUBTOTAL (9; phạm vi)
Với 9 là số thứ tự của hàm TÓM LƯỢC khi sử dụng SUBTOTAL.
Kết quả:
Sử dụng hàm SUBTOTAL của Excel để đếm các ô đã lọc không trống
Để đếm các ô không trống, bạn sẽ cần sử dụng hàm lấy số 3tức là chức năng COUNTA – hàm đếm các ô không trống.
= SUBTOTAL (3, phạm vi)
Ví dụ dưới đây là đếm các ô không trống trong cột Số ngày làm việc:
Kết quả:
Bỏ qua các giá trị trong công thức SUBTOTAL lồng nhau
Ví dụ, trong một bảng tính, bạn cần tính nhiều công thức trung bình khác nhau trong cùng một trang tính. Nếu bạn đã tính trung bình cho các thành phần nhỏ hơn, Excel sẽ tự động bỏ qua hàng trung bình và chỉ đếm các thành phần.
Ví dụ dưới đây sẽ giúp bạn hiểu rõ hơn về chức năng này của SUBTOTAL.
Đầu tiên, người tạo bảng cần tính số hạt đậu xanh trung bình (cả loại I và loại II) nên công thức sẽ là:
= SUBTOTAL (1; B2: B3)
Tiếp theo, trong dòng AVERAGE NUMBER OF peas, trong dòng này, người tạo bảng cần tính giá trị trung bình của tất cả các loại đậu, vì vậy công thức sẽ là:
= SUBTOTAL (1; B2: B7)
Kết quả thu được trong hàng SỐ LƯỢNG đậu Hà Lan AVERAGE là 6, là tổng số ki-lô-gam (5 + 4 + 10 + 4 + 7) chia cho 5 hạt đậu. Excel đã bỏ qua dòng HÀNG KHÔNG CÓ SỐ LƯỢNG Đậu Mung ngay cả khi phần phạm vi của trình tạo bảng bao gồm hàng này.
Ví dụ sử dụng SUBTOTAL trong Excel
Dưới đây là các ví dụ cụ thể về việc sử dụng hàm SUBTOTAL trong Excel trong các tác vụ phổ biến:
Đầu tiên là tính tổng số hạt đậu:
Tiếp theo là tính trung bình số lượng hạt đậu:
Tìm số lượng đậu cao nhất (MAX):
Tìm số hạt đậu ít nhất (MIN):
Kết quả:
Để sử dụng chức năng SUBTOTAL tiện lợi, bạn chỉ cần nhớ số thứ tự của các hàm thông dụng. Ví dụ, để tính tổng (sử dụng hàm TÓM LƯỢC) sẽ là số 9để tính giá trị trung bình (sử dụng hàm TRUNG BÌNH) sẽ là số Đầu tiên. Tuy nhiên, Excel cũng sẽ hiển thị cho bạn bảng số thứ tự này khi bạn nhập đúng tên hàm TIỀM NĂNGnên bạn không cần quá lo lắng.
Hàm SUBTOTAL không cho ra kết quả – Một số lỗi thường gặp
lỗi #value trong SUBTOTAL. hàm số
Khi sử dụng hàm SUBTOTAL trong Excel, đôi khi bạn sẽ gặp lỗi #giá trị. Đây là lỗi xảy ra vì 1 trong 2 lý do:
- Số được nhập trong function_num là một số không thuộc phạm vi 1-11 hoặc 101-111.
- Phạm vi bạn chọn (phần sau dấu chấm phẩy) là tham chiếu 3D.
Để khắc phục, bạn chỉ cần sửa lại thông tin và đảm bảo dữ liệu bạn nhập vào không mắc một trong hai lỗi trên.
Lỗi # DIV / 0! trong SUBTOTAL. hàm số
Đây là một lỗi xảy ra khi bạn sử dụng một hàm để chia một số nhất định hoặc một tổng cho 0. Thông thường, lỗi này sẽ xảy ra khi bạn đang tính giá trị trung bình hoặc độ lệch chuẩn của một dải ô không chứa giá trị nào. con số.
Việc bạn cần làm để khắc phục lỗi này là kiểm tra lại thông tin dữ liệu bạn đã nhập cho ô xem mình có nhập sai hay không.
Lỗi #NAME? trong SUBTOTAL. hàm số
Lỗi #TÊN? sẽ xuất hiện trong ô của bạn nếu bạn nhập sai tên hàm. Do đó, bạn cần nhập chính xác tên hàm TIỀM NĂNG vì vậy bạn không gặp lỗi này. Một mẹo nhỏ để bạn biết mình đã nhập đúng hay chưa là khi nhập ô, bạn chỉ cần nhập dấu. ‘=’ và tên chức năng chính xác, sau đó nhập ‘(‘ Excel sẽ hiển thị một bảng gợi ý để giúp bạn chọn đúng phần tử của hàm. Nếu bạn nhập sai tên chức năng, menu này sẽ không xuất hiện.
Thuthuat.tip.edu.vn còn rất nhiều bài viết khác về các thủ thuật trong Excel mà bạn có thể tham khảo:
Hy vọng sau bài viết này, bạn đã hiểu cách sử dụng hàm SUBTOTAL trong Excel. Đây là một chức năng vô cùng tiện lợi cho dân văn phòng phải không nào? Nếu thấy bài viết hữu ích, đừng quên Like, Share cũng như ghé thăm Thuthuat.tip.edu.vn thường xuyên nhé!
Tham khảo: Microsoft
Excel –