Excel: 5 consellos para evitar problemas de ficheiro ligados

Un cliente solicitou recentemente a seguinte pregunta: “Nun contexto onde varios departamentos están a traballar cun orzamento, separamos a nosa Modelo en varios ficheiros diferentes … coas dificultades que implica, as fórmulas máis complicadas e as ligazóns entre os ficheiros que se deben manter. Fai que outros traballen doutro xeito? “. Certamente, os problemas dos ficheiros de Excel relacionados prodúcense especialmente en empresas que teñen un proceso de orzamento descentralizado.

A galera dos ficheiros de Excel ligados

Aínda que Excel sempre pode proporcionar unha resposta a todos os Problemas (pola súa natureza de extrema flexibilidade), o concepto de xestión de ficheiros vinculado constitúe sen dúbida o seu talón de Aquiles. É esta debilidade que anima a moitos a recorrer a “sistemas” orzamento / previsión. Este último ofrece interfaces colaborativas e eliminar os problemas de ficheiros independentes. Pero estes sistemas, a miúdo caros, son realmente a solución definitiva a calquera problema de arquivo relacionado? Isto O artigo ten como obxectivo ofrecer alternativas de orzamento sinxelas e moi baixas, pero moitas veces descoñecidas para Excel usuarios.

Os usuarios de Excel crean ficheiros vinculados?

En teoría, nunca se debe dividir un modelo de Excel en varios ficheiros, a menos que:

  • Vostede é máis dun usuario que ter que usar o modelo ao mesmo tempo e a opción de traballo colaborativa incluída en Excel xa non é suficiente
  • Diferentes partes do modelo deben ser enviadas a diferentes destinatarios
  • O seu modelo é demasiado grande
Este artigo foi publicado orixinalmente o 11 de xullo de 2013 e poñer A partir do 16 de novembro de 2020.

Cales son os creadores de ficheiros vinculados?

A xestión de ficheiros en Excel expón aos usuarios a varios problemas potenciais:

  • a Ver os datos actualizados, nun ficheiro que se alimenta doutro ficheiro, ambos os ficheiros deben ser abertos
  • cando un usuario cambia o ficheiro de orixe, especialmente cando elimina ou modifica os rangos de datos, sen abrir a Modelo (o ficheiro de Excel que se alimenta nel), o modelo do modelo a miúdo reunirase con fórmulas que xa non son xustas (referencia ás malas praias de datos ou que volven da mensaxe de erro #ref a directamente! (Referencia a intervalos de datos que foron eliminados)
  • Se o nome das follas e ficheiros é o menos longo, isto aumentará significativamente as fórmulas e, polo tanto, o peso do modelo, aumentando así a posibilidade de reunirse Problemas de rendemento

Ten fontes de datos voluminosos para reunirse e analizar en Excel? Aprende a automatizar o proceso completo de importación, transformación e modelización de datos coa nosa formación en consulta de enerxía e pivote de potencia en Excel.

Traballa correctamente con ficheiros Excel externos

cando o Diferentes ficheiros foron divididos para permitir que os diferentes usuarios poidan actualizar diferentes seccións e que a opción de traballo colaborativa incluída en Excel non se conservou, aquí están as alternativas a poñer en marcha. Entre eles, os dous últimos son certamente os máis interesantes, especialmente os últimos.

Use fórmulas 3D para eliminar ficheiros relacionados

Cada xestor de orzamento podería cumprir o seu propio orzamento nun tipo independente Excel ficheiro, que sería idéntico a cada xestor. Poderiamos simplemente copiar os contidos destas páxinas, entre os terminales de inicio e final, un ficheiro de consolidación, que inclúe fórmulas 3D, que, polo tanto, permitiríanse e cando engadan estas páxinas. Por suposto, esta non sería unha opción “dinámica”. Por conseguinte, debería compensarse creando unha páxina de documentación, mantendo na memoria as datas de cada última versión destas páxinas tan copiadas. Para que este proceso sexa efectivo, un certo cheque sería ser tan necesario. Se pode eliminar este traballo “manual”, sempre será preferido. Entón sería necesario escoller outras opcións presentadas a continuación.

Use os campos nomeados nos seus ficheiros de orixe

Use os “campos chamados” para os campos do ficheiro de orixe que se usan . No modelo. Esta opción, aínda que sinxela de configurar, causa vantaxes e desvantaxes.Tan ben usado, ha preservar as fórmulas relacionadas, pero se os usuarios comezar a copiar a información desde unha folla de Excel con campos nomeados noutros arquivos de Excel, poderían, así, comezar a crear conexións entre os arquivos. Que non debe ser conectada. Por iso, é necesario que sexa máis vixiante con esta opción. Se pode priorizar outra das seguintes opcións, sempre será mellor facelo.

Usar follas de “espello” nos seus ficheiros relacionados

Crear unha folla “Importar datos” en o modelo e unha folla “exportar datos” no ficheiro de orixe. Cada unha destas follas debe ser un espello entre si. Así, no canto de crear unha fórmula no modelo, que se refire ás células dun ficheiro externo, será creado na folla de importación do mesmo ficheiro (por exemplo, enlace interno). Só a folla de importación terá enlaces ao ficheiro de orixe. Así, na cela F10 da folla 1 presentando 1, atoparemos unha fórmula que se referirá ao Contidos da célula F10 de Flase 1 do Workbook 2. Isto facilitará en gran medida a xestión e actualización destes ficheiros.

Exemplo de crear a creación de follas de espello

Por exemplo, Aquí temos os datos do ficheiro orixinal (clase Ur 2):

Exportación de datos en Excel

e aquí temos a nosa folla de importación do noso modelo (Workbook 1). Teña en conta que na célula C8, é simplemente unha fórmula = que se refire exactamente á mesma cela ou C8 pero do segundo libro de traballo.

Importación de datos en Excel

Para completar a pestana Modelo que se usa para calcular as vendas dun xestor, debe evitarse para crear unha fórmula que se refire directamente ao ficheiro de orixe e isto por todas as razóns mencionadas anteriormente. Teña en conta que aquí o número do libro con datos de orixe e o nome das pestanas deste libro será longo, máis a fórmula será pesada, tanto en termos de peso como de comprensión.

ficheiros relacionados en Excel

Para completar esta sección, debe ser simplemente referirse a un cálculo na pestana de importación.

ficheiros relacionados en Excel

Ademais de aliviar as fórmulas, se algunha persoa eliminou ou modifica as celas no Workbook 2, sen abrir O Binder 1, probablemente sería capaz de ver os erros creados na pestana de exportación e corrixilos antes de gardar o seu ficheiro. Así, na apertura do Workbook 1, as fórmulas seguirían a informar os valores correctos.

Use a programación VBA para substituír os ficheiros relacionados

Tamén sería posible programar un Macro VBA que permitiría, sempre que un usuario faga clic nun botón, importará os datos do (s) ficheiros de orixe ao modelo e isto, en valores (polo que non hai ningún problema de rendemento relacionado con fórmulas moi avariciosas). Só tes que atopar alguén no teu negocio que sabe como programar en VBA! Pero se é posible, é unha solución bastante eficaz!

Usar a consulta de enerxía (a nosa opción preferida … e de lonxe!) Para substituír os ficheiros relacionados

a opción final permanece Para min, o uso da consulta de enerxía, que permite que todos os usuarios estean máis limitados a Excel, importan datos de ficheiros externos a través de consultas que só terán que ser actualizadas para obter os valores. Máis recente. A diferenza da linguaxe VBA, tales solicitudes son bastante sinxelas de escribir xa que, mesmo se a consulta de enerxía está baseada nunha linguaxe de programación, a linguaxe M, foi deseñada por Microsoft por unha solución “de baixo código”. Noutras palabras, os usuarios poden crear consultas, sen ter que aprender a codificar. Poden simplemente facer clic en diferentes botóns e menús para construír as súas consultas e isto xerará automaticamente o código para eles. Non é impresionante?

Exemplo de uso da consulta de enerxía

Importar datos de ficheiro en lugar de vincular

Por exemplo, aquí, primeiro será, desde o Binder 1, importar os datos do Workbook 2.

Importar un ficheiro de Excel con consulta de enerxía a continuación, será necesario escoller a pestana do libro 2 que queremos importar no Workbook 1.

Excel File importado a través da consulta de enerxía

Crear unha solicitude de transformación

Finalmente, premendo en varios botóns e funcións, poderemos transformar os datos nunha táboa ben estructurada e estes pasos de transformación rexistraranse a través dos pasos aplicados (panel dereito).Isto significa que, sobre o desconto, a solicitude devolverá a través de todos os pasos de transformación e, polo tanto, hai datos novos ou datos modificados no Workbook 2, todo será facilmente integrado no reforzo 1. P.>

Consulta de consulta de potencia

Recuperar datos importados nunha

Tab, entón será posible recuperar os datos nunha táboa de Excel, a creación dun Dynamic Crosstab ou un crosstab dinámico baseado nestes datos ou enviando estes datos no pivote de potencia para crear un cubo virtual conectando esta táboa con outras táboas.

Excel matriz resultante dun Consulta de consulta de potencia'une requête Power Query

Crear unha consulta alternativa para acadar o mesmo resultado que anteriormente

Normalmente, coa consulta de enerxía, estamos intentando reconstruír bases de datos cos datos “non calculados “. Pola contra, úsanse as medidas en Dax, en pivote de poder ou pilotos. Crossover dinámico, para facer os cálculos. Pero, se é necesario, sería posible, en consulta de poder, para obter unha pintura con vendas, en vez de unha mesa con prezos e os números de unidades. Isto realmente nos traería de volta ao resultado no noso exemplo.

Proporcione a vantaxe dos datos importados a través de datos Obtida por ligazón

O que se necesita sobre todo aquí é que o usuario de consulta de enerxía xa non funciona con datos relacionados senón con datos importados, que se actualizan a petición. Ao facelo, os problemas dos ficheiros relacionados son completamente eliminados. Do mesmo xeito, o peso do ficheiro, que non require fórmulas complexas, que comprime os datos.

Non hai máis razón para non usar a consulta de enerxía

potencia. A consulta está agora dispoñible no Gran gran maioría das versións de Excel gratuitamente. Debemos engadir un complemento para a versión 2010 e 2013 pero está dispoñible en todas as outras versións posteriores. A consulta de enerxía cambiou por completo a forma de traballar con Excel, optimizando o tempo necesario para realizar tarefas manuais, pero tamén axustar os problemas de longa data, como o problema dos ficheiros relacionados.

ficheiro de soporte VIP para descargar

Para descargar o ficheiro empregado neste tutorial, convértese nun membro VIP do CFO enmascarado.

Formación complementaria

Co fin de profundar o seu coñecemento con Excel, especialmente o Tarefas que afectan datos, consolidación e procesamento de datos, recomendamos a nosa formación de Excel – Introdución á consulta de enerxía e a linguaxe M.

Aquí tes algúns comentarios dos alumnos que seguiron esta formación:

CFO-MaskFormation-in-Line_fbO CFO enmascarado ofrece unha gran variedade de decisión Facer formación informática con Excel e Power BI, a través dun portal en liña e remoto en TE MPs reais, segundo un calendario. Se desexa organizar cursos privados, simplemente envíanos un correo electrónico a [email protected] Os certificados axeitados para os estándares de educación continua para as diversas ordes de traballo de Quebec ofrécense para toda a formación.

Deixa unha resposta

O teu enderezo electrónico non se publicará Os campos obrigatorios están marcados con *