Cập nhật nội dung chi tiết về Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu mới nhất trên website Comforttinhdauthom.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp 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)
Cách Gộp Dữ Liệu Từ Nhiều Bảng Vào 1 Bảng Sử Dụng Power Query
Việc tổ chức dữ liệu trên Excel phụ thuộc rất nhiều yếu tố: đặc thù của nguồn nhập dữ liệu, đặc thù của dạng dữ liệu… nên không phải lúc nào chúng ta cũng có được 1 bảng dữ liệu đúng ý muốn ngay từ đầu. Do đó để có thể phục vụ việc lập báo cáo trong Excel được tốt hơn thì chúng ta cần phải tổ chức lại dữ liệu. Một trong những công việc thường gặp đó là gộp dữ liệu từ nhiều bảng tính (có thể trên nhiều sheet) vào 1 bảng tính (trên 1 sheet). Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query.
Mục tiêu của chúng ta như sau:
1. Chuẩn bị dữ liệu
Các bạn có thể tải về file dữ liệu gốc để thực hành theo bài viết tại địa chỉ: http://bit.ly/2JBYRh1
Power Query là 1 công cụ mới có trên Office từ các phiên bản Office 2016, Office 365 (đã tích hợp sẵn). Nếu bạn sử dụng Office phiên bản thấp hơn như 2010, 2013 thì bạn có thể tải Add-in Power Query trên hệ thống của Microsoft Office
Link tải Power Query: https://www.microsoft.com/vi-vn/download/details.aspx?id=39379
2. Nguyên tắc của việc gộp dữ liệu
Để tránh các lỗi có thể xảy ra trong quá trình gộp dữ liệu thì chúng ta phải tuân thủ một số nguyên tắc sau:
Dữ liệu từ các bảng tính sẽ được nối tiếp nhau trong 1 bảng chung
Các bảng tính phải có cấu trúc giống nhau: số cột, tên các cột… để đảm bảo nguyên tắc nối tiếp nhau
Các bảng tính phải bao gồm dòng tiêu đề và hạn chế dòng trống xen kẽ dữ liệu (đảm bảo tính liên tục của dữ liệu)
3. Cách thực hiện gộp dữ liệu bằng Power Query
a. Chuyển các bảng cần gộp về dạng Table Data
Tại mỗi bảng dữ liệu thành phần chúng ta sẽ chuyển về dạng Table data bằng cách:
Chọn bảng dữ liệu đó
Sử dụng phím tắt Ctrl+T (phím tắt của chức năng Insert/Table)
Xác nhận việc chuyển đổi này
Table1 – Cửa hàng A
Table2 – Cửa hàng B
Table3 – Cửa hàng C
Tại sao lại cần chuyển về dạng Table? Bởi trong Power Query việc kết nối dữ liệu từ các Table sẽ dễ hơn, nhanh hơn. Sau khi kết nối các bảng thì chúng ta có thể trả dạng Table về dạng Range một cách dễ dàng, không bị ảnh hưởng gì.
b. Sử dụng Power Query kết nối các bảng Table
Cửa sổ Power Query sẽ mở ra. Trong cửa sổ này chúng ta chọn mục Append Queries ngay trong thẻ Home
Let / Source: là các bảng dữ liệu nguồn. Ở đây chú ý Name'”Table1″ chính là tên của các Table đã được tạo ở trên. Chúng ta sẽ thay đổi Source này thành 3 Source ứng với 3 Table
Let/ Appended: là lệnh nối các bảng dữ liệu. Đặt các Source vào trong câu lệnh = Table.combine({…})
in / Appended: trích xuất kết quả của Appended
Các lệnh khác có thể bỏ qua (ví dụ như changed type)
Do đó chúng ta sẽ sửa lại câu lệnh trên thành:
c. Xuất dữ liệu từ Power Query về 1 Sheet kết quả
Để trích xuất dữ liệu từ Power Query về Sheet thì chúng ta làm như sau:
Trong cửa sổ Close & Load to chọn:
Select how you want to view this data in your workbook: Chọn dạng dữ liệu muốn hiển thị. Thông thường có thể chọn Table
Where do you want to put the data? Chọn nơi bạn muốn đặt bảng dữ liệu kết quả này. Nếu đã có vị trí tại 1 sheet xác định thì chọn Existing worksheet, còn nếu muốn đưa vào 1 sheet mới thì chọn New worksheet.
Để chuyển từ dạng Table về dạng bảng thường (Range) thì chúng ta bấm vào mục Convert to range trong thẻ Table Tools
Cách Lọc Dữ Liệu Excel Đơn Giản Bằng Công Cụ Filter
Cách Lọc Dữ Liệu Excel Đơn Giản Bằng Công Cụ Filter
Việc sử dụng bảng tính excel đã không còn trở nên xa lạ đối với tất cả mọi người, tất cả mọi ngành nghề. Vậy, để việc sử dụng excel được thuận tiện và nhanh chóng trong cách xử lý dữ liệu thì chúng ta nên biết các mẹo để lọc dữ liệu excel. Vừa tiết kiệm thời gian, xử lý dữ liệu nhanh còn giúp cho hiệu suất công việc tăng cao.
Bộ lọc trong excel là gì?
Cách thêm bộ lọc Filter trong Excel
Cách 1: Trên tab Data, trong nhóm Sort và Filter, bạn chọn nút Filter để thực hiện quá trình lọc dữ liệu excel.
Cách 2: Trên tab Home, trong nhóm Editing, bạn chọn Sort à Filter
Cách 3: Cách nhanh nhất bạn có thể sử dụng phím tắt: Ctrl Shift + L
Lọc một vùng dữ liệu
Để có thể thực hiện lọc dữ liệu một vùng trong excel các bạn thực hiện theo các bước sau:
Bước 1: Chọn vùng dữ liệu cần lọc ( nên để tiêu đề ở đầu mỗi cột để thao tác được chính xác). Trên thanh Data, bạn kích chuột chọn Filter.
Bước 3: Chọn phép so sánh thích hợp ( VD: để chọn các số giữa giới hạn nhỏ nhất và lớn nhất, bạn chọn Between).
Bước 4: Trong hộp Custom AutoFilter, chọn khoảng giới hạn để lọc dữ liệu.
Bước 5: Chọn Ok để hoàn thành việc lọc một vùng dữ liệu.
Lọc dữ liệu trong bảng excel
Bước 1: Chọn vùng dữ liệu cần lọc ( nên để tiêu đề ở đầu mỗi cột để thao tác được chính xác). Trên thanh Data, bạn kích chuột chọn Filter.
Bước 4: Chọn Ok. Dữ liệ bạn lọc sẽ được hiển thị còn những dữ liệu không mong muốn sẽ tạm thời bị ẩn đi.
Rút trích dữ liệu ngày tháng trong Excel
Bước 1: Dùng hàm Date để lấy các dữ liệu ngày, tháng, năm từ dữ liệu gốc
Hàm Date: =Date(Year(D3), Month(D3), Day(D3))
Bước 2: Định dạng dữ liệu ở cột kết quả bước 1 về dạng ngày,tháng năm: dd/mm/yy.
Bước 3: Áp dụng Data Filter vào cột kết quả bước 1, tìm kiếm những giá trị #VALUE! và sửa lại ngày ở dữ liệu gốc về đúng định dạng.
– Xử lỹ dữ liệu gốc ở những dòng mà hàm DATE báo lỗi về đúng định dạng ngày bằng cách sửa trực tiếp từng ô ở cột D.
– Sau khi sửa ta được kết quả như sau (áp dụng cho cả 2 cột K và L).
Bước 4: Bỏ chế độ lọc, copy toàn bộ kết quả ở cột K và cột L, paste giá trị (Paste special – Value) sang cột dữ liệu gốc. Giá trị khi bạn copy ra sẽ định dạng dưới dạng dd/mm/yy.
Bước 5: Vì dữ liệu có 2 cột Ngày vào và Ngày ra, nên cần kiểm tra thêm 1 quy ước nữa là Ngày ra phải lớn hơn ngày vào. Ta lấy Ngày ra – Ngày vào để kiểm tra.
Với những giá trị có kết quả <0, sửa tay trực tiếp vào ô Ngày ra (do dữ liệu ngày ở đây khi trích xuất ra excel bị hiểu là dạng mm/dd/yyyy chứ không phải dd/mm/yyyy.
Cách lọc dữ liệu trùng trong Excel
1. Lọc dữ liệu trùng trong excel 2019, 2016, 2013
Trên những phiên bản Excel 2016, 2013 rất nhiều tính năng và thao tác làm việc đã được tối ưu hóa nhằm thực hiện các nhiệm vụ một cách nhanh nhất có thể.
Để thực hiện lọc dữ liệu trùng trong excel bạn cần thực hiện các bước sau:
Bước 1: Bôi đen toàn bộ dữ liệu cần phải lọc. Sau đó chọn Data à Remove Duplicates.
Bước 2: Sau đó hộp thoại Remove Duplicates xuất hiện. Bạn có 2 lựa chọn:
– Select All: lọc tất cả các dữ liệu.
– Unselect All: Lựa chọn các trường muốn lọc để lọc dữ liệu.
Sau khi chọn lựa bạn nhấn Ok để hoàn tất.
Bước 3: Tiếp theo, bạn sẽ thấy hộp thoại Microsoft Excek xuất hiện. Trên đó hiển thị số lượng mẫu trùng, số mẫu bị xóa và tổng số giá trị sửa lại để bạn nắm bắt dễ dàng. Nhấn Ok là bạn đã hoàn tất việc lọc dữ liệu.
2. Các bước để lọc dữ liệu trùng trong excel 2010, 2007
Có 2 cách để chúng ta có thể lọc dữ liệu trùng trong excel:
Cách 1: Lọc dữ liệu excel trùng bằng Conditional Formatting
Bước 1: Bôi đen vùng dữ liệu bị trùng cần phải lọc.
Cách 2: Lọc và xóa dữ liệu trùng bằng Remove Duplicate. Cách này bạn có thể thực hiện đơn giản hơn so với cách 1
Bước 1: Bạn thực hiện bôi đen vùng dữ liệu trùng cần lọc hoặc xóa.
Bước 2: Bạn vào thẻ Data à Remove Duplicate. Bạn chọn lọc toàn bộ hoặc một vùng dữ liệu rồi nhấn OK. Vậy là bạn đã có vùng dữ liệu cần lọc được hiển thị.
Lọc dữ liệu excel trùng trong Excel 2003
Bước 1: Bôi đen toàn bộ dữ liệu bạn cần lọc.
Bước 2: Tiếp theo trên thanh công cụ bạn chọn Conditional Formatting à New Rule.
Bước 3: Cửa sổ Edit New Rule xuất hiện. Tại đây, bạn chọn Use formula to determine which cells to format. Sau đó nhập công thức lọc tại ô Format values where this format is true. Bạn phải nhớ công thức chính xác mới thực hiện được. Cuối cùng chọn Ok để máy lọc dữ liệu trùng.
Cách tìm dữ liệu trong Excel
1. Tìm kiếm dữ liệu đơn giản trong excel bằng Find and Replace
Bước 1: Bôi đen cột dữ liệu cần tìm kiếm.
Bước 2: Nhấn tổ hợp phím Ctrl + F để mở hộp thoại Find and Replace, và bạn thực hiện điền dữ liệu vào ô Find What và chọn Find All.
2. Tìm dữ liệu trong cột nhờ công cụ Auto Filter
Bước 1: Bôi đen cột dữ liệu cần tìm kiếm. Vào thẻ Data chọn Filter để tạo bộ lọc nội dung.
Bước 2: Tại cột mà bạn chọn đã xuất hiện bộ lọc. Bạn nhấn vào biểu tượng mũi tên rồi nhập dữ liệu cần tìm kiếm và nhấn Ok bên dưới.
Kết quả excel sẽ cho bạn thấy những dữ liệu bạn đã nhập. Còn các dữ liệu khác sẽ tạm thời bị ẩn đi. Nếu bạn muốn hiển thị lại như ban đầu, bạn chỉ cần kích chuột vào biểu tượng mũi tên và nhấn chọn Select All.
3. Cách tìm dữ liệu trong nhiều Sheet Excel
Bước 1: Bạn nhấn tổ hợp phím Ctrl + F để mở công cụ Find & Replace. Bạn nhập dữ liệu cần tìm kiếm vào ô Find What..
Bước 2: Bạn chọn Options. Trong phần Within bạn chuyển về WordBook và chọn Find All để tìm kiếm
Bước 3: Kết quả bạn nhìn thấy toàn bộ vị trí ở các sheet có tên mà bạn muốn tìm kiếm. Nếu nhấn vào vị trí nào trong bảng tìm kiếm thì người dùng được di chuyển đến đúng vị trí đó trong bảng dữ liệu Excel.
Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel
Để dữ liệu đó có thể được giới hạn trong một phạm vi nào đó, có thể là số nguyên, số thập phân, ngày, giờ, trong danh sách sẵn có hoặc chuỗi có độ dài xác định. Khi đó chức năng Data Validation sẽ giúp nhập liệu một cách chính xác theo yêu cầu, hạn chế sai sót. Các bước thực hiện:
Chọn vùng dữ liệu dữ liệu cần kiểm soát.
Tab : cho phép thiết lập về điều kiện nhập liệu trong Validation criteria. Tuỳ vào yêu cầu kiểm soát mà chọn trong danh sách bên dưới Allow. Mặc định ban đầu cho phép nhập bất cứ kiểu dữ liệu nào vào trong ô (Any value). Để thay đổi theo ý muốn, phải chọn trong danh sách thả xuống của Validation criteria, có các lựa chọn: Whole number, Decimal, List, Date, Time, Text lenght, Custom.
Whole number: chức năng này chỉ cho phép nhập dữ liệu là số nguyên. Nếu nhập số thập phân, chuỗi,… sẽ bị báo lỗi. Khi chọn Whole number, chức năng Data xuất hiện cho phép thiết lập phạm vi giá trị nhập.
Between (not between): chỉ cho phép nhập giá trị trong một phạm vi xác định (hoặc ngoài phạm vi xác định). Ví dụ điểm thi phải nằm trong phạm vi từ 0 đến 10, tháng trong năm từ 1 đến 12… trong đó:
Minimum: giá trị giới hạn nhỏ nhất khi nhập dữ liệu.
Maximum: giá trị giới hạn lớn nhất khi nhập dữ liệu.
Ignore blank: bỏ qua ô trống (không xét điều kiện nhập dữ liệu khi ô trống).
Clear All: huỷ bỏ tất cả.
Equal to (not equal to): chỉ cho phép nhập một giá trị xác định (hoặc ngoài giá trị xác định). Giá trị xác định đó được khai báo trong Value và có thể là giá trị cố định bằng cách nhập trực tiếp hoặc có thể thay đổi được bằng cách sử dụng hàm hoặc công thức.
Greater than (Greater than or equal to): chỉ cho phép nhập giá trị lớn hơn (hoặc lớn hơn hay bằng) giá trị giới hạn chỉ định. Giá trị giới hạn đó được khai báo trong Minimum và có thể là giá trị cố định bằng cách nhập trực tiếp hoặc có thể thay đổi được bằng cách sử dụng hàm hoặc công thức. Ngược lại với trường hợp này là less than (less than or equal to).
Decimal: chỉ cho phép nhập liệu là số nguyên hoặc số thập phân. Cách thực hiện tương tự đối với Whole number.
List: chỉ cho phép nhập dữ liệu từ một danh sách sẵn có. Danh sách này có thể được nhập trực tiếp, từ một vùng trong sheet, từ tên (Name) vùng sẵn có hoặc từ file khác.
Danh sách nhập trực tiếp: gõ danh sách trực tiếp trong Source, mỗi phần tử được ngăn cách bởi dấu phẩy.
Danh sách nhập từ một vùng trên bảng tính: chọn vùng danh sách trên bảng tính, địa chỉ danh sách sẽ hiện ra trong Source. Với cách này, vùng danh sách phải cùng nằm cùng sheet với những ô cần nhập. Nếu muốn sử dụng vùng danh sách ở sheet khác thì phải đặt tên cho vùng đó, khi đó Source sẽ hiển thị tên vùng.
Hạn chế của chức năng List là không tự động tìm đến phần tử cần tìm trong danh sách khi gõ ký tự đầu tiên. Vì vậy, với danh sách nhiều đối tượng việc tìm sẽ mất nhiều thời gian.
Date: chức năng này chỉ cho phép nhập giá trị ngày, việc điều khiển miền giá trị nhập giống như đối với Whole number.
Text length: Cho phép nhập dữ liệu là chuỗi có độ dài xác định, việc thiết lập chiều dài chuỗi nhập giống như đối với Whole number.
Tab Input Message: cho phép hiển thị thông tin nhập dữ liệu khi di chuyển chuột vào ô, từ đó định hướng cho công việc nhập dữ liệu.
Show input message when cell is selected: bật /tắt chế độ hiển thị thông báo khi ô được chọn.
Title: tiêu đề của thông báo.
Input message: nội dung thông báo.
Tab Error Alert: khi ô đã được đặt chế độ Data validation, nếu người dùng nhập liệu không đúng quy định thì Excel sẽ bị thông báo.
Show error alert after invalid data is selected: bật (tắt) chế độ hiển thị cảnh báo sau khi dữ liệu được nhập vào ô.
Style: kiểu cảnh báo, gồm Stop (dừng lại), Warrning (cảnh báo), Information (thông tin).
Title: tiêu đề hộp thông báo.
Error message: nội dung thông báo.
Các thao tác với Worksheet
Chèn thêm worksheet vào workbook, có thể thực hiện các cách sau:
Xóa worksheet khỏi workbook, có thể thực hiện các cách sau:
Nhấp phải chuột lên tên sheet muốn xóa trên thanh sheet tab, chọn Delete, xác nhận xóa chọn Delete.
Sắp xếp worksheet trên workbook, có thể thực hiện các cách sau:
Chọn tên sheet cần sắp xếp, drag đến vị trí mới.
Bạn đang đọc nội dung bài viết Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu trên website Comforttinhdauthom.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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!