Excel Power Query có tốt không?

Power Query là một công cụ kinh doanh thông minh có sẵn trong Excel cho phép bạn nhập dữ liệu từ nhiều nguồn khác nhau, sau đó làm sạch, chuyển đổi và định hình lại dữ liệu của bạn khi cần

Nó cho phép bạn thiết lập một truy vấn một lần và sau đó sử dụng lại nó bằng cách làm mới đơn giản. Nó cũng khá mạnh. Power Query có thể nhập và xóa hàng triệu hàng vào mô hình dữ liệu để phân tích sau. Giao diện người dùng trực quan và được bố trí hợp lý nên rất dễ sử dụng. Đó là một đường cong học tập cực kỳ ngắn khi so sánh với các công cụ Excel khác như công thức hoặc VBA

Phần tốt nhất về nó là bạn không cần phải học hoặc sử dụng bất kỳ mã nào để thực hiện bất kỳ thao tác nào trong số đó. Power query editor ghi lại tất cả các phép biến đổi của bạn từng bước và chuyển đổi chúng thành mã M cho bạn, tương tự như cách trình ghi Macro bằng VBA

Nếu bạn muốn chỉnh sửa hoặc viết mã M của riêng mình, bạn chắc chắn có thể, nhưng bạn chắc chắn không cần phải

Lấy dữ liệu được sử dụng trong bài đăng này để theo dõi

Power Query có thể làm gì?

Hãy tưởng tượng bạn nhận được một báo cáo bán hàng trong một tệp văn bản từ hệ thống của mình hàng tháng trông như thế này

Hàng tháng, bạn cần vào thư mục chứa tệp được tải lên và mở tệp và sao chép nội dung vào Excel

Sau đó, bạn sử dụng tính năng chuyển văn bản thành cột để tách dữ liệu thành các cột mới

Hệ thống chỉ xuất ra ID của nhân viên bán hàng, vì vậy bạn cần thêm một cột mới vào dữ liệu và sử dụng VLOOKUP để liên kết nhân viên bán hàng với từng ID. Sau đó, bạn cần tổng hợp doanh số bán hàng của nhân viên bán hàng và tính toán hoa hồng để thanh toán.

Bạn cũng cần liên kết ID sản phẩm với danh mục sản phẩm nhưng chỉ 4 chữ số đầu tiên của mã sản phẩm liên quan đến danh mục sản phẩm. Bạn tạo một cột khác dùng hàm LEFT để lấy 4 chữ số đầu tiên của mã sản phẩm, sau đó sử dụng hàm VLOOKUP trên cột này để lấy danh mục sản phẩm. Bây giờ bạn có thể tóm tắt dữ liệu theo danh mục

Có thể chỉ mất một giờ mỗi tháng để làm, nhưng đó là công việc khá tốn công sức, không thú vị và làm mất thời gian mà bạn thực sự có thể dành để phân tích dữ liệu và tạo ra thông tin chi tiết có ý nghĩa

Với Power Query, tất cả điều này có thể được tự động hóa chỉ bằng một lần bấm vào nút làm mới hàng tháng. Tất cả những gì bạn cần làm là tạo truy vấn một lần và sử dụng lại nó, tiết kiệm một giờ làm việc mỗi tháng

Power Query ở đâu?

Power Query có sẵn dưới dạng phần bổ trợ để tải xuống và cài đặt cho Excel 2010 và 2013, đồng thời sẽ xuất hiện dưới dạng tab mới trong dải băng có nhãn Power Query. Vào năm 2016, nó đã được đổi tên thành Nhận & Chuyển đổi và xuất hiện trong tab Dữ liệu mà không cần cài đặt bất kỳ bổ trợ nào

Nhập dữ liệu của bạn bằng Power Query

Nhập dữ liệu của bạn bằng Power Query rất đơn giản. Excel cung cấp nhiều kết nối dữ liệu phổ biến có thể truy cập từ tab Dữ liệu và có thể tìm thấy từ lệnh Lấy dữ liệu

  • Nhận dữ liệu từ một tệp, chẳng hạn như sổ làm việc Excel, tệp Văn bản hoặc CSV, tệp XML và JSON. Bạn cũng có thể nhập nhiều tệp từ trong một thư mục nhất định
  • Nhận dữ liệu từ nhiều cơ sở dữ liệu khác nhau như SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata và cơ sở dữ liệu SAP HANA
  • Nhận dữ liệu từ Microsoft Azure
  • Nhận dữ liệu từ các dịch vụ trực tuyến như Sharepoint, Microsoft Exchange, Dynamics 365, Facebook và Salesforce
  • Nhận dữ liệu từ các nguồn khác như bảng hoặc phạm vi bên trong sổ làm việc hiện tại, từ web, Microsoft Query, Hadoop, nguồn cấp dữ liệu OData, ODBC và OLEDB
  • Chúng ta có thể hợp nhất hai truy vấn với nhau tương tự như nối hai truy vấn trong SQL
  • Chúng ta có thể thêm một truy vấn vào một truy vấn khác tương tự như việc kết hợp hai truy vấn trong SQL

Ghi chú. Các tùy chọn kết nối dữ liệu có sẵn sẽ phụ thuộc vào phiên bản Excel của bạn

Có một số loại truy vấn phổ biến hơn có sẵn ở cấp cao nhất của các lệnh ruy-băng được tìm thấy trong phần Nhận & Biến đổi của tab Dữ liệu. Từ đây, chúng ta có thể dễ dàng truy cập các truy vấn Từ Văn bản/CSV, Từ Web và Từ Bảng/Phạm vi. Chúng chỉ được sao chép bên ngoài lệnh Lấy dữ liệu để thuận tiện cho việc sử dụng, vì bạn có thể sẽ sử dụng chúng thường xuyên hơn

Tùy thuộc vào loại kết nối dữ liệu bạn chọn, Excel sẽ hướng dẫn bạn thiết lập kết nối và có thể có một số tùy chọn để chọn trong quá trình này

Khi kết thúc quá trình thiết lập, bạn sẽ đến cửa sổ xem trước dữ liệu. Bạn có thể xem bản xem trước của dữ liệu tại đây để đảm bảo đó là những gì bạn đang mong đợi. Sau đó, bạn có thể tải dữ liệu nguyên trạng bằng cách nhấn nút Tải hoặc bạn có thể tiếp tục với trình chỉnh sửa truy vấn để áp dụng bất kỳ bước chuyển đổi dữ liệu nào bằng cách nhấn nút Chỉnh sửa

Một ví dụ đơn giản về nhập dữ liệu trong tệp Excel

Chúng ta hãy xem cách nhập một số dữ liệu từ sổ làm việc Excel đang hoạt động. Chúng tôi sẽ nhập một tệp Excel có tên là Dữ liệu bán hàng cung cấp văn phòng. xlsx. Nó chứa dữ liệu bán hàng trên một trang tính được gọi là Dữ liệu bán hàng và dữ liệu khách hàng trên một trang tính khác gọi là Dữ liệu khách hàng. Cả hai trang tính dữ liệu bắt đầu trong ô A1 và hàng đầu tiên của dữ liệu chứa các tiêu đề cột

Chuyển đến tab Data và chọn lệnh Get Data trong phần Get & Transform Data. Sau đó vào From File chọn From Workbook

Thao tác này sẽ mở menu bộ chọn tệp nơi bạn có thể điều hướng đến tệp bạn muốn nhập. Chọn tệp và nhấn nút Nhập

Sau khi chọn tệp bạn muốn nhập, cửa sổ Điều hướng xem trước dữ liệu sẽ mở ra. Thao tác này sẽ cung cấp cho bạn danh sách tất cả các đối tượng có sẵn để nhập từ sổ làm việc. Chọn hộp để Chọn nhiều mục vì chúng tôi sẽ nhập dữ liệu từ hai trang tính khác nhau. Bây giờ chúng tôi có thể kiểm tra cả Dữ liệu khách hàng và Dữ liệu bán hàng

Khi bạn bấm vào một trong các đối tượng trong sổ làm việc, bạn có thể xem bản xem trước dữ liệu cho nó ở phía bên phải của cửa sổ điều hướng. Điều này rất tốt cho việc kiểm tra cảm giác để đảm bảo bạn đã lấy đúng tệp

Khi bạn hài lòng rằng bạn đã có mọi thứ mình cần từ sổ làm việc, bạn có thể nhấn nút Chỉnh sửa hoặc Tải. Nút chỉnh sửa sẽ đưa bạn đến trình chỉnh sửa truy vấn nơi bạn có thể chuyển đổi dữ liệu của mình trước khi tải dữ liệu đó. Nhấn nút tải sẽ tải dữ liệu vào bảng trong trang tính mới trong sổ làm việc

Trong ví dụ đơn giản này, chúng tôi sẽ bỏ qua trình chỉnh sửa và chuyển thẳng sang tải dữ liệu vào Excel. Nhấn mũi tên nhỏ bên cạnh nút Tải để truy cập tùy chọn Tải vào. Điều này sẽ cung cấp cho bạn một số tùy chọn tải khác

Chúng tôi sẽ chọn tải dữ liệu vào một bảng trong một trang tính mới, nhưng có một số tùy chọn khác. Bạn cũng có thể tải dữ liệu trực tiếp vào bảng tổng hợp hoặc biểu đồ tổng hợp hoặc bạn có thể tránh tải dữ liệu và chỉ cần tạo kết nối với dữ liệu

Giờ đây, các bảng được tải vào các trang tính mới trong Excel và chúng tôi cũng có hai truy vấn có thể nhanh chóng được làm mới nếu dữ liệu trong sổ làm việc gốc được cập nhật

Trình soạn thảo truy vấn

Sau khi xem qua hướng dẫn để kết nối dữ liệu của bạn và chọn tùy chọn Chỉnh sửa, bạn sẽ thấy trình chỉnh sửa truy vấn. Đây là nơi mọi bước chuyển đổi dữ liệu sẽ được tạo hoặc chỉnh sửa. Có 6 khu vực chính trong trình chỉnh sửa để làm quen với

  1. Dải băng – Giao diện người dùng cho trình chỉnh sửa khá giống với Excel và sử dụng trung tâm lệnh kiểu dải băng trực quan. Nó tổ chức các lệnh chuyển đổi dữ liệu và các tùy chọn truy vấn nguồn khác thành 5 tab chính
  2. Danh sách truy vấn – Khu vực này liệt kê tất cả các truy vấn trong sổ làm việc hiện tại. Bạn có thể điều hướng đến bất kỳ truy vấn nào từ khu vực này để bắt đầu chỉnh sửa nó
  3. Xem trước dữ liệu – Khu vực này là nơi bạn sẽ thấy bản xem trước của dữ liệu với tất cả các bước chuyển đổi hiện được áp dụng. Bạn cũng có thể truy cập nhiều lệnh chuyển đổi ở đây từ biểu tượng bộ lọc trong tiêu đề cột hoặc nhấp chuột phải vào tiêu đề cột
  4. Thanh công thức – Đây là nơi bạn có thể xem và chỉnh sửa mã M của bước chuyển đổi hiện tại. Mỗi chuyển đổi bạn thực hiện trên dữ liệu của mình được ghi lại và xuất hiện dưới dạng một bước trong khu vực các bước đã áp dụng
  5. Thuộc tính – Đây là nơi bạn có thể đặt tên cho truy vấn của mình. Khi bạn đóng và tải truy vấn vào bảng Excel, truy vấn nguồn sẽ tạo một bảng có cùng tên với truy vấn nguồn nếu tên bảng chưa được sử dụng. Tên truy vấn cũng là cách mã M sẽ tham chiếu truy vấn này nếu chúng ta cần truy vấn nó trong một truy vấn khác
  6. Các bước đã áp dụng – Khu vực này là danh sách theo thứ tự thời gian của tất cả các bước chuyển đổi đã được áp dụng cho dữ liệu. Bạn có thể di chuyển qua các bước tại đây và xem các thay đổi trong khu vực xem trước dữ liệu. Bạn cũng có thể xóa, sửa đổi hoặc sắp xếp lại bất kỳ bước nào trong truy vấn tại đây

Danh sách truy vấn

Danh sách truy vấn có các khả năng khác ngoài việc chỉ liệt kê tất cả các truy vấn của sổ làm việc hiện tại

Một trong những chức năng chính của danh sách truy vấn là điều hướng. Không cần phải thoát khỏi trình chỉnh sửa truy vấn để chuyển đổi truy vấn mà bạn đang làm việc. Bạn có thể nhấp chuột trái vào bất kỳ truy vấn nào để chuyển đổi. Truy vấn bạn đang thực hiện sẽ được đánh dấu bằng màu lục nhạt

Cuối cùng, khi bạn thoát khỏi trình chỉnh sửa bằng nút đóng và tải, các thay đổi trong tất cả các truy vấn bạn đã chỉnh sửa sẽ được lưu lại

Bạn có thể ẩn danh sách truy vấn để tạo thêm chỗ cho việc xem trước dữ liệu. Nhấp chuột trái vào mũi tên nhỏ ở góc trên bên phải để chuyển danh sách giữa ẩn và hiển thị

Nếu bạn nhấp chuột phải vào bất kỳ truy vấn nào trong danh sách, sẽ có nhiều tùy chọn khả dụng

  • Sao chép và Dán – Sao chép và dán một truy vấn để tạo một bản sao khác của nó
  • Xóa – Xóa truy vấn. Nếu bạn vô tình xóa một truy vấn, sẽ không có nút hoàn tác, nhưng bạn có thể thoát khỏi trình chỉnh sửa truy vấn mà không lưu thông qua đóng và tải để khôi phục truy vấn của mình
  • Đổi tên – Đổi tên truy vấn của bạn. Điều này giống như đổi tên nó từ phần thuộc tính ở phía bên trái của trình chỉnh sửa
  • Bản sao – Tạo một bản sao khác của truy vấn. Điều này giống như sao chép và dán nhưng biến quy trình thành một bước
  • Di chuyển đến nhóm – Đặt các truy vấn của bạn vào một thư mục giống như cấu trúc để sắp xếp chúng khi danh sách trở nên lớn
  • Di chuyển lên và di chuyển xuống – Sắp xếp lại thứ tự các truy vấn của bạn xuất hiện trong danh sách hoặc trong các nhóm thư mục để thêm vào nỗ lực tổ chức của bạn. Điều này cũng có thể được thực hiện bằng cách kéo và thả truy vấn đến một vị trí mới
  • Tạo chức năng – Biến truy vấn của bạn thành một chức năng truy vấn. Chúng cho phép bạn truyền tham số cho truy vấn và trả về kết quả dựa trên tham số được truyền
  • Convert To Parameter – Cho phép bạn chuyển đổi tham số thành truy vấn hoặc truy vấn thành tham số
  • Trình chỉnh sửa nâng cao – Mở trình chỉnh sửa nâng cao để chỉnh sửa mã M cho truy vấn
  • Thuộc tính – Cho phép bạn thay đổi tên truy vấn, thêm văn bản mô tả và bật tùy chọn Tải dữ liệu nhanh cho truy vấn

Nếu bạn nhấp chuột phải vào bất kỳ vùng trống nào trong danh sách truy vấn, bạn có thể tạo một truy vấn mới

Xem trước dữ liệu

Công việc chính của khu vực xem trước dữ liệu là áp dụng các bước chuyển đổi cho dữ liệu của bạn và hiển thị bản xem trước của các bước bạn đang áp dụng này

Trong khu vực xem trước dữ liệu, bạn có thể chọn các cột bằng một vài phương pháp khác nhau. Một cột sẽ được tô sáng bằng màu lục nhạt khi được chọn

  • Chọn một cột bằng cách nhấp chuột trái vào tiêu đề cột
  • Chọn nhiều cột liền kề bằng cách nhấp chuột trái vào tiêu đề cột đầu tiên, sau đó giữ Shift và nhấp chuột trái vào tiêu đề cột cuối cùng
  • Chọn nhiều cột không liền kề bằng cách giữ Ctrl rồi nhấp chuột trái vào bất kỳ tiêu đề cột nào bạn muốn chọn

Sau đó, bạn có thể áp dụng bất kỳ bước chuyển đổi dữ liệu có liên quan nào trên các cột đã chọn từ dải băng hoặc có thể truy cập một số bước nhất định bằng cách nhấp chuột phải vào tiêu đề cột. Các lệnh không có sẵn cho cột hoặc các cột đã chọn của bạn sẽ có màu xám trong dải băng

Mỗi cột có một biểu tượng kiểu dữ liệu ở bên trái tiêu đề cột. Bạn có thể nhấp chuột trái vào nó để thay đổi kiểu dữ liệu của cột

Bạn có thể chọn từ các số thập phân, tiền tệ, số nguyên, tỷ lệ phần trăm, ngày và giờ, ngày, giờ, múi giờ, thời lượng, văn bản, Boolean và nhị phân

Sử dụng tùy chọn Locale cho phép bạn đặt định dạng kiểu dữ liệu bằng cách sử dụng quy ước từ các vị trí khác nhau. Ví dụ: nếu bạn muốn hiển thị ngày ở định dạng m/d/yyyy của Mỹ thay vì dd/mm/yyyy thông thường thì bạn có thể chọn Hoa Kỳ làm ngôn ngữ

Có một biểu tượng bảng nhỏ ở góc trên cùng bên trái của phần xem trước dữ liệu, bạn có thể nhấp chuột phải hoặc nhấp chuột trái vào biểu tượng này để truy cập các hành động khác nhau ảnh hưởng đến toàn bộ bảng

Đổi tên bất kỳ tiêu đề cột nào thực sự dễ dàng. Nhấp đúp chuột trái vào bất kỳ tiêu đề cột nào, sau đó nhập tên mới của bạn và nhấn Enter khi bạn hoàn tất

Bạn có thể thay đổi thứ tự của bất kỳ cột nào bằng hành động nhấp chuột trái và kéo. Đường viền màu lục giữa hai cột sẽ trở thành vị trí mới của cột được kéo khi bạn thả chuột trái

Mỗi cột cũng có một bộ lọc chuyển đổi ở phía bên tay phải. Nhấp chuột trái vào đây để sắp xếp và lọc dữ liệu của bạn. Menu bộ lọc này rất giống với các bộ lọc được tìm thấy trong bảng tính thông thường và sẽ hoạt động theo cùng một cách

Danh sách các mục được hiển thị dựa trên một mẫu dữ liệu nên có thể không chứa tất cả các mục có sẵn trong dữ liệu. Bạn có thể load thêm bằng cách click vào chữ Load more màu xanh

Nhiều phép biến đổi được tìm thấy trong menu ribbon cũng có thể truy cập được từ khu vực xem trước dữ liệu bằng cách nhấp chuột phải vào tiêu đề cột. Một số hành động bạn chọn từ menu nhấp chuột phải này sẽ thay thế cột hiện tại. Nếu bạn muốn tạo cột mới dựa trên, hãy sử dụng lệnh từ tab Thêm cột để thay thế

Các bước áp dụng

Mọi chuyển đổi bạn thực hiện đối với dữ liệu của mình sẽ xuất hiện dưới dạng một bước trong khu vực Các bước đã áp dụng. Nó cũng cho phép bạn điều hướng qua truy vấn của mình. Nhấp chuột trái vào bất kỳ bước nào và bản xem trước dữ liệu sẽ cập nhật để hiển thị tất cả các chuyển đổi cho đến và bao gồm cả bước đó

Bạn có thể chèn các bước mới vào truy vấn tại bất kỳ thời điểm nào bằng cách chọn bước trước đó rồi tạo chuyển đổi trong phần xem trước dữ liệu. Power Query sau đó sẽ hỏi bạn có muốn chèn bước mới này không. Mặc dù vậy, hãy cẩn thận vì điều này có thể phá vỡ các bước sau liên quan đến điều gì đó bạn đã thay đổi

Bạn có thể xóa bất kỳ bước nào đã được áp dụng bằng cách sử dụng dấu X ở phía bên trái của tên bước trong khu vực Các bước đã áp dụng. Tuy nhiên, cần thận trọng vì nếu bất kỳ bước nào sau đây phụ thuộc vào bước bạn đang cố xóa, thì bạn sẽ phá vỡ truy vấn của mình. Đây là nơi có thể hữu ích Xóa cho đến khi kết thúc từ menu nhấp chuột phải

Rất nhiều bước chuyển đổi có sẵn trong truy vấn nguồn sẽ có nhiều tham số đầu vào của người dùng và cài đặt khác được liên kết với chúng. Nếu bạn áp dụng bộ lọc trên cột sản phẩm để hiển thị tất cả các mặt hàng không bắt đầu bằng Bút, thì sau này bạn có thể quyết định rằng mình cần thay đổi bước bộ lọc này để hiển thị tất cả các mặt hàng không bắt đầu bằng Bút. Bạn có thể thực hiện các chỉnh sửa này từ khu vực Bước đã áp dụng

Một số bước sẽ có một biểu tượng bánh răng nhỏ ở phía bên tay phải. Điều này cho phép bạn chỉnh sửa đầu vào và cài đặt của bước đó

Bạn có thể sắp xếp lại thứ tự các bước được thực hiện trong truy vấn của mình. Chỉ cần nhấp chuột trái vào bất kỳ bước nào và kéo nó đến một vị trí mới. Một đường màu xanh lá cây giữa các bước sẽ cho biết vị trí mới. Đây là một bước khác mà bạn cần cẩn thận vì nhiều bước sẽ phụ thuộc vào các bước trước đó và việc thay đổi thứ tự có thể tạo ra lỗi vì điều này

Nhấp chuột phải vào bất kỳ bước nào để truy cập menu tùy chọn

  • Chỉnh sửa cài đặt – Điều này cho phép bạn chỉnh sửa cài đặt của bước tương tự như sử dụng biểu tượng bánh răng ở phía bên phải của bước
  • Đổi tên – Điều này cho phép bạn đổi tên nhãn các bước. Thay vì hiển thị tên chung chung như “Hàng đã lọc“, bạn có thể hiển thị tên này giống như “Hàng sản phẩm đã lọc trên bút” để bạn có thể dễ dàng xác định bước đang thực hiện
  • Xóa – Thao tác này sẽ xóa bước hiện tại tương tự như dấu X ở phía bên trái của bước
  • Xóa cho đến khi kết thúc – Điều này cho phép bạn xóa bước hiện tại cộng với tất cả các bước cho đến khi kết thúc. Vì các bước có thể phụ thuộc vào các bước trước đó, xóa tất cả các bước sau một bước là một cách hay để tránh mọi lỗi
  • Chèn bước sau – Điều này cho phép bạn chèn một bước mới sau bước hiện tại
  • Di chuyển lên và di chuyển xuống – Điều này cho phép bạn sắp xếp lại các bước truy vấn tương tự như phương pháp kéo và thả
  • Trích xuất trước đó - Đây có thể là một tùy chọn thực sự hữu ích. Nó cho phép bạn tạo một bản sao mới của truy vấn cho đến bước đã chọn

Thanh công thức

Khi bạn nhấp vào các bước khác nhau của quy trình chuyển đổi trong khu vực Các bước đã áp dụng, thanh công thức sẽ cập nhật để hiển thị mã M được tạo cho bước đó. Nếu mã M được tạo dài hơn thanh công thức, bạn có thể mở rộng thanh công thức bằng cách sử dụng mũi tên chuyển đổi ở phía bên tay phải

Bạn có thể chỉnh sửa mã M cho một bước trực tiếp từ thanh công thức mà không cần mở trình chỉnh sửa nâng cao. Trong ví dụ này, chúng tôi đã thay đổi bộ lọc của mình từ “Bút” thành “Ghế” bằng cách nhập vào thanh công thức rồi nhấn Enter hoặc sử dụng dấu kiểm ở bên trái để xác nhận thay đổi. Nhấn Esc hoặc sử dụng X ở
bên trái để hủy mọi thay đổi.

Thẻ Tệp

Tab Tệp chứa các tùy chọn khác nhau để lưu bất kỳ thay đổi nào được thực hiện đối với các truy vấn của bạn cũng như các tùy chọn và cài đặt truy vấn cấp nguồn

  • Đóng & Tải – Điều này sẽ lưu truy vấn của bạn và tải truy vấn hiện tại của bạn vào bảng Excel trong sổ làm việc
  • Đóng & Tải vào – Thao tác này sẽ mở menu Nhập Dữ liệu với nhiều tùy chọn tải dữ liệu khác nhau để lựa chọn
  • Hủy & Đóng – Thao tác này sẽ hủy mọi thay đổi bạn đã thực hiện đối với các truy vấn trong phiên của mình trong trình chỉnh sửa và đóng trình chỉnh sửa

Lưu ý, bạn vẫn cần lưu sổ làm việc theo cách thông thường để giữ mọi thay đổi đối với truy vấn nếu bạn đóng sổ làm việc

Các lệnh Close & Load và Close & Load To cũng có sẵn từ tab Home

Tùy chọn tải dữ liệu

Khi bạn sử dụng tùy chọn Đóng và Tải vào để thoát khỏi trình chỉnh sửa, thao tác này sẽ mở menu Nhập dữ liệu

Bạn có thể chọn tải truy vấn vào bảng, bảng tổng hợp, biểu đồ trục hoặc chỉ tạo kết nối cho truy vấn. Tùy chọn chỉ kết nối có nghĩa là không có dữ liệu đầu ra cho sổ làm việc, nhưng bạn vẫn có thể sử dụng truy vấn này trong các truy vấn khác. Đây là một tùy chọn tốt nếu truy vấn là bước trung gian trong quy trình chuyển đổi dữ liệu

Bạn cũng có thể chọn vị trí để tải vào sổ làm việc của mình nếu bạn đã chọn bảng, bảng tổng hợp hoặc biểu đồ tổng hợp trong phần trước. Bạn có thể chọn một ô trong một trang tính hiện có hoặc tải nó vào một trang tính mới mà Excel sẽ tự động tạo cho bạn

Tùy chọn khác mà bạn nhận được là Thêm dữ liệu này vào Mô hình Dữ liệu. Điều này sẽ cho phép bạn sử dụng đầu ra dữ liệu trong Power Pivot và sử dụng chức năng Mô hình Dữ liệu khác như xây dựng mối quan hệ giữa các bảng. Mô hình dữ liệu Cách lưu trữ và sử dụng lượng lớn dữ liệu hiệu quả mới của Excel

Cửa sổ Truy vấn & Kết nối

Khi bạn đang làm việc bên ngoài trình soạn thảo truy vấn nguồn, bạn có thể xem và tương tác với tất cả các truy vấn trong sổ làm việc thông qua cửa sổ Truy vấn & Kết nối. Để mở cái này, hãy chuyển đến tab Dữ liệu trong dải băng Excel thông thường, sau đó nhấn nút lệnh Truy vấn & Kết nối được tìm thấy trong phần Truy vấn & Kết nối

Khi mở nó sẽ được gắn vào bên phải của sổ làm việc. Bạn có thể bỏ gắn nó bằng cách nhấp chuột trái vào tiêu đề và kéo nó. Bạn có thể kéo nó sang bên trái và gắn nó ở đó hoặc để nó nổi. Bạn cũng có thể thay đổi kích thước cửa sổ bằng cách nhấp chuột trái và kéo các cạnh

Điều này rất giống với danh sách truy vấn trong trình chỉnh sửa và bạn có thể thực hiện nhiều hành động tương tự bằng cách nhấp chuột phải vào bất kỳ truy vấn nào

Một tùy chọn đáng chú ý không có trong menu nhấp chuột phải vào danh sách truy vấn, đó là tùy chọn Tải vào. Điều này sẽ cho phép bạn thay đổi tùy chọn tải cho bất kỳ truy vấn nào, vì vậy, bạn có thể thay đổi bất kỳ truy vấn Chỉ kết nối nào để tải vào bảng Excel trong sổ làm việc

Một điều đáng chú ý khác là khi bạn di chuột qua truy vấn bằng con trỏ chuột, Excel sẽ tạo Bản xem trước dữ liệu Peek. Điều này sẽ cho bạn thấy một số thông tin cơ bản về truy vấn

  • Xem trước dữ liệu – Đây là bản xem trước trực tiếp của dữ liệu tương tự như khi thiết lập truy vấn lần đầu tiên
  • Cột – Điều này sẽ cung cấp cho bạn danh sách tất cả các cột có trong kết quả cuối cùng của truy vấn cùng với số lượng cột có. Nhấp vào bất kỳ trong số chúng sẽ đánh dấu cột trong bản xem trước dữ liệu
  • Làm mới lần cuối - Điều này sẽ cho bạn biết khi nào dữ liệu được làm mới lần cuối
  • Trạng thái tải – Phần này hiển thị xem dữ liệu được tải vào bảng, bảng tổng hợp, biểu đồ tổng hợp hay chỉ là một kết nối
  • Nguồn dữ liệu – Phần này sẽ hiển thị cho bạn nguồn dữ liệu cùng với số lượng tệp nếu bạn truy vấn từ thư mục
  • Xem trong Trang tính – Nhấp vào đây sẽ đưa bạn đến bảng đầu ra nếu truy vấn được tải vào bảng, bảng tổng hợp hoặc biểu đồ tổng hợp

Bạn cũng có thể truy cập chế độ xem Peek này bằng cách nhấp chuột phải vào truy vấn và chọn Show the peek

Ngoài ra còn có một số thông báo hữu ích được hiển thị trong cửa sổ Truy vấn & Kết nối cho mỗi truy vấn. Nó sẽ cho bạn biết nếu truy vấn chỉ là một kết nối, nếu có bất kỳ lỗi nào khi truy vấn chạy lần cuối hoặc có bao nhiêu hàng được tải

Thẻ Trang chủ

Tab Trang chủ chứa tất cả các hành động, chuyển đổi và cài đặt sẽ ảnh hưởng đến toàn bộ bảng

  1. Đóng – Bạn có thể truy cập các tùy chọn Đóng & Tải và Đóng & Tải vào từ đây. Chúng cũng có sẵn trong menu tab Tệp
  2. Truy vấn – Bạn có thể làm mới bản xem trước dữ liệu cho truy vấn hiện tại hoặc tất cả các kết nối truy vấn. Bạn cũng có thể mở cài đặt thuộc tính và trình chỉnh sửa nâng cao cho truy vấn hiện tại và có các tùy chọn bên dưới nút Quản lý để xóa, sao chép hoặc tham chiếu truy vấn hiện tại
  3. Quản lý cột – Bạn có thể điều hướng đến các cột cụ thể và chọn giữ hoặc xóa cột
  4. Giảm hàng – Bạn có thể quản lý các hàng dữ liệu từ phần này. Có rất nhiều tùy chọn để giữ một số hàng nhất định hoặc xóa một số hàng nhất định. Giữ hoặc xóa N hàng trên cùng, N hàng dưới cùng, một phạm vi hàng cụ thể, hàng xen kẽ, hàng trùng lặp hoặc hàng có lỗi. Một tùy chọn chỉ khả dụng để xóa các hàng là xóa các hàng trống
  5. Sắp xếp – Bạn có thể sắp xếp bất kỳ cột nào theo thứ tự tăng dần hoặc giảm dần
  6. Chuyển đổi – Phần này chứa hỗn hợp các tùy chọn chuyển đổi hữu ích
    • Tách cột – Điều này cho phép bạn chia dữ liệu trong một cột dựa trên dấu phân cách hoặc độ dài ký tự
    • Nhóm theo – Điều này cho phép bạn nhóm và tóm tắt dữ liệu của mình tương tự như Nhóm theo trong SQL
    • Data Type – Điều này cho phép bạn thay đổi kiểu dữ liệu của bất kỳ cột nào
    • Sử dụng Hàng đầu tiên làm Tiêu đề – Điều này cho phép bạn thăng cấp hàng đầu tiên của dữ liệu thành tiêu đề cột hoặc giảm hạng tiêu đề cột thành một hàng dữ liệu
    • Thay thế giá trị – Điều này cho phép bạn tìm và thay thế bất kỳ giá trị nào từ một cột
  7. Kết hợp – Phần này chứa tất cả các lệnh để kết hợp truy vấn của bạn với các truy vấn khác. Bạn có thể hợp nhất, nối thêm truy vấn hoặc kết hợp các tệp khi làm việc với truy vấn từ thư mục
  8. Tham số – Power Query cho phép bạn tạo tham số cho truy vấn của mình. Ví dụ: khi thiết lập truy vấn từ thư mục, bạn có thể muốn đường dẫn thư mục là một tham số để bạn có thể dễ dàng thay đổi vị trí. Bạn có thể tạo và quản lý các tham số hiện có từ phần này
  9. Nguồn dữ liệu – Phần này chứa cài đặt nguồn dữ liệu bao gồm quản lý quyền đối với bất kỳ nguồn dữ liệu nào yêu cầu mật khẩu để truy cập
  10. Truy vấn mới – Bạn có thể tạo truy vấn mới từ nguồn dữ liệu mới hoặc nguồn dữ liệu đã sử dụng trước đó từ phần này

Sự khác biệt giữa các tab chuyển đổi và thêm cột

Phần lớn tất cả các biến đổi có sẵn trong truy vấn nguồn có thể được truy cập thông qua tab Biến đổi hoặc tab Thêm cột

Bạn có thể nghĩ rằng có rất nhiều sự trùng lặp giữa hai tab này. Ví dụ: cả hai tab đều chứa phần Từ văn bản có nhiều lệnh giống nhau. Nó không thực sự như vậy, có một sự khác biệt tinh tế

Khi bạn sử dụng một lệnh từ tab Thêm cột được tìm thấy trong cả hai tab, lệnh này sẽ tạo một cột mới với dữ liệu được chuyển đổi và cột ban đầu sẽ giữ nguyên. Trong khi sử dụng lệnh tương đương từ tab Chuyển đổi sẽ thay đổi cột ban đầu và không có cột mới nào được tạo

Đây là điểm mấu chốt cần lưu ý

Thẻ chuyển đổi

Phần tab Chuyển đổi

  1. Bảng – Phần này chứa các lệnh sẽ biến đổi toàn bộ bảng. Bạn có thể nhóm và tổng hợp truy vấn của mình, tăng cấp hàng thành tiêu đề, hạ cấp tiêu đề thành hàng, chuyển đổi dữ liệu của bạn, đảo ngược thứ tự hàng và đếm hàng
  2. Any Column – Phần này chứa các lệnh sẽ hoạt động trên bất kỳ cột dữ liệu nào bất kể loại dữ liệu. Bạn có thể thay đổi loại dữ liệu, tự động phát hiện và thay đổi loại dữ liệu, đổi tên tiêu đề cột, tìm và thay thế giá trị, điền giá trị xuống (hoặc lên) một cột để thay thế bất kỳ khoảng trống hoặc giá trị rỗng nào bằng giá trị ở trên (hoặc ở dưới) cột đó
  3. Cột văn bản – Phần này chứa các lệnh cho dữ liệu văn bản. Bạn có thể tách các cột bằng dấu phân cách, định dạng trường hợp, cắt và làm sạch, hợp nhất hai hoặc nhiều cột lại với nhau, trích xuất văn bản và phân tích cú pháp các đối tượng XML hoặc JSON
  4. Cột số – Phần này chứa các lệnh cho dữ liệu số. Bạn có thể thực hiện nhiều phép tính tổng khác nhau như tổng và trung bình cộng, thực hiện các phép toán đại số tiêu chuẩn hoặc lượng giác và làm tròn số lên hoặc xuống
  5. Cột Ngày & Giờ – Phần này chứa các lệnh cho dữ liệu ngày và giờ. Bạn có thể trích xuất thông tin từ dữ liệu ngày, giờ và thời lượng của mình
  6. Cột có cấu trúc – Phần này chứa các lệnh để làm việc với các cấu trúc dữ liệu lồng nhau, chẳng hạn như khi cột của bạn chứa các bảng

Tab Thêm cột

Tab Thêm cột chứa nhiều lệnh tương tự như tab Biến đổi, nhưng điểm khác biệt chính là chúng sẽ tạo một cột mới với phép biến đổi

  1. Chung – Phần này cho phép bạn thêm các cột mới dựa trên công thức hoặc hàm tùy chỉnh. Bạn cũng có thể thêm các cột chỉ mục hoặc sao chép một cột từ đây
  2. From Text – Rất giống với phần From Text trong tab Transform, nhưng các lệnh này sẽ tạo một cột mới với sự chuyển đổi
  3. From Number – Rất giống với phần From Number trong tab Transform, nhưng các lệnh này sẽ tạo một cột mới với phép biến đổi
  4. From Date & Time – Rất giống với phần From Date & Time trong tab Transform, nhưng các lệnh này sẽ tạo một cột mới với sự chuyển đổi

Thẻ Xem

Tab Chế độ xem khá thưa thớt so với các tab khác. Không có lệnh chuyển đổi nào được tìm thấy trong đó. Hầu hết người dùng Power Query sẽ hiếm khi cần sử dụng khu vực này, nhưng vẫn có một vài điều đáng để biết về

  1. Bố cục – Phần này cho phép bạn hiển thị hoặc ẩn ngăn Cài đặt truy vấn (chứa các thuộc tính và các bước được áp dụng) và Thanh công thức
  2. Xem trước dữ liệu – Phần này cho phép bạn hiển thị hoặc ẩn các ký tự khoảng trắng hoặc biến phông chữ thành phông chữ đơn cách trong khu vực xem trước dữ liệu. Điều này rất hữu ích khi xử lý dữ liệu được phân tách bằng một số ký tự nhất định
  3. Cột – Điều này cho phép bạn đi tới và chọn một cột nhất định trong bản xem trước dữ liệu. Lệnh này cũng có sẵn trong tab Trang chủ
  4. Tham số – Điều này cho phép bạn bật tham số hóa trong các nguồn dữ liệu và các bước chuyển đổi
  5. Nâng cao – Thao tác này sẽ mở trình chỉnh sửa truy vấn nâng cao hiển thị mã M cho truy vấn. Điều này cũng có sẵn từ tab Trang chủ
  6. Phụ thuộc – Thao tác này sẽ mở dạng xem sơ đồ của các phụ thuộc truy vấn trong sổ làm việc

Đặc biệt, dạng xem Phụ thuộc truy vấn là một tài nguyên hữu ích cho phép bạn xem biểu diễn trực quan của luồng quy trình chuyển đổi dữ liệu

kết luận

Ban đầu, Power Query có vẻ quá sức đối với người mới làm quen với tất cả, nhưng giao diện người dùng được bố trí rất tốt và dễ nắm bắt. Mặc dù nó có thể là mới đối với người dùng, nhưng rất nhiều khái niệm đã quen thuộc với người dùng Excel rồi

Làm quen với tất cả các phần của trình soạn thảo và bố cục của các tab dải băng là bước đầu tiên cần thiết để khám phá Power Query và kết hợp nó vào công việc hàng ngày của bạn

Mặc dù có rất nhiều điều để tìm hiểu về Power Query nhưng bạn nên dành thời gian để tìm hiểu. Có tiềm năng to lớn để tiết kiệm thời gian trong các tác vụ định dạng và làm sạch dữ liệu lặp đi lặp lại với nó. Đây là một trong những công cụ mạnh mẽ và hữu ích nhất đã được thêm vào Excel kể từ bảng tổng hợp

Muốn tốt hơn truy vấn quyền lực hơn?

Excel Power Query có hữu ích không?

Power Query có một số tính năng hữu ích được nhúng trong đó, chẳng hạn như nối thêm dữ liệu và tạo mối quan hệ giữa các tập dữ liệu khác nhau. Điều này được gọi là hợp nhất các tập dữ liệu. Chúng tôi cũng có thể nhóm và tóm tắt dữ liệu với sự trợ giúp của công cụ. Không cần phải nói, nó là một công cụ rất hữu ích .

Power Query có tốt hơn VBA không?

Power Query có thể dễ dàng thay thế VBA (Visual Basic for Applications) vì nó cho phép bạn. Xử lý bảng của bạn đơn giản bằng cách nhấp vào nút. Ngoài ra, không cần kỹ năng mã hóa. Trực quan hóa các hoạt động của bạn từng bước mà không cần chạy một macro nào.

Power Query khác với Excel như thế nào?

Power BI có thể kết nối với một số lượng lớn nguồn dữ liệu, trong khi khả năng kết nối của Excel bị hạn chế . Ngoài ra, không giống như Excel, Power BI có thể dễ dàng sử dụng từ thiết bị di động. Power BI có tốc độ xử lý nhanh hơn Excel. Bảng điều khiển Power BI hấp dẫn, tương tác và tùy chỉnh trực quan hơn so với trong Excel.

Excel Power Query có phải là một công cụ ETL không?

Sử dụng Power Query, bạn có thể thực hiện xử lý trích xuất, chuyển đổi và tải (ETL) dữ liệu .