Como criar referências estruturadas no Excel?
Referências estruturadas começam com tabelas do Excel. Assim que as tabelas criadas no excel ele cria automaticamente referências estruturadas para você.
Agora dê uma olhada na imagem abaixo.
- Passo 1: Eu dei um link para a célula B3, em vez de mostrar o link como B2, ele está mostrando como Tabela1 [@Vendas]. Aqui, Table1 é o nome da tabela e @Sales é a coluna à qual nos referimos. Todas as células nesta coluna são referenciadas por um nome de Tabela e seguido pelo nome do título da coluna.
- Etapa 2: agora alterarei o nome da tabela para Data_Table e alterarei o título da coluna para Amount .
- Passo 3: Para alterar o nome da tabela, coloque um cursor dentro da tabela> vá para Design> Nome da Tabela.
- Etapa 4: Mencione o nome da tabela como Data_Table.
- Passo 5: Agora mude, dê uma referência à célula B3.
Portanto, entendemos que a referência estruturada tem duas partes: Nome da tabela e Nome da coluna.
Exemplos
Você pode baixar este modelo Excel de referências estruturadas aqui - Modelo Excel de referências estruturadasExemplo 1
Usando referências estruturadas, você pode tornar sua fórmula dinâmica. Ao contrário das referências de células normais, permite que a fórmula esteja ativa em caso de adição e exclusão no intervalo de dados.
Deixe-me aplicar a fórmula SUM tanto para o intervalo normal quanto para a tabela do Excel.
Fórmula SUM para intervalo normal.
Fórmula SOMA para Tabela Excel.
Deixe-me adicionar algumas linhas aos dados da tabela normal e do Excel. Eu adicionei 2 itens de linha aos dados, agora vejo a diferença.
A referência estruturada na tabela do Excel mostra o valor atualizado, mas o intervalo de dados normal não mostra os valores atualizados, a menos que você faça algumas alterações na fórmula manualmente.
Exemplo # 2
Agora, veja mais um exemplo. Tenho informações sobre o nome do produto, a quantidade e o preço. Usando essas informações, preciso chegar ao valor de venda.
Para obter o valor de venda, a fórmula é Qtd * Preço . Vamos aplicar esta fórmula na tabela.
A fórmula diz [@QTY] * [@PRICE]. Isso é mais compreensível do que a referência normal de B2 * C2. Não obtemos o nome da tabela se colocarmos a fórmula dentro da tabela.
Problemas com referências estruturadas do Excel
Ao usar referências estruturadas, enfrentamos alguns problemas que são listados abaixo.
Problema # 1
As referências estruturadas também têm seus próprios problemas. Todos nós estamos familiarizados com a aplicação da fórmula do Excel e com a cópia ou arrastamento para as outras células restantes. Este não é o mesmo processo em Referências Estruturadas, ele funciona de maneira um pouco diferente.
Agora, veja o exemplo abaixo. Eu apliquei a fórmula SUM no Excel para a faixa normal.
Se eu quiser somar o preço e o valor de venda, simplesmente copiarei e colarei ou arraste a fórmula atual para as outras duas células e isso me dará o valor SUM de preço e valor de venda.
Agora aplique a mesma fórmula para a tabela do Excel para a coluna Qty.
Agora temos a soma da coluna Qty. Como o intervalo normal, a fórmula copia a fórmula atual e cole-a na coluna Preço para obter o preço total.
Oh meu Deus!!! Não está mostrando o total da coluna Preço, em vez disso, ainda está mostrando apenas o total da coluna Qty. Portanto, não podemos copiar e colar esta fórmula na célula adjacente ou em qualquer outra célula para se referir à coluna ou linha relativa.
Arraste a fórmula para alterar a referência
Agora que sabemos sua limitação, não podemos mais fazer o trabalho de copiar e colar com referências estruturadas. Então, como podemos superar essa limitação?
A solução é muito simples, basta arrastar a fórmula em vez de copiar. Selecione a célula da fórmula, use a alça de preenchimento e arraste-a para as duas células restantes para alterar a referência da coluna para Preço e Valor de venda.
Agora, atualizamos as fórmulas para obter os respectivos totais.
Problema 2
Vimos um problema com as referências de estrutura e também encontramos a solução, mas temos mais um problema aqui, não podemos fazer a chamada como uma referência absoluta se estivermos arrastando a fórmula para outras células.
Vamos dar uma olhada no exemplo abaixo agora. Eu tenho uma tabela de vendas com várias entradas e quero consolidar os dados usando a função SUMIF no Excel.
Agora vou aplicar a função SUMIF para obter os valores de vendas consolidados de cada produto.
Eu apliquei a fórmula para o mês de janeiro, uma vez que é uma referência estruturada, não podemos copiar e colar a fórmula nas duas colunas restantes, isso não mudará a referência para fevereiro e março, então arrastarei a fórmula.
Oh !! Não obtive nenhum valor na coluna de fevereiro e março. Qual seria o problema??? Observe a fórmula de perto.
Arrastamos a fórmula do mês de janeiro. Na função SUMIF, o primeiro argumento é Criteria Range Sales_Table [Product], uma vez que arrastamos a fórmula, ela mudou para Sales _Table [Jan].
Então, como vamos lidar com isso? Precisamos fazer o primeiro argumento, ou seja, coluna Produto como absoluta e as outras colunas como uma referência relativa. Ao contrário da referência normal, não temos o luxo de usar a tecla F4 para alterar o tipo de referência.
A solução é que precisamos duplicar a coluna de referência, conforme mostrado na imagem abaixo.
Agora podemos arrastar a fórmula para outras duas colunas alargadas. O intervalo de critérios será constante e outras referências de coluna serão alteradas de acordo.
Dica profissional: para tornar o ROW uma referência absoluta, precisamos fazer uma entrada dupla no ROW, mas precisamos colocar o símbolo @ antes do nome do ROW.
= Sales_Table [@ [Product]: [Product]]
Como desativar a referência estruturada no Excel?
Se você não é fã de referências estruturadas, pode desligar seguindo as etapas abaixo.
- Etapa 1: Vá para ARQUIVO> Opções.
- Etapa 2: Fórmulas> Desmarque Usar nomes de tabelas em fórmulas.
Coisas para lembrar
- Para fazer a referência absoluta na referência estruturada, precisamos dobrar o nome da coluna.
- Não podemos copiar a fórmula de referência estruturada, em vez disso, precisamos arrastar a fórmula.
- Não podemos ver exatamente a qual célula estamos nos referindo nas referências estruturadas.
- Se você não estiver interessado em referências estruturadas, pode desativá-las.
