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

The original title: "awesome, these four Excel serial number skills, 80% of people do not know!" "

Hello, everyone, I am a farmer, a farmer who specializes in difficult and complicated numbers.

In our daily work, we often encounter all kinds of problems of numbering the data.

For example:

Ranking number of year-end performance

The ranking number of the competitive score

The ranking number of the class score

Sequence number of personnel in different departments

The sorting number of different employees in the same department

.

In the face of different sorting requirements, some are very simple to deal with, some will give some partners a little headache to deal with, and some will be done in a very troublesome way.

So, in the face of these various serial number requirements, how should we use Excel for more efficient processing?

Today, I'm going to sort out the things about Excel marking serial numbers for you.

How to ensure that after deleting, filtering and hiding, the serial number is automatically continuously labeled?

How is it sorted by the number of specific repeats?

How to sort by fixed serial number?

How do I mark the sequence number from 1 within different classes?

.

Fill in a sequence number with a special symbol have you ever encountered a situation where you want to use numbers with circles or parentheses as data sequence numbers?

When some friends encounter such a situation, they will input them one by one using the symbol menu in the input method.

In fact, there is no need to bother at all. The UNICHAR function in Excel can output this special symbol.

The UNICHAR function translates the specific characters represented by Unicode coding into symbols that people can understand, such as numbers with circles or numbers with parentheses.

Fill in the serial number with a circle, the specific formula is as follows:

= UNICHAR ((ROW (A2)-1) + 9311), where 9312-9331 represents 1-20 "circled serial number" in the Unicode code number ~

Fill in the parenthesized serial number. The specific formula is as follows:

= UNICHAR (ROW (A2)-1) + 9331) where 9332-9341 represents 1-20 "parenthesized serial numbers" in Unicode code numbers.

Note: signed serial number filling can only be entered to 20.

The serial number after deleting a line is still continuous compared with the serial number with a special symbol, it is more common to delete several lines after the serial number is marked, which will result in a broken number.

Some partners will once again re-mark the serial number of all lines. In fact, you can easily solve this problem with only the ROW function.

The result of the ROW function returns the location of the row in the cell, as shown in the following formula:

= ROW (B2)-1 where ROW (B2) returns 2, that is, in the A2 cell in the second row. If you want to start labeling from 1, you need to subtract a few lines before the row of the serial number cell, minus 1 here.

Of course, you can also convert the data region to a super table, so that after you fill the first cell, you can automatically fill the entire region.

When the sequence number after filtering or hiding is still continuous, we often filter the data or hide specific rows.

However, we will find that all the sequence numbers of the filtered or hidden data are discontiguous, so how can we make the filtered or hidden data continuously labeled?

Here we need to use the SUBTOTAL function ~

The result of the SUBTOTAL function returns a list of data or a summary of the database.

Translated into popular language is: classified statistical calculation is carried out in a given area according to the requirements of the selected functional code.

The specific formula is as follows:

= SUBTOTAL (function code, data area, [data area],...)

Among them, if the number in [function Code 1] is used, the result of statistical calculation will contain the hidden data.

If the number in [function Code 2] is used, the hidden data will be ignored. Take the summation of the data as an example:

Here, we want to achieve the need for continuous labeling of filtered or hidden data.

So we use the non-empty cell counting function COUNTA, which corresponds to 103 in [function code 2].

The specific formula is as follows:

= SUBTOTAL, where absolute references (letters and numbers are preceded by the $symbol) are used to delineate the data region with H2 as the starting point, that is, the cumulative number of non-empty cells from H2 to the current cell.

In this way, serial numbers will be continuously labeled as the data is filtered and hidden.

In the work of filling the sequence numbers of indefinite empty cells, we will also encounter the situation of filling in the sequence numbers of a variable number of empty cells between the sequence numbers.

First, in the type column, use the filter function to filter out the non-empty cells, which corresponds to the cells filled with ordinal numbers in the ordinal column.

In the serial number column, fill all the visible cells with 1, or only the first cell with 1, then use the [location] function to select [visible cells] in the serial number column, and cancel the [filter] function ~

Secondly, click the "sequence" function in the "start" tab-"fill" function, and fill in the step value of 1, and the termination value is the maximum sequence number to be marked or a value greater than the maximum sequence number.

Note:

❶ cannot drop down and fill in the serial number here.

❷ cannot use the sequence feature without unfiltering.

This is the end of the knowledge review about the serial number operation, can you understand it? Let's review again:

❶ signed serial number: UNICHAR function.

The serial number with a circle-9312 to 9332.

Parenthesized serial numbers-9332 to 9342.

❷ automatically sequential labeling after deleting the serial number: ROW function.

The sequence number after ❸ filtering or hiding is still continuous: the SUBTOTAL function.

❹ interval indefinite empty cell ordinal filling: [filter]-[sequence].

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