Để nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets, chúng ta sẽ sử dụng chức năng REDUCE như một công cụ chính. Cách thức hoạt động của nó tương tự như việc điền vào các ngày bị thiếu.
Lợi ích chính của phương pháp REDUCE này nằm trong khả năng làm việc với mảng, loại bỏ sự cần thiết của các ô trợ giúp, cột hoặc hàng bổ sung.
Tuy nhiên, điều cần lưu ý trước khi sử dụng là chức năng REDUCE có thể gây giảm hiệu suất hoặc dừng hoàn toàn khi làm việc với lượng dữ liệu lớn.
Hãy nhớ giới hạn này khi tự động nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets.
Nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets
Để kiểm tra, chúng ta sẽ sử dụng dữ liệu mẫu trong phạm vi A1:F4 như sau (A1:F1 chứa nhãn trường).
Dữ liệu này bao gồm các cột cho Mục, Số lượng, Từ, Đến, Điểm đến và Trạng thái, tương ứng với các cột A, B, C, D, E và F. Nó đại diện cho tình trạng cung ứng vật liệu.
Đầu tiên, hãy tìm hiểu về công thức và hiểu cách thích ứng nó với bảng của bạn với cách bố trí khác. Sau đó, chúng ta sẽ chuyển sang phần giải thích công thức hấp dẫn.
=ArrayFormula(REDUCE( TOCOL(,1), C2:C4, LAMBDA(a, v, VSTACK(a, LET( seq, SEQUENCE(OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1, 1, OFFSET(v, 0, 0)), lkp, IF(seq, ROW(v)), fnl, HSTACK( VLOOKUP( lkp, HSTACK(ROW(C2:C4), A2:F4), SEQUENCE(1, COLUMNS(A2:F4), 2) ), seq ), CHOOSECOLS(fnl, 1, 2, 7, 5, 6)) ) ) ))
Lưu ý: Công thức này có trong ô A8 trong ảnh chụp ở trên. Hơn nữa, hãy định dạng cột ngày (trong trường hợp này, phạm vi C8:C16) thành định dạng ngày bằng cách áp dụng Format > Number > Date.
Thích nghi công thức với phạm vi bảng khác
Trong công thức này, C2:C4 đại diện cho phạm vi ngày bắt đầu và A2:F4 đại diện cho phạm vi bảng hoàn chỉnh.
Bạn cần chỉ định các tham chiếu phạm vi này, tức là phạm vi ngày bắt đầu và phạm vi bảng hoàn chỉnh. Công thức giả định rằng phạm vi ngày kết thúc là cột liền kề với ngày bắt đầu và sử dụng thông tin đó.
Ngoài ra, bạn cần thay đổi một lần nữa trong công thức, cụ thể là phần cuối: CHOOSECOLS(fnl, 1, 2, 7, 5, 6).
Trong ngữ cảnh này, 1, 2, 5 và 6 đề cập đến các cột tương ứng trong phạm vi bảng A2:F4. Các cột 3 và 4 không cần được chỉ định vì chúng đại diện cho cột ngày bắt đầu và kết thúc.
Bảng bao gồm tổng cộng 6 cột. Cột thứ 7 đại diện cho cột ngày mở rộng; tôi đã thay thế 3 và 4 bằng 7.
Tóm lại, các cột thực tế là CHOOSECOLS(fnl, 1, 2, 3, 4, 5, 6), nhưng chúng ta đã sử dụng CHOOSECOLS(fnl, 1, 2, 7, 5, 6)
Bây giờ, tôi hy vọng bạn có thể dễ dàng nhân bản các hàng dựa trên các ngày bắt đầu và kết thúc tương ứng trong Google Sheets.
Logic và phân tích công thức nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc
Chúng tôi đã sử dụng hàm REDUCE để nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc, sử dụng một hàm lambda với ba thành phần chính.
Logic công thức:
Công thức bao gồm ba thành phần: SEQUENCE và IF logical, VLOOKUP và CHOOSECOLS.
Trong phần đầu tiên, mục tiêu là mở rộng các ngày bắt đầu và kết thúc của bản ghi đầu tiên (hàng) thành một cột duy nhất và trả về các số hàng tương ứng.
Ví dụ, nếu ngày bắt đầu trong ô C2 là 01 Tháng 1, 2024 và ngày kết thúc trong ô D2 là 05 Tháng 1, 2024, việc mở rộng sẽ cho ra các ngày 01/01, 02/01, 03/01, 04/01 và 05/01.
Công thức trả về các số hàng (ví dụ: 2) lặp lại 5 lần trong một cột và các ngày từ 01 đến 05 trong một cột khác.
Những số hàng này được sử dụng làm khóa tìm kiếm trong VLOOKUP để lấy giá trị từ các hàng tương ứng trong bảng A2:F4. Kết quả được nối ngang với các ngày đã mở rộng bởi phần đầu tiên, tạo thành một bảng 7 cột.
Phần thứ ba liên quan đến sắp xếp lại các cột được trả về bởi VLOOKUP, cùng với các ngày đã được nối vào.
Kết quả cuối cùng của mỗi lần lặp (mỗi lần mở rộng) được xếp chồng chất dọc sử dụng bộ nhớ tạm, và đó là kết quả cuối cùng.
Logic này là cơ sở của công thức nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets. Hãy tiếp tục với phân tích chi tiết của công thức.
Phân tích công thức:
REDUCE(TOCOL(,1), C2:C4, LAMBDA(a, v, ..
Hàm REDUCE lặp qua từng phần tử trong mảng C2:C4 (cột ngày bắt đầu) và thực hiện một hàm lambda. Nó lấy một giá trị ban đầu (TOCOL(,1)) và một mảng (C2:C4).
Cú pháp: REDUCE(giá_trị_ban_đầu, mảng_điều_kiện, hàm_lambda)
Giá trị ban đầu TOCOL(,1), chính là một hàm TOCOL, đại diện cho một ô trống, chỉ dẫn nó bỏ qua các ô trống, từ đó ngăn REDUCE để lại một ô trống ở đầu cột kết quả.
Trong hàm lambda, “a” là giá trị ban đầu trong bộ nhớ tạm, và “v” là phần tử hiện tại trong mảng. Hàm lambda bắt đầu bằng VSTACK(a, chỉ ra rằng REDUCE xếp chồng kết quả trong mỗi lần lặp hiện tại theo chiều dọc.
Dưới đây là giải thích về hàm lambda được sử dụng trong REDUCE để mở rộng các ngày dựa trên ngày bắt đầu và kết thúc, một phần quan trọng trong việc nhân bản các bản ghi.
1. Phần SEQUENCE và IF Logical:
Hãy xem xét điều gì mà hàm lambda thực hiện với phần tử đầu tiên trong mảng, tức là giá trị trong ô C2.
SEQUENCE(OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1, 1, OFFSET(v, 0, 0))
Trong đó:
- Số hàng: OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1 bằng end_date – start_date + 1.
- Số cột: 1.
- Giá trị bắt đầu: OFFSET(v, 0, 0), đại diện cho ngày bắt đầu.
- Bước: bỏ qua.
Điều này tương ứng với cú pháp SEQUENCE:
SEQUENCE(số_hàng, [số_cột], [giá_trị_bắt_đầu], [bước])
Công thức trên mở rộng các ngày bắt đầu và kết thúc. Chúng tôi sử dụng hàm LET để đặt tên biểu thức giá trị này là “seq”.
IF(seq, ROW(v))
Phần IF này trả về các số hàng tương ứng với “seq”. Hàm LET gán tên “lkp” cho giá trị này, có nghĩa là giá trị tìm kiếm.
2. Phần VLOOKUP:
VLOOKUP(lkp, HSTACK(ROW(C2:C4), A2:F4), SEQUENCE(1, COLUMNS(A2:F4), 2))
Hàm VLOOKUP tìm kiếm “lkp”, tức là số hàng đã mở rộng của ngày bắt đầu và ngày kết thúc, trong phạm vi HSTACK(ROW(C2:C4), A2:F4). Nó trả về tất cả các bản ghi khớp trong tất cả các cột trừ cột đầu tiên. Kết quả được xếp chồng ngang với các ngày đã mở rộng.
Kết quả được gán tên “fnl” trong hàm LET.
3. Phần CHOOSECOLS:
CHOOSECOLS(fnl, 1, 2, 7, 5, 6)
Phần CHOOSECOLS này chọn các cột trừ cột ngày bắt đầu và kết thúc. Thay vào đó, nó chọn cột đã được mở rộng.
Đây là phần giải thích công thức được thiết kế để nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets.
Lợi ích của việc nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets
Nhân bản các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets cung cấp nhiều lợi ích. Dưới đây là những lợi ích quan trọng nhất.
Phân tích dữ liệu tập trung:
Lọc các ngày cho phép xem tập trung dữ liệu vào các ngày cụ thể. Các công thức như MONTH, YEAR, WEEKNUM hoặc week range trong một cột trợ giúp giới hạn dữ liệu thành các năm cụ thể, các tháng, các tuần hoặc các khoảng ngày tùy chỉnh.
Tích hợp dữ liệu với Pivot Tables:
Dữ liệu đã nhân bản có thể được tổng hợp một cách hiệu quả bằng cách sử dụng Pivot Tables trong Google Sheets. Điều này cho phép tổng hợp theo ngày, tháng, năm-tháng, quý và các khoảng thời gian tùy chỉnh khác.
Tiết kiệm thời gian nhập dữ liệu:
Nhập dữ liệu dựa trên ngày bắt đầu và ngày kết thúc tiết kiệm thời gian, và các công thức có thể được áp dụng để tự động mở rộng tập dữ liệu. Điều này đơn giản hóa quy trình nhập dữ liệu và giảm nguy cơ xảy ra lỗi.
Đây là một số lợi ích ngay lập tức của việc nhân bản các bản ghi dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets, mang lại sự tập trung dữ liệu, khả năng phân tích và hiệu suất trong quy trình nhập dữ liệu.
Kết luận
Trong bài hướng dẫn này, chúng tôi đã sử dụng VLOOKUP với REDUCE để nhân bản các bản ghi dựa trên ngày bắt đầu và ngày kết thúc. Một cách khác, chúng ta có thể loại bỏ việc sử dụng VLOOKUP và thay vào đó phụ thuộc vào OFFSET cho cùng một mục đích.
Tuy nhiên, điều quan trọng cần lưu ý là việc sử dụng OFFSET có thể ảnh hưởng đến hiệu suất của công thức. Chúng tôi đã hạn chế việc sử dụng OFFSET chỉ để lấy ngày bắt đầu và kết thúc.
Giới hạn này là cần thiết vì REDUCE sẽ không xử lý hai mảng cùng một lúc, ngăn chúng ta không thể chỉ định cả ngày bắt đầu và ngày kết thúc trong cùng một hàm.
Tài liệu:
