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:
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:
Funções para SQL
São Utilizadas para determinar quantidade de registros, totais, médias ou dados estatísticos.
Função SUM: Utilizada para somarmos uma determinada coluna de valor numérico ou monetário. Para utilizar esta função no SQL devemos "apelidar" a nova coluna que será criada seguido da palavra AS. Ex: SUM(salários) AS TotalSalário. A coluna TotalSalário será temporária e assim que fecharmos a consulta ela não mais existirá.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, sum(salário) as TotalSalários FROM [dados2$] GROUP BY superior;")
Função AVG: Assim como na função SUM sua utilidade é de calcular a média de certo valor monetária de uma coluna que satisfaça uma certa condição. No Exemplo anterior utilizamos a Cláusula GROUP BY para agruparmos quantos supervisores existiam na empresa; em seguida somamos os salários dos seus subordinados. Agora iremos calcular o salário médio dos subordinados.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários , AVG(salário) AS MédiaSalários FROM [dados2$] GROUP BY superior;")
Função COUNT: Utilizada para contar o número de registros. Neste exemplo utilizamos para saber o número de funcionários que cada supervisor comanda.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários ,AVG(salário) AS MédiaSalários , COUNT(salário) AS QtSalários FROM [dados2$] GROUP BY superior;")
Função MIN e MAX: Utilizadas para obter o menor e o maior registro respectivamente. Neste exemplo conseguimos saber o menor e o maior salário dos subordinados dos respectivos supervisores.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários ,AVG(salário) AS MédiaSalários , COUNT(salário) AS QtSalários , MIN(salário) AS MinSalários, MAX(salário) AS MaxSalários FROM [dados2$] GROUP BY superior;")
Consulta UNION: É usada para combinar o conjunto de registros de duas ou mais tabelas com campos idênticos. Sua sitaxe é a seguinte: "SELECT * FROM Tabela1 UNION ALL SELECT * FROM Tabela2;
Função SUM: Utilizada para somarmos uma determinada coluna de valor numérico ou monetário. Para utilizar esta função no SQL devemos "apelidar" a nova coluna que será criada seguido da palavra AS. Ex: SUM(salários) AS TotalSalário. A coluna TotalSalário será temporária e assim que fecharmos a consulta ela não mais existirá.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, sum(salário) as TotalSalários FROM [dados2$] GROUP BY superior;")
Função AVG: Assim como na função SUM sua utilidade é de calcular a média de certo valor monetária de uma coluna que satisfaça uma certa condição. No Exemplo anterior utilizamos a Cláusula GROUP BY para agruparmos quantos supervisores existiam na empresa; em seguida somamos os salários dos seus subordinados. Agora iremos calcular o salário médio dos subordinados.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários , AVG(salário) AS MédiaSalários FROM [dados2$] GROUP BY superior;")
Função COUNT: Utilizada para contar o número de registros. Neste exemplo utilizamos para saber o número de funcionários que cada supervisor comanda.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários ,AVG(salário) AS MédiaSalários , COUNT(salário) AS QtSalários FROM [dados2$] GROUP BY superior;")
Função MIN e MAX: Utilizadas para obter o menor e o maior registro respectivamente. Neste exemplo conseguimos saber o menor e o maior salário dos subordinados dos respectivos supervisores.
Exemplo:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT superior, SUM(salário) as TotalSalários ,AVG(salário) AS MédiaSalários , COUNT(salário) AS QtSalários , MIN(salário) AS MinSalários, MAX(salário) AS MaxSalários FROM [dados2$] GROUP BY superior;")
Consulta UNION: É usada para combinar o conjunto de registros de duas ou mais tabelas com campos idênticos. Sua sitaxe é a seguinte: "SELECT * FROM Tabela1 UNION ALL SELECT * FROM Tabela2;
Instruções SQL para realização de Consultas
SQL: Estrutura da Linguagem Query é a linguagem padrão para comunicar-se com um banco de dados.Utilizamos esta linguagem para extrair de uma tabela em um formato legível,de acordo com a solicitação do usuário informações que venha satisfazer suas necessidades. As consultas são ferramentas essenciais em qualquer sistema de gerenciamento de banco de dados, pois possibilitam selecionar registros, atualizar tabelas, editar incluir registros.
Comando SELECT: É o principal comando do SQL, Usado principalmente em consultas de seleção. Sua sintaxe é a seguinte:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT * FROM [dados$];")
Onde:SELECT é a instrução; o asterisco( * ) representa toda tabela; FROM [dados$], da tabela dados.
Cláusula ORDER BY: A critério do usuário pode-se ordenar uma tabela simultaneamente quando da execução de uma consulta, para isto basta utilizar o comando ORDER BY. Pode-se ordenar na ascendente (ASC) ou na Descendente (DESC). A sintaxe é a seguinte:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT * FROM [dados$] ORDER BY nome DESC;")
Cláusula WHERE: Tem alguns casos que precisamos filtrar algum registro em algum parametro específico. Por exemplo, precisamos filtrar todos nomes "José", neste caso utilizamos a cláusula WHERE. A cláusula WHERE aceita operadores OR (ou) e AND (e) para trabalho com expressões.
Estrutura JOIN...ON: Podemos utilizar mais de uma tabela para efetuar uma consulta complexa. No nosso caso prático temos duas tabelas com um campo em comum, "nº" ; Numa tabela consta o nome, cargo e bairro; e na outra consta o superior, salário e dataadmissão. Desejamos então saber quais os nomes da tabela 1 tem como superior imediato o sr. João. Devemos então fazer uma associação das duas tabelas com a estrutura JOIN...ON. As tabelas são incluidas na Cláusula JOIN e os
critérios de associação são colocados na cláusula ON. A três tipos de associações: INNER (Equivalência), LEFT (externa à esquerda) e RIGHT( Externa à direita). As associações externas são as que retornam todos os valores de uma tabela e apenas os correspondentes de outra.
Palavra Chave DISTINCT: Esta palavra chave faz com que sejam eliminadas linhas repetidas com base nos valores das linhas da consulta. Se utiliza esta palavra chave para sabermos quantos nomes direntes exisem em uma determinada tabela.
Modificadores TOP e PERCENT: Ambos limitam o conjunto de resultados de uma consulta. Utiliza-se para conhecer-se como por exemplo os maiores salários de uma empresa, os maiores clientes etc.
Utilizarmos a consulta para conhecer os 03 maiores. Obs: Para que a consulta seja verdadeira é necessário ordernarmos a coluna que servirá de resposta, no caso salario.
Cláusula GROUP BY: Serve agrupar dados.
Comando SELECT: É o principal comando do SQL, Usado principalmente em consultas de seleção. Sua sintaxe é a seguinte:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT * FROM [dados$];")
Onde:SELECT é a instrução; o asterisco( * ) representa toda tabela; FROM [dados$], da tabela dados.
Cláusula ORDER BY: A critério do usuário pode-se ordenar uma tabela simultaneamente quando da execução de uma consulta, para isto basta utilizar o comando ORDER BY. Pode-se ordenar na ascendente (ASC) ou na Descendente (DESC). A sintaxe é a seguinte:
Set MinhaTabela = MeuBD.OpenRecordset("SELECT * FROM [dados$] ORDER BY nome DESC;")
Cláusula WHERE: Tem alguns casos que precisamos filtrar algum registro em algum parametro específico. Por exemplo, precisamos filtrar todos nomes "José", neste caso utilizamos a cláusula WHERE. A cláusula WHERE aceita operadores OR (ou) e AND (e) para trabalho com expressões.
Estrutura JOIN...ON: Podemos utilizar mais de uma tabela para efetuar uma consulta complexa. No nosso caso prático temos duas tabelas com um campo em comum, "nº" ; Numa tabela consta o nome, cargo e bairro; e na outra consta o superior, salário e dataadmissão. Desejamos então saber quais os nomes da tabela 1 tem como superior imediato o sr. João. Devemos então fazer uma associação das duas tabelas com a estrutura JOIN...ON. As tabelas são incluidas na Cláusula JOIN e os
critérios de associação são colocados na cláusula ON. A três tipos de associações: INNER (Equivalência), LEFT (externa à esquerda) e RIGHT( Externa à direita). As associações externas são as que retornam todos os valores de uma tabela e apenas os correspondentes de outra.
Palavra Chave DISTINCT: Esta palavra chave faz com que sejam eliminadas linhas repetidas com base nos valores das linhas da consulta. Se utiliza esta palavra chave para sabermos quantos nomes direntes exisem em uma determinada tabela.
Modificadores TOP e PERCENT: Ambos limitam o conjunto de resultados de uma consulta. Utiliza-se para conhecer-se como por exemplo os maiores salários de uma empresa, os maiores clientes etc.
Utilizarmos a consulta para conhecer os 03 maiores. Obs: Para que a consulta seja verdadeira é necessário ordernarmos a coluna que servirá de resposta, no caso salario.
Cláusula GROUP BY: Serve agrupar dados.
Sheets expiration date
Have you thought sometimes pass their sheet to a friend, colleague or a third party and not knowing how they will use them, or worse appropriating their ideas? I put this down to a code where your spreadsheet will expire when the user closes it.
Private Sub Workbook_BeforeClose (Cancel As Boolean)
As Date Dim dtexp
'Select the date which will expire
dtexp = ("29/04/2011") 'here inhibit'
If Date> = # 1/11/2010 # Then 'turn this'
If Date> = dtexp Then 'inhibiting here'
ThisWorkbook.Saved = True
'If you want to send message only remove the single quote of the bottom line or customize
'MsgBox "This file is expired, if auto-delete!"
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
end If
end If
end Sub
** Note will not even to trash
Private Sub Workbook_BeforeClose (Cancel As Boolean)
As Date Dim dtexp
'Select the date which will expire
dtexp = ("29/04/2011") 'here inhibit'
If Date> = # 1/11/2010 # Then 'turn this'
If Date> = dtexp Then 'inhibiting here'
ThisWorkbook.Saved = True
'If you want to send message only remove the single quote of the bottom line or customize
'MsgBox "This file is expired, if auto-delete!"
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
end If
end If
end Sub
** Note will not even to trash
Unprotected multiple sheets at once
Below we reported a very simple way to unprotect multiple sheets at once in excel. It is a code in VBA where you only need to copy and paste our readers.
Note this tip we can use this code for several funionabilidades Excel such as: unprotect worksheets, protect worksheets, spreadsheets, hide, unhide worksheets, etc..
I hope you enjoyed it more this super tip Excel.
Sub unprotect_worksheets()
Dim i As Integer
'Loops in the standard of excel spreadsheets
'If more than 03 worksheets to unprotected change the number 3 to the desired amount
For i = 1 To 3
Sheets (i). Unprotect Password: = "123" 'where 123 is the password you
Next i
MsgBox "Worksheets unprotected successfully!" 'Optional message
end Sub
Note this tip we can use this code for several funionabilidades Excel such as: unprotect worksheets, protect worksheets, spreadsheets, hide, unhide worksheets, etc..
I hope you enjoyed it more this super tip Excel.
Sub unprotect_worksheets()
Dim i As Integer
'Loops in the standard of excel spreadsheets
'If more than 03 worksheets to unprotected change the number 3 to the desired amount
For i = 1 To 3
Sheets (i). Unprotect Password: = "123" 'where 123 is the password you
Next i
MsgBox "Worksheets unprotected successfully!" 'Optional message
end Sub
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.
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.
Suplementos em Excel - XLA
DEFINIÇÃO DA MICROSOFT
Os suplementos são programas que adicionam comandos e recursos opcionais ao Microsoft Excel.
Antes de usar um suplemento, você precisa instalá-lo no computador e, em seguida, carregá-lo
no Microsoft Excel. Por padrão, os suplementos (arquivos *.xla) são instalados na pasta Biblioteca
da pasta do Microsoft Excel. O carregamento de um suplemento disponibiliza o recurso no
Microsoft Excel e adiciona todos os comandos associados aos menus adequados. Os programas
suplementares listados na tabela a seguir são instalados, por padrão, na pasta Biblioteca, ou em
uma de suas subpastas, na pasta do Microsoft Excel. Se você não conseguir localizar determinado
suplemento no disco rígido ou em uma unidade da rede, poderá instalá-lo. Após instalar um
suplemento, será necessário usar o comando Suplementos (menu Ferramentas) para carregá-lo no
Microsoft Excel.
A partir dessa breve explicação vamos desenvolver nossa aplicação uma Agenda simples, porem muito funcional.
As Ferramentas necessárias
Para a criação de nossa Agenda de Tarefas precisaremos de um formulário para a inclusão e
consulta das tarefas agendadas , de uma planilha que servirá para armazenar as informações e ou-
tra planilha que servirá como relatório caso se deseje imprimir as anotações do dia.
O Formulário
Crie o formulário Agenda utilizando as especificações que fazem parte da Tabela a seguir:
Objeto Propriedade Ajuste
Formulário Name frmAgenda
Caption Anote Seus Afazeres e Lembretes para o ano de 2011
TextBox1 Name BoxDia
Font Takoma 10
TextAlign 2 - Center
TextBox2 Name BoxAnotações
Font Takoma 8
TextAlign 1 - Left
EnterKeyBehavior True
MultiLine True
TextBox3 Name BoxResumo
Font Takoma 8
TextAlign 1 - Left
EnterKeyBehavior True
MultiLine True
ScroollBars 3 - FmScrollBarsBoth
CommandButton1 Name CmdSair
Caption Incluir/Alterar
CommandButton2 Name CmdImpressão
Caption Imprimir
Label1 Name Label1
Caption Dia/Mês
Label2 Name Label2
Caption Hora
Label3 Name Label3
Caption Hora
Label4 Name Label4
Caption Anotações
Label5 Name Label5
Caption Anotação
A Tabela
Crie um planilha que servirá de tabela para armazenar os dados diários de sua agenda.
Está tabela terá 367 linhas que serão os dias do ano e 25 colunas que serão as horas do dia em que serão anotados os afazeres. Como os campos da tabela são fixos 367 linhas por 24 colunas, utilizaremos as funções internas do Excel para trabalhar os dados com a mesma eficiência.
A Agenda
Crie uma planilha para que você possa ter suas informações diárias em mãos através de um formulário, onde você poderá imprimi-la e levar com você para obde você quiser. Nem sempre as informações somente virtuais são necessárias, tem ocasiões que necessitamos de um relatório para trabalharmos.
O Programa Agenda
Como funcionará o Programa Agenda:
Com o formulário ativado você deverá digitar um dia do ano de 2011 no BoxDia e automaticamente o sistema responderá fazendo uma varreadura na tabela "datas" para encontrar o dia digitado e preencherá o ComboHora com a 1ª Hora do dia, no caso 1:00, e o BoxResumo com as anotações horárias. Para digitar a tarefa ou afazer de uma determinada hora basta preencher o BoxAnotação e clicar no botão Incluir/Alterar.
Codificação do Programa Agenda
Inclua o seguinte código no procedimento UserForm_Activate( ) do formulário:
Private Sub UserForm_Activate()
Dim Cont
Me.ComboHora.Clear
For Cont = 1 To 24
Me.ComboHora.AddItem Format(Cont & ":00", "h:mm")
Next
Me.BoxDia = Format(Date, "dd/mm")
End Sub
Como uma agenda diária serve para anotações de seus afazeres em determinadas horas,
precisamos então de um objeto em que possamos selecionar determinada hora. Esse objeto em nosso aplicativo é o ComboHora.
Para se preencher um ComboBox existem duas maneiras:
1º Utilizando a propriedade RowSource do ComboBox, mencionado o intervalo de células que se deseja preencher o combo;
2º Preenchendo o ComboBox em tempo de execução, ou seja, preencher o ComboBox toda vez que o Userform for carregado.
Para se preencher um ComboBox em tempo de execução devemos utilizar a propriedade AddItem
Ex.: ComboExemplo.AddItem "Laranja"
ComboExemplo.AddItem "Banana"
No exemplo acima teremos um ComboBox com uma lista preenchida com os itens Laranja e Banana.
No nosso programa precisaremos preencher uma Combo com o intervalo de horas 1:00 e 24:00.Poderíamos preencher essa lista um a um como no exemplo anterior, mas é nesta hora que a lógica de programação toma conta. utilizaremos a instrução For... Next... , que significa percorrer um certo intervalo, e em cima deste intervalo poderemos ir preenchendo o ComboBox.
Private Sub boxdia_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.BoxDia = "" Then
Else
If IsDate(Me.BoxDia) Then
Me.ComboHora.ListIndex = -1
Me.BoxDia = Format(Me.BoxDia, "dd/mm")
Me.ComboHora.ListIndex = 0
Else
MsgBox "Data inválida...", vbCritical, "Minhas tarefas e lembretes.."
Cancel = True
End If
End If
End Sub
Detalhamento do código no procedimento boxdia_Exit( ) do formulário:
Como o BoxDia é a principal interface do aplicativo, é importantíssimo que mesmo seja preenchido corretamente para o perfeito funcionamento do aplicativo. Para que isso ocorra utilizamos uma função interna no Excel chamada IsDate. A função IsDate tem como objetivo checar se no objeto TextBox foi digitada uma data válida. Se sim o BoxDia é validado, senão o cursor permanecerá no TextBox até a correta digitação. Isto é feito graças ao ByVal da evento Exit. onde Cancel = true permi-
te que só se siga adiante se a informação for correta.
Outra Função utilizada é a Format, que permite deixar o TextBox do jeito que se quer. No código acima no BoxDia deve ser digitado uma data. Quando esta data for validada o a função Format transforma esta data em dia e mês já que o ano não é necessário ano pois a agenda é do ano de 2011.
No caso do BoxDia se for digitado 01/01/2011 a função Format retornará o BoxDia em 01/01.
Inclua o seguinte código no procedimento ComboHora_Click( ) do formulário:
Dê dois cliques sobre o ComboHora e o evento ComboHora_Click( ) é ativado. Dentro do evento digite o seguinte código:
Private Sub combohora_Click()
Me.PesquisarData
End Sub
Detalhamento do código no procedimento ComboHora_Click( ) do formulário:
Dentro do evento digitamos a palavra reservada "Me", que tem como função substituir o nome do formulário. Se digitarmos "FrmAgenda.PesquisarData" também está correto, e PesquisarData simplesmente também, porém o ideal é que se digite o endereço onde o código está alocado para que o sistema saiba qual código você está se referindo, pois pode ocorrer de ter dois códigos com o mesmo nome em módulos de programas diferentes, o que é permitido.
Já o termo "PesquisarData" é um código de programação que você verá abaixo. Com isso você acaba de aprender que podemos chamar um código de programação dentro de outro código, basta referenciar corretamente. Qual é a vantagem de se chamar um código dentro de outro código ?
Simples, para que um código não se torne muito longo.
Inclua o seguinte código no procedimento PesquisarData( ) dentro do módulo do formulário:
Sub PesquisarData()
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Cont
i = 1
Me.BoxAnotação = ""
Me.BoxResumo = ""
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Me.BoxAnotação = Plan2.Cells(i, Me.ComboHora.ListIndex + 2)
For Cont = 1 To 24
Me.BoxResumo = Me.BoxResumo & Chr(10) & Plan2.Cells(1, Cont + 1) & _
":00 - " & Plan2.Cells(i, Cont + 1)
Next
Exit For
End If
Next
End Sub
Detalhamento do código PesquisarData:
Com a planilha "datas" criada devemos primeiramente saber que quando folheamos uma agenda a principal parâmetro é a data. A Data é a base de toda consulta em nossa agenda. Aqui no nosso programa também, as datas são o centro de todas as atenções, e em cima disto vamos selecionar todas as células que fazem parte das datas de nossa agenda, no nosso caso é o intervalo compreendido "A2:A367" e clicando sobre a caixa de nomes vamos renomear este intervalo para "nDatas".
Como o Código PesquisarData funciona:
O sistema irá percorrer todo intervalo nDatas que você nomeou e localizando a data que você digitar no BoxDia irá automaticamente preencher o BoxAnotação com a anotação da 1ª hora, e irá preencher o BoxResumo com todas anotações do dia em ordem de hora.
Utilizamos a instrução For Each in para percorrer todo intervalo nDatas para descobrir qual célula tem a data igual ao BoxDia.
Inclua o seguinte código no procedimento CmdIncluir_Click( ) do formulário:
Private Sub CmdIncluir_Click()
Me.Incluir_Alterar
Me.PesquisarData
End Sub
Detalhamento do código no procedimento ComboHora_Click( ) do formulário:
Como no ComboHora dentro o procedimento CndIncluir_Click( ) foi digitado o nome de outro procedimento, o Incluir_alterar. A Função deste botão é de Incluir uma nova anotação ou alterar alguma anotação existente.
Criação do procedimento incluir_Alterar:
Sub Incluir_Alterar()
Dim Célula As Excel.Range
Dim StrData, i, nLinha
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Plan2.Cells(i, Me.ComboHora.ListIndex + 2) = Me.BoxAnotação
Exit For
End If
Next
End Sub
Neste procedimento utilizamos praticamente a mesma rotina do procedimento PesquisarData, pois já que a tabela é fixa (366 linhas x 24 colunas ) a única função deste procedimento é localizar um dia e alterar a anotação existente ou incluir uma anotação caso a célula esteja vazia.
Basicamente fica fácil demonstrar que com poucas linhas de comando e poucos objetos pode-se fazer um pequeno programa e muito eficiente.
Inclua o seguinte código no procedimento CmdImpressão_Click( ) do formulário:
Private Sub CmdImpressão_Click()
On Error GoTo aviso
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Col
If Me.BoxDia = "" Then
MsgBox "Digite um dia para impressão", vbInformation, "Agenda"
Else
AGENDA.Range("A1") = Day(Me.BoxDia)
AGENDA.Range("B2") = UCase(Format(Me.BoxDia, "MMMM"))
AGENDA.Range("D2") = UCase(Dia_Semana(Me.BoxDia))
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
For Col = 1 To 24
AGENDA.Range("B" & Col + 4) = Plan2.Cells(i, Col + 1)
Next Col
Exit For
End If
Next
AGENDA.Copy
End If
Exit Sub
aviso:
If Err.Number = 18 Then
Else
MsgBox Err.Number & "-" & Err.Description
End If
End Sub
Detalhamento do código no procedimento CmdImpressão_Click( ) do formulário:
Como nos procedimentos Incluir_Alterar_Click( ) e PesquisarData utilizamos aqui a instrução For Each Next para percorrer a tabela de datas e encontrando a data digitada no BoxDia o sistema preencherá a planilha Agenda e fará uma cópia da mesma para que usuário possa vizualiza-la e se desejar até imprimir o relatório. Dentro do procedimento CmdImpressão_Click( ) incluímos uma novi-
dade: a função Dia_Semana.
Esta função examina o dia da semana (WeekDay) do BoxDia e converte para extenso.
Como Ativar um Suplemento ?
Quando se converte um arquivo ".xls" para suplemento ".xla" temos que ter algum meio de ativar o aplicativo, pois como já esclarecemos, um suplemento do Excel se torna invisível ao nossos olhos, apesar de estar ativado. Existem duas maneiras de faze-lo, ou criando um botão na barra de ferramentas ou um itém de menu na barra de menus. No aplicativo agenda vamos inserir um itém de menu para poder ativar-lo e podemos opera-lo.
Inserindo um Item de Menu
No Editor de VBA, no menu inserir escolha Módulo. Temos que criar dois códigos, um para criar um item de menu e outro para excluir este mesmo item, ou seja quando se ativa um suplemento o item de menu será criado e quando se desativa um suplemento exclui-se este item de menu.
Para se incluir um item de menu digite o seguinte procedimento no Módulo 1 que você acabou se inserir:
Sub CriarItemMenu()
Dim ctlMenuItem As CommandBarControl
Dim intCount As Integer
Dim cBut As CommandBarButton
Dim strBarName As String
Dim lngID As Long
Call ExcluirItemMenu
strBarName = QUAL_MENU
Do
Select Case intCount
Case 0
lngID = 846
Case 1
lngID = 3823
Case 2
lngID = 748
Case 3
lngID = 3
End Select
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Id:=lngID, recursive:=True)
Loop Until (Not ctlMenuItem Is Nothing) Or intCount = 3
If Not ctlMenuItem Is Nothing Then
Set cBut = ctlMenuItem.Parent.Controls.Add(before:=ctlMenuItem.Index + 1, _
temporary:=True)
With cBut
.FaceId = 125
.Caption = TÍTULO_ITEM_MENU
.OnAction = ThisWorkbook.Name & MACRO_ITEM_MENU
.Tag = TAG_ITEM_MENU
End With
End If
End Sub
Para que um item de menu seja corretamente alocado em um menu é necessário que o código avalie o menu verificando se já não existe um item de menu com as mesmas características, para se evitar tal falha dentro do código CriarItemMenu ( ) inseri o código ExcluiItemMenu ( ), ou seja o sistema primeiro excluirá um item de menu chamado "Minha Agenda Pessoal" para depois inclui-lo.
Segue abaixo código do procedimento ExcluirItemMenu:
Sub ExcluirItemMenu()
Dim ctlMenu As CommandBarControl
Dim ctlMenuItem As CommandBarControl
Dim strBarName As String
strBarName = QUAL_MENU
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Tag:=TAG_ITEM_MENU, recursive:=True)
If Not ctlMenuItem Is Nothing Then ctlMenuItem.Delete
End Sub
Com o código para criação do item de menu pronto, como fazer que o sistema o leia automaticamente ? simples, vamos utilizar o evento de pasta Workbook_Open( ), ou seja todos procedimentos digitados no Workbook_Open são reconhecidos toda vez que o suplemento é ativado.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Módulo1.ExcluirItemMenu
End Sub
Private Sub Workbook_Open()
Módulo1.CriarItemMenu
End Sub
Convertendo um Arquivo XLS em Suplemento(XLA)
Esta é a parte mais simples, converter um arquivo XLS em XLA. Basta ir no Menu Arquivo(F12)item de menu Salvar Como , e no DropDown Salvar Tipo Como escolha Suplemento do Microsoft Excel.
Você pode salvar seu suplemento em quanquer diretório, mas se você quizer que seu arquivo fique visivel em na caixa de dialogo padrão do office chamada "suplementos" salve seu arquivo no diretorio "Bibliote" localizado dentro o SubDiretório Office,. Microsoft Office em Arquivos de Programas.
Depois de salva seu 1ºsuplemento, basta ativar a caixa de dialogo "suplementos".
Abaixo coloco todo o codigo da nossa agenda.
Modulo 1
Public Const QUAL_MENU As String = "Worksheet Menu Bar"
Public Const TÍTULO_ITEM_MENU As String = "&Minha Agenda Pessoal"
Public Const MACRO_ITEM_MENU As String = "!ExibirFormulário"
Public Const TAG_ITEM_MENU As String = "AGENDA"
Sub CriarItemMenu()
Dim ctlMenuItem As CommandBarControl
Dim intCount As Integer
Dim cBut As CommandBarButton
Dim strBarName As String
Dim lngID As Long
Call ExcluirItemMenu
strBarName = QUAL_MENU
Do
Select Case intCount
Case 0
lngID = 846
Case 1
lngID = 3823
Case 2
lngID = 748
Case 3
lngID = 3
End Select
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Id:=lngID, recursive:=True)
Loop Until (Not ctlMenuItem Is Nothing) Or intCount = 3
If Not ctlMenuItem Is Nothing Then
Set cBut = ctlMenuItem.Parent.Controls.Add(before:=ctlMenuItem.Index + 1, _
temporary:=True)
With cBut
.FaceId = 125
.Caption = TÍTULO_ITEM_MENU
.OnAction = ThisWorkbook.Name & MACRO_ITEM_MENU
.Tag = TAG_ITEM_MENU
End With
End If
End Sub
Sub ExcluirItemMenu()
Dim ctlMenu As CommandBarControl
Dim ctlMenuItem As CommandBarControl
Dim strBarName As String
strBarName = QUAL_MENU
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Tag:=TAG_ITEM_MENU, recursive:=True)
If Not ctlMenuItem Is Nothing Then ctlMenuItem.Delete
End Sub
Sub ExibirFormulário()
FrmAgenda.Show
End Sub
No Formulário
Private Function Dia_Semana(nDia As Date)
If WeekDay(nDia) = 1 Then
Dia_Semana = "Domingo"
ElseIf WeekDay(nDia) = 2 Then
Dia_Semana = "Segunda"
ElseIf WeekDay(nDia) = 3 Then
Dia_Semana = "Terça"
ElseIf WeekDay(nDia) = 4 Then
Dia_Semana = "Quarta"
ElseIf WeekDay(nDia) = 5 Then
Dia_Semana = "Quinta"
ElseIf WeekDay(nDia) = 6 Then
Dia_Semana = "Sexta"
ElseIf WeekDay(nDia) = 7 Then
Dia_Semana = "Sábado"
Else
Dia_Semana = "Desconhecido"
End If
End Function
Private Sub CmdImpressão_Click()
On Error GoTo aviso
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Col
If Me.BoxDia = "" Then
MsgBox "Digite um dia para impressão", vbInformation, "Agenda"
Else
AGENDA.Range("A1") = Day(Me.BoxDia)
AGENDA.Range("B2") = UCase(Format(Me.BoxDia, "MMMM"))
AGENDA.Range("D2") = UCase(Dia_Semana(Me.BoxDia))
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
For Col = 1 To 24
AGENDA.Range("B" & Col + 4) = Plan2.Cells(i, Col + 1)
Next Col
Exit For
End If
Next
AGENDA.Copy
End If
Exit Sub
aviso:
If Err.Number = 18 Then
Else
MsgBox Err.Number & "-" & Err.Description
End If
End Sub
Private Sub CmdIncluir_Click()
Me.Incluir_Alterar
Me.PesquisarData
End Sub
Private Sub CmdSair_Click()
End
End Sub
Private Sub combohora_Click()
Me.PesquisarData
End Sub
Private Sub boxdia_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.BoxDia = "" Then
Else
If IsDate(Me.BoxDia) Then
Me.ComboHora.ListIndex = -1
Me.BoxDia = Format(Me.BoxDia, "dd/mm")
Me.ComboHora.ListIndex = 0
Else
MsgBox "Data inválida...", vbCritical, "Minhas tarefas e lembretes.."
Cancel = True
End If
End If
End Sub
Private Sub UserForm_Activate()
Dim Cont
Me.ComboHora.Clear
For Cont = 1 To 24
Me.ComboHora.AddItem Format(Cont & ":00", "h:mm")
Next
Me.BoxDia = Format(Date, "dd/mm")
End Sub
Sub PesquisarData()
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Cont
i = 1
Me.BoxAnotação = ""
Me.BoxResumo = ""
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Me.BoxAnotação = Plan2.Cells(i, Me.ComboHora.ListIndex + 2)
For Cont = 1 To 24
Me.BoxResumo = Me.BoxResumo & Chr(10) & Plan2.Cells(1, Cont + 1) & _
":00 - " & Plan2.Cells(i, Cont + 1)
Next
Exit For
End If
Next
End Sub
Sub Incluir_Alterar()
Dim Célula As Excel.Range
Dim StrData, i, nLinha
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Plan2.Cells(i, Me.ComboHora.ListIndex + 2) = Me.BoxAnotação
Exit For
End If
Next
End Sub
E em Esta_Pasta_de_Trabalho
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Módulo1.ExcluirItemMenu
End Sub
Private Sub Workbook_Open()
Módulo1.CriarItemMenu
End Sub
Os suplementos são programas que adicionam comandos e recursos opcionais ao Microsoft Excel.
Antes de usar um suplemento, você precisa instalá-lo no computador e, em seguida, carregá-lo
no Microsoft Excel. Por padrão, os suplementos (arquivos *.xla) são instalados na pasta Biblioteca
da pasta do Microsoft Excel. O carregamento de um suplemento disponibiliza o recurso no
Microsoft Excel e adiciona todos os comandos associados aos menus adequados. Os programas
suplementares listados na tabela a seguir são instalados, por padrão, na pasta Biblioteca, ou em
uma de suas subpastas, na pasta do Microsoft Excel. Se você não conseguir localizar determinado
suplemento no disco rígido ou em uma unidade da rede, poderá instalá-lo. Após instalar um
suplemento, será necessário usar o comando Suplementos (menu Ferramentas) para carregá-lo no
Microsoft Excel.
A partir dessa breve explicação vamos desenvolver nossa aplicação uma Agenda simples, porem muito funcional.
As Ferramentas necessárias
Para a criação de nossa Agenda de Tarefas precisaremos de um formulário para a inclusão e
consulta das tarefas agendadas , de uma planilha que servirá para armazenar as informações e ou-
tra planilha que servirá como relatório caso se deseje imprimir as anotações do dia.
O Formulário
Crie o formulário Agenda utilizando as especificações que fazem parte da Tabela a seguir:
Objeto Propriedade Ajuste
Formulário Name frmAgenda
Caption Anote Seus Afazeres e Lembretes para o ano de 2011
TextBox1 Name BoxDia
Font Takoma 10
TextAlign 2 - Center
TextBox2 Name BoxAnotações
Font Takoma 8
TextAlign 1 - Left
EnterKeyBehavior True
MultiLine True
TextBox3 Name BoxResumo
Font Takoma 8
TextAlign 1 - Left
EnterKeyBehavior True
MultiLine True
ScroollBars 3 - FmScrollBarsBoth
CommandButton1 Name CmdSair
Caption Incluir/Alterar
CommandButton2 Name CmdImpressão
Caption Imprimir
Label1 Name Label1
Caption Dia/Mês
Label2 Name Label2
Caption Hora
Label3 Name Label3
Caption Hora
Label4 Name Label4
Caption Anotações
Label5 Name Label5
Caption Anotação
A Tabela
Crie um planilha que servirá de tabela para armazenar os dados diários de sua agenda.
Está tabela terá 367 linhas que serão os dias do ano e 25 colunas que serão as horas do dia em que serão anotados os afazeres. Como os campos da tabela são fixos 367 linhas por 24 colunas, utilizaremos as funções internas do Excel para trabalhar os dados com a mesma eficiência.
A Agenda
Crie uma planilha para que você possa ter suas informações diárias em mãos através de um formulário, onde você poderá imprimi-la e levar com você para obde você quiser. Nem sempre as informações somente virtuais são necessárias, tem ocasiões que necessitamos de um relatório para trabalharmos.
O Programa Agenda
Como funcionará o Programa Agenda:
Com o formulário ativado você deverá digitar um dia do ano de 2011 no BoxDia e automaticamente o sistema responderá fazendo uma varreadura na tabela "datas" para encontrar o dia digitado e preencherá o ComboHora com a 1ª Hora do dia, no caso 1:00, e o BoxResumo com as anotações horárias. Para digitar a tarefa ou afazer de uma determinada hora basta preencher o BoxAnotação e clicar no botão Incluir/Alterar.
Codificação do Programa Agenda
Inclua o seguinte código no procedimento UserForm_Activate( ) do formulário:
Private Sub UserForm_Activate()
Dim Cont
Me.ComboHora.Clear
For Cont = 1 To 24
Me.ComboHora.AddItem Format(Cont & ":00", "h:mm")
Next
Me.BoxDia = Format(Date, "dd/mm")
End Sub
Como uma agenda diária serve para anotações de seus afazeres em determinadas horas,
precisamos então de um objeto em que possamos selecionar determinada hora. Esse objeto em nosso aplicativo é o ComboHora.
Para se preencher um ComboBox existem duas maneiras:
1º Utilizando a propriedade RowSource do ComboBox, mencionado o intervalo de células que se deseja preencher o combo;
2º Preenchendo o ComboBox em tempo de execução, ou seja, preencher o ComboBox toda vez que o Userform for carregado.
Para se preencher um ComboBox em tempo de execução devemos utilizar a propriedade AddItem
Ex.: ComboExemplo.AddItem "Laranja"
ComboExemplo.AddItem "Banana"
No exemplo acima teremos um ComboBox com uma lista preenchida com os itens Laranja e Banana.
No nosso programa precisaremos preencher uma Combo com o intervalo de horas 1:00 e 24:00.Poderíamos preencher essa lista um a um como no exemplo anterior, mas é nesta hora que a lógica de programação toma conta. utilizaremos a instrução For... Next... , que significa percorrer um certo intervalo, e em cima deste intervalo poderemos ir preenchendo o ComboBox.
Private Sub boxdia_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.BoxDia = "" Then
Else
If IsDate(Me.BoxDia) Then
Me.ComboHora.ListIndex = -1
Me.BoxDia = Format(Me.BoxDia, "dd/mm")
Me.ComboHora.ListIndex = 0
Else
MsgBox "Data inválida...", vbCritical, "Minhas tarefas e lembretes.."
Cancel = True
End If
End If
End Sub
Detalhamento do código no procedimento boxdia_Exit( ) do formulário:
Como o BoxDia é a principal interface do aplicativo, é importantíssimo que mesmo seja preenchido corretamente para o perfeito funcionamento do aplicativo. Para que isso ocorra utilizamos uma função interna no Excel chamada IsDate. A função IsDate tem como objetivo checar se no objeto TextBox foi digitada uma data válida. Se sim o BoxDia é validado, senão o cursor permanecerá no TextBox até a correta digitação. Isto é feito graças ao ByVal da evento Exit. onde Cancel = true permi-
te que só se siga adiante se a informação for correta.
Outra Função utilizada é a Format, que permite deixar o TextBox do jeito que se quer. No código acima no BoxDia deve ser digitado uma data. Quando esta data for validada o a função Format transforma esta data em dia e mês já que o ano não é necessário ano pois a agenda é do ano de 2011.
No caso do BoxDia se for digitado 01/01/2011 a função Format retornará o BoxDia em 01/01.
Inclua o seguinte código no procedimento ComboHora_Click( ) do formulário:
Dê dois cliques sobre o ComboHora e o evento ComboHora_Click( ) é ativado. Dentro do evento digite o seguinte código:
Private Sub combohora_Click()
Me.PesquisarData
End Sub
Detalhamento do código no procedimento ComboHora_Click( ) do formulário:
Dentro do evento digitamos a palavra reservada "Me", que tem como função substituir o nome do formulário. Se digitarmos "FrmAgenda.PesquisarData" também está correto, e PesquisarData simplesmente também, porém o ideal é que se digite o endereço onde o código está alocado para que o sistema saiba qual código você está se referindo, pois pode ocorrer de ter dois códigos com o mesmo nome em módulos de programas diferentes, o que é permitido.
Já o termo "PesquisarData" é um código de programação que você verá abaixo. Com isso você acaba de aprender que podemos chamar um código de programação dentro de outro código, basta referenciar corretamente. Qual é a vantagem de se chamar um código dentro de outro código ?
Simples, para que um código não se torne muito longo.
Inclua o seguinte código no procedimento PesquisarData( ) dentro do módulo do formulário:
Sub PesquisarData()
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Cont
i = 1
Me.BoxAnotação = ""
Me.BoxResumo = ""
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Me.BoxAnotação = Plan2.Cells(i, Me.ComboHora.ListIndex + 2)
For Cont = 1 To 24
Me.BoxResumo = Me.BoxResumo & Chr(10) & Plan2.Cells(1, Cont + 1) & _
":00 - " & Plan2.Cells(i, Cont + 1)
Next
Exit For
End If
Next
End Sub
Detalhamento do código PesquisarData:
Com a planilha "datas" criada devemos primeiramente saber que quando folheamos uma agenda a principal parâmetro é a data. A Data é a base de toda consulta em nossa agenda. Aqui no nosso programa também, as datas são o centro de todas as atenções, e em cima disto vamos selecionar todas as células que fazem parte das datas de nossa agenda, no nosso caso é o intervalo compreendido "A2:A367" e clicando sobre a caixa de nomes vamos renomear este intervalo para "nDatas".
Como o Código PesquisarData funciona:
O sistema irá percorrer todo intervalo nDatas que você nomeou e localizando a data que você digitar no BoxDia irá automaticamente preencher o BoxAnotação com a anotação da 1ª hora, e irá preencher o BoxResumo com todas anotações do dia em ordem de hora.
Utilizamos a instrução For Each in para percorrer todo intervalo nDatas para descobrir qual célula tem a data igual ao BoxDia.
Inclua o seguinte código no procedimento CmdIncluir_Click( ) do formulário:
Private Sub CmdIncluir_Click()
Me.Incluir_Alterar
Me.PesquisarData
End Sub
Detalhamento do código no procedimento ComboHora_Click( ) do formulário:
Como no ComboHora dentro o procedimento CndIncluir_Click( ) foi digitado o nome de outro procedimento, o Incluir_alterar. A Função deste botão é de Incluir uma nova anotação ou alterar alguma anotação existente.
Criação do procedimento incluir_Alterar:
Sub Incluir_Alterar()
Dim Célula As Excel.Range
Dim StrData, i, nLinha
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Plan2.Cells(i, Me.ComboHora.ListIndex + 2) = Me.BoxAnotação
Exit For
End If
Next
End Sub
Neste procedimento utilizamos praticamente a mesma rotina do procedimento PesquisarData, pois já que a tabela é fixa (366 linhas x 24 colunas ) a única função deste procedimento é localizar um dia e alterar a anotação existente ou incluir uma anotação caso a célula esteja vazia.
Basicamente fica fácil demonstrar que com poucas linhas de comando e poucos objetos pode-se fazer um pequeno programa e muito eficiente.
Inclua o seguinte código no procedimento CmdImpressão_Click( ) do formulário:
Private Sub CmdImpressão_Click()
On Error GoTo aviso
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Col
If Me.BoxDia = "" Then
MsgBox "Digite um dia para impressão", vbInformation, "Agenda"
Else
AGENDA.Range("A1") = Day(Me.BoxDia)
AGENDA.Range("B2") = UCase(Format(Me.BoxDia, "MMMM"))
AGENDA.Range("D2") = UCase(Dia_Semana(Me.BoxDia))
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
For Col = 1 To 24
AGENDA.Range("B" & Col + 4) = Plan2.Cells(i, Col + 1)
Next Col
Exit For
End If
Next
AGENDA.Copy
End If
Exit Sub
aviso:
If Err.Number = 18 Then
Else
MsgBox Err.Number & "-" & Err.Description
End If
End Sub
Detalhamento do código no procedimento CmdImpressão_Click( ) do formulário:
Como nos procedimentos Incluir_Alterar_Click( ) e PesquisarData utilizamos aqui a instrução For Each Next para percorrer a tabela de datas e encontrando a data digitada no BoxDia o sistema preencherá a planilha Agenda e fará uma cópia da mesma para que usuário possa vizualiza-la e se desejar até imprimir o relatório. Dentro do procedimento CmdImpressão_Click( ) incluímos uma novi-
dade: a função Dia_Semana.
Esta função examina o dia da semana (WeekDay) do BoxDia e converte para extenso.
Como Ativar um Suplemento ?
Quando se converte um arquivo ".xls" para suplemento ".xla" temos que ter algum meio de ativar o aplicativo, pois como já esclarecemos, um suplemento do Excel se torna invisível ao nossos olhos, apesar de estar ativado. Existem duas maneiras de faze-lo, ou criando um botão na barra de ferramentas ou um itém de menu na barra de menus. No aplicativo agenda vamos inserir um itém de menu para poder ativar-lo e podemos opera-lo.
Inserindo um Item de Menu
No Editor de VBA, no menu inserir escolha Módulo. Temos que criar dois códigos, um para criar um item de menu e outro para excluir este mesmo item, ou seja quando se ativa um suplemento o item de menu será criado e quando se desativa um suplemento exclui-se este item de menu.
Para se incluir um item de menu digite o seguinte procedimento no Módulo 1 que você acabou se inserir:
Sub CriarItemMenu()
Dim ctlMenuItem As CommandBarControl
Dim intCount As Integer
Dim cBut As CommandBarButton
Dim strBarName As String
Dim lngID As Long
Call ExcluirItemMenu
strBarName = QUAL_MENU
Do
Select Case intCount
Case 0
lngID = 846
Case 1
lngID = 3823
Case 2
lngID = 748
Case 3
lngID = 3
End Select
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Id:=lngID, recursive:=True)
Loop Until (Not ctlMenuItem Is Nothing) Or intCount = 3
If Not ctlMenuItem Is Nothing Then
Set cBut = ctlMenuItem.Parent.Controls.Add(before:=ctlMenuItem.Index + 1, _
temporary:=True)
With cBut
.FaceId = 125
.Caption = TÍTULO_ITEM_MENU
.OnAction = ThisWorkbook.Name & MACRO_ITEM_MENU
.Tag = TAG_ITEM_MENU
End With
End If
End Sub
Para que um item de menu seja corretamente alocado em um menu é necessário que o código avalie o menu verificando se já não existe um item de menu com as mesmas características, para se evitar tal falha dentro do código CriarItemMenu ( ) inseri o código ExcluiItemMenu ( ), ou seja o sistema primeiro excluirá um item de menu chamado "Minha Agenda Pessoal" para depois inclui-lo.
Segue abaixo código do procedimento ExcluirItemMenu:
Sub ExcluirItemMenu()
Dim ctlMenu As CommandBarControl
Dim ctlMenuItem As CommandBarControl
Dim strBarName As String
strBarName = QUAL_MENU
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Tag:=TAG_ITEM_MENU, recursive:=True)
If Not ctlMenuItem Is Nothing Then ctlMenuItem.Delete
End Sub
Com o código para criação do item de menu pronto, como fazer que o sistema o leia automaticamente ? simples, vamos utilizar o evento de pasta Workbook_Open( ), ou seja todos procedimentos digitados no Workbook_Open são reconhecidos toda vez que o suplemento é ativado.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Módulo1.ExcluirItemMenu
End Sub
Private Sub Workbook_Open()
Módulo1.CriarItemMenu
End Sub
Convertendo um Arquivo XLS em Suplemento(XLA)
Esta é a parte mais simples, converter um arquivo XLS em XLA. Basta ir no Menu Arquivo(F12)item de menu Salvar Como , e no DropDown Salvar Tipo Como escolha Suplemento do Microsoft Excel.
Você pode salvar seu suplemento em quanquer diretório, mas se você quizer que seu arquivo fique visivel em na caixa de dialogo padrão do office chamada "suplementos" salve seu arquivo no diretorio "Bibliote" localizado dentro o SubDiretório Office,. Microsoft Office em Arquivos de Programas.
Depois de salva seu 1ºsuplemento, basta ativar a caixa de dialogo "suplementos".
Abaixo coloco todo o codigo da nossa agenda.
Modulo 1
Public Const QUAL_MENU As String = "Worksheet Menu Bar"
Public Const TÍTULO_ITEM_MENU As String = "&Minha Agenda Pessoal"
Public Const MACRO_ITEM_MENU As String = "!ExibirFormulário"
Public Const TAG_ITEM_MENU As String = "AGENDA"
Sub CriarItemMenu()
Dim ctlMenuItem As CommandBarControl
Dim intCount As Integer
Dim cBut As CommandBarButton
Dim strBarName As String
Dim lngID As Long
Call ExcluirItemMenu
strBarName = QUAL_MENU
Do
Select Case intCount
Case 0
lngID = 846
Case 1
lngID = 3823
Case 2
lngID = 748
Case 3
lngID = 3
End Select
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Id:=lngID, recursive:=True)
Loop Until (Not ctlMenuItem Is Nothing) Or intCount = 3
If Not ctlMenuItem Is Nothing Then
Set cBut = ctlMenuItem.Parent.Controls.Add(before:=ctlMenuItem.Index + 1, _
temporary:=True)
With cBut
.FaceId = 125
.Caption = TÍTULO_ITEM_MENU
.OnAction = ThisWorkbook.Name & MACRO_ITEM_MENU
.Tag = TAG_ITEM_MENU
End With
End If
End Sub
Sub ExcluirItemMenu()
Dim ctlMenu As CommandBarControl
Dim ctlMenuItem As CommandBarControl
Dim strBarName As String
strBarName = QUAL_MENU
Set ctlMenuItem = Application.CommandBars(strBarName). _
FindControl(Tag:=TAG_ITEM_MENU, recursive:=True)
If Not ctlMenuItem Is Nothing Then ctlMenuItem.Delete
End Sub
Sub ExibirFormulário()
FrmAgenda.Show
End Sub
No Formulário
Private Function Dia_Semana(nDia As Date)
If WeekDay(nDia) = 1 Then
Dia_Semana = "Domingo"
ElseIf WeekDay(nDia) = 2 Then
Dia_Semana = "Segunda"
ElseIf WeekDay(nDia) = 3 Then
Dia_Semana = "Terça"
ElseIf WeekDay(nDia) = 4 Then
Dia_Semana = "Quarta"
ElseIf WeekDay(nDia) = 5 Then
Dia_Semana = "Quinta"
ElseIf WeekDay(nDia) = 6 Then
Dia_Semana = "Sexta"
ElseIf WeekDay(nDia) = 7 Then
Dia_Semana = "Sábado"
Else
Dia_Semana = "Desconhecido"
End If
End Function
Private Sub CmdImpressão_Click()
On Error GoTo aviso
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Col
If Me.BoxDia = "" Then
MsgBox "Digite um dia para impressão", vbInformation, "Agenda"
Else
AGENDA.Range("A1") = Day(Me.BoxDia)
AGENDA.Range("B2") = UCase(Format(Me.BoxDia, "MMMM"))
AGENDA.Range("D2") = UCase(Dia_Semana(Me.BoxDia))
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
For Col = 1 To 24
AGENDA.Range("B" & Col + 4) = Plan2.Cells(i, Col + 1)
Next Col
Exit For
End If
Next
AGENDA.Copy
End If
Exit Sub
aviso:
If Err.Number = 18 Then
Else
MsgBox Err.Number & "-" & Err.Description
End If
End Sub
Private Sub CmdIncluir_Click()
Me.Incluir_Alterar
Me.PesquisarData
End Sub
Private Sub CmdSair_Click()
End
End Sub
Private Sub combohora_Click()
Me.PesquisarData
End Sub
Private Sub boxdia_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.BoxDia = "" Then
Else
If IsDate(Me.BoxDia) Then
Me.ComboHora.ListIndex = -1
Me.BoxDia = Format(Me.BoxDia, "dd/mm")
Me.ComboHora.ListIndex = 0
Else
MsgBox "Data inválida...", vbCritical, "Minhas tarefas e lembretes.."
Cancel = True
End If
End If
End Sub
Private Sub UserForm_Activate()
Dim Cont
Me.ComboHora.Clear
For Cont = 1 To 24
Me.ComboHora.AddItem Format(Cont & ":00", "h:mm")
Next
Me.BoxDia = Format(Date, "dd/mm")
End Sub
Sub PesquisarData()
Dim Célula As Excel.Range
Dim StrData, i, nLinha, Cont
i = 1
Me.BoxAnotação = ""
Me.BoxResumo = ""
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Me.BoxAnotação = Plan2.Cells(i, Me.ComboHora.ListIndex + 2)
For Cont = 1 To 24
Me.BoxResumo = Me.BoxResumo & Chr(10) & Plan2.Cells(1, Cont + 1) & _
":00 - " & Plan2.Cells(i, Cont + 1)
Next
Exit For
End If
Next
End Sub
Sub Incluir_Alterar()
Dim Célula As Excel.Range
Dim StrData, i, nLinha
i = 1
For Each Célula In ThisWorkbook.Sheets("datas").Range("nDatas").Cells
StrData = Célula.Value
i = i + 1
If CDate(Me.BoxDia) = CDate(StrData) Then
nLinha = i
Plan2.Cells(i, Me.ComboHora.ListIndex + 2) = Me.BoxAnotação
Exit For
End If
Next
End Sub
E em Esta_Pasta_de_Trabalho
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Módulo1.ExcluirItemMenu
End Sub
Private Sub Workbook_Open()
Módulo1.CriarItemMenu
End Sub
Assinar:
Postagens (Atom)