Baixe o app para aproveitar ainda mais
Prévia do material em texto
Doc.06.02.v1 SISTEMA DE APRENDIZAGEM Técnico Desporto UFCD 0757 Folha de Cálculo – Funcionalidades avançadas João Alves MANUAL DE FORMAÇÃO Doc.06.02.v1 1 Porto, Setembro de 2019 PROMOÇÃO E COORDENAÇÃO MARFORMAR AUTOR João Alves EDIÇÃO E IMPRESSÃO MARFORMAR FINANCIAMENTO Programa Operacional e Capital Humano Doc.06.02.v1 2 ÍNDICE INTRODUÇÃO ________________________________________________________________ 3 CAPÍTULO I – Folhas Múltiplas de Cálculo __________________________________________ 4 CAPÍTULO II – Resumo de Dados _________________________________________________ 6 CAPÍTULO IV – Macros ________________________________________________________ 23 CONCLUSÃO ________________________________________________________________ 26 REFERÊNCIAS BILIOGRÁFICAS __________________________________________________ 26 Doc.06.02.v1 3 INTRODUÇÃO Nesta UFCD vamos abordar as funcionalidades avançadas da folha de cálculo ( Microsoft Excel), numa fase inicial iremos trabalhar dados ou seja quer a sua manipulação em várias folhas , quer a forma de visualização, aplicando filtragens , ordenações e sub-totais. Na segunda fase vamos trabalhar esses dados de uma forma dinâmica, criando e alterando tabelas e gráficos dinâmicos, por fim vamos criar e alterar macros, atribuindo a mesma a botões. Doc.06.02.v1 4 CAPÍTULO I – Folhas Múltiplas de Cálculo O Microsoft Excel é uma folha de cálculo dinâmica, que permite trabalhar com diversas folhas de trabalho e com diversas folhas de cálculo em simultâneo, existindo sempre uma atualização entre as mesmas a quando da sua alteração. A célula da folha de cálculo consiste numa série de células que são o cruzamento entre linhas horizontais, numeradas de 1 até 1 048 576, e de colunas verticais, com denominações de A até XFD, num total de 17 179 869 184 células, que podem conter números, datas, texto e/ou fórmulas e funções. Além da realização de cálculos, as folhas de Excel podem também ser usadas como bases de dados simples, bem como conjugar estas duas funcionalidades. Tendo em conta toda esta dimensão, porque usar várias folhas de trabalho ou várias folhas de cálculo? Pela organização e acessos, ou seja eu posso ter a informação dividida entre várias folhas e assim garantir uma melhor forma de trabalhar e posso limitar acessos a folhas de trabalho de forma a garantir a certeza nos dados. Reunião de folhas de cálculo A forma de reunirmos várias folhas de cálculo é um processo que nos simplifica o trabalho com muita informação. No friso em cima podemos: Fixar painéis ou seja fixar uma parte das linhas ou colunas podendo mover-nos na folha sem que essa parte se altere. Dispor Janelas é colocar várias folhas em diversas janelas podendo mover-nos facilmente entre elas. Dividir ou ocultar é o processo que permite dividir células ou ocultar linhas e colunas. Ver lado a lado é a forma de analisarmos de uma forma simples muita informação. Doc.06.02.v1 5 Ligação entre folhas A primeira forma de interligar-mos várias folhas de trabalho é usar o sinal = e desta forma tudo que se muda numa folha muda na outra. A outra forma é usar a opção Copiar e Colar ligação tal como mostra a figura abaixo. Doc.06.02.v1 6 CAPÍTULO II – Resumo de Dados Os dados em Excel são como o combustível para um automóvel, sendo assim o seu tratamento é muito importante bem como a sua organização. SubTotais Pode calcular automaticamente subtotais e totais gerais numa lista para uma coluna utilizando o comando Subtotal. O comando Subtotal irá aparecer a cinzento se estiver a trabalhar com uma tabela do Microsoft Excel. Para adicionar subtotais numa tabela, tem primeiro de converter a tabela num intervalo de dados normal e, em seguida, adicionar o subtotal. Tenha em atenção que tal irá remover toda a funcionalidade de tabela dos dados, exceto a formatação de tabela. Quando insere subtotais: Os subtotais são calculados com uma função de resumo, tal como Soma ou Média, utilizando a função SUBTOTAL. Pode apresentar mais do que um tipo de função de resumo para cada coluna. Os totais gerais são derivados dos dados de detalhe, e não dos valores nos subtotais. Por exemplo, se utilizar a função de resumo Média, a linha do total geral apresentará uma média de todas as linhas de detalhe da lista, e não uma média dos valores existentes nas linhas de subtotal. Se o livro estiver configurado para calcular fórmulas automaticamente, o comando Subtotal recalcula os valores do subtotal e total geral automaticamente à medida que os dados de javascript:AppendPopup(this,'xldefSummaryFunction_1_1') javascript:AppendPopup(this,'xldefDetailData_2_2') Doc.06.02.v1 7 detalhe forem sendo editados. O comando Subtotal também destaca a lista, para que seja possível mostrar e ocultar as linhas de detalhe de cada subtotal. Inserir subtotais Certifique-se de que cada coluna num intervalo de dados para o qual pretenda calcular subtotais tem uma etiqueta na primeira linha e contém factos semelhantes em cada coluna, e certifique-se também de que o intervalo não inclui linhas nem colunas em branco. 1. Selecione uma célula no intervalo. 2. Efetue um dos seguintes procedimentos: 3. Inserir um nível de subtotais Pode inserir um nível de subtotais para um grupo de dados conforme ilustrado no exemplo seguinte. javascript:AppendPopup(this,'xldefOutline_3_3') javascript:AppendPopup(this,'xldefRange_4_4') javascript:ToggleDiv('divExpCollAsst_IDAP1ZGG') Doc.06.02.v1 8 A cada alteração na coluna Data o subtotal na coluna Valor. Para ordenar a coluna que contém os dados pelos quais pretende agrupar, selecione essa coluna e, no separador Dados, no grupo Ordenar e Filtrar, clique em Ordenar de A a Z ou em Ordenar de Z a A. No separador Dados, no grupo Destaque, clique em Subtotal. A caixa de diálogo Subtotal é apresentada. Na caixa A cada alteração em, clique na coluna cujo subtotal pretende obter. Por exemplo, com base no exemplo acima, selecionaria Comercial. Na caixa Utilizar a função, clique na função de resumo que pretende utilizar para calcular os subtotais. Por exemplo, com base no exemplo acima, selecionaria Soma. Na caixa Adicionar subtotal a, selecione a caixa de verificação relativa a cada coluna que contém valores cujo subtotal pretende calcular. Por exemplo, com base no exemplo acima, selecionaria Valor. Se pretender uma quebra de página automática a seguir a cada subtotal, selecione a caixa de verificação Quebra de página entre grupos. Para especificar uma linha de resumo por cima da linha de detalhes, desmarque a caixa de verificação Sumário por baixo dos dados. Para especificar uma linha de resumo por baixo da linha de detalhes, selecione a caixa de verificação Sumário por baixo dos dados. Por exemplo, com base no exemplo acima, desmarcaria a caixa de verificação. Para adicionar mais subtotais com funções de resumo diferentes. Para evitar substituir os subtotais existentes, desmarque a caixa de verificação Substituir subtotais atuais. Inserir níveis de subtotais aninhados É possível inserir subtotais para grupos aninhados internos no interior dos grupos externos correspondentes, conforme apresentado no exemplo seguinte. Doc.06.02.v1 9 No separador Dados, no grupo Destaque, clique em Subtotal. A caixa de diálogo Subtotal é apresentada. Na caixa A cada alteração em, clique na coluna de subtotalaninhada. Por exemplo, com base no exemplo acima, selecionaria Comercial. Na caixa Utilizar a função, clique na função de resumo que pretende utilizar para calcular os subtotais. Por exemplo, com base no exemplo acima, selecionaria Soma. Selecione outras opções que pretenda. Doc.06.02.v1 10 Desmarque a caixa de verificação Substituir subtotais atuais. Repita o passo anterior para mais subtotais aninhados, trabalhando a partir dos subtotais mais externos para dentro. Remover subtotais Selecione uma célula no intervalo que contenha subtotais. No separador Dados, no grupo Destaque, clique em Subtotal. Na caixa de diálogo Subtotal, clique em Remover Tudo. Destaques e resumo Para visualizar um resumo apenas dos subtotais e totais gerais, clique nos símbolos de destaque junto dos números de linha. Utilize os símbolos e para apresentar ou ocultar as linhas de detalhe dos subtotais individuais. Doc.06.02.v1 11 Exemplo após aplicação de destaques Doc.06.02.v1 12 CAPÍTULO III – Análise de Dados Ordenação de dados A ordenação de dados é algum importante quando trabalhamos com um grande volume de informação. O primeiro passo é selecionar os dados que pretende colocar ordenar. De seguida seleciona um intervalo de dados, tal como A1:L5 (múltiplas linhas e colunas) ou C1:C80 (uma única coluna). O intervalo pode incluir títulos que criou para identificar colunas ou linhas. Ordenar rapidamente Selecione uma única célula na coluna pela qual pretende ordenar. Clique em para efetuar uma ordenação ascendente (de A a Z ou do número menor para o número maior). Ordenar especificando critérios Pode escolher as colunas pelas quais pretende ordenar clicando no comando Ordenar no grupo Ordenar e Filtrar no separador Dados. Selecione uma única célula em qualquer ponto do intervalo que pretende ordenar. No separador Dados, no grupo Ordenar e Filtrar, clique em Ordenar. A caixa de diálogo Ordenar é apresentada. Doc.06.02.v1 13 Na lista Ordenar por, selecione a primeira coluna pela qual pretende ordenar. Na lista Ordenar Em, selecione Valores, Cor da Célula, Cor do Tipo de Letra ou Ícone de Célula. Na lista Ordem, selecione a ordem que pretende aplicar à operação de ordenação — alfabética ou numericamente ascendente ou descendente (isto é, de A a Z ou de Z a A para texto ou do inferior para o superior ou do superior para o inferior para números). Filtragem de Dados Ao filtrar informações numa folha de cálculo, pode encontrar rapidamente valores. Pode filtrar uma ou mais colunas de dados. Através da filtragem, pode controlar não apenas o que visualiza, mas o que pretende excluir. Pode filtrar com base em escolhas efetuadas a partir de uma lista ou pode criar filtros específicos para se concentrar exatamente nos dados que pretende visualizar. Pode procurar texto e números quando filtrar utilizando a caixa Procurar na interface do filtro. Quando filtra dados, linhas inteiras são ocultadas se os valores numa ou mais colunas não cumprirem os critérios de filtragem. Pode aplicar filtros em valores numéricos ou de texto, filtrar por cor para células que tenham a formatação de cores aplicada ao respetivo fundo ou texto. Filtrar ou procurar valores A seleção de valores numa lista e as pesquisas constituem as formas mais rápidas de filtragem. Quando clica na seta numa coluna com a opção de filtragem ativada, todos os valores nessa coluna aparecem numa lista. Doc.06.02.v1 14 Utilize a caixa Procurar para introduzir texto ou números onde pretenda efetuar pesquisas Selecione e desmarque as caixas de verificação para mostrar valores encontrados na coluna de dados. Utilize critérios avançados para encontrar valores que cumpram condições específicas Para selecionar por valores, na lista, desmarque a caixa de verificação (Selecionar Tudo). Isto remove as marcas de verificação de todas as caixas de verificação. Em seguida, selecione apenas os valores que pretende visualizar e clique em OK para ver os resultados. Para procurar em texto na coluna, introduza texto ou números na caixa Procurar. Opcionalmente, pode utilizar caracteres universais, tais como o asterisco (*) ou o ponto de interrogação (?). Prima ENTER para ver os resultados. Filtrar dados através da especificação de condições Ao especificar condições, pode criar filtros personalizados que especificam os dados exatamente da forma pretendida. Pode fazê-lo através da construção de um filtro. Se já tiver consultado dados numa base de dados, isto ser-lhe-á familiar. Aponte para Filtros Numéricos ou Filtros de Texto na lista. É apresentado um menu que lhe permite filtrar em várias circunstâncias. Doc.06.02.v1 15 Escolha uma condição e, em seguida, seleccione ou introduza critérios. Clique no botão E para combinar critérios (ou seja, dois ou mais critérios que têm ambos de ser cumpridos) e o botão Ou para requerer que apenas uma de múltiplas condições seja cumprida. Clique em OK para aplicar o filtro e obter os resultados esperados. Tabela Dinâmica Uma Tabela Dinâmica é uma forma interativa de resumir rapidamente grandes quantidades de dados. Utilize um relatório de Tabela Dinâmica para analisar detalhadamente dados numéricos e para responder a questões inesperadas sobre os dados. Os relatórios de Tabela Dinâmica são concebidos especialmente para: Consultar grandes volumes de dados de várias formas intuitivas. Subtotalizar e agregar dados numéricos, resumir dados por categorias e subcategorias, bem como criar cálculos e fórmulas personalizadas. Expandir e encolher níveis de dados de modo a incidir o foco nos resultados e mostrar pormenorizadamente dados de sumário das áreas de interesse. Mover linhas para colunas ou colunas para linhas ("deslocação") para ver resumos diferentes dos dados de origem. Filtrar, ordenar, agrupar e aplicar formatação condicional ao subconjunto de dados mais útil ou mais interessante para permitir focar as informações pretendidas. Apresentar relatórios online ou impressos concisos, atractivos e anotados. Uma Tabela Dinâmica é frequentemente utilizada quando se pretende analisar totais relacionados, em especial quando se tem uma longa lista de valores para somar e se pretende comparar diversos aspetos de cada valor. No relatório de Tabela Dinâmica ilustrado em baixo, pode facilmente visualizar uma comparação das vendas na área de golfe durante o terceiro trimestre, na célula F3, com as vendas de outro desporto, com outro trimestre ou com as vendas totais. Criar uma Tabela Dinâmica ou Gráfico Dinâmico a partir de dados da folha de cálculo Doc.06.02.v1 16 Pode utilizar dados de uma folha de cálculo do Microsoft Excel como base para um relatório. Os dados devem estar no formato de lista, com etiquetas de coluna na primeira linha. Cada célula nas linhas subsequentes deve conter dados apropriados ao cabeçalho de coluna. Os dados de interesse não devem ter linhas nem colunas em branco. O Excel utiliza as etiquetas de coluna para os nomes de campo no relatório. Utilizar um intervalo com nome Para facilitar a atualização do relatório, atribua um nome ao intervalo de origem e utilize o nome quando criar o relatório. Se o intervalo com nome for expandido para incluir mais dados, pode atualizar o relatório para incluir os novos dados. Tabelas do Excel As tabelas do Excel já se encontram no formato de lista e são ótimas candidatas a dados de origem de Tabela Dinâmica. Quando atualizar o relatório de Tabela Dinâmica, os dados novos e atualizados da tabela do Excel são incluídos automaticamente na operação de atualização. Incluir totais O Excel cria automaticamente subtotais e totais gerais num relatório de TabelaDinâmica. Se os dados de origem contiverem subtotais e totais gerais automáticos criados com o comando Subtotais no grupo Destacar do separador Dados, utilize esse comando para remover os subtotais e totais gerais antes de criar o relatório. Temos como exemplo esta folha que possui vária linhas de dados: javascript:AppendPopup(this,'xldefPivotTableField_21_21') javascript:AppendPopup(this,'IDH_xldefName_22_22') javascript:AppendPopup(this,'IDH_xldefPivotTableRefresh_23_23') Doc.06.02.v1 17 Utilizando apenas filtros, até poderíamos resolver muitas coisas, mas será que não teríamos muito trabalho para obter, por exemplo, o Estado de MG, ou ainda quantas vendas do item Camisas foram realizadas pelo funcionário João? É para estes e uma infinidade de outros casos que utilizamos as Tabelas Dinâmicas. Bem, voltando ao assunto, Clique em qualquer ponto da sua tabela e vá a Inserir, Tabela Dinâmica. Podemos clicar diretamente no botão, ou clicar na seta e escolher Tabela Dinâmica. Você terá a seguinte janela: Doc.06.02.v1 18 Como estou a utilizar um Formato de Tabela, o nome desta tabela aparece como o Intervalo. Nada impediria que aparecesse um intervalo de células neste campo. Tabelas Dinâmicas também podem ser geradas a partir de uma fonte de dados externa tais como: Excel, Microsoft SQL Server ou Microsoft Access. No nosso caso, iremos utilizar a tabela que foi automaticamente gravada como Tabela 1. Vamos colocar a nossa Tabela Dinâmica numa Nova folha, e depois pressionaremos Ok. Doc.06.02.v1 19 Agora é a hora de planear de que maneira vamos exibir os resultados na nossa tabela dinâmica. Vou dar um pequeno exemplo inicial: Calcular o total vendido por vendedor Toda tabela dinâmica tem 4 áreas: Filtros de Relatório (antigo campo de página), Rótulos de Linha (Campo de Linha), Rótulos de Coluna (Campo de Coluna) e Valores (Área de Valores). Podemos utilizar duas ou mais áreas para podermos analisar os nossos dados. No primeiro exemplo, vamos selecionar o Vendedor e marcar a caixa. Repare que automaticamente ele vai ser colocado na área chamada Rótulos de Linha. Também poderiamos realizar este processo clicando sobre o nome Vendedor e arrastando-o para a área abaixo da Lista de Campos, dentro do quadro Rótulos de Linha. Teremos o seguinte resultado: Doc.06.02.v1 20 Uma coisa interessante: mesmo que tenhamos nomes repetidos na nossa folha principal, ele vai mostrar apenas uma ocorrência do nome na nossa tabela dinâmica. Isso vai ser de grande importância quando formos para a próxima etapa. Agora, damos um clique na caixa acima de Vendas, ou arrastamos o Campo Vendas para a área de Valores e ficamos com o total vendido. Agora vamos arrumar bem o nosso ecrã. As vendas deveriam ser exibidas num formato contabilidade. Para fazer isso de uma maneira simples, faço o seguinte: Clico na seta que aparece ao lado de Soma de Vendas, dentro do quadro Valores. Agora escolho a última opção – Configurações do Campo de Valor. Doc.06.02.v1 21 Na próxima janela, na parte inferior da janela, encontraremos um botão que nos vai ajudar bastante, Formato do Número, que permitirá escolher o formato a exibir. Escolheremos contabilidade e manteremos as outras configurações, clicando em Ok para confirmar. Doc.06.02.v1 22 Depois de pressionar Ok mais uma vez na janela Configurações do Campo de Valor, teremos o seguinte resultado: Doc.06.02.v1 23 CAPÍTULO IV – Macros Se tiver tarefas no Microsoft Excel que efetue repetidamente, pode gravar uma macro para automatizar essas tarefas. Uma macro é uma ação ou um conjunto de ações que pode executar as vezes que quiser. Quando cria uma macro, está a gravar cliques do rato e batimentos de teclas. Depois de criar uma macro, pode editá-la para efetuar pequenas alterações ao seu modo de funcionamento. Suponha que todos os meses tem de criar um relatório para o seu contabilista. Pretende formatar os nomes dos clientes com contas vencidas a vermelho e aplicar também formatação a negrito. Pode criar e, em seguida, executar uma macro que aplique rapidamente estas alterações de formatação às células que selecionar. Antes de gravar uma macro Certifique-se de que o separador Programador está visível no friso. Por predefinição, o separador Programador não está visível, pelo que deverá efetuar o seguinte procedimento: Clique no separador Ficheiro, clique em Opções e clique na categoria Personalizar Friso. Em Personalizar o Friso, na lista Separadores Principais, clique em Programador e clique em OK. Gravar macros No grupo Código, no separador Programador, clique em Gravar Macro e, em seguida, clique em OK para iniciar a gravação Execute algumas ações na sua folha de cálculo, tais como escrever algum texto, seleccionar algumas colunas ou linhas, ou preencher alguns dados. Doc.06.02.v1 24 No grupo Código, no separador Programador, clique em Terminar Gravação. Analisar de forma mais detalhada a macro e efetuar experiências Pode obter algumas informações sobre a linguagem de programação do Visual Basic ao editar uma macro que tenha gravado. Para editar uma macro, no grupo Código, no separador Programador, clique em Macros, selecione o nome da macro que gravou e clique em Editar. Este procedimento inicia o Visual Basic Editor. Analise o código e veja como as ações que gravou aparecem como código. Algumas partes do código provavelmente ser-lhe-ão familiares e algumas poderão parecer-lhe um pouco estranhas. Efetue experiências com o código, feche o Visual Basic Editor e execute novamente a macro. Desta vez, veja se acontece alguma coisa de diferente Associar uma macro a um botão Existem várias formas de executar uma macro no Microsoft Excel. Pode sempre executar uma macro clicando no comando Macros no friso (separador Programador, grupo Código). Dependendo da forma de execução atribuída à macro, também poderá conseguir executá-la premindo uma tecla de atalho em combinação com CTRL, clicando num botão na Barra de Ferramentas de Acesso Rápido ou num grupo personalizado no friso, ou ainda clicando numa área num objeto, gráfico ou controlo. Além disso, pode executar automaticamente uma macro ao abrir um livro. javascript:AppendPopup(this,'ofShortcutKey_1_1') Doc.06.02.v1 25 Executar uma macro premindo uma tecla de atalho de combinação com a tecla CTRL 1. Clique no separador Ficheiro, clique em Opções e clique na categoria Personalizar Friso. 2. Na lista Separadores Principais, selecione a caixa de verificação Programador e clique em OK. 3. No separador Programador, no grupo Código, clique em Macros. 4. Na caixa Nome da macro, clique na macro que pretende atribuir a uma tecla de atalho de combinação com a tecla CTRL. 5. Clique em Opções. e é apresentada a caixa de diálogo Opções de Macro. 6. Na caixa Tecla de atalho, escreva uma letra minúscula ou maiúscula que pretenda utilizar com a tecla CTRL. 7. Nota A tecla de atalho irá substituir qualquer tecla de atalho equivalente, predefinida do Excel, enquanto o livro que contém as macros estiver aberto. 8. Para obter uma lista de teclas de atalho de combinação com a tecla CTRL que já estejam atribuídas no Excel, consulte o artigo sobre teclas de atalho e de função do Excel (pode estar em inglês). 9. Na caixa Descrição, escreva a descrição da macro. 10. Clique em OK para guardar as alterações e clique em Cancelar para fechar a caixa de diálogo Macro. ms-help://MS.EXCEL.14.2070/EXCEL/content/HP10342494.htm ms-help://MS.EXCEL.14.2070/EXCEL/content/HP10342494.htm Doc.06.02.v1 26 CONCLUSÃO Este manual abordou a parte avançada de Excel, explicou a trabalhar com váriasfolhas em simultâneo, tendo em conta as ligações e as relações a criar. Aplicou diversas formas de ordenação e filtragem simples e complexas, criando e alterando tabelas dinâmicas de acordo com as necessidades, bem como a criação, alteração e associação de macros. REFERÊNCIAS BILIOGRÁFICAS MP Pinto (2011), Microsoft Excel 2010, Centro Atlântico. Vitor, Alves (2009), Macros para Excel na prática, Campus. Paula, Marques (2010), Exercícios Excel 2010, FCA
Compartilhar