341tica II Quimica.doc)2 SUMÁRIO 1. O Excel ..... 3 2. Versões ..... 3

Módulo 4

Informática II

2

SUMÁRIO

1. O Excel ............................................................................................................ 3

2. Versões ........................................................................................................... 3

3. Executando O Excel ..................................................................................... 5

4. A Janela Do Excel ......................................................................................... 6

5. O Que É Uma Planilha ................................................................................. 8

6. Arquivos No Excel ......................................................................................... 8

7. Abrindo Uma Pasta De Trabalho................................................................ 8

8. Salvando Uma Pasta De Trabalho ............................................................. 9

9. Fechando Uma Pasta De Trabalho.......................................................... 10

10. Iniciando Uma Nova Pasta De Trabalho ............................................. 10

11. Fechando O Excel .................................................................................. 10

12. Trabalhando Com Planilhas .................................................................. 10

13. Trabalhando Com Linhas E Colunas ................................................... 12

14. Alterando Colunas E Linhas .................................................................. 12

15. Operações Básicas Em Uma Planilha ................................................. 15

16. Endereço De Uma Célula ...................................................................... 17

17. Formatando A Planilha ........................................................................... 17

18. Formatação Condicional ........................................................................ 20

19. Cabeçalho E Rodapé ............................................................................. 23

20. Fórmulas ................................................................................................... 23

21. Funções .................................................................................................... 25

22. Gráficos No Excel ................................................................................... 28

Exercícios Básicos ............................................................................ 31

23. Copiar Fórmulas Relativas E Absolutas .............................................. 37

24. Funções Lógicas ..................................................................................... 38

25. Funções Estatísticas .............................................................................. 41

26. Funções Financeiras .............................................................................. 42

27. Funções De Procura ............................................................................... 46

28. Filtro E Auto Filtro ................................................................................... 50

29. Data E Hora ............................................................................................. 55

30. Vínculos Com Outras Planilhas Ou Arquivos ..................................... 61

31. Procv ......................................................................................................... 62

32. PROCV Com CONCATENAR (Duas Chaves) ................................... 63

33. Uso Do Comando FILTRAR .................................................................. 65

34. Função SUBTOTAL No Modo Filtrar ................................................... 66

35. Funções Matemáticas ............................................................................ 67

36. Banco De Dados ..................................................................................... 72

37. Funções De Banco De Dados .............................................................. 72

38. Tabela Dinâmica ..................................................................................... 75

39. Gráfico Dinâmico ..................................................................................... 78

40. Formulário ................................................................................................ 79

41. Macro ........................................................................................................ 79

42. Protegendo Uma Planilha ...................................................................... 82

3

Exercícios Avançados ...................................................................... 85

Titulo: Modulo - 11 Informática II

CONHECENDO O EXCEL

1. O Excel

O Excel é uma “Planilha Eletrônica”, desenvolvida pela Microsoft Corpora-tion para o pacote de aplicativos Office, é um aplicativo que permite a cria-ção e a manipulação de planilhas de cálculos, bem como a incorporação de gráficos e mapas às mesmas.

O Excel pode auxiliá-lo em todas as atividades, pessoais ou de empresas, que envolvam cálculos matemáticos como:

• Controle de Movimentação Financeira (contas a pagar, contas a re-ceber caixa, fluxo de caixa);

• Controle de Movimentação Bancária (saques, cheques, depósitos, extratos, saldos);

• Controle de Movimentação de Estoque (entrada/saída de mercadori-as, tabela de preços);

• Controle de Compras e Vendas; • Folha de Pagamento Simples; • Orçamento de Serviços; • Relatório de diversos controles criados pelo usuário; • Gráficos demonstrativos, a partir dos dados da planilha; • Qualquer trabalho que envolva uma lista de dados e operações ma-

temáticas. 2. Versões

O Excel já possui várias versões. Cada versão de um programa corresponde ao estágio de melhorias em que ele se encontra.

Atualmente o Excel, como a maioria dos programas do pacote Office, já se encon-tra na versão 2010.

Para saber qual a versão do Microsoft Excel que está instalada no computador é

4

só observar a primeira tela exibida quando o usuário executa o programa.

Na figura ao lado podemos ver a tela inicial do Excel na versão XP, do ano de 2003. Mesmo não sendo a versão mais atual, o pacote de aplicativos Of-fice do ano de 2003, o XP, é bastante difundido e utilizado atualmente em vários segmentos, possuindo praticamente todos os recursos encontrados na versão mais atual do ano de 2010, a figura abaixo mostra a janela do Excel na versão 2007.

E logo abaixo o da versão 2010:

5

3. Executando O Excel

Normalmente executa-se este aplicativo seguindo os seguintes procedimen-tos: 1. Clique sobre o botão Iniciar. Em seguida, posicione o cursor do mouse

sobre Programas; 2. No menu aberto, procure pelo item Microsoft Office, a lista aberta são os

aplicativos do pacote Office. 3. Procure pelo ícone correspondente ao Microsoft Excel. Posicione o cur-

sor do mouse sobre ele e dê um clique.

6

4. A Janela Do Excel

7

Ao iniciar o aplicativo será exibida uma janela como na imagem acima.

Vejamos a descrição de cada parte da tela do Excel:

(A): Barra de Títulos: Mostra o nome do aplicativo, seguido pelo nome da planilha em uso. O ícone do lado esquerdo desta barra, se clicado, abrirá o menu de controle da janela do aplicativo.

(B): Barra de Menus: Contém os menus onde se encontram todos os co-mandos do Excel. Para abrir um destes menus, basta clicar uma vez sobre o nome. Como atalho, experimente também teclar simultaneamente as te-clas Alt e aquela cuja letra se encontra sublinhada no nome do próprio me-nu.

(C): Barra de Ferramentas: Ou chamados botões de atalho. Normalmente são exibidos os atalhos mais comumente utilizados, mas o usuário também pode personalizá-la, bem como criar novas barras de ferramentas.

(D): Caixa de Nome: Indica ao usuário em qual célula se encontra o seletor e também a área de células que esteja selecionada no momento. É possível mover o seletor para qualquer célula, bastando digitar o endereço na caixa de nome e teclando Enter.

(E): Indicadores de Linhas: As linhas são identificadas por números.

(F): Guias de Planilhas: Cada pasta de trabalho (nome dado ao arquivo de-senvolvido no Excel) pode conter mais de uma planilha. Cada uma destas planilhas, além de poder receber um nome personalizado pelo usuário, po-de ser acessada com um clique sobre a guia correspondente.

(G): Botões de Rolagem de Guia: Quando clicados permitem rolar pelas guias de planilhas, para frente ou para trás, dependendo de sua posição atual. Clicando com o botão auxiliar do mouse, é aberto um menu, onde basta clicar sobre o nome da planilha para abri-la.

(H): Ferramentas de Desenhos: Uma barra contendo botões de atalho para funções voltadas a desenhos vetoriais, permitidos pelo Excel.

(I): Barra de Status: fornece informações atuais sobre determinadas situa-ções.

(J): Barra de Rolagem Vertical e Horizontal: Usadas quando o conteúdo da planilha ultrapassa o tamanho da tela, seja na horizontal ou na vertical.

(K): Planilha: A planilha é a própria área de trabalho do Excel. A planilha é dividida em células, dentro das quais os dados são digitados.

(L): Botões de controle das Janelas: Minimizar, Restaurar, Fechar as jane-las do aplicativo e do arquivo aberto.

(M): Indicados de Colunas: As colunas são identificadas por letras.

(N): Barra de Fórmulas: É a partir das barras de fórmulas que, direta ou indi-retamente, os dados são inseridos nas células. Assim que os dados come-çam a ser digitados, do lado esquerdo desta barra aparecem três botões, os quais são mostrados abaixo. Cada um destes botões possui uma função própria:

8

Cancelar: Caso o usuário desista da entrada que está por ser feita (Tecla de Atalho= Esc);

Inserir: Dará a entrada do dado digitado na célula (tecla de atalho= Enter)

Editar Fórmula: Permite colocar o conteúdo de uma célula em modo de edi-ção, para efeito de correções ou alterações (tecla de ata-lho= F2).

(O): Seletor de Células: O seletor de células faz às vezes de um cursor nas planilhas do Excel. Quando posicionado em uma célula qualquer este indica que a mesma está ativa.

5. O Que É Uma Planilha

A planilha funciona como uma tabela, dentro da qual são inseridos e mani-pulados valores para serem calculados. Com as fórmulas e funções aplica-das corretamente, o resultado dos cálculos, além de certos e imediatos, são automaticamente exibidos. A planilha é dividida em linhas e colunas, cuja in-terseção recebe o nome de célula.

6. Arquivos No Excel

Todo arquivo criado no Excel é comumente chamado de pasta de trabalho. A quantidade de planilhas suportadas por cada pasta de trabalho dependerá da quantidade de memória e de espaço em disco disponíveis. Por padrão cada pasta de trabalho no Excel é iniciada com três planilhas.

Cada planilha possui um nome que a identifica. Por padrão a primeira pos-sui o nome de plan 1, a segunda plan 2, e assim por diante.

7. Abrindo Uma Pasta De Trabalho

Abrir uma pasta de trabalho no Excel consiste em carregar uma pasta que esteja salva no computador para o programa, de forma que se possa visua-lizá-la e editá-la.

Existem várias formas de abrir uma pasta de trabalho no Excel, as mais co-muns são utilizando a Barra de Ferramentas.

Todas as formas para se abrir uma pasta de trabalho no Excel estão descri-tas abaixo.

9

• Barra de Menus:

1. Com o menu Arquivo aberto, clique sobre o comando Abrir. 2. Na Caixa de Diálogo Abrir, normalmente aberta na pasta padrão Meus

Documentos, procure pelo nome do arquivo desejado e clique sobre o mesmo, selecionando-o.

3. Clique sobre o botão Abrir. 4. Obs. Outra opção é dar um clique duplo no arquivo para não precisar cli-

car sobre o botão abrir.

• Barra de Ferramentas: 1. Com a área de trabalho do Excel ativa, clique sobre o botão abrir dispos-

to na barra de ferramentas padrão. 2. Será exibida a Caixa de Diálogo Abrir, selecione o arquivo e clique so-

bre o botão Abrir, ou dê um clique no mesmo. 8. Salvando Uma Pasta De Trabalho

Salvar uma pasta de trabalho significa gravá-la em uma unidade de arma-zenamento qualquer. Para que as planilhas possam ser acessadas e modi-ficadas posteriormente por algum usuário. Os procedimentos para salvar uma pasta de trabalho podem ser vários:

• Salvando pela primeira vez:

1. Abra o menu Arquivo e clique sobre o comando Salvar Como. Para exi-

bir a caixa de diálogo Salvar Como. 2. Defina o local em que o arquivo será salvo através do menu Salvar em:

no topo da janela, ou clicando sobre os atalhos na lateral esquerda da janela.

3. Digite o nome do arquivo na caixa Nome do Arquivo na parte inferior da janela.

4. Clique no botão Salvar.

• Salvando pela segunda vez: Quando se realiza alguma alteração em um arquivo que já foi salvo, é ne-cessário salvá-lo novamente. Neste caso o salvamento pode ser realizado como descrito abaixo, a não ser que o usuário deseje alterar o nome ou o endereço do arquivo.

1. Abra o menu Arquivo e clique sobre o comando Salvar; 2. (Atalho: Ctrl B ou Alt A S); 3. Clique sobre o botão salvar da barra de ferramentas.

10

9. Fechando Uma Pasta De Trabalho

Fechar uma pasta de trabalho implica no fechamento de um arquivo e não do aplicativo. Para isso o usuário pode:

1. Abrir o menu arquivo e clicar sobre o comando Fechar; 2. Clicar sobre o botão Fechar na caixa de controle do arquivo, na parte

superior direita da janela; 3. Através das teclas de atalho Alt A F. 10. Iniciando Uma Nova Pasta De Trabalho

Para dar início a uma nova pasta de trabalho no Excel, o usuário pode exe-cutar umas das seguintes opções:

1. Abrir o menu Arquivo e clicar sobre o comando Novo; 2. Teclar o atalho Ctrl O; 3. Clicar sobre o botão novo na Barra de Ferramentas. 11. Fechando O Excel

Para fechar o Excel o usuário pode optar pelos seguintes procedimentos:

1. Abrir o menu Arquivo e clicar sobre o comando Sair; 2. Clique sobre o botão Fechar da Caixa de Controle na Barra de Títulos; 3. Tecle o atalho Alt A R, ou Alt mais a tecla de função F4. 12. Trabalhando Com Planilhas

12.1. Inserindo e Excluindo Planilhas Uma pasta de trabalho padrão apresenta, inicialmente, 3 planilhas. Caso necessite de mais planilhas até um total de 255, você pode incluí-las, utili-zando o seguinte comando: menu Inserir => Planilha. Arraste as planilhas inseridas no local adequando do documento.

Uma pasta de trabalho padrão apresenta, inicialmente, 3 planilhas. Caso não necessite de todas, você pode excluir as desnecessárias, para isso se-lecione a guia correspondente à planilha que deseja excluir, clique no menu Editar e selecione a opção Excluir.

Obs.: Você também pode incluir e excluir planilhas na pasta de trabalho através de um clique com o botão direito sobre as guias das planilhas, esco-lhendo uma das opções: Inserir ou Excluir.

11

12.2. Renomeando Planilhas No Microsoft Office Excel 2003, um arquivo, ou seja, uma pasta pode conter até 255 planilhas diferentes, sendo, portanto, fundamental nomeá-las de maneira a distingui-las. A nomeação não grava a planilha, por isso é neces-sário utilizar o comando Salvar.

Para nomear a planilha, utilize um dos seguintes comandos: Dê um clique duplo na guia da planilha que deseja renomear. Digite o nome da planilha e pressione a tecla Enter.

12

13. Trabalhando Com Linhas E Colunas

13.1. Inserindo e Excluindo Linhas e Colunas Imagine que, durante a digitação de uma seqüência de dados, alguns dados foram esquecidos, ficando a tabela incompleta. Os dados podem ser intro-duzidos posteriormente nos locais corretos, bastando para isso fazer a es-colha adequada entre as opções de inserção, encontradas no menu Inserir: Selecione o local adequado e clique no menu Inserir => Linha ou Coluna.

Selecione a primeira linha e clique em Inserir => Linha. Digite o título da ta-bela e tecle Enter.

De modo semelhante é possível fazer a exclusão de colunas ou linhas que tenham sido introduzidas equivocadamente ou que não sejam mais neces-sárias. O comando de exclusão de linhas ou colunas pode ser encontrado no menu Editar => Excluir.

14. Alterando Colunas E Linhas

Tanto as larguras das colunas, quanto as alturas das linhas de uma plani-lha, possuem um padrão que o usuário, se quiser, pode alterar.

14.1. Alterando a Largura de uma Coluna Para executar tal ação, o usuário pode utilizar o mouse ou usar os coman-dos de menu.

• Utilizando o Mouse:

1. Posicione o cursor do mouse entre o indicador da coluna desejada e o da

próxima. O cursor se transformará em uma seta bilateral. 2. Pressione e arraste o mouse para a direita para aumentar e, para es-

querda para diminuir a coluna o quanto desejar. 3. Libere o botão do mouse para confirmar a ação.

• Utilizando Comandos:

13

1. Abra o menu Formatar e posicione o cursor do mouse sobre o submenu Coluna.

2. Clique sobre o comando Largura. 3. Será exibida uma Caixa de Diálogo, onde o usuário deve digitar na caixa

Largura da Coluna o valor desejado. 4. Confirme clicando sobre o botão OK.

14.2. Ajustando a Coluna Para ajustar o tamanho da coluna ao seu conteúdo, basta dar um clique du-plo na borda esquerda entre a outra coluna.

• Via Comandos:

1. Clique no menu Formatar posicione o cursor do mouse sobre Coluna. 2. Clique sobre o comando Ajuste da Seleção.

14.3. Alterando a Largura Padrão de uma Coluna 1. Abra o menu Formatar e posicione o cursor do mouse sobre Coluna. 2. Clique sobre o comando Largura Padrão. 3. Digite o valor desejado e confirme clicando sobre o botão OK.

14.4. Ocultando Colunas � Utilizando comandos de menus: 1. Selecione a coluna ou colunas que deseja ocultar. 2. Abra o menu Formatar e posicione o cursor do mouse sobre Coluna. 3. Clique sobre o comando Ocultar.

• Utilizando o mouse: 1. Selecione a Coluna ou Colunas que deseja ocultar. 2. Clique com o botão direito do mouse sobre as colunas selecionadas e

selecione Ocultar.

14.5. Alterando a Altura das Linhas A altura de uma ou mais linhas selecionadas pode ser alterada, através de arraste do mouse, ou por comando de menu.

Nota: Para exibir novamente a coluna, basta clicar

sobre o comando Reexibir, no mesmo menu.

14

• Utilizando o Mouse: 1. Posicione o cursor entre o indicador da linha desejada e a próxima. 2. Pressione o mouse e arraste para baixo para aumentar e para cima para

diminuir a altura. 3. Libere o botão do mouse para confirmar a ação.

• Utilizando Comandos de Menus: 1. Selecione uma ou mais linhas a serem modificadas. 2. Abra o menu Formatar e posicione o cursor em Linha. 3. No submenu, clique em Altura. 4. Digite uma nova Altura e confirme clicando sobre o botão OK.

14.6. Ajustando a Linha Para ajustar a linha ao conteúdo, dê um clique duplo entre o indicador e a próxima linha.

• Utilizando Comandos:

1. Selecione a linha que deseja ajustar. 2. Abra o menu Formatar e posicione o cursor do mouse sobre Linha. 3. Clique sobre o comando AutoAjuste.

14.7. Ocultando linha � Utilizando Comandos de Menus: 1. Selecione a Linha ou Linhas que deseja ocultar. 2. Abra o menu Formatar e posicione o cursor do mouse sobre Linha. 3. Clique sobre o comando Ocultar.

• Utilizando o Mouse: 1. Selecione a Linha ou Linhas que deseja ocultar. 2. Clique com o botão direito do mouse sobre as linhas selecionadas e se-

lecione Ocultar

Nota: Para exibir novamente a Linha, basta clicar

sobre o comando Reexibir, no mesmo menu.

15

15. Operações Básicas Em Uma Planilha

15.1. Digitando Dados em uma Planilha Assim que o Excel é iniciado, uma página em branco é aberta e o cursor é automaticamente posicionado na célula A1, isto significa que os dados já podem ser digitados.

Para concluir a entrada do dado após digitá-lo, o usuário pode confirmar te-clando Enter ou usando as teclas de movimentação para confirmar e mover pela planilha.

NOTA:

• Dados alfanuméricos são, por padrão, alinhados à esquerda na célu-la;

• Dados numéricos são alinhados à direita na célula; • Se, num dado numérico o usuário separar a casa decimal com um

ponto no lugar de uma vírgula, o dado será reconhecido como texto, e não como um número.

15.2. Deletando os dados Quando uma célula ou um grupo células é selecionado o usuário pode apa-gar o seu conteúdo pressionando a tecla Delete.

15.3. Editando (alterando) células Para alterar o conteúdo de uma célula, primeiro o usuário precisa colocá-la em modo de edição. Para isso:

1. Tecle F2, dê um duplo clique na célula ou de um clique na barra de fór-

mulas para colocá-la em modo de edição; 2. O cursor será liberado, a partir deste momento o conteúdo da célula já

pode ser alterado; 3. Confirme a ação teclando Enter ou clicando em outra célula.

15.4. Teclas de atalho para edição:

→→→→ Move para a célula à direita

←←←← Move para a célula à esquerda

↓↓↓↓ Move para a célula abaixo

↑↑↑↑ Move para a célula acima

Home Move para o início da linha

16

END + →→→→ Move para a última coluna preenchida

END + ←←←← Move para a primeira coluna preenchida

END + ↓↓↓↓ Move para a última linha preenchida

END + ↑↑↑↑ Move para a primeira linha preenchida

F2 Coloca a célula em modo de edição

Delete Apaga o caractere que está após a posição do cursor

Backspace Apaga o caractere imediatamente anterior à posição do cursor

15.5. Seleção de células Toda e qualquer ação sobre uma célula requer que a mesma esteja seleci-onada. A seleção pode ser feita pelo mouse ou pelo teclado.

• Selecionando uma célula:

Basta clicar sobre ela ou teclar uma das setas de cursor, movendo o seletor até a célula desejada.

• Selecionando um grupo de células:

Clique na primeira delas pressione e arraste o mouse, até que a área dese-jada esteja marcada. Outra opção é segurar a tecla Shift, clicar sobre a pri-meira célula e depois sobre a última célula da área desejada. As teclas de movimentação também podem ser usadas juntamente com a tecla Shift.

CTRL + Home Move para o início da planilha

Page UP Move uma tela abaixo

Page Down Move uma tela acima

CTRL + End Move para a última célula preenchida

17

• Selecionando células aleatórias: Clique sobre a primeira delas mantenha a tecla Ctrl pressionada, enquanto clica sobre as outras.

• Selecionando uma linha:

Para selecionar linhas de células no Excel basta clicar sobre o indicador da linha que deseja selecionar.

Com a tecla Shift pressionada o usuário pode selecionar múltiplas linhas em seqüência.

Com a tecla Ctrl o usuário pode selecionar linhas aleatórias na planilha.

• Selecionando uma coluna:

Para selecionar uma coluna de células basta clicar sobre o indicador da co-luna que deseja selecionar.

Com a tecla Shift pressionada o usuário pode selecionar múltiplas colunas em seqüência.

Com a tecla Ctrl o usuário pode selecionar colunas aleatórias na planilha.

16. Endereço De Uma Célula

As células são independentes uma da outra e são identificadas através de seu Endereço na Planilha. O endereço de uma célula é determinado pela sua posição na planilha. Exemplo: Uma célula que está na coluna A e na linha 1 é chamada A1.

17. Formatando A Planilha

Quando uma planilha é montada, os dados são incluídos nas células de forma simples, ou seja, sem nenhum formato especial. Para melhorar a apresentação da planilha, e a deixar com uma aparência mais profissional o usuário deve formatá-la. Para isso:

1. Selecione as células que serão formatadas, clique no Menu Formatar; 2. Clique sobre o item Células.

18

Será exibida a Caixa de Diálogo Formatar Células. As divisões desta caixa acessadas através de um clique do mouse, são denominadas guias. Cada guia dá acesso a opções diferentes de formatação, que serão descritas de-talhadamente a seguir.

17.1. Guia Número Praticamente, todas as planilhas que o usuário montar envolverão números e cada número pode representar tipos diferentes de informação, como: quantidade, valor em dinheiro, valor percentual, etc.

Para organizar a planilha, o usuário deve informar ao Excel que o formato de cada número. Para isso o usuário deve:

1. Selecionar as células que receberão um mesmo formato; 2. Clicar sobre a guia Número; 3. Clicar sobre o tipo de formato desejado na caixa Categoria; 4. Escolher o Símbolo e casas decimais se necessário; 5. Confirmar a escolha teclando sobre o botão OK.

19

As principais opções de formato são:

• Geral: quando o número não possuir uma formatação específica.

Exemplo: 100 • Número: indicado quanto o número exige um formato específico.

Exemplo: 9,0 com uma casa decimal. Quando o dado é um número, existe uma caixa para especificar o número de casas decimais de-sejadas e um botão para ativar ou desativar o separador de milhar.

• As opções Moeda e Contábil: devem ser selecionadas quando as cé-lulas representarem valores em dinheiro. Exemplo: (Real, Dólar, etc..).

• Opções Data e Hora: Devem ser usadas para inserir uma data ou a hora em uma célula.

• Especial: devem ser selecionada para formatar números como tele-fone, CPF, CEP, etc.

17.2. Guia Alinhamento A guia Alinhamento permite ao usuário o alinhamento das células selecio-nadas.

• A opção Horizontal define o alinhamento entre as bordas esquerda e

direita das células. • A opção Vertical define o alinhamento entre as bordas superior e in-

ferior das células. • Se o usuário marca a caixa de seleção Retorno Automático de Tex-

to, o Excel criará uma nova linha para a célula, caso o conteúdo digi-tado não caiba nela.

• O campo Orientação pode ser usado para inclinar o conteúdo das células selecionadas. Para isso, aumente ou diminua o grau de incli-nação na caixa Graus.

17.3. Guia de Fonte A guia Fonte permite modificar o tipo de letra, o estilo, o tamanho das letras e ainda, mudar a cor e sublinhar o conteúdo das células. Para isso:

1. Selecione as células ou célula a serem alteradas 2. Clique sobre a guia Fonte 3. Escolha as opções desejadas e confirme clicando sobre o botão OK.

17.4. Guia Borda A guia Borda permite ao usuário definir bordas para as células seleciona-das.

20

1. Clique na guia Borda. 2. Clique na opção Contorno, para colocar uma borda ao redor das células

selecionadas. 3. Clique na opção Internas para colocar bordas dentro das células seleci-

onadas. 4. O usuário pode ainda, mudar o Estilo e a Cor das bordas. 5. Clique sobre o botão OK para confirmar a ação.

17.5. Guia Padrões A guia Padrões permite que o usuário altere a cor de fundo das células se-lecionadas.

1. Clique sobre a guia Padrões. 2. Escolha a Cor e clique sobre o botão OK para confirmar.

17.6. Guia Proteção A guia proteção é utilizada para definir se o conteúdo das células será blo-queado ou oculto. Estas opções só terão efeito se a planilha for protegida. Para isso acione no menu ferramentas o item proteger, escolha a opção proteger planilha.

17.7. Modificando o Alinhamento do Texto É possível fazer com que o conteúdo das células selecionadas fiquem ali-nhados a esquerda, centralizados, à direita, justificado ou mesmo sem ali-nhamento.

Existem duas formas para determinar o alinhamento do conteúdo da célula: através dos botões da barra de ferramentas ou através do comando forma-tar células. Na barra de ferramentas Formatação encontramos os seguintes botões:

18. Formatação Condicional

21

A Formatação Condicional permite fazer formatações em diferentes dados na planilha a partir de uma condição determinada pelo usuário.

Por exemplo, podemos formatar uma faixa de valores com um formato para valores menores ou iguais a 1000 e outro formato diferente para valores maiores do que 1000. Utilizamos a formatação condicional para destacar dados importantes.

Ex 1: A partir da planilha abaixo digamos que você queria dar destaque pa-ra as médias que forem maior que 6,0 colocando-as em negrito e azul, e as menores que 6,0 em negrito e vermelho.

Se colocássemos as cores em cada nota demoraríamos muitos, por isso uti-lizaremos a formatação condicional.

Nesse caso selecione todas as notas, ou seja, o intervalo B2: B9. Depois de selecionado o intervalo clique sobre o menu Formatar e escolha a opção Formatação condicional, então surgirá a janela indicada na figura a seguir.

A tela surge com informação “Condição 1”, abaixo dela aparece uma lista contendo as seguintes opções.

• O valor da célula é - utilizada quando a célula possui valores digita-

dos diretamente.

22

• Fórmula é - utilizado quando o valor da célula é obtido a partir de cálculos realizados por uma fórmula. A opção mais utilizada para formatação condicional é "O valor da célula é", a qual já vem seleci-onada por padrão.

Na segunda lista podemos selecionar a condição. Temos as seguintes op-ções:

• Entre • Não está entre • Igual a • Diferente de • Maior do que • Menor do que • Maior ou igual a • Menor ou igual a

De acordo com o nosso exemplo iremos selecionar a opção Maior ou igual a, e no terceiro campo digite 6. Lembre que queremos formatar os valores maiores do que 6,0.

Agora precisamos definir o formato a ser aplicado para as células que se encaixam no critério especificado - va-lores maiores do que 6. Para definir o formato dê um clique no botão Formatar. Na janela que surge defina fonte em azul e negrito, con-forme indicado na figura a seguir e depois dê um clique no botão OK:

Observe que nesta janela também podemos definir bordas e cor de segundo plano (guia Padrões). Estas guias são idênticas às guias quando se acessa o comando Formatar -> Células.

Você estará de volta a janela Formatação Condicional. Dê um clique no bo-tão Adicionar, observe que a tela da formatação condicional aumentou e adicionou uma segunda informação “Condição 2”.

23

Nesta opção será feita a mesma coisa só que a condição a ser escolhida será Menor do que, e no terceiro campo será digitado a opção 6 novamen-te. Após isto clique no botão formatar e marque a opção negrito e cor ver-melho depois clique em OK.

Após ter determinado tais formatações, você novamente estará de volta a janela Formatação Condicional. Dê um clique no botão OK, para fechar esta janela e aplicar a formatação de acordo com os critérios definidos.

Segundo nosso exemplo as notas maiores e iguais a 6 ficaram em azul e negrito, e as notas menores que 6 ficaram em negrito e vermelho.

19. Cabeçalho E Rodapé

Os cabeçalhos e rodapés são áreas situadas nas margens superior e inferi-or da página de um documento. Estas áreas serão impressas em todas as páginas da planilha. Você pode inserir textos ou elementos gráficos em ca-beçalhos e rodapés, por exemplo, números de página, a data, o logotipo de uma empresa, o título do documento ou o nome de arquivo referente ao mesmo, ou o nome do autor, que são impressos no início ou no fim de cada página de um documento.

Para editar os cabeçalhos e rodapés das páginas é necessário selecionar a opção Cabeçalho e Rodapé do menu Exibir. As caixas que aparecem no início e no final da página são as áreas de edição dos cabeçalhos e rodapé.

20. Fórmulas

20.1. Definição de Fórmula Fórmula é uma seqüência de dados contidos numa célula e que normal-mente gera resultados a partir de dados que estão em células diferentes na

24

planilha. Operações como adição, subtração, multiplicação, divisão e com-paração quando requeridas pelo usuário, são aplicadas a partir de uma fór-mula. A fórmula serve como referência a outras células, áreas de células ou valores fixos. Para inserir uma fórmula, o usuário deverá iniciá-la sempre com o sinal de (=). O conteúdo principal de uma fórmula deve vir entre pa-rênteses, após o sinal de igual ou após a função utilizada.

Exemplo:

25

20.2. Operações Básicas

Para se criar uma fórmula em uma célula é necessário indicar a referência das células, que entrarão nos cálculos e usar o operadores abaixo:

Aritméticos Lógicos

+ Adição > Maior

- Subtração < Menor

* Multiplicação >= Maior ou igual

/ Divisão <= Menor ou igual

^ Exponenciação <> Diferente

21. Funções

Uma função nada mais é do que uma fórmula pré-definida que efetua um ti-po de cálculo especifico. Tudo o que você precisa para utilizar uma função é fornecer a ela os valores apropriados para efetuar esses cálculos. Tal como as fórmulas criadas pelo usuário, as funções devem começar com um sinal de igual (=) para que o Excel saiba interpretá-las como fórmulas e não com texto. É aconselhável digitar o nome da função e o nome das células em maiúsculos.

= SOMA(...) – Soma todos os valores do grupo ou células indicadas.

Ex.: =SOMA(A1:C1) ou =SOMA(A1;B1;C1)

= MÉDIA(...) – Calcula o valor médio do grupo ou célula indicadas.

Ex.: =MÉDIA(A1:C1) ou =MÉDIA(A1;B1;C1)

= MÁXIMO(...) – Calcula o maior valor do grupo de células ou células indi-cadas

Ex.: =MAXIMO(A1:C1) ou =MAXIMO(A1;B1;C1)

=MÍNIMO(...) – Calcula o menor valor do grupo de células ou células indica-das

Ex.: =MINIMO(A1:C1) ou =MINIMO(A1;B1;C1)

As funções são como uma espécie de comandos internos, os quais podem fornecer operações e resultados que normalmente, o usuário levaria muito tempo e teria mais dificuldade em conseguir.

As funções são palavras chaves já pré-programadas, contendo na maioria das vezes complementos chamados parâmetros de função, os quais são informações adicionais que o próprio usuário especifica. O Excel possui vá-

26

rias funções, que são divididas em categorias, de acordo com as suas ca-racterísticas.

Ex. 1- Categorias de Funções no Excel: Funções de Data e Hora, funções de Estatística, Funções Trigonométrica e Matemáticas, Funções de Lógica, Funções de Texto, Funções de Banco de Dados, etc..

Ex. 2- Na categoria Data e Hora podemos utilizar as funções: Data (), Hoje () e Tempo ().

21.1. Função SE Retorna um valor se uma condição que você especificou for avaliada como VERDADEIRA e outro valor se for avaliada como FALSA.

Use SE para conduzir testes condicionais sobre valores e fórmulas.

Sintaxe SE (teste_lógico; valor_se_verdadeiro; valor_se_falso)

Teste_lógico é qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador lógico de comparação.

Valor_se_verdadeiro é o valor retornado se o teste lógico for VERDADEI-RO. Por exemplo, se esse argumento for a seqüência de caracteres de tex-to "Dentro do orçamento" e o argumento teste_lógico for considerado VER-DADEIRO, a função SE exibirá o texto "Dentro do orçamento". Se tes-te_lógico for VERDADEIRO e valor_se_verdadeiro for vazio, o argumento retornará 0 (zero). Para exibir a palavra VERDADEIRO, use o valor lógico VERDADEIRO para esse argumento. Valor_se_verdadeiro pode ser outra fórmula. Valor_se_falso é o valor retornado se o teste lógico for FALSO. Por exemplo, se esse argumento for a seqüência de caracteres de texto "Acima do orçamento" e o argumento teste_lógico for considerado FALSO, a função SE exibirá o texto "Acima do orçamento". Se teste_lógico for FALSO e va-lor_se_falso for omitido (ou seja, se não houver ponto e vírgula após va-lor_se_verdadeiro, o valor lógico FALSO será retornado. Se teste_lógico for FALSO e valor_se_falso for vazio (ou seja, se houver um ponto e vírgula após valor_se_verdadeiro seguida do parêntese de fechamento), o valor 0 (zero) será retornado. Valor_se_falso pode ser outra fórmula.

27

Comentários

• É possível aninhar até sete funções SE como argumentos va-lor_se_verdadeiro e valor_se_falso para construir testes mais elabo-rados. Consulte o último dos exemplos a seguir.

• Quando os argumentos valor_se_verdadeiro e valor_se_falso são avaliados, SE retorna o valor que foi retornado por estas instruções.

• Se algum dos argumentos de SE for matriz, cada elemento da matriz será avaliado quando a instrução SE for executada.

• O Microsoft Excel fornece funções adicionais que podem ser usadas para analisar os dados com base em uma condição. Por exemplo, para contar o número de ocorrências de uma seqüência de caracte-res de texto ou um número dentro de um intervalo de células, use a função de planilha CONT.SE. Para calcular uma soma baseada em uma seqüência de caracteres de texto ou em um número dentro de um intervalo, use a função de planilha SOMASE. Saiba como calcular um valor baseado em uma condição.

28

21.2. Auto Soma O recurso Auto Soma permite que o usuário obtenha rapidamente a soma de valores constantes em uma determinada área de células. A fórmula rela-cionada a Auto Soma pode ser digitada ou obtida com um clique do mouse sobre o botão específico:

1. Selecione toda a área que deverá ser somada, incluindo uma ou duas

células a mais. 2. Clique sobre o botão AutoSoma na barra de ferramentas padrão. 3. O resultado é exibido imediatamente. Se o resultado for mostrado em forma de cerquilha como ######, por exemplo, é sinal que o conteúdo está truncado. Para corrigir esta trunca-gem, é necessário aumentar a largura da coluna correspondente.

21.3. Subtotais No menu Dados é um comando que calcula valores de total geral e subtotal para as colunas selecionadas. O Excel insere e rotula automaticamente as linhas de total e organiza a lista em estruturas de tópicos, permitindo ao usuário escolher o nível para visualizar a tabela (toda tabela; somente os subtotais ou apenas o total geral). Antes de inserir o subtotal você precisa organizar a tabela em ordem alfabética usando o botão classificação cres-cente na barra de ferramentas.

22. Gráficos No Excel

Os mais utilizados são:

• Gráficos de Colunas:

Esse tipo de gráfico é indicado quando se deseja comparar valo-res por categorias. Por exemplo, valores gastos com despesas di-ferentes. Neste gráfico cada des-pesa será representada por uma coluna e poderá então, ser visua-lizado qual representa maior e menor gasto.

0

2

4

6

8

10

12

1 2 3 4 5

Sequência 1

Sequência 2

Sequência 3

29

• Gráficos de Barras:

Este tipo de gráfico deve ser utili-zado quando for necessário com-parar valores entre diferentes ca-tegorias.

• Gráficos de Linhas:

Utiliza-se quando for necessário acompanhar a variação de um va-lor durante um determinado perío-do.

• Gráficos de Setores (Pizza):

Esse tipo de gráfico deve ser utili-zado quando for necessário de-monstrar a percentagem que cada item representa no valor total (100%).

22.1. Aplicando um Gráfico à Planilha Após ter feito a planilha, o usuário já pode criar um gráfico para representar seus dados.

1. Selecione os dados da tabela e abra o menu Inserir. 2. Clique sobre o comando Gráfico, este iniciará o Assistente de Gráficos

ou, clique no ícone de atalho Assistente Gráfico na barra Padrão. 3. Assim que o Assistente é iniciado, o usuário já pode escolher o tipo de

gráfico que quer inserir na planilha. 4. Escolha o subtipo do gráfico. 5. Clique sobre o botão Avançar.

0 5 10 15

1

2

3

4

5

Sequência 3

Sequência 2

Sequência 1

0

2

4

6

8

10

12

1 2 3 4 5 6 7 8 9

Sequência 1

Sequência 2

Sequência 3

Sequência 1

1

2

3

4

5

6

7

8

9

30

O gráfico é composto de várias partes: Área do Gráfico, Seqüência de Da-dos, Área de Plotagem, Eixo das Categorias, Eixo dos Valores, Linhas de Grade, Legenda, etc..

22.2. Selecionando os Dados do Gráfico Depois que tiver selecionado o tipo de gráfico e clicado em Avançar, será exibida uma tela, onde o usuário poderá selecionar os dados da planilha que serão exibidos no gráfico.

Nesta tela, o usuário tem a opção de selecionar a seqüência principal para a criação do gráfico: Linhas e Colunas.

• Linhas:

Essa opção criará o gráfico usando as linhas da planilha como eixo principal (eixo Y), ou seja, cada linha selecionada formará uma coluna, uma barra, uma linha ou uma fatia da pizza, dependendo do tipo de gráfico seleciona-do.

• Colunas:

Essa opção criará o gráfico usando as colunas da planilha como eixo princi-pal (eixo X), ou seja, cada coluna selecionada formará uma coluna, uma barra, uma linha ou uma fatia da pizza, dependendo do gráfico selecionado.

31

Exercícios Básicos

1. Formatação de planilha

� TABELA: Fonte - comic sans ms, tamanho 10; � JANEIRO: Sombreamento Padrão cinza 6,25%, Orientação vertical,

Alinhamento Horizontal e Vertical centro; � Excel: Fonte – Verdana, tamanho 16, Orientação 45 graus; � Alterar a altura das linha para 18 pixels. � Sombreamento Principal: Cor Cinza claro; � Adicionar borda nos itens da tabela, borda à direita e superior cinza

escuro e desenhar borda inferior cor branca; � Aplicar o botão estilo moeda; � Na coluna TOTAL PAGO usar o operador de multiplicação: *

2. Elaborar a planilha abaixo, fazendo-se o que se pede:

Empresa Nacional S/A

Código Produto Jan Fev Mar Total 1º Trim.

Máximo Mínimo Média

1 Porca R$ 4.500,00 R$ 5.040,00 R$ 5.696,00 2 Parafuso R$ 6.250,00 R$ 7.000,00 R$ 7.910,00 3 Arruela R$ 3.300,00 R$ 3.696,00 R$ 4.176,00 4 Prego R$ 8.000,00 R$ 8.690,00 R$ 10.125,00 5 Alicate R$ 4.557,00 R$ 5.104,00 R$ 5.676,00 6 Martelo R$ 3.260,00 R$ 3.640,00 R$ 4.113,00

Totais

32

Códi-

go Produto Abr Mai Jun Total 2º

Trim. Máx Mín Média

1 Porca R$ 6.265,00 R$ 6.954,00 R$ 7.858,00 2 Parafuso R$ 8.701,00 R$ 9.658,00 R$ 10.197,00 3 Arruela R$ 4.569,00 R$ 5.099,00 R$ 5.769,00 4 Prego R$ 12.341,00 R$12.365,00 R$ 13.969,00 5 Alicate R$ 6.344,00 R$ 7.042,00 R$ 7.957,00 6 Martelo R$ 4.525,00 R$ 5.022,00 R$ 5.671,00

Totais

Total do Semestre

FÓRMULAS: 1ª Tabela: � Total 1º Trimestre: soma das vendas dos meses de Jan / Fev / Mar. � Máximo: calcular o maior valor entre os meses de Jan / Fev / Mar. � Mínimo: calcular o menor valor entre os meses de Jan / Fev / Mar. � Média: calcular a média dos valores entre os meses de Jan / Fev /

Mar. 2ª Tabela: � Total 2º Trimestre: soma das vendas dos meses de Abr / Mai / Jun. � Máximo: calcular o maior valor entre os meses de Abr / Mai / Jun. � Mínimo: calcular o menor valor entre os meses de Abr / Mai / Jun. � Média: calcular a média dos valores entre os meses de Abr / Mai /

Jun. � Totais: soma das colunas de cada mês (1ª e 2ª tabela). � Total do Semestre: soma dos totais de cada trimestre.


FÓRMULAS:

33

� Total R$: multiplicar Qtde por Preço Unitário � Total US$: dividir Total R$ por Valor do Dólar – usar $ nas fórmulas � Alterar as colunas de acordo com a necessidade.


� Centralize o titulo e formate-o Estilo da fonte: Negrito; � Fonte: Lucida Console; Tamanho da fonte: 22 � Utilize os recursos de bordas e sombreamento � Utilizando o recurso AUTOSOMA, descubra a quantidade de produ-

tos vendida no mês de janeiro a junho. � Descubra a quantidade vendida em Goiás. Faça o mesmo com os

demais estados.

34


� Digite a tabela a cima centralizando o titulo e usando os recursos da

guia fonte ao seu critério. � Usando a guia alinhamento coloque os subtítulos no centro confor-

me o modelo a cima. � Calcule as medias dos alunos usando a função Media (teste

+prova/2) � Calcule a media final dos alunos (me-

dia1+media2+media3+media4/4)


� Digite o boletim de um aluno X, formate - o usando as guias de for-matação ao seu critério.

� Use a formula =Soma (nota 1° bim até 4°) para calcular as somas das notas de cada matéria.

� Use a função Media para calcular a media de cada nota

35

� Use a função Se para obter o resultado =Se (Média>=6 então "Apro-vado" caso contrário "Recuperação")

� Digite a nota de recuperação se o aluno precisar. � Use a função Se para calcular a media final =Se (Média>=6 então re-

petir a nota da média caso contrário será a nota da (média mais a no-ta da recuperação) /2)

7. Faça a tabela abaixo com seu respectivo gráfico.

Fórmulas � Classificar os funcionários em ordem crescente. � Total: =Se (SL>=2000; SL+ (SL*1%); SL+ (SL*2%)) � Impostos: =Total*10% � Plano de Saúde: =Total*5% � Total: =Impostos+Plano de Saúde � Sal. Liquido: =C10-F10 � Média de Salários: =Média (Célula inicial: Célula final) � Maior Salário: =Maximo (Célula Inicial: Célula final) � Menor Salário: =Mínimo (Célula Inicial: Célula final) � Gráfico: Selecione os Nomes e o Salário Liquido

36

37

23. Copiar Fórmulas Relativas E Absolutas

Fórmulas relativas são aquelas onde as referências das células utilizadas na função são atualizáveis, por exemplo, quando copiamos uma fórmula qualquer, para as linhas abaixo as referências das células mudam de acor-do com as linhas. Como exemplo, vamos utilizar a fórmula =A1*B1. Se a copiarmos para uma linha abaixo ela irá atualizar as referências. Sendo as-sim as próximas fórmula será =A2*B2, =A3*B3 e assim por diante.

Mas digamos que você deseja que determinado endereço deva ser fixo, ou seja, que não seja alterado à medida que a fórmula for copiada para outras células, estas são as referências absolutas, para este tipo de fórmula deve-

se usar o símbolo $ na referência de célula desejada. Por exemplo, diga-mos que na mesma fórmula =A1*B1, você deseja que a referência de célula B1 seja absoluta, ou seja, fixo, para que ao copiar a fórmula pra as próxi-mas linhas permaneça o B1, deve-se utilizar o $.

Ex. =A1*B$1

Note que o símbolo $ está antes do número 1, ao qual simboliza a linha.

Após copiar a fórmula para as próximas linhas, as referências irão ficar da seguinte forma:

=A2*B$1

=A3*B$1

=A4*B$1

Quando desejamos copiar uma fórmula para as colunas do Excel de forma que a fórmula fique fixa, a regra é a mesma só que o símbolo $ deve ser adicionado antes da letra que identifica a coluna. Por exemplo, utilizaremos agora =A1*A2, se a copiarmos desta forma, as fórmulas nas outras colunas ficarão: =B1*B2, =C1*C2, =D1*D2, e assim por diante.

Mas se colocarmos o $ antes de A1, ou seja, =$A1*A2, o referência será absoluta, depois da fórmula copiada os valores ficarão da seguinte forma nas outras colunas.

=$A1*B2

=$A1*C2

=$A1*D2

Agora digamos que na fórmula =A1*A2, você deseja fixar tanto a coluna quanto a linha A1, nesse caso usa-se o símbolo $ antes tanto da coluna quanto da linha, ou seja, =$A$1*A2, ao copiar tanto para baixo para as ou-tras linhas, ou para o lado para as outras colunas, o endereço =$A$1 sem-pre ficará fixo.

38

24. Funções Lógicas

24.1. Função E: Utilizamos a Função E sempre que precisarmos definir dois ou mais critérios de pesquisa. Quando os critérios são unidos por um operador E, todos os critérios devem ser verdadeiros para que um registro seja selecionado. Por exemplo, se forem definidos três critérios unidos por operadores E, os três critérios devem ser atendidos para que um registro seja selecionado. Se, por exemplo, dois dos critérios forem verdadeiros, mas um for falso, o referido registro não será selecionado.

Sintaxe

=E (Lógico1; Lógico2;...) Ex: Digamos que na coluna A você possua estes valores.

A 1 1 2 2 4 =E (A1<>10;

A2=10) A 1 1 2 2 4 VERDADEIRO

24.2. Função OU: Utilizamos a Função OU sempre que precisarmos definir dois ou mais crité-rios de pesquisa. Quando os critérios são unidos por um operador OU, bas-ta que um dos critérios seja verdadeiro para que um registro seja seleci-onado. Por exemplo, se forem definidos três critérios unidos por operadores OU e um dos critérios fosse verdadeiro, ou dois dos critérios fossem verda-deiros, ou até mesmo se os três critérios fossem verdadeiros, o registro se-ria selecionado. Um registro somente não será selecionado se todos os cri-térios unidos pelo operador OU forem falsos para o referido registro.

Mesmo que o primeiro valor lógico esteja correto, ou

seja, A1 é diferente de 10, o segundo não está, pois

A2 não é igual a 10. O valor resultante, portanto na

célula A4 será FALSO.

Agora se colocarmos a fórmula E da seguinte forma

=E(A1<>10;A2<>10), o valor resultante na célula

A4 será VERDADEIRO. Pois os dois valores são di-

ferentes de 10.

39

Sintaxe

=OU (Lógico1; Lógico2;...) Ex: Digamos que na coluna A você possua os mesmos valores do exemplo acima, ou seja.

A 1 1 2 2 4 =OU (A1<>10;

A2=10) A 1 1 2 2 4 VERDADEIRO

24.3. Função SOMASE

Sintaxe

=SOMASE (Intervalos; Critérios; Intervalo_Soma) Esta função procura em uma coluna por determinados valores, e caso en-contre o valor procurado, utiliza os valores de outra coluna para ir somando.

O primeiro valor lógico está correto, ou seja, A1 é di-

ferente de 10, mas o segundo não está, pois A2 não é

igual a 10. Mesmo assim a fórmula OU considera

que um valor tem que estar certo ou outro. Portanto

o valor resultante na célula A4 será VERDADEI-

RO.

Agora se colocarmos a fórmula de OU da seguinte

forma =OU(A1=10;A2=10), o valor resultante na cé-

lula A4 será FALSO.

40

Ex: Digamos que você deseje saber a soma dos funcionários de um empre-sa dependendo da área atuante dos mesmos.

A B C 1 Funcionário Salário Área 2 João César 350,00 Informática 3 Murilo Gomes 720,00 Gestão 4 Rosana Serra 560,00 Gestão 5 Cristiane Vieira 860,00 Informática 6 Mônica Oliveira 1250,00 Gestão 7 8 Soma Área Infor-

mática =SOMASE (C2: C6; "=Gestão”; B2: B6)

9 Soma Área Gestão =SOMASE (C2: C6; "=Informática”; B2: B6)

De acordo com a fórmula apresentada na célula B8, irá ser feito a soma dos valores dos salários dos funcionários ao qual pertencem a área de Gestão, o mesmo ocorre na célula B9, só que somando os valores dos salários da área de Informática.

41

25. Funções Estatísticas

25.1. Função Cont. Valores Esta função conta a quantidade de valores contida na lista de argumentos ou no intervalo das células especificadas como argumento. Esta função aceita de 1 a 30 argumentos. Os argumentos devem ser números, ou ma-trizes ou referências que contenham números.

Sintaxe

=CONT. VALORES (valor1; valor2; intervalo1;...) Ex: Digamos que agora você deseja obter a quantidade de funcionários existentes em sua empresa.

A B C 1 Funcionário Salário Área 2 João César 350,00 Informática 3 Murilo Gomes 720,00 Gestão 4 Rosana Serra 560,00 Gestão 5 Cristiane Vieira 860,00 Informática 6 Mônica Oliveira 1250,00 Gestão 7 8 Soma Área Informá-

tica 1210,00

9 Soma Área Gestão 2530,00 10 11 Quant. Funcionários =CONT. VALORES

(A2: A6)

De acordo com a fórmula apresentada na célula B11, irá ser feito à conta-gem de valores no intervalo A2: A6, o resultado nesse caso será 5.

25.2. Função Cont. Se

Sintaxe

=CONT. SE () Esta função conta valores de acordo com um critério definido, ou seja, de acordo com uma condição.

42

Sintaxe

=CONT. SE (Intervalo; Critérios) Ex: Digamos que você de posse da mesma planilha deseja agora contar a quantidade de funcionários de cada área, ou seja, saber quantos funcioná-rios há em cada setor de sua empresa.

A B C 1 Funcionário Salário Área 2 João César 350,00 Informática 3 Murilo Gomes 720,00 Gestão 4 Rosana Serra 560,00 Gestão 5 Cristiane Vieira 860,00 Informática 6 Mônica Oliveira 1250,00 Gestão 7 8 Soma Área Informá-

tica 1210,00

9 Soma Área Gestão 2530,00 10 11 Quant. Funcionários 5 12 Quant. Func. Gestão =CONT. SE (C2: C6;

“=Gestão”)

13 Quant. Func. Infor-mática

=CONT. SE (C2: C6; “=Informática”)

De acordo com a fórmula inserida na célula B12, irá ser feito contagem de valores no intervalo C2: C6, de acordo com o critério, ou seja, a contagem de todos os funcionários da área de Gestão, agora o mesmo pode ser feito na célula B13, nesse caso sabendo a quantidade de funcionários de infor-mática.

NOTA:

O critério deve vir sempre entre aspas, mesmo que seja um teste numérico. Por exemplo, para contar quantos valores, maiores do que 20 existem na faixa de A1 até A50, utilizamos a seguinte fórmula.

=CONT. SE (A1: A50; ">20").

26. Funções Financeiras

26.1. Fórmula PGTO (Pagamento) Retorna o pagamento periódico de uma anuidade de acordo com pagamen-tos constantes e com uma taxa de juros constante.

43

Sintaxe

=PGTO (taxa; nper; vp; vf; tipo)

• Taxa: é a taxa de juros por período. • Nper: é o número total de pagamentos pelo empréstimo. • Vp: é o valor presente—o valor total presente de uma série de paga-

mentos futuros. • Vf: é o valor futuro, ou o saldo de caixa, que você deseja obter de-

pois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0).

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

Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período

Comentários

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

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

Ex: Ao ser feito um empréstimo de R$ 2.000,00 junto a um determinado Banco, você deseja saber quanto irá pagar durante 6 meses ao mesmo pa-ra cobrir a divida, digamos que você irá pagar com um taxa de juros de 10%.

A B 1 Empréstimo 2.000,00 2 Taxa de Juros 10% 3 Período em meses 6 4 5 Valor Mensal =PGTO (B2/6; B3; -B1)

Veja que nesse exemplo só foi relatado a taxa de juros que seria cobrada por mês, o período que deveria pagar, e o valor do empréstimo, nesse caso respectivamente a TAXA, NPER e VP, ou seja, foi omitido o VF e o Tipo. Com relação ao VP, foi colocado como negativo (-), pois é pagamento.

Nesse exemplo o valor resultante na célula B5 é R$ 353,05.

44

26.2. Fórmula VP (Valor Presente) Retorna o valor presente de um investimento. O valor presente é o valor to-tal correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você pede dinheiro emprestado, o valor do empréstimo é o valor presente para quem empresta.

Sintaxe

=VP (taxa; nper; pgto; vf; tipo)

• Taxa: é a taxa de juros por período. Por exemplo, se você obtiver um empréstimo para um carro com uma taxa de juros de 10% ao ano e fizer pagamentos mensais, a sua taxa de juros mensal será 10%/12, ou 0,83%. Você deve inserir 10%/12, ou 0,83%, ou 0, 0083, na fór-mula como taxa.

• Nper: é o número total de períodos de pagamento de uma anuidade. Por exemplo, se você obtiver um empréstimo de quatro anos e fizer pagamentos mensais, o empréstimo terá 4*12 (ou 48) períodos. Você deve inserir 48 na fórmula para nper.

• Pgto: é o pagamento feito a cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros, e não há outras tarifas ou taxas. Por exemplo, os pagamentos men-sais por um empréstimo para o carro de R$ 10.000 de quatro anos a 12% são 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 um saldo de caixa, que você deseja obter de-pois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, é 0). Por exemplo, se quiser economizar R$ 50.000 para pagar um projeto es-pecial em 18 anos, então R$ 50.000 é o valor futuro. Você pode en-tão calcular a taxa de juros e determinar quanto deverá economizar a cada mês. Se vf for omitido, você deverá incluir o argumento pgto.

• Tipo: é o número 0 ou 1 e indica as datas de vencimento. Ex: Digamos que você deseja fazer um investimento para sua família, e que ao final deste investimento você deseja obter uma certa quantia, ao qual necessita ter em mãos um valor para entrada, valor este você não sabe.

A B 1 Taxa de Juros 10% 2 Período em Meses 8 3 Valor a ser depositado 100,00 4 Valor desejado ao final do In-

vestimento 5000,00

5 6 Valor Presente necessário =VP(B1;B2;B3;-B4)

45

Observe que nesse exemplo o valor desejado ao final do investimento foi colocado como negativo na fórmula, pois é um modelo de pagamento pelo investimento.

26.3. Fórmula VF (Valor Futuro) Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.

Sintaxe

=VF(taxa;nper;pgto;vp;tipo)

• 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 pode mudar durante a

vigência da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá inclu-ir o argumento vp.

• Vp: é o valor presente ou a soma total correspondente ao valor pre-sente 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 paga-mentos. Se tipo for omitido, será considerado 0.

Ex: Agora digamos que você deseja fazer um investimento, onde de posse da taxa de juros a ser cobrada, do valor a ser investido todo mês, não tendo nenhum valor presente e da quantidade de tempo que irá fazer o investi-mento você deseja saber quanto irar ter no final do mesmo.

A B 1 Taxa de Juros mensal 1,50% 2 Período em Meses 8 3 Valor a ser depositado 100,00 4 Valor Presente 5 6 Valor Futuro =VF(B1;B2;-B3;B4)

Observe que nesse exemplo o valor a ser depositado foi colocado como ne-gativo na fórmula, pois é um modelo de pagamento pelo investimento.

46

26.4. Fórmula TAXA (Taxa) Retorna a taxa de juros por período de uma anuidade. TAXA é calculado por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!.

Sintaxe

=TAXA(nper;pgto;vp;vf;tipo;estimativa)

• Nper: é o número total de períodos de pagamento em uma anuidade. • Pgto: é o pagamento feito em cada período e não pode mudar du-

rante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você de-verá 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 últi-mo 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. Ex: Digamos que você tenha que pagar um empréstimo feito, ao qual você ira pagar uma certa quantia todo mês, durante um certo período, mas não sabe qual a taxa de juros irá pagar pelo empréstimo.

A B 1 Período em meses 8 2 Valor Mensal a ser pago 600,00 3 Valor do Empréstimo 5000,00 4 5 Taxa de Juros seria =TAXA(B1;-B2;B3)

O mesmo ocorre com o valor mensal a ser pago, foi colocado com sinal ne-gativo, pois é forma de pagamento.

27. Funções De Procura

27.1. Função PROC A função PROC é muito utilizada quando se tem várias planilhas do Excel abertas, ou seja, de forma a pesquisar determinados dados em uma plani-lha e mostrar na outra. A mesma possui duas formas de procura.

47

Sintaxe

1º) =PROC(Valor procurado; matriz) Procura por intervalos, o valor específico na primeira linha ou coluna de uma matriz (tem que estar ordenada por ordem ascendente), desce ou cruza para a célula anterior, e devolve o valor da última linha ou coluna.

� Valor_procurado: é um valor que PROC localiza em uma matriz. Va-

lor_procurado pode ser um número, um texto, um valor lógico ou uma referência de nome associada a um valor.

� Matriz: é um intervalo de células que contém texto, números ou valores lógicos que se deseja comparar com o valor_procurado.

Ex: Digamos que você deseja pesquisar um determinado valor em matriz e assim resultar esse determinado valor.

A B 1 Frutas 2 Abacaxi 3 Limão 4 Maça 5 Abacate 6 7 Frutas Valor 8 =PROC(A2;$A$2:$A$5) 1,50 9 1,10 10 2,30 11 1,60

Na função foi pesquisado a célula A2 na matriz que vai do intervalo A2:A5, veja que este intervalo esta absoluto, ou seja, não se modifica quando se copia a fórmula para as demais células.

2º) =PROC(valor_procurado; vector_proc; vector_result)

• Valor_procurado: é o valor que PROC pesquisa no primeiro vetor. Valor_procurado pode ser um número, texto, um valor lógico ou um nome ou referência que se refira a um valor.

• Vetor_proc: é o intervalo que contém somente uma linha ou coluna. Os valores em vetor_proc podem ser textos, números ou valores ló-gicos.

• Vetor_result: é um intervalo que contém somente uma linha ou colu-na. Deve ser do mesmo tamanho de vetor_proc.

48

Ex: De posse de duas tabelas, digamos que você deseja procurar um de-terminado valor, buscando este valor em matriz, resultando outro dado.

A B 1 Código Frutas 2 101 Abacaxi 3 102 Limão 4 103 Maça 5 104 Abacate 6 7 Código Frutas 8 102 =PROC(A8;$A$2:$A$5;$B$2:$B$5) 9 104 10 103 11 101

Observe que agora o valor procurado na determinada matriz $A$2:$A$5, resulta um outro valor que no caso é o intervalo $B$2:$B$5.

27.2. Função ProcV Como o nome da função já mostra, é uma função de procura de valores (dados) na vertical.

Sintaxe

=PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)

• Valor_procurado: é o valor a ser localizado na primeira coluna da matriz. O Valor_procurado: pode ser um valor, uma referência ou uma seqüência de caracteres de texto.

• Matriz_tabela: é a tabela de informações em que os dados são pro-curados. Use uma referência para um intervalo ou nome de intervalo, tal como Banco de dados ou Lista.

• Núm_índice_coluna: é o número da coluna em matriz_tabela a par-tir do qual o valor correspondente deve ser retornado.

• Procurar_intervalo: é um valor lógico que especifica se você quer que PROCV encontre a correspondência exata ou uma correspon-dência aproximada.

Ex: De posse de duas tabelas, uma contendo o código dos funcionários e os nomes dos mesmos, a outra contendo apenas de código dos funcioná-rios de forma aleatória você deseja procurar o nome dos mesmos.

49

A B 1 Código Nome 2 1 Isaias Barros 3 2 Jaci Alves 4 3 Samuel Lima 5 4 Sonia Aguiar 6 5 Selma Maria 7 8 Código Nome 9 2 =PROCV(A9;$A$2:$B$6;2;0) 10 4 11 5 12 1 13 3

Depois de feita está formula pode copiar para as células abaixo. Veja pri-meiramente que a matriz_tabela ficou da seguinte forma $A$2:$B$6, ou se-ja, prendeu as células de tal forma que ficaram absolutas para não mudar de acordo quando você copiar para as demais células. Segundo, a primeira tabela é uma “matriz”, pois é composta de colunas e linhas, por isso que no núm_índice_coluna, foi colocado o número 2, representando a segunda coluna, já o número 0 no final da fórmula quer dizer que você deseja fazer uma pesquisa exata em cima da matriz_tabela.

27.3. Função ProcH De acordo com esta função a procura de valores será feita na horizontal.

Sintaxe

=PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo)

• Valor_procurado: é o valor a ser localizado na primeira coluna da matriz. O Valor_procurado: pode ser um valor, uma referência ou uma seqüência de caracteres de texto.

• Matriz_tabela: é a tabela de informações em que os dados são pro-curados. Use uma referência para um intervalo ou nome de intervalo, tal como Banco de dados ou Lista.

• Núm_índice_coluna: é o número da coluna em matriz_tabela a par-tir do qual o valor correspondente deve ser retornado.

• Procurar_intervalo: é um valor lógico que especifica se você quer que PROCH encontre a correspondência exata ou uma correspon-dência aproximada.

50

Ex: Novamente de posse de duas tabelas, uma contendo o código dos fun-cionários e os nomes dos mesmos, a outra contendo apenas de código dos funcionários de forma aleatória você deseja procurar o nome dos mesmos.

A B C 1 1 2 2 0,10 0,50 7 8 Produto Nr. Imposto Valor do Imposto 9 Arroz 1 =PROCH(B9;$A$1:$B$2;2;0) 10 Feijão 2 11 Óleo 2 12 Tomate 1 13 Abacate 1

A mesma coisa pode ser feita nesta planilha, depois de feita a formula pode copiar para as células abaixo. A matriz_tabela ficou da seguinte forma $A$1:$B$2, ou seja, prendeu as células de tal forma que ficaram absolutas para não mudar quando você copiar para as demais células. Novamente fa-lando a primeira tabela é uma “matriz”, pois é composta de colunas e linhas, por isso que no núm_índice_coluna, foi colocado o número 2, represen-tando a segunda linha, agora o número 0 no final da fórmula quer dizer que você deseja fazer uma pesquisa exata em cima da matriz_tabela.

28. Filtro E Auto Filtro

Filtrar é uma maneira rápida e fácil de localizar e trabalhar com um subcon-junto de dados em uma lista, ou seja, quando trabalhamos com listas muito extensas, com milhares de linhas, pode haver a necessidade de “filtrar” a lis-ta, para que sejam exibidas somente as linhas onde um ou mais campos atendam determinados critérios. Uma listra filtrada exibe somente as linhas que atendem aos critérios especificados para uma coluna. O Microsoft Excel fornece dois comandos para filtrar listas:

• AutoFiltro, que inclui filtrar por seleção, para critérios simples • Filtro avançado, para critérios mais complexos

28.1. AutoFiltro Ex: Digamos que de posse de uma lista de dados muito extensa, você de-seja filtrar alguns dados, como o exemplo abaixo.

A B C D 1 Código Pro-

duto Nome do Produto Tipo Preço de

Venda 2 5X-2 Arroz Cereal 8,20

51

3 14M Abacate Fruta 2,50 4 6540-X Abóbora Cabutia Verdura 3,70 5 X4501 Maça Argentina Fruta 2,30 6 W501 Maça Nacional Fruta 2,10 7 86401B Laranja Fruta 1,30 8 9681T Tomate Fruta 1,60 9 HJK25 Cenoura Verdura 1,80 10 101KM Repolho Verde Verdura 1,35 11 9825G Repolho Roxo Verdura 1,20 12 JKOE Batata Doce Verdura 0,80 13 AKB15 Batata Verdura 0,75 14 6540KG Mandioca 1,35 15 98746 Abacaxi Fruta 1,10 16 231LOG Limão Fruta 0,75 17 6564C Limão China Fruta 0,90 18 3631K Goiaba Fruta 1,15 19 LPQIS Jiló Verdura 0,65 20 LASG1 Banana Maça Fruta 1,10 21 21321H Banana Prata Fruta 1,25 22 9857L Banana Nanica Fruta 1,15 23 PO965 Brócolis Verdura 2,10

Observe que de acordo com o nosso exemplo, a lista um pouco extensa. Digamos que agora você deseja filtrar os dados contidos nesta lista.

Para utilizar o comando Auto Filtro, selecione os títulos dos campos da sua lista, depois clique no menu Dados, escolha a opção Filtra depois clique so-bre a opção Auto Filtro.

Observe que ao lado dos títulos dos campos de sua lista, apareceu setas indicando que você poderá filtrar qualquer campo que desejar.

Ao aparecer as setas ao lado de cada campo clique sobre qualquer uma das setas e ira aparecer uma listagem com todos os dados daquela coluna.

Veja que a figura ao lado, está de acordo com o nos-

so exemplo, mostrando todas as opções de código de

produto, ao clicar sobre algum código, observe que

foi mostrada a linha referente a este código, o produ-

to, o tipo e o preço de venda.

Observe também que a seta ao qual você filtrou os

dados ficou na cor azul.

Se você desejar a mostrar todos os dados novamen-

te, basta apenas clicar sobre a seta e escolher a op-

ção (Tudo).

Agora se desejar retirar o filtro colocado, clique so-

bre o menu Dados novamente, escolha opção Filtro,

observe que a opção Auto Filtro está marcada, basta

apenas clicar sobre a opção que automaticamente ira

desaparecer o filtro sobre a lista de dados.

52

28.2. Personalizar AutoFiltro Na janela Personalizar AutoFiltro podemos definir condições personaliza-das, para faixas de valores, como por exemplo, valores numéricos e faixas de datas. Existe uma lista de operadores pré-definidos, conforme indicado a seguir:

• é igual a • é diferente de • é maior do que • é maior ou igual a • é menor do que • é menor ou igual a

Ex: Digamos que você deseja personalizar o filtro, vamos tomar como base no exemplo anterior, ou seja, de posse da lista de dados abaixo e também já ter colocado a opção AutoFiltro, você deseja que filtre apenas os valores que são maiores que R$ 5,00.

53

A B C D 1 Código Pro-

duto Nome do Produto Tipo Preço de

Venda 2 5X-2 Arroz Cereal 8,20 3 14M Abacate Fruta 2,50 4 6540-X Abóbora Cabutia Verdura 3,70 5 X4501 Maça Argentina Fruta 2,30 6 W501 Maça Nacional Fruta 2,10 7 86401B Laranja Fruta 1,30 8 9681T Tomate Fruta 1,60 9 HJK25 Cenoura Verdura 1,80 10 101KM Repolho Verde Verdura 1,35 11 9825G Repolho Roxo Verdura 1,20 12 JKOE Batata Doce Verdura 0,80 13 AKB15 Batata Verdura 0,75 14 6540KG Mandioca 1,35 15 98746 Abacaxi Fruta 1,10 16 231LOG Limão Fruta 0,75 17 6564C Limão China Fruta 0,90 18 3631K Goiaba Fruta 1,15 19 LPQIS Jiló Verdura 0,65 20 LASG1 Banana Maça Fruta 1,10 21 21321H Banana Prata Fruta 1,25 22 9857L Banana Nanica Fruta 1,15 23 PO965 Brócolis Verdura 2,10

Para executar está ação, clique na seta ao lado do preço de vendas e esco-lha a opção Personalizar, será exibida a janela Personalizar AutoFiltro, con-forme indicado na Figura:

Observe que está escrito “Mostrar linhas onde: Preço Venda”, ou seja, de acordo com o nosso exemplo nos iremos personalizar o campo Preço de Venda, na primeira caixa está escrito é igual a, clique na seta ao lado e mude para é maior do que, na segunda caixa digite 5, depois clique em Ok,

54

veja que foi mostrado apenas produtos que tiverem valores maior que R$ 5,00.

28.3. Filtro Avançado O comando Filtro avançado pode filtrar uma lista no lugar como o comando AutoFiltro, mas ele não exibe listas suspensas relacionadas às colunas. Em vez disso, você digita os critérios pelos quais você filtrar em um intervalo de critérios separado acima da lista. Um intervalo de critérios permite a fil-tragem de critérios mais complexos.

Ex: Digamos que de posse de duas lista de valores você deseja especificar em uma delas o critério ao qual deseja filtrar.

A B C D 1 Vendedor Região Tipo Vendas 2 Andrade Leste Bebidas 5122 3 Andrade Norte Laticínios 6239 4 Amaral Sul Carnes 450 5 Amaral Sul Carnes 7673 6 Pereira Sul Carnes 6596 7 Andrade Oeste Bebidas 386 8 Pereira Leste Bebidas 6328 9 Amaral Sul Laticínios 6544 10 Andrade Leste Laticínios 5416 11 12 Vendas Vendas 13 >1000 <6000

Observe que nas células A12 e B12, foi digitado a palavra Vendas, um campo idêntico a primeira lista do nosso exemplo, só que nas células A13, foi digitado maior 1000 e B13, menor que 6000, nesse caso, a nossa pes-quisa deverá filtrar valores maiores 1000 e valores menores 6000, ou seja, neste intervalo.

Para executar o filtro avançado, clique no menu Dados, escolha a opção Filtro, depois clique sobre a opção filtro avançado, será exibida a janela de Filtro avançado conforme a figura abaixo:

55

Observe as opções acima disponíveis:

• Filtrar lista no local: Mostra os dados filtrados na própria planilha • Copiar para outro local: copias as informações filtradas para outro

local. • Intervalo da lista: Corresponde ao intervalo dos dados incluindo os

títulos. • Intervalo de critérios: corresponde ao intervalo onde estamos digi-

tando as informações. De acordo com nosso exemplo iremos deixar a primeira opção marcada, ou seja, filtrar lista no local, observe que tanto no campo intervalo da lista quanto no intervalo de critérios, há uma seta vermelha ao lado, indicando que você pode selecionar os intervalos.

Ao se clicar tanto na seta do intervalo da lista quanto na seta do intervalo de critérios, você deverá selecionar os intervalos correspondentes.

No nosso caso o intervalo da lista será o intervalo “ Plan1!$A$1:$D$10 ”, agora o intervalo de critérios deverá ser “Plan1!$A$12:$B$13 ”, de acordo com a figura abaixo.

Basta agora clicar em Ok, observe os dados foram filtrados na própria plani-lha.

29. Data E Hora

Vamos apresentar a maneira como o Excel armazena e trabalha com valo-res de datas e horas.

29.1. Como o Excel trabalha com datas e horas

56

O Excel fornece uma série de funções para facilitar o trabalho com valores de data e hora. Podemos utilizar as funções de data e hora para fazer, den-tre outros, os seguintes cálculos:

� Calcular o número de dias, meses ou anos entre duas datas. � Fazer operações de soma e subtração com datas e horas. Antes de aprendermos a utilizar as funções de datas e horas é importante entendermos como o Excel armazena os valores de data e hora.

29.2. Como o Excel Registra datas e horas: A unidade básica do tempo no Microsoft Excel é o dia. Cada dia é represen-tado por um valor numérico, de data seqüencial, que varia de 1 a 65380. A data base representada pelo valor 1, é Sábado, 1º de Janeiro de 1900. O valor seqüencial máximo, 65380, representa 31 de Dezembro de 2078. Quando você introduz uma data em sua planilha, o Excel registra a data como um valor seqüencial que representa o número de dias entre a data-base (01/01/1900) e a data digitada. Por exemplo, a data 30/09/1970 é ar-mazenada, internamente, pelo Excel como: 25841. O que significa este nú-mero? Significa que entre 01/01/1900 e 30/09/1970 passaram-se 25841 di-as.

A hora do dia é um valor decimal que representa a parte (fração) de um dia entre seu início - meia-noite - e a hora especificada. Meio-dia, por exemplo, é representado pelo valor 0,5, porque a diferença entre a meia-noite e o meio-dia é exatamente meio dia. A combinação hora/data 10:30:15, 10 de Julho de 2001, é representada pelo valor: 37082,4376736111. Observe a parte fracionários 0,43767. Esta parte representa o percentual do dia que já foi transcorrido. 10:30:15 representa 43,76 % do total de 24 horas do dia.

Ao atribuir valores seqüenciais a dias, horas, minutos e segundos, o Excel possibilita que você execute cálculos aritméticos sofisticados com data e ho-ra. Você pode manipular datas e horas em suas fórmulas de planilha da mesma forma que manipula outros tipos de valores numéricos.

29.3. Digitando datas e horas: Embora os valores de datas e horas sejam armazenados na forma numéri-ca, conforme descrito anteriormente, podemos digitar datas e horas no for-mato tradicional. Por exemplo:

Datas: 01//05/2001

Horas: 16:25:18

Para digitar datas e horas basta clicar na célula e digitar a data ou hora, no formato desejado. É importante salientar que o Excel aceita datas em for-matos variados. Por exemplo, a data 01/04/2001 também pode ser digitada no formato 01-04-2001 ou 01-abr-2001 ou 01 abril, 2001. Para horas tam-

57

bém podemos ter formatos variados. Por exemplo, a hora 10:25, neste caso será considerado 0 segundos.

DICA: Você pode inserir a data atual em uma célula ou fórmula, facilmente, pressionando simultaneamente a tecla Ctrl e a tecla de ponte-e-vírgula (;) - Ctrl+; . A data é inserida no formato dd/mm/aaaa.

DICA: Você pode inserir a hora atual em uma célula ou fórmula, facilmente, pressionando simultaneamente a tecla Ctrl, a tecla Shift e a tecla de dois-pontos (:). A hora é inserida no formato hh:mm.

29.4. Trabalhando com Datas e Horas As funções de data e hora do Microsoft Excel permitem que você execute cálculos de planilha rápida e precisamente. Por exemplo, se você usar sua planilha para calcular a folha de pagamento mensal de sua empresa, pode-ria usar a função HORA()para determinar o número de horas trabalhadas diariamente e a função DIA.DA.SEMANA() para determinar se os emprega-dos devem receber de acordo com o salário padrão (para o período de se-gunda a sexta-feira) ou a uma taxa de horas extras (para sábados e domin-gos).

29.5. Função Hoje() Sintaxe: Hoje()

Argumentos: Nenhum

A função Hoje() retorna a data atual do sistema. A data é inserida no forma-to dd/mm/aaaa. Por exemplo, para inserir a data atual em uma célula, basta digitar a seguinte fórmula: =Hoje()

Mas porque utilizar a função Hoje() e não digitar a data diretamente? A van-tagem da função Hoje() é que ela atualiza o valor da data na célula, toda vez que a planilha for aberta no Excel. Com o uso da função Hoje() teremos sempre a data atualizada.

29.6. Função Hora() Sintaxe: Hora(número)

ou

Hora("hh:mm:ss")

Argumentos: número é um valor que representa o percentual do dia, já transcorrido. Por exemplo, considere a fórmula:

=Hora(0,5), retorna o valor 12. Por que? 0,5 é a metade do dia, o que cor-responde a, exatamente 12 horas.

58

E a fórmula: =Hora(0,7), o que retorna? Retorna 16, pois 70% do dia seriam 16 horas e 48 minutos. A função hora retorna apenas a parte inteira da ho-ra.

Também podemos passar um valor de hora, como parâmetro para a função Hora(). Exemplo:

=Hora("16:12:35")

Neste caso a função retorna apenas o valor da hora. Um detalhe importante é que ao passarmos uma hora como parâmetro, devemos colocar este pa-râmetro entre aspas, conforme indicado no exemplo anterior.

29.7. Função Agora() Sintaxe: Agora()

Argumentos: Nenhum

A função Agora() retorna a data e hora do sistema. A data é inserida no formato dd/mm/aaaa e a hora no formato hh:mm. Por exemplo, para inserir a data e hora atual em uma célula, basta digitar a seguinte fórmula:

=Agora()

Mas porque utilizar a função Agora() e não digitar a data e hora diretamen-te? A vantagem da função Agora() é que ela atualiza o valor da data e da hora, toda vez que a planilha for aberta. Com o uso da função Agora() te-remos sempre a data e hora atualizadas.

29.8. Função DIA.DA.SEMANA() Sintaxe: DIA.DA.SEMANA("dd/mm/aa";tipo_retorno)

ou

DIA.DA.SEMANA(número;tipo_retorno)

Argumentos: Uma data entre aspas ou o número seqüencial que represen-ta uma data.

A função DIA.DA.SEMANA retorna um número inteiro, que varia entre 1 e 7. O número representa o dia da semana. O argumento tipo_retorno é opcio-nal e determina a maneira pela qual o resultado é interpretado. Se ti-po_retorno for 1 ou omitido, a função retornará um número entre 1 e 7, onde 1 representa o domingo e 7 representa o sábado. Se tipo_retorno for 2, a função retornará um número entre 1 e 7, onde 1 é segunda-feira e 7 é do-mingo. Se tipo_retorno for 3, a função retornará um número entre 0 e 6, on-de 0 é segunda feira e 6 é domingo.

59

Exemplo:

Na coluna A temos valores de datas a partir de 23/09/2001, que cai em um Domingo. Na coluna B utilizamos a fórmula =DIA.DA.SEMANA(A2;1). Ob-serve que definimos o parâ-metro tipo_retorno como sen-do igual a 1. Neste caso o domingo é o dia 1 e o sábado

o dia 7, conforme pode ser confirmado pelos resultados. Na coluna C utili-zamos a fórmula =DIA.DA.SEMANA(A2;2). Observe que o domingo é o dia 7 e a segunda-feira o dia 1, conforme pode ser confirmado pelos resultados. Na coluna D utilizamos a fórmula =DIA.DA.SEMANA(A2;3). Observe que o domingo é o dia 6 e a segunda-feira o dia 0, conforme pode ser confirmado pelos resultados.

29.9. Função Dia() Sintaxe: Dia(Data)

Argumentos: Recebe uma data ou o ende-reço de uma célula onde existe um valor do tipo data e retorna um número inteiro, cor-respondente ao dia da data passada como parâmetro.

Exemplo:

Na coluna B, utilizamos a função =Dia(A2), a qual retorna apenas o dia da data contida na coluna A, conforme pode ser confirmado pelos resultados obtidos.

29.10. Função Mês(Data) Sintaxe: Mês(Data)

Argumentos: Recebe uma data ou o endereço de uma célula onde existe um valor do tipo data.

Exemplo:

Na coluna B, utilizamos a função =Mês(A2), a qual retorna apenas o mês da data contida na coluna A, conforme pode ser confirmado pe-

los resultados obtidos.

60

Importante: A função mês deve ser digitada com o acento, caso contrário será gerada uma mensagem de erro.

29.11. Função Ano() Sintaxe: Ano(Data)

Argumentos: Recebe uma data ou o ende-reço de uma célula onde existe um valor do tipo data.

Exemplo:

Na coluna B, utilizamos a função =Ano(A2), a qual retorna apenas o Ano da data contida na coluna A, conforme pode ser confirmado pe-los resultados obtidos.

29.12. Função DATA.VALOR () Sintaxe: DATA.VALOR("dd/mm/aa")

Argumento: Uma data entre aspas.

A função DATA.VALOR recebe como argumento uma data e retorna o valor seqüencial (número) utilizado internamente pelo Excel para armazenar a Data. O argumento passado para a função deve ser uma data entre 01/01/1990 e 31/12/2078 em qualquer um dos formatos definidos pelo Ex-cel.

Exemplo:

=DATA.VALOR()

Esta função ira retornar 44196. Isto significa que entre 01/01/1900 e 31/12/2020 teremos 44196 dias.

29.13. Função Hora() Sintaxe: Hora(hh:mm:ss)

Argumento: Recebe uma valor de hora completa (hh:mm:ss) ou o endereço de uma célula onde existe um valor do tipo ho-ra.

Exemplo:

Na coluna B, utilizamos a função =Hora(A2), a qual retorna apenas o va-lor da hora contida na coluna A, conforme pode ser confirmado pelos resul-tados obtidos.

61

29.14. Função Minuto() Sintaxe: Minuto(hh:mm:ss)


Exemplo:

Na coluna B, utilizamos a função =Minuto(A2), a qual retorna apenas o valor dos minutos da hora completa contida na coluna A, conforme pode ser confirmado pelos resultados obtidos.

29.15. Função Segundo() Sintaxe: Segundo(hh:mm:ss)


Exemplo:

Na coluna B, utilizamos a função =Segundo(A2), a qual retorna apenas o valor dos segundos da hora completa contida na coluna A, conforme pode ser confirmado pelos resultados obtidos.

30. Vínculos Com Outras Planilhas Ou Arquivos

Podemos vincular uma célula a uma outra célula localizada em outra plani-lha ou arquivo.

Ex.: Na planilha montada para obter o resultado final do desempenho da empresa, podemos buscar de outras planilhas os dados específicos, como por exemplo, o total de faturamento da planilha de vendas, o estoque final da planilha de controle de estoques, etc.

Para vincular uma célula a outra o procedimento é bem simples.

Primeiro temos que abrir todos os arquivos que vamos buscar as informa-ções.

Estes arquivos devem aparecer relacionados quando selecionamos o menu Janela.

Na célula A1 da planilha atual queremos buscar o total de vendas da plani-lha Faturamento. Para isto, basta que posicionemos o cursor na planilha atual em A1, digitar = e com o mouse clicar em Janela, selecionar o arquivo

62

Faturamento, e clicar na célula que consta o valor que queremos buscar, e por final basta dar um Enter.

Pronto, neste momento na planilha atual aparece o valor constante lá na planilha de Faturamento. Sempre que for alterado o valor de faturamento, automaticamente será atualizado na planilha que criamos, criando assim um vínculo. Observe a fórmula que deverá ser parecido com o exemplo abaixo:

=[Faturamento.xls]Plan1!$A$4

No arquivo Faturamento.xls, na planilha Plan1, na célula A4, consta o valor que queremos buscar.

Pode-se digitar diretamente a fórmula caso saibamos antes o endereço completo, mas deve-se tomar cuidado para respeitar as regras que o Excel

exige. Por exemplo, deve-se começar com o sinal de ==, o nome do arqui-vo deve estar entre colchetes [ ], e logo após o nome da planilha e antes da célula, deve constar o sinal de exclamação.

31. Procv

A função PROCV é usada para pesquisar no banco de dados uma informa-ção baseada em uma chave de pesquisa. Por exemplo, qual o preço de um determinado produto identificado por uma referência ou modelo.

Em primeiro lugar, devemos identificar a base de dados definindo um nome.

Por exemplo, em um arquivo temos duas planilhas, uma com os dados e a outra onde vamos colocar a fórmula PROCV. Na plan2 temos as seguintes informações:

Modelo Descrição Cor Valor 10 BMW Branco 45.000,00 20 MERCEDES Azul 50.000,00 30 FERRARI Vermelha 150.000,00

Estas informações estão no intervalo A2 até D7, pois o título não contamos como informações.

Para definir um nome para este intervalo deve-se selecionar o menu Inse-rir/Nome/Definir. Defina o nome TABELA para esta região.

Agora sempre que nos referimos ao nome TABELA, o Excel entende que são as informações constantes em plan2!A2:D7.

Na planilha plan1 estamos montando um cadastro de pedidos onde digita-remos o modelo e automaticamente deverá buscar a descrição, cor e valor, ficando apenas o campo Qtd para digitar.

63

Modelo Descrição Cor Valor Pedido Qtd Valor Total

= PROCV ( CHAVE; TABELA; COLUNA; 0 OU 1)

Chave é a informação em comum nas duas planilhas, sendo que na tabela necessariamente deve ser a primeira coluna para que o Excel possa pes-quisar.

Tabela é o nome que definimos para o nosso banco de dados de informa-ções.

Coluna é a coluna onde está a informação que queremos buscar. Por exemplo, se queremos o valor, encontra-se na nossa tabela na coluna 4.

0 (exato) ou 1 (parecido) serve para que o Excel busque informações exa-tas ou parecidas. Por exemplo, se buscarmos por Josué e consta 0 (exato) na fórmula, somente será válido Josué. Se na fórmula consta 1 (parecido), poderá ser José que é parecido com Josué.

Outra curiosidade, se optarmos por 0 (exato) Josué e Josue são diferentes para o Excel, pois um tem o assento e o outro não.

No exemplo que vamos montar, queremos buscar a descrição do produto.

Na nossa planilha a chave é o Modelo que consta na célula A2.

= PROCV ( A2; TABELA; 2; 0 )

Baseado na chave em A2, pesquisar na tabela a coluna 2 que é a descri-ção, sendo que deve ser exata a informação.

32. PROCV Com CONCATENAR (Duas Chaves)

Quando a chave para pesquisa for mais que uma, por exemplo, um pedido de calçados que para cada tamanho de um mesmo modelo existe um preço diferente, precisamos usar a função CONCATENAR.

A nossa base de dados será a seguinte:

64

Chave Modelo Tamanho Cor Valor 1033 10 33 Branco 37,50

1034 10 34 Preto 41,20

2033 20 33 Branco 38,41 2034 20 34 Preto 45,74 3033 30 33 Branco 50,25 3034 30 34 Preto 55,00

Note que para um mesmo modelo, mas com tamanhos diferentes, temos chaves diferentes. O modelo 10 com tamanho 33 a chave é 1033 e o mode-lo 10 com tamanho 34 a chave é 1034. Isto porque juntamos (CONCATE-NAR) duas células para formar uma.

Ex.: =CONCATENAR(A2;B2) ou =A2&B2

Para esta base de dados definimos um nome como TABELA1.

Modelo Tamanho Cor Valor Pedido Qtd Valor Total 10 33 Branco 37,50 2545 5 187,50 10 34 Preto 41,20 2546 15 618,00 20 33 Branco 38,41 2547 4 153,64 20 34 Preto 45,74 2548 9 411,66 30 33 Branco 50,25 2549 6 301,50 30 34 Preto 55,00 2550 7 385,00

Na planilha de pedidos agora a chave de pesquisa passa a ser duas, o mo-delo e o tamanho, pois na base de dados foi esta a chave que definimos pa-ra diferenciar dentro de um mesmo modelo o preço de outros tamanhos.

Em A2 temos o modelo e B2 o tamanho. Sendo assim a fórmula para bus-car o valor será a seguinte:

= PROCV ( A2&B2 ; TABELA1; 5 ; 0 )

Baseado na chave em A2 e B2, pesquisar na tabela1 a coluna 5 que é o va-lor, sendo que deve ser exata a informação.

TABE-

LA1

PLANILHA

PEDIDOS

65

33. Uso Do Comando FILTRAR

Em um banco de dados podemos filtrar informações.

Pedido Cliente Ref. Preço Unitário Pares Total R$

2555 Beira Rio 21 2,50 12.541 31.352,50 2548 Musa 14 3,50 6.500 22.750,00 2561 Azaléia 27 7,80 3.251 25.357,80 2547 Beira Rio 10 9,60 5.400 51.840,00 2556 Beira Rio 22 5,20 1.200 6.240,00 2560 Bibi 26 3,60 3.220 11.592,00 2553 Bibi 19 4,20 2.355 9.891,00 2554 Bibi 20 1,20 1.254 1.504,80 2549 Beira Rio 15 1,20 5.200 6.240,00 40.921 166.768,10

Por exemplo, na lista acima, queremos mostrar na tela apenas as informa-ções do cliente Beira Rio.

Pedido Cliente Ref. Preço Unitário

Pares Total R$

2555 Beira Rio 21 2,50 12.541 31.352,50 2547 Beira Rio 10 9,60 5.400 51.840,00 2556 Beira Rio 22 5,20 1.200 6.240,00 2549 Beira Rio 15 1,20 5.200 6.240,00

Este comando está disponível no menu Dados/Filtrar/Auto Filtro.

Quando selecionado esta opção, o Excel coloca em cada campo no título um drop-down que quando ativado, mostra o conteúdo da coluna, podendo escolher uma informação a ser filtrada. E sempre que um filtro estiver ativo o drop-down correspondente aparecerá em azul indicando que neste campo foi feito um filtro.

66

Também é possível personalizar o filtro. Exemplo, filtrar campo Total R$ on-de o valor é maior que R$ 10.000,00.

Pedido Cliente Ref. Preço Unitário Pares Total R$

2555 Beira Rio 21 2,5 12.541 31.352,50 2548 Musa 14 3,5 6.500 22.750,00 2561 Azaléia 27 7,8 3.251 25.357,80 2547 Beira Rio 10 9,6 5.400 51.840,00 2560 Bibi 26 3,6 3.220 11.592,00

34. Função SUBTOTAL No Modo Filtrar

Em um banco de dados que contém valores ou quantidades, normalmente existe um total para estes dados.

Pedido Cliente Ref. Preço Unitário

Pares Total R$

2555 Beira Rio 21 2,5 12.541 31.352,50 2548 Musa 14 3,5 6.500 22.750,00 2561 Azaléia 27 7,8 3.251 25.357,80 2547 Beira Rio 10 9,6 5.400 51.840,00 2556 Beira Rio 22 5,2 1.200 6.240,00 2560 Bibi 26 3,6 3.220 11.592,00 2553 Bibi 19 4,2 2.355 9.891,00 2554 Bibi 20 1,2 1.254 1.504,80 2549 Beira Rio 15 1,2 5.200 6.240,00 40.921 166.768,10

Quando filtramos algum campo, como por exemplo, o cliente Beira Rio, o objetivo é saber no nosso exemplo, o total de pares e valor para este clien-te, mas como já tinha os totais antes de filtrar, não irá funcionar, pois a fun-ção que usamos no total de pares e valor foi =SOMA.

Para que no modo filtrar possamos analisar os totais somente dos dados fil-trados, usamos a função SUBTOTAL.

Para um banco de dados onde sabemos que vamos usar o modo filtrar da-dos, deixamos para criar a soma dos totais somente depois de feito um pri-meiro filtro, usando o botão autosoma ou montando a fórmula manualmente.

Observe no quadro as opções que podemos usar na função SUBTOTAL.

67

No nosso exemplo, a região que contém os valores totais é F2:F10. Para tanto, em vez de usarmos =SOMA(F2:F10), usamos =SUBTOTAL(9,F2:F10). Como para este exemplo queremos a soma dos valores filtrados, usamos a opção 9 conforme mostrado no quadro acima.

Pedido Cliente Ref. Preço

Unitário Pares Total R$

2555 Beira Rio 21 2,5 12.541 31.352,50 2547 Beira Rio 10 9,6 5.400 51.840,00 2556 Beira Rio 22 5,2 1.200 6.240,00 2549 Beira Rio 15 1,2 5.200 6.240,00 24.341 95.672,50

35. Funções Matemáticas

Função ARRED Arredonda um número até uma quantidade especificada de dígitos.

68

Sintaxe

ARRED(núm;núm_dígitos)

Núm é o número que você deseja arredondar.

Núm_dígitos especifica o número de dígitos para o qual você deseja arre-dondar núm.

Comentários

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

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

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

Exemplo

Fórmula Descrição (resultado)

=ARRED(2,15; 1) Arredonda 2,15 para uma casa decimal (2,2)

=ARRED(2,149; 1) Arredonda 2,149 para uma casa decimal (2,1)

=ARRED(-1,475; 2) Arredonda -1,475 para duas casas decimais (-1,48)

=ARRED(21,5; -1) Arredonda 21,5 para uma casa à esquerda da vírgula decimal (20)

Função INT Arredonda um número para baixo até o número inteiro mais próximo.

Sintaxe

INT(núm)

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

69

Função PAR Retorna o núm arredondado para o inteiro par mais próximo. Esta função pode ser usada para processar itens que aparecem em pares. Por exemplo, um engradado aceita fileiras de um ou dois itens. O engradado está cheio quando o número de itens, arredondado para mais até o par mais próximo, preencher sua capacidade.

Sintaxe

PAR(núm) Núm é o valor a ser arredondado.

Comentários

• Se núm não for numérico, PAR retornará o valor de erro #VALOR!. • Independentemente do sinal de núm, um valor será arredondado

quando for diferente de zero. Se núm for um inteiro par, não haverá arredondamento.

Exemplo

Fórmula Descrição (resultado) =PAR(1,5) Arredonda 1,5 para cima para o número inteiro par mais próxi-

mo (2) =PAR(3) Arredonda 3 para cima para o número inteiro par mais próximo

(4) =PAR(2) Arredonda 2 para cima para o número inteiro par mais próximo

(2) =PAR(-1) Arredonda -1 para cima para o número inteiro par mais próxi-

mo (-2)

A

1 Dados

2 19,5

Fórmula Descrição (resultado)

=INT(8,9) Arredonda 8,9 para baixo (8)

=INT(-8,9) Arredonda -8,9 para baixo (-9)

=A2-INT(A2)

Retorna a parte decimal de um número real positivo na célula A2 (0,5)

70

Função ÍMPAR

Devolve o número arredondado por excesso para o número inteiro ímpar mais próximo.

Sintaxe

ÍMPAR(núm)

Núm é o valor que se pretende arredondar.

Observações

• Se núm não for numérico, ÍMPAR devolve o valor de erro #VALOR!. • Independentemente do sinal de núm, um valor é arredondado por ex-

cesso quando está longe do zero. Se núm for um inteiro ímpar, não é arredondado.

Exemplo

Fórmula Descrição (Resultado)

=ÍMPAR(1,5) Arredonda 1,5 para o número ímpar mais próximo (3)

=ÍMPAR(3) Arredonda 3 para o número ímpar mais próximo (3)

=ÍMPAR(2) Arredonda 2 para o número ímpar mais próximo (3)

=ÍMPAR(-1) Arredonda -1 para o número ímpar mais próximo (-1)

=ÍMPAR(-2) Arredonda -2 para o número ímpar mais próximo (-3)

Função ARREDONDAR.PARA.CIMA

Arredonda um número para cima afastando-o de zero.

Sintaxe

ARREDONDAR.PARA.CIMA(núm;núm_dígitos)

71

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.

Função ARREDONDAR.PARA.BAIXO

Arredonda um número para baixo até zero.

Sintaxe

ARREDONDAR.PARA.BAIXO(núm;núm_dígitos)

Comentários

• As funções funcionam de maneira semelhante. • ARREDONDAR.PARA.CIMA ou ARREDONDAR.PARA.BAIXO funci-

ona como ARRED, com a diferença de sempre arredondar um núme-ro para cima.

• Se núm_dígitos for maior do que 0, então o número será arredonda-do para cima/baixo pelo número de casas decimais especificado.

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

• Se núm_dígitos for menor do que 0, então o número será arredonda-do para cima/baixo à esquerda da vírgula decimal.

72

Exemplo

Fórmula Descrição (resultado) =ARREDONDAR.PARA.CIMA(3,2;0) Arredonda 3,2 para cima,

para zero casa decimal (4) =ARREDONDAR.PARA.CIMA(76,9;0) Arredonda 76,9 para cima,

para zero casa decimal (77)

=ARREDONDAR.PARA.CIMA(3,14159; 3) Arredonda 3,14159 para cima, para três casas de-cimais (3,142)

=ARREDONDAR.PARA.CIMA(-3,14159; 1) Arredonda -3,14159 para cima, para uma casa de-cimal (-3,2)

=ARREDONDAR.PARA.CIMA(31415,92654; -2)

Arredonda 31415,92654 para cima, para 2 casas à esquerda da vírgula deci-mal (31500)

36. Banco De Dados

O banco de dados do Excel não é nenhuma função específica e sim trata-se das informações constantes organizadas por colunas, sendo a primeira li-nha o nome dos campos do banco de dados.

Nome Endereço Cidade CEP Estado Telefone

Com as informações digitadas no banco de dados, podemos usá-las para vários fins, como uma mala direta, tabela dinâmica, pesquisa, etc.

37. Funções De Banco De Dados

O Microsoft Excel inclui funções de planilha que analisam os dados arma-zenados em listas ou bancos de dados. Cada uma dessas funções, citadas genericamente como BDfunções, usa três argumentos: banco de dados, campo e critérios. Esses argumentos se referem aos intervalos de planilha usados pela função.

73

Sintaxe

=BDFunção(banco_dados;campo;critérios)

Banco_Dados: É o intervalo de células que constitui a lista ou o banco de dados. No Microsoft Excel, um banco de dados é uma lista de dados relaci-onados em que as 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. A referência pode ser inserida como um intervalo de células ou como um nome representando o intervalo que contém a lista.

Campo: Indica a coluna que será usada na função. As colunas de dados na lista devem ter um rótulo de identificação na primeira linha. O campo pode ser dado como texto com o rótulo de coluna entre aspas, como "Idade" ou "Rendimento".

Critérios: É uma referência a um intervalo de células que especifica as condições para a função. A função retorna informações da lista que aten-dem às condições especificadas no intervalo de critérios. O intervalo de cri-térios inclui uma cópia do rótulo da coluna na lista para a coluna que você deseja que a função resuma. A referência de critérios pode ser inserida co-mo um intervalo de células como A1:F2.

Função BDMÁX: Esta função é utilizada para retornar o maior número em uma coluna de uma lista ou banco de dados que coincidir com as condições especificadas por você.

Sintaxe

=BDMáx(banco_dados;campo;critérios)

Função BDMÍN: Esta função é utilizada para retornar o menor número em uma coluna de uma lista ou banco de dados que coincidir com as condições especificadas por você.

Sintaxe

=BDMín(banco_dados;campo;critérios)

74

Função BDMÉDIA:

A função BDMÉDIA é utilizada para calcular a média dos valores em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas por você. Ou seja, usamos a função BDMÉDIA para calcular a média dos valores de uma coluna, valores estes que coincidem com um cri-tério especificado.

Sintaxe

=BDMédia(banco_dados;campo;critérios)

Função BDSOMA: Esta função é utilizada para somar os números em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas por vo-cê.

Sintaxe

=BDSoma(banco_dados;campo;critérios)

Ex Geral: Na planilha abaixo, constitui-se duas tabelas, onde a primeira contendo as salas de um colégio qualquer, e a segunda contendo os alu-nos, suas respectivas salas e suas notas. O que queremos saber realmente é a maior nota de cada sala, a menor nota, a média de notas de cada sala e a soma de notas de cada sala.

75

A B C 1 SALA SALA SALA 2 A B C 3 4 ALUNOS SALA NOTA 5 Paulo B 7,0 6 Mariana C 8,5 7 Lucia A 9,5 8 Cristiano C 4,0 9 Roberto A 6,5 10 Rita B 3,5 11 12 Maior Nota Sala A =BDMáx(A4:C10;"NOTA";A1:A2) 13 Maior Nota Sala B =BDMáx(A4:C10;"NOTA";B1:B2) 14 Maior Nota Sala C =BDMáx(A4:C10;"NOTA";C1:C2) 15 16 Menor Nota Sala A =BDMín(A4:C10;"NOTA";A1:A2) 17 Menor Nota Sala B =BDMín(A4:C10;"NOTA";B1:B2) 18 Menor Nota Sala C =BDMín(A4:C10;"NOTA";C1:C2) 19 20 Média Notas Sala A =BDMédia(A4:C10;"NOTA";A1:A2) 21 Média Notas Sala A =BDMédia(A4:C10;"NOTA";B1:B2) 22 Média Notas Sala A =BDMédia(A4:C10;"NOTA";C1:C2) 23 24 Soma Notas Sala A =BDSoma(A4:C10;"NOTA";A1:A2) 25 Soma Notas Sala B =BDSoma(A4:C10;"NOTA";B1:B2) 26 Soma Notas Sala C =BDSoma(A4:C10;"NOTA";C1:C2)

Nesta fórmula o nosso banco_dados é o intervalo A4:C10, onde contem to-dos os dados necessários para o desenvolvimento da terceira tabela, o campo que queremos saber é a nota, observe que este campo está entre aspas ( “NOTA” ), agora o critério é o intervalo A1:B2, ou seja, a sala que queremos saber a maior, a menor, a média e a soma das notas. Na célula B12, queríamos saber a maior nota da sala A, infelizmente não tem como copiar está fórmula para a célula abaixo, isto devido ao critério, por isso de-ve ser feito manualmente, se tentássemos colocar o intervalo como absolu-to, ou seja, $A$1:$A$2, não teria como pois neste caso ira prender o inter-valo.

38. Tabela Dinâmica

Tabela Dinâmica é bastante útil quando queremos analisar dados em uma estrutura diferente da que temos no banco de dados. Para uma planilha de contas a pagar onde há informações digitadas uma abaixo da outra, quere-

76

mos visualizar por colunas, usamos a Tabela Dinâmica no menu Da-dos/Relatório da tabela dinâmica.

Vejamos os dados digitados na primeira planilha e logo abaixo a Tabela Di-nâmica pronta com os dados organizados.

77

Na etapa 3 de 4 é onde definimos como queremos organizar os dados. No nosso exemplo usamos o campo Fornecedor em LINHA, Valor em DADOS

B

C

78

e Data Pagto em COLUNA. Para isto basta arrastar os campos localizados a direita no quadro (B) para o local desejado.

Na opção DADOS, podemos ainda definir qual a operação a ser usada. Ob-serve no quadro abaixo.

Para obter este quadro basta dar 2 cliques no campo Soma de Valor (C) em DADOS na etapa 3 de 4.

39. Gráfico Dinâmico

Fornece uma análise interativa de dados, como um relatório de tabela di-nâmica. É possível alterar os modos de exibição dos dados, ver diferentes níveis de detalhe ou reorganizar o layout do gráfico arrastando campos e exibindo ou ocultando itens em campos.

Para criar a partir de um relatório da tabela dinâmica existente siga um des-tes procedimentos:

• Criar um gráfico padrão em uma etapa

Clique no relatório da tabela dinâmica e, em seguida, clique em Assistente de gráfico imagem de botão.

• Usar o Assistente de gráfico para criar um gráfico personalizado

1. Clique em uma célula que esteja fora e não seja adjacente ao relatório da

tabela dinâmica.

2. Clique em Assistente de gráfico imagem de botão.

79

3. Clique em um tipo de gráfico na etapa 1 do assistente. Você pode usar

qualquer tipo de gráfico, exceto xy (de dispersão), de bolhas ou de

ações.

4. Na etapa 2 do assistente, clique no relatório da tabela dinâmica para que

a referência da caixa Intervalo de dados seja expandida para incluir o re-

latório inteiro.

5. Siga as instruções das etapas restantes do Assistente de gráfico.

Se não desejar exibir o relatório da tabela dinâmica associado ao relatório do gráfico dinâmico, você poderá ocultá-lo. Clique na pasta de trabalho que contém o relatório da tabela dinâmica aponte para Planilha no menu Forma-tar e clique em Ocultar

. 40. Formulário

Quando temos um banco de dados muito extenso podemos usar a opção Formulário no menu Dados/Formulário para digitar as informações.

41. Macro

Como gravar macros do Excel Existem tarefas comuns que o usuário repete e volta a repetir no Microsoft Excel, por exemplo, aplica freqüentemente a mesma combinação de forma-tos ou recebe dados todas as semanas ou meses que organiza e analisa sempre do mesmo modo. É possível utilizar uma macro para combinar to-dos os passos de uma tarefa num único comando.

80

A macro grava cliques e combinações de teclas do mouse à medida que o usuário trabalha e permite reproduzi-las mais tarde. É possível utilizar uma macro para gravar a seqüência de comandos utilizados ao efetuar determi-nada tarefa. Quando uma macro é executada, reproduz os comandos exa-tos, pela mesma ordem, o que faz com que o Excel se comporte como se ti-vesse sido o próprio utilizador a introduzir os comandos.

É fácil criar macros: o usuário dá instruções ao Excel para começar a gra-var, efetuar as ações que realiza normalmente e, em seguida, indica ao Ex-cel que terminou.

O Excel utiliza uma linguagem de programação com o nome Visual Basic ® for Applications (VBA) para gravar as instruções. Não é necessário ter co-nhecimentos de programação ou de VBA para criar e utilizar macros que irão poupar tempo e facilitar o trabalho.

Nesta parte se mostra como criar e utilizar uma simples macro de formata-ção de células e como alterar esta macro para fazer ainda mais. Também explica as opções de armazenamento de macros, de modo a poder utilizá-las a partir de qualquer livro.

Aplicar formatos é um exemplo de macro bastante simples, mas é possível gravar macros para efetuar tarefas mais complexas, tais como obter e filtrar dados externos, criar e personalizar gráficos, e não só.

Ex: Digamos que o usuário trabalha num grupo que utiliza o Excel, e todos os dias tem lançar um relatório diário do caixa, ou seja, quanto teve de en-tradas, saídas, etc.

A B 1 Relatório de Caixa 2 Data 3 Entradas 4 Saídas 5 Dinheiro 6 Cheque

Eis os passos para gravar uma macro.

Antes de digitar a planilha, no nosso caso o exemplo acima, clique sobre o me-nu Ferramentas, e aponte para Macro e, em seguida, clique em Gravar nova macro, então aparecerá uma tela como mostra abaixo:

81

1. Na caixa de diálogo Gravar macro, escreva um nome para a macro no campo Nome da macro. Os nomes das macros têm de começar por uma letra e podem incluir letras, números e caracteres de sublinhado, mas não podem incluir espaços. No campo Tecla de atalho, escreva a tecla a utilizar com a tecla CTRL para executar a macro.

Nota: Evite utilizar uma combinação de teclas que já foi utilizada para outras operações do Excel, tais como CTRL+C para copiar.

Ao término clique em OK. Ao clicar em OK, observe que aparece a barra de ferramentas Parar gravação

2. Então agora sim o usuário pode começar a gravar, ou seja, a digitar os dados na planilha. Até parar a gravação, todos os comandos e combina-ções de teclas do Excel serão gravados na macro, na ordem pela qual foram introduzidos.

Para acabar de gravar a macro, clique no botão Parar gravação

Utilizando macros já gravadas De acordo com o nosso exemplo nós criamos a macro na Plan1, ou seja, na primeira planilha, digamos que você deseja que estes dados estejam tam-bém na Plan2, sem precisar digitar novamente, é claro que nosso caso bas-taria apenas copiar, pois a planilha e pequena, mas imagine se a planilha fosse um pouco maior.

1. Clique na Plan2 2. Clique na célula que pretende inserir os dados. 3. Clique no menu Ferramentas aponte para Macro e, em seguida, clique

em Macros. 4. Clique no nome da macro e, em seguida, clique em Executar, ou sim-

plesmente a tecla Ctrl + a tecla definida por você anteriormente, para execução da macro.

82

Se vai utilizar a macro com freqüência, é possível criar um botão de barra de ferramentas para ela

Criar um botão de barra de ferramentas que execute a sua macro:

1. No menu Ferramentas, clique em Personalizar e, em seguida, clique no separador Comandos.

2. Em Categorias, clique em Macros. 3. Arraste o botão personalizado para a barra de ferramentas. 4. Na caixa de diálogo Personalizar, clique em Modificar seleção e, em se-

guida, clique em Atribuir macro. 5. Na caixa de diálogo Atribuir macro, clique no nome da macro e, em se-

guida, clique em OK. 6. Para alterar o aspecto do botão, clique novamente em Modificar seleção,

aponte para Alterar imagem do botão e clique numa das imagens dispo-níveis; ou clique em Editar imagem do botão e utilize o editor de botões para criar a sua própria imagem.

7. Clique em Fechar.

42. Protegendo Uma Planilha

Podemos definir uma senha para leitura da planilha e outra para alteração da planilha. Ao abrir a planilha, em primeiro lugar será solicitada a senha para leitura. Em seguida, é solicitada a senha para alteração (caso esta te-nha sido definida).

Se o usuário souber apenas a senha para leitura, ele poderá abrir a plani-lha, porém, não poderá fazer alterações. Se ele souber a senha para altera-ção, poderá alterar a planilha e salvar as alterações.

As senhas são definidas para cada planilha individualmente. Um detalhe importante é que, se você esquecer a senha de leitura, não será mais possível abrir a planilha. A única maneira de voltar a ter acesso à planilha é lembrando da senha. Se você esquecer a senha de gravação, poderá abrir a planilha, porém, não poderá fazer alterações.

Como definir as senhas de leitura e alteração?

1. Abra a planilha na qual você deseja definir a(s) senha(s).

2. Selecione o comando Arquivo -> Salvar como. Surgirá a janela indicada na figura a seguir:

83

3. Dê um clique no botão Opções. Na janela que surge (conforme indicado na figura a seguir), você pode definir uma senha para leitura (senha de proteção) e também uma senha para gravação/alteração (senha de gra-vação). Se for definida apenas a senha de proteção, a senha será solici-tada na abertura da planilha. Se você fornecer uma incorreta, a planilha não será carregada. Se você definir apenas a de gravação, a senha será solicitada no momento da abertura da planilha. Se você não fornecer a senha de gravação ou fornecer uma incorreta, a planilha será carregada, porém, não poderá ser alterada. Na figura a seguir, definiremos uma se-nha de proteção e também uma de gravação:

4. Após digitar as senhas, dê um clique no bo-tão OK. Será exibida uma janela pedindo confirmação para a senha de proteção. Di-gite a senha novamente e dê um clique em OK.

84

5. Surgirá uma janela pedindo a confirmação da senha da gravação. Digite-a novamente e dê um clique em OK. Você estará de volta à janela Sal-var como. Dê um clique no botão Salvar. Você estará de volta à planilha.

Nota: as senhas de gravação e proteção não precisam ser iguais.

6. Feche a planilha. 7. Abra a planilha novamente e observe

que, em primeiro lugar, será solicita-da a senha de proteção. Digite-a, conforme indicado na figura a seguir, e dê um clique no botão OK:

Se você não digitar a senha ou digitar uma incorreta, a planilha não será aberta.

8. Em seguida, será solicitada a se-nha de gravação. Digite-a, confor-me indicado na figura a seguir, e dê um clique no botão OK:

Se você não souber a senha, pode cli-car no botão Somente leitura. A plani-lha será aberta, porém, não poderão ser feitas alterações.

9. Se você não quiser mais utilizar senhas em uma planilha, utilize o co-mando Arquivo -> Salvar como. Na janela que surge, dê um clique no botão Opções e defina as duas senhas em branco. Salve a planilha. Na próxima vez que a planilha for aberta, não serão mais solicitadas as se-nhas de proteção e gravação.

85

Exercícios Avançados

Funções se: 01) Dado dois valores, calcule a diferença entre eles, onde a diferença deve

ser sempre positiva. Usar função SE. 02) Uma empresa de vendas tem três corretores. A empresa paga ao corre-

tor, uma comissão calculada de acordo com o valor de suas vendas. Se o valor total da venda de um corretor for maior que R$ 5.000,00, a co-missão será de 12% do valor vendido. Se o valor da venda do corretor estiver entre R$ 30.000,00 e R$ 50.000,00 (incluindo extremos), a co-missão será de 9,5%. Qualquer outro caso, a comissão será de 7%. Es-creva um algoritmo que gere um relatório contendo nome, valor da ven-da e comissão de cada um dos corretores. O relatório deve mostrar também, o total de vendas da empresa.

03) Crie um Boletim escolar que informe a média, e a freqüência de um es-

tudante e que no campo situação informe se um estudante está aprova-do, reprovado ou em exame de acordo com as seguintes instruções: • Se média for maior ou igual a 7 e a freqüência for maior ou igual a

75%, o estudante estará aprovado;

• Mas se a média for menor do que 5,0 ou a freqüência menor do que 75%, então o estudante estará reprovado;

• Se nenhuma das duas anteriores ocorre, então o estudante estará em exame.

04) Faça uma planilha em Excel, que tendo como dados de entrada a altura

(h) e o sexo de uma pessoa mostre seu peso ideal de acordo com as fórmulas abaixo: HOMENS: peso ideal=72,7*h-58 MULHERES: peso ideal=62,1*h-44,7

05) Faça uma planilha em Excel que, dada a idade de um nadador, classifi-que-o em uma das categorias: � Infantil A – 5 a 7 anos � Infantil B – 8 a 10 anos � Juvenil A – 11 a 13 anos � Juvenil B – 14 a 17 anos � Adulto – 18 a 60 anos � Sênior – Acima de 60 anos

86

06) Construa uma planilha em Excel que calcule o valor do imposto de renda

de um contribuinte. Considere que o valor do imposto é calculado de acordo com a tabela abaixo:

Renda anual Alíquota Dedução Até R$ 10.000,00 Isento - > R$ 10.000,00 e <= R$ 25.000,00 10% R$ 1000,00 Acima de R$ 25.000,00 25% R$ 4.750,00 07) Construa a tabela a seguir utilizando função de procura para preencher

o conceito dos alunos, de acordo com a tabela auxiliar.

08) Elaborar o banco de dados abaixo, fazendo-se o que se pede: Nome Endereço Bairro Cidade Estado Ana Rodovia Anhanguera, km 180 Centro Leme SP Eduardo R. Antônio de Castro, 362 São Benedito Araras SP Érica R. Tiradentes, 123 Centro Salvador BA Fernanda Av. Orozimbo Maia, 987 Jd. Nova Campinas Campinas SP Gabriela Rodovia Rio/São Paulo, km 77 Praia Grande Ubatuba SP Helena R. Júlio Mesquita, 66 Centro Recife PE Katiane R. 5, 78 Jd. Europa Rio Claro SP Lilian R. Lambarildo Peixe, 812 Vila Tubarão Ribeirão Preto SP Lucimara Av. dos Jequitibas, 11 Jd. Paulista Florianópolis SC Maria Av. Ipiranga, 568 Ibirapuera Manaus AM Pedro R. Sergipe, 765 Botafogo Campinas SP Roberto Av. Limeira, 98 Belvedere Araras SP Rubens Al. dos Laranjais, 99 Centro Rio de Janeiro RJ Sônia R. das Quaresmeiras, 810 Vila Cláudia Porto Alegre RS Tatiane R. Minas Gerais, 67 Parque Industrial Poços de Caldas MG

Nome Rubens A Endereço B

87

Bairro C Cidade D Estado E Dica: A Digite o nome da pessoa a ser procurada. B =PROCV(B21;A2:E16;2;0) 09) Construa a tabela a seguir utilizando função de procura com duas cha-

ves de pesquisa.

10) Construa a tabela a seguir utilizando funções de banco de dados para

relacionar os valores desejados.

88

11) Construa a tabela a seguir utilizando função de banco de dados.

Funções matemáticas e Financeiras. 12) Usar funções matemáticas para completar a tabela.

89

13) Uma loja oferece um produto de acordo com os valores especificados abaixo. Calcule o valor à vista deste produto

Número de parcelas 12 Valor das parcelas R$ 69,90

Taxa de juros 3,5% 14) Informe a taxa de juros de um financiamento com os valores abaixo:

Número de parcelas 15 Valor das parcelas R$ 340,00

Valor presente R$ 3.000,00 15) Qual o valor da prestação que se pagará para juntar R$ 8000,00 em 30

meses, a uma taxa de juros de 3,6% ao mês

Número de parcelas 30 Taxa de juros 3,60% Valor presente R$ 8.000,00

16) Quanto tempo será necessário para se juntar R$ 5000,00, poupando R$

250,00 por mês, a uma taxa de juros de 3,6% ao mês

Valor das parcelas R$ 250,00 Taxa de juros 3,6% Valor presente R$ 5.000,00

90

17) Monte a planilha a seguir Crie uma tabela dinâmica de cidade x cargo,

onde os dados são os salários líquidos e as faltas (ver figura abaixo):

18) Construa a tabela a seguir.

91

• Construa uma macro que faça o gráfico do tipo linhas da planilha acima colocando-o nesta planilha.

• Crie um botão e atribua a macro gravada a este botão • Construa uma macro que remova as linhas de grade da planilha a

formate a cor de preenchimento e a cor da fonte com uma cor de sua preferência

• Crie um desenho utilizando a barra de ferramentas de desenho e atribua a macro gravada a ele

• Através do comando personalizar barra de ferramentas, crie um bo-tão que execute a macro criada anteriormente

92

93

Exercícios Extras

1) Folha de Pagamentos

94

2) Folha de Pagamento

95

3) Sistema de Controle de Vendas

96

4) Produtos de Estoque

5) Exercícios de Vendas

97

a) Colunas digitadas: Data, Tipo, Nível do Periférico, Região, Vendedor, Valor e Quantidade Vendida.

b) Coluna Total: multiplicação da Qtde com o Valor. c) Comissão: d) Verificar se o Tipo do Computador é Computador ou Software:

� Sendo a condição acima verdadeira, a comissão será 10% do Total.

� Sendo falsa,a comissão será 5% do Total. e) Qtde Vendida e Comissão Desejada, utilizar o recurso Atingir Meta. f) Em Classificação:

� Se a região for Sul ou Sudeste, verificar se o Total é superior a R$15000. Caso seja, exibir “Boas Vendas”, senão, exibir “Vendas Ruins”.

� Se forem as demais regiões, verificar se o Total é superior a R$8000. Caso seja, exibir “Boas Vendas”, senão exibir “Ven-das Ruins”.

g) Em Qtde Vendida e Comissão desejada, utilizar o Recurso Atingir Meta para calcular a quantidade de produtos que o vendedor terá que vender para ter a comissão desejada por ele.

h) Em Descrição do Periférico: � De acordo com o Nível do Periférico, buscar a descrição cor-

respondente. � Caso seja mouse (Nível I), analisar se o valor é:

i. - Maior do que R$100: exibir USB. ii. - Entre R$50 e R$100: exibir Serial. iii. - Menor do que R$50: exibir PS/2.

� Caso seja Teclado (Nível II) analisar se o Valor é superior a R$100. Se for, exibir Wireless, senão, exibir Comum.

� Se não for nem teclado nem mouse, não exibir nada a frente da desrição.

i) Colocar Autofiltro de modo que os cálculos referentes sejam atualiza-dos.

j) Utilizar Filtro Avançado nas tabelas Critérios e Resultados. k) Utilizar Somase em Informações por Vendedor. l) Exibir o Total das colunas Valor, Comissão e Total por Região. Para

isso, utilize o recurso SubTotais.

98

99

6) Realização de Pedidos

100

101

7) Controle de Vendas

102

103

8) Botões de Macro

Documents

341tica II Quimica.doc)2 SUMÁRIO 1. O Excel ..... 3 2. Versões ..... 3