Utilizando o CONTENT no Excel para contar o número de valores “Maior que” em uma matriz de valores

Question

Então aqui está a minha situação. Sou a principal responsável por criar um rastreador para meus professores usarem para acompanhar o crescimento dos alunos nas avaliações. No rastreador atual, ele é configurado assim:

            % of            67%
            Students Met

**Student Name    Week 1 Goal:         Week 1 Actual:        Goal Met?:**
  Example A       23                    32                    Y
  Example B       45                    44                    N
  Example C       53                    55                    Y

Eu uso uma fórmula para o "Go Met"? coluna para colocar um Y ou N, dependendo se o Real atende ou excede a coluna Meta. Em seguida, uso outra fórmula para determinar a% de alunos atendidos, fazendo um COUNTIF de "Y" s na coluna Meta de meta, dividindo-a por um COUNTA dessa coluna e multiplicando por 100.

O que eu estou querendo saber é ... isso é muito complicado de usar para os professores, e quanto mais colunas e fórmulas eu coloco, maior o risco de os professores digitarem no lugar errado e bagunçarem as fórmulas ... Existe alguma maneira de fazer um COUNTIF das colunas Array of the Weekly Goal e Weekly Actual, e contar apenas as linhas em que o Actual é > = the Goal? Como se houvesse uma maneira de fazer COUNTIF (E5: F17, F > = E) e colocar algum tipo de símbolo como uma variável pelo E e F nos critérios para que vá linha por linha e compare os valores, então conta o aqueles em que o valor F é maior que o valor E?

Qualquer sugestão seria ótima, porque eu posso eliminar a coluna "Meta Met" (que eles invariavelmente digitam e eu tenho que voltar e corrigir semanalmente). Fica chato ter que proteger cada terceira coluna em um rastreador massivo!

Muito obrigado por qualquer conselho!

Brendan

    

Answer 1

Você deve sempre proteger com senha suas células que não deseja alterar.

Para responder à sua pergunta, você precisará inserir uma fórmula de matriz. Digite o seguinte ao assumir que os nomes das colunas começam em A3 e, em seguida, tecle Ctrl + Deslocar + Enter .

=SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)

Se você fez isso corretamente, colchetes aparecerão ao redor de toda a fórmula.

={SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)}

    

Answer 2

Minha solução

A seguinte fórmula de matriz, com referências de células ajustadas adequadamente, deve fazer o que você está procurando:

=SUM(1*((OFFSET(C2,0,0,COUNT(C:C),1)-OFFSET(B2,0,0,COUNT(C:C),1))>=0))/COUNT(C:C)

Exemplo:

DefinaareferênciaC2noprimeiroOFFSETparaacélulasuperiordosdados"Reais" ( F5 no seu exemplo), o B2 referência no segundo OFFSET para a célula superior dos dados de 'Objetivo' ( E5 no seu) e defina todas as referências C: C para a coluna contendo um ou outro dos dados "Real" ou "Objetivo" ( E: E ou F: F no seu). Verifique se as únicas células com dados numéricos na coluna escolhida para substituir as referências C: C são aquelas células com 'Real' ou Valores de 'Objetivo' neles, caso contrário, a fórmula retornará um resultado impreciso ou falhará completamente.

Para inserir a fórmula como uma fórmula de matriz, digite-a apropriadamente e pressione Ctrl-Shift-Enter, em vez de apenas Enter. (Veja aqui para obter mais informações sobre fórmulas de matriz ou apenas pesquisar on-line para "fórmula de matriz de excel".) A qualquer momento você faz uma alteração na fórmula, sempre terá que inseri-la com Ctrl-Shift-Enter para que ele se comporte adequadamente.

Como funciona

• Cada COUNT recupera o número de linhas de dados presentes.
• As duas chamadas OFFSET recuperam as duas matrizes de interesse, as pontuações "Atual" e "Objetivo". Em cada chamada:
  • O primeiro argumento marca a célula superior esquerda da matriz;
  • O segundo e terceiro argumentos são os deslocamentos de linhas e colunas, respectivamente (ambos zero aqui);
  • O quarto argumento indica o número de linhas a serem incluídas na matriz; e
  • O quinto argumento indica o número de colunas a serem incluídas.
• Como essa é uma fórmula de matriz, subtrair as matrizes "Real" e "Objetivo" retorna uma matriz do mesmo tamanho, mas contendo a diferença entre elementos entre elas.
• A comparação >=0 converte a matriz de diferenças em uma matriz de valores TRUE / FALSE , indicando se a meta foi ou não atingida.
• O 1* está lá para converter os valores TRUE e FALSE booleano para 1 e 0 numéricos, para depois serem contados pela função SUM .
  • No Excel, TRUE e FALSE implicitamente transportam os valores numéricos de um e zero.
  • MAS, se você tentar transmitir um intervalo ou matriz contendo TRUE / FALSE valores para uma função como SUM , ele ignorará TRUE valores e retornará zero.
  • Portanto, multiplicar o array TRUE / FALSE por 1 é uma maneira de induzir o Excel a interpretar o array de uma maneira útil.
• O SUM dividido pelo COUNT é a fração desejada de alunos que atendem às metas definidas.

EDIT : Por resposta do wbeard52, deve ser possível no Excel 2010 ou posterior substituir o 1*... hack por uma estrutura IF . Em versões mais antigas do Excel, IF manipulou incorretamente as entradas da matriz.

    

Answer 3

Outro par de opções de fórmulas de matriz, então você precisa pressionar Ctrl + Deslocar + Enter para confirmá-las.

Para encontrar o número de metas atendidas:

=SUM(--(C4:C6>B4:B6))

Para encontrar a porcentagem de metas atingidas:

=SUM(--(C4:C6>B4:B6))/count(C4:C6)

Você pode usá-los independentemente. Lembre-se de inseri-los como fórmulas de matriz pressionando Ctrl + Deslocar + Enter .

    

Answer 4

Outra abordagem de "fórmula de matriz" é assim:

=AVERAGE(IF(E5:E17<>"",IF(F5:F17>=E5:E17,1,0)))

confirmado com CTRL + SHIFT + ENTER

Isso dá a você um% real como 0,5 - se você quiser 50, multiplique por 100