Manual statistics of a factory in recent years of different materials corresponding to the number and amount of returns, the amount of data is relatively large, there are thousands of lines, now the leadership requires to see different merchants, the top n items designated by the ranking.
As shown in the return data in the figure above, you need to see what is the material ** corresponding to the top three return amounts of each **merchant?In the case of not passing the formula, you can only manually filter a **merchant by sorting by amount in descending order to find the top three materials of this **merchant**.
Because there are too many **businessmen, it is unlikely that they will be screened, copied and pasted one by one, and leaders sometimes want to see the top three, and they may also want to see the top one, and the demand is dynamic. So I want to use formulas to achieve the above functions.
Knowledge point 1: Design skills for dynamically displaying the first n items.
Knowledge point 2: Combination application of filtering function, sorting function, deduplication function, and retention function.
Knowledge point 3: Text merge symbol "&" and sign to merge multiple columns of data;
Knowledge point 4: Custom text skills;
You need to display the first n items dynamically, you need to create a new cell, enter the number 3, here in order to display the effect, set the number 3 to a custom format:""before"#"item"", so that the content in the cell is displayed as data with Chinese prompts;
Before filtering the quotient, delete the quotient in the source data, that is, column a, and enter the function: =drop(unique(a2:a10000),-1);Get the data of the unique quotient. In order to facilitate everyone's understanding, here is the effect of the first ** business, the effect of the first business is OK, you can fill the formula down;
The first n project title: input function: =sequence(,g2), and set it to a custom format:""Section" # "First name"In this way, the display effect of the cell is the 1st, 2nd, and 3rd place.
The two-dimensional data needs to show that the total amount of returns is ranked in descending order, that is, the ** merchant with the most returns is ranked first, and the ** merchant who ranks first needs to look at the corresponding material code and quantity of the top 3 returns. Make the first requirement first and enter the formula:
*quotient: choosecols(sort(hstack(drop(unique(a2:a10000),-1),sumifs(e:e,a:a,drop(unique(a2:a10000),-1))))2,-1),1).
Amount: choosecols(sort(hstack(drop(unique(a2:a10000),-1),sumifs(e:e,a:a,drop(unique(a2:a10000),-1))))2,-1),2).
The effect is as follows:
*After the total amount of chain returns is ranked, the top three will be displayed for a single merchant, and the first merchant will be entered first, and the entry function:
Corresponding item**:
torow(choosecols(take(sort(filter(c2:e3000,a2:a3000=i2),3,-1),g2),1))
Corresponding item amount:
torow(choosecols(take(sort(filter(c2:e3000,a2:a3000=i2),3,-1),g2),3))
The effect is as follows:
In the above picture, you also need to merge the two columns into one cell and wrap the line automatically, and enter the formula:
let(a,take(sort(filter($c$2:$e$3000,$a$2:$a$3000=i2),3,-1),$g$2),torow(choosecols(a,1))&char(10)&torow(choosecols(a,3)))
And fill the formula downwards, you can get the following figure:
At the same time, test the top 5 return amounts, and change the number of the total return amount to 5 The effect is as follows:
The first n terms above still need to be filled in with the formula, so it is not "perfect", turn the above formula into a dynamic array:
ifna(drop(reduce("",i2#,lambda(x,y,vstack(x,let(a,take(sort(filter(c2:e3000,a2:a3000=y),3,-1),g2),torow(choosecols(a,1))&char(10)&torow(choosecols(a,3)))1),"")
This results in a dynamic display. The effect is as follows:
Source file: 234 dynamically displays the top n rankings of the return amount of different merchants. xlsx
I'm Brother Gu:
He has been engaged in the manufacturing industry for 20 years, and has rich practical experience in enterprise operation, chain management, and intelligent manufacturing systems. Expert in enterprise intelligence and flexible plan operation management, good at improving enterprise efficiency through enterprise process optimization and standardization, enterprise management, and introduction of plan operationHe has rich experience in improving the on-time delivery rate of enterprises, reducing enterprise inventory, and exporting intelligent manufacturing talents. Learn Xi PMC production plan and pay attention to the Guge plan!