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

The original title: "double the efficiency!" Use Excel to sort according to custom order, and arrange it as you like. "

Hello, everyone. I'm studying the ranking of grades.

I have a friend who is a teacher. Now he needs to sort the grades of his students. The rule is:

First look at the total score, in the case of the same total score, give priority to math results, in the case of the same math scores, give priority to Chinese scores, the same language scores, look at English grades. And finally sort it.

In Excel, there is a custom sorting function that is easy to operate, which can accomplish the above requirements.

Custom sorting first we enter the formula in the E2 cell to sum:

= SUM ($B2:$D2)

Select the A1:E10 table data area, under the start tab, click sort and filter, and select Custom sort.

Add the corresponding conditions in turn, total score-cell value-descending order, select to determine, we can meet the requirements we want.

At this point, the sorting is complete.

Although the custom sorting is simple, it cannot be sorted in real time every time the corresponding data is modified.

What he wants is an automatic ranking of grades.

Is there a sorting function that can be done directly?

The two sorting functions Office365 actually produce two sorting functions, Sort and Sortby.

The Sort function can sort the specified index in ascending or descending order in the region.

= SORT (array, [sort_index], [sort_order], [by_col]) = SORT (array region, [sort index, default is 1], [sort method, default is ascending order], [sort by row or column]) ▲ swipe left and right. For example, we want the total score to be sorted from big to small.

Enter the formula in the G2 cell:

= SORT (A2VOR E10Magi 5memlle 1memFALSE) data area: A2:E10

The total score relative to the data area is in column 5

Sort by:-1 represents descending order

Sort as False by row.

However, the Sort function can only specify a sort for one index value, and if you want to specify a sort for multiple columns, we need to know another function-the Sortby function.

= SORTBY (array,by_array1, [sort_order1], [by_array2, sort_order2],) = SORTBY array region, column 1, [sort method 1], [column 2, sort method 2],) ▲ swipe around to view this example, we can use the Sortby function.

In order for the table to automatically expand the range, we first convert the data region into a super table.

Select the table area and hold down the shortcut key [Ctrl+T] to convert the table to a super table.

Enter the formula in the G2 cell:

= SORTBY (Table 2, Table 2 [Total score]-1, Table 2 [Mathematics],-1, Table 2 [language],-1, Table 2 [English],-1)

PS.-1 is sorted in descending order, and Table 2 is the name of the table.

A simple function formula is done!

Because our table is a super table, when we update the record, the corresponding sort table is also updated in real time.

The Sortby function is easy to use, but what if I use WPS.

Don't worry, WPS now updates the Sortby function as well.

Wow, that's great.

However, the difference between WPS and Office365 in using the Sortby function is:

Office365 uses a dynamic array, which can be expanded automatically by holding down the [Enter] key, while WPS needs to press and hold down the array triple key [Ctrl+Shift+Enter] to perform array operations after selecting the region and in the editing state.

The formula is as follows:

= SORTBY (A2Parade E10Magnum E2Relay E10jury1MagneC2VOLLYLI B2RAPHY B10LINGLING 1LINE D2RIMOLY D10LILE1)

For WPS operation, the dynamic picture is as follows:

PQ sorting if you are not an Office365 user, but there is a sorting function in PowerQuery,PQ in Excel, which is the Table.Sort function.

Select the data area, click the [data] tab-[from Table / region] (some versions do not have this name), and import the data into the PQ editor.

Select the total column, on the Home tab, select descending order, select math column, language column, and English column, select descending order.

Here, we can see that all the sorting operations correspond to a Table.Sort function.

Let's briefly look at the corresponding parameters of the Table.Sort function.

Sort by:

Ascending order: Order.Ascending/ 0

Descending order: Order.Descending/ 1.

Table.Sorttable as table, comparisonCriteria as any) as tableTable.Sort table, {{field 1g0px1}, {field 2j0px1}, {field 3j0px1}. } ▲ swipe left and right to view, so, in this case, the corresponding m function formula is:

= Table.Sort (changed type, {{"Total score", Order.Descending}, {"Mathematics", Order.Descending}, {"Chinese", Order.Descending}, {"English", Order.Descending}}) is this m function formula very similar to the previous Sortby function formula?

All are tables, and then the fields are sorted.

= SORTBY Table 2, Table 2 [Total score],-1 Table 2 [Mathematics],-1 Table 2 [language],-1 Table 2 [English],-1)

The sorting completed by PQ method can also be updated in real time.

To sum up, this article introduces three custom sorting practices:

❶ Custom sort Operation

❷ Sortby function

Office365

WPS requires the use of array triple keys

❸ Table.Sort function

Sorting function in PowerQuery

Parameters are very similar to Sortby functions.

The sorting function is particularly common at work.

But the only drawback is that it cannot update sorting in real time. We used to use VBA worksheet events to achieve real-time update sorting.

Happily, Office updated the Sortby function in August of the year before last, and WPS updated the Sortby function in October last year.

The split function is also very common, and the split function is also a problem that cannot be updated in real time, so when can Office and WPS update the Split function? Do you think there will be a Split function in the future?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: Xiaoyin, 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.