Obtém porcentagem de valores distintos com dependência na segunda coluna

Question

Eu tenho uma pesquisa que enviei para as pessoas. Uma pergunta perguntou o sexo deles e outra perguntou se eles tinham dores de cabeça. Agora tenho que resultados em um arquivo do Excel e estou tentando analisá-lo.
Mas estou tendo problemas para calcular uma estatística.

O que eu quero é calcular que percentagem de pessoas que sofrem de dores de cabeça são homens e que percentagem são mulheres.

Eu sei que o conceito é # people that are men AND experience headaches / e # of people that experience headaches

Como obtenho essas porcentagens?

    

Answer 1

Um countif normal não funciona com mais de uma coluna como dependência. Use SUMPRODUCT em vez disso.

1. Esta fórmula contará todas as linhas em que men E dor de cabeça é verdadeira.

   =SUMPRODUCT((A1:A100=1)*(B1:B100=1))
   

   Observe que você pode expandir a fórmula para ter uma terceira ou mais dependências, se desejar »
   ...*(C1:C100=x)...

   Exemplo de saída = 2

2. Para obter a contagem de pessoas com dores de cabeça, use COUNTIF , pois aqui só temos uma coluna para procurar

   COUNTIF(B:B,1)
   

   Exemplo de saída = 6

3. Agora você só precisa combinar essas duas fórmulas

   =SUMPRODUCT((A1:A100=1)*(B1:B100=1)) / COUNTIF(B:B,1)
   =SUMPRODUCT((A1:A100=2)*(B1:B100=1)) / COUNTIF(B:B,1)
   

   Exemplo de saída = 2/6 = 0,333333 = 33,33%
   Exemplo de saída = 4/6 = 0,666667 = 66,67%

   A primeira fórmula é para homens com dor de cabeça, segundo para mulheres com dor de cabeça

Existem algumas alternativas para a primeira fórmula =SUMPRODUCT((A1:A100=1)*(B1:B100=1)) , que lhe dará o mesmo resultado. Escolha o seu favorito:

• {=SUM(IF((A1:A100=1)*(B1:B100=1),1))}

• {=SUM((A1:A100=1)*(B1:B100=1))}

• {=SUM(N(A1:A100&B1:B100=1&1))}

• =COUNTIFS(A:A;1,B:B,1) »não disponível no Excel 2003

• {=SUM((A:A=1)*(B:B=1))} »não disponível no Excel 2003

Observe que a maioria deles são fórmulas de matriz {...} que são inseridos com CTRL + SHIFT + ENTER

Origem

    

Answer 2

Outra maneira é usar uma função AVERAGE como esta

=AVERAGE(IF(B2:B100=1,IF(A2:A100=1,1,0)))

confirmado com CTRL + SHIFT + ENTER

Quando B2: B100 = 1 (dores de cabeça experimentadas), então 1 ou 0 serão retornados, dependendo se eles são homens ou mulheres. A média desses valores lhe dá a porcentagem correta