MICROSOFT EXCEL 2007
Apostila do Excel 2007
Tem como objetivo informar sobre o uso do Excel 2007, suas funções e os recursos disponíveis.
Andrea Roseli Moreira Cruz Jankoski Curitiba, 2011
1. Conceito
O Excel for Windows é uma planilha eletrônica capaz de armazenar dados, executar cálculos, gerar gráficos, importar e exportar valores de banco de dados. Uma das suas principais características é a capacidade de calcular operações a partir de fórmulas criadas pelo usuário utilizando as informações digitadas na planilha e fora dela.
No nosso dia a dia podemos construir uma planilha no Excel para fazer o controle do extrato bancário, controle de notas escolares, controle de estoque da empresa, despesas e receitas de casa, controlar o pagamento de funcionários e qualquer outro controle que necessite de resultados automáticos calculados.
2. Conteúdo
Uma Planilha Eletrônica é composta de interseções feitas do encontro de linhas e colunas. Para entendermos sua funcionalidade, veremos alguns conceitos do Microsoft Excel:
Linha: As linhas ocorrem na horizontal e são identificadas por números. (1, 2,3)
Coluna: As colunas ocorrem na vertical e são identificadas por letras. (A, B, C)
Células: É a interseção entre linhas e colunas. As células recebem os dados digitados pelo usuário ou fórmulas que calculam e retornam algum valor, dependendo das referências usadas na fórmula. O nome da célula é a identificação da coluna mais a linha (Ex.: A1, E5).
Um arquivo do Excel 2007 (a extensão é XLSX) é na verdade uma pasta de planilhas. Dentro de uma pasta podemos ter quantas planilhas quisermos (Plan1, Plan2, Plan3, etc). Com o botão direito do mouse sobre o nome da planilha, podemos inserir outra planilha, excluir ou alterar o seu nome.
3. Guias
Sem dúvida, o maior diferencial que o Excel 2007 possui em relação as suas versões anteriores é a substituição das Barras de Menus e Ferramentas pelas Guias que foram organizadas em: Início, Inserir, Layout da Página, Fórmulas, Dados, Revisão e Exibição. Comenta-se que a razão desta substituição foi o fato de que muitas ferramentas, opções e atalhos presentes nas versões mais antigas do Excel eram totalmente desconhecidos por seus usuários. Isso acontecia porque, nessas versões, a Barra de Ferramentas era padronizada de acordo com a freqüência de uso destes recursos e o espaço disponível para visualização, deixando assim, várias ferramentas, opções e atalhos ocultos aos usuários das versões 97, 2000, XP e 2003 do Excel. Entretanto, com a utilização de Guias tanto a visualização quanto a utilização dos recursos acima citados tornaram-se extremamente mais fáceis e práticos para os usuários do Excel 2007.
Há três componentes básicos para a Faixa de Opções:
1 – Guias: Existem sete guias na parte superior.
Cada uma representa tarefas principais executadas no Excel.
2 – Grupos: Cada guia tem grupos que mostram itens relacionados reunidos.
3 – Comandos: Um comando é um botão, uma caixa para inserir informações ou um menu.
3.1 Guia Início
3.2 Guia Inserir
3.3 Guia Layout da Página
3.4 Guia Fórmulas
3.5 Guia Dados
3.6 Guia Revisão
3.7 Guia Exibição
4. Pasta de trabalho e Planilhas
Como foi dito anteriormente, uma pasta de trabalho possui mais de uma planilha. Você pode mudar o nome dando duplo-clique sobre o nome da planilha na guia. Você pode inserir novas planilhas, excluir, mover ou criar uma cópia de uma planilha pronta (é muito útil quando temos planilhas de período, como folha de pagamento, receitas e despesas mensais, etc).
5. Teclas de atalho, células e dicas de formatação
Mudar de planilha: Ctrl + PageUp ou PageDown – alterna entre Plan1, Plan2, Plan3, etc.
Ir direto para a primeira célula (A1): Ctrl + Home.
Ir direto para a última linha da planilha: Ctrl +
Selecionar mais de uma planilha ao mesmo tempo: seleciona uma planilha, segura Shift e seleciona a última planilha. Se você formatar uma célula com mais de uma planilha selecionada, as células das outras planilhas também serão formatadas.
Editar conteúdo de célula: seleciona a célula desejada e aperte F2 (ou duplo-clique).
Inserir quebra de título: ao digitar ou editar uma célula, tecle Alt + Enter na posição que você quer que os dados sejam divididos em mais de uma linha.
Aumentar ou diminuir altura e largura de células: Se o conteúdo da célula for maior que a largura da coluna, basta clicar entre a divisão das colunas (onde há o nome A, B, C, D...) e arrastar para aumentar ou diminuir a coluna. Faça o mesmo para a altura da linha. Se você der duplo-clique, o próprio Excel procura a maior informação digitada e altera o tamanho automaticamente. Veja, na figura abaixo, o ponto exato que você deve colocar a seta do mouse:
1. Seleção de células: apenas clicar com a seta do mouse sobre a célula desejada. Para movimentar sobre outras células, utilize as setas do teclado, tecla <tab> ou o próprio mouse
2. Uma faixa ou várias faixas de células: clicar na primeira célula desejada e arrastar a seta do mouse até a última célula desejada. Com o teclado utilize as setas mais a tecla <shift>. Para faixas não- adjacentes: Selecione uma faixa normalmente, solte o mouse, segure a tecla <ctrl> e selecione as outras faixas desejadas.
Uma faixa de células Várias faixas de células
6. Digitação de valores literais
A digitação de dados no Excel possui duas informações: valores literais e fórmulas.
Os valores literais são informações fixas, como nome, endereço, salário, data de nascimento, porcentagens, identidade, cpf, nome de carro, etc. Quando digitamos estes dados, precisamos ficar atentos à disposição dos dados, deixando as informações estruturadas, isto é, cada coluna com dados semelhantes (nomes na coluna A, salário na coluna B, descontos na coluna C). Ao digitar os dados na planilha, podemos formatar a planilha para adequar melhor às informações, utilizando-se de cores, formatação de moedas, casas decimais, tamanho e tipo da fonte, alinhamento, etc. Veja um exemplo de formatação:
SEM FORMATAÇÃO COM FORMATAÇÃO
Para familiarizar com o Excel, abra uma nova planilha e digite os dados abaixo. Formate as informações para melhorar a estética da planilha:
Quando terminar de digitar e formatar esta planilha, clique no Botão do Office / Salvar e grave o arquivo com o nome PrimeiraPlanilha.xls.
7. Operadores
Quase todas as fórmulas conterão algum operador matemático. Esses operadores indicam qual tipo de operação será realizada.
OPERADOR NOME EXEMPLO COMENTÁRIO
+ Adição =A1+B1 Soma o conteúdo das células A1 e B1
- Subtração =A1-B1 Subtrai o conteúdo das células A1 e B1 / Divisão =A1/B1 Divide o conteúdo das células A1 e B1
* Multiplicação =A1/B1 Multiplica o conteúdo das células A1 e B1
% Percentual =A1*20% Multiplica o conteúdo da célula A1 por 0,2
^ Exponenciação =A1^3 Eleva o conteúdo da célula A1 à 3ª potência
Operadores especiais: avaliam dois valores e retornam o valor lógico verdadeiro (true) ou falso (false)
OPERADOR NOME EXEMPLO RETORNA
= Igualdade =20=20 Verdadeiro
> Maior que =20>21 Falso
< Menor que =20<21 Verdadeiro
>= Maior ou igual a =20>=21 Falso
<= Menor ou igual a =20<=21 Verdadeiro
<> Diferente de =20<>21 Verdadeiro
Quando você cria uma fórmula contendo mais de um operador do mesmo tipo, as operações matemáticas vão sendo realizadas da esquerda para direita até que a última tenha sido efetuada. Contudo, quando você mistura os operadores, o Excel segue uma tabela de prioridades executando determinadas operações matemáticas antes de outras.
OPERADOR DESCRIÇÃO
% Percentual
^ Exponenciação
* e / Multiplicação e divisão
+ e - Adição e subtração
=< > <= >= < > Comparação
8. Fórmulas
Até agora nós digitamos valores literais nas células como nomes, valores e datas. As fórmulas são digitadas nas células da mesma forma, porém é retornado para a célula o valor calculado pela fórmula.
Muito importante: toda fórmula é precedida do sinal de igualdade ( = ).
Veja o exemplo abaixo:
Veja os exemplos de fórmulas utilizando as 4 operações matemáticas:
9. Fórmulas com porcentagens
Para calcular uma porcentagem de um valor de célula utilizamos, normalmente, a multiplicação.
Quando digitamos, em uma célula, um valor percentual (%), na verdade o Excel calcula como um valor absoluto dividido por 100. Se digitarmos 9% o valor da célula é 0,09. Por isso, é errado criar fórmulas de soma ou subtração de porcentagens.
Fórmulas ERRADAS utilizando porcentagens:
Acrescentando um valor errado de porcentagem: =B2 + 8%
Diminuindo um valor errado de porcentagem: =B2 – 9%
O primeiro exemplo está, na verdade, somando R$ 0,08 (oito centavos) ao valor digitado na célula B2 e o segundo exemplo está diminuindo R$ 0,09 (nove centavos) do valor.
Fórmulas CORRETAS utilizando porcentagens:
Acrescentar um valor: =B2 + B2 * 8%
Calcular o valor da porcentagem: =B2 * 9%
Diminuir o valor da porcentagem: =B2 - B2 * 9%
10. Referência Relativa e Referência Absoluta
10.1 Referência Relativa
Quando criamos uma fórmula, as referências de células são modificadas automaticamente quando copiadas às demais linhas de dados. Chamamos isso de Referência Relativa. Exemplo:
A B C
1 Nome Salário Salário mais aumento Quando criamos a primeira fórmula (C2) as fórmulas da
C3 e C4 são apenas copiadas.
2 Maria da Silva R$ 500,00 =B2 + B2 * 10%
3 José Santos R$ 800,00 =B3 + B3 * 10%
4 Ana de Souza R$ 750,00 =B4 + B4 * 10%
Por causa da referência relativa, devemos criar a planilha com uma estrutura prática de dados, que possibilita desenvolver fórmulas que podem ser copiadas para a mesma coluna ou mesma linha, aproveitando a alteração automática da referência das células durante a cópia das fórmulas. Os valores fixos, como valor percentual, dinheiro ou data permanecem com o mesmo valor durante a cópia da fórmula.
10.2 Referência Absoluta
Na criação de uma fórmula, usamos a referência de alguma célula que, independente para onde copiamos a tal fórmula, sua referência continua a mesma. Para isso a referência da coluna e/ou da linha deve ser precedida do sinal $. Chamamos de Referência Absoluta.
Exemplo:
A B C
1 Nome Salário Salário mais aumento Os três funcionários estão recebendo o salário da coluna B com um aumento
de 50 reais, digitados na célula B6
2 Maria da Silva R$ 500,00 =B2 + $B$6 3 José Santos R$ 800,00 =B3 + $B$6 4 Ana de Souza R$ 750,00 =B4 + $B$6 5
6 Valor do Bônus R$ 50,00
A referência absoluta é utilizada principalmente com índices comuns à planilha, e normalmente são digitadas no final do corpo da planilha. Como exemplo, podemos dar o salário mínimo, porcentagens de aumento ou desconto, valor do dólar, horas trabalhadas, impostos a cobrar, etc. Sempre que um valor possa ser utilizado em várias fórmulas, ao invés de indicá-lo como valor fixo na fórmula, use-o sempre como referência absoluta. A grande vantagem do seu uso é quando um valor informado for alterado, automaticamente a fórmula calculará o novo valor quando o valor desta célula for modificado pelo usuário.
Para criar uma referência absoluta, você pode teclar F4 com o cursor próximo à célula para inserir o símbolo $, ou você mesmo pode digitá-lo.
11. Funções simples
Para desenvolvermos fórmulas, vimos que podemos usar as referências das células e os operadores da matemática para criarmos cálculos necessários na planilha. Porém, as fórmulas podem ficar complexas ou grandes, de acordo com nossa necessidade. Então podemos criar fórmulas utilizando FUNÇÕES do Excel, que são comandos com um objetivo específico, enviando parâmetros para serem calculados e retornando o valor esperado. Vamos ver seis funções bem simples, mas antes veja algumas considerações:
Os parâmetros são informados dentro de parênteses:
=NOME_DA_FUNÇÃO(PARÂMETROS)
Os parâmetros são separados por ponto-e-vírgula:
=NOME_DA_FUNÇÃO(PARÂMETRO1; PARÂMETRO2; PARÂMETROn) Uma faixa de valores deve ser separada por dois-pontos:
=NOME_DA_FUNÇÃO(CÉLULA1:CÉLULA2)
Dependendo da função, pode haver as duas situações, ou seja, separação de parâmetros com faixa de valores:
=NOME_DA_FUNÇÃO(CÉLULA1:CÉLULA2; CÉLULA3:CÉLULAn)
Se trabalhar com valores numéricos, digite-os sem formatação nenhuma (não digite R$ ou separação de milhar). Quando informar casa decimal, use a vírgula.
Quando informar um valor texto, use aspas:
=NOME_DA_FUNÇÃO(1500; “FUSCA”; 567,15)
O nome da função pode ser digitado em letras maiúsculas ou minúsculas.
11.1 Função SOMA
Tem o objetivo de somar o valor numérico das células enviadas como parâmetro. Veja os exemplos abaixo e o significado de cada uma:
=SOMA(B3:B10) => está somando os valores digitados das células B3 até B10.
=SOMA(B3;B5;B10) => está somando os valores das células B3, B5 e B10.
=SOMA(B3:B10;C3:C10) => está somando os valores das células B3 até B10 e das células C3 até C10.
Poderia ser digitada assim também: =SOMA(B3:C10)
11.2 Função MÉDIA
Retorna a média dos valores das referências definidas como parâmetro. Os parâmetros podem ser digitados da mesma maneira utilizado pela função SOMA.
=MÉDIA(B3:B10) => Retorna o valor médio da soma das células B3 a B10.
11.3 Função MÁXIMO
Retorna o maior valor dos valores enviados como parâmetro.
=MÁXIMO(B3:B10) => retorna o maior valor entre as células B3 e B10.
11.4 Função MÍNIMO
Retorna o menor valor dos valores enviados como parâmetro.
=MÍNIMO(B3:B10) => retorna o menor valor entre as células B3 e B10.
11.5 Função MAIOR
Retorna o N maior valor de um conjunto de células, onde N é uma posição desejada e informada na função. Diferente da função Máximo, que só retorna o maior valor, a função Maior pode retornar também o segundo maior valor, o terceiro maior valor e assim por diante.
=MAIOR(B3:B10;1) => retorna o maior valor. Semelhante à função MÁXIMO.
=MAIOR(B3:B10;2) => retorna o segundo maior valor entre B3 e B10.
11.6 Função MENOR
Retorna o N menor valor de um conjunto de células, onde N é uma posição desejada e informada na função. Semelhante à função Mínimo, mas possui o segundo parâmetro, que informa qual o valor menor deve ser retornado.
=MENOR(B3:B10;1) => retorna o menor valor. Semelhante à função MÍNIMO.
=MENOR(B3:B10;2) => retorna o segundo menor valor entre B3 e B10.
Nome de células
Agora que aprendemos algumas funções, podemos continuar com a criação de fórmulas utilizando referência absoluta, que no Excel podem-se criar nomes específicos para uma célula ou um conjunto de células.
Voltando ao exemplo de agência de veículos: temos uma célula que possui o valor do dólar, a célula A8. Podemos declarar que a célula A8 também se chame DOLAR.
Veja que na coluna B há o preço de três veículos. Vamos chamar o conjunto destas células (B3, B4 e B5 de VALORES). Podemos declarar nomes para as células pela barra de fórmulas:
No primeiro exemplo, selecionamos a célula A8 e, na barra de fórmulas alteramos a referência A8 para DOLAR. No segundo exemplo, selecionamos as três células B3, B4 e B5 numa seleção múltipla e alteramos a referência para VALORES.
Assim, se precisarmos somar as três células podemos usar a função SOMA de outra forma e a fórmula que calcula o preço do carro em dólar:
12. Formatação Condicional
A formatação condicional é uma opção do Excel que permite alterar a formatação de uma célula (estilo e cor da fonte) dependendo de uma condição informada. Nesta opção podemos usar três tipos de formatação, ou seja, criar três tipos de condição. Vá até a Guia início formatação condicional encontrará a tela seguir:
Você pode utilizar a formação condicional para anotar visualmente seus dados para fins de análise e apresentação. Para localizar com facilidade as exceções e destacar tendências importantes nos seus dados, você pode implementar e gerenciar várias regras de formatação condicional que aplicam a formatação visual rica em forma de cores de gradiente, barras de dados, conjuntos de ícones a dados que atendem a essas regras.
Os formatos condicionais também são fáceis de serem aplicados — com apenas alguns cliques, você pode ver relações nos seus dados que podem ser usados para fins de análise.
13. Funções Condicionais
13.1 Função SE
A função SE retorna valores ou executa alguma operação, dependendo de uma condição definida no primeiro parâmetro. Desta forma, podem-se retornar dois valores: se a condição é verdadeira ou se a condição é falsa. Para isso, a função SE possui três parâmetros:
=SE (Condição ; Retorno_Verdadeiro ; Retorno_Falso)
A condição é qualquer comparação com valores ou células existentes na planilha. Os retornos podem ser simples valores literais, valores de células, textos ou cálculos. Exemplos:
* Se a célula B3 possuir um valor maior que 500 reais, exiba a palavra OK; se a célula B3 possuir menor ou igual a 500 reais, exiba BAIXO:
=SE(B3>500; “OK” ;”BAIXO”)
* Se a célula C4 possuir a letra F, exiba a palavra FEMININO; caso contrário exiba a palavra MASCULINO:
=SE(C4=”F”;”FEMININO”;”MASCULINO”)
* Se a célula D5 for igual a V, retorne a célula F5 com um desconto de 10 reais, senão aumente 5 reais:
=SE(D5=”V”; F5 – 10; F5 + 5)
* Se o salário (célula B4) é menor que 500 reais, aumente 5%, senão aumente 4%:=B4 + B4 * SE(B4<500; 5% ; 4%).
Outros Exemplos:
Primeiro SE: se o departamento digitado na célula C3 é igual a 1 exiba o nome BANCO, senão exiba o nome CAIXA.
Segundo SE: se o salário digitado na célula D4 é menor que 500 reais, exiba a palavra BAIXO, senão exiba a palavra ALTO.
TENTE: Digite a planilha acima e crie as fórmulas com SE:
Exiba o sexo FEMININO se sexo é igual a F, senão exiba MASCULINO;
Calcule o salário mais 30 reais se o salário é menor que 600 reais, senão aumente 50 reais;
Calcule e exiba o salário com um aumento de 10% se ele for até 550 reais, senão aumente 9%.
13.2 Função PROCV
De acordo com a condição a ser empregada numa fórmula, e a quantidade de retornos, a função SE poderá ficar muito grande ou até impossível de ser utilizada. Podemos utilizar 7 funções SE dentro da mesma fórmula. A função PROCV, como não depende de uma condição dentro da fórmula, pode ter várias condições de igualdade ou de faixas de valores para retornar quantos valores quiser. Isso só é possível porque a função PROCV utiliza uma matriz digitada dentro da própria planilha para retornar os valores.
A função PROCV significa PROCURA VERTICAL, onde a célula definida com um valor pesquisará na primeira coluna da matriz uma comparação e, se encontrar, retornará o valor de outra coluna da matriz. A função PROCV possui quatro parâmetros:
=PROCV(Célula_Comparação; Matriz_Digitada; Coluna_Retornada; Comparação_Igual) Célula_Comparação: A célula que possui um valor a ser pesquisado;
Matriz_Digitada: Na própria planilha, digita-se uma coluna com os valores de comparação com o primeiro parâmetro e nas outras colunas os valores que queremos retornar;
Coluna_Retornada: O número da coluna da matriz que irá retornar os valores na função;
Comparação_Igual: um valor lógico: a palavra Verdadeiro ou a palavra Falso. Se for verdadeiro, a comparação pode ser de igualdade ou de aproximação (igual ou maior que o valor procurado até o próximo valor de comparação). Se for Falso, só pode ser de igualdade.
** ATENÇÃO ** Toda vez que informar a matriz (segundo parâmetro) sempre o coloque como referência absoluta, pois quando você copiar a fórmula pra baixo, ela deve continuar da mesma forma.
. O primeiro PROCV é de comparação de igualdade. Se a célula B3 é IGUAL a um dos valores da primeira coluna da matriz (C11 a D12, então vai pesquisar na C11 até C12) ele vai retornar o valor da linha correspondente da coluna NÚMERO 2 (terceiro parâmetro). O FALSO significa que só pode ser igualdade.
. O segundo PROCV é de faixa de valores. Se o primeiro parâmetro (D4) vai de 0 até 499,99 (C14 até C16) ele exibirá a palavra BAIXO; se o D4 for de 500 até 999,99 ele exibirá MÉDIO e se D4 for 1000 ou um valor maior ele exibirá ALTO. NÃO há o quarto parâmetro, pois VERDADEIRO já é o valor padrão.
- TENTE: Digite a planilha acima e crie as fórmulas com PROCV:
Exiba BANCO se o departamento for 1; exiba CAIXA se o departamento for 2;
Se o salário for até 1000 exiba LIGHT; senão exiba TÁ BOM;
Se o salário for menor que 600 reais retorne o salário mais 50 reais; se o salário for de 600 até 1100 reais aumente 70 reais, senão aumente 100 reais.
13.3 Função CONT.SE
A função CONT.SE tem como objetivo contar quantas células fazem parte de uma condição especificada. Pode-se contar qualquer tipo de célula: data, texto ou valores.
Contanto que a condição seja digitada com o mesmo tipo.
=CONT.SE ( Faixa_de_Células ; Condição )
Faixa_de_Células: Identifica as células cujos valores serão contados, dependendo da condição informada. Pode ser uma faixa de mais de uma linha e/ou mais de uma coluna;
Condição: Uma comparação lógica usando valor do tipo dos valores da faixa de células.
Pode usar as condições de =, >, <, >=, <= e <>. É IMPORTANTE digitar a condição entre aspas, mas também pode ser identificada dentro de uma célula.
Exemplos:
a. Quantos vendedores são do estado de MG?
=CONT.SE(C3:C9;”MG”)
b. Quantos vendedores venderam mais de 5000 reais?
=CONT.SE(B3:B9;”>5000”)
c. Você também pode definir a condição digitada numa célula. Digite a letra F na célula D15 e crie a fórmula abaixo:
=CONT.SE(D3:D9;D15)
A fórmula acima está contando quantas mulheres são vendedoras. Se você alterar o valor da célula D15 para M, automaticamente ela contará quantos vendedores são homens.
13.4 Função SOMASE
A função SOMASE é muito parecida com a CONT.SE; seu objetivo é, ao invés de contar, somar os valores numéricos referentes à condição informada. Porém, esta função possui um parâmetro a mais. O terceiro parâmetro é quem define a faixa de células a somar, e o primeiro parâmetro, como o CONT.SE, continua sendo a faixa de células referentes à condição.
Continuando com a planilha de exemplo acima do CONT.SE, agora usando SOMASE:
a. Qual é a soma das vendas dos vendedores de MG?
=SOMASE(C3:C9;”MG”;B3:B9)
Significa somar todas as vendas entre B3 e B9, somente dos vendedores de MG, identificados de C3 a C9.
b. Quando a faixa de células a somar é a mesma da condição, não há necessidade de usar o terceiro parâmetro. Qual a soma das vendas menores que 10000 reais:
=SOMASE(B3:B9;”<10000”)
c. Se você digitar a condição numa célula, também pode-se usá-la como parâmetro de condição.
Digite na célula C15 a condição <>8000 e crie a fórmula:
=SOMASE(B3:B9;C15)
Significa somar todas as vendas diferentes de 8000 reais.
14. Funções de Bancos de Dados
Quando precisamos somar ou calcular média de valores, usamos as funções simples SOMA() e MÉDIA(). Se precisamos somar ou contar valores com uma condição, usamos as funções SOMASE() e CONT.SE().
Porém, quando temos mais de uma condição para calcular uma faixa de valores, precisamos usar funções que utilizam parte da planilha para criar essas condições. Ao usarmos funções de banco de dados (o nome da função começa com BD) é preciso bastante atenção nos títulos das colunas, pois são esses títulos
que serão usados para criarmos as condições. Para demonstrar a utilização dessas funções de banco de dados, usaremos a planilha abaixo como exemplo:
14.1 Função BDSOMA
Essa função soma uma coluna numérica, dependendo de uma ou mais condições definidas numa matriz na própria planilha, onde a primeira linha desta matriz deve ser o mesmo título da coluna das células que fazem parte da condição.
=BDSOMA(Faixa_de_Células; Título_a_Somar; Células_Condição)
. Faixa_de_Células : Devem ser selecionadas as colunas que possuem os dados das condições da matriz e a coluna que será somada, dependendo da(s) condição(ões). É obrigatório selecionar também as células que possuem os títulos das colunas. EXEMPLO:
Selecionar as células de B2 até C9 da figura acima, para somar o estoque do tipo Saída.
. Título_a_Somar : Pode ser digitada, entre aspas, o nome do título da coluna que será somada (por exemplo, a coluna “Estoque”). MAS, é definir, na fórmula, a referência da célula do título, por que se você digitar o nome diferente do que está definido no título, a fórmula não saberá somar os valores. EXEMPLO: Se a fórmula for somar a coluna ESTOQUE da figura acima, ao invés de digitar “Estoque”, prefira digitar a referência C2.
. Células_Condição : Faixa de células que define as condições para somar a coluna definida no segundo parâmetro. A primeira linha dessa faixa de células deve ser o nome do título da(s) coluna(s) também selecionada no primeiro parâmetro, que possui as colunas que definirão as condições. SUGESTÃO:
da mesma forma que, no segundo parâmetro, é melhor digitar a referência da célula do que digitar literalmente o título da célula, aqui também é mais seguro igualar os títulos de condição com os títulos da faixa de células. EXEMPLO: Na figura acima, na célula B11 não foi digitada a palavra “Tipo” e sim a fórmula
=B2, para transportar o nome para essa célula.
14.2 Função BDCONTAR
Conta quantas células da faixa informada fazem parte da condição verdadeira definida.
=BDCONTAR(Faixa_de_Células; Título_a_Contar; Células_Condição)
A sintaxe do do BDCONTAR é a mesma do BDSOMA, com a diferença de contar quantas linhas satisfazem a tabela de condições. Agora, o mais IMPORTANTE é que o segundo parâmetro, que possui o
nome da coluna a contar, deve ser obrigatoriamente um valor numérico. Se você escolher uma coluna que possua nomes, endereços, símbolos com caracter ele retornará zero. Exemplo: usando a planilha da página anterior, se quisermos contar quantos produtos são do tipo COMPUTADOR, o segundo parâmetro deve ser o título ESTOQUE ou UNITÁRIO, que possuem valores numéricos. Não use PRODUTO ou TIPO, pois possuem valores alfanuméricos: =BDCONTAR(A2:D9;”ESTOQUE”;B11:B12)
14.3 Função BDMÉDIA
Calcula o valor médio de uma coluna númerica, apenas das linhas que satisfazem a tabela de condições. =BDMÉDIA(Faixa_de_Células; Título_a_Calcular; Células_Condição)
Outra função semelhante com a BDSOMA. Ele soma os valores encontrados de acordo com as condições definidas e divide pela quantidade de células encontradas.
Exemplos:
De acordo com a figura da planilha da página anterior, veja o resultado da criação das fórmulas utilizando funções de banco de dados:
a. Qual é a soma do estoque dos produtos cujo preço unitário é maior que 1000 reais?
. Selecione as colunas juntamente com a linha que contém os títulos como primeiro parâmetro (C2 a D9); escolha a célula que possui o título da coluna a somar (C2) como segundo parâmetro e selecione a tabela de condições (F2 a F3):
=BDSOMA(C2:D9; C2; F2:F3)
b. Quantos produtos possuem estoque maior que 10 e o preço unitário menor que 1000?
=BDCONTAR(C2:D9; C2; F5:G6)
c. Qual é a média dos preços unitários dos produtos do tipo saída ou Acessório?
=BDMÉDIA(B2:D9; D2; F8:F10)
d. Quantos produtos são do tipo computador, com estoque menor que 10 e preço unitário maior que 2000 reais OU (mais os) produtos do tipo saída com preço unitário igual a 1200 reais?
. Note que agora usamos mais de uma linha na condição. Por isso: condições na mesma linha correspondem a E (AND), ou seja todas devem ser verdadeiras e quando se usa outra linha significa OU (OR).
=BDCONTAR(B2:D9; C2; B11:D13)
15. Funções Diversas
15.1 Função EXT.TEXTO
Permite retornar parte do valor de uma célula que contém um texto. É possível, através da identificação do início e da quantidade de caracteres, retornar apenas uma parte da célula.
=EXT.TEXTO(Célula ; Posição_Inicial ; Quantidade_Caracteres)
Observação: Pesquise sobre as funções DIREITA e ESQUERDA. Vale a pena, e tem a ver com a função EXT.TEXTO.
15.2 Função LOCALIZAR
Retorna o número da posição de um caractere ou uma seqüência de caracteres dentro de um valor informado, procurando dentro do valor de uma célula. É preciso informar qual a informação a procurar, em qual célula e qual a posição inicial do texto deve começar a procurar.
=LOCALIZAR(Texto_Procurado ; Célula ; Posição_Inicial da Procura)
15.3 Função MOD
Esta função matemática retorna o resto da divisão de valores. Numa divisão de 4 dividido por 2 o resultado é 2; o resto desta divisão é zero. A função MOD é utilizada em alguns cálculos matemáticos, como o cálculo de dígitos verificadores.
=MOD(Número ; Divisor)
15.4 Função INT
Outra função matemática que tem o objetivo de retornar o valor inteiro de um número, descartando o valor das casas decimais. Se o valor é 6,1 ou 6,9 o valor retornado é apenas 6.
=INT(Número)
15.5 Função ARRED
Diferente da função INT, a função ARRED arredonda o número informado com a quantidade de casas decimais desejadas, não descartando o cálculo da proximidade do valor da casa decimal. Portanto, se for informado 6,1 e 6,9 desejando nenhuma casa decimal, a função retornará 6 e 7, respectivamente.
=ARRED(Número ; Quantidade_Casas_Decimais)
16. Algumas funções matemáticas
16.1 Função POTÊNCIA
Fornece o resultado de um número elevado a uma potência.
Sintaxe: POTÊNCIA(núm;potência)
- Núm: é o número base. Pode ser qualquer número real.
- Potência: é o expoente para o qual a base é elevada.
Comentários:
O operador "^" pode substituir POTÊNCIA para indicar a potência pela qual o número base deve ser elevado, tal como em 5^2.
Exemplos: Calcule a potência dos seguintes números:
=POTÊNCIA(5;2) =POTÊNCIA(8;3) =POTÊNCIA(4;5/4)
=POTÊNCIA(98,6;3,2) =POTÊNCIA(9;4) =POTÊNCIA(10;10)
16.2 Função RAIZ
Retorna uma raiz quadrada positiva.
Sintaxe: RAIZ(núm)
- Num: é o número do qual você deseja obter a raiz quadrada. Se num for negativo, RAIZ retornará o valor de erro #NÚM!.
Exemplos: Calcule a raiz quadrada dos seguintes números:
=RAIZ(16) =RAIZ(81) =RAIZ(144)
=RAIZ(25) =RAIZ(324) =RAIZ(9)
16.3 Função ROMANO
Converte um algarismo arábico em romano, como texto.
Sintaxe: ROMANO(núm;forma) Comentários:
Se o número for maior do que 3999, o valor de erro #VALOR! será retornado.
Exemplos: Converta os seguintes números em romano clássico:
=ROMANO(500) =ROMANO(10) =ROMANO(100)
17. Filtragem
A ferramenta filtragem é boa para selecionar dados numa lista grande. Filtrar é exibir dados que atendem certa condição e oculta as demais. Selecione qualquer célula que contenham dados a filtrar. Vá até a Guia Dados e clique em Filtro. Agora clique em uma das setas que apareceram e escolha qual dado você deseja filtrar. Por exemplo: em uma planilha de funcionários, somente quero saber os que possuem cargo de assistente em administração.
Exemplo 1: Tabela normal com todos os dados:
Exemplo2: Tabela mostra somente o servidores com o cargo de assistente em administração:
18. Atingindo metas
O Excel oferece o recurso de projetar metas. A ferramenta pode ser usada para determinar um valor inicial, a fim de obter um resultado específico, considerando uma série de dados de uma planilha.
Exemplo: Uma determinada empresa possui um faturamento bruto de R$5.200,00 por mês. Com o pagamento de impostos mensais, o valor líquido cai para R$3.640,00. A meta é atingir lucro líquido de R$5.200,00.
Clique na guia Dados, Teste de Hipóteses, Atingir meta, e siga as figuras.
Observe que para se obter um lucro líquido de R$5.200,00 reais o valor do faturamento Bruto tem que ser R$7.428,57.
19. Protegendo dados
O Excel fornece diversos recursos para limitar a forma como usuários podem exibir ou alterar dados em pastas de trabalhos ou planilhas.
19.1 Protegendo
a. Selecione a guia Início / no Grupo Células clique em Formatar/ Proteger planilha;
19.2 Desprotegendo uma planilha
a) Selecione a guia Início / no Grupo Células clique em Formatar/ Desproteger planilha.
Se você pretende proteger somente algumas células e deixar outras desprotegidas, por exemplo células que contenham quantidades que podem ser modificadas, você deve primeiramente desbloquear essas células, selecionando as células que deseja desbloquear e depois clique na guia Início / Formatar células e clique em proteção e desmarque a caixa de opção bloqueadas. Clique em OK.
Veja o exemplo:
Depois é só clicar em Formatar/ Proteger Planilha e inserir uma senha.
20 Formulário de Dados
Após criar uma lista, você pode acrescentar, localizar, editar ou excluir usando o Formulário de Dados. Um formulário de dados é uma caixa de diálogo que exige um registro complexo por vez. Os campos que contêm valores constantes, que podem ser editados, aparecem em uma caixa de edição. Os campos calculados, que não podem ser editados, não aparecem em uma caixa de edição. Esses campos contêm os resultados de fórmulas.
Células selecionadas que você não deseja proteger.
Para obter o ícone do formulário, você deve clicar no botão do Office e depois no botão opções do Excel, Personalizar, Escolher comandos fora da faixa de opção e selecionar o ícone Formulário.
