Hàm SUBTOTAL khác gì hàm SUM? Hàm tính tổng tối ưu với dữ liệu lọc

Hàm SUBTOTAL và hàm SUM đều là những hàm cơ bản và hữu ích trong Excel để thực hiện các phép tính tổng. Tuy nhiên, giữa chúng vẫn có những điểm khác biệt cơ bản về cú pháp, cách hoạt động và mục đích sử dụng, đặc biệt là khi làm việc với dữ liệu đã được lọc hoặc ẩn. Bài viết này, HVN Group sẽ cùng bạn khám phá “hàm SUBTOTAL khác gì hàm SUM”, để từ đó đưa ra lựa chọn phù hợp nhất.

Hàm SUBTOTAL khác gì hàm SUM?

Hàm SUBTOTAL và hàm SUM đều dùng để tính tổng, nhưng SUBTOTAL có tính năng đặc biệt là bỏ qua các hàng bị ẩn hoặc bị lọc, trong khi SUM luôn tính tổng của tất cả các giá trị trong phạm vi, bất kể chúng có bị ẩn hay không. Dưới đây là những thông tin cụ thể để bạn có thể hiểu rõ hơn hàm SUBTOTAL khác gì hàm SUM:

Hàm SUM là hàm gì?

Hàm SUM trong Excel được sử dụng để tính tổng tất cả các số trong một phạm vi ô hoặc một danh sách các giá trị. Với hàm tính này, tất cả các ô bạn chỉ định sẽ được cộng tổng, bao gồm cả những ô bị ẩn (do lệnh Hide) hoặc bị lọc (do AutoFilter).

1. Cú pháp của hàm SUM

=SUM(number1, [number2], …) 

Trong đó: number1, number2, … là các số, phạm vi ô, hoặc tham chiếu ô mà bạn muốn tính tổng.

2. Ví dụ về cách dùng hàm SUM

• =SUM(A1:A10): Hàm này sẽ tính tổng tất cả các số trong phạm vi từ ô A1 đến ô A10.
• =SUM(B2, B5, B8): Hàm này sẽ tính tổng giá trị của ba ô riêng lẻ là B2, B5 và B8.
• =SUM(10, 20, 30): Hàm này sẽ tính tổng trực tiếp các số 10, 20 và 30, cho kết quả là 60.
• =SUM(C1:C5, D1:D5): Hàm này sẽ tính tổng các số trong hai phạm vi riêng biệt là C1 đến C5 và D1 đến D5.

Hàm SUBTOTAL là hàm gì?

Hàm SUBTOTAL trong Excel là một hàm tính linh hoạt và mạnh mẽ, cho phép bạn thực hiện nhiều loại phép tính khác nhau (như tính tổng, trung bình, đếm, hoặc giá trị lớn nhất/nhỏ nhất,…) trong một phạm vi dữ liệu nhất định. Điểm nổi bật của SUBTOTAL là nó có khả năng tự động bỏ qua các hàng bị ẩn (do lệnh Hide), bị lọc (do AutoFilter), hoặc thậm chí bỏ qua các công thức SUBTOTAL khác trong phạm vi tính toán để tránh tính trùng lặp.

1. Cú pháp của hàm SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], …)

Trong đó: 

• function_num: Một số (từ 1 đến 11 hoặc từ 101 đến 111) nhằm xác định loại phép tính mà bạn muốn thực hiện.

• Từ 1 đến 11: Bao gồm các giá trị bị ẩn bằng lệnh Hide thủ công.
• Từ 101 đến 111: Bỏ qua các giá trị bị ẩn bằng lệnh Hide thủ công.

• ref1, ref2, …: Là các phạm vi ô mà bạn muốn áp dụng phép tính.
• Các function_num thường dùng:

•  1 hoặc 101: Hàm tính trung bình (AVERAGE) 
• 2 hoặc 102: Hàm đếm số ô chứa số (COUNT)
• 3 hoặc 103: Hàm đếm số ô không rỗng (COUNTA) 
• 4 hoặc 104: Hàm tính giá trị lớn nhất (MAX)
• 5 hoặc 105: Hàm tính giá trị nhỏ nhất (MIN)
• 9 hoặc 109: Hàm tính tổng (SUM)

2. Ví dụ cụ thể về cách dùng hàm SUBTOTAL

• =SUBTOTAL(9, A1:A10): Tính tổng các giá trị từ A1 đến A10. Nếu có các hàng bị lọc, nó sẽ chỉ tính tổng các hàng hiển thị.
• =SUBTOTAL(109, A1:A10): Tương tự như trên, nhưng nếu có hàng bị ẩn thủ công, nó cũng sẽ không tính các hàng đó.
• =SUBTOTAL(1, B2:B20): Tính trung bình các giá trị hiển thị trong phạm vi B2:B20.

Hàm SUBTOTAL và hàm SUM có gì khác biệt?

Hàm SUBTOTAL khác gì hàm SUM? Sự khác biệt cốt lõi giữa hàm SUBTOTAL và hàm SUM trong Excel nằm ở khả năng tương tác với dữ liệu được lọc hoặc ẩn. Dưới đây sẽ là bảng so sánh chi tiết để bạn có thể hiểu rõ hơn về sự khác biệt này:

Tóm lại, cả hàm SUM và hàm SUBTOTAL đều là những công cụ mạnh mẽ để tính tổng trong Excel. Tuy nhiên, hàm SUBTOTAL thường là lựa chọn tối ưu hơn trong các phân tích có dữ liệu lọc bởi sự linh hoạt và chính xác trên tập dữ liệu đang được phân tích.

Khi nào nên sử dụng hàm SUM?

Dù đơn giản, nhưng hàm SUM vẫn đóng một vai trò rất quan trọng, đặc biệt là khi bạn cần một cái nhìn tổng quan không bị ảnh hưởng bởi những yếu tố ẩn hiện của dữ liệu. Cụ thể: 

• Tính tổng toàn bộ dữ liệu, không quan tâm đến hiển thị: Hàm SUM cho phép bạn tính tổng số cố định của tất cả các giá trị trong một phạm vi nhất định, bất kể có hàng nào đang bị ẩn đi (do bộ lọc hay ẩn thủ công) hay không. 
• Khi không sử dụng bộ lọc (AutoFilter) hoặc tính năng ẩn hàng: Nếu bạn làm việc với bảng tính đơn giản và không có nhu cầu lọc hay ẩn hàng, hoặc cụ thể bạn muốn tổng số đó không bị ảnh hưởng bởi việc ẩn hiện dữ liệu, hàm SUM là lựa chọn hiệu quả nhất.
• Tính tổng các số trực tiếp hoặc các ô rời rạc: Khi bạn muốn cộng một vài con số cụ thể hoặc giá trị từ một vài ô không liền kề, hàm SUM cũng cho phép bạn thực hiện điều này một cách nhanh chóng.
• Khi muốn tạo sự dễ hiểu cho người khác: Hàm SUM là hàm tính cơ bản và rất dễ hiểu. Do đó, nếu không cần thiết phải sử dụng hàm SUBTOTAL, việc dùng SUM sẽ giúp người khác dễ dàng nắm bắt ý nghĩa của công thức hơn.

Khi nào nên sử dụng hàm tính SUBTOTAL?

Nếu hàm SUM là hàm tính tổng cơ bản, thì hàm SUBTOTAL lại nổi bật với khả năng linh hoạt và thích ứng cao. Trong môi trường dữ liệu luôn biến động, nơi bạn liên tục phải lọc và sắp xếp để phân tích từng khía cạnh, SUBTOTAL chính là “trợ thủ đắc lực” giúp bạn có được những con số chính xác trên tập dữ liệu đang hiển thị. 

• Khi làm việc với dữ liệu có bộ lọc (AutoFilter): Khi bạn áp dụng bộ lọc cho bảng dữ liệu, hàm SUBTOTAL sẽ tự động cập nhật kết quả để chỉ tính toán trên các hàng đang hiển thị (các hàng không bị ẩn bởi bộ lọc). Điều này cực kỳ hữu ích cho việc phân tích dữ liệu theo các tiêu chí khác nhau.
• Khi cần bỏ qua các hàng bị ẩn thủ công: Nếu muốn phép tính chỉ áp dụng cho các hàng hiển thị trên màn hình, bao gồm cả việc bỏ qua các hàng đã ẩn bằng tay, bạn có thể sử dụng hàm SUBTOTAL với số đếm từ 101 đến 111 (chẳng hạn như : 109 cho SUM).
• Khi cần thực hiện nhiều loại phép tính trên dữ liệu được lọc: SUBTOTAL không chỉ tính tổng, mà còn có thể tính trung bình (AVERAGE), đếm (COUNT, COUNTA), tìm giá trị lớn nhất (MAX), nhỏ nhất (MIN),… Tất cả các phép tính này đều có thể tự động điều chỉnh theo bộ lọc.
• Khi muốn tránh trùng lặp vì có các SUBTOTAL lồng nhau: Hàm SUBTOTAL có thể bỏ qua các hàm SUBTOTAL khác trong cùng phạm vi, giúp ngăn chặn việc tính toán trùng lặp nếu bạn có nhiều công thức SUBTOTAL trong một báo cáo.
• Khi tạo báo cáo động hoặc bảng tổng hợp: SUBTOTAL là lựa chọn lý tưởng cho các báo cáo mà người dùng thường xuyên thay đổi bộ lọc để xem các tập dữ liệu khác nhau.

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

1. Có thể sử dụng hàm SUBTOTAL thay thế hoàn toàn hàm SUM được không?

Có. Bạn có thể sử dụng SUBTOTAL(9, phạm_vi) hoặc SUBTOTAL(109, phạm_vi) thay cho SUM(phạm_vi). Tuy nhiên, nếu bạn không có nhu cầu lọc hoặc bỏ qua các hàng ẩn, thì hàm SUM vẫn đơn giản và dễ đọc hơn. 

2. Hàm SUBTOTAL có tác dụng với các cột bị ẩn không?

Không. Hàm SUBTOTAL chỉ bỏ qua các hàng bị ẩn hoặc lọc, không bao gồm các cột bị ẩn.

3. Tại sao khi tôi dùng SUBTOTAL mà kết quả vẫn bao gồm các hàng bị ẩn?

Với trường hợp này, bạn có thể đã sử dụng function_num từ 1 đến 11 (ví dụ: 9 cho SUM). Nhóm này chỉ bỏ qua các hàng bị ẩn bởi bộ lọc, nhưng vẫn tính các hàng bị ẩn thủ công. Để bỏ qua cả các hàng bị ẩn thủ công, bạn cần dùng function_num từ 101 đến 111 (ví dụ: 109 cho SUM).

>>> Xem thêm: Cách tính tổng trong Excel khi lọc 

Lời kết

Tóm lại, hàm SUBTOTAL khác gì hàm SUM không chỉ nằm ở tên gọi, mà còn ở cách chúng tương tác với dữ liệu của bạn. Hàm SUM là lựa chọn đáng tin cậy cho tổng thể cố định, bất kể dữ liệu có bị ẩn hay lọc.

Ngược lại, SUBTOTAL là công cụ tối ưu cho các báo cáo động, nơi bạn thường xuyên lọc dữ liệu và cần các phép tính chính xác trên những gì đang hiển thị. Việc nắm vững hai hàm này sẽ giúp bạn làm chủ Excel hiệu quả hơn, đảm bảo tính chính xác và linh hoạt cho mọi bảng tính của mình.

Nếu có thắc mắc, bạn hãy trực tiếp liên hệ đội ngũ tư vấn viên của HVN Group qua các kênh liên lạc dưới đây để nhận được sự hỗ trợ nhanh chóng và nhiệt tình nhất.

Fanpage: HVN Group – Hệ sinh thái kiến tạo 4.0

Hotline: 024.9999.7777