Cách tổng hợp dữ liệu từ nhiều file excel

     
Excel mang đến myga.vn 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Xem thêm...Ít hơn

Trong phần hướng dẫn này, bạn cũng có thể thực hiện Trình soạn thảo Truy vấn của nguồn Query để nhập dữ liệu từ tệp Excel tổng thể đựng công bố thành phầm với từ mối cung cấp cấp OData đựng lên tiếng giao dịch sản phẩm. Bạn tiến hành công việc thay đổi cùng tổng thích hợp, rồi kết hợp dữ liệu trường đoản cú cả nhì nguồn để tạo nên báo cáo "Tổng Doanh thu theo Sản phđộ ẩm và Năm".

Để triển khai theo phía dẫn này, bạn cần sổ làm việc Products. Trong hộp thoại Lưu Dưới dạng, hãy viết tên tệp là Products and Orders.xlsx.

Bạn đang xem: Cách tổng hợp dữ liệu từ nhiều file excel


Trong tác vụ này, chúng ta nhập thành phầm trường đoản cú tệp Products & Orders.xlsx (sẽ cài xuống với đổi tên ngơi nghỉ trên) vào sổ thao tác Excel, upgrade mặt hàng thành title cột, thải trừ một trong những cột cùng thiết lập truy tìm vấn vào một trang tính.

Cách 1: Kết nối với cùng một sổ thao tác Excel

Tạo sổ thao tác làm việc Excel.

Chọn Hộp dữ > nhận dữ > từ bỏ tệp với > từ sổ làm việc.

Trong vỏ hộp thoại Nhập Dữ liệu, chú ý và định vị tệp Products.xlsx các bạn đang tải xuống, rồi lựa chọn Mở.

Trong ngăn Sở dẫn phía, bnóng lưu ban vào bảng Sản phđộ ẩm. Trình biên soạn thảo Power nguồn Query xuất hiện.

Cách 2: Kiểm tra các bước truy hỏi vấn

Theo mang định, Power nguồn Query tự động hóa thêm một vài ba bước làm điều thuận lợi cho bạn. Kiểm tra từng bước một mặt bên dưới Các bước Áp dụng vào ngăn Cài đặt vấn để tham khảo thêm.

Bấm con chuột đề xuất vào bước Nguồn, rồi chọn Chỉnh sửa Cài đặt. Bước này được tạo ra khi bạn nhập sổ làm việc.

Bấm con chuột đề xuất vào bước Dẫn hướng, rồi lựa chọn Chỉnh Cài đặt. Bước này được tạo thành khi chúng ta lựa chọn bảng từ vỏ hộp thoại Dẫn phía.

Bnóng con chuột cần vào bước Loại đã Txuất xắc thay đổi, rồi lựa chọn Sửa Cài đặt. Bước này được tạo thành vày Power nguồn Query nhưng mà đang suy ra giao diện tài liệu của từng cột. Chọn mũi tên xuống ở mặt yêu cầu tkhô nóng cách làm để thấy toàn cục cách làm.

Bước 3: Xóa những cột khác để chỉ hiển thị những cột bạn muốn

Trong bcầu này người dùng sẽ loại bỏ tổng thể các cột, ngoại trừ ProductID, ProductName, CategoryID và QuantityPerUnit.

Trong Xem trước Dữliệu , chọn những cột ProductID, ProductName,CategoryIDcùng QuantityPerUnit (thực hiện Ctrl+Bấm hoặc Shift+Bấm).

Chọn Loại quăng quật cột > xóa các cột khác.

*

Cách 4: Tải truy hỏi vấn sản phẩm

Trong đoạn này, các bạn tải truy nã vấn Products vào trang tính Excel tính.

Chọn Trang > Đóng và Tải. Truy vấn đang xuất hiện thêm trong trang tính Excel new.

Tóm tắt: Các bước nguồn Query được chế tạo vào Tác vụ 1

Lúc bạn triển khai những hoạt động tầm nã vấn trong Power Query, quá trình tróc nã vấn sẽ tiến hành tạo nên và liệt kê vào ngăn Cài đặt truy nã vấn, vào danh sách Các bước Áp dụng. Mỗi bước truy hỏi vấn bao gồm một bí quyết Power nguồn Query, cũng rất được Hotline là ngữ điệu "M". Để biết thêm công bố về công thức nguồn Query, hãy xem Tạo bí quyết nguồn Query Excel.

Tác vụ

Cách tầm nã vấn

Công thức

Nhập sổ làm cho Excel việc

Nguồn

= Excel. Workbook(File.Contents("C:Products và Orders.xlsx"), null, true)

Chọn bảng Sản phẩm

Dẫn hướng

= Source

Power nguồn Query auto phân phát hiện nay những kiểu dữ liệu cột

Loại sẽ Tgiỏi đổi

= Table.TransformColumnTypes(Products_Table,"ProductID", Int64.Type, "ProductName", type text, "SupplierID", Int64.Type, "CategoryID", Int64.Type, "QuantityPerUnit", type text, "UnitPrice", type number, "UnitsInStock", Int64.Type, "UnitsOnOrder", Int64.Type, "ReorderLevel", Int64.Type, "Discontinued", type logical)

Xóa các cột không giống để chỉ hiển thị những cột các bạn muốn

Đã thải trừ những cột khác

= Table.SelectColumns(FirstRowAsHeader,"ProductID", "ProductName", "CategoryID", "QuantityPerUnit")


Trong tác vụ này, bạn nhập tài liệu vào sổ thao tác làm việc Excel của chính bản thân mình tự mối cung cấp cấp cho Northwind OData chủng loại trên http://services.odata.org/Northwind/Northwind.svc,bung rộng bảng Order_Details, loại bỏ cột, tính tổng chiếc, chuyển đổi mặt hàng OrderDate bởi ProductID cùng Year, thay tên truy nã vấn và tắt nhân tài download xuống truy hỏi vấn vào sổ làm việc Excel.

Bước 1: gửi Kết nối mối cung cấp cung cấp OData

Chọn Tùy chọn > thừa nhận dữ > từ bỏ những nguồn không giống > Từ Nguồn cấp cho OData.

Trong vỏ hộp thoại Nguồn cấp OData Feed, nhập URL mang đến mối cung cấp cấp cho Northwind OData.

Chọn OK.

Trong ngăn uống Bộ dẫn hướng, bnóng lưu ban vào bảng Đơn mặt hàng.

Bước 2: Bung rộng bảng Order_Details

Trong công đoạn này, các bạn bung rộng lớn bảng Order_Details liên quan đến bảng Đơn hàng, nhằm phối kết hợp các cột ProductID, UnitPrice cùng Quantity từ bỏ Order_Details thành bảng Đơn hàng . Thao tác Bung rộng kết hợp những cột trường đoản cú bảng tương quan thành một bảng chủ thể. Khi tầm nã vấn chạy, các mặt hàng từ bỏ bảng liên quan (Order_Details) được phối hợp thành những mặt hàng cùng với bảng thiết yếu ( Đơnhàng).

Trong Power Query, cột đựng bảng liên quan có mức giá trị Bản ghi hoặc Bảng trong ô. Đây được Gọi là cột tất cả cấu tạo. Bản ghi cho biết thêm một phiên bản ghi có liên quan riêng lẻ cùng diễn đạt mối quan hệ một đối một với dữ liệu ngày nay hoặc bảng chủ yếu. Bảng biểu lộ một bảng có tương quan với thể hiện mối quan hệ một đối những với bảng hiện nay hoặc bảng chủ yếu. Cột bao gồm kết cấu biểu hiện quan hệ trong nguồn tài liệu bao gồm mô hình quan hệ. lấy ví dụ như, cột tất cả cấu tạo chứng minh một thực thể có links khóa nước ngoài trong nguồn cung cấp OData hoặc mối quan hệ khóa ngoại trong các đại lý dữ Squốc lộ Server tài liệu.

Sau khi chúng ta bung rộng bảng Order_Details, tía cột mới và các sản phẩm bổ sung được cung cấp bảng Đơn hàng, ứng với từng mặt hàng trong bảng lồng hoặc bảng tương quan.

Trong Xem trước Dữliệu , cuộn theo hướng ngang Order_Details cột.

Trong cột Order_Details, chọn hình tượng bung rộng ().

Trong thực đơn thả xuống Bung rộng:

Chọn (Chọn Tất cả Cột) để xóa toàn bộ những cột.

Chọn ProductID, UnitPricecùng Quantity.

Bước 3: Xóa những cột không giống để chỉ hiển thị các cột bạn muốn

Trong đoạn này chúng ta xóa tất cả các cột kế bên cột OrderDate, ProductID, UnitPrice cùng Quantity

Trong Xem trước Dữliệu, nên chọn lựa những cột sau đây:

Chọn cột trước tiên, OrderID.

Shift+Clichồng cột ở đầu cuối, Shipper.

Ctrl+Cliông xã vào các cột OrderDate, Order_Details.ProductID, Order_Details.UnitPrice và Order_Details.Quantity.

Bấm con chuột nên vào tiêu đề cột đang chọn, rồi lựa chọn Loại bỏ Cột Khác.

Bmong 4: Tính dòng tổng cộng đến mỗi hàng Order_Details

Trong đoạn này, bạn chế tạo một Cột Tùy chỉnh nhằm đếm tổng thể cái cho từng mặt hàng Order_Details .

Trong Xem trước Dữliệu , lựa chọn biểu tượng bảng (

*
) ở góc cạnh bên trên thuộc phía bên trái của phiên bản coi trước.

Xem thêm: Nếu Nước Mắt Trôi Đi Yêu Thương Ngày Cũ Mãi Xa, Lời Bài Hát Muốn Nói Với Em (T Team)

Bấm Thêm Cột Tùy chỉnh.

Trong hộp thoại Cột Tùy chỉnh, vào vỏ hộp Công thức cột thiết lập, nhập * .

Trong vỏ hộp Tên cột new, nhập Tổng Dòng.

Chọn OK.

*

Bmong 5: Chuyển đổi cột năm OrderDate

Trong bước này, các bạn biến đổi cột OrderDate nhằm kết xuất năm tháng ngày của đơn hàng.

Trong Xem trước Dữliệu, nhấp chuột nên vào cột OrderDate, rồi chọn Chuyển đổi > Year.

Đổi tên cột OrderDate thành Year:

Bấm đúp chuột vào cột OrderDate và nhập Năm hoặc

Right-Cliông chồng trên cột OrderDate, lựa chọn Đổi tên, rồi nhập Năm.

Bước 6: Nhóm những mặt hàng bởi ProductID với Year

Trong Bản coi trước Dữliệu , chọn YearOrder_Details.ProductID.

Right-Cliông xã một trong những tiêu đề, rồi chọn Nhóm Theo.

Trong hộp thoại Nhóm Theo:

Trong vỏ hộp văn bạn dạng Tên cột mới, nhập Tổng Doanh thu.

Trong danh sách thả xuống Thao tác, chọn Tính tổng.

Trong danh sách thả xuống Cột, lựa chọn Tổng Dòng.

Chọn OK.

*

Bước 7: Đổi tên tróc nã vấn

Trước khi chúng ta nhập tài liệu bán sản phẩm vào vỏ hộp Excel, hãy đổi tên tầm nã vấn:

Trong ngnạp năng lượng Số Cài đặt, trong vỏ hộp Tên, nhập Tổng Doanh thu.

Kết quả: Truy vấn cuối cùng cho Tác vụ 2

Sau khi chúng ta thực hiện mỗi bước, các bạn sẽ bao gồm một truy tìm vấn Tổng Doanh thu trên mối cung cấp cấp Northwind OData.

*

Tóm tắt: Các bước nguồn Query được tạo vào Tác vụ 2 

Khi chúng ta tiến hành những vận động tróc nã vấn vào Power Query, các bước truy hỏi vấn sẽ tiến hành tạo ra cùng liệt kê vào ngăn Cài đặt truy nã vấn, trong danh sách Các bước Áp dụng. Mỗi bước truy vấn vấn có một cách làm Power Query, cũng được Gọi là ngữ điệu "M". Để hiểu thêm ban bố về bí quyết Power Query, hãy xem Tìm phát âm về phương pháp Power Query.

Tác vụ

Cách truy nã vấn

Công thức

Kết nối với mối cung cấp cung cấp OData

Nguồn

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, )

Chọn một bảng

Dẫn hướng

= Source

Bung rộng bảng Order_Details

Bung rộng Order_Details

= Table.ExpandTableColumn(Orders, "Order_Details", "ProductID", "UnitPrice", "Quantity", "Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity")

Xóa các cột không giống để chỉ hiển thị các cột bạn muốn

RemovedColumns

= Table.RemoveColumns(#"Expvà Order_Details","OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper")

Tính dòng tổng cộng đến mỗi hàng Order_Details

Đã thêm Tùy chỉnh

= Table.AddColumn(RemovedColumns, "Custom", each * )

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each * )

Đổi thành thương hiệu bao gồm ý nghĩa hơn, Tổng Lne

Đã đổi tên Cột

= Table.RenameColumns(InsertedCustom,"Custom", "Line Total")

Chuyển đổi cột OrderDate thành năm

Năm Đã trích xuất

= Table.TransformColumns(#"Grouped Rows","Year", Date.Year, Int64.Type)

Tgiỏi đổi thành 

tên gồm ý nghĩa hơn, Ngày Đặt sản phẩm cùng Năm

Cột được đổi tên 1

Table.RenameColumns

(TransformedColumn,"OrderDate", "Year")

Nhóm các hàng theo ProductID và Year

GroupedRows

= Table.Group(RenamedColumns1, "Year", "Order_Details.ProductID", "Total Sales", each List.Sum(), type number)


nguồn Query được cho phép chúng ta kết hợp các tróc nã vấn, bằng phương pháp phối hận hoặc chắp thêm truy tìm vấn. Thao tác Phối được tiến hành bên trên bất cứ tầm nã vấn như thế nào của nguồn Query với hình dáng bảng, độc lập cùng với nguồn dữ liệu nhưng mà dữ liệu bắt đầu từ đó. Để biết thêm công bố về Việc phối kết hợp những mối cung cấp dữ liệu, hãy xem Kết đúng theo các truy vấn vấn.

Trong tác vụ này, các bạn phối hợp các truy vấn vấn Sản phđộ ẩm cùng Tổng Doanh thu bằng cách sử dụng truy hỏi vấn Păn năn và Bung rộng lớn, rồi sở hữu truy tìm vấn Tổng Doanh thu theo Sản phẩm vào Mô hình Dữ liệu Excel Hợp nhất.

Bước 1: Phối hận ProductID với truy vấn vấn Tổng Doanh thu

Trong sổ có tác dụng Excel, dẫn đào bới tróc nã vấn Sản phẩm trên tab trang tính Sản phẩm.

Chọn một ô trong truy tìm vấn, rồi chọn Truy vấn>phối hận.

Trong hộp thoại Phối hận, lựa chọn Sản phđộ ẩm làm bảng đầu tiên, rồi lựa chọn Tổng Doanh thu làm bảng phụ hoặc truy vấn tương quan để păn năn. Tổng Doanh thu sẽ trở nên cột tất cả cấu trúc mới với biểu tượng bung rộng.

Để khớp Tổng Doanh thu với Sản phẩm theo ProductID, chọn cột ProductID trường đoản cú bảng Sản phẩm với cột Order_Details.ProductID tự bảng Tổng Doanh thu .

Trong vỏ hộp thoại Mức độ Riêng tư:

Chọn Thuộc tổ chức cho cường độ hòa bình riêng rẽ bốn của khách hàng với cả nhì nguồn tài liệu.

Chọn Lưu.

Kết quả

Thao tác Pân hận sẽ tạo nên ra một truy tìm vấn. Kết quả truy hỏi vấn chứa tất cả những cột từ bảng trước tiên (Sảnphẩm ), với một cột tất cả kết cấu Bảng đối chọi cho bảng tương quan ( Tổng Doanhthu). Chọn biểu tượng Bung rộng để thêm cột bắt đầu vào bảng chủ yếu từ bỏ bảng prúc hoặc bảng liên quan.

*

Cách 2: Bung rộng lớn cột được phối

Trong đoạn này, chúng ta bung rộng cột đang phối hận cùng với tên NewColumn để tạo ra nhì cột new vào truy vấn Sản phẩm: Năm cùng Tổng Doanh thu.

Trong Xem trước Dữliệu , hãy chọn Bung rộng lớn hình tượng () bên cạnh NewColumn.

Trong danh sách thả xuống Bung rộng:

Chọn (Chọn Tất cả Cột) để xóa tất cả những cột.

Chọn Năm và Tổng Doanh thu.

Chọn OK.

Đổi thương hiệu nhì cột này thành Năm cùng Tổng Doanh thu.

Để tìm hiểu coi sản phẩm như thế nào cùng năm làm sao nhưng mà sản phẩm đạt doanh số tối đa, hãy chọn Sắp xếp Giảm dần dần theoTổng Doanh thu.

Đổi tên truy vấn thành Tổng Doanh thu theo Sản phẩm.

Kết quả

*

Cách 3: Tải truy nã vấn Tổng Doanh thu theo Sản phđộ ẩm vào Mô hình Dữ liệu Excel

Trong đoạn này, bạn tải một tróc nã vấn vào trong 1 Mô hình Dữ liệu Excel, nhằm chế tạo một report được liên kết với công dụng tầm nã vấn. Sau khi bạn mua dữ liệu vào Mô Excel Liệu, bạnrất có thể sử dụng Power nguồn Pivot nhằm đối chiếu thêm tài liệu của chính bản thân mình.

Chọn Trang > Đóng & Tải.

Trong hộp thoại Nhập Dữ liệu, hãy đảm bảo rằng các bạn lựa chọn Thêm dữ liệu này vào Mô hình Dữ liệu. Để biết thêm đọc tin về phong thái sử dụng hộp thoại này, hãy lựa chọn vệt chấm hỏi (?).

Kết quả

quý khách gồm một tróc nã vấn Tổng Doanh thu theo Sản phđộ ẩm phối hợp dữ liệu trường đoản cú tệp Products.xlsx và nguồn cấp Northwind OData. Truy vấn này được vận dụng mang lại mô hình Power Pivot. Hình như, các biến đổi đối với truy nã vấn sửa đổi cùng có tác dụng bắt đầu bảng tác dụng trong Mô hình Dữ liệu.

Tóm tắt: Các bước Power Query được tạo nên trong Tác vụ 3

khi các bạn triển khai các hoạt động truy nã vấn Phối trong nguồn Query, các bước truy tìm vấn sẽ được chế tác và liệt kê vào ngăn uống Cài đặt truy vấn vấn, trong list Các bước Đã áp dụng. Mỗi bước tầm nã vấn có một cách làm nguồn Query, cũng được call là ngôn từ "M". Để biết thêm lên tiếng về phương pháp Power Query, hãy coi Tìm gọi về công thức Power nguồn Query.

Tác vụ

Cách truy hỏi vấn

Công thức

Pân hận ProductID vào tróc nã vấn Tổng Doanh thu

Nguồn (nguồn dữ liệu được cho phép toán Phối)

= Table.NestedJoin(Products, "ProductID", #"Total Sales", "Order_Details.ProductID", "Total Sales", JoinKind.LeftOuter)

Bung rộng cột phối

Tổng Doanh thu Được bung rộng

= Table.ExpandTableColumn(Source, "Total Sales", "Year", "Total Sales", "Total Sales.Year", "Total Sales.Total Sales")

Đổi tên nhị cột

Đã thay tên Cột

= Table.RenameColumns(#"Expanded Total Sales","Total Sales.Year", "Year", "Total Sales.Total Sales", "Total Sales")