Trong công việc hàng ngày với bảng tính Excel hay Google Sheets, hàm SUM là công cụ quen thuộc giúp chúng ta tính tổng nhanh chóng. Tuy nhiên, nếu bạn thường xuyên làm việc với các bộ dữ liệu lớn, cần lọc hoặc ẩn hàng, bạn sẽ nhận ra hàm SUM bộc lộ những hạn chế đáng kể, khiến báo cáo trở nên thiếu chính xác và gây ra không ít phiền toái. Thay vì phải vật lộn với những con số không khớp và liên tục điều chỉnh công thức, đã đến lúc bạn cần một giải pháp vượt trội hơn – đó chính là hàm SUBTOTAL, một tính năng mạnh mẽ giúp quản lý dữ liệu động hiệu quả và đáng tin cậy hơn.
Vì sao hàm SUM không đáng tin cậy khi làm việc với dữ liệu đã lọc/ẩn?
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 do bộ lọc hoặc thao tác thủ công. Đây chính là vấn đề cốt lõi khi bạn cần một cái nhìn tổng quan chính xác về dữ liệu hiển thị sau khi đã lọc. Ví dụ, bạn có một bảng danh sách doanh số hàng trăm, hàng nghìn dòng. Khi bạn áp dụng bộ lọc để chỉ xem doanh số của “Sản phẩm A”, các hàng không liên quan sẽ biến mất khỏi tầm nhìn. Tuy nhiên, hàm SUM vẫn tiếp tục cộng cả những số liệu của các sản phẩm đã bị ẩn đi, dẫn đến con số tổng cuối cùng bị thổi phồng và không phản ánh đúng thực tế dữ liệu đang hiển thị.
Điều này không chỉ xảy ra với hàm SUM mà còn tương tự với các hàm cơ bản khác như COUNT hay AVERAGE. Việc phải liên tục giải thích tại sao “tổng” của bạn lại lớn hơn nhiều so với dữ liệu đang được nhìn thấy là một cơn đau đầu thực sự cho bất kỳ ai làm báo cáo. Nó làm mất đi tính tin cậy của dữ liệu và đòi hỏi người dùng phải can thiệp thủ công liên tục để có được con số chính xác.
Bảng dữ liệu Excel đã lọc hiển thị tổng sai do sử dụng hàm SUM
SUBTOTAL hoạt động như thế nào và ưu điểm vượt trội?
Hàm SUBTOTAL ra đời để khắc phục triệt để những nhược điểm của hàm SUM truyền thống. Điểm mạnh lớn nhất của SUBTOTAL là khả năng tự động điều chỉnh kết quả dựa trên các hàng hiển thị sau khi áp dụng bộ lọc hoặc ẩn hàng thủ công. Điều này có nghĩa là khi bạn lọc dữ liệu, tổng hoặc các phép tính khác của bạn sẽ tự động cập nhật để chỉ hiển thị kết quả của các hàng đang được nhìn thấy, mang lại sự chính xác tức thì.
Cú pháp cơ bản của hàm SUBTOTAL rất đơn giản:
=SUBTOTAL(function_num, range)Trong đó:
function_num: Là một số xác định loại phép tính mà bạn muốn thực hiện (ví dụ: tính tổng, đếm, trung bình, tìm giá trị lớn nhất/nhỏ nhất).range: Là phạm vi ô mà bạn muốn thực hiện phép tính.
Điều tuyệt vời là hàm SUBTOTAL không chỉ giới hạn ở việc tính tổng. Nó hỗ trợ một loạt các phép tính khác nhau, cho phép bạn dễ dàng chuyển đổi giữa SUM, AVERAGE, COUNT, MIN, MAX và nhiều phép tính hữu ích khác chỉ bằng cách thay đổi giá trị function_num. Dưới đây là bảng tổng hợp các function_num đượ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 |
Một điểm cần lưu ý là nếu bạn muốn hàm SUBTOTAL bao gồm cả các hàng bị ẩn (không phải do bộ lọc), bạn chỉ cần bỏ chữ số đầu tiên (1) khỏi function_num. Ví dụ, SUM với function_num là 9 sẽ bao gồm các ô bị ẩn, trong khi 109 sẽ bỏ qua chúng (kể cả hàng bị ẩn thủ công và hàng bị lọc).
Ngoài ra, một ưu điểm vượt trội khác của hàm SUBTOTAL so với SUM là khả năng tránh đếm trùng lặp. Nếu bạn có các bảng con với SUBTOTAL riêng và một tổng lớn ở cuối, hàm SUBTOTAL đủ thông minh để bỏ qua các SUBTOTAL con khác, đảm bảo tổng cuối cùng là chính xác. Hàm SUM thì không có khả năng này, dẫn đến việc cộng dồn và làm sai lệch con số tổng.
Hướng dẫn sử dụng hàm SUBTOTAL trong Excel và Google Sheets hiệu quả
Sự linh hoạt và dễ sử dụng là yếu tố khiến hàm SUBTOTAL trở thành lựa chọn tối ưu. Hãy cùng xem một ví dụ cụ thể để thấy cách SUBTOTAL giúp bạn đơn giản hóa công việc:
Ví dụ về cách áp dụng hàm SUM thông thường trong bảng tính Excel
Để tính tổng các ô trong một cột (ví dụ từ C2 đến C15) mà bỏ qua các ô bị ẩn hoặc lọc, bạn sẽ sử dụng công thức sau:
=SUBTOTAL(109, C2:C15)Với công thức này, khi bạn áp dụng bộ lọc cho bảng dữ liệu, tổng số sẽ tự động cập nhật để chỉ hiển thị tổng của các dòng đang hiển thị. Kết quả này sẽ luôn chính xác và phù hợp với dữ liệu bạn đang xem.
Kết quả tính tổng chính xác trên bảng Excel đã lọc khi sử dụng hàm SUBTOTAL
Tương tự, nếu bạn cần đếm số lượng ô không rỗng (COUNTA) hoặc tính giá trị trung bình (AVERAGE) của các hàng hiển thị, hàm SUBTOTAL cũng hỗ trợ mạnh mẽ. Ví dụ, để đếm số lượng mục hiển thị trong cột A (từ A2 đến A15), thay vì dùng =COUNTA(A2:A15), bạn có thể dùng:
=SUBTOTAL(103, A2:A15)Với function_num là 103 cho hàm COUNTA, số lượng đếm sẽ tự động điều chỉnh khi bạn lọc dữ liệu, đảm bảo rằng các giá trị trung bình (nếu có) của bạn luôn chính xác. SUBTOTAL cũng hỗ trợ các hàm như MAX và MIN, giúp bạn dễ dàng tìm giá trị lớn nhất hoặc nhỏ nhất trong phạm vi dữ liệu đã lọc.
Ứng dụng hàm SUBTOTAL với function_num 103 để đếm số ô hiển thị trong Excel
Tổng cộng, hàm SUBTOTAL hỗ trợ 11 loại phép tính khác nhau, đáp ứng hầu hết các nhu cầu tổng hợp dữ liệu cơ bản cho các bảng báo cáo. Điều quan trọng hơn, mọi kiến thức về hàm SUBTOTAL bạn học được ở đây đều có thể áp dụng linh hoạt trên cả Microsoft Excel và Google Sheets, mang lại sự nhất quán trong công việc dù bạn sử dụng nền tảng nào.
Kết luận
Việc tiếp tục sử dụng hàm SUM trong mọi trường hợp, đặc biệt là với dữ liệu cần lọc hoặc ẩn, chỉ dẫn đến những sai sót không đáng có và mất thời gian cho việc kiểm tra, giải thích. Hàm SUBTOTAL là một công cụ tối ưu và cần thiết cho bất kỳ ai muốn làm việc hiệu quả với bảng tính. Nó giúp dữ liệu của bạn trở nên động, đáng tin cậy và hầu như loại bỏ lỗi trong các báo cáo hàng ngày.
Nếu bạn đang tìm kiếm cách để nâng cao kỹ năng xử lý bảng tính và muốn đảm bảo độ chính xác cho mọi con số, hãy bắt đầu chuyển sang sử dụng hàm SUBTOTAL ngay hôm nay. Hãy thử nghiệm và trải nghiệm sự khác biệt mà nó mang lại cho công việc của bạn. Đừng quên theo dõi thuthuat360.net để cập nhật thêm nhiều thủ thuật công nghệ hữu ích khác!
