Excel avancado 09_02_2013

Excel AvançadoErnane Rosa Martins

Técnico em Logística

e-Tec BrasilNome da Aula 3

Excel Avançado

Ernane Rosa Martins

Cuiabá - MT

2012

UFMT

Presidência da República Federativa do Brasil

Ministério da Educação

Secretaria de Educação Profissional e Tecnológica

Diretoria de Integração das Redes de Educação Profissional e Tecnológica

© Este caderno foi elaborado em parceria entre o Centro de Educação Profissional de Anápolis/GO, o Ministério da Educação e a Universidade Federal de Mato Grosso para a Rede e-Tec Brasil.

Universidade Federal de Mato Grosso – UFMT

Coordenação InstitucionalCarlos Rinaldi

Equipe de Elaboração

Coordenação de Produção de Material Didático ImpressoPedro Roberto Piloni

Designer EducacionalJair José Durigon

Designer MasterNeure Rejane Alves da Silva

Ilustração e DiagramaçãoVerônica Hirata

Revisão de Língua PortuguesaLívia de Sousa Lima Pulchério

Projeto GráficoRede e-Tec Brasil/UFMT

Centro de Educação Profissional de Anápolis-CEPA/GO

Coordenador Intitucional

José Teodoro Coelho

Equipe de ElaboraçãoDenise Mendes França

Elisa Maria GomideJoicy Mara Resende Rolindo

Maria Cristina Alves de Souza Costa

Coordenador do Curso Maria Cristina Alves de Souza Costa

Rede e-Tec Brasil5

Apresentação Rede e-Tec Brasil

Prezado/a estudante,

Bem-vindo/a à Rede e-Tec Brasil!

Você faz parte de uma rede nacional pública de ensino, a Rede e-Tec Brasil, instituída pelo

Decreto nº 7.589/2011, com o objetivo de democratizar o acesso ao ensino técnico público,

na modalidade a distância. O programa é resultado de uma parceria entre o Ministério da

Educação, por meio da Secretaria de Educação Profissional e Tecnológica (Setec), as universi-

dades e escolas técnicas estaduais e federais.

A educação a distância no nosso país, de dimensões continentais e grande diversidade regio-

nal e cultural, longe de distanciar, aproxima as pessoas ao garantir acesso à educação de qua-

lidade, e promover o fortalecimento da formação de jovens moradores de regiões distantes,

geograficamente ou economicamente, dos grandes centros.

A Rede e-Tec Brasil leva os cursos técnicos a locais distantes das instituições de ensino e para

a periferia das grandes cidades, incentivando os jovens a concluir o ensino médio. Os cursos

são ofertados pelas instituições públicas de ensino e o atendimento ao estudante é realizado

em escolas-polo integrantes das redes públicas municipais e estaduais.

O Ministério da Educação, as instituições públicas de ensino técnico, seus servidores técnicos

e professores acreditam que uma educação profissional qualificada – integradora do ensino

médio e educação técnica – é capaz de promover o cidadão com capacidades para produzir,

mas também com autonomia diante das diferentes dimensões da realidade: cultural, social,

familiar, esportiva, política e ética.

Nós acreditamos em você!

Desejamos sucesso na sua formação profissional!

Ministério da Educação

Março de 2012

Nosso contato

[email protected]

Rede e-Tec Brasil7

Indicação de Ícones

e-Tec Brasil7

Os ícones são elementos gráficos utilizados para ampliar as formas de lin-

guagem e facilitar a organização e a leitura hipertextual.

Atenção: indica pontos de maior relevância no texto.

Saiba mais: oferece novas informações que enriquecem o assunto

ou “curiosidades” e notícias recentes relacionadas ao tema estudado.

Glossário: indica a definição de um termo, palavra ou expressão uti-

lizada no texto.

Mídias integradas: remete o tema para outras fontes: livros, filmes,

músicas, sites, programas de TV.

Atividades de aprendizagem: apresenta atividades em diferentes

níveis de aprendizagem para que o estudante possa realizá-las e con-

ferir o seu domínio do tema estudado.

Reflita: momento de uma pausa na leitura para refletir/escrever so-

bre pontos importantes e/ou questionamentos.

Rede e-Tec Brasil9

Caro estudante,

Seja bem-vindo à disciplina Excel Avançado!

Saber utilizar os recursos avançados do software Microsoft Excel tornou-se

fundamental para o desenvolvimento das atividades profissionais do técnico

em logística, tanto no que se refere à organização, simulação e análise dos

dados como para automatizar a aplicação e apresentação de cálculos esta-

tísticos e financeiros.

Esta disciplina, portanto, serve de base para o curso de Técnico em Logística

no apoio ao desenvolvimento de cálculos e análise de resultados, além de

sua grande utilidade no mercado de trabalho.

O mercado de trabalho espera um profissional altamente qualificado e, para

isto, o conhecimento avançado de utilização do software Microsoft Excel

torna-se essencial para demonstrar essa qualificação.

Lembre-se de que sua participação dedicada e com pesquisas autônomas é

fundamental para a obtenção do sucesso em tudo que fizer, principalmente

neste caso em que estamos tratando de Educação a Distância. Dê o máximo

de si e verá que, ao final, todo esforço valeu à pena.

Aproveite bem esta oportunidade!

Um grande abraço!

Ernane Rosa Martins.

Palavra do professor-autor

Rede e-Tec Brasil11

Apresentação da Disciplina

Caro estudante,

Conhecer o software Excel avançado e saber utilizar seus recursos é fun-

damental para um profissional que atua na área de logística. O uso desta

ferramenta simplifica e agiliza a realização de tarefas e procedimentos,

aumenta a eficiência no desenvolvimento de processos, a eficácia na aná-

lise dos resultados e a efetividade das ações empreendidas por esse pro-

fissional.

Sendo assim, os objetivos de aprendizagem estabelecidos para esta disci-

plina requerem muita atenção e grande envolvimento nas atividades apre-

sentadas.

Apenas fazer uma leitura superficial deste material ou de qualquer outro

indicado não será suficiente para a plena compreensão dos temas abor-

dados.

Para o estudo da ferramenta Microsoft Excel é necessário conhecer e com-

preender bem esse software. Na primeira aula trataremos das categorias

nele inseridas tais como: banco de dados, data e hora, engenharia, in-

formação, texto e dados, estatística, financeiras, lógicas, matemática e

trigonométrica, pesquisa, entre outras. Trataremos também das funções

de cada categoria. Na segunda aula abordaremos temas que mostrarão o

uso das simulações e análise de dados. Na terceira aula trataremos da clas-

sificação, filtro, tabelas e gráficos. Para finalizar esta disciplina, na quarta

aula abordaremos o uso de formulários e de macros.

Ressaltamos que recorremos ao uso do negrito para as situações que lhe

remetem à observação de uma tecla ou item de alguma tela do Excel, pois

implica em um passo a passo para a realização de uma ação.

Bons estudos!

Ernane Rosa Martins

Rede e-Tec Brasil13

Sumário

Aula 1 - Funções (Lógicas, texto, data e hora, procura, financei-ras, estatísticas e banco de dados). 15

Aula 2 - Ferramentas de simulação e análise dos dados (cená-rio, atingir meta e solver) 27

Aula 3 - Classificação, filtro, tabela e gráfico dinâmico 39

Aula 4 - Formulários e macros 47

Palavras finais 53

Guia de Soluções 54

Referências 64

Currículo do Professor-autor 65

Aula 1 - Funções (Lógicas, texto, data e hora, procura, financeiras, estatísticas e banco

de dados). Rede e-Tec Brasil15

Aula 1 - Funções (Lógicas, texto, data e hora, procura, financeiras, estatísticas e banco de dados).

Objetivo:

• Identificar funções lógicas, texto, data e hora, pesquisa, finan-

ceiras, estatísticas e banco de dados.

Nesta disciplina conheceremos os recursos avançados da planilha eletrônica

Microsoft Office Excel, especificamente a versão 2010 (a partir de agora este

será somente citado como Excel) por apresentar uma melhor distribuição dos

comandos em seus menus em relação às versões anteriores.

Figura 1.1 - Tela inicial do Excel 2010.

O Excel é o mais utilizado software de edição de planilhas eletrônicas. A

planilha Excel facilita sobremaneira os trabalhos de gestores, gerentes e

operadores de empresa, e permite agilidade e presteza no desenvolvimento

de tabelas e análise de dados. O Excel é amplamente utilizado nas áreas:

administrativa – folha de pagamento, salários, contabilidade, controle de

compras, tabelas de preço; financeiras – análise de investimentos, custos,

projeção de lucros, fluxo de caixa, controle de captação de recursos, controle

Rede e-Tec Brasil 16 Excel Avançado

de contas a pagar e a receber, simulação de custos, criação de cenários; pro-dução – controle de produção, de produtividade e de estoque; comercial – plano de venda, controle de visitas, análise de mercado, controle de notas

fiscais, entre outros.

Apresentaremos a seguir as principais categorias e funções do Excel, as quais

tornam mais rápidas e práticas a realização de diversos procedimentos e

operações que envolvam tabulação, sistematização e análise de dados.

O Excel dispõe de diferentes categorias, já previamente relacionadas, sendo que as mais utilizadas são: matemáticas, de texto, estatísticas, data e hora, lógicas, de pesquisa e financeiras. Cada categoria apre-senta uma série de funções que a partir de agora você aprenderá como e em que situação utilizá-las.

Inserir função

Para inserir função em uma célula siga os seguintes passos:

• selecione a célula na qual deseja inseri-la;

• na barra de ferramentas, acesse Fórmulas, selecione fx (inserir função);

• escolha a categoria e nela a função desejada;

• clique na função escolhida para inseri-la na célula selecionada.

Como vimos, as fórmulas estão organizadas por categorias. Escolha então a

categoria e nela a função desejada, clique em OK, coloque os argumentos

necessários para constituir a fórmula e pressione Enter.

O Excel dispõe de diversas outras funções, mas estudaremos somente as

mais utilizadas. Se houver necessidade do uso de função que não foi aqui

exemplificada, basta acessar na função fx a opção “Ajuda” para ver a ma-

neira como a mesma pode ser utilizada.



Figura 1.2 - Tela que mostra a aba fórmulas, inserir função no Excel 2010.

Na categoria de Matemática e trigonométrica temos como principais funções:

• SOMA: É uma das funções mais utilizadas do Excel. Ela adiciona uma

listagem de argumentos, retornando o somatório dos números desta lis-

tagem. Exemplo: =SOMA(B2: E2)

• SOMASE: Esta função permite obter a soma de um conjunto de dados,

segundo um determinado critério. Exemplo: =SOMASE(B2: E2; ”>100”)

• SOMARPRODUTO: Esta função retorna a soma dos produtos. Exemplo:

=SOMARPRODUTO(C2: E2; C3: E3)

• ARRED: Arredonda um número até uma quantidade específica de dígi-

tos. Exemplo: =ARRED(B3; 2)

Na categoria de Texto, temos como principais funções:

• ARRUMAR: Esta função remove todos os espaços do texto, exceto os

espaços únicos entre as palavras. Exemplo: =ARRUMAR(B4).

• CONCATENAR: Esta função possibilita agrupar vários itens de texto em

um único item. Exemplo: =CONCATENAR(“O sabor do “;A2;” é de “;A5).

• DIREITA: Retorna os caracteres mais à direita de um valor de texto.

Exemplo: =DIREITA(B3; 3).


• ESQUERDA: Retorna os caracteres mais à esquerda de um valor de tex-

to. Exemplo: = ESQUERDA (B4; 5).

• TEXTO: Esta função formata um número e o converte em texto, segun-

do um formato específico. Exemplo: =TEXTO(18,6352; “$0,00”).

• MOEDA: Esta função converte um número em texto usando o formato

moeda. Exemplo: =MOEDA(582687; 2).

Na categoria Estatística, temos como principais funções:

• MÉDIA: Esta função calcula a média aritmética simples de um conjunto

de dados. Exemplo: =MEDIA(A2:B2)

• MED: Esta função permite obter a mediana dos números indicados.

Exemplo: =MED(B2: E2)

• MODO: Esta função retorna a moda, valor que ocorre com mais frequ-

ência em um intervalo de dados. Exemplo: MODO(B2:I2)

• MÁXIMO: Mostra o maior valor de um conjunto de valores. Exemplo:

=MAXIMO(B2: E2)

• MÍNIMO: Mostra o menor valor de um conjunto de valores. Exemplo:

=MÍNIMO(B3: E3)

• DESVPAD: Esta função calcula o desvio padrão amostral. Exemplo:

=DESVPAD(B2: I2)



Na categoria Data e hora temos como principais funções:

• AGORA: Esta função retorna a data e a hora atual formatada, não exis-

tindo argumentos. Exemplo: =AGORA( )

• HOJE: Esta função é similar à função agora, mas esta retorna somente a

data atual. Também não tem argumentos. Exemplo: =HOJE( )

Na categoria Lógicas temos como principais funções:

• E: Esta função retorna verdadeiro se todos os argumentos forem verda-

deiros. Exemplo: =E(B2>B3; C2>C3)

• OU: Esta função retorna verdadeiro se um dos argumentos for verdadei-

ro. Exemplo: =OU(B2>B3; C2>C3)

• SE: Esta função é uma das mais usadas no ambiente empresarial. Ela

especifica um teste lógico, que retorna um valor se a condição for verda-

deira e outro valor se for falso, sendo utilizada então para realizar testes

lógicos condicionais sobre valores e/ou fórmulas. Exemplo: =SE(C3>7;”

Aprovado”;”Reprovado”) Neste exemplo, se o conteúdo da célula C3

for maior que sete, a função mostra a palavra Aprovado, caso contrário,

mostra Reprovado.

Na categoria Pesquisa temos como principais funções:

• ESCOLHER: Esta função escolhe um valor a partir de uma lista de valores.

Exemplo: =ESCOLHER(A1;”SEGUNDA”;”TERÇA”;”QUARTA”;”QUINTA”)

• PROCH: Esta função procura valores na horizontal. Exemplo:

=PROCH(2;B3:G4;2). Use PROCH quando seus valores de comparação

estiverem localizados em uma linha ao longo da parte superior de uma

tabela de dados e você quiser observar um número específico de linhas

mais abaixo. No exemplo, o 2 é o valor a ser procurado, B3:G4 é o inter-

valo de células e o 2 representa a segunda linha do intervalo, a qual vai

ser mostrada.


• PROCV: Esta função procura valores na vertical. Exemplo:

=PROCV(32;B4:D7;3). Use PROCV quando os valores de comparação es-

tiverem em uma coluna à esquerda dos dados que você deseja localizar.

No exemplo, o 32 é o valor a ser procurado, B4:D7 é o intervalo de células

e o 3 representa a terceira coluna do intervalo, a qual vai ser mostrada.

Na categoria Financeira temos como principais funções:

• VP: Esta função calcula o valor presente de um investimento. Seu resul-

tado equivale ao retornado pela função [PV] das calculadoras financeiras.

Exemplo: =VP(D3;C3;;F3). Onde D3 é a taxa de juros, C3 representa o

número total de períodos de pagamento e o F3 o vf (valor futuro).

• VF: Esta função calcula o valor futuro de um investimento de acordo

com os pagamentos periódicos e constantes e com uma taxa de juro

constante. O resultado equivale ao retornado pela função [FV] das cal-

culadoras financeiras. Exemplo: =VF(D3;C3;;B3). Esta é bastante similar à

função VP, somente mudando o B3 que representa o vp (valor presente).

• NPER: Esta função calcula o número de períodos para investimento de

acordo com pagamentos constantes e periódicos e uma taxa de juros

constante. É similar à função [N] das calculadoras financeiras. Exemplo:

=NPER(D3;;B3;F3), onde D3 é a taxa de juros, B3 representa o valor pre-

sente e o F3 o valor futuro.

• TAXA: Esta função obtém a taxa de juros por período de uma anuidade.

Exemplo: = TAXA(C3;;B3;F3), onde C3 é Nper o B3 representa o valor

presente e o F3 o valor futuro.

• VPL: Esta retorna o valor líquido atual de um investimento, baseado em

uma série de fluxos de caixa periódicos e em uma taxa de desconto.

Seu resultado equivale ao retornado pela função [NPV] das calculadoras

financeiras. Exemplo: =VPL(F3;C3:E3). Sendo assim, esta calcula o valor

líquido atual de um investimento utilizando a taxa de desconto e uma

série de futuros pagamentos (valores negativos) e receita (valores positi-

vos). No exemplo, o F3 é a taxa de desconto e o intervalo C3:E3 é a série

de futuros pagamentos (valores negativos) e receita (valores positivos).



O uso correto da função VPL requer a subtração do investimento inicial por

fora da função.

• TIR: Retorna a taxa interna de retorno de uma sequência de fluxos de

caixa representada pelos números em valores. Exemplo: =TIR(B3:E3). Na

maioria dos casos não é necessário fornecer estimativa, sendo conside-

rado 0,1 (10%).

• PGTO: Calcula o pagamento periódico de uma anuidade de acordo com

o pagamento constante e com uma taxa de juros constante. Exemplo:

=PGTO(D3;C3;B3;E3;F3), onde D3 é a taxa de juros, C3 é nper, o B3 re-

presenta o valor presente, o E3 o valor futuro e o F3 o tipo.

Resumo Por ora você viu as várias funções que o Excel disponibiliza para serem utili-

zadas na realização de diversas tarefas de escritório. Estas funções estão dis-

tribuídas em categorias, sendo as principais: a matemática, texto, estatística,

data e hora, lógica, pesquisa e financeira. Lembrando que não foram espe-

cificadas todas as categorias e funções devido à enorme quantidade destas.

Atividades de AprendizagemConvido-lhe a exercitar e verificar se está apreendendo o estudo até o mo-

mento. Vamos lá?

1. André Luiz tomou um empréstimo no valor de R$ 150.000,00 acertando

um prazo de 6 meses e uma taxa efetiva de juros composto igual a 2%

ao mês. Os juros serão pagos no final do período combinado, juntamen-

te com a devolução do capital. Calcule, mediante o emprego de uma

função financeira apropriada do Excel, o valor a ser pago no encerramen-

to da operação e o valor dos juros incorridos.

2. Mariana quer ter, ao final de um período de um ano, um capital de R$

25.000,00. Se a taxa de juros compostos é de 3%, para que a pretensão

da Mariana se concretize, qual é o valor principal que ela deve aplicar?


3. Após quantos períodos um capital no valor de R$ 800,00 torna-se igual

a R$ 1.000,00 aplicado a uma taxa igual a 2% ao período?

4. Uma aplicação no valor de R$ 3.500,00 gerou um valor futuro igual a R$

5.142,65 após cinco meses. Qual a taxa mensal da operação?

5. Um aparelho de som é anunciado à vista por R$ 600,00 ou em três par-

celas sem entrada. Para uma taxa de juros compostos igual a 3% ao mês,

calcule o valor das parcelas.

6. Após investir R$ 75.000,00, a Cia do Mar Azul espera obter fluxos anuais

iguais a R$ 40.000,00, com acréscimos de R$ 5.000,00 por ano até o

sexto ano. Para uma taxa de juros igual a 12% ao ano (custo de capital),

qual o VPL deste investimento? Qual a TIR?

7. Com o auxílio do Excel, sintetize as informações sobre o comporta-

mento do preço das ações A, B e C no período discriminado abaixo e

calcule as seguintes estatísticas: média, mediana, máximo, mínimo e

desvio-padrão amostral.

COMPORTAMENTO DO PREÇO DAS AÇÕES

PERÍODO AÇÕES

Mês Ação A Ação B Ação C

Jan 0,32 1,20 0,56

Fev 0,82 1,10 0,87

Mar 0,77 1,17 0,77

Abr 0,8 1,07 0,65

Mai 0,66 0,98 0,79

Jun 0,58 0,87 0,72

Média

D.-padrão Amostral

Mediana

Máximo

Mínimo



8. A tabela seguinte apresenta as notas dadas por dois degustadores da com-

panhia de bebidas Água Dura Ltda (Juca e Jorge). Sabe-se que os degus-

tadores testam amostras de alguns lotes produzidos para detectar alguma

anomalia no sabor. Quando a média dos degustadores não atinge 7, o lote

é considerado reprovado, não sendo destinado à venda. Caso contrário, re-

cebe o selo de “Aprovado” e vai para o centro de distribuição da empresa.

COMPANHIA DE BEBIDAS ÁGUA DURA LTDA

NOTA DE DEGUSTADORES

Lote Juca Jorge Média Conceito

3345 5,0 6,0

3890 8,0 10,0

4111 9,0 8,0

5678 4,0 2,0

5701 9,0 10,0

9. Usando a função PROCH, crie uma planilha que permita identificar os

dados de uma nota fiscal apenas digitando o seu número.

NF 2941 6928 5895 5215 6795

Produto Frios Temperos Temperos Temperos Roupas

Origem SP SE SE MG PA

Preço 19,37 25,72 42,21 98,39 113,89

Custo 12,59 16,72 27,44 63,95 74,03

Número da NF ====> 5895

Produto

Origem

Preço

Custo

10. Utilizando a tabela abaixo, calcule os seguintes valores estatísticos:

Média (utilize os valores de compras dos clientes); Moda (utilize o número

de itens comprados por cliente); Mediana; Desvio-padrão amostral; Mínimo

e Máximo.


Código da

compra

Nome do

cliente

Bairro onde

mora

Gênero

(1-Masculino,

2-Feminino)

Idade

em

anos

Renda em

R$

No. de

itens

comprados

Valor da

compra

em R$

3 Diogo Bom Descanso 1 22 2.030,00 5 55,00

8 Mariana Bom Descanso 2 15 950,00 3 28,00

11 Gustavo Bom Descanso 1 36 1.940,00 2 20,00

20 Lise Bom Descanso 2 18 960,00 2 30,00

25 Vinícius Bom Descanso 1 32 1.980,00 3 41,00

2 Juliana Centro 2 17 1.090,00 5 58,00

7 Arthur Centro 1 37 1.955,00 2 26,00

9 Vítor Centro 1 45 2.175,00 3 39,00

10 Marina Centro 2 18 910,00 1 25,00

18 Samanta Centro 2 17 940,00 2 23,00

23 Sérgio Centro 1 21 1.615,00 1 8,00

1 Márcio Colina 1 26 1.890,00 3 41,00

5 Arnaldo Colina 1 43 2.045,00 2 30,00

13 Maria Colina 2 60 930,00 1 14,00

16 José Colina 1 16 1.640,00 2 23,00

21 Paula Colina 2 18 1.010,00 3 36,00

24 Lauro Colina 1 26 1.690,00 1 16,00

4 Thaís Prainha 2 16 920,00 2 26,00

6 Tiago Prainha 1 49 2.235,00 3 35,00

12 Marília Prainha 2 20 950,00 1 10,00

14 Neila Prainha 2 21 1.120,00 4 50,00

15 Pedro Prainha 1 37 2.155,00 4 50,00

17 Vanessa Prainha 2 22 1.040,00 2 22,00

19 Ana Prainha 2 18 910,00 1 10,00

22 Rejane Prainha 2 17 940,00 2 22,00



11. Utilizando a tabela abaixo, calcule as estatísticas para o campo Ganho

de peso. Encontre a Média, Mediana, Desvio-padrão amostral, Mínimo

e Máximo.

Animal Ganho Peso Situação SitDescr

1 250 0 Sem composto







8 -50 0 Sem composto






1 450 1 Com composto



4 -100 1 Com composto










Acredito que você chegou com êxito até aqui, mas temos mais desafios pela

frente. Em algum momento já se deparou com ferramentas de simulação e

análise dos dados? Vamos falar sobre isso na próxima aula.

Bons Estudos!

Rede e-Tec Brasil

Aula 2 - Ferramentas de simulação e análise dos dados (cenário, atingir meta e

solver) 27

Aula 2 - Ferramentas de simulação e análise dos dados (cenário, atingir meta e solver)

Objetivo:

• Identificar as ferramentas de simulação e análise dos dados (ce-

nário, atingir meta e solver).

Neste capítulo, você irá estudar as

ferramentas de simulação e aná-

lise dos dados (cenário, atingir

meta e solver).

Podemos entender análise de da-

dos como a atividade que permi-

te analisar os respectivos dados

através de diferentes técnicas e

métodos, dando a eles uma lógi-

ca, uma razão, um sentido. Para

tanto, o Excel se mostra uma fer-

ramenta fantástica.

Pois bem, o primeiro recurso que veremos será o de cenários, que é muito

útil na elaboração de simulações, pois permite realizar cruzamentos de valo-

res sem a necessidade de modificá-los a cada operação na planilha.

Para iniciar o uso desta ferramenta precisamos estabelecer o significado de

exibição e cenários, que é o conjunto de diferentes dados estabelecidos para

a mesma região nomeada de uma planilha.

Vejamos um exemplo prático: digite a seguinte planilha no Excel.


Na célula C7 coloque a fun-

ção =SOMA(C3:C5)

Na célula C13 coloque a

função =SOMA(C9:C11)

Na célula C15 coloque a

função =C7-C13

Digite nas células C3:C5 os

valores: Alugueis = 400, Sa-

lários = 700 e Horas Extras

= 400.

Figura 2.1 - Planilha para usar o gerenciador de cenários.

Pronto! Agora vamos utilizar o Gerenciador de cenários.

Selecione a faixa de células C3:C5. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Geren-ciador de Cenários. Será apresentada a caixa de diálogo abaixo:

Figura 2.2 - Caixa de diálogo Gerenciador de Cenários.

Rede e-Tec Brasil


solver) 29

Clique em Adicionar. Será apresentada a caixa de diálogo Adicionar Ce-nário como abaixo:

Figura 2.3 - Caixa de diálogo Adicionar Cenário.

Em Nome do cenário, digite o nome para o cenário: Receitas Normais.

Na caixa Células variáveis, se não preencher automaticamente, insira as

referências para as células que você deseja especificar no seu cenário.

Em Proteção, selecione as opções desejadas e clique em OK. Será apre-

sentada agora a caixa de diálogo Valores de Cenário. Acione o botão OK

dessa caixa para que retorne à caixa de diálogo Gerenciador de cenários.

Em seguida, acione o botão Adicionar. Informe agora o título Receitas Altas e clique em OK. Digite os valores que serão considerados altos, como

na figura abaixo:

Figura 2.4 - Caixa de diálogo Valores de Cenário.


Em seguida, aperte OK para voltar ao Gerenciador de cenários. Com isto

já temos duas versões de valores para a região de receitas: uma com valores

normais e outra com valores altos. Também definiremos outra para valores

baixos com o nome de Receitas Baixas, com os valores abaixo:

Figura 2.5 - Caixa de diálogo Valores de Cenário.

Pronto, concluímos a criação de cenários. Vamos selecionar agora uma das

versões listadas e clicar no botão Mostrar para ver o seu conteúdo. Para

finalizar, acionaremos o botão Fechar para voltar para a planilha. Realize o

mesmo processo para despesas.

Ao término de toda a criação de cenários, salve a planilha com o nome de

Lucros e Perdas.

Definidas as três versões distintas, para cada situação será possível efetuar

a seleção de cada versão de despesa e cruzar com uma versão de receita.

Assim é possível efetuar diversas análises como, por exemplo, saber o que

acontecerá com o meu lucro se minha receita for baixa e minha despesa alta.

Na caixa de diálogo Gerenciador de cenários temos ainda o botão Ex-cluir, com o qual é possível cancelar uma das versões; o botão Editar tem

como finalidade modificar uma versão; o botão Mesclar mescla cenários; e

o botão Resumir é para criar um relatório com todos os valores estabeleci-

dos em um cenário.

Agora veremos como utilizar o recurso de Atingir meta, que consiste em

uma ferramenta muito útil na solução de equações algébricas. Como exem-

plo: preencha uma planilha como esta abaixo:

Rede e-Tec Brasil


solver) 31

Figura 2.6 - Planilha para usar Atingir Meta.

Escreva na célula A11: Condições de Financiamento; na célula A13: Pres-

tação Mensal; na célula B13 digite: 450,00. Este valor está posicionado na

célula que será estabelecida como ajustável para atingir a meta pretendida.

Na célula A14 escreva: Entrada; na célula A15 escreva: Valor Financiado; e

na célula B14 coloque a fórmula que efetuará o cálculo do valor da entra-

da, que será o preço menos o valor do financiamento: =B5-B15. Agora na

célula B15 coloque a fórmula que efetuará o cálculo do valor financiado:

=ABS(VP(B8/12;B9;B13)).

A função ABS retorna o módulo de um número, ou seja, o valor positivo.

A função VP, como já vimos, é utilizada para calcular o valor presente de

um determinado investimento com base em uma prestação, taxa de juros e

tempo de investimento.

Agora vamos à guia Dados. No gru-

po Ferramentas de dados, clique em

Teste de hipóteses e, em seguida, cli-

que em Atingir meta. Será apresenta-

da a caixa de diálogo ao lado:

Figura 2.7 - Caixa de Diálogo Atingir Meta.

Estabeleça o endereço da célula que contém a meta no campo Definir célu-la, no nosso caso, B14. No campo Para valor digite o valor de entrada que


você está disposto a dar dentro do limite máximo estabelecido. Digite, por

exemplo, 1076. No campo Alternando célula digite a célula que sofrerá a

alteração de acordo com o campo Para valor. No nosso caso, a célula B13,

que é a célula da prestação. Em seguida clique em OK. Será apresentada a

caixa de diálogo Status do comando atingir meta como esta abaixo:

Figura 2.8 - Caixa de Diálogo Status do comando Atingir meta.

Clique novamente em OK. Perceba que foi informada uma solução para o

valor estabelecido como limite.

Agora veremos como utilizar o recurso Solver, que é uma ferramenta que

auxilia na obtenção de soluções de problemas matemáticos, semelhante ao

Atingir Meta, porém mais poderosa. O recurso nem sempre está disponível

automaticamente por se tratar de um suplemento precisando, às vezes, ser

ativado. Para isto basta acessar Arquivo <Opções> Suplementos. Abrirá

uma caixa de diálogo como abaixo:

Figura 2.9 - Caixa de Diálogo Opções do Excel

Rede e-Tec Brasil


solver) 33

Clique em Ir, em seguida selecione os Su-plementos: Ferramentas de Análise e Solver. Clique em OK e verifique na aba

Dados se apareceu o grupo Análise como

ao lado:

Figura 2.10 - Grupo Análise

Pronto. Agora poderemos utilizar o Solver. Crie a planilha abaixo como

exemplo.

Figura 2.11 - Planilha para usar Solver.

Em nosso exemplo usamos na célula B7 a formula =B5+B5*B6 e na célula

B14 usamos a fórmula =B5+B5*B13. Para podermos trabalhar com solver

precisamos estabelecer dois pontos: a célula de destino, que no nosso caso

será B14, e a célula variável, que será B13.

Agora selecione a célula B14 e acesse no grupo Análise o comando Solver. Será apresentada a caixa Parâmetros do Solver, como a seguir:


Figura 2.12 - Caixa de Diálogo Parâmetros do Solver

Em Definir Objetivo, coloque B14. Em Valor de: digite o valor para cobrir

a oferta do concorrente. No nosso exemplo vamos colocar 5790 e em se-

guida informe em Alterando Células Variáveis a célula B13 e clique em Resolver.

Neste momento aparecerá outra caixa de diálogo na qual poderemos escolher

Manter Solução do Solver ou Restaurar Valores Originais. É possível ain-

da salvar o resultado como um cenário. Agora basta somente clicar em OK.

Veja que a taxa de juros na célula B13 passou a ser de 29,02%.

Resumo Nesta aula vimos como utilizar as ferramentas de simulação e análise dos da-

dos (cenário, atingir meta e solver) utilizando exemplos práticos a fim de dei-

xar bem claro sua utilização. Lembrando que existem outras ferramentas de

simulação e análise dos dados que não foram especificadas, mas que podem

ser bem exploradas a partir do conhecimento adquirido nessa disciplina.

Rede e-Tec Brasil


solver) 35

Atividades de AprendizagemVamos ao exercício! Concentre-se e recorra ao conteúdo da aula caso haja

dúvida.

1. Com o comando Atingir meta descubra qual deve ser o preço unitário

do Produto A para que o Total seja R$ 1.000,00.

Item Unitário Qtde. Total

Produto A 200 0,00

Produto B 3,00 12 36,00

Produto C 3,00 18 54,00

Produto D 2,00 10 20,00

SOMA 110,00

2. Calcule o ponto de equilíbrio, em quantidades, para os dados abaixo.

Use o comando Atingir meta.

Preço Unitário 38,00

Custo variável unitário 22,00

Custo Fixo 30.000,00

Quantidade 3.000,00

Lucro 18.000,00

* DICA: Force o lucro para zero! O ponto de equilíbrio é aquele no qual a

quantidade vendida é igual a zero!

3. Com base nos mesmos dados da atividade 2, quanto é necessário vender

caso se deseje obter um lucro de R$ 80.000,00?





Quantidade 3.000,00

Lucro 18.000,00

4. A figura apresentada a seguir ilustra um problema enfrentado pela fá-

brica de sanduíches Queijo Quente Ltda. que precisa determinar qual a

produção dos produtos Sanduba e Mistão que permite a obtenção de um

lucro total máximo. As restrições dizem respeito ao consumo unitário de

cada um dos produtos, dos recursos queijo, presunto e pão de forma e

aos seus respectivos estoques.

Sanduba Mistão ----------- Estoque

Valores unitários ----------- ----------- ----------- -----------

Consumo unitário de queijo 8 6 ----------- 48

Consumo unitário de presunto 6 12 ----------- 72

Consumo unitário de pão de forma 10 5 ----------- 50

Lucro 6 4 ----------- -----------

Quantidades fabricadas 2 2

Valores totais Soma Folga

Consumo total de queijo 16 12 28 20

Consumo total de presunto 12 24 36 36

Consumo total de pão de forma 20 10 30 20

Lucro total 12 8 20

* DICA: As principais células estão pintadas! Lembre-se de ativar o Suple-

mento!

5. No início do ano, a Indústria e Comércio de Sorvetes Geladinhos en-

frentava um problema. Seus gerentes estavam com certa dificuldade de

alcançar o Lucro Máximo referente à produção e venda de seus sorvetes

e picolés. Uma caixa de sorvete apresenta um lucro unitário de R$ 4,00

e uma caixa de picolé apresenta um lucro de R$ 2,00. Na produção de

sorvetes são utilizados 10 litros de leite e 20 gramas de edulcorantes;

na produção de picolés são utilizados 4 litros de leite e 40 gramas de

edulcorantes. Há disponibilidade de 200 litros de leite e 800 gramas de

edulcorantes. Por tudo isso, é necessário determinar a quantidade de

sorvetes e picolés que devem ser produzidos e comercializados, tendo em

vista os dados e fatores limitantes existentes, visando a obtenção do lucro

máximo. Para facilitar o processo de busca de uma solução otimizada foi

construído o seguinte modelo no Excel. Pode-se otimizar o modelo com

Rede e-Tec Brasil


solver) 37

o auxílio do Solver. Lembre-se que a quantidade produzida tem que ser

um número inteiro (não se pode produzir meio picolé, por exemplo).

Unitários Totais

Lucro Leite Edulcorante Qtde Lucro Leite Edulcorante

Sorvete 4 10 20 15 60 150 300

Picolé 2 4 40 12,5 25 50 500

Soma 85 200 800

Restrição 200 800

Até aqui creio que você já se deparou com novidades no mundo do Excel.

Mas já operou esse software com intuito de classificar, filtrar, gerar tabela

e gráfico dinâmico? Se já o fez, vamos reforçar. Caso contrário, você terá

oportunidade de aprender sobre esse assunto na próxima aula.

Bons Estudos!

Rede e-Tec BrasilAula 3 - Classificação, filtro, tabela e gráfico dinâmico 39

Aula 3 - Classificação, filtro, tabela e gráfico dinâmico

Objetivo:

• Aplicar as ferramentas de classificação, filtro,

tabela e gráfico dinâmico.

A partir de agora a oportunidade dada é voltada para a

utilização das ferramentas de classificação, filtro, tabela e

gráfico dinâmico.

Para classificarmos os dados, uma das for-

mas mais simples de utilizar o recurso de

classificação é clicar nos botões de atalho

da aba Dados do Grupo Classificar e

Filtrar, conforme a figura ao lado:

Figura 3.1 - Botões de Atalho para Classificação de Dados.

Para isto, antes devemos selecionar as células da coluna que queremos re-

alizar a classificação, podendo optar por classificar em ordem crescente ou

decrescente. Também é possível classificar com mais de um nível como, por

exemplo, classificar uma lista de produtos por origem e preço, como na fi-

gura abaixo:

Figura 3.2 - Caixa de Diálogo Classificar

Excel AvançadoRede e-Tec Brasil 40

Outro recurso legal e que possibilita exibir os dados com base em critérios

são os filtros. Com os filtros é possível, por exemplo, exibir apenas os dados

referentes aos fornecedores com origem em um determinado estado. Para

isto, basta clicar em uma célula da área de dados, ir à aba Dados e clicar

no botão Filtro. Com isto serão inseridas janelas no cabeçalho da base de

dados. Podemos então marcar as opções que desejamos, deixando somente

estas visíveis, ocultando o restante. Podemos deixá-las visíveis novamente

clicando na opção Limpar Filtro de Origem. Como na figura abaixo:

Figura 3.3 - Planilha com Filtro

Podemos também utilizar dois dos mais importantes recursos do Excel e que

tornam mais simples e rápida a análise de dados: as tabelas e gráficos di-

nâmicos. Veremos agora como uma tabela dinâmica é utilizada para obter

cruzamento de informações em uma base de dados.

Como exemplo, utilizaremos a planilha abaixo:

Figura 3.4 - Planilha para usar Tabela Dinâmica.

Selecione os dados e acesse na aba Inserir no grupo Tabelas a opção Tabela Dinâmica. Abrindo a caixa de diálogo Criar Tabela Dinâmica, em Selecionar uma tabela ou intervalo coloque a seleção desejada como na figura a seguir:


Figura 3.5 - Caixa de Diálogo Criar Tabela Dinâmica

Indique também o local onde a tabela dinâmica deve ser incluída. No nosso

caso vamos deixar o padrão que é Nova Planilha. Clique em OK. Agora

vamos configurar o layout da tabela dinâmica, onde podemos personalizar

diferentes relatórios. Para isto devemos selecionar os campos na caixa de

seleção e, mantendo o mouse pressionado, arrastar o campo para a área de

elaboração da tabela em uma das opções: filtro de relatório, rótulos de linhas, rótulos de coluna e valores. No nosso exemplo iremos selecionar

Produto, Origem e Preço.

Podemos notar que a

configuração de soma é

automática no Excel (Pa-

drão). Para obter a média

ou outra medida esta-

tística bastaria clicar no

campo (no caso, em cima

de Soma de Preço) e se-

lecionar Configurações do Campo de Valor e

alterar a função. Como

podemos ver na figura

ao lado:Figura 3.6 - Caixa de Diálogo Configuração do Campo de Valor


Para configurar o formato de saída dos dados basta acessar o botão Forma-to do Número. No nosso exemplo vamos selecionar a opção Número, com

separador de milhares e duas casas decimais.

Podemos realizar várias alterações de configuração de uma tabela dinâmica

ou nos dados através das opções disponibilizadas no Menu temporário,

ferramentas de Tabela Dinâmica.

Com isto podemos agora a partir de uma tabela dinâmica gerar gráficos

dinâmicos, o que se torna muito útil na análise de dados. Para gerarmos um

gráfico dinâmico, basta acessar o menu Opções, dentro de Ferramentas de Tabela Dinâmica, e abrir a caixa abaixo:

Figura 3.7 - Caixa de Diálogo Inserir Gráfico

Nesta caixa temos todas as opções de configuração de gráficos disponíveis

no Excel. Para nosso exemplo vamos escolher o gráfico de colunas simples.

Ao clicarmos em OK o Excel já exibirá o gráfico dinâmico conforme a figura

a seguir:


Figura 3.8 - Tabela e Gráfico Dinâmico

O gráfico gerado é considerado dinâmico porque à medida que são efetu-

adas alterações na tabela dinâmica, as mesmas são aplicadas ao gráfico.

Não é necessário, portanto, para cada alteração da tabela, gerar um novo

gráfico.

ResumoO foco dessa aula foi a criação de planilhas e a partir delas o modo de utilizar

as ferramentas do Excel de classificação, filtro, tabela e gráfico dinâmico.

Estes recursos são muito úteis na análise de dados empresariais e na gestão

de negócios em geral.


Atividades de AprendizagemVocê está pronto para gerar um gráfico? Vamos conferir?

1. Classifique a base de dados a seguir de acordo como o maior faturamen-

to (1º critério), maior volume de compras (2º critério) e maior quantidade

de funcionários (3º critério).

Fornecedor Código Estado Número de Funcionários

Qualidade do Atendimento

Faturamento Mensal

Volume de compras

Astral 1 BA 10 Alta 1.000,00 500,00

Carioquinha 2 RJ 20 Media 1.010,00 100,00

Freijó 3 RJ 20 Baixa 1.210,00 60,00

Bom Bocado 4 SP 20 Baixa 1.150,00 50,00

Jambolão 5 RJ 20 Baixa 1.200,00 60,00

Quindim 6 RJ 20 Baixa 1.250,00 65,00

Preçobom 7 BA 30 Baixa 1.850,00 95,00

Souza 8 RJ 40 Baixa 1.350,00 65,00

Merendão 9 SP 40 Baixa 1.200,00 60,00

QTG 10 SP 50 Baixa 1.450,00 70,00

Almanara 11 MG 30 Alta 1.500,00 300,00

Doce Mel 12 MG 20 Alta 1.550,00 200,00

Zukermann 13 SP 10 Alta 1.600,00 170,00

Itabira 14 BA 10 Media 1.650,00 170,00

Oto 15 BA 50 Media 1.850,00 170,00

Delícia 16 BA 50 Media 1.750,00 190,00

Jajá 17 RJ 40 Media 1.250,00 200,00

Bem Barato 18 SP 30 Media 1.850,00 300,00

Rei do Mel 19 SP 20 Media 1.900,00 180,00

Guaraná 20 MG 20 Media 2.000,00 110,00


2. Considere a base de dados abaixo, de uma rede de farmácias. Faça um

filtro de forma que sejam apresentados apenas os registros do estado

Bahia.

Região Estado Filial Funcionários Faturamento

CO Goiás Anápolis 5 170

NE Sergipe Aracaju 6 200

N Pará Belém 5 170

CO Mato Grosso Cáceres 4 140

CO Mato Grosso do Sul Campo Grande 6 250

SE São Paulo Cotia 5 170

S Paraná Curitiba 5 170

SE São Paulo Diadema 8 260

NE Bahia Feira de Santana 8 210

CO Goiás Goiânia 8 260

NE Bahia Itabuna 4 170

N Amazonas Manaus 5 170

S Paraná Maringá 4 140

SE São Paulo Osasco 9 290

CO Tocantins Palmas 4 140

N Pará Parauapebas 3 140

S Rio Grande do Sul Pelotas 9 290

SE Rio de Janeiro Petrópolis 6 200

S Rio Grande do Sul Porto Alegre 7 230

SE Rio de Janeiro Rio de Janeiro 12 320

NE Bahia Salvador 12 380

SE São Paulo Santo André 8 260

SE São Paulo São Paulo 16 430

NE Sergipe Tobias Barreto 4 140

NE Bahia Vitória da Conquista 3 110


3. A partir da base abaixo, crie uma tabela dinâmica e um gráfico dinâmico

em colunas que mostre os nomes e as notas dos alunos.

Nome Sexo Origem Idade Altura Peso Nota Renda

Pedro M SP 17 1,65 64 8,1 540

Ana F SP 18 1,59 56 6,4 230

Maria F RJ 17 1,72 85 5,3 680

João M RJ 18 1,86 92 2,4 720

Alex M RJ 19 1,72 71 9,5 410

Nina F SP 17 1,66 58 7,6 330

Fernando M RJ 17 1,89 73 5,4 290

Júlio M SP 19 1,95 81 2,1 410

Penso que nesse momento você está refletindo o quanto valeu se dedicar,

pois certamente compreendeu a importância de ver um gerenciamento por

meio de gráficos.

Bons Estudos!

Rede e-Tec BrasilAula 4 - Formulários e macros 47

Objetivo:

• Analisar a importância das ferramentas Formulários e Macros.

Aula 4 - Formulários e macros

Como utilizar formulários e aprender a construir macros é o que veremos

nessa aula. O formulário é um facilitador de trabalho que tem como fun-

ção principal melhorar a qualidade de apresentação dos dados, já que em

algumas operações o usuário não os digita, mas sim escolhe os valores per-

mitidos, melhorando os aspectos visuais e profissionais de nossas planilhas.

Para poder utilizar formulários primeiro temos que habilitar a aba Desenvol-vedor do Excel. Para isto vá ao Arquivo <Opções>. Na opção Personalizar

Faixa de Opções marque Desenvolvedor e clique em OK, como na figura

abaixo:

Figura 4.1 - Caixa de Diálogo Opções do Excel


Pronto! Se tudo deu certo aparecerá a aba Desenvolvedor no Excel. Esta é

destinada a usuários avançados de Excel, que exploram recursos de Macros

e códigos em VBA.

Para inserir controles de formulário temos que acessar o Grupo Controles <Inserir>, onde estão disponíveis vários componentes de formulário.

Vamos criar um pequeno exemplo prático. Para isto insira um componente

chamado Caixa de Grupo para organização dos outros componentes e,

dentro deste, três Botões de Opção. Na Caixa de Grupo altere o título

para Modelo e logo abaixo formate as células para que fiquem iguais à

figura abaixo:

Figura 4.2 - Planilha com Formulário

Formate da seguinte maneira: preenchimento de fundo azul, título em negri-

to mesclado e centralizado e, na célula C11, coloque borda e fundo branco.

Então vamos à segunda parte do exemplo. Crie agora uma base de dados

conforme a figura abaixo:

Figura 4.3 - Exemplo de Base de Dados

O número um da célula M2 não é preciso digitar agora. Para que ele apareça

vamos fazer o seguinte: clique com o botão direito do mouse em cima do

primeiro Botão de Opção e selecione Formatar Controle. Será apresenta-

da a caixa de diálogo a seguir:


Figura 4.4 - Caixa de Diálogo Formatar Controle

Em Vínculo da célula selecione a célula M2 e clique em OK. Repita para

os outros dois componentes Botão de Opção. Perceba que ao selecionar o

primeiro botão de ação aparece o número um na célula M2. Se selecionar o

segundo, o número dois, e o terceiro o número três.

Agora vamos à fórmula. Clique na célula C11 e digite a fórmula =SE(M2=1

;L3;SE(M2=2;L4;SE(M2=3;L5))). Com isto associamos os preços da base de

dados com os botões de opção.

Existem diversos outros componentes para formulário que podemos utilizar,

tais como: Botão, Caixa de Combinação, Caixa de Seleção, Botão de Rotação, Caixa de Listagem, Rótulo e Barra de Rolagem.

Outro conhecimento que você poderá adquirir ainda nesta aula é a utilização

de Macro. Uma Macro é um recurso que funciona como gravação das ações

para automatizar certos processos, sendo assim, tudo que for executado

na planilha em termos de digitação, formatação e acionamento de menus

será monitorado e transformado em código Visual Basic, ficando associado

à planilha.

Para a construção de uma Macro, acesse a aba Exibição no Grupo Macros,

opção Gravar Macro, conforme a figura a seguir:


Figura 4.1 - Caixa de Diálogo Gravar Macro

Por padrão o Excel já coloca o nome de Macro1, como sugestão. Em nosso

exemplo vamos criar uma macro que escreva um nome no meio da tela.

Digite então no campo Nome da macro o titulo de Escreve_no_centro.

Podemos ainda definir teclas de atalho para a execução da Macro e o local

aonde será gravada.

O nome informado no campo Nome da macro não pode ser iniciado por

número e não deve conter espaços em branco entre as palavras.

No campo Descrição podemos escrever um breve comentário do que fará a

Macro. Em seguida clique em OK, iniciando a gravação.

A partir de agora o Excel já está gravando e basta executar os comandos

desejados para que fiquem registrados. No nosso exemplo, vamos digitar a

frase: TESTE DE EXECUÇÃO na célula D9, formatar com alinhamento cen-

tralizado e mesclar células usar fonte tamanho 16 e negrito. Posicione o

cursor na célula A1 e acesse na aba Exibição, Grupo Macros a opção Parar Gravação. Pronto. A Macro

foi criada. Agora para que

você veja a Macro criada em

ação, apague o que foi digi-

tado, acesse o mesmo lugar

e escolha a opção Exibir Macro. Será apresentada a

caixa de diálogo ao lado:

Figura 4.2 - Caixa de Diálogo Macro


A partir desta caixa de diálogo podemos selecionar uma Macro, executá-la

e assim ela irá refazer tudo que foi gravado. Podemos também excluir uma

determinada Macro ou editá-la.

Ao escolher editar será apresentado o editor do Visual Basic juntamente

com a folha de módulo Módulo1 com o código da Macro criada durante o

processo de gravação. Em seguida, salve a pasta de trabalho com o nome de

Macro01 e feche o Visual Basic voltando para o Excel.

Resumo O aprendizado proporcionado nessa aula foi o modo de utilizar formulários e

macros. Vimos que o bom uso do recurso de formulário e seus componentes

podem facilitar o trabalho e melhorar a qualidade dos dados.

Já as Macros se tornaram ferramentas muito boas no que se refere à auto-

matização de tarefas repetitivas, gravando determinadas tarefas e reutilizan-

do estas quando necessário.

Atividades de AprendizagemVamos verificar o conhecimento adquirido?

1. Crie uma planilha com título de Hotel, utilize uma caixa de grupo com

o título de Noites e três botões de opção (Uma, Duas e Três) associando

estes com os relativos preços: para uma noite, R$ 80; para duas noites,

R$ 160; e para três noites, R$ 240. Quando selecionada a opção deve ser

mostrada em uma célula o preço referente à quantidade de noites.

2. Crie uma Macro com o nome de exercício 1, que crie esta tabela:

Venda Qtde Preço Sub-total

Ar Condicionado 699,00

Ventilador 49,00

Fogão 299,00

Micro-ondas 199,00

DVD player 149,00

TV LCD 42” 2100,00

Home Theather 980,00

Total de vendas


3. Crie uma Macro com o nome de exercício 2 que gere uma tabela como

esta abaixo:

Planilha CUSTOS.XLSGerenciando Custos e Preços com o Excel

Por Adriano Leal Bruni e Rubens Famá

CUSTOS.XLS: Análise de custo padrão versus real

Componentes Padrão Real Diferença

Q Preço (R$)

Custo (R$)

Q Preço (R$)

Custo (R$)

Q Preço (R$)

Custo (R$)

Materiais 0,9 0,78 0,702 0,8 0,8 0,64 -0,1 0,02 -0,062

MOD 0,09 8 0,72 0,12 7 0,84 0,03 -1 0,12

CIF 0,85 1 0,85 0,9 0,95 0,855 0,05 -0,05 0,005

- - - - -

- - - - -

- - - - -

- - - - -

Soma 1,422 1,48 0,058

Chegamos ao final dessa fase de estudo de sua formação em Técnico de Lo-

gística. Espero que tenha sido enriquecedora essa oportunidade de aprendi-

zado. Obrigado pela confiança diante das orientações dadas. Siga em frente

com entusiasmo, dedicação e obterá sucesso.

Bons estudos!

Rede e-Tec Brasil53

Palavras finais

Caro (a) estudante,

É com muita satisfação que chegamos ao final de mais uma disciplina.

Quero parabenizá-lo (a) pelo êxito em mais esta etapa de seu aprendizado,

pois com certeza o conteúdo visto até aqui será muito importante para sua

atuação profissional e irá te ajudar muito no decorrer das disciplinas que

ainda virão.

Tenho certeza que este será somente um ponto de partida para um apren-

dizado maior que virá com o tempo, com a experiência e com as demais

atividades realizadas com o professor tutor.

Continue seus estudos com muita dedicação e esforço, mantendo sempre

acesa a chama da curiosidade a fim de pesquisar e buscar novos conheci-

mentos.

Pesquise sobre outras funcionalidades existentes no Excel. Isto te ajudará a

ter um diferencial em relação aos demais profissionais da área. O Excel é uma

ferramenta extremamente poderosa na análise e exibição das informações

empresariais. Pense nisto.

Espero ter contribuído na sua formação profissional.

Boa sorte.


Guia de Soluções

Aula 01

1. VP (150.000,00)

N 6

I 2,00%

VF R$ 168.924,36

Juros 18.924,36

2. VF 25.000,00

N 12

I 3%

VP (17.534,50)

3. VP (800,00)

VF 1.000,00

I 2,00%

N 11,27

4. VP (3.500,00)

VF 5.142,65

N 5

I 8,00%

5. VP 600,00

N 3

I 3,00%

PGTO (212,12)

Rede e-Tec Brasil55

6. Fluxo $

I 12,00%

0 -75.000,00

1 40.000,00

2 45.000,00

3 50.000,00

4 55.000,00

5 60.000,00

6 65.000,00

VPL R$ 134.107,15

TIR 58,10%

7. Média 0,66 1,07 0,73

D.-padrão Amostral 0,19 0,12 0,11

Mediana 0,72 1,09 0,75

Máximo 0,82 1,20 0,87

Mínimo 0,32 0,87 0,56

8. Lote Juca Jorge Média Conceito

3345 5,0 6,0 5,5 Reprovado

3890 8,0 10,0 9,0 Aprovado

4111 9,0 8,0 8,5 Aprovado

5678 4,0 2,0 3,0 Reprovado

5701 9,0 10,0 9,5 Aprovado

9.

NF 2941 6928 5895 5215 6795

Produto Frios Temperos Temperos Temperos Roupas

Origem SP SE SE MG PA

Preço 19,37 25,72 42,21 98,39 113,89

Custo 12,59 16,72 27,44 63,95 74,03


Número da NF ======> 6928

Produto Temperos

Origem SE

Preço 25,72

Custo 16,72

10.

Média 29,52

Mediana 26,00

Moda 2,00

Desvio-padrão 14,01

Mínimo 8,00

Máximo 58,00

11.

Média 264,42

Mediana 275,00

Desvio-padrão 158,44

Mínimo -100,00

Máximo 500,00

Aula 02

1. Item Unitário Qtde. Total

Produto A 4,45 200 890,00

Produto B 3,00 12 36,00

Produto C 3,00 18 54,00

Produto D 2,00 10 20,00

SOMA 1.000,00

Rede e-Tec Brasil57

Tela de configuração do Atingir Meta:

Tela do resultado do Atingir

Meta:

2.




Quantidade 1.875,00

Lucro 0,00

* DICA: Force o lucro para zero! O ponto de equilíbrio é aquele no qual a

quantidade vendida é igual a zero!


Tela do resultado do Atingir Meta:

3.




Quantidade 6.875,00

Lucro 80.000,00



Tela do resultado do Atingir Meta:

4.

Sanduba Mistão ----------- Estoque

Valores unitários ----------- ----------- ----------- -----------

Consumo unitário de queijo 8 6 ----------- 48

Consumo unitário de presunto 6 12 ----------- 72

Consumo unitário de pão de forma 10 5 ----------- 50

Lucro 6 4 ----------- -----------

Quantidades fabricadas 3 4

Valores totais Soma Folga

Consumo total de queijo 24 24 48 0

Consumo total de presunto 18 48 66 6

Consumo total de pão de forma 30 20 50 0

Lucro total 18 16 34

* DICA: As principais células estão pintadas! Lembre-se de ativar o Suple-

mento!

Tela de configuração do

Solver:

Rede e-Tec Brasil59

Tela do resultado do Solver:

5.

Unitários Totais

Lucro Leite Edulcorante Qtde Lucro Leite Edulcorante

Sorvete 4 10 20 14 56 140 280

Picolé 2 4 40 13 26 52 520

Soma 82 192 800

Restrição 200 800

Tela de configuração do

Solver:

Tela do resultado do Solver:


Aula 03

1.

Fornecedor Código Estado Número de Funcionários

Qualidade do Atendi-mento

Faturamento Mensal

Volume de compras

Guaraná 20 MG 20 Media 2.000,00 110,00

Rei do Mel 19 SP 20 Media 1.900,00 180,00

Bem Barato 18 SP 30 Media 1.850,00 300,00

Oto 15 BA 50 Media 1.850,00 170,00

Preçobom 7 BA 30 Baixa 1.850,00 95,00

Delícia 16 BA 50 Media 1.750,00 190,00

Itabira 14 BA 10 Media 1.650,00 170,00

Zukermann 13 SP 10 Alta 1.600,00 170,00

Doce Mel 12 MG 20 Alta 1.550,00 200,00

Almanara 11 MG 30 Alta 1.500,00 300,00

QTG 10 SP 50 Baixa 1.450,00 70,00

Souza 8 RJ 40 Baixa 1.350,00 65,00

Jajá 17 RJ 40 Media 1.250,00 200,00

Quindim 6 RJ 20 Baixa 1.250,00 65,00

Freijó 3 RJ 20 Baixa 1.210,00 60,00

Merendão 9 SP 40 Baixa 1.200,00 60,00

Jambolão 5 RJ 20 Baixa 1.200,00 60,00

Bom Bocado 4 SP 20 Baixa 1.150,00 50,00

Carioquinha 2 RJ 20 Media 1.010,00 100,00

Astral 1 BA 10 Alta 1.000,00 500,00

Rede e-Tec Brasil61

2.

Região Estado Filial Funcionários Faturamento

NE Bahia Feira de Santana 8 210

NE Bahia Itabuna 4 170

NE Bahia Salvador 12 380

NE Bahia Vitória da Conquista 3 110

3.

Média de Nota

Nome Total

Alex 9,5

Ana 6,4

Fernando 5,4

João 2,4

Júlio 2,1

Maria 5,3

Nina 7,6

Pedro 8,1

Total Geral 5,85


Aula 041.

2.

Rede e-Tec Brasil63

3.


Referências

ANDRADE, D. F. Excel 2010: Controlando dados. São Paulo: Editora Viena, 2011.

BRUNI, L. A. Excel Aplicado à Gestão Empresarial. São Paulo: Editora Atlas S. A,

2008.

CINTO, A. F. Excel Avançado. São Paulo: Editora Novatec, 2008.

MANZANO, A. N. G. Estudo Dirigido de Excel XP Avançado. São Paulo: Editora Ética,

2004.

Currículo do Professor-autor

Ernane Rosa Martins, Professor do IFG nos cursos

Bacharel em Informática, Técnico em Informática e

Técnico em Manutenção e Suporte em Informática.

Professor do Curso Técnico em Informática do CEPA

– Centro de Educação Profissional de Anápolis. Pro-

fessor do Curso Técnico em Informática do SENAC –

GO. Professor do Curso de Programador de Sistemas

de Informação do SENAI – GO, Desenvolvedor PHP e

JAVA WEB. Bacharel em Ciência da Computação pela Faculdade Unianhan-

guera – GO, Bacharel em Sistemas de Informação pela Faculdade Unievan-

gelica – GO e Especialista em Tecnologia em Gestão da Informação pela

Faculdade Unianhanguera – GO. Principais áreas de atuação: Desenvolvi-

mento de Sistemas para Internet, Banco de Dados e Análise de Sistemas.

Education

Excel avancado 09_02_2013