【 Weak current College 】 Excel spreadsheet sort method

The sort is data processing routine work, Excel sorts have a serial number calculated (similar scores in ranking) and data rearrangement in two categories. This article to several workshops of production value and name, for example, the introduction of Excel2000/XP data sorting method.

I. numerical sorting

1, the rank function

The rank function is the ordinal number of the main Excel calculation tools, it's syntax is: RANK (number, ref, order), which number is involved in the calculation of figures or the cells that contain numbers, ref is involved in the calculation of the number of cells of an absolute reference, the order is used to indicate the sort order number (if the order is zero or omitted, the method given in descending order, on the other hand the results in ascending order).

For example, to calculate the E2, E3, E4 cell holds a quarter of GDP, the workshop production in the ranking calculation is: F2 cell, enter the formula "= RANK (E2, $ E $ 2: $ E $ 4)", press ENTER to calculate the total ranking Foundry is 2. Then F2 formula is copied to the Clipboard, select the cell F3, F4 or CTRL + V, will be able to figure out the remaining two workshop production in the rankings for the 3 and 1. Wen fang remind you if B1 cell formula "= RANK (E2, $ E $ 2: $ E $ 4, 1)", then the calculated numbers are arranged in ascending order, that is, 2, 1 and 3. Note: the same as the value calculated with the RANK function ordinal (rank), but can cause subsequent ordinal numbers. If the example above F2 cell storing values and F3 are the same, according to this law the calculated rank are 3, 3, and 1 (descending).

2, the COUNTIF function

COUNTIF function can count an area eligible for the number of cells, it's syntax is COUNTIF (range, criteria). One range for participation in the range of statistics, the criteria is a number, expression, or text form the defined criteria. You can write directly to these figures, the expression and the text must be quoted.

Using the previous example, F2 cell, enter the formula "= COUNTIF ($ E $ 2: $ E $ 4," > "& E2) + 1". Calculate the value of the method for ranking workshop ibid., the result is exactly the same, 2, 1 and 3.

This formula of calculation process is this: first by E2-cell value, the role of the connector & creates a logical expression, i.e. "> 176. 7”、“>167。 3 ", etc. The COUNTIF function to calculate the reference zone qualifying number of cells, the result can be one of the values of rank. Obviously, using the above method get is descending ranking on duplicate data calculated results with the same RANK.

3, the IF function

Excel itself with sorting, you can make the data in descending or ascending way to rearrange. If it is combined with the IF function, you can calculate the no vacancy rankings. The above example, E2, E3, E4 output sorting of a cell, for example by selecting the cell E2, depending on the sort, click the Excel toolbar "descending" or "ascending order" button to make all the data on the worksheet as required to reorder.

If the data is output from largest to smallest (descending), and you want to give each workshop from 1 to n (n-natural number) rankings. You can type 1 in cell G2 and G3 cell formula "= IF (E2, E3 = G3, G3 + 1)", as long as you copy the formula to cells G4, etc, you can calculate the other workshop production in the rankings.

Second, text sorting

Election, etc need to be in alphabetical order for the text, Excel provides a better solution. If you want to use the example table as the name of the stroke sort workshop, you can use the following method: select the sort key column (or row) of the first cell, click Excel "data" menu under the "sort" command, and then click the "options" button. Select "options" dialog box to sort the "method" sort of "stroke," and then under data flow direction select "sort by rows or by columns sort," and "OK" toreturn to the "sort" in the dialog box. If your data has headings rows, you should select the "title" (but not selected), and then open the "sort by" drop-down list, select the "units", select the sort order ("ascending" or "descending") after the "OK", all data in the table will be rearranged accordingly. Printer maintenance (http://www.dyj123.com) remind you this method a little workaround available for "first name" and "second" text, readers to explore.

Third, a custom sort

If you require a machine shop in accordance with the "Excel" and "the casting workshop" and the "repair shop" in a specific order for rearranging the worksheet data, as described in the previous several ways you can do about it. This type of problem can be used to define the collation of the solution: first click the "Tools" menu of Excel under "options" command, open the "options" dialog box in the "custom list" tab. Select the left "custom list" under "new sequence", the cursor is in the right sequence of "input" box flashing, you can enter "machine shop and Foundry" and other custom lists, input of each sequence to use English comma separated, or enter a sequence on each press ENTER. If the list already exists on the worksheet, you can select the sequence of the cell area"Import", these sequences will automatically join the "input sequence" box. Regardless of which method above, click "add" button will sequence into the "custom list" in the reserve.

Use collation sorting of specific methods and stroke sort is similar, but you want to open the "sort option" dialog box in the "custom sort order" in the drop-down list, select the collation defined earlier, other options to remain intact. Return to the "sort" in the dialog select "ascending" or "descending", "OK" to complete the data to the custom sort.

It should be noted: the display in the "custom list" tab of the sequence (such as 1, 2, 3, etc.) are available on the above method of participation, the readers attention to Excel provides custom list types.