Bạn có thể truy vấn một truy vấn trong Excel không?

Power Query là gì?

Power Query là một ứng dụng để chuyển đổi và chuẩn bị dữ liệu. Với Power Query, bạn có thể lấy dữ liệu từ các nguồn bằng giao diện đồ họa và áp dụng các phép biến đổi bằng Power Query Editor. Sử dụng Power Query, một công cụ nghiệp vụ thông minh do Microsoft Excel cung cấp, bạn có thể nhập dữ liệu từ bất kỳ nguồn nào, làm sạch, chuyển đổi, sau đó định hình lại dữ liệu theo nhu cầu của bạn. Bằng cách này, bạn chỉ có thể thiết lập một truy vấn một lần, sử dụng lại sau này bằng cách làm mới đơn giản

Đúng như tên gọi, Power Query là công cụ tự động hóa dữ liệu mạnh mẽ nhất được tìm thấy trong Excel 2010 trở lên. Power Query cho phép người dùng nhập dữ liệu vào Excel thông qua các nguồn bên ngoài, chẳng hạn như tệp Văn bản, tệp CSV, Web hoặc sổ làm việc Excel, để liệt kê một số. Dữ liệu sau đó có thể được làm sạch và chuẩn bị cho các yêu cầu của chúng tôi

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

Làm cách nào để bạn kích hoạt Power Query?

Power Query có sẵn dưới dạng phần bổ trợ miễn phí trên Excel 2010 và 2013 mà bạn có thể tải xuống từ trang web của Microsoft. Liên kết có sẵn ở đây

Khi nhấp vào nút Tải xuống, một hộp thoại sẽ mở ra nơi bạn có thể chọn tùy chọn tải xuống phù hợp với hệ điều hành của mình. Power Query sau đó sẽ được tải xuống hệ thống của bạn

Nó là một công cụ tích hợp sẵn bắt đầu từ Excel 2016 và có sẵn trong Phần Nhận & Chuyển đổi Dữ liệu trong Tab Dữ liệu.  

Hãy tiếp tục và hiểu khái niệm về Power Query.  

Bạn có thể làm gì với Power Query?

Power Query là công cụ ETL [Trích xuất, Chuyển đổi, Tải] được sử dụng rộng rãi. Hãy xem ba bước cơ bản

1. Lấy dữ liệu

Nhập dữ liệu thật dễ dàng với sự trợ giúp của phần Lấy & Chuyển đổi Dữ liệu của tab Dữ liệu trong Excel.  

Bạn có thể nhập dữ liệu từ nhiều nguồn khác nhau

• Từ tập tin. Tệp Excel [Sổ làm việc], tệp Văn bản hoặc tệp CSV, tệp XML và tệp JSON
• Từ cơ sở dữ liệu. SQL Server, Microsoft Access, Dịch vụ phân tích máy chủ SQL
• Từ các nguồn khác. Bảng / Phạm vi Excel, Web, Microsoft Query, Nguồn cấp dữ liệu OData

2. Chuyển đổi dữ liệu

Sau khi nhập dữ liệu, chúng tôi có thể chuyển đổi dữ liệu đó với sự trợ giúp của Power Query. Power Query Editor giúp bạn chuyển đổi dữ liệu dựa trên nhu cầu của bạn

Hãy xem trình chỉnh sửa và hiểu các thành phần khác nhau của nó

Giao diện Power Query Editor

Học hỏi từ các chuyên gia nổi tiếng tại Caltech

Sáu phần chính của Power Query Editor như sau

• Dải băng Trình soạn thảo truy vấn. Ruy-băng này tương tự như trên giao diện Excel. Các lệnh khác nhau được tổ chức trong các tab riêng biệt.   
• Danh sách truy vấn. Phần này cho phép bạn duyệt qua danh sách tất cả các truy vấn trong sổ làm việc hiện tại của bạn
• Thanh công thức. Công thức của phép biến đổi hiện tại sẽ được chỉ định ở đây bằng ngôn ngữ M
• Xem trước dữ liệu. Bạn có thể xem bản xem trước dữ liệu của mình dựa trên bước chuyển đổi hiện tại. Bạn có thể truy cập các lệnh chuyển đổi khác nhau bằng cách nhấp chuột phải vào tiêu đề cột hoặc bằng cách nhấp vào tùy chọn bộ lọc của tiêu đề cột tương ứng
• Tính chất. Phần này bao gồm một danh sách các bước truy vấn. Tại đây, bạn sẽ có thể đặt tên cho truy vấn của mình. Đặt tên truy vấn là một bước quan trọng để xác định truy vấn dễ dàng
• Các bước áp dụng. Mỗi bước chuyển đổi bạn thực hiện sẽ được ghi lại ở đây theo thứ tự thời gian. Bạn có thể thêm, xóa, chỉnh sửa hoặc sắp xếp lại các bước nếu cần

Đây là tất cả về giao diện biên tập. Bây giờ, hãy tiếp tục tìm hiểu một ví dụ chuyển đổi đơn giản trên Trình chỉnh sửa

Thực hiện theo các bước bên dưới để tìm hiểu cách sắp xếp bảng dựa trên một cột

• Đầu tiên, tải dữ liệu lên Editor
• Sau đó, chọn cột bạn muốn sắp xếp
• Nhấp vào biểu tượng bộ lọc, như trong hình

• Giờ đây, bạn có thể sắp xếp dữ liệu theo thứ tự Tăng dần hoặc Giảm dần từ trình đơn thả xuống.  

• Khi nhấp vào OK, bảng được sắp xếp dựa trên cột 'Tên' theo thứ tự bảng chữ cái

Bạn sẽ thấy mã M trong thanh công thức. Điều này được sử dụng để ghi lại các bước áp dụng.  

• Các phép biến đổi được áp dụng sẽ phản ánh trong phần 'Các bước đã áp dụng'

Nhiều phép biến đổi khác có thể được thực hiện trên Trình chỉnh sửa. Sau bước này, chúng tôi cần tải dữ liệu vào bảng tính Excel của bạn.  

3. Xuất ra Excel

Sau khi thực hiện tất cả các thao tác trên trình soạn thảo, chúng tôi sẽ phải xuất nó ra bảng tính Excel của chúng tôi. Để thực hiện việc này, hãy bấm vào tùy chọn Đóng và Tải trên phần Dải băng của Power Query Editor

Chương trình Thạc sĩ Phân tích Kinh doanh

Khi nhấp vào tùy chọn này, Trình chỉnh sửa sẽ đóng và tải kết quả vào trang tính của bạn.   

Trong phần tiếp theo, chúng ta sẽ xem xét các cách khác nhau để chúng ta có thể Nhập dữ liệu vào trang tính Excel của mình

Các cách khác nhau để nhập dữ liệu vào trang tính Excel

Dưới đây là hướng dẫn chi tiết về cách nhập dữ liệu từ nhiều nguồn dữ liệu khác nhau

1. Nhập dữ liệu từ tệp văn bản

Làm theo các bước để nhập tệp văn bản bằng Power query

• Nhấp vào tab Dữ liệu -> Tệp văn bản/CSV
• Khi chúng tôi đã chọn tùy chọn “Tệp văn bản/CSV”, hộp thoại ‘Nhập dữ liệu’ sẽ được mở
• Chọn tệp văn bản mong muốn và nhấp vào Nhập
• Một hộp thoại được mở ra, hiển thị bản xem trước của dữ liệu chứa
• Cuối cùng nhấn Load để nhập dữ liệu

2. Nhập dữ liệu từ tệp CSV

Bạn có thể sử dụng Power Query để nhập từ tệp CSV bằng cách thực hiện theo các bước bên dưới

• Nhấp vào tab Dữ liệu -> Tệp văn bản/CSV
• Khi chúng tôi đã chọn tùy chọn “Tệp văn bản/CSV”, hộp thoại “Nhập dữ liệu” sẽ được mở
• Chọn tệp CSV mong muốn và nhấp vào nhập
• Một hộp thoại được mở ra, hiển thị bản xem trước của dữ liệu chứa
• Cuối cùng nhấn Load để nhập dữ liệu

3. Nhập một nguồn dữ liệu từ sổ làm việc Excel

Để nhập một Nguồn dữ liệu duy nhất, hãy làm theo các bước sau

• Nhấp vào tab Dữ liệu -> Nhận lệnh Nhận dữ liệu. Điều này sẽ mở ra một trình đơn thả xuống. Trình đơn thả xuống cung cấp các tùy chọn khác nhau để chúng tôi nhập dữ liệu của mình. Để nhập từ sổ làm việc Excel, chúng tôi chọn tùy chọn 'Từ tệp' rồi chọn 'Từ sổ làm việc'
• Excel mở ra hộp thoại giúp chúng ta điều hướng và chọn sổ làm việc
• Khi chúng tôi đã điều hướng đến vị trí sổ làm việc, chúng tôi có thể nhấp vào nó rồi nhấp vào 'Mở'
• Thao tác này sẽ mở ra hộp thoại điều hướng. Hộp thoại điều hướng cung cấp cho bạn một tập hợp các nguồn dữ liệu
• Từ đây, chúng tôi có thể chọn dữ liệu mà chúng tôi muốn làm việc
• Cuối cùng, nhấp vào 'Tải' để nhập dữ liệu

khóa học miễn phí. Giới thiệu về MS Excel

4. Nhập nhiều nguồn dữ liệu từ sổ làm việc Excel

Các bước sau đây sẽ giúp bạn nhập nhiều nguồn dữ liệu từ sổ làm việc Excel

• Nhấp vào tab Dữ liệu, theo sau là lệnh Nhận dữ liệu. Khi nhấp vào đây, một menu thả xuống sẽ mở ra. Trình đơn thả xuống cung cấp các tùy chọn khác nhau để chúng tôi nhập dữ liệu của mình. Để nhập từ sổ làm việc Excel, chúng tôi chọn tùy chọn 'Từ tệp' rồi chọn 'Từ sổ làm việc'
• Excel mở ra hộp thoại giúp chúng ta điều hướng và chọn sổ làm việc
• Khi chúng tôi đã điều hướng đến vị trí sổ làm việc, chúng tôi có thể nhấp vào nó rồi nhấp vào 'Mở'
• Thao tác này sẽ mở ra hộp thoại điều hướng. Hộp thoại điều hướng cung cấp cho bạn một tập hợp các nguồn dữ liệu
• Trong hộp thoại điều hướng, có một tùy chọn để 'Chọn nhiều mục'. Khi chọn tùy chọn này, chúng tôi có thể chọn nhiều mục
• Từ đây, chúng tôi có thể chọn nhiều nguồn dữ liệu mà chúng tôi muốn làm việc
• Cuối cùng nhấn Load để nhập dữ liệu

Vì vậy, đây là một vài kỹ thuật mà bạn có thể nhập dữ liệu vào Excel. Tiếp tục, hãy xem bản trình diễn đơn giản về cách bạn có thể Nhập dữ liệu từ tệp CSV

Bản demo để lấy dữ liệu từ tệp CSV

Chúng tôi sẽ giải thích cách nhập dữ liệu từ tệp CSV. Quá trình này đơn giản và bao gồm một vài bước

Nhập dữ liệu từ tệp CSV

• Nhấp vào tab Dữ liệu, sau đó tìm thấy lệnh tệp Văn bản/CSV
• Khi chúng tôi đã chọn tùy chọn “Tệp văn bản/CSV”, hộp thoại ‘Nhập dữ liệu’ sẽ được mở

• Chọn tệp CSV mong muốn và nhấp vào nhập.  

• Một hộp thoại được đặt tên theo tệp CSV được mở. Nó hiển thị bản xem trước của dữ liệu chứa
• Cuối cùng, nhấp vào 'Tải' để nhập dữ liệu

Như bạn có thể nhận thấy, 14 hàng được tải vào trang tính Excel

Bây giờ, hãy tiếp tục và hiểu các tác vụ và chuyển đổi khác nhau có thể được thực hiện bằng Power Query.  

Chương trình thạc sĩ phân tích dữ liệu

Bạn có thể thực hiện những chuyển đổi cơ bản nào bằng Power Query?

Trong phần này, chúng ta hãy xem xét các hàm chuyển đổi khác nhau có thể được thực hiện dễ dàng với sự trợ giúp của một vài cú nhấp chuột

1. Hàm định dạng văn bản

Trong phần này, bạn sẽ học cách định dạng văn bản ở dạng Chữ hoa, Chữ thường và hiểu cách sử dụng thao tác Trim

CHỮ HOA

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Lấy & Chuyển đổi Dữ liệu của tab Dữ liệu Excel. Thao tác này sẽ mở Trình chỉnh sửa, cho phép chúng tôi chỉnh sửa dữ liệu

Bước 2. Nhấp vào tên cột rồi chuyển đến tab “Chuyển đổi”, tab này sẽ hiển thị nhiều tùy chọn. Nhấp vào tùy chọn Định dạng văn bản sẽ mở ra menu thả xuống với tùy chọn chỉnh sửa văn bản là 'chữ HOA'

Bước 3. Cuối cùng, khi chọn tùy chọn chỉnh sửa CHỮ HOA, tất cả văn bản trong cột đã cho sẽ được chuyển thành chữ hoa

CHỮ HOA

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Get & Transform Data của tab dữ liệu Excel. Thao tác này sẽ mở Trình chỉnh sửa, cho phép chúng tôi chỉnh sửa dữ liệu

Bước 2. Nhấp vào tên cột và sau đó chuyển đến tab 'Chuyển đổi', tab này sẽ hiển thị nhiều tùy chọn. Nhấp vào tùy chọn Định dạng văn bản sẽ mở ra menu thả xuống với tùy chọn chỉnh sửa văn bản là 'CHỮ THƯỜNG'

Bước 3. Như bạn có thể thấy, tất cả văn bản từ cột đã chọn sẽ được chuyển thành chữ thường

TRIM

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Get & Transform Data của tab dữ liệu Excel. Thao tác này sẽ mở Power Query Editor, cho phép chúng tôi chỉnh sửa dữ liệu

Bước 2. Để xóa tất cả các khoảng trắng thừa khỏi dữ liệu, hãy nhấp vào tên cột, sau đó chọn tab 'Chuyển đổi', hiển thị các tùy chọn khác nhau. Nhấp vào tùy chọn 'Định dạng' sẽ hiển thị menu thả xuống với tùy chọn chỉnh sửa văn bản có tên 'Cắt'

Bước 3. Cuối cùng, khi chọn tùy chọn Trim edit, tất cả các khoảng trắng thừa trong cột đã cho sẽ bị xóa

khóa học miễn phí. Khái niệm cơ bản về phân tích kinh doanh

2. Tách một cột bằng dấu phân cách

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Get & Transform Data của tab dữ liệu Excel. Thao tác này sẽ mở Trình chỉnh sửa, cho phép chúng tôi chỉnh sửa dữ liệu

Bước 2. Để tách cột với sự trợ giúp của dấu phân cách từ dữ liệu, hãy nhấp vào tab 'Chuyển đổi', sau đó là tùy chọn 'Tách cột'. Thao tác này sẽ hiển thị menu thả xuống với tùy chọn phân chia dữ liệu theo Dấu phân cách

Bước 3. Một hộp thoại xuất hiện nơi bạn có thể chọn một dấu phân cách. Sau đó bấm vào OK

Bước 4. Bây giờ, chúng ta có thể thấy rằng dữ liệu được chia thành hai cột liên quan đến dấu phân cách

3. Chuyển bảng dữ liệu

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Get & Transform Data của tab dữ liệu Excel. Thao tác này sẽ mở Trình chỉnh sửa, cho phép chúng tôi chỉnh sửa dữ liệu

Bước 2. Vì muốn xoay hàng thành cột ta phải vào tab Transform. Chọn xong nó sẽ hiện ra 1 tùy chọn Transpose dữ liệu

Bước 3. Khi nhấp vào tùy chọn chuyển vị, các hàng sẽ được chuyển thành cột. Để tải các thay đổi vào một trang tính mới, hãy chuyển đến tab Trang chủ và nhấp vào 'Đóng và tải'

4. Loại bỏ các bản sao bằng Power Query

Bước 1. Tải dữ liệu cần thiết lên Power Query Editor. Điều này có thể được thực hiện bằng cách chọn nguồn dữ liệu tương ứng từ phần Get & Transform Data của tab dữ liệu Excel. Thao tác này sẽ mở Trình chỉnh sửa, cho phép chúng tôi chỉnh sửa dữ liệu

Nhìn vào dữ liệu trùng lặp được đánh dấu trong hình ảnh bên dưới

Bước 2. Bây giờ, chúng ta cần điều hướng đến tab Trang chủ → Tùy chọn Xóa hàng, tùy chọn này sẽ mở ra một menu thả xuống. Nhấp vào tùy chọn 'Xóa bản sao'.  

Bước 3. Như bạn có thể nhận thấy, dữ liệu hiện không còn trùng lặp. Để lưu bảng đã cập nhật mà không có hàng trùng lặp, hãy chuyển đến tab Trang chủ và nhấp vào 'Đóng và tải'.  

Kết hợp truy vấn

Power Query có hai tùy chọn khác nhau giúp chúng tôi kết hợp các bộ dữ liệu khác nhau. Hai lựa chọn là

• nối thêm
• hợp nhất

Phân tích kinh doanh MIỄN PHÍ với khóa học Excel

NỘP

Trong Power Query, thao tác chắp thêm sẽ tạo một bảng mới bằng cách nối tất cả các hàng từ truy vấn đầu tiên, theo sau là tất cả các hàng từ truy vấn thứ hai. Thực hiện theo các bước bên dưới để hiểu cách thực hiện thao tác Nối thêm

Bước 1

• Đầu tiên, chúng ta phải tải dữ liệu vào sổ làm việc Excel. Trong phần trình diễn này, bạn sẽ tìm hiểu cách Nối dữ liệu từ tệp CSV
• Điều này có thể được thực hiện bằng cách chọn tab Dữ liệu, theo sau là lệnh 'Tệp văn bản/CSV'.  
• Khi chúng tôi đã chọn tùy chọn, hộp thoại Nhập dữ liệu sẽ mở ra. Chọn tệp CSV mong muốn và nhấp vào nhập
• Một hộp thoại mở ra, hiển thị bản xem trước của dữ liệu chứa. Nhấp vào 'Tải' sẽ nhập dữ liệu vào một trang tính mới.  
• Tiếp tục bước này để thêm dữ liệu cần thiết vào trang tính mới

Bước 2. Bây giờ, để nối thêm dữ liệu có sẵn trên các trang tính khác nhau, chúng ta có thể điều hướng đến Tab Dữ liệu. Tại đây, chúng ta có thể tìm thấy một tùy chọn có tên là Lấy dữ liệu, nhấp vào tùy chọn này sẽ mở ra một menu thả xuống. Bạn sẽ tìm thấy một tùy chọn có tên Kết hợp truy vấn. Khi chọn nó, bạn sẽ tìm thấy tùy chọn 'Nối'

Bước 3. Nhấp vào 'Nối' sẽ mở ra một cửa sổ với các tùy chọn khác nhau, nơi chúng tôi có thể chọn nối thêm hai hoặc nhiều hơn ba bảng. Tiếp theo, chúng ta phải chọn các trang tính phải được thêm vào. Khi hoàn tất, chúng ta có thể nhấp vào OK

Bước 4. Trình soạn thảo Power Query mở ra và dữ liệu hiện đã được thêm vào. Chúng tôi có thể nhấp vào 'Đóng và tải' để lưu những thay đổi này, thao tác này sẽ tải dữ liệu đã cập nhật vào bảng tính Excel

HỢP NHẤT

Tùy chọn Merge tương tự như hàm JOIN trong SQL. Hợp nhất là cách kết hợp hai truy vấn hiện có và tạo một truy vấn mới.  

Bước 1

• Đầu tiên, chúng ta phải tải dữ liệu vào sổ làm việc Excel. Trong ví dụ này, chúng tôi sẽ trình bày cách Hợp nhất dữ liệu từ tệp CSV
• Điều này có thể được thực hiện bằng cách nhấp vào tab Dữ liệu, sau đó là lệnh 'Tệp văn bản/CSV'.  
• Khi chúng tôi đã chọn tùy chọn, hộp thoại Nhập dữ liệu sẽ mở ra.  
• Chọn tệp CSV mong muốn và nhấp vào nhập.  
• Một hộp thoại mở ra, hiển thị bản xem trước của dữ liệu chứa. Nhấp vào 'Tải' sẽ nhập dữ liệu vào một trang tính mới.  
• Tiếp tục bước này để thêm tất cả các bộ dữ liệu cần thiết để hợp nhất vào các trang tính khác nhau

Bước 2. Bây giờ, để Hợp nhất dữ liệu có sẵn trong các trang tính khác nhau, chúng ta phải điều hướng đến tab Dữ liệu. Ở đây chúng ta có thể tìm thấy một tùy chọn gọi là Lấy dữ liệu. Khi nhấp vào nó, một menu thả xuống sẽ được hiển thị, trong đó có tùy chọn Kết hợp các truy vấn. Khi chọn mục này, hãy nhấp vào 'Hợp nhất'

Bước 3. Một cửa sổ sẽ được hiển thị nơi chúng tôi có thể chọn các trang tính mà chúng tôi muốn hợp nhất. Bây giờ, chọn hai cột bằng cách nhấp vào tiêu đề cột dựa vào đó chúng tôi muốn kết nối cả hai trang tính. Sau đó, bấm vào OK

Bước 4. Khi đã xong, trình soạn thảo Power Query sẽ mở ra với một cột mới ở cuối chứa kết quả hợp nhất. Để lưu các thay đổi đã thực hiện, chúng tôi nhấp vào “Đóng và tải”

Bước 5. Dữ liệu đã hợp nhất hiện được tải vào trang tính Excel của chúng tôi.  

Đây là tất cả về việc kết hợp các truy vấn bằng cách sử dụng các hoạt động Hợp nhất và Nối.  

Bắt đầu câu chuyện phát triển sự nghiệp của bạn với PGP trong Phân tích kinh doanh. Có cơ hội thành thạo các công cụ Excel, Tableau và Python. Bắt đầu học ngay bây giờ

Phần kết luận

Trong bài viết này, bạn đã học cách tải dữ liệu bằng Power Query, thực hiện các phép biến đổi và xuất dữ liệu trở lại trang tính Excel của bạn. Sử dụng công cụ Power Query, bạn đang tiết kiệm rất nhiều thời gian bằng cách thực hiện nhiều chức năng chỉ với sự trợ giúp của một vài cú nhấp chuột.  

Cho dù bạn quan tâm đến việc học những kiến ​​thức cơ bản về Excel hay muốn phát triển các kỹ năng Excel nâng cao hơn, Simplilearn đều có Khóa học miễn phí về Phân tích kinh doanh với Excel dành cho bạn

Nếu bạn có bất kỳ câu hỏi nào cho chúng tôi, vui lòng đề cập đến chúng trong phần nhận xét của bài viết Power Query này và chúng tôi sẽ có các chuyên gia của chúng tôi trả lời ngay cho bạn