Questão Como extrair valores máximos de várias linhas e contar quantos caem para determinados critérios, na função de uma célula


Eu tenho uma folha no Excel que mostra o sucesso dos alunos em certas partes do exame (testes, apresentações, exame final etc.). Eu preciso calcular alguns dados estatísticos e o problema é que para alguns testes eles têm três tentativas, apenas os pontos mais altos contam e eu não tenho a coluna "max". Preciso contar quantos conseguiram mais de 15 pontos na melhor tentativa, quantos entre 10 e 14,9, etc. Também preciso ver quantos alunos tiveram pelo menos uma tentativa (com base em um campo não vazio ou> = 0)

Tabela se parece com isso (uma parte):
    Estudante .... Att1 .... Att2 .... Att3
    Anna ......... 10 ...... 14 ..... 13,5
    Belle ......... 7 ......... 15 ...... 15,5
    Clara ........ 17 ...... 18 ..... 16,5

Então, a partir disso eu precisaria extrair em uma célula esse número de valores máximos por linha> = 15 é 2 (Belle em 15,5 e Clara em 18), e que o total de alunos já tentados pelo menos uma vez é 3.

Repito que não posso adicionar a coluna separada onde eu calcularia os valores máximos porque é um documento oficial, e adicionar um à outra folha não é uma solução preferida.

Eu tentei algumas combinações de subtotal e offset, mas sem muito sucesso. Eu uso o MS Excel 2013, outros colegas usam 2007 e 2010.


1


origem


Se você não pode adicionar a esta folha, onde você pretende colocar a fórmula? - CharlieRB
Por favor, você poderia mostrar (com base neste exemplo) a matriz final esperada com todas essas informações extras sem nenhuma coluna adicionada? - NeronLeVelu


Respostas:


Esta fórmula pode ser facilmente expandida para qualquer número de linhas ou colunas

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>15)+0)

OFFSET retorna cada linha como um intervalo separado, SUBTOTAL encontra o valor máximo [4] em cada linha e SUMPRODUCT conta quantos desses valores são> 15.

Também é possível fazer o mesmo com COUNTIF em vez de SUBTOTAL, ou seja

=SUMPRODUCT((COUNTIF(OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0),">15")>0)+0)

Para um intervalo, por ex. > 10 e <= 15 você pode ajustar a versão SUBTOTAL assim

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>{10,15})*{1,-1})


3



Como você faria uma condição entre dois números? - Madball73
Boa pergunta, Madball - eu editei minha resposta - barry houdini
Testando o acima, parece o entre obras. Também me fez perceber que o meu método não deveria e deveria ter usado + -. Removendo minha resposta. O seu também tem a vantagem de ser um único intervalo em vez de 3. Doce! - Madball73
Madball73, muito obrigado por isso. A fórmula funciona bem e me fornece os dados desejados. Existe apenas um caso estranho: tenho alunos que tentaram o teste, conseguiram 0 pontos, e alunos que não tentaram o teste e não têm pontos (célula vazia), eu também tenho que diferenciá-los. A fórmula que você deu, com <{5,0}) * {1, -1}) no final (eu precisava> = 0 e <5), conta também quando o aluno tem 3 células vazias, ou 3 células com alguma texto como N / A, -. Terminando <5) +0) faz o mesmo. Como posso evitar isso e diferenciar célula vazia (ou célula contendo valor não numérico) e 0, ou contar apenas 0? - adriatic


para a contagem de mais ou igual a 15 para uma linha (digamos nas colunas E, então E2 aqui)

=COUNTIF(B2:D2;">=15")

para você max (aqui no E5)

=countif(E2:E4;">0")

amostra

student at1 at2 at3     up15
anna    10  14  13,5    0
Belle   7   15  15,5    2
clara   17  18  16,5    3
                 Max    2

pode adicionar um teste por pelo menos 3 tentativas por linha, mas não está claro onde você deseja (por linha ou no máximo)


1



Há um requisito em que o auxiliar / colunas adicionais não podem ser adicionadas. - Madball73
mas adicionar algo, por si só, adiciona uma coluna (ou linha). Agora uma coluna oculta está disponível? Há também 2 outra solução 1) fazê-lo a partir de uma planilha externa (não uma solução limpa) 2) fazê-lo a partir de uma macro que, eventualmente, usar a função interna excel (mas é certamente mais eficiente com código vbs puro). - NeronLeVelu