Cách gộp nhiều file Excel vào một file bằng Power Query

Bạn cần kết hợp ghép nhiều file Excel vào một? Microsoft Power Query có thể giúp bạn. Microsoft Power Query là một công cụ hữu ích để làm việc với dữ liệu bên trong Microsoft Excel, đi kèm với rất nhiều tính năng giúp quản lý các tập dữ liệu đơn giản nhưng mạnh mẽ.

Power Query hữu ích nhất khi làm việc với nhiều bộ dữ liệu, thay vì chỉ một bộ. Công cụ này hoạt động như một cầu nối giữa các bộ dữ liệu và Microsoft Excel.

Để chứng minh điều đó trong bài học Excel online hôm nay Gitiho sẽ hướng dẫn bạn một nhiệm vụ phổ biến mà Power Query tỏa sáng: kết hợp hai tập dữ liệu khác nhau thành một.

Microsoft Power Query là gì?

Microsoft Power Query là một công cụ có sẵn trong Microsoft Excel dành cho Windows. Công cụ này chỉ được bao gồm trong các phiên bản 2016 hoặc mới hơn, vì vậy hãy cập nhật phiên bản Excel mới nhất để bắt đầu sử dụng. Bạn nên sử dụng thành thạo Excel khi bắt đầu làm quen với Power Query.

Power Query cho phép bạn tải dữ liệu từ một số nguồn khác nhau, chỉnh sửa dữ liệu đó rồi nhập dữ liệu đó vào trang tính Excel.

Một trong những loại nguồn mà bạn có thể lấy dữ liệu là các trang tính Excel khác. Để lấy dữ liệu cho ví dụ về Power Query này, chúng tôi sẽ sử dụng hai trang tính Excel khác nhau chứa các tập dữ liệu tương tự, rồi kết hợp chúng thành một.

Hãy lấy hai sổ làm việc Excel, một có tiêu đề "Cars.xlsx" và một có tiêu đề "Trucks.xlsx". Nội dung của các trang tính này rất đơn giản. Mỗi trang tính chứa một bảng mô tả các loại xe nhất định được phân loại theo các cột: "Make", "Model", "color" và "Year".
 

 

Chúng ta cũng sẽ tạo một sổ làm việc trống có tiêu đề "Vehicles.xlsx" để chứa dữ liệu từ hai bản tính trên. Để đơn giản, chúng tôi sử dụng hai tập dữ liệu có cùng thông tin tiêu đề. Mục tiêu của chúng ta ở đây là tạo một bảng mới với cả thông tin xe hơi và xe tải.

Bây giờ, hãy bắt đầu làm việc với Power Query! Tham khảo:

Cách gộp nhiều file Excel vào một file bằng Power Query

Bước 1: Tải dữ liệu vào Power Query Editor

Để bắt đầu, tất cả những gì bạn cần làm là mở sổ làm việc bạn muốn chứa dữ liệu đã hoàn thành. Trong trường hợp này, sổ làm việc là "Vehicles.xlsx".

Sử dụng Power Query vô cùng dễ dàng, bạn thậm chí không phải mở sổ làm việc chứa dữ liệu để trích xuất những gì bạn cần. Trong "Vehicles.xlsx", điều hướng đến tab "Data".

Bạn sẽ thấy tùy chọn "Get Data" trên thanh công cụ của mình. Tùy chọn này là mục nhập vào Power Query và sẽ cho phép bạn chọn nguồn dữ liệu của mình.

Vì chúng ta sẽ lấy dữ liệu từ Excel, bạn hãy chọn các tùy chọn "From File", sau đó là "From Workbook".

Trình chỉnh sửa sẽ mở một file explorer và bạn có thể điều hướng đến sổ làm việc ở bất kỳ đâu trên máy tính của mình. Trước tiên, bạn sẽ chọn tệp "Cars.xlsx".

Sau khi bạn chọn tệp của mình, menu Power Query Navigator sẽ tải để hiển thị cho bạn bản xem trước của trang tính bạn đã chọn. Lưu ý tệp "Cars.xlsx" cũng như trang tính được hiển thị trong Navigator. Nhấp vào trang tính và bảng sẽ tải trên menu giống như nó xuất hiện trong trang tính của bạn! Nhấp vào "Transform Data" để mở dữ liệu trong trình chỉnh sửa.

Trình chỉnh sửa Power Query có rất nhiều tùy chọn, đừng lo lắng về những tùy chọn đó ngay bây giờ. Lúc này, bạn chỉ cần làm quen với những tính năng rất cơ bản. Thoạt nhìn, dữ liệu đã được tải lên đúng cách! Nó trông giống như một bảng Excel. Dữ liệu được sắp xếp theo hàng và cột, với tiêu đề được đặt tên ở trên cùng.

Ở bên phải của bạn là menu có tên "Query Settings" chứa cửa sổ "Applied Steps". Các bước chỉ là những thay đổi được thực hiện đối với bảng tính của bạn. 

Ở phía ngoài cùng bên trái, nhấp vào biểu tượng "Queries" để mở rộng menu và hiển thị tên của bảng bạn hiện đang làm việc. Power Query có thể lấy nhiều bảng cùng một lúc và sẽ hiển thị tất cả chúng trong thanh công cụ này.

Để nối hai bảng với nhau, cả hai bảng đều cần được tải lên Power Query. Hiện giờ cũng ta đã tải được 1 bảng tính, hãy tiếp tục tải bảng tính thứ 2.

Xem thêm: Hướng dẫn gộp 2 bảng trong Excel vào 1 bảng lớn với hàm SUMPRODUCT

Bước 2: Thêm nguồn dữ liệu thứ hai vào Power Query

Bên trong trình chỉnh sửa, ở góc trên cùng bên phải, là nút "New source". Nút này cho phép bạn chọn một nguồn dữ liệu bổ sung và thêm nó vào Power Query Editor.

 Chọn "File" rồi chọn "Excel" để mở trình khám phá tệp. Điều hướng đến "Trucks.xlsx" để chọn tệp để nhập.

Cũng giống như Query ở trên, Navigator sẽ bật lên cho phép bạn chọn bảng của mình từ trang tính. Chọn trang tính nhấp vào "OK". Bây giờ bạn sẽ thấy cả bảng từ trang tính cars và trucks trong thanh công cụ Query. Nhấp vào một trong hai bảng sẽ hiển thị dữ liệu có trong trang tính đó.

Xem thêm: Cách gộp dòng trong Excel: Gộp nhiều dòng thành một dòng cực nhanh

Bước 3: Nối các bảng dữ liệu trong Power Query 

Khi bạn đã chuẩn bị xong tất cả, việc kết hợp các bảng dữ liệu thực sự khá đơn giản. Nhấp vào bảng "cars" và trong thanh công cụ, chọn "Append Queries" trong phần "Combine".

Cửa sổ Append sẽ bật lên và nhắc nhở một bảng mà bạn muốn đưa vào bảng đã chọn của mình. Hãy nhớ rằng bạn đang ở trên bảng "cars" và gắn bảng "trucks" vào nó. Nhấp vào bảng trucks trong "Table to append" và chọn "OK"
 

Đây là kết quả:

Các tập dữ liệu hiện đã được kết hợp! Cũng lưu ý rằng Power Query đã tạo một "steps" mới trong danh sách của bạn "Appended Query".

Bước 4: Tải dữ liệu từ Power Query vào trang tính Excel

Điều cuối cùng cần làm là lấy dữ liệu này được lưu trữ trên trình soạn thảo Power Query và tải nó vào trang tính của bạn.

Ở góc trên cùng bên trái, nhấp vào "Close & Load" để đặt bảng mới tạo vào trang Vehicles ở định dạng bảng. Khi nhấp vào, những điều sau sẽ xảy ra:

• Trình chỉnh sửa sẽ lưu các bước Truy vấn của bạn và thoát
• Sổ làm việc sẽ xuất hiện với dữ liệu được tải dưới dạng Bảng Excel
• Excel cũng sẽ mở menu Workbook Queries ở bên phải trang tính của bạn

Bây giờ bạn đã học cách nối dữ liệu bằng Power Query, bạn có một công cụ mới mạnh mẽ để đơn giản hóa dữ liệu. Bây giờ dữ liệu được tải vào Excel để sử dụng, bạn có thể làm việc với nó giống như bất kỳ bảng nào. Tiếp theo, bạn có thể tạo đồ thị và biểu đồ từ bảng tính Excel hoặc tóm tắt dữ liệu bằng Pivot Tables.

Xem thêm: Cách dùng Merge Cell để gộp ô trong Excel

Kết luận

Hy vọng bài viết này đã cung cấp cho bạn những thông tin hữu ích. Để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng thú vị khác, hãy theo dõi Gitiho thường xuyên các bạn nhé.