Baixe a apostila de exemplo
Este tutorial fornecerá a você uma introdução às fórmulas de matriz dinâmica no Excel e no Planilhas Google.
Introdução
Em setembro de 2022, a Microsoft introduziu as Fórmulas de matriz dinâmica no Excel. Seu objetivo é tornar mais fácil escrever fórmulas complexas e com menos chance de erro.
As fórmulas de matriz dinâmica pretendem eventualmente substituir as fórmulas de matriz, ou seja, fórmulas avançadas que requerem o uso de Ctrl + Shift + Enter (CSE).
Aqui está uma comparação rápida entre a Fórmula de Matriz e a Fórmula de Matriz Dinâmica usada para extrair uma lista de departamentos exclusivos de nossa lista no intervalo A2: A7.
Fórmula de matriz legada (CSE):
A seguinte fórmula é inserida na célula D2 e é inserido pressionando Ctrl + Shift + Enter e copiando-o de D2 para D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Fórmula de matriz dinâmica:
A fórmula a seguir é apenas inserida na célula D2 e entrou pressionando Enter. Com uma rápida olhada, você pode dizer como é fácil e direto escrever uma Fórmula de matriz dinâmica.
| 1 | = ÚNICO (A2: A7) |
Disponibilidade
Desde agosto de 2022, as Fórmulas de matriz dinâmica estão disponíveis apenas para usuários do Office 365.
Spill and Spill Range
As fórmulas de matriz dinâmica funcionam retornando vários resultados para um intervalo de células com base em uma única fórmula inserida em uma célula.
Este comportamento é conhecido como “Derramando” e o intervalo de células onde os resultados são colocados é chamado de “Alcance do derramamento”. Quando você seleciona qualquer célula dentro do intervalo de derramamento, o Excel a destaca com uma borda azul fina.
No exemplo abaixo, a fórmula de matriz dinâmica ORDENAR está na cela D2 e os resultados foram espalhados na faixa D2: D7
| 1 | = CLASSIFICAR (A2: A7) |
Os resultados da fórmula são dinâmicos, o que significa que se ocorrer uma alteração no intervalo da fonte, os resultados também mudam e o intervalo de derramamento é redimensionado.
#DERRAMAR!
Você deve observar que se o seu Intervalo de derramamento não estiver completamente em branco, um erro #SPILL será retornado.
Quando você seleciona o erro #SPILL, o intervalo de derramamento desejado da fórmula é destacado com uma borda azul tracejada. Mover ou excluir os dados na célula que não está em branco remove esse erro, permitindo que a fórmula vaze.
Notação de referência de derramamento
Para fazer referência ao intervalo de vazamento de uma fórmula, colocamos o # símbolo após a referência de célula da primeira célula no derramamento.
Você também pode fazer referência ao derramamento selecionando todas as células no intervalo do derramamento e uma referência ao derramamento será criada automaticamente.
No exemplo abaixo, gostaríamos de contar o número de funcionários em nossa empresa usando a fórmula CONT.valor depois de serem ordenados alfabeticamente usando a fórmula de matriz dinâmica ORDENAR.
Nós entramos no ORDENAR fórmula em D2 para ordenar os funcionários em nossa lista:
| 1 | = CLASSIFICAR (A2: A7) |
Em seguida, entramos no CONT.valor fórmula em G2 para contar o número de funcionários:
| 1 | = CONT.valores (D2 #) |
Observe o uso de # em D2 # para se referir aos resultados divulgados por SORT no intervalo D2: D7.
Novas Fórmulas
Abaixo está a lista completa das novas fórmulas de matriz dinâmica:
- EXCLUSIVO - Retorna uma lista de valores únicos de um intervalo
- ORDENAR - Classifica valores em um intervalo
- ORDENAR POR - Classifica os valores com base em um intervalo correspondente
- FILTRO - Filtra um intervalo com base nos critérios fornecidos
- RANDARRAY - Retorna uma matriz de números aleatórios entre 0 e 1
- SEQÜÊNCIA - Gera uma lista de números sequenciais, como 1, 2, 3, 4, 5
Fórmula de matriz dinâmicas no Google Sheets
Todos os exemplos acima funcionam exatamente da mesma forma no Planilhas Google e no Excel.
