Trong Google Sheets, chúng ta có thể sử dụng một công thức VLOOKUP phức tạp để chèn các hàng bản sao. Tuy nhiên, công thức này không phức tạp nếu bạn tuân thủ theo hướng dẫn này. Tôi có một công thức động mà bạn có thể sử dụng để sao chép một hàng hoặc nhiều hàng nhiều lần.
Tôi biết rằng một số bạn có thể đang tự hỏi tại sao cần một công thức như vậy. Thông thường, chúng ta tìm cách loại bỏ các hàng bị trùng, không phải tạo ra các hàng bản sao trong Google Sheets. Tuy nhiên, việc tạo ra các hàng bản sao không đơn giản chỉ là lặp lại nội dung của một hàng khác. Nó có mục đích riêng.
Dưới đây là một ví dụ thực tế:
Dữ liệu mẫu (Nhấp vào nút dưới đây để nhận bản sao):
Bảng dữ liệu mẫu này hiển thị doanh số của cuốn sách và sách bìa mà tôi đã bán, các cuốn sách này được tải xuống thông qua các kênh trực tuyến khác nhau. Hãy xem xét cận cảnh dữ liệu này.
Trong hàng 2, như bạn có thể thấy, đã bán hai cuốn sách. Cột F hiển thị số lượng đơn bán mỗi ngày. Tôi muốn nhân đôi hàng này nếu số lượng sách bán là hai để tôi có cùng một hàng hai lần.
Trong hàng 3, số lượng sách bán là bốn. Vì vậy, tôi muốn hàng này được lặp lại bốn lần. Tôi kỳ vọng kết quả như dưới đây.
Lợi ích của loại báo cáo này là gì?
Các bạn có thể tạo thêm bảng liệt kê chi tiết hơn và nhóm dữ liệu với đầu ra như vậy.
Làm thế nào để sao chép các hàng như ví dụ trên, có nghĩa là làm thế nào để chèn các hàng bản sao trong Google Sheets dựa trên đầu vào cụ thể? Tôi sẽ giải thích điều đó.
Trước đây, tôi đã đưa ra một số gợi ý về cách loại bỏ các hàng bị trùng trong Google Sheets. Nhưng lần này, tôi sẽ chi tiết cách tạo các hàng bản sao.
Các bước đơn giản để chèn các hàng bản sao trong Google Sheets
Như tôi đã đề cập, tôi sẽ sử dụng VLOOKUP, công thức làm việc với mảng phổ biến nhất trong tất cả các thời gian, cho mục đích này. Đây là cách sử dụng VLOOKUP dựa trên mảng.
Công thức để chèn các hàng bản sao trong Google Sheets:
=ARRAYFORMULA(
VLOOKUP(
TRANSPOSE(SPLIT(QUERY(REPT(ROW(A2:A)&" ",F2:F),,9^9)," ")),
{ROW(A2:A),A2:E},
{1,2,3,4,5}+1,0
)
)Trong ví dụ trên, tôi đã áp dụng công thức này vào ô I2. Dưới đây là hai yếu tố quan trọng bạn cần hiểu về công thức này để bạn có thể sử dụng nó cho mục đích của mình:
- Cách điều chỉnh công thức này cho phạm vi dữ liệu của bạn:
Phạm vi dữ liệu trong dữ liệu mẫu là A2:F. Để điều chỉnh công thức này cho phạm vi dữ liệu của bạn, bạn cần chỉ định cột đầu và cột cuối trong phạm vi dữ liệu của bạn (`A2:A` và `F2:F` ở đây), và phạm vi dữ liệu thực tế cần lặp lại (A2:E ở đây). Bạn cũng cần chỉ định các cột bạn muốn trong kết quả của bạn (như là [1,2,3,4,5]).
- Giải thích về công thức:
Công thức này hơi phức tạp, nhưng nó hoạt động bằng cách tạo ra một chỉ số chuỗi ảo cho mỗi hàng trong phạm vi dữ liệu của bạn. Chỉ số chuỗi ảo này sau đó được sử dụng để tìm kiếm các giá trị tương ứng trong các cột B đến E. Kết quả của các tìm kiếm này sau đó được trả về trong một mảng, mà sau đó được hiển thị trong ô (ngang và dọc) mà công thức được nhập vào.
Tôi sẽ giải thích hai yếu tố này cho bạn một cách chi tiết hơn để bạn có thể nắm bắt cách chèn các hàng bản sao trong Google Sheets của bạn.
Cách tùy chỉnh công thức hàng bản sao trong Google Sheets
Trong công thức trên, bạn chỉ cần chỉnh sửa các tham chiếu của ô:
- `A2:A` (cột đầu tiên)
- `F2:F` (cột xác định số lần lặp lại các hàng)
- `A2:E` (phạm vi lặp lại)
Phạm vi dữ liệu ban đầu của tôi là A2:F (6 cột), không bao gồm hàng tiêu đề.
Công thức này chèn các hàng bản sao trong Google Sheets dựa trên cột F.
Nếu bạn không quen thuộc lắm với việc sửa đổi các công thức, hãy làm một việc: giữ cho cột cuối cùng là cột chứa số lần lặp lại mỗi hàng. Đó là cột `F2:F` trong công thức của tôi. Nếu bạn loại bỏ cột này, phạm vi dữ liệu còn lại là `A2:E`. Đây là những tham chiếu ô bạn nên lưu ý. Thay thế `A2:A` bằng phạm vi của cột đầu tiên.
Nếu bạn có nhiều cột trong dữ liệu của mình, hãy thực hiện các thay đổi sau vào công thức:
- Nếu phạm vi dữ liệu của bạn là `A2:G` (7 cột), hãy thay đổi `A2:E` thành `A2:F` và `F2:F` thành `G2:G` giả sử `G2:G` là phạm vi xác định số lần lặp lại các hàng.
- Thay đổi mảng số chỉ mục từ [1,2,3,4,5] thành [1,2,3,4,5,6].
Bây giờ, bạn có thể sửa đổi công thức trên theo nhu cầu của bạn, tùy thuộc vào số cột trong dữ liệu của bạn.
Cách công thức này tạo ra các hàng bản sao trong Google Sheets
Công thức này là một chức năng VLOOKUP sử dụng mảng.
Cú pháp của VLOOKUP:
`VLOOKUP(search_key, range, index, [is_sorted])`
Trong công thức này, `search_key`, `range` và `index` không chỉ là các tham chiếu ô đơn giản. Chúng là các kết hợp khác nhau của các công thức hoặc mảng.
Bạn có thể thấy điều đó từng cái một.
1. Search_Key
Tôi đã sử dụng công thức này (được in đậm) làm search key trong VLOOKUP:
`=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY(REPT(ROW(A2:A)&” “,F2:F),,9^9),” “)))`
Trong Công thức Gốc, bạn có thể thấy công thức này mà không có ArrayFormula. Tôi đã bọc công thức này bằng ArrayFormula vì điều này yêu cầu khi bạn sử dụng công thức này theo cách độc lập. Trong công thức chính, đã có một ArrayFormula ở đầu. Điều này chăm sóc các công thức khác nhau được lồng trong nó. Công thức này trả về kết quả sau đây.
Chi tiết hơn về Search_Key:
- Vị trí đầu tiên trong phạm vi dữ liệu của tôi là hàng 2. “Đơn vị đã bán” của nó là 2, vì vậy công thức lặp lại hàng 2 hai lần.
- “Đơn vị đã bán” của mặt hàng thứ hai là 4, và nó nằm ở hàng 3. Vì vậy, công thức sẽ lặp lại hàng 3 bốn lần.
- Tương tự, hàng 4 và 5 được lặp lại một lần lẻ.
2. Range
Bây giờ tôi sẽ giải thích “range” trong VLOOKUP. Phạm vi này là phạm vi dữ liệu gốc.
Vì chúng tôi có các số hàng là khóa tìm kiếm, để tìm kiếm các khóa tìm kiếm này trong phạm vi, tôi đã thêm một cột vào phạm vi. Cột này chứa các số hàng thực tế.
`=ARRAYFORMULA({ROW(A2:A),A2:E})`
Kết quả:
Tôi hy vọng bây giờ bạn có thể đoán cách công thức hoạt động. Chúng tôi đã tạo ra các khóa tìm kiếm, lặp lại số hàng hai lần nếu “Đơn vị đã bán” là 2, ba lần nếu “Đơn vị đã bán” là 3, v.v. Vì vậy, VLOOKUP sẽ tìm kiếm các khóa tìm kiếm đó trong cột đầu tiên của “phạm vi” này.
Vì số hàng (hàng số 2) lặp lại hai lần trong khóa tìm kiếm, công thức sẽ tìm kiếm số 2 trong cột đầu tiên trong “phạm vi” và trả về nội dung của số hàng phù hợp hai lần. Công thức sẽ trả về hàng số 3 bốn lần vì nó lặp lại 4 lần trong khóa tìm kiếm và cuối cùng hàng số 4 và 5 mỗi đi một lần.
Kết luận
Mọi người muốn tìm giải pháp để loại bỏ các hàng bị trùng, nhưng đôi khi chúng ta cần chèn các hàng bản sao để có được kết quả mong muốn. Hướng dẫn chi tiết này cho bạn biết cách làm điều đó trong Google Sheets.
Bạn cũng có thể sử dụng chức năng tùy chỉnh của tôi, REPT_ROWS, để chèn các hàng bản sao nếu bạn không thoải mái viết các công thức phức tạp.
Tài liệu tham khảo:
- Làm thế nào để sao chép các hàng dựa trên ngày bắt đầu và ngày kết thúc trong Google Sheets.
- Gán cùng một số thứ tự tuần tự cho các hàng trùng nhau trong danh sách trong Google Sheets.
- Làm thế nào để điền các ngày bị thiếu trong Google Sheets (Phân loại & Tổng quát).
- Mở rộng ngày và gán giá trị trong Google Sheets (Công thức mảng).
