Xem Nhiều 12/2022 #️ Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu / 2023 # Top 14 Trend | Trucbachconcert.com

Xem Nhiều 12/2022 # Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu / 2023 # Top 14 Trend

Cập nhật thông tin chi tiết về Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu / 2023 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.

Một trong những công cụ hữu ích phục vụ cho quá trình phân tích dữ liệu là Power Query. Đây là công cụ vô cùng mạnh mẽ để tự động quy trình nhập dữ liệu. Tiết kiệm thời gian nhập liệu mà vẫn đảm bảo về độ chính xác của dữ liệu.

Tổng quan về Power Query

Power Query là một công cụ phân tích dữ liệu, báo cáo và chia sẻ cực kỳ mạnh mẽ. Trước đây, nó chỉ là một công cụ Add-in trong Office 2010, 2013. Đối với Office 2016, Power Query được tích hợp trong Excel với tên Get & Transform.

Với chức năng Power Query này, bạn có thể thực hiện: 

Kết nối:

Kết nối phần mềm Excel với các dữ liệu trên đám mây, dịch vụ hoặc dữ liệu cục bộ. 

Chuyển đổi:

Định dạng dữ liệu đúng theo nhu cầu của người dùng.

Kết hợp:

kết hợp nhiều dữ liệu lại với nhau để có góc nhìn bao quát về dữ liệu

Chia sẻ:

Chia sẻ dữ liệu đã tạo đến nhiều người

Nhờ có chức năng Power Query, dữ liệu được trình bày theo cách khoa học nhất, dễ dàng sửa đổi, tính toán, tạo báo cáo và chia sẻ. Từ đó, nó giúp mang lại hiệu quả cao hơn trong công việc.

Hướng dẫn sử dụng Power Query trong Excel

Kết nối 

Power Query có thể kết nối với một nguồn dữ liệu đơn lẻ, như một bảng tính Excel, hoặc nhiều dữ liệu khác nhau từ các nguồn cấp dữ liệu và các dịch vụ đám mây.

Để thực hiện kết nối dữ liệu, truy cập thẻ Data, nhấn New Query. Bạn sẽ có các tùy chọn kết nối: From File (từ một File Excel khác), from Database (từ một cơ sở dữ liệu) và From Other Sources (Web, Facebook,…)

Chuyển đổi

Chức năng này của Power Query cho phép loại bỏ cột, thay đổi kiểu dữ liệu hoặc sáp nhập bảng. Mục đích là định dạng dữ liệu theo nhu cầu người dùng nhằm đẩy mạnh phân tích.

Đặc biệt, Power Query không thay đổi dữ liệu nguồn ban đầu và ghi lại những bước đã thao tác để bạn có thể theo dõi và hoàn tác khi cần. 

Bạn có thể sửa đổi dữ liệu trên bảng Query Editor.

Kết hợp 

Bạn có thể kết hợp nhiều dữ liệu với nhau bằng cách chọn Combine Queries.

Chia sẻ

Để chia sẻ dữ liệu từ Power Query, bạn cần phải đăng nhập Excel và đăng ký Power BI cho Microsoft 365. Sau khi chia sẻ, các dữ liệu được chia sẻ này trở nên sẵn dùng trong kho siêu dữ liệu Power BI. Những người được chia sẻ sẽ tìm thấy nó và sử dụng.

Power Query là một công cụ phức tạp và khó sử dụng. Người dùng cần có sự thực hành thường xuyên để sử dụng thành thạo nhưng nó sẽ cải thiện tốc độ công việc rất nhiều. 

Nếu bạn muốn tìm hiểu về Power Query cũng như các công cụ phân tích dữ liệu khác trong Excel, hãy tham gia khóa học  Phân tích và thấu hiểu dữ liệu nhân sự – kinh doanh trong doanh nghiệp sẽ tổ chức khóa đầu tiên tại Trung Tâm Tin Học Mos.

*Power Query image in the featured image used by permission of Excel Off The Grid.

Thông tin liên hệ

Trung tâm tin học văn phòng MOS – Viện kinh tế và thương mại Quốc tế – Đại học Ngoại Thương

Email: tinhocmos.edu.vn@gmail.com

Hotline: 0914 444 343

SĐT: 0909 111 485 (Nhánh 1)

Chuẩn Hoá Kiểu Dữ Liệu “Khó Đỡ” Với Công Cụ Power Query Trong Excel / 2023

Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn sử dụng công cụ Power Query để chuẩn hoá lại những kiểu bố trí dữ liệu khó, phức tạp trong Excel.

Chuẩn hoá dữ liệu, hay nói cách khác là đưa dữ liệu về dạng chuẩn có cấu trúc đúng với các trường thông tin phù hợp, được bố trí một cách khoa học, hợp lý, nhằm giúp cho việc quản lý, kiểm soát cũng như đưa ra các báo cáo thống kê, phân tích một cách nhanh chóng, chính xác và hiệu quả,….

Cấu trúc của bảng dữ liệu chuẩn sẽ giúp cho bạn không phải đau đầu, mất quá nhiều thời gian suy nghĩ với những công thức Excel phức tạp, công thức mảng, những công thức lồng nhau dài cả cây số,….

Bạn nghĩ như thế nào về kiểu bố trí dữ liệu như trên???. Rất khó để “Yêu” được phải không nào.

Các thông tin về giới tính, tuổi, trình độ nằm trong 1 cột. Các cột Mã nhân viên, SĐT thì chỗ có dữ liệu, chỗ không có. Không đỡ nổi phải không.

Phải chuyển đổi, những chuyển như nào cho nó đúng đây?, cần thiết kế lại với những trường thông tin nào đây?

Câu trả lời cho vấn đề trên là:

Cột thông tin trong dữ liệu gốc chứa các thông tin về Giới tính, Tuổi, Trình độ của mỗi nhân viên. Vấn đề của ta nằm ở đây và cách giải quyết là ta nên tách các thông tin này ra làm tiêu đề cột với các cột là Giới tính, Tuổi, Trình độ.

Với các cột về Mã nhân viên, SĐT ta cũng phải xử lý như nào để bỏ hết các khoảng trống đó đi, chỉ giữ lại những dòng có dữ liệu.

Cách thực hiện với Power Query

Trong phần này, Học Excel Online sẽ xử lý dữ liệu “Khó đỡ” và rất chi là “Oái oăm” trên với công cụ Power Query trong Excel. Ta sẽ có các bước sau:

Bước 1: Xử lý ban đầu

Sau khi đã tạo bảng, đặt một cái tên nào đó cho dữ liệu (mình đã đặt là DATA). Ta thực hiện Input bảng DATA này vào Power Query Editor. Xoá bỏ bước Changed Type và nó sẽ trông như sau:

– Trong ví dụ này không có. Tuy nhiên, trường hợp có giá trị null trong cột Thông tin thì bạn sẽ lọc và loại bỏ những giá trị null này đi (ở đây mình vẫn thực hiện để đảm bảo những lần sau nếu có dữ liệu trống trong bảng DATA gốc thì thao tác này sẽ loại bỏ những dòng trống đó cho chúng ta).

Sau khi thực hiện như trên, dữ liệu ban đầu sẽ giống như sau (hình đã cắt bớt 1 phần):

Tiếp theo, ta sẽ thêm cột Modulo

Tại Tab Add Column, bạn tìm tới nhóm From Number, trong danh sách sổ xuống của phần Standard bạn chọn Modulo, sẽ có 1 hộp thoại xuất hiện ra và bạn gõ vào số 3 như hình.

Vì sao phải làm việc đó?. Ta thấy thông tin của mỗi Mã nhân viên sẽ có 3 dòng đó là giới tính, tuổi, và trình độ (nhân viên nào cũng có 3 dòng này).

Việc đánh số Modulo này sẽ như sau:

Số 0 sẽ tương ứng với giới tính

Số 1 sẽ tương ứng với tuổi

Số 2 sẽ tương ứng với trình độ

Sau khi thực hiện các bước trên thì bảng DATA của ta sẽ trông như này đây:

Chọn cột Modulo, sau đó vào Tab Transform và chọn Pivot Column.

Values Column bạn chọn là Thông tin

Trong phần Advanced options bạn chọn Don’t Aggregate (Không tổng hợp)

Gần hoàn thành rồi đấy . Ở bước này, ta thực hiện các việc sau:

Sau đó, lọc cột có tiêu đề là 0, lọc bỏ cá giá trị null.

Kết quả sau bước này như sau, nhìn có vẻ khả quan hơn rồi phải không nào

Thực hiện xoá cột Index đi (nếu tại Bước 2 bạn đã thực hiện bằng cách thêm 1 cột Index như mình làm)

Đổi tên các cột:

Cột có tên 0 thành cột Giới tính

Cột có tên 1 thành Tuổi

Cột có tên 2 thành Trình độ

Chính sửa định dạng cho các trường (cột) dữ liệu, cũng như di chuyển vị trí giữa các cột cho phù hợp,….

Và đây, ta sẽ Load bảng dữ liệu này vào Excel.

Thực hiện Close & Load To… rồi chọn cách xem DATA sau khi đã xử lý cũng như chọn vùng để đặt DATA này.

Sau khi đã thực hiện các thao tác trong Power Query, và tất nhiên là những thao tác mà bạn thực hiện này đã được M Code ghi lại. Khi bạn thêm dữ liệu vào DATA gốc ban đầu thì bạn chỉ cần Refresh, ngay lập tức bảng kết quả sẽ được cập nhật cho bạn một cách nhanh chóng, chính xác.

Học Excel Online hy vọng qua bài viết này bạn đã nắm được cách thực hiện chuyển đổi kiểu dữ liệu phức tạp như trên về dạng chuẩn, một bảng dữ liệu được biến đổi về đúng cấu trúc, bố trí một cách khoa học, thống nhất giữa các trường thông tin.

Bạn có thể thấy, việc tổ chức 1 bảng dữ liệu không khoa học, không tuân theo cấu trúc nào cả sẽ gây ra rất nhiều khó khăn trong quá trình tổng hợp, báo cáo cũng như lấy đi của bạn rất nhiều thời gian trong công việc,… Với công cụ rất mạnh là Power Query có thể sẽ giúp bạn xử lý được. Tuy nhiên, nếu như bạn tổ chức dữ liệu tốt ngay từ ban đầu thì mình vẫn nghĩ là tốt hơn.

Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay

Tự Động Lọc Dữ Liệu Trước Khi Nhập Từ Sql Server Trong Excel / 2023

Vì vậy, nếu tôi nhập dữ liệu vào bảng, hãy áp dụng bộ lọc cho, ví dụ: Quốc gia là Vương quốc Anh, đóng tệp và mở lại vào tuần tới, lý tưởng là chỉ nên truy vấn và nhập dữ liệu cho Vương quốc Anh. Và khi tôi chọn một quốc gia khác, chỉ nên nhập dữ liệu cho quốc gia đó. Tôi cần các lựa chọn để được liệt kê từ bảng.

Bây giờ, tôi không tin các bảng có khả năng đó. Nhưng làm thế nào về Máy cắt, PivotTable, PowerView hoặc PowerP Pivot?

Đánh giá cao bất kỳ ý tưởng.

Bạn hoàn toàn có thể làm điều này với Bảng, cũng như PowerpOLL và Data Explorer. Cách dễ nhất và phương pháp ưa thích của tôi hiện tại là tạo một truy vấn được tham số hóa trong Excel. Để thực hiện việc này, hãy sử dụng Truy vấn Wizard và ở cuối, thêm các ràng buộc cho từng tham số bạn muốn vượt qua. Khi điều này được thực hiện, bạn có thể chỉnh sửa Thuộc tính kết nối và gán Tham số được trả về dưới dạng giá trị tĩnh, hộp thoại hoặc ô bảng tính.

Trong Cửa sổ Truy vấn MS, nhấp vào Show/Hide Criteria button và chọn trường Tiêu chí. Đây sẽ là những gì gọi các tham số của bạn để quay trở lại SQL. [.__.]

Ví dụ: Nếu bạn có trường ngày và chọn nó trong trường Tiêu chí, sau đó thêm Giá trị giữa [bắt đầu] và [kết thúc]. Điều này sẽ tạo một tham số và MS Query sẽ yêu cầu bạn điền cả hai giá trị và trả về ngay bộ dữ liệu phù hợp. Nếu bạn kiểm tra câu lệnh SQL mà nó đã tạo, nó sẽ thêm mệnh đề WHERE (dataource.date giữa? Và?).

Đối với mỗi tham số, bạn có thể chọn tham số đó [.__.]

Nhắc bạn để cung cấp một giá trị; hoặc là

Sử dụng một giá trị cụ thể; hoặc là

Nhận giá trị từ một ô bảng tính, với tùy chọn tự động cập nhật dữ liệu bảng của bạn bất cứ khi nào giá trị ô đó thay đổi. [.__.]

Là một tùy chọn bổ sung, bạn có thể sử dụng Xác thực dữ liệu trên ô bạn chọn cho tham số của mình, cho phép bạn giới hạn các giá trị có thể chọn. Bạn thậm chí có thể có các ô xác thực đến từ một bảng được cung cấp từ các giá trị từ cơ sở dữ liệu của bạn, đảm bảo rằng chỉ các giá trị được giữ trong cơ sở dữ liệu có thể được chọn.

Đặt giá trị paramater của bạn cho từng tham số và nhấp OK thông qua tất cả các hộp thoại.

Tôi sẽ thường tạo Bảng Pivot dựa trên Bảng được cung cấp từ truy vấn được tối ưu hóa để trả về các giá trị cho các phạm vi ngày cụ thể, được cập nhật tự động (ví dụ: 6 tháng trước).

EDIT : Để trả về nhiều giá trị để sử dụng trong mệnh đề SQL IN, bạn chỉ cần sửa đổi cửa sổ Tiêu chí trong hộp Truy vấn MS. Ví dụ: nếu bạn có tên trường của Quốc gia, hãy chọn nó trong Trường Tiêu chí, sau đó cho Giá trị nhập IN ([Đầu tiên], [Thứ hai], [Thứ ba]). Chọn Trả về dữ liệu cho Excel và MS Query sẽ yêu cầu nhập các giá trị thủ công. Khi bạn quay lại Excel, bạn có thể sửa đổi lại các tham số và trỏ chúng vào ba ô trong bảng tính của bạn, mỗi ô có thể bị ràng buộc bởi Xác thực dữ liệu. Mặc dù có thể có giới hạn về số lượng tham số bạn có thể vượt qua như thế này, Excel dễ dàng xử lý ba tham số, do đó, một số ít có thể không nằm ngoài câu hỏi.

Nếu bạn không phiền khi sử dụng VBA thì cách ‘nhẹ’ để thực hiện điều này là bằng cách tự động tạo truy vấn SQL.

Ví dụ.

Bảng ẩn ‘Các biến’ chứa một bảng ‘Table_Query_SALES_EMPLOYEES’. Điều này được SQL đưa ra để chỉ trả về mức tối thiểu cần thiết cho lựa chọn param. Một cái gì đó như SELECT DISTINCT AccountDirector FROM InvoicedSales ORDER BY AccountDirector.

Sau đó, tôi có một phạm vi được đặt tên ‘Sales_Emp viên’ mà phạm vi của nó tham chiếu là =Table_SQL_SALES_EMPLOYEES[AccountDirector]. Điều này được ánh xạ tới hộp tổ hợp, có Phạm vi đầu vào được đặt thành ‘Sales_Emp viên’. Điều này sẽ gửi một số đến một ô bạn chọn (E5 trong trường hợp của tôi) của mục trong danh sách đã chọn. Để trả về giá trị thực tế theo trích xuất SQL gốc, bạn cần sử dụng =OFFSET(Table_SQL_SALES_EMPLOYEES[[#Headers],[AccountDirector]],$E$5,0). Cái này được đặt tên là ‘Slp’.

.: Bây giờ chúng tôi có một hộp lựa chọn thả xuống của tất cả các giám đốc tài khoản có thể. Cũng có một số khác cho quý tài chính, nhưng cùng một nguyên tắc :.

Khi lựa chọn được thay đổi trong hộp tổ hợp, nó sẽ gọi macro ‘changeFilter ()’

Sub changeFilter() Dim wb As Workbook Set wb = Excel.ActiveWorkbook Dim ws As Worksheet Set ws = Excel.ActiveWorkbook.Sheets("Lookups") Dim conn As Excel.WorkbookConnection Dim slp As Name Set slp = wb.Names("Slp") Dim qtr As Integer qtr = wb.Names("qtr").RefersToRange.Value2 Dim query As String ' Adjusted Sales Consolidated Set conn = wb.Connections("SQL_ADJUSTEDSALES_CONSOLIDATED") query = "SELECT * FROM InvoicedSales WHERE AccountDirector=" & "'" & slp.RefersToRange.Value2 & "'" conn.OLEDBConnection.CommandText = query conn.Refresh

Về cơ bản, tôi định cấu hình trước các bảng/pivots bằng cách nối chúng với cơ sở dữ liệu với các biến được mã hóa cứng và chọn các tùy chọn ‘Làm mới dữ liệu khi mở tệp’ và ‘Xóa dữ liệu khỏi phạm vi dữ liệu ngoài trước khi lưu sổ làm việc’ – điều này giữ cho tệp nhỏ như không có gì thực sự được cứu.

Sau đó, bộ lọc thay đổi ghi đè biến mã hóa cứng bằng truy vấn SQL được tạo động bằng cách sử dụng tham số đã chọn và sau đó làm mới bảng.

Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot / 2023

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 Query

Lư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 Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu / 2023 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!