Matrizes dinâmicas no Excel

Conteúdo

O que são arrays dinâmicos

Em setembro de 2018, a Microsoft lançou uma atualização que adiciona uma ferramenta completamente nova ao Microsoft Excel: Dynamic Arrays e 7 novas funções para trabalhar com eles. Essas coisas, sem exagero, mudam radicalmente toda a técnica usual de trabalhar com fórmulas e funções e dizem respeito, literalmente, a todos os usuários.

Considere um exemplo simples para explicar a essência.

Suponha que tenhamos uma tabela simples com dados sobre cidades-mês. O que acontecerá se selecionarmos qualquer célula vazia à direita da planilha e inserirmos nela uma fórmula que vincule não a uma célula, mas imediatamente a um intervalo?

Em todas as versões anteriores do Excel, depois de clicar em Entrar obteríamos o conteúdo de apenas uma primeira célula B2. De que outra forma?

Bem, ou seria possível envolver esse intervalo em algum tipo de função de agregação como =SUM(B2:C4) e obter um total geral para isso.

Se precisássemos de operações mais complexas do que uma soma primitiva, como extrair valores únicos ou Top 3, teríamos que inserir nossa fórmula como uma fórmula de matriz usando um atalho de teclado Ctrl+Shift+Entrar.

Agora tudo é diferente.

Agora, depois de inserir essa fórmula, podemos simplesmente clicar em Entrar – e obtenha como resultado imediatamente todos os valores uXNUMXbuXNUMXb aos quais nos referimos:

Isso não é mágica, mas as novas matrizes dinâmicas que o Microsoft Excel agora possui. Bem vindo ao novo mundo 🙂

Recursos para trabalhar com arrays dinâmicos

Tecnicamente, todo o nosso array dinâmico é armazenado na primeira célula G4, preenchendo o número necessário de células para a direita e para baixo com seus dados. Se você selecionar qualquer outra célula da matriz, o link na barra de fórmulas ficará inativo, mostrando que estamos em uma das células “filhas”:

Uma tentativa de excluir uma ou mais células "filhas" não levará a nada - o Excel as recalculará imediatamente e as preencherá.

Ao mesmo tempo, podemos nos referir com segurança a essas células “filhas” em outras fórmulas:

Se você copiar a primeira célula de uma matriz (por exemplo, de G4 para F8), a matriz inteira (suas referências) se moverá na mesma direção das fórmulas regulares:

Se precisarmos mover a matriz, será suficiente mover (com o mouse ou uma combinação de Ctrl+X, Ctrl+V), novamente, apenas a primeira célula principal G4 – depois dela, ela será transferida para um novo local e toda a nossa matriz será expandida novamente.

Se você precisar fazer referência em outro lugar na planilha ao array dinâmico criado, poderá usar o caractere especial # (“libra”) após o endereço de sua célula inicial:

Por exemplo, agora você pode facilmente fazer uma lista suspensa em uma célula que se refere ao array dinâmico criado:

Erros de matriz dinâmica

Mas o que acontece se não houver espaço suficiente para expandir o array, ou se houver células já ocupadas por outros dados em seu caminho? Conheça um tipo fundamentalmente novo de erros no Excel – #TRANSFERIR! (#DERRAMAR!):

Como sempre, se clicarmos no ícone com um diamante amarelo e um ponto de exclamação, obteremos uma explicação mais detalhada da origem do problema e podemos encontrar rapidamente células interferentes:

Erros semelhantes ocorrerão se a matriz sair da planilha ou atingir uma célula mesclada. Se você remover o obstáculo, tudo será corrigido imediatamente.

Arrays dinâmicos e tabelas inteligentes

Se o array dinâmico aponta para uma tabela “inteligente” criada por um atalho de teclado Ctrl+T ou por Home – Formatar como uma tabela (Início — Formatar como Tabela), então ele também herdará sua principal qualidade – dimensionamento automático.

Ao adicionar novos dados na parte inferior ou à direita, a tabela inteligente e o intervalo dinâmico também serão estendidos automaticamente:

No entanto, há uma limitação: não podemos usar uma referência de intervalo dinâmico em fóruns dentro de uma tabela inteligente:

Matrizes dinâmicas e outros recursos do Excel

Ok, você diz. Tudo isso é interessante e engraçado. Não há necessidade, como antes, de esticar manualmente a fórmula com uma referência à primeira célula do intervalo original para baixo e para a direita e tudo mais. E isso é tudo?

Não é bem assim.

Matrizes dinâmicas não são apenas mais uma ferramenta do Excel. Agora eles estão embutidos no coração (ou cérebro) do Microsoft Excel – seu mecanismo de cálculo. Isso significa que outras fórmulas e funções do Excel familiares para nós agora também suportam o trabalho com matrizes dinâmicas. Vamos dar uma olhada em alguns exemplos para dar uma ideia da profundidade das mudanças que ocorreram.

Transpor

Para transpor um intervalo (trocar linhas e colunas) o Microsoft Excel sempre teve uma função interna TRANSP (TRANSPOR). No entanto, para usá-lo, você deve primeiro selecionar corretamente o intervalo para os resultados (por exemplo, se a entrada foi um intervalo de 5×3, você deve ter selecionado 3×5), depois entrar na função e pressionar o botão combinação Ctrl+Shift+Entrar, porque só poderia funcionar no modo de fórmula de matriz.

Agora você pode apenas selecionar uma célula, inserir a mesma fórmula nela e clicar no botão normal Entrar – array dinâmico fará tudo sozinho:

Tabela de multiplicação

Este é o exemplo que eu costumava dar quando me pediram para visualizar os benefícios das fórmulas de matriz no Excel. Agora, para calcular toda a tabela pitagórica, basta ficar na primeira célula B2, inserir lá uma fórmula que multiplique duas matrizes (conjunto vertical e horizontal de números 1..10) e simplesmente clicar em Entrar:

Colagem e conversão de caixa

Arrays não só podem ser multiplicados, mas também colados com o operador padrão & (e comercial). Suponha que precisamos extrair o nome e o sobrenome de duas colunas e corrigir o caso de salto nos dados originais. Fazemos isso com uma fórmula curta que forma toda a matriz e, em seguida, aplicamos a função a ela PROPNACH (APROPRIADO)para arrumar o registro:

Conclusão Top 3

Suponha que temos vários números dos quais queremos derivar os três principais resultados, organizando-os em ordem decrescente. Agora isso é feito por uma fórmula e, novamente, sem qualquer Ctrl+Shift+Entrar como antes:

Se você deseja que os resultados não sejam colocados em uma coluna, mas em uma linha, basta substituir os dois pontos (separador de linha) nesta fórmula por um ponto e vírgula (separador de elemento dentro de uma linha). Na versão em inglês do Excel, esses separadores são ponto e vírgula e vírgulas, respectivamente.

PROCV extraindo várias colunas de uma só vez

Funções VPR (PROCV) agora você pode extrair valores não de uma, mas de várias colunas ao mesmo tempo – basta especificar seus números (em qualquer ordem desejada) como um array no terceiro argumento da função:

Função OFFSET retornando um array dinâmico

Uma das funções mais interessantes e úteis (depois de PROCV) para análise de dados é a função DISPOSIÇÃO (DESLOCAMENTO), ao qual dediquei um capítulo inteiro de meu livro e um artigo aqui. A dificuldade em entender e dominar essa função sempre foi que ela retornava uma matriz (intervalo) de dados como resultado, mas não podíamos vê-la, pois o Excel ainda não sabia como trabalhar com matrizes fora da caixa.

Agora esse problema ficou no passado. Veja como agora, usando uma única fórmula e um array dinâmico retornado por OFFSET, você pode extrair todas as linhas de um determinado produto de qualquer tabela classificada:

Vejamos seus argumentos:

A1 – célula inicial (ponto de referência)
ПОИСКПОЗ(F2;A2:A30;0) – cálculo do deslocamento da célula inicial para baixo – para o primeiro repolho encontrado.
0 – deslocamento da “janela” para a direita em relação à célula inicial
СЧЁТЕСЛИ(A2:A30;F2) – cálculo da altura da “janela” retornada – o número de linhas onde há repolho.
4 — tamanho da “janela” horizontalmente, ou seja, saída de 4 colunas

Novas funções para matrizes dinâmicas

Além de oferecer suporte ao mecanismo de matriz dinâmica em funções antigas, várias funções completamente novas foram adicionadas ao Microsoft Excel, aprimoradas especificamente para trabalhar com matrizes dinâmicas. Em particular, estes são:

GRADE (ORDENAR) – classifica o intervalo de entrada e produz um array dinâmico na saída
SORTPO (ORDENAR POR) – pode classificar um intervalo por valores de outro
FILTRO (FILTRO) – recupera as linhas do intervalo de origem que atendem às condições especificadas
ÚNICO (ÚNICO) – extrai valores únicos de um intervalo ou remove duplicatas
SLMASSIVO (RANDARRAIO) – gera uma matriz de números aleatórios de um determinado tamanho
PÓS-NASCIMENTO (SEQÜÊNCIA) — forma uma matriz a partir de uma sequência de números com um determinado passo

Mais sobre eles – um pouco mais tarde. Eles valem um artigo separado (e não um) para estudo cuidadoso 🙂

Conclusões

Se você leu tudo o que está escrito acima, acho que já percebeu a escala das mudanças que ocorreram. Tantas coisas no Excel agora podem ser feitas de forma mais fácil, mais fácil e mais lógica. Devo admitir que estou um pouco chocado com a quantidade de artigos que agora terão que ser corrigidos aqui, neste site e em meus livros, mas estou pronto para fazer isso com o coração leve.

Resumindo os resultados, плюсы matrizes dinâmicas, você pode escrever o seguinte:

Você pode esquecer a combinação Ctrl+Shift+Entrar. O Excel agora não vê diferença entre “fórmulas regulares” e “fórmulas de matriz” e as trata da mesma maneira.
Sobre a função SUMPRODUCT (SUMPRODUTO), que era usado anteriormente para inserir fórmulas de matriz sem Ctrl+Shift+Entrar você também pode esquecer - agora é bastante fácil SOMA и Entrar.
Tabelas inteligentes e funções familiares (SUM, IF, VLOOKUP, SUMIFS, etc.) agora também suportam matrizes dinâmicas total ou parcialmente.
Há compatibilidade com versões anteriores: se você abrir uma pasta de trabalho com matrizes dinâmicas em uma versão antiga do Excel, elas se transformarão em fórmulas de matriz (entre chaves) e continuarão a funcionar no “estilo antigo”.

Encontrou algum número pontos negativos:

Você não pode excluir linhas, colunas ou células individuais de uma matriz dinâmica, ou seja, ela vive como uma única entidade.
Você não pode classificar uma matriz dinâmica da maneira usual através Dados - Ordenação (Dados - Classificar). Agora existe uma função especial para isso. GRADE (ORDENAR).
Um intervalo dinâmico não pode ser transformado em uma tabela inteligente (mas você pode criar um intervalo dinâmico com base em uma tabela inteligente).

Claro, isso não é o fim, e tenho certeza que a Microsoft continuará a melhorar esse mecanismo no futuro.

Onde posso baixar?

E por fim, a questão principal 🙂

A Microsoft anunciou e mostrou pela primeira vez uma visualização de matrizes dinâmicas no Excel em setembro de 2018 em uma conferência Inflamar. Nos meses seguintes, houve um teste completo e execução de novos recursos, primeiro em ~~gatos~~ funcionários da própria Microsoft e depois em testadores voluntários do círculo de Office Insiders. Este ano, a atualização que adiciona arrays dinâmicos começou a ser lançada gradualmente para assinantes regulares do Office 365. Por exemplo, só o recebi em agosto com minha assinatura do Office 365 Pro Plus (direcionado mensalmente).

Se o seu Excel ainda não possui matrizes dinâmicas, mas você realmente deseja trabalhar com elas, existem as seguintes opções:

Se você tem uma assinatura do Office 365, pode simplesmente esperar até que essa atualização chegue até você. A rapidez com que isso acontece depende da frequência com que as atualizações são entregues ao seu Office (uma vez por ano, uma vez a cada seis meses, uma vez por mês). Se você tiver um PC corporativo, poderá solicitar ao administrador que configure as atualizações para serem baixadas com mais frequência.
Você pode se juntar às fileiras desses voluntários de teste do Office Insiders – então você será o primeiro a receber todos os novos recursos e funções (mas há uma chance de aumentar o bug no Excel, é claro).
Se você não tiver uma assinatura, mas uma versão autônoma em caixa do Excel, terá que esperar até o lançamento da próxima versão do Office e do Excel em 2022, pelo menos. Os usuários dessas versões recebem apenas atualizações de segurança e correções de bugs, e todos os novos “brindes” agora vão apenas para assinantes do Office 365. Triste mas verdade 🙂

De qualquer forma, quando as matrizes dinâmicas aparecerem no seu Excel – após este artigo, você estará pronto para isso 🙂

O que são fórmulas de matriz e como usá-las no Excel
Soma de janela (faixa) usando a função OFFSET
3 maneiras de transpor uma tabela no Excel