Trong Google Sheets, chúng ta có thể sử dụng một công thức mảng để phân bổ số tiền vào các cột hàng tháng. Đây là một nhiệm vụ rất phức tạp!
Tôi đã quen thuộc với Google Sheets. Việc viết một công thức mảng để phân bổ số tiền hàng tháng là khá phức tạp phải không?
Đúng vậy! Nó hơi phức tạp. Dường như đơn giản, nhưng tôi đã mất một vài giờ để giải quyết vấn đề này. Tôi đã gặp một số khó khăn mà cuối cùng tôi đã giải quyết được.
Hãy cho tôi giải thích vấn đề trước. Dưới đây là dữ liệu của tôi.
Ngày bắt đầu: 15-09-2020 (trong ô B4).
Ngày kết thúc: 15-11-2020 (trong ô C4).
Số tiền cần phân bổ là $500, nằm trong ô D4.
Có nhiều hàng dữ liệu khác. Bây giờ hãy bỏ qua nó. Dựa trên những dữ liệu trên, tôi muốn phân bổ số tiền, tức là $500, vào các cột hàng tháng tháng 9, tháng 10 và tháng 11.
Tất nhiên, tôi có một công thức mảng để phân bổ số tiền đã nêu ở trên trên Google Sheets. Nhưng, tạm thời, hãy quên nó đi để hiểu vấn đề.
Sự phân bổ hàng tháng các số tiền (Hiểu vấn đề)
Chúng ta có thể phân bổ số tiền trên vào các cột hàng tháng theo thủ công như sau trên Google Sheets.
Đầu tiên, chúng ta phải tìm số ngày giữa ngày bắt đầu và ngày kết thúc.
Điều đó khá dễ dàng. Trong ô F4, chúng ta có thể sử dụng công thức sau để tìm số ngày giữa ngày bắt đầu và kết thúc.
=days(C4,B4)+1
Kết quả: 62
Chú ý: Bạn có thể sử dụng công thức =datedif(B4,C4,"D")+1 để thay thế. Tìm hiểu thêm về các hàm này trong hướng dẫn về hàm ngày của tôi.
Sau đó, chia số tiền cần phân bổ cho số ngày trả về ở trên trong ô F4 để có số tiền mỗi ngày.
=D4/F4
Kết quả: 8.06
Đây là phần phức tạp. Dựa trên ngày bắt đầu và ngày kết thúc, chúng ta phải phân bổ số tiền như sau.
- 16 ngày trong tháng 9.
- 31 ngày trong tháng 10.
- 15 ngày trong tháng 11.
Tôi có nghĩa là, trong cột tháng 9, 168.06; cột tháng 10, 318.06 và trong cột tháng 11, 15 * 8.06.
Điều đó có nghĩa là số ngày có thể khác nhau trong tháng bắt đầu và tháng kết thúc. Nó có thể hoặc không phải là cả tháng. Nếu bạn có nhiều hàng dữ liệu khác (xem hình ảnh bên dưới), điều đó sẽ là một cơn đau đầu.
Làm thế nào để giải quyết câu đố này?
Tôi đã thử các công thức khác nhau. Sau một vài lỗi và thử nghiệm, cuối cùng tôi đã viết thành công một công thức mảng để phân bổ số tiền vào các cột hàng tháng trên Google Sheets.
Dưới đây là kết quả của cuộc thí nghiệm của tôi (các giá trị tương ứng với ví dụ trên nằm ở hàng số 4).
Công thức mảng để phân bổ số tiền vào các cột hàng tháng của tôi nằm trong ô H2. Trên ô H2, công thức mảng này trả về kết quả trong phạm vi H2:M6!
Chỉ trong 5 bước, chúng ta có thể viết một công thức mảng để phân bổ số tiền vào các cột hàng tháng như trên trên Google Sheets.
Công thức mảng Google Sheets để phân bổ số tiền vào các cột hàng tháng
Dữ liệu ví dụ và Các bước cơ bản
Với dữ liệu ví dụ, vui lòng tham khảo hình ảnh ở trên. Dữ liệu ví dụ nằm trong các cột B, C, D và bắt đầu từ hàng số 1.
Tính tổng số ngày
Trong ô F2, chèn công thức sau để trả về tổng số ngày giữa ngày bắt đầu và ngày kết thúc.
=ArrayFormula(days(C2:C6,B2:B6)+1)
Kết quả sẽ nằm trong F2:F6. Vì vậy, trước khi chèn công thức, bạn phải đảm bảo rằng F2:F6 là trống.
Chuẩn bị cột hàng tháng
Chúng ta phải sử dụng các ngày cuối tháng để đại diện cho các tháng trong hàng tiêu đề cột tháng (H1:M1).
Nếu nó ở định dạng văn bản, như “Tháng Một, Tháng Hai, …”, thì chúng ta có thể sử dụng thêm một bước nữa để chuyển đổi các giá trị đó thành các ngày cuối tháng. Vì vậy, hãy sử dụng các ngày cuối tháng để đại diện cho mỗi tháng trong hàng tiêu đề.
Hơn nữa, tôi đã chọn các tháng từ tháng Tám 2020 (31/08/2020) đến tháng Một 2021 (31/01/2021). Điều đó vì các ngày bắt đầu và kết thúc của tôi trong các cột B và C đều nằm trong khoảng thời gian đó.
Nhưng bạn có thể chọn từ tháng Một 2020 đến tháng Mười Hai 2020 hoặc theo sở thích của bạn. Công thức của tôi để phân bổ số tiền vào các cột hàng tháng tương thích qua năm. Tạm thời, bạn có thể sử dụng các giá trị được cung cấp trong dữ liệu mẫu của tôi.
Hãy tiến đến bước đầu tiên để viết công thức mảng để phân bổ số tiền vào các cột hàng tháng trên Google Sheets.
Số ngày trong hàng đầu tiên trong Cột Tháng (Bước #1)
Đây là bước chính và phức tạp nhất.
Tất cả bốn bước khác đều tương đối đơn giản. Vì vậy, tôi sẽ chia bước #1 này thành một số bước phụ.
Bước phụ #1
Trước tiên, chúng ta sẽ tạo một hàng thay thế các ngày trong ô H1:M1 như dưới đây. Sau đó, bạn sẽ hiểu mục đích của việc này.
Nếu tháng của H1:M1 = tháng của B2, năm của H1:M1 = năm của B2 và ngày của H1:M1 > ngày của B2, thì thay thế ngày đặc biệt đó trong H1:M1 bằng ngày trong B2, ngược lại thay thế các ngày trong H1:M1 bằng eomonth ($H$1:$M$1,-1)+1.
Công thức EOMONTH ở đây chuyển đổi các ngày cuối tháng trong H1:M1 thành các ngày bắt đầu trong các tháng tương ứng. Tôi có nghĩa là, 31/08/2020 sẽ được chuyển đổi thành 01/08/2020.
=ArrayFormula( if( (month($H$1:$M$1)=month(B2))* (year($H$1:$M$1)=YEAR(B2))* (day($H$1:$M$1)>day(B2))=1, B2, eomonth($H$1:$M$1,-1)+1 ) )
Chúng ta muốn một công thức mảng để phân bổ số tiền vào các cột hàng tháng trên Google Sheets, nhưng công thức trên chỉ bao gồm hàng đầu tiên, phải không?
Đừng lo lắng! Chúng ta sẽ sau đó biến nó thành công thức mà bao gồm tất cả các hàng.
Bước phụ #2
Đây là việc lặp lại công việc trên.
Ở đây có hai thay đổi. Thay vì B2 (ngày bắt đầu), ở đây chúng ta sẽ sử dụng C2 (ngày kết thúc).
Sau đó, thay vì thay thế các ngày trong H1:M1 bằng các ngày cuối tháng, công thức sẽ trả về các ngày trong H1:M1 như cũ.
Có nghĩa là;
Nếu tháng của H1:M1 = tháng của C2, năm của H1:M1 = năm của C2 và ngày của H1:M1 > ngày của C2, thì thay thế ngày cụ thể đó trong H1:M1 bằng ngày trong C2, ngược lại trả lại các ngày trong H1:M1 như cũ.
Tạm thời tôi chèn công thức thứ hai trong ô H8.
=ArrayFormula( if( (month($H$1:$M$1)=month(C2))* (year($H$1:$M$1)=YEAR(C2))* (day($H$1:$M$1)>day(C2))=1, C2, $H$1:$M$1 ) )
Bước phụ #3
Để phân bổ số tiền vào các cột hàng tháng trên Google Sheets, trước tiên, chúng ta phải phân bổ các ngày vào các cột hàng tháng.
Bằng cách trừ kết quả của công thức bước phụ #2 với kết quả của công thức bước phụ #1, chúng ta có thể ‘một phần’ đạt được điều đó.
Để đạt được điều đó hoàn toàn, chúng ta phải chuyển công thức trên thành một công thức mảng bao gồm các ngày bắt đầu và kết thúc trong nhiều hàng.
Công thức chung:
=ArrayFormula((sub_step_2 - sub_step_1)+1)
Công thức theo công thức chung:
=ArrayFormula( ( if( (month($H$1:$M$1)=month(C2:C6))* (year($H$1:$M$1)=YEAR(C2:C6))* (day($H$1:$M$1)>day(C2:C6))=1, C2:C6, $H$1:$M$1 )- if( (month($H$1:$M$1)=month(B2:B6))* (year($H$1:$M$1)=YEAR(B2:B6))* (day($H$1:$M$1)>day(B2:B6))=1, B2:B6, eomonth($H$1:$M$1,-1)+1 ) )+1 )
Thay thế công thức trong ô H2 bằng công thức trên.
Và cũng, xóa công thức hiện có trong ô H8 (vì công thức trên là sự kết hợp của cả hai).
Ghi chú: Nếu bạn thấy một số giá trị khác trong H2:M2 như ngày hoặc giá trị ngày, định dạng dải số từ menu Format > Number > Number.
Công thức trên là chìa khóa để phân bổ số tiền vào các cột hàng tháng trên Google Sheets.
Bây giờ để phân bổ số tiền hàng tháng một cách chính xác, chúng ta có thể áp dụng tư duy logic. Hãy tiến đến đó.
Công thức mảng để trả về số ngày trong Cột Tháng (Bước #2)
Trước hết, hãy làm cho công thức trên bao gồm tất cả các ngày bắt đầu và kết thúc. Để làm được điều đó, thay B2 bằng B2:B6 và C2 bằng C2:C6.
Ở đây, bạn nên chú ý. Khi thay thế, đừng bỏ sót bất kỳ tham chiếu nào.
Tôi đang sử dụng lệnh Tìm kiếm và Thay thế để thay thế nhiều tham chiếu trong các công thức.
Trong Windows, sử dụng phím tắt; Ctrl + H để mở cửa sổ “Tìm kiếm và thay thế” và làm theo hướng dẫn sau (xem hình ảnh bên dưới).
Tương tự, thay C2 bằng C2:C6.
Công thức H2 sẽ trông như sau:
=ArrayFormula( ( if( (month($H$1:$M$1)=month(C2:C6))* (year($H$1:$M$1)=YEAR(C2:C6))* (day($H$1:$M$1)>day(C2:C6))=1, C2:C6, $H$1:$M$1 )- if( (month($H$1:$M$1)=month(B2:B6))* (year($H$1:$M$1)=YEAR(B2:B6))* (day($H$1:$M$1)>day(B2:B6))=1, B2:B6, eomonth($H$1:$M$1,-1)+1 ) )+1 )
Sau đó, làm nổi bật dải H2:M6 và định dạng nó thành số (Format > Number > Number).
Ghi chú: Đối với kết quả, hãy xem hình ảnh bên dưới tiêu đề tiếp theo (dải H2:M6).
Chỉ cần chọn tổng số ngày bằng cách làm nổi bật (chọn) F2:F6. Ở phần chân trang gần nút Khám phá, bạn có thể thấy tổng là 323.
Sau đó, làm nổi bật các ngày được phân bổ trong các cột hàng tháng trong dải H2:M6. Tổng sẽ là 877!
Cả hai giá trị tổng nên khớp, phải không?
Nó không khớp vì công thức trên điền số ngày vào tất cả các cột hàng tháng, điều chúng ta không muốn.
Để phân bổ số tiền vào các cột hàng tháng một cách chính xác, chúng ta muốn loại bỏ các giá trị trong một số cột dựa trên ngày bắt đầu và kết thúc.
Tìm các ô thích hợp để phân bổ số tiền vào các cột hàng tháng (Bước #3)
Sử dụng công thức sau trong ô H9, chúng ta có thể tìm các ô mà chúng ta muốn điền số ngày. Điều đó có nghĩa là xác định các ô cần điền số tiền phân bổ.
=ARRAYFORMULA( (H1:M1>=eomonth(B2:B6,0))* (H1:M1<=eomonth(C2:C6,0)) )
Tôi cho rằng công thức này không cần giải thích nào. Xem hình ảnh.
Chúng ta sẽ thay tất cả các giá trị 1 bằng các ngày từ phạm vi tương ứng trong H2:M6 và các giá trị 0 bằng ô trống.
Đây là thách thức cuối cùng chúng ta phải hoàn thành để phân bổ số tiền vào các cột hàng tháng trên trang tính Google Sheets.
Bạn có thể tìm hiểu thêm trong bước tiếp theo. Thực tế là, bước đơn giản hơn so với bước #1 và bước #2 ở trên.
Phân bổ số ngày theo các cột hàng tháng (Bước #4)
Để phân bổ số ngày theo tháng chính xác vào các cột và ô, hãy tuân thủ công thức chung sau.
=if(kết_quả_công_thức_bước_3=1,kết_quả_công_thức_bước_2,)
Tức là;
=ArrayFormula(if(H9:M13=1,H2:M6,))
Chỉ cần thay thế các dãy ô trong công thức trên bằng các công thức tương ứng. Xóa công thức H2 và H9 hiện có và chèn công thức kết hợp dưới đây vào ô H2. Đó là công thức chung ở trên.
=ArrayFormula( if( (H1:M1>=eomonth(B2:B6,0))* (H1:M1<=eomonth(C2:C6,0))=1, ( if( (month($H$1:$M$1)=month(C2:C6))* (year($H$1:$M$1)=YEAR(C2:C6))* (day($H$1:$M$1)>day(C2:C6))=1, C2:C6, $H$1:$M$1 )- if( (month($H$1:$M$1)=month(B2:B6))* (year($H$1:$M$1)=YEAR(B2:B6))* (day($H$1:$M$1)>day(B2:B6))=1, B2:B6, eomonth($H$1:$M$1,-1)+1 ) )+1, ) )
Công thức cuối cùng để phân bổ số tiền vào các cột hàng tháng trên Google Sheets (Bước #5)
Đây là công thức độc đáo của tôi có thể phân bổ số tiền vào các cột hàng tháng trên nhiều hàng chỉ cần nằm trong ô H2.
Tôi có thể làm cho bước này dễ hơn với một công thức chung.
Công thức chung để phân bổ số tiền vào các cột hàng tháng trên Google Sheets:
=ArrayFormula(D2:D6/F2:F6*H2:M6)
Hoặc chúng ta có thể nói;
=ArrayFormula(D2:D6/F2:F6* kết_quả_công_thức_bước_4)
Tức là số tiền mỗi ngày (số tiền chia cho tổng số ngày) nhân với các ngày được phân bổ theo tháng.
Hãy sửa công thức trong ô H2 như trên và đây là công thức mảng cuối cùng để phân bổ số tiền vào các cột hàng tháng trên Google Sheets.
=ArrayFormula( D2:D6/F2:F6* if( (H1:M1>=eomonth(B2:B6,0))* (H1:M1<=eomonth(C2:C6,0))=1, ( if( (month($H$1:$M$1)=month(C2:C6))* (year($H$1:$M$1)=YEAR(C2:C6))* (day($H$1:$M$1)>day(C2:C6))=1, C2:C6, $H$1:$M$1 )- if( (month($H$1:$M$1)=month(B2:B6))* (year($H$1:$M$1)=YEAR(B2:B6))* (day($H$1:$M$1)>day(B2:B6))=1, B2:B6, eomonth($H$1:$M$1,-1)+1 ) )+1, ) )
Kết luận
Tôi đã sử dụng dải đóng trong các công thức. Tôi có nghĩa là công thức chỉ dành cho các hàng 2 đến 6.
Tôi có thể làm nó dành cho một dải mở để xem xét các ngày bắt đầu tương lai trong các cột B và C được xem xét?
Có thể! Tôi có thể làm điều đó bằng cách thay B2:B6 bằng B2:B và C2:C6 bằng C2:C. Điều đó không đủ! Sử dụng điều kiện sau để tránh sử dụng các hàng trống ở cuối dải.
if(len(D2:D),
Cú pháp:
=ArrayFormula(if(len(D2:D), kết_quả_công_thức_bước_5,
Công thức này bạn có thể tìm thấy trong bảng mẫu của tôi được chia sẻ bên dưới (tên tab là ‘Copy of 5’).
Hơn nữa, trong bảng đó, tôi đã chỉnh sửa H1:M1 thành H1:S1 để bao quát tất cả các tháng trong năm 2020.
Vì vậy, bạn có thể sử dụng bảng đó để phân bổ số tiền vào các cột hàng tháng trên Google Sheets trong suốt một năm. Nếu bạn thêm nhiều cột hơn, hãy thay đổi H1:S1 tương ứng.
Cuối cùng, hãy lưu ý rằng công thức trên sẽ không hỗ trợ loại trừ cuối tuần trong việc phân bổ hàng tháng.
Đó là tất cả. Hãy tận hưởng!
