Shulou(Shulou.com)11/24 Report--

Can't sort with Excel? Then you may miss hundreds of millions of opportunities to get off work at one o'clock!

The boss assigned the same task:

Organize the salaries of all employees in the company, sorted by department, from high to low.

People who don't know how to sort:

Find the department manually and copy and paste one by one.

It takes about 30 seconds to find one. We have a total of 200 people, 6000 seconds, and it only takes two hours to finish it.

Put it together and click [descending]!

Oh, the order is out of order again.

People who can use sorting:

Click the mouse, knock on the keyboard, 5 seconds to finish!

Want to know how it was done?

Look at the following!

01, sort by color if the color of each department is the same, it can be sorted directly by color.

The steps are also simple:

Select the A column where the department is located, and click Ascending in the data tab.

Got it!

Or press [Ctrl + L] to convert the table to a smart table.

Select [sort by Color] directly in the department column, and select "Cell Color" or "Font Color".

02, multi-condition sort that, in the case of sorting by department, add another condition: sort by salary from high to low.

how to deal with it?

It doesn't matter ~ Xiao E will help you!

In the case of multiple conditions, we only need to disassemble the conditions first, and then sort them with multiple conditions.

The specific operations are as follows:

❶ select the F column of "Total basic salary" and click "sort" in the data tab.

Click "add criteria" on the ❷ "sort" page, add a "secondary keyword", and select "Total basic salary" in the "column".

The main and minor differences are:

The higher the level of keywords, the higher the priority.

In other words, the table will be sorted first by department, and then by total base salary.

❸ changes the "order" column in the secondary keyword to [descending] to determine.

So it's done.

03. Custom sorting this table needs to be sorted according to the size of the position.

Only name and position.

In this case where no rules can be found, how should we rank them?

Don't panic! We also have the ultimate trick-custom sorting!

❶ select the B column where the position is located, and click "sort" in the data tab.

On the ❷ "sort" page, select [position] for "main keywords" and [Custom sequence] for the "order" column.

❸ enters the desired sequence into the "input sequence" box and separates the entries with the enter key.

For example, here we enter the positions of general manager, minister, manager and so on.

❹ click [add], confirm twice, done!

04, other sorting in addition to the above commonly used sorting methods, in the "sorting" page [options], there are a variety of special sorting to meet your needs ~

▋ is sorted alphabetically

▋ sorts by Stroke

▋ horizontal sorting

You think it's over?

It's no use talking, let's have an actual fight--

05. The most common use of sorting payroll is to make payroll.

Let's look at the operation directly.

❶ adds a sequence next to the table.

❷ adds another sequence backwards.

Select the cell where the last number is located, and select [fill]-[sequence] in the start tab.

On the sequence page:

"sequence generated in" column selection [column]

Type column Select [congruent column]

Fill in the "step value" [- 1]

Fill in the "termination value" [1].

I'm sure!

❸ copy the header, use the [Shift] key to select the lower half of the entire area, paste!

The last step of ❹, our ranking is shining!

Select the serial number area and click Ascending in the data tab.

It'll be done in a second!

All right, that's all for today's "dry" to thirsty Excel sorting class!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Yuange, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.