Soma até N o maior valor de uma série?

Question

Eu tenho uma planilha onde cada linha é um conjunto de pontuações numéricas de competição. Cada fila pode ser considerada como um único competidor na competição geral, e cada coluna é um evento de competição. Nada realmente incomum lá.

Agora, quero extrair a soma das N maiores pontuações de valor de cada linha, de modo que:

• Se forem fornecidas pontuações menores ou iguais a N , some todas as pontuações listadas
• Se mais de N resultados forem fornecidos, some o N mais alto da série

Como fazer isso?

No meu caso particular, N = 5, mas espero que exista uma solução genérica.

Prefiro não usar uma macro para isso, mas estou disposto a explorar essa possibilidade, se isso não puder ser feito com uma fórmula simples.

    

Answer 1

Use a função LARGE .

=LARGE(Range;1)+LARGE(Range;2)+…+LARGE(Range;N)

ou

=SUM(LARGE(Range;1); LARGE(Range;2); …; LARGE(Range;N))

Observe que, em algumas localidades, os parâmetros da função devem ser separados por vírgulas, em vez de ponto e vírgula.

    

Answer 2

OK, se os dados numéricos de seus dados estiverem em linhas em Sheet1 , insira seu valor N em Sheet2!N1 e digite

=SUM(LARGE(Sheet1!1:1, ROW(INDIRECT("1:"&MIN(COUNT(Sheet1!1:1),N$1)))))

em Sheet2!A1 . (Se os dados estiverem apenas em, por exemplo, Colunas G a Z , altere Sheet1!1:1 para Sheet1!G1:Z1 .) Pressione Ctrl + Deslocamento + < kbd> Digite para criar uma fórmula de matriz. Arraste quantas linhas precisar e você deve terminar. (Observe que, em Sheet2!A17 , por exemplo, Sheet1!1:1 terá alterado automaticamente para Sheet1!17:17 , ou seja, a 17ª linha de Sheet1 , porque o número da linha é relativo - não precedido por $ .)

Explicação:

• COUNT(Sheet1!1:1) é o número de números (pontuações) na linha referenciada (que, conforme indicado acima, pode ser Linha 1 , Linha 17 ou a linha que você está visualizando em Sheet2 ). Eu faço isso porque você disse: “nem todos os competidores participam de cada evento”; Eu suponho que as não participações estão em branco, ou talvez uma string não numérica.
• N$1 é o valor N especificado; o número de pontuações que você deseja adicionar.
• MIN() é, obviamente, mínimo. Se N for 5 e um concorrente estiver em apenas três eventos, queremos adicionar todos os três. Se um competidor estava em sete eventos, queremos somar os cinco mais altos.
• & é o operador de concatenação de strings no Excel (e, pelo que ouvi, o Libre Office Calc é muito semelhante ao Excel), então, se o número de pontuações ( MIN(…) ) que estamos adicionando for, digamos , 5, em seguida, "1:"&MIN(…) se torna o valor da string "1:5" .
• INDIRECT("1:5") é a região que compreende a linha 1 até a linha 5 e
• ROW() disso é o array {1,2,3,4,5} .
  Este é um truque para criar um valor de matriz especificado por dados de tempo de execução.
• LARGE(Sheet1!1:1, {1,2,3,4,5}) é o array { LARGE(Sheet1!1:1,1), LARGE(Sheet1!1:1,2), LARGE(Sheet1!1:1,3), LARGE(Sheet1!1:1,4), LARGE(Sheet1!1:1,5) } , qual é a maior (mais alta) pontuação na faixa, a segunda maior,… e assim por diante, até o 5º.

Presumi que você está em uma localidade que usa vírgulas para separadores. Se você está na terra dos ponto e vírgula, faça como os ponto e vírgula.

    

Answer 3

Eu abordaria isso construindo as pontuações como uma tabela e depois mantendo a tabela classificada pela pontuação. Então você pode olhar apenas as primeiras n colunas ...

Se você precisar mudar n "on the fly", você pode fazer referência indireta das células com base em um valor que você definiu.