Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Encontra documentos específicos para os exames da tua universidade
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
excel avançado, conceito e função
Tipologia: Notas de estudo
1 / 76
Esta página não é visível na pré-visualização
Não perca as partes importantes!
Quando criamos uma nova planilha a preocupação inicial deve ser sempre com as informações digitadas deixando para depois a formatação das mesmas.
O menu Formatar/Células serve para formatar as informações digitadas de acordo com a nossa preferência. Encontramos neste menu as opções Número, Alinhamento, Fonte, Borda, Padrões e Proteção. Com a opção Número, define-se a forma como será apresentado os dados digitados. Ex.: R$ 1.200,00 - 1.200,00 - 1200,00 - 1200 - 1.200 etc,
Observe acima as formas de formatação para horas e datas. Alinhamento
Nesta opção podemos definir como será o alinhamento dos dados na célula. Marque o item Retorno automático de texto para que os dados digitados sejam organizados dentro da própria célula. Ex.: Hora inicial Hora final Total de horas Marque a opção Reduzir para ajustar para que os dados digitados caibam na célula sem que para isso seja feito o retorno automático do texto. Com a opção Mesclar células pode-se mesclar várias células fazendo com que fique sendo uma célula apenas. Ex.: Controle de horas Hora inicial Hora final
Esta opção é a mais básica do Excel, pois usamos para somar valores. =SOMA(A1:A10) - soma os valores das células A1 até A10. =SOMA(A1:A10;B1:B10) - soma os valores das células A1 até A10 e B1 até B10. Para obter a soma das células A1 até A10 poderíamos também usar uma forma simples como: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A
Mas se o intervalo das células for do A1 até A500 fica mais fácil usando =SOMA(A1:A500). As operações matemáticas usadas normalmente são as seguintes: Potenciação ^ Multiplicação * Divisão / Soma + Subtração - Para montar uma fórmula deve-se observar a regra matemática para as prioridades das operações. Se na fórmula não colocarmos parênteses ( ) o Excel irá aplicar a regra matemática. Primeiro será considerado a potenciação, depois a multiplicação, divisão, soma e subtração. Observe no exemplo a seguir que usaremos as mesmas operações, mas pode-se obter resultados diferentes. = 2 + 2 * 5 - o resultado será 12. = (2+2) * 5 - o resultado será 20. Quando usamos parênteses, este passa a ter prioridade sobre as demais operações.
Quando copiamos uma fórmula =A1B1 para as linhas abaixo o Excel entende que as fórmulas devem ser relativas ou seja, mudam de acordo com as linhas. No exemplo usado =A1B1 sendo relativas, mudam para =A2B2, =A3B3 e assim por diante. Mas quando um determinado endereço deve permanecer absoluto, antes de
Ex.: Na fórmula =A1B1 queremos que quando copiar para as linhas abaixo o endereço B1 permaneça absoluto, ou fixo. =A1B$
tomar caso o teste seja verdadeiro. Após o segundo ponto e vírgula ( ; ) deve constar a ação caso o teste seja falso. Para verdadeiro ou falso podem ser usadas fórmulas, palavras e até mesmo outra condição, que chamamos de condição encadeada. =SE(A1>10000;”Legal”;”Que pena” ) Observe que as palavras devem estar entre aspas ( “”). Neste exemplo deve aparecer a palavra Legal se o teste for verdadeiro, caso contrário, se for falso deve aparecer a palavra Que pena. Observe atentamente a regra seguinte: = SE( teste ; ação para verdadeiro ; ação para falso ) Outro exemplo, se a região das vendas for SUL a comissão será 10%, caso contrário será 5%. Na célula A1 está a região e B2 está o valor. =SE(A1=”SUL”;B20,10;B20,05) Usando OU e E junto com o SE Se a região for SUL ou NORTE ou LESTE a comissão será de 10%, caso contrário será de 5%.
Observe bem a colocação dos parênteses e o ponto e vírgula. No ponto destacado em vermelho está as várias condições que estamos perguntando, separados por ponto e vírgula. Pode-se pôr várias opções. Para a condição ser verdadeira, qualquer uma das opções serve, SUL, NORTE ou LESTE. A opção OU na fórmula está isolado por parêntese. OU(A1=”SUL”;A1=”NORTE”;A1=”LESTE”). Somente depois é que vem o primeiro ponto e vírgula onde constará a opção verdadeira e logo após, no segundo ponto- e-vírgula, a opção falsa. Exemplo para o E junto com o SE. Se o total das vendas for entre R$10.000,00 e R$20.000,00, a comissão será de 10%, caso contrário será de 5%.
Para a condição ser verdadeira, as opções destacadas em vermelho devem necessariamente serem verdadeiras, ou seja o valor deve ser entre R$10.000,00 e R$20.000,00. Diferente do OU onde qualquer opção poderia ser verdadeira. Usando condição encadeada Usamos quando houver mais que uma condição para testar. No cálculo do INSS deverá ser respeitada uma tabela divulgada pelo governo. Salário até R$ 100,00 o desconto será de 8%. Salário de R$ 101,00 até R$ 300,00 o desconto será de 9%. Salário de R$ 301,00 até R$ 500,00 o desconto será de 10%. Salário acima de R$ 500,00 o desconto será de R$ 80,00. Na célula A1 está o valor do salário que vamos testar. = SE(A1<=100;A18%;SE(A1<=300;A19%;SE(A1<=500;A1*10%;80))) Pode-se colocar até 7 condições encadeadas. Os operadores lógicos são:
maior
= maior ou igual
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
colchetes [ ], e logo após o nome da planilha e antes da célula, deve constar o
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, 20 MERCEDES Azul 50.000, 30 FERRARI Vermelha 150.000, 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 Inserir/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 digitaremos o modelo e automaticamente deverá buscar a descrição, cor e valor, ficando apenas o campo Qtd para digitar. Modelo Descrição Cor Valor Pedido Qtd Valor Total
Chave é a informação em comum nas duas planilhas, sendo que na tabela necessariamente deve ser a primeira coluna para que o Excel possa pesquisar. 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 exatas 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.
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: Chave Modelo Tamanho Cor Valor 1033 10 33 Branco 37, 1034 10 34 Preto 41, 2033 20 33 Branco 38, 2034 20 34 Preto 45,
Através do menu Formatar/Formatação Condicional podemos definir uma formatação para uma ou mais células com até 3 critérios. Por exemplo: Se a data de entrega do pedido for menor do que hoje, deve ser formatado com a fonte vermelha e em negrito itálico para que chame atenção o pedido que está em atraso.
Como já comentamos anteriormente na função PROCV, é importante definir nomes para as células, pois assim fica mais fácil de montar uma fórmula. Ex.: = TOTALJULHO + TOTALAGOSTO
Em um cadastro de pedidos, queremos fazer um resumo com o total de pares e valor dos clientes. Cliente Ref. Preço Unitário Pares total R$
Pode-se usar o botão colar função E selecionar a opção SOMASE. Mais uma vez usamos neste exemplo nomes para regiões na planilha para facilitar quando na montagem da fórmula. Cadped é o intervalo no banco de dados onde abrange desde a primeira informação em A2 até E21.
- Beira Rio 21 2,50 12.541 31.352, - Musa 14 3,80 6.500 24.700, - Azaléia 27 1,20 3.251 3.901, - Beira Rio 10 3,60 5.400 19.440, - Beira Rio 22 5,50 1.200 6.600, - Bibi 26 8,90 3.220 28.658, - Bibi 19 8,70 2.355 20.488, - Bibi 20 7,90 1.254 9.906, - Beira Rio 15 10,50 5.200 54.600, - Amapá 24 25,00 3.620 90.500, - Musa 23 2,30 1.200 2.760, - Amapá 10 3,60 1.350 4.860, - Amapá 16 2,50 1.255 3.137, - Bibi 28 1,20 2.500 3.000, - Azaléia 10 5,90 1.200 7.080, - Azaléia 10 8,60 1.200 10.320, - Musa 25 6,50 352 2.288, - Bibi 29 8,70 154 1.339, - Dilly 18 6,90 1.200 8.280, - Azaléia 17 4,80 1.200 5.760, - 56.152 338.972, Um indicador em vermelho aparece na célula indicando que existe um comentário. Podemos definir se este comentário ficará sempre visível ou se apenas aparecerá o indicador. Esta definição está disponível no menu Ferramentas/Opções orelha Exibir. 3 – Terceira parte Uso do comando FILTRAR Função SUBTOTAL no modo FILTRAR
Classificação do Banco de Dados Subtotais Tabela Dinâmica Formulário Validação
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, 2548 Musa 14 3,50 6.500 22.750, 2561 Azaléia 27 7,80 3.251 25.357, 2547 Beira Rio 10 9,60 5.400 51.840, 2556 Beira Rio 22 5,20 1.200 6.240, 2560 Bibi 26 3,60 3.220 11.592, 2553 Bibi 19 4,20 2.355 9.891, 2554 Bibi 20 1,20 1.254 1.504, 2549 Beira Rio 15 1,20 5.200 6.240, 40.921 166.768, 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, 2547 Beira Rio 10 9,60 5.400 51.840, 2556 Beira Rio 22 5,20 1.200 6.240, 2549 Beira Rio 15 1,20 5.200 6.240, 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. Também é possível personalizar o filtro. Exemplo, filtrar campo Total R$ onde o valor é maior que R$ 10.000,00.