In the work process, we often need to quickly check and match between tables. The search function is generally the first choice of everyone. Commonly used ones include VLOOKUP, LOOKUP also has the classic INDEX SMALL IF combination and so on. However, these functions have many limitations. VLOOKUP can only support single condition search, LOOKUP can only find the first matching column, and the INDEX SMALL IF combination is too difficult to master. Don’t worry now, today I will introduce to you how to use Power Query to realize multi-table lookup and matching of various requirements at one time.
I have introduced Power Query to you before. Currently, only EXCEL2016 can be used directly. EXCEL2010 and 2013 must install plug-ins to use it, and other versions cannot be used. In EXCEL2016, all Power Query functions are embedded in the [Acquisition and Transformation] group under the "Data" tab.
The case is as shown in the figure. There are two worksheets in the workbook, namely sales group and sales. Now we need to divide the "sales" table according to the region and group. The order number and order amount in are matched to the "Sales Group" table.
is a typical multi-condition query, which searches for data that meets multiple conditions and returns multiple columns of data.
Since the regions and groups in the two tables cannot be used as unique values for the search, it is necessary to search and match based on the two items, and the order number and order amount columns must be matched. It would be too brain-consuming to implement this using a function. How to do it? The steps are as follows:
1. Click on the Data tab, New Query - From File - From Workbook.
2. Find the workbook in the "Import Data" window and click Import.
3. Click "Multiple Select" in the "Navigator" window, then select two worksheets and click "Edit".
After entering the Power Query editor, you can see the two imported worksheet queries in the query window on the left.
#4. Since the imported table uses column as the new title, in order to facilitate future operations, we first use the first row of the two queries as the title. Click on both queries and click "Use first row as title" under the Home tab.
Complete as follows:
5. Next, perform a merge query of the two tables. Select the table "Sales Group" where you want to fill in the content, and click "Merge Query into New Query" in the "Merge Query" drop-down menu under the Home tab.
6. In the "Merge Window", the first table is the table "Sales Group" to be filled in with matching content, and the second table containing matching information is selected in the drop-down window. Table "Sales". First, select the "large area" column of the two tables, and the two columns will turn green. This means that the two tables match data through the "region" column.
Then hold down the Ctrl key and select the "Group" column of the two tables again. At this time, the two table column labels "1" and "2" appear. Where 1 column matches 1 column and 2 columns matches 2 columns. Click OK.
Note: There are six types of connection types below. We choose the first "left outer", that is, the values in the first table are unique values. According to the selected columns to join all rows in the first table to matching rows in the second table. That is the function of our commonly used VLOOKUP. The merged query here selects the first type by default. If you are interested, we can introduce the other five connection types later.
7. The query window will generate a new query "Merge1", and the information in the "Sales" table will be matched in the new query table. Click the table in the sales column to preview. In the preview pane below, you can see all the contents of the sales table matched based on the same region and group.
Using this method, we can freely select the number of matching columns in the merge window, 2 columns, 3 columns or even more columns can be satisfied. This solves the problem of multi-condition search; and all contents of the matching table can be found according to the matching columns.
8. Now expand the content that needs to be imported into the table. Click the expand button to the right of the "Sales" column. In the expansion pane below, select the columns "Number of Orders" and "Order Amount" to be expanded. Do not check "Use original column name as prefix".
Complete as follows:
9.Finally upload this query to the form. Select the new query table and click "Close and Upload" under the Home tab.
This will upload all three query tables to the workbook and generate three new worksheets. The "Workbook Query" window will appear on the right. Click on the new query and the workbook will automatically jump to the corresponding query worksheet.
Complete as follows:
Okay, this is the end of the introduction to the merge query of Power Query. This query method connects two tables based on multiple matching columns and matches the tables. It is very helpful for complex multi-table queries in daily work. If you are interested, please leave me a message!
Related learning recommendations:excel tutorial
The above is the detailed content of Practical Excel skills sharing: merge query to achieve multi-table search and matching for various requirements at one time. For more information, please follow other related articles on the PHP Chinese website!