Excel: 5 dicas para evitar problemas de arquivo vinculados

Um cliente recentemente fez a seguinte pergunta: “Em um contexto em que vários departamentos estão trabalhando em um orçamento, nós separamos nosso Modelo em vários arquivos diferentes … com as dificuldades que implica, as fórmulas mais complicadas e links entre os arquivos a serem mantidos. Os outros funcionam de outra forma? “. Certamente, os problemas de arquivos do Excel relacionados ocorrem especialmente em empresas que têm um processo de orçamento descentralizado.

A galera dos arquivos do Excel vinculados

Embora o Excel sempre possa fornecer uma resposta a todos os Problemas (pela sua natureza da extrema flexibilidade), o conceito de gerenciamento de arquivos vinculados constitui sem dúvida seu calcanhar de Aquiles. É essa fraqueza que incentiva muitos a se transformarem a “sistemas” orçamentários / previsão. Este último oferecem interfaces colaborativas e elimina os problemas de arquivos independentes. Mas esses sistemas, muitas vezes caros, eles são realmente a solução final para qualquer problema de arquivo relacionado? Artigo tem como objetivo oferecer alternativas de orçamento simples e muito baixas, mas muitas vezes desconhecido para usuários do Excel.

faz os usuários do Excel criam arquivos vinculados?

na teoria, nunca se deve dividir um modelo do Excel em Vários arquivos, a menos que:

  • você tem mais de um usuário para usar o modelo ao mesmo tempo e a opção de trabalho colaborativa incluída no Excel não é mais suficiente
  • Peças diferentes do modelo devem ser enviadas para diferentes destinatários
  • Seu modelo é muito grande
Este artigo foi originalmente publicado em 11 de julho de 2013 e colocar De 16 de novembro de 2020.

Quais são os criadores de arquivos vinculados?

Gerenciamento de arquivos no Excel expõe os usuários a vários problemas potenciais:

  • para Veja os dados atualizados, em um arquivo que se alimenta em outro arquivo, ambos os arquivos devem ser abertos
  • Quando um usuário altera o arquivo de origem, especialmente quando ele exclui ou modifica os intervalos de dados, sem abrir o Modelo (o arquivo do Excel que se alimenta disso), o modelo do modelo geralmente se encontra com fórmulas que não são mais simplesmente direito (referência a Bad Data Beachs ou que retornam da mensagem de erro #ref para diretamente! (Referência a intervalos de dados que foram excluídos)
  • Se o nome das folhas e arquivos forem o mínimo de tempo, isso aumentará significativamente as fórmulas e, portanto, o peso do modelo, aumentando assim a possibilidade de Problemas de desempenho

Você tem fontes de dados volumosos para reunir e analisar no Excel? Aprenda a automatizar todo o processo de importação, transformação e modelagem de dados com nosso treinamento na consulta de energia e pivô de energia no Excel.

funciona corretamente com arquivos Excel externos

quando o Arquivos diferentes foram divididos para permitir que os diferentes usuários atualizem seções diferentes e que a opção de trabalho colaborativa incluída no Excel não tenha sido retida, aqui estão as alternativas a serem colocadas. Entre eles, os dois últimos são certamente os mais interessantes, especialmente os mais recentes.

use fórmulas 3D para eliminar arquivos relacionados

Cada gerente de orçamento pode cumprir seu próprio orçamento em um tipo independente arquivo, que seria idêntico para cada gerente. Poderíamos simplesmente copiar o conteúdo dessas páginas, entre os terminais iniciais e finais, um arquivo de consolidação, compreendendo fórmulas 3D, que seriam permitidas como e quando adicionarem essas páginas. Claro, isso não seria uma opção “dinâmica”. Deve, portanto, ser compensado pela criação de uma página de documentação, mantendo na memória as datas de cada última versão dessas páginas tão copiadas. Para que este processo seja eficaz, uma certa verificação Seja tão necessário. Se você puder eliminar esse trabalho “manual”, será sempre o preferido. Será então necessário escolher outras opções apresentadas abaixo.

Use os campos nomeados em seus arquivos de origem

Use os “campos nomeados” para os campos do arquivo de origem que são usados . No modelo. Esta opção, embora simples de configurar, causa vantagens e desvantagens.Tão bem utilizado, preservará as fórmulas relacionadas, mas se os usuários começarem a copiar as informações de uma folha do Excel com campos nomeados em outros arquivos do Excel, eles poderiam, portanto, começar a criar links entre arquivos. O que não deve ser vinculado. Portanto, é necessário ser extra vigilante com esta opção. Se você puder priorizar outra das seguintes opções, será sempre melhor fazê-lo.

Use folhas “espelho” em seus arquivos relacionados

Criar uma folha “Importando dados” em O modelo e uma folha “exportando dados” no arquivo de origem. Cada uma dessas folhas deve ser um espelho um do outro. Assim, em vez de criar uma fórmula no modelo, que se refere a células de um arquivo externo, as fórmulas celulares serão criado na folha de importação do mesmo arquivo (portanto link interno). Somente a folha de importação terá links para o arquivo de origem. Assim, na célula F10 da folha 1 Filing 1, encontraremos uma fórmula que se refere ao Conteúdo da célula F10 do Flâne 1 da pasta de trabalho 2. Isso facilitará em grande parte a gestão e a atualização de tais arquivos.

Exemplo de criar a criação de folhas de espelho

por exemplo, Aqui temos os dados do arquivo original (classe UR 2):

Exportando dados no Excel

e aqui temos nossa folha de importação do nosso modelo (Workbook 1). Observe que na célula C8, é simplesmente uma fórmula = que se refere exatamente à mesma célula, ou C8, mas da segunda pasta de trabalho.

Importando dados no Excel

Para completar a guia Modelo que é usada para calcular as vendas do gerenciador de um gerenciador, ela deve ser evitada para criar uma fórmula que se refere diretamente ao arquivo de origem e isso por todas as razões mencionadas acima. Observe aqui que o número da pasta de trabalho com os dados de origem e o nome das guias desta pasta de trabalho serão longos, mais a fórmula será pesada, tanto em termos de peso e compreensão.

Arquivos relacionados no Excel

Para concluir esta seção, deve ser simplesmente consultar um cálculo na guia Importar.

Arquivos relacionados no Excel

Além de iluminar as fórmulas, se alguma pessoa excluída ou células modificadas na pasta de trabalho 2, sem abrir O fichário 1, provavelmente seria capaz de ver os erros criados na guia Exportar e corrigi-los antes de salvar seu arquivo. Assim, na abertura da pasta de trabalho 1, as fórmulas continuariam a relatar os valores corretos.

Use a programação do VBA para substituir os arquivos relacionados

Também seria possível programar um Macro VBA que permitiria, sempre que um usuário clicar em um botão, importar os dados do (s) arquivo (s) de origem para o modelo e isso, em valores (para que nenhum problema de desempenho relacionado a fórmulas muito gananciosas). Você só tem que encontrar alguém em sua empresa que saiba como programar no VBA! Mas se for possível, é uma solução bastante eficaz!

Use a consulta de energia (nossa opção preferida … e de longe!) Para substituir os arquivos relacionados

A opção Ultimate permanece Para mim, o uso da consulta de energia, que permite a todos os usuários, mais limitados ao Excel, importar dados de arquivos externos por meio de consultas que só precisam ser atualizadas para buscar os valores. Mais recentes. Ao contrário da linguagem VBA, essas solicitações são bastante simples de gravar, já que, mesmo que a consulta de energia seja baseada em uma linguagem de programação, no idioma m, foi projetado pela Microsoft para uma solução de “código baixo”. Em outras palavras, os usuários são capazes de criar consultas, sem ter que aprender a codificar. Eles podem simplesmente clicar em botões e menus diferentes para construir suas consultas e isso gerará automaticamente o código para eles. Não é incrível?

Exemplo de uso da consulta de energia

Importar dados do arquivo em vez de vincular

por exemplo, aqui, será primeiro, a partir do Binder 1, Importe os dados da pasta de trabalho 2.

Importe um arquivo do Excel com a consulta de energia Next, será necessário escolher a guia da pasta de trabalho 2 que queremos importar para a pasta de trabalho 1.

o arquivo do Excel importado via consulta de energia

Criar uma solicitação de transformação

Finalmente, clicando em vários botões e recursos, poderemos transformar os dados na tabela bem estruturada e essas etapas de transformação serão registradas através das etapas aplicadas (painel direito).Isso significa que, no desconto, a solicitação retornará através de todas as etapas de transformação e, se houver, portanto, novos dados ou dados modificados na pasta de trabalho 2, o todo será facilmente integrado ao reforço 1. P.>

Consulta de consulta de energia

Recuperar dados importados em uma guia

será possível recuperar os dados em uma tabela do Excel, criando um crosstab dinâmico ou um crosstab dinâmico com base nesses dados ou enviando esses dados no pivot de energia para criar um cubo virtual conectando esta tabela com outras tabelas.

Excel Array resultante de um Consulta de consulta de energia'une requête Power Query

Crie uma consulta alternativa para obter o mesmo resultado que anteriormente

normalmente, com a consulta de energia, estamos tentando reconstruir bancos de dados com os dados “não calculados “. Em vez disso, as medições no DAX, em pivô de energia ou guias são usadas. Crossover dinâmico, para tornar os cálculos. Mas, conforme necessário, também seria possível, na consulta de energia, para obter uma pintura com vendas, em vez de uma mesa com preços e números de unidades. Isso realmente nos traria de volta ao resultado em nosso exemplo.

consulta de poury de energia

Forneça a vantagem de dados importados sobre dados Obtido por link

O que é mais necessário aqui é que o usuário da consulta de energia não funciona mais com dados relacionados, mas sim com dados importados, que são atualizados a pedido. Ao fazê-lo, os problemas de arquivos relacionados são completamente eliminados. Da mesma forma, o peso do arquivo, que não requer fórmulas complexas, que compactam os dados.

Não mais razão para não usar a consulta de energia

poder. Query agora está disponível no vasta maioria das versões do Excel gratuitamente. Devemos adicionar um complemento para a versão 2010 e 2013, mas está disponível em todas as outras versões subseqüentes. A consulta de energia mudou completamente a maneira de trabalhar com o Excel, otimizando o tempo necessário para realizar tarefas manuais, mas também ajustando problemas de longa data, como o problema de arquivos relacionados!

arquivo de suporte VIP para fazer o download

para baixar o arquivo usado neste tutorial, tornar-se um membro VIP do CFO mascarado.

treinamento complementar

Para aprofundar seu conhecimento com o Excel, especialmente o Tarefas que afetam dados, consolidação e processamento de dados, recomendamos o nosso treinamento do Excel – Introdução à consulta de energia e linguagem M.

Aqui estão alguns comentários de alunos que seguiram este treinamento:

cfo-mask_formation-in-line_fbO CFO mascarado oferece uma ampla gama de decisões Fazendo treinamento de computação com o Excel e Power BI, por meio de um portal on-line e remoto em TE MPs reais, de acordo com um calendário. Se você quiser organizar campos privados, envie-nos um e-mail para [email protected] Certificados adequados para os padrões de educação continuada para as várias ordens de trabalho do Quebec são oferecidos para todo o treinamento.

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *