Cách Lặp Lại Hàng N Lần Trong Excel Bằng Power Query: Hướng Dẫn Chi Tiết

Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính

Tự động hóa quy trình: Power Query giúp lặp lại dữ liệu hàng loạt mà không cần sao chép thủ công (Copy-Paste).
Linh hoạt tối đa: Hỗ trợ lặp lại theo số lượng cố định hoặc dựa trên giá trị động từ một cột cụ thể.
Cú pháp List trong M-Code: Sử dụng công thức đơn giản {1..n} để tạo danh sách lặp.
Khả năng cập nhật: Dữ liệu đầu ra tự động làm mới khi bảng gốc thay đổi chỉ với một cú nhấp chuột.
Không cần VBA: Giải pháp an toàn, dễ quản lý hơn so với việc viết mã Macro phức tạp.

Việc lặp lại các hàng dữ liệu trong Excel là một yêu cầu phổ biến trong xử lý dữ liệu, đặc biệt khi bạn cần chuẩn bị dữ liệu cho việc in nhãn, mở rộng danh sách tồn kho, hoặc chuẩn hóa dữ liệu cho các hệ thống phân tích. Nếu bạn đang thực hiện công việc này bằng cách sao chép và dán thủ công (Ctrl+C / Ctrl+V), bạn đang lãng phí thời gian quý báu và dễ gặp phải sai sót.

Power Query, công cụ chuyển đổi và kết nối dữ liệu mạnh mẽ tích hợp sẵn trong Excel, cung cấp giải pháp tối ưu cho vấn đề này. Bài viết này sẽ hướng dẫn bạn hai phương pháp chính để lặp lại hàng: lặp lại tất cả các hàng một số lần cố định và lặp lại dựa trên giá trị riêng biệt của từng hàng.

1. Lặp Lại Tất Cả Các Hàng N Lần (Số Lượng Cố Định)

Trong kịch bản này, chúng ta có một tập dữ liệu về các bộ phim có doanh thu cao nhất mọi thời đại. Mục tiêu là lặp lại mỗi dòng dữ liệu này chính xác 5 lần. Phương pháp này hữu ích khi bạn cần nhân bản dữ liệu mẫu hoặc tạo các bản ghi giữ chỗ (placeholder) cho quy trình kiểm thử.

Bộ dữ liệu các phim có doanh thu cao nhất để thực hành lặp lại hàng

Lưu ý: Mặc dù hướng dẫn này tập trung vào việc lặp lại toàn bộ hàng, bạn hoàn toàn có thể áp dụng nguyên lý tương tự để lặp lại giá trị của một ô cụ thể trong cột.

Bước 1: Chuyển Đổi Dữ Liệu Sang Excel Table

Power Query hoạt động tốt nhất và ổn định nhất khi nguồn dữ liệu là một Excel Table (Bảng). Nếu dữ liệu của bạn đang ở dạng vùng (Range) thông thường, hãy thực hiện các thao tác sau:

Nhấp chọn bất kỳ ô nào nằm trong vùng dữ liệu của bạn.
Nhấn tổ hợp phím Ctrl + T trên bàn phím.
Hộp thoại Create Table sẽ xuất hiện. Hãy kiểm tra kỹ vùng dữ liệu được chọn tự động.
Đánh dấu chọn vào ô “My table has headers” nếu dữ liệu của bạn đã có dòng tiêu đề.

Hộp thoại tạo bảng Create Table trong Excel

Nhấn OK.

Sau khi chuyển đổi, để dễ dàng quản lý trong Power Query, bạn nên đặt tên cho bảng này.

Chọn bảng, đi đến thẻ Table Design.
Tại mục Table Name ở góc trái, đặt tên là RepeatRows.

Bước 2: Khởi Động Power Query Editor

Tiếp theo, chúng ta sẽ đưa bảng dữ liệu này vào môi trường xử lý của Power Query.

Chọn một ô bất kỳ trong bảng RepeatRows.
Di chuyển đến thẻ Data trên thanh công cụ Ribbon.
Trong nhóm Get & Transform Data, nhấp vào biểu tượng From Table/Range.

Nhấp vào biểu tượng From Table Range trên thanh Ribbon

Cửa sổ Power Query Editor sẽ mở ra, hiển thị dữ liệu của bạn. Tên của truy vấn (Query) sẽ mặc định trùng với tên bảng bạn đã đặt.

Dữ liệu được hiển thị trong giao diện Power Query

Bước 3: Tạo Danh Sách (List) Bằng Custom Column

Đây là bước quan trọng nhất. Chúng ta sẽ tạo một cột mới chứa một “danh sách” các số từ 1 đến 5 cho mỗi hàng. Power Query sử dụng ngôn ngữ M, và cú pháp để tạo một danh sách số liên tục là {start..end}.

Trong Power Query Editor, chuyển sang thẻ Add Column.

Chọn thẻ Add Column trong Power Query

Chọn tính năng Custom Column.

Chọn tính năng Custom Column

Trong hộp thoại xuất hiện:
- New column name: Đặt tên tùy ý, ví dụ Custom.
- Custom column formula: Nhập công thức sau: ={1..5}

Công thức {1..5} chỉ thị cho Power Query tạo ra một danh sách chứa các số nguyên từ 1 đến 5. Mỗi ô trong cột mới này sẽ chứa toàn bộ danh sách đó.

Nhập công thức tạo danh sách vào hộp thoại Custom Column

Nhấn OK.

Kết quả trả về sẽ là một cột mới, trong đó mỗi ô hiển thị chữ “List” màu vàng.

Cột Custom hiển thị giá trị List tại mỗi dòng

Giải thích cơ chế: Khác với Excel truyền thống nơi mỗi ô chỉ chứa một giá trị đơn lẻ (số hoặc chuỗi), Power Query cho phép một ô chứa một cấu trúc dữ liệu phức tạp như List (Danh sách), Record (Bản ghi) hoặc Table (Bảng). Ở đây, chúng ta đang nhúng một danh sách 5 phần tử vào mỗi dòng dữ liệu. Bạn có thể kiểm tra bằng cách nhấp vào khoảng trắng bên cạnh chữ “List” để xem trước nội dung ở dưới đáy màn hình.

Xem trước nội dung danh sách trong Power Query

Bước 4: Mở Rộng Danh Sách (Expand) Thành Các Hàng

Bây giờ, chúng ta sẽ “bung” danh sách này ra. Khi bạn mở rộng một danh sách thành các hàng mới, Power Query sẽ nhân bản dòng dữ liệu gốc tương ứng với số lượng phần tử có trong danh sách đó.

Nhấp vào biểu tượng Expand (hai mũi tên chéo nhau) ở tiêu đề cột Custom.
Chọn tùy chọn Expand to New Rows.

Chọn Expand to New Rows để nhân bản dữ liệu

Ngay lập tức, bạn sẽ thấy mỗi bộ phim trong danh sách gốc giờ đây xuất hiện 5 lần liên tiếp. Cột Custom sẽ hiển thị các số từ 1 đến 5 tương ứng cho mỗi lần lặp.

Kết quả mỗi hàng đã được lặp lại 5 lần

Bước 5: Loại Bỏ Cột Phụ Trợ

Cột Custom đã hoàn thành nhiệm vụ giúp chúng ta nhân bản dòng. Nếu bạn không cần giữ lại số thứ tự lần lặp, hãy xóa nó đi để làm gọn dữ liệu.

Nhấp chuột phải vào tiêu đề cột Custom.
Chọn Remove.

Xóa cột Custom không còn cần thiết

Bước 6: Tải Dữ Liệu Về Excel

Dữ liệu đã sẵn sàng. Bước cuối cùng là đưa nó trở lại bảng tính Excel để sử dụng.

Chuyển về thẻ Home (hoặc File).
Nhấp vào Close & Load.

Chọn Close and Load để xuất dữ liệu

Excel sẽ tạo một Sheet mới chứa bảng kết quả. Điểm mạnh của phương pháp này là tính “động”. Nếu bạn thêm phim mới vào bảng gốc hoặc muốn đổi từ lặp 5 lần thành 10 lần (bằng cách sửa công thức Power Query), bạn chỉ cần nhấp chuột phải vào bảng kết quả và chọn Refresh. Mọi thứ sẽ được cập nhật tự động trong tích tắc.

2. Lặp Lại Hàng Dựa Trên Giá Trị Cột (Số Lượng Động)

Trong thực tế, nhu cầu thường gặp hơn là lặp lại mỗi mặt hàng theo số lượng tồn kho, hoặc lặp lại tên nhân viên theo số ngày công. Ví dụ: Hàng 1 lặp 2 lần, Hàng 2 lặp 5 lần, Hàng 3 lặp 4 lần.

Dưới đây là bảng dữ liệu ví dụ có cột Rept quy định số lần lặp:

Dữ liệu mẫu với cột chỉ định số lần lặp lại

Bước 1: Chuẩn Hóa Dữ Liệu Thành Table

Tương tự như phương pháp trước, hãy đảm bảo dữ liệu của bạn là một Excel Table.

Chọn vùng dữ liệu.
Nhấn Ctrl + T và nhấn OK.

Đảm bảo vùng dữ liệu chính xác khi tạo bảng

Bạn có thể đặt tên bảng này là RepeatRowsCustom để dễ phân biệt.

Bước 2: Đưa Dữ Liệu Vào Power Query

Chọn một ô trong bảng.
Vào thẻ Data > From Table/Range.

Mở dữ liệu trong Power Query từ thẻ Data

Bước 3: Tạo Danh Sách Động Với Custom Column

Chúng ta vẫn sử dụng Custom Column, nhưng công thức sẽ thay đổi một chút. Thay vì nhập một số cố định (như 5), chúng ta sẽ tham chiếu đến cột chứa giá trị số lượng.

Vào thẻ Add Column > Custom Column.

Chọn Custom Column trong Power Query

Đặt tên cột mới là Custom.
Trong ô công thức, nhập: ={1..[Rept]}

Lưu ý quan trọng:

Power Query phân biệt chữ hoa chữ thường. Tên cột phải chính xác tuyệt đối.
Để tránh sai sót, bạn nên nhấp đúp vào tên cột Rept trong danh sách Available columns ở bên phải hộp thoại thay vì gõ tay.
Cú pháp [Rept] đại diện cho giá trị của cột đó tại dòng hiện tại.

Nhập công thức tham chiếu cột động vào Custom Column

Nhấn OK.

Kết quả vẫn là một cột chứa các giá trị “List”. Tuy nhiên, nội dung bên trong mỗi danh sách sẽ khác nhau tùy thuộc vào số trong cột Rept.

Nếu Rept = 2, danh sách là [1, 2].
Nếu Rept = 5, danh sách là [1, 2, 3, 4, 5].

Danh sách động được tạo dựa trên giá trị cột Rept

Bạn có thể kiểm chứng bằng cách chọn ô List ở dòng đầu tiên:

Kiểm tra giá trị danh sách ở dòng đầu tiên

Và kiểm tra dòng thứ hai:

Kiểm tra giá trị danh sách ở dòng thứ hai

Bước 4: Mở Rộng Danh Sách (Expand)

Thao tác tương tự như phương pháp 1:

Nhấp vào biểu tượng Expand trên cột Custom.
Chọn Expand to New Rows.

Chọn Expand to New Rows cho danh sách động

Kết quả là các hàng sẽ được lặp lại chính xác theo số lần quy định. Đây là cách cực kỳ hiệu quả để “bung” dữ liệu tổng hợp thành dữ liệu chi tiết.

Dữ liệu đã được lặp lại theo giá trị trong cột Rept

Bước 5: Dọn Dẹp Cột Thừa

Sau khi mở rộng, cột Rept (số lượng gốc) và cột Custom (số thứ tự lặp) có thể không còn cần thiết cho báo cáo cuối cùng.

Chọn cả hai cột (giữ phím Ctrl và nhấp vào tiêu đề từng cột).
Nhấp chuột phải và chọn Remove Columns.

Xóa các cột không cần thiết

Bước 6: Xuất Kết Quả Ra Excel

Hoàn tất quy trình bằng cách tải dữ liệu về:

Vào thẻ Home > Close & Load.

Xuất dữ liệu hoàn chỉnh ra Excel

Bây giờ bạn đã có một bảng dữ liệu sạch sẽ, được mở rộng theo đúng logic mong muốn. Bất kỳ thay đổi nào về số lượng trong bảng gốc sẽ được phản ánh ngay lập tức vào bảng kết quả sau khi bạn nhấn Refresh.

Câu Hỏi Thường Gặp (FAQ)

Câu hỏi 1: Tôi có thể sử dụng Power Query trên các phiên bản Excel cũ không?
Trả lời: Power Query được tích hợp sẵn trong Excel 2016, 2019, 2021 và Excel 365 (nằm trong thẻ Data). Đối với Excel 2010 và 2013, bạn cần tải xuống và cài đặt Add-in Power Query từ trang chủ của Microsoft.

Câu hỏi 2: Điều gì xảy ra nếu giá trị trong cột số lượng lặp là 0 hoặc số âm?
Trả lời: Nếu giá trị là 0, Power Query sẽ tạo ra một danh sách rỗng (Empty List). Khi thực hiện “Expand to New Rows”, dòng dữ liệu đó sẽ bị biến mất khỏi kết quả (vì không có gì để hiển thị). Nếu số âm, công thức {1..-5} sẽ không hoạt động như mong muốn và có thể gây lỗi hoặc tạo danh sách rỗng tùy thuộc vào phiên bản. Bạn nên lọc bỏ các giá trị <= 0 trước khi thực hiện bước Custom Column.

Câu hỏi 3: Làm thế nào để thêm số thứ tự tăng dần cho toàn bộ bảng sau khi lặp?
Trả lời: Trong Power Query, sau khi đã mở rộng các hàng, bạn có thể vào thẻ Add Column và chọn Index Column -> From 1. Điều này sẽ tạo ra một cột số thứ tự liên tục cho toàn bộ tập dữ liệu mới.

Câu hỏi 4: Phương pháp này có làm nặng file Excel không?
Trả lời: Power Query xử lý dữ liệu rất hiệu quả và nén dữ liệu tốt. Tuy nhiên, nếu bạn nhân bản một tập dữ liệu lớn (ví dụ: 10.000 dòng nhân lên 100 lần mỗi dòng = 1 triệu dòng), file Excel sẽ trở nên nặng và chậm hơn khi thao tác. Hãy cân nhắc chỉ tải dữ liệu cần thiết hoặc sử dụng Data Model (Power Pivot) nếu dữ liệu quá lớn.

Câu hỏi 5: Tôi có cần biết lập trình M-Code để sử dụng Power Query không?
Trả lời: Không nhất thiết. Hầu hết các tác vụ (95%) có thể thực hiện qua giao diện đồ họa (nhấp chuột). Tuy nhiên, việc hiểu một số cú pháp cơ bản như {1..n} trong bài viết này sẽ giúp bạn giải quyết các vấn đề tùy biến nhanh hơn.

Nguồn Tham Khảo

Để hiểu rõ hơn về các kỹ thuật xử lý dữ liệu nâng cao trong Excel, bạn có thể tham khảo các nguồn tài liệu uy tín sau:

Microsoft Support: Giới thiệu về Power Query trong Excel – Tài liệu chính thức từ Microsoft giải thích các khái niệm cơ bản và nâng cao.
Microsoft Learn: Power Query M formula language reference – Tài liệu kỹ thuật chi tiết về ngôn ngữ M dùng trong Power Query.
Excel Campus: How to Repeat Rows N Times – Các hướng dẫn thực hành và mẹo vặt Excel từ cộng đồng chuyên gia (Tìm kiếm từ khóa liên quan).
MrExcel: Power Query Solutions – Diễn đàn thảo luận về các giải pháp xử lý dữ liệu phức tạp.