Escolha os 3 principais valores e some-os

Question

Digamos que eu tenha alguns dados parecidos com isso:

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

Se eu quisesse escrever uma fórmula para somar as 3 melhores pontuações de cada equipe, como eu faria isso?

Por exemplo, ele retornaria esses dados:

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

Eu considerei usar um filtro para dizer "se o valor for maior que o valor médio dos membros da equipe" ou algo assim, mas se houvesse menos de 3 membros que não funcionariam.

Eu também considerei sumif , mas não acho que seria apropriado pelo mesmo motivo.

Também estou procurando encontrar a soma das pontuações do MIDDLE 3, mas tenho certeza de que, se obtiver conselhos sobre como encontrar o top 3, posso descobrir como adaptá-lo para o middle 3.

Isso é algo com o qual a Power Query poderia ser útil? Eu me interessei, mas não sou profissional. (Se eu tivesse que fazê-lo, provavelmente também poderia criar algo no VBA, mas prefiro não seguir esse caminho se possível, já que quero colocá-lo em uma planilha do Google para compartilhá-lo na Web, eventualmente).

    

Answer 1

Ordene seus dados por equipe e marque a pontuação decrescente. Em seguida, use essa fórmula que encontre a primeira de cada equipe e some as próximas três pontuações ou o número de pontuações para essa equipe, se menos:

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

    

Answer 2

Esta fórmula resumirá as três melhores pontuações para cada equipe:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

É uma fórmula de matriz, por isso deve ser introduzida com CTRL Deslocar Introduzir , em vez de apenas Introduzir .

Os resultados são mostrados abaixo:

Comofunciona:OIF()retornaumalistadepontosparaaequipelistadanacolunaE.Emseguida,LARGE()obtémapenasas3melhorespontuações.IFERROR()lidacomoscasosemquehámenosde3pontuações.Finalmente,SUM()adiciona-os.

EDIT:Paraobterasomadas3pontuaçõesdomeio,tenteiusaroformuláriodereferênciadeINDEX()commatrizesliteraiseumafórmulaparaescolhero"area_num":

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Mas não aceitaria as constantes da matriz como referência. Eu finalmente consegui que funcionasse usando uma coluna auxiliar para especificar as matrizes:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

    

Answer 3

Minha abordagem é apenas um pouco diferente para obter a soma dos 3 principais & meio 3 pontos.

1. Classifiqueiosdadosdeorigemnonomedaequipecomoocampoprincipalordemcrescenteeempontoscomoocamposecundárioemdescendenteordem.(Parapermitirocálculodasomadas3melhorespontuaçõesmanualmente).
2. Euuseiumafórmulaparageraralistadeequipesemambosascendente&ordemdecrescente.
3. Finalmente,calculeiasomadostrêsprimeirosetrêspontosmédios,usandoumafórmuladeumadasrespostasacima.

Aquiestãoasfórmulaspara:

Listadeequipesemordemcrescente:

{=INDEX($D$216:$D$233,MATCH(0,COUNTIF($J$215:J215,$D$216:$D$233),0))}

Listadeequipesemordemdecrescente:

=IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

Soma dos três melhores resultados para equipes em ordem crescente:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

Soma do meio 3 Pontuações para equipes em ordem crescente:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

Veja a Nota 2 abaixo para uma explicação desta fórmula.

Notas:

1. Substitua o J216 pelo G216 para obter o & meio 3 pontos para as equipes em ordem decrescente.
2. Como a contagem máxima de equipes é 5, presumi que o as pontuações do meio 3 são números 2,3 & 4. Isso elimina o mais alto pontuação do total, mesmo que haja apenas 2 ou 3 pontuações . E se você acredita que uma equipe com apenas 2 ou 3 pontos deve ter todos pontuações contribuindo para o total, considere uma das respostas acima.