Prévia do material em texto
Trainning Education Services ® - Sede: Av. Paulista, 2006, 16° andar – SP
São Paulo (11) 3171-2002 - Rio de Janeiro (21) 4063-6518 - Brasília (61) 4063-7766 - Belo Horizonte (31) 4063-
8187
Vitória (27) 4062-9192 - Curitiba (41) 4063-8083 - Porto Alegre (51) 4063-9474 - Recife (81) 3092-6500 - Salvador
(71) 4062-9323 – Manaus (92) 3642-1539 – Cuiabá (65) 4052-9232 – Fortaleza (85) 4062-9171 – Goiânia (62)
3942-5919 – Natal (84) 3201-4110
www.trainning.com.br - comercial@trainning.com.br
Sumário
Funções:
Estatística.
Texto.
Data e Hora.
Lógica.
Pesquisa e Auditoria de Fórmulas.
Informações.
Assistente de Soma Condicional.
Financeiras:
Fórmulas e Funções Financeiras.
Cálculos Financeiros – Utilizando-se de Funções.
Importação de Dados para o Excel:
Importar arquivos de texto para o Excel.
Importar arquivos de dados para o Excel.
Criar consulta à base de dados.
Vincular dados do Excel no Access.
Consultar para obter dados de uma página da Web.
Filtro, Classificação e Filtro Avançado:
Aplicar o AutoFiltro.
Aplicar o filtro a mais de um campo.
Classificar com o AutoFiltro.
Classificação Avançada.
Classificação por lista personalizada.
Classificação por cores.
Filtro avançado.
Filtro avançado com critérios múltiplos.
2
Formatos Condicionais Avançados:
Formatos Condicionais.
Funções de Banco de Dados:
Utilizando as funções BDSOMA e BDMÉDIA.
o Função BDSOMA.
o Função BDMÉDIA.
Utilizando as funções BDMÁX e BDMÍN.
o Função BDMÁX.
o Função BDMÍN.
Utilizando a função BDEXTRAIR.
Utilizando a função BDCONTARA.
Tabela Dinâmica:
Atribuindo Campos.
Removendo Campos de uma Tabela Dinâmica.
Atualizando informações em uma Tabela Dinâmica.
Mudando o layout de uma Tabela Dinâmica.
Formatando Tabelas Dinâmica.
Ocultando ou Mostrando Itens em um Campo.
Agrupando Dados da Tabela Dinâmica.
Ocultando e Mostrando Detalhes.
Campos Calculados.
Criando Nossos Campos Calculados.
Modificando ou Excluindo Campos Calculados.
Criando Gráficos Dinâmicos.
Funções de Banco de Dados:
Banco de Dados:
o Função BDSOMA.
o Função BDMÉDIA.
o Função BDMÁX.
o Função BDMÍN.
o Função BDCONTARA.
3
Criando e Gerenciando Cenários:
Exibindo Cenários.
Editando Cenários.
Excluindo Cenários.
Criando um Resumo dos Cenários.
Atalho para Cenários.
Atingir Meta:
Como efetuar o Atingir Meta.
Solver:
Salvando como um Cenário os Valores Variáveis do Solver.
Criando um Relatório no Solver.
Auditoria:
Como efetuar a Auditoria.
Modos de Exibição:
Configurando e Criando Modos de Exibição.
Criando Formulários Personalizados:
Criar planilhas combinando controles para automatização ds planilhas.
Combinar esses controles com macros e outros recursos.
Objetos no VBA:
Acessando o VBA.
Projeto – VBAProject.
Propriedades.
Módulos.
Objetos.
Métodos e Propriedades.
Métodos.
Localizando Procedimentos.
Variáveis de Memória.
Adicionando Comentários ao Código.
Como Definir Variáveis.
4
Declarando Variáveis de Memória.
Tipos de Dados.
Escopo das Variáveis.
Criando Funções:
Função NovaData.
Função Valor Imposto.
Função Premio.
Interligando Sub-Rotina com Function.
5
Microsof Excel 2013 Avançado
Carga Horária: 24 horas.
Disponível em Português.
Objetivos:
Este curso destina-se a usuários que já utilizam o Microsoft Excel e que necessitam
automatizar tarefas, tais como Validação de Dados, Exibição de Cenários, Trabalhar com
mais Funções e principalmente com Banco de Dados. Aborda ainda o desenvolvimento de
Aplicações e Automatização de Tarefas através de Macros. Traz uma breve introdução à
Linguagem de Programação VBA (Visual Basic for Application).
Pré-requistos:
Ter participado do Curso Microsoft Excel Básico ou ter conhecimentos específicos.
Funções Estatísticas do Excel
Visão Geral
O Excel e as estatísticas – noções básicas
Use o Excel para gerenciar seus dados estatísticos.
Vamos começar pelo começo: criar uma fórmula no Excel usando funções estatísticas é
tão fácil quanto usar qualquer outra função. Se você souber como usar as funções do
Excel e conhecer um pouco de estatística, estará pronto para prosseguir.
Usar o Excel para fazer cálculos estatísticos diários pode economizar bastante tempo e
trabalho. Esta lição explicará as noções básicas das razões de usar o Excel e apresentará
uma revisão rápida de como criar fórmulas no Excel.
6
Observação - Não confunda funções com fórmulas: uma função é apenas o nome, como,
por exemplo, DESVPAD ou TENDÊNCIA. Uma fórmula é uma função em ação, com
argumentos incluídos, como, por exemplo, =MÉDIA(A1:A33).
Por que o Excel?
Use as funções estatísticas do Excel no escritório, em sala de aula e na fábrica.
As aplicações práticas de estatística no Excel são variadas. Talvez você tenha os seus
próprios motivos para querer usar funções estatísticas no Excel. Veja alguns exemplos para
aguçar a sua curiosidade:
Um gerente de vendas pode querer projetar as vendas do próximo trimestre (função
TENDÊNCIA).
Um professor pode querer atribuir graduações em uma curva com base na média das
notas (MÉDIA ou MED, ou até MODO).
Um fabricante responsável pela qualidade do produto pode estar interessado na
quantidade de itens que está dentro dos limites de qualidade aceitáveis (DESVPADP
ou VAR).
Um pesquisador pode precisar saber quantas respostas dadas em uma pesquisa
ocorrem em um intervalo de respostas específico (FREQUÊNCIA).
7
Variância em ação
Um gerente de vendas usa a função VAR para analisar as estimativas de vendas de três
vendedores.
Suponha que um gerente de vendas esteja analisando os totais de vendas de três
vendedores diferentes para comparar os seus desempenhos. Uma das várias funções
estatísticas que o gerente poderia usar é a variância (VAR).
A variância mede a diferença existente entre os valores individuais dos dados. Dados com
variância baixa contêm valores que são idênticos ou semelhantes: 6, 7, 6, 6, 7. Dados com
variância alta contêm valores que não são semelhantes: 598, 1, 134, 5, 92.
Você poderá tentar a função de variância na sessão de exercícios.
Como construir uma fórmula estatística
Uma fórmula usada para descobrir a variância com base em um intervalo; nesse caso, da
célula B8 à B75.
8
Se você pode usar qualquer função do Excel, pode usar uma função estatística. Elas são
criadas da mesma forma:
1. Sempre comece com um sinal de igual (=).
2. Depois o nome da função.
3. E por último os argumentos entre parênteses.
Em fórmulas estatísticas, os argumentos costumam ser um intervalo, como você pode
observar na imagem acima.
Quando o Excel não é suficiente
Há situações em que o Excel não é tão eficiente — por exemplo, se você costuma fazer
pesquisa em laboratório.
Se você estiver fazendo análises estatísticas pesadas, é possível que o uso do Excel não
seja suficiente para atender às suas necessidades. Por exemplo, se você deseja fazer
análise de regressão, o Excel requer que os valores x estejam em um bloco único (colunas
ou linhas adjacentes), o que talvez não seja conveniente para você.
Nesses casos, convém usar um pacote de estatísticas dedicado que aborde uma gama de
opções de análise estatística e de funções relacionadas. Alguns pacotes também exibem
resultados adicionais associados a uma análise específica. Usuários típicos desses tipos de
programas são pessoas que trabalham em departamentos estatísticos de instituições
acadêmicas ou em laboratórios de pesquisa.
Muitos desses programas de análise estatística estão à venda no mercado. Além disso, há
suplementos do Excel criados por terceiros para lidar com situações especiais.
9
Alguns deles podem ser encontradosna seção Office Marketplace do site Microsoft Office
Online.
Crie fórmulas eficientes
Para obter êxito no Excel, você precisa saber como criar fórmulas eficientes.
Se o Excel é tão bom para fazer estatísticas, por que nem todas as pessoas o utilizam?
Bem, uma razão é que às vezes é difícil saber o que há lá. A outra razão é que as pessoas
podem obter erros ao tentar encontrar os resultados desejados.
Esta lição aborda alguns dos problemas que você pode encontrar ao criar fórmulas no
Excel e como evitá-los.
Qual é o problema?
Existem várias mensagens de erro diferentes que poderão ser exibidas se você criar
fórmulas incorretamente.
10
A construção de fórmulas eficientes no Excel geralmente faz com que você aumente o seu
conhecimento no uso de funções e evite cometer erros comuns.
O importante é usar a função certa e saber como criar uma fórmula adequada. O uso de
uma fórmula criada incorretamente, ou da função errada, geralmente resulta em uma
resposta incorreta.
Esses problemas são encontrados por qualquer pessoa que trabalha no Excel, não apenas
por aquelas que usam funções estatísticas. Para obter noções básicas sobre como criar
fórmulas no Excel, consulte o curso "Encontrar funções e inserir argumentos".
Como resolver o problema
Vá a guia Fórmulas, grupo Biblioteca de Funções e, em seguida, clique em Inserir
Função.
A caixa de diálogo Inserir Função
Como você pode observar na imagem abaixo, é possível escolher o tipo de função
desejada e depois escolher uma função específica na lista. Também é possível obter uma
descrição da função, bem como solicitar ajuda sobre ela.
11
Observação - Se você não conseguir encontrar a função desejada em uma determinada
categoria, tente uma categoria relacionada. Por exemplo, observe que a função de média
(MÉDIA) está listada na categoria Estatística, não em Matemática e Trigonometria, mas a
função de número aleatório (ALEATÓRIO) está listada em Matemática e Trigonometria.
Se você estiver em dúvida, consulte o link da ajuda para obter maiores informações.
Tópicos da Ajuda do Excel e a caixa de diálogo Inserir Função podem ajudá-lo a aprender
a criar fórmulas eficientes.
12
A importância da ortografia
A área abaixo da curva até a letra z mostra a probabilidade de obter z. Esses valores
também estão disponíveis em tabelas estatísticas.
Agora observe a Distribuição Normal, que é uma curva simétrica, suave, cuja forma lembra
um sino. Se você medir a altura de uma amostra de plantas que crescem sob as mesmas
condições, a distribuição das alturas se aproximaria à curva Normal.
Há duas funções no Excel para a Distribuição Normal: DIST.NORM e DIST.NORMP. Se você
errar a ortografia do nome da função (um erro bastante comum), obterá uma resposta
incorreta ou nenhuma resposta.
Na sessão prática, você usará a função DIST.NORMP, que calcula probabilidades
associadas à Distribuição Normal Padrão. A curva de Distribuição Normal Padrão está
centralizada em 0 (a média). A área abaixo da curva é 1. A probabilidade está espalhada
de forma consistente com desvio padrão da Distribuição Normal Padrão de valor 1. Isso
significa que cerca de 95% da probabilidade ocorre entre -2 e 2.
Se você quiser saber o grau de probabilidade à esquerda de z na curva, a fórmula seria
=DIST.NORMP(z). Você pode usar essa função, em vez de uma tabela de probabilidade de
Distribuição Normal Padrão.
13
Problemas mais comuns
Alguns erros comuns que ocorrem em planilhas.
Veja alguns dos erros mais comuns ao construir fórmulas
Esquecer o sinal de igual (=) no início da fórmula.
Inserir um espaço antes do sinal de igual (=).
Inserir dados no formato errado (por exemplo, como texto em vez de como números).
Selecionar o intervalo de dados errado.
Esses problemas podem ser facilmente evitados. Se, no entanto, você tiver inserido algum
dado errado na fórmula, o Excel poderá retornar um resultado incorreto, como #VALOR!,
conforme exibido na figura à esquerda. Uma lista completa de tipos de erros e soluções
de erros está disponível nos tópicos da Ajuda.
Qual função usar?
14
Você é um especialista em Excel, mas ainda se confunde com algumas funções
estatísticas?
Você já é um perito em criar fórmulas no Excel. Já sabe como começar do zero com o sinal
de igual fundamental e também como solucionar problemas de algumas mensagens de
erro recebidas. O que mais poderia causar problemas? Ainda é possível obter resultados
incorretos?
Escolher a função certa a ser usada nem sempre é tão fácil quanto parece. Talvez os seus
conhecimentos de estatística estejam um pouco enferrujados ou talvez você não tenha
percebido que havia tantas funções relacionadas disponíveis.
Você ainda obtém a resposta errada?
Há quatro funções de variância relacionadas disponíveis no Excel. Use a caixa de diálogo
Inserir Função para encontrar mais informações sobre cada uma delas.
Se você não souber qual função usar, o Excel poderá contribuir com vastos tópicos de
ajuda. Você precisa ter noções de estatística suficientes para poder analisar seus dados.
Mesmo quando você acha que sabe qual função estatística usar, nem tudo é tão simples
assim.
Há muitas funções no Excel com nomes semelhantes e que são relacionados entre si.
Talvez você tenha escolhido uma função com atribuições diferentes da desejava. Para
saber se você fez a escolha certa:
Verifique a finalidade de cada função individual na caixa de diálogo Inserir Função e
use o link para os tópicos daAjuda.
15
Qual função?
Uma amostra da população (à esquerda) comparada a uma população inteira (à direita).
Outro exemplo de função estatística que tem mais de uma opção a escolher no Excel é o
desvio padrão. (Você usará essa opção na sessão prática.)
As funções disponíveis no Excel são:
DESVPAD.
DESVPADA.
DESVPADP.
DESVPADPA.
A diferença entre essas funções deve-se parcialmente às estatísticas e à forma como o
Excel as calcula.
DESVPADP e DESVPADPA usam populações inteiras, enquanto DESVPADP e DESVPADPA
funcionam em amostras de populações. Essa é uma diferença estatística.
DESVPADA e DESVPADPA reconhecerão texto VERDADEIRO e FALSO (ou valores lógicos),
bem como numerais. DESVPAD and DESVPADP reconhecerão apenas valores numéricos.
A função escolhida dependerá dos seus dados. Você só tem valores numéricos? Você tem
dados para uma população inteira ou apenas para uma amostra?
16
Melhores resultados no Excel 2013
A função de variância é mais precisa para números muito altos no Excel 2013.
Algumas funções fornecerão resultados diferentes no Excel 2013, em comparação com
versões anteriores do Excel, pois várias funções foram aprimoradas. Um procedimento de
duas etapas agora é usado, aumentando a precisão dos resultados.
Não entre em pânico. As diferenças nos resultados devem ser observadas principalmente
em casos extremos e raros.
“Estatística” em “Fórmulas/Mais Funções”
Vá a guia Fórmulas, grupo Biblioteca de Funções e, em seguida, clique em Mais
Funções, Estatística desta forma você terá acesso a lista das funções desta categoria.
Utilizando Funções Estatísticas
Propósito:
Utilizar as Funções Estatísticas do MS Excel 2013.
17
Meta:
Abra a pasta de trabalho “Prêmio.xlsx”.
Calcular a maior e a menor venda entre os vendedores.
Calcular a média de vendas dos vendedores.
Calcular o número de vendedores que não efetuaram vendas.
Calcular o número de vendedores que efetuaram vendas.
Calcular o número de vendedores premiados com o primeiro lugar em vendas.
Calcular o total de vendedores por departamento.
Calcular o número de vendedores premiados com o 1º lugar em vendas por
departamento.
Calcular a média de vendas por departamento.
Calcular a média de vendas pelos vendedores não premiadoscom o primeiro lugar
por departamento.
Uma loja possui três departamentos de vendas: Informática, Eletrodomésticos e Livraria.
Cada departamento tem a sua equipe de vendas. Com a finalidade de motivar os
vendedores, a loja resolveu distribuir um prêmio no valor de R$ 450.000,00 para o(s)
vendedor(es) que ficou(aram) em 1º lugar no volume de vendas.
O objetivo é efetuar os seguintes controles:
Exibir o ranking de vendas, ou seja, calcular a colocação de cada um levando em
consideração a venda individual em relação ao conjunto de vendas dos vendedores;
Exibir a maior e a menor venda efetuada considerando todos os departamentos;
Calcular a média de vendas considerando todos os departamentos;
Exibir o número de vendedores da loja, o número de vendedores que não efetuaram
vendas, o número de vendedores que efetuaram vendas e quantos ficaram em 1º
lugar em vendas;
Por departamento, calcular o número de vendedores, quantos ficaram em 1º lugar em
vendas, calcular a média de vendas e a média de vendas dos vendedores não
ganhadores da gratificação.
Para resolver essa situação você vai trabalhar com a pasta de trabalho Gratificação.xlsx.
Nessa pasta você encontrará duas planilhas: VENDAS e RELATÓRIO.
18
Função MÁXIMO - Essa função retorna o valor máximo entre um conjunto de valores
numéricos informado.
Sintaxe: MÁXIMO(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos
cujo valor máximo queremos determinar. Os argumentos podem ser números,
nomes, matrizes ou referências que contenham números. Pode-se informar de 1 a
255 valores numéricos.
19
1. Abra a pasta de trabalho Prêmio.xlsx.
2. Selecione a planilha RELATÓRIO e clique na célula B2. Nessa célula você deve calcular
o valor máximo das vendas que estão na faixa C3:C22 da planilha VENDAS.
3. Digite =MÁXIMO(.
4. Selecione a planilha VENDAS e, com o mouse selecione a faixa C3:C22.
5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2:
=MÁXIMO(VENDAS!C3:C22).
Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a fórmula está sendo
colocada na planilha RELATÓRIO, é colocado o nome da planilha seguido pelo ponto de
exclamação na identificação da região informada.
Outra forma de selecionar a função MÁXIMO é através da ferramenta que se encontra
na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de
igual (=) antes de selecionar a função.
Função MÍNIMO – Essa função retorna o valor mínimo entre um conjunto de valores
numéricos informado.
Sintaxe: MÍNIMO(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos
cujo valor mínimo queremos determinar. Os argumentos podem ser números,
nomes, matrizes ou referências que contenham números. Pode-se informar de 1 a
255 valores numéricos.
1. Selecione a planilha RELATÓRIO e clique na célula B3. Nessa célula você vai calcular o
valor mínimo das vendas que estão na faixa C3:C22 da planilha VENDAS.
2. Digite =MÍNIMO(.
3. Selecione a planilha VENDAS e, com o mouse selecione a faixa C3:C22.
4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2:
=MÍNIMO(C3:C22).
Outra forma de selecionar a função MÍNIMO é através da ferramenta que se encontra
na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de
igual (=) antes de selecionar a função.
20
Função MÉDIA – Essa função retorna a média aritmética entre um conjunto de valores
numéricos informado.
Sintaxe: MÉDIA(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos
para os quais desejamos calcular a média aritmética. Os argumentos podem ser
números, nomes, matrizes ou referências que contenham números. Pode-se
informar de 1 a 255 valores numéricos.
1. Selecione a planilha RELATÓRIO e clique na célula B4. Nessa célula você vai calcular a
média aritmética das vendas que estão na faixa de C3:C22 da planilha VENDAS.
2. Digite =MÉDIA(.
3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22.
4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2:
=MÉDIA(VENDAS!C3:C22).
Outra forma de selecionar a função MÉDIA é através da ferramenta que se encontra na
guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de igual
(=) antes de selecionar a função.
Função ORDEM.EQ – Essa função retorna a posição de um número em uma lista de
números.
A presença de números com a mesma posição irá interferir na ordem dos números
subsequentes. Por exemplo, em uma lista de números inteiros classificados em ordem
crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma
ordem de 4 e nenhum número teria a ordem de 3.
Sintaxe: ORDEM.EQ(número; referência; [ordem])
Argumentos:
o Número: número cuja posição se quer encontar em uma lista de números;
o Referência: lista de números onde o número informado será posicionado;
o Ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionará o
número considerando a lista de números ordenada de forma descendente;
se for qualquer valor diferente de zero, o Excel posicionará o número
considerando a lista de números ordenada de forma ascendente.
21
1. Selecione a planilha VENDASe clique na célula D3.
2. Você vai urilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3
dentro da faixa de vendas C3:C22.
3. Digite a fórmula =ORDEM.EQ(C3;$C$3:$C$22).
4. Está fórmula deve sofrer uma mudança para
=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"") para evitar ocorrência de erro durante o
retorno da posição do número na lista de números.
Observe que a faixa de células C3:C22 foi fixada na fórmula, pois na cópia para as células
de baixo essa faixa deve continuar sendo a mesma.
5. Pressione a tecla ENTER e copie a fórmula até a célula D22.
Não se preocupe com as informações de erro que aparecem nas células D8 e D17. Esse erro
é devido a não existir um valor de venda nas células C8 e C17. Mais adiante, nesse mesmo
capítulo, esse erro será tratado de forma que não apareça a mensagem.
Função CONT.VALORES – Essa função conta o número de células não vazias em um
intervalo informado. O conjunto de células pode conter qualquer tipo de informação.
Sintaxe: CONT.VALORES(intervalo1;[intervalo2]; ...)
Argumentos:
o Intervalo1;[intervalo2];...: faixas de células que devem ser contadas;
6. Você vai contar o número total de vendedores da loja. Para isso conte quantas células
contém os nomes dos vendedores.
7. Selecione a planilha RELATÓRIO e clique na célula B7.
8. Digite =CONT.VALORES(.
9. Como os nomes dos vendedores estão na planilha VENDAS, selecione a planilha e,
com o mouse clicado, selecione a faixa de células A3:A22.
10. Pressione a tecla ENTER e você terá a fórmula: =CONT.VALORES(VENDAS!A3:A22).
Função CONTAR.VAZIO – Essa função conta o número de células vazias em um intervalo.
Células que contenham valor nulo não são contadas.
Sintaxe: CONTAR.VAZIO(intervalo)
Argumentos: o argumento intervalo representa a faixa de células que devem ser
contadas.
22
1. Agora você vai contar quantos vendedores não efetuaram vendas, ou seja, quantas
são as células da coluna VALOR DE VENDA que estão vazias.
2. Selecione a planilha RELATÓRIO e clique na célula B8.
3. Digite =CONTAR.VAZIO(.
4. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o
mouse clicado, selecione a faixa de células C3:C22.
5. Pressione a tecla ENTER e você terá a fórmula: =CONTAR.VAZIO(VENDAS!C3:C22).
Função CONT.NÚM – Essa função conta o número de células que contêm números em
um intervalo informado. Serão contadas as células que contenham números, datas ou
escritos entre aspas.
Sintaxe: CONT.NÚM(intervalo1;[intervalo2]; ...)
Argumentos:
o Intervalo1;[intervalo2];...:faixas de células que devem ser contadas;
1. Agora você vai contar quantos vendedores efetuaram vendas, ou seja, quantas são as
células da coluna VALOR DE VENDA que estão preenchidas com números.
2. Selecione a planilha RELATÓRIO e clique na célula B9.
3. Digite =CONT.NÚM(.
4. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o
mouse clicado, selecione a faixa de células C3:C22.
5. Pressione a tecla ENTER e você terá a fórmula: =CONT.NÚM(VENDAS!C3:C2)
Função CONT.SE – Essa função conta o número de ocorrências de uma determinada
condição em um intervalo de células informado.
Sintaxe: CONT.SE(intervalo; critério)
Argumentos:
o Intervalo1: intervalo de células que será considerado;
o Critério: condição que será procurada no intervalo de células;
1. Você vai contar quantos são os vendedores ganhadores da gratificação, ou seja,
aqueles que se encontram em 1º lugar na colocação de vendas.
2. Selecione a planilha RELATÓRIO e clique na célula B10.
3. Digite a fórmula: =CONT.SE(.
23
4. Como você quer verificar se o vendedor está na classificação de 1º, o intervalo de
células considerado deve ser o da coluna CLASSIFICAÇÃO da planilha VENDAS.
5. Selecione a planilha VENDAS e, com o mouse clicado, selecione o intervalo D3:D22.
6. Digite ; (ponto-e-vírgula).
7. Digite 1.
8. Pressione a tecla ENTER e você terá a seguinte fórmula:
=CONT.SE(VENDAS!D3:D22;1).
9. Agora você vai contar quantos vendedores são do departamento de Informática no
intervalo de células B3:B22 da planilha VENDAS. Clique na célula E2 da planilha
RELATÓRIO.
10. Digite a fórmula: =CONT.SE(VENDAS!B3:B22;”Informática”).
Como a palavra Informática, que é o critério para a função, está na célula D1 da planilha
RELATÓRIO, a fórmula também poderia ser escrita da seguinte forma:
=CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1).
Função CONT.SES – Essa função aplica critérios em vários intervalos e conta o número de
vezes que todos os critérios são verdadeiros.
Sintaxe: CONT.SES(intervalo1;critério1;intervalo2;critério;...)
Argumentos:
o Intervalo1: intervalo de células onde será procurado o critério 1;
o Critério1: condição que será procurada no intervalo de células intervalo1;
o Intervalo2: intervalo de células onde será procurado o critério2;
o Critério2: condição que será procurada no intervalo de células intervalo2.
Observação:
A função CONT.SES pode ter até 127 pares de intervalos/critérios.
1. Você vai calcular quantos são osvendedores ganhadores da gratificação do
departamento de Informática. Veja que agora você precisa considerar duas condições:
o vendedor está classificado como 1º e trabalha no departamento de Informática.
2. Selecione a planilha RELATÓRIO eclique na célula E3.
3. Digite a fórmula: =CONT.SES(.
4. Você deve procurar pela palavra “Informática”. Selecione a planilha VENDAS e
selecione o intervalo B3:B2. Digite ; (ponto-e-vírgula) e, na planilha RELATÓRIO,
clique na célula D1. Digite ; (ponto-e-vírgula).
5. Agora você vai procurar pelo número 1 no intervalo D3:D22 (estamos procurando
pelos primeiros colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite
; (ponto-e-vírgula) e digite 1.
24
6. Pressione a tecla ENTER e teremos a fórmula:
=CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1).
Função MÉDIASE – Tem como objetivo calcular a média aritmética dos valores indicados
de acordo com um determinado critério ou condição.
Sintaxe: MÉDIASE(intervalo; critérios; intervalo_média)
Argumentos:
o Intervalo: intervalo de células onde o critério será procurado;
o Critério: condição para definir quais valores serão considerados para o
cálculo da média;
o Intervalo_média: intervalo de células que será considerado para calcular a
média.
1. Calcule agora a média de vendas para o departamento de Informática. Selecione a
célula E4 da planilha RELATÓRIO e digite a fórmula a seguir. Utilize o método de
selecionar com o mouse, como foi feito para as fórmulas anteriores, para construir a
expressão: =MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22).
Função MÉDIASES – Tem como objetivo calcular a média aritmética em um intervalo
atendendo a vários critérios.
Sintaxe:
MÉDIASES(intervalo_média_critério1;critério1;intervalo_critério2;critério2;...)
Argumentos:
o Intervalo_média: intervalo de valores que serão considerados para o cálculo
da média.
o Intervalo_critério1: intervalo de células onde o critério1 será procurado;
o Critério1: condição para definir quais valores serão considerados para o
cálculo da média;
o Intervalo_critério2: intervalo de células onde o critério2 será procurado;
o Critério2: condição para definir quais valores serão considerados para o
cálculo da média.
25
1. Calcular a média aritmética das vendas dos vendedores do departamento de
Informática e que não são ganhadores da Gratificação. Na célula E5 da planilha
RELATÓRIO digite a fórmula:
=MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;”
<>1”).
Os critérios informados nessa fórmula são os seguintes:
VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da
média;
VENDAS!B3:B22 – intervalo onde será procutado o primeiro critério
(Departamento);
RELATÓRIO!D1 – endereço da célula que contém o primeiro critério
(Informática);
VENDAS!D3:D22 – intervalo onde será procurado o segundo critério
(Classificação);
2. Preencha, utilizando o mesmo raciocínio utilizado para as informações do
departamento de Informática, os quadros relativos aos departamentos de
Eletrodomésticos e Livraria da planilha RELATÓRIOS.
3. No final você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO.
26
Funções de TEXTO
As funções de texto do Excel são ferramentas bastante úteis no tratamento das
informações. Em muitos casos essas informações são provenientes de outros bancos de
dados e nem sempre são exibidas da forma mais adequada. Através da utilização das
funções de texto do Excel esses problemas são resolvidos obtendo a visualização correta
dos dados.
27
Clique na guia Fórmulas, no grupo Biblioteca de Funções, comando Texto.
Utilizando-se das funções de TEXTO – ARRUMAR, DIREITA, ESQUERDA, EXT.TEXTO,
MAIÚSCULA, MINÚSCULA e PRI.MAIÚSUCULA.
Função ARRUMAR
Situação comum, principalmente para quem trabalha com importação de dados que vêm
de outros sistemas é ter que fazer uma limpeza destes. Dentre as limpezas que são
necessárias, algumas já comentei neste blog, outras vou comentar e uma bem frequente é
a retirada de espaços em branco em textos de células.
Para isso, o Excel disponibiliza a função ARRUMAR. Segundo a própria Microsoft: Remove
todos os espaços do texto exceto os espaços únicos entre palavras. Use ARRUMAR no
texto que recebeu de outro aplicativo que pode ter espaçamento irregular.
A função ARRUMAR foi desenvolvida para aparar o caractere de espaço ASCII de 7 bits
(valor 32) do texto. No conjunto de caracteres Unicode, há um caractere de espaço
adicional denominado caractere de espaço incondicional, o qual possui um valor decimal
de 160. Esse caractere é comumente utilizado em páginas da Web como entidade HTML,
. Por si própria, a função ARRUMAR não remove o caractere de espaço
incondicional.
A sintaxe da função ARRUMAR tem os seguintes argumentos (argumento: um valor que
fornece informações a uma ação, um evento, um método, uma função ou um
procedimento).
Sintaxe
ARRUMAR(texto)
A sintaxe da função ARRUMAR tem os seguintes argumentos:
Texto – Obrigatório. O texto do qual se deseja remover espaços.
Exemplo:
28
É muito comum ao receber arquivos de outrem e no momento de fazer uma comparação
com algum valor nele existente, algo estranho acontece. Por exemplo, a imagem abaixo:
Comparação inconsistente
Reparem na fórmula que é colocada. Porque o resultadonão é o esperado? O fato é que
há espaços em branco depois do nome “Brasil” em A1, o que faz a comparação ser
inválida. É o tipo de erro que é bastante complicado de identificar. A correção abaixo, que
acrescente a função ARRUMAR resolve o problema:
Comparação usando a função ARRUMAR
Por isso, quando se trabalha com campos de texto e tratamento sobre estes, recomenda-
se limpar os dados com a função ARRUMAR para ter certeza que não haverão grandes
problemas mais adiante.
Utilizando a função ARRUMAR
Propósito:
Utilizar a função ARRUMAR.
Meta:
Abrir a pasta de trabalho Rodízio.xlsx.
Eliminar os espaços em branco no início das células da coluna MARCA da planilha
Dados.
29
Desde 1997 está em vigor, na cidade de São Paulo – SP, o rodízio municipal de veículos,
com o propósito de melhorar as condições ambientais e também de reduzir os
congestionamentos nas principais vias da cidade nos horários de maior movimento. Os
automóveis e caminhões não podem circular em uma determinada região, denominada
Centro Expandido, nos períodos de 07:00 – 10:00 horas e 17:00 – 20:00 horas todos os
dias, de segunda a sexta-feira, seguindo uma tabela de acordo com o final da placa do
veículo. Assim:
Veículos com finais de placa 1 e 2 Não circulam às segundas-feiras
Veículos com finais de placa 3 e 4 Não circulam às terças-feiras
Veículos com finais de placa 5 e 6 Não circulam às quartas-feiras
Veículos com finais de placa 7 e 8 Não circulam às quintas-feiras
Veículos com finais de placa 9 e 0 Não circulam às sextas-feiras
Recebmeos uma pasta contendo os dados de vários veículos e devemos, então, verificar
na data corrente quais veículos podem circular livremente e quais devem obedecer ao
rodízio. Além disso, os dados chegaram de forma não muito adequada, com espaços em
branco indevidos no início dos campos, letras minúsculas ao invés de maiúsculas e outros
acertos que devem ser feitos.
Para essa tarefa vamos utilizar as funções de texto do Excel, além de funções já
estudadas, como SE e PROCV.
Função ARRUMAR – Essa função tem como objetivo eliminar os espaços em branco à
esquerda e a à direita do texto.
Sintaxe: ARRUMAR(texto).
Argumentos: texto: texto do qual se deseja remover espaços à direita ou à
esquerda.
1. Abra a pasta de trabalho Rodízio.xlsx.
2. Selecione a planilha DADOS.
30
3. Você vai arrumar a coluna referente a MARCA para eliminar os espaços em branco no
início de cada célula. Esse ajuste será efetuado na planilha INTERMEDIÁRIA. Selecione
a planilha INTERMEDIÁRIA e clique na célula A2.
4. O texto onde se deseja eliminar os espaços indevidos está na célula A2 da planilha
DADOS. Portanto, digite a seguinte a seguinte fórmula: =ARRUMAR(DADOS!A2).
5. Copia a fórmula até a célula A18.
31
Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA
Propósito:
Utilizar as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA.
Meta:
Converter para letras maiúsculas os conteúdos das células referentes à coluna PLACA
da planilha DADOS.
Converter para letras minúsculas os conteúdos das células referentes à coluna
COMENTÁRIOS da planilha DADOS.
Função MAIÚSCULA – Essa função tem como objetivo converter o texto para caracteres
maiúsculos.
Sintaxe: MAIÚSCULA(texto).
Argumentos: texto: texto que se deseja converter para maiúsculas.
Função MINÚSCULA – Essa função tem como objetivo converter o texto para caracteres
minúsculos.
Sintaxe: MINÚSCULA(texto).
Argumentos: texto: texto que se deseja converter para minúsculas.
Função PRI.MINÚSCULA – Essa função tem como objetivo colocar em maiúsculas todas
as primeiras letras das palavras de uma cadeia de texto.
32
Sintaxe: PRI.MINÚSCULA(texto).
Argumentos: texto: texto que se deseja converter parcialmente para maiúsculas.
1. A coluna PLACA da planilha DADOS está mostrando as placas dos automóveis com a
identificação das letras em caracters minúsculos, Você vai modificar para que
apareçam em letras maiúsculas. Selecione a planilha INTERMEDIÁRIA, clique na célula
B2 e digite a fórmula: =MAIÚSCULA(DADOS!B2).
2. Copie a fórmula para todos os automóveis.
3. Agora você vai converter os valores da coluna COMENTÁRIOS da planilha DADOS
para minúsculas. Ainda na planilha INTERMEDIÁRIA clique na célula C2 e digite a
fórmula: =MINÚSCULA(DADOS!C2).
4. Copie a fórmula para todos os automóveis.
5. Os nomes dos proprietários dos automóveis estão digitados em letras minúsculas.
Vamos corrigir esse erro, colocando todas as iniciais em maiúsculas. Ainda na planilha
INTERMEDIÁRIA clique na célula D2 e digite a fórmula:
=PRI.MAIÚSCULA(DADOS!D2).
6. Copie a fórmula para todos os proprietários.
33
Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO
Propósito:
Utilizar as funções ESQUERDA, DIREITA e EXT.TEXTO.
Meta:
Retornar as letras das placas dos automóveis.
Retornar as duas últimas letras das placas dos automóveis.
Retornar o último número das placas dos automóveis.
Função ESQUERDA – Essa função tem como objetivo retornar os primeiros n caracteres à
esquerda de uma cadeia de texto.
Sintaxe: ESQUERDA(texto;[número_caracteres]).
Argumentos:
o texto: texto de onde se deseja extrair os caracteres à esquerda.
o número_caracteres: argumento opcional que indica o número de caracteres
à esquerda que se deseja extrair do texto. Se omitido, trará o primeiro
caractere à esquerda.
34
Função DIREITA – Essa função tem como objetivo retornar os últimos n caracteres à
direita de uma cadeia de texto.
Sintaxe: DIREITA(texto;[número_caracteres]).
Argumentos:
o texto: texto de onde se deseja extrair os caracteres à direita.
o número_caracteres: argumento opcional que indica o número de caracteres
à direita que se deseja extrair do texto. Se omitido, trará o último caractere
à direita.
Função EXT.TEXTO – Essa função tem como objetivo retornar os n caracteres à partir de
uma determinada posição em uma cadeia de texto.
Sintaxe: EXT.TEXTO(texto;posição;número_caracteres).
Argumentos:
o texto: texto de onde se deseja extrair os caracteres.
o posição: posição do primeiro caractere a ser extraído do texto.
o número_caracteres: número de caracteres que devem ser extraídos a partir
da posição do primeiro caractere considerado.
1. Você vai extrair as letras das placas do automóveis. Para isso, clique na célula E2 da
planilha INTERMEDIÁRIA, e digite: =ESQUERDA(B2;3).
2. Copie a fórmula para todos os automóveis.
3. Agora você vai extrair as duas últimas letras das placas dos automóveis, considerando
que se deseja trazer, a partir da posição 2, os dois caracteres do conteúdo das células
que estão na coluna PLACA da planilha INTERMEDIÁRIA. Clique na célula F2 e digite:
=EXT.TEXTO(E2;2;2).
4. Copie a fórmula para todos os automóveis.
5. Extraia o último das placas dos automóveis. Para isso, clique na célula G2 da planilha
INTERMEDIÁRIA, e digite: =DIREITA(B2;1).
6. Copie a fórmula para todos os automóveis.
7. Para completar a tarefa você deve preencher a planilha RODÍZIO. Nessa planilha você
vai ter a indicação dos automóveis que estão ou não na data informada. Copie
somente os valores das colunas PLACA e ÚLTIMO NÚMERO DA PLACA da planilha
INTERMEDIÁRIA para as colunas PLACA e FINAL DA PLACA da planilha RODÍZIO.
Copie somente os valores, e não fórmulas.
8. Na planilha RODÍZIO clique na célula B2 e digite a fórmula para mostrar o dia de hoje:
=HOJE().
35
9. Você vai colocar o nome do dia da semana correspondente à data de hoje na célula
D2. Para isso você precisa procurar pelo número do dia da semana na tabela Dia da
Semana através das funções DIA.DA.SEMANA e PROCV. Clique na célula D2 e digite:
=PROCV(DIA.DA.SEMANA(B2;2);L3:M9;2).
10. Preencha a coluna DIA DO RODÍZIO com o dia dasemana encontrado na tabela
Rodízio a partir do final da placa do automóvel. Clique na célula C5 e digite a fórmula:
=PROCV(B5;$I$3:$J$8;2).
11. Copie a fórmula para todos os automóveis.
12. Agora você vai preencher a coluna MENSAGEM informando se o veículo está ou não
no rodízio na data considerada. Clique na célula D5 e digite a fórmula:
=SE(C5=$D$2;”Carro no rodízio”;”Livre para circular”).
13. Formate condicionalmente essa coluna para que mostre a mensagem “Carro no
rodízio” em vermelho.
Funções de DATA e HORA
As funções de data e hora do Microsoft Excel permitem que você execute cálculos de
planilha rápida e precisamente. Por exemplo, se você usar sua planilha para calcular a
folha de pagamento mensal de sua empresa, poderia usar a função HORA() para
determinar o número de horas trabalhadas diariamente e a função DIA.DA.SEMANA() para
determinar se os empregados devem receber de acordo com o salário padrão (para o
período de segunda a sexta-feira) ou a uma taxa de horas extras (para sábados e
domingos).
Clique na guia Fórmulas, no grupo Biblioteca de Funções, comando Data e Hora.
36
Utilizando as funções de DATAS e HORAS
Propósito:
Formatar datas.
Utilizar as funções de datas e horas.
Efetuar cálculos com datas e horas.
Meta:
Formatar uma data para que seja exibido o dia da semana.
Formatar uma data para que seja exibido o mês.
Formatar uma data para que seja exibido o dia, mês e ano.
Utilizar as funções de data para exibir a data de hoje, o ano, dia e mês de uma data, o
número do dia dia da semana.
Projetar dias úteis a partir de uma data.
Calcular o número de dias úteis entre duas datas.
Projetar dias corridos a partir de uma data.
Calcular o número de dias corridos entre duas datas.
Calcular o número de horas e minutos entre dois horários.
Aplicar formatação condicional nas planilhas.
Em muitas planilhas é necessário o trabalho com datas e horas, efetuando cálculos e
projeções. O MS Excel 2013 oferece várias funções e opções de formatação que podem
ser aplicadas para as mais variadas necessidades.
Formatação de DATAS
1. Abra a pasta de trabalho Datas Formato.xlsx.
37
2. Copie o conteúdo da coluna A para as demais colunas.
3. Na coluna B você vai exibir o número serial que representa a data. Esse número é o
número de dias transcorridos entre a data 01/01/1900 e a data considerada. Selecione
o intervalo B2:B32.
4. Na guia Página Inicial, grupo Número, clique na seta do comando Formato de
número.
38
5. Selecione Geral.
6. Na coluna C você vai mostrar o nome do dia da semana da data armazenada.
Selecione o intervalo C2:C32.
7. Na guia Página Inicial, clique no grupo Número.
8. No grupo Número, selecione a categoria Personalizado.
9. Apague o que está no campo Tipo e digite dddd.
39
O que é informado nesse campo determina o formato do dado selecionado. Para o caso
de datas, considerando o dia da data, temos os seguintes formatos personalizados:
D Dias que são formados por um algarismo são exibidos com um algarismo.
DD Dias que são formados por um algarismo são exibidos com dois algarismos,
tendo o zero como inicial.
DDD Exibe o nome do dia de forma abreviada, através das três primeiras letras.
DDDD Exibe o nome do dia por extenso.
10. Clique em OK.
11. Agora você vai exibir a coluna D o nome do mês por extenso. Selecione o intervalo
D2:D32.
12. Clique na seta do grupo Número da guia Página Inicial.
13. Na guia Número, selecione a categoria Personalizado.
14. Apague o que está no campo Tipo e digite mmmm.
40
Para o caso de datas, considerando o mês da data, temos os seguintes formatos
personalizados:
M Meses que são formados por um algarismo são exibidos com um algarismo.
MM Meses que são formados por um algarismo são exibidos com dois algarismos,
tendo o zero como inicial.
MMM Exibe o nome do mês de forma abreviada, através das três primeiras letras.
MMMM Exibe o nome do mês por extenso.
15. Clique em OK.
Agora você vai mostrar a data por extenso. Por exemplo: para a data 01/01/2010, deverá
ser exibido sexta-feira, 01 de janeiro de 2010.
16. No campo Tipo digite o seguinte: dddd”, “dd” de “mmmm” de “aaa.
No caso do ano, a representação aa formata a data para que apareçam os dois últimos
dígitos do ano e a representação aaa formata a data para que apareçam os quatro dígitos
do ano.
17. Clique em OK.
41
Você deve ter em mente a diferença entre conteúdo e formato. Conteúdo é o dado
armazenado na célula, e Formato é a forma como ele está sendo exibido. Quando você
efetua cálculos com células formatadas, é o conteúdo que será trabalhado, e não o formato.
Funções de DATA
Função HOJE – A função HOJE retorna a data de hoje formatada como data. Essa função
não possui argumentos. No entanto deve-se digitar os parênteses.
Sintaxe: HOJE().
Função ANO – Essa função retorna o ano da data informada. O ano é retornado como
um inteiro no intervalo de 1900 a 9999.
Sintaxe: ANO(data_informada).
Argumentos: data_informada: data que se deseja retornar o ano.
42
Função MÊS – Essa função retorna o número do mês da data informada. O mês é
retornado como um inteiro no intervalo de 1 a 12.
Sintaxe: MÊS(data_informada).
Argumentos: data_informada: data que se deseja retornar o mês.
Função DIA – Essa função retorna o dia da data informada. O dia é retornado como um
inteiro no intervalo de 1 a 31.
Sintaxe: DIA(data_informada).
Argumentos: data_informada: data que se deseja retornar o dia.
Função DIA.DA.SEMANA – Essa função retorna o número do dia da semana da data
informada. O dia é retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por
padrão.
Sintaxe: DIA.DA.SEMANA(data_informada;tipo).
Argumentos:
o data_informada: data que se deseja retornar o número do dia da semana.
o tipo: número que determina o tipo do valor retornado. Este argumento é
opcional e pode ter os seguintes valores:
TIPO VALOR RETORNADO
1 ou omitido Números 1 (domingo) a 7 (sábado)
2 Números 1 (segunda-feira) a 7 (domingo)
3 Números 0 (segunda-feira) a 6 (domingo)
11 Números 1 (segunda-feira) a 7 (domingo)
12 Números 1 (terça-feira) a 7 (segunda-feira)
13 Números 1 (quarta-feira) a 7 (terça-feira)
14 Números 1 (quinta-feira) a 7 (quarta-feira)
15 Números 1 (sexta-feira) a 7 (quinta-feira)
16 Números 1 (sábado) a 7 (sexta-feira)
17 Números 1 (domingo) a 7 (sábado)
1. Abra a pasta de trabalho Datas Funções.xlsx.
43
2. Clique na célula B1.
3. Coloque nessa célula a função para mostrar o dia da data de hoje. Digite: =HOJE().
4. Clique na célula B2.
5. Digite nessa célula a função para mostrar o dia da data de hoje. Digite: =DIA(B1).
6. Clique na célula B3.
7. Digite nessa célula a função para mostrar o número do mês da data de hoje. Digite:
=MÊS(B1).
8. Clique na célula B4.
9. Digite nessa célula a função para mostrar o ano da data de hoje. =ANO(B1).
10. Clique na célula B5.
11. Digite nessa célula a função para mostrar o número do dia da semana da data de hoje,
considerando 1 como domingo e 7 como sábado. Digite: =DIA.DA.SEMANA(B1).
Função DIATRABALHO.INTL – Essa função retorna os dias úteis a projetar a partir de
uma data informada, considerando como é calculado o fim de semana e os feriados que
possam ocorrer no período.
Sintaxe: DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados).
Argumentos:
o data_inicial: data a partir da qual se deseja projetar dias úteis.
o dias: número de dias úteis antes ou depois da data inicial, isto é, quando
ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor
positivo gera uma data futura, um valor negativo gera uma data passada e
um valor zero gera uma data inicial.
44o fim_de_semana: argumento opcional. Indica que dias da semana são
considerados como pertencentes ao final de semana. Pode ter os seguintes
valores:
FIM_DE_SEMANA DIAS DE FIM DE SEMANA
1 ou omitido Sábado, Domingo
2 Domingo, Segunda
3 Segunda-feira, Terça-feira
4 Terça-feira, Quarta-feira
5 Quarta-feira, Quinta-feira
6 Quinta-feira, Sexta-feira
7 Sexta-feira, Sábado
11 Apenas Domingo
12 Apenas Segunda-feira
13 Apenas Terça-feira
14 Apenas Quarta-feira
15 Apenas Quinta-feira
16 Apenas Sexta-feira
17 Apenas Sábado
feriados: argumento opcional que é um conjunto de uma ou mais datas que
serão excluídas do calendário de dias úteis.
1. Abra a pasta de trabalho Caixa.xlsx.
2. Nela você encontra duas planilhas LANÇAMENTOS e FERIADOS. Na planilha
LANÇAMENTOS você fará a projeção dos dias úteis para o mês de Abril/2010.
45
3. Clique na célula B3 e digite a data 01/04/2010.
4. Formate a célula B3 para que apareça somente o nome do mês.
5. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março.
Essa data é o dia anterior da data 01/04/2010 que está armazenada na célula B3, ou
seja, é o conteúdo da célula B3-1. Clique na célula A6 e digite:
=DIATRABALHO.INTL(B3-1;.
6. Como se deseja obter o primeiro dia útil a partir dessa data, digite 1;.
7. Nesta atividade o final de semana é composto por sábado e domingo. Logo, o
parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;.
8. Na planilha FERIADOS, na coluna A, estão os feriados do mês de abril de 2010.
Selecione a planilha FERIADOS e clique sobre a indicação da coluna A. Marque a
coluna inteira para evitar refazer as fórmulas caso seja inserido algum feriado não
previsto. Pressione ENTER e terá a seguinte fórmula na célula:
=DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A).
9. Agora você vai calcular o próximo dia útil a partir da célula A6. Clique na célula A7 e
digite a seguinte fórmula: =DIATRABALHO.INTL(A6;1;1;Feriados!A:A).
10. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de
abril de 2010.
46
11. Feche a pasta de trabalho Caixa,xlsx.
Seu trabalho neste momento é calcular o salário que deve ser pago por uma empresa aos
seus estagiários. Esse controle é efetuado através da pasta de trabalho Pagamento
Estagiários.xlsx.
1. Abra a pasta de trabalho Pagamento Estagiários.xlsx.
2. O primeiro passo é calcular quantos são os dias de vigência do contrato, ou seja,
quantos dias corridos existem entre a data de início e a data de término do contrato.
Para saber quantos dias existem entre duas datas, basta subtrair a data inicial da data
final. Dessa forma, selecione a célula F4 e digite a fórmula: +D4-C4.
3. Copie a fórmula para todos os estagiários.
4. No entanto, o salário é pago considerando o número de dias a trabalhar no período,
ou seja, o número de dias úteis do contrato, e não o número de dias corridos. Clique
na célula G4 e digite a fórmula: =DIATRABALHO.INTL(C4;D4;1;Feriados!A:A).
47
Onde:
C4 Data inicial.
D4 Data final.
1 Final de semana formado por sábado e
domingo.
Feriados!A:A Lista de feriados no ano de 2010.
5. Copie a fórmula para todos os estagiários.
6. Agora calcule quanto cada estagiário deve receber, sabendo que o valor pago por
hora está armazenado na célula C14. Clique na célula H4 e digite a fórmula:
=E4*G4*$C$14.
7. Copie a fórmula para todos os estagiários.
8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi então
solicitado que uma nova coluna fosse inserida na planilha informando quantos dias
corridos deveriam ser acrescidos aos contratos. Sua tarefa agora é inserir essa coluna e
efetuar o cálculo das novas datas de término dos contratos para que o cálculo dos
salários fiquem corretos. Insira duas novas colunas na planilha antes da coluna Carga
horária diária:
48
9. Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor
aparecer uma data, formate os valores como números.
10. Se você tiver uma data e somar um número a essa data, você terá a data
correspondente à data inicial mais o número de dias somado. Portanto, para calcular a
data efetiva de término do contrato, temos que adicionar o número de dias de
prorrogação do contrato à data de término do contrato. Clique na célula F4 e digite a
fórmula: =D4+E4.
11. Copie a fórmula para todos os estagiários.
12. Agora é necessário recalcular os dias de vigência do contrato e os dias a trabalhar no
período, pois a data final do contrato é a nova data calculada, que se encontra na
coluna Data de término efetiva. Refaça as fórmulas e confira o resultado.
13. Feche a pasta de trabalho Pagamento Estagiários.xlsx.
14. Agora você vai efetuar cálculos com horas. Abra a pasta de trabalho Atividades e
horas.xlsx. Nessa pasta você encontra duas planilhas: CONTROLE e FERIADOS. Na
planilha CONTROLE você vai calcular quanto o funcionário deve receber em função do
tempo trabalhado nos dias úteis de fevereio de 2010 e do valor que deve receber por
hora.
49
15. Clique na célula B2 e digite: 01/02/2010.
16. Formate a célula para exibir o nome do mês.
17. A partir da célula A5 preencha até a célula A23 com os dias úteis de fevereiro. Utilize o
mesmo raciocínio do exercício anterior. Os feriados de fevereiro estão na planilha
Feriados.
18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de
copiar os valores, e não as fórmulas.
19. Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana.
20. Calcule agora o tempo trabalhado em cada dia. Esse tempo é calculado subtraindo o
horário de entrada do horário de saída. Para isso, clique na célula E5 e digite: =D5-C5.
21. Copie a fórmula para todo o intervalo.
Função HORA – Essa função retorna a hora de um valor de tempo. A hora é retornada
como um inteiro, variando de 0 (12:00 A.M.) a 23 (11:00 P.M.).
Sintaxe: HORA(valor_tempo).
Argumentos: valor_tempo: horário que contém a hora que desejamos encontrar.
50
Função MINUTO – Essa função retorna os minutos de um valor de tempo. O valor
retornado é um número inteiro que varia de 0 a 59.
Sintaxe: MINUTO(valor_tempo).
Argumentos: valor_tempo: horário que contém os minutos que desejamos
encontrar.
1. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a
seguinte fórmula: =HORA(E5).
2. Copie a fórmula para todos os dias. Se o valor para número de horas estiver
aparecendo como um valor do tipo hora, formate para que seja exibido como número.
3. Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite
a seguinte fórmula: =MINUTO(E5).
4. Copie a fórmula para todos os dias. Se o valor para número de minutos estiver
aparecendo como um valor do tipo hora, formate para que seja exibido como número.
5. Para calcular o valor a pagar você deve multiplicar o número de horas pelo valor por
hora e somar com a multiplicação do número de minutos pelo valor por hora dividido
por 60. Clique na célula I5 e digite a fórmula: =F5*$I$2+G5*$I$2/60.
6. Copie a fórmula para todos os dias.
7. Totalize o valor a pagar na célula I24.
51
Formatação Condicional
A formatação condicional permite que os dados sejam exibidos na planilha de uma forma
mais objetiva. Você pode, por exemplo, modificar a cor da fonte ou da célula para que os
dados que satisfazem critérios estbelecidos sejam exibidos.
1. Na coluna Dia da semana você vai exibir em vermelho todos os dias que forem
segunda-feira. No entanto você deve lembrar que o valor que está armazenado na
célula é uma data, e não o nome do dia da semana. Para obter o número do dia da
semana da data armazenada, voc~e deve utilizar a função DIA.DA.SEMANA vista
anteriormente. Selecione o intervalo B5:B23.2. Na guia Página Inicial, grupo Estilo, clique em Formatação Condocional e, no menu
apresentado, selecione a Nova Regra.
52
3. Na caixa de diálogo Nova Regra de Formatação, selecione Usar uma fórmula para
determinar quais células devem ser formatadas.
4. No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula:
=DIA.DA.SEMANA(B5;1)=2.
Lembre-se que a função DIA.DA.SEMANA traz como retorno o número do dia da
semana, de acordo com o tipo de semana indicado na função. No caso o argumento para
tipo foi passado como 1, ou seja, domingo é o dia 1, segunda-feira é o dia 2 e assim por
diante.
5. Clique no botçao Formatar e escolha a cor vermelha e negrito.
53
6. Clique em OK e, novamente em OK.
7. Feche a pasta de trabalho Atividades e horas.xlsx.
Utilizando as funções LÓGICAS
Propósito:
54
Utilizar as funções lógicas do Excel 2013.
Meta:
Calcular o valor da gratificação para cada um dos vendedores ganhadores.
Atribuir um conceito de acordo com o valor das vendas de cada vendedor.
Distribuir uma gratificação extra entre os vendedores que não alcançaram o primeiro
lugar de acordo com o valor de suas vendas.
Tratar adequadamente os erros que possam ocorrer em planilhas.
Para completar o preenchimento da planilha VENDAS da pasta de trabalho
Gratificação.xlsx, você ainda tem algumas tarefas a realizar:
Distribuir a gratificação que a empresa oferece aos vendedores colocados em primeiro
lugar;
Atribuir um conceito a cada vendedor de acordo com o seu volume de vendas,
comparando-o com a meta a ser atingida individualmente;
Distribuir uma gratificação extra aos vendedores que não se classificaram em primeiro
lugar, mas que também se destacaram de acordo com alguns critérios estabelecidos.
Função SE – Essa função tem como objetivo efetuar testes condicionais com valores e
fórmulas permitindo a escolha do que fazer de acordo com o resultado do teste, que
pode ser Falso ou Verdadeiro.
Sintaxe:
o SE(teste_lógico;valor_se_verdadeiro;valor_se_falso).
Argumentos:
o Teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro
como resultado;
o Valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for
Verdadeiro;
o Valor_se_falso: o que fazer se o resultado do teste_lógico for Falso.
1. Selecione a planilha VENDAS da pasta de trabalho Prêmio.xlsx.
Você deve verificar na célula E3 se o vendedor está em 1º lugar na classificação e, se
estiver, calcular e exibir a sua gratificação. Se o vendedor não estiver em 1º lugar, o valor
que devem ser colocado na célula é zero. Lembre-se que a gratificação total deve ser
dividido igualmente entre os vendedores ganhadores.
55
O raciocínio que vamos seguir é os eguinte:
a) SE classificação = 1.
b) ENTÃO dividir o valor da gratificação pelo número de ganhadores e exibir na
célula.
c) SENÃO exibir o valor 0 na célula.
2. Clique na célula E3 e digite a fórmula: =SE(D3=1;$B$1/RELATÓRIO!$B$10;0).
Obeserve que na célula D3 está a classificação do vendedor, na célula B1 está o valor da
gratificação total e na célula B10 da planilha RELATÓRIO está o número de ganhadores.
3. Copie a fórmula para todos os vendedores. Não se preocupe com os erros apontados
bas células E8 e E17. Mais adiante você vai modificar as fórmulas para tratar
adequadamente esses erros.
4. Agora você vai colocar uma mensagem para cada vendedor de acordo com os
seguintes critérios:
CONCEITO CONDIÇÃO
EXCELENTE Colocação do vendedor igual a 1.
ÓTIMO Valor das vendas do vendedor maior do que a meta.
BOM Valor das vendas do vendedor igual à meta.
PODE MELHORAR Nenhuma das condições anteriores.
Observe que existem quatro condições a serem verificadas. Isso não será possível através
de uma única função SE. Nesse caso, você terá que aninhar funções SE para resolver o
problema. Até 64 funções SE podem ser aninhadas no Excel 2013.
O raciocínio que você deve seguir é o seguinte:
SE colocação do vendedor = 1
ENTÃO atribuir conceito “EXCELENTE”
SENÃO SE valor das vendas maior do que a meta
ENTÃO atribuir conceito “ÓTIMO”
SENÃO SE valor das vendas igual à meta
ENTÃO atribuir conceito “BOM”
SENÃO atribuir conceito “PODE MELHORAR”
56
5. Clique na célula F3 e digite a seguinte fórmula:
=SE(D3=1;”EXCELENTE”;SE(C3>$D$1;”ÓTIMO”;SE(C3=$D$1;”BOM”;”PRODE
MELHORAR”))).
6. Copie a fórmula para todos os vendedores.
7. Feche a pasta de trabalho Prêmio.xlsx.
Função E – Essa função retorna o valor Verdadeiro se todos os seus argumentos forem
verdadeiros.
Sintaxe:
o E(lógico;lógico2;...).
Argumentos:
o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como
resultado;
o Lógico2: condições que serão avaliadas, trazendo Falso ou Verdadeiro
como resultado.
Em uma função E você pode colocar até 255 argumentos, ou seja, pode-se ter até 255
condições lógicas para avaliar.
Função OU – Essa função retorna o valor Verdadeiro se pelo menos um de seus
argumentos for Verdadeiro.
Sintaxe:
o E(lógico;lógico2;...).
Argumentos:
o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como
resultado;
o Lógico2: condições que serão avaliadas, trazendo Falso ou Verdadeiro
como resultado.
Em uma função OU você pode colocar até 255 argumentos, ou seja, pode-se ter até 255
condições lógicas para avaliar.
As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções
que executam testes lógicos, como por exemplo, em conjunto com a função SE.
1. Abra a pasta de trabalho Estado Civil.xlsx. Você vai verificar a diferença dos
resultados entre as funções E e OU.
57
2. Clique na célula D3. Nela deve-se exibir o valor VERDADEIRO se a pessoa for solteira e
sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser exibido o valor
FALSO.
3. Digite a fórmula: =E(B3=”Solteiro(a)”;C3>=22).
4. Copie a fórmula para todas as pessoas.
58
5. Clique na célula E3. Nela deve-se exibir o valor de VERDADEIRO se a pessoa for
solteira ou se sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser
exibido o valor FALSO.
6. Digite a fórmula: =OU(B3=”Solteiro(a)”;C3>=22).
7. Copie a fórmula para todas as pessoas.
Note que no caso da função E, o resultado só é verdadeiro se as duas condições forem
verdadeiras. No caso da função OU, o resultado é verdadeiro se pelo menos uma das
condições for verdadeira, e somente será falso se as duas condições forem falsas.
59
8. Feche a pasta de trabalho Estado Civil.xlsx e abra a pasta de trabalho Prêmio.xlsx.
9. A empresa resolveu premiar também os funcionários que obtiveram ÓTIMO com uma
viagem para Natal(RN) e os funcionários que venderam menos ou o valor dameta, mas
que venderam mais do que R$ 300.000,00, com um jantar.
Considere o seguinte raciocínio:
SE conceito = ÓTIMO
ENTÃO prêmio extra = Viagem para Natal(RN)
SENÃO SE vendas <= meta E vendas > 300000
ENTÃO prêmio extra = Jantar
10. Clique na célula G3 e digite a fórmula:
=SE(F3=”ÓTIMO”;”Viagem para Natal”;SE(E(C3<=$D$1;C3)300000);”Jantar”;””)).
Observe que se nenhuma das condições forem Verdadeiras, o valor “” será colocado na
célula, ou seja, a célula ficará vazia para evitar que seja exibida a mensagem FALSO.
11. Copie a fórmula para todos os vendedores.
Função SEERRO – Essa função tem como objetivo retornar um valor especificado se uma
fórmula gerar um erro. Se não existir erro, a função retorna o resultado da fórmula.
Sintaxe:
o SEERRO(fórmula;valor-se_erro).
Argumentos:
o Fórmula: fórmula que se deseja verificar quanto a erros;
o Lógico2: valor a ser retornado se a fórmula gerar um erro.
Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!,
#NOME? OU #NULO!.
12. As fórmulas da coluna CLASSIFICAÇÃO estãogerando erros nos casos em que a célula
correspondete a venda está vazia. Como é um fato que pode acontecer, é
aconselhável não mostrar a mensagem de erro gerada pelo MS Excel, mas sim
armazenar na célula um valor mais adequado. Nesse caso, se existir o erro na célula,
você deve deixá-la vazia, ou seja, armazenar “”. Clique na célula D3 e modifque a
fórmula para: =SEERRO(ORDEM.EQ(C3;$C$3:$C$22);’’).
60
Observe que todos os erros da planilha foram solucuinados.
É necessário saber qual valor deve-se colocar na célula nos casos de erro para não
ocasionar novos erros.
13. Feche a pasta de trabalho Prêmio.xlsx.
Funções de PESQUISA
As funções de PESQUISA do Excel são poderosas ferramentas que permitem a busca de
dados de forma rápida, muitas vezes evitando o uso de várias funções SE aninhadas.
Utilizando as funções PROCV e PROCH
Propósito:
Utilizar as funções PROCV e PROCH do Excel 2013.
Meta:
Abrir a pasta de trabalho Relatório de Vendas.xlsx.
Através da utilização da função PROCV, preencher a planilha RELATÓRIO com os
nomes dos funcionários, comissões, salário fixo e imposto.
Calcular o valor a receber para cada funcionário.
Através da utilização da função PROCH, preencher a coluna Classificação faixas com as
devidas faixas.
61
Função PROCV – A função PROCV permite procurar por um valor na primeira coluna de
um intervalo de células e trazer outro valor da mesma linha em outra coluna especificada.
Sintaxe:
o PROCV(valor_procurado;intervalo_células;núm_coluna,[procurar_interv
alo]).
Argumentos:
o valor_procurado: valor a ser procurado na primeira coluna do intervalo de
células. Pode ser um valor ou uma referência.
o intervalo_células: intervalo de células que contém os dados. Pode ser uma
referência a um intervalo ou um nome de intervalo. Os valores na primeira
coluna de intervalo_células são os valores procurados por valor_procurado e
podem ser texto, números ou valores lógicos. Não existe distinção entre
maiúsculas e minúsculas.
o núm_coluna: número da coluna do intervalo de células que contém a
informação que deve ser retornada.
o procurar_intervalo: argumento opcional que corresponde a um valor lógico
e que indica de forma o valor que será procurado na primeira coluna do
intervalo, se a correspondência deverá ser exata ou aproximada.
Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou
aproximada. Neste caso, os valores da primeira coluna do intervalo deverão estar
classificados em ordem ascendente, ou a função poderá retornar valores indevidos.
Se o valor do argumento for FALSO, a correspondência se dará de forma exta. Neste caso,
os valores da primeira coluna do intervalo não precisam estar classificados e, se houver
mais de um valor que coincida com o valor_procurado, será considerado o primeiro
encontrado.
1. Abra a pasta de trabalho Relatório de Vendas.xlsx e selecione a planilha
RELATÓRIO.
62
2. Preencha a coluna NOME DO FUNCIONÁRIO com os nomes dos funcionários,
procurando-os na planilha SUPORTE. Clique na célula B2 e digite a fórmula:
=PROCV(A2;.
Você iniciou a digitação da função PROCV informando o primeirio argumento, que é o
que se deseja procurar. No caso, você deve procurar pelo Código do Funcionário na
planilha SUPORTE.
3. Selecione a planilha SUPORTE. Com o mouse pressionado, selecione o intervalo de
A2:B23. Essa região é a que contém o valor a ser procurado (código do funcionário,
que está na primeira coluna do intervalo) e o valor a ser retornado (o nome do
funcionário, que está na segunda coluna do intervalo). A fórmula fica da seguinte
forma: =PROCV(A2;Suporte!$A$2:$B$23;.
Esse argumento indica qual intervalo de células será considerado para a procura e retorno
dos dados. Observe que o intervalo A2:B23 da planilha SUPORTE foi fixado, pois não
pode ser modificado quando você copiar a fórmula para as células de baixo. Outra forma
de evitar que os endereços sejam modificados na cópia é atribuir um nome ao intervalo.
4. Digite 2 e pressione a tecla ENTER. O parâmetro 2 indica que o valor que se deseja
que retorne é o que está na coluna 2 do intervalo de células indicado.
63
=PROCV(A2;Suporte!$A$2:$B$23;2)
5. Copie a fórmula para todos os funcionários.
6. Agora calcule o valor da comissão, salário fixo e impostos para cada funcionário. Esses
valores estão em SUPORTE e dependem do valor da venda efetuada pelo funcionário.
Atribua um nome à região da planilha SUPORTE onde será procurado o valor da
venda e retornados os valores desejados para facilitar a digitação das fórmulas.
Selecione a planilha SUPORTE e selecione o intervalo E2:H7.
7. Na Caixa de Nome (a esquerda da Barra de Fórmulas) digite Valor_Vendas e clique
em OK.
64
8. Você vai procurar pelo valor da venda na região Valor_Vendas e trazer o percentual
de comissão. Esse percentual é obtido pela procura do valor da venda na região
Valor_Vendas e trazendo o conteúdo da célula que está na coluna 2. O valor da
comissão dev ser calculado como sendo o percentual de comissão multiplicado pelo
valor da venda. Selecione a planilha RELATÓRIO. Clique na célula D2 e digite a
seguinte fórmula: =PROCV(C2;Valor_Vendas;2)*C2.
9. Copie a fórmula para todos os vendedores.
10. Agora você vai preencher a coluna Salário Fixo com o valor do salário de cada
vendedor, Mais uma vez o valor da venda deve ser procurado no interbalo
Valor_Vendas. A função PROCV deve retornar o valor do salário fixo que está na
coluna 3 do intervalo. Clique na célula E2 e digite a seguinte fórmula:
PROCV(C2;Valor_Vendas;3).
65
11. O cálculo do imposto, nessa empresa, é feito sobre o valor das comissões. Agora você
vai calcular quanto de imposto cada funcionário deve pagar. Para isso você deve
procurar pelo percentual de imposto de cada um no intervalo Valor_Vendas. A função
PROCV deve retornar o valor do salário fixo que está na coluna 3 do intervalo. Clique
na célula E2 e digite a seguinte fórmula: =PROCV(C2;Valor_Vendas;3).
66
12. Calcule o valor a receber para cada funcionário como sendo: comissão + salário fixo
– imposto.
67
Função PROCH – A função PROCH permite procurar por um valor na primeira linha de
um intervalo de células e trazer outro valor da mesma coluna em outra linha especificada.
Sintaxe:
o PROCVH(valor_procurado;intervalo_células;núm_linha,[procurar_interv
alo]).
Argumentos:
o valor_procurado: valor a ser procurado na primeira linha do intervalo de
células. Pode ser um valor ou uma referência.
o intervalo_células: intervalo de células que contém os dados. Pode ser uma
referência a um intervalo ou um nome de intervalo. Os valores na primeira
linha de intervalo_células são os valores procurados por valor_procurado e
podem ser texto, números ou valores lógicos. Não existe distinção entre
maiúsculas e minúsculas.
o núm_linha: número da linha do intervalo de células que contém a
informação que deve ser retornada.
o procurar_intervalo: argumento opcional que corresponde a um valor lógico
e que indica de forma o valor que será procurado na primeira linha do
intervalo, se a correspondência deverá ser exata ou aproximada.
68
Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exta ou
aproximada. Neste caso, os valores da primeira linha do intervalo deverão estar
classificados em ordem ascendente, ou a função poderá retornar valores indevidos.
Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Neste
caso, os valores da primeira linha do intervalo não precisam estar classificados e, se
houver mais de um valor que coincida com o valor_procurado, será considerado o primeiro
encontrado.
A diferença entre as funções PROCV e PROCH é que a primeira efetua a procurade forma
vertical, e a segunda de forma horizontal. Você pode utilizar qualwuer uma deas duas,
dependendo da disposição do intervalo de células adotada.
1. Você vai preencher a coluna Classifcação faixas de acordo com as vendas de cada
funcionário. Os valores a serem colocados nessa coluna estão na planilha SUPORTE.
Selecione a planilha SUPORTE e selecione o intervalo de células F10:I11. Nomeie esse
intervalo como Faixas.
2. Na Caixa de Nome (a esquerda da Barra de Fórmulas) digite Faixas e clique em OK.
3. Selecione a planilha RELATÓRIO. Clique na célula H2 e digite a seguinte fórmula:
=PROCH(C2;Faixas;2).
4. Copie a fórmula para todos os vendedores.
69
Utilizando as funções CORRESP e ÍNDICE
Propósito:
Deslocar e rotacionar um objeto pelo teclado.
Utilizar as funções CORRESP e ÍNDICE.
Meta:
Calcular os valores de vendas máximo e mínimo.
Identificar a posição desses valores dentro da planilha de vendas.
Identificar os nomes dos vendedores responsáveis pelos valores máximo e mínimo de
vendas.
Função CORRESP – Essa função traz como resultado a posição em que o valor procurado
encontra-se no intervalo de células indicado, que deve ser formado por uma linha de
células ou por uma coluna de células.
Sintaxe:
o CORRESP(valor_procurado;intervalo;[correspondência]).
70
Argumentos:
o valor_procurado: argumento obrigatório, sendo o valor que desejamos
procurar no intervalo de células e obter sua posição relativa dentro dele.
o intervalo: argumento obrigatório, sendo o intervalo de células que estão
sendo pesquisadas. Esse intervalo deve ser uma linha de células ou uma
coluna de células.
o correspondência: argumento opcional que especifica como o valor será
encontrado. Pode ser:
1 ou omitido: localiza o maior valor que é menor ou igual a
valor_procurado. Nesse caso, os dados no intervalo devem estar
classificados em ordem crescente.
0: localiza o primeiro valor que é exatamente igual ao
valor_procurado. Os dados no intervalo podem estar em qualquer
ordem.
-1: localiza o menor valor que é maior ou igual ao valor_procurado.
Nesse caso, os dados no intervalo devem estar classificados em
ordem decrescentes.
1. Agora você vai verificar em que local da planilha se encontram os valores para maior e
menor venda. Selecione a planilha CONTROLE.
2. Calcule a maior e menor venda na coluna VALOR. Clique na célula E5 e digite a
seguinte fórmula: =CORRESP(.
3. Você vai procurar pelo valor da maior venda, que está na célula D5. Continue a
fórmula digitando D5, a mesma recebe =CORRESP(D5;.
4. Agora você vai informar o intervalo de células onde será procurado o valor da venda.
Esse intervalo é C2:C23 da planilha RELATÓRIO. Continue a fórmula:
=CORRESP(D5;Relatório!C2:C23;.
5. Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será
0. A fórmula final é a seguinte: =CORRESP(D5;Relatório!C2:C23;0).
71
6. Repita os procedimentos para encontrar a posição da menor venda na planilha
RELATÓRIO.
Função ÍNDICE – Essa função o conteúdo de uma célula através do fornecimento de sua
posição dentro do intervalo de células.
Sintaxe:
o ÍNDICE(intervalo;linha[coluna]).
Argumentos:
o intervalo: argumento obrigatório, sendo o intervalo de células que contém
o dado a ser retornado.
o intervalo: argumento obrigatório, sendo o número da linha do intervalo
onde está o dado a ser retornado.
o coluna: argumento opcional, sendo o número da coluna do intervalo onde
está o dados a ser retornado.
72
1. Agora você deve retornar os nomes dos vendedores que efetuaram a maior e menor
venda. Clique na célula F5 da planilha CONTROLE e digite a seguinte fórmula:
=ÍNDICE(.
2. Você deve informar o intervalo de células que contém o nome do vendedor que está
sendo procurado. Para tanto digite a seguinte fórmula: =ÍNDICE(Relatório!B2:B23;.
3. Agora você deve informar em que linha desse intervalo encontra-se o dado desejado.
Pela função CORRESP você determinou, anteriormente, o número da linha onde está a
maior venda. Portanto, o parâmetro que você deve informar na função é exatamente o
resultado da função CORRESP, que se encontra na célula E5 da planilha CONTROLE. A
fórmula completa fica então: =ÍNDICE(Relatório!B2:B23;Controle!E5).
4. Utilize os mesmos procedimentos para trazer o nome vendedor que efetuou a menor
venda.
Auditoria de Fórmulas
Muitas vezes é necessário executar auditoria em fórmulas inseridas em suas planilhas.
Muitas vezes as fórmulas podem gerar erros ou mesmo retornarem valores que não são
os esperados. O MS Excel 2013 fornece ferramentas bastante úteis para que você possa
localizar e investigar as causas dos erros e determinar soluções.
Essas ferramentas incluem o rastreamento de precedentes e dependentes de uma célula,
rastreamento de erros, exibição e depuração de fórmulas. Todas essas ferramentas estão
presentes na guia Fórmulas, grupo Auditoria de Fórmulas.
73
Rastreando os precedentes de uma célula
Propósito:
Rastrear as células precedentes de uma célula.
Meta:
Abrir a pasta de trabalho Faculdade.xlsx.
Rastrear as células precedentes das células da planilha BOLETIM.
Rastrear as células precedentes das células da planilha RESUMO.
A ferramenta Rastrear Precedentes permite verificar quais células afetam o valor da
célula selecionada no momento, ou seja, de que célula aquela que se encontra
selecionada depende para o cálculo de seu valor.
1. Abra a pasta de trabalho Faculdade.xlsx.
2. Selecione a planilha BOLETIM.
3. Clique na célula I5.
4. Clique na ferramenta Rastrear Precedentes, do grupo Auditoria de Fórmulas, da
guia Fórmulas.
5. Veja que uma seta vem da célula K2 e outra vem da célula H5, ou seja, o resultado da
fórmula contida na célula I5 depende dos valores das células K2 e H5. Isso é mesmo
verdade, pois a célula I5 contém a fórmula: =SE(H5<=25%*$K$2;”Faltas
Ok”;!”Faltas acima do limite”).
74
Se você clicar duas vezes em cima da seta, a célula precedente fica selecionada.
6. Selecione a planilha RESUMO.
7. Clique na célula B14.
8. Clique na ferramentas de Rastrear Precedentes.
A linha pontilhada indica que a célula ou células precedentes encontram-se em outra
planilha.
75
9. Clique duas vezes sobre a linha pontilhada. A janela exibe a origem das células
precedentes, com a indicação da pasta, planilha e endereço de célula ou intervalo.
Para acessar a origem desejada, basta selecioná-la e clicar no botão Ok.
10. Removas as setas selecionando a ferramenta Remover Setas. Você deve remover as
setas das duas planilhas.
Rastreando os dependentes de uma célula
Propósito:
Rastrear as células dependentes de uma célula.
Meta:
Rastrear as células dependentes das células da planilha BOLETIM.
Rastrear as células dependentes das células da planilha RESUMO.
A ferramenta Rastrear Dependentes permite verificar quais célukas são afetadas pelo
valor da célula selecionada no momento.
1. Selecione a planilha BOLETIM e clique na célula I5.
2. Clique na ferramenta Rastrear Dependentes, do grupo Auditoria de Fórmulas, da
guia Fórmulas.
3. Veja que uma seta vai para a célula K5 e uma linha pontilhada vai para outra planilha.
76
A primeira seta, indicando a célula K5, mostra que a fórmula =SE(E(G5="Média
OK";I5="Faltas OK");"Aprovado";"Reprovado") depende do valor da célula I5.
A seta indicada pela linha pontilhada e apontando para outra planilha, mostra que as
células B8, B9 e B10 da planilha RESUMO dependem do valor da célula I5. Podemos
verificar essas referências cliucando duas vezes sobre a linha pontilhada.
4. Remova as setas da planilha.
Rastreando erros em fórmulas
Propósito:
Rastrear erros em fórmulas.
Meta:
Gerar um erro na fórmula que calcula a média de um aluno.
Rastrear esse erro.
A ferramenta Verificação de Erros permite localizar, rastrear e realizar as etapas de
cálculo de uma célula que contenha um erro.
77
1. Clique na célula F5 da planilha BOLETIM e modifique a sua fórmula para
=MÉDIA(B5:E5).
2. Observe que muitas células agora apresentam erros, ocasionados pelo erro da célula
F5.
3. Clique na célula F5 e observe que o no momento em que a célula foi selecionada, você
tem a indicação do que está ocorrendo.
4. Clique na ferramenta Rastrear Erro, do grupo Auditoria de Fórmulas, da guia
Fórmulas.
78
Existe uma seta vinda da célula G5 que, por sua vez, contém uma seta vinda da célula F5
que, por sua vez, contém uamseta vinda do intervalo de células B5:E5. Ou seja, o erro
apontado na célula K5 é decorrente dos erros apontados nas células G5, F5 e do intervalo
B5:E5. Na verdade, esse erro é causado pela tentativa de efetuar o cálculo da média
aritmética com conteúdos de células vazias.
5. Retorne a fórmula da célula F5 ao seu original: =SEERRO(MÉDIA(B5:E5);0).
Avaliando fórmulas
Propósito:
Depurar fórmulas.
Meta:
Verificar as etapas de cálculo de uma fórmula da planilha BOLETIM.
A ferramenta Avaliar Fórmula permite depurar uma fórmula, analisando individualmente
cada uma de suas partes. É bastante útil quando o resultado retornado não é o esperado.
1. Clique na célula I5 e selecione a ferramenta Avaliar Fórmula na guia Fórmulas, grupo
Auditoria de Fórmulas.
79
2. Na janela exibida você pode efetuar, passo a passo, os cálculos que estão envolvidos
na fórmula.
Para mostrar o resultado da expressão que está sublinhada, clique em Avaliar. Cada vez
que você clica nesse botão, a expressão sublinhada é calculada mostrando o resultado
recente. Dessa forma você pode verificar todas as etapas de cálculo da fórmula.
80
3. Clique em Avaliar. Perceba que, a cada vez que clicamos nesse botão, os cálculos são
efetuados, até que a expressão chegue ao resultado final.
Se quiser reiniciar a avaliação, clique no botão Reiniciar.
4. Clique em Fechar. Selecione a célula K5 e selecione a ferramenta Avaliar Fórmula.
5. Clique no botão Depuração total.
81
Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula K5
para que seja avaliada.
6. Clique novamente em Depuração total.
Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula G5
para que seja avaliada.
Uma vez que se tenha abertas todas as fórmulas envolvidas no cálculo da célula K5, você
pode avaliar cada uma delas individualmente. Basta clicar dentro do quadro
correspondente e efetuar a avaliação até o resultado final.
7. Efetue a avaliação de cada fórmula.
8. Para finalizar, clique em Fechar.
82
Mostrando fórmulas
Propósito:
Mostrar as fórmulas da planilha.
Meta:
Exibir as fórmulas das planilhas BOLETIM e RESUMO.
A ferramenta Mostrar Fórmulas permite a visualização nas células das planilhas ao invés
de seus resultados.
1. Selecione a planilha RESUMO.
2. Clique na ferramenta Mostrar Fórmulas na guia Fórmulas, grupo Auditoria de
Fórmulas.
83
Observe que, ao invés dos resultados, você visualiza as fórmulas.
3. Selecione a planilha BOLETIM.
4. Mostre as fórmulas nas células. Para mostrar novamente nas células, clique na
ferramenta Mostrar Fórmulas.
5. Feche a pasta Faculdade.xlsx.
Função INFORMAÇÃO
Retorna informações sobre o ambiente operacional atual.
Sintaxe: INFORMAÇÃO(tipo_texto)
A sintaxe da função INFORMAÇÃO tem os seguintes argumentos:
Tipo_texto – Obrigatório, o texto que específica o tipo de informação a ser retornado.
84
Na versão anterior do Microsoft Excel, os valores "memavail", "memused" e "totmem" de
type_text retornavam informações sobre a memória. Esses valores de type_text não são
mais aceitos e agora retornam um valor de erro #N/A.
Exemplos:
1. Abra a pasta de trabalho Orçamento Doméstico.xlsx.
2. Selecione a célula C1 da planilha Ex1.
85
3. Posicione-se na célula C1, e inicie a digitação da fórmula =NFORMAÇÃO(.
Observe na imagem que esta função permite a coleta de informações de:
DIRETÓRIO – Diretório ou pasta atual;
NÚMARQUIVO – Planilhas ativas;
ORIGEM – Primeira célula visível superior à esquerda;
OSVERSÃO – Versão do sistema operacional;
RECALC – Modo de recálculo;
SOLTAR – Versão do Microsoft Excel;
SISTEMA – Ambiente Operacional.
Exemplo:
86
Assistente de Soma Condicional
Se você procurou o suplemento Assistente de Soma Condicional e não o encontrou, foi
porque esse suplemento não é fornecido com o Excel 2013.
No Excel 2013, você pode escrever fórmulas condicionais usando funções criadas para
ajudar com essa tarefa, como SOMASE e SOMASES.
Nas versões anteriores do Excel, você podia usar o Assistente de Soma Condicional para
ajudar a escrever fórmulas para calcular somas de valores que atendessem a condições
especificadas. As fórmulas criadas com esse Assistente continuam funcionando no Excel
2013.
Você ainda pode editar as fórmulas criadas usando o Assistente diretamente na barra de
fórmulas ou selecionando a célula da fórmula e clicando em Fórmulas > Inserir Função, o
que abre a caixa de diálogo Argumentos da Função.
87
Financeiras
Fórmulas e Funções Financeiras
O Excel tem um conjunto de funções financeiras muito completo e poderoso. A maioria
delas será apenas usada por utilizadores com necessidades específicas, mas outras podem
ser bastante úteis à maioria dos utiloizadores, quando pretendem fazer cálculos, por
exemplo, referentes a empréstimos.
Uma das funções mais úteis, é a função =PGTO() que é usada para calcular os
pagamentos mensais de um empréstimo.
Função PGTO
Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes
e com uma taxa de juros constante.
Tem a seguinte sintaxe: PGTO(Taxa, Nper, Va, Vf, Tipo)
Observação - Para obter uma descrição mais detalhada dos argumentos em PGTO,
consulte a função VP.
A sintaxe da função PGTO tem os seguintes argumentos:
Taxa Obrigatório. A taxa de juros para o empréstimo.
Nper Obrigatório. O número total de pagamentos pelo empréstimo.
Vp Obrigatório. O valor presente, ou a quantia total agora equivalente a uma
série de pagamentos futuros.
Vf Opcional. O O valor futuro, ou o saldo, que você deseja obter após o último
pagamento. Se vf for omitido, será considerado 0 (zero), ou seja, o valor
futuro de um empréstimo é 0.
Tipo Opcional. O número 0 (zero) ou 1 e indica o vencimento dos pagamentos.
Comentários:
O pagamento retornado por PGTO inclui o principal e os juros e não inclui taxas,
pagamentos de reserva ou tarifas, às vezes associados a empréstimos.
88
Certifique-se de que esteja sendo consistente quanto às unidades usadas para
especificar taxa e nper. Se fizer pagamentos mensais por um empréstimo de quatro
anos com juros de 12% ao ano, utilize 12%/12 para taxa e 4*12 para nper. Se fizer
pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.
Dica
Para encontrar o total pago no período da anuidade, multiplique o valor PGTO retornado
por nper.
Exemplo 1
Exemplo 2
Função TAXA
Retorna a taxa de juros por período de uma anuidade. TAXA é calculado 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!.
Sintaxe: TAXA(nper,pgto,vp,[vf],[topo],[estimativa])
Observação – Consulte VP para obter uma descrição completa dos argumentos nper,
pgto, vp, vf e tipo.
A sintaxe da função TAXA tem os seguintes argumentos:
89
Nper Obrigatório. O pagamento total de períodos de pagamento emuma
anuidade.
Pgto Obrigatório. 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 atributo. Se pgto for omitido, você deverá incluir o
argumento vf.
Vf Opcional. 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).
Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento.
Estimativa – Opcional. A sua estimativa para a taxa.
o Se você omitir estimativa, este argumento será considerado 10%.
o Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral,
TAXA converge se estimativa estiver entre 0 e 1.
Comentários
Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar
estimativa e nper. Se você fizer pagamentos mensais por um empréstimo de quatro anos
com juros de 12% ao ano, utilize 12%/12 para estimativa e 4*12 para nper. Se fizer
pagamentos anuais para o mesmo empréstimo, utilize 12% para estimativa e 4 para nper.
90
Função VP
Retorna o valor presente de um investimento. O valor presente é o valor total
correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando
você toma uma quantia de dinheiro emprestada, a quantia do empréstimo é o valor
presente para o concessor do empréstimo.
Sintaxe: VP(taxa,nper,pgto,[vf],[tipo])
A sintaxe da função VP tem os seguintes argumentos:
Taxa Necessário. A taxa de juros por período. Por exemplo, se você tiver um
empréstimo para um automóvel com taxa de de juros de 10% ano ano e
fizer pagamentos mensais, sua taxa de juros mensal será de 10%/12 ou
0,83%. Você deveria inserir 10%/12 ou 0,83%, ou 0,0083, na fórmula como
taxa.
Nper Necessário. O número total de períodos de pagamento em uma anuidade.
Por exemplo, se você conseguir um empréstimo de carro de quatro anos e
fizer pagamentos mensais, seu empréstimo terá 4*12 (ou 48) períodos. Você
deveria inserir 48 na fórmula para nper.
Pgto Necessário. 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. Por exemplo, os pagamentos mensais de R$
10.000 de um empréstimo de quatro anos para um carro serão de R$ 263,33.
Você deveria inserir -263,33 na fórmula como pgto. Se pgto for omitido,
você deverá incluir o argumento vf.
Vf Opcional. 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). Por exemplo, se você deseja economizar R$
50.000 para pagar um projeto especial em 18 anos, então o valor futuro será
de R$ 50.000. Você poderia então fazer uma estimativa conservadora na taxa
de juros e concluir quanto economizaria por mês. Se vf for omitido, você
deverá incluir o argumento pgto.
Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento.
91
Comentários:
Certifique-se de que esteja sendo consistente quanto às unidades usadas para
especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro
anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se
você fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para
nper.
As seguintes funções aplicam-se às anuidades:
Exemplo 1
Uma anuidade é uma série de constantes pagamentos em caixa realizados por um
período contínuo. Por exemplo, um empréstimo para um carro ou um prestação é
uma anuidade. Para obter mais informações, consulte a descrição para cada função de
anuidade.
Nas funções de anuidade, o saldo em dinheiro pago, como depósitos em poupanças, é
representado por um número negativo; o saldo em dinheiro recebido, como cheques
de dividendos é representado por números positivos. Por exemplo, um depósito de R$
1.000 no banco deveria ser representado pelo argumento -1000 se você for o
depositante e pelo argumento 1000 se você for o banco.
O Microsoft Excel soluciona o argumento financeiro em termos dos outros. Se a taxa não
for 0, então:
Se a taxa for 0, então: (pmt*nper)+pv+fv=0.
92
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.
Sintaxe: VF(taxa,nper,pgto,[vf],[tipo])
A sintaxe da função VP tem os seguintes argumentos:
Taxa Obrigatório. A taxa de juros por período.
Nper Obrigatório. O número total de períodos de pagamento em uma anuidade.
Pgto Obrigatório. O pagamento feito a cada período; não pode mudar durante a
vigência da anuidade. Geralmente, pgto contém o capital e os juros e
nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá incluir o
argumento vp.
Vp Opcional. 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.
Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento dos
pagamentos. Se tipo for omitido, será considerado 0.
Comentários:
Certifique-se de que esteja sendo consistente quanto às unidades usadas para
especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro
anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se
você fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para
nper.
Todos os argumentos, saques, tais como depósitos em poupança, serão representados
por números negativos; depósitos recebidos, tais como cheques de dividendos, serão
representados por números positivos.
93
Exemplo 1
Exemplo 2
Exemplo 3
Exemplo 4
94
Função NPER
Retorna o número de períodos para investimento de acordo com pagamentos constantes
e periódicos e uma taxa de juros constante.
Sintaxe: VF(taxa,pgto,vp,[vf],[tipo])
Para obter uma descrição completa dos argumentos em NPER e sobre as funções de
anuidade, consulte VP.
A sintaxe da função VP tem os seguintes argumentos:
Taxa Necessário. A taxa de juros por período.
Pgto Necessário. O pagamento feito em cada período; não pode mudar durante a
vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas
nenhuma outra tarifa ou taxas.
Vp Necessário. O valor presente ou atual de uma série de pagamentos futuros.
Vf Opcional. 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).
Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento.
Exemplo 1
95
Importação de Dados para o Excel
Propósito:
Importar dados.
Meta:
Importar arquivos de texto para o Excel.
Importar arquivos de dados para o Excel.
Criar consulta à base de dados.
Vincular dados do Excel no Access.
Muitas vezes é necessário utilizar no Excel dados que fazem parte de outros bancos de
dados ou mesmo tabelas na Internet. Nesses casos você pode utilizar os recursos que o
software disponibiliza quanto à obtenção de dados externos. Esses dados podem ser
provenientes de arquivos de texto, tabelas do MS Access, tabelas da Internet, do Microsoft
Query e outros tipos de arquivos. Veremos a seguir alguns destes recursos.
Importando arquivos de texto para o Excel
Propósito:
Importar um arquivo txt (Bloco de Notas) para o Excel.
Meta:
Abrir o arquivo Base.txt.
Fechar o arquivo Base.txt.
Importar o arquivo Base.txt para uma planilha do Excel.
Importando um arquivo texto
Em muitas aplicações as empresas gravam os seus dados em arquivos do tipo texto, com
extensão txt. Esses arquivos normalmente possuem uma primeira linha com os títulos das
colunas e, a partir da segundalinha, com os dados, um registro em cada linha e as colunas
(ou campos) separados por algum delimitador.
Para que possam ser importados pelo Excel é necessário que seja texto puro, ou seja, não
podem ser arquivos com extensões doc, docx, pdf ou quaisquer arquivos formatados em
outros editores de texto. Se forem gerados em algum editor de texto como Microsoft
Word, por exemplo, devem ser gravados no formato texto sem formatação, gerando um
arquivo de extensão txt.
96
1. Abra o arquivo Base.txt clicando duas vezes sobre o seu nome.
Observe que nesse arquivo existem informações sobre o código do funcionárioo, nome,
cargo, cidade, loja, meta mensal, data de aniversário e data de admissão. Sua tarefa
consiste em importar essas informações para dentro do Excel, gerando uma planilha.
2. Feche o arquivo Base.txt.
3. Abra uma nova pasta de trabalho em branco no Excel.
4. Na guia Dados, grupo Obter Dados Externos, clique em De Texto.
5. Localize o arquivo Base.txt e clique em Importar.
97
6. Será então iniciado o processo de importação do arquivo através do assistente, e a
tela referente será exibida.
Nessa janela você tem as seguintes informações:
Delimitado: as informações estão separadas por algum delimitador, como vírgula,
ponto e vírgula ou tabulação. No nosso caso, o assistente identificou que os dados
estão delimitados.
Largura fixa: especifica as colunas com largura fixa.
Iniciar importação na linha: permite escolher a partir de que linha a importação será
efetuada. O padrão é linha 1.
98
Origem do Arquivo: permite escolher o sistema operacional e o idioma. É
recomendável deixar no padrão que foi colocado.
7. Clique em Avançar, e a seguinte tela é exibida:
Delimitadores: especifica qual é o delimitador utilizado para separar as informações.
No seu caso, o delimitador utilizado é Tabulação. Se você quiser ter a certeza desse
fato, abra o arquivo no Microsoft Word e ative a ferramenta Mostrar tudo e você
poderá visualizar o caracteres → separando as informações.
Considerar delimitadores consecutivos como um só: essa opção é bastante útil
quando existirem dois delimitadores em seguida. Se não for selecionada, o fato de
existirem dois dilimitadores em seguida irá gerar uma coluna em branco na planilha.
Se estiver ativada, os dois delimitadores serão considerados como um só evitando as
colunas em branco.
8. Clique em Avançar. A janela exibida permite selecionar cada coluna e definir o
formato dos dados.
99
Caso não queira escolher os formatos das colunas, os dados serão importados no formato
Geral, que converte valor numérico em número, valor de dadta em data e o restante em
texto.
Cuidado na utilização de datas. Seu arquivo pode ter sido gerao por algum programa que
utilize outro sistema para sua representação, como por exemplo, algum programa norte
americano, onde a data é expressa como mês/dia/ano. Nesse caso a conversão desses
valores não será para um valor de data.
9. Clique em Concluir.
10. Na janela exibida, selecione a célula A1 da planilha para que seja o local inicial da
importação.
100
11. Clique em OK para finalizar.
12. A planilha gerada pode ser atualizada pela modificação do conteúdo do arquivo que
deu origem a ela, no caso, o arquivo Base.txt. Abra o arquivo Base.txt no Bloco de
Notas e faça as seguintes modificações:
NOME ORIGINAL NOME DOFICICADO
Douglas Francisco Douglas Piccoli
Fernando Abreu Fernando José Abreu
Thais Flores Thatiana Flores
Camila Piccoli Camila Flores Piccoli
13. Feche o arquivo Base.txt salvando suas alterações.
14. Volte à pasta de trabalho do Excel. Observe que as alterações efetuadas no arquivo
texto ainda não se refletem na planilha. Na guia Dados, grupo Conexões, clique em
Atualizar tudo.
101
15. Na janela que se abre selecione o arquivo Base.txt e clique em Importar.
16. Note que os registros foram atualizados. Feche a pasta e salve-a como Base de
Dados.xlsx.
17. Abra novamente a pasta de trabalho Base de Dados.xlsx.
18. Observe o aviso de segurança que aparece n aparte superior da planilha.
102
Essa mensagem informa que existem conexões externas com outros arquivos que podem
não ser seguras e foram desabilitadas. Quando isso ocorrer, se você souber a origem das
conexões e considera-las seguras, clique em Habilitar Conteúdo. Somente habilitando o
conteúdo o vínculo ficará disponível para atualizações.
19. Feche a pasta de trabalho Base de Dados.xlsx.
Importando arquivos de dados para o Excel
Propósito:
Importar uma tabela do Microsoft Access para o Excel.
Meta:
Importar a tabela Funcionários do Banco de Dados Grupo Empresarial.accdb para
uma planilha do Excel.
Importando um arquivo de dados
Você pode importar arquivos de dados para o Excel com muita facilidade. Importaremos
uma tabela de um banco de dados do Microsoft Access para uma pasta de trabalho do
Excel. O procedimento a ser realizado é bastante semelhante para outros tipos de banco
de dados.
1. Abra uma pasta de trabalho no Excel.
2. Na guia Dados, grupo Obter Dados Externos, selecione a opção Do Access.
3. Você vai importar uma tabela do Banco de dados Grupo Empresarial.accdb.
Selecione o arquivo e clique em Abrir.
103
4. O banco de dados possui duas tabelas. Selecione a tabela Funcionários e clique em
OK.
5. Na janela Importar Dados selecione as seguintes opções e clique em OK.
104
Você terá o seguinte resultado:
6. Feche a pasta de trabalho, salvando-a com o nome de Grupo Empresarial.xlsx.
Importando base de dados como consulta para o Excel
Propósito:
Importar uma tabela do Microsoft Access para o Excel como uma consulta.
105
Meta:
Importar a tabela Produtos do Banco de Dados Grupo Empresarial.accdb como uma
consulta para uma planilha do Excel.
Importando uma Tabela como Consulta
Você pode importar base de dados para o Excel como consulta, oermitindo a escolha dos
dados e campos que serão importados.
1. Abra uma nova pasta de trabalho no Excel.
2. Na guia Dados, grupo Obter Dados Externos, clique em De Outras Fontes. No menu
apresentado, selecione Do Microsoft Query.
106
3. Na janela apresentada escolha MS Access Database e clique em OK.
4. Escolha o banco de dados Grupo Empresarial.accdb e clique em OK.
5. Selecione a tabela Produtos e clique no sinal + para abrir o seus campos. Selecione os
campos Nome do produto, Fabricante, Fornecedor e Valor Unitário, passando-os
para o painel Colunas em sua consulta situado à direita. Clique em Avançar.
107
108
6. Você vai solicitar que a importação seja feita somente para o fornecedor Doces do
Brasil S/A. Para isso clique sobre o campo Fornecedor e e preencha o quadro Só
incluir linhas onde da maneira exibida a seguir:
109
7. Clique em Avançar. Agora você vai classificar a consulta por Nome dom produto.
8. Clique em Avançar. Deixe selecionada a opção Retornar dados ao Microsoft Excel e
clique em Concluir.
110
9. Selecione a célula A1 e clique em OK.
Você terá o seguinte resultado:
10. Feche a pasta de trabalho, salvando-a com o nome de Produtos Doces do Brasil.xlsx.
Vinculando dados do Excel no Access
Propósito:
Vincular os dados da planilha Dados da pasta Access.xlsx no Access.
111
Meta:
Abrir o Banco de Dados Bichos.accdb.
Importar a planilha Dados da pasta de trabalho Access.xlsx vinculando os dados.
Vinculando Dados do Excel no Access
Você pode importar para dentro do Microsoft Access planilhas do Excel. A tabela gerada
no Accessficará vinculada à planilha do Excel.
1. Abra o Banco de Dados Bichos.accdb.
2. Clique no botão Habilitar Conteúdo.
3. Na guia Dados, no grupo Dados Externos, em Importar e Vincular, selecione Excel.
112
4. Na janela que se abre procure pelo arquivo Access.xlsx e marque a opção Vincular à
fonte de dados criando tabela vinculada. Clique em OK.
5. Deixe selecionada a planilha Dados e clique em Avançar.
113
6. Selecione a primeira linha contém títulos de coluna e clique em Avançar.
7. Informe como nome da tabela Dados e clique em Concluir.
114
8. Agora você tem uma tabela no banco de dados denominada Dados. Clique duas vezes
sobre o nome da tabela no painel à esquerda e você terá o seguinte resultado:
Os dados só poderão ser atualizados pelo Excel, não sendo possível modifica-los pelo
Access. Se for efetuada qualquer modificação na planilha do Excel, essa modificação se
refletirá na tabela do Access, uma vez que os objetos estão vinculados.
Consultar para obter dados de uma página da Web
Você pode criar ou executar uma consulta à Web (consulta à Web: uma consulta que
recupera dados armazenados em sua intranet ou na Internet.) para recuperar texto ou
dados em uma página da Web. As consultas à Web são especialmente úteis para
recuperar dados que estão em tabelas ou áreas pré-formatadas (as tabelas são definidas
com o a marca HTML (HTML: a linguagem de marcação padrão usada em documentos na
World Wide Web. HTML usa marcas para indicar de que forma os navegadores da Web
devem exibir elementos de página, como texto e elementos gráficos, e de que forma
devem responder às ações do usuário.) <TABLE>; as áreas pré-formatadas são muitas
vezes definidas com a marca HTML <PRE>). Os dados recuperados não incluem imagens,
como imagens .gif, e não incluem o conteúdo de scripts.
Observação - Se você recuperar dados de um site da Web, é de sua responsabilidade
garantir que a utilização dos mesmos cumpra os termos e as condições aplicáveis que
regem o uso dos dados pelo proprietário e/ou operador desse site da Web.
115
Criar uma nova consulta Web
Siga um destes procedimentos:
Iniciar o Microsoft Excel 2013
Na guia Dados, grupo Obter Dados Externos, clique na opção Da Web.
Na caixa de diálogo Nova Consulta à Web, insira o URL referente à página Web da
qual você deseja obter dados. É possível digitar o URL, colá-lo de um endereço
copiado ou clicar na seta próxima à lista Endereço e selecionar um endereço utilizado
recentemente.
Clique em Importar.
116
Na pasta de trabalho com a a planilha (Plan1) aberta, selecione a célula A1.
Na caixa de diálogo Importar Dados, observe se a opção Na planilha existente
=$A$1 está selecionada e, em seguida, clique em OK.
Veja o resultado obtido:
Observação - Você pode realizar uma nova consulta à Web, importando os dados para
uma nova planilha na mesma pasta de trabalho.
117
Filtro, Classificação e Filtro Avançado
Nesse capítulo você destacará dados de interesse através da aplicação do recurso filtro e
classificará o conteúdo das planilhas por uma chave e por mais de uma chave. Você criará
planilhas que apresentarão resultados desejados. Com base em uma lista de produtos
você criará planilhas que apresentarão os produtos separados por departamento.
Propósito:
Conhecer o recurso AutoFiltro.
Meta:
Aplicar o AutoFiltro no arquivo Filtro Avançado.xlsx.
Nesta atividade você utilizará o arquivo Filtro Avançado.xlsx e aplicará o AutoFiltro para
destacar um grupo de registros que correspondem aos produtos de determinado
Departamento.
1. Abra a pasta de trabalho Filtro Avançado.xlsx.
2. Na guia Dados, grupo Classificar e Filtrar e, em seguida, clique no comando Filtro.
Botões para aplicação do filtro aparecem ao lado do rótulo de cada coluna
3. Clique no botão da coluna Departamento aplique Filtro abra as opções de lista e
desmarque a opção Selecionar Tudo.
118
4. E, em seguida, marque apenas a opção Telefones e Celulares e clique em OK.
119
5. O resultado é a exibição das linhas onde constam o departamento escolhido. As linhas
que correspondem a outrs departamentos ficam ocultas.
Observação – A coluna onde o filtro foi aplicado exibe uma imagem representando a
aplicação do filtro.
6. Para mostrar todas as linhas novamente clique no botão ao lado do nome do campo
Departamento, escolha Selecionar Tudo e clique em OK.
Podemos reexibir todas as linhas clicando na opçõe Limpar do grupo Classificar e Filtrar
da guia Dados.
120
Aplicar o filtro a mais de um campo
Propósito:
Apresentar dados que atendam a critérios combinados.
Meta:
Aplicar um filtro que exiba os produtos do departamento Telefones e Celulares cujo
preço seja inferior a R$ 600,00.
1. Aplique o AutoFiltro que apresenta os produtos do departamento Telefones e
Celulares.
2. Clique no botão da coluna Preço.
3. Escolha a opção Filtros de número e escolha a opção É Menor do Que...
121
4. Digite o limite superior R$ 600,00 e clique em OK.
Clique em Limpar do grupo Classificar e Filtrar quando desejar exibir todas as linhas.
Aplicar filtro alternativo
Propósito:
Aplicar filtro quando parte da informação é conhecida.
Meta:
Aplicar filtro para produtos iniciados pela letra No e filtro para produtos que
contenham como parte de sua descrição a informação – 14”.
1. Clique no botão de filtro do campo Produto.
2. Escolha em Filtros de Texto a opção Começa com...
122
3. Preencha com a parte conhecida da informação. Pressione OK.
4. Clique em Limpar para restaurar todos os produtos.
5. Clique no botão de filtro da coluna Descrição.
6. Escolha Filtros de Texto e opção Contém 14”.
As opções que aprecem na lista suspensa quando pressionamos o botão filtro dependem do
tipo de informação que predomina na coluna. Os tipos podem ser Filtros de Texto, Filtros
de Número e Filtros de Data.
123
Classificar com AutoFiltro
Propósito:
Apresentar o AutoFiltro como alternativa de classificação.
Meta:
Classificar por odem alfabética de produtos.
1. Clique em Limpar para retirar os filtros aplicados.
2. Clique no botão de filtro da coluna Produto.
3. Escolha a opção Classificar de A a Z para colocar os dados da coluna em ordem
crescente ou Classificar de Z a A para colocar os dados em ordem decrescente.
Quando a coluna estiver preenchida com números as opções Classificar do Menor para o
Maior, Classificar do Maior para o Menor. Quando a coluna estiver preenchida com
datas, as opções serão Classifcar do Mais Antigo para o Mais Novo e Classificar do
Mais Novo para o Mais Antigo.
Clique no botão Filtro do grupo Classificar e Filtrar quando quiser retirar os botões do
Auto Filtro.
124
Classificação Avançada
Propósito:
Apresentar o recurso de classificação por mais de uma chave.
Meta:
Classificar os funcionários da planilha Nome.xlsx por Estado Civil e Nome.
1. Abra a pasta de trabalho Nome.xlsx.
2. Na planilha Nome, selecione qualquer célula dentro da lista.
3. Na guia Dados, grupo Classificar e Filtrar, clique no comando Classificar.
125
4. Escolha na caixa de diálogo Classificar na coluna Classificar por escolha Estado Civil.
5. Clique em Adicionar Nível para definir uma segunda chave de classificação.
6. Escolha a coluna Nomes na linha que surgiu e clique em OK.
126
Classificação por lista personalizada
Propósito:
Classificar os dados por uma ordem estabelecida pelo usuário.
Meta:
Classificar os funcionários por Cargos em ordem hierárquica.
Você criará uma lista personalizada para poder classificar os cargos em umaordem de
importância arbitrária. Para isso você digitará os cargos na lista personalizada na seguinte
ordem: President, Diretor(a), Gerente, Subgerente, Analista, Contador(a), Secretária,
Escriturário, Comprador, Vendedor, Conferente e Estigiário.
1. Na guia Dados, grupo Classificar e Filtrar, comando Classificar.
2. Escolha a coluna Cargo e escolha Lista Peronalizada na caixa de combinação
Ordem.
127
3. Digite os cargos na ordem especificada e após a digitação, pressione ENTER. Após
cada cargo ou separando por vírgulas.
4. Clique em Adicionar e a lista será criada.
128
5. Clique em OK e OK novamente para classificar pela lista personalizada.
Classificação por cores
Propósito:
Utilizar cores como critério de classificação.
Meta:
Classificar os produtos mais vendidos através de codificação por cores.
Você vai classificar o arquivo Produtos Mais Vendidos.xlsx onde a cor verde representa
os produtos mais vendidos, a cor vermelha os produtos de venda mediana e a cor azul os
produtos com pouca venda.
1. Abra o arquivo Produtos Mais Vendidos.xlsx.
2. Na guia Dados, grupo Classificar e Filtrar, comando Classificar.
129
3. Escolha a coluna Produto e Cor de Célula na caixa de combinação Classificar.
Existem as opções Cor da Fonte e Ícone da Célula, neste último caso quando for aplicada a
Formatação Condicional.
4. Estabeleça a ordem de importância das cores, adicionando a cada cor definida.
5. Clique em OK para efetuar a classificação.
130
Filtro avançado
Propósito:
Aplicar filtro utilizando critérios avançados.
Meta:
Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha para cada
departamento.
1. Abra o arquivo Filtro Avançado.xlsx. Você vai criar uma lista dos departamentos sem
repetição.
2. Selecione a coluna Departamento.
3. Clique xom o botão direito do mouse e, em seguida, clique em Copiar ou use
qualquer outro método de cópia.
4. Selecione a planilha Critérios e a célula A1.
5. Cole o conteúdo da área de transferência.
6. Mantendo a coluna selecionada, vá a guia Dados, grupo Ferramentas de Dados,
clique no comando Remover Duplicata.
131
7. Certifique-se que a caixa de verificação Meus dados contêm cabeçalhos esteja
marcada. Caso não esteja, marque-a e clique em OK.
8. Clique em OK para concluir.
132
Você vai criar as áreas de critérios necessárias para o uso do filtro avançado. Um critério é
constituído de uma célula com o nome da coluna que contém o dado a ser pesquisado
tendo logo abaixo a célula que contém o dado pesquisado. Ela é constituída, portanto, de
um intervalo de pelo menos duas células.
9. Construa os critérios como abaixo:
10. Insira e renomeie uma planilha para os departamentos de Informática, Telefones e
Celulares, Câmeras e Filmadoras, Eletrônicos e uma planilha para os produtos dos
outros departamentos.
11. Você vai iniciar agora a construção das planilhas utilizando o Filtro Avançado.
Selecione todas as linhas da planilha Geral e renomeie este intervalo como Geral.
133
12. Vá para a planilha Informática.
13. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.
14. No quadro que surge marque a opção Copiar para outro local.
15. Preencha o Intervalo da lista com Geral.
16. Preencha o intervalo de critérios clicando na aba da planilha Critérios e
selecionando as células A1 e A2. E marque a caixa de seleção Somente registros
exclusivos.
17. Vá para Copiar para e clique na célula A1 da planilha Informática.
18. Clique em OK.
134
19. Repita os passo 12 a 17 iniciando o processo nas planilhas Telefones e Celulares,
Câmeras e Filmadoras, Eletrônicos utilizando os intervalos de critérios
correspondentes.
20. Vá para a planilha Outros.
21. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.
22. Marque a opção Copiar para outro local.
23. Preencha Intervalo da lista com Geral.
24. Preencha Intervalo de critérios selecionando o intervalo A14:A18 da planilha
Critérios.
25. Clique em OK.
135
Filtro avançado com critérios múltiplos
Propósito:
Aplicar filtro utilizando critérios avançados combinados.
Meta:
Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha com os
produtos do departamento Informática com preço inferior a R$ 1.200,00.
1. Vá a planilha Critérios, digite Preço na célula B1 e <1200 na célula B2.
O intervalo A1:B2 da planilha Critérios formará o critério combinado a ser usado no filtro.
Esse critério fará com que o filtro avançado retorne os produtos de informática e com
preço inferior a R$ 1.200,00.
Só os produtos que atenderem a estas duas condições aparecerão no resultado.
2. Insira uma nova planilha.
3. Renomeie como Informática – menor que 1200.
4. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados.
5. Marque a opção Copiar para outro local.
6. Preencha Intervalo da lista com Geral.
7. Preencha Intervalo de critérios selecionando o intervalo A1:B2 da planilha Critérios.
136
8. Clique em OK.
Formatos Condicionais Avançados
Aprender mais sobre a formatação condicional
A formatação condicional ajuda a responder visualmente a questões específicas sobre
seus dados. Você pode aplicar a formatação condicional a um intervalo de células, a uma
tabela do Microsoft Excel ou a um relatório de Tabela Dinâmica. Há diferenças
importantes que devem ser entendidas ao usar formatação condicional em um relatório
de Tabela Dinâmica.
Os benefícios da formatação condicional
Ao analisar dados, você sempe formula questões como:
Onde estão as exceções em um resumo de lucros nos últimos cinco anos?
Quais são as tendências em uma sondagem de opinião de marketing ao longo dos
últimos dois anos?
Quem vendeu mais de R$ 50.000,00 este mês?
Qual é a distribuição estaria geral dos empregados?
Quais produtos têm aumentos de faturamento acima de 10% de um ano para outro?
Quais são os alunos com melhor e pior desempenho na classe novata?
A formatação condicional ajuda a responder a essas questões ao facilitar o destaque de
células ou intervalos de células, a ênfase de valores não usuais e a visualização de dados
usando barras de dados, escalas de cores e conjuntos de ícones. Um formato condicional
altera a aparência de um intervalo de células com base em condições (ou critérios). Se a
condição for verdadeira, o intervalo de células será formatado com base nessa condição;
se a condição for falsa, o intervalo de células não será formatado com base nessa
condição.
Você poderá classificar e filtrar por formato, incluindo a cor de plano de fundo da célula e
a cor da fonte, quer tenha formatado as células de modo condicional ou manual. O
exemplo a seguir mostra a formatação condicional que usa cores de fundo da célula, um
conjunto de ícones de 3 setas e barras de dados.
137
Observação - Ao criar um formato condicional, você só poderá fazer referência a outras
células da mesma planilha ou, em alguns casos, a células de planilhas da mesma pasta de
trabalho aberta no momento. Não é possível usar formatação condicional em referências
externas a outra pasta de trabalho.
Formatação condicional para um relatório de Tabela Dinâmica
A formatação condicional em um relatório de Tabela Dinâmica difere da formatação
condicional em um intervalo de células ou em uma tabela do Excel de diversas maneiras:
Se você alterar o layout do relatório de Tabela Dinâmica filtrando, ocultando níveis,
reduzindo e expandindo níveis, ou movendo um campo, o formato condicional será
mantido desde que os campos dos dados subjacentes não sejam removidos.
O escopo do formato condicional de campos na área Valores pode estar baseadona
hierarquia de dados, sendo determinado por todos os filhos visíveis (o nível inferior
seguinte em uma hierarquia) de um pai (o nível superior seguinte em uma hierarquia)
em linhas de uma ou mais colunas, ou em colunas de uma ou mais linhas.
Observação - Na hierarquia de dados, os filhos não herdam a formatação condicional do
pai, e nem o pai a herda dos filhos.
Há três métodos para definir o escopo do formato condicional de campos na área
Valores: por seleção, por campo de correspondência e por campo de valor.
O método padrão de definir escopo de campos na área Valores é por seleção. É possível
alterar o método de escopo para o campo de correspondência ou de valor usando o
botão de opção Aplicar regra de formatação a, a caixa de diálogo Nova Regra de
Formatação ou a caixa de diálogo Editar Regra de Formatação. Os três métodos de
escopo (guia Página Inicial, grupo Estilos, comando Formatação Condicional) oferecem
maior flexibilidade, dependendo de suas necessidades.
138
139
140
Usando a Caixa de Diálogo – Nova Regra de Formatação
Selecione um Tipo de Regra:
Formatar todas as células com base em seus respectivos valores;
Formatar apenas células que contenham;
Formatar apenas os primeiros ou últimos valores;
Formatar apenas valores acima ou abaixo da média;
Formatar apenas valores exclusivos ou duplicados.
Usar uma fórmulas para determinar quais células devem ser formatadas.
Observação – associada a sua escolha em Selecione um Tipo de Regra você irá também
utilizar-se da área Edite a Descrição da Regra.
141
142
143
Tabela Dinâmica
Propósito:
Resumir dados usando a Tabela Dinâmica.
Meta:
Construir Tabela Dinâmica que apresente dados agrupados por projeto.
Utilizando os dados do arquivo Movimento Financeiro.xlsx você criará uma
apresentação da soma dos valores agrupados por projeto.
1. Abra o arquivo Movimento Financeiro.xlsx.
2. Na guia Inserir, grupo Tabelas, comando Tabela Dinâmica.
3. Para preencher o campo Tabela/Intervalo do quadro Criar Tabela Dinâmica,
selecione o intervalo A3:G56 da planilha Dados.
4. Selecione Nova Planilha para criar a Tabela Dinâmica em uma planilha
independente.
144
5. Clique em OK. Surge uma nova guia Ferramentas de Tabela Dinâmica com duas
subguias: Opções e Design.
A direita surge o Painel de Tarefas Lista de Campos da Tabela Dinâmica.
145
Na lista de campos você escolherá os campos (colunas) que utilizará no relatório de tabela
dinâmica.
Filtro de Relatório – permite que você escolha um ou mais campos para filtar dados
no seu relatório.
Rótulos de Linha – são os campos onde itens iguais são agrupados.
Rótulos de Coluna – são os campos que serão suas informações conmsolidadas.
Valores – corresponde a uma ou mais colunas que serão suas informações
consolidadas.
Marque o campo Projeto e o campo Valor. Será construído o relatório agrupando os
valores por Projeto.
146
Atualização e Inclusão de Dados
Propósito:
Modificar os dados existentes e acrescentar novos dados tornando-os visíveis na
Tabela Dinâmica.
Meta:
Alterar dados já existentes na guia Dados e atualizar a Tabela Dinâmica.
Inserir uma nova linha entre linhas já existentes e no linha após o último projeto e
torna-los visíveis na Tabela Dinâmica.
1. Torne a célula A13 como célula ativa na planilha de Dados.
2. Insira uma nova linha.
3. Acrescente os dados:
o Data: 23/10/2010
o Projeto: Hidroelétrica Torrente
o Item: Venda de Ações
o Categoria: Dinheiro dos Sócios
o E/S: Entrada
o Valor: 10.000.000,00
o Estado: Paraná
147
4. Volte à Tabela Dinâmica, Este novo projeto não foi incluído.
5. Vá a guia Dados, grupo Conexões, comando Atualizar Tudo e o novo projeto será
relacionado no final.
6. Clique no botão Classificar para ordenar os projetos.
148
7. Inclua mais uma entrada na última linha vazia da planilha Dados. Digite:
o Data: 20/06/2011
o Projeto: Aeroporto Perus
o Item: Duplicatas – Desconto
o Categoria: Contas a Receber
o E/S: Entrada
o Valor: 500.000,00
o Estado: Goiás
A linha acrescentada está fora do intervalo de dados reconhecido pela Tabela Dinâmica.
Portanto, o recursos Atualizar não terá efeito neste caso. É necessário redefinir o
intervalo.
149
8. Volte à Tabela Dinâmica.
9. Clique na guia Inserir, grupo Tabelas, comando Tabela Dinâmica e corrija o intervalo
selecionado Dados!$A$3:$G$58.
10. Clique em OK.
150
Criação de outros níveis
Propósito:
Acrescentar subníveis à Tabela Dinâmica.
Meta:
Subdividir os valores de cada projeto por Entrada e Saída. Cada um desses níveis será,
também subdividido por Categoria.
1. Marque E/S e Categoria no painel da lista de campos da Tabela Dinâmica e os dados
são apresentados com as subdivisões desejadas.
2. Selecione a célula de qualquer subtotal e clique em FERRAMENTAS DE TABELA
DINÂMICA em ANALISAR, grupo Campo Ativo, comando Configurações do
Campo.
151
3. Clique em OK.
4. Na coluna Soma de Valor, dê um duplo clique na linha correspondente a um subtotal.
Será incluída uma nova planilha na pasta de trabalho contendo os itens que formam o
referido subtotal.
Quando criamos uma Tabela Dinâmica com a opção Nova Planilha, uma nova planilha é
acrescentada. É aconselhável renomearmos as planilhas que vão sendo incluídas com nomes
que indiquem o conteúdo da planilha.
5. Na parte inferior do painel de tarefas, arraste os campos E/S da área Rótulos de Linha
para a área Rótulos de Colunas.
152
Formatar a Tabela Dinâmica
Propósito:
Alterar a aparência da Tabela Dinâmica para facilitar a visualização.
Meta:
Aplicar formato monetário á Tabela Dinâmica.
Aplicar Bordas à Tabela Dinâmica e alterar as funções agregadas utilizadas.
Escolha e selecione o Estilo desejado na guia Design, no grupo Estilos de Tabela
Dinâmica.
1. Na guia Opções, no grupo Campo Ativo, clique em Configurações do Campo.
153
No quadro que surge, analise as várias opções de funções que estão disponíveis para
resumir o campo. Além de somar, você pode contar, calcular a média aritmética, obter o
valor máximo ou mínimo, etc.
2. Clique em Formato do Número e escolha Moeda.
3. Clique em OK. Esse será o resultado.
154
Agrupar Itens
Propósito:
Formar grupos de itens.
Meta:
Agrupar os projetos pelas regiões.
1. Crie uma nova Tabela Dinâmica. Volte à planilha Dados, clique em Tabela Dinâmica
do grupo Tabelas da guia Inserir.
2. Selecione os Campos Estado, Projeto e Valor.
155
Os Estados correspondem às regiões Sul (Santa Catarina) e Sudeste (São Paulo, Rio de
Janeiro e Minas Gerais).
3. Selecione o grupo Minas Gerais, mantenha a tecla CTRL pressionada e selecione os
demais Estados da região Sudeste (Rio de Janeiro e São Paulo).
4. Clique em Seleção de Grupo do grupo Agrupar da guia Opções.
156
Aplicar Filtro de Relatório
Propósito:
Selecionar os dados a serem visualizados.
Meta:
Filtrar para visualizar os projetos de um só Estado.
1. Crie uma Tabela Dinâmica em uma nova planilha.
2. Escolha os campos Projeto, Estado e Valor.
3. Arraste o campo estado para a área de Filtro de Relatório.
157
4. Na célula A1 e B1 surge o Filtro de Relatório.
5. Clique na caixa de combinação da célula B1.
6. Clique no Estado que quer exibir. Os projetos e respectivos valores dos Estados não
selecionados ficarãoocultos.
158
Agrupar campos por períodos
Propósito:
Agrupar informações por períodos de tempo com base num campo de Data.
Meta:
Criar Tabela Dinâmica que relaciona os dados dos projetos agrupados por categoria
de itens e por período de tempo.
1. Crie uma Tabela Dinâmica em uma nova planilha.
2. Escolha os campos E/S, Categoria, Valor e Data.
159
3. Arraste o campo Data para a área de Rótulos de Colunas.
160
Surge uma coluna para cada data relacionada no campo Data. Você vai agrupar estas
datas em períodos que tenham significado para quem analisa.
4. Selecione a primeira data. Clique em Agrupar Campo do grupo Agrupar da guia
Opções.
5. Clique em Anos. O item Meses já estava marcado e você pode acrescentar vários
níveis de agrupamento de datas. Cada grupo em destaque formará um grupo. Caso
deseje desmarcá-lo clique novamente sobre o período.
6. Clique em OK.
161
Segmentação de Dados
Propósito:
Agrupar filtros aprimorados em Tabelas Dinâmicas.
Meta:
Utilizar o recurso de segmentação de dados para filtar dados por Projeto e por Estado.
1. Ainda na Tabela Dinâmica da aplicação anterior, clique no botão Segmentação de
Daos, do grupo Filtro da guia Inserir.
162
2. Marque os campos Projeto e Estado e clique em OK.
3. Posicione as segmentações de dados. Todos os itens aparecem com cor destacada
indicando que nenhum filtro foi aplicado ainda.
4. Clique no Estado do Rio de Janeiro. Somente este Estado fica destacado. No outro
quadro aparecem destacados os projetos deste Estado. Os dados na Tabela Dinâmica
correspondem somente aos dados dos projetos do Rio de Janeiro.
163
Você pode fazer a seleção de vários Estados mantendo a tecla CTRL pressionada enquanto
clica com o mouse nos Estados escolhidos.
5. Clique em um desses projetos. O resultado é a apresentação dos valores deste projeto.
Neste caso, usamos um filtro combinando os dois campos, Estado e Projeto.
Para desaplicar o(s) filtro(s) clique no botão Limpar Filtro de cada Segmentação de
Dados.
Gráfico Dinâmico
Propósito:
Demonstrar a construção e o uso do Gráfico Dinâmico.
164
Meta:
Construir gráfico dinâmico referente à Tabela Dinâmica.
1. Em uma célula da Tabela Dinâmica, clique no botão Gráfico Dinâmico do grupo
Ferramentas da guia Opções.
Você também pode criar o gráfico com a tecla de função F11.
165
2. Escolha o tipo e clique em OK.
Use os recursos de gráfico do Excel. A única diferença entre um gráfico normal do Excel e do
Gráfico Dinâmico é que este possui caixas de combinação para aplicar filtro.
3. Clique no botão Alternar Linha/Coluna para que o eixo X apresente os meses e os
anos.
166
4. Use as caixas de combinação para filtrar pelo campo desejado.
Mostrar Páginas de Relatório
Propósito:
Utilizar recurso para criar uma cópia da Tabela Dinâmica para item do Filtro de
Relatório.
167
Meta:
Criar Páginas de Relatório para a Tabela Dinâmica.
1. Acrescente o campo Estado na área Filtro de Relatório.
2. Clique no botão Tabela Dinâmica, na guia Opções e, em seguida, em Mostrar Páginas
do Filtro do Relatório.
168
3. Escolha Estado e clique em OK.
Será criada uma cópia da Tabela Dinâmica para cada Estado constante na planilha Dados.
169
Gráfico Dinâmico
Pode ser difícil obter uma visão geral quando você tem dados em uma Tabela Dinâmica
enorme ou quando você tem um monte de dados complexos de planilha que inclui texto
e números com títulos de coluna, como este:
Um Gráfico Dinâmico pode ajudá-lo a dar sentido a esses dados. Enquanto um Gráfico
Dinâmico mostra a série de dados, as categorias e os eixos de gráfico da mesma forma
que um gráfico padrão, também lhe fornece os controles de filtragem interativos na
gráfico para que você possa analisar rapidamente um subconjunto dos dados.
Para os dados da planilha, você pode criar um Gráfico Dinâmico sem criar primeiro uma
Tabela Dinâmica. Você pode até mesmo criar um Gráfico Dinâmico que seja recomendado
para seus dados. O Excel criará automaticamente uma Tabela Dinâmica associada. Veja
como:
1. Clique em qualquer lugar dos dados.
2. Na guia Inserir, no grupo Gráficos, escolha Gráficos Recomendados.
170
3. Na guia Gráficos Recomendados, escolha qualquer gráfico com o ícone Gráfico
Dinâmico no canto superior. Uma visualização do seu Gráfico Dinâmico é exibida
no painel Visualização.
4. Depois de encontrar um Gráfico Dinâmico que você goste, clique em OK.
Observação - Se você não encontrar um Gráfico Dinâmico de que gosta, clique em
Gráfico Dinâmico na guia Inserir em vez de Gráficos Recomendados.
5. No Gráfico Dinâmico que é exibido, clique em qualquer controle interativo e escolha
as opções de classificação ou filtragem desejadas.
171
Dica
Para remover um Gráfico Dinâmico que você não deseja mais, selecione-o e pressione
Delete.
Outras maneiras de criar um Gráfico Dinâmico
Se você já tem uma Tabela Dinâmica, você pode basear um Gráfico Dinâmico nessa Tabela
Dinâmica. Ou se você tiver conectado um OLAP (processamento analítico online) externo
ou uma fonte de dados do Modelo de Dados, poderá criar um Gráfico Dinâmico
autônomo, "separado", sem criar uma Tabela Dinâmica.
o Criar um Gráfico Dinâmico para uma Tabela Dinâmica existente.
o Conectar-se a dados externos para criar um Gráfico Dinâmico.
o Usar uma conexão de dados externa existente para criar um Gráfico Dinâmico.
172
Sobre os relatórios de gráfico dinâmico
Um relatório de gráfico dinâmico oferece uma representação gráfica dos dados em um
relatório da tabela dinâmica (relatório de tabela dinâmica: um relatório de referências
cruzadas interativo do Excel que resume e analisa dados, como registros de banco de
dados, de várias fontes, incluindo fontes externas ao Excel.). Você pode alterar o layout e
os dados exibidos em um relatório de gráfico dinâmico da mesma forma que em um
relatório da tabela dinâmica.
Um relatório da tabela dinâmica de dados de vendas
Um relatório de gráfico dinâmico dos mesmos dados
Terminologia de gráfico dinâmico
Os relatórios de gráfico dinâmico têm alguns elementos especializados além das séries
(série de dados: pontos de dados relacionados plotados em um gráfico e provenientes de
linhas ou colunas de folha de dados. Cada série de dados tem uma cor ou um padrão
exclusivo. É possível plotar uma ou mais séries de dados em um gráfico. Os gráficos de
pizza têm somente uma série de dados.), categorias, marcadores de dados (marcador de
dados: uma barra, uma área, um ponto, uma fatia ou outro símbolo em um gráfico que
representa um único ponto de dados ou valor que se origina em uma célula de planilha.
Os marcadores de dados relacionados em um gráfico constituem uma série de dados.) e
eixos (eixo: uma linha que contorna a área de plotagem do gráfico usada como um
quadro de referência de medida. Em geral, o eixo y é o eixo vertical e contém dados; o
eixo x é o eixo horizontal e contém categorias.) dos gráficos normais do Microsoft Excel.
173
Campo de página
Campo de dados
Campo de série
Itens
Campo de categoria
Campo de página
Um campo que você usa para filtrar dados por itens específicos. No exemplo, o campo da
página Região exibe dados de todas as regiões. Para exibir dados de uma única região,
você pode clicar na seta suspensa ao lado de (Tudo) e selecionar a região.
Campo de dados
Um campo dos dados de origem (dados de origem: a lista ou a tabela usada para criar um
relatório de tabela dinâmica ou de gráfico dinâmico. Os dados de origempodem ser
obtidos de uma lista ou intervalo do Excel, de um banco de dados ou cubo externo, ou de
outro relatório de tabela dinâmica.) de base que fornece valores para serem comparados
ou medidos. No exemplo, Soma de vendas é um campo de dados que resume as vendas
trimestrais em cada região de acordo com cada esporte. O marcador de dados da primeira
categoria (Tri1) alcança quase 250 no eixo dos valores (y). Essa quantia é a soma de
vendas de Tênis, Safári e Golfe no primeiro trimestre. Dependendo dos dados de origem
usados para o relatório, você poderá alterar a função de resumo (função resumo: um tipo
de cálculo que combina os dados de origem em um relatório de tabela dinâmica ou em
uma tabela de combinação ou quando você está inserindo subtotais automáticos em uma
lista ou banco de dados. Os exemplos de função resumo incluem Soma, Contar e Média.)
para Média, Contagem, Produto ou outro cálculo.
174
Campo de série
Um campo que você atribui a uma orientação de série em um relatório de gráfico
dinâmico. Os itens no campo fornecem as séries de dados individuais. No exemplo,
Esporte é um campo de série com três itens: Tênis, Safári e Golfe.
Item
Os itens representam as entradas exclusivas em um campo e aparecem nas listas
suspensas dos campos de página, campos de categoria e campos de série. No exemplo,
Tri1, Tri2, Tri3 e Tri4 são itens no campo de categoria Trimestre, enquanto Tênis, Safári e
Golfe são itens no campo de série Esporte.
Campo de categoria
Um campo dos dados de origem que é atribuído a uma orientação de categoria em um
relatório de gráfico dinâmico. Um campo de categoria fornece as categorias individuais
para os quais os pontos de dados são colocados em gráfico. No exemplo, Trimestre é um
campo de categoria.
Como um relatório de gráfico dinâmico representa dados
Um relatório de gráfico dinâmico sempre tem um relatório da tabela dinâmica associado
(relatório de tabela dinâmica associado: o relatório de tabela dinâmica que fornece os
dados de origem ao relatório de gráfico dinâmico. É criado automaticamente quando você
cria um novo relatório de gráfico dinâmico. Quando você altera o layout de um relatório, o
outro também é alterado.). Os dois relatórios têm campos que correspondem entre si.
Quando você altera a posição de um campo em um relatório, o campo correspondente no
outro relatório também é movido.
O exemplo a seguir mostra a correspondência entre os campos de cada tipo de relatório:
175
Campos de página
Campos de dados
Campo de linha, corresponde ao campo de categoria
Campo de coluna, corresponde ao campo de série
Quando você cria um relatório de gráfico dinâmico a partir de um relatório da tabela
dinâmica, o layout do relatório de gráfico dinâmico — ou seja, a posição de seus
campos — é determinado inicialmente pelo layout do relatório da tabela dinâmica.
Quando você cria primeiro o relatório de gráfico dinâmico, você determina o layout do
gráfico arrastando campo da janela Lista de campos da tabela dinâmica para as áreas para
soltar (área para soltar: uma área em um relatório de tabela dinâmica ou de gráfico
dinâmico na qual é possível soltar campos da caixa de diálogo Lista de Campos para exibir
os dados no campo. Os rótulos em cada área para soltar indicam os tipos de campos que
podem ser criados no relatório.) na planilha de gráfico (folha de gráfico: uma folha em
uma pasta de trabalho que contém somente um gráfico. A folha de gráfico é útil na
exibição de um gráfico ou relatório de gráfico dinâmico separada dos dados da planilha
ou do relatório de tabela dinâmica.). O Microsoft Excel cria automaticamente um relatório
da tabela dinâmica associado que utiliza um layout correspondente.
Diferenças entre relatórios de gráfico dinâmico e gráficos não interativos normais
No caso de gráficos normais, você cria um gráfico para cada modo de exibição de dados
que deseja ver. No caso de relatórios de gráfico dinâmico, você pode criar um único
gráfico e ver os dados de diferentes maneiras, alterando o layout do relatório ou os
detalhes exibidos.
Se estiver familiarizado com os gráficos normais, você verá que a maioria das operações
são iguais em relatórios de gráfico dinâmico. Entretanto, existem algumas diferenças:
Tipos de gráfico
O tipo de gráfico padrão de um gráfico normal é o gráfico de colunas agrupadas, que
compara valores entre categorias. O tipo de gráfico padrão de um relatório de gráfico
dinâmico é o gráfico de colunas empilhadas, que compara o peso de cada valor com
relação a um total entre categorias. Você pode alterar um relatório de gráfico dinâmico
para qualquer tipo de gráfico, exceto gráficos xy (de dispersão), de ações ou de bolhas.
176
Local do gráfico
Os gráficos normais são incorporados (gráfico inserido: um gráfico colocado em uma
planilha e não em uma folha de gráfico separada. Os gráficos inseridos são úteis quando
você deseja ver ou imprimir um gráfico ou relatório de gráfico dinâmico com seus dados
de origem ou outras informações em uma planilha.) nas planilhas por padrão. Os
relatórios de gráfico dinâmico são criados em planilhas de gráfico (folha de gráfico: uma
folha em uma pasta de trabalho que contém somente um gráfico. A folha de gráfico é útil
na exibição de um gráfico ou relatório de gráfico dinâmico separada dos dados da
planilha ou do relatório de tabela dinâmica.) por padrão. Uma vez criado, você pode
realocar um relatório de gráfico dinâmico para uma planilha.
Criando o gráfico
Para criar um gráfico normal no Microsoft Excel, você usa o Assistente de gráfico. Para
criar um relatório de gráfico dinâmico, você pode usar o Assistente de gráfico ou o
Assistente de tabela dinâmica e gráfico dinâmico se já tiver um relatório da tabela
dinâmica para servir como dados de origem para o relatório de gráfico dinâmico.
Dados de origem
Os gráficos normais são vinculados diretamente a células de planilha. Os relatórios de
gráfico dinâmico podem ser baseados em diversos tipos de dados, incluindo bancos de
dados e listas (lista: uma série de linhas que contêm dados relacionados ou que você
determina que funcionará como uma folha de dados, usando o comando Criar Lista.) do
Excel, vários intervalos de dados que você queira consolidar e origens externas, como
bancos de dados do Microsoft Access e bancos de dados OLAP (OLAP: uma tecnologia de
banco de dados otimizada para a geração de consultas e relatórios, e não para o
processamento de transações. Os dados OLAP são organizados hierarquicamente e
armazenados em cubos em vez de tabelas.).
Elementos de gráficos
Os relatórios de gráfico dinâmico contêm os mesmos elementos dos gráficos normais,
mas também contêm campos e itens que podem receber adições, ser girados ou
removidos para exibir seus dados de formas diferentes. As categorias, séries e dados nos
gráficos normais são campos de categoria, campos de série e campos de dados nos
relatórios de gráfico dinâmico. Os relatórios de gráfico dinâmico também podem conter
campos de página. Cada um desses campos contém itens, que em gráficos normais são
exibidos como rótulos de categoria ou nomes de séries em legendas (legenda: uma caixa
que identifica os padrões ou cores atribuídos às séries de dados ou categorias de um
gráfico.). É possível ocultar as estruturas de botões de campo (botão de campo: botão que
identifica um campo em um relatório de tabela dinâmica ou de gráfico dinâmico. É
possível arrastar os botões de campo para alterar o layout do relatório ou clicar nas setas
ao lado dos botões para alterar o nível de detalhe exibido no relatório.) e área para soltar
(área para soltar: uma área em um relatório de tabela dinâmica ou de gráfico dinâmico na
qual é possível soltar campos da caixa de diálogo Lista de Campos para exibir os dados no
campo. Os rótulos em cada área para soltar indicam os tipos de campos que podem ser
criados no relatório.) para impressão ou publicaçãona Web.
177
Formatação
Alguns tipos de formatação são perdidos após você alterar o layout ou atualizar (atualizar:
atualizar o conteúdo de um relatório de tabela dinâmica ou de gráfico dinâmico para
refletir alterações nos dados de origem subjacentes. Se o relatório se basear em dados
externos, a atualização executará a consulta subjacente para recuperar dados novos ou
alterados.) um relatório de gráfico dinâmico. Esses tipos de formatação incluem linhas de
tendência (linha de tendência: uma representação gráfica de tendências em séries de
dados, como uma linha inclinada para cima para representar um aumento de vendas em
um período de meses. As linhas de tendência são usadas para o estudo de problemas de
prognóstico, também denominado análise de regressão.) e barras de erros (barras de
erros: normalmente usadas em dados estatísticos ou científicos, mostram um possível erro
ou grau de incerteza em relação a cada marcador de dados em uma série.), alterações em
rótulos de dados (rótulo de dados: um rótulo que fornece informações adicionais sobre
um marcador de dados, que representa um único ponto de dados ou valor que se origina
em uma célula da planilha.) e alterações em séries de dados. Os gráficos normais não
perdem essa formatação após ela ter sido aplicada.
Movendo ou redimensionando itens
Em um relatório de gráfico dinâmico, é impossível mover ou redimensionar a área de
plotagem (área de plotagem: em um gráfico 2D, a área delimitada pelos eixos, incluindo
todas as séries de dados. Em um gráfico 3D, a área delimitada pelos eixos, incluindo a
série de dados, os nomes de categorias, os rótulos de marca de escala e os títulos de
eixos.), legenda, títulos de gráfico ou títulos de eixo, embora você possa selecionar uma
entre diversas posições predefinidas para a legenda e possa alterar o tamanho da fonte de
títulos. Em um gráfico normal, você pode mover e redimensionar todos esses elementos.
Criando um relatório de gráfico dinâmico
Começando um relatório de tabela dinâmica
Certifique-se de que o relatório da tabela dinâmica tem pelo menos um campo de linha
(campo de linha: um campo ao qual foi atribuída uma orientação de linha em um relatório
de tabela dinâmica. Os itens associados ao campo de linha são exibidos como rótulos de
linha)., para se tornar o campo de categoria no relatório de gráfico dinâmico, e um campo
de coluna (campo de coluna: um campo ao qual foi atribuída uma orientação de coluna
em um relatório de tabela dinâmica. Os itens associados ao campo de coluna são exibidos
como rótulos de coluna.) para se tornar o campo de série. Se o relatório da tabela
dinâmica estiver em formato recuado, mova pelo menos um campo para a área de coluna
antes de criar o gráfico.
Começando a partir do zero
No Assistente de tabela dinâmica e gráfico dinâmico, especifique o tipo de dados de
origem (dados de origem: a lista ou a tabela usada para criar um relatório de tabela
dinâmica ou de gráfico dinâmico. Os dados de origem podem ser obtidos de uma lista ou
intervalo do Excel, de um banco de dados ou cubo externo, ou de outro relatório de
tabela dinâmica.) a ser usado e defina as opções para como os dados serão utilizados.
178
Em seguida, crie o layout do relatório de gráfico dinâmico de maneira semelhante a de um
relatório de tabela dinâmica. Se a sua pasta de trabalho não contiver um relatório de
tabela dinâmica, o Microsoft Excel criará uma quando você criar o relatório de gráfico
dinâmico. Quando você alterar esse relatório, seu relatório da tabela dinâmica associado
(relatório de tabela dinâmica associado: o relatório de tabela dinâmica que fornece os
dados de origem ao relatório de gráfico dinâmico. É criado automaticamente quando você
cria um novo relatório de gráfico dinâmico. Quando você altera o layout de um relatório, o
outro também é alterado.) será alterado e vice-versa.
Personalizando o gráfico
Em seguida, você altera o tipo de gráfico e outras opções — como os títulos (títulos em
gráficos: um texto descritivo alinhado automaticamente a um eixo ou centralizado na
parte superior de um gráfico.), o local da legenda (legenda: uma caixa que identifica os
padrões ou cores atribuídos às séries de dados ou categorias de um gráfico.), os rótulos
de dados (rótulo de dados: um rótulo que fornece informações adicionais sobre um
marcador de dados, que representa um único ponto de dados ou valor que se origina em
uma célula da planilha.), o local do gráfico e assim por diante — usando o Assistente de
gráfico e os comandos do menu Gráfico.
Quando usar campos de página
O uso de campos de página é uma forma conveniente de resumir e enfatizar rapidamente
um subconjunto de dados sem precisar modificar suas informações de série e categoria.
Por exemplo, se você estiver fazendo uma apresentação, poderá clicar em (Tudo) no
campo de página Ano para mostrar as vendas referentes a todos os anos e, depois,
enfatizar anos específicos, clicando em um ano de cada vez. As páginas do gráfico têm o
mesmo layout de categoria e série para os diferentes anos, para que os dados de cada
ano possam ser comparados facilmente. Além disso, ao permitir que você recupere uma
página de cada vez de um grande conjunto de dados, os campos de página conservam
memória quando seu gráfico usa dados de origem externa.
Funções de Banco de Dados
Nesse capítulo você vai aprender a utilizar funções de banco de dados para manipular
informações em planilhas com características de banco de dados.
Utilizando as funções BDSOMA e BDMÉDIA
Propósito:
Utilizar as funções BDSOMA e BDMÉDIA.
179
Meta:
Abrir a pasta de trabalho Relatório Produtos.xlsx.
Preencher um relatório com o valor total de vendas efetuadas por fornecedor.
Preencher um relatório com o valor total de vendas efetuadas por fabricante.
Preencher um relatório com o valor da média de vendas efetuadas por fornecedor.
Preencher um relatório com o valor da média de vendas efetuadas por fabricante.
A pasta de trabalho Relatório Produtos.xlsx contém informações sobre itens fornecidos
por vários fornecedores. Seu trabalho será gerar relatórios para resumir e consolidar
informações sobre os fornecedores e fabricantes dos produtos.
Função BDSOMA
Essa função tem como objetivo somar os valores de uma coluna de acordo com critérios
estabelecidos.
Sintaxe: BDSOMA(banco de dados;campo;critérios)
Argumentos:
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados que serão somados. Pode
ser indicada pelo nome do campo entre aspas, o endereço da célula que
contém o nome da coluna ou pelo número da coluna dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
1. Abra a pasta de trabalho Relatório Produtos.xlsx.
2. Planilha: Controle de Produtos.
180
3. Planilha: Relatório.
181
4. Planilha: Critérios.
5. O primeiro passo é somar os valores de venda por fornecedor, para isso você vai criar
seus critérios para utilizá-los com a função BDSOMA. Selecione a planilha Critérios.
6. Como se deseja efetuar a soma por fornecedor, os critérios devem conter a
identificação do nome do campo (FORNECEDOR) e o conteúdo que se deseja procurar
no campo. Digite as informações exibidas na imagem:
CUIDADO! Tanto o nome do campo quanto o conteúdo a ser procurado devem estar
digitados exatamente como na planilha onde se fará a procura. É muito comum, durante a
digitação, inserir um espaço em branco a mais ou mesmo pressionar alguma tecla que não
gera caractere imprimível e, na hora da procura, não se obter nenhum resultado. Se isso
acontecer, digite novamente as informações dos critérios.
182
7. Selecione a planilha Relatórioe clique na célula B4. Você vai calcular a soma de
vendas para o fornecedor 3 Manos Hipermercados.
8. Digite a seguinte fórmula =BDSOMA(.
9. O primeiro argumento refere-se à região da planilha que será considerada como o
banco de dados. Selecione a planilha Controle de Produtos e selecione o intervalo
A1:D24.
10. Digite a seguinte fórmula =BDSOMA(‘Controle de Produtos’!A1:D24;.
11. O próximo argumento é o nome do campo que será somado. No caso, é o campo
Total e que está na célula D1 da planilha Controle de Produtos.
12. Digite a seguinte fórmula =BDSOMA(‘Controle de Produtos’!A1:D24;’Controle de
Produtos’!D1;.
13. O último parâmetro é o critério para efetuar a soma. Nesse caso o critério é:
Fornecedor igual a 3 manos Hipermercados. Esse critério está indicado na planilha
Critérios na região A4:A5.
14. Digite a seguinte fórmula =BDSOMA(‘Controle de Produtos’!A1:D24;’Controle de
Produtos’!D1;;Critérios!A4:A5)
15. Preencha a coluna SOMA do relatório por fornecedor calculando a soma para os
outros fornecedores.
Função BDMÉDIA
Essa função tem como calcular a média aritmética entre os valores de uma coluna de
acordo com critérios estabelecidos.
Sintaxe: BDMÉDIA(banco de dados;campo;critérios)
Argumentos:
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados para os quais a média será
calculada. Pode ser indicada pelo nome do campo entre aspas, o endereço da
célula que contém o nome da coluna ou pelo número da coluna dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
183
1. Agora você vai calcular a média de vendas para cada fornecedor. Para facilitar, você vai
atribuir um nome à região da planilha Controle de Produtos que corresponde ao
banco de dados, um nome à célula que contém a indicação do campo onde será
efetuado o cálculo e nomes às regiões que contêm os critérios.
2. Selecione a região A1:D24 da planilha Controle de Produtos e atribua o nome Geral.
Vá Caixa de Nome à esquerda da Barra de Fórmulas e digite o nome Geral para a
área selecionada.
3. Selecione a célula D1 da planilha Controle de Produtos e atribua o nome Coltotal.
4. Selecione a região A1:A2 da planilha Critérios e atribua o nome Carretel.
5. Selecione a região A4:A5 da planilha Critérios e atribua o nome Manos.
6. Selecione a região A7:A8 da planilha Critérios e atribua o nome Doces.
7. Selecione a região A10:A11 da planilha Critérios e atribua o nome Flores.
8. Selecione a planilha Relatório e clique na célula C4. Você vai calcular a média de
vendas para o fornecedor 3 Manos Hipermercados.
184
9. Digite a fórmula: =BDMÉDIA(Geral;Coltotal;Manos).
Onde:
Geral Identificação do banco de dados.
Coltotal Célula que contém o título da coluna onde será calculada a média.
Manos Critério.
10. Calcule a média de vendas para os outros fornecedores.
11. Agora você vai calcula a soma por fabricante. Na planilha Critérios, digite os critérios
para os fabricantes:
12. Nomeie os critérios: garoto, granja, maeterra, nestle, parmalat, renata, royal e
uniao.
13. Selecione a planilha Relatório e clique na célula B13.
14. Digite a fórmula: =BDSOMA(Geral;Coltotal;garoto).
15. Calcule a soma para os outros fabricantes.
16. Seguindo o mesmo raciocínio utilizado para os fornecedores, calcule a média de
vendas para os fabricantes.
185
Função BDMÁX
Essa função tem como objetivo retornar o valor máximo dentro de uma lista de valores de
acordo com as condições especificadas.
Sintaxe: BDMÁX(banco de dados;campo;critérios)
Argumentos:
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados para os quais o valor
máximo será calculado. Pode ser indicada pelo nome do campo entre aspas, o
endereço da célula que contém o nome da coluna ou pelo número da coluna
dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
Função BDMÍN
Essa função tem como objetivo retornar o valor mínimo dentro de uma lista de valores de
acordo com as condições especificadas.
Sintaxe: BDMÍN(banco de dados;campo;critérios)
Argumentos:
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados para os quais o valor
mínimo será calculado. Pode ser indicada pelo nome do campo entre aspas, o
endereço da célula que contém o nome da coluna ou pelo número da coluna
dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
1. Selecione a planilha Relatório e clique na célula D4.
2. Os parâmetros da função BDMÁX são os mesmos das funções BDSOMA e BDMÉDIA.
3. Digite a fórmula: =BDMÁX(Geral;Coltotal;Manos).
4. Calcule o valor máximo de vendas para os outros fornecedores.
5. Calcule o valor máximo de vendas para os fabricantes.
6. Agora calcule o valor mínimo de vendas para os fornecedores.
7. Clique na célula F4 e digite a fórmula: =BDMÍN(Geral;Coltotal;Manos).
8. Calcule o valor mínimo de vendas para os outros fornecedores.
186
9. Calcule o valor mínimo de vendas para os outros fabricantes.
Utilizando a função BDEXTRAIR
Propósito:
Utilizar as funções BDEXTRAIR.
Meta:
Preencher um relatório com o nome dos produtos que apresentam o valor máximo de
vendas efetuadas por fornecedor.
Preencher um relatório com o nome dos produtos que apresentam o valor máximo de
vendas efetuadas por fabricante.
Preencher um relatório com o nome dos produtos que apresentam o valor mínimo de
vendas efetuadas por fornecedor.
Preencher um relatório com o nome dos produtos que apresentam o valor mínimo de
vendas efetuadas por fabricante.
Sua planilha Relatório mostra os valores máximno e mínimo de vendas por fornecedor e
por fabricante. No entanto você precisa também saber quais são os produtos responsáveis
por essas vendas. Para isso você utilizará a função BDEXTRAIR.
Função BDEXTRAIR
Essa função tem como objetivo retornar um único valor em uma coluna de uma lista que
coincida com as condições especificadas.
Sintaxe: BDMÍN(banco de dados;campo;critérios)
Argumentos:
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados para extração do valor
desejado. Pode ser indicada pelo nome do campo entre aspas, o endereço da
célula que contém o nome da coluna ou pelo número da coluna dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
1. Você vai procurar no banco de dados pelos produtos responsáveis pelas maiores e
menores vendas por fornecedor. O seu critério agora é selecionar o fornecedor e o
valor da sua maior venda. Para isso você deve modificar sua planilha de critérios.
Selecione a planilha Critérios e clique na célula B1.
187
2. Nessa célula você vai digitar o nome do campo que contém o valor da maior venda do
fornecedor Carretel Hipermercados na planilha Controle de Produtos. Esse campo é
o campo de nome Total. Portanto, na célula B1 digite: Total.
3. Selecione a célula B2. Nessa célula você vai indicar o valor da maior venda efetuada
pelo fornecedor Carretel Hipermercados. Esse valor está sendo calculado na planilha
Relatório na célula D5. Portanto, ba célula B2 da planilha Critérios, digite:
=Relatório!D5.
4. Modifique os critérios para os outros fornecedores.
5. Selecione a planilha Relatório e cliquena célula E4.
6. Digite a fórmula: =BDEXTRAIR(Geral;Controle de Produtos!A1;Critérios!A4:B5).
Note que a coluna a ser considerada nessa função é a que contém o nome do produto na
planilha Controle de Produtos (célula A1), e a região que indica o critério agora contém
o campo Valor.
7. Mostre os nomes dos produtos de maior valor para todos os fornecedores.
8. Modifique a planilha Critérios para que você possa exibir os nomes dos produtos de
menor valor para todos os fornecedores:
188
9. Preencha a planilha Relatório procurando pelos produtos de menor valor.
10. Utilize o mesmo raciocínio para extrair os nomes dos produtos de maior e menor valor
para cada fabricante. Construa os critérios na planilha Critérios.
Se a funçã BDEXTRAIR retornar o erro #Valor!, significa que o valor produrado não foi
encontrado; se retornar o erro #Num!, significa que existe mais de um valor que atende o
critério.
189
Utilizando a função BDCONTARA
Propósito:
Utilizar as função BDCONTARA.
Meta:
Preencher um relatório com o número de produtos por fornecedor.
Preencher um relatório com o número de produtos por fabricante.
Função BDCONTARA
Essa função tem como contar as células não vazias em uma coluna de uma lista de acordo
com as condições especificadas.
Sintaxe: BDCONTARA(banco de dados;campo;critérios)
Argumentos:
190
o banco de dados: intervalo de células compondo uma lista com os dados
disposotos em linhas e colunas. A primeira linha dessa lista contém os rótulos
(nomes) de cada coluna;
o campo: identificação da coluna que contém os dados para extração do valor
desejado. Pode ser indicada pelo nome do campo entre aspas, o endereço da
célula que contém o nome da coluna ou pelo número da coluna dentro da lista.
o critérios: intervalo de células que contém a condição especificada.
1. Selecione a planilha Relatório.
2. Clique na célula H4.
3. Os parâmetros da função BDCONTARA são os mesmos das funções anteriores. Logo,
digite na célula H4 a fórmula =BDCONTARA(Geral;’Controle de
Produtos’!$B$1;Manos).
4. Preencha o relatório contando o número de produtos para os demais fornecedores e
fabricantes.
191
Criando e Gerenciando Cenários
Propósito:
Apresentar o recurso Gerenciador de Cenários para realize simulações..
Meta:
Formular hipóteses para analisar os efeitos nos saldos ao se alterarem as entradas e as
saídas.
O Gerenciador de Cenários se encontra em Dados, Teste de Hipóteses. Clique na guia
Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida,
clique em Gerenciador de Cenários.
Você irá realizar a simulação de variações para as planilhas analisando cenários prováveis.
Você criará um primeiro cenário com os dados atuais da planilha para poder recuperar
estes dados no futuro.
Em seguida você criará um cenário prevendo aumento de 25% nos itens de Entrada de
Janeiro, aumento de 15% nas Saídas de Fevereiro e queda de 10% nas Saídas de Março.
1. Abra o arquivo Cenários.xlsx.
2. Vá para a planilha Janeiro.
3. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia
Dados.
192
4. Escolha Gerenciador de Cenários... A lista aparece vazia pois nenhum cenário foi
criado ainda. Clique no botão Adicionar.
5. Preencha o campo Nome do Cenário com Dados Originais.
Você deve selecionar as células variáveis. São as células que terão seus valores alterados
para análise de resultados.
As células selecionadas não devem conter fórmulas, pois quando um cenário é aplicado as
fórmulasserão substituídas por valores.
6. Selecione os intervalos B3:B4 e B6:B9, no campo Células Variáveis.
193
7. Preencha o campo Comentários como achar necessário.
8. Clique em OK.
9. As células variáveis aparecem para receber novos valores. Como a ideia é recuperar os
valores atuais, eles não serão alterados.
194
10. Clique em OK para que o cenário seja criado com os valores atuais.
11. Clique em Adicionar.
12. Preencha o campo Nome do cenário com Aumento de 25% nas Entradas de
janeiro.
13. Mantenha as mesmas células vazias.
14. Preencha o campo Comentário.
195
15. Clique em OK.
16. Clique em OK.
17. Digite a fórmula que representa o cálculo de acréscimo de 25% ou digite o valor já
calculado.
18. Clique em OK.
196
Surge um quadro avisando que as fórmulas foram convertidas nos valores correspondentes
ao cálculo.
O Gerenciador de Cenários agora apresenta os cenários que já foram criados.
19. Selecione o cenário que quer analisar e clique no botão Mostrar.
197
Para alterar os valores de um cenário clique no botão Editar... e modifique os valores das
células variáveis. Clique em Excluir para eliminar um cenário.
Agora você criará novo cenário com a hipótese de aumento de 15% nas Saídas de
feveriro. Lembre-se de criar um cenário para recuperar os dados originais.
Atingir Meta
Propósito:
Apresentar o recurso Atingir Meta que permite formular hipóteses de variação de um
valor para que uma fórmula apresente um determinado resultado.
Meta:
Calcular quanto um item das entradas deve variar para alcançar um saldo positivo.
Se você conhece o resultado que deseja obter de uma fórmula, mas não tem certeza
sobre o valor de entrada necessário para chegar a esse resultado, use o recurso Atingir
Meta. Por exemplo, suponha que você precise de algum dinheiro emprestado. Você sabe
o quanto precisa, por quanto tempo deseja pagar o empréstimo e quanto pode pagar por
mês. Nesse caso, você pode usar Atingir Meta para determinar que taxa de juros precisa
fixar para atender à sua meta de empréstimo.
Observação - Atingir Meta funciona somente com um valor de entrada de variável. Se
quiser aceitar mais de um valor de entrada; por exemplo, o valor do empréstimo e o valor
do pagamento mensal de um empréstimo, use o suplemento Solver.
1. Abra o arquivo Metas.xlsx.
198
Nos meses de abril e junho os saldos são negativos, indicando que as Entradas, que
representam os valores que empresa prevê receber, não consiguirão superar as saídas,
que representam os compromissos da empresa. Para ela captar recursos e aumentar a
entrada de recursos será preciso realizar o desconto de duplicatas nesses meses para fazer
frente aos compromissos. Você usará o recurso Atingir Meta para definir quanto o item
Duplicatas – Desconto deve alcançar.
2. Clique no botão Testes de Hipóteses do grupo Ferramentas de Dados da guia
Dados. Escolha Atingir Meta.
3. No quadro Atingir Meta informe a célula E13 no campo Definir célula.
4. Em Para valor: digite 222000 que é o valor a ser alcançado por E13.
5. Defina a célula E7 como célula variável em Alternando célula:
199
6. Clique em OK.
200
A célula E7 (Duplictas – Desconto) terá seu valor alterado para que a célula E13 (soma das
entradas) alcance 222.000. Se você clicar em OK esses valores será assumidos. Se você
clicar em Cancelar os valores anteriores permanecerão.
Exemplo 1
Use Atingir Meta para determinar a taxa de juros
1. Na guia Dados no grupo Ferramentas de Dados, clique em Hipóteses e, em seguida,
clique em Atingir Meta.
2. Na caixa Definir célula, insira a referência para a célula que contém a fórmula que
você deseja resolver. No exemplo, essa referência é a célula B4.
3. Na caixa Para valor, digite o resultado da fórmula desejado. No exemplo, esse valor é
-900. Observe que o número é negativo porque representa um pagamento.
4. Na caixa Por alteração, insira a referência para a célula que contém o valor que você
deseja ajustar. No exemplo, essa referência é a célula B3.
Observação - A célula alterada por Atingir Meta deve ser referenciadapela fórmula na
célula que você especificou na caixa Definir célula.
5. Clique em OK.
O recurso Atingir Meta é executado e gera um resultado, como mostra a ilustração a
seguir.
Exemplo 2
O recurso Atingir Meta no Excel, tem por finalidade alterar o valor de uma célula para
que uma fórmula atinja determinado valor.
Por exemplo, você tem uma prestação, sabe o quanto emprestou, em quantas parcelas, e
o quanto paga por parcela, porém gostaria de saber a taxa aplicada no seu empréstimo.
201
Para isso vamos fazer uma planilha:
1. Crie a seguinte planilha com os dados fixos nas células C8, C9 e C10.
2. Na célula H9, digite a seguinte fórmula: =D10/12.
3. Na célula G11 digite a seguinte fórmula: =PGTO(C9/12;C10;-C8), esta fórmula é
utilizada para definir o valor de uma parcela utilizando o método PRICE. Os
parâmetros destas funções são:
o Taxa: taxa de juros para o empréstimo.
o Nper: número total de pagamentos pelo empréstimo.
o VP: valor principal, ou o valor emprestado.
o VF: valor futuro, ou o saldo que se deseja ter ao final dos períodos, se omitido
será 0.
o Tipo: indica o vencimento das parcelas. 0 no final do período e 1 no início do
período.
Utilizando o Atingir Meta:
1. Na guia Dados, grupo Teste de Hipóteses, comando Atingir Meta.
2. Na tela que segue selecione no campo Definir célula o endereço G11, esta célula
define aonde está a fórmula na qual quer se atingir meta.
3. Para valor: digite 1400, o valor que se pretende atingir.
4. Alternando célula: selecionar a célula G9. Este campo será alterado para se atingir o
valor desejado.
202
5. Pressionando o botão OK, você notará que o valor da célula C9 irá ser alterado, assim
como o resultado da fórmula do pagamento na célula C11, descobrindo desta forma a
taxa de juros que está incidindo sobre o valor emprestado.
Exemplo 2
Na minha empresa fictícia, a análisedo meu resultado em Janeiro de 2013, o total de
vendas do mês foi de R$ 50.000,00. A minha folha de pagamento foi de R$ 12.126,00 e
os seus encargos de 78,12% sobre a folha foram de R$ 9.472,83. O meu custo fixo (luz,
aluguel, água, etc.) é de R$ 4.523,65.
Como resultado, o lucro bruto é de R$ 23.877,52. O Imposto de Renda é de 25% sobre o
Lucro Bruto, que é de R$ 5.969,38.
O meu Resultado Líquido, descontando-se tudo é de R$ 17.908,14.
A questão é; quero aumentar meu Resultado Líquido para R$ 25.000,00. Quanto teria que
aumentar o meu faturamento?
Poderia ficar tentando modificar o valor contido na célula D3, experimentando valores, até
que o meu Resultado Líquido atingisse R$ 25.000,00.
A outra forma é através do Excel fazer uso do recurso Atingir Meta, que fizesse isso por
mim. Posicionado na célula do conteúdo do Resultado, D11 (o conteúdo da célula que
pretende-se alcançar a meta deve conter obrigatoriamente uma fórmula); na guia Dados,
grupo Teste de Hipóteses, comando Atingir Meta.
203
Na caixa de diálogo – Atingir Meta insira as informações solicitadas.
Se concordarmos com o resultado, clica-se OK e a célula D3 assumirá o novo valor, se não
quisermos consolidar esse valor clicamos em Cancelar, e tudo volta a ser igual como era
antes.
O total de vendas do mês terá de ser R$59.455,81 para obtermos um Resultado de
Líquido de R$25.000,00.
204
Solver
Propósito:
Apresentar o recurso Solver que permite formular hipóteses.
Meta:
Formular hipóteses de alteração de valores em uma planilha, aplicando restrições
conhecidas.
O Solver faz parte de um conjunto de programas algumas vezes chamado de ferramentas
de análise hipotética. Com o Solver você pode localizar um valor ideal para uma fórmula
em uma célula — chamada de célula de destino — em uma planilha. O Solver trabalha
com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de
destino. O Solver ajusta os valores nas células variáveis que você especificar — chamadas
de células ajustáveis — para produzir o resultado especificado por você na fórmula da
célula de destino. Você pode aplicar restrições para restringir os valores que o Solver
poderá usar no modelo e as restrições podem se referir a outras células que afetem a
fórmula da célula de destino.
Use o Solver para determinar os valores máximo e mínimo de uma célula pela alteração
de outras células.
O recurso Solver consiste em um complemento que precisa ser carregado para que você
possa usá-lo. Carregue este complemento.
1. Abra o arquivo Solver.xlsx.
205
2. Clique na guia Arquivo.
3. Clique em Opções.
4. Clique em Suplementos.
5. Certiique-se que no campo Gerenciar: está selecionada a opção Suplementos do
Excel e clique no botão Ir ...
206
6. Marque a opção Solver e clique em OK.
7. Na guia Dados, grupo Análise, comando Solver.
207
A pasta Solver.xlsx apresenta o cálculo que definirá o número de passageiros que
determinado avião pode carregar de acordo com algumas restrições conhecidas.
O problema é calcular o número de passageiros que um avião pode levar em uma viagem.
O peso total desse tipo de avião não pode ultrapassar 1.157 Kg. Para a viagem pretendida
ele consome 182 Kg de combustível, levará 91 Kg no bagageiro e deve-se considerar que
nos 2 assentos dianteiros permite um peso máximo de 154 Kg. Um destes assentos é
ocupado pelo piloto. Para cálculo do peso das pessoas a bordo é considerado o valor de
77 Kg. O avião vazio pesa 635 Kg.
Desta forma você tem as seguintes restrições a considerar:
Peso do avião vazio =635 Kg É sempre o mesmo.
Assentos dianteiros >=77 Kg e <=154
Kg
Pelo menos um assento ocupado pelo
piloto.
Combustível =182 Kg Necessário para a viagem.
Bagageiro =91 Kg Peso da bagagem.
Passageiros ? Valor a ser encontrado. Cada passageiro
=77 Kg.
Peso máximo
permitido
<=1.157 Kg A soma dos itens acima não pode
ultrapassar esse limite.
Quantos passageiros pode-se transportar respeitando estes limites e maximizando o
pessoal disponível?
8. Clique na guia Dados. Agora existe um novo grupo chamado Análise.
9. Clique em Solver.
A célula B7 apresenta o cálculo do peso total e quanto maior for esse valor maior será o
número de passageiros que poderá ser transformado. Portanto, voc~e definirá esta célula
como objetivo a ser maximizado.
10. No campo Definir Objetivo: selecione a célula B7.
11. No campo Alterando células variáveis: selecione o intervalo B2:B6.
12. Clique no botão Adicionar para definir as restrições.
13. No campo Referência de Célula: selecione A11.
14. Escolha o operador =.
15. No campo restrição: selecione C11.
208
16. Clique em Adicionar.
17. Repita os passos 13 a 15 para as restrições do quadro abaixo:
A12 >= C12
A12 <= E12
A13 = C13
A14 = C14
A15 <= C15
18. Após a última restrição clique em OK ao invés de Adicionar.
209
19. Clique no botão Resolver.
Caso haja conflito entre as restrições e as opções escolhidas o Solver apresentará uma
mensagem informando que não encontrou uma solução.
210
20. Selecione os relatórios Resposta, Sensibilidade e Limites.
21. Caso os valores apresentados sejam satisfatórios, marque a opção Manter Solução do
Solver. Caso contrário, marque Restaurar Valores Originais.
22. Clique em OK.
Você pode transformar a resposta do Solver em um Cenário. Para isto, clique no botão
Salvar Cenário...
Modos de Exibição
Vá a guia Exibição, grupo Modos de Exibição de Pasta de Trabalho, neste grupo você
tem acesso as seguintes opções – Modo Normal, Visualização da Quebra de Página,
Modo de Exibição de Layout de página e Modo de Exibição de Pasta de Trabalho.
211
O que é o Modo de Exibição Protegido?
Arquivos da Internet e de outros locais potencialmente não seguros podem conter vírus,
worms ou outros tiposde malware, que podem danificar seu computador. Para proteger o
seu computador, os arquivos desses locais potencialmente inseguros são abertos no
Modo de Exibição Protegido. Com o uso desse modo, você pode ler um arquivo e ver o
seu conteúdo, reduzindo os possíveis riscos.
Por que meu arquivo é aberto no Modo de Exibição Protegido?
O Modo de Exibição Protegido é um modo somente de leitura no qual a maioria das
funções de edição está desabilitada. Há vários motivos pelos quais um arquivo é aberto no
Modo de Exibição Protegido:
O arquivo foi aberto de um local da Internet – Quando você encontrar a mensagem
Este arquivo foi originado de um local da Internet e pode não ser seguro. Clique
para obter mais detalhes. no Modo de Exibição Protegido, o arquivo está sendo
aberto da Internet. Arquivos da Internet podem conter vírus e outros tipos de
conteúdo nocivo inseridos. É recomendável editar o documento somente se você
confiar em seu conteúdo.
O arquivo foi recebido como um anexo do Outlook e a política do seu
computador definiu o remetente como não seguro - Quando você receber a
mensagem Este arquivo foi originado como um anexo de email e pode não ser
seguro. Clique para obter mais detalhes. no Modo de Exibição Protegido, o arquivo
foi recebido de um remetente possivelmente não seguro.
O arquivo foi aberto de um local não seguro - Quando você receber a mensagem
Este arquivo foi aberto de um local potencialmente não seguro. Clique para obter
mais detalhes. no Modo de Exibição Protegido, o arquivo foi aberto de uma pasta
que não é segura. Um exemplo de um local inseguro é a pasta Arquivos de Internet
Temporários.
O arquivo é bloqueado pelo Bloqueio de Arquivo - O que é o Bloqueio de Arquivo?
As mensagens a seguir são exemplos:
212
Não é permitida a edição.
A edição é permitida.
Falha de validação de arquivo - Quando você encontrar a mensagem O Office
detectou um problema com este arquivo. Editá-lo poderá prejudicar o
computador. Clique para obter mais detalhes. no Modo de Exibição Protegido. O
arquivo não passou na validação de arquivo.
O arquivo foi aberto no Modo de exibição Protegido com o uso da opção Abrir
no Modo de Exibição Protegido - Quando você vir a mensagem Este arquivo foi
aberto no Modo de Exibição Protegido. Clique para obter mais detalhes. no Modo
de Exibição Protegido, você optou por abrir esse arquivo no Modo de Exibição
Protegido. Isso pode ser feito usando a opção Abrir no Modo de Exibição Protegido:
1. Clique em Arquivo, Abrir.
2. Na caixa de diálogo Abrir, clique na seta ao lado do botão Abrir.
3. Na lista exibida, clique em Abrir no Modo de Exibição Protegido.
Importante - Os administradores podem estender a lista de locais potencialmente não
seguros para incluir pastas adicionais também consideradas não seguras.
Explicação das configurações do Modo de Exibição Protegido
Habilitar Modo de Exibição Protegido para arquivos que falham na validação - A
validação de arquivo verifica o arquivo em busca de problemas de segurança que
possam resultar de alterações na estrutura do arquivo. Desmarcar essa opção não
desativa a validação. Em vez disso, uma mensagem de erro é exibida para arquivos nos
quais o processo de validação falha.
Habilitar o Modo de Exibição Protegido para arquivo provenientes da Internet -
A Internet é considerada um local não seguro por causa das muitas oportunidade de
intenções maliciosas.
213
Habilitar o Modo de Exibição Protegido para arquivos que se encontram em
locais possivelmente não seguros - Essa mensagem se refere a pastas no
computador ou na rede que são consideradas não seguras, como a pasta Arquivos de
Internet Temporários ou outras pastas atribuídas pelo seu administrador.
Habilitar o Modo de Exibição Protegido para anexos do Outlook - Os anexos em
emails podem vir de fontes desconhecidas ou não confiáveis.
Habilitar o modo DEP (Prevenção de Execução de Dados).
Criando Formulários Personalizados
Usando formulários e os diversos controles e objetos que podem ser adicionados a eles, é
possível facilitar a entrada de dados nas suas planilhas e melhorar sua aparência. E você
pode fazer isso você mesmo, com pouca ou nenhuma necessidade de código do
Microsoft Visual Basic for Applications (VBA).
Uma formulário de planilha não é a mesma coisa que um modelo do Excel. Um modelo é
um arquivo pré-formatado para você começar a criar uma pasta de trabalho com a
aparência que desejar. Um formulário contém controles, como caixas ou listas suspensas,
que podem facilitar a inserção ou edição de dados para as pessoas que usam sua planilha.
O que é um formulário?
Formulários de planilha contêm controles. Dentre alguns exemplos de controles comuns
estão caixas de listagem, botões de opção e botões de comando. Controles também
podem executar macros (macro: uma ação ou um conjunto de ações que você pode usar
para automatizar tarefas. As macros são gravadas na linguagem de programação Visual
Basic for Applications.) atribuídas e responder a eventos, como cliques do mouse, por
meio da execução de um código do Visual Basic for Applications (VBA) (Visual Basic for
Applications (VBA):uma versão de linguagem macro do Microsoft Visual Basic usada para
programar aplicativos do Microsoft Windows e incluída em vários programas da
Microsoft.).
Você pode usar o Excel de várias maneiras para criar formulários.
214
Tipos de formulários do Excel
Existem vários tipos de formulários que você pode criar no Excel: formulários de dados,
planilhas que contêm controles de formulário e ActiveX e UserForms do VBA. Você pode
usar cada tipo de formulário individualmente ou pode combiná-los de maneira diferente
para criar uma solução que seja ideal para você.
Formulário de Dados;
Planilha com Controles de Formulário e ActiveX;
Controles de Formulários;
Controles ActiveX.
Automação de Planilhas
A seguir veremos o desenvolvimento de uma planilha para pesquisas de dados sobre
veículos a venda, introduzindo alguns controles que facilitarão a escolha do veículo.
Também serão criadas macros e fórmulas necessárias para interagir com os controles.
Você utilizará o arquivo Automação.xlsx que contém dados digitados e alguns intervalos
foram nomeados para facilitar a utilização de alguns recursos:
o Intervalo A4:I56 da planilha Origem dos dados: nomeado tabela – contém todos
os veículos e suas características a serem pesquisados.
o Intervalo B1:B2 da mesma planilha: nomeado modelo – contém o critério a ser
utilizado para filtrar os modelos de uma determinada marca.
o Célula B1 da planilha Modelos: nomeada título – contém o rótulo do campo a ser
retornado ao filtrar os modelos.
Criar controles para escolha de dados
Propósito:
Utilizar os controles de formulário para facilitar a pesquisa de dados.
Meta:
Criar os controles para escolha de opções.
Nesta atividade você utilizará o arquivo nAutomação.xlsx. Você introduzirá na planilha
Pesquisa os primeiros controles para fazer as escolhas básicas da pesquisa. Nas próximas
atividades você complementará com os recursos necessários para tornar esses controles
funcionais.
1. Verifiue se a guia Desenvolvedor está presente na Faixa de Opções. Caso não esteja
presente, acesse a guia Arquivo, escolha Opções. Escolha o comando Personalizar
215
Faixa de Opções e na lista de guias à direita marque a caixa de seleção
Desenvolvedor.
2. Clique em OK.
3. Abra o arquivo Automação.xlsx e acesse a planilha Pesquisa.
4. Vá para a guia Desenvolvedor. No grupo Controles, clique o comando Inserir.
216
5. Clique no comando Caixa de Grupo e desenhe um retângulo abaixo do texto
Pesquisa de Veículos.
6. Mantendo o controle selecionado, digite seu título: Veículos.
7. Clique o comando Inserir, acione o controle Botão de opção e desenhe um retângulo
dentro da caixa de Grupo Veículos.8. Mantendo o controle selecionado digite Novos. E, em seguida, clique no comando
Propriedades do grupo Controles e defina G5 como Vínculo da célula e marque a
caixa de seleção Sombreamento 3D. Vínculo de Célula é o endereço que recebe o
número do botão de opção selecionado: 1 para Novos e 2 para Usados.
9. Clique em OK.
10. Repita os passos 7, 8 e 9 para criar o Botão de Opção Usados. Defina G5 como vínculo
da célula também para esta opção. Os botões de opções criados são identificados
pelo número qu representa sua ordem de criação. Portanto no nosso exemplo o botão
de opção Novos recebeu o número 1 e o botão de opção Usados recebeu o número 2.
Quando voc~e escolher uma das opções ela será representada na célula G5.
217
11. Clique agora no comando Inserir do grupo Controles para introduzir uma Caixa de
Combinação. Esta caixa de combinação exibirá as marcas disponíveis para pesquisa.
Essas informações estão na coluna A da planilha Marcas.
12. Desenhe o retângulo abaixo da palavra Marca:
13. Mantendo o controle selecionado clique sobre o comando Propriedades.
218
14. O intervalo de entrada representa a lista dos itens que aparecerão suspensos na
Caixa de Combinação. Defina a coluna A da planilha Marcas como intervalo de
entrada. Defina a célula G10 como Vínculo da célula.
15. Teste os controles entre Novos e Usados. Perceba que o número correspondente ao
botão selecionado aparece na célula G5. Clique no botão lateral da Caixa de
combinação. Perceba que o número de ordem do item selecionado aparece na célula
G10.
16. Na célula G11 digite a fórmula: =ÍNDICE(Marcas!A:A;Pesquisa!G10;1).
Esta função busca o conteúdo da coluna A da planilha Marcas que está na linha
representada em G10.
Criação da Macro Modelos
Propósito:
Apresentar somente os modelos correspondetes à marca selecionada.
Meta:
Criar macro que filtre os modelos a serem apresentados. Estes modelos serão exibidos
na Caixa de Combinação a ser criada na próxima atividade.
Nesta atividade voc~e criará uma macro que apresentará somente os modelos da marca
selecionda. Ela incluirá a aplicação do filtro avançado e exclusão de modelos repetidos.
1. Vá para a planilha Origem de Dados. Nela estão todos os dados dos veículos
disponíveis na empresa. Na célula A2 há uma referência ao veículo escolhido, Novo ou
Usado, através da fórmula =Pesquisa!$G$5 e na célula B2 há uma referência à marca
escolhida através da fórmula =Pesquisa!$G$11.
2. Na guia Desenvolvedor acione a opção Gravar Macro do grupo Código.
3. Preencha com o nome Modelo e o campo Descrição com “Cria lista de modelos da
marca selecionada”.
219
Ao pressionar OK os comandos executados daqui por diante serão gravados na macro.
4. Selecione a planilha Modelos.
5. Clique no botão seletor da coluna A e B, depois clique em Delete.
6. Clique em B1 e digite Modelo.
7. Clique em qualquer célula fora da coluna A, B ou C. O recurso Filtro Avançado que
você utilizará possui algumas restrições.
8. Na guia Dados, selecione Avançado do grupo Classificar e Filtrar.
9. Selecione a opção Copiar para outro local e preencha Intervalo da lista com tabela,
intervalo de critérios com modelo e Copiar para um título.
10. Marque a opção Somente registros exclusivos e OK.
220
11. Será criada uma lista semelhante à lista abaixo com os modelos da marca selecionada
no momento.
Você deve copiar os dados para a coluna A retirando o títlo Modelo.
12. Selecione a célula B2, na guia Desenvovedor clique em Usar Referências relativas
do grupo Código.
13. Mantenha pressionada a tecla SHIFT, tecle END, e seta para baixo.
14. Copie com CTRL+C ou outro comando de cópia.
15. Clique novamente em Usar referências relativas para desativar.
16. Cole em A1. Tecle ESC para retirar o pontilhado.
17. Selecione a coluna B e clique em Delete para apagar seu conteúdo.
18. Selecione a planilha Pesquisa.
19. Na guia Desenvolvedor, escolha Parar Gravação do grupo Código.
221
Não se esqueça de parar a gravação. Se você não o fizer, todos os comandos que você usar
na sequência serão gravados e farão parte da macro. Você pode parar a gravação através
do botão Parar gravação no canto inferior esquerdo da barra de status ao lado de Pronto.
20. Quando tentar salvar o arquivo surgirá a mensagem que você perderá a macro se
salvar neste formato (xlsx). Clique em Não.
21. Na guia Arquivo, escolha Salvar Como e defina o tipo de arquivo como Pasta do
Excel Habilitada para Macros e salve nesse formato (xlsm).
222
Criar Controles Complementares à Pesquisa
Propósito:
Utilizar outros controles para pesquisa de dados.
Meta:
Criar Caixa de Listagem para a escolha dos modelos.
Criar Caixa de Listagem para a escolha dos opcionais.
1. Na célula E4 digite a palavra Modelo: e formate.
2. Acesse a guia Desenvolvedor e introduza uma Caixa de listagem abaixo do texto
Modelo:
3. Defina suas propriedades: Intervalo de entrada – coluna A da planilha Modelos,
Vínculo da célula – G8 e Sombreamento 3D. Pressione OK.
223
A célula G8 apresentará um número que indicará a posição do modelo na coluna A.
4. Clique com o botão direito do mouse para selecionar a caixa de combinação das
Marcas.
5. No menu suspenso escolha Atribuir Macro. Atribua a macro Modelo, criada na
atividade anterior. Esta associação fará com que os itens da caixa de combinação
Modelos se alterem quando escolhermos a Marca.
224
6. Selecione a macro e clique em OK.
Agora você vai introduzir as Caixas de Seleção para a escolha dos opcionais.
7. Na planilha Pesquisa, selecione a guia Desenvolvedor, clique em Inserir do grupo
Controles.
8. Clique no controle Caixa de Seleção e desenhe um retângulo abaixo do texto
Opcionais:
9. Mantendo o controle selecionado digite Direção Hidráulica.
225
10. Acesse o comando Propriedade. Defina o Vínculo da célula como $G$13 e
Sombreamento 3D.
11. Repita os passos 5 a 8 para criar as Caixas de Seleção Ar Condicionado, Travas
Elétricas e Vidros Elétricos. Defina os vínculos das células como G15, G17 e G19
respectivamente.
226
12. Teste o funcionamento dos controles.
Criar Critérios para a pesquisa
Propósito:
Criar critérios que serão usados na pesquisa do veículo selecionado.
Meta:
Criar fórmulas necessárias para criar oi inervalo de critérios a ser usado no Filtro
avançado (na próxima atividade).
Abaixo é exibida parte da planilha Origem dos dados de onde serão filtrados de acordo
com os critérios que serão escolhidos na planilha Pesquisa.
O intervalo A1:I2 na planilha Origem dos dados determinará o critério para pesquisa.
1. Na célula C2 também use a função ÍNDICE: =ÍNDICE(Modelos!A:A;Pesquisa!$G$8).
2. Na célula D2 faça referência à célula das planilha Pesquisa que contém a opção
correspondente a Ar Condicionado. Digite á fórmula: =Pesquisa!$G$13.
3. Na célula E2 digite a fórmula =Pesquisa!$G$15.
4. Na célula F2 digite a fórmula =Pesquisa!$G$17.
5. Na célula G2 digite a fórmula =Pesquisa!$G$19.
227
Criar macro Veículos
Propósito:
Criar macro para filtrar dados do veículo selecionado.
Meta:
Criar macro utiloizando o recurso do filtro avançado.
Crie uma macro que utilize o recurso de filtro avançado com o intervalo de critérios
indicado na atividade anterior:
1. Na planilha Origem de dados selecione o intervalo de A1:I12 e dê o nome de
Criterio.
2. Vá para a planilha Pesquisa e na célula I1 digite Resultado da Pesquisa.
3. Selecione a célula I2 copie o intervalo A1:I1 da planilha Origem de dados.
4. Selecione I2:Q2 da planilha Pesquisa e dê o nome de Resultado.
5. Na guia Desenvolvedor, clique em Gravar macro do grupo Código.
6. Preencha o Nome da macro: como Veículose descreva o que ela fará no campo
Descrição.
7. Tecle OK. A gravação da macro inicia.
8. Selecione a célula K6.
9. Na guia Dados, escolha Avançado do grupo Classifica e Filtrar, e marque a opção
Copiar para outro local.
10. Preencha Intervalo da lista com tabela.
11. Preencha Intervalo de critérios com Criterio.
12. Em Copiar para digite resultado.
228
13. Clique em OK.
14. Selecione uma célula vazia.
15. Na guia Desenvolvedor, escolha Parar gravação do grupo Código.
Caso você esqueça de parar a gravação, os comandos que você utilizar no Excel continuarão
a serem gravados.
Crie um Botão para executar a macro criada:
16. Na guia Desenvolvedor, clique no comando Inserir.
17. Escolha Botão e determine suas dimensões de um retângulo que ocupe as células
D14:E15.
18. Surgirá a caixa de Atribuir Macro.
19. Escolha a macro nVeiculos e tecle OK.
229
20. Mantendo o botão selecionado digite Pesquisa para definir o nome da face do botão.
21. Selecione o texto digitado e clique com o botão direito do mouse sobre o botão e
escolha formatar.
22. Formate a fonte como desejar.
23. Faça algumas escolhas na guia Pesquisa e teste o funcionamento do botão Pesquisa.
No caso do resultado não apresentar nenhum veículo é provável que não exista nenhum
veículo com as características escolhidas.
Controles para cálculos
Propósito:
Usar os valores de retorno dos controles para efetuar cálculos.
Meta:
Formular hipóteses de financiamento de veículos.
Você intriduzirá uma barra de rolagem em que a posição do cursor deslizante represente
uma taxa de juros desejada.
230
1. Clique em Inserir no grupo Controles da guia Desenvolvedor.
2. Clique no controle Barra de Rolagem e desenhe um retângulo com o tamanho
aproximado do intervalo de D21:E21.
A Barra de Rolagem permite que uma célula receba um valor entre um mínimo e um
m´ximo. Utilizaremos a barra de rolagem para simular uma taxa de juros do financiamento
do veículo variando de 0 a 10%. O retorno da barra de rolagem é um número inteiro que
pode variar de 0 a 33.000.
3. Clique em Propriedades do grupo Controles da guia Desenvolvedor.
4. Informe 0 no Campo Valor Mínimo: que é o menor valor que a barra de rolagem
assuma quando estiver com o cursor mais à esquerda.
5. Inform 1000 para Valor Máixmo: que é o valor máximo quando o cursor estiver à
direita.
A taxa de juros terá incrementos de 0,25%, isto é, assumirá valores de 0%, 025%, 050%,
0,75% e assim por diante até 10,00%. A barra de rolagem só retorna números inteiros. Por
isso, devemos transformar o número devolvido em porcentagem. Faremos com que o
número 1 represente 0,01%, ela será representada por 1.000. Portanto, este é o valor que
informamos para Valor Máximo:
6. Informe 25 para Valor Incremental. Este indica quantas unidades o cursor deslizante
se deslocará quando clicamos no botões laterais.
7. Informe 100 para Mudança de página. Esse valor determina quantas unidades o
cursor se deslocará quando clicamos nos espaços entre o cursor e o botão lateral.
8. Indique a célula G21 para receber o valor qu representa a posição do cursor na Barra
de rolagem.
231
9. Clique em OK.
10. Digite 0% em D22 e 10% em E22.
11. Alinhe a fonte da célula D22 à esquerda.
12. Digite Juros em B21.
13. Em C21 digite a fórmula: =G21/10000.
Formate como porcentagem e defina 2 casas decimais. Isto é necessário para que 25
represente 0,25% (que corresponde a 0,0025), 50 represente 0,50% (0,005) e 100
represente 1,00% (1).
14. Clique nos botões laterais e no espaço entre o cursor e os botões para testar o
funcionamento da barra de rolagem.
232
Você introduzirá o controle Botão de Rotação para definir o período de financiamento
que pode ser 12, 24, 36, 48 ou 60 meses.
15. Clique em Inserir do grupo Controles da guia Desenvolvedor.
16. Clique no controle Botão de Rotação.
17. Desenhe o controle conforme a representação abaixo:
18. Com o controle selecionado clique em Propriedades.
19. Preencha Valor mínimo: com 0.
20. Preencha Valor máximo: com 60.
21. Preencha Alteração Incremental: com 12.
22. Preencha Vínculo da célula: com C24.
233
23. Clique em OK.
24. Digite Prazo na célula B24.
25. Digite Valor Financiado: em B26.
26. Digite Valor da Parcela: em B27.
27. Aplique bordas nas células C26 e C27.
28. Na célula C27 digite a fórmula para cálculo da parcela: =PGTO(C21;C24;-C26).
29. Digite um valor na célula C26 e use a barra de rolagem e o botão de rotação para
simular taxas e prazos.
30. Oculte as colunas G e H pois seus dados não precisam ser exibidos, retire as linhas de
grade e aplique outras formatações que julgar interessante.
Criando funções personalizadas no Excel
O Microsoft Excel, assim como diversos outros programas de planilha eletrônica, está
repleto de funções prontas para uso. Tais funções podem ser acessadas facilmente
pressionando-se [SHIFT]+F3 em qualquer célula da planilha. São centenas, divididas em
várias categorias: científicas, contábeis, matemáticas, engenharia, etc..
A cada nova versão do programa, mais e mais recursos são adicionados visando
automatizar tarefas e facilitar a vida do usuário. No entanto, pode ser que você, usuário do
Excel, se depare com situações em que o programa talvez não tenha a solução que você
necessita pronta para uso ou que talvez você imagine uma solução diferente ou mais
prática para o problema.
Nessas horas, o melhor a fazer é arregaçar as mangas e resolver tudo no velho e bom
estilo "Do it yourself!" , ou "faça você mesmo!"
Desde a versão 5, o Excel disponibiliza um recurso de programação interessante: o VBA
(Visual Basic for Applications). O VBA é uma versão da linguagem de programação Visual
Basic específica para os aplicativos da família Office. Com o VBA, é possível automatizar
tarefas e definir funções personalizadas conhecidas como UDF (user defined functions).
Funções personalizadas podem ser usadas como qualquer outra função do Excel.
234
Após a criação, aparecem listadas no assistente de funções do Excel juntamente com
todas as outras, porém numa categoria específica denominada "Definida pelo usuário".
O Visual Basic é relativamente fácil. Sua estrutura e modo de escrita é muito semelhante
ao Inglês básico, não requerendo identificadores como ";" (ponto e vírgula) ou "{ }"
(chaves) para delimitar ou finalizar instruções, como ocorre em linguagens como "C" e
"Java".
Também não faz distinção de comandos escritos com letra minúscula ou maiúscula como
ocorre em outras linguagens "mais burocráticas" ou de baixo nível.
O Visual Basic é uma linguagem de alto nível, orientada a eventos e de fácil compreensão.
Um pouco de noção de lógica de programação e entendimento sobre escopo de variáveis
e funções são os requisitos mínimos necessários para poder trabalhar com ela. Requisitos
estes que com certeza não faltam a um operador experiente de Excel.
Macros
Macro e o Ambiente VBA
Macro comandos são um conjunto de instruções dispostas de forma lógica com a
finalidade de automatizar tarefas ou ações da planilha do Excel. São consideradas com o
mesmo requinte daárea de programação de computadores, pois as macros são
constituídas de um conjunto de comandos e funções que atendem a certa necessidade
específica, definida pelo próprio usuário.
O termo macro comando é aqui utilizado no mesmo sentido em que se utiliza o termo
linguagem de programação de computadores. A diferença entre as duas nomenclaturas
consiste no fato de que uma linguagem de programação de computadores é uma
ferramenta independente de trabalho, o que não ocorre com os recursos de macro
comandos, que, para serem utilizados, necessitam de um aplicativo em execução, como
ocorre com o efeito de programação usado no programa de planilha eletrônica do Excel.
Para o sucesso adequadodessa etapa da aprendizagem, é ideal que seja feito um estudo
paralelo do tema com um livro de apresentação de algoritmos e lógica de programação.
Assim sendo, recomenda-se a leitura e o estudo de obras relacionadas aos mesmos
(assuntos).
O programa Microsoft excel 2013 usa, para o desenvolvimento de macro comandos, o
ambiente Visual Basic for Applications (VBA) versão 7.0, compatível com o ambiente
integrado de programação Visual Basic do pacote Visual Studio 6.0, de 1988. O pacote
de programação Visual Studio é uma ferramenta que possui alguns ambientes de
programação, destacando-se o ambiente integrado de programação Visual Basic, que
opera com a linguagem de programação Structured BASIC. Tanto o ambiente Visual
235
Basic como o ambiente VBA do Excel 2013 usam a linguagem Structured BASIC, o que
lhes dá certa similaridade na escrita de códigos, mas não em sua forma de execução.
Ativação da Guia Desenvolvedor
O Excel possui o ambiente VBA, dedicado à criação de macro comandos. Por ser um
recurso destinado a uma parcela menor de usuários, encontra-se omitido no programa
após a instalação, de forma que apenas os usuários que realmente necessitam desse
recurso devem ativá-lo.
Caso a guia Desenvolvedor não esteja disponível ) o que é provável após a instalação do
programa), acompanhe os passos seguintes:
Vá a guia Arquivo, Opções, e na categoria Personalizar Faixa de Opções e, em seguida,
na categoria Guias Principais, selecione a caixa de seleção desenvolvedor e acione o
botão OK. A opção de habilitação da guia Desenvolvedor é do tipo Liga/Desliga,
podendo ser habilitada ou desativada.
Observe na Faixa de Opções a exibição da guia Desenvolvedor.
236
Tipos de Macros
As macros podem ser divididas em três tipos, sendo de comando, de funções definidas
pelo usuário e de sub-rotina, as quais são descritas em seguida.
Macros de Comando
Esse é o tipo mais comum de procedimento. Geralmente, equivale a comandos de menu e
caixas de diálogo, utilizados somente nas regiões selecionadas de uma pasta de trabalho e
suas planilhas. É como se os mesmos comandos do menu fossem executados diversas
vezes.
Macros de Funções Definidas pelo Usuário
Esse tipo de procedimento trabalha de forma idêntica ás funções incorporadas ao Excel. A
diferença é que o usuário cria sua própria função. O efeito de trabaho desse tipo de macro
não se verifica nas regiões previamente selecionadas. Essa macros sempre retornam um
determinado valor.
Macro de Sub-Rotina
Esse procedimento é uma junção dos dois amnbientes anteriores, pois tanto permite
alterações em regiões previamente selecionadas como também retornam valores.
Caravteriza-se pela possibilidade de ser chamada de dentro de outras macros, o que
minimiza o uso de tarefas repitidas e torna o código de programação mais leve.
Necessidade de Planejar uma Macro
Jamais elabore uma macro sem planejá-la e rascunhá-la, pois o resultado pode ser o
trabalho dobrado. Tenha o conceito a seguir sempre em mente: tudo o que é feito em um
computador precisa ser planejado de antemão, não importa a experiência que você tenha.
Uma construtora nunca ergue uma edificação sem sua respectiva planta, por mais
tecnologia que possua ou conhecimento que tenha sobre o assunto. Sendo assim, aja da
mesma forma. O processo de planejamento é fácil e muito útil, pois basta descrever todas
as etapas a serem cumpridas para resolver o problema. Siga esse passos básicos:
Entenda o problema a ser resolvido, fazendo uma análise de cada parte dele, a fim de
ter uma sólida ideia do que deve ser feito.
237
Planeje o procedimento manualmente, rascunhando em um papel. Em alguns casos, é
aconselhável até elaborar um diagrama de blocos para completar a tarefa a ser
executada mais rapidamente.
Somente após executados os passos anteriores, é que a codificação do procedimento
pode ser feito na planilha. Em alguns casos, essas tarefas pode ser simplificada com a
utilização do recurso de gravação de toques no teclado e no mouse.
Sempre deve-se atribuir um nome a um procedimento, para identificação e posterior
execução. O nome precisa ser o mais sugestivo possível à sua função.
Ambiente Microsoft Visual Basic
O ambiente de edição de macros do Visual basic no Excel inclui um editor de código, um
pesquisador de objeto hierárquico, um depurador multipainel, uma janela de propriedade
e um Explorer de projeto, para ajudar a visualizar e organizar o código e o objeto do
projeto. Auxilia, assim, a escrever um código de programação sintaticamente correto. Para
ativar o ambiente de programação de forma simples, execute:
Vá a guia Desenvolvedor, grupo Código, botão Visual Basic. É apresentado o ambiente
de desenvolvimento de programação Microsoft Visual Basic.
238
Construção de uma Macro
Macros são construídas e montadas no ambiente do Microsoft Visual Basic ora
apresentado, o qual é aberto automaticamente pelo processo de gravação de macro. Para
fazer um pequeno teste, vamos utilizar a forma mais simples de construção de macros, o
recurso de gravação.
Tudo o que for executado na planilha, em termos de digitação, formatação e acionamento
de menus, é monitorado e transformado em código do Visual Basic, o qual fica associado
à planilha.
Procedimento
Em primeiro lugar, certifique-se que uma folha de planilha em branco esteja aberta e
execute o comando: guia Desenvolvedor, grupo Código, botão Gravar Macro.
239
Abre-se a caixa de diálogo Gravar Macro, como observamos na imagem acima, na qual
devem ser informadas todas as lacunas editáveis para o início da documentação e da
futura localização da macro.
Observe que o Excel fornece automaticamente o nome Macro1 como sugestão para o
nome da primeira macro, aberta nessa instância de trabalho. Essa macro terá por objetivo
escrever um nome no meio da tela.
Para tanto, escreva no campo de identificação de nomes, Nome da macro, o título
Escre_no_Centro.
Observação:
O nom informado no campo Nome da macro não pode se riniciado por um número e
não deve haver espaços em branco entre as palavras.
Para melhor identificar um procedimento, apresenta-se, na caixa de diálogo, uma área
denominada Descrição. Nesse campo, você pode escrever um breve comentário sobre o
procedimento, que, no caso, pode ser Apresentação de texto centralizado.
Na mesma caixa de diálogo existe também um botão drop-down, perto da opção
Armazenar macro em, o qual permite selecionar uma entre três opções de
armazenamento da macro a ser criada.
A opção Pasta de trabalho pessoal de macros faz a gravação de todas as macros em
um arquivo denominado PESSOAL na pasta C:\program Files\Microsoft
Office\Office15\CLSART, de modo que as macros desenvolvidas ficam disponíveis
para usp por qualquer planilha.
A opção Esta pasta de trabalho permite a criação de macros na própria pasta de
trabalho em uso.É utilizada para macros que devem estar perto das planilhas, sendo
de uso exclusivo delas.
240
Já a opção Nova pasta de trabalho grava uma macro em outra pasta de trabalho, a
qual pode ser utilizada com um número grande de planilhas de outras pastas. Esse
recurso é muito parecido com a opção Pasta de trabalho pessoal de macros, porém,
com ela, é possível escolher o nome da pasta de trabalho. Vale salientar que o arquivo
gerado por essa opção não é carregado automaticamente, como acontece com o
arquivo PESSOAL.
Observação:
Além dos campos já trabalhados, existe ainda a opção Tecla de atalho, que permite a
definição de uma letra associada à tecla Ctrl, as quis, usadas em conjunto, executam a
macro desejada. Caso seja indicada um aletra maiúscula, o comando de atalho deve ser
utilizado com a tecla Shift, além do Ctrl+Shift+E deve ser executada.
Acione o botão OK. Observe, na Barra de Status da planilhga, a indicação do botão Parar
gravação.Agora que o Excel passou a indicar que está gravando, basta executar os comandos
desejados para que eles sejam registrados. Assim sendo, posicione o cursor sobre a célula
A9.
Digite, na célula A9, o texto Teste de execução de macro e acione Enter. Selecione a faixa
de células A9:I9 e execute o comando: guia Página Inicial, grupo Alinhamento, botão
Mesclar e Centralizar. Escolha o formato Negrito e clique, em seguida, no botão Parar
gravação, que se encontra ao lado do prompt Pronto na Barra de Status do programa.
A macro foi criada e guardada em um módulo do Miocrosoft Visual Basic. Para ver a
macro criada, execute o comando: guia Desenvolvedor, grupo Código, botão Visual
Basic.
241
Na janela projeto – VBAProject, selecione a pasta Módulos com um clique no sinal + e
selecione, na sequência, Módulo1. Veja nma tela a exibição do código da macro recém-
criada.
Em seguida, veja a listagem completa da macro criada por meio do recurso de gravação.
Todas as ações executadas durante o processo de gravação foram monitoradas e
traduzidas para comandos macro.
242
Veja o código da Macro1:
Sub Escreve_no_Centro()
'
' Escreve_no_Centro Macro
' teste1 com texto.
'
'
Range("A9").Select
ActiveCell.FormulaR1C1 = "Teste de execução de macro"
Range("A9:I9").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Bold = True
243
With Selection.Font
.Name = "Calibri"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
End Sub
Características Típicas de Macros em VBA
Uma macro escrita em Visual Basic possui, tipicamente, as características descritas nos
tópicos seguintes.
o Linhas de Comentários – São as linhas iniciadas por ‘ (apóstrofo). Elas indicam
para o Excel que são apenas linhas de comentários, não sendo, assim, processadas.
São muito úteis, pois exibem o nome do procedimento, a data da sua criação, o
nome de quem as fez e a descrição de sua finalidade. Essas mensagens só são
apresentadas se forem informadas no momento da gravação.
Sub Escreve_no_Centro()
'
' Escreve_no_Centro Macro
244
' teste1 com texto.
'
'
o Comandos Sub/End Sub – Essas palavras indicam o início e o final de um
procedimento de comandos e, por esse motivo, são denominadas Procedimentos
Sub. Se o procedimento for do tipo função definida pelo usuário, apresentam-se
as palavras Function/End Function, que recém o nome técnico de palavras-
chave.
Sub Escreve_no_Centro()
[Corpo do procedimento com o código de programa]
End Sub
o Nome do Procedimento – O nome do procedimento é apresentado à frente da
palavra-chave, seguido de parênteses, os quais são obrigatórios e podem ser
usados para indicar argumentos, no caso de funções definidas pelo usuário.
Sub Escreve_no_Centro()
o Código-Fonte do procedimento – É o código inserido entre as palavras-chave e
representa toda ação monitorada e gravada. No exemplo anterior, o código foi
distribuído em duas rotinas, com as palavras-chave Width/End Width. A primeira
equivale à ação de formatar as células no centro de uma faixa e a segunda à
formatar o título digitado como negrito. Antes da primeira rotina, aparecem as
indicações do posicionamento do cursor, da digitação do título e da seleção da
faixa de células a ser formatada.
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
245
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End Withf
Os demais comandos existentes no código do programa se referem às instruções de
posicionamento do cursor de ntro da planilha, como:
Range("A9").Select
O comando
ActiveCell.FormulaR1C1 = "Teste de execução de macro"
Permite escrever dentro de uma célula da planilha.
Para retornar à planilha,, execute o comando pela janela Microsoft Visual Basic: Arquivo,
Fechar e voltar para Excel. Ou, então, selecione a folha de trabalho pelas teclas de atalho
Alt+Tab.
Executar uma Macro
Para ver o funcionamento vá na guia Desenvolvedor, grupo Código, botão Macros.
Abre-se a caixa de diálogo – Macro, marque a desejada e mande executar.
246
Quando da opção Salvar Como você poderá optar por salvar o arquivo em tipo como
Pasta de Trabalho Habilitada para Macro do Excel.
247
Macros de Endereço Absoluto e Relativo
Ao usar o gravador para gerar um procedimento, por padrão ele define os endereços das
células como absolutos, ou seja, toda vez que se aciona o procedimento, ele é executado
na mesm aposição. Havendo a necessidade de um procedimento ocorrer em outras
células da mesma planilha, é necessário utilizar o endereço relativo. Nesse ponto, você
também aprende a executar uma macro com a utilização das teclas de atalho.
Macro de Endereço Absoluto
Para exemplificar um procedimento bastante simples, que escreva o nome de uma
empresa em uma célula, seu telefone duas células à frente e o nome do contato três
células à frente.
Criando Funções
Existem muitas funções prontas no EXCEL, mas nem todas as fórmulas do mundo estão
implementadas na forma de função. Entretanto, é possível criar nossas próprias funções,
uma função definida pelo usuário (UDF - User Defined Functions ou Custom Excel
Functions). Este tópico é descrito pela Microsoft como
Escrevendo um procedimento Function
Se houver um cálculo complexo a ser reutilizado com freqüência no Microsoft Excel, você
pode criar sua própria função de planilha para executar o cálculo.
Para criar suas próprias funções personalizadas, utilize o Microsoft Visual Basic® for
Applications (VBA). O VBA é uma linguagem de programação interna do Excel que está
acessível pelo atalho ALT+F11.
Vamos começar com uma fórmula simples de ser implementada como função…
Função Definida pelo Usuário
Problema: um termômetro americano marca 68 graus Fahrenheit. Será que está quente ou
frio?
A fim de manter o exemplo simples, escolhemos implementar uma função calcula o
número de graus Celsius a partir dos graus Fahrenheit. Quando a função for chamada a
partir de uma célula do EXCEL, o resultado do cálculo é retornado e mostrado na célula.
248
Antes de começar, crie a seguinte planilha colocando os valores numéricos nas células A2,
A3 e A4:
O procedimento a seguir ensina, em apenas 5 passos, como criar e usar uma função
definida pelo usuário:
I) O atalho ALT+F11 abre a janela do VBA (Microsoft Visual Basic For Applications)
II) Procure pela guia Inserir e selecione a opção Módulo:
III) Na janela que abriu, digite o código de sua função. Por exemplo, uma função para
calcular graus Celsius a partir dos graus Fahrenheit:
Function Celsius(grausF)
Celsius = (grausF - 32) * 5 / 9
249
End Function
Digite a função acima na janela Módulo 1 aberta:
IV) No menu Arquivo do VBA, clique em Fechar e voltar para Microsoft Excel (ou use o
atalho Alt+Q) e Volte para a planilha Excel .
V) Teste sua função. Digite na planilha a função que você acabou de implementar (não
esqueça de começar com o sinal de igualdade) colocando entre parênteses o argumento
desejado (nestecaso um número ou uma célula que contém um número). Neste exemplo
você deve digitar =CELSIUS(A2) na célula B2 (ou outra célula qualquer) do Excel.
Observe que o Excel reconhece a existência da função CELSIUS à medida que está sendo
digitada:
Na planilha, se os valores numéricos estiverem nas células A2, A3 e A4, use sua função
para fórmulas do mesmo modo que usaria qualquer função de planilha. Por exemplo, a
função Celsius pode ser usada na planilha assim:
250
Importante: Uma função criada dessa maneira somente se encontra disponível para uso
na pasta de trabalho onde tiver sido criada e, se quiser ser usada posteriormente,
precisará ser salva como Pasta de Trabalho Habilitada Para Macro Do Excel.
Dica: se quiser usar essa função em outro local, você pode copiar o código da função para
módulos do VBA situados em outras pastas de trabalho.
Detalhes da função definida pelo usuário Celsius
Eis, novamente, a listagem de nossa função, vamos examiná-la em detalhes:
Function Celsius(grausF)
Celsius = (grausF - 32) * 5 / 9
End Function
O nome da função que será usada pelo usuário, Celsius no exemplo acima, deve receber
dentro do corpo da função o resultado do cálculo realizado com o argumento grausF,
que, neste caso foi subtraído de 32 e depois multiplicado por cinco nonos (5/9). As quatro
operações básicas (soma, subtração, multiplicação e divisão) mais a potência utilizam os
mesmos operadores que usamos dentro de uma planilha Excel: +, -, *, / e ^.
Outros operadores bem como algumas funções estão disponíveis, mas usam uma forma
diferente dos operadores e funções que usamos dentro de uma planilha. Outros
operadores disponíveis dentro do VBA: \ (divisão inteira), Mod (para obter o resto da
divisão inteira), & (para concatenar texto). Existem ainda várias funções , mas isso é fácil
de achar na Internet e foge do escopo da nossa postagem.
O exemplo de FUNÇÃO DEFINIDA PELO USUÁRIO (UDF) usado nesta postagem foi
bastante simplificado, apenas para demonstrar o funcionamento básico de uma função
definida pelo usuário. Para criar funções complexas no VBA, é preciso conhecer um pouco
o modelo de objetos do VBA para Excel, a estrutura da linguagem do Visual Basic (VB) e o
ambiente VBA.
251
Exemplo de outra Função Definida pelo Usuário
1) Como calcular através de uma UDF o processo inverso, transformação de Celsius para
Fahrenheit?
Neste caso, siga os mesmos passos anteriores, mas a função digitada em módulo 1 ficará
assim (pode ser digitada abaixo da função anterior, se não quiser apagar a outra função já
digitada):
Function Fahrenheit(grausC) ' de graus C para graus F
Fahrenheit = grausC * 9 / 5 + 32
End Function
Para usar a função acima dentro de uma planilha, escreva =FAHRENHEIT(100) dentro de
uma célula no Excel para saber, por exemplo, a temperatura em que a água ferve (100°C)
em um termômetro com a escala Fahrenheit. Deverá surgir o número 212.
2) Eis aqui um outro exemplo de função definida pelo usuário para calcular centímetros a
partir de valores em polegadas:
Function cm(pol) ' transformar polegadas em centímetros
cm = pol * 2.54
End Function
A função acima, quando digitada em Módulo 1 (conforme fizemos com a função anterior),
permite calcular na planilha a conversão de polegadas para centímetros, bastando para
isso digitar =CM(A2) na planilha Excel, caso o número em polegadas esteja na célula A2,
por exemplo.
3) A função que faz o contrário, transforma centímetros em polegadas está aqui:
Function pol(cm) ' transformar centímetros em polegadas
pol = cm / 2.54
End Function
252
Para usar, basta digitar, por exemplo, =POL(12) na planilha Excel e saberemos quanto
mede, em polegadas, a medida 12 centímetros. Deve dar 4,724409 polegadas.
Como criar uma função para calcular a combinação com repetição
Seguindo os passos acima, mostrados aqui de forma resumida:
I) Tecle o atalho ALT+F11 para abrir a janela do VBA
II) Vá na guia Inserir e selecione a opção Módulo
III) Digite o seguinte código:
Function CombinRep(ByVal n As Long, ByVal p As Long) As Double
If (n > 0 And p > 0) Then
CombinRep = factorial(n + p - 1) / (factorial(p) * factorial(n - 1))
Else
CombinRep = Error()
End If
End Function
Function factorial(ByVal n As Long) As Double
factorial = 1
Dim a As Double
For a = 1 To n
factorial = factorial * a
Next a
End Function
253
IV) Tecle o atalho ALT+Q para sair do VBA e voltar à planilha
V) Teste a função entrando com algum valor, como por exemplo, digitando na planilha
Excel, =CombiRep(4,2) em alguma célula qualquer. Se tudo estiver funcionando como
deveria, deve aparecer programagicamente o número 10.
Acabamos de criar, conforme prometido, uma função para cálculo de Combinação com
Repetição.
Trainning Education Services ® - Sede: Av. Paulista, 777, 15° andar – SP
São Paulo (11) 3323-1676 - Rio de Janeiro (21) 4063-6518 - Brasília (61) 4063-7766 - Belo Horizonte (31) 4063-8187
Vitória (27) 4062-9192 - Curitiba (41) 4063-8083 - Porto Alegre (51) 4063-9474 - Recife (81) 3301-1600 - Salvador (71) 4062-9323
www.trainning.com.br - comercial@trainning.com.br
---- DESTACAR ESTA FOLHA E ENTREGAR AO INSTRUTOR ----
Data:
NOME:
CURSO:
INSTRUTOR:
AVALIAÇÃO FINAL DE TREINAMENTO
Marque com um X na lacuna ao lado da figura na opção desejada.
Conteúdo Programático do Curso � � ☺
Tempo de Realização do Curso � � ☺
Referência Final ao Treinamento � � ☺
Material Didático � � ☺
Didática Instrutor � � ☺
Conhecimento Técnico do Instrutor � � ☺
Instalações Físicas (Mesa, Cadeira, Quadro, Projetor, Computador) � � ☺
Atendimento Comercial e Administrativo � � ☺
Conteúdo, Material Adicional e Informações no site da Trainning � � ☺
COMENTÁRIOS
Espaço livre para comentários, críticas e sugestões sobre o treinamento realizado.
INDICAÇÕES
Indique 1 amigo e Ganhe Desconto no próximo treinamento.
� REGULAR
� SATISFATÓRIO
☺ ÓTIMO
A
V
A
L
IA
Ç
Ã
O
F
IN
A
L
D
E
T
R
E
IN
A
M
E
N
T
O
Janela da Capa (Trainning).pdf
Page 44
Página em branco
Página em branco