Introdução
O Excel 2013 é um software (programa) capaz de elaborar planilhas de cálculos com fórmulas complexas, bem como representar as informações inseridas através de gráficos, proporcionando uma visão mais ampla dos valores contidos na planilha.
É muito utilizado nos dias atuais, pois é um aplicativo que pode ser utilizado por todos os funcionários de uma empresa, independente do setor de trabalho. Um funcionário do estoque de uma loja, por exemplo, pode perfeitamente utilizar o Excel para controlar a entrada e saída dos produtos de um estoque.
Até mesmo dentro de nossas casas podemos tirar proveito dos recursos do Excel, sendo possível controlar nossas despesas do dia-dia, elaborando um orçamento doméstico ou até um simples controle de conta corrente.
Vamos Iniciar o Excel 2013 para conhecermos suas atividades e principais características do software. Para iniciar o Excel, clique no Botão Iniciar, posicione o mouse ou clique sobre a opção Todos os Programas, clique em Microsoft Office 2013 e por fim selecione Excel 2013.
Em alguns instantes, a janela do Excel 2013 serรก aberta.
Dê um clique em Pasta de trabalho em branco.
Vamos conhecer um pouco a área de trabalho do Excel e algumas funções de suas guias Botão de Arquivo: Através do botão Arquivo é possível criar um novo documento, abrir, salvar, imprimir, compartilhar e ainda opções gerais de trabalho do Excel.
Barra de ferramentas de acesso Rápido: Possui por padrão alguns comandos executados com frequência, como Salvar, Desfazer e Refazer.
Você pode adicionar ou retirar comandos se Barra de Ferramentas de Acesso Rápido, observe a imagem abaixo explicando como fazer.
Barra de TĂtulo: Mostra o nome do arquivo que estĂĄ sendo utilizado seguido pelo nome do programa.
A faixa de Opções é composta por três componentes básicos: 1 - Guias: Cada guia apresenta uma Faixa De Opções. 2 - Grupos: As Faixas de Opções possuem grupos com as ferramentas de acesso rápido aos comandos e operações do programa. 3 - Comandos: Um comando é um botão, uma caixa para inserir informações ou um menu. Para acessar o menu de opções de cada grupo, basta clicar na seta na parte inferior deles.
Barra de Fórmulas: nela podemos inserir fórmulas para realização de cálculos.
Área de Trabalho: através desta área, podemos elaborar nossas planilhas de cálculo, inserir gráficos e figuras.
Barra de Zoom: pode-se arrastar o controle deslizante ou clicar nos botões Ampliar, para modo a ter uma melhor visualização, ou Reduzir o tamanho do conteúdo na folha de cálculo, de modo a visualizar mais conteúdo.
Selecione a célula A1 e digite Orçamento Doméstico. Observe que enquanto você digita o texto, a Barra de Fórmulas vai exibindo o conteúdo da célula A1.
Pressione ENTER para finalizar. Texto digitado na célula A1.
Selecione a célula A3. Com a célula selecionada, digite Renda Familiar e pressione ENTER.
Digite os dados da coluna A, como mostra a imagem abaixo.
Continuando.
Quando uma nova planilha ĂŠ iniciada no Excel todas as colunas possuem a mesma largura. Observe que o texto da cĂŠlula A1 chega atĂŠ a coluna B1.
Posicione a seta do mouse entre as colunas A e B, Quando surgir duas setas, clique e segure o clique arrastando para o lado que desejar.
Para completar a planilha, continue digitando os dados como mostra a figura a seguir.
Salve o arquivo com Nome de Orçamento Doméstico.
Após salvar a planilha, o Excel exibe na Barra de Título o nome da pasta de trabalho.
Apenas na primeira vez que a pasta de trabalho está sendo salva é necessário esse procedimento, a partir de agora basta clicar em Salvar da Barra de Ferramentas de Acesso Rápido ou pressionando as teclas CTRL + B.
Vamos somar a Renda Familiar, e isso requer uma fórmula que utilize os valores do Salário, 13° Salário e Outro, conforme mostra figura abaixo.
Não podemos esquecer que toda e qualquer fórmula inserida no Excel deverá ser iniciada pelo sinal de igual "=".
Selecione a célula B7 e digite a seguinte fórmula =B4+B5+B6.
Para finalizar pressione ENTER. Observe como irá ficar sua planilha após inserir a fórmula.
Agora teríamos que fazer o mesmo cálculo para os outros meses. Existe outra maneira de fazer isso que é copiando a fórmula para as outras células. É um processo mais rápido, além de evitaria o risco de digitação errada da fórmula. Ao copiar as fórmulas, o próprio Excel faz o ajuste necessário das mesmas. Se estivermos copiando uma linha para baixo, todas as linhas que aparecem na fórmula também serão ajustadas, o mesmo ocorre para colunas.
Agora clique e segure no quadrado verde ao lado da célula e puxe ate a célula D7.
Para calcular o total de um jeito mais fácil, utilizaremos uma formula, vamos aprender a utilizar o botão AutoSoma.
Ative a célula B21, agora vá ate a guia FÓRMULAS e botão AutoSoma.
Pressiona agora a tecla ENTER.
Faça a mesma coisa para as cÊlulas C21 e D21.
Vamos colocar os valores das cĂŠlulas B25 e B26, Rendimentos e Gastos.
Selecione a cĂŠlula B25 e digite =b7 seguido de ENTER.
Agora selecione a cĂŠlula B26 e digite =b21 seguido de Enter.
Selecione a célula b27 e digite a fórmula mostrada seguida da tecla ENTER. =B25-B26
Copie a formula para as demais células c25, d25, c26, d26, c27 e d27. Deixei a planilha como abaixo:
Na célula B28 digite: =B27 Célula C28: =C27+B28 Célula D28: =D27+C28
Com auxílio da tecla CTRL, selecione o intervalo de células como mostra a imagem a seguir.
Com o intervalo de célula selecionado clique na setinha do botão Formato de Número da guia Página Inicial, do grupo número.
Em seguida, clique na opção Moeda.
Observe que o formato moeda foi aplicado aos valores da planilha.
Com o Excel também é capaz de alterar a aparência dos textos inseridos em uma planilha através de ferramentas, para formatação do estilo de fonte, tamanho da fonte, negrito, itálico, sublinhado, entre outros.
Selecione a cĂŠlula A1.
Em seguida, selecione o tamanho 18.
Selecione o título e segure a tecla CTRL, e em seguida selecione o intervalo de células B2:D2.
Texto com o estilo Negrito.
Selecione as células que estão em destaque na imagem a seguir e coloque o estilo Negrito. Não esqueça de utilizar a tecla CTRL durante a seleção.
Planilha com nova aparĂŞncia.
Selecione o intervalo de células B2:D2, cujo conteúdo são os meses do ano. Depois, dê um clique no botão Centralizar da guia Página Inicial, do grupo Alinhamento para que o texto fique no centro da coluna.
Observe como ficarão as células centralizadas.
Sabemos que é possível centralizar o conteúdo de uma célula dentro de uma coluna. Vamos ver agora com centralizar um texto em relação à várias colunas.
Com a guia Página Inicial ativada, No grupo alinhamento clique na opção Mesclar e Centralizar.
Com o auxilio da tecla CTRL, selecione os seguintes intervalos de células: B2:D2 e A3:D21, como mostra a imagem a seguir.
Com o intervalo de células selecionado, na guia Página Inicial, no grupo Fonte, clique na seta do botão Bordas. Nas opções que surgirem, escolha Todas as bordas.
Clique em qualquer lugar para retirar a seleção e observe a planilha com as bordas.
Aplique todas as bordas no intervalo de A24:D28, e Centralize o intervalo B24:D24.
Para destacar uma parte da planilha podemos alterar a cor de fundo das células. Este recurso além de deixar a planilha mais atraente, melhora a organização dos dados resultando em uma melhor visualização. Selecione o intervalo de células B2:D2. Na guia Página Inicial, no grupo Fonte, clique no botão drop-down da opção Cor de Preenchimento e escolha a cor Azul,Ênfase 1.
Altere tambÊm a cor da fonte para branco, Observe as mudanças.
Deixe sua planilha de orçamento conforme mostra a seguir. Obs: Aplique o negrito no intervalo B25:D28.
Para exibir os dados na planilha de forma ideal, talvez seja necessário reposicionar o texto em uma célula. Além de alterar o alinhamento do conteúdo da célula, pode-se exibir os dados em um ângulo diferente, girandoos.
Após todas as formatações, sua planilha de orçamento doméstico deve estar com a seguinte aparência:
O diferencial de utilizar o Excel são as fórmulas. Fórmula no Excel é uma expressão que utiliza as operações e funções do Excel para calcular um resultado. De acordo com a documentação da Microsoft fórmulas também podem ser definidas como "equações que efetuam cálculos sobre valores na planilha". As fórmulas no Excel são sempre iniciadas com sinal de igual (=). Por exemplo: =25+32. Com o arquivo Orçamento aberto observe que as planilhas são identificadas por guias localizadas na parte inferior da tela do
Excel. Podemos criar novas planilha na mesma pasta de trabalho.
No Excel podemos trabalhar com mais de uma planilha dentro de uma única pasta de trabalho. Mas imagine que em uma pasta de trabalho temos 20 planilhas. Ficaria difícil identificar cada uma delas, levando-se em conta que o Excel padroniza os nomes das guias (Plan1, Plan2, Plan3, etc).
Planilha após as alterações.
Para que uma planilha fique com uma aparência mais atraente, Vamos utilizar um recurso muito interessante: a inserção de imagens. Um exemplo de uma situação onde podemos utilizar esse recurso é quando uma determinada empresa resolve utilizar seu logotipo nas planilhas de orçamento emitidas aos seus clientes. Selecione a célula A2 da planilha referente aos meses de Janeiro à Março. Em seguida, na guia Inserir clique em Imagens Online.
Na janela que surgir, digite "dinheiro" na caixa do Clip-art e pressione ENTER.
Escolha a imagem que desejar e clique duas vezes sobre
ela.
Diminua sua imagem até ocupar o espaço mostrado a seguir.
Vamos aprender agora com criar um gráfico, forma fácil de representar as informações e dados contidos em uma planilha. É um recurso muito funcional e proporciona uma melhor visualização da informações inseridas. O primeiro passo para criação de um gráfico e a seleção das informações que serão representadas e a escolha do gráfico é feita através da guia Inserir, grupo Gráficos. Nesta guia temos diversos tipos de gráficos.
O primeiro passo para inserir gráfico é selecionar os dados, então selecione o intervalo como mostra abaixo.
Uma novidade do Excel 2013 é a opção "Gráficos Recomendados". Ela mostra para você os melhores gráficos para os dados selecionados. Clique na opção "Gráficos Recomendados" .
Escolha a terceira opção "Colunas Agrupadas" e clique no botão OK. De acordo com a informação selecionada o gráfico será gerado e temos como elementos o eixos Y que representa a faixa de valores, o eixo X com a representação dos nomes referente a cada valor, a Legenda e o título do gráfico.
Vamos alterar nossa legenda, para isso, Clique com botão direto do mouse sobre a legenda do gráfico e em seguida "Selecionar Dados". Irá surgir uma janela para alterarmos os nomes da legenda.
Em "Nome de série" digite "Janeiro" e pressione OK. Agora selecione "Serie 2" e Clique em Editar, digite "Fevereiro" e depois OK. Para "Serie 3" digite "Março" e pressione OK. Para finalizar clique em OK. Vamos alterar o título do gráfico, para isso clique duas vezes sobre o "Titulo do Gráfico" para que possamos editá-lo, agora digite "Gráfico de Orçamento Doméstico".
Formulas são equações que executam cálculos considerando-se valores de planilha. Todas as formulas se inicia com o igual (=). Funções são fórmulas predefinidas que efetuam cálculos usando valores específicos, denominados Argumentos, em uma determinada ordem. Existe fórmulas de cálculos simples ou complexos. Funções de cálculos matemáticos, financeiros e estatísticos. Por exemplo, na função: =SOMA(A1:A10) a função soma todos os valores que estão entre A1 até A10.
Crie um novo arquivo no Excel.
A função =HOJE() retornara o dia de hoje. A função AGORA retorna a data e hora atual do sistema. Selecione a célula A3.
A função DIA retorna apenas o dia de uma data especificada. Selecione a célula C1 e digite 16/06/2014. Na célula C3 digite.
Na célula C1, apague a data digitada. E digite 05/08/2016. Observe que onde inserimos as funções DIA, MÊS e ANO são alterados automaticamente.
Fecha o documento.
Faremos primeiro o cálculo do total de bilheterias por semana utilizando a função SOMA.
Ative a guia FÓRMULAS, botão AUTOSOMA.
Repita a SOMA para a Semana 2, Semana 3.
Faremos agora passo para encontrar a média, se encontra no mesmo botão AUTOSOMA, encontrou a função MÉDIA.
Repita a MÉDIA para a Semana 2 e Semana 3. Salve seu arquivo.
Função SE, iremos analisar a situação das bilheteiras, se a meta foi alcançada ou não. A função SE retorna um valor se uma condição que foi especificada avaliar como verdadeiro e outro valor se for avaliado como falso. Usa-se para efetuar testes condicionais com valores e fórmulas.
Irá surgir a caixa de diálogo "Argumentos da função". Preencha como a imagem abaixo.
A formatação condicional, trata-se de um formato como cor, estilo da fonte, borda ou preenchimento da célula, que o Excel aplica automaticamente, as células se a
condição por verdadeira.
Na guia Página Inicial, clique no botão Formatação Condicional.
Salve o seu arquivo. Abra um novo arquivo do Excel. Insira os dados na planilha (Plan1), como a imagem abaixo. De dois cliques na Plan1 e altere para "Vendas".
Crie uma nova planilha, clicando no botĂŁo "Nova planilha".
Renomeia a planilha para "ComissĂŁo".
A palavra Comissão foi digitada na célula A3. Depois foi feita a mesclagem de células no intervalo A3:A10.
Na guia Comissão, definimos uma tabela de valores que servirá para pesquisar as porcentagens de comissão a serem pagas de acordo com os valores vendidos. Como precisaremos pesquisar valores em outra pasta de trabalho, utilizamos a função PROCV, que efetua pesquisas de valores em tabelas verticais. Ative a planilha Vendas.
Ative a guia FÓRMULAS, depois clique no botão Inserir Função.
Digite os dados na janela de Argumentos da Função e deixe como a imagem abaixo.
Clique no botão OK. Olhe na imagem abaixo como deve ficar sua planilha.
Agora copie a formula para as demais células até a D15.
Utilize o botão AutoSoma para realizar a SOMA dos valores vendidos.
Faça as funções MÉDIA para calcular a célula C23, MÍNIMO para calcular a célula C24 e MÁXIMO para calcular a célula C25. Todas as funções utilizam como referência o intervalo C6:C20.
Ative a AutoSoma.
guia
FÓRMULAS,
e
clique
no
botão
Copie as formulas para a coluna D calcule o Total, Média, Mínimo e Máximo. Formate também a coluna D com formatação de porcentagem. Deixe sua planilha com abaixo.
Agora vamos criar um arquivo utilizando algumas funções do Excel. Para isso, crie um novo arquivo.
Digite os dados com as formatações necessárias, como mostra na imagem a seguir. Obs. As células em destaque são as que foram mescladas. A mesclagem das células foi feita linha por linha.
Para ajustar a largura das colunas das disciplinas (coluna B, C, D, E e F) e deixar todas do mesmo tamanho, como por exemplo: Largura: 12,00, faça como mostra na ilustração a seguir.
Salve o arquivo na Sua Pasta com o nome de Boletim.
Agora vamso acrescentar na planilha estilos, bordas e cor de fundo para melhorar sua aparĂŞncia. Utilizando os recursos aprendidos durante o curso e dicas,deixe sua planilha como a imagem a
seguir.
Dicas para formatação da planilha: Na célula B6 onde se encontra o nome do aluno, além do estilo Negrito, foi alterado o tamanho para 12 e a cor da fonte para Vermelho. Título da planilha: Guia Página Inicial, grupo Estilo, em Estilos de Célula opção Ênfase5. Cor da fonte Branco.
Cores de preenchimento utilizadas na planilha:
Nas áreas em destaque foram utilizada a Borda superior espessa.
Salve as alterações do arquivo. O arquivo que acabamos de criar é um boletim escolar e terá o objetivo de mostra o quadro de avaliação do aluno, indicando se ele estará aprovado ou reprovado na respectiva matéria. Vamos iniciar os cálculos da planilha. Faremos o cálculo do total de pontos por matéria, que será encontrado com a soma dos pontos de cada uma, em todos os meses, de março a novembro. Utilizaremos a função SOMA(Num1;Num2;...), a qual retorna a soma de todos os números na lista de argumentos. Essa função já foi utilizada em exercícios anteriores. Na célula B18, digite: =SOMA(B9:B16). Poderíamos também utilizar o botão AutoSoma.
Utilize o recurso de cópia para copiar a fórmula para as demais células.
O próximo passo será encontrar a média de pontos de cada matéria, ara isso, dividimos o total de pontos por matéria pela quantidade de meses. Utilizaremos a função MÉDIA(Num1;Num2;...). A média é calculada através da soma de um grupo de números e da divisão pela quantidade desses números. Selecione a célula B20. Na guia Fórmulas, no grupoBiblioteca de Funções, clique no botão drop-down ao lado de AutoSoma e escolha a opção Média. Depois selecione o intervalo B9:B16, em seguida, pressione a tecla <Enter>. Realize o cálculo da média conforme a ilustração a seguir.
Não esqueça de utilizar o recurso de cópia para copiar a fórmula para as demais células. Através da função SE, iremos analisar a situação do aluno. Somente é aprovado o aluno que obtiver a média maior ou igual a 7, caso contrário, estará de exame. A função SE retorna um valor se uma condição que foi especificada avaliar como verdadeiro e outro valor se for avaliado como falso. Usa-se para efetuar testes condicionais com valores e fórmulas. Sintaxe: SE(teste_lógico;valor_se_verdadeiro;valor_se_falso) Teste_lógico: é qualquer valor ou expressão que possa ser avaliado como verdadeiro ou falso. Valor_se_verdadeiro: é o valor retornado se o teste lógico for verdadeiro. Valor_se_falso: é o valor retornado se teste lógico for falso. Selecione a célula B22 e na guia Fórmulas, no grupo Biblioteca de Funções, clique no botão Lógica e escolha a opção SE.
Após o clique, será aberta a caixa de diálogo Argumentos da função. No campo SE, em Teste_lógico digite: B20>=7. Em Valor_se_verdadeiro digite a palavra APROVADO e em Valor_se_falso digite EXAME. Depois clique no botão OK. Ao terminar, utilize o recurso da cópia para encontrar a Situação deste aluno também nas outras disciplinas.
Para que entenda melhor, informamos que o valor da média (B20) deverá ser maior ou igual a 7 para que o aluno seja aprovado, do contrário, estará de exame.
Não se preocupe se os seus resultados referente à Média estão com mais casas decimais do que os mostrados no curso. Centralize o intervalo da célula B22 até F22, onde é exibida a situação do aluno. Caso os valores da Média estajam com mais de duas casas decimais, altere o estilo dos números para o formato de Número. Depois salve as alterações do arquivo.
Sua planilha deve estar com a seguinte aparência após as alterações:
Além da formatação utilizada nos exercícios anteriores, o Excel disponibiliza da Formatação Condicional, oou seja, trata-se de um formato como cor, estilo da fonte, borda ou preenchimento da célula, que o Excel aplica automaticamente às células, se uma condição especificada for verdadeira. Vamos utilizar esse tipo d formatação para diferenciar as notas do aluno. Selecione o intervalo de células B9:F16.
Na guia Página Inicial, no grupo Estilo, clique no botão Formatação Condicional.
Em Regras de Primeiros/Últimos, escolha a opção Abaixo da Média...
Selecionando a opção Abaixo da Média, o Excel vai localizar valores abaixo da média no intervalo de células selecionados. Na caixa de diálogo Abaixo da Média, vamos escolher um formato pré-definido pelo Excel para o intervalo selecionado. Então escolha a opção Preenchimento Vermelho Claro e Texto Vermelho Escuro. Em seguida, clique no botão OK.
Observe que todos os valores menores que 7, ou seja, abaixo da média, foram formatados.
Agora vamos utilizar uma formatação mais avançada. Selecione o intervalo B22:F22.
Na guia Página Inicial, no grupo Estilo, clique no botão drop-down ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras...
Logo após, será exibida a caixa de diálogo Gerenciador de Regras de Formatação Condicional.
Deixe selecionada a opção Seleção Atual na caixa de listagem Mostrar regras de formatação para e clique na opção Nova Regra.
Será exibida a caixa de diálogo Nova Regra de Formatação, na qual criaremos todas as formatações.
Agora vamos criar o formato condicional. Em Selecione um Tipo de Regra, clique em Formatar apenas células que contenham.
Em Edite a Descrição da Regra, na caixa de listagem Formatar apenas células com, selecione as opções Texto Específico e contém, respectivamente e, em seguida, digite Exame. Depois, clique no botão Formatar.
Será exibida a caixa de diálogo Formatar Células.
Na guia Fonte, selecione em Estilo da fonte a opção Negrito e aplique a cor vermelha, selecionando a opção Vermelho da caixa Cor.
Ative a guia Preenchimento e clique sobre a cor de plano de fundo Amarelo.
Confirme as alterações, clique sobre o botão OK dacaixa de diálogo Formatar Células.
Observe o formato que acabamos de criar na caixa de Visualização. As células que contém a palavra Exame ficarão com o preenchimento amarelo, o estilo em negrito e a cor da fonte vermelha.
Agora, clique também sobre o botão OK da caixa de diálogo Editar Regra de Formatação para finalizar o processo de formatação.
Após clicar no botão OK, é exibida a caixa de diálogo Gerenciador de Regras de Formatação Condicional. Observe que além da opção Nova Regra, é exibida agora a opção Editar Regra e Excluir Regra.
Pode-se criar mais um formato para o mesmo intervalo selecionado. Através das opções Editar Regra e Excluir Regra podemos alterar ou excluir o formato criado, respectivamente. Para finalizar a formtação condicional, clique no botão OK da caixa de diálogo Gerenciador de Regras de Formatação Condicional.
Planilha após a Formatação Condicional.
Para excluir a Formatação Condicional, na guia Página Inicial, no grupo Estilos, clique no botão drop-down ao lado de Formatação Condicional e, em seguida, escolha uma das opções em Limpar Regras. E no menu, basta escolher a opção desejada.
Qualquer dúvida, solicite a presença do seu orientador. Salve as alterações do arquivo e feche-o em seguida. Uma das grandes características do Excel é permitir a construção de planilhas que têm a capacidade de tomar decisões dentro de parâmetros preestabelecidos, tornando uma planilha “inteligente” através do uso das funções =SE e =PROCV. Crie um novo arquivo. Insira os dados na planilha (Plan1) deixando-a como mostra a imagem a seguir. Altere o noe da guia da planilha para Vendas.
Logo embaixo da planilha digite o seguinte:
Salve o arquivo com o nome de Folha dentro da Sua Pasta. Agora insira os dados na Plan2,como mostra a imagem a seguir. Deixe os valores da coluna D no formato Estilo de Porcentagem. Altere o nome da guia da planilha para ComissĂŁo.
O nome da guia alterado.
Só falta inserir os dados na Plan3. Então digite-os como mostra a seguir. Altere a guia da planilha para Folha.
Salve as alterações do arquivo. Ative a guia Vendas. Vamos completar a planilha com os cálculos. Começaremos pela tabela porcentagem de comissão do funcionário. Vamos efetuar o cálculo de procentagem de comissão de cada vendedor com base no valor vendido.
Na guia Comissão, definimos uma tabela de valores que servirá para pesquisar as porcentagens de comissão a serem pagas de acordo com os valores vendidos. Como precisaremos pesquisar valores em outra pasta de trabalho, utilizaremos a função PROCV, que efetua pesquisas de valores em tabelas verticais. Sua sintaxe é a seguinte: PROCV (valor_procurado;matriz_tabela;num_índice_coluna;procurar_intervalo) Valor_procurado: é o valor a ser localizado na primeira coluna da matriz. Este valor pode ser um valor, uma referência ou um intervalo. Matriz_tabela: é a tabela de informações onde os dados são procurados. Num_índice_coluna: é o número da coluna em matriz_tabela, a partir do qual valor correspondente deve ser retornado. Um num_índice_coluna de 1 retornará o valor na “primeira coluna” em matriz_tabela, 2 retornará o valor na “segunda coluna” em matriz_tabela e assim sucessivamente. Procurar_intervalo: é um valor lógico que especifica se você que que PROCV encontre a correspondência exata ou uma correspondência aproximada. Selecione a célula D6 e na guia Fórmulas, clique sobre o botão Inserir Função.
Na caixa Inserir Função que é exibida, selecione a categoria Pesquisa e Referência. Utilizando a Barra de Rolagem, localize a função PROCV e clique sobre o botão OK.
Após o clique, será aberta a caixa de Argumentos da função, para que possamos fornecer os valores a serem utilizados. Na caixa Valor_procurado, digite o endereço de célula C6, que corresponde ao valor vendido.
Você pode ainda clicar sobre a célula C6, ao invés de digitar. Alterne para a caixa Matriz_tabela, ou seja, posicione o cursor do mouse dentro da caixa Matriz_tabela e ative a gui de planilha Comissão.
Apague o texto Comissão! Inserido automaticamente na caixa Matriz_tabela, após alternar de planilha, para que não ocorra duplicidade de dados. Em seguida, clique no botão do lado direito da caixa Matriz_tabela. Caso a janela Argumentos da função fique na frente dos dados da planilha, arraste-a para baixo. Depois selecione o intervalo de células C4:D10 e pressione a tecla de função <F4> para fixar o endereço da busca. Clique no botão Recolher Caixa de Diálogo ou pressione a tecla <Enter> para que a janela volte ao tamanho normal. Clique na caixa Num_índice_coluna e digite o número 2.
O número 2 foi informado porque desejamos buscar a porcentagem da comissão e ela se encontra na segunda coluna da área selecionada. Clique no botão OK para aplicar a função.
Função aplicada.
Copie a fórmula para as demais células. Não se esqueça de mudar os valores dessa coluna para o formato Estilo de Porcentagem.
Para calcular a comissão do vendedor, na célula E6 digite =C6*D6. Depois copie a fórmula para as demais células.
Após calcular o valo da comissão, sua planilha deve estar com a seguinte aparência:
A fórmula =C6*D6 está calculando o Valor Vendido x Comissão. Uma das características mais importantes é o fato de um programa de planilha eletrônica poder tomar algumas decisões sobre uma determinada condição ou situação. Nessa planilha temos a coluna Bônus, que deverá exibir uma das seguintes mensagens: “resultado do cálculo do bônus” ou “R$ 0,00”, dependendo das vendas efetuadas por um vendedor em relação à previsão de vendas. Para que o vendedor possa ganhar um bônus extra de 12% a mais sobre o valor vendido, ele tem que vender acima de R$ 1.800,00 e o valor total das vendas do mês tem que ser superior a R$ 15.000,00. Em nosso caso, vai existir uma situação onde estarão sendo analisadas mais de uma condição. Para tanto, será abordada a utilização da função SE juntamente com a função E. Função E retorna verdadeiro se todos os argumentos forem verdadeiros; retorna falso se um ou mais argumentos forem falsos. Sintaxe: E(lógico1;lógico2;...) Lógico1;Lógico2;...: são de 1 a 255 condições que você deseja testar e que podem ser verdadeiro ou falso. Antes de calcular o bônus, precisamos saber o total das vendas. Na célula C23, utilize o botão AutoSoma para saber o total do valor vendido.
Vamos fazer o cálculo do bônus, digite na célula F6 a fórmula a seguir. Depois pressione a tecla <Enter>. =SE(E(C6>$B$3;$C$23>15000);C6*12%;”R$0,00”) Observe que na célula F6 foi retornado o texto R$ 0,00, e isto se deu pelo fato da condição não ser verdadeira, pois só receberá bônus o vendedor que possuir vendas superiores a R$ 1.800,00 que não é o caso deste, apesar do total das vendas estar acima de R$ 15.000,00.
Quando o argumento da função SE for verdadeiro, será retornado um número e quando for falso será retornado caracteres de texto, os quais devem ser colocados entre aspas duplas para que o Excel o reconheça. Poderíamos ter colocado o valor 0 (zero) como retorno da condição falsa, mas colocamos dessa maneira na fórmula porque depois no cálculo da função Média, precisaremos calcular a média somente das células que contenha os valores dos bônus. Efetue a cópia da função para as outras células da coluna, observando que quando as duas condições são verdadeiras, o valor do bônus é calculado.
A coluna do bônus está no formato Moeda e com alinhameto à direita. Às vezes é dificil compreender como uma fórmula aninhada calcula o resultado final, porque há vários cálculos intermediários e testes lógicos. Entretanto, utilizando a caixa de diálogo Avaliar Fórmula, podemos avaliar a fórmula na ordem em que ela é calculada. Selecione a célula F6 e na guia Fórmulas, no grupo Auditoria de Fórmulas, clique em Avaliar Fórmula.
Será exibida a caixa de diálogo Avaliar fórmula.
Clique no botão Avaliar para examinar o valor da referência sublinhada. O resultado da avaliação é exibido em itálico. Caso queira ver a avaliação novamente, basta clicar no botão Reiniciar.
Ao término da avaliação, clique no botão Fechar. Explicando as etapas do cálculo.
Qualquer dúvida, solicite a presença do seu orientador. Utilize as funções: MÉDIA para calcular a célula C24, MÍNIMO para calcular a célula C25 e MÁXIMO para calcular a célula C26. Todas as funções utilizam como referênciao intervalo C6:C21.
Faça a cópia da fórmula para as outras células, nas colunas D, E e F. Não esqueça de formatar os valores. Deixe sua planilha como mostra a imagem a seguir.
Agora é possível saver qual é a medida, o menor e o maior valor de vendas, comissão e bônus. Observação: Note que o menor valor mostrado é o de R$ 252,00 e não R$ 0,00 como deveria ser, pois na fórmula que criamos há alguns instantes, no momento em que definimos as condições =SE(E(C6>$B$3;$C$23>15000);C6*12%;”R$0,00”), ela retorna R$ 0,00 em formato de texto, pois o mesmo está entre aspas. Portanto, como as funções: MÉDIA, MÍNIMO E MÁXIMO só reconhecem caracteres numéricos, o valor R$ 0,00 que está em formato de texto, não será mostrado. Porém, se o mesmo for alterado apor qualquer outro caracter numérico, passará a ser exibido. Peça para seu orientador avaliar seu exercício e esclarecer possíveis dúvidas. Salve o arquivo. Abra o arquivo Folha caso o mesmo tenha sido fechado e ative a guia Folha para terminamos os cálculos que estão faltando. Vamos começar pela comissão do vendedor. O cálculo já foi feito, basta apenas buscarmos os valores na planilha Vendas. Selecione a célula E4 na guia Fórmulas, clique sobre o botão Inserir Função.
Na caixa Inserir Função que é exibida, selecione a categoria Pesquisa e Referência. Utilizando a Barra de Rolagem, localize a função PROCV e clique sobre o botão OK.
Após o clique, será aberta a caixa Argumentos da função, para que possamos fornecer os valores a serem utilizados. Na caixa Valor_procurado, digite o endereço de célula A4, que corresponde ao código do vendedor. Posicione o cursor do mouse dentro da caixa Matriz_tabela e ative a guia de planilha Vendas. Apague o texto Vendas! Inserido automaticamente na caixa Matriz_tabela, após alternar planilha, para que não ocorra duplicidade de dados. Em seguida, selecione o intervalo de células A6:F21 e pressione a tecla de função <F4> para fixar o endereço da busca. Clique na caixa Num_índice_coluna e digite o número 5.
O número 5 foi informado porque desejamos buscar o valor da comissão do vendedor e ele se encontra na quinta coluna da área selecionada. Clique no botão OK para aplicação da função.
Planilha com o vaor da comissão do vendedor.
Copie a fórmula para as demais células da coluna. Não se esqueça de formatar os valores para o estilo Moeda.
Agora vamos buscar o valor do bônus na planilha Vendas. Vamos utilizar a mesma função do valor da comissão. A única alteração será na caixa Num_índice_coluna que iremos digitar o número 6.
Copie a fórmula para as outras células. Coloque o alinhamento à direita e formate com o estilo Moeda. Sua planilha deve estar com a seguinte aparência:
Somam-se o Salário base + Comissão + Bônus para encontrar o valor do Salário Bruto. Digite na célula G4 a seguinte fórmula: =D4+E4+F4 e depois copie a fórmula para as demias células.
Salário Bruto calculado.
Salve as alterações da planilha. O desconto do INSS é o produto entre o salário bruto e a alíquota. A alíquota é determinada por três faixas de valores. Como cada faixa é determinada por uma condição, podemos usar a função SE. Como essa função tem apenas duas saídas, para obter os três resultados possíveis, serão necessárias duas funções SE aninhadas. Então vamos criar três faixas da função SE e depois fazemos a junção das mesmas. Primeira faixa: caso o salário bruto seja menor ou igual a R$ 868,29, a alíquota será de 8%. SE(G4<=868,29;8%); Segunda faixa: o salário bruto que não se enquadrar na condição anterior deve ser atestado. Inclui-se, então, uma nova função SE: caso o salário bruto seja menor ou igual a R$ 1.447,14, a alíquota será de 9%. SE(G4<=868,29;8%;SE(G4<=1447,14;9%); Terceira-faixa: o salário bruto que não se enquadra nas condições anteriores terá alíquota de 11%. SE(G4<=868,29;8%;SE(G4<=1447,14;9%;11%))
Montamos a função para calcular a alíquota. Agora vamos multiplicar o valor do salário bruto pela função que calcula a alíquota. Na célula H4, digite a seguinte fórmula para encontrar o valor do INSS. =G4*SE(G4<=868,29;8%;SE(G4<=1447,14;9%;11%))
Não esqueça de copiar a fórmula para as demais células. Sua planilha após o cálculo do INSS.
As alíquotas e faixa salarial citadas nesta planilha são exemplos, os valores e porcentagens são atualizadas constantemente. Para calcular o valor referente às faltas do funcionário, será dividido o Salário Base por 30 (total de dias no mês) para encontrar o valor recebido por dia, em seguida, é multiplicado esse resultado pela quantidade de dias em que o funcionário faltou.
Na célula I4 digit a seguinte fórmula: =(D4/30)*C4.
Depois copie a fórmula para as outras células da coluna e formate-as com estilo Moeda.
Para completar o cálculo da nossa planilha, só falta calacular o Salário Líquido, o qual será igual ao Salário Bruto menos o valor de desconto do INSS e o valor das faltas do funcionário. Na célula J4 digite: =G4-H4-I4.
Em seguida, copie a fórmula para as demais células.
Caso queira visualizar o total e a média dos salários, sem efetuar nenhum cálculo. Basta personalizar a Barra de Status. Para isso, clique com o botão direito do mouse sobre a Barra de Status e no menu que aparece, selecione as opções Média e Soma.
Em seguida, selecione o intervalo de células J4:J19 que são referentes aos salários dos funcionários e observe que na Barra de Status é exibida a média e a soma dos salários.
Qualquer dúvida, solicite a presença do seu orientador. Salve as alterações do arquivo e feche-o em seguida. Exist uma variedade de fuções financeiras disponíveis no Excel. Veremos algumas delas. Vamos criar uma planilha para calcular um investimento a longo prazo. Crie um novo arquivo e digite os dados mostra na imagem a seguir. Faça as formatações necessárias na planilha.
Na célula D5, D13, D14 e D15 formate em Formato Moeda, a célula D6 Formato Porcentagem e a célula D7 e D12 Formato Geral.
Salve o arquivo com o nome de Investimento dentro da Sua Pasta.
Portanto, a primeira célula da planilha a ser calculada é a correspondente ao número de pagamentos, a qual vai informar quantos pagamentos mensais você vai fazer no período indicado. A fórmula vai calcular onúmero de períodos, em anos, multiplicado por “12” (quantidade de meses no ano). Então selecione a célula D12 e digite: =D7*D12.
Vamos calcular os juros acumulados, o qual corresponde ao montante acumulado no período, menos o valor investido. Montante é o valor investido, mais os juros. A fórmula correspondente ao total acumulado menos o total investido. Na célula D13 digite: =D15-D14.
Agora vamos calcular o total investido, o qual será a soma de todos os pagamentos mensais. Então, a fórmula vai calcular o valor mensal multiplicado pelo número de pagamentos. Na célula D14 digite: =D5*D12.
Para o total acumulado, vamos utilizar a função VP (Valor Presente), a qual retorna o valor presente de um investimento. O valor presente éo valor total correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você toma emprestada uma quantia de dinheiro, a quantia do empréstimo é o valor presente para o concessor do empréstimo.
Sintaxe da função: VP(taxa,per,pgto,vf,tipo) Taxa: é a taxa de juros por período. Per: pé o número total de períodos de pagamentos em uma anuidade. Pgto: é o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra taxa ou tarifa. Se pgto for omitido, você deverá incluir o argumento vf. Vf: é o valor futuro, ou seja, o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (zero) e você deverá incluir o argumento pgto. Tipo: é o número 0 (fim do período) ou 1 (início do período) e indica as datas de vencimento dos pagamentos. A função VP deve ser usada na forma negativa, ou seja, deve ser procedida de um sinal menos. A taxa de juros também deve ser precedida de um sinal de menos. Selecione a célula D15 e, em seguida, clique no botão Inserir Função.
Serpa exibida a caixa de diálogo Inserir Função. Clique no botão drop-down de Ou selecione uma categoria e escolha a opção Financeira. Em Selecione uma função, escolha a opção VP. Em seguida clique OK.
Abrirá a caixa de diálogo Argumentos da função. Observe que são exibidos três campos com seus nomes em negrito. São esses campos que você precisa preencher para montar a fórmula. Clique no botão à direita do campo Taxa. A janela se reduz deixando que seja vista a planilha. Clique na célula da taxa de juros (D6) e clique outra vez no botão da janela Argumentos da Função. A janela Argumentos da Função retorna ao tamanho normal. Vamos entrar com o valor do número de pagamentos. Clique no botão à direita do campo Per e selecione a célula D12. Clique novamente no botão da janela Argumentos da Função. Agora, clique no botão à direita do campo Pgto e selecione a célula D5, que é a célula referente ao valor a ser investido. Clique no botão da janela Argumentos da Função para exibir a janela no tamanho normal. Conclua a montage da fórmula clicando no botão OK.
Falta fazermos um pequeno ajuste na fórmula. Então vamos alterá-la. Selecione a célula D15 e depois dê um clique dentro da Barra de Fórmulas.
Inclua um sinal de menos antes de VP e outro antes da taxa de juros (D6). Depois clique no botão Inserir.
Caso tenha alguma dúvida, solicite a ajuda do seu orientador. A planilha está pronta. Então vamos fazer um teste. Suponha que você deseje investir mensalmente R$ 200,00 a uma taxa mensal de 1%, em um período de 7 anos. Digite os valores 200, 1 e 7 nas células D5, D6 e D7, respectivamente. E para o número de pagamentos digite 84 na célula D12. Se necessário, aumente a largura da coluna D.
Formate a cĂŠlula D15 colocando o estilo Negrito; e na cĂŠlula D6, diminua as casas decimais clicando duas vezes no botĂŁo Diminuir Casas Decimais.
Qualquer dúvida solicite a presença do seu orientador. Salve e depois feche o arquivo. Vamos criar uma planilha para calcular juros de financiamentos. Crie um novo arquivo e digite os dados a seguir. Faça as formatações necessárias na planilha.
Salve o arquivo com o nome de Financiamento dentro da Sua Pasta.
Criamos células na planilha para entrada do valor à vista, do valor da entrada, do número de parcelas e da taxa de juros. Vamos definir o funcionamento dessa planilha. Os quatro primeiros valores, citados anteriormente, são digitados pelo usuário. Portanto, a primeira célula da planilha a ser calculada é a correspondente ao valor das parcelas. Utilizaremos para o cálculo a função PGTO, a qual retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante. Sintaxe: PGTO (taxa;nper;vp;vf;tipo) Taxa: é a taxa de juros por período. Nper: é o número total de pagamentos pelo empréstimo. Vp: é o valor presente, ou seja, o valor total presente de uma série de pagamentos futuros. Vf: é o valor futuro, ou seja, o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (zero). Tipo: é o número 0 ou 1 e indica as datas de vencimento. Então selecione a célula C10 e insira a função PGTO. Depois clique no botão OK.
Campos da caixa de diálogo Argumentos da função.
Taxa: selecione a célula C8, que corresponde a taxa de juros. Nper: selecione a célula C7, que corresponde ao número de pagamentos de um empréstimo. Vp: selecione a célula C4, digite o sinal de menos e, em seguida, selecione a célula C6. Vp: é a quantia financiada, que em nosso caso, é o valor de compra à vista menos o que foi pago como entrada. Utilizando os recursos vistos, deixe a caixa de diálogo Argumentos da função como mostra a imagem a seguir. Depois clique no botão OK.
Ao invés de utilizar o mouse para selecionar as células, digite o mouse da célula nos respectivos campos da caixa de diálogo Argumentos da função. A fórmula está pronta. Antes de informarmos qualquer valor, a sua planilha deve estar com a seguinte aparência.
A função PGTO trabalha com um valor constante para o pagamento e para a taxa de juros. Então o valor da parcela fixa. Vamos fazer um teste. Digite 5400, 400, 4 e 3 para a entrada dos dados: À Vista, Entrada, Parcelas e Juros, respectivamente. Formate as células com os estilos necessários e deixe como a imagem abaixo. Após digitar esses valores, observe que o valor da parcela aparece negativo.
Para corrigir isso, selecione a célula C10 e na Barra de Fórmulas introduza um sinal de menos antes de PGTO.
Agora vamos calcular o total financiado que corresponde ao número de parcelas multiplicado pelo valor da parcela. Vamos calcular também o total da compra que é a soma da entrada como o total financiado. Na célula C13 digite: =C7*C10 e na célula C14 digite: =C6+C13.
Sua planilha deve estar com a seguinte aparência:
Caso for utilizar essa planilha em uma loja, por exemplo, não é necessário digitar a taxa de juros com frequência, desde que se crie uma constante para a taxa de juros. Selecione a célula C8 e na guia Fórmulas, no grupo Nomes Definidos, clique em Definir Nome.
Será exibida a caixa de diálogo Novo Nome, na caixa Nome, digite Juros. Os nomes podem ter até 255 caracteres. Na caixa de listagem Escopo, deixe selecionada a opção Pasta de Trabalho. Na caixa Refere-se a, digite =3%. Pode-se digitar uma constante ou uma fórmula, em nosso caso estamos entrando com o valor de uma constante. Para concluir e retornar à planilha, clique em OK.
Ainda com a célula C8 seleciona, digite =Juros. Observe que foi criada uma constante chamada Juros, cujo o valor é de 3%.
Agora não precisamos mais digitar o valor da taxa de juros. Se o valor for alterado, basta atualizá-lo. Utilizamos esta solução quando na nossa planilha não tem muitos cálculos com taxas de juros diferentes. Para atualizar o valor constante, selecione a guia Fórmulas, no grupo Nomes Definidos, clique em Gerenciador de Nomes ou pressione as teclas <Ctrl>+<F3>. Em seguida, selecione a constante criada e clique no botão Editar para realizar a alteração.
Podemos aperfeiçoar nossa planilha. Que tal se o usuário só puder digitar nas células que precisam informar valores? Para isso, vamos proteger algumas partes da planilha para que o usuário não possa alterá-la. Clique no botão Selecionar Tudo para selecionar toda a planilha.
Na guia Página Inicial, no grupo Células, clique em Formatar e, em seguida, clique em Formatar Células... Ou pressione as teclas <Ctrl> + <1>.
Será aberta a caixa de diálogo Formatar Células. Ative a guia Proteção, marque as caixas de seleção Bloqueadas e Ocultas e clique no botão OK.
As opções Bloqueadas tem a função de bloquear a célula contra alterações e a opção Oculta, tem a função de ocultar (esconder) a fórmula na planilha. Agora vamos selecionar as células que ficarão desbloqueadas. Seriam quatro células se tivesse que digitar a taxa de juros, como não vamos digitar, são três células desbloqueadas (valor à vista, valor da entrada e número de parcelas). Com o auxílio da tecla <Ctrl> selecione as células C4, C6 e C7. Abra novamente a caixa de diálogo Formatar Células. Selecione a guia Proteção, desmarque a opção Bloqueadas e clique no botão OK.
Bloquear células e ocultar fórmulas não tem nenhum efeito se a planilha não estiver protegida. Então vamos protegê-la. Para proteger a planilha, na guia Página Inicial, no grupo Células, clique em Formatar e, em seguida, escolha a opção Proteger Planilha...
Na caixa de diálogo Proteger planilha, no campo Senha para desbloquear a planilha, deixe-o em branco. Vamos configurar a senha em branco, pois se digitarmos algo e esquecermos, não conseguiremos desbloquear a planilha. Dentre as opções disponíveis no campo Permitir que todos os usuários desta planilha possam, deixe selecionadas as opções como mostra na imagem a seguir, as quais têm as seguintes funcionalidades: Selecionar células bloqueadas – Desmarcando esta opção, o usuário só poderá navegar nas células que tiverem nas suas propriedades a opção Bloqueada desmarcada. Selecionar células desbloqueadas – Desmarcando esta opção, o usuário estará incapacitado de navegar pela planilha.
Clique no botão OK. A senha é opcional. Contudo, se você não forneceu uma senha, qualquer usuário poderá desproteger a planilha e alterar os elementos protegidos. Quando for montar alguma planilha e protegê-la, certifique-se de escolher uma senha da qual consiga lembrar, pois, caso perca, você não poderá acessar os elementos protegidos na planilha. Após proteger a planilha, você não consegue digitar nela, a não ser nas células em que deixou desbloqueada. Não consegue também visualizar as fórmulas nas células em que foram digitadas. Tente alterar qualquer célula bloqueada. Surgirá uma mensagem informando que a célula está protegida.
Para desproteger a planilha, basta clicar novamente na opção Formatar, após proteger a planilha, aparece a opção Desproteger planilha...
Pressione a tecla Tab várias vezes e observe que o cursor só se movimenta nas células desbloqueadas. Altere o valor da entrada para R$ 1.400,00 e observe a modificação nos valores da planilha.
Qualquer dúvida solicite a presença do seu orientador.
Salve e feche seu arquivo. Nesta lição vamos fazer a impressão do conteúdo de uma planilha. Abra o arquivo Conta_Corrente que está gravado em sua pasta.
Com a planilha aberta, clique na guia Arquivo e na opção Imprimir, e já verá as opções de impressão.
Propriedades de impressĂŁo.
1 - Botão Imprimir: deve ser pressionado para concluir as configurações e enviar a impressão. 2 – Cópias: permite selecionar o número de cópias da impressão. 3 – Impressora: usado para selecionar a impressora disponível e suas propriedades. 4 – Configurações: podemos selecionar qual planilha queremos imprimir, ou seja, a planilha ativa, todas as pastas de trabalho ou definir uma área de impressão. É possível definir qual página será impressa ou intervalo de páginas, a orientação da impressão (Paisagem/horizontal ou Retrato/vertical); o tamanho do papel, margens e alinhamento da planilha.
Vamos manter as configurações padrão, confirme com seu orientador sobre a impressora disponível e pressione o botão Imprimir.
Solicite sua impressão ao orientador e caso tenha alguma dúvida, aproveite para esclarecê-las. Feche o arquivo da sua planilha. Cenários fazem parte de um conjunto de comandos, algumas vezes chamados de ferramentas de análises hipotéticas. Um cenário é um conjunto de valores que o Excel salva e pode substituir automaticamente na sua planilha. Você pode usar cenários para prever o resultado de um modelo de planilha. Você pode criar e salvar diferentes grupos de valores em uma planilha e alternar para qualquer um desses novos cenários para exibir resultados diferentes. Análise hipotética: um processo de alteração dos valores em células para saber como essas alterações afetam o resultado das fórmulas na planilha. Por exemplo, variar a taxa de juros usada em uma tabela de amortização para determinar o valor dos pagamentos. No dia-a-dia, utilizamos o Excel para registrar fatos concretos, por exemplo, folha de pagamento, despesas domésticas, contas a pagar e receber, entre outros. Porém, vamos utilizar o Excel para trabalhar com hipóteses. É possível simular situações, alterando os valores de determinadas células e alcançando resultados diferentes na mesma planilha. Digite os dados como mostra a imagem a seguir e faça as formatações necessárias na planilha.
Digite na célula C4 a seguinte fórmula: =$C$1*B4 e na célula D4: =$C$1+C4. Aplique a fórmula para as demais células.
Utilize para formatar os valores a opção Formato Moeda e para coluna porcentagem utilize a opção Estilo de Porcentagem.
Salve o arquivo com o nome de Cenários dentro da Sua Pasta. O ponto inicial da planilha é o resultado das vendas de produtos no mês de Julho: R$ 200.000,00. Com base neste dado real, podemos trabalhar com diferentes percentuais de vendas e visualizar diferentes cenários. Nesta 1ª simulação, espera-se que as vendas em Dezembro sejam 40% maiores do que as de Julho, ou seja, vamos utilizar os valores atuais da planilha para criar o cenário. Na coluna B, é registrado o possível crescimento (em relação a Julho) das vendas durante o 2º semestre de 2008. A coluna C exibe o resultado em moeda. A coluna D exibe o total das vendas: o valor inicial (Julho) mais o crescimento. Este é o cenário definido como mais provável para a empresa. Através do qual, podemos trabalhar com hipóteses (otimistas e pessimistas), com diferentes resultados utilizando a mesma planilha e trabalhando com o mesmo intervalo de células.
Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, escolha a opção Gerenciador de Cenários.
A caixa de diálogo Gerenciador de cenários será exibida. Primeiro, devemos criar um cenário com os dados originais da planilha. Clique, então, no botão Adicionar.
Na caixa de diálogo Adicionar cenário, digite Original no campo Nome do cenário, o qual é o nome para identificar os dados atuais da planilha. Na caixa Células variáveis, vamos indicar quais células poderão variar-nos diversos cenários que serão criados. Então, selecionaremos o intervalo B4:B8, para isso clique no botão dessa caixa, como mostra na imagem a seguir.
A caixa de diálogo Adicionar cenário – Células variáveis ficarão, temporariamente, oculta. Selecione, então, as células do intervalo B4:B8.
Em seguida, clique no botão que se encontra na parte inferior direita da janela Adicionar cenário – Células variáveis para aumentar a caixa de diálogo.
Com o nome do cenário e as células variáveis definidas, clique no botão OK.
Em Valores de cenário, foi criada uma caixa para hipotéticos, simulando diferentes situações. Como estamos criando um cenário para os dados originais, não altere os valores. Clique no botão Adicionar.
A caixa de diálogo Adicionar cenário é exibida novamente.
O próximo passo será criar os cenários Otimistas e Pessimistas para a empresa. Na caixa Nome do cenário, digite o nome Pessimista para outro cenário. Observe a caixa Células variáveis, o Excel preserva o intervalo de células selecionado anteriormente. Então clique no botão OK.
Estes são os valores informados para o cenário Original.
Vamos trabalhar com a pior hipótese. As vendas no período Agosto até Dezembro ficarão estagnadas. Todos os meses apresentarão o mesmo crescimento (5%) em relação a Julho. Ou seja, as vendas em Agosto crescerão 5% em relação a Julho, Setembro também e assim por diante. Agora, informe os novos valores para a simulação Pessimista, defina todos os valores 0,05. Clique no botão Adicionar para criar o 3º e o último cenário para esta planilha.
Neste último cenário vamos trabalhar com a melhor hipótese para empresa. Espera-se que as vendas em Dezembro sejam 100% maiores do que as de Julho. Digite Otimista para o cenário e clique no botão OK.
Digite os valores para as Células variáveis, como mostra a imagem a seguir e clique no botão OK.
O Gerenciador de cenários é ativado com os 3 cenários criados.
Neste momento, a planilha exibe os dados originais. Vamos alterar entre os cenários que criamos. Para visualizar o pior cenário para as vendas da empresa, selecione a opção Pessimista e clique em Mostrar.
As células variáveis são alteradas e, consequentemente, as colunas Crescimento e Total. Todos os meses apresentarão o mesmo resultado: 5% de crescimento em relação a Julho.
Selecione o cenário Otimista e clique no botão Mostrar. Feche a caixa de diálogo e salve a planilha. Suponha que no cenário Otimista seja bem melhor. Vamos então alterar os valores desse cenário. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, escolha a opção Gerenciador de Cenários.
No Gerenciados de cenários, na caixa Comentário é exibida a data de criação e se for alterado o cenário a data de alteração do item selecionado.
Então, selecione o cenário Otimista e clique no botão Editar.
Na caixa de diálogo Editar cenário, clique no botão OK. Inicialmente, são exibidos os valores atuais do cenário selecionado. Altere-os para 1, 2, 3, 4 e 5, respectivamente, clique no botão OK.
No Gerenciador de cenários, observe que aparece a data de alteração do cenário. Então clique em Mostrar.
Feche o Gerenciador de cenários. Planilha com os dados atualizados.
Vamos aprender a criar um relatório de resumo do cenário. Na guia Dados, clique em Teste de Hipóteses e, em seguida, escolha a opção Gerenciador de Cenários. Na caixa de diálogo cliquem em Resumir.
Será exibida a caixa de diálogo Resumo do cenário. No campo Tipo de relatório, temos duas opções: Resumo do cenário: cria um relatório que lista os seus cenários com seus valores de entrada e células de resultado. Utilize este tipo de relatório apenas quando o seu modelo tiver um conjunto de células variáveis fornecidos por um único usuário. Tabela dinâmica de cenário: cria um relatório que fornece uma análise hipotética das variáveis dos seus cenários. Utilize este tipo de relatório quando o seu modelo tiver vários conjuntos de células. Na janela Resumo do cenário, marque a opção Resumo do cenário. Na caixa Células de resultado, insira as referências para as células que fazem
referência as células cujos valores são alterados pelos cenários. Para isso, selecione o intervalo A3:B8 e depois clique no botão OK.
Observe que é criada uma nova guia de planilha com o nome de Resumo do cenário e é exibido um relatório resumido dos cenários.
Qualquer dúvida solicite a presença do seu orientador. Salve seu arquivo e feche-o. Criando Gráficos Suponhamos que você trabalhe em um departamento de finanças e precise analisar o desempenho da empresa quanto ao crescimento da produção durante os últimos seis meses. Abra um novo documento e digite os dados a seguir. Formate a planilha.
Iremos utilizar os conhecimentos anteriores para aperfeiçoar a análise dessa planilha. Na célula D6 digite Índice de Meta. Altere cor do preenchimento, a cor da fonte e coloque negrito de acordo com a imagem a seguir.
Imagine que a empresa determinou que a produção devesse ter um crescimento mensal de 15%, baseando-se nos valores do ano anterior. E que, na análise, deveria ser exposto se a meta de crescimento foi ou não atingida, para tanto utilizaremos a função SE. Esse tipo de avaliação é comum em qualquer empresa, mesmo sem a utilização do gráfico. Na célula D6 digite: =SE(C4>=B4+B4*15%;”J”;”L”)
Ainda com a célula D6 selecionada, formate a fonte para Wingdings e tamanho 24. Utilize o alinhamento centralizado para a célula. ,
As metas estarão representadas na planilha da seguinte maneira: observe que a letra J transformou-se em um Rostinho feliz. Isso significa que a empresa conseguiu atingir a meta. Caso contrário, Rostinho infeliz, representada pela letra L. Copie a fórmula para as demais células desta linha. Altere a cor da fonte para Vermelho do Rostinho Feliz. Deixe a planilha como mostra na imagem a seguir.
Salve esta pasta de trabalho com o nome de Meta dentro da Sua Pasta. Antes de inserirmos o gráfico, precisaremos adicionar mais alguns dados. Na célula E3, digite Metas/Valores e na célula E4 digite a seguinte fórmula: =B4*15%+B4.
Em seguida, copie-a para as demais células.
Vamos criar um gráfico para a representação dos dados da planilha. Para isto, selecione os intervalos B4:C9 e E4:E9.
Utilize a tecla <Ctrl> para selecionar os intervalos. Na guia Inserir no grupo Gráficos, clique no botão Criar gráfico.
Será exibida a caixa de diálogo Inserir Gráfico.
Escolha o tipo e o subtipo de grรกfico Coluna, como mostra a imagem a seguir. Depois clique no botรฃo OK.
Gráfico inserido na planilha.
Clique sobre a área do gráfico e arraste o gráfico abaixo dos dados da planilha. Vamos formatar o gráfico criado anteriormente com recursos mais avançado, deixando-o com melhor apresentação e muito mais personalizado. Clique sobre o gráfico para selecioná-lo ao qual vamos adicionar um título. Ao selecionar o gráfico são exibidas as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. Na guia Layout, no grupo Títulos de gráfico, clique em Adicionar Elemento Gráfico em seguida, escolha a opção Título do Gráfico, e por fim Acima do Gráfico.
Em seguida, clique com o botão direito do mouse sobre o titulo do gráfico e escolha a opção Editar Texto.
Logo após, digite Análise Semestral, depois clique fora da área do gráfico em qualquer célula da planilha para retirar a seleção do gráfico.
Clique sobre a legenda do gráfico para selecioná-lo e na guia Design, no grupo Dados, clique em Selecionar Dados.
Será exibida a caixa de diálogo Selecionar Fonte de Dados.
1 – Intervalo dos dados do gráfico: exibe o intervalo dos dados da planilha plotada no gráfico.
2 – Entradas de Legenda (Série): lista os nomes das séries de dados existentes. Você pode inserir, editar e remover séries de dados do gráfico sem afetar os dados da planilha através das seguintes opções: • Adicionar: adiciona uma nova série de dados ao gráfico. • Editar: altera a série de dados selecionada. • Remover: remove a série de dados selecionada. 3 – Rótulos do Eixo Horizontal (Categorias): lista os rótulos do eixo horizontal existentes. • Editar: modifica os rótulos horizontais. Na caixa Entradas de Legenda (Série), selecione a Série1, em seguida, clique no botão Editar.
Será aberta a caixa de diálogo Editar Série, com o cursor posicionado na caixa Nome de Série. Vamos definir um nome para série que será Produção 2007. Então clique na célula B3, em seguida, clique no botão OK.
Altere a Série2 e a Série3 para Produção 2013 e Meta/Valores, respectivamente.
Após alterar as entradas da legenda, a caixa Entradas de Legenda (Série) deve estar como a aparência abaixo. Observe que a legenda do gráfico já foi alterada.
Acabamos de alterar a legenda e vamos então aproveitar para alterar os rótulos do eixo horizontal. *Eixo: uma linha que a área de plotagem do gráfico usada como um quadro de referência de medida. Em geral, o eixo y é o eixo vertical e contém dados; o eixo y é o eixo vertical e contém categorias. Clique no botão Editar da caixa Rótulos do Eixo Horizontal (Categorias).
Será exibida a caixa de diálogo Rótulos do Eixo. Clique no botão da caixa Intervalo do rótulo do eixo.
Se for necessário, arraste a caixa de diálogo. Em seguida, selecione o intervalo B3:G3. Depois na caixa de diálogo Rótulos do Eixo minimizada, clique no botão Recolher Caixa de Diálogo para que a janela volte ao tamanho normal. Para finalizar clique no botão OK.
Agora clique no botão OK da caixa de diálogo Selecionar Fonte de Dados. Vamos adicionar no gráfico uma Linha de tendência, que é uma representação gráfica de tendências em séries de dados, como uma linha inclinada para cima para representar um aumento da produção em um período de meses. Selecione a área do gráfico. Na guia Design, clique em Adicionar Elemento Gráfico, em seguida, em Linha de Tendência e selecione a opção Mais Opções de Linha Tendência.
Na caixa de diálogo Adicionar Linha de Tendência, selecione a opção Meta/Valores e depois clique no botão OK.
Logo após, será aberta a caixa de diálogo Formatar Linha de Tendência. Nessa caixa deixe selecionada a opção Opções de Linha de Tendência.
Em Tipo de Tendência/Regresso, selecione a opção Polinomial, a qual aplica uma linha curva para exibir os valores de dados flutuantes, em seguida, insira o número 3 na caixa Ordem para determinar a quantidade de curvas na linha curva.
Agora selecione a opção Cor da Linha da caixa de diálogo Formatar Linha de Tendência. Em seguida, em Cor da Linha, escolha Linha sólida e a cor Roxo.
Para finalizar, clique no botão Fechar da caixa de diálogo. Linha de tendência adicionada ao gráfico.
Selecione o gráfico, clique com o botão direito do mouse sobre o Eixo Vertical (Valor) e, em seguida, escolha a opção Formatar eixo.
Em seguida, é exibida a caixa de diálogo Formatar Eixo.
Na caixa de diálogo Formatar Eixo, deixe selecionada a opção Opções de Eixo, pois vamos definir novos valores para o eixo vertical do gráfico. Em seguida, faça as alterações nas seguintes opções: - Mínimo: selecione a opção Fixo e digite 0. Essa opção determina o número em que o eixo de valor começa. - Máximo: selecione a opção Fixo e digite 60000. Essa opção determina o número em que o eixo de valor termina. - Unidade principal: selecione a opção Fixo e digite 10000. Essa opção determina o intervalo das linhas de grade do gráfico para a unidade principal. - Unidade secundária: selecione a opção Fixo e digite 2000. Essa opção determina o intervalo das linhas de grade do gráfico para a unidade secundária.
Agora selecione a opção Número da caixa de diálogo Formatar Eixo. Em Categorias, selecione também a opção Número. Em seguida, clique no botão Fechar.
O grรกfico com eixo vertical formatado.
Caso queira excluir algo no gráfico, basta clicar com o botão direito do mouse e escolher a opção Excluir. Clique com o botão direito do mouse em uma parte livre da área do gráfico e selecione a opção Formatar área do gráfico.
Será exibida a caixa de diálogo Formatar Área do Gráfico.
Na caixa de diálogo Formatar Área do Gráfico, selecione a opção Preenchimento e o Preenchimento gradiente com a cor predefinida Médio gradiente – Ênfase 3.
Escolha o tipo Linear e o ângulo 90º. Deixe as opções como mostra a imagem a seguir.
Clique no botão Fechar da caixa de diálogo. Seu gráfico deve estar com a seguinte aparência.
Clique com o botão direito do mouse sobre a legenda e escolha a opção Formatar Legenda.
Na caixa de diálogo Formatar Legenda, escolha a opção Preenchimento e o Preenchimento Sólido com a cor Branco.
Clique no botão Fechar da caixa de diálogo. Clique com o botão direito do mouse sobre a legenda e escolha a opção Fonte.
Surgirá a caixa de diálogo Fonte. Com a guia Fonte ativa, escolha a fonte Arial, com Negrito e Itálico e tamanho 9. Em seguida, clique no botão OK.
Gráfico após formatar a legenda.
Vamos formatar as colunas do gráfico. Dê um clique na primeira coluna do gráfico. Em seguida, dê um clique com o botão direito do mouse sobre a coluna e selecione a opção Formatar Série de Dados.
Na caixa de diálogo Formatar Série de Dados, selecione a opção Preenchimento e o Preenchimento sólido Amarelo.
Ainda com a caixa de diálogo Formatar Série de Dados aberta, clique sobre a segunda coluna e escolha Preenchimento sólido com a cor Laranja, Ênfase 6, Mais Escuro 50%. Para terceira coluna, selecione Preenchimento sólido com a cor Laranja, Ênfase 6, Mais Escuro 25%. Em seguida feche a caixa de diálogo Formatar Série de Dados. O gráfico ficará com a seguinte aparência:
Qualquer dúvida solicite a presença do seu orientador. Salve o arquivo e feche-o. Crie um novo arquivo e digite os dados a seguir.
Vamos fazer algumas formatações na tabela. Selecione o intervalo A3:D9 e na guia Início, no grupo Estilo, clique em Formatar como Tabela, em seguida, escolha a opção Estilo de Tabela Média 15.
Na caixa de diálogo Formatar como Tabela clique no botão OK.
Formate o título do gráfico. Selecione a célula A1, coloque o estilo Negrito e altere o tamanho da fonte para 18. Planilha após as formatações.
Vamos terminar a planilha digitando a fĂłrmula. Antes de digitar a fĂłrmula, formate o intervalo D4:D9 para o Estilo de Porcentagem.
Na cĂŠlula D4 digite: =C4 / B4, deixando sua planilha como mostra a imagem a seguir.
Copie a fórmula para as demais células. Salve a pasta de trabalho com o nome de Produção dentro da Sua Pasta. Agora que já sabemos qual foi a porcentagem da colheita por estados. Vamos visualizar essa representação no gráfico que vamos criar. Com o auxílio da tecla <Ctrl>, selecione o intervalo A3:A9 e D3:D9 da planilha. Em seguida, crie um gráfico de Pizza 3D. Depois de criar o gráfico, posicione-o abaixo dos dados da planilha.
Vamos adicionar rótulos aos dados do gráfico. Clique com o botão direito do mouse sobre qualquer fatia do gráfico, em seguida, no Menu de Opções que surge, escolha a opção Adicionar Rótulos de Dados.
Agora vamos formatar o rótulo que acabamos de adicionar. Clique com o botão direito do mouse sobre o rótulo e escolha a opção Fonte.
Na caixa de diálogo Fonte, escolha o estilo da fonte Negrito e a cor da fonte Branca. Depois clique no botão OK.
Formate também a legenda com o estilo da fonte Negrito e a cor da fonte Branca. Escolha a cor da fonte Branca para o título do gráfico. Formate também a área do gráfico. Clique com o botão direito do mouse sobre a área do gráfico e selecione a opção Formatar Área do Gráfico.
Escolha o Preenchimento sólido com a cor Preto e depois feche a caixa de diálogo.
Selecione o gráfico e na guia Formatar, no grupo Estilos de Forma, clique no botão drop-down de Contorno da Forma, em seguida, cliquem em Bordas Suaves e escolha a opção 5 pt.
Seu arquivo após as alterações.
Salve seu arquivo e feche-o em seguida.
Tabela Dinâmica Tabela Dinâmica é uma ferramenta poderosa do Excel que consiste em uma análise completa por parte dos dados inseridos. A tabela dinâmica do Excel é conhecida como: Relatório de Tabela Dinâmica. Esse relatório visto de todos os ângulos, ajuda na tomada de decisão. Crie um novo arquivo e digite os dados a seguir, e formate-os em seguida.
Salve o arquivo com o nome de Vendas dentro da Sua Pasta. Vamos montar uma tabela dinâmica com base numa planilha de vendas. A planilha contém: nome do cliente, data, valor da venda e prazo de pagamento. Cada linha representa uma operação de venda. Portanto, há clientes que se repetem porque fizeram várias compras. Com esses registros de vendas podemos fazer as seguintes perguntas: Qual cliente comprou mais? Em quais datas ocorreram os maiores volumes de vendas? As tabelas dinâmicas podem responder essas perguntas. Então vamos criar essas tabelas. Selecionaremos as células em que vamos analisar, de modo que todos os valores sejam analisados. Selecione o intervalo de células A4:D19. Na guia Inserir, no grupo Tabelas, clique no botão Tabela Dinâmica.
Na caixa de diálogo Criar Tabela Dinâmica, a opção Selecionar uma tabela ou intervalo já esta selecionada. A caixa Tabela/Intervalo mostra o intervalo dos dados selecionados. A opção Nova Planilha também está selecionada. Você pode clicar em Planilha Existente se não quiser colocar o relatório em uma nova planilha. Deixe a caixa de diálogo Criar Tabela Dinâmica como mostra a imagem a seguir e clique no botão OK.
Observe que foi criada uma guia de planilha (Plan2). No lado esquerdo da janela está a Área de layout pronta para receber o relatório da tabela dinâmica, e no outro lado está a Lista de campos da tabela dinâmica. Essa lista mostra os títulos da coluna dos dados de origem. Cada título é um campo: Nome, Data, Valor e Prazo.
Se você clicar fora da área de layout do relatório da tabela dinâmica, a lista desaparece. Para ter a lista de campos de volta, clique dentro da área de layout ou do relatório da tabela dinâmica. Você cria um relatório de tabela dinâmica, movendo qualquer um dos campos para a área de layout do relatório da tabela dinâmica. É possível fazer isso marcando a caixa de seleção ao lado do nome do campo, ou clicando com o botão direito do mouse em um nome de campo e selecionando uma das opções para mover o campo (rótulo).
Vamos agora criar uma tabela dinâmica com o resumo dos valores comprados por clientes e por datas. Sendo assim, marque a caixa de seleção na Lista de campos da tabela dinâmica ao lado do campo Nome, para que o Excel monte, imediatamente, uma coluna com todos os nomes dos clientes da tabela original. Mas cada nome só aparece uma única vez.
Precisamos saber além do nome dos clientes, a data da compra. Então clique com o botão direito do mouse sobre o campo Data e selecione a opção Adicionar aos Rótulos de Coluna.
O Excel cria uma linha com o resumo das datas de vendas, sem repetições.
Agora vamos adicionar os valores ao relatório. Então clique com o botão direito do mouse sobre o campo Valor e selecione a opção Adicionar aos Valores.
A tabela dinâmica exibe um resumo das vendas com os totais por clientes e por datas. Como a tabela é pequena, já visualizamos quais são os clientes que fizeram as maiores compras.
Salve seu arquivo e feche-o
Macros Macro é uma ação ou um conjunto de ações que você pode usar para automatizar tarefas. As macros são gravadas na linguagem de programação Visual Basic for Applications através do Editor do Visual Basic (grava e edita uma macro anexada a uma pasta de trabalho do Microsoft Office Excel). Com o uso de Macros temos um ganho de produtividade considerável, ao evitar que tenhamos que executar manualmente os diversos passos de uma tarefa. Podemos encontrar a opção Macros na guia Exibição, no grupo Macros, no botão Macros.
Agora vamos criar uma macro usando o Gravador de Macros. Crie um novo arquivo e digite os dados abaixo.
Agora vamos gravar a macro, clique em Exibição - Macro - Gravar – Macro.
Será aberta a caixa de diálogo Gravar Macro, preencha como mostra a figura a seguir.
Após clique em OK. Tudo que você fizer daí em diante estará gravando na sua Macro. Formate a célula A3 com: Negrito Cor da Fonte Vermelha Borda Superior Espessa Cor do Preenchimento Azul, Ênfase1
Sua planilha deve esta assim até agora.
Cliquem em Macro – Parar Gravação.
Agora vamos usar o atalho que colocamos na Macro para fazer a mesma formatação nas outras células. Selecione a célula A5 e digite o atalho Ctrl + M.
Faça isso em todas as células.
Salve na Sua Pasta com o nome de Macro, e feche-o em seguida. O recurso Configurar Página possibilita alterar as características da página, margens, cabeçalho e rodapé, a própria planilha, no momento da impressão, permitindo uma personalização do documento. Podemos ainda definir áreas de impressão para que sejam impressos apenas alguns dados contidos numa planilha.
Abra o arquivo com o nome de folha e ative a guia de planilha Folha. Visualize o documento clicando em Visualização de Impressão, na barra de Ferramentas de Acesso Rápido ou pressionando as teclas <Ctrl> + <F2>.
Você também encontra o Visualização de Impressão dentro do botão Office na opção Imprimir. Documento sendo visualizado.
Depois clique no botão Voltar.
Veja que apareceu uma linha vertical pontilhada. Isso significa que será impresso apenas o que estiver ao lado esquerdo da linha e o restante à direita saíra em outra página.
Vamos configurar o documento para que ele seja impresso em apenas uma página. Na guia Layout da Página, cliquem em Configurar Página. Será aberta a caixa de diálogo Configurar Página.
A caixa Configurar Página, possui guias respectivamente: Página, Margens, Cabeçalho/Rodapé e Planilha. Veja características de cada uma: - Página: orienta a posição e as dimensões da planilha no papel, o formato do papel e também a qualidade da impressão. - Margens: orienta as dimensões das margens, cabeçalho, rodapé e centralização da planilha. - Cabeçalho/Rodapé: personaliza o cabeçalho e/ou rodapé. - Planilha: possibilita configurar alguns itens da planilha, para impressão. Na guia Página, no campo Orientação, escolha opção Retrato. No campo Dimensionar, selecione a opção Ajustar para e altere para 65%. Essa opção reduzirá 35% do tamanho do arquivo. Verifique se o tamanho do papel é A4.
A linha pontilhada está em uma nova posição no documento, sendo possível a impressão de todos os dados. Agora vamos configurar as margens. Abra novamente a caixa de diálogo Configurar Página, ative a guia Margens e faça as seguintes alterações: - Superior: 3,0 - Cabeçalho: 1,5 - Esquerda: 2,0 - Direita: 2,0 - Inferior: 2,5 - Rodapé: 1,5 No campo Centralizar na página, selecione a opção Horizontal. Finalize clicando em Visualizar impressão, para visualizar as alterações.
Documento visualizado.
Salve o documento e feche. ParabĂŠns! VocĂŞ concluiu o curso de Excel.