Cập nhật thông tin chi tiết về Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
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. Nó đi kèm với rất nhiều tính năng giúp quản lý bộ 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. Nó 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 đó, bài viết sẽ hướng dẫn bạn thực hiện một tác vụ chung nơi Power Query tỏa sáng: Kết hợp hai bộ 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ó trong Microsoft Excel cho Windows. Nó chỉ được bao gồm trong các phiên bản Excel 2016 hoặc mới hơn, vì vậy hãy kiểm tra các phiên bản này để bắt đầu sử dụng Power Query. Người dùng mới nên đảm bảo rằng đã nắm được mọi thứ cơ bản trong Excel trước khi bắt đầu sử dụng Power Query.
Những gì Power Query cho phép bạn làm là load dữ liệu từ một số nguồn khác nhau, chỉnh sửa dữ liệu đó và sau đó nhập dữ liệu vào bảng tính Excel. Hãy tìm hiểu thêm về lý do tại sao bạn nên bắt đầu sử dụng Microsoft Power Query.
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. Để có được dữ liệu minh họa cho Power Query trong bài viết này, ví dụ sẽ sử dụng 2 bảng tính Excel riêng biệt, chứa các bộ dữ liệu tương tự, sau đó kết hợp chúng thành một.
Thiết lập dữ liệu
Hãy chọn 2 workbook Excel, lần lượt là “Cars.xlsx” và “Trucks.xlsx” trong ví dụ. Nội dung của những bảng tính này rất đơn giản. Mỗi bảng tính chứa một bảng mô tả một số phương tiện nhất định được phân loại theo các cột: “Make”, “Model”, “Color” và “Year”.
Ví dụ cũng đang tạo ra một workbook trống có tiêu đề “Vehicles.xlsx” để thực hiện công việc của mình.
Để đơn giản, bài viết đang sử dụng hai bộ dữ liệu có cùng thông tin tiêu đề. Mục tiêu ở đây là tạo ra một bảng mới với cả thông tin về xe hơi và xe tải.
Những workbook này đã được lưu vào máy tính. Bây giờ, hãy bắt tay làm việc với Power Query nào!
Load 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ở workbook chứa dữ liệu đã tạo. Trong trường hợp này, workbook là “Vehicles.xlsx”.
Power Query rất dễ sử dụng. Bạn không cần phải mở workbook có chứa dữ liệu để trích xuất những gì mình cần. Trong “Vehicles.xlsx”, điều hướng đến tab Data chứa các tùy chọn cho workbook.
Bạn sẽ thấy tùy chọn “Get Data” trong các thanh công cụ. 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.
Bạn muốn làm việc với một workbook Excel, vì vậy hãy chọn tùy chọn “From File”, theo sau là “From Workbook”.
Trình chỉnh sửa sẽ mở file explorer và bạn có thể điều hướng đến workbook ở bất cứ đâu trên PC. Trước tiên, bạn sẽ chọn file “Cars.xlsx”.
Khi bạn chọn xong file của mình, menu Power Query Navigator sẽ load để hiển thị cho bạn bản xem trước của trang tính đã chọn. Lưu ý file “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ẽ load trên menu giống như nó xuất hiện trong bảng tính! Nhấp vào “Transform Data” để mở dữ liệu trong trình chỉnh sửa.
Sử dụng Power Query Editor
Power Query Editor có rất nhiều tùy chọn, đừng lo lắng về những thứ đó ngay bây giờ, vì bạn sẽ chỉ làm quen với những điều cơ bản trong bài viết này. Thoạt nhìn, dữ liệu đã được upload đú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 các tiêu đề được đặt tên ở trên cùng.
Bên phải bạn là một menu có tên là “Query Settings” có chứa một cửa sổ “Applied Steps”. Các bước ở đây là thay đổi được thực hiện cho bảng. Lưu ý phần này để sử dụng sau.
Ở phía xa bên trái, nhấp vào biểu tượng “Queries” sẽ mở rộng menu và hiển thị tên của bảng bạ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ả trong thanh công cụ này.
Để nối hai bảng với nhau, cả hai bảng cần phải được upload lên Power Query. Bạn đã có một cái, hãy upload nốt cái kia.
Thêm nguồn dữ liệu thứ hai
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” và sau đó là “Excel” để mở file explorer. Điều hướng đến “Trucks.xlsx” để chọn file cần nhập.
Giống như truy vấn đầu tiên, Navigator sẽ xuất hiện, cho phép bạn chọn bảng của mình từ bảng tính. Chọn bảng tính, rồi nhấp vào OK. Bây giờ, bạn sẽ thấy cả bảng ô tô và xe tải trong thanh công cụ Queries. Nhấp vào một trong hai bảng sẽ hiển thị dữ liệu trong bảng tính đó.
Nối các bảng dữ liệu
Khi đã thực hiện xong tất cả các bước chuẩn bị, 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à trên thanh công cụ, hãy chọn “Append Queries” trong mục “Combine”.
Cửa sổ Append sẽ xuất hiện và nhắc đến bảng mà bạn muốn kết hợp vào bảng đã chọn. Hãy nhớ rằng bạn đang ở trên bảng “Cars” và muốn kết hợp bảng “Trucks” với nó. Nhấp vào bảng “Trucks” bên trong “Table to append” để kết hợp và chọn OK.
Đây là kết quả:
Lưu ý rằng tất cả dữ liệu xe hơi và xe tải sẽ được đặt trong cùng một bảng. Các bộ dữ liệu đã được kết hợp! Ngoài ra, hãy lưu ý rằng Power Query đã tạo ra một “bước” mới trong danh sách của bạn, gọi là “Appended Query”.
Load dữ liệu vào trang tính
Điều cuối cùng cần làm là lấy dữ liệu được lưu trữ trong Power Query Editor và load nó vào bảng 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 vừa tạo vào Vehicles ở định dạng bảng. Khi nhấp vào, những điều sau đây sẽ xảy ra:
Trình chỉnh sửa sẽ lưu các bước truy vấn và thoát.
Workbook sẽ xuất hiện với dữ liệu được load dưới dạng bảng Excel.
Excel cũng sẽ mở menu Workbook Queries ở bên phải trang tính.
Bạn đã sẵn sàng làm việc với bảng hoàn toàn mới mà không cần mở bảng tính!
Bây giờ, bạn đã học được cách nối thêm 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 load 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. Bạn có thể tạo biểu đồ từ các bảng Excel hoặc chèn ảnh hàng loạt vào ô Excel.
Nhập Dữ Liệu Từ Cơ Sở Dữ Liệu Bằng Truy Vấn Cơ Sở Dữ Liệu Gốc (Power Query)
Lưu ý: Power Query còn được biết đến với tên Get & Transform trong Excel 2016. Thông tin được cung cấp ở đây áp dụng cho cả hai. Để tìm hiểu thêm, hãy xem mục Get & Transform trong Excel 2016.
Power query cung cấp cho bạn sự linh hoạt để nhập dữ liệu từ nhiều loại cơ sở dữ liệu mà nó hỗ trợ (không bao gồm Microsoft Access). Nó có thể chạy truy vấn cơ sở dữ liệu gốc, mà có thể giúp bạn tiết kiệm thời gian cần thiết để xây dựng truy vấn bằng cách sử dụng giao diện Power query. Tính năng này đặc biệt hữu ích cho việc sử dụng các truy vấn phức tạp đã tồn tại – và bạn có thể không muốn hoặc biết cách dựng lại bằng cách sử dụng giao diện Power query.
Ghi chú Bảo mật: Một ý định của truy vấn cơ sở dữ liệu bản địa là không được cắt ngang. Tuy nhiên, Power query không đảm bảo rằng truy vấn sẽ không ảnh hưởng đến cơ sở dữ liệu. Nếu bạn chạy truy vấn cơ sở dữ liệu gốc do một người dùng khác viết, bạn sẽ được nhắc để đảm bảo rằng bạn đã biết về các truy vấn sẽ được đánh giá với thông tin xác thực của bạn.
Trong cửa sổ bật lên Microsoft SQL cơ sở dữ liệu :
Xác định máy chủ và cơ sở dữ liệu từ vị trí bạn muốn nhập dữ liệu bằng cách dùng truy vấn cơ sở dữ liệu gốc.
Bung rộng trường câu lệnh SQL và dán hoặc nhập truy vấn cơ sở dữ liệu gốc của bạn, rồi bấm OK.
Nếu đây là lần đầu tiên bạn đang kết nối với máy chủ này, bạn sẽ thấy lời nhắc để chọn chế độ xác thực để kết nối với cơ sở dữ liệu. Chọn một chế độ xác thực thích hợp, rồi tiếp tục.
Lưu ý: Nếu bạn không có quyền truy nhập vào nguồn dữ liệu (cả máy chủ và database), bạn sẽ thấy lời nhắc để yêu cầu truy nhập vào máy chủ/cơ sở dữ liệu (nếu thông tin truy nhập-yêu cầu được xác định trong Power bi cho nguồn dữ liệu).
Nếu kết nối được thiết lập, dữ liệu kết quả được trả về trong trình soạn thảo truy vấn.
Định hình dữ liệu như bạn muốn, sau đó bấm áp dụng & đóng để lưu các thay đổi và nhập dữ liệu vào trang tính.
Nhập dữ liệu từ nguồn dữ liệu bên ngoài
Pivottable Nâng Cao: Kết Hợp Dữ Liệu Từ Nhiều Sheet
Khi bạn muốn tạo một PivotTable, bạn sẽ làm gì nếu bạn có dữ liệu trong các sheet khác nhau? Nếu bạn đang sử dụng Excel 2013, thì bạn có một phương pháp được tổ chức hợp lý để thực hiện việc này. Có một kỹ thuật được gọi là Mô hình Dữ liệu và nó sử dụng dữ liệu quan hệ theo cách cơ sở dữ liệu làm việc.
Trong bài này, tôi sẽ hướng dẫn cho bạn mọi thứ mà bạn cần để tạo một PivotTable trong Excel 2013 từ dữ liệu trong nhiều sheet, sử dụng Mô hình Dữ liệu.
Video Hướng dẫn
Bạn có thể làm theo hướng dẫn này bằng cách sử dụng tập tin Excel của riêng bạn. Hoặc nếu bạn thích, hãy tải xuống tập tin zip kèm theo hướng dẫn này, nó có chứa một bảng tính mẫu có tên gọi là Pivot Consolidate.xlsx.
Kiểm tra Dữ liệu
Bảng tính này có ba sheet: Customer Info, Order Info và Payment Info.
Nhấp vào sheet Customer Info và xem nó có chứa số đơn hàng và tên và trạng thái của khách hàng.
Nhấp vào bảng Order Info và xem nó có chứa số đơn hàng, cũng như các trường ngày tháng, các sản phẩm được đặt, và các sản phẩm có phải là hữu cơ hay không.
Nhấp vào sheet Payment Info và xem nó có chứa số đơn hàng, giá mỗi đơn hàng, phương thức thanh toán và đơn hàng được đặt bởi một khách hàng mới hay khách hàng cũ.
Bằng cách kết nối tất cả các sheet này trong bảng tác vụ của PivotTable, chúng ta có thể chọn dữ liệu từ mỗi sheet. Vì số đơn hàng tồn tại trong cả ba sheet, nên chúng sẽ trở thành các điểm kết nối. Cơ sở dữ liệu gọi đây là một primary key (khoá chính). Lưu ý rằng không phải lúc nào cũng cần thiết phải có một khóa chính, nhưng nó làm giảm khả năng xảy ra lỗi.
Tạo bảng
Trước khi tạo ra PivotTable, chúng ta hãy tạo ra một bảng từ mỗi sheet.
Quay trở lại Bảng Khách hàng, sau đó nhấp vào bất kỳ nơi nào trong vùng dữ liệu. Chuyển đến tab Insert trên thanh ribbon, sau đó nhấp vào biểu tượng Table.
Hộp thoại Create Table xác định chính xác khu vực của bảng. Hộp đánh dấu ở phía dưới cũng sẽ xác định rằng hàng đầu tiên của bảng là dành cho tiêu đề. (Nếu không, hãy chọn tùy chọn đó.)
Hộp thoại Create Table sẽ lấy chính xác vùng dữ liệu
Nhấp OK, và bây giờ bạn có một bảng với các nút lọc dữ liệu. Bạn có thể nhấp vào bên trong nó để bỏ chọn, nếu bạn muốn xem nó rõ hơn (chỉ cần không bấm bên ngoài bảng). Thanh ribbon cũng hiển thị tab Design cho bảng. Ở phía bên trái của dải ribbon, hộp Table Name hiển thị tên tạm thời của Table 1. Xóa nó, và gọi nó là Customer_Info (sử dụng một dấu gạch dưới thay vì một khoảng trắng). Sau đó nhấn Enter.
Áp dụng một cái tên cho mỗi bảng
Lặp lại quy trình này trên sheet Order Info và Payment Info. Đặt tên các bảng tương ứng là Order_Info và Payment_Info.
Bây giờ chúng ta đã sẵn sàng để thêm PivotTable.
Chèn PivotTable
Trên sheet Payment Info, đảm bảo con trỏ ở đâu đó trong bảng. Quay trở lại tab Insert trên ribbon, và nhấp vào biểu tượng PivotTable (đó là biểu tượng đầu tiên).
Hộp thoại xuất hiện sẽ xác định bảng phù hợp và chọn PivotTable sẽ nằm trong một bảng tính mới. Ở phía dưới cùng, nhấp vào hộp đánh dấu Add this data to the Data Model. Sau đó bấm OK.
Adding the data to the Data Model là thứ cho phép các kết nối hoạt động
Bạn sẽ có một PivotTable trên một sheet mới, sẽ có một cửa sổ tác vụ ở phía bên phải màn hình, và thanh ribbon sẽ hiển thị tab Analyze.
Cửa sổ tác vụ hiển thị bảng và các trường chỉ cho sheet hiện tại, do đó hãy nhấp vào ALL để thấy tất cả các bảng mà bạn đã tạo. Nhưng trước khi có thể sử dụng chúng, chúng ta phải kết nối chúng lại với nhau, và điều đó có nghĩa là tạo ra các mối quan hệ. Nhấp vào nút Relationships trên thanh ribbon.
Thiết lập Quan hệ Bảng
Nhấp vào nút đó sẽ hiển thị hộp thoại Manage Relationships. Nhấp vào nút New và hiển thị hộp thoại Create Relationships. Chúng ta sẽ tạo ra hai mối quan hệ bằng cách sử dụng trường Order # làm yếu tố kết nối.
Từ danh sách sổ xuống, chọn Payment_Info cho table, và bên cạnh nó, chọn Order # từ trình đơn thả xuống Column. Trên hàng thứ hai, chọn Customer_Info từ danh sách sổ xuống Related Table và bên cạnh nó, chọn Order # từ danh sách Related Column.
Có ba bảng, từ đó tạo ra hai mối quan hệ
Nhấp vào OK và chúng ta thấy rằng mối quan hệ được liệt kê trong hộp Manage Relationships.
Lặp lại quá trình này để tạo ra một mối quan hệ kết nối Payment_Info với Order_Info, tương tự sử dụng trường Order #. Hộp Manage Relationships bây giờ sẽ giống như sau:
Hộp thoại Manage Relationships sẽ hiển thị các mối quan hệ mà bạn tạo ra
Lưu ý rằng không cần phải tạo mối quan hệ giữa bảng Order_Info và Customer_Info, vì chúng được tự động kết nối thông qua bảng Payment_Info.
Nhấp vào nút Close ở dưới cùng của cái hộp. Bây giờ cuối cùng chúng ta có thể kéo các trường vào bảng PivotTable.
Chèn các Trường vào Bảng PivotTable
Trong phần ALL của bảng tác vụ, nhấp vào các mũi tên nhỏ để thấy cả ba bảng, để bạn thấy các trường của chúng. Kéo các trường vào các vùng PivotTable như sau:
Bây giờ bạn có thể sử dụng và sửa đổi điều này giống như bất kỳ PivotTable khác.
Phần tóm tắt
Nếu bạn đang tìm kiếm các cách tối ưu để trình bày dữ liệu của mình, thì Envato Market có nhiều template Excel và PowerPoint, cũng như các script và ứng dụng để chuyển đổi dữ liệu Excel sang các định dạng thân thiện với web và ngược lại.
Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot
Cách làm như sau:
Hình 1: Cách nạp dữ liệu từ Power Query
Bước 2: Duyệt tới thư mục nơi chứa file Access và chọn file
Bước 3: Đánh dấu vào mục Select Multiple Items (1), sau đó chọn các bảng dữ liệu (2) rồi bấm Load (3)
Hình 2: Các thao tác chọn bảng để nạp vào Power Query
Sau khi Load dữ liệu xong, chúng ta sẽ có kết quả là cửa sổ Queries & Connections xuất hiện phía bên phải màn hình làm việc của Excel, đồng thời trong mục Queries sẽ bao gồm 5 Queries
Hình 3: kết quả sau khi nạp dữ liệu bằng Power QueryLưu ý:
Có thể bạn đang có câu hỏi: Tại sao chúng ta cần nạp dữ liệu thông qua Power Query mà không nạp trực tiếp vào Power Pivot?
Câu trả lời là: Bởi vì không phải bảng dữ liệu nào (hay bảng tham chiếu nào) cũng đúng tiêu chuẩn làm việc của Power Pivot. Có những bảng có nhiều hơn 1 dòng tiêu đề, có những bảng mà có gộp ô, trộn ô tại dòng tiêu đề, hay có dòng trống trong bảng… Rất ít trường hợp bảng dữ liệu đúng tiêu chuẩn ngay và có thể dùng trực tiếp được. Thường bạn chỉ gặp khi học, khi đọc sách, xem tài liệu mẫu, còn trong thực tế thì không được như vậy. Do đó để đảm bảo bạn có thể ứng dụng kiến thức trong sách này vào thực tế, chúng ta cần biết cách sử dụng Power Query thay vì chỉ dùng trực tiếp trong Power Pivot.
Hướng dẫn về Power Query trong Power Pivot: https://blog.gitiho.com/wp-admin/post.php?post=45404&action=edit Hướng dẫn sử dụng Power Query để sửa đổi bảng dữ liệu trong Power Pivot: https://blog.gitiho.com/wp-admin/post.php?post=45432&action=edit
Thời đại công nghệ 4.0 đang dần đi vào cuộc sống đòi hỏi mọi người phải tự trang bị kiến thức tin học cho phù hợp để có thể bắt kịp nhưng thay đổi nhanh chóng này. Chẳng ai khác ngoài bạn hiểu mình cần trang bị thêm kiến thức gì. Hãy tìm hiểu ngay TẠI ĐÂY những khóa học hấp dẫn, có tính thực tế cao của Gitiho và đăng ký nhận tư vấn ngay hôm nay.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Bạn đang xem bài viết Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!