Hàm SUBTOTAL là gì? Công thức tính của hàm Subtotal như thế nào? Cách dùng hàm SUBTOTAL trong excel hay ứng dụng của hàm SUBTOTAL trong cuộc sống đặc biệt là lĩnh vực kế toán ra sao? Để bạn trả lời được các câu hỏi trên, Blogphanmem sẽ chia sẻ bài viết này. Các bạn cùng theo dõi nhé.
Bạn đang xem bài viết: Hàm SUBTOTAL là gì?
Nội dung bài viết
Hàm SUBTOTAL là gì?
Hàm SUBTOTAL cho phép người dùng gom nhóm các đối tượng và thực hiện đồng thời các câu lệnh lên chúng. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX,… (xem bảng bên dưới). Đặc biệt hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn.
Xem thêm: 3 cách sử dụng hàm Rank trong Excel
Hiểu đơn giản hàm SUBTOTAL là hàm được sử dụng để phân tích và tổng hợp dữ liệu của bạn một cách hiệu quả.
Mục đích của hàm SUBTOTAL là trả về một tổng số phụ trong danh sách hoặc cơ sở dữ liệu
Công thức tính của hàm SUBTOTAL
Công thức của hàm SUBTOTAL như sau:
=SUBTOTAL (function_num, ref1, [ref2], …)
Đơn giản hơn: =SUBTOTAL(a,b,c,..)
Cú pháp hàm SUBTOTAL có các thành phần sau đây:
function_num: (Bắt buộc) Một số chỉ định hàm nào sẽ sử dụng để tính tổng phụ trong danh sách. Xem bảng dưới đây để biết danh sách đầy đủ.
a,b,c: là các giá trị đi kèm với SUBTOTAL ví dụ như các giá trị có trong bảng sau:
- Với function_num từ 1 – 11 thì sẽ bao gồm những hàng ẩn bằng cách thu công
- Với function_num từ 101 – 111 thì sẽ loại trừ những ô bị ẩn
- Ref1 Bắt buộc. Phạm vi hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó.
- Ref2,… Tùy chọn. Phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 mà bạn muốn tính tổng phụ cho nó.
Bảng mã phía trên chỉ mang tính chất tham khảo. Bạn không cần thiết phải ghi nhớ hết tất cả các mã, phần này khi ta gõ mã phần mềm sẽ gợi ý để ta chọn cho phù hợp.
Cách dùng hàm SUBTOTAL trong Excel
Tính tổng các hàng được lọc
Bạn muốn tính tổng dữ liệu sau khi chọn lọc thì áp dụng công thức hàm SUBTOTAL trong trường hợp này sẽ là:
=SUBTOTAL(9,pham_vi)
pham_vi: 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
Bạn muốn đếm các ô được lọc không trống thì sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Và chỉ sử dụng SUBTOTAL 103 trong trường hợp có hàng ẩn để đếm chính xác các ô không trống nhìn thấy
Cần lưu ý vấn đề hàng ẩn vì sử dụng sai hàm sẽ cho ra kết quả khác nhau. Như ví dụ dưới đây ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.
Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.
Còn với SUBTOTAL 103 sẽ chỉ tính ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.
Công thức SUBTOTAL lồng nhau
Ví dụ: chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.
Công thức tính trung bình cho kho A2 =SUBTOTAL(1,C2:C4) và cho kết quả 19.
Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.
Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.
Ví dụ minh họa
Sau đây chuyên mục thủ thuật excel xin được liệt kê một vài ví dụ về SUBTOTAL được định hình cấu thành SUM, COUNT và AVERAGE để tính các giá trị trong một phạm vi. Sự khác biệt duy nhất của các hàm SUBTOTAL chính là đối số truyền vào đầu tiên của hàm:
= SUBTOTAL (109, phạm vi) // Tính tổng (SUM)
= SUBTOTAL (103, phạm vi) // Đếm (COUNT)
= SUBTOTAL (101, phạm vi) // Tính trung bình (AVERAGE)
VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109
Như đã giới thiệu ở tiêu đề, hàm SUBTOTAL có 2 loại đối số 1-11 và 101-111 có thể truyền vào. Cả 2 đối số đều bỏ qua các hàng đã được lọc ra, tuy nhiên 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để trực quan hơn ta xét ví dụ sau đây :
Để tính tổng một danh sách các ô, bạn có thể dùng cả SUBTOTAL 109 và SUBTOTAL 9 như hình dưới:
Nhưng nếu đã ẩn đi các hàng không liên quan bằng HideRows tại mục Home tab > Cells group > Format > Hide & Unhide, hoặc chuột phải vào cột số của hàng và chọn Hide, tuy nhiên bạn bây giờ chỉ muốn chọn các hàng đang được hiển thị, hãy chọn function_num là SUBTOTAL 109:
Bạn hãy làm tương tự với các chức năng khác trong bộ của hàm SUBTOTAL. Ví dụ, dùng SUBTOTAL 3 hoặc SUBTOTAL 103 để đếm ô không trống đã chọn. Nhưng hãy nhớ nếu muốn đếm các ô không trống nhìn thấy chính xác hãy dùng SUBTOTAL 103 nếu có bất kì hàng ẩn nào trong dãy.
Lưu ý:
Hàm Excel SUBTOTAL với số chức năng 101-111 không ảnh hưởng với cột bị ẩn, chỉ bỏ qua giá trị trong hàng bị ẩn. Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, thì tổng phụ của bạn sẽ bao gồm cả hàng trong cột bị ẩn.
Xem thêm: Hướng dẫn sử dụng hàm Match trong Excel
Ghi chú:
– Khi function_num nằm trong khoảng 1-11, SUBTOTAL bao gồm các giá trị bị ẩn
– Khi function_num nằm trong khoảng 101-111, SUBTOTAL loại trừ các giá trị bị ẩn
SUBTOTAL loại bổ các tổng phụ khác tồn tại trong tài liệu tham khảo được loại bỏ để ngăn chặn tình trạng bị lặp hai lần
SUBTOTAL được thiết kế tốt nhất cho các dữ liệu theo hàng dọc. Trong phạm vi Ngang, giá trị trong các cột ẩn luôn được bao gồm.
Ví dụ: Đây là một ví dụ sử dụng SUBTOTAL để tạo ra nhiều hàm khác nhau tổng hợp các giá trị trong các ô từ C2 đến C8:
Cách sửa lỗi #VALUE! hàm SUBTOTAL
– Lỗi #VALUE xảy ra khi người dùng sử dụng sai cách đối với hàm SUBTOTAL. Có thể kể đến như funcation_num không nằm trong khoảng 1-11 hoặc101-111. Hoặc tham chiếu đến một ô nào đó có giá trị không thể thực hiện được.
– Ngoài ra cũng có thể do người dù đã gõ sai công thức như thiếu dấu hoặc thiếu từ
Ví dụ: bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy nào đó ở sheet STT1.
Fix lỗi#VALUE! trong hàm SUBTOTAL
– Cách sử lỗi #value đơn giản nhất:
+ Xem lại công thức chuẩn : =SUBTOTAL (function_num, ref1, [ref2], …) xem liệu mình đã gõ đúng hay chưa.
+ Nếu sai số xác định chức năng, thì ta cần xem lại bảng chức năng đã mô tả phía trên xem có bị nằm ngoài hay không hoặc trong quá trình nhập liệu có sai sót gì hay không.
+ Hãy xoá phần tham chiếu sai và nhập lại từ đầu nếu thấy lỗi do tham chiếu, kiểm tra đúng dãy ô mà đang tham chiếu đúng với dãy ô của sheet mà bạn muốn thực hiện với hàm SUBTOTAL.
Tổng kết
Hàm SUBTOTAL là 1 hàm nâng cao sử dụng chủ yếu trong công việc. Hi vọng qua bài viết Hàm SUBTOTAL là gì? Ngoài những thông tin trên, bạn cũng cần tìm thêm các ví dụ để luyện tập thì mới có thể sử dụng thành thạo. Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn
