Calculando o intervalo de confiança no Excel

O Excel é utilizado para realizar diversas tarefas estatísticas, uma das quais é o cálculo do intervalo de confiança, que é utilizado como a substituição mais adequada para uma estimativa pontual com tamanho amostral pequeno.

Queremos notar desde já que o próprio procedimento para calcular o intervalo de confiança é bastante complicado, no entanto, no Excel existem várias ferramentas projetadas para facilitar essa tarefa. Vamos dar uma olhada neles.

Cálculo do intervalo de confiança

Um intervalo de confiança é necessário para fornecer uma estimativa de intervalo para alguns dados estáticos. O principal objetivo desta operação é remover as incertezas da estimativa pontual.

Existem dois métodos para executar esta tarefa no Microsoft Excel:

• operador NORMA DE CONFIANÇA – utilizado nos casos em que a dispersão é conhecida;
• operador ESTUDANTE DE CONFIANÇAquando a variação é desconhecida.

Abaixo vamos analisar passo a passo os dois métodos na prática.

Método 1: declaração TRUST.NORM

Esta função foi introduzida pela primeira vez no arsenal do programa na edição Excel 2010 (antes desta versão, foi substituída pelo operador “CONFIÁVEL”). O operador está incluído na categoria “estatística”.

Fórmula da função NORMA DE CONFIANÇA se parece com isso:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Como podemos ver, a função tem três argumentos:

• "Alpha" é um indicador do nível de significância, que é tomado como base para o cálculo. O nível de confiança é calculado da seguinte forma:
  • 1-"Альфа". Esta expressão é aplicável se o valor "Alpha" apresentado como um coeficiente. Por exemplo, 1-0,7 0,3 =, onde 0,7=70%/100%.
  • (100-"Альфа")/100. Esta expressão será aplicada se considerarmos o nível de confiança com o valor "Alpha" em porcentagens. Por exemplo, (100-70) / 100 = 0,3.
• "Desvio padrão" – respectivamente, o desvio padrão da amostra de dados analisada.
• "Tamanho" é o tamanho da amostra de dados.

Observação: Para esta função, a presença de todos os três argumentos é um pré-requisito.

Operador “CONFIÁVEL”, que foi usado em versões anteriores do programa, contém os mesmos argumentos e executa as mesmas funções.

Fórmula da função CONFIÁVEL como se segue:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Não há diferenças na fórmula em si, apenas o nome do operador é diferente. No Excel 2010 e em edições posteriores, esse operador está na categoria Compatibilidade. Nas versões mais antigas do programa, ele está localizado na seção de funções estáticas.

O limite do intervalo de confiança é determinado pela seguinte fórmula:

X+(-)ДОВЕРИТ.НОРМ

onde Х é o valor médio no intervalo especificado.

Agora vamos ver como aplicar essas fórmulas na prática. Assim, temos uma tabela com vários dados de 10 medições realizadas. Nesse caso, o desvio padrão do conjunto de dados é 8.

Nossa tarefa é obter o valor do intervalo de confiança com um nível de confiança de 95%.

1. Em primeiro lugar, selecione uma célula para exibir o resultado. Então clicamos no botão “Inserir função” (à esquerda da barra de fórmulas).
2. A janela do Assistente de Função é aberta. Ao clicar na categoria atual de funções, expanda a lista e clique na linha nela “Estatístico”.
3. Na lista proposta, clique no operador “NORMA DE CONFIANÇA”, então aperte OK.
4. Veremos uma janela com as configurações dos argumentos da função, preenchendo a qual pressionamos o botão OK.
   • no campo "Alpha" indicar o nível de significância. Nossa tarefa assume um nível de confiança de 95%. Substituindo esse valor na fórmula de cálculo, que consideramos acima, obtemos a expressão: (100-95)/100. Nós o escrevemos no campo do argumento (ou você pode escrever imediatamente o resultado do cálculo igual a 0,05).
   • no campo “std_off” de acordo com nossas condições, escrevemos o número 8.
   • no campo “Tamanho”, especifique o número de elementos a serem examinados. No nosso caso, foram feitas 10 medições, então escrevemos o número 10.
5. Para evitar ter que reconfigurar a função quando os dados são alterados, você pode automatizá-la. Para isso usamos a função “VERIFICA". Coloque o ponteiro na área de entrada das informações do argumento "Tamanho", clique no ícone de triângulo no lado esquerdo da barra de fórmulas e clique no item “Mais recursos…”.
6. Como resultado, outra janela do Assistente de Função será aberta. Ao escolher uma categoria “Estatístico”, clique na função “VERIFICA", Então ok.
7. A tela exibirá outra janela com as configurações dos argumentos da função, que é usada para determinar o número de células em um determinado intervalo que contém dados numéricos.

   Fórmula da função VERIFICAÇÃO está escrito assim: =СЧЁТ(Значение1;Значение2;...).

   O número de argumentos disponíveis para esta função pode ser de até 255. Aqui você pode escrever números específicos, endereços de células ou intervalos de células. Usaremos a última opção. Para fazer isso, clique na área de entrada de informações do primeiro argumento, em seguida, mantendo pressionado o botão esquerdo do mouse, selecione todas as células de uma das colunas de nossa tabela (sem contar o cabeçalho) e pressione o botão OK.

8. Como resultado das ações realizadas, o resultado dos cálculos para o operador será exibido na célula selecionada NORMA DE CONFIANÇA. Em nosso problema, seu valor acabou sendo igual a 4,9583603.
9. Mas este ainda não é o resultado final da nossa tarefa. Em seguida, você precisa calcular o valor médio em um determinado intervalo. Para fazer isso, você precisa usar a função “CORAÇÃO"A que executa a tarefa de calcular a média em um intervalo especificado de dados.

   A fórmula do operador é escrita assim: =СРЗНАЧ(число1;число2;...).

   Selecione a célula onde pretendemos inserir a função e pressione o botão “Inserir função”.

10. Na categoria “Estatístico” escolha um operador chato "CORAÇÃO" e clique OK.
11. Nos argumentos da função no valor do argumento "Número" especifique o intervalo, que inclui todas as células com os valores de todas as medições. Então clicamos OK.
12. Como resultado das ações realizadas, o valor médio será calculado automaticamente e exibido na célula com a nova função inserida.
13. Agora precisamos calcular os limites do CI (intervalo de confiança). Vamos começar calculando o valor da borda direita. Selecionamos a célula onde queremos exibir o resultado e realizamos a soma dos resultados obtidos utilizando os operadores “CORAÇÃO" e "NORMAS DE CONFIANÇA”. No nosso caso, a fórmula fica assim: A14+A16. Após digitá-lo, pressione Entrar.
14. Como resultado, o cálculo será realizado e o resultado será exibido imediatamente na célula com a fórmula.
15. Em seguida, de forma semelhante, realizamos o cálculo para obter o valor da borda esquerda do IC. Somente neste caso o valor do resultado “NORMAS DE CONFIANÇA” você não precisa somar, mas subtrair do resultado obtido usando o operador “CORAÇÃO". No nosso caso, a fórmula fica assim: =A16-A14.
16. Depois de pressionar Enter, obteremos o resultado na célula fornecida com a fórmula.

Observação: Nos parágrafos acima, tentamos descrever com o máximo de detalhes possível todas as etapas e cada função utilizada. No entanto, todas as fórmulas prescritas podem ser escritas juntas, como parte de uma grande:

• Para determinar a borda direita do IC, a fórmula geral ficará assim:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• Da mesma forma, para a borda esquerda, apenas em vez de um mais, você precisa colocar um menos:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Método 2: operador TRUST.STUDENT

Agora, vamos nos familiarizar com o segundo operador para determinar o intervalo de confiança - ESTUDANTE DE CONFIANÇA. Essa função foi introduzida no programa há relativamente pouco tempo, a partir da versão Excel 2010, e tem como objetivo determinar o IC do conjunto de dados selecionado usando a distribuição de Student, com variância desconhecida.

Fórmula da função ESTUDANTE DE CONFIANÇA como se segue:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Vamos analisar a aplicação deste operador no exemplo da mesma tabela. Só que agora não sabemos o desvio padrão de acordo com as condições do problema.

1. Primeiro, selecione a célula onde planejamos exibir o resultado. Em seguida, clique no ícone “Inserir função” (à esquerda da barra de fórmulas).
2. A já conhecida janela do Assistente de Função será aberta. Escolha uma categoria “Estatístico”, em seguida, na lista de funções propostas, clique no operador “ESTUDANTE DE CONFIANÇA”, então - OK.
3. Na próxima janela, precisamos configurar os argumentos da função:
   • No "Alpha" como no primeiro método, especifique o valor 0,05 (ou “100-95)/100”).
   • Passemos ao argumento. “std_off”. Porque de acordo com as condições do problema, seu valor é desconhecido para nós, precisamos fazer os cálculos apropriados, nos quais o operador “DESV.B”. Clique no botão adicionar função e depois no item “Mais recursos…”.
   • Na próxima janela do Function Wizard, selecione o operador “DESV.B” na categoria “Estatístico” e clique OK.
   • Entramos na janela de configurações dos argumentos da função, cuja fórmula se parece com isso: =СТАНДОТКЛОН.В(число1;число2;...). Como primeiro argumento, especificamos um intervalo que inclui todas as células da coluna “Valor” (sem contar o cabeçalho).
   • Agora você precisa voltar para a janela com os argumentos da função “CONFIANÇA.ESTUDANTE”. Para fazer isso, clique na inscrição do mesmo nome no campo de entrada da fórmula.
   • Agora vamos passar para o último argumento “Size”. Como no primeiro método, aqui você pode simplesmente especificar um intervalo de células ou inserir o operador "VERIFICA". Escolhemos a última opção.
   • Após preencher todos os argumentos, clique no botão OK.
4. A célula selecionada exibirá o valor do intervalo de confiança de acordo com os parâmetros que especificamos.
5. Em seguida, precisamos calcular os valores dos limites do CI. E para isso você precisa obter o valor médio para o intervalo selecionado. Para fazer isso, aplicamos novamente a função “CORAÇÃO". O algoritmo de ações é semelhante ao descrito no primeiro método.
6. Tendo recebido o valor “CORAÇÃO", você pode começar a calcular os limites do IC. As fórmulas em si não são diferentes daquelas usadas com o “NORMAS DE CONFIANÇA”:
   • Borda direita CI=MÉDIA+CONFIANÇA DO ESTUDANTE
   • CI Esquerdo = CONFIANÇA MÉDIA DO ESTUDANTE

Conclusão

O arsenal de ferramentas do Excel é incrivelmente grande e, juntamente com funções comuns, o programa oferece uma ampla variedade de funções especiais que facilitarão muito o trabalho com dados. Talvez as etapas descritas acima possam parecer complicadas para alguns usuários à primeira vista. Mas depois de um estudo detalhado do assunto e da sequência de ações, tudo ficará muito mais fácil.