Informatica - Excel - 53

SAVE OFFLINE

Excel 2007

SERVIÇO NACIONAL DE APRENDIZAGEM INDUSTRIAL – SENAI DR/MG

Excel 2007

________________________________Página 1 de 53 Excel 2007

Excel 2007

Presidente da FIEMG Olavo Machado Junior Gestor do SENAI Petrônio Machado Zica Diretor Regional do SENAI e Superintendente de Conhecimento e Tecnologia Lucio José de Figueiredo Sampaio Gerente de Educação e Tecnologia Edmar Fernando de Alcântara

Elaboração / Ano Anderson Dias de Oliveira / 2010 Unidade Operacional Centro de Formação Profissional “Orlando Chiarini”

________________________________Página 2 de 53 Excel 2007

Excel 2007

APRESENTAÇÃO

“Muda a forma de trabalhar, agir, sentir, pensar na chamada sociedade do conhecimento.” Peter Drucker

O ingresso na sociedade da informação exige mudanças profundas em todos os perfis profissionais, especialmente naqueles diretamente envolvidos na produção, coleta, disseminação e uso da informação. O SENAI, maior rede privada de educação profissional do país, sabe disso, e, consciente do seu papel formativo, educa o trabalhador sob a égide do conceito da competência: “formar o profissional com responsabilidade no processo produtivo, com iniciativa na resolução de problemas, com conhecimentos técnicos aprofundados, flexibilidade e criatividade, empreendedorismo e consciência da necessidade de educação continuada”. Vivemos numa sociedade da informação. O conhecimento, na sua área tecnológica, amplia-se e se multiplica a cada dia. Uma constante atualização se faz necessária. Para o SENAI, cuidar do seu acervo bibliográfico, da sua infovia, da conexão de suas escolas à rede mundial de informações – internet - é tão importante quanto zelar pela produção de material didático. Isto porque, nos embates diários, instrutores e alunos, nas diversas oficinas e laboratórios do SENAI, fazem com que as informações, contidas nos materiais didáticos, tomem sentido e se concretizem em múltiplos conhecimentos. O SENAI deseja, por meio dos diversos materiais didáticos, aguçar a sua curiosidade, responder às suas demandas de informações e construir links entre os diversos conhecimentos, tão importantes para sua formação continuada ! Gerência de Educação e Tecnologia

________________________________Página 3 de 53 Excel 2007

Excel 2007

SUMÁRIO 1

FORMATAÇÃO E

1.1 1.2

FUNÇÕES........................................................................................... Formatação e guias............................................................................ 05 Uso da função soma, multiplicação, divisão, subtração e

1.3 1.4

potenciação......................................................................................... 05 Copiar fórmulas absolutas e relativas ................................................ 05 Função SE (Fórmulas condicionais)................................................... 06

2

BANCO DE DADOS E TRATAMENTO DE DADOS...........................

2.1

Banco de dados .................................................................................

2.2

Vínculos com planilhas externas e arquivos.......................................

2.3

PROCV

2.4

PROCV/CONCATENAR

2.5

Formatação condicional

2.6

Nome em células

2.7

SOMASE

2.8

Comentários em células

3 3.1 3.2

FILTROS E TABELA DINÂMICA Uso do comando FILTRAR Função SUBTOTAL no modo filtrar

3.3 3.4

Classificação do banco de dados Subtotais

3.5

Tabela Dinâmica

3.6

Formulário

3.7

Validação

4

MACROS, CAIXAS E BOTÕES

4.1

Macros

4.2

Filtro Avançado

________________________________Página 4 de 53 Excel 2007

05

Excel 2007

4.3

Caixa drop-down

4.4

Botões de seleção

5

FUNÇÕES AVANÇADAS

5.1

Funções financeiras

5.2

Funções estatísticas

5.3

Funções diversas

5.4

Gráficos

5.5

Vínculos com outros aplicativos

5.6

Botões em macros

________________________________Página 5 de 53 Excel 2007

Excel 2007

Antes de um piloto de avião fazer seu primeiro vôo, ele precisa primeiramente conhecer muito bem os controles e botões que o avião possui no seu painel, ele deve saber, por exemplo, qual botão ou controle ele usa para baixar o trem de pouso. Usar o Excel, ou qualquer outro software, não é muito diferente, para que você possa obter sucesso na sua utilização você precisa conhecer bem suas ferramentas, atalhos, menus e recursos. Os próximos capítulos irão lhe oferecer justamente isto, um conhecimento detalhado de cada Guia para que você possa “decolar” com o Excel 2007 e ter um vôo de sucesso. Porém, antes disso, estudaremos uma breve explicação sobre o porquê das Guias. Sem dúvida, o maior diferencial que o Excel 2007 possui em relação as suas versões anteriores é a substituição das Barras de Menus e Ferramentas pelas Guias que foram organizadas em: Início, Inserir, Layout da Página, Fórmulas, Dados, Revisão, Exibição e Desenvolvedor. Comenta-se que a razão desta substituição foi o fato de que muitas ferramentas, opções e atalhos presentes nas versões mais antigas do Excel eram totalmente desconhecidos por seus usuários. Isso acontecia porque, nessas versões, a Barra de Ferramentas era padronizada de acordo com a freqüência de uso destes recursos e o espaço disponível para visualização, deixando assim, várias ferramentas, opções e atalhos ocultos aos usuários das versões 97, 2000, XP e 2003 do Excel. Entretanto, com a utilização de Guias tanto a visualização quanto a utilização dos recursos acima citados tornaram-se extremamente mais fáceis e práticos para os usuários do Excel 2007.

________________________________Página 6 de 53 Excel 2007

Excel 2007

1.1 GUIA INÍCIO

________________________________Página 7 de 53 Excel 2007

Excel 2007

GUIA INSERIR

________________________________Página 8 de 53 Excel 2007

Excel 2007

GUIA LAYOUT DA PÁGINA

________________________________Página 9 de 53 Excel 2007

Excel 2007

GUIA FÓRMULAS

________________________________Página 10 de 53 Excel 2007

Excel 2007

GUIA DADOS

________________________________Página 11 de 53 Excel 2007

Excel 2007

GUIA REVISÃO

________________________________Página 12 de 53 Excel 2007

Excel 2007

GUIA EXIBIÇÃO

________________________________Página 13 de 53 Excel 2007

Excel 2007

FORMATAÇÕES OPERACIONAIS • Barra de status como calculadora Imagine que você precise fazer vários tipos de cálculos como: média, máximo, mínimo e soma. Ao invés de criar várias fórmulas para obter estes cálculos você pode, simplesmente, usar a barra de status como uma calculadora. Para isso, você só precisará dar alguns cliques para configurá-la. 1. Clique com o botão direito do mouse sobre a Barra de Status. 2. Selecione todas as opções de cálculos desejadas. 3. Agora, toda vez que você selecionar um intervalo de dados no Excel 2007, a Barra de Status efetuará diversos cálculos automaticamente, simples e sem fórmulas, funcionando como uma calculadora.

• Barra de Ferramentas de Acesso Rápido do Excel 2007 1. Clique no botão com o símbolo do Office . 2. Depois clique em Opções do Excel. 3. Clique em Personalizar.

________________________________Página 14 de 53 Excel 2007

Excel 2007

4. Clique na opção que deseja acionar rapidamente e depois no botão Adicionar 5. O Atalho escolhido foi adicionado a sua Barra de Ferramentas de Acesso Rápido do Excel 2007. É só clicar nele para acioná-lo.

• Formatação condicional 1. Selecione as células nas quais você deseja aplicar a formatação condicional. 2. Clique em Início e depois em Formatação Condicional Neste item podemos obter uma noção visual direta e instantânea dos elementos dispostos na tabela. Este recurso funciona somente com números. • Inserindo dados de forma mais rápida em um formulário 1- Inicialmente insira na barra de acesso rápido o item “FORMULÁRIO”; 2- O item formulário deverá aparecer na barra de acesso rápido, então ao clicar em qualquer célula portadora de informação e clicar no ícone “formulário”, surgirá uma janela como ilustra a figura abaixo:

________________________________Página 15 de 53 Excel 2007

Excel 2007

3 – Para inserir novos dados deve-se clicar em novo, e escrever o que se deseja, para mudar de campo é necessário a utilização da tecla “TAB”, para efetivar a inserção basta apertar a tecla “enter”. • Plano de fundo Para fins de personalização da planilha podemos alterar seu plano de fundo, e para isso temos que clicar na guia “Layout da Página”, “Plano de fundo”, então deve-se selecionar a figura desejada e clicar em “OK”. Ainda nesta guia temos os itens “Linhas de Grade” e “Títulos”, sendo que se desabilitados retiram as linhas de grade do Excel e as letras e números indicadores do endereço da célula.

Para uma formatação mais rápida, podemos selecionar a área a ser modificada e clicar na mesma com o botão direito, uma barra de menus surgirá, então deve-se selecionar “Formatar células...”, uma janela, como se segue abaixo surgirá:

Nesta janela podemos configurar nossa planilha para trabalhar de forma a facilitar nossos cálculos e fórmulas, podemos alterar as grades, fontes, tamanho, cor da letra, cor da célula, etc. Esta será muito utilizada durante toda a apostila e no cotidiano do trabalho.

________________________________Página 16 de 53 Excel 2007

Excel 2007

1.2 Uso da função soma, multiplicação, divisão, subtração e potenciação

1.3 Copiar fórmulas absolutas e relativas

________________________________Página 17 de 53 Excel 2007

Excel 2007

Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim quando copiamos esta célula para baixo ou para o lado no Excel, A1 permanecerá fixo. Quando precisamos de copiar somente os valores, e o fazemos selecionando os dados, clicando em “copiar” na guia acionada com o botão direito e depois clicamos em colar, os itens se desconfiguram por completo, desde que sejam oriundos de fórmulas, isto porque o Excel copia somente as fórmulas e não os dados, assim o mesmo perde a referência. Para sanar este problema podemos colocar “$” em todas as variáveis, ou copiar normalmente através do menu acionado com o botão direito, e quando a colar devemos escolher o item “Colar especial”, e depois “Valores” finalizando com OK.

________________________________Página 18 de 53 Excel 2007

Excel 2007

1.4 Função SE (Fórmulas condicionais)

________________________________Página 19 de 53 Excel 2007

Excel 2007

________________________________Página 20 de 53 Excel 2007

Excel 2007

Todas as demais fórmulas condicionais fazem-se da mesma forma, abaixo seguem mais alguns exemplos: • SOMASE = Soma as células determinadas por um critério ou condição. A fórmula =SOMASE(H2:H12;">10"), adiciona os valores contidos no intervalo de H2 a H12, porém somente os valores maiores que 10. • CONT.SE = Conta o número de células não vazias que atendem a uma determinada condição. A fórmula =CONT.SE(G2:G12;"=v"), conta todas as células que contém o valor “v” no intervalo de G2 a G12. O valor pode ser substituído por palavras ou números. • CONTAR.VAZIO = Conta um número de células vazias em determinado intervalo. A fórmula =contar.vazio(A2:A20), conta as células vazias existentes no intervalo de A2 a A20. Devemos tomar certo cuidado para não selecionar células indesejadas, pois estas podem atrelar um resultado errôneo a tabela. Esta função é bastante utilizada com as funções, concatenar, procv e proch, as quais veremos mais adiante. • MÉDIA = Calcula a média de determinado intervalo. Uma célula que apresente a fórmula =MÉDIA(H2:H12), soma os valores do intervalo H2 a H12 e divide o resultado por 11, obtendo assim a média simples. Comumente utilizamos esta função em planilhas que determinam desvio padrão, variância, etc. • MÁXIMO = Retorna o valor máximo de um conjunto de argumentos. Se tenho em uma célula a fórmula =máximo(A1;A2;A3;A4;A5), esta nos dará o valor numérico mais alto que encontrar no intervalo. • MÍNIMO = Retorna o valor mínimo de um conjunto de argumentos. Assim como o MÁXIMO, esta função também nos mostra um valor numérico, mas sendo este o menor encontrado no intervalo.

EXERCÍCIOS Confeccione a tabela abaixo utilizando os recursos vistos até o momento, sendo que quaisquer valores alterados a planilha se atualiza.

________________________________Página 21 de 53 Excel 2007

Excel 2007

Confeccione a tabela abaixo utilizando os recursos disponíveis, sabendo que: 1 – Total = Soma das Notas 2 – Média = Média das notas 3 – Resultado = Se o aluno têm média acima de 6, deve-se exibir “APR”, se possui média menor que 40, deve-se exibir “REP” em cor vermelha, e se a média da nota estiver entre 40 e 60, deve-se escrever “REC” 4 – Prêmio – O programa deve escrever ótimo para o aluno que obteve média entre 8,5 e 9,99999. 5 – Em “Alunos aprovados”, “Alunos de recuperação” e “Alunos reprovados”, deve-se exibir o número de alunos em cada situação. 6 – Formate a planilha de forma a parecer mais organizada.

________________________________Página 22 de 53 Excel 2007

Excel 2007

2 BANCO DE DADOS E TRATAMENTO DE DADOS 2.1 Banco de dados

2.2 Vínculos com planilhas externas e arquivos

________________________________Página 23 de 53 Excel 2007

Excel 2007

2.3 Função PROCV A função PROCV é utilizada para buscar informações automaticamente em um banco de dados tomando uma referência primária. Para facilitar nossa compreensão, abra um documento novo do Microsoft Excel 2007, e crie, em Plan2, o banco de dados abaixo.

Vamos então confeccionar agora em Plan1 um cadastro de pedidos onde ao digitarmos o Modelo, a planilha automaticamente buscará os dados de Descrição, Cor e Valor, ficando apenas o item QTD (quantidade) para ser preenchido, já que podemos também automatizar o item “Valor Total”. Segue exemplo:

________________________________Página 24 de 53 Excel 2007

Excel 2007

Buscamos então as informações na primeira planilha e completamos a segunda automaticamente, ou seja, toda vez que digitarmos um valor na guia modelo do cadastro de pedidos, os valores serão buscados no banco de dados, para tanto, utilizamos a função PROCV. PROC significa procurar, e o V significa vertical, ou melhor, é uma função que procura valores em colunas, podemos também ter o PROCH, que procura valores nas linhas, (PROC = procura, H = horizontal). O desenvolvimento da função dá-se da seguinte maneira: =PROCV(Valor procurado;Tabela Matriz;Número índice de coluna;Critério) Sendo: • Valor procurado = é o valor que queremos comparar, no nosso caso os dados contidos em Modelo. • Tabela matriz = é o banco de dados de onde retiraremos as informações, que no exemplo é em Plan2!A2:D10 • Número índice de coluna = é um número que indica qual informação buscar no banco de dados, no nosso estudo, por exemplo, temos uma área de busca A2:D10, a coluna “A” corresponde ao número índice 1, a coluna “B” corresponde ao número índice 2, e assim sucessivamente. • Critério = 0 (exato) ou 1 (parecido) serve para que o Excel busque informações exatas ou parecidas. Então nossa fórmula no cadastro de pedidos na célula B2 ficou da seguinte forma: =PROCV(A2;Plan2!A2:D10;2;0) Ao arrastarmos esta fórmula para baixo e para o lado, devemos tomar cuidado com as adições de endereços indevidas, ou seja, se levarmos esta fórmula até B3, a mesma ficará assim: =PROCV(A3;Plan2!A3:D11;2;0) Note que o banco de dados mudou também seu valor, e isto pode gerar erros. Então faz-se necessário travar o endereço, ficando conforme exemplo abaixo: =PROCV(A3;Plan2!$A$2:$D$11;2;0) Tendo:

________________________________Página 25 de 53 Excel 2007

Excel 2007

Exemplo das fórmulas:

Note ainda que um erro ocorreu, pois, se não lançarmos informação nenhuma em modelo aparece #N/D na célula. EXERCÍCIO – Desenvolva uma maneira de sanar o erro citado acima e demais, com o que já aprendemos, pode-se usar fórmulas para que o cadastro de pedidos aparente o exemplo abaixo: ____________________________________________________________________ ____________________________________________________________________ ____________________________________________________________________ ____________________________________________________________________ ____________________________________________________________________ ____________________________________________________________________

CONCATENAR A função concatenar é utilizada para “ajuntar” valores, para formar um terceiro valor, como segue o exemplo. Imaginemos que em uma fábrica de sapatos existam 4 tipos de sapatos diferentes, A, B, C e D, cada tipo em 2 tamanhos 38 e 41. Foi necessário a criação de um código para nomear estes sapatos, então decidiu-se que o código seria a junção do modelo e o tamanho. Utilizaremos comando concatenar para solucionar este problema, assim como mostra a figura abaixo:

________________________________Página 26 de 53 Excel 2007

Excel 2007

A função concatenar também pode ser representada pelo símbolo “&”, então, como ilustro o exemplo, nas células da coluna C, ao invés de utilizar a fórmula =concatenar(A2;B2), poderíamos simplesmente escrever =A2&B2.

PROCV E CONCATENAR Imaginemos que na fabrica de sapatos do exemplo anterior o banco de dados fosse o seguinte.

Banco de dados criado em Plan4

E foi criado uma pequena planilha de consulta, da seguinte forma – O vendedor escreve o modelo e o tamanho do sapato desejado e o programa busca por informações no banco de dados e retorna a cor e o preço do item em pesquisa. Note que neste caso fica mais fácil buscar a informação desejada utilizando o comando concatenar adicionado do procv, da maneira descrita logo abaixo:

Na célula C2  =PROCV(A2&B2;Plan4!A1:E9;2;0) e na célula D2 temos  =PROCV(A2&B2;Plan4!A1:E9;5;0) PROTEGENDO CÉLULAS 1.

Selecione a planilha que deseja proteger.

2.

Para desbloquear células ou intervalos que deseja liberar para outros

usuários alterarem, faça o seguinte: 1. Selecione cada célula ou intervalo que deseja desbloquear. 2. Na guia Início, no grupo Células, clique em Formatar e, em seguida, clique em Formatar Células.

________________________________Página 27 de 53 Excel 2007

Excel 2007

3.

Na guia Proteção, limpe a caixa de seleção Bloqueada e clique em OK.

4.

Para ocultar fórmulas que você não deseja que fiquem visíveis, faça o

seguinte: 5.

Na planilha, selecione as células que contêm as fórmulas que deseja

ocultar. 6.

Na guia Início, no grupo Células, clique em Formatar e, em seguida, clique

em Formatar Células. 7.

Na guia Proteção, marque a caixa de seleção Oculta e clique em OK.

8.

Na guia Revisão, no grupo Alterações, clique em Proteger Planilha.

9.

Na lista Permitir a todos os usuários desta pasta de trabalho, selecione os

elementos que você deseja que os usuários possam alterar. Nos passos acima protegemos células para que usuários não possam fazer modificações nas mesmas, como por exemplo em fórmulas. É de extrema importância desbloquear as celular que os usuários podem modificar, e travar e até mesmo ocultar as fórmulas.

________________________________Página 28 de 53 Excel 2007

Excel 2007

EXERCÍCIO Proteja as células e fórmulas na planilha “Cadastro de pedidos” feita nos exemplos anteriores, liberando somente as células de utilização dos usuários.

VALIDAÇÃO DE DADOS Na GUIA DADOS, entre em validação de dados, a seguinte janela deverá aparecer. Neste item podemos definir alguns padrões para as células, como por exemplo impedir que o usuário digite números fracionários, o número mínimo e o máximo de letras

que

podemos

se

pode

permitir

escrever,

somente

a

inserção de datas ou horas, e até definir um pequeno menu com as opções que existem. Imaginemos que na célula L7 queremos somente digitar um valor superior a 100, então devemos selecionar “Personalizado” e inserir a fórmula abaixo no campo.

Podemos ainda inserir uma lista para que o usuário possa selecionar somente itens pré-definidos, fazemos isto clicando na guia “Lista”, e inserindo os valores que desejamos que apareçam no menu no campo denominado fonte.

________________________________Página 29 de 53 Excel 2007

Excel 2007

Neste campo “Fonte” devemos simplesmente colocar as condições separadas por ponto e vírgula. Na guia “Mensagem de entrada” podemos definir uma frase que o usuário lerá quando selecionar a célula. Na guia “Alerta de erro” podemos definir ícones e frases após uma tentativa de inserção de dados ilegais. EXERCÍCIO 1 - Ainda sobre a planilha “Cadastro de pedidos” faça com que seja impossível lançar um valor errado na coluna modelo. 2 – Siga as instruções abaixo: • Crie o banco de dados abaixo em uma planilha em branco e salve no seu PEN-DRIVE com o nome de BD.

•

Crie agora um outro documento do Excel chamado Pedido de compra, e o salve em seu PEN-DRIVE. o Faça o seguinte documento seguindo as especificações abaixo.  Em Pedido número de aparecer um código que seja a junção do total da compra com a data e hora atual, o formato da data e hora no número do pedido deve ser DDMMAAAAHHMM.  Assim que inserirmos um código de produto na coluna código, este mesmo deve buscar os demais itens em “BD”, sendo que o usuário pode somente lançar dados em “Código” e “QTD”, todos os outros campos devem ser travados e com fórmulas ocultas. O programa deve dar menus de escolha aos usuários.  Se o total de venda for até R$500,00 o frete é de 10% do total, se as vendas forem de R$500,00 até R$1000,00 o frete é de 5%

________________________________Página 30 de 53 Excel 2007

Excel 2007

 

do valor total e se a compra for acima de R$1000,00 o frete é grátis. Em compras acima de R$1500,00 o desconto é de 7,5%. A data da entrega deve ser dez dias após a data do pedido.

________________________________Página 31 de 53 Excel 2007

Excel 2007

FILTROS Utilizamos o filtro para listar dados de uma determinada família, facilitando a compreensão. Vejamos o exemplo abaixo: Imaginemos que precisamos mostrar na tela apenas as informações do cliente Beira Rio. Faz-se então necessário selecionar a GUIA DADOS,e selecionar a ferramenta filtro: Surgirão então nas células referentes aos títulos algumas guias drop down, que quando acionadas nos mostram o conteúdo das células daquela coluna, então podemos selecionar o item desejado e trabalhar com o mesmo.

Devemos notar que surge no botão drop down um símbolo de filtro, o que indica que aquele campo já foi filtrado. É possível ainda personalizar o filtro, ou melhor, utilizar o filtro avançado. Com este recurso podemos, por exemplo, inserir um critério para que o Excel nos retorne um valor. Para tanto é necessário fazer o seguinte: Imaginemos que no banco de dados acima desejamos filtrar somente os “Totais” que forem acima de 10000,00. Primeiro temos que criar em uma célula qualquer em branco o (s) critério (s) que desejamos, e logo após, clicar em Avançado, que se localiza também na GUIA DADOS logo ao lado do filtro. Uma pequena janela surgirá conforme ilustra a figura abaixo:

________________________________Página 32 de 53 Excel 2007

Excel 2007

Podemos nesta opção, obter um filtro local, mas isto, apagaria parcialmente o banco de dados atual, então pode-se selecionar a guia “Copiar para outro local”, que o Excel cria o filtro em local pré-selecionado. No campo “Intervalo da lista” selecionamos toda a área que desejamos que o filtro faça a busca, que neste caso foi todo o banco de dados. No campo “Intervalo de critérios” selecionamos as células que criamos como critério de busca, que no caso acima compreende a informação abaixo do banco de dados circundada pela elipse. Chamamos esta informação adicional de planilha de apoio, nela inserimos o título da guia que queremos filtrar e abaixo deste título o critério. No campo “Copiar para” selecionamos uma célula ou área de destino para as informações filtradas. Caso existam informações em duplicatas podemos selecionar a guia “Somente registros exclusivos”, sendo que este nos dará somente dados não repetidos. Clicando finalmente em OK, obteremos os dados abaixo:

CLASSIFICAÇÃO DO BANCO DE DADOS É comum que um banco de dados seja organizado por algum parâmetro, ou por algum campo especifico, como por exemplo por ordem crescente de cliente. Na GUIA DADOS encontramos a ferramenta classificar, que pode ser utilizada para ordenar os dados de várias formas diferentes em um banco de dados, dependendo é claro, dos critérios utilizados. Para tanto devemos selecionar os dados a serem classificados ou clicar em uma das células do banco de dados, pois assim que clicarmos em classificar o Excel selecionará automaticamente o Banco de informações, como segue o exemplo abaixo:

________________________________Página 33 de 53 Excel 2007

Excel 2007

A janela acima deverá aparecer quando a ferramenta “Classificar” for acionada, nota-se que no campo “Classificar por” podemos selecionar qualquer título, classificar por valores, ou formatação e colocar em ordem crescente ou decrescente. Nas versões mais antigas do Excel podíamos realizar uma classificação somente até três critérios, nesta versão podemos definir quantas classificações forem necessárias, basta clicar no botão “Adicionar nível”, se não quisermos mais determinado critério podemos excluí-lo, selecionando-o e clicando em “Excluir nível” . No botão “Opções” podemos escolher se queremos diferenciar letras maiúsculas de minúsculas e se a classificação deve ser vertical ou horizontal.

SUBTOTAIS Na GUIA DADOS,e ferramenta “Subtotal”, é possível subtotalizar um banco de dados desde que este esteja classificado corretamente. Por exemplo, se vamos subtotalizar por cliente, o banco de dados deve primeiro estar classificado por cliente, caso contrário não irá funcionar. Se fizer um subtotal por cliente, quando o Excel encontrar um cliente diferente, ele subtotaliza o cliente anterior, e assim sucessivamente. Vejamos o exemplo:

________________________________Página 34 de 53 Excel 2007

Excel 2007

Podemos observar que a coluna “Cliente” está classificada, então atrelamos o subtotal, informando ao Excel que a cada alteração de cliente, ele deve gerar um resultado parcial de soma dos valores. Podemos adicionar um subtotal a todos os títulos, conforme a necessidade. Ao clicar em OK, obtivemos a tabela ao lado. Nela temos a cada cliente um resultado parcial daquele mesmo, e ao final o total geral. Repare ainda que no canto esquerdo da tabela temos os números 1, 2 e 3, que compreendem a forma de ver a tabela, ou seja, ao clicar no número 1 temos simplesmente os títulos da planilha e o “Total Geral”, ao clicar no número 2 encontraremos uma tabela reduzida com os totais de cada cliente e o total geral, e já se clicarmos no botão 3, teremos a tabela com os resultados parciais, totais e total geral, conforme ilustra a figura a lado. Para que retornemos a planilha como era anteriormente ao subtotal, podemos clicar novamente no ícone da ferramenta citada e quando a janela aparecer novamente, clicamos em “REMOVER TODOS”, desta forma retiramos todos os subtotais e a planilha volta ao “normal”. TABELA DINÂMICA A tabela dinâmica é bastante útil quando queremos analisar dados em uma estrutura diferente da que temos no banco de dados. Por exemplo, para uma planilha de contas a pagar como o exemplo abaixo, onde as informações estão lançadas uma

________________________________Página 35 de 53 Excel 2007

Excel 2007

abaixo da outra, e queremos visualizá-la por colunas, então podemos utilizar a tabela dinâmica, na GUIA INSERIR.

Ao clicar no ícone correspondente a janela acima deverá surgir, e devemos selecionar a área, ou intervalo das informações que queremos visualizar, podemos ainda obter uma fonte de dados externa, escolhendo uma conexão na rede. Pode-se também nesta janela definir se a tabela dinâmica será criada em um novo arquivo novo do Excel ou na mesma pasta de trabalho.

Uma lista semelhante a figura acima faz-se real quando clicamos em OK, na guia a direita podemos selecionar quais itens desejamos visualizar na tabela dinâmica, sendo que os mesmos ficam dispostos nos quatro campos localizados no lado inferior direito da tela.

________________________________Página 36 de 53 Excel 2007

Excel 2007

Podemos clicar sobre as guias drop down nos campos inferiores e formatar a composição da tabela, ao clicar com o botão direito um menu surge, e o mesmo nos dá opções para movimentar os dados. Na tabela dinâmica, em rótulos de linha temos um filtro para uma eventual necessidade de filtrar a informação, e se quisermos formatar a mesma, e a lista de campos da tabela dinâmica não estiver mais presente, podemos clicar em qualquer parte da planilha que esta re-aparecerá.

No exemplo acima modificou-se a tabela dinâmica para que apresentasse a “Data Pgto” como rótulo de coluna.

________________________________Página 37 de 53 Excel 2007

Excel 2007

MACROS Imaginemos que em uma planilha seja necessário executar uma ação várias vezes, por exemplo copiar um dado, gerar um relatório, etc. Podemos então utilizar a macro, que por sua vez grava todas as ações que fazemos no Excel em determinado período, sendo que podemos executá-la sempre que necessário. Para tanto devemos clicar na GUIA EXIBIÇÃO, ao final da barra de ferramentas encontraremos o ícone “Macros”, se acionado o programa nos exibirá a lista de macros já existentes no computador, sendo que podemos editar, criar ou excluir uma macro, porém para criar uma macro neste ambiente as ferramentas disponíveis são em Visual Basic, os quais não abordaremos aqui. Então podemos clicar na caixa drop down abaixo do referido ícone, onde inicialmente, teremos duas opções: Exibir macros e Gravar macros... Podemos então selecionar “Gravar Macro...” Primeiramente definimos um nome para a macro. Podemos ainda definir uma hotkey, ou tecla de atalho para a macro, que sempre é acompanhada de “Ctrl”. Na guia “Armazenar macro em:” podemos definir aonde queremos guardar esta macro, que geralmente é na mesma pasta de trabalho como mostra o exemplo ao lado. Em “Descrição” podemos descrever as ações da macro, para facilitar a identificação. Ao clicar em OK, a macro já começa a ser gravada, então basta executarmos manualmente as ações que queremos que o Excel execute sozinho, que o mesmo começa a armazenar todas estas informações. Assim que finalizarmos as ações atribuídas a macro, podemos clicar na caixa drop down abaixo do ícone Macro e clicar em “Parar Gravação”. Para executar as macros podemos clicar no ícone Macro, sendo que a seguinte exibirá a seguinte janela: Basta selecionarmos a macro e clicar em “Executar” para realizarmos a tarefa gravada a macro. No botão “Depurar” podemos observar o código fonte da macro, exibido em VB (Visual Basic), em “Editar” podemos re-configurar a macro porém também em VB. Podemos ainda excluir uma macro, selecionando-a e clicando em “Excluir”. No botão “Opções” podemos atribuir uma tecla de atalho para a macro e inserir uma descrição para a mesma.

________________________________Página 38 de 53 Excel 2007

Excel 2007

Caso queira que a Macro seja executada quando a planilha for aberta, devese atrelar o nome macro em auto_open. Existem alguns problemas que podem ocorrer quando tentamos salvar o arquivo, pois agora temos uma macro para executar e é necessário então salvar o programa selecionando um tipo de arquivo habilitado para macro, e podemos escolher esta opção na janela referente ao salvamento no campo “tipo”. Pode ainda ocorrer que o Excel ao reabrir o documento com macro e tentar executar a mesma não o faça, nos mostrando a seguinte mensagem:

O que significa que para a segurança do arquivo o Excel desabilitou as macros para que uma pessoa mal intencionada ou desinformada modifique ou execute macros indevidas. Para permitir que as macros estejam sempre aptas ao funcionamento devemos clicar no ícone do Office, depois em “Opções do Excel”, posteriormente na guia “Central de confiabilidade” e por fim no botão de “Configurações da central de confiabilidade”. Após este processo devemos selecionar a sub-guia “Opções de macros”, conforme ilustra a figura abaixo, selecionando finalmente a habilitação de macros e clicando em OK.

INSERINDO BOTÕES Se necessitamos de executar uma macro sempre, fica mais trabalhoso clicar no ícone e realizar a tarefa. Podemos então inserir botões no Excel e atrelá-los a alguma ação específica ou propriamente dito, uma macro. Portanto necessitamos de inserir a ferramenta de inserção de botões em nossa barra de ferramentas. Esta ferramenta pode ser encontrada na GUIA DESENVOLVEDOR, mas existe uma maneira mais fácil de localizar esta ferramenta. Clique no ícone do Office, no canto esquerdo superior, depois clique em opções do Excel, posteriormente clicando em “Personalizar”. Feito isto, selecione a ferramenta “Botão (controle de formulário)”, adicione o mesmo na barra de acesso rápido, agora um ícone da respectiva ferramenta deve aparecer na barro de acesso rápido. Para inserir um botão devemos clicar na ferramenta citada, clicar e arrastar na área que queremos inserir o mesmo, assim que soltarmos o botão do mouse uma janela aparecerá, conforme ilustra a figura abaixo:

________________________________Página 39 de 53 Excel 2007

Excel 2007

Esta é a janela de atribuição de macro, nela nos adicionamos uma macro previamente criada ao botão que estamos inserindo, como exemplo, temos a macro “limpa”, para adicioná-la ao “botão 6” ao lado, devemos selecionar respectiva macro e clicar em “OK”, nesta janela podemos ainda criar novas macros, e definir os arquivos que esta macro estará inserida no campo “Macros em:”. Podemos também formatar o botão que acabamos de inserir, formatando seu nome, clicamos com o botão direito do mouse sobre o botão, selecionamos seu texto, clicando com o direito novamente e selecionando em um pequeno menu que surgirá o item “Formatar controle”. GRAFICOS Podemos criar gráficos para que as informações fiquem mais fáceis de compreender, cliquemos então na GUIA INSERIR, nela notaremos que temos vários tipos de gráficos, como colunas, linhas, pizza, barra, área, dispersão, etc. Para criar no Excel um gráfico básico que você possa modificar e formatar posteriormente, comece inserindo os dados do gráfico em uma planilha. Em seguida, basta selecionar esses dados e escolher o tipo de gráfico que deseja utilizar na Faixa de Opções da barra “Gráficos”.

O interessante desta versão inserirmos um gráfico, ou este

________________________________Página 40 de 53 Excel 2007

do Excel é que ao ser selecionado

Excel 2007

surge uma barra de ferramentas bastante completa, conforme podemos ver a ilustração abaixo:

Nestas guias de tratamentos de planilhas podemos modificar facilmente o Layout dos gráficos, ou seja podemos inserir títulos de gráficos e eixos, legenda, rótulos de dados, cores, tipos de gráficos, etc. Informação também interessante deste gráfico é que podemos inserir linhas de tendência, no caso dos gráficos de linha, e ainda os respectivos erros e desvio padrão.

Resumindo, na caixa de ferramentas de gráfico podemos, através das três abas (Design, Layout e formatar) modificar QUALQUER item do gráfico. Os dois exemplos de gráficos acima são extremamente simples, mas utilizando estas três uteis ferramentas já citadas podemos profissionalizar uma planilha e principalmente gráficos.

________________________________Página 41 de 53 Excel 2007

Excel 2007

Para formatar determinada área do gráfico necessitamos selecionar esta área no campo “Área do gráfico” na figura acima. Após formatar o gráfico podemos salvar a configuração que fizemos, isto é, para evitar de o tempo todo re-configurar gráficos, salvamos o nosso modelo personalizado e o utilizamos sempre que necessário. Primeiramente, deve-se formatar o gráfico de acordo com a utilização, uma vez que o gráfico estiver pronto podemos clicar na guia “Design” e em seguida clicar em “Salvar como modelo”, escolher um nome para este layout e clicar em OK. Desta forma quando fizermos um gráfico e necessitarmos de uma configuração padrão, podemos, ainda na aba “Design” clicar em “Alterar tipo de gráfico”, selecionando o modelo salvo desejado. EXERCÍCIOS 1 - Na planilha de controle semanal de massas, crie alguns botões que assim que acionados, gerem os respectivos gráficos, cada um em uma “plan” diferente, referentes a produção semanal de massas de todas as linhas. Faça o mesmo processo para os resultados gerais da produção, mas este deve ser um gráfico com indicadores de desvio padrão. O modelo dos gráficos podem ser escolhidos a vontade, mas todos devem ter todos os títulos. 2 – Na planilha pedido de compras ainda temos um problema, sempre que realizamos um pedido, têm-se que apagar todos os dados antigos manualmente, crie uma forma simples de apagar estes dados. 3 – Monte a planilha abaixo em um documento em branco. - Complete a coluna “Total” - Crie, se possível em outra planilha um subtotal desta planilha, classificada por fornecedor. - Aplique um tipo de filtro que me mostre apenas as compras acima de $160,00. - Crie um gráfico para que tenhamos feedback visual dos nossos maiores fornecedores, crie um gráfico tipo pizza 3d. Seria possível criar macros para realizar estas tarefas? Se sim, execute.

________________________________Página 42 de 53 Excel 2007

Excel 2007

FUNÇÕES AVANÇADAS DATAS E HORAS =DIAS360(data inicial;data final) A função DIAS360 retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). Use essa função para ajudar no cálculo de pagamentos, se o seu sistema contábil estiver baseado em doze meses de 30 dias. =DIATRABALHOTOTAL(data inicial;data final;[feriados]) Retorna o número de dias úteis inteiros entre data_inicial e data_final. Os dias úteis excluem os fins de semana e quaisquer datas identificadas em feriados. Use DIATRABALHOTOTAL para calcular os benefícios aos empregados que recebem com base no número de dias trabalhados durante um período específico. =DIATRABALHO(data inicial;dias;feriados) Retorna um número que representa uma data que é o número indicado de dias úteis antes ou após uma data (a data inicial). Os dias úteis excluem fins de semana e quaisquer datas identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura, horas de entrega esperadas ou o número de dias de trabalho executado. =AGORA() Retorna a data e a hora atuais. =HOJE() Retorna somente a data atual. FUNÇÕES FINACEIRAS O Excel possui mais de 50 fórmulas financeiras, veremos abaixo as fórmulas mais utilizadas. =PGTO(taxa;nper;vp;vf;tipo) Retorna o valor a ser pago mensalmente considerando os critérios abaixo. • Taxa é a taxa de juros por período (mensal). • Nper é o número total de pagamentos, ou período. • Vp é o valor presente — o valor total presente de uma série de pagamentos futuros. • Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). • Tipo é o número 0 ou 1 e indica as datas de vencimento, se “0” ou não especificado utiliza o final do período para calcular a quantia final, e se “1” calcula os valores considerando o início do período.

________________________________Página 43 de 53 Excel 2007

Excel 2007

Imagine que fizemos um empréstimo de R$10000,00, e queremos saber quanto pagaremos por mês. Sabemos que a taxa de juros é de 8% ao ano, e que pagaremos este valor em 10 meses. Podemos proceder da seguinte forma. Dividimos a taxa por doze para obter o valor mensal de juros, o resultado é mostrado como negativo porque o Excel entende que como o valor é um pagamento, este mesmo está sendo decrescido de nossa receita.

EXERCÍCIO Queremos R$50000,00, e utilizaremos uma poupança onde o rendimento é de 6% AA, e depositaremos esta quantia por 18 anos. Quanto devemos depositar por mês nesta poupança para obter o valor pretendido? R:_____________________________ =VF(taxa;nper;pgto;vp;tipo) Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante. • Taxa é a taxa de juros por período. • Nper é o número total de períodos de pagamento em uma anuidade. • Pgto é o pagamento feito a cada período; não pode mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá incluir o argumento vp. • Vp é o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do argumento pgto será obrigatória. • Tipo é o número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo for omitido, será considerado 0. Imaginemos que vamos iniciar uma poupança, sendo que a taxa de juros da mesma é de 6%AA, que nós depositaremos inicialmente R$500,00 e que a cada mês depositaremos mais R$200,00. Quanto teríamos ao final de 12 meses? =VF(6%/12;12;-200;-500;0) Colocamos então a fórmula com um sinal de – no pgto e vp, porque estes valores foram pagos e não recebidos. EXERCÍCIO 1 - Você está fazendo uma poupança para custear a faculdade do seu filho, o valor inicial é de R$0,00, você depositará R$100,00 mensais durante 18 anos. Sabendo que a taxa de rendimento da poupança é de 0,53%AM, quanto você terá ao final do período? R:______________________________

________________________________Página 44 de 53 Excel 2007

Excel 2007

2 – E se os valores fossem os mesmos, alterando apenas a taxa de juros para 11%AA? R:_______________________________ =TAXA(nper;pgto;vp;vf;tipo;estimativa) Retorna a taxa de juros por período de uma anuidade. TAXA é calculado por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!. • Nper é o número total de períodos de pagamento em uma anuidade. • Pgto é o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf. • Vp é o valor presente — o valor total correspondente ao valor atual de uma série de pagamentos futuros. • Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). • Tipo é o número 0 ou 1 e indica as datas de vencimento. • Estimativa é a sua estimativa para a taxa. Imaginemos que emprestamos R$8000,00, e que pagaremos este em 4 anos, sendo um pagamento fixo mensal de R$200,00. Queremos conhecer a taxa de juros, então faremos o seguinte. =TAXA(4*12;-200;8000)*12 Com a fórmula acima conhecemos então a taxa de juros anual, por isso multiplicamos a fórmula por 12. Se quisermos a taxa de juros mensal, não faz-se necessária esta multiplicação final. EXERCÍCIO - TESTE Vou comprar um carro financiando totalmente. O valor do veículo é de R$15000,00. A financeira “A” ofereceu-me 36 parcelas de R$491,08, e a financeira “B” ofereceu-me 48 parcelas de R$409,90. As duas financeiras garantiram que a taxa de juros é fixa independentemente do período. Eu quero pagar em 30 meses. Em qual financeira é mais compensatório o negócio? R:____________________________ =NPER(taxa;pgto;vp;vf;tipo) Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. • Taxa é a taxa de juros por período. • Pgto é o pagamento feito em cada período; não pode mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxas. • Vp é o valor presente ou atual de uma série de pagamentos futuros. • Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0).

________________________________Página 45 de 53 Excel 2007

Excel 2007

• Tipo é o número 0 ou 1 e indica as datas de vencimento. Imagine que se queira ajuntar R$10000,00, temos inicialmente R$1000,00 e por mês depositaremos mais R$100,00 a uma taxa de juros de 8%AA. Por quanto tempo deveremos depositar este dinheiro para obter o objetivo. =NPER(8%/12;-100;-1000;10000) Inserimos o sinal negativo a frente de Vp e Vf pois são valores que devem ser considerados como gastos. EXERCICIOS Determine o período de um investimento de R$150,00 mensais, tendo um valor inicial de R$0,00 e uma taxa de juros de 12%AA. Sabe-se ainda que o valor final do investimento deve ser de R$18000,00. R:_______________________________ FUNÇÕES ESTATÍSCAS =VAR(núm1;núm2;...) Estima a variância com base em uma amostra. =VAR(123;124;123;125;130;132;120)  17,9

=DESVPAD(núm1;núm2;...) Calcula o desvio padrão a partir de uma amostra. O desvio padrão é uma medida do grau de dispersão dos valores em relação ao valor médio (a média). Núm1;núm2;... são de 1 a 255 argumentos numéricos correspondentes a uma amostra de população. Você também pode usar uma única matriz ou uma referência a uma matriz em vez de argumentos separados por ponto-e-vírgula. Imaginemos o quadro abaixo com a relação de produção diária de latas:

Calculamos o desvio inserindo =desvpad(B2:G2), assim o Excel nos retorna o valor referido. FUNÇÕES DIVERSAS ARRED E TRUNCAR A função ARRED arredonda o resultado de um cálculo para quantas casas for necessário. Basta incluir na fórmula esta função, observe abaixo: =ARRED(B5*A2;3) Esta fórmula significa que os valores das células B5 e A2 serão multiplicados, porem o resultado somente nos dará valores com três casas decimais.

________________________________Página 46 de 53 Excel 2007

Excel 2007

A função TRUNCAR é semelhante ao ARRED, porém ao passo que o arred arredonda os valores o truncar simplesmente ignora os valores, realizando o cálculo desta forma, como segue o exemplo: =TRUNCAR(A5+B2;2) Na condição acima a função somaria A5 e B2, feito isso nos daria um resultado somente com duas casas decimais. O problema é que em valores que necessitam de grande precisão, um erro grosseiro poderia ser gerado. EXERCÍCIO 1 - Crie uma planilha com formatação própria, mas que exiba campos para o usuário imputar Taxa de juros, mensalidade valor inicial e mensalidade. Esta planilha deve indicar o valor futuro. A guia taxa de juros deve conter uma lista drop down que contenha os valores (6%, 8%, 10%, 12% e 14%) ao ano. Também em forma de lista devemos ter o período em 6, 12, 24, 36, 48, 60, 84 e 100 meses. Deve haver um botão para limpar a pesquisa. OBS.: todas as células devem trabalhar com duas casas decimais. 2 – Crie uma planilha que resolva sozinha o problema exposto em EXERCÍCIO – TESTE na página 45.

________________________________Página 47 de 53 Excel 2007

Excel 2007

ATINGIR META Esta função é muito útil quando desejamos saber qual o valor inicial ideal para obtermos um resultado já definido, considerando vários critérios. Crie em uma planilha o exemplo ao lado. O item IR deve ser 7,5% do salário nominal. O item INSS deve ser 5% do salário nominal e o item “Convênio médico” deve ser um valor fixo. Notamos que o salário líquido é o valor do salário nominal decrescido dos respectivos descontos. Então pergunta-se, “Para um salário líquido de R$1200,00, qual deveria ser o salário nominal?” Para tanto devemos clicar na GUIA DADOS e posteriormente em teste de hipóteses. Surgirá então um menu com três opções, selecione a opção “Atingir meta...”. Uma pequena janela como a da figura ao lado deve surgir, nela temos o campo, “Definir célula”, no qual devemos colocar o endereço da célula que queremos modificar o valor, que no exemplo acima seria “B5”, é normal que ao clicar na célula o Excel a trave utilizando $. Depois precisamos informar qual seria o valor desejado, por isso colocamos 1200 no campo “Para valor”, e por fim devemos indicar ao atingir meta qual célula deve variar para este valor de 1200, que este caso é a célula “B1”. Clicamos então em OK. Neste ponto o Excel calculará a quantia inicial e nos dará o resultado final considerando as variações, exibindo desta forma outra janela, nos mostrando o status da ferramenta, em casos de planilhas muito grandes onde a ferramenta precisa analisar muitos dados é comum levar-se alguns segundos para a finalização do cálculo. Podemos agora clicar em OK, que nossa planilha manterá os dados determinados na tela. Nos itens “Tabela de dados...” e “Gerenciador de cenários...” que também encontramos em teste de hipóteses pode fazer-se o cálculo contrário do atingir meta, ou seja, enquanto o atingir meta utiliza um resultado para chegar a um valor primário, as outras duas ferramentas citadas fazem checar valores de entrada para chegar a um resultado, exatamente como uma fórmula convencional faz.

________________________________Página 48 de 53 Excel 2007

Excel 2007

SOLVER Primeiramente devemos inserir a ferramenta clicando no ícone do Office, no topo esquerdo da tela, depois em “Opções do Excel” selecionando a guia “SUPLEMENTOS”, neste ponto surgirá uma lista onde deve-se localizar e selecionar o item “Solver”, clica-se então em “Ir”. Uma janela como a ao lado deverá surgir, selecione a ferramenta solver e clique em “OK”. O ícone Solver aparecerá na GUIA DADOS.

Esta ferramenta é um atingir metas mais avançado. Imaginem que para o exemplo anterior (atingir metas) onde somente temos uma opção de célula variável, no solver é bem mais completo. Tomemos como princípio, ainda no exemplo anterior que o salário líquido deverá ser R$1200,00, mas o IR não pode ser maior que R$80,00. Com a ferramenta solver é possível resolver este problema. Observe o exemplo abaixo:

• • •

Célula de destino = deve ser a célula onde se quer aplicar a solução. Valor de = é o valor que desejamos no Salário líquido. Células variáveis = São IR, INSS e Convênio médico, pode-se selecionar o intervalo. É necessário sempre selecionar TODAS as variáveis, pois o solver faz alguns ajustes nos demais valores onde não existem restrições.

________________________________Página 49 de 53 Excel 2007

Excel 2007

•

Submeter as restrições = Para inserirmos as restrições temos que clicar em adicionar, uma janela surgirá.

Podemos clicar na célula que queremos submeter a restrição, e inserir os respectivos dados, clicando em OK. Se quisermos retirar uma restrição devemos selecionar a mesma e clicar em excluir. Assim que acionamos OK ao Adicionar Restrição podemos visualizar novamente a janela de “Parâmetros do Solver”, se as informações estiverem corretas podemos clicar em “Resolver”. O Excel solucionará o problema e nos dará a janela abaixo, onde podemos manter a solução do solver ou voltar a tabela aos valores originais. Podemos ainda selecionar relatórios para exibição de resultados, selecionando-os no campo “Relatórios” e clicando em OK.

Note nos resultados que para manter o IR de acordo com a restrição o solver ajustou INSS e Convênio médico. Vejamos um outro exemplo: • Uma empresa pode fabricar dois produtos (A e B). • Na fabricação do produto 1 a empresa gasta nove horas-homem e três horasmáquina. • Na fabricação do produto 2 a empresa gasta uma hora-homem e uma horamáquina. • A empresa dispõe de 18 horas-homem e 12 horas-máquina para um período de produção. • Sabe-se que os lucros líquidos dos produtos são $4 e $1 respectivamente. Tendo esta informação acima pergunta-se.

________________________________Página 50 de 53 Excel 2007

Excel 2007

Como podemos determinar a melhor maneira de produção, visando o maior lucro possível? Necessitamos construir uma planilha para melhor compreensão utilizando claro, o solver.

•

• • • •

•

Na planilha temos a coluna restrições, que servem somente para que tenhamos noção visual de que temos somente 18HH e 12HM. Ainda na planilha temos, ao lado de restrições uma coluna chamada LIMITE, nela temos as seguintes fórmulas =(G18*G20)+(H18*H20) e =(G19*G20)+ (H19*H20), que simplesmente são os valores máximos de tempo por produção. Por exemplo, multiplica-se a HH pelo total de produção do produto “A” e multiplica-se a HM também pelo total de produção de “A” Já na janela do solver, temos: Em definir destino lançamos o valor que queremos atingir, que no nosso caso é o total de lucro. Nesta célula nós colocamos uma fórmula =(G20*4)+ (H20*1), ou seja, multiplicamos a produção pelo valor do lucro. Selecionamos a guia “`Máx” para obtermos a maximização de nossa solução, que é o Total lucro. Valor pode ser zero, pois queremos o máximo e não um valor mínimo. Em células variáveis selecionamos as células que terão uma variação, que são neste caso, o total da produção. Estes valores aqui foram imputados manualmente, mas podem ser automatizados com a ajuda do procv, por exemplo. Adicionamos as restrições que temos que são um tempo limitado a 18 horas e outro limitado a 12 horas.

________________________________Página 51 de 53 Excel 2007

Excel 2007

•

Podemos ainda clicar em opções e selecionar as guias “Presumir modelo linear” para que o resultado seja linear e não logarítmico, e selecionamos também “Presumir não negativos” para que o programa não nos retorne um valor negativo. Clicamos então em OK

•

Ao clicar em OK o solver voltou a janela principal do mesmo, acionamos então RESOLVER, e a imagem abaixo surgirá.

Isto significa que para o lucro ser o maior possível devemos produzir uma peça do produto “A” e 9 peças do produto “B”, observando ainda que o LIMITE de tempo foi respeitado. EXERCÍCIO • A empresa JM fabrica vários produtos mas três deles não apresentam resultados produtivos satisfatórios. Precisamos então maximizar a produção. • Na fabricação de talharine gasta-se 3 horas-homem e 3 horas-máquina.

________________________________Página 52 de 53 Excel 2007

Excel 2007

• • •

Na fabricação de Espaguete gasta-se 2 hora-homem e 1 hora-máquina. Na fabricação de Parafuso gasta-se 1 hora-homem e 4 hora-máquina. A empresa dispõe de 30 horas-homem e 20 horas-máquina para um período de produção. • Sabe-se que os lucros líquidos dos produtos são R$10,00, R$8,00 e R$6,00 respectivamente. Tendo estas especificações de tempo, quanto deve-se produzir de cada massa para obter o MAIOR lucro possível? R:___________________________________________________________

________________________________Página 53 de 53 Excel 2007