Hàm Subtotal trong Google Sheets và cách sử dụng

Khi làm việc với bảng dữ liệu có bộ lọc, việc tính toán chính xác theo dữ liệu đang hiển thị là yêu cầu rất quan trọng. Hàm SUBTOTAL trong Google Sheets được thiết kế để xử lý các tình huống này, giúp tổng hợp số liệu linh hoạt và hạn chế sai lệch. Nội dung sau sẽ giúp bạn hiểu rõ cách sử dụng hàm SUBTOTAL hiệu quả trong thực tế.

Hướng dẫn chi tiết cách sử dụng hàm Subtotal trong Google Sheets

Hàm SUBTOTAL trong Google Sheets là hàm thống kê cho phép thực hiện nhiều phép tính tổng hợp như tính tổng, trung bình, đếm dữ liệu hoặc xác định giá trị lớn nhất – nhỏ nhất trên một tập dữ liệu. Điểm nổi bật của hàm này là chỉ tính các dòng đang hiển thị, tự động bỏ qua những dòng đã bị ẩn hoặc lọc.

Nhờ đặc tính làm việc với dữ liệu động, SUBTOTAL giúp hạn chế sai lệch số liệu khi sử dụng bộ lọc, điều mà các hàm thông thường như SUM hay COUNT khó đáp ứng. Chỉ với một cú pháp và thay đổi mã chức năng, người dùng có thể áp dụng nhiều phép tính khác nhau, rất phù hợp cho báo cáo doanh thu, thống kê nhân sự, phân tích bán hàng và tổng hợp tài chính.

Hàm SUBTOTAL trong Google Sheets được sử dụng với cú pháp tổng quát như sau:

=SUBTOTAL(function_code; range1; [range2]; …)

Giải thích tham số:

• function_code: Mã số đại diện cho phép tính cần thực hiện, chẳng hạn tính tổng, trung bình hoặc đếm dữ liệu.
• range1, range2,…: Các vùng ô chứa dữ liệu mà hàm sẽ áp dụng phép tính.
• Thông qua việc thay đổi function_code, bạn có thể linh hoạt sử dụng SUBTOTAL cho nhiều mục đích thống kê khác nhau mà không cần dùng nhiều hàm riêng lẻ.

Công thức sử dụng hàm SUBTOTAL trong Google Sheets

Ví dụ minh họa: 

Giả sử bạn có bảng dữ liệu bán hàng với cột Thành tiền nằm trong phạm vi E2:E6. Để tính tổng phụ cho cột này, bạn nhập công thức sau tại ô E8: =SUBTOTAL(9;E2:E6)

Trong đó: function_code = 9 tương ứng với phép tính tổng. Kết quả hiển thị là tổng của các dòng đang hiển thị trong bảng. Khi bạn áp dụng bộ lọc để ẩn bớt dữ liệu, giá trị tổng phụ sẽ tự động cập nhật theo dữ liệu còn lại, giúp việc tổng hợp số liệu luôn chính xác.

Hàm SUBTOTAL trong Google Sheets tính tổng phụ theo dữ liệu đang hiển thị

Ví dụ minh họa:

Bước 1: Tạo bộ lọc cho bảng dữ liệu

Chọn toàn bộ bảng dữ liệu (bao gồm hàng tiêu đề), sau đó vào menu Dữ liệu → Tạo bộ lọc để bật chức năng lọc.

Tạo bộ lọc trong Google Sheets để chuẩn bị tính toán dữ liệu bằng hàm SUBTOTAL

Bước 2: Lọc dữ liệu theo điều kiện

Giả sử bạn cần tính tổng thành tiền của các mặt hàng thuộc Nhóm A:

• Nhấp vào biểu tượng lọc tại cột Nhóm
• Bỏ chọn tất cả và chỉ chọn A
• Xác nhận để áp dụng bộ lọc

Lọc dữ liệu theo Nhóm A trong Google Sheets trước khi áp dụng hàm SUBTOTAL

Bước 3: Nhập công thức SUBTOTAL

Tại ô cần hiển thị kết quả, nhập: =SUBTOTAL(9;E2:E6)

Trong đó:

• 9: Mã chức năng tương ứng với phép tính tổng (SUM)
• E2:E6: Phạm vi gốc của cột Thành tiền

Nhập hàm SUBTOTAL để tính tổng chính xác trên dữ liệu đã được lọc

Bước 4: Xem kết quả

Nhấn Enter, Google Sheets sẽ trả về tổng giá trị của các dòng đang hiển thị sau khi lọc, và kết quả này sẽ tự động thay đổi nếu bạn điều chỉnh điều kiện lọc.

Khi làm việc với nhiều bảng dữ liệu liên quan, bạn thường cần tra cứu thông tin từ bảng phụ rồi tiếp tục tính toán trên kết quả đó và yêu cầu kết quả thay đổi theo bộ lọc. Trong trường hợp này, việc kết hợp SUBTOTAL và VLOOKUP trong Google Sheets giúp xử lý dữ liệu linh hoạt và chính xác hơn.

Ví dụ minh họa: 

Giả sử bạn có:

• Sheet KPI: chứa danh sách nhân viên và điểm KPI tháng 11
• Sheet Nhóm: dùng để tra cứu tên nhóm theo mã nhóm

Yêu cầu đặt ra:

• Xác định điểm KPI cao nhất trong tháng 11
• Chỉ xét nhân viên thuộc Nhóm 2
• Khi áp dụng bộ lọc, kết quả phải tự động cập nhật

Bước 1: Tra cứu tên nhóm bằng VLOOKUP

Tại Sheet KPI, ở cột Nhóm (ô D2), nhập: =VLOOKUP(B2;'Nhóm'!$A$2:$B$4;2;FALSE)

Kết hợp hàm SUBTOTAL để tìm giá trị lớn nhất và tự động cập nhật khi lọc dữ liệu

Bước 2: Tính giá trị lớn nhất bằng SUBTOTAL

Tại ô hiển thị kết quả, nhập: =SUBTOTAL(104;C2:C6)

Trong đó:

• 104: Mã hàm đại diện cho MAX, đồng thời bỏ qua các dòng đã bị lọc hoặc ẩn
• C2:C6: Vùng dữ liệu chứa điểm KPI

Nhập công thức tính giá trị lớn nhất cho dữ liệu

Bước 3: Lọc dữ liệu theo Nhóm

• Chọn bảng KPI → Vào Dữ liệu → Tạo bộ lọc
• Tại cột Nhóm, chỉ chọn Nhóm 2
• Ngay sau khi lọc, giá trị SUBTOTAL sẽ tự động thay đổi, phản ánh đúng điểm KPI cao nhất của riêng Nhóm 2 mà không cần chỉnh sửa lại công thức.

Khi xử lý bảng dữ liệu lớn, bạn có thể gặp những tình huống như dòng trống xen kẽ, cần đánh số thứ tự (STT) cho dòng có dữ liệu và đặc biệt là STT phải tự cập nhật khi lọc. Nếu chỉ dùng các hàm cơ bản như ROW hay COUNT, kết quả rất dễ sai lệch khi áp dụng bộ lọc.

Để giải quyết vấn đề này, bạn có thể kết hợp SUBTOTAL và IF trong Google Sheets nhằm bỏ qua dòng trống và đảm bảo STT luôn đúng theo dữ liệu đang hiển thị.

Ví dụ minh họa:

Giả sử bạn có bảng dữ liệu nhân viên, trong đó:

• Cột B chứa tên nhân viên
• Một số dòng trong bảng có thể để trống

Yêu cầu đặt ra:

• Chỉ đánh STT cho dòng có tên nhân viên
• Dòng trống không hiển thị STT
• Khi lọc dữ liệu, STT tự động chạy lại từ đầu

Công thức áp dụng:

Tại ô STT tương ứng dòng đầu tiên (ví dụ A2), nhập: =IF(B2="";"";SUBTOTAL(3;$B$2:B2))

Giải thích:

• B2="": Kiểm tra ô chứa tên nhân viên có trống hay không
• "": Nếu trống, không hiển thị số thứ tự
• SUBTOTAL(3;…): Đếm số ô có dữ liệu đang hiển thị (COUNTA)
• $B$2:B2: Phạm vi đếm được mở rộng dần theo từng dòng

Nhờ cách kết hợp này, số thứ tự sẽ luôn chính xác, tự động thay đổi khi bạn lọc dữ liệu hoặc ẩn dòng, rất phù hợp cho bảng danh sách nhân sự, học viên hoặc báo cáo động.

Kết hợp SUBTOTAL và IF để xử lý điều kiện và đánh số thứ tự tự động trong Google Sheets

Lỗi #VALUE! thường xuất hiện khi hàm SUBTOTAL trong Google Sheets không thể thực hiện phép tính do tham số không hợp lệ. Hai nguyên nhân phổ biến nhất gồm:

• Nhập sai mã function_num: Mã chức năng không nằm trong khoảng cho phép hoặc nhập nhầm ký tự, khiến Google Sheets không nhận diện được phép tính cần thực hiện.
• Sử dụng phạm vi dữ liệu trên nhiều sheet: Hàm SUBTOTAL chỉ hoạt động trên một sheet duy nhất, nên việc tham chiếu dữ liệu trải dài trên nhiều sheet sẽ gây lỗi.

Lỗi #VALUE! khi dùng hàm SUBTOTAL do nhập sai mã chức năng hoặc chọn phạm vi không hợp lệ

Trong quá trình tính toán, nếu kết quả trả về không thể thực hiện phép chia, Google Sheets sẽ hiển thị lỗi #DIV/0!. Khi áp dụng hàm SUBTOTAL, lỗi này thường liên quan đến các phép tính như trung bình hoặc độ lệch chuẩn, vốn yêu cầu phải có dữ liệu số hợp lệ.

Nguyên nhân phổ biến là phạm vi tính toán không còn giá trị số do dữ liệu trống, chứa ký tự hoặc đã bị lọc hết. Khi không có số để xử lý, Google Sheets sẽ không thể hoàn tất phép tính và sinh ra lỗi. Để khắc phục, bạn nên kiểm tra lại vùng dữ liệu và đảm bảo vẫn còn các giá trị số đang được hiển thị.

Khi công thức chứa tên hàm không hợp lệ, Google Sheets sẽ trả về lỗi #NAME?. Trường hợp này với hàm SUBTOTAL thường xảy ra do nhập sai tên hàm, thiếu ký tự hoặc viết không đúng định dạng chuẩn mà hệ thống nhận diện.

Để khắc phục, bạn chỉ cần kiểm tra lại tên hàm và đảm bảo sử dụng đúng SUBTOTAL. Ngoài ra, cách nhập an toàn nhất là gõ dấu = và chữ S, sau đó chọn hàm SUBTOTAL từ danh sách gợi ý mà Google Sheets cung cấp, giúp tránh lỗi cú pháp không cần thiết.

Mã chức năng (Function Code) trong hàm SUBTOTAL dùng để xác định phép tính và cách bỏ qua các dòng ẩn hoặc bị lọc:

Mặc dù SUBTOTAL và SUM đều có thể dùng để cộng dữ liệu trong Google Sheets, nhưng mục đích sử dụng của hai hàm này không hoàn toàn giống nhau.

SUM phù hợp khi cần tính tổng toàn bộ phạm vi dữ liệu, bất kể các dòng đó đang hiển thị hay đã bị ẩn/lọc. Hàm này thường được dùng cho các phép tính cố định, không phụ thuộc vào bộ lọc.

SUBTOTAL được thiết kế cho bảng dữ liệu động, cho phép tính toán dựa trên các dòng đang hiển thị và có thể linh hoạt lựa chọn phép tính thông qua mã chức năng, không chỉ giới hạn ở tính tổng mà còn hỗ trợ trung bình, đếm, tìm giá trị lớn nhất hoặc nhỏ nhất.

Sự khác nhau giữa hàm SUBTOTAL và SUM trong Google Sheets khi tính toán dữ liệu có lọc

Dưới đây là các lưu ý quan trọng khi sử dụng hàm SUBTOTAL trong Google Sheets:

• Tránh đặt hàm SUBTOTAL bên trong chính vùng dữ liệu đang được tính: Việc đặt công thức SUBTOTAL ngay trong phạm vi dữ liệu có thể khiến kết quả trả về không chính xác hoặc khó kiểm soát, đặc biệt khi bảng có nhiều phép tính tổng hợp. Để đảm bảo độ ổn định, bạn nên đặt hàm SUBTOTAL ở vị trí tách biệt với vùng dữ liệu nguồn.
• Phân biệt dòng bị lọc và dòng ẩn thủ công: Google Sheets xử lý khác nhau giữa dòng bị ẩn do bộ lọc và dòng bị ẩn thủ công. Tùy mã chức năng được sử dụng, hàm SUBTOTAL có thể bỏ qua một hoặc cả hai loại dòng này. Nếu chọn không đúng mã, kết quả tính toán trong báo cáo có thể bị sai lệch.
• Không sử dụng SUBTOTAL cho dữ liệu trên nhiều sheet: Hàm SUBTOTAL chỉ hoạt động với dữ liệu nằm trong một sheet duy nhất. Việc tham chiếu dữ liệu từ nhiều sheet trong cùng một công thức sẽ không được hỗ trợ và dễ phát sinh lỗi.
• Đảm bảo dữ liệu đầu vào là dữ liệu số: SUBTOTAL chỉ cho kết quả chính xác khi phạm vi tính toán chứa các giá trị số hợp lệ. Các ô trống, văn bản hoặc số được lưu dưới dạng text có thể làm kết quả bị sai hoặc không hiển thị.
• Cẩn trọng khi kết hợp SUBTOTAL với các hàm điều kiện: SUBTOTAL không xử lý điều kiện trực tiếp như các hàm SUMIF hay COUNTIF. Khi cần áp dụng điều kiện, bạn nên lọc dữ liệu trước bằng các hàm hỗ trợ, sau đó mới sử dụng SUBTOTAL để tính toán.

Hàm SUBTOTAL mang lại lợi thế rõ rệt khi xử lý dữ liệu động, đặc biệt trong các bảng có lọc hoặc ẩn dòng. Việc nắm rõ function code, cú pháp và các lưu ý quan trọng sẽ giúp bạn áp dụng hàm chính xác hơn trong báo cáo và phân tích dữ liệu. Tuy nhiên, các nội dung trong bài viết chỉ mang tính tham khảo, bạn nên điều chỉnh cho phù hợp với từng bảng dữ liệu cụ thể.

Để làm việc hiệu quả với Google Sheets và các bảng dữ liệu lớn, bạn nên sử dụng máy tính bảng có hiệu năng ổn định. Tham khảo các sản phẩm công nghệ chính hãng tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để phục vụ tốt cho học tập, công việc và xử lý dữ liệu hằng ngày.