Apostila Excel 2010 Avançado by Materiali

ADMINISTRAÇÃO REGIONAL DO SENAC NO ESTADO DE SÃO PAULO Gerência de Desenvolvimento Claudio Luiz de Souza Silva Coordenação Técnica Richard Martelli Apoio Técnico Paulo Roberto Pereira Dias Revisão Técnica Núcleo Digital Prestação de Serviços em Informática Ltda. © Senac São Paulo 2011 1ª. edição Elaboração do material didático Maria Silvia Mendonça de Barros Edição e Produção Núcleo Digital Prestação de Serviços em Informática Ltda.

EXCEL 2010 AVANÇADO

2010

Excel 2010 – Avançado

Sumário Capítulo 1 – Funções matemáticas, estatísticas, lógicas e manipulação de datas ........................1 FUNÇÕES MATEMÁTICAS..................................................................................................... 3 Atividade 1 – Utilizando funções matemáticas ......................................................................... 3 Função SOMA...................................................................................................................... 3 Função PRODUTO ............................................................................................................. 4 Função SOMARPRODUTO ................................................................................................ 4 Função SOMASE.................................................................................................................. 5 Função SOMASES ................................................................................................................ 6 Atividade 2 – Utilizando Funções Estatísticas ........................................................................... 9 Função MÁXIMO .............................................................................................................. 10 Função MÍN ....................................................................................................................... 11 Função MÉDIA................................................................................................................... 11 Função ORDEM.EQ .......................................................................................................... 12 Função CONT.VALORES.................................................................................................. 12 Função CONTAR.VAZIO.................................................................................................. 13 Função CONT.NÚM......................................................................................................... 13 Função CONT.SE .............................................................................................................. 14 Função CONT.SES ............................................................................................................ 14 Função MÉDIASE............................................................................................................... 15 Função MÉDIASES ............................................................................................................. 15 Atividade 3 – Utilizando Funções Lógicas ............................................................................... 19 Função SE .......................................................................................................................... 19 Função E ............................................................................................................................ 20 Função OU ........................................................................................................................ 21 Função SEERRO ................................................................................................................ 23 Atividade 4 – Manipulando Datas e Horas.............................................................................. 25 Formatação de DATAS...................................................................................................... 25 FUNÇÕES DE DATA ....................................................................................................... 29 Função HOJE ................................................................................................................ 29 Função ANO ................................................................................................................ 29 Função MÊS .................................................................................................................. 29 Função DIA ................................................................................................................... 29 Função DIA.DA.SEMANA ............................................................................................ 29 Função DIATRABALHO.INTL .......................................................................................... 31 Função DIATRABALHOTOTAL.INTL .............................................................................. 33

Senac São Paulo

Excel 2010 – Avançado

Função HORA ................................................................................................................... 37 Função MINUTO .............................................................................................................. 37 Formatação CONDICIONAL ........................................................................................... 38 Exercício Proposto ................................................................................................................. 40

Capítulo 2 – Funções de Pesquisa e Auditoria de Fórmulas ..................................................... 45 Funções de PESQUISA ........................................................................................................... 47 Atividade 1 – Utilizando as funções PROCV e PROCH ......................................................... 47 Função PROCV ................................................................................................................. 47 Função PROCH................................................................................................................. 49 Atividade 2 – Utilizando as funções CORRESP e ÍNDICE ..................................................... 51 Função CORRESP ............................................................................................................. 51 Função ÍNDICE ................................................................................................................. 52 AUDITORIA DE FÓRMULAS ................................................................................................ 53 Atividade 3 – Rastreando os precedentes de uma célula ....................................................... 53 Atividade 4 – Rastreando os dependentes de uma célula ...................................................... 55 Atividade 5 – Rastreando erros em fórmulas ......................................................................... 57 Atividade 6 – Avaliando fórmulas ........................................................................................... 59 Atividade 7 – Mostrando fórmulas ......................................................................................... 61 Exercício Proposto ................................................................................................................. 63

Capítulo 3 – Funções de Texto ............................................................................................... 65 Funções de PESQUISA ........................................................................................................... 67 Atividade 1 – Utilizando a função ARRUMAR ........................................................................ 67 Função ARRUMAR ............................................................................................................ 67 Atividade 2 – Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA ......... 69 Função MAIÚSCULA ........................................................................................................ 69 Função MINÚSCULA ........................................................................................................ 69 Função PRI.MAIÚSCULA .................................................................................................. 69 Atividade 3 – Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO ........................ 71

Senac São Paulo

Excel 2010 – Avançado

Função ESQUERDA .......................................................................................................... 71 Função DIREITA ................................................................................................................ 71 Função EXT.TEXTO ......................................................................................................... 71 Exercício Proposto ................................................................................................................. 73

Capítulo 4 – Funções de Banco de Dados................................................................................ 75 Funções de BANCO DE DADOS .......................................................................................... 77 Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA .................................................. 77 Função BDSOMA .............................................................................................................. 77 Função BDMÉDIA ............................................................................................................. 79 Atividade 2 – Utilizando as funções BDMÁX e BDMÍN ......................................................... 81 Função BDMÁX................................................................................................................. 81 Função BDMÍN .................................................................................................................. 81 Atividade 3 – Utilizando a função BDEXTRAIR...................................................................... 83 Função BDEXTRAIR.......................................................................................................... 83 Atividade 4 – Utilizando a função BDCONTARA .................................................................. 87 Função BDCONTARA ...................................................................................................... 87 Exercício Proposto ................................................................................................................. 88

Capítulo 5 – Importação de Dados para o Excel ...................................................................... 89 Importação de Dados para o EXCEL ..................................................................................... 91 Atividade 1 – Importando arquivos texto para o Excel .......................................................... 91 Importando um arquivo texto .......................................................................................... 91 Atividade 2 – Importando arquivos de dados para o Excel .................................................... 97 IMPORTANDO UM ARQUIVO DE DADOS ................................................................... 97 Atividade 3 – Importando base de dados como consulta para o Excel .................................. 99 Importando uma Tabela como Consulta ........................................................................... 99 Atividade 4 – Vinculando dados do Excel no Access ............................................................ 103 Vinculando Dados do Excel no Access ............................................................................ 103 Exercício Proposto ............................................................................................................... 106

Senac São Paulo

III

Excel 2010 – Avançado

Capítulo 6 – Filtro, Classificação e Filtro Avançado................................................................ 107 Filtrando e classificando no Excel ......................................................................................... 109 Atividade 1 – Aplicar o AutoFiltro ........................................................................................ 109 Atividade 2 – Aplicar o filtro a mais de um campo ............................................................... 111 Atividade 3 – Aplicar filtro alternativo .................................................................................. 113 Atividade 4 – Classificar com o AutoFiltro ........................................................................... 115 Atividade 5 – Classificação Avançada ................................................................................... 117 Atividade 6 – Classificação por lista personalizada ............................................................... 119 Atividade 7 – Classificação por cores ................................................................................... 121 Atividade 8 – Filtro avançado ............................................................................................... 123 Atividade 9 – Filtro avançado com critérios múltiplos ......................................................... 127 Exercício Proposto ............................................................................................................... 128

Capítulo 7 – Visualização de Dados ....................................................................................... 129 Atividade 1 – Criação de Minigráficos .................................................................................. 131 Atividade 2 – Alteração e formatação de Minigráficos ......................................................... 133 Atividade 3 – Capturar telas ................................................................................................. 135 Exercício Proposto ............................................................................................................... 136

Capítulo 8 – Tabela Dinâmica ............................................................................................... 137 Atividade 1 – Criação da Tabela Dinâmica ........................................................................... 139 Atividade 2 – Atualização e Inclusão de Dados .................................................................... 141 Atividade 3 – Criação de outros níveis ................................................................................. 143 Atividade 4 – Formatar a Tabela Dinâmica .......................................................................... 145

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Agrupar Itens .................................................................................................. 147 Atividade 6 – Aplicar Filtro de Relatório............................................................................... 149 Atividade 7 – Agrupar campos por períodos ........................................................................ 151 Atividade 8 – Segmentação de Dados .................................................................................. 153 Atividade 9 – Gráfico Dinâmico ............................................................................................ 155 Atividade 10 – Mostrar Páginas de Relatório ........................................................................ 157 Exercício Proposto ............................................................................................................... 158

Capítulo 9 – Resumindo Dados ............................................................................................. 159 Atividade 1 – Consolidação de Dados .................................................................................. 161 Atividade 2 – Consolidação de Dados com Vínculo ............................................................. 165 Atividade 3 – Estrutura de Tópicos ...................................................................................... 167 Atividade 4 – Subtotais ......................................................................................................... 169 Atividade 5 – Cenários .......................................................................................................... 173 Exercício Proposto ............................................................................................................... 179

Capítulo 10 – Validação de Dados ......................................................................................... 181 Atividade 1 – Criar regras de validação ................................................................................ 183 Exercício Proposto ............................................................................................................... 188

Capítulo 11 – Utilizando pasta de trabalho em equipe ........................................................... 189 Trabalho em Equipe.............................................................................................................. 191 Atividade 1 – Proteção de planilha ....................................................................................... 191 Atividade 2 – Compartilhar a pasta de trabalho ................................................................... 197

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto ............................................................................................................... 200

Capítulo 12 – Simulações ...................................................................................................... 201 Atividade 1 – Atingir Meta .................................................................................................... 203 Atividade 2 – Solver ............................................................................................................. 205 Atividade 3 – Tabela de Dados ............................................................................................ 209 Atividade 4 – Inserir Tabela ................................................................................................. 213 Exercício Proposto ............................................................................................................... 216

Capítulo 13 – Macros Intrativas ............................................................................................. 219 Trabalhando com Macros..................................................................................................... 221 Atividade 1 – Criar macro .................................................................................................... 221 Atividade 2 – Criando macro com deslocamentos relativos ................................................ 225 Atividade 3 – Execução e exclusão de macros ..................................................................... 229 Exercício Proposto ............................................................................................................... 235

Capítulo 14 – Automação de Planilhas ................................................................................... 237 Automação de Planilhas ........................................................................................................ 239 Atividade 1 – Criar controles para escolha de dados ........................................................... 239 Atividade 2 – Criação da Macro Modelos ............................................................................ 243 Atividade 3 – Criar controles complementares à pesquisa .................................................. 245 Atividade 4 – Criar Critérios para a pesquisa ....................................................................... 249 Atividade 5 – Criar macro Veículos ...................................................................................... 251 Atividade 6 – Controles para cálculos .................................................................................. 253 Exercício Proposto ............................................................................................................... 256

Senac São Paulo

Excel 2010 – Avançado

A estrutura da apostila Esta apostila está dividida em capítulos. Cada capítulo contém atividades práticas e informações teóricas sobre o software. Para obter o melhor rendimento possível em seu estudo, evitando dúvidas ou erros, é importante que você: •

leia com atenção todos os itens da apostila, pois sempre encontrará informações úteis para a execução das atividades;

•

conheça e respeite o significado dos símbolos colocados na margem esquerda de determinados parágrafos do texto, pois eles servem para orientar seu trabalho;

•

faça apenas o que estiver indicado no item e só execute uma seqüência após ter lido a instrução do respectivo item.

Significado dos símbolos Início Este símbolo sinaliza o início de uma atividade. Apesar de poder interromper seu estudo em qualquer etapa, é recomendável que você inicie e finalize uma atividade sem interrupção. Dica Quando este símbolo aparecer, você terá informações adicionais sobre o assunto, como dicas, atalhos e sugestões para facilitar o trabalho com o software. Sua leitura não é obrigatória para o desenvolvimento da atividade; mas lembre-se: quanto mais informações você tiver, melhor será o seu aproveitamento. Observação Este símbolo marca um texto com observações sobre o assunto tratado. Embora importante, sua leitura não é obrigatória, nem interfere diretamente na atividade em execução. Atenção Toda vez que se deparar com este símbolo, leia com muita atenção o texto que o acompanha, pois as informações que ele contém afetarão os passos seguintes da atividade. Isso evitará dúvidas posteriores.

Utilizando o CD Para a execução das atividades dos capítulos, disponibilizamos os arquivos num CD que acompanha a apostila. Para facilitar seu trabalho, faça uma cópia da pasta “PLANILHAS - ATIVIDADES” para sua Área de Trabalho. Esta pasta contém todos os arquivos necessários para a execução de suas atividades e dos exercícios propostos. Além dela, há uma pasta chamada “PLANILHAS RESOLVIDAS”, que contém os arquivos finais das atividades e exercícios propostos para você possa comparar com as atividades executadas por você.

Senac São Paulo

VII

Excel 2010 – Avançado

VIII

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Utilizar funções matemáticas do Excel.  Utilizar funções estatísticas do Excel.  Utilizar funções lógicas do Excel.  Utilizar funções de data e hora do Excel.  Formatar datas.  Efetuar cálculos com datas e horas.

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

FUNÇÕES MATEMÁTICAS

O Excel 2010 oferece ferramentas avançadas que possibilitam a análise, o gerenciamento e o compartilhamento de informações de tal forma que a tomada de decisões para uma situação específica se torne mais eficiente. É nesse contexto que neste curso você aprenderá algumas dessas funções e ferramentas, para que possa utilizar em suas planilhas aprimorando sua capacidade de analisar conjuntos de dados.

Atividade 1 – Utilizando funções matemáticas Objetivos :

• Utilizar as funções matemáticas do Excel 2010.

Tarefas

• Abrir a pasta Vendas trimestre 1.xlsx.

• Somar o número de itens vendidos. • Calcular o preço total de cada produto. • Somar o valor total de vendas. • Somar o valor vendido por filial. • Somar o valor vendido por filial e por mês.

Função SOMA A função SOMA tem como 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 se deseja somar. 1. Abra a pasta “Vendas trimestre 1.xlsx”.

Senac São Paulo

Excel 2010 – Avançado

2. Você vai calcular o número de itens vendidos pelas filiais no 1º trimestre de 2010. Clique na célula D19 e digite: = SOMA( 3. Com o mouse clicado selecione a região D4:D17 . 4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: = SOMA(D4:D17)

Uma outra forma de selecionar a função SOMA é através da ferramenta Ʃ que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função PRODUTO A função PRODUTO tem como objetivo efetuar o produto entre os valores numéricos contidos em uma faixa de células. •

Sintaxe: PRODUTO(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 multiplicar.

1. Você vai calcular o valor total de cada item vendido pelas filiais no 1º trimestre de 2010. Clique na célula F4 e digite: = PRODUTO( 2. Clique na célula D4, digite ; (ponto e vírgula) e clique na célula E4. 3. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: = PRODUTO(D4:E4) No caso da atividade, o argumento D4:E4 refere-se ao intervalo de células D4 até E4, ou seja, serão considerados todos os valores entre essas duas células. Você pode, no entanto, substituir o argumento por D4;E4, uma vez que não existe nenhuma célula entre D4 e E4. 4. Copie a fórmula para todos os itens da planilha.

Função SOMARPRODUTO A função SOMARPRODUTO tem como objetivo multiplicar os componentes das faixas de dados fornecidas e retornar a soma destes produtos. •

Sintaxe: SOMAPRODUTO(faixa1;faixa2;.....)

•

Argumentos: os argumentos faixa1, faixa2,..... correspondem a faixa de células que desejamos multiplicar e depois somar.

Senac São Paulo

Excel 2010 – Avançado

1. Você vai calcular o valor total das vendas efetuadas pelas filiais no 1º trimestre de 2010. Na verdade terá de somar os resultados da multiplicação de quantidade por preço unitário de cada produto. Clique na célula D20 e digite: = SOMARPRODUTO( 2. Com o mouse selecione a faixa D4:D17 3. Digite ; (ponto e vírgula). 4. Com o mouse selecione a faixa E4:E17. Você poderia indicar a fórmula selecionando a faixa D4:D17 e, pressionando a tecla CTRL, selecionar a faixa E4:E17. 5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: =SOMARPRODUTO(D4:D17;E4:E17) A operação realizada corresponde a somar a coluna Valor Total.

Função SOMASE A função SOMASE tem como objetivo efetuar a soma dos valores indicados de acordo com um determinado critério ou condição. •

Sintaxe: SOMASE(intervalo; critérios; intervalo_soma)

•

Argumentos: o Intervalo: intervalo de células onde o critério será procurado; o Critério: condição para definir quais valores serão somados. Esses critérios podem ser

número, expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);

o Intervalo_soma: intervalo de valores que serão somados. Se este 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). 6. Você vai calcular o valor vendido pela filial de Vila Mariana. Clique na célula D21 e digite: = SOMASE( 7. Com o mouse selecione a faixa A4:A17 8. Digite ; (ponto e vírgula). 9. Digite “Vila Mariana”. 10. Digite ; (ponto e vírgula). 11. Com o mouse selecione a faixa F4:F17. 12. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: =SOMASE(A4:A17;"Vila Mariana";F4:F17)

Senac São Paulo

Excel 2010 – Avançado

13. Na célula D22 calcule o valor vendido pela filial do Brooklin. Digite a fórmula: =SOMASE(A4:A17;"Brooklin";F4:F17) 14. Na célula D23 calcule o valor das vendas no mês de Janeiro. Observe que o critério “Janeiro” deve ser procurado na faixa B4:B17. =SOMASE(B4:B17;"Janeiro";F4:F17) 15. Nas células D24 e D25 calcule os valores vendidos para os meses de Fevereiro e Março.

Função SOMASES A função SOMASES tem como objetivo efetuar a soma em um intervalo atendendo a vários critérios, que serão combinados. •

Sintaxe: o SOMASES(intervalo_soma; intervalo_critério1; critério1; intervalo_critério2; critério2;......)

•

Argumentos: o Intervalo_soma: intervalo de valores que serão somados. o Intervalo_critério1: intervalo de células onde o critério1 será procurado; o Critério1: condição para definir quais valores serão somados. Esses critérios podem ser

o Intervalo_critério2: intervalo de células onde o critério2 será procurado; o Critério2: condição para definir quais valores serão somados. Esses critérios podem ser número, expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>). Você pode ter até 127 pares de intervalos/critérios. 1. Você vai calcular o valor vendido pela filial de Vila Mariana no mês de Fevereiro. Clique na célula D26 e digite: = SOMASES( 2. Com o mouse selecione a faixa F4:F17 3. Digite ; (ponto e vírgula). 4. Com o mouse selecione a faixa A4:A17. 5. Digite ; (ponto e vírgula). 6. Digite “Vila Mariana”. 7. Digite ; (ponto e vírgula). 8. Com o mouse selecione a faixa B4:B17. 9. Digite ; (ponto e vírgula). 10. Digite “Fevereiro”.

Senac São Paulo

Excel 2010 – Avançado

11. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: =SOMASES(F4:F17;A4:A17;"Vila Mariana";B4:B17;"Fevereiro") 12. Na célula D27 calcule o valor vendido pela filial do Brooklin no mês de Janeiro.

13. Feche a pasta salvando as modificações.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Utilizando Funções Estatísticas Objetivos :

• Utilizar as funções estatísticas do Excel 2010.

Tarefas

• Abrir a pasta Prêmio.xlsx.

• Calcular a maior e a menor venda entre os vendedores. • Calcular a média de vendas dos vendedores. • Calcular o total de vendedores. • Calcular o número de vendedores que não efetuaram vendas. • Calcular o número de vendedores que efetuaram vendas. • Calcular o número de vendedores premiados com o primeiro lugar em vendas. • Calcular o total de vendedores por departamento. • Calcular o número de vendedores premiados com o primeiro lugar em vendas por departamento. • Calcular a média de vendas por departamento. • Calcular a média de vendas pelos vendedores não premiados com o primeiro lugar por departamento. Uma loja possui três departamentos de vendas: Informática, Eletrodomésticos e Livraria. Cada departamento tem a sua equipe de vendas. Com a finalidade de motivar os vendedores, a loja resolveu distribuir um prêmio no valor de R$ 450.000,00 para o(s) vendedor(es) que ficou(aram) em primeiro lugar no volume de vendas. O objetivo é efetuar os seguintes controles: •

Mostrar o ranking de vendas, ou seja, calcular a colocação de cada um levando em consideração a venda individual em relação ao conjunto de vendas dos vendedores.

•

Mostrar a maior e a menor venda efetuada considerando todos os departamentos;

•

Calcular a média de vendas considerando todos os departamentos;

•

Mostrar o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o número de vendedores que efetuaram vendas e quantos ficaram em primeiro lugar em vendas;

•

Por departamento, calcular o número de vendedores, quantos ficaram em primeiro lugar em vendas, calcular a média de vendas e a média de vendas dos vendedores não ganhadores do prêmio.

Para resolver essa situação você vai trabalhar com a pasta Prêmio.xlsx. Nessa pasta você encontrará duas planilhas: VENDAS e RELATÓRIO.

Senac São Paulo

Excel 2010 – Avançado

Função MÁXIMO Essa função retorna 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. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Abra a pasta Prêmio.xlsx. 2. Selecione a planilha RELATÓRIO e clique na célula B2. Nessa célula você deve calcular o valor máximo das vendas que estão na faixa C3:C22 da planilha VENDAS. 3. Digite: = MÁXIMO( 4. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÁXIMO(VENDAS!C3:C22)

Senac São Paulo

Excel 2010 – Avançado

Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a fórmula está sendo colocada na planilha RELATÓRIO, é colocado o nome da planilha seguido pelo ponto de exclamação na identificação da região informada.

Outra forma de selecionar a função MÁXIMO é através da ferramenta Ʃ que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função MÍN Essa função retorna o valor mínimo entre um conjunto de valores numéricos informado. •

Sintaxe: MÍN(núm1;núm2;.....)

•

Argumentos: os argumentos núm1, núm2,..... correspondem aos valores numéricos cujo valor

mínimo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Selecione a planilha RELATÓRIO e clique na célula B3. Nessa célula você vai calcular o valor mínimo das vendas que estão na faixa C3:C22 da planilha VENDAS. 2. Digite: = MÍN( 3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÍN(VENDAS!C3:C22)

Outra forma de selecionar a função MÍN é através da ferramenta Ʃ que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função MÉDIA Essa função retorna 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. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Selecione a planilha RELATÓRIO e clique na célula B4. Nessa célula você vai calcular a média aritmética das vendas que estão na faixa C3:C22 da planilha VENDAS. 2. Digite: = MÉDIA( 3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. Senac São Paulo

Excel 2010 – Avançado

4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÉDIA(VENDAS!C3:C22)

Outra forma de selecionar a função MÉDIA é através da ferramenta Ʃ que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função ORDEM.EQ Essa função retorna a posição de um número em uma lista de números. •

Sintaxe: ORDEM.EQ(número; referência; [ordem])

•

Argumentos: o Número: número cuja posição se quer encontrar em uma lista de números; o Referência: lista de números onde o número informado será posicionado; o Ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionará o número

considerando a lista de números ordenada de forma descendente; se for qualquer valor diferente de zero, o Excel posicionará o número considerando a lista de números ordenada de forma ascendente.

A presença de números com a mesma posição irá interferir na ordem dos números subsequentes Por exemplo, em uma lista de números inteiros classificados em ordem crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3. 5. Selecione a planilha VENDAS e clique na célula D3. 6. Você vai utilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3 dentro da faixa de vendas C3:C22. Digite a fórmula: = ORDEM.EQ(C3;$C$3:$C$22) Observe que a faixa de células C3:C22 foi fixada na fórmula, pois na cópia para as células de baixo essa faixa deve continuar sendo a mesma. 7. Pressione a tecla ENTER e copie a fórmula até a célula D22.

Não se preocupe com as informações de erro que aparecem nas células D8 e D17. Esse erro é devido a não existir um valor de venda nas células C8 e C17. Mais adiante, nesse mesmo capítulo, esse erro será tratado de forma que não apareça a mensagem.

Função CONT.VALORES Essa função conta 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: o Intervalo1;[intervalo2];....: faixas de células que devem ser contadas.

Senac São Paulo

Excel 2010 – Avançado

1. Você vai contar o número total de vendedores da loja. Para isso conte quantas células contém os nomes dos vendedores. Selecione a planilha RELATÓRIO e clique na célula B7. 2. Digite: =CONT.VALORES( 3. Como os nomes dos vendedores estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células A3:A22. 4. Pressione a tecla ENTER e você terá a fórmula: =CONT.VALORES(VENDAS!A3:A22)

Função CONTAR.VAZIO Essa função conta o número de células vazias em um intervalo informado. Células que contenham valor nulo não serão contadas. •

Sintaxe: CONTAR.VAZIO(intervalo)

•

Argumento: o argumento intervalo representa a faixa de células que devem ser contadas.

1. Agora você vai contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da coluna VALOR DA VENDA que estão vazias. Selecione a planilha RELATÓRIO e clique na célula B8. 2. Digite: =CONTAR.VAZIO( 3. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22. 4. Pressione a tecla ENTER e você terá a fórmula: =CONTAR.VAZIO(VENDAS!C3:C22)

Função CONT.NÚM Essa função conta o número de células que contêm números em um intervalo informado. Serão contadas as células que contenham números, datas ou números escritos entre aspas. •

Sintaxe: CONT.NÚM(intervalo1; [intervalo2]; ....)

•

Argumentos: o Intervalo1;[intervalo2];....: faixas de células que devem ser contadas.

1. Agora você vai contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna VALOR DA VENDA que estão preenchidas com números. Selecione a planilha RELATÓRIO e clique na célula B9. 2. Digite: =CONT.NÚM(

Senac São Paulo

Excel 2010 – Avançado

3. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22. 4. Pressione a tecla ENTER e você terá a fórmula: =CONT.NÚM(VENDAS!C3:C22)

Função CONT.SE Essa função conta 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: o Intervalo: intervalo de células que será considerado; o Critério: condição que será procurada no intervalo de células;

1. Você vai contar quantos são os vendedores ganhadores do prêmio, ou seja, aqueles que se encontram em primeiro lugar na colocação de vendas. Selecione a planilha RELATÓRIO e clique na célula B10. 2. Digite: =CONT.SE( 3. Como você quer verificar se o vendedor está na classificação 1, o intervalo de células considerado deve ser o da coluna COLOCAÇÃO da planilha VENDAS. Selecione a planilha VENDAS e, com o mouse clicado, selecione o intervalo D3:D22. 4. Digite ; (ponto e vírgula). 5. Digite 1 6. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: = CONT.SE(VENDAS!D3:D22;1) 7. Agora você vai contar quantos vendedores são do departamento de Informática. Para isso você deve contar o número de ocorrências da palavra Informática no intervalo de células B3:B22 da planilha VENDAS. Clique na célula E2 da planilha RELATÓRIO. 8. Digite a fórmula: = CONT.SE(VENDAS!B3:B22;"Informática") Como a palavra Informática, que é o critério para a função, está na célula D1 da planilha RELATÓRIO, a fórmula também poderia ser escrita da seguinte forma: = CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1)

Função CONT.SES Essa função aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são verdadeiros.

Senac São Paulo

Excel 2010 – Avançado

•

Sintaxe: CONT.SES(intervalo1; critério1;intervalo2;critério2;....)

•

Argumentos: o Intervalo1: intervalo de células onde será procurado o critério1; o Critério1: condição que será procurada no intervalo de células intervalo1; o Intervalo2: intervalo de células onde será procurado o critério2; o Critério2: condição que será procurada no intervalo de células intervalo2; Pode-se ter até 127 pares de intervalos/critérios.

1. Você vai calcular quantos são os vendedores ganhadores do prêmio do departamento de Informática. Veja que agora você precisa considerar duas condições: o vendedor está classificado como primeiro e trabalha no departamento de Informática. Selecione a planilha RELATÓRIO e clique na célula E3. 2. Digite: =CONT.SES( 3. Você deve procurar pela palavra “Informática”. Selecione a planilha VENDAS e selecione o intervalo B3:B22. Digite ; (ponto e vírgula) e, na planilha RELATÓRIO, clique na célula D1. Digite ; (ponto e vírgula). 4. Agora você vai procurar pelo número 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite ; (ponto e vírgula) e digite 1. Pressione a tecla ENTER e teremos a seguinte fórmula: = CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1)

Função MÉDIASE A função MÉDIASE tem como objetivo calcular a média aritmética dos valores indicados de acordo com um determinado critério ou condição. •

Sintaxe: MÉDIASE(intervalo; critérios; intervalo_média)

•

Argumentos: o Intervalo: intervalo de células onde o critério será procurado; o Critério: condição para definir quais valores serão considerados para o cálculo da média; o Intervalo_média: intervalo de células que será considerado para calcular a média.

1. Calcule agora a média de vendas para o departamento de Informática. Selecione a célula E4 da planilha RELATÓRIO e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito para as fórmulas anteriores, para construir a expressão. = MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22)

Função MÉDIASES A função MÉDIASES tem como objetivo calcular a média aritmética em um intervalo atendendo a vários critérios.

Senac São Paulo

Excel 2010 – Avançado

•

Sintaxe:

•

MÉDIASES(intervalo_média; intervalo_critério1; critério1; intervalo_critério2; critério2;......)

•

Argumentos: o Intervalo_média: intervalo de valores que serão considerados para o cálculo da média. o Intervalo_critério1: intervalo de células onde o critério1 será procurado; o Critério1: condição para definir quais valores serão considerados para o cálculo da média; o Intervalo_critério2: intervalo de células onde o critério2 será procurado; o Critério2: condição para definir quais valores serão considerados para o cálculo da média.

1. Calcular a média aritmética das vendas dos vendedores do departamento de Informática e que não são ganhadores do prêmio. Na célula E5 da planilha RELATÓRIO digite a fórmula: = MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;"<>1") Os critérios informados nessa fórmula são os seguintes: •

VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da média;

•

VENDAS!B3:B22 – intervalo onde será procurado o primeiro critério (Departamento);

•

RELATÓRIO!D1 – endereço da célula que contém o primeiro critério (Informática);

•

VENDAS!D3:D22 – intervalo onde será procurado o segundo critério (Colocação);

•

“<>1” – segundo critério, vendedores que não estão na posição 1.

2. Preencha, utilizando o mesmo raciocínio utilizado para as informações do departamento de Informática, os quadros relativos aos departamentos de Eletrodomésticos e Livraria da planilha RELATÓRIOS. 3. No final você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO:

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Utilizando Funções Lógicas Objetivos :

• Utilizar as funções lógicas do Excel 2010.

Tarefas

• Calcular o valor do prêmio para cada um dos vendedores ganhadores.

• Atribuir um conceito de acordo com o valor das vendas de cada vendedor. • Distribuir um prêmio extra entre os vendedores que não alcançaram o primeiro lugar de acordo com o valor de suas vendas. • Tratar adequadamente os erros que possam ocorrer em planilhas. Para completar o preenchimento da planilha VENDAS da pasta Prêmio.xlsx, você ainda tem algumas tarefas a realizar: •

Distribuir o prêmio que a empresa oferece aos vendedores colocados em primeiro lugar;

•

Atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-o com a meta a ser atingida individualmente;

•

Distribuir um prêmio extra aos vendedores que não se classificaram em primeiro lugar, mas que também se destacaram de acordo com alguns critérios estabelecidos.

Função SE Essa função tem como 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: o SE(teste_lógico; valor_se_verdadeiro; valor_se_falso)

•

Argumentos: o Teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro; o Valor_se_falso: o que fazer se o resultado do teste_lógico for Falso.

1. Selecione a planilha VENDAS da pasta Prêmios.xlsx. Você deve verificar na célula E3 se o vendedor está em primeiro lugar na classificação e, se estiver, calcular e mostrar o seu prêmio. Se o vendedor não estiver em primeiro lugar, o valor que deve ser colocado na célula é zero. Lembre-se que o prêmio total deve ser dividido igualmente entre os vendedores ganhadores. O raciocínio que vamos seguir é o seguinte: a) SE classificação = 1 b) ENTÃO dividir o valor do prêmio pelo número de ganhadores e mostrar na célula c) SENÃO mostrar o valor 0 na célula 2. Clique na célula E3 e digite o seguinte: = SE(D3=1;$B$1/RELATÓRIO!$B$10;0)

Senac São Paulo

Excel 2010 – Avançado

Observe que na célula D3 está a classificação do vendedor, na célula B1 está valor do prêmio total e na célula B10 da planilha RELATÓRIO está o número de ganhadores. 3. Copie a fórmula para todos os vendedores. Não se preocupe com os erros apontados nas células E8 e E17. Mais adiante você vai modificar as fórmulas para tratar adequadamente esses erros. 4. Agora você vai colocar uma mensagem para cada vendedor de acordo com os seguintes critérios: CONCEITO

CONDIÇÃO

EXCELENTE

Colocação do vendedor igual a 1

ÓTIMO

Valor das vendas do vendedor maior do que a meta

BOM

Valor das vendas do vendedor igual à meta

PODE MELHORAR

Nenhuma das condições anteriores

Observe que existem quatro condições a serem verificadas. Isso não será possível através de uma única função SE. Nesse caso, você terá que aninhar funções SE para resolver o problema. Até 64 funções SE podem ser aninhadas no Excel 2010. O raciocínio que você deve seguir é o seguinte:

SE colocação do vendedor = 1 ENTÃO atribuir conceito “EXCELENTE” SENÃO SE valor das vendas maior do que a meta ENTÃO atribuir conceito “ÓTIMO” SENÃO SE valor das vendas igual à meta ENTÃO atribuir conceito “BOM” SENÃO atribuir conceito “PODE MELHORAR” 5.

Clique na célula F3 e digite a seguinte fórmula: = SE(D3=1;"EXCELENTE";SE(C3>$D$1;"ÓTIMO";SE(C3=$D$1;"BOM";"PODE MELHORAR")))

6. Copie a fórmula para todos os vendedores. 7. Feche a pasta Premio.xlsx.

Função E Essa função retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros. •

Sintaxe: o E(lógico1; lógico2;...)

•

Argumentos: o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado.

Senac São Paulo

Excel 2010 – Avançado

Em uma função E você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas para avaliar.

Função OU Essa função retorna o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro. •

Sintaxe: o OU(lógico1; lógico2;...)

•

Argumentos: o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado.

Em uma função OU você pode colocar até 255 argumentos, ou seja, pode-se 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. 1. Abra a pasta Estado civil.xlsx. Você vai verificar a diferença dos resultados entre as funções E e OU.

2. Clique na célula D3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira e sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser mostrado o valor FALSO. 3. Digite a fórmula: = E(B3="Solteiro(a)";C3>=22) 4. Copie a fórmula para todas as pessoas. 5. Clique na célula E3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira ou se sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser mostrado o valor FALSO.

Senac São Paulo

Excel 2010 – Avançado

6. Digite a fórmula: = OU(B3="Solteiro(a)";C3>=22) 7. Copie a fórmula para todas as pessoas.

Note que no caso da função E, o resultado só é verdadeiro se as duas condições forem verdadeiras. No caso da função OU, o resultado é verdadeiro se pelo menos uma das condições for verdadeira, e somente será falso se as duas condições forem falsas. 8. Feche a pasta Estado civil.xlsx e abra a pasta Prêmio.xlsx. 9. A empresa resolveu premiar também os funcionários que obtiveram conceito ÓTIMO com uma viagem para Natal (RN) e os funcionários que venderam menos ou o valor da meta, mas que venderam mais do que R$ 300.000,00, com um jantar. Considere o seguinte raciocínio:

SE conceito = ÓTIMO ENTÃO prêmio extra = Viagem para Natal SENÃO SE vendas <= meta E vendas > 300000 ENTÃO prêmio extra = Jantar 10. Clique na célula G3 e digite o seguinte: = SE(F3="ÓTIMO";"Viagem para Natal";SE(E(C3<=$D$1;C3>300000);"Jantar";"")) Observe que se nenhuma das condições for verdadeira, o valor “” será colocado na célula, ou seja, a célula ficará vazia para evitar que seja mostrada a mensagem FALSO. 11. Copie a fórmula para todos os vendedores.

Senac São Paulo

Excel 2010 – Avançado

Função SEERRO Essa função tem como objetivo retornar um valor especificado se uma fórmula gerar um erro. Se não existir erro, a função retorna o resultado da fórmula. •

Sintaxe: o SEERRO(fórmula; valor_se_erro)

•

Argumentos: o Fórmula: fórmula que se deseja verificar quanto a erros; o Lógico2;...: 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!. 12. As fórmulas da coluna COLOCAÇÃO estão gerando erros nos casos em que a célula correspondente a venda está vazia. Como é um fato que pode acontecer, é aconselhável não mostrar a mensagem de erro gerada pelo Excel, mas sim armazenar na célula um valor mais adequado. Nesse caso, se existir o erro na célula, você deve deixá-la vazia, ou seja, armazenar “”. Clique na célula D3 e modifique a fórmula para: =SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"") Observe que todos os erros da planilha foram solucionados. É necessário saber qual valor deve-se colocar na célula nos casos de erro para não ocasionar novos erros.

13. Feche a pasta Prêmio.xlsx.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Manipulando Datas e Horas Objetivos :

• Formatar datas. • Utilizar funções de datas e horas. • Efetuar cálculos com datas e horas.

Tarefas

• Formatar uma data para que seja mostrado o dia da semana. • Formatar uma data para que seja mostrado o mês. • Formatar uma data para que seja mostrado o dia, mês e ano. • Utilizar as funções de data para mostrar a data de hoje, o ano, dia e mês de uma data, o número do dia da semana. • Projetar dias úteis a partir de uma data. • Calcular o número de dias úteis entre duas datas. • Projetar dias corridos a partir de uma data. • Calcular o número de dias corridos entre duas datas. • Calcular o número de horas e minutos entre dois horários. • Aplicar formatação condicional nas planilhas.

Em muitas planilhas é necessário o trabalho com datas e horas, efetuando cálculos e projeções. O Excel 2010 oferece várias funções e opções de formatação que podem ser aplicadas para as mais variadas necessidades.

Formatação de DATAS 1. Abra a pasta “Datas Formato.xlsx “.

Senac São Paulo

Excel 2010 – Avançado

2. Copie o conteúdo da coluna A para as demais colunas. 3. Na coluna B você vai mostrar o número serial que representa a data. Esse número é o número de dias transcorridos entre a data 01/01/1900 e a data considerada. Selecione o intervalo B2:B32. 4. Na guia Página Inicial, grupo Número, clique na seta do comando Formato de número.

5. Selecione Geral. 6. Na coluna C você vai mostrar o nome do dia da semana da data armazenada. Selecione o intervalo

C2:C32.

7. Clique na seta do grupo Número da guia Página Inicial.

8. Na guia Número, selecione a categoria Personalizado. 9. Apague o que está no campo Tipo e digite dddd.

Senac São Paulo

Excel 2010 – Avançado

O que é informado nesse campo determina o formato do dado selecionado. Para o caso de datas, considerando o dia da data, temos os seguintes formatos personalizados: •

dias que são formados por um algarismo são exibidos com um algarismo.

•

dias que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.

•

DDD

exibe o nome do dia de forma abreviada, através das três primeiras letras.

•

DDDD

exibe o nome do dia por extenso.

10. Clique em OK. 11. Agora você vai exibir na coluna D o nome do mês por extenso. Selecione o intervalo D2:D32. 12. Clique na seta do grupo Número da guia Página Inicial. 13. Na guia Número, selecione a categoria Personalizado. 14. Apague o que está no campo Tipo e digite mmmm. Para o caso de datas, considerando o mês da data, temos os seguintes formatos personalizados: •

meses que são formados por um algarismo são exibidos com um algarismo.

•

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, através das três primeiras letras.

•

MMMM

exibe o nome do mês por extenso.

Senac São Paulo

Excel 2010 – Avançado

15. Clique em OK. Agora você vai mostrar a data por extenso. Por exemplo: para a data 01/01/2010, deverá ser mostrado sexta-feira, 01 de janeiro de 2010. 16. 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

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 e a representação aaa formata a data para que apareçam os quatro dígitos do ano. 17. Clique em OK.

Senac São Paulo

Excel 2010 – Avançado

Você deve ter em mente a diferença entre conteúdo e formato. Conteúdo é o dado armazenado na célula, e formato é a forma como ele está sendo mostrado. Quando você efetuar cálculos com células formatadas, é o conteúdo que será trabalhado, e não o formato.

FUNÇÕES DE DATA Função HOJE A função HOJE retorna a data de hoje formatada como data. •

Sintaxe: HOJE()

Essa função não possui argumentos. No entanto deve-se digitar os parênteses. Função ANO Essa função retorna o ano da data informada. O ano é retornado como um inteiro no intervalo de 1900 a 9999. •

Sintaxe: ANO(data_informada)

• Argumentos: data_informada: data que se deseja retornar o ano. Função MÊS Essa função retorna o número do mês da data informada. O mês é retornado como um inteiro no intervalo de 1 a 12. •

Sintaxe: MÊS(data_informada)

•

Argumentos: data_informada: data que se deseja retornar o mês.

Função DIA Essa função retorna o dia da data informada. O dia é retornado como um inteiro no intervalo de 1 a 31. •

Sintaxe: DIA(data_informada)

•

Argumentos: data_informada: data que se deseja retornar o dia.

Função DIA.DA.SEMANA Essa função retorna o número do dia da semana da data informada. O dia é retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. •

Sintaxe: o DIA.DA.SEMANA(data_informada;tipo)

•

Argumentos: o data_informada: data que se deseja retornar o número do dia da semana. o tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores:

Senac São Paulo

Excel 2010 – Avançado

TIPO

VALOR RETORNADO

1 ou omitido

Números 1 (domingo) a 7 (sábado)

Números 1 (segunda-feira) a 7 (domingo)

Números 0 (segunda-feira) a 6 (domingo)

Números 1 (segunda-feira) a 7 (domingo)

Números 1 (terça-feira) a 7 (segunda-feira)

Números 1 (quarta-feira) a 7 (terça-feira)

Números 1 (quinta-feira) a 7 (quarta-feira)

Números 1 (sexta-feira) a 7 (quinta-feira)

Números 1 (sábado) a 7 (sexta-feira)

Números 1 (domingo) a 7 (sábado)

1. Abra a pasta “Datas Funções.xlsx”.

2. Clique na célula B1. 3. Coloque nessa célula a função para mostrar a data de hoje. Digite: = HOJE() 4. Clique na célula B2. 5. Coloque nessa célula a função para mostrar o dia da data de hoje. Digite: = DIA(B1) 6. Clique na célula B3. 7. Coloque nessa célula a função para mostrar o número do mês da data de hoje. Digite: = MÊS(B1) 8. Clique na célula B4.

Senac São Paulo

Excel 2010 – Avançado

9. Coloque nessa célula a função para mostrar o ano da data de hoje. Digite: = ANO(B1) 10. Clique na célula B5. 11. Coloque nessa célula a função para mostrar o número do dia da semana da data de hoje, considerando 1 como domingo e 7 como sábado. Digite: = DIA.DA.SEMANA(B1)

Função DIATRABALHO.INTL Essa função retorna os dias úteis a projetar a partir de uma data informada, considerando como é calculado o fim de semana e os feriados que possam ocorrer no período. •

Sintaxe: o DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados)

•

Argumentos: o data_inicial: data a partir da qual se deseja projetar dias úteis. o 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 zero gera uma data inicial. o fim_de_semana: argumento opcional. Indica que dias da semana são considerados como pertencentes ao final de semana. Pode ter os seguintes valores:

Senac São Paulo

FIM_DE_SEMANA

DIAS DE FIM DE SEMANA

1 ou omitido

Sábado, Domingo

Domingo, Segunda

Segunda-feira, Terça-feira

Terça-feira, Quarta-feira

Quarta-feira, Quinta-feira

Quinta-feira, Sexta-feira

Sexta-feira, Sábado

Apenas Domingo

Apenas Segunda-feira

Apenas Terça-feira

Apenas Quarta-feira

Apenas Quinta-feira

Excel 2010 – Avançado

•

Apenas Sexta-feira

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.

1. Abra a pasta Caixa.xlsx. 2. Nela você encontra duas planilhas: Lançamentos e Feriados. Na planilha Lançamentos você fará a projeção dos dias úteis para o mês de Abril/2010.

3. Clique na célula B3 e digite a data 01/04/2010. 4. Formate a célula B3 para que apareça somente o nome do mês. 5. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março. Essa data é o dia anterior da data 01/04/2010 que está armazenada na célula B3, ou seja, é o conteúdo da célula B3 – 1. Clique na célula A6 e digite: = DIATRABALHO.INTL(B3-1; 6. Como se deseja obter o primeiro dia útil a partir dessa data, digite 1; 7. Nesta atividade o final de semana é composto por sábado e domingo. Logo, o parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;

Senac São Paulo

Excel 2010 – Avançado

8. Na planilha Feriados, na coluna A, estão os feriados do mês de abril de 2010. Selecione a planilha Feriados e clique sobre a indicação da coluna A. Marque a coluna inteira para evitar refazer as fórmulas caso seja inserido algum feriado não previsto. Pressione ENTER e terá a seguinte fórmula na célula: = DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A) 9. Agora você vai calcular o próximo dia útil a partir da célula A6. Clique na célula A7 e digite a seguinte fórmula: = DIATRABALHO.INTL(A6;1;1;Feriados!A:A) 10. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2010.

11. Feche a pasta Caixa.xlsx.

Função DIATRABALHOTOTAL.INTL Essa função retorna o número de dias úteis entre duas datas usando parâmetros para indicar quais e quantos dias são dias de fim de semana, bem como feriados. •

Sintaxe: o

•

DIATRABALHO.INTL(data_inicial;data_final;fim_de_semana;feriados)

Argumentos:

Senac São Paulo

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 são considerados como pertencentes ao final de semana. Pode ter os seguintes valores:

Excel 2010 – Avançado

FIM_DE_SEMANA

DIAS DE FIM DE SEMANA

1 ou omitido

Sábado, Domingo

Domingo, Segunda

Segunda-feira, Terça-feira

Terça-feira, Quarta-feira

Quarta-feira, Quinta-feira

Quinta-feira, Sexta-feira

Sexta-feira, Sábado

Apenas Domingo

Apenas Segunda-feira

Apenas Terça-feira

Apenas Quarta-feira

Apenas Quinta-feira

Apenas Sexta-feira

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.

Seu trabalho neste momento é calcular o salário que deve ser pago por uma empresa aos seus estagiários. Esse controle é efetuado através da planilha Pagamento Estagiarios.xlsx. 1. Abra a pasta Pagamento Estagiarios.xlsx. 2. O primeiro passo é calcular quantos são os dias de vigência do contrato, ou seja, quantos dias corridos existem entre a data de início e a data de término do contrato. Para saber quantos dias existem entre duas datas, basta subtrair a data inicial da data final. Dessa forma, selecione a célula F4 e digite: = D4-C4 3. Copie a fórmula para todos os estagiários. 4. No entanto, o salário é pago considerando o número de dias a trabalhar no período, ou seja, o número de dias úteis do contrato, e não o número de dias corridos. Clique na célula G4 e digite a fórmula: = DIATRABALHOTOTAL.INTL(C4;D4;1;Feriados!A:A)

Senac São Paulo

Excel 2010 – Avançado

Onde:

C4 D4 1 Feriados!A:A

data inicial data final final de semana formado por sábado e domingo lista de feriados no ano de 2010

5. Copie a fórmula para todos os estagiários. 6. Agora calcule quanto cada estagiário deve receber, sabendo que o valor pago por hora está armazenado na célula C14. Clique na célula H4 e digite a fórmula: = E4*G4*$C$14 7. Copie a fórmula para todos os estagiários.

8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi então solicitado que uma nova coluna fosse inserida na planilha informando quantos dias corridos deveriam ser acrescidos aos contratos. Sua tarefa agora é inserir essa coluna e efetuar o cálculo das novas datas de término dos contratos para que o cálculo dos salários fique correto. Insira duas novas colunas na planilha antes da coluna Carga horária diária:

9. Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor aparecer uma data, formate os valores como números.

Senac São Paulo

Excel 2010 – Avançado

10. Se você tiver uma data e somar um número a essa data, você terá a data correspondente à data inicial mais o número de dias somado. Portanto, para calcular a data efetiva de término do contrato, temos que adicionar o número de dias de prorrogação do contrato à data de término do contrato. Clique na célula F4 e digite a fórmula: = D4+E4 11. Copie a fórmula para todos os estagiários. 12. Agora é necessário recalcular os dias de vigência do contrato e os dias a trabalhar no período, pois a data final do contrato é a nova data calculada, que se encontra na coluna Data de término efetiva. Refaça as fórmulas e confira o resultado:

13. Feche e pasta Pagamento Estagiários.xlsx. 14. Agora você vai efetuar cálculos com horas. Abra a pasta Atividades e horas.xlsx. Nessa pasta você encontra duas planilhas: Controle e Feriados. Na planilha Controle você vai calcular quanto o funcionário deve receber em função do tempo trabalhado nos dias úteis de fevereiro de 2010 e do valor que deve receber por hora.

15. Clique na célula B2 e digite: 01/02/2010.

Senac São Paulo

Excel 2010 – Avançado

16. Formate a célula para aparecer o nome do mês. 17. A partir da célula A5 preencha até a célula A23 com os dias úteis de fevereiro. Utilize o mesmo raciocínio do exercício anterior. Os feriados de fevereiro estão na planilha Feriados. 18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de copiar os valores, e não as fórmulas. 19. Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana. 20. Calcule agora o tempo trabalhado em cada dia. Esse tempo é calculado subtraindo o horário de entrada do horário de saída. Para isso, clique na célula E5 e digite: = D5-C5 21. Copie a fórmula para todo o intervalo.

Função HORA Essa função retorna a hora de um valor de tempo. A hora é retornada 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 Essa função retorna os minutos de um valor de tempo. O valor retornado é 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.

1. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a seguinte fórmula: 2. = HORA(E5) 3. Copie a fórmula para todos os dias. Se o valor para número de horas estiver aparecendo como um valor do tipo hora, formate para que seja mostrado como número. 4. Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite a seguinte fórmula:

5. = MINUTO(E5) 6. Copie a fórmula para todos os dias. Se o valor para número de minutos estiver aparecendo como um valor do tipo hora, formate para que seja mostrado como número. 7. Para calcular o valor a pagar você deve multiplicar o número de horas pelo valor por hora e somar com a multiplicação do número de minutos pelo valor por hora dividido por 60. Clique na célula I5 e digite a fórmula: = F5*$I$2+G5*$I$2/60 8. Copie a fórmula para todos os dias. 9. Totalize o valor a pagar na célula I24.

Senac São Paulo

Excel 2010 – Avançado

Formatação CONDICIONAL A formatação condicional permite que os dados sejam mostrados na planilha de uma forma mais objetiva. Você pode, por exemplo, modificar a cor da fonte ou da célula para que os dados que satisfazem critérios estabelecidos sejam exibidos. 1. Na coluna Dia da semana você vai mostrar em vermelho todos os dias que forem segunda-feira. No entanto você deve lembrar que o valor que está armazenado na célula é uma data, e não o nome do dia da semana. Para obter o número do dia da semana da data armazenada, você deve utilizar a função DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23. 2. Na guia Página Inicial, grupo Estilo, clique em Formatação Condicional e, no menu apresentado, selecione Nova regra.

3. Na janela exibida, selecione Usar uma fórmula para determinar quais células devem ser formatadas. 4. No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula:

Senac São Paulo

Excel 2010 – Avançado

=DIA.DA.SEMANA(B5;1)=2 Lembre-se que a função DIA.DA.SEMANA traz como retorno o número do dia da semana, de acordo com o tipo de semana indicado na função. No caso o argumento para tipo foi passado como 1, ou seja, domingo é o dia 1, segunda-feira é o dia 2 e assim por diante. 5. Clique no botão Formatar e escolha a cor vermelha e negrito. 6. Clique em OK e, novamente em OK.

7. Feche a pasta Atividades e horas.xlsx.

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto

1. Abra a planilha Faculdade.xlsx.

Siga os procedimentos descritos para efetuar os cálculos: a) Selecione a planilha Boletim. b) Calcule a média dos alunos. Utilize a função de tratamento de erros se for necessário em todos os cálculos que serão efetuados. c) Preencha a coluna MENSAGEM MÉDIA considerando a seguinte condição: se a média for maior ou igual a 5, a mensagem deve ser "Média OK", caso contrário deve ser "Média abaixo do limite". d) Preencha a coluna MENSAGEM FALTAS considerando a seguinte condição: se o número de faltas for menor ou igual a 25% das aulas dadas, a mensagem deve ser "Faltas OK", caso contrário deve ser "Faltas acima do limite". e) Calcule o RANKING DE MÉDIAS classificando os alunos por média. f)

Preencha a coluna RESULTADO da seguinte forma: se a média está OK E faltas também OK, então colocar “Aprovado”, caso contrário colocar “Reprovado”.

g) Formate condicionalmente a coluna RESULTADO colocando em vermelho os reprovados e em azul os aprovados. h) Preencha a planilha Resumo com as informações: número total de alunos, maior e menor média, número de aprovados e de reprovados, número de reprovados por média, por falta, por média e por falta, número de alunos que não entregaram o exercício, número de alunos que entregaram o exercício, média das médias dos alunos aprovados, média de faltas dos alunos com média OK e reprovados.

Senac São Paulo

Excel 2010 – Avançado

2. Abra a pasta Atendimento ao cliente.xlsx.

Nessa pasta você encontrará as planilhas ATENDIMENTO e Feriados. A primeira planilha deve ser preenchida da seguinte forma: a) Considere o mês de novembro. b) Preencha a coluna DATA com os dias úteis de novembro. c) Calcule o tempo de atendimento de cada cliente. d) Calcule a data para retorno considerando o dia do atendimento e o prazo (em dias) para retorno. e) Calcule o prazo para retorno em dias úteis, considerando a data do atendimento e a data para retorno. f)

Utilize a planilha Feriados para informar os feriados do ano de 2010.

Senac São Paulo

Excel 2010 – Avançado

3. Abra a pasta Banco.xlsx. Siga os seguintes procedimentos para efetuar os cálculos na planilha:

a) Calcular o Saldo Atual. b) A coluna Situação deve ser preenchida da seguinte forma: se o cliente está com saldo positivo e a média entre o saldo anterior e o saldo atual for superior ou igual a R$ 3.000,00, deve ser apresentada a mensagem “Cheque Especial”, caso contrário deve ser apresentada a mensagem “Cheque Comum. c) A coluna Limite do Cheque Especial deve ser preenchida da seguinte forma: se o cliente não tiver cheque especial, a célula deve ser preenchida com zero; se o cliente tiver Cheque Especial e se o total de créditos for menor ou igual a R$ 3.000,00, o valor do cheque especial será de R$ 2.000,00, caso contrário (cliente com cheque especial e o total de créditos maior do que R$ 3.000,00) o valor do cheque especial será de R$ 5.000,00. d) Contar o número de clientes que receberam cheque especial. 4. Abra a pasta Idades e cursos.xlsx. Nessa pasta temos duas planilhas: Dados e Relatório.

Senac São Paulo

Excel 2010 – Avançado

a) Calcular a idade de cada aluno. Para isso considere o ano da data de hoje e o ano da data de nascimento do aluno. b) Preencher a planilha Relatório contando o número de alunos do sexo feminino, masculino, matriculados em biologia, educação física, história, letras, matemática e química e o número de alunos com idade maior do que 50 anos, menor do que 25 anos, maior de 40 anos e maior do que 30 anos. 5. Abra a pasta Vendedores.xlxs. Calcular o valor da Comissão e do Salário Total devido aos vendedores.

Senac São Paulo

Excel 2010 – Avançado

a) A coluna Comissão deve ser preenchida como sendo o valor da comissão sobre o valor total das vendas. b) A coluna Salário Total deve ser preenchida com o valor da comissão mais o valor do salário mínimo se o vendedor atingiu um volume de vendas maior ou igual a R$ 10.000,00. Caso contrário, a célula deve ser preenchida com o valor do salário mínimo. c) Calcular o total de vendas por funcionário. d) Calcular o valor da maior e menor venda.

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Utilizar as funções de pesquisa PROCV, PROCH, CORRESP e ÍNDICE.  Executar auditoria em fórmulas

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

Funções de PESQUISA

As funções de pesquisa do Excel são poderosas ferramentas que permitem a busca de dados de forma rápida, muitas vezes evitando o uso de várias funções SE aninhadas.

Atividade 1 – Utilizando as funções PROCV e PROCH Objetivos :

• Utilizar as funções PROCV e PROCH.

Tarefas

• Abrir a pasta Relatório de Vendas.xlsx.

• Através da utilização da função PROCV, preencher a planilha Relatório com os nomes dos funcionários, comissões, salário fixo e imposto. • Calcular o valor a receber para cada funcionário. • Através da utilização da função PROCH, preencher a coluna Classificação faixas com as devidas faixas.

Função PROCV A função PROCV permite procurar por um valor na primeira coluna de um intervalo de células e trazer outro valor da mesma linha em outra coluna especificada. •

Sintaxe: o PROCV(valor_procurado;intervalo_células;núm _coluna,[procurar_intervalo])

•

Argumentos: o valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode ser um valor ou uma referência. o 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. o núm_coluna: número da coluna do intervalo de células que contém a informação que deve ser retornada. o 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 este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste 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. Neste 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. 1. Abra a pasta Relatório de Vendas.xlsx. Selecione a planilha Relatório. 2. Preencha a coluna NOME DO FUNCIONÁRIO com os nomes dos funcionários, procurando-os na planilha Suporte. Clique na célula B2 e digite: = PROCV(A2;

Senac São Paulo

Excel 2010 – Avançado

Você iniciou a digitação da função PROCV informando o primeiro argumento, que é o que se deseja procurar. No caso, você deve procurar pelo código do funcionário na planilha Suporte. 3. Selecione a planilha Suporte. Como o mouse clicado, selecione a região A2:B23. Essa região é a que contém o valor a ser procurado (o código do funcionário, que está na primeira coluna do intervalo) e o valor a ser retornado (o nome do funcionário, que está na segunda coluna do intervalo). A fórmula fica da seguinte forma: = PROCV(A2; Suporte!$A$2:$B$23; Esse argumento indica qual intervalo de células será considerado para a procura e retorno dos dados. Observe que o intervalo A2:B23 da planilha Suporte foi fixado, pois não pode ser modificado quando você copiar a fórmula para as células de baixo. Outra forma de evitar que os endereços sejam modificados na cópia é atribuir um nome ao intervalo. 4. Digite 2 e pressione a tecla ENTER. O parâmetro 2 indica que o valor que se deseja que retorne é o que está na coluna 2 do intervalo de células indicado. = PROCV(A2;Suporte!$A$2:$B$23;2) 5. Copie a fórmula para todos os funcionários. 6. Agora calcule o valor da comissão, salário fixo e impostos para cada funcionário. Esses valores estão em Suporte e dependem do valor da venda efetuada pelo funcionário. Atribua um nome à região da planilha Suporte onde será procurado o valor da venda e retornados os valores desejados para facilitar a digitação das fórmulas. Selecione a planilha Suporte e selecione o intervalo E2:H7. 7. Na guia Fórmulas, grupo Nomes definidos selecione Definir Nome. Na janela apresentada digite Valor_Vendas e clique em OK.

8. Você vai procurar pelo valor da venda na região Valor_Vendas e trazer o percentual de comissão. Esse percentual é obtido pela procura do valor da venda na região Valor_Vendas e trazendo o conteúdo da célula que está na coluna 2. O valor da comissão deve ser calculado como sendo o percentual de comissão multiplicado pelo valor da venda. Selecione a planilha Relatório. Clique na célula D2 e digite a seguinte fórmula: = PROCV(C2;Valor_Vendas;2) * C2 9. Copie a fórmula para todos os vendedores.

Senac São Paulo

Excel 2010 – Avançado

10. Agora você vai preencher a coluna Salário Fixo com o valor do salário de cada vendedor. Mais uma vez o valor da venda dever ser procurado no intervalo Valor_Vendas . A função PROCV deve retornar o valor do salário fixo que está na coluna 3 do intervalo. Clique na célula E2 e digite a fórmula: = PROCV(C2;Valor_Vendas;3) 11. O cálculo do imposto, nessa empresa, é feito sobre o valor das comissões. Agora você vai calcular quanto de imposto cada funcionário deve pagar. Para isso você deve procurar pelo percentual de imposto de cada um no intervalo Valor_Vendas e multiplicar pelo valor da comissão. Clique na célula F2 e digite a fórmula: = PROCV(C2;Valor_Vendas;4) * D2 12. Calcule o valor a receber para cada funcionário como sendo: comissão + salário fixo – imposto.

Função PROCH A função PROCH permite 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: o PROCH(valor_procurado;intervalo_células;núm _linha,[procurar_intervalo])

•

Argumentos: o valor_procurado: valor a ser procurado na primeira linha do intervalo de células. Pode ser um valor ou uma referência. o 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. o núm_linha: número da linha do intervalo de células que contém a informação que deve ser retornada. o 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 este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste 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. Neste 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, e a segunda de forma horizontal. Você pode utilizar qualquer uma das duas, dependendo da disposição do intervalo de células adotada. 1. Você vai preencher a coluna Classificação faixas de acordo com as vendas de cada funcionário. Os valores a serem colocados nessa coluna estão na planilha Suporte. Selecione a planilha Suporte e selecione o intervalo de células F10:I11. Nomeie esse intervalo como Faixas.

Senac São Paulo

Excel 2010 – Avançado

2. Selecione a planilha Relatório. Clique na célula H2 e digite a seguinte fórmula:

= PROCH(C2;Faixas;2) 3. Copie a fórmula para todos os vendedores.

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Utilizando as funções CORRESP e ÍNDICE Objetivo :

• Deslocar e rotacionar um objeto pelo teclado.

Objetivos :

• Utilizar as funções CORRESP e ÍNDICE.

Tarefas

• Calcular os valores de vendas máximo e mínimo.

• Identificar a posição desses valores dentro da planilha de vendas. • Identificar os nomes dos vendedores responsáveis pelos valores máximo e mínimo de vendas.

Função CORRESP Essa função traz como resultado a posição em que o valor procurado encontra-se no intervalo de células indicado, que deve ser formado por uma linha de células ou por uma coluna de células. •

Sintaxe: CORRESP(valor_procurado;intervalo;[correspondência])

•

Argumentos: o valor_procurado: argumento obrigatório, sendo o valor que desejamos procurar no intervalo de células e obter sua posição relativa dentro dele. o intervalo: argumento obrigatório, sendo o intervalo de células que estão sendo pesquisadas. Esse intervalo deve ser uma linha de células ou uma coluna de células. o correspondência: argumento opcional que especifica como o valor será encontrado. Pode ser: 

1 ou omitido: Localiza o maior valor que é menor ou igual a 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 igual a valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem decrescente.

1. Agora você vai verificar em que local da planilha se encontram os valores para maior e menor venda. Selecione a planilha Controle. 2. Calcule a maior e menor venda na coluna VALOR. Clique na célula E5 e digite: = CORRESP( 3. Você vai procurar pelo valor da maior venda, que está na célula D5. Continue a fórmula digitando D5; = CORRESP(D5; 4. Agora você vai informar o intervalo de células onde será procurado o valor da venda. Esse intervalo é C2:C23 da planilha Relatório. = CORRESP(D5; Relatório!C2:C23; 5. Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será 0. A fórmula final é a seguinte: Senac São Paulo

Excel 2010 – Avançado

= CORRESP(D5; Relatório!C2:C23;0) 6. Repita os mesmos procedimentos para encontrar a posição da menor venda na planilha Relatório.

Função ÍNDICE Essa função traz o conteúdo de uma célula através do fornecimento de sua posição dentro do intervalo de células. •

Sintaxe: ÍNDICE(

•

Argumentos:

intervalo;linha;[coluna])

o intervalo: argumento obrigatório, sendo o intervalo de células que contém o dado a ser retornado. o linha: argumento obrigatório, sendo o número da linha do intervalo onde está o dado a ser retornado. o coluna: argumento opcional, sendo o número da coluna do intervalo onde está o dado a ser retornado. 7. Agora você deve retornar os nomes dos vendedores que efetuaram a maior e menor venda. Clique na célula F5 e digite: = ÍNDICE( 8. Você deve informar o intervalo de células que contém o nome do vendedor que está sendo procurado. Para tanto digite na fórmula: = ÍNDICE(Relatório!B2:B23; 9. Agora você deve informar em que linha desse intervalo encontra-se o dado desejado. Pela função CORRESP você determinou, anteriormente, o número da linha onde está a maior venda. Portanto, o parâmetro que você deve informar na função é exatamente o resultado da função CORRESP, que se encontra na célula E5 da planilha Controle. A fórmula completa fica então: = ÍNDICE(Relatório!B2:B23;Controle!E5) 10. Utilize os mesmos procedimentos para trazer o nome do vendedor que efetuou a menor venda.

Senac São Paulo

Excel 2010 – Avançado

AUDITORIA DE FÓRMULAS

Muitas vezes é necessário executar auditoria em fórmulas inseridas em suas planilhas. Muitas vezes as fórmulas podem gerar erros ou mesmo retornarem valores que não são os esperados. O Excel 2010 fornece ferramentas bastante úteis que você possa utilizar para localizar e investigar as causas dos erros e determinar soluções. Essas ferramentas incluem o rastreamento de precedentes e dependentes de uma célula, rastreamento de erros, exibição e depuração das fórmulas. Todas essas ferramentas estão presentes no grupo Auditoria de Fórmulas da guia Fórmulas.

Atividade 3 – Rastreando os precedentes de uma célula Objetivos :

• Rastrear as células precedentes de uma célula

Tarefas

• Abrir a pasta Faculdade.xlsx.

• Rastrear as células precedentes das células da planilha Boletim. • Rastrear as células precedentes das células da planilha Resumo. A ferramenta Rastrear Precedentes permite verificar quais células afetam o valor da célula selecionada no momento, ou seja, de que células aquela que se encontra selecionada depende para o cálculo de seu valor. 1. Abra a pasta Faculdade.xlsx. 2. Selecione a planilha Boletim. 3. Clique na célula I5. 4. Clique na ferramenta Rastrear Precedentes, do grupo Auditoria de Fórmulas, da guia Fórmulas. 5. Veja que uma seta vem da célula K2 e outra vem da célula H5, ou seja, o resultado da fórmula contida na célula I5 depende dos valores das células K2 e H5. Isso é mesmo verdade, pois a célula I5 contém a fórmula: =SE(H5<=25%*$K$2;"Faltas OK";"Faltas acima do limite")

Se você clicar duas vezes em cima da seta, a célula precedente fica selecionada. 6. Selecione a planilha Resumo.

Senac São Paulo

Excel 2010 – Avançado

7. Clique na célula B14. 8. Clique na ferramenta Rastrear Precedentes.

A linha pontilhada indica que a célula ou células precedentes encontram-se em outra planilha. 9. Clique duas vezes sobre a linha pontilhada. A janela mostra a origem das células precedentes, com a indicação da pasta, planilha e endereço de célula ou intervalo. Para acessar a origem desejada, basta selecioná-la e clicar no botão OK.

10. Remova as setas selecionando a ferramenta Remover Setas. Você deve remover as setas das duas planilhas.

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Rastreando os dependentes de uma célula Objetivos :

• Rastrear as células dependentes de uma célula

Tarefas

• Rastrear as células dependentes das células da planilha Boletim.

• Rastrear as células dependentes das células da planilha Resumo. A ferramenta Rastrear Dependentes permite verificar quais células são afetadas pelo valor da célula selecionada no momento. 1. Selecione a planilha Boletim e clique na célula I5. 2. Clique na ferramenta Rastrear Dependentes, do grupo Auditoria de Fórmulas, da guia Fórmulas. 3. Veja que uma seta vai para a célula K5 e uma linha pontilhada vai para outra planilha.

A primeira seta, indicando a célula K5, mostra que a fórmula =SE(E(G5="Média OK";I5="Faltas OK");"Aprovado";"Reprovado") depende do valor da célula I5. A seta indicada pela linha pontilhada e apontando para outra planilha, mostra que as células B8, B9 e B10 da planilha Resumo dependem do valor da célula I5. Podemos verificar essas referências clicando duas vezes sobre a linha pontilhada.

4. Remova as setas da planilha.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Rastreando erros em fórmulas Objetivos :

• Rastrear erros em fórmulas

Tarefas

• Gerar um erro na fórmula que calcula a média de um aluno.

• Rastrear esse erro. A ferramenta Verificação de Erros permite localizar, rastrear e realizar as etapas de cálculo de uma célula que contenha um erro. 1. Clique na célula F5 e modifique a sua fórmula para: = MÉDIA(B5:E5) 2. Observe que muitas células agora apresentam erros, ocasionados pelo erro da célula F5.

3. Clique na célula K5 e observe que no momento em que a célula foi selecionada, você tem a indicação que está ocorrendo.

Senac São Paulo

Excel 2010 – Avançado

4. Clique na setinha ao lado do botão mostrado e selecione Rastrear erro e observe as setas mostradas:

Existe uma seta vinda da célula G5 que, por sua vez, contém uma seta vinda da célula F5 que, por sua vez, contém uma seta vinda do intervalo de células B5:E5. Ou seja, o erro apontado na célula K5 é decorrente dos erros apontados nas células G5, F5 e do intervalo B5:E5. Na verdade, esse erro é causado pela tentativa de efetuar o cálculo da média aritmética com conteúdos de células vazias. 5. Retorne a fórmula da célula F5 ao seu valor original: =SEERRO(MÉDIA(B5:E5);0)

Senac São Paulo

Excel 2010 – Avançado

Atividade 6 – Avaliando fórmulas Objetivos :

• Depurar fórmulas

Tarefas

• Verificar as etapas de cálculo de uma fórmula da planilha Boletim.

A ferramenta Avaliar Fórmula permite depurar uma fórmula, analisando individualmente cada uma de suas partes. É bastante útil quando o resultado retornado não é o esperado. 1. Clique na célula I5 e selecione a ferramenta Avaliar Fórmula. 2. Na janela exibida você pode efetuar, passo a passo, os cálculos que estão envolvidos na fórmula.

Para mostrar o resultado da expressão que está sublinhada, clique em Avaliar. Cada vez que você clica nesse botão, a expressão sublinhada é calculada mostrando o resultado recente. Dessa forma você pode verificar todas as etapas de cálculo da fórmula. 3. Clique em Avaliar. Perceba que, a cada vez que clicarmos nesse botão, os cálculos são efetuados, até que a expressão chegue ao seu resultado final.

Se quiser reiniciar a avaliação, clique no botão Reiniciar.

Senac São Paulo

Excel 2010 – Avançado

4. Clique em Fechar. Selecione a célula K5 e selecione a ferramenta Avaliar Fórmula. 5. Clique no botão Depuração total.

Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula K5 para que seja avaliada. 6. Clique novamente em Depuração total.

Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula G5 para que seja avaliada. Uma vez que se tenha abertas todas as fórmulas envolvidas no cálculo da célula K5, você pode avaliar cada uma delas individualmente. Basta clicar dentro do quadro correspondente e efetuar a avaliação até o resultado final. 7. Efetue a avaliação de cada fórmula. 8. Para finalizar, clique em Fechar.

Senac São Paulo

Excel 2010 – Avançado

Atividade 7 – Mostrando fórmulas Objetivos :

• Mostrar as fórmulas das planilhas

Tarefas

• Exibir as fórmulas das planilhas Boletim e Resumo.

A ferramenta Mostrar Fórmulas permite a visualização das fórmulas nas células das planilhas ao invés de seus resultados. 1. Selecione a planilha Resumo. 2. Clique na ferramenta Mostrar Fórmulas.

Observe que, ao invés dos resultados, você visualiza as fórmulas. 3. Selecione a planilha Boletim. 4. Mostre as fórmulas nas células. Para mostrar novamente os resultados nas células, clique na ferramenta Mostrar Fórmulas. 5. Feche a pasta Faculdade.xlsx.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta IMC.xlsx. a) Calcule o índice de massa corpórea (IMC) de cada pessoa como sendo peso/altura2. Lembre-se que para o cálculo de exponenciação utiliza-se o caractere ^. b) Preencha a coluna Resultado com a mensagem adequada de acordo com a classificação do IMC. c) Preencha a coluna Regime com a mensagem adequada de acordo com a classificação do IMC. 2. Abra a pasta Escola de Idiomas.xlsx. a) Preencha a coluna CURSO com o curso correspondente a seu código. b) Preencha a coluna MENSALIDADE com a mensalidade correspondente ao código do curso. 3. Abra a pasta Escritório.xls. a) Na planilha Atraso calcule o total de atrasos para cada funcionário. b) Preencha a coluna Atrasos da planilha Salários de acordo com a tabela constante da planilha Atraso. c) O valor dos atrasos deve ser calculado da seguinte forma: se o número de atrasos for maior do que 5, o valor será o valor hora (salário fixo / 240) acrescido de 20%; caso contrário, será o próprio valor hora. d) Calcular o valor da coluna Total de Atrasos em função das colunas Atrasos e Valor dos Atrasos. e) Calcule o número total de horas extras para cada funcionário na planilha Hora Extra. Nessa mesma planilha, calcule o número de pessoas que têm horas extras em cada semana e no total. f)

Traga para a coluna Hora Extra da planilha Salários o total de horas extras de cada funcionário.

g) O valor da hora extra deve ser calculado da seguinte forma: se o número de atrasos no mês for maior do que a média das horas de atraso de todos os funcionários, o valor da hora extra deve ser de 20% a mais sobre o valor da hora normal (salário fixo / 240); caso contrário, o valor deverá ser 35% a mais sobre o valor da hora normal. h) Calcule a coluna Total da Hora Extra. i)

Calcule o Salário Total como sendo o Salário Fixo + Total da Hora Extra – Total dos Atrasos.

A coluna Condição deve ser preenchida com a mensagem “Destaque” para os funcionários que tiveram um número de horas de atraso menor ou igual a 1 e fizeram mais de 7 horas extras no mês; caso contrário a coluna deve ficar em branco.

k) O funcionário deve receber um recado no holerite de acordo com a seguinte regra: se o número de horas de atraso for maior do que 5 ou se o número de horas extras for menor do que 2, a mensagem será “Precisa melhorar”; caso contrário, a mensagem será “Bom Trabalho!”. 4. Abra a planilha Impostos.xlsx. Calcule o Imposto de Renda e o INSS a pagar para cada um dos salários de acordo com as tabelas a seguir: a) Imposto de Renda Salário Até 1.499,15 De 1.499,16 até 2.246,75 De 2.246,76 até 2.995,70

Senac São Paulo

Alíquota 0% 7,5% 15,0%

Parcela a deduzir 0 112,43 280,94 63

Excel 2010 – Avançado

De 2.995,71 até 3.743,19 Acima de 3.743,19

22,5% 27,5%

505,62 692,78

O cálculo do imposto devido é feito multiplicando-se o valor do salário pela alíquota correspondente e subtraindo-se a parcela a deduzir. b) INSS Salário Até 1.040,22 De 1.040,23 até 1.733,70 De 1.733,71 até 3.467,40

Alíquota 8% 9% 11%

O cálculo da contribuição devida é feito multiplicando-se o valor do salário pela alíquota correspondente. Se o salário for maior do que R$ 3.467,40, o desconto será de R$ 381,41, independente do valor do salário.

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Utilizar as funções de texto ARRUMAR, DIREITA, ESQUERDA, EXT.TEXTO, MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA.

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

Funções de PESQUISA

As funções de texto do Excel são ferramentas bastante úteis no tratamento das informações. Em muitos casos essas informações são provenientes de outros bancos de dados e nem sempre são exibidas da forma mais adequada. Através da utilização das funções de texto do Excel esses problemas são resolvidos obtendo a visualização correta dos dados.

Atividade 1 – Utilizando a função ARRUMAR Objetivos :

• Utilizar a função ARRUMAR.

Tarefas

• Abrir a pasta Rodízio.xlsx.

• Eliminar os espaços em branco no início das células da coluna MARCA da planilha Dados. Desde 1997 está em vigor, na cidade de São Paulo – SP, o rodízio municipal de veículos, com o propósito de melhorar as condições ambientais e também de reduzir os congestionamentos nas principais vias da cidade nos horários de maior movimento. Os automóveis e caminhões não podem circular em uma determinada região, denominada Centro Expandido, nos períodos de 07:00 – 10:00 horas e 17:00 – 20:00 horas todos os dias, de segunda a sexta-feira, seguindo uma tabela de acordo com o final da chapa do veículo. Assim: Veículos com finais de chapa 1 e 2

Não circulam às segundas-feiras

Veículos com finais de chapa 3 e 4

Não circulam às terças-feiras

Veículos com finais de chapa 5 e 6

Não circulam às quartas-feiras

Veículos com finais de chapa 7 e 8

Não circulam às quintas-feiras

Veículos com finais de chapa 9 e 0

Não circulam às sextas-feiras

Recebemos uma pasta contendo os dados de vários veículos e devemos, então, verificar na data corrente quais veículos podem circular livremente e quais devem obedecer ao rodízio. Além disso, os dados chegaram de forma não muito adequada, com espaços em branco indevidos no início dos campos, letras minúsculas ao invés de maiúsculas e outros acertos que devem ser feitos. Para essa tarefa vamos utilizar as funções de texto do Excel, além de funções já estudadas, como SE e PROCV.

Função ARRUMAR Essa função tem como objetivo eliminar os espaços em branco à esquerda e à direita do texto. •

Sintaxe: ARRUMAR(texto)

•

Argumentos: texto: texto do qual se deseja remover espaços à direita ou à esquerda.

1. Abra a pasta Rodízio.xlsx. 2. Selecione a planilha DADOS.

Senac São Paulo

Excel 2010 – Avançado

3. Você vai arrumar a coluna referente a MARCA para eliminar os espaços em branco no início de cada célula. Esse ajuste será efetuado na planilha INTERMEDIÁRIA. Selecione a planilha INTERMEDIÁRIA e clique na célula A2.

4. O texto onde se deseja eliminar os espaços indevidos está na célula A2 da planilha DADOS. Portanto, digite a seguinte fórmula: =ARRUMAR(DADOS!A2) 5. Copie a fórmula até a célula A18.

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA Objetivos :

• Utilizar as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA.

Tarefas

• Converter para letras maiúsculas os conteúdos das células referentes à coluna PLACA da planilha DADOS.

• Converter para letras minúsculas os conteúdos das células referentes à coluna COMENTÁRIOS da planilha DADOS.

Função MAIÚSCULA Essa função tem como objetivo converter o texto para caracteres maiúsculos. •

Sintaxe: MAIÚSCULA(texto)

•

Argumentos: texto: texto que se deseja converter para maiúsculas.

Função MINÚSCULA Essa função tem como objetivo converter o texto para caracteres minúsculos. •

Sintaxe: MINÚSCULA(texto)

•

Argumentos: texto: texto que se deseja converter para minúsculas.

Função PRI.MAIÚSCULA Essa função tem como objetivo colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia de texto. •

Sintaxe: PRI.MAIÚSCULA(texto)

•

Argumentos: texto: texto que se deseja converter parcialmente para maiúsculas.

1. A coluna PLACA da planilha DADOS está mostrando as placas dos automóveis com a identificação das letras em caracteres minúsculos. Você vai modificar para que apareçam em letras maiúsculas. Selecione a planilha INTERMEDIÁRIA, clique na célula B2 e digite a fórmula: = MAIÚSCULA(DADOS!B2) 2. Copie a fórmula para todos os automóveis. 3. Agora você vai converter os valores da coluna COMENTÁRIOS da planilha DADOS para minúsculas. Ainda na planilha INTERMEDIÁRIA clique na célula C2 e digite: = MINÚSCULA(DADOS!C2) 4. Copie a fórmula para todos os automóveis. 5. Os nomes dos proprietários dos automóveis estão escritos em letras minúsculas. Vamos corrigir esse erro, colocando todas as iniciais em maiúsculas. Ainda na planilha INTERMEDIÁRIA clique na célula D2 e digite: =PRI.MAIÚSCULA(DADOS!D2) 6. Copie a fórmula para todos os proprietários.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO Objetivos :

• Utilizar as funções ESQUERDA, DIREITA e EXT.TEXTO.

Tarefas

• Retornar as letras das chapas dos automóveis.

• Retornar as duas últimas letras das chapas dos automóveis. • Retornar o último número das chapas dos automóveis.

Função ESQUERDA Essa função tem como objetivo retornar os primeiros n caracteres à esquerda de uma cadeia de texto. •

Sintaxe: ESQUERDA(texto;[número_caracteres])

•

Argumentos: o texto: texto de onde se deseja extrair os caracteres à esquerda. o número_caracteres: argumento opcional que indica o número de caracteres à esquerda que se deseja extrair do texto. Se omitido, trará o primeiro caractere à esquerda.

Função DIREITA Essa função tem como objetivo retornar os últimos n caracteres à direita de uma cadeia de texto. •

Sintaxe: DIREITA(texto;[número_caracteres])

•

Argumentos: o texto: texto de onde se deseja extrair os caracteres à direita. o número_caracteres: argumento opcional que indica o número de caracteres à direita que se deseja extrair do texto. Se omitido, trará o último caractere à esquerda.

Função EXT.TEXTO Essa função tem como objetivo retornar os n caracteres à partir de uma determinada posição em uma cadeia de texto. •

Sintaxe: EXT.TEXTO(texto;posição;número_caracteres)

•

Argumentos: o texto: texto de onde se deseja extrair os caracteres. o posição: posição do primeiro caractere a ser extraído do texto. o número_caracteres: número de caracteres que devem ser extraídos a partir da posição do primeiro caractere considerado.

1. Você vai extrair as letras das chapas dos automóveis. Para isso, clique na célula E2 da planilha INTERMEDIÁRIA, e digite: = ESQUERDA(B2;3) 2. Copie a fórmula para todos os automóveis. 3. Agora você vai extrair as duas últimas letras das placas dos automóveis, considerando que se deseja trazer, a partir da posição 2, os dois caracteres do conteúdo das células que estão na coluna PLACA da planilha INTERMEDIÁRIA. Clique na célula F2 e digite:

Senac São Paulo

Excel 2010 – Avançado

= EXT.TEXTO(E2;2;2) 4. Copie a fórmula para todos os automóveis. 5. Extraia o último número das chapas dos automóveis. Para isso, clique na célula G2 da planilha INTERMEDIÁRIA, e digite: = DIREITA(B2;1) 6. Copie a fórmula para todos os automóveis. 7. Para completar a tarefa você deve preencher a planilha RODÍZIO. Nessa planilha você vai ter a indicação dos automóveis que estão ou não no rodízio na data informada. Copie somente os valores das colunas PLACA e ÚLTIMO NÚMERO DA PLACA da planilha INTERMEDIÁRIA para as colunas PLACA e FINAL DA PLACA da planilha RODÍZIO. Copie somente os valores, e não as fórmulas. 8. Na planilha RODÍZIO clique na célula B2 e digite a fórmula para mostrar o dia de hoje. = HOJE() 9. Você vai colocar o nome do dia da semana correspondente à data de hoje na célula D2. Para isso você precisa procurar pelo número do dia da semana na tabela Dia da Semana através das funções DIA.DA.SEMANA e PROCV. Clique na célula D2 e digite: = PROCV(DIA.DA.SEMANA(B2;2);L3:M9;2) 10. Preencha a coluna DIA DO RODÍZIO com o dia da semana encontrado na tabela Rodízio a partir do final da chapa do automóvel. Clique na célula C5 e digite a fórmula: = PROCV(B5;$I$3:$J$8;2) 11. Copie a fórmula para todos os automóveis. 12. Agora você vai preencher a coluna MENSAGEM informando se o veículo está ou não no rodízio na data considerada. Clique na célula D5 e digite a fórmula: =SE(C5=$D$2;"Carro no rodízio";"Livre para circular") 13. Formate condicionalmente essa coluna para que mostre a mensagem “Carro no rodízio” em vermelho.

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta Livraria Libélula.xlsx. Na planilha Aplicação faça os seguintes ajustes: a) A coluna Nome do Livro deve conter o conteúdo da coluna Nome do Livro da planilha Original sem os espaços em branco iniciais. b) A coluna Autor deve conter os nomes dos autores da planilha Original escritos com as iniciais em letras maiúsculas. c) A coluna Editora deve ter os nomes das editoras da planilha Original escritos em letras maiúsculas. d) A coluna Pré Venda deve ter o conteúdo da coluna Pré Venda da planilha Original escrito em letras minúsculas. e) A coluna Código 1º par deve conter os dois primeiros caracteres dos códigos constantes da coluna Código da planilha Original. f)

A coluna Código 2º par deve conter os dois caracteres do meio dos códigos constantes da coluna Código da planilha Original.

g) A coluna Código 3º par deve conter os dois últimos caracteres dos códigos constantes da coluna Código da planilha Original. 2. Abra a pasta Codigos Produtos.xlsx. Na planilha Estoque cada produto é identificado por um código de formato XXXXXX-XXX que é formado pelos seguintes caracteres: a) O primeiro caractere informa o tipo de alimento de acordo com a tabela Tipo de Produto na planilha Tabelas; b) Do segundo ao quarto caractere temos a informação se o produto pertence à cesta básica ou não. Se esse conjunto for CBS, indica que o produto pertence à cesta básica; se for CBN, indica que o produto não pertence à cesta básica; c) O quinto e sexto caracteres informam a distribuidora de acordo com a tabela Distribuidoras na planilha Tabelas. Preencha as colunas da planilha Estoque da seguinte forma: a) Coluna Tipo do Produto: deve conter o tipo do produto. b) Coluna Cesta Básica: deve conter “Sim” se o produto pertence à cesta básica ou “Não” se não pertence. Coluna Distribuidora: deve conter o nome da distribuidora.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Utilizar as funções de banco de dados: BDCONTARA, BDEXTRAIR, BDMÁX, BDMÍN, BDMÉDIA e BDSOMA.

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

Funções de BANCO DE DADOS Nesse capítulo você vai aprender a utilizar funções de banco de dados para manipular informações em planilhas com características de banco de dados.

Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA Objetivos :

• Utilizar as funções BDSOMA e BDMÉDIA.

Tarefas

• Abrir a pasta Relatório Produtos.xlsx.

• Preencher um relatório com o valor total de vendas efetuadas por fornecedor. • Preencher um relatório com o valor total de vendas efetuadas por fabricante. • Preencher um relatório com o valor da média de vendas efetuadas por fornecedor. • Preencher um relatório com o valor da média de vendas efetuadas por fabricante. A pasta Relatório Produtos.xlsx contém informações sobre itens fornecidos por vários fornecedores. Seu trabalho será gerar relatórios para resumir e consolidar informações sobre os fornecedores e fabricantes dos produtos.

Função BDSOMA Essa função tem como objetivo somar os valores de uma coluna de acordo com critérios estabelecidos. •

Sintaxe: BDSOMA(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. o campo: identificação da coluna que contém os dados que serão somados. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

1. Abra a pasta Relatório Produtos.xlsx.

Senac São Paulo

Excel 2010 – Avançado

2. O primeiro passo é somar os valores de venda por fornecedor, para isso você vai criar seus critérios para utilizá-los com a função BDSOMA. Selecione a planilha Critérios. 3. Como se deseja efetuar a soma por fornecedor, os critérios devem conter a identificação do nome do campo (FORNECEDOR) e o conteúdo que se deseja procurar no campo. Digite os valores mostrados a seguir:

CUIDADO! Tanto o nome do campo quanto o conteúdo a ser procurado devem estar digitados exatamente como na planilha onde se fará a procura. É muito comum, durante a digitação, inserir um espaço em branco a mais ou mesmo pressionar alguma tecla que não gera caractere imprimível e, na hora da procura, não se obter nenhum resultado. Se isso acontecer, digite novamente as informações dos critérios.

Senac São Paulo

Excel 2010 – Avançado

4. Selecione a planilha Relatório e clique na célula B4. Você vai calcular a soma de vendas para o fornecedor 3 Manos Hipermercados. 5. Digite: = BDSOMA( 6. O primeiro argumento refere-se à região da planilha que será considerada como o banco de dados. Selecione a planilha Controle de Produtos e selecione a região A1:D24. = BDSOMA('Controle de Produtos'!A1:D24; 7. O próximo argumento é o nome do campo que será somado. No caso, é o campo Total e que está na célula D1 da planilha Controle de Produtos. = BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1; 8. O último parâmetro é o critério para efetuar a soma. Nesse caso o critério é: Fornecedor igual a 3 Manos Hipermercados. Esse critério está indicado na planilha Critérios na região A4:A5. = BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1;Critérios!A4:A5) 9. Preencha a coluna SOMA do relatório por fornecedor calculando a soma para os outros fornecedores.

Função BDMÉDIA Essa função tem como objetivo calcular a média aritmética entre os valores de uma coluna de acordo com critérios estabelecidos. •

Sintaxe: BDMÉDIA(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. o campo: identificação da coluna que contém os dados para os quais a média será calculada. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

1. Agora você vai calcular a média de vendas para cada fornecedor. Para facilitar, você vai atribuir um nome à região da planilha Controle de Produtos que corresponde ao banco de dados, um nome à célula que contém a indicação do campo onde será efetuado o cálculo e nomes às regiões que contêm os critérios. Selecione a região A1:D24 da planilha Controle de Produtos e atribua o nome Geral. 2. Selecione a célula D1 da planilha Controle de Produtos e atribua o nome Coltotal. 3. Selecione a região A1:A2 da planilha Critérios e atribua o nome Carretel. 4. Selecione a região A4:A5 da planilha Critérios e atribua o nome Manos. 5. Selecione a região A7:A8 da planilha Critérios e atribua o nome Doces. 6. Selecione a região A10:A11 da planilha Critérios e atribua o nome Flores.

Senac São Paulo

Excel 2010 – Avançado

7. Selecione a planilha Relatório e clique na célula C4. Você vai calcular a média de vendas para o fornecedor 3 Manos Hipermercado. Digite a fórmula: = BDMÉDIA(Geral;Coltotal;Manos) Onde:

Geral Coltotal Manos

identificação do banco de dados. célula que contém o título da coluna onde será calculada a média. critério.

8. Calcule a média de vendas para os outros fornecedores. 9. Agora você vai calcular a soma por fabricante. Na planilha Critérios digite os critérios para os fabricantes:

10. Nomeie os critérios: garoto, granja, maeterra, nestle, parmalat, renata, royal e união. 11. Selecione a planilha Relatório e clique na célula B13. 12. Digite a fórmula: =BDSOMA(Geral;Coltotal;garoto) 13. Calcule a soma para os outros fabricantes. 14. Seguindo o mesmo raciocínio utilizado para os fornecedores, calcule a média de vendas para os fabricantes.

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Utilizando as funções BDMÁX e BDMÍN Objetivo :

• Deslocar e rotacionar um objeto pelo teclado.

Objetivos :

• Utilizar as funções BDMÁX e BDMÍN.

Tarefas

• Preencher um relatório com o valor máximo de vendas efetuadas por fornecedor.

• Preencher um relatório com o valor máximo de vendas efetuadas por fabricante. • Preencher um relatório com o valor mínimo de vendas efetuadas por fornecedor. • Preencher um relatório com o valor mínimo de vendas efetuadas por fabricante.

Função BDMÁX Essa função tem como objetivo retornar o valor máximo dentro de uma lista de valores de acordo com as condições especificadas. •

Sintaxe: BDMÁX(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna.

o campo: identificação da coluna que contém os dados para os quais o valor máximo será calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

Função BDMÍN Essa função tem como objetivo retornar o valor mínimo dentro de uma lista de valores de acordo com as condições especificadas. •

Sintaxe: BDMÍN(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. o campo: identificação da coluna que contém os dados para os quais o valor mínimo será calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

1. Selecione a planilha Relatório e clique na célula D4. 2. Os parâmetros da função BDMÁX são os mesmos das funções BDSOMA e BDMÉDIA. Digite a fórmula: = BDMÁX(Geral;Coltotal;Manos)

Senac São Paulo

Excel 2010 – Avançado

3. Calcule o valor máximo de vendas para os outros fornecedores. 4. Calcule o valor máximo de vendas para os fabricantes. 5. Agora calcule o valor mínimo de vendas para os fornecedores. Clique na célula F4 e digite a fórmula: = BDMÍN(Geral;Coltotal;Manos) 6. Calcule o valor mínimo de vendas para os outros fornecedores. 7. Calcule o valor mínimo de vendas para os fabricantes.

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Utilizando a função BDEXTRAIR Objetivos :

• Utilizar a função BDEXTRAIR.

Tarefas

• Preencher um relatório com o nome dos produtos que apresentam o valor máximo de vendas efetuadas por fornecedor.

• Preencher um relatório com o nome dos produtos que apresentam o valor máximo de vendas efetuadas por fabricante. • Preencher um relatório com o nome dos produtos que apresentam o valor mínimo de vendas efetuadas por fornecedor. • Preencher um relatório com o nome dos produtos que apresentam o valor mínimo de vendas efetuadas por fabricante. Sua planilha Relatório mostra os valores máximo e mínimo de vendas por fornecedor e por fabricante. No entanto você precisa também saber quais são os produtos responsáveis por essas vendas. Para isso você utilizará a função BDEXTRAIR.

Função BDEXTRAIR Essa função tem como objetivo retornar um único valor em uma coluna de uma lista que coincida com as condições especificadas. •

Sintaxe: BDEXTRAIR(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. o campo: identificação da coluna que contém os dados para a extração do valor desejado. Pode ser indicado pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

1. Você vai procurar no banco de dados pelos produtos responsáveis pelas maiores e menores vendas por fornecedor. O seu critério agora é selecionar o fornecedor e o valor da sua maior venda. Para isso você deve modificar sua planilha de critérios. Selecione a planilha Critérios e clique na célula B1. 2. Nessa célula você vai digitar o nome do campo que contém o valor da maior venda do fornecedor Carretel Hipermercados na planilha Controle de Produtos. Esse campo é o campo de nome Total. Portanto, na célula B1 digite: Total 3. Selecione a célula B2. Nessa célula você vai indicar o valor da maior venda efetuada pelo fornecedor Carretel Hipermercados. Esse valor está sendo calculado na planilha Relatório na célula D5. Portanto, na célula B2 da planilha Critérios, digite: = Relatório!D5 4. Modifique os critérios para os outros fornecedores.

Senac São Paulo

Excel 2010 – Avançado

5. Selecione a planilha Relatório e clique na célula E4. 6. Digite a seguinte fórmula: = BDEXTRAIR(Geral;'Controle de Produtos'!A1;Critérios!A4:B5) Note que a coluna a ser considerada nessa função é a que contém o nome do produto na planilha Controle de Produtos (célula A1), e a região que indica o critério agora contém o campo Valor. 7. Mostre os nomes dos produtos de maior valor para todos os fornecedores. 8. Modifique a planilha Critérios para que você possa mostrar os nomes dos produtos de menor valor para todos os fornecedores:

Senac São Paulo

Excel 2010 – Avançado

9. Preencha a planilha Relatório procurando pelos produtos de menor valor. 10. Utilize o mesmo raciocínio para extrair os nomes dos produtos de maior e menor valor para cada fabricante. Construa os critérios na planilha Critérios.

Se a função BDEXTRAIR retornar o erro #Valor!, significa que o valor procurado não foi encontrado; se retornar o erro #Num!, significa que existe mais de um valor que atende o critério.

Senac São Paulo

Excel 2010 – Avançado

ANOTAÇÕES

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Utilizando a função BDCONTARA Objetivos :

• Utilizar a função BDCONTARA.

Tarefas

• Preencher um relatório com o número de produtos por fornecedor.

• Preencher um relatório com o número de produtos por fabricante.

Função BDCONTARA Essa função tem como objetivo contar as células não vazias em uma coluna de uma lista de acordo com as condições especificadas •

Sintaxe: BDCONTARA(banco de dados;campo;critérios)

•

Argumentos: o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. o campo: identificação da coluna que contém os dados que devem ser contados. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista. o critérios: intervalo de células que contém a condição especificada.

1. Selecione a planilha Relatório. 2. Clique na célula H4. 3. Os parâmetros da função BDCONTARA são os mesmos das funções anteriores. Logo, digite na célula H4 a fórmula: = BDCONTARA(Geral;'Controle de Produtos'!$B$1;Manos) 4. Preencha o relatório contando o número de produtos para os demais fornecedores e fabricantes.

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto Uma empresa vende produtos para diversos estados. De acordo com a localidade, os valores do frete e ICMS variam. Na pasta Bebidas e Alimentos.xlsx temos três planilhas: a) Nota: deve ser preenchida com os cálculos dos valores dos produtos e a forma de pagamento; b) Critérios: planilha que contém os critérios para o cálculo das funções no Relatório; c) Relatório: planilha que deve ser preenchida, por estado, com os cálculos da soma, média, produtos de menor e maior valor, quantidade dos produtos de menor e maior valor e quantidade total de produtos.

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Importar arquivos de texto para o Excel;  Importar arquivos de dados para o Excel;  Criar consulta à base de dados;  Vincular dados do Excel no Access.

Senac São Paulo

Excel 2010 – Avançado

Senac São Paulo

Excel 2010 – Avançado

Importação de Dados para o EXCEL

Muitas vezes é necessário utilizar no Excel dados que fazem parte de outros bancos de dados ou mesmo tabelas na Internet. Nesses casos você pode utilizar os recursos que o software disponibiliza quanto à obtenção de dados externos. Esses dados podem ser provenientes de arquivos texto, tabelas do Access, tabelas de Internet, do Microsoft Query e outros tipos de arquivos. Nesse curso você vai estudar alguns destes recursos.

Atividade 1 – Importando arquivos texto para o Excel Objetivos :

• Importar um arquivo txt para o Excel.

Tarefas

• Abrir o arquivo Base.txt.

• Fechar o arquivo Base.txt. • Importar o arquivo Base.txt para uma planilha do Excel.

Importando um arquivo texto Em muitas aplicações as empresas gravam os seus dados em arquivos do tipo texto, com extensão txt. Esses arquivos normalmente possuem uma primeira linha com os títulos das colunas e, a partir da segunda linha, com os dados, um registro em cada linha e as colunas (ou campos) separados por algum delimitador. Para que possam ser importados pelo Excel é necessário que seja texto puro, ou seja, não podem ser arquivos com extensões doc, docx, pdf ou quaisquer arquivos formatados em outros editores de texto. Se forem gerados em algum editor de textos como Word, por exemplo, devem ser gravados no formato texto sem formatação, gerando um arquivo de extensão txt. 1. Abra o arquivo Base.txt clicando duas vezes sobre o seu nome.

Observe que nesse arquivo existem informações sobre o código do funcionário, nome, cargo, cidade, loja, meta mensal, data de aniversário e data de admissão. Sua tarefa consiste em importar essas informações para dentro do Excel, gerando uma planilha. 2. Feche o arquivo Base.txt. 3. Abra uma nova pasta de trabalho em branco no Excel. 4. Na guia Dados, grupo Obter Dados Externos, clique em De Texto.

Senac São Paulo

Excel 2010 – Avançado

5. Localize o arquivo Base.txt e clique em Importar.

6. Será então iniciado o processo de importação do arquivo através do assistente, e a tela referente será exibida.

Senac São Paulo

Excel 2010 – Avançado

Nessa janela você tem as seguintes informações: •

Delimitado: as informações estão separadas por algum delimitador, como vírgula, ponto e vírgula ou tabulação. No nosso caso, o assistente identificou que os dados estão delimitados.

•

Largura fixa: especifica as colunas com largura fixa.

•

Iniciar importação na linha: permite escolher a partir de que linha a importação será efetuada. O padrão é linha 1.

•

Origem do Arquivo: permite escolher o sistema operacional e o idioma. É recomendável deixar no padrão que foi colocado.

7. Clique em Avançar, e a seguinte tela é mostrada:

•

Delimitadores: especifica qual é o delimitador utilizado para separar as informações. No seu caso, o

delimitador utilizado é Tabulação. Se você quiser ter a certeza desse fato, abra o arquivo no Word e ative a ferramenta Mostrar tudo e você poderá visualizar o caractere → separando as informações.

•

Considerar delimitadores consecutivos como um só: essa opção é bastante útil quando existirem dois delimitadores em seguida. Se não for selecionada, o fato de existirem dois delimitadores em seguida irá gerar uma coluna em branco na planilha. Se estiver ativada, os dois delimitadores serão considerados como um só evitando as colunas em branco.

8. Clique em Avançar. A janela mostrada permite selecionar cada coluna e definir o formato dos dados.

Senac São Paulo

Excel 2010 – Avançado

Caso não queira escolher os formatos das colunas, os dados serão importados no formato Geral, que converte valor numérico em número, valor de data em data e o restante em texto.

Cuidado na utilização de datas. Seu arquivo pode ter sido gerado por algum programa que utilize outro sistema para sua representação, como por exemplo, algum programa norte americano, onde a data é expressa como mês/dia/ano. Nesse caso a conversão desses valores não será para um valor de data. 9. Clique em Concluir. 10. Na janela mostrada, selecione a célula A1 da planilha para que seja o local inicial da importação.

11. Clique em OK para terminar.

Senac São Paulo

Excel 2010 – Avançado

12. A planilha gerada pode ser atualizada pela modificação do conteúdo do arquivo que deu origem a ela, no caso, o arquivo Base.txt. Abra o arquivo Base.txt no bloco de notas e faça as seguintes modificações: NOME ORIGINAL

NOME MODIFICADO

Douglas Francisco

Douglas Piccoli

Fernando Abreu

Fernando José Abreu

Thais Flores

Thatiana Flores

Camila Piccoli

Camila Flores Piccoli

13. Feche o arquivo Base.txt salvando as alterações. 14. Volte à pasta do Excel. Observe que as alterações efetuadas no arquivo texto ainda não se refletem na planilha. Na guia Dados, grupo Conexões, clique em Atualizar tudo.

15. Na janela que se abre selecione o arquivo Base.txt e clique em Importar.

Senac São Paulo

Excel 2010 – Avançado

16. Note que os registros foram atualizados. Feche a pasta e salve-a como Base de Dados.xlsx. 17. Abra novamente a pasta Base de Dados.xlsx. 18. Observe o aviso de segurança que aparece na parte superior da planilha.

Essa mensagem informa que existem conexões externas com outros arquivos que podem não ser seguras e foram desabilitadas. Quando isso ocorrer, se você souber a origem das conexões e considerá-las seguras, clique em Habilitar Conteúdo. Somente habilitando o conteúdo o vínculo ficará disponível para atualizações. 19. Feche a pasta.

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Importando arquivos de dados para o Excel Objetivos :

• Importar uma tabela do Access para o Excel.

Tarefas

•Importar a tabela FUNCIONARIOS do banco de dados Grupo Empresarial.accdb para uma planilha do Excel.

IMPORTANDO UM ARQUIVO DE DADOS Você pode importar arquivos de dados para o Excel com muita facilidade. Nesta atividade você vai importar uma tabela de um banco de dados do Access para uma pasta de trabalho do Excel. O procedimento a ser realizado é bastante semelhante para outros tipos de bancos de dados. 1. Abra uma nova pasta de trabalho. 2. Na guia Dados, grupo Obter Dados Externos, selecione a opção Do Access. 3. Você vai importar uma tabela do banco de dados Grupo Empresarial.accdb. Selecione o arquivo e clique em Abrir.

4. O banco de dados possui duas tabelas. Selecione a tabela FUNCIONARIOS e clique em OK.

Senac São Paulo

Excel 2010 – Avançado

5. Na janela Importar Dados selecione as seguintes opções e clique em OK:

Você terá o seguinte resultado:

6. Feche a pasta de trabalho salvando-a com o nome Grupo Empresarial.xlsx.

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Importando base de dados como consulta para o Excel Objetivos :

• Importar uma tabela do Access para o Excel como uma consulta.

Tarefas

• Importar a tabela PRODUTOS do banco de dados Grupo Empresarial.accdb como uma consulta para uma planilha do Excel.

Importando uma Tabela como Consulta Você pode importar bases de dados para o Excel como consultas, permitindo a escolha dos dados e campos que serão importados. 1. Abra uma nova pasta de trabalho em branco. 2. Na guia Dados, grupo Obter Dados Externos, clique em De Outras Fontes. No menu apresentado, selecione Do Microsoft Query.

3. Na janela apresentada escolha MS Access Database e clique em OK.

Senac São Paulo

Excel 2010 – Avançado

4. Escolha o banco de dados Grupo Empresarial.accdb e clique em OK.

5. Selecione a tabela PRODUTOS e clique no sinal + para abrir os seus campos. Selecione os campos Nome do produto, Fabricante, Fornecedor e Valor Unitário, passando-os para o painel Colunas em sua consulta situado à direita. Clique em Avançar.

6. Você vai solicitar que a importação seja feita somente para o fornecedor Doces do Brasil S/A. Para isso clique sobre o campo Fornecedor e preencha o quadro Só incluir linhas onde da forma mostrada:

100

Senac São Paulo

Excel 2010 – Avançado

7. Clique em Avançar. Agora você vai classificar a consulta por Nome do produto.

8. Clique em Avançar. Deixe selecionada a opção Retornar dados ao Microsoft Excel e clique em Concluir.

Senac São Paulo

101

Excel 2010 – Avançado

9. Selecione a célula A1 e clique em OK.

Você terá o seguinte resultado:

10. Feche a pasta salvando-a com o nome Produtos Doces do Brasil.xlsx.

102

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Vinculando dados do Excel no Access Objetivos :

• Vincular os dados da planilha Dados da pasta Access.xlsx no Access. .

Tarefas

• Abrir o banco de dados BICHOS.ACCDB.

• Importar a planilha Dados da pasta Access.xlsx vinculando os dados.

Vinculando Dados do Excel no Access Você pode importar para dentro do Access planilhas do Excel. A tabela gerada no Access ficará vinculada à planilha do Excel. 1. Abra o banco de dados BICHOS.ACCDB. 2. Clique no botão Habilitar Conteúdo. 3. Na guia Dados Externos, grupo Importar e Vincular, selecione Excel.

4. Na janela que se abre procure pelo arquivo Access.xlsx e marque a opção Vincular à fonte de dados criando uma tabela vinculada. Clique em OK.

Senac São Paulo

103

Excel 2010 – Avançado

5. Deixe selecionada a planilha Dados e clique em Avançar.

6. Selecione A primeira linha contém títulos de coluna e clique em Avançar.

7. Informe como nome da tabela Dados e clique em Concluir.

104

Senac São Paulo

Excel 2010 – Avançado

8. Agora você tem uma tabela no banco de dados denominada Dados. Clique duas vezes sobre o nome da tabela no painel à esquerda e você terá o seguinte resultado:

Os dados só poderão ser atualizados pelo Excel, não sendo possível modificá-los pelo Access. Se for efetuada qualquer modificação na planilha do Excel, essa modificação se refletirá na tabela do Access, uma vez que os objetos estão vinculados.

Senac São Paulo

105

Excel 2010 – Avançado

Exercício Proposto 1. Importar o arquivo CLIENTES.TXT para uma planilha do Excel. Salve a pasta como Clientes.xlsx. 2. Importar a tabela ANIMAIS E SEUS DONOS do banco de dados BICHOS.ACCDB. Salvar a pasta como Animais e seus donos.xlsx. Importar os dados dos campos AnCodigo, AnNome, AnTipo e AnRaca da tabela ANIMAIS do banco de dados BICHOS.ACCDB como uma consulta somente para os animais de raça Golden Retriever. Salve a pasta como Golden Retriever.xlsx.

106

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Selecionar dados aplicando AutoFiltro.  Classificar dados por uma ou mais chaves.  Aplicar o Filtro Avançado.

Senac São Paulo

107

Excel 2010 – Avançado

108

Senac São Paulo

Excel 2010 – Avançado

Filtrando e classificando no Excel

Neste capítulo você destacará dados de interesse através da aplicação do recurso filtro e classificará o conteúdo das planilhas por uma chave e por mais de uma chave. Você criará planilhas que apresentarão resultados desejados. Com base em uma lista de produtos você criará planilhas que apresentarão os produtos separados por departamento.

Atividade 1 – Aplicar o AutoFiltro Objetivo :

• Conhecer o recurso AutoFiltro

Tarefas

• Aplicar o AutoFiltro no arquivo Filtro Avançado.xlsx

Nesta atividade você utilizará o arquivo Filtro Avançado.xlsx e aplicará o AutoFiltro para destacar um grupo de registros que correspondem aos produtos de determinado Departamento. Abra o arquivo Automação.xlsx e acesse a guia Pesquisa. 1. Abra o arquivo Filtro Avançado.xlsx. 2. Exiba o AutoFiltro. Na guia Dados, no grupo Classificar e Filtrar clique no comando Filtro.

Botões para aplicação do filtro aparecem ao lado do rótulo de cada coluna. 3. Clique no botão da coluna Departamento e selecione a opção Selecionar Tudo para desmarcar todas as opções. 4. Em seguida, marque apenas a opção Telefones e Celulares.

Senac São Paulo

109

Excel 2010 – Avançado

5. Clique no botão OK. 6. O resultado é a exibição das linhas onde constam o departamento escolhido. As linhas que correspondem a outros departamentos ficam ocultas.

A coluna onde o filtro foi aplicado exibe uma imagem representando a aplicação do filtro.

7. Para mostrar todas as linhas novamente clique no botão ao lado do nome do campo Departamento, escolha Selecionar Tudo e clique em OK.

Podemos reexibir todas as linhas clicando na opção Limpar do grupo Classificar e Filtrar da guia Dados.

110

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Aplicar o filtro a mais de um campo Objetivo :

• Apresentar dados que atendam a critérios combinados.

Tarefa

• Aplicar um filtro que exiba os produtos do departamento Telefones e Celulares cujo preço seja inferior a R$ 600,00.

1. Aplique o AutoFiltro que apresenta os produtos do departamento Telefones e Celulares. 2. Clique no botão da coluna Preço. 3. Escolha a opção Filtros de número e escolha a opção É Menor do Que...

4. Digite o limite superior - 600. 5. Clique em OK.

Clique em Limpar do grupo Classificar e Filtrar quando desejar exibir todas as linhas.

Senac São Paulo

111

Excel 2010 – Avançado

ANOTAÇÕES

112

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Aplicar filtro alternativo Objetivo :

• Aplicar filtro quando parte da informação é conhecida.

Tarefa

• Aplicar filtro para produtos iniciados pela letra N e filtro para produtos que contenham como parte de sua descrição a informação - 14”.

1. Clique no botão de filtro do campo Produto. 2. Escolha em Filtros de Texto e a opção Começa com...

3. Preencha com a parte conhecida da informação. Pressione OK.

4. Clique em Limpar para restaurar todos os produtos. 5. Clique no botão de filtro da coluna Descrição.

Senac São Paulo

113

Excel 2010 – Avançado

6. Escolha Filtros de Texto e opção Contém...

As opções que aparecem na lista suspensa quando pressionamos o botão do filtro dependem do tipo de informação que predomina na coluna. Os tipos podem ser Filtros de Texto, Filtros de Número e Filtros de Data.

114

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Classificar com o AutoFiltro Objetivo :

• Apresentar o AutoFiltro como alternativa de classificação.

Tarefa

• Classificar por ordem alfabética de produtos.

1. Clique em Limpar para retirar os filtros aplicados. 2. Clique no botão de filtro da coluna Produto. 3. Escolha a opção Classificar de A a Z para colocar os dados da coluna em ordem crescente ou Classificar de Z a A para colocar os dados em ordem decrescente.

Quando a coluna estiver preenchida com números as opções serão Classificar do Menor para o Maior, Classificar do Maior para o Menor. Quando a coluna estiver preenchida com datas, as opções serão Classificar do Mais Antigo para o Mais Novo e Classificar do Mais Novo para o Mais Antigo. Clique no botão Filtro do grupo Classificar e Filtrar quando quiser retirar os botões do AutoFiltro.

Senac São Paulo

115

Excel 2010 – Avançado

ANOTAÇÕES

116

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Classificação Avançada Objetivo :

• Apresentar o recurso de classificação por mais de uma chave.

Tarefa

• Classificar os funcionários da planilha Nome.xlsx por Estado Civil e Nome.

1. Abra o arquivo Nome.xlsx. 2. Na planilha Nome, selecione qualquer célula dentro da lista. 3. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados.

4. Escolha na caixa de diálogo a Coluna Estado Civil.

5. Clique em Adicionar Nível para definir uma segunda chave de classificação.

6. Escolha a coluna Nomes na linha que surgiu e clique em OK.

Senac São Paulo

117

Excel 2010 – Avançado

ANOTAÇÕES

118

Senac São Paulo

Excel 2010 – Avançado

Atividade 6 – Classificação por lista personalizada Objetivo :

• Classificar os dados por uma ordem estabelecida pelo usuário.

Tarefa

• Classificar os funcionários por Cargo em ordem hierárquica.

Você criará uma lista personalizada para poder classificar os cargos em uma ordem de importância arbitrária. Para isso você digitará os cargos na lista personalizada na seguinte ordem: Presidente, Diretor(a), Gerente, Subgerente, Analista, Contador(a), Secretária, Escriturário, Comprador, Vendedor, Conferente e Estagiário. 1. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 2. Escolha a coluna Cargo e escolha Lista Personalizada na caixa de combinação Ordem.

3. Digite os cargos na ordem especificada pressionando ENTER após cada cargo ou separando por vírgulas.

4. Clique em Adicionar e a lista será criada.

Senac São Paulo

119

Excel 2010 – Avançado

5. Clique em OK e OK novamente para classificar pela lista personalizada.

120

Senac São Paulo

Excel 2010 – Avançado

Atividade 7 – Classificação por cores Objetivo :

• Utilizar cores como critério de classificação.

Tarefa

• Classificar os produtos mais vendidos através de codificação por cores.

Você vai classificar o arquivo Produtos Mais Vendidos.xlsx onde a cor verde representa os produtos mais vendidos, a cor vermelha os produtos de venda mediana e a cor azul os produtos com pouca venda. 1. Abra o arquivo Produtos mais Vendidos.xlsx. 2. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 3. Escolha a coluna Produto e Cor da Célula na caixa de combinação Classificar em.

Existem as opções Cor da Fonte e Ícone da Célula, neste último caso quando for aplicada a Formatação Condicional. 4. Estabeleça a ordem de importância das cores, adicionando nível a cada cor definida.

5. Clique em OK para efetuar a classificação.

Senac São Paulo

121

Excel 2010 – Avançado

ANOTAÇÕES

122

Senac São Paulo

Excel 2010 – Avançado

Atividade 8 – Filtro avançado Objetivo :

• Aplicar filtro utilizando critérios avançados.

Tarefas

• Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha para cada departamento.

1. Abra o arquivo Filtro Avançado.xlsx. Você vai criar uma lista dos departamentos sem repetição. 2. Selecione a coluna Departamento. 3. Clique com o botão direito e a opção Copiar ou use qualquer outro método de cópia.

4. Selecione a planilha Critérios e a célula A1. 5. Cole o conteúdo da área de transferência. 6. Mantendo a coluna selecionada, clique no botão Remover Duplicata do grupo Ferramenta de Dados, da guia Dados.

Senac São Paulo

123

Excel 2010 – Avançado

7. Certifique-se que a caixa de verificação Meus dados contêm cabeçalhos esteja marcada. Caso não esteja marque-a. clique em OK.

8. Clique em OK para concluir.

Você vai criar as áreas de critérios necessárias para o uso do filtro avançado. Um critério é constituído de uma célula com o nome da coluna que contém o dado a ser pesquisado tendo logo abaixo a célula que contém o dado pesquisado. Ela é constituída, portanto, de um intervalo de pelo menos duas células. 9. Construa os critérios como abaixo:

10. Insira e renomeie uma planilha para os departamentos de Informática, Telefones e Celulares, Câmeras e Filmadoras, Eletrônicos e uma planilha para os produtos dos outros departamentos.

124

Senac São Paulo

Excel 2010 – Avançado

11. Você vai iniciar agora a construção das planilhas utilizando o Filtro Avançado. Selecione todas as linhas da planilha Geral e renomeie este intervalo como Geral. 12. Vá para a planilha Informática. 13. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.

14. No quadro que surge marque a opção Copiar para outro local. 15. Preencha o Intervalo da lista com Geral. 16. Preencha o intervalo de critérios clicando na aba da planilha Critérios e selecionando as células A1 e A2. 17. Vá para Copiar para e clique na célula A1 da planilha Informática.

18. Clique em OK.

19. Repita os passos 12 a 17 iniciando o processo nas planilhas Telefones e celulares, Câmeras e Filmadoras, Eletrônicos utilizando os intervalos de critérios correspondentes. 20. Vá para a planilha OUTROS.

Senac São Paulo

125

Excel 2010 – Avançado

21. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 22. Marque a opção Copiar para outro local. 23. Preencha Intervalo da lista com Geral. 24. Preencha Intervalo de critérios selecionando o intervalo A14:A18 da planilha Critérios.

Quando colocamos o cabeçalho do campo e várias linhas de dados a pesquisar, o filtro avançado considera que um ou outro nome relacionado atenderá o critério. 25. Vá para Copiar para e clique na célula A1 da planilha OUTROS.

26. Clique em OK.

126

Senac São Paulo

Excel 2010 – Avançado

Atividade 9 – Filtro avançado com critérios múltiplos Objetivo :

• Aplicar filtro utilizando critérios avançados combinados.

Tarefas

• Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha com os produtos do departamento Informática com preço inferior a R$ 1.200,00.

1. Vá à planilha Critérios, digite Preço na célula B1 e <1200 na célula B2.

O intervalo A1:B2 da planilha Critérios formará o critério combinado a ser usado no filtro. Este critério fará com que o filtro avançado retorne os produtos de informática e com preço inferior a 1200.

Só os produtos que atenderem a estas duas condições aparecerão no resultado. 2. Insira uma nova planilha. 3. Renomeie como Informática - menor que 1200. 4. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 5. Marque a opção Copiar para outro local. 6. Preencha Intervalo da lista com Geral. 7. Preencha Intervalo de critérios selecionando o intervalo A1:B2 da planilha Critérios.

8. Clique em OK.

Senac São Paulo

127

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta de trabalho Nome-exercício.xlsx e crie uma nova planilha separando as informações por cidade usando o filtro avançado. 2. Ainda na mesma pasta e usando o filtro avançado, crie uma planilha com os dados das pessoas casadas e outra com os dados das pessoas solteiras.

128

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Apresentar recursos para exibição e interpretação de dados.

Senac São Paulo

129

Excel 2010 – Avançado

130

Senac São Paulo

Excel 2010 – Avançado

Atividade 1 – Criação de Minigráficos Objetivo :

• Apresentar dados através de minigráficos.

Tarefa

• Exibir dados da evolução das vendas de cada vendedor.

Você criará gráficos que demonstrarão a evolução das vendas de cada vendedor na coluna Evolução. 1. Abra o arquivo Vendas.xlsx. 2. No grupo Minigráficos da guia Inserir escolha o tipo de minigráfico que deseja para representar os dados. Use Linha.

3. Preencha Intervalo de dados com os dados das vendas mensais do primeiro vendedor D2:I2. 4. Preencha Intervalo de Locais com J2, onde será criado o minigráfico.

5. Clique em OK. 6. Copie o minigráfico gerado para as linhas de baixo como você faz com fórmulas

Senac São Paulo

131

Excel 2010 – Avançado

ANOTAÇÕES

132

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Alteração e formatação de Minigráficos Objetivo :

• Apresentar os recursos para formatação e alteração dos minigráficos.

Tarefas

• Configurar a exibição de valores nulos.

• Editar dados. • Alterar o tipo e o estilo. Ao se construir o minigráfico uma nova guia Ferramentas de Minigráfico é apresentada.

Para verificar como o minigráfico apresenta valores nulos ou células contendo zero, coloque zero em alguns valores da planilha. 1. Digite zero na célula G6. 2. Apague o conteúdo da célula E3 e I4. 3. Assinale Marcadores no grupo Mostrar da guia Design para destacar os pontos de dados. 4. Clique na parte inferior do botão Editar Dados do grupo Minigráfico da guia Design. 5. Clique na opção Células Ocultas e Vazias... 6. Marque a opção Zero no quadro que aparece para alterar a representação das células vazias no minigráfico.

As células vazias aparecem como Lacunas por padrão. Ao alterarmos a opção para Zero a linha do gráfico muda sua representação. Se escolhermos a opção Conectar pontos de dados com a linha, a linha ligará o ponto anterior diretamente com o ponto posterior. 7. Clique em OK para executar a alteração. No grupo Tipo da guia Design você pode escolher entre gráfico de Linhas, Barras ou Ganha/Perde, que apresentam os seguintes estilos, respectivamente:

Senac São Paulo

133

Excel 2010 – Avançado

8. Defina a cor do minigráfico e as características dos marcadores no grupo Estilo da guia Design.

Os Minigráficos são tratados pelo Excel como células agrupadas e, portanto, a alteração da formatação e escolha de estilos é geral. Para formatarmos um minigráfico isoladamente devemos desagrupar. 9.

Selecione uma célula que contenha o Minigráfico, clique no botão Desagrupar do grupo Agrupar da guia Design.

10. Selecione o minigráfico a ser modificado e aplique outra cor no minigráfico e outros formatos desejados.

134

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Capturar telas Objetivo :

• Capturar tela utilizando o recurso Instantâneo.

Tarefa

• Capturar parte do texto do arquivo Programa de Metas.docx

1. Abra o arquivo Programa de Metas.docx. 2. Abra o arquivo Vendas.xlsx se ele ainda não estiver aberto. 3. Clique no botão Instantâneo do grupo Ilustrações da guia Inserir.

4. Clique na opção Recorte de Tela. 5. Com a cruz que aparece selecione o trecho a copiar.

Para copiar a tela inteira clique na miniatura listada em Janelas Disponíveis. Para copiar parte da tela use a opção Recorte de Tela. Esta opção atuará na tela mais recentemente aberta anterior ao arquivo que recebe a cópia. 6. Após o aparecimento da imagem no arquivo de destino, utilize os recursos de tratamento de imagem normais usados no Excel.

Senac São Paulo

135

Excel 2010 – Avançado

Exercício Proposto Abra a pasta Bebidas.xlsx e crie minigráficos para representar a evolução do consumo das marcas de cervejas. Formate os minigráficos para melhorar sua apresentação.

136

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Resumir dados de planilhas utilizando a Tabela Dinâmica.

Senac São Paulo

137

Excel 2010 – Avançado

138

Senac São Paulo

Excel 2010 – Avançado

Atividade 1 – Criação da Tabela Dinâmica Objetivo :

• Resumir dados usando a Tabela Dinâmica.

Tarefas

• Construir Tabela Dinâmica que apresente dados agrupados por projeto.

Utilizando os dados do arquivo Movimento Financeiro.xlsx você criará uma apresentação da soma dos valores agrupados por projeto. 1. Abra o arquivo Movimento Financeiro.xlsx. 2. Clique na metade superior do botão Tabela Dinâmica do grupo Tabelas da guia Inserir.

Se você clicar na metade inferior do botão, as sub-opções Tabela Dinâmica e Gráfico Dinâmico aparecem. Escolha, então, Tabela Dinâmica.

3. Para preencher o campo Tabela/Intervalo do quadro Criar Tabela Dinâmica, selecione o intervalo A3:G56 da planilha Dados. 4. Selecione Nova Planilha para criar a Tabela Dinâmica em uma planilha independente.

5. Clique em OK. Surge uma nova guia Ferramentas de tabela Dinâmica com duas subguias: Opções e Design.

Senac São Paulo

139

Excel 2010 – Avançado

À direita surge o Painel de Tarefas Lista de Campos da Tabela Dinâmica

. Na lista de campos você escolherá os campos (colunas) que utilizará no relatório de tabela dinâmica. •

Filtro de Relatório – permite que você escolha um ou mais campos para filtrar dados no seu relatório.

•

Rótulos de Linha – são os campos onde itens iguais são agrupados.

•

Rótulos de Coluna – são os campos que serão indicados quando necessitarmos do cruzamento de duas informações.

•

Valores – corresponde a uma ou mais colunas que terão suas informações consolidadas.

6. Marque o campo Projeto e o campo Valor. Será construído o relatório agrupando os valores por Projeto.

140

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Atualização e Inclusão de Dados Objetivo :

• Modificar os dados existentes e acrescentar novos dados tornando-os visíveis na Tabela Dinâmica.

Tarefas

• Alterar dados já existentes na guia Dados e atualizar a Tabela Dinâmica.

• Inserir uma nova linha entre linhas já existentes e nova linha após o último projeto e torná-los visíveis na Tabela Dinâmica. 1. Torne a célula A13 como célula ativa na planilha Dados. 2. Insira uma nova linha. 3. Acrescente os dados: •

Data: 23/10/2010

•

Projeto: Hidroelétrica Torrente

•

Item: Venda de Ações

•

Categoria: Dinheiro dos Sócios

•

E/S: Entrada

•

Valor: 10.000.000,00

•

Estado: Paraná.

4. Volte à Tabela Dinâmica. Este novo projeto não foi incluído. 5. Clique no botão Atualizar do grupo Dados da guia Opções e o novo projeto será relacionado no final.

6. Clique no botão classificar para ordenar os projetos.

7. Inclua mais uma entrada na última linha vazia da planilha Dados. Digite: •

Data: 20/06/2011

•

Projeto: Aeroporto Perus

•

Item: Duplicatas – Desconto

•

Categoria: Contas a Receber

•

E/S: Entrada

Senac São Paulo

141

Excel 2010 – Avançado

•

Valor: 500.000,00

•

Estado: Goiás.

A linha acrescentada está fora do intervalo de dados reconhecido pela Tabela Dinâmica. Portanto, o recurso Atualizar não terá efeito neste caso. É necessário redefinir o intervalo. 8. Volte à Tabela Dinâmica. 9. Clique em Alterar Fonte de Dados do grupo Dados da guia Opções.

10. Digite ou redefina com o mouse o novo intervalo que inclua a linha acrescentada. Após a definição do novo intervalo um novo quadro surge.

11. Clique em OK.

142

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Criação de outros níveis Objetivo :

• Acrescentar subníveis à Tabela Dinâmica.

Tarefa

• Subdividir os valores de cada projeto por Entrada e Saída. Cada um desses níveis será, também subdividido por Categoria.

1. Marque E/S e Categoria no painel da lista de campos da tabela dinâmica e os dados são apresentados com as subdivisões desejadas.

2. Selecione a célula de qualquer subtotal e clique em Configurações do Campo do grupo Campo Ativo da guia Opções. 3. Marque a opção Nenhum para ocultar os subtotais.

Senac São Paulo

143

Excel 2010 – Avançado

4. Clique em OK. 5. Na coluna Soma de Valor, dê um duplo clique na linha correspondente a um subtotal. Será incluída uma nova planilha na pasta de trabalho contendo os itens que formam o referido subtotal.

Quando criamos uma Tabela Dinâmica com a opção Nova Planilha, uma nova planilha é acrescentada. É aconselhável renomearmos as planilhas que vão sendo incluídas com nomes que indiquem o conteúdo da planilha. Para alterar o layout de forma a cruzar as informações você pode arrastar os campos E/S para o diagrama na posição que representa os Rótulos das Colunas. 6. Na parte inferior do painel de tarefas, arraste os campos E/S da área Rótulos de Linha para a área Rótulos de Colunas.

144

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Formatar a Tabela Dinâmica Objetivo :

• Alterar a aparência da tabela dinâmica para facilitar a visualização.

Tarefa

• Aplicar formato monetário à Tabela Dinâmica.

• Aplicar Bordas à Tabela Dinâmica e alterar as funções agregadas utilizadas. 1. Escolha e selecione o Estilo desejado na guia Design, no grupo Estilos de Tabela Dinâmica.

2. Na guia Opções, no grupo Campo Ativo, clique em Configurações do Campo.

No quadro que surge, analise as várias opções de funções que estão disponíveis para resumir o campo. Além de somar, você pode contar, calcular a média aritmética, obter o valor máximo ou mínimo, etc. 3. Clique em Formato do Número e escolha Moeda.

Senac São Paulo

145

Excel 2010 – Avançado

4. Clique em OK. Este será o resultado.

146

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Agrupar Itens Objetivo :

• Formar grupos de itens.

Tarefa

• Agrupar os projetos pelas regiões.

1. Crie uma nova Tabela Dinâmica. Volte à planilha Dados, clique em Tabela Dinâmica do grupo Tabelas da guia Inserir. 2. Selecione os campos Estado, Projeto e Valor.

Os Estados correspondem às regiões Sul (Santa Catarina) e Sudeste (São Paulo, Rio de Janeiro e Minas Gerais). 3. Selecione o grupo Minas Gerais, mantenha a tecla CTRL pressionada e selecione os demais Estados da região Sudeste (Rio de Janeiro e São Paulo). 4. Clique em Agrupar Seleção do grupo Agrupar da guia Opções.

Senac São Paulo

147

Excel 2010 – Avançado

5. Digite o nome da região, Sudeste, sobre Agrupar1. 6. Selecione Paraná e Santa Catarina. 7. Clique em Agrupar Seleção do grupo Agrupar da guia Opções. 8. Digite Sul onde aparece Agrupar2. 9. Digite Centro-Oeste no grupo Goiás.

Clique em Desagrupar para desfazer os grupos.

148

Senac São Paulo

Excel 2010 – Avançado

Atividade 6 – Aplicar Filtro de Relatório Objetivo :

• Selecionar os dados a serem visualizados.

Tarefa

• Filtrar para visualizar os projetos de um só Estado.

1. Crie outra Tabela Dinâmica em uma nova planilha. 2. Escolha os campos Projeto, Estado e Valor. 3. Arraste o campo Estado para a área de Filtro de Relatório.

4. Nas células A1 e B1 surge o Filtro de Relatório. 5. Clique na caixa de combinação da célula B1.

6. Clique no Estado que quer exibir. Os projetos e respectivos valores dos Estados não selecionados ficarão ocultos.

Senac São Paulo

149

Excel 2010 – Avançado

Marque a caixa de verificação Selecionar Vários Itens para exibir mais de um item por vez.

150

Senac São Paulo

Excel 2010 – Avançado

Atividade 7 – Agrupar campos por períodos Objetivo :

• Agrupar informações por períodos de tempo com base num campo de Data.

Tarefa

• Criar Tabela Dinâmica que relaciona os dados dos projetos agrupados por categoria de itens e por período de tempo.

1. Crie uma nova Tabela Dinâmica em uma nova planilha. 2. Escolha os campos E/S, Categoria, Valor e Data.

3. Arraste o campo Data para a área Rótulos de Colunas.

Surge uma coluna para cada data relacionada no campo Data. Você vai agrupar estas datas em períodos que tenham significado para quem analisa. 4. Selecione a primeira data. Clique em Agrupar Campo do grupo Agrupar da guia Opções.

Senac São Paulo

151

Excel 2010 – Avançado

5. Clique em Anos. O item Meses já estava marcado e você pode acrescentar vários níveis de agrupamento de datas. Cada grupo em destaque formará um grupo. Caso deseje desmarcá-lo clique novamente sobre o período. 6. Clique em OK.

152

Senac São Paulo

Excel 2010 – Avançado

Atividade 8 – Segmentação de Dados Objetivo :

• Aplicar filtros aprimorados em tabelas dinâmicas.

Tarefa

• Utilizar o recurso de segmentação de dados para filtrar dados por Projeto e por Estado.

1. Ainda na tabela dinâmica da atividade anterior, clique no botão Segmentação de Dados, do grupo Filtro da guia Inserir.

2. Marque os campos Projeto e Estado e clique em OK. 3. Posicione as Segmentações de Dados. Todos os itens aparecem com cor destacada indicando que nenhum filtro foi aplicado ainda. 4. Clique no Estado do Rio de Janeiro. Somente este Estado fica destacado. No outro quadro aparecem destacados os projetos deste Estado. Os dados na tabela dinâmica correspondem somente aos dados dos projetos do Rio de Janeiro.

Você pode fazer a seleção de vários Estados mantendo a tecla CTRL pressionada enquanto clique com o mouse nos Estados escolhidos. 5. Clique em um destes projetos. O resultado é a apresentação dos valores deste projeto. Neste caso, usamos um filtro combinando os dois campos, Estado e Projeto.

Senac São Paulo

153

Excel 2010 – Avançado

Para desaplicar o(s) filtro(s), clique no botão Limpar Filtro de cada Segmentação de Dados.

154

Senac São Paulo

Excel 2010 – Avançado

Atividade 9 – Gráfico Dinâmico Objetivo :

• Demonstrar a construção e o uso do Gráfico Dinâmico.

Tarefa

• Construir gráfico dinâmico referente à Tabela Dinâmica.

1. Em uma célula da Tabela Dinâmica, clique no botão Gráfico Dinâmico do grupo Ferramentas da guia Opções.

Você também pode criar o gráfico com a tecla de função F11. 2. Escolha o tipo e clique em OK.

Use os recursos de gráfico do Excel. A única diferença entre um gráfico normal do Excel e do gráfico dinâmico é que este possui caixas de combinação para aplicar filtro. 3. Clique no botão Alternar Linha/Coluna para que o eixo X apresente os meses e os anos.

Senac São Paulo

155

Excel 2010 – Avançado

4. Use as caixas de combinação para filtrar pelo campo desejado.

156

Senac São Paulo

Excel 2010 – Avançado

Atividade 10 – Mostrar Páginas de Relatório Objetivo :

• Utilizar recurso para criar uma cópia da Tabela Dinâmica para item do Filtro de Relatório.

Tarefa

• Criar Páginas de Relatório para a Tabela Dinâmica dos projetos.

1. Acrescente o campo Estado na área Filtro de Relatório.

2. Clique no botão Tabela Dinâmica, na opção Opções e, em seguida, em Mostrar Páginas do Filtro de Relatório.

Senac São Paulo

157

Excel 2010 – Avançado

3. Escolha Estado e clique em OK.

Será criada uma cópia da Tabela Dinâmica para cada Estado constante na planilha Dados.

Exercício Proposto 1. Abra o arquivo Movimento-ex-din.xlsx e construa uma tabela dinâmica que mostre os valores das Entradas e Saídas por Estado. 2. Use os recursos para formatar a tabela dinâmica. 3. Crie um gráfico dinâmico baseada nesta tabela.

158

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Apresentar recursos para consolidação e resumo de dados.

Senac São Paulo

159

Excel 2010 – Avançado

160

Senac São Paulo

Excel 2010 – Avançado

Atividade 1 – Consolidação de Dados Objetivo :

• Apresentar o recurso Consolidar Dados.

Tarefa

• Reunir informações de várias planilhas e arquivos.

Nesta atividade você somará os valores previstos de entrada e saída dos meses de janeiro, fevereiro e março contidos nas planilhas do arquivo Movimento.xlsx e valores do mês de abril que se encontram no arquivo Movimento-abril.xlsx. Será utilizado o recurso Consolidar. Este recurso consolida os dados de duas formas: consolidação por posição e consolidação por categoria. Na consolidação por posição presume-se que o dado mantém sua posição relativa, como no exemplo abaixo:

As informações de Janeiro do Item 1 ficam sempre no canto superior esquerdo, de Fevereiro do Item 1 à direita e assim por diante. Nesta situação os cabeçalhos de coluna e de linha não são importantes para a consolidação, bastando apenas selecionar o intervalo de dados.

Porém as tabelas podem não manter a posição dos dados, como no exemplo abaixo:

Neste caso a consolidação deve considerar os cabeçalhos das colunas e das linhas como referencial para consolidar as informações. Deve-se incluir os cabeçalhos no intervalo a consolidar.

Senac São Paulo

161

Excel 2010 – Avançado

1. Abra o arquivo Movimento.xlsx. Analise as informações das planilhas Janeiro, Fevereiro e Março. 2. Posicione-se na célula A3 da planilha Consolidado. 3. Clique na opção Consolidar do grupo Ferramentas de dados da guia Dados.

4. Escolha a função desejada para a consolidação das planilhas. Nesse caso indique Soma.

5. Vá para o campo Referência, clique na guia da planilha Janeiro e selecione o intervalo de dados a ser considerado para consolidar.

162

Senac São Paulo

Excel 2010 – Avançado

6. Clique em Adicionar. 7. Repita os passos 5 e 6 para os meses Fevereiro e Março.

Os dados do mês de abril estão em um arquivo separado que não está na memória. 8. Clique no botão Procurar... 9. Localize o arquivo Movimento-Abril.xlsx. 10. Depois de selecioná-lo no quadro Procurar, clique em OK. 11. Clique na tecla de função F2. 12. Clique em qualquer local dentro da caixa de referência para desfazer a seleção. 13. Tecle End e digite o nome do intervalo que contém os dados: Abril.

14. Clique em Adicionar.

Sempre use a tecla F2 caso você não tenha acesso com o cursor para digitar o intervalo. Se tentar usar outro método, é possível que você perca a indicação do caminho e do nome do arquivo.

Senac São Paulo

163

Excel 2010 – Avançado

15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna esquerda em Usar rótulos na. 16. Clique em OK.

164

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Consolidação de Dados com Vínculo Objetivo :

• Consolidar dados de várias planilhas mantendo o vínculo do total com as planilhas de origem.

Tarefa

• Consolidar os dados das planilhas dos arquivos Movimento e Movimento-Abril, para que qualquer alteração nos dados por ser vista na planilha de totais.

1. Vá para a planilha Vinculado. 2. Posicione-se na célula A3 da planilha Vinculado. 3. Clique na opção Consolidar do grupo Ferramentas de dados da guia Dados.

4. Escolha a função Soma para a consolidação das planilhas. 5. Vá para o campo Referência, clique na guia da planilha Janeiro e selecione o intervalo de dados a ser considerado para consolidar.

6. Clique em Adicionar. 7. Repita os passos 5 e 6 para os meses Fevereiro e Março.

Senac São Paulo

165

Excel 2010 – Avançado

8. Clique no botão Procurar... 9. Localize o arquivo Movimento-Abril.xlsx. 10. Depois de selecioná-lo no quadro Procurar, clique em OK. 11. Clique na tecla de função F2. 12. Clique em qualquer local dentro da caixa de referência para desfazer a seleção. 13. Tecle End e digite o nome do intervalo que contém os dados: Abril. 14. Clique em Adicionar. 15. Para que os itens sejam relacionados e seus valores considerados corretamente, assinale Coluna esquerda em Usar rótulos na. 16. Assinale Criar vínculos com dados de origem.

17. Clique em OK. Analise as células que contém os valores. Agora elas possuem fórmulas. Qualquer alteração nas planilhas mensais refletirá no resultado da planilha Vinculado. 18. Altere alguns valores das planilhas mensais e compare os reflexos nas planilhas Consolidado e Vinculado. O Excel introduziu uma área cinza com números e sinais do lado esquerdo da planilha a qual chamamos de Estrutura de Tópicos. Sua função é permitir ao usuário ocultar ou exibir dados que interferem nas fórmulas criadas. Agora você fará uso deste recurso e terá uma visão mais completa na próxima atividade. 19. Clique nos botões que se assemelham a sinais de adição. As linhas que contém os itens que contribuem para o subtotal são exibidas. Ao clicar novamente no botão, que agora tem aparência do sinal de subtração, os itens são ocultos novamente. 20. Clique no número 2 e todos os detalhes de todos os subtotais são exibidos. Se você clicar no número 1 os itens serão recolhidos.

166

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Estrutura de Tópicos Objetivo :

• Apresentar o recurso de Estrutura de tópicos que configura os dados para serem exibidos ou ocultos.

Tarefas

• Introduzir o recurso AutoTópicos e o recurso Estrutura de Tópicos para agrupar dados arbitrários.

1. Abra o arquivo Tópicos.xlsx. 2. Clique na parte Inferior do botão Agrupar do grupo Estrutura de Tópicos da guia Dados.

3. Clique em AutoTópicos.

Os sinais são usados para ocultar/exibir os itens que compõem cada subtotal. Os números permitem a escolha do nível de subtotal que será exibido para todos os subtotais. 4. Clique nos sinais e números para familiarizar-se com seu funcionamento.

O recurso AutoTópicos cria os agrupamentos reconhecendo os itens que compõem os cálculos. Considera, portanto, a lógica usada na construção da fórmula. Você vai agrupar itens que não contribuem para os cálculos da planilha. Como o Excel não pode reconhecer a lógica devemos informar arbitrariamente os itens a serem agrupados. 5. Selecione as colunas F, G e H. 6. Clique na parte Inferior do botão Agrupar do grupo Estrutura de Tópicos da guia Dados. 7. Clique em Agrupar.

Senac São Paulo

167

Excel 2010 – Avançado

Uma vez que a intenção é Agrupar, podemos clicar diretamente na parte superior do botão Agrupar. Se a seleção não for feita pelos botões seletores das colunas e das linhas, uma mensagem aparece para você informe se deseja agrupar as linhas ou colunas selecionadas. .

8. Para desagrupar, selecione as linhas ou colunas e clique no botão Desagrupar do grupo Estrutura de tópicos da guia Dados.

Para retirar a estrutura de tópicos da planilha clique na parte inferior do botão Desagrupar e escolha Limpar Estrutura de Tópicos.

168

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Subtotais Objetivo :

• Apresentar o recurso Subtotal que resume dados em uma planilha.

Tarefa

• Resumir os dados por Tipo e por categoria.

1. Abra o arquivo Movimento Financeiro2.xlsx. 2. Classifique pela coluna E/S e Categoria. A classificação é necessária para que os itens que formam um mesmo grupo fiquem juntos. O recurso percorre o campo escolhido e subtotaliza a cada vez que a informação naquela coluna fica diferente. Se os dados não estiverem juntos, vários subtotais do mesmo item podem compor o resultado. 3. Clique no botão Subtotal do grupo Estrutura de Tópicos da guia Dados.

4. No campo A cada alteração em: escolha E/S. 5. Escolha a função Soma. 6. Marque os campos a serem totalizados. Neste exemplo use Valor. 7. Clique em OK. 8. Clique novamente no botão Subtotal do grupo Estrutura de Tópicos da guia Dados para acrescentar outro subtotal. 9. Escolha o campo Categoria, mantenha a função Soma selecionada e o campo Valor para adicionar subtotal. 10. Desmarque a opção Substituir subtotais atuais. 11. Marque a opção Quebra de página entre grupos.

Senac São Paulo

169

Excel 2010 – Avançado

12. Clique em OK.

Os Subtotais adicionais são inseridos antes dos subtotais inseridos anteriormente. Por essa razão, quando optamos pela quebra de página entre grupos quando já existem subtotais aplicados, a quebra de página pode não ser a desejada. É necessário efetuar a quebra dos grupos manualmente. Siga os passos descritos a seguir. 13. Clique no botão Quebras do grupo Configurar Página da guia Layout da Página.

14. Clique na opção Redefinir todas as quebras de página. 15. Localize a linha pontilhada vertical que indica a largura máxima a ser impressa. 16. Torne ativa a célula imediatamente à direita da linha e logo abaixo do subtotal. 17. Clique no botão Quebras e escolha Inserir Quebra de Página.

18. Repita os passos 15, 16 e 17 para cada subtotal desejado. 19. Clique na opção Imprimir da guia Arquivo.

170

Senac São Paulo

Excel 2010 – Avançado

20. Visualize a primeira página da impressão à direita.

Nas páginas seguintes os cabeçalhos das colunas não são repetidos. 21. Clique na guia Layout da Página. 22. Clique no botão Imprimir Títulos do grupo Configurar Página.

Senac São Paulo

171

Excel 2010 – Avançado

23. Selecione na planilha as linhas a repetir na parte superior.

24. Clique em OK. As linhas selecionadas serão repetidas em cada página impressa.

172

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Cenários Objetivo :

• Apresentar o recurso Gerenciador de Cenários para realizar simulações.

Tarefa

• Formular hipóteses para analisar os efeitos nos saldos ao se alterarem as entradas e as saídas.

Você irá simular variações de valores para as planilhas analisando cenários prováveis. Você criará um primeiro cenário com os dados atuais da planilha para poder recuperar estes dados no futuro. Em seguida você criará um cenário prevendo aumento de 25% nos itens de Entrada de janeiro, aumento de 15% nas Saídas de fevereiro e queda de 10% nas Saídas de março. 1. Abra o arquivo Cenários.xlsx. 2. Vá para a planilha Janeiro. 3. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados.

4. Escolha Gerenciador de Cenários... A lista aparece vazia pois nenhum cenário foi criado ainda. Clique no botão Adicionar. 5. Preencha o campo Nome do Cenário com Dados Originais.

Você deve selecionar as células variáveis. São as células que terão seus valores alterados para análise dos resultados. As células selecionadas não devem conter fórmulas, pois quando um cenário é aplicado as fórmulas serão substituídas por valores. 6. Selecione os intervalos B3:B4 e B6:B9, no campo Células variáveis.

Senac São Paulo

173

Excel 2010 – Avançado

7. Preencha o campo Comentário como achar necessário. 8. Clique em OK. 9. As células variáveis aparecem para receber novos valores. Como a idéia é recuperar os valores atuais, eles não serão alterados.

10. Clique em OK para que o cenário seja criado como os valores atuais. 11. Clique em Adicionar. 12. Preencha o campo Nome do cenário com Aumento de 25% nas Entradas de janeiro. 13. Mantenha as mesmas células variáveis. 14. Preencha o campo Comentário.

15. Clique em OK. 16. Clique em OK. 17. Digite a fórmula que representa o cálculo de acréscimo de 25% ou digite o valor já calculado.

174

Senac São Paulo

Excel 2010 – Avançado

18. Clique em OK.

Surge um quadro avisando que as fórmulas foram convertidas nos valores correspondentes ao cálculo.

O Gerenciador de cenários agora apresenta os cenários que já foram criados.

19. Selecione o cenário que quer analisar e clique no botão Mostrar.

Para alterar os valores de um cenário clique no botão Editar... e modifique os valores das células variáveis. Clique em Excluir para eliminar um cenário. Agora você criará novo cenário com a hipótese de aumento de 15% nas Saídas de fevereiro. Lembre-se de criar um cenário para recuperar os dados originais.

Senac São Paulo

175

Excel 2010 – Avançado

20. Vá para a planilha Fevereiro. 21. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 22. Escolha Gerenciador de Cenários... 23. Clique no botão Adicionar. 24. Preencha o campo Nome do Cenário com Dados Originais. 25. No campo Células variáveis informe os intervalos B3:B5 e B7:B9. 26. Preencha o campo Comentário. 27. Clique em OK até retornar ao Gerenciador de cenários. 28. Clique no botão Adicionar. 29. Preencha o campo Nome do Cenário com Aumento de 15% nas saídas de fevereiro. 30. Nas células variáveis B7, B8 e B9 escreva a fórmula que acrescenta 15% a cada valor.

31. Clique em OK. 32. Clique OK no quadro de aviso. 33. Clique em Mostrar para analisar os valores. Você criará um cenário que simula a hipótese de queda de 10% nas saídas de março. Antes crie um cenário para recuperar os dados originais da planilha. 34. Vá para a planilha Março. 35. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 36. Escolha Gerenciador de Cenários... 37. Clique no botão Adicionar. 38. Preencha o campo Nome do Cenário com Dados Originais. 39. No campo Células variáveis informe os intervalos B3:B5 e B7:B10. 40. Preencha o campo Comentário. 41. Clique em OK até retornar ao Gerenciador de cenários. 42. Clique no botão Adicionar. 43. Preencha o campo Nome do Cenário com Queda de 10% nas saídas de março. 44. Nas células variáveis B7, B8, B9 e B10 escreva a fórmula que diminui 10% de cada valor.

176

Senac São Paulo

Excel 2010 – Avançado

45. Clique em OK. 46. Clique OK no quadro de aviso. 47. Clique em Mostrar para analisar os valores. Quando você tem uma pasta de trabalho com os mesmos dados, você pode aproveitar cenários que já foram criados em outra pasta. Agora você abrirá um arquivo e importará os cenários criados nas atividades anteriores. 48. Abra o arquivo Cenários-Mesclar.xlsx. 49. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 50. Escolha Gerenciador de Cenários... 51. Clique no botão Mesclar.

Na caixa de combinação Pasta: aparecem os arquivos do Excel que estão abertos na memória. Na caixa de listagem Planilha: aparecem as planilhas do arquivo selecionado. Os dados do arquivo correspondem aos valores de janeiro e, portanto, importaremos os cenários de janeiro. 52. Certifique-se que o arquivo Cenários.xlsx está selecionado no campo Pasta:. 53. Selecione a planilha Janeiro. 54. Clique em OK. No Gerenciador de cenários aparecem os cenários que foram importados.

Senac São Paulo

177

Excel 2010 – Avançado

55. Volte ao arquivo Cenários.xlsx. 56. Vá para a planilha Março. 57. No Gerenciador de cenários clique no botão Resumir... 58. Selecione a célula B6 que representa a soma das entradas e com a tecla CTRL pressionada clique em B11 que representa a soma das saídas e B12 que representa o saldo.

59. Clique em OK. Será criada uma planilha com o resumo dos cenários que foram criados.

178

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta Despesas.xlsx e consolide os dados das lojas na planilha Trimestre. 2. Crie Cenários para aumento de 20% no Gás para a Loja Centro em janeiro. 3. Crie Cenários para queda de 10% no item telefone para a loja SUL em março.

Senac São Paulo

179

Excel 2010 – Avançado

ANOTAÇÕES

180

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Restringir a introdução de dados nas planilhas.

Senac São Paulo

181

Excel 2010 – Avançado

182

Senac São Paulo

Excel 2010 – Avançado

Atividade 1 – Criar regras de validação Objetivo :

• Determinar quais dados pode ser introduzido nas planilhas.

Tarefa

• Definir as regras para aceitação dos dados introduzidos na planilha.

Algumas planilhas podem conter restrições quanto às informações que podem ser introduzidas em determinadas colunas. Neste caso precisamos estabelecer quais serão os critérios para aceitação ou não dos dados introduzidos. Para esse fim utilizamos o recurso Validação de Dados. 1. Abra o arquivo Validação.xlsx. 2. Selecione a coluna A clicando no botão seletor.

3. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 4. Na caixa de combinação Permitir: escolha Data.

5. Na caixa de combinação Dados: escolha é maior do que. 6. Em Data de inicio: digite 30/09/10. Somente datas posteriores à data de início serão aceitas.

Senac São Paulo

183

Excel 2010 – Avançado

7. Clique em OK. Vá para o final da planilha e introduzida dados na primeira linha disponível. 8. Informe a data de 10/05/10. A Validação de Dados está restringindo os dados desta coluna para datas posteriores a 30/09/10. Portanto uma mensagem de erro surge informando que a regra foi violada.

9. O botão Repetir permite que você volte à célula e digite uma data válida. O botão Cancelar desconsidera a digitação e retorna o valor anterior da célula. Clique em Repetir e digite uma data válida. Os dados da coluna Projeto devem pertencer a uma lista de projetos já definidos na planilha Projetos. 10. Clique na planilha Projetos. A lista desta planilha é a que deve ser considerada a digitação da coluna Projeto. 11. Selecione a coluna A. 12. Clique na caixa de nome para definir um nome para o intervalo selecionado. 13. Digite Projetos e finalize com ENTER. 14. Volte à planilha Dados. 15. Selecione a coluna B. 16. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 17. No campo Permitir escolha Lista. 18. No campo Fonte: digite =Projetos. 19. Desmarque a opção Ignorar em branco.

184

Senac São Paulo

Excel 2010 – Avançado

20. Você pode associar uma Mensagem de entrada às células que recebem a Validação de Dados como forma de auxílio ao digitador. Clique na guia Mensagem de entrada. 21. Preencha os campos Título e Mensagem de entrada.

Esta mensagem ficará visível quando a célula for ativada. 22. Você também pode substituir o alerta de erro padrão por um alerta de erro personalizado. Clique na guia Alerta de erro. 23. Preencha o Título e a Mensagem de erro. Tecle ENTER para continuar o texto em outra linha. 24. Escolha o Estilo do quadro de mensagem.

Senac São Paulo

185

Excel 2010 – Avançado

- O estilo Parar apresenta os botões Repetir para voltar à célula no modo de edição, e o botão Cancelar para retornar ao conteúdo anterior da célula. Neste estilo não há possibilidade de introduzir algum dado que infrinja o critério. - O estilo Aviso apresenta o botão Sim para aceitar o dado digitado, Não para rejeitá-lo e voltar à digitação e Cancelar para retornar o dado anterior. - O estilo Informações exibe o botão OK para aceitar o dado digitado e o botão Cancelar para retornar o dado anterior.

25. Escolha Aviso ou Informações. 26. Clique em OK.

186

Senac São Paulo

Excel 2010 – Avançado

Agora você aplicará a validação de dados à coluna E/S para aceitar os valores Entrada ou Saída. 27. Selecione a coluna E. 28. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 29. Em Permitir: escolha Lista. 30. No campo Fonte: digite Entrada e Saída separados por ponto e vírgula (;). 31. Escolha o estilo de alerta de erro e preencha os campos Título e Mensagem de erro. 32. Clique em OK. 33. Os valores a serem introduzidos em Valor – coluna F – não podem ser menores ou iguais a zero e nem maiores que 10 milhões. Você aplicará a validação de dados para que este critério seja respeitado. Selecione a coluna F. 34. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 35. Em Permitir: escolha Decimal. 36. Preencha o campo Mínimo: com 1 e o campo Máximo: com 10000000. 37. Defina o Alerta de erro e clique em OK.

38. Complete as informações das colunas restantes.

Senac São Paulo

187

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta validaexercício.xlsx . 2. Coloque restrições para que a coluna Estado civil apresente uma lista com Casado(a), Solteiro(a), Viúvo(a), Separado(a). 3. Na coluna Sexo deve aparecer uma lista com Masculino e Feminino. 4. Na coluna Nascimento somente devem ser aceitas pessoas nascidas antes de 1996. 5. Na coluna Salário o limite superior é 18.000.

188

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Apresentar recursos de trabalho em equipe na mesma pasta de trabalho associados aos recursos de proteção de planilha.

Senac São Paulo

189

Excel 2010 – Avançado

190

Senac São Paulo

Excel 2010 – Avançado

Trabalho em Equipe

Quando abrimos um arquivo em rede, a pasta de trabalho será aberta no modo exclusivo. Se mais alguém tentar abrir o mesmo arquivo, será avisado que só poderá fazê-lo no modo Somente Leitura. Este modo permite que este usuário altere os dados, mas não poderá sobrescrever os dados nesta pasta de trabalho. Para guardar os dados alterados terá que salvar o arquivo com outro nome ou gravar com o mesmo nome, porém em local diverso do original. O Excel permite que você compartilhe o arquivo para que várias pessoas possam usá-lo ao mesmo tempo. É possível acompanhar as alterações feitas e qualquer pessoa pode aceitar ou rejeitar as alterações realizadas. Para que o compartilhamento seja útil e controlado, você deve combinar este recurso com os recursos de proteção de planilha.

Atividade 1 – Proteção de planilha Objetivo :

• Aplicar restrições ao uso da planilha.

Tarefas

• Identificar o usuário do Excel.

• Dotar a planilha dos recursos de proteção de planilha. 1. Clique em Opções da guia Arquivo.

2. Digite seu nome no campo Nome do Usuário.

Senac São Paulo

191

Excel 2010 – Avançado

3. Clique em OK. 4. Abra o arquivo Movimento-equipe.xlsx. O Excel oferece vários recursos para proteção de planilha. O Excel determina que todas as células de todas as planilhas estão, a princípio, bloqueadas. Porque, então, conseguimos digitar dados em qualquer célula de qualquer planilha? A razão é que esta propriedade só passa a funcionar quando acionamos o recurso de proteção da planilha. Você pode desbloquear células para que o usuário altere-as, e impedir que ele altere as outras células bloqueadas. Você pode, também, proteger a pasta de trabalho para não se possa inserir, excluir, mover, renomear, ocultar e reexibir planilhas da pasta, bem como proteger as janelas da pasta de trabalho. Além destes recursos abordados no curso básico, você definirá intervalos protegidos com senha para que só usuários autorizados possam alterá-los. 5. Selecione todos os dados abaixo dos cabeçalhos da planilha Entrada.

192

Senac São Paulo

Excel 2010 – Avançado

6. Clique no botão Formatar do grupo Células da guia Página Inicial. Perceba que o item Bloquear Célula está ativado pois este é o padrão. 7. Clique no item Bloquear Célula para desativar esta propriedade para as células que estão selecionadas. Todas as demais permanecerão bloqueadas. 8. Para que o bloqueio passe a funcionar clique novamente no botão Formatar e clique em Proteger Planilha... 9. Marque as operações que deseja permitir para todos os usuários. 10. Digite uma senha, clique em OK e confirme a senha.

11. Tente alterar o cabeçalho e verá que surge uma mensagem indicando que as células estão bloqueadas.

Todos os dados abaixo dos cabeçalhos permitem a edição porque foram desbloqueados.

Para ocultar fórmulas das planilhas selecione as células que as contém, clique em Formatar do grupo Célula da guia Página Inicial. Clique na opção Formatar Células... A seguir clique na guia Proteção, marque a opção Ocultas, clique OK e ative a proteção de planilha.

Senac São Paulo

193

Excel 2010 – Avançado

Para que somente usuários autorizados tenham permissão de edição, colocaremos proteção por senha em alguns intervalos da planilha. Você definirá uma senha para os dados abaixo do cabeçalho da planilha Entrada e outra senha para os dados abaixo do cabeçalho da planilha.

Se um intervalo tiver suas células desbloqueadas, qualquer usuário poderá editá-las. Para que só usuários autorizados possam editá-las, precisamos bloqueá-las de novo. 12. Remova a proteção da planilha. Clique em Desproteger planilha do grupo Células da guia Página Inicial.

13. Digite a senha introduzida anteriormente e clique em OK. 14. Selecione o intervalo A4:G28. 15. Clique em Bloquear Célula. Esta ação tornará o intervalo de células bloqueado novamente. 16. Vá para a guia Revisão. 17. Clique em Permitir que os Usuários Editem Intervalos do grupo Alterações.

18. Clique em Novo... 19. Em Título: digite Entradas. 20. Em Referência a células: informe o intervalo que deseja permitir a edição A4:G28. 21. Digite uma senha. 22. Clique em OK.

194

Senac São Paulo

Excel 2010 – Avançado

Se cada parte da planilha é de responsabilidade de grupos de usuários diferentes, crie um novo intervalo para cada uma destas partes. 23. Após definir todos os intervalos da planilha clique em Proteger planilha... Quando for solicitado, informe a senha de proteção de planilha. 24. Clique em Ok. 25. Execute os mesmos procedimentos para criar intervalos de edição para a planilha Saída.

Senac São Paulo

195

Excel 2010 – Avançado

ANOTAÇÕES

196

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Compartilhar a pasta de trabalho Objetivo :

• Permitir que mais de um usuário possa fazer alterações na pasta de trabalho ao mesmo tempo.

Tarefas

• Compartilhar a pasta de trabalho Movimento-equipe.xlsx.

• Controlar as alterações. 1. Clique em Compartilhar Pasta de Trabalho do grupo Alterações da guia Revisão. 2. Marque a caixa de verificação Permitir alterações por mais de um usuário ao mesmo tempo.

3. Clique em OK e uma mensagem é exibida informando que a planilha será salva. Confirme e a pasta já está compartilhada. 4. Clique em Controlar Alterações para enxergar as edições dos usuários. 5. Clique em Realçar Alterações...

6. Desmarque as opções Quando: e Autor: para que todas as alterações possam ser vistas.

Senac São Paulo

197

Excel 2010 – Avançado

7. Clique em OK. 8. Peça que outro colega abra seu arquivo pela rede e faça algumas alterações. É necessário que você forneça as senhas dos intervalos. 9. Peça para que ele salve e veja as alterações na pasta aberta no seu computador. 10. Você pode gerar uma planilha com todas as alterações de todos os usuários que recebe o nome Histórico. Seu nome é reservado não sendo possível renomear outras planilhas com este nome. Clique em Controlar Alterações. 11. Clique em Realçar Alterações... 12. Desmarque Quando: e Autor: 13. Marque Listar alterações em uma nova planilha. 14. Clique em OK.

A planilha Histórico é gerada com as alterações ocorridas e será descartada quando salvarmos a pasta de trabalho. 15. Retire o compartilhamento da pasta de trabalho para abri-la no modo exclusivo. 16. Clique em Compartilhar Pasta de Trabalho do grupo Alterações da guia Revisão. 17. Desmarque a opção Permitir alterações por mais de um usuário ao mesmo tempo. 18. Clique em OK.

198

Senac São Paulo

Excel 2010 – Avançado

19. Leia o alerta e caso queira remover o compartilhamento clique em Sim. A pasta não está mais compartilhada. Você pode definir senha de proteção e senha de gravação para a pasta de trabalho quando a pasta for salva. A senha de proteção é solicitada ao abrir a pasta. A senha de gravação é solicitada ao salvar a pasta de trabalho. 20. Clique na guia Arquivo. 21. Clique em Salvar Como. 22. Clique no botão Ferramentas e escolha Opções Gerais...

23. Digite as senhas e clique em OK.

24. Digite a confirmação das senhas. salve e feche a pasta de trabalho. 25. Abra a pasta e, quando solicitada, informe a senha de proteção. 26. Faça alterações e clique em Salvar. A senha de gravação será solicitada.

Senac São Paulo

199

Excel 2010 – Avançado

Exercício Proposto 1. Abra o arquivo Produtos.xlsx. 2. Crie um intervalo com permissão de edição da coluna Preços para cada departamento, definindo uma senha para cada intervalo: Intervalos: C2:C16 para Informática, C17:C23 para Telefones e celulares,C24:C30 para Câmeras e Filmadoras, C31:C44 para Eletrônicos, C45:C73 para Eletro portáteis, C74:C88 para Utilidades Domésticas, C89:C100 para Esporte e Lazer. 3. Proteja a planilha e compartilhe a pasta de trabalho. 4. Faça alterações na planilha e solicite que outro colega abra seu arquivo e altere os dados de um intervalo. Você deve fornecer a senha desse intervalo. Acompanhe as alterações efetuadas.

200

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Introduzir recursos para simulação e análise de valores.

Senac São Paulo

201

Excel 2010 – Avançado

202

Senac São Paulo

Excel 2010 – Avançado

Atividade 1 – Atingir Meta Objetivo :

• Apresentar o recurso Atingir Meta que permite formular hipóteses de variação de um valor para que uma fórmula apresente um determinado resultado.

Tarefa

• Calcular quanto um item das entradas deve variar para alcançar um saldo positivo.

1. Abra o arquivo Metas.xlsx. Nos meses de abril e junho os saldos são negativos, indicando que as somas das Entradas, que representam os valores que empresa prevê receber, não conseguirão superar as saídas, que representam os compromissos da empresa. Para ela captar recursos e aumentar a entrada de recursos será preciso realizar o desconto de duplicatas nesses meses para fazer frente aos compromissos. Você usará o recurso Atingir meta para definir quanto o item Duplicatas - Desconto deve alcançar. 2. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. Escolha Atingir meta...

3. No quadro Atingir meta informe a célula E3 no campo Definir célula:. 4. Em Para valor: digite 222000 que é o valor a ser alcançado por E3. 5. Defina a célula E7 como célula variável em Alternando célula:.

6. Clique em OK.

Senac São Paulo

203

Excel 2010 – Avançado

A célula E7 (Duplicatas – Desconto) terá seu valor alterado para que a célula E13 (soma das entradas) alcance 222.000. Se você clicar em OK estes valores serão assumidos. Se você clicar em Cancelar os valores anteriores permanecerão.

204

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Solver Objetivo :

• Apresentar o recurso Solver que permite formular hipóteses.

Tarefa

• Formular hipóteses de alteração de valores em uma planilha, aplicando restrições conhecidas.

O recurso Solver consiste em um complemento que precisa ser carregado para que você possa usá-lo. Carregue este complemento. 1. Abra o arquivo Solver.xlsx. 2. Clique na guia Arquivo. 3. Clique em Opções. 4. Clique em Suplementos. 5. Certifique-se que no campo Gerenciar: está selecionada a opção Suplementos do Excel e clique no botão Ir...

6. Marque a opção Solver e clique em OK.

Senac São Paulo

205

Excel 2010 – Avançado

A pasta Solver.xlsx apresenta o cálculo que definirá o número de passageiros que determinado avião pode carregar de acordo com algumas restrições conhecidas. O problema é calcular o número de passageiros que um avião pode levar em uma viagem. O peso total deste tipo de avião não pode ultrapassar 1.157 kg. Para a viagem pretendida ele consome 182 kg de combustível, levará 91 kg no bagageiro e deve-se considerar que nos 2 assentos dianteiros permite um peso máximo de 154 kg. Um destes assentos é ocupado pelo piloto. Para cálculo do peso das pessoas a bordo é considerado o valor de 77 kg. O avião vazio pesa 635 kg. Desta forma você tem as seguintes restrições a considerar: Peso do avião vazio

= 635 kg

É sempre o mesmo.

Assentos dianteiros

>= 77 kg e <= 154 kg

Pelo menos um assento ocupado pelo piloto.

Combustível

= 182 kg

Necessário para a viagem.

Bagageiro

= 91 kg

Peso da bagagem.

Passageiros Peso máximo permitido

<=1.157 kg

Valor a ser encontrado. Cada passageiro = 77 kg. A soma dos itens acima não pode ultrapassar este limite.

Quantos passageiros pode-se transportar respeitando estes limites e maximizando o peso disponível?. 7. Clique na guia Dados. Agora existe um novo grupo chamado Análise. 8. Clique em Solver. A célula B7 apresenta o cálculo do peso total e quanto maior for este valor maior será o número de passageiros que poderá ser transformado. Portanto, você definirá esta célula como objetivo a ser maximizado. 9. No campo Definir Objetivo: selecione a célula B7. 10. No campo Alterando células variáveis: selecione o intervalo B2:B6.

206

Senac São Paulo

Excel 2010 – Avançado

11. Clique no botão Adicionar para definir as restrições. 12. No campo Referência de Célula: selecione A11. 13. Escolha o operador =. 14. No campo Restrição: selecione C11.

15. Clique em Adicionar. 16. Repita os passos 12 a 15 para as restrições do quadro abaixo:

A12 A12 A13 A14 A15

>= <= = = <=

C12 E12 C13 C14 C15

17. Após a última restrição clique em OK ao invés de Adicionar.

Senac São Paulo

207

Excel 2010 – Avançado

18. Clique no botão Resolver.

Caso haja conflito entre as restrições e as opções escolhidas o Solver apresentará uma mensagem informando que não encontrou uma solução.

19. Selecione os relatórios Resposta, Sensibilidade e Limites. 20. Caso os valores apresentados sejam satisfatórios, marque a opção Manter Solução do Solver. Caso contrário, marque Restaurar Valores Originais. 21. Clique em OK.

Você pode transformar a resposta do Solver em um Cenário. Para isto, clique no botão Salvar Cenário...

208

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Tabela de Dados Objetivo :

• Conhecer a ferramenta Tabela de dados que formula hipóteses de variação de valores em uma fórmula.

Tarefa

• Criar tabela com os valores das parcelas de um pagamento considerando variação na taxa de juros e no número de períodos.

Você criará uma tabela que contém os valores das parcelas de um financiamento combinando vários prazos e várias taxas de juros. 1. Abra o arquivo Tabela de Dados.xlsx.

Na célula B5 existe uma fórmula que calcula o valor da parcela considerando um empréstimo de R$ 180.000,00, uma taxa de juros de 2,40% e um prazo de 36 meses - =PGTO(B2;B3;B1).

Senac São Paulo

209

Excel 2010 – Avançado

Abaixo a fórmula se repete na célula C7. Na área próxima existem valores de períodos e taxas a serem calculados. Você usará o recurso Tabela de Dados para estender os cálculos. 2. Selecione o intervalo C7:H27. A fórmula deve estar incluída no intervalo onde os dados serão expandidos. 3. Clique em Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 4. Escolha a opção Tabela de Dados... 5. Preencha o campo Célula de entrada da linha com a célula que na fórmula representa os dados que estão dispostos em linha, neste caso, os períodos. Na fórmula o período está na célula B3. Clique nesta célula com o mouse. 6. Preencha o campo Célula de entrada da coluna com a célula que na fórmula representa os dados que estão dispostos em coluna, ou seja, as taxas, célula B2.

7. Clique em OK.

210

Senac São Paulo

Excel 2010 – Avançado

Quando o valor do empréstimo, que representa o VF (Valor futuro) na fórmula, é positivo , o valor das parcelas serão negativas. Para que as parcelas sejam positivas altere a fórmula introduzindo o sinal de menos antes da célula que na função representa o Valor Futuro =PGTO(B2;B3;-B1). O recurso Tabela de dados cria fórmulas matriciais. Estas fórmulas não podem ser alteradas individualmente porque formam um conjunto.

Senac São Paulo

211

Excel 2010 – Avançado

ANOTAÇÕES

212

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Inserir Tabela Objetivo :

• Transformar um intervalo em uma tabela gerenciável.

Tarefas

• Transformar dados de um intervalo em uma tabela.

• Acrescentar totalizações. 1. Abra o arquivo MovimentoTabela.xlsx. 2. Clique em qualquer célula do intervalo de dados. 3. Clique em Tabela do grupo Tabelas na guia Inserir. 4. Confirme ou selecione o intervalo de dados com os cabeçalhos, todas as linhas e todas as colunas.

5. Clique em OK.

Senac São Paulo

213

Excel 2010 – Avançado

Surgem os botões do AutoFiltro e uma nova guia Ferramentas de Tabela. 6. Marque a caixa de verificação Linha de total do grupo Opções de Estilo de Tabela. 7. Na coluna desejada, clique no botão da linha de totais e escolha a função que deseja para a coluna.

Se você clicar em Mais Funções... surge o assistente Inserir Função.

Na escolha das funções do primeiro menu é introduzida a função SUBTOTAL, que tem a seguinte sintaxe: SUBTOTAL(operação;campo) A operação é um número que representa a função a ser usada e campo o cabeçalho da coluna a ser operada delimitada por colchetes.

214

Senac São Paulo

Excel 2010 – Avançado

Tabela com os códigos possíveis para as operações: OPERAÇÃO (INCLUINDO VALORES OCULTOS) 1 2 3 4 5 6 7 8 9 10 11

OPERAÇÃO (IGNORANDO VALORES OCULTOS) 101 102 103 104 105 106 107 108 109 110 111

FUNÇÃO MÉDIA CONT.NUM CONT.VALORES MÁXIMO MÍN PRODUTO DESVPAD.A DESVPAD.P SOMA VAR.A VAR.P

Para formatar a tabela use a guia Design da Ferramentas de Tabela:

8. Para transformar novamente a tabela em intervalo clique em Converter em Intervalo do grupo Ferramentas da guia Ferramentas de Tabela.

Senac São Paulo

215

Excel 2010 – Avançado

Exercício Proposto 1. Abra o arquivo Metas-exercício.xlsx.

Qual deve ser o valor das receitas para que o Lucro descontando o IR atinja 350.000? 2. Abra o arquivo Solver-exercício.xlsx. Neste exercício a empresa Água Preta telecomunicações deseja obter um total de entradas igual a 926.000. O valor das Contas a Receber é igual a 576.000. Você deve calcular quais os valores que serão provenientes dos sócios e completados com empréstimos considerando que os empréstimos devem corresponder a 33% da soma das Contas a Receber e do Dinheiro dos sócios.

3. Abra o arquivo Tabela-exercício.xlsx.

216

Senac São Paulo

Excel 2010 – Avançado

Preencha a coluna para os possíveis valores assumidos por X. Perceba que os dados estão dispostos em coluna.

Senac São Paulo

217

Excel 2010 – Avançado

ANOTAÇÕES

218

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Apresentar o gravador de macro.

Senac São Paulo

219

Excel 2010 – Avançado

220

Senac São Paulo

Excel 2010 – Avançado

Trabalhando com Macros

Macro é uma seqüência de comandos que são gravados para executar tarefas de forma rápida e sem margem de erro. Quando você identificar atividades repetitivas ou complexas, você pode torná-las simples e rápidas através da gravação de uma macro.

Atividade 1 – Criar macro Objetivo :

• Identificar um processo que pode ser automatizado, planejar e criar a macro.

Tarefas

• Criar macro que atualize a planilha Macros.xlsx com informação dos últimos seis meses .

A planilha Dados contém informações de um período de seis meses. Quando a informação atualizada é recebida, os dados do primeiro mês são descartados e os novos dados são introduzidos. 1. Abra a pasta de trabalho Macros.xlsx. 2. Digite dados de teste na área de entrada: 100 em B9, 200 em B10, 300 em B11, 400 em B12.

3. Acesse a guia Exibição, no grupo Macros, pressione o botão Macros e clique em Gravar Macro... ou pressione o botão Gravar macro na barra de Status.

4. Digite o nome da macro – “Preenche”, posicione no campo Tecla de atalho:, pressione SHIFT + P para atribuir as teclas de atalho CTRL + SHIFT + P.

Senac São Paulo

221

Excel 2010 – Avançado

A macro pode ser armazenada junto com a pasta de trabalho e, portanto, acompanha o arquivo quando ele é transportado para outro computador. As macros ficam disponíveis para qualquer pasta de trabalho enquanto a pasta de trabalho que as contém estiver carregada na memória do computador. Neste caso você deve usar a opção Esta pasta de trabalho para armazenamento da macro. A macro pode ser armazenada no Excel do seu computador e, neste caso, ela pode ser usada por todas as pastas de trabalho que forem abertas nele. Neste caso você deve usar a opção Pasta de trabalho pessoal de macros. As macros serão gravadas na pasta Pessoal.xlsb armazenada do diretório XLInício. A opção Nova pasta de trabalho permite que você crie uma nova pasta para receber a macro criada.

5. Escolha o local de armazenamento. Preencha a Descrição e pressione OK.

A partir de agora os comandos utilizados estão sendo gravados. 6. Selecione os meses que serão copiados para a esquerda (intervalo C2:G6) e acione o comando Copiar. 7. Selecione a célula B2 e cole.

8. Para preencher o nome do próximo mês, selecione a célula F2, coloque o cursor sobre a alça de arraste e arraste até G2. 9. Transporte os dados da área de entrada para o último mês: selecione B9:B12, copie e cole em G3 que é a posição do último mês.

222

Senac São Paulo

Excel 2010 – Avançado

10. Apague os dados da área de entrada (B9:B12). 11. Selecione F2:F6, pressione o botão Pincel e copie o formato para a próxima coluna (G2:G6).

12. Pare a gravação da macro: na guia Exibição, no grupo Macros pressione Parar Gravação ou botão Parar Gravação na Barra de status.

13. A macro está pronta para ser usada. Digite alguns valores na área de entrada e teste a macro. 14. Clique no botão Macros, selecione a Macro desejada e clique em Executar. Ao criar uma macro em uma pasta de trabalho com a extensão .xlsx e tentar salvá-la, surgirá uma mensagem alertando que as Macros só serão armazenadas junto com a pasta de trabalho se alterarmos o Tipo de Arquivo para “Pasta De Trabalho Habilitada Para Macro Do Excel (.Xlsm)”.

15. Salve a pasta de trabalho. 16. Escolha a opção Não e volte à janela Salvar Como 17. Escolha Pasta de Trabalho Habilitada Para Macro do Excel em Tipo. 18. Salve a pasta de trabalho.

Senac São Paulo

223

Excel 2010 – Avançado

ANOTAÇÕES

224

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Criando macro com deslocamentos relativos Objetivo :

• Usar referências relativas

Tarefa

• Criar uma macro que armazene os meses descartados pela macro Preenche.

Por vezes, os dados ou os resultados não tem local definido antes da execução da macro. Nestes casos você deve utilizar os comandos de forma que a própria macro encontre a posição para as informações. Imagine que você precisasse armazenar os meses que são descartados na macro da Atividade 1. Crie uma macro que transporte o mês descartado para a primeira coluna livre da planilha Registro: 1. Estando posicionado na planilha Dados, inicie a gravação da macro com o nome de “Transporta”, atribua a combinação de tecla CTRL + SHIFT + T e pressione OK.

2. Copie o intervalo do mês a ser descartado B2:B6. Seu conteúdo ficará armazenado na área de transferência.

3. Selecione a planilha Registro, pressione F5 (Ir para), preencha Referência com XFD1 e clique em OK. A célula ativa será a primeira célula da última coluna (XFD).

Senac São Paulo

225

Excel 2010 – Avançado

4. Acesse a guia Exibição, no grupo Macros e clique em Usar Referências Relativas. Certifique-se que o botão está ativado. Esta providência é necessária para que a cópia do mês ocorra sempre em uma coluna vazia.

5. Pressione a tecla END e depois pressione seta para a esquerda para tornar ativa a primeira célula preenchida a esquerda. 6. Pressione seta à direita para posicionar na primeira coluna vazia.

7. Acione o comando Colar. O conteúdo da área de transferência é colado aqui.

226

Senac São Paulo

Excel 2010 – Avançado

8. Desative Usar Referências Relativas. 9. Volte à planilha Dados e pare a gravação da macro.

Senac São Paulo

227

Excel 2010 – Avançado

ANOTAÇÕES

228

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Execução e exclusão de macros Objetivo :

• Trabalhar com Macros.

Tarefas

• Executar Macros.

• Excluir Macros Você pode executar a macro de três modos: •

Usando a combinação de teclas definida na gravação da macro.

•

Acessando a guia Exibição e no grupo Macros pressionar o botão Macros. Aparecerá a tela abaixo:

Esta caixa de diálogo permite a execução e também a exclusão da macro. •

Criando um botão na barra de ferramentas de acesso rápido ou criando uma guia personalizada na faixa de opções.

Execute as macros criadas: 1. Preencha a área de entrada com alguns dados e use a combinação Ctrl + SHIFT + T para executar a macro Transporta. 2. Acesse a guia Exibição, pressione o botão Macros do grupo Macros. 3. Na lista, selecione a macro Preenche e pressione o botão Executar. Você criará, agora, uma nova guia na faixa de opções. 4. Clique no botão Opções da guia Arquivo.

Senac São Paulo

229

Excel 2010 – Avançado

5. Selecione o item Personalizar Faixa de Opções. 6. Clique em Nova Guia e em Renomear.

7. Digite Executar comandos.

8. Selecione Novo Grupo (Personalizado) e clique em Renomear. 9. Digite o nome Macros.

230

Senac São Paulo

Excel 2010 – Avançado

10. Clique em OK.

11. Na caixa de combinação Escolher comandos em: selecione Macros. 12. Selecione a macro Preencher e clique em Adicionar. 13. Selecione o item Preencher no lado direito e clique em Renomear.

Senac São Paulo

231

Excel 2010 – Avançado

14. Escolha um ícone e clique em OK. 15. Clique em OK novamente e a guia Executar comandos aparece na faixa de opções.

Segurança de macros As macros de fontes desconhecidas podem conter código que infectam o computador com vírus. Podemos determinar como o Excel tratará as macros quando a pasta de trabalho for aberta: •

Desabilitar todas as macros sem notificação - Todas as macros serão desabilitadas nos documentos e nos alertas de segurança sobre macros

•

Desabilitar todas as macros com notificação - (padrão). Você poderá decidir quando habilitar macros caso a caso. Quando esta opção é definida, toda vez que abrimos uma pasta habilitada para Macros surge a tela abaixo permitindo que habilitemos ou não as macros do arquivo.

•

Desabilitar todas as macros, exceto as digitalmente assinadas - Esta configuração é igual à opção Desabilitar todas as macros com notificação. A única exceção é que, se a macro for digitalmente assinada por um editor confiável, ela poderá ser executada se você já tiver confiado no editor. Caso contrário, você será notificado. Dessa forma, você poderá optar entre habilitar essas macros assinadas ou confiar no editor. Todas as macros não assinadas serão desabilitadas sem qualquer notificação.

•

Habilitar todas as macros (não recomendável; códigos possivelmente perigosos podem ser executados) - Permite a execução de todas as macros.

Para definir o modo de tratamento das macros: 1. Clique no botão Opções da guia Arquivo.

232

Senac São Paulo

Excel 2010 – Avançado

2. Clique em Central de Confiabilidade.

3. Clique no botão Configurações da Central de Confiabilidade, em Configurações de Macro e escolha a opção desejada.

Senac São Paulo

233

Excel 2010 – Avançado

234

Senac São Paulo

Excel 2010 – Avançado

Exercício Proposto Criar uma macro que transporte os dados da Entrada para a primeira linha disponível na região de armazenamento (abaixo da linha 6). 1. Abra a pasta de trabalho Exercício Macro1.xlsx.

2. Digite dados na área de Entrada (A3:C3). Crie a Macro com o nome “Transfere” . Não se esqueça de indicar quando o deslocamento é relativo ou não.

Senac São Paulo

235

Excel 2010 – Avançado

ANOTAÇÕES

236

Senac São Paulo

Excel 2010 – Avançado

OBJETIVOS  Criar planilhas combinando controles para automatização das planilhas.  Combinar esses controles com macros e outros recursos.

Senac São Paulo

237

Excel 2010 – Avançado

238

Senac São Paulo

Excel 2010 – Avançado

Automação de Planilhas

Neste capítulo você desenvolverá uma planilha para pesquisas de dados sobre veículos a venda, introduzindo alguns controles que facilitarão a escolha do veículo. Também serão criadas macros e fórmulas necessárias para interagir com os controles. Você utilizará o arquivo Automação.xlsx que contém dados digitados e alguns intervalos foram nomeados para facilitar a utilização de alguns recursos: •

Intervalo A4:I56 da planilha Origem dos dados: nomeado tabela – contém todos os veículos e suas características a serem pesquisados.

•

Intervalo B1:B2 da mesma planilha: nomeado modelo – contém o critério a ser utilizado para filtrar os modelos de uma determinada marca.

•

Célula B1 da planilha Modelos: nomeada titulo – contém o rótulo do campo a ser retornado ao filtrar os modelos.

Atividade 1 – Criar controles para escolha de dados Objetivo :

• Utilizar os controles de formulário para facilitar a pesquisa de dados.

Tarefas

• Criar os controles para escolha de opções

Nesta atividade você utilizará o arquivo Automação.xlsx. Você introduzirá na planilha Pesquisa os primeiros controles para fazer as escolhas básicas da pesquisa. Nas próximas atividades você complementará com os recursos necessários para tornar esses controles funcionais. 1. Verifique se a guia Desenvolvedor está presente na Faixa de Opções. Caso não esteja presente, acesse a guia Arquivo, escolha Opções. Escolha o comando Personalizar Faixa de Opções e na lista de guias à direita marque a caixa de seleção Desenvolvedor.

Senac São Paulo

239

Excel 2010 – Avançado

2. Clique em OK. 3. Abra o arquivo Automação.xlsx e acesse a planilha Pesquisa. 4. Vá para a guia Desenvolvedor. No grupo Controles, clique o comando Inserir.

5. Clique no comando Caixa de Grupo e desenhe um retângulo abaixo do texto Pesquisa de veículos.

6. Mantendo o controle selecionado, digite seu título: Veículos. 7. Clique o comando Inserir, acione o controle Botão de opção e desenhe um retângulo dentro da Caixa de Grupo Veículos.

8. Mantendo o controle selecionado digite Novos. Em seguida clique no comando Propriedades do grupo Controles e defina G5 como Vínculo da célula e marque a caixa de seleção Sombreamento 3D. Vínculo da célula é o endereço que recebe o número do botão de opção selecionado: 1 para Novos e 2 para Usados.

240

Senac São Paulo

Excel 2010 – Avançado

9. Clique em OK. 10. Repita os passos 7,8 e 9 para criar o Botão de Opção Usados. Defina G5 como vínculo da célula também para esta opção. Os botões de opções criados são identificados pelo número que representa sua ordem de criação. Portanto no nosso exemplo o botão de opção Novos recebeu o número 1 e o botão de opção Usados recebeu o número 2. Quando você escolher uma das opções ela será representada na célula G5.

11. Clique agora no comando Inserir do grupo Controles para introduzir uma Caixa de Combinação. Esta caixa de combinação exibirá as marcas disponíveis para pesquisa. Estas informações estão na coluna A da planilha Marcas. 12. Desenhe o retângulo abaixo da palavra Marca:.

13. Mantendo o controle selecionado clique sobre o comando Propriedades.

Senac São Paulo

241

Excel 2010 – Avançado

14. O intervalo de entrada representa a lista dos itens que aparecerão suspensos na Caixa de Combinação. Defina a coluna A da planilha Marcas como intervalo de entrada. Defina a célula G10 como Vínculo da célula. 15. Teste os controles alternando entre Novos e Usados. Perceba que o número correspondente ao botão selecionado aparece na célula G5. Clique no botão lateral da Caixa de combinação. Perceba que o número de ordem do item selecionado aparece na célula G10. 16. Na célula G11 digite a função: = ÍNDICE(Marcas!A:A;Pesquisa!G10;1). Esta função busca o conteúdo da coluna A da planilha Marcas que está na linha representada em G10.

242

Senac São Paulo

Excel 2010 – Avançado

Atividade 2 – Criação da Macro Modelos Objetivo :

• Apresentar somente os modelos correspondentes à marca selecionada.

Tarefa

•Criar macro que filtre os modelos a serem apresentados. Estes modelos serão exibidos na Caixa de Combinação a ser criada na próxima atividade.

Nesta atividade você criará uma macro que apresentará somente os modelos da marca selecionada. Ela incluirá a aplicação do filtro avançado e exclusão de modelos repetidos. 1. Vá para a planilha Origem dos dados. Nela estão todos os dados dos veículos disponíveis na empresa. Na célula A2 há uma referência ao veículo escolhido, Novo ou Usado, através da fórmula Pesquisa!$G$5 e na célula B2 há uma referência à marca escolhida através da fórmula Pesquisa!$G$11. 2. Na guia Desenvolvedor acione a opção Gravar Macro do grupo Código. 3. Preencha com o nome Modelo e o campo Descrição com “Cria lista de modelos da marca selecionada”.

Ao pressionar OK os comandos executados daqui por diante serão gravados na macro. 4. Selecione a planilha Modelos. 5. Clique no botão seletor da coluna A e B, depois clique em Delete. 6. Clique em B1 e digite Modelo. 7. Clique em qualquer célula fora da coluna A ou B. O recurso Filtro Avançado que você utilizará possui algumas restrições. 8. Na guia Dados, selecione Avançado do grupo Classificar e Filtrar. 9. Selecione a opção Copiar para outro local e preencha Intervalo da lista com tabela, intervalo de critérios com modelo e Copiar para com titulo. 10. Marque a opção Somente registros exclusivos e OK.

Senac São Paulo

243

Excel 2010 – Avançado

11. Será criada uma lista semelhante à lista abaixo com os modelos da marca selecionada no momento.

Você deve copiar os dados para a coluna A retirando o título Modelo. 12. Selecione a célula B2, na guia Desenvolvedor clique em Usar Referências relativas do grupo Código. 13. Mantenha pressionada a tecla SHIFT, tecle End, e seta para baixo. 14. Copie com CRTL + C ou outro comando de cópia. 15. Clique novamente em Usar Referências relativas para desativar. 16. Cole em A1. Tecle ESC para retirar o pontilhado. 17. Selecione a coluna B e clique em Delete para apagar seu conteúdo. 18. Selecione a planilha Pesquisa. 19. Na guia Desenvolvedor, escolha Parar Gravação do grupo Código.

Não se esqueça de parar a gravação. Se você não o fizer, todos os comandos que você usar na sequência serão gravados e farão parte da macro. Você pode parar a gravação através do botão Parar gravação no canto inferior esquerdo da barra de status ao lado de Pronto.

20. Quando tentar salvar o arquivo surgirá a mensagem que você perderá a macro se salvar neste formato (xlsx). Clique em Não. 21. Na guia Arquivo, escolha Salvar Como e defina o tipo de arquivo como Pasta do Excel Habilitada para Macros e salve nesse formato (xlsm).

244

Senac São Paulo

Excel 2010 – Avançado

Atividade 3 – Criar controles complementares à pesquisa Objetivo :

• Utilizar outros controles para pesquisa de dados.

Tarefa

• Criar Caixa de listagem para a escolha dos modelos.

• Criar Caixas de listagem para escolha dos opcionais. 1. Na célula E4 digite a palavra Modelo: e formate. 2. Acesse a guia Desenvolvedor e introduza uma Caixa de listagem abaixo do texto Modelo:.

3. Defina suas propriedades: Intervalo de entrada – coluna A da planilha Modelos, Vínculo da célula – G8 e Sombreamento 3D. Pressione OK.

A célula G8 apresentará um número que indicará a posição do modelo na coluna A. 4. Clique com o botão direito do mouse para selecionar a caixa de combinação das Marcas. 5. No menu suspenso escolha Atribuir Macro. Atribua a macro Modelo, criada na atividade anterior. Esta associação fará com que os itens da caixa de combinação Modelos se alterem quando escolhermos a Marca.

Senac São Paulo

245

Excel 2010 – Avançado

6. Selecione a macro e clique em OK.

Agora você vai introduzir as Caixas de Seleção para a escolha dos opcionais. 7. Na planilha Pesquisa, selecione a guia Desenvolvedor, clique em Inserir do grupo Controles. 8. Clique no controle Caixa de Seleção e desenhe um retângulo abaixo do texto Opcionais:. 9. Mantendo o controle selecionado digite Direção Hidráulica.

10. Acesse o comando Propriedade. Defina o Vínculo da célula como $G$13 e sombreamento 3D.

246

Senac São Paulo

Excel 2010 – Avançado

11. Repita os passos 5 a 8 para criar as Caixas de Seleção Ar condicionado, Travas elétricas e Vidros elétricos. Defina os vínculos das células como G15, G17 e G19 respectivamente.

12. Teste o funcionamento dos controles.

Senac São Paulo

247

Excel 2010 – Avançado

ANOTAÇÕES

248

Senac São Paulo

Excel 2010 – Avançado

Atividade 4 – Criar Critérios para a pesquisa Objetivo :

• Criar critérios que serão usados na pesquisa do veículo selecionado.

Tarefa

• Criar fórmulas necessárias para criar o intervalo de critérios a ser usado no Filtro avançado (na próxima atividade).

Abaixo é exibida parte da planilha Origem dos dados de onde os dados serão filtrados de acordo com os critérios que serão escolhidos na planilha Pesquisa.

O intervalo A1:I2 da planilha Origem dos dados determinará o critério para pesquisa. 1. Na célula C2 também use a função ÍNDICE: =ÍNDICE(Modelos!A:A;Pesquisa!$G$8) 2. Na célula D2 faça referência à célula da planilha Pesquisa que contém a opção correspondente ao Ar condicionado: =Pesquisa!$G$13 3. Em E2 digite =Pesquisa!$G$15 4. Em F2 digite =Pesquisa!$G$17 5. Em G2 digite =Pesquisa!$G$19

Senac São Paulo

249

Excel 2010 – Avançado

ANOTAÇÕES

250

Senac São Paulo

Excel 2010 – Avançado

Atividade 5 – Criar macro Veículos Objetivo :

• Criar macro para filtrar dados do veículo selecionado.

Tarefa

• Criar macro utilizando o recurso do filtro avançado.

Crie uma macro que utilize o recurso filtro avançado com o intervalo de critérios indicado na atividade anterior: 1. Na planilha Origem dos Dados selecione o intervalo A1:I2 e dê o nome de Criterio. 2. Vá para a planilha Pesquisa e na célula I1 digite Resultado da Pesquisa. 3. Selecione a célula I2 copie o intervalo A1:I1 da planilha Origem dos Dados. 4. Selecione I2:Q2 da planilha Pesquisa e dê o nome de Resultado. 5. Na guia Desenvolvedor, clique em Gravar macro do grupo Código. 6. Preencha o Nome da Macro: com Veiculos e descreva o que ela fará no campo Descrição.

7. Tecle OK. A gravação da macro inicia. 8. Selecione a célula K6. 9. Na guia Dados, escolha Avançado do grupo Classificar e Filtrar, e marque a opção Copiar para outro local. 10. Preencha Intervalo da lista com tabela. 11. Preencha Intervalo de critérios com Criterio. 12. Em Copiar para digite resultado.

Senac São Paulo

251

Excel 2010 – Avançado

13. Clique em OK. 14. Selecione uma célula vazia. 15. Na guia Desenvolvedor, escolha Parar gravação do grupo Código.

Caso você esqueça de parar a gravação, os comandos que você utilizar no Excel continuarão a serem gravados. Crie um Botão para executar a macro criada: 16. Na guia Desenvolvedor, clique no comando Inserir. 17. Escolha Botão e determine suas dimensões através de um retângulo que ocupe as células D14:E15.

18. Surgirá a caixa de Atribuir Macro. 19. Escolha a macro Veiculos e tecle OK.

20. Mantendo o botão selecionado digite Pesquisa para definir o nome da face do botão. 21. Selecione o texto digitado e clique com o botão direito do mouse sobre o botão e escolha Formatar Controle. 22. Formate a fonte como desejar. 23. Faça algumas escolhas na guia Pesquisa e teste o funcionamento do botão Pesquisa. No caso do resultado não apresentar nenhum veículo é provável que não exista nenhum veículo com as características escolhidas.

252

Senac São Paulo

Excel 2010 – Avançado

Atividade 6 – Controles para cálculos Objetivo :

• Usar os valores de retorno dos controles para efetuar cálculos.

Tarefa

• Formular hipóteses de financiamento de veículo.

Você introduzirá uma barra de rolagem em que a posição do cursor deslizante represente uma taxa de juros desejada. 1. Clique em Inserir no grupo Controles da guia Desenvolvedor. 2. Clique no controle Barra de rolagem e desenhe um retângulo com o tamanho aproximado do intervalo D21:E21. A Barra de rolagem permite que o uma célula receba um valor entre um mínimo e um máximo. Utilizaremos a barra de rolagem para simular uma taxa de juros do financiamento do veículo variando de 0 a 10%. O retorno da barra de rolagem é um número inteiro que pode variar de 0 a 33.000. 3. Clique em Propriedades do grupo Controles da guia Desenvolvedor. 4. Informe 0 no Campo Valor Mínimo: que é o menor valor que a barra de rolagem assuma quando estiver com o cursor mais à esquerda. 5. Informe 1000 para Valor máximo: que é o valor máximo quando o cursor estiver à direita. A taxa de juros terá incrementos de 0,25%, isto é, assumirá valores de 0%, 0,25%, 0,50%, 0,75% e assim por diante até 10,00%. A barra de rolagem só retorna números inteiros. Por isso, devemos transformar o número devolvido em porcentagem. Faremos com que o número 1 represente 0,01%, o número 25 represente 0,25% e o número 100 represente 1%. Como a taxa máxima é 10%, ela será representada por 1.000. Portanto, este é o valor que informamos para Valor máximo:. 6. Informe 25 para Valor incremental:. Este indica quantas unidades o cursor deslizante se deslocará quando clicamos nos botões laterais. 7. Informe 100 para Mudança de página:. Este valor determina quantas unidades o cursor se deslocará quando clicamos nos espaços entre o cursor e o botão lateral. 8. Indique a célula G21 para receber o valor que representa a posição do cursor na Barra de rolagem.

Senac São Paulo

253

Excel 2010 – Avançado

9. Clique em OK. 10. Digite 0% em D22 e 10% em E22. 11. Alinhe a fonte da célula D22 à esquerda. 12. Digite Juros: em B21. 13. Em C21 digite a fórmula =G21/10000 Formate como porcentagem e defina 2 casas decimais. Isto é necessário para que 25 represente 0,25% (que corresponde a 0,0025), 50 represente 0,50% (0,005) e 100 represente 1,00% (1). 14. Clique nos botões laterais e no espaço entre o cursor e os botões para testar o funcionamento da barra de rolagem.

Você introduzirá o controle Botão de Rotação para definir o período de financiamento que pode ser 12, 24, 36, 48 ou 60 meses. 15. Clique em Inserir do grupo Controles da guia Desenvolvedor. 16. Clique no controle Botão de Rotação.

17. Desenhe o controle conforme a figura abaixo:

18. Com o controle selecionado clique em Propriedades. 19. Preencha Valor mínimo: com 0. 20. Preencha Valor máximo: com 60. 21. Preencha Alteração incremental: com 12.

254

Senac São Paulo

Excel 2010 – Avançado

22. Preencha Vínculo da célula: com C24.

23. Clique em OK. 24. Digite Prazo: na célula B24.

25. Digite Valor Financiado: em B26. 26. Digite Valor da Parcela: em B27. 27. Aplique bordas nas células C26 e C27.

28. Na célula C27 digite a fórmula para cálculo da parcela =PGTO(C21;C24;-C26). 29. Digite um valor na célula C26 e use a barra de rolagem e o botão de rotação para simular taxas e prazos. 30. Oculte as colunas G e H pois seus dados não precisam ser exibidos, retire as linhas de grade e aplique outras formatações que julgar interessante.

Senac São Paulo

255

Excel 2010 – Avançado

Exercício Proposto 1. Abra a pasta de trabalho Automação-exercício.xlsx. 2. Na planilha Consulta crie uma Caixa de combinação que apresente as subprefeituras da planilha Subprefeituras (intervalo Subprefeituras!B3:B33). Desenhe o controle ocupando aproximadamente o tamanho da célula A2. 3. Defina como Vínculo da célula: a célula A2. Teste o funcionamento. Devem surgir os dados de área e população da subprefeitura selecionada.

256

Senac São Paulo