Obtenha porcentagem de valores distintos com dependência da 2ª coluna

Question

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

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

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

Como consigo essas porcentagens?

Answer 1

Um normal countifnão funciona com mais de uma coluna como dependência. Use SUMPRODUCTem vez disso.

1. Esta fórmula contará todas as linhas ondehomensEdor de cabeçaé verdade.

   =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)...

   Saída de exemplo = 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)
   

   Saída de exemplo = 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, a segunda para mulheres com dor de cabeça

---

Existem algumas alternativas para a primeira fórmula =SUMPRODUCT((A1:A100=1)*(B1:B100=1))que darão 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ãofórmulas de matriz {...}que são inseridos com CTRL+ SHIFT+ENTER

Fonte

Answer 2

Outra maneira é usar uma AVERAGEfunção 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), então 1 ou 0 será retornado dependendo se são homens ou mulheres, a média deles fornece a porcentagem correta