How to do Excel table association – You are out if you know only VLOOKUP
The association of tables is a common task in work, and if you only think about using VLOOKUP, you are out. Although VLOOKUP can also solve many common problems of data lookup and referencing, it also has many drawbacks, such as cumbersome writing, poor readability, easy to step into hassles, and difficult to implement complex conditions. In practice, it can cause various headaches. And SPL XLL is much easier to use. It is an Excel plugin that excels at handling various complex data operations, and using it to do table associations is just a piece of cake.
Table association is usually widening the table and implemented with the join function in SPL XLL.
The data is as follows:
Associate two sheets based on the Name column, find the corresponding House for each animal, and concatenate the result to sheet1.
The operation is very simple. In a blank cell of Excel, write the following code:
=spl("=E(?1).join(Name,E(?2):Name,House)",Sheet1!A1:D4,Sheet2!A1:E9)
As shown in the following figure:
?1 and ?2 represent the data table to be calculated, here they refer to Sheet1!A1:D4 and Sheet2!A1:E9. join() is an association function that represents the association between the Name key in Sheet1 and the Name key in Sheet2, and concatenates the House field to Sheet1.
Return Result:
Still using the data of the previous example, associate based on the values of columns M, N, and O, and concatenate the corresponding House values in Sheet2 to the table in Sheet1.
Still use the join function, directly input multiple association column names, separated by ':'.
=spl("=E(?1).join(M:N:O,E(?2):M:N:O,House)",Sheet1!A1:D4,Sheet2!A1:E9)
Return Result:
The customer order data is as follows:
The order details are as follows:
It is necessary to identify customer order information with a total order amount greater than 1000.
Analysis: An order contains multiple products, and the total order amount is equal to the total amount of all products in the order. Therefore, the total amount of each order should be calculated in the detail table and orders>1000 should be filtered out. Then, the OrderID should be associated with the order data in Sheet1 to filter out eligible orders in the order data.
For ease of understanding, we use multiple lines of code to implement.
The code is as follows:
| A | |
|---|---|
| 1 | =E('Sheet1!A1:D8') |
| 2 | =E('Sheet2!A1:E17') |
| 3 | =A2.groups(OrderID;sum(Price*Quantity):Amount).select(Amount>1000) |
| 4 | =A1.join@i(OrderID,A3:OrderID) |
A3: goups() is a grouping aggregation function that groups A2 data by OrderID, calculates the total amount of each order, and then selects orders with Amount>1000 using the select() function.
A4: A1 and A3 are associated according to OrderID, and option @i means deleting rows in A1 that do not match. Only orders with a total amount greater than 1000 are retained in order data A1.
Multiple lines of SPL code are written in Excel as follows, and Alt-Enter is used to enter a carriage return:
Still, ctrl-enter returns the result, as shown in the following figure, with 3 orders with a total amount greater than 1000.
The freight standard table is as follows:
The table of transportation orders is as follows:
Calculate the actual shipping cost based on the freight standard table.
Analysis: actual shipping cost=first weight shipping cost + excess weight * excess weight unit price, where less than 1KG will be calculated as 1KG.
The code is as follows:
=spl("=E(?2).join(City,E(?1):City,First1KG+(ceil(WeightKG)-1)*Add1KG:Fee)",Sheet1!A1:C9,Sheet2!A1:C10)
Associate the order table and freight table according to City, reference the corresponding column to calculate the actual freight, and name it as Fee to concatenate it to the order table.
Return Result:
The sales details data is as follows:
The sales personnel table data is as follows:
Concatenate the gender of sales personnel to the sales detail table.
One sales person has multiple sales details, and it belongs to a many-to-one association. It can also be implemented using the join function.
=spl("=E(?1).join(Name,E(?2):Name,Gender)",Sheet1!A1:C25,Sheet2!A1:B13)
Return Result:
Using SPL XLL for association operations is simple and intuitive, and multi column associations and references are also implemented in one step, which is much easier to use than VLOOKUP.
Of course, the functionality of SPL XLL goes far beyond that. It also has many flexible data processing functions that can handle various complex Excel operations, and the syntax is very simple, making it very user-friendly.
SPL XLL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
Reference cases: esProc Desktop and Excel Processing