VietNetwork.Vn

Tôi đã từ bỏ thói quen "kéo và điền" để chuyển sang phương pháp tự động hóa, gọn gàng hơn cho việc lập báo cáo hàng ngày của mình.

Trong nhiều năm, tôi vẫn dựa vào việc tính tổng liên tục trong Microsoft Excel, nhưng các phương pháp truyền thống rất dễ hỏng và không đáng tin cậy. Kể từ khi Microsoft giới thiệu hàm SCAN, tôi đã chuyển sang một phương pháp hoàn toàn mới, linh hoạt, tự phục hồi và không bị lỗi khi dữ liệu của tôi tăng lên.


Hàm QUÉT (SCAN) khả dụng cho những người sử dụng Excel trên Microsoft 365, Excel trên web, ứng dụng Excel trên thiết bị di động và máy tính bảng, cũng như các phiên bản Excel đặc biệt được phát hành từ năm 2024 trở đi.

1. Vì sao phương pháp truyền thống lại có vấn đề

Trước khi chức năng SCAN được giới thiệu, nếu tôi muốn tính tổng lũy kế, tôi sẽ sử dụng thủ thuật tuyệt đối-tương đối.

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.


Trong ô C2, tôi sẽ nhập:

=SUM($B$2:B2)
Sau đó, tôi sẽ nhấp đúp vào tay cầm điền tự động để áp dụng công thức cho các hàng còn lại.


Thoạt nhìn, cách này có vẻ hoạt động tốt. Tuy nhiên, giả sử sau đó tôi nhận ra mình đã bỏ sót một tháng. Khi tôi 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 khoảng trống cần phải được xử lý thủ công.


Ngoài ra, nếu tôi đặt ngày tháng ở cột A thành cuối năm và kéo công thức ở cột C xuống cuối, trừ khi tôi thay đổi công thức một cách đáng kể để kết hợp câu lệnh IF, tôi sẽ nhận được tổng cộng lặp lại không gọn gàng và khó hiểu.


Trong các ví dụ trên, dữ liệu nằm trong một phạm vi thông thường. Tuy nhiên, nếu tôi định dạng dữ liệu của mình dưới dạng bảng Excel, mọi thứ sẽ trở nên phức tạp hơn. Tham chiếu có cấu trúc không có cách nào tích hợp sẵn để neo điểm bắt đầu của một phạm vi, vì vậy tôi sẽ cần phải ghi đè logic của bảng bằng các tham chiếu ô thông thường hoặc xây dựng một công thức mới sử dụng hàm INDEX.

=SUM(INDEX([Profit],1):[@Profit])


2. Giải pháp: Hàm SCAN

Hàm SCAN nằm trong một ô duy nhất và hiển thị kết quả xuống cột, đảm bảo tổng số của tôi luôn chính xác về mặt cấu trúc và toán học.

2.1. Cú pháp SCAN

Hàm SCAN có ba tham số:

=SCAN([initial_value],array,lambda)
Trong đó:

    initial_value là điểm khởi đầu cho tổng tích lũy.
    `array` là mảng cần được quét để tạo tổng tích lũy.
    Lambda là một hàm tùy chỉnh được gọi để quét mảng.

2.2. Cấu trúc LAMBDA

Đối số thứ ba, lambda, chấp nhận ba tham số:

=LAMBDA(accumulator,value,body)
Trong đó:

    Biến tích lũy là giá trị được cộng lại và trả về dưới dạng kết quả.
    `value` là giá trị hiện tại trong mảng.
    `body` là phép tính được áp dụng cho từng phần tử trong mảng.

2.3. Những điểm quan trọng cần lưu ý trước khi bắt đầu

Thoạt nhìn, hàm SCAN có vẻ phức tạp hơn hàm SUM đơn giản. Nhưng có sự khác biệt giữa một công thức khó gõ và một công thức khó quản lý. Hàm SCAN đòi hỏi một chút thời gian học hỏi ban đầu, nhưng một khi đã thiết lập xong, nó là một công cụ "cài đặt một lần và quên đi" giúp bảo vệ dữ liệu của bạn khỏi những lỗi thủ công thường gặp khi tính tổng lũy kế truyền thống.

Mặc dù hàm SCAN rất hữu ích để xử lý tổng lũy kế, nhưng có một hạn chế: nó không hoạt động bên trong bảng Excel. Điều này là do bảng yêu cầu mỗi hàng phải có công thức hoặc giá trị độc lập riêng, nhưng hàm SCAN lại trải kết quả trên nhiều hàng. Điều này có nghĩa là nó phải được sử dụng trong một phạm vi thông thường. Tuy nhiên, vẫn nên định dạng dữ liệu nguồn dưới dạng bảng Excel vì những lý do tôi sẽ đề cập sau.

Để dễ dàng theo dõi khi đọc, hãy tải xuống bản sao miễn phí của bảng tính Excel được sử dụng trong các ví dụ. Sau khi nhấp vào liên kết, bạn sẽ thấy nút tải xuống ở góc trên bên phải màn hình.

3. Ví dụ 1: Tính tổng lũy kế cơ bản

Trong bảng tính này, các cột Tháng và Lợi nhuận được định dạng dưới dạng bảng Excel (có tên là T_Profits), và cột tổng lũy kế nằm trong phạm vi thông thường ở cột D. Cột trống C đóng vai trò là vùng đệm để bảng không "chiếm" cột bên ngoài.


Đây là công thức tôi sẽ nhập vào ô D2:

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))
Trong đó:

    Giá trị initial_value là 0 vì hàng 2 chứa mục nhập đầu tiên trong tập dữ liệu của tôi (tôi không chuyển bất kỳ số liệu nào từ năm 2023).
    T_Profits[Profit] là danh sách các giá trị lợi nhuận trong cột Lợi nhuận của bảng T_Profits mà nó sẽ quét qua.
    Hàm LAMBDA(a,b,a+b) coi a là tổng số tiền tính đến thời điểm hiện tại, b là giá trị trong cột lợi nhuận trên hàng hiện tại, và a+b là phép tính cần thực hiện.


Bạn có thể sử dụng bất kỳ ký hiệu nào cho ba đối số của hàm LAMBDA. Tôi sử dụng a và b để giữ cho công thức ngắn gọn nhất có thể, nhưng bạn cũng có thể sử dụng các tên mô tả rõ ràng hơn.

Lợi ích của việc sử dụng phương pháp này thay vì cách làm truyền thống trở nên rõ ràng khi tôi cần chèn một hàng vào giữa tập dữ liệu — nó xử lý thay đổi cấu trúc này một cách dễ dàng, sẵn sàng cho tôi nhập tổng số hàng tháng vào ô trống ở cột B.


Tôi cũng không cần phải thêm ngày vào cột A hoặc kéo bất kỳ công thức nào xuống, bởi vì khi tôi nhập tháng mới vào ô A22, toàn bộ cấu trúc sẽ tự động điều chỉnh cho phù hợp với sự bổ sung này.


Hơn nữa, nếu tôi cần xóa một hàng ở giữa tập dữ liệu, nó sẽ xử lý việc chỉnh sửa này một cách dễ dàng.

4. Ví dụ 2: Theo dõi đường trung bình động

Để tính trung bình động cập nhật khi tôi thêm dữ liệu, tôi kết hợp các hàm SCAN và SEQUENCE. Trong khi tổng tích lũy trong ví dụ trên cộng các giá trị, trung bình động chia tổng đó cho số lượng mục đã được xử lý cho đến nay.

Để làm điều này, trong ô D2, tôi sẽ nhập:

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))/SEQUENCE(ROWS(T_Profits[Profit]))
Trong đó:

    Phần SCAN của công thức tính tổng lũy kế chính xác như tôi đã làm trong Ví dụ 1.
    Phần SEQUENCE tạo ra một danh sách số động (1, 2, 3...) tương ứng với chỉ số hàng. Ví dụ, ở hàng thứ năm, chuỗi sẽ trả về số 5.
    Bằng cách chia (/) tổng tích lũy cho số thứ tự, Excel sẽ trả về giá trị trung bình của tất cả các giá trị từ đầu đến hàng hiện tại.


Công thức này yêu cầu tôi phải đảm bảo không có bất kỳ hàng trống nào bên trong. Ví dụ, nếu không có dữ liệu cho tháng 5 năm 2025, hàm SEQUENCE sẽ coi ô trống đó là số 0, điều này làm giảm giá trị trung bình một cách không chính xác.

5. Ví dụ 3: Thiết lập lại từ đầu năm đến nay

Trong các ví dụ trước, hàm SCAN được sử dụng với giả định rằng tôi muốn cộng dồn tổng số tích lũy qua năm 2024 và 2025. Tuy nhiên, giả sử tôi muốn phép tính tích lũy đến tháng 12 năm 2024 và sau đó đặt lại về 0 khi đến tháng 1 năm 2025.

Nếu dùng phương pháp truyền thống, tôi sẽ phải tự tay ngắt chuỗi tổng kiểm tra mỗi 12 tháng. Tuy nhiên, với SCAN, tôi có thể tích hợp công tắc đặt lại trực tiếp vào LAMBDA.

Đây là công thức tôi cần sử 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))))
Trong đó:

    SEQUENCE(ROWS(T_Profits[Profit])) tạo ra một danh sách các số (1, 2, 3...) đại diện cho mỗi hàng.
    Trong hàm LAMBDA, i biểu thị số thứ tự của hàng hiện tại.
    INDEX(T_Profits[Month],i) xem xét ngày trong hàng hiện tại. Nếu đó là tháng Giêng ( 1 ), công tắc đặt lại sẽ được bật, chỉ trả về lợi nhuận hiện tại ( INDEX(T_Profits[Profit],i) ). Nếu đó là bất kỳ tháng nào khác, nó sẽ thực hiện phép tính a+lợi nhuận thông thường.


6. Các bước cuối cùng: Dọn dẹp kết quả đầu ra

Mặc dù các công thức trên hoạt động tốt, nhưng khâu hoàn thiện cuối cùng mới là điều giúp bảng tính của tôi trở nên tốt nhất có thể:

    Nguyên tắc "một ô": Hãy nhớ rằng vì đây là các công thức tràn, chúng chỉ cần được nhập vào ô đầu tiên. Nếu tôi cần thay đổi logic (ví dụ như chuyển từ tổng lũy kế sang trung bình lũy kế), tôi chỉ cần chỉnh sửa duy nhất ô đó.
    Lỗi tràn dữ liệu: Nếu tôi thấy lỗi #SPILL! khi nhập công thức và nhấn Enter, thường là do có thứ gì đó đang ngăn cản mảng động tràn dữ liệu. Trong trường hợp này, ngay khi tôi xóa bất kỳ công thức thủ công cũ hoặc văn bản thừa nào trong cột, kết quả SCAN sẽ được hiển thị ngay lập tức.

Mặc dù hàm SCAN là một bước đột phá trong việc tính tổng lũy kế, nó chỉ là một trong số nhiều hàm thay đổi cuộc chơi đã làm thay đổi cách thức hoạt động của Excel – nhờ vào mảng động. Các hàm như FILTER, SORT và UNIQUE về cơ bản đã thay thế nhiều hàm cũ mà tôi từng dựa vào. Thay vì phải xử lý từng hàng trong tập dữ liệu, các công cụ mới này cho phép một ô duy nhất đảm nhiệm phần lớn công việc trên toàn bộ phạm vi dữ liệu. Bằng cách ưu tiên hành vi tràn dữ liệu này, tôi đang xây dựng các bảng tính không còn mắc phải những lỗi thủ công kinh điển từng làm gián đoạn quy trình làm việc của tôi.