Baixe o app para aproveitar ainda mais
Prévia do material em texto
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. 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. Pressione ENTER para finalizar. 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á até 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 auxílio 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, girando-os. 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çãoe 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 guia FÓRMULAS, e clique no botão AutoSoma. 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 vamos 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, para 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 grupo Biblioteca de Funções, clique no botão dropdown 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_f also) 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 referentes à 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 estejam 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, ou 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 de 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 dropdown 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 da caixa de diálogo Formatar Células. Observe o formatoque 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 formataçã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 nome 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 porcentagem 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_índi ce_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ê quer 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 guia 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 dafunçã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 alinhamento à direita. Às vezes é difícil 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 saber 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 caractere 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. 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. 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 título 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. 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 estar assim até agora. Cliquem em Macro – Parar Gravação. Agora vamos usar o atalho que colocamos na Macro para fazer a mesma formatação nasoutras 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. Fórmulas Fórmulas são os cálculos no Excel que parecem com expressões matemáticas, e que utilizamos apenas operadores matemáticos e referência de células ou valores. Em suma, quando o cálculo possui apenas números e sinais matemáticos, é uma fórmula. Exemplo: de fórmula: =E1+12 =C1*C2 =A1*(3-B1)/(2-F40) =A1+A2 =J17*2%-E1*3%. O uso dos parênteses tem a mesma função no Excel que possui na matemática, que é forçar a resolver uma determinada parte do cálculo antes de outras que teria maior prioridade. Sabemos que a multiplicação e a divisão têm maior prioridade que a adição e a subtração e que, numa expressão, elas seriam resolvidas primeiro. Mas se possuirmos parênteses, a história pode tomar rumos diferentes, verifique os exemplos abaixo: 2+4*2 = 2+8 = 10 (Multiplicação realizada primeiramente, pois tem prioridade). (2+4)*2 = 6*2 = 12 (Adição realizada primeiro, por causa dos parênteses) Como escrever equações complexas com o Excel? Não se preocupe, isso é só um problema de “tradução”, que seria apenas a mudança do modo de escrita conhecido por todos que entendem matemática para o modo que o Excel entende, veja abaixo: 18 – [20 x (3+1) – 2] = (18-(20*(3+1)-2)) / ((2-1)*7) (2-1) x 7 Como pode ver, para separar numerador de denominador, foi necessário usar não somente o símbolo de divisão (a barra /), mas também os parênteses para definir bem quem seria dividido e quem seria divisor. Foi por isso que cercamos completamente o numerador com um par de parênteses e fizemos o mesmo com o denominador. Operador matemático usados nas fórmulas: Operação Matemática Excel Adição A + B = A + B Subtração A – B = A – B Multiplicação A x B = A * B Divisão A ÷ B = A / B Potenciação AB = A ^ B Funções Funções são comandos que existem somente no Excel, para executamos equações matemática complexas, ou equações de comparação, referência, condição, contagem, e até mesmo, operações com texto. Existem várias funções diferentes, para as mais diferentes áreas de utilizações de cálculos, como engenharia, matemática geral e financeira, trigonometria, geometria, estatística, contabilidade, e funções gerais como as que trabalham exclusivamente com hora e data, com texto e com referências condicionais. Basicamente qualquer função do Excel pode ser escrita com a seguinte Sintaxe: = NOME_DA_FUNÇÃO (ARGUMENTO) Onde NOME_DA_FUNÇÃO é o nome da mesma (cada função tem o seu) e os ARGUMENTOS são informações que fazem a função trabalhar corretamente. Algumas funções solicitam um argumento, outras podem solicitar vários argumentos, outras simplesmente requerem os parênteses vazios. Se alguma função necessita de mais de um argumento, eles vêm separados por; (ponto e vírgula) dentro dos parênteses, se no lugar do ; , aparecer um sinal de : (dos pontos), significa que estamos apontando para um intervalo de células (ou seja, C4; C20 é lido como C4 e C20 e a expressão C4:C20 é lido C4 até C20, incluindo tudo que estiver no meio delas). Abaixo uma listagem das mais usadas funções do programa, com suas explicações e, é claro, os exemplos de portanto, se for necessário criar um cálculo específico (como a média ponderada de uma determinada faculdade), deve-se utilizar fórmulas, não funções. LISTAGEM DAS FUNÇÕES MAIS USADAS NO EXCEL Ainda há muitas funções que podem ser úteis para os mais variados tipos de profissionais, como contadores, engenheiros, professores, ou qualquer um que queira trabalhar com o Excel como sua ferramenta de trabalho. ALGUMAS FUNÇÕES DO EXCEL. Objetivo: Vamos aprender algumas funções básicas do Excel. Nesta lição aprenderemos a utilizar as seguintes funções que foram mais cobradas nos cinco últimos anos: =SOMA( ) =MÉDIA( ) =MÁXIMO( ) =MÍNIMO( ) =MULT( ) =MOD( ) =MED( ) =MAIOR( ) =MENOR( ) =CONCATENAR ou & =CONT.SE( ) =SOMASE( ) =HOJE( ) =AGORA( ) =DIAS360( ) =CONT.VALORES( ) =CONT.NÚM( ) =CONTAR.VAZIO( ) =ARRED( ) =TRUNCAR( ) =SE( =SE(E( =SE(OU( SOMA Retorna a soma de todos os números na lista de argumentos. Sintaxe =SOMA(núm1;núm2; ...) =SOMA(A1:A10) =SOMA(A1;A3;A5;A6;A9; ...) =SOMA(A1+A2+A3; ...) Núm1, núm2,... Por exemplo, são argumentos de 1 a 30 que se deseja somar. Comentários Os números, valores lógicos e representações em forma de texto de números digitados diretamente na lista de argumentos são contados. Consulte o primeiro e o segundo exemplos seguintes. Se um argumento for uma matriz ou referência, apenas os números nesta matriz ou referência serão contados. Células vazias, valores lógicos, texto ou valores de erro na matriz ou referência são ignorados. Consulte o terceiro exemplo a seguir. Os argumentos que são valores de erro ou texto que não podem ser traduzidosem números geram erros. MÉDIA Retorna a média aritmética dos argumentos. Sintaxe =MÉDIA(núm1;núm2; ...) Núm1; núm2;... são de 1 a 30 argumentos numéricos para os quais você deseja obter a média. =MÉDIA(A1:A5) =MÉDIA(A1;A2;A5; ...) =MÉDIA(A1:A5;B1:B3); ...) Comentários Os argumentos devem ser números ou eles devem ser nomes, matrizes ou referências que contenham números. Se uma matriz ou argumento de referência contiver texto, valores lógicos ou células vazias, estes valores serão ignorados; no entanto, células com valor zero serão incluídas. MÁXIMO =MÁXIMO(núm1;núm2; ...) Retorna o valor máximo de um conjunto de valores. Sintaxe =MÁXIMO(A1;A10) =MÁXIMO(A1;A4;A8; ...) =MÁXIMO(A1:C10) Núm1, núm2,... são de 1 a 30 números para os quais você deseja saber o valor máximo. Comentários Você pode especificar os argumentos que são números, células vazias, valores lógicos ou representações em texto de números. Os argumentos que são valores de erro ou texto que não podem ser traduzidos em números causam erros. Se um argumento for uma matriz ou referência, apenas os números nesta matriz ou referência serão usados. Células vazias, valores lógicos ou texto na matriz ou referência serão ignorados. Se os argumentos não contiverem números, MÁXIMO retornará 0. MÍNIMO =MÍNIMO(núm1;núm2;...) Retorna o menor número na lista de argumentos. Sintaxe =MÍNIMO(A1:A10;3;5) =MÍNIMO(A1;A5;A7;...) =MÍNIMO(A1:B5) Núm1, núm2,... são de 1 a 30 números dos quais você deseja saber o valor mínimo. Comentários Você pode especificar os argumentos que são números, células vazias, valores lógicos ou representações em texto de números. Os argumentos que são valores de erro ou texto que não podem ser traduzidos em números causam erros. Se um argumento for uma matriz ou referência, apenas os números daquela matriz ou referência poderão ser usados. Células vazias, valores lógicos ou valores de erro na matriz ou referência serão ignorados. Se os argumentos não contiverem números, MÍNIMO retornará 0. MULT =MULT(núm1;núm2;...) Multiplica todos os números fornecidos como argumentos e retorna o produto. Sintaxe =MULT(A1:A10;5) =MULT(A1;A6;A8;A10;2;...) =MULT(A1:B6) Núm1, núm2,... são números de 1 a 30 que você deseja multiplicar. Comentários Os argumentos que são números, valores lógicos ou representações em forma de texto de números serão contados; os argumentos que são valores de erro ou texto que não podem ser traduzidos em números causam erros. Se um determinado argumento for uma matriz ou referência, apenas os números na matriz ou referência serão contados. As células vazias, valores lógicos ou de erro na matriz ou referência serão ignorados. MOD =MOD(núm,divisor) Retorna o resto depois da divisão de um número por divisor. O resultado possui o mesmo sinal que divisor. Sintaxe =MOD(A5,B2) =MOD(25,4) =MOD(A6,8) Núm é o número para o qual você deseja encontrar o resto. Divisor é o número pelo qual você deseja dividir o número. Comentários Se divisor for 0, Resto retornará o valor de erro #DIV/0!. MED =MED(núm1;núm2;...) Retorna a mediana dos números indicados. A mediana é o número no centro de um conjunto de números; isto é, metade dos números possui valores que são maiores do que a mediana e a outra metade possui valores menores. Sintaxe =MED(A1:A8) =MED(A1;A4;A8;A9;...) =MED(A1:A8;A10;5) Núm1; núm2;... são de 1 a 30 números dos quais você deseja obter a mediana. Comentários Os argumentos devem ser números ou nomes, matrizes ou referências que contenham números. O Microsoft Excel examina todos os números em cada argumento de referência ou matriz. Se uma matriz ou argumento de referência contiver texto, valores lógicos ou células vazias, estes valores serão ignorados; no entanto, células com valor zero serão incluídas. Se houver uma quantidade par de números no conjunto, MED calculará a média dos dois números do meio. Se a quantidade for impar, o número será o que ficar no centro após a ordenação (crescente ou decrescente). MAIOR =MAIOR(matriz;k) Retorna o maior valor k-ésimo de um conjunto de dados. Você pode usar esta função para selecionar um valor de acordo com a sua posição relativa. Por exemplo, você pode usar MAIOR para obter o primeiro, o segundo e o terceiro resultados. Sintaxe =MAIOR(matriz;k) =MAIOR(A1:A10;5) - O quinto maior valor no intervalo entre A1:A10; Matriz é a matriz ou intervalo de dados cujo maior valor k-ésimo você deseja determinar. K é a posição (do maior) na matriz ou intervalo de célula de dados a ser fornecida. Comentários Se a matriz estiver vazia, MAIOR retornará o valor de erro #NÚM!. Se k ≤ 0 ou se k for maior que o número de pontos de dados, MAIOR retornará o valor de erro #NÚM!. Se n for o número de pontos de dados em um intervalo, MAIOR(matriz;1) retornará o maior valor e MAIOR(matriz;n) retornará o menor valor. MENOR =MENOR(matriz;k) Retorna o menor valor k-ésimo do conjunto de dados. Use esta função para retornar valores com uma posição específica relativa em um conjunto de dados. Sintaxe =MENOR(matriz;k) =MENOR(A1:A10;5) - O quinto menor valor no intervalo entre A1:A10; Matriz é uma matriz ou intervalo de dados numéricos cujo menor valor k-ésimo você deseja determinar. K é a posição (a partir do menor) na matriz ou intervalo de dados a ser fornecido. Comentários Se matriz estiver vazia, MENOR retornará o valor de erro #NÚM!. Se k ≤ 0 ou k exceder o número de pontos de dados, MENOR retornará o valor de erro #NÚM!. Se n for o número de pontos de dados em matriz, MENOR(matriz;1) será igual ao menor valor, e MENOR(matriz;n) será igual ao maior valor. CONCATENAR =CONCATENAR (texto1;texto2; ...) Agrupam várias sequencias de caracteres de texto em uma única sequência de caracteres de texto. Sintaxe =CONCATENAR (texto1;texto2; ...) Texto1; texto2;... são 1 a 30 itens de texto a serem agrupados em um único item de texto. Os itens de texto podem ser sequência de caracteres de texto, números ou referências de célula única. Comentários O operador "&" pode ser usado no lugar do CONCATENAR para agrupar itens de texto. =B2&B5&C6 Serão unidos os conteúdos das células B2 com o valor da célula B5 com o valor da célula C6. Quando o valor for numérico será reconhecido como texto. Podemos também utilizar constantes como no exemplo abaixo: CONT.SE Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios. Sintaxe =CONT.SE(intervalo;critérios) Intervalo é o intervalo de células do qual se deseja contar células não vazias. Critérios é o critério na forma de um número, expressão, referência de célula ou texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs" ou B4. Comentários O Microsoft Excel oferece funções adicionais que podem ser usadas para analisar os dados com base em uma condição. Para calcular uma soma com base em uma sequência ou um número dentro de um intervalo, use a função de pasta de trabalho SOMASE. Para que a fórmula retorne um de dois valores com base em uma condição, como uma bonificação de vendas com base em um valor de vendas especificado, use a função de planilha SE. Para contar ascélulas vazias ou não, use as funções CONT.VALORES e CONTAR.VAZIO. SOMASE Adiciona as células especificadas por um determinado critério. Sintaxe =SOMASE(intervalo;critérios;intervalo_soma) Intervalo é o intervalo de células que se deseja calcular. Critérios são os critérios na forma de um número, expressão ou texto, que define quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs". Intervalo_soma: são as células que serão realmente somadas. HOJE Retorna o número de série da data atual. O número de série é o código de data-hora usado pelo Microsoft Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. Sintaxe =HOJE( ) Comentários O Excel armazena datas como números de série sequenciais para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2008 é o número de série 39448 porque está 39.448 dias após 1° de janeiro de 1900. O Microsoft Excel para o Macintosh usa um sistema de data diferente como padrão. AGORA Retorna o número de série sequencial da data e hora atuais. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. Sintaxe =AGORA( ) Comentários O Microsoft Excel armazena datas como números de série sequenciais para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2008 é o número de série 39448 porque está 39.448 dias após 1° de janeiro de 1900. O Microsoft Excel para o Macintosh usa um sistema de data diferente como padrão. Os números à direita da vírgula decimal no número de série representam a hora; os números à esquerda representam a data. Por exemplo, o número de série 0,5 representa a hora 12:00 meio-dia. A função AGORA só muda quando a planilha é calculada ou quando a macro que contém a função é executada, não sendo atualizada continuamente. CONT.VALORES Calcula o número de células não vazias na lista de argumentos (Letras, Números e Caracteres gerais). Use CONT.VALORES para calcular o número de células com dados em um intervalo ou matriz. Sintaxe =CONT.VALORES(valor1;valor2;...) Valor1; valor2;... são argumentos de 1 a 30 que representam os valores que você deseja calcular. Neste caso, um valor é qualquer tipo de informações, incluindo texto vazio (""), mas não incluindo células em branco. Se um argumento for uma matriz ou referência, as células vazias na matriz ou referência são ignoradas. Se você não precisa calcular valores lógicos, texto ou valores de erro, utilize a função CONT.NÚM CONT.NÚM Conta quantas células contêm números na lista de argumentos. Use CONT.NÚM para obter o número de entradas em um campo de número que estão em um intervalo ou matriz de números. Sintaxe =CONT.NÚM(valor1;valor2;...) Valor1; valor2, ... são argumentos de 1 a 30 que contêm ou se referem a uma variedade de diferentes tipos de dados, mas somente os números são contados. Comentários Os argumentos que são números, datas ou representações de texto de número são calculados, os argumentos que são valores de erro ou texto que não podem ser traduzidos em números são ignorados. Se um argumento for uma matriz ou referência, somente os números nesta matriz ou referência são calculados. Células vazias, valores lógicos, texto ou valores de erro nesta matriz ou referência são ignorados. Se você precisa calcular valores lógicos, texto ou valores de erro, utilize a função CONT.VALORES. CONTAR.VAZIO Conta o número de células vazias no intervalo especificado. Sintaxe =CONTAR.VAZIO(intervalo) Intervalo é o intervalo no qual se deseja contar as células em branco. Comentários Células com fórmulas que retornam "" (texto vazio) também são contadas. Células com valores nulos não são contadas. Exemplo ARRED Arredonda um número até uma quantidade especificada de dígitos. Sintaxe =ARRED(núm;núm_dígitos) Núm é o número que você deseja arredondar. Núm_dígitos especifica o número de dígitos para o qual você deseja arredondar núm. TRUNCAR(núm;núm_dígitos) Núm é o número que se deseja truncar. Núm_dígitos é um número que especifica a precisão da operação. O valor padrão para núm_dígitos é 0 (zero). Sintaxe =TRUNCAR(núm;núm_dígitos) TRUNCAR remove a parte fracionária do número. Resposta: o valor apresentado será 1,56, porque o TRUNCAR elimina os valores de acordo com o parâmetro após o ponto-e-vírgula como mostra no exemplo acima (duas casas decimais). DIAS360 Retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). Use esta função para ajudar no cálculo dos pagamentos, se o seu sistema de contábil estiver baseado em doze meses de 30 dias. Sintaxe =DIAS360(data_inicial; data_final; método) Data_inicial e data_final são as duas datas entre as quais você deseja saber o número de dias. Se data_inicial ocorrer após data_final, DIAS360 retornará um número negativo... Comentários O Microsoft Excel armazena datas como números de série sequenciais para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2008 é o número de série 39448 porque está 39.448 dias após 1° de janeiro de 1900. O Microsoft Excel para o Macintosh usa um sistema de data diferente como padrão. Exemplo Talvez seja mais fácil compreender o exemplo se você copiá-lo para uma planilha em branco. Crie uma pasta de trabalho ou planilha em branco. A B 1 =DIAS360(A2;A3) 2 30/01/10 3 01/02/10 =DIAS360(A2;A3) O número de dias entre as duas datas acima, com base em um ano de 360 dias. Ano contábil. SE Retorna um valor se uma condição que você especificou avaliar como VERDADEIRO e um outro valor se for avaliado como FALSO. Use SE para conduzir testes condicionais sobre valores e fórmulas. Sintaxe =SE(teste_lógico;valor_se_verdadeiro;valor_se_fal so) Teste_lógico é qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador de cálculo de comparação. Valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO. Por exemplo, se esse argumento for a sequência de caracteres de texto "Dentro do orçamento" e o argumento teste_lógico for considerado VERDADEIRO, a função SE exibirá o texto "Dentro do orçamento". Se teste_lógico for VERDADEIRO e valor_se_verdadeiro for vazio, o argumento retornará 0 (zero). Para exibir a palavra VERDADEIRO, use o valor lógico VERDADEIRO para esse argumento. Valor_se_verdadeiro pode ser outra fórmula. Valor_se_falso é o valor retornado se teste_lógico for FALSO. Por exemplo, se esse argumento for a sequência de caracteres de texto "Acima do orçamento" e o argumento teste_lógico for considerado FALSO, a função SE exibirá o texto "Acima do orçamento". Se teste_lógico for FALSO e valor_se_falso for omitido (ou seja, se não houver vírgula após valor_se_verdadeiro), o valor lógico FALSO será retornado. Se teste_lógico for FALSO e valor_se_falsofor vazio (ou seja, se houver uma vírgula após valor_se_verdadeiro seguida do parênteses de fechamento), o valor 0 (zero) será retornado. Valor_se_falso pode ser outra fórmula. Comentários É possível aninhar até sete funções SE como argumentos valor_se_verdadeiro e valor_se_falso para construir testes mais elaborados. Consulte o último dos exemplos a seguir. Quando os argumentos valor_se_verdadeiro e valor_se_falso são avaliados, SE retorna o valor que foi retornado por estas instruções. Se algum dos argumentos de SE for matriz, cada elemento da matriz será avaliado quando a instrução SE for executada. O Microsoft Excel fornece funções adicionais que podem ser usadas para analisar os dados com base em uma condição. Por exemplo, para contar o número de ocorrências de uma sequência de caracteres de texto ou um número dentro de um intervalo de células, use a função de planilha CONT.SE. Para calcular uma soma baseada em uma sequência de caracteres de texto ou em um número dentro de um intervalo, use a função de planilha SOMASE. Saiba como calcular um valor baseado em uma condição. Exemplos: =SE(VERDADEIRO;1;2) retorna 1 =SE(FALSO;1;2) retorna 2 =SE(A1<5;10;20) SE A1 for igual à 3 retorna 10 SE A1 for igual a 8 retorna 20 Tabela lógica OU E V V V V V V V F V V F F F V V F V F F F F F F F Mais Funções do Excel Objetivo: Vamos aprender mais algumas funções básicas do Excel. Neste lição aprenderemos a utilizar as seguintes funções: · OU() · E() · NÃO() =E() Todos os argumentos devem ser verdadeiros, para que a função retorne um valor verdadeiro. Sintaxe: =E(Argumentos) Exemplo: =E(2<3;7>5) Retorna Verdadeiro =E(2>3;5>4))Retorna Falso Também posso utilizar referência a Células. Por exemplo, se na Célula A5 eu tiver o valor 10, teremos o seguinte: =E(A5<12;A5=10) Retorna Verdadeiro =E(A5<10;5>3) Retorna Falso, pois A5<10 é falso =OU() Pelo menos um dos argumentos testados devem ser verdadeiros, para que a função retorne um valor verdadeiro. A função somente retorna falso, quando todos os argumentos testados forem falsos. Sintaxe: =OU(Argumentos) Exemplo: =OU(2<3;7>5) Retorna Verdadeiro =OU(2>3;5>4)) Retorna Verdadeiro =OU(2>3;5<4) Retorna Falso Também posso utilizar referência à Células. Por exemplo, se na Célula A5 eu tiver o valor 10, teremos o seguinte: =OU(A5<12;A5=9) Retorna Verdadeiro =OU(A5<10;5<3) Retorna Falso =NÃO() Inverte o Valor de uma expressão Lógica, se a expressão for verdadeira, retorna Falso, e se a expressão Falso retorna Verdadeiro. Sintaxe: =NÃO(Argumento) Exemplo: =NÃO(2>3) Retorna Verdadeiro =NÃO(3>2) Retorna Falso Utilização Avançada da função SE Objetivo: Nesta lição aprenderemos a utilizar funções "SE ANINHADAS", isto é, uma função SE dentro da outra. Também utilizaremos a função E(). =SE(F2>=7; ”Aprovado” ; SE(F2<2 ; ”Reprovado” ; ”Recuperação”)) A Célula F2 corresponde a Média do aluno, neste caso estamos verificando se o aluno tem a média maior ou igual a média 7 (sete), caso a média que não seja maior ou igual a 7, então será passado para o próximo parâmetro que é o valor falso, que foi substituído por outro SE, que faz novo teste lógico com a possibilidade de duas alternativas para a resposta. Operadores de Comparação Operador Descrição Exemplo de Fórmula = Igual =(A1=B7) > Maior que =D4>70 < Menor que =D4<70 >= Maior ou igual a =E2>=A1 <= Menor ou igual a =C19<=100 <> Diferente =A1<>D4 Operadores Aritméticos Operador Descrição Exemplo de Fórmula + Adição =A1+B1+10 - Subtração = A4-5000 / Divisão =(A1+C3)/4 * Multiplicação =C4*10 % Porcentagem =A1*6% ^ Exponenciação =2^6 TRABALHANDO COM CÉLULA LOCALIZADA EM OUTRA PASTA E PLANILHA. =[pasta1.xls]Plan2!B2*2 [pasta1.xls] → Corresponde a pasta de trabalho que você quer acessar, mesmo que a mesma esteja fechada, a pasta será acessada. Plan2 → Corresponde à planilha que você deseja acessar dentro da pasta exibida antes da expressão. ! → Corresponde ao caractere que faz a junção da planilha com a Célula. B2 → Corresponde a Célula que cera acessada. COPIANDO FÓRMULAS E FUNÇÕES No Excel, cada fórmula, deve ser usada para um determinado cálculo, observe na tela que se segue que temos 6 alunos e consequentemente 6 médias serão calculadas, mas apenas construímos uma delas (a do primeiro aluno). Se quisermos que as outras crianças tenham médias automaticamente calculadas, devemos construir as outras funções também (uma para cada aluno, ou seja, uma para cada linha). Não se preocupe com a quantidade de fórmulas que você vai ter que digitar, na verdade, com o recurso da ALÇA DE PREENCHIMENTO, o Excel construirá as outras fórmulas baseadas no formato do original. Como funciona? Verifique na figura acima, com a média já pronta que a Célula Ativa possui um quadradinho preto em sua extremidade inferior direita. Ele aparece em todas as células que selecionamos e se chama Alça de Preenchimento. Depois que construir a fórmula que deseja copiar, arraste-a por essa Alça até atingir a linha desejada (ou coluna, pois podemos arrastar lateralmente também). O interessante dessa estória é que, a Segunda fórmula (que no caso da figura é a média de SICLANO) não pode ter o mesmo conteúdo da primeira, afinal =MÉDIA(B4:E4) é pra ser somente a de FULANO. Mas o Excel atualiza a cópia das fórmulas, pois as referências são relativas, então, na segunda linha teremos MÉDIAS (B5:E5) e abaixo MÉDIA (B6:E6) e assim por diante. Esse recurso de copiar fórmulas não é possível somente com o uso da Alça de Preenchimento, também podemos realizá-lo com copiar (CTRL+C) e colar (CTRL + V), ou arrastando o conteúdo da célula com o CTRL pressionado (que significa COPIAR). ATENÇÃO: Se usar CTRL+X (recortar) para depois colar, a fórmula presente na célula não se alterará, ou seja, continuará apontando para as referências para as quais estavam apontando antes, isso serve para arrastar (mover) o conteúdo da célula sem o CTRL também. ATENÇÃO 2: Se quiser fixar uma referência (componente) antes de copiar uma fórmula, para que ela não seja atualizada com o movimento, basta colocar $ (cifrão) antes do componente que se deseja fixar (ou seja, se quer fixar a coluna da célula A4, escreva $A4, se quer fixar a linha, escreva A$4, se quiser fixar Coluna e linha, escreva a $A$4). A tecla de atalho é F4. Por exemplo, se copiarmos a fórmula =B4+C4 para duas linhas abaixo, ela vai se tornar =B6+C6, mas se a escrevermos =B4+C$4, ao copiarmos para duas linhas abaixo, ela se tornará = B6+C$4 (espero que tenha entendido que o 4 não variou por conta do cifrão). Outro exemplo, se copiarmos a fórmula =B4+C4 para duas colunas a direita, ela vai se tornar =D4+E4, mas se a escrevermos =B4+$C4, ao copiarmos para duas colunas a direita, ela se tornará = D4+$C4 (espero que tenha entendido que a coluna C não variou por conta do cifrão). NOTA: Se a Alça de Preenchimento for usada em palavras, elas se repetem, a menos que as palavras existam num conjunto de listas (sequencias) que o Excel possui. Por exemplo, se você digitar SEG e arrastar a alça o Excel criará TER, QUA, QUI, etc... O mesmo acontece com Textos seguidos de números ALUNO1, ALUNO2,etc... Formatos de número disponíveis Aplicando diferentes formatos de número, você pode alterar a aparência dos números sem alterar o número em si. Um formato de número não afeta o valor real da célula que o Microsoft Excel usa para executar cálculos. O valor real é exibido na barra de fórmulas. A tabela a seguir é um resumo dos formatos de número disponíveis na guia Número da caixa de diálogo Formatar Células (menu Formatar, comando Células). Para obter informações sobre como exibir números nesses formatos, clique nos títulos dos tópicos correspondentes na seção Consulte também, que é exibida quando você está conectado à Internet. Formato Geral Esse é o formato padrão de número que o Excel aplica quando você digita um número. Na sua maioria, os números que estão no formato Geral são exibidos exatamente da maneira que você os digitou. Entretanto, se a célula não for grande o suficiente para exibir todo o número, o formato Geral arredonda os números pelos decimais. O formato de número Geral também usa a notação científica (exponencial) para números grandes (12 ou mais dígitos). Número Esse formato é usado para a exibição geral de números. Você pode especificar o número desejado de casas decimais, a utilização ou não de separadores de milhar e a forma de exibição de números negativos. Moeda Esse formato é usado para valores monetários em geral e exibe o símbolo da moeda padrão com os números. Você pode especificar o número desejado de casas decimais, a utilização ou não de separadores de milhar e a forma de exibição de números negativos. Contábil Esse formato também é usado para valores monetários, mas alinha os símbolos de moeda e pontos decimais dos números na coluna. Data Esse formato exibe números seriais de hora e data como valores de data, de acordo com o tipo e local (localidade) especificados. Exceto para itens que tenham um asterisco (*) na lista Tipo (guia Número, caixa de diálogo Formatar Células), os formatos de data aplicados não alternam ordens de data com o sistema operacional. Hora Esse formato exibe números seriais de hora e data como valores de hora, de acordo com o tipo e local (localidade) especificados. Exceto para itens que tenham um asterisco (*) na lista Tipo (guia Número, caixa de diálogo Formatar Células), os formatos de hora aplicados não alternam ordens de hora com o sistema operacional. Porcentagem Esse formato multiplica o valor da célula por 100 e exibe o resultado com o símbolo de porcentagem. Você pode especificar o número desejado de casas decimais. Fração Esse formato exibe um número como uma fração, conforme o tipo de fração especificado. Científico Esse formato exibe um número em notação exponencial, substituindo parte do número por E+n, onde E (para Expoente) multiplica o número precedente por 10 à enésima potência. Por exemplo, o formato Científico de 2 decimais exibe o número 12345678901 como 1,23E+10, que é 1,23 vezes 10 à 10ª potência. Você pode especificar o número desejado de casas decimais. Texto Esse formato trata o conteúdo de uma célula como texto e o exibe exatamente como digitado, mesmo quando são números. Especial Esse formato exibe um número como um código postal (CEP), número de telefone ou número do CPF (CIC). Personalizado Esse formato permite que você modifique uma cópia de um código de formato de número existente. Isso cria um formato de número personalizado que é adicionado à lista de códigos de formato de número. Você pode adicionar entre 200 e 250 formatos de número personalizados, dependendo da versão do idioma do Excel que você tem instalada. ERROS # Algumas vezes cometemos erros de construção da fórmula, não pela sintaxe da mesma, mas por erros nas referências das células utilizadas. O Excel às vezes nos retorna mensagens de erro, normalmente precedidas pelo sinal de # (sustenido). As mensagens de erro # mais comuns estão listadas abaixo: #VALOR: Este erro é apresentado quando criamos uma fórmula que aponta para uma referência que possui TEXTO. Esse erro não é apresentado quando utilizamos uma função, apenas quando foi utilizada uma fórmula. #NOME: Este erro ocorre quando digitamos errado no nome de uma função. #DIV/0!: O Excel apresenta este erro quando, em algum momento do trabalho, enviamos uma solicitação para que ele use 0 (zero) como divisor em alguma fórmula. #REF - Este erro ocorre quando a referência de célula não existe na planilha. #NULO - Ocorre quando não é usado o operador, representando a operação, colocando apenas as células com um espaço em branco entre elas. #NÚM! – Ocorre com valores numéricos inválidos em uma fórmula ou função. #N/D – Ocorre quando um valor não está disponível para uma função ou fórmula. ######## - Ocorre quando o conteúdo da célula é maior que a largura da célula, para corrigir este erro, podemos clicar duas vezes na borda da coluna estreita e a posterior. Referência Circular O Erro de Referência Circular é quando inserimos uma fórmula que dependa direta ou indiretamente da célula onde ela está sendo inserida. É um tipo de erro que só é uma pequena falta de atenção. Observe o exemplo abaixo: Isto acontece porque estamos querendo somar uma célula que contem a própria fórmula. Ex: =SOMA(C3:C6) O Excel não conseguirá resolver o que produzira sucessivos cálculos fazendo o programa entrar em “loop”. O resultado é uma mensagem de erro e a célula apresentando 0 (zero) e uma seta apontando para onde o erro ocorreu. Esta é a tela de erro da Referência Circular: LISTAGEM DAS FERRAMENTAS E COMANDOS PRÓPRIOS DO EXCEL. O Excel possui uma série de comandos parecidos com Word, mas possui ferramentas exclusivas, e as irei mostrar agora, desejando que você as assimile bem, pois podem ser cobradas no Concurso. Formatar Células: O Menu Formatar possui uma opção chamada Células, que também pode ser acessada pelo atalho CTRL+1, nesta opção podemos alterar toda e qualquer configuração no que diz respeito ao formato das células do Excel, como cores, tipos de letras, bordas, formatos dos números, alinhamento do texto e até mesmo se a célula pode ser travada ou não. Na próxima página segue uma imagem da tela de Formatação de Células e suas várias “orelhas” de opções. Numero Ajusta o formato dos números de uma célula, como o número de casas decimais, os símbolos decimais e de milhar, os separadores de hora e data, o formato do número negativo, o formato dos valores de moeda, e muitos ajustes. Alinhamento Ajusta a forma como o texto se comporta na célula, se está à direita ou à esquerda, no centro (tanto horizontal como vertical), e até mesmo se o texto ficará inclinado ou não. Há também como fazer o texto estar em duas linhas na mesma célula. Fonte Ajusta a formatação das letras da planilha. Borda Altera o formato das Bordas que cercam uma célula, é possível alterar estilos, largura, cor e qualquer outra configuração. Padrões Altera as cores de fundo das células, que podem não ser brancas. Proteção Informa se célula pode ser ou não alterada quando travamos a planilha. Se marcarmos que a célula está travada, no momento em que protegemos a planilha, ela não poderá ser mais alterada, apenas pela pessoa que possua a senha para desprotegê-la. As outras páginas desta tela, como fonte e Bordas, têm
Compartilhar