Bạn đã quen thuộc với hàm SUM trong Excel hoặc Google Sheets đến mức gần như dùng nó hàng nghìn lần? SUM là một công cụ tuyệt vời cho các bài tập ở trường hay những bảng tính nhỏ. Tuy nhiên, trong thế giới thực của dữ liệu và báo cáo chuyên nghiệp, SUM đôi khi lại trở thành một “con dao cùn”, gây ra những sai sót mà bạn khó lòng nhận ra. Nếu bạn muốn có được những con số tổng chính xác và đáng tin cậy tuyệt đối, có một phương pháp tốt hơn nhiều – một giải pháp mà rất nhiều người dùng Excel chuyên nghiệp ước mình đã tìm thấy sớm hơn để tránh vô vàn rắc rối.
Hàm SUM: “Kẻ Phản Bội” Thầm Lặng Khi Lọc Dữ Liệu
Vấn đề cốt lõi của hàm SUM là nó cộng tổng mọi thứ, dù dữ liệu đó có hiển thị hay bị ẩn. Đó là bản chất công việc của nó. Nhưng điều gì sẽ xảy ra khi bạn lọc dữ liệu hoặc ẩn đi một số hàng trong bảng tính của mình? Hàm SUM vẫn vui vẻ bao gồm tất cả những con số bị ẩn đó, dẫn đến việc làm tăng sai lệch tổng số và ngấm ngầm phá hoại các báo cáo của bạn. Nếu đã từng phải giải thích lý do tại sao “tổng cộng” của bạn lại lớn hơn nhiều so với dữ liệu hiển thị sau khi lọc, chắc hẳn bạn hiểu rõ vấn đề này.
Đây là điểm mà hầu hết mọi người gặp phải rào cản. Bạn thường phải dựa vào SUM, COUNT, và các hàm cơ bản khác, liên tục điều chỉnh công thức của mình mỗi khi có gì đó không khớp. Sau đó, bạn sẽ khám phá ra một hàm có thể xử lý mọi thứ mà các hàm cơ bản đó làm được, nhưng không gây ra những “cơn đau đầu” thường thấy. Đó chính là SUBTOTAL.
SUBTOTAL Hoạt Động Như Thế Nào Để Khắc Phục Vấn Đề?
Hãy tưởng tượng bạn có một bảng tính chứa hàng trăm, thậm chí hàng nghìn hàng dữ liệu bán hàng. Có thể bạn chỉ muốn xem doanh số của “Sản phẩm A”, vì vậy bạn lọc cột đó. Các con số không liên quan sẽ biến mất khỏi tầm nhìn, nhưng hàm SUM lại không “hiểu” điều đó. Nó vẫn cộng tổng mọi hàng trong nền, bao gồm cả những hàng bạn không thấy. Điều này cũng đúng với các hàm COUNT và AVERAGE truyền thống.
Ví dụ về hàm SUM vẫn tính tổng các hàng bị ẩn hoặc lọc trong bảng tính Excel
Mặt khác, hàm SUBTOTAL lại tự điều chỉnh theo thời gian thực. Với SUBTOTAL, khi bạn lọc dữ liệu, tổng số của bạn sẽ tự động cập nhật để chỉ hiển thị các hàng hiển thị, đã được lọc.
=SUBTOTAL(function_num, range)Điều này không chỉ áp dụng cho việc tính tổng. SUBTOTAL có thể linh hoạt chuyển đổi giữa tính tổng (SUM), trung bình (AVERAGE), đếm (COUNT), giá trị lớn nhất (MAX), giá trị nhỏ nhất (MIN) và nhiều phép tính hữu ích khác, chỉ bằng cách thay đổi số đầu tiên trong công thức (function_num). Dưới đây là bảng đầy đủ các hàm được hỗ trợ:
| Function Number | Function |
|---|---|
| 101 | AVERAGE |
| 102 | COUNT |
| 103 | COUNTA |
| 104 | MAX |
| 105 | MIN |
| 106 | PRODUCT |
| 107 | STDEV |
| 108 | STDEVP |
| 109 | SUM |
| 110 | VAR |
| 111 | VARP |
Bạn có thể yêu cầu SUBTOTAL bao gồm cả các hàng bị ẩn bằng cách bỏ đi chữ số đầu tiên (số “1”) từ function_num. Ví dụ: 1 sẽ tính tổng cả các ô ẩn, nhưng 101 sẽ bỏ qua chúng.
Không giống như SUM, SUBTOTAL đủ thông minh để không tính tổng chính nó hai lần. Nếu bạn có các tổng phụ (subtotals) cho mỗi danh mục và sau đó là một tổng lớn ở cuối, SUBTOTAL sẽ bỏ qua các tổng phụ khác. SUM chỉ đơn thuần cộng tất cả mọi thứ lại với nhau và làm phồng con số của bạn. Nếu bạn từng thấy một tổng số quá cao và tự hỏi tại sao, hãy kiểm tra xem có các hàm SUM lồng nhau hay không. SUBTOTAL không gặp vấn đề đó.
Hướng Dẫn Sử Dụng Hàm SUBTOTAL Trong Excel Và Google Sheets
Điều làm cho SUBTOTAL trở thành một lựa chọn hiển nhiên là tính linh hoạt của nó – bạn có nhiều tùy chọn hơn mà không cần thêm bất kỳ sự phức tạp nào so với hàm SUM. Hãy lấy ví dụ tương tự và xem cách sử dụng SUBTOTAL có thể làm mọi thứ dễ dàng hơn.
Bảng dữ liệu mẫu với cột tổng sử dụng hàm SUM trước khi áp dụng SUBTOTAL
Để tính tổng các ô, công thức sẽ như sau:
=SUBTOTAL(109, C2:C15)Công thức này tính tổng các ô từ C2 đến C15, đồng thời bỏ qua các ô bị ẩn. Tôi sẽ áp dụng công thức này cho hai hàng khác, và bây giờ tôi đã có các tổng số chính xác. Chúng hoạt động tốt cho toàn bộ bảng và cũng hoạt động hoàn hảo khi tôi lọc bảng. Lúc này, các con số đã trở nên có ý nghĩa.
Hàm SUBTOTAL tự động cập nhật tổng chỉ với các hàng hiển thị sau khi dữ liệu được lọc
Tuy nhiên, các con số khác vẫn có thể không hợp lý. Hàm COUNT vẫn hiển thị 14, và giá trị trung bình của tôi hiện đang chia tổng phụ cho số đếm đó, đó là lý do tại sao giá trị trung bình thấp hơn so với thực tế. Đừng lo lắng. SUBTOTAL cũng hỗ trợ các hàm COUNT và COUNTA.
Vì vậy, đối với ô đếm của tôi (B16), thay vì viết:
=COUNTA(A2:A15)Tôi sẽ thay thế bằng:
=SUBTOTAL(103, A2:A15)
Ví dụ áp dụng hàm SUBTOTAL với tham số 103 để đếm số ô hiển thị trong bảng Excel
103 là số hàm cho COUNTA. Giờ đây, các số đếm của tôi tự động điều chỉnh khi tôi lọc dữ liệu, và giá trị trung bình của tôi luôn hiển thị chính xác. SUBTOTAL cũng hỗ trợ MAX và MIN, đây thực sự là những cứu cánh.
Tổng cộng, SUBTOTAL bao gồm 11 hàm khác nhau – vì vậy, mặc dù nó sẽ không thay thế mọi công thức Excel, nhưng 11 hàm này thực sự là tất cả những gì bạn cần cho hầu hết các bảng tổng hợp. SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn học được ở đây đều áp dụng được bất kể bạn sử dụng nền tảng nào.
Khi Nào Nên Sử Dụng Hàm SUBTOTAL Thay Vì SUM?
Thực tế, chỉ có hai lý do để bạn tiếp tục sử dụng SUM. Một là bạn thực sự không bao giờ lọc, không bao giờ ẩn hàng, không bao giờ chuyển file của mình cho người khác, và không bao giờ cần kiểm tra các con số. Hoặc là bạn thích phải giải thích lý do tại sao tổng số của mình không khớp với những gì hiển thị trên màn hình.
Đối với tất cả những người còn lại, thực sự không có lý do gì để không chuyển sang SUBTOTAL. Hãy chuyển đổi sang SUBTOTAL và các bảng tính của bạn sẽ trở nên động, đáng tin cậy và về cơ bản là không lỗi cho việc báo cáo hàng ngày.
