Hàm Subtotal và những ứng dụng tuyệt vời !

| Thông tin cập nhật dự toán Phú Yên | Lỗi phát sinh khi tính dự toán theo PP bù chênh lệch |

* * *

Sau bài viết giới thiệu hàm xử lý chuỗi trong excel, hôm này mình tiếp tục giới thiệu đến anh em một hàm rất hay nữa của excel, đó là hàm Subtotal, hàm này có cú pháp như thế nào, ứng dụng vào việc gì ?, anh em theo dõi chi tiết ở phần tiếp sau đây của bài viết:

Mình sẽ cố gắng trình bày ngắn gọn, dễ hiểu nhất có thể:

1. Ứng dụng của hàm Subtotal:

Có nhiều ứng dụng, tùy theo nhu cầu cụ thể. Trên thực tế nó rất thường gặp với công việc kế toán, kỹ thuật.

Ví dụ:

1.1. Tính tổng khối lượng một loại hàng hóa/vật liệu nào đó trong một danh sách nhiều hàng hóa/vật liệu khi sử dụng chế độ lọc (Auto Filter) – xem video minh họa bên dưới.

1.2. Ghi số thứ tự dạng “động”, số thứ tự có thể tự thay đổi khi danh sách hàng hóa thay đổi khi khi sử dụng chế độ lọc (Auto Filter) – xem video minh họa bên dưới.

Đó là 2 trong rất nhiều ứng dụng khác. Phạm vi bài viết này sẽ trình bày 2 ứng dụng đó.

2. Cú pháp hàm:

=SUBTOTAL(x; y)

Trong đó:

x: Là một số từ 1 đến 11 hoặc 101 đến 111 để xác định dùng hàm nào trong tính toán. Có nghĩa là hàm này có thể biến hóa thành các hàm khác nhau, tùy thuộc vào việc ta cho x bằng mấy. Chẳng hạn: Khi x = 3, hàm SUBTOTAL sẽ hoạt động như hàm COUNTA (đếm số ô không trống); Khi x =  9, hàm SUBTOTAL sẽ hoạt động như hàm SUM (cộng tổng các ô).

y: Là phạm vi vùng tham chiếu mà ta muốn tính toán, ví dụ vùng “E14:E16”.

Giữa x và y ngăn cách nhau bởi dấu “;”

3. Minh họa ứng dụng trong thực tế:

3.1. Tính tổng khối lượng một mặt hàng trong danh sách nhiều mặt hàng:

Giả sử ta có 1 bảng tính excel như hình dưới:

Ta cần tính hoặc biết tổng khối lượng cho từng mặt hàng (Xi măng, Cát…). Trường hợp này, hàm SUBTOTAL là một lựa chọn rất phù hợp (hàm SumIf cũng được nhưng không linh hoạt bằng).

Gõ công thức vào ô cuối của bảng (ô E17): =SUBTOTAL(9;E4:E16) > Enter

(Số 9 trong công thức biến hàm SUBTOTAL thàm hàm SUM)

Sau đó ta sử dụng tính năng Lọc (Auto Filter) của excel để lọc từng mặt hàng (Xi măng, Cát…), kết quả sẽ tự động thay đổi trong ô E17.

3.2. Ghi số thứ tự dạng “động”:

Vấn đề tiếp theo là ghi số thứ tự, làm sao để sau khi lọc danh sách một loại hàng hóa (ví dụ Xi măng), thì cột STT thay đổi theo, như ở bảng trên, Xi măng là mặt hàng nằm ở các số thứ tự 1, 4, 6, 13, nhưng làm sao để sau khi lọc nó phải là 1, 2, 3, 4... ?

Các bước:

(1) Nhập vào ô A4 số 1 > Enter

(2) Nhập vào ô A5 công thức sau: =SUBTOTAL(3;$E$4:E4)+1 > Enter [ cũng có thể nhập =SUBTOTAL(3;$E$5:E5) cũng được ]. (Số 3 trong công thức biến hàm SUBTOTAL thành hàm COUNTA)

(3) Copy ô A5 xuống các ô A6:A13

Chỉ 3 bước vậy thôi.

Giải thích ý nghĩa: Để giải thích chi tiết cách ghi số thứ tự này cũng hơi dài dòng, anh em tự tìm hiểu thêm, tóm tắt như sau:

(1) Ô A4 là ô đầu tiên nên nó luôn là 1.

(2) Các ô từ A5:A13 biến động tùy vào danh sách lọc, nhưng có chung đặc điểm là có giá trị bằng tổng số mặt hàng hiển thị phía bên trên nó + thêm 1, từ đó hình thành công thức cho ô A5.

(3) Copy A5 xuống các ô A6:A13 là để sao chép công thức.

Xem video:

4. Link tải file excel  minh họa hàm Subtotal như trong bài: TẠI ĐÂY

Bài viết đã trình bày 2 ứng dụng tuyệt vời của hàm Subtotal trong excel, một hàm dễ nhớ, dễ dùng nhưng cực kỳ lợi hại với anh chị em làm kế toán, kỹ thuật.

Cảm ơn anh em đã quan tâm, theo dõi./.

P/S:

- Anh em có thể tìm hiểu thêm về hàm này trên Internet, tuy nhiên theo ý kiến cá nhân, mình thấy các hướng dẫn thường thiên về "lý thuyết" và cũng không có video minh họa nên khó hiểu, khó vận dụng.

- Các vấn đề cần trao đổi về excel, lập, cài đặt p/m dự toán xây dựng… vui lòng liên hệ ĐT/Zalo: 0919.410.280

* * *

Tuy Hòa, th4/2025. Ngô Tuấn Anh.