Esta página explica como o recurso Matrizes Dinâmicas (Dynamic Arrays) irá mudar a forma como escrevemos fórmulas no Excel, além de mostrar funções novas como SIMPLES, ÚNICO, CLASSIFICAR, CLASSIFICARPOR, SEQUÊNCIA, RANDARRAY, FILTRO.

Introdução

Matrizes Dinâmicas é um novo recurso que alterou profundamente o motor de cálculos do Excel, afetou o funcionamento de fórmulas existentes, adicionou novas funções e permite resolver problemas que, outrora complexos, agora tornaram-se triviais.

Esta página é um guia complexo que mostra como esse novo recurso pode facilitar seu trabalho.

O recurso de Matrizes Dinâmicas ainda não está disponível para todas as instalações do Excel (nem sei quando estará). Portanto, é bastante provável que você não consiga reproduzir os exemplos deste artigo. Estou usando uma versão do Office Insider: clique neste link se instalar a versão do Office Insider na sua máquina.

Em versões anteriores do Excel, se você tivesse uma tabela como a mostrada abaixo e digitasse a fórmula =A2:A7:

Obteria o erro #VALOR mostrado abaixo:

No entanto, com a nova versão que suporta o recurso de Matrizes Dinâmicas, veja o que acontece:

Ao entrar a fórmula, o Excel automaticamente cria uma região delimitada por um retângulo azul, e as células desse retângulo são os valores correspondentes da referência entrada.

Se escolhermos uma célula qualquer que não seja a primeira acima à esquerda (E2), veja que ele irá mostrar a mesma definição da fórmula de E2, mas com uma cor cinza mais fraca:

Isso significa que E2 é a célula responsável por causar esse efeito dos valores despejarem pela região do retângulo azul. De acordo com a terminologia da Microsoft, o nome correto para empregar nesse caso é esse mesmo: despejar. Vale ressaltar que, em nenhum momento, foi pressionada a combinação Ctrl+Shift+Enter, típica para entrar fórmulas matriciais.

Logo, matrizes dinâmicas são fórmulas criadas que, implicitamente ou explicitamente, despejam.

Erro #DESPEJAR

Veja o cenário a seguir, em que há a obstrução da ação de despejar causadas pelas células E4 e F4:

Ao pressionar Enter, obtém-se o erro #DESPEJAR:

Naturalmente, se você limpar os valores das células que causam a obstrução, o despejamento irá funcionar normalmente. Vale ressaltar que o Excel sequer tenta mostrar os resultados parcialmente: em outras palavras, ou o Excel mostra todos os valores despejados corretamente, ou ele mostra o erro #DESPEJAR!.

Se você selecionar a célula onde o erro aparece e clicar no ícone de exclamação para ver as opções de erro, verá que existe a opção Selecionar Células Que Estão Obstruindo:

Dessa forma, facilmente você é redirecionado à células que fazem a obstrução:

Se quiser limpá-las, basta pressionar Delete.

Além deste caso, o erro #DESPEJAR pode acontecer em outros cenários:

  • Limite de Memória: obtém-se ao referenciar um intervalo muito grande. Para corrigir o erro, é necessário referenciar um intervalo menor.
  • Extrapolar limite da planilha: se a quantidade de células despejadas ultrapassar a última linha ou coluna da planilha.
  • Limitações em Tabelas: você não pode usar as matrizes dinâmicas dentro de Tabelas.
  • Células mescladas: uma matriz dinâmica não pode se transformar em uma célula mesclada, logo, não é possível despejar numa célula mesclada.
  • Tamanho Indeterminado: ao tentar usar argumentos voláteis como a função ALEATÓRIOENTRE dentro da função SEQUÊNCIA (você irá compreender este erro apenas ao terminar de ler este artigo).

Comportamento em Tabelas

As Tabelas (para saber mais sobre Tabelas, clique aqui), embora não permitam fórmulas que despejam, podem ser referenciadas por matrizes dinâmicas, que expandem ou contraem automaticamente.

No exemplo abaixo, temos a Tabela1, então a fórmula que referencia o intervalo com todos os valores do seu corpo é simplesmente =Tabela1. Ao pressionar Enter, o Excel despeja todos os valores da Tabela a partir da célula onde a fórmula foi entrada:

Se você inserir uma nova linha na Tabela (fiz isso inserindo um valor na primeira linha em branco após a Tabela), veja que o Excel despeja automaticamente o novo valor na matriz dinâmica:

A Função SIMPLES (@) e Interseção Implícita

Uma das novidades que Matrizes Dinâmicas trouxe foi a função SIMPLES. A forma mais fácil de explicar é através do exemplo abaixo, onde escrevi =SIMPLES($A$2:$A$7) na célula E4:

Veja o resultado, que é Rodrigo:

Se eu copiar essa fórmula para baixo, obtenho outros valores, correspondentes à uma interseção da linha onde cada célula se encontra com a coluna de referência:

O nome desse tipo desse tipo de interseção é Interseção Implícita. Ela será utilizada nos exemplos posteriores de Matrizes Dinâmicas.

O Excel converte automaticamente a função SIMPLES no caractere @, de forma a serem equivalentes. Confira o valor da célula E4 que você verá que essa conversão foi realizada:

Em outras palavras, podemos dizer que o @ é o operador de interseção implícita. Não estranhe se, daqui a algum tempo, passar a ver esse operador. Ela faz com que um intervalo não seja entendido como uma matriz dinâmica e, então, ele não despeja.

A Função ÚNICO

Um problema clássico do Excel é como escrever uma fórmula que extraia os valores distintos de um intervalo, como em A2:A7 abaixo:

Resolvi o problema de duas formas diferentes: na célula E2, da forma clássica, sem utilizar nenhum recurso de matrizes dinâmicas. E em G2, através de uma nova função das matrizes dinâmicas.

A fórmula em E2 é:

=SEERRO(ÍNDICE($A$2:$A$7;CORRESP(0;CONT.SE($E$1:E1;$A$2:$A$7);0));"")

Além de ser terrivelmente complicada, ainda há a necessidade de copiá-la para as células adjacentes abaixo.

Por outro lado, a fórmula em G2 é, simplesmente:

=ÚNICO(A2:A7)

A função ÚNICO é uma das novidades para essa nova forma de trabalhar no Excel. Isso representa um enorme ganho de produtividade. Agora, usuários de nível básico poderão resolver problemas que antes apenas usuários avançados eram capazes.

A sintaxe completa da função ÚNICO é:

=ÚNICO(matriz;[by_col];[occurs_once])
  • matriz: representa o intervalo a ser analisado.
  • by_col: por padrão, a fórmula analisa combinações únicas por linhas, e não por colunas. Então, por padrão, esse valor é FALSO. Para considerar comparações por colunas, use VERDADEIRO. É opcional.
  • occurs_once: por padrão, se omitido seu valor é FALSO. Se for VERDADEIRO, a fórmula irá retornar apenas valores que aparecem uma vez em matriz, ou seja, apenas valores exclusivos, desconsiderando outros. É opcional.

A Função CLASSIFICAR

Na tabela abaixo, são mostrados dois exemplos com a função CLASSIFICAR:

  • Em Por Nome (célula E2): a tabela é classificada em ordem crescente (alfabética) pelo Nome das pessoas.
  • Em Por Pontuação (célula I2): a tabela é classificada de acordo com a maior pontuação das pessoas.

A fórmula em E2 é:

=CLASSIFICAR(A2:C18)

Por padrão, a classificação é em ordem crescente e pela primeira coluna do intervalo referenciado.

Fazer isso com as fórmulas clássicas do Excel pode ser bastante difícil, especialmente se os nomes repetem.

A fórmula em I2 é:

=CLASSIFICAR(A2:C18;3;-1)

O primeiro argumento representa o intervalo ao qual será analisado. O segundo, o índice da coluna do intervalo que será usado como base para fazer a classificação (1 -> Nome, 2 -> Região e 3 -> Produção). O terceiro parâmetro, -1, indica que é uma classificação decrescente (1 representa crescente).

Ainda existe um quarto parâmetro, que não usei nos exemplos, mas é explicado abaixo. A sintaxe completa da função CLASSIFICAR é:

=CLASSIFICAR(matriz;[índice_de_classificação];[ordem_de_classificação];[por_col])
  • matriz: o intervalo a ser analisado.
  • índice_de_classificação: o índice de qual coluna a classificação será realizada. Se você omitir, o valor é 1, que é exatamente a primeira coluna de matriz. É opcional.
  • ordem_de_classificação: por padrão, a fórmula classifica o resultado em ordem crescente, que é representado por um valor de argumento igual a 1. Se quiser classificar em ordem decrescente, entre com um valor de argumento igual a -1. É opcional.
  • por_col: por padrão, a fórmula faz classificações por linhas, e não por colunas. Então, por padrão, esse valor é FALSO. Para fazer as classificações por colunas, use VERDADEIRO. É opcional.

A Função CLASSIFICARPOR

Veja o caso abaixo: preciso classificar a tabela por ordem decrescente de Região e ordem crescente de Nome:

A fórmula em E2 é:

=CLASSIFICAR(A2:C19;{2\1};{-1\1})

No segundo argumento, {2\1} representa um vetor que considera que primeiro deve-se classificar a coluna de índice 2, que é Região, e depois a de índice 1, que é Nome.

No terceiro argumento, {-1\1} representa que, no argumento {2\1}, o 2 será classificado de ordem decrescente (-1) e 1 será classificado em ordem crescente (1).

Eu não arriscaria escrever essa fórmula do jeito clássico, tamanha seria a complexidade. Mesmo assim, para usuários básicos, essa notação pode ser um pouco difícil, e é aí que entra outra nova função introduzida, a CLASSIFICARPOR. Ela foi empregada na célula I2:

=CLASSIFICARPOR(A2:C18;B2:B18;-1;A2:A18;1)

Para entender melhor como essa fórmula foi construída, veja sua sintaxe:

=CLASSIFICARPOR(array;by_array1;sort_order1;by_array2;sort_order2;...)
  • array: intervalo a ser classificado (que no nosso caso é A2:C18).
  • by_array1: intervalo ao qual irá classificar array, que no nosso caso é a Região representada por B2:B18.
  • sort_order1: ordem de classificação crescente (1) ou decrescente (-1). Queremos Região por ordem decrescente, então usei -1.
  • by_array2: semelhante à by_array1, e respeitando a ordem de prioridade de classificação do mesmo. No nosso caso é o Nome representada por A2:A18.
  • sort_order2: Para Nome, queremos por ordem crescente, então usei 1.

Você pode colocar até 14 critérios de classificação, representadas por by_array3, sort_order3, by_array4, sort_order4 e assim por diante até by_array14, sort_order14.

Com a função CLASSIFICARPOR, usuários com menos experiência no Excel poderão fazer classificações avançadas.

A Função SEQUÊNCIA

A função SEQUÊNCIA gera, como o próprio nome diz, gera um intervalo de valores em sequência. Pode despejar em linhas, ou colunas, ou ambos. A seguir, três exemplos:

A fórmula em A1 é:

=SEQUÊNCIA(10)

Por padrão, o incremento da sequência despejada é de uma unidade, e em linhas.

A fórmula em C1 é:

=SEQUÊNCIA(;5;6)

Note que o primeiro argumento está em branco e o segundo, que vale 5, corresponde à quantidade de colunas despejadas. O terceiro argumento, valendo 6, determina o valor inicial da sequência.

A fórmula em C4 é:

=SEQUÊNCIA(7;5;2;3)

Por ter argumentos tanto em linhas como colunas, o resultado despejado forma um retângulo. O valor inicial, determinado pelo terceiro parâmetro, é 2 e o incremento da sequência, determinado pelo quarto argumento, é de saltos de 3 unidades.

Logo, a sintaxe completa da função SEQUÊNCIA é:

=SEQUÊNCIA(linhas;colunas;início;incremento)

A Função RANDARRAY

A função RANDARRAY gera um intervalo com valores aleatórios em linhas, ou colunas, ou ambos:

Em A1, a fórmula é:

=RANDARRAY(10)

O valor 10 representa quantas linhas serão despejadas. Note que os valores são decimais e se encontram entre 0 e 1, que é o padrão.

Em C1, a fórmula é:

=RANDARRAY(; 5; 1; 10)

Nesse caso, as células despejaram pelas colunas. Note que o primeiro argumento foi deixado em branco, e o segundo vale 5, que é o número de colunas. Os argumentos 1 e 10 representam, respectivamente o valores mínimo e máximo dos números aleatórios gerados.

Em C4, a fórmula é:

=RANDARRAY(7; 5; 10; 20; VERDADEIRO)

Como ambos os argumentos de linha e coluna estão preenchidos, forma-se um retângulo com valores aleatórios. Pelos argumentos, ainda é possível perceber que eles vão de 10 a 20, e o último argumento é igual VERDADEIRO, significa que os valores gerados serão inteiros. Por padrão esse valor é FALSO – decimais.

Logo, a sintaxe da nova função RANDARRAY é:

=RANDARRAY(linhas;colunas;min;max;inteiro)

A Função FILTRO

E agora, minha função favorita, a FILTRO. Na tabela abaixo, desejamos listar todas as pessoas que sejam da Região Norte:

A fórmula na célula E2 é:

=FILTRO(A2:C18;B2:B18="Norte")

O primeiro argumento representa o intervalo da tabela a ser analisada, linha a linha, e a condição é representada por uma igualdade onde o lado esquerdo representa uma coluna dentro do intervalo analisado, e o lado direito representa a condição a ser testada.

Se, no nosso exemplo, buscássemos por uma região que não existe na tabela, como Nordeste, obteríamos o erro #CALC!. Para evitar que esse erro apareça quando não houver ocorrências, basta preencher o terceiro argumento da função, como mostra a célula I2:

=FILTRO(A2:C18;B2:B18="Nordeste";"(nenhum resultado encontrado)")

O terceiro argumento é o texto que você quer que exiba caso o filtro não retorne nenhum resultado. Você pode definir um valor diferente para cada coluna, nesse caso, se fizer algo como:

=FILTRO(E2:G18;F2:F18="Nordeste";{"(sem nome)"\"(sem região)"\"(sem produção)"})

Dessa forma, caso nenhum valor seja encontrado, a primeira célula da coluna será preenchida por (sem nome), a segunda por (sem região) e a terceira (sem produção).

Filtro com Múltiplas Condições

Na figura abaixo, com base na tabela original, foram despejadas tabelas com dois tipos de filtros diferentes:

  • Região = Norte E Produção > 50
  • Região = Norte OU Região = Sul

A fórmula em E2 é:

=FILTRO(A2:C18;(B2:B18="Norte")*(C2:C18>50))

Note que cada condição deve vir entre parênteses, e que o operador E é representado por *.

A fórmula em I2 é:

=FILTRO(A2:C18;(B2:B18="Norte")+(B2:B18="Sul"))

Note que o operador OU é representado por +.

Substituindo o SOMASES e o SOMARPRODUTO

O motivo dessa função ser uma das minhas favoritas é que podemos usá-la para fazer um cálculo de valores de uma tabela de acordo com um filtro.

A fórmula abaixo faz s soma de todos os valores da Região igual a Norte:

=SOMA(FILTRO(C2:C18;B2:B18="Norte"))

A função SOMA, fez a agregação de todos os valores resultantes da função filtro e somou. Ao invés de SOMA, poderia calcular, por exemplo, a média com:

=MÉDIA(FILTRO(C2:C18;B2:B18="Norte"))

 

Logo, com a função FILTRO, você pode reescrever de um jeito mais flexível várias funções que agregam por condição como SOMASES, SOMARPRODUTO, CONT.SES, MÉDIASES, MÍNIMOSES, MÁXIMOSES, MÉDIASES, e assim por diante.

Combinando Funções de Matrizes Dinâmicas

As novas funções que vieram no Excel com as Matrizes Dinâmicas podem ser aninhadas, conforme alguns exemplos selecionados a seguir.

CLASSIFICAR com ÚNICO

Na tabela abaixo, deseja-se criar uma lista em ordem alfabética e de valores distintos dos nomes das pessoas. Fazer isso na forma clássica – isto é, usando recursos anteriores à Matrizes Dinâmicas – pode ser um pesadelo. Veja a imagem abaixo, onde escrevi duas soluções:

A assustadora fórmula em E2 é:

=SEERRO(ÍNDICE($A$2:$A$7;CORRESP(MENOR(SE(CONT.SE($E1:E$1;$A$2:$A$7)=0;CONT.SE($A$2:$A$7;"<"&$A$2:$A$7);"");1);CONT.SE($A$2:$A$7;"<"&$A$2:$A$7);0));"")

Por outro lado, a simples fórmula em G2, possível de escrever somente com o novo recurso de Matrizes Dinâmicas, é:

=CLASSIFICAR(ÚNICO(A2:A7))

CLASSIFICARPOR com RANDARRAY

Suponha que você precisa gerar uma lista aleatória com alguns nomes, para fazer um sorteio. Você pode fazer isso combinando as funções CLASSIFICARPOR e RANDARRAY:

A fórmula em C2 é:

=CLASSIFICARPOR(A2:A18;RANDARRAY(17))

Onde 17 é o número de linhas na referência de origem. Obviamente, se você quiser uma solução mais elegante, pode usar a função LINS para não precisar se preocupar com a quantidade de linhas que existem na origem:

=CLASSIFICARPOR(C2:C18;RANDARRAY(LINS(A2:A18)))

Funções Existentes que Aceitam Recursos de Matrizes Dinâmicas

Além das Matrizes Dinâmicas afetarem profundamente o motor de cálculo do Excel, alteraram também o comportamento de funções já existentes. Veja a figura abaixo:

A fórmula em E2 é:

=NÚM.CARACT(A2:C8)

A fórmula em E11 é:

=TEXTO(A11:C14;"ddd")

Estamos acostumados a inserir apenas uma célula como referência no primeiro argumento das funções NÚM.CARACT e TEXTO. Com Matrizes Dinâmicas, essas funções passaram a suportar o recurso de despejar, como mostrado na figura. O nome desse processo é Lifting (ainda não há uma tradução para o português).

Ainda não há uma lista oficial de quais funções fazem lifting mas, pelos meus testes, boa parte delas suportam o recurso.

UNIRTEXTO, CARACT e SEQUÊNCIA

Há outros exemplos curiosos. Por exemplo, a fórmula:

=UNIRTEXTO(", ";VERDADEIRO;CARACT(SEQUÊNCIA(26;;65)))

Produz o resultado:

A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z

UNIRTEXTO irá concatenar 26 caracteres, a partir do código 65 (que é A), 66 (que é B), 67 (que é C) e assim por diante.

TRANSPOR, MAIOR, SEQUÊNCIA

Veja outro exemplo interessante:

A fórmula em M2 traz os 3 maiores valores do intervalo ao lado, e mostra os valores horizontalmente:

=MAIOR(I1:K8;SEQUÊNCIA(;3))

SEQUÊNCIA gera um vetor de com valores de 1 a 3 em colunas (observe que o primeiro argumento está em branco, e o segundo é responsável em gerar colunas), então, por esse motivo, a fórmula despeja pela horizontal os 3 maiores valores.

Para efeito de ilustração, você poderia gerar uma sequência de 1 a 3 em linhas, desde que usasse TRANSPOR para transformar linhas em colunas e o resultado despejasse em colunas:

=TRANSPOR(MAIOR(I1:K8;SEQUÊNCIA(3)))

SEERRO, ÍNDICE, SEQUÊNCIA

Em A1:A16, temos uma lista de nomes e desejamos distribuir esses nomes em 3 colunas. Resolvi o problema em duas etapas:

Em C1, entrei a fórmula:

=ÍNDICE(A1:A16;SEQUÊNCIA(16;3))

O segundo argumento, que representa o número do índice a ser retornado, é por sua vez uma função que despeja em 16 linhas e 3 colunas. Logo, o resultado não é retornado em apenas uma célula, mas sim num retângulo 16 x 3.

Podemos melhorar a fórmula. Em primeiro lugar, 16 pode ser representado por LINS(A1:A16) e as células extras que retornam erros podem ser suprimidas através da função SEERRO. A fórmula em G1 é:

=SEERRO(ÍNDICE(A1:A16;SEQUÊNCIA(LINS(A1:A16);3));"")

O Operador #

Veja o exemplo abaixo: Em E2, foi criado um intervalo despejado:

A fórmula em G2 é:

=ÚNICO(E2#)

Ela faz referência à um intervalo despejado. O operador #, ao final da referência E2, é usado para que o Excel considere automaticamente toda a região despejada referenciada.

Obviamente, se o despejamento em E2 alterar, a fórmula em G2 irá também automaticamente considerar o novo despejamento, seja contraindo ou expandindo.

Chamo # de Operador de Despejo (embora a Microsoft ainda não tenha dado um nome oficial).

Simulando Tabelas Dinâmicas

Se você não acha Tabelas Dinâmicas tão dinâmicas assim porque elas não recalculam automaticamente, você poderá gostar deste novo recurso. É possível emular uma tabela dinâmica com matrizes dinâmicas:

A fórmula em E2 é:

=CLASSIFICAR(ÚNICO(A2:A16))

A fórmula em F1 é:

=TRANSPOR(CLASSIFICAR(ÚNICO(B2:B16)))

A fórmula em F2 é:

=SOMASES(C2:C16;A2:A16;E2#;B2:B16;F1#)

Note o uso dos operadores # na fórmula, com o objetivo de despejar o resultado.

Se eu alterar o valor de E13 de Emanuel para Maria, veja que interessante:

Clique na imagem para ampliar, se estiver dificuldade para enxergar. A linha correspondente a Emanuel foi eliminada e seu valor foi contabilizado para Maria, tal qual acontece numa Tabela Dinâmica.

Validação de Dados

Abaixo, temos uma relação de qual Estado cada Cidade faz parte. Se eu selecionar um determinado Estado em H2, desejo que a validação de Cidade, na célula H4, mostre apenas cidades do estado selecionado. Em outras palavras, temos um caso de validação dependente (as opções da lista de Cidade dependem do valor selecionado em Estado):

Para criar a validação de Estado, primeiro escrevi em D2 a fórmula:

=CLASSIFICAR(ÚNICO(A2:A9))

Em seguida, selecionei Lista em Permitir e, como Fonte, escrevi a expressão =D2#:

Com isso, temos nossa validação de Estado funcionando corretamente.

Para fazer o mesmo com cidade, em E2 escrevi:

=FILTRO(B2:B9;A2:A9=H2)

Depois, em H4, criei uma validação e na janela que surge, da mesma forma como fiz na etapa anterior, escolhi Lista e entrei a fórmula =E2#.

E assim, sua lista de validação dependente estará funcionando.

Download

Para baixar a pasta de trabalho que possui os exemplos deste artigo, clique aqui.