Bạn có hai ngày trong một bảng tính – một ngày bắt đầu và một ngày kết thúc. Bây giờ bạn muốn điền tất cả các ngày giữa hai ngày này vào một cột hoặc dòng. Trong Google Sheets, bạn có thể tự động điền ngày giữa hai ngày đã cho bằng một Công thức Mảng.
Tôi sẽ nói về cách mở rộng hai ngày trong Google Doc Spreadsheets.
Trong ví dụ trên, tôi có một ngày bắt đầu trong ô A2 và một ngày kết thúc trong ô B2.
Chỉ với một công thức duy nhất trong ô D2, tôi đã liệt kê tất cả các ngày giữa hai ngày này, bao gồm cả ngày bắt đầu và ngày kết thúc. Làm thế nào?
Tự động điền ngày giữa hai ngày đã cho theo chiều dọc trong Google Sheets
Tôi có hai công thức kết hợp dựa trên:
- ROW và INDIRECT.
- DAYS/DATEDIF và SEQUENCE.
Giải pháp đầu tiên phổ biến hơn trong việc sử dụng hoặc bạn có thể thấy người dùng Google Sheets nâng cao sử dụng. Bởi vì một trong số các hàm, tức là SEQUENCE, trong bộ đôi thứ hai là một hàm tương đối mới trong Google Sheets.
Hãy chọn công thức bạn muốn. Hãy đi vào các công thức.
1. Công thức ROW và INDIRECT
Dưới đây là công thức đầu tiên của tôi.
=CôngThứcMảng(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))
Bạn có thể sử dụng công thức này để lấy tất cả các ngày giữa hai ngày cho trước theo chiều dọc trong Google Sheets. Trong công thức này, A2 chứa ngày bắt đầu và B2 chứa ngày kết thúc.
Chỉ cần thay đổi các tham chiếu ô này theo ngày bắt đầu và ngày kết thúc của bạn trong bảng tính của bạn.
Cách hoạt động của công thức/ Giải thích công thức
Bạn sẽ làm gì nếu bạn muốn tự động điền các số liên tục (số tuần tự) trong một cột?
Ví dụ, tôi muốn các số tuần tự từ 1 đến 50 trong một cột. Những gì tôi sẽ làm là sử dụng hàm ROW trong một Mảng như bên dưới.
=CôngThứcMảng(row(A1:A50))
Công thức ROW này sẽ liệt kê các số từ 1 đến 50 trong cột mà bạn áp dụng nó.
Ở đây, giá trị của Row(A1) là 1 và Row(A50) là 50.
Bây giờ hãy xem công thức bên dưới sử dụng hàm Indirect.
=row(indirect("A"&A2))
Nếu bạn sử dụng công thức này, nó sẽ trả về 43191, giá trị của ngày trong ô A2.
Theo đúng với thứ tự trên, tôi đang tham chiếu đến ô B2 trong công thức dưới đây.
=row(indirect("A"&B2))
Công thức này sẽ trả về số 43205. Bây giờ bạn có thể đoán phần còn lại, đúng không?
Xem công thức chính của tôi ở trên, trong đó tôi đã sử dụng hai hàm kết hợp Row và Indirect này để điền số từ 43191 đến 43205.
Đó là các ngày không được định dạng từ 01/04/2018 đến 15/04/2018. Với sự trợ giúp của hàm To_Date, tôi đã chuyển đổi các giá trị Ngày này thành các ngày. Đó là tất cả.
Trên đây không phải là giải pháp duy nhất để tự động điền các ngày giữa hai ngày cho trước theo chiều dọc trong Google Sheets. Hãy đi vào một công thức khác.
2. Công thức DAYS/DATEDIF và SEQUENCE
Dưới đây là công thức để sử dụng.
=sequence(days(B2,A2)+1,1,A2)
Hãy để tôi giải thích công thức này ngắn gọn với cú pháp của nó, tức là SEQUENCE(rows, columns, start).
rows: Số ngày giữa các ngày trong ô B2 và A2 để trả lại kết quả trong số hàng đó.
columns: 1 (số cột trong kết quả)
start: A2 (chuỗi bắt đầu từ)
Kết quả của công thức trên sẽ là các giá trị ngày (định dạng số). Để có định dạng ngày, làm như sau.
- Chọn phạm vi kết quả.
- Đi đến menu Định dạng > Số > Ngày.
Tôi có thể sử dụng hàm To_date với công thức này để định dạng kết quả tự động từ số thành ngày. Nhưng tôi đã chọn không sử dụng nó vì nó yêu cầu ArrayFormula để mở rộng.
Trong công thức số 1, tôi đã sử dụng To_date vì hàm Row trong công thức đó bất kể phái yêu cầu ArrayFormula.
Đó là một giải pháp khác để tự động điền thông tin giữa hai ngày cho trước trong Google Sheets.
Như lưu ý phụ, nếu bạn muốn thay Days bằng Datedif, hãy sử dụng công thức dưới đây.
=sequence(datedif(A2,B2,"D")+1,1,A2)
Hãy kiểm tra: Học các hàm Ngày đầy đủ của Google Sheets
Cách tự động điền ngày giữa hai ngày cho trước theo chiều ngang trong Google Sheets
Trong một số trường hợp, bạn có thể muốn điền các ngày theo chiều ngang, chẳng hạn như trong tiêu đề trên một vùng thanh Gantt chart. Vậy hãy xem cách làm điều đó với hai công thức trên.
Liên quan: Công thức Mảng để Tạo ngày hai tháng trong Google Sheets.
Trong công thức đầu tiên, tôi sẽ sử dụng hàm TRANSPOSE để thay đổi hướng điền ngày tự động. Nhưng cách đặt hàm này là quan trọng. Nó phải như sau.
=CôngThứcMảng(TRANSPOSE(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))))
Trong công thức thứ hai, bạn có thể sử dụng Transpose hoặc thay đổi các phần tử (tham chiếu/biểu thức) một chút. Tôi sẽ ưu tiên cái sau như sau.
=CôngThứcMảng(sequence(1,datedif(A2,B2,"D")+1,A2))
Sử dụng nâng cao của việc mở rộng các ngày trong Google Sheets
Bạn đã học cách tự động điền các ngày giữa hai ngày cho trước trong Google Sheets.
Bạn có thể sử dụng những mẹo trên, tức là mở rộng ngày giữa hai ngày, trong Vlookup trong Google Sheets để khắc phục một trong nhược điểm của nó! Đó là gì?
Trong Google Sheets, trong một cột ngày không được sắp xếp, Vlookup chỉ có thể tìm kiếm các ngày ‘có sẵn vật lý’ trong các ô.
Bạn không thể cung cấp hai ngày và buộc Vlookup tìm một ngày nằm giữa hai ngày đã cung cấp.
Vì vậy, trong những trường hợp như vậy, bạn có thể xem xét mở rộng các ngày bắt đầu và kết thúc như trên và nhân bản các giá trị cột tương ứng để sử dụng trong Vlookup.
Vẫn không hiểu? Xem hướng dẫn Vlookup nâng cao này độc quyền trên Info Inspired.
Cảm ơn đã ở lại. Thưởng thức!
