Sometimes in work, it is necessary to fill in different values based on different interval gears. For example, in the student evaluation system, there are excellent or good based on different score intervals; the shipping cost is calculated based on different prices among different weight ranges. In Excel, it is generally necessary to use multiple nested IF or lookup functions to implement, which is cumbersome and prone to errors. Here is a simple and easy-to-use method, using the Excel plugin SPL XLL. SPL XLL has rich data operation functions that can solve many complex Excel problems. In interval association, the pseg() function is usually used to return the position sequence number of the data to be searched in the intervals.
For example:
1. Directly write the formula for interval association
The data is as follows:
Calculate the price of column B based on the purchase quantity in column A. The calculation rule is that different quantity intervals correspond to different prices, as shown in the table below:
For cases with few intervals, the formula can be directly written.
Write in cell B2:
=spl("=[15,13.75,13,12.5]([30,50,100,300,500].pseg@r(?1))",A2)
As shown in the figure:
?1 represents the data parameter to be passed in, which refers to A2, which is 221.
First, use the pseg function to calculate which segment number A2 is located in the interval [30,50,100,300,500], and then retrieve the corresponding segment number’s price from [15,13.75,13,12.5] to return. Here 221 is located in the third interval of 100-300, the psge() function will return 3, and then retrieve the third one from the price list, which is 13.
@r represents the front opening and back closing of segmented intervals.
Return result:
Then drag and copy B2 to each cell:
2. Use the association table to implement interval association
The psge() function can also be used for association with table parameters.
For example, there is the following data:
Calculate the values of the Price column in Sheet2. The calculation rule is to use Quantity to search in Sheet1, if the quantity is greater than StartQuantity and less than or equal to EndQuantity, return the Price of this row. Write the code in B2:
=spl("=E(?1).segp@r(StartQuantity,?2).Price",Sheet1!A$1:C$5,A2)
Similarly, ?1 and ?2 represent the data parameters to be passed in, here referring to Sheet1!A$1:C$5 and A2 respectively.
The whole statement represents finding the segment number of A2 in the StartQuantity column of Sheet1, and then retrieving the price of the corresponding segment number row to return.
The result is shown in the figure:
Similarly, drag B2 and copy to each row:
Using SPL XLL for interval association can avoid racking your brain with multiple IF statements. The code is intuitive, easy to understand, and very user-friendly.
Of course, SPL XLL also has many useful features, such as set operations, group calculations, batch processing, and so on. Using it to assist Excel can double work efficiency in seconds.
SPL XLL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
Reference cases: Desktop and Excel Data Processing Cases
Top comments (0)