Đánh giá lequocthai.com:
Trong nhiều năm, tôi đã phụ thuộc vào việc tính tổng lũy kế trong Microsoft Excel, nhưng các phương pháp cũ thường dễ phát sinh lỗi và thiếu ổn định. Kể từ khi Microsoft ra mắt hàm SCAN, tôi đã chuyển sang một cách tiếp cận hoàn toàn mới: linh hoạt, có khả năng tự điều chỉnh và vẫn hoạt động trơn tru ngay cả khi dữ liệu mở rộng.
Hàm SCAN có sẵn cho người dùng Excel thuộc gói Microsoft 365, Excel trên web, ứng dụng Excel cho di động/máy tính bảng, và các phiên bản Excel đơn lẻ (perpetual license) từ năm 2024 trở đi.
Tại sao phương pháp cũ lại có vấn đề?
Trước khi có hàm SCAN, để tính tổng lũy kế, tôi thường dùng kỹ thuật tham chiếu hỗn hợp (cố định điểm đầu).
Trong bảng tính này, cột B chứa lợi nhuận hàng tháng và tôi muốn hiển thị tổng lũy kế ở cột C.
Tại ô C2, tôi nhập:
=SUM($B$2:B2)Thoạt nhìn, công thức này hoạt động tốt. Tuy nhiên, giả sử sau đó tôi phát hiện đã bỏ sót một tháng. Khi chèn thêm một hàng mới, Excel không tự động điền công thức vào hàng trống đó, để lại một ô trống cần điền thủ công.
Hơn nữa, nếu tôi điền sẵn dữ liệu ngày tháng cho cả năm vào cột A và kéo công thức ở cột C xuống cuối, thì trừ khi tôi sửa công thức phức tạp để kết hợp hàm IF, tôi sẽ nhận được một chuỗi tổng lặp lại trông rất rối mắt và khó theo dõi.
Các ví dụ trên dùng phạm vi dữ liệu thông thường. Nếu tôi định dạng dữ liệu thành Bảng Excel (Table), mọi thứ còn phức tạp hơn. Tham chiếu có cấu trúc (structured reference) của bảng không có cách tích hợp sẵn để cố định điểm bắt đầu, vì vậy tôi phải ghi đè logic bảng bằng tham chiếu ô thông thường hoặc dùng một công thức mới với hàm INDEX:
=SUM(INDEX([Profit],1):[@Profit])Giải pháp: Hàm SCAN
Hàm SCAN chỉ cần nhập vào một ô duy nhất và tự động tràn kết quả (spill) xuống các ô bên dưới, đảm bảo tổng lũy kế luôn chính xác.
Cú pháp hàm SCAN
Hàm SCAN có ba đối số:
=SCAN([initial_value], array, lambda)Trong đó:
[initial_value]: Giá trị khởi tạo cho phép tính tích lũy. Tùy chọn, có thể để trống.array: Mảng hoặc phạm vi dữ liệu cần xử lý.lambda: Một hàm LAMBDA định nghĩa phép tính sẽ thực hiện trên từng phần tử.
Cấu trúc hàm LAMBDA
Đối số lambda chấp nhận ba tham số:
=LAMBDA(accumulator, value, body)Trong đó:
- accumulator: Giá trị tích lũy tạm thời (tổng tạm thời) được truyền từ bước trước và trả về kết quả.
- value: Giá trị hiện tại trong mảng đang được xử lý.
- body: Phép tính được áp dụng cho accumulator và value.
Lưu ý quan trọng trước khi bắt đầu
Thoạt đầu, SCAN có vẻ phức tạp hơn một phép SUM đơn giản. Nhưng sự khác biệt nằm ở chỗ: SCAN đòi hỏi học một chút ban đầu, nhưng một khi đã thiết lập, nó trở thành công cụ “cài rồi quên”, bảo vệ dữ liệu của bạn khỏi các lỗi thủ công thường gặp với phương pháp cũ.
Để làm theo hướng dẫn, bạn có thể tải xuống một bản sao miễn phí của sổ làm việc Excel được dùng trong các ví dụ. Sau khi nhấp vào liên kết, nút tải xuống nằm ở góc trên bên phải màn hình.
Ví dụ 1: Tính tổng lũy kế cơ bản
Trong bảng tính này, cột Tháng và Lợi nhuận được định dạng thành Bảng Excel (tên là T_Profits). Cột Tổng lũy kế nằm ngoài bảng, ở cột D. Cột C trống đóng vai trò cột đệm để bảng không tự động mở rộng bao gồm cột kết quả.
Công thức nhập vào ô D2:
=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a+b))Trong đó:
0: Giá trị khởi tạo cho tổng.T_Profits[Profit]: Mảng dữ liệu lợi nhuận từ bảng.LAMBDA(a, b, a+b): Hàm lambda, vớialà tổng tạm thời,blà giá trị hiện tại.a+bcộng dồn giá trị hiện tại vào tổng.
Bạn có thể dùng bất kỳ ký hiệu nào cho các đối số của LAMBDA. Tôi dùng a và b cho ngắn gọn.
Lợi ích của phương pháp này thể hiện rõ khi tôi chèn một hàng mới vào giữa tập dữ liệu — công thức tự động xử lý thay đổi này, và tôi chỉ cần nhập lợi nhuận tháng vào ô trống trong cột B.
Tôi cũng không cần điền sẵn dữ liệu hay kéo công thức. Khi nhập một tháng mới vào ô A22, toàn bộ kết quả ở cột D tự động cập nhật.
Tương tự, nếu xóa một hàng, công thức vẫn hoạt động mà không gặp vấn đề gì.
Ví dụ 2: Tính trung bình động (Running Average)
Để có trung bình động cập nhật tự động, tôi kết hợp SCAN với SEQUENCE. Trong khi tổng lũy kế cộng giá trị, trung bình động chia tổng đó cho số lượng phần tử đã xử lý.
Công thức tại ô D2:
=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a+b)) / SEQUENCE(ROWS(T_Profits[Profit]))Trong đó:
- Phần
SCAN(...)tính tổng lũy kế như ví dụ 1. SEQUENCE(ROWS(T_Profits[Profit]))tạo mảng số thứ tự {1;2;3;…} tương ứng số hàng dữ liệu.- Chia tổng lũy kế cho mảng số thứ tự này cho ra trung bình động đơn giản.
Lưu ý: Công thức này yêu cầu không có hàng trống trong phạm vi dữ liệu. Một ô trống sẽ được SEQUENCE coi là 0, làm sai lệch kết quả trung bình.
Ví dụ 3: Đặt lại tổng lũy kế theo năm (Year-to-Date Reset)
Các ví dụ trên tính tổng xuyên suốt nhiều năm. Giả sử tôi muốn tổng lũy kế đặt lại về 0 khi bắt đầu mỗi năm mới (tính tổng theo từng năm riêng biệt).
Công thức cần dùng:
=SCAN(0, SEQUENCE(ROWS(T_Profits[Profit])), LAMBDA(a, i, IF(MONTH(INDEX(T_Profits[Month], i))=1, INDEX(T_Profits[Profit], i), a + INDEX(T_Profits[Profit], i))))Giải thích:
SEQUENCE(ROWS(...))tạo danh sách số {1;2;3;…} đại diện cho vị trí từng hàng.- Trong LAMBDA,
ilà số hàng hiện tại. INDEX(T_Profits[Month], i)lấy ngày tháng tại hàngi.IF(MONTH(...)=1, ...): Nếu tháng đó là tháng 1, thì tổng được đặt lại và chỉ lấy lợi nhuận của tháng đó (INDEX(T_Profits[Profit], i)). Nếu không phải tháng 1, thì thực hiện cộng dồn như bình thường (a + INDEX(T_Profits[Profit], i)).
Bước hoàn thiện cuối cùng
Để bảng tính chuyên nghiệp và dễ sử dụng hơn:
- Định dạng số: Áp dụng định dạng Tiền tệ hoặc Số cho cột kết quả.
- Xử lý lỗi: Bao bọc công thức SCAN trong
IFERRORđể thay thế lỗi bằng giá trị trống hoặc thông báo.
Ví dụ: =IFERROR(SCAN(...), "")
- Tích hợp với Bảng: Nếu muốn kết quả nằm trong chính Bảng Excel, hãy chèn cột mới vào bảng và nhập công thức tương tự với tham chiếu có cấu trúc của bảng.
Tags: Excel SCAN, cumulative sum, Excel automation
