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 đượ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.
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)
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
Mã Hóa Dữ Liệu Tự Động: Cách Thay Thế Tất Cả Chữ Số Bằng Dấu X
Dạo gần đây tôi có rơi vào một trường hợp như sau: một công ty đối tác muốn được xem qua bố cục các bài báo cáo và thuyết trình mà tôi đã chuẩn bị từ trước. Để tránh để lộ những số liệu bí mật của công ty mình, tôi phải nghĩ cách để mã hóa dữ liệu lại. Một trong những phương pháp mà tôi nghĩ đến đó là xóa đi tất cả bảng biểu và thay thế tất cả các chữ số bằng một ký tự đặc biệt (ở đây tôi lựa chọn dấu X để mã hóa số liệu).
Sau khi xác định được cách thức thực hiện, việc đầu tiên là phải tìm cách viết đè nội dung mới lên toàn bộ văn bản. Lúc đầu tôi băn khoăn liệu có một công thức đơn giản có dạng “Tìm kiếm/Thay thế”, với chức năng tự động biến tất cả các chữ số thành dấu X hay không. Không may là có vẻ chức năng “Tìm kiếm/Thay thế” cơ bản trong Microsoft không thực sự hỗ trợ điều đó. Vì thế, tôi bị kẹt giữa 2 sự lựa chọn, hoặc là thực hiện 10 lệnh tìm kiếm và thay thế liên tục cho từng tệp dữ liệu một (từ số 0 đến số 9), hoặc tự viết ra một macro VBA mới. Bạn biết tôi đã chọn phương án nào rồi đấy?
Sub ClassifyNumbers()'PURPOSE: Replace all numerical values with an "x" in the active PowerPoint Presentation'SOURCE: chúng tôi fnd As VariantDim rplc As VariantDim NumberArray As VariantDim TxtRng As TextRangeDim TmpRng As TextRangeDim sld As SlideDim shp As Shape'Find/Replace Variables NumberArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) rplc = "x"'Loop Through Each Slide For Each sld In ActivePresentation.Slides For y = LBound(NumberArray) To UBound(NumberArray) For Each shp In sld.Shapes fnd = NumberArray(y) If shp.HasTextFrame Then If shp.TextFrame.HasText Then Set TxtRng = shp.TextFrame.TextRange Set TmpRng = TxtRng.Replace(FindWhat:=fnd, _ ReplaceWhat:=rplc, WholeWords:=False) End If End If 'Replace Other Instances (if necessary) Do While Not TmpRng Is Nothing Set TmpRng = TxtRng.Replace(FindWhat:=fnd, _ ReplaceWhat:=rplc, WholeWords:=False) Loop Next shp Next y Next sldMsgBox "All numbers are now classified (ie " & rplc & rplc & ")!"End Sub Sub ClassifyNumbers()'PURPOSE: Replace all numerical values with an "x" in the active Word Document'SOURCE: chúng tôi rplc As VariantDim NumberArray As VariantApplication.ScreenUpdating = False'Find/Replace Variables NumberArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) rplc = "x"'Loop through all numbers and replace them For x = LBound(NumberArray) To UBound(NumberArray) With ActiveDocument.Content.Find .Forward = True .Wrap = wdFindStop .Execute _ FindText:=NumberArray(x), _ ReplaceWith:=rplc, _ Replace:=wdReplaceAll, _ MatchCase:=False End With Next xApplication.ScreenUpdating = TrueMsgBox "All numbers are now classified (ie " & rplc & rplc & ")!"End SubHướng Dẫn Cách Viết Code Tự Động Ghi Nhận Thời Gian Nhập Dữ Liệu Bằng Vba
Code tự động ghi nhận thời gian nhập dữ liệu
Đặc điểm hàm NOW trong Excel
Thông thường để xác định một thời điểm cụ thể trong Excel, chúng ta có hàm NOW. Ví dụ như sau:
Hàm NOW sẽ ghi nhận cả Ngày/tháng/năm và Giờ:phút:giây theo thời gian thực.
Tuy nhiên nếu sử dụng hàm NOW, kết quả của hàm sẽ luôn tự động thay đổi mỗi khi Excel thực hiện tính toán. Trong khi đó mục tiêu của chúng ta là chỉ ghi nhận kết quả thời điểm khi chúng ta kết thúc thao tác nhập dữ liệu, và kết quả đó không được thay đổi.
Để làm điều đó mà không dùng tới VBA, bạn phải làm thủ công các bước sau:
Bước 1: Viết hàm NOW, sau đó copy kết quả của hàm
Bước 2: Dán kết quả của hàm để tránh hàm tự động cập nhật kết quả
Nếu việc nhập dữ liệu liên tục thì cách làm trên khá bất tiện vì số thao tác nhiều và thủ công. Do đó để có thể tự động hóa việc này, chúng ta ứng dụng VBA trong Excel như sau:
Bước 1: Sử dụng hàm NOW trong VBA
Ví dụ bạn nhập dữ liệu vào ô A3, muốn điền kết quả thời gian thực vào ô B3, câu lệnh như sau:
ActiveSheet.Range(“B3”).Value = Now
Bước 2: Gắn câu lệnh hàm NOW vào sự kiện khi nhập dữ liệu
Để có thể tự động điền thời gian thực bởi hàm NOW trong VBA, chúng ta phải gắn với sự kiện thay đổi dữ liệu trong ô A3.
Ví dụ bạn thay đổi dữ liệu tại ô A3 trong Sheet1 thì sẽ gọi sự kiện Worksheet_Change tại Sheet1 và đưa câu lệnh trong bước 1 vào
2, Chọn đối tượng Worksheet
3, Chọn sự kiện Change
4, Một Sub được tự động tạo ra là Worksheet_Change
5, Viết code bên trong Sub đó:
Cú pháp:
If Not Application.Intersect(Range(“A3”), Target) Is Nothing Then … End If
Nghĩa là xét sự thay đổi có diễn ra trong ô A3 hay không
Cú pháp:
ActiveSheet.Range(“B3”).Value = Now
Nghĩa là gán giá trị Now vào ô B3
Khi đó quay trở lại Excel, nhập dữ liệu tại ô A3 sẽ có ngay giá trị thời gian thực tại ô B3
Bước 3: Tổng quát câu lệnh, mở rộng phạm vi tác động
Để câu lệnh được tối ưu, chúng ta không chỉ xét 1 ô A3 mà có thể sẽ áp dụng trong 1 vùng rộng hơn: A2:A100, hoặc hơn nữa là cả cột A.
Tại bất cứ ô nào được nhập nội dung trong cột A, tại cột B trên dòng tương ứng cũng sẽ lấy giá trị kết quả hàm NOW
Như vậy câu lệnh tổng quát là:
If Not Application.Intersect(Range(“A2:A100”), Target) Is Nothing Then
If Range(“A” & Target.Row).Value = “” Then
Range(“B” & Target.Row).ClearContents
Else
Range(“B” & Target.Row).Value = Now
End If
End If
Trong đó:
Intersect(Range(“A2:A100”), Target) là vùng diễn ra sự kiện. Bạn thay đổi tọa độ trong Range(“….”) là được.
Target chính là mục tiêu (ô được chọn).
chúng tôi là số dòng của ô được chọn.
Logic là:
Xét sự kiện diễn ra trong vùng A2:A100
Nếu giá trị trong cột A tại dòng tác động là rỗng (không có nội dung) thì
Xóa nội dung trong cột B, tại dòng tương ứng với dòng đang tác động
Còn lại / Mặt khác (giả định trên bị sai)
Gán giá trị kết quả hàm NOW vào cột B, tại dòng đang tác động
Kết thúc hàm if xét giá trị rỗng
Kết thúc hàm if xét vùng diễn ra sự kiện
Kết quả thu được như sau:
Link tải file mẫu: http://bit.ly/2KcLlQS
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 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!