Sorting and filtering with Excel functions

    Execl itself is very easy sorting and filtering capabilities, the drop-down "data" menu to select the sort or filter the data to sort or filter the list. But there are also inadequate, first of all regardless of sort or filter the list to change the original appearance of the original, especially the list of links to data from other worksheet changes to the source data, or list a new record entry must be from a new sort or filter. Second, there are limitations, such as sorting can only be a maximum of three keywords (three data) sorting, filtering, data available on the same column "and" or "or" conditions for screening, but can only be used for different columns of data "and" conditions of screening. Zhang Hua Mingce employees such as a workbook to require screening older than 25 years of age and less than 50 years of age or older than 50 years old or less than 25 years are feasible, such as male gender is also required or the woman is feasible. But requires selected woman aged from 22 to 45 years old, men aged 25 to 50 years old Execl the filtering itself is helpless. Furthermore, sorting and filtering can not be combined, that can not sort out under conditions selected to sort the records. For example, employees have a list of data, some of which employees have retired, the age for serving employees can not be removed to sort the data has been retired workers.
    This paper attempts to function with Execl to address the issue.
    First, sort by function implementation
    Subject
    If a payroll, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for the allowance, D1 for the prize, E1 wage, F1 total revenue. Income of workers are now required on the order from more to less, and in the total income workers the same wages from more to less then sorted, and wages in total income workers the same bonus from more to less then sort, in the total income of workers and employees salaries, bonuses, allowances and then the same ranking from more to less.
    Methods
    G1 cells enter the formula "= if (F2 = 0,10 ^ 100, INT (CONCATENATE (999-f2 ,999-e2 ,999-d2 ,999-c2 )))", CONCATENATE is a mosaic function can be 30 the following units of data together such a data, these data are put together with commas. With f2, e2, etc. The data were put together to reduce by 999, the same number of bits to make them. (Assuming any of the employees total income of less than 899 yuan). Been together such a function is a text function, CONCATENATE and apply the INT function is to make the text is converted to digital. If the outermost layer of the sort function is used to sort the records are not removed, middle income is zero in this case the record. (The age of workers in the above-mentioned order, the formula changed to "if (f2 =" retire ", 10 ^ 100, ... ..)", that the exclusion of retired workers.)
    The second step to drag the formula cell G1 to G500 cells (the most convenient way is to click the cell G1 G1 cells to the lower right corner after moving the mouse, double-click the mouse to see the black ten o'clock to finish filling G1 to G500 .)
    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. This formula is actually a synthesis of the three formulas a formula, ROW (A1) A1 is the number of rows is 1, with the drop down the order 2,3,4 ..., SMALL (G: G, ROW (A1 )) is the smallest number in the G column drop down the order as 2, 3, .. a small number, MATCH (SMALL (G: G, ROW (A1)), G: G, 0) is G column for the smallest of all the data rows, 2, 3 and small in the first few lines of data.
    The fourth step the A1 to the F1 cell header copied to I1 to N1 cells, in I2 cells enter the formula "= INDEX ($ A $ 2: $ F $ 501, $ H2, COLUMN (A $ 1))" INDEX function is a reference function, that is $ A $ 2: $ F $ 501 $ H2 cell array of the first row COLUMN (A $ 1) out of the data into the I2 cell. I2 cell and then drag and drop the formula N2 cell, click the N2 to the N2 cells after cell to see the bottom right of the black ten o'clock move the mouse double-click to complete the cell I2 to fill this N501 completed.
    The above description may seem complicated reality is very simple, as long as the A1 to the F1 copy of the table header cell I1 to N1, and then were in G1, H2, I2 cells enter the formula and drag down, even for unskilled EXCEL application Comrade Dr be finished within one minute.
    Changes in these procedures can be a little more expense. The above example data is arranged from largest to smallest, such as a function of the column in the H SMALL to LARGE, the above example data is ranked from small to large. Such as the H2 cell formula 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 the drag and drop. This cell type 1 in O1 the example above the data is ordered from largest to smallest, O1 cells enter a number other than the example above, data on the arrangement of the small to large.
    If the H column, insert several columns, such as insert a column, similar to the current G H column input column formulas, such as "= if (F2 = 0,10 ^ 100, d2)", now I changed the formula column "= IF (P1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (SMALL (H: H, ROW (A1)), H: H, 0)) ) "is 1 in P cell input to achieve a value other than by order of prize money. so long as by changing the P1 (O1 original cell) can change the cell contents immediately sorted according to different requirements.

    Second, with the screening function implementation
    Subject
    If an employee roster table, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for gender, D1 for the ages, E1 for the education, F1 titles. Workers are now required on gender, age, education, job title interleaving filter, such as requiring a table in the same selected 1 woman aged from 22 to 45 years old, men aged 25 to 50 years of age, 2, female Dr, 3, male post-doctoral.
    Methods
    The first step in the G2 cells enter the formula "= IF (OR (AND (C2 =" Female ", D2> = 22, D2 <= 45), AND (C2 =" M "
D2> = 25, D2 <= 50)), ROW (A1), 0) ", in the H2 cell enter the formula" = IF (AND (C2 = "Female", E2 = "Doctor"), ROW (B1) , 0) ", enter the formula in cell I2" = IF (AND (C2 = "M", E2 = "doctoral"), ROW (B1), 0) ". enter the formula in cell J2" = 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 drop down to the above-mentioned methods. G, H, I listed the meaning of the formula is where the records match the filter conditions down the line number zero otherwise, J out of the equation meaning based on the value selected K2 G, H, I sort a column in the rows and remove the substandard conditions.
    The second step in the K1 cells lose the text "filter selection", A1 to the F1 table copied to the L1 to the first Q1, cell entry in the L2
The formula "= IF ($ J2 = 0,0, INDEX ($ A $ 2: $ F $ 501, $ J2, COLUMN (A $ 1 )))", then right onto Q2, and then drop down. INDEX function has explained the meaning above.
    The third step in the P1 cell type 1 or 2 or 3 can achieve these three screening.