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

The original title: "can the Large function still be played like this?" This statistical function, which 90% of people don't know, is awesome! "

Hello, everyone, Hi~. I am Tian Tian, who comes to share practical information about Excel to you every week.

It's time for the [0 basic Excel function] column that starts on time.

Today we learn a very useful statistical function-LARGE function.

1. The main function of the LARGE function is to find the specified ranking value in an array according to the size of the value.

To make good use of it, you only need to remember two tips:

Where can I find it?

What is the ranking of size?

= LARGE (array or numeric range, maximum value)

2. The designated ranking case is a score table that records the test results of 70 examinees. If you want to find out what the tenth test score is, you can use the LARGE function.

❶ enters the = LARGE function in the [G2] cell on the right, with parentheses "(".

Where to find the first ❷ formula? select the entire score area [A2:E15] with the mouse and enter a comma in English.

What is the ranking of size-we are looking for a test score that ranks 10th, so for the second parameter, enter the number 10, put anti-parentheses "), press enter, and the result will come out.

The complete formula is as follows:

3. The array is arranged before the operation. Let's learn an auxiliary function, ROW function, which returns the line number of the specified cell.

For example, enter the = ROW function in the [G2] cell and specify the [B5] cell in parentheses, then the formula returns the line number of the [B5] cell, that is, the fifth line.

If no arguments are filled in in the ROW function, the line number of the current formula cell is returned. For example, deleting "B5" in parentheses displays the current cell in the second row of the table.

OK, after learning the ROW function, we can sort the test results.

❶ input = ROW function in G2 cell, in parentheses we directly refer to [A1] cell, calculate that the line number of [A1] cell is 1, fill down the formula, we get a set of sequences. We take this set of sequences as the second argument of the LARGE function.

❷ enter the LARGE function after the "=" sign, the first parameter we select the entire grade area [A1:E15] cell, press [F4] to lock, and then put a "," comma.

❸ just took the column of the ROW function as the second argument, and then put the anti-parenthesis ")" directly, press enter, and fill the formula down. In this way, we have sorted the scores in order of size.

The complete formula is as follows:

4. In addition to calculating rankings, LARGE can also find rankings from scores, as well as skills such as calculating total scores and average scores.

❶ input = LARGE function, the first parameter we specify as the score area [A2:E15], press [F4] to lock, with a comma.

❷ packages the ROW function as a whole as the second parameter of the LARGE function, with anti-parentheses and pressing enter to find out the top three test scores of all scores.

The complete formula is as follows:

= LARGE ($Agg2VERVONESTOW 15 A1:A3 row) ❸ then, we package the entire function formula again, take it as a whole, and sum it.

Enter the SUM function after the equal sign, enclose everything that follows in parentheses, and press enter. In this way, we calculate the total score of the top three.

The complete formula is as follows:

= SUM (LARGE ($LARGE ($Aq2VERVOLING 15th row (A1:A3) if you change the SUM function to the AVERAGE function, you can also calculate the average score of the top three.

The complete formula is as follows:

= AVERAGE (LARGE ($Abig2VOGRW (A1:A3), have you learned all the tricks of brain play?

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

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.