Không có gì to lớn hơn việc đếm số ngày kể từ lần thanh toán trước đó trong Google Sheets. Chúng ta có thể đơn giản hóa việc này bằng cách sử dụng công thức mảng. Dưới đây là cách làm.
Lý do chúng ta cần công thức tùy chỉnh?
Cột C là cột thanh toán và bạn có thể thấy các ngày thanh toán tương ứng trong cột A. Như bạn có thể thấy, có các ô trống (không có thanh toán) giữa các lần thanh toán. Nếu không có bất kỳ ngày nào khác giữa các ngày thanh toán (nếu tất cả thanh toán nằm trong các hàng liền kề), chúng ta có thể sử dụng hai hàm chuyên dụng sau đây: DAYS và DATEDIF. Chúng ta cũng có thể sử dụng toán tử trừ để trả về số ngày kể từ ngày thanh toán trước đó trong Google Sheets. Một tùy chọn khác là dùng Query DATEDIFF, nhưng điều đó không phù hợp với bảng của chúng ta.
Công thức mảng để đếm số ngày kể từ lần thanh toán trước đó trong Google Sheets
Hãy xem công thức của tôi từng bước một. Trước đó, để có được sự cân đối, tôi đã sử dụng công thức sau đây trong ô D1:
={"Nợ còn lại (Số dư tổng cộng)";ArrayFormula(if(len(A2:A),(SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-SUMIF(ROW(A2:A),"<="&ROW(A2:A),C2:C)),))}Dưới đây là công thức mảng của tôi đếm số ngày kể từ lần thanh toán trước đó.
Công thức dài vì tôi đã thay thế một số tham số bằng biểu thức. Vì vậy, hãy cùng tìm hiểu công thức từng bước.
Công thức để trả về số hàng của các ngày thanh toán
Đây là bước đầu tiên của chúng ta.
Như bạn có thể thấy từ các hàng được tô màu, các số hàng cần được trích xuất là 3, 4, 6, 7, 8, 9, 11 và 13.
Chúng ta có thể làm được điều này bằng cách lọc các số hàng A2:A nếu các giá trị trong C2:C là > 0.
Dưới đây là công thức cho Bước 1:
=query(filter(row(A2:A),C2:C>0),"Select * offset 1",0)Chúng ta không muốn số hàng của ngày thanh toán đầu tiên vì không có ý nghĩa trong việc tìm hiểu sự khác biệt giữa các ngày khác nhau vì đây là lần thanh toán đầu tiên. Đó là lý do tại sao tôi đã sử dụng Truy vấn kết hợp với Bộ lọc và lùi 1 hàng. Tôi đã chèn công thức trên vào ô G1. Hãy xem ảnh chụp màn hình ở bước thứ hai dưới đây.
Tạo hai cột ngày từ một cột thanh toán
Bước thứ hai này là bước quan trọng trong việc viết công thức mảng để đếm số ngày kể từ lần thanh toán trước đó trong Google Sheets.
Bước này sẽ giúp chúng ta trả về sự khác biệt ngày từ lần thanh toán cuối cùng.
Mục đích của bước thứ nhất là phân phối kết quả bước 2 vào những hàng tương ứng. Chúng ta sẽ tìm hiểu điều đó sau.
Có hai công thức.
Trong ô H1:
={FILTER(A2:A,C2:C>0);0}Trong ô I1:
={0;filter(A2:A,C2:C>0)}Những công thức đầu tiên và thứ hai (công thức H1 và I1) lọc phạm vi ô A2:A nếu số tiền đã thanh toán (C2:C) lớn hơn 0.
Công thức đầu tiên chèn một hàng chứa 0 ở cuối và công thức thứ hai chèn 0 ở đầu hàng.
Bạn có thể tham khảo hình ảnh trên để hiểu nó (xem dấu mũi tên).
Ở đây, logic nằm trong công thức để đếm số ngày kể từ lần thanh toán trước đó trong Google Sheets. Hãy xem chi tiết phần đó trong bước tiếp theo.
Công thức mảng để đếm số ngày kể từ các ngày thanh toán trước đó trong Google Sheets
Đây là bước thứ ba của chúng ta, chỉ còn một bước nữa thôi.
Trừ các ngày trong cột I từ các ngày trong cột H để có được số ngày kể từ lần thanh toán trước đó.
Nhưng chúng ta không muốn sự khác biệt ngày trong các ô J1 và J9 (vui lòng xem ảnh bên dưới) vì mỗi hàng đều có một ô trống (I1 và H9).
Vì vậy, chúng ta có thể sử dụng công thức chung này:
if(step_2.1_formula*step_2.2_formula,step_2.1_formula-step_2.2_formula,)Công thức tương ứng trong ô J1 (Công thức Dự thảo Bước 3):
=ArrayFormula(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},))Nếu nhân của công thức step_2.1_formula với step_2.2_formula trả lại một giá trị lớn hơn 0, hãy trừ kết quả của công thức step_2.2_formula từ kết quả của công thức step_2.1_formula.
Như bạn có thể thấy, nếu bạn xem cột G, có hai ô trống trong cột J. Hai ô mà tôi đang nói đến là J1 và J9.
Nếu tôi sửa trực tiếp công thức trên, có thể sẽ gây nhầm lẫn cho một số người. Vì vậy, tôi sẽ chỉnh sửa nó dựa trên công thức chung sau đây.
=filter(step_3_draft_formula,step_3_draft_formula<>"")Do đó, công thức để sử dụng trong ô J1 như sau:
=filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")Vlookup để sắp xếp số ngày kể từ lần thanh toán trước đó vào các hàng tương ứng
Trong bước cuối cùng này, hãy kết hợp tất cả các phần lại.
Chúng ta hiện đã có tất cả thông tin cần thiết.
Điều đó có nghĩa là chúng ta có số ngày kể từ lần thanh toán trước đó trong cột J và các số hàng tương ứng trong cột G.
Chúng ta có thể kết hợp chúng để tạo ra một bảng (phạm vi) để sử dụng Vlookup. Như vậy, sử dụng các số hàng A2:A làm khóa tìm kiếm, chúng ta có thể phân phối số ngày.
Công thức chung để sử dụng trong ô E2:
=ArrayFormula(IFNA(vlookup(row(A2:A),{step_1_formula,step_3_formula},2,0)))Phần công thức {step_1_formula,step_3_formula} kết hợp các cột G và J.
Vlookup cố gắng khớp tất cả các số hàng trong A2:A trong bảng này và trả về kết quả từ cột thứ hai.
Dưới đây là dạng công thức:
=ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))Công thức cuối cùng:
Chúng ta muốn công thức trong ô E1, không phải trong ô E2. Vì vậy, hãy chèn tiêu đề “Số ngày kể từ lần thanh toán trước đó” trong đó.
={"Số ngày kể từ lần thanh toán trước đó";ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))}Trên đây là công thức cuối cùng. Bây giờ bạn có thể xóa tất cả các công thức cột trợ giúp trong phạm vi G1:J.
Đó là tất cả. Cảm ơn vì đã đọc. Chúc bạn thấy thú vị!
