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 tạo tất cả các ngày nằm giữa hai ngày này trong một cột hoặc một hàng. Trên Google Sheets, bạn có thể tự động tạo ngày giữa hai ngày đã cho bằng một Công thức Mảng.
Tôi sẽ giới thiệu cách mở rộng hai ngày trên Google Sheets.
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.
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 nằm 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 Tạo Ngày Giữa Hai Ngày Xác Định theo Chiều Dọc trên 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 được sử dụng phổ biến hơn, hoặc bạn có thể thấy người dùng Google Sheets có trình độ cao sử dụng giải pháp này. Vì một trong các hàm, tức là SEQUENCE, trong giải pháp thứ hai là một hàm tương đối mới trong Google Sheets.
Hãy chọn một trong hai giải pháp theo ý muốn. Bây giờ chúng ta sẽ đi vào 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:
=ArrayFormula(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 nằm giữa hai ngày đã cho theo chiều dọc trên 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 trong bảng của bạn.
Ý nghĩa của Công Thức/Giải thích
Bạn sẽ làm gì nếu bạn muốn tự động tạo các số serial liên tục (số tuần tự) trong một cột?
Ví dụ, tôi muốn có các số serial từ 1 đến 50 trong một cột. Điều tôi sẽ làm là sử dụng hàm ROW trong một Mảng như dưới đây:
=ArrayFormula(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ó.
Trong đó, giá trị của Row(A1) là 1 và Row(A50) là 50.
Bây giờ hãy xem công thức dưới đây 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ả lại 43191, giá trị của ngày trong ô A2.
Tương tự, trong công thức dưới đây, tôi đề cập đến ô B2:
=row(indirect("A"&B2))
Công thức này sẽ trả về số 43205. Giờ bạn có thể đoán phần còn lại, đúng không?
Hãy xem công thức chính của tôi ở trên, nơi tôi đã sử dụng hai hàm kết hợp Row và Indirect này để tạo các số nằm giữa các số 43191 và 43205.
Đó là các ngày chưa đượ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 thực tế. Đó là tất cả.
Giải pháp trên không phải là giải pháp duy nhất để tự động tạo ngày nằm giữa hai ngày đã cho theo chiều dọc trên Google Sheets. Hãy xem 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 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 hai ngày trong các ô B2 và A2 để trả lại kết quả trong số lượng hàng đó.
columns: 1 (số cột trong kết quả)
start: A2 (bắt đầu từ chuỗi này)
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 theo các bước sau.
- Chọn phạm vi kết quả.
- Đi đến trình đơn Format > Number > Date.
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 đó bất kỳ cách nào cũng yêu cầu ArrayFormula.
Đó là một giải pháp khác để tự động tạo thông tin giữa hai ngày đã cho trên Google Sheets.
Như một ghi chú 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)
Tự Động Tạo Ngày Giữa Hai Ngày Xác Định theo Chiều Ngang trên Google Sheets
Trong một số trường hợp, bạn có thể muốn tạo ngày theo chiều ngang, như trong phần tiêu đề của biểu đồ Gantt. Vậy, chúng ta hãy xem làm thế nào để làm điều đó với hai công thức trên.
Trong công thức đầu tiên, tôi sẽ sử dụng hàm TRANSPOSE để thay đổi hướng của các ngày tự động tạo. Nhưng vị trí của hàm rất quan trọng. Nó phải như sau.
=ArrayFormula(TRANSPOSE(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))))
Trong công thức thứ hai, bạn có thể sử dụng cả Transpose hoặc thay đổi các yếu tố (tham chiếu/biểu thức) một chút. Tôi thích cách làm thứ hai như dưới đây.
=sequence(1,datedif(A2,B2,"D")+1,A2)
Sử Dụng Nâng Cao của Việc Mở Rộng Ngày trên Google Sheets
Bạn đã học cách tự động tạo ngày giữa hai ngày đã cho trên Google Sheets.
Bạn có thể sử dụng các mẹo trên, tức là mở rộng các ngày giữa hai ngày, trong Vlookup trên 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 chưa được sắp xếp, Vlookup chỉ có thể tra cứu 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 tra cứu một ngày nằm giữa hai ngày đã cho.
Vì vậy trong các 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à sao chép các giá trị cột tương ứng để sử dụng trong Vlookup.
Bạn vẫn không hiểu? Xem hướng dẫn Vlookup nâng cao này duy nhất trên Info Inspired.
Cảm ơn bạn đã đọc. Chúc bạn thật vui!
Source: Crawlan.com
