Lấy dữ liệu từ nhiều file excel

Excel cho mdtq.vn 365 Excel 2021 Excel 2019 Excel năm nhâm thìn Excel 2013 Excel 2010 Xem thêm...Ít hơn

Trong phần khuyên bảo này, bạn cũng có thể áp dụng Trình soạn thảo Truy vấn của nguồn Query nhằm nhập tài liệu trường đoản cú tệp Excel toàn thể cất đọc tin thành phầm và từ bỏ mối cung cấp cấp OData cất thông báo deals thành phầm. quý khách thực hiện công việc biến hóa cùng tổng vừa lòng, rồi phối hợp tài liệu từ bỏ cả hai mối cung cấp nhằm chế tác report "Tổng Doanh thu theo Sản phđộ ẩm với Năm".

Để tiến hành theo hướng dẫn này, bạn cần sổ làm việc Products. Trong vỏ hộp thoại Lưu Dưới dạng, hãy đặt tên tệp là Products và Orders.xlsx.

You watching: Lấy dữ liệu từ nhiều file excel


Trong tác vụ này, các bạn nhập thành phầm trường đoản cú tệp Products và Orders.xlsx (vẫn download xuống cùng thay tên làm việc trên) vào sổ làm việc Excel, tăng cấp hàng thành tiêu đề cột, sa thải một số cột và tải truy nã vấn vào trong 1 trang tính.

Cách 1: Kết nối với một sổ thao tác làm việc Excel

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

Chọn Hộp dữ > nhấn dữ > từ bỏ tệp và > trường đoản cú sổ làm việc.

Trong hộp thoại Nhập Dữ liệu, chú ý với xác định tệp Products.xlsx chúng ta sẽ cài xuống, rồi chọn Mở.

Trong ngăn Sở dẫn phía, bnóng đúp vào bảng Sản phđộ ẩm. Trình soạn thảo Power nguồn Query lộ diện.

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

Theo khoác định, nguồn Query tự động hóa thêm một vài bước có tác dụng điều dễ ợt cho chính mình. Kiểm tra mỗi bước mặt dưới Các bước Áp dụng vào ngăn uống Cài đặt vấn để tìm hiểu thêm.

Bnóng con chuột phải vào bước Nguồn, rồi lựa chọn Chỉnh sửa Cài đặt. Bước này được tạo nên khi bạn nhập sổ làm việc.

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

Bnóng chuột bắt buộc vào bước Loại đã Ttốt đổi, rồi lựa chọn Sửa Cài đặt. Cách này được tạo nên vị Power Query cơ mà đang suy ra thứ hạng tài liệu của từng cột. Chọn mũi tên xuống nghỉ ngơi bên phải tkhô nóng công thức giúp thấy toàn thể cách làm.

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

Trong bcầu này khách hàng sẽ loại bỏ tất cả các cột, ngoại trừ ProductID, ProductName, CategoryID và QuantityPerUnit.

Trong Xem trước Dữliệu , lựa chọn những cột ProductID, ProductName,CategoryIDQuantityPerUnit (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.

*

Bước 4: Tải truy tìm vấn sản phẩm

Trong công đoạn này, bạn cài đặt tróc nã vấn Products vào trang tính Excel tính.

Chọn Trang > Đóng và Tải. Truy vấn đã xuất hiện vào trang tính Excel bắt đầu.

Tóm tắt: Các bước Power Query được sản xuất trong Tác vụ 1

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

Tác vụ

Cách truy nã vấn

Công thức

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

Nguồn

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

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

Dẫn hướng

= Source

Power Query tự động hóa phân phát hiện các thứ hạng tài liệu cột

Loại vẫn Txuất xắc đổ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 những cột không giống nhằm 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 dữ liệu vào sổ thao tác Excel của mình từ nguồn cung cấp Northwind OData chủng loại trên http://services.odata.org/Northwind/Northwind.svc,bung rộng lớn bảng Order_Details, vứt bỏ cột, tính tổng loại, biến hóa hàng OrderDate bằng ProductID và Year, thay tên truy hỏi vấn cùng tắt tính năng mua xuống tầm nã vấn vào sổ thao tác Excel.

Bước 1: chuyển Kết nối nguồn cung cấp OData

Chọn Tùy chọn > nhận dữ > từ bỏ những nguồn khác > Từ Nguồn cung cấp OData.

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

Chọn OK.

Trong ngăn Sở dẫn phía, bấm lưu ban vào bảng Đơn hàng.

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

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

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

Sau khi bạn bung rộng lớn bảng Order_Details, cha cột new và các hàng bổ sung cập nhật được cấp dưỡng bảng Đơn hàng, ứng với từng mặt hàng vào bảng lồng hoặc bảng liên 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 menu 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, UnitPriceQuantity.

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

Trong công đoạn này bạn xóa toàn bộ các cột ngoài cột OrderDate, ProductID, UnitPrice với Quantity

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

Chọn cột đầu tiên, OrderID.

Shift+Click cột cuối cùng, Shipper.

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

Bấm loài chuột buộc phải vào title cột vẫn lựa chọn, rồi lựa chọn Loại vứt Cột Khác.

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

Trong đoạn này, bạn sinh sản một Cột Tùy chỉnh để đếm tổng thể chiếc cho mỗi hàng Order_Details .

Trong Xem trước Dữliệu , chọn hình tượng bảng (

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

See more: Top 10 Đội Hình Mạnh Nhất Dtcl Mùa 4, Top Đội Hình Mạnh Dtcl 11

Bnóng Thêm Cột Tùy chỉnh.

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

Trong vỏ hộp Tên cột bắt đầu, nhập Tổng Dòng.

Chọn OK.

*

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

Trong bước này, bạn thay đổi cột OrderDate nhằm kết xuất năm tháng ngày của giao dịch.

Trong Xem trước Dữliệu, click chuột bắt buộc vào cột OrderDate, rồi lựa chọn Chuyển thay đổi > Year.

Đổi thương hiệu cột OrderDate thành Year:

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

Right-Clichồng bên trên cột OrderDate, chọn Đổi tên, rồi nhập Năm.

Bước 6: Nhóm các hàng bằng ProductID với Year

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

Right-Cliông chồng một trong số title, rồi chọn Nhóm Theo.

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

Trong hộp vnạp năng lượng bản Tên cột mới, nhập Tổng Doanh thu.

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

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

Chọn OK.

*

Cách 7: Đổi thương hiệu truy tìm vấn

Trước khi bạn nhập dữ liệu bán sản phẩm vào hộp Excel, hãy đổi tên tróc nã vấn:

Trong ngnạp năng lượng Số Cài đặt, trong 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 triển khai mỗi bước, các bạn sẽ bao gồm một tróc nã vấn Tổng Doanh thu trên mối cung cấp cấp cho Northwind OData.

*

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

lúc các bạn tiến hành các chuyển động truy vấn vấn vào Power nguồn Query, quá trình truy hỏi vấn sẽ tiến hành tạo thành cùng liệt kê vào ngăn Cài đặt tróc nã vấn, vào danh sách Các bước Áp dụng. Mỗi bước truy tìm vấn có một công thức Power Query, cũng khá được điện thoại tư vấn là ngôn ngữ "M". Để hiểu biết thêm lên tiếng về bí quyết Power Query, hãy coi Tìm đọc về công thức nguồn Query.

Tác vụ

Bước truy hỏi vấn

Công thức

Kết nối cùng với nguồn cấp cho 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 những cột không giống để chỉ hiển thị những cột chúng ta 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 cho 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 chân thành và ý nghĩa rộng, 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 

thương hiệu có ý nghĩa sâu sắc rộng, Ngày Đặt mặt hàng 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)


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

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

Cách 1: Păn năn ProductID cùng với truy vấn Tổng Doanh thu

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

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

Trong hộp thoại Păn năn, 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 tróc nã vấn liên quan nhằm phối. Tổng Doanh thu đã đổi mới cột bao gồm kết cấu new cùng với biểu tượng bung rộng lớn.

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

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

Chọn Thuộc tổ chức mang lại cường độ hòa bình riêng rẽ tứ 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 Phối hận sẽ khởi tạo ra một truy hỏi vấn. Kết trái truy hỏi vấn đựng tất cả những cột từ bảng thứ nhất (Sảnphđộ ẩm ), và một cột bao gồm cấu trúc Bảng đối kháng mang lại bảng tương quan ( Tổng Doanhthu). Chọn biểu tượng Bung rộng lớn để thêm cột bắt đầu vào bảng chính trường đoản cú bảng phụ hoặc bảng tương quan.

*

Bước 2: Bung rộng cột được phối

Trong công đoạn này, chúng ta bung rộng cột sẽ pân hận cùng với thương hiệu NewColumn để tạo hai cột new vào truy nã vấn Sản phẩm: Năm cùng Tổng Doanh thu.

Trong Xem trước Dữliệu , hãy lựa chọn Bung rộng 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 toàn bộ các 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ămTổng Doanh thu.

Để tò mò xem thành phầm như thế nào cùng năm như thế nào mà lại thành phầm đạt lợi nhuận cao nhất, nên chọn Sắp xếp Giảm dần dần theoTổng Doanh thu.

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

Kết quả

*

Bước 3: Tải tầm nã vấn Tổng Doanh thu theo Sản phẩm vào Mô hình Dữ liệu Excel

Trong bước này, chúng ta cài đặt một truy vấn vấn vào một trong những Mô hình Dữ liệu Excel, nhằm tạo ra một báo cáo được liên kết cùng với tác dụng truy vấn vấn. Sau khi chúng ta thiết lập dữ liệu vào Mô Excel Liệu, bạncó thể sử dụng Power Pivot nhằm phân tích thêm dữ liệu của bản thân mình.

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

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

Kết quả

Quý khách hàng có một truy nã vấn Tổng Doanh thu theo Sản phđộ ẩm phối hợp dữ liệu từ tệp Products.xlsx với mối cung cấp cấp cho Northwind OData. Truy vấn này được áp dụng mang đến mô hình Power Pivot. Trong khi, những đổi khác đối với truy vấn vấn sửa đổi và làm new bảng hiệu quả vào Mô hình Dữ liệu.

See more: Tổng Hợp Tivi Thường Giá Rẻ Dưới 1 Triệu Đồng Đang Có Mặt Trên Thị Trường

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

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

Tác vụ

Bước truy hỏi vấn

Công thức

Pân hận ProductID vào truy vấn vấn Tổng Doanh thu

Nguồn (nguồn dữ liệu chất nhận được tân oá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 hai cột

Đã đổi tên Cột

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