Escolha os 3 principais valores e some-os

Question

Digamos que eu tenha alguns dados parecidos com estes:

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 faria isso?

Por exemplo, ele retornaria estes dados:

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

Pensei em 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 isso não funcionaria.

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 receber conselhos sobre como encontrar os 3 primeiros, poderei descobrir como adaptá-lo para os 3 do meio.

Isso é algo em que o Power Query pode ser útil? Eu me envolvi, mas não sou profissional. (Se fosse absolutamente necessário, provavelmente também poderia preparar algo em VBA, mas prefiro não seguir esse caminho, se possível, já que quero colocá-lo em uma planilha do Google para eventualmente compartilhá-lo na web).

Answer 1

Classifique seus dados por equipe e pontuação decrescente. Em seguida, use esta fórmula que encontra o primeiro de cada equipe e soma as próximas três pontuações ou o número de pontuações dessa equipe, se for menor:

=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 irá somar as 3 melhores pontuações de cada equipe:

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

É uma fórmula de matriz, portanto deve ser inserida com CTRLShiftEnter, em vez de apenas Enter.

Os resultados são mostrados abaixo:

Como funciona: O IF()retorna uma lista dos pontos da equipe listada na coluna E. Em seguida, LARGE()obtém apenas as 3 primeiras pontuações. IFERROR()trata dos casos em que há menos de 3 pontuações. Finalmente, SUM()some-os.

EDIT: Para obter a soma das 3 pontuações intermediárias, tentei usar o formulário de referência INDEX() com matrizes literais e uma fórmula para escolher "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. Finalmente consegui que funcionasse usando uma coluna auxiliar para especificar os arrays:

=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 das 3 primeiras e das 3 pontuações intermediárias.

1. Classifiquei os dados de origem em Nome da equipe como campo primário em ordem crescente e em Pontos como campo secundário em ordem decrescente. (Para permitir o cálculo manual da soma das 3 primeiras pontuações).
2. Usei uma fórmula para gerar a lista de equipes em ordem crescente e decrescente.
3. Por fim, calculei a soma das 3 pontuações principais e das 3 intermediárias, usando uma fórmula de uma das respostas acima.

Aqui estão as fórmulas para:

Lista de equipes em ordem crescente:

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

Lista de equipes em ordem decrescente:

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

Soma das 3 primeiras pontuações das equipes em ordem crescente:

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

Soma das 3 pontuações intermediárias das equipes em ordem crescente:

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

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

Notas:

1. Substitua J216 por G216 para obter as 3 pontuações principais e intermediárias para equipes em ordem decrescente.
2. Como a contagem máxima de equipes é 5, presumi que as 3 pontuações intermediárias são os números 2,3 e 4.Isso elimina a pontuação mais alta do total, mesmo que haja apenas 2 ou 3 pontuações. Se você acredita que uma equipe com apenas 2 ou 3 pontos deveria tertodosas pontuações contribuem para o total, então considere uma das respostas acima.