Apostila excel by CURSOS DO CENDEGE

EXCEL 2013 AVANÇADO

Serviço Nacional de Aprendizagem Comercial Departamento Regional na Paraíba

Senac - Serviço Nacional de Aprendizagem Comercial

Presidente do Conselho Regional José Marconi Medeiros de Souza

Departamento Regional Diretor Regional Geraldo Bezerra Veras Diretora Financeira Maria de Fátima Maciel Diretora de Planejamento e Orçamento Indira Toscano Brandão Diretora de Educação Profissional Vera Lúcia da Silva

Elaboração Fábio Ramos Martins Editoração Eletrônica, Revisão e Produção Gráfica Setor de Produção e Utilização de Meios Instrucionais – SPUMI Criação e Arte Setor de Marketing e Comunicação

SENAC. DR/PB. Segmento Informática; Excel Avançado. Fábio Ramos Martins. João Pessoa: Senac/DR/PB, 2014, 93 p. Inclui Bibliografia. Atualizada por Adelson de Oliveira Barreto e Francisco Xavier da Costa. João Pessoa: Senac/DR/PB, 2017, 93 p.

Av. Dom Pedro I, 389 - Centro Tel.: (83) 3214-2330 - CEP: 58013-020 - João Pessoa - Paraíba E-mail: senac@pb.senac.br - Home page: www.pb.senac.br Impresso no SPUMI – IN_AP_067

Apresentação

O Excel 2013, desenvolvido pela Microsoft Corporation para o pacote Office, é um aplicativo que permite a criação e a manipulação de planilhas de cálculos, bem como a incorporação de gráficos e mapas às mesmas. Assim, o usuário pode criar planilhas para cálculos de orçamentos, previsões e planejamentos para investimentos futuros, tabelas variadas, controle de gastos pessoais ou da empresa, controle de caixa, entre outras planilhas que envolvam cálculos matemáticos, desde os mais simples aos mais complexos. Além disso, o Excel 2013 permite ao usuário formatar suas planilhas da maneira que melhor lhe for útil, possibilitando a aplicação de estilos de letras, tamanhos, cores e uma série de outras características especiais. Na área dos gráficos, o usuário pode contar com excelentes tipos e subtipos disponibilizados pelo Excel 2013. Os gráficos, que são de fácil criação, podem ajudar a melhor representar as informações das planilhas. Por fim, podemos afirmar que os recursos do gráfico nas planilhas do Excel 2013 são de suma importância para o desenvolvimento de nossos trabalhos.

Sumário Capítulo 1 - Funções Matemáticas, Estatísticas, Lógicas e de Datas ............................................. 7 ATIVIDADE 01 – Utilizando Funções Matemáticas ........................................................................................ 7 ATIVIDADE 02 – Utilizando Funções Estatísticas ........................................................................................... 9 ATIVIDADE 03 – Utilizando Funções Lógicas ............................................................................................... 12 ATIVIDADE 04 – Manipulando Datas e Horas .............................................................................................. 16

Capítulo 2 - Funções de Pesquisa e Auditoria de Fórmulas ........................................................ 22 ATIVIDADE 05 – Utilizando as funções PROCV, PROCH, CORRESP, ÍNDICE e DESLOC ................................ 23

Capítulo 3 - Funções de Textos ................................................................................................. 25 ATIVIDADE 06 – Utilizando as funções ARRUMAR, MAIÚSCULA, MINÚSCULA, PRI.MAIÚSCULA, ESQUERDA, DIREITA e EXT.TEXTO ............................................................................................................... 25

Capítulo 4 - Funções de Banco de Dados ................................................................................... 27 ATIVIDADE 07 – Utilizando as Funções BDSOMA, BDMÉDIA, BDMÁX, BDMÍN, BDEXTRAIR e BDCONTARA ..................................................................................................................................................................... 27

Capítulo 5 - Importação de Dados para o Excel ......................................................................... 31 ATIVIDADE 08 – Importando arquivos de texto para Excel ......................................................................... 31 ATIVIDADE 09 – Vinculando dados do Excel no Access ............................................................................... 36

Capítulo 6 - Filtro, Classificação e Filtro Avançado ..................................................................... 37 ATIVIDADE 10 – Aplicando o AutoFiltro ...................................................................................................... 37 ATIVIDADE 11 – Classificação Avançada...................................................................................................... 40 ATIVIDADE 12 – Classificação por Lista Personalizada ................................................................................ 41 ATIVIDADE 13 – Classificação por Cores...................................................................................................... 42 ATIVIDADE 14 – Filtro Avançado ................................................................................................................. 43 ATIVIDADE 15 – Filtro Avançado com Critérios Múltiplos........................................................................... 46

Capítulo 7 - Visualização de Dados ............................................................................................ 47 ATIVIDADE 16 – Criação de Minigráficos ..................................................................................................... 47 ATIVIDADE 17 – Alteração e Formação de Minigráficos ............................................................................. 48

Capítulo 8 - Tabela Dinâmica .................................................................................................... 49 ATIVIDADE 18 – Atualização e Inclusão de Dados ....................................................................................... 51 ATIVIDADE 19 – Criação de Outros Níveis ................................................................................................... 52 ATIVIDADE 20 – Formatando a Tabela Dinâmica ........................................................................................ 53 ATIVIDADE 21 – Agrupar Itens ..................................................................................................................... 54 ATIVIDADE 22 – Aplicar Filtro de Relatório ................................................................................................. 55 ATIVIDADE 23 – Agrupar Campos por Períodos .......................................................................................... 56 ATIVIDADE 24 – Segmentação de Dados ..................................................................................................... 57 ATIVIDADE 25 – Gráfico Dinâmico ............................................................................................................... 58

Capítulo 9 - Resumindo Dados .................................................................................................. 60 ATIVIDADE 26 – Consolidação de Dados ..................................................................................................... 61 ATIVIDADE 27 – Consolidação de Dados com Vínculo ................................................................................ 62 ATIVIDADE 28 – Estrutura de Tópicos ......................................................................................................... 64

Capítulo 10 - Validação de Dados .............................................................................................. 66 ATIVIDADE 29 – Criar Regras de Validação.................................................................................................. 66

Capítulo 11 - Trabalhando com Pastas e Proteção ..................................................................... 70 ATIVIDADE 30 – Protegendo Planilhas ........................................................................................................ 70

Capítulo 12 - Simulações .......................................................................................................... 73 ATIVIDADE 31 – Trabalhando com Metas ................................................................................................... 73 ATIVIDADE 32 – Solver................................................................................................................................. 74

Capítulo 13 - Macros Interativas ............................................................................................... 77 ATIVIDADE 33 – Criar Macro ....................................................................................................................... 77 ATIVIDADE 34 – Criar Macro com deslocamentos relativos ....................................................................... 79 ATIVIDADE 35 – Execução e Exclusão de Macros ........................................................................................ 81

Capítulo 14 - Automação de Planilhas ....................................................................................... 83 ATIVIDADE 36 – Criar Controles para escolha de dados ............................................................................. 83 ATIVIDADE 37 – Criar Controles complementares ...................................................................................... 86 ATIVIDADE 38 – Macro Veicular .................................................................................................................. 87

Capítulo 15 - Funções Financeiras ............................................................................................. 89 Capítulo 16 - Funções inseridas na Versão 2016 ........................................................................ 93 Bibliografia .............................................................................................................................. 94

Capítulo 1 - Funções Matemáticas, Estatísticas, Lógicas e de Datas 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.

FUNÇÕES MATEMÁTICAS FUNÇÃO

SINTAXE

SOMA

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

MULT

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

SOMARPRODUTO SOMASE SOMASES

SOMARPRODUTO(matriz1; [matriz2]; ...) SOMASE(intervalo; critérios; intervalo_soma) SOMASES(intervalo_soma; intervalo_critério1; critério1; intervalo_critério2; critério2;...)

ATIVIDADE 01 – Utilizando Funções Matemáticas

1. Abra a pasta “Vendas trimestre 1.xlsx”.

2. Realize a multiplicação da Quantidade x Valor Unitário para adquirir o Valor Total 3. Para calcular o Número de itens vendidos siga os passos abaixo:  Clique na célula D19 e digite: = SOMA(  Selecione a região D4:D17.  Você terá a seguinte fórmula na célula: = SOMA(D4:D17) SENAC/DR.PB

4. Total de itens vendidos:  Clique na célula D20 e digite: = SOMARPRODUTO(  Selecione a faixa D4:D17, digite ; (ponto e vírgula)  Selecione a faixa E4:E17  Você terá a seguinte fórmula na célula: =SOMARPRODUTO (D4:D17;E4:E17) 5. Valor Total Vila Mariana:  Clique na célula D21 e digite: = SOMASE(  Selecione a faixa A4:A17, digite ; (ponto e vírgula).  Digite “Vila Mariana”.  Digite ; (ponto e vírgula).  Selecione a faixa F4:F17.  Você terá a seguinte fórmula na célula:=SOMASE(A4:A17;"Vila Mariana";F4:F17) 6. Valor Total Brooklin:  Digite a fórmula: =SOMASE(A4:A17;"Brooklin";F4:F17) 7. Valor Total Janeiro:  Digite a fórmula: =SOMASE(B4:B17;"Janeiro";F4:F17) 8. Seguinte o item anterior calcule os valores de Fevereiro e Março. 9. Valor Vila Mariana em Fevereiro:  Clique na célula D26 e digite: =SOMASES(  Selecione a faixa F4:F17, digite ; (ponto e vírgula).  Selecione a faixa A4:A17, digite ; (ponto e vírgula).  Digite “Vila Mariana”.  Digite ; (ponto e vírgula).  Selecione a faixa B4:B17, digite ; (ponto e vírgula).  Digite “Fevereiro”.  A fórmula será : =SOMASES(F4:F17;A4:A17;"vila mariana";B4:B17;"fevereiro") 10. Valor Blooklin em Janeiro:  Repita os passos da questão anterior, alterando apenas os nomes da Filial e do Mês

FUNÇÕES ESTATÍSTICAS FUNÇÃO MÁXIMO

MÁXIMO(núm1;núm2;...)

MÍNIMO

MÍNIMO (núm1;núm2;...)

MÉDIA

MÉDIA(núm1;núm2;...)

ORDEM.EQ

CONT.VALORES

SINTAXE

SENAC/DR.PB

ORDEM.EQ(número; referência; [ordem]) CONT.VALORES(intervalo1; [intervalo2]; ...)

EXPLICAÇÃO Essa função retorna o valor máximo entre um conjunto de valores numéricos informado. Essa função retorna o valor mínimo entre um conjunto de valores numéricos informado. Essa função retorna a média aritmética entre um conjunto de valores numéricos informado. Essa função retorna a posição de um número em uma lista de números. 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.

CONTAR.VAZIO CONTAR.VAZIO(intervalo)

CONT.NUM

CONT.SE

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

CONT.SE(intervalo; critério)

CONT.SES

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

MÉDIASE

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

MÉDIASES

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

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. 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. Essa função conta o número de ocorrências de uma determinada condição em um intervalo de células informado. 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. 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. A função MÉDIASES tem como objetivo calcular a média aritmética em um intervalo atendendo a vários critérios.

ATIVIDADE 02 – Utilizando Funções Estatísticas

1. Abra a pasta Prêmio.xlsx.  Selecione a planilha RELATÓRIO e clique na célula B2.  Para calcular o maior valor da venda digite: = MÁXIMO(  Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22.  Pressione a tecla ENTER 2. Para calcular o menor valor da venda digite: = MÍNIMO(  Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22.  Pressione a tecla ENTER 3. Selecione a planilha VENDAS e clique na célula D3 para calcularmos a colocação do vendedor.  Digite a fórmula: = ORDEM.EQ(C3; C3: C22) Obs1 É necessário selecionar o intervalo C3:C22 e fixar a fórmula, pressionando a tecla F4, pois na cópia para as células de baixo essa faixa deve continuar sendo a mesma.

 Após seguir os passos acima pressione a tecla ENTER e copie a fórmula até a célula D22. Obs2 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.

SENAC/DR.PB

4. Selecione a planilha RELATÓRIO e clique na célula B7 para calcularmos o número total de vendedores  Digite: =CONT.VALORES(  Clique na Planilha Vendas e selecione a faixa de células A3:A22.  Tecle ENTER e você terá a fórmula: =CONT.VALORES(VENDAS!A3:A22) 5. Vendedores que não efetuaram vendas, ou seja, quantas são as células vazias.  Selecione a planilha RELATÓRIO e clique na célula B8.  Digite: =CONTAR.VAZIO(  Clique na Planilha Vendas e selecione a faixa de células C3:C22.  Tecle ENTER e você terá a fórmula: =CONTAR.VAZIO(VENDAS!C3:C22) 6. Vendedores que efetuaram vendas, ou seja, quantas são as células preenchidas com números.  Selecione a planilha RELATÓRIO e clique na célula B9.  Digite: =CONT.NÚM(  Clique na Planilha Vendas e selecione a faixa de células C3:C22.  Tecle ENTER e você terá a fórmula: =CONT.NÚM(VENDAS!C3:C22) 7. Agora iremos contar quantos vendedores f o r a m ganhadores do prêmio.  Selecione a planilha RELATÓRIO e clique na célula B10.  Digite: =CONT.SE(  Clique na planilha VENDAS e selecione o intervalo D3:D22.  Digite ; (ponto e vírgula).  Digite 1  Tecle ENTER e você terá a fórmula: = CONT.SE(VENDAS!D3:D22;1) 8. Agora iremos trabalhar com o departamento de Informática, para iniciar iremos contar o número de vendedores do setor.  Clique na célula E2 da planilha RELATÓRIO.  Digite a fórmula: = CONT.SE(VENDAS!B3:B22;"Informática") a) Você vai calcular quantos são os vendedores ganhadores do prêmio do departamento de Informática.  Selecione a planilha RELATÓRIO e clique na célula E3.  Digite: =CONT.SES(  Clique na planilha VENDAS e selecione o intervalo B3:B22.  Digite ; (ponto e vírgula)  Na planilha RELATÓRIO, clique na célula D1. Digite ; (ponto e vírgula).  Selecione a planilha VENDAS e o intervalo D3:D22.  Digite ; (ponto e vírgula) e digite 1.  Tecle ENTER e teremos a seguinte fórmula: = CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1) b) Agora iremos calcular 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. =MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22) c) Calcular a média dos que não são ganhadores do prêmio. Selecione a 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")

SENAC/DR.PB

9. 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. No final você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO:

FUNÇÕES LÓGICAS FUNÇÃO

E OU SEERRO

SINTAXE SE(teste_lógico; Valor_se_verdadeiro; valor_se_falso)

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

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

SEERRO(fórmula; valor_se_erro)

EXPLICAÇÃO 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. Essa função retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros. Essa função retorna o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro. 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. SENAC/DR.PB

ATIVIDADE 03 – Utilizando Funções Lógicas

Tarefas: • Iremos continuar com o mesmo arquivo PRÊMIO para execução das tarefas seguintes • Iremos 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.

1. Com o arquivo PRÊMIO aberto, selecione a planilha VENDAS

TRABALHANDO COM A FUNÇÃO SE NOMENCLATURA DOS SINAIS MAIOR MENOR ENTÃO OU SENÃO VEZES OU SOBRE IGUAL

SINAL

> < ; * =

Observação: Todas as vezes que na situação for pronunciado um SE o usuário deverá colocar esse SE, abrir um parênteses e no final Fechá-lo. VAMOS SEGUIR É A SITUAÇÃO ABAIXO: SE classificação = 1 ENTÃO dividir o valor do prêmio pelo número de ganhadores SENÃO mostrar o valor 0 na célula  Clique na célula E3 e digite:=SE(D3=1;B1/RELATÓRIO!B10;0) Observação Lembre-se de congelar a célula B1 e B10 pressionando F4, para que o valor seja fixado.

 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. 2. Agora você vai colocar uma mensagem para cada vendedor de acordo com os seguintes critérios: CONCEITO EXCELENTE ÓTIMO BOM PODE MELHORAR

SENAC/DR.PB

CONDIÇÃO Colocação do vendedor igual a 1 Valor das vendas do vendedor maior do que a meta Valor das vendas do vendedor igual à meta Nenhuma das condições anteriores

VAMOS SEGUIR É A SITUAÇÃO ABAIXO: SE colocação do vendedor = 1 ENTÃO “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 “BOM” SENÃO “PODE MELHORAR”  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")))

 Copie a fórmula para todos os vendedores. A empresa r e s o l v e u premiar também o s 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. VAMOS SEGUIR É A SITUAÇÃO ABAIXO: SE conceito igual ÓTIMO ENTÃO “Viagem para Natal” SENÃO SE vendas for menor ou igual meta ENTÃO “Jantar” SENÃO VAZIO  Clique na célula G3 e digite o seguinte: = SE(F3="ÓTIMO";"Viagem para Natal";SE(C3<=D1;"Jantar";””))  Copie a fórmula para todos os vendedores. 3. 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);"")

 Feche a pasta Prêmio.xlsx.

SENAC/DR.PB

4. Abra a pasta Estado civil.xlsx.  iremos vai verificar a diferença dos resultados entre as funções E e OU.

Obs 1 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.

 Digite a fórmula: = E(B3="Solteiro(a)";C3>=22)  Copie a fórmula para todas as pessoas. Obs 2 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.

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

 Feche a pasta Estado civil.xlsx 14

SENAC/DR.PB

FUNÇÕES DE DATAS FUNÇÃO

SINTAXE

HOJE

HOJE()

ANO

MÊS

DIA

DIA.DA.SEMANA

DIATRABALHO.INTL

DIATRABALHOTOTAL.INTL

HORA MINUTO FORMATAÇÃO CONDICIONAL

EXPLICAÇÃO

A função HOJE retorna a data de hoje formatada como data. Essa função retorna o ano da data informada. O ano é ANO(data_informada) retornado como um inteiro no intervalo de 1900 a 9999 Essa função retorna o número do mês da data MÊS(data_informada) informada. O mês é retornado como um inteiro no intervalo de 1 a 12. Essa função retorna o número do dia da semana da data informada. O dia é DIA(data_informada) retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. Essa função retorna o número do dia da semana DIA.DA.SEMANA(data_informada da data informada. O dia é ; tipo) retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. Essa função retorna uma data final a projetar a partir de uma data inicial e dias DIATRABALHO.INTL(data_inicial; úteis informados, dias; fim_de_semana; feriados) considerando como é calculado o fim de semana e os feriados que possam ocorrer no período. Retorna o número de dias úteis a projetar entre duas DIATRABALHO.INTL(data_inicial; datas informadas, data_final; [fim_de_semana]; considerando como é [feriados]) calculado os finais de semana e feriados que possam ocorrer no período. Essa função retorna a hora HORA(valor_tempo) de um valor de tempo Essa função retorna os MINUTO(valor_tempo) minutos de um valor de tempo. 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 satisfaçam critérios estabelecidos sejam exibidos.

SENAC/DR.PB

ATIVIDADE 04 – Manipulando 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 2013 oferece várias funções e opções de formatação que podem ser aplicadas para as mais variadas necessidades. 1. Abra a pasta “Datas Formato.xlsx “.  Copie o conteúdo da coluna A para as demais colunas. Para 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.  Na guia Página Inicial, grupo Número, clique na seta do comando Formato de número.

 Selecione Geral. 2. Para mostrar o nome do dia da semana da data armazenada. Selecione o intervalo C2:C32.  Clique na seta do grupo Número da guia Página Inicial.

SENAC/DR.PB

 Na guia Número, selecione a categoria Personalizado.

 Apague o que está no campo Tipo e digite dddd.

 Clique em OK.

3. Agora para exibir o nome do mês. Selecione o intervalo D2:D32.    

Clique na seta do grupo Número da guia PÁGINA INICIAL. Na guia Número, selecione a categoria PERSONALIZADO. Apague o que está no campo TIPO e digite mmmm. Clique em OK.

4. 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.  No campo TIPO digite o seguinte: Explicação: dddd", "dd" de "mmmm" de "aaa 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

 Clique em OK. 5. Abra a pasta “Datas Funções.xlsx”.

 Clique na célula B1 e digite: = HOJE()  Clique na célula B2 e digite: = DIA(B1)  Clique na célula B3 e digite: = MÊS(B1) SENAC/DR.PB

 Clique na célula B4 e digite: = ANO(B1)  Clique na célula B5 e digite: = DIA.DA.SEMANA(B1) Obs Consideramos 1 como domingo e 7 como sábado.

6. Abra a pasta Caixa.xlsx. 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.  Clique na célula B3 e digite a data 01/04/2010.  Formate a célula B3 para que apareça somente o nome do mês. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março.  Clique na célula A6 e digite: = DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A) Explicação Como se deseja obter o primeiro dia útil a partir dessa data, digite 1; 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; caso seja inserido algum feriado não previsto.

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)  Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2010.

 Feche a pasta Caixa.xlsx. 7. Abra a pasta Pagamento Estagiarios.xlsx. 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.

 Selecione a célula F4 e digite: = D4-C4  Copie a fórmula para todos os estagiários.  Clique na célula G4 e digite a fórmula: = DIATRABALHOTOTAL.INTL(C4;D4;1;Feriados!A:A)  Copie a fórmula para todos os estagiários. Explicação: 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. 18

SENAC/DR.PB

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

 O valor pago por hora está armazenado na célula C14.  Clique na célula H4 e digite a fórmula: = E4*G4*C14 Observação Lembre-se de congelar a célula C14 pressionando F4, para que o valor seja fixado.

 Copie a fórmula para todos os estagiários.

8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Então solicitou 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:

 Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor aparecer uma data, formate os valores como geral.  Clique na célula F4 e digite a fórmula: = D4+E4  Copie a fórmula para todos os estagiários.

SENAC/DR.PB

9. Utilizando as fórmulas do item 7 faça novamente o CÁLCULO DOS DIAS DE VIGÊNCIA DO CONTRATO e d os DIAS A TRABALHAR NO PERÍODO.

 Feche e pasta Pagamento Estagiários.xlsx. 10. 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 de trabalho nos dias úteis de fevereiro de 2010 e do valor que deve receber por hora.

 Clique na célula B2 e digite: 01/02/2010  Formate a célula para aparecer o nome do mês.  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.  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.  Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana.  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.  Copie a fórmula para todo o intervalo. 20

SENAC/DR.PB

11. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a seguinte fórmula:  =HORA(E5)  Copie a fórmula para todos os dias. Se o valor para o número de horas estiver aparecendo como um valor do tipo hora, formate para que seja mostrado como número.  Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite a seguinte fórmula:  =MINUTO(E5)  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.  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  Copie a fórmula para todos os dias.  Totalize o valor a pagar na célular I24. 12. 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~e deve utilizar a função DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23.  Na Página Inicial, grupo Estilo, clique em Formatação Condicional e no menu apreentado, selecione Nova Regra.

 Na janela exibida, selecione Usar uma fórmula para determinar quais células devem ser formatadas.  No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula: =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 o tipo foi passado como 1, ou seja, domingo é o dia 1, segunda-feira é o dia 2 e assim por diante.

 Clique no botão Formatar, escolha a cor vermelha e negrito.  Clique em OK e novamente em Ok.  Feche a pasta Atividades e horas.xlsx SENAC/DR.PB

Capítulo 2 - Funções de Pesquisa e Auditoria de Fórmulas OBJETIVOS • Utilizar funções de pesquisa PROCV, PROCH, CORRESP, ÍNDICE e DESLOC • Executar auditoria em fórmulas.

FUNÇÕES DE PESQUISA FUNÇÃO SINTAXE

PROCV(valor_procurado;intervalo_células; PROCV

núm_coluna,[procurar_intervalo])

PROCH(valor_procurado;intervalo_células;núm_linha, PROCH

[procurar_intervalo])

CORRESP

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

ÍNDICE

ÍNDICE(intervalo;linha;[coluna])

DESLOC

DESLOC(ref; lins; col; [altura]; [largura])

SENAC/DR.PB

EXPLICAÇÃO 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. 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. Essa função traz como resultado a posição em que o valor procurado encontrase no intervalo de células indicado, que deve ser formado por uma linha de células ou por uma coluna de células. 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. Retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de célula. Você pode especificar um número de linhas e colunas a serem retornadas.

ATIVIDADE 05 – Utilizando as funções PROCV, PROCH, CORRESP, ÍNDICE e DESLOC Tarefas: • 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. 1. Abra a pasta Relatório de Vendas.xlsx. Selecione a planilha Relatório.  Para preencher a coluna NOME DO FUNCIONÁRIO iremos procura-los na planilha Suporte.  Clique na célula B2 e digite: = PROCV(A2;  Clique a planilha Suporte, selecione a região A2:B23.  A fórmula fica da seguinte forma: = PROCV(A2; Suporte!A2:B23;  Digite 2 e pressione a tecla ENTER. Obs 1 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) Obs 2 Lembre-se de congelar o Intervalo A2:B3 pressionando F4, para que o valor seja fixado.

 Copie a fórmula para todos os funcionários. 2. Agora, calcule o valor da comissão, salário fixo e impostos para cada funcionário.  Selecione a planilha Suporte e selecione o intervalo E2:H7.  Clique na guia Fórmulas, Opção Nomes definidos, Selecione Definir Nome.  Na janela apresentada digite Valor_Vendas e clique em OK.

 Para calcular o valor da comissão iremos utilizar os valores contidos na planilha Suporte.  Selecione a planilha Relatório.  Clique na célula D2  Digite a seguinte fórmula: = PROCV(C2;Valor_Vendas;2) * C2  Copie a fórmula para todos os vendedores. 3. Para calcular coluna Salário Fixo iremos utilizar mais uma vez os valores contidos na planilha Suporte.  Clique na célula E2  Digite a fórmula: = PROCV(C2;Valor_Vendas;3) 4. Agora iremos realizar o cálculo do imposto, p ara 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 SENAC/DR.PB

 Digite a fórmula: = PROCV(C2;Valor_Vendas;4) * D2 5. Calcule agora o valor a receber para cada funcionário  Clique na célula G2  Faça o seguinte cálculo: =comissão + salário fixo – imposto. 6. Iremos agora preencher a coluna Classificação faixas  Clique na planilha Suporte e selecione o intervalo de células F10:I11.  Nomeie esse intervalo como Faixas.  Clique na planilha Relatório. Clique na célula H2 e digite a seguinte fórmula: = PROCH(C2;Faixas;2)  Copie a fórmula para todos os vendedores. 7. Agora você vai verificar em que local da planilha se encontram os valores para maior e menor venda.  Selecione a planilha Controle.  Utilizando as funções máximo e mínimo encontre os valores de MAIOR e MENOR venda, em seguida siga os procedimentos abaixo:  Clique na célula E5 e digite: = CORRESP(  Continue a fórmula digitando D5;  Agora você vai informar o intervalo de células onde será procurado o valor da venda.  Digite = CORRESP(D5; Relatório!C2:C23;  Digite 0 e tecle enter Obs Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será 0.

 Repita os mesmos procedimentos para encontrar a posição da menor venda na planilha Relatório.

8. Agora você deve retornar os nomes dos vendedores que efetuaram a maior e menor venda.     

Clique na célula F5 e digite: = ÍNDICE( Digite a fórmula: = ÍNDICE(Relatório!B2:B23; Clique na Planilha Controle e na célula E5 A fórmula completa fica então: = ÍNDICE(Relatório!B2:B23;Controle!E5) Utilize os mesmos procedimentos para trazer o nome do vendedor que efetuou a menor venda.

9. Utilize a função DESLOC e a planilha anterior para retornar a matrícula dos vendedores.      24

Para a maior venda clique na célula G5 e digite a fórmula: = DESLOC( Clique na planilha Relatório e na célula A1 Agora clique na planilha Controle e célula E5 Digite 0 e a fórmula completa ficará: = DESLOC(Relatório!A1;Controle!E5;0) Para a menor venda na célula G6 digite a fórmula completa: =DESLOC(Relatorio!A1;Controle!E6;0)

SENAC/DR.PB

Capítulo 3 - Funções de Textos OBJETIVO • Utilizar funções de texto ARRUMAR, DIREITA, ESQUERDA, EXT.TEXTO, MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA

FUNÇÕES DE TEXTO FUNÇÃO

SINTAXE

ARRUMAR

ARRUMAR(texto)

MAIÚSCULA

MAIÚSCULA(texto).

MINÚSCULA

MINÚSCULA(texto).

PRI.MAIÚSCULA

PRI.MAIÚSCULA(texto)

DIREITA

DIREITA(texto;[número_caracteres])

ESQUERDA

ESQUERDA(texto;[número_caracter es])

EXT.TEXTO

EXT.TEXTO(texto;posição;número_ caracteres)

EXPLICAÇÃO Essa função tem como objetivo eliminar os espaços em branco à esquerda e à direita do texto. Essa função tem como objetivo converter o texto para caracteres maiúsculos. Essa função tem como objetivo converter o texto para caracteres minúsculos. Essa função tem como objetivo colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia de texto. Essa função tem como objetivo retornar os últimos n caracteres à direita de uma cadeia de texto. Essa função tem como objetivo retornar os primeiros n caracteres à esquerda de uma cadeia de texto. Essa função tem como objetivo retornar n caracteres à partir de uma determinada posição em uma cadeia de texto.

ATIVIDADE 06 – Utilizando as funções ARRUMAR, MAIÚSCULA, MINÚSCULA, PRI.MAIÚSCULA, ESQUERDA, DIREITA e EXT.TEXTO 1. Abra a pasta Rodízio.xlsx.  Selecione a planilha DADOS.  A coluna referente a MARCA é necessário eliminar os espaços em branco no início de cada célula.  Selecione a planilha INTERMEDIÁRIA e clique na célula A2.

 Digite a seguinte fórmula: =ARRUMAR(DADOS!A2)  Copie a fórmula até a célula A18. SENAC/DR.PB

2. A coluna PLACA da planilha DADOS está mostrando as placas dos automóveis com a identificação das letras em caracteres minúsculos.  Selecione a planilha INTERMEDIÁRIA, clique na célula B2  Digite a fórmula: = MAIÚSCULA(DADOS!B2)  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.  Seleciona a planilha INTERMEDIÁRIA, clique na célula C2  Digite: = MINÚSCULA(DADOS!C2)  Copie a fórmula para todos os automóveis. 4. 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  Digite: =PRI.MAIÚSCULA(DADOS!D2)  Copie a fórmula para todos os proprietários. 5. Você vai extrair as letras das chapas dos automóveis.  Clique na célula E2 da planilha INTERMEDIÁRIA  Digite: = ESQUERDA(B2;3)  Copie a fórmula para todos os automóveis. 6. Para extrair as duas últimas letras das placas  Clique na planilha INTERMEDIÁRIA.  Vá até a célula F2 e digite: = EXT.TEXTO(E2;2;2)  Copie a fórmula para todos os automóveis. 7. Extraia o último número das chapas dos automóveis.  Para isso, clique na célula G2 da planilha INTERMEDIÁRIA  Digite: = DIREITA(B2;1)  Copie a fórmula para todos os automóveis. 8. 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.  Na planilha RODÍZIO clique na célula B2  Digite a fórmula = 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)  Na coluna DIA DO RODÍZIO com a fórmula: = PROCV(B5;$I$3:$J$8;2)  Copie a fórmula para todos os automóveis. 10. 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"). Formate condicionalmente essa coluna para que mostre a mensagem “Carro no rodízio” em vermelho.

SENAC/DR.PB

Capítulo 4 - Funções de Banco de Dados OBJETIVO • Utilizar as funções de banco de dados: BDSOMA, BDMÉDIA, BDMÁX, BDMÍN, BDEXTRAIR e BDCONTARA

FUNÇÕES DE BANCO DE DADOS FUNÇÃO

SINTAXE

BDSOMA

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

BDMÉDIA

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

BDMÁX

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

BDMÍN

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

BDEXTRAIR

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

BDCONTARA

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

EXPLICAÇÃO Essa função tem como objetivo somar os valores de uma coluna de acordo com critérios estabelecidos. Essa função tem como objetivo calcular a média aritmética entre os valores de uma coluna de acordo com critérios estabelecidos. Essa função tem como objetivo retornar o valor máximo dentro de uma lista de valores de acordo com as condições especificadas. Essa função tem como objetivo retornar o valor mínimo dentro de uma lista de valores de acordo com as condições especificadas. Essa função tem como objetivo retornar um único valor em uma coluna de uma lista que coincida com as condições especificadas. 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.

ATIVIDADE 07 – Utilizando as Funções BDSOMA, BDMÉDIA, BDMÁX, BDMÍN, BDEXTRAIR e BDCONTARA Tarefas: • 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. 1. Abra a pasta Relatório Produtos.xlsx.  O primeiro passo é somar os valores de venda por fornecedor, para isso, você vai criar seus critérios e utilizá-los com a função BDSOMA.  Selecione a planilha Critérios.  Digite os valores mostrados a seguir: SENAC/DR.PB

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.

 Selecione a planilha Relatório e clique na célula B4. Você vai calcular a soma de vendas para o fornecedor 3 Manos Hipermercados.

      

Digite: = BDSOMA( Clique na planilha Controle de Produtos Selecione a região A1:D24. Digite Ponto e Virgula ; Clique na planilha Controle de Produtos e na Célula D1 Selecione a região A4:A5 contido na Planilha Critérios e tecle enter a fórmula final ficará assim:

= BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1;Critérios!A4:A5)  Complete a coluna SOMA utilizando os critérios com outros fornecedores. 2. 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.  Selecione a célula D1 da planilha Controle de Produtos e atribua o nome Coltotal.  Selecione a região A1:A2 da planilha Critérios e atribua o nome Carretel.  Selecione a região A4:A5 da planilha Critérios e atribua o nome Manos.  Selecione a região A7:A8 da planilha Critérios e atribua o nome Doces.  Selecione a região A10:A11 da planilha Critérios e atribua o nome Flores.  Selecione a planilha Relatório e clique na célula C4. 3. Você vai calcular a média de vendas para o fornecedor 3 Manos Hipermercado.  Digite a fórmula: = BDMÉDIA(Geral;Coltotal;Manos)  Calcule a média de vendas para os outros fornecedores. 4. Agora você vai calcular a soma por fabricante. Na planilha Critérios digite os critérios para os fabricantes:

SENAC/DR.PB

 Nomeie os critérios: garoto, granja, maeterra, nestle, parmalat, renata, royal e união.  Selecione a planilha Relatório e clique na célula B13.  Digite a fórmula: =BDSOMA(Geral;Coltotal;garoto)  Calcule a soma para os outros fabricantes. 5. Seguindo o mesmo raciocínio utilizado para os fornecedores, calcule a média de vendas para os fabricantes.  Selecione a planilha Relatório e clique na célula D4.  Digite a fórmula: = BDMÁX(Geral;Coltotal;Manos)  Calcule o valor máximo de vendas para os outros fornecedores.  Calcule o valor máximo de vendas para os fabricantes. 6. Agora calcule o valor mínimo de vendas para os fornecedores. Clique na célula F4 e digite a fórmula:  Use a Fórmula: = BDMÍN(Geral;Coltotal;Manos)  Calcule o valor mínimo de vendas para os outros fornecedores.  Calcule o valor mínimo de vendas para os fabricantes. 7. Iremos agora 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.  Digite: Total  Selecione a célula B2 e digite: = Relatório!D5.  Repita os procedimentos para os demais Fornecedores

SENAC/DR.PB

8. Selecione a planilha Relatório e clique na célula E4.  Digite a fórmula abaixo: = BDEXTRAIR(Geral;'Controle de Produtos'!A1;Critérios!A4:B5)  Mostre os nomes dos produtos de maior valor para todos os fornecedores.  Modifique a planilha Critérios para que você possa mostrar os nomes dos produtos de menor valor para todos os fornecedores:  Preencha a planilha Relatório procurando pelos produtos de menor valor.  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. 9. Selecione a planilha Relatório.  Clique na célula H4.  Digite na célula H4 a fórmula abaixo: = BDCONTARA(Geral;'Controle de Produtos'!$B$1;Manos)  Preencha o relatório contando o número de produtos fornecedores e fabricantes.

SENAC/DR.PB

para os demais

Capítulo 5 - Importação de Dados para o Excel 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.

ATIVIDADE 08 – Importando arquivos de texto para Excel Objetivo: 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. 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.

 Feche o arquivo Base.txt.  Abra uma nova pasta de trabalho em branco no Excel.  Na guia Dados, grupo Obter Dados Externos, clique em De Texto.

 Localize o arquivo Base.txt e clique em Importar. SENAC/DR.PB

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

Nessa janela escolha 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: largura fixa.  Iniciar importação na linha: O padrão é linha 1.  Origem do Arquivo: É recomendável deixar no padrão que foi colocado.  Clique em Avançar, e a seguinte tela é mostrada:

 Delimitadores: O delimitador utilizado é Tabulação  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.  Clique em Avançar. A janela mostrada permite selecionar cada coluna e definir o formato dos dados. 32

SENAC/DR.PB

 Clique em Concluir.  Na janela mostrada, selecione a célula A1 da planilha para que seja o local inicial da importação.  Clique em OK para terminar.

2. 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

 Feche o arquivo Base.txt salvando as alterações.  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.

 Na janela que se abre selecione o arquivo Base.txt e clique em Importar. SENAC/DR.PB

 Note que os registros foram atualizados. Feche a pasta e salve-a como Base de Dados.xlsx.  Abra novamente a pasta Base de Dados.xlsx.  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.  Feche a pasta. 3. Abra uma nova pasta de trabalho em branco.  Na guia Dados, grupo Obter Dados Externos, clique em De Outras Fontes. No menu apresentado, selecione Do Microsoft Query.

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

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

SENAC/DR.PB

 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.

 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:

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

 Clique em Avançar. Deixe selecionada a opção Retornar dados ao Microsoft Excel e clique em Concluir.  Selecione a célula A1 e clique em OK. Você terá o seguinte resultado:

 Feche a pasta salvando-a com o nome Produtos Doces do Brasil.xlsx. SENAC/DR.PB

ATIVIDADE 09 – Vinculando dados do Excel no Access Objetivo: 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. e siga os passos abaixo  Clique no botão Habilitar Conteúdo.  Na guia Dados Externos, grupo Importar e Vincular, selecione Excel.

 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.  Deixe selecionada a planilha Dados e clique em Avançar.  Selecione A primeira linha contém títulos de coluna e clique em Avançar.  Informe como nome da tabela Dados e clique em Concluir.  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:

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.

SENAC/DR.PB

Capítulo 6 - Filtro, Classificação e Filtro Avançado OBJETIVOS:

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. TAREFAS: • Selecionar dados aplicando AutoFiltro. • Classificar dados por uma ou mais chaves. • Aplicar o Filtro Avançado.

ATIVIDADE 10 – Aplicando o AutoFiltro Objetivo: 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.  Exiba o AutoFiltro. Na guia Dados, no grupo Classificar e Filtrar clique no comando Filtro.

 Clique no botão da coluna Departamento e selecione a opção Selecionar Tudo para desmarcar todas as opções.  Em seguida, marque apenas a opção Telefones e Celulares.

SENAC/DR.PB

 Clique no botão OK.  O resultado é a exibição das linhas onde constam o departamento escolhido. As linhas que correspondem a outros departamentos ficam ocultas.  Para mostrar todas as linhas novamente clique no botão ao lado do nome do campo Departamento, escolha Selecionar Tudo e clique em OK. 2. Podemos reexibir todas as linhas clicando na opção Limpar do grupo Classificar e Filtrar da guia Dados  Aplique o AutoFiltro que apresenta os produtos do departamento Telefones e Celulares.  Clique no botão da coluna Preço.  Escolha a opção Filtros de número e escolha a opção É Menor do Que...

 Digite o limite superior - 600.  Clique em OK.

 Clique em Limpar do grupo Classificar e Filtrar quando desejar exibir todas as linhas.  Clique no botão de filtro do campo Produto.  Escolha em Filtros de Texto e a opção Começa com...

SENAC/DR.PB

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

 Clique em Limpar para restaurar todos os produtos.  Clique no botão de filtro da coluna Descrição.  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. SENAC/DR.PB

 Clique em Limpar para retirar os filtros aplicados.  Clique no botão de filtro da coluna Produto.  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. Obs 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.

ATIVIDADE 11 – Classificação Avançada Objetivo: • Apresentar o recurso de classificação por mais de uma chave. 1. Abra o arquivo Nome.xlsx.  Na planilha Nome, selecione qualquer célula dentro da lista.  Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados.

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

SENAC/DR.PB

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

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

ATIVIDADE 12 – Classificação por Lista Personalizada

Objetivo: • Classificar os dados por uma ordem estabelecida pelo usuário. 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.  Escolha a coluna Cargo e escolha Lista Personalizada na caixa de combinação Ordem.

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

SENAC/DR.PB

 Clique em Adicionar e a lista será criada.

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

ATIVIDADE 13 – Classificação por Cores Objetivo: • Utilizar cores como critério de classificação. 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.  Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados.  Escolha a coluna Produto e Cor da Célula na caixa de combinação Classificar em.

SENAC/DR.PB

Existem as opções Cor da Fonte e Ícone da Célula, neste último caso quando for aplicada a Formatação Condicional.

 Estabeleça a ordem de importância das cores, adicionando nível a cada cor definida.

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

ATIVIDADE 14 – Filtro Avançado Objetivo: • Aplicar filtro utilizando critérios avançados. 1. Abra o arquivo Filtro Avançado.xlsx. Você vai criar uma lista dos departamentos sem repetição.  Selecione a coluna Departamento.  Clique com o botão direito e a opção Copiar ou use qualquer outro método de cópia.

 Selecione a planilha Critérios e a célula A1.  Cole o conteúdo da área de transferência. SENAC/DR.PB

 Mantendo a coluna selecionada, clique no botão Remover Duplicata do grupo Ferramenta de Dados, da guia Dados.  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.

 Clique em OK para concluir.

Obs 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.

 Construa os critérios como abaixo:

 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.

SENAC/DR.PB

   

Para construir as planilhas usando o Filtro Avançado. Selecione todas as linhas da planilha Geral e renomeie este intervalo como Geral. Vá para a planilha Informática. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.

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

 Clique em OK.  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.  Vá para a planilha OUTROS.  Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.  Marque a opção Copiar para outro local.  Preencha Intervalo da lista com Geral.  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. SENAC/DR.PB

 Vá para Copiar para e clique na célula A1 da planilha OUTROS.

 Clique em OK.

ATIVIDADE 15 – Filtro Avançado com Critérios Múltiplos

Objetivo: • Aplicar filtro utilizando critérios avançados combinados. 1. Vá à planilha Critérios, digite Preço na célula B1 e <1200 na célula B2.

OBS 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.      

Insira uma nova planilha. Renomeie como Informática - menor que 1200. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. Marque a opção Copiar para outro local. Preencha Intervalo da lista com Geral. Preencha Intervalo de critérios selecionando o intervalo A1:B2 da planilha Critérios.  Clique em OK.

SENAC/DR.PB

Capítulo 7 - Visualização de Dados OBJETIVO: • Apresentar recursos para exibição e interpretação de dados.

ATIVIDADE 16 – Criação de Minigráficos

Objetivo: • Apresentar dados através de minigráficos. 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.  No grupo Minigráficos da guia Inserir escolha o tipo de minigráfico

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

SENAC/DR.PB

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

ATIVIDADE 17 – Alteração e Formação de Minigráficos Objetivo: • Apresentar os recursos para formatação e alteração dos minigráficos. 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.

 Digite zero na célula G6.  Apague o conteúdo da célula E3 e I4.  Assinale Marcadores no grupo Mostrar da guia Design para destacar os pontos de dados.  Clique na parte inferior do botão Editar Dados do grupo Minigráfico da guia Design.  Clique na opção Células Ocultas e Vazias...  Marque a opção Zero no quadro que aparece para alterar a representação das células vazias no minigráfico. 48

SENAC/DR.PB

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.

 Clique em OK para executar a alteração.  Defina a cor do minigráfico e as características dos marcadores no grupo Estilo da guia Design.

Obs 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.

 Selecione uma célula que contenha o Minigráfico, clique no botão Desagrupar

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

Capítulo 8 - Tabela Dinâmica OBJETIVO: • Resumir dados de planilhas utilizando a Tabela Dinâmica. 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.  Clique no botão Tabela Dinâmica do grupo Tabelas da guia Inserir.

SENAC/DR.PB

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

 Clique em OK.

 À direita surge o Painel de Tarefas Lista de Campos da Tabela Dinâmica. Observações Importantes 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.

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

SENAC/DR.PB

ATIVIDADE 18 – Atualização e Inclusão de Dados Objetivo: •

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

• 1. Torne a célula A13 como célula ativa na planilha Dados.  Insira uma nova linha.  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á.

 Volte à Tabela Dinâmica. Este novo projeto não foi incluído.  Clique no botão Atualizar do grupo Dados

 Clique no botão Classificar para ordenar os projetos.  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 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.  Volte à Tabela Dinâmica.  Clique em Alterar Fonte de Dados do grupo Dados da guia Analisar.

 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. SENAC/DR.PB

 Clique em OK

ATIVIDADE 19 – Criação de Outros Níveis Objetivo: 

Acrescentar subníveis à Tabela Dinâmica.

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.

   

Selecione a célula de qualquer subtotal e clique em Configurações do Campo Marque a opção Nenhum para ocultar os subtotais. Clique em OK. Na coluna Soma de Valor, dê um duplo clique na linha correspondente a um 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.

2. 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.  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. 52

SENAC/DR.PB

ATIVIDADE 20 – Formatando a Tabela Dinâmica

Objetivo: 

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

1. Escolha e selecione o Estilo desejado na guia Design, no grupo Estilos de Tabela Dinâmica.

 Na guia Analisar, no grupo Campo Ativo, clique em Configurações do Campo.

 Clique em Formato do Número e escolha Moeda. SENAC/DR.PB

 Clique em OK. Este será o resultado.

ATIVIDADE 21 – Agrupar Itens Objetivo: 

Formar grupos de itens.

1. Crie uma nova Tabela Dinâmica.  Volte à planilha Dados, clique em Tabela Dinâmica  Selecione os campos Estado, Projeto e Valor.

 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).  Clique em Seleção de Grupo

SENAC/DR.PB

    

Digite o nome da região, Sudeste, sobre Agrupar1. Selecione Paraná e Santa Catarina. Clique em Seleção de Grupo do grupo Agrupar da guia Analisar. Digite Sul onde aparece Agrupar2. Digite Centro-Oeste no grupo Goiás. Selecione os grupos e clique em Desagrupar para desfazer os grupos.

ATIVIDADE 22 – Aplicar Filtro de Relatório

Objetivo: 

Selecionar os dados a serem visualizados.

1. Crie outra Tabela Dinâmica em uma nova planilha.  Escolha os campos Projeto, Estado e Valor.  Arraste o campo Estado para a área de Filtros.

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

SENAC/DR.PB

 Clique no Estado que quer exibir. Os projetos e respectivos valores dos Estados não selecionados ficarão ocultos. Marque a caixa de verificação Selecionar Vários Itens para exibir mais de um item por vez.

ATIVIDADE 23 – Agrupar Campos por Períodos

Objetivo: 

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

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

SENAC/DR.PB

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

Surge uma coluna para cada m ês ou data relacionada no campo Data. Você vai agrupar estes períodos para que tenham significado para quem analisa.

 Selecione o primeiro período (jan). Clique em Agrupar Campo do grupo Agrupar da guia Analisar.

 Clique em Anos, caso não esteja marcado. Obs 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.

 Clique em OK.

ATIVIDADE 24 – Segmentação de Dados Objetivo: 

Aplicar filtros aprimorados em tabelas dinâmicas. SENAC/DR.PB

1. Ainda na tabela dinâmica da atividade anterior, clique no botão Segmentação de Dados.  Marque os campos Projeto e Estado e clique em OK.  Posicione as Segmentações de Dados. Todos os itens aparecem com cor destacada indicando que nenhum filtro foi aplicado ainda.  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.

 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.

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

ATIVIDADE 25 – Gráfico Dinâmico

Objetivo: 

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

SENAC/DR.PB

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

Você também pode criar o gráfico com a tecla de função F11.

 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.

 Clique no botão Alternar Linha/Coluna do grupo Dados da guia Design de Ferramentas de Gráfico Dinâmico para que o eixo X apresente os meses e os anos.

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

SENAC/DR.PB

Capítulo 9 - Resumindo Dados Objetivo: 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/DR.PB

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

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

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

 Clique em Adicionar.  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.

2. Clique no botão Procurar...  Localize o arquivo Movimento-Abril.xlsx.  Depois de selecioná-lo no quadro Procurar, clique em OK.  Clique na tecla de função F2. SENAC/DR.PB

 Clique em qualquer local dentro da caixa de referência para desfazer a seleção.  Tecle End e digite o nome do intervalo que contém os dados: Abril.

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

ATIVIDADE 27 – 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.

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

SENAC/DR.PB

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

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

2. Clique no botão Procurar...       

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

SENAC/DR.PB

 Clique em OK.  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.

ATIVIDADE 28 – Estrutura de Tópicos

Objetivo: 

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

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

 Clique em AutoTópicos.

 Clique nos sinais e números para familiarizar-se com seu funcionamento. 64

SENAC/DR.PB

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.

2. 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.  Selecione as colunas F, G e H.  Clique na parte Inferior do botão Agrupar do grupo Estrutura de Tópicos da guia Dados.  Clique em Agrupar.

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.

 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.

SENAC/DR.PB

Capítulo 10 - Validação de Dados OBJETIVO: 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.

ATIVIDADE 29 – Criar Regras de Validação

Objetivo: 

Determinar quais dados podem ser introduzidos nas planilhas.

1. Abra o arquivo Validação.xlsx.  Selecione a coluna A clicando no botão seletor.

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

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

SENAC/DR.PB

 Clique em OK. Vá para o final da planilha e introduza dados na primeira linha disponível.  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.

 Clique em Repetir e digite uma data válida. 2. Os dados da coluna Projeto devem pertencer a uma lista de projetos já definidos na planilha Projetos.  Clique na planilha Projetos. A lista desta planilha é a que deve ser considerada para a digitação da coluna Projeto.  Selecione a coluna A.  Clique na caixa de nome para definir um nome para o intervalo selecionado.  Digite Projetos e finalize com ENTER.  Volte à planilha Dados.  Selecione a coluna B.  Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados.  No campo Permitir escolha Lista.  No campo Fonte: digite =Projetos.  Desmarque a opção Ignorar em branco.

3. 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.  Preencha os campos Título e Mensagem de entrada.

SENAC/DR.PB

Esta mensagem ficará visível quando a célula for ativada.

 Você também pode substituir o alerta de erro padrão por um alerta de erro personalizado. Clique na guia Alerta de erro.  Preencha o Título e a Mensagem de erro. Tecle ENTER para continuar o texto em outra linha.  Escolha o Estilo do quadro de mensagem.

 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.

SENAC/DR.PB

 Escolha Aviso ou Informações.  Clique em OK. 4. Agora você aplicará a validação de dados à coluna E/S para aceitar os valores Entrada ou Saída.  Selecione a coluna E.  Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados.  Em Permitir: escolha Lista.  No campo Fonte: digite Entrada e Saída separados por ponto e vírgula (;).  Escolha o estilo de alerta de erro e preencha os campos Título e Mensagem de erro.  Clique em OK. 5. 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.  Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados.  Em Permitir: escolha Decimal.  Preencha o campo Mínimo: com 1 e o campo Máximo: com 10000000.  Defina o Alerta de erro e clique em OK.  Complete as informações das colunas restantes.

SENAC/DR.PB

Capítulo 11 - Trabalhando com Pastas e Proteção Objetivo: 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 que não se possa inserir, excluir, mover, renomear, ocultar e reexibir planilhas da pasta, bem como proteger as janelas da pasta de trabalho.

ATIVIDADE 30 – Protegendo Planilhas

1. Clique em Opções da guia Arquivo.  Digite seu nome no campo Nome do Usuário.

 Clique em OK.  Abra o arquivo Movimento-equipe.xlsx. 70

SENAC/DR.PB

 Selecione todos os dados abaixo dos cabeçalhos da planilha Entrada.

 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.  Clique no item Bloquear Célula para desativar esta propriedade para as células que estão selecionadas. Todas as demais permanecerão bloqueadas.

 Para que o bloqueio passe a funcionar clique novamente no botão Formatar e clique em Proteger Planilha...  Marque as operações que deseja permitir para todos os usuários.  Digite uma senha, clique em OK e confirme a senha.  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 em OK e ative a proteção de planilha.

SENAC/DR.PB

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.

 Remova a proteção da planilha. Clique em Desproteger planilha do grupo Células da guia Página Inicial.

 Digite a senha introduzida anteriormente e clique em OK.  Selecione o intervalo A4:G28.  Clique em Bloquear Célula. Esta ação tornará o intervalo de células bloqueado novamente.  Vá para a guia Revisão.  Clique em Permitir que os Usuários Editem Intervalos do grupo Alterações.  Clique em Novo...  Em Título: digite Entradas.  Em Referência a células: informe o intervalo que deseja permitir a edição A4:G28.  Digite uma senha.  Clique em OK. Se cada parte da planilha é de responsabilidade de grupos de usuários diferentes, crie um novo intervalo para cada uma destas partes.

 Após definir todos os intervalos da planilha clique em Proteger planilha... Quando for solicitado, informe a senha de proteção de planilha.  Clique em Ok.  Execute os mesmos procedimentos para criar intervalos de edição para a planilha Saída.

SENAC/DR.PB

Capítulo 12 - Simulações 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.

ATIVIDADE 31 – Trabalhando com Metas

1. Abra o arquivo Metas.xlsx. Obs Nos meses de abril e junho os saldos são negativos, indicando que as somas das Entradas, que representam os valores que a 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.

    

Clique no botão Teste de Hipóteses da guia Dados. Escolha Atingir meta... No quadro Atingir meta informe a célula E3 no campo Definir célula:. Em Para valor: digite 222000 que é o valor a ser alcançado por E3. Defina a célula E7 como célula variável em Alternando célula:.

 Clique em OK.

SENAC/DR.PB

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.

ATIVIDADE 32 – Solver Objetivo: O recurso Solver consiste em um complemento que precisa ser carregado para que você possa usá-lo. Carregue esse complemento. 1. Abra o arquivo Solver.xlsx.    

Clique na guia Arquivo. Clique em Opções. Clique em Suplementos. Certifique-se que no campo Gerenciar: está selecionada a opção Suplementos do Excel e clique no botão Ir...  Marque a opção Solver e clique em OK.

SENAC/DR.PB

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 dos 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

Valor a ser encontrado. Cada passageiro = 77 kg.

<=1.157 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?.  Clique na guia Dados. Agora existe um novo grupo chamado Análise.  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.

     

No campo Definir Objetivo: selecione a célula B7. No campo Alterando células variáveis: selecione o intervalo B2:B6. Clique no botão Adicionar para definir as restrições. No campo Referência de Célula: selecione A11. Escolha o operador =. No campo Restrição: selecione C11.

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

C12

A12

E12

A13

C13

A14

C14

A15

C15 SENAC/DR.PB

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

 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.

 Selecione os relatórios Resposta, Sensibilidade e Limites.  Caso os valores apresentados sejam satisfatórios, marque a opção Manter Solução do Solver. Caso contrário, marque Restaurar Valores Originais.  Clique em OK. Você pode transformar a resposta do Solver em um Cenário. Para isto, clique no botão Salvar Cenário... 76

SENAC/DR.PB

Capítulo 13 - Macros Interativas OBJETIVO: Macro é uma sequê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 33 – Criar Macro 1. Abra a pasta de trabalho Macros.xlsx.  Digite dados de teste na área de entrada: 100 em B9, 200 em B10, 300 em B11, 400 em B12.

 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.

 Digite o nome da macro – “Preenche”, posicione no campo Tecla de atalho:, pressione SHIFT + P para atribuir as teclas de atalho CTRL + SHIFT + P. 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 opção Nova pasta de trabalho permite que você crie uma nova pasta para receber a macro criada. SENAC/DR.PB

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

 Selecione os meses que serão copiados para a esquerda (intervalo C2:G6) e acione o comando Copiar.  Selecione a célula B2 e cole.

 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.  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.  Apague os dados da área de entrada (B9:B12).  Selecione F2:F6, pressione o botão Pincel e copie o formato para a próxima coluna (G2:G6).

 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. 78

SENAC/DR.PB

 A macro está pronta para ser usada. Digite alguns valores na área de entrada e teste a macro.  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)”.

   

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

ATIVIDADE 34 – Criar Macro com deslocamentos relativos

Objetivo: 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. Vá até a planilha Dados    

Inicie a gravação da macro com o nome “Transporta” atribua a combinação de tecla CTRL + SHIFT + T Pressione OK. Copie o intervalo do mês a ser descartado B2:B6. Seu conteúdo ficará armazenado na área de transferência. SENAC/DR.PB

 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).

 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.

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

 Acione o comando Colar. O conteúdo da área de transferência, então, é colado.

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

SENAC/DR.PB

ATIVIDADE 35 – Execução e Exclusão de Macros

Objetivo: 

Trabalhar com 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. Então, aparecerá a tela abaixo:  Criando um botão na barra de ferramentas de acesso rápido ou criando uma guia personalizada na faixa de opções.

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

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

 Selecione o item Personalizar Faixa de Opções.  Clique em Nova Guia e em Renomear.  Digite Executar comandos.

3. Selecione Novo Grupo (Personalizado) e clique em Renomear.  Digite o nome Macros.  Clique em OK. SENAC/DR.PB

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

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

SENAC/DR.PB

Capítulo 14 - Automação de Planilhas OBJETIVOS: 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.

1. 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 título – contém o rótulo do campo a ser retornado ao filtrar os modelos. 

ATIVIDADE 36 – Criar Controles para escolha de dados 

Objetivo: 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. Iremos precisar da Guia Desenvolvedor, se não estiver visualizando-a siga os passos abaixo:     

Acesse a guia Arquivo Escolha Opções. Escolha o comando Personalizar Faixa de Opções Marque a caixa de seleção Desenvolvedor. Clique em OK.

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

SENAC/DR.PB

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

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

 Mantendo o controle selecionado, digite Novos. Em seguida, clique no comando Propriedades do grupo Controles, 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.  Clique em OK.  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. O b s  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. 84

SENAC/DR.PB

 Clique no comando Inserir do grupo Controles  Desenhe o retângulo abaixo da palavra Marca:.

 Mantendo o controle selecionado clique sobre o comando Propriedades.  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.  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.  Na célula G11 digite a função: = ÍNDICE(Marcas!A:A;Pesquisa!G10;1).

Obs Esta função busca o conteúdo da coluna A da planilha Marcas que está na linha representada em G10.

SENAC/DR.PB

ATIVIDADE 37 – Criar Controles complementares

1. Na célula E4 digite a palavra Modelo: e formate.  Acesse a guia Desenvolvedor e introduza uma Caixa de listagem abaixo do texto Modelo:.

 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.

 Clique com o botão direito do mouse para selecionar a caixa de combinação das Marcas.  No menu suspenso escolha Atribuir Macro.  Atribua a macro Modelo, criada na atividade anterior. O b s  Esta associação fará com que os itens da caixa de combinação Modelos se alterem quando escolhermos a Marca.

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

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

SENAC/DR.PB

 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.

 Teste o funcionamento dos controles.

ATIVIDADE 38 – Macro Veicular

Objetivo: • Criar macro para filtrar dados do veículo selecionado. 1. Clique na planilha Origem dos Dados       

Selecione o intervalo A1:I2 Dê o nome de Critério. Vá para a planilha Pesquisa e na célula I1 digite Resultado da Pesquisa. Selecione a célula I2 e copie o intervalo A1:I1 da planilha Origem dos Dados. Selecione I2:Q2 da planilha Pesquisa e dê o nome de Resultado. Na guia Desenvolvedor, clique em Gravar macro do grupo Código. Preencha o Nome da Macro: com Veículos e descreva o que ela fará no campo Descrição.

SENAC/DR.PB

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

 Clique em OK.  Selecione uma célula vazia.  Na guia Desenvolvedor, escolha Parar gravação do grupo Código. Caso você se esqueça de parar a gravação, os comandos que você utilizar no Excel continuarão a ser gravados.

2. Crie um Botão para executar a macro criada:  Na guia Desenvolvedor, clique no comando Inserir.  Escolha Botão e determine suas dimensões através de um retângulo que ocupe as células D14:E15.

 Surgirá a caixa de Atribuir Macro.  Escolha a macro Veículos e tecle em OK.

SENAC/DR.PB

 Mantendo o botão selecionado digite Pesquisa para definir o nome da face do botão.  Selecione o texto digitado, clique com o botão direito do mouse sobre o botão e escolha Formatar. (Controle).  Formate a fonte como desejar.  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.

Capítulo 15 - Funções Financeiras

VP – VALOR PRESENTE É utilizada quando se precisa calcular o valor presente de uma série de pagamentos futuros, seja de um empréstimo ou investimento. Sintaxe= VP(taxa, nper, pgto, [vf], [tipo]) Argumentos: Taxa: Taxa de juros no período. Campo obrigatório. Nper: É o número de períodos. Campo obrigatório. Pgto: É o pagamento realizado em cada período. Campo obrigatório, se omitido, deve conter o argumento de Vf. VF: Este é o valor que se deseja obter ao final de um empréstimo, por exemplo. O campo é opcional, se for omitido será considerado pelo Excel como sendo 0 Tipo: Número 0 ou 1, indica se o pagamento é postecipado ou antecipado, respectivamente. Campo opcional.

SENAC/DR.PB

VF – VALOR FUTURO É utilizada quando se deseja saber qual o valor final de uma aplicação ou empréstimo, em um determinado período de tempo, a uma taxa constante de juros, utilizamos a função VF. Sintaxe = VF(taxa,nper,pgto,[vp],[tipo]) Argumento: Taxa: Taxa de juros no período. Campo obrigatório. Nper: É o número de períodos. Campo obrigatório. Pgto: É o pagamento realizado em cada período. Campo obrigatório, se omitido, deve conter o argumento de Vp. Vp: Este é o valor presente de uma série de pagamentos futuros. O campo é opcional, se for omitido será considerado pelo Excel como sendo 0. Tipo: Número 0 ou 1, indica se o pagamento é postecipado ou antecipado, respectivamente. Campo opcional.

NPER – NÚMERO DE PERÍODOS É utilizada quando se deseja saber qual é o tempo necessário para realizar um investimento. Vale ressaltar, que essa função deve ser usada se os pagamentos forem constantes e periódicos a taxas de juros fixas. Sintaxe: NPER(taxa, pgto,vp,[vf],tipo]) Argumentos: Taxa: É a taxa de juros no período. Campo obrigatório. 90

SENAC/DR.PB

Obs A taxa de juros deverá ser indicada com sinal negativo, não podendo ter percentuais acima de 4% ao mês, poi, poderá apresentar erro de referência ou de cálculo.

Pgto: É o pagamento realizado em cada período, e este não poderá sofrer alterações durante a vigência da anuidade, ou seja, são valores periódicos e constantes. Este campo é necessário na função. Vp: É o valor presente na série de pagamentos futuros. Campo obrigatório. Vf: O valor futuro é o que se deseja obter ao final dos pagamentos. O valor é opcional, porém se ele for omitido o Excel entende que o Vf é 0. Tipo: Número 0 ou 1, indica se o pagamento é postecipado ou antecipado, respectivamente. Campo opcional.

PGTO – PAGAMENTO É utilizada quando se deseja saber o valor que deve ser pago mensalmente em um financiamento ou empréstimo até mesmo o valor que deve ser investido mensalmente para se obter um valor no futuro. Sintaxe = PGTO(taxa, nper, vp, [vf], [tipo]) Argumentos: Taxa: É a taxa de juros para o empréstimo. Campo obrigatório. Nper: Número total dos pagamentos de um empréstimo. Campo obrigatório. Vp: É o valor presente na série de pagamentos futuros. Campo obrigatório. Vf: O valor futuro é o que se deseja obter ao final dos pagamentos. O valor é opcional, porém se ele for omitido o Excel entende que o Vf é 0. Tipo: Número 0 ou 1, indica se o pagamento é postecipado ou antecipado, respectivamente. Campo opcional.

SENAC/DR.PB

TAXA É utilizado quando se deseja calcular a taxa de juros de um empréstimo ou financiamento em um determinado período de tempo. Sintaxe = Taxa(nper, pgto, vp, [vf], [tipo], [estimativa]) Argumentos: Nper: É o número de períodos. Campo obrigatório. Pgto: É o pagamento realizado em cada período, não podendo ser alterado durante a anuidade. Se o pagamento for omitido, o argumento vf deverá ser incluído. Vp: É o valor presente na série de pagamentos futuros. Campo obrigatório. Vf: O valor futuro é o que se deseja obter ao final dos pagamentos. O valor é opcional, porém se ele for omitido o Excel entende que o Vf é 0 e se o Vf for omitido, deve-se colocar o argumento Pgto. Tipo: Número 0 ou 1, indica se o pagamento é postecipado ou antecipado, respectivamente. Campo opcional. Estimativa: É a sua estimativa para a taxa. Campo opcional.

SENAC/DR.PB

Capítulo 16 - Funções inseridas na Versão 2016 =MÁXIMOSES(intervalo_máximo; critério_intervalo_1; critério_1; ...) =MÍXIMOSES(intervalo_mínimo; critério_intervalo_1; critério_1; ...)

=SES(teste_lógico_1; valor_se_verdadeiro_1; [teste_lógico_n]; [valor_se_verdadeiro_n])

=PARÂMETRO(expressão; valor_1; resultado_1; [valor_n]; [resultado_n]; [padrão])

=CONCAT(texto_1; [texto_n])

=UNIRTEXTO(delimitador; ignorar_vazio; texto_1; texto_n; ...)

SENAC/DR.PB

Bibliografia

CINTO, Antonio Fernando; GÓES, Wilson Moraes. Excel Avançado. São Paulo: Novatec, 2005. FERNANDES, Maicris; Et al. Desenvolvendo aplicações poderosas com Excel e VBA. São Paulo: Visual Books, 2004. MANZANO, José Augusto N. G.; MANZANO, André Luiz N. G. Estudo dirigido de Microsoft Office Excel 2007 – Avançado. São Paulo: Editora Érica. Martelli, Richard; Mendonça de Barros, Maria Silvia. Excel 2010 Avançado - Col. Nova Série Informática. SENAC São Paulo. SILVA, Camila Ceccatto. EXCEL 2007 AVANÇADO. São Paulo: Viena MICROSOFT OFFICE 2013. Disponível em: http://office.microsoft.com/pt-br Acessado em abril 2017.

SENAC/DR.PB