Como calcular idade no Excel

Aprenda a programar sua planilha para calcular automaticamente quantos anos as pessoas têm, a partir de sua data de nascimento

Passo 1. Na planilha do Excel, crie uma coluna programada com a fórmula “HOJE” para atualizar automaticamente a data atual em futuras operações. Para isso, selecione a célula e digite a fórmula “=HOJE()”, exibindo a data assim que ativada com a tecla “Enter”;



Passo 2. Em seguida, replique o comando programado para as demais linhas desta coluna. Clique no ponto no canto inferior direito da célula com a fórmula “HOJE” e arraste-o para baixo até o limite desejado;



Passo 3. Para criar a programação que calcula datas, é preciso especificar três variáveis da fórmula “DATADIF”: a data inicial, a data atual e unidade do cálculo (que pode ser em anos completos, “y”, meses completos, “m”, ou dias, “d”). Neste caso, utilizou-se a fórmula “=DATADIF(B2;C2;”y”)”, com “B2” e “C2” indicando as células nas coluna de data inicial e de atual, respectivamente, enquanto “y” especificou a resposta em número de anos;



Passo 4. Para reproduzir a fórmula nas linhas abaixo, clique com o botão esquerdo do mouse no ponto no canto inferior direito da célula com a fórmula “DATADIF” e arraste-o para até a linha desejada;



Passo 5. É possível combinar a resposta da fórmula DATADIF com outras variáveis, dentre elas as outras três mais sofisticadas: diferença de dias, ignorando anos e meses, “md”, diferença de meses, ignorando dias e anos, “ym” ou diferença de dias, ignorando anos “yd”. Para isso, lembre-se de inserir “&” para especificar parâmetros da resposta e novas fórmulas;



Fonte: techtudo

Usar objetos 3D em planilhas

Se você é um usuário que possui o Office 2016 ou alguma versão posterior, vai gostar deste post.

Recentemente, o Excel incorporou um recurso que pode ser útil para muita gente: agora, é possível inserir objetos em 3D nas planilhas produzidas no programa.

A primeira versão do Excel foi lançada na década de 80, e era bem básica - e um dos recursos disponíveis era o das formas. Eram objetos simples, como setas indicativas, balões de pensamento, rostos sorridentes e outros desenhos que podiam ser colocadas nos documentos. Assim, essa novidade pode ser vista como uma evolução delas, que agora se tornaram objetos de três dimensões e personalizáveis.

O mais bacana dos objetos 3D é que eles podem ser movidos pelo usuário em qualquer direção e ângulo, o que com certeza vai te ajudar a ver cada detalhe e ângulo do objeto que está sendo utilizado. Pense numa peça de uma máquina ou um carro, por exemplo: não seria bem melhor se fosse possível visualizar o objeto em todos os ângulos possíveis?

E aí, curtiu a novidade? Confira no tutorial abaixo como começar a usar objetos 3D no Excel do seu computador:

• Abra o Excel.
• No menu da parte superior da tela, vá na guia “Inserir”. Você verá alguns recursos listados.
• Em seguida, clique em “Modelos 3D”.
• Agora, é só escolher se você vai utilizar um arquivo do seu próprio computador ou se irá buscá-lo online.

Caso você escolha procurar algum arquivo bacana online, você verá uma janela com os modelos que estão disponíveis atualmente. Depois de selecionar o modelo que você irá usar, é possível utilizar os controles da tela para deixá-lo no melhor ângulo ou até mesmo colocá-lo atrás das células da planilha.

Por enquanto, só há um repositório com as imagens, mas é importante lembrar que o recurso é bem recente. Assim, é bem provável que haverá mais modelos sendo adicionados futuramente. O recurso de modelos 3D também está disponível para outros aplicativos do pacote Office, como o Powerpoint.

Para utilizar os modelos 3D, é preciso ter a versão 2016 dos programas ou posterior.


Fonte: TECMUNDO

Como adicionar fórmulas no Excel pelo celular ou tablet

O Excel, aplicativo de planilha da Microsoft, possui versão para dispositivos móveis, como celulares e tablets Android, iPhone e iPad (iOS), além do Windows 10 Mobile. Assim como no computador, o usuário pode adicionar e usar fórmulas em planilhas criadas ou editadas no telefone. O recurso pode ser útil para fazer ajustes rápidos de última hora quando não se tem um computador à disposição.

Se você não sabe como inserir e usar fórmulas no Excel pelo celular, confira as dicas e aprenda. O passo a passo foi feito em um iPhone (iOS), mas o procedimento é o mesmo em aparelhos com Android ou Windows.


Passo 1. Abra a planilha no Excel e posicione o cursor sobre a célula que você quer adicionar a fórmula. Em seguida, toque sobre o botão “ƒx”, no canto superior esquerdo da tela.




Passo 2. Localize a fórmula que você quer usar e toque sobre ela. Se você souber o comando para a fórmula desejada, é possível digitá-lo para encontrar rapidamente a fórmula.




Passo 3. Adicione os argumentos à fórmula digitando os valores ou tocando sobre as células. Por fim, toque sobre o botão com o tique verde, no canto superior direito da tela, para calcular o resultado.




Com essa dica simples, você poderá adicionar e usar fórmulas no Excel pelo seu celular ou tablet.

Fonte: TechTudo

Habilitar execução de macros no Excel 2010

Clique no Botão do Microsoft Office e depois em Opções do Excel.
Clique em Central de Confiabilidade, clique em Configurações da Central de Confiabilidade e depois em Configurações de Macro.
Clique nas opções desejadas:
Desabilitar todas as macros sem notificação Clique nesta opção se não confiar em macros. Todas as macros existentes nos documentos e alertas de segurança sobre macros serão desabilitadas. Se houver documentos com macros não assinadas e não confiáveis, você poderá transferi-los para um local confiável. Os documentos mantidos nesse local poderão ser executados sem precisarem ser verificados pelo sistema de segurança da Central de Confiabilidade.
Desabilitar todas as macros com notificação Esta é a configuração padrão. Clique nesta opção se desejar desabilitar as macros e receber alertas de segurança caso seja detectada alguma macro. Dessa forma, você poderá decidir quando habilitar essas macros individualmente.
Desabilitar todas as macros, exceto as digitalmente assinadas Esta configuração é igual à opção Desabilitar todas as macros com notificação. A única diferença é que, se a macro for assinada digitalmente por um editor confiável, ela poderá ser executada se você confiar no editor; caso contrário, você será notificado. Portanto, você pode optar entre habilitar essas macros assinadas ou confiar no editor. Todas as macros não assinadas são desabilitadas sem notificação.
Habilitar todas as macros (não recomendável; códigos possivelmente perigosos podem ser executados) Clique nesta opção para permitir a execução de todas as macros. Esta configuração não é recomendável, pois torna o computador vulnerável a códigos possivelmente mal-intencionados.
Confiar no acesso ao modelo de objeto do projeto do VBA Essa configuração serve para desenvolvedores e é usada para bloquear deliberadamente ou para permitir o acesso programático ao modelo de objetos do VBA a partir de qualquer cliente de automação. Em outras palavras, ela fornece uma opção de segurança para o código que é gravado para automatizar um programa do Office e manipular programaticamente o ambiente e o modelo de objetos do Microsoft VBA (Visual Basic for Applications). Trata-se de uma configuração por usuário e aplicativo que nega o acesso por padrão. Essa opção de segurança torna mais difícil para programas não autorizados criarem um código de auto-replicação que possa prejudicar os sistemas dos usuários finais. Para que qualquer cliente de automação possa acessar o modelo de objetos do VBA programaticamente, o usuário que executa o código deve conceder acesso explicitamente. Para ativar o acesso, marque a caixa de diálogo.

O Microsoft Excel (nome completo Microsoft Office Excel) é um programa de planilhas eletronicas escrito e produzido pela Microsoft para computadores usando o sistema operacional Microsoft Windows. Seus recursos incluem uma interface intuitiva e capacitadas ferramentas de cálculo e de construção de gráficos que, juntamente com marketing agressivo, tornaram o Excel um dos mais populares aplicativos de computador até hoje. É por grande vantagem o aplicativo de planilha eletrônica dominante disponível para essas plataformas e tem o sido desde a versão 5 em 1993 e da sua inclusão como parte do Microsoft Office.

Um Pouco de História
A Microsoft originalmente comercializou um programa de planilha eletrônica chamado Multiplan em 1982, o qual era muito popular em sistemasCP/M , mas em sistemas MS-DOS perdia em popularidade para o Lotus 1-2-3 . Isso levou ao desenvolvimento de um novo programa chamado Excel que começou com a intenção de, nas palavras de Doug Klunder, "fazer tudo que o 1-2-3 faz e fazer melhor". A primeira versão do Excel foi lançada para o Mac em 1985 e a primeira versão para Windows (numerada 2.0 para se alinhar com o Mac e distribuída com um tempo de execução do ambiente Windows) foi lançada em novembro de 1987 . A Lotus foi lenta em trazer o 1-2-3 ao Windows e por volta de 1988 o Excel havia começado a passar o 1-2-3 em vendas e ajudou a Microsoft a alcançar a posição de liderança no desenvolvimento de software para o PC. Essa conquista, destronando o rei do mundo do software, solidificou a Microsoft como um competidor válido e mostrou seu futuro de desenvolvimento de software gráfico. A Microsoft aumentou sua vantagem com lançamento regular de novas versões, aproximadamente a cada dois anos. A versão atual para a plataforma Windows é o Excel 12, também chamado de Microsoft Excel 2007 . A versão atual para a plataforma Mac OS X é o Microsoft Excel 2004.

No começo da sua vida o Excel se tornou alvo de um processo judicial de marca registrada por outra empresa que já vendia um pacote de software chamado "Excel" na indústria financeira. Como resultado da disputa a Microsoft foi solicitada a se referir ao programa como "Microsoft Excel" em todas as press releases formais e documentos legais. Contudo, com o passar do tempo essa prática foi sendo ignorada, e a Microsoft resolveu a questão quando ela comprou a marca registrada reservada ao outro programa. Ela também encorajou o uso das letras XL como abreviação para o programa; apesar dessa prática não ser mais comum, o icone do programa no Windows ainda é formado por uma combinação estilizada das duas letras, e a extensão do arquivo do formato padrão do Excel até a versão 11 (Excel 2003) é .xls, sendo .xlsx a partir da versão 12, acompanhando a mudança nos formatos de arquivo dos aplicativos do Microsoft Office.

O Excel oferece muitos ajustes na interface ao usuário em relação às mais primitivas planilhas eletrônicas; entretanto, a essência continua a mesma da planilha eletrônica original, as células são organizadas em linhas e colunas, e contêm dados ou fórmulas com referências relativas ou absolutas às outras células.

O Excel foi o primeiro programa de seu tipo a permitir ao usuário definir a aparência das planilhas (fontes, atributos de caractere e aparência da célula). Também introduziu recomputação inteligente de células, na qual apenas céluas dependentes da célula a ser modificada são atualizadas (programas anteriores recomputavam tudo o tempo todo ou aguardavam um comando específico do usuário). O Excel tem capacidades avançadas de construção de gráficos.

Desde 1993, o Excel tem incluído o Visual Basic para Aplicativos (VBA), uma linguagem de programação baseada no Visual Basic que adiciona a capacidade de automatizar tarefas no Excel e prover funções definidas pelo usuário (UDF, user defined functions) para uso em pastas de trabalho. o VBA é um complemento poderoso ao aplicativo que, em versões posteriores, inclui um ambiente integrado de desenvolvimento (IDE, integrated development environment). A gravação de macros pode produzir código VBA que replica ações do usuário, desse modo permitindo automação simples de tarefas cotidianas. o VBA permite a criação de formulários e controles dentro da pasta de trabalho para comunicação com o usuário.

A funcionalidade de automação provida pelo VBA fez com que o Excel se tornasse um alvo para vírus de macro. Esse foi um problema sério no mundo corporativo até os produtos anti-vírus começarem a detectar tais vírus. A Microsoft adotou tardiamente medidas para previnir o mau uso com a adição da capacidade de desativar as macros completamente, de ativar as macros apenas quando se abre uma pasta de trabalho ou confiar em todas as macros assinadas com um certificado confiável.

As versões 5.0 a 9.0 do Excel contêm vários "ovos de páscoa", porém desde a versão 10 a Microsoft tomou medidas para eliminar tais recursos não documentados de seus produtos.


Versões:

Excel 2.0 para Windows
Excel 3.0
Excel 4.0
Excel 5.0 (Office 4.2 e 4.3, também uma versão de 32 bits para o Windows NT somente)
Excel 7.0 (Office 95)
Excel 8.0 (Office 97)
Excel 9.0 (Office 2000)
Excel 10.0 (Office XP)
Excel 11.0 (Office 2003)
Excel 12.0 (Office 2007)

Não há Excel 1.0 para evitar confusão com versões para o Macintosh e nem 6.0 por que ele foi lançado com o Word 7. Todos os produtos do Office 95 têm capacidades de OLE 2 - para mover dados automaticamente de vários programas - e o nome Excel 7 deveria mostrar que ele era contemporâneo do Word 7.

Atingir Meta - Excel

Auditoria de Fórmulas Excel

Muitas vezes você já deve ter visto isto no Excel, mas, nunca imaginou para que serviria certo?
Então vamos a uma breve explicação do que seria a AUDITORIA DE FÓRMULAS em Excel.

Imaginemos que você possui uma planilha que vai calcular valor de desconto, então, você tem na célula A1 o valor real, na B1 você quer que calcule o valor de desconto e este desconto esta na C1.

vamos lá !


100 5 5%
100 0

mas e se você arrasta para linhas de baixo?

se você não fixar na fórmula a coluna C1 irá acontecer um erro e para isso servirá a auditoria de fórmulas.

Deixe o erro acontecer para que possamos analisar.

Agora vá na célula B2 e clique em auditoria de fórmulas - rastrear precedentes e ele te mostrará onde está o erro, você estará solicitando ao Excel para pegar um valor vazio, com este recurso é muito simples corrigir um erro de fórmula.

Excluir Dados Duplicados

Excluir dados duplicados é bem simples também, a partir do Excel 2007 esta ferramenta está muito simples.

Selecione a coluna que será feita a verificação, clique em Dados, e Remover Dados Duplicados, irá abrir uma janela informando a coluna e solicitando a confirmção, clique em OK e pronto.

Evitando dados duplicados em Excel - Validação

Validar para que uma determinada coluna não aceite dados duplicados é muito simples, segue abaixo uma maneira bem rápida de se fazer.

Primeiro escolha a coluna que será aplicada essa validação, clique em Validação.

Será aberta uma caixa de mensagem com alguns critérios para validação no nosso caso vamos escolher o Personalizado em "Permitir"

No campo fórmula insira a está fórmula =CONT.SE(A$1:A$50;A1)=1

Na aba Alerta de erro, descreva a mensagem que o usuário irá receber caso já exista o dado digitado.

Validando Datas com Excel

Validando células que irão receber datas de um intervalo de datas.

Exemplo

01/01/2011 a 31/12/2011

Selecione uma célula e digite a seguinte fórmula no campo de validação.

=OU(ÉTEXTO(A1);E(A1>=C1;A1<=D1))

Se for acionado qualquer intervalo de datas que não esteja no intervalo que você definiu a validação será acionada.

Validando Datas de um determinado mês

=MÊS(A1)=MÊS(HOJE())

Validando células para aceitar datas até 360 dias da data atual.

=E(A1>=HOJE();A1<=DATA(ANO(HOJE())+1;MÊS(HOJE());DIA(HOJE())))

Validando células que aceite somente alguns dias anteriores a data de hoje.

=HOJE()-A1<=10

Neste caso a célula aceitará até 10 dias da data indicada.

Bom acho que deu pra entender um pouquinho, mais para frente postarei outras formas de validação.

Função ÉERRORS

Quando uma fórmula produz erro, o Excel inclui na célula uma mensagem como #DIV/0! (divisão por zero) ou #REF! (referência inválida). Para evitar que essas mensagens apareçam na planilha, ou mesmo para personalizá-las, use a função ÉERROS. Se, por exemplo, em lugar de uma mensagem de erro você quiser que apareçam três asteriscos (###), escreva uma fórmula como:

=SE(ÉERROS(B1/A1);"###";B1/A1)

Comando voltar Excel (100 vezes)

A possibilidade de voltar para a última ação é uma ajuda importantíssima para desfazer erros, mas essa função vem automaticamente limitada a 16 ações. Quando isso não é suficiente, você pode manipular o registro do Windows para aumentar o número de níveis e estabelecer um máximo de cem ações a serem desfeitas.

1. Feche o Excel e selecione Executar no menu Iniciar do Windows.

2. Na caixa de texto Abrir, digite regedit e clique OK.

3. Abra as seguintes chaves no painel esquerdo: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options (leve em consideração que 11.0 é para a versão 2003 do Excel e 10.0, para a 2002).

4. Selecione Editar/Novo/Valor DWORD, digite UndoHistory e tecle Enter.

5. Clique duas vezes em UndoHistory, selecione Decimal no painel Base e digite o número de comando que o Excel poderá desfazer (um número compreendido entre 16 e 100).

6. Clique OK e feche o Editor do Registro.

Agora você já pode abrir o Excel e verificar se desfaz o número de vezes estabelecido.

MATEMÁTICA FINANCEIRA NO EXCEL

FUNÇÕES FINANCEIRAS:

=EFETIVA(taxa_nominal,nper)
=IPGTO(taxa;período;nper;vp;vf;tipo)
=NOMINAL(taxa_efet,nper)
=NPER(taxa;pgto;vp;vf;tipo)
=PGTO(taxa;nper;vp;vf;tipo)
=PGTOCAPACUM(taxa;nper;vp;início_período;final_período;tipo_pgto)
=PGTOJURACUM(taxa;nper;vp;início_período;final__período;tipo_pgto)
=PPGTO(taxa;período;nper;vp;vf;tipo)
=TAXA(nper;pgto;vp;vf;tipo;estimativa)
=TIR(valores;estimativa)
=VF(taxa;nper;pgto;vp;tipo)
=VFPLANO(capital;plano)
=VP(taxa;nper;pgto;vf;tipo)
=VPL(taxa;valor1;valor2;...)
=XTIR(valores;datas;estimativa)
=XVPL(taxa;valores;datas)


FUNÇÕES MATEMÁTICAS:

=EXP(núm)
=LN(núm)
=LOG(núm;base)
=LOG10(núm)
=POTÊNCIA(núm;potência)
=RAIZ(núm)
=SOMA(núm1;núm2; ...)
=SOMARPRODUTO(matriz1;matriz2;matriz3; ...)


FUNÇÕES DE LÓGICA:

=E(lógico1;lógico2; ...)
=OU(lógico1;lógico2;...)
=SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)

FUNÇÕES DE PROCURA E REFERÊNCIA:

=PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo)
=PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)


FUNÇÕES DE DATAS:

=DATAM(data_inicial;meses)
=DIA.DA.SEMANA(núm_série; retornar_tipo)
=DIATRABALHO(data_inicial;dias;feriados)
=DIATRABALHOTOTAL(data_inicial;data_final;feriados)
=HOJE( )


Algumas das funções que estudaremos será necessário a utilização dos suplementos Ferramentas de Análises, para habilitar vá em Ferramentas - Suplementos - Ferramentas de análise - VBA.

Operações Simples

Exemplo 1

(A * B) / (C * D)
------------------ = ? =((A*B)/(C*D))/(E/F)
(E/F)


Exemplo 2

(A / B) - (C * D) + G
---------------------- = ? =((A/B)-(C*D)+ G)/((E/F)/(H/I))
(E/F)/(H/I)

Exemplo 3

10000 + 125
--------------------- = ? = =(10000+125)/(1-((15+0,49)*45/3000))
[1-(15+0.49)*45
------------
3000 ]


Operações Complexas

Vimos as operações básicas no Excel, agora verermos as operações complexas como : Potência, Porcentagem, Lógica, Soma Produtos e Procv.

A melhor maneira de aprender é com exemplos práticos, e é o que mostraremos a seguir.


Potência

Exemplos

(1,28)3 - =1,28^3
(27)1/3 - =27^(1/3)
(1,07)3 * (1,05)4 - =(1,07^3)*(1,05^4)
(1 + 4,50) 32/360 - =(1+4,5)^(32/360)
[ ( 1 + 0,60) 68/30 - 1 ] * 100 - =((1+0,6)^(68/30)-1)*100
[(1,10)4 * 0,10 * 100.000,00
------------------------------- - =((1,1^4)*0,1)/((1,1^4)-1)*100000
(1,10)4 - 1 ]



Porcentagem

Na matermática financeira aprender sobre porcentagem é um dos tópicos de suma importânca, então diversos exemplos foram expostos abaixo.

Sabendo que a prestação foi paga em atraso com multa de 2%. Qual o valor da multa e o novo valor da prestação?


Valor original da prestação 260000,00
Multa 2,00%
Valor da multa 5200,00 =H487*H488
Valor da prestação atual 265200,00


Sabendo que um produto no valor de R$ 400,00 está sendo vendido com um desconto de 8,50%. Qual o valor do desconto fornecido pela loja e qual o valor a ser pago por este produto?


Preço anunciado 400,00
Desconto 8,50%
Valor do desconto 34,00 =H498*H499
Valor a ser pago 366,00


Sabendo que a alíquota do Imposto de Renda devido num rendimento de aplicação é 20%, calcular o valor do imposto e o e o valor líquido de uma aplicação:

Rendimento nominal 12350,00
Alíquota de I.R 20,00%
Valor do I.R. devido 2470,00 =H509*H510
Valor líquido de resgate 9880,00

Sabendo o montante ganho em diversas aplicações e o total desses ganhos calcular o valor de ganho em cada aplicação:

Tipos de investimentos Valor %
Poupança 23.000,00 26,47% =G524/$G$528
Fundos de Investimentos 35.000,00 40,28% =G525/$G$528
CDB 20.000,00 23,01% =G526/$G$528
Ações 8.900,00 10,24% =G527/$G$528
Total 86.900,00 100,00%


Quantos por cento de 40 são 16?

40,00%=16/40

35% de um número é igual a 175,00. Qual é esse número?
500,00=175/0,35

Um veículo no valor de R$ 35.000,00 está sendo vendido com desconto de 15%. Qual o valor do desconto e o valor a ser pago pelo veículo?

Preço anunciado do veículo 35000,00
Desconto 15,00%
Valor do desconto 5250,00 =H544*H545
Valor total a pagar 29750,00

Verificar a variação percentual do preço das ações da empresa Beta a seguir:

Valor da ação no dia 01-03-2007 1,20
Valor da ação no dia 01-04-2007 1,26
Variação % 5,00% =H554/H553-1

Valor da ação no dia 01-06-99 1,20
Valor da ação no dia 01-08-99 1,14
Variação % -5,00% =H558/H557-1

Verificar qual a margem percentual de lucro sobre o preço de custo obtida numa venda:

Preço de custo 35,00
Preço de venda 50,00
Margem de lucro 42,86% =H566/H565-1


Verificar qual o percentual de desconto sobre o preço de venda a prazo concedido:

Preço a prazo 8000,00
Preço a vista 7300,00
% de desconto -8,75% =H576/H575-1


Uma mercadoria está sendo vendida com a seguinte promoção:
a. qualquer compra pode ser paga com cheque pré-datado para 30 dias sem acréscimo;
b. todas as compras pagas a vista recebem um desconto de 10%.

Verificar qual a taxa de juros mensal efetiva cobrada para quem paga
a prazo:

Preço a vista 90,00
Preço a prazo 100,00
Taxa efetiva mensal 11,11% =H589/H588-1

Função SOMAPRODUTO

Multiplica os componentes correspondentes nas matrizes fornecidas e retorna a soma destes produtos.

Sintaxe: SOMARPRODUTO(matriz1;matriz2;matriz3; ...)
Matriz1, matriz2, matriz3,... são matrizes de 2 a 30 cujos componentes se
multiplicar e depois somar.

Os argumentos da matriz devem ter a mesma dimensão. Se não tiverem, SOMARPRODUTO fornecerá o valor de erro #VALOR!

SOMARPRODUTO trata as entradas da matriz não-numéricas como se fossem zeros.


Exemplos:

A tabela a seguir apresenta o total de vendas das ações da Cia. Petropina no dia 02-09-2007. Nesse dia, foram realizadas vendas da mesma ação a diversos preços.
De posse dos dados da tabela, calcularemos o valor total das vendas do dia e o preço médio ponderado de venda dessa ação.

DISTRIBUIÇÃO DAS VENDAS

Qtd. Vendida Preço Unitário
1000 5,00 Total de Vendas: 108.900,00 - =SOMARPRODUTO(D619:D623;E619:E623)
2000 5,50 Preço Médio
5000 4,90 Ponderado: 4,95
6000 5,80
8000 4,20


No próximo exemplo vamos calcular o prazo médio ponderado das duplicatas a seguir:
RELAÇÃO DAS DUPLICATAS
DATA DE EMISSÃO DATA DE VENCIMENTO VALOR DE EMISSÃO PRAZO(DIAS)
02/06/07 02/07/07 30.000,00 30,00
02/06/07 03/08/07 40.000,00 62,00
02/06/07 26/09/07 50.000,00 116,00
02/06/07 03/10/07 60.000,00 123,00
02/06/07 03/11/07 70.000,00 154,00

Prazo médio ponderado (dias)109,36
=SOMARPRODUTO(D631:D635*E631:E635/SOMA(D631:D635))

CRIANDO FÓRMULAS MATRICIAIS COM CONSTANTES

O que é Uma Constante ?

Um item nomeado que mantém um valor constante por toda a execução de uma fórmula.
Uma constante pode ser uma literal de seqüência de caracteres ou numérica, uma outra constante ou qualquer combinação que inclua operadores aritméticos ou lógicos.

Como Criar Uma Constante:

Criaremos uma constante numa coluna com quatro linhas. Na primeira linha desta coluna digite "={1;2;3;4}", sim digite as chaves também !
Quando você "arrastar para a segunda, terceira e quarta linha todas células ficarão preenchidas com o número 1, que o primeiro valor da constante. Agora entra a fórmula matricial, selecione as quatro linhas e na barra de fórmulas pressione Ctrl+Shift+Enter. O resultado será que cada linha será preenchida com um numéro da constante, ou seja 1,2,3 e 4. Se você "arrastar" por mais de 4 linha um erro será gerado pois a constante só permite 4.

Coluna Constante, antes do pressionamento das teclas
={1;2;3;4}

Coluna Constante, depois do pressionamento das teclas
{={1;2;3;4}}

Exemplo Prático de Utilização de Constante:

Números
4
9 3+ grandes valores 3+ grandes valores
2 15 15
12 15 12
6 15 9
15

=MAIOR(F2:F7;{1;2;3}), Antes do pressionamento das teclas
{=MAIOR(F2:F7;{1;2;3})}, Depois do pressionamento das teclas

No exemplo acima utilizamos a fórmula matricial para fazer um ranking dos três maiores valores.

Podemos também utilizar constantes para fórmulas horizontais:

Fórmulas Matriciais - Outra visão do Excel

Fórmulas Matriciais são de vital importancia, visto que possibilitam inumeras possibilidades, desde fórmulas simples a fórmulas como se fossem banco de dados no Excel.

1 - INTRODUÇÃO

O excel, a princípio, foi criado para resolver o problema de muitos, que com suas cálculadoras efetuavam enormes cálculos e traduziam os complexos resultados para planilhas manuais.Estes cálculos eram tão importantes que várias tomadas de decisões eram neles baseados. Agora, imagine você se alguns destes cálculos estivessem errados ! Muitos negócios poderiam ser prejudicados e quantos prejuízos poderiam ser gerados nas empresas.
E realmente foi isso que o excel fez, minimizou o problema de muitos com a inserção de funções para a criação de fórmulas, e o que é melhor , não prender o usuário a um padrão, ou seja, no excel cada um cria fórmulas com a função que desejar, desde que o resultado seja correto. A Microsoft percebendo o sucesso de seu aplicativo, e percebendo que algumas fórmulas criadas estavam tornando-se complexas demais, resolveu simplificar tudo isso, e aí nasceu as fómulas matriciais.
Podemos dizer então que fórmulas matriciais é o que mais avançado existe de no tocante a fórmulas, e você que estudará este tutorial se destacará em qualquer meio que suas fórmulas forem apresentadas.

2 - DEFININDO FÓRMULA E FUNÇÕES

2.1 - Fórmulas
Fórmulas são equações que executam cálculos sobre valores na planilha. Uma fórmula também pode conter quaisquer dos seguintes itens: funções, referências, operadores e constantes.
2.2 - Funções
Funções são fórmulas predefinidas que efetuam cálculos usando valores específicos, denominados argumentos, em uma determinada ordem ou estrutura. As funções podem ser usadas para executar cálculos simples ou Complexos.
Vamos citar como exemplo a função Arred( ):
=ARRED(A10;2)

Estrutura de uma função:

Estrutura. A estrutura de uma função começa com um sinal de igual (=), seguido do nome da função, um parêntese aberto, os argumentos da função separados por vírgulas e um parêntese de fechamento.

Nome da função. Para obter uma lista das funções disponíveis, clique em uma célula e pressione SHIFT+F3.

Argumentos. Os argumentos podem ser números, texto, valores lógicos, como VERDADEIRO ou FALSO, matrizes , valores de erro como #N/D ou referências de célula.O argumento que você atribuir deve produzir um valor válido para esse argumento. Os argumentos também podem ser constantes, fórmulas ou outras funções.

Dica de ferramenta Argumentos. Uma dica de ferramenta com a sintaxe e argumentos é exibida à medida que você digita a função. Por exemplo, digite =ARRED( e a dica de ferramenta aparecerá. As dicas de ferramenta são exibidas somente para funções internas.

Inserir fórmulas:

Quando você cria uma fórmula que contém uma função, a caixa de diálogo Inserir função ajuda você a inserir funções de planilha. Enquanto você digita uma função na fórmula, a caixa de diálogo Inserir função exibe seu nome, cada um de seus argumentos, as descrições, seu resultado atual e o resultado atual da fórmula inteira.

Funções Aninhadas:

Em determinados casos, é possível você precise usar uma função como um dos argumentos de outra
função. Por exemplo, a fórmula a seguir usa uma função aninhada MÉDIA e compara o resultado com
o valor 50.

=SE(MÉDIA(F1:F5)>18;SOMA((G1:G5);0) - Já Criamos um tópico sobre o assunto.

Quando uma função aninhada é usada como argumento,ela deve retornar o mesmo tipo de valor utilizado pelo argumento. Por exemplo, se o argumento retornar um valor VERDADEIRO ou FALSO, a função aninhada deverá retornar VERDADEIRO ou FALSO. Se não retornar, o Microsoft Excel exibirá um valor de erro #VALOR!

Uma fórmula pode conter até sete níveis de funções aninhadas. Quando a Função B for usada como argumento na Função A, a Função B será de segundo nível. Por exemplo, as funções MÉDIA e SOMA são de segundo nível, pois são argumentos da função SE. Uma função aninhada na função MÉDIA seria de terceiro nível, e assim por diante.

Referências em Fórmulas:

Uma referência identifica uma célula ou um intervalo de células em uma planilha e informa ao Microsoft Excel onde procurar pelos valores ou dados a serem usados em uma fórmula. Com referências, você pode usar dados contidos em partes diferentes de uma planilha em uma fórmula ou usar o valor de uma célula em várias fórmulas. Você também pode se referir a células de outras planilhas na mesma pasta de trabalho e a outras pastas de trabalho. Referências às células de outras pastas de trabalho são chamadas vínculos.

Referências Relativas e Referências Absolutas:

Uma referência relativa em uma fórmula, como A1, é baseada na posição relativa da célula que contém a fórmula e da célula à qual a referência se refere. Se a posição da célula que contém a fórmula se alterar, a referência será alterada.Se você copiar a fórmula ao longo de linhas ou colunas, a referência se ajustará automaticamente. Por padrão, novas fórmulas usam referências relativas.
Por exemplo, se você copiar uma referência relativa que está na célula B2 para a célula B3, a referência será automaticamente ajustada de =A1 para =A2.

Uma referência absoluta de célula em uma fórmula, como $A$1, sempre se refere a uma célula em um local específico. Se a posição da célula que contém a fórmula se alterar, a referência absoluta permanecerá a mesma. Se você copiar a fórmula ao longo de linhas ou colunas, a referência absoluta não se ajustará. Por padrão, novas fórmulas usam referências relativas e você precisa trocá-las para referências absolutas. Por exemplo, se você copiar uma referência absoluta na célula B2 para a célula B3, ela permanecerá a mesma em ambas as células =$A$1.


Uma referência mista tem uma coluna absoluta e linha relativa, ou linha absoluta e coluna relativa. Uma referência de coluna absoluta tem o formato $A1, $B1 e assim por diante.Uma referência de linha absoluta tem o formato A$1, B$1 e assim por diante. Se a posição da célula que contém a fórmula se alterar, a referência relativa será alterada e a referência absoluta não se alterará. Se você co-
piar a fórmula ao longo de linhas ou colunas, a referência relativa se ajustará automaticamente e a referência absoluta não se ajustará. Por exemplo, se você copiar uma referência mista da célula A2 para B3, ela se ajustará de =A$1 para =B$1.

Utilizando Nomes Em Células:

Um nome definido em uma fórmula pode facilitar a compreensão do objetivo da fórmula. Por exemplo,a fórmula =SOMA(PrimeiroTrimestreVendas) talvez seja mais fácil de identificar do que =SOMA(C20:C30).

Os nomes estão disponíveis em qualquer planilha. Por exemplo, se o nome VendasProjetadas referir - se ao intervalo A20:A30 na primeira planilha de uma pasta de trabalho, você poderá usar o nome VendasProjetadas em qualquer outra planilha da mesma pasta de trabalho para referir-ao intervalo A20:A30 na primeira planilha.

Os nomes também podem ser usados para representar fórmulas ou valores que não se alteram (constantes). Por exemplo, você pode usar o nome ImpostoVendas para representar o valor do imposto sobre vendas (como 6,2 por cento) aplicado às transações de vendas.

Você também pode vincular a um nome definido em outra pasta de trabalho ou definir um nome que se refira a células em outra pasta de trabalho.
Por exemplo, a fórmula =SOMA(Vendas.xls!VendasProjetadas)refere-se ao intervalo nomeado VendasProjetadas na pasta de trabalho chamada Vendas.

Diretrizes para nomes:

Quais são os caracteres permitidos?O primeiro caractere de um nome deve ser uma letra ou um sublinhado. Os demais caracteres no nome podem ser letras, números, pontos e sublinhado.

Nomes podem ser referências de células? Os nomes não podem ser iguais a uma referência de célula, como Z$100 ou L1C1.

É possível usar mais de uma palavra? Sim, mas espaços não são permitidos. Os caracteres desublinhado e os pontos podem ser usados como separadores de palavras — por exemplo,
Imposto_Vendas ou Primeiro.Trimestre.

Quantos caracteres podem ser usados? Os nomes podem conter até 255 caracteres.Os nomes diferenciam maiúsculas de minúsculas? Os nomes podem conter letras maiúsculas e minúsculas. O Microsoft Excel não faz distinção entre caracteres maiúsculos e minúsculos em nomes
Por exemplo, se você tinha criado o nome Vendas e, em seguida, criou outro nome chamado VENDAS na mesma pasta de trabalho, o segundo nome substituirá o primeiro.

3 - DEFININDO FÓRMULA MATRICIAIS

Uma fórmula que executa vários cálculos em um ou mais conjuntos de valores e retorna um único resultado ou vários resultados.As fórmulas de matriz ficam entre chaves { } e são inseridas pressionando CTRL+SHIFT+ENTER.).

Fórmulas matriciais agem sobre um ou mais conjuntos de valores, que são conhecidos como argumentos de matriz. Cada argumento de matriz deve ser retangular e cada argumento precisa ter o mesmo número de linhas e/ou o mesmo número de colunas que os outros argumentos.

Além de pouco utilizado, por parecer muito complexo, é um recurso que quase ninguém conhece, mas a partir de agora você que está estudando este tutorial se tornará um expert e sempre recorrerá a elas em seus trabalhos, destacando-se dentre muitos.

Muitos, por preferência, não utilizam o VBA para automatizar suas planilhas, o que acho um erro, então as fórmulas matriciais é a melhor maneira para suprir algumas deficiências das fórmulas tradicionais.

4 - CRIANDO FÓRMULA MATRICIAIS

Aprender a criar fórmulas no Excel é igual a resolver equações de matemática, nada como muitos exercicíos para se tornar um expert.

Vamos dividir as fórmulas por categoria para facilitar o entendimento:

CRIANDO EXPRESSÕES
CRIANDO FÓRMULAS MATRICIAIS COM CONSTANTES
CRIANDO FÓRMULAS MATRICIAIS PARA ANÁLISE DE DADOS
TRABALHANDO COM DATAS E FÓRMULAS MATRICIAIS
OUTRAS UTILIZAÇÕES DAS FÓRMULA MATRICIAIS


4.1 - CRIANDO EXPRESSÕES

Criaremos a seguir uma série de fórmulas ideal para quem trabalho em depto.pessoal e precisa administrar seus dados no Excel

Vamos primeiramente criar uma tabela com os seguintes dados: Nome, Depto. Qualificação e Salário

Criaremos duas consultas: a 1ª verificará quais os funcionários estão alocados no depto de Compras, e a 2ª verificará quais são qualificados na categoria "Q1".

A utilização de expressões Matriciais agilizam muito a construção de fórmulas idênticas para diversas linhas. Na 1ªlinha da coluna que receberá as fórmulas digite a expressão, no nosso caso:
"=B2:B10="Compras"", esta expressão fará uma análise no intervalo de 2 a 10 para verificar se o texto digitado é "Compras". Após a digitação selecione esta linha até a última linha que receberá a expressão, nosso exemplo 10. Com a região selecionada vá até a barra de fórmulas , selecione a fórmula e pressione simultaneamente as teclas Ctrl + Shit + Enter e pronto nossa expressão matricial será criada automaticamente. Repita a operação para a 2ª e 3ª consultas, você irá se surpreender com o resultado!

Na primeira e segunda consulta se os critérios satisfazem a expressão o termo "Verdadeiro" é retornado, senão "Falso". Já na 3ª consulta se as duas colunas satisfizerem os critérios o valor 1 será retornado senão zero.

Todo o segredo das fórmulas matriciais estão no pressionamento simultâneo das teclas Ctrl+Shift+Enter da expressão digitada!

2º Exemplo

Para incrementar mais um pouco a fórmula utilizaremos agora 2 critérios de consulta, o depto. e a Qualificação.

Escolha o Depto. Compras
Escolha a Qualificação Q2

NºDepto. em Qual. 2 =SOMARPRODUTOS((B2:B10=H1)*(C2:C10=H2))

Soma Depto. Em Q1 4.000,00 =SOMARPRODUTOS((B2:B10=H1)*(C2:C10=H2)*D2:D10)


3º Exemplo

Vamos tornar nossa consulta um pouco mais complexa, incrementando com a data de nascimento dos funcionários:

Nome Depto. Qualif Salário Nascimento
Carlos Compras Q1 2.000,00 13/12/50
Romeu Estoque Q2 4.000,00 14/12/50
Martin Fabric Q3 2.000,00 15/12/60
Alberto Compras Q2 2.000,00 16/12/60
Doremi Compras Q1 2.000,00 17/12/60
Mermoz Estoque Q1 3.000,00 18/12/50
Balu Compras Q2 2.000,00 19/12/50
Boresco Fabric Q3 2.000,00 20/12/50
Delpont Compras Q4 2.000,00 21/12/50


Utilizaremos agora fórmulas para pesquisarmos nomes e funcionários que começar com uma determinada letra e também que nasceram num determinado ano, tudo graças às formulas matriciais.



Soma dos salários das Pessoas que começam com a letra D
{=SOMA((ESQUERDA(E275:E283)="D")*H275:H283)} Para validar Tecle Cotrl+Shift+Enter
ou
{=SOMA(SE(ESQUERDA(E275:E283;1)="D";H275:H283;0))} Para validar Tecle Cotrl+Shift+Enter


Nº de pessoas que começas com letra D
{=SOMA((ESQUERDA(E275:E283)="D")*1)} Para validar Tecle Cotrl+Shift+Enter
ou
{=SOMA(SE(ESQUERDA(E275:E283;1)="D";1;""))}

Nº de pessoas que nasceram em 1950
{=SOMA((ANO(I275:I283)=1950)*1)}
ou
{=SOMA(SE(ANO(I275:I283)=1950;1;0))}

{=MÉDIA(SE(H275:H283>2000;H275:H283;""))} Para validar Tecle

Podemos utilizar a mesma tabela para somar os salários de funcionários de um ou mais departamentos.

=SOMA(((Depto="Compras")+(Depto="Estoque"))*Salário
Validar Ctrl+Shift+Enter

A seguir vamos verificar se um mais funcionário pertece à lista de funcionários:

=SE(SOMA((nome="martin")*(Qualif="q3")*1)>0;"Sim";"Não")
Validar Ctrl+Shift+Enter

4º Exemplo

Nosso próximo exemplo é uma amostra de valores com previsão de recebimento e datas de efetivo recebimento. Vamos então criar fórmulas para verificar quanto foram recebidos no devido vencimento e a soma desses valores.

Fatura Previsão Real Valores
1 01/06/06 01/06/06 1.000,00
2 02/06/06 02/06/06 1.000,00 3 03/06/06 03/06/06 1.000,00 4 04/06/06 04/06/06 1.000,00
5 05/06/06 05/06/06 1.000,00
6 06/06/06 08/06/06 1.000,00
7 07/06/06 07/06/06 1.000,00
8 08/06/06 10/06/06 1.000,00
2 - {=SOMARPRODUTO((E413:E420>D413:D420)*1)} Duas faturas foram pagas no vecto.
2.000,00 - {=SOMARPRODUTO((E413:E420>D413:D420)*F413:F420)} Soma das duas faturas

Nb de pagtos real>pagtos previstos
=SOMARPRODUTO((Real>Previsão)*1)
Valores
=SOMARPRODUTO((Real>Previsão)*Valores)

A fórmula acima é ideal para o depto. financeiro
Totaliza-se as quantidades por produto
{=SOMARPRODUTO((C339:G346=J339)*(N(DESLOC(C339:G346;;1))))}

Esta fórmula é ótima, pois economiza muitas funções como "Cont.Se", "Somase" e até mesmo "Se"
que teríamos que utilizar para conseguir os mesmos resultados.

Criando Gráficos em Excel

Com a utilização do Excel podemos criar gráficos de maneira simples e rápida a partir da seleção de dados, o Excel é capaz de gerar gráficos sem a necessidade de definir parâmetros adicionais.

Exemplo

Na coluna A colocaremos os produdos e da coluna B até a G colocaremos algumas datas sequenciais, nas linha abaixo colocaremos o tipo de produto e as quantidades.

Produtos 01/01/2011 02/01/2011 03/01/2011 04/01/2011 05/01/2011 06/01/2011
sapato 2 5 7 9 0 1
tinta 5 4 5 7 8 4
caneta 8 9 10 3 2 6


Terminado isso faremos a seleção dos dados e em Inserir - Gráficos, clicar em Colunas e escolher o tipo de gráfico, neste momento você já pode clicar em concluir e o gráfico será inserido em sua planilha.

Clicando na opção avançar você tem outras opções.

Fixar células em fórmulas

Vamos supor que você esteja fazendo um calculo onde varias outras células referenciam a mesma, para que você não precise digitar sempre o mesmo endereço de célula o Excel nos proporciona um "atalho" para isso

Exemplo

Temos na célula A1 o percentual de 10% na célula B1 100, B2 200 e B3 300 e queremos que as células C1 até C3 referenciem a célula A1 utilizamos a tecla F4 para criar assim uma fixação na referência, então faremos na célula C1=B1*$A$1, isto significa que a célula A1 é fixa, agora é só arrastar para baixo com o mouse e pronto.

Resultado

10% 200 20
100 10
300 30

Função Mínimo / Máximo e Média

Como o nome já diz estas funções servem para calcular o menor valor em um intervalo, o maior valor em um intervalo e a média dos valores selecionados.

Por ser bem simples segue abaixo exemplos de como estas fórmulas funcionam.

Mínimo
=mínimo(1;3;5;0) - retorna 0

Máximo
=máximo(1;3;5;0) - retorna 5

Média
=média(1;3;5;0) - retorna 2,2

Para determinar a separação dos valores é necessário inserir o ponto e virgula para reconhecimento pelo Excel, estes exemplos foram feitos digitando os números, porem você pode utilizar estas fórmulas utilizando intervalo de células.

=MÉDIA(A1:A5)

**Obs.: o Excel reconhece maiúscula, minuscula e até mesmo sem acento.

Conceito de criação de fórmulas em Excel

Quando digitamos na célula um dado que não seja uma fórmula, este dado torna-se uma constante.Isto significa que ele não se altera mais.

Fórmula é um tipo de dado que resulta em um valor. A fórmula sempre se inicia com um sinal de (=) e o seu resultado pode ser alterado sem a necessidade de editar a célula.

Dados aceitos em uma fórmula
números, textos, datas, horas, matrizes e valores lógicos, referências de outras células, planilhas ou pasta de trabalho.

Valor é o resultado da fórmula.

Exemplo

Somar = 1+1
Valor após o enter = 2

Este exemplo aplica-se também para outras operações, abaixo operadores aceitos pelo Excel.

+ Realiza adição (mais).
- Realiza subtração (menos).
* Realiza multiplicação (vezes).
/ Realiza divisão (dividir).
^ Realiza exponenciação (valor elevado).
% Realiza a porcentagem (porcento).

Quando utilizamos expressões, o Excel utiliza a precedência matemática dos operadores para a solução da expressão. Sendo assim, ele calcula nesta exponenciação, multiplicação ou divisão e por ultimo soma e subtração (da esquerda para direita), também é interessante ressaltar que primeiro é calculado o que estiver entre parênteses.

Exitem também Chaves {} e Colchetes[] que explicaremos mais adiante.

Utilizando a ajuda do Excel

Por meio da ajuda do Excel, temos acesso a modelos, dicas e suporte aos diversos comandos.A ajuda do Excel pode ser acessada a partir do botão ajuda, localizado no canto superior direito da tela ou através da tecla de atalho F!.

Caso esteja conectado a internet quando acionamos a ajuda do Excel , este pode se conectar ao site da microsoft para incrementar as opções e os artigos da ajuda.Se não houver uma conexão com a internet, a ajuda utiliza apenas arquivos que fora instalados.

Podemos utilizar o recurso de ajuda de 2 maneiras

1-Digitando a palavra chave na caixa de Pesquisar
2-Navegando através dos tópicos de menu.

Em muitas janelas do Excel o botão de ajuda pode estar localizado ao lado do botão fechar com um sinal de (?).