Sorting and filtering function are  very convenient features in Execl itself, but there are also inadequate, regardless of ranking or have changed the original screening of the original list, in particular a list of other data from the worksheet and links to source data changes, or a list of The new entry must be a new record of the sort or filter. Second, there are limitations, such as the sequencing of only up to three keywords to sort, but with different data can only be used “and” conditions for screening.

For example, that certain employees of the roster workbook requires selected more than 25 years of age and less than 50 years of age or older than 50 years old or less than the 25-year-old is feasible, gender requirement at the same time as a man or woman is Feasible. But the screening requirements of a woman aged 22 years old to 45 years, men aged 25 to 50 at the age of Execl itself with the screening function can do nothing to help. In addition to sort and filter can not be used in conjunction, that is not in accordance with the conditions of time to sort out the selection of records to sort. For example, there is a list of information workers, including some retired workers, employees of the age when they can not sort out retired workers.

So here we are again using Excel functions to complete some of the advanced features of Excel.

I used to sort function

Title: If a payroll, A2: F501, a total of 6 500 cell line 3000. Table A1 for the first name of the code (1 to 500), B1 for the name, C1 for the allowance, D1 for the prize, E1 wage, F1 total revenue. Is called for workers to less revenue from the multi-sort, and the total income workers the same wages and then in decreasing order of the total income workers the same wages and bonuses and then in decreasing order of the total income workers and workers Wages, bonuses and then the same benefits in decreasing order.

Method: G1 cells fill in the formula

“= If (F2 = 0,10 ^ 100, INT (CONCATENATE (999-f2 ,999-e2 ,999-d2 ,999-c2 )))”,

CONCATENATE is a function together, can be less than 30 units of a synthesis of hard data, which was put together data between commas to separate. With f2, e2, and so was put together using data from 999 to cut in order to enable them to the same median. (Assuming any of the workers, the total income of less than 899 yuan). Was hard synthetic version is a function of function, CONCATENATE with the INT function is to be applied so that the text is converted to digital. If the outer function is used to sort out when they do not sort of record, in the case of middle income recorded zero. (In the above mentioned age workers sort, the formula be changed to “if (f2 =” retirement “, 10 ^ 100, … ..)”, out that the retired workers.)

The second step of the G1 cell formula to drag and drop cells G500 (The easiest way is to click the cell G1 after the lower right corner of the cell G1 to move the mouse, and 10:00 to see double-click the mouse on the completion of the G1 to the G500 Filling).

The third step in the H2 unit fill in the formula “= MATCH (SMALL (G: G, ROW (A1)), G: G, 0)” and the second step, like drag and drop to the H501 cells. The formula is in fact the synthesis of Formula 3 a formula, ROW (A1) is the A1 is the number of rows 1, followed by dragging and dropping down as 2,3,4 …, SMALL (G: G, ROW (A1 )) For the G out in the smallest number with the drag and drop down the order for 2, 3, .. a small number, MATCH (SMALL (G: G, ROW (A1)), G: G, 0) that G for the trip out of the data in the smallest, 2, 3, and other small data in the first few lines.

The fourth step to F1 cell A1 to the table for the first copy to the N1 cells to I1, I2 in the cell enter the formula “= INDEX ($ A $ 2: $ F $ 501, $ H2, COLUMN (A $ 1))” INDEX function Is a reference function, that is, the $ A $ 2: $ F $ 501 cell array first $ H2 Bank COLUMN (A $ 1) data I2 into the cell. I2 cell and then drag and drop the formula to cell N2, N2-click the cell after the N2 to the lower right corner of the cell and move the mouse to see the 10 o’clock Double-click the mouse on the completion of the I2 to the N501 cell filled with this completed.

Described above may seem complicated reality is very simple, as long as the A1 to the F1 table for the first copy to the N1 to I1 cells, in G1, H2, I2 cells enter the formula down and then drag and drop, even for unskilled application of EXCEL Comrade one will be able to be completed within Chung.

The above-mentioned procedures can also change a little more expense. Data above example is the smallest of the order, such as the H out of the function of SMALL changed LARGE, the above examples of data on the array from small to large. If the H2 cell formula be changed to “= IF (O1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (LARGE (G: G, ROW (A1)) , G: G, 0)) “and the H2 cell formula down drag-and-drop. O1 in such a cell above an example of input data is arranged in the smallest, O1 cells enter a number other than the above example of the data on the array from small to large.

If in insertion pre-set number of H out, such as an insert, is now out of the H similar import G out of the equation, such as “= if (F2 = 0,10 ^ 100, d2)”, now I read out the formula “= IF (P1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (SMALL (H: H, ROW (A1)), H: H, 0)) ) “In P cells enter a value other than to achieve a bonus according to the size of the sort. As long as this by changing the P1 (the original cell O1) cells can change the content of the immediate requirements of different sort.

Second, use the filtering function

Title: If a staff roster sheet, A2: F501, a total of 6 500 cell line 3000. Table A1 for the first name of the code (1 to 500), B1 for the name, C1 for sex, D1 for the ages, E1 for education, F1 title. Workers are called for the sex, age, education, staggered title for screening, such as requiring the same table on a selected 1, the woman at the age of 22 to 45 years old, men aged 25 to 50-year-old, 2, F Dr, 3, men and post-doctoral.

Methods: The first step in the G2 cells enter the formula “= IF (OR (AND (C2 =” M “, D2> = 22, D2 <= 45), AND (C2 =” M ”

D2> = 25, D2 <= 50)), ROW (A1), 0) “, in the H2 cell enter the formula” = IF (AND (C2 = “M”, E2 = “Dr.”)

ROW (B1), 0) “, in I2 cells enter the formula” = IF (AND (C2 = “M”, E2 = “post”), ROW (B1), 0) “. Import cell J2 in the formula” = IF (K $ 2 = 1, LARGE (G: G, ROW (A1)), IF (K $ 2 = 2, LARGE (H: H, ROW (A1)),

IF (K $ 2 = 3, LARGE (I: I, ROW (A1)), 0))) “and then use the above mentioned methods of dragging and dropping down. G, H, I listed the formula where the meaning is in line with the screening The trip down the conditions, otherwise the record is zero, J formula for meaning out of K2 in accordance with the choice of values G, H, I in a sort out and remove the line conditions.

The second step in the K1 cells lose words “choice selection”, A1 to the F1 table for the first copy to the L1 to Q1, in L2 cells enter

The formula “= IF ($ J2 = 0,0, INDEX ($ A $ 2: $ F $ 501, $ J2, COLUMN (A $ 1 )))”, then drag and drop right into Q2, and then drag and drop down. INDEX function of the meaning of the above description.

The third step in the P1 cell input 1 or 2 or 3 will be able to achieve the above-mentioned three types of screening.

 

Excel function of the function is a very powerful feature, if the use of the good you can let the efficiency of the highly effective. There are other ways to use the function, everyone is concerned about the follow-up article.