Excel Avanado

Gustavo Cipriano Mota Sousagustavocms@gmail.com

Reviso

Introduo (1)

Planilha eletrnica que integra o pacote Microsoft Office

Planilha eletrnicaTabela para apresentao de valores numricos ou alfa numricos.

Os valores em uma planilha podem ser automaticamente calculados a partir de outros valores

Introduo (2)

O Microsoft Excel pode ser usado para...Criar oramentos

Calcular impostos

Elaborar oramento

Registrar notas de alunos

Acompanhar a venda de produtos

Controlar cronograma

Criar grficos

Extrair informaes de dados

...

Introduo (3)

Exemplo de planilha

Introduo (4)

Introduo (5)

Um arquivo do Excel chamado de pasta, e pode conter vrias planilhas

Uma planilha composta por vrias clulas, onde os dados podero ser inseridosCada clula de uma planilha se encontra no ponto de encontro de uma coluna e uma linha

As colunas so dispostas verticalmente, e identificadas por uma letra

As linhas so dispostas horizontalmente, e identificadas por um nmero

Introduo (6)

Cada clula identificada por um endereoO endereo de uma clula composto pelo nome da coluna e linha onde esta se encontra

Por exemplo...A clula na interseco da coluna C com a linha 4 denominada C4

Qualquer ao realizada na clula selecionada, que se apresenta destacada com uma borda

Introduo (7)

Para abrir o Microsoft ExcelIniciar Todos Programas Microsoft Office Microsoft Office Excel 2003

Iniciar ExecutarDigitar: excel

Introduo (8)

Edio de dadosNavegarClique ou Teclas direcionais

InserirDigitar e

(na barra de frmulas) ou Enter

ApagarDelete

...

Introduo (9)

Edio de dadosAlterarClique duplo ou F2

Barra de frmulas

FormataoCor da letra, cor de fundo, bordas, alinhamento,

Formatar Clulas...

Exerccio (1)

Construir uma tabela de notas com a formatao ilustrada

A partir das notas dos alunos, calcularemos a soma destas, e em seguida a mdia de cada aluno

Exerccio (2)

FormataoMesclar clulas (ttulo)

Redimensionar linha (ttulos)

Redimensionar coluna (Nomes)

Formatar fonte (ttulo: Negrito, Arial 16pt)

Formatar borda

Formatar nmero (notas: nmero, 1 casa decimal)

Formatar alinhamentoTtulos: Centro (horizontal e vertical), Quebrar texto

Exerccio (3)

FrmulasDefinem uma regra para o clculo automtico de um valor

Total das Notas

Mdia

Ala de preenchimentoArrastar

Clique duplo

Exerccio (4)

FunesMDIA

SE

Remover colunas

Formatao condicional

Resultado:http://tinyurl.com/tabnotas

Referncias (1)

Uma referncia um endereo de uma clula

Ao utilizar uma referncia em uma funo ou frmula que espera um valor, utilizado o valor existente na clula da referncia

Exemplo:

= A1 + 520 = A1 + A323= SOMA(4;A2)13= MDIA(A1:A3)10,7

A

115

29

38

Referncias (2)

Frmulas que utilizam apenas referncias podem ser recalculadas sem necessidade de alterao na frmula

interessante evitar o uso de valores fixos nas frmulas, preferindo o uso de referncias

No Excel existem trs tipos de refernciasRelativa

Absoluta (Com nome)

Mista

Referncias (3)

RelativaQuando uma referncia relativa utilizada em uma frmula...

e esta frmula copiada ou preenchida...

esta referncia relativizada

Referncias (4)

Relativa

Copiar&Colar

Referncias (5)

Relativa

Ala dePreenchimento

Referncias (6)

AbsolutaQuando uma referncia absoluta utilizada em uma frmula...

e esta frmula copiada ou preenchida...

esta referncia no alterada

Referncias (7)

AbsolutaExemplo de utilizao para evitar o uso de valor fixo na frmula do resultado

Referncias (8)

Absoluta

Copiar&Colar

Obs: Este apenas um exemplo do comportamento das referncias absolutas. No faz sentido o uso de referncias absolutas em uma tabela desta natureza, onde o comportamento natural uma frmula distinta para cada linha

Referncias (9)

Exerccio:Atualize a planilha de tabela de notas, removendo a utilizao do valor fixo no clculo do resultado

http://tinyurl.com/tabnotas2

Referncias (10)

Exerccio:Obtenha a planilha no endereo abaixo, e utilize referncias absolutas para calcular o resultado esperado

http://tools.assembla.com/svn/gustavo/cepss/absoluta.xls

Referncias (11)

Absoluta (com nomes) possvel definir um nome para uma referncia

Caixa de nome

Inserir Nome Definir...

Aps definir um nome, ele pode ser usado diretamente na frmula!

Referncias (12)

Absoluta (com nomes)Exemplo:

=SE(F3=7;APROVADO;REPROVADO)

SE(D3A2+A3 1 0

A

115

29

38

E desta frmula?

Resposta: 0

Expresso Condicional (11)

=SE(condicao;valor_se_verdadeiro;valor_se_falso)

123

A1>A2+A3 1 SE(A3+1=A2;2;0)

A

115

29

38

E desta frmula?

Expresso Condicional (12)

=SE(condicao;valor_se_verdadeiro;valor_se_falso)

123

A1>A2+A3 1 SE(A3+1=A2;2;0)

A

115

29

38

E desta frmula?

Resposta: 2

Expresso Condicional (13)

=SE(A2=15; "OK"; "No OK")Resultado: OK

=SE(A4Rodas;OK;No OK)Resultado: OK

=SE(A2=A1;OK;No OK)Resultado: No OK

=SE(A3+1=A2;1+1;2+2)Resultado: 2

http://tinyurl.com/excel-condicional

A

115

29

38

4Rodas dentadas

5Acessrios

Sites

http://www.expresstraining.com.br/

http://blog.dicasdeexcel.com/

Anlise de Dados

Listas (1)

Cabealhos

http://tinyurl.com/dados-xls

Enter data into the list.

Filter the list to display only the rows that meet a certain criteria.

Sort the list.

Insert formulas to calculate subtotals.

Create formulas to calculate results on the list filtered by certain criteria.

Export the list to a SharePoint server so it can be shared with others.

Create a summary table of the data in the list. (Pivot table)

Listas (2)

DefinioTabela com cabealhos definidos na primeira linha e uma entrada por linha

O Excel identifica estes cabealhos, e permite trabalhar com diretamente com o nome dos campos

Operaes aplicveisFormulrios

Filtros

Classificao

Subtotais

Funes de banco de dados

Insert descriptive labels (one for each column) in the first row of the list.This is the header row. If the labels are lengthy, consider using the word-wrapformat so that you dont have to widen the columns.

Each column should contain the same type of information. For example, dontmix dates and text in a single column.

You can use formulas that perform calculations on other fields in the samerecord. If you use formulas that refer to cells outside the list, make these absolutereferences; otherwise, you get unexpected results when you sort the list.

Dont use any empty rows within the list. For list operations, Excel determinesthe list boundaries automatically, and an empty row signals the end of the list.

For best results, try to keep the list on a worksheet by itself. If this isnt possible,place other information above or below the list. In other words, dont usethe cells to the left or the right of a list.

Select the upper-left data cell and choose WindowFreeze Panes to makesure that the headings are visible when the list is scrolled.

You can preformat entire columns to ensure that the data has the same format.For example, if a column

Listas (3)

FormulriosUtilizado para adicionar, excluir e procurar itens em uma lista

Dados Formulrio

Cria nova linha

Localizar linha

Alterar campos

As frmulas existentes so inseridas automaticamente

Listas (4)

ClassificaoOrdenar os dados de acordo com uma coluna

Menu Dados Classificar

Campos docabealho

Listas (5)

Classificao

Listas (4)

SubtotaisPermite agrupar os dados de acordo com uma informao

Exemplo:Quantidade de funcionrios por departamento

Salrio total dos funcionrios por departamentos

Total de vendas por filial

Listas (5)

Subtotais

Exerccio

ExerccioInserir uma nova linha utilizando formulrioProduto: Caf

Qtd: 1

Valor: R$ 1,00

Filial: Goinia

Ordenar por filial

Utilizar a funo subtotais para exibir o valor total das vendas por filial

Remover os subtotais

Ordenar por produto

Utilizar a funo subtotais para exibir o valor total das vendas por produto

Funes Condicionais (1)

CONT.SE(intervalo;critrios)

Calcula o nmero de clulas em um intervalo que corresponde a determinados critrios.

Funes Condicionais (2)

Intervalo:Espao de vrias clulas consecutivas

Critrios:Condio para contagem

Exemplos:=ADM

1500

Funes Condicionais (3)

Exemplo:Quantidade de funcionrios que pagam IRRF (salrio >= R$ 1.434,01)

Funes Condicionais (4)

SOMASE(intervalo pesquisa;critrios;intervalo soma)

Realiza a soma de valores que correspondem a determinados critrios

Exemplo:

Calcular a soma os salrios de um departamentoCalcular a soma das vendas em uma filial

Funes Condicionais (5)

Exemplo:Soma dos salrios do departamento ADM

Funes Condicionais (6)

ExerccioCalcule a quantidade, e o valor total de vendas por filial

Funes de Banco de Dados (1)

So aplicadas em listas, utilizando critrios

BDMDIA

BDCONTAR

BDEXTRAIR

BDMX

BDMN

BDMLTIPL

BDSOMA

Funes de Banco de Dados (2)

BDSOMA(banco_dados;campo;critrios)

Funes de Banco de Dados (3)

BDSOMA(banco_dados;campo;critrios)

A1:C8

Funes de Banco de Dados (4)

BDSOMA(banco_dados;campo;critrios)

Campo onde ser realizado a operao (SOMA, MDIA,CONTAR, MULTIPL)

Salario

Funes de Banco de Dados (5)

BDSOMA(banco_dados;campo;critrios)

Similar a uma listaCabealhos

Critrios para cada campo

F1:G2

Funes de Banco de Dados (6)

Exemplo:Obter a soma dos salrios dos funcionrios com salrio acima de R$ 1000,00

Funes de Banco de Dados (7)

ExerccioObtenha o arquivo em

http://tinyurl.com/bd-excel

Calcule a mdia salarial do funcionrios com idade >= 30 anosUtilize a funo BDMDIA

Funes de Banco de Dados (8)

Funes de Banco de Dados (9)

BDCONTARConta a quantidade de linhas que atendem um determinado critrio

Exemplo:Quantidade de funcionrios com:Idade > 25

Salrio 1.23

1 => .1

12 => .12

12345 => 123.45

123456 => 1234.56

Formatao Personalizada (4)

Smbolos:

#

O digito s exibido se significativoNo so considerados dgitos significativos:

Zero esquerda

Zero direita aps vrgula (o formato contm vrgula)

Exemplo:#####,###

Exibir no mximo 3 nmeros aps a vrgula

Formatao Personalizada (5)

Smbolos:

0

Se o nmero possui menos dgitos que o formato, estes dgitos so exibidos como 0

Exemplo:

0000,000

SEMPRE exibir 3 nmeros aps a vrgula, e no mnimo 4 nmeros antes

Formatao Personalizada (6)

Smbolos:

0

Se o nmero possui menos dgitos que o formato, estes dgitos so exibidos como 0

Exemplo:

0000,000

SEMPRE exibir 3 nmeros aps a vrgula, e no mnimo 4 nmeros antes

Formatao Personalizada (7)

Smbolos:

a m d

Utilizados para formatar datas

Exemplo:Data: 29/03/2009

dd/mm=> 29/03

dd/mmm=> 29/mar

dd/mm/aaaa=> 29/03/2009

mm/aaaa=> 03/2009

mmm/aaaa=> mar/2009

aaaa-mm-dd=> 2009-03-29

Formatao Personalizada (7)

Outros smbolos*=> Repete smbolo adiante

Formato geral:

Positivo;Negativo;Zero;Texto

Cores

Condies

Formatao Personalizada (5)

ConsideraesA formatao personalizada (e outras) afeta apenas a apresentao

O valor da clula o mesmo independente de sua formatao

Estilos e Modelos (1)

EstilosPermitem aplicar vrias formataes a uma clula

Podem ser cadastrados, modificados, e excludos

Exemplo:TtuloNmero: Moeda

Fonte Arial; Negrito

Padres: Sombreado

Caminho:Formatar Estilos

Estilos e Modelos (2)

ModelosUm modelo permite criar novas pastas com formatao, validao, frmulas, filtros, e outras funcionalidades j configuradas

O Excel permite a criao de arquivos de modelos e sua utilizao para criao de novas pastas

Exemplo:Folha de pagamento

Fluxo de caixa

Ficha de ponto

Estilos e Modelos (3)

Para criar um modelo basta acessar:Arquivo Salvar como...

E selecionar no campo tipo a opo Modelo (*.xlt)

Estilos e Modelos (4)

Para criar uma nova pasta usando modelo:Arquivo Novo

E na guia Geral selecionar o modelo desejado

Estilos e Modelos (5)

Exerccio:

http://tools.assembla.com/svn/gustavo/cepss/diario.doc

Para isto:Apague os dados inseridos

Mantenha as frmulas e demais configuraes intactas

Utilize a opo Arquivo Salvar como... para criar um modelo

Tente criar uma nova pasta a partir deste arquivo.

Preencha alguns valores e verifique se as frmulas esto sendo calculadas corretamente

Funes de Procura (1)

Buscam uma matriz, procurando uma linha ou coluna que possuam um valor especificado

Funes de Procura (2)

Exemplo: Procurar o estado de uma cidade

Funes de Procura (4)

possvel escolher o nome de uma cidade, e obter automaticamente o nome do estado, ou UF

Campos preenchidos atravs da funo de procura

Funes de Procura (5)

Funo PROCura Vertical

PROCV(valor_procurado;tabela;ndice;intervalo)

Procura um valor_procurado na primeira coluna de uma tabela. Ao encontrar este valor, a funo retorna o valor que se encontra na mesma linha, mas na coluna ndice.

Funes de Procura (6)

PROCV(Goinia;A4:C12;3;FALSO)

BUSCA INTERVALOS

Primeira coluna

Funes de Procura (7)

Funo PROCura Horizontal

PROCH(valor_procurado;tabela;ndice;intervalo)

Realiza a procura nas linhas da tabela, neste caso, o ndice representa o nmero da linha

Funes de Procura (8)

Exerccio

http://tinyurl.com/cidades-xls

http://tinyurl.com/proch-xls

Funes de Procura (9)

Busca em um intervalo

http://tinyurl.com/irrf-xls

Funes de Procura (10)

Exercciohttp://tinyurl.com/proc-ex-xls

Dividir e Congelar Painis

Permite visualizar simultaneamente diferentes partes de uma planilha

Exemplo:Permite manter sempre visvel os ttulos de uma tabela.

Consolidao de Dados (1)

Permite consolidar os dados de vrias planilhas em uma nica planilha

Consolidao de Dados (2)

ConsolidaoSOMA

MDIA

CONTAGEM

Posio/Categoria

Tabela dinmica (1)

Permite selecionar diferentes visualizaes dos dados facilmente

Utilizado para analisar grandes quantidades de dados

http://tinyurl.com/cedbgx

Ou

http://tools.assembla.com/svn/gustavo/cepss/tabeladinamica.xls

Tabela dinmica (2)

Exemplo: Pedidos de venda

Campos: Empresa, Funcionrio, Data do pedido, Destino, Meio de entrega

Como comparar as vendas realizadas pelos funcionrios? Como identificar qual funcionrio realizou o maior volume de vendas?

Como identificar qual o meio de entrega preferido pelos clientes?

Tabela dinmica (3)

Exemplo: Pedidos de venda

Como identificar qual a regio com um maior volume de vendas?

Como identificar o meio de entrega mais utilizado para vendas no Brasil?

Como identificar o ms com maior volume de vendas?

...

Tabela dinmica (4)

Dados 2D

Produto 2

Produto 3

....

Filial 1 Filial 2 ....

Produto 1

Tabela dinmica (5)

Dados 3D

Produto 1

Produto 2

Produto 3

...

Vendedor 1Vendedor 2

....

Filial 1Filial 2

...

Tabela dinmica (6)

Campos calculados

Itens calculados

Analise de Metas

Atingir meta

Solver

Assume that youre in the market for a new home and you know that you can afford$1,800 per month in mortgage payments. You also know that a lender can issue afixed-rate mortgage loan for 6.50 percent, based on an 80 percent loan-to-value (thatis, a 20 percent down payment). The question is What is the maximum purchaseprice I can handle? In other words, what value in cell C4 causes the formula in cellC11 to result in $1,800? You could plug values into cell C4 until C11 displays $1,800;however, Excel can determine the answer much more efficiently.

Funes Financeiras (1)

Utilizadas para clculos de rendimentos ou pagamentos envolvendo taxas de juros, e parcelas

Permite:Calcular o valor de parcelas de um financiamento

Calcular a taxa de juros embutida em um produto

Calcular o retorno de um investimento

Calcular o nmero de parcelas necessrias para pagar um valor

Funes Financeiras (2)

TAXACusto do dinheiro (taxa de juros) por um perodo determinado

NPERNmero de perodos ou parcelas existentes.

PGTOValor desembolsado ou recebido a cada perodo (parcela do pagamento ou rendimento)

VFValor futuro o valor obtido ao final de um perodo por uma aplicao ou financiamento.

VPValor presente o valor do dinheiro trazido data atual, ou seja, isolado dos juros.

Funes Financeiras (3)

Exemplo:

Quero financiar um carro no valor de R$30.000,00 em 12 meses. Sei que a taxa de juros que ser cobrada de 2% a.m. Qual ser o valor das prestaes?

Pretendo financiar um imvel de R$80.000,00 com prestaes mensais de R$ 500,00 ao ms, durante 10 anos. Qual o valor da entrada necessria?

Funes Financeiras (4)

Pretendo acumular em 40 anos R$2.000.000,00 de reais para a minha aposentadoria. Para isto estou planejando alcanar um rendimento mensal de 1,5% em meus investimentos. Qual o valor que devo poupar mensalmente?

Funes Financeiras (5)

http://tools.assembla.com/svn/gustavo/cepss/financeira.xls

Funes (1)

Data e Hora

AGORA()

HOJE()

ANO(data)

MES(data)

DIA(data)

HORA(horario)

MINUTO(horario)

Funes (2)

DATADIF(dt_inicial; dt_final; argumento)

Argumento:

Y anos entre as datas.M meses entre as datas.D dias entre as datas.

...

Funes (3)

ArredondamentoARRED(num;num_digitos)

ARREDONDAR.PARA.BAIXO(num;num_digitos)

AREDONDAR.PARA.CIMA(num;num_digitos)

PAR(num)

MPAR(num

Funes (4)

Referncia

LIN(ref)

COL(ref)

Outros temas

Auditoria de frmulas

Comentrios

Macros

Endereo

Este documento pode ser encontrado nos seguintes endereos

http://tools.assembla.com/svn/gustavo/cepss/aulas.odp

http://tools.assembla.com/svn/gustavo/cepss/aulas.pdf