Bạn đã quen thuộc với hàm SUM – công cụ tính tổng cơ bản mà ai cũng sử dụng. SUM hoàn hảo cho các bài tập ở trường hay những bảng dữ liệu nhỏ gọn. Tuy nhiên, trong thế giới thực của dữ liệu kinh doanh và báo cáo, SUM lại giống như một công cụ thô sơ, kém hiệu quả. Nếu bạn muốn có những con số tổng chính xác và đáng tin cậy, đặc biệt khi làm việc với lượng lớn dữ liệu cần lọc, ẩn, thì đã đến lúc bạn cần một giải pháp tốt hơn. Đây là một bí quyết mà tôi ước gì mình đã khám phá ra sớm hơn, tiết kiệm được vô vàn thời gian và cả những cơn đau đầu khi xử lý các bảng tính phức tạp.
Tại Sao Hàm SUM Lại Gây Ra Vấn Đề?
SUM có nhiệm vụ cộng tất cả các giá trị, dù chúng có hiển thị hay không. Đây chính là điểm yếu cố hữu của nó. Điều gì sẽ xảy ra khi bạn lọc dữ liệu hoặc ẩn một số hàng? Hàm SUM vẫn vui vẻ bao gồm tất cả những con số bị ẩn đó vào tổng cuối cùng. Hậu quả là tổng của bạn bị phóng đại, âm thầm phá hoại sự chính xác của các báo cáo. Nếu bạn đã từng phải giải thích lý do tại sao “tổng” của mình lại lớn hơn nhiều so với dữ liệu đã lọc trên màn hình, bạn chắc chắn hiểu rõ vấn đề này.
Đây là lúc nhiều người gặp bế tắc – tôi cũng từng như vậy. Tôi phụ thuộc vào SUM, COUNT và các hàm cơ bản khác, liên tục phải điều chỉnh công thức mỗi khi có gì đó không khớp. Cho đến khi tôi phát hiện ra một hàm có thể xử lý mọi tác vụ mà các hàm cơ bản đó làm được, nhưng không gây ra những rắc rối thường gặp. Đó chính là hàm SUBTOTAL.
Hàm SUBTOTAL Hoạt Động Thực Tế Như Thế Nào?
Hãy tưởng tượng bạn có một bảng dữ liệu bán hàng khổng lồ, hàng trăm hoặc hàng ngàn dòng. Giả sử bạn chỉ muốn xem doanh số của “Sản phẩm A”, vì vậy bạn lọc cột dữ liệu. Các con số khác biến mất khỏi tầm nhìn, nhưng SUM thì không hề hay biết. Nó vẫn tính tổng mọi dòng trong nền, bao gồm cả những dòng bạn không thể nhìn thấy. Điều này cũng đúng với các hàm COUNT và AVERAGE.
Mô tả sự cố khi sử dụng hàm SUM trên bảng dữ liệu Excel đã lọc
Ngược lại, SUBTOTAL tự điều chỉnh linh hoạt. Với SUBTOTAL, khi bạn lọc dữ liệu, tổng của bạn sẽ tự động cập nhật để chỉ hiển thị các hàng nhìn thấy đượ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ể chuyển đổi giữa các phép tính như SUM, AVERAGE, COUNT, MIN, MAX 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. Dưới đây là bảng đầy đủ các chức năng đượ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 ẩn bằng cách bỏ đi chữ số đầu tiên trong function_num. Ví dụ, 1 sẽ tính SUM cả các ô ẩn, nhưng 109 sẽ bỏ qua chúng.
Khác với SUM, SUBTOTAL đủ thông minh để không tính trùng lặp chính nó. Nếu bạn có các bảng con tính tổng phụ cho từng danh mục và sau đó là một tổng chung ở cuối, SUBTOTAL sẽ biết bỏ qua các tổng phụ khác. SUM thì chỉ đơn giản cộng mọi thứ lại với nhau và làm số liệu của bạn bị “phình” ra. Nếu bạn đã từng thấy một tổng quá cao và tự hỏi tại sao, hãy kiểm tra xem có các hàm SUM lồng nhau không. SUBTOTAL không gặp phải vấn đề này.
Cách Sử Dụng SUBTOTAL Trong Excel (Và Google Sheets)
Điều làm cho SUBTOTAL trở thành một lựa chọn rõ ràng là tính linh hoạt của nó – bạn có đượ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 SUM. Hãy lấy ví dụ tương tự và xem cách sử dụng SUBTOTAL có thể giúp mọi thứ dễ dàng hơn như thế nào.
Minh họa cách sử dụng hàm SUM trên bảng dữ liệu Excel thông thường
Để tính tổng các ô, công thức sẽ là:
=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. Áp dụng công thức này cho hai hàng khác, và giờ tôi đã có các tổng đúng. Chúng hoạt động tốt cho toàn bộ bảng, và chúng vẫn hoạt động chính xác khi tôi lọc bảng. Bây giờ, các con số đã trở nên có ý nghĩa.
Ví dụ minh họa hàm SUBTOTAL hoạt động chính xác trên bảng dữ liệu Excel đã lọc
Tuy nhiên, các con số khác (như COUNT) vẫn chưa hợp lý. COUNT vẫn hiển thị 14, và các giá trị trung bình của tôi đang chia tổng phụ cho số lượng đó, đó là lý do tại sao giá trị trung bình thấp hơn thực tế. Đừng lo lắng. SUBTOTAL cũng hỗ trợ các hàm COUNT và COUNTA.
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)
Hướng dẫn sử dụng hàm SUBTOTAL để đếm các ô trong bảng dữ liệu Excel
103 là số chức năng cho COUNTA. Giờ đây, số lượng của tôi sẽ tự động điều chỉnh khi tôi lọc dữ liệu và các giá trị trung bình luôn hiển thị đúng. SUBTOTAL cũng hỗ trợ MAX và MIN, đây thực sự là một cứu tinh.
Tổng cộng, SUBTOTAL bao gồm 11 chức năng 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 chức năng 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 tốt trong Google Sheets. Mọi điều bạn học được ở đây đều có thể áp dụng, bất kể bạn sử dụng nền tảng nào.
Kết Luận: Đã Đến Lúc Nâng Cấp Kỹ Năng Bảng Tính Của Bạn
Có lẽ chỉ có hai lý do để bạn tiếp tục gắn bó với hàm 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 lại các con số của mình. Hai là bạn thích việc phải giải thích tại sao tổng của mình không bao giờ khớp với những gì hiển thị trên màn hình.
Đối với những người còn lại, thực sự không có lý do gì để không chuyển đổi. Hãy nâng cấp lên SUBTOTAL và các bảng tính của bạn sẽ trở nên linh hoạt, đáng tin cậy và gần như không thể mắc lỗi cho các báo cáo hàng ngày. Đừng để những sai số nhỏ làm hỏng các phân tích quan trọng. Hãy thử áp dụng hàm SUBTOTAL ngay hôm nay để trải nghiệm sự khác biệt và tối ưu hóa quy trình làm việc của bạn. Chia sẻ trải nghiệm của bạn với chúng tôi trong phần bình luận nhé!
