Sort the data processing routine work, Excel sort order is calculated (similar to the results of the rank statistics) and two types of data rearrangement. In this paper, the output value and the name of several workshops as an example, Excel 2000/XP data sorting method.
A numerical ranking
1, RANK function
Excel RANK function is the main tool for calculating the number sequence, its syntax is: RANK (number, ref, order), which number or numbers involved in calculating the number of cells containing, ref is the range of cells that take part in the absolute number of quote, order is used to illustrate the sort of number (if order is zero or omitted, Zeyi method gives results in descending order, otherwise ascending mode).
For example, to calculate E2, E3, E4 cells stored in the first quarter of the total output value, to calculate the plant output ranking is: in the F2 cell, enter the formula "= RANK (E2, $ E $ 2: $ E $ 4)", knock press Enter to calculate the output value of foundry ranking is 2. F2 in the formula then copied to the clipboard, select the F3, F4 cell press Ctrl + V, will be able to calculate the value of the remaining two workshops and a ranking of 3. Essay Square to remind you that if the cell B1 enter the formula "= RANK (E2, $ E $ 2: $ E $ 4,1)", then calculated the number sequence ordered by ascending manner, that is 2,1 and 3. Note that: the same value calculated by using RANK function ordinal (ranking), but it will lead to follow-up to the ordinal number of vacancies. If the above example the cell F2 and F3 values stored in the same position calculated according to this Law are 3,3 and 1 (when descending).
2, COUNTIF function
Statistics COUNTIF function can meet the requirements of a region in the cell number, and its syntax is COUNTIF (range, criteria). Which range the range of cells involved in statistics, criteria based on numbers, expressions or terms defined in text form. Number of them can write, expression, and text must be in quotes.
Still above example, F2 and enter the cell's formula "= COUNTIF ($ E $ 2: $ E $ 4 ,">"& E2) +1". Calculate the value of ranking methods workshop ibid., exactly the same results, 2,1 and 3.
The calculation formula is this: First of all, according to E2 within the cell values, in the connector under the action & create a logical expression, that is, "> 176.7", "> 167.3" and so on. COUNTIF function to calculate the reference area the number of eligible cells, the result plus one can get the value of the ranking. Obviously, using the above method is obtained by descending order of rank, on the results of duplicate data calculated with the same RANK function.
3, IF function
Excel itself with a sort, allowing data to rearrange in descending or ascending mode. If you combine it with the IF function can be calculated no vacancies in the rankings. These cases E2, E3, E4 output of the cell sorting, for example, specific methods are: select the E2 cell, according to sorting needs, click the Excel tool bar of the "descending order" or "ascending order" button work table can make all the data required to rearrange.
If the data is based on output from big to small (descending) order, and you want to give each workshop from 1 to n (n is natural number) in the rankings. G2 cells can enter 1, then enter the formula in cell G3 "= IF (E3 = E2, G3, G3 +1)", as long as the formula copied to cell G4, etc., we can calculate the other workshop output ranking.
Second, the text sort
Elections and so forth need to text in alphabetical order, Excel provides a better solution. If you want to shop on the case of table names by alphabetical order, you can use the following method: sort keywords selected column (or row) of the first cell, click the Excel "Data" menu under "Sort" command , and then click one of the "Options" button. Select the "Sort Options" dialog box "method" under the "stroke order" and then select the data arranged in the direction of "sorting by rows" or "sort by column", "OK" back to "sort" dialog box. If your data with a header row, you should select "a header row" (otherwise not vote), and then open the "main keywords" drop-down list, select one of the "unit", select the Sort ("ascending "or" descending ") after the" OK ", the table of all the data will be accordingly readjusted. This method can be used a little work, "first", "second" and other text sorting, the reader to explore on their own.
Third, custom sorting
If you ask Excel in accordance with the "machine shop", "casting workshop" and "repair shop" in a specific order rearrangement worksheet data, several methods described above can not do anything. Such problems can be defined collation methods to solve: First, click the Excel "Tools" menu under "Options" command, open the "Options" dialog box "Custom Series" tab. Select the left of the "Custom Series" under the "new sequence", the cursor will be in the right "input sequence" box flashing, you can type "machine shop", "foundry" and other custom sequence, and input each sequence separated by a comma between the use of English, or enter a sequence on each hit Enter. If the sequence already exists in the worksheet, you can select the sequence where the range of cells click the "Import", the sequence will be automatically added to the "Enter Codes" box. Regardless of which method above, click the "Add" button to sequence into the "Custom Series" in the reserve.
Collation order to use the specific methods and alphabetical order is very similar to, but you want to open the "Sort Options" dialog box "custom sort order" drop-down list, select the previous definition of collation, the other option is to remain intact . Back to the "Sort" dialog box be selected "ascending" or "Descending", "OK" to complete the custom data sorting.
Be stated that: appear in the "Custom Series" tab in the sequence (as one, two, three), may participate in the above method according to sequencing, please note that Excel provides readers a custom sequence type.