Chắc hẳn bạn đã dùng hàm SUM hàng ngàn lần. Ai cũng vậy. SUM rất ổn cho các bài tập ở trường hay những bảng dữ liệu nhỏ, nhưng trong thế giới thực, nó giống như một công cụ thô sơ.
Nếu bạn muốn có những con số tổng đáng tin cậy, có một cách tốt hơn – một cách mà tôi ước mình đã khám phá ra từ nhiều năm (và nhiều cơn đau đầu) trước đây.
Vấn đề thực sự của hàm SUM là gì?
Hàm SUM cộng tất cả mọi thứ, dù hiển thị hay không. Đó là công việc của nó. Nhưng sẽ thế nào nếu bạn lọc dữ liệu của mình hoặc ẩn một số hàng? SUM vẫn vui vẻ tính toán tất cả những con số ẩn đó. Kết quả là, nó làm tăng sai lệch tổng số và ngấm ngầm 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 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 những gì 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 đã phụ thuộc vào SUM, COUNT và tất cả các hàm quen thuộ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 xử lý mọi thứ 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 thường. Đó là khi tôi tìm thấy SUBTOTAL.
Hàm SUBTOTAL hoạt động chính xác như thế nào?
Hãy tưởng tượng bạn có một bảng tính chứa hàng trăm, hàng ngàn dòng dữ liệu bán hà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 hàm SUM lại không nhận được thông báo. Nó vẫn đang 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 đúng với các hàm COUNT và AVERAGE.
Sử dụng hàm SUM trên bảng Excel đã lọc dữ liệu, cho thấy tổng số bị sai lệch
SUBTOTAL, mặt khác, 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à một loạt các phép tính hữu ích khác, chỉ bằng cách thay đổi con số đầu tiên trong công thức. Dưới đây là bảng đầy đủ các hàm được hỗ trợ:
Bảng tham chiếu các chức năng của SUBTOTAL
| 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 ẩn bằng cách bỏ đi chữ số đầu tiên từ 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, SUBTOTAL đủ thông minh để không tính toán chính nó hai lầ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 thuần cộng tất cả mọi thứ lại với nhau 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 các hàm SUM lồng nhau. 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 hiển nhiên là sự linh hoạt của nó – bạn có nhiều tùy chọn hơn mà không phức tạp hơn so với SUM. Hãy lấy ví dụ tương tự và xem cách sử dụng SUBTOTAL có thể giúp mọi thứ dễ dàng hơn nữa.
Minh họa cách sử dụng hàm SUM trong bảng Excel thông thường
Ví dụ 1: Tính tổng với SUBTOTAL
Để lấy 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 ô ẩ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 bảng đầy đủ, và chúng vẫn hoạt động tốt khi tôi lọc bảng. Bây giờ các con số đã trở nên có ý nghĩa.
Ví dụ về việc sử dụng hàm SUBTOTAL trên bảng Excel đã lọc, hiển thị tổng chính xác
Ví dụ 2: Đếm số lượng với SUBTOTAL
Tuy nhiên, các con số khác vẫn chưa có ý nghĩa. 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ố lượng đó, đó là lý do tại sao các giá trị trung bình thấp hơn mức chúng phải là. Đừ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)
Hướng dẫn sử dụng hàm SUBTOTAL với mã 103 để đếm số lượng ô không rỗng trong bảng Excel đã lọc
103 là mã 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 tinh.
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ổng hợp.
SUBTOTAL cũng hoạt động trong Google Sheets. Mọi thứ bạn đã học ở đây đều áp dụng, bất kể bạn sử dụng nền tảng nào.
Kết luận
Có lẽ chỉ có hai lý do để bạn tiếp tục gắn bó với hàm SUM: hoặc 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 người khác và không bao giờ cần kiểm tra lại số liệu của mình; hoặc bạn thích giải thích lý do tại sao các con số tổng 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 tất cả 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 SUBTOTAL và các bảng tính của bạn sẽ trở nên linh hoạt, đá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. Đừng để những con số sai lệch gây ảnh hưởng đến công việc của bạn nữa!
