Bạn có lẽ đã dùng hàm SUM hàng nghìn lần. Ai cũng vậy. SUM rất ổn 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ó 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 giải pháp mà tôi ước mình đã tìm thấy từ nhiều năm trước, để tránh những rắc rối đau đầu.
Tại sao hàm SUM không đáng tin cậy?
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 sẽ thế nào nếu bạn lọc dữ liệu hoặc ẩn một số hàng? 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à âm thầm 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 lý do tại sao “tổng” của mình lại lớn hơn dữ liệu đã lọc, bạn chắc chắn hiểu tôi đang nói gì.
Đây là lúc hầu hết mọi người gặp khó khăn – tôi biết mình cũng 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 đã khám phá 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 dữ liệu bán hàng đầy đủ, 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ố khác biến mất khỏi tầm nhìn, nhưng SUM không “nhận được thông báo”. Nó vẫn cộng tất cả các hàng trong nền, bao gồm cả những hàng bạn không nhìn thấy. Điều này cũng đúng với các hàm COUNT và AVERAGE.
Sự khác biệt khi sử dụng hàm SUM trên bảng 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 số 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)Đây không chỉ là về các phép tính tổng. SUBTOTAL có thể chuyển đổi giữa 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. 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 hàng bị ẩn bằng cách bỏ đi chữ số đầu tiên (1) từ function_num. Ví dụ, 1 sẽ tính tổng cả các ô bị ẩn, nhưng 101 sẽ bỏ qua chúng.
Không giống như SUM, SUBTOTAL đủ thông minh để không tính trùng chính nó. Nếu bạn có các tổng phụ (subtotal) cho từng danh mục và sau đó là một tổng lớn ở cuối, SUBTOTAL biết cách bỏ qua các tổng phụ khác. SUM chỉ đơn giản cộng tất cả mọi thứ lại với nhau và làm phồng số liệu 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 các hàm SUM lồng nhau. SUBTOTAL không gặp phải vấn đề đó.
Cách 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à sự linh hoạt của nó – bạn nhận được nhiều tùy chọn hơn mà không phức tạp hơn so với SUM. Hãy lấy ví dụ tương tự và xem việc sử dụng SUBTOTAL có thể giúp mọi thứ dễ dàng hơn như thế nào.
Sử dụng hàm SUM để tính tổng trong bảng Excel
Để tính tổng các ô, công thức sẽ như sau:
=SUBTOTAL (109, C2:C15)Công thức này sẽ 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 dò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à cũng hoạt động tốt khi tôi lọc bảng. Lúc này các con số đã trở nên hợp lý.
Ví dụ sử dụng hàm SUBTOTAL trên một bảng Excel đã lọc
Tuy nhiên, các con số khác vẫn chưa hợp lý. Hàm COUNT vẫn hiển thị 14, và các 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 các giá trị trung bình thấp hơn mức chúng nên 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ế nó bằng:
=SUBTOTAL(103, A2:A15)
Áp dụng hàm SUBTOTAL để đếm số ô trong bảng Excel
103 là mã hàm cho COUNTA. Giờ đây, 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ị đúng. SUBTOTAL cũng hỗ trợ MAX và MIN, đây là một cứu cánh thực sự.
Tổng cộng, SUBTOTAL bao gồm 11 hàm khác nhau – vì vậy mặc dù nó không thể 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óm tắt hàng ngày. SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều áp dụng được bất kể bạn sử dụng nền tảng nào.
Thực ra, 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 cho người khác và không bao giờ cần kiểm tra số liệu của mình; hai là bạn thích việc phải giải thích tại sao tổng số của bạn 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 sử dụng SUBTOTAL. Hãy chuyển sang SUBTOTAL và 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.
