Bạn đang có một công thức mảng để tính tổng tiến trình ngược trong Google Sheets.
Khi bạn đã sắp xếp dữ liệu của mình từ mới nhất đến cũ nhất, bạn có thể thấy công thức này rất hữu ích so với tổng tiến trình (tổng tích lũy).
Ví dụ, hãy xem xét sản lượng lúa mì tại Úc từ năm 2010 đến 2019 (dữ liệu từ wiki).
Trong Google Sheets, chúng ta có thể sắp xếp dữ liệu trong hai cột như sau.
Cột A và B chứa các năm và số lượng sản lượng lúa mì (triệu tấn), tương ứng.
Điều quan trọng ở đây là dữ liệu được sắp xếp theo thứ tự giảm dần (từ mới nhất đến cũ nhất) theo năm trong cột A.
Hãy xem các giá trị trong các cột tổng tiến trình C và D khác nhau như thế nào.
Ở cột C, bạn có thể thấy tổng tiến trình được tính từ trên xuống dưới, trong khi ở cột D, tính toán tương tự từ dưới lên trên.
Từ cột D, bạn có thể hiểu được tổng sản lượng lúa mì tích lũy cho đến năm đó từ mỗi hàng.
Làm thế nào để tính tổng tiến trình ngược trong Google Sheets như trên ở cột D?
Nếu bạn thích một công thức không thuộc mảng, bạn có thể sử dụng công thức sau trong ô D2 và kéo xuống cho đến hàng #11.
=sum($B$2:$B)-sum(arrayformula(n($B$1:B1)))
Nhưng tôi có một công thức mảng.
Công thức mảng cho Tổng tiến trình ngược trong Google Sheets
Chúng ta sẽ sử dụng một công thức mảng SUMIF ở đây.
Cú pháp:
ArrayFormula(SUMIF(range, criterion, [sum_range]))
Chúng ta có sum_range để tính tổng tiến trình tích lũy ngược, đó là B2:B. Còn range và criterion thì sao?
Bạn có thể tìm hiểu thêm trong công thức dưới đây.
Làm trống cột D hoàn toàn vì chúng ta muốn chèn một công thức mảng vào ô D1, đòi hỏi một cột trống để hoạt động mà không gặp lỗi #REF.
Công thức dưới đây dành cho ô D1.
={“Tổng tiến trình ngược”;ArrayFormula(If(B2:B=””,,SUMIF(sort(row(A2:A),1,0),”<=”&sort(row(A2:A),1,0),B2:B)))}
Công thức mảng trên sẽ trả về tổng tiến trình ngược trong Google Sheets cho phạm vi B2:B.
Nó có lợi ích rõ rệt so với phiên bản không thuộc mảng.
Nó sử dụng một phạm vi mở B2:B. Vì vậy, nó sẽ trả về tổng tiến trình ngược trong tất cả các hàng trong cột D. Tuy nhiên, cột B phải có giá trị. Các hàng trống sẽ được bỏ qua.
Hãy tìm hiểu về phạm vi và điều kiện được sử dụng trong công thức từ phần giải thích dưới đây.
Phần giải thích công thức
Hãy loại bỏ chuỗi không cần thiết khỏi công thức, tức là dòng tiêu đề “Tổng tiến trình ngược”, và làm cho các phạm vi đóng. Sau đó sử dụng một cột trợ giúp.
Vì vậy, chúng tôi có thể rút gọn công thức SUMIF và làm cho nó dễ đọc.
=ArrayFormula(If(B2:B11=””,,SUMIF(C2:C11,”<=”&C2:C11,B2:B11)))
Chúng tôi có thể chia công thức thành ba phần.
Phần 1
ArrayFormula – Để giúp SUMIF trả về kết quả mảng. IF cũng yêu cầu điều này.
ArrayFormula(
Phần 2
IF – Để giới hạn đầu ra trong những hàng chứa giá trị trong cột B.
If(B2:B11=””,,
Phần 3
SUMIF – Để trả về tổng tiến trình ngược trong Google Sheets.
SUMIF(C2:C11,”<=”&C2:C11,B2:B11)
Chú ý: Phạm vi ô C2:C11 là một phạm vi trợ giúp tạm thời cho phần giải thích của công thức. Vui lòng tham khảo hình ảnh phía trên. Trong công thức chính, chúng tôi đã sử dụng sort(row(A2:A),1,0) thay vào đó.
Công thức này đòi hỏi một lời giải thích chi tiết để hiểu cách SUMIF trả về tổng tiến trình ngược trong Google Sheets.
Hãy thử công thức trong một hàng cụ thể, ví dụ: hàng số 5.
=SUMIF(C2:C11,”<=”&C5,B2:B11)
Công thức trả về tổng sản lượng lúa mì tại Úc từ năm 2010 đến 2016 (các ô được đánh dấu).
Bởi vì tiêu chí, tức là C2:C11<=C5, trong cột C khớp trong các hàng được đánh dấu đó.
Tài liệu tham khảo:
- Running Count in Google Sheets – Formula Examples.
- How to Calculate Running Balance in Google Sheets.
- Array Formula for Conditional Running Total in Google Sheets.
- Sum, Count, Cumulative Sum Comma Separated Values in Google Sheets.
- Cumulative Count of Distinct Values in Google Sheets (How-To).
- Cumulative Balance against Each Payment in Google Sheets.
Và đừng quên kiểm tra website Crawlan để tham khảo thêm các bài viết hữu ích khác!
