SUM chỉ dành cho người mới: Các chuyên gia Excel sử dụng hàm này thay thế!

Có lẽ bạn đã dùng hàm SUM hàng ngàn lần rồi. Ai cũng vậy. SUM rất phù hợp với bài tập ở trường và các bảng nhỏ, nhưng trong thực tế, nó lại là một công cụ khá thô sơ. Nếu bạn muốn có tổng số thực sự đáng tin cậy, có một cách tốt hơn.

SUM có vấn đề gì?

SUM cộng tất cả mọi thứ, dù hiển thị hay không. Đó là chức năng của nó. Nhưng còn khi bạn lọc dữ liệu hoặc ẩn một số hàng thì sao? SUM lại vô tình bao gồm tất cả những con số ẩn đó. Kết quả là, nó làm tăng tổng số và âm thầm phá hỏng báo cáo của bạn. Nếu đã từng thấy mình phải giải thích tại sao "tổng số" của mình lớn hơn dữ liệu đã lọc, thì bạn sẽ hiểu chính xác những gì bài viết đang nói.

Đây là lúc hầu hết mọi người gặp phải khó khăn. Nhiều người đã dựa vào SUM, COUNT và tất cả những công cụ thường dùng, liên tục điều chỉnh công thức mỗi khi có gì đó không hợp lý. Sau đó, họ phát hiện ra một hàm xử lý mọi thứ mà những hàm cơ bản đó không làm được, nhưng lại không gây ra những khó khăn thường gặp. Đó là hàm SUBTOTAL.

Cách thức hoạt động thực sự của hàm SUBTOTAL

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 hàng. Có thể bạn chỉ muốn xem doanh số của "Product A", nên cần lọc cột. Các con số biến mất khỏi chế độ xem, nhưng hàm SUM lại không nhận được ghi chú. Nó vẫn cộng tất cả các hàng ở chế độ nền, bao gồm cả những hàng bạn không thể nhìn thấy. Điều này cũng áp dụng cho các hàm COUNT và AVERAGE.

Mặt khác, hàm SUBTOTAL tự động điều chỉnh. Với SUBTOTAL, khi bạn lọc dữ liệu, tổng sẽ tự động cập nhật để chỉ hiển thị các hàng được lọc và hiển thị.

=SUBTOTAL(function_num, range)

Điều này không chỉ liên quan đến tổng. SUBTOTAL có thể hoán đổi giữa tổng, trung bình, số lượng, min, max 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ợ:

Mẹo: Bạn có thể yêu cầu hàm SUBTOTAL bao gồm các hàng ẩn bằng cách bỏ chữ số đầu tiên khỏi function_num. Ví dụ, 1 sẽ tính TỔNG các ô ẩn, nhưng 101 sẽ bỏ qua chúng.

Không giống như SUM, hàm SUBTOTAL đủ thông minh để không tự tính 2 lần. Nếu bạn có tổng phụ cho từng danh mục và sau đó là tổng cộng ở cuối, hàm SUBTOTAL sẽ biết cách bỏ qua các tổng phụ khác. Hàm SUM chỉ gộp tất cả lại với nhau và làm tăng số liệu của bạn. Nếu bạn đã từng thấy tổng 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 đề đó.

Cách sử dụng Subtotal trong Excel (và Google Sheets)

Điều khiến 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 gặp bất kỳ sự phức tạp nào so với SUM. Hãy cùng xem xét ví dụ tương tự và xem việc sử dụng SUBTOTAL có thể giúp mọi việc dễ dàng hơn như thế nào.

Để 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 những ô bị ẩn. Ví dụ sẽ áp dụng công thức này cho hai hàng còn lại và giờ đã có con số tổng. Chúng hoạt động tốt trên toàn bộ bảng và cũng hoạt động tốt khi lọc bảng. Giờ thì các con số đã hợp lý.

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 hiện đang chia subtotal cho giá trị đếm đó, đó là lý do tại sao những 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 (B16), thay vì viết:

=COUNTA(A2:A15)

Hãy đổi thành:

=SUBTOTAL(103, A2:A15)

103 là số hiệu hàm cho COUNTA. Giờ đây, số đếm tự động điều chỉnh khi 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, một tính năng 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ó 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.

Lưu ý: SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều áp dụng cho bất kỳ nền tảng nào bạn sử dụng.

Hãy xem, chỉ có hai lý do để sử dụng hàm SUM. Hoặc là bạn thực sự không bao giờ lọc, không bao giờ ẩn hàng, không bao giờ giao file cho bất kỳ ai và không bao giờ cần kiểm tra số liệu, hoặc bạn thích giải thích tại sao tổng không bao giờ khớp với số liệu trên màn hình.

Đối với những người khác, thực sự không có lý do gì để bào chữa. Hãy chuyển sang hàm SUBTOTAL và bảng tính của bạn sẽ trở nên năng động, đá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.