Is the problem difficult , In addition to knowledge reserves , And the angle of view ！

Let's look at the first “ problem ”, Sports test , Each person tested several times , Now it's up to everyone to test in order , Add number , For example, Zhang San , The first is 1, The second time is 2, By analogy ！

** problem 1 | Number... In the order in which they appear **

`=COUNTIF($A$2:A2,A2)`

**Knowledge point ( I've written a special topic ）**：

1、COUNTIF Use of functions ： function | COUNTIF A complete collection of usage

2、 Cell references ： Basics | Excel How to refer to cells in , Reading this is enough ！

This method also applies to ,

**Duplicate name detection**, As long as it is greater than 1 It's a duplicate name .

For example, we only need the first time or the second time , Can be directly screened , Very convenient ！

For example, we need to find the second grade , Direct screening 2 that will do ！

If this case is a novice , There may be

*Screen by name*, And then number them one by one ！ If the data is large , It's a lot of work ！

In this case , We continue , Find out the highest average of each person's three grades , If less than three times, take the average directly ！

** problem 2 | The highest average of three grades **

**First step ： Sort by name and grade**

Be careful

**The grades are in descending order**Sort , From big to small ！

**The second step ： Use the formula AVERAGEIFS**

`=ROUND(AVERAGEIFS(C:C,B:B,"<=3",A:A,E2),)`

If you don't think it's cool enough , You can use the formula

**▼ I'm an array formula , Please use Ctrl+Shift+Enter Enter me**

=ROUND(AVERAGE(LARGE(IF($A$2:$A$15=$E2,$C$2:$C$15),ROW(INDIRECT("1:"&MIN(3,COUNTIF(A:A,E2)))))),)

There are cases of less than three tests ！ Take the average according to the actual number of times ！ use 3 And the number of tests can be reduced

Of course , If you're a data analyst , understand

**Power Pivot**, You can also use what you think is cooler 、 More appropriate DAX To deal with it

**▼ The highest three score average measure**

=IF(HASONEVALUE(' surface 1'[ full name ]),ROUND(CALCULATE(AVERAGEX(TOPN(3,' surface 1',' surface 1'[ Test scores ],DESC),' surface 1'[ Test scores ])),0),BLANK())

Xiaobian is a DAX beginner ！ If there's something wrong with it , Welcome to correct ！

We've been sharing Power Query, Why don't we write about it ！

**▼Power Query Medium M Functions are based on simplicity**

= Table.Group( Type of change ," full name ",{" Average of the highest three scores ",each Number.Round(List.Average(List.MaxN([ Test scores ],3)),0)})

List.MaxN It's very simple to deal with this kind of problem ！M There's a lot of List function , They greatly enrich the extraction of data 、 Organize and aggregate ！

** problem 3 | How many people tested ？**

Everyone has multiple tests , So it should be right

**Name de duplication**！

The simplest , Nature is a PivotTable , Just drag it to get it done ！

You still feel , Not cool enough , You have to use a function to handle it ！

=SUMPRODUCT(1/COUNTIF($A$2:$A$15,$A$2:$A$15))

Formula interpretation ：COUNTIF Decipher the double count

Of course, come back , What's the best way ？ What's the easiest ！ It's the best to fit yourself ! Everyone has different knowledge and direction , Ordinary office workers who are new to the workplace , The first one is recommended , Can solve problems , It's more important than anything fancy ！