Hướng dẫn sử dụng hàm SUBTOTAL trong Google Sheets từ A-Z

Trong quá trình làm việc với Google Sheets, khi xử lý dữ liệu, lọc dữ liệu hay tạo báo cáo tổng hợp, nhiều người gặp khó khăn khi các hàm tính toán thông thường như SUM cho ra kết quả không chính xác. Lúc này, bạn có thể dùng hàm SUBTOTAL trong Google Sheets để thay thế. Bài viết này sẽ hướng dẫn bạn sử dụng hàm SUBTOTAL trong Google Sheets chi tiết để dễ dàng áp dụng trong các trường hợp gặp khó khăn. Hãy cùng tham gia nội dung cụ thể và chính xác nhất. 

Hàm SUBTOTAL trong Google Sheets là gì?

Hàm SUBTOTAL trong Google Sheets là một hàm thống kê đặc biệt. Nó được thiết kế để thực hiện các phép tính tổng hợp như tính tổng, trung bình, đếm số lượng, tìm giá trị lớn nhất hoặc nhỏ nhất trên một tập dữ liệu. Đặc biệt, hàm SUBTOTAL có khả năng bỏ qua các dòng đã bị lọc hoặc ẩn. 

Về bản chất, hàm SUBTOTAL là một công cụ hỗ trợ xử lý dữ liệu động. Khi bạn áp dụng bộ lọc trong Google Sheets, dữ liệu hiển thị có thể thay đổi liên tục. Nếu sử dụng các hàm như SUM hoặc COUNT, kết quả vẫn tính cả những dòng đã bị ẩn, dẫn đến sai lệch số liệu. Trong khi đó, hàm SUBTOTAL trong Google Sheets sẽ tự động chỉ tính các dòng đang hiển thị, giúp kết quả luôn chính xác theo ngữ cảnh bạn đang xem.

Trong thực tế, việc sử dụng hàm SUBTOTAL tức là thay vì phải viết hàng chục hàm khác nhau, bạn chỉ cần ghi nhớ một cú pháp duy nhất và thay đổi mã chức năng. Sau đó, bạn chỉ cần thực hiện các phép toán như tính tổng, tính trung bình, đếm ô, tìm giá trị lớn nhất, nhỏ nhất. Công dụng chính của hàm SUBTOTAL trong Google Sheets đó là:

• Tính tổng, trung bình, đếm số lượng dữ liệu sau khi lọc.
• Tạo các bảng tổng phụ trong báo cáo.
• Hạn chế lỗi tính trùng khi sử dụng nhiều hàm tổng hợp.
• Hỗ trợ phân tích dữ liệu theo nhóm một cách linh hoạt.

Tính tính năng hữu ích của mình, hàm SUBTOTAL được ứng dụng vào khá nhiều lĩnh vực khác nhau. Cụ thể như là:

• Báo cáo doanh thu theo từng tháng, quý hoặc năm
• Thống kê nhân sự, theo dõi chấm công và lương thưởng
• Phân tích số liệu bán hàng và quản lý lượng hàng tồn kho
• Tổng hợp dữ liệu kế toán và tình hình tài chính

Tại sao nên dùng hàm SUBTOTAL thay vì các hàm tính toán thông thường?

Nhiều người mới sử dụng Google Sheets thường có thói quen dùng các hàm quen thuộc như SUM, COUNT để tính toán. Tuy nhiên, khi dữ liệu bắt đầu phức tạp hơn, đặc biệt là có lọc, ẩn dòng hoặc phân nhóm, những hàm này sẽ bị hạn chế. Đây chính là một trong những lý do bạn nên ưu tiên sử dụng hàm SUBTOTAL trong Google Sheets, cũng như một vài lợi ích khác. Như là:

• Thứ nhất là tính linh hoạt theo trạng thái hiển thị dữ liệu

Khi bạn lọc dữ liệu theo điều kiện, hàm SUBTOTAL chỉ tính các dòng đang được hiển thị, trong khi hàm SUM vẫn tính toàn bộ vùng dữ liệu ban đầu. 

• Thứ hai là giúp tránh hiện tượng tính trùng dữ liệu

Nếu bạn lồng nhiều phép tính tổng trong cùng một bảng, việc sử dụng hàm SUM có thể dẫn đến kết quả bị cộng lặp. Hàm SUBTOTAL sẽ giúp nhận diện và bỏ qua các kết quả không đúng, kết quả sẽ chính xác hơn.

• Thứ ba là hỗ trợ nhiều loại phép tính trong cùng một hàm thông qua mã function_code

Thay vì phải nhớ và dùng nhiều hàm khác nhau, bạn chỉ cần thay đổi mã để chuyển từ tính tổng sang trung bình, đếm, tìm max/min… Hàm SUBTOTAL sẽ giúp bảng tính gọn gàng và dễ quản lý hơn.

• Cuối cùng là hữu ích khi làm báo cáo, dashboard hoặc bảng tổng hợp

Bởi hàm SUBTOTAL trong Google trang tính sẽ giúp giảm công thức dư thừa. Đồng thời, giúp tăng tốc độ xử lý dữ liệu và dễ bảo trì cũng như chỉnh sửa file. 

Điểm khác biệt của hàm SUBTOTAL với hàm SUM trong Google trang tính

Mặc dù cùng dùng để tính tổng, nhưng hàm SUBTOTAL và SUM trong Google Sheets phục vụ hai mục đích hoàn toàn khác nhau. Do đó, việc hiểu rõ sự khác biệt này sẽ giúp bạn chọn đúng hàm trong từng tình huống sử dụng.

• Về công dụng

Hàm SUM, đây là hàm tính tổng tĩnh. Một khi bạn đã thiết lập vùng tham chiếu, ví dụ =SUM(A1:A10). Lúc này, thì dù bạn có làm gì với các dòng từ 1 đến 10 (lọc bỏ hay ẩn đi), kết quả của hàm SUM vẫn giữ nguyên giá trị của cả 10 dòng đó. Tuy nhiên, nếu bạn muốn sử dụng bộ lộ Filter để biến phạm vi của một cùng giá trị cụ thể trong 10 dòng tham chiếu. Lúc này, hàm SUBTOTAL sẽ gặp khó khăn và không thực hiện được phép toán. 

Ngược lại, hàm SUBTOTAL là một hàm động. Nó thay đổi giá trị trả về dựa trên những gì hiển ra. Khi bạn áp dụng Filter, hàm SUBTOTAL sẽ tự động loại trừ các giá trị không thỏa mãn điều kiện lọc ra khỏi phép tính. Bên cạnh đó, hàm SUBTOTAL còn có cơ chế tự loại trừ các hàm SUBTOTAL khác trong vùng tham chiếu để tránh trùng. Đây là một tính năng mà hàm SUM hoàn toàn không có. 

• Về mặt chức năng

Hàm SUM chỉ có một nhiệm vụ duy nhất là cộng. Trong khi hàm SUBTOTAL chỉ với một cấu trúc hàm, có thể thực hiện 11 phép toán khác nhau. Bạn chỉ cần thay đổi mã số chức năng trong hàm SUBTOTAL. 

Cú pháp và các tham số của hàm SUBTOTAL trong Google Sheets

Để biết cách sử dụng hàm SUBTOTAL trong Google Sheets, trước tiên bạn phải hiểu rõ cấu trúc của nó. 

Cú pháp tổng quát

Cú pháp của hàm SUBTOTAL trong Google Sheets đầy đủ như sau:

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

Trong đó:

• function_code (bắt buộc): Con số này sẽ quyết định hàm SUBTOTAL sẽ thực hiện phép tính gì (tổng, trung bình, hay đếm…) và nó có xử lý các hàng ẩn thủ công hay không.
• range1 (bắt buộc): Dải ô đầu tiên mà bạn muốn tính toán. 
• range2, …(tùy chọn): Các dải ô bổ sung nếu bạn muốn tính toán trên nhiều vùng không liền kề. 

Danh sách mã function_code trong Google Sheets

Đây là phần quan trọng nhất trong cách sử dụng hàm SUBTOTAL trong Google Sheets. Dưới đây là bảng tổng hợp chi tiết các mã chức năng trong hàm SUBTOTAL.

*Lưu ý quan trọng:

Để trong quá trình sử dụng hàm SUBTOTAL không gặp khó khăn, bạn cần phải hiểu khi nào nên dùng các mã. Trước tiên, bạn cần hiểu về hai cách ẩn dữ liệu trong Google Sheets như sau:

• Ẩn do Bộ lọc (Filter): 

Đây là khi bạn dùng công cụ Filter để lọc dữ liệu. Trong trường hợp này, cả hai nhóm mã (1-11 và 101-111) đều hoạt động giống nhau. Chúng đều bỏ qua các hàng bị lọc ra.

• Ẩn thủ công (Hide rows): 

Đây là khi bạn bôi đen các hàng, chuột phải và chọn “Ẩn hàng” (Hide).

Nhóm mã 1 – 11: Vẫn tính toán cả những giá trị trong các hàng bạn đã ẩn thủ công. Nó chỉ quan tâm đến bộ lọc Filter mà thôi.

Nhóm mã 101 – 111: Loại bỏ hoàn toàn các hàng ẩn thủ công ra khỏi phép tính. Kết quả trả về chỉ dựa trên những gì bạn thực sự nhìn thấy trên màn hình.

Hướng dẫn cách sử dụng hàm SUBTOTAL qua các ví dụ thực tế

Để hiểu và sử dụng thành thạo hàm SUBTOTAL trong Google Sheets, bạn có thể áp dụng và các dữ liệu thực tế. Dưới đây là một số ví dụ áp dụng hàm SUBTOTAL thực tế, các bạn có thể tham khảo. 

Cách sử dụng hàm SUBTOTAL trong Google Sheets để tạo bảng phụ

Trong các báo cáo bán hàng, nhân sự hoặc tài chính, bạn thường cần tổng hợp số liệu theo từng nhóm. Ví dụ, tính tổng doanh thu theo khu vực, theo nhân viên hoặc theo tháng. Nếu chỉ dùng hàm SUM, bạn rất dễ gặp lỗi tính trùng dữ liệu khi báo cáo ngày càng mở rộng. Lúc này, hàm SUBTOTAL trong Google Sheets sẽ là lựa chọn phù hợp để tạo bảng tổng phụ linh hoạt. 

*Giả sử bạn có bảng dữ liệu bán hàng như sau:

Yêu cầu: Tạo bảng tổng phụ doanh thu theo từng khu vực. Tránh việc cộng trùng khi dữ liệu mở rộng và công thức phải dễ quản lý và mở rộng sau này.

Công thức: 

=SUBTOTAL(9; D2:D3)   (cho Hà Nội)

=SUBTOTAL(9; D4:D5)   (cho Đà Nẵng)

=SUBTOTAL(9; D6:D7)   (cho TP.HCM)

Trong đó: 

• 9 là mã chức năng (function_code), tính tổng
• D2:D3 là vùng dữ liệu mà hàm sẽ thực hiện tính toán

Kết quả:

Cách dùng hàm SUBTOTAL trong Google Sheets để tính trên dữ liệu đã lọc

Một trong những ưu điểm lớn nhất của hàm SUBTOTAL trong Google Sheets là khả năng chỉ tính trên các dòng đang hiển thị sau khi lọc dữ liệu. Đây là điều mà các hàm như SUM, AVERAGE không làm được. 

*Giả sử với bảng dữ liệu bán hàng ở trên:

Yêu cầu: Lọc dữ liệu theo Khu vực = Hà Nội. Tính tổng doanh thu của Hà Nội sau khi lọc. Kết quả phải thay đổi linh hoạt khi đổi điều kiện lọc.

Công thức:

Trước tiên, bạn cần bật bộ lọc và chọn toàn bộ bảng bằng cách vào Dữ liệu (Data) >> chọn Tạo bộ lọc (Create a filter).

Sau đó, tại ô cần tính tổng, nhập công thức

=SUBTOTAL(9; D2:D7)

Trong đó:

• 9 là mã chức năng (function_code), tính tổng
• D2:D7 là vùng dữ liệu mà hàm sẽ thực hiện tính toán

Kết quả:

Một số ví dụ nâng cao – Cách dùng hàm SUBTOTAL trong Google Sheets với hàm khác

Hàm SUBTOTAL không chỉ mạnh khi dùng 1 mình trong công thức. Bạn có thể kết hợp nó với nhiều hàm khác để mở ra khả năng xử lý dữ liệu trong nhiều trường hợp phức tạp khác. Chẳng hạn như tổng hợp dữ liệu sau khi tra cứu, tính toán có điều kiện, báo cáo động theo bộ lọc. 

Hàm SUBTOTAL kết hợp cùng với hàm VLOOKUP

Trong các bảng báo cáo bán hàng hoặc quản lý nhân sự, dữ liệu thường được chia thành nhiều bảng khác nhau. Ví dụ, một bảng tính chính để tính toán và một bảng phụ để tra cứu thông tin. Khi bạn muốn tra cứu dữ liệu bằng VLOOKUP, sau đó tổng hợp hoặc tìm min/max các giá trị tra cứu đó và có thể kết quả phải thay đổi theo bộ lọc. Lúc này, việc kết hợp giữa hàm SUBTOTAL và VLOOKUP là giải pháp hoàn hảo nhất.

*Giả sử với hai bảng dữ liệu sau:

Bảng KPI nhân viên (Sheet KPI)

Bảng danh sách nhóm (Sheet Nhóm)

*Yêu cầu: Sử dụng 2 bảng dữ liệu trên để tính toán với 3 yếu tố sau:

• Tìm điểm KPI cao nhất trong tháng 11. 
• Chỉ xét nhân viên thuộc Nhóm 2. 
• Khi lọc dữ liệu, kết quả phải tự động cập nhật

*Cách tính chi tiết.

Tại Sheet KPI, ô D2 (Cột Tên nhóm):

=VLOOKUP(B2;’Nhóm’!$A$2:$B$4;2;FALSE)

Sau đó, bạn nhập công thức tại ô mới để tính điểm.

=SUBTOTAL(104; C2:C6)

Trong đó:

• 104: Là mã hàm đại diện cho hàm MAX (tìm giá trị lớn nhất) và có tính năng bỏ qua các hàng đã bị ẩn thủ công hoặc bị lọc ra.
• C2:C6: Vùng dữ liệu chứa điểm KPI của nhân viên.

Kết quả:

Để tìm KPI cao nhất của riêng Nhóm 2, bạn thực hiện các bước sau:

– Bước 1: Bạn chọn vùng dữ liệu bảng KPI >> Vào menu Dữ liệu >> Tạo bộ lọc.

– Bước 2: Bạn hãy nhấn vào biểu tượng bộ lọc ở cột Nhóm (hoặc cột Tên nhóm vừa tạo) và chỉ chọn “2” (hoặc “Marketing”).

Ô công thức SUBTOTAL sẽ tự động nhảy số.

• Khi chưa lọc: Kết quả là 95 (NV05).
• Khi lọc Nhóm 2: Kết quả sẽ tự động cập nhật thành 95 (Vì Nhóm 2 có NV02 đạt 92 và NV05 đạt 95).

Hàm SUBTOTAL cùng kết hợp với hàm IF

Trong quá trình làm việc với bảng dữ liệu lớn, bạn sẽ thường xuyên gặp các tình huống phức tạp. Chằng hạn như:

• Bảng dữ liệu có dòng trống xen kẽ dòng có dữ liệu
• Cần đánh số thứ tự tự động cho các dòng có dữ liệu
• Khi lọc dữ liệu, số thứ tự phải tự động chạy lại từ đầu

Nếu chỉ sử dụng các hàm thông thường như COUNT hoặc ROW, kết quả rất dễ bị sai khi áp dụng bộ lọc. Lúc này, bạn có thể lồng ghép hàm SUBTOTAL kết hợp với hàm IF để xử lý điều kiện logic, bỏ qua các dòng trống và đảm bảo kết quả luôn đúng với dữ liệu đang hiển thị.

*Giả sử với bảng dữ liệu như sau:

Yêu cầu: Dùng bảng dữ liệu trên đây để tính:

• Chỉ đánh số thứ tự cho những 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 phải tự động cập nhật lại

Công thức: 

=IF(B2=””; “”; SUBTOTAL(3; $B$2:B2))

Trong đó:

• B2=”” → Kiểm tra ô B2 có trống hay không
• “” → Nếu ô B2 trống, không hiển thị STT
• SUBTOTAL(…) → Nếu ô B2 có dữ liệu, tiến hành đánh số thứ tự
• 3 tương ứng với COUNTA
• Phạm vi $B$2:B2 mở rộng dần theo từng dòng

Kết quả: 

Lỗi và cách khắc phục khi sử dụng hàm SUBTOTAL trong Google Sheets

Trong quá trình áp dụng hàm SUBTOTAL trong Google trang tính, nhiều người rất dễ gặp phải lỗi công thức. Nhưng lỗi này thường không quá phức tạp, nếu bạn hiểu rõ nguyên nhân thì dễ dàng sửa chữa. Dưới đây là một số lỗi phổ biến và cách khắc phục cụ thể, các bạn có thể tham khảo.

Lỗi #VALUE!

Lỗi #VALUE! là lỗi xuất hiện khá thường xuyên khi dùng hàm SUBTOTAL trong Google Sheets. Lỗi này cho biết rằng một hoặc nhiều tham số trong công thức không hợp lệ, khiến Google Sheets không thể thực hiện phép tính.

Lỗi #VALUE! thông thường sẽ xảy ra do hai nguyên nhân chính sau đây:

Nguyên nhân 1: Mã function_num không hợp lệ

Trong hàm SUBTOTAL, tham số đầu tiên là function_num (mã định chức năng). Mã này phải nằm trong khoảng 1–11 (bỏ qua các dòng bị lọc) hoặc 101–111 (bỏ qua cả dòng bị lọc và dòng ẩn thủ công). Nếu bạn nhập một số ngoài hai khoảng trên hoặc nhập nhầm ký tự, chữ cái, Google Sheets sẽ ngay lập tức trả về lỗi #VALUE!.

Do đó, bạn hãy kiểm tra lại function_num bạn đang dùng. Bạn cần đảm bảo mã phù hợp với phép tính mong muốn (ví dụ: 9 hoặc 109 cho SUM, 1 hoặc 101 cho AVERAGE).

Nguyên nhân 2: Phạm vi dữ liệu là tham chiếu 3D

Hàm SUBTOTAL không hỗ trợ tham chiếu 3D, tức là không thể tính toán dữ liệu trải dài trên nhiều sheet cùng lúc. Công thức sai sẽ là =SUBTOTAL(9, Sheet1!A2:A10, Sheet2!A2:A10). Trong trường hợp này, Google Sheets sẽ trả về lỗi #VALUE! vì SUBTOTAL chỉ hoạt động trên một sheet duy nhất. 

Cách khắc phục hiệu quả là bạn hãy gộp dữ liệu về cùng một Sheet trước khi dùng hàm SUBTOTAL. Hoặc bạn có thể dùng các hàm khác như QUERY, FILTER để xử lý dữ liệu đa sheet rồi mới dùng SUBTOTAL.

Lỗi #DIV/0!

Lỗi #DIV/0! xuất hiện khi Google Sheets cố gắng thực hiện phép chia cho 0. Trong hàm SUBTOTAL, lỗi này thường xảy ra khi bạn sử dụng các mã liên quan đến:

• Trung bình cộng (AVERAGE – function_num 1 hoặc 101)
• Độ lệch chuẩn (STDEV – function_num 7, 107…)

Nguyên nhân gây ra lỗi là phạm vi dữ liệu không chứa giá trị số nào. Tất cả các ô trong vùng tính đều trống, chứa văn bản hoặc bị lọc hết. Khi đó, Google Sheets không thể tính trung bình hoặc độ lệch chuẩn vì không có số để chia, dẫn đến lỗi #DIV/0!.

Để khắc phục lỗi này, bạn cần kiểm tra lại vùng dữ liệu xem có giá trị số hay không. Đồng thời, bạn cần đảm bảo dữ liệu chưa bị lọc hết. 

Lỗi #NAME?

Lỗi #NAME? là lỗi cú pháp, xảy ra khi Google Sheets không nhận diện được tên hàm trong công thức. Với hàm SUBTOTAL, lỗi ngày thường do gõ sai tên hàm (ví dụ: =SUBTOLTAL, =SUB_TOTAL). Đồng thời, có thể là do sử dụng sai ngôn ngữ hàm.

Để khắc phục lỗi, bạn cần đảm bảo rằng nhập đúng tên hàm là SUBTOTAL. Hoặc khi nhập công thức, bạn chỉ cần gõ dấu = và chữ S, Google Sheets sẽ hiển thị danh sách gợi ý. Nhấp chọn SUBTOTAL từ danh sách để tránh sai sót cú pháp.

Lưu ý quan trọng khi dùng hàm SUBTOTAL trong Google Sheets

Dưới đây là một số lưu ý quan trọng bạn phải nắm rõ khi dùng hàm SUBTOTAL. Từ đó, quá trình áp dụng hàm tính toán công thức được suôn sẻ và đảm bảo đúng chính xác.

• Hiểu rõ sự khác biệt giữa dòng bị lọc và dòng bị ẩn thủ công

Hàm SUBTOTAL là hàm xử lý các dòng không hiển thị. Đây là một khái niệm không đúng hoàn toàn. Trong Google Sheets sẽ phân biệt rất rõ giữa dòng bị ẩn do bộ lọc (Filter) và dòng bị ẩn thủ công (Hide row). 

Bạn cần lưu ý rằng không phải tất cả các mã function_num của hàm SUBTOTAL đều bỏ qua cả hai loại dòng này. Các mã từ 1–11 chỉ bỏ qua dòng bị lọc, vẫn tính dòng bị ẩn thủ công. Còn các mã từ 101–111 thì bỏ qua cả dòng bị lọc và dòng bị ẩn thủ công.

Nếu chọn không đúng mã, kết quả tính toán có thể sẽ sai số trong báo cao. 

• Không sử dụng hàm cho tham chiếu nhiều Sheet

Hàm SUBTOTAL không hỗ trợ tham chiếu nhiều Sheet, tức là không thể tính toán trên nhiều Sheet cùng lúc. Ví dụ, công thức =SUBTOTAL(9, Sheet1!A2:A10, Sheet2!A2:A10) sẽ bị lỗi. 

Trong trường hợp này, bạn cần tổng hợp các dữ liệu về 1 Sheets, hoặc sử dụng thêm các hàm QUERY, IMPORTRANGE, FILTER để xử lý dữ liệu trước, sau đó mới áp dụng SUBTOTAL.

• Tránh đặt hàm SUBTOTAL trong vùng dữ liệu đang được tính

Hàm SUBTOTAL có thể tự động bỏ qua các ô chứa kết quả SUBTOTAL khác để tránh cộng trùng. Tuy nhiên, nếu bạn đặt hàm SUBTOTAL bên trong cùng một vùng dữ liệu đang được tính. Lúc này, kết quả có thể không đúng. 

• Đảm bảo dữ liệu đầu vào là dữ liệu số

Hàm SUBTOTAL chỉ tính toán chính xác khi vùng dữ liệu là số. Nếu trong phạm vi có văn bản, ô trống, số được lưu dưới dạng text. Lúc này, kết quả hàm SUBTOTAL sẽ bị sai hoặc không được tính. 

• Cẩn thận khi kết hợp hàm SUBTOTAL với hàm điều kiện

Hàm SUBTOTAL không hoạt động trực tiếp với điều kiện như SUMIF hay COUNTIF. Nếu bạn muốn áp dụng điều kiện, bạn cần dùng hàm FILTER để lọc dữ liệu trước. Sau đó, bạn sử dụng hàm SUBTOTAL để tính toán. 

Lời kết

Nắm vững cách sử dụng hàm SUBTOTAL trong Google Sheets rất quan trọng. Bởi khi thành thạo cách dùng, bạn sẽ tính toán chính xác hơn, nâng cao hiệu suất làm việc và tiết kiệm thời gian áp dụng công thức.

Nếu trong quá trình sử dụng hàm hoặc có thắc mắc gì về tính năng, đừng ngại liên hệ với GCS Việt Nam qua các kênh chính thức. Đội ngũ chuyên gia, kỹ thuật viên của GCS Việt Nam sẽ hỗ trợ bạn nhanh chóng, kịp thời nhất.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777