Làm thế nào chúng ta có thể mở rộng ngày, như ngày bắt đầu và ngày kết thúc dự án, và gán giá trị, chẳng hạn như tên nhiệm vụ (cho một dự án), trong Google Sheets?
Chúng ta có thể sử dụng công thức SEQUENCE (để mở rộng ngày) và VLOOKUP (để gán giá trị) trong Google Sheets.
Lưu ý: Cách tiếp cận dưới đây sử dụng METRICS TEMPLATES, một tính năng google sheet mới cho lập kế hoạch và theo dõi các mục tiêu thanh toán và chất lượng trong công việc SEO. Xem chi tiết tại Crawlan.com.
Cách tiếp cận truyền thống
Để mở rộng ngày và gán giá trị trong Google Sheets, bạn có thể làm theo các bước sau:
1. Tìm ngày nhỏ nhất và lớn nhất:
Đầu tiên, hãy tính giá trị ngày nhỏ nhất và lớn nhất trong phạm vi A1:B bằng cách nhập các công thức sau:
Trong ô G2: = MIN (A2: B)
Trong ô H2: = MAX (A2: B)2. Mở rộng các ngày nhỏ nhất và lớn nhất:
Tiếp theo, tạo danh sách các ngày tuần tự (giá trị ngày) từ ngày nhỏ nhất (G2) đến ngày lớn nhất (H2), bao gồm cả 2 ngày này:
Trong ô I2: = SEQUENCE (DAYS (H2, G2) + 1, 1, G2)Sau đó, chọn phạm vi I2:I và áp dụng Format > Number > Date.
3. Bảng trợ giúp từ Dữ liệu nguồn:
Tiếp theo, làm phẳng dữ liệu trong A2:B4 vào các cột K2:K, và sao chép các nhiệm vụ tương ứng vào cột L2:L.
Chèn một hàng mới sau mỗi nhiệm vụ và nhập các ngày mà ngày kết thúc phải lớn hơn ngày trước đó + 1. Xem ảnh dưới đây để hiểu rõ hơn:
4. Tra cứu dọc:
Chèn công thức VLOOKUP sau vào ô J2.
=ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))Với bước này, chúng ta đã mở rộng ngày và gán giá trị trong Google Sheets.
Bây giờ, hãy kết hợp các công thức và loại bỏ bất kỳ hàng không mong muốn nào từ kết quả.
5. Kết hợp Các bước 1, 2 và 4 Công thức:
Dưới đây là các bước để kết hợp các công thức từ các Bước 1, 2 và 4.
Trước tiên, kết hợp Bước 2 (I2) và Bước 4 (J2) như sau trong ô M2 (bỏ qua bất kỳ lỗi có thể có):
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))}Sau đó, chỉnh sửa công thức này và thay thế các tham chiếu ô H2 và G2 bằng các công thức từ các ô tương ứng:
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))}Thay đổi cuối cùng cho công thức này. Thay thế I2:I bằng công thức I2 (Bước 2):
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(H2, G2)+1, 1, G2), K2:L, 2, 1))}Một lần nữa, chỉnh sửa nó để thay thế các tham chiếu ô H2 và G2 còn lại bằng các công thức tương ứng:
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), K2:L, 2, 1))}Cuối cùng, sử dụng công thức QUERY để loại bỏ (lọc ra) các hàng không mong muốn (các hàng không chứa nhiệm vụ trong cột thứ hai):
=QUERY({SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), K2:L, 2, 1))},"SELECT * WHERE Col2 <> ''")Đừng quên xóa giá trị trong các cột G đến J. Giữ bảng trợ giúp (K2:L) như thế.
Cách tiếp cận mới sử dụng công thức LAMBDA
Vui lòng cuộn lên và xem hình ảnh số 1 để xem dữ liệu mẫu trong các ô A1:C1.
Dưới đây, bạn sẽ tìm thấy một phương pháp mới không sử dụng bảng trợ giúp để mở rộng ngày và gán giá trị trong Google Sheets. Tôi khuyến nghị sử dụng giải pháp này thay vì phương pháp trước.
Để triển khai phương pháp mới này, hãy nhập công thức dựa trên LAMBDA sau vào ô E2 sau khi xóa E2:F:
=ArrayFormula(SPLIT(TOCOL(MAP(A2:A, B2:B, C2:C, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))), 1), "|"))Chọn E2:E và áp dụng Format > Number > Date.
Để biết ví dụ minh họa, vui lòng tham khảo tab ‘Lambda Eg 1’ trong bảng mẫu của tôi.
Cấu trúc của Công thức và Logic
Giữ lại chỉ dữ liệu mẫu trong các ô A1:C trong Bảng của bạn. Hãy phân rã công thức bằng từng bước.
Ban đầu, mở rộng hai ngày, ví dụ A2 và B2, bằng cách sử dụng công thức SEQUENCE là dễ dàng. Thách thức xuất hiện khi nhân bản phép toán này trên mỗi hàng mà không cần kéo công thức theo cách thủ công.
Đặt công thức sau vào ô D1 (nó trả về các giá trị ngày; bỏ qua cách định dạng):
=IF(A2*B2, SEQUENCE(1, B2-A2+1, A2),)Để lặp lại công thức này trên mỗi hàng trong phạm vi A2:C4, sử dụng Hàm Trợ giúp LAMBDA MAP (LHF). Trong ô D1, nhập:
=MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a),)))Gán nhiệm vụ cho mỗi ngày trong kết quả bằng cách chỉnh sửa nó như sau:
=ArrayFormula(MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))))Đảm bảo rằng nó được sử dụng như một công thức mảng.
Làm phẳng kết quả (nhiều cột thành một cột) bằng cách sử dụng hàm TOCOL, cũng làm lọc các hàng trống trong một bước:
=ArrayFormula(TOCOL(MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))), 1))Chia kết quả thành hai cột tại dấu phân cách “|” . Thay thế các phạm vi A2:A4, B2:B4 và C2:C4 trong công thức, lần lượt, bằng các phạm vi A2:A, B2:B và C2:C.
Bảng ví dụ
