Curso avançado excel 2007

Aula 1 – Apresentação

Bem vindos ao Curso de Excel Avançado. Neste curso vamos estudar os recursos avançados deste gerenciador de planilhas eletrônicas chamado Excel. Para este curso tomaremos como base a versão contida no pacote Microsoft Office 2007, no entanto várias destas ferramentas já estão presentes desde a versão 97.
Este site é um material de apoio para guia-lo durante o curso. Para navegar no curso use o menú lateral e acesse cada aula individualmente. Cada aula contém seu conteúdo específico, com exemplos e sugestões de exercícios.
Este material de apoio não deve ser utilizado separadamente, e depende do complemente da aula presencial com o professor para ter sua efetividade completa. Então preste atenção nas aulas, siga os conteúdos pelo site, faça os exercícios e tire suas dúvidas com o professor em sala.
Você também pode utilizar os formulários de comentários deste site para poder tirar dúvidas a qualquer momento estando em casa ou no trabalho. Sinta-se livre para postar suas dúvidas!

Aula 1.1 – Formatação Condicional

Iniciando então o nosso curso avançado, vamos iniciar nossas aulas com a Formatação Condicional.
A Formatação Condicional é uma forma automatizada de ilustrar melhor nossas planilhas. Com ela podemos alterar cores, adicionar figuras e diversas outras funcionalidades com base no valor de uma célula.
Com esta ferramenta é possível montar tabelas com efeitos antes impossíveis nas versões anteriores do Excel 2007, veja um exemplo na ilustração da figura abaixo:

Introdução a Formatação Condicional

Para utilizar a formatação condicional é necessário ter uma tabela com dados de valores de texto ou numéricos. A partir destes dados podemos então iniciar o uso da ferramenta seguindo os passos:

• Na Aba Início clique em “Formatação Condicional”
• Em seguida clique em “Gerenciador de Regras”

Nesta tela vamos inserir as regras condicionais para cada situação desejada. Podemos criar várias condções diferentes como: maior que, menor que, entre e etc..

Para adicionar uma nova regra basta clicar em “Nova Regra”. Abrirá a seguinte tela:

 

Nesta tela podemos selecionar o tipo de regra para aplicar a formatação condicional. Neste caso estamos formatando valores da célula que são maiores que 201. Para efetivamente criar a formatação para esta condição, clique no botão “Formatar” e defina qual serão as configurações das células com estes valores.

 

Você pode modificar a fonte, cores de borda, preenchimento e diversas outras opções. Ao terminar suas configurações confirme a formatação, e na tela do Gerenciador de Regras crie quantas regras forem necessárias. Depois de todas as regras definidas basta clicar em Aplicar que suas regras farão efeito.

Método Alternativo

Você tem outra maneira de adicionar uma formatação condicional a uma célula ou intervalo. Tendo o intervalo selecionado faça:

• clique na Aba Início
• agora em Formatação Condicional
• selecione uma das opções

 

Remover uma formatação Condicional

Para limpar as células e retirar formatações condicionais previamente adicionadas basta seguir os passos:

• faça uma seleção da célula ou intervalo do qual deseja remover a formatação
• clique na Aba Início
• clique em Formatação Condição condicional e faça conforme a figura

Defina se quer definir remover as formatações do intervalo selecionado ou se deseja limpar as formatações da Planilha Inteira.

Exemplos

Vejamos agora um exemplo de aplicação desta funcionalidade:

• Lista de Pedido com: Produto, quantidade, valor unitário, total.

Hora de Praticar

Exercício 1:

• Para praticar vamos montar a seguinte planilha:
• insira as colunas: aluno, nota 1, nota 2, nota 3, nota 4 e média;
• alimente a planilha com os dados de 10 alunos e suas respectivas notas;
• faça o cálculo automático da média usando fórmula ou função MÉDIA;
• cries regras condicionais para a coluna da média considerando: notas menores que 5, entre 5 e 7, entre 7 e 9 e maiores que 9. Atribua cores diferentes para cada faixa de nota.

Exercício 2:

• Na mesma tabela do exercício anterior faça as modificações seguintes;
• insira novas colunas: aula 1, aula 2, aula 3 e aula 4 e outra chamada frequência
• em cada aluno atribua em quantos períodos ele esteve presente em cada aula (da 1 a 4)
• com base na presença em cada aula calcule a frequência de cada aluno com o resultado variando de 0, para quem faltou todas as aulas, e 100 para quem veio em todas as aulas nos 4 períodos;
• crie uma formatação condicional de barra para a frequência de cada aluno.

Exercício 3:

• utilize outros tipos de formatação condicional nas todas e presenças de modo a experimentas as possibilidades do Excel.
• Sugestões: use ícones nas notas, escala de cor na frequência e etc..

Aula 1.2 – Funções Lógicas

As funções lógicas permitem extender as operações do Excel para um nível bem mais avançado. O domínio desta técnica realmente eleva e muito o nível do uso desta ferramenta.
As funções lógicas disponíveis no Excel 2007 são: SE, E, OU, NÃO, VERDADEIRO e FALSO. Sendo as duas últimas mantidas apenas para haver compatibilidade com versões anteriores do programa.
No entanto é muito importante que antes de se usar as funções lógicas você conheça os operadores lógicos.

• > maior do que
• >= maior ou igual
• < menor do que
• <= menor ou igual
• <> não é igual
• = igual a

Função E

A função lógica “E” faz a comparação entre dois ou mais testes lógicos e retorna VERDADEIRO somente quando o PRIMEIRO e o SEGUNDO teste forem verdade.
Digamos que nós tivéssemos uma tranca com senha de 3 dígitos. A função lógica E só permitiria que o código fosse dado como correto se o dígito um e o dígito dois e o dígito três forem corretos. Sendo assim:

E(Dígito1; Dígito2; Dígito3) -> abre a tranca

A sua forma de uso real é bastante simples, basta aplicar na célula a seguinte fórmula:

 E(lógico1;lógico2; …)

Ex didático:

• E(2+2=4; 3+3=6)  Retorna VERDADEIRO
• E(2+2=4; 3+3=7)  Retorna FALSO

Função OU

A função lógica “OU” faz a comparação entre dois ou mais testes lógicos e retorna VERDADEIRO quando o PRIMEIRO ou o SEGUNDO teste forem verdade
Sendo assim considerando o exemplo da tranca acima a porta seria aberta assim que qualquer um dos 3 dígitos fossem verdadeiros. A aplicação real desta função no Excel é bastante simples, basta selecionar a célula em questão e aplicar da seguinte maneira:

OU(lógico1;lógico2;…)

Ex didático:

• OU(2+2=4; 3+3=6)  Retorna VERDAREIDO
• OU(2+2=4; 3+3=7)  Retorna VERDADEIRO
• OU(2+2=6; 3+3=7)  Retorna FALSO

Função NÃO

Esta é uma função bastante contraditória. Isso porque ela retorna VERDADEIRO toda a vez que um teste lógico contido nele é FALSO. Já quando um resultado lógico dentro de um NÃO retorna em um resultado FALSO o não retorna VERDADEIRO.
Chamamos esta função também de inversão, ou negação
A sua forma de aplicação no Excel é bastante simples, veja:

NÃO(lógico)

Ex didático:

• NÃO(2+2=4)  Retorna FALSO
• NÃO(3+3=7)  Retorna VERDADEIRO

Função SE

A função lógica SE é a estrutura de decisão mais simples que temos no mundo da computação. Esta função permite que tenhamos um teste lógico, e com base no resultado deste teste podemos executar uma ação caso o teste retorne VERDADEIRO, ou outra ação quando o teste retornar FALSO.
Um exemplo prático do cotidiano seria: “Caso haja pão na padaria, compre pão, caso contrário compre sonhos”
A sua forma de aplicação no Excel é bastante simples, veja:

SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)

Ex didático: SE(‘HAVER PÃO’, ‘COMPRE PÃO’, ‘COMPRE BATATAS’)

Hora de Praticar:

Exercício 1:

• Vamos criar uma tabela para um campeonato de português. Neste campeonato cada competidor recebe uma nota de 0 a 10 a cada tentativa.
• Crie uma tabela com as colunas nome, tentativa 1, tentativa 2, resultado;
• Neste campeonato, só é aprovado para as finais os competidores que que tiveram notas maiores ou iguais a 5 nas duas tentativas.
• Para isso na coluna resultado mostre VERDADEIRO somente quando as notas das duas tentativas forem maiores do que 5;

Exercício 2:

• Percebemos nas regras do campeonato do exercício anterior, poucos candidatos foram aprovados, sendo assim precisamos alterar as regras deste campeonato.
• Para isso queremos aprovar qualquer competidor que tenha tido em alguma das tentativas uma nota maior ou igual a 5.
• Então altere a lógica da coluna resultado para que apareça VERDADEIRO quando qualquer uma das tentativas for maior de um competidor for maior ou igual a 5;

Exercício 3:

• Ainda assim, nosso campeonato não ficou justo. Muitos competidores reclamaram das regras, e precisamos alterá-la novamente.
• Precisamos aprovar agora todos os competidores em que a primeira tentativa recebeu nota superior a 4, e a segunda recebeu nota superior ou igual a 5;
• Então altera a lógica da coluna resultado para que aparece VERDADEIRO quando a primeira tentativa for maior que 4, e a segunda for maior ou igual a 5;

Exercício 4:

• Vamos agora usar a tabela de alunos criada na lição anterior e seguir os seguintes passos:
• Adicione a coluna chamada situação a esta planilha de notas dos alunos;
• Faça com que na coluna de situação apareça o texto Aprovado toda a vez que a média deste aluno form maior ou igual a 7, e reprovado quando menor.
• Tente usar nesta coluna a função lógica se para mostrar o texto Aprovado ou Reprovado conforme as condições estipuladas;

Exercício 5:

• Aplique a formatação condicional com cores diferenciadas no campo de situação do aluno no exercício anterior.

Aula 1.3 – Funções Encadeadas

Posted on 9 de julho de 2011

Para ampliar as funcionalidades de cada uma das funções lógicas, o Excel permite que você crie funções encadeadas. Desta forma você pode ter condições que dependam uma das outras criando lógicas bastante complexas.
Veja um exemplo simples:

E( OU(3=3;2=1) ; OU(2=1;5=5) )

Nesta condição acima o primeiro OU retorna VERDADEIRO já que a sua primeira operação lógica é VERDADEIRA, e o segundo OU também visto que sua segunda operação lógica é VERDADEIRA. Sendo assim, a função lógica E retornará VERDADEIRO também, visto que seus dois testes lógicos são VERDADEIROS.

Um Exemplo de Aplicação

Suponha que você tenha uma loja, e queira ampliar suas vendas oferecendo descontos crescentes conforme os valores das compras. Então precisamos oferecer um desconto de 10% para compras de até que R$100,00, descontos de 20% em compras de até R$200,00 e 30% para compras maiores que isso.
Como poderíamos fazer isso? A forma simples de se entender fica assim:

Se valor da compra menor ou igual a 100, dar 10%
Se não, Se valor for menor ou igual a 200, dar 20%
Se não dar 30%

No Excel teremos algo como:

SE( (valor <= 100) ; valor*0,9 ; SE( valor <= 200 ; valor*0,8 ; valor*0,7 ) )

Hora de Praticar:

Exercício 1:

• Digamos que você esteja querendo comprar um cachorrinho, mas não sabe exatamente qual raça comprar. O que você sabe é que deve tomar uma decisão com base em algumas informações lógicas;
• Crie então uma planilha com as colunas: Raça, Altura (cm), Peso (Kg), Valor (R$), Posso Comprar?
• Sabendo que as condições para comprar este cachorro são que o cão não pode ser maior do que 30cm, deve ter menos do que 2Kg e custar menos R$300,00.
• Elabore uma lógica na coluna Posso Comprar de sua tabela de modo que a resposta seja SIM para os cães que estejam dentro das condições acertadas, e NÃO para os demais.

Exercício 2:

• Usando como base a tabela do exercício anterior, altere as condições de compra para: cães com tamanho entre 20cm e 50cm, peso menor que 10Kg e valor entre R$400,00 e R$700,00;

Exercício 3:

• Voltando agora a nossa planilha de notas e frequência de alunos vamos fixar as condições para que ele seja aprovado conforme as regras do MEC;
• Um aluno só pode ser aprovado se sua nota for igual ou maior que 7, e se sua frequência geral for igual ou maior a 75%.
• Altere a fórmula do campo situação para que atenda as novas condições impostas. Faça com que caso seja aprovada o texto seja “Aprovado”, em reprovação por nota mostre “Reprovado” e em casos de reprovação por falta mostre “Reprovado por Falta”;

Exercício 4:

• Vamos alterar novamente nossa planilha de notas dos alunos. Agora vamos considerar também que um aluno tenha a chance de fazer um EXAME. Sendo assim adicione a coluna Exame em sua tabela.
• Agora um aluno reprovado por NOTA tem direito a usar sua nota do EXAME, caso essa nota seja maior ou igual a 7 o aluno é aprovado por exame;
• Sendo assim, caso reprovado por FALTA o aluno terá na situação o texto “Reprovado por Falta”, em caso de aprovado no Exame mostrar “Aprovado no Exame”, e em qualquer tipo de reprovação mostrar apenas “Reprovado”;

Aula 1.4 – Referência Usando Nomes

No Excel é possível determinar nomes para celulas, grupo de celulas, linhas e colunas para facilitar o seu uso em fórmulas.
Veja o exemplo abaixo:

Sendo assim quando quiser realizar uma soma, média ou qualquer operação que envolva este conjunto de células é possível utilizar a seguinte técnica:

=SOMA(coluna_b)

Assim o resultado será a soma de todos os dados desta coluna.
Podemos também nomear um conjunto de células, ou células individuais. Veja o exemplo:

 

Gerenciador de Nomes

No último exemplo deixamos uma linha sem ser selecionada propositalmente. Infelizmente já havíamos definido um nome para a seleção em questão. Caso fosse necessário modificar uma referência de nomes, ou até mesmo excluir uma referência de nome de uma planilha podemos usar o Gerenciador de Nomes.

• vá a Aba Fórmulas
• clique em Gerenciador de Nomes

Aqui você pode selecionar e remover uma referência existente, ou até mesmo editar o intervalo de células que pertencem a esta referência selecionando-a e clicando em Editar:

Para alterar a referência bastou alterar a informação “Refere-se a:” e fazer a seleção do novo intervalo.

Aula 2.1 – Filtrando dados

Quando trabalhamos com uma grande quantidade de dados muitas vezes é necessários classificar e/ou filtrar estes dados para que possamos encontrar uma informação desejada. Para esta finalidade existe uma ferramenta de filtragem de dados que permite que a organização dique simples e dinâmica.
Veja um exemplo abaixo:

Como utilizar a ferramente

A utilização desta ferramenta é bastante simples, basta seguir os passos:

• Faça uma seleção da tabela com os dados a serem filtrados.
• Com as células selecionadas clique na aba Dados
• Em seguida clique em filtros.

Desta forma o filtro é criado automaticamente, e para fazer a filtragem dos dados basta clicar no item em questão conforme figura abaixo:

Hora de Praticar:

Exercício 1:

• Vamos supor que você tenha uma distribuidora de verduras, esta vende alface, batatas, feijão, pepinos e aipo.
• Para controlar suas vendas monte uma tabela de pedidos com as colunas: cliente, estado, cidade, item pedido, situação;
• Para cada venda sua, alimente a planilha com o nome do cliente, cidade, estado e o item pedido. Marque também se este pedido já foi pago ou ainda está pendente.
• Alimente a tabela com os dados de cada pedido com no mínimo 15 vendas.
• Aplicar os filtros nesta tabela para poder encontrar:
• todas as vendas de batatas;
• todas as vendas de um determinado cliente;
• todas as vendas pendentes;
• todas as vendas de batata pagas;
• todas as vendas de feijão pendentes;
• vendas para um determinado estado;
• vendas pagas a uma cidade;
• vendas pagas de aipo, feijão e batatas agrupadas.

Filtro de dados numéricos

A ferramenta de filtros é bastante genérica, e permite automaticamente a filtragem de vários tipos de dados. Com isso veremos agora os filtros numéricos para intervalos de dados e seleções especiais com base nos valores das células.
Para fazer este tipo de classificação faça:

• a seleção de uma coluna com dados numéricos
• na aba início clique em Classificar e Filtrar
• em seguida clique em Filtro

A ferramenta de filtro detecta automaticamente que esta coluna tem dados numéricos e habilita filtros bastante diferenciados. Observe que ao clicar no filtro teremos uma opção exclusiva de “Filtros de Número” observe as opções disponíveis na figura abaixo:

 

 

Hora de Praticar:

Exercício 2:

• Sabendo da existência deste novo tipo de filtro, vamos adicionar agora a nossa planilha de controle de vendas os valores de cada venda;
• Na tabela do exercício anterior crie a coluna Valor antes da coluna situação;
• Alimente a coluna valor com os valores dos pedidos em questão;
• Com base neste novo tipo de filtro faça os seguintes levantamentos:
• os valores pendentes acima de R$200,00
• vendas entre R$100,00 e R$250,00
• todos os valores pagos acima da média
• todas as vendas de batata que ainda não foram pagas.

Filtros de Datas

O Excel permite também que exista os filtros por data. Ele detecta automaticamente que está trabalhando com este tipo de dado e permite filtros bastante úteis. Para ativa-los siga os passos:

• faça a seleção de uma coluna com dados do tipo data
• na aba início clique em Classificar e Filtrar
• em seguida clique em Filtro

Em seguida basta clicar no filtro, e escolher as condições conforme sua necessidade.

Hora de Praticar:

Exercício 4:

• Conforme vamos adquirindo mais conhecimento no Excel podemos ir melhorando o nosso controle empresarial. Agora vamos controlar também as datas em que as vendas aconteceram.
• Na tabela do exercício anterior crie a coluna Data a esquerda da coluna Cliente
• Complete seus pedidos com as datas das negociações.
• Faça os filtros:
• pedidos pendentes anteriores a 30 dias;
• vendas pendentes entre 12/01/11 até hoje.
• todas as vendas para um determinado cliente anteriores a 2003.

Exercício 5:

• Primeiramente coloque formatações condicionais para os valores, e para as situações.
• Vamos utilizar mais alguns tipos de filtros que podem nos ajudar no futuro. Para isso tente utilizar as seguintes opções:
• maiores e menores que a média
• filtro po espaços em branco
• filtrar por cor com base na formatação condicional.
• utilize livremente outras opções de filtro.

Aula 2.2 – Validação de Dados

A validação de dados é muito importante principalmente para evitar o cadastramento de dados iguais de uma ou mais formas diferentes. Por exemplo o cadastro da cidade de Joinville pode ser cadastrada como: Joinville, JVE, JLLE que em teoria tem exatamente o mesmo significado.
Para criar este tipo de validação podemos utilizar o formato de listas suspensas em uma coluna. Isso faz com que o usuário consiga apelas selecionar dados previamente estipulados por você. Veja o exemplo:

Para criar uma validação desta siga os passos:

• após selecionar o campo desejado vá a aba Dados
• clique em Validação de Dados

• na janela acima temos todas as opções de validação de dadas.
• escolha a opção lista
• no campo fonte selecione a lista de campos válidos para esta célula com dos dados previamente estipulados por você.

Na tela anterior podemos também definir uma mensagem de entrada, que funciona como uma mensagem de ajuda. Esta mensagem aparece quando o usuário clica sobre a célula ajudando no preenchimento.

É possível também utilizar uma mensagem de erro, esta mensagem será mostrada toda a vez que o usuário tentar usar um valor diferente do estipulado. Veja o exemplo:

Outras Validações

O Excel permite também o uso de outras validações de dados como: Datas, Números, Frações, Inteiros e outros.

Hora de Praticar

Exercício 1:

• Vamos criar agora uma lista de cadastros para nossos clientes. Sendo assim vamos usar a planilha do exercício anterior criando uma nova planilha chamada CLIENTES.
• Nesta nova planilha crie uma tabela de cadastro com os seguintes dados: nome, cidade, sexo, faixa de idade, peso, altura;
• Aplique a Validação de dados para o campo de cidade determinando 10 cidade brasileiras.
• Aplique a Validação de Dados na coluna Sexo.
• Aplique a Validação de dados para a faixa de idade de: 0 a 10, 11 a 30, 31 a 60 e acima de 60.
• no campo peso permita somente valores inteiros.
• no campo altura permita somente valores maiores que 20cm.

Exercício 2:

• Agora que você tem sua lista de clientes, use-a em sua planilha de vendas para que somente os clientes existentes na planilha de clientes possam ter vendas cadastradas.
• Tente fazer isso usando referência por nomes.

Aula 2.3 – Estrutura de Tópicos

Posted on 8 de julho de 2011

Quando estamos trabalhando com dados financeiros, ou agrupamentos de dados é muito interessante que sejam criadas estruturas de tópicos. As estruturas de tópicos facilitam e automatizam processos de soma, multiplicação e outras operações dentro de grupos de dados. Veja um exemplo:


Existem algumas condições básicas para que a estrutura de tópicos funcionem corretamente. Primeiramente dados a serem organizados em tópicos devem estar em um intervalo, onde cada coluna possua um rótulo na primeira linha. Os dados utilizados devem ser semelhantes. E não podem haver linhas com células vazias no meio da planilha.
Para ordenar um dado em tópicos faça a seleção dos dados em questão e:

• clique na Aba Dados
• na seção Estrutura de Tópicos clique em SubTotal ou Agrupar

Para remover/desagrupar uma seleção basta selecionar as células em questão e em seguida clicar em Desagrupar na Aba Dados, Estrutura de Tópicos.

IMPORTANTE: 

É possível aplicar tópicos e subtópicos, no entanto para isso este procedimento deverá ser manual para cada tópico gerado. E lembre-se a segunda coluna deve estar ordenada em relação a primeira. Para isso você pode usar a ferramenta de Ordenar e Classificar na aba Início para criar as regras personalizadas de classificação.

Hora de Praticar:

Exercício 1:

• Vamos então ver a aplicação das planilhas com estrutura de tópicos. Para isso vamos controlar os gastos de nossa empresa conforme o mês e o tipo de gasto.
• Sendo assim crie uma planilha com os campos: mês, tipo de gasto, valor;
• Usando a ferramenta de estrutura de tópicos agrupe os subtotais dos gastos mensais;

Exercício 2:

• Agora vamos criar mais um tópico dentro desta estrutura.
• Dentro dos meses tente agrupar os gastos por categorias de consumo mostrando a média do período.
•  

  Aula 3.1 – Revisão ao Uso de Fórmulas

  
  

  A principal utilização do Excel ;e para a aplicação de fórmulas ao invés de valores constantes, permitindo que, sempre que ocorrer uma alteração nos valores que influenciam a fórmula, o resultado apresentado seja automaticamente atualizado.
  Sendo assim o Excel suporta como padrão as seguintes operações descritas na lista abaixo:
  

  • + Realiza adição (mais).
  • - Realiza subtração (menos).
  • * Realiza multiplicação (vezes).
  • / Realiza divisão (dividir).
  • ^ Realiza exponenciação (valor elevado).
  • % Realiza a porcentagem (porcento).

  Para ter certeza que todos sabemos utilizar as fórmulas, e principalmente sabemos como organizar, extrair e formatar um cálculo com base em um problema vamos exercitar um pouco.
  Exemplo:
  

  1. Selecione a célula onde pretende efetuar o cálculo.
  2. Digite o sinal de “=”.
  3. Faça um clique na célula B3.
  4. Digite o operador “+”.
  5. Clique na célula C3.
  6. Confirme (Pressione enter).

  Dizemos então que a fórmula possui  endereços relativos, porque ao ser copiada para as linhas seguintes, os seus endereços alteram-se, adaptando-se ás novas coordenadas e fazendo com que o utilizador não tenha de digitar várias fórmulas idênticas.

  Lembrem-se, que podemos fixar o endereço de uma célula em uma fórmula apenas marcando-a com cifrão:

  $B$1

  Hora de Praticar

  Exercício1:

  • Vamos agora criar um gabarito, neste gabarito teremos 3 células.
  • Uma será chamada de Altura, outra de Largura e a terceira de ÁREA.
  • Faça com que no momento em que alguém coloque as medidas de seu terreno, a planilha calcule automaticamente a área do terreno.
  • Utilize a formula ÁREA = ALTURA * LARGURA

  Exercício 2

  • Agora vamos supor que tenha um terreno triangular, e também precisa deste cálculo de área.
  • Crie então uma tabela com uma célula onde digitaremos a altura, outra para a largura e outra para a área. E calcule a área deste triângulo com base na fórmula:
  • ÁREA = (ALTURA * LARGURA)/2

  Exercício 3:

  • Uma outra conversão bastante interessante é a conversão de Graus C em F.
  • Vamos criar uma tabela onde você digitará uma temperatura em F, e o Excel mostrará o resultado em outra coluna em C.
  • Use como base para seu cálculo a fórmula:
  • C=(5*(F-32)/9)

  Exercício 4::

  • Saindo da área técnica e mudando um pouco nossa linha de pensamento vamos pensar em saúde. Existe uma fórmula que com base em sua altura consegue informar seu peso ideal.
  • Para isso crie uma tabela onde você digitará sua altura em metros. E em outra célula o Excel fará o cálculo de seu peso ideal com base na fórmula:
  • PESO= (72.7*ALTURA)-58

  Exercício 5:

  • Agora que já sabemos como utilizar bem as fórmulas, vamos aplicá-la em um novo negócio. Suponha que você tem uma loja de tintas, e deseja automatizar o processo de orçamento com base na área a ser pintada.
  • Para isso cria uma planilha onde você digita a altura e a largura da parede, e o Excel calcula automaticamente a área, os litros de tinta, a quantidade de galões, e o valor em reais das tintas com base em:
  • 1 litro de tinta pinta até 3 metros quatrados
  • 1 galão de tinta tem 3,6 litros
  • o valor de um galão de tinta é de R$12,50

  
  

  
  

  Aula 3.2 – Fórmulas Matemáticas Básicas

  
  

  O Excel conta com inúmeras fórmulas matemáticas, estatísticas, físicas e muitas outras para a automatização de cálculos em suas planilhas.
  Nesta aula vamos estudar algumas destas fórmulas de forma que lhe ajudem na utilização otimizada de planilhas de cálculo.
  Função Soma
  Soma valores ou intervalos de células e retorna seu valor total.
  

  SOMA(núm1;núm2; …)

  Multiplicação:
  Esta função oferece como resultado a multiplicação entre vários valores ou interválos de células:
  

  MULT(núm1;núm2;…)

  Raiz
  Função que retorna a raiz de um número:
  

  RAIZ(núm)

  Resto
  Esta função retorna o resto de uma divisão:
  

  MOD(núm,divisor)

  Inteiro
  Retorna o valor inteiro de um número fracionário.
  

  INT(valor)

  
  

  Hora de praticar

  Exercício 1:
  

  • Com base nas novas funções apresentadas vamos melhorar nosso sistema de orçamento usando novas regras.
  • Primeiramente agora teremos novas colunas nesta tabela, pois agora ao invés de orçar somente galões de 3,6l, também venderemos latadas com 18l de tinta.
  • Sendo assim teremos agora as seguintes colunas: altura, largura, área, litros, latas de 18, galões de 3,6, valor das latas 18, valor dos galões 3,6, valor total
  • Considere as seguintes regras:
  • 1 litro de tinta pinta 3 metros quadrados
  • calcule a quantidade de latas usadas, e a quantidade de galões de forma que o cliente compre sempre a melhor relação de produtos de forma a não levar muita tinta em excesso.
  • Calcule o valor total do orçamento tomando como base que a lata de 18 litros custa R$34,00 e os galões de 3,6 litros custam R$12,50;

  Exercício 2:

  • Vamos transformar agora nossa planilha em um controle de vendas.
  • Adicione então uma coluna de datas, e faça vários orçamentos.
  • Ao final utilize a função soma para retornar o valor estimado de latas orçadas, galões orçados, volume de tinta, valor total das possíveis vendas.

  Aula 3.3 – Consolidando Planilhas

  
  
  A consolidação de dados tem a finalidade de automatizar e facilitar a análise de dados agrupando-os de diversas planilhas em um único resultado.
  Uma aplicação prática bastante interessante seria se você tivesse um resumo mensal de vendas de um determinado produto dividido em várias planilhas. Caso você precise fazer um levantamento dos valores totais seria necessário fazer a soma de cada campo individualmente relacionando-os na planilha de balanço anual todos os campos totais de cada mês.
  Para facilitar este processo o Excel oferece uma ferramenta de consolidação automática.
  Para fazer a consolidação destes dados siga os passos:
  

  • crie duas ou mais planilhas com dados e totais das linhas e colunas.
  • em uma planilha vazia clique na aba Dados e depois em Consolidar
  • selecione agora as os dados das 3 planilhas s serem consolidadas.

  

  • não esqueça de selecionar a função, que neste caso será a de somar todos os dados para a consolidação.

  Nesta tela anterior é possível adicionar e remover referências de diversas planilhas diferentes. Você também pode consolidar dados de uma mesma planilha desde que estas tenham as mesmas formatações.

  Hora de Praticar

  Exercicio 1

  • Vamos consolidar os dados de venda de nossa empresa distribuidora de alimentos com base nas vendas de Janeiro, Fevereiro, Março de 2011. Para isso crie uma planilha para cada mês, e dentro delas alimente com os valores das vendas.
  • Em cada um dos meses faça especifique as vendas dos produtos,: carne, higiene, matinais e padaria. Considere que os produtos foram distribuidos para: Joinville, Jaraguá, Floripa, Blumenau.
  • Sendo assim nas planilhas mensais crie as colunas: região, carnes, higiene, matinais, padaria;
  • E as linhas: Joinville, Jaraguá, Floripa, Blumenau;
  • Alimente as planilhas com os dados das vendas de cada produto para as suas respectivas regiões.
  • Para cada uma das planilhas mensais, faça os totais de vendas por região e por produto.
  • Crie agora uma planilha chamada Primeiro Trimestre e faça a consolidação dos dados com as vendas do primeiro trimestre do ano.

  
  
  
  
  

  Aula 4.1 – Atingindo Metas

  
  

  A ferramenta atingindo metas é uma versão mais simples da ferramenta Solver que veremos mais adiante em nosso curso. Ela permite que você ajuste um resultado conforme um determinado parâmetro máximo, mínimo ou meta a ser atingida.
  Esta ferramenta pode ser bem aplicada em situações onde um resultado deve ser especificado com base em um único valor. Sendo assim ao invés de utilizarmos o método de erro e acerto para chegar a um resultado, deixamos que o Excel automatize este processo.
  Vamos então utilizar a ferramenta atingir metas:
  

  • Vá na Aba Dados,
  • Em Testes de Hipóteses clique em Atingir Meta

  

  • Configure a ferramenta nesta tela:

  

  Onde:

  • Definir célula é a célula a ser controlada
  • Para valor é o valor que se deseja atingir, a meta em sí
  • Alterando célula é a célula que será ajustada conforme a meta a ser atingida.

  Hora de praticar

  Exercício 1:

  • Vamos fazer agora nossa compra do mês, faremos então um levantamento no Excel dos produtos, quantidades e valores dos produtos. Utilizando estes dados vamos fazer a soma do valor total de cada produto e por fim o valor final de toda a nossa compra.
  • Para isso crie uma tabela com as colunas: produto, quantidade, valor unitário e valor total.
  • Ao final da coluna valor total crie a soma de todos os itens comprados.
  • Crie uma outra tabela ao lado desta que simule o parcelamento do valor total das compras em seu cartão de crédito. Para isso faça com que o cálculo use o valor total da compra, divido por um número de parcelas que resulte o valor da parcela mensal.
  • Agora use a ferramenta Atingir Metas para estipular em quantas vezes você deve parcelar esta lista de compras de modo que a parcela fique próxima dos R$50,00.

  Exercício 2:

  • Vamos agora usar uma outra aplicação para a ferramenta de atingir metas. Crie uma planilha semelhante ao exercício anterior, com produtos, quantidade, valor de compra, valor de venda e total. Vamos controlar agora o lucro mediante a venda de produtos que temos em estoque.
  • Alimente sua planilha com os valores de compra, e faça com que os valores de venda sejam calculados com base em uma margem de lucro contida em uma célula especificada por você.
  • Calcule quanto você ganhará vendendo todos os seus produtos conforme as quantidades estipuladas.
  • Agora utilize a ferramenta Atingir Metas para que ela altere a sua margem de lucro para que você atinja um valor de venda desejado.

  
  

  Aula 4.2 – Cenários

  
  

  O Excel possui ferramentas para trabalhar com hipóteses sobre dados. A ferramenta mais interessante para esta finalidade é a ferramenta cenários. Esta ferramenta lhe ajuda a criar diversas hipóteses sobre um determinado tema, e permite que você crie várias situações para uma mesma planilha.
  Com esta ferramenta, como diz o nome podemos criar diversos cenários para uma determinada planilha, simulando assim várias situações hipotéticas. Para utilizar a ferramenta dados crie uma planilha semelhante a da figura abaixo:
  
  Agora na aba Dados, Testes de Hipóteses selecione Gerenciador de Cenários. Abrirá a janela abaixo:
  
  Crie a quantidade de cenários necessários para suas simulações usando o botão “Adicionar”.
  
  
  Nesta tela determine o nome do cenário em questão e selecione qual o intervalo de células que terão seus valores alterados. Caso considere necessário coloque um comentário para este cenário descrevendo que tipo de simulação está sendo feita.
  Clique em OK e:
  
  
  Agora basta determinar os valores para o intervalo de células definidas e clicar em OK.
  
  
  Agora nesta tela podemos editar ou excluir um cenário existente ou até mesmo criar um novo. Para aplicar os valores dos cenários as células determinadas basta selecionar o cenário desejado e clicar em mostrar.
  Para uma determinada situação podemos criar diversos cenários diferentes afim de simular por exemplo resultados futuros.
  

  Hora de Praticar

  Exercício 1:
  

  • Para fixar o uso da ferramenta de cenários vamos fazer a simulação de vendas de um determinado trimestre.
  • Vamos criar então as planilhas de Julho, Agosto e Setembro, e por fim uma chamada de Terceiro Trimestre onde colocaremos os resultados financeiros deste período.
  • Nas planilhas mensais crie as colunas: cidade, despesas, vendas, lucro.
  • Para cada mês defina as despesas e lucros para suas vendas em cada cidade onde você tem franquias.
  • Deixe a planilha de Setembro com os dados de vendas e despesas vazios, afinal não sabemos quando vamos ganhar.
  • Na planilha de resultados do trimestre crie uma tabela com os lucros de cada mês, fazendo assim um levantamento dos lucros mensais para cada cidade.
  • Usando a ferramenta cenários na planilha de Setembro crie pelo menos 5 cenários diferentes de despesas e vendas, então estude qual o impacto deles em seus resultados finais.

  Exercício 2:

  • Vamos agora usar o exercício da lição anterior onde simulamos nossa margem de lucro para as vendas de nosso estoque.
  • Na coluna de quantidade crie 3 cenários diferentes para a quantidade de produtos em estoque.
  • Em seguida use a ferramenta atingir metas para cada cenário de modo a se atingir o lucro esperado.

  
  

  
  

  Aula 4.3 – Tabelas Dinâmicas

  
  

  As tambelas dinâmicas são um recurso poderoso do Excel. Elas permitem que você crie relatórios rápidos com o levantamento de diversos dados diferentes partindo de uma mesta planilha de dados.
  Com as tabelas dinâmicas, podemos resumir ainda mais uma consolidação de dados e dinamizar as análises de dados usando este recurso. Um exemplo prático seria utilizar uma tabela com todos os dados de venda de uma empresa por mês, cliente e valor. Com base nestes dados podemos construir uma tabela dinâmica que nos gere diferentes dados.
  Veja o exemplo da planilha abaixo:
  
  Para criar uma tabela dinâmica com estes dados:
  

  • clique na Aba Inserir
  • clique em Tabela Dinâmica

  Na janela correspondente a configuração desta ferramenta, você será obrigado a selecionar os dados que serão as fontes de sua tabela dinâmica. E terá que especificar um destino onde esta tabela será montada.

  

  Por padrão o Excel cria sempre uma nova planilha para a geração destes dados. E nós vamos manter este método.

  Feito isso você deverá selecionar as especificações de sua tabela conforme o resultado desejado.

   

  O resultado de sua tabela dinâmica seguirá as especificações e características selecionadas conforme as especificações acima. Para ilustrar o seu funcionamento segue o resultado de uma tabela dinâmica abaixo.

  

  É importante lembrar que esta é uma ferramenta de análise de dados. Sua principal vantagem é poder criar diversas planilhas a partir de uma mesma relação de dados sem precisar gastar tempo com formatação e duplicação de dados.
  

  Hora de Praticar

  Exercício 1

  • Chegou a hora de fazer uma avaliação de nossas vendas de 2010. Para isso vamos usar uma planilha que compila todos os dados de venda do ano anterior.
  • Para este controle crie uma planilha chamada 2010 com as colunas: cliente, cidade, mês, produto, valor, situação;
  • Para evitar falhas na digitação faça a validação dos dados para as colunas mês, cidade, produto e situação com listas suspensas. Lembrando que as vendas são feitas para Joinville e Jaraguá, e nossa linha de produtos são: Batatas, Feijão, Arroz e Carne.
  • Crie também a validação do campo valor para receber somente números.
  • Faça a formatação condicional no campo situação para mostrar fundo verde quando estiver marcada como pago, e fundo vermelho quando pendente.
  • Crie uma tabela dinâmica criando diversos relatórios com características diferentes.

  
  

  Aula 4.4 – Gráficos

  
  

  Uma boa forma de analisar os dados no Excel é por meio de gráficos. Na versão 2007 temos a disposição os seguintes tipos de gráficos:
  

  • Gráficos de colunas
  • Gráficos de linha
  • Gráficos de pizza
  • Gráficos de barras
  • Gráficos de área
  • Gráficos de dispersão (XY)
  • Gráficos de ações
  • Gráficos de superfície
  • Gráficos de rosca
  • Gráficos de bolhas
  • Gráficos de radar

  Não estudaremos aqui todos os tipos. No entanto veremos como criar alguns modelos de gráficos.

  Em nosso primeiro exemplo vamos usar a seguinte tabela:

   

  Agora para criar um gráfico de seu desempenho escolhar basta:

  • selecionar os dados desejados
  • clicar na aba inserir
  • clicar em Colunas

  Automaticamente o Excel criará um gráfico com as formatações padrões, conforme exemplo abaixo:

   

  Ao clicar sobre o gráfico o Excel 2007 oferece atalhos rápidos para editar e melhorar nosso gráfico.

   

  Podemos também trocar o estilo de nosso gráfico facilmente. Seja para adicionar títulos, legendas, rótulos. O Excel já traz modelos préfabricados de gráficos que facilitam na hora da formatação de nossos modelos.

  

  Você pode também:

  • alterar cores das linhas
  • valores da escala
  • adicionar rótulos
  • e muito mais.

  Hora de Praticar

  Exercício 1:

  • Vamos então fazer um levantamento de nossos vendedores para cada trimestre do ano.
  • Crie então uma planilha com as colunas: Vendedor, Trimestre 1, Trimestre 2, Trimestre 3, Trimestre 4.
  • Alimente as tabelas com os dados de venda de 4 vendedores e crie gráficos de linhas e colunas para esta série de dados ilustrando assim as informações que tabulamos.

  Exercício 2:

  • Usando a tabela anterior, crie uma coluna chamada total de vendas.
  • Nesta coluna faça a soma de todas as vendas de um vendedor no ano.
  • Faça então um gráfico de pizza que relacione as vendas totais dos Some todas as vendas do ano para cada vendedor e crie um gráfico pizza comparando as vendas de cada vendedor no ano.

  Exercício 3:

  • Sabendo que nem todo o volume de água que sai das estações da tratamento chegam aos hidrômetros, vamos criar uma planilha que relacione o volume de água tratada e o cobrado.
  • Para isso crie uma planilha com as colunas: informação, janeiro, fevereiro … dezembro
  • Crie a linha Volume Tratado, e alimente com o volume tratado para mos meses de janeiro a dezembro de 2010.
  • Agora crie a linha Volume Faturado para os mesmos meses.
  • Faça uma linha chamada Volume Perdido que relacione a quantidade de água que não chega ao seu destino final.
  • Crie então um gráfico que relacione a evolução na qualidade do abastecimento de água, mostrando a relação entre o volume tratado e o desperdiçado.
  • Crie outro relacionando o volume desperdiçado com o faturado.

  Exercício 4:

  • Usando como base o exercício anterior, crie tabelas com os resultados trimestrais dos dados em questão.
  • Gere gráficos de pizza que relacionem as informações sobre o uso da água.

  
  

  Aula 4.4.1 – Gráficos com Metas

  
  

  
  

  
  Para criar um gráfico com indicadores de metas é necessário cria-lo a partir de uma tabela onde estas metas já estejam especificadas. Como exemplo usaremos a seguinte série de dados:
  Para criar o gráfico em questão selecione toda a série de dados e crie um gráfico de colunas conforme visto na lição anterior.
  O resultado será algo parecido com:
  
  Para transformar a última coluna em uma linha de metas, clique sobre ela com o botão esquerdo selecionando-a. Em seguida clique com o botão direito e vá em “Alterar tipo de gráfico da série”
  Feito isso selecione o modelo desejado. Para este exemplo selecionamos o tipo linha que resultou na representação a seguir:
  

   

  Hora de Praticar

  Exercício 1:
  

  • Crie uma planilha com as colunas: mês, rh, produção, administração, TI e média.
  • Preencha as linhas com os dados de despesa de cada setor para os meses de janeiro a junho.
  • Crie um gráfico que aponte as despesas dos setores para cada mês.
  • Adicione ao gráfico a linha com as médias de gastos de cada mês afim de observar o quanto cada setor consumiu além ou abaixo da média de consumo mensal.

   Exercício 2:
  

  • Crie uma planilha com as colunas: vendedor, janeiro, fevereiro, março, abril, maio, junho
  • Alimente as linhas com os nomes de seus vendedores e suas vendas para cada mês.
  • crie a última linha com as metas de venda para cada mês.
  • crie um gráfico que ilustra as vendas mensais de cada vendedor, e sua aproximação perante a meta estipulada.

  ula 5.1 – Gráficos Dinâmicos

  
  

  Os gráficos dinâmicos são parte do conjunto de ferramentas de análise de dados. Com eles é possível criar gráficos onde com filtros dinâmicos é possível criar várias análises diferentes com base em comparações e hipóteses diferentes.
  A criação de gráficos dinâmicos segue uma linha bem semelhante a criação de tabelas dinâmicas. Para sua criação é necessário:
  

  • selecionar uma série de dados
  • clicar na Aba Inserir;
  • clicar na flecha abaixo de Tabela Dinâmica
  • selecionar a opção gráfico dinâmico.

   

  Assim como as tabelas dinâmicas é necessário escolher a fonte dos dados, e em seguida a planilha destino onde estes dados serão inseridos.

  Para configurar seu gráfico espeficifique como serão formatados os seus dados.

   

  E utilise os filtros de dados conforme cada tipo de gráfico que deseja gerar.

   

  Hora de Praticar

  Exercício 1:

  • Crie uma planilha com as colunas: Fornecedor, Serviço, Valor Compra, Valor Venda, Lucro, mês, ano.
  • Valide os campos ano (2008, 2009, 2010), Serviço (instalação, habilitação, manutenção), Fornecedor (criar 3 fornecedores)
  • Faça com que o cálculo do campo Valor de venda seja igual ao valor de compra multiplicado por uma “margem de lucro”
  • Calcule o lucro para cada linha.
  • Alimente a tabela com no mínimo 15 entradas distintas.
  • Crie um gráfico dinâmico e faça diversos filtros explorando as funcionalidades da ferramenta.

  
  
  
  

  
  

  Aula 5.2 – Soma Condicional

  
  

  A soma condicional é uma ferramenta do Excel que aplica a autosoma com base em uma condição de um intervalo.
  Sua forma de uso é:
  

  =SOMASE(intervalo, critérios, [intervalo_soma])

  Sendo assim no exemplo a seguir:
  
  Os valores somados foram todos os que estavam marcados como OK. A fórmula utilizada foi:
  

  =SOMASE(G13:G16;”OK”;F13:F16)

  Hora de Praticar:

  Exercício 1:
  

  • Crie uma planilha com as colunas: Cliente, produto, valor, situação.
  • Faça a validação dos dados e crie 4 clientes, 3 produtos e as situações pendente e pago;
  • Alimente a planilha com os dados de pelo menos 10 vendas.
  • Crie um indicador em outra planilha que mostre o valor total de todos os produtos pagos.
  • Crie indicadores que mostrem a quantidade total de vendas para cada produto, independentemente de estar pago ou não.

  Exercício 2:

  Aula 5.3 – Assistente de Soma Condicional

  
  

  Com este assistente podemos fazer somas condicionais que dependam de duas condições. Para isso primeiro habilite a soma condicional nas Opções de Excel -> Suplementos -> Assistente de Soma Condicional.
  
  
  
  
  
  Após ativado usaremos o assistente de soma condicional na tabela a seguir:
  Ao selecionar o Assistente de soma condicional, selecione a fonte de sua tabela e cria as condições para que só sejam somados os itens faturados e entregues.
  
  Com base nestes dados o valor da soma condicional dos produtos em questão foi representado assim:
  
  Hora de Praticar
  Exercício 1:
  

  • Vamos agora complementar o nosso exercício anterior. Vamos modificar nosso indicador de produtos vendidos, e somar somente as vendas de cada produto que desta vez devem ter sua situação como PAGO.
  • Cópia de Avançcado-Aula5-2A

  Exercício 2:

  • Usando ainda a mesma tabela do exercício anterior, vamos adicionar uma nova coluna chamada NOTA FISCAL.
  • Marque como Emitida todas as vendas que as notas fiscais foram emitidas. E marque como Pendente todas as notas fiscais que ainda não foram emitidas.
  • Crie uma célula que calcule o imposto que deve ser pago com base nas vendas com a nota fiscal marcada como Emitida.
  •  

  
  
  
  

  Aula 6.1 – Solver

  
  

  A ferramenta Solver é uma facilidade oferecida no Excel 2007 como suplemento. Ela serve principalmente para resolver problemas lineares e não-lineares baseados em fórmulas. Ele consegue gerar um resultado para determinada célula baseando-se em uma condição e interação em uma outra célula ou conjunto de informações com base em restrições.
  Basicamente o Solver nos ajuda a resolver problemas que normalmente seriam executados como tentativa e erro para se atingir um resultado. É uma ferramenta ideal para se trabalhar com análise e hipóteses de dados.
  

  Habilitar o Solver

  Antes de utilizar a ferramenta é necessário verificar se ela está instalada em seu pacote office. Os passos são os mesmo para habilitar o Assistente de Soma Condicional. Você pode olhar na aula anterior ou simplesmente seguir os passos:
  

  • clique no botão de iniciar do Excel
  • vá em Opções do Excel
  • Suplementos
  • Gerenciar -> Suplementos do Excel -> Ir
  • Habilite o Solver

  Como Utilizar

  Como forma de exemplo vamos utilizar o Solver em sua fórmula mais simples. Crie uma planilha com os seguintes dados:
  Supondo que despendemos de um determinado valor máximo para as compras, vamos fazer com que o solver altere as quantidades de compras de modo que o valor não ultrapasse R$1500,00.
  É importante lembrar que o Solver somente resolve problemas onde o resultado final esperado é resultante de uma fórmula. Outro ponto importante é que o Solver é capaz de conter e respeitar algumas restrições antes de resolver nosso problema. Acompanhe o exemplo.
  Para aplicar a ferramenta faça:
  

  • Na Aba Dados
  • clique em Solver

  
  

  • selecione a célula com o valor que se quer definir
  • defina quais serão as células que o Solver irá alterar
  • crie as restrições, como mínimos e máximos, neste caso definimos que as quantidades devem ser números superiores a 0, e que um deles deve ter a quantidade mínima de 20 unidades.
  • clique em resolver e veja o resultado.

   

  É possível criar até 100 restrições diretas, e alterar até 200 dados para que a solução com o Solver funcione corretamente.

  Exemplo:

  Para exemplificar vamos usar um exemplo ligeiramente mais complexo. Vamos supor que você tenha uma distribuidora de material de construção e para este mês você precisa comprar os seguintes materiais:

  • barras de ferro, preço de compra R$30,00 o metro, e vendida por R$40,00
  • cimento, preço de compra R$45,00 o saco, e vendido por R$70,00
  • cal, preço de compra R$25,00 o saco, e vendido por R$40,00

  Com base nas encomendas já feitas por seus clientes, você necessita comprar no mínimo as quantidades definidas abaixo de cada material:

  • 20 metros de ferro
  • 30 sacos de cimento
  • 10 sacos de cal

  Use o solver para resolver seu problema e definir a quantidade de cada produto a ser comprado de modo a maximizar seus lucros. No entanto lembre-se que em seu caixa o capital de giro disponível para a compra de materias é de apenas R$7.000,00.

  
  

  Hora de Praticar

  Exercicio 1:

  • Suponha que você tem uma fábrica de tijolos e precise produzir 3000 peças por mês ao menor custo possível, para isso você a disposição os seguintes operários:
  • João que produz 10un/hora a R$50/hora e tem disponível 100 horas por mês
  • Mário que produz 15un/hora a R$65/hora e tem disponível 100 horas por mês
  • Álvaro que produz 20un/hora a R$100/hora e tem disponível 50 horas por mês
  • Com base em suas restrições defina quantas peças cada funcionário deverá produzir de modo que você minimize seus custos com contratados.

  Exercício 2:

  • Sua empresa cresceu, e você adquiriu também uma empresa que fabrica veículos automotores (carros). Sua fábrica faz apenas dois modelos, o ModeloA, e o ModeloB. Sua fábrica conta com 3 linhas de produção a montagem, pintura e carregamento.
  • O ModeloA leva 3 horas para ser montado, 5 horas para ser pintado, e 7 horas para ser carregado.
  • O ModeloB leva 4 horas para ser montado, 4 horas para ser pintado, e 6 horas para ser carregado.
  • Sabendo que:
  • O ModeloA é vendido a R$12.000
  • O ModeloB é vendido a R$14.000
  • A linha de montagem tem disponível 300 horas/mês
  • A linha de pintura tem disponível 400 horas/mês
  • A linha de carregamento tem disponível 500 horas/mês
  • Com base em suas restrições de mão de obra, e considerando os preços de venda de cada modelo defina qual a melhor proporção de produtividade entre os modelos de forma que você consiga maximizar o seu faturamento?

  
  

  Aula 6.2 – Funções Financeiras

  
  

  O Excel 2007 tem suporte a diversas ferramentas e fórmulas financeiras. Estudaremos nesta aula as principais voltadas a cálculos de empréstimo, juros e etc..
  

  TAXA

  Retorna a taxa de juros, por período, de uma anuidade, ou seja: você ficará sabendo a taxa de juros exata do valor de um empréstimo que tem a condição de pagamento com parcelas fixas.
  

  TAXA(nper;pgto;vp;vf;tipo;estimativa)

  NPER é número total de períodos de pagamento.
  Pgto é o pagamento feito em cada período e não pode mudar durante a vigência. Inclui juros e tributos. Se pgto for omitido, você deverá incluir o argumento VF.
  Vp é o valor presente – o valor total correspondente ao valor atual de uma série de pagamentos futuros.
  Vf é o valor futuro, ou seja, o saldo, que você deseja obter depois do último pagamento. Neste caso, se VF for omitido, será considerado “0″.
  Tipo é o número 0 ou 1 e indica as datas de vencimento.
  

  VF

  Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.
  

  =VF(taxa;nper;pgto;vp;tipo)

  NPER é número total de períodos de pagamento.
  Pgto é o pagamento feito em cada período e não pode mudar durante a vigência. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf.
  Vp é o valor presente – o valor total correspondente ao valor atual de uma série de pagamentos futuros.
  Vf é o valor futuro, ou seja, o saldo, que você deseja obter depois do último pagamento. Neste caso, se VF for omitido, será considerado “0″.
  Tipo é o número 0 ou 1 e indica as datas de vencimento.
  

  PGTO

  Calcula o valor das parcelas a serem pagas em um financiamento depois de especificada a taxa de juros e o prazo.
  

  =PGTO(taxa;nper;pgto;vp;tipo)

  NPER é número total de períodos de pagamento.
  Pgto é o pagamento feito em cada período e não pode mudar durante a vigência. Se pgto for omitido, você deverá incluir o argumento VF.
  Vp é o valor presente – o valor total correspondente ao valor atual de uma série de pagamentos futuros.
  Vf é o valor futuro, ou seja, o saldo, que você deseja obter depois do último pagamento. Neste caso, se VF for omitido, será considerado “0″.
  Tipo é o número 0 ou 1 e indica as datas de vencimento.
  EXEMPLOS: Fórmulas Financeiras
  

  Hora de Praticar

  Exercício 1:
  

  • Considerando que sua empresa tomou emprestado o valor de R$200.000,00 para ser pago em 4 anos.
  • A parcela paga mensalmente é de R$5.140,00;
  • Qual a taxa de juros que está sendo cobrada?

  Exercício 2:

  • Suponha que você está fazendo um empréstimo com a taxa mensal de 0,99% ao mês;
  • não foi dado nenhum valor de entrada;
  • o valor foi parcelado em 30 meses pagando R$450,00 por mês;
  • no final deste período qual será o montante total pago?

  Exercício 3:

  • suponha que você quer financiar seu carro. O valor do empréstimo é de R$25.000,00;
  • a taxa mensal é de 1,2% ao mês
  • em quantas vezes você deverá financiar seu carro considerando que o valor máximo da parcela deverá ser de R$600,00?

   Exercício 4:
  

  • Considerando que você tenha R$40.000,00 em caixa, e reserva mensal de R$1000,00 mês.
  • O apartamento que você quer comprar custa R$80.000,00.
  • Considerando que a taxa de seu investimento rende aproximadamente 0,76% ao mês, quantos meses serão necessários de economia para que você consiga o capital para seu investimento?
  • OBS: use a formula do Valor Futuro.

  
  

  Aula 6.3 – Funções Úteis

  
  

  Vamos ver agora algumas funções úteis:
  

  Dias de Trabalho em um Mês

  Para se calcular os dias úteis em um mês de trabalho você pode usar a fórmula:
  

  =DIATRABALHOTOTAL(data_inicial:data_final)

  O resultado será um número correspondente a quantidade de dias úteis.
  
  

  Data após um número de dias úteis

  =DIATRABALHO(data_inicial;qntd_dias)

  Irá retornar um número de série do excel correspondente a data em questão. Somará a dada inicial a quantidade de dias úteis citadas.
  

  Outras:

  =DIA(data)
  =MÊS(data)
  =ANO(data)

  
  
  
  
  

  Aula 7.1 – Tabela do Excel (Recurso Lista)

  
  

  A Tabela do Excel, anteriormente conhecida como recurso lista é uma ferramenta bastante interessante para a análise de uma grande série de dados. Ela cria por padrão colunas de filtro e totais de acordo com os filtros e operações selecionadas. Ela é uma ferramenta que pode ser utilizada como substituta as tabelas dinâmicas principalmente onde queremos os dados em apenas um lugar.
  Para criar uma lista (tabela do Excel) basta fazer a seleção dos seus dados e:
  

  • clicar na Aba Inserir
  • clicar em tabela

  

  • na janela confirme se a fonte das células está de acordo com a sua seleção de dados

   

  • clique em OK

  O resultado será uma planilha como essa:

   

  Nesta tabela já temos criados os filtros e a possiblidade de classificação automaticamente. Sendo assim já é possível filtrar os dados por categoria, clientes e qualquer tipo de dados existentes.

  Um grande diferencial é que podemos também inserir uma linha com os totais. Esta ferramenta faz desta ferramenta um utilitário prático e rápido para análises rápidas de dados. Para adicionar a linha de totais:

  • clique com o botão direito sobre a tabela
  • vá em tabela
  • em seguida clique em “Linha de Totais”

   

  Observe que para cada total é possível utilizar várias operações diferentes para cada coluna.

   

  Hora de Praticar

  Exercício 1:
  

  • Utilize uma das planilhas das aulas anteriores ou crie uma planilha com as colunas: cliente, produto, peso, valor.
  • alimente a planilha com informações de 3 clientes e 2 produtos com seus respectivos pesos e valores pedidos.
  • crie uma tabela do excel e extraia: a soma de todos os produtos vendidos, a soma de todo o peso vendido, a média dos valores de venda para cada cliente e demais informações que desejar.

  
  

  Aula 7.2 – Procura de dados em Matrizes e Listas

  
  

  Todas as planilhas no Excel são consideradas matrizes com um número de linhas e colunas. Tratando-as deste modo o Excel contém ferramentas para a localização de dados em certas matrizes de forma a nos ajudar a encontrar um determinado valor em uma linha ou coluna.
  Busca Vertical Exata
  Para fazer uma busca em uma coluna que retorne um valor específico utilizaremos a fórmula:
  

  =PROCV(procurado;matriz;indice_coluna;FALSO)

  Veja o Exemplo:
  
  Busca Vertical Aproximada
  Para fazer uma busca em uma coluna que retorne um valor aproximado ao desejado utilizaremos a fórmula:
  

  =PROCV(procurado;matriz;indice_coluna;VERDADEIRO)

  Acompanhe o exemplo:
  
  
  Observe que mesmo buscando o valor 2, ele retornou o conceito correspondente a 1 “Ruim”. Isso acontece porque a busca aproximada procura o valor exato ou o maior valor menor ao valor pesquisado.
  Busca Horizontal Exata
  Para fazer uma busca em uma linha que retorne um valor específico utilizaremos a fórmula:
  

  =PROCH(procurado;matriz;indice_coluna;FALSO)

  Veja o Exemplo:
  
  
  
  Busca Horizontal Aproximada
  Para fazer uma busca em uma linha que retorne um valor aproximado ao desejado utilizaremos a fórmula:
  

  =PROCH(procurado;matriz;indice_coluna;VERDADEIRO)

  Veja o Exemplo:
  
  
  O valor resultante será 0,5. Caso a procura fosse por 179, ele resultaria 0,3, isso porque a busca retorna sempre o indice do valor igual ou ao maior valor menor do que a procura.
  Busca em Vertical uma Matriz com CORRES, INDICE E DESLOC
  Podemos, ao definir uma matriz de dados, fazer uma pesquisa de valor em um campo determinado, para isso podemos usar a combinação de comandos CORRESP e ÍNDICE.
  Veja o Exemplo:
  
  
  
  

  Outro Exemplo:

  
  
  O valor retornado é 38. O que aconteceu foi que deslocando os valores de a partir de A1, se procurou número da linha onde havia “Banana”, na coluna 1)
  O Comando DESLOC, faz o deslocamento dos endereços de uma matriz com base no ponto inicial estipulado. Neste caso A1.
  

  Hora de Praticar

  Exercício 1:
  

  • Crie uma tabela de com as colunas: Cliente, Consumo, Fator de Cobrança e Valor da Fatura.
  • Preencha a planilha com os clientes e o seu consumo em m3.
  • Crie uma tabela separada com os índices de Fator de Cobrança seguindo as regras: consumo de 0 a 10m3 o custo é de R$2,00, de 10 a 30m3 o custo é de R$2,50, de 30 a 60m3 o custo é de R$3,00, e acima disso R$4,00.
  • Agora faça com que a sua planilha principal busque os valores desta segunda planilha conforme o consumo de cada cliente, e calcule o valor da fatura conforme o seu índice de Fator de Cobrança.

  Exercício 2:

  • Crie uma planilha com as colunas: operação, jan, fev, mar, abr, mai.
  • preencha a planilha com as linhas correspondentes as operações de compra, venda e lucro para cada mês.
  • usando a fórmula PROCH, escreva em uma célula fora desta tabela o mês específico no qual deseja extrair o valor do lucro. E em outra célula e faça com que  apareça o valor do lucro do mês especificado.

  Exercício 3:
  

  • Crie uma planilha com as colunas: Cliente, Consumo, Faixa Cobrança, Favor de Cobrança, Faixa Correção, Fator de Correção, Fatura.
  • Crie uma validação para os campos Faixa de Cobrança e Faixa de Correção.
  • Para a Faixa de Cobrança estipule 3 faixas, alta, média e baixa. Faça com que ao selecionar a Faixa de Cobrança o campo Fator de Cobrança seja preenchido comforme o valor estipulado na tabela com as fontes da validação.
  • Para a Faixa de Correção estipule 3 faixas, normal, reduzida, social. Onde quando selecionadas preenchem o campo Fator de Correção com 1 para normal, 0,8 para reduzida e 0,3 para a social.
  • Com base no consumo do cliente e os fatores de correção e cobrança selecionados calcule o valor da fatura.

  
  

  Aula 7.3 – Mais sobre Matrizes

  
  

  Vamos utilizar agora fórmulas com matrizes para calcular uma série de dados. Para isso foi construída a seguinte tabela:
  
  Observe que:
  

  • foi feita uma seleção de D2 a D14, e nela foi inserida uma fórmula;
  • a formula foi: SOMA(B2:B14*C2:C14)
  • Porém ao invés de dar um enter para confirmar a fórmula foi dado um CTRL+SHIFT+ENTER

  Assim adicionamos uma fórmula da matriz. Essa formula garante 3 coisas:

  • Segurança, pois não é possível alterar as fórmulas individualmente;
  • Consistência, pois veremos as mesmas fórmulas em todos os campos;
  • Tamanho de arquivo menor: pois ao invés de 13 fórmulas utilizamos apenas uma.

  Transposição de Matrizes

  Muitas vezes quando trabalhamos com séries de dados precisamos inverter matrizes, ou tecnicamente falando TRANSPOR uma matriz. Isso significa trocar suas linhas por suas colunas.

  Veja o Exemplo:

   

  Ao final da operação é interessante que se termine com o comando “CTRL+SHIFT+ENTER” para definir o resultado como uma matriz.

  Constantes de Matrizes

  É possível criar contrantes imutáveis para criação de fórmulas especiais. Para isso podemos usar os exemplos:

  Exemplo 1:

  • selecione uma linha e 5 colunas
  • digite ={1.2.3.4.5}
  • aperte CTRL+SHIFT+ENTER

   

  Exemplo 2:

  • Selecione 3 linhas e 4 colunas
  • digite ={1.2.3.4;5.6.7.8;9.10.11.12}
  • aperte CTRL+SHIFT+ENTER

   

  
  

  Aula 7.4 – Macros

  
  

  
  

  As macros são gravações de processos repetitivos que nos auxiliam na automatização destes. Muitas vezes podemos reduzir drásticamente nosso trabalho de criação e formatação de planilhas apenas com este procedimento.
  Vamos aprender um pouco sobre macros. Primeiramente nem sempre seu Excel bem preparado para usar macros. Para isso faça:
  

  • clique no botão iniciar do Excel
  • clique em Opções do Excel
  • na Aba “Mais Usados”
  • habilite a opção: Mostrar Guia Desenvolvedor na Faixa de Opções

  Para efetuar a gravação de uma macro, vá na Aba Desenvolvedor e clique em Gravar Macro.

   

  Determine o nome e o atalho de sua macro e grave suas ações.

  Hora de Praticar

  Exercício 1:

  • Crie uma macro que simule um formulário na planinha 1 que peça os campos nome, telefone, cpf, email, cidade;
  • Crie uma macro que ao executada crie uma nova linha na planilha 2, e adicione os dados inseridos na planilha 1 e limpando os dados do formulário ao finalizar.

  Exercício 2:

  • Com base na listagem gerada no exercício anterior crie uma macro que quando executada monte um cartão de visita na planilha 3 com formatação e cores desejadas.
  • Fazer com que esta macro seja acionada com o atalho ctrl+i

  
  
  
  

  Aula 8.1 – Funções de Banco de Dados

  
  

  As funções de Banco de dados fazem com que planilhas do Excel se comportem como bancos de dados. Com isso conseguimos fazer algumas operações interessantes.
  Para facilitar nossa vida precisamos ter uma planilha de dados e outra com as restrições. Veja abaixo:
  
  
  

  BDSOMA

  BDSOMA(Base de dados;coluna;critérios)

  Exemplo:
  
  
  
  O resultado é a soma dos lucros gerados por joão que estão pagos.
  BDCONTAR
  

  BDCONTAR(Base de dados;coluna;critérios)

  Exemplo:
  
  
  O resultado é 3, que são o número de operações pagas do joão.
  Outras funções:
  

  • BDMAX
  • BDMIN
  • BDMÉDIA
  • BDMULTIPL
  • e outras em conforme o manual: http://office.microsoft.com/pt-br/excel-help/CH006252820.aspx

  Hora de Praticar

   Exercício 1:
  

  • Extraia a quantidade de vendas pagas acima de R$300,00
  • Informe quantas vendas pendentes existem com valores acima de R$700,00
  • Informe a média dos custos do produto batata
  • informe a quantidade de batata que cada cliente comprou.
  • informe a média de lucro das vendas de feijão
  • extraia a maior compra do cliente mário
  • qual a maior compra paga
  • qual a menor compra pendente

  Exercício 2:

  • Crie validações para as restrições de modo que ao fazer a seleção de vários critérios automaticamente já tenhamos os valores de quantidade de resultados, soma, média, máximo e mínimo dos valores.

  
  

  Aula 8.2 – Importando Dados

  
  

  Importando dados Da Web

  Das diversas formas de se importar dados externos ao Excel, uma das mais interessantes é a importação de tabelas constantes em páginas da web. Para efetuar este tipo de importação faça:
  

  • clique na Aba Dados
  • depois em Da Web
  • selecione o endereço do site onde a sua tabela está contida

   

  Em seguida selecione a tabela que deseja importar clicando na flecha amarela. Feito isso a sua tabela estará importada ao Excel.

  I

   

  mportando arquivos de Texto

  O Excel suporta a importação de arquivos de texto de diversos modelos. Sejam arquivos separados por vírgulas, por tabulações e diversos outros formatos. Para importar um arquivo de texto faça:
  

  • na Aba Dados
  • clique em De Texto
  • selecione o arquivo de texto em questão

   

  Siga as instruções, selecione um destino para a importação e pronto, texto importado.

  Hora de Praticar

  Exercício 1:

  • Importe uma tabela de algum site de sua preferência.
  • na falta de opções baixe a planilha de salários do site: http://exame.abril.com.br/carreira/ferramentas/tabela-de-salarios-rh/

  Exercício 2:

  • importe os dados do arquivo de texto em anexo abaixo:
  • importe.txt

  
  

  Aula 8.3 – Compartilhando Planilhas

  
  

  
  

  Para compartilhar uma pasta de trabalho no Excel é simples, basta adicioná-la em uma pasta compartilhada na rede que automaticamente ela estará disponível a todos. No entanto o Excel 2007 traz algumas facilidades e controles para este compartilhamento.
  
  Para visualizar o compartilhamento da tabela faça:
  

  • vá no menu Revisão
  • clique em Compartilhar Pasta de Trabalho

   

  Nesta janela podemos verificar todos que estão editando o documento ao mesmo tempo, e também podemos habilitar a edição simultânea.

  A Edição Simultânea permite que várias pessoas editem a mesma pasta de planilhas ao mesmo tempo, para isso o Excel automaticamente faz uma mesclagem das alterações de cada usuário para que o conteúdo das planilhas permaneça igual para todos.

  Nesta tela podemos também remover usuários que estão editando a planilha para evitar que pessoas indesejadas alteram o conteúdo da mesma.

  Aqui temos o guia completo sobre as opções de compartilhamento de pasta de trabalho.

  Proteger Planilha

  É possível também proteger uma planilha com senha para evitar a edição total ou parcial de uma planilha em questão. Para isso faça:
  

  • clique na Aba Revisão
  • Em seguida Proteger Planilha

   

  Selecione nesta janela as opções que deseja efetuar o bloqueio e em seguida defina uma senha. Clique em OK e seus bloqueios serão feitos automaticamente.

  Para desfazer estas configurações basta seguir os mesmos passos.