Bạn đang xem bài viết Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. 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.
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,…
Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot
Trong những bài viết trước, chúng ta đã tìm hiểu về các thao tác đều được thực hiện trực tiếp trong cửa sổ làm việc của Power Pivot: từ nạp dữ liệu cho tới thay đổi các bảng. Tuy nhiên trong thực tế việc này lại khá bất tiện so với thực hiện thông qua công cụ Power Query.
Power Query là gì?
Power Query cũng là 1 add-in giống như Power Pivot nhưng được tích hợp sẵn trên Excel từ phiên bản Office 2016 là nhóm Get & Transform trong thẻ Data. Nếu máy tính của bạn đang sử dụng Office ở phiên bản thấp hơn thì bạn có thể tải Add-in này về theo địa chỉ:
https://www.microsoft.com/en-us/download/confirmation.aspx?id=39379
Sau khi tải về bạn tiến hành cài đặt là sử dụng được.
Hình 1: Vị trí nhóm Get & Transform Data trên thanh công cụ tại thẻ Data
Power Query có tác dụng chính là:
Hình 2: Các tác dụng chính của Power Query
Kết nối: tạo kết nối tới dữ liệu trong nền tảng điện toán đám mây, trong một dịch vụ, hoặc cục bộ.
Chuyển đổi (tái cấu trúc): định hình dữ liệu để đáp ứng nhu cầu của bạn; dữ liệu nguồn ban đầu vẫn không thay đổi.
Kết hợp: tạo một mô hình dữ liệu từ nhiều nguồn dữ liệu và có được góc nhìn độc đáo về dữ liệu
Chia sẻ: sau khi truy vấn của bạn hoàn tất, bạn có thể lưu, chia sẻ hoặc sử dụng nó cho báo cáo
Hướng dẫn cách sửa nội dung bảng đã nạp vào Power Pivot: https://blog.gitiho.com/wp-admin/post.php?post=45384&action=edit Hướng dẫn cách sử dụng Power Query để nạp dữ liệu vào Power Pivot https://blog.gitiho.com/wp-admin/post.php?post=45417&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,…
Hướng Dẫn Cách Sử Dụng Vba Để Lọc Dữ Liệu Vào Báo Cáo Chi Tiết
Trong công việc, bạn có từng gặp phải những câu hỏi này không:
Lập báo cáo chi tiết như thế nào?
Làm thế nào để lấy dữ liệu từ một bảng theo những yêu cầu nhất định?
Cách trích xuất dữ liệu theo điều kiện để đưa sang 1 bảng khác?
Bài viết sau đây sẽ giúp bạn trả lời những câu hỏi đó. Đồng thời bạn cũng có thể khám phá ra một cách làm mới rất hay khi sử dụng VBA vào việc lọc dữ liệu vào Báo cáo chi tiết.
Tại tab Developer, các bạn chọn nút lệnh Record Macro
Thao tác 1: Chọn chức năng Data / Filter cho bảng dữ liệu
Thao tác 2: Lọc cột Nhà cung cấp theo tên Nhà cung cấp ở ô I4
Thao tác 3: Lọc ngày ở cột Ngày theo thông tin ngày ở ô I2 và I3
Bước 2: Đọc nội dung macro
Mở cửa sổ VBA, chúng ta xem nội dung Macro vừa ghi được
Range(“A2:F2”).Select Selection.AutoFilter
Nội dung này là : Chọn vùng ô từ A2 đến F2, mở chức năng Auto filter
ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tu?n”
Nội dung này là: Lọc dữ liệu ở cột thứ 1 (cột Ngày), điều kiện lọc là Lớn hơn hoặc bằng ngày 01/10/2017, và nhỏ hơn hoặc bằng 31/10/2017
Đừng bỏ lỡ: lớp học Excel kế toán với các chuyên gia
Bước 3: Tinh gọn macro để sử dụng
Chúng ta chú ý vào các vùng điều kiện (Criteria1, Criteria2) ở trong câu lệnh trong VBA
* Câu lệnh lọc NCC
ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tu?n”
Thay “Anh Tu?n” bằng ô I4 (dòng 4, cột 9, sheet 1) trong sheet1 như sau:
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=2, Criteria1:=Sheet1.Cells(4, 9).value
Vì giá trị ngày tháng trong Excel về bản chất là dạng số nên có thể chuyển đổi về dạng CLng(…)
* Tinh gọn và hoàn thiện Code VBA
Range(“A2:F2”).Select + Selection.AutoFilter = Range(“A2:F2”).AutoFilter
Range(“B2”).Select Dòng này có thể bỏ đi
Bước 4: Copy dữ liệu và paste sang bảng chi tiết và hoàn thiện code
Từ bảng dữ liệu đã được lọc, chúng ta copy toàn bộ kết quả đã lọc được rồi dán vào Bảng chi tiết để lấy kết quả.
Code cho bước này như sau: (Các bạn có thể record macro cho thao tác này rồi chọn lọc code)
Vì bảng chi tiết chỉ cần lấy nội dung Tên hàng, số lượng, đơn giá, thành tiền nên nội dung sẽ lấy từ cột C tới cột F, bắt đầu từ ô C3 tới F47 (cuối bảng). Chỉ copy những giá trị xuất hiện sau khi lọc
ActiveSheet.Range(“$C$3:$F$47”).SpecialCells(xlVisible).Copy
Paste dữ liệu: Dán vào Bảng chi tiết, bắt đầu từ ô H6, chỉ dán dữ liệu dạng Value (giá trị)
Range(“H6”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
Sau khi Paste dữ liệu xong, chúng ta sẽ bỏ lệnh Copy và Filter đi bằng 2 dòng lệnh:
Application.CutCopyMode = False ‘Hủy bỏ chế độ Cut Copy trong excel (khi bạn Cut/Copy thì Excel sẽ lưu nội dung đó trong bộ nhớ, và tô đường viền nhấp nháy ở nội dung đó. Khi không dùng đến bạn có thể bỏ đi)
Range(“A2:F2”).AutoFilter ‘Hủy bỏ chế độ Filter. Lặp lại thao tác này để hủy bỏ chế độ filter khi không dùng đến nữa (trả về trạng thái ban đầu khi chưa dùng Filter)
Bước 5: Kiểm tra code bằng phím F8
Sau khi hoàn thành xong code trong VBA, chúng ta bấm nút F8 để kiểm tra xem code đó hoạt động ra sao
Nếu code hoạt động đúng thì sẽ ra kết quả như sau:
Thao tác như sau:
Khi thay đổi dữ liệu ở vùng I2:I4 thì chúng ta muốn cập nhật nội dung của báo cáo chi tiết. Do đó chúng ta sẽ gọi ra Macro vừa hoàn thành ở phần trên.
* Bổ sung:
Do mỗi điều kiện sẽ cho kết quả nhiều / ít khác nhau, do đó để có thể xác định rõ kết quả của Bảng chi tiết chỉ đúng với điều kiện được chọn, chúng ta cần làm sạch vùng Bảng chi tiết trước khi dán dữ liệu vào.
Đặt dòng Code xóa dữ liệu lên đầu Macro:
Những nội dung học được qua bài này là:
Cách Record macro và tinh gọn code từ thao tác Record
Cách đọc hiểu code trong VBA
Cách gán Macro vào sự kiện xảy ra trong Sheet (ví dụ với sự kiện thay đổi một số nội dung trong sheet)
Trình tự logic của câu lệnh trong VBA
Trong công việc chúng ta sẽ gặp phải những việc này rất nhiều, được ứng dụng nhiều trong thực tế.
Bài viết này sẽ tạo tiền đề cho các bạn làm quen với VBA, cách học VBA dễ dàng và làm quen dần với kỹ thuật VBA giúp tự động hóa khi sử dụng Excel.
Cảm ơn các bạn đã theo dõi.
Tải về tài liệu kèm theo bài học
Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query
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.
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot trên website Hoisinhvienqnam.edu.vn. 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. Chúc bạn một ngày tốt lành!