Bạn có thể đã sử dụng hàm SUM hàng ngàn lần. Ai cũng vậy. Hàm SUM rất tốt cho các bài tập ở trường hoặc các bảng 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 đáng tin cậy, có một cách tốt hơn nhiều mà có lẽ bạn sẽ ước mình đã biết đến từ lâu.
Hàm SUM: “Kẻ Phản Bội” Thầm Lặng Trong Báo Cáo 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ố hà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 tăng tổng số của bạn và âm thầm phá hoại 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ớn hơn dữ liệu đã lọc, bạn sẽ hiểu chính xác điều này.
Đây là lúc hầu hết mọi người gặp khó khăn – tôi cũng vậy. Tôi đã dựa 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 mỗi khi có gì đó không khớp. Sau đó, tôi 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 rắc rối thông thường. Đó là lúc tôi tìm thấy hàm SUBTOTAL.
SUBTOTAL Hoạt Động Như Thế Nào Để Khắc Phục Hạn Chế Của SUM?
Hãy hình dung bạn có một bảng đầy dữ liệu bán hàng, hàng trăm hoặc hàng ngà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ố liên quan đến sản phẩm khác biến mất khỏi tầm nhìn, nhưng hàm 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 thể nhìn thấy. Điều này cũng đúng với các hàm COUNT và AVERAGE.
Minh họa hàm SUM hiển thị tổng sai lệch trên bảng Excel đã lọc dữ dữ liệu
Mặt khác, hàm SUBTOTAL tự điều chỉnh ngay lập tứ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 đang hiển thị, đã 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 tổng, trung bình, đếm, giá trị lớn nhất, giá trị nhỏ nhất 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ác hàng bị ẩn bằng cách bỏ chữ số đầu tiên khỏi 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 lặp chính nó. Nếu bạn có các tổng phụ cho từng 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ỉ cộng mọi thứ lại với nhau và làm tă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 xem có các hàm SUM lồng ghép hay không. 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 SUBTOTAL trở thành một lựa chọn hiển nhiên là sự 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 sự phức tạp nào so với 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ụ về cách sử dụng hàm SUM trong một bảng dữ liệu Excel chưa lọc
Để có được tổng của các ô, công thức sẽ là 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 điều 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ả tính toán chính xác của hàm SUBTOTAL trên bảng dữ liệu Excel đã lọc
Tuy nhiên, các 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 phải là. Đừ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)
Sử dụng hàm SUBTOTAL với chức năng COUNTA (103) để đếm số lượng mục 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à các giá trị trung bình của tôi luôn hiển thị giá trị đúng. 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. SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều áp dụng bất kể bạn sử dụng nền tảng nào.
Kết Luận: Nâng Tầm Báo Cáo Dữ Liệu Với SUBTOTAL
Có lẽ chỉ có hai lý do để bạn tiếp tục sử dụng hàm SUM: hoặc bạn thực sự không bao giờ lọc, không bao giờ ẩn hàng, không bao giờ chuyển các tệp của mình cho bất kỳ ai và không bao giờ cần kiểm tra số liệu, hoặc bạn thích việc phải giải thích lý do tại sao các 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 những người còn lại, thực sự không có lý do gì để không chuyển đổi. Hãy sử dụng hàm SUBTOTAL và các bảng tính của bạn sẽ trở nên động, đá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. Hãy bắt đầu áp dụng SUBTOTAL ngay hôm nay để tối ưu hóa công việc của bạn!
