1
Índice
Aula 1...................... 1............................................. .............................................. ............................................. .........................4 ...4 Introdução .............................................................. ................................................................................................................................. ...................................................................4 Formatações de tabela ......................................................... ..................................................................................................... ............................................5 Função HOJE ...................................................................... ............................................................................................................................. .......................................................7 Função SE.................................................................. .................................................................................................................................... ..................................................................7 Exercícios .................................................................. .................................................................................................................................... ..................................................................8 Exercício de Fixação ............................................................... ........................................................................................................ ......................................... 15 Aula 2...................... 2............................................. .............................................. ............................................. ........................16 Vínculos de Células ..................................................................... ........................................................................................................... ...................................... 16 Planilhas .................................................................... ................................................................................................................................... ............................................................... 16 Função PROCV .................................................................. ...................................................................................................................... .................................................... 17 Exercícios .................................................................. ................................................................................................................................. ............................................................... 19 Exercício de fixação ................................................................... ......................................................................................................... ...................................... 34 Aula 3...................... 3............................................. .............................................. ............................................. ........................35 Tabelas Dinâmicas ................................................................... ............................................................................................................ ......................................... 35 Criando uma tabela Dinâmica ............................................................... .................................................................................. ................... 36 Adicionando Filtros..................................................................... ........................................................................................................... ...................................... 40 Exercícios .................................................................. ................................................................................................................................. ............................................................... 42 Exercícios de Fixação............................................................. ...................................................................................................... ......................................... 44 Aula 4...................... 4............................................. .............................................. ............................................. ........................50 Macros ............................................................. ................................................................................................................................... ........................................................................... ..... 50 Macros de programação .................................................................... ............................................................................................... ........................... 50 Macros de aplicação................................................................... ......................................................................................................... ...................................... 50 Criando uma Macro..................................................................... ........................................................................................................... ...................................... 52 Segurança das Macros .......................................................... ................................................................................................... ......................................... 53 Locais Confiáveis ............................................................ ................................................................................................................ .................................................... 54 Exercícios .................................................................. ................................................................................................................................. ............................................................... 55 Exercícios de Fixação............................................................. ...................................................................................................... ......................................... 67 Aula 5...................... 5............................................. .............................................. ............................................. ........................68 Funções Financeiras ............................................................... ........................................................................................................ ......................................... 68 Função VF .............................................. ..................................................................... ...................................... ...............68 Função Nper ........................................................... ........................................................................................................................... ................................................................ 69 Função Taxa ........................................................... ........................................................................................................................... ................................................................ 70 Função Pgto............................................................ ............................................................................................................................ ................................................................ 71 Exercícios .................................................................. ................................................................................................................................. ............................................................... 72 Exercícios de Fixação............................................................. ...................................................................................................... ......................................... 79 Aula 6...................... 6............................................. .............................................. ............................................. ........................80 Mala Direta .............................................................. .............................................................................................................................. ................................................................ 80 Criando uma Mala Direta passo a passo .......................................................... 81 Exercícios .................................................................. ................................................................................................................................. ............................................................... 85 Exercícios de Fixação. ........................................................... .................................................................................................... ......................................... 97 Aula 7...................... 7............................................. .............................................. ............................................. ........................98 Gráficos Dinâmicos ..................................................................... ........................................................................................................... ...................................... 98 Inserindo um gráfico dinâmico ............................................................ ............................................................................... ................... 98 Exercícios .................................................................. .............................................................................................................................. ............................................................ 101 Exercícios de Fixação............................................................. ................................................................................................... ...................................... 104 2
Aula 8...................... 8............................................. .............................................. ............................................ .....................105 Gráficos........................................................... ................................................................................................................................. ........................................................................ .. 105 Tipos de Gráficos ............................................................ ............................................................................................................. ................................................. 105 Colunas ........................................................... ................................................................................................................................. ........................................................................ .. 105 Pizza ................................................................... ........................................................................................................................................ ....................................................................... .. 106 Barras............................................................... ..................................................................................................................................... ........................................................................ .. 107 Dispersão (XY) ................................................................. .................................................................................................................. ................................................. 109 Rosca ................................................................. ...................................................................................................................................... ....................................................................... .. 110 Área .................................................................... ......................................................................................................................................... ....................................................................... .. 111 Linhas ............................................................... ..................................................................................................................................... ........................................................................ .. 112 Ações ................................................................. ...................................................................................................................................... ....................................................................... .. 114 Superfície .................................................................. .............................................................................................................................. ............................................................ 115 Bolhas .............................................................. .................................................................................................................................... ........................................................................ .. 115 Radar ................................................................. ...................................................................................................................................... ....................................................................... .. 116 Exercícios .................................................................. .............................................................................................................................. ............................................................ 117 Exercícios de Fixação............................................................. ................................................................................................... ...................................... 120 Aula 9...................... 9............................................. .............................................. ............................................ .....................121 Formulários Personalizados Parte 1 ........................................................... 121 Criando um formulário passa a passo- Parte 1 ....................................... 121 Caixa de Combinação ............................................................. ................................................................................................... ...................................... 123 Exercícios .................................................................. .............................................................................................................................. ............................................................ 126 Exercícios de Fixação............................................................. ................................................................................................... ...................................... 140 Aula 10.................................... .......................................................... ............................................. ............................. ......142 Formulários Personalizados Parte 2 ........................................................... 142 Botão de Opção .............................................................. ................................................................................................................ .................................................. 142 Aparência do Formulário................................................................... ........................................................................................... ........................ 144 Exercícios .................................................................. .............................................................................................................................. ............................................................ 146 Exercícios de Fixação............................................................. ................................................................................................... ...................................... 163 Aula 11.................................... .......................................................... ............................................. ............................. ......164 Gráficos de Gantt ........................................................... ............................................................................................................ ................................................. 164 Inserindo um gráfico de Gantt ............................................................. ............................................................................. ................ 165 Exercícios .................................................................. .............................................................................................................................. ............................................................ 166 Exercícios de Fixação............................................................. ................................................................................................... ...................................... 171 Aulas 12 e 13 ........................................................... ............................................................................. ..................172 Projeto Final ...................................................................... ....................................................................................................................... ................................................. 172 Tela de Abertura ............................................................. .............................................................................................................. ................................................. 173 Planilha Locar ................................................................... .................................................................................................................... ................................................. 174 Planilha Clientes ............................................................. .............................................................................................................. ................................................. 174 Planilha Filmes ................................................................. .................................................................................................................. ................................................. 175 Exercícios .................................................................. .............................................................................................................................. ............................................................ 177 Exercício de Fixação ............................................................... ..................................................................................................... ...................................... 256 –
–
3
Aula 1 Introdução O Microsoft Excel (popularmente conhecido somente como “Excel” ) é um programa de planilha eletrônica de cálculo escrito e produzido pela Microsoft para computadores que utilizam o sistema operacional Microsoft Windows e também computadores Macintosh da Apple. Seus recursos incluem uma interface intuitiva, além de capacitadas ferramentas de cálculo e de construção de gráficos que, juntamente com o Marketing agressivo, tornaram do Excel um programa muito utilizado nos computadores de todo o mundo. É com grande vantagem que o aplicativo de planilha eletrônica tornou-se
dominante. Especificamente, você pode usar o Excel para acompanhar dados, criar modelos de análise de dados, criar fórmulas para fazer cálculos desses dados, organizar dinamicamente os dados de várias maneiras e apresentá-los em diversos tipos de gráficos profissionais. Cenários comuns de uso do Excel incluem: incluem :
Contabilidade - Você pode usar os poderosos recursos de cálculo do Excel em vários demonstrativos de contabilidade financeira; por exemplo, de fluxo de caixa, de rendimentos ou de lucros e perdas. Orçamento - Independentemente de as suas necessidades serem pessoais ou relacionadas a negócios, você pode criar qualquer tipo de orçamento no Excel; por exemplo, um plano de orçamento de marketing, um orçamento de evento ou de aposentadoria. Cobrança e vendas - O Excel também é útil para gerenciar dados de cobrança e vendas, você pode criar facilmente os formulários de que precisa; por exemplo, notas fiscais de vendas, guias de remessa ou pedidos de compra. Relatórios - Você pode criar muitos tipos de relatórios no Excel que reflitam uma análise ou um resumo de dados; por exemplo, relatórios que medem desempenho de projeto, mostram variação entre resultados reais e projetados, ou ainda relatórios que você pode usar para previsão de dados. Planejamento - O Excel é uma ótima ferramenta para criar planos profissionais ou planejadores úteis; por exemplo, um plano de aula semanal, de pesquisa de marketing, de impostos para o final do ano, ou ainda planejadores que ajudam a organizar refeições semanais, festas ou férias. Acompanhamento - Você pode usar o Excel para acompanhamento de dados de uma folha de ponto ou de uma lista; por exemplo, uma folha de ponto para acompanhar o trabalho, ou uma lista de estoque que mantém o controle de equipamentos. Usando calendários - Por causa de seu espaço de trabalho semelhante a grades, o Excel é ideal para criar qualquer tipo de calendário; por exemplo, um calendário acadêmico para controlar atividades durante o ano escolar, um calendário de ano fiscal para acompanhar eventos e etapas comerciais.
4
Formatações de tabela A primeira atitude que devemos ter ao trabalhar com o Excel é definir qual o tipo de formatação vamos utilizar. Podemos digitar um texto inserindo-o dentro de uma célula. Logo após podemos fazer o processo de formatação desejado.
No campo 1 encontramos uma formatação típica para o texto, que é escolher o tipo de fonte e também o tamanho da mesma. Existem fontes que podemos considerar “padrões” para o desenvolvimento de qualquer arquivo em qualquer editor. Dentre elas se destacam a Times New Roman, Arial, Verdana, Cambria, dentre muitas outras. No campo 2 podemos definir alguns estilos para a fonte que escolhemos, da seguinte forma: Negrito é um estilo de formatação que consegue deixar uma palavra, linha ou texto em total evidencia dentro do documento. Exemplo.: Excel Avançado. Itálico nos permite deixar a fonte inclinada, como se fosse a torre de Pisa, localiza na Itália. Exemplo.: Excel Avançado.
Sublinhado nos permite dar um visual mais “moderno” para a palavra, tem por principal função traçar uma linha abaixo da palavra. Exemplo.: Excel Avançado. No campo 3 encontramos uma das formatações mais utilizadas dentro do Excel, a formatação de Bordas de uma tabela. Podemos definir uma gama de estilos e tipo de Bordas, porém não podemos abusar delas, por que geralmente os projetos feitos dentro do Excel contém um padrão, onde não podemos ultrapassar esses limites. Assim como nas fontes, as bordas também contém padrões de uso, onde existem alguns tipos principais, como colocar bordas em toda a tabela, colocar somente uma borda para dar um contorno na tabela, ou quem sabe colocar somente uma borda para sublinhar a tabela. Enfim, existem inúmeras possibilidades e formas de aplicar as bordas dentro da tabela.
5
No campo 4 podemos encontrar as cores de preenchimento da tabela, ou seja, podemos definir uma cor de preenchimento tanto para a fonte quanto para a tabela. Cores de preenchimento
Cores da Fonte
No campo 5 já é possível escolher o estilo de alinhamento desejado, ou seja, podemos colocar um texto, número ou imagem em diferente formas de alinhameto. Alinhando o texto à esquerda podemos alinhar o texto para que ele se inicie na margem esquerda do documento, como ilustra a figura. Alinhando o texto ao centro podemos alinhar de uma maneira em que ele fique totalmente focado ao centro do documeto, como ilustra a figura. Alinhando o texto à direita podemos fazer o oposto de alinha-lo à esquerda, ou seja, ao invés dele começar na margem esquerda ele irá começar na margem direita do documento, como ilustra a figura. No campo 6 podemos encontrar um estilo muito parecido com o do campo 5, porém ele alinha ao tamanho da pagina e não ao do texto. Podemos alinhar o texto acima, no centro e abaixo da página, conforme for nossa necessidade. No campo 7 podemos encontrar uma função muito utilizada no Excel, que é Mesclar as Células que você não estiver usando, ou seja, você pode adicionar a tabela um título, número ou qualquer outro caractere, fazendo isso você poder selecionar o número de células desejado e ativar a opção, com isso todas as células selecionadas irão ficar somente a cargo do título ou caractere que você escolheu.
O Excel nos disponibiliza inúmeras opções de funções para o uso. Na aula 1 relembramos para você duas das principais funções, que é a função HOJE e a função S E.
6
Função HOJE É responsável por devolver o número de série da data atual. O número de série é o código de data-hora utilizado pelo Excel para cálculos de datas e horas. Se o formato das células correspondia a Geral antes da introdução da fórmula, o Excel altera o formato da célula para Data. Se pretender visualizar o número de série, terá de alterar o formato da célula para Geral ou Número. A função HOJE é útil quando precisamos apresentar a data atual numa folha de cálculo, independentemente de quando abrir o livro. Esta função também é útil para calcular intervalos. Por exemplo, se conhecer alguém que tenha nascido em 1963, poderá utilizar a fórmula para calcular a idade dessa pessoa no respectivo aniversário no ano atual.
Função SE A função SE retornará um valor se uma condição que você especificou for avaliada como verdadeiro e outro valor se essa condição for avaliada como falso. Por exemplo, a fórmula =SE(A1>10,"Mais que 10","10 ou menos") retornará "Mais que 10" se A1 for maior que 10 e "10 ou menos" se A1 for menor que ou igual a 10. Sua estrutura é composta pelos seguintes fatores: - Teste Lógico - É Obrigatório. Qualquer valor ou expressão que possa ser avaliado como verdadeiro ou falso. Por exemplo, A10=100 é uma expressão lógica, se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada como Falso. - Valor se for verdadeiro - É opcional. O valor que você deseja que seja retornado se o argumento for considerado verdadeiro. Por exemplo, se o valor desse argumento for "Dentro do orçamento" e o argumento for considerado verdadeiro, a função SE retornará o texto "Dentro do orçamento". - Valor se for falso - É opcional. O valor que você deseja que seja retornado se o argumento for considerado falso. Por exemplo, se o valor desse argumento for "Acima do orçamento" e o argumento for considerado falso, a função SE retornará o texto "Acima do orçamento".
7
Exercícios 1) Clique em Iniciar. 2) Clique em Todos os programas. 3) Clique sobre o Microsoft Excel.
4) Crie uma tabela
8
5) Após este procedimento, selecione toda a tabela, clique sobre o botão borda e escolha a opção Todas as Bordas , para que sua tabela fique similar a imagem abaixo.
6) Selecione a tabela novamente e clique sobre formatar células, que se localiza na seta que fica no canto inferior direito do submenu alinhamento.
9
7) Clique sobre a guia Fonte e escolha a fonte Impact.
8) Clique sobre a guia Bordas e defina um contorno com a linha grossa Contorno e finalmente em Ok.
, depois em
10
9) Em seguida, selecione a primeira célula da coluna Data da Aprovação.
10) Clique sobre o quadro de funções
.
11) Procure a função HOJE e selecione-a.
11
12) Clique em OK. Na caixa argumentos da função que se abrirá, clique em OK.
13) Após este processo, selecione a primeira célula da coluna Aprovação.
14) Clique novamente sobre o quadro de funções e insira a função SE.
12
15) Preencha os valores da função da mesma forma demonstrada abaixo.
Obs: Você pode ver que caso a Média seja maior que 7, o aluno será aprovado, e caso sua Média seja menor que 7, o mesmo será reprovado. 16) Feito isso, clique em OK e você terá o resultado abaixo.
17) Utilize o Laço para transpor o teste e a data para os demais itens da tabela.
13
18) Você terá um resultado similar ao exibido abaixo.
14
Exercício de Fixação Crie uma tabela com base em matrículas de alunos, use como referência a idade do cliente, ou seja, se o aluno for menor de idade, não poderá completar a matrícula sem a mediação dos pais. Utilize a função SE para fazer este teste lógico. Aplique a data em que o cliente foi fazer a matrícula.
Dica: Vale a pena lembrar que além da função SE que será utilizada para o desenvolvimento do exercício, você deve se habituar ao uso da função HOJE da mesma forma, pois ela pode lhe poupar muito tempo de digitação, assim agilizando seu trabalho.
15
Aula 2 Vínculos de Células O Excel nos dá a possibilidade de vincular células do mesmo arquivo e também de pastas diferentes, para que isto ocorra, devemos nos adaptar com as seguintes funções. A primeira delas é uma forma manual de vincularmos as células de determinadas tabelas.
Entre colchetes ([]) devemos sempre colocar o nome do arquivo. Devemos salvar este arquivo em uma pasta dentro do computador, lembrando que o arquivo deve estar na extensão .xls. Após isso, devemos informar o número correspondente à planilha onde o arquivo está localizado. Depois disso devemos colocar o símbolo do Cifrão ($) que nos permite copiar para todas as células selecionadas o mesmo valor. Ele não faz somas, divisões, multiplicações ou subtrações, ele apenas copia um valor e distribui o mesmo para as células selecionadas. Planilhas Planilha eletrônica, ou folha de cálculo, é um tipo de programa de computador que utiliza tabelas para realização de cálculos ou apresentação de dados. Cada tabela é formada por uma grade composta de linhas e colunas. O nome eletrônica se deve à sua implementação por meio de programas de computador. No Brasil, estas tabelas também são chamadas de planilhas. Em Portugal são chamadas de folhas de cálculo. Para identificarmos uma célula, normalmente utilizamos o nome da coluna seguido do nome da linha. Por exemplo, se tomarmos a coluna de nome A e a linha de número 10, neste cruzamento teremos a célula A10. Como padrão, o Excel nos disponibiliza três planilhas, seus nomes são denominados como Plan, seguindo de seu número. Plan 1, Plan 2, Plan 3.
Podemos também renomear o nome destas planilhas, para isto basta clicar duas vezes sobre a planilha que desejamos renomear. Depois disto basta digitar o novo nome e pressionar a tecla Enter. Dando um duplo clique na planilha
Com o nome alterado
16
Função PROCV Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela. Sintaxe da fórmula: PROCV(valor_procurado;matriz_tabela;núm_índice_coluna) - Valor procurado - O valor a ser localizado na primeira coluna da matriz da tabela. O Valor procurado pode ser um valor ou uma referência. Se o valor procurado for menor do que o menor valor na primeira coluna de matriz tabela, o PROCV fornecerá o valor de erro #N/D. - Matriz tabela - Duas ou mais colunas de dados. Use uma referência a um intervalo ou nome de intervalo. Os valores na primeira coluna da matriz tabela são aqueles procurados pelo valor procurado. Esses valores podem ser textos, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. - núm_índice_coluna - É o número da coluna na tabela matriz a partir do qual o valor correspondente deve ser retornado. Um número índice coluna de 1 retornará o valor na primeira coluna na tabela matriz. Um número índice coluna de 2 retornará o valor na segunda coluna na tabela matriz, e assim por diante. Se número índice coluna for: Menor do que 1, PROCV retornará o valor de erro #VALOR!. Maior que o número de colunas em matriz tabela, PROCV fornecerá o valor de erro #REF!. - Procurar intervalo - Um valor lógico que especifica se você deseja que o PROCV localize uma correspondência exata ou aproximada. Se for verdadeiro, uma correspondência aproximada será retornada. Se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor procurado será retornado. Os valores na primeira coluna de matriz tabela devem ser colocados em ordem de classificação crescente, caso contrário, o PROCV poderá não fornecer o valor correto. Você pode colocar os valores em ordem ascendente escolhendo o comando Classificar no menu Dados e selecionando Crescente. Para obter mais informações, consulte Ordens de classificação padrão. Se falso, o PROCV somente localizará uma correspondência exata. Nesse caso, os valores na primeira coluna de tabela matriz não precisam ser classificados. Se houver dois ou mais valores na primeira coluna da tabela matriz que corresponderem ao valor procurado, o primeiro valor encontrado será usado. Se uma correspondência exata não for encontrada, o valor de erro #N/D será retornado.
17
Também podemos, ao invés de digitarmos a função, procura-la no quadro de funções.
No campo 1 encontramos o valor a ser procurado, ou seja, o valor que irá ser vinculado. Podemos digitar o valor como também podemos selecionar a célula onde está localizado este valor. No campo 2 encontramos a tabela Matriz, ou seja, onde devemos colocar o intervalo referente à tabela que contém os valores. No campo 3 encontramos o número do índice, ou seja, onde devemos colocar o número da coluna que irá conter o índice do resultado. No campo 4 contém uma opção que é somente válida quando precisamos testar alguma condição. É muito utilizado quando queremos vincular alguns caracteres na tabela.
18
Exercícios Exercício 1 1) Abra o Editor do Excel. 2) Dentro dele, crie uma tabela dupla, porém simples, assim como exemplifica a imagem.
3) Como você pode notar, na segunda tabela existem células em branco. É nelas em que vamos vincular os valores da primeira parte da tabela. 4) Em Glória Souza, clique na célula em branco referente à Janeiro.
5) Insira o sinal de igualdade.
19
6) Na primeira parte da tabela, clique sobre a célula que corresponde ao mês de Janeiro da vendedora Glória Souza.
7) Pressione a tecla Enter e veja o resultado.
8) Faça o mesmo processo com José Augusto, vincule suas vendas de março à tabela 2.
20
9) Você terá o resultado abaixo.
10) Salve o arquivo e feche o editor.
21
Exercício 2 1) Crie duas tabelas, porém em duas planilhas. Utilize um demonstrativo, como exemplifica a imagem abaixo. Na plan1 crie a seguinte tabela.
2) Agora na plan2, crie a mesma tabela, porém, preencha os campos em branco com alguns valores.
22
3) Retorne a planilha 1 e clique sobre a célula em branco que representa as compras da empresa Baterias Souza.
4) digite o sinal de igualdade (=).
5) Vá até a plan2 e clique sobre a célula que corresponde ao valor das compras da empresa Baterias Souza.
23
6) Pressione a tecla Enter e veja o resultado na Plan1.
7) Faça este mesmo processo com os outros campos em branco na tabela.
24
Exercício 3: 1) Crie uma tabela similar à demonstrada abaixo.
2) Clique em no campo Curso da tabela de Pesquisa.
3) Clique no botão de Funções
.
4) Digite PROCV e clique em Ir como indicado abaixo.
25
5) Clique agora em OK.
6) Você Visualizará a tela de Argumentos de função. Clique no local indicado para que possamos determinar o Valor_procurado.
7) Selecione agora a célula H3, pois é exatamente o campo que utilizaremos para digitar o nome do aluno.
26
8) Pronto, como você pode ver a coordena da célula já aparece no interior de nosso argumento, clique novamente no botão indicado abaixo.
9) Clique agora em Matriz_tabela.
10)Aqui iremos determinar a área que abrangerá nossa pesquisa, que vai de B4 á E17, portanto, digite B4:E17 como na imagem abaixo.
27
11) Clique agora em Núm_índice_coluna.
12) Como queremos determinar o curso ao qual o aluno está matriculado, digite o número 2, que é referente à segunda coluna de nossa tabela.
13) Agora clique em Procurar_intervalo.
28
14) Como nossa primeira coluna está fora de ordem, vamos determinar esse valor como falso. Digite 0 e clique em OK.
15) Você pode achar estranha a mensagem que ficou no interior de nossa célula, mas isso é porque solicitamos que fosse feita nenhuma busca.
16) Clique agora na célula referente ao campo de Mês de Início.
17) Clique no botão de Funções
.
29
18) Digite PROCV e clique em Ir como indicado abaixo.
19)Clique agora em OK.
20) Preencha os Argumentos de função da mesma forma demonstrada na imagem abaixo e clique em OK.
30
21)Clique agora na célula referente ao campo de Mês Final.
22)Clique no botão de Funções
.
23) Digite PROCV e clique em Ir como indicado abaixo.
24)Clique agora em OK.
31
25) Preencha os Argumentos de função da mesma forma demonstrada na imagem abaixo e clique em OK.
26) Agora vamos fazer um teste, clique no campo referente à pesquisa de nome de aluno.
32
27)Digite Thomas Silva.
28)Agora pressione Enter e verifique se seu resultado ficou semelhante ao demonstrado na imagem abaixo.
29)Salve seu arquivo com o nome de Relatório de Alunos.
33
Exercício de fixação Utilize o PROCV para procurar os dados de representante e quantidade de visitas, tendo como base o nome de sua empresa.
Clique sobre a primeira seta e insira a função PROCV, já na segunda seta, insira a mesma função, tendo somente o cuidado com a coluna referente às quantidades.
34
Aula 3 Tabelas Dinâmicas As tabelas Dinâmicas são tabelas interativas que resumem elevadas quantidades de dados, usando estruturas e métodos de cálculos especializados. Trata-se de uma abordagem simples de um conjunto de ferramentas de análises muito utilizado atualmente, denominadas genericamente de OLAP (On line Analytical Processing), que, dentre outras características permite uma análise multidimensional dos dados. As tabelas Dinâmicas permitem organizar dados e calcular informações resumidas utilizando categorias (campos) e funções resumo (soma, média, dentre outros).
No campo 1 podemos encontrar uma parte destinada aos relatórios, ou seja, todos os comandos executados dentro da tabela Dinâmica serão visíveis no centro da tela em formas de gráficos. No campo 2 são as opções sobre a tabela Dinâmica, ou seja, todas as ferramentas que precisamos para corrigir e formatar possíveis erros. No campo 3 ficam disponíveis os rótulos, ou seja, formas de visualização dentro do relatório. Você poderá incluir todos os rótulos dentro do relatório, ou simplesmente os que você desejar. No campo 4 podemos encontrar os filtros, ou seja, formas de visualização dos dados dentro do relatório. Você poderá alterar os filtros apenas clicando e arrastando até o filtro desejado. A partir daqui vamos lhe mostrar como montar uma tabela dinâmica passo a passo.
35
Criando uma tabela Dinâmica O primeiro passo é abrir o arquivo com a tabela que você deseja transformar em tabela dinâmica.
Selecione todos os campos com o Mouse.
Agora, clique na guia Inserir e depois em Tabela Dinâmica. Então surgirá uma nova janela em sua tela. Nela, você poderá alterar os campos que farão parte de sua nova tabela e também você poderá definir o local onde ela será criada.
36
Além dos campos em sua própria planilha, é possível também selecionar fontes externas. Para isso, clique em Usar fontes de dados externa e então escolha a fonte de dados de sua nova tabela Dinâmica. Depois disso, clique em OK.
Sua tabela dinâmica está pronta. A partir de agora, para acessar os valores e dados, clique sobre a tabela e então selecione tudo o que deseja analisar.
Com este tipo de tabela, você poderá comparar os dados facilmente, como no exemplo abaixo, onde foram selecionados os dados de apenas três pessoas.
37
38
Ainda na barra do lado direito, você poderá mover os dados entre os campos filtros de relatórios, rótulos de coluna, rótulos de linhas e Valores.
Acessando as opções da Tabela Dinâmica (Tabela Dinâmica> Opções> Opções), você poderá alterar algumas configurações de sua planilha, como impressão, definições de exibição, layout, filtros e dados.
39
Adicionando Filtros Para melhor analisar os dados, você pode adicionar filtros para poder reduzir a visualização de dados ou algum conteúdo mais específico. Para isto, clique sobre a guia Rótulos de Linha para que seja aberto o seu menu dropdown, depois vá em Filtros e Valores e escolha o tipo de filtro que deseja aplicar.
Veja um exemplo com o filtro.
Outros tipos de filtros são os Filtros de Rótulos, estes relativos aos rótulos da sua tabela. No nosso exemplo, os rótulos são os meses. Esta função também será bem útil no caso de tabelas com um grande número de dados e informações. Você pode ainda utilizar seus próprios dados como filtros para comparação na tabela. Por exemplo, arraste qualquer um dos dados da aba direita para o campo Filtro de Relatório e então ele aparecerá em cima da sua tabela.
40
Clique para abrir sua guia e então selecione o valor que deseja utilizar como referência. Desta forma, você poderá selecionar um ou vários itens (no caso de nosso exemplo, meses) para filtrar e comparar com os demais dados informados na tabela.
41
Exercícios 1) Abra o Excel. 2) Crie um tabela de orçamentos no Excel.
3) Selecione a tabela e logo em seguida clique em Inserir.
4) Clique em Tabela Dinâmica.
5) Na opção Escolha onde deseja que o relatório dinâmica seja colocado clique em OK.
42
6) Visualize e adicione os rótulos, de maneira que a tabela fique de uma forma mais legível.
7) Desfaça a tabela dinâmica criada anteriormente, isto é, delete a tabela dinâmica.
43
Exercícios de Fixação 1) Crie uma tabela no Excel. Utilize como base algumas médias escolares assim como na imagem abaixo.
2) Transforme sua tabela em uma tabela dinâmica e visualize todos os rótulos.
44
3) Desfaça agora sua tabela dinâmica para que ela volte ao formato anterior.
4) Selecione toda a coluna Média.
5) Clique na aba Dados.
6) Clique em Validação de Dados.
45
7) Clique agora no menu DropDown de Permitir.
8) Selecione a opção Numero Inteiro.
9) Selecione agora o menu dropdown de Dados e escolha a opção É igual ou maior a.
46
10)Em Mínimo digite 7 e Clique em Alerta de erro.
11) Clique em Título e digite Média Insuficiente.
12) Agora clique em Mensagem de Erro e digite A nota está abaixo do índice de aprovação!
47
13)Clique em OK.
14) Vamos agora testar nossa validação, clique na célula D6.
15)Digite 6.
48
16)Pressione agora a tecla Enter e você receberá a mensagem abaixo. Clique em Cancelar.
17)Pronto, nossa validação está completa, mas você deve estar se perguntando o porque de as notas com média abaixo de 7 que estavam previamente determinadas na tabela ainda estão no interior da mesma. Isso é muito simples de entender, elas foram adicionadas antes da criação de nossa validação, sendo assim, seus dados permanecem, apenas o que for acrescentado daquele momento em diante será validado.
49
Aula 4 Macros O nome pode até parecer estranho, mas muita gente usa sem se dar conta do que se trata. Macros são definidas na programação como um padrão de entrada que é substituído por um novo padrão de saída. Podem ser aplicadas como recurso de linguagem em programação ou para converter entradas periféricas em comandos e ações dentro do sistema operacional. A essa altura o usuário pode estar ainda mais confuso, mas o que dizer do comando “Control + C” (atalho do teclado para copiar)? Na verdade, este é um ótimo exemplo de uma entrada simples convertida em um comando elaborado, ou seja, uma macro. O mesmo ocorre para todos os demais atalhos para programas que convertem uma sequência do teclado ou mouse em uma ação. Em outras palavras seria uma sub-rotina, que é uma sequência de comandos e funções armazenados no VBA (Visual Basic for Application) que podem ser criadas, por exemplo, para automatizar uma tarefa que seria executada várias vezes no Excel. O que ajuda você a ganhar tempo e diminuir os erros que poderia cometer ao executar as mesmas tarefas repetidas vezes, já que o sistema fica encarregado de executar essa sequência de tarefas sempre na ordem correta e sem pular nenhuma etapa. É claro que devemos ter muito cuidado ao criar uma Macro para ter certeza que esta contempla todas as etapas do processo. Isso quer dizer que a integridade das informações geradas depende principalmente do planejamento que o usuário faz antes de gravar a Macro, para certificar-se que todas as etapas foram contempladas. A Macro pode ser gravada etapa a etapa para realizar uma tarefa, mas antes de gravá-la, devemos planejar as etapas e os comandos que devem ser executados. Cometendo-se erros durante a gravação, as correções que também forem efetuadas no momento serão gravadas em sequência. Então, lembre-se que é muito importante o planejamento da macro antes de gravá-la. Podemos também executar uma Macro através do caminho Exibição -> Macros -> Exibir Macros, através de um botão específico, de uma combinação de teclas, de um botão de uma barra de ferramentas, a partir de um atalho no teclado, de um objeto gráfico em uma planilha etc. Porém com exceção da primeira opção, que já existe na Barra de Menu do Excel, todas as outras dependem de programação VBA. Vejamos abaixo dois dos principais tipos de macros. Macros de programação Linguagens como C e Assembly possuem sistemas simples de macro implementados como pré-processadores no complicador ou montador, respectivamente. No caso do C, as macros funcionam pela simples busca textual pelo código fonte. Dialetos da linguagem LISP, tais como Common LISP e Scheme, possuem sistemas mais elaborados de macros. Macros de aplicação Aplicações podem usar sistemas análogos de macros, que permitem ao usuário definir uma série de ações em um novo procedimento. As macros são geralmente gravadas ao executar a sequência uma vez, permitindo à aplicação lembrar-se de tais ações. Usuários avançados podem também fazer uso de uma linguagem de programação de macros embutida, que possui acesso direto às funcionalidades da aplicação.
50
Uma linguagem de macro é uma linguagem de programação na qual toda a computação é feita pela expansão (instanciação) de macros. Geralmente, não são usadas para a programação geral, mas são comuns em aplicações de p rocessamento de texto.
51
Criando uma Macro Primeiramente devemos abrir uma nova pasta de trabalho no Excel. Após isso, selecione uma célula (de preferência) a A1. Vá em Exibição>Macros>Gravar Macro. Será aberta a seguinte janela.
No campo 1 podemos encontrar o nome da Macro. Como exemplo, utilizamos o nome mostrado, mas você poderá utilizar o nome desejado. Lembre-se, você não poderá usar espaçamento entre os caracteres, utilizar caracteres especiais e também não poderá começar o nome da Macro com um número. No campo 2 podemos definir uma tecla de atalho para poder abrir a macro criada. Esta tecla de atalho é dada em conjunto com as teclas Ctrl e Shift. No campo 3 podemos selecionar em que pasta vamos armazenar a macro. Podemos armazenar em todas as pastas de trabalho, pasta de trabalho atual e também a pasta 1.
No campo 4 devemos colocar uma descrição para a macro. Muitos programadores que a utilizam, colocam sempre em descrição a sua funcionalidade, ou seja, se a macro é criada para abrir um arquivo, pode ser posto em descrição o exemplo “Abrir um arquivo”. Os botões para inicialização da macro e para parar a macro são os seguintes.
Botão Iniciar Macro
Botão parar Macro
52
Segurança das Macros Ainda no contexto de Macros, podemos também definir uma segurança para as mesmas. Com o padrão já temos a disposição quatro tipos de segurança de Macro.
1 - Desativar todas as Macros sem notificação Ative essa função somente se você não confia nos macros existentes. Todos os macros e todos os alertas de segurança sobre os macros serão desabilitados. Contendo macros confiáveis, você poderá salvá-las em outro local de sua total confiança. Fazendo isso você poderá abrir o arquivo sem precisar passar pela central de confiabilidade. 2 - Desabilitar todas as macros com notificação Essa é a configuração padrão. Clicando nessa opção você faz com que suas macros sejam desabilitadas. Entretanto, se você ativar essa opção irá receber alertas de segurança se houver macros presentes. Dessa maneira, é possível escolher quando habilitar essas macros de acordo com cada caso. 3 - Excluir todas as macros, exceto as digitalmente assinadas. Equivale praticamente à opção Desabilitar todas as macros com notificação, exceto pelo fato de que, ela poderá ser executada se for digitalmente assinada (confiável). Se você não tiver total confiança sobre à macro, receberá uma notificação. Todas as macros não confiáveis são desabilitados sem notificação. 4 - Habilitar todas as macros É uma opção não recomendada, pois macros confiáveis e não confiáveis serão executadas. Isso Faz com que seu computador execute códigos vulneráveis e muitas vezes de extremo perigo.
53
Locais Confiáveis Locais confiáveis são locais onde não há existência de riscos para a Macro, como perda de dados, vírus, dentre outros. Seja uma pasta, diretório ou apenas um endereço. Basta criá-lo e colocá-lo dentro do Excel como um arquivo confiável. Para Adicionar novos locais, faça o seguinte: Clique em Adicionar novo local. Será aberta a seguinte tela.
Para procurar um novo arquivo, basta você clicar em Procurar. Fazendo isso você terá um novo local confiável, onde você poderá armazenar as suas novas Macros.
54
Exercícios 1) Abra o Excel. 2) Veja se a aba Desenvolvedor está ativada.
3) Como você pode notar no exemplo acima, ela não está em nossa barra atual, vamos ativá-la, clique em Arquivo.
4) Agora clique em Opções.
5) Clique em personalizar faixa de opções.
55
6) Ative a aba Desenvolvedor, clicando no local indicado abaixo e clique em OK.
7) Crie uma tabela tendo como base um ranking de médias escolares.
8) Selecione a tabela de acordo com a imagem abaixo.
56
9) Clique sobre a aba Desenvolvedor.
10)Agora selecione a opção Gravar Macro.
57
11) Defina o nome como ordenarmacro e clique em OK.
13) Com a macro ainda em execução, selecione a tabela da mesma forma demonstrada abaixo.
58
14) Clique na aba Dados.
15)Agora clique em Classificar.
59
16)Dentro da janela de Classificar na opção Classificar Por escolha Média Terceiro Trimestre.
17)O campo de Classificar em continuará sendo Valores assim como demonstrado no campo azul da imagem abaixo. Em Ordem, escolha a opção Do Maior para o Menor assim como destacado em vermelho abaixo e clique em OK.
60
18)Como você pode ver, nossa classificação já foi aplicada. Clique na aba Desenvolvedor.
19)Pare a gravação da Macro clicando no local indicado abaixo.
61
20)Clique na célula H2.
21) Clique agora na opção Inserir da aba Desenvolvedor.
62
22)Escolha a opção Botão.
23)Agora clique novamente na célula H2.
63
24)Na janela que será aberta, selecione a macro ordenarmacro e clique em OK.
25)Se tudo ocorrer certo, um botão será criado, assim como na imagem abaixo.
64
26)Agora modifique os valores do Terceiro Trimestre.
27)Clique agora no botão criado anteriormente.
65
28)Se tudo ocorrer como o esperado, você terá o resultado abaixo.
29)Salve seu arquivo tendo como tipo Pasta de Trabalho Habilitada Para Macro de Excel.
66
Exercícios de Fixação Crie a tabela representada pela Imagem 1, logo após, grave uma macro tendo classificado na aba Dados a Quantidade de Visitas como Do Maior Para o Menor. Logo após isso, crie um botão e atribua a macro criada a ele, depois disso determine 600 visitas para Xsoluções. Então clique no botão para gerar um ranking de visitas. Você terá um resultado similar ao da Imagem 2. Imagem 1
Imagem 2
67
Aula 5 Funções Financeiras Funções financeiras do Excel equivalem praticamente à calculadora científica de bolso Hp 12c. O Excel é um editor que não nos possibilita o trabalho com juros simples, mas sim, juros compostos. Para isso que foram desenvolvidas as funções financeiras do Excel, ou seja, funções de auxílio para empresas que, por exemplo, devem cuidar de quarenta funcionários ao mesmo tempo. Dentre tantas funções financeiras existentes, vamos falar apenas das principais, ou seja, aquelas funções que são usadas diariamente por empresas e afins. Função VF Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.
No campo 1 decidimos a taxa da tabela, ou seja, a taxa de Juros por período solicitado. No campo 2 decidimos o Nper da tabela, ou seja, o número total de períodos de pagamento em uma anuidade. No campo 3 decidimos o Pgto da tabela, ou seja, o pagamento feito a cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e não contém nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá incluir o argumento vp. No campo 4 podemos decidir o Vp, ou seja, o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do argumento pgto será obrigatória. No campo 5 decidimos o tipo da tabela, ou seja, se o número 0 ou 1 indicar as datas de vencimento dos pagamentos. Se tipo for omitido, será considerado 0. Já no campo 6, podemos encontrar apenas uma breve descrição sobre a funcionalidade do campo selecionado.
68
Agora, para calcular o número de parcelas para efetuar o pagamento, podemos utilizar outra função, que é a Nper. Função Nper A partir de agora você poderá notar uma grande semelhança entre as funções, mas lembrese, cada função citada é designada para cada parte da tabela.
Assim como na função VF, no campo 1 podemos decidir as Taxas, ou seja, a taxa de juros por período. No campo 2 definimos o Pgto, ou seja, o pagamento feito em cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxas. No campo 3 definimos o Vp da tabela, ou seja, o valor presente ou atual de uma série de pagamentos futuros. No campo 4 podemos definir o Vf da tabela, ou seja, o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). No campo 5 definimos o Tipo da tabela, ou seja, é o número 0 ou 1 e indica as datas de vencimento. Assim como na função Vf, podemos também ver uma breve descrição do que significa cada campo, no qual é bem semelhante coma função Vf. A partir daqui vamos ter um breve conhecimento sobre outra função, que é a função Taxa.
69
Função Taxa Ela Retorna a taxa de juros por período de uma anuidade. Taxa é calculada por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!.
No campo 1 definimos o Nper, ou seja, o número total de períodos de pagamento em uma anuidade. No campo 2 podemos decidir o Pgto, ou seja, o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf. No campo 3 definimos o Vp, ou seja, o valor presente. O valor total correspondente ao valor atual de uma série de pagamentos futuros. No campo 4 podemos definir o Vf, ou seja, o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). No campo 5 definimos o Tipo, ou seja, é o número 0 ou 1 e indica as datas de vencimento .
70
Função Pgto Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante.
No campo 1 podemos encontrar a Taxa, ou seja, a taxa de juros por p or período. No campo 2 encontramos as taxas de Nper, ou seja, o número total de pagamentos pelo empréstimo. No campo 3 encontramos o Vp, ou seja, valor presente. O valor total presente de uma série de pagamentos futuros. No campo 4 encontramos o Vf, ou seja, o valor futuro, ou o saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). No campo 5 encontramos o Tipo, ou seja, é o número 0 ou 1 e indica as datas de vencimento.
71
Exercícios 1) Abra o Excel. 2) Crie uma tabela onde simule uma empresa de empréstimos.
3) Selecione a célula que corresponde ao total de pagamentos de Ivo Souza.
72
4) Clique sobre o botão de Inserir Função OK. OK.
e procure a função VF, VF, selecione-a e clique em
5) Vamos definir primeiramente o valor da Taxa de Ivo Souza, Souza, clique sobre a célula D4, D4, que corresponde aos 5% da taxa de Ivo Souza. Souza.
73
9) Clique agora em Nper, Nper, vamos definir o Número de Parcelas, Parcelas, para isso clique sobre a célula E4, E4, que corresponde ao número de parcelas do pagamento de Ivo Souza. Souza.
10) Clique agora em Pgto. Pgto. Vamos definir agora o valor do empréstimo, clique sobre a célula C4, C4, que corresponde ao total do valor a ser pago por Ivo Souza.
11) Clique em VP. VP. Como não teremos um valor presente para a tabela, em VP digite 0 e clique em OK. OK.
74
12)Clique 12) Clique em OK e veja o resultado.
13)Agora 13) Agora faça o mesmo com os demais funcionários.
Souza. 14)Clique 14) Clique agora no campo Taxa referente a Ivo Souza.
75
15)Clique sobre o botão de Inserir Função clique em OK.
e procure a função TAXA, selecione-a e
16) Na janela de Argumentos da função preencha os dados de acordo com a imagem abaixo, onde Nper é referente ao número de parcelas localizado em E4, Pgto é referente ao valor do empréstimo localizado em C4, Vp que é referente a valor presente é 0 pois o mesmo não existe e VF que é referente ao Total de Pagamentos localizado em C18. Logo em seguida clique em OK.
17) Se tudo ocorrer corretamente, você terá o mesmo resultado abaixo. Efetue o mesmo procedimento para os demais funcionários.
76
18) Clique agora na célula referente ao número de parcelas de Ivo Souza, essa seria a célula E18.
19) Clique sobre o botão de Inserir Função clique em OK.
e procure a função Nper, selecione-a e
77
20)Na janela Argumentos da função, preencha os campos da mesma forma demonstrada abaixo, sendo Taxa a taxa de juros aplicada na célula D4, Pgto o valor do empréstimo localizado na célula C4, Vp o valor presente que é zero e Vf o valor final que é representado pela célula C18. Em seguida clique em OK.
21) Se tudo ocorrer corretamente, você terá o mesmo resultado abaixo. Efetue o mesmo procedimento para os demais funcionários.
22) Salve seu arquivo.
78
Exercícios de Fixação Crie duas tabelas tomando por base os exemplos abaixo. Logo após, aplique na tabela Cálculo de Empréstimos - 1º Semestre as funções aprendidas em aula para calcular o Pagamento (Pgto), Taxa (Taxa) e Número de Parcelas (Nper).
79
Aula 6 Mala Direta Vamos seguir como exemplo a seguinte analogia: Você deseja mandar convites para 20 pessoas, querendo colocar o nome de cada pessoa no respectivo convite. Você deverá criar um modelo e uma tabela com os 20 nomes. Logo após você deverá criar uma Mala Direta no Word. A Mala Direta nos permite criar “etiquetas” ou “rótulos” para facilitar o trabalho no vínculo do Excel entre o Word. Em outras palavras Mala Direta significa Mailing (abreviação de Mailing List , em inglês), banco de dados onde se armazenam dados de consumidores (nome, endereços, características do consumidor, entre outros) para serem utilizados em marketing direto, tais como telemarketing e correio eletrônico. Lembre-se que antes devemos salvar a tabela criada na extensão .xls. Com isso, vamos poder importá-la para o Word sem maiores problemas. Seu objetivo é criar a estrutura do banco de dados que será utilizado para a convocação dos recém aprovados no concurso público da nossa Instituição . Ao iniciar a Mala direta você verá as seguintes opções:
No campo 1 podemos encontrar a opção Cartas, ou seja, a opção que simula uma ocorrência de carta, podendo ser colocada em um envelope com o seu destino. No campo 2 encontramos a opção Envelopes, que ao contrário da opção cartas, faz um envelope onde podemos rotular como destinatário e remetente. No campo 3 encontramos a opção Diretório, ou seja, o assistente nos cria um diretório para que possamos trabalhar melhor com as extensões. No campo 4 podemos selecionar um Documento Normal do Word, ou seja, com a extensão .doc. No campo 5 podemos selecionar um documento de Etiquetas, ou seja, apenas criar um rótulo para o documento já criado dentro do Excel. No campo 6 podemos encontrar as Mensagens de E-mail, ou seja, mensagens que podemos enviar pelo correio eletrônico com o destinatário de e-mail, cópias, dentre outros. No campo 7 esteja talvez a opção mais utilizada no Mala Direta, que é o Assistente passo a passo para conseguirmos montar uma Mala Direta explicando passo por passo.
80
Criando uma Mala Direta passo a passo Para criar sua mala direta, vá até a guia Correspondências e clique no botão Iniciar Mala Direta.
Em seguida clique em Assistente de Mala Direta Passo a Passo....
Então o painel do assistente da mala direta se abrirá á direta do Word 2010, seleciona a opção Cartas e clique em Próximo passo.
81
No próximo passo você deverá escolher uma forma para criar o conteúdo do texto da mala direta. Existem três opções: Usar documento atual (para criar seu próprio texto), Iniciar com base em um modelo (utilizar os modelos personalizados) e Iniciar com base em documento. Seguiremos com a opção Iniciar com base em um modelo, logo você deverá escolher um modelo previamente personalizado que atenda suas necessidades e seguir para o próximo passo.
No passo seguinte você definirá como os destinatários serão utilizados na mala direta. Existem três opções: Usar uma lista existente, Selecionar nos contatos do Outlook e Digitar uma nova lista. Para criar sua própria lista de destinatários selecione a opção Digitar uma nova lista e siga os procedimentos.
82
Depois de criar a lista de destinatários da mala direta, você deverá salvá-la em uma pasta segura.
Salvando a lista de destinatário.
83
No próximo passo você já poderá incluir os campos de mesclagem posicionando o mouse no local desejado e clicando em Inserir campo de Mesclagem no documento da mala direta. E por fim clique em Visualizar Resultados para que os campos mostrem as informações cadastradas na lista de destinatário:
84
Exercícios 1) Abra o Excel. 2) Crie uma tabela no similar a exemplificada abaixo.
3) Clique em Arquivo.
85
4) Clique em Salvar como.
5) Nomeie o arquivo como Word.xls e clique em Salvar.
6) Abra o Word.
86
7) Crie um documento dentro do Word contendo os respectivos campos demonstrados na imagem abaixo para vincularmos o documento à tabela criada no Excel.
8) Clique agora na aba Correspondências.
9) Clique em Mala Direta.
87
10) Selecione a opção Assistente de Mala Direta Passo a Passo....
11) Escolha primeiramente a opção Cartas e clique em Próxima: Documento Inicial.
12) Selecione agora a opção Usar o documento atual e clique em Próxima: Selecione os destinatários.
88
13) Clique agora em Usar uma lista existente e logo em seguida em Procurar...
14) Procure agora o arquivo Word.xls e clique em Abrir.
89
15) Selecione a Plan1 e Clique em OK.
16) Desmarque as caixas Cronograma Para Envio de Convites à Festa e Convidados , depois disso clique em OK.
90
17) Clique agora em Próximo: Escreva a carta.
18) Clique em Mais itens...
91
19) Clique agora em Inserir.
20) Agora clique em Fechar.
21) Agora clique em Local.
92
22) Clique em Mais itens...
23) Selecione F2 e clique em Inserir.
24) Agora clique em Fechar.
93
25) Clique agora em Dia da Semana:
26) Clique em Mais itens...
94
27) Selecione F3 e clique em Inserir.
28) Agora clique em Fechar.
95
28) Clique agora em Próxima: Visualize as cartas.
29) Se tudo ocorrer como o esperado, você terá o resultado abaixo.
30) Para finalizar, salve seu arquivo.
96
Exercícios de Fixação. Crie uma tabela simples dentro do Excel (como mostra a figura abaixo).
Em seguida crie dentro do Word os títulos, que irão receber cada informação cadastrada dentro da tabela feita no Excel. Aplique o uso de Mala Direta neste exemplo.
97
Aula 7 Gráficos Dinâmicos A criação de tabelas dinâmicas é um excelente recurso para movimentar dinamicamente as informações de linhas e colunas dentro de uma tabela, permitindo a exibição de campos selecionados, o cruzamento de dados, a aplicação de filtros e a realização de cálculos matemáticos e estatísticos, como por exemplo, a soma, o produto ou o desvio padrão em campos previamente determinados. Além de todas essas opções disponíveis, quando criamos um relatório de tabela dinâmica também podemos gerar gráficos dinâmicos, que seguem a mesma lógica da tabela, podendo ser dinamicamente alterados de acordo com as necessidades do usuário. Um gráfico dinâmico é um gráfico que pode ser usado dinamicamente, de acordo com os parâmetros que o usuário estipulou para o mesmo. Inserindo um gráfico dinâmico Para criar um gráfico dinâmico devemos primeiramente criar ou importar uma tabela dinâmica para dentro do Excel. A partir da tabela dinâmica vamos poder ter o acesso a todas as configurações necessárias para o gráfico dinâmico. Crie uma tabela dinâmica dentro do Excel.
98
Em seguida, você deverá aplicar todos os rótulos (títulos) para dentro da tabela assim como efetuado em aula.
Você poderá inserir qualquer tipo de gráfico que desejar par a tabela. Cada tipo de gráfico pode ser utilizado para cada tipo de tabela específica. Isso vamos ver na aula 8, que é um aula somente sobre gráficos.
99
Após inserir o tipo de gráfico, você poderá visualiza-lo dinamicamente.
Você poderá mover o gráfico de uma planilha para a outra, ou como bem preferir. Para isso, abra o menu rápido do gráfico e escolha a opção “Mover Gráfico”. Assim que você clicar na opção “Mover Gráfico” irá abrir a seguinte janela de diálogo.
No campo 1 você poderá selecionar uma nova planilha para abrir o gráfico. No campo 2 você poderá selecionar um planilha que já esteja em uso para poder utiliza-la.
100
Exercícios 1 – Crie uma tabela dentro do Excel.
2 – Selecione todas as células.
3 – Clique na aba Inserir.
101
4) Clique agora na opção Tabela Dinâmica.
5) Clique agora em OK para criar a Tabela Dinâmica.
6) Agora insira todos os rótulos.
102
7) Clique agora em Gráfico Dinâmico.
8) Selecione o tipo de gráfico Colunas Agrupadas e clique em OK.
9) Posicione a tabela como na imagem abaixo.
10) Agora salve o seu documento para ele possa ser usado como objeto de estudos posteriores.
103
Exercícios de Fixação Crie uma tabela dinâmica simulando o faturamento anual de uma empresa. Em seguida selecione-a e insira o gráfico de Colunas 3D Agrupadas.
104
Aula 8 Gráficos Gráficos são a tentativa de se expressar visualmente dados ou valores numéricos, de maneiras diferentes, assim facilitando a compreensão dos mesmos. Existem vários tipos de gráficos e os mais utilizados são os de colunas, os de linhas e os circulares. Os principais elementos são: números, título, fonte, nota e chamada. Em outras palavras, gráfico seria a maneira de transmitir informações através de índices para que o mesmo nos mostre um resultado. Tipos de Gráficos Quantas vezes você já ficou na dúvida sobre qual gráfico usar para os seus trabalhos e apresentações? Não fique preocupado, isso acontece com muita frequência. Afinal, o Excel 2010 possui várias opções de gráficos para você criar. Porém nem todos os gráficos podem ser aplicados à mesma situação. Antes de começarmos, você precisa saber sobre três conceitos importantes que constituem os gráficos no Excel 2010. O primeiro deles é o “ponto de dados”. Este elemento é representado pela combinação de dados obtidos de sua tabela e é o resultado também dos valores obtidos nos eixos X e Y. O segundo deles é a “série de dados”. Uma série de dados é composta por uma tabela de valores listados em colunas ou linhas. Cada coluna ou linha com valores é chamada de Série. O último conceito é o “marcador de dados”. Os marcadores de dados são elementos gráficos utilizados para representar os pontos de dados. Sabendo dessas informações, vamos aos tipos de gráficos. Colunas Sem dúvidas é um dos gráficos mais utilizados no Excel desde as suas primeiras versões. Este gráfico é bastante simples, afinal, compara valores em diferentes séries. Isto é, cruza as informações de acordo com a quantia presente em determinada categoria. Se você precisa informar seus recursos para a realização de uma tarefa durante a reunião para a liberação de verbas da sua empresa, este gráfico pode ajudar bastante. Sua função é separar em colunas verticais a quantidades de acordo com a categoria que foi atribuída ao valor. Este tipo de gráfico torna a coluna mista. Isto significa que os valores de cada categoria podem ficar no mesmo elemento gráfico. O que os diferencia é a cor da legenda, que sempre irá destacar um elemento do outro para que não haja confusões. Nem sempre os elementos gráficos serão colunas retangulares inseridas verticalmente, muitas vezes podem ser cones, cilindros e pirâmides seguindo o mesmo esquema do modelo original.
105
Pizza Na segunda posição dos gráficos mais utilizados pelos usuários vêm os gráficos de “Pizzas”. O nome já é bastante explicativo, mas para aqueles que nunca tiveram contato com este tipo de gráfico, vamos explica-lo brevemente. É preciso de duas ou mais categorias e um valor correspondente para cada uma delas. Nesta nova edição do Excel, o gráfico de Pizza ganhou uma novidade, sendo ela a opção “Pizza de pizza” e “Barra de pizza”. Quando já um grande valor secundário, este passa a ser exibido em uma barra ou pizza logo ao lado da principal, de maneira que o lado complemente a primeira demonstração.
Este modelo de gráfico torna mais fácil à visualização dos dados, afinal ele consegue dividir bem cada pedaço para uma determinada proporção. Assim, qualquer pessoa consegue dimensionar as quantidades referentes às categorias inseridas na tabela origem. Os gráficos de “pizza” ainda permitem que você enfatize alguma fração, porém só é possível fazer em gráficos “pizza” 3D.
106
Os gráficos secundários “pizza de pizza” e “barra de pizza” são resultado da extração de dados da primeira “pizza” e têm a finalidade de destacá -los. Normalmente a primeira “pizza” apresentará grupos bem distintos, sendo que um deles será destacado – normalmente, é o que possui o maior número dentro da sequência de dados. A especificação dos valores que compõem a fatia maior da “pizza” primária estará na barra ou na “pizza” secundária. Barras Os gráficos de barra assim como os de coluna e “pizza” são os mais utilizados. Isso porque os três possuem uma disposição que facilita muito na compreensão dos dados ali representados. O funcionamento deste tipo de gráfico pouco difere do gráfico em colunas. Isto porque os dois trabalham com informações lineares que podem ser compreendidas horizontalmente – no caso dos gráficos em barras. O uso deste tipo de gráfico é aconselhado quando é preciso trabalhar com rótulos muito longos ou então os eixos utilizados estão relacionados ao tempo de duração de alguma experiência. O gráfico em barras é bastante utilizado em apresentações de pesquisa de intenção de votos ou opinião pelas redes de televisão.
107
Este tipo de gráfico também possui subtipos, que, são os mesmos do gráfico em colunas. Os subtipos são: Barras agrupadas e barras agrupadas em 3D: exibem três categorias colocadas no eixo vertical e os valores no eixo horizontal – muito útil para comparação rápida de valores. Barras empilhadas e barras empilhadas em 3D: reúnem os valores das categorias em uma só barra que ilustra muito bem a visão total de um determinado item. As cores de cada legenda são separadas de maneira bem evidente. Barras 100% empilhadas e barras 100% empilhadas em 3D: os gráficos exibidos desta maneira compreendem a contribuição de cada valor para a soma de todos eles dentro de uma categoria. Cone, cilindro, pirâmide horizontais: são apenas formatos de barras diferentes que podem ajudar a diferenciar um tópico representado de outro em um relatório ou estudo. Estas formas também podem ser apresentadas em 3D, agrupadas, empilhadas e 100% empilhadas.
108
Dispersão (XY) Os gráficos de dispersão XY, como se pode entender diretamente do nome, trabalham com eixos específicos e pontuam quando os dados numéricos se cruzam. Este gráfico é específico para a representação de várias séries de dados. É interessante usá-lo em situações em que é preciso mostrar como determinadas sequências (ou séries) podem ser parecidas e por isso, agrupadas, fazer uma comparação de dados sem preocupações com o eixo de tempo (caso exista um), alterar a escala do eixo horizontal ou torná-lo uma escala logarítmica. É importante lembrar que o gráfico de dispersão (XY) precisa de pares de valores para trabalhar. Se não houver valores em par, é impossível determinar um ponto.
109
Assim como todos os outros gráficos do Excel 2007, o de dispersão (XY) possui subtipos de gráficos que consistem nos gráficos de dispersão com linhas suaves com marcadores e dispersão com linhas suaves que servem para evidenciar a ligação entre os pontos determinados na área de marcação do gráfico. A diferença entre o primeiro subtipo e o segundo está nos ícones exibidos ao longo da linha traçada. No primeiro existe a marcação do ponto original seguido de linha. No segundo subtipo existe apenas a linha seguindo o trajeto descrito pelos pontos (agora invisíveis).
Ainda existem outros dois subtipos de gráficos de dispersão: o de dispersão com linhas retas e o de dispersão com linhas retas e marcadores. Os dois têm a mesma função dos subtipos anteriores, porém as linhas deste modelo são mais fortes e destacadas. Assim, a ligação entre os pontos fica mais evidente a partir da conexão das linhas angulosas e retas. Rosca Gráficos de rosca não são fáceis de ler. Muitas vezes, os dados podem parecer confusos principalmente quando se usa mais de uma série (ou sequência) para produzi-lo. Sempre que houver mais de uma sequência no gráfico de rosca, as subsequentes ficarão na parte interna do anel principal ou primeira sequência. É por esta razão que o gráfico torna-se confuso, quanto mais séries na parte interior, mais difícil fica organizar a leitura dos dados. O funcionamento dele é muito semelhante ao do gráfico “pizza”, uma vez que existem fatias para relacionar todas as partes do inteiro.
110
É importante lembrar que cada anel fecha o valor de 100%, caso você opte por exibir as porcentagens junto aos rótulos de cada valor e categoria. O anel vai se dividir de acordo com o número de categorias que você inserir na sua tabela de origem. O gráfico de rosca também possui um subtipo que é o Rosca destacada. Assim como o “pizza”, ele permite enfatizar determinados valores dentro do círculo de maneira manual, ou seja, clicar e arrastar.
Área Os gráficos em área são ótimos para destacar mudanças e oscilações de uma categoria de acordo com o tempo, por exemplo. Se você precisa demonstrar a evolução das vendas de um produto ou então o tempo de desenvolvimento de um projeto, este modelo pode representar melhor as suas informações. Por mais que pareça impossível, este gráfico tem um ponto em comum com o de pizza e o de rosca – os três representam partes de um todo.
Assim como as demais opções de gráfico, este tipo também conta com diversos subtipos em duas ou três dimensões, empilhados e 100% empilhados. Porém se você for trabalhar com um gráfico de apenas uma série, pode ser mais adequado utilizar o gráfico de linhas para que todas as informações apareçam sem interferência de outras categorias. Este problema já não ocorre com os gráficos de área empilhada e 100% empilhada em 2D ou 3D. Assim como as barras e as colunas que podem ser empilhadas, o gráfico de área exibirá as séries ou categorias umas sobre as outras em que uma fique oculta.
111
Linhas Os gráficos de linha são aqueles que aprendemos a fazer no colégio e costumamos ver em filmes, desenhos e até revistas em quadrinhos. Isto porque eles são ótimos para representar sequências de dados em uma escala de tempo dividida em períodos iguais. Normalmente, no eixo horizontal temos a divisão do tempo em dias, meses ou qualquer unidade de tempo (em casos em que se está trabalhando com assuntos que envolvam tempo) e no eixo vertical os valores.
112
As linhas deste gráfico são ideais para representar várias séries, porém se você estiver trabalhando com apenas uma, prefira usar um gráfico que exiba as categorias com mais exatidão como barras ou colunas. Os rótulos são bem evidentes neste tipo de gráfico quando a quantidade deles é inferior a dez. Caso você tenha um número superior a este, prefira gráficos de dispersão, assim seus pontos poderão ser vistos sem interferências.
Este tipo de gráfico também conta com subtipos que podem ajudar na exibição de dados individuais, tendências temporais, categorias e outros elementos. No caso dos dados individuais ou pontos, as linhas ajudam muito para guiar o observador pelo trajeto que os pontos descrevem, caso sejam muitos. Assim, pode-se escolher entre os subtipos linha empilhada e linha empilhada com marcadores, linha 100% empilhada e linha 100% empilhada com marcadores. Todos eles compartilham da mesma finalidade do gráfico de linhas normal. Ainda existe o gráfico de linha em 3D. Este gráfico trabalha com três eixos (vertical, horizontal e profundidade) que podem ser alterados manualmente.
113
Ações Já os gráficos de ações são específicos para o controle e movimentação deste tipo de flutuação de valores. Entretanto, ele não restringe seu funcionamento apenas para questões financeiras e mercadológicas. É possível utilizar o gráfico de ações para detalhamentos científicos. Para isso, as informações que você deseja representar devem estar na ordem correta, ou seja, atendendo à sequência: Alta-Baixa-Fechamento ou os dados necessários para completar as variáveis dos outros subtipos deste modelo de gráfico.
É importantíssimo saber que se os dados não forem inseridos em colunas nomeadas corretamente, o gráfico não poderá ser feito. Assim, você deve colocar os dados em sequência de cima para baixo em cada tópico como no exemplo abaixo.
Os subtipos de gráfico de ações são os seguintes:
Alta-baixa-fechamento: Representa três séries contendo as variações de preço das ações. Os dados devem obedecer à ordem correta como é vista no nome do gráfico. Abertura-alta-baixa-fechamento: Funciona da mesma maneira que o subtipo acima porém com quatro séries. Este modelo também pede que os dados sigam a ordem correta. Volume-alta-baixa-fechamento: Também requer quatro séries preenchidas de maneira correta seguindo a ordem dos dados. Volume-abertura-alta-baixa-fechamento: O mais completo dos subtipos de gráficos de ações, este modelo trabalha com cinco séries e assim como os outros, deve conter valores que sigam a ordem correta.
114
Superfície Se você precisa encontrar quais combinações valem mais a pena os gráficos de superfície são os mais recomendáveis. O aspecto visual dele se assemelha muito a um mapa topográfico com diferentes níveis, cores e texturas para identificar quais valores pertencem ao mesmo grupo de informações ou intervalo numérico. O uso deste tipo de gráfico pede uma situação em que ambas as categorias e séries de dados são números. Assim, fica fácil encontrar seus pontos em comum.
Os subtipos de gráficos de superfície são quatro:
Superfície 3D: A curva exibida por este subtipo de gráfico contém a tendência desses números enquanto as cores representam as diferenças entre os valores determinados por você. É perfeito para as situações em que o volume de dados é muito grande e qualquer outra opção de gráfico dificultaria a visualização das informações. Superfície 3D delineada: É “esqueleto” do modelo anterior. Por isso é muito difícil compreender as linhas acabam se confundindo como se fosse a armação de metal de um edifício. Contorno: Este modelo lembra muito um mapa topográfico porque as faixas de cor representam os pontos que tem valores iguais, de maneira que fiquem dispostos em formas concêntricas. Contorno delineado: Assim como o modelo anterior, este subtipo de gráfico lembra mapas topográficos. Porém, o subtipo “contorno delineado” tem muito a ver com a “superfície 3D delineada”, já que mostra o que há por trás do “contorno”. E assim como o outro gráfico delineado, este também é de leitura complicada.
Bolhas O gráfico de bolhas é um tipo de gráfico que consegue determinar bem os valores determinados pelo usuário. Isso porque ele, assim como o gráfico de ações, exige uma ordem de inserção de dados. Na primeira coluna devem estar os dados que estarão no eixo X, enquanto os dados do eixo Y devem estar na segunda coluna e o tamanho da bolha (normalmente representa a porcentagem) na terceira. Este tipo de gráfico possui apenas uma variante ou subtipo que é o de bolhas 3D. A única diferença é visual, porque o gráfico continua trabalhando com conjuntos triplos de valores.
115
Radar Os gráficos radares adquirem a sua forma de acordo com a quantidade de categorias que você enumera. Cinco categorias resultarão em um pentágono, três, em um triângulo e assim por diante. Este tipo de gráfico ficou conhecido entre os jovens a partir de jogos de RPG (Role Playing Games) e do famoso game Winning Eleven. Pois bem, este gráfico cabe tanto para os jogos quanto para a vida profissional. Isso porque ele é capaz de comparar várias séries de dados ao mesmo tempo. Quanto mais próxima a linha fica do vértice, maior o valor do dado que o usuário inseriu. Nos jogos, o usuário está acostumado a ver a forma geométrica (geralmente um pentágono), porém nos gráficos do Excel é possível inserir quantas categorias (vértices) for necessário. As linhas também são normalmente apenas o traçado simples, porém no editor de planilhas e gráficos é possível preenchê-las e inclusive posicionar marcadores ao longo das linhas.
Agora você já sabe todos os tipos de gráficos existentes no Excel. Você poderá usar cada um de acordo com a sua necessidade.
116
Exercícios 1) Abra o Excel. 2) Crie de médias similar a imagem abaixo.
3) Selecione toda a tabela e clique em Inserir.
117
4) Clique no menu botão para Criar Gráfico.
4) Será aberta a janela Inserir Gráfico, selecione o gráfico Colunas 3D Agrupadas e clique em OK.
118
7) Pressione agora a tecla F11 para visualizar o gráfico em Tela Cheia.
8) Salve seu arquivo.
119
Exercícios de Fixação Crie uma tabela similar a Figura 1, utilize ela para criar um gráfico em Colunas Agrupadas, similar ao da Figura 2, para visualizar o crescimento da empresa. Figura 1
Figura 2
120
Aula 9 Formulários Personalizados Parte 1 Ao trabalhar em um software, constantemente nos deparamos com caixas de diálogo para enviar informações ao computador, configurando as opções desejadas e, quando as confirmamos, os dados são processados conforme nossa escolha. Um exemplo claro é a guia Exibir da caixa de Opções, que pode ser acessada escolhendo Ferramentas/Opções. Ela possui caixas de seleção na parte superior e inferior, como em opções de janela, contendo, dentre outras, as caixas Quebra de páginas e Fórmulas, que são controles dependentes apenas deles, ou seja, se você marcar a caixa, para desmarcar é preciso clicar nela mesmo. Também nessa caixa, há outros controles como o grupo de opções de objetos, cujas opções são dependentes umas das outras. Por exemplo, se escolher Ocultar tudo, os outros botões ficarão desmarcados, assim como se escolher outro botão, os demais ficarão desmarcados, pois em um grupo de opções somente uma delas poderá ser escolhida. Existem também outros controles, como as caixas de seleção, também chamadas de caixas de verificação, como a barra de fórmulas, que depende somente dela para estar ou não selecionada, caixas de combinação, como aquela em que você escolhe a fonte de uma célula, dentre outros. Um desafio para os desenvolvedores de planilhas é tornar fácil e interativo o acesso aos dados, de forma que o usuário não se perca. Imagine alguém que irá consultar dados em uma planilha, mas não sabe onde eles estão nem como fazer operações com eles. Para situações como esta podemos usar ferramentas que permitem criar uma tela em que o usuário não precise conhecer os comandos do Excel para operar, apenas clicando em botões, tornando o acesso aos dados interativo. –
Criando um formulário passa a passo- Parte 1 Essa foi a tabela criada em aula.
Dividimos essa área de trabalho para duas planilhas: Export e Consulta.
121
Selecione a planilha consulta, pois nela será criado o formulário, e siga estes passos para inserir o primeiro controle da planilha.
Na barra de ferramentas Formulário, clique no botão Caixa de combinação. Note que o mouse se transformou em uma cruz preta. Posicione o mouse em qualquer célula da planilha, arraste e solte para desenhar um objeto. Ferramentas do Formulário
Botão criado na planilha Consulta
Botão Caixa de combinação Agora vamos ver algumas técnicas que nos possibilitam editar os controles. Você criou uma caixa de combinação, que é um objeto sobrepondo as células. Antes de conhecer as funcionalidades dessa caixa, você deve algumas técnicas para editar o objeto, que também servirão para o demais e para outros objetos que você inserir na planilha, inclusive figuras e clip-arts. Para editar um objeto, é necessário que ele esteja selecionado. Enquanto o objeto estiver selecionado, surgem oito alças de redimensionamento, nos quatro lados e nos quatro cantos. Se surgirem somente seis, é devido ao tamanho do objeto. Como este é um botão de formulário no qual podem ser inseridos itens, ao posicionar o mouse sobre ele, este se transforma em uma mão. Quando clicar, por se tratar de uma caixa de combinação, será aberta uma lista com os dados de origem do objeto, que no momento não existem.
122
Alças de redimensionamento Vamos começar a elaborar o formulário, nosso primeiro passo será alterar o nome dos intervalos, ou seja, vamos selecionar os três intervalos para alterar seus respectivos nomes.
Aqui podemos alterar os intervalos, para isto você deve selecionar o intervalo desejado, logo após basta você cadastrar um novo nome. Você poderá inserir comentários a este novo nome, com também poderá alterar o intervalo. Não se esqueça de selecionar a planilha a qual este intervalo pertence.
Caixa de Combinação A caixa de combinação que inserimos na planilha é um controle que tem como objetivo exibir os valores de uma lista, permitindo ao usuário escolher um deles. O nosso objetivo é listar os nomes dos países do intervalo nomeado como Países. Para isso é preciso abrir a caixa Formatar controle do objeto. Para exibir essa caixa, com o objeto selecionado, siga um destes métodos, cujas técnicas servem também para os demais controles do formulário. Para acessar as propriedades do botão, clique com o botão direito sobre ele e escolha a opção Formatar.
123
No campo 1 encontramos o Intervalo de entrada, ou seja, essa caixa deve conter a origem dos dados a serem listados pelo controle. Como o nosso objetivo é listar os países, vamos preenchê-lo como Países, que o intervalo renomeado na planilha Export. No campo 2 encontramos o Sombreamento 3D, ou seja, se você deseja que seu botão tenha uma aparência tridimensional, ative-o. No campo 3 encontramos as Linhas suspensas, ou seja, o número de itens a serem exibidos ao clicar no botão. Se a lista tiver mais itens do que o especificado, surgirá uma barra de rolagem. E no campo 4 encontramos o Vínculo da célula, ou seja, é uma célula, que também pode ser de outra planilha, que conterá o número da posição do item escolhido na lista. Essa célula é de fundamental importância, pois será a base de todas as fórmulas a serem criadas de acordo com o item escolhido. Feito isso, tire a seleção da caixa de combinação, clicando em uma célula qualquer, e clique novamente nela. Será exibida a lista dos cinco primeiros países. Como há mais, surgirá uma barra de rolagem que lhe permite exibir os demais.
Barra de Rolagem
124
Agora devemos inserir os valores referentes a cada país. Devemos inserir uma fórmula que retorne o valor das exportações do país escolhido. Este valor está no intervalo nomeado como Total. Para obter este valor vamos utilizar a função Índice, que retorna o valor de uma célula segundo a posição que a célula ocupa na matriz.
Função Índice Agora para consultar o valor de outro país, escolha na lista.
125
Exercícios 1) Abra o Excel.
2) Crie uma tabela tendo como exemplo a imagem abaixo e salve com o nome de Pasta1.
3) Renomeie a planilha 1 para Totais e a segunda para Formulário.
126
4) Selecione a planilha Formulário.
5) Clique na aba Desenvolvedor.
6) Clique em Inserir.
127
7) Clique agora em Caixa de Combinação.
8) Clique na célula D2 e arraste até a célula F2.
128
9) Volte agora à planilha Totais.
10) Selecione a coluna Estilos Musicais.
129
11) Selecione a aba Fórmulas.
12) Clique agora em Definir Nome.
13) Determine o nome como Estilos e clique em OK.
130
14) Selecione agora todos os valores de Julho à Dezembro.
15) Clique agora em Definir Nome.
131
16) Determine o nome como Valores e clique em OK.
17) Selecione agora os totais.
132
18) Clique agora em Definir Nome.
19) Determine o nome como Totais e clique em OK.
20) Agora clique na planilha Formulários.
133
21) Clique agora na aba Desenvolvedor.
22) Clique em Propriedades.
134
23) Determinaremos primeiro o Intervalo de Entrada que será a coluna Estilos da planilha Totais, para isso, clique no botão destacado abaixo.
24) Clique agora na planilha Totais.
135
25) Agora clique na célula B4 e arraste até a célula B13.
26) Clique agora no botão indicado.
136
27) Agora para Vínculo de célula digite J2.
28) Para Linhas suspensas, determine o valor de 5 e clique em OK.
29) Clique agora na célula B4.
137
30) Digite Total. 31) Clique agora na célula C4.
32) Agora digite =INDICE(Pasta1!Totais;J2) e pressione a tecla Enter.
33) Agora clique no menu Drop Down indicado abaixo.
138
34) Selecione a opção Pop.
14) Veja o resultado, o total de vendas do estilo Pop foi exibido em nosso campo Total.
15) Salve o projeto.
139
Exercícios de Fixação Para dar início ao nosso formulário personalizado, vamos criar apenas uma parte da tabela, pois a conclusão da mesma será feita na próxima aula. Crie uma tabela similar a representada abaixo e salve com o nome de Pasta1.
Com a ajuda da aba Fórmulas, adicione nomes para os Modelos como Modelos, as quantidades em estoque no decorrer dos meses como Estoque e os Totais como Totais, é de vital importância para o correto funcionamento do formulário que você adicione esses nomes, pois a partir deles nossa fórmula irá se desenvolver. Em seguida renomeie a Plan2 para Total e crie um caixa de combinação. Com a ajuda da opção Propriedades, defina o Intervalo de Entrada, Vinculo de Célula e Linhas Suspensas.
140
Em seguida selecione uma célula qualquer e nomeie-a como Total de Camisas Vendidas. Em seguida insira a fórmula =ÍNDICE(Pasta1!Totais;J2), em seguida selecione o modelo Social pelo menu Drop Down e observe o total de camisas sociais em estoque.
141
Aula 10 Formulários Personalizados Parte 2 Vamos dar continuidade à aula anterior, onde estávamos falando sobre algumas técnicas para edição e criação de um formulário personalizado. –
Botão de Opção Vamos conhecer a funcionalidade de outro controle, que é o botão de Opção. Na barra de ferramentas do formulário, clique no botão de opção e depois arraste o mouse sobre as células que você deseja desenhar o botão. Após soltar o mouse, surgirá um objeto com o rótulo de “botão de opção” seguido de um número, que não te m a menor importância qual for.
Botão de Opção
Botão de Opções
O processo deve ser o mesmo para os demais meses do primeiro semestre do ano, ou seja, vamos criar um botão para rotular Janeiro, Fevereiro, Março, Abril, Maio e Junho. Após isto vamos renomeá-los para o devido nome do mês.
142
Em um grupo, apenas um botão poderá estar selecionado. Lembre-se, assim como antes, formatamos este controle e vinculamos a ele a célula G2, ou seja, a célula G2 será a célula que irá executar o retorno da função. Na planilha Export, o intervalo nomeado como valores contém o valor de cada mês. Agora o objetivo é inserir uma fórmula que retorne o valor referente ao país escolhido na caixa de combinação no mês escolhido no grupo de opções.
143
Está fórmula também foi elaborada com a função Índice. A matriz é Valores, que é o intervalo contendo o valor de todos os meses. Como a matriz possui oito linhas e seis colunas, é necessário definir o número da linha e da coluna. O segundo argumento, número da linha, é definido pela escolha do País na caixa de combinação. Então, como essa caixa está vinculada à célula G1, esta será a referência. Como há vários meses na lista, quando clicar no botão de opção do mês a consultar , o número do botão será inserido em G2, que é o valor do segundo argumento da função, número da coluna. Então, a escolha do País define o número da linha e do mês.
Aparência do Formulário Podemos utilizar os recursos de formatação do Excel para que os dados sejam facilmente interpretados, inclusive inserindo figuras, clip-arts, utilizando a barra de ferramentas chamada Desenho, dentre outras formas. Neste formulário, vamos inserir cores e bordas que são consideradas típicas de um formulário, ou seja, vamos lhe mostrar as opções mais simples, porém mais utilizadas dentro de um formulário. Bordas Externas é o tipo de borda mais utilizado em formulário. Ao contrário das outras bordas, as bordas externas têm como função dar um “contorno” à tabela, ou seja, ela não cria bordas dentro da tabela, mas sim dá um contorno para ela se diferenciar do resto da planilha do Excel.
144
Em respeito as cores, vai do gosto de cada programador, mas a cor mais utilizada para este processo é uma variação do tom de cinza, como descreve a figura abaixo.
Variação de Cinza Não podemos nos esquecer também de por um título no formulário. O título é de extrema importância, pois é com ele que podemos localizar e saber diferenciar um formulário do outro.
145
Exercícios 1) Abra o arquivo referente ao exercício da apostila aula passada.
2) Como você pode ver, você está na planilha Formulários. Vamos criar agora um botão de opção referente ao mês de Julho, portanto, clique na aba Desenvolvedor.
146
3) Selecione agora a opção Inserir.
4) Clique agora em Botão de Opção.
5) Agora clique na célula E5 e arraste até F5.
147
6) Como você pode ver, foi criado nosso botão, vamos agora modificar seu nome, de um duplo clique no botão.
7) Renomeie o botão para Julho.
148
8) Faça este mesmo processo com mais cinco botões de Opções, que serão intitulados como Agosto, Setembro, Outubro, Novembro e Dezembro.
149
9) Agora clique com o botão direito sobre o mês de Julho e selecione a opção Formatar Controle.
10) Para vínculo, determine a célula J1 e clique em OK.
11) Crie um outro título na célula B5, e o nomeie como Valor Mensal.
150
12) Vamos agora renomear o intervalo de meses de nossa planilha Totais, para isso clique em Totais.
13) Clique agora na aba Fórmulas.
14) Selecione agora todo o intervalo de células referente aos meses, portanto, selecione a célula C4 e arraste até H13.
151
15) Clique agora em Definir Nome.
16) Defina o nome do intervalo para Meses e clique em OK.
17) Agora volte à planilha Formulários clicando no local indicado abaixo.
152
18) Clique agora na célula C5.
19) Digite agora =ÍNDICE(Totais!Meses;J2;J1) e pressione Enter.
20) Como você pode ver, foi atribuído o valor de Julho do estilo Pop. Clique duas vezes agora sobre a Plan3.
153
21) Digite Dados e pressione Enter.
22) Clique agora na planilha Totais.
154
23) Clique agora na célula B2 e arraste até I13.
24) Clique agora com botão direito sobre a seleção e clique em Copiar.
25) Agora clique sobre a planilha Dados.
155
26) Clique agora na célula B2 e arraste até I13.
27) Agora clique com o botão direito na seleção e escolha a opção de colagem destacada abaixo.
28) Clique agora sobre a célula C4 e arraste até a célula H13.
156
29) Agora clique em Definir Nome.
30) Coloque o nome como Mesesd, pois estamos calculando o controle de vendas da tabela Dados e clique em OK.
31) Agora clique na célula I3 e arraste até I14.
157
32) Clique agora em Definir Nome.
33) Coloque seu nome agora como Totald e clique em OK.
34) Agora clique na planilha Formulários.
158
35) Agora ative a aba Desenvolvedor.
36) Agora clique em Inserir.
37) Selecione a opção Caixa de Grupo.
159
38) Agora clique entre as células E4 e E5 e arraste até a célula F10.
39) Agora de um duplo clique sobre o nome da caixa.
40) Renomeie a caixa para Mês e pressione a tecla Enter.
160
41) Selecione toda a Formulários e insira o título Formulário a ela, aplique logo em seguida a formatação exemplificada abaixo.
42) Agora clique na célula C5.
43) Digite agora =ÍNDICE('Dados!Mesesd;J2;J1) e pressione Enter para tomarmos como base agora a planilha Dados.
161
44) Agora clique na célula C4 e digite =ÍNDICE(Dados!Totald;J2), logo em seguida pressione a tecla Enter.
45) Agora vamos efetuar alguns teste, clique sobre o menu drop down e selecione o estilo Rock.
46) Como você pode ver, os valores mudaram, agora vamos verificar o valor do mês de Setembro, para isso, clique em Setembro.
47) Salve seu arquivo.
162
Exercícios de Fixação
Abra o arquivo que foi criado no exercício de fixação da aula anterior. Botões de opção para todos os meses dentro da planilha Formulário. Em seguida crie uma caixa de Grupo e insira ao redor dos botões de opções renomeando a mesma para Mês. Em insira o layout abaixo.
163
Aula 11 Gráficos de Gantt O diagrama de Gantt (ou mapa de Gantt) é um gráfico usado para ilustrar o avanço das diferentes etapas de um projeto. Os intervalos de tempo representando o início e fim de cada fase aparece como barras coloridas sobre o eixo horizontal do gráfico. Desenvolvido em 1917 pelo engenheiro social Henry Gantt, esse gráfico é utilizado como uma ferramenta de controle de produção. Nele podem ser visualizadas as tarefas de cada membro de uma equipe, bem como o tempo utilizado para cumpri-la. Assim, pode-se analisar o empenho de cada membro no grupo, desde que os mesmos sejam associados, à tarefa, como um recurso necessário ao desempenho da mesma. Associado a esta ideia, está o fato de esta forma de representação gráfica, das atividades de um projeto, permitir, ainda, avaliar os seus custos, resultante do consumo de recursos necessários à conclusão de cada uma das tarefas do mesmo. A forma de balizar o desempenho do projeto, por medição relativa entre o tempo decorrido, e o grau atual de conclusão da tarefa, perante o previsto, e a partir do diagrama de Gantt, permite tirar conclusões sobre o seu desempenho em termos de custo e prazo. Uma das técnicas de balizamento, mais frequentes, e utilizadas para aquele efeito, é chamado de EVM ( Earned Value Management ).
164
Inserindo um gráfico de Gantt Na primeira coluna de sua planilha, a partir da célula A2, escreva a lista de tarefas ou etapas do projeto em questão. Na segunda coluna, a partir de B2, escreva as datas ou momentos de início de cada fase. Na terceira coluna, a partir de C2, digite as datas de término de cada etapa. Na primeira linha da planilha, a partir de D1, escreva a sequência de datas ou intervalos (horas, meses, anos) a serem ocupados por todo o projeto. Logo após insira um gráfico de barras. Formate-o para que fique dentro do padrão de Gantt.
165
Exercícios 1) Crie uma tabela tendo como base a imagem abaixo.
2) Clique agora em B4 e arraste até I13. I13.
Inserir. 3) Selecione agora a aba Inserir.
166
4) Clique em Gráfico de Barras. Barras.
5) Selecione agora o gráfico de barras empilhado.
6) Posicione o gráfico ao lado da tabela.
167
7) Selecione agora uma das barras azuis.
8) Clique com o botão direito agora em alguma das barras azuis e selecione a opção Formatar Serie de Dados. Dados.
9) Selecione agora a aba Preenchimento. Preenchimento.
168
10) Selecione a opção Sem preenchimento e clique em Fechar. Fechar.
11) Clique agora nas legendas.
169
12) Pressione agora a tecla Delete e visualize o gráfico criado.
170
Exercícios de Fixação Tome como base este gráfico. Monte uma tabela para que você tenha o resultado semelhante ao do gráfico.
171
Aulas 12 e 13 Projeto Final Tendo em vista alguns dos principais aspectos mostrados na aula, preparamos para você uma aula em que você poderá criar seu próprio projeto através dos conhecimentos adquiridos em aula. Para começar qualquer formulário, você tem que ter bem em mente do quão longe você quer chegar, ou seja, você tem que ter bem claro em sua mente qual o tipo de formulário que você deseja desenvolver. Basicamente um formulário é subdividido em três aspectos: Esboço Dentro do esboço você poderá definir alguns parâmetros de layout para a apresentação do formulário. Nele você deverá deixar bem claro alguns aspectos como cores, fontes, bordas, alinhamentos, número, reais, dentre outros. Desenvolvimento Dentro do desenvolvimento você terá de colocar fórmulas e funções para que seu formulário possa executar as ações propostas pelo usuário. De tal modo, o desenvolvimento é a parte em que você tira as suas ideias do papel e passa a programá-las dentro do Excel. Conclusão A conclusão talvez seja a parte mais importante dentro do formulário, pois é nela que vamos testar tudo o que fizemos anteriormente dentro do formulário. Após criar o layout, passar todo o desenvolvimento de conteúdo, funções e fórmulas, é preciso confirmar que tudo está realmente funcionando dentro do formulário. Tendo isto como base, podemos desenvolver qualquer tipo de formulário. Do mais simples ao mais complexo, dentre outros. Durante a execução da aula, seguimos como base alguns passos para que se tornasse mais fácil à utilização das ferramentas dentro do formulário. Como exemplificado em aula, utilizamos como base de exemplo um formulário sobre o controle interno de uma locadora de vídeo.
172
Antes de você começar a se preocupar com funções e fórmulas, preocupe-se em ter um layout bem definido, para que possa facilitar o seu trabalho e também para que fique mais fácil a utilização de funções e afins. Dentro do projeto utilizamos seis planilhas diferentes, onde quatro delas usamos para fins do próprio projeto e as outras duas utilizamos como banco de dados, para que pudessem receber os cadastros de filmes e clientes. Tela de Abertura Dentro dela gravamos três macros para executar as três tarefas que estavam descritas nos botões. Uma macro foi designada para abrir a planilha Locar, que era o “centro” do formulário, onde poderíamos gerar o filme locado pelo cliente e concluir o processo de locação. A segunda macro foi designada para abrir o cadastro de clientes, ou seja, todos os clientes novos teriam que ter um cadastro dentro de nossa locadora, para isso bastávamos inserir o seu nome e telefone. Feito este processo o cliente estava apto para locar os vídeos na locadora. A terceira e última macro, foi designada para que pudéssemos cadastrar os novos filmes dentro da locadora, ou seja, todo e qualquer filme teria que ter um cadastro específico, para que o nosso cliente pudesse locá-lo para assisti-lo em casa.
173
Planilha Locar Desenvolvemos a planilha Locar com o único intuito de que ela fosse a planilha principal dentro do projeto. A planilha Locar será a responsável por todo o processo de locação dos Filmes, desde a escolha do cliente até a conclusão da locação. Dentro dela utilizamos algumas Macros, para que depois pudéssemos atribuí-las aos botões e também utilizamos a função PROCV. PROCV. Utilizamos a função para que pudéssemos encontrar o valor e o gênero de cada filme, tendo como base uma coluna com o cadastro das mesmas.
Planilha Clientes Esta planilha criamos para que ela seja somente a responsável pelo cadastro dos novos clientes, para que os mesmos possam p ossam locar filmes dentro da nossa Vídeo locadora. Para fazer o cadastro dos novos clientes, utilizamos a Macro para vincular o cadastro ao banco de dados, assim também como utilizamos a Macro para vincular ao botão de Incluir. Inserimos também um campo designado ao telefone do cliente. Assim como fizemos com o nome do cliente (onde tivemos que gravar uma macro para que depois pudéssemos vinculala ao banco de dados), fizemos com o campo Telefone do cliente.
174
Planilha Filmes Esta planilha foi desenvolvida com o mesmo intuito da planilha Clientes, porém a sua única diferença será que esta planilha ficará direcionada aos filmes e não aos clientes. Dentro desta planilha poderemos cadastrar os filmes pelo seu nome e gênero.
175
E por fim, criamos duas planilhas para servirem de banco de dados aos filmes e clientes, ou seja, tudo o que cadastrarmos na planilha Filmes e Clientes irão cair dentro do banco de dados.
176
Exercício 1
–
Criação de Layout
1) Crie um documento do Excel e dentro dele crie 5 planilhas: Inicial, Inicial, Principal, Principal, Clientes, Clientes, Livros, Livros , BD_Clientes, BD_Clientes, BD_Livros como demonstrado abaixo.
2) Primeiramente, criaremos um layout para a planilha Inicial, Inicial , para isso, clique no local indicado abaixo.
177
3) Agora clique em Cor de Preenchimento.
4) Selecione a cor destacada abaixo.
5) Agora iremos inserir alguns botões e imagens. Clique na aba Inserir.
178
6) Agora clique em Imagem.
7) Vamos utilizar como exemplo essa bela imagem de um livro, mas você pode utilizar qualquer imagem que desejar ao desenvolver o seu projeto. Clique sobre a imagem Livro.png e clique em Inserir.
179
8) Agora arraste um pouco nossa imagem, clicando no primeiro ponto e arrastando até o segundo, como demonstrado abaixo.
9) Vamos colocar um efeito nessa imagem, clique na opção destacada abaixo.
10) Agora vamos voltar a aba Inserir para criarmos alguns botões. Clique em Inserir.
180
11) Clique em Formas.
12) Selecione agora a opção Retângulo de Cantos Arredondados.
13) Agora vamos desenhar nosso retângulo, clique no primeiro ponto da arraste até o segundo ponto como na imagem abaixo.
181
14) Como você pode ver, nosso botão foi criado, vamos editá-lo um pouco, clique em Preenchimento da Forma.
15) Selecione a cor destacada abaixo.
16) Agora vamos modificar o contorno de nosso botão, para isso, clique em Contorno da Forma.
182
17) Selecione agora a cor preta em destaque.
18) Como você pode ver, nosso botão está com um visual muito interessante. O que faremos agora será criar duas cópias deste botão, para isso clique com o botão direito do mouse sobre ele.
19) Selecione a opção Copiar.
183
20) Agora clique com o botão direito em qualquer lugar da tabela e selecione a opção Copiar e Manter a Formatação Original, para colarmos nosso novo Botão.
21) Clique agora no primeiro ponto e arraste até o segundo ponto para colocarmos nosso botão no local correto.
22) Vamos criar mais uma cópia de nosso botão mas como você já conhece esse processo, vamos fazer isso para você, observe abaixo.
184
23) Agora vamos renomear nosso primeiro botão, para isso clique com o botão direito do mouse sobre o primeiro botão.
24) Selecione a opção Editar Texto.
185
25) Clique duas vezes no botão Aumentar Tamanho da Fonte, para que ela fique com o tamanho de 14.
26) Digite agora Livros.
186
27) Clique agora em Alinhar no Meio.
28) Clique agora em Centralizar.
187
29) Agora vamos efetuar o mesmo processo para os demais botões, mas como você já está familiarizado com isso, vamos fazer isso para você, observe na imagem abaixo. Para o segundo botão, determinamos o nome de Cadastro de Clientes e para o terceiro botão, determinamos o nome de Cadastro de Livro.
30) Assim terminamos os passos para a criação do layout de nossa planilha Inicial. Vamos dar inicio a criação do layout de nossa planilha Principal, para isso, clique no local indicado abaixo.
188
31) Vamos primeiramente modificar seu fundo, para isso, clique no local indicado abaixo.
32) Clique agora em Cor de Preenchimento.
33) Selecione a cor cinza indicada.
189
34) Agora clique na aba Inserir.
35) Clique em Imagem.
36) Selecione agora a imagem Livro2.png e clique em Inserir.
190
37) Agora vamos posicionar nossa imagem para outro lugar. Clique no primeiro ponto e arraste até o segundo, como indicado na imagem abaixo.
38) Vamos agora algumas formatações em nossa tabela. Clique sobre a célula C14.
191
39) Digite agora Nome do Cliente e clique na célula D14 e arraste até a J14. J14.
40) Clique agora em Tipos de Borda e selecione a opção Borda Inferior. Inferior.
41) Agora clique na célula F16. F16.
192
42) Digite agora Livros e aumente a fonte para 18. 18.
43) Agora vamos aplicar um sombreamento a nosso Logo. Clique no logo, isto é, na imagem que importamos anteriormente.
44) Clique agora em Formatar. Formatar.
193
45) Selecione a opção Retângulo com Sombra Projetada. Projetada.
Livro. 46) Agora selecione a célula C18 e digite Nome do Livro.
48) Agora você deve efetuar o mesmo procedimento para a criação do campo logo ao lado de Nome do Livro, Livro, como você já está familiarizado com o procedimento, vamos faze-lo e demonstrar abaixo para você.
194
49) Clique agora sobre a célula C19 e digite Gênero. Gênero.
Valor. 50) Clique agora sobre a célula G19 e digite Valor.
51) Agora você deve efetuar o mesmo procedimento para a criação dos campos logo ao lado de Gênero e Valor, Valor, como você já está familiarizado com o procedimento, vamos faze-lo e demonstrar abaixo para você.
195
52) Como você já está familiarizado com o processo de edição de layout, vamos efetuar mais algumas modificações para você, observe abaixo que criamos os campos Data, Data, Livros, Livros, Valor e Valor Total, Total, modificamos também sua cor de preenchimento naquela área para um cinza mais claro.
53) Agora iremos criar um botão b otão chamado Incluir, Incluir, como você já está familiarizado com o processo de criação de botão, nós iremos cria-lo para você.
54) Terminamos o desenvolvimento do layout layout da tabela Principal, agora agora vamos editar a tabela Clientes, para isso, clique em Clientes. Clientes.
196
55) Primeiramente vamos modificar o fundo da planilha, colocaremos um preenchimento nele, observe a imagem abaixo.
56) Vamos agora inserir uma imagem, para inserir uma imagem, primeiro você deve habilitar a guia Inserir, já fizemos isso para você, portanto, clique em Imagem.
57) Selecione agora a imagem Livro2.png e clique em Inserir.
197
58) Posicione a imagem no mesmo local destacado abaixo.
59) Selecione agora a opção Retângulo com Sobra Projetada.
60) Agora clique na célula C15 e digite Nome do Cliente.
198
61) Agora clique na célula C17 e digite Telefone do Cliente.
62) Agora você deve criar o preenchimento na parte inferior dos campos de Nome do Cliente e Telefone do Cliente, nós iremos efetuar esse procedimento para você, confira abaixo.
63) Agora você deverá criar um botão com o nome de Salvar já efetuamos esse procedimento para você, confira abaixo.
199
64) Terminamos assim o layout da planilha Clientes, agora vamos editar o layout da planilha Livros, para isso, clique em Livros.
65) Primeiro modifique a cor de fundo da planilha, como demonstrado abaixo.
66) Agora adicione uma imagem de sua preferência ao centro da planilha e aplique o efeito Retângulo com Sobra Projetada.
200
67) Agora selecione célula C15 e digite Nome do Livro.
68) Clique agora na célula C17 e digite Gênero.
69) Crie agora um botão chamado Salvar, similar ao botão abaixo.
201
70) O Layout dos dois bancos de dados, que serão referente aos Clientes e Livros, poderão ser mais simples, pois não vamos executá-los no projeto. Clique em BD_Clientes.
71) Edite a tabela para que ela fique similar a do exemplo abaixo.
72) Clique agora na planilha BD_Livros.
202
73) Crie uma tabela similar a exibida abaixo.
75) Salve seu arquivo pois usaremos esses layouts como base para o desenvolvimento do projeto de nosso próximo exercício.
203
Exercício 2
–
Criando Gerenciador de Livraria
1) Abra o arquivo desenvolvido no exercício anterior.
2) Vamos iniciar o desenvolvimento das funções. Para isso, clique em Desenvolvedor.
3) Clique agora em Gravar Macro.
204
4) Coloque o nome de nossa macro como Principal e clique em OK.
5) Clique agora sobre a planilha Principal.
6) Clique agora em Parar gravação.
205
7) Volte agora a planilha Inicial clicando no local indicado abaixo.
8) Agora clique com o botão direito do mouse sobre o botão Livros e selecione a opção Atribuir macro...
9) Selecione a macro Principal e clique em OK.
206
10) Clique novamente em Gravar Macro.
11) Coloque seu nome como CadClientes e clique em OK.
12) Agora clique sobre a planilha Clientes.
207
13) Agora clique em Parar Gravação.
14) Volte agora a planilha Inicial clicando no local indicado.
15) Clique agora com o botão direito sobre o botão Cadastro de Clientes para que se abra o menu rápido e selecione a opção Atribuir macro....
208
16) Selecione a macro CadClientes e clique em OK.
17) Clique agora em Gravar Macro.
209
18) Defina seu nome como CadLivros e clique em OK.
19) Clique agora sobre a planilha Livros.
210
20) Clique agora em Parar gravação.
21) Clique agora sobre a planilha Inicial.
22) Clique agora com o botão direito sobre o botão Cadastro de Livros para que se abra o menu rápido e selecione a opção Atribuir macro....
211
23) Selecione a macro CadLivros e clique em OK.
24) Pronto criamos todos os botões referentes a nossa planilha Inicial. Agora clique sobre a planilha Principal.
25) Selecione agora todo o campo de Nome do Cliente.
212
26) Clique agora na aba Página Inicial.
27) Agora clique em Mesclar e Centralizar.
28) Agora clique sobre a aba Dados.
213
29) Clique sobre o botão de Validação de Dados.
30) Será aberta a janela de validação de dados, no menu dropdown de Permitir, selecione a opção Lista.
31) Agora clique no botão logo ao lado do campo Fonte.
214
32) Clique agora sobre a planilha BD_Clientes.
33) Agora selecione todo o intervalo de células referentes a Nome, isto é, de B3 à B24.
34) Clique agora no botão ao lado do campo Fonte.
215
35) Clique agora em OK.
36) Agora selecione todo o campo referente a Nome do Livro.
216
37) Você deve primeiramente mesclar as células como fez na validação anterior, você já está familiarizado com o procedimento (Aba Página Inicial > Mesclar e Centralizar) sendo assim, já adiantamos esse processo para você, com as células mescladas, clique em Validação de Dados.
38) Selecione a validação em Lista e logo em seguida clique botão referente a Fonte.
39) Clique agora em BD_Livros.
217
40) Selecione agora todo o campo referente ao nome dos livros. Isto é, de C4 à C26.
41) Clique agora no botão referente a Fonte.
42) Clique em OK para finalizar a validação.
218
43) Clique agora no campo ao lado de Gênero.
44) Mescle as células referentes a esse campo, logo em seguida clique no botão de Funções.
45) Procure a função PROCV e clique em OK.
219
46) Em Valor_procurado, devemos determinar o valor que servirá como base de nossa procura, nesse caso, será o Nome do Livro, que se encontra na célula D18. Digite D18.
47) Em Matriz_tabela, devemos determinar o intervalo referente ao banco de dados dos livros, portanto, clique no botão destacado abaixo.
48) Agora clique em BD_Livros.
220
49) Selecione desde C4 a E26.
221
50) Clique novamente no botão indicado.
51) Em Num_índice_coluna devemos determinar o numero da coluna referente aos gêneros, nesse caso, digite 2.
52) Em Procurar_intervalo, digite FALSO e clique em OK.
222
53) Agora selecione todo o campo referente a Valor e mescle suas células, após isso clique no botão de funções.
54) Procure a função PROCV e clique em OK.
55) Para Valor_procurado, escreva D18.
223
56) Em Matriz_tabela, devemos determinar o intervalo referente ao banco de dados dos livros, portanto, clique no botão destacado abaixo.
57) Selecione agora a tabela BD_Livros.
58) Selecione toda a tabela, isto é, desde C4 a E26.
224
59)Clique novamente no botão indicado abaixo.
60) Para Num_índice_coluna vamos determinar o valor 3, pois a coluna valor é a terceira de nosso banco de livros. Digite 3.
61) Em Procurar_intervalo digite FALSO e clique em OK.
225
62) Agora clique no botão Formato de Número de Contabilização.
63) Selecione agora a opção R$ Português (Brasil) para que nossos valores sejam exibidos de forma contábil.
64) Vamos modificar um pouco o layout de nossa planilha Clientes. Clique em Clientes.
226
65) Selecione a célula D15.
66) Agora clique em Bordas e selecione a opção Todas as Bordas.
227
67) Efetue exatamente o mesmo procedimento com o campo Telefone.
68) Agora vamos iniciar a gravação de uma nova macro, mas dessa vez utilizaremos um atalho que se encontra no rodapé da página. Clique no local indicado abaixo.
228
69) Digite InsereCad1 e clique em OK.
70) Clique agora na célula D15 com o botão direito para abrir o menu rápido. Clique em Copiar.
71) Agora selecione a planilha BD_Clientes.
229
72) Agora clique com o botão direito na célula B4 e selecione a opção Inserir células copiadas.
73) Selecione a opção Deslocar células para baixo e clique em OK.
230
74) Agora clique novamente na planilha Clientes.
75) Clique agora com o botão direito no campo Telefone e selecione a opção Copiar.
231
76) Agora clique na planilha BD_Clientes.
77) Clique agora com o botão direito sobre a célula C4 e selecione a opção Inserir células copiadas.
232
78) Selecione a opção Deslocar células para baixo e clique em OK.
79) Clique agora na planilha Clientes
80) Agora clique em Parar gravação.
.
233
81) Clique agora com o botão direito no botão Salvar e selecione a opção Atribuir Macro...
82) Selecione a InsereCad1 e clique em OK.
234
83) Clique agora na planilha Livros.
84) Clique agora sobre a célula D15.
85) Agora selecione a opção Borda de Preenchimento e escolha o tipo Bordas Externas.
235
86) Efetue o mesmo procedimento para o campo Gênero e logo abaixo crie um campo chamado Valor.
87) Clique agora no botão referente a Gravação de Macro.
88) Clique agora em InsereCad1 e logo em seguida clique em OK.
236
89) Clique agora com o botão direito do mouse sobre a célula D15.
90) Clique agora sobre a planilha BD_Livros.
91) Clique agora com o botão direito sobre a célula C4 e selecione a opção Inserir células copiadas.
237
92) Clique agora em Deslocar células para baixo e clique em OK.
93) Agora clique sobre a planilha Livros novamente.
94) Agora clique com o botão direito sobre a célula D17 e clique sobre a opção Copiar.
238
95) Clique agora sobre a planilha BD_Livros.
96) Clique agora com o botão direito sobre a célula D4 e selecione a opção Inserir células copiadas.
97)Clique agora sobre a opção Deslocar células para baixo e clique em OK.
239
98) Clique novamente sobre a planilha Livros.
99) Clique agora com o botão direito sobre a célula D19 e selecione a opção Copiar.
100) Clique agora sobre a planilha BD_Livros.
240
101) Clique agora com o botão direito sobre a célula E4 e selecione a opção Inserir células copiadas.
102) Clique agora sobre a opção Deslocar células para baixo e clique em OK.
103) Clique novamente sobre a planilha Livros.
241
104) Agora clique em Parar gravação.
105) Clique agora com o botão direito sobre o botão Salvar e selecione a opção Atribuir macro...
106) Clique agora em InsereCad1 e logo em seguida clique em OK.
242
107) Clique agora sobre a planilha Principal.
108) Não iremos precisar de todas as linhas de cadastro que criamos anteriormente, elas foram criadas apenas para que você tenha uma boa noção de como seu cadastro deve ficar, nesse caso selecione da célula C23 à J25.
109) Agora clique com o botão direito do mouse sobre a seleção e escolha a opção Excluir.
243
110) Selecione a opção Deslocar células para cima e clique em OK.
112) Agora clique na célula J23.
113) Agora clique no botão referente às formulas OK.
, procure a função SOMA e clique em
244
114) Clique agora na célula C21 e arraste até C22.
115) Agora clique em OK.
116) Você receberá uma mensagem de aviso do Excel, clique em OK.
245
117) Agora iremos criar uma macro, para isso, clique no botão de atalho para criação de macro.
118) Coloque seu nome como InsereCad3 e clique em OK.
246
119) Agora clique com o botão direito sobre a célula D18 e selecione a opção Copiar.
120) Agora clique com o botão direito do mouse sobre a célula D22 e selecione a opção Colar Valores.
247
121) Agora clique com o botão direito sobre a célula H19 e clique em Copiar.
122) Agora clique com o botão direito sobre a célula J22 e selecione a opção Colar Valores.
248
123) Agora clique sobre a célula C22.
124) Agora clique no botão referente às formulas OK.
, procure a função HOJE e clique em
249
125) Você verá uma tela de Argumentos de função, clique em OK.
126) Agora selecione as células de C22 à J22.
127) Agora clique com o botão direito sobre a seleção e selecione a opção Inserir.
250
128)Selecione a opção Linha inteira e clique em OK.
129) Clique agora em Parar gravação.
130) Agora selecione a célula C23 e arraste até a J23.
251
131) Clique com o botão direito na seleção e escolha a opção Excluir.
132) Selecione a opção Deslocar células para cima e clique em OK.
133) Você receberá uma mensagem do Excel, clique em OK.
252
134) Agora vamos atribuir o a macro que criamos ao nosso botão Incluir. Clique com o botão direito sobre o botão Incluir e selecione a opção Atribuir macro...
135) Selecione a macro InsereCad3 e clique em OK.
253
134) Agora selecione as células de J21 à J23.
135) Selecione agora o botão Formato de Número de Contabilização.
254
136) Selecione a opção R$ Português (Brasil).
137) Salve seu arquivo. 138) Agora você pode utilizar a planilha Clientes para cadastrar novos clientes, a planilha Livros para cadastrar os livros e a planilha Principal para efetuar o relatório de locação dos livros.
255
