Eu escrevi muitas vezes sobre os muitos benefícios de formatar seus dados como uma tabela estruturada no Microsoft Excel. No entanto, apesar disso, há uma questão importante que continua a lançar uma chave inglesa nas obras – as tabelas de Excele são incompatíveis com fórmulas de matriz dinâmica.
O problema: as fórmulas de matriz dinâmica não funcionam nas mesas do Excel
Uma fórmula de matriz dinâmica é uma fórmula cujo resultado derrama de uma célula para outras células. Além disso, se o resultado da fórmula aumentar ou diminuir o tamanho, a matriz faz o mesmo.
Relacionado
Tudo o que você precisa saber sobre o derramamento no Excel
Não vale a pena chorar por referências derramadas.
Por exemplo, digitando:
=UNIQUE(A2:A21)
na célula B2 e pressionando Enter retorna todos os valores exclusivos das células A2 a A21 em células separadas na coluna B. Observe como, quando você seleciona qualquer uma das células que contêm um desses valores exclusivos, uma linha azul lembra que essa é uma matriz derramada.
No entanto, as tabelas formatadas do Excel são projetadas para manter linhas e colunas de dados independentes, em vez de dados que se espalham de uma única célula. Então, se você tentar inserir a mesma fórmula de matriz dinâmica em uma célula em uma tabela, verá o #spill! erro.
Relacionado
Tudo o que você precisa saber sobre as mesas do Excel (e por que você deve sempre usá -las)
Isso pode mudar totalmente a maneira como você trabalha no Excel.
Tabelas do Excel e fórmulas de matriz dinâmica são contêineres para expandir automaticamente os dados. Portanto, se você tentar colocar uma coisa que se expanda automaticamente (como uma fórmula de matriz dinâmica) dentro de outra coisa que também se expande automaticamente (como uma tabela do Excel), o Excel não pode descobrir qual dos dois deve ditar o tamanho do conjunto de dados.
Em um mundo ideal, a Microsoft encontraria uma maneira de permitir que você use fórmulas de matriz dinâmica nas tabelas, com a tabela se expandindo e diminuindo automaticamente para acomodar a matriz derramada. Até que isso, no entanto, continuarei usando as seguintes abordagens alternativas, que, embora úteis, não são correções perfeitas.
Solução 1: converta a tabela em uma faixa não formatada
Uma maneira de contornar esse problema é converter a tabela do Excel em um intervalo selecionando qualquer célula na tabela e clicando em “Converter em intervalo” na guia Design da tabela.
Agora, como os dados não estão mais em uma tabela de Excel estruturada, você pode usar fórmulas de matriz dinâmica no conteúdo do seu coração.
Quando você converte uma tabela em um intervalo, a formatação das células é mantida porque a conversão se concentra na limpeza das propriedades estruturais da tabela, em vez da formatação celular subjacente. Então, mesmo que o alcance visual Como uma mesa formatada, vai comportar-se Como um alcance regular.
Dito isto, mesmo que você possa reativar os botões de filtro pressionando Ctrl+Shift+L, os intervalos não formatados não têm os mesmos recursos que as tabelas do Excel. Por exemplo, você não pode fazer referência aos cabeçalhos da coluna em fórmulas, e intervalos regulares não têm a mesma capacidade de expansão automática das tabelas do Excel.
Além disso, gráficos e dinâmicos vinculados ao intervalo de dados regulares não capturam automaticamente linhas adicionadas e, se desejar linhas em faixas, precisará aplicar regras de formatação condicional complexas.
Relacionado
Como formatar uma matriz derramada no Excel
Não aplique formatação direta.
Em vez disso, para manter seus dados em um formato tabular estruturado, você pode usar funções alternativas que podem ser aplicadas a linhas individuais em uma coluna de tabela sem derramar os resultados.
Solução 2: Use funções alternativas
A Microsoft está sempre procurando maneiras de simplificar tarefas de redação de fórmula no Excel, e é por isso que introduziu muitas funções chiques que retornam mais de um resultado nos últimos anos. No entanto, como eles não funcionam em tabelas, você pode reverter para algumas das funções mais antigas que não têm essa capacidade.
Dito isto, como as funções mais antigas geralmente exigem fórmulas mais complexas do que as novas funções dinâmicas, elas geralmente exigem mais argumentos e podem ser menos eficientes, especialmente em conjuntos de dados maiores.
Quando você digita as seguintes funções na primeira linha de uma coluna em uma tabela e pressione Enter, a fórmula será aplicada às células restantes nessa coluna automaticamente, bem como em quaisquer linhas adicionais que você subsequentemente adiciona à parte inferior da tabela.
Gerando uma lista numerada dinâmica
Função de matriz dinâmica |
Sequência (com Counta) |
|---|---|
O que esta função faz | Retorna uma sequência de números |
Função alternativa | LINHA |
Neste exemplo, digitando:
=SEQUENCE(COUNTA(B:B)-1,1,1)
Na célula A2 conta o número de células não em branco na coluna B, menos uma para explicar a linha do cabeçalho e cria uma única coluna de números seqüenciais, a partir de 1. Além disso, se os dados são adicionados ou removidos da coluna B, a contagem na coluna A atualizará automaticamente.
Relacionado
Não crie listas numeradas manualmente no Excel: use sequência e contagem em vez disso
Esta fórmula simples economizará muito tempo.
Para obter o mesmo resultado em uma tabela do Excel sem usar uma fórmula de matriz dinâmica, na célula A2, tipo:
=ROW()-ROW(TableX[[#Headers],[Name]])
Substituir Tablex com o nome da tabela correta e Nome com o nome correto da coluna.
Criando uma variedade de números aleatórios
Função de matriz dinâmica |
Randarray |
|---|---|
O que esta função faz | Retorna uma variedade de números aleatórios |
Função alternativa | Borda |
Nesta faixa não formatada, digitando:
=RANDARRAY(20,1,50,100,TRUE)
na célula A2 retorna uma lista aleatória de números inteiros entre 50 e 100 nas células A2 a A21.
Relacionado
Como gerar números aleatórios no Microsoft Excel
Use uma função simples ou obtenha um controle mais avançado com uma ferramenta de gerador.
Para fazer o mesmo em uma tabela formatada, na célula A2, tipo:
=RANDBETWEEN(50,100)
na célula A2 e clique e arraste a alça da tabela para expandir a tabela para baixo até que haja 20 linhas de dados.
Randarray e Randbetween são funções voláteis, o que significa que eles se recalculam cada vez que uma mudança é feita na planilha. Para corrigir os números aleatórios depois de gerados, selecione todos os dados (Ctrl+A), copie -os (Ctrl+C) e pressione CRL+Shift+V para colar os números como valores.
Dividindo uma célula em colunas separadas
Função de matriz dinâmica |
Textsplit |
|---|---|
O que esta função faz | Divide texto em linhas ou colunas usando delimitadores |
Funções alternativas | Texto antes e textafter |
Neste exemplo, o Textsplit pega o nome na célula A2 e derrama a versão dividida de cada nome nas células B2 e C2, com a sequência de espaço de vírgula atuando como delimitador. Em seguida, a seleção da célula B2 e clicando duas vezes na alça de preenchimento aplica a fórmula de matriz dinâmica às células restantes no intervalo.
=TEXTSPLIT(A2,", ")
Ao trabalhar com tabelas do Excel, em vez de usar esta fórmula de matriz dinâmica, você pode usar o texto antes e o texto.
Na célula B2, tipo:
=TEXTBEFORE([@Name],",")
Para extrair o texto antes da vírgula da coluna Nome.
Então, na célula C2, tipo:
=TEXTAFTER([@Name]," ")
Para extrair o texto após o espaço.
Relacionado
4 maneiras de dividir dados em várias colunas no Microsoft Excel
Você está mimado pela escolha!
Função de matriz dinâmica |
EXCLUSIVO |
|---|---|
O que esta função faz | Retorna valores únicos de um intervalo |
Funções alternativas | Índice, único e linha |
Nesse intervalo regular, esta fórmula única lista todos os valores exclusivos nas células A2 a A50.
=UNIQUE(A2:.A50)
Observe o período após o cólon. Esse personagem, conhecido neste contexto como um operador de TRIM REF, diz ao Excel para aparar qualquer linha em branco no final do resultado, o que impede que um zero apareça na lista.
Relacionado
Como listar e classificar valores e texto exclusivos no Microsoft Excel
Crie uma lista de nomes, datas ou outros dados exclusivos em sua planilha com uma função simples.
Para obter um resultado semelhante em uma tabela do Excel, na célula B2, tipo:
=INDEX(UNIQUE($A$1:$A$50),ROW(A2))
onde
- ÍNDICE() Retorna um valor,
- Único ($ a $ 1: $ a $ 50)que é o primeiro argumento da fórmula do índice, encontra valores únicos nas células A1 a A50 e
- Linha (A2)que é o segundo argumento da fórmula do índice, retorna o no valor único, onde n é o número da linha da célula ativa.
No entanto, embora isso encontre com sucesso todos os valores exclusivos dos dados de origem, a tabela não se expande e contrata automaticamente. Para explicar isso, na fita de design da tabela, verifique “linha total” e expanda o menu suspenso resultante na parte inferior da tabela para alterar o tipo de agregação para “contagem”. Isso conta os valores exclusivos na coluna da tabela.
Então, em uma célula separada, tipo:
=COUNTA(UNIQUE($A$2:.$A$50))
Para contar valores exclusivos no intervalo de origem.
Agora, se a contagem na linha total da tabela não corresponder à contagem na célula Counta-Unique, você sabe que precisa expandir ou reduzir o tamanho da sua tabela de acordo.
Sim, existem maneiras de navegar na incompatibilidade entre as tabelas do Excel e as fórmulas de matriz dinâmica, mas, reconhecidamente, elas são pesadas e demoradas. É por isso que todos precisamos da Microsoft para nos fazer um favor e encontrar uma maneira de integrar essas duas ferramentas úteis Soner, e mais tarde.
