☰

Com o Excel, dados agrupados, como estimar quartis?

tag-icon • tag-icon microsoft-excel
Com o Excel, dados agrupados, como estimar quartis?

Suponha que eu tenha os seguintes dados agrupados para massa de cães:

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

Como posso usar o Excel para estimar o primeiro quartil?

Sei como fazer uma ogiva (usando um diagrama de dispersão com pontos conectados por uma curva suave) e posso usar a ogiva e meus olhos (e talvez uma régua colocada na tela) para encontrar aproximadamente o primeiro quartil. Mas o Excel pode me dar uma resposta mais rigorosa?

Não quero usar a função "Adicionar linha de tendência", porque uma linha de tendência não é realmente uma ogiva (uma linha de tendência não passa por todos os pontos).

Responder1

Estes são dados agrupados, portanto, a menos que você tenha os dados brutos, você terá que fazer algo para recriar a variação em seus dados. Para simplificar, podemos assumir uma distribuição plana - os cães têm a mesma probabilidade de ter 0 ou 5.

Use a REPT()função para gerar uma lista de números separados por vírgula que representa cada grupo.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

Usar REPT

Na parte inferior, concatene cada uma dessas strings em uma string grande:

Corda longa

Copie esta string eColar especialcomoValoresem uma nova planilha. Use oTexto para colunasferramenta noDadosguia da faixa de opções para dividir os dados em uma coluna por valor. Copie todo esse intervalo e entãoColar especialeTransporpara transformar isso em uma lista vertical de valores. Seus dados devem ser parecidos com isto:

dados transpostos

Sinta-se à vontade para descartar a linha horizontal - não precisamos dela. Agora queremos interpolar alguns valores na coluna B, usando uma fórmula parecida com:

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

Decompondo isso, temos:

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

Agora, você pode usar a QUARTILE()função nesta lista de valores estimados para aproximar seus quartis:

=QUARTILE($B$5:$B$130,1)

insira a descrição da imagem aqui

Responder2

Graficamente, você quer fazer umhistogramacom as "caixas" definidas em quartis. Você pode pesquisar como fazer isso.

Alternativamente, você pode usar uma fórmula. Descreverei isso em palavras e você poderá descobrir a matemática.

Existem 126 cães. 25% disso é 31,5. Você deseja encontrar o peso que os 31,5 cães mais leves pesariam menos.

Há 13 cães na lixeira mais leve, então eles são contados. Isso deixa 18,5 cachorros, mas a próxima lixeira tem 28 cachorros. Portanto, a questão é: qual é o peso do 18,5º cão mais leve da segunda lixeira?

Usando o que é chamado de interpolação linear, você pode estimar que o peso desconhecido é 18,5/28 entre 6 e 10 libras. Isso é 6+4*(18,5/28).

Você pode usar a mesma abordagem para encontrar os outros quartis, se necessário.

A linguagem aqui é um pouco estranha, mas você entendeu. Eu espero que isso ajude.

EDITAR: Se você conhece o peso de todos os cães individualmente, basta classificá-los em ordem e calcular o peso médio do 31º e 32º cães.

informação relacionada