Excel: Como fazer uma contagem do número de células que contêm “X” E que a célula acima contenha “Y”

Question

Eu sou um usuário médio do Excel e este é um problema que eu encontrei antes e, eventualmente, encontrei uma maneira diferente de fazê-lo, mas neste caso eu acho que é minha única opção.

Aqui está uma imagem de amostra da minha planilha:

Eu quero contar o número de vezes que uma célula no intervalo contém "1a" E a célula acima da célula com 1a contém "Math". Obviamente, este exemplo é trivial, mas imaginei que a lógica para resolver isso seria a mesma que se fosse uma planilha maior com meus dados preenchendo-a completamente.

Neste exemplo, a resposta deve ser duas vezes. Como eu faria para construir uma fórmula para fazer esse tipo de contagem condicional?

    

Answer 1

Estou propondo uma solução que requer uma coluna auxiliar. Supondo que seus dados estejam organizados em Células A3: J8, use a Coluna K como Ajudante e coloque a seguinte Fórmula de Matriz aqui

{=SUM(IF($A4:$J4="1a",IF($A3:$J3="Math",1,0)))}

Coloque a fórmula sem chaves e, em seguida, na barra de fórmulas, pressione CTRL + SHIFT + ENTER para criar a fórmula de matriz. Isso dá contagem de '1a' com a palavra 'Math' acima disso. Comece no K4 e arraste para baixo. Para linhas alternadas, ele retornará 0, mas isso não deve importar. Agora apenas SUM a coluna auxiliar. Você pode usar a função SUM simples para isso. Pode haver maneiras mais inteligentes de conseguir isso sem a coluna Auxiliar ou até mesmo usar Macros VBA.

    

Answer 2

Você pode usar a fórmula do COUNTIFS. Ele permite que você especifique vários critérios. Usando o exemplo por pat2015, se os dados estiverem no intervalo A3: J8, a fórmula deve ser

=COUNTIFS(A3:J7;"Math";A4:J8;"1a")

(observe o deslocamento de uma linha entre os critérios)

    

Answer 3

Se você reformatar os dados para ter Math e 1a na mesma célula:

=G5&" "&G6

você pode usar o countif:

=COUNTIF($F$15:$K$18,N15)

    

Answer 4

Você pode usar:

=SUMPRODUCT(--(F3:K12&F2:K11="1a"&"Math"))

Você tem que usar a matriz inicial (F3: K12) na primeira referência e depois de & sua referência menos uma linha (adicionar uma linha acima e menos uma linha abaixo) (F2: K11)
Nesse caso (F3:K12&F2:K11="1a"&"Math") retornará True se as linhas em F3: K12="1a" e F2: K11="Math"
O -- na frente da condição retornará 1 se for True
Sumproduct será Sum (1,1,0 ...) Qual é a contagem

    

Answer 5

=COUNTIFS(F3:K11, "Math", F4:K12, "1a")

que já foi apresentado por fitch496 , é um bom começo. (Nota: algumas localidades usam ; para separar parâmetros de funções; outros usam , .) Tem um problema possível: ele irá contar pares de células onde "Math" está em uma linha par, e “1a” está abaixo em uma linha ímpar. (Embora a questão não fale sobre esse problema, a ilustração sugere que tais pares não devem ser contados.)

Para resolver esse problema, comecei traduzindo a fórmula acima em um que produz o mesmo resultado, mas usando SUMPRODUCT :

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"))

Aqui usamos os truques padrão de preceder uma expressão booleana por -- para transformar TRUE em 1 e FALSE em 0, e usando a multiplicação implícita de SUMPRODUCT como AND lógico. Então eu estendi isso para

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11),2)=1))

para testar que "Math" apareceu em uma linha ímpar ( MOD(ROW(…),2)=1 ). Isso falhou porque o Excel parece tratar ROW(F3:K11) como o mesmo que ROW(3:11) , produzindo uma matriz linear de 9 valores em vez de uma matriz retangular de 54 valores. (IMNSHO, isso é um bug no Excel.) Eu descobri que eu poderia consertar isso forçando-o a levar as colunas em consideração, ignorando-as:

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2)=1))

que avalia o número da coluna de cada célula ( COLUMN(…) ) e depois multiplica por 0. Isso funciona. A ilustração abaixo é a mesma dos dados do OP na pergunta, mas com a adição de "Math" e "1a" nas células J8 e J9 . fitch496 COUNTIFS formula (que incluo no topo da minha resposta) e minha simples fórmula SUMPRODUCT ambos contam este “Math 1a” desalinhado e produzem um total de 3; minha fórmula SUMPRODUCT final ignora o desalinhado "Math 1a" e produz um total de 2.

^{(Vejaafontedestepostparaumacópiaamigávelcopiarecolardositensacima.)}

Minhafórmulapodesersimplificadaumpouco:

• --(MOD(rownumber,2)=1)levaonúmerodalinhamodulo2etestaseéiguala1.Sefor,otestedecomparaçãoproduzumVERDADEIRO,queéconvertidoem1por--.Nãoé1,deveser0;acomparaçãopara1produzumFALSE,queéconvertidoem0por--.Podemosdispensarasoperaçõeslógicas(testandoaqualidadee,emseguida,aplicando--aoresultadobooleano)euseapenasMOD(rownumber,2).
• --(Boolean)ésimplesmente-(-(Boolean)).Omaisinterno-converteTRUEem1eFALSEem0,masdepoistransforma1em-1.Entãoo%externo-converte-1para1.Jáqueestamoslevandooprodutodedoisfatores--,os-sexternosseanulam,entãopodemosomiti-los.

Portanto,afórmulafinalesimplificadaé

=SUMPRODUCT(-(F3:K11="Math"), -(F4:K12="1a"), MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2))

    

Answer 6

Se você deseja obter o valor total de quantas vezes Math e amp; 1a é escrito por fórmula única, deve ser,

= COUNTIF (B86; E91, "Math") + Countif (B86: E91, "1a")

Se quiser contar separadamente, use a mesma fórmula Countif, duas vezes.