How can I easily solve several difficult problems in excel
Two leisure places 2020-11-27 04:48:35

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)



Excel Some of the problems in , How I did it easily


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 !


Excel Some of the problems in , How I did it easily


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 !


Excel Some of the problems in , How I did it easily


The second step : Use the formula AVERAGEIFS


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

Excel Some of the problems in , How I did it easily



Excel Some of the problems in , How I did it easily


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


Excel Some of the problems in , How I did it easily


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())



Excel Some of the problems in , How I did it easily


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)})



Excel Some of the problems in , How I did it easily


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 !


Excel Some of the problems in , How I did it easily


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


Excel Some of the problems in , How I did it easily


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 !
版权声明
本文为[Two leisure places]所创,转载请带上原文链接,感谢
https://fheadline.com/2020/11/202011270445259289.html
相似文章

2021-08-09