Excel: 5 consells per evitar problemes de fitxer enllaçats

Un client va fer la següent pregunta: “En un context on diversos departaments estan treballant en un pressupost, vam separar la nostra Model en diversos fitxers … Amb les dificultats que implica, les fórmules més complicades i els enllaços entre els fitxers que es mantindran. Els altres treballen en cas contrari? “. Certament, els problemes dels fitxers d’Excel relacionats es produeixen especialment en empreses que tenen un procés pressupostari descentralitzat.

La galera dels fitxers d’Excel enllaçat

Tot i que Excel sempre pot proporcionar una resposta a tots Problemes (per la seva naturalesa de flexibilitat extrema), el concepte de gestió de fitxers vinculada constitueix sens dubte el seu taló d’Aquil·les. És aquesta debilitat que anima a molts a convertir-se en “sistemes” pressupostar / predicció. Aquest últim ofereix interfícies de col·laboració i eliminar els problemes dels fitxers independents. Però aquests sistemes, sovint són cars, realment són la solució definitiva a qualsevol problema de fitxer relacionat? L’article té com a objectiu oferir alternatives pressupostàries senzilles i molt baixes, però sovint desconegudes per als usuaris d’Excel.

Els usuaris d’Excel creen fitxers enllaçats?

En teoria, mai no hauríeu de dividir un model d’Excel Múltiples fitxers, tret que:

  • sou més d’un usuari que ha d’utilitzar el model al mateix temps i l’opció de treball col·laborativa inclòs a Excel ja no és suficient
  • S’han d’enviar diferents parts del model a diferents destinataris
  • El vostre model és massa gran
Aquest article va ser publicat originalment el 11 de juliol de 2013 i es va posar Del 16 de novembre de 2020.

Quins són els creadors de fitxers enllaçats?

Gestió de fitxers a Excel exposa els usuaris a múltiples problemes potencials:

  • a Consulteu les dades actualitzades, en un fitxer que s’alimenta d’un altre fitxer, els dos fitxers s’han d’obrir
  • quan un usuari canvia el fitxer font, especialment quan es suprimeix o modifica els rangs de dades, sense obrir el fitxer Model (el fitxer Excel que s’alimenta), el model del model sovint es reunirà amb fórmules que ja no són correctes (referència a les platges de dades dolentes o que tornen del missatge d’error #REF a quadrat. (Referència a rangs de dades que s’han suprimit)
  • Si el nom de les fulles i els fitxers són els menys llargs, això augmentarà significativament les fórmules i, per tant, el pes del model, augmentant així la possibilitat de reunir-se Problemes de rendiment
Teniu fonts de dades voluminoses per recopilar i analitzar a Excel? Apreneu a automatitzar tot el procés d’importació, transformació i modelatge de dades amb la nostra formació sobre consulta elèctrica i pivot de potència a Excel.

Treballar correctament amb fitxers Excel Excel

quan el S’han dividit diferents fitxers per permetre als diferents usuaris que actualitzin diferents seccions i que l’opció de treball col·laborativa inclosa a Excel no hagi estat retinguda, aquí hi ha les alternatives que es posaran al seu lloc. Entre ells, els dos últims són, sens dubte, els més interessants, especialment els últims.

Utilitzeu fórmules 3D per eliminar fitxers relacionats

Cada gestor de pressupostos podria complir el seu propi pressupost en un tipus independent d’Excel Fitxer, que seria idèntic per a cada gestor. A continuació, podríem copiar el contingut d’aquestes pàgines, entre terminals d’inici i finalització, un fitxer de consolidació, que inclou fórmules 3D, que, per tant, es permetrà que i quan afegeixen aquestes pàgines. Per descomptat, això no seria una opció “dinàmica”. Per tant, hauria de ser compensat creant una pàgina de documentació, mantenint-se en memòria les dates de cada última versió d’aquestes pàgines tan copiades. Perquè aquest procés sigui efectiu, un cert control ho faria Sigui tan necessari. Si pots eliminar aquest treball “manual”, sempre es preferirà. A continuació, seria necessari triar altres opcions presentades a continuació.

Utilitzeu els camps anomenats als fitxers d’origen

Utilitzeu els “camps anomenats” per als camps del fitxer font que s’utilitzen . En el model. Aquesta opció, encara que sigui senzilla de configurar, provoca avantatges i desavantatges.Tan ben utilitzat, es preservarà les fórmules relacionades, però si els usuaris comencen a copiar la informació des d’un full d’Excel amb camps anomenats en altres fitxers d’Excel, podrien començar a crear enllaços entre fitxers. Que no ha de ser vinculat. Per tant, cal tenir una addició extra amb aquesta opció. Si es pot prioritzar una altra de les opcions següents, sempre serà millor fer-ho.

Utilitzeu fulls “mirall” als fitxers relacionats

Creeu un full “importació de dades” El model i un full “Exportació de dades” al fitxer font. Cada un d’aquests fulls ha de ser un mirall entre si. Per tant, en lloc de crear una fórmula en el model, que fa referència a les cèl·lules d’un fitxer extern, les fórmules cel·lulars seran Creat al full d’importació del mateix fitxer (tan enllaç intern). Només el full d’importació tindrà enllaços al fitxer font. Així, a la cel·la F10 de la fulla 1 Presentació 1, trobarem una fórmula que es referirà a la Contingut de la cel·la F10 de la Flasa 1 del llibre de treball 2. Això facilitarà en gran mesura la gestió i actualització d’aquests fitxers.

Exemple de creació de fulles de mirall

Per exemple, Aquí tenim les dades del fitxer original (classe Ur 2):

Exportació de dades en Excel

I aquí tenim el nostre full d’importació del nostre model (llibre de treball 1). Tingueu en compte que a la cel·la C8, és simplement una fórmula = que exactament es refereix a la mateixa cel·la, o C8, però del segon llibre de treball.

Importació de dades a Excel

Per completar la pestanya Model que s’utilitza per calcular les vendes del gestor d’un gestor, s’hauria d’evitar per crear una fórmula que es refereixi directament al fitxer font i això per totes les raons esmentades anteriorment. Tingueu en compte que el número del llibre de treball amb les dades d’origen i el nom de les pestanyes d’aquest llibre de treball serà llarg, més la fórmula serà pesada, tant en termes de pes i comprensió.

Arxius relacionats a Excel

Per completar aquesta secció, hauria de ser més aviat consulteu un càlcul a la pestanya Importació.

Arxius relacionats a Excel

A més d’alleugerir les fórmules, si alguna persona ha esborrat o modificat cèl·lules al llibre de treball 2, sense obrir-se La carpeta 1, probablement podrà veure els errors creats a la pestanya Export i corregir-los abans de desar el seu fitxer. Així, a l’obertura del llibre de treball 1, les fórmules continuarien informant dels valors correctes.

Utilitzeu la programació de VBA per substituir els arxius relacionats

També seria possible programar un Macro VBA que permetria, cada vegada que un usuari faci clic en un botó, importarà les dades dels fitxers d’origen (s) al model i això, en valors (de manera que no hi ha cap problema de rendiment relacionat amb fórmules massa cobejos). Només has de trobar algú en el seu negoci que sàpiga programar a VBA! Però si és possible, és una solució bastant eficaç!

Utilitzeu la consulta d’alimentació (la nostra opció preferida … i de lluny!) Per substituir els arxius relacionats

L’opció final restes Per a mi, l’ús de la consulta d’alimentació, que permet a tots els usuaris més limitada a Excel, importar dades de fitxers externs a través de consultes que només hauran d’actualitzar-se per obtenir els valors. Més recents. A diferència del llenguatge VBA, aquestes sol·licituds són bastant senzilles d’escriure, ja que, fins i tot si la consulta d’alimentació es basa en un llenguatge de programació, el llenguatge M, va ser dissenyat per Microsoft per a una solució “Baixa”. En altres paraules, els usuaris poden crear consultes, sense haver d’aprendre a codificar. Simplement poden fer clic a diferents botons i menús per construir les seves consultes i això generarà automàticament el codi per a ells. No és increïble?

Exemple d’ús de la consulta d’alimentació

Importa dades de fitxers en lloc d’enllaçar

Per exemple, aquí serà, primer serà, des del Binder 1, importa les dades del llibre de treball 2.

Importa un fitxer Excel amb consulta d'alimentació Següent, serà necessari triar la pestanya del llibre de treball 2 Que volem importar al llibre de treball 1.

Fitxer Excel importat a través de consulta elèctrica

Crear una sol·licitud de transformació

Finalment, fent clic a diversos botons i funcions, podrem transformar les dades en una taula ben estructurada i aquests passos de transformació es gravaran a través dels passos aplicats (panell dret).Això significa que en el descompte, la sol·licitud retornarà a través de tots els passos de transformació i, per tant, hi ha dades noves o dades modificades al llibre de treball 2, el conjunt s’integrarà fàcilment a BOLSTER 1. p.>

Consulta de consulta d'alimentació

Recuperar dades importades en una pestanya

Es podrà recuperar les dades en una taula d’Excel, creant un Crestab dinàmic o un Crestest dinàmic basat en aquestes dades o enviament d’aquestes dades en pivot de potència per crear un cub virtual connectant aquesta taula amb altres taules.

Array Excel resultant d'un Consulta d'alimentació'une requête Power Query

Crea una consulta alternativa per aconseguir el mateix resultat que anteriorment

Normalment, amb consulta d’energia, estem tractant de reconstruir les bases de dades amb les dades “no calculades “. Més aviat, s’utilitzen les mesures en DAX, en pivot de potència o pestanyes. Crossover dinàmic, per fer els càlculs. Però, segons sigui necessari, també seria possible, en consulta elèctrica, per obtenir una pintura amb vendes, en lloc d’una taula amb preus i els números d’unitats. Això realment ens portaria al resultat en el nostre exemple.

Proporcionar l’avantatge de les dades importades sobre les dades Obtingut per enllaç

Què és majoritàriament necessari aquí és que l’usuari de consulta d’alimentació ja no funciona amb dades relacionades, sinó amb dades importades, que s’actualitzen a petició. En fer-ho, els problemes dels fitxers relacionats s’eliminen completament. De la mateixa manera, el pes del fitxer, que no requereix fórmules complexes, que comprimeix les dades.

No hi ha més motius per no utilitzar consulta elèctrica

potència. La consulta ja està disponible a la pàgina gran majoria de versions d’Excel de forma gratuïta. Hem d’afegir un complement per a la versió 2010 i 2013, però està disponible en totes les altres versions posteriors. La consulta d’alimentació ha canviat completament la manera de treballar amb Excel, optimitzant el temps necessari per realitzar tasques manuals, però també ajustant qüestions de llarga data, com ara el problema dels fitxers relacionats!

Fitxer de suport VIP per descarregar

Per descarregar el fitxer utilitzat en aquest tutorial, convertiu-vos en un membre VIP del CFO emmascarat.

Formació complementària

Per aprofundir els vostres coneixements amb Excel, especialment el Tasques que afecten les dades, la consolidació i el processament de dades, es recomana la nostra formació d’Excel – Introducció a la consulta elèctrica i el llenguatge M.

Aquí hi ha alguns comentaris dels aprenents que han seguit aquesta formació:

CFO-mask_formation-in-line_fbEl CFO emmascarat ofereix un ampli ventall de decisió. Fer formació informàtica amb Excel i Power Bi, a través d’un portal en línia i remot a TE Dames reals, segons un calendari. Si voleu organitzar cursos privats, simplement envieu-nos un correu electrònic a [email protected] Els certificats adequats per a les normes de formació continuada per a les diferents ordres laborals de Quebec s’ofereixen per a tota la formació.

Deixa un comentari

L'adreça electrònica no es publicarà. Els camps necessaris estan marcats amb *