Curso de Excel 2007 – Prof. Alexei Bueno · AULA 2 – FÓRMULAS E FUNÇÕES Toda célula 1 no Excel é como se fosse uma calculadoras, onde podemos inserir fórmulas ... Araras

Curso de Excel 2007 – Prof. Alexei Bueno

1

AULA 1 - INTRODUÇÃO

O Microsoft Excel (nome completo Microsoft Office Excel) é um programa de planilha eletrônica de cálculo escrito e produzido pela Microsoft para computadores que utilizam o sistema operacional Microsoft Windows e também computadores Macintosh da Apple. Seus recursos incluem uma interface intuitiva e capacitadas ferramentas de cálculo e de construção de gráficos que tornaram o Excel um dos mais populares aplicativos de computador até hoje. É, com grande vantagem, o aplicativo de planilha eletrônica dominante e o tem sido desde a versão 5, lançado em 1993 e sua inclusão como parte do Microsoft Office. 1.1 – A interface (tela) do Excel 2007

Célula: é a intersecção de uma coluna com uma linha, no Excel as linhas são numeradas de 1 a 1.048.576 e as colunas são identificadas pelo alfabeto indo de A até XFD, sendo que ao acabar um alfabeto inicia-se outro ao lado, exemplo: AA, AB, AC...

Guia de Opções: cada guia trabalha determinadas funções. Ex.: “Início” para funções básicas

Grupo de ferramentas: agrupam os botões cujas funções são semelhantes.

Clicando aqui vocês terão acesso as telas existentes nas versões anteriores do Excel referente a função do grupo.

Botão Office: permite dentre outras funções abrir arquivos, imprimir, visualizar e salvar (inclusive para versões anteriores do Excel)

Caixa Nome: ao digitar o endereço de uma célula e pressionar Enter, o cursor irá se deslocar até a referida célula

Célula ativa: é a célula que você estará trabalhando


2

Guia Funcionalidades principais Início Trabalhar com fontes, estilo dos números, células e filtragens. São funções

básicas e que utilizamos no dia-a-dia.

Inserir Permite inserir tabela dinâmica, imagens, clip-arts, gráficos e outros recursos adicionais em nossas planilhas

Layout da Página Permite configurarmos as margens, orientação e tamanho do papel, além de configurar algumas configurações que podem ser exibidas na página

Fórmulas Agrupa as principais categorias de fórmulas que temos no Excel além de permitir realizar algumas configurações avançadas como definir nomes e realizar auditoria

Dados Esta guia permite executarmos algumas funções avançadas do Excel com relação a conectar nossa planilha com base de dados, além de realizar agrupamento, subtotal e filtragens variadas.

Revisão Permite verificar ortografia, inserir comentário, proteger a planilha com senha dentre outras funcionalidades

Exibição Permite controlarmos algumas configurações de exibição, como janelas, zoom, mostrar ou ocultar régua e exibir em tela inteira

1.2 – Trabalhando com números

Da esquerda para a direita: Estilo moeda: automaticamente coloca cifrão (R$) e duas casas decimais Estilo percentual: já faz a multiplicação por 100, inclusive colocando o símbolo “%” Aumentar casas decimais e Reduzir casas decimais: aumenta ou reduz a

quantia de casas decimais de um número. Ao reduzir o Excel, quando necessário, automaticamente realiza o arredondamento do número.

Planilhas: todo arquivo novo do Excel vem com 3 planilhas para trabalharmos, porém podemos adicionar quantas forem necessárias

Clicando neste botão, adicionamos novas planilhas

Estes botões servem para navegarmos entre as planilhas de nosso arquivo (principalmente quando tivermos muitas)

Barra de status: exibe algumas informações extras. Quando selecionarmos alguns números irá mostrar a média, contagem e soma

Aqui podemos trabalhar com zoom


3

1.3 – Trabalhando com fonte, cores e bordas

O baldinho serve para colorirmos o fundo da célula. O “A” serve para definirmos a cor da fonte Outras opções possíveis: estilo da fonte, negrito, itálico e sublinhado, tamanho da fonte (botões para aumentar e diminuir)

Obs.: as células do Excel não são inicialmente impressas, então se você quiser que imprima as linhas das tabelas terá que definir as bordas na mesma. 1.4 – Como selecionar células no Excel e alterando o tamanho

Células distintas: pressione a tecla Ctrl e clique sobre as células desejadas para selecionar. Células em seqüência: clique na primeira célula, pressiona Ctrl e clique na última desejada. Obs.: para selecionar uma linha inteira, clique sobre o número desejado e também para

selecionar uma coluna inteira, clique na letra desejada. Para selecionar a planilha toda clique na seta

situada antes do número 1 e da letra A:

Ajustando a largura da célula: para aumentarmos ou diminuirmos a largura de uma célula basta posicionar o mouse entre as letras da coluna desejada e quando o mesmo se transformar em uma seta apontando horizontalmente, clique e arraste.

Obs.: caso você tenha uma célula com determinado conteúdo e deseja ajustar seu tamanho

para o tamanho exato do conteúdo da célula, basta dar duplo clique entre as colunas (letras) para que o Excel faça a adequação do tamanho da célula.

Obs.: caso o conteúdo da célula não caiba para a largura da célula existente, o Excel irá exibir o símbolo “###”, indicando que você tem que aumentar a largura da mesma:

Permite colocar bordas nas células do Excel

Duplo clique aqui para que o Excel ajuste automaticamente a largura da célula para que seu conteúdo fique visível.


4

1.5 – Inserindo linhas e colunas na planilha

Exercício: faça um boletim escolar, contendo as disciplinas de seu curso:

Adicional: insira uma coluna de “faltas” entre disciplina e 1º Bim. Insira uma nova disciplina entre a primeira e a segunda definidas por você. 1.6 – Auto-preenchimento

No caso de Janeiro o Excel continuará a seqüência dos demais meses: Fevereiro, Março... e assim por diante. Este recurso chama-se auto-preenchimento e é muito útil para agilizarmos o desenvolvimento das planilhas.

Este botão permite inserir linhas e colunas nas planilhas. Abaixo deste temos o botão que permite realizar as mesmas tarefas, mas excluindo.

Ao clicar e arrastar na “alça de auto-preenchimento” o Excel irá dar prosseguimento...


5

AULA 2 – FÓRMULAS E FUNÇÕES Toda célula1 no Excel é como se fosse uma calculadoras, onde podemos inserir fórmulas criadas por nós ou funções que já são pré-definidas pelo Excel, existindo mais de 400 onde se aplica na área de estatística, lógica, etc. Atenção: seja uma fórmula ou função, sempre deverá iniciar com o sinal de igual (=) de forma que o Excel possa identificar que você deseja realizar um cálculo matemático e não digitar um texto na célula. Operadores matemático � utilizado na construção de fórmulas são eles:

+ (soma) - (subtração) / (divisão) * (multiplicação) Observação: assim como na matemática, os parênteses fazem com que determinadas

operações dentro de uma fórmula seja priorizada e, portanto executada primeiramente, desta forma:

= A1 + B1 + C1 + D1 / 4 é diferente de = (A1 + B1 + C1 + D1) / 4

Como vemos no exemplo acima, como a divisão tem prioridade, no primeiro caso não será feito a média corretamente, já no segundo está correto, pois primeiro irá ser feito a somas das notas (A1 até D1) e só depois a divisão por 4.

Anatomia de uma função

= NOME_DA_FUNÇÃO ( PARÂMETROS ) Nome da função � você define a operação matemática que deseja realizar, ou seja, soma, média... Parâmetros � é a forma que a função irá trabalhar com as células, veja exemplos:

(A1 : A10) � para selecionar a célula A1 ATÉ a célula A10, ou seja, utilizamos dos dois pontos (:) quando desejamos que a função opere com uma quantia contínua de células.

(A1 ; C4 ; E9) � quando desejamos selecionar células “separadas” devemos utilizar o

ponto-e-vírgula (;) de maneira a selecionar células distantes uma das outras, ou seja, neste exemplo estamos selecionando a célula A1 E C4 E a E9.

(B1 : B10 ; E1 : E10) � neste exemplo fazemos uma combinação das duas formas vistas

acima, ou seja, estamos selecionando da célula B1 até a B10, de maneira contínua e da célula E1 até a E10, também continuamente, de forma que obtemos o resultado exemplificado na figura abaixo:

1 Intersecção de uma linha com uma coluna, ou seja, são todos os “quadradinhos” de uma planilha


6

(B1 : B10 ; E1 : E10)

PRINCIPAIS FUNÇÕES DO EXCEL

FUNÇÃO... SERVE PARA... EXEMPLO: SOMA Somar células numéricas =SOMA(A1;A2;A3)

=SOMA(A1:A3)

MÉDIA Realizar a média aritmética (soma dos elementos e divisão pela quantia de elementos)

=MÉDIA(A1;A2;A3) =MÉDIA (A1:A3)

MÁXIMO Retorna o valor do maior elemento dentre os informados

=MÁXIMO(A1;A2;A3) =MÁXIMO(A1:A3)

MÍNIMO Retorna o valor do menor elemento dentre os informados

=MÍNIMO(A1;A2;A3) =MÍNIMO (A1:A3)

CONT.NUM Faz uma contagem dos elementos informados

=CONT.NUM(A200:A232)

INT Retorna a parte inteira do número (arredondando para baixo)

=INT(A14)

MULT Multiplica o valor dos elementos informados

=MULT(A1;A2;A3) =MULT (A1:A3)

ARRED Realiza um arredondamento dos números. O número informado é a quantia de casas decimais desejadas

=ARRED(A2;2)

=ARREDONDAR.PARA.CIMA(número;casas) � Arredonda número para cima

=ARREDONDAR.PARA.BAIXO(número;casas) � Arredonda número para baixo

MOD Obtenho o resto da divisão. O primeiro número é o desejado e o segundo o divisor.

=MOD(7;3)


7

HOJE Obter a data do dia que o Excel obteve do sistema

=HOJE()

AGORA Obter a data e hora atuais do sistema =AGORA()

=DIAS360(“01/01/2008”;”01/05/2008”) � Retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). Use esta função para ajudar no cálculo dos pagamentos, se o seu sistema de contábil estiver baseado em doze meses de 30 dias.

=DIA.DA.SEMANA(“data”;2) � Fornece-nos o dia da semana a que uma data corresponde. Será um número, sendo 1 para segunda-feira até 7 para domingo.

Exercício 1: no boletim da aula 1, insira uma coluna no final denominada “média” e utilizando da função média, insira algumas notas e faça o cálculo da média do aluno. Exercício 2: Elaborar as planilhas abaixo, fazendo-se o que se pede:

Araras Informática - Hardware e Software Rua São Francisco de Assis, 123 - Araras SP

Nº NOME Salário Bruto INSS Gratificação INSS R$ Gratificação R$ Salário Líquido

1 Eduardo R$ 853,00 10,00% 9,00% 2 Maria R$ 951,00 9,99% 8,00% 3 Helena R$ 456,00 8,64% 6,00% 4 Gabriela R$ 500,00 8,50% 6,00% 5 Edson R$ 850,00 8,99% 7,00% 6 Elisangela R$ 459,00 6,25% 5,00% 7 Regina R$ 478,00 7,12% 5,00% 8 Paulo R$ 658,00 5,99% 4,00%

FÓRMULAS INSS R$: multiplicar Salário Bruto por INSS. Gratificação R$: multiplicar Salário Bruto por Gratificação. Salário Líquido: Salário Bruto mais Gratificação R$ menos INSS R$. Formatar os números para que eles apareçam de acordo com a planilha dada.


8

Exercício 3: faça a planilha abaixo

Dia Janeiro Fevereiro Março 1 R$ 23,00 R$ 23,00 R$ 90,00

2 R$ 45,00 R$ 22,00 R$ 98,00

3 R$ 65,00 R$ 14,00 R$ 78,00

4 R$ 12,00 R$ 87,00 R$ 65,00

5 R$ 34,00 R$ 65,00 R$ 45,00

6 R$ 32,00 R$ 45,00 R$ 67,00

7 R$ 67,00 R$ 32,00 R$ 43,00

8 R$ 32,00 R$ 45,00 R$ 23,00

9 R$ 13,00 R$ 67,00 R$ 45,00

10 R$ 23,00 R$ 21,00 R$ 67,00

Em qualquer outra célula da planilha, desenvolva as fórmulas que trazem como resultado:

1) Média de Janeiro, do dia 1 até 10: 2) Soma dos valores de Janeiro e Março: 3) Média de Fevereiro dos dias 2, 4, 6 8: 4) Maior valor do mês de Março: 5) Menor valor do mês de Janeiro: 6) Maior valor dos meses Janeiro e Março: 7) Média dos dias 6, 8 e 10 de Jan. e Març.: 8) Soma dos dias 1, 3, e 5 de Jan. e Fev.: 9) Soma de Fevereiro, do dia 1 até 10:

Exercício 4: desenvolva no Excel a planilha abaixo e desenvolva fórmulas para “valor estoque” e “previsão de lucro”, no resumo utilize das funções estudadas para descobrir maior preço, menor, média e a quantidade, ou seja, quantidade de itens (cont.num).

TABELA DE PREÇOS PARA O 1º SEMESTRE Item Preço Porcentagem Estoque Valor Estoque Previsão de Lucro Calça R$ 45,00 10% 200

Saia R$ 26,00 8% 150

Shorts R$ 15,00 7% 300

Bermuda R$ 18,00 10% 123

Camisetas R$ 20,00 8% 500

TOTAL.....:

RESUMO

Maior Preço:

Menor Preço:

Média

Quantidade:

Obs.: Valor Estoque = Preço x Estoque Previsão de Lucro = Porcentagem x Valor Estoque


9

AULA 3 – CONGELAMENTO DE CÉLULAS E FUNÇÃO SE Observe a planilha abaixo:

Veja que o percentual de lucro é o mesmo para todos os produtos... A fórmula da previsão de lucro consiste na multiplicação de porcentagem de lucro por valor do estoque. Neste caso como o percentual de lucro é o mesmo para todos os produtos esta célula deverá ser fixada ou “congelada” de forma que ao clicar e arrastar a fórmula de previsão de lucro não ocorra do Excel também “descer” de forma a multiplicar outros valores que não será a porcentagem de lucro com o valor do estoque. Veja a forma errada:

Para congelarmos uma célula utilizamos do símbolo “$”. Exemplo: E$4 � quer dizer que fixamos ou congelamos a linha 4 $E$4 � quer dizer que fixamos ou congelamos a coluna E e a linha 4. Ao clicar e arrastar a fórmula da célula F7 de maneira correta eu teria que fazer como na figura abaixo:

Multiplicou E6 por E9 o que está errado. (ocorreu que a célula E4 não está “congelada”)


10

Maneira correta:

FUNÇÕES DE ARREDONDAMENTO DE NÚMEROS Obtém a parte inteira do número, arredondando para baixo: = INT (célula ) Arredondamento de números, especificando a quantidade de dígitos que deverá arredondar: = ARRED (célula ; número de dígitos) Arredondamento de números, especificando se é para cima ou para baixo e a quantia de dígitos: =ARREDONDAR.PARA.CIMA(célula ; número dígitos) =ARREDONDAR.PARA.BAIXO(célula ; número dígitos) Exercício:

Descarte = Unidades * % Descarte Utilize a função int para obter a parte inteira do número (12,5 para 12) =int(...) Utilize a função para arredondar para cima (12,5 para 13) =arredondar.para.cima(...) Unidades OK = Unidades – Descarte Sobra � Fazer função que calcula o resto da divisão: =MOD(Unidades; Clientes)

Posso clicar e arrastar normalmente pois a célula E4 está fixada, ou seja, será a mesma porcentagem de lucro para todos os produtos.


11

FUNÇÕES DE DATA

=HOJE() � exibe a data do dia =DIA.DA.SEMANA(data; 2) � retorna o número correspondente ao dia da semana. Ex.: 1 para segunda-feira =DIAS360(data_inicial;data_final) � retorna a quantia de dias entre as datas especificadas. Obs.: baseia-se no ano de 360 dias, com meses de 30 dias. =AGORA() � exibe o dia e a hora que a função foi aplicada

Função SE

Função SE � Permite a uma célula do Excel tomar uma decisão segundo o resultado de uma condição lógica.

= SE ( condição lógica ; ação se verdadeira ; ação se falsa)

Onde a ação poderá ser exibir algo escrito (que deverá ser colocado entre aspas), poderá também uma fórmula ou até mesmo outra função.

Faça a planilha abaixo:


12

Neste caso nossa condição lógica é se o aluno ficar com a média maior ou igual a sete. Caso esta condição lógica seja verdadeira irá exibir APROVADO, caso contrário, REPROVADO.

Observação, para construir uma condição lógica temos que utilizar dos operadores relacionais:

Operador Função

> Maior >= Maior e igual <= Menor e igual < Menor

<> Diferente

Exercício:

Situação da Produção: OK (se a produção for maior que a metas de produção) Não Atingida Situação da Venda: OK (se a venda for maior que a meta de venda) Não Atingida Representação gráfica da função SE:

Problema: uma empresa deseja controlar o pagamento de títulos de forma que os clientes que atrasarem o pagamento terão um acréscimo de 10% sobre o valor a ser pago. Já os que pagarem até o dia do vencimento terá, como incentivo, um desconto de 5%.

Neste caso temos que utilizar a função SE de forma a determinar segundo o resultado uma condição (que é o atraso no pagamento) o valor que o cliente deverá pagar.

Condição Lógica

(operador

Ação a ser tomada caso a condição seja avaliada como Verdadeira

V F Ação a ser tomada caso a condição seja avaliada como Falsa

Utilizamos Operadores


13

1) Faça a planilha abaixo:

2) Na célula E2 iremos colocar nossa função que calculará o valor que o cliente deverá pagar de acordo com a data do pagamento do título:

=Se(C2 - B2 > 0; D2+(D2*10%); D2-(D2*5%))

3) Preencha a sua planilha com os dados abaixo e veja o “Valor a Pagar” calculado pelo Excel:

Cliente Dt. Vencimento Dt. Pagamento Valor do Título José da Silva 15/02/2007 14/02/2007 R$ 500,00 Maria do Carmo 20/03/2007 23/03/2007 R$ 1500,00 Antônia Pereira 05/04/2007 05/04/2007 R$ 360,00 Rodrigo dos Santos 03/05/2007 07/05/2007 R$ 1800,00

4) Podemos utilizar da Função SE também para “escrever” no Excel conforme uma condição:

Construa a planilha abaixo:

5) Na célula D2 coloque a função: =SE(C2>B2; “OK”; “NÃO ATINGIDO”)

6) Arraste a função até a D5 e observe que o Excel avalia automaticamente cada situação.

Se a Dt. Pagto – Dt. Venc for maior que zero, quer dizer que atrasou “x” dias. Se a condição for

verdadeira, será acrescentado 10% sobre o valor do título.

Se a condição for falsa, será realizado um desconto de 10% sobre o valor do título.


14

Exercícios: 1) Construa uma planilha que tenha: código do produto, descrição, unidade, quantidade em estoque, quantidade mínima e situação. Se a quantidade em estoque for menor que a quantidade mínima em situação deverá escrever “Repor”, caso contrário deverá escrever “OK”. 2) Faça uma planilha de notas para 5 alunos que deverá conter o nome do aluno, os 4 bimestres, menor nota, maior nota, média (por aluno) e situação (aprovado se média for maior ou igual a 7). Esta planilha deverá informar com base em todos os alunos as seguintes informações: média das notas, menor nota e maior nota. 3) Desenvolva a planilha abaixo:

Faça uma função SE para a coluna de “Situação da Produção” de forma que para os produtos cuja produção foi maior que a meta de produção fique “OK”, caso contrário “Não atingido”. Para a coluna “Situação da Venda” deverá aparece “OK” para caso a venda seja maior que a meta de venda, caso contrário deverá aparecer “Não atingido”.


15

AULA 4 – OUTRAS FUNÇÕES LÓGICAS São funções cujo resultado poderá ser ou VERDADEIRO ou FALSO, assim como a função SE vista anteriormente. � FUNÇÃO LÓGICA E: para esta função as duas condições especificadas tem que ser verdadeiras para que o resultado seja verdadeiro. Ex.:

=SE(E(B2>=7;C2<=30%); “aprovado”; “reprovado”) No caso da planilha acima o resultado será aprovado APENAS se o aluno tiver nota 7 ou superior E até 30% de falta, caso contrário estará reprovado. Veja que para que a função lógica E retorne VERDADEIRO é necessário que duas condições seja verdadeiras, que no exemplo é B2>=7 e C2 <=30. � FUNÇÃO LÓGICA OU: basta uma das condições ser verdadeira para que o resultado seja verdadeiro. Ex.:

=SE(OU(A2>=7;B2>=70);"aprovado";"reprovado") CONT.SE � conta a quantia de determinado caractere.

A função CONT.SE acima irá exibir a quantidade de clientes cujo Status seja “PG”, ou seja, a quantia de clientes que efetuaram o pagamento.


16

SOMASE � Realiza uma soma de valores caso uma condição lógica seja verdadeira:

No caso da planilha acima digitamos o nome de um departamento na célula D10 (exemplo: Financeiro, Administração ou Produção) e a função SOMASE irá somar todos os valores do referido departamento (veja que há uma repetição)


17

AULA 5 – FUNÇÕES DE PESQUISA - PROCV

A função PROCV é utilizada para localiza ou procurar um valor na primeira coluna à esquerda de uma tabela e retorna outro valor desejado, na mesma linha, de uma coluna especificada na tabela. O “V” em PROCV significa "Vertical", pelo fato desta função realizar uma pesquisa verticalmente. Veja abaixo a sintaxe desta função: = PROCV ( valor_procurado; matriz_tabela; núm_índice_coluna; procurar_intervalo) Tome por exemplo a seguinte planilha de alunos, na primeira coluna temos o código, seguido dos dados do mesmo. Mais abaixo temos definido onde iremos realizar a pesquisa:

Abaixo descrevo o que realiza cada parâmetro da função: valor_procurado � é o valor que desejamos localizar na primeira coluna da planilha (CÓDIGO). matriz_tabela � é a tabela interna em que os dados são procurados (não incluir o cabeçalho).

Neste caso compreende de A2 até D4. núm_índice_coluna � é o número da coluna na planilha a partir do qual o valor correspondente

deve ser retornado (o valor que você quer retornar). Neste exemplo o índice 2 é o nome do aluno, o 3 seria o curso e o 4 a sala. Cada coluna tem um número referente ao seu índice.

procurar_intervalo � se for “0” a matriz_tabela não precisará ser ordenada, ou seja, os códigos

poderão ser aleatórios se for “1” será realizado uma pesquisa por aproximação que será vista em um exercício futuro.

Digite esta fórmula na B8


18

PROCV COM INTERVALO “1”

No exemplo da planilha acima observamos o parâmetro “1” especificado como tipo de intervalo especificado na procura.

Digitando-se salários de R$ 600,00 até R$ 1499,00 (teto máximo) esta função PROCV deverá retornar 8% e assim por diante. Pratique a função PROCV, desenvolvendo a seguinte planilha

Fórmula a ser digitada na célula B7 Salário Desconto

Digito o código do produto e obtenho seu preço de venda

Documents

Curso de Excel 2007 – Prof. Alexei Bueno · AULA 2 – FÓRMULAS E FUNÇÕES Toda célula 1 no Excel é como se fosse uma calculadoras, onde podemos inserir fórmulas ... Araras