Việc tổng hợp dữ liệu từ nhiều trang tính Excel theo cách thủ công có thể tốn rất nhiều thời gian và dễ dẫn đến các lỗi sao chép không đáng có. Hơn nữa, quá trình này sẽ trở nên cực kỳ nặng nề nếu bạn cần thường xuyên cập nhật bộ dữ liệu. May mắn thay, công cụ Power Query trong Excel có thể thực hiện tất cả những công việc này một cách tự động và hiệu quả.
Trong bài viết này, chúng tôi sẽ hướng dẫn chi tiết cách hợp nhất thông tin từ nhiều tệp XLSX riêng lẻ vào một bảng tính duy nhất. Mặc dù trọng tâm là các tệp Excel, bạn hoàn toàn có thể áp dụng quy trình tương tự để kết hợp dữ liệu từ các định dạng tệp khác như TXT, CSV, và XML một cách dễ dàng. Đây là một thủ thuật Excel vô cùng hữu ích, giúp bạn tối ưu hóa quy trình xử lý dữ liệu và tiết kiệm đáng kể thời gian cũng như công sức.
Bước 1: Sắp xếp các sổ làm việc bạn sẽ kết hợp
Trước khi bắt đầu quá trình gộp nhiều file Excel thành một trang tính duy nhất, có một số bước chuẩn bị quan trọng bạn cần thực hiện để đảm bảo quá trình diễn ra suôn sẻ.
Đầu tiên, hãy chắc chắn rằng tất cả các bộ dữ liệu bạn định kết hợp được cấu trúc theo cùng một định dạng. Cụ thể, chúng phải chứa cùng số lượng cột, có cùng tiêu đề cột, và các thẻ trang tính (worksheet tabs) phải có cùng tên. Ngoài ra, quá trình sẽ đơn giản hơn rất nhiều nếu tên các tệp Excel cũng tuân theo một cú pháp tương tự.
Hai sổ làm việc Excel có cấu trúc và tên thẻ trang tính giống nhau
Mặc dù vậy, các cột không nhất thiết phải theo cùng một thứ tự và số lượng hàng trên mỗi trang tính có thể khác nhau.
Thứ hai, đảm bảo rằng tất cả các tệp Excel cần gộp được lưu trữ trong cùng một thư mục.
Thư mục chứa ba tệp Excel với tên tương tự
Nếu có thể, thư mục này chỉ nên chứa duy nhất các tệp bạn sẽ kết hợp, vì điều này giúp quá trình sạch sẽ và dễ dàng hơn trong các bước sau. Tuy nhiên, nếu bạn buộc phải lưu trữ các tệp khác trong thư mục này, chúng tôi sẽ hướng dẫn cách đảm bảo chỉ chọn đúng các tệp cần hợp nhất dữ liệu trong phần tiếp theo.
Bước 2: Mở Excel và kết nối đến thư mục chứa dữ liệu
Sau khi các tệp đã được tổ chức đúng cách vào thư mục theo các điều kiện tiên quyết ở trên, bạn đã sẵn sàng để bắt đầu tổng hợp dữ liệu.
Đầu tiên, hãy mở một sổ làm việc Microsoft Excel mới, bắt đầu một trang tính trống, sau đó nhấn F12 để lưu tệp này vào một thư mục khác với thư mục chứa các tệp riêng lẻ bạn sẽ kết hợp. Việc này giúp tránh nhầm lẫn và đảm bảo tính nguyên vẹn của dữ liệu gốc.
Chọn sổ làm việc trống trong màn hình chào mừng của Microsoft Excel
Tiếp theo, trong thẻ Data (Dữ liệu) trên dải băng (ribbon), nhấp vào Get Data (Lấy dữ liệu). Sau đó, di chuột qua From File (Từ tệp) và chọn From Folder (Từ thư mục). Đây là bước quan trọng để thiết lập kết nối dữ liệu từ thư mục chứa các file Excel của bạn.
Chọn 'From Folder' từ menu Get Data trong thẻ Data của Excel
Bây giờ, trong hộp thoại Browse (Duyệt), hãy tìm và chọn thư mục nơi các tệp bạn muốn kết hợp được lưu trữ, sau đó nhấp vào Open (Mở).
Thư mục "Team Scores" được chọn trong hộp thoại Browse của Windows 11
Bước 3: Chọn các tệp tin bạn muốn kết hợp
Những gì bạn thực hiện trong bước này sẽ phụ thuộc vào nội dung của thư mục bạn đã chọn. Nếu thư mục chỉ chứa các tệp bạn muốn hợp nhất, hãy xem phần 3A dưới đây. Ngược lại, nếu thư mục còn chứa các tệp khác mà bạn không muốn hợp nhất, hãy chuyển đến phần 3B.
3A: Thư mục chỉ chứa các tệp tin bạn muốn gộp
Như đã đề cập trước đó, quá trình hợp nhất sổ làm việc sẽ sạch sẽ và dễ dàng hơn nếu thư mục bạn chọn chỉ dành riêng cho các tệp bạn muốn kết hợp. Lý do là bạn không cần phải lọc bỏ bất kỳ tệp không mong muốn nào. Vì vậy, khi bạn đã sẵn sàng để kết hợp các tệp, hãy nhấp vào Combine (Kết hợp) và trong menu thả xuống xuất hiện, nhấp vào Combine And Transform Data (Kết hợp và Biến đổi dữ liệu).
Menu Combine và tùy chọn "Combine And Transform Data" được chọn trong hộp thoại chứa danh sách ba tệp Excel
Sau khi Excel đánh giá dữ liệu của bạn một cách nhanh chóng, bạn sẽ thấy hộp thoại Combine Files (Kết hợp Tệp), và bạn đã sẵn sàng chuyển sang Bước 4 để tiếp tục quy trình tự động hóa Excel mạnh mẽ này.
Cửa sổ "Evaluating Query" của Microsoft Excel đang xử lý dữ liệu
3B: Thư mục chứa một số tệp tin bạn không muốn gộp
Nếu thư mục đã chọn cũng chứa các tệp khác ngoài những tệp bạn muốn hợp nhất, sau khi bạn định vị thư mục trong Bước 2, bạn sẽ thấy chúng được liệt kê chi tiết. Trong trường hợp này, bạn cần nhấp vào Transform Data (Biến đổi dữ liệu), vì bạn muốn thực hiện các thay đổi đối với lựa chọn tệp trước khi kết hợp dữ liệu.
Tùy chọn "Transform Data" được chọn trong hộp thoại với danh sách các tệp Excel, bao gồm một tệp không liên quan
Việc nhấp vào tùy chọn này sẽ khởi chạy Power Query Editor (Trình chỉnh sửa Power Query), một cửa sổ riêng biệt trong Microsoft Excel, nơi bạn có thể lọc bỏ các tệp mà bạn không muốn đưa vào quá trình kết hợp.
Trình chỉnh sửa Power Query của Excel hiển thị danh sách bốn tên tệp, trong đó có một tệp không liên quan
Trong ví dụ này, tệp có tên “Contact Details” (Thông tin liên hệ) cần được loại trừ. Vì vậy, hãy nhấp vào mũi tên thả xuống bộ lọc trong tiêu đề cột Name (Tên), bỏ chọn tệp đó và nhấp OK.
Tệp "Contact Details" bị bỏ chọn trong cột Name của trình chỉnh sửa Power Query của Excel
Bây giờ, chỉ những tệp bạn muốn kết hợp mới được liệt kê. Hãy tiếp tục và nhấp vào mũi tên kép xuống trong cột Content để khởi chạy hộp thoại Combine Files (Kết hợp Tệp).
Mũi tên kép xuống trong tiêu đề cột Content trong trình chỉnh sửa Power Query của Excel
Bước 4: Kết hợp các tệp tin
Có một vài điều cần lưu ý trong hộp thoại Combine Files (Kết hợp Tệp) của Power Query.
Đầu tiên, theo mặc định, sổ làm việc được chỉ định làm tệp mẫu là tệp đầu tiên bạn chọn trong danh sách. Tệp mẫu này sẽ quy định cách cấu trúc dữ liệu khi các tệp được kết hợp. Vì vậy, miễn là tất cả các tệp của bạn được cấu trúc nhất quán, bạn có thể giữ nguyên tùy chọn này. Đây là yếu tố then chốt để đảm bảo dữ liệu của bạn được gộp chính xác.
Khu vực Sample File trong hộp thoại Combine Files của Excel, với tùy chọn "First File" được chọn
Thứ hai, nếu các sổ làm việc của bạn có nhiều hơn một thẻ trang tính (worksheet tab), bạn sẽ thấy chúng được liệt kê trong ngăn Display Options (Tùy chọn hiển thị) ở bên trái. Đây là lý do tại sao việc tất cả các thẻ bạn muốn kết hợp trên các sổ làm việc được chỉ định phải có cùng tên là rất quan trọng. Ngoài ra, nếu bạn đã định dạng dữ liệu dưới dạng bảng Excel và đặt tên cho chúng, bạn cũng sẽ thấy danh sách tên ở đây.
Ngăn Display Options trong hộp thoại Combine Files của Microsoft Excel hiển thị các tùy chọn sheet
Trong ví dụ này, mỗi sổ làm việc chỉ có một trang tính (được đặt tên là “Scores”), vì vậy hãy chọn tùy chọn này. Sau khi xem lại bản xem trước của tệp mẫu đã chọn trong ngăn bên phải, nhấp OK để xác nhận và khởi chạy Power Query Editor.
Thẻ trang tính "Scores" trong hộp thoại Excel được chọn, với bản xem trước của file mẫu hiển thị và nút OK được đánh dấu
Bước 5: Biến đổi dữ liệu
Nếu đây là lần đầu tiên bạn mở Power Query Editor trong Excel, nó có thể gây choáng ngợp ban đầu. Tuy nhiên, một khi bạn dành thời gian để hiểu các khu vực khác nhau của trình chỉnh sửa này hoạt động như thế nào, bạn sẽ nhận ra rằng nó thực sự là một công cụ trực quan và mạnh mẽ để biến đổi dữ liệu.
Ngăn bên trái là nơi tất cả các truy vấn (queries) được đặt. Khi kết hợp dữ liệu từ nhiều sổ làm việc Excel, bạn chỉ cần tập trung vào hai trong số này—truy vấn Transform Sample File, được sử dụng làm mẫu cho bất kỳ thay đổi nào bạn muốn thực hiện đối với các bộ dữ liệu, và truy vấn được nối (appended query) ở cuối danh sách, truy vấn này hợp nhất các tệp. Tùy thuộc vào những gì bạn muốn làm, cả hai đều hữu ích để biến đổi dữ liệu của bạn.
Hai truy vấn được đánh dấu trong ngăn Queries của trình chỉnh sửa Power Query của Excel
Đầu tiên, trong truy vấn Transform Sample File, hãy nhấp vào các biểu tượng định dạng số trong tiêu đề của mỗi cột để cho Excel biết chúng chứa loại dữ liệu nào. Trong ví dụ này, cột thứ nhất, thứ ba và thứ tư chứa số nguyên, và cột thứ hai chứa ngày. Việc này giúp đảm bảo tính chính xác của dữ liệu sau khi gộp.
Các định dạng số của nhiều cột trong trình chỉnh sửa Power Query của Excel được điều chỉnh để khớp với kiểu dữ liệu tương ứng
Hãy nhìn vào ngăn Query Settings (Cài đặt Truy vấn) ở phía bên phải của Power Query Editor để xem (và xóa nếu cần) từng bước bạn thực hiện trong việc biến đổi dữ liệu.
Tiếp theo, trong truy vấn đã nối (appended query), chúng ta không cần cột Source.Name, nhưng sẽ rất tiện nếu có năm cho mỗi điểm số của đội.
Cột Source.Name trong trình chỉnh sửa Power Query của Excel
Bạn có thể biến đổi cột này để chỉ giữ lại các năm trong khi loại bỏ phần còn lại của tên tệp. Để làm điều này, hãy nhấp vào tiêu đề cột để tất cả các giá trị trong cột được chọn, và trong thẻ Transform (Biến đổi) trên dải băng, nhấp vào Extract (Trích xuất), sau đó Text Before Delimiter (Văn bản trước dấu phân cách).
Một cột được chọn trong trình chỉnh sửa Power Query của Excel, và "Text Before Delimiter" được chọn trong menu Extract
Trong trường hợp này, chúng ta muốn giữ lại năm và loại bỏ mọi thứ sau khoảng trắng đầu tiên. Vì vậy, trong trường văn bản Delimiter (Dấu phân cách), hãy gõ một khoảng trắng duy nhất. Sau đó, nhấp vào Advanced Options (Tùy chọn nâng cao) và chọn From The Start Of The Input (Từ đầu nhập liệu). Bây giờ, nhấp OK.
Dấu phân cách khoảng trắng được thêm vào hộp thoại Text Before Delimiter trong trình chỉnh sửa Power Query của Excel, và "From The Start Of The Input" được chọn trong tùy chọn nâng cao
Cuối cùng, nhấp đúp vào tiêu đề cột để đổi tên thành Year (Năm), và thay đổi kiểu dữ liệu thành Whole Number (Số nguyên). Việc này hoàn tất quá trình chuẩn bị dữ liệu.
Một cột trong Power Query Editor được đổi tên thành "Year", với kiểu dữ liệu được thay đổi thành Whole Number
Hãy dành một chút thời gian để cuộn xuống danh sách dữ liệu đã kết hợp để kiểm tra xem có bất kỳ lỗi nào hoặc bất kỳ biến đổi nào khác mà bạn muốn thực hiện không.
Bước 6: Tải dữ liệu đã kết hợp vào trang tính mới
Bây giờ, khi dữ liệu từ các sổ làm việc đã được kết hợp và biến đổi theo ý muốn, đã đến lúc xem nó trông như thế nào trong một trang tính Excel thông thường. Trong thẻ Home (Trang chủ) trên cửa sổ Power Query Editor, nhấp vào nửa trên của nút tách Close And Load (Đóng và Tải).
Phần trên của nút "Close And Load" trong trình chỉnh sửa Power Query được chọn
Để có thêm các tùy chọn tải, bạn có thể nhấp vào nửa dưới của nút tách Close And Load. Ví dụ, bạn có thể tải dữ liệu vào một trang tính hiện có hoặc dưới dạng một PivotTable để phân tích sâu hơn.
Bây giờ, dữ liệu sẽ được tải lên một trang tính mới dưới dạng một bảng Excel được định dạng, nghĩa là bạn có thể định dạng giao diện và các thuộc tính của nó trong thẻ Table Design (Thiết kế Bảng) trên dải băng.
Thẻ "Table Design" trên dải băng Microsoft Excel được kích hoạt, hiển thị các tùy chọn thiết kế bảng
Để thực hiện các thay đổi tiếp theo đối với truy vấn trong Power Query Editor, sau khi kích hoạt ngăn Queries And Connections (Truy vấn và Kết nối) trong thẻ Data (Dữ liệu), nhấp chuột phải vào truy vấn và chọn Edit (Chỉnh sửa).
Ngăn Queries And Connections trong Microsoft Excel được kích hoạt, và menu chuột phải trên truy vấn chính được mở rộng, với "Edit" được chọn
Sau khi bạn đã thực hiện các chỉnh sửa cần thiết trong Power Query Editor, hãy nhấp Close And Load một lần nữa để cập nhật bộ dữ liệu kết quả.
Bước 7: Thêm dữ liệu bổ sung từ các sổ làm việc khác
Cuối cùng, hãy tưởng tượng dữ liệu cho năm 2024 đã có và bạn muốn thêm thông tin mới này vào truy vấn hiện có. Tuyệt vời thay, vì bạn đã thực hiện các bước cần thiết trong Power Query Editor, tất cả những gì bạn cần làm là đảm bảo sổ làm việc mới đáp ứng tất cả các tiêu chí mà chúng tôi đã nêu trong Bước 1, và nhấp một nút duy nhất trong Excel để làm mới bảng.
Đầu tiên, hãy di chuyển sổ làm việc chứa dữ liệu mới vào thư mục nơi dữ liệu các năm trước được đặt, đảm bảo rằng tên sổ làm việc, cấu trúc trang tính và tên thẻ trang tính nhất quán với các tệp khác.
Sau đó, mở sổ làm việc chứa dữ liệu đã được tổng hợp trước đó, và trong ngăn Queries And Connections (mà bạn có thể kích hoạt bằng cách nhấp vào Queries And Connections trong thẻ Data trên dải băng), nhấp vào biểu tượng Refresh (Làm mới) bên cạnh truy vấn chính.
Sau khi cho Excel một vài giây để hoàn tất cập nhật này, hãy cuộn xuống để xem dữ liệu bổ sung được thêm vào cuối bảng.
Để giữ bất kỳ định dạng nào bạn đã áp dụng cho bảng trước khi làm mới Power Query, hãy nhấp vào Properties (Thuộc tính) trong thẻ Table Design (Thiết kế Bảng) trên dải băng và đảm bảo Preserve Cell Formatting (Giữ định dạng ô) được chọn. Nếu bạn thay đổi độ rộng cột và không muốn chúng tự động điều chỉnh theo dữ liệu mỗi khi bạn làm mới, hãy bỏ chọn Adjust Column Width (Điều chỉnh độ rộng cột).
Ngoài việc tổng hợp dữ liệu từ các sổ làm việc riêng biệt được lưu vào một thư mục, bạn còn có thể xếp chồng dữ liệu từ nhiều trang tính Excel trong một sổ làm việc duy nhất bằng cách tạo kết nối dữ liệu giữa chúng và kết hợp các truy vấn. Hơn nữa, bạn có thể sử dụng Power Query Editor để nhập bảng từ web vào Excel.
Việc thành thạo công cụ Power Query sẽ mở ra nhiều khả năng mới trong việc xử lý và phân tích dữ liệu trong Excel, giúp bạn trở thành một chuyên gia trong lĩnh vực này. Nếu bạn có bất kỳ câu hỏi nào về thủ thuật Excel này, đừng ngần ngại để lại bình luận bên dưới!
