Microsoft Excel 2003

Associação Educacional Dom Bosco
Faculdades de Engenharia de Resende
Microsoft Excel 2003
Professores:
Eduardo Arbex
Mônica Mara
Tathiana da Silva
Resende
2010
INICIANDO O EXCEL
Para abrir o programa Excel, usaremos os seguintes passos:
1.Clique no botão iniciar
2. Posicione o cursor do mouse na opção todos os Programas
3. Posicione o cursor na opção Microsoft Office
4. Visualize e clique em Microsoft Office Excel 2003
A TELA DO EXCEL
Para quem já trabalhou com o editor de textos Microsoft Word, a tela abaixo lembra um
pouco, principalmente a barra de Ferramentas. Alguns botões do Word, porém, o que
diferencia mesmo são as células, divididas em linhas e colunas.
CONHECENDO AS BARRAS DO EXCEL
Barra de Título
Fornece o nome do software e também o nome do arquivo que está sendo editado (ou que se
encontra aberto).
Barra de Menus
Fornece os menus de comandos do Excel.
Barra padrão ou de ferramentas de comandos
Fornece, ao usuário, botões representando alguns dos comandos disponíveis nos menus.
Barra de Ferramentas de Formatação
Permite ao usuário formatar os caracteres do texto na célula, modificando estilo, cor de textos
e células, alinhamento do texto, centralizar colunas, formatar em moeda, etc.
Área de Trabalho
É o local onde serão digitados valores nas células.
Barra de Fórmulas
Mostra a célula selecionada pelo cursor, bem como a fórmula, função e ainda textos, se
usados, em cada célula.
Barras de Rolagens
Usadas para que o usuário se desloque pelo texto com o auxílio do mouse, elas podem ser
vertical ou horizontal. Permite o deslocamento pela planilha clicando nas setas ou arrastando
o botão.
OS COMPONETES DO EXCEL 2003
Este software é composto de cinco partes fundamentais, que são: Pasta, Planilha, Coluna,
Linha e Célula.
Pasta : É denominada “PASTA” todo arquivo que for criado neste software “Excel”. Tudo
que for criado e posteriormente será um arquivo, porém considerado uma PASTA.
Planilha : Uma planilha é considerada a parte onde será executado todo o trabalho por
isso esta é fundamental, se não temos planilha não podemos criar qualquer calculo que
seja. O Excel 2003 possui varias planilhas, (Inicialmente temos 03 planilhas para
trabalharmos, mas que posteriormente podem ser alteradas). Podemos localizar uma
planilha através dos nomes que elas receberam (inicialmente PLAN1, PLAN2...) na parte
inferior da Área de Trabalho.
OBS: Dentro de uma planilha estão contidas as colunas, linhas e células.
Coluna _É o espaçamento entre dois traços na vertical. As colunas do Excel são
representadas em letras de acordo coma a ordem alfabética crescente sendo que a ordem
vai de A até IV, e tem no total de 256 colunas em cada planilha.
Linha _É o espaçamento entre dois traços na horizontal. As linhas de uma planilha são
representadas em números, formam um total de 65.536 linhas e estão localizadas na
parte vertical esquerda da planilha.
Célula _ As células são formadas através da intersecção “cruzamento” de uma coluna
com uma linha e, cada célula tem um endereço “nome” que é mostrado na caixa de
nomes que se encontra na Barra de Fórmulas. Multiplicando as colunas pelas linhas
vamos obter o total de células que é 16.777.216.
ENTENDENDO FUNÇÕES
O Excel permite o cálculo através de funções já existentes ou ainda que sejam criadas novas
funções. Um detalhe muito importante é que uma função no Excel deve ser precedida pelo
sinal de = (igual), independentemente da função, pois o Software reconhece uma fórmula ou
função a partir do momento que for iniciado com o sinal de igual.
As fórmulas que envolvem cálculos mais simples podem ser montadas através dos sinais
operacionais diretamente :
- Sinal de * para Multiplicação
- Sinal de / para Divisão
- Sinal de + para Adição
- Sinal de – para Subtração
- Sinal de ^ para Potenciação
- Sinal de % para Porcentagem
A função é um método para tornar mais rápido a montagem de fórmulas que envolvem
cálculos mais complexos e vários valores. Existem funções para cálculos matemáticos,
financeiros e estatísticos. Por exemplo, na função: =SOMA(A1:A10), significa que a função
SOMA, somará os valores do intervalo A1 até A10, sem você precisar informar célula por
célula.
O Excel oferece centenas de funções, mas as mais utilizadas são:
Fórmula da soma
Ex: =SOMA(A1:A8)
A fórmula irá somar todos os valores que se encontram no endereço A1 até o endereço A8.
Os dois pontos indicam até, ou seja, some de A1 até A8. A fórmula será sempre a mesma, só
mudará os devidos endereços dos valores que você deseja somar.
Veja o outro exemplo:
Neste exemplo estão sendo somados todos os valores do endereço A1 até o endereço D1. A
fórmula seria digitada como no exemplo, e ao teclar ENTER o valor apareceria.
Outra maneira de somar é utilizar o Botão da Autosoma, conforme o exemplo:
Este é o botão da AutoSoma.
Para trabalhar com o botão da Autosoma:
1. Selecionar os valores que desejar somar.
2. Depois clique no Botão da Autosoma e ele mostrará o resultado.
Mais um exemplo de Soma:
Agora quando se deseja somar todos os valores dispostos nesta planilha usando uma única
fórmula:
Mais um exemplo de Soma:
Para somar números dispostos de maneira alternada, ou seja, em endereços diferentes:
Para somar somente os valores de água, basta digitar o endereço de cada valor, ou seja, o
endereço do primeiro valor + o endereço do 2º valor e assim sucessivamente. Os cálculos
sempre devem iniciar-se com o sinal de igualdade.
Fórmula da subtração
O exemplo abaixo calcula o saldo líquido do José. Basta digitar o endereço do salário bruto –
e o endereço do desconto.
Fórmula da multiplicação
MULT_ Multiplica todos os números fornecidos como argumentos e retorna o produto.
Sintaxe : = MULT(núm1;núm2;...)
A forma utilizada para subtrair é a mesma para multiplicar, será preciso apenas trocar o sinal
de subtração pelo o sinal de multiplicação (*). Ou: digitar a função:=MULT(selecionar as
células com os seguintes valores a serem multiplicados).
Fórmula da divisão
A fórmula ocorre da mesma maneira que as duas anteriores. Só precisa trocar o sinal para
dividir (/).
Fórmula da porcentagem
O cálculo se realiza da mesma maneira como numa máquina de calcular, a diferença é que
são adicionados os endereços na fórmula. Veja o exemplo:
Um cliente de uma loja fez uma compra no valor de R$ 1.500,00 e a planilha deseja dar a ele
um desconto de 5% em cima do valor da compra. A fórmula ficaria do Desconto ficaria da
seguinte forma:
Onde:
B2 – Refere-se ao endereço do valor da compra.
* - sinal de multiplicação.
5/100 – é o valor do desconto dividido por 100, ou seja, 5%.
Esta forma está multiplicando o endereço do valor da compra por 5 e dividindo por 100,
gerando assim o valor do desconto de 5%.
Outra forma:
Onde:
B2 – endereço do valor da compra
* - sinal de multiplicação
5% - o valor da porcentagem.
Depois para calcular o Valor a Pagar, basta subtrair o Valor da Compra – o Valor do
Desconto, como mostra no exemplo.
Fórmula do máximo
Mostra o valor máximo de uma faixa de células.
Exemplo: Suponhamos que fosse necessário descobrir qual a maior idade de crianças em
uma tabela de dados. A forma de fazer isso é usando a fórmula MAX, conforme o exemplo
abaixo:
Onde:
(A2:A5) – refere-se ao endereço dos valores onde você deseja ver qual é o maior valor. No
caso a resposta seria 30.
Fórmula do mínimo
Mostra o valor mínimo de uma faixa de células.
Exemplo: Suponhamos que fosse necessário descobrir qual o peso idade de crianças em
uma tabela de dados. Veja a fórmula no exemplo abaixo:
Fórmula da média
Calcula-se a média aritmética de uma faixa de valores.
Exemplo: Qual a média de idade da tabela de dados abaixo?
Fórmula da data
Esta fórmula insere a data automática em uma planilha, conforme o exemplo:
Fórmula da condição SE
Caso fosse necessário criar um Controle de Notas de Aluno, onde ao se calcular a média, ele
automaticamente especificasse se o aluno fora aprovado ou não.
Primeiramente, seria necessário entender o que será necessário fazer. Por exemplo: Escrever
no campo situação “Aprovado” somente se o aluno tirar uma nota Maior ou igual a 7 na
média, caso contrário, escrever “Reprovado”, já que o aluno não atingiu a condição para
passar. Veja como você deve escrever a fórmula utilizando a função do SE>
Onde:
SE – é a função.
B2 – refere-se ao endereço da média do aluno. Sendo ela que determinará se o aluno
passará ou não.
>=7 – é o teste lógico refere-se à condição para o aluno passar, ou seja, para está Aprovado
ele deve atingir uma média maior ou igual a 7.
“Aprovado” - refere-se à resposta verdadeira, ou seja, se a condição for verdadeira (a nota
for maior ou igual a7) então ele escreverá aprovado. Por isso você deve colocar entre aspas,
já que se refere a texto.
; (ponto e vírgula) - é o separador de lista, separa o teste lógico da opção para o teste
verdadeiro e, a opção verdadeira da opção falsa.
“Reprovado” – refere-se a resposta falso, ou seja, caso ele não tenha média maior ou igual a
7, então escreva Reprovado.
“ (aspas) – quando a opção verdadeira ou falsa for um texto deverá ser colocado entre
aspas.
Siga esta sintaxe, substituindo somente, o endereço, a condição, as respostas para
verdadeiro e para falso. Não esquecendo que deve iniciar a fórmula sempre com: =SE e
escrever dentro dos parênteses.
Mais um exemplo do SE com mais de uma condição.
Agora, para escrever o aproveitamento do aluno quanto à média, colocando Ótimo para uma
média maior ou igual a 9, Bom para uma média maior ou igual a 8, Regular para uma
média maior ou igual a 7 e Insuficiente para uma média menor que 7.
Onde:
B2 – refere-se ao endereço da média
>=9 – refere-se à condição para ótimo
“Ótimo” - refere-se a resposta se caso for maior ou igual a 9 as demais têm o mesmo
sentindo só mudam as condições e as respostas.
Para resolver esse tipo de situação é necessário escrever um SE, dentro de outro Se após o
ponto e vírgula. Para parêntese aberto, será necessário fechá-lo ao final e o Excel auxilia
provendo cores diferentes para cada um deles.
Mais um exemplo:
Calcular, dentro de uma planilha de pagamentos o Imposto de Renda para os funcionários
listados. Mas, o cálculo só será efetuado para aqueles funcionários que ganham mais de R$
650,00, ou seja, se o salário do funcionário for maior que R$ 650,00, então deverá ser
multiplicado por uma taxa de 5% em cima do Salário Bruto, mas somente se ele ganhar mais
de R$ 650,00, caso contrário deverá ficar 0 (zero).
Onde:
G11 – refere-se ao endereço do Salário Bruto
>650 – refere-se a condição para que seja feito o cálculo
G11*5% - refere-se a resposta se for verdadeira, ou seja, se no endereço G11 conter um
valor maior que 650, então ele multiplicará o Valor do Salário Bruto(G11) por 5% (taxa do
Imposto de Renda).
0(zero) – refere-se a resposta se for falso, ou seja, caso no endereço G11 não tenha um valor
maior que 650, então não haverá cálculo, ele colocará 0(zero).
UTILIZANDO FORMULÁRIOS
Um recurso muito interessante do MS-Excel é a possibilidade de utilizarmos os recursos de
formulários diretamente nas Planilhas, para facilitar o trabalho de digitação ou de simulação
de dados. Com o auxilio destes objetos na planilha, você poderá escolher uma produto em
uma lista ao invés de digitar, poderá indicar o número desejado simplesmente clicando o
mouse até indicar o número desejado, com a grande vantagem de não ser necessário a
criação de código para verificar o que foi digitado.
Este recurso está disponível através da Barra de Ferramentas Formulários. Para ativá-la,
clique com o botão direito do mouse sobre uma Barra de Ferramentas e solicite a opção
Formulários.
Temos os seguintes botões a nossa disposição na Barra de Ferramentas Formulários:
Botão
Descrição
Rótulo, cria um rótulo de texto no gráfico ou folha de diálogo.
Caixa de edição, cria uma caixa de edição para digitação de texto em folhas de
diálogo.
Caixa de grupo, cria uma caixa de grupo na sua planilha, gráfico ou folha de
diálogo. Então, você poderá usar o botão "Botão de opção" para colocar botões de
opção na caixa de grupo.
Criar Botão, cria um botão ao qual você pode anexar uma macro ou um módulo de
Visual Basic. Quando você usar o botão " Criar botão", o MS-Excel exibe a caixa de
diálogo Atribuir Macro para que você designe uma macro já existente ao botão, ou
grave uma nova macro para ser designada a ele.
Caixa de verificação, cria uma caixa de verificação na sua planilha, gráfico ou folha
de diálogo.
Botão de Opção, cria um botão de opção na sua planilha, gráfico ou folha de
diálogo, seja um botão de um grupo de botões de opção dentro de uma caixa de
grupo ou um botão de opção individual.
Caixa de listagem, cria uma caixa de listagem na sua planilha, gráfico ou folha de
diálogo.
Drop Down, cria uma combinação de caixa de listagem e caixa de edição na sua
planilha ou na sua folha de diálogo.
Combinação caixa de listagem-edição, cria uma combinação de caixa de listagem
e caixa de edição na sua folha de diálogo.
Combinação caixa de edição-drop down, cria uma combinação de caixa de
listagem drop-down e caixa de edição na sua folha de diálogo.
Barra de rolagem, cria uma barra de rolarem na sua planilha, gráfico ou folha de
diálogo.
Controle giratório, cria um controle giratório na sua planilha, gráfico ou folha de
diálogo.
Propriedades de Controle, exibe a guia "Controle" na caixa de diálogo Formatar
Objetos para que se possa visualizar ou mudar as propriedades dos controles
selecionados na sua planilha, gráfico ou folha de diálogo.
Editar código , edita ou cria um código para o objeto selecionado. Se atualmente
existir um código atribuído ao objeto, clicar sobre este botão abrirá o módulo do
Visual Basic ou a folha de macro do MS-Excel 4.0 que contém o código. Se não
existir código algum atribuído ao objeto, clicar sobre este botão abrirá um módulo
novo do Visual Basic e criará um procedimento vazio que será automaticamente
atribuído ao objeto.
Alternar grade, exibe ou oculta a grade usada para alinhar objetos em uma planilha
ou folha de diálogo.
Executar caixa de diálogo, executa a caixa de diálogo personalizada que estiver
sendo editada na folha de diálogo.
Dos recursos acima descritos, os que estão indicados para serem utilizados diretamente na
planilha do MS-Excel serão vistos no nosso exemplo. Caso a barra de ferramentas
“Formulário” não esteja exibida, clique em:
Exibir / Barra de ferramenta / Formulário
Como exemplo criaremos uma planilha que calcule o preço de um produto por região,
exibindo automaticamente o custo, o preço de venda que calcula o valor das parcelas
conforme o número do pedido e a quantidade de parcelas. A planilha deverá ter a aparência
da figura abaixo:
Digitaremos duas planilhas. Uma terá o valor dos produtos por região (tabela de preços) e a
outra será onde os valores serão mostrados (consulta).
Digite as planilhas e formate conforme o exemplo.
- Consultas
- Tabela de Preço
Para escolha de Produto será mostrado automaticamente uma lista de todos os produtos
através de uma caixa de combinação. Para criar clique em “Caixa de Combinação” e
preencha conforme o exemplo das figuras abaixo :
Precisaremos criar um vínculo em uma célula qualquer para facilitar nosso trabalho.
Após criar esses elementos a planilha deverá fica igual a figura abaixo:
Para exibir as Regiões e permitir que apenas uma delas seja escolhida, criaremos uma caixa
de grupo com o nome “Região”. Crie também botões de opção para cada região com a
ferramenta “Botão de Opção”
Precisamos criar também um vínculo para os botões de opção.
Após criar esses elementos a planilha deverá fica igual à figura abaixo:
Na planilha “Tabela de Preços” nomeie o intervalo dos valores para “ValorRegiao” pois iremos
precisar deste nome para criar a função “Índice”. Para isso selecione toda área de dados que
possuem os preços dos produtos (da célula B4 até F10) e coloque o nome definido.
Para exibir o custo usaremos a função ÍNDICE que busca uma determinada posição em um
intervalo de valores. No nosso caso digite =ÍNDICE(ValorRegiao;J5;J6), onde ValorRegiao é
o nome do intervalo dos valores, J5 é o vinculo da célula que indica qual o produto foi
selecionado e J6 é o vínculo da célula que indica qual região foi selecionada. Por exemplo, se
for selecionado o produto Cadeira o valor de J5 será 3 ; se for selecionada a região Nordeste
o valor de J6 será 4 e com isso a função índice busca a posição 3,4 no intervalo de valores
ValorRegiao.
Para o Preço de Venda digitamos uma fórmula simples =C7*130% , para um valor de lucro
definido de 30%.
Para mostrar o valor obtido de lucro usaremos uma caixa de seleção para mostrar o valor do
lucro. Caso queira que mostre o lucro da venda, essa caixa de seleção deverá estar marcada,
caso esteja desmarcada não mostrará nada. Para isso crie a “Caixa de seleção” conforme a
figura abaixo.
Precisamos criar também um vínculo para essa caixa de seleção.
Na célula que se deseja mostrar o valor do lucro digite a formula condicional que analisa se a
caixa de seleção está marcada ou não através do valor na célula de vínculo (VERDADEIRO
quando a caixa está selecionada e FALSO quando não está):
=SE(J8=VERDADEIRO;C9-C7;"") esta formula calculará o valor do lucro
Para definir a quantidade de parcelas mínimas e a quantidade de pedido mínimo usaremos
dois elementos ‘‘Barra de Rolagem’’ para mostrar os valores possíveis:
No item pedido mínimo faça conforme a figura abaixo:
Para exibir o Valor das Parcelas devemos calcular através do preço de venda * pedido
mínimo / nº das parcelas :
=C9*C17/C16
Para que o usuário não fique com acesso às células de vínculo poderemos ocultá-las
selecionando a coluna onde as células estão, clicando com o botão da direita do mouse e
escolhendo a opção Ocultar :
Após isso a planilha de Consultas deverá da seguinte forma :
PROCV
No Excel, há possibilidades de se trabalhar com múltiplas funções de várias categorias. Por
exemplo: podemos associar uma função de procura de referência com uma função financeira,
para encontrarmos um determinado valor, e ainda dentro dessa operação podemos inserir
uma função lógica que ajudará principalmente na condição de uma eventual alteração dos
valores devido às alterações nas informações que ela pode armazenar.
Em se tratando de lógica e procura de referência, vamos estudar duas das principais funções
dessas duas categorias.
Em primeiro lugar vamos saber como funciona a procura de referência de valores. Para isso
vamos estudar a fórmula PROCV, por ser mais usada quando se faz uma planilha,
principalmente quando queremos buscar informações de outra planilha ou de um campo
dentro da mesma tabela.
PROCV age exatamente como uma variável num banco de dados por exemplo. Ela busca a
informação e associa ao conjunto de informações de sua atual operação. Para isso basta criar
uma planilha com determinados valores, sempre numerando a primeira coluna em ordem
crescente, para que PROCV as encontre dentro dessa ordem, caso contrário você receberá
uma mensagem ( #N/D ). A sintaxe básica é a seguinte :
=PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)
Onde:
Valor_procurado - é o valor a ser procurado na primeira coluna da matriz. Valor_procurado
pode ser um valor, uma referência ou uma seqüência de caracteres de texto. Textos em
maiúsculas e minúsculas são equivalentes.
Matriz_tabela - é a tabela de informações em que os dados são procurados. Use uma
referência para um intervalo ou nome de intervalo, tal como Banco de dados ou Lista. Os
valores na primeira coluna de matriz_tabela podem ser texto, números ou valores lógicos.
Num_indice_coluna - é o número da coluna onde a informação será buscada para ser
exibida na mesma linha onde o valor_procurado for encontrado na matriz de dados.
Procurar_intervalo - é um valor lógico que especifica se você quer que PROCV encontre a
correspondência exata ou uma correspondência aproximada. Se VERDADEIRO ou omitida,
uma correspondência aproximada é retornada; em outras palavras, se uma correspondência
exata não for encontrada, o valor maior mais próximo que é menor que o valor_procurado é
retornado. Se FALSO, o PROCV encontrará uma correspondência exata. Se nenhuma
correspondência for encontrada, o valor de erro #N/D é retornado.
Como exemplo criaremos uma planilha com uma consulta de endereço a partir de nomes que
serão digitados. A planilha deverá ter a aparência da figura abaixo:
Digitaremos duas planilhas. Uma terá as informações de endereços cadastradas (Cadastro) e
a outra será onde os valores serão mostrados (Procv_1).
Digite as planilhas de Cadastro como o exemplo abaixo.
Ainda na planilha “Cadastro” nomeie o intervalo dos valores para “dados1”, pois iremos
precisar deste nome na função PROCV. Para isso selecione toda área de dados que
possuem os dados (da célula B2 até F16) e coloque o nome definido.
Na planilha de consulta, na célula que exibirá o endereço, vamos usar a função PROCV da
seguinte forma :
=PROCV(D5;dados1;2;0)
Onde :
D5 – célula onde o nome está sendo digitado
dados1 – matriz de dados onde o nome será procurado
2 – número da coluna da matriz de dados onde está a informação que deverá ser exibida
0 – para não exibir nenhum dado (#N/D) caso o valor procurado não seja encontrado
Por exemplo, se digitarmos o valor Lilian na célula de nome o PROCV encontrará o seguinte
valor para Endereço :
Seguindo esse raciocínio criaremos as células PROCV para exibir os valores do Bairro,
Cidade e Estado :
=PROCV(D5;dados1;3;0)
=PROCV(D5;dados1;4;0)
=PROCV(D5;dados1;5;0)