Trong thế giới kinh doanh và phân tích dữ liệu ngày nay, việc xử lý và tổng hợp thông tin từ nhiều nguồn khác nhau là một nhiệm vụ thường xuyên nhưng không kém phần thách thức. Đặc biệt, khi bạn phải kết hợp dữ liệu từ nhiều file Excel khác nhau, quá trình này có thể trở nên cực kỳ tốn thời gian, dễ gây ra lỗi sao chép, và trở thành gánh nặng lớn nếu bạn cần cập nhật bộ dữ liệu thường xuyên. Tưởng tượng bạn có hàng chục, thậm chí hàng trăm file Excel chứa dữ liệu bán hàng theo tháng, theo khu vực, và bạn cần gộp tất cả chúng lại thành một bảng tính duy nhất để phân tích. Việc này thủ công không chỉ mất vài giờ mà còn tiềm ẩn rủi ro sai sót nghiêm trọng.
May mắn thay, Microsoft Excel cung cấp một công cụ mạnh mẽ mang tên Power Query có thể tự động hóa toàn bộ quy trình này. Power Query không chỉ giúp bạn gộp nhiều file Excel một cách nhanh chóng, chính xác mà còn cho phép bạn dễ dàng cập nhật dữ liệu khi có các tệp mới xuất hiện. Hướng dẫn này của thichcongnghe.net sẽ chỉ cho bạn cách tận dụng Power Query để biến công việc “ác mộng” này thành một tác vụ đơn giản chỉ với vài cú click chuột, giúp bạn tiết kiệm thời gian quý báu và nâng cao hiệu quả làm việc. Mặc dù bài viết tập trung vào các tệp XLSX, bạn hoàn toàn có thể áp dụng quy trình tương tự cho các định dạng khác như TXT, CSV hay XML.
Bước 1: Chuẩn Bị Các Tệp Excel Cần Kết Hợp
Trước khi bắt tay vào việc gộp nhiều file Excel thành một bảng tính duy nhất, việc chuẩn bị kỹ lưỡng là yếu tố then chốt để đảm bảo quá trình diễn ra suôn sẻ và không gặp lỗi.
Đảm bảo cấu trúc dữ liệu nhất quán
Đây là nguyên tắc quan trọng nhất. Tất cả các bộ dữ liệu bạn định kết hợp phải có cùng một cấu trúc. Cụ thể:
- Số lượng cột phải giống nhau: Mỗi tệp Excel phải có cùng số lượng cột.
- Tiêu đề cột phải trùng khớp: Các tên tiêu đề cột (header) ở hàng đầu tiên của mỗi tệp phải giống hệt nhau, kể cả dấu cách hay chữ hoa/thường.
- Tên tab bảng tính phải đồng nhất: Nếu các tệp của bạn có nhiều tab (worksheet), tab mà bạn muốn gộp dữ liệu từ đó phải có cùng một tên trên tất cả các tệp.
- Tên tệp nên theo cùng một cú pháp: Mặc dù không bắt buộc, nhưng việc đặt tên tệp theo một quy tắc nhất định (ví dụ:
DoanhThu_Thang01.xlsx,DoanhThu_Thang02.xlsx) sẽ giúp bạn quản lý và lọc dữ liệu dễ dàng hơn về sau.
Cần lưu ý rằng 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 sheet có thể khác nhau. Power Query sẽ tự động sắp xếp và điều chỉnh.
Hai tệp Excel có cấu trúc dữ liệu nhất quán và cùng tên tab, minh họa cho việc chuẩn bị gộp nhiều file Excel với Power Query.
Lưu trữ tệp trong cùng một thư mục
Để Power Query có thể truy cập và kết hợp dữ liệu từ nhiều file Excel một cách hiệu quả, tất cả các tệp nguồn cần được lưu trữ trong cùng một thư mục trên máy tính của bạn.
- Tạo một thư mục riêng biệt: Tốt nhất, thư mục này chỉ nên chứa duy nhất các tệp mà bạn muốn gộp. Điều này sẽ giúp quá trình trở nên “sạch” và dễ dàng hơn trong các bước sau.
- Nếu phải lưu trữ chung: Trong trường hợp bạn bắt buộc phải lưu trữ các tệp khác trong cùng thư mục, đừng lo lắng, chúng tôi sẽ hướng dẫn cách lọc bỏ các tệp không liên quan trong Power Query Editor.
Thư mục trên Windows 11 chứa ba tệp Excel với tên theo năm, sẵn sàng để kết hợp dữ liệu.
Bước 2: Khởi Tạo Power Query và Kết Nối Thư Mục
Sau khi các tệp đã được tổ chức đúng cách vào một thư mục riêng, bạn đã sẵn sàng bắt đầu quá trình gom dữ liệu Excel bằng Power Query.
Đầu tiên, hãy mở một sổ làm việc Microsoft Excel mới hoàn toàn. Để tránh nhầm lẫn, bạn nên 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 nguồn mà bạn sắp gộp. Nhấn F12 để lưu tệp ngay lập tức.
Giao diện khởi động Excel với tùy chọn Sổ làm việc trống (Blank Workbook) được chọn để bắt đầu gộp dữ liệu.
Tiếp theo, trên thanh Ribbon của Excel, chọn tab Data (Dữ liệu). Trong nhóm Get & Transform Data (Truy vấn & Kết nối), click vào Get Data (Lấy Dữ liệu). Di chuột qua From File (Từ Tệp), sau đó chọn From Folder (Từ Thư mục).
Chọn From Folder (Từ Thư mục) trong menu Get Data (Lấy Dữ liệu) trên tab Data của Excel, bước đầu để sử dụng Power Query.
Một hộp thoại duyệt thư mục sẽ xuất hiện. Bạn hãy tìm đến và chọn thư mục mà bạn đã lưu trữ các tệp Excel cần kết hợp dữ liệu. Sau khi chọn thư mục, click Open (Mở).
Thư mục "Team Scores" được chọn trong hộp thoại Duyệt (Browse) trên Windows 11 để nhập dữ liệu vào Excel.
Bước 3: Lựa Chọn Các Tệp Muốn Kết Hợp
Ở bước này, cách bạn thực hiện sẽ phụ thuộc vào việc thư mục đã chọn chỉ chứa các tệp bạn muốn gộp, hay còn chứa các tệp không liên quan khác.
Trường hợp 1: Thư mục chỉ chứa các tệp cần gộp
Nếu bạn đã làm theo lời khuyên ở Bước 1 và thư mục của bạn chỉ chứa các tệp Excel cần gộp dữ liệu, thì quá trình này sẽ rất đơn giản. Bạn không cần phải lọc bất kỳ tệp nào không mong muốn.
Trong hộp thoại Navigator (Điều hướng) xuất hiện, bạn sẽ thấy danh sách các tệp Excel. Click vào nút Combine (Kết hợp), sau đó trong menu thả xuống, chọn Combine And Transform Data (Kết hợp và Chuyển đổi Dữ liệu).
Ba tệp Excel tương tự được liệt kê, chọn Combine And Transform Data (Kết hợp và Chuyển đổi Dữ liệu) để gộp chúng.
Excel sẽ mất một chút thời gian để đánh giá dữ liệu của bạn. Sau đó, hộp thoại Combine Files (Kết hợp Tệp) sẽ xuất hiện, và bạn có thể chuyển sang Bước 4.
Cửa sổ Evaluating Query (Đánh giá Truy vấn) của Microsoft Excel đang xử lý dữ liệu trước khi hiển thị trong Power Query Editor.
Trường hợp 2: Thư mục có chứa tệp không mong muốn
Nếu thư mục đã chọn của bạn chứa cả các tệp khác ngoài những tệp bạn muốn ghép file Excel, sau khi bạn chọn thư mục ở Bước 2, bạn sẽ thấy tất cả chúng được liệt kê chi tiết. Trong trường hợp này, bạn cần click vào Transform Data (Chuyển đổi Dữ liệu). Việc này sẽ đưa bạn đến Power Query Editor, nơi bạn có thể thực hiện các thay đổi lựa chọn tệp trước khi kết hợp dữ liệu.
Một tệp không mong muốn xuất hiện trong danh sách, chọn Transform Data (Chuyển đổi Dữ liệu) để lọc trước khi gộp các file Excel.
Power Query Editor sẽ mở ra trong một cửa sổ riêng. Tại đây, bạn có thể lọc ra các tệp không muốn đưa vào quá trình gom dữ liệu Excel.
Giao diện Power Query Editor trong Excel, hiển thị danh sách các tệp đã nhập từ thư mục nguồn.
Ví dụ, nếu có một tệp tên “Contact Details” không liên quan, bạn chỉ cần click vào biểu tượng mũi tên thả xuống ở tiêu đề cột Name (Tên), bỏ chọn tệp đó và click OK.
Bỏ chọn tệp "Contact Details" trong cột Name của Power Query Editor để loại trừ khỏi quá trình gộp dữ liệu Excel.
Bây giờ, chỉ còn các tệp bạn muốn kết hợp dữ liệu được liệt kê. Click vào biểu tượng mũi tên kép xuống ở tiêu đề cột Content (Nội dung) để khởi động hộp thoại Combine Files (Kết hợp Tệp).
Biểu tượng mũi tên kép xuống ở tiêu đề cột Content trong Power Query Editor, dùng để gộp các tệp Excel đã chọn.
Bước 4: Hoàn Tất Quá Trình Kết Hợp Các Tệp
Hộp thoại Combine Files (Kết hợp Tệp) là nơi bạn xác nhận cách các tệp sẽ được gộp. Có một vài điểm cần lưu ý ở đây:
Đầu tiên, theo mặc định, sổ làm việc được chọn làm tệp mẫu (sample file) sẽ là tệp đầu tiên trong danh sách. Tệp mẫu này sẽ định hình cấu trúc dữ liệu khi các tệp được kết hợp. Miễn là tất cả các tệp của bạn có cấu trúc nhất quán như đã chuẩn bị ở Bước 1, bạn có thể để tùy chọn này như mặc định.
Khu vực Sample File (Tệp Mẫu) trong hộp thoại Combine Files (Kết hợp Tệp) của Excel, hiển thị tệp đầu tiên được chọn làm mẫu.
Thứ hai, nếu các sổ làm việc của bạn có nhiều tab bảng tính, 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ên tab cần được đồng nhất across all workbooks is crucial. 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 bảng tại đây.
Ngăn Display Options (Tùy chọn Hiển thị) trong hộp thoại Combine Files của Excel, cho phép chọn bảng tính hoặc tên bảng để gộp.
Trong ví dụ này, mỗi sổ làm việc chỉ có một bảng tính (tên là “Scores”). Vì vậy, bạn chỉ cần chọn tùy chọn này. Sau khi xem xét bản xem trước của tệp mẫu đã chọn ở ngăn bên phải, click OK để xác nhận và khởi động Power Query Editor.
Tab bảng tính "Scores" được chọn trong hộp thoại Combine Files, hiển thị bản xem trước dữ liệu mẫu trước khi gộp các file Excel.
Bước 5: Chuyển Đổi và Tối Ưu Dữ Liệu Trong Power Query Editor
Nếu đây là lần đầu tiên bạn mở Power Query Editor, giao diện có thể hơi khó hiểu. Tuy nhiên, một khi bạn dành thời gian tìm hiểu các khu vực khác nhau của trình chỉnh sửa này, bạn sẽ nhận ra nó là một công cụ trực quan và mạnh mẽ để chuyển đổi dữ liệu Excel.
Tổng quan về Power Query Editor
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 file Excel, bạn chỉ cần tập trung vào hai trong số đó:
- Transform Sample File query: Truy vấn tệp mẫu này được sử dụng làm khuôn 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.
- Truy vấn được nối (appended query) ở cuối danh sách: Đây là truy vấn tổng hợp các tệp.
Cả hai đều hữu ích cho việc tối ưu dữ liệu Excel tùy thuộc vào mục đích của bạn.
Hai truy vấn chính được tô sáng trong ngăn Queries (Truy vấn) của Power Query Editor, một cho mẫu và một cho kết quả gộp dữ liệu.
Định dạng kiểu dữ liệu cho các cột
Trước tiên, trong truy vấn Transform Sample File, hãy click vào các biểu tượng định dạng số ở tiêu đề mỗi cột để cho Excel biết kiểu dữ liệu mà chúng chứa. Ví dụ, trong trường hợp này, cột thứ nhất, thứ ba và thứ tư chứa số nguyên (whole numbers), và cột thứ hai chứa ngày (date). Việc định dạng đúng kiểu dữ liệu là rất quan trọng để tránh lỗi và đảm bảo tính toán chính xác.
Điều chỉnh định dạng kiểu dữ liệu cho các cột trong Power Query Editor để đảm bảo tính chính xác khi xử lý dữ liệu Excel.
Bạn có thể theo dõi (và loại bỏ nếu cần) từng bước bạn thực hiện trong việc chuyển đổi dữ liệu trong ngăn Query Settings (Cài đặt Truy vấn) ở phía bên phải của Power Query Editor.
Tùy chỉnh cột “Source.Name” để trích xuất thông tin hữu ích
Trong truy vấn được nối (appended query), chúng ta không cần cột Source.Name nguyên bản, nhưng sẽ rất tiện lợi nếu có năm cho mỗi điểm số của đội.
Cột Source.Name trong Power Query Editor, chứa tên tệp nguồn, sẵn sàng để trích xuất thông tin hữu ích.
Bạn có thể trích xuất dữ liệu Excel từ cột này để chỉ giữ lại các năm và loại bỏ phần còn lại của tên tệp. Để làm điều này, click vào tiêu đề cột để chọn tất cả các giá trị trong cột. Sau đó, trong tab Transform (Chuyển đổi) trên thanh Ribbon, click Extract (Trích xuất), rồi chọn Text Before Delimiter (Văn bản trước Dấu phân cách).
Chọn cột và tính năng Text Before Delimiter (Văn bản trước Dấu phân cách) trong Power Query Editor để trích xuất dữ liệu.
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 Delimiter (Dấu phân cách), gõ một dấu cách đơn. Sau đó, click Advanced Options (Tùy chọn Nâng cao), và chọn From The Start Of The Input (Từ đầu đầu vào). Cuối cùng, click OK.
Thêm dấu phân cách khoảng trắng và chọn From The Start Of The Input (Từ đầu đầu vào) để trích xuất năm từ tên tệp.
Cuối cùng, click đú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).
Đổi tên cột thành "Year" và thay đổi kiểu dữ liệu thành Whole Number (Số Nguyên) trong Power Query Editor.
Bạn nên 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ỳ chuyển đổi dữ liệu nào khác bạn muốn thực hiện hay không.
Bước 6: Tải Dữ Liệu Đã Kết Hợp Về Bảng Tính Excel Mới
Sau khi dữ liệu từ các sổ làm việc đã được kết hợp và chuyển đổi trong Power Query Editor, đã đến lúc xem nó hiển thị như thế nào trong một bảng tính Excel thông thường. Trong tab Home (Trang chủ) trên cửa sổ Power Query Editor, click vào nửa trên của nút Close And Load (Đóng và Tải).
Chọn phần trên của nút Close And Load (Đóng và Tải) trong Power Query Editor để đưa dữ liệu đã gộp vào Excel.
Bạn cũng có thể click vào nửa dưới của nút Close And Load để có thêm các tùy chọn tải. Ví dụ, bạn có thể tải dữ liệu lên một bảng tính hiện có hoặc dưới dạng PivotTable để phân tích sâu hơn.
Ngay lập tức, dữ liệu sẽ được tải về một bảng tính mới dưới dạng bảng Excel đã được định dạng (formatted Excel table). Điều này có nghĩa là bạn có thể định dạng giao diện và các thuộc tính của nó trong tab Table Design (Thiết kế Bảng) trên thanh Ribbon.
Tab Table Design (Thiết kế Bảng) trên thanh Ribbon của Microsoft Excel được kích hoạt sau khi tải dữ liệu từ Power Query.
Để 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 tab Data, click chuột phải vào truy vấn chính, sau đó chọn Edit (Chỉnh sửa).
Ngăn Queries And Connections (Truy vấn và Kết nối) trong Excel được kích hoạt, chọn Edit (Chỉnh sửa) để sửa truy vấn Power Query.
Sau khi bạn đã thực hiện các chỉnh sửa cần thiết trong Power Query Editor, click lại Close And Load để cập nhật bộ dữ liệu kết quả.
Bước 7: Cập Nhật Dữ Liệu Mới Tự Động và Nhanh Chóng
Đây là một trong những tính năng mạnh mẽ nhất của Power Query, biến nó thành công cụ không thể thiếu cho việc quản lý dữ liệu lớn Excel: khả năng cập nhật dữ liệu tự động. Giả sử dữ liệu cho năm 2024 đã được nhập và bạn muốn thêm thông tin mới này vào truy vấn hiện có. Đáng kinh ngạc là, 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í đã nêu ở Bước 1, và click một nút duy nhất trong Excel để làm mới bảng.
Đầu tiên, 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 lưu trữ. Đảm bảo rằng tên sổ làm việc, cấu trúc bảng tính và tên tab đều nhất quán với các tệp khác.
Thư mục trên Windows 11 chứa bốn tệp Excel với dữ liệu theo năm, bao gồm cả tệp mới, sẵn sàng để tự động cập nhật.
Sau đó, mở sổ làm việc chứa dữ liệu đã được nối trước đó. Trong ngăn Queries And Connections (Truy vấn và Kết nối) (bạn có thể kích hoạt bằng cách click “Queries And Connections” trong tab Data trên thanh Ribbon), click vào biểu tượng Refresh (Làm mới) bên cạnh truy vấn chính.
Chọn biểu tượng Refresh (Làm mới) bên cạnh truy vấn trong ngăn Queries And Connections của Excel để cập nhật dữ liệu.
Chờ Excel vài giây để hoàn tất việc cập nhật này, sau đó cuộn xuống để xem dữ liệu bổ sung đã được nối vào cuối bảng.
Dữ liệu mới được tự động thêm vào cuối bảng trong Excel sau khi làm mới Power Query, minh họa tính năng cập nhật.
Để giữ nguyên 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, click Properties (Thuộc tính) trong tab Table Design (Thiết kế Bảng) trên thanh Ribbon và đảm bảo tùy chọn Preserve Cell Formatting (Giữ nguyên đị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 làm mới, hãy bỏ chọn Adjust Column Width (Điều chỉnh độ rộng cột).
Kết luận
Với Power Query trong Excel, việc gộp nhiều file Excel không còn là nỗi ám ảnh. Công cụ mạnh mẽ này không chỉ giúp bạn tự động hóa hoàn toàn quy trình tổng hợp dữ liệu, loại bỏ những thao tác copy-paste thủ công tẻ nhạt và dễ gây lỗi, mà còn đảm bảo tính chính xác và khả năng cập nhật linh hoạt. Từ việc chuẩn bị dữ liệu cẩn thận, kết nối thư mục, đến việc chuyển đổi dữ liệu Excel một cách thông minh trong Power Query Editor và cuối cùng là tự động cập nhật dữ liệu chỉ bằng một cú click, bạn đã trang bị cho mình một kỹ năng vô cùng giá trị.
Thành thạo Power Query sẽ giúp bạn tối ưu Excel và tiết kiệm hàng giờ làm việc mỗi tuần, cho phép bạn tập trung vào phân tích và ra quyết định thay vì vật lộn với việc chuẩn bị dữ liệu. Hãy áp dụng ngay hướng dẫn này vào công việc của bạn để trải nghiệm sự khác biệt mà Power Query mang lại.
Ngoài việc gom dữ liệu từ nhiều file Excel trong một thư mục, bạn còn có thể sử dụng Power Query để kết hợp dữ liệu từ nhiều bảng tính Excel trong cùng một sổ làm việc bằng cách tạo các kết nối dữ liệu và kết hợp các truy vấn. Hơn nữa, Power Query Editor còn cho phép bạn nhập bảng từ các trang web trực tiếp vào Excel để phân tích. Hãy tiếp tục khám phá tiềm năng của Power Query để nâng cao khả năng xử lý dữ liệu của bạn!
