Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel para Executivos – Ferramenta de Analise Maria Silvia Mendonça de Barros EXCEL PARA EXECUTIVOS 2 SUMÁRIO CAPÍTULO 1 – REVISÃO DE CONCEITOS BÁSICOS EM PLANILHAS.......................................... 4 FÓRMULAS ................................................................................................................ 5 CÓPIA DE FÓRMULAS................................................................................................ 5 Endereços relativos ........................................................................................ 5 Endereços absolutos ....................................................................................... 5 CAPÍTULO 2 – FUNÇÕES ........................................................................................................... 7 FUNÇÕES MATEMÁTICAS ......................................................................................... 8 SOMA .............................................................................................................. 8 SOMASE .......................................................................................................... 8 SOMASES ........................................................................................................ 8 FUNÇÕES ESTATÍSTICAS ............................................................................................ 9 MÁXIMO ......................................................................................................... 9 MÍNIMO .......................................................................................................... 9 MÉDIA ............................................................................................................. 9 MÉDIASE ......................................................................................................... 9 MÉDIASES ....................................................................................................... 9 CONT.SE ........................................................................................................ 10 CONT.SES ...................................................................................................... 10 CONT.VALORES ............................................................................................. 10 FUNÇÕES LÓGICAS .................................................................................................. 10 SE .................................................................................................................. 10 E .................................................................................................................... 11 OU ................................................................................................................. 11 SEERRO ......................................................................................................... 11 FUNÇÕES DE PESQUISA .......................................................................................... 11 PROCV ........................................................................................................... 11 PROCH........................................................................................................... 12 CORRESP ....................................................................................................... 13 ÍNDICE ........................................................................................................... 13 FUNÇÕES FINANCEIRAS .......................................................................................... 18 VF .................................................................................................................. 18 VP .................................................................................................................. 18 PGTO ............................................................................................................. 18 NPER ............................................................................................................. 19 TRABALHANDO COM DATAS E HORA ..................................................................... 20 Visualização de datas .................................................................................... 20 Funções de data e hora ................................................................................ 21 HOJE .............................................................................................................. 21 ANO .............................................................................................................. 22 MÊS ............................................................................................................... 22 DIA ................................................................................................................ 22 DIA.DA.SEMANA ........................................................................................... 22 DIATRABALHO.INTL ...................................................................................... 23 EXCEL PARA EXECUTIVOS 3 DIATRABALHOTOTAL.INTL ............................................................................ 24 HORA ............................................................................................................ 25 MINUTO ........................................................................................................ 25 CAPÍTULO 3 – VALIDAÇÃO DE DADOS E FORMATAÇÃO CONDICIONAL .............................. 28 VALIDAÇÃO DE DADOS ........................................................................................... 29 FORMATAÇÃO CONDICIONAL ................................................................................. 31 CAPÍTULO 4 – CLASSIFICAÇÃO DE DADOS, TOTALIZAÇÃO E FILTROS .................................. 33 CLASSIFICAÇÃO DE DADOS ..................................................................................... 34 Classificação por mais de um campo ............................................................ 34 Criando lista personalizada .......................................................................... 35 FILTROS ................................................................................................................... 35 Auto Filtro ..................................................................................................... 36 Filtros avançados .......................................................................................... 37 TOTAIS E SUB-TOTAIS ............................................................................................. 39 CAPÍTULO 5 – GRÁFICOS E MINI GRÁFICOS .......................................................................... 40 MINI GRÁFICOS ....................................................................................................... 41 GRÁFICOS ................................................................................................................ 42 CAPÍTULO 6 – TABELAS E GRÁFICOS DINÂMICOS ................................................................ 43 TABELAS DINÂMICAS .............................................................................................. 44 GRÁFICOS DINÂMICOS ............................................................................................ 49 CAPÍTULO 7 – HIPÓTESES ...................................................................................................... 50 ATINGIR METAS ...................................................................................................... 51 CENÁRIOS ................................................................................................................ 52 CAPÍTULO 8 – PROTEÇÃO ......................................................................................................55 PROTEGENDO CÉLULAS DA PLANILHA .................................................................... 56 PROTEGENDO UMA PLANILHA ............................................................................... 57 PROTEGENDO UMA PASTA DE TRABALHO ............................................................. 57 CAPÍTULO 9 – MACROS INTERATIVAS ................................................................................... 58 BIBLIOGRAFIA ...................................................................................................................... 62 EXCEL PARA EXECUTIVOS 4 CAPÍTULO 1 Revisão de conceitos básicos em planilhas FÓRMULAS Uma fórmula é uma expressão que permite a realização de cálculos, comparações e outros procedimentos em uma célula de uma planilha. Na digitação, deve sempre ser iniciada com o sinal de igual (=). Na maioria das vezes a utilização de fórmulas envolve o uso dos operadores matemáticos, que são os seguintes: + Soma - Subtração * Multiplicação / Divisão ^ Exponenciação Na construção de fórmulas mais complexas, deve-se tomar cuidado com a ordem em que as operações devem ser realizadas. Para que não aconteçam resultados inesperados, deve-se levar em consideração a prioridade em que são executadas as operações matemáticas. Assim, multiplicação e divisão sempre são efetuadas antes da soma e subtração, e a exponenciação é sempre efetuada em primeiro lugar. Por exemplo: o resultado de 5+15/4 é igual a 8,75, ou seja, primeiro é efetuada a operação 15/4 e, a seguir, seu resultado é somado a 5. Para que a ordem de cálculo nesse exemplo seja alterada para que a soma seja efetuada entes da divisão, deve-se utilizar os parênteses. Assim: 5+15/4 = 8,75 (5+15)/4 = 5 Se dois operadores consecutivos têm o mesmo nível de prioridade, a ordem em que são calculados não tem importância. CÓPIA DE FÓRMULAS Normalmente as fórmulas criadas em uma planilha são “repetitivas”, ou seja, elas têm o mesmo raciocínio lógico, mudando-se somente os endereços das células que as compõem. Para minimizar o trabalho em planilhas podemos copiar as fórmulas existentes para linhas ou colunas adjacentes. No caso da cópia de fórmulas, vamos trabalhar com dois tipos de endereços: relativos e absolutos. Endereços relativos A referência relativa refere-se à célula por sua posição em relação à célula que contém a fórmula. Endereços absolutos A referência absoluta refere-se à célula que ocupa uma posição fixa ou absoluta na planilha. EXCEL PARA EXECUTIVOS 5 Esses dois conceitos são importantes no momento da cópia de fórmulas de células. Quando quisermos inserir um endereço de célula absoluto na planilha, devemos digitar o caractere cifrão ($) antes das coordenadas de linha e coluna. Podemos ter um endereço absoluto somente para a identificação de linha, somente para identificação de coluna ou para a identificação de linha e coluna. Vamos pegar como exemplo a planilha PRODUTOS EM DÓLAR.xlsx: Abra a planilha PRODUTOS EM DÓLAR.xlsx. Para calcular o valor do produto em real, devemos multiplicar o valor do produto em dólar pelo valor do dólar. Dessa forma as fórmulas nas células C4, C5, C6, C7 e C8 ficariam: Em C4: =B4*C1 Em C5: =B5*C1 Em C6: =B6*C1 Em C7: =B7*C1 Em C8: =B8*C1 Podemos verificar que a célula C1 está presente em todas as fórmulas. No entanto, se copiarmos a fórmula da célula C4 para as células C5, C6, C7 e C8, não teremos o resultado correto, pois o endereço da célula C1 mudará conforme a linha. Dessa forma, para que o endereço de C1 não seja modificado durante a cópia, teremos que fixá-lo na fórmula inserida em C4, colocando o caractere $ antes da identificação da coluna e antes da identificação da linha. A fórmula em C4 ficará: =B4*$C$1 EXCEL PARA EXECUTIVOS 6 CAPÍTULO 2 Funções EXCEL PARA EXECUTIVOS 7 FUNÇÕES MATEMÁTICAS Função SOMA Objetivo: somar os valores numéricos contidos em uma faixa de células. Sintaxe: SOMA(núm1;núm2;...) Argumentos: os argumentos núm1, núm2,... correspondem a valores, endereços de células ou faixa de células que desejamos somar. Função SOMASE Objetivo: efetuar a soma dos valores indicados de acordo com determinado critério ou condição. Sintaxe: SOMASE(intervalo;critérios;intervalo_soma) Argumentos: - intervalo: intervalo de células em que o critério será procurado; - critério: condição para definir os valores que serão somados. Esses critérios podem ser: número, expressão, referência de célula, texto ou função. Por exemplo: “<>250”, 100, “Mesa para computador”, G34. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>); - intervalo_soma: intervalo de valores que serão somados. Se esse argumento for omitido, serão somadas as células especificadas no argumento intervalo (as mesmas células às quais os critérios são aplicados). Função SOMASES Objetivo: efetuar a soma em um intervalo de células atendendo a vários critérios, que serão combinados. Sintaxe: SOMASES(intervalo_soma;intervalo_critério1;critério1;intervalo_critério2;critério2;...) Argumentos: - intervalo_soma: intervalo de valores que serão somados; - intervalo_critério1: intervalo de células em que o critério1 será procurado; - critério1: condição para definir os valores que serão somados. Esses critérios podem ser número, expressão, referência de célula, texto ou função. Por exemplo: 120, “Vila Mariana”, “<>100”, A12. O critério deverá ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>); - intervalo_critério2: intervalo de células em que o critério2 será procurado; - critério2: condição para definir os valores que serão somados. As mesmas considerações feitas ao critério1 devem ser aplicadas ao critério2 e a todos os outros que possam existir. Podemos ter até 127 pares de intervalos/critérios. EXCEL PARA EXECUTIVOS 8 FUNÇÕES ESTATÍSTICAS Função MÁXIMO Objetivo: retornar o valor máximo entre um conjunto de valores numéricos informado. Sintaxe: MÁXIMO(núm1;núm2;...) Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor máximo queremos determinar. Pode-se informar de 1 a 255 valores numéricos. Função MÍNIMO Objetivo: retornar o valor mínimo entre um conjunto de valores numéricos informado. Sintaxe: MÍNIMO(núm1;núm2;...) Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor mínimo queremos determinar. Pode-se informar de 1 a 255 valores numéricos. Função MÉDIA Objetivo: retornar a média aritmética entre um conjunto de valores numéricos informado. Sintaxe: MÉDIA(núm1;núm2;...) Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos para os quais desejamos calcular a média aritmética. Pode-se informar de 1 a 255 valores numéricos. Função MÉDIASE Objetivo: calcular a média aritmética dos valores indicados de acordo com determinado critério ou condição. Sintaxe: MÉDIASE(intervalo; critérios;intervalo_média) Argumentos: • intervalo: intervalo de células em que o critério será procurado; • critério: condição para definir os valores que serão considerados para o cálculo da média; • intervalo_média: intervalo de células que será considerado para calcular a média. Função MÉDIASES Objetivo: calcular a média aritmética em um intervalo de células que satisfaçam vários critérios. As células de um intervalo podem ser adjacentes ou não adjacentes, atendendo a vários critérios. Sintaxe: MÉDIASES(intervalo_média;intervalo_critério1;critério1;intervalo_critério2;critério2;...) Argumentos: • intervalo_média: intervalo de valores que serão considerados para o cálculo da média; • intervalo_critério1: intervalo de células em que o critério1 será procurado; • critério1:condição para definir os valores que serão considerados para o cálculo da média; • intervalo_critério2: intervalo de células em que o critério2 será procurado; EXCEL PARA EXECUTIVOS 9 • critério2: condição para definir os valores que serão considerados para o cálculo da média. Função CONT.SE Objetivo: contar o número de ocorrências de uma determinada condição em um intervalo de células informado. Sintaxe: CONT.SE(intervalo;critério) Argumentos: • intervalo: intervalo de células que será considerado. • critério: condição que será procurada no intervalo de células. Função CONT.SES Objetivo: aplicar critérios a células em vários intervalos e contar o número de vezes que todos os critérios são verdadeiros. Sintaxe: CONT.SES(intervalo1;critério1;intervalo2;critério2;...) Argumentos: • intervalo1: intervalo de células em que será procurado o critério1; • critério1: condição que será procurada no intervalo de células intervalo1; • intervalo2: intervalo de células em que será procurado o critério2; • critério2: condição que será procurada no intervalo de células intervalo2; Podemos ter até 127 pares de intervalos/critérios. Função CONT.VALORES Objetivo: contar o número de células não vazias em um intervalo informado. O conjunto de células pode conter qualquer tipo de informação. Sintaxe: CONT.VALORES(intervalo1; [intervalo2]; ...) Argumentos: intervalo1;[intervalo2];...: faixas de células que devem ser contadas. FUNÇÕES LÓGICAS Função SE Objetivo: efetuar testes condicionais com valores e fórmulas, permitindo a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro. Sintaxe: SE(teste_lógico;valor_se_verdadeiro; valor_se_falso) Argumentos: • teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; • valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro; EXCEL PARA EXECUTIVOS 10 • valor_se_ falso: o que fazer se o resultado do teste_lógico for Falso. Função E Objetivo: retornar o valor Verdadeiro se todos os seus argumentos forem verdadeiros. Sintaxe: E(lógico1;lógico2;...) Argumentos: • lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; • lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. Em uma função E podemos colocar até 255 argumentos, ou seja, podemos ter até 255 condições lógicas para avaliar. Função OU Objetivo: retornar o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro. Sintaxe: OU(lógico1;lógico2;...) Argumentos: • lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; • lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. Em uma função OU, podemos colocar até 255 argumentos, ou seja, podemos ter até 255 condições lógicas para avaliar. As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções que executam testes lógicos, como, por exemplo, em conjunto com a função SE. Função SEERRO Objetivo: retornar um valor especificado se uma fórmula gerar um erro. Se não existir erro, a função retornará o resultado da fórmula. Sintaxe: SEERRO(fórmula;valor_se_erro) Argumentos: • fórmula: fórmula que desejamos verificar em relação a erros; • valor_se_erro: valor a ser retornado se a fórmula gerar um erro. Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!. FUNÇÕES DE PESQUISA Função PROCV Objetivo: procurar por um valor na primeira coluna de um intervalo de células e retornar um valor da mesma linha em uma coluna especificada. Sintaxe: PROCV(valor_procurado;intervalo_células;núm_coluna,[pro- curar_intervalo]) EXCEL PARA EXECUTIVOS 11 Argumentos: • valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode ser um valor ou uma referência; • intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira coluna de intervalo_células são os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas; • núm_coluna: número da coluna do intervalo de células que contém a informação que deve ser retornada; • procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira coluna do intervalo, se a correspondência deverá ser exata ou aproximada. Se esse argumento for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira coluna do intervalo deverão estar classificados em ordem ascendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Nesse caso, os valores da primeira coluna do intervalo não precisam estar classificados e se houver mais de um valor que coincida com o valor_procurado será considerado o primeiro encontrado. Função PROCH Objetivo: procurar por um valor na primeira linha de um intervalo de células e trazer outro valor da mesma coluna em outra linha especificada. Sintaxe: PROCH(valor_procurado;intervalo_células;núm_linha,[procurar_intervalo]) Argumentos: • valor_ procurado: valor a ser procurado na primeira linha do intervalo de células. Pode ser um valor ou uma referência; • intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira linha de intervalo_células são os valores procurados por valor_ procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas; • núm_linha: número da linha do intervalo de células que contém a informação que deve ser retornada; • procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira linha do intervalo, se a correspondência deverá ser exata ou aproximada. Se esse valor for Verdadeiro ou omitido, a correspondência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira linha do intervalo deverão estar classificados em ordem ascendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for Falso, a correspondência se dará de forma exata. Nesse caso, os valores da primeira linha do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com o valor_procurado, será considerado o primeiro encontrado. A diferença entre as funções PROCV e PROCH é que a primeira efetua a procura de forma vertical, enquanto a segunda o faz de forma horizontal. Você pode utilizar qualquer uma das duas, dependendo da disposição do intervalo de células adotada. EXCEL PARA EXECUTIVOS 12 Função CORRESP Objetivo: retornar a posição em que o valor procurado encontra-se no intervalo de células indicado, que deve indicar uma linha ou uma coluna de células. Sintaxe: CORRESP(valor_procurado;intervalo;[correspondência]) Argumentos: • valor_procurado: argumento obrigatório e que corresponde ao valor que desejamos procurar no intervalo de células e obter sua posição relativa dentro dele; • intervalo: argumento obrigatório e que corresponde ao intervalo de células pesquisadas. Esse intervalo deve ser uma linha ou uma coluna de células; • correspondência: argumento opcional que especifica como o valor será encontrado. Pode ser: 1 ou omitido: Localiza o maior valor que é menor ou igual ao valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem crescente. 0: Localiza o primeiro valor que é exatamente igual ao valor_procurado. Os dados no intervalo podem estar em qualquer ordem. -1: Localiza o menor valor que é maior ou igualao valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem decrescente. Função ÍNDICE Objetivo: retornar o conteúdo de uma célula pelo fornecimento de sua posição dentro do intervalo de células. Sintaxe: ÍNDICE(intervalo;linha;[coluna]) Argumentos: • intervalo: argumento obrigatório que corresponde ao intervalo de células que contém o dado a ser retornado; • linha: argumento obrigatório que corresponde ao número da linha do intervalo em que está o dado a ser retornado; • coluna: argumento opcional que corresponde ao número da coluna do intervalo em que está o dado a ser retornado. ATIVIDADE 1 Para utilizar as funções matemáticas, de pesquisa, estatísticas e lógicas vamos imaginar uma planilha de controle de vendas de uma loja de automóveis que comercializa três tipos de veículos: usados, zero- quilômetro e veículos com isenções de impostos para deficientes. A loja está dividida em três departamentos de vendas identificados com os nomes USADOS, ZERO KM e ISENÇÕES. A pasta LOJA.xlsx deve efetuar o controle das vendas e também identificar os vendedores que irão receber prêmios como incentivo, de acordo com os valores estipulados como metas para cada departamento. A pasta LOJA.xlsx é formada pelas planilhas: VENDAS – armazena os dados das vendas de cada vendedor, identifica os ganhadores dos prêmios e seus valores. EXCEL PARA EXECUTIVOS 13 RELATÓRIO – será preenchida com os resultados para análise. METAS – contém os valores das metas de vendas e dos prêmios para cada departamento. 1. Abra a pasta LOJA.xlsx. 2. Vamos preencher a coluna META da planilha VENDAS com os valores das metas de vendas para cada vendedor. Esses valores estão na planilha METAS. Por meio da função PROCV vamos procurar na região da planilha METAS pelo departamento do vendedor e trazer o respectivo valor da meta de vendas. Para isso, na célula D2 digite a seguinte fórmula: =PROCV(B2;METAS!$A$2:$B$4;2) Onde: B2: departamento do vendedor. Esse é o valor que será procurado na primeira coluna da região indicada. METAS!$A$2:$B$4: região da planilha METAS onde será feita a procura do valor. Observe que o endereço é absoluto para que, na cópia da fórmula para as células de baixo, esse valor não se modifique. 2: número da coluna onde está a informação que desejamos retornar. No nosso caso, o valor da meta de vendas. 3. Copie a fórmula para todos os vendedores. 4. Vamos agora preencher a coluna RECEBE PRÊMIO da planilha VENDAS. Para ter direito ao prêmio, o vendedor deve ter efetuado um total de vendas maior ou igual à sua meta de vendas. Para verificar se o vendedor tem direito ao prêmio, vamos utilizar a função SE. Na célula E2 digite a fórmula: EXCEL PARA EXECUTIVOS 14 =SE(C2>=D2;"sim";"não") Onde: C2>=D2: condição que será avaliada. No nosso caso, verificar se o valor da venda é maior ou igual ao valor da meta. “sim”: valor que será colocado na célula se a condição avaliada for verdadeira. No nosso caso, colocar o valor sim para os vendedores que venderam um total igual ou maior do que a meta. ”não”: ”: valor que será colocado na célula se a condição avaliada for falsa. No nosso caso, colocar o valor não para os vendedores que venderam um total menor do que a meta. 5. Copie a fórmula para todos os vendedores. 6. Para calcular o valor do prêmio de cada vendedor contemplado, vamos ter que calcular o número de ganhadores e o valor individual do prêmio para cada departamento na planilha METAS. O número de ganhadores será obtido pela função CONT.SES, que vai procurar pelo departamento e se o vendedor merece o prêmio. Na célula D2 da planilha METAS digite a fórmula: =CONT.SES(VENDAS!$B$2:$B$21;METAS!A2;VENDAS!$E$2:$E$21;"sim") Onde: VENDAS!$B$2:$B$21: região da planilha VENDAS onde será procurado pelo nome do departamento. METAS!A2: departamento que será procurado na região indicada. VENDAS!$E$2:$E$21: região da planilha VENDAS onde será procurado se o vendedor tem direito ao prêmio. "sim": valor que será procurado na região indicada. 7. Copie a fórmula para o restante dos departamentos. 8. O prêmio deve ser dividido entre os ganhadores. Então, para saber quanto cada ganhador deve receber, basta dividir o valor do prêmio pelo número de ganhadores. No entanto, o departamento ZERO KM não teve nenhum vendedor contemplado. Nesse caso, a nossa operação de divisão retornaria um erro (#DIV/0!). Para evitar esse erro vamos utilizar a função SEERRO que vai colocar o valor zero na célula caso estejamos dividindo o valor do prêmio por zero ganhadores. Na célula E2 da planilha METAS digite a fórmula: =SEERRO(C2/D2;0) Onde: C2/D2: valor que deverá ser calculado caso não exista erro na fórmula. 0: valor que deve ser colocado na célula caso exista erro no cálculo. 9. Copie a fórmula para o restante dos departamentos. 10. Voltando à planilha VENDAS, vamos preencher a coluna VALOR DO PRÊMIO para os vendedores contemplados. Para isso vamos utilizar as funções SE e PROCV para verificar se o vendedor tem direito ao prêmio e, se tiver, quanto receberá. Na célula F2 da planilha VENDAS digite a fórmula: =SE(E2="sim";PROCV(B2;METAS!$A$2:$E$4;5);0) Onde: E2="sim": condição que será avaliada, ou seja, verifica se o vendedor tem direito ao prêmio. PROCV(B2;METAS!$A$2:$E$4;5): se o vendedor recebe prêmio, procura pelo valor individual na planilha METAS. 0: coloca o valor zero se o vendedor não recebe prêmio. 11. Copie a fórmula para todos os vendedores. Para preencher a coluna CONCEITO a loja adotou os seguintes critérios: Se venda <= 0 então a mensagem será “Verificar vendedor”; Se venda >= meta de vendas a mensagem será “Excelente desempenho” Se venda < meta de vendas e >= 80% da meta de vendas, a mensagem será “Bom desempenho”; EXCEL PARA EXECUTIVOS 15 Se não ocorrer nenhuma das situações anteriores, a mensagem será “Desempenho regular”; Como podemos perceber, para construir a fórmula na célula, vamos ter que utilizar funções SE aninhadas. Para melhor visualização dessa fórmula, vamos escrever o raciocínio lógico da seguinte forma: SE valor da venda <= 0 ENTÃO “Verificar vendedor” SENÃO SE valor da venda >= meta de vendas ENTÃO “Excelente desempenho” SENÃO SE valor da venda < meta E valor da venda >= 80% da meta ENTÃO “Bom desempenho” SENÃO “Desempenho regular” 12. Na célula G2 digite a seguinte fórmula: =SE(C2<=0;"Verificar vendedor";SE(C2>=D2;"Excelente desempenho"; SE(E(C2<D2;C2>=D2*80%);"Bom desempenho";"Desempenho regular"))) 13. Copie a fórmula para o restante dos vendedores. 14. A loja também oferece um prêmio extra para os vendedores que obtiveram bom desempenho como conceito, contemplando-os com um valor referente a 1% da sua meta de vendas. Vamos então preencher a coluna PRÊMIO EXTRA. Na célula H2 digite a fórmula: =SE(G2="Bom desempenho";D2*1%;0) Vamos agora preencher a planilha RELATÓRIO. 15. Na célula B2 vamos calcular o valor da maior venda efetuada. Para isso, digite a fórmula: =MÁXIMO(VENDAS!C2:C21) 16. De forma análoga, vamos calcular na célula B3 o valor da menor venda efetuada. Digite a fórmula: =MÍNIMO(VENDAS!C2:C21) 17. Na célula B4 vamos calcular a média de vendas. Como a média é calculada somando-se os valores e dividindo o resultado pelo número de elementos, se não houver elementos será feita uma tentativa de divisão por zero, ocasionando um erro. Por esse motivo, vamos utilizar a função SEERRO juntamente com a função MÉDIA. Digite a fórmula: =SEERRO(MÉDIA(VENDAS!C2:C21);0) 18. Na célula B5 vamos calcular a soma total de vendas. Digite a fórmula: =SOMA(VENDAS!C2:C21) 19. Vamos agora verificar qual foi o vendedor que realizou a maior venda e a qual departamento pertence. Para isso vamos utilizar a função ÍNDICE juntamente com a função CORRESP. Na célula A14 digite a seguinte fórmula: =ÍNDICE(VENDAS!A2:A21;CORRESP(B2;VENDAS!C2:C21;0))Onde: VENDAS!A2:A21: região da planilha VENDAS onde será procurado o nome do vendedor. CORRESP(B2;VENDAS!C2:C21;0): número da linha que contém o nome do vendedor. Nesse caso, temos os seguintes parâmetros para a função CORRESP: B2: valor da maior venda. VENDAS!C2:C21: região da planilha VENDAS onde será procurado o valor da maior venda. 0: localiza o primeiro valor na lista. 20. De forma análoga, vamos procurar pelo departamento do vendedor que efetuou a maior venda. Na célula B14 digite a fórmula: EXCEL PARA EXECUTIVOS 16 =ÍNDICE(VENDAS!B2:B21;CORRESP(B2;VENDAS!C2:C21;0)) 21. Como foi feito para a maior venda, vamos procurar pelo vendedor que efetuou a menor venda e o departamento ao qual pertence. Na célula A17 digite a fórmula: =ÍNDICE(VENDAS!A2:A21;CORRESP(B3;VENDAS!C2:C21;0)) Na célula B17 digite a fórmula: =ÍNDICE(VENDAS!B2:B21;CORRESP(B3;VENDAS!C2:C21;0)) Vamos agora preencher os quadros relativos a cada departamento individualmente. As fórmulas que serão mostradas para o departamento USADOS serão também utilizadas para os outros departamentos. 22. Na célula E2 da planilha RELATÓRIO vamos utilizar a função CONT.SE para calcular o total de vendedores do departamento USADOS. Digite a fórmula: =CONT.SE(VENDAS!B2:B21;RELATÓRIO!D1) Onde: VENDAS!B2:B21: região da planilha VENDAS onde será procurado o nome do departamento. RELATÓRIO!D1: nome do departamento procurado. 23. Vamos preencher o número de ganhadores do departamento USADOS procurando pelo seu valor na planilha METAS. Para isso vamos utilizar a função PROCV. Na célula E3 digite a fórmula: =PROCV(D1;METAS!A2:D4;4) 24. Para calcular a média de vendas total para o departamento USADOS, vamos utilizar a função MÉDIASE. Vamos também utilizar a função SEERRO para evitar o erro de divisão por zero. Na célula E4 digite a fórmula: =SEERRO(MÉDIASE(VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!C2:C21);0) A função MÉDIASE tem os seguintes argumentos: VENDAS!B2:B21: região da planilha VENDAS onde será procurado o nome do departamento. RELATÓRIO!D1: nome do departamento que será procurado. VENDAS!C2:C21: região da planilha VENDAS onde será calculada a média. 25. Na célula E5 vamos calcular a média de vendas para os não ganhadores de prêmio do departamento USADOS. Para isso utilizaremos a função MÉDIASES juntamente com a função SEERRO. Digite a fórmula: =SEERRO(MÉDIASES(VENDAS!C2:C21;VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!E2:E21;"<>sim") ;0) A função MÉDIASES tem os seguintes argumentos: VENDAS!C2:C21: região da planilha VENDAS onde será calculada a média. VENDAS!B2:B21: região da planilha VENDAS onde será procurado o departamento. RELATÓRIO!D1: nome do departamento. VENDAS!E2:E21: região da planilha VENDAS onde serão procurados os não ganhadores. “<>sim”: parâmetro que indica os não ganhadores. 26. Utilizando o mesmo raciocínio vamos calcular a média de vendas para os ganhadores de prêmio do departamento USADOS. Na célula E6 digite a fórmula: =SEERRO(MÉDIASES(VENDAS!C2:C21;VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!E2:E21;"sim");0) 27. Da mesma forma que calculamos as médias de vendas, vamos calcular as somas de vendas para o departamento, dos não ganhadores e dos ganhadores de prêmio. As funções que iremos utilizar são SOMASE e SOMASES. Para o cálculo da soma de vendas total no departamento USADOS digite a seguinte fórmula na célula E7: =SOMASE(VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!C2:C21) 28. Na célula E8 vamos calcular a soma de vendas dos não ganhadores. Digite a fórmula: =SOMASES(VENDAS!C2:C21;VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!E2:E21;"<>sim") 29. Na célula E9 vamos calcular a soma de vendas para os ganhadores. Digite: EXCEL PARA EXECUTIVOS 17 =SOMASES(VENDAS!C2:C21;VENDAS!B2:B21;RELATÓRIO!D1;VENDAS!E2:E21;"=sim") De forma análoga, preencha as informações referentes aos quadros ZERO KM e ISENÇÕES. FUNÇÕES FINANCEIRAS Função VF Objetivo: retornar o valor futuro de um investimento ao final de um período, de acordo com pagamentos e taxa de juros constantes. Sintaxe: VF(taxa;nper;pgto;[vp];[tipo]) Argumentos: • taxa: argumento obrigatório que corresponde à taxa de juros mensal; • nper: argumento obrigatório que corresponde ao número de parcelas; • pgto: argumento obrigatório que corresponde ao valor da parcela; • vp: argumento opcional que corresponde ao valor presente; • tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0. Função VP Objetivo: retornar o valor presente de um investimento ao final de um período, de acordo com pagamentos e taxa de juros constantes. Sintaxe: VP(taxa;nper;pgto;[vf];[tipo]) Argumentos: • taxa: argumento obrigatório que corresponde à taxa de juros mensal; • nper: argumento obrigatório que corresponde ao número de parcelas; • pgto: argumento obrigatório que corresponde ao valor da parcela; • vf: argumento opcional que corresponde ao valor futuro; • tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0. Função PGTO Objetivo: retornar o valor da parcela de um investimento de acordo com pagamentos e taxa de juros constantes. Sintaxe: PGTO(taxa;nper;vp;[vf];[tipo]) Argumentos: • taxa: argumento obrigatório que corresponda à taxa de juros mensal; • nper: argumento obrigatório que corresponda ao número de parcelas; • vp: argumento obrigatório que corresponda ao valor presente ou principal; EXCEL PARA EXECUTIVOS 18 • vf: argumento opcional que corresponda ao valor futuro; • tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0. Função NPER Objetivo: retornar o número de parcelas para um investimento de acordo com pagamentos e taxa de juros constantes. Sintaxe: NPER(taxa;pgto;vp;[vf];[tipo]) Argumentos: • taxa: argumento obrigatório que corresponde à taxa de juros mensal; • pgto: argumento obrigatório que corresponde ao valor da parcela; • vp: argumento obrigatório que corresponde ao valor presente ou principal; • vf: argumento opcional que corresponde ao valor futuro; • tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0. ATIVIDADE 2 1. Abra a pasta FINANCEIRO.xlsx. 2. Suponha que você deseja investir R$ 500,00 em uma aplicação que rende 1,5% ao mês em um período de 60 meses. Para calcular quanto teremos ao final desse período vamos utilizar a função VF. Na célula D4 digite a fórmula: =VF(B3;A3;C3) 3. Suponha que você financiou um bem em 24 meses, a uma taxa mensal de juros de 3,5% e com uma prestação de R$ 78,00 por mês. Para calcular quanto custaria esse bem se fosse pago à vista vamos utilizar a função VP. Na célula D7 digite a fórmula: =VP(B7;A7;C7) 4. Suponha que você vá adquirir um bem no valor de R$ 35.000,00 e deseja financiá-lo em 24 meses com uma taxa mensal de juros de 0,5%. Para calcular o valor da parcela vamos utilizar a função PGTO. Na célula C11 digite a fórmula: =PGTO(B11;A11;D11) EXCEL PARA EXECUTIVOS 19 5. Suponha que você deseja investir R$ 1.551,22 por mês em uma aplicação que rende uma taxa de juros mensal de 2,4%. Para calcular o número de parcelas que serão necessárias para obter R$ 35.000,00 com o investimento, vamos utilizar a função NPER. Na célula A15 digite: =NPER(B15;C15;D15) TRABALHANDO COM DATAS E HORA Visualização de datas Para o manuseio de datas e horas em nossas planilhas, o Excel oferece uma grande variedade de funções e procedimentos de formatação que possibilitam maior flexibilidade no cálculo de dados desse tipo. Muitas vezes é necessário visualizar a data de uma forma diferente, como, por exemplo, pelo nome do mês, pelo nome do diada semana ou mesmo pela data no seu formato completo. Para isso, vamos utilizar os recursos de formatação disponíveis para datas. ATIVIDADE 3 1. Abra a pasta FORMATACAO DE DATAS.xlsx. 2. Na coluna B, vamos mostrar o número do dia da data armazenada. Selecione o intervalo B2:B15. 3. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial. 4. Na guia Número, selecione a categoria Personalizado. Devemos informar nesse campo o formato desejado para o(s) dado(s) selecionado(s). Na formatação de datas, quando queremos mostrar o número do dia, devemos digitar uma das opções que se seguem: D dias formados por um algarismo são exibidos com um algarismo. DD dias formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial. DDD exibe o nome do dia de forma abreviada, usando as três primeiras letras. DDDD exibe o nome do dia por extenso. 5. Clique em OK. 6. Agora vamos mostrar o nome do dia da semana na coluna C. Para isso, selecione o intervalo C2:C15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial. 7. Na guia Número, selecione a categoria Personalizado. 8. Apague o que está no campo Tipo e digite dddd. Clique em OK. EXCEL PARA EXECUTIVOS 20 9. Vamos agora exibir na coluna D o nome do mês por extenso. Selecione o intervalo D2:D15. 10. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial. 11. Na guia Número, selecione a categoria Personalizado. 12. Apague o que está no campo Tipo e digite mmmm. A formatação personalizada para datas em relação à exibição do mês pode ser feita por uma das opções: M meses que são formados por um algarismo são exibidos com um algarismo. MM meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial. MMM exibe o nome do mês de forma abreviada usando as três primeiras letras. MMMM exibe o nome do mês por extenso. 13. Clique em OK. 14. Vamos agora mostrar a data por extenso. Por exemplo: para a data 21-06-2011, deverá ser mostrado terça-feira, 21 de junho de 2011. Selecione o intervalo E2:E15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial. No campo Tipo, digite o seguinte: dddd”, “dd” de “mmmm” de “aaa Onde: dddd nome do dia da semana. “ , ” coloca um espaço, vírgula, espaço. dd número do dia. “ de ” coloca espaço, a palavra “de”, espaço. mmmm nome do mês por extenso. “ de ” coloca espaço, a palavra “de”, espaço. aaa número do ano. No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano, enquanto as representações aaa ou aaaa formatam a data para que apareçam os quatro dígitos do ano. Existe uma grande diferença entre conteúdo e formato. Conteúdo é o valor original do dado armazenado na célula. Formato é como ele está sendo mostrado. Quando efetuarmos cálculos com células formatadas, o conteúdo será considerado, não o formato. FUNÇÕES DE DATA E HORA Função HOJE Objetivo: retornar a data de hoje formatada como data. Sintaxe: HOJE() Essa função não possui argumentos. Apesar disso, devemos digitar os parênteses. EXCEL PARA EXECUTIVOS 21 Função ANO Objetivo: retornar o ano da data informada como um número inteiro no intervalo de 1900 a 9999. Sintaxe: ANO(data_informada) Argumento: • data_informada: data que se deseja retornar o ano. Função MÊS Objetivo: retornar o número do mês da data informada como um número inteiro no intervalo de 1 a 12. Sintaxe: MÊS(data_informada) Argumento: • data_informada: data que se deseja retornar o mês. Função DIA Objetivo: retornar o dia da data informada como um número inteiro no intervalo de 1 a 31. Sintaxe: DIA(data_informada) Argumento: • data_informada: data que se deseja retornar o dia. Função DIA.DA.SEMANA Objetivo: retornar o número do dia da semana da data informada como um número inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. Sintaxe: DIA.DA.SEMANA(data_informada;tipo) Argumentos: • data_informada: data que se deseja retornar o número do dia da semana. • tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores: TIPO VALOR RETORNADO 1 ou omitido Números 1 (domingo) a 7 (sábado) 2 Números 1 (segunda-feira) a 7 (domingo) 3 Números 0 (segunda-feira) a 6 (domingo) 11 Números 1 (segunda-feira) a 7 (domingo) EXCEL PARA EXECUTIVOS 22 12 Números 1 (terça-feira) a 7 (segunda-feira) 13 Números 1 (quarta-feira) a 7 (terça-feira) 14 Números 1 (quinta-feira) a 7 (quarta-feira) 15 Números 1 (sexta-feira) a 7 (quinta-feira) 16 Números 1 (sábado) a 7 (sexta-feira) 17 Números 1 (domingo) a 7 (sábado) O Excel armazena datas como números de série sequenciais para que possam ser utilizados em cálculos. Por padrão, 1º de janeiro de 1900 corresponde ao número 1 da série e, por exemplo, o número 1º de janeiro de 2013 corresponde ao número 41.275, porque está 41.275 dias após 1º de janeiro de 1900. Função DIATRABALHO.INTL Objetivo: retornar os dias úteis a projetar a partir de uma data informada, considerando os dias da semana que compõem o fim de semana e os feriados que possam ocorrer no período. Sintaxe: DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados) Argumentos: • data_inicial: data a partir da qual se deseja projetar dias úteis. • dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor 0 gera a data inicial. • fim_de_semana: argumento opcional. Indica os dias da semana que serão considerados como dias que compõem o final de semana. Pode ter os seguintes valores: FIM_DE_SEMANA DIAS DE FIM DE SEMANA 1 ou omitido Sábado, Domingo 2 Domingo, Segunda-feira 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas Domingo 12 Apenas Segunda-feira 13 Apenas Terça-feira EXCEL PARA EXECUTIVOS 23 14 Apenas Quarta-feira 15 Apenas Quinta-feira 16 Apenas Sexta-feira 17 Apenas Sábado • feriados: argumento opcional composto pelo conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis. Função DIATRABALHOTOTAL.INTL Objetivo: retornar o número de dias úteis entre duas datas considerando os finais de semana e feriados no período. Sintaxe: DIATRABALHOTOTAL.INTL(data_inicial;data_final;fim_de_semana;feriados) Argumentos: • data_inicial: data a partir da qual se deseja projetar dias úteis. • data_final: data até a qual se deseja projetar dias úteis. • fim_de_semana: argumento opcional. Indica que dias da semana compõem o final de semana. Pode ter os seguintes valores: FIM_DE_SEMANA DIAS DE FIM DE SEMANA 1 ou omitido Sábado, Domingo 2 Domingo, Segunda-feira 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas Domingo 12 Apenas Segunda-feira 13 Apenas Terça-feira 14 Apenas Quarta-feira 15 Apenas Quinta-feira 16 Apenas Sexta-feira EXCEL PARA EXECUTIVOS 24 17 Apenas Sábado • feriados: argumento opcional, que é um conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis. Função HORA Objetivo: retornar a hora de um valor de tempo como um inteiro, variando de 0 (12:00 a.m.) a 23 (11:00 p.m.). Sintaxe: HORA(valor_tempo) Argumento: • valor_tempo: horário que contém a hora que desejamos encontrar. Função MINUTO Objetivo: retornar os minutos de um valor de tempo como um número inteiro que varia de 0 a 59. Sintaxe: MINUTO(valor_tempo) Argumento: • valor_tempo: horário que contém os minutos que desejamos encontrar.ATIVIDADE 4 Vamos considerar uma empresa que trabalha com um estoque de determinada peça que é atualizado diariamente. Em todos os dias úteis entram novas unidades dessa peça e saem outras no processo de venda. O estoque deve ser calculado diariamente. A empresa trabalha de segunda a sexta-feira e, todo início de mês, já deixa preparada sua planilha de controle com todos os dias úteis daquele mês para que seja preenchida com os dados e cálculo de seu estoque diário. A planilha que será desenvolvida é referente ao mês de maio de 2013. Nesse mês, os dias 1 e 30 foram feriados e não houve expediente no dia 31. 1. Abra a pasta ESTOQUE.xlsx. EXCEL PARA EXECUTIVOS 25 2. Na pasta ESTOQUE.xlsx existem duas planilhas: ESTOQUE e FERIADOS. Na planilha ESTOQUE vamos projetar todos os dias úteis do mês de maio de 2013 e calcular o estoque diário de peças. A planilha FERIADOS contém os dias do mês que não deverão ser considerados como dias úteis. Clique na célula B2 e digite a data 01/05/2013, que corresponde ao primeiro dia do mês de maio/2013. 3. Formate a célula para aparecer somente o nome do mês. 4. Na célula C2 vamos mostrar o ano da data considerada. Digite: =ANO(B2) 5. Na célula A5 vamos colocar o primeiro dia útil de maio, que será a partir do último dia de abril. Digite a fórmula: =DIATRABALHO.INTL(B2-1;1;1;FERIADOS!A:A) Onde: B2-1: último dia de abril 1: indica que desejamos o primeiro dia útil a partir da data 1: indica que o fim de semana é composto por sábado e domingo FERIADOS!A:A: região da planilha FERIADOS onde estão os dias de maio/2013 que não são considerados como dias úteis. 6. Vamos agora calcular o próximo dia útil a partir da célula A5. Na célula A6 digite a fórmula: =DIATRABALHO.INTL(A5;1;1;FERIADOS!A:A) 7. Copie a fórmula para as células de baixo terá uma lista de dias úteis do mês de maio/2013. 8. Copie somente os valores da coluna A para a coluna B e formate os dados para que apareçam os nomes dos dias da semana. 9. Calcule as colunas para estoque do dia e estoque anterior. ATIVIDADE 5 Uma empresa contrata mão de obra terceirizada para alguns serviços. Para isso conta com a prestação de serviços de três escritórios: Escritório X, Escritório Y e Escritório Z. Esses três escritórios trabalham por contrato, recebem por hora trabalhada e a carga horária diária varia em cada contrato, trabalhando de segunda-feira a sábado. Vamos calcular os valores que devem ser pagos para cada contrato, considerando que esse valor deve ser calculado em função dos dias úteis trabalhados. 1. Abra a pasta TERCEIRIZADAS.xlsx. 2. Na célula F6 digite a fórmula: =DIATRABALHOTOTAL.INTL(C6;D6;11;FERIADOS!A:A) 3. Copie a fórmula para todos os contratos. 4. Calcule quanto deve ser pago a cada contrato. EXCEL PARA EXECUTIVOS 26 ATIVIDADE 6 Uma lan house oferece acesso à Internet cobrando um valor por tempo de utilização. O cálculo é efetuado considerando o tempo exato, ou seja, todos os minutos são cobrados. No horário da manhã, das 9h às 11h59, é oferecido um desconto de 20% no valor da hora de utilização dos computadores. A planilha a seguir tem como objetivo controlar os valores pagos pelos usuários de um determinado computador no dia 03/06/2013. 1. Abra a pasta LAN HOUSE.xlsx. CAP2_FIG8 2. Vamos calcular o tempo de utilização de cada usuário, subtraindo a hora inicial da hora final. Na célula D5 digite a fórmula: =C5-B5 3. Vamos calcular o número de horas do tempo total. Na célula E5 digite: =HORA(D5) 4. Vamos calcular o número de minutos do tempo total. Na célula F5 digite: =MINUTO(D5) 5. Vamos calcular o tempo total em minutos. Na célula G5 digite: =E5*60+F5 6. Para calcular o valor do minuto devemos considerar que se a hora inicial for menor do que 12, o usuário terá um desconto de 20% no valor da hora. Na célula H5 digite: =SE(HORA(B5)<=11;($B$2*80%)/60;$B$2/60) 7. Para calcular o valor total a ser pago pelo usuário, digite a seguinte fórmula na célula I5: =G5*H5 8. Copie as fórmulas para todos os usuários. EXCEL PARA EXECUTIVOS 27 CAPÍTULO 3 Validação de dados e formatação condicional EXCEL PARA EXECUTIVOS 28 VALIDAÇÃO DE DADOS A validação de dados é utilizada para controlar o valor que será inserido em uma célula da planilha, evitando a entrada de dados inválidos. Para acessar a opção de validação de dados, devemos acessar a guia Dados, ferramenta Validação de Dados. ATIVIDADE 1 1. Abra a pasta CADASTRO.xlsx CAP3_FIG2 2. Vamos restringir a entrada de dados na coluna Curso para um valor que esteja na lista de cursos da planilha CURSOS. Selecione a região A4:A20. 3. Clique na ferramenta Validação de Dados. 4. Na guia Configurações, na caixa Permitir, selecione Lista. Na caixa Fonte indique a coluna A da planilha Cursos. Clique em OK. CAP3_FIG3 Observe que agora ao selecionar qualquer uma das células do intervalo é exibida uma seta que, ao ser clicada, mostra um menu suspenso com os nomes dos cursos para escolha. EXCEL PARA EXECUTIVOS 29 Se digitar um valor que não consta da lista, o Excel envia uma mensagem de erro, oferecendo as opções Repetir (permite uma nova digitação na célula), Cancelar (cancela a digitação anterior) e Ajuda. 5. Selecione a região B4:B20. Vamos estabelecer que a data deve ser maior do que 01/01/2014. 6. Na ferramenta Validação de Dados na caixa Permitir selecione Data, na caixa Dados selecione é maior do que e na caixa Data de início digite 01/01/2014. 7. Vamos configurar uma mensagem que será exibida quando a célula for selecionada. Ainda com a janela Validação de dados aberta, na guia Mensagem de entrada, na caixa Título digite Data do Curso e na caixa Mensagem de entrada digite A data do curso deve ser maior do que 01/01/2014. 8. Vamos configurar uma mensagem de alerta de erro para ser mostrada quando o dado inserido na célula não corresponder ao critério estabelecido. Na guia Alerta de erro, na caixa Título digite ATENÇÃO! e na caixa Mensagem de erro digite A data do curso deve ser maior do que 01/01/2014. EXCEL PARA EXECUTIVOS 30 9. Pressione OK e verifique as condições colocadas. 10. Crie uma validação de dados para a entrada dos departamentos, onde o usuário possa escolher o valor dentro de uma lista. A relação de departamentos está na planilha DEPARTAMENTOS. 11. Crie uma validação de dados para a coluna Custo que só permita entrada de valores entre R$ 300,00 e R$ 1.500,00. FORMATAÇÃO CONDICIONAL O recurso de formatação condicional permite melhor visualização dos dados, definindo critérios para exibição. ATIVIDADE 2 1. Abra a pasta VENDAS.xlsx. 2. Vamos formatar a coluna Classificação faixas para mostrar as células que contém Faixa 1 com preenchimento em azul, Faixa 2 em vermelho, Faixa 3 em amarelo e Faixa 4 em verde. Selecione os dados da coluna. 3. Na guia PÁGINA INICIAL selecione Formatação Condicional e, a seguir, Nova regra. 4. Selecione Formatar apenas células que contenham. No quadro Edite a Descrição da Regra selecione é igual a e digite Faixa 1. EXCEL PARA EXECUTIVOS 31 5. Clique em Formatar... 6. Na guia Preenchimento escolha a cor azul e clique em OK. 7. Clique em OK novamente e repita o procedimento para as demais faixas. 8. Formate as colunas restantes da forma que achar mais adequada utilizando as opções do menu apresentado na ferramenta Formatação Condicional. EXCEL PARA EXECUTIVOS 32 CAPÍTULO 4 Classificação de dados, totalização e filtros EXCEL PARA EXECUTIVOS 33 CLASSIFICAÇÃO DE DADOS A classificação de dados em uma planilha auxilia a visualização das informações, permitindo uma rápida procura daquilo que desejamos encontrar. Os dados podem ser classificados por ordem alfabética, ordem de data e ordem numérica. ATIVIDADE 1 1. Abra a pasta DADOS.xlsx. 2. Selecione qualquercélula da coluna A. 3. Na guia Dados, grupo Classificar e Filtrar, selecione Do menor para o maior. A planilha ficou classificada por ordem alfabética de nome de funcionário. Da mesma forma que a classificação em ordem alfabética foi efetuada para a coluna FUNCIONÁRIO, você pode classificar a planilha por qualquer coluna. Classificação por mais de um campo Podemos classificar os dados da planilha por mais de um campo. ATIVIDADE 2 1. Posicione o cursor em qualquer célula da planilha. 2. Na guia Dados, grupo Classificar e Filtrar, selecione Classificar. Na caixa de diálogo que se abre podemos classificar os dados da planilha considerando vários campos ao mesmo tempo. 3. Vamos classificar a planilha por DEPARTAMENTO e, depois, por FUNCIONÁRIO. No campo Coluna, selecione DEPARTAMENTO. Clique no botão Adicionar Nível e selecione o campo FUNCIONÁRIO. EXCEL PARA EXECUTIVOS 34 4. Clique em OK e a planilha ficará classificada por DEPARTAMENTO e, dentro de cada departamento, por FUNCIONÁRIO. Criando lista personalizada Suponha que você necessite visualizar os dados da sua planilha classificados de acordo com uma ordem que não seja nenhuma das oferecidas pelo Excel. Por exemplo, vamos classificar a nossa planilha utilizando a seguinte ordem no campo CARGO: Gerente, Assistente, Analista e Técnico. Como essa é uma lista personalizada, vamos cria-la no Excel. Para isso vamos desfazer as classificações criadas anteriormente. ATIVIDADE 3 1. Posicione o cursor em qualquer célula da planilha. 2. Na guia Dados, grupo Classificar e Filtrar, selecione Classificar. Clique em Excluir Nível. 3. No campo Ordem selecione Lista Personalizada... 4. Clique na opção NOVA LISTA e em Adicionar. 5. Digite as opções pressionando Enter para colocar os valores um em cada linha. O texto digitado deve ser exatamente igual ao texto digitado na planilha. FILTROS Utilizamos os filtros em planilhas para localizar rapidamente conjuntos de dados de acordo com os critérios informados. Serão mostradas somente as linhas que contêm os dados filtrados. Podemos filtrar os dados por meio de uma ou mais colunas. EXCEL PARA EXECUTIVOS 35 Auto Filtro O auto filtro é aplicado diretamente nas colunas, trazendo os resultados de acordo com os critérios escolhidos. ATIVIDADE 4 1. Com a pasta DADOS.xlsx aberta, clique em qualquer célula da planilha. 2. Na guia Dados, opção Classificar e Filtrar, clique em Filtro. Observe que ao lado dos rótulos de cada coluna aparecem botões para a aplicação do filtro. 3. Clique no botão de filtro da coluna DEPARTAMENTO. CAP4_FIG6 4. Deixe selecionado somente o departamento Financeiro. Clique em OK. EXCEL PARA EXECUTIVOS 36 A planilha exibirá somente as linhas correspondentes ao departamento Financeiro. Observe que podemos filtrar os dados por outros critérios. No caso do campo DEPARTAMENTO, que contém dados do tipo texto, o menu apresentado para filtro oferece outras opções. Nesse momento, se criarmos um outro filtro, os dados serão selecionados dentro dessa última região considerada no filtro anterior. Para desativar os filtros basta clicar novamente na ferramenta Filtro. Filtros avançados A utilização de um filtro avançado é baseada na utilização de uma planilha (ou regiões da planilha) que contenha os critérios pelos quais desejamos obter os dados. Dessa forma podemos, em um mesmo filtro, obter dados que obedecem a vários critérios que podem dizer respeito a várias colunas da planilha. Com a pasta DADOS.xlsx ainda aberta e com o auto filtro desativado, vamos trabalhar com filtros avançados. ATIVIDADE 5 1. Vamos filtrar os dados referentes ao departamento Administrativo ou todos os dados referentes ao departamento Recursos Humanos. Para isso, na planilha CRITÉRIOS, vamos digitar os critérios referentes a esse filtro conforme a figura abaixo: Observe que digitamos o nome da coluna na qual deverá ser aplicada o filtro e, logo abaixo, os nomes dos departamentos desejados em células adjacentes. A forma de colocação das informações do critério é muito importante. Nesse caso, como os nomes dos departamentos estão uma abaixo do outro, a condição que é avaliada é o operador OU, ou seja, queremos informações do departamento Administrativo OU Recursos Humanos. Se essas condições fossem colocadas lado a lado, o operador para avaliação seria E, não trazendo nenhum resultado. 2. Crie uma nova planilha de nome Filtro 1. 3. Na guia Dados, grupo Classificar e Filtrar, selecione Avançado. EXCEL PARA EXECUTIVOS 37 4. Selecione Copiar para outro local. Em intervalo da lista, selecione a região A1:F103 da planilha CADASTRO. Em Intervalo de critérios selecione a região A1:A3 da planilha CRITÉRIOS. Em Copiar para selecione a célula A1 da planilha Filtro 1. Clique em OK. A diferença entre o Auto Filtro e o Filtro Avançado, além da possibilidade de utilizar vários critérios, é que podemos guardar os resultados em outras planilhas. 5. Vamos filtrar todos os Assistentes do departamento Administrativo com idade menor ou igual a 30 anos. Para isso, na planilha CRITÉRIOS, vamos digitar os critérios referentes a esse filtro conforme a figura abaixo: Nesse caso digitamos os critérios um ao lado do outro, em células adjacentes. O operador considerado para o filtro é E. 6. Crie uma nova planilha de nome Filtro 2. 7. Na guia Dados, grupo Classificar e Filtrar, selecione Avançado. 8. Selecione Copiar para outro local. Em intervalo da lista, selecione a região A1:F103 da planilha CADASTRO. Em Intervalo de critérios selecione a região C1:E2 da planilha CRITÉRIOS. Em Copiar para selecione a célula A1 da planilha Filtro 2. Clique em OK. Você pode construir filtros com critérios que envolvem funções OU e funções E dentro da mesma expressão a ser avaliada. É necessário, porém, a colocação correta dos valores nas células de critério para que não haja um resultado inesperado. EXCEL PARA EXECUTIVOS 38 TOTAIS E SUB-TOTAIS Podemos totalizar os dados de nossas planilhas aplicando o recurso Subtotal. Vamos totalizar o campo SALÁRIO da planilha CADASTRO por DEPARTAMENTO. ATIVIDADE 6 1. Classifique a planilha CADASTRO por DEPARTAMENTO. A classificação é necessária para que os itens que formam um mesmo grupo fiquem juntos. 2. Na guia DADOS, grupo Estrutura de Tópicos, clique em Subtotal. 3. No campo A cada alteração em: selecione DEPARTAMENTO Escolha a função Soma Marque o campo SALÁRIO para ser totalizado Clique em OK EXCEL PARA EXECUTIVOS 39 CAPÍTULO 5 Gráficos e Mini Gráficos EXCEL PARA EXECUTIVOS 40 MINI GRÁFICOS A utilização de mini gráficos em células de uma planilha permite representar visualmente e mostrar uma tendência em seus dados. ATIVIDADE 1 1. Abra a pasta EVOLUÇÃO DE VENDAS.xlsx. 2. Clique na célula H2. 3. Na guia INSERIR, grupo Minigráficos, selecione Linha. 4. No campo Intervalo de dados informe a região B2:G2. No campo Intervalo de locais deixe informada a célula H2. Clique em OK. 5. Copie o mini gráfico gerado para as linhas de baixo, exatamente da mesma forma que copiamos fórmulas. Você pode alterar ou formatar o mini gráfico utilizando as ferramentas da guia DESIGN que é ativada quando uma das células que contêm mini gráficos seja selecionada. EXCEL PARA EXECUTIVOS 41 GRÁFICOS A utilização de gráficos permite melhor visualização de dados em uma planilha. ATIVIDADE 2 1. Selecione a faixa de dados A1:G8. 2. Na guia INSERIR, grupo Gráficos selecione o Gráfico de Colunas. Com o gráfico selecionado, por meio das guias DESIGN e FORMATAR é possível movê-lo, aumentar ou diminuir seu tamanho, modificar os dados, formatar seus elementos. 3. Selecione a faixa de dados B1:G1 e B9:G9 (utilize a tecla CTRL). 4. Pressione a tecla F11. 5. Perceba que um novo gráfico de barras foicriado em uma nova planilha. Vamos transformar esse gráfico de barras em um gráfico de pizza. Na guia DESIGN selecione Alterar tipo de gráfico. 6. Escolha o tipo Pizza. 7. Selecione todo o gráfico e, na guia DESIGN, clique em Adicionar Elemento Gráfico. 8. Clique em Legenda e selecione Direita. 9. Clique novamente em Adicionar Elemento Gráfico. 10. Clique em Rótulos de Dados e, a seguir, selecione Mais Opções de Rótulo de Dados. 11. Selecione os campos Valor e Porcentagem. 12. Formate o gráfico da forma como achar melhor. EXCEL PARA EXECUTIVOS 42 CAPÍTULO 6 Tabelas e gráficos dinâmicos EXCEL PARA EXECUTIVOS 43 TABELAS DINÂMICAS O recurso relatório de tabela dinâmica permite resumir os dados de uma planilha possibilitando que sejam analisados e explorados de forma mais eficiente. Com a tabela dinâmica, podemos rapidamente visualizar os dados filtrados, agrupados e calculados, o que nos permite analisá-los por meio de diferentes perspectivas. ATIVIDADE 1 1. Abra a pasta Controle Bancario.xlsx. 2. Clique na célula A4 da planilha Contas. 3. Clique na ferramenta Tabela Dinâmica do grupo Tabelas da guia INSERIR. 4. Selecione a opção Nova Planilha, para que a tabela dinâmica seja criada em uma planilha independente. A seguir, clique em OK. 5. Uma nova planilha é aberta com as opções para trabalho com a tabela dinâmica. Também são apresentadas duas novas guias: ANALISAR e DESIGN. Essas guias somente Do lado direito da janela aparece um painel com os seguintes componentes: •Campos da Tabela Dinâmica: painel em que são mostrados os campos da planilha para que sejam escolhidos para fazer parte do relatório da tabela dinâmica. •Filtros: campos que serão os filtros principais do relatório. EXCEL PARA EXECUTIVOS 44 •Colunas: campos que serão os rótulos das colunas quando for necessário o cruzamento de informações. • Linhas: campos por meio dos quais os itens serão agrupados, ficando dispostos em linhas. • Valores: campos que terão seus dados somados, contados ou resumidos. 6. Vamos criar um relatório que permitirá o filtro por grupo e terá os movimentos indicados pelas descrições e pelos valores. Selecione o campo Grupo e arraste-o até o painel Filtros. 7. Selecione o campo Descrição e arraste-o até o painel Linhas. 8. Selecione o campo Valor e arraste-o até o campo Valores. 9. Na tabela criada observe que podemos filtrar os grupos. Para isso, basta clicar sobre a seta ao lado do campo e selecionar a opção desejada. 10. Também podemos selecionar as descrições. Clique na seta ao lado da célula Rótulos de Linha e selecione a(s) descrição(ões) desejada(s). 11. Para formatar o valor como unidade monetária, selecione a célula B3 e clique na ferramenta Configurações do Campo do grupo Campo Ativo da guia Analisar. ATIVIDADE 2 Vamos agora verificar como devemos proceder para atualizar dados na tabela dinâmica. 1. Selecione a planilha CONTAS. 2. Clique na célula C9 e modifique seu conteúdo para: Viagem a Salvador - Férias Coletivas. 3. Clique na planilha que contém a tabela dinâmica e observe que a informação não foi modificada. Para que a alteração feita na planilha seja refletida na tabela dinâmica, é necessário atualizá-la. Clique na ferramenta Atualizar do grupo Dados da guia ANALISAR. 4. Observe que a informação modificada foi atualizada. 5. Selecione a planilha Contas. 6. Insira uma nova linha antes da linha 13 da planilha. 7. Digite os valores: EXCEL PARA EXECUTIVOS 45 10-02-2015 Eventual Bolo de aniversário Saída 170 Itaú 8. Selecione a planilha com a tabela dinâmica. Observe que a atualização não foi efetuada. Atualize a tabela dinâmica e, se necessário, classifique a coluna. 9. Selecione a planilha Contas. 10. Inclua o seguinte registro no final da planilha: 26-12-2015 Eventual Direitos Autorais Entrada 2100 Bradesco 11. Observe na tabela dinâmica que, da mesma forma que nos casos anteriores, os dados não foram atualizados. Clique na ferramenta Atualizar. 12. Nesse caso, os dados não foram atualizados, pois inserimos uma nova linha no final da planilha Contas. Essa linha ficou fora da região especificada para a construção da tabela dinâmica. É necessário, então, corrigir a região a ser considerada. Na tabela dinâmica, clique na ferramenta Alterar Fonte de Dados do grupo Dados da guia ANALISAR. 13. Corrija o endereço da região considerada para CONTAS!$A$3:$F$60. Observe que a tabela dinâmica foi atualizada com o novo movimento. ATIVIDADE 3 Vamos agora visualizar os dados da planilha pelo campo Grupo, agrupados por Descrição, de tal forma que os valores sejam mostrados de forma cruzada com o movimento. Vamos criar uma nova tabela dinâmica. 1. Clique na célula A4 da planilha Contas e insira uma nova tabela dinâmica. 2. No painel Linhas, coloque o campo Grupo e, depois, Descrição. A ordem de colocação desses campos é fundamental, pois indica os níveis de agrupamento das informações. 3. No painel Valores, coloque o campo Valor. 4. Vamos visualizar as informações separadas por tipo de movimento. Coloque no painel Colunas o campo Movimento. EXCEL PARA EXECUTIVOS 46 5. Formate os valores para serem exibidos no formato monetário. ATIVIDADE 4 Vamos visualizar os dados resumidos por movimento e totalizados por mês. Para isso, vamos criar uma nova tabela dinâmica, que terá o campo Movimento como rótulo de linha e o campo Data como rótulo de coluna. 1. Crie uma nova tabela dinâmica. Coloque os campos Movimento em Linhas, Data em Colunas e Valor em Valores. Formate os valores para unidade monetária. Observe que as colunas correspondem às datas. 2. Vamos agrupar os campos para que os dados sejam resumidos por mês. Clique na célula B4 e, depois, na opção Agrupar Campo da ferramenta Agrupar da guia Analisar. CAP6_FIG5 3. A janela apresentada permite a mudança das datas inicial e final, bem como o agrupamento que desejamos efetuar. Podemos ter vários níveis de agrupamento, por exemplo: por ano, mês e trimestre. Clique em Meses e depois em OK. 4. Observe o resultado: ATIVIDADE 5 Por meio do recurso Linha do Tempo vamos exibir os dados resumidos por banco, mas que possam ser mostrados em períodos mensais, diários, trimestrais. Este recurso, disponível na versão 2013 do Excel, simplifica a comparação dos dados em uma tabela dinâmica considerando diferentes períodos. Assim, podemos filtrar datas de forma interativa sem que o campo referente à data esteja presente no relatório da tabela dinâmica. EXCEL PARA EXECUTIVOS 47 1. Crie uma nova tabela dinâmica para a planilha Contas colocando o campo Banco em Linhas e o campo Valor em Valores. 2. Na guia Analisar, grupo Filtrar clique em Inserir Linha do Tempo. 3. Selecione Data e clique em OK. Observe a linha do tempo que é colocada na planilha. 4. Podemos modificar o período para a visualização dos dados clicando na seta próxima ao nível de tempo escolhendo o mais adequado. A linha do tempo pode ser movida para qualquer localização na planilha. Também é possível modificar o seu estilo selecionando-a e, na guia Opções, em Estilos da Linha do Tempo, escolher o estilo desejado. Também podemos modificar o seu tamanho selecionando-a e arrastando suas alças. ATIVIDADE 6 A ferramenta Segmentação de Dados permite a aplicação de filtros rápidos aplicados nos filtros existentes, tornando mais fácil a leitura e a interpretação dos dados, sem que haja a necessidade de inserção de novos menus suspensos. 1. Selecione a tabela dinâmica criada com as datas, clique na ferramenta Inserir Segmentação de Dados do grupo Filtrar da guia Analisar. 2. Selecione o campo Banco e clique em OK. 3. O quadro mostrado permite filtrar os dados da tabela dinâmica pela escolha do banco. Clique no botão Bradesco e verifique que os dados mostrados referem-se a todosos movimentos do Bradesco. EXCEL PARA EXECUTIVOS 48 Podemos ter vários filtros criados pela Segmentação de Dados. Assim, se for necessário que os dados da tabela dinâmica, além de serem filtrados por banco, também sejam filtrados por Grupo, basta criarmos uma nova segmentação para o campo Grupo. 4. Crie uma segmentação de dados para o campo Grupo. GRÁFICOS DINÂMICOS Os gráficos dinâmicos permitem uma visualização rápida e eficiente dos dados de uma tabela dinâmica. Os recursos utilizados são os mesmos disponíveis nos gráficos normais do Excel. A única diferença é que o gráfico dinâmico possui caixas de combinação para aplicação de filtros diretamente sobre ele. ATIVIDADE 7 Para ter uma melhor visualização de gastos e proventos, vamos mostrar os dados da tabela dinâmica mensal por meio de um gráfico. 1. Clique em qualquer célula da tabela dinâmica criada na atividade anterior. 2. Selecione a ferramenta Gráfico Dinâmico do grupo Ferramentas da guia Analisar. 3. Escolha o tipo Coluna e clique em OK. 4. Observe que, a cada mudança na tabela dinâmica por meio da ação de qualquer filtro selecionado, os dados do gráfico também se modificam. O gráfico também apresenta opções para filtros (veja as caixas de combinação). O gráfico também pode ser criado por meio da tecla F11. Nesse caso será criado em uma nova planilha. Uma vez criado, o gráfico pode ser editado utilizando os mesmos recursos disponíveis em um gráfico qualquer do Excel. EXCEL PARA EXECUTIVOS 49 CAPÍTULO 7 Hipóteses EXCEL PARA EXECUTIVOS 50 O Excel fornece ferramentas que permitem a aplicação de testes de hipóteses para analisar resultados baseados em alterações nos valores das células da planilha. ATINGIR METAS O recurso Atingir Metas permite que, na hipótese de um possível resultado, sejam determinados valores de entrada que gerem o resultado esperado. ATIVIDADE 1 Suponha que você queira adquirir um automóvel financiando parte do seu valor. O banco apresentou uma proposta para financiamento de R$ 15.000,00, em 12 parcelas, a uma taxa anual de juros de 15%. Essa situação está mostrada na planilha a seguir. 1. Abra a pasta METAS.xlsx. O valor da parcela está calculado pela função PGTO, vista no Capítulo 2. 2. Você deseja, no entanto, diminuir o valor dessa parcela mensal para R$ 800,00. Para verificar qual o número de parcelas que devem ser pagas com essa condição, vamos utilizar a ferramenta Atingir Meta. Na guia Dados, grupo Ferramenta de Dados, clique em Teste de Hipóteses e, a seguir, Atingir meta... 3. Nessa caixa de diálogo insira as seguintes informações: Definir célula: D7 Para valor: -800,00 Alternando célula: C5 EXCEL PARA EXECUTIVOS 51 Clique em OK. Você terá o seguinte resultado: CENÁRIOS O gerenciador de cenários é uma ferramenta que permite, por meio de alterações nos valores das células, fazer uma projeção para um conjunto de diferentes resultados, comparando valores e verificando como essas alterações afetarão a planilha. ATIVIDADE 2 1. Abra a pasta CENÁRIOS.xlsx. 2. Essa pasta contém duas planilhas: Janeiro e Resumo. 3. Vamos, inicialmente, criar um cenário com os dados atuais da planilha Janeiro para preservá-los. Acesse a planilha Janeiro e, na guia Dados, grupo Ferramentas de Dados clique em Teste de Hipóteses, Gerenciador de Cenários... 4. Na janela apresentada clique no botão Adicionar. 5. Preencha o campo nome com Dados Originais Janeiro. 6. Vamos agora selecionar as células variáveis, ou seja, as células que terão seus valores alterados para análise dos resultados. No campo Células variáveis selecione os intervalos $C$3:$C$4;$C$6:$C$9. As células selecionadas não devem conter fórmulas, para que não sejam substituídas por valores quando da aplicação do cenário. 7. Digite o que achar necessário para o campo Comentário. EXCEL PARA EXECUTIVOS 52 8. Clique em OK. Serão mostradas as células variáveis para receber os novos valores. Como estamos criando um cenário para preservar os valores atuais, não vamos alterá-los. 9. Clique em OK e o cenário será criado. 10. Agora vamos criar um cenário para projetar um aumento de 20% nas entradas do mês de janeiro. Clique no botão Adicionar... 11. Como nome do cenário digite Aumento de 20% nas entradas de janeiro. Mantenha as mesmas células variáveis. Clique em OK. 12. Digite as fórmulas para calcular os valores das entradas com acréscimo de 20% ou digite os valores calculados. Clique em OK. EXCEL PARA EXECUTIVOS 53 13. O Excel avisa que as fórmulas foram convertidas em valores correspondentes ao cálculo efetuado. Clique em OK. Agora temos no Gerenciador de cenários os dois cenários criados. Para visualizar um cenário, selecione-o e clique em Mostrar. 14. Da mesma forma que fizemos anteriormente, crie mais dois cenários para a planilha Janeiro: um que tenha uma queda de 5% nas saídas e outro que tenha um aumento de 3% na entrada Vendas outros produtos e uma queda de 4% nas saídas Compras à vista e Despesas gerais. 15. Clique no botão Resumir. 16. Selecione Resumo do cenário e no campo Células de resultado indique as células C5, C10 e C11. Será criada uma planilha de nome Resumo do cenário que contém todas as simulações criadas. Essa planilha pode ser formatada da forma que você ache mais adequada. EXCEL PARA EXECUTIVOS 54 CAPÍTULO 8 Proteção EXCEL PARA EXECUTIVOS 55 PROTEGENDO CÉLULAS DA PLANILHA Podemos proteger elementos de uma planilha evitando que um usuário danifique seus dados, alterando ou mesmo excluindo informações e fórmulas que prejudiquem seu funcionamento. Por padrão, quando uma planilha é protegida, todas as suas células são bloqueadas. Dessa forma, para que uma área da planilha possa ser modificada, é necessário desbloquear suas células. ATIVIDADE 1 1. Abra a pasta LOJA.xlsx. 2. Selecione as colunas A, B e C. Essas colunas, que contêm as informações referentes ao nome do vendedor, departamento ao qual pertence e valor da venda, serão desbloqueadas para que possam sofrer modificações. As colunas restantes são compostas por fórmulas e serão protegidas. 3. Na guia Página Inicial, grupo Células, opção Formatar, selecione Bloquear Célula. Esse comando irá desbloquear as células selecionadas. 4. Na guia Revisão, grupo Alterações, clique em Proteger Planilha. 5. Existem várias opções para a proteção da estrutura da planilha. Digite uma senha e deixe selecionadas as duas primeiras opções. 6. Confirme a senha e clique em OK. IMPORTANTE - Na senha, o Excel diferencia letras maiúsculas de minúsculas. - Não se esqueça de sua senha. Sem ela, não será possível desproteger a pasta ou planilha e o Excel não consegue recuperá-la. 7. Tente alterar qualquer célula que esteja protegida. 8. Para desproteger os elementos da planilha, acesse a guia REVISÃO, grupo Alterações, opção Desproteger Planilha. Digite a senha e clique em OK. EXCEL PARA EXECUTIVOS 56 PROTEGENDO UMA PLANILHA É possível bloquear a estrutura de uma pasta de trabalho impedindo os usuários de excluir ou adicionar planilhas, bem como de acessar planilhas que estejam ocultas. ATIVIDADE 2 1. Clique com o botão direito do mouse sobre o nome da planilha METAS. 2. Selecione a opção Ocultar. 3. Na guia REVISÃO, grupo Alterações, selecione Proteger Pasta de Trabalho. 4. Digite uma senha e clique em OK. Observe que a opção Re-exibir não está mais disponível. 5. Para desproteger a planilha, selecione Proteger Pasta de Trabalho e digite a senha. PROTEGENDO UMA PASTA DE TRABALHO É possível bloquear o acesso ao arquivo por meio de uma senha. ATIVIDADE 3 1. Acesse a guia ARQUIVO, opção Informações e Proteger Pasta de Trabalho. 2. Selecione Criptografar com Senha e digite e confirme uma senha. 3. Feche a pasta, salvando as alterações,
Compartilhar