Bạn đã sử dụng hàm SUM hàng ngàn lần, và điều đó hoàn toàn bình thường. Hàm SUM rất tốt cho các bài tập ở trường hay những bảng dữ liệu nhỏ gọn. Thế nhưng, trong thế giới thực của dữ liệu và báo cáo, SUM lại giống như một công cụ thô sơ, thường xuyên dẫn đến những sai sót không đáng có.
Nếu bạn muốn có những con số tổng đáng tin cậy, đặc biệt khi làm việc với các bảng tính phức tạp, thì có một cách tốt hơn nhiều. Đây là giải pháp mà chúng tôi ước mình đã tìm thấy sớm hơn, để tránh hàng giờ đau đầu với những báo cáo không khớp số liệu.
Tại Sao Hàm SUM Không Đáng Tin Cậy Trong Báo Cáo Dữ Liệu?
Về bản chất, hàm SUM được thiết kế để cộng tất cả các giá trị trong một phạm vi được chỉ định, dù chúng có hiển thị hay bị ẩn đi. Đó chính là chức năng cốt lõi của nó. Tuy nhiên, điều này lại trở thành một vấn đề lớn khi bạn cần lọc dữ liệu trong Excel hoặc ẩn đi một số hàng. Hàm SUM vẫn vui vẻ cộng tất cả những con số bị ẩn đó vào tổng cuối cùng của bạn.
Kết quả là, tổng số bạn nhận được bị “phóng đại” so với dữ liệu thực tế đang hiển thị, âm thầm phá hoại độ chính xác của các báo cáo. Nếu bạn từng phải vất vả giải thích tại sao “tổng” của mình lại lớn hơn nhiều so với dữ liệu đã lọc, bạn sẽ 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 – chúng tôi cũng từng như vậy. Chúng tôi phụ thuộc vào SUM, COUNT, và các hàm cơ bản khác, liên tục điều chỉnh công thức mỗi khi số liệu không khớp. Rồi một ngày, chúng tôi đã khám phá ra một hàm xử lý mọi việc mà các hàm cơ bản làm được, nhưng không hề gây ra những rắc rối thường gặp. Đó chính là hàm SUBTOTAL.
Hàm SUBTOTAL Hoạt Động Thông Minh Như Thế Nào?
Hãy tưởng tượng bạn có một bảng dữ liệu bán hàng khổng lồ, hàng trăm hoặc hàng ngàn dòng. Giả sử bạn chỉ muốn xem doanh số của “Sản phẩm A”, vì vậy bạn lọc cột dữ liệu. Các con số liên quan đến sản phẩm khác sẽ biến mất khỏi tầm nhìn, nhưng hàm SUM không “nhận được tin báo” đó. Nó vẫn cộng tấtất cả các hàng trong nền, bao gồm cả những hàng bạn không thấy. Tương tự, điều này cũng xảy ra với các hàm COUNT và AVERAGE.
Bảng dữ liệu Excel với hàm SUM hiển thị tổng không chính xác khi đã lọc
Ngược lại, hàm SUBTOTAL tự động điều chỉnh linh hoạt. 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 dữ liệu đang hiển thị hoặc đã được lọc. Công thức của hàm SUBTOTAL như sau:
=SUBTOTAL(function_num, range)Điều đặc biệt của SUBTOTAL không chỉ dừng lại ở việc tính tổng. Hàm này có thể chuyển đổi linh hoạt giữa các phép tính như tổng (SUM), trung bình (AVERAGE), đếm (COUNT), giá trị lớn nhất (MAX), giá trị nhỏ nhất (MIN) và nhiều phép tính hữu ích khác, chỉ bằng cách thay đổi tham số function_num đầu tiên trong công thức. Dưới đây là bảng đầy đủ các chức năng đượ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ể điều khiển hàm SUBTOTAL để bao gồm các hàng bị ẩn bằng cách bỏ chữ số đầu tiên (1) khỏi số hàm (function_num). Ví dụ, số 1 sẽ tính tổng cả các ô ẩn, nhưng 101 sẽ bỏ qua chúng. Điều này cực kỳ tiện lợi cho các báo cáo đa dạng.
Không giống như SUM, SUBTOTAL đủ thông minh để không tính tổng chính nó hai lần (double-count). Nếu bạn có các tổng phụ (subtotals) cho từng danh mục và sau đó là một tổng chung ở cuối, SUBTOTAL sẽ biết cách bỏ qua các tổng phụ khác. Trong khi đó, SUM chỉ đơn thuần cộng tất cả mọi thứ lại và làm phồng 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 không. SUBTOTAL không gặp vấn đề đó.
Hướng Dẫn Chi Tiết Cách Dùng SUBTOTAL Trong Excel và Google Sheets
Điều làm cho hàm SUBTOTAL trở thành lựa chọn hiển nhiên chính là sự linh hoạt vượt trội mà không tăng thêm độ phức tạp so với hàm SUM. Hãy cùng xem xét một ví dụ thực tế và cách sử dụng SUBTOTAL có thể làm mọi thứ trở nên dễ dàng hơn.
Ví dụ ban đầu về việc sử dụng hàm SUM trong bảng dữ liệu Excel
Để tính tổng các ô trong ví dụ này, công thức sẽ là:
=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. Chúng tôi sẽ áp dụng công thức này cho hai dòng khác, và giờ đây chúng ta đã có các tổng số chính xác. Các tổng này hoạt động hoàn hảo cho cả bảng đầy đủ và khi chúng ta lọc bảng. Bây giờ, các con số đã trở nên có ý nghĩa.
Bảng dữ liệu Excel sử dụng hàm SUBTOTAL tự động cập nhật khi lọc
Trong ví dụ gốc, các con số khác như COUNT vẫn chưa chính xác. Hàm COUNT vẫn hiển thị 14, và các giá trị trung bình chia tổng phụ cho số đếm đó, khiến giá trị trung bình thấp hơn thực 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 chúng ta (B16), thay vì viết:
=COUNTA(A2:A15)Chúng ta sẽ thay thế bằng:
=SUBTOTAL(103, A2:A15)
Sử dụng hàm SUBTOTAL để đếm số ô hiển thị trong bảng Excel đã lọc
103 là số hàm cho COUNTA. Giờ đây, số đếm của chúng ta tự động điều chỉnh khi chúng ta lọc dữ liệu, và các giá trị trung bình 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 lớn cho việc phân tích dữ liệu nhanh chóng.
Tổng cộng, SUBTOTAL bao gồm 11 chức năng khác nhau – mặc dù nó không thể thay thế mọi công thức Excel, nhưng 11 chức năng này 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. Đặc biệt, hàm SUBTOTAL cũng hoạt động hoàn hảo trong Google Sheets, vì vậy mọi điều bạn học đượ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 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ờ gửi 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. Hoặc hai là 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 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à 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 nâng cấp kỹ năng bảng tính của bạn ngay hôm nay!
