Bạn có thể đã sử dụng hàm SUM hàng nghìn lần, đó là một công cụ quen thuộc với mọi người. Hàm SUM hoàn toàn ổn cho các bài tập học đường hay những bảng tính nhỏ, nhưng trong thế giới thực của dữ liệu và báo cáo, nó lại là một công cụ khá “thô sơ” và tiềm ẩn nhiều rủi ro. Nếu bạn muốn có những con số tổng đáng tin cậy, có một phương pháp tốt hơn rất nhiều – một phương pháp mà tôi ước gì mình đã tìm thấy sớm hơn nhiều năm trước, để tránh vô số lần đau đầu vì các con số không khớp.
Tại sao hàm SUM gây rắc rối?
Hàm SUM có nhiệm vụ cộng tất cả mọi thứ, dù là hiển thị hay bị ẩn. Đó chính là chức năng cơ bản của nó. Nhưng sẽ ra sao khi bạn lọc dữ liệu hoặc ẩn đi 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, 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 bế tắc – 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 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 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 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 doanh số bán hàng với 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 tương ứng. Các con số khác biến mất khỏi tầm nhìn, nhưng hàm SUM lại không nhận được “thông báo” này. Nó vẫn cộng tất cả các hàng ở phía sau, 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.
Biểu đồ minh họa cách hàm SUM tính toán tổng trên bảng Excel đã được lọc, cho thấy kết quả bị sai lệch do bao gồm cả dữ liệu ẩn
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 được nhìn thấy và đã được lọc.
=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 các phép tính như tổng (sum), trung bình (average), đếm (count), giá trị lớn nhất (min), giá trị nhỏ 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 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 của function_num. Ví dụ, 1 sẽ tính tổng 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. Hàm SUM chỉ đơn giản là 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ình to” không cần thiết. 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 đề đó.
Hướng dẫn 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à tính 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 hàm SUM. Hãy lấy cùng 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.
Hình ảnh minh họa việc sử dụng hàm SUM trong một bảng dữ liệu Excel chưa được lọc, hiển thị tổng số thông thườ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à bây giờ tôi đã có các tổng số chính xác. Chúng hoạt động tốt cho toàn bộ bảng và cũng hoạt động hoàn hảo khi tôi lọc bảng. Lúc này, các con số đã hợp lý.
Bảng Excel sau khi được lọc dữ liệu và áp dụng hàm SUBTOTAL, cho thấy tổng số đã được cập nhật chính xác chỉ với các hàng hiển thị
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ị là 14, và các giá trị trung bình của tôi đang chia tổng phụ đó cho số lượng này, đó 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)
Minh họa cách sử dụng hàm SUBTOTAL với tham số COUNTA (103) để đếm số ô không trống trong một bảng dữ liệu Excel, đảm bảo kết quả chính xác 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 giá trị. SUBTOTAL cũng hỗ trợ các hàm 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 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.
Kết luận
Thực tế, chỉ có hai lý do để bạn vẫn kiên trì sử dụng hàm SUM: hoặc bạn thực sự không bao giờ lọc dữ liệu, không bao giờ ẩn hàng, không bao giờ chuyển giao tệp 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ố của mình; hoặc 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 chuyển đổi. Hãy bắt đầu sử dụng hàm SUBTOTAL ngay hôm nay để các bảng tính của bạn trở nên linh hoạt, đáng tin cậy và hầu như không mắc lỗi cho các báo cáo hàng ngày. Nếu bạn có bất kỳ câu hỏi nào về việc áp dụng SUBTOTAL vào công việc của mình, đừng ngần ngại để lại bình luận phía dưới!
