Nếu bạn đã từng sử dụng hàm SUM hàng nghìn lần, bạn không đơn độc. Hàm SUM rất hiệu quả cho các bài tập ở trường hay những bảng tính nhỏ, nhưng trong thực tế công việc, nó thường trở nên cứng nhắc và dễ gây nhầm lẫn. Để có được các tổng số liệu đáng tin cậy, đặc biệt là khi làm việc với dữ liệu lớn, có một phương pháp tốt hơn nhiều – một công cụ mà tôi ước mình đã khám phá ra sớm hơn để tránh khỏi vô vàn rắc rối.
SUM Có Vấn Đề Gì Khi Xử Lý Dữ Liệu?
Hàm SUM có nhiệm vụ cộng tất cả các giá trị, dù hiển thị hay ẩn. Đó chính là bản chất 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ẻ tính toán tất cả những con số bị ẩn đó. Kết quả là, tổng của bạn bị phóng đại 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ại lớn hơn nhiều so với dữ liệu đang được lọc, thì bạn chắc chắn hiểu rõ vấn đề này.
Đây chính là điểm mà hầu hết mọi người gặp khó khăn – tôi 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 đã phát hiện 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 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ố khác biến mất khỏi tầm nhìn, nhưng SUM không nhận ra điều đó. Nó vẫn cộng tất cả các hàng trong nền, kể 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.
Ví dụ hàm SUM tính tổng dữ liệu đã lọc trên bảng Excel, cho thấy kết quả sai lệch
Mặt khác, hàm SUBTOTAL tự động đ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 đang hiển thị, đã đượ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 (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. Dưới đây là bảng đầy đủ các chức năng được hỗ trợ:
| Số Hàm | Hàm Chức Năng |
|---|---|
| 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 khỏi function_num. Ví dụ: 1 sẽ SUM 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 toán trùng 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 biết cách bỏ qua các tổng phụ khác. SUM chỉ đơn giản là cộng tất cả mọi thứ lại và làm phình to 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 vấn đề đó.
Hướng Dẫn Sử Dụng Hàm SUBTOTAL Trong Excel và Google Sheets
Điều khiến SUBTOTAL trở thành một lựa chọn rõ ràng 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 SUM. Hãy lấy cùng một ví dụ và xem cách sử dụng SUBTOTAL có thể giúp mọi thứ dễ dàng hơn.
Minh họa cách sử dụng hàm SUM trong một bảng dữ liệu Excel cơ bản để tính tổng
Để 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à 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 tốt khi tôi lọc bảng. Bây giờ các con số đã có ý nghĩa.
Sử dụng hàm SUBTOTAL tính tổng trên bảng Excel đã lọc, hiển thị kết quả chính xác theo dữ liệu hiển thị
Tuy nhiên, các con số khác vẫn chưa có ý nghĩa. 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)
Ví dụ ứng dụng hàm SUBTOTAL để đếm số ô trong bảng dữ liệu Excel, cho phép điều chỉnh khi lọc
103 là số hàm cho COUNTA. Giờ đây, số lượng 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 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. 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.
Thực tế, chỉ có hai lý do để tiếp tục sử dụng 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 bất kỳ ai, và không bao giờ cần kiểm tra lại các con số. Hai là bạn thích việc phải giải thích lý do 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 tất cả 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à các bảng tính của bạn sẽ trở nên động, đáng tin cậy và về cơ bản là không lỗi cho các báo cáo hàng ngày.
