Xu Hướng 6/2023 # How To Create A Microsoft Query In Excel (Excel Query) # Top 13 View | Hoisinhvienqnam.edu.vn

Xu Hướng 6/2023 # How To Create A Microsoft Query In Excel (Excel Query) # Top 13 View

Bạn đang xem bài viết How To Create A Microsoft Query In Excel (Excel Query) đượ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.

Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel formulas or a VBA Macro. A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!

What can I do with MS Query?

Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook

Access – you can extract data from Access Database files

MS SQL Server – you can extract data from Microsoft SQL Server Tables

CSV and Text – you can upload CSV or tabular Text files

Step by Step – Microsoft Query in Excel

In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.

Open the MS Query (from Other Sources) wizard Select the Data Source Select Excel Source File Select Columns for your MS Query Return Query or Edit Query

Return Data to Microsoft Excel – this will return your query results to Excel and complete the Wizard

View data or edit query in Microsoft Query – this will open the Microsoft Query window and allow you to modify you Microsoft Query

Optional: Edit Query Import Data

AS you can see there are quite a lot of steps needed to achieve something potentially pretty simple. Hence there are a couple of alternatives thanks to the power of VBA Macro….

MS Query – Create with VBA Sub ExecuteSQL() Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "Sn14" 'AnalystCave.com On Error GoTo ErrorHandl Dim SQL As String, sConn As String, qt As QueryTable SQL = InputBox("Provide your SQL Query", "Run SQL Query") If SQL = vbNullString Then Exit Sub sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;;Password=;User ID=Admin;Data Source=" & _ chúng tôi & "/" & chúng tôi & ";" & _ "Mode=Share Deny Write;Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Set qt = ActiveCell.Worksheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveCell) With qt .CommandType = xlCmdSql .CommandText = SQL .Name = Int((1000000000 - 1 + 1) * Rnd + 1) .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery:=False End With Exit Sub ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear End Sub

Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+ SHIFT+ S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.

Learning SQL with Excel

Creating MS Queries is one thing, but you need to have a pretty good grasp of the SQL language to be able to use it’s true potential. I recommend using a simple Excel database (like Northwind) and practicing various queries with JOINs.

Alternatives in Excel – Power Query MS Query vs Power Query Conclusions

, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:

You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query

MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:

Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s

Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations

How To Combine Tables With Power Query

Bottom Line: Learn how to combine tables in Excel using Power Query.

Skill Level: Intermediate

Video Tutorial Download the Excel File

If you’d like to download the file that I use in the video, you can do so here:

Here is the file that contains the VBA macro to create Power Query connections to all tables in the workbook.

Combining Tables

If you have tables on several worksheets that contain the same type of data and you are looking to combine them into one master table, Power Query can help you do it quickly and effectively. This is a great alternative to copying and pasting data piece by piece, which can get tedious if there are several tables that you want to merge.

There are just two prerequisites to keep in mind.

Prerequisite #1

All of the sheets or data sets that you are looking to combine must be formatted as Excel Tables, not just data set up in a table format.

To turn a data set into an Excel Table, just select any cell in the set and then choose Format as Table on the Home tab. It’s usually a good idea to name the table after you’ve created/inserted it.

If you are relatively new to Excel Tables, check out my Beginner’s Guide to Excel Tables. Here are some useful Tips & Shortcuts for Inserting Excel Tables, and this post will give you some Best Practices for Naming Excel Tables.

Prerequisite #2

The tables you are working with must contain the same column headings, though they do not have to be in the same order. If you are working with columns that have similar data, but your headings are not the same, Power Query will put them into different columns when it combines them.

There are ways around this, which I will cover in a future post.

The Setup Work in Power Query

Now you can create queries in Power Query. First we will create connection queries for each table. Then we will combine those queries with an Append query to combine or stack the data.

1. Create Connection Queries to the Tables

To combine, or append, your tables together, you need to create a connection to each of them in Power Query.

This brings up a preview of your data. To create a connection:

Select Close & Load To…

That brings up the Import Data window. From here, select Only Create Connection.

You can see the connection you’ve just created in the Queries & Connections pane. If ever you don’t see the Queries & Connections pane, you can open it by selecting that button on the Data tab in the ribbon.

This process of creating connections must be repeated for every table that you want to append. Again, you only need to do this work one time for the initial setup. However, here are a few tips to speed up the process.

Use the Table Connections Macro

Since creating and connecting lots of tables can be time-consuming, I’ve created a macro that automates it. The macro loops through all tables in the workbook and creates connection only queries for any table that do not have queries yet.

I will write a post in the future that explains the macro. However, you can download the file that contains the VBA macro code here.

The macros runs on the Active Workbook. You can add the macro to your Personal Macro Workbook and add a macro button to the Ribbon or Quick Access Toolbar to run it on any open workbook.

Close & Load Settings

If you’d rather not use a macro, you can also shorten the process by changing the setting of the Close & Load split-button. The default for the top half of that button will load the output table to a new sheet, but you can adjust the settings so that it only creates a connection instead. To change the setting:

Go to the File menu.

Select Options and Settings.

Choose Query Options.

That will bring up the Query Options window, where you can select Specify custom default load settings.

Deselect the Load to worksheet option.

Hit OK.

Just remember to change this setting back once you’ve finished connecting all of your tables.

2. Combining Connected Tables with Append

Once all of your tables are connected, it’s a piece of cake to consolidate them:

This brings up the Append window, where we can select Three or more tables. This allows us to move any or all of the tables that we’ve connected from our Available tables (on the left) to the list of Tables to append (on the right).

Once you hit OK, you will be taken back to the Power Query editor, where you can see a preview of the combined tables. You can make adjustments and transformations to the data before closing the editor and loading the data to a new worksheet.

Updating & Refreshing the Data

This means we have fully automated this process. You do NOT have repeat the steps above every time your data changes or you get new rows in your tables.

Adding New Tables

If you ever want to add new tables to the query (or exlcude existing ones) you can reopen the Append window by:

Opening the Query Settings pane if it’s not already visible (View tab, then Query Settings).

This opens the Append window, where you can add or delete tables.

The new columns will still need to have the same column header name on each sheet. If any of the tables are missing columns, then Power Query will fill the rows for that table with blank (null) values in the append query and output table.

Other Power Query Posts

If you’re just getting started with Power Query, check out my overview post here: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool.

Then get Power Query up and running with this tutorial: The Complete Guide to Installing Power Query.

Free Training Webinar on the Power Tools

Right now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.

It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.

You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.

Tạo Các Công Thức Power Query Trong Excel

Tạo một công thức đơn giản

Để xem ví dụ về công thức đơn giản, chúng ta hãy chuyển đổi một giá trị văn bản thành kiểu chữ thích hợp bằng cách dùng công thức Text.Proper() .

Trong thanh công thức Trình soạn thảo Truy vấn, hãy nhập = Text.Proper(“text value”), và nhấn Enter hoặc chọn biểu tượng Enter.

Power Query cho bạn thấy kết quả trong ngăn kết quả công thức.

Để xem kết quả trong trang tính Excel, hãy chọn Đóng & Tải.

Kết quả sẽ trông như thế này trong một trang tính:

Bạn cũng có thể tạo công thức truy vấn nâng cao trong Trình soạn thảo Truy vấn.

Tạo công thức nâng cao

Để xem ví dụ về công thức nâng cao, chúng ta hãy chuyển đổi văn bản trong một cột thành kiểu chữ thích hợp bằng cách kết hợp nhiều công thức. Bạn có thể sử dụng Ngôn ngữ Công thức Power Query để kết hợp nhiều công thức thành các bước truy vấn có kết quả tập dữ liệu. Có thể nhập kết quả vào một trang tính Excel.

Chẳng hạn, giả sử bạn có một bảng Excel chứa các tên sản phẩm mà bạn muốn chuyển thành kiểu chữ thích hợp.

Bảng ban đầu trông như thế này:

Và bạn muốn bảng kết quả trông giống như thế này:

Chúng ta hãy xem qua các bước công thức truy vấn để thay đổi bảng ban đầu sao cho các giá trị trong cột Tên Sản phẩm có kiểu chữ thích hợp.

Ví dụ về truy vấn nâng cao bằng Trình soạn thảo Nâng cao

Tạo một chuỗi các bước công thức truy vấn bắt đầu bằng câu lệnh let. Vui lòng lưu ý rằng Ngôn ngữ Công thức Power Query có phân biệt chữ hoa, chữ thường.

Mỗi bước công thức truy vấn xây dựng trên bước trước đó bằng cách tham chiếu đến một bước theo tên.

Tạo đầu ra cho công thức truy vấn bằng câu lệnh in. Thông thường, bước truy vấn sau cùng được dùng làm kết quả tập dữ liệu cuối cùng.

Bước 1 – Mở Trình soạn thảo Nâng cao

Trong Trình soạn thảo Truy vấn, hãy chọn Trình soạn thảo Nâng cao.

Bạn sẽ thấy Trình soạn thảo Nâng cao.

Bước 2 – Xác định nguồn ban đầu

Trong Trình soạn thảo Nâng cao:

Dùng câu lệnh let gán Source = công thức Excel.CurrentWorkbook(). Thao tác này sẽ sử dụng bảng Excel làm nguồn dữ liệu. Để biết thêm thông tin về công thức Excel.CurrentWorkbook(), hãy xem Excel.CurrentWorkbook.

Gán Source cho kết quả in.

letSource = Excel.CurrentWorkbook(){[Name="Orders"]}[Content] inSource

Truy vấn nâng cao của bạn sẽ trông giống như thế này trong Trình soạn thảo Nâng cao.

Để xem kết quả trong một trang tính:

Bấm Xong.

Trong ribbon Trình soạn thảo Truy vấn, bấm Đóng & Tải.

Kết quả sẽ trông như thế này trong một trang tính:

Bước 3 – Tăng cấp hàng đầu tiên thành tiêu đề

Để chuyển đổi các giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, trước tiên bạn cần tăng cấp hàng đầu tiên thành tiêu đề cột. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

Thêm #”First Row as Header” = công thức Table.PromoteHeaders() vào các bước công thức truy vấn của bạn và tham chiếu đến Source là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.PromoteHeaders(), hãy xem Table.PromoteHeaders.

Gán #”First Row as Header” cho kết quả in.

let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"

Kết quả sẽ trông như thế này trong một trang tính:

Bước 4 – Thay đổi mỗi giá trị trong một cột thành kiểu chữ thích hợp

Để chuyển đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, bạn dùng Table.TransformColumns() và tham chiếu đến bước công thức truy vấn “First Row as Header”. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

Thêm #”Capitalized Each Word” = công thức Table.TransformColumns() vào các bước công thức truy vấn của bạn và tham chiếu đến #”First Row as Header” là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.TransformColumns(), hãy xem Table.TransformColumns.

Gán #”Capitalized Each Word” cho kết quả in.

let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source),#"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}}) in#"Capitalized Each Word"

Kết quả cuối cùng sẽ thay đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp và trông giống như thế này trong trang tính:

Với Ngôn ngữ Công thức Power Query bạn có thể tạo các truy vấn dữ liệu từ đơn giản đến nâng cao để khám phá, kết hợp và tinh chỉnh dữ liệu. Để tìm hiểu thêm về Power Query, hãy xem Trợ giúp Microsoft Power Query cho Excel.

Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query

Microsoft Power Query là một công cụ hữu ích để làm việc với dữ liệu bên trong Microsoft Excel. Nó đi kèm với rất nhiều tính năng giúp quản lý bộ dữ liệu đơn giản nhưng mạnh mẽ.

Power Query hữu ích nhất khi làm việc với nhiều bộ dữ liệu. Nó hoạt động như một cầu nối giữa các bộ dữ liệu và Microsoft Excel.

Để chứng minh điều đó, bài viết sẽ hướng dẫn bạn thực hiện một tác vụ chung nơi Power Query tỏa sáng: Kết hợp hai bộ dữ liệu khác nhau thành một.

Microsoft Power Query là gì?

Microsoft Power Query là một công cụ có trong Microsoft Excel cho Windows. Nó chỉ được bao gồm trong các phiên bản Excel 2023 hoặc mới hơn, vì vậy hãy kiểm tra các phiên bản này để bắt đầu sử dụng Power Query. Người dùng mới nên đảm bảo rằng đã nắm được mọi thứ cơ bản trong Excel trước khi bắt đầu sử dụng Power Query.

Những gì Power Query cho phép bạn làm là load dữ liệu từ một số nguồn khác nhau, chỉnh sửa dữ liệu đó và sau đó nhập dữ liệu vào bảng tính Excel. Hãy tìm hiểu thêm về lý do tại sao bạn nên bắt đầu sử dụng Microsoft Power Query.

Một trong những loại nguồn mà bạn có thể lấy dữ liệu là các trang tính Excel khác. Để có được dữ liệu minh họa cho Power Query trong bài viết này, ví dụ sẽ sử dụng 2 bảng tính Excel riêng biệt, chứa các bộ dữ liệu tương tự, sau đó kết hợp chúng thành một.

Thiết lập dữ liệu

Hãy chọn 2 workbook Excel, lần lượt là “Cars.xlsx” và “Trucks.xlsx” trong ví dụ. Nội dung của những bảng tính này rất đơn giản. Mỗi bảng tính chứa một bảng mô tả một số phương tiện nhất định được phân loại theo các cột: “Make”, “Model”, “Color” và “Year”.

Ví dụ cũng đang tạo ra một workbook trống có tiêu đề “Vehicles.xlsx” để thực hiện công việc của mình.

Để đơn giản, bài viết đang sử dụng hai bộ dữ liệu có cùng thông tin tiêu đề. Mục tiêu ở đây là tạo ra một bảng mới với cả thông tin về xe hơi và xe tải.

Những workbook này đã được lưu vào máy tính. Bây giờ, hãy bắt tay làm việc với Power Query nào!

Load dữ liệu vào Power Query Editor

Để bắt đầu, tất cả những gì bạn cần làm là mở workbook chứa dữ liệu đã tạo. Trong trường hợp này, workbook là “Vehicles.xlsx”.

Power Query rất dễ sử dụng. Bạn không cần phải mở workbook có chứa dữ liệu để trích xuất những gì mình cần. Trong “Vehicles.xlsx”, điều hướng đến tab Data chứa các tùy chọn cho workbook.

Bạn sẽ thấy tùy chọn “Get Data” trong các thanh công cụ. Tùy chọn này là mục nhập vào Power Query và sẽ cho phép bạn chọn nguồn dữ liệu của mình.

Bạn muốn làm việc với một workbook Excel, vì vậy hãy chọn tùy chọn “From File”, theo sau là “From Workbook”.

Trình chỉnh sửa sẽ mở file explorer và bạn có thể điều hướng đến workbook ở bất cứ đâu trên PC. Trước tiên, bạn sẽ chọn file “Cars.xlsx”.

Khi bạn chọn xong file của mình, menu Power Query Navigator sẽ load để hiển thị cho bạn bản xem trước của trang tính đã chọn. Lưu ý file “Cars.xlsx”, cũng như trang tính được hiển thị trong Navigator. Nhấp vào trang tính và bảng sẽ load trên menu giống như nó xuất hiện trong bảng tính! Nhấp vào “Transform Data” để mở dữ liệu trong trình chỉnh sửa.

Sử dụng Power Query Editor

Power Query Editor có rất nhiều tùy chọn, đừng lo lắng về những thứ đó ngay bây giờ, vì bạn sẽ chỉ làm quen với những điều cơ bản trong bài viết này. Thoạt nhìn, dữ liệu đã được upload đúng cách! Nó trông giống như một bảng Excel. Dữ liệu được sắp xếp theo hàng và cột, với các tiêu đề được đặt tên ở trên cùng.

Bên phải bạn là một menu có tên là “Query Settings” có chứa một cửa sổ “Applied Steps”. Các bước ở đây là thay đổi được thực hiện cho bảng. Lưu ý phần này để sử dụng sau.

Ở phía xa bên trái, nhấp vào biểu tượng “Queries” sẽ mở rộng menu và hiển thị tên của bảng bạn đang làm việc. Power Query có thể lấy nhiều bảng cùng một lúc và sẽ hiển thị tất cả trong thanh công cụ này.

Để nối hai bảng với nhau, cả hai bảng cần phải được upload lên Power Query. Bạn đã có một cái, hãy upload nốt cái kia.

Thêm nguồn dữ liệu thứ hai

Bên trong trình chỉnh sửa, ở góc trên cùng bên phải, là nút “New Source”. Nút này cho phép bạn chọn một nguồn dữ liệu bổ sung và thêm nó vào Power Query Editor.

Chọn “File” và sau đó là “Excel” để mở file explorer. Điều hướng đến “Trucks.xlsx” để chọn file cần nhập.

Giống như truy vấn đầu tiên, Navigator sẽ xuất hiện, cho phép bạn chọn bảng của mình từ bảng tính. Chọn bảng tính, rồi nhấp vào OK. Bây giờ, bạn sẽ thấy cả bảng ô tô và xe tải trong thanh công cụ Queries. Nhấp vào một trong hai bảng sẽ hiển thị dữ liệu trong bảng tính đó.

Nối các bảng dữ liệu

Khi đã thực hiện xong tất cả các bước chuẩn bị, việc kết hợp các bảng dữ liệu thực sự khá đơn giản. Nhấp vào bảng “Cars”, và trên thanh công cụ, hãy chọn “Append Queries” trong mục “Combine”.

Cửa sổ Append sẽ xuất hiện và nhắc đến bảng mà bạn muốn kết hợp vào bảng đã chọn. Hãy nhớ rằng bạn đang ở trên bảng “Cars” và muốn kết hợp bảng “Trucks” với nó. Nhấp vào bảng “Trucks” bên trong “Table to append” để kết hợp và chọn OK.

Đây là kết quả:

Lưu ý rằng tất cả dữ liệu xe hơi và xe tải sẽ được đặt trong cùng một bảng. Các bộ dữ liệu đã được kết hợp! Ngoài ra, hãy lưu ý rằng Power Query đã tạo ra một “bước” mới trong danh sách của bạn, gọi là “Appended Query”.

Load dữ liệu vào trang tính

Điều cuối cùng cần làm là lấy dữ liệu được lưu trữ trong Power Query Editor và load nó vào bảng tính của bạn.

Ở góc trên cùng bên trái, nhấp vào “Close & Load” để đặt bảng vừa tạo vào Vehicles ở định dạng bảng. Khi nhấp vào, những điều sau đây sẽ xảy ra:

Trình chỉnh sửa sẽ lưu các bước truy vấn và thoát.

Workbook sẽ xuất hiện với dữ liệu được load dưới dạng bảng Excel.

Excel cũng sẽ mở menu Workbook Queries ở bên phải trang tính.

Bạn đã sẵn sàng làm việc với bảng hoàn toàn mới mà không cần mở bảng tính!

Bây giờ, bạn đã học được cách nối thêm dữ liệu bằng Power Query. Bạn có một công cụ mới mạnh mẽ để đơn giản hóa dữ liệu. Bây giờ dữ liệu đã được load vào Excel để sử dụng, bạn có thể làm việc với nó giống như bất kỳ bảng nào. Bạn có thể tạo biểu đồ từ các bảng Excel hoặc chèn ảnh hàng loạt vào ô Excel.

Cập nhật thông tin chi tiết về How To Create A Microsoft Query In Excel (Excel Query) 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!