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

The original title: "the seemingly inconspicuous ROW function unexpectedly has these 7 hidden efficient tips!" "

I am La Xiaodeng, a sand sculpture high-energy teacher who can design forms.

There are a lot of obscure tricks in the function formula.

For example, the ROW function we are going to talk about today is very simple: it returns the line number of the current cell.

What's the point of saying this line number? It doesn't make any sense.

But this humble ROW function, combined with different functions, can achieve the effect of ants moving elephants.

1, ROW introduction usage ▋ auto-fill serial number since the return line number, then the most basic use is to automatically return the line number.

In the 1st cell, enter the ROW function, and then fill the formula down to quickly fill in the sequence number.

▋ automatically updates the sequence number because the formula is calculated automatically in real time, so after we delete the row, the sequence number will be updated in real time to become the line number of the current cell to fill in the sequence number automatically.

▋ fill in the sequence number anywhere. If your sequence number does not start with the A1 cell, you can reference the A1 cell in the ROW function.

The sequence number returned in this way is the line number of cell A1, which has nothing to do with the location of the formula.

2. ROW combinational use ROW function is very simple, right? But don't fool yourself into thinking that the Excel function formula is no more than that.

We said before: the essence of the function formula = function + condition.

On the basis of the ROW function, add some conditions, difficulty and usage scenarios will be shown immediately.

▋ condition 1: interlace filling sequence number such as line number divided by 2, and then interlacing filling formula, you can produce interlaced filling sequence number.

The formula is as follows:

= ROW (A2) / 2 if you want to fill in 1, 3, 5, 7, etc., the formula will look like this.

The formula is as follows:

The * 2 in the formula = 1 + (ROW (A1)-1) * 2 represents the step size of equidifference. If it is changed to 5, the sequence numbers of 1, 6, 11, 16 will be generated by 5 digits.

It is useless for ▋ conditional 2:INDEX+ROW to extract data only to generate sequence numbers. combined with other functions, the usage scenario opens at once.

One of the most commonly used is the INDEX function.

In the table below, if you want to divide your name and phone number into two separate columns, you can do it quickly with INDEX+ROW.

The formula is as follows:

The ROW function generates a sequence number with a 2-digit difference, and then the INDEX function extracts the corresponding data according to the sequence number. = INDEX ($Bamboo 2ROW (A1 (A1)-1) * 2) function generates a 2-digit sequence number.

The function of the ▋ conditional 3:LARGE+ROW data sorting LARGE function is to find the nth largest data in the data.

For example, the following formula returns the second largest number.

The formula is as follows:

= LARGE (B2LARGE B6) if the "Nth" is implemented with the ROW function, the data can be sorted quickly with the LARGE function.

The formula is as follows:

= LARGE ($ROW (A1)) because the sequence number of 1234 is automatically generated if you fill it downwards, then combined with the LARGE function, the data of 1234 names is automatically extracted, and the sorting effect is achieved.

The function of the ▋ conditional 4:MID+ROW split text MID function is to extract text of a specified length from a specified location of the text.

For example, the following formula is used to extract the beginning of the third character and the next five characters, namely: Excel.

If you change the beginning to the ROW function and extract the length to 1, you can quickly split a whole paragraph of text into a single text.

The formula is as follows:

= MID ($Agg2jour row (A1), 1) what's the use of doing this? Imagine that you must have encountered that the data is clearly there, but when VLOOKUP returns NA, this is because there are invisible characters in the cell.

So at this time, using the MID+ROW function, you can split each character so that the missing character is instantly prototyped.

3. Summarize the simple function formula, that is, the use of a single function.

Complex function formulas are essentially very similar to computer programming, except that multiple function formulas need to be nested to complete complex requirements.

But how to disassemble a complex problem into small steps and turn it into a function and code that can be executed is the same in principle.

Secretly tell you, change ROW into COLUMN, the above usage can be universal, specific magic effect, quickly try it yourself!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

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.