Bạn có lẽ đã sử dụng hàm SUM hàng nghìn lần. Ai cũng vậy. SUM là một công cụ tiện lợi cho các bài tập học đường và những bảng tính nhỏ, nhưng trong thế giới thực, nó lại là một công cụ thô sơ và kém hiệu quả. 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 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 và đau đầu không đáng có.
Tại Sao Hàm SUM Không Hiệu Quả?
Hàm SUM cộng tất cả mọi thứ, dù hiển thị hay không hiển thị. Đó là chức năng vốn có của nó. Nhưng sẽ ra sao 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 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 lý do tại sao “tổng” của mình lại lớn hơn dữ liệu đã lọc, thì bạn sẽ 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 phải khó khăn – tôi biết mình cũng từng như vậy. Tôi đã dựa vào SUM, COUNT và tất cả các hàm cơ bản thông thường, liên tục phải đ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 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 phiền toái quen thuộc. Đó chính là lúc 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 chứa đầ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ố biến mất khỏi tầm nhìn, nhưng SUM lại không nhận được thông báo. Nó vẫn đang 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.
Kết quả sai lệch khi 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 số của bạn sẽ tự động cập nhật để chỉ hiển thị các dòng hiển thị và đã đượ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 tổng, trung bình, đếm, giá trị nhỏ nhất, lớn nhất và một loạt các 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ỏ chữ số đầu tiên (1) khỏi function_num. Ví dụ, 1 sẽ SUM 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 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 biết cách 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 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 hay không. 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 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 cùng một ví dụ và xem cách sử dụng SUBTOTAL có thể làm cho mọi thứ dễ dàng hơn nữa.
Ví dụ cách sử dụng hàm SUM trong bảng dữ liệu Excel
Để lấy 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à chúng vẫn hoạt động hoàn hảo khi tôi lọc bảng. Giờ đây, các con số đã trở nên hợp lý.
Bảng dữ liệu Excel đã lọc hiển thị tổng chính xác với hàm SUBTOTAL
Tuy nhiên, các con số khác vẫn chưa hợp lý. Số lượng (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ố lượng đó, đó là lý do tại sao giá trị trung bình thấp hơn mức cần thiế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)
Sử dụng hàm SUBTOTAL để đếm số ô hiển thị trong bảng Excel
103 là số hàm của 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à giá trị trung bình của tôi luôn hiển thị đúng giá trị. 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ó không thay thế được 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óm tắt.
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.
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 lại các con số của mình. Hai là bạn thích việc phải giải thích lý do 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 thay đổi. Hãy chuyển sang SUBTOTAL và các bảng tính của bạn sẽ trở nên linh hoạt, đáng tin cậy và về cơ bản là không thể xảy ra lỗi trong các báo cáo hàng ngày.
