So với những hàm khác trong Excel, hàm Subtotal được sử dụng rất linh hoạt và còn được ứng dụng phổ biến trong tính toán. Ở bài viết này, các chuyên gia tại Trang công nghệ số 1 tại Việt Nam – HPConnect.vn sẽ giúp các bạn biết được hàm Subtotal là gì? Đồng thời, với cách dùng hàm Subtotal trong Excel đơn giản nhất sẽ rất hữu ích. Mời các bạn cùng theo dõi ngay sau đây nhé!
Nội Dung Chính
Hàm Subtotal là gì?
Hàm Subtotal là hàm gì? Hàm Subtotal trong Excel là gì? Ý nghĩa hàm Subtotal? Đây là những vấn đề được hầu hết người dùng băn khoăn. Vì vậy, các chuyên gia tại HPConnect.vn xin giải đáp như sau:
Hàm Subtotal là một hàm trong Excel. Hàm Subtotal để làm gì? Hàm này có chức năng tính tổng phụ trong cơ sở dữ liệu hoặc trong danh sách.
Hầu hết các hàm trong Excel sẽ có những nhiệm vụ riêng. Tuy nhiên, đối với hàm Subtotal này lại rất linh hoạt. Khi người dùng có thể sử dụng đến hàm Subtotal trong Excel với mục đích tính toán, hàm Subtotal đánh số thứ tự, đếm số ô, tìm giá trị nhỏ nhất/ lớn nhất, làm phép tính logic hoặc tính giá trị trung bình,…
Các bạn có thể sử dụng hàm Subtotal dễ dàng trên các phiên bản Excel. Cụ thể là phiên bản Excel 2007, hàm Subtotal trong Excel 2013, hàm Subtotal trong Excel 2010 cho đến phiên bản 2016.
Công thức hàm Subtotal
Như đã được chia sẻ ở trên, hàm Subtotal được ứng dụng vào rất nhiều trường hợp khác nhau. Với công thức hàm Subtotal cụ thể như sau:
=SUBTOTAL (function_num, ref1, [ref2],…)
Trong đó:
- Function_num: Đây là một số chỉ định hàm nào được sử dụng cho tổng phụ.
- Ref1, Ref2,…: Đây là một hay nhiều ô, phạm vi thành tổng phụ. Với đối số Ref đầu tiên là bắt buộc, còn những đối số khác là tùy chọn (tối đa là 254 đối số).
Đối số Function_num có khả năng thuộc một trong những số sau đây:
- 1 – 11: bỏ qua những ô được lọc, gồm có cả những hàng ẩn thủ công.
- 101 – 111: bỏ qua tất cả những ô ẩn, gồm cả ô được lọc và ẩn thủ công.
Bảng Function_num gồm có cả những mã số được sử dụng để tính trong các trường hợp khác nhau trong hàm Subtotal như sau:
| Function_num (Bao gồm cả giá trị ẩn) | Function_num (Bỏ qua giá trị ẩn) | Chức năng | Mô tả |
| 1 | 101 | AVERAGE | Tính trung bình các con số |
| 2 | 102 | COUNT | Đếm số ô chứa giá trị số |
| 3 | 103 | COUNTA | Đếm số ô không trống |
| 4 | 104 | MAX | Tìm giá trị lớn nhất |
| 5 | 105 | MIN | Tìm giá trị nhỏ nhất |
| 6 | 106 | PRODUCT | Tính kết quả của các ô |
| 7 | 107 | STDEV | Tính độ lệch chuẩn mẫu dựa trên mẫu |
| 8 | 108 | STDEVP | Tính độ lệch chuẩn dựa trên toàn bộ số |
| 9 | 109 | SUM | Cộng các số |
| 10 | 110 | VAR | Ước tính độ dao động dựa trên mẫu |
| 11 | 111 | VARP | Ước tính độ dao động dựa trên toàn bộ số |
Lưu ý:
- Hàm Subtotal sẽ được dùng cho những cột số liệu nằm theo chiều dọc.
- Đối với trường hợp ref1, ref2,… có chứa hàm Subtotal. Như vậy, sẽ được bỏ qua để tráng tình trạng tính trùng 2 lần.
- Subtotal sẽ bỏ qua phần dữ liệu đã bị ẩn do Filter (vì dữ liệu này không đảm bảo thỏa mãn bộ lọc).
- Người dùng cần biết cách phân biệt giá trị ẩn và giá trị loại bỏ khi không thỏa mãn bộ lọc.
+ Với giá trị ẩn là giá trị mà người dùng đã chủ động ẩn đi thông qua thao tác chọn hàng >> Chọn Hide.
+ Với giá trị bị loại bỏ là giá trị không thỏa mãn bộ lọc. Những giá trị này không khớp với yêu cầu tìm kiếm của người dùng.
Hướng dẫn cách sử dụng hàm Subtotal trong Excel
Hàm Subtotal sử dụng như thế nào? Chúng ta có thể thấy rằng, cách sử dụng hàm Subtotal trong Excel còn phụ thuộc vào yêu cầu tính toán của người dùng là gì. Nó phụ thuộc vào hàm được chọn thông qua đối số ref1.
Tính giá trị các hàng được chọn
Vì hàm Subtotal sẽ bỏ qua những hàng được lọc ra. Vì vậy, người dùng có thể sử dụng để tính tổng dữ liệu một cách linh hoạt. Những giá trị trong tổng phụ sẽ được tự động tính lại dựa theo bộ lọc.
Ví dụ: Dưới đây là một bảng doanh số bán hàng tại 3 vùng Bắc – Trung – Nam của cửa hàng. Nếu bạn tiến hành lọc bảng doanh số bán hàng đối với miền Bắc duy nhất. Lúc này, công thức hàm Subtotal sẽ tiến hành điều chỉnh để bỏ qua miền Trung và Nam khi thực hiện tính tổng. Như vậy, các bạn sẽ thu được kết quả như bảng dưới đây:
Lưu ý: Vì cách 2 bộ số (1 – 11 và 101 – 111) xác định chức năng này đều bỏ qua những ô đã được lọc. Vì vậy, bạn có thể sử dụng đến công thức hàm SUBTOTAL 9 và SUBTOTAL 109.
Tính các hàng được nhìn thấy
Trong bảng tính gồm có các hàng, ô được lọc hoặc đã bị ẩn đi. Đối với trường hợp bạn cần tính những ô được nhìn thấy. Hãy sử dụng hàm Subtotal với đối số thứ nhất là số thuộc trong khoảng 101 – 111.
Nếu bạn cần tính cả các hàng đã bị ẩn đi. Hãy sử dụng đến công thức hàm Subtotal với đối số từ 1 – 11. Lúc này, hàm Subtotal trong Excel sẽ tự động hiểu và bắt đầu thực hiện phép tính cho bạn.
Khi hàm Subtotal được lồng ghép trong phép tính
Nếu một dãy ô trong công thức hàm Subtotal cũng chứa thêm một công thức Subtotal khác. Lúc này, công thức Subtotal được lồng vào này sẽ không được tính. Vậy kết quả hiển thị trong bảng không phải tính 2 lần.
Như hình dưới đây, chúng ta sẽ có công thức tính tổng SUBTOTAL(9, E2:E17). Lưu ý rằng ở đây là kết quả của công thức hàm Subtotal đã bỏ qua công thức Subtotal tại ô E8.
Cách sửa lỗi khi dùng hàm subtotal
Vì hàm Subtotal là hàm ghép nên người dùng sẽ rất dễ xảy ra lỗi khi thực hiện tính toán mà không nắm rõ cú pháp. Sau đây là cách sửa lỗi hàm Subtotal trong Excel chi tiết nhất mà các chuyên gia tại Trang công nghệ số 1 tại Việt Nam – HPConnect.vn xin chia sẻ đến các bạn như sau:
Những lỗi thường gặp
Sau đây là một số lỗi mà người dùng thường gặp khi sử dụng hàm Subtotal trong Excel:
- #VALUE!: Đây là trường hợp lỗi không xác định được giá trị. Thường thì lỗi này sẽ xuất hiện khi số xác định chức năng không thuộc trong khoảng 1 – 11 hay từ 101 – 111 hoặc có tham chiếu (ref) chính là tham chiếu 3D.
- #DIV/0!: Đây là trường hợp lỗi xảy ra khi một tổng cụ thể phải chia cho 0 ( Ví dụ: tính trung bình cộng hay độ lệch chuẩn của một dãy ô bất kỳ không có chứa giá trị số).
- #NAME?: Đây là trường hợp lỗi tên hàm SUBTOTAL bị sai chính tả.
Cách khắc phục lỗi hàm Subtotal như thế nào?
- Đối với lỗi #VALUE!: Các bạn nên kiểm tra lại cú pháp của hàm tính subtotal xem đã đúng hay chưa. Tham số thứ nhất của hàm Subtotal trong Excel phải là một trong những số từ 1 đến 11 hay từ 101 đến 111. Đối với tham chiếu (ref), bạn nên cân nhắc trong quá trình chọn những vùng tại một sheet khác.
- Đối với lỗi #DIV/0: Đây là trường hợp lỗi thường xảy ra khi tham số thứ nhất là số 1 hay 101. Hàm subtotal sẽ hoạt động tương tự như hàm average. Các bạn nên kiểm tra lại những giá trị tại các ô được tính để xem có giá trị nào trong các ô đó không xác định hay không.
- Đối với lỗi #NAME? thì bạn chỉ cần kiểm tra lại xem tên hàm Subtotal đã viết đúng hay chưa? Vì khi sai lỗi chính tả khi viết “subtotal”, bạn chỉ cần sửa lại cho đúng là được.
Ví dụ hàm Subtotal trong Excel
Để giúp các bạn hiểu rõ hơn về hàm Subtotal và cách sử dụng hàm Subtotal. Các chuyên gia tại Trang công nghệ số 1 tại Việt Nam – HPConnect.vn sẽ gửi đến các bạn một số ví dụ cụ thể như sau:
Ví dụ 1: Tính tổng trong 1 bảng
Đề: Hãy tính tổng số tiền thu được trong bảng sau đây bằng hàm Subtotal.
Bước 1: Tại bảng tính Excel, bạn hãy nhập vào cú pháp hàm Subtotal như sau và ô tham chiếu cần hiển thị kết quả:
=SUBTOTAL(9,F4:F9)
Giải thích hàm:
- SUBTOTAL: Đây là lệnh hàm.
- 9: Là Function_num và số 9 là hàm SUM.
- F4:F9: Là vùng dữ liệu tham chiếu.
Bước 2: Bạn nhấn phím Enter trên bàn phím để kết quả được hiển thị.
Ví dụ 2: Tính số tiền trung bình thu được
Đề: Hãy tính số tiền trung bình thu được sau khi bán những sản phẩm tại bảng sau bằng hàm SUBTOTAL.
Bước 1: Tại bảng tính Excel, bạn hãy nhập vào cú pháp hàm SUBTOTAL như sau tại ô tham chiếu cần hiển thị kết quả:
=SUBTOTAL(1,F4:F9)
Giải thích hàm:
- SUBTOTAL: Đây là lệnh hàm.
- 1: Là Function_num và số 1 là hàm AVERAGE.
- F4:F9: Là vùng dữ liệu tham chiếu.
Bước 2: Sau đó, bạn nhấn phím Enter trên bàn phím để kết quả cần tìm được hiển thị.
Ví dụ 3: Tìm giá trị lớn nhất trong bảng
Đề: Hãy tìm giá trị lớn nhất trong bảng sau đây bằng hàm SUBTOTAL.
Bước 1: Tại bảng tính dữ liệu Excel, bạn tiến hành nhập vào cú pháp sau đây vào ô tham chiếu cần hiển thị kết quả:
=SUBTOTAL(4,F4:F9)
Giải thích hàm:
- SUBTOTAL: Đây là lệnh hàm.
- 4: Là Function_num và số 4 là hàm MAX.
- F4:F9: Là vùng dữ liệu tham chiếu.
Bước 2: Bạn nhấn phím Enter để kết quả được hiển thị.
Ví dụ 4: Đếm số ô chứa các chữ số
Đề: Hãy đếm số ô chứa các chữ số trong bảng sau đây bằng hàm SUBTOTAL.
Bước 2: Tại bảng tính dữ liệu Excel, bạn nhập vào cú pháp hàm SUBTOTAL tại ô tham chiếu cần hiển thị kết quả như sau:
=SUBTOTAL(2,F4:F9)
Giải thích hàm:
- SUBTOTAL: Đây là lệnh hàm.
- 2: Là Function_num và số 2 là hàm COUNT.
- F4:F9: Là vùng dữ liệu tham chiếu.
Bước 2: Sau đó, bạn nhấn vào phím Enter trên bàn phím để hiển thị kết quả.
Hàm Subtotal khác gì hàm Sum?
Trong Excel, hàm Sum là một hàm tính tổng. Hàm này có nhiệm vụ cơ bản là tính tổng số của các con số và tham số thuộc trong ô và dãy ô.
Để biết được sự khác nhau giữa hàm Subtotal và hàm Sum là gì? Mời các bạn cùng theo dõi ví dụ sau đây:
Đối với trường hợp trên, nếu không có thêm điều kiện gì thì kết quả của hàm Sum là đúng.
Tuy nhiên, nếu chúng ta muốn lọc ra những người thuộc bộ phận kinh doanh. Lúc này, bảng tính vẫn có kết quả là:
Do đó, chúng ta sẽ không sử dụng đến hàm Sum mà thay vào đó là sử dụng hàm Subtotal với cú pháp như sau:
=SUBTOTAL(9,C2;C6)
Mặc dù hàm vẫn tham chiếu số liệu từ C2:C6. Tuy nhiên, kết quả tính được bằng hàm Subtotal nâng cao chỉ tính cho những ô C2, C3 và C4. Còn những ô C5, C6 sẽ bị ẩn đi và không được tính. Đó chính là sự khác biệt giữa hàm subtotal và hàm sum.
>>> Xem ngay:
- Hàm SUMIF là gì? Cách dùng hàm SUMIF đầy đủ và dễ dàng
- Hướng dẫn cách xuống dòng trong Excel hiệu quả, nhanh chóng nhất
- Hàm Vlookup là gì? Cách sử dụng hàm Vlookup trong Excel
Hàm Subtotal khác gì hàm Sum?
Cụ thể hơn giữa sự khác biệt giữa hàm Subtotal với hàm Sum như sau:
Mục đích sử dụng:
- Hàm SUM: Hàm này chỉ tính tổng cố định hoặc còn được hiểu theo cách khác là tính tổng cho tất cả những đối tượng. Nó sẽ không tách thành tổng của những nhóm riêng (Giá trị này sẽ không có sự thay đổi khi áp dụng lệnh Filter). Nếu chúng ta lồng những hàm Sum vào với nhau thì sẽ bị tính 2 lần.
- Hàm SUBTOTAL: Hàm Subtotal nhiều điều kiện có thể tính tổng cố định và khả năng di động. Có nghĩa là nó sẽ tính tổng riêng dành cho từng nhóm, giá trị sẽ được thay đổi nếu chúng ta thực hiện Filter. Nếu lồng nhiều hàm SUBTOTAL lại với nhau thì sẽ không bị tính.
Cú pháp:
- Hàm SUM: = SUM (giá trị 1;giá trị 2;…)
- Hàm SUBTOTAL: =SUBTOTAL(function_num;ref1;ref2;…)
Qua những chia sẻ trên đây, có lẽ các bạn đã biết được hàm Subtotal khác gì hàm Sum. Từ đó sẽ giúp bạn lựa chọn chính xác khi thực hiện tính toán trong bảng dữ liệu Excel.
Kết luận
Như vậy là các chuyên gia công nghệ HPConnect.vn đã chia sẻ đến các bạn những thông tin liên quan đến Hàm Subtotal. Hy vọng đã giúp các bạn hiểu rõ Hàm Subtotal là gì? Cách dùng hàm Subtotal trong Excel như thế nào? Chúc các bạn áp dụng thành công để đáp ứng cho nhu cầu công việc hiệu quả hơn nhé!
