AvançadoP9 Mod. 6 Rev. 0
Excel AvançadoÍndiceINTRODUÇÃO .............................................................................................................. 2EXERCÍCIO Nº1 - FUNÇÕES DE DATA E HORA .............................................................. 3EXERCÍCIO Nº2 - FUNÇÕES MATEMÁTICAS.................................................................. 5EXERCÍCIO Nº3 - FUNÇÕES DE INFORMAÇÃO .............................................................. 7EXERCÍCIO Nº4 - FUNÇÕES DE TEXTO ......................................................................... 9EXERCÍCIO Nº5 - FUNÇÕES DE ESTATÍSTICA ............................................................ 11EXERCÍCIO Nº6 - FUNÇÕES FINANCEIRAS................................................................. 13EXERCÍCIO Nº7 – FUNÇÃO DE PROCURA ................................................................... 17EXERCÍCIO Nº8 – FUNÇÕES DE LÓGICA E FORMAÇÃO CONDICIONAL........................ 19EXERCÍCIO Nº9 - ESTUDO DOS GRÁFICOS................................................................. 23EXERCÍCIO Nº10 – ESQUEMA DE PÁGINA.................................................................. 29EXERCÍCIO Nº11 – AUDITORIA DE FÓRMULAS .......................................................... 32 1EXERCÍCIO Nº11 – AUTO FILTRO E FILTRO AVANÇADO ............................................ 35EXERCÍCIO Nº12 – PROTEGER FOLHA E PARTILHA DE LIVROS.................................. 39EXERCÍCIO Nº13 – CONSOLIDAÇÃO DE DADOS ......................................................... 42EXERCÍCIO Nº14 – VALIDAÇÃO DE DADOS ................................................................ 46EXERCÍCIO Nº15 – SUB TOTAIS ................................................................................ 51EXERCÍCIO Nº16 - TABELAS DINÂMICAS................................................................... 55EXERCÍCIO Nº17 – CRIAR MACROS ........................................................................... 59EXERCÍCIO Nº18 – ATRIBUIR BOTÕES ÀS MACROS ................................................... 61EXERCÍCIO Nº19 – REVISÃO MACROS ....................................................................... 66EXERCÍCIO Nº20 - PALAVRA PASSE (GUARDAR/ABRIR) ........................................... 68P9 Mod. 6 Rev. 0
Excel Avançado IntroduçãoEste manual de exercícios é dirigida aos utilizadores do Excel que conhecem assuas funcionalidades básicas e pretendem aprofundar os seus conhecimentos emelhorar as suas técnicas. É apresentado um conjunto amplo e diversificado deexercícios práticos e situações que conduzem à criação de folhas de cálculoeficazes no apoio à análise, gestão e tratamento da informação.A metodologia utilizada na exposição das matérias, privilegiando a perspectivado utilizador, foi organizada para uma aprendizagem por projecto com tarefasque permitem ao formando uma formação gradual numa perspectivaiminentemente prática.Os formandos encontrarão neste manual de exercícios sobre: 2 Funções Data e Hora Matemáticas Informação Texto Estatística Financeiras Procura Lógica e Formação Condicional Estudo dos Gráficos Esquema de Página Auditoria de Fórmulas Auto Filtro e Filtro Avançado Proteger Folha e Partilha de Livros Consolidação de Dados Validação de Dados Sub Totais Tabelas Dinâmicas Macros Palavra Passe (guardar/abrir)P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº1 - Funções de Data e Hora1) Observe as seguinte funções de Data e Hora:AGORA() ....................................... Devolve a data e a hora actual do sistema.HOJE() ........................................... Devolve a data actual.MÊS(de uma data) ......................... Devolve o mês correspondente a uma data. O número é dado como um número inteiro, entre 1 (Janeiro) e 12 (Dezembro).ANO(de uma data) ........................ Devolve o ano correspondente a uma data indicada.DIA(de uma data) .......................... Devolve o dia de uma data. O dia é dado como 3 um número inteiro que varia entre 1 e 31.HORA(de um horário) .................... Devolve a hora da hora completa do sistema. A hora é devolvida sob a forma de um número inteiro entre 0 e 23.MINUTO(de um horário) ............... Devolve os minutos de um valor de tempo. O minuto é dado como um número inteiro, entre 0 e 59.SEGUNDO(de um horário)............. Devolve os segundos de um valor de tempo. O segundo é fornecido como um número inteiro no intervalo de 0 a 59.P9 Mod. 6 Rev. 0
Excel Avançado2) Numa nova folha de Excel, escreva a seguinte informação: 43) Utilizando as funções de Data e Hora que considerar adequadas, preencha as células a sombreado.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº2 - Funções Matemáticas1) Observe as seguinte funções Matemáticas: ABS(número)............................ Devolve o valor absoluto de um número (é o próprio número sem sinal) Exemplo =ABS(-2)................... Devolve 2ARRED(número; decimais) ....... Arredonda um número até uma quantidade específica de dígitos.Exemplo =ARRED(2,15; 1) ....... Arredonda 2,15 para uma casa decimalINT(número) ....................... Devolve o valor inteiro um número. 5Exemplo = INT(3,5) ................. Devolve 3POTÊNCIA(número; potência) Devolve o nº elevado a determinada potência.Exemplo = POTÊNCIA(5;2) ....... Devolve 5 elevado a 2, que são 25RAIZQ(número; potência) ...... Devolve a raiz quadradaExemplo = RAIZQ(16) .............. Devolve a Raiz quadrada de 16 que são 4SOMA.SE(intervalo;critérios;intervalo_soma)...... Soma as células especificadas por um determinado critério.Intervalo - é o intervalo de células que deseja comparar com o critérioCritério - é o critério que define quais as células a serem adicionadasIntervalo_soma - são as células a serem realmente somadasP9 Mod. 6 Rev. 0
Excel AvançadoExemplo: Neste exemplo, serão somadas apenas os valores referentes a Luanda (400)2) Numa nova folha de Excel, escreva a seguinte Informação: 63) Utilizando as funções Matemáticas que considerar adequadas, preencha as células a sombreado.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº3 - Funções de Informação1) Observe as seguinte funções de Informação:É.CÉL.VAZIA(valor) ............ Devolve Verdadeiro se o valor for vazio, caso contrário, devolve falsoÉ.ERRO(valor) .................... Devolve Verdadeiro se o valor for Erro, caso contrário, devolve falsoÉ.NÃO.TEXTO(valor) ........... Devolve Verdadeiro se o valor Não for Texto, caso contrário, devolve falsoÉ.NÚM(valor) ..................... Devolve Verdadeiro se o valor for Número, 7 caso contrário, devolve falsoÉ.TEXTO (valor) ................. Devolve Verdadeiro se o valor for Texto, caso contrário, devolve falsoÉÍMPAR(valor) ................... Devolve Verdadeiro se o valor for Impar, caso contrário, devolve falsoÉPAR(valor) ....................... Devolve Verdadeiro se o valor for Par, caso contrário, devolve falsoP9 Mod. 6 Rev. 0
Excel Avançado2) Numa nova folha de Excel, escreva a seguinte Informação: 83) Utilizando as funções de Informação que considerar adequadas, preencha as células a sombreado.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº4 - Funções de Texto1) Observe as seguinte funções de Texto:CONCATENAR(texto1; texto2; texto3…) .....Junta vários itens de textoDIREITA(texto; nº de caracteres) .............Devolve os últimos caracteres que se encontram à direita, baseados no nº de caracteresESQUERDA(texto; nº de caracteres) ..........Devolve os primeiros caracteres que se encontram à esquerda, baseados no nº de caracteresSEG.TEXTO(texto; nº Inicial; nº de Caracteres) ..............Devolve um número específico de caracteres, começando no nº Inicial 9LOCALIZAR(texto a localizar; texto onde localizar) ..............Localiza uma cadeia de texto dentro de outra cadeia de texto e devolve a posição de partidaMAIÚSCULAS(texto)............................Converte o texto em MaiúsculasMINÚSCULAS(texto)............................Converte o texto em MinúsculasNÚM.CARACT(texto) ............................Conta o número de caracteres de um textoP9 Mod. 6 Rev. 0
Excel Avançado2) Numa nova folha de Excel, escreva a seguinte Informação: 103) Utilizando as funções de Texto que considerar adequadas, preencha as células a sombreado.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº5 - Funções de Estatística1) Observe as seguinte funções de Estatística: CONTAR(intervalo)...............................Conta o número de células de um intervalo, que contenham números CONTAR.SE(intervalo; critério…) ..............Conta o número de células de um intervalo (que obedeçam a um determinado critério) CONTAR.VAL(intervalo) ........................Conta o número de células de um intervalo, que não estão vazias CONTAR.VAZIO (intervalo) ...................Conta o número de células de um intervalo, que estão vazias 11 MAIOR(intervalo; k) .............................Devolve o enésimo Maior valor de um conjunto de dados MAXIMO(intervalo) ..............................Devolve o maior valor de um conjunto de dados MENOR(intervalo; k).............................Devolve o enésimo menor valor de um conjunto de dados MÍNIMO(intervalo)...............................Devolve o menor valor de um conjunto de dados MÉDIA(intervalo) .................................Calcula a Média AritméticaP9 Mod. 6 Rev. 0
Excel Avançado2) Numa nova folha de Excel, escreva a seguinte Informação:3) Utilizando as funções de Estatística CONTAR.VAZIO, CONTAR.VAL e CONTAR.SE, preencha as células a sombreado.4) Escreva a seguinte Informação: 125) Utilizando as funções de Estatística que considerar adequadas, preencha as células a sombreado.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº6 - Funções Financeiras1) Observe as funções Financeiras PGTO, IPGTO e VF:PGTO(taxa; nper; va) ................. Calcula o pagamento mensal de um empréstimo, isto é, calcula qual a prestação mensal (a partir de pagamentos constantes e uma taxa de juro constante) é o valor do empréstimo (capital) é o número de pagamentos do empréstimo (nº de prestações) é a taxa de juro do empréstimo (se for ao ano, dividir-se por 12 meses)Exemplo: 13P9 Mod. 6 Rev. 0
Excel AvançadoIPGTO(taxa; período; nper; va) ..... Calcula o pagamento de juros para um determinado período de investimento (de acordo com pagamentos periódicos e constantes e com uma taxa de juro constante) é o valor do empréstimo (capital) número total de períodos de pagamento numa anuidade. é o período que deseja saber os juros é a taxa de juro por período Exemplo: 14VF(taxa;nper; pgto; vp) ................... Calcula o Valor Futuro de um investimento Depósito inicial (valor do investimento) Valor da cada prestação nº de prestações é a taxa de juro por período Exemplo:P9 Mod. 6 Rev. 0
Excel Avançado2 - Resolva o seguinte problema: Determine qual a mensalidade a pagar ao banco por um empréstimo de 90000, sabendo que o banco está disposto a emprestar o dinheiro à taxa nominal fixa de 9%, por um período de 20 anos? Dados do problema: Taxa = 9% / 12 Número de períodos = 12 * 20 anos Valor actual do empréstimo = 90000 Resolução: PGTO( 9%/12; 12*20;90000)3 - Calcule os juros devidos no primeiro mês de um empréstimo de 4 anos de 9000 a 10% de juros ao ano.Dados do problema: 15 Taxa de juro anual (taxa) = 10% / 12 Período = 1 (primeiro mês) Número de períodos = 4 anos * 12 Empréstimo (va) = 9000Resolução: IPGTO( 10%/12; 1; 4 * 12; 9000)4 - Resolva o seguinte problema: Vai efectuar a compra de uma mota (a prestações) com o custo de 20000. O banco que vai conceder o emprestimo durante 5 anos oferece uma taxa anual de 8%. A venda da mota antiga resulta numa entrada inicial de 2000. Qual irá ser o valor de cada prestação mensal? Dados do problema: Taxa anual = 8% / 12 Número de períodos =5 anos * 12 Valor do empréstimo = 20000 (custo) – 2000 (entrada) = 18000 Resolução: PGTO( 8%/12; 5*12; 18000)P9 Mod. 6 Rev. 0
Excel Avançado5 - Calcule os juros devidos no último ano de um empréstimo de três anos de 11000 a 8% de juros ao ano, em que os pagamentos são feitos anualmente. Dados do problema: Taxa de juro anual (taxa) = 8% Período = 3 (último ano) Número de períodos = 3 anos Empréstimo (va) = 11000 Resolução: IPGTO( 8%; 3; 3; 11000)6 - Resolva o seguinte problema: Quanto é que terá amealhado daqui a 20 anos, se efectuar depósitos mensais constantes de 250 à taxa anual de 5%?Dados do problema: 16 Taxa = 5% / 12 Número de períodos = 12 * 20 anos Depósitos mensais = -250Resolução: VF( 5%/12; 12*20; -250)7 - Resolva o seguinte problema: O Banco, concede-lhe um empréstimo mediante prestações mensais de 600 e durante 20 anos, à taxa nominal de 8%. Qual é o valor do empréstimo? Dados do problema: Taxa = 9% / 12 Número de períodos = 12 * 18 anos Depósitos mensais = -600 Resolução: VF( 9%/12; 12*18; -600)P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº7 – Função de Procura1) Observe as seguinte função de Procura PROCV(valor a procurar; lista de dados; nº índice da coluna; 0) ......Procura um valor numa lista de dados) 0 -procura valor exacto 1 -procura valor aproximadoExemplo: Pretende-se saber quais as Existências do Artigo cujo código é o 105 =PROCV(105; A1:C9; 3; 0) Resultado: 4,00 $ 17P9 Mod. 6 Rev. 0
Excel Avançado2) Escreva a seguinte informação: 183) Utilizando a função PROCV, preencha as células a sombreado. Exemplo:P9 Mod. 6 Rev. 0
Excel Avançado Exercício nº8 – Funções de Lógica e Formação Condicional1) Observe as seguinte função Lógica: SE (condição; valor se verdadeiro; valor se falso)......... Esta Função analisa uma condição e retorna o evento verdadeiro ou falso Exemplo: 192) Crie o seguinte mapa de Notas:3) Preencha a coluna G com a média das 4 notas de cada alunoP9 Mod. 6 Rev. 0
Excel Avançado4) Usando a função SE, preencha a coluna H do seguinte modo: Se a média do aluno for Maior ou igual a 10, deverá aparecer a palavra “Aprovado”, caso contrário, deverá aparecer a palavra “Reprovado” Exemplo:5) Seleccione as linhas compreendidas entre a 6 e a 13 e aumente a sua alturapara 40 20Para as próximas alíneas, utilize as opções do grupo Alinhamento6) Seleccione os Nomes e as Notas e alinhe ao meio (verticalmente) )7) Seleccione a frase Escola Secundária e Incline para a Esquerda ( ) )8) Seleccione as células, desde B1 até H1 e una e centre na selecção (9) Coloque o cursor no início da frase Mapa de Notas e molde o texto (P9 Mod. 6 Rev. 0
Excel Avançado Para as próximas alíneas, utilize a opção Formatação Condicional do grupo Estilos10) Seleccione as Notas e realce as que forem maiores que 15 2111) Mantenha as Notas seleccionadas, realce as que forem menores que 10 (realcecom preenchimento Amarelo)P9 Mod. 6 Rev. 0
Excel Avançado12) Limpe as regras de formatação12) Mantenha as Notas seleccionadas, e crie a seguinte nova regra de Formataçãocondicional: Para as Notas >= 11 ....... deverá aparecer um símbolo de Visto Verde Para as Notas 9 e 10 ....... deverá aparecer um ponto de exclamação Amarelo Para as Notas < 9............ deverá aparecer uma cruz Vermelha 22P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº9 - Estudo dos Gráficos1) Transcreva a seguinte informação:Ana Matemática Inglês Português MédiaAndreia 16 16 10 14Bruno 8 12 16 12Vitor 8 10 12 10 10 12 14 122) Seleccione a Guia Inserir3) Seleccione os dados e crie o Gráfico de Colunas correspondente: 23Ana Matemática Inglês Português MédiaAndreia 16 16 10 14Bruno 8 12 16 12Vitor 8 10 12 10 10 12 14 124) Observe agora que, quando o gráfico está seleccionado, surgem mais 3 guias: Estrutura, Esquema e Formatar (com o título “Ferramentas de Gráfico”) 5) Seleccione a Guia EstruturaP9 Mod. 6 Rev. 0
Excel Avançado6) Altere o tipo de gráfico para “gráfico de Barras”7) Troque a ordem das Linhas/Colunas… verifique que o eixo trocou com as legendas8) Altere o esquema do gráfico, para Esquema 3… verifique que as legendas 24 mudaram de posição e os barras ficaram com rótulos9) Escolha um estilo de gráfico ao seu gosto10) Mova o gráfico para uma NOVA folha. Folha esta c/o nome FolhaComGráficoP9 Mod. 6 Rev. 0
Excel Avançado11) Observe o conteúdo das suas folhas, clicando alternadamente numa e noutra:12) Mova novamente o gráfico para a Folha1 2513) Observe que a FolhaComGrafico desapareceu14) Seleccione a Guia Esquema 15) Seleccione a LegendaP9 Mod. 6 Rev. 0
Excel Avançado16) Formate a selecção para Preenchimento das legendas com um fundo Amarelo17) Seleccione o Título do Gráfico e escreva Mapa de Notas18) Seleccione Formate a selecção e incline o Título um pouco 2619) Apresente mais linhas de grelha no seu gráfico20) (Na folha de dados) seleccione os nomes dos alunos e as notas de matemática:Ana Matemática Inglês Português MédiaAndreia 16 16 10 14Bruno 8 12 16 12Vitor 8 10 12 10 10 12 14 12P9 Mod. 6 Rev. 0
Excel Avançado21) Insira o respectivo gráfico Circular 3D22) Seleccione a Guia Esquema 2723) Muda a apresentação da Legenda para a Esquerda24) Apresente os rótulos ao centroP9 Mod. 6 Rev. 0
Excel Avançado25) Seleccione a Guia Formatar26) Seleccione o Título do Gráfico e altere o estilo de Forma (ao seu gosto)27) Seleccione a Área do Gráfico, e no preenchimento da forma, seleccione uma Imagem ao seu gosto (deverão existir alguns exemplos de imagens no seu computador) 28 28) Seleccione o Título do Gráfico e aplique um Estilo do WordArt (ao seu gosto)P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº10 – Esquema de Página1) Transcreva a seguinte informação:2) Seleccione o mês de Jan e continue a sequência para a direita, de forma a preencher as células até Dez3) Seleccione os valores numéricos e continue a sequência para baixo e para a direita, de forma a preencher todo o mapa: 294) Seleccione a Guia Esquema de página5) Altere as margens da página para as seguintes: Esquerda=2cm; Direita=2cm; Superior=3cm; Inferior =2,5cmP9 Mod. 6 Rev. 0
Excel Avançado6) Altere a orientação da página para horizontal7) Coloque o cursor em cima da palavra Borrachas e insira uma Quebra de Página8) Defina os Títulos de impressão como se apresenta na figura: 309) Pré-visualize e observe que o mapa ocupa 2 páginas, e que ambas as páginas apresentam as linhas e colunas que indicou na alínea anteriorP9 Mod. 6 Rev. 0
Excel Avançado10) Seleccione novamente a Guia Esquema de página e em configurar Página: a) Reduza o Tamanho da página para 75% b) Centre a informação na página (quer horizontalmente, quer verticalmente) e reserve 1cm para o cabeçalho e também 1 cm para o rodapé c) No Cabeçalho ao centro da página, escreva frase “Exercício de Excel”. E no Rodapé à esquerda, adicione a Data e Hora do sistema 3111) Desactive e Active (as vezes que quiser) as opções de Folha: a) Ver Linhas de Grelha, e observe o que acontece às linhas do seu ecran b) Ver Títulos, e observe o que acontece aos títulos das colunas/linhas12) Certifique-se que ficam Activadas as opções Ver Linhas de Grelha e Ver Títulos.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº11 – Auditoria de Fórmulas1) Numa nova folha, transcreva a seguinte informação:2) Seleccione a Guia Fórmulas 323) a) Procure a função Soma e efectue os 2 totais do mapa b) Procure a função Média e efectue as 2 médias do mapaP9 Mod. 6 Rev. 0
Excel Avançado4) a) Coloque o cursor na célula que contém o Total do que Compraram e no grupo Nomes Definidos, atribua o nome TotalCompraram a essa célula. b) De igual modo, coloque o cursor na célula que contém o Total do que Pagaram e atribua o nome TotalPagaram a essa célula 335) Calcule a diferença dos totais (verifique que ao invés de coordenadas, aparecem os nomes que definiu)P9 Mod. 6 Rev. 0
Excel Avançado6) Coloque o cursor no total 7500 e pressione a opção Analisar Precedentes e observe a seta que surgiu7) Mantendo o cursor no total 7500, pressione a opção Analisar Dependentes e observe a seta que surgiu8) Pressione a opção para remover as setas 349) Pressione a opção para Mostrar as fórmulas e observe as suas fórmulas, ao invés dos resultados.10) Oculte novamente as fórmulas11) Grave este trabalho e feche-oP9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº11 – Auto Filtro e Filtro Avançado1) Numa nova folha, transcreva a seguinte informação: 352) Seleccione a Guia Dados3) Coloque o cursor numa das células dos Nomes4) Ordene alfabeticamenteP9 Mod. 6 Rev. 0
Excel Avançado5) E agora ordene alfabeticamente, Mas por ordem Descendente6) Ordene alfabeticamente da seguinte forma: 1º pelo Género de 2º pelo Nome (ordem Ascendente)7) Active o botão Filtrar. Observe que surgiram umas setas em célula do cabeçalho: 368) Apresente apenas as linhas correspondentes ao género EconomiaP9 Mod. 6 Rev. 0
Excel Avançado9. Apresente os géneros Economia e Livros Técnicos10.Apresente TODOS11.Apresente apenas as linhas que contêm compras entre 30 e 4012.E apenas as do género Economia 3713.Desactive a opção Filtrar14.Copie o capeçalho da tabela, para a coluna G15.Por baixo da palavra Género, escreva Economia, Livros TécnicosP9 Mod. 6 Rev. 0
Excel Avançado16. Utilizando a opção de Filtro Avançado, apresente apenas as linhas que contêm Economia ou Livros Técnicos 3817.Limpe o filtro18. Utilizando novamente a opção de Filtro Avançado, apresente apenas as linhas do género Economia e cujas compras sejam superiores a 25 (nota: verifique se a área do critério ficou bem indicada, pois não pode ter linhas a mais nem a menos)19. Limpe o filtroP9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº12 – Proteger Folha e Partilha de LivrosI - Pretende-se que proteger a Folha. Para tal: primeiro tem que informa-se quais ascélulas a não serem protegidas… e depois sim, é que se irá proteger a folha1) Numa nova folha, transcreva a seguinte informação: 392) Seleccione as células que não se pretendem proteger: desde a C5 até à H13P9 Mod. 6 Rev. 0
Excel Avançado3) No meio da zona seleccionada, pressione o botão direito do rato e: Formatar Células + Separador Protecção + desmarque Protegida e Ok (nota: toda a folha vai ficar protegida, excepto estas células)4) Proteja a folha: Para tal, no friso Rever seleccione Proteger Folha e Ok (como se 40 trata de um exemplo, não necessita de colocar palavra-passe)5) Agora, experimente a escrever algo na folha e observe que APENAS pode escrever na zona que definiu como desprotegida na alínea 3P9 Mod. 6 Rev. 0
Excel Avançado6) Desproteja a folha: Para tal, no friso Rever, seleccione Desproteger Folha e OkII - Pretende-se que partilhe um ficheiro Excel (um qualquer ao seu gosto). Para tal,execute os seguintes passos:4) Seleccione o friso Rever, e clique em Partilhar livro.5) Seleccione a caixa de verificação Permitir alterações por mais de um utilizador em simultâneo e, em seguida, clique em OK. 416) Quando pedido, guarde o livro… (outros utilizadores passarão a ter acesso e este ficheiro simultaneamente).P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº13 – Consolidação de DadosIntrodução ao Exercício Nº13:A consolidação de dados - é um recurso do Excel que permite o agrupamento ea totalização de informações que estejam em vários intervalos de dados.Por exemplo, se tiver numa folha de cálculo os registos das despesas de cada um dosescritórios regionais que a empresa possui, é possível utilizar uma consolidação paracolocar estes registos numa folha de cálculo para as despesas conjuntas da empresa.O Excel fornece diferentes modos de consolidar dados:Consolidar por posição Consolide pela posição quando os dados de todas as áreas de origem estão 42 dispostos pela mesma ordem e no mesmo local; por exemplo, se existem dados de uma série de folhas de cálculo criadas a partir do mesmo modelo, é possível consolidar os dados por posição.Consolidar por categoria Consolide pela categoria quando deseja resumir um conjunto de folhas de cálculo que têm os mesmos rótulos de linha e coluna, mas que organizam os dados de formas diferentes. Este método combina os dados de cada folha de cálculo que tenham rótulos correspondentes.Outras formas de combinar dados É possível criar um Relatório de tabela dinâmica a partir de múltiplos intervalos de consolidação. Este método é semelhante à consolidação pela categoria, mas oferece mais flexibilidade na reorganização das categorias.P9 Mod. 6 Rev. 0
Excel AvançadoConsolidar dados por posição: 1- Escreva os seguintes dados na Folha1: 2- Escreva os seguintes dados na Folha2: 43 3- Seleccione a Folha3 e clique na célula A1 (vai ser aqui a área de destino para os dados consolidados). 4- No friso Dados, clique em Consolidar. 5- Escolha a função de Soma. 6- Na caixa De ‘referência’, escreva (seleccione) cada área de origem que pretende consolidar, e em seguida, clique em Adicionar: Seleccione a primeira área Folha1!$A$1:$C$6 e Adicionar. Seleccione a segunda área da segunda Folha2!$A$1:$C$6 e Adicionar.P9 Mod. 6 Rev. 0
Excel Avançado 7- Em ‘Utilizar rótulos na’, seleccione a caixa de verificação Linha de cima e a caixa de verificação coluna à esquerda.Nota: Se pretender que fique sempre ligado à origem, seleccione ‘Criar Ligações aos dados deorigem’ 8- Clique em ' OK ' (e depois verifique que foram somados os valores)Consolidar dados por categoria: 44 1- Crie um NOVO ficheiro e escreva os seguintes dados na Folha1:P9 Mod. 6 Rev. 0
Excel Avançado2- Clique na célula A1 na Folha2.3- No menu Dados, clique em ' consolidar ' .4- Escolha a função Soma.5- Na caixa De referência: Escreva a primeira área Folha1! $A$2:$C$6 e Adicionar. Escreva a segunda área Folha1! $A$12:$C$16 e Adicionar.6- Em Utilizar rótulos na, clique para seleccionar a caixa de verificação coluna àesquerda. 45Nota: Se pretender que fique sempre ligado à origem, seleccione ‘Criar Ligações aos dados deorigem’7- Clique em ' OK '.P9 Mod. 6 Rev. 0
Excel Avançado Exercício Nº14 – Validação de DadosIntrodução ao Exercício Nº14:Com a Validação de Dados podemos limitar os valores que serão inseridos numa célulaou intervalo de células. Por exemplo, podemos limitar os valores de uma coluna paraque não sejam inseridos valores maiores do que 10000. Outro exemplo: Podemoslimitar as entradas de uma coluna Cidade de tal maneira que apenas sejam inseridosvalores de uma determinada tabela de cidades.Além dos valores inseridos podemos definir o tipo de dados inseridos. Por exemplo,podemos definir que os valores num intervalo de células devem ser, obrigatoriamentenuméricos. Neste caso, se o utilizador digitar um texto, o Excel não aceita a entrada eemite uma mensagem de erro.Esta funcionalidade facilita bastante a digitação, além de evitar que sejam inseridosvalores que não estão na tabela. 461- Crie uma nova folha de cálculo com a seguinte informação:P9 Mod. 6 Rev. 0
Excel Avançado2- Configure os seguintes critérios para limitar a entrada de dados nas colunas: Cidade: Apenas pode conter uma das seguintes: Brasília, Porto Alegre, Rio de Janeiro, Santa Maria ou São Paulo. Total de Arrecadação: Não pode ser superior a 1000000. Data de Arrecadação: Tem que estar dentro do ano de 2012, ou seja, entre 01/01/2012 e 31/12/2012.Passos a seguir para a resolução deste exercício:3- Na coluna Cidade queremos definir uma tabela de valores permitidos. O primeiropasso é digitar esses valores num intervalo de células da Folha de cálculo (no nossocaso, estes já estão no intervalo E3:E8)4- Para configurar critérios de validação, o primeiro passo é seleccionar as célulasonde os critérios serão aplicados. Seleccione o intervalo de A4:A50. Com isso os 47critérios que definiremos para o campo Cidade, serão válidos para as células de A4 atéA50.5- Seleccione Dados Validação de dados.P9 Mod. 6 Rev. 0
Excel Avançado6- Será apresentada a janela Validação de dados. Na tabela “Por” seleccione a opção“Lista”. Após seleccionar “Lista”, o campo Origem será activado. No campo Origemseleccione o intervalo de células onde estão os valores que serão inseridos pela colunaCidade. No nosso exemplo é o intervalo de E4:E8 487- Clique em OK.8- Observe que as células da coluna Cidade já são transformadas numa Caixa decombinação. Ao abrir esta Caixa de combinação são apresentados apenas os valoresdo intervalo E4:E8, conforme indicado na Figura.P9 Mod. 6 Rev. 0
Excel Avançado9- Tente digitar uma cidade que não está na tabela, por exemplo Luanda. Ao tentarsair da Célula, o Excel emite a mensagem de erro indicada na Figura seguinte,informando que o valor digitado não é válido. Ou seja, o Excel apenas aceita osvalores que existem na tabela.10- Clique em Cancelar para fechar a mensagem de erro.11- Agora vamos definir o critério para que a coluna Total de Arrecadação nãoaceite valores maiores do que 1000000 (isto é, < 1000000). Para tal, Seleccione ointervalo de B4:B50 e depois, seleccione o comando Dados –> Validação de dados. 4912- Será apresentada a janela Validação de dados. Na Lista “Por” seleccione NúmeroInteiro. Nos “Dados” escolha é menor que e indique 1000000.13- Clique em OK.P9 Mod. 6 Rev. 0
Search
