Baixe Excel avançado empresarial e outras Teses (TCC) em PDF para Informática Empresarial, somente na Docsity!
Sumário
- INTRODUÇÃO
- Personalizando células
- Alguns modelos de máscaras de entrada:
- Nomeando intervalo de células ou a célula.
- Usando as Referências Relativa e Absoluta
- AUDITORIA DE CÉLULAS
- 1.1 RASTREAR PRECEDENTES
- 1.2 RASTREAR DEPENDENTES
- 1.3 REMOVER SETAS..................................................................................................................................
- 1.4 MOSTRAR FÓRMULAS
- 1.5 VERIFICAÇÃO DE ERROS
- 1.6 AVALIAR FÓRMULA
- FILTRO AVANÇADO
- 2.1. CARACTERES CURINGA
- 2.1.1 APLICAR CARACTERES CURINGAS ......................................................................................................
- FUNÇÕES
- 3.1 FUNÇÕES DE RESUMO
- 3.2 EXEMPLOS DE FUNÇÕES
- 3.2.1 PROCV ................................................................................................................................................
- 3.2.2 SE ........................................................................................................................................................
- 3.2.3 SOMASE..............................................................................................................................................
- 3.2.4 CONT.SE ..............................................................................................................................................
- 3.2.5 CONT.NUM .........................................................................................................................................
- 3.2.6 CONTAR.VAZIO ...................................................................................................................................
- 3.2.7 CONT.VALORES ...................................................................................................................................
- 3.2.8 DATA E HORA ......................................................................................................................................
- 3.2.9 SEN
- 3.2.10 COS
- 3.2.11 TAN
- 3.2.12 ABS
- 3.2.13 ÍMPAR ...............................................................................................................................................
- 3.2.14 PAR ...................................................................................................................................................
- 3.2.15 ARRED ..............................................................................................................................................
- 3.2.16 ARREDONDAR PARA CIMA ...............................................................................................................
- 3.2.17 ARREDONDAR PARA BAIXO ..............................................................................................................
- 3.2.18 MOD .................................................................................................................................................
- 3.2.19 POTÊNCIA .........................................................................................................................................
- 3.2.20 PI ......................................................................................................................................................
- 3.2.21 MODO ..............................................................................................................................................
- 3.2.22 ALEATÓRIO .......................................................................................................................................
- Funções lógica
- Pesquisa e referência
- Informações
- Matemática e trigonométrica
- Funções de texto
- Funções Financeiras
- FUNÇÕES BANCO DE DADOS
- TABELA DINÂMICA
- 4.1 CRIAR UM RELATÓRIO DE TABELA DINÂMICA
- GRÁFICO DINÂMICO.....................................................................................................................................
- CENÁRIO
- ATINGIR METAS
- SUBTOTAL
- VALIDAÇÃO DE DADOS
- SOLVER
- FORMULÁRIO
- PROTEGER PLANILHA
- Macro no aplicativo Excel
- EXERCÍCIOS
- REFERÊNCIA
Para utilizar a categoria personalizada e poder criar as máscaras de entrada de dados temos que clicar no tipo que está escrito geral e apagar e digitar o modelo desejado.
Alguns modelos de máscaras de entrada:
Telefone – (00) 0000-
Cep – 00000-000 ou 00“.”000“-“
Cpf – 000“.”000“.”000 “-”
Cnpj – 00“.”000“.”000“/”0000“-“
Exemplo:
Nomeando intervalo de células ou a célula.
Para que nomear uma célula ou intervalo de célula? Parafacilitar a criação das fórmulas como a compreensão da mesma. Nas planilhas grandes, temos grandes dificuldades de ir e voltar para criação de fórmulas assim usando a célula nomeada fica fácil de lembrar.
Para nomear um intervalo, selecione o intervalo de célula desejado e vá para caixa de nome e escreva o nome do intervalo.
Ou Selecione o intervalo de célula desejado e vá para o menu fórmulas> o ícone definir nome> e escreva o nome do intervalo e OK.
Nomear célula basta selecionar a célula desejada ir para a caixa de nome digitar o nome da célula ou ir ao menu fórmula e clicar no ícone definir nome e digitar o nome da célula e OK.
Caso precise apagar ou renomear o nome do intervalo ou da célula click no menu>fórmula>gerenciador de nomes
Caixa de nome
Exemplo:
As células que estão cinza vamos nomeá-las. A célula (B2) vai se chamar de DESCONTO e a célula (D2) de ACRÉSCIMO , na célula D9 nomear de subtotal Seleciona de( B4 até B8) e nomeamos de qtde e de (C4 até C8) e nomeamos de preçounitário , e de D4 até D8 nomeamos de total Na célula D4, fazemos a seguinte fórmula =qtdepreçounitário No subtotal, célula D9 digite =soma(total) Desconto da compra, na célula D10, fazemos a seguinte fórmula =subtotaldesconto Acréscimo da compra, na célula D11, fazemos a seguinte fórmula =subtotal*acréscimo Total da compra: =subtotal-d10+d
INSS; =b3b$15 e IMPOSTO SINDICAL: =b3b$
Ficando assim:
1. AUDITORIA DE CÉLULAS
A auditoria de Fórmulas do Excel 2010 é um grupo de ferramentas responsável pelo rastreamento precedente e dependente de células que estão sendo utilizadas em fórmulas criadas nas planilhas. Esta funcionalidade é importante para localizar as células envolventes em cálculos realizados em uma tabela qualquer, um recurso muito útil a ser utilizado em tabelas complexas e com muitas fórmulas com a finalidade de não deixar o usuário confuso.
1.1 RASTREAR PRECEDENTES
A função para rastrear células Precedentes é utilizada quando você tem a localização exata da fórmula e necessita localizar todas as células envolventes utilizadas na fórmula selecionada. Você deve selecionar a célula que possui o resultado da fórmula e clica na função Rastrear Precedentes, localizado na Guia Fórmulas, grupo Auditoria de Fórmulas.
Para visualizar, preencha a planilha conforme abaixo, clique na celular que possui o resultado da fórmula e clique em Rastrear Precedentes. Então todas as células precedentes do resultado serão selecionadas na cor Azul e apontarão com uma seta para a fórmula do resultado.
1.2 RASTREAR DEPENDENTES
A função para rastrear células Dependentes é inversa a anterior, pois é utilizada quando você tem a localização exata das células e necessita localizar a fórmula que depende da célula selecionada para gerar o resultado.
No exemplo você seleciona uma célula, clica na função Rastrear Dependentes, localizado na Guia Fórmulas, grupo Auditoria de Fórmulas .e automaticamente a fórmula que
1.5 VERIFICAÇÃO DE ERROS
Esta função é responsável por buscar erros nas tabelas. Útil ao final de qualquer trabalho no Excel 2010. Se não houver erros a mensagem será mostrada. Para executa-la basta acionar a função Verificação de Erros , localizado na Guia Fórmulas, grupo Auditoria de Fórmulas.
1.6 AVALIAR FÓRMULA
Para utilizá-la selecione uma fórmula de sua tabela e clique na função Avaliar Fórmula localizada na Guia Fórmulas, grupo Auditoria de Fórmulas. Como no exemplo abaixo você poderá clicar em Avaliar para confirmar o resultado. olv
Resultado:
2. FILTRO AVANÇADO
Quando o filtro não atender a sua necessidade, como, por exemplo, havendo duas ou mais condições em um campo, você pode aplicar o filtro avançado localizado na guia Dados , grupo Classificar e Filtrar, opção Avançado.
Exemplo: Plan1: será considerada a base de dados
guia Dados , grupo Classificar e Filtrar, opção Avançado. Será exibida a janela como filtro avançado.
Marque a Ação Copiar para outro local. Em seguida, selecione no campo Intervalo da lista o intervalo d a planilha que possui todos os dados, neste caso, a plan1.
No campo Intervalo de critérios selecione as células da plan2 que possui os dados do critério. No campo Copiar para, marque a plan3 a célula a1 e em seguida botão OK.
O resultado na plan3 será com os registros onde a região for igual a Sudeste.
2.1. CARACTERES CURINGA
Caracteres Curingas são utilizados para ampliar nosso escopo de pesquisas. São úteis quando estamos procurando um grupo com entradas semelhantes, mas não idênticas, ou quando não temos uma boa recordação a respeito da informação completa.
Estes caracteres nos ajudam a filtrar um intervalo de células utilizando não só critérios simples, mas também complexos, como no caso do filtro Avançado.
2.1.1 APLICAR CARACTERES CURINGAS
Suponhamos que numa planilha bem extensa, tenhamos que pesquisar alguns dados, mas, não temos com exatidão todos os critérios para efetuar nosso trabalho. Sabemos apenas a inicial de um dos dados e o final do outro. Neste caso, a aplicação de Caracteres Curingas será fundamental para elaborarmos o que nos foi solicitado.
Os caracteres curingas a seguir podem ser usados como critérios de comparação para filtros, e durante a pesquisa e substituição de conteúdo.
Use Para Localizar
? (ponto de interrogação) Qualquer caractere único Por exemplo, antoni? localizará "antonio" e "antonia"
- (asterisco) Qualquer número de caracteres Por exemplo, *este localiza "Nordeste" e "Sudeste"
~ (til) seguido de ?, *, ou ~ Um ponto de interrogação, asterisco ou til Por exemplo, fy91~? localizará "fy91?"
Para exemplo, faremos uso de um arquivo com poucos dados, a fim de facilitar a compreensão, porém, este recurso pode ser utilizado com inúmeros dados, por ser esta a real intenção do Filtro Avançado. Para verificar, preencha a planilha conforme abaixo:
Clicando no botão OK teremos o resultado da filtragem avançada seguindo os critérios especificados através dos Caracteres Curingas :
3. FUNÇÕES
3.1 FUNÇÕES DE RESUMO
O grupo de ferramentas Biblioteca de Funções , localizada na guia Fórmulas do Excel 2010, apresenta algumas categorias de funções como Financeira , Lógica , Texto e etc. Porém nem todas as categorias estão expostas no grupo de ferramentas mencionado. Aproveito para listar todas as categorias com suas respectivas descrições, veja:
Categoria de Funções
- Cubo : Possui cerca de 7 funções que manipulam dados de procedimentos armazenados em banco de dados existente.
- Banco de Dados : Possui cerca de 12 funções que auxiliam na manipulação de dados de uma base existente na planilha, permitindo realizar análises em registros, tais como média, desvio padrão e outros.
- Engenharia : Possui cerca de 39 funções que permitem realizar conversões e cálculos com números decimais, hexadecimais e logaritmos.
- Financeiras : Possui cerca de 53 funções que manipulam taxas, valores de depreciação, pagamento e outras funções similares à calculadora científica e financeira.
- Informação : Possui cerca de 17 funções que manipulam e retornam expressões de informações referentes aos dados manipulados em uma ou mais células, como tipo de erro, conteúdo e tipo de célula.
- Lógicas : Possui cerca de 6 funções que retornam valores lógicos ou booleanos (verdadeiro/falso) e outros tipos.
- Procura e Referência : Possui cerca de 18 funções que permitem pesquisar dados e referências em um conjunto de células ou listas.
- Matemáticas e Trigonométricas : Possui cerca de 60 funções que permitem arredondar valores exibidos em cálculos, seno e co-senos e cálculo raiz quadrada.
- Estatísticas : Possui cerca de 82 funções que permitem realizar análise de dados, como médias, desvios, variância, etc.
- Texto : Possui cerca de 28 funções que permitem manipular células com conteúdo de texto, tais como procura e substituição de dados por outro e conversões para maiúsculas, minúsculas e valores.
- Data e Hora : Possui cerca de 20 funções que manipulam datas e horas, realizando conversões e localização de ano, mês, dia da semana e minutos.
- Suplemento e Automação : Possui cerca de 5 funções que visam atualizar vínculos, bibliotecas e procedimentos de consulta a banco de dados.
3.2 EXEMPLOS DE FUNÇÕES
3.2.2 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.
Na planilha abaixo, devemos criar uma fórmula que analise a média de cada aluno para retornar o valor “Aprovado” ou “Reprovado” no campo “Situação”. Para que o aluno seja aprovado, a média deverá ser maior ou igual a 7.
Neste caso, criamos a coluna situação e inserimos o comando SE conforme abaixo:
Teremos o seguinte resultado:
3.2.3 SOMASE
Esta função é utilizada para somar as células especificadas por um determinado critério ou condição.
Sintaxe: =somase(intervalo; critérios; intervalo_soma)
Em seguida, vamos acrescentar as seguintes informações na planilha:
