Excel avancado

Info

rmát

ica

ExcelAvançado

Escola Virtual

Informática

ExcelAvançado

Escola Virtual

Ficha técnica

® Reservados todos os direitos patrimoniais e de reprodução à Fundação Bradesco

Homepage: www.fb.org.br

AUTORIA

InfoSERVER S.A.

Departamento de Treinamento

COLABORADORES

Departamento Escola Virtual - Fundação Bradesco

PROJETO GRÁFICO E REVISÃO

Setor Geração de Recursos Didáticos

PUBLICAÇÃO: 2010

3

APRESENTAÇÃO

Esta apostila compõe o material didático dos cursos de Informática referentes ao Pacote Offi ce 2007, composto pelos aplicativos Word, Excel, PowerPoint, Access, Outlook e Internet.

Na sequência, apresentamos defi nições, orientações técnicas, procedimentos e exercícios práticos, fundamentais para qualifi car profi ssionalmente jovens e adultos, para que possam ter autonomia no uso da ferramenta, dominar as competências exigidas pelo mercado de trabalho e, assim, favorecer sua empregabilidade em qualquer área ocupacional.

Lembramos que as profundas transformações ocorridas no mundo do trabalho, a velocidade da informação, a comunicação, a globalização, a difusão de novas tecnologias e de novas formas de organização do trabalho são alguns exemplos das mudanças que vêm exigindo dos trabalhadores o desenvolvimento de novas competências frente às profi ssões.

Diante desse contexto, a informática é parte fundamental dessa transformação, visto que está presente em todos os setores da sociedade: no comércio, na indústria, na saúde, na educação etc.

Enfi m, as informações aqui apresentadas servirão para alicerçar a base do conhecimento requerida para a construção das competências e habilidades propostas na qualifi cação de Operador de Microcomputador.

5

SUMÁRIO1. TRABALHANDO COM NOMES ..................................................................................11

1.1 O que é um nome? .........................................................................................11

2. ORGANIZANDO DADOS ..........................................................................................152.1 Classifi cação de dados ....................................................................................152.2 Subtotais .......................................................................................................18

2.2.1 Criando subtotais ..................................................................................192.2.2 Removendo subtotais ............................................................................22

2.3. Filtros ...........................................................................................................232.3.1 Utilizando fi ltro com critérios avançados ..................................................232.3.2 Vários critérios em uma coluna ..............................................................262.3.3 Vários critérios em várias colunas em que todos os critérios devem ser verdadeiros ...................................................................................................272.3.4 Vários critérios em várias colunas em que qualquer critério pode ser verdadeiro ....................................................................................................282.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios para várias colunas ...............................................................................................282.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios para uma coluna ..........................................................................................................292.3.7 Critérios para localizar valores de texto que compartilhem alguns caracteres mas não outros .............................................................................................30

3. TABELA DINÂMICA .................................................................................................313.1 Criar um relatório de tabela dinâmica ou gráfi co dinâmico .................................31

4. IMPORTAÇÃO DE DADOS ........................................................................................404.1 Arquivo de texto .............................................................................................40

4.1.1 Importando arquivo de texto e abrindo-o ................................................404.1.2 Importando arquivo de texto como intervalo de dados externos ...............46

4.2 Importando dados do Access ...........................................................................464.3 Importando dados de uma página Web ............................................................504.3 Atualizando dados importados .........................................................................52

5. CENÁRIOS .............................................................................................................525.1 Editando um cenário .......................................................................................575.2 Criando um relatório de cenário .......................................................................585.3 Excluindo um cenário ......................................................................................60

6. SEGURANÇA DAS INFORMAÇÕES ............................................................................616.1 Protegendo toda a planilha ..............................................................................616.2 Protegendo partes da planilha .........................................................................636.3 Protegendo uma pasta de trabalho ..................................................................656.4 Desprotegendo células e planilhas ...................................................................66

6

7. VALIDAÇÃO ............................................................................................................667.1 Criando uma validação de dados ......................................................................677.2 Inserindo uma mensagem de entrada ..............................................................737.3 Inserindo um alerta de erro .............................................................................74

8. USANDO AUDITORIA EM CÉLULAS ..........................................................................768.1 Janela de inspeção .........................................................................................76

8.1.1 Adicionando células à janela de inspeção ................................................768.2 Realizando auditoria em planilha ......................................................................78

8.2.1 Rastreando células precedentes ou dependentes .....................................798.2.2 Removendo o rastreamento ...................................................................80

9. PERSONALIZANDO A ÁREA DE TRABALHO ...............................................................809.1 Criando barra de ferramentas .........................................................................809.2 Excluindo barra de ferramentas .......................................................................829.3 Criando menu de comandos ............................................................................82

10. CONSTRUINDO UM GRUPO DE TRABALHO ............................................................8210.1 Salvando um grupo de pastas de trabalho personalizadas ................................82

11. SOLUCIONADO PROBLEMAS ..................................................................................8311.1 Problemas com uma variável .........................................................................8311.2 Problemas com mais de uma variável .............................................................8711.3 Problemas sem solução .................................................................................93

12. FUNÇÕES .............................................................................................................9512.1 Funções deTexto ...........................................................................................95

12.1.1 Função ESQUERDA ..............................................................................9512.1.2 Função DIREITA ..................................................................................9612.1.3 Função MAIÚSCULA .............................................................................9712.1.4 Função MINÚSCULA ............................................................................9812.1.5 Função PRI.MAIÚSCULA ......................................................................9812.1.6 Função LOCALIZAR .............................................................................9912.1.7 Função EXT.TEXTO ............................................................................10212.1.8 Função CONCATENAR ........................................................................103

12.2 Funções Matemáticas e Trigonométricas .......................................................10412.2.1 Função ARRED ..................................................................................10412.2.2 Função ARREDONDAR.PARA.CIMA ......................................................10512.2.3 Função ARREDONDAR.PARA.BAIXO ....................................................10612.2.4 Função INT .......................................................................................10712.2.5 Função SOMASE ................................................................................108

12.3 Funções Estatísticas ....................................................................................11212.3.1 Função CONT.NÚM ............................................................................11212.3.2 Função CONT.VALORES .....................................................................11312.3.3 Função CONTAR.VAZIO .....................................................................11512.3.4 Função CONT.SE ...............................................................................116

7

12.4 Funções de Pesquisa e Referência ................................................................11712.4.1 Função PROCH ..................................................................................11812.4.2 Função PROCV ..................................................................................12012.4.3 Função ÍNDICE .................................................................................12212.4.4 Função CORRESP ..............................................................................125

12.5 Funções de Banco de Dados ........................................................................12712.5.1 Função BDMÉDIA ..............................................................................12812.5.2 Função BDCONTAR ............................................................................12912.5.3 Função BDMÍN ..................................................................................12912.5.4 Função BDMÁX .................................................................................13012.5.5 Função BDMULTIPL ...........................................................................13112.5.6 Função BDSOMA ...............................................................................132

12.6 Funções de Informações .............................................................................13312.6.1 Função ÉERROS ................................................................................134

12.7 Funções Financeiras ....................................................................................13612.7.1 Função VF .........................................................................................13612.7.2 Função NPER ....................................................................................13812.7.3 Função PGTO ....................................................................................13912.7.4 Função VP .........................................................................................14012.7.5 Função TAXA .....................................................................................142

13. TABELA DE DADOS .............................................................................................14413.1 Tabela de dados com uma variável de entrada ..............................................14413.2 Tabela de dados com duas variáveis de entrada ............................................147

14. FORMULÁRIOS PERSONALIZADOS .......................................................................14814.1 Criando formulário ......................................................................................149

EXERCÍCIOS ............................................................................................................161

9

INTRODUÇÃO

Seja bem-vindo ao Excel 2007 – Avançado!

No curso de Excel Avançado, você aprenderá como organizar um banco de dados utilizando o conceito de relacionamento entre tabelas. Além disso, serão abordados recursos como o uso da importação de dados, cenários, segurança das informações, validação de dados, auditoria em células, funções, formulários personalizados etc.

11

1. Trabalhando com nomes

1.1 O que é um nome?

Nome é uma referência que fazemos a uma célula ou intervalo de células. Esse nome pode ser utilizado em uma fórmula ou função, dentro de qualquer planilha da pasta de trabalho. Usando nomes, você pode facilitar muito o entendimento e a manutenção das fórmulas.

Depois de adotar a prática do uso de nomes, você poderá atualizá-los, auditá-los e gerenciá-los facilmente.

Exemplo

Tipo Exemplo sem nome Exemplo com nome

Referência =SOMA(C20:C30) =SOMA(TOTALFINAL)

Constante =PRODUTO(A5,8.3) =PRODUTO(PRECO;QUANT)

Fórmula =SOMA(PROCV(A1,B1:F20,5,FALSO), —G5) =SOMA(PROCURA)

Tabela =C4:G36 =TABELAPRINCIPAL

Para nomear uma célula ou região, faça o seguinte:

1. Abra a pasta de trabalho Exemplos e selecione a planilha Nomes.

2. Clique na célula B12.

12

Figura 1

Figura 2

3. Na guia Fórmulas, grupo Nomes Defi nidos, clique em Defi nir Nome .

4. Na caixa de diálogo Novo Nome, digite o nome desejado no campo Nome e clique em OK.

Para criar um nome, algumas regras devem ser obedecidas:

• O primeiro caractere do nome deve ser letra ou underline.

• O nome pode ter até 255 caracteres.

• Não pode haver espaços.

13

Figura 3

Ao defi nirmos um nome para uma célula ou região, ele passa a ser exclusivo da pasta de trabalho, ou seja, em qualquer planilha, podemos fazer referência a ele. Por exemplo, em qualquer célula de qualquer planilha da pasta de trabalho em que você digite =Dólar, aparecerá o valor digitado para o dólar.

Agora, vamos utilizar esse nome no cálculo da célula C5.

5. Posicione o cursor na célula C5 e digite a fórmula =B5*Dólar.

6. Em seguida, é só copiar a fórmula para as demais células.

Observe que não foi necessário fi xar o endereço ao utilizar um nome.

Dica

Você pode nomear uma célula ou região rapidamente. Basta selecionar a célula ou região, clicar na caixa Nome na barra de fórmulas, digitar o nome e pressionar Enter.

14

Para visualizar todos os nomes atribuídos a células ou regiões nas planilhas, basta clicar na seta Drop Down, na caixa Nome, à esquerda na barra de fórmulas.

Figura 4

Observação

A caixa Nome, na barra de fórmulas, só pode ser utilizada para criar ou exibir nomes.

Para excluir um nome, faça o seguinte:

1. Na guia Fórmulas, grupo Nomes Defi nidos, clique no botão Gerenciador de Nomes .

2. Na caixa Gerenciador de Nomes, selecione o nome desejado e clique no botão Excluir.

15

Figura 5

3. Caso esse nome tenha sido utilizado em alguma fórmula ou função, aparecerá a seguinte mensagem de erro: #NOME?

Vamos praticar! Exercício 1

2. Organizando dados

Ao inserir dados em uma planilha, eles podem não se apresentar ordenados da maneira que você deseja visualizá-los.

Com os recursos de classifi cação e a aplicação de fi ltros, são criadas novas perspectivas para ajustar os dados às suas necessidades.

2.1 Classificação de dados

1. Abra a pasta de trabalho Exemplos e selecione planilha Subtotais.

2. Selecione o rótulo de uma coluna.

16

Figura 6

Figura 7

Figura 8

3. Na guia Dados, grupo Classifi car e Filtrar, clique em Classifi car .

4. Na caixa de diálogo Classifi car, selecione a coluna desejada, valores e ordem.

17

Figura 9

Figura 10

Você pode adicionar até 64 níveis de classifi cação por meio do botão Adicionar Nível. Veja detalhes na fi gura 9.

No botão Opções de classifi cação, você pode defi nir outras orientações de classifi cação.

18

2.2 Subtotais

Automaticamente, você pode calcular os subtotais e os totais gerais para uma coluna, usando o comando Subtotal, grupo Estrutura de Tópicos, guia Dados.

Figura 11

Os subtotais são calculados com uma função de resumo, como SOMA ou MÉDIA. Você pode exibir mais de um tipo de função de resumo para cada coluna.

Os totais gerais são derivados de dados de detalhes, e não dos valores nos subtotais. Por exemplo, se você usar a função de resumo MÉDIA, a linha de total geral exibirá uma média de todas as linhas de detalhes, e não uma média dos valores das linhas de subtotal.

Área de detalhes

Para subtotais automáticos e estruturas de tópicos de planilha, as linhas ou colunas de subtotal são totalizadas pelos dados de resumo. Em geral, os dados de detalhes estão, imediatamente, acima ou à esquerda dos dados de resumo.

19

Figura 12

2.2.1 Criando subtotais

1. Selecione o intervalo de células desejado.

Certifi que-se de que as colunas tenham um rótulo, contenham dados relacionados e que o intervalo selecionado não tenha linhas ou colunas em branco.

2. Classifi que a coluna que servirá de base para gerar o subtotal.

3. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal .

4. Na caixa de diálogo Subtotais, campo A cada alteração em, selecione a coluna a ser subtotalizada. Em nosso exemplo, é Produto.

5. No campo Usar função, clique na função que você deseja utilizar para calcular os subtotais. Em nosso exemplo, é SOMA.

6. Na caixa Adicionar subtotal a, marque a caixa de seleção de cada coluna que contenha valores a serem subtotalizados. Em nosso exemplo, é Total.

20

Figura 13

Figura 14

Veja o resultado.

21

Figura 15

7. Se você desejar uma quebra de página automática após cada subtotal, marque a caixa de seleção Quebra de página entre grupos (fi gura 15).

8. Para especifi car uma linha de resumo acima da linha de detalhes, desmarque a caixa de seleção Resumir abaixo dos dados (fi gura 15).

9. Como opção, você poderá usar o comando Subtotais novamente, repetindo as etapas de 1 a 6 para adicionar mais subtotais com funções de resumo diferentes. Para impedir que os subtotais existentes sejam sobrescritos, desmarque a caixa de seleção Substituir subtotais atuais (fi gura 15).

Dica

Para exibir um resumo somente dos subtotais e totais gerais, clique nos símbolos de estrutura de tópicos , ao lado dos números de linha. Use os símbolos e para exibir ou ocultar as linhas de detalhes dos subtotais individuais.

22

2.2.2 Removendo subtotais

Quando você remove subtotais, o Excel remove também a estrutura de tópicos e as quebras de página que você inseriu na lista juntamente com os subtotais.

1. Clique em uma célula na lista que contém um subtotal.

2. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal . A caixa de diálogo Subtotal é exibida.

3. Clique em Remover todos.

Figura 16


23

Figura 17

2.3 Filtros

Os dados fi ltrados exibem somente as linhas que atendem aos critérios especifi cados e ocultam as demais.

Depois de fi ltrar os dados, você pode copiá-los, editá-los, formatá-los, fazer gráfi co e imprimir o subconjunto de dados fi ltrados, sem reorganizá-los nem movê-los.

Você também pode fi ltrar dados por mais de uma coluna. Os fi ltros são aditivos, o que signifi ca que cada fi ltro adicional baseia-se no fi ltro atual e ainda reduz o subconjunto de dados.

2.3.1 Utilizando filtro com critérios avançados

A opção Avançado trabalha de forma diferente da opção Filtro (ambas na guia Dados, grupo Classifi car e fi ltrar) em vários aspectos:

• Exibe a caixa de diálogo Filtro Avançado em vez do menu AutoFiltro.

• Requer critérios avançados em um intervalo de critérios separados na planilha e acima do intervalo de células ou da tabela que se deseja fi ltrar. O Excel usa o intervalo de critérios separados na caixa de diálogo Filtro Avançado como fonte dos critérios avançados.

• Ao criar o intervalo para o critério, devem-se adicionar pelo menos três linhas em branco acima do intervalo a ter os dados fi ltrados, sendo que este deve ter rótulos de coluna.

Segue exemplo de planilha estruturada com intervalo de critérios e de dados a serem fi ltrados.

24

1. Abra a pasta de trabalho Exemplos e selecione a planilha FILTROS_AVANC.

Figura 18

Figura 19

2. Na célula B2, digite Flauta para selecionar esse instrumento.

3. Na guia Dados, grupo Classifi car e Filtrar, clique no botão Avançado .

4. Para fi ltrar o intervalo ocultando as linhas que não obedecem aos critérios, clique em Filtrar a lista no local.

25

Figura 20

Figura 21

Figura 22

5. Para fi ltrar o intervalo para outra área da planilha, copiando as linhas que obedecem aos critérios, clique em Copiar para outro local. E, na caixa Copiar para, indique a célula a partir da qual você deseja colar as linhas.

6. Na caixa Intervalo da lista, insira a referência do intervalo em que os dados serão fi ltrados, inclusive os rótulos.

7. Na caixa Intervalo de critérios, insira a referência do intervalo de critérios, inclusive os rótulos.

8. Clique em OK e observe o resultado.

26

Figura 23

9. Para fi ltrar novos dados, altere o valor no intervalo de critérios e fi ltre os dados novamente.

10. Para visualizar todas as linhas, clique no botão Limpar.

Ao denominar um intervalo de células como Critérios, a referência para o intervalo aparecerá na caixa Intervalo de critérios automaticamente.

Você também pode defi nir o nome Banco de dados para o intervalo de dados a ser fi ltrado e o nome Extração para a área onde você deseja colar as linhas. Esses intervalos aparecerão, automática e respectivamente, nas caixas Intervalo da lista e Copiar para.

Ao copiar linhas fi ltradas para outro local, você pode especifi car que colunas incluir na operação de cópia.

Antes de fi ltrar, copie os rótulos das colunas desejadas para a primeira linha da área onde planeja colar as linhas fi ltradas.

Ao fi ltrar, insira uma referência nos rótulos das colunas copiadas na caixa Copiar para. As linhas copiadas incluirão somente as colunas para as quais você copiou os rótulos.

2.3.2 Vários critérios em uma coluna

Lógica booleana: (Região = Norte OU Região = Nordeste)

Para localizar linhas que atendam a vários critérios para uma coluna, digite os critérios diretamente um após o outro, em linhas separadas.

27

Figura 24

Figura 25

No exemplo da fi gura 24, o intervalo de critérios fi ltrará as linhas que contêm Norte ou Nordeste na coluna Região.

2.3.3 Vários critérios em várias colunas em que todos os critérios devem ser verdadeiros

Lógica booleana: (SIGLA UF = SP E POPULAÇÃO > 10000)

Para localizar linhas que atendam a critérios em várias colunas, digite todos os critérios na mesma linha do intervalo de critérios.

No exemplo da fi gura 25, o intervalo de critérios fi ltrará as linhas que contêm SP na coluna SIGLA UF e população maior que 10.000 na coluna POPULAÇÃO.

28

2.3.4 Vários critérios em várias colunas em que qualquer critério pode ser verdadeiro

Lógica booleana: (SIGLA UF = AC OU REGIÃO = Sudeste)

Para localizar linhas que atendam a critérios em várias colunas, em que qualquer um pode ser verdadeiro, digite os critérios em linhas diferentes do intervalo de critérios.

No exemplo da fi gura 26, o intervalo de critérios fi ltrará as linhas que contêm AC na coluna SIGLA UF ou SUDESTE na coluna REGIÃO.

Figura 26

2.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios para várias colunas

Lógica booleana: ((SIGLA UF = MG E POPULAÇÃO >=300000) OU (SIGLA UF = RN E POPULAÇÃO <=250000))

Para localizar linhas que atendam a vários conjuntos de critérios, em que cada conjunto inclui critérios para várias colunas, digite cada conjunto de critérios em linhas separadas.

No exemplo da fi gura 27, o intervalo de critérios fi ltrará as linhas que contêm MG na coluna SIGLA UF e o número de população maior ou igual que 300.000 na coluna POPULAÇÃO; ou exibirá as linhas que contêm RN na coluna SIGLA UF e um valor menor ou igual a 250.000 na coluna POPULAÇÃO.

29

Figura 27

Figura 28

2.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios para uma coluna

Lógica booleana: ((POPULAÇÃO > 1000000 E < 2000000) OU (POPULAÇÃO < 1500000))

Para localizar linhas que correspondam a vários conjuntos de critérios, em que cada conjunto inclui critérios para uma coluna, inclua várias colunas para o mesmo título da coluna.

No exemplo da fi gura 28, o intervalo de critérios fi ltrará as linhas que contêm POPULAÇÃO entre 1.000.000 e 2.000.000, bem como valores menores que 1.500.000 na coluna POPULAÇÃO.

30

2.3.7 Critérios para localizar valores de texto que compartilhem alguns caracteres, mas não outros

Para localizar valores de texto que compartilhem alguns caracteres, mas não outros, siga um ou mais destes procedimentos:

Digite um ou mais caracteres para localizar linhas com valor de texto em uma coluna que inicie com tais caracteres. Por exemplo, se você digitar o texto São como critério, o Excel irá localizar “São Paulo”, “São Sebastião” e “São Pedro”.

Os seguintes caracteres curinga podem ser utilizados como critérios de comparação.

Use Para localizar

? (ponto de interrogação)

Qualquer caractere único

Por exemplo: antoni?

Localiza:“antonio” e “antonia”

* (asterisco)

Qualquer número de caracteres

Por exemplo: *este

Localiza: “Nordeste” e “Sudeste”

~ (til) seguido de ?, * ou ~

Um ponto de interrogação, asterisco ou til

Por exemplo: fy91~?

Localiza: “fy91?”

No seguinte intervalo de dados, o intervalo de critérios exibe as linhas que se iniciem com São* na coluna MUNICÍPIOS.

Figura 29


31

Figura 30

3. Tabela dinâmica

Um relatório de tabela dinâmica é utilizado para resumir, analisar, explorar e apresentar dados de resumo e para ver comparações, padrões e tendências facilmente. O relatório de tabela dinâmica permite tomar decisões corretas sobre dados críticos.

3.1 Criar um relatório de tabela dinâmica ou gráfico dinâmico

Para criar um relatório de tabela dinâmica ou gráfi co dinâmico, você deve se conectar à fonte de dados e inserir o local do relatório. Essa fonte não deve conter colunas ou linhas vazias. Por exemplo, linhas ou colunas em branco que são usadas para separar um bloco de dados de outro devem ser removidas.

1. Abra a pasta de trabalho Exemplos e selecione a planilha Tabela-Dinâmica.

2. Selecione uma célula em um intervalo de células ou coloque o ponto de inserção dentro da planilha.

32

3. Certifi que-se de que o intervalo de células tenha títulos de coluna, que, na tabela dinâmica, são chamados de campos.

Figura 31

Figura 32

Figura 33

4. Para criar um relatório de tabela dinâmica, na guia Inserir, grupo Tabelas, clique em Tabela Dinâmica .

5. Em seguida, clique em Tabela Dinâmica.

A caixa de diálogo Criar Tabela Dinâmica é exibida.

6. Clique em Selecionar uma tabela ou intervalo.

7. Digite o intervalo de células ou a referência do nome da tabela e clique em OK

33

Figura 34

Se você selecionou uma célula em um intervalo de células, ou se o ponto de inserção estava em uma tabela antes de iniciar o assistente, o intervalo de células ou a referência do nome da tabelas é exibido na caixa Tabela/Intervalo.

Como alternativa, para selecionar um intervalo de células ou uma tabela, clique em Recolher Caixa de Diálogo para ocultar a caixa de diálogo temporariamente. Selecione o intervalo na planilha e pressione Expandir Caixa de Diálogo .

8. Um relatório de tabela dinâmica vazio é criado em uma nova planilha e a caixa de diálogo Lista de campos da tabela dinâmica é aberta.

1 Área de layout do relatório de tabela dinâmica.

2 Lista de campos de tabela dinâmica.

34

Agora, você está pronto para criar o relatório de tabela dinâmica. Os campos selecionados para o relatório dependem do que você deseja saber. Por exemplo, valor total por produto. Para obter a resposta, você precisa de dados sobre os produtos e seus preços.

9. Selecione os campos Produto e Preço na caixa de seleção, na Lista de campos da tabela dinâmica.

Figura 35

Observe que você não precisa usar todos os campos da lista de campos para criar um relatório. Quando você seleciona um campo, o Excel coloca-o em uma área padrão do layout para você.

• Os dados do campo Produto, que não contêm números, são exibidos como linhas no lado esquerdo do relatório automaticamente.

• Os dados do campo Preço, que contêm números, são mostrados corretamente na área à direita.

• O título sobre os dados do produto é Rótulos de Linha. O título sobre os totais do preço é Soma de Preço. Isso ocorre porque o Excel usa a função SOMA para somar campos numéricos.

Agora, você conhece o total por produto.

10. Salve a pasta de trabalho.

35

Figura 36

Figura 37

Agora, observe que os dados de origem apresentam informações sobre os produtos em todos os países que fazem parte da planilha. Sendo assim, outra pergunta que você poderia fazer seria a seguinte: Quais são os totais dos preços dos produtos por país?

Para obter essa resposta, adicione o campo País ao relatório de tabela dinâmica como um fi ltro de relatório. Use um fi ltro de relatório para localizar um subconjunto de dados no relatório, geralmente uma linha de produto, uma duração ou uma região geográfi ca.

Para isso, faça o seguinte:

1. Clique com o botão direito do mouse sobre o campo País.

2. Selecione a opção Adicionar ao Filtro de Relatório.

3. Usando o campo País como um fi ltro de relatório, você pode ver um relatório separado por um país específi co, ou pode ver as vendas para ambos os países juntos.

36

4. Para que você possa entender melhor o exemplo, selecionaremos um país e, depois, clicaremos no botão OK. Observe o exemplo da fi gura 38:

Figura 38

Figura 39

5. Note que apenas os produtos e os preços do país escolhido estão sendo exibidos na tabela dinâmica.

37

Figura 40

Observe também que, ao lado do país selecionado, será exibido o botão Filtro , que representa que os dados abaixo foram fi ltrados segundo o dado ao lado. Essa informação também aparece na lista de campos da tabela dinâmica.

Para selecionar mais de um país, abra novamente a caixa para seleção e marque Selecionar vários itens. Marque os países que deseja fi ltrar e clique em OK.

Figura 41


38

Para utilizar dados externos, faça o seguinte:

1. Clique em Usar uma fonte de dados externa.

2. Clique em Escolher Conexão.

Figura 43

3. A caixa de diálogo Conexões Existentes é exibida.

39

Figura 44

Figura 45

Figura 46

4. Na lista suspensa Mostrar, na parte superior da caixa de diálogo, selecione a categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as Conexões, que é o padrão.

5. Selecione uma conexão a partir da caixa de listagem Selecionar uma Conexão e clique em Abrir.

6. Ao escolher uma conexão da categoria Conexões desta Pasta de Trabalho, você reutilizará ou compartilhará uma conexão existente. Ao escolher uma conexão das categorias Arquivos de conexão da rede ou Arquivos de conexão deste computador, o arquivo de conexão será copiado na pasta de trabalho como uma nova conexão de pasta de trabalho e usado como a nova conexão para o relatório de tabela dinâmica.

7. Para colocar o relatório de tabela dinâmica em uma nova planilha, começando na célula A1, clique em Nova Planilha.

8. Para colocar o relatório de tabela dinâmica em uma planilha existente, selecione Planilha Existente. Em seguida, digite a primeira célula no intervalo de células onde deseja colocar o relatório de tabela dinâmica.

40

9. Como alternativa, clique em Recolher Caixa de Diálogo para ocultar a caixa de diálogo temporariamente. Selecione a célula inicial na planilha e pressione Expandir Caixa de Diálogo .

10. Clique em OK.

4. Importação de dados

O principal benefício da conexão com dados externos (importação) é a possibilidade de analisar esses dados no Excel periodicamente, sem copiá-los repetidamente. Essa é uma operação que pode levar tempo e que está propensa a erros.

Depois de conectar-se a dados externos, você também pode, automaticamente, atualizar as pastas de trabalho a partir da fonte de dados original, sempre que ela for alterada.

4.1 Arquivo de texto

Há duas formas de importar dados de um arquivo de texto usando o Excel:

• Abrir o arquivo de texto no Excel.

• Importar o arquivo de texto como um intervalo de dados externos.

Você pode importar até 1.048.576 linhas e 16.384 colunas.

4.1.1 Importando arquivo de texto e abrindo-o

Você pode converter um arquivo de texto criado em outro programa em uma pasta de trabalho do Excel, usando o comando Abrir.

1. Clique no botão Offi ce e, em seguida, em Abrir.

2. Na caixa Arquivos do tipo, selecione Arquivos de texto.

3. Localize o arquivo de texto que deseja abrir e clique duas vezes nele.

41

Figura 47

Figura 48

Se o arquivo de texto for (.txt), o Excel iniciará o Assistente de importação de texto.

42

4. Se os itens do arquivo de texto estiverem separados por guias, dois-pontos, ponto e vírgula, espaços ou outros caracteres, selecione Delimitado, na caixa Tipo de dados originais. Se todos os itens do arquivo de texto tiverem o mesmo tamanho, selecione Largura fi xa.

5. Digite ou selecione um número de linha para especifi car a primeira linha dos dados que deseja importar na caixa Iniciar importação na linha.

Figura 49

Figura 50

Figura 51

6. Na caixa Origem do arquivo, selecione o conjunto de caracteres usado no arquivo de texto. Na maioria dos casos, você pode deixar a confi guração Windows (ANSI) como padrão.

7. A caixa de diálogo Visualização do arquivo mostra como o texto será exibido, quando separado em colunas na planilha.

43

Figura 52

Figura 53

8. Após selecionar as confi gurações, clique em Avançar.

9. A próxima tela será visualizada conforme mostra a fi gura 52.

10. Na caixa Delimitadores, selecione o caractere que separa os dados no arquivo de texto. Se o caractere não estiver listado, marque a caixa de seleção Outros. Em seguida, digite um caractere na caixa que contém o cursor. Essas opções não se encontrarão disponíveis, se o tipo de dados for Largura fi xa.

44

11. Selecione a opção Considerar delimitadores consecutivos como um só, se os dados contiverem um delimitador de mais de um caractere entre os campos de dados, ou se contiverem vários delimitadores personalizados.

Figura 54

Figura 55

12. Qualifi cador de texto – Quando o Excel encontra o caractere qualifi cador de texto, todo o texto que segue esse caractere e vem antes da próxima ocorrência desse caractere é importado como um valor, mesmo que o texto contenha um caractere delimitador. Por exemplo, se o delimitador for uma vírgula (,) e o qualifi cador de texto forem aspas (“), “São Paulo, SP” será importado em uma célula como São Paulo, SP. Se nenhum qualifi cador ou se o qualifi cador apóstrofo (‘) for especifi cado, “Dallas, Texas” será importado em duas células adjacentes como “São Paulo e SP”.

13. Após selecionar as confi gurações, clique em Avançar.

14. A tela seguinte será visualizada conforme mostra a fi gura 56.

15. Na caixa Formato dos dados da coluna, selecione o formato dos dados da coluna selecionada na seção Visualização dos dados. Se você não desejar importar a coluna selecionada, clique em Não importar coluna (Ignorar). Essa escolha fará o Excel converter os dados importados corretamente.

45

Figura 56

Figura 57

16. Clique em Concluir.

17. Faça os ajustes desejados na planilha, para que os dados importados sejam visualizados adequadamente e salve a pasta de trabalho.


46

4.1.2 Importando arquivo de texto como intervalo de dados externos

Para importar um arquivo de texto como um intervalo de dados externos, o procedimento difere da forma anteriormente vista apenas nos primeiros passos:

1. Abra a pasta de trabalho e selecione a planilha que deverá receber os dados.

2. Na guia Dados, grupo Obter dados externos, clique no botão De Texto .

3. Localize o arquivo de texto que você deseja abrir e clique duas vezes nele.

4. Repita os passos de 4 a 17 vistos no tópico anterior.

4.2 Importando dados do Access

Para importar dados do Access, faça o seguinte:

1. Na faixa de opções Dados, grupo Obter dados externos, clique no botão Do Access .

2. Na caixa de diálogo Selecionar fonte de dados, localize o arquivo do Access, selecione-o e clique no botão Abrir.

Vamos usar o banco de dados Northwind 2007, que está gravado na pasta Exemplos_curso.

47

Figura 58

Figura 59

3. Em seguida, selecione a tabela Análise de Vendas e clique em OK.

48

4. Selecione o método como os dados deverão ser exibidos. Em nosso exemplo, será o modo Tabela.

Figura 60

Figura 61

5. Informe onde você deseja armazenar seus dados. Em nosso exemplo, será Na nova planilha.

49

Figura 62

6. Clicando no botão Propriedades, você pode informar o tempo em que o Excel deverá verifi car se há novas atualizações na tabela, bem como formato de conexão, endereço de conexão, idioma.

7. Escolha sua confi guração e clique no botão OK.

50

8. Na caixa de diálogo Importar dados, clique no botão OK, para que a importação seja concluída.

Figura 63

4.3 Importando dados de uma página Web

Faça uma consulta à Web para recuperar dados atualizáveis armazenados em sua Intranet ou na Internet como uma única tabela, várias tabelas ou todo o texto de uma página da Web. Em seguida, analise os dados usando as ferramentas e os recursos do Excel. Por exemplo, você pode recuperar e atualizar cotações de ações de uma página pública da Web, ou recuperar e atualizar uma tabela de informações de vendas da página de uma empresa na Web.

Consultas à Web são especialmente úteis para recuperação de dados em tabelas ou áreas pré-formatadas.

Para criar ou editar uma consulta à Web, faça o seguinte:

1. Na guia Dados, no grupo Obter dados externos, clique em Da Web .

51

Figura 64

Figura 65

Ao ser exibida a caixa de diálogo Nova consulta à Web, insira a URL (endereço que especifi ca um protocolo, como HTTP ou FTP, e a localização de um objeto ou documento de que você deseja obter dados). É possível digitar a URL, colá-la de um endereço copiado ou clicar na seta próxima à lista Endereço e selecionar um endereço utilizado recentemente. O comprimento máximo de uma URL é de 255 caracteres.

2. Clique em Ir.

3. Clicando no botão Opções, você pode defi nir a formatação e as confi gurações de importação.

52

4. Clique no botão , ao lado da tabela que você deseja importar, ou clique no botão , no canto superior esquerdo da página, para importar a página toda.

5. Em seguida, clique no botão Importar.

Figura 66

4.3 Atualizando dados importados

Para atualizar dados importados, abra a planilha desejada e clique na guia Dados, grupo Conexões, e no botão Atualizar.

5. Cenários

Muitas vezes, há ocasiões que nos obrigam a fazer uma projeção de situações futuras para direcionar nossas decisões com a menor margem de erro possível. A utilização das planilhas eletrônicas nos permite visualizar cenários que podem se realizar ou não. Portanto, são hipóteses de acordo com os elementos que fornecemos para alimentar determinada situação.

Para criar um cenário, primeiramente, monte a planilha com todas as fórmulas e as células que contêm os valores variáveis. As que conterão valores diferentes para a mesma

53

Figura 67

Figura 68

célula serão alimentadas pelo conjunto de cenários.

A fi gura 67 mostra os valores relativos ao mês de janeiro de cada um dos três países no intervalo B8:B10.

Para os meses de fevereiro a maio, será utilizada uma fórmula que multiplica o valor de janeiro pelo índice de cada mês que está no intervalo B2:B5. Por exemplo, a fórmula do primeiro valor de fevereiro é =$B8*B$2.

A empresa tem diversas previsões sobre o aumento ou diminuição dos valores desses meses. Para visualizar a situação de acordo com as diversas hipóteses, criamos um conjunto de cenários.

Para criar um cenário, faça o seguinte:

1. Abra a pasta de trabalho Exemplos e selecione a planilha Cenario.

2. Selecione as células variáveis do cenário (B2:B5).

3. Na guia Dados, grupo Ferramentas de Dados, clique sobre a opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários.

54

4. Na caixa Gerenciador de Cenários, clique no botão Adicionar.

Figura 69

Figura 70

5. Dê um nome para o cenário, por exemplo, Baixo, e defi na quais serão as células variáveis. Automaticamente, é exibido o endereço da seleção atual.

Se desejar fazer algum Comentário, utilize a caixa correspondente.

55

Figura 71

Figura 72

8. Para criar um novo cenário, por exemplo, Alto, siga os passos 4, 5 e 6 e defi na os seguintes valores na caixa Valores de cenário: $B$2=1,8; $B$3=1,9; $B$4=2 E $B$5=2,1.

9. Para atualizar os valores nas células variáveis, clique sobre o cenário desejado e, a seguir, sobre o botão Mostrar.

O item Proteção permite que o cenário não seja alterado ou visualizado, caso a planilha esteja protegida.

6. Defi nidas as opções, clique em OK.

7. Será exibida a janela Valores de cenário com os valores das células selecionadas. Clique em OK.

56

Na fi gura 73, foi aplicado o índice utilizando cenário Baixo para exibir os resultados. Já na fi gura 74, foi aplicado o índice Alto.

Figura 73

Figura 74

57

Figura 75

5.1 Editando um cenário

Para modifi car um cenário, faça o seguinte:

1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários.

2. Clique no nome do cenário a ser alterado e, a seguir, em Editar.

3. Faça as alterações necessárias e clique em OK para alterar os valores.

58

5.2 Criando um relatório de cenário

Para criar um relatório de cenário, faça o seguinte:

1.Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários.

2. Clique no botão Resumir.

Figura 76

3. A caixa de diálogo Resumo do cenário exibe duas opções. Selecione Resumo do cenário para criar um relatório especifi cando os valores atuais das células variáveis e os valores das células de resultado, que dependem das células variáveis.

Em nosso exemplo, as células de resultado são as referentes aos valores dos meses de fevereiro a maio. Em Células de resultado, especifi que as células que deseja representar no relatório e clique em OK.

59

Figura 77

Figura 78

Na fi gura 78, veja o exemplo do relatório Resumo do cenário.

60

4. Se você selecionar Tabela Dinâmica do Cenário, uma tabela dinâmica será criada em uma nova planilha, baseada nos dados dos cenários existentes na planilha atual.

Na fi gura 79, veja o exemplo do relatório Tabela Dinâmica do Cenário.

Figura 79

Figura 80

5.3 Exclusão de um cenário

Para excluir um cenário, tenha certeza de que deseja fazê-lo, pois, a partir do momento que excluí-lo, ele não poderá ser recuperado.

1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários.

2. Selecione o cenário desejado e clique em Excluir.


61

6. Segurança das informações

Dependendo do grau de automação de um modelo de planilha, você pode desejar que os usuários não alterem nenhuma parte dela, ou tenham acesso a somente algumas de suas áreas exclusivamente para entrada de dados, não permitindo que sejam alterados rótulos ou fórmulas de cálculo.

A seguir, apresentamos alguns níveis de proteção do Excel:

• Proteger Planilha – Feita planilha por planilha, essa proteção permite alterar apenas o conteúdo das células que não estiverem travadas.

• Permitir que Usuários Editem Intervalos – Permite designar usuários, computadores da rede ou grupos que possam realizar alterações em células específi cas sem digitar uma senha.

• Proteger Pasta de Trabalho – Esse nível de proteção aplica-se à estrutura e à janela da pasta. Ao proteger a estrutura, torna-se impossível inserir ou excluir, ocultar ou reexibir, alterar o nome ou modifi car a disposição das planilhas na pasta de trabalho. Quando protegemos a janela, o tamanho e a posição da janela do arquivo fi cam inalterados.

• Proteger e Compartilhar Pasta de Trabalho – Quando uma pasta é compartilhada, ou seja, quando é aberta por vários usuários por meio da rede e se quer controlar as alterações feitas por cada usuário, é importante inserir uma senha, sem a qual não é possível remover o histórico do controle de alterações ou do uso compartilhado.

6.1 Protegendo toda a planilha

1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger. Vamos proteger a planilha Cenário-pronta.

2. Na guia Revisão, grupo Alterações, clique em Proteger Planilha .

3. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados na planilha.

62

Figura 81

4. Digite uma senha de proteção com até 255 caracteres, podendo conter letras, números e símbolos. Ao clicar em OK, será aberta uma caixa para reinserir a senha de proteção.

Cuidado

Se esquecer a senha, não há como recuperá-la.

63

Figura 82

Figura 83

6.2 Protegendo partes da planilha

1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger. Vamos proteger a planilha Nome-pronta.

2. Selecione as células que não deseja proteger (liberadas para a entrada de dados). Em nosso exemplo, será a célula B12.

3. Clique com o botão direito do mouse na célula e, em seguida, na opção Formatar células.

64

4. Ative a guia Proteção, desmarque a opção Bloqueadas e clique em OK.

5. Na guia Revisão, grupo Alterações, clique em Proteger Planilha .

6. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados na planilha.

7. Digite uma senha de proteção.

8. Agora, faça alguns testes e veja que apenas a célula B12 pode ser alterada.


65

Figura 85

Figura 86

6.3 Protegendo uma pasta de trabalho

1. Abra a pasta de trabalho que deseja proteger.

2. Na guia Revisão, grupo Alterações, clique em Proteger Pasta de Trabalho .

3. Na caixa de diálogo Proteger Estruturas e Janelas, selecione as opções desejadas para bloquear:

• Estrutura – Movimentação, exclusão e inserção de planilhas

• Janelas – Botões minimizar, maximizar e restaurar

4. Digite uma senha e, em seguida, clique em OK. A pasta de trabalho estará protegida por senha contra alterações.

5. Clique no botão Offi ce e em Salvar como.

6. Na caixa de diálogo Salvar como, clique no botão Ferramentas e, e em seguida, escolha Opções gerais.

66

7. Na área Compartilhamento de arquivos, entre com as senhas de proteção e gravação. Habilite também a opção Recomendável somente leitura e clique em OK.

Figura 87


6.4 Desprotegendo células e planilhas

Para desproteger células ou planilhas, faça o seguinte:

1. Selecione a célula ou planilha que deseja desproteger.

2. Na guia Revisão, grupo Alterações, clique em Desproteger Planilha.

7. Validação

Em muitas planilhas criadas, os usuários digitam dados para obter os resultados desejados. Por essa razão, assegurar a entrada de dados válidos é uma tarefa importante.

Por exemplo, convém restringir a entrada de dados a um determinado intervalo de datas e a escolhas usando uma lista limitada, ou certifi car-se de que apenas números inteiros positivos sejam digitados.

Fornecer ajuda imediata para orientar os usuários e mensagens claras quando dados inválidos forem digitados também é essencial para permitir que a entrada de dados aconteça de forma adequada.

67

Figura 88

7.1 Criando uma validação de dados

Uma vez decidida que validação você deseja usar em uma planilha, confi gure-a, adotando o seguinte procedimento:

1. Abra a pasta de trabalho Exemplos e selecione a planilha Vendas.

2. Selecione uma ou mais células para validar. Vamos validar o intervalo B3:M23.

3. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados.

68

4. Na guia Confi gurações, caixa Permitir, selecione a opção mais adequada.

Por exemplo, você pode selecionar Lista para limitar respostas a perguntas como:

• Mensalidade paga? Sim ou Não.

• A quantidade vendida foi: Baixa, Média, Alta.

Figura 89

Figura 90

Figura 91

5. Clique na caixa Fonte e, em seguida, digite os valores da lista separados por ponto e vírgula.

6. Em seguida, selecione a célula B3 e clique na seta para abrir a lista.

69

Figura 92

A largura da lista suspensa é determinada pela largura da célula que tem a validação de dados. Talvez seja necessário ajustar essa largura para impedir que sejam truncadas entradas válidas maiores do que a largura da lista suspensa.

Você também pode criar uma lista de valores a partir de um intervalo de células.

• Em Fonte, clique no botão que esconde temporariamente a janela.

• Selecione o intervalo de células que deseja exibir na lista.

Vejamos outros exemplos:

Para limitar a entrada a um número inteiro

1. Na caixa Permitir, selecione Número Inteiro.

2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para defi nir limite superior e inferior, selecione está entre.

3. Insira o valor mínimo, máximo ou específi co a ser permitido (fi gura 95). Você também pode inserir uma fórmula que retorne um valor numérico.

Por exemplo, para defi nir um limite mínimo de deduções para duas vezes o número de fi lhos na célula F1, selecione maior ou igual a na caixa Dados e digite a fórmula =2*F1 na caixa Mínimo.

70

Para limitar a entrada a um número decimal

1. Na caixa Permitir, selecione Decimal.

2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para defi nir limite superior e inferior, selecione entre.

3. Insira o valor mínimo, máximo ou específi co a ser permitido. Você também pode inserir uma fórmula que retorne um valor numérico.

Por exemplo, para defi nir um limite máximo para comissões e bônus de 6% do salário de um vendedor na célula E1, selecione menor ou igual a na caixa Dados e digite a fórmula =E1*6% na caixa Máximo.

Para permitir que um usuário digite porcentagens, por exemplo, 20%, selecione Decimal na caixa Permitir, selecione o tipo de restrição desejado na caixa Dados, digite o mínimo, o máximo ou um valor específi co como um decimal, por exemplo, 0,2.

Para limitar a entrada a um período de tempo (data)

1. Na caixa Permitir, selecione Data.

2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir datas após um determinado dia, selecione maior que.

3. Insira a data inicial, fi nal ou uma data específi ca a ser permitida. Você também pode inserir uma fórmula que retorne um valor de data.

Por exemplo, para defi nir um período de tempo entre a data de hoje e três dias da data de hoje, selecione entre na caixa Dados, digite =HOJE() na caixa Mínimo e digite =HOJE()+3 na caixa Máximo.

Para limitar a entrada a um intervalo de tempo (hora)

1. Na caixa Permitir, selecione Hora.

2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir horas antes de uma determinada hora do dia, selecione menor que.

3. Insira a hora inicial, fi nal ou uma hora específi ca a ser permitida. Você também pode inserir uma fórmula que retorne um valor de hora.

71

Por exemplo, para defi nir determinado período para servir o café da manhã entre a hora de abertura do restaurante, na célula H1, e cinco horas depois que o restaurante abrir, selecione entre na caixa Dados, digite =H1 na caixa Mínimo e, em seguida, digite =H1+”5:00” na caixa Máximo.

Para limitar a entrada a um texto de comprimento especifi cado

1. Na caixa Permitir, selecione Comprimento do Texto.

2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir até um determinado número de caracteres, selecione menor que ou igual a.

3. Insira o valor mínimo, máximo ou um comprimento específi co para o texto. Você também pode inserir uma fórmula que retorne um valor numérico.

Por exemplo, para defi nir que o comprimento específi co de um campo de nome completo (C1) seja o comprimento atual de um campo de primeiro nome (A1) e de um campo de sobrenome (B1) mais 10, selecione menor ou igual a na caixa Dados e digite =SOMA(NÚM.CARACT(A1),NÚM.CARACT(B1),10) na caixa Máximo.

Para calcular o que é permitido com base no conteúdo de outra célula

1. Na caixa Permitir, selecione o tipo de dado desejado.

2. Na caixa Dados, selecione o tipo de restrição desejado.

3. Na caixa ou caixas abaixo da caixa Dados, clique na célula que você deseja usar para especifi car o que é permitido.

Por exemplo, para permitir entradas em uma conta somente se o resultado não exceder o orçamento, na célula E4, selecione Decimal em Permitir, menor que ou igual a em Dados e, na caixa Máximo, digite =E4.

Usar uma fórmula para calcular o que é permitido

1. Na caixa Permitir, selecione Personalizado.

2. Na caixa Fórmula, insira uma fórmula que calcule um valor lógico (VERDADEIRO para entradas válidas ou FALSO para inválidas). Por exemplo:

72

Para assegurar que Insira esta fórmula

A célula da conta do piquenique (B1) possa ser

atualizada apenas se nada estiver orçado para

a conta sem restrições (D1) e o orçamento total

(D2) for menor do que os R$ 40.000 alocados.

=E(D1=0,D2<40000)

A célula que contém uma descrição do produto

(B2) contenha apenas texto. =ÉTEXTO(B2)

Para a célula que contém um orçamento publicitário

projetado (B3), o subtotal para subcontratantes

e serviços (E1) seja menor ou igual a R$ 800 e

a quantia total do orçamento (E2) também seja

menor ou igual a R$ 97.000.

=E(E1<=800,E2<=97000)

A célula que contém a idade de um empregado

(B4) seja sempre maior do que o número de anos

no emprego (F1) mais 18 (a idade mínima para

contratação).

=SE(B4>F1+18,VERDADEIRO,FALSO)

Todos os dados no intervalo de células A1:A20

contenham valores únicos.

=CONT.SE($A$1:$A$20,A1)=1

Você deve digitar a fórmula na validação de dados para a

célula A1 e, em seguida, preencher as células A2 a A20,

de tal modo que a validação de dados para cada célula

no intervalo tenha uma fórmula similar, mas o segundo

argumento para a função CONT.SE equivalha à célula

atual.

A célula que contém um nome de código de

produto (B5) comece sempre com o prefi xo

padrão ID- e tenha, no mínimo, dez caracteres

de comprimento.

=E(ESQUERDA(B5, 3) ="ID-",NÚM.CARACT(B5) > 9)

73

Valores Nulo

Para especifi car como você deseja lidar com valores em branco (nulos), marque ou desmarque a caixa de seleção Ignorar em branco.

Se os seus valores permitidos forem baseados em um intervalo de células com um nome defi nido e houver uma célula em branco em qualquer parte do intervalo, a defi nição da caixa de seleção Ignorar em branco permitirá que qualquer valor seja inserido na célula validada. Isso também é válido para qualquer célula referenciada por fórmulas de validação: se qualquer célula referenciada estiver em branco, a defi nição da caixa de seleção Ignorar em branco permitirá que qualquer valor seja inserido na célula validada.

7.2 Inserindo uma mensagem de entrada

É possível inserir uma mensagem para auxiliar o usuário no preenchimento da célula. Para isso, faça o seguinte:

1. Clique na guia Mensagem de entrada.

2. Certifi que-se de que a caixa de seleção Mostrar mensagem de entrada ao selecionar célula esteja marcada.

3. Preencha o título e o texto da mensagem de entrada e clique em OK.

Figura 93

74

4. Clique na seta e observe a mensagem.

Figura 94

7.3 Inserindo um alerta de erro

Além da mensagem para auxiliar o usuário a preencher os dados, você pode mostrar uma mensagem de erro, alertando-o sobre o que está ocorrendo e ainda selecionar o ícone que ele visualizará. Para isso, faça o seguinte:

1. Clique na guia Alerta de Erro e certifi que-se de que a caixa de seleção Mostrar alerta de erro após a inserção de dados inválidos esteja marcada.

2. Selecione uma destas opções para a caixa Estilo:

• Para exibir uma mensagem informativa que não impeça a entrada de dados inválidos, selecione Informações.

• Para exibir uma mensagem de aviso que não impeça a entrada de dados inválidos, selecione Aviso.

• Para impedir a entrada de dados inválidos, selecione Parar.

3. Digite o título e a mensagem de erro a ser visualizada em caso de dados inválidos (erro) e clique em OK (fi gura 95).

Se você não inserir um título ou texto, o título usará o padrão "Microsoft Excel" e a

75

Figura 95

Figura 96

mensagem usará o padrão seguinte: "O valor inserido não é válido. Outro usuário restringiu valores que podem ser inseridos nesta célula."

4. Ao digitar dados inválidos, uma caixa de diálogo será exibida em conformidade com a opção selecionada em Estilo. No exemplo anterior (Aviso), há a possibilidade de manter o valor ou rejeitá-lo.

Dica

Se você alterar a validação em uma célula, será possível aplicar, automaticamente, suas alterações em todas as outras células que têm as mesmas confi gurações.


76

8. Usando auditoria em células

Ao copiar uma fórmula de uma célula para outra, você pode não perceber que está gerando dados errados. Muitas células podem conter a mesma fórmula e, como elas mostram sempre o resultado, é difícil visualizar quais delas possuem apenas números e quais são calculadas a partir de outros dados.

Caso você faça uma alteração temporária e substitua uma fórmula por um número, esse dado pode se tornar desatualizado. Possivelmente, você não se dará conta dessa alteração, até ser questionado sobre o resultado.

Para evitar inconsistências em suas planilhas, o Excel possui ferramentas de auditoria. Elas se destinam a verifi car como estão distribuídas as dependências entre as células, e analisar possíveis erros de cálculos e entrada de dados.

8.1 Janela de inspeção

Quando as células estão ocultas em uma planilha, você pode inspecionar as fórmulas e seus resultados por meio da barra de ferramentas Janela de inspeção.

A barra de ferramentas Janela de inspeção torna conveniente inspecionar, auditar ou confi rmar os cálculos de uma fórmula e os resultados em uma planilha extensa, pois, ao utilizá-la, não é necessário navegar várias vezes para diferentes partes da planilha.

É permitida somente uma inspeção por célula.

8.1.1 Adicionando células à janela de inspeção

1. Selecione as células que você deseja inspecionar.

2. Para selecionar todas as células com fórmulas, na guia Início, no grupo Edição, clique em Localizar e Substituir, clique em Ir para Especial e, em seguida, clique em Fórmulas.

3. Na guia Fórmulas, no grupo Auditoria de fórmulas, clique em Janela de inspeção .

77

Figura 97

Figura 98

4. Clique em Adicionar inspeção de variáveis .

5. Clique em Adicionar.

6. Mova a barra de ferramentas Janela de inspeção para a parte superior, inferior, lado esquerdo ou direito da janela.

7. Para alterar a largura de uma coluna, arraste o limite no lado direito do título da coluna.

8. Para exibir a célula à qual uma entrada da barra de ferramentas Janela de inspeção se refere, clique duas vezes na entrada.

As células que têm referências externas (referência a uma célula ou a um intervalo em uma planilha de outra pasta de trabalho) são exibidas na barra de ferramentas Janela de inspeção, somente quando a outra pasta de trabalho está aberta.

78

Figura 99

8.2 Realizando auditoria em uma planilha

Às vezes, verifi car a precisão de uma fórmula ou localizar a origem de um erro pode ser difícil, quando ela usa células precedentes ou dependentes.

Células precedentes – São células que fornecem valores a outras células. Por exemplo, se a célula D10 contiver a fórmula =B5*3, a célula B5 será um precedente da célula D10.

Células dependentes – São células afetadas pelo valor de outras células. Por exemplo, se a célula D10 contiver a fórmula =B5*3, o valor da célula D10 será dependente da célula B5.

Para ajudá-lo a verifi car as fórmulas, exibindo grafi camente as relações entre células e fórmulas com setas de rastreamento, você pode usar os botões Rastrear precedentes e Rastrear dependentes . As setas rastreadoras são:

• Azuis – Quando apontam de uma célula que fornece dados para outra célula.

• Vermelhas – Quando uma célula contém um valor de erro, como #DIV/0!

• Pretas – Quando uma célula, em outra planilha ou pasta de trabalho, fi zer referência à célula selecionada, uma seta preta apontará da célula selecionada para um ícone de planilha .

Caso as setas não sejam visualizadas, faça o seguinte:

1. Clique no botão Offi ce , clique em Opções do Excel e, em seguida, clique na categoria Avançado.

2. Na seção Exibir opções para esta pasta de trabalho, verifi que se a opção Tudo está selecionada em Para objetos, mostrar.

79

Figura 100

Figura 101

Se as fórmulas fi zerem referência à outra pasta de trabalho, ela deverá estar aberta, para que o

Excel possa rastrear as células.

8.2.1 Rastreando células precedentes ou dependentes

Para rastrear células precedentes ou dependentes, faça o seguinte:

1. Selecione a célula que contém a fórmula para a qual você deseja localizar as células precedentes ou dependentes.

2. Na guia Fórmulas, grupo Auditoria de fórmulas, clique em Rastrear precedentes ou em Rastrear dependentes .

No exemplo da fi gura 101, a célula C3 tem como precedente a célula A3 e como dependente a célula A4.

80

3. Para identifi car o próximo nível de células que fornecem dados para a célula ativa, clique em Rastrear precedentes novamente.

8.2.2 Removendo o rastreamento

Para remover as setas de rastreamento, faça o seguinte:

1. Na guia Fórmulas, grupo Auditoria de fórmula, clique na seta do botão Remover Setas e, em seguida, selecione uma das opções:

Figura 102

9. Personalizando a área de trabalho

9.1 Criando barra de ferramentas

Dentre as várias ferramentas que o Excel possui, algumas fi cam visíveis, outras não. Para adicionar mais recursos à sua área de trabalho, faça o seguinte:

1. Clique com o botão direito em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.

2. Clique em Personalizar e, na lista Escolher comandos em, selecione o grupo de comando Guia Suplementos e procure por Barra de Ferramentas Personalizadas.

3. Clique no botão Adicionar.

81

Figura 103

Figura 104

4. Em seguida, clique no botão OK.

5. A barra será exibida no canto superior esquerdo da janela:

82

9.2 Excluindo barra de ferramentas

1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.

2. Selecione a barra de ferramentas desejada e clique no botão Remover.

9.3 Criando menu de comandos

1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido.

2. Em Escolher comandos em, selecione Guia Suplementos.

3. Clique na opção Comandos de Menu e no botão Adicionar.

10. Construindo um grupo de trabalho

Esse recurso permite visualizar, ao mesmo tempo, várias planilhas relacionadas e compartilhar elementos entre as pastas de trabalho e entre suas respectivas planilhas, facilitando a criação de vínculos e a consolidação de dados. Também possibilita grande economia de tempo na localização dos arquivos e no início dos trabalhos.

10.1 Salvando um grupo de pastas de trabalho personalizadas

1. Abra as pastas de trabalho que você deseja abrir como um grupo.

2. Dimensione e posicione as janelas das pastas de trabalho como deseja exibi-las na próxima vez em que forem abertas.

83

Figura 105

3. Clique na guia Exibição, grupo Janela e em Salvar Espaço de Trabalho.

4. Escolha o nome e o local do arquivo e clique no botão Salvar.

11. Solucionado Problemas

Você já se deparou com um problema que apresenta diversas soluções possíveis? Como saber qual é a melhor? Se ele envolver apenas uma variável, é simples. Já se houver mais de uma variável, a questão pode tornar-se complexa.

O Excel dispõe de excelentes ferramentas capazes de auxiliar na solução de questões complexas. Antes de utilizá-las, é necessário saber que tipo de problema você precisa resolver para, então, escolher que ferramenta utilizar.

11.1 Problemas com uma variável

Esse tipo de problema é bastante simples de ser solucionado. O que é um problema com uma variável? É aquele em que precisamos defi nir o valor de apenas um elemento para gerar o resultado de outro elemento.

84

Por exemplo, a direção de uma empresa precisa saber quanto poderá pagar de aluguel, de forma que, somando todas as despesas, esse valor não seja superior à receita. Para isso, criou uma planilha com todos os gastos e receitas do mês e montou o problema para o Excel resolver, como mostra a fi gura seguinte.

Figura 106

Primeiramente, vamos entender o problema.

É necessário saber que célula deverá variar. Célula variável é aquela que precisa ter um valor a ser defi nido. Nesse caso, é o valor do aluguel (célula B8). Outro elemento a identifi car é qual será a célula de destino, que terá seu valor modifi cado de acordo com a célula variável. Portanto, em sua fórmula, a célula de destino contém a referência da célula variável.

Nesse caso, o total da despesa (célula B9) é a célula de destino, pois esse valor depende do valor do aluguel para ser calculado. Identifi cados esses dois elementos, podemos cuidar da solução do problema.

Como esse é um problema com uma variável (o valor do aluguel), utilizamos o recurso denomina Atingir meta.

1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Atingir meta.

85

Figura 107

Figura 108

Figura 109

2. Na caixa Defi nir célula, insira a referência para a célula que contém a fórmula que você deseja resolver. No exemplo, essa é a célula B9.

3. Na caixa Para valor, digite o resultado desejado. No exemplo, esse valor é 7185.

86

4. Na caixa Alternando célula, insira a referência para a célula que contém o volume que deseja ajustar. No exemplo, essa é a célula B8.

Figura 110

Figura 111

5. Essa célula deve ser referenciada pela fórmula na célula que você especifi cou na caixa Defi nir célula.

6. Clique em OK.

7. A janela que apareceu informa se houve ou não alteração.

87

Figura 112

O resultado é o seguinte:

11.2 Problemas com mais de uma variável

Agora, vamos analisar problemas que contêm mais de uma variável. Esses problemas tanto podem ser de simples resolução, como podem apresentar elevado grau de complexidade. As questões que envolvem mais de uma variável são aquelas em que há diversos fatores a serem defi nidos para gerar um resultado.

No problema anterior, para encontrar um valor para a despesa total, havia diversas variáveis (despesas). Entretanto, somente uma variável não havia sido defi nida: o valor do aluguel.

E se for necessário saber qual o valor máximo de cada uma das despesas?

Nesse caso, teremos um problema com mais de uma variável, ou seja, todas as despesas.

Para solucionar problemas dessa natureza, o Excel dispõe de excelente recurso chamado Solver.

88

O Solver Add-in é um programa de suplemento do Excel, isto é, programa que adiciona comandos ou recursos personalizados ao Microsoft Offi ce. Contudo, para usá-lo, é preciso primeiro carregá-lo. Para isso, faça o seguinte:

1. Clique no botão Offi ce e, em seguida, em Opções do Excel.

2. Clique em Suplementos e, na caixa Gerenciar, selecione Suplementos do Excel.

3. Clique em Ir para.

4. Na caixa Suplementos disponíveis, marque a caixa de seleção Solver Add-in e clique em OK.

Dica

Se o Solver Add-in não estiver listado na caixa Suplementos disponíveis, clique em Procurar para localizá-lo. Se você for informado de que o Solver Add-in não está atualmente instalado no computador, clique em Sim para instalá-lo.

Depois de carregar o Solver Add-in, o botão Solver torna-se disponível no grupo Análise, na guia Dados.

Imagine que você tenha que distribuir uma verba orçamentária para montar um escritório e precise saber a quantidade que poderá comprar de cada item. Primeiramente, há certas exigências básicas que devem ser atendidas: será necessário adquirir, exatamente, 15 computadores e, para cada computador, serão necessárias 2 cadeiras; para cada 5 computadores, haverá pelo menos uma impressora e, para cada uma delas, pelo menos 3 mesas. O valor máximo do orçamento é R$ 50.000,00.

Veja que esse problema envolve diversas variáveis e, para cada uma, há uma restrição a ser atendida. O recurso Solver nos permite determinar restrições para as variáveis de um problema, constituindo excelente recurso para a solução de questões, uma vez que, por meio de fórmulas apenas, o processo poderia se tornar lento e cansativo.

89

Após fazer a pesquisa de preços, vamos montar a planilha.

Figura 113

Veja que, na célula B9, há o total do orçamento, defi nido como 50.000,00.

Agora, precisamos saber qual é a célula de destino, ou seja, uma célula que contém uma fórmula e que precisa ser defi nida de acordo com as células variáveis. Como temos um valor máximo a ser empregado, a célula de destino é exatamente o valor total a ser gasto na compra, que é a célula com a soma geral, cujo endereço é D6.

As células variáveis são as quantidades a serem defi nidas do intervalo C2:C5.

Elaborada a planilha com as devidas fórmulas, podemos recorrer ao Solver para solucionar o problema.

1. Clique na guia Dados, no grupo Análise e no botão Solver.

2. A primeira opção a defi nir na caixa Parâmetros do Solver é a célula de destino. Nesse caso, é D6, que é o total a ser gasto. Podemos defi nir três opções de valor para essa célula:

• Máx – Encontra o maior valor possível para a célula de destino de acordo com as restrições. Essa é a opção de nosso problema, pois precisamos encontrar o valor mais próximo da verba disponível.

90

• Mín – Encontra o menor valor possível para a célula de destino, sempre de acordo com as restrições impostas. Minimizamos quando precisamos reduzir ao máximo o valor de uma célula. Por exemplo, quando é necessário cortar despesas.

• Valor de – Permite especifi car um valor exato para a célula de destino, ou seja, quando as células variáveis precisam produzir um valor exato. Por exemplo, se você quiser saber quanto precisa economizar por mês para ter exatamente R$ 10.000,00 no fi nal do ano.

3. Células variáveis são as células que serão modifi cadas para gerar o valor defi nido para a célula de destino, que, nesse caso, são as quantidades C2:C5.

Se clicarmos em Estimar, o Solver sugere as células variáveis de acordo com a fórmula da célula de destino. Pode haver até 200 células variáveis por problema.

4. Para especifi car as restrições, ou seja, colocar as condições para as células variáveis, clique em Adicionar para abrir a caixa Adicionar restrição.

5. Nossa primeira restrição será especifi car que o número de computadores deve ser 15. Então, a referência de célula será C3. E a restrição deverá ser igual a B16, que contém o número de computadores a serem adquiridos. Veja a fi gura a seguir:

Figura 114

6. Após inserir uma restrição, clique em Adicionar para acrescentar mais restrições.

• A segunda restrição é C5=C3*B15. A quantidade de cadeiras (C5) tem que ser igual ao número de computadores (C3) vezes 2 (B15).

• A próxima é C2>=C3/B14, ou seja, a quantidade de impressoras (C2) deve ser maior ou igual ao número de computadores (C3) dividido por 5 (B14).

• A outra restrição é C4>=C2*B13. O número de mesas (C4) será maior ou igual ao número de impressoras (C2) vezes 3 (B13).

• Determinamos também que o valor total (D6) tem que ser menor ou igual ao total do orçamento (B9) pela expressão D6<=B9.

91

Figura 115

7. Após inserir a última restrição, clique em OK para retornar para a caixa Parâmetros do Solver. Todas as restrições serão listadas em Submeter às restrições.

8. Quando tudo estiver confi gurado, clique em Resolver.

9. A caixa Resultados do Solver será exibida.

Se for encontrada uma solução cujas condições foram atendidas, signifi ca que houve sucesso no processo de solução. A partir desse ponto, há duas opções:

• Manter solução do Solver – Mantém os valores das células variáveis encontrados pelo Solver. Confi rma-se o resultado.

• Restaurar valores originais – As células permanecem com os valores existentes antes de iniciar o Solver. Nada é modifi cado.

92

Figura 116

Na fi gura 116, veja que o Solver conseguir otimizar ao máximo a distribuição do orçamento de 50.000,00, atendendo a todas as restrições. Mas como comprar 12,8636 impressoras ou 38,59 mesas?

10. Nesse caso, clique em Restaurar valores originais.

Para solucionar o problema empregando o valor máximo do orçamento de 50.000,00, foi necessário fracionar os números. Nesse caso, precisamos inserir mais uma restrição: os valores de todas as células variáveis têm que ser números inteiros.

11. Para inserir mais uma restrição, clique no botão Solver novamente. As confi gurações permanecem inalteradas, até que sejam modifi cadas. Como vamos acrescentar uma restrição, clique em Adicionar e insira a seguinte restrição:

• C2:C5=número para determinar que o intervalo que contém os valores das quantidades deve ter números inteiros. Clique em OK e em Resolver.

93

Figura 117

Figura 118

12. Veja que todas as quantidades apresentam números inteiros e que todas as condições foram satisfeitas, gerando 49.950,00 como valor total.

11.3 Problemas sem solução

Há problemas cuja solução não satisfaz todas as restrições. Por exemplo, se o orçamento for de 35.000,00, será possível montar o escritório? Vamos verifi car.

Na célula B9, substituímos o valor 50.000,00 por 35.000,00 e inicializamos o Solver. Como a confi guração anterior é mantida, basta clicar em Resolver.

94

Após esgotar todas as tentativas para solucionar o problema, surge a caixa Resultados do Solver com a mensagem "Não foi possível ao Solver encontrar uma solução viável", signifi cando que a questão não foi solucionada.

Figura 119

Com isso, concluímos que o valor mínimo para adquirir todos os itens é o valor atual da célula D6, ou seja, 39.150,00.


95

12. Funções

Como já vimos nos cursos de Excel Básico e Excel Intermediário, desenvolver planilhas envolve o trabalho constante com fórmulas. Existem fórmulas em que, apenas por meio dos operadores matemáticos, podemos alcançar os resultados desejados. Porém, em muitos casos, esses recursos não são sufi cientes para produzir o resultado necessário.

Em situações dessa natureza, devemos recorrer a um dos recursos mais empregados para a geração de resultados por meio de fórmulas: as funções.

Você já aprendeu a trabalhar com algumas delas. Como você já sabe, as funções devem seguir uma sintaxe.

Por ser uma fórmula, o primeiro caractere a ser inserido é o sinal de igual (=). Depois, vem o nome da função, seguido dos seus argumentos, que devem ser colocados entre parênteses e separados por ponto e vírgula.

12.1 Funções de texto

Essa categoria contém funções que manipulam valores de texto. Há diversas aplicações para elas e seu entendimento é bastante fácil.

Vamos nos lembrar do operador que une ou concatena valores de texto, utilizado quando se trabalha com textos em fórmulas: &. Quando desejar concatenar um texto que não está em uma célula, digite-o entre aspas.

12.1.1 Função ESQUERDA

Essa função extrai caracteres a partir da esquerda até o número de caracteres especifi cado de um texto. Por exemplo, na palavra “Petróleo”, ao se extrair os três caracteres da esquerda, obtém-se “Pet”.

A sintaxe é a seguinte: =ESQUERDA(texto;[núm_caract]), onde:

• Texto – É a sequência de caracteres de texto que contém os caracteres que você deseja extrair.

• Núm_caract – Especifi ca o número de caracteres que Esquerda deve extrair. Se for omitido, será considerado 1.

96

Veja o exemplo a seguir:

Figura 120

Na fi gura 120, os códigos dos países foram formados pelos três primeiros caracteres do nome do país. A fórmula de C2 é a seguinte: =ESQUERDA(A2;3).

12.1.2 Função DIREITA

Essa função extrai os últimos caracteres de uma sequência de texto.

A sintaxe é a seguinte: =DIREITA(texto;[núm_caract]), onde:

• Texto – É a sequência de caracteres de texto que contém os caracteres que você deseja extrair.

• Núm_caract – Especifi ca o número de caracteres que Direita deve extrair. Se for omitido, será considerado 1.

97

Figura 121

Figura 122


12.1.3. Função MAIÚSCULA

Como o próprio nome sugere, essa função converte todo o texto para letras maiúsculas.

A sintaxe é a seguinte =MAIÚSCULA(texto), onde:

• Texto – É o texto que se deseja converter para maiúsculas e que pode ser uma célula ou uma sequência de caracteres de texto.


98

12.1.4. Função MINÚSCULA

Essa função converte todo o texto para letras minúsculas.

A sintaxe é a seguinte =MINÚSCULA(texto), onde:

• Texto – É o texto que se deseja converter para maiúsculas. Texto pode ser uma referência ou uma sequência de caracteres de texto.


Figura 123

12.1.5. Função PRI.MAIÚSCULA

Essa função converte o texto, deixando as iniciais de cada palavra em maiúscula e os demais caracteres em minúsculo.

A sintaxe é a seguinte: =PRI.MAIÚSCULA(texto), onde:

• Texto – É o texto entre aspas, uma fórmula que retorna o texto ou uma referência a uma célula que contenha o texto que você deseja colocar em maiúscula parcialmente.

99

Figura 124


12.1.6 Função LOCALIZAR

Essa função retorna o número da posição de um caractere em um texto, sempre da esquerda para a direita, não distinguindo maiúsculas de minúsculas. Pode ser utilizada em conjunto com outras funções de texto para retornar sequências de textos a partir de um determinado caractere.

A sintaxe é a seguinte: =LOCALIZAR(texto_procurado;no_texto;[núm_inicial]), onde:

• Texto_procurado – É o texto a ser localizado. Por exemplo, se quiser localizar a letra "s" na palavra "Pressuposto", então "s" será o texto procurado.

• No_texto – É o texto que será pesquisado. Segundo nosso exemplo, "Pressuposto" é o valor para esse argumento.

• Núm_inicial – É o caractere inicial a partir do qual será procurado um texto. Nesse exemplo, se determinarmos como 6 o número inicial, então a letra "s" será pesquisada a partir da letra "u", que é o sexto caractere. Se omitido, entende-se como 1.

Comentários

• Use LOCALIZAR para determinar o local de um caractere ou de uma sequência de caracteres de texto em outra sequência, de modo que você possa usar as funções EXT.TEXTO para alterar o texto.

100

• LOCALIZAR não diferencia maiúsculas de minúsculas. Se você não deseja uma pesquisa que diferencie maiúsculas de minúsculas ou caracteres curinga, você pode utilizar PROCURAR.

• Você pode utilizar caracteres curinga, como ponto de interrogação (?) e asterisco (*), em texto_procurado. Um ponto de interrogação corresponde a qualquer caractere; um asterisco corresponde a qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

• Se texto_procurado não for localizado, o valor de erro #VALOR! será retornado.

• Se núm_inicial for omitido, será equivalente a 1.

• Se núm_inicial não for maior do que 0 ou for maior do que o comprimento de no_texto, o valor de erro #VALOR! será retornado.

• Use núm_inicial para ignorar um número de caracteres especifi cado. Usando LOCALIZAR como exemplo, suponha que você esteja trabalhando com a sequência de caracteres de texto “ARF0093.RoupaMasculina”. Para localizar o número do primeiro “R” na parte descritiva da sequência de caracteres de texto, defi na núm_inicial como 8, para que a parte do texto relativa ao número de série não seja localizada. LOCALIZAR começa com o caractere 8, procura texto_procurado no próximo caractere e retorna o número 9. LOCALIZAR sempre retorna o número de caracteres a partir do início de no_texto, contando os caracteres ignorados, se núm_inicial for maior que 1.

Exemplo 1

Figura 125

Exemplo 2

Se o caractere procurado não for localizado, surgirá o valor de erro #VALOR!. Na fi gura seguinte, a fórmula localiza a letra "a" na célula A2, e esta procura inicia-se a partir

101

Figura 126

Figura 127

Exemplo 3

Agora, vamos extrair apenas os nomes dos países das células da coluna A. Como os nomes estão no início da sequência de texto e os códigos vêm após, iniciamos com a função ESQUERDA. O texto a ser pesquisado no primeiro caso é A2.

E o número de caracteres? Como os nomes dos países são formados por quantidades diferentes de letras, é preciso ter uma base comum para determinar esse número. Como o hífen (-) é o primeiro caractere após o nome de todos os países, ele será a base. Portanto, a partir da esquerda, vamos extrair tantos caracteres quantos houver até o hífen menos 1. Menos 1 para excluir o hífen.

Na fórmula a seguir, a função LOCALIZAR serve como argumento para Núm_caract da função ESQUERDA e tem a tarefa de retornar o número da posição do hífen. Quando encontrado esse número, subtraímos 1 para excluir o próprio hífen da extração.

do segundo caractere, pois 2 é o valor do terceiro argumento, que é Núm_inicial. Veja que, em "Argentina-54", o número é 9, pois foi localizado o "a" que está após o "n". A procura iniciou-se a partir do "r", que é o segundo caractere.

102

12.1.7 Função EXT.TEXTO

A partir da esquerda, essa função extrai determinado número de caracteres de um texto, ou de uma sequência de texto, especifi cada por um número fi nal. Por exemplo, use EXT.TEXTO para extrair a palavra "América" do texto "Brasil - América do Sul".

A sintaxe é a seguinte =EXT.TEXTO(texto; núm_inicial;núm_caract), onde:

• Texto – É a sequência de texto de onde será extraído um outro texto.

• Núm_inicial – É o número da posição do primeiro caractere a ser extraído.

• Núm_caract – É a quantidade de caracteres a serem extraídos a partir de Núm_inicial.

Comentários

• Se núm_inicial for maior do que o comprimento de texto, EXT.TEXTO retornará “” (texto vazio).

• Se núm_inicial for menor do que o comprimento de texto, mas núm_inicial e núm_caract excederem o comprimento de texto, EXT.TEXTO retornará os caracteres até o fi nal do texto.

• Se núm_inicial for menor do que 1, EXT.TEXTO retornará o valor de erro #VALOR!.

• Se núm_caract for negativo, EXT.TEXTO retornará o valor de erro #VALOR!.

Exemplo

No exemplo anterior (fi gura 127), quando extraímos o nome do país, utilizamos a função ESQUERDA. Porém, para extrair o código não basta a função DIREITA, porque o nome do país possui quantidades diferentes de caracteres, e a função LOCALIZAR sempre localiza da esquerda para a direita.

Para obter os códigos, utilizamos o resultado obtido pela função LOCALIZAR como argumento para Núm_inicial da função EXT.TEXTO.

103

Figura 128

12.1.8 Função CONCATENAR

Essa função agrupa duas ou mais cadeias de caracteres em uma única cadeia de caracteres.

A sintaxe é a seguinte: =CONCATENAR(texto1;texto2;...), onde:

• Texto1; texto2; ... – São de 2 a 255 itens de texto a serem agrupados em um único item de texto. Os itens de texto podem ser cadeia de caracteres, números ou referências a células únicas.

Dica

• Você também pode usar o operador de cálculo & (e comercial), em vez da função CONCATENAR, para agrupar itens de texto. Por exemplo, =A1&B1 retornará o mesmo valor que =CONCATENAR(A1;B1).

104

Exemplo

Figura 129

12.2 Funções Matemáticas e Trigonométricas

Essas funções executam cálculos que envolvem matemática, como Soma, Somase, funções de arredondamento etc. ou trigonometria, como Sen para calcular o seno e Cos para o cosseno entre outras.

12.2.1 Função ARRED

Quando fazemos cálculos em planilhas e obtemos um resultado com várias casas decimais, podemos exibi-los de acordo com a necessidade. É lógico, porém, que os valores internos das células consideram todas as casas decimais. Por exemplo, se o valor de uma célula é 10,988 e o formato eliminar as casas decimais, será exibido 11, embora todos os cálculos baseados nessa célula considerem 10,988.

A função ARRED arredonda um número para cima, se o dígito for maior ou igual a 5; ou para baixo, se for menor que 5, de acordo com o número de dígitos especifi cados.

A sintaxe é a seguinte: =ARRED(núm;núm_dígitos), onde:

• Núm – É o número que se deseja arredondar.

• Núm_dígitos – Especifi ca o número de dígitos para o qual você deseja arredondar núm.

105

Figura 130

Comentários

• Se núm_dígitos for maior que 0, núm será arredondado para o número especifi cado de casas decimais.

• Se núm_dígitos for 0, núm será arredondado para o inteiro mais próximo.

• Se núm_dígitos for menor que 0, núm será arredondado para a esquerda da vírgula decimal.

Exemplo

12.2.2 Função ARREDONDAR.PARA.CIMA

Essa função arredonda para cima de acordo com o número de dígitos. A sintaxe e os argumentos são os mesmos de ARRED. O número 10,941 arredondado para cima, com 2 dígitos, será 10,95.

A sintaxe é a seguinte: =ARREDONDAR.PARA.CIMA(núm;núm_dígitos), onde:

• Núm – É qualquer número real que se deseja arredondar.

• Núm_dígitos – É o número de dígitos para o qual se deseja arredondar núm.

106

Comentários

• ARREDONDAR.PARA.CIMA funciona como ARRED, com a diferença de sempre arredondar um número para cima.

• Se núm_dígitos for maior que 0, o número será arredondado para cima pelo número de casas decimais especifi cado.

• Se núm_dígitos for 0, núm será arredondado para cima até o inteiro mais próximo.

• Se núm_dígitos for menor que 0, o número será arredondado para cima, à esquerda da vírgula decimal.

Exemplo

Figura 131

12.2.3 Função ARREDONDAR.PARA.BAIXO

Como o próprio nome indica, essa função arredonda para baixo de acordo com o número de dígitos especifi cados. A sintaxe e os argumentos são os mesmos da função ARRED. O número 10.9899 arredondado para baixo, com 2 dígitos, será 10,98.

A sintaxe é a seguinte: =ARREDONDAR.PARA.BAIXO(núm;núm_dígitos), onde:

• Núm - É qualquer número real que se deseja arredondar.

• Núm_dígitos – É o número de dígitos para o qual se deseja arredondar núm.

Comentários

• ARREDONDAR.PARA.BAIXO funciona como ARRED, com a diferença de sempre arredondar um número para baixo.

107

Figura 132

Figura 133

• Se núm_dígitos for maior do que 0, o número será arredondado para baixo pelo número de casas decimais especifi cado.

• Se núm_dígitos for 0, o número será arredondado para baixo, até o inteiro mais próximo.

• Se núm_dígitos for menor do que 0, o número será arredondado para baixo, à esquerda da vírgula decimal.

Exemplo

12.2.4 Função INT

Essa função leva em consideração apenas a parte inteira do número. Por exemplo, o inteiro do número 55,001 é 55.

A sintaxe é a seguinte: =INT(núm;núm_dígitos), onde:

• Núm – É o número real que se deseja arredondar para baixo, até um inteiro.

Exemplo

108

12.2.5 Função SOMASE

Para somar os valores de um ou mais intervalos, utilizamos a função SOMA, que permite inserir até 30 intervalos ou argumentos para somar.

E para somar os valores de um intervalo de acordo com um critério específi co?

Veja o exemplo seguinte: Em uma coluna, existem vários itens repetidos, como nome do produto. Em outra coluna, há as quantidades de cada item. Como calcular a quantidade total de um item da lista?

Para solucionar questões desse tipo, utilizamos a função SOMASE, que soma os valores existentes em um intervalo segundo um critério determinado.

A sintaxe é a seguinte =SOMASE(intervalo;critérios;[intervalo_soma]), onde:

• Intervalo – É o intervalo de células que se deseja calcular por critérios. As células em cada intervalo deverão ser números e nomes, matrizes ou referências que contêm números. Os espaços em branco e os valores de texto são ignorados.

• Critérios – São os critérios na forma de um número, expressão ou texto que defi ne quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, “32”, “>32” ou “maçãs”.

• Intervalo_soma – São as células reais a serem adicionadas, se as células correspondentes no intervalo coincidirem com os critérios. Se intervalo_soma for omitido, as células no intervalo serão avaliadas pelos critérios e adicionadas, se corresponderem a eles.

Comentários

• Intervalo_soma não possui o mesmo tamanho e forma que o intervalo. As células reais que foram adicionadas são determinadas utilizando-se o intervalo_soma na célula superior, à esquerda, como a célula inicial, incluindo-se as células que correspondem ao intervalo em tamanho e forma. Por exemplo:

109

Se o intervalo for e intervalo_soma for Então, as células reais serão

A1:A5 B1:B5 B1:B5

A1:A5 B1:B3 B1:B5

A1:B4 C1:D4 C1:D4

A1:B4 C1:C2 C1:D4

• Nos critérios, você pode utilizar caracteres curinga, como ponto de interrogação (?) e asterisco (*). Um ponto de interrogação corresponde a qualquer caractere; um asterisco corresponde a qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

Exemplo

Para calcular o total das unidades da França em uma lista de países, devemos pesquisar “França” no campo País e somar os valores que estiverem na mesma linha de cada registro no campo Unidades, como mostra a fórmula da célula B22 da fi gura 134:

Figura 134

110

Onde:

B2:B18 – É o intervalo com todos os países, onde será pesquisado o item defi nido como Critérios, A22, cujo valor é o texto França. A região a ser somada ou lntervalo_soma é D2:D18, o intervalo que contém todas as quantidades.

O resultado dessa fórmula é 16, que corresponde ao total de unidades da França. Se nomearmos o intervalo B2:B18 para País e D2:D18 para Unidades, poderemos utilizar esses nomes na fórmula, que seria a seguinte:

=SOMASE(País;A22;unidades), produzindo o mesmo resultado.

Para calcular o valor total, ou seja, a soma do campo cujo rótulo é Total referente à França, utilizamos a seguinte fórmula: =SOMASE(B2:B18;A22;F2:F18). Essa fórmula contém apenas uma diferença com relação à anterior: o lntervalo_soma é F2:F18. Caso seja renomeado para Total, a fórmula também poderá ser: =SOMASE(País;A22;Total).

O critério deve estar entre aspas, quando não fi zer referência a uma célula. Se for uma referência, esse critério não pode estar entre aspas; senão, servirá como critério o próprio texto da referência e não o valor da célula.

Por exemplo, se A22 for o critério, o valor da célula é que será considerado, como nos exemplos anteriores. Porém, se o critério for “A22”, então será pesquisado o próprio texto que está entre aspas. Veja alguns exemplos de critérios:

• “B*” – Signifi ca todos os que começam com b.

• “>=10” – Para soma de valores maiores ou iguais a 10.

• “>0” – Para soma de valores maiores que 0 (zero).

• “Suécia” – Para soma de todos os registros que forem diferentes de Suécia.

• “>”&A18 – Para soma de valores maiores que o valor da célula A18.

111

Quando for omitido o valor para o argumento lntervalo_soma, o intervalo a ser somado será o especifi cado em Intervalo. A seguinte fórmula soma os totais do intervalo F2:F18 que forem maiores que 1.000:

Figura 135

Vamos praticar! Exercícios 10, 11 e 12

112

12.3. Funções Estatísticas

Essa categoria contém funções que fazem cálculos estatísticos. Algumas delas você já conheceu no curso Básico, como Média, que retorna a média aritmética; Máximo, que retorna o maior valor existente em uma lista de valores; Mínimo, que retorna o menor valor.

12.3.1. Função CONT.NÚM

Essa função conta quantas células contêm números e os números na lista de argumentos. Use CONT.NÚM para obter o número de entradas em um campo de número que esteja em um intervalo ou matriz de números.

A sintaxe é a seguinte: =CONT.NÚM(valor1;valor2;...), onde:

• Valor1; valor2 ... – São argumentos de 1 a 255 que contêm diferentes tipos de dados ou a eles se referem, mas somente os números são contados.

Comentários

• Os argumentos que são números, datas ou representações de números por extenso são contados.

• Os valores lógicos e as representações de números por extenso digitados diretamente na lista de argumentos são contados.

• Os argumentos que são valores de erro ou texto que não possam ser convertidos em números são ignorados.

• Se um argumento for uma matriz ou referência, somente os números dessa matriz ou referência serão contados. Células vazias, valores lógicos, texto ou valores de erro da matriz ou referência são ignorados.

• Se você deseja contar valores lógicos, texto ou valores de erro, use a função CONT.VALORES.

113

Figura 136

Exemplo

12.3.2 Função CONT.VALORES

Essa função calcula o número de células não vazias e os valores na lista de argumentos. Use CONT.VALORES para calcular o número de células com dados em um intervalo ou matriz.

A sintaxe é a seguinte: =CONT.VALORES(valor1;valor2;...), onde:

• Valor1; valor2;... – São argumentos de 1 a 255 que representam os valores que você deseja calcular.

114

Comentários

• Um valor é qualquer tipo de informação, incluindo valores de erro e texto vazio (“”). Um valor não inclui células vazias.

• Se um argumento for uma matriz ou referência, somente os valores dessa matriz ou referência serão usados. As células vazias e os valores de texto da matriz ou referência são ignorados.

• Se você não precisa calcular valores lógicos, texto ou valores de erro, use a função CONT.NÚM.

Exemplo

Figura 137

115

Figura 138

12.3.3 Função CONTAR.VAZIO

Essa função conta o número de células vazias no intervalo especifi cado.

A sintaxe é a seguinte: =CONTAR.VAZIO(intervalo), onde:

• Intervalo – É o intervalo em que as células em branco serão contadas.

Células com fórmulas que retornam “” (texto vazio) também são contadas. Células com valores nulos não são contadas.

Exemplo

116

12.3.4 Função CONT.SE

Essa função calcula o número de células não vazias em um intervalo que corresponde a determinados critérios.

A sintaxe é a seguinte: =CONT.SE(intervalo;critérios), onde:

• Intervalo – É uma ou mais células para contar, incluindo números ou nomes, matrizes ou referências que contêm números. Os campos em branco e valores de texto são ignorados.

• Critérios – É o critério na forma de um número, expressão, referência de célula ou texto que defi ne que células serão contadas. Por exemplo, os critérios podem ser expressos como 32, “32”, “>32”, “maçãs” ou B4.

Exemplo 1

Figura 139

117

Figura 140

Exemplo 2

Fórmulas CONT.SE utilizando caracteres curinga e manipulando valores

12.4 Funções de Pesquisa e Referência

Essa categoria é composta por funções que localizam valores em uma lista ou retornam sua referência. Existem 18 funções nessa categoria. Dentre elas, vamos conhecer algumas de grande utilidade no desenvolvimento de planilhas inteligentes.

A seguir, você conhecerá as funções PROCH, PROCV, ÍNDICE e CORRESP. Em muitos casos, elas trabalham em conjunto, proporcionando excelentes resultados.

118

12.4.1 Função PROCH

Essa função localiza um valor na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especifi cada na tabela ou matriz.

Use PROCH quando seus valores de comparação estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específi co de linhas mais abaixo.

A sintaxe é a seguinte: =PROCH(valor_procurado;matriz_tabela;núm_índice_lin;[procurar_intervalo]), onde:

• Valor_procurado – É o valor a ser localizado na primeira linha da tabela. Valor_procurado pode ser um valor, uma referência ou uma sequência de caracteres de texto.

• Matriz_tabela – É uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo.

– Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos.

– Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO. Em caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, a matriz_tabela não precisará ser ordenada.

– Textos em maiúsculas e minúsculas são equivalentes.

– Classifi que os valores em ordem crescente, da esquerda para a direita. Para obter mais informações, consulte Classifi car dados.

• Núm_índice_lin – É o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela e assim por diante.

Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!.

119

• Procurar_intervalo – É um valor lógico que especifi ca se você quer que PROCH localize uma correspondência exata ou aproximada. Se VERDADEIRO (1) ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado. Se FALSO (0), PROCH encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado.

Comentários

• Se PROCH não localizar valor_procurado e procurar_intervalo for VERDADEIRO, PROCH usará o maior valor que é menor do que o valor_procurado.

• Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_tabela, PROCH retornará o valor de erro #N/D.

• Se procurar_intervalo for FALSO e valor_procurado for texto, você poderá usar os caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

Exemplo

Figura 141

120

12.4.2 Função PROCV

Essa função localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela.

O V em PROCV signifi ca vertical. Use PROCV em vez de PROCH, quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você deseja procurar.

A sintaxe é a seguinte: =PROCV(valor_procurado;matriz_tabela;núm_índice_col;[procurar_intervalo]), onde:

• Valor_procurado – É o valor a ser procurado na primeira coluna da matriz da tabela (Matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.). O valor_procurado pode ser um valor ou uma referência. Se o valor_procurado for menor do que o menor valor da primeira coluna de matriz_tabela, o PROCV retornará o valor de erro #N/D.

• Matriz_tabela – São duas ou mais colunas de dados. Use uma referência para um intervalo ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes.

• Núm_índice_col – É o número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela, um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela e assim por diante. Se núm_índice_coluna for:

– Menor que 1, PROCV retornará o valor de erro #VALOR!.

– Maior que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!.

• Procurar_intervalo – É um valor lógico que especifi ca se você quer que PROCV localize uma correspondência exata ou aproximada.

– Se VERDADEIRO ou omitido, uma correspondência exata ou aproximada é retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado.

– Os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente. Em caso contrário, PROCV poderá não retornar o valor correto. Para obter mais informações, consulte Classifi car dados.

121

– Se FALSO, PROCV encontrará somente uma correspondência exata. Nesse caso, os valores na primeira coluna da matriz_tabela não precisam ser classifi cados. Se houver dois ou mais valores na primeira coluna de matriz_tabela que não coincidam com o valor_procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata for localizada, o valor de erro #N/D será retornado.

Comentários

• Ao procurar valores de texto na primeira coluna da matriz_tabela, certifi que-se de que os dados da primeira coluna da matriz_tabela não tenham espaços à esquerda ou de fi m de linha, uso inconsistente de aspas normais (‘ou“) e curvas (‘ou“) ou caracteres não imprimíveis. Nesses casos, a função PROCV pode fornecer um valor correto ou não esperado. Para obter mais informações, consulte Tirar e Arrumar.

• Ao procurar valores de número ou data, certifi que-se de que os dados da primeira coluna da matriz_tabela não estejam armazenados como valores de texto. Nesse caso, a função PROCV pode fornecer um valor correto ou não esperado. Para obter mais informações, consulte Converter números armazenados como texto em números.

• Se procurar_intervalo for FALSO e valor_procurado for texto, você poderá usar os caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

Exemplo

Figura 142


122

12.4.3 Função ÍNDICE

Essa função retorna um valor ou a referência para um valor de dentro de uma tabela ou intervalo. Há duas formas da função ÍNDICE: matricial e de referência.

Se quiser Consulte

Retornar o valor de uma célula ou matriz de células especifi cadas Forma matricial

Retornar a referência para células especifi cadas Forma de referência

Forma matricial

Retorna o valor de um elemento em uma tabela ou matriz selecionadas pelos índices de número de linha e coluna (Matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.).

Use a forma de matriz se o primeiro argumento de ÍNDICE for uma constante de matriz.

A sintaxe é a seguinte: =INDICE(matriz;núm_linha;núm_coluna), onde:

• Matriz – É um intervalo de células ou uma constante de matriz.

– Se a matriz contiver apenas uma linha ou coluna, o argumento núm_linha ou núm_coluna correspondente será opcional.

– Se a matriz tiver mais de uma linha e mais de uma coluna e apenas núm_linha ou núm_coluna for usado, ÍNDICE retornará uma matriz referente à linha ou coluna inteira da matriz.

• Núm_linha – Seleciona a linha na matriz a partir da qual um valor deverá ser retornado. Se núm_linha for omitido, núm_coluna será obrigatório.

• Núm_coluna – Seleciona a coluna na matriz a partir da qual um valor deverá ser retornado. Se núm_coluna for omitido, núm_linha será obrigatório.

123

Comentários

• Se os argumentos núm_linha e núm_coluna forem usados, ÍNDICE retornará o valor contido na célula que estiver no ponto de interseção entre núm_linha e núm_coluna.

• Se você defi nir núm_linha ou núm_coluna como 0 (zero), ÍNDICE retornará a matriz de valores referente à coluna ou linha inteira respectivamente. Para usar valores retornados como uma matriz, insira a função ÍNDICE como uma fórmula de matriz (Fórmula de matriz: fórmula que executa vários cálculos em um ou mais conjuntos de valores e retorna um único resultado ou vários resultados. As fórmulas de matriz fi cam entre chaves { } e são inseridas pressionando-se CTRL+SHIFT+ENTER.) em um intervalo horizontal de células para uma linha e em um intervalo vertical de células para uma coluna. Para inserir uma fórmula de matriz, pressione CTRL+SHIFT+ENTER.

• Núm_linha e núm_coluna devem fazer referência a uma célula dentro de uma matriz. Em caso contrário, ÍNDICE retornará o valor de erro #REF!.

Exemplo

Figura 143

Forma de referência

Retorna a referência da célula na interseção de linha e coluna específi cas. Se a referência for formada por seleções não adjacentes, você poderá escolher a seleção que deseja observar.

A sintaxe é a seguinte: =INDICE(ref;núm_linha;núm_coluna;núm_área), onde:

124

• Ref – É uma referência a um ou mais intervalos de célula.

– Se você estiver inserindo um intervalo não adjacente para a ref, coloque ref entre parênteses.

– Se cada área na referência contiver apenas uma linha ou coluna, o argumento núm_linha ou núm_coluna, respectivamente, será opcional. Por exemplo, para uma referência de linha única, use ÍNDICE(ref;núm_coluna).

• Núm_linha – É o número da linha em ref de onde será fornecida uma referência.

• Núm_coluna – É o número da coluna em ref de onde será fornecida uma referência.

• Núm_área – Seleciona um intervalo em ref do qual deve ser retornada a interseção de núm_linha com núm_coluna. A primeira área selecionada ou inserida recebe o número 1, a segunda recebe o número 2 e assim por diante. Se núm_área for omitido, ÍNDICE usará a área 1.

– Por exemplo, se ref descrever as células (A1:B4;D1:E4;G1:H4), então núm_área 1 representará o intervalo A1:B4, núm_área 2 representará o intervalo D1:E4 e núm_área 3 representará o intervalo G1:H4.

Comentários

• Depois que ref e núm_área tiverem selecionado um intervalo específi co, núm_linha e núm_coluna selecionam uma célula específi ca: núm_linha 1 é a primeira linha do intervalo, núm_coluna 1 é a primeira coluna e assim por diante. A referência que ÍNDICE retorna é a interseção entre núm_linha e núm_coluna.

• Se você defi niu núm_linha ou núm_coluna como 0, ÍNDICE retorna a referência para a coluna ou linha inteira respectivamente.

• Núm_linha, núm_coluna e núm_área devem apontar para uma célula na referência; senão, ÍNDICE retornará o valor de erro #REF!. Se núm_linha e núm_coluna forem omitidos, ÍNDICE retornará a área em referência especifi cada por núm_área.

• O resultado da função ÍNDICE é uma referência e é interpretado como tal por outras fórmulas. Dependendo da fórmula, o valor retornado por ÍNDICE pode ser usado como uma referência ou como um valor. Por exemplo, a fórmula de macro CÉL(“largura”;ÍNDICE(A1:B2;1;2)) é equivalente a CÉL(“largura”;B1). A função CÉL usa o valor retornado por ÍNDICE como uma referência de célula. Por outro lado, uma fórmula tal como 2*ÍNDICE(A1:B2;1;2) traduz o valor retornado por ÍNDICE no número da célula B1.

125

Exemplo

Figura 144

12.4.4 Função CORRESP

Essa função retorna a posição relativa de um item em uma matriz que coincide com um valor determinado em uma ordem específi ca (Matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.). Use CORRESP em vez de uma das funções PROC, quando precisar da posição de um item em um intervalo em lugar do item propriamente dito.

A sintaxe é a seguinte: =CORRESP(valor_procurado;matriz_procurada;[tipo_correspondência]), onde:

• Valor_procurado – É o valor utilizado para localizar o valor desejado em uma tabela.

126

• Matriz_procurada – É um intervalo contíguo de células que contém valores possíveis de procura. Matriz_procurada precisa ser uma matriz ou uma referência de matriz.

• Tipo_correspondência – É o número -1, 0 ou 1. Tipo_correspondência especifi ca como o Microsoft Excel corresponde a valor_procurado com os valores contidos em matriz_procurada.

– Se tipo_correspondência for 1, CORRESP localizará o maior valor que for menor que ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem ascendente: ...-2, -1, 0, 1, 2, ...A-Z, FALSO, VERDADEIRO.

– Se tipo_correspondência for 0, CORRESP localizará o primeiro valor que for exatamente igual a valor_procurado. Matriz_procurada pode ser colocada em qualquer ordem.

– Se tipo_correspondência for -1, CORRESP localizará o menor valor que seja maior ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem decrescente: VERDADEIRO, FALSO, Z-A, ...2, 1, 0, -1, -2, ... e assim por diante.

– Se tipo_correspondência for omitido, será equivalente a 1.

Comentários

• CORRESP retorna a posição do valor coincidente em matriz_procurada e não o valor propriamente dito. Por exemplo: CORRESP(“b”;{”a”.”b”.”c”};0) retorna 2, a posição relativa de “b” na matriz {”a”.”b”.”c”}.

• CORRESP não faz distinção entre letras maiúsculas e minúsculas, quando estiver fazendo a correspondência entre valores de texto.

• Se CORRESP não conseguir localizar um valor coincidente, ele fornecerá o valor de erro #N/D.

• Se tipo_correspondência for 0 e valor_procurado for um texto, você poderá utilizar caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um ponto de interrogação corresponde a qualquer caractere; um asterisco corresponde a qualquer sequência de caracteres. Se você quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

127

Exemplo

Figura 145

12.5 Funções de Banco de Dados

No trabalho com planilhas, é comum termos que saber qual é o valor total de um ou mais itens de uma lista ou banco de dados.

Você já se deparou com uma situação em que precisava saber qual era a quantidade vendida ou comprada de um produto em determinado período, ou qual foi o maior valor pago por uma mercadoria específi ca em um ano, mês ou semana, ou em qualquer outro período?

Situações como essas e muitas outras envolvendo banco de dados são frequentes no dia a dia do desenvolvimento de planilhas. Para resolver questões desse gênero, existem as funções de Banco de Dados. Para utilizar uma dessas funções, é necessário haver uma lista ou banco de dados e uma região de critérios, que podem estar na mesma planilha ou em outras planilhas e até mesmo em pastas (arquivos) diferentes.

As funções de Banco de Dados BDMÉDIA, BDCONTAR, BDMÍN, BDMÁX, BDSOMA e BDVARP possuem os mesmos argumentos, descritos a seguir:

• Banco de dados – É o intervalo de células da lista ou do banco de dados. Um banco de dados é uma lista de dados relacionados, cujas linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna.

128

• Campo – Indica a coluna que será usada na função. O campo pode ser estabelecido como texto com o rótulo da coluna entre aspas, como “Idade” ou “Rendimento”, ou como um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante.

• Critérios – Intervalo de células que contém as condições especifi cadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e pelo menos uma célula abaixo do rótulo de coluna para especifi car uma condição para a coluna.

12.5.1 Função BDMÉDIA

Essa função calcula a média dos valores em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especifi cadas.

A sintaxe a seguinte: =BDMÉDIA(banco-dados;campo;critérios)

Exemplo

O exemplo abaixo calcula média utilizando o campo Total do produto Relógio e país Itália.

Figura 146

129

Figura 147

12.5.2 Função BDCONTAR

Essa função conta as células que contêm números em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especifi cadas.

O argumento de campo é opcional. Se o campo for omitido, BDCONTAR contará todos os registros no banco de dados que atendam aos critérios.

A sintaxe é a seguinte: =BDCONTAR(banco-dados;campo;critérios)

Exemplo

O exemplo seguinte conta o campo Unitário referente aos produtos que comecem com C* e países que comecem com I*.

12.5.3 Função BDMÍN

Essa função retorna o menor número em um campo (coluna) de registros em uma lista ou banco de dados que atenda às condições especifi cadas.

A sintaxe é a seguinte: =BDMÍN(banco-dados;campo;critérios)

130

Exemplo

O exemplo seguinte exibe o menor valor do campo Total de acordo com os critérios: produtos que comecem com R* do país Argentina.

Figura 148

12.5.4 Função BDMÁX

Essa função retorna o maior número em um campo (coluna) de registros em uma lista ou banco de dados que atenda às condições especifi cadas.

A sintaxe é a seguinte: =BDMÁX(banco-dados;campo;critérios)

Exemplo

O exemplo seguinte exibe o maior valor do campo Total de acordo com os critérios: produtos que comecem com R* do país Argentina.

131

Figura 149

12.5.5 Função BDMULTIPL

Essa função multiplica os valores em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especifi cadas.

A sintaxe é a seguinte: =BDMULTIPL(banco-dados;campo;critérios)

Exemplo

O exemplo seguinte multiplica os dados que estão no campo QTDE de acordo com os critérios: produto Relógio e todos os países que começam com J*.

132

Figura 150

12.5.6 Função BDSOMA

Essa função adiciona os números em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especifi cadas.

A sintaxe é a seguinte: =BDSOMA(banco-dados;campo;critérios)

Exemplo

O exemplo seguinte soma os valores que estão no campo Unitário para os critérios: produtos que começam com C* do país Inglaterra.

133

Figura 151

12.6 Funções de Informações

Você já deve ter se deparado com valores de erro em fórmulas.

Esses valores podem ser tratados por funções de informações, que permitem testar valores e retornam VERDADEIRO, se o valor testado corresponder ao tipo de informação procurado pela função. As funções dessa categoria verifi cam diversos tipos de erros.

Na tabela a seguir, veja os erros que podem ser gerados nas fórmulas e sua origem.

Erros Ocorrência

##### Coluna não é larga o bastante ou quando é usada uma data ou hora negativa.

#DIV/0! Número é dividido por zero (0).

#N/A Valor não está disponível para uma função ou fórmula.

#NOME? O Microsoft Offi ce Excel não reconhece o texto em uma fórmula.

#NULL!Especifi ca uma interseção de duas áreas que não se interceptam. O operador de

interseção é um espaço entre referências.

134

Erros Ocorrência

#NUM! Valores numéricos inválidos em uma fórmula ou função.

#REF!

Referência de célula não é válida. (Referência de célula é o conjunto de

coordenadas que a célula abrange em uma planilha. Por exemplo, a referência da

célula que aparece na interseção da coluna B e linha 3 é B3.)

#VALOR!

Usado o tipo errado de argumento ou operando. (Argumento são os valores que

uma função usa para executar operações ou cálculos. O tipo de argumento que

uma função usa é específi co à função. Os argumentos comuns usados em funções

incluem números, texto, referências de célula e nomes./ Operando são itens nos

dois lados de um operador em uma fórmula. No Excel, os operandos podem ser

valores, referências de célula, nomes, rótulos e funções.)

A seguir, vamos conhecer uma das funções de informações que verifi ca a ocorrência de um valor de erro.

12.6.1 Função ÉERROS

Essa função retorna VERDADEIRO, se o valor testado retornar qualquer tipo de erro.

A sintaxe é a seguinte: =ÉERROS(valor), onde:

• Valor – É o valor a ser testado. Pode ser uma fórmula, uma célula ou um nome.

Exemplo

Se dividirmos um número por 0 (zero), o resultado da fórmula será #DIV/0!. A fórmula a seguir retorna VERDADEIRO, se o valor da célula B1 for um erro; ou FALSO, caso o erro não exista.

Figura 152

135

Figura 153

Figura 154

Se você estiver perguntando sobre o que fazer com o resultado VERDADEIRO, veja a fórmula na imagem 153.

Essa fórmula contém a função SE com o teste lógico éerros (B1). Se o teste for VERDADEIRO, surgirá o texto “Valor Inválido”, chamando atenção para o erro. Caso o teste lógico seja FALSO, isto é, se o valor de B1 não for um erro, surgirá o texto “Certo”.

Como o retorno de ÉERROS sempre será um valor lógico, não é necessário especifi car, no teste lógico da função SE, a comparação por meio de um operador lógico, que deixaria a fórmula da seguinte maneira:

=SE( ÉERROS (B1)=Verdadeiro; “Valor Inválido”; “Certo” )

Observe que as duas fórmulas produzem o mesmo resultado. Agora, é preciso fazer desaparecer o “#DIV/0!” de B1, que, atualmente, contém a fórmula =A1/A2. Para isso, no lugar do B1 da fórmula que trata o erro, inserimos o próprio cálculo que causa o erro, que é A1/A2, e transferimos toda a fórmula para a célula B1, que fi cará da seguinte maneira:

=SE(ÉERROS(A1/A2) ; “Valor Inválido”; “Certo”)

Retornando “Valor Inválido”, caso o valor de A1 dividido por A2 seja um erro; e “Certo”, se o erro não ocorrer, o que será possível se A2 não for O (zero), como mostra a fi gura a seguir.

136

Mas a questão é a seguinte: Qual o resultado de A1 dividido por A2. Pois esse “Certo” não mostra o resultado da fórmula; só indica que ela não está errada. Veja: se não houver um erro, qual deverá ser o valor da célula no lugar do texto “Certo”? A divisão.

Então, repetimos o próprio cálculo no lugar do “Certo” na fórmula, que passa a ser, defi nitivamente, assim:

=SE(ÉERROS(A1/A2) ; “Valor Inválido”;A1/A2)

Figura 155

Vamos resumir os passos anteriores:

• Iniciamos com a função SE e colocamos a condição que testa, pela função ÉERROS, se o cálculo a ser feito será qualquer valor de erro; na resposta verdadeira, ou seja, se o teste acusar um valor de erro, indicamos o valor que deverá surgir: em nosso caso, foi “Valor inválido”. Na resposta para o valor FALSO, ou seja, se não houver um erro no valor testado, repetimos o próprio valor testado pela função ÉERROS. Nesse caso, A1/A2.

12.7 Funções Financeiras

Essa categoria de funções executa operações envolvendo cálculos fi nanceiros, como encontrar o valor presente ou a taxa de juros de uma aplicação VF.

12.7.1 Função VF

Essa função retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.

A sintaxe é a seguinte: =VF(taxa;nper;pgto;[vp];[tipo]), onde:

137

Figura 156

• Taxa – É a taxa de juros por período.

• Nper – É o número total de períodos de pagamento em uma anuidade.

• Pgto – É o pagamento feito a cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou taxa. Se pgto for omitido, você deverá incluir o argumento vp.

• Vp – É o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero), e a inclusão do argumento pgto será obrigatória.

• Tipo – É o número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo for omitido, será considerado 0.

Defi nir tipo para Se os vencimentos forem

0 No fi nal do período

1 No início do período

Comentários

• Certifi que-se de que esteja sendo consistente quanto às unidades usadas para especifi car taxa e nper. Se fi zer pagamentos mensais de um empréstimo de quatro anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se fi zer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.

• Todos os argumentos, saques, tais como depósitos em poupança, serão representados por números negativos; depósitos recebidos, tais como cheques de dividendos, serão representados por números positivos.

Exemplo

138

12.7.2 Função NPER

Essa função retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante.

A sintaxe é a seguinte: =NPER(taxa;pgto;vp;[vf];[tipo]), onde:


• Pgto – É o pagamento feito em cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxa.

• Vp – É o valor presente ou atual de uma série de pagamentos futuros.

• Vf – É o valor futuro ou o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0).

• Tipo – É o número 0 ou 1 e indica as datas de vencimento.

Exemplo

Figura 157

139

12.7.3 Função PGTO

Essa função retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante.

A sintaxe é a seguinte: =PGTO(taxa;nper;vp;[vf];[tipo]), onde:


• Nper – É o número total de pagamentos pelo empréstimo.

• Vp – É o valor presente de uma série de pagamentos futuros.

• Vf – É o valor futuro ou o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, é 0).


Comentários

• O pagamento retornado por PGTO inclui o principal e os juros. Não inclui taxas, pagamentos de reserva ou tarifas, às vezes associados a empréstimos.

• Certifi que-se de que esteja sendo consistente quanto às unidades usadas para especifi car taxa e nper. Se fi zer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilize 12%/12 para taxa e 4*12 para nper. Se fi zer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.

Dica

Para encontrar o total pago no período da anuidade, multiplique o valor PGTO retornado por NPER.

140

Exemplo

Figura 158

12.7.4 Função VP

Essa função retorna o valor presente de um investimento. O valor presente é o valor total correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você toma uma quantia de dinheiro emprestada, a quantia do empréstimo é o valor presente para o concessor do empréstimo.

A sintaxe é a seguinte: =VP(taxa;nper;[vf];[tipo]), onde:

• Taxa – É a taxa de juros por período. Por exemplo, se você tiver um empréstimo para um automóvel com taxa de juros de 10% ao ano e fi zer pagamentos mensais, sua taxa de juros mensal será de 10%/12 ou 0,83%. Como taxa, você deve inserir 10%/12 ou 0,83% ou 0,0083 na fórmula.

• Nper – É o número total de períodos de pagamento em uma anuidade. Por exemplo, se você conseguir um empréstimo de carro de quatro anos e fi zer pagamentos mensais, seu empréstimo terá 4*12 (ou 48) períodos. Você deveria inserir 48 na fórmula para nper.

• Pgto – É o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou

141

tributo. Por exemplo, os pagamentos mensais de R$ 10.000 de um empréstimo de quatro anos para um carro serão de R$ 263,33. Você deve inserir -263,33 na fórmula como pgto. Se pgto for omitido, você deverá incluir o argumento vf.

• Vf – É o valor futuro ou o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Por exemplo, se você deseja economizar R$ 50.000 para pagar um projeto especial em 18 anos, então o valor futuro será de R$ 50.000. Você poderia, então, fazer uma estimativa conservadora na taxa de juros e concluir quanto economizaria por mês. Se vf for omitido, você deverá incluir o argumento pgto.


Comentários

• Certifi que-se de que esteja sendo consistente quanto às unidades usadas para especifi car taxa e nper. Se fi zer pagamentos mensais de um empréstimo de quatro anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se você fi zer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.

• Nas funções de anuidade, o saldo em dinheiro pago, como depósitos em poupanças, é representado por um número negativo; o saldo em dinheiro recebido, como cheques de dividendos, é representado por números positivos. Por exemplo, um depósito de R$ 1.000 no banco deveria ser representado pelo argumento -1.000, se você for o depositante; e pelo argumento 1.000, se você for o banco.

• O Microsoft Excel soluciona o argumento fi nanceiro em termos dos outros. Se a taxa não for 0, então:

Se a taxa for 0, então: (pgto * nper) + vp + vf = 0

142

Exemplo

Figura 159

12.7.5 Função TAXA

Essa função retorna a taxa de juros por período de uma anuidade. TAXA é calculada por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!.

A sintaxe é a seguinte: =TAXA(nper;pgto;vp;[vf];[tipo];[estimativa]), onde:

• Nper – É o número total de períodos de pagamento em uma anuidade.

• Pgto – É o pagamento feito em cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhum outro tributo ou taxa. Se pgto for omitido, você deverá incluir o argumento vf.

• Vp – É o valor presente — o valor total correspondente ao valor atual de uma série de pagamentos futuros.

• Vf – É o valor futuro ou o saldo que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0).


143

Figura 160

• Estimativa – É a estimativa estabelecida para a taxa.

– Se você omitir estimativa, esse argumento será considerado 10%.

– Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, a TAXA converge se a estimativa estiver entre 0 e 1.

Comentário

• Certifi que-se de que esteja sendo consistente quanto às unidades usadas para especifi car estimativa e nper. Se você fi zer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilize 12%/12 para estimativa e 4*12 para nper. Se fi zer pagamentos anuais para o mesmo empréstimo, utilize 12% para estimativa e 4 para nper.

Exemplo

144

13. Tabela De Dados

Tabela de dados é um recurso que exibe os valores de uma fórmula de acordo com algumas variáveis para uma ou duas de suas referências. Digamos que você vai comprar um carro e precisa calcular quais serão os pagamentos mensais do fi nanciamento de acordo com diversos prazos.

O Excel permite criar uma tabela de dados com uma ou duas variáveis de entrada.

13.1 Tabela de dados com uma variável de entrada

Para calcular os diversos pagamentos mensais de acordo com os diferentes prazos, crie uma planilha da seguinte maneira:

1. Na célula B4, digite o valor do fi nanciamento, por exemplo, 18.000.

2. Em B5, digite a taxa de juros, que é de 5%.

3. Em B6, digite um dos prazos, que é de 12 meses.

4. Prepare a planilha para criar a tabela com uma variável de entrada, inserindo, na célula C8, a seguinte fórmula, que será utilizada pela variável de entrada: =PGTO(B5;B6;B4).

5. No intervalo B9:B12, digite os diversos prazos de fi nanciamentos: 18, 24, 30 e 36 meses.

Figura 161

145

Figura 162

6. Agora, do lado de cada prazo, vamos calcular os valores referentes aos respectivos pagamentos mensais. Essa será a tabela que contém uma única variável, o prazo de pagamento.

7. Selecione o intervalo B8:C12, que é a região com a fórmula e as células com os prazos.

8. Na guia Dados, grupo Ferramenta de dados, clique em Teste de Hipóteses. Em seguida, clique em Tabela de Dados.

9. Na caixa de diálogo Tabela de Dados, precisamos defi nir qual será a célula de entrada da linha ou da coluna. Nesse caso, é a célula B6, que se refere ao número de períodos (quantidade de pagamentos).

10. Clique em OK.

Figura 163

146

11. Quando é criada a tabela, o Excel insere a fórmula com a função TABELA, cujos argumentos são a linha e a coluna da célula de entrada. Como, nessa fórmula, há somente a entrada da coluna, a fórmula fi ca da seguinte maneira: =TABELA(;B6).

Os valores são negativos por se referirem a um débito para quem contrai o fi nanciamento. Formate a tabela do modo que preferir.

Figura 164

Se precisar excluir a tabela, é preciso selecioná-la inteira para, somente depois, excluí-la.

Se quiser, altere os valores da fórmula ou a própria fórmula para visualizar diversos resultados diferentes.

12. Renomeie a planilha com o nome de Tabela_dados e salve a pasta de trabalho.

147

Figura 165

Figura 166

13.2 Tabela de dados com duas variáveis de entrada

Para visualizar os valores com prazos e taxas diferentes, é preciso criar a tabela com duas variáveis de entrada. Vamos usar uma cópia da planilha do exemplo anterior.

1. Insira as taxas 7%, 8%, 9% e 10% no intervalo C8:F8. A fórmula da tabela é a mesma que a anteriormente utilizada: =TABELA(;B6).

2. Selecione o intervalo que inclui todos os valores de entrada e a fórmula. Nesse caso, o intervalo é B8:F12.

3. Na guia Dados, grupo Ferramenta de dados, clique em Teste de Hipóteses e, em seguida, em Tabela de Dados.

4. Na caixa de diálogo Tabela de Dados, defi na B5 como célula de entrada da linha e B6 como célula de entrada da coluna, pois as taxas estão na linha e os prazos em colunas.

5. Clique em OK.

148

Figura 167

6. Renomeie a planilha de Tabela_dados1 e salve a pasta de trabalho.

14. Formulários personalizados

Criar planilhas para consulta de dados é uma tarefa interessante, pois é possível determinar a precisão das entradas escolhidas pelo usuário por meio da criação de formulários personalizados.

Você já deve ter notado que, nas caixas de diálogo do Windows, há controles que permitem ao operador determinar as entradas e, após confi rmá-las, executar a ação de acordo com a confi guração especifi cada.

No Excel, há vários botões de controle, como os botões de opções, que permitem selecionar apenas uma opção; caixas de seleção que serão ou não marcadas; caixas de edição onde digitamos valores etc.

A seguir, vamos criar formulários personalizados para consulta de dados. Primeiramente, é necessário exibir a guia Desenvolvedor por meio dos seguintes comandos:

1. Clique no botão Offi ce e em Opções do Excel.

2. Clique em Mais Usados e, no item Opções principais para o trabalho com o Excel, habilite a caixa Mostrar Guia Desenvolvedor na Faixa de Opções.

A planilha Preços (fi gura 168) contém preços de custo e preços de venda, que variam de acordo com a cidade e conforme a margem de lucro e o estoque de cada modelo.

149

Figura 168

Essa planilha servirá como base de consulta por meio dos controles de formulários, que serão criados na planilha Pesquisa.

Para facilitar o trabalho, algumas regiões da planilha foram nomeadas de acordo com a seguinte tabela:

Células Nome

A8:A16 Modelos

B8:B16 Custo

C8:F16 Venda

G8:G16 Estoque

14.1 Criando formulário

1. Abra a pasta de trabalho Exemplos e selecione planilha Preços para observar as informações.

2. Selecione uma planilha Pesquisa.

3. Clique na guia Desenvolvedor, grupo Controles, e no botão Inserir .

4. Clique na ferramenta Caixa de combinação . Note que o ponteiro do mouse se transforma em uma cruz fi na.

150

5. Posicione o ponteiro do mouse no canto superior esquerdo da célula C4, clique, arraste-o para a direita até o fi nal da célula e solte-o. Observe que surgiu um objeto chamado caixa de combinação.

Figura 169

Figura 170

6. Clique em qualquer célula e, depois, na caixa de combinação que você acabou de criar. Note que a caixa está vazia. Será necessário determinar um intervalo de entrada, que consiste em um intervalo de células que contém os valores que devem surgir na caixa de combinação.

7. Clique com o botão direito do mouse na caixa de combinação e em Formatar controle.

Quando precisar formatar os controles de um objeto, este é um dos métodos mais práticos de fazê-lo.

151

Figura 171

8. Na caixa de diálogo Formatar objeto, na guia Controle, que já deve estar ativada, confi guraremos os controles referentes à caixa de combinação.

• Em Intervalo de entrada, inserimos a coluna que contém os valores a serem listados, ou seja, os modelos dos carros. Digite Modelos, que é o intervalo nomeado na planilha Preços.

• Em Vinculo da célula, digite G4, que será a base a ser referenciada nas fórmulas. O vínculo da célula indica a posição que o item selecionado ocupa no intervalo de entrada.

• Em Linhas suspensas, digite 5 e marque Sombreamento 3-D.

9. Clique em OK.

10. Se precisar redimensionar o objeto, clique com o botão direito do mouse sobre ele e pressione a tela <ESC> ao surgir o menu de atalho. Depois, arraste um dos seis pontos ao redor do objeto para a direita, para a esquerda, para cima ou para baixo.

11. Agora, clique na caixa de combinação e veja que são listados todos os modelos, com cinco itens por vez. Por haver mais itens que a quantidade listada, surge uma barra de rolagem vertical. Selecione Vectra e veja que, na célula G4, surge o número 4, porque Vectra é o quarto item da lista de modelos. Se escolher Línea, o valor de G4 será 1.

152

Figura 172

Figura 173

12. Digite Estoque na célula C6 e, em D6, insira a fórmula: =ÍNDICE(Estoque;G4).

Como a função Índice retorna o valor de uma célula de acordo com a posição dessa célula em uma matriz, determinamos Estoque como matriz e G4 como o número da linha.

Essa fórmula retorna o valor da célula que estiver na linha cujo número é indicado pela célula G4 na região que contém todos os estoques.

Como Vectra é o quarto modelo e 10 é o quarto item do estoque, então este será o resultado da fórmula.

13. Em C8, digite Preço de custo e, em D8, insira a fórmula =ÍNDICE(Custo;G4), que retorna o custo do carro.

14. Em C10, digite Preço de Venda. Antes de inserir a fórmula, lembre-se de que existem quatro preços de venda, um para cada cidade. Então, vamos criar um botão de opção para cada cidade.

153

Figura 174


16. Clique na ferramenta Botão de opção .

17. Note que o ponteiro do mouse se transforma em uma cruz fi na.

18. Posicione o ponteiro do mouse no canto superior esquerdo da célula D1, clique, arraste-o para a direita até o fi nal da célula E1 e solte-o. Observe que surgiu um objeto chamado Botão de opção 1 (se o número não for 1, não se preocupe).

19. Clique nesse objeto com o botão direito do mouse e escolha Formatar controle.

20. Determine G5 como vínculo da célula e marque Sombreamento 3-D.

21. Clique em OK.

22. Com o objeto ainda selecionado, digite São Paulo e clique em qualquer célula ou pressione a tecla <ESC> duas vezes.

23. Faça mais três botões de opção como esse, sem precisar formatar os controles, que já vêm com a formatação do primeiro. Conforme você cria o objeto, ele deve conter o nome da próxima cidade de acordo com a ordem que está na planilha. Portanto, ao criar o segundo botão, ele deverá ser de Curitiba; o terceiro de Goiânia; e o quarto de Natal. Escolha o local apropriado para cada um.

24. Depois, clique no botão São Paulo e veja que, em G5, há o número 1, que corresponde à primeira cidade. Ao selecionar outra cidade, a anterior será desmarcada, porque somente um botão de opção pode estar selecionado por vez.

154

25. A célula G5 determina a posição em que se encontra a cidade escolhida no intervalo C9:F9 da planilha Preços.

Figura 175

Figura 176

26. Agora, vamos encontrar o preço de venda em D10. Como precisamos retornar o preço de acordo com o modelo e a cidade, a função ÍNDICE deve pesquisar a região nomeada para Vendas e retornar o valor que estiver de acordo com as coordenadas da linha escolhida pelo produto e da coluna escolhida pela cidade.

Como a célula G4 representa o número da linha do produto e G5, o deslocamento da coluna de acordo com a cidade, a fórmula a ser utilizada é a seguinte: =ÍNDICE(Venda;G4;G5).

27. Agora, vamos criar um controle para determinar o número de prestações. Em C12, digite Nro. Prestações.


29. Clique na ferramenta Barra de rolagem .

155

Figura 176

30. Note que o ponteiro do mouse transforma-se em uma cruz fi na.

31. Posicione o ponteiro do mouse no canto superior esquerdo da célula C13, clique, arraste-o para a direita até preencher a largura da coluna e solte-o.


33. Em Valor mínimo, digite 1, pois esse será o menor número de prestações possível para o controle da barra de rolagem.

34. Em Valor máximo, coloque 24, ou seja, o carro só poderá ser fi nanciado em até 24 vezes.

35. Em Alteração incremental, digite 1.

36. Defi na G6 como vínculo da célula.

37. Clique em OK.

38. Agora, clique em qualquer célula. Depois, clique na seta da direita da barra de rolagem e veja que o número da célula G6 aumenta de um em um (mudança incremental), até o máximo de 24.

39. Na célula D12, insira a fórmula =G6.

40. Assim, conforme você aumenta ou diminui o valor de G6 pela barra de rolagem, o número de prestações também se altera. Você entenderá mais adiante por que não defi nimos a própria célula D12 como vínculo da célula para esse controle.

156

Figura 178

41. Para calcular o valor da prestação, vamos considerar uma taxa de juros de 3,5% ao mês. Na célula C15, digite Valor da prestação e, em D15, insira a fórmula =-PGTO(3,5%;D12;D10).

42. Vamos verifi car se a venda é a vista ou a prazo.


44. Clique na ferramenta Caixa de seleção .

A diferença entre o Botão de opção e a Caixa de seleção é que a Caixa permite que sejam marcadas tantas opções quantas houver na planilha, enquanto o Botão permite selecionar apenas uma opção.

45. Note que o ponteiro do mouse transforma-se em uma cruz fi na.

46. Posicione o ponteiro do mouse no canto superior esquerdo da célula E15, clique, arraste-o para a direita até preencher a largura da coluna e solte-o.


48. Defi na G7 como vínculo da célula e clique em OK.

157

Figura 179

Figura 180

49. Ainda com o objeto selecionado, digite A vista como seu título.

50. Clique em qualquer célula. Depois, clique em A vista para marcar essa opção e note que, em G7, surge o valor lógico VERDADEIRO. Quando não estiver marcada essa opção, o valor de G7 será FALSO.

158

51. Agora, vamos corrigir a fórmula que calcula o pagamento e o valor das prestações. Selecione D12, a célula com o número de prestações, e insira a condição, determinando que, se G7 for VERDADEIRO (a vista), o valor dessa célula será 0 (zero). Caso contrário, será igual a G6 (vínculo da célula da barra de rolagem). A fórmula deve ser =SE(G7;0;G6).

Fica, assim, esclarecido por que não defi nimos a própria célula D12 como vínculo da célula da barra de rolagem.

52. Veja o resultado. 0 número de prestações passa a ser 0 (zero), causando um erro no valor das prestações (célula D15).

Para corrigir esse erro, insira as mesmas condições para fazer o cálculo por meio da fórmula =SE(G7;0;-PGTO(3,5%;D12;D10)).

Figura 181

53. Depois de criados todos os controles desejados, formate e proteja a planilha. Se inserir linhas ou colunas, assim como se quiser mover as células ou arrastar os objetos, isso não causará problemas com as fórmulas.

Portanto, faça uso dos recursos de formatação do Excel, como o WordArt, e dos efeitos de preenchimentos disponíveis para os objetos. Entre outras possibilidades, mude a cor das bordas das células.

159

Figura 182


Excel Avançado

Exer

cíci

os

163

Exercícios

Exercício 1

1. Abra a pasta de trabalho Exerc_Avanc.

2. Selecione a planilha NOMES.

3. Calcule o campo Valor Total.

4. Atribua o nome Comissão para a célula I1.

5. Utilizando o nome atribuído, calcule o campo Total c/ comissão.

6. O resultado deverá ser idêntico à imagem abaixo:

7. Salve as alterações feitas neste exercício.

164

Exercício 2


2. Selecione a planilha SUBTOTAL.

3. Organize a coluna Marca em ordem crescente e adicione subtotais. Utilize a função Soma() para o campo da planilha denominado Valor Total.

4.Altere a visualização para o Subtotal por Marca, utilizando os níveis de estrutura de tópico e oculte as colunas B, C e D.

165

• O resultado obtido deverá ser o seguinte:

5.Salve as alterações deste exercício.

166

Exercício 3


2. Selecione a planilha FILTRO_AVANÇADO.

3. Aplique os fi ltros para atender às solicitações a seguir:

a) Filtre as vendas de mouse que tenham quantidade superior a 8 e cuja vendedora seja Márcia.

167

b) Crie um fi ltro que exiba a quantidade de memória vendida no dia 13/07/2007.

c) Filtre as vendas realizadas pela vendedora Márcia, cujo produto seja teclado e a data seja posterior a 04/02/2008.

d) Exiba todas as vendas com total superior a 100,00 e que tenham sido feitas pelos vendedores Antônio, Jonas e Mauro.

e) Mencione os produtos vendidos por Pedro.

f) Exiba todos os produtos vendidos no dia 31/9/2007.

g) Filtre as vendas de Felipe, cujo produto seja CPU e o total seja superior a 3.600.

h) Filtre as vendas do produto ‘Monitor’, cujo total seja superior a 4.200 e cujo vendedor seja “Antônio, Pedro ou Felipe”.


168

Exercício 4


2. Selecione a planilha TABELA_DINÂMICA.

3. Com o recurso tabela dinâmica, fi ltre as vendas dos esportes por Trimestre. Na tabela dinâmica, você deverá selecionar o trimestre e exibir as vendas de cada esporte.

169

Com a tabela dinâmica, você pode conseguir outros layouts. O layout esperado para este exercício deve ser o apresentado a seguir:

4. Salve as alterações.

Exercício 5


2. Selecione a planilha IMPORTANDO_DADOS.

3. Importe os dados do arquivo Importação.txt.

170

• O resultado deverá ser o seguinte:

4. Salve as alterações da pasta de trabalho.

171

Exercício 6


2. Selecione a planilha CENÁRIOS.

3. Você deverá criar três tipos de cenário: Baixo, Signifi cativo e Muito Signifi cativo.

Valores do cenário

Natal Reveillon Carnaval

Baixo 2,2 3,4 5,1

Signifi cativo 3,3 5,4 6,2

Muito Signifi cativo 4,3 6,3 7,2

172

Você deverá atingir os resultados a seguir:

Cenário Baixo

Cenário Signifi cativo

173

Cenário Muito Signifi cativo

Resumo do cenário

Observações

• O campo Valores atuais representa os valores das células variáveis no momento em que o relatório de resumo do cenário foi criado.

• As células variáveis para cada cenário estão destacadas em cinza.

4. Salve as alterações realizadas.

174

Exercício 7


2. Selecione a planilha PROTEÇÃO.

3. Após a digitação, o campo Nome da primeira tabela deverá ser validado por meio de Lista, onde seja possível selecionar um nome cadastrado, evitando-se erros de digitação.

175

4. Os campos Endereço, Bairro, Cidade e Estado deverão ser preenchidos automaticamente, quando o nome do funcionário for escolhido.

Isso será possível após você aprender a função PROCV. Não se preocupe. Voltaremos a esse exercício posteriormente.

5. Proteja a pasta de trabalho, para que não seja possível adicionar planilhas ou fazer alterações nos dados existentes.


Exercício 8 - VALIDAÇÃO


2. Selecione a planilha VALIDAÇÃO.

3. Utilize o recurso de validação para as áreas de Nota e Falta. A área de notas deverá aceitar somente valores entre 0 e 10. Para cada coluna de falta, o aluno poderá ter, no máximo, 15 faltas.

176

4. Defi na as seguintes mensagens para os campos:

• De entrada

a) Nota: Digite apenas valores entre 0 e 10.

b) Falta: Digite valores entre 0 e 15

• De erro

a) Nota: O valor digitado não é válido. Para essa área, digite valores entre 0 e 10.

b) Falta: Para valores de falta, digite valores entre 0 e 15.

O resultado para o 4º item deverá ser o seguinte:

Notas

Mensagem de Entrada

Alerta de Erro

177

Faltas

Mensagem de Entrada

Alerta de Erro

5. Salve as alterações feitas nesta pasta de trabalho.

178

Exercício 9


2. Selecione a planilha SOLVER.

3. A quantidade de produtos deverá ser defi nida pelo SOLVER. Evite deixar que o total ultrapasse o valor de B9.


179

Exercício 10


2. Selecione a planilha FUNÇÃO_DE_TEXTO.

3. Utilizando funções de texto e Somase(), permita ao usuário digitar o departamento na célula B36 e visualizar o total de custo com treinamento do departamento. Por exemplo: “Total gasto com cursos para o departamento de ...”


180

Exercício 11

1. Abra a pasta de trabalho Exercícios_Avançados_2007.

2. Selecione a planilha SOMASE.

3. Calcule o valor do estoque de cada marca da tabela abaixo, utilizando a função Somase.

4. Qual o valor do estoque da Samsung?

181

5. Que marca possui o estoque mais valioso?


Exercício 12

1. Abra a pasta de trabalho Exercícios_Avançados_2007.

Neste exercício, usaremos duas planilhas para demonstrar a função PROCV:

PROCV

182

BASE_PROCV

Ao digitar o RM do funcionário na planilha PROCV, os demais dados deverão ser preenchidos automaticamente.

2. Selecione a planilha BASE_PROCV e calcule o salário líquido com base na tabela de descontos e convênio.

Observação: Os estagiários não deverão ter descontos no salário, exibindo-se, portanto, o salário bruto.

A planilha BASE_PROCV deverá fi car parecida com a imagem abaixo:

183

3. Em seguida, selecione a planilha PROCV e, utilizando a função PROCV, exiba o salário líquido que deverá ser atribuído, quando um RM for informado na célula C5 da planilha PROCV.

4. Efetue pesquisa dos campos disponíveis de acordo com o RM digitado. Para auxiliar na pesquisa, na coluna A, foram adicionados valores de RM que você poderá consultar.


Exercício 13


2. Selecione a planilha PROTEÇÃO.

3. Utilize a função PROCV para que os campos Endereço, Bairro, Cidade e Estado sejam preenchidos automaticamente, quando o nome do funcionário for escolhido.


184

Exercício 14


2. Selecione a planilha PREÇO_HOTÉIS.

3. Com base nos dados acima, em uma nova planilha, crie um formulário conforme modelo abaixo, tendo por base as seguintes informações:

• Número de dias -> mínimo 3 e máximo 8

• Valor do pacote -> Caso o pagamento não seja a vista, calcule um acréscimo de 2,5%.

185

4. Nomeie a planilha como FORMULÁRIO e salve a pasta de trabalho.

www.fundacaobradesco.org.br

Excel avancado

Engineering

Transcript of Excel avancado