Bạn có lẽ đã dùng hàm SUM hàng nghìn lần, ai cũng vậy. SUM hoàn toàn ổn cho các bài tập ở trường hay những bảng tính nhỏ, nhưng trong thế giới thực, nó lại là một công cụ khá thô sơ. Nếu bạn muốn có những tổng số liệu thực sự đáng tin cậy, có một cách tốt hơn nhiều—một cách mà tôi ước mình đã tìm thấy từ nhiều năm trước (và đã tránh được bao nhiêu cơn đau đầu).
Vấn Đề Của Hàm SUM Trong Excel Là Gì?
Hàm SUM cộng tất cả mọi thứ, dù hiển thị hay không. Đó là nhiệm vụ 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? 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. Nếu bạn từng phải giải thích 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 bế tắc—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 thông thường, 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ấy. Đó là khi tôi tìm thấy SUBTOTAL.
Hàm SUBTOTAL Hoạt Động Như Thế Nào?
Hãy hình dung 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ố 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 đang cộng tất cả các hàng ở phía sau, 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.
Ví dụ về hàm SUM bị sai lệch khi áp dụng trên bảng dữ liệu Excel đã lọc, cho thấy tổng không khớp với các hàng hiển thị.
Mặt khác, 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 đã lọc, đang hiển thị.
=SUBTOTAL(function_num, range)Điều này không chỉ áp dụng cho tổng số. SUBTOTAL có thể chuyển đổi giữa tổng, trung bình, đếm, giá trị nhỏ nhất, giá trị lớn 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 | 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 ô 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 lặp chính nó. Nếu bạn có các subtotals 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 subtotals khác. SUM chỉ đơn thuần cộng tất cả mọi thứ lại với nhau và làm cho các con số của bạn bị “phồng” lê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. 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 sự phức tạp 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.
Minh họa cách hàm SUM được sử dụng trong một bảng dữ liệu Excel trước khi áp dụng các bộ lọc hoặc ẩn hàng.
Để lấy tổng của 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ố đã hợp lý.
Kết quả chính xác khi sử dụng hàm SUBTOTAL để tính tổng trên bảng Excel đã lọc dữ liệu, tự động điều chỉnh theo các hàng hiển thị.
Tuy nhiên, các con số khác vẫn không hợp lý. COUNT vẫn hiển thị 14, và các giá trị trung bình của tôi bây giờ đ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 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ế bằng:
=SUBTOTAL(103, A2:A15)
Ứng dụng hàm SUBTOTAL với mã 103 để đếm số lượng ô không trống trong một bảng Excel, đảm bảo đếm chính xác trên dữ liệu đã lọc.
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ị đú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 hàm 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 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.
SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều có thể á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 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 bất kỳ ai và không bao giờ cần kiểm tra số liệu của mình; hoặc bạn thích 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 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 linh hoạt, đáng tin cậy và về cơ bản là không có lỗi cho các báo cáo hàng ngày. Đừng quên theo dõi thuthuatdidong.net để cập nhật thêm các thủ thuật Excel và công nghệ hữu ích khác!
