Chắc hẳn bạn đã sử dụng hàm SUM hàng nghìn lần. Ai cũng vậy. Hàm SUM rất tốt cho các bài tập ở trường hay những bảng dữ liệu nhỏ, nhưng trong thế giới thực, nó giống như một công cụ thô sơ. Nếu bạn muốn có được những con số tổng hợp thực sự đáng tin cậy, có một cách tốt hơn – một cách mà tôi ước mình đã tìm thấy từ nhiều năm trước, để tránh biết bao nhiêu lần đau đầu.
Hàm SUM Có Vấn Đề Gì Khi Xử Lý Dữ Liệu?
Hàm SUM cộng tất cả mọi thứ, dù hiển thị hay không. Đó là 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 một số dòng? Hàm SUM vẫn vui vẻ bao gồm tất cả những con số bị ẩn đó. Kết quả là, nó làm phồng tổng số của bạn và lặng lẽ phá hoại các báo cáo của bạn. Nếu bạn đã từng phải giải thích tại sao “tổng số” của mình lại lớn hơn dữ liệu đã lọc, thì bạn hiểu chính xác điều tôi đang nói đến.
Đây là lúc hầu hết mọi người gặp bế tắc – tôi biết mình đã từng như vậy. Tôi phụ thuộc vào SUM, COUNT và tất cả 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 đó, tôi phát hiện ra một hàm 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 rắc rối thường gặp. Đó là khi tôi tìm thấy SUBTOTAL.
Hàm SUBTOTAL Hoạt Động Như Thế Nào?
Hãy tưởng tượng bạn có một bảng tính đầy dữ liệu bán hàng, hàng trăm hoặc hàng nghìn dò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ố ẩn đi, nhưng hàm SUM không nhận được thông báo. Nó vẫn cộng tất cả các dòng ở chế độ 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.
Sử dụng hàm SUM trên bảng Excel đã lọc dữ liệu
Mặt khác, hàm SUBTOTAL tự động điều chỉnh theo dữ liệu hiển thị. 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 dòng đã lọc và đang hiển thị.
=SUBTOTAL(function_num, range)Điều này không chỉ áp dụng cho việc tính tổng. Hàm SUBTOTAL có thể chuyển đổi giữa tổng (SUM), trung bình (AVERAGE), đếm (COUNT), giá trị nhỏ nhất (MIN), giá trị lớn nhất (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 (function_num). Dưới đây là bảng đầy đủ các chức năng được hỗ trợ:
| Mã Chức Năng (Function Number) | Chức Năng (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 hàm SUBTOTAL bao gồm cả các dòng bị ẩn bằng cách bỏ chữ số đầu tiên (1) khỏi function_num. Ví dụ, mã 1 sẽ SUM cả các ô bị ẩn, nhưng mã 101 sẽ bỏ qua chúng.
Không giống như SUM, SUBTOTAL đủ thông minh để không tính toán lặp lại chính nó. Nếu bạn có các tổng phụ (subtotals) cho từng danh mục và sau đó là một tổng lớn ở phía dưới, SUBTOTAL sẽ tự động bỏ qua các tổng phụ khác. Hàm SUM chỉ đơn giản là cộng tất cả mọi thứ lại với nhau và làm tăng vọt các 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 không. Hàm SUBTOTAL không gặp vấn đề đó.
Hướng Dẫn Chi Tiết Cách Sử Dụng Hàm SUBTOTAL Trong Excel và Google Sheets
Điều làm cho hàm 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 nhậ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 cùng một ví dụ và xem cách sử dụng SUBTOTAL có thể làm mọi thứ trở nên dễ dàng hơn như thế nào.
Ví dụ sử dụng hàm SUM truyền thống trong một bảng dữ liệu Excel
Để 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úng hoạt động tốt cho toàn bộ bảng, và chúng cũng hoạt động tốt khi tôi lọc bảng. Bây giờ các con số đã trở nên hợp lý.
Kết quả khi áp dụng hàm SUBTOTAL trên bảng Excel sau khi lọc dữ liệu
Tuy nhiên, các con số khác vẫn không hợp lý. Hàm COUNT vẫn hiển thị là 14, và các giá trị trung bình của tôi hiện đang chia tổng phụ cho số lượng đó, đó là lý do tại sao các giá trị trung bình thấp hơn đáng lẽ phải có. Đừ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)
Hướng dẫn sử dụng hàm SUBTOTAL với mã 103 để đếm số ô dữ liệu có chứa nội dung trong bảng Excel
103 là mã chức năng 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à các giá trị trung bình của tôi luôn hiển thị giá trị chính xác. Hàm SUBTOTAL cũng hỗ trợ MAX và MIN, đây thực sự là một cứu cánh.
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 hàng ngày.
Hàm SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều áp dụng cho dù bạn sử dụng nền tảng nào.
Chuyển Đổi Sang SUBTOTAL: Quyết Định Nhanh Chóng Để Có Báo Cáo Đáng Tin Cậy
Thực tế, chỉ có hai lý do để bạn vẫn gắn bó với hàm SUM. Hoặc là bạn thực sự không bao giờ lọc dữ liệu, không bao giờ ẩn dòng, không bao giờ chuyển file 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, hoặc bạn thích việc phải giải thích tại sao tổng số 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 tất cả những người còn lại, thực sự không có lý do gì để không thay đổi. Chuyển sang sử dụng hàm 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 có lỗi cho việc lập báo cáo hàng ngày. Hãy trải nghiệm sự tiện lợi và chính xác mà SUBTOTAL mang lại cho công việc của bạn!
