Excel
Informática
Avançado
Escola Virtual
Informática
Excel
Avançado
(VFROD9LUWXDO
Ficha técnica
® Reservados todos os direitos patrimoniais e de reprodução à Fundação Bradesco
Homepage: www.fb.org.br
AUTORIA
InfoSERVER S.A.
Departamento de Treinamento
COLABORADORES
Departamento Escola Virtual - Fundação Bradesco
PROJETO GRÁFICO E REVISÃO
Setor Geração de Recursos Didáticos
PUBLICAÇÃO: 2010
APRESENTAÇÃO
Esta apostila compõe o material didático dos cursos de Informática
referentes ao Pacote Office 2007, composto pelos aplicativos Word,
Excel, PowerPoint, Access, Outlook e Internet.
Na sequência, apresentamos definições, orientações técnicas,
procedimentos e exercícios práticos, fundamentais para qualificar
profissionalmente jovens e adultos, para que possam ter autonomia
no uso da ferramenta, dominar as competências exigidas pelo
mercado de trabalho e, assim, favorecer sua empregabilidade em
qualquer área ocupacional.
Lembramos que as profundas transformações ocorridas no mundo
do trabalho, a velocidade da informação, a comunicação, a
globalização, a difusão de novas tecnologias e de novas formas
de organização do trabalho são alguns exemplos das mudanças
que vêm exigindo dos trabalhadores o desenvolvimento de novas
competências frente às profissões.
Diante desse contexto, a informática é parte fundamental dessa
transformação, visto que está presente em todos os setores da
sociedade: no comércio, na indústria, na saúde, na educação etc.
Enfim, as informações aqui apresentadas servirão para alicerçar
a base do conhecimento requerida para a construção das
competências e habilidades propostas na qualificação de Operador
de Microcomputador.
3
SUMÁRIO
1. TRABALHANDO COM NOMES ..................................................................................11
1.1 O que é um nome? .........................................................................................11
2. ORGANIZANDO DADOS ..........................................................................................15
2.1 Classificação de dados ....................................................................................15
2.2 Subtotais .......................................................................................................18
2.2.1 Criando subtotais ..................................................................................19
2.2.2 Removendo subtotais ............................................................................22
2.3. Filtros ...........................................................................................................23
2.3.1 Utilizando filtro com critérios avançados..................................................23
2.3.2 Vários critérios em uma coluna ..............................................................26
2.3.3 Vários critérios em várias colunas em que todos os critérios devem ser
verdadeiros ...................................................................................................27
2.3.4 Vários critérios em várias colunas em que qualquer critério pode ser
verdadeiro ....................................................................................................28
2.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios para
várias colunas ...............................................................................................28
2.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios para uma
coluna ..........................................................................................................29
2.3.7 Critérios para localizar valores de texto que compartilhem alguns caracteres
mas não outros .............................................................................................30
3. TABELA DINÂMICA .................................................................................................31
3.1 Criar um relatório de tabela dinâmica ou gráfico dinâmico .................................31
4. IMPORTAÇÃO DE DADOS ........................................................................................40
4.1 Arquivo de texto .............................................................................................40
4.1.1 Importando arquivo de texto e abrindo-o ................................................40
4.1.2 Importando arquivo de texto como intervalo de dados externos ...............46
4.2 Importando dados do Access ...........................................................................46
4.3 Importando dados de uma página Web ............................................................50
4.3 Atualizando dados importados .........................................................................52
5. CENÁRIOS .............................................................................................................52
5.1 Editando um cenário .......................................................................................57
5.2 Criando um relatório de cenário .......................................................................58
5.3 Excluindo um cenário ......................................................................................60
6. SEGURANÇA DAS INFORMAÇÕES ............................................................................61
6.1 Protegendo toda a planilha ..............................................................................61
6.2 Protegendo partes da planilha .........................................................................63
6.3 Protegendo uma pasta de trabalho ..................................................................65
6.4 Desprotegendo células e planilhas ...................................................................66
5
7. VALIDAÇÃO ............................................................................................................66
7.1 Criando uma validação de dados ......................................................................67
7.2 Inserindo uma mensagem de entrada ..............................................................73
7.3 Inserindo um alerta de erro .............................................................................74
8. USANDO AUDITORIA EM CÉLULAS ..........................................................................76
8.1 Janela de inspeção .........................................................................................76
8.1.1 Adicionando células à janela de inspeção ................................................76
8.2 Realizando auditoria em planilha ......................................................................78
8.2.1 Rastreando células precedentes ou dependentes .....................................79
8.2.2 Removendo o rastreamento ...................................................................80
9. PERSONALIZANDO A ÁREA DE TRABALHO ...............................................................80
9.1 Criando barra de ferramentas .........................................................................80
9.2 Excluindo barra de ferramentas .......................................................................82
9.3 Criando menu de comandos ............................................................................82
10. CONSTRUINDO UM GRUPO DE TRABALHO ............................................................82
10.1 Salvando um grupo de pastas de trabalho personalizadas ................................82
11. SOLUCIONADO PROBLEMAS..................................................................................83
11.1 Problemas com uma variável .........................................................................83
11.2 Problemas com mais de uma variável .............................................................87
11.3 Problemas sem solução .................................................................................93
12. FUNÇÕES .............................................................................................................95
12.1 Funções deTexto ...........................................................................................95
12.1.1 Função ESQUERDA ..............................................................................95
12.1.2 Função DIREITA ..................................................................................96
12.1.3 Função MAIÚSCULA.............................................................................97
12.1.4 Função MINÚSCULA ............................................................................98
12.1.5 Função PRI.MAIÚSCULA ......................................................................98
12.1.6 Função LOCALIZAR .............................................................................99
12.1.7 Função EXT.TEXTO ............................................................................ 102
12.1.8 Função CONCATENAR ........................................................................ 103
12.2 Funções Matemáticas e Trigonométricas ....................................................... 104
12.2.1 Função ARRED .................................................................................. 104
12.2.2 Função ARREDONDAR.PARA.CIMA...................................................... 105
12.2.3 Função ARREDONDAR.PARA.BAIXO .................................................... 106
12.2.4 Função INT ....................................................................................... 107
12.2.5 Função SOMASE................................................................................ 108
12.3 Funções Estatísticas .................................................................................... 112
12.3.1 Função CONT.NÚM ............................................................................ 112
12.3.2 Função CONT.VALORES ..................................................................... 113
12.3.3 Função CONTAR.VAZIO ..................................................................... 115
12.3.4 Função CONT.SE ............................................................................... 116
6
12.4 Funções de Pesquisa e Referência ................................................................ 117
12.4.1 Função PROCH.................................................................................. 118
12.4.2 Função PROCV .................................................................................. 120
12.4.3 Função ÍNDICE ................................................................................. 122
12.4.4 Função CORRESP .............................................................................. 125
12.5 Funções de Banco de Dados ........................................................................ 127
12.5.1 Função BDMÉDIA .............................................................................. 128
12.5.2 Função BDCONTAR............................................................................ 129
12.5.3 Função BDMÍN .................................................................................. 129
12.5.4 Função BDMÁX ................................................................................. 130
12.5.5 Função BDMULTIPL ........................................................................... 131
12.5.6 Função BDSOMA ............................................................................... 132
12.6 Funções de Informações ............................................................................. 133
12.6.1 Função ÉERROS ................................................................................ 134
12.7 Funções Financeiras .................................................................................... 136
12.7.1 Função VF ......................................................................................... 136
12.7.2 Função NPER .................................................................................... 138
12.7.3 Função PGTO .................................................................................... 139
12.7.4 Função VP......................................................................................... 140
12.7.5 Função TAXA ..................................................................................... 142
13. TABELA DE DADOS ............................................................................................. 144
13.1 Tabela de dados com uma variável de entrada .............................................. 144
13.2 Tabela de dados com duas variáveis de entrada ............................................ 147
14. FORMULÁRIOS PERSONALIZADOS ....................................................................... 148
14.1 Criando formulário ...................................................................................... 149
EXERCÍCIOS ............................................................................................................ 161
7
INTRODUÇÃO
Seja bem-vindo ao Excel 2007 – Avançado!
No curso de Excel Avançado, você aprenderá como organizar
um banco de dados utilizando o conceito de relacionamento
entre tabelas. Além disso, serão abordados recursos como o uso
da importação de dados, cenários, segurança das informações,
validação de dados, auditoria em células, funções, formulários
personalizados etc.
9
1. Trabalhando com nomes
1.1 O que é um nome?
Nome é uma referência que fazemos a uma célula ou intervalo de células. Esse nome
pode ser utilizado em uma fórmula ou função, dentro de qualquer planilha da pasta de
trabalho. Usando nomes, você pode facilitar muito o entendimento e a manutenção das
fórmulas.
Depois de adotar a prática do uso de nomes, você poderá atualizá-los, auditá-los e
gerenciá-los facilmente.
Exemplo
Tipo
Exemplo sem nome
Exemplo com nome
Referência
=SOMA(C20:C30)
=SOMA(TOTALFINAL)
Constante
=PRODUTO(A5,8.3)
=PRODUTO(PRECO;QUANT)
Fórmula
=SOMA(PROCV(A1,B1:F20,5,FALSO), —G5)
=SOMA(PROCURA)
Tabela
=C4:G36
=TABELAPRINCIPAL
Para nomear uma célula ou região, faça o seguinte:
1. Abra a pasta de trabalho Exemplos e selecione a planilha Nomes.
2. Clique na célula B12.
11
Figura 1
3. Na guia Fórmulas, grupo Nomes Definidos, clique em Definir Nome
.
4. Na caixa de diálogo Novo Nome, digite o nome desejado no campo Nome e
clique em OK.
Figura 2
Para criar um nome, algumas regras devem ser obedecidas:
• O primeiro caractere do nome deve ser letra ou underline.
• O nome pode ter até 255 caracteres.
• Não pode haver espaços.
12
Ao definirmos um nome para uma célula ou região, ele passa a ser exclusivo da pasta
de trabalho, ou seja, em qualquer planilha, podemos fazer referência a ele. Por exemplo,
em qualquer célula de qualquer planilha da pasta de trabalho em que você digite =Dólar,
aparecerá o valor digitado para o dólar.
Agora, vamos utilizar esse nome no cálculo da célula C5.
5. Posicione o cursor na célula C5 e digite a fórmula =B5*Dólar.
Figura 3
6. Em seguida, é só copiar a fórmula para as demais células.
Observe que não foi necessário fixar o endereço ao utilizar um nome.
Dica
Você pode nomear uma célula ou região rapidamente. Basta selecionar a célula ou região,
clicar na caixa Nome na barra de fórmulas, digitar o nome e pressionar Enter.
13
Para visualizar todos os nomes atribuídos a células ou regiões nas planilhas, basta
clicar na seta Drop Down, na caixa Nome, à esquerda na barra de fórmulas.
Figura 4
Observação
A caixa Nome, na barra de fórmulas, só pode ser utilizada para criar ou exibir nomes.
Para excluir um nome, faça o seguinte:
1. Na guia Fórmulas, grupo Nomes Definidos, clique no botão Gerenciador de
.
Nomes
2. Na caixa Gerenciador de Nomes, selecione o nome desejado e clique no botão
Excluir.
14
Figura 5
3. Caso esse nome tenha sido utilizado em alguma fórmula ou função, aparecerá a
seguinte mensagem de erro: #NOME?
Vamos praticar! Exercício 1
2. Organizando dados
Ao inserir dados em uma planilha, eles podem não se apresentar ordenados da
maneira que você deseja visualizá-los.
Com os recursos de classificação e a aplicação de filtros, são criadas novas perspectivas
para ajustar os dados às suas necessidades.
2.1 Classificação de dados
1. Abra a pasta de trabalho Exemplos e selecione planilha Subtotais.
2. Selecione o rótulo de uma coluna.
15
Figura 6
3. Na guia Dados, grupo Classificar e Filtrar, clique em Classificar
.
4. Na caixa de diálogo Classificar, selecione a coluna desejada, valores e ordem.
Figura 7
Figura 8
16
Você pode adicionar até 64 níveis de classificação por meio do botão Adicionar
Nível. Veja detalhes na figura 9.
Figura 9
No botão Opções de classificação, você pode definir outras orientações de
classificação.
Figura 10
17
2.2 Subtotais
Automaticamente, você pode calcular os subtotais e os totais gerais para uma coluna,
usando o comando Subtotal, grupo Estrutura de Tópicos, guia Dados.
Figura 11
Os subtotais são calculados com uma função de resumo, como SOMA ou MÉDIA. Você
pode exibir mais de um tipo de função de resumo para cada coluna.
Os totais gerais são derivados de dados de detalhes, e não dos valores nos subtotais.
Por exemplo, se você usar a função de resumo MÉDIA, a linha de total geral exibirá
uma média de todas as linhas de detalhes, e não uma média dos valores das linhas de
subtotal.
Área de detalhes
Para subtotais automáticos e estruturas de tópicos de planilha, as linhas ou colunas de
subtotal são totalizadas pelos dados de resumo. Em geral, os dados de detalhes estão,
imediatamente, acima ou à esquerda dos dados de resumo.
18
2.2.1 Criando subtotais
1. Selecione o intervalo de células desejado.
Certifique-se de que as colunas tenham um rótulo, contenham dados relacionados e
que o intervalo selecionado não tenha linhas ou colunas em branco.
2. Classifique a coluna que servirá de base para gerar o subtotal.
Figura 12
3. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal
.
4. Na caixa de diálogo Subtotais, campo A cada alteração em, selecione a coluna
a ser subtotalizada. Em nosso exemplo, é Produto.
5. No campo Usar função, clique na função que você deseja utilizar para calcular os
subtotais. Em nosso exemplo, é SOMA.
6. Na caixa Adicionar subtotal a, marque a caixa de seleção de cada coluna que
contenha valores a serem subtotalizados. Em nosso exemplo, é Total.
19
Figura 13
Veja o resultado.
Figura 14
20
7. Se você desejar uma quebra de página automática após cada subtotal, marque a
caixa de seleção Quebra de página entre grupos (figura 15).
8. Para especificar uma linha de resumo acima da linha de detalhes, desmarque a
caixa de seleção Resumir abaixo dos dados (figura 15).
9. Como opção, você poderá usar o comando Subtotais novamente, repetindo as
etapas de 1 a 6 para adicionar mais subtotais com funções de resumo diferentes. Para
impedir que os subtotais existentes sejam sobrescritos, desmarque a caixa de seleção
Substituir subtotais atuais (figura 15).
Figura 15
Dica
Para exibir um resumo somente dos subtotais e totais gerais, clique nos símbolos de
, ao lado dos números de linha. Use os símbolos
e
estrutura de tópicos
para exibir ou ocultar as linhas de detalhes dos subtotais individuais.
21
2.2.2 Removendo subtotais
Quando você remove subtotais, o Excel remove também a estrutura de tópicos e as
quebras de página que você inseriu na lista juntamente com os subtotais.
1. Clique em uma célula na lista que contém um subtotal.
2. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal
de diálogo Subtotal é exibida.
3. Clique em Remover todos.
Figura 16
Vamos praticar! Exercício 2
22
. A caixa
2.3 Filtros
Os dados filtrados exibem somente as linhas que atendem aos critérios especificados
e ocultam as demais.
Depois de filtrar os dados, você pode copiá-los, editá-los, formatá-los, fazer gráfico e
imprimir o subconjunto de dados filtrados, sem reorganizá-los nem movê-los.
Você também pode filtrar dados por mais de uma coluna. Os filtros são aditivos, o que
significa que cada filtro adicional baseia-se no filtro atual e ainda reduz o subconjunto de
dados.
2.3.1 Utilizando filtro com critérios avançados
A opção Avançado trabalha de forma diferente da opção Filtro (ambas na guia
Dados, grupo Classificar e filtrar) em vários aspectos:
• Exibe a caixa de diálogo Filtro Avançado em vez do menu AutoFiltro.
• Requer critérios avançados em um intervalo de critérios separados na planilha e
acima do intervalo de células ou da tabela que se deseja filtrar. O Excel usa o intervalo
de critérios separados na caixa de diálogo Filtro Avançado como fonte dos critérios
avançados.
• Ao criar o intervalo para o critério, devem-se adicionar pelo menos três linhas em
branco acima do intervalo a ter os dados filtrados, sendo que este deve ter rótulos de
coluna.
Segue exemplo de planilha estruturada com intervalo de critérios e de dados a serem
filtrados.
Figura 17
23
1. Abra a pasta de trabalho Exemplos e selecione a planilha FILTROS_AVANC.
Figura 18
2. Na célula B2, digite Flauta para selecionar esse instrumento.
3. Na guia Dados, grupo Classificar e Filtrar, clique no botão Avançado
.
4. Para filtrar o intervalo ocultando as linhas que não obedecem aos critérios, clique
em Filtrar a lista no local.
Figura 19
24
5. Para filtrar o intervalo para outra área da planilha, copiando as linhas que obedecem
aos critérios, clique em Copiar para outro local. E, na caixa Copiar para, indique a
célula a partir da qual você deseja colar as linhas.
Figura 20
6. Na caixa Intervalo da lista, insira a referência do intervalo em que os dados
serão filtrados, inclusive os rótulos.
Figura 21
7. Na caixa Intervalo de critérios, insira a referência do intervalo de critérios,
inclusive os rótulos.
Figura 22
8. Clique em OK e observe o resultado.
25
Figura 23
9. Para filtrar novos dados, altere o valor no intervalo de critérios e filtre os dados
novamente.
10. Para visualizar todas as linhas, clique no botão Limpar.
Ao denominar um intervalo de células como Critérios, a referência para o intervalo
aparecerá na caixa Intervalo de critérios automaticamente.
Você também pode definir o nome Banco de dados para o intervalo de dados a
ser filtrado e o nome Extração para a área onde você deseja colar as linhas. Esses
intervalos aparecerão, automática e respectivamente, nas caixas Intervalo da lista e
Copiar para.
Ao copiar linhas filtradas para outro local, você pode especificar que colunas incluir
na operação de cópia.
Antes de filtrar, copie os rótulos das colunas desejadas para a primeira linha da área
onde planeja colar as linhas filtradas.
Ao filtrar, insira uma referência nos rótulos das colunas copiadas na caixa Copiar
para. As linhas copiadas incluirão somente as colunas para as quais você copiou os
rótulos.
2.3.2 Vários critérios em uma coluna
Lógica booleana: (Região = Norte OU Região = Nordeste)
Para localizar linhas que atendam a vários critérios para uma coluna, digite os critérios
diretamente um após o outro, em linhas separadas.
26
No exemplo da figura 24, o intervalo de critérios filtrará as linhas que contêm Norte
ou Nordeste na coluna Região.
Figura 24
2.3.3 Vários critérios em várias colunas em que todos os critérios devem
ser verdadeiros
Lógica booleana: (SIGLA UF = SP E POPULAÇÃO > 10000)
Para localizar linhas que atendam a critérios em várias colunas, digite todos os critérios
na mesma linha do intervalo de critérios.
No exemplo da figura 25, o intervalo de critérios filtrará as linhas que contêm SP na
coluna SIGLA UF e população maior que 10.000 na coluna POPULAÇÃO.
Figura 25
27
2.3.4 Vários critérios em várias colunas em que qualquer critério pode ser
verdadeiro
Lógica booleana: (SIGLA UF = AC OU REGIÃO = Sudeste)
Para localizar linhas que atendam a critérios em várias colunas, em que qualquer um
pode ser verdadeiro, digite os critérios em linhas diferentes do intervalo de critérios.
No exemplo da figura 26, o intervalo de critérios filtrará as linhas que contêm AC na
coluna SIGLA UF ou SUDESTE na coluna REGIÃO.
Figura 26
2.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios
para várias colunas
Lógica booleana: ((SIGLA UF = MG E POPULAÇÃO >=300000) OU (SIGLA UF = RN
E POPULAÇÃO <=250000))
Para localizar linhas que atendam a vários conjuntos de critérios, em que cada
conjunto inclui critérios para várias colunas, digite cada conjunto de critérios em linhas
separadas.
No exemplo da figura 27, o intervalo de critérios filtrará as linhas que contêm MG na
coluna SIGLA UF e o número de população maior ou igual que 300.000 na coluna
POPULAÇÃO; ou exibirá as linhas que contêm RN na coluna SIGLA UF e um valor
menor ou igual a 250.000 na coluna POPULAÇÃO.
28
Figura 27
2.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios
para uma coluna
Lógica booleana: ((POPULAÇÃO > 1000000 E < 2000000) OU (POPULAÇÃO <
1500000))
Para localizar linhas que correspondam a vários conjuntos de critérios, em que cada
conjunto inclui critérios para uma coluna, inclua várias colunas para o mesmo título da
coluna.
No exemplo da figura 28, o intervalo de critérios filtrará as linhas que contêm
POPULAÇÃO entre 1.000.000 e 2.000.000, bem como valores menores que
1.500.000 na coluna POPULAÇÃO.
Figura 28
29
2.3.7 Critérios para localizar valores de texto que compartilhem alguns
caracteres, mas não outros
Para localizar valores de texto que compartilhem alguns caracteres, mas não outros,
siga um ou mais destes procedimentos:
Digite um ou mais caracteres para localizar linhas com valor de texto em uma coluna
que inicie com tais caracteres. Por exemplo, se você digitar o texto São como critério, o
Excel irá localizar “São Paulo”, “São Sebastião” e “São Pedro”.
Os seguintes caracteres curinga podem ser utilizados como critérios de comparação.
Use
Para localizar
Qualquer caractere único
? (ponto de interrogação)
Por exemplo: antoni?
Localiza:“antonio” e “antonia”
Qualquer número de caracteres
* (asterisco)
Por exemplo: *este
Localiza: “Nordeste” e “Sudeste”
Um ponto de interrogação, asterisco ou til
~ (til) seguido de ?, * ou ~
Por exemplo: fy91~?
Localiza: “fy91?”
No seguinte intervalo de dados, o intervalo de critérios exibe as linhas que se iniciem
com São* na coluna MUNICÍPIOS.
Figura 29
Vamos praticar! Exercício 3
30
3. Tabela dinâmica
Um relatório de tabela dinâmica é utilizado para resumir, analisar, explorar e apresentar
dados de resumo e para ver comparações, padrões e tendências facilmente. O relatório
de tabela dinâmica permite tomar decisões corretas sobre dados críticos.
3.1 Criar um relatório de tabela dinâmica ou gráfico
dinâmico
Para criar um relatório de tabela dinâmica ou gráfico dinâmico, você deve se conectar
à fonte de dados e inserir o local do relatório. Essa fonte não deve conter colunas ou
linhas vazias. Por exemplo, linhas ou colunas em branco que são usadas para separar um
bloco de dados de outro devem ser removidas.
1. Abra a pasta de trabalho Exemplos e selecione a planilha Tabela-Dinâmica.
2. Selecione uma célula em um intervalo de células ou coloque o ponto de inserção
dentro da planilha.
Figura 30
31
3. Certifique-se de que o intervalo de células tenha títulos de coluna, que, na tabela
dinâmica, são chamados de campos.
Figura 31
4. Para criar um relatório de tabela dinâmica, na guia Inserir, grupo Tabelas,
.
clique em Tabela Dinâmica
5. Em seguida, clique em Tabela Dinâmica.
Figura 32
A caixa de diálogo Criar Tabela Dinâmica é exibida.
6. Clique em Selecionar uma tabela ou intervalo.
7. Digite o intervalo de células ou a referência do nome da tabela e clique em OK
Figura 33
32
Se você selecionou uma célula em um intervalo de células, ou se o ponto de inserção
estava em uma tabela antes de iniciar o assistente, o intervalo de células ou a referência
do nome da tabelas é exibido na caixa Tabela/Intervalo.
Como alternativa, para selecionar um intervalo de células ou uma tabela, clique
para ocultar a caixa de diálogo temporariamente.
em Recolher Caixa de Diálogo
Selecione o intervalo na planilha e pressione Expandir Caixa de Diálogo .
8. Um relatório de tabela dinâmica vazio é criado em uma nova planilha e a caixa de
diálogo Lista de campos da tabela dinâmica é aberta.
Figura 34
1 Área de layout do relatório de tabela dinâmica.
2 Lista de campos de tabela dinâmica.
33
Agora, você está pronto para criar o relatório de tabela dinâmica. Os campos
selecionados para o relatório dependem do que você deseja saber. Por exemplo, valor
total por produto. Para obter a resposta, você precisa de dados sobre os produtos e seus
preços.
9. Selecione os campos Produto e Preço na caixa de seleção, na Lista de campos
da tabela dinâmica.
Figura 35
Observe que você não precisa usar todos os campos da lista de campos para criar
um relatório. Quando você seleciona um campo, o Excel coloca-o em uma área padrão
do layout para você.
• Os dados do campo Produto, que não contêm números, são exibidos como linhas
no lado esquerdo do relatório automaticamente.
• Os dados do campo Preço, que contêm números, são mostrados corretamente na
área à direita.
• O título sobre os dados do produto é Rótulos de Linha. O título sobre os totais
do preço é Soma de Preço. Isso ocorre porque o Excel usa a função SOMA para somar
campos numéricos.
Agora, você conhece o total por produto.
10. Salve a pasta de trabalho.
34
Agora, observe que os dados de origem apresentam informações sobre os produtos
em todos os países que fazem parte da planilha. Sendo assim, outra pergunta que você
poderia fazer seria a seguinte: Quais são os totais dos preços dos produtos por país?
Para obter essa resposta, adicione o campo País ao relatório de tabela dinâmica como
um filtro de relatório. Use um filtro de relatório para localizar um subconjunto de dados
no relatório, geralmente uma linha de produto, uma duração ou uma região geográfica.
Para isso, faça o seguinte:
1. Clique com o botão direito do mouse sobre o campo País.
2. Selecione a opção Adicionar ao Filtro de Relatório.
Figura 36
3. Usando o campo País como um filtro de relatório, você pode ver um relatório
separado por um país específico, ou pode ver as vendas para ambos os países juntos.
Figura 37
35
4. Para que você possa entender melhor o exemplo, selecionaremos um país e, depois,
clicaremos no botão OK. Observe o exemplo da figura 38:
Figura 38
5. Note que apenas os produtos e os preços do país escolhido estão sendo exibidos
na tabela dinâmica.
Figura 39
36
Observe também que, ao lado do país selecionado, será exibido o botão Filtro
, que representa que os dados abaixo foram filtrados segundo o dado ao lado. Essa
informação também aparece na lista de campos da tabela dinâmica.
Figura 40
Para selecionar mais de um país, abra novamente a caixa para seleção e marque
Selecionar vários itens. Marque os países que deseja filtrar e clique em OK.
Figura 41
Vamos praticar! Exercício 4
37
Para utilizar dados externos, faça o seguinte:
1. Clique em Usar uma fonte de dados externa.
2. Clique em Escolher Conexão.
3. A caixa de diálogo Conexões Existentes é exibida.
Figura 43
38
4. Na lista suspensa Mostrar, na parte superior da caixa de diálogo, selecione a
categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as
Conexões, que é o padrão.
Figura 44
5. Selecione uma conexão a partir da caixa de listagem Selecionar uma Conexão
e clique em Abrir.
6. Ao escolher uma conexão da categoria Conexões desta Pasta de Trabalho,
você reutilizará ou compartilhará uma conexão existente. Ao escolher uma conexão
das categorias Arquivos de conexão da rede ou Arquivos de conexão deste
computador, o arquivo de conexão será copiado na pasta de trabalho como uma nova
conexão de pasta de trabalho e usado como a nova conexão para o relatório de tabela
dinâmica.
7. Para colocar o relatório de tabela dinâmica em uma nova planilha, começando na
célula A1, clique em Nova Planilha.
Figura 45
8. Para colocar o relatório de tabela dinâmica em uma planilha existente, selecione
Planilha Existente. Em seguida, digite a primeira célula no intervalo de células onde
deseja colocar o relatório de tabela dinâmica.
Figura 46
39
9. Como alternativa, clique em Recolher Caixa de Diálogo
para ocultar a caixa
de diálogo temporariamente. Selecione a célula inicial na planilha e pressione Expandir
Caixa de Diálogo .
10. Clique em OK.
4. Importação de dados
O principal benefício da conexão com dados externos (importação) é a possibilidade
de analisar esses dados no Excel periodicamente, sem copiá-los repetidamente. Essa é
uma operação que pode levar tempo e que está propensa a erros.
Depois de conectar-se a dados externos, você também pode, automaticamente,
atualizar as pastas de trabalho a partir da fonte de dados original, sempre que ela for
alterada.
4.1 Arquivo de texto
Há duas formas de importar dados de um arquivo de texto usando o Excel:
• Abrir o arquivo de texto no Excel.
• Importar o arquivo de texto como um intervalo de dados externos.
Você pode importar até 1.048.576 linhas e 16.384 colunas.
4.1.1 Importando arquivo de texto e abrindo-o
Você pode converter um arquivo de texto criado em outro programa em uma pasta
de trabalho do Excel, usando o comando Abrir.
1. Clique no botão Office
e, em seguida, em Abrir.
2. Na caixa Arquivos do tipo, selecione Arquivos de texto.
3. Localize o arquivo de texto que deseja abrir e clique duas vezes nele.
40
Figura 47
Se o arquivo de texto for (.txt), o Excel iniciará o Assistente de importação de
texto.
Figura 48
41
4. Se os itens do arquivo de texto estiverem separados por guias, dois-pontos, ponto
e vírgula, espaços ou outros caracteres, selecione Delimitado, na caixa Tipo de dados
originais. Se todos os itens do arquivo de texto tiverem o mesmo tamanho, selecione
Largura fixa.
5. Digite ou selecione um número de linha para especificar a primeira linha dos dados
que deseja importar na caixa Iniciar importação na linha.
Figura 49
6. Na caixa Origem do arquivo, selecione o conjunto de caracteres usado no
arquivo de texto. Na maioria dos casos, você pode deixar a configuração Windows (ANSI)
como padrão.
Figura 50
7. A caixa de diálogo Visualização do arquivo mostra como o texto será exibido,
quando separado em colunas na planilha.
Figura 51
42
8. Após selecionar as configurações, clique em Avançar.
9. A próxima tela será visualizada conforme mostra a figura 52.
Figura 52
10. Na caixa Delimitadores, selecione o caractere que separa os dados no arquivo de
texto. Se o caractere não estiver listado, marque a caixa de seleção Outros. Em seguida,
digite um caractere na caixa que contém o cursor. Essas opções não se encontrarão
disponíveis, se o tipo de dados for Largura fixa.
Figura 53
43
11. Selecione a opção Considerar delimitadores consecutivos como um só, se
os dados contiverem um delimitador de mais de um caractere entre os campos de dados,
ou se contiverem vários delimitadores personalizados.
Figura 54
12. Qualificador de texto – Quando o Excel encontra o caractere qualificador
de texto, todo o texto que segue esse caractere e vem antes da próxima ocorrência
desse caractere é importado como um valor, mesmo que o texto contenha um caractere
delimitador. Por exemplo, se o delimitador for uma vírgula (,) e o qualificador de texto
forem aspas (“), “São Paulo, SP” será importado em uma célula como São Paulo, SP. Se
nenhum qualificador ou se o qualificador apóstrofo (‘) for especificado, “Dallas, Texas”
será importado em duas células adjacentes como “São Paulo e SP”.
Figura 55
13. Após selecionar as configurações, clique em Avançar.
14. A tela seguinte será visualizada conforme mostra a figura 56.
15. Na caixa Formato dos dados da coluna, selecione o formato dos dados da
coluna selecionada na seção Visualização dos dados. Se você não desejar importar
a coluna selecionada, clique em Não importar coluna (Ignorar). Essa escolha fará o
Excel converter os dados importados corretamente.
44
Figura 56
16. Clique em Concluir.
17. Faça os ajustes desejados na planilha, para que os dados importados sejam
visualizados adequadamente e salve a pasta de trabalho.
Figura 57
Vamos praticar! Exercício 5
45
4.1.2 Importando arquivo de texto como intervalo de dados externos
Para importar um arquivo de texto como um intervalo de dados externos, o
procedimento difere da forma anteriormente vista apenas nos primeiros passos:
1. Abra a pasta de trabalho e selecione a planilha que deverá receber os dados.
2. Na guia Dados, grupo Obter dados externos, clique no botão De Texto
.
3. Localize o arquivo de texto que você deseja abrir e clique duas vezes nele.
4. Repita os passos de 4 a 17 vistos no tópico anterior.
4.2 Importando dados do Access
Para importar dados do Access, faça o seguinte:
1. Na faixa de opções Dados, grupo Obter dados externos, clique no botão Do
.
Access
2. Na caixa de diálogo Selecionar fonte de dados, localize o arquivo do Access,
selecione-o e clique no botão Abrir.
Vamos usar o banco de dados Northwind 2007, que está gravado na pasta
Exemplos_curso.
46
Figura 58
3. Em seguida, selecione a tabela Análise de Vendas e clique em OK.
Figura 59
47
4. Selecione o método como os dados deverão ser exibidos. Em nosso exemplo, será
o modo Tabela.
Figura 60
5. Informe onde você deseja armazenar seus dados. Em nosso exemplo, será Na
nova planilha.
Figura 61
48
6. Clicando no botão Propriedades, você pode informar o tempo em que o Excel
deverá verificar se há novas atualizações na tabela, bem como formato de conexão,
endereço de conexão, idioma.
Figura 62
7. Escolha sua configuração e clique no botão OK.
49
8. Na caixa de diálogo Importar dados, clique no botão OK, para que a importação
seja concluída.
Figura 63
4.3 Importando dados de uma página Web
Faça uma consulta à Web para recuperar dados atualizáveis armazenados em sua
Intranet ou na Internet como uma única tabela, várias tabelas ou todo o texto de uma
página da Web. Em seguida, analise os dados usando as ferramentas e os recursos do
Excel. Por exemplo, você pode recuperar e atualizar cotações de ações de uma página
pública da Web, ou recuperar e atualizar uma tabela de informações de vendas da página
de uma empresa na Web.
Consultas à Web são especialmente úteis para recuperação de dados em tabelas ou
áreas pré-formatadas.
Para criar ou editar uma consulta à Web, faça o seguinte:
1. Na guia Dados, no grupo Obter dados externos, clique em Da Web
50
.
Ao ser exibida a caixa de diálogo Nova consulta à Web, insira a URL (endereço que
especifica um protocolo, como HTTP ou FTP, e a localização de um objeto ou documento
de que você deseja obter dados). É possível digitar a URL, colá-la de um endereço
copiado ou clicar na seta próxima à lista Endereço e selecionar um endereço utilizado
recentemente. O comprimento máximo de uma URL é de 255 caracteres.
Figura 64
2. Clique em Ir.
3. Clicando no botão Opções, você pode definir a formatação e as configurações de
importação.
Figura 65
51
4. Clique no botão
, ao lado da tabela que você deseja importar, ou clique no
, no canto superior esquerdo da página, para importar a página toda.
botão
5. Em seguida, clique no botão Importar.
Figura 66
4.3 Atualizando dados importados
Para atualizar dados importados, abra a planilha desejada e clique na guia Dados,
grupo Conexões, e no botão Atualizar.
5. Cenários
Muitas vezes, há ocasiões que nos obrigam a fazer uma projeção de situações futuras
para direcionar nossas decisões com a menor margem de erro possível. A utilização
das planilhas eletrônicas nos permite visualizar cenários que podem se realizar ou não.
Portanto, são hipóteses de acordo com os elementos que fornecemos para alimentar
determinada situação.
Para criar um cenário, primeiramente, monte a planilha com todas as fórmulas e as
células que contêm os valores variáveis. As que conterão valores diferentes para a mesma
52
célula serão alimentadas pelo conjunto de cenários.
A figura 67 mostra os valores relativos ao mês de janeiro de cada um dos três países
no intervalo B8:B10.
Para os meses de fevereiro a maio, será utilizada uma fórmula que multiplica o valor
de janeiro pelo índice de cada mês que está no intervalo B2:B5. Por exemplo, a fórmula
do primeiro valor de fevereiro é =$B8*B$2.
Figura 67
A empresa tem diversas previsões sobre o aumento ou diminuição dos valores desses
meses. Para visualizar a situação de acordo com as diversas hipóteses, criamos um
conjunto de cenários.
Para criar um cenário, faça o seguinte:
1. Abra a pasta de trabalho Exemplos e selecione a planilha Cenario.
2. Selecione as células variáveis do cenário (B2:B5).
3. Na guia Dados, grupo Ferramentas de Dados, clique sobre a opção Teste de
Hipóteses e, em seguida, em Gerenciador de Cenários.
Figura 68
53
4. Na caixa Gerenciador de Cenários, clique no botão Adicionar.
Figura 69
5. Dê um nome para o cenário, por exemplo, Baixo, e defina quais serão as células
variáveis. Automaticamente, é exibido o endereço da seleção atual.
Figura 70
Se desejar fazer algum Comentário, utilize a caixa correspondente.
54
O item Proteção permite que o cenário não seja alterado ou visualizado, caso a
planilha esteja protegida.
6. Definidas as opções, clique em OK.
7. Será exibida a janela Valores de cenário com os valores das células selecionadas.
Clique em OK.
Figura 71
8. Para criar um novo cenário, por exemplo, Alto, siga os passos 4, 5 e 6 e defina
os seguintes valores na caixa Valores de cenário: $B$2=1,8; $B$3=1,9; $B$4=2 E
$B$5=2,1.
9. Para atualizar os valores nas células variáveis, clique sobre o cenário desejado e, a
seguir, sobre o botão Mostrar.
Figura 72
55
Na figura 73, foi aplicado o índice utilizando cenário Baixo para exibir os resultados.
Já na figura 74, foi aplicado o índice Alto.
Figura 73
Figura 74
56
5.1 Editando um cenário
Para modificar um cenário, faça o seguinte:
1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de
Hipóteses e, em seguida, em Gerenciador de Cenários.
2. Clique no nome do cenário a ser alterado e, a seguir, em Editar.
3. Faça as alterações necessárias e clique em OK para alterar os valores.
Figura 75
57
5.2 Criando um relatório de cenário
Para criar um relatório de cenário, faça o seguinte:
1.Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de
Hipóteses e, em seguida, em Gerenciador de Cenários.
2. Clique no botão Resumir.
Figura 76
3. A caixa de diálogo Resumo do cenário exibe duas opções. Selecione Resumo
do cenário para criar um relatório especificando os valores atuais das células variáveis e
os valores das células de resultado, que dependem das células variáveis.
Em nosso exemplo, as células de resultado são as referentes aos valores dos
meses de fevereiro a maio. Em Células de resultado, especifique as células que deseja
representar no relatório e clique em OK.
58
Figura 77
Na figura 78, veja o exemplo do relatório Resumo do cenário.
Figura 78
59
4. Se você selecionar Tabela Dinâmica do Cenário, uma tabela dinâmica será
criada em uma nova planilha, baseada nos dados dos cenários existentes na planilha
atual.
Na figura 79, veja o exemplo do relatório Tabela Dinâmica do Cenário.
Figura 79
5.3 Exclusão de um cenário
Para excluir um cenário, tenha certeza de que deseja fazê-lo, pois, a partir do momento
que excluí-lo, ele não poderá ser recuperado.
1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de
Hipóteses e, em seguida, em Gerenciador de Cenários.
2. Selecione o cenário desejado e clique em Excluir.
Figura 80
Vamos praticar! Exercício 6
60
6. Segurança das informações
Dependendo do grau de automação de um modelo de planilha, você pode desejar que
os usuários não alterem nenhuma parte dela, ou tenham acesso a somente algumas de
suas áreas exclusivamente para entrada de dados, não permitindo que sejam alterados
rótulos ou fórmulas de cálculo.
A seguir, apresentamos alguns níveis de proteção do Excel:
• Proteger Planilha – Feita planilha por planilha, essa proteção permite alterar
apenas o conteúdo das células que não estiverem travadas.
• Permitir que Usuários Editem Intervalos – Permite designar usuários,
computadores da rede ou grupos que possam realizar alterações em células específicas
sem digitar uma senha.
• Proteger Pasta de Trabalho – Esse nível de proteção aplica-se à estrutura e à
janela da pasta. Ao proteger a estrutura, torna-se impossível inserir ou excluir, ocultar ou
reexibir, alterar o nome ou modificar a disposição das planilhas na pasta de trabalho. Quando
protegemos a janela, o tamanho e a posição da janela do arquivo ficam inalterados.
• Proteger e Compartilhar Pasta de Trabalho – Quando uma pasta é compartilhada,
ou seja, quando é aberta por vários usuários por meio da rede e se quer controlar as
alterações feitas por cada usuário, é importante inserir uma senha, sem a qual não é
possível remover o histórico do controle de alterações ou do uso compartilhado.
6.1 Protegendo toda a planilha
1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger.
Vamos proteger a planilha Cenário-pronta.
2. Na guia Revisão, grupo Alterações, clique em Proteger Planilha
.
3. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados
na planilha.
61
Figura 81
4. Digite uma senha de proteção com até 255 caracteres, podendo conter letras,
números e símbolos. Ao clicar em OK, será aberta uma caixa para reinserir a senha de
proteção.
Cuidado
Se esquecer a senha, não há como recuperá-la.
62
6.2 Protegendo partes da planilha
1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger.
Vamos proteger a planilha Nome-pronta.
2. Selecione as células que não deseja proteger (liberadas para a entrada de
dados). Em nosso exemplo, será a célula B12.
Figura 82
3. Clique com o botão direito do mouse na célula e, em seguida, na opção Formatar
células.
Figura 83
63
4. Ative a guia Proteção, desmarque a opção Bloqueadas e clique em OK.
5. Na guia Revisão, grupo Alterações, clique em Proteger Planilha
.
6. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados
na planilha.
7. Digite uma senha de proteção.
8. Agora, faça alguns testes e veja que apenas a célula B12 pode ser alterada.
9. Salve a pasta de trabalho.
64
6.3 Protegendo uma pasta de trabalho
1. Abra a pasta de trabalho que deseja proteger.
2. Na guia Revisão, grupo Alterações, clique em Proteger Pasta de Trabalho
.
3. Na caixa de diálogo Proteger Estruturas e Janelas, selecione as opções
desejadas para bloquear:
• Estrutura – Movimentação, exclusão e inserção de planilhas
• Janelas – Botões minimizar, maximizar e restaurar
4. Digite uma senha e, em seguida, clique em OK. A pasta de trabalho estará protegida
por senha contra alterações.
Figura 85
5. Clique no botão Office e em Salvar como.
6. Na caixa de diálogo Salvar como, clique no botão Ferramentas e, e em seguida,
escolha Opções gerais.
Figura 86
65
7. Na área Compartilhamento de arquivos, entre com as senhas de proteção e
gravação. Habilite também a opção Recomendável somente leitura e clique em OK.
Figura 87
8. Salve a pasta de trabalho.
6.4 Desprotegendo células e planilhas
Para desproteger células ou planilhas, faça o seguinte:
1. Selecione a célula ou planilha que deseja desproteger.
2. Na guia Revisão, grupo Alterações, clique em Desproteger Planilha.
7. Validação
Em muitas planilhas criadas, os usuários digitam dados para obter os resultados
desejados. Por essa razão, assegurar a entrada de dados válidos é uma tarefa
importante.
Por exemplo, convém restringir a entrada de dados a um determinado intervalo de
datas e a escolhas usando uma lista limitada, ou certificar-se de que apenas números
inteiros positivos sejam digitados.
Fornecer ajuda imediata para orientar os usuários e mensagens claras quando dados
inválidos forem digitados também é essencial para permitir que a entrada de dados
aconteça de forma adequada.
66
7.1 Criando uma validação de dados
Uma vez decidida que validação você deseja usar em uma planilha, configure-a,
adotando o seguinte procedimento:
1. Abra a pasta de trabalho Exemplos e selecione a planilha Vendas.
2. Selecione uma ou mais células para validar. Vamos validar o intervalo B3:M23.
3. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de
Dados.
Figura 88
67
4. Na guia Configurações, caixa Permitir, selecione a opção mais adequada.
Por exemplo, você pode selecionar Lista para limitar respostas a perguntas como:
• Mensalidade paga? Sim ou Não.
• A quantidade vendida foi: Baixa, Média, Alta.
Figura 89
5. Clique na caixa Fonte e, em seguida, digite os valores da lista separados por ponto
e vírgula.
Figura 90
6. Em seguida, selecione a célula B3 e clique na seta para abrir a lista.
Figura 91
68
A largura da lista suspensa é determinada pela largura da célula que tem a validação
de dados. Talvez seja necessário ajustar essa largura para impedir que sejam truncadas
entradas válidas maiores do que a largura da lista suspensa.
Você também pode criar uma lista de valores a partir de um intervalo de células.
• Em Fonte, clique no botão que esconde temporariamente a janela.
• Selecione o intervalo de células que deseja exibir na lista.
Figura 92
Vejamos outros exemplos:
Para limitar a entrada a um número inteiro
1. Na caixa Permitir, selecione Número Inteiro.
2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para definir
limite superior e inferior, selecione está entre.
3. Insira o valor mínimo, máximo ou específico a ser permitido (figura 95). Você
também pode inserir uma fórmula que retorne um valor numérico.
Por exemplo, para definir um limite mínimo de deduções para duas vezes o número
de filhos na célula F1, selecione maior ou igual a na caixa Dados e digite a fórmula
=2*F1 na caixa Mínimo.
69
Para limitar a entrada a um número decimal
1. Na caixa Permitir, selecione Decimal.
2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para definir
limite superior e inferior, selecione entre.
3. Insira o valor mínimo, máximo ou específico a ser permitido. Você também pode
inserir uma fórmula que retorne um valor numérico.
Por exemplo, para definir um limite máximo para comissões e bônus de 6% do salário
de um vendedor na célula E1, selecione menor ou igual a na caixa Dados e digite a
fórmula =E1*6% na caixa Máximo.
Para permitir que um usuário digite porcentagens, por exemplo, 20%, selecione
Decimal na caixa Permitir, selecione o tipo de restrição desejado na caixa Dados,
digite o mínimo, o máximo ou um valor específico como um decimal, por exemplo, 0,2.
Para limitar a entrada a um período de tempo (data)
1. Na caixa Permitir, selecione Data.
2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir
datas após um determinado dia, selecione maior que.
3. Insira a data inicial, final ou uma data específica a ser permitida. Você também
pode inserir uma fórmula que retorne um valor de data.
Por exemplo, para definir um período de tempo entre a data de hoje e três dias da
data de hoje, selecione entre na caixa Dados, digite =HOJE() na caixa Mínimo e digite
=HOJE()+3 na caixa Máximo.
Para limitar a entrada a um intervalo de tempo (hora)
1. Na caixa Permitir, selecione Hora.
2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir
horas antes de uma determinada hora do dia, selecione menor que.
3. Insira a hora inicial, final ou uma hora específica a ser permitida. Você também
pode inserir uma fórmula que retorne um valor de hora.
70
Por exemplo, para definir determinado período para servir o café da manhã entre a
hora de abertura do restaurante, na célula H1, e cinco horas depois que o restaurante
abrir, selecione entre na caixa Dados, digite =H1 na caixa Mínimo e, em seguida,
digite =H1+”5:00” na caixa Máximo.
Para limitar a entrada a um texto de comprimento especificado
1. Na caixa Permitir, selecione Comprimento do Texto.
2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir
até um determinado número de caracteres, selecione menor que ou igual a.
3. Insira o valor mínimo, máximo ou um comprimento específico para o texto. Você
também pode inserir uma fórmula que retorne um valor numérico.
Por exemplo, para definir que o comprimento específico de um campo de nome
completo (C1) seja o comprimento atual de um campo de primeiro nome (A1) e de um
campo de sobrenome (B1) mais 10, selecione menor ou igual a na caixa Dados e digite
=SOMA(NÚM.CARACT(A1),NÚM.CARACT(B1),10) na caixa Máximo.
Para calcular o que é permitido com base no conteúdo de outra célula
1. Na caixa Permitir, selecione o tipo de dado desejado.
2. Na caixa Dados, selecione o tipo de restrição desejado.
3. Na caixa ou caixas abaixo da caixa Dados, clique na célula que você deseja usar
para especificar o que é permitido.
Por exemplo, para permitir entradas em uma conta somente se o resultado não
exceder o orçamento, na célula E4, selecione Decimal em Permitir, menor que ou
igual a em Dados e, na caixa Máximo, digite =E4.
Usar uma fórmula para calcular o que é permitido
1. Na caixa Permitir, selecione Personalizado.
2. Na caixa Fórmula, insira uma fórmula que calcule um valor lógico (VERDADEIRO
para entradas válidas ou FALSO para inválidas). Por exemplo:
71
Para assegurar que
Insira esta fórmula
A célula da conta do piquenique (B1) possa ser
atualizada apenas se nada estiver orçado para
a conta sem restrições (D1) e o orçamento total
=E(D1=0,D2<40000)
(D2) for menor do que os R$ 40.000 alocados.
A célula que contém uma descrição do produto
(B2) contenha apenas texto.
=ÉTEXTO(B2)
Para a célula que contém um orçamento publicitário
projetado (B3), o subtotal para subcontratantes
e serviços (E1) seja menor ou igual a R$ 800 e =E(E1<=800,E2<=97000)
a quantia total do orçamento (E2) também seja
menor ou igual a R$ 97.000.
A célula que contém a idade de um empregado
(B4) seja sempre maior do que o número de anos
no emprego (F1) mais 18 (a idade mínima para
=SE(B4>F1+18,VERDADEIRO,FALSO)
contratação).
=CONT.SE($A$1:$A$20,A1)=1
Você deve digitar a fórmula na validação de dados para a
Todos os dados no intervalo de células A1:A20
contenham valores únicos.
célula A1 e, em seguida, preencher as células A2 a A20,
de tal modo que a validação de dados para cada célula
no intervalo tenha uma fórmula similar, mas o segundo
argumento para a função CONT.SE equivalha à célula
atual.
A célula que contém um nome de código de
produto (B5) comece sempre com o prefixo
padrão ID- e tenha, no mínimo, dez caracteres
=E(ESQUERDA(B5, 3) ="ID-",NÚM.CARACT(B5) > 9)
de comprimento.
72
Valores Nulo
Para especificar como você deseja lidar com valores em branco (nulos), marque ou
desmarque a caixa de seleção Ignorar em branco.
Se os seus valores permitidos forem baseados em um intervalo de células com um
nome definido e houver uma célula em branco em qualquer parte do intervalo, a definição
da caixa de seleção Ignorar em branco permitirá que qualquer valor seja inserido na
célula validada. Isso também é válido para qualquer célula referenciada por fórmulas
de validação: se qualquer célula referenciada estiver em branco, a definição da caixa
de seleção Ignorar em branco permitirá que qualquer valor seja inserido na célula
validada.
7.2 Inserindo uma mensagem de entrada
É possível inserir uma mensagem para auxiliar o usuário no preenchimento da célula.
Para isso, faça o seguinte:
1. Clique na guia Mensagem de entrada.
2. Certifique-se de que a caixa de seleção Mostrar mensagem de entrada ao
selecionar célula esteja marcada.
3. Preencha o título e o texto da mensagem de entrada e clique em OK.
Figura 93
73
4. Clique na seta e observe a mensagem.
Figura 94
7.3 Inserindo um alerta de erro
Além da mensagem para auxiliar o usuário a preencher os dados, você pode mostrar
uma mensagem de erro, alertando-o sobre o que está ocorrendo e ainda selecionar o
ícone que ele visualizará. Para isso, faça o seguinte:
1. Clique na guia Alerta de Erro e certifique-se de que a caixa de seleção Mostrar
alerta de erro após a inserção de dados inválidos esteja marcada.
2. Selecione uma destas opções para a caixa Estilo:
• Para exibir uma mensagem informativa que não impeça a entrada de dados inválidos,
selecione Informações.
• Para exibir uma mensagem de aviso que não impeça a entrada de dados inválidos,
selecione Aviso.
• Para impedir a entrada de dados inválidos, selecione Parar.
3. Digite o título e a mensagem de erro a ser visualizada em caso de dados inválidos
(erro) e clique em OK (figura 95).
Se você não inserir um título ou texto, o título usará o padrão "Microsoft Excel" e a
74
mensagem usará o padrão seguinte: "O valor inserido não é válido. Outro usuário
restringiu valores que podem ser inseridos nesta célula."
Figura 95
4. Ao digitar dados inválidos, uma caixa de diálogo será exibida em conformidade
com a opção selecionada em Estilo. No exemplo anterior (Aviso), há a possibilidade de
manter o valor ou rejeitá-lo.
Figura 96
Dica
Se você alterar a validação em uma célula, será possível aplicar, automaticamente, suas
alterações em todas as outras células que têm as mesmas configurações.
Vamos praticar! Exercício 8
75
8. Usando auditoria em células
Ao copiar uma fórmula de uma célula para outra, você pode não perceber que está
gerando dados errados. Muitas células podem conter a mesma fórmula e, como elas
mostram sempre o resultado, é difícil visualizar quais delas possuem apenas números e
quais são calculadas a partir de outros dados.
Caso você faça uma alteração temporária e substitua uma fórmula por um número,
esse dado pode se tornar desatualizado. Possivelmente, você não se dará conta dessa
alteração, até ser questionado sobre o resultado.
Para evitar inconsistências em suas planilhas, o Excel possui ferramentas de auditoria.
Elas se destinam a verificar como estão distribuídas as dependências entre as células, e
analisar possíveis erros de cálculos e entrada de dados.
8.1 Janela de inspeção
Quando as células estão ocultas em uma planilha, você pode inspecionar as fórmulas
e seus resultados por meio da barra de ferramentas Janela de inspeção.
A barra de ferramentas Janela de inspeção torna conveniente inspecionar, auditar
ou confirmar os cálculos de uma fórmula e os resultados em uma planilha extensa, pois,
ao utilizá-la, não é necessário navegar várias vezes para diferentes partes da planilha.
É permitida somente uma inspeção por célula.
8.1.1 Adicionando células à janela de inspeção
1. Selecione as células que você deseja inspecionar.
2. Para selecionar todas as células com fórmulas, na guia Início, no grupo Edição,
clique em Localizar e Substituir, clique em Ir para Especial e, em seguida, clique em
Fórmulas.
3. Na guia Fórmulas, no grupo Auditoria de fórmulas, clique em Janela de
.
inspeção
76
Figura 97
4. Clique em Adicionar inspeção de variáveis
.
Figura 98
5. Clique em Adicionar.
6. Mova a barra de ferramentas Janela de inspeção para a parte superior, inferior,
lado esquerdo ou direito da janela.
7. Para alterar a largura de uma coluna, arraste o limite no lado direito do título da
coluna.
8. Para exibir a célula à qual uma entrada da barra de ferramentas Janela de
inspeção se refere, clique duas vezes na entrada.
As células que têm referências externas (referência a uma célula ou a um intervalo em
uma planilha de outra pasta de trabalho) são exibidas na barra de ferramentas Janela de
inspeção, somente quando a outra pasta de trabalho está aberta.
77
Figura 99
8.2 Realizando auditoria em uma planilha
Às vezes, verificar a precisão de uma fórmula ou localizar a origem de um erro pode
ser difícil, quando ela usa células precedentes ou dependentes.
Células precedentes – São células que fornecem valores a outras células. Por
exemplo, se a célula D10 contiver a fórmula =B5*3, a célula B5 será um precedente da
célula D10.
Células dependentes – São células afetadas pelo valor de outras células. Por
exemplo, se a célula D10 contiver a fórmula =B5*3, o valor da célula D10 será dependente
da célula B5.
Para ajudá-lo a verificar as fórmulas, exibindo graficamente as relações entre
células e fórmulas com setas de rastreamento, você pode usar os botões Rastrear
e Rastrear dependentes
. As setas rastreadoras são:
precedentes
• Azuis – Quando apontam de uma célula que fornece dados para outra célula.
• Vermelhas – Quando uma célula contém um valor de erro, como #DIV/0!
• Pretas – Quando uma célula, em outra planilha ou pasta de trabalho, fizer referência
à célula selecionada, uma seta preta apontará da célula selecionada para um ícone de
.
planilha
Caso as setas não sejam visualizadas, faça o seguinte:
1. Clique no botão Office
na categoria Avançado.
, clique em Opções do Excel e, em seguida, clique
2. Na seção Exibir opções para esta pasta de trabalho, verifique se a opção
Tudo está selecionada em Para objetos, mostrar.
78
Figura 100
Se as fórmulas fizerem referência à outra pasta de trabalho, ela deverá estar aberta,
para que o
Excel possa rastrear as células.
8.2.1 Rastreando células precedentes ou dependentes
Para rastrear células precedentes ou dependentes, faça o seguinte:
1. Selecione a célula que contém a fórmula para a qual você deseja localizar as
células precedentes ou dependentes.
2. Na guia Fórmulas, grupo Auditoria de fórmulas, clique em Rastrear
ou em Rastrear dependentes
.
precedentes
Figura 101
No exemplo da figura 101, a célula C3 tem como precedente a célula A3 e como
dependente a célula A4.
79
3. Para identificar o próximo nível de células que fornecem dados para a célula ativa,
novamente.
clique em Rastrear precedentes
8.2.2 Removendo o rastreamento
Para remover as setas de rastreamento, faça o seguinte:
1. Na guia Fórmulas, grupo Auditoria de fórmula, clique na seta do botão
Remover Setas e, em seguida, selecione uma das opções:
Figura 102
9. Personalizando a área de trabalho
9.1 Criando barra de ferramentas
Dentre as várias ferramentas que o Excel possui, algumas ficam visíveis, outras não.
Para adicionar mais recursos à sua área de trabalho, faça o seguinte:
1. Clique com o botão direito em uma das guias da faixa de opções e, em seguida,
clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.
2. Clique em Personalizar e, na lista Escolher comandos em, selecione o grupo de
comando Guia Suplementos e procure por Barra de Ferramentas Personalizadas.
3. Clique no botão Adicionar.
80
Figura 103
4. Em seguida, clique no botão OK.
5. A barra será exibida no canto superior esquerdo da janela:
Figura 104
81
9.2 Excluindo barra de ferramentas
1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em
seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.
2. Selecione a barra de ferramentas desejada e clique no botão Remover.
9.3 Criando menu de comandos
1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em
seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.
2. Em Escolher comandos em, selecione Guia Suplementos.
3. Clique na opção Comandos de Menu e no botão Adicionar.
10. Construindo um grupo de trabalho
Esse recurso permite visualizar, ao mesmo tempo, várias planilhas relacionadas e
compartilhar elementos entre as pastas de trabalho e entre suas respectivas planilhas,
facilitando a criação de vínculos e a consolidação de dados. Também possibilita grande
economia de tempo na localização dos arquivos e no início dos trabalhos.
10.1 Salvando um grupo de pastas de trabalho
personalizadas
1. Abra as pastas de trabalho que você deseja abrir como um grupo.
2. Dimensione e posicione as janelas das pastas de trabalho como deseja exibi-las na
próxima vez em que forem abertas.
82
Figura 105
3. Clique na guia Exibição, grupo Janela e em Salvar Espaço de Trabalho.
4. Escolha o nome e o local do arquivo e clique no botão Salvar.
11. Solucionado Problemas
Você já se deparou com um problema que apresenta diversas soluções possíveis?
Como saber qual é a melhor? Se ele envolver apenas uma variável, é simples. Já se
houver mais de uma variável, a questão pode tornar-se complexa.
O Excel dispõe de excelentes ferramentas capazes de auxiliar na solução de questões
complexas. Antes de utilizá-las, é necessário saber que tipo de problema você precisa
resolver para, então, escolher que ferramenta utilizar.
11.1 Problemas com uma variável
Esse tipo de problema é bastante simples de ser solucionado. O que é um problema
com uma variável? É aquele em que precisamos definir o valor de apenas um elemento
para gerar o resultado de outro elemento.
83
Por exemplo, a direção de uma empresa precisa saber quanto poderá pagar de aluguel,
de forma que, somando todas as despesas, esse valor não seja superior à receita. Para
isso, criou uma planilha com todos os gastos e receitas do mês e montou o problema para
o Excel resolver, como mostra a figura seguinte.
Figura 106
Primeiramente, vamos entender o problema.
É necessário saber que célula deverá variar. Célula variável é aquela que precisa ter
um valor a ser definido. Nesse caso, é o valor do aluguel (célula B8). Outro elemento a
identificar é qual será a célula de destino, que terá seu valor modificado de acordo com
a célula variável. Portanto, em sua fórmula, a célula de destino contém a referência da
célula variável.
Nesse caso, o total da despesa (célula B9) é a célula de destino, pois esse valor
depende do valor do aluguel para ser calculado. Identificados esses dois elementos,
podemos cuidar da solução do problema.
Como esse é um problema com uma variável (o valor do aluguel), utilizamos o recurso
denomina Atingir meta.
1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses
e, em seguida, em Atingir meta.
84
Figura 107
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 é a célula B9.
Figura 108
3. Na caixa Para valor, digite o resultado desejado. No exemplo, esse valor é 7185.
Figura 109
85
4. Na caixa Alternando célula, insira a referência para a célula que contém o volume
que deseja ajustar. No exemplo, essa é a célula B8.
Figura 110
5. Essa célula deve ser referenciada pela fórmula na célula que você especificou na
caixa Definir célula.
6. Clique em OK.
7. A janela que apareceu informa se houve ou não alteração.
Figura 111
86
O resultado é o seguinte:
Figura 112
11.2 Problemas com mais de uma variável
Agora, vamos analisar problemas que contêm mais de uma variável. Esses problemas
tanto podem ser de simples resolução, como podem apresentar elevado grau de
complexidade. As questões que envolvem mais de uma variável são aquelas em que há
diversos fatores a serem definidos para gerar um resultado.
No problema anterior, para encontrar um valor para a despesa total, havia diversas
variáveis (despesas). Entretanto, somente uma variável não havia sido definida: o valor
do aluguel.
E se for necessário saber qual o valor máximo de cada uma das despesas?
Nesse caso, teremos um problema com mais de uma variável, ou seja, todas as
despesas.
Para solucionar problemas dessa natureza, o Excel dispõe de excelente recurso
chamado Solver.
87
O Solver Add-in é um programa de suplemento do Excel, isto é, programa que
adiciona comandos ou recursos personalizados ao Microsoft Office. Contudo, para usá-lo,
é preciso primeiro carregá-lo. Para isso, faça o seguinte:
1. Clique no botão Office
e, em seguida, em Opções do Excel.
2. Clique em Suplementos e, na caixa Gerenciar, selecione Suplementos do
Excel.
3. Clique em Ir para.
4. Na caixa Suplementos disponíveis, marque a caixa de seleção Solver Add-in
e clique em OK.
Dica
Se o Solver Add-in não estiver listado na caixa Suplementos disponíveis, clique
em Procurar para localizá-lo. Se você for informado de que o Solver Add-in não está
atualmente instalado no computador, clique em Sim para instalá-lo.
Depois de carregar o Solver Add-in, o botão Solver torna-se disponível no grupo
Análise, na guia Dados.
Imagine que você tenha que distribuir uma verba orçamentária para montar um
escritório e precise saber a quantidade que poderá comprar de cada item. Primeiramente, há
certas exigências básicas que devem ser atendidas: será necessário adquirir, exatamente,
15 computadores e, para cada computador, serão necessárias 2 cadeiras; para cada 5
computadores, haverá pelo menos uma impressora e, para cada uma delas, pelo menos
3 mesas. O valor máximo do orçamento é R$ 50.000,00.
Veja que esse problema envolve diversas variáveis e, para cada uma, há uma restrição
a ser atendida. O recurso Solver nos permite determinar restrições para as variáveis de
um problema, constituindo excelente recurso para a solução de questões, uma vez que,
por meio de fórmulas apenas, o processo poderia se tornar lento e cansativo.
88
Após fazer a pesquisa de preços, vamos montar a planilha.
Figura 113
Veja que, na célula B9, há o total do orçamento, definido como 50.000,00.
Agora, precisamos saber qual é a célula de destino, ou seja, uma célula que contém
uma fórmula e que precisa ser definida de acordo com as células variáveis. Como temos
um valor máximo a ser empregado, a célula de destino é exatamente o valor total a ser
gasto na compra, que é a célula com a soma geral, cujo endereço é D6.
As células variáveis são as quantidades a serem definidas do intervalo C2:C5.
Elaborada a planilha com as devidas fórmulas, podemos recorrer ao Solver para
solucionar o problema.
1. Clique na guia Dados, no grupo Análise e no botão Solver.
2. A primeira opção a definir na caixa Parâmetros do Solver é a célula de destino.
Nesse caso, é D6, que é o total a ser gasto. Podemos definir três opções de valor para
essa célula:
• Máx – Encontra o maior valor possível para a célula de destino de acordo com as
restrições. Essa é a opção de nosso problema, pois precisamos encontrar o valor mais
próximo da verba disponível.
89
• Mín – Encontra o menor valor possível para a célula de destino, sempre de acordo
com as restrições impostas. Minimizamos quando precisamos reduzir ao máximo o valor
de uma célula. Por exemplo, quando é necessário cortar despesas.
• Valor de – Permite especificar um valor exato para a célula de destino, ou seja,
quando as células variáveis precisam produzir um valor exato. Por exemplo, se você
quiser saber quanto precisa economizar por mês para ter exatamente R$ 10.000,00 no
final do ano.
3. Células variáveis são as células que serão modificadas para gerar o valor definido
para a célula de destino, que, nesse caso, são as quantidades C2:C5.
Se clicarmos em Estimar, o Solver sugere as células variáveis de acordo com a
fórmula da célula de destino. Pode haver até 200 células variáveis por problema.
4. Para especificar as restrições, ou seja, colocar as condições para as células variáveis,
clique em Adicionar para abrir a caixa Adicionar restrição.
5. Nossa primeira restrição será especificar que o número de computadores deve ser
15. Então, a referência de célula será C3. E a restrição deverá ser igual a B16, que contém
o número de computadores a serem adquiridos. Veja a figura a seguir:
Figura 114
6. Após inserir uma restrição, clique em Adicionar para acrescentar mais
restrições.
• A segunda restrição é C5=C3*B15. A quantidade de cadeiras (C5) tem que ser igual
ao número de computadores (C3) vezes 2 (B15).
• A próxima é C2>=C3/B14, ou seja, a quantidade de impressoras (C2) deve ser
maior ou igual ao número de computadores (C3) dividido por 5 (B14).
• A outra restrição é C4>=C2*B13. O número de mesas (C4) será maior ou igual ao
número de impressoras (C2) vezes 3 (B13).
• Determinamos também que o valor total (D6) tem que ser menor ou igual ao total
do orçamento (B9) pela expressão D6<=B9.
90
7. Após inserir a última restrição, clique em OK para retornar para a caixa Parâmetros
do Solver. Todas as restrições serão listadas em Submeter às restrições.
Figura 115
8. Quando tudo estiver configurado, clique em Resolver.
9. A caixa Resultados do Solver será exibida.
Se for encontrada uma solução cujas condições foram atendidas, significa que houve
sucesso no processo de solução. A partir desse ponto, há duas opções:
• Manter solução do Solver – Mantém os valores das células variáveis encontrados
pelo Solver. Confirma-se o resultado.
• Restaurar valores originais – As células permanecem com os valores existentes
antes de iniciar o Solver. Nada é modificado.
91
Figura 116
Na figura 116, veja que o Solver conseguir otimizar ao máximo a distribuição do
orçamento de 50.000,00, atendendo a todas as restrições. Mas como comprar 12,8636
impressoras ou 38,59 mesas?
10. Nesse caso, clique em Restaurar valores originais.
Para solucionar o problema empregando o valor máximo do orçamento de 50.000,00,
foi necessário fracionar os números. Nesse caso, precisamos inserir mais uma restrição:
os valores de todas as células variáveis têm que ser números inteiros.
11. Para inserir mais uma restrição, clique no botão Solver novamente. As configurações
permanecem inalteradas, até que sejam modificadas. Como vamos acrescentar uma
restrição, clique em Adicionar e insira a seguinte restrição:
• C2:C5=número para determinar que o intervalo que contém os valores das
quantidades deve ter números inteiros. Clique em OK e em Resolver.
92
Figura 117
12. Veja que todas as quantidades apresentam números inteiros e que todas as
condições foram satisfeitas, gerando 49.950,00 como valor total.
Figura 118
11.3 Problemas sem solução
Há problemas cuja solução não satisfaz todas as restrições. Por exemplo, se o
orçamento for de 35.000,00, será possível montar o escritório? Vamos verificar.
Na célula B9, substituímos o valor 50.000,00 por 35.000,00 e inicializamos o Solver.
Como a configuração anterior é mantida, basta clicar em Resolver.
93
Após esgotar todas as tentativas para solucionar o problema, surge a caixa Resultados
do Solver com a mensagem "Não foi possível ao Solver encontrar uma solução
viável", significando que a questão não foi solucionada.
Figura 119
Com isso, concluímos que o valor mínimo para adquirir todos os itens é o valor atual
da célula D6, ou seja, 39.150,00.
Vamos praticar! Exercício 9
94
12. Funções
Como já vimos nos cursos de Excel Básico e Excel Intermediário, desenvolver planilhas
envolve o trabalho constante com fórmulas. Existem fórmulas em que, apenas por meio
dos operadores matemáticos, podemos alcançar os resultados desejados. Porém, em
muitos casos, esses recursos não são suficientes para produzir o resultado necessário.
Em situações dessa natureza, devemos recorrer a um dos recursos mais empregados
para a geração de resultados por meio de fórmulas: as funções.
Você já aprendeu a trabalhar com algumas delas. Como você já sabe, as funções
devem seguir uma sintaxe.
Por ser uma fórmula, o primeiro caractere a ser inserido é o sinal de igual (=). Depois,
vem o nome da função, seguido dos seus argumentos, que devem ser colocados entre
parênteses e separados por ponto e vírgula.
12.1 Funções de texto
Essa categoria contém funções que manipulam valores de texto. Há diversas aplicações
para elas e seu entendimento é bastante fácil.
Vamos nos lembrar do operador que une ou concatena valores de texto, utilizado
quando se trabalha com textos em fórmulas: &. Quando desejar concatenar um texto que
não está em uma célula, digite-o entre aspas.
12.1.1 Função ESQUERDA
Essa função extrai caracteres a partir da esquerda até o número de caracteres
especificado de um texto. Por exemplo, na palavra “Petróleo”, ao se extrair os três
caracteres da esquerda, obtém-se “Pet”.
A sintaxe é a seguinte: =ESQUERDA(texto;[núm_caract]), onde:
• Texto – É a sequência de caracteres de texto que contém os caracteres que você
deseja extrair.
• Núm_caract – Especifica o número de caracteres que Esquerda deve extrair. Se
for omitido, será considerado 1.
95
Veja o exemplo a seguir:
Figura 120
Na figura 120, os códigos dos países foram formados pelos três primeiros caracteres
do nome do país. A fórmula de C2 é a seguinte: =ESQUERDA(A2;3).
12.1.2 Função DIREITA
Essa função extrai os últimos caracteres de uma sequência de texto.
A sintaxe é a seguinte: =DIREITA(texto;[núm_caract]), onde:
• Texto – É a sequência de caracteres de texto que contém os caracteres que você
deseja extrair.
• Núm_caract – Especifica o número de caracteres que Direita deve extrair. Se for
omitido, será considerado 1.
96
Veja o exemplo a seguir:
Figura 121
12.1.3. Função MAIÚSCULA
Como o próprio nome sugere, essa função converte todo o texto para letras
maiúsculas.
A sintaxe é a seguinte =MAIÚSCULA(texto), onde:
• Texto – É o texto que se deseja converter para maiúsculas e que pode ser uma
célula ou uma sequência de caracteres de texto.
Veja o exemplo a seguir:
Figura 122
97
12.1.4. Função MINÚSCULA
Essa função converte todo o texto para letras minúsculas.
A sintaxe é a seguinte =MINÚSCULA(texto), onde:
• Texto – É o texto que se deseja converter para maiúsculas. Texto pode ser uma
referência ou uma sequência de caracteres de texto.
Veja o exemplo a seguir:
Figura 123
12.1.5. Função PRI.MAIÚSCULA
Essa função converte o texto, deixando as iniciais de cada palavra em maiúscula e os
demais caracteres em minúsculo.
A sintaxe é a seguinte: =PRI.MAIÚSCULA(texto), onde:
• Texto – É o texto entre aspas, uma fórmula que retorna o texto ou uma referência
a uma célula que contenha o texto que você deseja colocar em maiúscula parcialmente.
98
Veja o exemplo a seguir:
Figura 124
12.1.6 Função LOCALIZAR
Essa função retorna o número da posição de um caractere em um texto, sempre da
esquerda para a direita, não distinguindo maiúsculas de minúsculas. Pode ser utilizada
em conjunto com outras funções de texto para retornar sequências de textos a partir de
um determinado caractere.
A sintaxe é a seguinte: =LOCALIZAR(texto_procurado;no_texto;[núm_
inicial]), onde:
• Texto_procurado – É o texto a ser localizado. Por exemplo, se quiser localizar a
letra "s" na palavra "Pressuposto", então "s" será o texto procurado.
• No_texto – É o texto que será pesquisado. Segundo nosso exemplo, "Pressuposto"
é o valor para esse argumento.
• Núm_inicial – É o caractere inicial a partir do qual será procurado um texto. Nesse
exemplo, se determinarmos como 6 o número inicial, então a letra "s" será pesquisada a
partir da letra "u", que é o sexto caractere. Se omitido, entende-se como 1.
Comentários
• Use LOCALIZAR para determinar o local de um caractere ou de uma sequência de
caracteres de texto em outra sequência, de modo que você possa usar as funções EXT.
TEXTO para alterar o texto.
99
• LOCALIZAR não diferencia maiúsculas de minúsculas. Se você não deseja uma
pesquisa que diferencie maiúsculas de minúsculas ou caracteres curinga, você pode
utilizar PROCURAR.
• Você pode utilizar caracteres curinga, como ponto de interrogação (?) e asterisco
(*), em texto_procurado. Um ponto de interrogação corresponde a qualquer caractere;
um asterisco corresponde a qualquer sequência de caracteres. Se você quiser localizar
um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.
• Se texto_procurado não for localizado, o valor de erro #VALOR! será retornado.
• Se núm_inicial for omitido, será equivalente a 1.
• Se núm_inicial não for maior do que 0 ou for maior do que o comprimento de
no_texto, o valor de erro #VALOR! será retornado.
• Use núm_inicial para ignorar um número de caracteres especificado. Usando
LOCALIZAR como exemplo, suponha que você esteja trabalhando com a sequência de
caracteres de texto “ARF0093.RoupaMasculina”. Para localizar o número do primeiro “R”
na parte descritiva da sequência de caracteres de texto, defina núm_inicial como 8, para
que a parte do texto relativa ao número de série não seja localizada. LOCALIZAR começa
com o caractere 8, procura texto_procurado no próximo caractere e retorna o número
9. LOCALIZAR sempre retorna o número de caracteres a partir do início de no_texto,
contando os caracteres ignorados, se núm_inicial for maior que 1.
Exemplo 1
Figura 125
Exemplo 2
Se o caractere procurado não for localizado, surgirá o valor de erro #VALOR!. Na
figura seguinte, a fórmula localiza a letra "a" na célula A2, e esta procura inicia-se a partir
100
do segundo caractere, pois 2 é o valor do terceiro argumento, que é Núm_inicial. Veja
que, em "Argentina-54", o número é 9, pois foi localizado o "a" que está após o "n". A
procura iniciou-se a partir do "r", que é o segundo caractere.
Figura 126
Exemplo 3
Agora, vamos extrair apenas os nomes dos países das células da coluna A. Como os
nomes estão no início da sequência de texto e os códigos vêm após, iniciamos com a
função ESQUERDA. O texto a ser pesquisado no primeiro caso é A2.
E o número de caracteres? Como os nomes dos países são formados por quantidades
diferentes de letras, é preciso ter uma base comum para determinar esse número. Como o
hífen (-) é o primeiro caractere após o nome de todos os países, ele será a base. Portanto,
a partir da esquerda, vamos extrair tantos caracteres quantos houver até o hífen menos
1. Menos 1 para excluir o hífen.
Na fórmula a seguir, a função LOCALIZAR serve como argumento para Núm_caract
da função ESQUERDA e tem a tarefa de retornar o número da posição do hífen. Quando
encontrado esse número, subtraímos 1 para excluir o próprio hífen da extração.
Figura 127
101
12.1.7 Função EXT.TEXTO
A partir da esquerda, essa função extrai determinado número de caracteres de um
texto, ou de uma sequência de texto, especificada por um número final. Por exemplo, use
EXT.TEXTO para extrair a palavra "América" do texto "Brasil - América do Sul".
A sintaxe é a seguinte =EXT.TEXTO(texto; núm_inicial;núm_caract), onde:
• Texto – É a sequência de texto de onde será extraído um outro texto.
• Núm_inicial – É o número da posição do primeiro caractere a ser extraído.
• Núm_caract – É a quantidade de caracteres a serem extraídos a partir de Núm_
inicial.
Comentários
• Se núm_inicial for maior do que o comprimento de texto, EXT.TEXTO retornará “”
(texto vazio).
• Se núm_inicial for menor do que o comprimento de texto, mas núm_inicial e núm_
caract excederem o comprimento de texto, EXT.TEXTO retornará os caracteres até o final
do texto.
• Se núm_inicial for menor do que 1, EXT.TEXTO retornará o valor de erro #VALOR!.
• Se núm_caract for negativo, EXT.TEXTO retornará o valor de erro #VALOR!.
Exemplo
No exemplo anterior (figura 127), quando extraímos o nome do país, utilizamos a
função ESQUERDA. Porém, para extrair o código não basta a função DIREITA, porque o
nome do país possui quantidades diferentes de caracteres, e a função LOCALIZAR sempre
localiza da esquerda para a direita.
Para obter os códigos, utilizamos o resultado obtido pela função LOCALIZAR como
argumento para Núm_inicial da função EXT.TEXTO.
102
Figura 128
12.1.8 Função CONCATENAR
Essa função agrupa duas ou mais cadeias de caracteres em uma única cadeia de
caracteres.
A sintaxe é a seguinte: =CONCATENAR(texto1;texto2;...), onde:
• Texto1; texto2; ... – São de 2 a 255 itens de texto a serem agrupados em um
único item de texto. Os itens de texto podem ser cadeia de caracteres, números ou
referências a células únicas.
Dica
• Você também pode usar o operador de cálculo & (e comercial), em vez da função
CONCATENAR, para agrupar itens de texto. Por exemplo, =A1&B1 retornará o mesmo
valor que =CONCATENAR(A1;B1).
103
Exemplo
Figura 129
12.2 Funções Matemáticas e Trigonométricas
Essas funções executam cálculos que envolvem matemática, como Soma, Somase,
funções de arredondamento etc. ou trigonometria, como Sen para calcular o seno e Cos
para o cosseno entre outras.
12.2.1 Função ARRED
Quando fazemos cálculos em planilhas e obtemos um resultado com várias casas
decimais, podemos exibi-los de acordo com a necessidade. É lógico, porém, que os valores
internos das células consideram todas as casas decimais. Por exemplo, se o valor de uma
célula é 10,988 e o formato eliminar as casas decimais, será exibido 11, embora todos os
cálculos baseados nessa célula considerem 10,988.
A função ARRED arredonda um número para cima, se o dígito for maior ou igual a 5;
ou para baixo, se for menor que 5, de acordo com o número de dígitos especificados.
A sintaxe é a seguinte: =ARRED(núm;núm_dígitos), onde:
• Núm – É o número que se deseja arredondar.
• Núm_dígitos – Especifica o número de dígitos para o qual você deseja
arredondar núm.
104
Comentários
• Se núm_dígitos for maior que 0, núm será arredondado para o número especificado
de casas decimais.
• Se núm_dígitos for 0, núm será arredondado para o inteiro mais próximo.
• Se núm_dígitos for menor que 0, núm será arredondado para a esquerda da vírgula
decimal.
Exemplo
Figura 130
12.2.2 Função ARREDONDAR.PARA.CIMA
Essa função arredonda para cima de acordo com o número de dígitos. A sintaxe e os
argumentos são os mesmos de ARRED. O número 10,941 arredondado para cima, com
2 dígitos, será 10,95.
A sintaxe é a seguinte: =ARREDONDAR.PARA.CIMA(núm;núm_dígitos),
onde:
• Núm – É qualquer número real que se deseja arredondar.
• Núm_dígitos – É o número de dígitos para o qual se deseja arredondar núm.
105
Comentários
• ARREDONDAR.PARA.CIMA funciona como ARRED, com a diferença de sempre
arredondar um número para cima.
• Se núm_dígitos for maior que 0, o número será arredondado para cima pelo número
de casas decimais especificado.
• Se núm_dígitos for 0, núm será arredondado para cima até o inteiro mais próximo.
• Se núm_dígitos for menor que 0, o número será arredondado para cima, à esquerda
da vírgula decimal.
Exemplo
Figura 131
12.2.3 Função ARREDONDAR.PARA.BAIXO
Como o próprio nome indica, essa função arredonda para baixo de acordo com o
número de dígitos especificados. A sintaxe e os argumentos são os mesmos da função
ARRED. O número 10.9899 arredondado para baixo, com 2 dígitos, será 10,98.
A sintaxe é a seguinte: =ARREDONDAR.PARA.BAIXO(núm;núm_dígitos),
onde:
• Núm - É qualquer número real que se deseja arredondar.
• Núm_dígitos – É o número de dígitos para o qual se deseja arredondar núm.
Comentários
• ARREDONDAR.PARA.BAIXO funciona como ARRED, com a diferença de sempre
arredondar um número para baixo.
106
• Se núm_dígitos for maior do que 0, o número será arredondado para baixo pelo
número de casas decimais especificado.
• Se núm_dígitos for 0, o número será arredondado para baixo, até o inteiro mais
próximo.
• Se núm_dígitos for menor do que 0, o número será arredondado para baixo, à
esquerda da vírgula decimal.
Exemplo
Figura 132
12.2.4 Função INT
Essa função leva em consideração apenas a parte inteira do número. Por exemplo, o
inteiro do número 55,001 é 55.
A sintaxe é a seguinte: =INT(núm;núm_dígitos), onde:
• Núm – É o número real que se deseja arredondar para baixo, até um inteiro.
Exemplo
Figura 133
107
12.2.5 Função SOMASE
Para somar os valores de um ou mais intervalos, utilizamos a função SOMA, que
permite inserir até 30 intervalos ou argumentos para somar.
E para somar os valores de um intervalo de acordo com um critério específico?
Veja o exemplo seguinte: Em uma coluna, existem vários itens repetidos, como nome
do produto. Em outra coluna, há as quantidades de cada item. Como calcular a quantidade
total de um item da lista?
Para solucionar questões desse tipo, utilizamos a função SOMASE, que soma os
valores existentes em um intervalo segundo um critério determinado.
A sintaxe é a seguinte =SOMASE(intervalo;critérios;[intervalo_soma]), onde:
• Intervalo – É o intervalo de células que se deseja calcular por critérios. As células
em cada intervalo deverão ser números e nomes, matrizes ou referências que contêm
números. Os espaços em branco e os valores de texto são ignorados.
• Critérios – São os critérios na forma de um número, expressão ou texto que define
quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como 32,
“32”, “>32” ou “maçãs”.
• Intervalo_soma – São as células reais a serem adicionadas, se as células
correspondentes no intervalo coincidirem com os critérios. Se intervalo_soma for omitido,
as células no intervalo serão avaliadas pelos critérios e adicionadas, se corresponderem
a eles.
Comentários
• Intervalo_soma não possui o mesmo tamanho e forma que o intervalo. As células
reais que foram adicionadas são determinadas utilizando-se o intervalo_soma na célula
superior, à esquerda, como a célula inicial, incluindo-se as células que correspondem ao
intervalo em tamanho e forma. Por exemplo:
108
Se o intervalo for
e intervalo_soma for
Então, as células reais serão
A1:A5
B1:B5
B1:B5
A1:A5
B1:B3
B1:B5
A1:B4
C1:D4
C1:D4
A1:B4
C1:C2
C1:D4
• Nos critérios, você pode utilizar caracteres curinga, como ponto de interrogação
(?) e asterisco (*). Um ponto de interrogação corresponde a qualquer caractere; um
asterisco corresponde a qualquer sequência de caracteres. Se você quiser localizar um
ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.
Exemplo
Para calcular o total das unidades da França em uma lista de países, devemos
pesquisar “França” no campo País e somar os valores que estiverem na mesma linha de
cada registro no campo Unidades, como mostra a fórmula da célula B22 da figura 134:
Figura 134
109
Onde:
B2:B18 – É o intervalo com todos os países, onde será pesquisado o item definido
como Critérios, A22, cujo valor é o texto França. A região a ser somada ou lntervalo_
soma é D2:D18, o intervalo que contém todas as quantidades.
O resultado dessa fórmula é 16, que corresponde ao total de unidades da França. Se
nomearmos o intervalo B2:B18 para País e D2:D18 para Unidades, poderemos utilizar
esses nomes na fórmula, que seria a seguinte:
=SOMASE(País;A22;unidades), produzindo o mesmo resultado.
Para calcular o valor total, ou seja, a soma do campo cujo rótulo é Total referente à
França, utilizamos a seguinte fórmula: =SOMASE(B2:B18;A22;F2:F18). Essa fórmula
contém apenas uma diferença com relação à anterior: o lntervalo_soma é F2:F18. Caso seja
renomeado para Total, a fórmula também poderá ser: =SOMASE(País;A22;Total).
O critério deve estar entre aspas, quando não fizer referência a uma célula. Se for
uma referência, esse critério não pode estar entre aspas; senão, servirá como critério o
próprio texto da referência e não o valor da célula.
Por exemplo, se A22 for o critério, o valor da célula é que será considerado, como nos
exemplos anteriores. Porém, se o critério for “A22”, então será pesquisado o próprio texto
que está entre aspas. Veja alguns exemplos de critérios:
• “B*” – Significa todos os que começam com b.
• “>=10” – Para soma de valores maiores ou iguais a 10.
• “>0” – Para soma de valores maiores que 0 (zero).
• “Suécia” – Para soma de todos os registros que forem diferentes de Suécia.
• “>”&A18 – Para soma de valores maiores que o valor da célula A18.
110
Quando for omitido o valor para o argumento lntervalo_soma, o intervalo a ser somado
será o especificado em Intervalo. A seguinte fórmula soma os totais do intervalo F2:F18
que forem maiores que 1.000:
Figura 135
Vamos praticar! Exercícios 10, 11 e 12
111
12.3. Funções Estatísticas
Essa categoria contém funções que fazem cálculos estatísticos. Algumas delas você
já conheceu no curso Básico, como Média, que retorna a média aritmética; Máximo, que
retorna o maior valor existente em uma lista de valores; Mínimo, que retorna o menor
valor.
12.3.1. Função CONT.NÚM
Essa função conta quantas células contêm números e os números na lista de
argumentos. Use CONT.NÚM para obter o número de entradas em um campo de número
que esteja em um intervalo ou matriz de números.
A sintaxe é a seguinte: =CONT.NÚM(valor1;valor2;...), onde:
• Valor1; valor2 ... – São argumentos de 1 a 255 que contêm diferentes tipos de
dados ou a eles se referem, mas somente os números são contados.
Comentários
• Os argumentos que são números, datas ou representações de números por extenso
são contados.
• Os valores lógicos e as representações de números por extenso digitados diretamente
na lista de argumentos são contados.
• Os argumentos que são valores de erro ou texto que não possam ser convertidos
em números são ignorados.
• Se um argumento for uma matriz ou referência, somente os números dessa matriz
ou referência serão contados. Células vazias, valores lógicos, texto ou valores de erro da
matriz ou referência são ignorados.
• Se você deseja contar valores lógicos, texto ou valores de erro, use a função CONT.
VALORES.
112
Exemplo
Figura 136
12.3.2 Função CONT.VALORES
Essa função calcula o número de células não vazias e os valores na lista de argumentos.
Use CONT.VALORES para calcular o número de células com dados em um intervalo ou
matriz.
A sintaxe é a seguinte: =CONT.VALORES(valor1;valor2;...), onde:
• Valor1; valor2;... – São argumentos de 1 a 255 que representam os valores que
você deseja calcular.
113
Comentários
• Um valor é qualquer tipo de informação, incluindo valores de erro e texto vazio (“”).
Um valor não inclui células vazias.
• Se um argumento for uma matriz ou referência, somente os valores dessa matriz ou
referência serão usados. As células vazias e os valores de texto da matriz ou referência
são ignorados.
• Se você não precisa calcular valores lógicos, texto ou valores de erro, use a função
CONT.NÚM.
Exemplo
Figura 137
114
12.3.3 Função CONTAR.VAZIO
Essa função conta o número de células vazias no intervalo especificado.
A sintaxe é a seguinte: =CONTAR.VAZIO(intervalo), onde:
• Intervalo – É o intervalo em que as células em branco serão contadas.
Células com fórmulas que retornam “” (texto vazio) também são contadas. Células
com valores nulos não são contadas.
Exemplo
Figura 138
115
12.3.4 Função CONT.SE
Essa função calcula o número de células não vazias em um intervalo que corresponde
a determinados critérios.
A sintaxe é a seguinte: =CONT.SE(intervalo;critérios), onde:
• Intervalo – É uma ou mais células para contar, incluindo números ou nomes,
matrizes ou referências que contêm números. Os campos em branco e valores de texto
são ignorados.
• Critérios – É o critério na forma de um número, expressão, referência de célula
ou texto que define que células serão contadas. Por exemplo, os critérios podem ser
expressos como 32, “32”, “>32”, “maçãs” ou B4.
Exemplo 1
Figura 139
116
Exemplo 2
Fórmulas CONT.SE utilizando caracteres curinga e manipulando valores
Figura 140
12.4 Funções de Pesquisa e Referência
Essa categoria é composta por funções que localizam valores em uma lista ou retornam
sua referência. Existem 18 funções nessa categoria. Dentre elas, vamos conhecer algumas
de grande utilidade no desenvolvimento de planilhas inteligentes.
A seguir, você conhecerá as funções PROCH, PROCV, ÍNDICE e CORRESP. Em muitos
casos, elas trabalham em conjunto, proporcionando excelentes resultados.
117
12.4.1 Função PROCH
Essa função localiza um valor na linha superior de uma tabela ou matriz de valores e
retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz.
Use PROCH quando seus valores de comparação estiverem localizados em uma linha
ao longo da parte superior de uma tabela de dados e você quiser observar um número
específico de linhas mais abaixo.
A sintaxe é a seguinte: =PROCH(valor_procurado;matriz_tabela;núm_índice_
lin;[procurar_intervalo]), onde:
• Valor_procurado – É o valor a ser localizado na primeira linha da tabela. Valor_
procurado pode ser um valor, uma referência ou uma sequência de caracteres de texto.
• Matriz_tabela – É uma tabela de informações onde os dados devem ser procurados.
Use uma referência para um intervalo ou um nome de intervalo.
– Os valores na primeira linha de matriz_tabela podem ser texto, números ou
valores lógicos.
– Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_
tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO,
VERDADEIRO. Em caso contrário, PROCH pode não retornar o valor correto. Se procurar_
intervalo for FALSO, a matriz_tabela não precisará ser ordenada.
– Textos em maiúsculas e minúsculas são equivalentes.
– Classifique os valores em ordem crescente, da esquerda para a direita. Para
obter mais informações, consulte Classificar dados.
• Núm_índice_lin – É o número da linha em matriz_tabela de onde o valor
correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da
primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da
segunda linha na matriz_tabela e assim por diante.
Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; se
núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará
o valor de erro #REF!.
118
• Procurar_intervalo – É um valor lógico que especifica se você quer que PROCH
localize uma correspondência exata ou aproximada. Se VERDADEIRO (1) ou omitido, uma
correspondência aproximada é retornada. Em outras palavras, se uma correspondência
exata não for localizada, o valor maior mais próximo que seja menor que o valor_
procurado será retornado. Se FALSO (0), PROCH encontrará uma correspondência exata.
Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado.
Comentários
• Se PROCH não localizar valor_procurado e procurar_intervalo for VERDADEIRO,
PROCH usará o maior valor que é menor do que o valor_procurado.
• Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_
tabela, PROCH retornará o valor de erro #N/D.
• Se procurar_intervalo for FALSO e valor_procurado for texto, você poderá usar os
caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um
ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com
qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou
asterisco real, digite um til (~) antes do caractere.
Exemplo
Figura 141
119
12.4.2 Função PROCV
Essa função localiza um valor na primeira coluna de uma matriz de tabela e retorna
um valor na mesma linha de outra coluna na matriz da tabela.
O V em PROCV significa vertical. Use PROCV em vez de PROCH, quando os valores
da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados
que você deseja procurar.
A sintaxe é a seguinte: =PROCV(valor_procurado;matriz_tabela;núm_índice_
col;[procurar_intervalo]), onde:
• Valor_procurado – É o valor a ser procurado na primeira coluna da matriz da
tabela (Matriz: usada para criar fórmulas únicas que produzem vários resultados ou que
operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo
de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de
constantes usado como um argumento.). O valor_procurado pode ser um valor ou uma
referência. Se o valor_procurado for menor do que o menor valor da primeira coluna de
matriz_tabela, o PROCV retornará o valor de erro #N/D.
• Matriz_tabela – São duas ou mais colunas de dados. Use uma referência para um
intervalo ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os
valores procurados por valor_procurado. Os valores podem ser texto, números ou valores
lógicos. Textos em maiúsculas e minúsculas são equivalentes.
• Núm_índice_col – É o número da coluna em matriz_tabela a partir do qual o
valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor
na primeira coluna em matriz_tabela, um núm_índice_coluna de 2 retornará o valor na
segunda coluna em matriz_tabela e assim por diante. Se núm_índice_coluna for:
– Menor que 1, PROCV retornará o valor de erro #VALOR!.
– Maior que o número de colunas em matriz_tabela, PROCV retornará o valor de
erro #REF!.
• Procurar_intervalo – É um valor lógico que especifica se você quer que PROCV
localize uma correspondência exata ou aproximada.
– Se VERDADEIRO ou omitido, uma correspondência exata ou aproximada é
retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo
que seja menor que o valor_procurado será retornado.
– Os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem
ascendente. Em caso contrário, PROCV poderá não retornar o valor correto. Para obter
mais informações, consulte Classificar dados.
120
– Se FALSO, PROCV encontrará somente uma correspondência exata. Nesse caso,
os valores na primeira coluna da matriz_tabela não precisam ser classificados. Se houver
dois ou mais valores na primeira coluna de matriz_tabela que não coincidam com o valor_
procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata
for localizada, o valor de erro #N/D será retornado.
Comentários
• Ao procurar valores de texto na primeira coluna da matriz_tabela, certifique-se de
que os dados da primeira coluna da matriz_tabela não tenham espaços à esquerda ou
de fim de linha, uso inconsistente de aspas normais (‘ou“) e curvas (‘ou“) ou caracteres
não imprimíveis. Nesses casos, a função PROCV pode fornecer um valor correto ou não
esperado. Para obter mais informações, consulte Tirar e Arrumar.
• Ao procurar valores de número ou data, certifique-se de que os dados da primeira
coluna da matriz_tabela não estejam armazenados como valores de texto. Nesse
caso, a função PROCV pode fornecer um valor correto ou não esperado. Para obter
mais informações, consulte Converter números armazenados como texto em
números.
• Se procurar_intervalo for FALSO e valor_procurado for texto, você poderá usar os
caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um
ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com
qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou
asterisco real, digite um til (~) antes do caractere.
Exemplo
Figura 142
Vamos praticar! Exercício 13
121
12.4.3 Função ÍNDICE
Essa função retorna um valor ou a referência para um valor de dentro de uma tabela
ou intervalo. Há duas formas da função ÍNDICE: matricial e de referência.
Se quiser
Consulte
Retornar o valor de uma célula ou matriz de células especificadas
Retornar a referência para células especificadas
Forma matricial
Forma de referência
Forma matricial
Retorna o valor de um elemento em uma tabela ou matriz selecionadas pelos índices
de número de linha e coluna (Matriz: usada para criar fórmulas únicas que produzem
vários resultados ou que operam em um grupo de argumentos organizados em linhas e
colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de
matriz é um grupo de constantes usado como um argumento.).
Use a forma de matriz se o primeiro argumento de ÍNDICE for uma constante de
matriz.
A sintaxe é a seguinte: =INDICE(matriz;núm_linha;núm_coluna), onde:
• Matriz – É um intervalo de células ou uma constante de matriz.
– Se a matriz contiver apenas uma linha ou coluna, o argumento núm_linha ou
núm_coluna correspondente será opcional.
– Se a matriz tiver mais de uma linha e mais de uma coluna e apenas núm_linha
ou núm_coluna for usado, ÍNDICE retornará uma matriz referente à linha ou coluna
inteira da matriz.
• Núm_linha – Seleciona a linha na matriz a partir da qual um valor deverá ser
retornado. Se núm_linha for omitido, núm_coluna será obrigatório.
• Núm_coluna – Seleciona a coluna na matriz a partir da qual um valor deverá ser
retornado. Se núm_coluna for omitido, núm_linha será obrigatório.
122
Comentários
• Se os argumentos núm_linha e núm_coluna forem usados, ÍNDICE retornará o valor
contido na célula que estiver no ponto de interseção entre núm_linha e núm_coluna.
• Se você definir núm_linha ou núm_coluna como 0 (zero), ÍNDICE retornará a matriz
de valores referente à coluna ou linha inteira respectivamente. Para usar valores retornados
como uma matriz, insira a função ÍNDICE como uma fórmula de matriz (Fórmula de matriz:
fórmula que executa vários cálculos em um ou mais conjuntos de valores e retorna um
único resultado ou vários resultados. As fórmulas de matriz ficam entre chaves { } e são
inseridas pressionando-se CTRL+SHIFT+ENTER.) em um intervalo horizontal de células
para uma linha e em um intervalo vertical de células para uma coluna. Para inserir uma
fórmula de matriz, pressione CTRL+SHIFT+ENTER.
• Núm_linha e núm_coluna devem fazer referência a uma célula dentro de uma
matriz. Em caso contrário, ÍNDICE retornará o valor de erro #REF!.
Exemplo
Figura 143
Forma de referência
Retorna a referência da célula na interseção de linha e coluna específicas. Se a
referência for formada por seleções não adjacentes, você poderá escolher a seleção que
deseja observar.
A sintaxe é a seguinte: =INDICE(ref;núm_linha;núm_coluna;núm_área),
onde:
123
• Ref – É uma referência a um ou mais intervalos de célula.
– Se você estiver inserindo um intervalo não adjacente para a ref, coloque ref
entre parênteses.
– Se cada área na referência contiver apenas uma linha ou coluna, o argumento
núm_linha ou núm_coluna, respectivamente, será opcional. Por exemplo, para uma
referência de linha única, use ÍNDICE(ref;núm_coluna).
• Núm_linha – É o número da linha em ref de onde será fornecida uma referência.
• Núm_coluna – É o número da coluna em ref de onde será fornecida uma
referência.
• Núm_área – Seleciona um intervalo em ref do qual deve ser retornada a interseção
de núm_linha com núm_coluna. A primeira área selecionada ou inserida recebe o número
1, a segunda recebe o número 2 e assim por diante. Se núm_área for omitido, ÍNDICE
usará a área 1.
– Por exemplo, se ref descrever as células (A1:B4;D1:E4;G1:H4), então núm_área
1 representará o intervalo A1:B4, núm_área 2 representará o intervalo D1:E4 e núm_área
3 representará o intervalo G1:H4.
Comentários
• Depois que ref e núm_área tiverem selecionado um intervalo específico, núm_
linha e núm_coluna selecionam uma célula específica: núm_linha 1 é a primeira linha do
intervalo, núm_coluna 1 é a primeira coluna e assim por diante. A referência que ÍNDICE
retorna é a interseção entre núm_linha e núm_coluna.
• Se você definiu núm_linha ou núm_coluna como 0, ÍNDICE retorna a referência
para a coluna ou linha inteira respectivamente.
• Núm_linha, núm_coluna e núm_área devem apontar para uma célula na referência;
senão, ÍNDICE retornará o valor de erro #REF!. Se núm_linha e núm_coluna forem
omitidos, ÍNDICE retornará a área em referência especificada por núm_área.
• O resultado da função ÍNDICE é uma referência e é interpretado como tal por
outras fórmulas. Dependendo da fórmula, o valor retornado por ÍNDICE pode ser
usado como uma referência ou como um valor. Por exemplo, a fórmula de macro
CÉL(“largura”;ÍNDICE(A1:B2;1;2)) é equivalente a CÉL(“largura”;B1). A função CÉL
usa o valor retornado por ÍNDICE como uma referência de célula. Por outro lado,
uma fórmula tal como 2*ÍNDICE(A1:B2;1;2) traduz o valor retornado por ÍNDICE no
número da célula B1.
124
Exemplo
Figura 144
12.4.4 Função CORRESP
Essa função retorna a posição relativa de um item em uma matriz que coincide com
um valor determinado em uma ordem específica (Matriz: usada para criar fórmulas únicas
que produzem vários resultados ou que operam em um grupo de argumentos organizados
em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma
constante de matriz é um grupo de constantes usado como um argumento.). Use
CORRESP em vez de uma das funções PROC, quando precisar da posição de um item em
um intervalo em lugar do item propriamente dito.
A sintaxe é a seguinte: =CORRESP(valor_procurado;matriz_procurada;[tipo_
correspondência]), onde:
• Valor_procurado – É o valor utilizado para localizar o valor desejado em uma
tabela.
125
• Matriz_procurada – É um intervalo contíguo de células que contém valores
possíveis de procura. Matriz_procurada precisa ser uma matriz ou uma referência de
matriz.
• Tipo_correspondência – É o número -1, 0 ou 1. Tipo_correspondência especifica
como o Microsoft Excel corresponde a valor_procurado com os valores contidos em
matriz_procurada.
– Se tipo_correspondência for 1, CORRESP localizará o maior valor que for menor
que ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem
ascendente: ...-2, -1, 0, 1, 2, ...A-Z, FALSO, VERDADEIRO.
– Se tipo_correspondência for 0, CORRESP localizará o primeiro valor que for
exatamente igual a valor_procurado. Matriz_procurada pode ser colocada em qualquer
ordem.
– Se tipo_correspondência for -1, CORRESP localizará o menor valor que seja
maior ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem
decrescente: VERDADEIRO, FALSO, Z-A, ...2, 1, 0, -1, -2, ... e assim por diante.
– Se tipo_correspondência for omitido, será equivalente a 1.
Comentários
• CORRESP retorna a posição do valor coincidente em matriz_procurada e não o
valor propriamente dito. Por exemplo: CORRESP(“b”;{”a”.”b”.”c”};0) retorna 2, a posição
relativa de “b” na matriz {”a”.”b”.”c”}.
• CORRESP não faz distinção entre letras maiúsculas e minúsculas, quando estiver
fazendo a correspondência entre valores de texto.
• Se CORRESP não conseguir localizar um valor coincidente, ele fornecerá o valor de
erro #N/D.
• Se tipo_correspondência for 0 e valor_procurado for um texto, você poderá utilizar
caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um
ponto de interrogação corresponde a qualquer caractere; um asterisco corresponde a
qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou
asterisco real, digite um til (~) antes do caractere.
126
Exemplo
Figura 145
12.5 Funções de Banco de Dados
No trabalho com planilhas, é comum termos que saber qual é o valor total de um ou
mais itens de uma lista ou banco de dados.
Você já se deparou com uma situação em que precisava saber qual era a quantidade
vendida ou comprada de um produto em determinado período, ou qual foi o maior valor
pago por uma mercadoria específica em um ano, mês ou semana, ou em qualquer outro
período?
Situações como essas e muitas outras envolvendo banco de dados são frequentes no
dia a dia do desenvolvimento de planilhas. Para resolver questões desse gênero, existem
as funções de Banco de Dados. Para utilizar uma dessas funções, é necessário haver uma
lista ou banco de dados e uma região de critérios, que podem estar na mesma planilha
ou em outras planilhas e até mesmo em pastas (arquivos) diferentes.
As funções de Banco de Dados BDMÉDIA, BDCONTAR, BDMÍN, BDMÁX, BDSOMA e
BDVARP possuem os mesmos argumentos, descritos a seguir:
• Banco de dados – É o intervalo de células da lista ou do banco de dados. Um banco
de dados é uma lista de dados relacionados, cujas linhas de informações relacionadas
são os registros e as colunas de dados são os campos. A primeira linha da lista contém
os rótulos de cada coluna.
127
• Campo – Indica a coluna que será usada na função. O campo pode ser estabelecido
como texto com o rótulo da coluna entre aspas, como “Idade” ou “Rendimento”, ou como
um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a
primeira coluna, 2 para a segunda coluna e assim por diante.
• Critérios – Intervalo de células que contém as condições especificadas. Você pode
usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos
um rótulo de coluna e pelo menos uma célula abaixo do rótulo de coluna para especificar
uma condição para a coluna.
12.5.1 Função BDMÉDIA
Essa função calcula a média dos valores em um campo (coluna) de registros em uma
lista ou banco de dados que atendam às condições especificadas.
A sintaxe a seguinte: =BDMÉDIA(banco-dados;campo;critérios)
Exemplo
O exemplo abaixo calcula média utilizando o campo Total do produto Relógio e país
Itália.
Figura 146
128
12.5.2 Função BDCONTAR
Essa função conta as células que contêm números em um campo (coluna) de registros
em uma lista ou banco de dados que atendam às condições especificadas.
O argumento de campo é opcional. Se o campo for omitido, BDCONTAR contará todos
os registros no banco de dados que atendam aos critérios.
A sintaxe é a seguinte: =BDCONTAR(banco-dados;campo;critérios)
Exemplo
O exemplo seguinte conta o campo Unitário referente aos produtos que comecem
com C* e países que comecem com I*.
Figura 147
12.5.3 Função BDMÍN
Essa função retorna o menor número em um campo (coluna) de registros em uma
lista ou banco de dados que atenda às condições especificadas.
A sintaxe é a seguinte: =BDMÍN(banco-dados;campo;critérios)
129
Exemplo
O exemplo seguinte exibe o menor valor do campo Total de acordo com os critérios:
produtos que comecem com R* do país Argentina.
Figura 148
12.5.4 Função BDMÁX
Essa função retorna o maior número em um campo (coluna) de registros em uma lista
ou banco de dados que atenda às condições especificadas.
A sintaxe é a seguinte: =BDMÁX(banco-dados;campo;critérios)
Exemplo
O exemplo seguinte exibe o maior valor do campo Total de acordo com os critérios:
produtos que comecem com R* do país Argentina.
130
Figura 149
12.5.5 Função BDMULTIPL
Essa função multiplica os valores em um campo (coluna) de registros em uma lista ou
banco de dados que atendam às condições especificadas.
A sintaxe é a seguinte: =BDMULTIPL(banco-dados;campo;critérios)
Exemplo
O exemplo seguinte multiplica os dados que estão no campo QTDE de acordo com os
critérios: produto Relógio e todos os países que começam com J*.
131
Figura 150
12.5.6 Função BDSOMA
Essa função adiciona os números em um campo (coluna) de registros em uma lista
ou banco de dados que atendam às condições especificadas.
A sintaxe é a seguinte: =BDSOMA(banco-dados;campo;critérios)
Exemplo
O exemplo seguinte soma os valores que estão no campo Unitário para os critérios:
produtos que começam com C* do país Inglaterra.
132
Figura 151
12.6 Funções de Informações
Você já deve ter se deparado com valores de erro em fórmulas.
Esses valores podem ser tratados por funções de informações, que permitem testar
valores e retornam VERDADEIRO, se o valor testado corresponder ao tipo de informação
procurado pela função. As funções dessa categoria verificam diversos tipos de erros.
Na tabela a seguir, veja os erros que podem ser gerados nas fórmulas e sua origem.
Erros
Ocorrência
#####
Coluna não é larga o bastante ou quando é usada uma data ou hora negativa.
#DIV/0!
Número é dividido por zero (0).
#N/A
#NOME?
#NULL!
Valor não está disponível para uma função ou fórmula.
O Microsoft Office Excel não reconhece o texto em uma fórmula.
Especifica uma interseção de duas áreas que não se interceptam. O operador de
interseção é um espaço entre referências.
133
Erros
#NUM!
Ocorrência
Valores numéricos inválidos em uma fórmula ou função.
Referência de célula não é válida. (Referência de célula é o conjunto de
#REF!
coordenadas que a célula abrange em uma planilha. Por exemplo, a referência da
célula que aparece na interseção da coluna B e linha 3 é B3.)
Usado o tipo errado de argumento ou operando. (Argumento são os valores que
uma função usa para executar operações ou cálculos. O tipo de argumento que
#VALOR!
uma função usa é específico à função. Os argumentos comuns usados em funções
incluem números, texto, referências de célula e nomes./ Operando são itens nos
dois lados de um operador em uma fórmula. No Excel, os operandos podem ser
valores, referências de célula, nomes, rótulos e funções.)
A seguir, vamos conhecer uma das funções de informações que verifica a ocorrência
de um valor de erro.
12.6.1 Função ÉERROS
Essa função retorna VERDADEIRO, se o valor testado retornar qualquer tipo de erro.
A sintaxe é a seguinte: =ÉERROS(valor), onde:
• Valor – É o valor a ser testado. Pode ser uma fórmula, uma célula ou um nome.
Exemplo
Se dividirmos um número por 0 (zero), o resultado da fórmula será #DIV/0!. A fórmula
a seguir retorna VERDADEIRO, se o valor da célula B1 for um erro; ou FALSO, caso o erro
não exista.
Figura 152
134
Se você estiver perguntando sobre o que fazer com o resultado VERDADEIRO, veja a
fórmula na imagem 153.
Essa fórmula contém a função SE com o teste lógico éerros (B1). Se o teste for
VERDADEIRO, surgirá o texto “Valor Inválido”, chamando atenção para o erro. Caso o
teste lógico seja FALSO, isto é, se o valor de B1 não for um erro, surgirá o texto “Certo”.
Figura 153
Como o retorno de ÉERROS sempre será um valor lógico, não é necessário especificar,
no teste lógico da função SE, a comparação por meio de um operador lógico, que deixaria
a fórmula da seguinte maneira:
=SE( ÉERROS (B1)=Verdadeiro; “Valor Inválido”; “Certo” )
Observe que as duas fórmulas produzem o mesmo resultado. Agora, é preciso
fazer desaparecer o “#DIV/0!” de B1, que, atualmente, contém a fórmula =A1/A2.
Para isso, no lugar do B1 da fórmula que trata o erro, inserimos o próprio cálculo que
causa o erro, que é A1/A2, e transferimos toda a fórmula para a célula B1, que ficará da
seguinte maneira:
=SE(ÉERROS(A1/A2) ; “Valor Inválido”; “Certo”)
Retornando “Valor Inválido”, caso o valor de A1 dividido por A2 seja um erro; e
“Certo”, se o erro não ocorrer, o que será possível se A2 não for O (zero), como mostra
a figura a seguir.
Figura 154
135
Mas a questão é a seguinte: Qual o resultado de A1 dividido por A2. Pois esse “Certo”
não mostra o resultado da fórmula; só indica que ela não está errada. Veja: se não houver
um erro, qual deverá ser o valor da célula no lugar do texto “Certo”? A divisão.
Então, repetimos o próprio cálculo no lugar do “Certo” na fórmula, que passa a ser,
definitivamente, assim:
=SE(ÉERROS(A1/A2) ; “Valor Inválido”;A1/A2)
Figura 155
Vamos resumir os passos anteriores:
• Iniciamos com a função SE e colocamos a condição que testa, pela função ÉERROS,
se o cálculo a ser feito será qualquer valor de erro; na resposta verdadeira, ou seja, se
o teste acusar um valor de erro, indicamos o valor que deverá surgir: em nosso caso, foi
“Valor inválido”. Na resposta para o valor FALSO, ou seja, se não houver um erro no valor
testado, repetimos o próprio valor testado pela função ÉERROS. Nesse caso, A1/A2.
12.7 Funções Financeiras
Essa categoria de funções executa operações envolvendo cálculos financeiros, como
encontrar o valor presente ou a taxa de juros de uma aplicação VF.
12.7.1 Função VF
Essa função retorna o valor futuro de um investimento de acordo com os pagamentos
periódicos e constantes e com uma taxa de juros constante.
A sintaxe é a seguinte: =VF(taxa;nper;pgto;[vp];[tipo]), onde:
136
• Taxa – É a taxa de juros por período.
• Nper – É o número total de períodos de pagamento em uma anuidade.
• Pgto – É o pagamento feito a cada período, não podendo mudar durante a vigência
da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou
taxa. Se pgto for omitido, você deverá incluir o argumento vp.
• Vp – É 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 – É o número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo
for omitido, será considerado 0.
Definir tipo para
Se os vencimentos forem
0
No final do período
1
No início do período
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 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.
Exemplo
Figura 156
137
12.7.2 Função NPER
Essa função retorna o número de períodos para investimento de acordo com
pagamentos constantes e periódicos e uma taxa de juros constante.
A sintaxe é a seguinte: =NPER(taxa;pgto;vp;[vf];[tipo]), onde:
• Taxa – É a taxa de juros por período.
• Pgto – É o pagamento feito em cada período, não podendo mudar durante a
vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra
tarifa ou taxa.
• Vp – É o valor presente ou atual de uma série de pagamentos futuros.
• Vf – É 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 – É o número 0 ou 1 e indica as datas de vencimento.
Exemplo
Figura 157
138
12.7.3 Função PGTO
Essa função retorna o pagamento periódico de uma anuidade de acordo com
pagamentos constantes e com uma taxa de juros constante.
A sintaxe é a seguinte: =PGTO(taxa;nper;vp;[vf];[tipo]), onde:
• Taxa – É a taxa de juros por período.
• Nper – É o número total de pagamentos pelo empréstimo.
• Vp – É o valor presente de uma série de pagamentos futuros.
• Vf – É 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 determinado empréstimo, por
exemplo, é 0).
• Tipo – É o número 0 ou 1 e indica as datas de vencimento.
Comentários
• O pagamento retornado por PGTO inclui o principal e os juros. Não inclui taxas,
pagamentos de reserva ou tarifas, às vezes associados a empréstimos.
• 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.
139
Exemplo
Figura 158
12.7.4 Função VP
Essa função 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.
A sintaxe é a seguinte: =VP(taxa;nper;[vf];[tipo]), onde:
• Taxa – É a taxa de juros por período. Por exemplo, se você tiver um empréstimo
para um automóvel com taxa de juros de 10% ao ano e fizer pagamentos mensais, sua
taxa de juros mensal será de 10%/12 ou 0,83%. Como taxa, você deve inserir 10%/12
ou 0,83% ou 0,0083 na fórmula.
• Nper – É 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 – É 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
140
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ê deve inserir -263,33 na fórmula como pgto.
Se pgto for omitido, você deverá incluir o argumento vf.
• Vf – É 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 – É o número 0 ou 1 e indica as datas de vencimento.
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.
• 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 -1.000, se você for o depositante; e
pelo argumento 1.000, 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: (pgto * nper) + vp + vf = 0
141
Exemplo
Figura 159
12.7.5 Função TAXA
Essa função retorna a taxa de juros por período de uma anuidade. TAXA é calculada
por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não
convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro
#NÚM!.
A sintaxe é a seguinte: =TAXA(nper;pgto;vp;[vf];[tipo];[estimativa]), onde:
• Nper – É o número total de períodos de pagamento em uma anuidade.
• Pgto – É o pagamento feito em cada período, não podendo mudar durante a
vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhum outro
tributo ou taxa. Se pgto for omitido, você deverá incluir o argumento vf.
• Vp – É o valor presente — o valor total correspondente ao valor atual de uma série
de pagamentos futuros.
• Vf – É 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 – É o número 0 ou 1 e indica as datas de vencimento.
142
• Estimativa – É a estimativa estabelecida para a taxa.
– Se você omitir estimativa, esse argumento será considerado 10%.
– Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, a
TAXA converge se a estimativa estiver entre 0 e 1.
Comentário
• 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.
Exemplo
Figura 160
143
13. Tabela De Dados
Tabela de dados é um recurso que exibe os valores de uma fórmula de acordo com
algumas variáveis para uma ou duas de suas referências. Digamos que você vai comprar
um carro e precisa calcular quais serão os pagamentos mensais do financiamento de
acordo com diversos prazos.
O Excel permite criar uma tabela de dados com uma ou duas variáveis de entrada.
13.1 Tabela de dados com uma variável de entrada
Para calcular os diversos pagamentos mensais de acordo com os diferentes prazos,
crie uma planilha da seguinte maneira:
1. Na célula B4, digite o valor do financiamento, por exemplo, 18.000.
2. Em B5, digite a taxa de juros, que é de 5%.
3. Em B6, digite um dos prazos, que é de 12 meses.
4. Prepare a planilha para criar a tabela com uma variável de entrada, inserindo, na célula
C8, a seguinte fórmula, que será utilizada pela variável de entrada: =PGTO(B5;B6;B4).
5. No intervalo B9:B12, digite os diversos prazos de financiamentos: 18, 24, 30 e 36
meses.
Figura 161
144
6. Agora, do lado de cada prazo, vamos calcular os valores referentes aos respectivos
pagamentos mensais. Essa será a tabela que contém uma única variável, o prazo de
pagamento.
7. Selecione o intervalo B8:C12, que é a região com a fórmula e as células com os
prazos.
8. Na guia Dados, grupo Ferramenta de dados, clique em Teste de Hipóteses.
Em seguida, clique em Tabela de Dados.
Figura 162
9. Na caixa de diálogo Tabela de Dados, precisamos definir qual será a célula de
entrada da linha ou da coluna. Nesse caso, é a célula B6, que se refere ao número de
períodos (quantidade de pagamentos).
10. Clique em OK.
Figura 163
145
11. Quando é criada a tabela, o Excel insere a fórmula com a função TABELA, cujos
argumentos são a linha e a coluna da célula de entrada. Como, nessa fórmula, há somente
a entrada da coluna, a fórmula fica da seguinte maneira: =TABELA(;B6).
Os valores são negativos por se referirem a um débito para quem contrai o
financiamento. Formate a tabela do modo que preferir.
Figura 164
Se precisar excluir a tabela, é preciso selecioná-la inteira para, somente depois,
excluí-la.
Se quiser, altere os valores da fórmula ou a própria fórmula para visualizar diversos
resultados diferentes.
12. Renomeie a planilha com o nome de Tabela_dados e salve a pasta de
trabalho.
146
13.2 Tabela de dados com duas variáveis de entrada
Para visualizar os valores com prazos e taxas diferentes, é preciso criar a tabela com
duas variáveis de entrada. Vamos usar uma cópia da planilha do exemplo anterior.
1. Insira as taxas 7%, 8%, 9% e 10% no intervalo C8:F8. A fórmula da tabela é a
mesma que a anteriormente utilizada: =TABELA(;B6).
Figura 165
2. Selecione o intervalo que inclui todos os valores de entrada e a fórmula. Nesse
caso, o intervalo é B8:F12.
3. Na guia Dados, grupo Ferramenta de dados, clique em Teste de Hipóteses e,
em seguida, em Tabela de Dados.
4. Na caixa de diálogo Tabela de Dados, defina B5 como célula de entrada da linha
e B6 como célula de entrada da coluna, pois as taxas estão na linha e os prazos em
colunas.
5. Clique em OK.
Figura 166
147
Figura 167
6. Renomeie a planilha de Tabela_dados1 e salve a pasta de trabalho.
14. Formulários personalizados
Criar planilhas para consulta de dados é uma tarefa interessante, pois é possível
determinar a precisão das entradas escolhidas pelo usuário por meio da criação de
formulários personalizados.
Você já deve ter notado que, nas caixas de diálogo do Windows, há controles que
permitem ao operador determinar as entradas e, após confirmá-las, executar a ação de
acordo com a configuração especificada.
No Excel, há vários botões de controle, como os botões de opções, que permitem
selecionar apenas uma opção; caixas de seleção que serão ou não marcadas; caixas de
edição onde digitamos valores etc.
A seguir, vamos criar formulários personalizados para consulta de dados. Primeiramente,
é necessário exibir a guia Desenvolvedor por meio dos seguintes comandos:
1. Clique no botão Office e em Opções do Excel.
2. Clique em Mais Usados e, no item Opções principais para o trabalho com o
Excel, habilite a caixa Mostrar Guia Desenvolvedor na Faixa de Opções.
A planilha Preços (figura 168) contém preços de custo e preços de venda, que variam
de acordo com a cidade e conforme a margem de lucro e o estoque de cada modelo.
148
Essa planilha servirá como base de consulta por meio dos controles de formulários,
que serão criados na planilha Pesquisa.
Figura 168
Para facilitar o trabalho, algumas regiões da planilha foram nomeadas de acordo com
a seguinte tabela:
Células
Nome
A8:A16
Modelos
B8:B16
Custo
C8:F16
Venda
G8:G16
Estoque
14.1 Criando formulário
1. Abra a pasta de trabalho Exemplos e selecione planilha Preços para observar
as informações.
2. Selecione uma planilha Pesquisa.
3. Clique na guia Desenvolvedor, grupo Controles, e no botão Inserir
4. Clique na ferramenta Caixa de combinação
se transforma em uma cruz fina.
149
.
. Note que o ponteiro do mouse
5. Posicione o ponteiro do mouse no canto superior esquerdo da célula C4, clique,
arraste-o para a direita até o final da célula e solte-o. Observe que surgiu um objeto
chamado caixa de combinação.
Figura 169
6. Clique em qualquer célula e, depois, na caixa de combinação que você acabou de
criar. Note que a caixa está vazia. Será necessário determinar um intervalo de entrada,
que consiste em um intervalo de células que contém os valores que devem surgir na caixa
de combinação.
7. Clique com o botão direito do mouse na caixa de combinação e em Formatar
controle.
Figura 170
Quando precisar formatar os controles de um objeto, este é um dos métodos mais
práticos de fazê-lo.
150
8. Na caixa de diálogo Formatar objeto, na guia Controle, que já deve estar
ativada, configuraremos os controles referentes à caixa de combinação.
• Em Intervalo de entrada, inserimos a coluna que contém os valores a serem
listados, ou seja, os modelos dos carros. Digite Modelos, que é o intervalo nomeado na
planilha Preços.
• Em Vinculo da célula, digite G4, que será a base a ser referenciada nas fórmulas. O
vínculo da célula indica a posição que o item selecionado ocupa no intervalo de entrada.
• Em Linhas suspensas, digite 5 e marque Sombreamento 3-D.
9. Clique em OK.
Figura 171
10. Se precisar redimensionar o objeto, clique com o botão direito do mouse sobre ele
e pressione a tela <ESC> ao surgir o menu de atalho. Depois, arraste um dos seis pontos
ao redor do objeto para a direita, para a esquerda, para cima ou para baixo.
11. Agora, clique na caixa de combinação e veja que são listados todos os modelos,
com cinco itens por vez. Por haver mais itens que a quantidade listada, surge uma barra
de rolagem vertical. Selecione Vectra e veja que, na célula G4, surge o número 4, porque
Vectra é o quarto item da lista de modelos. Se escolher Línea, o valor de G4 será 1.
151
Figura 172
12. Digite Estoque
=ÍNDICE(Estoque;G4).
na
célula
C6
e,
em
D6,
insira
a
fórmula:
Como a função Índice retorna o valor de uma célula de acordo com a posição dessa
célula em uma matriz, determinamos Estoque como matriz e G4 como o número da
linha.
Essa fórmula retorna o valor da célula que estiver na linha cujo número é indicado
pela célula G4 na região que contém todos os estoques.
Como Vectra é o quarto modelo e 10 é o quarto item do estoque, então este será o
resultado da fórmula.
Figura 173
13. Em C8, digite Preço de custo e, em D8, insira a fórmula =ÍNDICE(Custo;G4),
que retorna o custo do carro.
14. Em C10, digite Preço de Venda. Antes de inserir a fórmula, lembre-se de que
existem quatro preços de venda, um para cada cidade. Então, vamos criar um botão de
opção para cada cidade.
152
15. Clique na guia Desenvolvedor, grupo Controles, e no botão Inserir
16. Clique na ferramenta Botão de opção
.
.
17. Note que o ponteiro do mouse se transforma em uma cruz fina.
18. Posicione o ponteiro do mouse no canto superior esquerdo da célula D1, clique,
arraste-o para a direita até o final da célula E1 e solte-o. Observe que surgiu um objeto
chamado Botão de opção 1 (se o número não for 1, não se preocupe).
19. Clique nesse objeto com o botão direito do mouse e escolha Formatar
controle.
20. Determine G5 como vínculo da célula e marque Sombreamento 3-D.
21. Clique em OK.
Figura 174
22. Com o objeto ainda selecionado, digite São Paulo e clique em qualquer célula ou
pressione a tecla <ESC> duas vezes.
23. Faça mais três botões de opção como esse, sem precisar formatar os controles,
que já vêm com a formatação do primeiro. Conforme você cria o objeto, ele deve conter o
nome da próxima cidade de acordo com a ordem que está na planilha. Portanto, ao criar
o segundo botão, ele deverá ser de Curitiba; o terceiro de Goiânia; e o quarto de Natal.
Escolha o local apropriado para cada um.
24. Depois, clique no botão São Paulo e veja que, em G5, há o número 1, que
corresponde à primeira cidade. Ao selecionar outra cidade, a anterior será desmarcada,
porque somente um botão de opção pode estar selecionado por vez.
153
25. A célula G5 determina a posição em que se encontra a cidade escolhida no
intervalo C9:F9 da planilha Preços.
Figura 175
26. Agora, vamos encontrar o preço de venda em D10. Como precisamos retornar
o preço de acordo com o modelo e a cidade, a função ÍNDICE deve pesquisar a região
nomeada para Vendas e retornar o valor que estiver de acordo com as coordenadas da
linha escolhida pelo produto e da coluna escolhida pela cidade.
Como a célula G4 representa o número da linha do produto e G5, o deslocamento
da coluna de acordo com a cidade, a fórmula a ser utilizada é a seguinte:
=ÍNDICE(Venda;G4;G5).
Figura 176
27. Agora, vamos criar um controle para determinar o número de prestações. Em
C12, digite Nro. Prestações.
28. Clique na guia Desenvolvedor, grupo Controles, e no botão Inserir
29. Clique na ferramenta Barra de rolagem .
154
.
30. Note que o ponteiro do mouse transforma-se em uma cruz fina.
31. Posicione o ponteiro do mouse no canto superior esquerdo da célula C13, clique,
arraste-o para a direita até preencher a largura da coluna e solte-o.
32. Clique nesse objeto com o botão direito do mouse e escolha Formatar
controle.
33. Em Valor mínimo, digite 1, pois esse será o menor número de prestações
possível para o controle da barra de rolagem.
34. Em Valor máximo, coloque 24, ou seja, o carro só poderá ser financiado em até
24 vezes.
35. Em Alteração incremental, digite 1.
36. Defina G6 como vínculo da célula.
Figura 176
37. Clique em OK.
38. Agora, clique em qualquer célula. Depois, clique na seta da direita da barra de
rolagem e veja que o número da célula G6 aumenta de um em um (mudança incremental),
até o máximo de 24.
39. Na célula D12, insira a fórmula =G6.
40. Assim, conforme você aumenta ou diminui o valor de G6 pela barra de rolagem,
o número de prestações também se altera. Você entenderá mais adiante por que não
definimos a própria célula D12 como vínculo da célula para esse controle.
155
Figura 178
41. Para calcular o valor da prestação, vamos considerar uma taxa de juros de
3,5% ao mês. Na célula C15, digite Valor da prestação e, em D15, insira a fórmula
=-PGTO(3,5%;D12;D10).
42. Vamos verificar se a venda é a vista ou a prazo.
43. Clique na guia Desenvolvedor, grupo Controles, e no botão Inserir
44. Clique na ferramenta Caixa de seleção
.
.
A diferença entre o Botão de opção e a Caixa de seleção é que a Caixa permite que
sejam marcadas tantas opções quantas houver na planilha, enquanto o Botão permite
selecionar apenas uma opção.
45. Note que o ponteiro do mouse transforma-se em uma cruz fina.
46. Posicione o ponteiro do mouse no canto superior esquerdo da célula E15, clique,
arraste-o para a direita até preencher a largura da coluna e solte-o.
47. Clique nesse objeto com o botão direito do mouse e escolha Formatar
controle.
48. Defina G7 como vínculo da célula e clique em OK.
156
Figura 179
49. Ainda com o objeto selecionado, digite A vista como seu título.
50. Clique em qualquer célula. Depois, clique em A vista para marcar essa opção e
note que, em G7, surge o valor lógico VERDADEIRO. Quando não estiver marcada essa
opção, o valor de G7 será FALSO.
Figura 180
157
51. Agora, vamos corrigir a fórmula que calcula o pagamento e o valor das prestações.
Selecione D12, a célula com o número de prestações, e insira a condição, determinando
que, se G7 for VERDADEIRO (a vista), o valor dessa célula será 0 (zero). Caso contrário, será
igual a G6 (vínculo da célula da barra de rolagem). A fórmula deve ser =SE(G7;0;G6).
Fica, assim, esclarecido por que não definimos a própria célula D12 como vínculo da
célula da barra de rolagem.
52. Veja o resultado. 0 número de prestações passa a ser 0 (zero), causando um erro
no valor das prestações (célula D15).
Para corrigir esse erro, insira as mesmas condições para fazer o cálculo por meio da
fórmula =SE(G7;0;-PGTO(3,5%;D12;D10)).
Figura 181
53. Depois de criados todos os controles desejados, formate e proteja a planilha. Se
inserir linhas ou colunas, assim como se quiser mover as células ou arrastar os objetos,
isso não causará problemas com as fórmulas.
Portanto, faça uso dos recursos de formatação do Excel, como o WordArt, e dos
efeitos de preenchimentos disponíveis para os objetos. Entre outras possibilidades, mude
a cor das bordas das células.
158
Figura 182
Vamos praticar! Exercício 14
159
Exercícios
Excel
Avançado
Exercícios
Exercício 1
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha NOMES.
3. Calcule o campo Valor Total.
4. Atribua o nome Comissão para a célula I1.
5. Utilizando o nome atribuído, calcule o campo Total c/ comissão.
6. O resultado deverá ser idêntico à imagem abaixo:
7. Salve as alterações feitas neste exercício.
163
Exercício 2
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha SUBTOTAL.
3. Organize a coluna Marca em ordem crescente e adicione subtotais. Utilize a função
Soma() para o campo da planilha denominado Valor Total.
4.Altere a visualização para o Subtotal por Marca, utilizando os níveis de estrutura de
tópico e oculte as colunas B, C e D.
164
• O resultado obtido deverá ser o seguinte:
5.Salve as alterações deste exercício.
165
Exercício 3
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha FILTRO_AVANÇADO.
3. Aplique os filtros para atender às solicitações a seguir:
a) Filtre as vendas de mouse que tenham quantidade superior a 8 e cuja vendedora
seja Márcia.
166
b) Crie um filtro que exiba a quantidade de memória vendida no dia 13/07/2007.
c) Filtre as vendas realizadas pela vendedora Márcia, cujo produto seja teclado e a
data seja posterior a 04/02/2008.
d) Exiba todas as vendas com total superior a 100,00 e que tenham sido feitas pelos
vendedores Antônio, Jonas e Mauro.
e) Mencione os produtos vendidos por Pedro.
f) Exiba todos os produtos vendidos no dia 31/9/2007.
g) Filtre as vendas de Felipe, cujo produto seja CPU e o total seja superior a 3.600.
h) Filtre as vendas do produto ‘Monitor’, cujo total seja superior a 4.200 e cujo
vendedor seja “Antônio, Pedro ou Felipe”.
4. Salve a pasta de trabalho.
167
Exercício 4
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha TABELA_DINÂMICA.
3. Com o recurso tabela dinâmica, filtre as vendas dos esportes por Trimestre. Na
tabela dinâmica, você deverá selecionar o trimestre e exibir as vendas de cada esporte.
168
Com a tabela dinâmica, você pode conseguir outros layouts. O layout esperado para
este exercício deve ser o apresentado a seguir:
4. Salve as alterações.
Exercício 5
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha IMPORTANDO_DADOS.
3. Importe os dados do arquivo Importação.txt.
169
• O resultado deverá ser o seguinte:
4. Salve as alterações da pasta de trabalho.
170
Exercício 6
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha CENÁRIOS.
3. Você deverá criar três tipos de cenário: Baixo, Significativo e Muito Significativo.
Valores do cenário
Natal
Reveillon
Carnaval
Baixo
2,2
3,4
5,1
Significativo
3,3
5,4
6,2
Muito Significativo
4,3
6,3
7,2
171
Você deverá atingir os resultados a seguir:
Cenário Baixo
Cenário Significativo
172
Cenário Muito Significativo
Resumo do cenário
Observações
• O campo Valores atuais representa os valores das células variáveis no momento
em que o relatório de resumo do cenário foi criado.
• As células variáveis para cada cenário estão destacadas em cinza.
4. Salve as alterações realizadas.
173
Exercício 7
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha PROTEÇÃO.
3. Após a digitação, o campo Nome da primeira tabela deverá ser validado por
meio de Lista, onde seja possível selecionar um nome cadastrado, evitando-se erros de
digitação.
174
4. Os campos Endereço, Bairro, Cidade e Estado deverão ser preenchidos
automaticamente, quando o nome do funcionário for escolhido.
Isso será possível após você aprender a função PROCV. Não se preocupe. Voltaremos
a esse exercício posteriormente.
5. Proteja a pasta de trabalho, para que não seja possível adicionar planilhas ou fazer
alterações nos dados existentes.
6. Salve a pasta de trabalho.
Exercício 8 - VALIDAÇÃO
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha VALIDAÇÃO.
3. Utilize o recurso de validação para as áreas de Nota e Falta. A área de notas
deverá aceitar somente valores entre 0 e 10. Para cada coluna de falta, o aluno poderá
ter, no máximo, 15 faltas.
175
4. Defina as seguintes mensagens para os campos:
• De entrada
a) Nota: Digite apenas valores entre 0 e 10.
b) Falta: Digite valores entre 0 e 15
• De erro
a) Nota: O valor digitado não é válido. Para essa área, digite valores entre 0 e 10.
b) Falta: Para valores de falta, digite valores entre 0 e 15.
O resultado para o 4º item deverá ser o seguinte:
Notas
Mensagem de Entrada
Alerta de Erro
176
Faltas
Mensagem de Entrada
Alerta de Erro
5. Salve as alterações feitas nesta pasta de trabalho.
177
Exercício 9
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha SOLVER.
3. A quantidade de produtos deverá ser definida pelo SOLVER. Evite deixar que o
total ultrapasse o valor de B9.
4. Salve a pasta de trabalho.
178
Exercício 10
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha FUNÇÃO_DE_TEXTO.
3. Utilizando funções de texto e Somase(), permita ao usuário digitar o departamento
na célula B36 e visualizar o total de custo com treinamento do departamento. Por exemplo:
“Total gasto com cursos para o departamento de ...”
4. Salve a pasta de trabalho.
179
Exercício 11
1. Abra a pasta de trabalho Exercícios_Avançados_2007.
2. Selecione a planilha SOMASE.
3. Calcule o valor do estoque de cada marca da tabela abaixo, utilizando a função
Somase.
4. Qual o valor do estoque da Samsung?
180
5. Que marca possui o estoque mais valioso?
6. Salve a pasta de trabalho.
Exercício 12
1. Abra a pasta de trabalho Exercícios_Avançados_2007.
Neste exercício, usaremos duas planilhas para demonstrar a função PROCV:
PROCV
181
BASE_PROCV
Ao digitar o RM do funcionário na planilha PROCV, os demais dados deverão ser
preenchidos automaticamente.
2. Selecione a planilha BASE_PROCV e calcule o salário líquido com base na tabela
de descontos e convênio.
Observação: Os estagiários não deverão ter descontos no salário, exibindo-se,
portanto, o salário bruto.
A planilha BASE_PROCV deverá ficar parecida com a imagem abaixo:
182
3. Em seguida, selecione a planilha PROCV e, utilizando a função PROCV, exiba o
salário líquido que deverá ser atribuído, quando um RM for informado na célula C5 da
planilha PROCV.
4. Efetue pesquisa dos campos disponíveis de acordo com o RM digitado. Para auxiliar
na pesquisa, na coluna A, foram adicionados valores de RM que você poderá consultar.
5. Salve a pasta de trabalho.
Exercício 13
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha PROTEÇÃO.
3. Utilize a função PROCV para que os campos Endereço, Bairro, Cidade e Estado
sejam preenchidos automaticamente, quando o nome do funcionário for escolhido.
4. Salve a pasta de trabalho.
183
Exercício 14
1. Abra a pasta de trabalho Exerc_Avanc.
2. Selecione a planilha PREÇO_HOTÉIS.
3. Com base nos dados acima, em uma nova planilha, crie um formulário conforme
modelo abaixo, tendo por base as seguintes informações:
• Número de dias -> mínimo 3 e máximo 8
• Valor do pacote -> Caso o pagamento não seja a vista, calcule um acréscimo de
2,5%.
184
4. Nomeie a planilha como FORMULÁRIO e salve a pasta de trabalho.
185
www.fundacaobradesco.org.br