How to sort data correctly in Excel

Sort problem in Excel

It is inconvenient to use custom sequences for this kind of data, because the numbers attached to the "text" will change at any time.

It is recommended to use the formula to get the sorting results:

For example, the original data is in A1:A6, and you enter the formula in the blank column:

=＂Text＂&SMALL(--TRIM(MID($A$1:$A$6,3,10)),ROW())

Just copy the formula down to the corresponding row.

If there are other columns of data that need to be sorted together, the VLOOKUP function can be used to solve the problem.

Supplementary answer:

According to your situation, you can set the cell format of the column where the cell that needs to be used as the sort key to the corresponding "custom" type can be sorted normally.

Set the cell format of the data in columns c1, c100, c250, c2, c3, c4 as:

"c"0

Set the cell format of the data in the best1, best5, best6, best11, best100 columns as:

"best"0

In this way, you only need to enter the following numbers when inputting.

And this column can be sorted according to your needs.

How to sort data in excel

There are two types of data sorting in excel: data rearrangement and ordinal calculation (similar to rankings in performance statistics).

1. Data rearrangement

1. General sorting (click the "Sort" command under the Excel "Data" menu)

For example, to sort the student roster by the strokes of "name", you can use the following method:

Select the first cell of the column (or row) where the sort keyword is located, click the "Sort" command under the Excel "Data" menu, and then click the "Options" button. Select "Stroke Sort" under "Method" in the "Sort Options" dialog box, then select "Sort by Row" or "Sort by Column" according to the data arrangement direction, and click "OK" to return to the "Sort" dialog box. If your data has a header row, you should select "Have header row" (otherwise, leave it unchecked), then open the "Main keyword" drop-down list, select "Name", and select the sorting method ("Ascending" or "Descending" ") and click "OK", all the data in the table will be rearranged accordingly.

2. Custom sorting

If you want Excel to rearrange the worksheet data in a specific order of "Principal", "Vice Principal", "Director" and "Teacher", this type of problem can be solved by defining sorting rules:

First click the "Options" command under the Excel "Tools" menu to open the "Custom Sequence" tab in the "Options" dialog box. Select "New Sequence" under "Custom Sequence" on the left, the cursor will flash in the "Input Sequence" box on the right, and you can enter "Principal", "Vice Principal", "Director" and "Teacher" to customize After defining the sequence, each input sequence must be separated by commas, or press Enter after each input sequence. If the sequence already exists in the worksheet, you can select the cell range where the sequence is located and click "Import", and these sequences will be automatically added to the "Input sequence" box. Regardless of the above method, click the "Add" button to put the sequence into "Custom Sequence" for later use.

Open the "Custom Sort Order" drop-down list in the "Sort Options" dialog box, select the sorting rule defined previously, and leave other options unchanged. After returning to the "Sort" dialog box, select "Ascending" or "Descending" as needed, and click "OK" to complete the custom sorting of the data.

3. Numerical sorting

1.RANK function

RANK function is the main tool for Excel to calculate ordinal numbers. Its syntax is: RANK (number, ref, order), where number is the number participating in the calculation or the cell containing the number, and ref is the number cell participating in the calculation. An absolute reference to the range, order is a number used to describe the sorting method (if order is zero or omitted, the results are given in descending order, otherwise in ascending order).

For example, cells E2, E3, and E4 store students' total scores. The method to calculate the students' total score ranking is: Enter the formula "=RANK(E2,$E$2:$E$4)" in cell F2, and Drop down fill formula. If the formula entered in the cell is "=RANK(E2,$E$2:$E$4,1)", the calculated ordinal numbers are arranged in ascending order.

2.COUNTIF function

The COUNTIF function can count the number of cells that meet the conditions in a certain range. Its syntax is COUNTIF

(range,criteria). Among them, range is the cell area participating in statistics, and criteria is expressed in numbers and

Conditions defined in formula or text form. Numbers can be written directly, expressions and text must be quoted.

For example, the formula entered in the cell is =COUNTIF($E$2:$E$4, ">＂&E2) 1

and drop down to fill in the formula, the calculation result is exactly the same as the above method.

C language structure sorting problem

Since you are just exchanging strings, please modify it as follows. Please check.

Use strcpy(s1,s2) to copy the string, you cannot directly s1=s2.

#include

#include

typedef struct

{

char number[10];//book number

char name[50]; //Book title

char author[20];//Author

char publish[50]; //Publishing house

char time[20]; //Publishing time

float price；//unit price

}mbook;

typedef struct

{

int count; //Number of books

mbook book[10];//Maximum number of books

}mlibrary;

mlibrary library,t;

main()

{ void sort_by_price(void);

sort_by_price();

}

void sort_by_price(void) /*Define sorting function by price*/

{

int i,j,k;

char sn[10];

for(i=0;i{

k=i;

for(j=i 1;jif(library.book[j].pricek=j;

if(k!=i)

{

strcpy(sn,library.book[k].number);

strcpy(library.book[k].number,library.book[i].number);

strcpy(library.book[i].number,sn);

}

}

}

The above is the detailed content of How to sort data correctly in Excel. For more information, please follow other related articles on the PHP Chinese website!