Excel 2010 Avancado.pdf

Excel 2010Avançado

Book Excel2010 avancado.indb 1 20/09/2013 08:40:51

Dados Internacionais de Catalogação na Publicação (CIP)(Jeane Passos Santana – CRB 8a/6189)

Martelli, Richard; Barros, Maria Silvia Mendonça deExcel 2010 avançado / Richard Martelli, Maria Silvia

Mendonça de Barros. – São Paulo : Editora Senac São Paulo, 2013. – (Nova Série Informática)

ISBN 978-85-396-0346-6

1. Microsoft Excel (Programa de computador) 2. Microsoft Excel 2010 (Programa de computador) I. Título. II. Série.

13-085s CDD-005.369

Índice para catálogo sistemático:

1. Microsoft Excel 2010 : Computadores : Programas : Processamento de dados 005.369


Richard Martelli e Maria Silvia Mendonça de Barros

Excel 2010Avançado

Editora Senac São Paulo – São Paulo – 2013


ADMINISTRAÇÃO REGIONAL DO SENAC NO ESTADO DE SÃO PAULO

Presidente do Conselho Regional: Abram SzajmanDiretor do Departamento Regional: Luiz Francisco de A. SalgadoSuperintendente Universitário e de Desenvolvimento: Luiz Carlos Dourado

EDITORA SENAC SÃO PAULO

Conselho Editorial: Luiz Francisco de A. Salgado Luiz Carlos Dourado Darcio Sayad Maia Lucila Mara Sbrana Sciotti Jeane Passos Santana

Gerente/Publisher: Jeane Passos Santana ([email protected])Coordenação Editorial: Márcia Cavalheiro Rodrigues de Almeida ([email protected]) Thaís Carvalho Lisboa ([email protected])Comercial: Marcelo Nogueira da Silva ([email protected])Administrativo: Luís Américo Tousi Botelho ([email protected])

Edição de Texto: Rafael Barcellos MachadoPreparação de Texto: Barbara BenevidesRevisão de Texto: Globaltec Editora Ltda., Luciana Lima (coord.)Projeto Gráco e Capa: Fabiana FernandesEditoração Eletrônica e Ilustrações: Globaltec Editora Ltda.Impressão e Acabamento: Imprensa Ocial do Estado S/A – Imesp

Nenhuma parte desta publicação poderá ser reproduzida, guardada pelo sistema “retrieval” ou transmitida de qualquer modo ou por qualquer outro meio, seja este eletrônico, mecânico, de fotocópia, de gravação, ou outros, sem prévia autorização, por escrito, da Editora Senac São Paulo.

Todos os direitos desta edição reservados àEditora Senac São PauloRua Rui Barbosa, 377 – 1o andar – Bela Vista – CEP 01326-010Caixa Postal 1120 – CEP 01032-970 – São Paulo – SPTel. (11) 2187-4450 – Fax (11) 2187-4486E-mail: [email protected] Home page: http://www.editorasenacsp.com.br

© Richard Martelli e Maria Silvia Mendonça de Barros, 2013


Sumário

Apresentação I O que é a Nova Série Informática? III

1 Funções matemáticas, estatísticas, lógicas, nanceiras e de data e hora 9

Funções matemáticas 11Atividade 1 – Controlando as vendas da loja Tudo em Informática 11Atividade 2 – Gerando números aleatórios para apostas na Mega-Sena 16Funções estatísticas 17Atividade 3 – Vericando o número que foi sorteado com maior frequência

nos sorteios da Mega-Sena no período de 1º de janeiro de 2011 a 11 de junho de 2011 17

Atividade 4 – Vericando o desempenho dos vendedores da loja Seu Carro é Aqui 19

Funções lógicas 29Atividade 5 – Premiando os vendedores que ocupam a primeira posição

em vendas e estabelecendo conceitos sobre seu desempenho, de acordo com a meta de vendas estipulada 29

Atividade 6 – Premiando vendedores que não ocupam o primeiro lugar em vendas, mas que atingiram metas de vendas estipuladas 31

Funções nanceiras 35Atividade 7 – Calculando o valor futuro de um investimento 35Atividade 8 – Calculando o valor presente de um investimento 36Atividade 9 – Calculando o valor da parcela a ser paga para um investimento 37Atividade 10 – Calculando o número de parcelas de um investimento a uma

taxa de juros e pagamentos constantes 39Atividade 11 – Formatando datas 40Atividade 12 – Funções de data e hora 44Resumo do capítulo 54


6 – EXCEL 2010 – Avançado

Sumário

2 Funções de pesquisa 59Funções de pesquisa 61Atividade 1 – Utilizando a função PROCV 61Atividade 2 – Utilizando as funções CORRESP e ÍNDICE 69Resumo do capítulo 72

3 Funções de texto 73 Funções de texto 75Atividade 1 – Utilizando as funções PRI.MAIÚSCULA, ARRUMAR,

MAIÚSCULA e MINÚSCULA 75Atividade 2 – Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO 79Resumo do capítulo 84

4 Funções de banco de dados 85 Funções de banco de dados 87Atividade 1 – Utilizando as funções BDSOMA, BDMÉDIA, BDMÁX,

BDMÍN, BDEXTRAIR e BDCONTARA 87Resumo do capítulo 97

5 Auditoria de fórmulas 99Auditoria de fórmulas 101Atividade 1 – Rastreando os precedentes de uma célula 101Atividade 2 – Rastreando os dependentes de uma célula 103Atividade 3 – Rastreando erros em fórmulas 104Atividade 4 – Avaliando fórmulas 106Atividade 5 – Mostrando fórmulas 108Resumo do capítulo 110

6 Dados externos 111Dados externos 113Atividade 1 – Importando arquivos-texto para o Excel 113Atividade 2 – Importando arquivos de dados para o Excel 118Atividade 3 – Importando base de dados como consulta para o Excel 120Atividade 4 – Importando dados de uma página da web 123Resumo do capítulo 127


EXCEL 2010 – Avançado – 7

Sumário

7 Tabelas dinâmicas 129Tabela dinâmica 131Atividade 1 – Criação da tabela dinâmica 131Atividade 2 – Atualizando dados na tabela dinâmica 136Atividade 3 – Criando níveis na tabela dinâmica e cruzando dados 138Atividade 4 – Criando novos grupos 139Atividade 5 – Utilizando segmentação de dados 144Atividade 6 – Criando grácos dinâmicos 145Atividade 7 – Mostrando os dados da tabela dinâmica por página e relatório 147Resumo do capítulo 148

8 Classicar e ltrar 149Classicando dados 151Atividade 1 – Classicando dados 151Atividade 2 – Classicando por mais critérios 153Atividade 3 – Criando uma lista personalizada 155Atividade 4 – Classicando por fonte 156Atividade 5 – Filtrando dados em uma tabela 158Atividade 6 – Filtrando dados por meio de critérios 163Atividade 7 – Filtrando dados com mais de um critério 165Atividade 8 – Filtrando com critérios avançados 170Resumo do capítulo 179

9 Hipóteses 181Aplicando hipóteses 183Atividade 1 – Atingindo metas 183Atividade 2 – Trabalhando com cenários 185Atividade 3 – Instalando o Solver 195Atividade 4 – Resolvendo um problema com o Solver 197Atividade 5 – Utilizando o Solver com restrições 200Resumo do capítulo 202

10 Ferramentas de dados 205Validação de dados 207Atividade 1 – Adicionando regras de validação 207Atividade 2 – Denindo mensagens de dados 209Atividade 3 – Criando regras de validação em lista de outra planilha 212



Sumário

Atividade 4 – Aplicando validação a dados já inseridos 215Atividade 5 – Personalizando regras de validação 218Atividade 6 – Consolidando dados 224Atividade 7 – Consolidando dados com vínculo 228Atividade 8 – Consolidando dados em outras pastas 231Resumo do capítulo 235

11 Estrutura de dados 239Atividade 1 – Trabalhando com estrutura de tópicos 241Resumo do capítulo 249

12 Proteção 251Atividade 1 – Protegendo elementos da planilha 253Atividade 2 – Protegendo uma planilha 257Atividade 3 – Protegendo um arquivo 259Resumo do capítulo 263

13 Macros interativas 265Atividade 1 – Criando macros interativas 267Atividade 2 – Executando macros 273Atividade 3 – Utilizando referências relativas para criar macros 277Resumo do capítulo 280

14 Controles de formulários – automação de planilhas 281Controles de formulário 283Atividade 1 – Mostrando a faixa de opções Desenvolvedor 283Atividade 2 – Criando um formulário para entrada e pesquisa de dados 286Atividade 3 – Utilizando macros para automatizar os controles criados 294Atividade 4 – Utilizando o controle Botão para efetuar a pesquisa desejada 298Atividade 5 – Utilizando controles para cálculo 300Resumo do capítulo 305

Atividades complementares 307

Sobre os autores 321

Índice geral 323


Apresentação



EXCEL 2010 – Avançado – III

Apresentação

O que é a Nova Série Informática?A Nova Série Informática foi criada para que você aprenda informática sozinho, sem professor! Com ela, é possível estudar os softwares mais utilizados pelo mercado, sem diculdade. O texto de cada volume é complementado por arquivos eletrônicos disponibilizados pela Editora Senac São Paulo.

Para utilizar o material da Nova Série Informática, é necessário ter em mãos o livro, um equipamento que atenda às congurações necessárias e o software a ser estudado.

Neste volume, estruturado com base em atividades que permitem estudar o software passo a passo, são apresentadas informações avançadas para a operação do Excel 2010. Você deverá ler com atenção e seguir corretamente todas as instruções. Se encontrar algum problema durante uma atividade, volte ao início e recomece; isso vai ajudá-lo a esclarecer dúvidas e resolver diculdades.

Equipamento necessárioPara você estudar com este material e operar o Excel 2010, é importante que seu computador tenha as congurações mínimas a seguir.

Componentes Requisitos

Computador e processador

Processador de 500 MHz ou mais rápido.

Memória 256 MB de RAM ou superior.

Disco rígido 2 GB de espaço disponível em disco.

Monitor Monitor com resolução de 1024 px × 576 px ou superior.

Sistema operacional

Windows XP com Service Pack 3 (somente 32 bits) ou Windows Vista com SP1, Windows 7, Windows Server 2003 R2 com MSXML 6.0, Windows Server 2008 ou outro sistema operacional posterior (32 bits ou 64 bits).

Outros A funcionalidade e os grácos do produto podem variar de acordo com a conguração do sistema. Alguns recursos podem exigir itens de hardware adicionais ou avançados ou conectividade com o servidor. Verique em www.ofce.com/products.


IV – EXCEL 2010 – Avançado

Apresentação

Ajuste do monitor 1. Feche todos os softwares que estiverem abertos no momento.

Esse procedimento é importante, pois, em alguns casos, quando as congurações do monitor são alteradas, é necessário reiniciar o computador.

2. Verique se o seu monitor está devidamente ajustado para trabalhar com este material. Clique no botão Iniciar , da barra de tarefas do Windows 7 e, do lado direito do menu Iniciar aberto, clique na opção Painel de Controle.

3. Será exibida a janela do Painel de Controle.


EXCEL 2010 – Avançado – V

Apresentação

Além da resolução, é possível alterar o tema e denir um novo plano de fundo da área de trabalho, entre outros recursos.

4. Na opção Aparência e Personalização, clique em Ajustar a reso-lução da tela.

Será aberta a janela abaixo.

5. A resolução deve estar ajustada para o recomendável, de acordo com o monitor que você está utilizando. Clique no item Resolução. Clique então no botão deslizante e movimente-o até obter a opção indicada como recomendável.

6. Clique no botão Aplicar e em OK para conrmar.


VI – EXCEL 2010 – Avançado

Apresentação

7. Caso tenha alterado as congurações, será exibida uma caixa de diálogo solicitando a conrmação. Clique em Manter alterações, caso a alteração tenha sido satisfatória. Caso não queira alterar, clique no botão Reverter.

8. Feche a janela do Painel de Controle.

Dica: Você pode alterar a resolução de tela diretamente na área de trabalho. Basta clicar com o botão direito do mouse em qualquer ponto da Área de trabalho do Windows e, no menu aberto, clicar em Resolução da tela.

Estrutura do livroEste livro está dividido em capítulos que contêm uma série de atividades práticas e informações teóricas sobre o Excel 2010.

Para obter o melhor rendimento possível em seu estudo, evitando dúvidas ou erros, é importante que você:

• leia com atenção todos os itens do livro, pois sempre encontrará informações úteis para a execução das atividades;

• conheça e respeite o signicado dos símbolos colocados na margem esquerda de determinados parágrafos do texto, pois eles servem para orientar seu trabalho;

• faça apenas o que estiver indicado no item e só execute uma sequência após ter lido a instrução do respectivo item.


EXCEL 2010 – Avançado – VII

Apresentação

Significado dos símbolos

DicaQuando este símbolo aparecer, você terá informações adicionais sobre o assunto, como dicas, atalhos e sugestões para facilitar o trabalho com o software. Sua leitura não é obrigatória para o desenvolvimento da atividade, mas lembre-se: quanto mais informações você tiver, melhor será o seu aproveitamento.

ObservaçãoEste símbolo marca um texto com observações sobre o assunto tratado. Embora importante, sua leitura não é obrigatória nem interfere diretamente na atividade em execução.

AtençãoToda vez que se deparar com este símbolo, leia com muita atenção o texto que o acompanha, pois as informações que ele contém afetarão os passos seguintes da atividade. Isso evitará dúvidas posteriores.

ExercícioEste símbolo sinaliza os exercícios propostos, que são apresentados no m da atividade ou do capítulo. Procure fazer esses exercícios antes de iniciar outro capítulo. Eles vão auxiliá-lo a xar melhor os recursos estudados.

Utilizando o material da Nova Série InformáticaÉ muito simples utilizar o material da Nova Série Informática: inicie sempre pelo capítulo 1, leia atentamente as instruções e execute, passo a passo, os procedimentos solicitados.

Para a vericação dos exercícios dos capítulos e das atividades propostas, disponibilizamos no site da Editora Senac São Paulo os arquivos compactados contendo o conjunto de pastas referentes aos projetos que serão desenvolvidos ao longo do livro.

1. Para fazer o download, acesse a internet e digite o link abaixo:

http://www.editorasenacsp.com.br/informatica/excel2010avanc/planilhas.zip

2. Ao ser exibida a caixa de diálogo Download de Arquivos, clique no botão Salvar.


VIII – EXCEL 2010 – Avançado

Apresentação

3. Faça o download na pasta com o nome planilhas na Área de trabalho (ou em um local de sua preferência).

4. Ao ser exibida a caixa de diálogo Download Concluído, clique no botão Abrir.

5. Descompacte os arquivos em um local de sua preferência.

Boa leitura!


1Funções matemáticas, estatísticas, lógicas, financeiras e de data e hora

O B J E T I V O S Utilizar funções matemáticas do Excel.

Utilizar funções estatísticas do Excel.

Utilizar funções lógicas do Excel.Utilizar funções financeiras do Excel.

Utilizar funções de data e hora do Excel.

Formatar datas.

Efetuar cálculos com datas e horas.




Funções matemáticas, estatísticas, lógicas, financeiras e de data e hora

O Excel 2010 apresenta uma grande gama de funções, contemplando as mais diversas áreas e necessidades, o que permite ao usuário aprimorar a capacidade de análise e cálculo de dados. Essas funções oferecem poderosas ferramentas, que possibilitam maior precisão em cálculos, eciência na tomada de decisões e análises consistentes para situações especícas.

Estudaremos a seguir as funções mais utilizadas do Excel 2010.

Funções matemáticas

Atividade 1 – Controlando as vendas da loja Tudo em InformáticaObjetivo: • Utilizar as funções matemáticas do Excel 2010: SOMA,

PRODUTO, SOMARPRODUTO, SOMASE e SOMASES.

Tarefas: • Abrir a pasta Tudo em informatica.xlsx.

• Somar o número de itens vendidos.

• Calcular o preço total de cada produto.

• Somar o valor total de vendas.

• Somar o valor vendido por lial.

• Somar o valor vendido por lial e por mês.

A loja Tudo em Informática possui liais em dois bairros da cidade de São Paulo: Brooklin e Vila Mariana. O gerente geral solicitou ao gerente nanceiro um relatório mostrando a posição de vendas dos itens Mesa para computador, Mouse e Impressora no primeiro trimes-tre de 2011.

O gerente nanceiro foi alertado de que no relatório deveriam constar dados referentes a: número de itens vendidos, valor total das vendas, valor total das vendas por lial, valor total das vendas por mês, valor total das vendas da lial da Vila Mariana no mês de fevereiro e valor total das vendas da lial do Brooklin no mês de janeiro.

Vamos ajudar o gerente nanceiro a elaborar o relatório solicitado utili-zando as funções matemáticas do Excel 2010.

Função SOMAObjetivo: somar os valores numéricos contidos em uma faixa de células.

Sintaxe: SOMA(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem a valores, endereços de células ou faixa de células que desejamos somar.




1. Abra a pasta Tudo em informatica.xlsx.

2. Vamos calcular o número de itens vendidos pelas liais no primeiro trimestre de 2011. Clique na célula D19 e digite:

= SOMA(

3. Com o mouse pressionado, selecione a região D3:D16.

4. Pressione a tecla Enter e você terá a seguinte fórmula na célula:

= SOMA(D3:D16)

Você também poderá utilizar a função SOMA por meio da ferra-menta , que se encontra na uia Página Inicial, rupo Edição.

aso opte por utilizar essa ferramenta, não di ite o sinal no início da fórmula.

Função MULTObjetivo: efetuar o produto entre os valores numéricos contidos nas células informadas.

Sintaxe: MULT(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem a valores, endereços de células ou faixa de células que desejamos multiplicar.




1. Vamos calcular o valor total de cada item vendido pelas liais no primeiro trimestre de 2011. Clique na célula F3 e digite:

= MULT(

2. Clique na célula D3, digite ; (ponto e vírgula) e clique na célula E3.


= MULT(D3;E3)

4. Copie a fórmula para todos os itens da planilha.

Função SOMARPRODUTOObjetivo: multiplicar os componentes das faixas de dados fornecidas e retornar a soma desses produtos.

Sintaxe: SOMARPRODUTO(faixa1;faixa2;...)

Argumentos: os argumentos faixa1, faixa2,...correspondem à faixa de células que desejamos multiplicar e depois somar.

1. Vamos calcular o valor total das vendas efetuadas pelas liais no primeiro trimestre de 2011. Nosso objetivo é somar os resultados da multiplicação de quantidade por preço unitário de cada produto. Clique na célula D20 e digite:

= SOMARPRODUTO(

2. Com o mouse, selecione a faixa D3:D16.

3. Digite ; (ponto e vírgula).

4. Com o mouse, selecione a faixa E3:E16.


=SOMARPRODUTO(D3:D16;E3:E16)

A operação realizada corresponde à soma dos itens da coluna Valor Total.

Função SOMASEObjetivo: efetuar a soma dos valores indicados de acordo com determi-nado critério ou condição.

Sintaxe: SOMASE(intervalo;critérios;intervalo_soma)

Argumentos:

• intervalo: intervalo de células em que o critério será procurado;

• critério: condição para denir os valores que serão somados. Es-ses critérios podem ser: número, expressão, referência de célula, texto ou função. Por exemplo: “<>250”, 100, “Mesa para com-




putador”, G34. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores rela-cionais (>, <, >=, <=, <>);

• intervalo_soma: intervalo de valores que serão somados. Se esse argumento for omitido, serão somadas as células especicadas no argumento intervalo (as mesmas células às quais os critérios são aplicados).

1. Vamos calcular o valor vendido pela lial da Vila Mariana. Clique na célula D21 e digite:

= SOMASE(

2. Com o mouse, selecione a faixa A3:A16.


4. Digite “Vila Mariana”.

5. Com o mouse, selecione a faixa F3:F16.


=SOMASE(A3:A16;“Vila Mariana”;F3:F16)

7. Na célula D22, calcule o valor vendido pela lial do Brooklin. Digi-te a fórmula:

=SOMASE(A3:A16;“Brooklin”;F3:F16)

8. Na célula D23, calcule o valor das vendas no mês de janeiro. Obser-ve que o critério “Janeiro” deve ser procurado na faixa B3:B16.

=SOMASE(B3:B16;“Janeiro”;F3:F16)

9. Nas células D24 e D25, calcule os valores vendidos para os meses de fevereiro e março.

Função SOMASESObjetivo: efetuar a soma em um intervalo de células atendendo a vá-rios critérios, que serão combinados.

Sintaxe: SOMASES(intervalo_soma;intervalo_critério1;critério1;inter- valo_critério2;critério2;...)

Argumentos:

• intervalo_soma: intervalo de valores que serão somados;

• intervalo_critério1: intervalo de células em que o critério1 será procurado;

• critério1: condição para denir os valores que serão somados. Es-ses critérios podem ser número, expressão, referência de célula, texto ou função. Por exemplo: 120, “Vila Mariana”, “<>100”,




A12. O critério deverá ser informado entre aspas se for um va-lor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);


• critério2: condição para denir os valores que serão somados. As mesmas considerações feitas ao critério1 devem ser aplicadas ao critério2 e a todos os outros que possam existir.

Podemos ter até 127 pares de intervalos/critérios.

1. Vamos calcular o valor vendido pela lial de Vila Mariana no mês de fevereiro. Clique na célula D26 e digite:

= SOMASES(

2. Com o mouse, selecione a faixa F3:F16.


4. Com o mouse, selecione a faixa A3:A16.


6. Digite “Vila Mariana”.


8. Com o mouse, selecione a faixa B3:B16.


10. Digite “Fevereiro”.


=SOMASES(F3:F16;A3:A16;“Vila Mariana”;B3:B16;“Fevereiro”)

12. Na célula D27, calcule o valor vendido pela lial do Brooklin no mês de janeiro.




13. Salve as modicações e feche a pasta.

Atividade 2 – Gerando números aleatórios para apostas na Mega-SenaObjetivo: • Utilizar a função matemática ALEATÓRIOENTRE do

Excel 2010.

Tarefas: • Abrir a pasta Loteria.xlsx.

• Gerar apostas para a Mega-Sena.

Depois de muito jogar na Mega-Sena e não ganhar, Mariana resolveu criar um procedimento para gerar números aleatórios na faixa entre 1 e 60 para serem jogados em uma aposta de 6 números. Vamos ajudá-la a utilizar uma planilha no Excel para essa nalidade.

Função ALEATÓRIOENTREObjetivo: gerar um número aleatório inteiro entre os valores especi-cados. Sempre que a planilha for calculada, um novo valor será gerado.

Sintaxe: ALEATÓRIOENTRE(valor_inferior;valor_superior)

Argumentos:

• valor_inferior: argumento obrigatório que corresponde ao menor número inteiro que pode ser gerado pela função;




• valor_superior: argumento obrigatório que corresponde ao maior número inteiro que pode ser gerado pela função.

1. Abra a pasta Loteria.xlsx.

As apostas para a Mega-Sena têm de conter números entre 1 e 60. Portanto, vamos utilizar a função para gerar números inteiros dentro dessa faixa.

2. Na célula A4, digite:

=ALEATÓRIOENTRE(1;60)

3. Copie a fórmula para as células B4 a F4. Os resultados que você obterá em sua planilha não serão os mesmos mostrados na planilha a seguir, pois os números gerados são aleatórios e sempre que a planilha for recalculada a função retornará novos valores. Boa sorte!

4. Salve as modicações e feche a pasta.

Funções estatísticas

Atividade 3 – Verificando o número que foi sorteado com maior frequência nos sorteios da Mega-Sena no período de 1o de janeiro de 2011 a 11 de junho de 2011Objetivo: • Utilizar a função estatística MODO.ÚNICO do Excel 2010.

Tarefas: • Abrir a pasta Megasena_2011.xlsx.

• Gerar uma aposta com cinco números aleatórios.

• Inserir o sexto número na aposta gerada e que corresponda ao valor que foi sorteado com maior frequência nos sor-teios de 1º de janeiro de 2011 a 11 de junho de 2011.




Mariana resolveu modicar a aposta gerada e colocou como o sexto palpite o número que foi mais sorteado nos últimos concursos da Mega--Sena (período de 1º de janeiro de 2011 a 11 de junho de 2011). Para isso, vamos utilizar uma planilha com os resultados desses concursos e determinar o número que apareceu com maior frequência.

Função MODO.ÚNICOObjetivo: retornar o valor que aparece com maior frequência em um intervalo de dados numéricos.

Sintaxe: MODO.ÚNICO(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem ao conjun-to de valores numéricos dentre os quais desejamos obter aquele que aparece com maior frequência.

1. Abra a pasta Megasena_2011.xlsx.

2. Nas células K2, L2, M2, N2 e O2, utilize a função ALEATÓRIO- ENTRE para gerar os cinco primeiros números na aposta.

3. Na célula P2, digite:

=MODO.ÚNICO(

4. Com o mouse, selecione a faixa C2:H47.

MODO.ÚNICO(C2:H47

5. Pressione a tecla Enter.

6. Salve as alterações e feche a pasta.




Atividade 4 – Verificando o desempenho dos vendedores da loja Seu Carro é AquiObjetivo: • Utilizar as funções estatísticas do Excel 2010: MÁXIMO,

MÍNIMO, MAIOR, MENOR, MÉDIA, ORDEM.EQ, CONT.SE, CONT.SES, CONTAR.VAZIO, CONT.NÚM, CONT.VALORES, MÉDIASE e MÉDIASES.

Tarefas: • Abrir a pasta Loja de carros.xlsx.

• Calcular a maior e a menor venda entre os vendedores.

• Calcular a média de vendas dos vendedores.

• Calcular o total de vendedores.

• Calcular o número de vendedores que não efetuaram vendas.

• Calcular o número de vendedores que efetuaram vendas.

• Calcular o número de vendedores premiados com o pri-meiro lugar em vendas.

• Calcular o total de vendedores por departamento.

• Calcular o número de vendedores premiados com o pri-meiro lugar em vendas por departamento.

• Calcular a média de vendas por departamento.

• Calcular a média de vendas pelos vendedores não premia-dos por departamento.

A loja de automóveis Seu Carro É Aqui comercializa três tipos de veícu-los: usados, zero-quilômetro e veículos com isenções de impostos para decientes. Para melhor atender a seus clientes, a loja está dividida em três departamentos de vendas, identicados como Usados, Zero KM e Isenções. Em cada um desses departamentos, trabalha uma equipe de vendedores. Além de precisar efetuar o controle das vendas, a loja resolveu adotar uma política de prêmios para motivar os vendedores e para isso estabeleceu metas para as vendas.

Nosso objetivo é efetuar os seguintes controles:

• mostrar o ranking de vendas, ou seja, calcular a colocação de cada vendedor levando em consideração a venda individual em relação ao conjunto de vendas dos vendedores;

• mostrar a maior e a menor venda efetuada, considerando todos os departamentos;

• calcular a média de vendas, considerando todos os departamentos;




• mostrar o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o número de vendedores que efetuaram vendas e quantos caram em primeiro lugar em vendas;

• por departamento, calcular o número de vendedores, quantos ca-ram em primeiro lugar em vendas, a média de vendas e a média de vendas dos vendedores não ganhadores do prêmio.

Para resolver essa situação, vamos trabalhar com a pasta Loja de car-ros.xlsx. Nessa pasta encontraremos duas planilhas: Vendas e Relatório.

Função MÁXIMO

Objetivo: retornar o valor máximo entre um conjunto de valores nu-méricos informado.

Sintaxe: MÁXIMO(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor máximo queremos determinar. Pode-se informar de 1 a 255 valores numéricos.




1. Abra a pasta Loja de carros.xlsx.

2. Selecione a planilha Relatório e clique na célula B2. Nessa célula será calculado o valor máximo das vendas que estão na faixa de células C3:C22 da planilha Vendas.

3. Digite:

=MÁXIMO(

4. Selecione a planilha Vendas e, com o mouse pressionado, selecione a faixa C3:C22.

5. Pressione a tecla Enter e você terá a seguinte fórmula na célula B2:

=MÁXIMO(VENDAS!C3:C22)

Observe que, como consideramos a faixa C3:C22 da planilha Vendas e a fórmula está sendo colocada na planilha Relatório, é colocado o nome da planilha seguido pelo ponto de exclamação na identicação da região informada.

Você também poderá utilizar a função MÁXIMO por meio da ferramenta , que se encontra na uia Página Inicial, rupo Edição. aso opte por utilizar essa ferramenta, não di ite o sinal no início da fórmula.

Função MÍNIMOObjetivo: retornar o valor mínimo entre um conjunto de valores numé-ricos informado.

Sintaxe: MÍNIMO(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor mínimo queremos determinar. Pode-se informar de 1 a 255 valores numéricos.

1. Selecione a planilha Relatório e clique na célula B3. Nessa célu-la vamos calcular o valor mínimo das vendas que estão na faixa C3:C22 da planilha Vendas.

2. Digite:

=MÍNIMO(



=MÍNIMO(VENDAS!C3:C22)




Você também poderá utilizar a função MÍNIMO por meio da fer-ramenta , que se encontra na uia Página Inicial, rupo Edição.


Função MÉDIAObjetivo: retornar a média aritmética entre um conjunto de valores nu-méricos informado.

Sintaxe: MÉDIA(núm1;núm2;...)

Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos para os quais desejamos calcular a média aritmética. Pode-se informar de 1 a 255 valores numéricos.

1. Selecione a planilha Relatório e clique na célula B4. Nessa célula vamos calcular a média aritmética das vendas que estão na faixa C3:C22 da planilha Vendas.

2. Digite:

=MÉDIA(



=MÉDIA(VENDAS!C3:C22)

Você também poderá utilizar a função MÉDIA por meio da ferra-menta , que se encontra na uia Página Inicial, rupo Edição.


Função ORDEM.EQObjetivo: retornar a posição de um número em uma lista de números.

Sintaxe: ORDEM.EQ(número;referência;[ordem])

Argumentos:

• número: número cuja posição se quer encontrar em uma lista de números;

• referência: lista de números em que o número informado será posi-cionado;

• ordem: argumento opcional. Se for 0 ou omitido, o Excel posiciona-rá o número considerando a lista de números classicada de forma




descendente; se for qualquer valor diferente de 0, o Excel posiciona-rá o número considerando a lista de números classicada de forma ascendente.

A presença de números com a mesma posição vai interferir na ordem dos números subsequentes. Por exemplo, em uma lista de números in-teiros classicados em ordem crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3.

Nosso objetivo nesse momento é vericar quais vendedores ocupam o primeiro lugar em vendas.

1. Selecione a planilha Vendas e clique na célula D3.

2. Vamos utilizar a função ORDEM.EQ para posicionar o valor da ven-da da célula C3 dentro da faixa de vendas C3:C22. Digite a fórmula:

=ORDEM.EQ(C3;$C$3:$C$22)

Como a fórmula será copiada para as células de baixo, é necessário que a faixa de células C3:C22 seja xada, pois a localização da região em que o valor da venda de cada vendedor será procurada deverá se manter a mesma.

3. Pressione a tecla Enter e copie a fórmula até a célula C22.

ão se preocupe com a informação de erro que aparece na célu-la D17 ocasionado pelo fato de ela se encontrar azia . diante, neste capítulo, trataremos esse erro de forma que não apareça no amente a mensa em.

Função CONT.VALORESObjetivo: contar o número de células não vazias em um intervalo informado. O conjunto de células pode conter qualquer tipo de infor-mação.

Sintaxe: CONT.VALORES(intervalo1; [intervalo2]; ...)

Argumentos: intervalo1;[intervalo2];...: faixas de células que devem ser contadas.

1. Vamos contar o número total de vendedores da loja. Para isso vamos contar quantas células contêm os nomes dos vendedores (células não vazias). Selecione a planilha Relatório e clique na célula B7.

2. Digite:

=CONT.VALORES(




3. Como os nomes dos vendedores estão na planilha Vendas, sele-cione a planilha e, com o mouse pressionado, selecione a faixa de células A3:A22.

4. Pressione a tecla Enter e você terá a fórmula:

=CONT.VALORES(VENDAS!A3:A22)

Função CONTAR.VAZIOObjetivo: contar o número de células vazias em um intervalo informa-do. Células que contenham valor nulo não serão contadas.

Sintaxe: CONTAR.VAZIO(intervalo)

Argumento: o argumento intervalo representa a faixa de células que devem ser contadas.

1. Vamos agora contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da coluna Valor da venda que estão va-zias. Selecione a planilha Relatório e clique na célula B8.

2. Digite:

=CONTAR.VAZIO(

3. Como os valores das vendas estão na planilha Vendas, selecione a planilha e, com o mouse pressionado, selecione a faixa de células C3:C22.


=CONTAR.VAZIO(VENDAS!C3:C22)

Função CONT.NÚMObjetivo: contar o número de células que contêm números em um in-tervalo informado. Serão contadas as células que contenham números, datas ou números escritos entre aspas.

Sintaxe: CONT.NÚM(intervalo1; [intervalo2]; ...)

Argumento: intervalo1;[intervalo2];...: faixas de células que devem ser contadas.

1. Vamos agora contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna Valor da venda que estão preenchidas com números. Selecione a planilha Relatório e clique na célula B9.

2. Digite:

=CONT.NÚM(




3. Como os valores das vendas estão na planilha Vendas, selecione a planilha e, com o mouse pressionado, selecione a faixa de células C3:C22.


=CONT.NÚM(VENDAS!C3:C22)

Função CONT.SEObjetivo: contar o número de ocorrências de uma determinada condi-ção em um intervalo de células informado.

Sintaxe: CONT.SE(intervalo;critério)

Argumentos:

• intervalo: intervalo de células que será considerado;

• critério: condição que será procurada no intervalo de células;

1. Vamos contar quantos vendedores receberão o prêmio, ou seja, aqueles que se encontram em primeiro lugar na colocação de ven-das. Selecione a planilha Relatório e clique na célula B10.

2. Digite:

=CONT.SE(

3. Como queremos vericar se o vendedor está na classicação 1, o intervalo de células considerado deve ser o da coluna Colocação da planilha Vendas. Selecione a planilha Vendas e, com o mouse pressionado, selecione o intervalo D3:D22.


5. Digite 1.


=CONT.SE(VENDAS!D3:D22;1)

7. Vamos agora contar quantos vendedores são do departamento Usa-dos. Para isso devemos contar o número de ocorrências da palavra “usados” no intervalo de células B3:B22 da planilha Vendas. Clique na célula E2 da planilha Relatório.

8. Digite a fórmula:

= CONT.SE(VENDAS!B3:B22;“USADOS”)

Como a palavra “usados”, que é o critério para a função, está na cé-lula D1 da planilha Relatório, a fórmula também poderia ser escrita da seguinte forma:

= CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1)




Função CONT.SESObjetivo: aplicar critérios a células em vários intervalos e contar o nú-mero de vezes que todos os critérios são verdadeiros.

Sintaxe: CONT.SES(intervalo1;critério1;intervalo2;critério2;...)

Argumentos:

• intervalo1: intervalo de células em que será procurado o critério1;

• critério1: condição que será procurada no intervalo de células intervalo1;

• intervalo2: intervalo de células em que será procurado o critério2;

• critério2: condição que será procurada no intervalo de células intervalo2;

Podemos ter até 127 pares de intervalos/critérios.

1. Vamos calcular quantos vendedores do departamento Usados serão ganhadores do prêmio. Veja que agora teremos que considerar duas condições: o vendedor ocupa o primeiro lugar em vendas e trabalha no departamento Usados. Selecione a planilha Relatório e clique na célula E3.

2. Digite:

=CONT.SES(

3. Vamos procurar pela palavra “usados”. Selecione a planilha Vendas e selecione o intervalo B3:B22.Digite ; (ponto e vírgula) e, na plani-lha Relatório, clique na célula D1. Digite ; (ponto e vírgula).

4. Vamos agora procurar pelo número 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha Vendas e o intervalo D3:D22. Digite ; (ponto e vírgula) e digite 1. Pressione a tecla Enter e teremos a seguinte fórmula:

= CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3: D22;1)

Função MÉDIASEObjetivo: calcular a média aritmética dos valores indicados de acordo com determinado critério ou condição.

Sintaxe: MÉDIASE(intervalo; critérios;intervalo_média)

Argumentos:

• intervalo: intervalo de células em que o critério será procurado;

• critério: condição para denir os valores que serão considerados para o cálculo da média;




• intervalo_média: intervalo de células que será considerado para cal-cular a média.

1. Vamos agora calcular a média de vendas para o departamento Usa-dos. Selecione a célula E4 da planilha Relatório e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito para as fórmulas anteriores, para construir a expressão:

= MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS! C3:C22)

Função MÉDIASESObjetivo: calcular a média aritmética em um intervalo de células. As células de um intervalo podem ser adjacentes ou não adjacentes, aten-dendo a vários critérios.

Sintaxe: MÉDIASES(intervalo_média;intervalo_critério1;critério1; intervalo_critério2;critério2;...)

Argumentos:

• intervalo_média: intervalo de valores que serão considerados para o cálculo da média;


• critério1: condição para denir os valores que serão considerados para o cálculo da média;


• critério2: condição para denir os valores que serão considerados para o cálculo da média.

1. Vamos calcular a média aritmética das vendas dos vendedores do departamento Usados e que não são ganhadores do prêmio. Na cé-lula E5 da planilha Relatório, digite a fórmula:

= MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;”<>1”)

Os critérios informados nessa fórmula são os seguintes:

VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da média;

VENDAS!B3:B22 – intervalo em que será procurado o primeiro cri-tério (departamento);




RELATÓRIO!D1 – endereço da célula que contém o primeiro crité-rio (Usados);

VENDAS!D3:D22 –intervalo em que será procurado o segundo cri-tério (colocação);

“<>1” – segundo critério, vendedores que não estão na posição 1.

2. Preencha, considerando o mesmo raciocínio utilizado para as infor-mações do departamento Usados, os quadros relativos aos departa-mentos Zero KM e Isenções da planilha RELATÓRIOS.

3. No nal, você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO:

4. Salve as alterações na pasta.




Funções lógicas

Atividade 5 – Premiando os vendedores que ocupam a primeira posição em vendas e estabelecendo conceitos sobre seu desempenho, de acordo com a meta de vendas estipuladaObjetivo: • Utilizar a função SE.

Tarefas: • Calcular o valor do prêmio para cada um dos vendedores ganhadores.

• Atribuir um conceito de acordo com o valor das vendas de cada vendedor.

Para completar o preenchimento da planilha Vendas da pasta Loja de carros.xlsx, temos ainda algumas tarefas a realizar:

• distribuir o prêmio que a empresa oferece aos vendedores colocados em primeiro lugar;

• atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-o com a meta a ser atingida individualmente.

Função SEObjetivo: efetuar testes condicionais com valores e fórmulas, permitin-do a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro.

Sintaxe: SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)

Argumentos:

• teste_lógico: condição que será avaliada, trazendo Falso ou Verda-deiro como resultado;

• valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro;

• valor_se_falso: o que fazer se o resultado do teste_lógico for Falso.

1. Selecione a planilha Vendas da pasta Loja de carros.xlsx. Vamos vericar na célula E3 se o vendedor está em primeiro lugar na clas-sicação e, se estiver, calcular e mostrar o seu prêmio. Se o vende-dor não estiver em primeiro lugar, o valor que deve ser colocado na célula é 0. Lembre-se de que o prêmio total deve ser dividido igualmente entre os vendedores ganhadores.




O raciocínio que vamos seguir é o seguinte:

SE colocação = 1

ENTÃO dividir o valor do prêmio pelo número de ganhadores e mostrar na célula

SENÃO mostrar o valor 0 na célula

Clique na célula E3 e digite o seguinte:

= SE(D3=1;$B$1/RELATÓRIO!$B$10;0)

Observe que na célula D3 temos a classicação do vendedor, na célula B1 temos o valor do prêmio total e na célula B10 da planilha Relatório temos o número de ganhadores.

2. Copie a fórmula para todos os vendedores. Não se preocupe com o erro apontado na célula E17. Mais adiante vamos modicar as fórmulas para tratar adequadamente esse erro.

3. Vamos agora colocar uma mensagem para cada vendedor de acordo com os seguintes critérios:

CONCEITO CONDIÇÃO

EXCELENTE Colocação do vendedor igual a 1

ÓTIMO Valor das vendas do vendedor maior do que a meta

BOM Valor das vendas do vendedor igual à meta

PODE MELHORAR Nenhuma das condições anteriores

Podemos observar que temos quatro condições para vericar. Isso não será possível por meio de uma única função SE. Nesse caso, teremos que aninhar funções SE para resolver o problema. Até 64 funções SE podem ser aninhadas no Excel 2010.

O raciocínio que devemos seguir é o seguinte:

SE colocação do vendedor = 1

ENTÃO atribuir conceito “EXCELENTE”

SENÃO SE valor das vendas maior do que a meta

ENTÃO atribuir conceito “ÓTIMO”

SENÃO SE valor das vendas igual à meta

ENTÃO atribuir conceito “BOM”

SENÃO atribuir conceito “PODE MELHORAR”

Clique na célula F3 e digite a seguinte fórmula:

=SE(D3=1;”EXCELENTE”;SE(C3>$D$1;”ÓTIMO”;SE(C3= $D$1;”BOM”;”PODE MELHORAR”)))




4. Copie a fórmula para todos os vendedores.

5. Salve as alterações e feche a pasta Loja de carros.xlsx.

Atividade 6 – Premiando vendedores que não ocupam o primeiro lugar em vendas, mas que atingiram metas de venda estipuladasObjetivo: • Utilizar as funções E, OU e SEERRO.

Tarefas: • Distribuir um prêmio extra entre os vendedores que não alcançaram o primeiro lugar de acordo com o valor de suas vendas.

• Tratar adequadamente os erros que possam ocorrer em planilhas.

Em virtude do bom resultado que a premiação para os melhores vende-dores rendeu à loja de automóveis, o gerente resolveu também premiar aqueles vendedores que não foram os primeiros colocados nas vendas, mas que se destacaram em função das metas estabelecidas. Para isso, estabeleceu os seguintes critérios:

• o vendedor que obteve conceito Ótimo recebe como prêmio uma viagem de uma semana com tudo pago para Natal (RN) com direito a acompanhante;

• o vendedor que teve um valor de vendas menor ou igual ao valor da meta de vendas estabelecida e maior ou igual a R$ 100.000,00 recebe como prêmio um computador.

Para poder completar nossa planilha, vamos entender a utilização das funções E, OU e SEERRO.

Função E

Objetivo: retornar o valor Verdadeiro se todos os seus argumentos fo-rem verdadeiros.

Sintaxe: E(lógico1;lógico2;...)

Argumentos:

• lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;

• lógico2;...: condições que serão avaliadas, trazendo Falso ou Verda-deiro como resultado.




Em uma função E podemos colocar até 255 argumentos, ou seja, pode-mos ter até 255 condições lógicas para avaliar.

Função OUObjetivo: retornar o valor Verdadeiro se pelo menos um de seus argu-mentos for verdadeiro.

Sintaxe: OU(lógico1;lógico2;...)

Argumentos:

• lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;

• lógico2;...: condições que serão avaliadas, trazendo Falso ou Verda-deiro como resultado.

Em uma função OU, podemos colocar até 255 argumentos, ou seja, podemos ter até 255 condições lógicas para avaliar.

As funções E e OU são normalmente utilizadas para dar mais exibili-dade a outras funções que executam testes lógicos, como, por exemplo, em conjunto com a função SE.

1. Abra a pasta Itens.xlsx. Vamos vericar a diferença dos resultados entre as funções E e OU.

2. Clique na célula C2. Aparecerá o valor Verdadeiro se o item for Lápis e sua cor for Vermelho. Caso contrário, deverá aparecer o valor Falso.





=E(A2=“Lápis”;B2=”Vermelho”)

4. Copie a fórmula para todos os itens.

5. Clique na célula E2. Aparecerá o valor Verdadeiro se o item for Lápis ou se sua cor for Vermelho. Caso contrário, deverá aparecer o valor Falso.


=OU(A2=“Lápis”;B2=“Vermelho”)

7. Copie a fórmula para todos os itens.

Note que, no caso da função E, o resultado só será Verdadeiro se as duas condições forem verdadeiras. No caso da função OU, o resul-tado será Verdadeiro se pelo menos uma das condições for verdadei-ra e será Falso se as duas condições forem falsas.

8. Feche a pasta Itens.xlsx e abra a pasta Loja de carros.xlsx.

9. Vamos continuar a planilha com a colocação dos prêmios extras. O raciocínio que devemos usar é o seguinte:

SE conceito = ÓTIMO

ENTÃO prêmio extra = viagem para Natal

SENÃO SE vendas <= meta E vendas >100000

ENTÃO prêmio extra = computador

SENÃO deixar a célula vazia




Clique na célula G3 e digite o seguinte:

= SE(F3=“ÓTIMO”;”Viagem para Natal”;SE(E(C3<=$D$1; C3>100000);“Computador”;””))

Observe que, se nenhuma das condições for verdadeira, o valor “” será colocado na célula, ou seja, deixaremos a célula vazia para evi-tar que seja mostrada a mensagem FALSO.


Função SEERROObjetivo: retornar um valor especicado se uma fórmula gerar um erro. Se não existir erro, a função retornará o resultado da fórmula.

Sintaxe: SEERRO(fórmula;valor_se_erro)

Argumentos:

• fórmula: fórmula que desejamos vericar em relação a erros;

• lógico2;...: valor a ser retornado se a fórmula gerar um erro.

Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!.

1. A fórmula na coluna Colocação está gerando um erro no caso em que a célula correspondente à venda está vazia. Os erros podem ocorrer nas planilhas e devem ser tratados de maneira adequada para que a mensagem exibida não seja a mensagem gerada pelo Excel, muitas vezes de difícil compreensão para o usuário. Além disso, erros em células poderão ocasionar mais erros em resultados de cálculos dependentes. É aconselhável, então, armazenar na célu-la um valor mais adequado, evitando mensagens de difícil entendi-mento e outros erros. Nesse caso, se existir o erro na célula, vamos deixá-la vazia, ou seja, vamos armazenar “”. Clique na célula D3 e modique a fórmula para:

=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);””)

Observe que todos os erros da planilha foram solucionados.

É importante saber o valor que deve ser colocado na célula nos ca-sos de erro para não ocasionar novos erros.




2. Feche a pasta Loja de carros.xlsx.

Funções financeiras

Atividade 7 – Calculando o valor futuro de um investimentoObjetivo: • Utilizar a função VF.

Tarefa: • Calcular o valor de um investimento de acordo com uma taxa de juros estipulada e pagamentos iguais.

Fábio deseja guardar dinheiro para montar seu consultório assim que se formar em medicina, dentro de 60 meses. O banco lhe oferece uma apli-cação que rende 1,5% ao mês. Ele tem disponível R$ 500,00 mensais para essa aplicação. Fábio deseja saber a quantia que terá disponível ao nal do curso.

Função VFObjetivo: retornar o valor futuro de um investimento ao nal de um período, de acordo com pagamentos e taxa de juros constantes.

Sintaxe: VF(taxa;nper;pgto;[vp];[tipo])

Argumentos:

• taxa: argumento obrigatório que corresponde à taxa de juros mensal;

• nper: argumento obrigatório que corresponde ao número de parcelas;

• pgto: argumento obrigatório que corresponde ao valor da parcela;

• vp: argumento opcional que corresponde ao valor presente;

• tipo: argumento opcional que pode ser 0 (pagamento no nal do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0.




1. Abra a pasta Contas do Fabio.xlsx.

2. Para saber quanto Fábio terá no nal de 60 meses, pagando R$ 500,00 por mês, com rendimento de 1,5% ao mês, vamos utilizar a função VF. Seus argumentos estão nas seguintes células:

Taxa: célula B5

Nper: célula A5

Pgto: célula C5

Preencha essas células com seus respectivos valores.

3. Na célula D5, digite a fórmula:

=VF(B5;A5;C5)

Atividade 8 – Calculando o valor presente de um investimentoObjetivo: • Utilizar a função VP.

Tarefa: • Calcular o valor à vista de um bem comprado em parcelas a uma taxa de juros mensal xa.

Resolvido o problema do investimento para o consultório, Fábio com-pra uma nova máquina de lavar roupas para o seu apartamento. Para




isso, fez um nanciamento na loja por 24 meses, a uma taxa mensal de juros de 3,5%, pagando R$ 78,00 por mês. Após a compra, sua noiva questiona quanto custaria a máquina se fosse comprada à vista.

Função VPObjetivo: retornar o valor presente de um investimento ao nal de um período, de acordo com pagamentos e taxa de juros constantes.

Sintaxe: VP(taxa;nper;pgto;[vf];[tipo])

Argumentos:


• nper: argumento obrigatório que corresponde ao número de parcelas;


• vf: argumento opcional que corresponde ao valor futuro;


1. Insira os seguintes valores na planilha de Fábio:

Célula A9: 24

Célula B9: 3,5%

Célula C9: 78,00

2. Na célula D9, digite a fórmula:

=VP(B9;A9;C9)

Atividade 9 – Calculando o valor da parcela a ser paga para um investimentoObjetivo: • Utilizar a função PGTO.

Tarefa: • Calcular o valor da parcela xa de um nanciamento a uma taxa de juros constante.




Após o lançamento de um novo modelo de determinado automóvel, a fábrica resolve oferecer aos consumidores o modelo anterior desse automóvel nanciado a uma taxa de juros de 0,5% ao mês. Como são automóveis 0 km, Fábio resolve vericar a oferta. O carro pelo qual se interessa custa R$ 35.000,00, e o nanciamento pode ser feito em até 24 meses. Para saber se a parcela caberá em seu orçamento, Fábio utili-za a função PGTO em sua planilha.

Função PGTOObjetivo: retornar o valor da parcela de um investimento de acordo com pagamentos e taxa de juros constantes.

Sintaxe: PGTO(taxa;nper;vp;[vf];[tipo])

Argumentos:

• taxa: argumento obrigatório que corresponda à taxa de juros mensal;

• nper: argumento obrigatório que corresponda ao número de parcelas;

• vp: argumento obrigatório que corresponda ao valor presente ou principal;

• vf: argumento opcional que corresponda ao valor futuro;



Célula A13: 24

Célula B13: 0,5%

Célula D13: 35.000,00

2. Na célula C13, digite a fórmula:

=PGTO(B13;A13;D13)




Atividade 10 – Calculando o número de parcelas de um investimento a uma taxa de juros e pagamentos constantesObjetivo: • Utilizar a função NPER.

Tarefa: • Calcular o número de parcelas de um investimento con-siderando que a taxa de juros e o valor das parcelas são constantes.

Fábio pensa bem e acha melhor continuar utilizando sua bicicleta para ir ao trabalho e, em vez de comprar o carro 0 km, investir a quantia mensal que iria despender nessa compra em uma aplicação que lhe ren-deria ao nal de um período o valor total de R$ 35.000,00 a uma taxa de juros de 2,4% ao mês. A sua pergunta é: por quantos meses teria que investir R$ 1.555,22 para obter os R$ 35.000,00 desejados?

Função NPERObjetivo: retornar o número de parcelas para um investimento de acor-do com pagamentos e taxa de juros constantes.

Sintaxe: NPER(taxa;pgto;vp;[vf];[tipo])

Argumentos:



• vp: argumento obrigatório que corresponde ao valor presente ou principal;

• vf: argumento opcional que corresponde ao valor futuro;



Célula B17: 2,4%

Célula C17: 1.551,22

Célula D17: 35.000,00

2. Na célula A17, digite a fórmula:

=NPER(B17;C17;D17)




Visualização de datas

Atividade 11 – Formatando datasObjetivo: • Formatar datas.

Tarefa: • Formatar datas para que mostrem o dia da semana, o mês e o formato de dia, mês e ano.

Para o manuseio de datas e horas em nossas planilhas, o Excel oferece uma grande variedade de funções e procedimentos de formatação que possibilitam maior exibilidade no cálculo de dados desse tipo.

Formatação de datasMuitas vezes é necessário visualizar a data de uma forma diferente, como, por exemplo, pelo nome do mês, pelo nome do dia da semana ou mesmo pela data no seu formato completo. Para isso, vamos utilizar os recursos de formatação disponíveis para datas.

Regina trabalha em uma empresa que tem as datas de aniversário de seus clientes cadastradas em uma planilha do Excel no formato dd/mm/aaaa. A empresa solicitou que as datas fossem visualizadas de formas especícas: o dia do mês, o dia da semana, o nome do mês e a data no seu formato completo. Para a realização dessa tarefa, vamos ajudar Regina utilizando procedimentos de formatação personalizada.




1. Abra a pasta Formatacao de datas.xlsx.

2. Na coluna B, vamos mostrar o número do dia da data armazenada. Selecione o intervalo B2:B15.

3. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.

4. Na guia Número, selecione a categoria Personalizado.

5. Apague o que está no campo Tipo e digite dd.




Devemos informar nesse campo o formato desejado para o(s) dado(s) selecionado(s). Na formatação de datas, quando queremos mostrar o número do dia, devemos digitar uma das opções que se seguem:

D dias formados por um algarismo são exibidos com um algarismo.

DD dias formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.

DDD exibe o nome do dia de forma abreviada, usando as três primeiras letras.

DDDD exibe o nome do dia por extenso.

6. Clique em OK.

7. Agora vamos mostrar o nome do dia da semana na coluna C. Para isso, selecione o intervalo C2:C15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.


9. Apague o que está no campo Tipo e digite dddd.

10. Vamos agora exibir na coluna D o nome do mês por extenso. Sele-cione o intervalo D2:D15.

11. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.


13. Apague o que está no campo Tipo e digite mmmm.

A formatação personalizada para datas em relação à exibição do mês pode ser feita por uma das opções:

M meses que são formados por um algarismo são exibidos com um algarismo.

MM meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.

MMM exibe o nome do mês de forma abreviada usando as três primeiras letras.

MMMM exibe o nome do mês por extenso.

14. Clique em OK.

15. Vamos agora mostrar a data por extenso. Por exemplo: para a data 21-06-2011, deverá ser mostrado terça-feira, 21 de junho de 2011.

No campo Tipo, digite o seguinte:

dddd”, “dd” de “mmmm” de “aaa




Onde:

dddd nome do dia da semana.

“ , ” coloca um espaço, vírgula, espaço.

dd número do dia.

“ de ” coloca espaço, a palavra “de”, espaço.

mmmm nome do mês por extenso.

“ de ” coloca espaço, a palavra “de”, espaço.

aaa número do ano.

No caso do ano, a representação aa formata a data para que apa-reçam os dois últimos dígitos do ano, enquanto as representações aaa ou aaaa formatam a data para que apareçam os quatro dígitos do ano.

16. Clique em OK.

iste uma rande diferença entre conte do e formato. onte -do é o alor ori inal do dado armazenado na célula. ormato é como ele está sendo mostrado. uando efetuarmos cálculos com células formatadas, o conte do será considerado, não o formato.

17. Clique na célula C2. Observe que, apesar de ser mostrado o nome do dia da semana (sexta-feira), o valor armazenado na célula é 14/01/2011.




Atividade 12 – Funções de data e horaObjetivos: • Utilizar funções de datas e horas.

• Efetuar cálculos com datas e horas.

Tarefas: • Utilizar as funções de data para mostrar a data de hoje, o ano, dia e mês de uma data, o número do dia da semana.

• Projetar dias úteis a partir de uma data e calcular o número de dias úteis entre duas datas.

• Projetar dias corridos a partir de uma data e calcular o nú-mero de dias corridos entre duas datas.

• Calcular o número de horas e minutos entre dois horários.

A empresa Serviços de Limpeza Ltda. trabalha com um estoque de vassouras que é atualizado diariamente. Em todos os dias úteis entram novas unidades de vassouras e saem outras para serem utilizadas pelos funcionários da empresa em seus serviços terceirizados. Desse modo, a empresa consegue calcular o seu estoque todos os dias. A empresa tra-balha de segunda a sexta-feira e todo início de mês já deixa preparada sua planilha de controle com todos os dias úteis daquele mês para poder inserir os dados e calcular seu estoque diário.

A planilha que vamos desenvolver é referente ao mês de junho de 2011. Atente para o fato de que dia 23 desse mês será feriado e no dia 24 não haverá expediente. No início da planilha serão inseridas informações sobre a data atual.

Função HOJEObjetivo: retornar a data de hoje formatada como data.

Sintaxe: HOJE()

Essa função não possui argumentos. Apesar disso, devemos digitar os parênteses.

Função ANOObjetivo: retornar o ano da data informada como um número inteiro no intervalo de 1900 a 9999.

Sintaxe: ANO(data_informada)

Argumento:

• data_informada: data que se deseja retornar o ano.




Função MÊSObjetivo: retornar o número do mês da data informada como um nú-mero inteiro no intervalo de 1 a 12.

Sintaxe: MÊS(data_informada)

Argumento:

• data_informada: data que se deseja retornar o mês.

Função DIAObjetivo: retornar o dia da data informada como um número inteiro no intervalo de 1 a 31.

Sintaxe: DIA(data_informada)

Argumento:

• data_informada: data que se deseja retornar o dia.

Função DIA.DA.SEMANAObjetivo: retornar o número do dia da semana da data informada como um número inteiro, variando de 1 (domingo) a 7 (sábado), por padrão.

Sintaxe: DIA.DA.SEMANA(data_informada;tipo)

Argumentos:

• data_informada: data que se deseja retornar o número do dia da semana.

• tipo: número que determina o tipo do valor retornado. Este argu-mento é opcional e pode ter os seguintes valores:

TIPO VALOR RETORNADO

1 ou omitido Números 1 (domingo) a 7 (sábado)

2 Números 1 (segunda-feira) a 7 (domingo)



12 Números 1 (terça-feira) a 7 (segunda-feira)

13 Números 1 (quarta-feira) a 7 (terça-feira)

14 Números 1 (quinta-feira) a 7 (quarta-feira)

15 Números 1 (sexta-feira) a 7 (quinta-feira)

16 Números 1 (sábado) a 7 (sexta-feira)

17 Números 1 (domingo) a 7 (sábado)




1. Abra a pasta Vassouras.xlsx.

2. Clique na célula F1.

3. Vamos colocar nessa célula a função para mostrar a data de hoje. Digite:

= HOJE()


5. Vamos colocar nessa célula a função para mostrar o dia da data de hoje. Digite:

= DIA(F1)


7. Vamos colocar nessa célula a função para mostrar o número do mês da data de hoje. Digite:

= MÊS(F1)


9. Vamos colocar nessa célula a função para mostrar o ano da data de hoje. Digite:

= ANO(F1)





11. Vamos colocar nessa célula a função para mostrar o número do dia da semana da data de hoje, considerando 1 como domingo e 7 como sábado. Digite:

= DIA.DA.SEMANA(F1)

Função DIATRABALHO.INTLObjetivo: retornar os dias úteis a projetar a partir de uma data informa-da, considerando os dias da semana que compõem o m de semana e os feriados que possam ocorrer no período.

Sintaxe: DIATRABALHO.INTL(data_inicial;dias;m_de_semana;feriados)

Argumentos:

• data_inicial: data a partir da qual se deseja projetar dias úteis.

• dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor 0 gera a data inicial.

• m_de_semana: argumento opcional. Indica os dias da semana que serão considerados os dias que compõem o nal de semana. Pode ter os seguintes valores:

FIM_DE_SEMANA DIAS DE FIM DE SEMANA

1 ou omitido Sábado, Domingo

2 Domingo, Segunda-feira

3 Segunda-feira, Terça-feira

4 Terça-feira, Quarta-feira

5 Quarta-feira, Quinta-feira

6 Quinta-feira, Sexta-feira

7 Sexta-feira, Sábado

11 Apenas Domingo

12 Apenas Segunda-feira

13 Apenas Terça-feira

14 Apenas Quarta-feira

15 Apenas Quinta-feira

16 Apenas Sexta-feira

17 Apenas Sábado




• feriados: argumento opcional composto pelo conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis.

1. Na pasta Vassouras.xlsx existem duas planilhas: Estoque e Feria-dos. Na planilha Estoque vamos projetar todos os dias úteis do mês de junho/2011 e calcular o estoque diário de vassouras. Na planilha Feriados vamos inserir os dias do mês de junho/2011 que não deve-rão ser considerados como dias úteis.

2. Clique na célula B8 e digite a data 01-06-2011, que corresponde ao primeiro dia do mês de junho.

3. Formate a célula B8 para que apareça somente o nome do mês.

4. Vamos agora projetar o primeiro dia útil de junho a partir do último dia do mês de maio. Essa data é o conteúdo da célula B8 – 1. Clique na célula A11 e digite:

=DIATRABALHO.INTL(B8-1;

5. Como desejamos obter o primeiro dia útil a partir dessa data, digite 1;.

6. O nosso nal de semana é composto por sábado e domingo. Logo, o parâmetro que deve ser informado para m_de_semana é 1. Con-tinue digitando 1;.

7. Na planilha Feriados, na coluna A, estão os feriados do mês de ju-nho de 2011. Selecione a planilha Feriados e clique sobre a indica-ção da coluna A. Vamos marcar a coluna inteira para evitar refazer as fórmulas caso seja inserido algum dia não previsto. Pressione Enter e você terá a seguinte fórmula na célula:

= DIATRABALHO.INTL(B8-1;1;1;FERIADOS!A:A)

8. Vamos agora calcular o próximo dia útil a partir da célula A11. Cli-que na célula A12 e digite a seguinte fórmula:

= DIATRABALHO.INTL(A11;1;1;FERIADOS!A:A)

9. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de junho de 2011.

10. Copie somente os valores da coluna A para a coluna B e formate as datas para que apareçam os nomes dos dias da semana.

11. Calcule o estoque do primeiro dia como sendo o Estoque Anterior + Quantidade Entrada – Quantidade Saída.

12. Calcule o Estoque Anterior do segundo dia como sendo o estoque nal do dia anterior.

13. Copie as fórmulas para todos os dias.




14. Feche a pasta Vassouras.xlsx.

A empresa ZAP S/A contrata mão de obra terceirizada para alguns ser-viços administrativos e de contabilidade, além de possuir alguns fun-cionários contratados. Para a mão de obra terceirizada, ela conta com os serviços de três empresas: Adm Mão de Obra, Contábil Total e Lucas Bros. Essas empresas trabalham por contrato e recebem por hora traba-lhada, e a carga horária diária varia em cada contrato. No período de 01/01/2011 a 31/07/2011 a empresa ZAP fechou vários contratos com essas empresas. Ao nal de julho, precisa emitir um relatório demons-trativo dos pagamentos efetuados aos terceirizados. Para isso, deve cal-cular os dias trabalhados e o valor a ser pago em cada contrato. As em-presas terceirizadas prestam serviços à ZAP de segunda-feira a sábado.

Função DIATRABALHOTOTAL.INTLObjetivo: retornar o número de dias úteis entre duas datas consideran-do os nais de semana e feriados no período.

Sintaxe: DIATRABALHOTOTAL.INTL(data_inicial;data_nal;m_de_ semana;feriados)

Argumentos:

• data_inicial: data a partir da qual se deseja projetar dias úteis.

• data_nal: data até a qual se deseja projetar dias úteis.




• m_de_semana: argumento opcional. Indica que dias da semana compõem o nal de semana. Pode ter os seguintes valores:

FIM_DE_SEMANA DIAS DE FIM DE SEMANA

1 ou omitido Sábado, Domingo

2 Domingo, Segunda-feira

3 Segunda-feira, Terça-feira

4 Terça-feira, Quarta-feira

5 Quarta-feira, Quinta-feira

6 Quinta-feira, Sexta-feira

7 Sexta-feira, Sábado

11 Apenas Domingo

12 Apenas Segunda-feira

13 Apenas Terça-feira

14 Apenas Quarta-feira

15 Apenas Quinta-feira

16 Apenas Sexta-feira

17 Apenas Sábado

• feriados: argumento opcional, que é um conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis.

1. Abra a pasta ZAP.xlsx.

2. O valor a ser pago para cada empresa considera os dias úteis tra-balhados e não os dias corridos. Vamos então calcular quantos são os dias úteis compreendidos entre a data inicial e a data nal dos




contratos. Para isso, utilizaremos a planilha Feriados, que se en-contra nesta pasta. Atenção: como a empresa ZAP está localizada na cidade de São Paulo, os feriados informados referem-se aos fe-riados municipais (da cidade de São Paulo) e estaduais (do estado de São Paulo).

Na célula F6, digite:

=DIATRABALHOTOTAL.INTL(C6;D6;11;FERIADOS!A:A)

Em que:

C6: data inicial.

D6: data nal.

11: nal de semana formado somente por domingo.

FERIADOS!A:A: lista de feriados no ano de 2011.

3. Copie a fórmula para todos os contratos.

4. Vamos agora calcular quanto cada contrato deve receber, sabendo que o valor pago por hora está armazenado na célula B3. Clique na célula G6 e digite a fórmula:

=F6*E6*$B$3

5. Copie a fórmula para todos os contratos:


A Lan House NETHOUSE & CIA. oferece acesso à internet cobrando determinado valor por hora de utilização. O cálculo é efetuado con-siderando o tempo exato de utilização, ou seja, todos os minutos são cobrados. No horário da manhã (das 9 h às 11h59), a Lan House oferece um desconto de 20% no valor da hora de utilização dos computadores. Nosso trabalho é elaborar uma planilha para controlar os valores pagos pelos usuários de um computador no dia 01/06/2011.




Função HORAObjetivo: retornar a hora de um valor de tempo como um inteiro, va-riando de 0 (12:00 a.m.) a 23 (11:00 p.m.).

Sintaxe: HORA(valor_tempo)

Argumento:

• valor_tempo: horário que contém a hora que desejamos encontrar.

Função MINUTOObjetivo: retornar os minutos de um valor de tempo como um número inteiro que varia de 0 a 59.

Sintaxe: MINUTO(valor_tempo)

Argumento:

• valor_tempo: horário que contém os minutos que desejamos en-contrar.

1. Abra a pasta LAN HOUSE.xlsx.

2. Vamos calcular o tempo de utilização de cada usuário. Para isso, de-vemos subtrair a hora inicial da hora nal. Clique na célula D5 e digite:

=C5-B5

3. Copie a fórmula para todos os usuários.

4. Agora vamos calcular o número de horas do tempo total. Clique na célula E5 e digite:

=HORA(D5)


6. Vamos calcular o número de minutos do tempo total. Clique na cé-lula F5 e digite:

=MINUTO(D5)





8. Vamos calcular o tempo total em minutos. Clique na célula G5 e digite:

=E5*60+F5

9. Vamos agora calcular o valor do minuto. Lembre-se: se a hora ini-cial for menor do que 12 h o usuário terá um desconto de 20% sobre o valor da hora. Clique na célula H5 e digite:

=SE(HORA(B5)<=11;($B$2*80%)/60;$B$2/60)

10. Agora devemos calcular o valor total a ser pago pelos usuários. Cli-que na célula I5 e digite:

=G5*H5




Resumo do capítulo

Para Procedimento Botão

Utilizar a função SOMA Digite o sinal de igual na célula em que pretende obter a soma. Digite o nome da função SOMA e selecione o intervalo a ser somado.

Utilizar a função MULT Digite o sinal de igual na célula em que pretende obter a multiplicação. Digite o nome da função MULT e selecione o intervalo a ser multiplicado.

Utilizar a função SOMARPRODUTO

Digite o sinal de igual na célula em que pretende obter a soma da multiplicação. Digite o nome da função SOMARPRODUTO e selecione os intervalos que devem ser multiplicados e somados.

Utilizar a função SOMASE

Digite o sinal de igual na célula em que pretende obter a soma. Digite o nome da função SOMASE, selecione o intervalo de células que serão testadas, insira o critério e selecione o intervalo de células que serão somadas.

Utilizar a função SOMASES

Digite o sinal de igual na célula em que pretende obter a soma. Digite o nome da função SOMASES, selecione o intervalo de células que serão somadas, selecione o intervalo de células que serão testadas e insira o critério. Repita o procedimento para todas as condições desejadas.

Utilizar a função ALEATÓRIOENTRE

Digite o sinal de igual na célula em que pretende obter o número aleatório. Digite o nome da função ALEATÓRIOENTRE e informe o valor do menor número inteiro que pode ser gerado e o valor do maior número inteiro que pode ser gerado.

Utilizar a função MODO.ÚNICO

Digite o sinal de igual na célula em que pretende obter o valor que aparece com maior frequência. Digite o nome da função MODO.ÚNICO e informe o conjunto de valores numéricos dentre os quais deseja obter o que aparece com maior frequência.





Utilizar a função MÁXIMO

Digite o sinal de igual na célula em que pretende obter o maior valor em um intervalo. Digite o nome da função MÁXIMO e selecione o intervalo de células em que pretende obter o maior valor numérico.

Utilizar a função MÍNIMO

Digite o sinal de igual na célula em que pretende obter o menor valor em um intervalo. Digite o nome da função MÍNIMO e selecione o intervalo de células em que pretende obter o menor valor numérico.

Utilizar a função MÉDIA

Digite o sinal de igual na célula em que pretende obter a média. Digite o nome da função MÉDIA e selecione o intervalo em que deseja obter a média aritmética.

Utilizar a função ORDEM.EQ

Digite o sinal de igual na célula em que pretende obter a posição de um número. Digite o nome da função ORDEM.EQ, informe o número desejado e selecione o intervalo de células em que deseja obter a posição do número.

Utilizar a função CONT.VALORES

Digite o sinal de igual na célula em que pretende obter contagem de valores. Digite o nome da função CONT.VALORES e informe o intervalo de células em que deverão ser contados os conteúdos não vazios.

Utilizar a função CONTAR.VAZIO

Digite o sinal de igual na célula em que pretende obter contagem de células vazias. Digite o nome da função CONTAR.VAZIO e informe o intervalo de células em que deverão ser contados os conteúdos vazios.

Utilizar a função CONT.NÚM

Digite o sinal de igual na célula em que pretende obter contagem de números. Digite o nome da função CONT.NÚM e informe o intervalo de células em que deverão ser contados os conteúdos numéricos.





Utilizar a função CONT.SE

Digite o sinal de igual na célula em que pretende obter contagem de valores. Digite o nome da função CONT.SE, selecione o intervalo de células em que deverão ser contados os valores e informe o critério.

Utilizar a função CONT.SES

Digite o sinal de igual na célula em que pretende obter contagem de valores. Digite o nome da função CONT.SES, selecione o intervalo de células em que deverão ser contados os valores e informe o critério. Repita o procedimento para todas as condições desejadas.

Utilizar a função MÉDIASE

Digite o sinal de igual na célula em que pretende obter a média. Digite o nome da função MÉDIASE, selecione o intervalo em que será testado o critério, informe o critério e selecione o intervalo que será considerado para o cálculo da média aritmética.

Utilizar a função MÉDIASES

Digite o sinal de igual na célula em que pretende obter a média. Digite o nome da função MÉDIASES, selecione o intervalo que será considerado para o cálculo da média aritmética, selecione e informe o intervalo em que será testado o critério. Repita o procedimento para todas as condições desejadas.

Utilizar a função SE Digite o sinal de igual na célula em que pretende colocar um valor de acordo com uma condição estipulada. Digite o nome da função SE. Indique a condição a ser testada, o procedimento que deve ser executado caso a condição seja verdadeira e o procedimento que deve ser executado, caso a condição seja falsa.

Utilizar a função E Digite o sinal de igual na célula em que pretende obter o valor Falso ou Verdadeiro. Digite o nome da função E e informe todas as condições que devem ser testadas. Essa função é mais frequentemente utilizada em conjunto com a função SE.





Utilizar a função OU Digite o sinal de igual na célula em que pretende obter o valor Falso ou Verdadeiro. Digite o nome da função OU e informe todas as condições que devem ser testadas. Essa função é mais frequentemente utilizada em conjunto com a função SE.

Utilizar a função SEERRO

Digite o sinal de igual na célula em que pretende gerenciar um erro. Digite o nome da função SEERRO e informe a fórmula a ser testada e o valor a ser retornado na ocorrência de erro.

Utilizar a função VF Digite o sinal de igual na célula em que pretende obter o valor futuro de um investimento. Digite o nome da função VF e informe a taxa mensal de juros, o número de parcelas e o valor das parcelas.

Utilizar a função VP Digite o sinal de igual na célula em que pretende obter o valor presente de um investimento. Digite o nome da função VP e informe a taxa mensal de juros, o número de parcelas e o valor das parcelas.

Utilizar a função PGTO Digite o sinal de igual na célula em que pretende obter o valor da parcela de um nanciamento. Digite o nome da função PGTO e informe a taxa mensal de juros, o número de parcelas e o valor principal.

Utilizar a função NPER Digite o sinal de igual na célula em que pretende obter o número de parcelas de um investimento. Digite o nome da função NPER e informe a taxa mensal de juros, o valor da parcela e o valor principal.

Formatar datas Selecione a célula ou faixa de células que será formatada. Clique no iniciador da caixa de diálogo Número da guia Página Inicial. Na guia Número, selecione a opção Personalizado.





Utilizar a função HOJE Digite o sinal de igual na célula em que pretende obter a data de hoje. Digite o nome da função HOJE.

Utilizar a função ANO Digite o sinal de igual na célula em que pretende obter o valor do ano de uma data. Digite o nome da função ANO e informe a data desejada.

Utilizar a função MÊS Digite o sinal de igual na célula em que pretende obter o número do mês de uma data. Digite o nome da função MÊS e informe a data desejada.

Utilizar a função DIA Digite o sinal de igual na célula em que pretende obter o número do dia de uma data. Digite o nome da função DIA e informe a data desejada.

Utilizar a função DIA.DA.SEMANA

Digite o sinal de igual na célula em que pretende obter o número do dia da semana de uma data. Digite o nome da função DIA.DA.SEMANA e informe a data desejada.

Utilizar a função DIATRABALHO.INTL

Digite o sinal de igual na célula em que pretende projetar os dias úteis a partir de uma data. Digite o nome da função DIATRABALHO.INTL e informe a data desejada e o número de dias úteis antes ou depois da data informada.

Utilizar a função DIATRABALHOTOTAL.INTL

Digite o sinal de igual na célula em que pretende obter o número de dias úteis entre duas datas. Digite o nome da função DIATRABALHOTOTAL.INTL e informe a data inicial e a data nal.

Utilizar a função HORA Digite o sinal de igual na célula em que pretende obter a hora de um valor de tempo. Digite o nome da função HORA e informe o valor tempo.

Utilizar a função MINUTO

Digite o sinal de igual na célula em que pretende obter os minutos de um valor de tempo. Digite o nome da função MINUTO e informe o valor tempo.


2Funções de pesquisa

O B J E T I V O Utilizar as funções de pesquisa PROCV, PROCH, CORRESP e ÍNDICE.




Funções de pesquisa

Funções de pesquisaAs funções de pesquisa do Excel permitem encontrar valores em regiões determinadas de uma planilha de forma rápida e precisa. Na maioria dos casos, substituem a utilização de funções SE encadeadas.

Atividade 1 – Utilizando a função PROCVObjetivo: • Utilizar a função PROCV.

Tarefas: • Abrir a pasta Techby.xlsx.

• Preencher a planilha com os nomes e valores dos cursos.

• Abrir a pasta Descontos.xlsx.

• Calcular o valor do Imposto de Renda retido na fonte e a contribuição mensal referente ao INSS para os funcioná-rios da empresa Brinquedos & Brincadeiras.

A escola Techby oferece cursos de informática em três de suas liais: Vila Mariana, Aclimação e Itaim Bibi. Para seu controle, mantém um cadastro com o nome do aluno e o código do curso dos alunos que participam de seus cursos. Para permitir uma melhor visualização dos dados, o responsável pela área administrativa da escola criou uma pla-nilha com o código, o nome e o valor das mensalidades de cada curso. Nosso objetivo é atualizar a planilha com os dados dos alunos, preen-chendo-a com o nome e o valor da mensalidade dos cursos.

Função PROCVObjetivo: procurar por um valor na primeira coluna de um intervalo de células e retornar um valor da mesma linha em uma coluna espe-cicada.

Sintaxe: PROCV(valor_procurado;intervalo_células;núm_coluna,[pro- curar_intervalo])

Argumentos:

• valor_procurado: valor a ser procurado na primeira coluna do inter-valo de células. Pode ser um valor ou uma referência;

• intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os va-lores na primeira coluna de intervalo_células são os valores procu-rados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas;

• núm_coluna: número da coluna do intervalo de células que contém a informação que deve ser retornada;




• procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira coluna do intervalo, se a correspondência deverá ser exata ou aproxi-mada. Se esse argumento for VERDADEIRO ou omitido, a correspon-dência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira coluna do intervalo deverão estar classicados em ordem ascendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Nesse caso, os valores da primeira coluna do intervalo não precisam estar classicados e se houver mais de um valor que coincida com o valor_procurado será considerado o primeiro encontrado.

1. Abra a pasta Techby.xlsx.




2. Nessa pasta temos duas planilhas: Cadastro, que contém os nomes dos alunos, a lial em que realizaram o curso e o código do curso frequentado, e Tabela, que contém o código, o nome e o valor da mensalidade dos cursos. Vamos preencher a coluna Nome Curso, como o próprio nome indica, com o nome do curso. Para isso, va-mos procurar pelo código do curso na tabela da planilha Tabela e trazer o seu nome.

Na célula D4 da planilha CADASTRO, digite:

=PROCV(C4;TABELA!$A$2:$B$10;2)

Os argumentos informados são os seguintes:

C4: célula que contém o código do curso e que corresponde ao valor que será procurado na tabela da planilha Tabela.

TABELA!$A$2:$B$10: região em que será procurado o código do curso.

2: coluna que contém o valor que deve ser retornado. No caso, co-luna que contém o nome do curso.

3. Copie a fórmula para todos os alunos.

4. Clique na célula E4. Vamos agora preencher a coluna Valor da Mensalidade com o valor encontrado na tabela da planilha Tabela. Digite:

=PROCV(C4;TABELA!$A$2:$C$10;3)

Os argumentos são os seguintes:

C4: célula que contém o código do curso e que corresponde ao valor que será procurado na tabela da planilha Tabela.

TABELA!$A$2:$C$10: região em que será procurado o código do curso.

3: coluna que contém o valor que deve ser retornado. No caso, co-luna que contém o valor da mensalidade do curso.

5. Copie a fórmula para todos os alunos.





A empresa Brinquedos & Brincadeiras controla os descontos men-sais de sua folha de pagamentos por meio de uma planilha que cal-cula, mensalmente, quanto cada funcionário pagará de Imposto de Renda e INSS. Esses descontos são calculados de acordo com o salário bruto do funcionário. Para o cálculo dos valores em 2011 foram consideradas as duas tabelas que se seguem.

Imposto de Renda

Salário bruto (R$) Alíquota (%) Parcela a deduzir R$

Até 1.499,15 – –

De 1.499,16 a 2.246,75 7,5 112,43

De 2.246,76 a 2.995,70 15,0 280,94

De 2.995,71 a 3.743,19 22,5 505,62

Acima de 3.743,19 27,5 692,78




INSS

Salário bruto (R$) Alíquota (%)

Até 1.106,90 8,00

De 1.106,91 a 1.844,83 9,00

De 1.844,84 a 3.689,66 11,00

Salários acima de 3.689,66 sofrem desconto de R$ 405,86, independentemente do seu valor.

7. Abra a pasta Descontos.xlsx.

8. Vamos preencher a coluna IR com o valor do Imposto de Renda a ser descontado do funcionário. As alíquotas e parcelas a deduzir estão na tabela IR. Para encontrar os valores corretos, vamos procu-rar pelo valor do salário bruto na primeira coluna dessa tabela. Na célula C4, digite:

= PROCV(A2;Suporte!$A$2:$B$23;2)

9. Se parássemos a fórmula por aqui, teríamos somente a alíquota do imposto. Mas o que deve ser colocado na célula é o valor do impos-to, ou seja, o salário bruto multiplicado pela alíquota. Vamos então completar a fórmula multiplicando o valor da alíquota pelo valor do salário bruto:

=PROCV(B4;$I$4:$J$8;2)*B4

10. Copie a fórmula para todos os funcionários.

11. Vamos agora procurar pela parcela a deduzir no cálculo do imposto de renda. Clique na célula D4 e digite a fórmula:

=PROCV(B4;$I$4:$K$8;3)




Observe que a região considerada nesse caso é I4:K8, pois o valor que desejamos que retorne é o que está na terceira coluna da tabela IR. Copie a fórmula para todos os funcionários.

12. O desconto real de Imposto de Renda corresponde ao valor calculado com a alíquota menos a parcela a deduzir. Clique na célula E4 e digite:

=C4-D4


14. Vamos agora calcular a contribuição para o INSS. Essa contribuição corresponde ao salário bruto multiplicado pela alíquota correspon-dente. No entanto, se o salário bruto for superior a R$ 3.689,66, o desconto será no valor de R$ 405,86, independentemente do valor do salário. Na célula F4, digite:

=SE(B4>3689,66;405,86;PROCV(B4;$M$4:$N$6;2)*B4)

O raciocínio utilizado nessa fórmula é o seguinte:

SE salário > R$ 3.689,66 ENTÃO

coloque na célula o valor R$ 405,86

SENÃO

procure pelo valor do salário em M4:N6 e retorne o valor da coluna 2

multiplique o valor retornado pelo valor do salário





Raquel recebeu a incumbência de preencher a planilha CLIENTES com as informações A, B, C ou D de acordo com a renda mensal dos clientes da loja onde trabalha. Os critérios estabelecidos foram os seguintes:

Renda mensal Classicação

Até R$ 800,00 D

Acima de R$ 800,00 e abaixo de R$ 2.000,00 C

Acima de R$ 2.000,00 e abaixo de R$ 4.000,00 B

Acima de R$ 4.000,00 A

Esses critérios foram colocados na planilha CLASSIFICAÇÃO. Vamos ajudar Raquel na sua tarefa.

Função PROCHObjetivo: procurar por um valor na primeira linha de um intervalo de células e trazer outro valor da mesma coluna em outra linha especi-cada.

Sintaxe: PROCH(valor_procurado;intervalo_células;núm_linha,[pro- curar_intervalo])

Argumentos:

• valor_ procurado: valor a ser procurado na primeira linha do inter-valo de células. Pode ser um valor ou uma referência;

• intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os va-lores na primeira linha de intervalo_células são os valores procu-rados por valor_ procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas;

• núm_linha: número da linha do intervalo de células que contém a informação que deve ser retornada;

• procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira linha do intervalo, se a correspondência deverá ser exata ou aproxi-mada. Se esse valor for Verdadeiro ou omitido, a correspondência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira linha do intervalo deverão estar classicados em ordem as-cendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for Falso, a correspondência se dará de forma exata. Nesse caso, os valores da primeira linha do intervalo não precisam estar classicados e, se houver mais de um valor que coincida com o valor_ procurado, será considerado o primeiro encontrado.




A diferença entre as funções PROCV e PROCH é que a primeira efe-tua a procura de forma vertical, enquanto a segunda o faz de forma horizontal. Você pode utilizar qualquer uma das duas, dependendo da disposição do intervalo de células adotada.

16. Abra a pasta Classes.xlsx. Nessa pasta existem duas planilhas: Clientes e Classicação.

17. Vamos preencher a coluna Classicação de acordo com a faixa de renda mensal de cada cliente. Os valores que devem ser colocados nessa coluna estão na planilha Classicação. Selecione a planilha Clientes. Clique na célula C2 e digite a seguinte fórmula:

=PROCH(B2;CLASSIFICAÇÃO!$B$1:$E$2;2)

18. Copie a fórmula para todos os clientes.




Atividade 2 – Utilizando as funções CORRESP e ÍNDICEObjetivo: • Utilizar as funções CORRESP e ÍNDICE.

Tarefas: • Calcular os valores máximo e mínimo para renda mensal.

• Identicar a posição desses valores dentro da planilha Clientes.

• Identicar os nomes dos clientes que apresentam os valo-res máximo e mínimo para renda mensal.

Função CORRESPObjetivo: retornar a posição em que o valor procurado encontra-se no intervalo de células indicado, que deve indicar uma linha ou uma colu-na de células.

Sintaxe: CORRESP(valor_procurado;intervalo;[correspondência])

Argumentos:

• valor_procurado: argumento obrigatório e que corresponde ao va-lor que desejamos procurar no intervalo de células e obter sua posi-ção relativa dentro dele;

• intervalo: argumento obrigatório e que corresponde ao intervalo de células pesquisadas. Esse intervalo deve ser uma linha ou uma co-luna de células;

• correspondência: argumento opcional que especica como o valor será encontrado. Pode ser:

1 ou omitido: Localiza o maior valor que é menor ou igual ao va-lor_procurado. Nesse caso, os dados no intervalo de-vem estar classicados em ordem crescente.

0: Localiza o primeiro valor que é exatamente igual ao valor_procurado. Os dados no intervalo podem estar em qualquer ordem.

–1: Localiza o menor valor que é maior ou igual ao valor_procurado. Nesse caso, os dados no interva-lo devem estar classicados em ordem decrescente.

1. Vamos vericar em que local da planilha se encontram os valo-res para a maior e a menor renda mensal. Selecione a planilha CLIENTES.




2. Calcule a maior e a menor renda mensal nas células F2 e F3.

3. Clique na célula G2 e digite:

= CORRESP(

4. Vamos procurar pelo valor da maior renda mensal, que está na célu-la F2. Continue a fórmula digitando F2;

= CORRESP(F2;

5. Vamos agora informar o intervalo de células em que será procurado o valor da renda mensal. Esse intervalo é B2:B10.

= CORRESP(F2;B2:B10;

6. Como desejamos que o Excel encontre exatamente o valor, o próxi-mo parâmetro será 0. A fórmula nal é a seguinte:

=CORRESP(F2;B2:B10;0)

Observe que o resultado corresponde ao número da linha que o va-lor procurado ocupa na faixa de dados indicada e não na planilha.

7. Repita os mesmos procedimentos para encontrar a posição da me-nor renda mensal.

Função ÍNDICEObjetivo: retornar o conteúdo de uma célula pelo fornecimento de sua posição dentro do intervalo de células.

Sintaxe: ÍNDICE(intervalo;linha;[coluna])

Argumentos:

• intervalo: argumento obrigatório que corresponde ao intervalo de células que contém o dado a ser retornado;




• linha: argumento obrigatório que corresponde ao número da linha do intervalo em que está o dado a ser retornado;

• coluna: argumento opcional que corresponde ao número da coluna do intervalo em que está o dado a ser retornado.

1. Vamos agora retornar os nomes dos clientes que possuem a maior e a menor renda mensal. Clique na célula H2 e digite:

= ÍNDICE(

2. Devemos informar o intervalo de células que contém o nome do cliente que está sendo procurado. Para tanto, digite na fórmula:

= ÍNDICE(A2:A10;

3. Agora devemos informar em que linha desse intervalo encontra-se o dado desejado. Pela função CORRESP obtivemos, anteriormente, o número da linha em que está a maior renda mensal. Portanto, o parâmetro que devemos informar na função é exatamente o resulta-do da função CORRESP, que se encontra na célula G2. A fórmula completa ca então:

=ÍNDICE(A2:A10;G2)

4. Utilize os mesmos procedimentos para trazer o nome do cliente que possui a menor renda mensal.




Resumo do capítulo


Utilizar a função PROCV

Digite o sinal de igual na célula em que pretende obter o valor procurado. Digite o nome da função PROCV e informe o conteúdo a ser procurado, o intervalo de células em que o conteúdo será procurado e o número da coluna que contém o valor encontrado.

Utilizar a função PROCH

Digite o sinal de igual na célula em que pretende obter o valor procurado. Digite o nome da função PROCH e informe o conteúdo a ser procurado, o intervalo de células em que o conteúdo será procurado e o número da linha que contém o valor encontrado.

Utilizar a função CORRESP

Digite o sinal de igual na célula em que pretende obter a posição de um valor. Digite o nome da função CORRESP e informe o valor e o intervalo de células.

Utilizar a função ÍNDICE

Digite o sinal de igual na célula em que pretende obter o conteúdo de uma célula. Digite o nome da função ÍNDICE e informe o intervalo de células que contém o conteúdo procurado e o número da linha.


3Funções de texto

O B J E T I V O Utilizar as funções de texto ARRUMAR, DIREITA, ESQUERDA, EXT.TEXTO, MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA.




Funções de texto

Funções de textoAs funções de texto do Excel permitem que o tratamento de infor-mações de texto seja aprimorado. Algumas dessas funções corrigem eventuais erros ou vícios de digitação, como excesso de espaços em branco e nomes escritos em letras minúsculas. Outras funções per-mitem o trabalho com partes da informação, tornando o processo de pesquisa mais preciso.

Atividade 1 – Utilizando as funções PRI.MAIÚSCULA, ARRUMAR, MAIÚSCULA e MINÚSCULAObjetivo: • Utilizar as funções de texto PRI.MAIÚSCULA, ARRU-

MAR, MAIÚSCULA e MINÚSCULA.

Tarefas: • Abrir a pasta Hotel.xlsx.

• Converter os nomes dos hóspedes na forma correta, com a primeira letra do nome e do sobrenome em letras maiúsculas.

• Eliminar os espaços em branco em excesso do tipo de acomodação.

• Converter o conteúdo da coluna Gastos Extras em letras maiúsculas.

O Hotel Samambaia Tropical efetua o controle de gastos e diárias de seus hóspedes por meio de planilhas. No mês de janeiro a planilha foi preenchida por um funcionário que não tinha muita experiência em digitação, causando a ocorrência de erros em cálculos e dados for-matados incorretamente. O gerente do hotel, então, solicitou a outra funcionária que efetuasse as correções necessárias para que a plani-lha mostrasse os resultados adequadamente. Essa funcionária terá que efetuar os seguintes procedimentos:

• Corrigir os nomes dos hóspedes para que sejam exibidos de for-ma correta, colocando as primeiras letras de nome e sobrenome em maiúsculas.

• Eliminar os espaços em branco excedentes da identicação do tipo de acomodação, para que os valores sejam encontrados corretamen-te na respectiva tabela, por meio da função PROCV.

• Transformar os dados referentes a gastos extras em letras maiúsculas.

• Calcular o valor a ser pago pelos hóspedes considerando os dias de permanência, o valor da diária correspondente ao tipo de acomoda-ção e os gastos extras efetuados.



Funções de texto

Função PRI.MAIÚSCULAObjetivo: colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia de texto.

Sintaxe: PRI.MAIÚSCULA(texto)

Argumento:

• texto: texto que se deseja converter parcialmente em maiúsculas.

1. Abra a pasta Hotel.xlsx.

2. Selecione a planilha Corrigida.

3. Vamos corrigir os nomes dos hóspedes colocando em letras maiús-culas a primeira letra do nome e a do sobrenome. Clique na célula A4 e digite:

=PRI.MAIÚSCULA(ORIGINAL!A4)

4. Copie a fórmula para todos os hóspedes.



Funções de texto

Função ARRUMARObjetivo: eliminar os espaços em branco à esquerda e à direita do texto.

Sintaxe: ARRUMAR(texto)

Argumento:

• texto: texto do qual se deseja remover espaços à direita ou à esquerda.

5. Os dados da coluna Acomodação foram digitados com excesso de espaços em branco tanto no início quanto no nal. Consequente-mente, a procura pelo tipo de acomodação na planilha Acomodações não traz os resultados corretos. É necessário, então, corrigir esse erro, eliminando os espaços excedentes nos dados digitados. Clique na célula D4 e digite:

=ARRUMAR(ORIGINAL!D4

6. Copie a fórmula para todas as acomodações.

7. Vamos agora preencher a coluna Valor Diária. O valor da diária a ser pago pelo hóspede depende do tipo de acomodação. Na planilha Acomodações temos uma tabela com os tipos de acomodação e seus respectivos valores. Vamos, então, utilizar a função PROCV para encontrar o valor da diária correspondente ao valor da acomodação utilizada pelo hóspede.

Clique na célula E4 e digite a fórmula:

=PROCV(D4;ACOMODAÇÕES!$A$3:$B$7;2;FALSO)

Observe a colocação do argumento Falso, indicando que a procura pelo tipo de acomodação deve ser pelo valor exato e não aproxima-do. Para que esse critério fosse dispensável, seria obrigatório que a coluna Tipo estivesse classicada de forma ascendente.

8. Copie a fórmula para todas as células.

9. O próximo passo é calcular o número de diárias. Esse valor cor-responde à Data de Saída – Data de Entrada. Clique na célula F4 e digite a fórmula:

=C4-B4



Funções de texto

10. Copie a fórmula para todas as células.

Função MAIÚSCULAObjetivo: converter o texto em caracteres maiúsculos.

Sintaxe: MAIÚSCULA(texto)

Argumento:

• texto: texto que se deseja converter em maiúsculas.

Função MINÚSCULAObjetivo: converter o texto em caracteres minúsculos.

Sintaxe: MINÚSCULA(texto)

Argumento:

• texto: texto que se deseja converter em minúsculas.

11. A coluna Gastos Extras deve ter como conteúdo os dados exibi-dos em letras maiúsculas. Para transformar o que foi digitado em maiúsculas, vamos utilizar a função MAIÚSCULA. Na célula G4, digite a fórmula:

=MAIÚSCULA(ORIGINAL!G4)

Copie a fórmula para todas as células.



Funções de texto

12. Para terminar o preenchimento da planilha, vamos calcular quanto será o valor que o hóspede deve pagar ao fechar sua conta. Clique na célula I4 e digite:

=F4*E4+H4



Atividade 2 – Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTOObjetivo: • Utilizar as funções ESQUERDA, DIREITA e EXT.TEXTO.

Tarefas: • Retornar as letras das chapas dos automóveis.

• Retornar as duas últimas letras das chapas dos automóveis.

• Retornar o último número das chapas dos automóveis.

• Mostrar os automóveis que estão no rodízio na data atual.

Desde 1997 está em vigor, na cidade de São Paulo, o rodízio munici-pal de veículos, com o objetivo de melhorar as condições ambientais e reduzir os congestionamentos nas principais vias da cidade nos ho-rários de maior movimento. Os automóveis e caminhões não podem circular em determinada região, denominada Centro Expandido, entre 7h e 10h e entre 17h e 20h de segunda a sexta-feira. O dia do rodízio é determinado pelo nal da placa do automóvel, de acordo com a seguinte tabela:

Veículos com nal de chapa 1 ou 2 não circulam às segundas-feiras.

Veículos com nal de chapa 3 ou 4 não circulam às terças-feiras.

Veículos com nal de chapa 5 ou 6 não circulam às quartas-feiras.

Veículos com nal de chapa 7 ou 8 não circulam às quintas-feiras.

Veículos com nal de chapa 9 ou 0 não circulam às sextas-feiras.

O Condomínio Orquídea coloca todos os dias, em seus elevadores, uma lista alertando quais automóveis estão proibidos de circular. Para esse serviço, utiliza a pasta Rodízio-SP, que vamos desenvolver nesta atividade.



Funções de texto

Função ESQUERDAObjetivo: retornar os primeiros n caracteres à esquerda de uma sequên-cia de texto.

Sintaxe: ESQUERDA(texto;[número_caracteres])

Argumentos:

• texto: texto do qual se deseja extrair os caracteres à esquerda.

• número_caracteres: argumento opcional que indica o número de ca-racteres à esquerda que se deseja extrair do texto. Se omitido, trará o primeiro caractere à esquerda.

Função DIREITAObjetivo: retornar os últimos n caracteres à direita de uma sequência de texto.

Sintaxe: DIREITA(texto;[número_caracteres])

Argumentos:

• texto: texto do qual se deseja extrair os caracteres à direita.

• número_caracteres: argumento opcional que indica o número de ca-racteres à direita que se deseja extrair do texto. Se omitido, trará o último caractere à direita.

Função EXT.TEXTOObjetivo: retornar os n caracteres a partir de determinada posição em uma sequência de texto.

Sintaxe: EXT.TEXTO(texto;posição;número_caracteres)

Argumentos:

• texto: texto do qual se deseja extrair os caracteres.

• posição: posição do primeiro caractere a ser extraído do texto.



Funções de texto

• número_caracteres: número de caracteres que devem ser extraídos a partir da posição do primeiro caractere considerado.

1. Na coluna Letras, vamos mostrar as letras que compõem a chapa do automóvel. Sabemos que as letras ocupam as três primeiras po-sições do conjunto de caracteres que identicam a chapa. Desse modo, vamos utilizar a função ESQUERDA para extrair os três pri-meiros caracteres à esquerda da identicação da chapa. Clique na célula D5 e digite:

=ESQUERDA(C5;3)

2. Copie a fórmula para todos os automóveis.

3. Na coluna Números, vamos mostrar os números que compõem a chapa do automóvel. Sabemos que os números ocupam as quatro últimas posições do conjunto de caracteres que identicam a chapa. Desse modo, vamos utilizar a função DIREITA para extrair os qua-tro últimos caracteres à direita da identicação da chapa. Clique na célula E5 e digite:

=DIREITA(C5;4)


5. Na coluna Duas Últimas Letras, vamos colocar as duas últimas letras da chapa do automóvel. Para isso, devemos extrair o texto que inicia na posição 2 da identicação da chapa e que contém dois caracteres. Utilizaremos a função EXT.TEXTO. Clique na célula F5 e digite:

=EXT.TEXTO(C5;2;2)


7. Agora devemos preencher a coluna Final da Placa com o último dígito da placa, para determinar se o veículo está ou não em seu dia de rodízio. Clique na célula G5 e digite a fórmula:

=DIREITA(C5;1)




Funções de texto

9. Para preencher a coluna Dia do Rodízio, vamos utilizar as tabelas Rodízio e Dia da Semana que estão em nossa planilha. Vamos tam-bém utilizar algumas funções já vistas. Em primeiro lugar, vamos colocar na planilha a data atual. Clique na célula B2 e digite a fórmula:

=HOJE()

10. Vamos colocar o nome do dia da semana correspondente à data de hoje na célula C2. Para isso, vamos procurar pelo número do dia da semana na tabela Dia da Semana utilizando as funções DIA.DA.SEMANA e PROCV. Clique na célula C2 e digite:

=PROCV(DIA.DA.SEMANA(B2;2);K16:L22;2)

11. Vamos agora preencher a coluna DIA DO RODÍZIO com o dia da semana encontrado na tabela Rodízio a partir do nal da chapa do automóvel. Clique na célula H5 e digite a fórmula:

=PROCV(G5;$K$7:$L$12;2)


13. Agora vamos preencher a coluna Mensagem informando se o veícu-lo está ou não no rodízio na data considerada. Clique na célula I5 e digite a fórmula:

=SE(H5=$C$2;“Veículo no rodízio”;“Livre para circular”)

14. Formate condicionalmente essa coluna para que mostre a mensa-gem “Veículo no rodízio” em vermelho.



Funções de texto



Funções de texto

Resumo do capítulo


Utilizar a função PRI.MAIÚSCULA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função PRI.MAIÚSCULA e informe o texto a ser modicado.

Utilizar a função ARRUMAR

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função ARRUMAR e informe o texto a ser modicado.

Utilizar a função MAIÚSCULA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função MAIÚSCULA e informe o texto a ser modicado.

Utilizar a função MINÚSCULA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função MINÚSCULA e informe o texto a ser modicado.

Utilizar a função ESQUERDA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função ESQUERDA, o texto do qual serão extraídos os caracteres à esquerda e o número de caracteres desejado.

Utilizar a função DIREITA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função DIREITA, o texto do qual serão extraídos os caracteres à direita e o número de caracteres desejado.

Utilizar a função EXT.TEXTO

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função EXT.TEXTO, o texto do qual serão extraídos os caracteres, a posição do primeiro caractere a ser extraído e o número de caracteres que devem ser extraídos.


4Funções de banco de dados

O B J E T I V O Utilizar as funções de banco de dados: BDCONTARA, BDEXTRAIR, BDMÁX, BDMÍN, BDMÉDIA e BDSOMA.




Funções de banco de dados

Funções de banco de dadosO Excel oferece uma coleção de funções para utilização em planilhas com características de banco de dados. Neste capítulo você aprenderá algumas dessas funções.

Atividade 1 – Utilizando as funções BDSOMA, BDMÉDIA, BDMÁX, BDMÍN, BDEXTRAIR e BDCONTARAObjetivo: • Utilizar as funções de banco de dados BDSOMA, BDMÉ-

DIA, BDMÁX, BDMÍN, BDEXTRAIR e BDCONTARA.

Tarefas: • Abrir a pasta Gomes Pereira.xlsx.

• Preencher um relatório por departamento contendo: total e média de salários dos funcionários, funcionários que ga-nham o maior e o menor salário, funcionário mais velho e funcionário mais novo e o total de funcionários.

• Preencher um relatório por cargo contendo: total e mé-dia de salários dos departamentos, maior e menor salário, departamentos que pagam o maior e o menor salário, de-partamentos onde trabalham o funcionário mais velho e o mais novo e o número de funcionários.

A empresa Gomes Pereira Empreendimentos possui quatro departa-mentos: Recursos Humanos, Financeiro, Administrativo e Informática. Esses quatro departamentos têm funcionários que ocupam os cargos de Assistente, Gerente, Analista e Técnico. Em algumas ocasiões, a empre-sa precisa gerar dois relatórios (um por departamento e outro por cargo) com as informações referentes aos salários, ao número de funcionários e à idade dos funcionários. Dessa vez, especicamente, a empresa soli-citou que os dois relatórios contivessem as seguintes informações:

• Relatório por departamento:

Soma e média dos salários, maior e menor salário, funcionários que recebem o maior e o menor salário, quantidade de funcionários, fun-cionário mais velho e funcionário mais novo.

• Relatório por cargo:

Soma e média dos salários, maior e menor salário, departamentos que pagam o maior e o menor salário, quantidade de funcionários, departamento onde trabalha o funcionário mais velho e departamen-to onde trabalha o funcionário mais novo.

Para desenvolver este trabalho, a empresa utiliza a pasta Gomes Pereira.xlsx.




Função BDSOMAObjetivo: somar os valores de uma coluna de um banco de dados de acordo com critérios estabelecidos.

Sintaxe: BDSOMA(banco de dados;campo;critérios)

Argumentos:

• banco de dados: intervalo de células contendo dados dispostos em linhas e colunas. A primeira linha dessa lista deve conter os rótulos (nomes) de cada coluna;

• campo: identicação da coluna que contém os dados que serão so-mados. Pode ser indicada pelo nome do campo entre aspas, o en-dereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;

• critérios: intervalo de células que contém a condição especicada.

1. Abra a pasta Gomes Pereira.xlsx.

A pasta Gomes Pereira é formada por três planilhas:

• Cadastro: contém o banco de dados.

• Relatório: onde serão desenvolvidos os relatórios solicitados.

• Critérios: onde serão colocados os critérios para as diversas situações.




2. Vamos calcular a idade dos funcionários. Na planilha Cadastro, clique na célula F3 e digite a fórmula:

=ANO(HOJE())-ANO(E3)


3. Inicialmente vamos preencher o relatório por departamento. A primeira tarefa é somar os salários pagos por departamento. Para utilizar a função BDSOMA é necessário criarmos os critérios para selecionar os registros desejados. Como estamos desenvolvendo um relatório por departamento, os critérios serão os nomes dos departa-mentos. Selecione a planilha Critérios.

4. Como desejamos efetuar a soma por departamento, os critérios de-vem conter a identicação do nome do campo (Departamento) e o conteúdo que desejamos procurar no campo. Digite os valores mostrados a seguir:

Tanto o nome do campo quanto o conteúdo a ser procurado de-vem estar digitados exatamente como na planilha onde se fará a procura, para que o valor seja encontrado sem erros.

5. Selecione a planilha Relatório e clique na célula B3. Vamos calcular a soma dos salários do departamento Administrativo.

6. Digite:

= BDSOMA(

7. O primeiro argumento refere-se à região da planilha que será con-siderada como banco de dados. Clique na planilha Cadastro e sele-cione a região A2:F104.

=BDSOMA(CADASTRO!A2:F104;




8. O próximo argumento é o nome do campo que contém os valores que serão somados. No caso, é o campo Salário, que está na célula D2 da planilha Cadastro.

=BDSOMA(CADASTRO!A2:F104;CADASTRO!D2;

9. O último parâmetro é o critério (condição) que deve ser considerado para efetuar a soma. Nesse caso, o critério é: Departamento igual a Administrativo. Esse critério está indicado na planilha Critérios, na região A1:A2.

=BDSOMA(CADASTRO!A2:F104;CADASTRO!D2;CRITÉRIOS! A1:A2)

10. Preencha a coluna Soma Salários do relatório por departamento cal-culando a soma para os outros departamentos.

Função BDMÉDIAObjetivo: calcular a média aritmética entre os valores de uma coluna de um banco de dados de acordo com critérios estabelecidos.

Sintaxe: BDMÉDIA(banco de dados;campo;critérios)

Argumentos:

• banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna;

• campo: identicação da coluna que contém os dados para os quais a média será calculada. Pode ser indicada pelo nome do campo entre aspas, pelo endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;


11. Para facilitar a digitação das fórmulas, vamos atribuir nomes às re-giões do banco de dados, coluna que será calculada e critérios. Atri-bua os seguintes nomes:

• Na planilha Cadastro, região A2:F104, atribua o nome Dados.

• Na planilha Cadastro, célula D2, atribua o nome Valsalario.




• Na planilha Critérios, região A1:A2, atribua o nome Admin.

• Na planilha Critérios, região A4:A5, atribua o nome Financ.

• Na planilha Critérios, região A7:A8, atribua o nome Infor.

• Na planilha Critérios, região A10:A11, atribua o nome Rechum.

12. Vamos agora calcular a média de salários para cada departamento. Selecione a planilha Relatório e clique na célula C3. Vamos calcular a média de salários para o departamento Administrativo. Digite a fórmula:

=BDMÉDIA(DADOS;VALSALARIO;ADMIN) Onde: DADOS: identicação do banco de dados. VALSALARIO: célula que contém o título da coluna em que será

calculada a média. ADMIN: critério. 13. Calcule a média de salários para os outros departamentos.

Função BDMÁXObjetivo: retornar o valor máximo dentro de uma lista de valores de acordo com as condições especicadas.

Sintaxe: BDMÁX(banco de dados;campo;critérios)

Argumentos:


• campo: identicação da coluna que contém os dados para os quais o valor máximo será calculado. Pode ser indicada pelo nome do cam-po entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;





Função BDMÍNObjetivo: retornar o valor mínimo dentro de uma lista de valores de acordo com as condições especicadas.

Sintaxe: BDMÍN(banco de dados;campo;critérios)

Argumentos:


• campo: identicação da coluna que contém os dados para os quais o valor mínimo será calculado. Pode ser indicada pelo nome do cam-po entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;


14. Vamos agora calcular o maior e o menor salário em cada departa-mento. Os parâmetros das funções BDMÁX e BDMÍN são os mes-mos das funções BDSOMA e BDMÉDIA. Clique na célula D3 e di-gite a fórmula:

=BDMÁX(DADOS;VALSALARIO;ADMIN)

15. Calcule o maior salário nos outros departamentos.

16. Vamos agora calcular o menor salário em cada departamento. Cli-que na célula F3 e digite a fórmula:

=BDMÍN(DADOS;VALSALARIO;ADMIN)

17. Calcule o menor salário nos outros departamentos.

Função BDEXTRAIRObjetivo: retornar um único valor em uma coluna de uma lista que satisfaça as condições especicadas.

Sintaxe: BDEXTRAIR(banco de dados;campo;critérios)

Argumentos:


• campo: identicação da coluna que contém os dados para a extração do valor desejado. Pode ser indicado pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;





18. Vamos procurar no nosso banco de dados pelos funcionários que recebem os maiores e os menores salários em cada departamento. Para o maior salário, o critério a ser considerado é departamento e maior salário. Vamos modicar a planilha Critérios. Selecione a planilha Critérios e clique na célula B1.

19. Nessa célula vamos digitar o nome do campo que contém o valor do salário. Esse campo é o de nome SALÁRIO. Portanto, na célula B1, digite SALÁRIO.

20. Selecione a célula B2. Nessa célula vamos colocar o valor do maior salário do departamento Administrativo, que está calculado na planilha Relatório na célula D3. Portanto, na célula B2 da pla-nilha Critérios, digite:

= RELATÓRIO!D3

21. Modique os critérios para os outros departamentos.

22. Selecione a planilha Relatório e clique na célula E3.

23. Digite a seguinte fórmula:

=BDEXTRAIR(DADOS;CADASTRO!A2;CRITÉRIOS!A1:B2)

Note que a coluna a ser considerada nessa função é a que contém o nome do funcionário na planilha Cadastro (célula A2) e a região que indica o critério agora contém o campo Valor.

24. Mostre os nomes dos funcionários que recebem o maior salário em todos os departamentos.

25. Utilizando o mesmo raciocínio dos itens 20 e 21, modique a planilha Critérios para considerar os menores salários em cada departamento.




26. Preencha a planilha Relatório procurando pelos funcionários que recebem os menores salários em cada departamento.

A função BDEXTRAIR pode retornar os erros:

#Valor!, indicando que o valor procurado não foi encontrado.

#Num!, indicando que existe mais de um valor que atende ao critério.

Função BDCONTARAObjetivo: contar as células não vazias em uma coluna de valores de acordo com as condições especicadas

Sintaxe: BDCONTARA(banco de dados;campo;critérios)

Argumentos:


• campo: identicação da coluna que contém os dados que devem ser contados. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou pelo número da coluna dentro da lista;


27. Vamos agora contar o número de funcionários de cada departamen-to. Clique na célula H3 e digite a fórmula:

=BDCONTARA(DADOS;CADASTRO!B2;ADMIN)

28. Construa a fórmula para todos os departamentos.

29. Utilizando as funções vistas, preencha a tabela por departamento calculando maior e menor idade e indicando os nomes dos funcio-nários correspondentes.




Veja como cam as planilhas Critérios e Relatório.

30. Preencha a tabela por cargo. Coloque os critérios adequados na pla-nilha Critérios.







Resumo do capítulo


Utilizar a função BDSOMA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDSOMA e informe a região da planilha que será considerada para a soma, a identicação da coluna que contém os dados que serão somados e o intervalo de células que contém os critérios estabelecidos.

Utilizar a função BDMÉDIA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDMÉDIA e informe a região da planilha que será considerada para a média, a identicação da coluna que contém os dados que serão considerados para a média e o intervalo de células que contém os critérios estabelecidos.

Utilizar a função BDMÁX

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDMÁX e informe a região da planilha que será considerada para o cálculo, a identicação da coluna que contém os dados para os quais o valor máximo será calculado e o intervalo de células que contém os critérios estabelecidos.

Utilizar a função BDMÍN

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDMÍN e informe a região da planilha que será considerada para o cálculo, a identicação da coluna que contém os dados para os quais o valor mínimo será calculado e o intervalo de células que contém os critérios estabelecidos.





Utilizar a função BDEXTRAIR

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDEXTRAIR e informe a região da planilha que será considerada para o cálculo, a identicação da coluna que contém os dados para a extração do valor desejado e o intervalo de células que contém os critérios estabelecidos.

Utilizar a função BDCONTARA

Digite o sinal de igual na célula em que pretende obter o valor desejado. Digite o nome da função BDCONTARA e informe a região da planilha que será considerada para o cálculo, a identicação da coluna que contém os dados que serão contados e o intervalo de células que contém os critérios estabelecidos.


5Auditoria de fórmulas

O B J E T I V O Executar auditoria em fórmulas




Auditoria de fórmulas

Auditoria de fórmulasQuando trabalhamos com fórmulas no Excel, em muitas situações nos deparamos com a ocorrência de erros ou resultados inesperados. Ana-lisar e descobrir o fator que ocasionou o erro pode ser um processo demorado e complicado. O Excel 2010 oferece ferramentas bastante úteis para a investigação dessas ocorrências por meio dos métodos de auditoria de fórmulas.

Essas ferramentas incluem rastreamento de precedentes e dependentes de uma célula, rastreamento de erros e exibição e depuração das fór-mulas. Todas essas ferramentas estão presentes no grupo Auditoria de Fórmulas da guia Fórmulas.

Atividade 1 – Rastreando os precedentes de uma célulaObjetivo: • Rastrear as células precedentes de uma célula

Tarefas: • Abrir a pasta Vassouras.xlsx.

• Rastrear as células precedentes das células da planilha Estoque.

A ferramenta Rastrear Precedentes permite identicar as células que afetam o valor da célula selecionada no momento.

Ricardo começou a trabalhar na empresa Serviços de Limpeza Ltda. como estagiário no departamento de informática. Seu chefe solicitou, como forma de aprendizagem, que ele examinasse os cálculos desen-volvidos na pasta Vassouras.xlsx, vericando as fórmulas e as células componentes em cada uma delas. Com a utilização dos recursos de rastreamento de células, Ricardo conseguiu entender de que forma os cálculos foram efetuados.

1. Abra a pasta Vassouras.xlsx.

2. Selecione a planilha Estoque.


4. Clique na ferramenta Rastrear Precedentes do grupo Auditoria de Fórmulas da guia Fórmulas.




5. Podemos vericar uma seta vinda das células C11, D11 e E11. A fórmula armazenada em F11 é:

=C11+D11-E11

Se você clicar duas vezes sobre a seta, a célula precedente cará selecionada.

6. Selecione a célula A11.

A linha pontilhada mostrando uma pequena tabela indica que a cé-lula ou as células precedentes encontram-se em outra planilha.

7. Clique duas vezes sobre a linha pontilhada. A janela mostra a ori-gem das células precedentes, com a indicação da pasta, da planilha e do endereço de célula ou intervalo. Para acessar a origem desejada, basta selecioná-la e clicar no botão OK.

8. Remova as setas selecionando a ferramenta Remover Setas.




Atividade 2 – Rastreando os dependentes de uma célulaObjetivo: • Rastrear as células dependentes de uma célula.

Tarefas: • Rastrear as células dependentes das células da planilha Estoque.

• Rastrear as células dependentes das células da planilha Feriados.

A ferramenta Rastrear Dependentes permite identicar as células que são afetadas pelo valor da célula selecionada. Selecione a planilha Estoque.


10. Clique na ferramenta Rastrear Dependentes do grupo Auditoria de Fórmulas da guia Fórmulas.

11. Podemos vericar uma seta indo para a célula C17.

A seta indica que a fórmula contida na célula C17 depende do valor da célula F16.

12. Selecione a planilha Feriados.

13. Clique na célula A1.

14. Clique na ferramenta Rastrear Dependentes do grupo Auditoria de Fórmulas da guia Fórmulas.

15. Podemos vericar uma seta apontando para outra planilha.

16. Clique duas vezes sobre a linha pontilhada.




A janela mostra que a faixa de células A11:A30 da planilha Estoque depende do valor da célula A1 da planilha Feriados.

17. Remova todas as setas das planilhas e feche a pasta Vassouras.xlsx.

Atividade 3 – Rastreando erros em fórmulasObjetivo: • Rastrear erros em fórmulas

Tarefas: • Abrir a pasta Loja de Carros.xlsx.

• Gerar um erro na fórmula que calcula a colocação do vendedor.

• Rastrear esse erro.

A ferramenta Vericação de Erros permite localizar, rastrear e realizar as etapas de cálculo de uma célula que contenha um erro.

Reginaldo, funcionário da loja de carros Seu Carro É Aqui, ca assusta-do quando, na manhã de segunda-feira, abre a pasta Loja de Carros.xlsx e verica que ela contém erros que não apareciam na sexta-feira quan-do saiu da empresa. Reginaldo não sabe, mas Conceição, que estava estudando Excel no computador dele, após a sua saída da loja, alterou algumas fórmulas da planilha. Para ajudar Reginaldo a vericar o que aconteceu, vamos simular o erro causado por Conceição.

Abra a pasta Loja de Carros.xlsx.

18. Selecione a planilha Vendas.

19. Clique na célula D3 e modique a sua fórmula para:

=ORDEM.EQ(C3;$C$3:$C$22)





21. Observe que as células D17 a G17 apresentam erros.

Clique na célula D17.

22. No grupo Auditoria de Fórmulas, clique no iniciador da caixa de diálogo ao lado da opção Verificação de Erros e selecione Rastrear Erro.

23. Observe as setas mostradas:

As setas indicam que o erro é ocasionado pela procura de um valor vazio na região que mostra as vendas.

24. Retorne a fórmula da célula D3 ao seu valor original:

=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);””)

25. Copie a fórmula para as células de baixo.




Atividade 4 – Avaliando fórmulasObjetivo: • Depurar fórmulas.

Tarefas: • Vericar as etapas de cálculo de uma fórmula da planilha Vendas.

26. A ferramenta Avaliar fórmula permite depurar uma fórmula, ana-lisando individualmente cada uma de suas partes. É bastante útil quando o resultado retornado não é o esperado.

27. Após corrigir o erro da planilha, Reginaldo resolve ensinar Concei-ção a utilizar o Excel de forma adequada. Utilizando a mesma pla-nilha, explica a ela o raciocínio usado com a função SE para atribuir os prêmios aos vendedores que mais se destacaram. Para facilitar, utiliza o recurso Avaliar fórmula.

28. Clique na célula G3 e selecione a ferramenta Avaliar fórmula.

29. Na janela que se abre, podemos efetuar, passo a passo, os cálculos que estão envolvidos na fórmula.

Para mostrar o resultado da expressão que está sublinhada, clique em Avaliar. Cada vez que clicamos nesse botão, a expressão su-blinhada é calculada mostrando o resultado recente. Dessa forma, podemos vericar todas as etapas de cálculo da fórmula.

30. Clique em avaliar.




31. Perceba que, a cada vez que clicamos nesse botão, os cálculos são efetuados até que a expressão chegue ao seu resultado nal.

Se quiser reiniciar a avaliação, clique no botão Reiniciar.

32. Clique em Fechar. Selecione a célula G3 e selecione a ferramenta Avaliar fórmula.

33. Clique no botão Depuração total.

Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula G3 para que seja avaliada.




34. Clique novamente em Depuração total.

Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula F3 para que seja avaliada.

Uma vez que todas as fórmulas envolvidas no cálculo da célula G3 estejam abertas, podemos avaliar cada uma delas individualmente. Basta clicar dentro do quadro correspondente e efetuar a avaliação até o resultado nal.

35. Efetue a avaliação de cada fórmula.

36. Finalize clicando no botão Fechar.

Atividade 5 – Mostrando fórmulasObjetivo: • Mostrar as fórmulas das planilhas.

Tarefa: • Exibir as fórmulas da planilha Vendas.




A ferramenta Mostrar Fórmulas permite a visualização das fórmulas nas células das planilhas em vez de seus resultados.

Conceição se interessa em saber como são as fórmulas contidas na pla-nilha e solicita a Reginaldo que as imprima para que possa entender melhor sua utilização.


2. Clique na ferramenta Mostrar Fórmulas.




Resumo do capítulo


Rastrear os precedentes de uma célula

Clique na ferramenta Rastrear Precedentes do grupo Auditoria de Fórmulas da guia Fórmulas.

Rastrear os dependentes de uma célula

Clique na ferramenta Rastrear Dependentes do grupo Auditoria de Fórmulas da guia Fórmulas.

Remover as setas Clique na ferramenta Remover Setas do grupo Auditoria de Fórmulas da guia Fórmulas.

Rastrear erros em fórmulas

No grupo Auditoria de Fórmulas, clique no iniciador da caixa de diálogo ao lado da opção Vericação de Erros e selecione Rastrear Erro.

Avaliar fórmulas Clique na ferramenta Avaliar Fórmula do grupo Auditoria de Fórmulas da guia Fórmulas.

Mostrar fórmulas Clique na ferramenta Mostrar Fórmulas do grupo Auditoria de Fórmulas da guia Fórmulas.


6Dados externos

O B J E T I V O S Importar arquivos de texto para o Excel

Importar arquivos de dados para o Excel

Importar arquivos de dados do Access como consultas

Importar tabelas da internet




Dados externos

Dados externosEm muitas situações é necessário importar dados de outras fontes ou aplicativos para pastas do Excel. Essas tarefas são facilmente realizadas por meio dos recursos para manuseio de dados externos.

Esses dados podem ser provenientes de arquivos-texto, tabelas do Access, tabelas de internet, do Microsoft Query e outros tipos de arquivo. A seguir serão abordados alguns desses recursos.

Atividade 1 – Importando arquivos-texto para o ExcelObjetivo: • Importar um arquivo.txt para o Excel.

Tarefas: • Abrir o arquivo Vendas.txt.

• Fechar o arquivo Vendas.txt.

• Importar o arquivo Vendas.txt para uma planilha do Excel.

Importando um arquivo-texto Um arquivo tipo texto (extensão .txt) contém informações não formata-das. Muitas vezes a nossa fonte de dados é um arquivo-texto que apre-senta os dados dispostos como se estivessem em uma tabela, onde a primeira linha contém os títulos das colunas e as linhas restantes con-têm os dados separados por um caractere delimitador. Cada linha desse arquivo-texto corresponde a um registro, e os caracteres delimitadores informam onde termina uma coluna e inicia outra.

É importante ressaltar que este arquivo-texto não pode apresentar nenhum recurso de formatação. Devem ser formados por texto puro e não podem ter extensões .doc, .docx, .pdf ou qualquer outra ex-tensão criada por outros editores de texto. Se, porventura, forem criados em algum editor de textos que não o bloco de notas, devem ser gravados no formato texto sem formatação, gerando um arquivo com extensão .txt.

A papelaria Realeza teve um problema em seu computador principal e não pôde efetuar o controle de suas vendas em suas planilhas do Excel como de costume. Durante o período de manutenção do equipamento, Roseli, funcionária da loja e responsável pelas vendas, resolveu anotar o movimento no programa Bloco de Notas, pois era o único disponível na máquina que poderia utilizar naquele momento. Para evitar confu-sões, criou um arquivo e na primeira linha colocou os nomes das co-



Dados externos

lunas da planilha que utiliza no Excel separados por ponto e vírgula e digitou os movimentos efetuados, um em cada linha. As informações também foram separadas por ponto e vírgula.

Quando o computador principal cou pronto para uso, Roseli copiou o arquivo criado para sua pasta de trabalho. Agora ela necessita importar as informações digitadas para sua planilha no Excel. Vamos ajudá-la.

1. Abra o arquivo Vendas.txt.

No arquivo, temos informações sobre a data da compra, o nome do cliente, o item comprado, o valor unitário e a quantidade.

2. Feche o arquivo Compras.txt.

3. Abra uma nova pasta de trabalho em branco no Excel.

4. Na guia Dados, no grupo Obter Dados Externos, clique em De Texto.

5. Localize o arquivo Vendas.txt e clique em Importar.



Dados externos

6. O processo de importação do arquivo será iniciado por meio do assistente de importação de texto.

Na janela, temos as seguintes informações:• Delimitado: informa se as informações estão ou não separadas por

algum delimitador, como vírgula, ponto e vírgula, tabulação. Nes-se caso, o assistente identicou que os dados estão delimitados.

• Largura xa: no caso de os campos não serem delimitados, in-forma se as colunas têm largura xa, ou seja, se existem espaços entre os campos.

• Iniciar importação na linha: permite escolher a partir de que linha a importação será efetuada. O padrão é linha 1.

• Origem do arquivo: permite escolher o sistema operacional e o idioma. É recomendável deixar no padrão que foi colocado.

Clique em Avançar.

7. Será mostrada a seguinte janela:



Dados externos

• Delimitadores: especica qual é o delimitador utilizado para se-parar as informações. No caso, o delimitador utilizado é ponto e vírgula. Desmarque a opção Tabulação e marque a opção Ponto e vírgula.

• Considerar delimitadores consecutivos como um só: essa opção é bastante útil se existirem dois delimitadores em seguida. Se não for selecionada, o fato de existirem dois delimitadores em seguida vai gerar uma coluna em branco na planilha. Se a opção estiver ativada, os dois delimitadores serão considerados um só, evitando as colunas em branco.

Clique em Avançar.

8. A janela permite selecionar cada coluna e denir o formato dos dados.

Caso os formatos não sejam modicados, os dados serão impor-tados no formato Geral, que converte valor numérico em número, valor de data em data e o restante em texto.

Clique em Concluir.

9. Na janela mostrada, selecione a célula A1 da planilha para que seja o local inicial da importação.



Dados externos

10. Clique em OK para terminar.

11. A planilha gerada tem uma conexão com o arquivo-texto original e pode ser atualizada pela modicação do conteúdo desse arquivo; no caso, o arquivo Vendas.txt. Abra o arquivo Vendas.txt no bloco de notas e faça as seguintes modicações:

Nome original Nome modicado

Francisco Francisco de Assis

Diana Diana Lopes

Gilberto Gilberto Ordenval

Maria Lais Maria Lais Nogueira

Fernando Fernando Capote

12. Feche o arquivo Vendas.txt, salvando as alterações.

13. Volte à pasta do Excel. Observe que as alterações efetuadas no arquivo-texto ainda não se reetem na planilha. Na guia Dados, no grupo Conexões, clique em Atualizar tudo.

14. Selecione o arquivo Compras.txt e clique em Importar.

15. Os registros serão atualizados. Salve a pasta como Vendas.xlsx e feche-a.

16. Abra novamente a pasta Vendas.xlsx.



Dados externos

17. Observe o aviso de segurança que aparece acima da barra de fórmulas.

Essa mensagem informa que existem conexões externas com outros arquivos que podem não ser seguras e foram desabilitadas. Quando isso ocorrer, se você souber a origem das conexões e considerá-las seguras, clique em Habilitar Conteúdo. Somente habilitando o con-teúdo o vínculo cará disponível para atualizações.

18. Feche a pasta.

Atividade 2 – Importando arquivos de dados para o ExcelObjetivo: • Importar uma tabela do Access para o Excel.

Tarefa: • Importar a tabela Animais_Dados do banco de dados Dados.accdb para uma planilha do Excel.

Importando um arquivo de dadosO pet shop Cachorros e Outros Bichos gerencia suas atividades por meio de um sistema em Access. A funcionária Eliana necessita dos da-dos de todos os animais clientes do pet shop, mas estes dados devem estar no formato de uma planilha do Excel. O arquivo Dados.accdb do Access possui uma tabela com as informações que Eliana deseja. Nossa tarefa é importar a tabela correspondente para uma pasta do Excel.

Abra uma nova pasta de trabalho.

1. Na guia Dados, no grupo Obter Dados Externos, selecione a opção Do Access.

2. Vamos importar uma tabela do banco de dados Dados.accdb. Sele-cione o arquivo e clique em Abrir.



Dados externos

3. Selecione a tabela Animais_Dados e clique em OK.

4. Selecione as seguintes opções e clique em OK.



Dados externos

5. Veja a planilha resultante.

A planilha resultante cou vinculada à tabela no Access. Qualquer modicação feita na tabela Animais_Dados no Access se reetirá na planilha do Excel. No entanto, as modicações feitas na planilha do Excel não afetarão a tabela do Access.

Para atualizar os dados na planilha, clique em Atualizar no grupo Conexões da guia Dados.

6. Salve a pasta de trabalho com o nome Animais.xlsx e feche-a.

Atividade 3 – Importando base de dados como consulta para o ExcelObjetivo: • Importar uma tabela do Access para o Excel com os dados

selecionados de acordo com critérios estabelecidos.

Tarefa: • Importar a tabela Clientes_Dados do banco de dados Dados.accdb somente para os clientes de São Paulo.

Importando uma tabela como consultaPodemos importar bases de dados para o Excel como consultas, permi-tindo a escolha dos dados e campos que serão importados.

Eliana agora necessita de uma planilha com os dados dos clientes do pet shop, mas somente dos residentes em São Paulo. Mais uma vez, vamos ajudá-la nessa tarefa.

Abra uma nova pasta de trabalho em branco.

1. Na guia Dados, no grupo Obter Dados Externos, clique em De Ou-tras Fontes. No menu apresentado, selecione Do Microsoft Query.



Dados externos

2. Escolha MS Access Database e clique em OK.

3. Escolha o banco de dados Dados.accdb e clique em OK.



Dados externos

4. Selecione a tabela Clientes_Dados e clique no sinal + para abrir os seus campos. Selecione todos os campos, passando-os para o painel Colunas em sua consulta, situado à direita. Clique em Avançar.

5. A solicitação é para importar somente os clientes de São Paulo. Cli-que sobre o campo ClientesCidade e preencha o quadro Só incluir linhas onde, conforme indicado:

6. Clique em Avançar. Vamos classicar a consulta por ClientesNome.



Dados externos

7. Clique em Avançar. Deixe selecionada a opção Retornar Dados ao Microsoft Excel e clique em Concluir.

8. Selecione a célula A1 e clique em OK.

9. Veja o resultado:

10. Salve a pasta com o nome Clientes São Paulo.xlsx e feche-a.

Atividade 4 – Importando dados de uma página da webObjetivo: • Importar uma tabela de uma página da internet.

Tarefas: • Importar de uma página da internet uma tabela contendo as siglas, os nomes, as capitais e as regiões dos estados brasileiros.



Dados externos

A internet é uma excelente fonte de consulta para nossos trabalhos, e as informações obtidas podem ser inseridas em documentos que serão impressos ou visualizados, guardadas em arquivos para posterior utili-zação ou utilizadas imediatamente. Em muitos casos, necessitamos de dados importantes obtidos na internet para que sejam armazenados em planilhas ou bancos de dados. Daniel está fazendo um projeto em Excel e necessita de uma planilha com as siglas, os nomes, as capitais e as regiões de todos os estados brasileiros. Por meio de uma pesquisa na internet, encontrou um site com todos esses dados. Para evitar a digitação da tabela em uma pla-nilha do Excel, resolveu utilizar o recurso de importação dessa tabela diretamente para uma planilha de sua pasta de trabalho. Vamos vericar como Daniel resolveu o problema. O endereço do site pesquisado é: www.astrologie.com.br/brasil.htm.

1. Abra uma nova pasta de trabalho em branco. 2. Clique na ferramenta Da Web do grupo Obter Dados Externos da

guia Dados.

3. Acesse o endereço www.astrologie.com.br/brasil.htm no navegador aberto.



Dados externos

4. Clique na setinha ao lado da lista de códigos dos estados para sele-cioná-la e, a seguir, clique no botão Importar.

5. Selecione a célula A1 e clique em OK.



Dados externos

6. Veja o resultado.

7. Salve a pasta com o nome Estados.xlsx e feche-a.

O endereço encontrado para a pesquisa de Daniel pode não es-tar mais disponível no momento em que você estiver lendo este livro. Como é uma página da internet, pode ter sido modificada, retirada da web ou estar em manutenção.



Dados externos

Resumo do capítulo


Importar um arquivo-texto

Clique na ferramenta De Texto do grupo Obter Dados Externos da guia Dados.

Importar uma tabela do Access

Clique na ferramenta Do Access do grupo Obter Dados Externos da guia Dados.

Importar uma tabela como consulta

Clique na ferramenta De Outras Fontes do grupo Obter Dados Externos da guia Dados. No menu apresentado, selecione Do Microsoft Query.

Importar dados de uma página da web

Clique na ferramenta Da Web do grupo Obter Dados Externos da guia Dados.



Anotações

Anotações


7Tabelas dinâmicas

O B J E T I V O S Resumir dados de planilhas utilizando relatórios de tabela dinâmica.

Visualizar dados da tabela dinâmica pela utilização de gráficos dinâmicos.




Tabelas dinâmicas

Tabela dinâmicaO recurso relatório de tabela dinâmica permite resumir os dados de uma planilha possibilitando que sejam analisados e explorados de forma mais eciente. Com a tabela dinâmica, podemos rapidamente visualizar os dados ltrados, agrupados e calculados, o que nos permite analisá--los por meio de diferentes perspectivas.

Atividade 1 – Criação da tabela dinâmicaObjetivo: • Resumir dados de uma planilha utilizando tabela dinâmica.

Tarefas: • Abrir a pasta Controle Bancario.xlsx.

• Construir um relatório de tabela dinâmica ltrado por grupo e mostrando as descrições dos movimentos e seus valores.

Camila utiliza o Excel para controlar seu movimento nanceiro diário. A planilha que Camila utiliza para armazenar os dados dos movimentos efetuados contém os seguintes campos:• Data: data do movimento.• Grupo: as contas são divididas em três grupos – eventual, mensal

e diário.• Descrição: descrição do movimento.• Movimento: entrada ou saída.• Banco: banco onde o movimento se realiza. Camila trabalha com

quatro bancos: Itaú, Bradesco, Banco do Brasil e HSBC.Para melhor visualizar o movimento nanceiro, Camila tem a neces-sidade de agrupar e resumir os dados por data, grupo, movimento e banco. Vamos ajudá-la na tarefa de construir seus relatórios.A pasta que contém a planilha utilizada por Camila é a Controle Ban-cario.xlsx. 1. Abra a pasta Controle Bancario.xlsx. 2. Clique na célula A4 da planilha Contas. É mais fácil inserir a tabela

dinâmica quando pelo menos uma célula de dados da planilha está selecionada.

3. Clique na ferramenta Tabela Dinâmica do grupo Tabelas da guia Inserir.



Tabelas dinâmicas

4. Observe que, pelo fato de ter iniciado o processo com uma célula da planilha selecionada, o intervalo que deve ser considerado já está indicado. Se não estivesse, ou se fosse um intervalo inadequado, neste momento deveria ser selecionado o intervalo desejado.

5. Selecione a opção Nova Planilha, para que a tabela dinâmica seja criada em uma planilha independente. A seguir, clique em OK.

6. Uma nova planilha é aberta com as opções para trabalho com a ta-bela dinâmica. Também são apresentadas duas novas guias: Opções e Design. Essas guias somente estarão disponíveis quando qualquer célula da tabela dinâmica estiver selecionada.

Do lado direito da janela aparece um painel com os seguintes componentes:



Tabelas dinâmicas

• Lista de campos da tabela dinâmica: painel em que são mostra-dos os campos da planilha para que sejam escolhidos para fazer parte do relatório da tabela dinâmica.

• Filtro de Relatório: campos que serão os ltros principais do relatório.

• Rótulos de Coluna: campos que serão os rótulos das colunas quando for necessário o cruzamento de informações.

• Rótulos de Linhas: campos por meio dos quais os itens serão agrupados, cando dispostos em linhas.

• Valores: campos que terão seus dados somados, contados ou resumidos.

7. Vamos criar um relatório que permitirá o ltro por grupo e terá os movimentos indicados pelas descrições e pelos valores. Clique no campo Grupo e arraste-o até o painel Filtro de Relatório.

8. Clique no campo Descrição e arraste-o até o painel Rótulos de Linha.

9. Clique no campo Valor e arraste-o até o campo Valores.



Tabelas dinâmicas

10. Na tabela criada observe que podemos ltrar os grupos. Para isso, basta clicar sobre a seta ao lado do campo e selecionar a opção desejada.

11. Também podemos selecionar as descrições. Clique na seta ao lado da célula Rótulos de Linha e selecione a(s) descrição(ões) desejada(s).

12. Para formatar o valor como unidade monetária, selecione a célula B3 e clique na ferramenta Congurações do Campo do grupo Campo Ativo da guia Opções.



Tabelas dinâmicas

13. Na janela apresentada, clique no botão Formato do Número.

14. Selecione o formato Moeda, clique em OK e, novamente, em OK.



Tabelas dinâmicas

Atividade 2 – Atualizando dados na tabela dinâmicaObjetivo: • Atualizar os dados da tabela dinâmica a partir da planilha

atualizada.

Tarefas: • Modicar dados existentes na planilha Contas.

• Inserir novas linhas de movimento na planilha Contas.

• Atualizar a tabela dinâmica para que reita as modica-ções efetuadas na planilha Contas.

Ao vericar a planilha Contas, Camila percebeu que algumas altera-ções deveriam ser feitas.

• corrigir a descrição do movimento do dia 20-01-2011 para: Viagem a Salvador – Férias Coletivas;

• inserir um novo movimento com data de 10-02-2011: Eventual, Bolo de aniversário, Saída, R$ 170,00, Itaú;

• inserir um novo movimento no nal da planilha com data de 26-12-2011: Eventual, Direitos autorais, Entrada, R$ 2.100,00, Bradesco.

1. Selecione a planilha Contas.

2. Clique na célula C10 e modique seu conteúdo para: Viagem a Sal-vador – Férias Coletivas.

3. Clique na planilha que contém a tabela dinâmica e observe que a in-formação não foi modicada. Para que a alteração feita na planilha seja reetida na tabela dinâmica, é necessário atualizá-la. Clique na ferramenta Atualizar do grupo Dados da guia Opções.

4. Observe que a informação modicada foi atualizada e agora se en-contra no nal da tabela. Classique os dados de forma correta.

5. Selecione a planilha Dados.

6. Insira uma nova linha antes da linha 13 da planilha.

7. Digite os valores: 10-02-2011 Eventual



Tabelas dinâmicas

Bolo de aniversário Saída 170 Itaú

8. Selecione a planilha com a tabela dinâmica. Observe que a atuali-zação não foi efetuada. Atualize a tabela dinâmica e, se necessário, classique a coluna.

9. Selecione a planilha Dados.

10. Inclua o seguinte registro no nal da planilha: 26-12-2011 Eventual Direitos Autorais Entrada 2100 Bradesco

11. Observe na tabela dinâmica que, da mesma forma que nos casos ante-riores, os dados não foram atualizados. Clique na ferramenta Atualizar.

12. Nesse caso, os dados não foram atualizados, pois inserimos uma nova linha no nal da planilha Dados. Essa linha cou fora da região es-pecicada para a construção da tabela dinâmica. É necessário, então, corrigir a região a ser considerada. Na tabela dinâmica, clique na fer-ramenta Alterar Fonte de Dados do grupo Dados da guia Opções.

13. Corrija o endereço da região considerada para CONTAS!$A$3:$F$60.

Observe que a tabela dinâmica foi atualizada com o novo movimento.



Tabelas dinâmicas

Atividade 3 – Criando níveis na tabela dinâmica e cruzando dadosObjetivos: • Agrupar os dados na tabela dinâmica pela criação de

níveis.

• Colocar campos, como colunas, para cruzar informações.

Tarefas: • Criar uma nova tabela dinâmica que mostre os dados agru-pados pelo campo Grupo.

• Modicar a tabela dinâmica para agrupar os movimentos.

• Mostrar os valores na tabela dinâmica.

• Cruzar os valores com as informações de Entrada e Saída.

Camila agora quer visualizar os dados de sua planilha por Grupo, agru-pados por descrição, e que os valores sejam mostrados de forma cruza-da com o movimento.

Para não perder a tabela criada anteriormente, Camila decide criar uma nova.

1. Clique na célula A4 da planilha Contas e insira uma nova tabela dinâmica.

2. No painel Rótulos de Linha, coloque o campo Grupo e, depois, Descrição. A ordem de colocação desses campos é fundamental, pois indica os níveis de agrupamento das informações.

3. No painel Valores, coloque o campo Valor.

4. No entanto, Camila deseja visualizar as informações separadas por tipo de movimento. Coloque no painel Rótulos de Coluna o campo Movimento.



Tabelas dinâmicas

5. Formate os valores para serem exibidos no formato monetário.

Atividade 4 – Criando novos gruposObjetivo: • Criar grupos de itens.

Tarefas: • Criar uma nova tabela dinâmica.

• Agrupar os movimentos de acordo com uma classicação estipulada.

• Agrupar os valores por período.

Para poder visualizar melhor seu controle, Camila criou uma lista em que os movimentos são classicados conforme a sua natureza.

É necessário separar os itens de acordo com a nova classicação.

Contas obrigatórias

Conta de gás

Conta de luz

TV a cabo



Tabelas dinâmicas

Despesas necessárias

Açougue

Doação

Roupas

Sapatos

Supermercado

Empréstimos

Empréstimo

Empréstimo feito ao Salvador

Pagamento empréstimo

Gastos escritórioMaterial de escritório

Salário Janaína

Lazer gastronômico

Churrasco

Cinema e jantar

Jantar

Restaurante Boa Refeição

Teatro e jantar

Outras despesas

Aluguel de lmes

Bolo de aniversário

Passeio

Presente de casamento

Reforma Encanador

Ladrilhos – reforma

Trabalho

Comissões sobre serviços

Direitos autorais

Livros

Serviços de informática

Viagens

Compras em Buenos Aires

Viagem a Salvador – Férias Coletivas

Viagem a Buenos Aires

Viagem a Campinas



Tabelas dinâmicas

1. Crie uma nova tabela dinâmica. 2. Coloque o campo Descrição em Rótulos de Linha e o campo Valor

em Valores. 3. Formate o campo Valor para unidade monetária. 4. Vamos criar o grupo Contas Obrigatórias, que é formado pelos

itens Conta de gás, Conta de luz e TV a cabo. Clique sobre a célula A11 de forma a selecionar todos os dados da linha.

Não selecione a linha inteira, mas somente os dados das colunas A e B. Para isso, o mouse precisa estar colocado na posição cor-reta, como mostra a figura.

5. Com a tecla CTRL pressionada, selecione os itens Conta de luz e TV a cabo.



Tabelas dinâmicas

6. Na guia Opções do grupo Agrupar, selecione Agrupar Seleção.

7. Mude o nome do grupo de Agrupar1 para Contas Obrigatórias.

8. Crie os outros grupos.

Camila precisa visualizar seus dados resumidos por movimento e tota-lizados por mês. Para isso, deverá criar uma nova tabela dinâmica, que terá o campo Movimento como rótulo de linha e o campo Data como rótulo de coluna. Vamos ajudá-la.

9. Crie uma nova tabela dinâmica. Coloque os campos Movimento em Rótulos de Linha, Data em Rótulos de Coluna e Valor em Valores. Formate os valores para unidade monetária.



Tabelas dinâmicas

Observe que as colunas correspondem às datas.

10. Vamos agrupar os campos para que os dados sejam resumidos por mês. Clique na célula B4 e, depois, na ferramenta Agrupar Campo do grupo Agrupar da guia Opções.

11. A janela apresentada permite a mudança das datas inicial e nal, bem como o agrupamento que desejamos efetuar. Podemos ter vá-rios níveis de agrupamento; por exemplo: por ano, mês e trimestre. Clique em Meses e depois em OK.

12. Observe o resultado:



Tabelas dinâmicas

Atividade 5 – Utilizando segmentação de dadosObjetivo: • Filtrar dados pela ferramenta Segmentação de Dados.

Tarefa: • Filtrar os dados da tabela dinâmica construída anterior-mente por banco.

A ferramenta Segmentação de Dados permite a aplicação de ltros rá-pidos aplicados nos ltros existentes, tornando mais fácil a leitura e a interpretação dos dados, sem que haja a necessidade de inserção de novos menus suspensos.

A última tabela dinâmica construída por Camila auxiliou bastante na visualização dos dados por movimento e por mês. Camila, no entanto, quer aprimorar a análise permitindo que os valores de entrada e saída, além de serem exibidos por mês, possam ser escolhidos por banco.

1. Ainda na tabela dinâmica anterior, clique na ferramenta Inserir Segmentação de Dados do grupo Classicar e Filtrar da guia Opções.

2. Selecione o campo Banco e clique em OK.

3. O quadro mostrado permite ltrar os dados da tabela dinâmica pela escolha do banco.



Tabelas dinâmicas

Botão para limpar o filtro

Clique no botão Bradesco e verique que os dados mostrados refe-rem-se a todos os movimentos do Bradesco.

Podemos ter vários ltros criados pela Segmentação de Dados. As-sim, se for necessário que os dados da tabela dinâmica, além de serem ltrados por banco, também sejam ltrados por Grupo, basta criarmos uma nova segmentação para o campo Grupo.

4. Crie uma segmentação de dados para o campo Grupo.

Atividade 6 – Criando gráficos dinâmicosObjetivo: • Criar gráco dinâmico.

Tarefa: • Criar um gráco dinâmico para a tabela dinâmica desen-volvida na atividade anterior.

Os grácos dinâmicos permitem uma visualização rápida e eciente dos dados de uma tabela dinâmica. Os recursos utilizados são os mes-mos disponíveis nos grácos normais do Excel. A única diferença é que o gráco dinâmico possui caixas de combinação para aplicação de ltros diretamente sobre ele.



Tabelas dinâmicas

Para ter uma melhor visualização de seus gastos e proventos, Camila deseja mostrar os dados da tabela dinâmica mensal por meio de um gráco. Vamos ajudá-la a criar esse recurso. 1. Clique em qualquer célula da tabela dinâmica criada na atividade

anterior. 2. Selecione a ferramenta Gráco Dinâmico do grupo Ferramentas da

guia Opções.

3. Escolha o primeiro tipo do modelo Coluna e clique em OK.

4. Observe que, a cada mudança na tabela dinâmica por meio da ação de qualquer ltro selecionado, os dados do gráco também se mo-dicam. O gráco também apresenta opções para ltros (veja as caixas de combinação).



Tabelas dinâmicas

O gráfico também pode ser criado por meio da tecla F11.

Uma vez criado, o gráfico pode ser editado utilizando os mes-mos recursos disponíveis em um gráfico qualquer do Excel.

Atividade 7 – Mostrando os dados da tabela dinâmica por página de relatórioObjetivo: • Criar cópias da tabela dinâmica para os itens do Filtro de

Relatório.Tarefas: • Criar uma tabela dinâmica com ltro no campo Banco. • Criar páginas de relatório para a tabela dinâmica com l-

tro no campo Banco.

Camila necessita criar uma tabela dinâmica com ltro no campo Banco, mas quer que os dados possam ser visualizados em planilhas diferentes. 1. Crie uma nova tabela dinâmica colocando o campo Banco como

Filtro de Relatório, o campo Descrição como Rótulos de Linha, Movimento como Rótulos de Coluna e Valor como Valores.

2. Na guia Opções, clique na ferramenta Tabela Dinâmica, Opções, Mostrar Páginas do Filtro de Relatório.

3. Escolha Banco e clique em OK.

Veja que na sua pasta de trabalho foram criadas quatro novas plani-lhas, uma cópia da tabela dinâmica para cada banco.



Tabelas dinâmicas

Resumo do capítulo


Criar uma tabela dinâmica

Clique na ferramenta Tabela Dinâmica do grupo Tabelas da guia Inserir.

Atualizar dados na tabela dinâmica

Clique na ferramenta Atualizar do grupo Dados da guia Opções.

Alterar fonte de dados Clique na ferramenta Alterar Fonte de Dados do grupo Dados da guia Opções.

Filtrar dados pela segmentação de dados

Clique na ferramenta Inserir Segmentação de Dados do grupo Classicar e Filtrar da guia Opções.

Criar um gráco dinâmico

Clique na ferramenta Gráco Dinâmico do grupo Ferramentas da guia Opções.

Mostrar dados da tabela dinâmica por página de relatório

Clique na ferramenta Tabela Dinâmica da guia Opções. A seguir, selecione Opções, Mostrar Páginas do Filtro de Relatório.


8Classificar e filtrar

O B J E T I V O S Trabalhar com diversos tipos de classificações na planilha.

Habilitar a filtragem das células por meio de critérios complexos.




Classificar e filtrar

Classificando dadosA classicação em uma lista auxilia a análise de dados. Isso ajuda a visualização e permite uma compreensão mais rápida, o que possibili-ta que os dados sejam encontrados mais rapidamente. A classicação permite organizar os dados em ordem alfabética, por ordem de data e por ordem numérica. Mas é importante que a tabela possua rótulos descrevendo cada coluna.

Atividade 1 – Classificando dadosObjetivo: • Aprender a classicar as informações por diferentes tipos

de dados.

Tarefas: • Abrir arquivo de dados

• Classicar as informações por data, texto e número.

• Salvar o arquivo.

1. Abra o arquivo Filtro.xlsx.

Esse exemplo apresenta uma tabela contendo informações de uma loja que comercializa móveis e utensílios domésticos. Observe que o exem-plo traz informações referentes à venda de um produto: a data da venda, o nome do vendedor, o nome do produto, o preço do produto, o setor a que pertence o produto e a lial em que foi feita a venda.

2. Posicione o cursor em qualquer célula na coluna B.

3. Na guia Dados, no grupo Classicar e Filtrar, clique em Classicar da Mais Antiga para a Mais Nova.




Esse comando fará com que a tabela seja classicada da data mais an-tiga para a mais recente.

4. Posicione o cursor em qualquer parte da coluna G.

5. Na guia Dados, no grupo Classicar e Filtrar, clique em Classicar de Z a A.

Pelo fato de os dados da coluna B serem do tipo data e os da coluna G serem do tipo texto, o nome do botão de comando é alterado. Isso também acontecerá quando os dados forem do tipo número.

Sua planilha terá o seguinte aspecto:

6. Posicione o cursor em qualquer parte da coluna E. Na guia Dados, no grupo Classicar e Filtra, clique em Classicar do Menor para o Maior.




Sua planilha terá a classicação do menor para o maior preço.

7. Posicione o cursor em qualquer parte da coluna B. Na guia Dados, no grupo Classicar e Filtrar, clique em Classicar da Mais Antiga para a Mais Nova.

Sua planilha terá a classicação por data da mais antiga para a mais recente.

8. Salve o seu arquivo.

Atividade 2 – Classificando por mais critériosObjetivo: • Classicar dados por mais de um critério.

Tarefas: • Classicar as informações por matriz e vendedor.


Dentro de um mesmo exemplo você pode necessitar que a classicação seja feita por mais de uma coluna. Quando os dados forem reagrupados na segunda coluna, porém, será preciso classicá-los também.

1. Posicione o cursor em qualquer parte da tabela, na guia Dados, no grupo Classicar e Filtrar, e clique em Classicar.

Esse comando permitirá que a classicação aconteça com base em vários critérios ao mesmo tempo.

Será apresentada a caixa de diálogo Classicar.




2. Em Coluna, na opção Classicar por, selecione a opção Matriz.

3. Clique em Adicionar Nível.

Uma nova linha de critério será adicionada, permitindo uma nova classicação.

4. Em Coluna, na opção E depois por, selecione Vendedor e clique no botão OK.

Pronto. Sua planilha estará classicada por Matriz e em seguida por Vendedor.





Atividade 3 – Criando uma lista personalizadaObjetivo: • Criar uma nova lista de classicação.

Tarefas: • Excluir nível.

• Criar e aplicar uma nova lista de classicação.


Suponha que você necessite classicar seu exemplo ordenando as in-formações dentro da coluna Setor, mas queira indicar que a ordem deve ser esta: sala de estar, dormitórios, colchões, cozinhas e espaço infantil. Utilizando os critérios de classicação vistos até aqui não seria possí-vel. Você poderá, entretanto, inserir uma nova lista de classicação; para tanto, basta seguir estes procedimentos:

1. Posicione o cursor em qualquer parte da tabela. Na guia Dados, no grupo Classicar e Filtrar, clique em Classicar.

Os critérios de classicação anteriormente utilizados serão apresen-tados, ou seja, a classicação será por Matriz e Vendedor.

2. Clique em Excluir Nível.

3. Em Coluna, na opção Classicar por, selecione Setor.

4. Em Coluna, na opção Ordem, selecione Lista Personalizada....

Será exibida a caixa de diálogo Listas Personalizadas.

5. Selecione a opção Nova Lista e clique em Adicionar.




6. Digite as opções pressionando Enter para separar as entradas em cada linha.

O texto das entradas deverá ser digitado exatamente como está em sua tabela.

7. Clique em OK duas vezes.


Além das listas personalizadas, o Excel fornece listas personaliza-das internas de dia da semana e mês do ano.

Atividade 4 – Classificando por fonteObjetivo: • Criar uma lista personalizada por cor da fonte.

Tarefas: • Abrir arquivo.

• Classicar por fonte.


Nesse exemplo você utilizará um novo arquivo, em que as principais vendas estão destacadas em vermelho. O objetivo será classicar pri-meiro por esses itens e posteriormente pelos demais valores.




1. Abra o arquivo Vendas e Vendedores.xlsx.

As principais vendas de cada vendedor estão em vermelho.

2. Posicione o cursor em qualquer parte da tabela, na guia Dados, no grupo Classicar e Filtrar, e clique em Classicar.

3. Em Coluna, na opção Classicar por, selecione Preço.

4. Em Coluna, na opção Classicar em, selecione Cor da Fonte.

Ao escolher Cor da Fonte, a opção Ordem apresentará duas caixas de seleção.

5. Em Coluna, na opção Ordem, selecione a cor vermelha.




6. Clique em Adicionar Nível.

7. Insira um novo critério classicando por Preço, sendo a ordem do Maior para o Menor, e clique em OK.

Sua tabela será exibida conforme segue:

Atividade 5 – Filtrando dados em uma tabelaObjetivo: • Filtrar dados em uma tabela.

Tarefas: • Aplicar o comando AutoFiltro.

• Utilizar diferentes critérios nos ltros.

• Inserir dados com ltro aplicado.

• Reaplicar ltro.

• Limpar ltros.

• Salvar arquivo.

A ltragem dos dados é uma maneira rápida de localizar um subconjun-to de dados em uma tabela. Os dados ltrados exibem somente as linhas que atendem aos critérios (condições que você especica para limitar os registros que devem ser incluídos no conjunto de resultados de uma consulta ou de um ltro) especicados e ocultam as linhas que você não deseja exibir. Após a aplicação de um ltro é possível reaplicar um novo ltro, obtendo assim novos resultados. Você pode ltrar por mais de uma coluna. Os ltros são aditivos, o que signica que cada ltro adicional baseia-se no ltro atual e ainda reduz o subconjunto de dados apresentados. Você pode limpar o ltro e exibir novamente todos os dados iniciais.

Existem três tipos de ltros: por uma lista de valores, por um formato ou por critérios. Cada um deles é exclusivo para o tipo de dado de cada tabela de coluna.




1. Com o arquivo Vendas e Vendedores.xlsx aberto, certique-se de que a célula ativa esteja em uma coluna da sua tabela.

Caso utilize um modelo em que os seus dados não estejam no formato de tabela, você deve selecionar um intervalo de célula que contenha seus dados.

2. Na guia Dados, no grupo Classicar e Filtrar, clique em Filtro.

Nas linhas dos cabeçalhos de sua tabela serão exibidos os botões ltro, indicando que a opção AutoFiltro está ativa.

Se a opção AutoFiltro estiver ativa mas nenhum filtro tiver sido aplicado, ao passar o mouse sobre o botão AutoFiltro será exibida uma mensagem com o nome do cabeçalho e a frase “Mostrando Tudo”.

3. No cabeçalho em que estão as informações sobre o vendedor, clique no botão AutoFiltro.

Observe que é possível também aplicar classicações.




As opções rápidas para aplicar um ltro levam a três possíveis cami-nhos: o primeiro é marcar e desmarcar as caixas de seleção para mos-trar ou ocultar os valores na coluna de dados (1), o segundo é a caixa Pesquisar (2), em que é possível inserir texto ou números que serão pesquisados, ou ainda pode-se utilizar critérios avançados (3) para en-contrar valores que atendem a condições especícas.

Inicialmente todos os vendedores são apresentados e selecionados. Caso necessite de um vendedor apenas, você poderá desmarcar todos os outros vendedores que não atendem a sua seleção, mas esse trabalho demandaria tempo em alguns casos. Veja outra maneira de fazer isso.

4. Desmarque a caixa Selecionar Tudo. Todas as caixas também serão desmarcadas.

5. Clique na respectiva caixa do vendedor Abraão Gomes e no botão OK.




Sua planilha exibirá apenas os dados correspondentes a esse vende-dor; os demais dados estarão ocultos. Isso ca claro observando, nesse exemplo em particular, que alguns números de linhas que antes eram sequenciais não estão sendo exibidos.

Um pequeno funil é exibido no botão, indicando que está sendo apli-cado um ltro. Ao passar o mouse, é exibida a mensagem contendo o nome do cabeçalho e o ltro que está sendo aplicado.Mesmo com um ltro aplicado, é possível aplicar um ltro em outro cabeçalho. 6. No botão AutoFiltro da coluna Matriz, ltre os dados que corres-

pondam a Alphaville. Sua planilha deve apresentar uma redução mais acentuada dos itens

exibidos para o mesmo vendedor, conforme a gura.

Limpando filtros 7. Para excluir a ltragem, clique no botão AutoFiltro da coluna relati-

va à Matriz e escolha a opção Limpar Filtro de “Matriz”.

Isso fará com que as linhas ocultas sejam reexibidas.




É possível limpar todos os filtros de uma só vez. Na guia Dados, no grupo Classificar e Filtrar, clique em Limpar.

Mesmo após a aplicação de um ltro é possível inserir novas linhas e informações na tabela.

8. Posicione o cursor na última linha de sua tabela e insira a informa-ção conforme se segue.

Data: 17-01-2012

Vendedor: Paulo Dias

Preço: R$ 800,00

Produto: Cama de Casal Solaris

Setor: Dormitórios

Matriz: Alphaville

Reaplicando filtros Mesmo após várias inserções ou alterações de dados, você poderá rea-plicar o ltro anterior mantendo a exibição de um único vendedor.

9. Na guia Dados, no grupo Classicar e Filtrar, clique em Reaplicar.

Sua tabela apresentará somente os dados relativos ao ltro em vi-gência, ocultando qualquer inclusão que não corresponda ao ltro.

10. Você pode incluir mais um critério em um ltro existente. Na coluna Vendedor, clique no botão AutoFiltro, selecione o vendedor Paulo Dias e clique em OK.

Sua tabela será alterada e apresentará também os dados referentes a esse vendedor, incluindo a última linha inserida.




11. Limpe o ltro da coluna Vendedor.


Atividade 6 – Filtrando dados por meio de critériosObjetivo: • Aprender a utilizar critérios na pesquisa com o uso dos

caracteres * e ?.

Tarefas: • Aplicar ltros na caixa Pesquisar.

• Limpar ltros.


A caixa Pesquisar (2) está disponível no botão AutoFiltro.

Nesse exemplo, você poderá digitar os caracteres que serão ltrados.

As operações de pesquisa dessa caixa não diferenciam se as le-tras digitadas são maiúsculas ou minúsculas.




1. No botão AutoFiltro da coluna Vendedor, na caixa Pesquisar, digite a letra c e clique em OK.

O Excel retorna os valores que contêm a letra c em qualquer posição.

2. Limpe os ltros.

3. No botão AutoFiltro da coluna Produtos, na caixa Pesquisar, digite c* e clique em OK. Devido ao uso do asterisco (*), o Excel retorna os valores que contêm a letra “c” na primeira posição.

4. Limpe os ltros.

5. No botão AutoFiltro da coluna Produtos, na caixa Pesquisar, digite C?n* e clique em OK.




O Excel retornará todos os itens que na primeira posição iniciam com a letra “c”; o uso do ponto de interrogação (?) faz com que o Excel tra-ga qualquer caractere na segunda posição, mas obrigatoriamente ltra apenas os itens que possuem a letra “n” na terceira posição e qualquer caractere a partir do asterisco (*).

6. Limpe os ltros.

7. No botão AutoFiltro da coluna Vendedor, na caixa Pesquisar, digite *o e clique em OK. O Excel retornará os valores que terminam com a vogal “o”.

8. Limpe os ltros.

9. Salve o arquivo.

Atividade 7 – Filtrando dados com mais de um critérioObjetivo: • Filtrar dados com mais de um critério para os diferentes

tipos de campo.

Tarefas: • Aplicar ltros de texto.

• Aplicar ltros de número.

• Aplicar ltros de data.

• Limpar ltro.


O uso de mais de um critério deve ser feito por meio do botão AutoFiltro.

Nesse exemplo, você aprenderá a ltrar dados do tipo texto (3) utilizan-do mais de um critério.




1. No botão AutoFiltro da coluna Vendedor, em Filtros de Texto, escolha a opção É Diferente de...

Na caixa de diálogo Personalizar AutoFiltro, você pode combinar mais de um critério de pesquisa.

2. Clique na seta na segunda caixa de combinação e selecione o ven-dedor Paulo Dias.

3. Mantenha selecionada a opção E.

A diferença entre essas duas opções é que quando indicamos a opção E, os dois critérios deverão ser respeitados; porém, quando indica-mos a opção OU, bastará que apenas um critério atenda aos requisitos indicados.

4. Na linha abaixo, digite Abraão*. Essa é outra opção para o caso de você não necessitar informar o nome completo do vendedor ou escolher mais de uma opção dentro dos critérios utilizando os carac-teres coringas (* e ?).

O Excel apresentará a lista excluindo os vendedores Paulo e Abraão.

5. Limpe o seu ltro.

Os dados do tipo numérico seriam outra opção para aplicar critérios aos ltros.




6. No botão AutoFiltro da coluna Preço, em Filtros de Número, esco-lha a opção É Maior ou Igual a...

7. Na caixa de diálogo Personalizar AutoFiltro, com a opção é maior ou igual a... selecionada, digite 500 e clique em OK.

Sua tabela ltrará todos os dados com valor igual ou superior a R$ 500,00.

8. Limpe o ltro.

Outra maneira de trabalhar um ltro consiste em trabalhar um intervalo.




9. No botão AutoFiltro da coluna Preço, em Filtros de Número, esco-lha a opção Está Entre...

10. Na caixa de diálogo Personalizar AutoFiltro, digite 500 para a primeira linha.

11. Mantenha selecionada a opção E.

12. Na segunda linha, digite 800 e clique em OK.

Os dados exibidos ltrarão somente os resultados que atendam ao crité-rio dentro do intervalo especicado.

13. Limpe o ltro.

Você pode ltrar os primeiros ou os últimos itens de uma tabela por valor ou pelos percentuais que representam na lista. Para isso, siga os passos:

14. No botão AutoFiltro da coluna Preço, em Filtros de Número, esco-lha a opção 10 primeiros....




15. Na caixa de diálogo AutoFiltro – 10 primeiros, em Mostrar, escolha Primeiros, na primeira caixa de combinação.

16. Digite 5 na segunda caixa de combinação e mantenha a opção Itens, na terceira caixa de combinação.

17. Clique em OK.

O Excel apresentará na lista os cinco principais valores, porém mostra-rá sete linhas, pois a sexta e a sétima linhas possuem o mesmo valor da quinta linha.

18. Limpe o ltro.

Caso necessite aplicar um ltro levando em conta um intervalo es-pecíco de datas, siga a sequência abaixo:

19. No botão AutoFiltro da coluna Data, em Filtros de Data, escolha a opção Está Entre....




A caixa de diálogo Personalizar AutoFiltro é exibida apresentando itens semelhantes aos que você viu até aqui. Contudo, existem botões que possibilitam abrir um pequeno calendário, facilitando assim a esco-lha das respectivas datas.

Nesse exemplo, você escolherá os itens que correspondem às vendas da terceira semana de janeiro de 2012.

20. Digite ou escolha no calendário a primeira data do intervalo que corresponde a 16/01/2012.

21. Digite ou escolha no calendário a segunda data do intervalo que corresponde a 20/01/2012 e clique em OK.

O Excel trará a lista que corresponde aos itens vendidos na respectiva semana.

22. Limpe o ltro e salve o seu arquivo.

Atividade 8 – Filtrando com critérios avançadosObjetivo: • Aprender a utilizar critérios na pesquisa com o uso dos

caracteres * e ?.

Tarefas: • Aplicar ltros na caixa Pesquisar.

• Limpar ltros.


Os critérios avançados funcionam de uma maneira diferente do que foi visto com o uso do AutoFiltro.

A ltragem de dados é baseada na digitação de dados de sua planilha, que, de uma forma lógica, são indicados como critérios para essa ltragem.

Você utilizará um exemplo em que temos as vendas, os produtos e os setores do mês de janeiro de uma loja de móveis.




1. Abra o arquivo Filtro Avançado.xlsx.

A tabela traz a lista de produtos, o total das vendas de janeiro dos res-pectivos produtos e o setor a que cada produto pertence.

Duas planilhas estão disponíveis no exemplo:

A primeira – Vendas Geral Janeiro – exibe a tabela com os dados.

Na segunda – Filtros Avançados – estarão os critérios de pesquisa. Para facilitar o entendimento, parte dela já está preenchida.

Para facilitar o seu trabalho, você deve renomear o intervalo que corres-ponde à lista de dados.

2. Selecione toda a planilha

3. Na caixa de nome, digite Intervalo.




4. Vá para a planilha Filtros Avançados.

Existem vários itens que serão utilizados nesse exemplo.

Para inserir os critérios, é importante que haja um rótulo corres-pondente ao da planilha de dados.

Filtrando com um critério e utilizando caracteres coringasO primeiro exemplo indica que deverão ser ltrados os itens de produ-tos que iniciem com a letra E. Siga os seguintes passos:

5. Na célula A3, digite =“=E*” e pressione Enter. O Excel retor-nará =E*.

6. Crie uma nova planilha, com o nome de Filtro 1.

7. Na guia Dados, no grupo Classicar e Filtrar, clique em Avançado.




É exibida a caixa de diálogo Filtro avançado.

Na opção Ação, você pode ltrar os dados na planilha em que está a sua tabela ou indicar um novo local na própria planilha ou em outra, como você está fazendo.

A opção Intervalo da lista refere-se à área que corresponde aos dados principais de sua planilha. Você nomeou como Intervalo.

A opção Intervalo de critérios indica onde estão os critérios para ltra-gem digitados. No seu exemplo, você está inserindo esses critérios na planilha Filtros Avançados.

A opção Copiar para indica onde serão apresentados os itens que cor-respondem à ltragem. Eles podem ser exibidos na mesma planilha onde estão os dados ou em outra planilha. Como exemplo, você vai criar uma planilha para cada ltro.

8. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.

9. Em Intervalo da lista digite Intervalo (esse nome corresponde ao endereço A1:C30 da planilha Vendas Geral Janeiro).

10. Em Intervalo de critérios, selecione o intervalo A2:A3 da planilha Filtros Avançados.

11. Em Copiar para, selecione o intervalo A1 da planilha Filtro 1.

12. Clique em OK.




O Excel ltrará na planilha uma lista que corresponde aos produtos que iniciam pela letra “e”.

Filtrando com critério para um registro específicoSeguindo o raciocínio, você pode ltrar um registro especíco.


14. Na célula A7, digite =“=Colchão Garibaldi Mola Pillow Unifase” e pressione Enter.

15. Crie uma nova planilha com o nome FILTRO 2.



18. Em Intervalo da lista digite Intervalo.

19. Em Intervalo de critérios, selecione o intervalo A6:A7 da planilha Filtros Avançados.


21. Clique em OK.

O Excel exibirá a ltragem com o respectivo registro.

Filtrando com mais de um critério em uma colunaVocê pode ltrar mais de um produto. Nesse caso, a ltragem dos dados deve indicar uma condição onde mais de um critério possa atender à ltragem. Como se trata de um mesmo campo, um ou outro pode ser verdadeiro. O Excel interpreta essa ltragem como lógica booleana.




Por exemplo, Produto = “A” OU Produto = “B”, bastando apenas um critério ser verdadeiro para atender à expressão.

Siga os passos:


23. Na célula A11, digite =“=Colchão Garibaldi Mola Pillow Unifase” e pressione Enter.

24. Na célula A12, digite =“=Colchão Donnatelo Mola Donnel” e pressione Enter.

25. Crie uma nova planilha com o nome FILTRO 3.



28. Em Intervalo da lista, digite Intervalo.

29. Em Intervalo de critérios, selecione o intervalo A10:A12 da plani-lha Filtros Avançados.


31. Clique em OK.


Filtrando com critério numéricoPara digitar os critérios nos números não é necessário o sinal de igual (=). Nesse exemplo, você deve ltrar os registros que possuem o valor de venda maior que R$ 1000,00.

Siga os passos:


2. Na célula A16, digite >1000 e pressione Enter.

3. Crie uma nova planilha com o nome Filtro 4.



6. Em Intervalo da lista digite Intervalo.






9. Clique em OK.


Filtrando com vários conjuntos de critério para a mesma colunaQuando você ltrar um conjunto de registros em uma mesma colu-na, a lógica booleana interpretará como verdadeiros os dois critérios. Por exemplo, Venda > 5 e Venda < 2. Nesse exemplo, você utilizará o ltro para trazer os registros em que o valor de venda está entre R$ 500,00 e R$ 800,00.

Siga os passos:


2. Na célula A20, digite >=500 e pressione Enter.

3. Na célula B20, digite <=800 e pressione Enter.





8. Em Intervalo de critérios, selecione o intervalo A19:B20 da plani-lha Filtros Avançados.


10. Clique em OK.




O Excel exibe a ltragem com o respectivo registro.

Filtrando um conjunto de critérios com diferentes colunas Você pode utilizar diferentes colunas, e nesse exemplo você deve ltrar os registros que tenham o valor de venda menor que R$ 500,00 e per-tençam ao setor de dormitórios.

Siga os passos:


2. Na célula A25, digite <500 e pressione Enter.

3. Na célula B20, digite =“=Dormitórios” e pressione Enter.





8. Em Intervalo de critérios, selecione o intervalo A24:B25 da plani-lha Filtros Avançados.


10. Clique em OK.


Filtrando um conjunto de critérios utilizando uma fórmulaAo trabalhar com fórmulas e funções, você deve ter o cuidado de mencionar as planilhas de onde obtém seus dados. Neste exemplo,




você vai exibir os valores de venda que são maiores que a média de todos os preços.

Siga os passos:


2. Na célula A25, digite, =’Vendas Geral Janeiro’!B2>MÉDIA(‘Vendas Geral Janeiro’!$B$2:$B$30) e pressione Enter.







9. Clique em OK.





Resumo do capítulo


Classicar dados

Posicione o cursor em qualquer célula na coluna que deseja classicar.

Na guia Dados, no grupo Classicar e Filtrar, clique em Classicar da A a Z ou clique em Classicar de Z a A.

Classicar por mais de um critério

Posicione o cursor em qualquer parte da tabela, na guia Dados, no grupo Classicar e Filtrar, e clique em Classicar.

Criar lista personalizada


Em Coluna, na opção Classicar por, selecione o nome do campo.

Em Coluna, na opção Ordem, selecione Lista Personalizada...

Selecione a opção Nova Lista e clique em Adicionar.

Classicar por cor de fonte


Em Coluna, na opção Classicar por, selecione o campo desejado.

Em Coluna, na opção Classicar em, selecione Cor da Fonte.

Em Coluna, na opção Ordem, selecione a cor desejada e clique em OK.

Aplicar ltro em uma tabela

Na guia Dados, no grupo Classicar e Filtrar, clique em Filtrar.

No cabeçalho em que estão as informações sobre o nome do cabeçalho da coluna, clique no botão AutoFiltro.





Filtrar dados por meio de um critério

No botão AutoFiltro da coluna desejada, na caixa Pesquisar, digite a letra c e clique em OK.

ou

No botão AutoFiltro da coluna desejada, na caixa Pesquisar, digite C?n* e clique em OK.

Filtrar dados com mais de um critério

No botão AutoFiltro da coluna desejada, em Filtros de Texto escolha a opção É Diferente de...

Clique na seta na segunda caixa de combinação e selecione uma opção que será o primeiro critério.

Mantenha selecionada a opção E.

Na linha abaixo, digite ou selecione uma opção que será o segundo critério.

Filtrar dados com critérios avançados

Na guia Dados, no grupo Classicar e Filtrar, clique em Avançado.

Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.

Em Intervalo da lista, digite Intervalo.

Em Intervalo de critérios, selecione o intervalo.

Em Copiar para, selecione o intervalo.


9Hipóteses

O B J E T I V O S Solucionar problemas por meio do comando Atingir Meta.

Testar hipóteses por meio de Cenários.

Determinar valores utilizando o método Solver.




Hipóteses

Aplicando hipótesesO teste de hipóteses é o processo de alteração dos valores nas célu-las. Na maioria dos casos, essas alterações afetam resultados que estão amarrados por meio de fórmulas. Esses testes são interessantes para ver como os resultados na planilha serão afetados. Neste capítulo, você aprenderá três ferramentas para efetuar testes: Atingir Meta, Cenários e Solver.

Atividade 1 – Atingindo metasObjetivo: • Testar hipóteses por meio do comando Atingir Meta.

Tarefas: • Abrir arquivo existente.

• Utilizar o comando Atingir Meta.

• Salvar e fechar arquivo.

Atingir Meta permite que, na hipótese de um possível resultado, sejam determinados valores de entrada que gerem esse resultado.

Suponha que você queira trocar o seu automóvel e necessite nanciar parte desse valor. Você sabe o valor de que precisa, porém necessita de um valor de parcela que caiba no seu bolso por um período de parcelamento.

1. Abra o arquivo Atingir Metas.xlsx.

O exemplo possui como campos uma estrutura fácil em que você en-contrará o seguinte exemplo:

• Na célula B5 temos o percentual anual de juros.

• Na célula C5 temos o número de parcelas.

• Na célula D5 temos o valor do empréstimo.

• Na célula D7 temos o valor a ser pago que é obtido por meio da função PGTO vista no capítulo 1.

A fórmula é composta dos argumentos =PGTO(B5/12;C5;D5), em que:

• a taxa é o percentual mensal calculado pela divisão do valor de B5 pela quantidade de meses no ano;



Hipóteses

• o período é o valor de C5, que representa a quantidade de parcelas;• o valor presente que está em D5 é o valor do empréstimo.

Observe que, no exemplo, para obter um empréstimo de R$ 15.000,00 em um ano, o valor da prestação está muito alto. Como obter uma nova hipótese pensando na redução do valor a ser pago mensalmente para R$ 800,00? Para isso, você utilizará o comando Atingir Meta.

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Atingir meta.

Será exibida a caixa de diálogo Atingir meta.

3. Na caixa Denir célula, insira a referência para a célula que contém aquela em que você deseja testar a sua nova condição ou hipótese. Digite D7.

4. Na caixa Para valor, digite o valor desejado: -800. Observe que o número é negativo porque representa um pagamento.

5. Na caixa Alternando célula, insira a referência para a célula que contém o valor que pode ser modicado. Nesse exemplo, será ajus-tado o número de parcelas. Digite C5.

6. Clique no botão OK.

Será apresentada a caixa de diálogo Status do comando atingir meta.




Hipóteses

O comando Atingir meta é executado e gera um resultado com um nú-mero de um pouco mais de 21 parcelas, como mostra a gura a seguir.

Você pode testar uma hipótese da mesma parcela de R$ 800,00, porém em apenas um ano, mas com um valor de empréstimo menor.

8. Digite 12 na célula C5. A planilha será recalculada.

9. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Atingir meta.

10. Na caixa Denir célula, digite D7.

11. Na caixa Para valor, digite -800.

12. Na caixa Alternando célula, digite D5.

13. Clique no botão OK duas vezes.

O comando Atingir meta é executado e gera um resultado do valor de empréstimo menor, porém dentro da hipótese de valor de parcela em um ano.

14. Salve e feche o seu arquivo.

Atividade 2 – Trabalhando com cenáriosObjetivos: • Ter uma projeção de valores por meio da criação de cenários.

Tarefas: • Abrir um arquivo. • Criar cenários • Mesclar cenários • Exibir cenários. • Salvar o arquivo.



Hipóteses

Gerenciador de cenáriosO Gerenciador de cenários é um teste de hipótese em que, por meio da alteração nos valores das células, é possível fazer uma projeção pro-gressiva para explorar, criar e salvar um conjunto de diferentes resulta-dos, permitindo comparar valores e ver como essas alterações afetarão sua planilha.

Você pode agrupar informações de outras fontes, mesclando cenários em uma planilha; por exemplo, uma empresa onde existem projeções de vários departamentos, como orçamentos, folhas de pagamentos, va-lores de produção, etc. Para comparar vários cenários, você pode criar um relatório que os resuma na mesma página.

Você pode criar um número ilimitado de cenários, porém cada cenário pode acomodar até 32 valores de variáveis. Se quiser analisar mais de 32 valores e esses valores representarem apenas uma ou duas variáveis, use tabela de dados.

Criando cenáriosPara criar um cenário, você deve construir um conjunto inicial de valo-res na planilha ou ter um modelo pronto. Nesse caso, você utilizará um exemplo já preparado para esse exercício.

1. Abra o arquivo Cenário.xlsx.

Esse modelo traz uma planilha que estima, por meio de uma fórmula, um custo nal para revenda de um produto (Revenda), levando em conta o preço pago na transação (Preço unitário), o volume comprado



Hipóteses

(Quantidade comprada), o percentual de impostos pago (Impostos), o valor para armazenagem do produto (Custo de armazenamento). Esse valor é obtido por meio de uma fórmula que pesquisa o quadro de ar-mazenamento (B11:D18) e o valor total pago (Custo nal), que é uma fórmula que leva em conta o preço unitário, a quantidade comprada, os impostos e o custo de armazenamento, o custo individual de cada peça (Custo peça) e o percentual desejado a ser adicionado para o valor de revenda (% revenda).

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Gerenciador de Cenários.

3. Na janela Gerenciador de cenários, clique em Adicionar.

Será exibida a caixa de diálogo Editar cenário, em que:

• Nome do cenário: como você pode criar mais de um cenário, procu-re colocar nomes relacionados às hipóteses desejadas.

• Células variáveis: são os endereços que sofrerão alteração conforme a hipótese desejada. Você poderá selecionar uma ou mais células e, pressionando a tecla CTRL, endereços não adjacentes.

• Proteção: selecione Evitar Alterações para impedir a edição do ce-nário quando a planilha estiver protegida e selecione Oculto para impedir a exibição do cenário quando a planilha estiver protegida.



Hipóteses

Para preservar os valores iniciais para as células variáveis, adi-cione um cenário que use esses valores antes de criar cenários adicionais que usem valores diferentes.

4. Na caixa Nome do cenário, digite Compras de Janeiro.

5. Na caixa Células variáveis, insira a célula C3.

6. Clique em OK.

7. Na caixa de diálogo Valores de Cenário, para preservar os valores iniciais, mantenha o valor inicial e clique em OK.

Novamente será exibida a caixa de diálogo Gerenciador de cenários com o cenário criado.

Para criar cenários adicionais, repita as etapas de 3 a 7.



Hipóteses

8. Adicione um novo cenário com o nome Compras de Fevereiro.


10. Na caixa de diálogo Valores de Cenário, altere o valor para 700 e clique em OK. O novo cenário será criado.

11. Adicione um novo cenário com o nome Compras de Março.


13. Na caixa de diálogo Valores de Cenário, altere o valor para 1200 e clique em OK. O novo cenário será criado.

Quando um cenário for exibido, você alterna para o conjunto de valores que são salvos como parte desse cenário. Os valores de cenário são exibidos nas células que são alteradas de cenário para cenário, além das células de resultados.

Exibindo cenários Para exibir os cenários, clique no nome do cenário que deseja mostrar.

14. Clique no cenário Compras de Fevereiro e em Mostrar.



Hipóteses

Observe que a planilha terá os seus valores alterados por causa do novo cenário. Nesse caso, para cobrir um investimento maior e man-ter os mesmos percentuais na revenda, é possível ter um valor de re-venda menor.

Procure exibir o outro cenário.

15. Clique no cenário Compras de Março e em Mostrar.

Nesse caso, sua planilha terá um valor de revenda maior, mantendo o percentual de revenda de 40%. Talvez o vendedor tenha diculdades em negociar esse produto. Uma solução pode ser negociar o valor unitário de compra. Uma vez que a quantidade comprada será bem maior, pode--se conseguir um desconto. Será necessário que no cenário de Compras de Março você tenha essa hipótese.

Editando cenários 16. Clique no cenário de Compras de Março e em Editar.



Hipóteses

17. Na caixa Células variáveis, altere o endereço para C2:C3 e clique em OK.

18. Na caixa de diálogo Valores do cenário, altere o valor da caixa rela-tiva à célula C2 para a fórmula =C2-(C2*10%) e clique em OK.

O Excel exibirá uma mensagem dizendo que os resultados de fórmula serão convertidos em valores.

Você pode inserir valores diretamente em vez de inserir uma fórmula. Usamos uma fórmula para facilitar o cálculo.

19. Clique em OK.

Observe que, mesmo com um desconto de 10% na compra, o resultado do valor de revenda não foi satisfatório. Com isso, é possível vericar por meio de cenários novas possibilidades e situações.

20. Clique em Fechar.



Hipóteses

Depois que você fechar a caixa de diálogo Gerenciador de cenários, os valores do último cenário exibido permanecerão na planilha.

Mesclando cenáriosVocê pode utilizar cenários feitos em outras planilhas, mesclando essas hipóteses em uma única planilha. Primeiro, você deve selecionar a pla-nilha na qual deseja armazenar os resultados desses cenários.

Observe que no arquivo Cenário.xlsx existem mais três planilhas: Matriz, Filial 1 e Filial 2.

21. Selecione a planilha Matriz para que seja a planilha ativa.

22. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Gerenciador de cenários.

23. Clique em Mesclar.

Será exibida a caixa de diálogo Mesclar cenários.

Pasta: ao clicar na seta ao lado de Pasta, serão exibidas as pastas de trabalho que contêm cenários a serem mesclados nos seus resultados.

Planilha: exibe os cenários existentes na pasta escolhida.

24. Na caixa Planilha, clique em Filial 1.

25. Clique em OK.

Será exibida a caixa de diálogo Gerenciador de cenários com os cená-rios mesclados.



Hipóteses

Você agora deve inserir os cenários da planilha Filial 2.

26. Clique em Mesclar.

27. Na caixa de diálogo Mesclar cenários, na caixa Planilha, clique em Filial 2.

28. Clique em OK.

Note que mais cenários farão parte da planilha atual.

Selecionando uma das opções dos cenários disponíveis e clicando em Mostrar serão exibidos os cenários das planilhas mescladas.

29. Clique em Fechar.

Criando um relatório de resumo do cenárioO resumo do cenário gera automaticamente um relatório de dados com os cenários existentes em uma nova pasta, permitindo a exibição das simulações em uma única planilha.

Você utilizará os cenários de compras da planilha Armazenamento, que aprendeu a criar no início deste capítulo.

30. Clique na pasta Armazenamento.

31. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Gerenciador de cenários.

O Excel exibirá a caixa de diálogo Gerenciador de cenários com os cenários criados, que foram as compras de janeiro, fevereiro e março.

32. Clique em Resumir.



Hipóteses

Será exibida a caixa de diálogo Resumo do cenário.

Dois tipos de relatórios podem ser exibidos na planilha, um resumo dos cenários existentes e uma tabela dinâmica contendo esses cenários.

33. Clique em Resumo do cenário.

34. Na caixa Células de resultado, insira as células que fazem referên-cia aos valores que são alterados pelos cenários; nesse exemplo, a quantidade comprada, os custos de armazenamento, nal, da peça e valor de revenda (C3, C5, C6, C7 e C9).

Separe as diversas referências por vírgulas.

35. Clique em OK.

Será criada uma nova pasta com o nome Resumo do cenário.



Hipóteses

Você pode fazer alterações e formatar o conteúdo se desejar.

36. Exclua a coluna D e as linhas 5, 6, 7, 14, 15 e 16.

37. Altere o título das Células de resultado, conforme a gura abaixo:

Relatórios de cenários não são automaticamente recalculados. Se você alterar os valores de um cenário, essas alterações não aparecerão em um relatório de resumo existente, mas serão exibidas se você criar um novo relatório de resumo.

Você não precisa de células de resultados para gerar um rela-tório de resumo de cenários, mas precisará delas para obter um relatório de tabela dinâmica de cenários.

38. Salve o arquivo e feche a planilha.

Atividade 3 – Instalando o SolverObjetivo: • Instalar suplemento no Excel

Tarefas: • Instalar o suplemento Solver.

O Solver é um suplemento do Excel que permite testar hipóteses pro-curando um valor ideal (máximo ou mínimo) para uma fórmula ou uma célula. O Solver trabalha com um grupo de células que participam do cálculo das fórmulas (chamadas variáveis de decisão) ajustando seus valores e produzindo um resultado que satisfaça objetivos e restrições indicados pelo usuário.

Suplementos são recursos do Microsoft Excel que oferecem comandos e recursos adicionais. Para utilizar o Solver, é necessário instalar um suplemento.



Hipóteses

1. Na guia Arquivo, clique em Opções.

2. Na caixa de diálogo Opções do Excel, clique na categoria Suple-mentos.

3. Na caixa Gerenciar, clique em Ir.



Hipóteses

4. Na caixa de diálogo Suplementos disponíveis, marque a caixa de seleção Solver e clique em OK.

5. Clique na guia Dados.

Observe que o grupo Análise foi adicionado. Esse grupo contém o bo-tão com o suplemento Solver.

Atividade 4 – Resolvendo um problema com o SolverObjetivo: • Aprender a utilizar o método Solver.

Tarefas: • Abrir arquivo existente.

• Aplicar o Solver.

• Restaurar congurações originais.

Nesse exemplo, você terá uma lista de compras de produtos já elaborada, em que temos como células variáveis o Preço unitário, a Quantidade, o Preço nal com uma fórmula de multiplicação do Preço unitário, a Quantidade e o somatório dos preços indicado como Total.



Hipóteses

1. Abra o arquivo Solver.xlsx.

O problema em questão é que o usuário necessita comprar os produtos que estão na lista, mas possui apenas R$ 70,00. O custo apontado na célula F18 é de R$ 94,66. Será necessário alterar os valores nas células variáveis (Preço unitário ou Quantidade), apontando uma solução que atenda ao objetivo, em que o valor de despesa não ultrapasse o va-lor do cliente (R$ 70,00). Como não é possível inuenciar nos valores de mercado (Preço unitário), resta apenas a possibilidade de alterar as quantidades a serem compradas. 2. Na guia Dados, no grupo Análises, clique em Solver.Será exibida a caixa de diálogo Parâmetros do Solver.



Hipóteses

Na caixa Denir Objetivo, deve-se selecionar o endereço ou intervalo que faz referência ao objetivo procurado.

3. Em Denir Objetivo, selecione a célula F18.

Na caixa Para você pode indicar três opções:

• Máx: para que o valor da célula de objetivo seja o maior possível.

• Mín: para que o valor da célula de objetivo seja o menor possível.

• Valor de: para que o valor da célula de objetivo seja especicado.

4. Na caixa Para, selecione a opção Valor de e digite 70.

Na caixa Alterando Células Variáveis, é preciso indicar a referência em que estão localizadas as células variáveis de decisão.

5. Na caixa Alterando Células Variáveis, indique o intervalo E3:E17.

6. Clique em Resolver.

A planilha será alterada com novos valores das quantidades dos res-pectivos produtos e será apresentada a caixa de diálogo Resultados do Solver, indicando que o resultado foi encontrado e que todas as condições foram satisfeitas.

Para aceitar a sugestão indicada, basta clicar em OK e formatar a plani-lha, mas nesse exemplo você vai ignorar a sugestão indicada.

7. Selecione a opção Restaurar Valores Originais e clique em OK.



Hipóteses

Atividade 5 – Utilizando o Solver com restriçõesObjetivo: • Resolver problemas com Solver restringindo valores.

Tarefas: • Aplicar o Solver restringindo linhas.


Dentro da lista de compras já criada, o problema persiste: o usuário necessita fazer a compra de produtos que estão na lista, mas possui apenas R$ 70,00. Na atividade anterior, você aprendeu como resolver esse problema, porém o usuário necessita que dois itens não sofram alteração: a quantidade de laranjas e de uvas. Ao utilizar o Solver, você deverá indicar que as células variáveis possuem esse tipo de restrição.

Com o arquivo Solver.xlsx aberto, na guia Dados, no grupo Análises, clique em Solver.

A caixa Parâmetros do Solver é aberta com os dados anteriormente indicados.

Na caixa Sujeito às Restrições, insira as restrições que você deseja apli-car, procedendo como se segue.

Na caixa de diálogo Parâmetros do Solver, clique em Adicionar.

Será exibida a caixa de diálogo Adicionar Restrição.

Na caixa Referência de Célula, você pode inserir a referência de células cujo valor você deseja restringir.

Na relação, deverão ser escolhidas as opções <=, =, >=, int, bin ou dif , que fazem referência entre a célula e a restrição.

Em Restrição, você deverá digitar um número ou indicar a referência à restrição desejada.



Hipóteses

1. Em Referência de Célula, selecione a célula E10.

2. Escolha a opção igual (=).

3. Em Restrição, digite 2.

Com isso, você indicou a restrição à quantidade de laranjas. Você deve-rá indicar o mesmo para a quantidade de uvas.

4. Clique no botão Adicionar.

5. Selecione a célula E17 como nova referência.

6. Escolha a opção igual (=).

7. Em Restrição, digite 2.

8. Clique em OK.

O Excel apresentará sugestões que atendam ao objetivo e aos critérios indicados.

9. Em Resultados do Solver, deixe a opção Manter Solução do Solver selecionada e clique em OK.

10. Aplique as formatações necessárias para que as quantidades tenham duas casas decimais e salve o seu arquivo.



Hipóteses

Resumo do capítulo


Atingir metas Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Atingir meta.

Na caixa Denir célula, insira a referência para a célula que contém aquela em que você deseja testar a sua nova condição ou hipótese.

Na caixa Para valor, digite o valor desejado.

Na caixa Alternando célula, insira a referência para a célula que contém o valor que pode ser modicado. Clique no botão OK.

Criar cenários Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Gerenciador de cenários.

Na janela Gerenciador de cenários, clique em Adicionar.

Na caixa Nome do cenário, digite o nome do cenário desejado.

Na caixa Células variáveis, insira a célula correspondente e clique em OK.

Clique em Mostrar para exibir o cenário.



Hipóteses


Instalar suplemento Solver

Na guia Arquivo, clique em Opções.

Na caixa de diálogo Opções do Excel, clique na categoria Suplementos. Na caixa Gerenciar, clique em Ir.

Na caixa de diálogo Suplementos disponíveis, marque a caixa de seleção de Solver e clique em OK.

Aplicar o Solver

Na guia Dados, no grupo Análises, clique em Solver.

Na caixa Denir Objetivo, selecione o endereço ou intervalo que faz referência ao objetivo procurado.

Na caixa Para, selecione a opção Valor de e digite o valor desejado.

Na caixa Alterando Células Variáveis, indique a referência em que estão localizadas as células variáveis de decisão.

Clique em Resolver.



Anotações

Anotações


10Ferramentas de dados

O B J E T I V O S Validar e controlar a entrada de dados em uma planilha.

Agrupar e resumir dados em uma única planilha.




Ferramentas de dados

Validação de dadosA validação de dados é utilizada para controlar o tipo de dado a ser in-serido em uma célula. Isso impede que dados inválidos sejam digitados na planilha, além de fornecer instruções, auxiliando o usuário a corrigir esses erros.

Atividade 1 – Adicionando regras de validaçãoObjetivo: • Aprender a restringir a entrada de dados em uma planilha.Tarefas: • Abrir um arquivo existente. • Restringir a entrada de dados.

Aplicando uma regra de validação de dados 1. Abra o arquivo Validação de Dados.xlsx. 2. Clique na planilha Cursos.

3. Selecione o intervalo A8:A18. 4. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação

de Dados.

O comando Validação de Dados não estará disponível na guia Dados enquanto você estiver inserindo dados em uma célula ou se a planilha estiver protegida ou compartilhada.

5. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Lista.




6. Clique na caixa Fonte.

Os valores da lista devem ser separados por ponto e vírgula.

Esse caractere separador de listas é definido pelo Microsoft Windows (Painel de Controle, Região e Idioma, Configurações adicionais, Separador de listas).

7. Digite 94;95;96;196 e clique no botão OK.

Ao posicionar o cursor em qualquer das células indicadas, o Excel exi-be uma seta suspensa ao lado de cada célula, contendo a lista dos itens denidos.

8. Na célula A8, digite 97 e pressione Enter.

Como a restrição a alguns dados não permite o número 97 como um número válido, o Excel apresenta uma mensagem de erro informando a violação da regra.

O botão Repetir permite uma nova digitação na célula, enquanto o bo-tão Cancelar desconsidera a digitação anterior.




Você pode clicar na seta ao lado da célula e escolher um valor válido ou digitar diretamente esse valor.

9. Clique no botão Repetir, digite o valor 94 e pressione Enter.

Atividade 2 – Definindo mensagens de dadosObjetivo: • Congurar mensagens para orientar a entrada de dados em

uma planilha.

Tarefas: • Denir mensagem de orientação de dados.

• Denir mensagens de alerta de dados.

Mensagem de validação de dadosÉ possível congurar mensagens antes da digitação dos dados pelo usuário, que orientem sobre o tipo de dado desejado. Você também pode optar por mostrar um alerta de erro, que será exibido somente depois que os usuários inserirem dados inválidos.

1. Selecione o intervalo A8:A18.

2. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados.

3. Na guia Congurações, desmarque a opção Menu suspenso na célula. Isso fará com que não seja mais exibida a seta suspensa ao lado da célula.

4. Clique na guia Mensagem de entrada.

5. Na caixa Título, digite Códigos da Subárea.

6. Na caixa Mensagem de entrada, digite Utilize apenas os códigos 94, 95, 96 ou 196 na coluna subárea.

7. Clique em OK.




Ao posicionar o cursor sobre qualquer célula dentro da área de valida-ção, será exibida uma mensagem indicando a restrição criada por você. Essa mensagem poderá ser selecionada e arrastada para qualquer parte da planilha, facilitando a visualização e a digitação.



10. Clique na guia Mensagem de entrada e desmarque a opção Mostrar mensagem de entrada ao selecionar célula.

11. Clique na guia Alerta de erro.

12. Na caixa Título, digite Atenção.

13. Na caixa Mensagem de erro, digite Esta célula aceita os valores 94, 95, 96 e 196. O texto da mensagem pode ter até 225 caracteres.




14. Na caixa de combinação Estilo, escolha a opção Parar.

É possível escolher três tipos de alertas de erro, conforme apresentado a seguir.

Ícone Tipo Use para

Parar

Impede que os usuários insiram dados inválidos em uma célula. Uma mensagem de alerta Parar tem duas opções: Repetir ou Cancelar.

Aviso

Avisa aos usuários que os dados inseridos são inválidos, mas não os impede de inseri-los. Quando uma mensagem de alerta de aviso é exibida, os usuários podem clicar em Sim, para aceitar a entrada inválida, em Não, para editá-la, ou em Cancelar, para removê-la.

Informações

Informa aos usuários que os dados inseridos são inválidos, sem impedir sua inserção. Esse tipo de alerta de erro é o mais exível. Quando uma mensagem de alerta de Informações é exibida, os usuários podem clicar em OK, para aceitar o valor inválido, ou em Cancelar, para rejeitá-lo.

Se você não inserir um título ou um texto, o Excel utilizará um texto padrão.





16. Na célula A8, digite 99 e pressione Enter. Será exibida a mensagem de alerta.

17. Clique no botão Repetir e corrija o valor digitando 94.

18. Clique na célula B8 e digite EXCEL AVANÇADO.

19. Clique na célula C8 e digite APLICATIVOS.

Atividade 3 – Criando regras de validação em lista de outra planilhaObjetivo: • Criar regra de validação de dados fazendo referência a

uma lista em outra planilha.

Tarefas: • Criar regra de validação fazendo referência a outra planilha.

• Limpar regras de validação.

• Criar referência ao nome de um intervalo.

• Salvar arquivo.

Referência a um intervalo de outra planilhaVocê também pode criar as entradas da lista fazendo referência a um intervalo de células em outro local da pasta de trabalho.

1. Selecione o intervalo D8:D18.


3. Na guia Congurações, na caixa Permitir, escolha a opção Lista.

4. Na caixa Fonte, clique no botão Recolher.




A caixa de diálogo será minimizada.

5. Clique na planilha Modalidade.


7. Clique no botão Expandir.

8. Clique no botão OK. Os valores sofrerão restrição para a seleção indicada.

9. Para fazer um teste, na célula D8 digite À DISTÂNCIA. Pressione Enter. O Excel emitirá uma mensagem de erro.

10. Clique em Repetir e digite EAD. Pressione Enter.

Limpando as regras de validaçãoPara limpar as regras de validação de uma célula ou de uma seleção, siga estes passos:



13. Clique no botão Limpar Tudo e no botão OK.




Referência a nome de um intervalo de outra planilha 14. Clique na planilha Modalidade.

15. Selecione a coluna A.

16. Atribua o nome Modalidades para essa área.

17. Clique na planilha Cursos.



20. Na guia Congurações, na caixa Permitir, escolha a opção Lista.

21. Desmarque a opção Ignorar em branco.

Se os valores permitidos forem baseados em um intervalo de células que possui um nome definido e houver uma célula em branco em qualquer parte desse intervalo, marcar a caixa de se-leção Ignorar em branco permitirá que qualquer valor seja inse-rido na célula validada.

22. Na caixa Fonte, digite =Modalidades.


24. Na célula D8, digite PARTICULAR. O Excel apresentará uma men-sagem de erro.

25. Clique em Cancelar.

26. Clique na planilha Modalidade.

27. Na célula A11, digite PARTICULAR.

28. Clique na planilha Cursos.

29. Na célula D8, digite PARTICULAR. Note que não haverá mensagem de erro e esse dado passou a ser inserido na regra de validação.

30. Salve seu arquivo.




Atividade 4 – Aplicando validação a dados já inseridosObjetivo: • Criar regra de validação de dados para corrigir entradas

inválidas.

Tarefas: • Criar regra de validação no formato hora.

• Denir regras utilizando fórmulas.

• Circular dados inválidos.

• Limpar círculos de validação.

• Testar regras de validação.

• Salvar arquivo.

É possível aplicar a validação de dados a células que já possuem dados inseridos. O Excel pode realçar com círculos os dados inválidos por meio de instruções fornecidas pelo usuário, e após a identicação é possível ocultar os círculos e corrigir as entradas inválidas.

Restringindo entrada de dados com formato hora 1. Clique na planilha Horário.

A planilha que será utilizada como exemplo traz horários de um funcio-nário durante a primeira quinzena do mês de julho.

As regras desse funcionário compreendem:

• um intervalo mínimo de 1 h de almoço;

• 8 h de trabalho, considerando a entrada e o intervalo de almoço.




Para isso, siga estes passos:



4. Na guia Congurações, na caixa Permitir, escolha a opção Hora.

5. Na caixa Dados, escolha a opção é maior ou igual a.

6. Na caixa Hora inicial, digite =C2+“1:00”.


8. Selecione o intervalo E2:E16.


10. Na guia Congurações, na caixa Permitir, escolha a opção Hora.

11. Na caixa Dados, escolha a opção é maior ou igual a.

12. Na caixa Hora inicial, digite =D2+”08:00”+(D2-C2).


Como os dados já foram digitados, as regras não funcionarão de ime-diato; será necessário aplicar uma validação para novas regras.




Circulando dados inválidos 14. Na guia Dados, no grupo Ferramentas de Dados, clique em Circular

Dados Inválidos.

O Excel realçará com um círculo todas as células que contêm dados inválidos.

Limpando círculos de validaçãoAntes de corrigir os dados inválidos, procure limpar os círculos da pla-nilha.

15. Na guia Dados, no grupo Ferramentas de Dados, clique em Limpar Círculos de Validação.

Testando a regra de validação 16. Clique na célula D2, digite 12:59 e pressione Enter. Será exibida a

mensagem de alerta.

17. Digite o valor correto. Clique no botão Repetir.

18. Na célula D2, digite 13:00 e pressione Enter.




A regra de validação da célula D2 foi respeitada, porém as demais cé-lulas continuam com dados inválidos. Procure novamente circular os dados e corrigir essas informações.

19. Na guia Dados, no grupo Ferramentas de Dados, clique em Circular Dados Inválidos.

Observe que a célula E2 possui indicação de dados inválidos. Isso se deu por causa da alteração da informação da célula D2. Por isso, nesse exemplo, é importante que a cada alteração seja aplicada uma nova validação.

20. Clique na célula E2 e digite 18:00.

21. Corrija os demais dados aplicando, após a correção, a validação de dados.

22. Salve o arquivo.

Atividade 5 – Personalizando regras de validação Objetivo: • Testar outras regras de validação.

Tarefas: • Criar regra de validação de data.

• Criar regra de validação de comprimento.

• Criar regra de validação de número inteiro.

• Criar regra de validação de número decimal.

• Criar regra de validação utilizando função Texto.





Restringindo entrada de dados com outros critérios 1. Clique na planilha Vendas Sudeste.

A planilha Vendas Sudeste refere-se a uma lista de equipamentos em que é necessário restringir a entrada de dados em um período no mês, denir um tamanho xo para o código do produto, evitar que os núme-ros da saída de equipamentos sejam maiores que o estoque, permitir que no máximo seja dado um desconto de 8% do valor unitário e que no campo Estado só seja digitado texto.

Tudo isso é possível de ser feito aplicando regras de validação.

Inserindo restrição de data 2. Selecione o intervalo A3:A10.


4. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Data.

5. Na caixa Dados, selecione a opção está entre.

6. Em Data de início será inserida a primeira data, que fará a referên-cia ao intervalo pretendido. Digite 01-05-2012.

7. Em Data de término, digite 31-05-2012 e clique em OK.




8. Na célula A3, digite 13-06-2012. Você receberá uma mensagem de erro ao tentar conrmar a data.

9. Clique em Repetir e digite a linha conforme abaixo:

Data 13-05-2012

Código R02

Equipamento Roteador

Estoque 55

Saída 15

Valor 99

Desconto 5

Estado RJ

Inserindo restrição de comprimentoOs dados do Código devem car restritos a até três caracteres.

10. Selecione o intervalo B3:B10.


12. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Comprimento de texto.

13. Na caixa Dados, selecione a opção é igual a.

14. Em Comprimento, digite 3 e clique em OK.

15. Na célula A4, digite 16-05-2012.

16. Na célula B4, digite N1. Você receberá uma mensagem de erro ao tentar conrmar o Código.





Código N01

Equipamento Notebook

Estoque 250

Saída 25

Valor 1500

Desconto 100

Estado SP

Inserindo restrição de númerosVocê deve restringir os dados de saída para que tenham um valor máxi-mo igual ao da quantidade de Estoque.

18. Selecione o intervalo E3:E10.


20. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Número inteiro.

21. Na caixa Dados, selecione a opção é menor ou igual a.

22. Em Máximo, digite =D3 e clique em OK.


24. Na célula B5, digite C02.

25. Na célula E5, digite 130. Você receberá uma mensagem de erro ao tentar conrmar o valor da saída.





Saída 90

Valor 2600

Desconto 200

Estado SP

Inserindo restrição de decimaisVocê deve restringir os dados de Desconto para que tenham um valor menor ou igual a 8% do valor do produto.

27. Selecione o intervalo G3:G10.


29. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Decimal.

30. Na caixa Dados, selecione a opção é menor ou igual a.

31. Em Máximo, digite =F3*0,08 e clique em OK.


33. Na célula B6, digite M04.

34. Na célula E6, digite 5.

35. Na célula G6, digite 100. Você receberá uma mensagem de erro ao tentar conrmar o valor do Desconto.


Desconto 43,90

Estado SP




Inserindo restrição de texto por meio de funçãoVocê deve restringir os dados do Estado para que não sejam inseridos números.

37. Selecione o intervalo H3:H10.


39. Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Personalizado.

40. Na caixa Dados, selecione a opção é igual a.

41. Em Fórmula, digite =ÉTEXTO(H3) e clique em OK.


43. Na célula B7, digite M15.

44. Na célula E7, digite 12.

45. Na célula G7, digite 20.

46. Na célula H7, digite 1. Você receberá uma mensagem de erro ao tentar conrmar o Estado.

47. Clique em Repetir e digite RJ.

48. Preencha as demais linhas com quaisquer valores, testando as re-gras. Salve e feche seu arquivo.

Consolidando dadosPara agrupar e resumir informações de planilhas separadas em uma úni-ca planilha, você pode consolidar os dados de outras planilhas em uma única que será o resumo de todas as informações. As planilhas podem estar na mesma pasta de trabalho que a planilha que será o resumo ou ainda em outras pastas de trabalhos.




Há duas maneiras para consolidar dados:

• Por posição: quando você tiver planilhas que foram criadas com o mesmo modelo e, os dados de origem forem organizados na mesma ordem e usarem a mesma linha e os mesmos rótulos de coluna.

• Por categoria: quando você tiver dados de várias planilhas organi-zados de forma diferente, porém com os mesmos rótulos de linha e coluna, ainda que cada planilha contenha itens diferentes ou um número diferente de itens.

Atividade 6 – Consolidando dadosObjetivo: • Consolidar planilhas criadas em um mesmo modelo.

Tarefas: • Abrir um arquivo.

• Consolidar valores de outras planilhas.


A empresa possui duas liais, uma em São Paulo e outra no Rio de Janeiro. Ambas possuem o mesmo modelo de planilha de despesas e semestralmente elaboram uma planilha de gastos.

Será necessário elaborar uma planilha contendo a combinação de dados e a soma dos respectivos valores de despesas constantes nas duas liais.

1. Abra o arquivo Consolidação de Despesas.xlsx.

A pasta trará três planilhas: Filial São Paulo, Filial Rio de Janeiro e Despesas Semestrais.




2. Clique na planilha Despesas Semestrais.

3. Clique na célula B3, para que ela seja a célula ativa.

Na planilha mestre, clique na célula superior esquerda da área em que você deseja que os dados consolidados sejam exibidos.

4. Na guia Dados, no grupo Ferramentas de Dados, clique no botão de comando Consolidar.

Será exibida a caixa de diálogo Consolidar.

Na caixa Função, serão exibidas várias opções, porém você deve deixar na opção Soma.




5. Na caixa Referência, clique no botão Recolher Caixa de Diálogo para selecionar os dados na planilha.

Será apresentada a caixa de diálogo Consolidar – Referência.

6. Na planilha Filial São Paulo, selecione o intervalo B3:G12.

7. Clique no botão Recolher na caixa de diálogo Consolidar – Refe-rência.




8. Com a caixa de diálogo Consolidar ampliada, clique no botão Adicionar.

O intervalo indicando a região da Filial São Paulo será inserido na caixa Todas as referências.

Você deve repetir os passos 5 a 8 para inserir o intervalo da Filial Rio de Janeiro.


10. Na planilha Filial Rio de Janeiro, selecione o intervalo B3:G12.



13 Com o intervalo da Filial Rio de Janeiro inserido na caixa Todas as referências, clique no botão OK.




As somas das despesas constantes nas duas liais serão apresentadas na planilha, consolidando os valores.

14. Salve o arquivo

Atividade 7 – Consolidando dados com vínculoObjetivo: • Consolidar planilha mantendo vínculos com as planilhas

de origem.Tarefas: • Congurar a consolidação para que ela se atualize auto-

maticamente quando os dados de origem forem alterados. • Salvar o arquivo.

Em caso de qualquer alteração nos valores nas planilhas Filial São Paulo ou Filial Rio de Janeiro, os valores na planilha Despesas Semestrais não serão alterados. Isso somente acontecerá se houver um vínculo nos itens consolidados. 1. Na planilha Despesas Semestrais, clique na célula B3 para que ela

seja a célula ativa. 2. Na guia Dados, no grupo Ferramentas de Dados, clique no botão de

comando Consolidar. 3. Marque a caixa de seleção Criar vínculos com dados de origem.





Importante: marque essa caixa de seleção apenas se a planilha que con-tém os dados estiver em outra pasta de trabalho. Depois de marcar essa caixa de seleção, não será mais possível alterar as células e os intervalos que estão inclusos na consolidação.

O Excel exibirá uma área cinza com números (1 e 2) e com sinais de adição (+) do lado esquerdo. Note que a planilha sofrerá alterações nas respectivas linhas em que estão as despesas (Água, Luz e Telefone foram deslocadas duas linhas para baixo, o mesmo acontecendo com as demais despesas).

Cada sinal de adição compreende um agrupamento de linhas que cor-respondem ao total que é exibido.

5. Clique no número 2 do lado esquerdo.

Todas as linhas que estavam ocultas serão exibidas, e o símbolo de sub-tração substituirá o símbolo de adição para cada total exibido.

6. Clique no número 1 do lado esquerdo.




A planilha retornará a situação anterior. Você pode visualizar um item de despesa isoladamente em vez da planilha inteira.

7. Clique no sinal de adição, que corresponde à despesa Água, Luz e Telefone da linha 5.

As linhas 3 e 4 que são exibidas correspondem aos valores das liais de São Paulo e do Rio de Janeiro. Ao posicionar o cursor, por exemplo, na célula B3, observe que o conteúdo é uma fórmula que faz referência à planilha Filial Rio de Janeiro.

8. Clique no sinal de subtração para ocultar novamente as linhas 3 e 4.

A grande vantagem da consolidação com vínculo é que você pode alte-rar valores das planilhas que compõem os dados, alterando a exibição dos valores na planilha consolidada.

9. Na planilha Filial São Paulo, na célula B3, altere o valor da despesa Água, Luz e Telefone para 3000.

10. Retorne novamente à planilha Despesas Semestrais.

O valor será reajustado automaticamente.

11.Salve e feche o arquivo.




Atividade 8 – Consolidando dados em outras pastasObjetivo: • Consolidar dados de vários arquivos em uma única planilha.

Tarefas: • Abrir arquivos existentes.

• Consolidar dados em uma planilha.


O Senac oferece cursos de pós-graduação em várias de suas unidades. Essas informações estão distribuídas em arquivos. O objetivo é con-solidar as informações de números de vagas oferecidas e números de inscritos em uma única planilha.

Abra as planilhas Consolidação Geral, Consolidação Tito, Consolidação Sto Amaro e Consolidação 9 de Julho.

O Excel abrirá as quatro planilhas, mas exibirá apenas a última planilha aberta. Você deve organizar as planilhas para que sejam exibidas lado a lado, para facilitar a utilização dos dados.

1. Em qualquer planilha que esteja aberta, na guia Exibição, no grupo Janela, clique no botão de comando Organizar Tudo.




Será exibida a caixa de diálogo Organizar janelas.

As janelas serão exibidas lado a lado.

2. Selecione a opção Lado a lado e clique no botão OK.

Observe que as demais planilhas possuem as mesmas informações, mas que não estão na mesma ordem e nem na mesma quantidade (número de linhas). Talvez elas não tenham sido abertas na mesma ordem da gura. Você deverá consolidar os dados na planilha Consolidação_Geral.

3. Na planilha Consolidação_Geral, clique na célula B3 para que seja a célula ativa.

4. Na guia Dados, no grupo Ferramentas de Dados, clique no botão de comando Consolidar.

Você deverá indicar as referências de dados nos outros arquivos abertos.

5. Na caixa de diálogo Consolidar, em Função, mantenha a opção Soma.


7. Com a caixa de diálogo Consolidar-Referência aberta, selecione os dados da região A3:C7 da planilha Consolidação-Campus_Stoamaro.






Repita os procedimentos de 6 a 10 para adicionar os demais itens.

10. Após adicionar o itens referentes às planilhas Consolidação-9dejulho e Consolidação-Tito, selecione a opção Coluna esquerda na caixa Usar rótulos na.





A planilha Consolidação-Geral trará os dados referentes aos cursos consolidados.

12. Salve o arquivo Consolidação_Geral e feche os demais arquivos.




Resumo do capítulo


Adicionar regras de validação

Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados.

Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Lista.

Clique na caixa Fonte, digite os valores da lista e clique no botão OK.

Denir mensagens de dados


Na guia Congurações, desmarque a opção Menu suspenso na célula.

Clique na guia Mensagem de entrada.

Na caixa Título, digite um texto identicador, na caixa Mensagem de entrada, digite Utilize apenas os códigos necessários e clique no botão OK.

Ou ainda:


Clique na guia Alerta de erro.

Na caixa Mensagem de erro, digite a mensagem desejada.

Na caixa de combinação Estilo, escolha a opção desejada.





Criar regras de validação em lista de outra planilha


Na guia Congurações, na caixa Permitir, escolha a opção Lista.

Na caixa Fonte, clique no botão Recolher.

Clique na outra planilha e selecione o intervalo desejado.

Clique no botão OK. Os valores sofrerão restrição para a seleção indicada.

Aplicar validação a dados já inseridos


Na guia Congurações, na caixa Permitir, escolha a opção Hora.

Na caixa Dados, escolha a opção é maior ou igual a.

Na caixa Hora Inicial, digite a fórmula.

Clique no botão OK.

Personalizar regras de validação


Na caixa de diálogo Validação de dados, na guia Congurações, na caixa Permitir, selecione Data.

Na caixa Dados, selecione a opção Está entre.

Em Data de início, será inserida a primeira data que fará referência ao intervalo pretendido.

Em Data de término, clique em OK.





Consolidar dados

Na guia Dados, no grupo Ferramentas de Dados, clique no botão de comando Consolidar.Na caixa Função, escolha a opção Soma.Na caixa Referência, clique no botão Recolher Caixa de Diálogo para selecionar os dados na planilha.Em Consolidar – Referência, selecione o intervalo da planilha desejadaCom a caixa de diálogo Consolidar ampliada, clique no botão Adicionar.Clique em OK.

Consolidar dados com vínculos

Na guia Dados, no grupo Ferramentas de Dados, clique no botão de comando Consolidar.Marque a caixa de seleção Criar vínculos com dados de origem.Clique no botão OK.

Consolidar dados em outras pastas

Abra várias planilhas.Na guia Dados, no grupo Ferramentas de Dados, clique no botão de comando Consolidar.Na caixa de diálogo Consolidar, em Função, mantenha a opção Soma.Na caixa Referência, clique no botão Recolher Caixa de Diálogo para selecionar os dados na planilha.Com a caixa de diálogo Consolidar − Referência aberta, selecione os dados da região da planilha desejada.Clique no botão Recolher na caixa de diálogo Consolidar – Referência.Com a caixa de diálogo Consolidar ampliada, clique no botão Adicionar.Após adicionar os itens referentes às planilhas, selecione a opção Coluna esquerda na caixa Usar rótulos na.Clique no botão OK.



Anotações

Anotações


11Estrutura de dados

O B J E T I V O Agrupar linhas ou colunas de resumo utilizando o recurso estrutura de tópicos.




Estrutura de dados

Para organizar uma lista de dados, você pode criar uma estrutura de tópicos. Nela, serão exibidas linhas e colunas de resumo e será possível trabalhar em até oito níveis. O nível interno é representado pelo número mais alto. O resumo dos dados é representado por símbolos que alteram o modo de exibição de uma planilha. Essa organização é chamada de estrutura de tópicos.

Atividade 1 – Trabalhando com estrutura de tópicosObjetivo: • Trabalhar dados de uma planilha por meio de uma estrutu-

ra de tópicos.

Tarefas: • Abrir um arquivo.

• Criar uma estrutura de tópicos em linha.

• Exibir ou ocultar uma estrutura de dados.

• Desagrupar uma estrutura de tópicos em linha.

• Remover uma estrutura de tópicos.

• Criar uma estrutura de tópicos por coluna.

• Incluir subtotais.

• Incluir estrutura de AutoTópicos.

• Salvar arquivo.

Agrupar uma estrutura de tópicos em linha 1. Abra o arquivo Vendas Loja Shopping.xlsx.

Uma loja de eletrônicos deseja controlar as suas vendas quinzenalmente por meio de uma planilha. Para isso, necessita resumir os principais dados, já que a planilha não permite uma fácil visualização.

2. Clique na célula A4 para que ela seja a célula ativa.

Você deve selecionar o intervalo entre a descrição e os totais exibidos.

AOb

Ta

M



Estrutura de dados

3. Selecione o intervalo de A4:L7.

4. Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Agrupar.

Será apresentada a caixa de diálogo Agrupar.

5. Escolha a opção Linhas e clique no botão OK.

Será exibido um resumo dos dados com símbolos à esquerda de sua planilha. Números simbolizando, no seu exemplo, dois níveis (números 1 e 2) e o sinal de subtração.



Estrutura de dados

6. Clique no botão 1 para exibir o resumo dos dados.

Você ocultará a descrição dos equipamentos dos games, apresentando apenas os totais.

Note que no lugar do símbolo de subtração, o Excel exibe o sinal de adição. Isso indica que existem dados resumidos e ocultos.

7. Para reexibir os dados ocultos, clique no botão 2.

Você pode exibir e ocultar os itens em estrutura de tópicos pres-sionando os símbolos de adição ( ) e subtração ( ), ou ainda os botões de comando Mostrar Detalhe e Ocultar Detalhe.

8. Escolha a opção Linha e clique no botão OK.

Você deverá resumir os demais dados, seguindo o mesmo raciocínio dos passos 3 a 5. Procure resumir toda a estrutura para facilitar a visualização.

Ao nal do exercício, sua planilha deverá ter o seguinte aspecto:

Desagrupar uma estrutura de tópicos em linha 1. Clique no botão 2 para exibir toda a estrutura de tópicos.

2. Selecione o intervalo de A4:L7.



Estrutura de dados

3. Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Desagrupar.

Será exibida a caixa de diálogo Desagrupar.

4. Escolha a opção Linhas e clique no botão OK.

Note que somente esse intervalo voltará ao normal sem a estrutura de tópicos.

Remover uma estrutura de tópicosVocê pode desagrupar os demais itens de uma única vez. Para isso, siga estes passos:

1. Clique em qualquer célula dentro de sua estrutura de tópicos.

2. Na guia Dados, no grupo Estrutura de Tópicos, clique na seta abai-xo do botão de comando Desagrupar.

3. Clique na opção Limpar estrutura de tópicos.

Sua planilha retornará ao estado inicial, removendo todas as estruturas de tópicos.



Estrutura de dados

Agrupar uma estrutura de tópicos em coluna 1. Clique na planilha Plan2 . Você terá um mo-

delo já preenchido para esse próximo exercício.

Essa planilha já terá uma estrutura de tópicos criada por linha. Você fará a inserção de uma estrutura por colunas para melhorar ainda mais a visualização.

2. Selecione as colunas D e F.

3. Na guia Dados, no grupo Estrutura de Tópicos, clique na seta abai-xo do botão de comando Agrupar.

Pelo fato de você ter selecionado as colunas e não uma área, não foi solicitada a indicação de linha ou coluna.

Você poderá resumir e ocultar as colunas correspondentes às quinzenas apresentando apenas o valor do mês. Utilize para isso o botão 1 ou o sinal de subtração.

4. Faça o mesmo para criar uma estrutura de tópicos nas quinzenas dos meses de fevereiro (colunas G e H) e março (colunas J e K). Utilize o raciocínio das etapas 2 e 3.

Após a inserção de estrutura de tópicos por coluna e agrupando os dados, seu modelo apresentará os agrupamentos conforme a gura a seguir.



Estrutura de dados

Com o agrupamento por coluna, você obterá níveis de resumo que per-mitirão novas visualizações dos resultados.

Inserindo subtotais em uma lista de dados de uma planilhaO comando Subtotal permite que o Excel calcule automaticamente os subtotais e os totais gerais em uma lista para uma coluna. Os subtotais são calculados com uma função de resumo como Soma ou Média.

1. Clique na planilha Plan3. Nela, você encontrará uma planilha já preenchida para essa atividade.

Para esse exercício, algumas modicações foram feitas:

• Na coluna A, foi inserido o rótulo Setor e foram feitas as cópias da descrição de cada setor.

• Foram retirados os totais de cada setor.

2. Selecione o intervalo A4:L23.

3. Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Subtotal.

O Excel exibirá uma caixa de diálogo indicando que existe uma linha que poderá servir de rótulo para cada lista de dado.

4. Clique no botão Sim.

Será exibida a caixa de diálogo Subtotais. Essa caixa de diálogo mostra que a cada alteração na descrição do setor será inserida uma totalização dos valores ou dos itens selecionados.



Estrutura de dados

5. No item A cada alteração em, escolha Setor. 6. No item Usar função, escolha Soma. 7. No item Adicionar subtotal a, selecione todos os itens relacionados

a Valor. 8. Deixe marcadas as opções Substituir subtotais atuais e Resumir

abaixo dos dados. 9. Clique no botão OK.A planilha exibirá os subtotais de cada valor a partir da mudança da des-crição do setor e totalizará os valores no nal da planilha. Os três níveis de agrupamento corresponderão ao total geral para o nível 1, subtotais por setor para o nível 2 e para a planilha geral nível 3.

Agrupando dados em uma planilha por meio de AutoTópicosO comando AutoTópicos permite que o Excel calcule automaticamente os subtotais e os totais gerais em uma planilha que já possua dados totalizados. 1. Clique na planilha Plan4. Nela, você encontrará uma planilha já

preenchida para essa atividade.Essa planilha é bem simples e possui a totalização mensal dos equipa-mentos vendidos e a totalização bimestral de cada produto. 2. Clique em qualquer célula dentro da planilha. 3. Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de

comando Agrupar. 4. Clique na opção AutoTópicos.



Estrutura de dados

Sua planilha terá inseridos todos os agrupamentos automaticamente. O Excel considerará a cada subtotal um agrupamento a ser construído.




Estrutura de dados

Resumo do capítulo


Agrupar tópicos Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Agrupar.Escolha a opção Linhas e clique no botão OK.Clique no botão 1 para exibir o resumo dos dados.Para reexibir os dados ocultos, clique no botão 2.

Desagrupar tópicos Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Desagrupar.Escolha a opção Linhas e clique no botão OK.

Remover uma estrutura de tópicos

Na guia Dados, no grupo Estrutura de Tópicos, clique na seta abaixo do botão de comando Desagrupar.Clique na opção Limpar estrutura de tópicos.

Inserir subtotais Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Subtotais. Clique no botão Sim.No item A cada alteração em, escolha Setor.No item Usar função, escolha Soma.No item Adicionar subtotal a, selecione todos os itens relacionados a Valor.Deixe marcadas as opções Substituir subtotais atuais e Resumir abaixo dos dados.Clique no botão OK.

Agrupar por meio de AutoTópicos

Na guia Dados, no grupo Estrutura de Tópicos, clique no botão de comando Agrupar. Clique na opção AutoTópicos.



Anotações

Anotações


12Proteção

O B J E T I V O erenciar um arquivo do Excel protegendo partes de uma planilha, estrutura ou arquivo completo por meio de senha.




Proteção

É muito comum trabalhar e compartilhar planilhas. Assim, outros usuá-rios terão acesso à informação e, se necessário, alterarão os conteúdos. Em alguns casos, porém, isso pode ser catastróco, pois um usuário sem conhecimento do Excel pode danicar, acidental ou propositada-mente, a planilha criada, alterando ou mesmo excluindo informações que prejudiquem seu correto funcionamento. Para evitar isso, você pode proteger determinados elementos da planilha ou da pasta de traba-lho, inserindo ou não uma senha.

Atividade 1 - Protegendo elementos da planilhaObjetivo: • Permitir restrições na planilha para que usuários não pos-

sam alterá-la, desbloqueando apenas partes para digitação.

Tarefas: • Abrir arquivo.

• Proteger elementos de uma planilha.

• Remover a proteção.


Quando você compartilha um arquivo com outros usuários, é reco-mendável proteger dados em partes especícas de sua planilha para impedir alterações. Outros usuários não conseguem inserir, modi-car, excluir ou formatar dados em uma célula bloqueada. E é possível especicar quais elementos os usuários podem alterar, quando uma planilha é protegida.

1. Abra o arquivo Vendas Trimestre.xlsx.

2. Selecione o intervalo D3:E17. Esse intervalo será desbloqueado e o usuário poderá alterá-lo.

Por padrão, quando uma planilha é protegida, todas as suas células são bloqueadas, impedindo qualquer alteração por parte dos usuários. As-sim, para que a área selecionada possa ser utilizada, é necessário que você desative o recurso Bloquear célula.

3. Na guia Página Inicial, no grupo Células, clique em Formatar.

4. Na guia Proteção, clique em Bloquear Célula para desativar o bloqueio.



Proteção

5. Na guia Revisão, no grupo Alterações, clique em Proteger Planilha.

Será exibida a caixa de diálogo Proteger planilha.

É possível especicar uma senha que os usuários terão que digitar para alterar elementos especícos protegidos de planilhas e pastas de trabalho.

Ao proteger uma planilha ou pasta de trabalho, você tem a opção de adicionar uma senha. Esse nível de proteção por bloqueio de elementos por senha não garante que todos os dados sigilosos da pasta de trabalho



Proteção

estejam seguros. É recomendável proteger a própria pasta de trabalho com uma senha, para ajudar a evitar acesso não autorizado.

Use senhas que combinem letras maiúsculas e minúsculas, números e símbolos. Procure criar uma senha com oito ou mais caracteres. É fun-damental que você não se esqueça de sua senha. Sem ela, não será pos-sível desproteger a pasta de trabalho ou planilha e o Excel não poderá recuperá-la.

Além disso, é possível impedir que os usuários alterem a estrutura de uma planilha. Na caixa de listagem existem várias opções:

• Selecionar células bloqueadas: permite que os usuários movimen-tem o ponteiro para células nas quais a caixa de seleção Bloquear Célula está marcada. Por padrão, o usuário pode selecionar células bloqueadas.

• Selecionar células desbloqueadas: permite que os usuários mo-vimentem o ponteiro para células nas quais a caixa de seleção Bloquear Célula está desmarcada na guia Proteção da caixa de diálogo Formatar Células. Por padrão, os usuários podem selecio-nar células desbloqueadas e pressionar a tecla TAB para mover-se entre células desbloqueadas em uma planilha protegida.

• Formatar células: permite que os usuários alterem qualquer uma das opções das caixas de diálogo Formatar Células ou Formatação Condicional. Se você aplicou formatos condicionais antes de prote-ger a planilha, a formatação continuará sendo alterada quando um usuário inserir um valor que atenda a uma condição diferente.

• Formatar Colunas: permite que os usuários usem qualquer coman-do de formatação de coluna, incluindo a alteração da largura da co-luna ou a ocultação das colunas.

• Formatar linhas: permite que os usuários usem qualquer comando de formatação de linha, incluindo a alteração da altura da linha ou a ocultação das linhas.

• Inserir colunas: permite que os usuários insiram colunas.

• Inserir linhas: permite que os usuários insiram linhas.

• Inserir hiperlinks: permite que os usuários insiram novos hiperlinks, até mesmo em células desbloqueadas.

• Excluir colunas: permite que os usuários excluam colunas.

• Excluir linhas: permite que os usuários excluam linhas.

• Classicar: permite que os usuários usem qualquer comando para classicar dados.

• Usar AutoFiltro: permite que os usuários usem setas suspensas para alterar o ltro em intervalos quando os AutoFiltros forem aplicados.



Proteção

• Usar relatórios de tabela dinâmica: permite que os usuários forma-tem, alterem o layout, atualizem ou modiquem de alguma forma os relatórios de tabela dinâmica ou criem novos relatórios.

• Editar objetos: permite que os usuários façam alterações em obje-tos grácos, incluindo mapas, grácos incorporados, formas, caixas de texto e controles que você não desbloqueou antes de proteger a planilha.

• Editar cenários: permite que os usuários exibam cenários que você ocultou, façam alterações em cenários que não podem ser alterados e excluam esses cenários.

6. Deixe selecionada a opção Proteger a planilha e o conteúdo de células bloqueadas.

7. Digite a seguinte senha: cursoexcel2010.

Lembre-se de que o Excel diferencia as letras maiúsculas das letras minúsculas.

8. Mantenha selecionadas as opções Selecionar células bloqueadas e Selecionar células desbloqueadas e clique em OK.

9. Digite novamente a senha e clique em OK.

Caso ocorra qualquer tentativa de alteração das áreas que não foram selecionadas, o Excel exibirá um aviso.

Uma vez protegidos os elementos da planilha, basta salvar o arquivo e distribuir para a utilização de outro usuário.

Observe que novas digitações podem ser feitas no intervalo D3:E17.

10. Com a planilha protegida, digite quaisquer valores nos meses de fevereiro e março.

Para desbloquear os elementos da planilha, siga os seguintes passos:

11. Na guia Revisão, no grupo Alterações, clique em Desproteger Planilha.

12. Digite a senha cursoexcel2010 e clique em OK.




Proteção

Atividade 2 - Protegendo uma planilhaObjetivo: • Ocultar a estrutura da pasta de trabalho para que usuários

não possam alterá-la.

Tarefas: • Ocultar planilha.

• Proteger planilha.

• Reexibir planilha.

• Salvar arquivo.

É possível bloquear a estrutura de uma pasta de trabalho, o que impede os usuários de adicionar ou excluir planilhas, ou de exibir pastas de trabalho ocultas. Também é possível impedir os usuários de alterar o ta-manho ou a posição de janelas de planilhas. A proteção da estrutura e de janelas da pasta de trabalho se aplica à própria pasta de trabalho inteira.

1. Abra o arquivo Comissão e Vendas.xlsx.

Observe que nesse arquivo existem duas planilhas já criadas: Comissão e Vendas.

Será importante permitir alterações na planilha de Vendas e não permitir acesso aos usuários à planilha Comissão, pois essa exibe o percentual que é utilizado na fórmula e que deverá ser restrito a quem administra a área.

2. Selecione a planilha Comissão.

3. Clique com o botão direito do mouse e oculte a planilha.

4. Na guia Revisão, no grupo Alterações, clique em Proteger Pasta de Trabalho.



Proteção

Será exibida a caixa de diálogo Proteger Estruturas e Janelas.

Para proteger a estrutura de uma pasta de trabalho, marque as caixas de seleção relacionadas a seguir.

EstruturaEssa opção faz com que os usuários sejam impedidos de ver as pla-nilhas que você tenha ocultado; mover, excluir, ocultar ou alterar os nomes das planilhas e de inserir novas planilhas ou planilhas de gráco. Também não será possível que movam ou copiem planilhas para outra pasta de trabalho. Em relatórios de tabela dinâmica, não será possível exibir os dados de origem de uma célula da área de dados ou exibir páginas de campo de página em planilhas separadas; em cenários, não será possível criar um relatório de resumo do cenário; em ferramentas de análise, não será possível usar as ferramentas de análise que colo-quem resultados em uma nova planilha.

JanelaFaz com que os usuários sejam impedidos de alterar o tamanho e a posi-ção das janelas da pasta de trabalho quando esta for aberta. Não permite que movam, redimensionem ou fechem as janelas.

5. Selecione as opções Estrutura e Janelas.

6. Digite uma senha e clique em OK.

7. Clique com o botão direito do mouse em Vendas.



Proteção

Observe que não está disponível qualquer tentativa de mover, excluir ou mesmo reexibir a planilha oculta. A planilha Vendas, porém, está disponível para digitação dos valores dos respectivos meses, e quem zer as alterações não verá os percentuais que são utilizados por cada funcionário.

Para desproteger a pasta de trabalho, siga as instruções:

8. Na guia Revisão, no grupo Alterações, clique em Proteger Pasta de Trabalho.

9. Digite a senha escolhida e clique em OK.

10. Reexiba a planilha anteriormente oculta.


Atividade 3 – Protegendo um arquivoObjetivo: • Proteger um arquivo, bloqueando o acesso aos seus dados

por meio de uma senha.

Tarefas: • Proteger um arquivo inserindo senha.

• Abrir um arquivo com senha de proteção.

• Desproteger um arquivo.

• Salvar arquivo.

É possível bloquear o acesso ao arquivo. Para isso, é necessário utilizar senhas para ajudar a evitar que outras pessoas abram ou modiquem uma planilha. Esse nível de proteção impede o acesso ao arquivo.



Proteção

Inserindo uma senha 1. Abra o arquivo Vendas Trimestre.xlsx.

2. Na guia Arquivo, em Informações, na opção Permissões, clique em Proteger Pasta de Trabalho.

Serão exibidas as opções relacionadas a seguir.

Marcar como Final Torna o documento “somente leitura”. Quando uma planilha é marcada como nal, a digitação, a edição de comandos e as marcas de revisão de texto são desabilitadas ou desativadas, e a planilha se torna somente leitura. Esse comando ajuda a comunicar que você está compartilhando uma versão concluída de uma planilha, evitando que revisores ou leito-res façam alterações inadvertidas na planilha.

Criptografar com SenhaDene uma senha para o documento. Quando você seleciona Cripto-grafar com Senha, a caixa de diálogo Criptografar Documento é exibi-da. Na caixa Senha, digite uma senha.

Importante: a Microsoft não pode recuperar senhas perdidas ou es-quecidas, por isso, mantenha uma lista de suas senhas e os nomes de arquivo correspondentes em um local seguro.



Proteção

Proteger Planilha AtualProtege a planilha e as células bloqueadas. Com esse recurso, você pode selecionar a proteção por senha e permitir ou impedir outros usuários de selecionar, formatar, inserir, excluir, classicar ou editar áreas da planilha.

Proteger Estrutura da Pasta de TrabalhoProtege a estrutura da planilha. Você pode selecionar a proteção por se-nha e opções para impedir que os usuários alterem, movam ou excluam dados importantes.

Restringir Permissão por Pessoas Instala o Gerenciamento de Direitos do Windows para restringir per-missões. Para isso, use um Windows Live ID ou uma conta do Micro-soft Windows para restringir permissões. Você pode aplicar permissões por meio de um modelo usado por sua organização ou adicionar permis-sões; para isso, basta clicar em Restringir Acesso.

Adicionar uma Assinatura DigitalAdiciona uma assinatura digital visível ou invisível. As assinaturas di-gitais autenticam informações digitais, como documentos, mensagens de e-mail e macros, usando a criptograa do computador. Para criar as-sinaturas digitais, digite uma assinatura ou use uma imagem de uma as-sinatura para estabelecer a autenticidade, a integridade e o não repúdio.

3. Clique em Criptografar com Senha.

4. Na caixa Criptografar Documento, digite a senha cursoexcel2010 e clique em OK.

5. Na caixa Conrmar Senha, digite novamente cursoexcel2010 e cli-que em OK.




Proteção

Abrindo um arquivo com senhaAo distribuir ou receber um arquivo protegido, será necessário que o autor passe ou receba a senha de acesso. Para isso, siga estes passos:

7. Abra o arquivo Vendas Trimestre.xlsx.

O Excel solicitará a digitação da senha.

8. Na caixa Senha, digite cursoexcel2010 e clique em OK.

A planilha será aberta e permitirá que o usuário faça qualquer alteração.

Retirando a senha de proteçãoIndependentemente de o usuário salvar ou não esse arquivo, para entrar novamente nele será necessário digitar a senha de acesso. Entretanto, se o usuário quiser retirar a senha, deve seguir estes passos:

9. Na guia Arquivo, em Informações, na opção Permissões, clique em Proteger Planilha.

10. Clique em Criptografar Senha, apague a senha digitada e clique em OK.

11 .Salve e feche o arquivo.

Ao abrir novamente o mesmo arquivo, não será mais solicitada a senha de acesso.



Proteção

Resumo do capítulo


Proteger elementos de uma planilha

Selecione o intervalo desejado.

Na guia Página Inicial, no grupo Células, clique em Formatar.

Na guia Proteção, clique em Bloquear Célula para ativar o bloqueio.

Na guia Revisão, no grupo Alterações, clique em Proteger Planilha.

Especique as estruturas que deseja bloquear.

Digite uma senha e clique em OK.

Proteger uma planilha

Na guia Revisão, no grupo Alterações, clique em Proteger Pasta de Trabalho. Selecione a planilha Comissão.

Com o botão direito do mouse, oculte a planilha desejada.

Na guia Revisão, no grupo Alterações, clique em Proteger Pasta de Trabalho.

Selecione as opções Estrutura e Janelas.

Digite uma senha e clique em OK.

Proteger um arquivo

Na guia Arquivo, em Informações, na opção Permissões, clique em Proteger Planilha.

Clique em Criptografar com Senha.

Na caixa Criptografar Documento, digite a senha cursoexcel2010 e clique em OK.

Na caixa Conrmar Senha, digite novamente cursoexcel2010 e clique em OK.

Salve e feche o seu arquivo.



Anotações

Anotações


13Macros interativas

O B J E T I V O Gravar macros por meio do gravador de macros.




Macros interativas

Macro é uma sequência de procedimentos que serão executados com a nalidade de realizar tarefas repetitivas.

As macros podem ser criadas de duas formas:

• Pelo gravador de macros: criadas a partir da gravação dos proce-dimentos realizados pelo usuário na planilha. Esse processo será estudado neste capítulo.

• Pela linguagem VBA: criadas pela escrita do script da macro, uti-lizando comandos e funções da linguagem VBA (Visual Basic for Applications).

A gravação de uma macro requer que saibamos exatamente a sequência de procedimentos que deveremos realizar para alcançar nosso objetivo. Uma boa prática é anotar em um papel todos os comandos que serão executados, na ordem em que devem ser executados.

A empresa Confecções Mariska mantém um registro de suas vendas mensais por meio de planilha do Excel. O departamento responsável pela manutenção da planilha recebe do departamento de vendas um relatório contendo o volume de vendas no mês corrente. A cada mês, Silvana, que efetua a atualização da planilha geral de vendas, tem que transferir os dados do relatório recebido para o seu controle. Por ser uma tarefa que se repete todos os meses, Silvana decide criar uma ma-cro que execute os procedimentos de transferência, cálculo e formata-ção dos dados de forma automatizada.

A planilha que contém os registros das vendas mensais é a planilha Vendas, que se encontra na pasta Mariska-Vendas.xlsx. A planilha que Silvana recebe todos os meses do departamento de vendas tem o nome de Vendas no Mês e está na mesma pasta.

Atividade 1 – Criando macros interativasObjetivo: • Criar macro interativa utilizando o gravador de macros.

Tarefas: • Criar macro que atualize a planilha Vendas da pasta Ma-riska-Vendas.xlsx com os dados da planilha Vendas no Mês.

• Gravar a pasta Mariska-Vendas no formato de pasta habi-litada para macros.

• Habilitar as macros no carregamento da planilha Mariska--Vendas.xlsm.



Macros interativas

Na planilha Vendas da pasta Mariska-Vendas.xlsx, os procedimen-tos que Silvana deve executar para gravar a macro desejada são os seguintes:

• Clicar na célula H4.

• Copiar os dados da planilha Vendas no Mês para a planilha Vendas.

• Formatar adequadamente a planilha Vendas.

1. Abra a pasta Mariska-Vendas.xlsx.

2. Na guia Exibição, no grupo Macros, clique na setinha da ferramenta Macros e escolha a opção Gravar Macro...

O gravador também pode ser acessado clicando na ferramenta Gravar Macro na Barra de Status.



Macros interativas

3. A caixa de diálogo se abrirá para que você preencha os campos:

• Nome da macro: você pode adotar como nome da macro o padrão sugerido pelo Excel. No entanto, é melhor que atribua um nome que identique o que a macro executa. Esse nome não pode conter espaços em branco. Digite PreencheVendas.

• Tecla de atalho: combinação de teclas que pode ser utilizada para executar a macro. Digite a letra P.

• Armazenar macro em: podemos armazenar a macro nas seguintes pastas:

• Pasta de trabalho pessoal de macros: a macro cará gravada no Excel do seu computador e estará disponível em qualquer pasta de trabalho. Neste caso, a macro será gravada na pasta do Excel de nome Personal.xlsb.

• Nova pasta de trabalho: a macro cará disponível em todas as novas pastas criadas.

• Esta pasta de trabalho: a macro cará disponível somente na pasta em que foi gravada. Selecione esta opção.

• Descrição: breve descrição da macro.

Clique no botão OK. A partir de agora todos os seus movimentos na planilha serão gravados.

4. Selecione a planilha Vendas no Mês.

5. Copie a faixa de células B1:B7.

6. Selecione a planilha VENDAS.

7. Clique na célula H4.

8. Cole as células copiadas.



Macros interativas

9. Clique na célula G3 e, pela alça de preenchimento, preencha o valor da célula G4.

10. Clique na célula B2, mescle e centralize corretamente o conteúdo.

11. Faça o mesmo na célula A1.

12. Formate corretamente as células H4:H10.

13. Na guia Exibição, no grupo Macros, clique na setinha da ferramenta Macros e escolha a opção Parar gravação.

A gravação também poderá ser interrompida clicando na ferramenta Parar gravação na Barra de Status.

14. Salve a pasta de trabalho. Observe que, ao tentar salvá-la no forma-to .xlsx, o Excel envia uma mensagem informando que a planilha contém macros e que, se quisermos que sejam gravadas junto com a pasta, é necessário modicar o tipo do arquivo para Pasta de Tra-balho Habilitada para Macro do Excel (.xlsm).

Clique em Não e, na janela Salvar Como, escolha o tipo de arquivo adequado para salvar a pasta (.xlsm).

15. Feche a pasta de trabalho.

16. Vamos agora abrir a pasta salva (Mariska-Vendas.xlsm).



Macros interativas

17. Observe o aviso de segurança. Quando abrimos uma pasta que con-tém macro, o Excel interpreta o código como um possível perigo à execução da planilha.

Para poder executar as macros gravadas é necessário habilitar o conteúdo. Clique no botão Habilitar Conteúdo.

Segurança de macroA partir da versão 2007 do Excel, as congurações para ajuste de segu-rança de macros foram modicadas para que a sua execução seja mais segura, evitando que códigos maliciosos possam danicar pastas de tra-balho, arquivos ou mesmo o próprio sistema operacional.

Para acessar a janela que permite escolher o nível de segurança de ma-cro, clique na guia Arquivo, Opções.



Macros interativas

Na janela apresentada, selecione a opção Central de Conabilidade.

18. Clique em Congurações da Central de Conabilidade...



Macros interativas

Ao clicar no botão Congurações de Macro, você terá as seguintes opções:

• Desabilitar todas as macros sem noticação: desabilita todas as ma-cros e impede que sejam executadas. Se for ativada, somente as ma-cros presentes em locais conáveis poderão ser executadas. Esses locais conáveis podem ser estabelecidos pelo usuário.

• Desabilitar todas as macros com noticação: as macros estão de-sabilitadas, mas poderão ser habilitadas pela mensagem exibida na Área de Mensagem no carregamento da planilha. É a opção reco-mendada para a maioria das pastas de trabalho do Excel.

• Desabilitar todas as macros, exceto as digitalmente assinadas: permite que somente as macros que tenham assinatura digital sejam executadas.

• Habilitar todas as macros (não recomendado; códigos possivel-mente perigosos podem ser executados): habilita todas as macros, tornando o programa vulnerável a ataques de códigos maliciosos. Essa opção não é recomendada.

Atividade 2 – Executando macrosObjetivo: • Executar a macro gravada.

Tarefas: • Executar a macro PreencheVendas pela caixa de diálogo de macros.

• Executar a macro PreencheVendas pelas teclas de atalho.

• Executar a macro PreencheVendas por meio de um botão criado na Barra de Ferramentas de Acesso Rápido.



Macros interativas

Agora que a macro foi criada, vamos aprender a executá-la. Existem várias formas de executar macros. Neste capítulo veremos três manei-ras: pela ferramenta Macros, pelas teclas de atalho e por um botão co-locado na Barra de Ferramentas de Acesso Rápido.

1. Apague toda a coluna H para retornar a planilha aos seus valores anteriores à execução da macro.

2. Vamos executar a macro pela ferramenta Macros. Na guia Exibição, selecione a ferramenta Macros e clique sobre o nome da macro cria-da e em Executar.

3. Vamos executar a macro pelas teclas de atalho. Elimine a coluna H e pressione as teclas Ctrl+Shift+P.

4. Vamos agora atribuir a macro a um botão que será colocado na Bar-ra de Ferramentas de Acesso Rápido. Na guia Arquivo, clique em Opções.



Macros interativas

5. Clique em Barra de Ferramentas de Acesso Rápido.

6. No quadro Escolher comandos em, selecione Macros. A seguir, clique sobre o nome da macro PreencheVendas e em Adicionar >>.



Macros interativas

7. Observe que o nome da macro foi adicionado ao quadro da direita. Se você for utilizar a macro somente quando esta pasta de trabalho estiver aberta, selecione a opção De Mariska-Vendas.xlsm.

8. Para mostrar o botão na Barra de Ferramentas de Acesso Rápido, o Excel vai utilizar um ícone genérico. Vamos escolher um ícone per-sonalizado para representar a macro. Clique sobre o nome da macro no quadro à direita e, depois, no botão Modicar....

9. Nessa janela, podemos escolher o ícone desejado e também modi-car o nome da macro para exibição. Escolha um ícone da lista e digite o nome Vendas no Mês. Clique em OK.



Macros interativas

10. Observe que podemos, agora, executar a macro clicando no botão criado na Barra de Ferramentas de Acesso Rápido. Quando posi-cionamos o mouse sobre o ícone, o nome para exibição atribuído à macro é mostrado na Dica de Tela.

Silvana deseja modicar a macro criada para que, além de copiar os dados da planilha Vendas no Mês, os dados do primeiro mês na planilha Vendas sejam transferidos para a planilha Histórico. Dessa forma, ela pretende preservar os dados da planilha e criar um histórico de vendas.

Atividade 3 – Utilizando referências relativas para criar macrosObjetivo: • Utilizar referências relativas na criação de macros.

Tarefa: • Criar a macro TransfereVendas.

Em muitos casos, quando utilizamos macros, desejamos que os dados não sejam posicionados sempre nas mesmas células, mas que ocupem espaços relativos dentro da planilha. É o caso, por exemplo, de trans-ferir os dados de um local para outro. Esses dados transferidos, porém, não deverão ocupar o lugar de informações já existentes; a própria ma-cro deverá encontrar um local vazio e adequado para efetuar a movi-mentação.

A macro que será criada deverá executar os seguintes procedimentos:

• Copiar os dados referentes ao primeiro mês da planilha Vendas.

• Colar esses dados na primeira coluna vazia da planilha Histórico.

• Transferir ao dados da planilha Vendas no Mês para a planilha Vendas, formatando-a adequadamente.

1. Modique os dados da planilha Vendas no Mês para os seguintes:



Macros interativas

2. Inicie a gravação da macro TransfereVendas.

3. Selecione a planilha Vendas e recorte os dados de B3:B10.

4. Clique na planilha Histórico.

5. Pressione a tecla F5 (Ir Para) e no campo Referência digite o ende-reço XFD1. Em seguida, clique em OK.

6. Na guia Exibição, clique na ferramenta Macros e na opção Usar Referências Relativas. Certique-se de que a ferramenta esteja sele-cionada.

7. Pressione END e, depois, a tecla Seta à esquerda para selecionar a primeira célula preenchida à esquerda.

8. Pressione a tecla Seta à direita para selecionar a primeira célula vazia.

9. Cole os valores recortados.

10. Desative a opção Usar Referências Relativas.


12. Selecione a região C3:H10 e movimente-a para a região B3:G10.



Macros interativas

13. Clique na célula G3 e, pela alça de preenchimento, preencha a célula H3.

14. Selecione a planilha Vendas no Mês e recorte o intervalo B1:B7.

15. Selecione a planilha Vendas, clique na célula H4 e cole o intervalo recortado.

16. Formate a planilha adequadamente e encerre a gravação da macro.

17. Insira novos valores na planilha Vendas no Mês e execute a macro.




Macros interativas

Resumo do capítulo


Gravar uma macro interativa

Clique na seta da ferramenta Macros do grupo Macros da guia Exibição. Escolha a opção Gravar Macro...

Gravar uma macro interativa

Clique na ferramenta Gravar Macro na Barra de Status.

Parar a gravação de uma macro interativa

Clique na setinha da ferramenta Macros do grupo Macros da guia Exibição. Escolha a opção Parar gravação.

Parar a gravação de uma macro interativa

Clique na ferramenta Parar Gravação na Barra de Status.

Executar uma macro gravada

Clique na ferramenta Macros da guia Exibição. Clique sobre o nome da macro desejada.


14Controles de formulário – automação de planilhas

O B J E T I V O Automatizar planilhas usando controles de formulários e macros.




Controles de formulário – automação de planilhas

Controles de formulárioUma planilha é um tipo de formulário no qual podemos inserir, excluir ou modicar dados. As planilhas oferecem vários recursos para facilitar a entrada dos dados e sua validação, inserir comentários, imagens e outras funcionalidades típicas de formulários.

No entanto, as planilhas podem melhorar em funcionalidade e automa-ção com o uso de controles e outros objetos de desenho combinados, muitas vezes, com a utilização de macros.

Controles de formulário são elementos que possibilitam interagir com as células da planilha de forma simples, selecionando valores de uma lista, executando macros, efetuando cálculos, etc.

Para trabalhar com os controles de formulário, é necessário exibir a faixa de opções Desenvolvedor.

Atividade 1 – Mostrando a faixa de opções DesenvolvedorObjetivo: • Exibir a faixa de opções Desenvolvedor.

Tarefa: • Ativar a opção Desenvolvedor na janela Opções do Excel.

1. Na guia Arquivo, selecione Opções.

2. Na janela Opções do Excel, selecione Personalizar Faixa de Opções.

3. No quadro à direita da janela exibida, ative a opção Desenvolvedor e, a seguir, clique em OK.




4. A faixa de opções Desenvolvedor é mostrada com os seguintes grupos:

• Código: contém as ferramentas para o desenvolvimento e a uti-lização de macros e Visual Basic.

• Suplementos: permite gerenciar os suplementos disponíveis.

• Controles: permite trabalhar com os controles disponíveis.

• XML: permite gerenciar arquivos XML.

• Painel de Documentos: permite especicar o tipo de modelo do painel de informações do documento.

O Excel 2010 disponibiliza os seguintes controles de formulário:

CONTROLE NOME DESCRIÇÃO

Botão (ou botão de ação)

Quando pressionado, executa uma macro. A macro é atribuída ao botão pelo usuário.

Caixa de Combinação

Exibe uma lista suspensa de valores para que um deles seja selecionado.

Caixa de Seleção Ativa ou desativa um valor.

Botão de Rotação

Clicando nas setas, aumenta ou diminui um valor, um horário ou uma data.

Caixa de Listagem

Exibe uma lista de itens para que um deles seja escolhido.

Botão de Opção

Permite a escolha de uma única opção dentro de um conjunto de opções. Este controle está, normalmente, inserido em um controle Caixa de Grupo.

Caixa de Grupo

Agrupa controles que representam itens de conteúdos relacionados, normalmente botões de opção ou caixas de seleção.




CONTROLE NOME DESCRIÇÃO

Rótulo Permite a exibição de um texto.

Barra de Rolagem

Percorre um intervalo de valores quando as setas são clicadas.

Para acessar esses controles, clique na ferramenta Inserir, no grupo Controles, da guia Desenvolvedor.

A escola de informática Infomatik oferece cursos para as áreas de software e hardware. Os dados referentes aos programas de treinamen-to oferecidos estão em uma planilha do Excel. O diretor da escola ne-cessita desenvolver uma forma de extrair informações dessa planilha facilmente, sem que o seu funcionário necessite saber sobre ltros, ma-cros e outros procedimentos. Para isso, desenvolveu um modelo que mostra exatamente o que precisa. O nosso trabalho é ajudar o diretor a desenvolver essa ferramenta. Ele deseja que a janela em que o funcio-nário vai denir as opções de ltro seja a seguinte:




Atividade 2 – Criando um formulário para entrada e pesquisa de dadosObjetivo: • Utilizar os controles de formulário.

Tarefa: • Inserir os controles Caixa de Grupo, Botão de Opção, Caixa de Combinação, Caixa de Listagem e Caixa de Seleção em um formulário do Excel.

1. Abra a pasta Informatik.xlsx.

Nessa pasta você encontrará três planilhas:

• Formulário: onde o formulário para pesquisa e exibição dos dados será desenvolvido.

• Dados: onde estão os dados referentes aos cursos que serão manipulados.




• Filtros: planilha em que carão os ltros e os dados auxiliares.

2. Vamos colocar os controles: Caixa de Grupo, para agrupar as opções Software e Hardware, e Caixas de Opção, para a seleção das duas op-ções. Na guia Desenvolvedor, no grupo Controles, selecione Inserir. Selecione a opção Caixa de Grupo.

3. Com o mouse posicionado perto da célula B5, desenhe a caixa de grupo.

4. Com a Caixa de Grupo ainda selecionada, digite ÁREAS.




5. Selecione o controle Botão de Opção.

6. Com o mouse, desenhe o controle dentro da caixa de grupo e, ainda com o controle selecionado, digite SOFTWARE.

7. Ainda com o botão de opção selecionado, clique em Propriedades no grupo Controles.

8. Na caixa de diálogo apresentada, devemos informar em que local será armazenada a condição do botão de opção, ou seja, em que célula será armazenado o valor 1 (caso a opção Software esteja sele-cionada) ou 2 (caso a opção Software não esteja selecionada). Esse valor será armazenado na célula A3 da planilha Filtros. Dena esse endereço como Vínculo da célula, selecione Sombreamento 3D e clique em OK.




9. Repita os procedimentos 5, 6, 7 e 8 para inserir o botão de opção Hardware. A célula A3 da planilha Filtros também deve ser denida como Vínculo da célula.

Os números que representam as opções são atribuídos de acordo com a ordem de criação dos controles. Como o botão para Software foi criado em primeiro lugar, se esta for a opção selecionada, o valor que aparecerá na célula A3 da planilha Filtros será 1. Seguindo o mesmo raciocínio, como o botão para Hardware foi criado em se-gundo lugar, se esta for a opção selecionada, o valor que aparecerá na célula A3 da planilha Filtros será 2.

10. Teste o funcionamento dos botões de opção vericando o conteúdo da célula A3 da planilha Filtros.

11. O diretor da escola precisa que o funcionário informe qual é a área desejada, clicando na opção correta, e que o Excel possa ltrar os dados da planilha Dados de acordo com a opção informada. No en-




tanto, nesta planilha, a Área está identicada pelo nome (Software ou Hardware), e na célula A3 da planilha Filtros temos o número que representa a escolha efetuada. Como vamos trabalhar com l-tros, é necessário que o critério esteja de acordo com o que está ar-mazenado na fonte de dados. Clique na célula A2 da planilha Filtros e digite a fórmula:

=SE(A3=1;”SOFTWARE”;”HARDWARE”)

12. Agora, vamos colocar uma Caixa de Combinação para que seja es-colhido o assunto. A fonte para este controle (todos os assuntos) estará contida na coluna H da planilha Filtros. Copie todos os dados da coluna B da planilha Dados para a coluna H da planilha Filtros.

13. Observe que temos diversos valores repetidos. Para retirar esses va-lores repetidos, selecione toda a coluna H da planilha Filtros e, na guia Dados, no grupo Ferramentas de Dados, clique na ferramenta Remover Duplicatas.

14. Deixe selecionada a caixa colunas H e clique em OK.

15. Observe que todos os valores duplicados da lista foram eliminados. Selecione a planilha Formulário.

16. Na guia Desenvolvedor, no grupo Controles, ferramenta Inserir, se-lecione o controle Caixa de Combinação.




17. Desenhe a caixa de combinação abaixo da palavra ASSUNTO.

18. Com o controle ainda selecionado, clique em Propriedades no gru-po Controles. Informe como Intervalo de entrada a coluna H da planilha Filtros e como Vínculo da célula a célula B3 da planilha Filtros. Selecione Sombreamento 3D e clique em OK.




19. O número colocado na célula B3 da planilha Filtros corresponde à posição do item selecionado dentro da lista de itens, ou seja, cor-responde ao número da linha em que se encontra na lista. Teste o controle escolhendo um assunto e vericando o conteúdo da célula B3 da planilha Filtros.

20. Como vamos procurar o assunto selecionado na planilha Dados pelo nome e não pela posição ocupada na lista da caixa de combinações, vamos preencher a célula B2 da planilha Filtros com o nome do Assunto escolhido. Para isso, vamos utilizar a função Índice. Clique na célula B2 da planilha Filtros e digite a seguinte fórmula:

=ÍNDICE(H:H;B3;1) 21. Vamos agora colocar o controle Caixa de Listagem para a escolha

do curso desejado. Na guia Desenvolvedor, no grupo Controles, na ferramenta Inserir, selecione o controle Caixa de Listagem.

22. Logo abaixo da palavra Curso, desenhe uma caixa de listagem. 23. Com o controle ainda selecionado, clique em Propriedades no gru-

po Controles. Informe como Intervalo de entrada a coluna J da planilha Filtros e como Vínculo da célula a célula C3 da planilha Filtros. Selecione Sombreamento 3D e clique em OK.




24. Da mesma forma que procedemos para o item Assunto, vamos copiar os cursos para a coluna J da planilha Dados, removendo suas duplicatas.

25. Clique na célula C2 da planilha Filtros e digite a fórmula:

=ÍNDICE(J:J;C3;1)

26. Teste os controles efetuando as escolhas dos itens e verique na planilha Filtros.

27. Vamos agora colocar os períodos (Manhã, Tarde e Noite) usando o controle Caixa de Seleção. A caixa de seleção retorna o valor Verdadeiro se estiver selecionada e Falso se não estiver. Na guia Desenvolvedor, no grupo Controles, na ferramenta Inserir, sele-cione o controle Caixa de Seleção.

28. Desenhe a primeira caixa de seleção ao lado da caixa de listagem inserida anteriormente.

29. Com o controle ainda selecionado, digite Manhã.

30. Clique em Propriedades. Informe como Vínculo da célula a célula D3 da planilha Filtros, selecione Sombreamento 3D e clique em OK.

31. Na célula D2 da planilha Filtros, digite a fórmula:

=SE(D3=VERDADEIRO;“Sim”;“Não”)

32. Repita os procedimentos 27, 28, 29, 30 e 31 para inserir caixas de se-leção para as opções Tarde e Noite. Para a opção Tarde, coloque como vínculo da célula a célula E3 da planilha Filtros. Para a opção Noite, coloque como vínculo da célula a célula F3 da planilha Filtros.

33. Teste os controles.




A planilha foi mostrada ao diretor da escola e ele, então, fez as seguin-tes solicitações:

• Ao escolher uma área, mostrar na caixa de combinação somente os assuntos correspondentes à área selecionada.

• Ao escolher um assunto, mostrar na caixa de listagem somente os cursos referentes ao assunto selecionado.

Atividade 3 – Utilizando macros para automatizar os controles criadosObjetivo: • Utilizar macros associadas a controles de formulário.

Tarefa: • Associar macros aos controles Assuntos e Cursos para au-tomatizar as planilhas, mostrando somente as informações relacionadas.

Vamos criar a macro que gere uma lista de assuntos na coluna H (que é a fonte de dados para a caixa de combinação). Esta lista deve conter somente os assuntos relacionados à área escolhida. Para isso, vamos utilizar o recurso ltro avançado.

1. Inicie a gravação da macro.

2. Informe o nome Assunto e armazene a macro Nesta pasta de traba-lho. Clique em OK para iniciar a gravação da macro.

3. Selecione a planilha Filtros.

4. Apague os dados das colunas H e I.

5. Clique em I1 e digite ASSUNTO.

6. Clique na célula M1 (ou em qualquer outra célula longe dos dados da planilha) e, na guia Dados, no grupo Classicar e Filtrar, clique em Avançado.

7. Preencha os campos da seguinte forma:

Ative a opção Copiar para outro local.

Intervalo da lista: DADOS!$A$1:$I$53

Intervalo de critérios: FILTROS!$A$1:$A$2

Copiar para: FILTROS!$I$1

Ative a opção Somente registros exclusivos

Clique em OK.




8. Clique em I2.

9. Na guia Desenvolvedor, no grupo Código, selecione Usar Referências Relativas.

10. Pressione a tecla SHIFT e, mantendo-a pressionada, pressione as teclas END e seta para baixo simultaneamente.

11. Copie os dados.


13. Clique na célula H1 e cole os dados copiados. A seguir, pressione a tecla ESC.

14. Selecione a coluna I e apague seu conteúdo.

15. Selecione a planilha Formulário.

16. Pare a gravação da macro.

17. Observe que agora somente os assuntos relacionados à área que está marcada são mostrados na caixa de combinação. No entanto, se você mudar a seleção da área, os assuntos da caixa de combi-nação não se modicarão. Vamos, então, associar a macro criada aos botões de opção que permitem a seleção da área desejada para que, a cada mudança de seleção, a macro seja executada mostrando somente os assuntos relacionados à área na caixa de combinação.

Clique com o botão direito do mouse dentro do botão de opção Software. No menu suspenso, selecione a opção Atribuir macro...




18. Selecione a macro ASSUNTO e clique em OK.

19. Repita os procedimentos 17 e 18 para o botão de opção Hardware.

20. Modique a seleção da área e verique a lista mostrada na caixa de combinação ASSUNTO.

21. Vamos agora mostrar na caixa de listagem Cursos somente aqueles referentes ao assunto selecionado. Inicie a gravação da macro.

22.Informe o nome Curso e armazene a macro Nesta pasta de trabalho. Clique em OK para iniciar a gravação da macro.

23. Selecione a planilha Filtros.




24. Apague os dados das colunas J e K.

25. Clique em K1 e digite CURSO.

26. Clique na célula M1 (ou em qualquer outra célula longe dos dados da planilha) e, na guia Dados, no grupo Classicar e Filtrar, clique em Avançado.




Intervalo de critérios: FILTROS!$B$1:$B$2

Copiar para: FILTROS!$K$1

Ative a opção Somente registros exclusivos.

Clique em OK.

28. Clique em K2.


30. Pressione a tecla SHIFT e, mantendo-a pressionada, pressione as teclas END e seta para baixo simultaneamente.

31. Copie os dados.


33. Clique na célula J1 e cole os dados copiados. A seguir, pressione a tecla ESC.

34. Selecione a coluna K e apague seu conteúdo.

35. Selecione a planilha Formulário.





37. Vamos associar a macro criada à caixa de combinação que permite a seleção do assunto desejado para que, a cada mudança de seleção, a macro seja executada mostrando somente os cursos relacionados à área na caixa de listagem.

Clique com o botão direito do mouse dentro da caixa de combinação Assunto. No menu suspenso, selecione a opção Atribuir macro...

38. Selecione a macro Curso e clique em OK.

39. Verique o funcionamento dos controles.

Atividade 4 – Utilizando o controle Botão para efetuar a pesquisa desejadaObjetivo: • Utilizar macros associadas a controles de formulário.

Tarefa: • Associar macro a um controle Botão para retornar o(s) curso(s) desejado(s) de acordo com os dados informados nos controles criados.

Já temos todos os dados informados para que o diretor da escola pos-sa fazer sua pesquisa. Para concluir nossa tarefa, vamos utilizar uma macro associada a um controle Botão que vai executar um ltro, con-siderando os critérios construídos, e que mostre o resultado na planilha Formulário.

1. Inicialmente, vamos criar a macro que vai fornecer o resultado de-sejado. Inicie a gravação da macro.

2. Apague os dados da região B15:J21.




3. Clique na célula O15 (ou em qualquer outra célula longe dos dados da planilha) e, na guia Dados, no grupo Classicar e Filtrar, clique em Avançado.




Intervalo de critérios: FILTROS!$A$1:$F$2

Copiar para: FORMULÁRIO!$B$15

Clique em OK.


6. Na guia Desenvolvedor, no grupo Controles, na ferramenta Inserir, selecione o controle Botão.

7. Desenhe o botão abaixo da caixa de combinação ASSUNTO.

8. Atribua a macro Resultado ao botão.




9. Clique dentro do botão e modique seu nome para Pesquisa.

10. Teste o funcionamento do botão, modicando as opções de cursos.

11. Feche a pasta, salvando a planilha como Infomatik.xlsm.

Atividade 5 – Utilizando controles para cálculoObjetivo: • Utilizar os controles Barra de Rolagem e Botão de Rota-

ção para efetuar cálculos no formulário.

Tarefas: • Inserir o controle Barra de Rolagem para escolher a taxa de juros de um nanciamento.

• Inserir o controle Botão de Rotação para denir o prazo de nanciamento.

O Banco Tuiuiú deseja enviar aos seus clientes preferenciais uma pla-nilha que efetue a simulação de um nanciamento automotivo. Com isso, o gerente pretende que o cliente possa vericar em seu próprio computador valores nanciados, taxas de juros e número de parcelas. O banco deniu como leiaute da planilha o formulário mostrado abaixo. Nossa tarefa é inserir controles neste formulário para que o objetivo de simulação seja atingido.




O valor a ser nanciado será informado na célula G16. O número de parcelas será informado na célula G17 e deverá ser 12, 24, 36, 48 ou 60. A taxa de juros será informada na célula G19 e deverá estar entre 0% e 7%. O valor da parcela, que é o resultado da simulação, deverá ser mostrado na célula G23.

Abra a pasta Banco Tuiuiu.xlsx.

12. Vamos inserir o controle Botão de Rotação para denir o número de parcelas. Na guia Desenvolvedor, no grupo Controles, na ferramen-ta Inserir, selecione o controle Botão de Rotação.

13. Desenhe o botão de rotação ao lado da célula G16.

14. Com o controle selecionado, clique na opção Propriedades do gru-po Controles.




15. Digite as seguintes informações:• Valor mínimo: 12 (é o número mínimo de prestações permitido).• Valor máximo: 60 (é o número máximo de prestações permitido).• Alteração incremental: 12 (os períodos só podem variar de 12

em 12 meses).• Vínculo da célula: $G$17 (o valor escolhido cará armazenado

em G17). Clique em OK.

16. Teste o funcionamento do controle, aumentando e diminuindo o prazo de nanciamento.

17. Vamos inserir o controle Barra de Rolagem para denir a taxa de juros. Na guia Desenvolvedor, no grupo Controles, na ferramenta Inserir, selecione o controle Barra de Rolagem.




18. Desenhe a barra de rolagem abaixo da palavra Juros.

19. Com o controle selecionado, clique na opção Propriedades.

20. A barra de rolagem só aceita números inteiros como argumentos. Como o número que desejamos é um percentual, deve estar na faixa de 0% a 7% e desejamos que seja incrementado em 0,5%, vamos ter que trabalhar com os valores multiplicados por 100. Assim, o valor 7% (0,07) será representado por 700, e o incremento será represen-tado por 50. Dessa forma, digite os seguintes valores na caixa de diálogo:

• Valor mínimo: 0 – corresponde a 0%, que é a taxa mínima de juros.

• Valor máximo: 700 – corresponde a 7%, que é a taxa máxima de juros.

• Alteração incremental: 50 – corresponde a 0,5%, que é o in-cremento da taxa de juros. Indica o número de unidades que o cursor se deslocará a cada vez que as setas laterais da barra de rolagem forem clicadas.

• Mudança de página: 100 – corresponde a 1%, indicando o nú-mero de unidades que o cursor se deslocará a cada vez que o es-paço entre o cursor e uma das setas laterais da Barra de rolagem for clicado.

• Vínculo da célula: $H$19 – célula que armazenará o valor escolhido.

Clique em OK.




21. Na célula G19, vamos calcular o valor correto do percentual esco-lhido. Digite a fórmula:

=H19/10000

Formate para ser exibido no formato percentual.

22. Na célula G23, vamos colocar a fórmula para calcular o valor da parcela do nanciamento de um valor presente na célula G16, com número de parcelas indicado na célula G17 e com taxa de juros in-dicada na célula G19. Digite a fórmula:

=PGTO(G19;G17;-G16)

23. Utilize os controles criados e o valor a ser nanciado para vericar as simulações.

24. Formate a cor da fonte da célula H19 para branco.




Resumo do capítulo


Inserir Botão (ou Botão de ação)

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Botão.

Inserir Caixa de Combinação

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Caixa de Combinação.

Inserir Caixa de Seleção

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Caixa de Seleção.

Inserir Botão de Rotação

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Botão de Rotação.

Inserir Caixa de Listagem

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Caixa de Listagem.

Inserir Botão de Opção Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Botão de Opção.

Inserir Caixa de Grupo Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Caixa de Grupo.

Inserir Rótulo Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Rótulo.

Inserir Barra de Rolagem

Na guia Desenvolvedor, clique na ferramenta Inserir e selecione Barra de Rolagem.



Anotações

Anotações


Atividades complementares





1. A Universidade Grande São Paulo adotou, a partir do ano de 2010, novos procedimentos para aprovação dos alunos na matéria Estatística. Após estudos, seus dirigentes perceberam que, con-siderando somente a média das provas, os alunos aprovados não apresentavam conhecimento suciente para exercer suas pros-sões. Resolveram, então, considerar mais dois fatores para compor o conceito de aprovação: média ponderada de trabalhos propostos e frequência às aulas. Dessa forma, o critério para aprovação cou sendo o seguinte:

• Cálculo da média aritmética das provas: para ser considerado como aprovado neste quesito, o aluno tem que obter média igual ou maior que 7.

• Cálculo da média ponderada dos trabalhos: para ser considera-do como aprovado neste quesito, o aluno tem que obter média igual ou maior que 6. A média deve ser calculada como sendo a soma dos produtos das notas pelos pesos dividido pela soma dos pesos.

• Vericação de faltas: para ser considerado aprovado neste que-sito, o número de faltas do aluno deve ser igual ou menor que 25% das aulas dadas.

• Para ser aprovado na matéria, o aluno precisa ser aprovado nos três quesitos considerados.

a) Abra a pasta Universidade.xlsx.

b) Selecione a planilha Médias.

c) Calcule a média das provas.

d) Calcule a média dos trabalhos como sendo a média ponderada entre o trabalho 1 e o trabalho 2.




e) Preencha a coluna Condição provas da seguinte forma: se a mé-dia das provas for maior ou igual a 7, então colocar OK, senão colocar Média insuciente.

f) Preencha a coluna Condição trabalhos da seguinte forma: se a média dos trabalhos for maior ou igual a 6, então colocar OK, senão colocar Média insuciente.

g) Preencha a coluna Condição faltas da seguinte forma: se o núme-ro de faltas for igual ou menor que 25% do número de aulas no ano, colocar OK, senão colocar Faltas acima do limite.

h) Preencha a coluna Resultado da seguinte forma: se a média de provas for igual a OK e média de trabalhos estiver OK e faltas também for OK, então colocar “Aprovado”; caso contrário, co-locar “Reprovado”.

i) Formate condicionalmente a coluna Resultado, colocando em vermelho os reprovados e em azul, os aprovados.

j) Calcule a planilha Resumo.

2. A loja Caravelas.com decidiu exercer maior controle sobre o aten-dimento aos clientes pelo SAC. Assim, cada atendente preenche uma planilha onde são registrados data, nome do cliente, problema relatado pelo cliente, hora inicial e nal do atendimento, tempo to-tal de atendimento, dias úteis para retorno ao cliente, data prevista para o retorno e observações sobre o atendimento. Essa coluna de observações é preenchida de acordo com o tempo de atendimento. A loja instituiu que o tempo máximo de atendimento ao cliente é de 20 min. Vamos então vericar a planilha de Leonardo no mês de setembro de 2011.




a) Abra a pasta SAC.xlsx.

Nessa pasta, você encontrará as planilhas Atendimento e Feriados. A primeira planilha deve ser preenchida da seguinte forma:

b) Considere o mês de setembro de 2011.

c) Preencha a coluna Data com os dias úteis deste mês.

c) Calcule o tempo de atendimento de cada cliente.

e) Calcule a data para retorno considerando o dia do atendimento e o prazo (em dias úteis) para retorno.

f) Preencha a coluna Observações com o seguinte: se o tempo de atendimento for igual ou menor que 20 min, deve ser colocado “OK”; se o tempo de atendimento for maior do que 20 min e igual ou menor que 30 min, colocar a mensagem “Cuidado com o tempo de atendimento”; se o tempo de atendimento for maior do que 30 min, colocar a mensagem “Tempo excessivo! Reveja os procedimentos!”. Se achar necessário, insira uma coluna para calcular o total de minutos dos atendimentos.

g) Utilize a planilha Feriados para informar os feriados de setembro de 2011.

3. O banco Bomsucesso resolveu modicar os procedimentos para contemplar seus clientes com cheque especial. Dessa forma, para o próximo trimestre, será considerado o seguinte:

• O cliente só terá cheque especial se o saldo for positivo e se a média entre o saldo atual e o saldo anterior for igual ou maior do que R$ 3.000,00.

• O limite do cheque especial, para os clientes que serão contem-plados com ele, será calculado de acordo com o total de créditos




da seguinte forma: se o total de créditos do cliente for igual ou menor do que R$ 3.000,00, o limite do cheque especial será de R$ 2.000,00; caso contrário, o limite será de R$ 5.000,00.

a) Abra a pasta Banco.xlsx. Siga os seguintes procedimentos para efetuar os cálculos na planilha:

b) Calcule o saldo atual.

c) A coluna Situação deve ser preenchida com Cheque especial se o cliente for contemplado com cheque especial e Cheque normal se o cliente não tiver direito a cheque especial.

d) Preencha a coluna Limite do cheque especial considerando as regras estabelecidas para atribuir o limite.

e) Conte o número de clientes que receberam cheque especial.

4) O Pet Shop Cães & Gatos tem uma lista de animais cadastrados em que constam as seguintes informações: nome do dono, nome do ani-mal, tipo (cachorro ou gato), sexo, data de nascimento e idade. Para poder planejar promoções aos seus clientes, a loja precisa emitir um relatório no qual constem as seguintes informações:

• a quantidade de animais de cada cliente;

• a quantidade de cachorros machos e de gatas fêmeas;

• a quantidade de animais que têm menos do que 5 anos, mais do que 3 anos e mais do que 8 anos;

• a quantidade de animais aniversariantes em cada mês do ano.




a) Abra a pasta Cães e gatos.xlsx. Nessa pasta, temos duas plani-lhas: Dados e Relatório.

b) Calcule a idade de cada animal. Para isso, considere o ano da data de hoje e o ano da data de nascimento do animal.

c) Calcule o número do mês de nascimento de cada animal.

d) Preencha a planilha Relatório.

5. A loja de suprimentos Compnet vende quatro tipos de produto: pla-ca de rede, roteador, pen drive e HD externo. Ao nal de cada quin-zena a loja emite um relatório contendo a quantidade vendida de cada produto, o valor da venda por produto e os valores da maior e menor venda no período. Você deve calcular os valores do relatório para as vendas efetuadas na primeira quinzena do mês de julho de 2011 de acordo com a planilha a seguir.




a) Abra a pasta Compnet.xlxs.

b) Calcule o valor total de vendas para cada dia.

c) Calcule o número de itens vendidos por produto, o valor total das vendas por produto e a maior e menor venda.

6. A clínica Boa saúde mantém em seus registros o histórico de seus pacientes, incluindo o controle de peso. Na última atualização, a clínica decide enviar aos clientes um relatório que, entre outras in-formações, mostra como está o peso da pessoa e uma mensagem de acordo com esse estado. Os valores são avaliados de acordo com o Índice de Massa Corpórea (IMC), que indica a condição do paciente em relação ao peso e à altura. A tabela informa o seguinte:

IMC Resultado

Abaixo de 18,5 Abaixo do peso ideal

Entre 18,5 e 24,9 Peso normal

Entre 25 e 29,9 Sobrepeso

Entre 30 e 34,9 Obesidade grau I

Entre 35 e 39,9 Obesidade grau II

Acima de 40 Obesidade grau III

O IMC é calculado da seguinte forma: peso/altura2.

Preencha a planilha IMC com os resultados obtidos pela tabela.




Abra a pasta IMC.xlsx.

1. Calcule o índice de massa corpórea (IMC) de cada pessoa. Lem-bre-se de que para o cálculo de potenciação utiliza-se o caractere ̂ .

2. Preencha a coluna Resultado com a mensagem adequada de acor-do com a classicação do IMC.

7. A escola Santa Clara faz todo o controle de notas de seus alunos por meio de planilhas do Excel. No mês de dezembro de 2011, para calcular as médias gerais e conceitos na matéria Português do 6o ano, a escola trabalhou com a pasta Portugues.xlsx. Nessa pasta existem três planilhas: Boletim, Médias e Conceitos. A planilha Boletim mos-tra a média calculada dos alunos, conceitos e mensagens que devem ser impressas nos boletins. A planilha Médias armazena as notas bimestrais dos alunos e calcula suas médias. A planilha Conceitos armazena uma tabela com as faixas de médias, seus conceitos e suas mensagens. O nosso objetivo é preencher a planilha Boletim com as médias, os conceitos e as mensagens.

Abra a pasta Portugues.xlsx.

Preencha a planilha Boletim com as médias, os conceitos e as men-sagens aos alunos.

8. O mercado Tudo tem aqui tem em seu estoque produtos de diversos tipos. Cada um recebe um código que, além de identicar o produto individualmente, também identica o tipo de produto (pelo primei-ro caractere), se pertence à cesta básica (pelos três caracteres que ocupam da segunda à quarta posição) e a distribuidora (pelos dois caracteres que ocupam a quinta e a sexta posições). Preencha a pla-nilha ESTOQUE utilizando as tabelas que se encontram na planilha Tabelas da seguinte forma:

a) Coluna Tipo do Produto: deve conter o tipo do produto.

b) Coluna Cesta Básica: deve conter “Sim” se o produto pertence à cesta básica ou “Não” se não pertence.

c) Coluna Distribuidora: deve conter o nome da distribuidora.

9. A empresa Consumir.com vende produtos para quatro estados bra-sileiros: Bahia, Rio de Janeiro, Rio Grande do Sul e São Paulo. O preço do frete e a alíquota de ICM variam de acordo com o estado. A forma de pagamento varia de acordo com o volume de vendas. A empresa efetuou uma venda para o mercado Gigante, que possui liais nos quatro estados. Para consolidar a venda, a Consumir deve enviar ao mercado Gigante uma nota e um relatório que estão na pasta Consumir.xlsx.




a) Nota: deve ser preenchida com os cálculos dos valores dos pro-dutos e a forma de pagamento;

b) Critérios: planilha que contém os critérios para o cálculo das fun-ções no Relatório;

c) Relatório: planilha que deve ser preenchida, por estado, com os cálculos da soma, média, produtos de menor e maior valor, quan-tidade dos produtos de menor e maior valor e quantidade total de produtos.

10. O pet shop Cachorros e outros bichos necessita que Eliana realize outras tarefas:

a) Importar para uma planilha do Excel os nomes e as datas de nas-cimento de todos os cachorros presentes na tabela Animais_dados do banco de dados do Access Dados.accdb.

b) Importar para uma planilha do Excel os dados de todos os funcio-nários que também se encontram no mesmo banco de dados.

c) O departamento de compras efetuou uma compra de um forne-cedor que, antes de entregar a mercadoria, enviou por e-mail um relatório no formato de arquivo-texto contendo itens comprados, quantidades, preços unitários e respectivos códigos de barras. Não sabendo como proceder para transformar as informações em dados de uma planilha, Roberto solicitou a Eliana que o ajudasse nessa importação. Importe para uma planilha do Excel o arquivo de texto Compras pet shop.txt.

d) Para ter certeza de que os itens enviados pelo fornecedor provi-nham de fato das regiões informadas no ato da compra, Roberto mostra a Eliana que, por meio dos primeiros dígitos do código de barras, é possível vericar onde foram fabricados os produ-tos adquiridos. Ele diz que essa vericação evita que as pessoas sejam enganadas, comprando produtos de procedência diferen-te da anunciada. Mas, para vericar essa informação, Eliana deve procurar na internet uma tabela com esses códigos. Por meio de uma pesquisa, ela encontrou um site com a tabela com-pleta dos primeiros dígitos do código de barras e a procedência: www.tourlife.com.br/blog/?p=185.

a) Importe para uma planilha do Excel a tabela com os primeiros dígitos dos códigos de barras.

b) Grave a pasta com as planilhas elaboradas com o nome Ativi-dades pet shop.xlsx.

11. A administradora de cartões de crédito VipCard vai oferecer uma promoção aos seus associados. Para ter uma ideia exata do perl




desejado, precisa obter, a partir dos dados cadastrais, uma série de informações. Os dados considerados como principais para a emissão dos relatórios estão na pasta Vipcard.xlsx, na planilha Dados. A nossa tarefa é produzir os relatórios desejados por meio de tabelas dinâmicas.

Os relatórios desejados são os seguintes:

a) Número de associados agrupados por instrução, moradia e sexo.

b) Média de renda mensal por sexo.

c) Número de associados agrupados por viagens, sexo e instrução.

d) Número de associados por cidade e grau de instrução.

e) Número de automóveis por cidade e sexo dos associados.

Utilize, também, os recursos de segmentação de dados e grácos dinâmicos.

12. A empresa Madeira do Brasil deseja personalizar suas planilhas com cabeçalho e rodapé contendo as seguintes informações:

Cabeçalho: nome da empresa, data e número da página.

Rodapé: endereço da empresa, telefone e endereço do site.

a) Abra uma nova pasta de trabalho.

b) Crie uma macro que coloque na planilha as informações solicita-das no cabeçalho e no rodapé.

c) A macro deve car disponível para todas as planilhas.

13. A empresa Gomes Pereira solicitou que fosse desenvolvido um for-mulário para executar algumas pesquisas em seu cadastro de funcio-nários. São elas:

a) Listar todos os funcionários que trabalham em um departamento selecionado, ocupam um cargo selecionado e recebem um salário maior ou igual ao informado.

b) Listar todos os funcionários que ocupam um cargo selecionado e que têm idade superior a uma idade informada.

Você deve utilizar os controles de formulário e macros para atender ao pedido da empresa. Para isso, utilize a pasta Gomes Pereira – Formulario.xlsx.

14. Uma escola de informática necessita que sejam desenvolvidos cri-térios de classicação e ltros em um arquivo do Excel para que posteriormente sejam utilizados em pesquisa e impressão. Abra o




arquivo Cursos.xlsx e crie várias planilhas conforme a sugestão de nomes abaixo, utilizando os critérios como classicação ou ltro:

a) Cursos – Manter como planilha original que não terá alterações.

b) Maior Preço – Classicar os automóveis do maior ao menor preço.

c) Área e Cursos – Classicar em ordem crescente por Área e em seguida por Cursos.

d) Segmento Personalizado – Criar uma lista personalizada classi-cando sua planilha pela coluna Segmentos com a seguinte ordem: Vídeo, 3D e Animação, Animação, Criação de Páginas Web, Lin-guagem Web, Navegadores Web, Aplicações Web, Arquitetura, Editoração, Gerência Técnica, Gestão, Certicação, Apresenta-ção de Slides, Editor de Texto, Melhor Idade, Planilha Eletrôni-ca, Escritório, Banco de Dados, Automação de Escritório, Gene-ralista e Projetos.

e) Classicação por Cor – Classicar os cursos seguindo a ordem de cor: Vermelho, Laranja e Preto.

f) Filtro entre R$ 400,00 e R$ 500,00 – Filtrar os cursos que têm preço entre R$ 400,00 e R$ 500,00.

g) Filtro Duplo – Filtrar cursos cujo nome comece com a letra “A” ou “E” e que tenham valor maior que R$ 600,00.

h) Cursos de Março – Utilize critérios avançados para copiar dados da planilha referentes ao mês de março. Crie uma planilha com o nome de Critérios para servir de intervalo de consulta.

15. Uma agência de automóveis possui uma planilha que controla suas vendas, porém a quantidade de informações não permite uma vi-sualização adequada. Abra o arquivo Agência autos.xlsx e trabalhe com estrutura de tópicos inserindo subtotais nos meses de janeiro, fevereiro, março e vendas no trimestre a cada mudança de marca.

16. Uma empresa necessita cadastrar funcionários utilizando o Excel. Para facilitar o cadastramento, a responsável solicitou que a planilha tenha uma solução que valide os dados a serem digitados evitando diferenças em informações já utilizadas e mantendo um padrão de digitação. Para resolver o problema, utilize regras de validação de dados, conforme a seguir:

a) Abra o arquivo Cadastro funcionários.xlsx.

b) Em Escolaridade, dena uma lista de valores (até a linha 32) com os seguintes itens: Ensino Médio, Graduação, Pós-Gradua-ção e Mestrado. Utilize uma mensagem de erro não permitindo uma digitação fora da lista.




c) Em Estado Civil, crie uma lista de valores (até a linha 32) com os seguintes itens: Solteiro(a), Casado(a), Separado(a), Divorciado(a) e Viúvo(a). Utilize uma mensagem de erro não permitindo uma digitação fora da lista.

d) Em Departamento, crie uma lista de valores (até a linha 32), uti-lizando como fonte os itens da coluna H. Utilize uma mensagem de erro não permitindo uma digitação fora da lista.

e) Em Mês de Admissão, crie uma lista de valores (até a linha 32), utilizando como fonte a planilha Meses. Utilize uma mensagem de erro não permitindo uma digitação fora da lista.

17. Abra o arquivo Orçamento domestico 2.xlsx e consolide os gastos dos meses de janeiro, fevereiro e março em uma nova planilha com o nome Despesa Trimestral.

18. O arquivo Calculo do deposito.xlsx possui um valor de depósito mensal em dois anos em um caderneta de poupança levando em conta juros de 0,99% ao mês. Calcule o valor que deve ser feito de depósito para que tenha um valor no futuro de R$ 25.000,00.

19. Abra o arquivo Orçamento domestico 1.xlsx. Veja que a planilha apresentada mostra que o mês de abril apresentará um valor negati-vo de R$ 684,00. Construa dois cenários para os gastos do mês de março:

a) Corte Ameno – redução de 50% nos gastos de março em relação ao mês anterior dos itens: Energia elétrica, Telefone, Combustí-vel, Supermercado/açougue e Artigos pessoais.

b) Corte Drástico – Corte nos gastos nos itens: TV por assinatura, Empregada/faxineira, Combustível, Academia, Artigos pessoais e Passeios.

20. A empresa Egênia – produtos de informática trabalha com supri-mentos na área e necessita desenvolver uma nota scal no Excel onde possa:

a) permitir somente a digitação nos itens de quantidade, bloqueando as demais áreas da planilha (senha: notascal);

b) colocar uma senha para o acesso ao arquivo (senha: notascal).



Anotações

Anotações



Sobre os autores

Sobre os autores

Richard Martelli é pós-graduado pela PUC de Campinas no curso Educação e Gestão de Pessoas, pós-graduado pela GV no curso de Administração da Tecnologia da Informação e graduado pela Funda-ção Escola de Sociologia e Política de São Paulo como bacharel no curso de Sociologia e Política. É coordenador educacional das áreas de Aplicativos, Internet e Computação Gráca na Gerência de De-senvolvimento no Senac SP. É autor do livro Excel Básico 2010, da Editora Senac São Paulo.

Maria Silvia Mendonça de Barros possui formação em matemática pela Pontifícia Universidade Católica de São Paulo (PUC-SP). É ana-lista de sistemas, desenvolvedora de software e instrutora de Access, VBA, Lógica de Programação, Excel e outros aplicativos. Dá suporte técnico, treinamento e elabora a programação de aulas e material di-dático para cursos no Senac. É autora do livro Access 2010 da Editora Senac São Paulo.



Índice geral


Índice geral

Abrindo um arquivo com senha, 262Adicionando regras de validação (Atividade 1), 207Adicionar uma assinatura digital, 261Agrupando dados em uma planilha por meio de AutoTópicos, 247Agrupar uma estrutura de tópicos em coluna, 245Agrupar uma estrutura de tópicos em linha, 241Ajuste do monitor, IVAplicando hipóteses, 183Aplicando uma regra de validação de dados, 207Aplicando validação a dados já inseridos (Atividade 4), 215Apresentação, IAtingindo metas (Atividade 1), 183Atividades complementares, 307Atualizando dados na tabela dinâmica (Atividade 2), 136Auditoria de fórmulas, 99, 101Avaliando fórmulas (Atividade 4), 106Calculando o número de parcelas de um investimento a uma taxa de juros

e pagamentos constantes (Atividade 10), 39Calculando o valor da parcela a ser paga para um investimento

(Atividade 9), 37Calculando o valor futuro de um investimento (Atividade 7), 35Calculando o valor presente de um investimento (Atividade 8), 36Circulando dados inválidos, 217Classicando dados, 151Classicando dados (Atividade 1), 151Classicando por fonte (Atividade 4), 156Classicando por mais critérios (Atividade 2), 153Classicar e ltrar, 149Consolidando dados, 223Consolidando dados (Atividade 6), 224Consolidando dados com vínculo (Atividade 7), 228Consolidando dados em outras pastas (Atividade 8), 231Controlando as vendas da loja Tudo em Informática (Atividade 1), 11


Índice geral


Controles de formulário, 283Controles de formulário – automação de planilhas, 281Criação da tabela dinâmica (Atividade 1), 131Criando cenários, 186Criando grácos dinâmicos (Atividade 6), 145Criando macros interativas (Atividade 1), 267Criando níveis na tabela dinâmica e cruzando dados (Atividade 3), 138Criando novos grupos (Atividade 4), 139Criando regras de validação em lista de outra planilha (Atividade 3), 212Criando um formulário para entrada e pesquisa de dados (Atividade 2), 286Criando um relatório de resumo do cenário, 193Criando uma lista personalizada (Atividade 3), 155Criptografar com senha, 260Dados externos, 111, 113Denindo mensagens de dados (Atividade 2), 209Desagrupar uma estrutura de tópicos em linha, 243Editando cenários, 190Equipamento necessário, IIIEstrutura, 258Estrutura de dados, 239Estrutura do livro, VIExecutando macros (Atividade 2), 273Exibindo cenários, 189Ferramentas de dados, 205Filtrando com critério numérico, 175Filtrando com critério para um registro especíco, 174Filtrando com critérios avançados (Atividade 8), 170Filtrando com mais de um critério em uma coluna, 174Filtrando com um critério e utilizando caracteres coringas, 172Filtrando com vários conjuntos de critério para a mesma coluna, 176Filtrando dados por meio de critérios (Atividade 6), 163Filtrando dados com mais de um critério (Atividade 7), 165Filtrando dados em uma tabela (Atividade 5), 158Filtrando um conjunto de critérios com diferentes colunas, 177Filtrando um conjunto de critérios utilizando uma fórmula, 177Formatação de datas, 40Formatando datas (Atividade 11), 40Função ALEATÓRIOENTRE, 16Função ANO, 44


Índice geral


Função ARRUMAR, 77Função BDCONTARA, 94Função BDEXTRAIR, 92Função BDMÁX, 91Função BDMÉDIA, 90Função BDMÍN, 92Função BDSOMA, 88Função CONT.NÚM, 24Função CONT.SE, 25Função CONT.SES, 26Função CONT.VALORES, 23Função CONTAR.VAZIO, 24Função CORRESP, 69Função DIA, 45Função DIA.DA.SEMANA, 45Função DIATRABALHO.INTL, 47Função DIATRABALHOTOTAL.INTL, 49Função DIREITA, 80Função E, 31Função ESQUERDA, 80Função EXT.TEXTO, 80Função HOJE, 44Função HORA, 52Função ÍNDICE, 70Função MAIÚSCULA, 78Função MÁXIMO, 20Função MÉDIA, 22Função MÉDIASE, 26Função MÉDIASES, 27Função MÊS, 45Função MÍNIMO, 21Função MINÚSCULA, 78Função MINUTO, 52Função MODO.ÚNICO, 18Função MULT, 12Função NPER, 39Função ORDEM.EQ, 22Função OU, 32Função PGTO, 38


Índice geral


Função PRI.MAIÚSCULA, 76Função PROCH, 67Função PROCV, 61Função SE, 29Função SEERRO, 34Função SOMA, 11Função SOMARPRODUTO, 13Função SOMASE, 13Função SOMASES, 14Função VF, 35Função VP, 37Funções de banco de dados, 85, 87Funções de data e hora (Atividade 12), 44Funções de pesquisa, 59, 61Funções de texto, 73, 75Funções estatísticas, 17Funções nanceiras, 35Funções lógicas, 29Funções matemáticas, 11Funções matemáticas, estatísticas, lógicas, nanceiras e de data e hora, 9Gerando números aleatórios para apostas na Mega-Sena (Atividade 2), 16Gerenciador de cenários, 186Hipóteses, 181Importando arquivos de dados para o Excel (Atividade 2), 118Importando arquivos-texto para o Excel (Atividade 1), 113Importando base de dados como consulta para o Excel (Atividade 3), 120Importando dados de uma página da web (Atividade 4), 123Importando um arquivo de dados, 118Importando um arquivo-texto , 113Importando uma tabela como consulta, 120Índice geral, 323Inserindo restrição de comprimento, 220Inserindo restrição de data, 219Inserindo restrição de decimais, 222Inserindo restrição de números, 221Inserindo restrição de texto por meio de função, 223Inserindo subtotais em uma lista de dados de uma planilha, 246Inserindo uma senha, 260Instalando o Solver (Atividade 3), 195


Índice geral


Janela, 258Limpando as regras de validação, 213Limpando círculos de validação, 217Limpando ltros, 161Macros interativas, 265Marcar como nal, 260Mensagem de validação de dados, 209Mesclando cenários, 192Mostrando a faixa de opções Desenvolvedor (Atividade 1), 283Mostrando fórmulas (Atividade 5), 108Mostrando os dados da tabela dinâmica por página de relatório

(Atividade 7), 147O que é a Nova Série Informática?, IIIPersonalizando regras de validação (Atividade 5), 218Premiando os vendedores que ocupam a primeira posição em vendas e

estabelecendo conceitos sobre seu desempenho, de acordo com a meta de vendas estipulada (Atividade 5), 29

Premiando vendedores que não ocupam o primeiro lugar em vendas, mas que atingiram metas de venda estipuladas (Atividade 6), 31

Proteção, 251Protegendo elementos da planilha (Atividade 1), 253Protegendo um arquivo (Atividade 3), 259Protegendo uma planilha (Atividade 2), 257Proteger estrutura da pasta de trabalho, 261Proteger planilha atual, 261Rastreando erros em fórmulas (Atividade 3), 104Rastreando os dependentes de uma célula (Atividade 2), 103Rastreando os precedentes de uma célula (Atividade 1), 101Reaplicando ltros, 162Referência a nome de um intervalo de outra planilha , 214Referência a um intervalo de outra planilha, 212Remover uma estrutura de tópicos, 244Resolvendo um problema com o Solver (Atividade 4), 197Restringindo entrada de dados com formato hora, 215Restringindo entrada de dados com outros critérios, 219Restringir permissão por pessoas, 261Resumo do capítulo, 54, 72, 84, 97, 110, 127, 148, 179, 202, 235, 249,

263, 280, 305Retirando a senha de proteção, 262Segurança de macro, 271


Índice geral


Signicado dos símbolos, VIISobre os autores, 321Tabela dinâmica, 131Tabelas dinâmicas, 129Testando a regra de validação, 217Trabalhando com cenários (Atividade 2), 185Trabalhando com estrutura de tópicos (Atividade 1), 241Utilizando a função PROCV (Atividade 1), 61Utilizando as funções BDSOMA, BDMÉDIA, BDMÁX, BDMÍN,

BDEXTRAIR e BDCONTARA (Atividade 1), 87Utilizando as funções CORRESP e ÍNDICE (Atividade 2), 69Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO

(Atividade 2), 79Utilizando as funções PRI.MAIÚSCULA, ARRUMAR, MAIÚSCULA e

MINÚSCULA (Atividade 1), 75Utilizando controles para cálculo (Atividade 5), 300Utilizando macros para automatizar os controles criados (Atividade 3), 294Utilizando o controle Botão para efetuar a pesquisa desejada

(Atividade 4), 298Utilizando o material da Nova Série Informática, VIIUtilizando o Solver com restrições (Atividade 5), 200Utilizando referências relativas para criar macros (Atividade 3), 277Utilizando segmentação de dados (Atividade 5), 144Validação de dados, 207Vericando o desempenho dos vendedores da loja Seu Carro é Aqui

(Atividade 4), 19Vericando o número que foi sorteado com maior frequência nos sorteios

da Mega-Sena no período de 1º de janeiro de 2011 a 11 de junho de 2011 (Atividade 3), 17


Documents

Excel 2010 Avancado.pdf