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.
Nenhum comentário:
Postar um comentário