Nếu bạn đáp ứng hai tiêu chí dưới đây, việc viết một công thức mảng để đặt lại tổng chạy hàng năm trong Google Sheets sẽ dễ dàng hơn bao giờ hết.
Đó là hai điều kiện gì ?
- Bạn phải sắp xếp bảng dữ liệu theo cột ngày theo thứ tự tăng dần hoặc giảm dần.
- Không có ô trống nào trong cột ngày.
Tương tự như tổng chạy có điều kiện, chúng ta cũng có thể sử dụng MMULT ở đây.
Nhưng, như bạn có thể biết, đó là một công thức “nóng tài nguyên” và có thể bị lỗi khi dữ liệu lớn.
Để đặt lại tổng chạy hàng năm, chúng ta có thể sử dụng một công thức dựa trên SUMIF trong Google Sheets. Đó có vẻ là một công thức “thân thiện với tài nguyên”.
Dữ liệu mẫu:
Hai cột đầu tiên trong bảng dưới đây chứa dữ liệu mẫu, và cột thứ ba chứa kết quả dự kiến của công thức của chúng tôi.
| Ngày | Số tiền | Tổng chạy lại ở mỗi năm |
|---|---|---|
Hãy viết công thức mảng để đặt lại tổng chạy hàng năm trong Google Sheets.
Đặt lại tổng chạy hàng năm (Công thức mảng):
Hai cột đầu tiên của dữ liệu mẫu là A1:B, trong đó A1 và B1 chứa nhãn trường “Ngày” và “Số tiền”.
Điều đó có nghĩa rằng chúng ta cần tính toán từ hàng #2, vì vậy (mảng) công thức sẽ đi vào ô C2.
Chúng ta có dữ liệu đã được sắp xếp theo thứ tự A-Z, còn được gọi là thứ tự tăng dần. Vì vậy, chúng ta có thể sử dụng công thức sau trong ô C2 để đặt lại tổng chạy hàng năm trong A2:A.
=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), "<"&year(A2:A), B2:B),))Hãy trả lời hai câu hỏi nhanh mà người dùng có thể tự hỏi.
Có bao gồm bản ghi tương lai sau hàng #9 không?
Có! Đó sẽ có.
Tại sao công thức trả về lỗi #REF?
Nếu đó là trường hợp, hãy chọn C3:C và nhấn nút xóa.
Bởi vì công thức được sử dụng để đặt lại tổng chạy hàng năm là một công thức mảng.
Nó yêu cầu các ô trống phía dưới ô C2 để mở rộng. Nếu không, nó sẽ cố ghi đè lên dữ liệu hiện có và có thể gây ra lỗi được đề cập ở trên.
Nếu các ngày trong cột A được sắp xếp theo thứ tự Z-A (giảm dần), công thức đặt lại tổng chạy hàng năm trên sẽ không hoạt động đúng.
Bạn phải thực hiện một thay đổi trong công thức, tức là thay thế < bằng >. Đây là công thức đó!
=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), ">"&year(A2:A), B2:B),))Giải thích công thức
Hãy xem xét dữ liệu được sắp xếp theo thứ tự A-Z và công thức để giải thích. Điều đó sẽ giúp bạn hiểu cái khác, tức là dữ liệu được sắp xếp theo thứ tự Z-A.
Thực ra, có hai phần chính trong công thức, và đây là chúng (được làm nổi bật bằng màu đỏ và màu xanh lá cây).
=ArrayFormula( if(len(A2:A), sumif(row(B2:B), "<="&row(B2:B), B2:B) - sumif(year(A2:A), "<"&year(A2:A), B2:B), ) )Chúng trả về tổng chạy (CUSUM) của cột B theo hai cách khác nhau. Tôi sẽ đến với đó.
Phần 1 (tham khảo cột E trên hình bên dưới):
Tổng số cột B nếu số dòng nhỏ hơn hoặc bằng số dòng. Vì vậy, nó trả về tổng của một chuỗi số trong A2:A ở mỗi thay đổi hàng.
=ArrayFormula( sumif(row(B2:B), "<="&row(B2:B), B2:B) )Vui lòng lưu ý rằng chúng ta cần sử dụng hàm ArrayFormula để mở rộng kết quả. Vì vậy, chúng ta phải sử dụng nó với mỗi phần như trên khi kiểm tra.
Phần 2 (tham khảo cột F trên hình bên dưới):
Tổng số cột B nếu năm nhỏ hơn năm. Vì vậy, nó trả về tổng của một chuỗi số trong B2:B ở mỗi thay đổi năm trong cột A.
=ArrayFormula( sumif(year(A2:A), "<"&year(A2:A), B2:B) )Phần 1 – Phần 2 = công thức cuối cùng để đặt lại tổng chạy dựa trên thay đổi năm trong cột ngày.
Chúng ta cũng đã sử dụng if(len(A2:A) để loại bỏ các giá trị từ các hàng trống sau hàng #9.
Trên đây là các công thức mảng thân thiện với tài nguyên để đặt lại tổng chạy hàng năm trong Google Sheets.
Cảm ơn vì đã đọc. Thành công!
Tài liệu tham khảo
- Reverse Running Total in Google Sheets (Array Formula)
- Running Count in Google Sheets – Formula Examples
- How to Calculate Running Balance in Google Sheets
- Running Max Values in Google Sheets (Array Formula Included)
- Find the Running Minimum Value in Google Sheets
- Calculating Running Average in Google Sheets (Array Formula)
