Để tự động điền các ngày liên tiếp trong Google Sheets, chúng ta có thể dựa vào một công thức rất đơn giản hoặc công cụ fill handle trong Google Sheets.
Fill handle hoạt động như sau. Bạn nhập một ngày trong một ô và ngày khác trong ô kề cạnh nó.
Ví dụ, nhập ngày 01 Tháng 11, 2019 trong ô A1 và ngày 02 Tháng 11, 2019 trong ô A2. Để tự động điền các ngày liên tiếp, chọn hai ô này và nhấp đúp chuột vào fill handle.
Điều này sẽ điền ngày cho đến hàng cuối cùng chứa giá trị trong cột kế tiếp.
Việc tự động điền thủ công các ngày liên tiếp trong Google Sheets có một vấn đề. Nếu bạn nhập thêm các giá trị trong cột B, bạn sẽ cần lặp lại thao tác trên.
Ví dụ, tôi đã nhập các giá trị trong các ô B15, B16 và B17. Để mở rộng các ngày liên tiếp đến hàng số 17, tôi phải chọn các ngày trong ô A13 và A14 và nhấp đúp chuột vào fill handle một lần nữa.
Chúng ta có thể tự động hóa việc này bằng cách sử dụng một công thức. Nghĩa là chúng ta có thể viết một công thức trong ô A1 để tự động điền các ngày liên tiếp cho đến hàng không trống cuối cùng trong cột kề cạnh.
Công Thức Mảng để Tự Động Điền Các Ngày Liên Tiếp Dựa Trên Cột Kế Bên Trong Google Sheets
Đầu tiên, hãy cho tôi cho bạn thấy điều tôi đang nói đến.
Dưới đây là công thức mảng mà tôi đã sử dụng trong ô A1 trong ví dụ trên (xem GIF).
=sequence(counta(B1:B),1,date(2019,11,1))
Công thức này có thể trả về các số (giá trị ngày) thay vì các ngày chính xác. Vì vậy, trước khi nhập công thức, hãy chọn cột A và định dạng nó thành ngày tháng.
Định dạng ngày tháng nằm trong menu Format > Number > Date.
Công Thức Làm Việc Như Thế Nào?
Ngày trong Google Sheets hoặc bất kỳ bảng tính nào khác thực chất là được ghi nhận dưới dạng số. Để tìm số ẩn của các ngày, bạn có thể sử dụng hàm DATEVALUE. Ví dụ, xem công thức dưới đây.
=DATEVALUE(today())
Điều đó có nghĩa là chúng ta có thể sử dụng các ngày trong hàm SEQUENCE để trả về một lưới các ngày liên tiếp.
Cú pháp:
SEQUENCE(rows, [columns], [start], [step])
Từ cú pháp này, bạn có thể hiểu một điều mà không cần giải thích của tôi và đó là đối số ‘start’. Đó là để thay thế ‘start’ bằng ngày bắt đầu trong chuỗi.
Trong công thức của tôi, đó là ngày 01 Tháng 11, 2019, được sử dụng như date(2019,11,1). Vì vậy, bạn sẽ không gặp bất kỳ vấn đề nào liên quan đến cài đặt ngày khu vực như mm/dd/yyyy hoặc dd/mm/yyyy.
Chúng ta muốn tự động điền các ngày liên tiếp trong chỉ một cột. Vì vậy, sử dụng 1 làm ‘columns’. Các đối số còn lại là ‘rows’ và ‘step’.
Chúng ta có thể tìm số hàng cần điền bằng các ngày liên tiếp bằng cách sử dụng đối số ‘rows’. Tôi đã sử dụng counta(B1:B), đếm các giá trị trong cột B.
Nếu có 3 hàng đã điền giá trị trong cột B, công thức Sequential sẽ trả về các ngày liên tiếp trong ba hàng. Điều này bởi vì COUNTA sẽ cung cấp ‘rows’ với số 3.
Giá trị ‘step’ không hữu ích cho các ngày hoặc số liên tiếp. Để hiểu đối số này, sửa lại công thức của tôi như dưới đây để bao gồm giá trị ‘step’ là 2.
=sequence(counta(B1:B),1,date(2019,11,1),2)
Kết quả sẽ là các ngày xen kẽ như 01 Tháng 11 19, 03 Tháng 11 19, 05 Tháng 11 19 và cứ tiếp tục.
Tự Động Điền Các Ngày Liên Tiếp Trừ Các Hàng Ẩn/Được Lọc Ra Trong Google Sheets
Nếu bạn muốn, bạn có thể tự động điền các ngày liên tiếp chỉ trong các hàng hiển thị.
Những Điều Cần Biết Trước Khi Bắt Đầu
Trong Google Sheets, để loại trừ các hàng bị ẩn (hoặc các hàng bị lọc ra) trong các công thức, theo như tôi biết, chúng ta có thể cần một cột trợ giúp.
Tất nhiên, bạn có thể khắc phục giới hạn này theo một cách giới hạn với một cột trợ giúp ảo mà tôi đã thử nghiệm trong một trong các hướng dẫn trước đây của mình tại đây – Xử lý hàng ẩn của Google Sheets Query với cột trợ giúp ảo.
Nó chỉ hoạt động trong một phạm vi dữ liệu giới hạn. Vì vậy, tôi sẽ sử dụng một cột trợ giúp thay vì một cột trợ giúp ảo ở đây.
Ở ví dụ của chúng tôi, cột C là cột trợ giúp. Nhập công thức tương đương với COUNTA này trong ô C1 và sao chép-dán đến hàng cuối cùng trong cột đó. Chúng ta có thể ẩn cột này vào bất kỳ thời điểm nào.
=subtotal(103,B1)
Theo liên quan: Tổng bộ hạng Hàm Với Điều Kiện Trong Excel và Google Sheets.
Chức năng số 103 trong Subtotal hoạt động như COUNTA tương đương. Nhưng hàm này có một ưu điểm so với COUNTA. Đó là gì?
Subtotal Counta sẽ trả về 0 nếu hàng chứa công thức này bị ẩn/được lọc ra.
Ví dụ, nếu bạn ẩn hàng thứ 3, giá trị (số lượng) trong ô C3 sẽ là 0.
Các Bước Thực Hiện
Trong ví dụ đầu tiên của chúng tôi, công thức Sequence được nhập trong ô A1. Cắt công thức đó và dán vào ô F1.
Sau đó, thay thế COUNTA trong công thức Sequence đó bằng COUNTIF sau đây.
=countif(C1:C,">0")
Tại sao lại như vậy?
Lưu ý: Để tìm hiểu về các chức năng Sheets, vui lòng tham khảo Hướng Dẫn chức năng Google Sheets của tôi.
Điều đó là vì Counta trả về số lượng các hàng không rỗng dựa trên các giá trị trong cột B trong khi Countif trả về số lượng các hàng không rỗng trừ các hàng ẩn dựa trên các giá trị trong cột B.
Vì vậy, công thức trong ô F1 để tự động điền ngày trong các hàng hiển thị sẽ như sau.
=sequence(countif(C1:C,">0"),1,date(2019,11,1),2)
Công thức này sẽ không hoạt động như mong đợi. Chúng ta nên thực hiện thêm một số thay đổi.
Trong ô E1, nhập công thức dưới đây để trả về các số hàng không bị ẩn. Tôi sẽ bỏ qua việc giải thích công thức này vì nó có thể làm bạn bối rối. Hãy chỉ đơn giản sử dụng nó.
=filter(row(indirect("A1:A"&counta(B1:B))),indirect("C1:C"&counta(B1:B))>0)
Bây giờ bảng của tôi sẽ trông như sau.
Trong ô A1, tôi sẽ viết một công thức Vlookup sẽ sử dụng E1:F trên làm phạm vi.
Cú pháp:
VLOOKUP(search_key, range, index, [is_sorted])
Các search_key(s) sẽ là các số hàng và chỉ số sẽ là cột thứ hai trong phạm vi đó là các giá trị trong cột F.
=ArrayFormula(IFNA(vlookup(Row(A1:A),E1:F,2,0)))
Chúng ta có thể thay thế E1:F bằng hai công thức tương ứng trong E1 và F1 như dưới đây.
={filter(row(indirect("A1:A"&counta(B1:B))),indirect("C1:C"&counta(B1:B))>0),sequence(countif(C1:C,">0"),1,date(2019,11,1))}
Công thức Vlookup cuối cùng trong ô A1 với công thức trên là phạm vi.
=ArrayFormula(IFNA(vlookup(Row(A1:A),{filter(row(indirect("A1:A"&counta(B1:B))),indirect("C1:C"&counta(B1:B))>0),sequence(countif(C1:C,">0"),1,date(2019,11,1))},2,0)))
Bây giờ chúng ta đã sẵn sàng để thử công thức. Ẩn bất kỳ hàng nào và xem ngày điều chỉnh tương ứng.
Trong hướng dẫn này, bạn đã học hai mẹo.
- Tự động điền các ngày liên tiếp khi có giá trị nhập trong cột kế bên.
- Các ngày liên tiếp trừ các hàng ẩn/được lọc ra, cộng với điểm số 1 ở trên.
Cảm ơn bạn đã ở lại. Chúc bạn vui vẻ!
Để biết thêm thông tin, hãy truy cập Crawlan.com.
