Excel: 5 consejos para evitar problemas de archivo vinculados

Un cliente Recientemente hizo la siguiente pregunta: «En un contexto donde varios departamentos están trabajando en un presupuesto, separamos nuestra Modelo en varios archivos diferentes … con las dificultades que implica, las fórmulas y los vínculos más complicados entre los archivos que se deben mantener. ¿Otros trabajan de otra manera? «. Ciertamente, los problemas de los archivos de Excel relacionados se producen especialmente en compañías que tienen un proceso de presupuesto descentralizado.

Los archivos de la galera de los archivos de Excel vinculados

Aunque Excel siempre puede proporcionar una respuesta a todos los Problemas (por su naturaleza de flexibilidad extrema), el concepto de gestión de archivos vinculados constituye sin duda su tacón de Aquiles. Es esta debilidad la que alientan a muchos a recurrir a la presupuesta / pronóstico de «sistemas». Estos últimos ofrecen interfaces de colaboración y eliminan los problemas de los archivos independientes. Pero estos sistemas, a menudo caros, son realmente la solución definitiva para cualquier problema de archivo relacionado. Esta El artículo tiene como objetivo ofrecer alternativas simples y muy bajas presupuestarias, pero a menudo desconocidas para sobresalir a los usuarios.

¿Los usuarios de Excel crean archivos vinculados?

En teoría, uno nunca debe dividir un modelo de Excel en múltiples archivos, a menos que:

  • Usted es más de un usuario que debe usar el modelo al mismo tiempo y la opción de trabajo de colaboración incluida en Excel ya no es suficiente
  • Diferentes partes del modelo deben enviarse a diferentes destinatarios
  • Su modelo es demasiado grande
Este artículo fue publicado originalmente el 11 de julio de 2013 y se puso Del 16 de noviembre, 2020.

¿Qué son los creadores de los archivos vinculados?

La administración de archivos en Excel expone a los usuarios múltiples problemas potenciales:

  • a Ver los datos actualizados, en un archivo que se alimenta en otro archivo, ambos archivos deben abrirse
  • cuando un usuario cambia el archivo de origen, especialmente cuando elimina o modifica los rangos de datos, sin abrir los rangos de datos. Modelo (el archivo de Excel que se alimenta de él), el modelo del modelo a menudo se reunirá con fórmulas que ya no son correctas (referencia a las playas de datos incorrectos o que regresan del mensaje de error #REF a STARLY! (Referencia a los rangos de datos que se han eliminado)
  • Si el nombre de las hojas y los archivos es el menos largo, esto aumentará significativamente las fórmulas y, por lo tanto, el peso del modelo, lo que aumenta la posibilidad de reunirse. Problemas de rendimiento

¿Tiene fuentes de datos voluminosos para reunirse y analizar en Excel? Aprenda cómo automatizar la importación completa, la transformación y el proceso de modelado de datos con nuestra capacitación en consulta de energía y pivote de potencia en Excel.

Trabaje correctamente con archivos Externos Excel

Cuando el Se han dividido diferentes archivos para permitir que los diferentes usuarios actualicen diferentes secciones y que la opción de trabajo de colaboración incluida en Excel no se ha conservado, aquí están las alternativas a poner en su lugar. Entre ellos, los dos últimos son ciertamente los más interesantes, especialmente los últimos.

Use fórmulas 3D para eliminar los archivos relacionados

Cada administrador de presupuesto podría cumplir su propio presupuesto en un tipo de excelente excelente. Archivo, que sería idéntico para cada gerente. Luego, podríamos simplemente copiar los contenidos de estas páginas, entre los terminales de inicio y finalización, un archivo de consolidación, que comprende fórmulas 3D, que, por lo tanto, se permitiría y cuando agreguen estas páginas. Por supuesto, esta no sería una opción «dinámica». Por lo tanto, debería compensarse creando una página de documentación, manteniendo en la memoria las fechas de cada última versión de estas páginas así que copiadas. Para que este proceso sea efectivo, cierto cheque ser tan necesario. Si puede eliminar este trabajo «manual», siempre será preferido. Entonces sería necesario elegir otras opciones presentadas a continuación.

Use los campos nombrados en sus archivos de origen

Utilice los «campos nombrados» para los campos del archivo fuente que se utilizan . En el modelo. Esta opción, aunque simple de configurar, causa ventajas y desventajas.Tan bien utilizado, preservará las fórmulas relacionadas, pero si los usuarios comienzan a copiar la información de una hoja de Excel con campos nombrados en otros archivos de Excel, por lo tanto, podrían comenzar a crear enlaces entre archivos. Lo que no debe estar vinculado. Por lo tanto, es necesario ser muy vigilante con esta opción. Si puede priorizar otra de las siguientes opciones, siempre será mejor hacerlo.

Use las hojas «espejo» en sus archivos relacionados

Crear una hoja «Importación de datos» en El modelo y una hoja «Datos de exportación» en el archivo de origen. Cada una de estas hojas debe ser un espejo entre sí. Por lo tanto, en lugar de crear una fórmula en el modelo, que se refiere a las células de un archivo externo, las fórmulas celulares serán creado en la hoja de importación del mismo archivo (para que el enlace interno). Solo la hoja de importación tendrá enlaces al archivo de origen. Por lo tanto, en la celda F10 de la Foliación 1 de la hoja 1, encontraremos una fórmula que se referirá a la Contenido de la celda F10 de Flase 1 del libro de trabajo 2. Esto facilitará en gran medida la administración y actualización de dichos archivos.

Ejemplo de creación de la creación de hojas de espejo

Por ejemplo, Aquí tenemos los datos del archivo original (clase UR 2):

Datos de exportación en Excel

y aquí tenemos nuestra hoja de importación de nuestro modelo (Workbook 1). Tenga en cuenta que en la celda C8, es simplemente una fórmula = que se refiere exactamente a la misma celda, o C8, pero del segundo libro de trabajo.

importando datos en Excel

Para completar la pestaña Modelo que se usa para calcular las ventas de un administrador, debe evitarse para crear una fórmula que se refiere directamente al archivo de origen y esto para todas las razones mencionadas anteriormente. Tenga en cuenta que el número del libro de trabajo con los datos de origen y el nombre de las pestañas de este libro de trabajo será largo, más será la fórmula pesada, tanto en términos de peso y comprensión.

Archivos relacionados en Excel

Para completar esta sección, debe ser simplemente consultar un cálculo en la pestaña Importar.

Archivos relacionados en Excel

Además de aligerar las fórmulas, si alguna vez una persona eliminó o modificó celdas en el libro de trabajo 2, sin apertura El aglutinante 1, probablemente podría ver los errores creados en la pestaña Exportación y corríjalos antes de guardar su archivo. Por lo tanto, en la apertura del libro de trabajo 1, las fórmulas continuarán informando los valores correctos.

Use la programación VBA para reemplazar los archivos relacionados

También sería posible programar una Macro VBA que permitiría, siempre que un usuario haga clic en un botón, importará los datos de los archivos de origen al modelo y esto, en valores (por lo que ningún problema de rendimiento relacionado con fórmulas demasiado codicitas). ¡Solo tienes que encontrar a alguien en tu negocio que sabe cómo programar en VBA! Mais si c’est possible, c’est une solution plutôt efficace!

Utiliser Power Query (notre option privilégiée… et de loin!) pour remplacer les fichiers liés

L’option ultime demeure pour moi, l’utilisation de Power Query, qui permet à tous les utilisateurs le moindrement dégourdis dans Excel, d’importer les données de fichiers externes via des requêtes qui n’auront qu’à être actualisées pour aller chercher les valeurs les más recientes. A diferencia del idioma VBA, dichas solicitudes son bastante simples de escribir, ya que incluso si la consulta de energía se basa en un lenguaje de programación, el idioma M, fue diseñado por Microsoft para una solución de «código bajo». En otras palabras, los usuarios pueden crear consultas, sin tener que aprender a código. Simplemente pueden hacer clic en los diferentes botones y menús para construir sus consultas y esto generará automáticamente el código para ellos. ¿No es increíble?

Ejemplo de uso de la consulta de energía

Importar datos de archivos en lugar de enlazar

Por ejemplo, aquí, primero será, de la Carpeta 1, importe los datos del libro de trabajo 2.

Importar un archivo de Excel con la consulta de alimentación Siguiente, será necesario elegir la pestaña del libro de trabajo 2 que queremos importar en el libro de trabajo 1.

Excel Archivo importado a través de la consulta de alimentación

Crear una solicitud de transformación

Finalmente, haciendo clic en varios botones y características, podremos transformar los datos en la tabla bien estructurada y estos pasos de transformación se registrarán a través de los pasos aplicados (panel derecho).Esto significa que, en el descuento, la solicitud volverá a través de todos los pasos de transformación y, por lo tanto, hay datos nuevos o datos modificados en el libro de trabajo 2, el conjunto se integrará fácilmente en Bolster 1. P.>

Consulta de consulta de energía

Recupere los datos importados en la pestaña

En la pestaña, será posible recuperar los datos en una tabla de Excel, creando una Dynamic Crosstab o un Dynamic Crosstab se basa en estos datos o enviando estos datos en Poder Pivot para crear un cubo virtual conectando esta tabla con otras tablas.

Excel Array resultante de un Consulta de consulta de energía'une requête Power Query

Crear una consulta alternativa Para lograr el mismo resultado que anteriormente

normalmente, con la consulta de alimentación, estamos tratando de reconstruir bases de datos con los datos «no calculados «. Más bien, se utilizan las mediciones en DAX, en pivote de potencia o pestañas. Crossover dinámico, para hacer los cálculos. Pero según sea necesario, también sería posible, en consulta de poder, para obtener una pintura con ventas, en lugar de una mesa con precios y los números de unidades. Esto realmente nos llevaría de vuelta al resultado en nuestro ejemplo.

consulta de la pregunta de potencia

Proporcionar la ventaja de los datos importados sobre los datos obtenido por Link

Lo que se necesita principalmente aquí es que el usuario de la consulta de energía ya no funciona con datos relacionados, sino con los datos importados, que se actualizan a petición. Al hacerlo, los problemas de los archivos relacionados se eliminan por completo. De manera similar, el peso del archivo, que no requiere fórmulas complejas, que comprime los datos.

No hay más motivos para no usar la consulta de alimentación

Potencia. La consulta ahora está disponible en el Gran mayoría de las versiones de Excel de forma gratuita. Debemos agregar un complemento para la versión 2010 y 2013, pero está disponible en todas las versiones posteriores. La consulta de poder ha cambiado completamente la forma de trabajar con Excel, optimizando el tiempo requerido para realizar tareas manuales, pero también ajustar problemas de larga duración, como el problema de los archivos relacionados!

archivo de soporte VIP para descargar

Para descargar el archivo utilizado en este tutorial, convertirte en un miembro VIP del CFO enmascarado.

Formación complementaria

Para profundizar su conocimiento con Excel, especialmente el Tareas que afectan a los datos, la consolidación y el procesamiento de datos, le recomendamos que nuestra capacitación de Excel – Introducción a la consulta de energía y el idioma M.

Aquí hay algunos comentarios de los alumnos que han seguido este entrenamiento:

CFO-Mask_Formation-in-line_fbEl CFO enmascarado ofrece una amplia gama de decisiones Hacer entrenamiento informático con Excel y Power Bi, a través de un portal en línea y remoto en TE MPS reales, según un calendario. Si desea organizar cursos privados, simplemente envíenos un correo electrónico a [email protected] Los certificados adecuados para las normas de educación continua para las diversas órdenes ocupacionales de Quebec se ofrecen para toda la capacitación.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *