Nếu bạn đã từng làm việc với Excel hoặc Google Sheets, chắc hẳn hàm SUM là một trong những công cụ cơ bản và được sử dụng thường xuyên nhất. Nó đơn giản, dễ dùng và hoàn thành tốt nhiệm vụ của mình trong các bảng tính nhỏ. Tuy nhiên, trong thế giới thực của dữ liệu phức tạp và báo cáo chuyên nghiệp, hàm SUM bỗng trở nên “cùn” và dễ gây ra những sai sót khó lường.
Liệu có một cách nào tốt hơn để có được những tổng số liệu đáng tin cậy, đặc biệt khi bạn cần xử lý các bảng dữ liệu lớn với nhiều bộ lọc và hàng bị ẩn? Chắc chắn là có, và nó sẽ giúp bạn tiết kiệm vô số thời gian và những cơn đau đầu không đáng có.
Hàm SUM có vấn đề gì khi làm việc với dữ liệu đã lọc?
Vấn đề cốt lõi của hàm SUM là nó cộng tất cả mọi thứ, dù dữ liệu đó có hiển thị hay không. Đó là chức năng được thiết kế 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? Hàm SUM vẫn vui vẻ bao gồm tất cả những con số bị ẩn đó vào tổng của bạn. Kết quả là, nó làm phồng tổng số liệu, âm thầm phá hoại các báo cáo và phân tích của bạn. Nếu bạn đã từng phải vật lộn giải thích lý do tại sao “tổng cộng” của mình lại lớn hơn nhiều so với dữ liệu đang hiển thị sau khi lọc, thì bạn hoàn toàn hiểu rõ vấn đề này.
Đây là điểm mà hầu hết mọi người đều gặp bế tắc. Chúng ta thường dựa vào SUM, COUNT và các hàm cơ bản tương tự, liên tục điều chỉnh công thức mỗi khi số liệu không khớp. Cho đến khi khám phá ra một hàm có thể xử lý mọi thứ mà các hàm cơ bản này làm được, nhưng không hề gây ra những rắc rối thông thường: đó chính là hàm SUBTOTAL.
Hàm SUBTOTAL hoạt động khác biệt như thế nào?
Hãy hình dung bạn có một bảng dữ liệu doanh số bán hàng khổng lồ, hàng trăm hoặc thậm chí 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 tương ứng. Các con số của các sản phẩm khác biến mất khỏi tầm nhìn, nhưng hàm SUM không “nhận ra” điều đó. Nó vẫn cộng tất cả các dòng ở chế độ nền, bao gồm cả những dò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.
Hàm SUM hiển thị tổng không chính xác trên bảng Excel đã lọc dữ liệu
Mặt khác, hàm SUBTOTAL lại tự động điều chỉnh theo dữ liệu hiển thị. Với SUBTOTAL, khi bạn lọc dữ liệu, tổng số liệu 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ị.
Cú pháp cơ bản của hàm SUBTOTAL:
=SUBTOTAL(function_num, range)Điều thú vị là SUBTOTAL không chỉ giới hạn ở việc tính tổng. Nó có thể chuyển đổi linh hoạt giữa các phép tính như SUM, AVERAGE, COUNT, MIN, MAX 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 | 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á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ác ô ẩ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ụ (subtotals) cho từng danh mục và sau đó là một tổng lớn ở cuối, SUBTOTAL sẽ 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 số liệu của bạn. Nếu bạn đã từng thấy một tổng số liệu 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 phải vấn đề này.
Cách sử dụng hàm SUBTOTAL trong Excel và Google Sheets hiệu quả
Đ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 nhậ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 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 như thế nào.
Minh họa cách sử dụng hàm SUM trong một bảng dữ liệu Excel
Để tính tổng các ô, công thức sẽ là:
=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. Áp dụng công thức này cho các dòng khác, bạn sẽ có các tổng số liệu chính xác. Chúng hoạt động tốt cho toàn bộ bảng và vẫn hoạt động hoàn hảo khi bạn lọc bảng. Giờ đây, các con số đã trở nên hợp lý.
Hàm SUBTOTAL tự động cập nhật tổng khi lọc dữ liệu trong bảng Excel
Trong ví dụ trên, có thể bạn sẽ thấy hàm COUNT vẫn hiển thị 14 và các giá trị trung bình vẫn chia tổng phụ cho số đếm đó, khiến các 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 bạn (B16), thay vì viết:
=COUNTA(A2:A15)Bạn có thể thay thế bằng:
=SUBTOTAL(103, A2:A15)
Sử dụng hàm SUBTOTAL với mã 103 để đếm số ô hiển thị trong bảng dữ liệu
103 là số hàm cho COUNTA. Giờ đây, các giá trị đếm của bạn sẽ tự động điều chỉnh khi bạn lọc dữ liệu và các giá trị trung bình của bạn luôn hiển thị giá trị đúng. 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 chức năng 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 chức năng 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. Đặc biệt, mọi điều bạn đã học ở đây đều áp dụng được cho cả Google Sheets, giúp bạn làm việc hiệu quả trên mọi nền tảng.
Chỉ có hai lý do để bạn tiếp tục sử dụng 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ờ chuyển file của mình cho người khác, và không bao giờ cần kiểm tra số liệu của mình; hoặc hai là bạn thích việc phải giải thích lý do tại sao tổng số liệu 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 sang SUBTOTAL. Hãy áp 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 có lỗi cho việc báo cáo hàng ngày.
