Three practical ways to sort Excel tables

Steps/Methods

One, numerical sorting

1, Rank function

The rank function is the primary tool for Excel to compute ordinal numbers, and its syntax is: RANK (Number,ref,order), where number is the numeric or cell containing numbers, and ref is an absolute reference to the range of numeric cells that participate in the calculation. An order is a number that describes how the sort is sorted (if the order is zero or omitted, the result is given in descending order, in ascending order).

For example, to calculate E2, E3, E4 cells for the first quarter of the total output value, the calculation of the number of workshop output ranking method is: In the F2 cell input formula "=rank (E2, $E $: $E $)", hit the return can calculate the casting shop output value is ranked 2. Then copy the formula in F2 to the Clipboard, select F3, F4 Cell Press CTRL + V to calculate the output value of the remaining two workshops is 3 and 1. The American Gallery reminds you if the formula entered in cell B1 is "=rank (E2, $E $: $E $4,1)", the computed ordinals are sorted in ascending order, that is, 2, 1, and 3. Note that the same number is computed using the rank function for the same ordinal number (rank), but will result in ordinal vacancies for subsequent digits. If the F2 cell in the previous example holds the same value as the F3, the rankings calculated by this method are 3, 3, and 1 (in descending order).

2. countif function

The COUNTIF function can count the number of eligible cells in a range, and its syntax is COUNTIF (Range,criteria). Where range is the range of cells participating in the statistic, criteria is defined as a number, expression, or text form. Where numbers can be written directly, expressions and text must be enclosed in quotes.

Still take the example above, the formula entered in the F2 cell is "=countif ($E $: $E $," > "&e2) +1". The method of calculating the output value of each workshop is the same, and the result is identical, 2, 1 and 3.

The calculation of this formula is as follows: first, according to the value within the E2 cell, a logical expression, >176, is generated under the action of the Connector &. 7 "," >167. 3 "and so on. The COUNTIF function calculates the number of eligible cells within the reference area, and the result adds one to the rank of the number. Obviously, using the above method to get the ranking in descending order, the result of the calculation of the duplicate data is the same as the rank function.

3, if function

Excel has its own sorting function, which rearranges the data in descending or ascending order. If you combine it with the IF function, you can calculate a rank without a vacancy. In the above example, the output value of E2, E3, and E4 cells is sorted as an example, by selecting the E2 cell, and clicking the descending sort or ascending sort button on the Excel toolbar to rearrange all the data on the worksheet as required.

If the data is in the order of value from big to small (descending), and you want to give each workshop from 1 to N (n is the natural number) ranking. You can enter 1 in cell G2, and then enter the formula "=if (e3=e2,g3,g3+1)" in cell G3, so that you can calculate the output rank of other workshops by copying the formula to cells such as G4.

Second, text sorting

For elections, such as the use of the last name strokes for text sorting, Excel provides a better solution. If you want to sort the previous data table by the name of the workshop, you can use the following methods: Select the first cell in the column (or row) that contains the sort key, click Sort on the Excel Data menu, and then click the Options button. Select Sort by stroke under methods in the Sorting Options dialog box, and then select Sort by row or sort by column according to the direction of the data, and then go back to the Sort dialog box. If you have a header row for your data, you should select "Header row" (or vice versa). Then open the primary keyword drop-down list, select units, select the Sort method (ascending or descending), and then "OK" and all the data in the table will be rearranged accordingly. This method can be used for "first name", "second name" and other text sorting, please the reader to explore.

Three, custom sort

If you require Excel to rearrange worksheet data in a specific order in the metalworking shop, foundry, and repair shop, there are several ways to do this. This type of problem can be resolved by defining a collation: First, click Options on the Excel Tools menu, and open the Custom Series tab in the Options dialog box. Select New sequence under Custom series on the left. The cursor will flash in the "Input sequence" box on the right, and you can enter custom sequences such as "Metalworking Shop", "foundry", and so on, separating each sequence in English by commas, or by entering a sequence on each entry. If the sequence already exists on the worksheet, you can select the range of cells that contains the series click Import, and the sequence is automatically added to the input sequence box. Click the Add button to place the sequence in the custom sequence.

Sorting by using collations is similar to stroke sorting, except that you open the custom sort order Drop-down list in the Sort Options dialog box, select the collation you defined earlier, and leave the other options unchanged. After you go back to the Sort dialog box and select Ascending or descending as needed, you can then complete the custom sort of data.

Note that the sequences displayed on the Custom Series tab (such as one or two, three, and so on) can be sorted by the above methods, and readers should note the custom sequence types that Excel provides.