Apostila Microplus Excel

Apostila Microsoft Excel

Apostila Microsoft Excel faz faz parte do material didático do treinamento Windows – Office Office da Microplus Educação Profissional. ©2014 Microplus Educação Profissional – Todos Todos Direitos Reservados

CURSO WINDOWS OFFICE Apresentação

Volume 3 - Treinamento Microsoft Excel Esta apostila compõe o material didático referente ao curso profissionalizante Windows - Office Office da Microplus Educação Profissional, composto pelo sistema operacional Windows, os aplicativos Word, Excel, PowerPoint e Access e do Treinamento em Digitação e Internet. Na sequência, apresentamos definições, orientações técnicas, procedimentos e exercícios práticos, fundamentais para qualificar profissionalmente jovens e adultos, para que possam ter autonomia no uso da ferramenta, dominar as competências exigidas pelo mercado de trabalho e, assim, favorecer sua empregabilidade em qualquer área ocupacional. Lembramos que as profundas transformações ocorridas no mundo do trabalho, a velocidade da informação, a comunicação, a globalização, a difusão de novas tecnologias e de novas formas de organização do trabalho são alguns exemplos das mudanças que vêm exigindo dos trabalhadores sua capacitação. A informática é parte fundamental dessa transformação, visto que está presente em todos os setores da sociedade: no comércio, na indústria, na saúde, na educação etc. Enfim, as informações aqui apresentadas servirão para alicerçar a base do conhecimento requerida para a construção das competências e habilidades propostas na qualificação de Operador de Computadores. Equipe Microplus Educação Profissional


Apresentação

4

CURSO WINDOWS OFFICE Sumário

AULA 41 – INTRODUÇÃO EXCEL ............................................................................................................................................. 8 O Excel ................................................................................................................................................................................... 8 Iniciando o programa ............................................................................................................................................................ 8 A janela do Excel..................................... ................................................................. ............................................................. . 8 Pastas de trabalho, planilhas e células. ................................................................................................................................ 9 Comandos de Movimentação do Cursor ............................................................................................................................... 9 Comandos de Edição ........................................................................................................................................................... 10 Como Entrar com Dados na Planilha ................................................................................................................................... 10 O que são Títulos ................................................................................................................................................................. 10 O que são Valores ................................................................................................................................................................ 10 O que são Fórmulas ............................................................................................................................................................. 10 AULA 42 – A PRIMEIRA PLANILHA ........................................................................................................................................13 Planilha de Orçamento Doméstico ...................................................................................................................................... 13 Formatar a Planilha ...................................................... ................................................................. ...................................... 13 Largura da Coluna ............................................................................................................................................................... 13 O Procedimento de Cálculo ................................................................................................................................................. 13 Uso da Função = SOMA ....................................................................................................................................................... 14 Formatar Valores Numéricos. ............................................................................................................................................. 14 Formatar Células - Alinhamento ......................................................................................................................................... 15 Formatar Células - Fonte ..................................................................................................................................................... 15 AULA 43 – COPIAR E COLAR, MOVER, FORMULAS, =SOMA ..................................................................................................17 Abrindo uma pasta de trabalho ......................................................... .............................................................. ................... 17 Seleção ................................................................................................................................................................................ 17 Recorte, cópia e colagem .................................................................................................................................................... 17 Movendo dados por arraste ................................................................................................................................................ 18 Inserindo linhas e colunas ................................................................................................................................................... 18 FÓRMULA SOMA ................................................................................................................................................................. 18 Botão Autosoma ........................................................... ................................................................. ...................................... 19 Somar Matrizes: .................................................................................................................................................................. 19 Somar Células Alternadas ................................................................................................................................................... 19 FÓRMULA SUBTRAÇÃO ....................................................................................................................................................... 20 FÓRMULA MULTIPLICAÇÃO ..................................................... .............................................................. .............................. 20 FÓRMULA DIVISÃO ....................................................... ................................................................. ...................................... 20 AULA 44 – FORMATAÇÃO, =MEDIA ......................................................................................................................................21 Barra de Formatação .......................................................................................................................................................... 21 Planilha Rendimento Escolar ............................................................................................................................................... 21 Copiar Células ...................................................................................................................................................................... 21 Formatar Casas Decimais .................................................................................................................................................... 22 Mesclar Células ................................................................................................................................................................... 22 Alinhamento ...................................................... ................................................................. ................................................. 23 Formula Média Aritmética .................................................................................................................................................. 23 USO DA FUNÇÃO =Média .............................................................................................................. ...................................... 24 AULA 45 – FORMATAÇÃO, USO DE : .....................................................................................................................................26 Uso de dois pontos e de ponto e vírgula ............................................................................................................................. 26 Formatação de Bordas e Cor de Preenchimento ................................................................................................................. 26 AULA 46 – OPERADORES, =MINIMO, =MAXIMO...................................................................................................................30

5


Sumário

CURSO WINDOWS OFFICE Sumário Operadores de cálculo e precedência ................................................................................................................................. 30 Tipos de operadores ............................................................................................................................................................ 30 Ordem de cálculo ................................................................................................................................................................ 31 PRECEDÊNCIA DE OPERADORES ............................................................ .............................................................. .............................. 31 Uso de parênteses ............................................................................................................................................................... 31 USO DA FUNÇÃO =MÁXIMO ............................................................................................................................................... 31 USO DA FUNÇÃO =MÍNIMO ................................................................................................................................................ 32 AULA 47 – PORCENTAGEM .................................................................................................................................................. 35 FORMULAS COM PORCENTAGEM ................................................................ ............................................................... ........ 35 AULA 48 – CABEÇALHO E RODAPÉ, IMPRIMIR..................................................................................................................... 39 Definir área de impressão ................................................................................................................................................... 39 Visualizar impressão ........................................................................................................................................................... 40 Margens .............................................................................................................................................................................. 40 Cabeçalho e Rodapé ............................................................................................................................................................ 40 Imprimir .............................................................................................................................................................................. 41 AULA 49 – REFERENCIA RELATIVA E ABSOLUTA, DATAS....................................................................................................... 43 Referencia de Células .......................................................................................................................................................... 43 Referência relativa .............................................................................................................................................................. 43 Referência absoluta ............................................................................................................................................................ 43 Utilizando Datas em planilhas do Excel .............................................................................................................................. 44 Formatando Datas .............................................................................................................................................................. 44 Calcular o numero de dias entre duas datas ....................................................................................................................... 45 Função =DIATRABALHOTOTAL ............................................................................................................................................ 45 Função =HOJE() ................................................................................................................................................................... 45 AULA 50 – FUNÇÃO =SE ....................................................................................................................................................... 47 Lógica de Programação ...................................................................................................................................................... 47 FUNÇÃO SE .......................................................................................................................................................................... 47 AULA 51 – ATIVIDADES FUNÇÃO =SE ................................................................................................................................... 51 Utilizando a Função SE ........................................................................................................................................................ 51 TRABALHANDO COM DIVERSAS PLANILHAS ....................................................................................................................... 53 AULA 52 – FORMATAÇÃO CONDICIONAL ............................................................................................................................. 55 Formatação Condicional ..................................................................................................................................................... 56 AULA 53 – FUNÇÃO =CONT.SE ............................................................................................................................................. 59 Atividade: Planilha Campeonato Futsal ................................................................................................ .............................. 59 FUNÇÃO =CONT.SE ....................................................... .............................................................. ......................................... 60 Exercícios de Fixação Função Cont.Se ................................................................................................................................. 61 AULA 54 – NOME DE CÉLULAS, =PROCV ............................................................................................................................... 63 Atividade: Planilha Controle de Comissões ............................................................. ............................................................ 63 A TOMADA DE DECISÕES ............................................................................. ............................................................... ........ 63 FUNÇÃO =PROCV ................................................................................................................................................................ 65 Nome de células .................................................................................................................................................................. 65 Juntar função =SE e =PROCV .............................................................. .............................................................. ................... 66 Formatação Condicional ..................................................................................................................................................... 66 AULA 55 – GRÁFICOS ........................................................................................................................................................... 68 Gráficos ............................................................................................................................................................................... 68


Sumário

6

CURSO WINDOWS OFFICE Sumário Gráfico Exemplo: ................................................................................................................................................................. 68 AULA 56 – EXERCÍCIOS COM GRÁFICOS ................................................................................................................................72 Atividades – Exercícios com Gráficos................................................................................................................................... 72 AULA 57 – GERENCIAMENTO DE DADOS ..............................................................................................................................74 Gerenciando Dados ............................................................................................................................................................. 74 Classificação ........................................................................................................................................................................ 74 Filtragem ............................................................................................................................................................................. 75 AULA 58 – FUNÇÕES DATA E HORA ......................................................................................................................................78 Funções com Datas e Horas ................................................................................................................................................ 78 Função Hoje() ...................................................................................................................................................................... 78 Função Hora(número) ......................................................................................................................................................... 78 Função Agora() .................................................................................................................................................................... 78 Função DIA.DA.SEMANA() ................................................................................................................................................... 79 Função Dia(Data) ................................................................................................................................................................ 79 Função Mês(Data) ............................................................................................................................................................... 80 Função Ano(Data) ............................................................................................................................................................... 80 Função DATA.VALOR("dd/mm/aaaa") ................................................................................................................................ 80 Função Hora(hh:mm:ss) ...................................................................................................................................................... 81 Função Minuto(hh:mm:ss) .................................................................................................................................................. 81 Função Segundo(hh:mm:ss) ................................................................................................................................................ 81 AULA 59 – VALIDAÇÃO DE DADOS ........................................................................................................................................83 AULA 60 – CONGELAMENTO DE PAINÉIS ..............................................................................................................................87 CONGELAMENTO DE PAINÉIS ............................................................................................................... ERRO! INDICADOR NÃO DEFINIDO. Divisão de janela ................................................................................................................................................................. 87 AULA 61 – PRATICANDO .......................................................................................................................................................91 Função SOMASE – Planilha Relatório de Vendas ................................................................................................................ 91 FUNÇÃO SE - CALCULANDO O SALÁRIO DE CADA VENDEDOR .................................................................................... ........ 95 FUNÇÃO SOMASE - TOTAL DE VENDAS POR REGIÃO: ................................................................................................. ........ 95 GRÁFICO TOTAL DE VENDAS POR PRODUTO ...................................................................................................................... 95 PROCV PARA ENCONTRAR O PRODUTO EM PREÇOS .......................................................................................................... 95 AULA 62 – ATIVIDADES .........................................................................................................................................................96 Exercícios Avaliação Continua: ............................................................................................................................................ 96 AULA 63 – TRABALHO FINAL............................................................................................................................................... 100 Planilha Resultado Escolar ................................................................................................................................................ 100 AULA 64 – AVALIAÇÃO EXCEL ............................................................................................................................................. 102 Avaliação Excel: PROVA ........................................................... ................................................................. ......................... 102

7


Sumário

CURSO WINDOWS OFFICE Aula 49: Introdução Excel

AULA

49

O Excel O Excel faz parte do pacote Office da Microsoft e atualmente é o software de planilha eletrônica mais popular do mercado. As planilhas eletrônicas automatizam as tarefas que envolvem cálculos e segundo as pesquisas, é o aplicativo mais utilizado nos escritórios do mundo inteiro. Com o Excel você faz os mais variados cálculos com rapidez. Você pode fazer desde um controle de despesas domésticas até sofisticados cálculos de engenharia. Faz desde um gráfico para trabalho escolar até complexas análises econômicas.

Iniciando o programa O Excel pode ser iniciado como qualquer outro programa do ambiente Windows. A maneira mais comum é usar o ícone criado no menu Programas. Faça assim: 1. 2. 3. 4.

Clique no botão Iniciar da Barra de Tarefas. Aponte o menu Programas. Aponte para Microsoft Office. Clique no ícone Microsoft Excel.

A janela do Excel Os itens da janela do Excel são: a barra de título, a barra de menus, a barra de ferramentas padrão, a barra de formatação, a barra de fórmulas, a barra de status, a área da planilha, barras de rolagem e guias de planilhas.

Barra de ferramentas padrão: Contém botões para os comandos mais usados do Excel. Barra de formatação: Contém botões para os comandos de formatação de planilhas mais usados. Barra de fórmulas: Na barra de fórmulas é mostrado o conteúdo da célula selecionada tal como ele foi digitado, que em muitos casos é diferente do que é exibido na planilha. Área da planilha: Exibe a planilha ativa. Apostila Microsoft Excel

Aula 49: Introdução Excel

8


AULA

49

Guias de planilhas: Ficam na parte de baixo da área de planilha. Há uma guia para cada planilha da pasta de trabalho aberta. Barras de rolagem: Permitem movimentar a área da planilha para visualizar células que estejam fora do campo de visão. Barra de status: Exibe informações sobre a operação do programa.

Note: os botões minimizar , maximizar e fechar aparece duas vezes na Área de Trabalho do Excel. Os botões que ficam na barra de título agem sobre o Excel. Os botões que ficam na barra de menus ou na área de planilhas agem sobre a pasta de trabalho ativa.

Pastas de trabalho, planilhas e células. Pastas de trabalho. O Excel cria arquivos com extensão xls, chamados pastas de trabalho. Estes arquivos são os documentos básicos do Excel e contém um conjunto de planilhas. Não confunda com as pastas do Windows. Planilhas. As planilhas ficam contidas nas pastas de trabalho. A planilha se apresenta ao usuário como uma tabela formada por várias linhas e colunas. As colunas são identificadas por letras e as linhas são identificadas por números. O cruzamento das linhas com as colunas produz as células. Células. A célula é o elemento básico da planilha. A célula armazena dados de vários tipos, tais como: texto, valores fórmulas e funções. A célula é identificada pela letra da coluna e pelo número da linha que pertence. Por exemplo, a célula C5 é a intersecção da coluna C com a linha 5. Você também pode inserir planilhas na pasta de trabalho. Clique no menu Inserir, comando Planilha. Exemplo: Plan1, Plan2, Plan3... . Para renomear uma planilha dê um duplo clique sobre o seu nome atual e digite seu novo nome ou clique com o botão direito do mouse, e selecione a opção Renomear.

Comandos de Movimentação do Cursor O cursor dentro de uma folha de planilha poderá ser movimentado de dois modos: Com o uso do mouse: limitando-se mais à tela em que se encontra, basta clicar numa determinada célula para já estar selecionada; Uso das setas de movimentação de teclado: você encontrará maior eficiência do que no mouse, pois evita que se avance demasiadamente além dos limites da tela. Tecla Combinação    

+  +  +  +  + + +

9


Ação de Movimento Posiciona o cursor uma célula à direita Posiciona o cursor uma célula à esquerda Posiciona o cursor uma célula acima Posiciona o cursor uma célula abaixo Posiciona o cursor na última célula à direita Posiciona o cursor na última célula à esquerda Posiciona o cursor na última célula acima Posiciona o cursor na última célula abaixo Posiciona o cursor uma célula A1 Posiciona o cursor Alça de Planilha posterior Posiciona o cursor Alça de Planilha anterior


CURSO WINDOWS OFFICE

AULA


49

Comandos de Edição Os comandos de edição estão disponíveis quando se aperta a Tecla de Função , sobre alguma fórmula, valor ou texto, os quais são descritos em seguida: Tecla / Combinação  

+  + 

Ação de Movimento do Cursor Move um caractere à direita Move um caractere à esquerda Posiciona-o no inicio da linha Posiciona-o no fim da linha Apaga caractere à esquerda Apaga caractere sobre o cursor Cancela edição, volta a Planilha Move o cursor à palavra ou ao argumento posterior Move o cursor à palavra ou ao argumento anterior

Como Entrar com Dados na Planilha Há basicamente três diferentes formas de introduzir dados em uma Planilha: 1. Digitar o conteúdo diretamente na célula; 2. Copiar conteúdo de uma célula para outra; 3. Copiar conteúdo de um arquivo para uma célula; Uma célula pode conter títulos (textos), fórmulas ou valores, identificados da seguinte maneira:

O que são Títulos As informações armazenadas nesta modalidade devem ser introduzidas com letras, para que o Excel perceba que não se trata e valores. Qualquer texto digitado deverá ser considerado como Título ou Rótulo. Números para serem usados como títulos deverão ser precedidos do caractere apóstrofo. Valor Base

Cálculo 1,5 Ton

12 litros Projeto

Média Horas

Maior Mês

Pico Período

O que são Valores As informações armazenadas nesta modalidade devem ser introduzidas com algarismos numéricos (0 a 9). 1 2 56678 121 121.1 121,54 -4 -5 -8 -10 -12,5 -21 Quando você for iniciar um cálculo, deve começar com sinal de Igual (=), para depois seguir os números.

O que são Fórmulas Toda fórmula, por mais simples que seja, deve ser iniciada com o sinal de Igualdade (=). As informações armazenadas nesta modalidade utilizam os seguintes operadores aritméticos:

=2+2 =2^3

=2*2 =3*2/4

=2/2 =(3+2)/4

=-2+2 =4+2/4

=3*2+(3-2) =(-3+2)/4

=2-2 =3+(3^2)

Os níveis de prioridade de Cálculo são os seguintes:



10


AULA


49

Prioridade 1: Exponenciação e Radiciação (vice-versa)  Prioridade 2: Multiplicação e Divisão (vice-versa)  Prioridade 3: Adição e Subtração (vice-versa) OBS: Quando se quiser achar a raiz quadrada de 9, por exemplo, basta usar a formula: =9^(1/2). Os cálculos são executados de acordo com a prioridade matemática, conforme a sequencia anteriormente mostrada, podendo ser 

utilizados parênteses ―( )‖ para definir uma nova priori dade de cálculo.

Exemplo: 3 * 5 + 2 = 17 é diferente de 3 * (5 + 2) = 21 Para efetuar a introdução de títulos, valores e fórmulas em uma Planilha, devem ser observados os seguintes passos: 4. Posicione o cursor na célula desejada. 5. Digite os dados 6. Tecle ou qualquer uma das setas de movimentação do cursor para dar a entrada dos dados.

Exercício de Fixação 7. Execute os cálculos abaixo:

Adição

Subtração

Multiplicação

Divisão

Exponenciação e Radiciação

2+4+3+4+1+7

7-9

3*5

3/1

3^2

4+5+8+7+12

3-5-3

45*68

5/2

3 ^ (1/2)

35+3+4

-3-6-8

3*7

-5/3

-3 ^ (-2)

-2+5+4

-6-8-7

-45*8

-4/-2

3 ^ (-2)

-99+9

-8-8

-7*-6

-4/3

30 ^ (1/3)

8. Calcule as fórmulas seguintes e compare os resultados:

=3 * (4 – 5) =3 – (2 – 1 + 5) =3 * (4 – 5) + (4 * (3 – (2 – 1 + 5)) – 5) / 2^2 =(4 – 5) + (4 * (3 – (2 – 1 + 5)) – 5) =(4 * (3 – (2 – 1 + 5)) – 5) 9.

Tenho R$ 18,00 para pagar minha excursão. Se a excursão custa R$ 45,00, preciso de mais quanto dinheiro? 11




AULA

49

Obtenha o resultado das operações matemáticas abaixo:  5 + 2 + (8 + 2)=  (5 + 2) + 8 + 2=  (5 + 2) + (8 + 2)=  8 - (7 – 5)=  34 – 18 – (13 – 3) =  34 – (18 – 13 – 3) = 11. Coloque parênteses, se necessário, nos locais adequados para que a operação seja verdadeira.  15 + 3 + 8 + 10 = 36  15 – 3 + 8 + 10 = 30  15 – 3 + 8 + 10 =24  15 + 3 – 8 + 10 = 20  15 + 3 – 8 + 10 = 0 10.



12

CURSO WINDOWS OFFICE Aula 50: A Primeira Planilha

AULA

50

Planilha de Orçamento Doméstico A Planilha que será criada mostrará um assunto bastante comum, o Orçamento Doméstico de uma família composta pelo casal e por dois filhos como idade de 12 e 14 anos. Procure digitar os dados nas referencias de células mostradas porque a partir delas criaremos corretamente as fórmulas:

Uma vez os valores inseridos em suas respectivas células o objetivo será calcular o total de rendimentos e o total de despesas. As células com o título ―Total‖. Antes porem de criar as formulas vamos faze r alguns ajustes em nossa planilha para melhorar a visualização.

Formatar a Planilha ―Formatar‖ significa melhorar a estética de sua Planilha, como alterar a largura das colunas, efetuar um destaque

nas letras, alterar o tipo, estilo, cores, etc. ou ainda mudar o alinhamento de títulos e formatar os números com casas decimais e separação de milhares.

Largura da Coluna Você deve ter percebido que a coluna A não é larga o suficiente para caber as informações. Neste caso devemos aumentar a largura da coluna. 1. Posicione-se em qualquer célula pertencente à coluna que deverá ser alterada, no caso a coluna A; 2. Clique no menu Formatar – Coluna – Largura; 3. Este comando mostrará a Caixa de Diálogo Largura da coluna: Altere para a largura 30 e clique sobre o botão OK. Pronto agora todas as informações cabem na coluna.

O Procedimento de Cálculo A vantagem de trabalhar com uma planilha eletrônica é que para realizar os cálculos não há a necessidade de repetir os valores já digitados, procure apenas indicar o local em que eles se encontram, 13


Aula 50: A Primeira Planilha


AULA

50

criando assim uma referência. Observe que para calcular o ―Total‖ que está na célula B6: referente aos ―Rendimentos:‖ basta efetuar a seguinte fórmula:

=B4+B3 em vez de =6200+670

Uso da Função = SOMA O Excel traz muitas funções divididas em diversas categorias, entre as funções mais conhecidas e usadas há a função =SOMA(faixa), que permite efetuar a soma dos valores contidos em uma faixa de células. No caso do ―Total‖ que está na célula B21: referente às ―Despesas‖ pode -se até mesmo proceder da mesma forma,

porém a fórmula ficará muito grande. Observe:

=b9+b10+b11+b12+b13+b14+b15+b16+b17+b18+b19+b20 Podemos utilizar a função soma:

=soma(b9:b20) A fórmula acima ira somar todos os valores no intervalo de células de b9 até b20. Nas funções do Excel o sinal : (dois pontos) significa até. Conclua a planilha Orçamento Domestico calculando o Saldo e compare com a planilha abaixo:

Formatar Valores Numéricos. Formatar os números é muito importante porque eles darão uma idéia precisa do que significam na Planilha. Se for valor monetário, pode-se optar por mostrar o símbolo da moeda ou simplesmente separar os milhares e as casas decimais (duas casas). Para você mudar o formato dos números, basta executar o seguinte comando: 1. Selecione as células a serem formatadas (use o mouse ou o teclado para selecionar). No nosso caso precisará selecionar a faixa B4:B21 (da célula B4 até a célula B21); 2. Clique no menu Formatar – Células – Número; 3. Clique na categoria Número; 4. Ajuste Casas Decimais para 2; Apostila Microsoft Excel


14


5. Usar separador de 1000(.) 6. Escolha a opção que mostra números negativos em vermelho. 7. Clique em Ok e observe os resultados em sua planilha.

Formatar Células - Alinhamento Podemos melhorar o posicionamento dos títulos (rótulos) da seguinte forma: 1. 2. 3. 4. 5. 6. 7. 8.

Selecione a célula que será alinhada: A3; Clique em Formatar – Células - Alinhamento; Ajuste a opção Horizontal para Centro; Repita este mesmo comando para as células: A8 e A21; Selecione as células A1:B1; Clique em Formatar – Células – Alinhamento; Ajuste a opção Horizontal para Centralizar seleção; Dê um clique sobre o botão OK da Caixa de Diálogo ―Formatar células‖.

Formatar Células - Fonte Podemos melhorar a estética de nossas planilhas formatando a fonte, tamanho, cor, etc. 1. Posicione o cursor na célula A1; 2. Clique em Formatar – Célula – Fonte; 3. Escolha a fonte, o tamanho e a cor.

15



AULA

50


AULA

50

Atividades: 1. Complete a formatação da planilha Orçamento Domestico como mostra o exemplo acima. 2. Altere o nome da planilha para Orçamento. 3. Salve-a na pasta meus documentos com o nome de OrçamentoDomestico .



16

CURSO WINDOWS OFFICE Aula 51: Copiar e Colar, Mover,

AULA

51

Abrindo uma pasta de trabalho Para abrir pastas de trabalho salva faça assim: 1. No menu Arquivo escolha Abrir... Surgirá a caixa Abrir. 2. No campo Examinar selecione a pasta que contém o documento desejado. 3. No campo Nome do arquivo digite o nome da pasta de trabalho ou clique no nome exibido na lista. 4. Finalize clicando em Abrir.

Seleção Antes de executar um comando no Excel precisamos definir quais células serão atingidas pelos efeitos do comando. Uma célula selecionada fica realçada para diferenciar-se das outras células. Há várias maneiras de selecionar no Excel. Para selecionar:     





Uma célula: Clique célula: Clique na célula ou digite o nome da célula na barra de fórmulas. Uma linha: Clique linha: Clique no número da linha localizado no lado esquerdo da área de trabalho do Excel. Uma coluna: Clique coluna: Clique na letra da coluna, localizada no lado superior da área de trabalho do Excel. Um grupo contínuo de células: se células: se as células estão lado a lado, clique na primeira da seqüência e depois segure apertada a tecla SHIFT enquanto clica na última célula do grupo. Um grupo contínuo de linhas: Clique linhas: Clique no número da primeira linha e depois segure apertada a tecla SHIFT enquanto clica no número da última linha do grupo. Os números das linhas ficam no lado esquerdo da área de trabalho do Excel. Um grupo contínuo de colunas: Clique colunas: Clique na letra da primeira coluna e depois segure apertada a tecla SHIFT enquanto clica na letra da última coluna do grupo As letras das colunas ficam no lado superior da área de trabalho do Excel. Um grupo qualquer de células, linhas ou colunas: Se as células não estão lado a lado clique na primeira célula e em seguida mantenha a tecla CTRL apertada enquanto clica nas outras células, linhas ou colunas do grupo.

Recorte, cópia e colagem Você usa as operações de recorte, cópia e colagem no Excel de modo semelhante ao usado em outros aplicativos. Para mover o conteúdo de uma célula para outra posição use os comandos Recortar quer copiar um conteúdo para outra posição use Copiar e Colar.

e Colar

. Se você

Para mover dados faça assim: 1. 2. 3. 4.

Selecione as células que contém os dados. No menu Editar escolha Recortar. As células selecionadas ficam realçadas. Selecione as células que receberão os dados recortados. No menu Editar escolha Colar.

Para copiar dados faça assim: 1. 2. 3. 4. 5.

Selecione as células que contém os dados. No menu Editar escolha Copiar. As células selecionadas ficam realçadas. Selecione as células que receberão os dados copiados. No menu Editar escolha Colar. Se você quiser fazer mais cópias selecione outras células para receber os dados copiados e use o comando Colar. 17


Aula 51: Copiar e Colar, Mover,


AULA

51

6. Para encerrar o processo tecle ESC.

Movendo dados por arraste Outra forma de mover dados de uma célula para outra é usar o arraste de mouse. Faça assim: 1. Selecione as células cujos dados serão movidos. 2. Aponte sobre as células selecionadas de modo que o ponteiro do mouse fique na forma de seta padrão. 3. Arraste o conteúdo das células até o local de destino e solte o botão do mouse.

Inserindo linhas e colunas Imagine que você digitou um volume grande de dados e só então percebeu que esqueceu de digitar uma linha no meio da planilha. Não se preocupe. Você pode inserir uma linha nova na planilha a qualquer momento fazendo assim: 4. Selecione a linha localizada abaixo de onde você quer inserir uma linha nova. 5. No menu Inserir escolha Linhas. Para inserir colunas adicionais o procedimento é semelhante. A coluna nova entra a direita da coluna selecionada.

Atividades Fórmulas Vamos conhecer as quatro operações matemáticas no Excel. Em cada planilha crie mais 5 exemplos além do proposto. Salve todas as planilhas em sua pasta Documentos.

FÓRMULA SOMA Exemplo: =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. Veja o outro exemplo:

Neste exemplo estamos somando todos os valores do endereço A1 até o endereço D1. Digite a fórmula como no exemplo e observe que ao teclar ENTER ENTER o resultado será mostrado. No caso a resposta seria 60.


Aula 51: Aula 51: Copiar e Colar, Mover,

18


AULA

51

Botão Autosoma Outra maneira de somar é utilizando 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.

Somar Matrizes: Podemos somar todos os valores dispostos em uma faixa de células usando uma única fórmula, onde devemos indicar a célula inicial e a célula final.

Exemplo:

Somar Células Alternadas Para somar números dispostos de maneira alternada, ou seja, em endereços diferentes utilizamos o sinal de entre cada endereço de célula na formula. Exemplo:

Neste exemplo desejamos somar somente os valores de água, então, basta digitar o endereço de cada valor, ou seja, o endereço do primeiro valor + o endereço do segundo valor e assim sucessivamente. Lembre-se que sempre devemos iniciar o cálculo usando o sinal de igualdade.

19



+


AULA

51

FÓRMULA SUBTRAÇÃO No exemplo abaixo você deseja saber qual o saldo líquido do Alan. Então é simples: Basta que você digite o endereço do SALÁRIO BRUTO MENOS o endereço do DESCONTO. De maneira mais clara quero dizer que para realizar uma subtração no Excel, você só precisa digitar o endereço dos devidos valor (inicial e final) acompanhado do sinal de subtração (-), como mostrar no exemplo abaixo. Para os demais funcionários você só precisa copiar a fórmula.

FÓRMULA MULTIPLICAÇÃO Agora a maneira como você subtraiu é a mesma para multiplicar, será preciso apenas trocar o sinal de subtração pelo o sinal de multiplicação *. Veja o exemplo.

FÓRMULA DIVISÃO Observe o sinal de divisão / .



20

CURSO WINDOWS OFFICE Aula 52: Formatação, =MEDIA

AULA

52

Barra de Formatação Nesta aula utilizaremos os recursos da barra de formatação. Veja as principais ferramentas para a formatação de células que compõem esta barra:

Planilha Rendimento Escolar Vamos fazer uma planilha que controla o rendimento escolar de um aluno. Iremos inserir as notas bimestrais de cada matéria e calcular a média final de cada uma delas. Inicie o Excel com uma pasta de trabalho nova e inicie o nosso trabalho digitando como no exemplo abaixo:

Copiar Células Ao digitar Bimestre 1 podemos copias para as demais células sem a necessidade de digitar novamente Bimestre 2, Bimestre 3 e Bimestre 4. Selecione a célula B3 que contém Bimestre 1 e observe um quadradinho no canto inferior direito da célula. Este quadradinho é chamado de Alça de Preenchimento e é utilizado para copiar o conteúdo de uma célula para outras células adjacentes. Posicionando o cursor na alça de preenchimento observe que ele muda de formato para uma cruz:

21


Aula 52: Formatação, =MEDIA


AULA

52

Segure o botão esquerdo do mouse pressionado e arraste até a célula E3, solte-o nesta célula. Observe que o Excel copia automaticamente o conteúdo da célula B3 para as células C3, D3 e E3 em modo sequencial, ou seja, no nosso exemplo: Bimestre 1,2,3 e 4.

Agora vamos continuar a digitar nossa planilha:

Formatar Casas Decimais Ao digitar as notas você já observou que muitas possuem uma casa decimal. Por exemplo 7,5. Vamos então formatar as células das notas para este formato. Selecione as células B4 a E8: Clique no botão Aumentar Casas Decimais na Barra de Formatação. Todas as notas serão mostradas no novo formato.

Mesclar Células Quando você mescla duas ou mais células horizontais ou verticais adjacentes, as células se tornam uma grande célula que é exibida em várias colunas e linhas. O conteúdo de uma das células aparece no centro da célula mesclada. Observe em nosso exemplo vamos mesclar as células de A1 até F1. Clique no botão Mesclar Células na Barra de Formatação.



22


AULA

52

Mescle também as células de B2 até F2 que serão utilizadas para digitar o nome do aluno.

Alinhamento Para exibir os dados na planilhade forma ideal, talvez seja necessário reposicionar os dados em uma célula. Você pode alterar o alinhamento do conteúdo da célula usando os botões da barra de ferramentas formatação: alinhar a esquerda, centralizar e alinhar a direita. Selecione a célula A2 e clique no botão Alinhar a direita. Selecione também as células de B3 até F3 e clique no botão Centralizar.

Formula Média Aritmética Na coluna de Média utilizaremos fórmulas. A média é calculada somando as notas dos quatro bimestres e dividindo o resultado por Para que o Excel calcule a média anual do aluno em Português digite célula F4 a fórmula:

4. na

=(B4+C4+D4+E4)/4 Ao digitar a formula o Excel calcula e exibe o resultado dá média de Português: Selecione a célula F4, clique e araste a alça de preenchimento células F5, F6, F7 e F8.

23



para copiar a formula da média para as


AULA

52

USO DA FUNÇÃO =Média Média aritmética é calculada por meio da adição de um grupo de números e, em seguida, da divisão pela contagem desses números. Por exemplo, a média de 2, 3, 3, 5, 7 e 10 é 30 dividido por 6, q ue é 5. A função Média calcula a média dos valores em um intervalo de células. Em nosso exemplo, a formula =(B4+C4+D4+E4)/4 pode ser substituída pela função =MEDIA(B4:E4). Substitua a formula da célula F4 pela função soma e copie para as demais células.



24


AULA

52

Atividade: 1. Agora que você já conhece a barra de formatação conclua a formatação de sua planilha conforme o exemplo abaixo:

25



CURSO WINDOWS OFFICE Aula 53: Formatação, Uso de :

AULA

53

Uso de dois pontos e de ponto e vírgula Dois pontos (:) O dois pontos é usado nas fórmulas para definir um intervalo de células. Nós já o usamos na função SOMA e na função MÉDIA. Veja os exemplos:

=SOMA(A3:A10)

=MÉDIA(A3:A10)

A3:A10 pode ser lido como intervalo de células entre A3 e A10, ou seja, estão incluídas no intervalo as células A3, A4, A5, A6, A7, A8, A9 e A10. Ponto e vírgula (;) O ponto é vírgula é usado nas fórmulas para separar dados e intervalos. Vamos exemplificar:

=SOMA(A3:A6;A8:A10) Lemos a fórmula assim: Soma dos valores dos intervalos de A3 até A6 e de A8 até A10. Ou seja, será feita a soma dos valores de A3, A4, A5, A6 e também de A8, A9 e A10.

Formatação de Bordas e Cor de Preenchimento Podemos alterar as bordas das células e suas cores conseguindo assim uma melhor visualização das informações e dados da planilha. Isso facilita a compreensão das informações pelo usuário. Bordas Para formatar a borda de uma célula podemos utilizar o botão Bordas da barra de ferramentas formatação ou a opção bordas da janela formatar células.

Abra a planilha Médias Escolares e aplique as bordas conforme o exemplo abaixo:


Aula 53: Formatação, Uso de :

26


AULA

53

Cor de Preenchimento Para aplicar uma cor de preenchimento selecione uma célula ou um intervalo de células e clique no botão Cor de Preenchimento da barra de ferramentas formatação e escolha a cor desejada.

Para remover a cor da célula, selecione-a e na tabela de cores da ferramenta Cor de Preenchimento selecione Sem preenchimento. Agora aplique as seguintes cores de preenchimento na planilha Médias Escolares:

27




AULA

53

Exercícios 1. Criar a seguinte planilha no Excel. Para calcular o Total Pago multiplique a Quantidade pelo Preço.

2. Digite e formate a planilha abaixo. Calcule os totais de cada mês utilizando a FUNÇÃO SOMA e os totais gasto por depesas no trimestre. Coloque em Azul o total gasto em cada mês. Coloque em Verde o total gasto com cada despesa no trimestre. Coloque em Roxo o total geral do trimestre.

3. A expressão =soma (A1:A4) faz o somatório das células: a) b) c) d) e)

da linha 1 com as células da linha 4. Da coluna 1 com as células da coluna 4. Das linhas 1, 2, 3 e 4. A1 e A4 A1, A2, A3 e A4.

4. A expressão =SOMA (B1;B4) faz o somatório das células: a) b) c) d) e)

da linha 1 com as células da linha 4. Da coluna 1 com as células da coluna 4. Das linhas 1, 2, 3 e 4. B1 e B4. B1, B2, B3 e B4.

5. Elabore as seguintes planilhas: f)

Some o total de despesas de cada mês, lembre-se de que Sálario não é despesa. Calcule o saldo de cada mês:

saldo=salario-total despesas Apostila Microsoft Excel


28


AULA

53

g) Faça uma planilha em Excel que forneça o valor em Fahrenheit de uma temperatura expressa em graus

Celsius: F=(9*C+32)/5

h) Elabore a planilha abaixo utilizando fórmulas:

Fórmula para a célula F4: ________________________ Fórmula para a célula G4: ________________________

29



CURSO WINDOWS OFFICE Aula 54: Operadores, =MINIMO,

54

AULA

Operadores de cálculo e precedência Os operadores especificam o tipo de cálculo que você deseja efetuar nos elementos de uma fórmula. Há uma ordem padrão na qual os cálculos ocorrem, mas você pode alterar essa ordem utilizando parênteses. Já conhecemos alguns dos operadores utilizados no Excel em nossos exercícios anteriores. Nesta aula vamos conhecer todos os Tipos de Operadores e a Ordem em que o Excel efetua as operações em formulas.

Tipos de operadores Há quatro diferentes tipos de operadores de cálculo: aritmético, comparação, concatenação de texto e referência. Operadores aritméticos: PARA efetuar operações matemáticas básicas, como adição, subtração ou multiplicação, combinar números e produzir resultados numéricos, use estes operadores aritméticos. Operador aritmético

Significado

Exemplo

+ (sinal de mais)

Adição

3+3

– (sinal de menos)

Subtração Negação

3 –1 –1

* (asterisco)

Multiplicação

3*3

Divisão

3/3

% (sinal de porcentagem)

Porcentagem

20%

^ (acento circunflexo)

Exponenciação

3^2

/ (sinal de divisão)

Operadores de comparação: Você pode comparar dois valores, usando os operadores a seguir. Quando dois valores são comparados usando esses operadores, o resultado será um valor lógico, VERDADEIRO ou FALSO. Significado

Operador de comparação

Exemplo

= (sinal de igual)

Igual a

A1=B1

> (sinal de maior que)

Maior que

A1>B1

< (sinal de menor que)

Menor que

A1
>= (sinal de maior ou igual a)

Maior ou igual a

A1>B1

<= (sinal de menor ou igual a)

Menor ou igual a

A1
<> (sinal de diferente de)

Diferente de

A1<>B1

Operador de concatenação de texto: Use o 'E' comercial (&) para associar, ou concatenar, uma ou mais seqüências de caracteres de texto para produzir um único texto. Operador de texto & (E comercial)

Significado

Exemplo

Conecta, ou concatena, dois valores para produzir um valor de texto contínuo

("North"&"wind")

Operadores de referência: Combine intervalos de células para cálculos com estes operadores. Operador de referência

Significado

Exemplo

: (dois-pontos)

Operador de intervalo, que produz uma referência para todas as células entre duas referências, incluindo as duas referências

B5:B15

, (vírgula)

Operador de união, que combina diversas referências em uma referência

SOMA(B5:B15,D5:D15)

Operador de interseção, que produz uma referência a células comuns a duas referências

B7:D7 C6:C8

(espaço)


Aula 54: Operadores, =MINIMO,

30


AULA

54

Ordem de cálculo Em alguns casos, a ordem na qual o cálculo é executado pode afetar o valor retornado da fórmula. Então é importante compreender como a ordem é determinada e como você pode alterar a ordem para obter o resultado desejado. As fórmulas calculam valores segundo uma ordem específica. Uma fórmula no Excel sempre começa com um sinal de igual (=). O sinal de igual informa ao Excel que os caracteres a seguir constituem uma fórmula. Depois do sinal de igual estão os elementos a serem calculados (os operandos), que são separados por operadores de cálculo. O Excel calcula a fórmula da esquerda para a direita, de acordo com uma ordem específica para cada operador da fórmula.

Precedência de operadores Se você combinar vários operadores em uma única fórmula, o Excel executará as operações na ordem mostrada na tabela a seguir. Se uma fórmula contiver operadores com a mesma precedência — por exemplo, se uma fórmula contiver um operador de multiplicação e divisão — o Excel avaliará os operadores da esquerda para a direita. Operador

Descrição

: (dois-pontos) (espaço simples) , (vírgula)

Operadores de referência

–

Negação (como em –1)

%

Porcentagem

^

Exponenciação

*e/

Multiplicação e divisão

+ e –

Adição e subtração

&

Conecta duas seqüências de texto (concatenação)

= <> <= >= <>

Comparação

Uso de parênteses Para alterar a ordem da avaliação, coloque entre parênteses a parte da fórmula a ser calculada primeiro. Por exemplo, a fórmula a seguir retorna 11 porque o Excel calcula a multiplicação antes da adição. A fórmula multiplica 2 por 3 e, em seguida, soma 5 ao resultado.

=5+2*3 Por outro lado, se você usar parênteses para alterar a sintaxe, o Excel somará 5 e 2 e, em seguida, multiplicará o resultado por 3 para produzir 21.

=(5+2)*3 No exemplo abaixo, os parênteses na primeira parte da fórmula forçam o Excel a calcular B4+25 primeiro e, em seguida, dividir o resultado pela soma dos valores nas células D5, E5 e F5.

=(B4+25)/SOMA(D5:F5)

USO DA FUNÇÃO =MÁXIMO A função =MÁXIMO retorna o valor máximo de um conjunto de valores. Você pode especificar uma sequência de células e obter o maior valor entre elas. 31




AULA

54

USO DA FUNÇÃO =MÍNIMO A função =MÍNIMO retorna o menor valor de um conjunto de valores. Você pode especificar uma sequência de células e obter o menor valor entre elas.



32


AULA

54

Atividades 1. Elabore uma planilha em Excel utilizando as funções =MAXIMO, =MINIMO e =MEDIA para obter os resultados abaixo:

2. A planilha abaixo é utilizada por uma pequena quitanda para acompanhar a evolução de suas vendas durante o ano. Seria muito importante para o dono da quitanda saber algumas informações de suas vendas, como por exemplo a média mensal de leite que ele vende. Ajude-o criando as formulas necessárias para obter estes valores estatisticos. Veja na página seguinte a planilha ampliada.

3. Para digitar os meses você também pode utilizar o recurso da Alça de Preenchimento. Digite o primeiro mês JAN, selecione a célula, clique na alça de preenchimento e arraste-a para as demais colunas. Serão preenchidos automaticamente os demais meses.

4. 33





AULA


54

34

CURSO WINDOWS OFFICE Aula 55: Porcentagem

AULA

55

FÓRMULAS COM PORCENTAGEM As porcentagens são calculadas usando-se esta equação: valor/total = porcentagem

Onde a porcentagem está no formato decimal. No Excel, você pode facilmente calcular qualquer uma dessas variáveis. Pode também exibir rapidamente o resultado como uma porcentagem clicando em Estilo de Porcentagem na barra de ferramentas Formatação. 1. Um cliente de sua loja fez uma compra no valor de R$ 1.500,00 e você deseja dar a ele um desconto de 5% em cima do valor da compra. Veja a formula:

Onde: B2 - se refere ao endereço do valor da compra * - sinal de multiplicação 5/100 - é o valor do desconto dividido por 100. Ou seja, você está multiplicando o endereço do valor da compra por 5 e depois dividindo por 100, gerando assim o valor do desconto. Podemos também utilizar o exemplo com o sinal de porcentagem: =B2*5% Onde: B2 - se refere ao endereço do valor da compra * - sinal de multiplicação 5% - é o valor do desconto dividido por 100 Depois para o saber o Valor a Pagar, basta subtrair do Valor da Compra o Valor do Desconto. 2. Uma loja oferece 20% de desconto para a compra de um televisor de 29 polegadas que custa R$ 1.200,00. Quanto corresponde (em dinheiro) 20% desse valor?

Nesse caso, você deve utilizar a fórmula valor * desconto /100 ou valor * desconto% Quando estamos multiplicando o valor do desconto por 5 %, por exemplo, o sinal de porcentagem (%) está automaticamente convertendo o número 5 (que é um número inteiro positivo) em um número decimal, na base percentual, ou seja, 5% correspondem a 0,05 décimos de cem.

Exemplos: 25% de 500 = 500*25% 78% de 9888 = 9888*78% . 35


Aula 55: Porcentagem


AULA

55

3. Agora vamos obter o resultado em forma de %. Digamos que a loja nos oferece R$ 200,00 de desconto para um determinado produto que custa R$ 1.200,00. Veja como obter o valor em porcentagem: desconto/valor ou seja a parte dividida pelo todo.

Em nosso exemplo abaixo utilize a formula: =C4/B4 O resultado será 0,166666667. Selecione a célula e clique no botão porcentagem na barra de formatação.

4. Numa sala existem 40 pessoas. Logo, quarenta é o todo da sala ou 40 representa 100% do universo da pesquisa. Dessas 40 pessoas, 32 são mulheres e 8 são homens. Qual o percentual de homens e mulheres nessa sala?

Para saber o resultado basta que você faça o seguinte cálculo:

=32/40 (dividindo a parte pelo todo) =8/40 (dividindo novamente a parte pelo todo) Feito isso, o Excel mostrará o resultado em números decimais, basta que você selecione-os formate para porcentagem. Daí você terá o resultado de 80% para mulheres e 20% para homens. Veja o exemplo no Excel:



36


AULA

55

Atividades 1. Calcule o valor de reajuste de cada veiculo e o valor do veiculo com aumento.

2. Calcule o total a receber de cada funcionário descontando o valor do vale. Ao final informe o total de salario bruto e o total de salario a receber.

3. As lojas Marias o preço de cada produto depende da forma de pagamento. Para pagamentos a vista a loja oferece desconto de 5%. Pagamentos no cartão há acréscimo de 2,5% e pagamentos parcelados em 3X há acrescimo de 4%. Calcule o valor de cada produto da loja.

37




AULA

55

4. Você foi contratado para trabalhar em uma mercearia. Tudo estava indo bem até que o dono da mercearia te entregou uma lista com as porcentagens de reajuste de preços dos produtos, sendo que cada produto tem um reajuste diferenciado. Você não tem tempo a perder pois tem que mudar os preços nas gôndolas dos produtos. Resolva isto utilizando uma planilha no Excel:

5. Cria a seguinte planilha utilizando formulas nas colunas Valor do Imposto, Valor a Pagar e Total.

6. Utilize as funções =MEDIA, =MINIMO, =MAXIMO e =SOMA para calcular as notas abaixo. Observe que uma prova foi cancelada, portanto utilize para separar as provas válidas da que foi cancelada.



38

CURSO WINDOWS OFFICE Aula 56: Cabeçalho e Rodapé,

AULA

56

Definir área de impressão Para imprimir sua planilha você precisa determinar a área de impressão, para que o Excel saiba exatamente o que você pretende imprimir. Vamos fazer um exemplo para conhecer esses recursos. A planilha a seguir é um controle de orçamento domestico. Já vimos uma parecida, esta porem inclui mais meses. 1. Digite a planilha utilizando formulas para calcular o total de rendimentos, o total de despesas, o saldo de cada mês e o saldo anterior a partir do mês de fevereiro que deve ser o saldo do mês anterior. 2. Agora selecione a faixa de células de A1 até E21, que são exatamente as células que queremos imprimir.

3. Clique em Arquivo - Área de Impressão – Definir área de impressão

39


Aula 56: Cabeçalho e Rodapé,


AULA

56

Assim você definiu a área de impressão que é a área selecionada anteriormente de A1 até E21.

Visualizar impressão Agora vamos Visualizar a Impressão, isto é importante para certificarmos que a impressão esta de acordo com o que queremos, corrigi-la se for necessário e evitar o gasto de papel com impressões erradas. Clique em Arquivo – Visualizar impressão.

Margens Em Visualizar impressão podemos fazer ajustes como margem, orientação do papel, dimensionar o tamanho da planilha de forma a obtermos o melhor resultado de aproveitamento do papel. Em nosso exemplo estamos ajustando a margem esquerda e a margem direita para que a coluna do mês de abril caiba na folha.

Cabeçalho e Rodapé Podemos inserir notas um cabeçalho e um rodapé em nosso documento para melhor identificá-lo. Insira o seguinte cabeçalho e rodapé em nosso exemplo: Na tela de Visualizar Impressão clique em Configurar e em Cabeçalho e Rodapé. Você pode escolher algumas opções predefinidas ou personalizar seu cabeçalho e rodapé. Em nosso exemplo clique em personalizar cabeçalho e complete como no exemplo abaixo:


Aula 56: Cabeçalho e Rodapé, Imprimir

40


AULA

56

Faça o mesmo procedimento para personalizar o rodapé inserindo a data na seção da direita. Para inserir a data basta clicar no botão

.

Imprimir Uma vez que o trabalho esta completo e revisado você poderá imprimi-lo clicando no botão imprimir na janela de visualizar impressão ou no menu arquivo – imprimir. Aparecerá a caixa de dialogo imprimir:

41


Aula 56: Cabeçalho e Rodapé,


AULA

56

Atividades 1. Agora que você já domina vários recursos do Excel elabora a planilha abaixo, salve-a em Meus Documentos e imprima. Esta é uma planilha que você já pode aplicar em sua casa ajudando sua família no planejamento financeiro. Não se esqueça de configurar cabeçalho e rodapé para identificar seu trabalho na impressão.

2. Formulas utilizada na planilha Planejamento Familiar: a) b) c) d)

Total de Despesas: Soma das despesas (saídas) do mês. Total de Receitas: Soma das receitas (entradas) do mês. Resultado do mês: Total das receitas menos o total das despesas. Saldo acumulado: Saldo do mês anterior mais resultado do mês menos investimento do mês.

3. Formatação: a) b) c) d) e)

Formate os valores numéricos para o formato Moeda, símbolo de Real e números negativos em vermelho. Selecione as células e cliquem em FORMATAR – CELULAS – NUMEROS Estamos utilizando a fonte Arial tamanho 11 nos textos da planilha e a fonte Calibri tamanho 10 nos valores numéricos. Utilize a alça de preenchimento para completar a linha dos meses e também para copiar as formulas do mês de janeiro para os demais meses. Complete todos os meses do ano. Altere a orientação da página para paisagem, assim sua planilha cabera interia em uma folha.


Aula 56: Cabeçalho e Rodapé, Imprimir

42


AULA

Aula 57: Referencia Relativa e

60

Referencia de Células Você já aprendeu que para identificar uma célula usamos uma referencia que é a coluna e a linha a qual e célula pertencem. Para criar essas referencias o Excel utiliza colunas com letras (A até XFD, para um total de 16384 colunas) e linhas com números (1 até 1048576). Veja alguns exemplos de referencia: Para se referir a

Use

A célula na coluna A e linha 10

A10

O intervalo de células na coluna A e linhas 10 a 20

A10:A20

O intervalo de células na linha 15 e colunas B até E

B15:E15

Todas as células na linha 5

5:5

Todas as células nas linhas 5 a 10

05:10:00

Todas as células na coluna H

H:H

Todas as células nas colunas H a J

H:J

O intervalo de células nas colunas A a E e linhas 10 a 20

A10:E20

Referência relativa Ao utilizar referências de células em uma formula você observa que ao copiar ou mover a formula a referencia será alterada, ajustando a formula. Esta referencia é denominada referencia relativa. Por exemplo, na planilha abaixo copiamos a formula da célula D4 para as células D5 e D6, a formula foi ajustada.

Referência absoluta Em algumas situações não queremos que uma referencia seja automaticamente ajustada. Precisamos que alguma referencia na formula permanece sempre a mesma. Esta referencia é denominada referencia absoluta e para especificá-la devemos acrescentar o símbolo $ antes da linha ou da coluna ou de ambas que queremos que se torne absoluta. Veja o exemplo de um caso onde devemos utilizar uma referencia absoluta:

43



CURSO WINDOWS OFFICE Aula 57: Referencia Relativa e

AULA

60

Neste exemplo a taxa de margem utilizada para calcular o preço de venda é o mesmo para todos os produtos e esta definida na célula D2. A fórmula em C5 para calcular o valor da margem de lucro é =B5*D2. Porém se copiarmos esta formula para as demais células a ela será automaticamente alterada para =B6*D3, =B7*D4 e assim por diante. Observe então que a referencia D2 não pode ser alterada. Devemos então tornar esta referencia absoluta, assim: =b5*D$2

Utilizando Datas em planilhas do Excel Podemos realizar várias operações no Excel utilizando datas, como por exemplo, saber o numero de dias entre duas datas. Porém para que o Excel possa realizar cálculos com datas há uma diferença do que elas significam para nos do que elas significam para o Excel. As datas são armazenadas no Excel como números de série começando com 1, que representa 1 º de janeiro de 1900, que é quando o calendário do Excel começa. Cada dia depois desse acrescenta um número à sequência. Por exemplo, a data 2 de janeiro de 1900 é armazenada como 2. Isso significa que, se você digitar 22/08/2010, o Excel armazenará a data como 40412 — ou 40411 dias a partir de 1° de janeiro de 1900. Armazenar datas como números de série permite que o Excel realize a aritmética de datas. Para encontrar o número de dias entre duas datas, por exemplo, o Excel subtrai um número de série do outro. Observação: Não é possível usar datas anteriores a 1 º de janeiro de 1900 em fórmulas do Excel.

Formatando Datas Mesmo que você digite 22/08/2010, 22-ago-2010 ou 22 de agosto de 2010, essa data é armazenada como o número de série 40412. Às vezes, o Excel poderá surpreender você trocando o formato de data digitado pelo seu formato padrão. Mas, independentemente do formato, o número de série da data é 40412. Haverá uma exceção se você usar um formato não reconhecido como data pelo Excel. Por exemplo, se tiver digitado agosto-22, 2010 ou 8,22,10, o Excel poderá armazenar essas informações como texto sem formatação e não como um número de série. Para alterar o formato abra a caixa de diálogo Formatar Células, mostrada na figura, clicando com o botão direito em uma célula contendo uma data e escolhendo Formatar Células.



44


AULA

60

Calcular o numero de dias entre duas datas Agora que você já sabe como o Excel enxerga uma data fica mais fácil entender como ele faz cálculos com datas. Para saber o numero de dias entre duas datas basta realizar uma simples subtração da data final com a data inicial.

Função =DIATRABALHOTOTAL Não é possível encontrar o número de dias úteis (de segunda à sexta) entre duas datas apenas subtraindo uma data da outra. Esse procedimento requer uma fórmula pré-escrita, chamada de função DIATRABALHOTOTAL, que calcula o número corrente de dias úteis entre duas datas.

Exemplo: Hoje é 9 de junho de 2010, e suas férias começam em 21 de agosto de 2010. Você deseja descobrir quantos dias úteis faltam para as férias. Para isso, digite a fórmula:

=DIATRABALHOTOTAL(A2;A3) Há 53 dias úteis até as férias. Os valores nas células A2 e A3 são os argumentos, informações que indicam à função DIATRABALHOTOTAL o que deve ser calculado. Parênteses separam a função dos argumentos. Dentro dos parênteses, uma vírgula separa os argumentos entre si.

Função =HOJE() A função HOJE é incomum. Trata-se de uma das poucas funções do Excel que não requer nenhum argumento, embora ela precise dos parênteses de abertura e fechamento: =HOJE() HOJE insere a data atual, que é atualizada sempre que uma pasta de trabalho é aberta ou recalculada.

Exemplo: Utilize a função HOJE para descobrir quantos dias de idade você tem hoje.

Em E3 utilize a função =hoje() Em E4 formate a célula com a categoria data e digite sua data de nascimento. Em C5 subtraia a data de hoje da data de seu nascimento e descubra quantos adias você tem de vida:

=E3-E4 45




AULA

60

Exercício 1. Calcule o valor total em reais e o valor total em dólar deste pedido da papelaria Papel Branco.

2. Elabore a planilha abaixo inserindo as formulas necessárias para calcular DIAS EM ATRASO e DIAS UTEIS EM ATRASO. Observe a formula da célula F9 em destaque na barra de formulas como exemplo.



46

CURSO WINDOWS OFFICE Aula 58: Função =SE

AULA

60

Lógica de Programação Em muitas situações somos obrigados a tomar decisões. Se estiver sol vamos à praia, senão vamos ao cinema. Se for aprovado no exame vou estudar engenharia, se não for vou fazer novamente o curso preparatório para o vestibular. Veja a representação gráfica abaixo mostrando como tomar decisões em uma situação eminente: A luminária não ascende. Será que a lâmpada esta enroscada? Estará queimada? O que fazer?

Atividades: 1) O que devemos verificar em primeiro lugar se a lampada não ascender?

________________________________________________________________________ 2) Se a lampada estiver queimada devemos trocar a luminária?

________________________________________________________________________ 3) Se a lampada estiver enroscada devemos trocar a lampada?

________________________________________________________________________ 4) Em que situação devemos trocar a luminária?

________________________________________________________________________

FUNÇÃO SE Quando estamos programando uma planilha eletrônica também nos deparamos com decisões a tomar. Por exemplo, ao calcular a média de um aluno temos que informar se o aluno foi APROVADO ou REPROVADO. Veja um fluxograma para esta situação:

47


Aula 58: Função =SE


AULA

60

Operações Lógicas são utilizadas quando se torna necessário tomar decisões. Toda decisão terá sempre como resposta o resultado VERDADEIRO ou FALSO. No Excel, para utilizar uma operação lógica e tomar uma decisão utilizamos a função SE.

A função SE retorna um valor se uma condição que você especificou avaliar como VERDADEIRO e outro valor se for avaliado como FALSO. Use SE para efetuar testes condicionais com valores e fórmulas. Apostila Microsoft Excel


48


AULA

60

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 de cálculo de comparação. Valor_se_verdadeiro é o valor retornado se teste-lógico for VERDADEIRO. Por exemplo, se esse argumento for a seqüência de caracteres de texto "Dentro do orçamento" e o argumento teste-lógico for considerado VERDADEIRO, a função SE exibirá o texto "Dentro do orçamento". Se teste-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 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 valor-se-falso for omitido (ou seja, se não houver vírgula após valor-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 uma vírgula após valor-se-verdadeiro seguida do parênteses de fechamento), o valor 0 (zero) será retornado. Valor-se-falso pode ser outra fórmula.

Exemplo:

49




AULA

60

Atividades: 1. Complete a planilha para informar o resultado na classificação de cada clube.

2. Estamos criando uma planilha para informar a classificação dos alunos numa olimpiada de matematica. Complete com as seguintes formulas: f) Use a função SOMA para calcular o total de pontos g) Use uma fórmula simples para calcular o Bônus conseguido pelo aluno h) Use a função SE para mostrar o resultado ―CLASSIFICADO‖ OU ―ELIMINADO‖.

3. Verificar o desconto consedido pelo fornecedor de peças e informar se foi um ―Bom Desconto‖ ou um ―Mal Desconto‖. Para calcular o valor do desconto subtraia o Preço Promoção do Preço.



50

CURSO WINDOWS OFFICE Aula 59: Atividades Função =SE

AULA

60

Utilizando a Função SE A função Se é uma das mais importantes funções do Excel. A capacidade do computador de tomar decisões baseadas em uma expressão lógica é fundamental para a automação de muitas rotinas no dia a dia de uma empresa. Domine esta função para ter sucesso programando planilhas em Excel.

Vamos aprender outras formar de utilizar a função Se: 1. Vamos verificar o estoque de materiais, informando quando o estoque estiver no nivel Crítico:

Observe as fórmulas utilizadas no exemplo acima e monte sua planilha:

2. Calcular a diferença entre dois numeros sendo que o resultado nunca poderá apresentar um resultado negativo.

51


Aula 59: Atividades Função =SE


AULA

60

3. Verificar se o sexo informado é ―F‖ (Feminino) e se a idade é maior que 18 anos; se o sexo for ―M‖ (Masculino) exibir somente a mensagem Sexo Masculino.

Observe as formulas:



52


AULA

60

TRABALHANDO COM DIVERSAS PLANILHAS O Excel permite que você trabalhe com várias planilhas em uma mesma pasta de trabalho. Assim você pode, por exemplo, criar em um mesmo arquivo (pasta de trabalho do Excel) uma planilha de compras, outra de vendas, estoque, preços, etc. Para tanto na parte inferior da tela do Excel existem guias (Plan1, Plan2, Plan3...) cada qual representando uma planilha do arquivo atual. Para começar uma nova planilha em seu arquivo clique em uma das Plans e crie-a. A parte mais interessante é que você pode relacioná-las, isto é, trabalhar com valores de varias planilhas diferentes, bastando para isso em suas formulas indicar a planilha a que a célula pertence. Exemplo:

=Plan2!G8 + Plan3!D15 No exemplo acima a célula retornaria o resultado da soma da célula G8 da planilha Plan2 com a célula D15 da planilha Plan3.

ATIVIDADES 1. Crie as seguintes planilhas na mesma pasta de trabalho: a)

Planilha Plan1 – Renomeia para Preços:

Fórmula Utilizada:

D4=C4+(C4*D$2)

b) Planilha Plan2 – Renomeia para Vendas:

Fórmulas Utilizadas:

A3=Preços!A4 B3=Preços!B4 D3=Preços!C4*Compras!C3

c)

53

Planilha Plan3 – Renomeia para Compras:




AULA

60


A3=Preços!A4 B3=Preços!B4 D3=Preços!D4*Vendas!C3

d) Planilha Plan4 – Renomeia para Estoque:

2. Formate cada planilha conforme os exemplos utilizando bordas, sombreamento, cores e estilo moeda. 3. Salve a pasta de trabalho em seus Documentos com o nome de Quitanda. 4. Qual o lucro obtido com as vendas do produto 40Manga com a Margem de Venda em 30%?


A3=Preços!A4 B3=Preços!B4 C3=Compras!C3-Vendas!C3

_____________________________________________________________

D3=Vendas!D3-

5. Insira uma fórmula na planilha Estoque para calcular o lucro total da Quitanda.

(Vendas!C3*Preços!C4)

______________________________________________________________

D3=SE(C3<=15;”Produto em Falta!”;”Estoque em Ordem”)



54

CURSO WINDOWS OFFICE Aula 60: Formatação Condicional

AULA

60

Observe o seguinte fluxograma:

DIGITE AS NOTAS N1,N2,N3 E N4

CALCULE: MEDIA= (N1+N2+N3+N4)/4

S APROVADO

MEDIA>7

N S MEDIA<4

REPROVADO

N EXAME FINAL

É muito comum termos de colocar como resultado final de um cálculo, não somente o valor puro, mas sim uma resposta, composta por uma palavra ou sentença, condicionada ao resultado numérico obtido. A isso chamamos de Respostas Condicionais. Um bom exemplo é o do controle de aprovação de uma classe de alunos. Se um aluno tirar média final maior a 6 ele estará APROVADO, em caso da média ficar entre 4 e 6 deverá ir para EXAME FINAL, enquanto que se tiver média inferior a 4 estará REPROVADO. Vamos à planilha que nos servirá como exemplo, observe na barra de formulas em destaque a fórmula da célula H5:

55


Aula 60: Formatação Condicional


AULA

60

Observe pelo fluxograma que temos duas tomadas de decisões: A primeira verifica se a média é maior que 6 e, caso não seja, a segunda verifica se a média é menor do que 4. Seguindo a lógica nossa formula também terá duas funções SE, uma dentro da outra:

=SE(G5>7;"APROVADO";SE(G5<4;"REPROVADO";"EXAME FINAL"))

Formatação Condicional Bem, terminou! Não? Ah, já sei, tem sempre alguém querendo ainda mais, para dar maior e melhor visibilidade aos resultados. Ok, então por que não colocar as respostas diferenciadas em azul (para os aprovados), amarelas (para quem vai para os exames) e vermelhas (para os reprovados). Isto é o que chamamos de formatação condicional que, como o próprio nome diz, formata sua planilha de acordo com uma condição. Vamos então aplicar a formatação condicional em nossa planilha, siga os passos abaixo: 1.

2.

Selecione toda a faixa de células que devam ser formatadas condicionalmente; no nosso caso as células de H5 até H14; Selecione o menu Formatar - Formatação Condicional;



56

CURSO WINDOWS OFFICE Aula 60: Formatação Condicional 3.

4. 5.

Ao se abrir a janela Formatação Condicional altere a condição e preencha o campo de resultado com a expressão ="APROVADO"; Clique no botão FORMATAR ; Ao se abrir a janela "Formatar Células" escolha a cor AZUL e clique em OK.

AULA 1

para

60 IGUAL

A

Pronto, você criou uma condição para as células selecionadas e esta condição terá efeito somente nas células cujo conteúdo seja ―APROVADO‖. Pressione o botão "ADICIONAR ” para criar as outras condições:

6. Clique em Ok para concluir e observe o resultado:

57




AULA

60

Atividades: 1. Complete a planilha ―Regularização de Autos‖ utilizada por uma loja de veiculos seguindo os seguintes critérios:

Situação: De acordo com um RECALL em vigor todos os veiculos com ano menor que 2000 devem ser recolhidos para regularizar a parte elétrica. Crie a função que indique se será preciso REGULARIZAR (em vermelho) ou se o veiculo já esta REGULARIZADO (em verde). Brinde: Todos os veiculos com DESIGN 4 portas terão por brinde a instalação de CD-PLAYER, já os de DESING 2 portas não. Crie a função para indicar corretamente o brinde de veiculo.

2. Durante a transmissão de uma corrida de formula 1 o locutor precisa informar a pontuação e a situação de cada piloto, você esta encarregado de preparar uma planilha para que esta informação seja obtida rapidamente. Você consegue?

Situação: Total de pontos igual ou maior que 30 o piloto esta CLASSIFICADO (em azul), senão o piloto esta ELIMINDADO (em vermelho).



58

CURSO WINDOWS OFFICE Aula 61: Função =CONT.SE

AULA

61

Atividade: Planilha Campeonato Futsal Vamos criar uma planilha para fazer os cálculos da campanha de um time durante um campeonato de futsal., Observe a planilha abaixo que contem as primeiras informações que deverão ser copiadas. Procure ajustar as colunas, alinhamentos, fontes, etc. Quando terminar de copiar as informações, salve o arquivo com o nome Campeonato.

Para obter este resultado limpo em sua planilha você pode ocultar as linhas de grade. Clique no menu Ferramentas e em Opções e desmarque a caixa Linhas de Grade.

Utilize os recursos de Sombreamento, Bordas e Fontes e Negrito para formatar sua planilha como a do exemplo acima. Agora vamos programar a coluna G, RESULTADO com os seguintes critérios: Se o placar for maior para o time da casa será considerado VITORIA, caso contrário se o placar for igual para ambos os times será considerado EMPATE caso contrário será considerado DERROTA. Observe a formula da célula G5: 

=SE(B5>D5;"VITORIA";SE(B5=D5;"EMPATE";"DERROTA")) Para a coluna H (Pontos) iremos considerar os seguintes critérios:  

59

VITORIA – 3 PONTOS EMPATE – 1 PONTO Apostila Microsoft Excel

Aula 61: Função =CONT.SE


AULA

61

DERROTA – O PONTOS Observe a fórmula da célula H5: 

=SE(G5="VITORIA";3;SE(G5="EMPATE";1;0)) Complete as demais células e sua planilha deve ficar assim:

Utilizando a função matemática =SOMA e as funções estatísticas =MEDIA, =MAXIMO e =MINIMO crie as fórmulas na coluna M, DESEMPENHO ARRANQUE F.C.:

FUNÇÃO =CONT.SE Esta função conta o numero de células que não estão vazias em um determinado intervalo de células que correspondam a uma determinada condição. Observe a sintaxe desta função:

=cont.se(intervalo, condição) Em nossa planilha utilizaremos esta função para determinar quantas vitórias, empates e derrotas o Arranque F.C. obteve durante esta campanha. Observe como fica a formula de H13 e complete sua planilha com as demais formulas:



60


AULA

61

No exemplo acima a função cont.se ira contar quantas células do intervalo G5:G10 satisfazem a condição de serem iguais a ―VITORIA‖. Em nosso exemplo o resultado é 3. O mesmo acontece com as demais formulas das células H14 e H15, bastando apenas substituir a condição para ―EMPATE‖ em H14 e ―DERROTA‖ em H15. Agora que sua

planilha esta pronta, salve-a em seus Documentos com o nome de Campeonato.

Exercícios de Fixação Função Cont.Se 3. Calcular a soma e a média das notas abaixo. Calcular a quantidade de notas 10 para cada quesito:

4. Digite a tabela abaixo e elabore as seguintes fórmulas: e) f) g) h)

61

Valor total em R$: Quantidade multiplicada pelo valor unitário. Tipo de Produto: Se o código do produto for 1 então escreva COURO senão escreva TECIDO. Nº de Ocorrencias: Calcule a quantidade de vezes que ocorrem artigos de couro e artigos de tecido. Soma Total dos Produtos: Calcule a soma total de couros e a soma total de tecidos.



CURSO WINDOWS OFFICE Aula 61: Função =CONT.SE 

______________________________________ 

Qual a fórmula utilizada para determinar o Tipo de Produto na linha do produto Jaqueta ? _______________________________________________________ ______________________________________



Qual a fórmula utilizada para calcular a quantidade de vezes em que ocorrem os artigos de couro?

61

______________________________________________

Qual a formula utilizada para calcular o Valor Total em R$ na linha do produto sapato? _______________________________________________________

AULA

______________________________________________ 

Qual será o resultado obtido pelas seguintes fórmulas: 1. =cont.se(A2:C21;78) _____________________________ 2. =cont.se(A2:A7;‖>50‖) _____________________________ 3. =cont.se(A2:B7;‖<20‖) _____________________________

_______________________________________________________ ______________________________________ 

Qual a fórmula utilizada para calcular a quantidade de vezes em que ocorrem os artigos de tecido? _______________________________________________________ ______________________________________



Qual a fórmula utilizada para calcular a soma total de couros? _______________________________________________________ ______________________________________



Qual a fórmula utilizada para calcular a soma total de tecidos? _______________________________________________________ ______________________________________

5. Para as próximas questões observe a seguinte planilha:



Escreva uma fórmula que calcule quantos números da Lista 1 são maiores que 10: _______________________________________________



Escreva uma fórmula para calcular a média entre os valores da Lista 3: _______________________________________________



Escreva fórmulas que mostrem o maior e o menor valor da Lista 2: Apostila Microsoft Excel


62

CURSO WINDOWS OFFICE Aula 62: Nome de Células, =PROCV

AULA

64

Atividade: Planilha Controle de Comissões Uma empresa produz e vende dois produtos: Produto A e Produto B. Os vendedores da empresa ganham comissões pelas vendas conseguidas. O calculo da comissão se baseia na soma das vendas dos dois produtos. Para calcular a comissão precisamos criar uma tabela que ira controlar o valor merecido para cada vendedor: Quem vende mais ganha mais, quem vende menos ganha menos. Observe a planilha a ser montada abaixo, digite-a e salve-a com o nome de Controle de Comissões.

A planilha de controle de vendas tem por objetivo efetuar o cálculo da comissão de cada vendedor com base no valor vendido e indicar um resultado para o vendedor, ou seja, se ele ultrapassou a sua meta, se cumpriu a meta ou se não atingiu a sua meta. Isto poderá ser medido tomando por base a relação entre o valor vendido e a previsão de vendas. A partir da célula D5 até a célula D10, calcule a soma do Produto A com o produto B. Em seguida estabeleça o cálculo dos valores de: Total Vendido, Média Vendida, Maior Venda e Menor Venda, respectivamente para as colunas Produto A, Produto B e Total.

A TOMADA DE DECISÕES Uma das características mais importantes é o fato de um programa de planilha eletrônica poder tomar algumas decisões sobre uma determinada condição ou situação. Nessa planilha, temos a coluna Situação, que deverá exibir uma das seguintes mensagens: SUPEROU, NÃO ATINGIU e IGUALOU, dependendo das vendas efetuadas por um vendedor em relação à previsão de vendas. Para exibir uma das três mensagens na coluna Situação, devemos utilizar a função SE, que possui a seguinte sintaxe:

=SE(Condição;Verdadeiro;Falso) Onde: Condição – Esta condição é efetuada de acordo com os seguintes operadores lógicos: = Igualdade < Menor que > Maior que <= Menor que ou igual >= Maior que ou igual <> Diferente Verdadeiro – Se a condição FOR satisfeita, então é executado o primeiro argumento.

63


Aula 62: Nome de Células, =PROCV


AULA

63

FALSO – Se a condição NÃO FOR satisfeita, então é executado o segundo argumento. Em um primeiro momento, serão consideradas somente as seguintes mensagens: se o Valor Vendido >= Meta = “SUPEROU”, caso contrário “NÃO ATINGIU”. Coloque na célula F5 a função SE utilizando os seguintes argumentos:

=SE(D5>=E5;”SUPEROU”;”NÃO ATINGIU”) Se o Valor vendido for maior ou igual ao valor da Meta, será exibida na coluna Situação a mensagem SUPEROU, ou seja, o vendedor atingiu ou ultrapassou a sua meta, caso contrário será exibido NÃO ATINGIU. Em seguida efetue a cópia da função para as demais células da coluna Situação e ajuste a largura da coluna. Agora será acrescentada a terceira mensagem na função SE, isto é, a mensagem IGUALOU. A solução para este problema está em trabalhar com duas funções SE simultaneamente, da seguinte forma: estando com o cursor na célula F5, digite a seguinte linha:

=SE(D5>E5;”SUPEROU”;SE(D5=E5;”IGUALOU”;”NÃO ATINGIU”)) Se o Valor Vendido for maior que a Meta, será exibida na coluna Situação a mensagem SUPEROU, ou seja, o vendedor ultrapassou a sua meta. Caso contrário, será verificado o segundo SE, que verificará se o Valor vendido é igual à Meta. Caso verdadeiro, será exibida a mensagem IGUALOU, pois o vendedor cumpriu exatamente a sua meta, caso contrário será exibido NÃO ATINGIU, para o vendedor que não cumpriu a sua meta.

Vamos utilizar uma segunda planilha em nosso trabalho para criar a tabela de comissões. Esta tabela servirá para calcular a comissão de cada vendedor de acordo com o valor vendido, já que quem vende mais ganha mais, quem vende menos ganha menos. Digite a seguinte planilha em Plan2:



64


AULA

64

FUNÇÃO =PROCV Localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela.

PROCV(valor_procurado;matriz_tabela;núm_índice_coluna) Valor_procurado: O valor a ser procurado na primeira coluna da matriz (matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.) da tabela. O Valor_procurado pode ser um valor ou uma referência. Se o valor_procurado for menor do que o menor valor na primeira coluna de matriz_tabela, o PROCV retornará o valor de erro #N/D. Matriz_tabela: Duas ou mais colunas de dados. Use uma referência para um intervalo ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. Núm_índice_coluna: O número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Vamos utilizar a função =PROCV para localizar o valor total de vendas na tabela de comissões e assim determinar qual a porcentagem de comissão a ser paga. Nossa formula na célula G5 ficará assim:

=PROCV(D5;Plan2!A$4:B$9;2) Onde: D5 é o valor total das vendas que iremos pesquisar na tabela de comissões; Plan2!A$4:B$9 é a referencia de toda a tabela de comissões onde a função Procv ira pesquisar o valor D5; 2 é a coluna da tabela de comissões que nos interessa, ou seja o valor da comissão a ser paga referente ao valor pesquisado. Para calcular o valor da comissão em H5 basta multiplicar o Total de Vendas pela Porcentagem de Comissão:

=D5*G5

Nome de células Para facilitar a criação de fórmulas podemos dar nome a uma célula ou a uma faixa de células que usaremos na formula. Geralmente é mais prático trabalhar com nomes do que com endereços de células. Para definir um nome de células para a tabela de comissões siga os seguintes passos: 1. Selecione na tabela de comissões exatamente as células que contem valores, não selecione os títulos das colunas; 2. Clique em Inserir – Nome - Definir para definir um nome para as células selecionadas; 3. Digite o nome TABELA e clique em Ok para aceitar o nome. Agora podemos utilizar o nome TABELA para esta faixa de células.

65




AULA

63

Veja como fica nossa fórmula para a célula G5 utilizando o nome de célula:

=PROCV(D5;TABELA;2)

Juntar função =SE e =PROCV Vamos melhorar nossa planilha. Imagine que o pagamento da comissão seja diferenciado de acordo com a situação de cada vendedor. Quem atingiu a meta merece receber uma comissão maior do que aquele que igualou ou não atingiu. Altere a tabela de comissões conforme o exemplo. Em seguida será necessário redefinir o nome da tabela. Execute o comando: Inserir - Nome - Definir Selecione TABELA e dê um clique no botão Excluir. Somente então poderá ser definido o nome para a nova área. Digite novamente o nome TABELA. Posicione o cursor no campo Refere-se a e selecione a faixa de célula de A4 até D9. Clique o botão OK. É necessário em seguida alterar a fórmula da célula G5 em nossa planilha. Observe: =SE(F5="SUPEROU";PROCV(D5;TABELA;2);SE(F5="IGUALOU";PROCV(D5;TABELA;3);PROCV(D5;TABELA;4)))

Na fórmula acima, é feita uma verificação da situação do vendedo. Se o vendedor superou a meta receberá a comissão da coluna 2 da tabela. Caso o vendedor tenha igualado à meta receberá a comissào da coluna 3 da tabela. Por último, se não superou e nem igualou a meta receberá a comissão da coluna 4 da tabela. Copie para a demais células, de forma que a planilha fique completa.

Formatação Condicional Com o objetivo de melhorar o acabamento visual da planilha utilize os recursos de formatação condicional para apresentar os textos ―Superou‖, ―Igualou‖ e ―Não Superou‖ em cores diferentes :   

Superou Igualou Não Superou

Fonte Verde Escuro e Sombreamento Verde Claro Fonte Azul Claro e Sombreamento Azul Escuro Fonte Vermelho Escuro e Sombreamento Vermelho Claro Apostila Microsoft Excel


66


Agora conclua e salve sua planilha:

67



AULA

64

CURSO WINDOWS OFFICE Aula 63: Gráficos

AULA

63

Gráficos Os Gráficos são muito importantes para a apresentação e visualização de comparações, padrões e tendências de dados. Por apresentarem um apelo visual atraente, produzem melhores resultados que as mesmas informações em uma tabela. Geralmente os gráficos são apresentados no formato de colunas ou ―pizza‖, mas a variedade deles é muito grande. Desde que um gráfico transmita corretamente sua informação podemos utilizar qualquer formato disponível no Excel, como por exemplo, gráficos em forma de cilindros, bolhas, áreas, dispersão, linhas, barras, etc. Veja alguns exemplos:

Os gráficos são objetos vinculados aos dados da planilha, os quais servem de base para sua formação. Quando os dados são alterados, o gráfico é automaticamente atualizado.

Gráfico Exemplo: 1. Monte a tabela abaixo:

2. Dê um clique no botão de inserir gráfico na Barra de Ferramentas Padrão . Este botão inicia um assistente de 4 (quatro) etapas que ira orienta-lo durante a criação do gráfico. A primeira etapa é escolher o tipo de gráfico. Observe que não é necessário selecionar os dados para começar o gráfico, o assistente ira solicitar os dados no momento certo.


Aula 63: Gráficos

68


AULA

64

3. Escolhido o gráfico é preciso selecionar os dados que formarão o gráfico. Na etapa 2 o assistente solicita os dados, você pode digitar o intervalo de células ou selecionar clicando no botão .

4. Feito isso clique no botão avançar. A tela abaixo irá surgir, e você deverá definir um título para o seu gráfico.

5. Na ultima etapa você ira definir se quer o gráfico em uma nova planilha ou como objeto na planilha existente.

69


Aula 63: Gráficos


AULA

63

6. Clique em concluir para ver então seu gráfico pronto:

Exercícios 1. Gráfico em Linhas: Essa forma de representação gráfica é praticamente uma aplicação do conceito elementar de funções, onde utiliza dois eixos, sendo um na horizontal chamado de eixo das abcissas (ou eixo ―x‖) e outro na vertical chamado de eixo das ordenadas (ou eixo y). Cada ponto é representado no plano cartesiano pelos valores de x e y, e são chamados de coordenadas (x,y).

2. Gráfico de Barras ou Colunas Agrupadas: Para a construção dos gráficos de barras ou colunas, representando séries estatísticas, usamos retângulos dispostos verticalmente (colunas) ou horizontalmente (barras), seguindo uma certa proporcionalidade entre largura e altura.

3. Gráfico de Barras ou Colunas Empilhadas: Existem situações em que precisamos estudar dois fatos ou mais de forma simultânea.


Aula 63: Gráficos

70


AULA

64

4. Gráfico de Pizza: Normalmente utiliza-se este tipo de gráfico quando se pretende comparar diversas partes de um todo.

5. Crie a tabela a seguir, contendo uma distribuição fictícia de custos de uma empresa e monte um gráfico do tipo pizza como o mostrado na figura. Após pronto, altere os valores de custo para ver o que acontece no gráfico.

71


Aula 63: Gráficos

CURSO WINDOWS OFFICE Aula 64 – Exercícios com Gráficos

AULA

66

Atividades – Exercícios com Gráficos Crie os seguintes gráficos conforme solicitado: 1. Monte a planilha dos Goleiros Artilheiros abaixo e crie os seguintes gráficos de barras com efeito 3D:  Para a artilharia em penalts;  Para a artilharia em faltas;  Para a artilharia em total de gols.

2. Na planilha Basquete NBA – 2005/2006 insira um gráfico pizza 3D para os resultados da conferencia Leste e outro para os resultados da conferencia Oeste.


Aula 64 – Exercícios com Gráficos

72

CURSO WINDOWS OFFICE Aula 64 – Exercícios com Gráficos

AULA

64

3. Insira o Gráfico COLUNAS COM PROFUNDIDADE para os dados da tabela abaixo.

4. Na tabela abaixo O Países que mais pagam impostos insira um gráfico cilindro para representar os dados corretamente.

5. Crie a tabela abaixo, complete com as formulas necessárias e insira um gráfico cilindro (Horizontal):

73


Aula 64 – Exercícios com Gráficos

CURSO WINDOWS OFFICE Aula 65: Gerenciamento de Dados

AULA

66

Gerenciando Dados Vamos exemplificar o gerenciamento de dados com os dados da planilha seguinte:

Classificação No Excel não é preciso se preocupar com a ordem que digitamos listas de dados. O Excel tem a ferramenta de classificação para nos ajudar. Vamos classificar a planilha Preços de automóveis da seguinte maneira: vamos organizar os carros por marca e por preço, começando pelo carro mais caro de cada marca. Faça assim: 1. Selecione o intervalo de dados dos preços (A3:D20). 2. No menu Dados clique em Classificar... Surgirá a caixa de diálogo Classificar. 3. No campo Classificar por selecione a coluna Marca e ordem crescente. Deste modo o Excel vai reorganizar as linhas para que os carros da mesma marca fiquem juntos. 4. No campo Em seguida por selecione a coluna Preço e ordem decrescente. Deste modo, depois de organizar por marca o Excel vai organizar pelo preço, começando pelo maior. 5. Clique em OK. Observe a figura abaixo a janela Classificar permite classificar a faixa de planilha selecionada de acordo com os critérios estabelecidos.


Aula 65: Gerenciamento de Dados

74


AULA

66

Ao clicar em OK sua planilha será classificada e você obterá o seguinte resultado:

Filtragem A ferramenta de filtragem é utilizada para selecionar dados numa lista grande. Filtrar é exibir dados que atendem a certa condição e ocultar os demais. Vamos filtrar a planilha Preços de automóveis da seguinte maneira: Vamos exibir apenas as linhas dos carros da categoria Sedan. Faça assim: 1. Selecione o intervalo de dados (A3:D20) 2. No menu Dados clique em Filtrar e em AutoFiltro. Surgirão botões ao lado de cada título de coluna.

3. Clique no botão do título Categoria e selecione a opção Sedan. 4. Pronto o Excel exibirá apenas as linhas dos carros Sedan. 75




AULA

66

Para exibir as linhas ocultas pela filtragem clique no menu Dados, em Filtrar e na opção Mostrar tudo. Vamos fazer mais uma filtragem de modo diferente. Vamos exibir apenas os carros que custam menos de R$ 20.000,00. Faça assim: 5. Clique no botão da coluna Preço. 6. Escolha a opção Personalizar... Surgirá a caixa de diálogo Personalizar AutoFiltro. 7. No primeiro campo selecione é menor do que e no segundo campo digite 20000,00

8. Clique em OK.

Para remover um filtro clique em Dados, em Filtrar e desmarque a opção Autofiltro.



76


AULA

66

Atividades: 1. Na tabela abaixo do Campeonato Brasileiro vamos criar gráficos que mostram o desempenho dos times por estado. Classifique a tabela então por estado e crie um gráfico para cada estado, destacando a classificação de cada time. Você ira criar seis gráficos.

2. Classifique a tabela por "Nome" em ordem decrescente e depois filtre por salário "Maior do que 2.500,00"

77



CURSO WINDOWS OFFICE Aula 66: Funções Data e Hora

AULA

66

Funções com Datas e Horas Objetivo: Já aprendemos sobre a maneira como o Excel trata de datas e horas. Nesta lição vamos estudar as principais funções do Excel para manipulação de valores de 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, poderia 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 empregados devem receber de acordo com o salário padrão (para o período de segunda a sexta-feira) ou a uma taxa de horas extras (para sábados e domingos).

Função Hoje() Sintaxe: Hoje() A função Hoje() retorna a data atual do sistema. A data é inserida no formato 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 vantagem 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.

Função Hora(número) 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 corresponde a exatamente 12 horas.

=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 hora. Também podemos passar um valor de hora, como parâmetro para a função Hora(). Considere o 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 coloca -la entre aspas, conforme indicado no exemplo anterior.

Função Agora() Sintaxe: Agora() 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 diretamente? 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() teremos sempre a data e hora atualizadas.


Aula 66: Funções Data e Hora

78


AULA

66

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 representa 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 é opcional e determina a maneira pela qual o resultado é interpretado. Se tipo_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 é domingo. Se tipo_retorno for 3, a função retornará um número entre 0 e 6, onde 0 é segunda feira e 6 é domingo. Vamos considerar o exemplo da planilha a seguir:

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). Observe que definimos o parâmetro tipo_retorno como sendo 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 utilizamos 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.

Função Dia(Data) Sintaxe: Dia(Data) Argumentos: Recebe uma data ou o endereço de uma célula onde existe um valor do tipo data e retorna um número inteiro, correspondente ao dia da data passada como parâmetro. Vamos considerar o exemplo da planilha a seguir:

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. 79




AULA

66

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. Vamos considerar o exemplo da planilha a seguir:

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 pelos resultados obtidos. Importante: A função mês deve ser digitada com o acento, caso contrário será gerada uma mensagem de erro.

Função Ano(Data) Sintaxe: Ano(Data) Argumentos: Recebe uma data ou o endereço de uma célula onde existe um valor do tipo data. Vamos considerar o exemplo da planilha a seguir:

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 pelos resultados obtidos.

Função DATA.VALOR("dd/mm/aaaa") 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 Excel. Considere o exemplo:

=DATA.VALOR("31/12/2020") esta função ira retornar 44196. Isto significa que entre 01/01/1900 e 31/12/2020 teremos 44196 dias . Apostila Microsoft Excel


80


AULA

66

Função Hora(hh:mm:ss) 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 hora. Vamos considerar o exemplo da planilha a seguir:

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

Função Minuto(hh:mm:ss) Sintaxe: Minuto(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 hora. Vamos considerar o exemplo da planilha a seguir:

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.

Função Segundo(hh:mm:ss) Sintaxe: Segundo(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 hora. Vamos considerar o exemplo da planilha a seguir:

81




AULA

66

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.



82

CURSO WINDOWS OFFICE Aula 67: Validação de Dados

AULA

68

A validação é um mecanismo que permite controlar a digitação de dados. Podemos definir que alguns valores não serão aceitos numa célula. Vamos a um exemplo: Uma empresa atacadista de equipamentos de Informática tira seus pedidos de compra no Excel. No pedido de compra há uma tabela com as seguintes colunas: Quantidade, produto, preço unitário e preço total. Vamos criar uma validação de dados nas células da quantidade. Queremos que sejam aceitos apenas números inteiros maiores que 3. Isso porque 3 é o pedido mínimo aceito pela empresa e inteiros porque não dá para cortar os equipamentos e vender em pedacinhos.

Para criar uma validação de dados faça assim: 3. 4. 5. 6. 7. 8. 9. 10.

Selecione as células do item quantidade (A4:A7). No menu Dados escolha Validação... Surgirá a caixa de diálogo Validação de dados. No campo Permitir escolha Número inteiro. No campo Dados escolha maior do que. No campo mínimo digite 3. Na guia Mensagem de entrada digite o título Quantidade e a mensagem Mínimo 3. Na guia Alerta de erro digite o título Quantidade e a mensagem A quantidade mínima é 03. Clique em OK.

83


Aula 67: Validação de Dados


AULA

67

Pronto. O Excel não aceitará qualquer valor na coluna das quantidades. Quando o usuário selecionar uma célula receberá uma mensagem de entrada. Se o usuário tentar digitar um valor inválido será avisado por uma caixa de alerta.

Para remover uma validação, primeiro selecione as células. Em seguida clique no menu Dados e escolha Validação... Na caixa de diálogo clique no botão Limpar tudo.

Atividades 1. AutoFiltro Construa uma planilha de acordo com a seguinte figura:

Aplique AutoFiltro para a seleção ―todas as pessoas com salário maior do que R$ 2000,00 e menor do que R$ 5000,00‖. Clique sobre o ―filtro‖ da coluna Salário e escolha personalizar. Preencha os campos como mostra a figura

a seguir:

OBS: para exibir novamente todos os dados, você poderá acessar o menu Dados -> Filtrar e escolher a opção Mostrar todos. Apostila Microsoft Excel


84


AULA

68

2. Validação Abra novamente a planilha do exercício anterior. Vamos utilizar o recurso de validação de dados do Excel para exigir que o usuário digite sempre uma data válida para as células do campo Data Nasc. Veja os passos a seguir;   



Selecione as células referentes às datas de nascimento (F2:F6) Vá até ao menu Dados e escolha a opção Validação... A janela a seguir permite a definição dos critérios de validação. Suponha que desejamos permitir apenas datas posteriores a 1/1/1900. Assim, preencha os campos de acordo com a figura a seguir.

Ainda na janela Validação de dados , clique na aba Mensagem de entrada. Esta parte da janela permite que especifiquemos uma mensagem que será mostrada para o usuário quando a célula for selecionada, com o intuito de auxiliá-lo no preenchimento. Veja a figura a seguir.

Clique na aba Alerta de erro. Nesta parte da janela, a caixa Estilo permite a seleção do tipo de alerta de erro. As opções são: a) Parar – Não permite, de forma alguma, que o usuário informe um dado inválido para a célula. b) Aviso – Notifica o usuário quando um dado inválido está sendo inserido, mostrando uma janela para confirmação ou não da inserção daquele dado. c) Informações – Apenas notifica o usuário quando um dado inválido está sendo inserido na célula. Veja a figura a seguir. 

85






AULA

67

Clique em Ok na janela da figura anterior e em seguida tente digitar uma data inválida qualquer (por exemplo, o texto ―teste‖) em uma célula do campo Data Nasc. Faça testes escolhendo outras opções para

o campo Estilo da janela de validação de dados. Tente digitar novamente datas inválidas e repare as mensagens de erro apresentadas.



86

CURSO WINDOWS OFFICE Aula 68: Congelamento de Painéis

AULA

68

Quando trabalhamos com planilhas grandes temos que usar as barras de rolagem. Só que ao rolar uma planilha para cima os títulos das colunas somem da nossa vista. Do mesmo modo ao rolar uma planilha para a esquerda perdemos de vista os títulos de linhas. É descômodo trabalhar sem ver os títulos das colunas. Para resolver este problema existe o congelamento de painéis. Congelar painéis é deixar algumas linhas ou colunas da planilha fixas na tela. Mesmo se rolarmos a planilha as células congeladas continuam visíveis. Vejamos um exemplo:

As linhas 1, 2 e 3 da planilha estão congeladas. Na segunda imagem da planilha as linhas 4 até 17 estão ocultas por causa da rolagem da página para cima, mas as linhas 1, 2 e 3 continuam fixas na tela. Para congelar painéis faça o seguinte: 3. Clique na célula que delimitará os painéis congelados. No exemplo é a célula A4. 4. Clique no menu Janela e em Congelar painéis. pa inéis. 5. Pronto. As linhas acima e as colunas à esquerda da célula selecionada ficam congeladas. Para desfazer o congelamento de painéis clique no menu Janela e no comando Descongelar painéis.

Divisão de janela Às vezes quando trabalhamos numa planilha grande queremos enxergar algo no início da planilha e algo que está no fim. Para trabalhar em duas partes diferentes da planilha ao mesmo tempo existe a divisão de janela. Para dividir a janela em duas partes faça assim: 6. Clique numa linha para definir que a divisão será acima desta linha. 7. No menu Janela escolha Dividir. 8. Pronto. A área de trabalho está dividida em duas partes, com barras de rolagem independentes.

87


Aula 68: Congelamento de Painéis


AULA

68

Para desfazer a divisão de janelas clique no menu Janela e em Remover divisão.

Exercícios de Revisão 1 1. Planilha Produtos em Estoque – Alimentos a)

O Preço de Venda deverá ser o Preço de Custo acrescido de 60%.

b) O Lucro é o Preço de Venda menos o Preço de Custo multiplicado pela quantidade. c)

Calcular o total, média, maior valor e menor valor das colunas Preço de Custo e de Venda.

d) Para realizar os cálculos acima para a coluna Lucro, nomear esta coluna e utilizar este nome na função. e) Na coluna Qtde de Dias para Vencer, indicar com a cor vermelha a qtde inferior a sete dias. f)

Na célula k5 inserir o valor R$300. Na coluna I, somar o lucro com o valor desta célula. Utilizar o recurso Fixar Célula.



88


AULA

68

2. Planilha Produtos em Estoque – Higiene e Limpeza a) O Preço de Venda deverá ser o Preço de Custo acrescido de 60%. b) O Lucro é o Preço de Venda menos o Preço de Custo multiplicado pela quantidade. c) Calcular o total, média, maior valor e menor valor das colunas Preço de Custo e de Venda. d) Para realizar os cálculos acima para as colunas Preço de Custo e de Venda, nomear esta coluna e utilizar o nome na função. e) Na célula J5 inserir o valor R$500. Na coluna I, somar o lucro com o valor desta célula. Utilizar o recurso Fixar Célula.

3. Planilha Loja de Moveis a) b) c) d) e) f) g) h) i) j)

89

Colunas digitadas: Código, Produto, Preço e Qtde. Total: Qtde multiplicada pelo Preço. Situação do Estoque: Se Qtde for inferior a 5, exibir ―Repor Estoque‖, senão ―Estoque OK‖.

Desconto: Se o Preço for superior a R$ 800,00, o Desconto será de 15% do Preço, senão será de 8%. Em Soma Total + R$ 300,00, utilizar Fixar Célula ($). Criar um gráfico com o Nome do Produto e seu Preço. Inserir mais cinco produtos. Congelar Painéis. Dividir a Planilha. Criar um Procv que exiba a situação do estoque de acordo com o código passado.




AULA

68

4. Planilha Alunos do Excel. Utilizar uma Procv para exibir o curso do aluno de acordo com a matricula passada.



90

CURSO WINDOWS OFFICE Aula 69: Praticando

AULA

69

Função SOMASE – Planilha Relatório de Vendas Você trabalha em uma loja de produtos de informática que possui dezenas de vendedores espalhados em várias regiões. Seu gerente solicitou uma planilha para calcular o total de vendas por vendedores, o total de vendas por região e o total de vendas por produto, todos representados em gráficos. Além disso, também quer saber o salário de cada vendedor baseado em comissões sobre o total de vendas. Para atender a esta necessidade vamos criar uma pasta de trabalho no Excel com 3 planilhas: 1. Relatórios – Planilha com o relatório de vendas por vendedor, região e produtos. 2. Gráficos – Total de vendas por vendedor, região e produtos. 3. Tabelas – Cadastro dos vendedores e dos produtos. Agora é com você, observe os exemplos abaixo e mãos a obra: 4. Crie a pasta de trabalho com as tres planilhas necessárias.

5. Na planilha tabelas cadastre os vendedores e produtos a serem consultados pelas demais planihas. Utilize NOME DE CÉLULAS: Selecione a faixa de celulas dos produtos e nomeia como PRECOS, faça o mesmo com a faixa de células dos vendedores e nomeia VENDEDORES:

6. Crie a planilha Relatórios de forma que o operador apenas digite o código do vendedor, o código do produto e a quantidade vendida. Sua planilha deverá preenchar automaticamente as demais informações, buscando as informações na planilha Tabelas (PROCV) e calculando o valor total de cada venda (PREÇO UNITARIO * QUANT).  Observe as funções das seguintes células:  91

Função na célula B4 de Relatórios: =PROCV(A4;VENDEDORES;2) Apostila Microsoft Excel

Aula 69: Praticando

CURSO WINDOWS OFFICE Aula 69: Praticando 

AULA

69

Função na célula C4 de Relatórios: =PROCV(A4;VENDEDORES;3)

7. Agora vamos calcular o total de vendas por vendedor, o total de vendas por região e o total de vendas por produto. Parece complicado, mas as funções do Excel nos auxiliam muito nestas tarefas. Para calcular o total de vendas por vendedor é necessário realizar uma soma condicional, isto é, queremos que o Excel apenas calcule o total de um vendedor X. Para isso uma formula não seria suficiente, devemos utilizar uma função, no caso: SOMASE: (o próprio nome já diz a que veio – essa função realiza uma Soma, Se algo for cumprido – é uma soma condicional). Veja se ela realmente se aplica ao nosso caso: Precisamos calcular o total das vendas do vendedor X, precisamos que o programa busque na coluna VENDEDOR todas as referências ao vendedor X e faça uma relação com a coluna TOTAL dos produtos vendidos. A função SOMASE possui uma estrutura básica que se mantêm inalterável.

=SOMASE(Xi:Xf;”nome”;Yi:Yf) Onde (os exemplos são para a linha 4 da planilha Gráficos): Xi:Xf = endereço inicial (Xi) até o endereço final (Xf) da coluna onde se encontra o item a ser comparado. No caso do nosso exemplo: Relatorios!A4:A20 “nome” = Nome do item a ser comparado – no caso, o nome do vendedor, pode-se usar neste caso a célula onde se encontra o nome do vendedor, ou seja: Gráficos!A4

Yni:Ynf = Endereço inicial (Yni) até o endereço final (Ynf) da coluna onde se encontram os valores a serem somados. No nosso exemplo: Relatorios!H4:H20 Ficando a função completa assim:

=SOMASE(Relatorios!A4:A20;Gráficos!A4;Relatorios!H4:H20) Como a faixa de células dos vendedores e a faixa de células dos valores devem ser sempre as mesmas, devemos fixar as linhas (REFERENCIA ABSOLUTA) destas faixas para que possamos copiar a função para as demais células através da alça de preenchimento, assim:


Aula 69: Praticando

92


AULA

69

=SOMASE(Relatorios!A$4:A$20;Gráficos!A4;Relatorios!H$4:H$20) Devemos repetir o mesmo procedimento para gerar relatórios dos totais de Produtos e Regiões.

8. Já o salário de cada vendedor deverá ser calculado considerando o seguinte critério: Se o vendedor vendeu mais de R$ 5.000,00 receberá 1,5% de comissão mais o salário que é R$ 500,00, se vendeu abaixo disso, a comissão será de 1% mais o salário. Quando trabalhamos com condição, usamos a Função SE, que estipula que algo só acontecerá se a condição for cumprida. A função SE possui uma estrutura bastante simples de se entender, e é ela a chave para que nunca mais esqueçamos como usá-la. Estrutura da Função SE:

=SE(Teste_lógico;Valor_se_Verdadeiro;Valor_se_Falso) Sendo Teste Lógico a nossa condição, o valor se verdadeiro é o que acontecerá caso a condição seja atendida e o valor se falso é o que acontecerá se a condição não for atendida. Dessa maneira temos que transpor o que desejamos obter para dentro dessa estrutura. O primeiro passo seria, então, descobrir qual é o Teste Lógico. Na Frase: Se o vendedor vendeu mais de R$ 5.000,00 receberá 1,5% de comissão mais o salário que é R$500,00, se vendeu abaixo disso, a comissão será de 1% mais o salário – qual é o Teste Lógico? O que desejamos testar? Preste atenção no inicio da frase: total de vendas do Vendedor ser maior que R$ 5.000,00 – então esse seria o teste lógico. Se esse total for maior que R$ 5.000,00, o vendedor receberá 1,5% de comissão (desse total), acrescido de R$ 500,00 – Esse então seria o valor se verdadeiro e por fim, caso o total de vendas seja inferior a R$ 5.000,00 então a comissão seria de 1% (desse total), mais R$500,00. Ficando assim a nossa Função:

=SE(total_de_vendas>5000;(total_de_vendas*1,5%)+500;(total_de_vendas*1%)+500) Acontece que total de vendas possui um endereço e devemos substituir o texto pelo endereço do mesmo, olhe na tabela e veja em que lugar se encontra o total de vendas por vendedor. No nosso exemplo, o total está situado na coluna D, linha 4 da planilha Gráficos. Então nossa Função ficaria exatamente assim:

=SE(C4>5000;(C4*1,5%+D$2);(C4*1%+D$2)) Resolvendo assim todos os desafios da planilha, agora é só criar os gráficos e salvar sua atividade.

93


Aula 69: Praticando



Aula 69: Praticando

AULA

69

94


Exemplos: FUNÇÃO SE - CALCULANDO O SALÁRIO DE CADA VENDEDOR

FUNÇÃO SOMASE - TOTAL DE VENDAS POR REGIÃO:

GRÁFICO TOTAL DE VENDAS POR PRODUTO

PROCV PARA ENCONTRAR O PRODUTO EM PREÇOS

95


Aula 69: Praticando

AULA

69

CURSO WINDOWS OFFICE Aula 70: Atividades

AULA

70

Exercícios Avaliação: Digite e resolva os exercícios abaixo. Estes exercícios serão avaliados e somarão pontos em sua nota de Avaliação Continua. Faça TODOS os exercícios. Se for necessário agende Aula Extra. Depois de concluído envie-os para o email de seu professor. 1. Calcule os gastos mensais usando as Funções Soma, Média, Máximo e Mínimo:

2. Complete as formulas utilizando o recurso de referencia absoluta:

3. Calcule a tabela a seguir utilizando a Função SE: Se o Total,Multiplicado por 2,5 (Bônus) for Maior que 250, então será "Classificado", senão "Eliminado‖. Faça as seguintes formatações: Se o TOTAL de pontos for maior

ou igual a 90 AZUL e Negrito, se for menor do que 90 VERMELHO e Negrito. Classificados Padrões de Cor Verde Escuro, Fonte Verde Claro, Eliminados Padrões de Cor Amarelo e Fonte Vermelha.


Aula 70: Atividades

96


4. Complete com as formulas necessárias:

5. Calcule a idade das personalidades usando a Função HOJE():

97


Aula 70: Atividades

AULA

70


AULA

70

6. Classificar as colunas "GÊNERO","FILMES"e "CLASSIFICAÇÃO" em ordem Decrescente:

7. Calcule a porcentagem multiplicando a coluna B pela C,e depois somando a B com a D:

8. Crie 2 Gráficos Pizza 3D para as notas de Salto 1 e Salto 2:


Aula 70: Atividades

98


AULA

70

9. Digite e formate a tabela abaixo e calcule usando as formulas ou funções apropriadas para obter o resultado:

   

Calcule os totais de cada mês utilizando a FUNÇÃO SOMA e os totais gastos por depesas no trimestre. Coloque em Azul o total gasto em cada mês. Coloque em Verde o total gasto com cada despesa no trimestre. Coloque em Roxo o total geral do trimestre.

10. Digite e formate a tabela abaixo e calcule usando as formulas ou funções apropriadas para obter o resultado:

   

99

Calcule as Médias dos alunos. Ache o resultado, sabendo-se que a condição para o aluno ser aprovado é que a média seja superior ou igual a 7. Usando a formatação condicional faça com que o resultado do aluno quando for aprovado fique Azul. Formate a planilha a seu gosto.


Aula 70: Atividades

CURSO WINDOWS OFFICE Aula 71: Trabalho Final

AULA

71

Está é a aula do trabalho final de Excel. Elabore a planilha abaixo de acordo com o enunciado. Salve a planilha em Documentos e Imprima. Este trabalho deverá ser entregue ao professor para avaliação.

Planilha Resultado Escolar 1. 2. 3. 4.

Digite a planilha Resultado Escolar do Colégio Space Ghost. Na coluna MÉDIA calcule a média das notas bimestrais de cada aluno. Em RESULTADO DA TURMA calcule a média das médias dos alunos. Informe a SITUAÇÃO do aluno e da turma conforme as tabelas abaixo: Critério para os alunos: CRITÉRIO DE APROVAÇÃO DO ALUNO APROVADO MÉDIA >= A 7,0 RECUPERAÇÃO MÉDIA < 7,0 E >= 5,0 MÉDIA < 5,0 REPROVADO Critério para a turma: CRITÉRIO DE RESULTADO DA TURMA SATISFATÓRIO MÉDIA >= 5,0 MÉDIA < 5,0 INSATISFATÓRIO

5. Formate a planilha observando as células mescladas e as bordas aplicadas. 6. Insira o logotipo da escola. 7. Insira os seguintes cabeçalho e rodapé: Cabeçalho:

Rodapé:

8. Salve em Documentos como TrabalhoFinalExcel. 9. Imprima e entregue ao professor para avaliação.


Aula 71: Trabalho Final

100

CURSO WINDOWS OFFICE Aula 71: Trabalho Final

101


Aula 71: Trabalho Final

AULA

71

Apostila Microplus Excel

Recommend Documents