Công thức Excel SUBTOTAL và cách sử dụng

Microsoft Excel có rất nhiều hàm dùng để tính tổng, và SUBTOTAL là một trong số đó. Tuy nhiên, Subtotal khác với hàm SUM. Nó không phải tính tổng thông thường. Vậy công thức Excel SUBTOTAL là gì? Công thức này được sử dụng như thế nào? Hãy cùng EDUSA về công thức Excel SUBTOTAL qua bài viết này nhé!

Nội dung bài viết

1. Công thức Excel SUBTOTAL là gì?

Hàm SUBTOTAL trong Excel được dùng để tính tổng phụ. Hàm Excel SUBTOTAL trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể tính tổng, số trung bình, số lượng, tối đa… Excel này cũng có thể bao gồm hoặc loại bỏ giá trị trong các hàng ẩn.

Mục đích: Tính tổng số phụ trong một danh sách hoặc database.
Giá trị trả về: Một số đại diện cho một kiểu tổng phụ.
Đối số:
- function_num – Một số chỉ định sử dụng hàm nào để tính tổng phụ trong danh sách.
- ref1 – Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.
- ref2 – [tùy chọn] Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.

SUBTOTAL hoạt động với các tính năng Excel khác, chẳng hạn như lọc ô. Nếu bạn lọc một bảng bằng một giá trị, công thức Excel SUBTOTAL sẽ cập nhật, nhận dạng bộ lọc và loại trừ các ô tương ứng. SUBTOTAL cũng hữu ích để bỏ qua các giá trị ẩn, điều mà những hàm khác (như SUM) không thể làm được.

2. Công thức Excel SUBTOTAL

Hàm SUBTOTAL hoạt động bằng cách tính toán tổng giá trị của một phạm vi ô, dựa trên một hàm toán học khác. SUBTOTAL sử dụng tới 11 hàm toán học khác nhau (từ AVERAGE đến VAR.P) để tìm tổng giá trị phù hợp, đồng thời làm việc trong các tham số của hàm (ví dụ, bỏ qua các công thức SUBTOTAL khác).

Hàm SUBTOTAL có cú pháp: =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:

Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
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 thiết kế để tính toán cho các cột số liệu theo chiều dọc.
Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.
Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

Xem thêm: Các cách tự học Excel hiệu quả

3. Cách sử dụng công thức Excel SUBTOTAL

Tính tổng các hàng được lọc: Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là: =SUBTOTAL(9,pham_vi)

pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

Đếm các ô được lọc không trống: Các bạn có thể sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên, trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

4. Những điều cần cân nhắc trước khi sử dụng công thức Excel SUBTOTAL

Mặc dù SUBTOTAL có những ưu điểm, nhưng cũng có một số điều bạn cần cân nhắc trước khi bắt đầu sử dụng hàm này, bao gồm những điểm sau:

Sử dụng 1-11 cho đối số function_num sẽ đảm bảo rằng SUBTOTAL bao gồm các giá trị ẩn, trong khi 101-111 bỏ qua chúng.
Sử dụng giá trị khác 1-11 hoặc 101-111 sẽ khiến Excel trả về lỗi #VALUE!. Điều này cũng sẽ xảy ra đối với tham chiếu ô 3D (trong đó cùng một ô, trên nhiều trang tính, được tham chiếu trong một phạm vi).
Khi các phạm vi ô ngang như A1:D1 được sử dụng, các giá trị ẩn sẽ tự động được đưa vào (bất kể giá trị đối số function_num). Đây là một hạn chế của hàm SUBTOTAL và không thể khắc phục được.
Sử dụng SUBTOTAL trên dữ liệu được lọc sẽ đảm bảo rằng các giá trị ẩn luôn bị bỏ qua, bất kể đối số function_num được sử dụng.
SUBTOTAL có thể được sử dụng dưới dạng hàm lồng nhau như một phần của các hàm khác, bao gồm những công thức có chứa hàm IF.
Nếu một công thức Excel SUBTOTAL khác nằm trong phạm vi dữ liệu ref1, thì SUBTOTAL sẽ bỏ qua và loại trừ nó khỏi phép tính tổng thể.
Excel cho phép sử dụng tối đa 254 phạm vi ô trong công thức SUBTOTAL, mặc dù chỉ cần 1 phạm vi ô để công thức hoạt động.

5. Cách lỗi khi thực hiện công thức Excel SUBTOTAL

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

#VALUE!: Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
#DIV/0!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).
#NAME? tên hàm SUBTOTAL sai chính tả.

6. Một số mẹo về cách dùng công thức Excel SUBTOTAL

Mẹo 1: Giả sử bạn muốn đảm bảo tất cả màu áo phông đều có sẵn ở mọi kích thước của một trong hai nhà kho. Bạn có thể làm theo những bước sau:

Bước 1: Click Subtotal. Nhớ rằng đây là bước thêm nhiều tiêu chí cho dữ liệu tổng phụ hiện tại.

Bước 2: Chọn COUNT từ menu thả xuống và Size từ trường “Add subtotal field to”. Sau đó, bỏ tích Replace current subtotals. Khi click OK, bạn sẽ nhận được bảng dữ liệu sau:

Điều này giúp đảm bảo rằng bạn có đủ số lượng các kích thước khác nhau và có thể phân loại dữ liệu theo cách không cần phải lặp lại thao tác.

Mẹo 2: Luôn hữu ích khi cần phân loại dữ liệu theo cột.
Mẹo 3: Giúp bạn bao gồm một nhãn trong hàng đầu tiên.
Mẹo 4: Nếu muốn tóm tắt dữ liệu, bỏ tích ô “Summary below the data when inserting Subtotal.” – Tóm tắt bên dưới dữ liệu khi chèn hàm SUBTOTAL.

Xem thêm: Cách định dạng trang tính Excel chi tiết nhất

7. Trung tâm đào tạo các khóa học Excel uy tín – EDUSA

Hiện nay, có rất nhiều trung tâm tin học được mở ra nhằm phục vụ những nhu cầu của các bạn. Tuy nhiên, mỗi trung tâm lại có chất lượng giảng dạy khác nhau, và theo sau đó là những khóa học với chi phí và ưu đãi khác nhau.

Đến với trung tâm EDUSA, bạn sẽ được tư vấn kỹ lưỡng về những khoá học phù hợp với bạn nhất. Bên cạnh đó, những mức phí của khóa học đều nằm ở mức trung bình giúp bạn có thể chi trả một cách dễ dàng. Mặc dù mức phí khá thấp nhưng chất lượng giảng dạy tại EDUSA vô cùng tuyệt vời, đội ngũ giáo viên có chuyên môn cao, nhiệt tình giảng dạy các bạn học viên.

Và đặc biệt hơn, EDUSA sẽ cung cấp một vài buổi học và thi lại miễn phí cho các bạn nếu các bạn thi không đạt chứng chỉ ở lần thi đầu tiên. Thật tuyệt vời phải không nào? Hãy đến trung tâm EDUSA để được tư vấn kỹ hơn 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

Trung tâm đào tạo các khóa học Excel uy tín - EDUSA — Trung tâm đào tạo các khóa học Excel uy tín – EDUSA

8. Câu hỏi thường gặp

8.1 Khóa học MOS tại EDUSA có gì?

EDUSA hiện đang đào tạo các chứng chỉ MOS Word, Excel, Powerpoint. Học viên sau khi tham gia khóa học có thể tự tin lấy chứng chỉ MOS một cách dễ dàng.

8.2 Nên lựa chọn luyện thi Excel tại nhà hay trung tâm?

Điều này phụ thuộc vào điều kiện, hoàn cảnh của mỗi người. Nếu bạn đang đi làm, khó có thể sắp xếp được thời gian hay khoảng cách địa lý quá xa thì nên lựa chọn phương pháp luyện thi Excel tại nhà. Còn bạn đang là học sinh hoặc sinh viên thì nên ôn ở trung tâm.

8.3 Những ai nên học và thi Excel?

Sinh viên các trường đại học, cao đẳng trên cả nước cần có chứng chỉ Excel để đáp ứng điều kiện xét tốt nghiệp.
Người lao động cần chứng chỉ Excel để tạo thêm ấn tượng trong hồ sơ tuyển dụng, hỗ trợ tối ưu các kỹ năng làm việc văn phòng.

9. Kết luận

Sử dụng công thức Excel SUBTOTAL có thể giúp bạn nhanh chóng phân tích tập dữ liệu mà không cần dựa vào các bảng tổng hợp phức tạp hơn. Nếu gặp khó khăn, bạn cũng có thể sử dụng tính năng Subtotal để tự động tạo công thức SUBTOTAL mà sau đó bạn có thể chỉnh sửa hoặc sao chép ở nơi khác. Bên cạnh đó, nếu như bạn đang tìm một khóa học Excel chất lượng thì bạn có thể đến trực tiếp trung tâm EDUSA để được tư vấn và lựa chọn khóa học Excel phù hợp với bạn nhé.