Roteiro de Aula - AD145

Seção 05 - Microsoft Excel

Antes de começarmos a trabalhar com funções, vale a pena relembrar a aula passada, criando a planilha acima.

1. Para você calcular os juros do mês corrente, multiplique o valor da dívida pela taxa de juros mensais.

2. O pagamento mensal é fixo, logo deve ser repetido o valor da parcela de D8.

3. Calcule os custos do mês, taxa que o banco cobra sobre a parcela mensal paga, multiplicando o valor fixo da parcela pela taxa mensal de custos bancários.

4. Calcule o saldo a pagar, que corresponde ao valor da dívida naquele mês, somando os juros do mês menos o pagamento mensal mais os custos do mês.

5. No mês seguinte, referente à parcela 2, o valor da dívida deve ser igual ao saldo a pagar do mês anterior.

6. Descubra quantos meses são necessários para pagar toda a dívida?

7. Em qual mês do ano ocorre o pagamento da última parcela?

Caro aluno, na aula de hoje estaremos tratando de um assunto muito importante no uso do Excel, o uso de funções matemáticas e estatísticas entre outras, fornecidas pelo Excel.

Vale lembrar que as funções do Excel como por exemplo SOMA(B23:B27), na mais são que fórmulas pré-elaboradas que nos fornecem um determinado resultado. A função SOMA dada como exemplo poderia ser escrita da seguinte maneira: B23+B24+B25+B26+B27, e assim você chegaria ao mesmo resultado.

Precisamos introduzir, rapidamente, alguns conceitos importantes de Estatística, antes de iniciarmos nosso estudo de funções:

População

O termo população refere-se a todos os indivíduos ou a todos os objetos de um grupo em que estamos interessados.

Amostra

Uma amostra é um conjunto de elementos extraídos da população.

Dispersão

É o grau de afastamento de um conjunto de números em relação à sua média.

Desvio-Padrão

Raiz quadrada da variância.

Média

A média é uma medida estatística (número utilizado par resumir as propriedades de um conjunto de números) obtida pelo somatório de todos os números da amostra, divididos pela quantidade de números.

Mediana

A mediana é o ponto, ou elemento, a meio caminho dos dados,  ou seja, metade dos números está acima dela e metade abaixo. A mediana também é chamada de tendência central da distribuição.

Moda

Moda é o valor que ocorre com mais freqüência.

Amplitude

É uma forma de medir a dispersão, bastando para isso calcular a diferença entre o maior e o menor valor.

Distância da Média (Desvio médio)

É o valor absoluto da diferença entre o número e a média dos números.

Desvio médio absoluto

É o resultado da divisão entre o somatório das distâncias médias e a quantidade de números.

Variância

É a média do somatório dos quadrados dos desvios.

Desvio-Padrão

É a raiz quadrada da variância.

Coeficiente de variação

É o resultado da divisão do desvio padrão pela média.

Atividades

O administrador de uma pizzaria resolveu contar durante 9 dias o número de pizzas do tipo calabresa que foram vendidas. Abra o Microsoft Excel e monte em uma pasta de trabalho nova a tabela a seguir:

1. Na coluna Distância da média, calcule o desvio médio, segundo a fórmula apresentada no alto da página.
2. Na célula A13, escreva Média, e na célula B13 calcule a média das vendas.
3. Na célula A14, escreva Moda, e na célula B14 calcule a moda das vendas.
4. Na célula A15, escreva Mediana, e na célula B15, calcule a mediana das vendas.
5. Na célula A16, escreva Desvio médio absoluto, e na célula B16, calcule o desvio médio absoluto das vendas
6. Na célula A17, escreva Maiores Vendas, e na célula B17, calcule o valor máximo das vendas.
7. Na célula A18, escreva Menores Vendas, e na célula B18, calcule o menor valor das vendas.
8. Na célula A19. escreva Desvio-padrão, e na célula B19, calcule o desvio padrão das vendas.
9. Na célula A20, escreva Variância, e na célula A20, calcule a variância.
10. Renomeie sua planilha de Plan1 para Estudo de Funções Estatísticas
11. Salve sua salve a pasta de trabalhos do Excel com o nome e local a seguir:
    • Novo Nome: ADM-Seu Nome-E0x   (onde x é o número do exercício)
    • Novo Local: Pasta Meus Documentos

Nesta próxima atividade estaremos utilizando a seguinte planilha:

1. A partir da célula A11, calcule:
   
   • a taxa de evasão por classe
   • a taxa de evasão total
   • a taxa de aprovação por classe
   • a taxa de aprovação geral
   • a taxa de recuperação por classe
   • a taxa de recuperação geral
   • a taxa de reprovação na recuperação geral
   • a taxa de aprovação sem a recuperação
   • a taxa de retidos sem a recuperação

Para podermos calcular uma taxa, devemos antes calcular alguns coeficientes.

Chamamos coeficientes às razões entre o número de ocorrências e o número total, assim teríamos por exemplo, como coeficente de mortalidade = (número de óbitos)/(população total).

Então, em nossa planilha do colégio Trampolim, teríamos como coeficiente de aprovação geral, o total de alunos aprovados, dividido pelo número total de alunos.

Chamamos de taxas, aos coeficientes multiplicados por uma potência de 10 (10, 100, 1000) para tornar o resultado mais legível. Assim a Taxa de mortalidade = coeficente de mortalidade X 1000, por exemplo.

Dando prosseguimento ao estudo de funções, vamos trabalhar com a seguinte planilha:

Para calcular a média final de cada aluno (coluna G) você deve aplicar a função média na faixa de valores obtidos nos 4 bimestres (colunas C até F); por exemplo a média da aluna Helena poderia ser obtida com a fórmula =média(c9:f9).

Para obtermos a maior nota obtida pelos alunos em cada bimestre (coluna B linha 17), devemos aplicar a função máximo na faixa de valores de cada bimestre, assim teríamos a seguinte fórmula para calcular a maior nota do 3º bimestre, =máximo(e6:e15). Para obter a menor nota neste mesmo bimestre, aplicamos a função mínimo, =mínimo(e6:e15).

Devemos repetir o procedimento acima, para calcular a maior e menor nota geral, e a maior e menor média.

Para verificarmos o resultado do aluno, ou seja se ele foi aprovado ou reprovado, devemos utilizar a função condicional se. Assim, para sabermos se o aluno João Macedo foi aprovado ou reprovado podemos usar a seguinte fórmula: =se(g10>=5;"Aprovado";"Reprovado"). Observe que para ser aprovado o aluno deve tirar nota igual ou superior a 5. A palavra Aprovado deve vir entre aspas para que ela seja escrita na célula caso o valor contido na célula g10 seja verdadeiro, caso seja falso vai ser escrito a palavra Reprovado.

Para calcularmos o conceito obtido pelo aluno, devemos trabalhar com uma função de pesquisa especial do Excel chamada PROCV.

No caso do conceito, queremos substituir o valor numérico obtido pelo aluno com sua média final (células da coluna G), em um valor alfabético (por letras), que estão localizados na faixa de  células H17 até I22. A função PROCV deve procurar o valor da média obtida pelo aluno dentro daquela faixa de valores e retornar a letra correspondente à nota.

Vejamos o caso do aluno Marcelo Lima (linha 12): Sua média foi 3,20 (G12), logo devemos verificar no quadro de conceitos qual o conceito obtido, neste caso D. Para isso devemos utilizar a seguinte fórmula =PROCV(g12;h17:h22;2).