Top 16 # Cách Tạo Query Trong Excel / 2023 Xem Nhiều Nhất, Mới Nhất 12/2022 # Top Trend | Trucbachconcert.com

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

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.

How To Create A Microsoft Query In Excel (Excel Query) / 2023

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

Cách Tạo Một Câu Hỏi Truy Vấn (Query) Căn Bản Trong Access 2022 / 2023

Sức mạnh thực sự của 1 cơ sở dữ liệu đến từ khả năng thu thập và phân tích dữ liệu thông qua việc sử dụng câu hỏi truy vấn (Query). Query giúp bạn lấy được các thông tin cần thiết có trong 1 hoặc một vài bảng dữ liệu khác dựa trên các điều kiện quy định sẵn. Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn cách tạo 1 câu hỏi truy vấn căn bản trong Access 2016

Định nghĩa về câu hỏi truy vấn

Câu hỏi truy vấn là một cách để tìm kiếm và tổng hợp dữ liệu từ một hay một vài bảng dữ liệu khác nhau. Chạy một câu hỏi query cũng giống như việc đưa ra một câu hỏi chi tiết cho cơ sở dữ liệu trả lời vậy. Khi bạn tạo lập 1 query trong Access thì bạn cũng đã tự thiết lập một chuỗi các điều kiện tìm kiếm để sang lọc được dữ liệu cần.

Câu hỏi truy vấn được sử dụng như thế nào

Query có khả năng tìm kiếm thông tin tốt hơn rất nhiều so với cách tìm kiếm thông thường hay các bộ lọc dữ liệu. Lý do là bởi nhờ có query mà ta có thể tổng hợp dữ liệu từ nhiều nguồn khác nhau. Chẳng hạn, lệnh Search có thể giúp bạn tìm được tên một người cụ thể trong danh sách khách hàng của công ty, trong khi thông qua bộ lọc filter thì ta có thể tìm được danh sách các món ăn được gọi trong tuần trước, tuy nhiên với Query bạn hoàn toàn có thể truy cập cùng lúc cả 2 loại dữ liệu trên. Hoặc bạn có thể dễ dàng thiết kế 1 query để tổng hợp danh sách các khách hàng kèm theo số điện thoại cũng như tên món ăn mà từng người lựa chọn trong tuần trước đó. Tóm lại query giúp bạn tìm được những thông tin không thể hiện rõ nếu chỉ xem qua loa nội dung bảng số liệu.

Kết quả của query sẽ được hiện lên trong bảng nhưng bạn sẽ phải sử dụng một cửa sổ khác để thiết kế nội dung của query đó. Cửa sổ này được gọi là Query Design và trong đó sẽ biểu thị từng bước lập một query là như thế nào

Để tìm danh sách các khách hàng sống ở thành phố Raleigh, ta tìm từ khóa Raleigh trong mục Thành phố. Đối với cư dân đền từ vùng ngoại ô lân cận, ta cũng sẽ điền họ vào danh sách khách mời. Điền thêm mã bưu điện 27513 làm 1 tiêu chí lựa chọn nữa

Nếu bạn cảm thấy đây không khác gì một bộ lọc dữ liệu cả thì bạn đã đúng. Query dạng 1 bảng số liệu chính là một bộ lọc nâng cao mà thôi.

Bước 1: Chọn tab Create trên thanh Ribbon, và tìm đến phần Queries

Bước 2: Chọn lệnh Query Design

Join Two Or More Tables In Excel With Power Query / 2023

In this tutorial, we will look at how you can join tables in Excel based on one or more common columns by using Power Query and Merge Tables Wizard.

Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you’ve messed up important information. If you are an experienced Excel pro, then you can possibly rely on VLOOKUP and INDEX MATCH formulas. A macro, you believe, could do the job in no time, if only you knew how. The good news for all Excel users – Power Query or Merge Tables Wizard can be your time-saver. The choice is yours.

How to join tables with Excel Power Query

In simple terms, Power Query (also known as Get & Transform in Excel 2016 and Excel 2019) is a tool to combine, clean and transform data from multiple sources into the format you need such as a table, pivot table or pivot chart.

Among other things, Power Query can join 2 tables into 1 or combine data from multiple tables by matching data in columns, which is the focus of this tutorial.

For the results to meet your expectations, please keep in mind the following things:

Power Query is a built-in feature in Excel 2016 and Excel 2019, but it can also be downloaded in Excel 2010 and Excel 2013 and used as an add-in. In earlier versions, some windows may look different from the images in this tutorial that were captured in Excel 2016.

For the tables to be combined correctly, they should have at least one common column (also referred to as a common id or key column or unique identifier). Also, the common columns should contain only unique values, with no repeats.

The source tables can be located on the same sheet or in different worksheets.

Unlike formulas, Power Query does not pull data from one table to another. It creates a new table that combines data from the original tables.

The resulting table does not update automatically. You should explicitly tell Excel to do this. Please see how to refresh a merged table.

Source data

As an example, let’s join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.

Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:

Table 1 is named Orders

Table 2 is named Products

Table 3 is named Commissions

Create Power Query connections

Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.

To save a table as a connection in Power Query, here’s what you do:

Select your first table (Orders) or any cell in that table.

This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.

Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).

When finished, you will see all the connections on the pane:

Merge two connections into one table

With the connections in place, let’s see how you can join two tables into one:

In the Merge dialog box, do the following:

Select your 1st table (Orders) from the first drop-down.

Select your 2nd table (Products) from the second drop-down.

In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).

Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word “Table” in all the cells. But don’t feel discouraged, you did everything right, and we are going to fix that in a moment!

Select the columns to add from the second table

At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:

In the box that opens, do this:

Keep the Expand radio button selected.

Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.

Uncheck the Use original column name as prefix box (unless you want the column name to be prefixed with the table name from which this column is taken).

As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:

If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.

Merge more tables (optional)

In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:

Save the table you’ve got in the previous step (shown in the screenshot above) as a connection:

The screenshot below shows my settings:

In this example, we add only the Commission column:

As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.

Import the merged table to Excel

With the resulting table in the Power Query Editor, there is just one thing left for you to do – load it in your Excel workbook. And it is the easiest part!

In the Import Data dialog box, select Table and New Worksheet options.

A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!

As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:

In the previous example, we were combining tables by matching data in one key column. But there is nothing that would prevent you from selecting two or more column pairs. Here’s how:

After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.

Merge Tables Wizard – quick way to join 2 tables in Excel

Now that you are familiar with the inbuilt tool, let me show you our approach to merging tables in Excel.

In this example, we will be combining the same tables that we joined with Power Query a moment ago. I have just added a few more rows to the second table to show you more capabilities of our add-in:

With the Merge Tables Wizard installed in your Excel, here’s what you need to do:

Please notice the Case-sensitive matching box at the top. Select it if you want to treat uppercase and lowercase text in the key columns as different characters. For this example, we don’t need that, so we leave the box unselected.

We select the Seller column because we have more rows in the second table and we want the new seller names to appear in the existing Seller column:

This step is very important because it determines how your tables will be merged. In this example, we go with the default options shown in the screenshot below. But I’d like to draw your attention to the following 2 boxes that can prevent overwriting your existing data in case you’ve chosen to update some columns:

Empty cells only

Only if cells in the lookup table contain data

With the default options, the wizard highlights the newly added rows and adds the Status column. If you don’t want any of that, clear the corresponding boxes in the last step.

To join three and more tables, simply repeat the above steps. Just remember to select the result of a previous merge as your main table.

This example has shown just one scenario that our wizard can handle, but there is much more to it! If you are curious to know other use cases, please check out these examples.

Also, you can download a a trial version of Ultimate Suite for Excel that includes Merge Tables Wizard as well as 60+ other useful tools.

In case you are looking to join tables in some other way, you may find the following resources useful.

Other ways to combine data in Excel:

Merge tables by column headers – join two or more tables based on column names. You can choose to combine all the columns or only the ones you select.

Combine multiple worksheets into one – copy multiple sheets into one summary worksheet. Of course, it’s not manual copy/pasting! You only indicate which worksheets to merge, and our Copy Sheets tool does the rest.

Compare two Excel files – how to compare two tables (worksheets) for differences and merge them into a single sheet.